Horton

Thread

  1. ALTER TABLE

    Gena Gurchonok <gena@rt.mipt.ru> — 2000-08-03T10:26:39Z

    ============================================================================
                         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

  2. Re: ALTER TABLE

    Bruce Momjian <pgman@candle.pha.pa.us> — 2000-08-03T13:17:16Z

    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

  3. Re: ALTER TABLE

    Tom Lane <tgl@sss.pgh.pa.us> — 2000-08-03T15:52:20Z

    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