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

# Juergen.Rose@sag-el.com / 2005-07-22 13:04:27 +0200:
>
>
> > # 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.

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

    Yup, a row-level trigger.

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

    Right. Have you read the manual?

    http://www.postgresql.org/docs/current/static/sql-createrule.html

    "It is important to realize that a rule is really a command
    transformation mechanism, or command macro. The transformation
    happens before the execution of the commands starts. If you actually
    want an operation that fires independently for each physical row,
    you probably want to use a trigger, not a rule. More information
    about the rules system is in Chapter 33."

    Chapter 33 is http://www.postgresql.org/docs/current/static/rules.html

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