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

BUG #5894: Rules' behaviour when SERIAL data types are used

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

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2011-02-18 17:35:13
Subject: Re: postgresql 9.0.3: parallel restore fails with comments on indices
Previous:From: Tom LaneDate: 2011-02-18 14:48:26
Subject: Re: ROLLBACK, SAVEPOINT, COMMIT throwing error in transaction block.

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