ALTER TABLE

I can confirm that this is a bug, and crashes in the current development
tree.

============================================================================
POSTGRESQL BUG REPORT TEMPLATE
============================================================================

Your name : Gena Gurchonok
Your email address : gena@rt.mipt.ru

System Configuration
---------------------
Architecture (example: Intel Pentium): intel Celeron 500
Operating System (example: Linux 2.0.26 ELF): 2.2.16 ELF
PostgreSQL version (example: PostgreSQL-7.0): PostgreSQL-7.0.2
Compiler used (example: gcc 2.8.0): pgcc-2.91.66

Please enter a FULL description of your problem:
------------------------------------------------

When I renaming table using ALTER TABLE,
it doesn't change table name in RI triggers. This results in backend
crash due to invalid arguments for RI_Check trigger procedure.

Please describe a way to repeat the problem. Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

CREATE TABLE "pr1" (
"id" int4 ,
PRIMARY KEY ("id")
);

CREATE TABLE "fr" (
"f_id" int4 NOT NULL,
CONSTRAINT fr_fkey FOREIGN KEY (f_id)
REFERENCES pr1(id)
MATCH FULL
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
INITIALLY IMMEDIATE
);

insert into pr1 values(1);
insert into fr values(1);

alter table fr rename to fr2;

delete from pr1;

As the result we have:
pqReadData() -- backend closed the channel unexpectedly.
This probably means the backend terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

pg_dump gives

\connect - gena
CREATE TABLE "pr1" (
"id" int4 NOT NULL,
PRIMARY KEY ("id")
);
CREATE TABLE "fr2" (
"f_id" int4 NOT NULL
);
COPY "pr1" FROM stdin;
1
\.
COPY "fr2" FROM stdin;
1
\.
CREATE CONSTRAINT TRIGGER "fr_fkey" AFTER DELETE ON "pr1" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_restrict_del" ('fr_fkey', 'fr', 'pr1', 'FULL', 'f_id', 'id');
CREATE CONSTRAINT TRIGGER "fr_fkey" AFTER UPDATE ON "pr1" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_cascade_upd" ('fr_fkey', 'fr', 'pr1', 'FULL', 'f_id', 'id');
CREATE CONSTRAINT TRIGGER "fr_fkey" AFTER INSERT OR UPDATE ON "fr2" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE "RI_FKey_check_ins" ('fr_fkey', 'fr', 'pr1', 'FULL', 'f_id', 'id');

please take a look at TRIGGERS' arguments

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Gena Gurchonok <gena@rt.mipt.ru> writes:

When I renaming table using ALTER TABLE,
it doesn't change table name in RI triggers. This results in backend
crash due to invalid arguments for RI_Check trigger procedure.

Yes, this is a known bug. Two bugs actually, first being that the
trigger definitions don't track the rename (they should probably be
storing OID not relname, although that would complicate dump/restore).
Second is that the table opens in the triggers themselves neglect to
check for open failure :-(, which results in crashes later on.

regards, tom lane

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:

Yes, this is a known bug. Two bugs actually, first being that the
trigger definitions don't track the rename (they should probably be
storing OID not relname, although that would complicate dump/restore).

I do plan on trying to move all the table/attribute storage to OIDs.
I actually don't think it'll be too hard on dump/restore, since they
should be able to get rewritten as an ALTER TABLE ADD CONSTRAINT
rather than the CREATE CONSTRAINT TRIGGER, so it should just be
a matter of turning the oids back into rel/attrib names at dump time.

That's doable, certainly, but I think it will be a little bit fragile.
How will pg_dump know which arguments of which triggers need to be
processed in this fashion? Some ugly hardwired assumptions will be
needed AFAICS.

I think this ties into the discussions we've had on-and-off about not
storing enough metadata. It'd be better if the FK constraints were
stored explicitly in some system table or other, in a form designed
for inspection, and not solely stored in a form designed for execution.
It's the same kind of problem we have with SERIAL columns...

regards, tom lane