Re: [HACKERS] [GENERAL] Using views and MS access via odbc
-Original Message- From: Ron Snyder [mailto:[EMAIL PROTECTED]] Although I can't tell for sure, I really don't think it's the output of the UPDATE 0 that is causing the problem. You may have other problems. However you can't get expected results anyway as long as you are using ordinary updatable views in 7.2. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Using views and MS access via odbc
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
"Hiroshi Inoue" [EMAIL PROTECTED] 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);
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Using views and MS access via odbc
Hiroshi Inoue [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Using views and MS access via odbc
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] "Hiroshi Inoue" [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] Using views and MS access via odbc
-Original Message- From: Tom Lane Hiroshi Inoue [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] Using views and MS access via odbc
Hiroshi Inoue [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [GENERAL] Using views and MS access via odbc
Hiroshi Inoue [EMAIL PROTECTED] 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 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [GENERAL] Using views and MS access via odbc
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Hiroshi Inoue [EMAIL PROTECTED] 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 x; 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 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [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/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
