If have some problem with updating `pg_relcheck' entries. Who can help ?
PostgreSQL version:
CVS of Jan 03 with some additions for my CREATE ERROR TRIGGER project (announced on this list). Among those is the
followingchange in pg_relcheck:
NameData rcname; text rcbin; text rcsrc;
+ int2 rcerrhandlers;/* # of ON ERROR triggers,
+ * currently on 0 or 1 is supported */} FormData_pg_relcheck; rcerrhandlers is the number of
errorhandlers for a CHECK constraint, i.e. the number of procedures to be called if a CHECK constraint fails. Error
handlersare stored in pg_triggers like normal triggers. They reference CHECK constraints by OID in the same way as
normaltriggers reference relations by OID. For this to work I have still to add an OID to pg_relcheck, but the
currentquestion does not depend on this.
Error handlers are created by the following statement:
=# create table mytab (arg text CHECK (length(arg)>10));
CREATE
=# CREATE ERROR TRIGGER ehtest on CONSTRAINT CHECK mytab_arg FOR EACH ROW EXECUTE PROCEDURE __proc('arg');
CREATE
In executing the CREATE ERROR TRIGGER command I have to update the entry
of the CHECK constraint in pg_relcheck and ro add an entry to pg_trigger.
The problem I have is related to updating the pg_relcheck entry.
For this I use the following code:
/* Grab an exclusive lock on the pg_relcheck relation */rcrel = heap_openr(RelCheckRelationName, RowExclusiveLock);
/* * Create the scan key. We need to match the name of the CHECK * constraint. */ScanKeyEntryInitialize(&key, 0,
Anum_pg_relcheck_rcname, F_NAMEEQ, PointerGetDatum(constrName));/* * Begin scanning the heap */rcscan =
heap_beginscan(rcrel,0, SnapshotNow, 1, &key);
/* * We take the first CHECK constraint we can find. */
if (! HeapTupleIsValid(rctup = heap_getnext(rcscan, 0))){ elog(ERROR,"CreateErrorHandler: CHECK constraint \"%s\" does
notexist", constrName);}
/** * Copy the tuple to be able to end the scan. */rctup = heap_copytuple(rctup);
heap_endscan(rcscan);
[snip]
/** * update the error handler counter of the constraint */((Form_pg_relcheck) GETSTRUCT(rctup))->rcerrhandlers =
found+ 1;simple_heap_update(rcrel, &rctup->t_self, rctup); /** * update the indices, too */
[snip]heap_freetuple(rctup);
heap_close(rcrel, RowExclusiveLock);
pg_relcheck before the update is done:
# select * from pg_relcheck where rcname='mytab_arg';
rcrelid | rcname | rcbin .. | rcsrc .. | rcerrhandlers 16570 | mytab_arg | ({ EXPR :typeOid 16 :opType op :oper {
OPER:opno 521 :opid 147 :opresulttype 16 } :args ({ EXPR :typeOid 23 :opType func :oper { FUNC :funcid 1317 :functype
23} :args ({ VAR :varno 1 :varattno 1 :vartype 25 :vartypmod -1 :varlevelsup 0 :varnoold 1 :varoattno 1})} { CONST
:consttype23 :constlen 4 :constbyval true :constisnull false :constvalue 4 [ 10 0 0 0 ] })}) | (length(arg) > 10) |
0
(1 row)
pg_relcheck after the update is done:
=# select * from pg_relcheck where rcname='mytab_arg';rcrelid | rcname | rcbin | rcsrc |
rcerrhandlers
---------+-----------+---------------+--------------------+--------------- 16564 | mytab_arg | ({ EXPR :typ |
(length(arg)> 10) | 0
(1 row)
Furthermore when I abort the transaction (with `elog') after the code
segment shown above is executed, the entry in pg_relcheck even vanishes !
I'm really puzzled.
--
Holger Krug
hkrug@rationalizer.com