Re: Using views and MS access via odbc

I've got a table, view, and rules as below. The permissions are set up in
such a way that I can use it just fine as myself via psql. When I try to
access the data using an ms access interface via odbc, I get the first
record in the view, but any attempts to go to other records cause ms access
to tell me that they've been deleted (it's lying though, because I can still
see them through the psql interface).

I thought I had seen a mention on one of the web pages that said some
interfaces don't handle views very well, but can't seem to find that page
again (in hopes that it also had some suggestions to get around the
problem).

Here are my questions:
1) Is this a known problem?
2) Am I doing something wrong?
3) Are there work arounds?

-ron

create table log (
id serial not null,
whenentered timestamp,
username name not null default user,
class varchar(10),
entry varchar
);

create view myview as select id,whenentered,class,entry from log where
username=user;

create rule ins_mylog as on insert to myview do instead insert into log
(whenentered,class,entry) values (now(), NEW.class, NEW.entry);

-- create the rule that will actually do an update on the right record
create rule upd_mylog as on update to myview do instead update log set
whenentered=NEW.whenentered, class=NEW.class, entry=NEW.entry where
id=OLD.id;

-- create a rule that satisfies postgres' need for completeness
create rule upd_mylog0 as on update to myview do instead nothing;

create rule del_mylog0 as on delete to myview do instead nothing;
create rule del_mylog as on delete to myview do instead delete from log
where id=OLD.id;

Ron Snyder wrote:
>
> I've got a table, view, and rules as below. The permissions are set up in
> such a way that I can use it just fine as myself via psql. When I try to
> access the data using an ms access interface via odbc, I get the first
> record in the view, but any attempts to go to other records cause ms access
> to tell me that they've been deleted (it's lying though, because I can still
> see them through the psql interface).

Are you using 7.2 ?
Your settings probably worked well under 7.1 but
doesn't in 7.2 due to the following change in
tcop/postgres.c.

/*
* It is possible that the original query was removed due to
* a DO INSTEAD rewrite rule. In that case we will still have
* the default completion tag, which is fine for most purposes,
* but it may confuse clients if it's INSERT/UPDATE/DELETE.
* Clients expect those tags to have counts after them (cf.
* ProcessQuery).
*/
if (strcmp(commandTag, "INSERT") == 0)
commandTag = "INSERT 0 0";
else if (strcmp(commandTag, "UPDATE") == 0)
commandTag = "UPDATE 0";
.
.

* UPDATE 0 * means no tuple was updated.

regards,
Hiroshi Inoue
http://w2422.nsk.ne.jp/~inoue/

Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> Your settings probably worked well under 7.1 but
> doesn't in 7.2 due to the following change in
> tcop/postgres.c.

AFAIR, there is only a visible change of behavior for
INSERT/UPDATE/DELETE queries, not for SELECTs. So I don't think
this change explains Ron's complaint.

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> > Your settings probably worked well under 7.1 but
> > doesn't in 7.2 due to the following change in
> > tcop/postgres.c.
>
> AFAIR, there is only a visible change of behavior for
> INSERT/UPDATE/DELETE queries, not for SELECTs. So I don't think
> this change explains Ron's complaint.

For a view a_view

UPDATE a_view set ... where xxxxx;
returns UPDATE 0 in any case in 7.2.

The psqlodbc driver understands that no row was updated
and returns the info to the upper application if requested.
MS access( and I) think there's no such case other than
the row was changed or deleted after it was SELECTed.
Note that MS access doesn't issue any SELECT commands
to check the optimistic concurrency of the row. The where
clause of the UPDATE command contains *a_item = old_value*
for all items to check the optimisitic concurrency at the
same time.

regards,
Hiroshi Inoue

> -----Original Message-----
> From: Tom Lane
>
> Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp> writes:
> > Your settings probably worked well under 7.1 but
> > doesn't in 7.2 due to the following change in
> > tcop/postgres.c.
>
> AFAIR, there is only a visible change of behavior for
> INSERT/UPDATE/DELETE queries, not for SELECTs. So I don't think
> this change explains Ron's complaint.

If you'd not like to change the behavior, I would change it, OK ?

regards,
Hiroshi Inoue

"Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
> If you'd not like to change the behavior, I would change it, OK ?

To what? I don't want to simply undo the 7.2 change.

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
> > If you'd not like to change the behavior, I would change it, OK ?
>
> To what? I don't want to simply undo the 7.2 change.

What I'm thinking is the following makeshift fix.
I expect it solves Ron's case though I'm not sure.
Returning UPDATE 0 seem to make no one happy.

regards,
Hiroshi Inoue

*** postgres.c.orig Thu Feb 28 08:17:01 2002
--- postgres.c Sat May 4 22:53:03 2002
***************
*** 805,811 ****
if (DebugLvl > 1)
elog(DEBUG, "ProcessQuery");

! if (querytree->originalQuery)
{
/* original stmt can override default tag string */
ProcessQuery(querytree, plan, dest, completionTag);
--- 805,811 ----
if (DebugLvl > 1)
elog(DEBUG, "ProcessQuery");

! if (querytree->originalQuery || length(querytree_list) == 1)
{
/* original stmt can override default tag string */
ProcessQuery(querytree, plan, dest, completionTag);

"Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
> If you'd not like to change the behavior, I would change it, OK ?
>>
>> To what? I don't want to simply undo the 7.2 change.

> What I'm thinking is the following makeshift fix.
> I expect it solves Ron's case though I'm not sure.
> Returning UPDATE 0 seem to make no one happy.

Agreed, that doesn't seem like it's going over well. Let's see, you
propose returning the tag if there is only one replacement query, ie,
we had just one DO INSTEAD rule. [ thinks... ] I guess the only thing
that bothers me about this is the prospect that the returned tag is
completely different from what the client expects. For example,
consider a rule like ON UPDATE DO INSTEAD INSERT INTO history_table...
With your patch, this would return an "INSERT nnn nnn" tag, which'd
confuse a client that expects an "UPDATE nnn" response. (This is one
of the issues that prompted changing the behavior to begin with.)

Would it be reasonable to allow the rewritten query to return a tag
only if (a) it's the only query, per your patch AND (b) it's the same
query type as the original, unrewritten query?

regards, tom lane

> -----Original Message-----
> From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
>
> "Hiroshi Inoue" <Inoue(at)tpf(dot)co(dot)jp> writes:
> > If you'd not like to change the behavior, I would change it, OK ?
> >>
> >> To what? I don't want to simply undo the 7.2 change.
>
> > What I'm thinking is the following makeshift fix.
> > I expect it solves Ron's case though I'm not sure.
> > Returning UPDATE 0 seem to make no one happy.
>
> Agreed, that doesn't seem like it's going over well. Let's see, you
> propose returning the tag if there is only one replacement query, ie,
> we had just one DO INSTEAD rule. [ thinks... ] I guess the only thing
> that bothers me about this is the prospect that the returned tag is
> completely different from what the client expects. For example,
> consider a rule like ON UPDATE DO INSTEAD INSERT INTO history_table...
> With your patch, this would return an "INSERT nnn nnn" tag, which'd
> confuse a client that expects an "UPDATE nnn" response.

Is it worse than returning "UPDATE 0" ?
Unfortunately "UPDATE 0" never means the result is unknown
but clearly means no rows were affected. It can never be safe
to return "UPDATE 0".

regards,
Hiroshi Inoue