On 14th of November 2020, Tom Lane committed patch:
Provide the OR REPLACE option for CREATE TRIGGER.
This is mostly straightforward. However, we disallow replacing
constraint triggers or changing the is-constraint property; perhaps
that can be added later, but the complexity versus benefit tradeoff
doesn't look very good.
Also, no special thought is taken here for whether replacing an
existing trigger should result in changes to queued-but-not-fired
trigger actions. We just document that if you're surprised by the
results, too bad, don't do that. (Note that any such pending trigger
activity would have to be within the current session.)
Takamichi Osumi, reviewed at various times by Surafel Temesgen,
Peter Smith, and myself
Continue reading Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.
Recently I've seen case like:
- application had to add column to table.
- application ran ALTER TABLE ADD COLUMN (without default!)
- everything stopped for many MINUTES
Why? How to avoid the problem?
Continue reading How to run short ALTER TABLE without long locking concurrent queries
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 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.
If you worked with certain other (than PostgreSQL) open source database, you might wonder why PostgreSQL doesn't have MERGE, and why UPSERT example in documentation is so complicated.
Well, let's try to answer the question, and look into some alternatives.
Continue reading Why is UPSERT so complicated?
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
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