Quick and Dirty Postgres Hacks

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!

Removing a sequence from a table

Sometimes you'll create a table with a sequence, add a bunch of data and then find that you don't need the sequence anymore.

First you need to find out what the sequence is called:

SELECT refobjid::regclass as table_name,
       objid::regclass as sequence
  FROM pg_depend
       WHERE refobjid = '<table_name>'::regclass
       AND deptype = 'a';

Then you:

• Remove the sequence from the table:

ALTER SEQUENCE <sequence> OWNED BY NONE;

• Alter the table to not assign from the sequence by default:

ALTER TABLE <table_name> ALTER COLUMN <sequence id column> DROP DEFAULT;

• Convert the type of the id column back to int from serial:

ALTER TABLE expiration_rules ALTER COLUMN exp_row type int;

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
• Updated 4/13/2022: Added sequence removal