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

Re: ALTER TABLE RENAME fix

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Brent Verner <brent(at)rcfile(dot)org>
Cc: pgsql-patches(at)postgresql(dot)org
Subject: Re: ALTER TABLE RENAME fix
Date: 2001-11-11 02:41:23
Message-ID: 17937.1005446483@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-patches
Brent Verner <brent(at)rcfile(dot)org> writes:
> | But we do, because we know whether we're scanning by tgrelid or
> | tgconstrrelid.  

> My brain is not stretching far enough to see this.  Yes, we know
> what we are scanning on, but that stilll doesn't tell us which
> side of the FK/PK relation this relname is on.

Doesn't it?  Maybe I'm the one who's confused.  The RI trigger functions
certainly know which field is which without any searching.

I did:

regression=# CREATE TABLE p_rel (p_id int UNIQUE);
NOTICE:  CREATE TABLE / UNIQUE will create implicit index 'p_rel_p_id_key' for table 'p_rel'
CREATE
regression=# CREATE TABLE f_rel (f_id int REFERENCES p_rel(p_id) ON UPDATE CASCADE);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
regression=# select tgfoid::regproc,tgargs from pg_trigger where tgrelid =
regression-# (select oid from pg_class where relname = 'p_rel');
        tgfoid        |                             tgargs
----------------------+----------------------------------------------------------------
 RI_FKey_noaction_del | <unnamed>\000f_rel\000p_rel\000UNSPECIFIED\000f_id\000p_id\000
 RI_FKey_cascade_upd  | <unnamed>\000f_rel\000p_rel\000UNSPECIFIED\000f_id\000p_id\000
(2 rows)

regression=# select tgfoid::regproc,tgargs from pg_trigger where tgconstrrelid =
regression-# (select oid from pg_class where relname = 'p_rel');
      tgfoid       |                             tgargs
-------------------+----------------------------------------------------------------
 RI_FKey_check_ins | <unnamed>\000f_rel\000p_rel\000UNSPECIFIED\000f_id\000p_id\000
(1 row)

regression=# select tgfoid::regproc,tgargs from pg_trigger where tgrelid =
regression-# (select oid from pg_class where relname = 'f_rel');
      tgfoid       |                             tgargs
-------------------+----------------------------------------------------------------
 RI_FKey_check_ins | <unnamed>\000f_rel\000p_rel\000UNSPECIFIED\000f_id\000p_id\000
(1 row)

regression=# select tgfoid::regproc,tgargs from pg_trigger where tgconstrrelid =
regression-# (select oid from pg_class where relname = 'f_rel');
        tgfoid        |                             tgargs
----------------------+----------------------------------------------------------------
 RI_FKey_noaction_del | <unnamed>\000f_rel\000p_rel\000UNSPECIFIED\000f_id\000p_id\000
 RI_FKey_cascade_upd  | <unnamed>\000f_rel\000p_rel\000UNSPECIFIED\000f_id\000p_id\000
(2 rows)

Hmm, so the same tgargs vector is passed to all the triggers regardless
of which side of the relationship they're on, and it's up to the trigger
itself to know which relname/attname applies to its own table.

I think what this means is that a boolean "is_ri_trigger" classification
isn't good enough.  You need to make the knowledge function have a
three-way return: not an RI trigger, RI trigger for PK relation, or
RI trigger for FK relation.  Then the scan subroutine has to take two
item-number parameters, one telling it which tgarg item to look at for a
PK relation and a different one to look at for an FK relation.

Or maybe some slightly different structure would be cleaner.  But the
point is that you have to be able to decide which item to look at
by some means that doesn't involve checking the relname item.  Else it
doesn't work for self-referential RI constraints (same relname on
both sides).

> AFAICS, given a relname, the only way we can know if it is the PK or
> FK is the number of tuples a known scan returns.

The number of tuples is an implementation artifact and might change;
I don't think we should trust that.  (For example, RI_FKey_noaction_del
and RI_FKey_cascade_upd could theoretically be merged into a single
trigger that pays attention to the event it was called for.)

			regards, tom lane

In response to

Responses

pgsql-patches by date

Next:From: Brent VernerDate: 2001-11-11 03:39:12
Subject: Re: ALTER TABLE RENAME fix
Previous:From: Brent VernerDate: 2001-11-11 02:13:11
Subject: Re: ALTER TABLE RENAME fix

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