Thread: Re: [GENERAL] Using views and MS access via odbc
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@tpf.co.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@sss.pgh.pa.us] > > Hiroshi Inoue <Inoue@tpf.co.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@tpf.co.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@tpf.co.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@sss.pgh.pa.us]
>
> "Hiroshi Inoue" <Inoue@tpf.co.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@tpf.co.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@sss.pgh.pa.us] > > "Hiroshi Inoue" <Inoue@tpf.co.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