[BUGS] Problems renaming referencing column
(sorry if it's a dup) In 7.4.3, if I rename a column which references another table, constraint trigger fails on update or delete from main table. There are a couple of similar (and about rename table itself) reports for 7.0, 7.1 (as Tom Lane said, rename table is fixed in 7.2), but I see no more reports since 2001. Here's a simple reproducible example: [EMAIL PROTECTED] CREATE TABLE master (k integer NOT NULL PRIMARY KEY) WITHOUT OIDS; NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "master_pkey" for table "master" CREATE TABLE [EMAIL PROTECTED] CREATE TABLE slave (ref integer REFERENCES master (k)) WITHOUT OIDS; CREATE TABLE [EMAIL PROTECTED] INSERT INTO master VALUES (1); INSERT 0 1 [EMAIL PROTECTED] INSERT INTO master VALUES (2); INSERT 0 1 [EMAIL PROTECTED] DELETE FROM master WHERE k = 1; DELETE 1 [EMAIL PROTECTED] ALTER TABLE slave RENAME ref TO k; ALTER TABLE [EMAIL PROTECTED] UPDATE master SET k = 2 where k = 2; ERROR: table "slave" does not have column "ref" referenced by constraint "$1" [EMAIL PROTECTED] DELETE FROM master WHERE k = 2; ERROR: table "slave" does not have column "ref" referenced by constraint "$1" However triggers themselves look good after rename: [EMAIL PROTECTED] \d slave Table "public.slave" Column | Type | Modifiers +-+--- k | integer | Foreign-key constraints: "$1" FOREIGN KEY (k) REFERENCES master(k) [EMAIL PROTECTED] SELECT * from pg_trigger WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname = 'slave'); tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs -++++---++--+---+--++-++ 77304 | RI_ConstraintTrigger_77307 | 1644 | 21 | t | t | $1 | 77300 | f| f | 6 || $1\000slave\000master\000UNSPECIFIED\000k\000k\000 (1 row) [EMAIL PROTECTED] SELECT * from pg_trigger WHERE tgrelid = (SELECT oid FROM pg_class WHERE relname = 'master'); tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs -++++---++--+---+--++-++ 77300 | RI_ConstraintTrigger_77309 | 1655 | 17 | t | t | $1 | 77304 | f| f | 6 || $1\000slave\000master\000UNSPECIFIED\000k\000k\000 77300 | RI_ConstraintTrigger_77308 | 1654 | 9 | t | t | $1 | 77304 | f| f | 6 || $1\000slave\000master\000UNSPECIFIED\000k\000k\000 (2 rows) The problem goes away after re-creating the foreign key: [EMAIL PROTECTED] ALTER TABLE slave DROP CONSTRAINT "$1"; ALTER TABLE [EMAIL PROTECTED] ALTER TABLE slave ADD CONSTRAINT "$1" FOREIGN KEY (k) REFERENCES master(k); ALTER TABLE [EMAIL PROTECTED] DELETE FROM master WHERE k = 2; DELETE 1 -- Fduch M. Pravking ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] Problems renaming referencing column
On Sat, 17 Jul 2004, Alexander M. Pravking wrote: > (sorry if it's a dup) > > In 7.4.3, if I rename a column which references another table, > constraint trigger fails on update or delete from main table. I think this probably has to do with the fact that the plan gets cached. If I close the connection and restart it, the constraint appears to work. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] Replace function BUG
7.4.2 gives it like this:
testbed=# select replace('test %%400%% result',
'result', 'ok');
replace
-
test %%400%% ok
(1 row)
testbed =# select replace('test %400% result',
'result', 'ok');
replace
---
test %400% ok
(1 row)
Ted
-- Nicolas Bottarini <[EMAIL PROTECTED]> wrote:
> In postgreSQL 7.3.2 when I execute the following
> query
>
> select replace('test %400% result', 'result', 'ok');
>
>
>
> it returns "test % ok"
>
>
>
> And If I Execute:
>
> select replace('test %%400%% result', 'result',
> 'ok');
>
> it returns the correct result: "test %400% ok"
>
>
>
> If this is a known BUG please tell me because I need
> to fix this.
>
>
>
> Thanks
>
>
>
> Nicolas Bottarini
>
>
>
>
__
Do you Yahoo!?
Vote for the stars of Yahoo!'s next ad campaign!
http://advision.webevents.yahoo.com/yahoo/votelifeengine/
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [BUGS] Problems renaming referencing column
"Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > In 7.4.3, if I rename a column which references another table, > constraint trigger fails on update or delete from main table. > The problem goes away after re-creating the foreign key: Actually all you have to do is start a fresh backend. The problem is that the "ALTER TABLE slave" fails to force an update of the backend's relcache entry for "master". AFAICS this problem has always been there. Certainly your test case yields a failure of some type in every release back to 7.0 ... regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [BUGS] Problems renaming referencing column
"Alexander M. Pravking" <[EMAIL PROTECTED]> writes: > In 7.4.3, if I rename a column which references another table, > constraint trigger fails on update or delete from main table. The following patch (against 7.4.*) appears to fix this problem. regards, tom lane Index: tablecmds.c === RCS file: /cvsroot/pgsql-server/src/backend/commands/tablecmds.c,v retrieving revision 1.91 diff -c -r1.91 tablecmds.c *** tablecmds.c 13 Oct 2003 22:47:15 - 1.91 --- tablecmds.c 17 Jul 2004 17:16:36 - *** *** 1534,1539 --- 1534,1553 CatalogUpdateIndexes(tgrel, tuple); + /* +* Invalidate trigger's relation's relcache entry so that other +* backends (and this one too!) are sent SI message to make them +* rebuild relcache entries. (Ideally this should happen +* automatically...) +* +* We can skip this for triggers on relid itself, since that +* relcache flush will happen anyway due to the table or column +* rename. We just need to catch the far ends of RI relationships. +*/ + pg_trigger = (Form_pg_trigger) GETSTRUCT(tuple); + if (pg_trigger->tgrelid != relid) + CacheInvalidateRelcache(pg_trigger->tgrelid); + /* free up our scratch memory */ pfree(newtgargs); heap_freetuple(tuple); ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
