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

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

> 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