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

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

Kill a troublesome query identified using the last command:

SELECT pg_cancel_backend(<pid>)
Post Metadata

Last Updated: 5/12/2020