Re: Insert into ... Select ... From ... too intelligent transaction - Mailing list pgsql-general

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

So I can't actually solve this problem, but what I could do would be to
not create views, but tables with rules, and put some trigger on the
tables?

Further if I understand you right, the rules are transformed actually to
two different queries which are executed one after another and not row
by row?

Thanks for the enlightment so far
Juergen