The following bug has been logged online:
Bug reference: 5894
Logged by: Piergiorgio Buongiovanni
Email address: piergiorgio.buongiova...@netspa.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.
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
