PostgreSQL's rules and nextval()/serial problem (very PostgreSQL-specific)

Question

When I use a rewrite rule that splits an insert into one table into inserts to two other tables where one of the inserted values has as default nextval('some_sequence') with the same sequence for both tables, then the inserted default values are different in the two tables. This is probably due to simple text replacement by the rewrite rule. I had hoped instead that the default value would be first resolved and then the same value be written to both tables.

Here an example (as you probably guess, I'm trying to implement specialization/generalization using rules):

-- first and third commands can be skipped if id is defined as serial
create sequence parents_id_seq;
create table Parents(
  id integer default(nextval('parents_id_seq')) primary key,
  type varchar(50) not null check(type in ('Child1', 'Child2')),
  unique (id, type),
  attribute1 varchar(50) not null unique check(length(attribute1) > 0)
);
alter sequence parents_id_seq owned by parents.id;

The data specific to children of the first kind is kept in

create table Partial_Children1(
  id integer default(nextval('parents_id_seq')) primary key,
  type varchar(50) not null check(type = 'Child1'),
  foreign key (id, type) references Parents(id, type),
  attribute2 varchar(50) not null check(length(attribute2) > 0)
);

Next I defined a view Children1 that joins both tables above (I rewrote the view by stating explicitly what PostgreSQL does to define views according to the documentation)

create table Children1(
  id int default(nextval('parents_id_seq')),
  type varchar(50) not null check(type in ('Child1')),
  attribute1 varchar(50) not null check(length(attribute1) > 0),
  attribute2 varchar(50) not null check(length(attribute2) > 0)
);
create rule "_RETURN" as on select to Children1 do instead
  select p.*, c.attribute2
  from Parents p
    join Partial_Children1 c
      on p.id = c.id;

Finally the rewrite rule I'm having problems with:

create rule ct_i_children1 as
  on insert to Children1
  do instead (
    insert into Parents(attribute1, type)
      values(new.attribute1, 'Child1');
    insert into Partial_Children1(attribute2, type)
      values(new.attribute2, 'Child1');
  );

Trying to insert data with

insert into Children1 (attribute1, attribute2)
  values ('a1', 'a2'),
         ('b1', 'b2');

yields the error message

ERROR:  insert or update on table "partial_children1" violates foreign key constraint "partial_children1_id_fkey"
DETAIL:  Key (id,type)=(3,Child1) is not present in table "parents".

A way to solve this is replacing the second insert of the rewrite rule by

insert into Partial_Children1(id, attribute2, type)
  select p.id, new.attribute2, p.type
    from Parents p
    where p.attribute1 = new.attribute1

but this relies on the uniqueness of attribute1, which I don't want to impose. Another solution would be to insert the values first into an temporary table, and then to select twice from there for the insertions into the two tables. But I don't like it because of performance reasons.

Does anyone have another idea how to get the same default values in both tables (just using rules and not triggers)?

Answer 1

Rules will do that for you - they rewrite the query before it's executed.

As long as you have an actual table for the base (Children1), I think you'll be able to accomplish the same thing with a TRIGGER instead of a RULE.

Answer 2

From the docs http://www.postgresql.org/docs/8.4/static/rules.html

It (The Rule system) modifies queries to take rules into consideration, and then passes the modified query to the query planner for planning and execution

so it first rewrites the queries without executing anything.

you can make it work when you do not insert multipe records at once:

create or replace rule ct_i_children1 as
  on insert to Children1
  do instead (
    insert into Parents(id, attribute1, type)
      values(nextval('parents_id_seq'), new.attribute1, 'Child1');
    insert into Partial_Children1(id, attribute2, type)
      values(currval('parents_id_seq'), new.attribute2, 'Child1');
  );

Then you can do:

insert into Children1 (attribute1, attribute2) values ('a1', 'a2');
insert into Children1 (attribute1, attribute2) values ('b1', 'b2');

but not

insert into Children1 (attribute1, attribute2)
  values ('a1', 'a2'),
         ('b1', 'b2');

So you really should not use the rules system with tricky currval() calls.

Additionally take a look at the comments on these pages:

• http://www.postgresql.org/docs/8.2/interactive/rules-update.html
• http://archives.postgresql.org/pgsql-sql/2004-10/msg00195.php
• http://archives.postgresql.org/pgsql-general/2009-06/msg00278.php

Another tip: the support at the postgresql mailing list is as excellent as the database engine itself!

And by the way: do your know that postgresql has support for inheritance out-of-the-box?

• http://www.postgresql.org/docs/8.4/interactive/ddl-inherit.html

Summary: you should use triggers or avoid multiple row inserts!