Thread: inserting to a multi-table view

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

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?

Thanks,
Mike

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: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 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 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:
> 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'.
>
> 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?
>
> Thanks,
> Mike
>
>

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

On Tue, 17 Jun 2008 12:46:27 -0700,
"Richard Broersma" <richard.broersma@gmail.com> wrote:

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

I've read this thread with great interest as I'm coming to PostgreSQL
from the MS Access world of databases, where one can enter new data into
queries/forms and tables get automatically updated/deleted/inserted into
where expected.

I'm also leaning towards using natural keys where possible and was
wondering how best to create multi-table views that can be
updated/deleted/inserted into.  Therefore, any further insights
following the discussion above would be very helpful.  Particularly, I'm
curious to learn how PostgreSQL database maintainers handle data
entry/modification requiring multi-table queries.  Thanks.


--
Seb

On Sun, Sep 28, 2008 at 5:31 PM, Seb <spluque@gmail.com> wrote:
> I've read this thread with great interest as I'm coming to PostgreSQL
> from the MS Access world of databases, where one can enter new data into
> queries/forms and tables get automatically updated/deleted/inserted into
> where expected.

Well,  I was able to get PostgreSQL Update-able views to work nearly
as well as the update-able queries did in Access.

In the case of update-able joined queries, I would expect that
MS-Access had the advantage since it was using Pessimistic locking on
native Access tables rather than the Optimistic locking that MS-Access
uses on all ODBC linked tables.  As a side note, you'll notice that
MS-Access will not allow update-able queries based on ODBC linked
table like it does on its native tables for this reason.


> I'm also leaning towards using natural keys where possible and was
> wondering how best to create multi-table views that can be
> updated/deleted/inserted into.

Natural Primary key/Foreign key CASCADE UPDATEs don't work well with
update-able views.  Choose one or the other.

> Particularly, I'm
> curious to learn how PostgreSQL database maintainers handle data
> entry/modification requiring multi-table queries.  Thanks.

My opionion is that Multitable update-able views are not safe if you
plan to allow multiple users concurrent access to the view.  Because
of this I scrapped these kinds of views for multiple prepared
statements issued in a serializable level transaction.


--
Regards,
Richard Broersma Jr.

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

On Sun, 28 Sep 2008 21:43:49 -0700,
"Richard Broersma" <richard.broersma@gmail.com> wrote:

[...]

> Well, I was able to get PostgreSQL Update-able views to work nearly as
> well as the update-able queries did in Access.

Would you mind sharing a sample schema?


> As a side note, you'll notice that MS-Access will not allow
> update-able queries based on ODBC linked table like it does on its
> native tables for this reason.

That's right, I did find that out once but didn't know whether it was
due to ODBC limitations or something else.


> Natural Primary key/Foreign key CASCADE UPDATEs don't work well with
> update-able views.  Choose one or the other.

Not sure what you mean; can you please tell more about what doesn't work
well with updateable views what the choice is?


Thanks for the feedback,

--
Seb

On Sun, Sep 28, 2008 at 9:57 PM, Seb <spluque@gmail.com> wrote:

>> Well, I was able to get PostgreSQL Update-able views to work nearly as
>> well as the update-able queries did in Access.
> Would you mind sharing a sample schema?

I'll see what I can do.  I did post some sample schema a while back
but I can't seem of find them in the archive.  I do have some
production tables and views, but there is too much non related
attributes to make a useful example.

>> As a side note, you'll notice that MS-Access will not allow
>> update-able queries based on ODBC linked table like it does on its
>> native tables for this reason.
>
> That's right, I did find that out once but didn't know whether it was
> due to ODBC limitations or something else.

MS-Access Implements Optimistic locking with all ODBC data sources.
The basic differences with an ODBC data source is that MS-Access's Jet
Engine can't put a file lock on it like it can with other file type db
like access, excel, flat files et.al.

Optimistic locking means that every time Access issues an update to
the ODBC server, it includes the all old values of a record (that
ms-access is aware of) in the update statement's where clause.  So if
your MS-Access client was only aware of a stale version of the record,
its update count will be zero, thereby access with throw an exception
saying that the update could not be completed as the underlying table
was changed by another user.


>> Natural Primary key/Foreign key CASCADE UPDATEs don't work well with
>> update-able views.  Choose one or the other.
>
> Not sure what you mean; can you please tell more about what doesn't work
> well with update-able views what the choice is?

This one is kind of hard to explain.  Basically it deals with the
order of operation between Referential Integrity updates versus client
side or update-able view updates.

Lets say your client app or update-able view wants to update a single
row in a view.  However, in addition to changing the fields from each
table, you also want to update the natural primary key.  This sounds
simple but its not.

1) The record changes made on the client application are not instantly
committed and refreshed for each field change that the user makes.
Basically, the whole row is updated with an update statement once when
the user commits the change.

2) The view redirects the update statement to its underlying rules
(usually on rule for each joined table update).  First of all the
primary table fields are changed (lets call it tableA) with the rule
update including its natural primary key.  Lets say the primary key
was changed from 'OLDVALUE' to 'NEWVALUE' on tableA.

3) Now tableB that has a foreign key referencing tableA with its
foreign key set to ON UPDATE CASCADE.  Declarative Referential
Integrity (DRI) such as ON UPDATE CASCADE have a much higher priority
over the PostgreSQL Rule system.  So before the rule system can
perform updates on tableB, tableB has it foreign key changed from
'OLDVALUE' to 'NEWVALUE'.

4) Here is where the problem occurs.  When the next update-able view
rule is executed to update tableB its where clause still thinks that
tableB foreign key is 'OLDVALUE'.  And because 'OLDVALUE' is in the
where clause of the rule's update statement instead of 'NEWVALUE', no
record is found to match and so the remaining field updates fail.

So the end result is that all of tableA updates are successful,
TableB's foreign key is updated by DRI but the rest of the field
updates are not.  So you are left with an inconsistent update from the
perspective of the view.  By the way, this really confuses MS-Access.
It doesn't know what to do when this happens.

That's why I says that "Natural Primary key/Foreign key CASCADE
UPDATEs don't work well with update-able views."


Also, if you have concurrent users on the same updateable view, update
anomolies like this can still occur just from problems with user
concurrent updates.  Thats the reason I decided to abandon join tabled
updateable views.


--
Regards,
Richard Broersma Jr.

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