On 29th of March 2019, Peter Eisentraut committed patch:
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
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
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
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
Continue reading Waiting for 9.2 – DROP INDEX CONCURRENTLY
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!
Continue reading Bloat happens
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
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
Continue reading Waiting for 9.1 – INVALID FOREIGN KEYS
On 28th of July, Simon Riggs committed patch which:
Reduce LOCK levels OF CREATE TRIGGER AND SOME ALTER TABLE, CREATE RULE actions.
Avoid hard-coding lockmode used FOR many altering DDL commands, allowing easier
future changes OF LOCK levels. Implementation OF initial analysis ON DDL
sub-commands, so that many LOCK levels are now at ShareUpdateExclusiveLock OR
ShareRowExclusiveLock, allowing certain DDL NOT TO block reads/writes.
FIRST OF NUMBER OF planned changes IN this area; additional docs required
WHEN FULL project complete.
Continue reading Waiting for 9.1 – Reduced lock levels for ALTER TABLE