inserting to a multi-table view

On Mon, Jun 16, 2008 at 8:49 PM, Michael Shulman <shulman@mathcamp.org> wrote:

Hi,

This feels like a very basic question but I cannot figure it out.
Suppose I have two tables and a view that combines their data:

CREATE TABLE person
(person_id SERIAL PRIMARY KEY,
...);

CREATE TABLE student
(student_id SERIAL PRIMARY KEY,
person_id INTEGER REFERENCES person,
...)

CREATE VIEW studentinfo AS
SELECT * FROM person JOIN student USING person_id;

I want to be able to do INSERTs on "studentinfo" and have rows created
in both "person" and "student". This requires first inserting into
"person", capturing the "person_id" of the resulting row, and using it
to insert into "student". This seems as though it must be a common
situation.

I am happy to use either rules or triggers, but I can't figure
out how to do it with either. I can write a rule that does two
INSERTs but I don't know how to capture the id resulting from the
first insert and put it into the second. I can write a trigger
function that does the right thing, with 'INSERT ... RETURNING
person_id INTO ...', but Postgres will not let me add an INSERT
trigger to a view; it says 'ERROR: "studentinfo" is not a table'.

Got a short example of what you've tried so far?

Michael Shulman wrote:

I want to be able to do INSERTs on "studentinfo" and have rows created
in both "person" and "student". This requires first inserting into
"person", capturing the "person_id" of the resulting row, and using it
to insert into "student". This seems as though it must be a common
situation.

Have you considered using table inheritance to solve this?

It has some limitations, and I've never seen the need myself, but it
sounds like it might fit your needs.

http://www.postgresql.org/docs/8.3/static/ddl-inherit.html

--
Craig Ringer

On Mon, Jun 16, 2008 at 10:27 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

I want to be able to do INSERTs on "studentinfo" and have rows created
in both "person" and "student". This requires first inserting into
"person", capturing the "person_id" of the resulting row, and using it
to insert into "student". This seems as though it must be a common
situation.

Have you considered using table inheritance to solve this?

No, I don't think table inheritance will help. My actual situation is
somewhat more complicated: the view takes data from more than two
tables with a many-to-one rather than one-to-one relationship. For
instance, consider tables "person", "address", and "phone", with a
view "person_with_contact_info" that joins a person with their primary
address and phone number, while inserting to the view should insert a
person along with an address and phone number. In that case there is
no table that can inherit from the other to solve the problem.

Mike

On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

I can write a trigger
function that does the right thing, with 'INSERT ... RETURNING
person_id INTO ...', but Postgres will not let me add an INSERT
trigger to a view; it says 'ERROR: "studentinfo" is not a table'.

Got a short example of what you've tried so far?

create function ins_st() returns trigger as $$
declare
id integer;
begin
insert into person (...) values (NEW....) returning person_id into id;
insert into student (person_id, ...) values (id, NEW....);
end;
$$ language plpgsql;

create trigger ins_student before insert on studentinfo
for each row execute procedure ins_st();

ERROR: "studentinfo" is not a table

Mike

On Mon, Jun 16, 2008 at 8:32 PM, Michael Shiulman <shulman@mathcamp.org> wrote:

No, I don't think table inheritance will help. My actual situation is
somewhat more complicated: the view takes data from more than two
tables with a many-to-one rather than one-to-one relationship. For
instance, consider tables "person", "address", and "phone", with a
view "person_with_contact_info" that joins a person with their primary
address and phone number, while inserting to the view should insert a
person along with an address and phone number. In that case there is
no table that can inherit from the other to solve the problem.

Ya, I agree. Postgresql inheritance is really just fancy horizontal
partitioning with a built-in UNION ALL. As far as I know, it still
doesn't support referential integrity (i.e. foreign keys from the
sub-tables). Building your own vertically partitioned schema will
fix many of the referential integrity problems, but at the expense of
opening your self up for view update anomoloies (I wished that the
postgresql update rules where executed as serializable transactions,
that way if one of the joined tables in the view was updated during
your change, it would though an exception rollback your update instead
of writing over the other persons work.)

Anyway, here is a link discussing a generalized vertical partitioned
view. Perhaps it can give you some idea to get yourself rolling.
http://archives.postgresql.org/pgsql-general/2006-12/msg01119.php

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Mon, Jun 16, 2008 at 10:24 PM, Richard Broersma
<richard.broersma@gmail.com> wrote:

On Mon, Jun 16, 2008 at 8:32 PM, Michael Shiulman <shulman@mathcamp.org> wrote:
Anyway, here is a link discussing a generalized vertical partitioned
view. Perhaps it can give you some idea to get yourself rolling.
http://archives.postgresql.org/pgsql-general/2006-12/msg01119.php

