Getting value from dynamic column in pl/PgSQL triggers?

Every so often, on irc, someone asks how to get value from column that is passed as argument.

This is generally seen as not possible, as pl/PgSQL doesn't have support for dynamic column names.

We can work around it, though. Are the workarounds usable, in terms of performance?

Continue reading Getting value from dynamic column in pl/PgSQL triggers?

Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

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
 
Discussion: https://postgr.es/m/0DDF369B45A1B44B8A687ED43F06557C010BC362@G01JPEXMBYT03

Continue reading Waiting for PostgreSQL 14 – Provide the OR REPLACE option for CREATE TRIGGER.

Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

On 18th of March 2020, Alvaro Herrera committed patch:

Enable BEFORE row-level triggers for partitioned tables
 
... with the limitation that the tuple must remain in the same
partition.
 
Reviewed-by: Ashutosh Bapat
Discussion: https://postgr.es/m/20200227165158.GA2071@alvherre.pgsql

Continue reading Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables

Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.

Another one missed, quite a long time ago, too..:

On 4th of November 2016, Kevin Grittner committed patch:

Implement syntax for transition tables in AFTER triggers.
 
 
This is infrastructure for the complete SQL standard feature.  No
support is included at this point for execution nodes or PLs.  The
intent is to add that soon.
 
As this patch leaves things, standard syntax can create tuplestores
to contain old and/or new versions of rows affected by a statement.
References to these tuplestores are in the TriggerData structure.
C triggers can access the tuplestores directly, so they are usable,
but they cannot yet be referenced within a SQL statement.

Continue reading Waiting for PostgreSQL 10 – Implement syntax for transition tables in AFTER triggers.

Waiting for 9.5 – Event Trigger for table_rewrite

On 7th of December, Simon Riggs committed patch:

Event Trigger for table_rewrite
 
Generate a table_rewrite event when ALTER TABLE
attempts to rewrite a table. Provide helper
functions to identify table and reason.
 
Intended use case is to help assess or to react
to schema changes that might hold exclusive locks
for long periods.
 
Dimitri Fontaine, triggering an edit by Simon Riggs
 
Reviewed in detail by Michael Paquier

Continue reading Waiting for 9.5 – Event Trigger for table_rewrite

Waiting for 9.3 – Event triggers

On 20th of July, Robert Haas committed patch:

Make new event trigger facility actually do something.
 
Commit 3855968f328918b6cd1401dd11d109d471a54d40 added syntax, pg_dump,
psql support, and documentation, but the triggers didn't actually fire.
With this commit, they now do.  This is still a pretty basic facility
overall because event triggers do not get a whole lot of information
about what the user is trying to do unless you write them in C; and
there's still no option to fire them anywhere except at the very
beginning of the execution sequence, but it's better than nothing,
and a good building block for future work.
 
Along the way, add a regression test for ALTER LARGE OBJECT, since
testing of event triggers reveals that we haven't got one.
 
Dimitri Fontaine and Robert Haas

This was preceded (two days earlier) by commit, also by Robert Haas, which stated:

Syntax support and documentation for event triggers.
 
They don't actually do anything yet; that will get fixed in a
follow-on commit.  But this gets the basic infrastructure in place,
including CREATE/ALTER/DROP EVENT TRIGGER; support for COMMENT,
SECURITY LABEL, and ALTER EXTENSION .. ADD/DROP EVENT TRIGGER;
pg_dump and psql support; and documentation for the anticipated
initial feature set.
 
Dimitri Fontaine, with review and a bunch of additional hacking by me.
Thom Brown extensively reviewed earlier versions of this patch set,
but there's not a whole lot of that code left in this commit, as it
turns out.

Continue reading Waiting for 9.3 – Event triggers