From: | "Piergiorgio Buongiovanni" <piergiorgio(dot)buongiovanni(at)netspa(dot)it> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5894: Rules' behaviour when SERIAL data types are used |
Date: | 2011-02-18 15:47:24 |
Message-ID: | 201102181547.p1IFlOsE056574@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5894
Logged by: Piergiorgio Buongiovanni
Email address: piergiorgio(dot)buongiovanni(at)netspa(dot)it
PostgreSQL version: 8.4.4
Operating system: RedHat Centos
Description: Rules' behaviour when SERIAL data types are used
Details:
We are experiencing a problem on using rules and serial data types with
PostgreSQL.
We are trying to align two tables in two different schemas through rules and
the main table has a column of data type SERIAL.
The case is the following:
CREATE table business.prova
(
iSId SERIAL,
cName varchar,
CONSTRAINT _prova_PK PRIMARY KEY (iSId)
);
CREATE table l10n.Prova
(
iSId integer,
cName varchar,
CONSTRAINT _Prova_PK PRIMARY KEY (iSId),
CONSTRAINT _Prova_FK FOREIGN KEY (iSId) REFERENCES business.Prova (iSId)
);
Now we create a rule in order to align l10n.prova when a row is inserted in
business.prova:
CREATE OR REPLACE RULE _Prova_Ins AS ON INSERT
TO business.Prova
DO ALSO INSERT INTO l10n.Prova VALUES (NEW.iSId, NEW.cName);
We are now ready to insert a value in business.Prova and we expect to see a
copy of that row in l10n.Prova, so we execute the following statement:
INSERT INTO business.Prova (cName) VALUES ('Prova_2');
We obtain the following error message:
ERROR: insert or update on table "prova" violates foreign key constraint
"_prova_fk"
DETAIL: Key (isid)=(384) is not present in table "prova".
********** Errore **********
ERROR: insert or update on table "prova" violates foreign key constraint
"_prova_fk"
Stato SQL: 23503
Dettaglio: Key (isid)=(384) is not present in table "prova".
To analyze the problem we have dropped the constraint _prova_fk on
l10n.Prova table; After this we can re-execute the previous statement and
this time we have no errors. If we now look at the two tables we found the
following situation:
SELECT * from business.Prova;
385;"Prova_2"
SELECT * from l10n.Prova;
386;"Prova_2"
Why the value of the iSId column is different?
it seems that a new sequence value is used when the INSERT in l10n.Prova
occurs.
We tried the same SQL statement on PostgreSQL 9.0 and we obtain the same
behaviour.
Is there a limit in the use of rules with SERIAL data types? This is not
clear from your documentation.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2011-02-18 17:35:13 | Re: postgresql 9.0.3: parallel restore fails with comments on indices |
Previous Message | Tom Lane | 2011-02-18 14:48:26 | Re: ROLLBACK, SAVEPOINT, COMMIT throwing error in transaction block. |