From: | Rigmor Ukuhe <rigmor(dot)ukuhe(at)finestmedia(dot)ee> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Migrating from 7.2 to 8 |
Date: | 2005-03-26 12:08:15 |
Message-ID: | 424550AF.1040807@finestmedia.ee |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Hi,
I have problem with migrating our system to PG 8.
problem is with rules and triggers. Basically i want to insert row to
table 'a1', its primary key has default value
nextval('"REO_ID_seq"'::text).
I have a rule that must insert row to another table 'a2' using
currval('"REO_ID_seq"'::text). And i have trigger that should enforce
referential integrity between two tables.
CREATE CONSTRAINT TRIGGER "fk_a1_id" AFTER INSERT OR UPDATE ON "a1" FROM
"a2" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE PROCEDURE
"RI_FKey_check_ins" ('fk_a1_id', 'a1', 'a2', 'UNSPECIFIED', 'id', 'id');
CREATE RULE "rule_a1_INSERT" AS ON INSERT TO a1 DO INSERT INTO a2
("name", "id") VALUES ('REO_HOUSE'::"varchar",
int4(currval('"REO_ID_seq"'::text)));
These rule and trigger create statements are taken from dump script. (if
i load this dump back to 7.2 then everything is working).
In PG 7.2 it worked nicely, but in PG8 i must create trigger with
DEFERRABLE INITIALLY DEFERRED to get code to work, otherwise it
complains about missing key in table a2.
Can anybody point out if i am missing something? Now when i got it to
work in pg 8 i ask myself, why this code is even working on pg 7.2?
--
Rigmor Ukuhe
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2005-03-26 18:49:20 | Re: read-only database on CD-ROM? |
Previous Message | Dragan Matic | 2005-03-26 11:59:18 | read-only database on CD-ROM? |