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.
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"))
equities_id_seqis the Postgres sequence object
serialbeing corrected, and
equitiesis 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:
Go nuclear on said query:
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!
- 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