Recently I've seen case like:
Why? How to avoid the problem?
Continue reading How to run short ALTER TABLE without long locking concurrent queries
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
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
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
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.
On 6th of April, Simon Riggs committed patch:
Add DROP INDEX CONCURRENTLY [IF EXISTS], uses ShareUpdateExclusiveLock
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
For various reasons, and in various cases, bloat happens. Theoretically autovacuum protects us all, but sometimes it doesn't. Sometimes someone disables it, or mis-configures, or bad planet alignment happens, and we end up in deep bloat.
What to do then? Vacuum? Vacuum Full? Cluster? No. pg_reorg!
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
On 8th of February, Simon Riggs committed patch:
Extend ALTER TABLE TO allow FOREIGN KEYS TO be added WITHOUT initial validation. FK constraints that are marked NOT VALID may later be VALIDATED, which uses an ShareUpdateExclusiveLock ON CONSTRAINT TABLE AND RowShareLock ON referenced TABLE. Significantly reduces LOCK strength AND duration WHEN adding FKs. NEW state visible FROM psql. Simon Riggs, WITH reviews FROM Marko Tiikkaja AND Robert Haas