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

The following bug has been logged online:

Bug reference:      5894
Logged by:          Piergiorgio Buongiovanni
Email address:      piergiorgio.buongiovanni@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.

"Piergiorgio Buongiovanni" <piergiorgio.buongiovanni@netspa.it> writes:
> 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.
> ...
> 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);


By and large, rules don't work very well with commands involving
volatile functions like nextval().  That's because a rule is a macro
and the macro expansion can result in multiple calls of the volatile
function.

Best advice is to use a trigger, not a rule, for propagating changes
from one table to another.

            regards, tom lane