Oops, there is one other thing to bring up. If your clients
front-ends use a form of Optimistic locking, they will probably balk
at update-able views on vertically partitioned joined tables.

However, in the case of using ODBC, there was a work-around that
solved the problem of optimistic locking. However, you are still
faced with the problem of update anomalies caused by concurrent
updates on your base tables.

http://archives.postgresql.org/pgsql-odbc/2006-12/msg00029.php

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Michael Shulman wrote:

On Mon, Jun 16, 2008 at 10:03 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

I can write a trigger
function that does the right thing, with 'INSERT ... RETURNING
person_id INTO ...', but Postgres will not let me add an INSERT
trigger to a view; it says 'ERROR: "studentinfo" is not a table'.

Got a short example of what you've tried so far?

create function ins_st() returns trigger as $$
declare
id integer;
begin
insert into person (...) values (NEW....) returning person_id into id;
insert into student (person_id, ...) values (id, NEW....);
end;
$$ language plpgsql;

create trigger ins_student before insert on studentinfo
for each row execute procedure ins_st();

ERROR: "studentinfo" is not a table

Mike

The only way I could find to make this work is to use a rule and wrap
the inner "insert returning" in a function.

create or replace function newperson (studentinfo) returns setof person as
$$
declare
arec person%rowtype;
begin
for arec in
insert into person (foo,bar) values ($1.foo,$1.bar) returning *
loop
-- insert into address (...) values (arec.person_id, $1....)
-- insert into phone (...) values (arec.person_id, $1....)
return next arec;
end loop;
return;
end;
$$
language plpgsql volatile;
create rule atest as on insert to studentinfo do instead (
insert into student (person_id) select (select person_id from
newperson(new));
);

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

Michael,

You can try the following:

CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
DO INSTEAD
(
INSERT INTO person ...;
INSERT INTO student(person_id,...) VALUES
(currval('person_person_id_seq'),...);
);

The currval() function gives you the value of the sequence associated to
your field. The name of the sequence should be the one in my example.

Philippe Gregoire
Information Manager
www.boreal-is.com

Michael Shulman wrote:

On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <kgore4@une.edu.au> wrote:

The only way I could find to make this work is to use a rule and wrap the
inner "insert returning" in a function.

Thanks, this works! Although it feels like something of a hack;
shouldn't there be a more elegant solution?

Also, I don't understand why

create rule atest as on insert to studentinfo do instead (
insert into student (person_id) select (select person_id from newperson(new));
);

is necessary; what is wrong with

create rule atest as on insert to studentinfo do instead (
insert into student (person_id) select person_id from newperson(new);
);

