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

> # Juergen.Rose@sag-el.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