Tag Archives: PostgreSQL

Postgresql index naming convention

Use the table name as the prefix. Use the following suffixes: _pkey for a Primary Key constraint _key for a Unique constraint _excl for an Exclusion constraint _idx for any other kind of index _fkey for a Foreign key _check for a Check constraint https://stackoverflow.com/a/4108266/801426 Also implicitly mentioned here — https://www.postgresql.org/docs/current/sql-altertable.html

Postgresql exercises

Basic querying — https://pgexercises.com/questions/basic/. Joins — https://pgexercises.com/questions/joins/. Data modification — https://pgexercises.com/questions/updates/. Aggregations — https://pgexercises.com/questions/aggregates/. That could be useful to refresh your memory. Not really up-to-date, but still pretty useful.

Install postgresql 11 on CentOs7

Just what to do, no discussion.

Postgres in docker: random «unique violation» errors

While running postgres in docker (for development, of course), from time to time I just run into errors like «Duplicate Key Value Violates Unique Constraint». I found the solution (thanks to this answer, for sure — https://stackoverflow.com/a/47089825/801426): if you have a table called «[table]», just do this

SELECT setval('[table]_id_seq', (SELECT MAX(id) FROM [table]));

1	SELECT setval('[table]_id_seq', (SELECT MAX(id) FROM [table]));

Another cool article on using PostgreSQL+pgBouncer with go

Extremely interesting and very practical talk about problems occurring with go+pgBouncer (in Russian, sorry). https://habr.com/ru/company/oleg-bunin/blog/461935/ And a video on that: https://www.youtube.com/watch?v=Uojy57I-xP0

PgBouncer and prepared statements

In our system, we use connection pooler called PgBouncer as a proxy to PostgreSQL server. PgBouncer has two main modes Session pooling mode This mode is less performant (it’s a default mode). When a client connects, a server connection will be assigned to it for the whole duration it stays connected. So it does not …

SELECT rolname,
    calls,
    total_time,
    mean_time,
    max_time,
    stddev_time,
    rows,
    query,
    regexp_replace(query, '[ \t\n]+', ' ', 'g') AS query_oneline
FROM pg_stat_statements
JOIN pg_roles r ON r.oid = userid
WHERE calls > 100
AND rolname NOT LIKE '%backup'
ORDER BY mean_time DESC
LIMIT 15;

SELECT rolname,

calls,

total_time,

mean_time,

max_time,

stddev_time,

rows,

query,

regexp_replace(query, '[ \t\n]+', ' ', 'g') AS query_oneline

FROM pg_stat_statements

JOIN pg_roles r ON r.oid = userid

WHERE calls > 100

AND rolname NOT LIKE '%backup'

ORDER BY mean_time DESC

LIMIT 15;

Golang, PostgreSQL and array_agg

If you have SQL like this:

PostgreSQL: using indices on json fields

It’s obviously a really bad idea. But if you really need it, that’s what you can do.

Postgresql: show all locked queries

WITH RECURSIVE l AS (
 SELECT pid, locktype, mode, granted,
ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj
 FROM pg_locks
), pairs AS (
 SELECT w.pid waiter, l.pid locker, l.obj, l.mode
 FROM l w
 JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted
 WHERE NOT w.granted
), tree AS (
 SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids
 FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l
 UNION ALL
 SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()
 FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )
)
SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,
      replace(a.state, 'idle in transaction', 'idletx') state,
      (clock_timestamp() - state_change)::interval(3) AS change_age,
      a.datname,tree.pid,a.usename,a.client_addr,lvl,
      (SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,
      repeat(' .', lvl)||' '||left(regexp_replace(query, '\s+', ' ', 'g'),100) query
FROM tree
JOIN pg_stat_activity a USING (pid)
ORDER BY path;

WITH RECURSIVE l AS (

SELECT pid, locktype, mode, granted,

ROW(locktype,database,relation,page,tuple,virtualxid,transactionid,classid,objid,objsubid) obj

FROM pg_locks

), pairs AS (

SELECT w.pid waiter, l.pid locker, l.obj, l.mode

FROM l w

JOIN l ON l.obj IS NOT DISTINCT FROM w.obj AND l.locktype=w.locktype AND NOT l.pid=w.pid AND l.granted

WHERE NOT w.granted

), tree AS (

SELECT l.locker pid, l.locker root, NULL::record obj, NULL AS mode, 0 lvl, locker::text path, array_agg(l.locker) OVER () all_pids

FROM ( SELECT DISTINCT locker FROM pairs l WHERE NOT EXISTS (SELECT 1 FROM pairs WHERE waiter=l.locker) ) l

UNION ALL

SELECT w.waiter pid, tree.root, w.obj, w.mode, tree.lvl+1, tree.path||'.'||w.waiter, all_pids || array_agg(w.waiter) OVER ()

FROM tree JOIN pairs w ON tree.pid=w.locker AND NOT w.waiter = ANY ( all_pids )

)

SELECT (clock_timestamp() - a.xact_start)::interval(3) AS ts_age,

replace(a.state, 'idle in transaction', 'idletx') state,

(clock_timestamp() - state_change)::interval(3) AS change_age,

a.datname,tree.pid,a.usename,a.client_addr,lvl,

(SELECT count(*) FROM tree p WHERE p.path ~ ('^'||tree.path) AND NOT p.path=tree.path) blocked,

repeat(' .', lvl)||' '||left(regexp_replace(query, '\s+', ' ', 'g'),100) query

FROM tree

JOIN pg_stat_activity a USING (pid)

ORDER BY path;