Skip site navigation (1) Skip section navigation (2)

Fixing handling of constraint triggers

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: pgsql-hackers(at)postgreSQL(dot)org
Subject: Fixing handling of constraint triggers
Date: 2010-01-17 17:29:55
Message-ID: 22331.1263749395@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I want to do something about the open item discussed in this thread:
http://archives.postgresql.org/message-id/20090811111446.GA25965@depesz.com

The right way to handle that, IMO, is to create pg_constraint rows for
triggers created via CREATE CONSTRAINT TRIGGER.  Then,
AfterTriggerSetState can initially search pg_constraint to identify
which constraint is being targeted.  Aside from allowing it to throw a
more understandable error for non-deferrable index constraints, this
will greatly simplify its search logic, which is a mess right now.

What seems to be needed in detail is:

pg_constraint.contype gains an additional possible value,
CONSTRAINT_TRIGGER = 't'.

We can drop pg_trigger.tgconstrname (and the index on it) and
pg_trigger.tgisconstraint.  Instead we'll want an index on
pg_trigger.tgconstraint so that we can cheaply search pg_trigger
by constraint OID.

Because CREATE CONSTRAINT TRIGGER will now create a pg_trigger row
with nonzero tgconstraint, it is no longer possible to use "tgconstraint
is nonzero" as a proxy for "system-generated trigger".  This is a
problem for pg_dump in particular, which won't know which triggers it
actually needs to dump.  I think the best fix is to add a boolean
column "tgisinternal" to flag system-generated triggers.

Normally, a trigger associated with a constraint has an internal
dependency on the pg_constraint entry.  For CREATE CONSTRAINT TRIGGER
it'll be the other way around --- pg_constraint internally depends
on pg_trigger --- since you're supposed to use DROP TRIGGER not DROP
CONSTRAINT to remove the assemblage.

AFAICS the only user-visible change in behavior from prior versions
will be that the system will complain if you try to create a constraint
trigger that has the same name as an existing constraint of another type
on the same table.  This doesn't seem like a big problem in practice,
and in any case it's appropriate since a conflict would make it unclear
which constraint SET CONSTRAINTS is meant to apply to.

Thoughts, objections?

			regards, tom lane

Responses

pgsql-hackers by date

Next:From: Pavel StehuleDate: 2010-01-17 19:04:09
Subject: Re: quoting psql varible as identifier
Previous:From: Jan UrbaƄskiDate: 2010-01-17 16:33:40
Subject: Re: xpath improvement suggestion

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group