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?

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

# 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

# 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

# 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

# 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