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
Reviewed-by: Ashutosh Bapat
Continue reading Waiting for PostgreSQL 13 – Enable BEFORE row-level triggers for partitioned tables
This question appeared couple of times on irc, so I figured I can do a blogpost about it.
Continue reading How to make sure there is always at least one “sub" record?
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.
Some time ago someone on irc asked about creating fast counters for something (banners I think).
I talked with her (him?) about it, but figured, I can as well write a blogpost, so others can use it too.
Continue reading Incrementing counters in database
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
On 11th of December, Peter Eisentraut committed patch:
PL/Perl: Add event trigger support
From: Dimitri Fontaine <dimitri@2ndQuadrant.fr>
Continue reading Waiting for 9.4 – PL/Perl: Add event trigger support
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
Continue reading Waiting for 9.3 – Event triggers
On 25th of January, Alvaro Herrera committed patch:
Add pg_trigger_depth() function
This reports the depth level of triggers currently in execution, or zero
if not called from inside a trigger.
No catversion bump in this patch, but you have to initdb if you want
access to the new function.
Author: Kevin Grittner
Continue reading Waiting for 9.2 – Trigger Depth
Some time ago I wrote about getting fast pagination. While fast, it had some problems which made it unusable for some. Specifically – you couldn't get page count, and easily jump to page number N.
I did some thinking on the subject, and I think I found a way to make it all work. Quite fast. And with not big overhead. Let me show you.
Continue reading Pagination with fixed order
On 10th of October, Tom Lane committed patch by Deal Rasheed, which adds triggers on views:
Support triggers ON views.
This patch adds the SQL-standard concept OF an INSTEAD OF TRIGGER, which
IS fired instead OF performing a physical INSERT/UPDATE/DELETE. The
TRIGGER FUNCTION IS passed the entire OLD AND/OR NEW ROWS OF the VIEW,
AND must figure OUT what TO do TO the underlying TABLES TO implement
the UPDATE. So this feature can be used TO implement updatable views
USING TRIGGER programming STYLE rather than rule hacking.
IN passing, this patch corrects the names OF SOME COLUMNS IN the
information_schema.triggers VIEW. It seems the SQL committee renamed
them somewhere BETWEEN SQL:99 AND SQL:2003.
Dean Rasheed, reviewed BY Bernd Helmle; SOME additional hacking BY me.
Continue reading Waiting for 9.1 – triggers on views