Message-ID: <445108CF.702@xss.co.at>
Date: Thu, 27 Apr 2006 20:09:19 +0200
From: Andreas Haumer <andreas@xss.co.at>
Organization: xS+S
User-Agent: Thunderbird 1.5.0.2 (X11/20060420)
MIME-Version: 1.0
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-sql@postgresql.org
Subject: Re: Porting application with rules and triggers from PG 7.4.x
References: <200604212131.k3LLVQ613099@candle.pha.pa.us>
In-Reply-To: <200604212131.k3LLVQ613099@candle.pha.pa.us>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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

Hi!

Sorry for the late response, but I'm quite busy and I wanted
to test this before replying...

Bruce Momjian schrieb:
> Tom Lane wrote:
>> Andreas Haumer <andreas@xss.co.at> writes:
>>> How can I get the functionality of an "deferred AFTER trigger"
>>> again with PostgreSQL 8?
>> Use CREATE CONSTRAINT TRIGGER.  The manual is fairly negative about this
>> but I don't actually foresee it going away any time soon.
> 

With PostgreSQL 8.x, CREATE CONSTRAINT TRIGGER indeed seems the way to go.

I have now converted all my triggers used for temporal table
referential integrity checking from (example)

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');

(as used with PostgreSQL 7.x)

to (example)

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

(for PostgreSQL 8.x)

The documentation on "CREATE CONSTRAINT TRIGGER" really is not
very extensive, so I cannot say if this really is the right way
to use it, but it seems to work as far as I have tested. I still
do not have a very confident feeling, though...

> Do we need to update the manual?
> 
Yes, please!
If CREATE CONSTRAINT TRIGGER is the only way to solve this
particular problem, it would be very good to have a more
elaborate documentation about it. Currently the documentation
mostly says "not intended for general use". Some real world
examples in chapter 33 ("Triggers") would be nice, too
(If you want you can even use the example from my previous
post, which seems to be a typical application for deferred
triggers... :-)

Thank you all!

- - andreas

- --
Andreas Haumer                     | mailto:andreas@xss.co.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.2 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFEUQjOxJmyeGcXPhERAl32AJ9n7SJgudu5AhdT0AX98FdalyrTowCfTojE
fD+uh7lID845Hi6TPqe/3rM=
=3Pu4
-----END PGP SIGNATURE-----