Porting application with rules and triggers from PG 7.4.x to 8.1.3

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi!

I'm currently porting a rather complex application from
PostgreSQL 7.4.x to 8.1.3 and I'm having problems with
changed semantics of the trigger functionality.

The problem is quite complex and I'm trying to describe
the functionality involved as detailled as necessary.
If you have any question please feel free to ask!

My application is using lots of temporal tables(*).
Each temporal table has two additional columns "from" and "to"
which store the time period where a single row was "valid".
In this concept, a row which still contains valid data has a
value of 'infinity' in its "to" column.

Example:

CREATE TABLE mwdb.t_ug
(
id serial NOT NULL,
from mwdb.d_pit NOT NULL DEFAULT now(),
to mwdb.d_pit NOT NULL DEFAULT ('infinity'::d_pit)::d_pit,
name mwdb.d_name NOT NULL,
code mwdb.d_code NOT NULL,
kommentar mwdb.d_comment
);

For each temporal table there is also a "current view" which
shows all rows from the temporal table where "to" = 'infinity'
(i.e. all rows which contain data which is still valid at the
current PIT)

Example:

CREATE OR REPLACE VIEW mwdb.vc_ug AS
SELECT t_ug.id,
t_ug.name,
t_ug.code,
t_ug.kommentar
FROM mwdb.t_ug
WHERE (t_ug.to = 'infinity');

Most database operations in the application are done against
these "current views".

I'm using rules, triggers and several PL/pgSQL functions
to enforce constraints like primary keys and foreign keys,
because the "standard" constraints of a relational database
do not work for temporal tables due to implicit temporal
semantics.

Each "current view" has attached "INSERT", "UPDATE" and
"DELETE" rules so that the user can work with the current
view "tables" as with any normal, non-temporal table. All
modifications to the current view are recorded in the
temporal table by use of PL/pgSQL functions which are
called by these rules.

There is never a row changed or deleted in a temporal table,
every change to any row in the current view is recorded as
a new row in the temporal table.

As an example I'm showing the function "func_ug_update"
which is called from the UPDATE rule on view "vc_ug",
which is the "current view" for temporal table "t_ug":

CREATE OR REPLACE RULE rule_ug_update AS
ON UPDATE TO mwdb.vc_ug DO INSTEAD
SELECT mwdb.func_ug_update(old.id::mwdb.d_rid, new.name, new.code, new.kommentar) AS func_ug_update;

CREATE OR REPLACE FUNCTION mwdb.func_ug_update(mwdb.d_rid, mwdb.d_name, mwdb.d_code, mwdb.d_comment)
RETURNS int4 AS $$
DECLARE
old_id ALIAS FOR $1;
new_name ALIAS FOR $2;
new_code ALIAS FOR $3;
new_kommentar ALIAS FOR $4;
retval integer;
now_pit d_pit;

BEGIN
now_pit := (now())::d_pit;

UPDATE t_ug SET
to=now_pit
WHERE
id = old_id AND
to = 'infinity';

IF FOUND=true
THEN
INSERT INTO t_ug (id, from, name, code, kommentar)
VALUES (old_id, now_pit, new_name, new_code, new_kommentar);

END IF;

GET DIAGNOSTICS retval = ROW_COUNT;
return retval;

END;
$$ LANGUAGE plpgsql;

As you can see this function "closes" the row from t_ug which
has "to" set to 'infinity' by storing the current PIT into
the "to" column. Then it inserts a new row with the updated
data and sets the "from" column to the current PIT (the insert
operation implicitly sets the "to" column to 'infinity')
This implements a "sequenced valid-time, closed-open interval"
temporal table concept.

So far, so good. But there is also a foreign key relationship
between table "t_ug" and table "t_pns". Table "t_pns" also is
a temporal table and contains a column "ug" which references
column "id" in table "t_ug" as a foreign key.

CREATE TABLE mwdb.t_pns
(
id serial NOT NULL,
from mwdb.d_pit NOT NULL DEFAULT now(),
to mwdb.d_pit NOT NULL DEFAULT ('infinity'::d_pit)::d_pit,
ug mwdb.d_rid NOT NULL,
name mwdb.d_name NOT NULL,
code mwdb.d_code NOT NULL,
kommentar mwdb.d_comment
);

The "temporal table foreign key constraint" says: For each row
in table "t_pns" with column "ug" set to N and column "to" set
to 'infinity' there must always be one row in table "t_ug" with
column "id" set to N and column "to" set to 'infinity'

This constraint is enforced by a special trigger function which
is attached to table "t_ug" as follows:

