Hello,
Our problem is as follows:
Given the following tables and insertions using multiple inheritance:
----
CREATE TABLE test1 (id serial, t1 text,PRIMARY KEY(id));
CREATE TABLE test2 (id serial, t2 text,PRIMARY KEY(id));
CREATE TABLE test () INHERITS(test1,test2);
INSERT INTO test2 (t2) values('t2.1');
INSERT INTO test (t1,t2) values('t1.1', 't2.2');
----
, this will generate the following database contents:
----
select * from test2*;
id | t2
----+------
1 | t2.1
1 | t2.2
(2 rows)
select * from test1*;
id | t1
----+------
1 | t1.1
(1 row)
select * from test;
id | t1 | t2
----+------+------
1 | t1.1 | t2.2
(1 row)
As you can see from the output this will generate a sequence error in test2.
If we had first inserted a value in test1, and then insert values into test, the
problem won't occur.
, but as soon a value is put explicitly into test2 the sequence defined over
test2.id will be confused and will start counting from scratch. (See the include
queries below.)
Our question is whether this is a bug in postgres, or that we are using the
inheritance mechanism in an incorrect manner.
Please help us out!!
----
CREATE TABLE test1 (id serial, t1 text,PRIMARY KEY(id));
CREATE TABLE test2 (id serial, t2 text,PRIMARY KEY(id));
CREATE TABLE test () INHERITS(test1,test2);
INSERT INTO test1 (t1) values('t1.2');
INSERT INTO test (t1,t2) values('t1.2', 't2.1');
INSERT INTO test2 (t2) values('t2.3');
select * from test2*;
id | t2
----+------
1 | t2.3
2 | t2.1
(2 rows)
select * from test1*;
id | t1
----+------
1 | t1.2
2 | t1.2
(2 rows)
select * from test;
id | t1 | t2
----+------+------
2 | t1.2 | t2.1
(1 row)
Greetings,
Ivo Klerkx and Wouter Tijhuis
