Quick and Dirty Postgres Hacks

Posted on Tue 12 May 2020 in technology

I have a sieve-like memory, so I am going to start recording things about Postgres that I find myself googling every time I need to do them.

Resetting sequences

An attempt to insert a row into a table with a serial fails because the someone has manually inserted a value and failed to update the sequence.

SELECT setval('equities_id_seq', (SELECT MAX(id) from "equities"))

Where:

  • equities_id_seq is the Postgres sequence object
  • id is the serial being corrected, and
  • equities is the table

Adding sequence to an already existing table

The problem: you have a column with

What's holding locks in my database?

This query (or some variant of it) gives you all the queries currently holding locks on your database.

SELECT distinct
       a.query,
       a.pid,
       age(now(), a.query_start) AS "age",
       a.datname,
       l.transactionid,
       l.mode,
       l.GRANTED,
       a.usename,
       a.query,
       a.query_start
  FROM pg_stat_activity a
       INNER JOIN pg_locks l ON l.pid=a.pid
 WHERE a.query not like '%a.query%'
       ORDER BY a.pid, age;

F**k this lock in particular

Politely tell a troublesome query identified using the last command:

SELECT pg_cancel_backend(<pid>)

Go nuclear on said query:

SELECT pg_terminate_backend(<pid>)

Turn autovacuum off for a table

Don't do this lightly: autovacuum is needed for postgres to function well. But if you're doing periodic bulk loads on a table that has had surgery done it and autovacuum keeps interrupting the party...

ALTER TABLE table_name SET (autovacuum_enabled = false);

Finding how big a relation is

This one is pretty nifty:

SELECT pg_size_pretty( pg_total_relation_size('<table>') );

Querying how many rows are in a table without querying the table

This function is a roundabout way to get an estimate of how many rows are in a table without actually querying it:

DROP FUNCTION count_estimate(query text);
CREATE FUNCTION count_estimate(query text) RETURNS BIGINT AS
$func$
DECLARE
    rec   record;
    ROWS  BIGINT;
BEGIN
    FOR rec IN EXECUTE 'EXPLAIN ' || query LOOP
        ROWS := SUBSTRING(rec."QUERY PLAN" FROM ' rows=([[:digit:]]+)');
        EXIT WHEN ROWS IS NOT NULL;
    END LOOP;

    RETURN ROWS;
END
$func$ LANGUAGE plpgsql;

You can then obtain a reasonable estimate of a query return size by executing:

select count_estimate('select * from <table>')

The results returned by this function will get progressively worse as dead tuples accumulate; make sure autovacuum is turned on for the table!

Post Metadata
  • Updated 5/15/2020: Added sequence assignment
  • Updated 6/10/2020: Added autovacuum shenanigans
  • Updated 6/15/2020: Added relation size and count estimate queries