CREATE TRIGGER trigger_fk_ug_pns
AFTER UPDATE OR DELETE
ON mwdb.t_ug
FOR EACH ROW
EXECUTE PROCEDURE mwdb.func_fk_temporal_trigger('t_pns', 'ug', 't_ug', 'id');

The trigger function itself is rather complicated because it
is generic for all temporal tables but it implements the
"temporal table foreign key constraint" as mentioned above.
The function looks as follows:

CREATE OR REPLACE FUNCTION mwdb.func_fk_temporal_trigger()
RETURNS trigger AS
$$
DECLARE
referer_tab text;
referer_col text;
referenced_tab text;
referenced_col text;
stmt varchar(4000);
result record;

BEGIN
referer_tab := TG_ARGV[0];
referer_col := TG_ARGV[1];
referenced_tab := TG_ARGV[2];
referenced_col := TG_ARGV[3];

stmt := ' SELECT id FROM ' || quote_ident(referer_tab);
stmt := stmt || ' WHERE ' || quote_ident(referer_tab) || '.to = \'infinity\'';
stmt := stmt || ' AND ' || quote_ident(referer_tab) || '.' || quote_ident(referer_col) || ' IS NOT NULL';
stmt := stmt || ' AND NOT EXISTS (SELECT id FROM ' || quote_ident(referenced_tab);
stmt := stmt || ' WHERE ' || quote_ident(referer_tab) || '.' || quote_ident(referer_col) || ' = ' || quote_ident(referenced_tab) || '.' || quote_ident(referenced_col);
stmt := stmt || ' AND ' || quote_ident(referenced_tab) || '.to = \'infinity\')';

FOR result in EXECUTE stmt LOOP
RAISE EXCEPTION 'temporal table referential integrity violation - key referenced from %<id=%>.% not found in %.%', referer_tab, result, referer_col, referenced_tab, referenced_col;
END LOOP;

RETURN new;
END;

$$
LANGUAGE plpgsql;

With PostgreSQL 7.4.x this did work fine. The execution of the
trigger was deferred until the end of the UPDATE rule. But this
seemingly has changed with PostgreSQL 8.

With PostgreSQL 8 I get the following error:

numbis=> select * from vc_ug;
id | name | code | kommentar
- ------+------------------+--------+---------------------
10 | UG1 | ug1 |
3893 | Test-UG | testug | Just a test
(2 Zeilen)

Zeit: 0,378 ms

numbis=> select * from vc_pns where ug=3893;
id | ug | name | code | kommentar
- ------+------+----------+---------+-------------
3894 | 3893 | Test-PNS | testpns | just a test
(1 Zeile)

Zeit: 0,575 ms

numbis=> update vc_ug set kommentar='Another test' where id=3893;
ERROR: temporal table referential integrity violation - key referenced from t_pns<id=(3894)>.ug not found in t_ug.id
KONTEXT: SQL statement "UPDATE t_ug SET to= $1 WHERE id = $3 AND to = 'infinity'"

Here the trigger is fired immediately after the first UPDATE
statement in func_ug_update() and before the INSERT statement,
which follows the UPDATE and which is needed to bring the
temporal tables into a consistent state again.

I found the following statement in the release notes for PostgreSQL 8.0
in section "Migration to version 8.0" which seem to describe this change:

"Nondeferred AFTER triggers are now fired immediately after
completion of the triggering query, rather than upon finishing
the current interactive command. This makes a difference when
the triggering query occurred within a function: the trigger is
invoked before the function proceeds to its next operation."

So, the change seemingly is intentional, but it leads to the
problem I described above.

Now, what can I do to resolve this problem?
Is there another way to defer the execution of the trigger
to the end of func_ug_update()?
How can I get the functionality of an "deferred AFTER trigger"
again with PostgreSQL 8?

Any idea?
Any help is appreciated!

- - andreas

(*) FYI: My implementation of temporal tables is based on the
books "Developing Time-Oriented Database Applications in SQL"
by Richard T. Snodgrass and "Temporal Data and the Relational
Model" by C.J. Date, Hugh Darwen and Nikis A. Lorentzos

- --
Andreas Haumer | mailto:andreas(at)xss(dot)co(dot)at
*x Software + Systeme | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org

iD8DBQFESMjoxJmyeGcXPhERAgi6AJ0V0M1v0EkTVHUTvPN9W9YioLlznwCgtQmE
OnaVQafp6Dy+Q6NMCwxoHok=
=Xp84
-----END PGP SIGNATURE-----