Tag: triggers

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

Waiting for 9.2 – Trigger Depth

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

Waiting for 9.1 – triggers on views

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

Test driven development for PostgreSQL

I have a mixed love/hate relationship with tests.

I hate writing them. I hate remembering to add them when I'm in the zone, and application code is flowing freely from the tips of my fingers.

But when I do add them, I absolutely love the ability to twist and replace the most core innards of application, and be able to tell that at least the sanity check of the code passes.

I love them even more when they prevent me for repeating some mistake/bug – i.e. when there is a bug, and I have tests (which clearly failed, as they didn't catch the bug), I add test for this specific bug, so I know that in future it will not happen again.

For a long time I've been fan of stored procedures (or functions) – of course not for everything, but where it makes sense.

Continue reading Test driven development for PostgreSQL

Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL

Dynamic updates of fields in NEW in PL/pgSQL

Today, on #postgresql on IRC, strk asked about updating fields in NEW record, in plpgsql, but where name of the field is in variable.

After some time, he sent his question to hackers mailing list. And he got prompt reply that it's not possible.

Well, I dare to disagree.

Continue reading Stupid tricks – Dynamic updates of fields in NEW in PL/pgSQL

Waiting for 8.5 – conditional triggers

On 20th of November Tom Lane committed patch by Takahiro Itagaki which adds interesting functionality:

ADD a WHEN clause TO CREATE TRIGGER, allowing a BOOLEAN expression TO be
checked TO determine whether the TRIGGER should be fired.
 
FOR BEFORE triggers this IS mostly a matter OF spec compliance; but FOR AFTER
triggers it can provide a noticeable performance improvement, since queuing OF
a DEFERRED TRIGGER event AND re-fetching OF the ROW(s) at END OF statement can
be short-circuited IF the TRIGGER does NOT need TO be fired.
 
Takahiro Itagaki, reviewed BY KaiGai Kohei.

Continue reading Waiting for 8.5 – conditional triggers

Waiting for 8.5 – TRIGGERS on columns

On 14th of October Tome Lane committed new patch from Itagaki Tahahiro:

Log Message:
-----------
Support SQL-compliant triggers ON COLUMNS, ie fire ONLY IF certain COLUMNS
are named IN the UPDATE's SET list.
 
Note: the schema of pg_trigger has not actually changed; we've just started
TO USE a COLUMN that was there ALL along.  catversion bumped anyway so that
this commit IS included IN the history OF potentially interesting changes
TO system catalog contents.
 
Itagaki Takahiro

Continue reading Waiting for 8.5 – TRIGGERS on columns