title: PostgreSQL's rules and nextval()/serial problem (very PostgreSQL-specific)
body: <p>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.</p>
  
  <p>Here an example (as you probably guess, I'm trying to implement specialization/generalization using rules):</p>
  
  <pre><code>-- 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) &gt; 0)
  );
  alter sequence parents_id_seq owned by parents.id;
  </code></pre>
  
  <p>The data specific to children of the first kind is kept in</p>
  
  <pre><code>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) &gt; 0)
  );
  </code></pre>
  
  <p>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)</p>
  
  <pre><code>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) &gt; 0),
    attribute2 varchar(50) not null check(length(attribute2) &gt; 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;
  </code></pre>
  
  <p>Finally the rewrite rule I'm having problems with:</p>
  
  <pre><code>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');
    );
  </code></pre>
  
  <p>Trying to insert data with</p>
  
  <pre><code>insert into Children1 (attribute1, attribute2)
    values ('a1', 'a2'),
           ('b1', 'b2');
  </code></pre>
  
  <p>yields the error message</p>
  
  <pre><code>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".
  </code></pre>
  
  <p>A way to solve this is replacing the second insert of the rewrite rule by</p>
  
  <pre><code>insert into Partial_Children1(id, attribute2, type)
    select p.id, new.attribute2, p.type
      from Parents p
      where p.attribute1 = new.attribute1
  </code></pre>
  
  <p>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.</p>
  
  <p>Does anyone have another idea how to get the same default values in both tables (just using rules and <strong>not</strong> triggers)?</p>