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

# Juergen.Rose@sag-el.com / 2005-07-22 09:10:01 +0200:
> > # 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.

> 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 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.

    PostgreSQL did exactly what you told it to do. RULEs *rewrite
    queries*, which means the INSERT INTO ... SELECT gets transformed to
    something like

    insert into neworg( orgid, legacyid, orgname)
    select
        coalesce(new.orgid, nextval('neworg_orgid_seq')),
        new.id as legacyid,
        coalesce(new.lastname, '') || ', ' || coalesce(new.firstname, '')
    from olddata new;

    insert into newpersons ( orgid, lastname, firstname)
    select
        coalesce(new.orgid, currval('neworg_orgid_seq')),
        new.lastname,
        new.firstname
    from olddata new;

    and this is run once, not for every row. IOW, you'll have this
    problem with any multi-row inserts.

--
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991