Обсуждение: ALTER TABLE
============================================================================
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
EXECUTEPROCEDURE "RI_FKey_check_ins" ('fr_fkey', 'fr', 'pr1', 'FULL', 'f_id', 'id');
please take a look at TRIGGERS' arguments
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
EXECUTEPROCEDURE "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
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> I believe these are all the cases I saw of heap_openr with no lock
> and no check in the actual trigger functions (patch to
> hopefully elog(ERROR) instead of crashing attached).
> [ patch snipped ]
We had a discussion about that on 11-July and the consensus seemed to be
that the real problem is heap_open's definition; it's too easy to forget
when you need to check for failure return. I have just committed
changes that split heap_open into two routines: heap_open() now ALWAYS
elogs on failure, regardless of lock mode, while heap_open_nofail() is
what to call if you really want a NULL return on failure. (Likewise for
heap_openr() of course.) I found only about three places in the whole
backend that really wanted the _nofail() case.
Accordingly, this patch is not needed anymore in current sources, though
it'd still be the most convenient fix for 7.0.* series if anyone is
concerned enough to apply it.
A possibly more important issue: why are the RI triggers opening the
referenced rel with NoLock anyway? Doesn't that leave you open to
someone deleting the referenced rel out from under you while you are
working with it? Seems like at minimum you should grab AccessShareLock.
regards, tom lane