SELECT
    pid,
    (CURRENT_TIMESTAMP - query_start) as query_time,
    datname,
    usename,
    query
FROM pg_stat_activity
ORDER BY query_time DESC;

This is particularly useful for keeping an eye on long-running queries. Bonus - if you need to kill one, grab the PID and run:

SELECT pg_terminate_backend(12345);

tldr pages are simplified and community-driven man pages

I use this tool daily. It's a companion to man pages which provides a small number of usage examples for a command instead of an exhaustive reference for every flag and parameter. One of my favourite aspects of the project is the large ecosystem of client libraries for consuming the content via the console or the web.

• Website
• Github

I've encountered python warnings before and I've used python's -W flag to configure displaying them, but I'd never really given much thought to how warnings are raised in userland code until I read this article - Working with warnings in Python. This is a language feature to use infrequently, but worth having in the back pocket when maintaining libraries.

I use Xpath selectors infrequently enough that I can never really remember the syntax off the top of my head. Fortunately this reference usually gets me through. Devhints also has a wide variety of other cheat sheets but this is the one I keep on coming back to.

I usually reach for freezegun when I need to mock calls to the standard library datetime functions, but Adam Johnson explains in this recent blog post why he has chosen to create a new library for this. Time-machine uses a similar decorator-based syntax to freezegun but promises substantial performance gains with large codebases.

I'm looking forward to giving this a spin next time I need to write a test that mocks the current time.

q executes SQL-like queries on CSV and TSV files.

I use this all the time for running quick ad-hoc queries on data that is a bit too large to deal with in a spreadsheet.

Example call:

q -H -d "," "SELECT COUNT(*) FROM ./raw.csv WHERE created>'2020-05-22 23:00:00';"

-H skips the header row. -d specifies the delimiter.

• Github
• Examples

pspg is a pager with support for tabular data

Install it:

sudo apt install pspg

Then set it as the default pager for psql in ~/.psqlrc:

\x auto
\pset pager on
\pset linestyle unicode
\pset border 2
\setenv PAGER 'pspg -bX --no-mouse'

This is particularly useful when combined with yesterday's post

Two handy examples:

1) Bind local port 4001 to port 5432 on postgres-server:

ssh user@postgres-server -L 4001:localhost:5432
psql -h 127.0.0.1 -p 4001

2) Bind local port 4001 to port 5432 on postgres-server using sentinel as a jump box:

ssh user@sentinel -L 4001:postgres-server:5432
psql -h 127.0.0.1 -p 4001

Poetry's killer feature is consistent deterministic dependency resolution and proper handling of the diamond dependency problem. In most packaging ecosystems, this is table stakes. Sadly in python, it still isn't.

A few years ago I migrated some projects to pipenv hoping it would be the solution I was looking for but, like many others, I quickly hit some roadblocks with this approach.

I've been using poetry in earnest for around six months and have since migrated all of my active personal projects to use poetry. It is now my go-to solution for dependency installation, virtualenv management and packaging.

• Github
• Docs

First!

The plan for this is to post fairly regularly with the format being either an interesting link with a bit of commentary or a useful code snippet. Lets see how long I stick to it for...