Skip site navigation (1) Skip section navigation (2)

Migrating from 7.2 to 8

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 (view raw or flat)
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


pgsql-admin by date

Next:From: Tom LaneDate: 2005-03-26 18:49:20
Subject: Re: read-only database on CD-ROM?
Previous:From: Dragan MaticDate: 2005-03-26 11:59:18
Subject: read-only database on CD-ROM?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group