Tag: locks

How to run short ALTER TABLE without long locking concurrent queries

Recently I've seen case like:

  1. application had to add column to table.
  2. application ran ALTER TABLE ADD COLUMN (without default!)
  3. everything stopped for many MINUTES

Why? How to avoid the problem?

Continue reading How to run short ALTER TABLE without long locking concurrent queries

Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY

On 29th of March 2019, Peter Eisentraut committed patch:

REINDEX CONCURRENTLY 
 
This adds the CONCURRENTLY option to the REINDEX command.  A REINDEX
CONCURRENTLY on a specific index creates a new index (like CREATE
INDEX CONCURRENTLY), then renames the old index away and the new index
in place and adjusts the dependencies, and then drops the old
index (like DROP INDEX CONCURRENTLY).  The REINDEX command also has
the capability to run its other variants (TABLE, DATABASE) with the
CONCURRENTLY option (but not SYSTEM).
 
The reindexdb command gets the --concurrently option.
 
Author: Michael Paquier, Andreas Karlsson, Peter Eisentraut
 
Discussion: https://www.postgresql.org/message-id/flat/-956b-4478-45ed-%402ndquadrant.com#

Continue reading Waiting for PostgreSQL 12 – REINDEX CONCURRENTLY

Waiting for 9.5 – Reduce lock levels of some trigger DDL and add FKs

On 5th of April, Simon Riggs committed patch:

Reduce lock levels of some trigger DDL and add FKs
 
Reduce lock levels to ShareRowExclusive for the following SQL
 CREATE TRIGGER (but not DROP or ALTER)
 ALTER TABLE ENABLE TRIGGER
 ALTER TABLE DISABLE TRIGGER
 ALTER TABLE … ADD CONSTRAINT FOREIGN KEY
 
Original work by Simon Riggs, extracted and refreshed by Andreas Karlsson
New test cases added by Andreas Karlsson
Reviewed by Noah Misch, Andres Freund, Michael Paquier and Simon Riggs

Continue reading Waiting for 9.5 – Reduce lock levels of some trigger DDL and add FKs

Waiting for 9.5 – Implement SKIP LOCKED for row-level locks

On 7th of October, Alvaro Herrera committed patch:

Implement SKIP LOCKED for row-level locks
 
This clause changes the behavior of SELECT locking clauses in the
presence of locked rows: instead of causing a process to block waiting
for the locks held by other processes (or raise an error, with NOWAIT),
SKIP LOCKED makes the new reader skip over such rows.  While this is not
appropriate behavior for general purposes, there are some cases in which
it is useful, such as queue-like tables.
 
Catalog version bumped because this patch changes the representation of
stored rules.
 
Reviewed by Craig Ringer (based on a previous attempt at an
implementation by Simon Riggs, who also provided input on the syntax
used in the current patch), David Rowley, and Álvaro Herrera.
 
Author: Thomas Munro

Continue reading Waiting for 9.5 – Implement SKIP LOCKED for row-level locks

Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

On 16th of July, Kevin Grittner committed patch:

Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.
 
This allows reads to continue without any blocking while a REFRESH
runs.  The new data appears atomically as part of transaction
commit.
 
Review questioned the Assert that a matview was not a system
relation.  This will be addressed separately.
 
Reviewed by Hitoshi Harada, Robert Haas, Andres Freund.
Merged after review with security patch f3ab5d4.

Continue reading Waiting for 9.4 – Add support for REFRESH MATERIALIZED VIEW CONCURRENTLY.

Tips N’ Tricks – getting sizes of relations without locks

If you have production DB servers, chances are you're running variant of these queries:

SELECT SUM(pg_relation_size(oid)) FROM pg_class WHERE relkind = 'i'
SELECT SUM(pg_relation_size(oid)) FROM pg_class WHERE relkind = 'r'

To get summarized size of tables and/or indexes in your database (for example for graphing purposes).

This (getting pg_relation_size for rows in pg_class) has one problem – it can lock, or it can fail.

Continue reading Tips N' Tricks – getting sizes of relations without locks

Waiting for 9.1 – Transaction level advisory locks

On 18th of February, Itagaki Takahiro committed patch:

Add transaction-level advisory locks.
 
They share the same locking namespace with the existing session-level
advisory locks, but they are automatically released at the end of the
current transaction and cannot be released explicitly via unlock
functions.
 
Marko Tiikkaja, reviewed by me.

Continue reading Waiting for 9.1 – Transaction level advisory locks