PostgreSQL with serial fields , rule and wrong currval / sequence

Question

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 ?!

What you learned is don't use rules. My experience is they are non-deterministic. Instead of wasting time going down the rabbit hole to figure out what happened, use the trigger solution and move on. — Adrian Klaver
– Adrian Klaver, Commented Nov 26, 2020 at 3:34

Laurenz Albe · Accepted Answer · 2020-11-26 04:46:43Z

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.

Collectives™ on Stack Overflow

PostgreSQL with serial fields , rule and wrong currval / sequence

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related