? (Other than the evident fact that it doesn't work; the error
message "function expression in FROM cannot refer to other relations
of same query level" is not illuminating to me.)

Additionally, is there a reason why you put one of the inserts in the
function and the other in the rule? Why not have the function do both
inserts and then the rule just invoke the function?

Mike

On Tue, Jun 17, 2008 at 7:56 AM, Philippe Grégoire
<philippe.gregoire@boreal-is.com> wrote:

CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
DO INSTEAD
(
INSERT INTO person ...;
INSERT INTO student(person_id,...) VALUES
(currval('person_person_id_seq'),...);
);

I initially thought of this, but discounted it because if the original
insert query tried to set person_id directly (instead of getting it from
the default sequence) the supplied value would have to be discarded. I
have any plans to do anything of the sort, though, and I suppose the user
has little right to expect to be able to do such a thing safely anyway.
So perhaps this is the simplest solution; thanks.

Mike

On Tue, Jun 17, 2008 at 12:24 AM, Richard Broersma
<richard.broersma@gmail.com> wrote:

Anyway, here is a link discussing a generalized vertical partitioned
view. Perhaps it can give you some idea to get yourself rolling.
http://archives.postgresql.org/pgsql-general/2006-12/msg01119.php

Thank you very much for pointing this out! I am somewhat disturbed by
the example in that thread of a "partially executed update" resulting
from the obvious way to write an update rule for a view. I guess I
need to be calling a function to do it instead, but this again seems
somewhat kludgy. Perhaps rules are not as wonderful as they seemed
when I first encountered them.

(I wished that the postgresql update rules where executed as
serializable transactions, that way if one of the joined tables in
the view was updated during your change, it would though an
exception rollback your update instead of writing over the other
persons work.)

This is also disturbing! I'm not completely clear on what sort of
overwriting can happen, though; could you give an example or a link?

Are there any plans to fix these problems? In any case, it seems as
though these sorts of caveats should appear in the documentation.

Mike

On Tue, Jun 17, 2008 at 8:34 AM, Michael Shulman <shulman@mathcamp.org> wrote:

Thank you very much for pointing this out! I am somewhat disturbed by
the example in that thread of a "partially executed update" resulting
from the obvious way to write an update rule for a view. I guess I
need to be calling a function to do it instead, but this again seems
somewhat kludgy. Perhaps rules are not as wonderful as they seemed
when I first encountered them.

Ya, there are a couple of things that I've learned while using rule.
1) Update-able views are only 100% safe when the view is based on a
single base table.
2) Update-able views based on joined tables require you to use
surrogate primary keys. I.E. if your primary key were natural, and
there was a possibility that it could be changed, the resulting
updates would break. Since on UPDATE CASCADE Referential Integrity
will cascade to primary key update before the rule is fired. (when the
rule is fired, it will still be using the old primary key before the
cascade occurred.

This is also disturbing! I'm not completely clear on what sort of
overwriting can happen, though; could you give an example or a link?

Lets say you had a view based one the following select:

SELECT T1.att1, T1.att2, (...), T[n-1].att[n-1], T[n].att[n]
FROM T1
JOIN T2 ON T1.id = T2.id
JOIN (...) ON T1.id = (...).id
JOIN T[n-1] ON T1.id = T[n-1].id
JOIN T[n] ON ON T1.id = T[n].id;

While your individual update rules are firing for each of your tables
from T1 thru T[n] to change your OLD row to NEW. Another client could
also at the same time be updating any of the other tables before and
after your update Rules take affect. The net result is that, some of
what you've changed could over write what the other client commited.
And some of what you've commited could be over written by what the
other client wrote. The end result is that the view's virtual "row"
appears to be left in an inconsistant state. This is a case where the
PostgreSQL inheritance has an advantage. Since you are dealing with
an actual table record, MVCC unsures that only one of the changes will
be commited, not parts of both.

Are there any plans to fix these problems? In any case, it seems as
though these sorts of caveats should appear in the documentation.

I think the reason that it isn't in the documentation is that the
problem is really a design problem and not really a PostgreSQL rule
problem. As soon as you split a table in to two using a form of
vertical partitioning, you've introduce the opportunity for update
anomalies to occur when dealing with concurrent database operations.
Since it is by design that the table is split, it is therefore up to
the designer to choose a method to ensure that consistant concurrent
updates are achieved.

Basically what you want to achieve is something like:

begin:
Select for update table T1 where id = old.id;
Select for update table T2 where id = old.id;
Select for update table (...) where id = old.id;
Select for update table T[n-1] where id = old.id;
Select for update table T[n] where id = old.id;
if all the needed row lock are aquired, then
begin the updates
else rollback
commit;

I also recall a discussion for allowing trigger to be attached to
views. However, IIRC, Tom Lane indicated that UPDATE triggers would
not be added to views since the possibility for update anomalies would
still exist.

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

On Tue, Jun 17, 2008 at 11:50 AM, Richard Broersma
<richard.broersma@gmail.com> wrote:

While your individual update rules are firing for each of your tables
from T1 thru T[n] to change your OLD row to NEW. Another client could
also at the same time be updating any of the other tables before and
after your update Rules take affect. The net result is that, some of
what you've changed could over write what the other client commited.
And some of what you've commited could be over written by what the
other client wrote. The end result is that the view's virtual "row"
appears to be left in an inconsistant state.

Got it; thanks.

Basically what you want to achieve is something like:

begin:
Select for update table T1 where id = old.id;
Select for update table T2 where id = old.id;
Select for update table (...) where id = old.id;
Select for update table T[n-1] where id = old.id;
Select for update table T[n] where id = old.id;
if all the needed row lock are aquired, then
begin the updates
else rollback
commit;

Would it be possible to actually do something like this in an update
rule? You couldn't write the "begin/commit", but it seems that you
wouldn't need to either, since the UPDATE command invoking the rule
will be wrapped in its own begin/commit (automatic or explicit).

Mike

On Tue, Jun 17, 2008 at 12:34 PM, Michael Shulman <shulman@mathcamp.org> wrote:

Would it be possible to actually do something like this in an update
rule? You couldn't write the "begin/commit", but it seems that you
wouldn't need to either, since the UPDATE command invoking the rule
will be wrapped in its own begin/commit (automatic or explicit).

Thats a good question. I've never tried it. and since then, I gotten
away from using update-able view. In my case, I like using Natural
Primary keys so update-able views wouldn't work for me any more. :o)

--
Regards,
Richard Broersma Jr.

Visit the Los Angles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

Michael Shulman wrote:

On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <kgore4@une.edu.au> wrote:

The only way I could find to make this work is to use a rule and wrap the
inner "insert returning" in a function.

Thanks, this works! Although it feels like something of a hack;
shouldn't there be a more elegant solution?

The more elegant statements don't work.

The ideal solution would be if some variant of
insert into student (person_id) values ((insert into person (...)
values (...) returning person_id));
worked.

Also, I don't understand why

create rule atest as on insert to studentinfo do instead (
insert into student (person_id) select (select person_id from newperson(new));
);

is necessary; what is wrong with

create rule atest as on insert to studentinfo do instead (
insert into student (person_id) select person_id from newperson(new);
);

? (Other than the evident fact that it doesn't work; the error
message "function expression in FROM cannot refer to other relations
of same query level" is not illuminating to me.)

Got it in 1 - it doesn't work. I'm sure there's a good reason for the
error message that someone who knows more about rules can explain.

Additionally, is there a reason why you put one of the inserts in the
function and the other in the rule? Why not have the function do both
inserts and then the rule just invoke the function?

If the rule does the insert into student, then the return to the
application looks like a normal insert (e.g. you can check rows affected).

klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789
Fax: 02 6773 3266
EMail: kgore4@une.edu.au

On Tue, Jun 17, 2008 at 10:15 AM, Michael Shulman <shulman@mathcamp.org> wrote:

<philippe.gregoire@boreal-is.com> wrote:

CREATE RULE studentinro_insert AS ON INSERT TO studentinfo
DO INSTEAD
(
INSERT INTO person ...;
INSERT INTO student(person_id,...) VALUES
(currval('person_person_id_seq'),...);
);

This does, however, break if someone tries to insert more than one row
at a time.

INSERT INTO studentinfo (...) VALUES (...), (...);

produces

ERROR: duplicate key value violates unique constraint.

I expect that what happens is that first all the inserts into person
happen, then all the inserts into student happen, and all the latter
ones try to use the same currval.

Mike

Thanks to everyone who responded to this thread; although I have not
gotten a complete solution I have learned a lot about how rules and
triggers work. One particular question that is still unanswered:

On Mon, Jun 16, 2008 at 9:49 PM, Michael Shulman <shulman@mathcamp.org> wrote:

The Postgres manual:
http://www.postgresql.org/docs/8.3/static/rules-triggers.html
says "a trigger that is fired on INSERT on a view can do the same as
a rule: put the data somewhere else and suppress the insert in the
view." So what do I need to do to make an INSERT trigger on a view?

This quote from the manual implies to me that it is possible to make a
trigger fire on INSERT on a view. But postgres won't let me do this,
and some things I've read elsewhere on the Internet imply that it is
impossible. Is the manual wrong? Or am I reading it wrong? Or is it
possible to make a trigger fire on INSERT on a view?

Mike

"Michael Shulman" <shulman@mathcamp.org> writes:

http://www.postgresql.org/docs/8.3/static/rules-triggers.html
says "a trigger that is fired on INSERT on a view can do the same as
a rule: put the data somewhere else and suppress the insert in the
view." So what do I need to do to make an INSERT trigger on a view?

This quote from the manual implies to me that it is possible to make a
trigger fire on INSERT on a view. But postgres won't let me do this,
and some things I've read elsewhere on the Internet imply that it is
impossible. Is the manual wrong?

The manual is wrong. Although this text is so ancient that it probably
was true when written. I don't offhand know when the check against
installing triggers on views was put in, but this section of the manual
predates the time when we started to draw a hard distinction between
views and tables.

There's been some recent talk about allowing ON INSERT triggers on
views, which would make this statement correct again, but nothing's
been done about it yet. It's not entirely clear how useful such a
thing would be if we couldn't support UPDATE/DELETE too, and as stated
here those cases are a lot harder.

regards, tom lane

On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore <kgore4@une.edu.au> wrote:

The only way I could find to make this work is to use a rule and wrap the
inner "insert returning" in a function.

create or replace function newperson (studentinfo) returns setof person as
$$
declare
arec person%rowtype;
begin
for arec in
insert into person (foo,bar) values ($1.foo,$1.bar) returning *
loop
-- insert into address (...) values (arec.person_id, $1....)
-- insert into phone (...) values (arec.person_id, $1....)
return next arec;
end loop;
return;
end;
$$
language plpgsql volatile;
create rule atest as on insert to studentinfo do instead (
insert into student (person_id) select (select person_id from
newperson(new));
);

Here is another question: why does "newperson" have to be a table
function (returning SETOF)? It seems to work fine for me to do

create or replace function newperson (studentinfo) returns integer as $$
declare
pid integer;
begin
insert into person (foo,bar) values ($1.foo,$1.bar) returning
person_id into pid;
return pid;
end; $$ language plpgsql;

create rule atest as on insert to studentinfo do instead
insert into student (person_id, baz) values (newperson(new), new.baz);

Mike