Re: Insert into ... Select ... From ... too intelligent transaction

> # Juergen(dot)Rose(at)sag-el(dot)com / 2005-07-21 19:11:04 +0200:
> > I use some updateable views to handle my data (which are amazingly
> > slow), which gives me ultimate flexibility to handle my data.
> >
> > there are some insert rules which use currval() to get the last
> > sequence id for my data which I have to insert.
> >
> > The problem now is, it works fine if I do the statement via normal
> > insert into satatements, even within a transaction block. So far so
> > good. But If I migrate my old data via **Insert into my_new_view
> > Select ... From my_old_table**, Only the last retrieved value of the
> > sequences is used which blows my whole internal logic, because
> > obviously I want to use the current (for that row) and not the last
> > id.
>
> What does the code look like?
>

This is a quite accurate example of what I trying to do, just stripped
down to the bare minimum. Please look at the statements at the end, and
execute them just one after another (test 1-3).

create table olddata(
id int,
lastname varchar(50),
firstname varchar(50)
);

insert into olddata values (1, 'Picard', 'Jean Luc');
insert into olddata values (3, 'Spock', 'Harold');
insert into olddata values (6, 'Zimmerman', 'Doc');
insert into olddata values (9, 'Lefler', 'Robin');
insert into olddata values (10, 'Kirk', 'James T.');

create table neworg(
orgid serial,
legacyid int,
orgname varchar(100)
);

create table newpersons(
persid serial,
orgid int,
lastname varchar(50),
firstname varchar(50)
);

create view v_persons as
select
P.orgid,
O.legacyid,
P.persid,
P.lastname,
P.firstname
from
neworg O,
newpersons P
where
O.orgid = P.orgid;

create or replace rule r_insert_a_organisation as on insert to v_persons
do instead
insert into neworg(
orgid,
legacyid,
orgname)
values (
coalesce(new.orgid, nextval('neworg_orgid_seq')),
new.legacyid,
coalesce(new.lastname, '') || ', ' || coalesce(new.firstname,
'')
);

create or replace rule r_insert_b_persons as on insert to v_persons
do
insert into newpersons(
orgid,
lastname,
firstname)
values (
coalesce(new.orgid, currval('neworg_orgid_seq')),
new.lastname,
new.firstname
);

/* test 1 */
insert into v_persons(legacyid, lastname, firstname) values (11, 'Dax',
'Jadzia');
select * from v_persons;

/* my result:

orgid | legacyid | persid | lastname | firstname
-------+----------+--------+----------+-----------
1 | 11 | 1 | Dax | Jadzia
(1 row)

*/

/* test 2 */
begin;
insert into v_persons(legacyid, lastname, firstname) values (12,
'Bashir', 'Dr.');
insert into v_persons(legacyid, lastname, firstname) values (13, '',
'Odo');
insert into v_persons(legacyid, lastname, firstname) values (14, '',
'Worf');
end;
select * from v_persons;

/* my result:

orgid | legacyid | persid | lastname | firstname
-------+----------+--------+----------+-----------
1 | 11 | 1 | Dax | Jadzia
2 | 12 | 2 | Bashir | Dr.
3 | 13 | 3 | | Odo
4 | 14 | 4 | | Worf
(4 rows)

*/

/* test 3 */
insert into v_persons(legacyid, lastname, firstname) select * from
olddata;
select * from v_persons;

/* my result:

orgid | legacyid | persid | lastname | firstname
-------+----------+--------+-----------+-----------
1 | 11 | 1 | Dax | Jadzia
2 | 12 | 2 | Bashir | Dr.
3 | 13 | 3 | | Odo
4 | 14 | 4 | | Worf
9 | 10 | 5 | Picard | Jean Luc
9 | 10 | 6 | Spock | Harold
9 | 10 | 7 | Zimmerman | Doc
9 | 10 | 8 | Lefler | Robin
9 | 10 | 9 | Kirk | James T.
(9 rows)

*/

And exactly in test 3 you see my problem, it should actually look like

orgid | legacyid | persid | lastname | firstname
-------+----------+--------+-----------+-----------
1 | 11 | 1 | Dax | Jadzia
2 | 12 | 2 | Bashir | Dr.
3 | 13 | 3 | | Odo
4 | 14 | 4 | | Worf
5 | 1 | 5 | Picard | Jean Luc
6 | 3 | 6 | Spock | Harold
7 | 6 | 7 | Zimmerman | Doc
8 | 9 | 8 | Lefler | Robin
9 | 10 | 9 | Kirk | James T.

Why the heck gets the wrong data inserted if it is an int!!!???

I hope somebody will help me out on this, for me this looks very much
like a bug.

Juergen

PS: By the way it is a postgres 7.4.7. on Debian stable