CODEWITHSUNDEEP
postgresqltriggersrules
siga0984
siga0984

Reputation: 243

PostgreSQL with serial fields , rule and wrong currval / sequence

Usually insert records into a log table based on CRUD events with PotsgreSQL requires (at least) one trigger and one trigger function. I tried to make it easier, using Postgres RULES. And it almost work, except for the scenario below:

create table mydata ( somedata char(4) , id1 bigserial );
create table mylog ( logid bigint , op char(1), id2 bigserial );
create rule mydata_ilog as on insert to mydata do insert into mylog( logid , op ) values ( new.id1 ,'i' );
insert into mydata( somedata ) values ('0001');

The main table and log table have a serial field, and I expect -- after insert one record into mydata -- to have one record in mydata with id1=1, and one record in mylog, with logid=1 ( as the id of mydata record), and id2=1... At mydata, id1=1, right ... ay mylog, id2 is 1, right ... but at mylog, logid field is 2, and the database sequence created to increment the id1 serial field is 2 ...

select currval('mydata_id1_seq'); -- return 2 ?!
select last_value from mydata_id1_seq; -- return 2 ?!
select id1 from mydata -- return 1 ok 
select id2 from mydata -- return 1 ok 
select logid from mylog -- return 2 ?!

I already solved this with a trigger and a trigger function, works fine. But, is there any reason to PostgreSQL do this ?!

Upvotes: 0

Views: 189

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247260

Look at the execution plan:

EXPLAIN (VERBOSE, COSTS OFF) INSERT INTO mydata (somedata) VALUES ('0001');
                                                 QUERY PLAN                                                 
------------------------------------------------------------------------------------------------------------
 Insert on myschema.mydata
   ->  Result
         Output: '0001'::character(4), nextval('mydata_id1_seq'::regclass)
 
 Insert on myschema.mylog
   ->  Result
         Output: nextval('mydata_id1_seq'::regclass), 'i'::character(1), nextval('mylog_id2_seq'::regclass)
(7 rows)

A rule rewrites a query, using the same expression that gets used for mydata.id for the second statement, so you have the nextval call in both statements.

Rules for data modifying queries are difficult to get right, and you just stepped into a typical trap. Use triggers.

Upvotes: 1

Related Questions