diff options
| -rw-r--r-- | doc/TODO.detail/return | 1363 |
1 files changed, 0 insertions, 1363 deletions
diff --git a/doc/TODO.detail/return b/doc/TODO.detail/return deleted file mode 100644 index 046c77e960a..00000000000 --- a/doc/TODO.detail/return +++ /dev/null @@ -1,1363 +0,0 @@ -From pgsql-hackers-owner+M22587@postgresql.org Wed May 8 19:47:28 2002 -Return-path: <pgsql-hackers-owner+M22587@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g48NlR416874 - for <pgman@candle.pha.pa.us>; Wed, 8 May 2002 19:47:27 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id A5D5F475ED7; Wed, 8 May 2002 19:47:18 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 074664762D2; Wed, 8 May 2002 19:38:42 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 54D9A475F6C - for <pgsql-hackers@postgresql.org>; Wed, 8 May 2002 19:38:32 -0400 (EDT) -Received: from amanda.mallet-assembly.org (durham-24-086.biz.dsl.gtei.net [4.3.24.86]) - by postgresql.org (Postfix) with ESMTP id 5ACCD476487 - for <pgsql-hackers@postgresql.org>; Wed, 8 May 2002 19:08:19 -0400 (EDT) -Received: from localhost.localdomain (amanda.mallet-assembly.org [127.0.0.1]) - by localhost.mallet-assembly.org (Postfix) with ESMTP id 8399D12F78C - for <pgsql-hackers@postgresql.org>; Wed, 8 May 2002 19:08:10 -0400 (EDT) -Received: by amanda.mallet-assembly.org (Postfix, from userid 1000) - id 0730F12F685; Wed, 8 May 2002 19:08:02 -0400 (EDT) -To: pgsql-hackers@postgresql.org -Subject: [HACKERS] Queries using rules show no rows modified? -From: Michael Alan Dorman <mdorman@debian.org> -Date: 08 May 2002 19:08:01 -0400 -Message-ID: <87znzaqlv2.fsf@amanda.mallet-assembly.org> -Lines: 61 -User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 -MIME-Version: 1.0 -Content-Type: text/plain; charset=us-ascii -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -I'm using 7.2.1 on a Debian system. - -If I do an insert or update or delete on a table, postgres tells me -how many rows were affected. - -Using the following input to psql, I got the results: - -INSERT 0 0 -UPDATE 0 -DELETE 0 - -Is this expected? The principle of least suprise suggests to me that -regardless of the query being rewritten, there is some number of -tuples being affected, and it would thus still be appropriate to -return that number. - -I realize it's not technically a "bug", since there's no particular -guarantee that someone specified existing records or whatnot, but as -an additional fourth-string check in some web code I put together, I -was checking to see if stuff was returned or updated (since the system -should only being allowing changes to things that exist) as a -heuristic to guard against 1) bugs, and 2) attempts to maliciously -subvert the public interface. - -I can find no mention of this issue in the documentation regarding the -rule system. Anyone have any guidance? - -Mike. - ------8<----- -drop sequence member_id_seq; -create sequence member_id_seq; - -drop table member; -create table member ( - id integer not null constraint member_id primary key default nextval('member_id_seq'), - created timestamp not null default now (), - modified timestamp not null default now (), - deleted timestamp default null, - email character varying (128) not null constraint member_email unique, - password character varying (128) not null -); - -drop view members; -create view members as select * from member m1 where m1.deleted is null; - -drop rule members_delete; -create rule members_delete as on delete to members do instead update member set deleted = current_timestamp; - -drop rule members_insert; -create rule members_insert as on insert to members do instead insert into member (email, password) values (new.email, new.password); - -drop rule members_update; -create rule members_update as on update to members do instead update member set email = new.email, password = new.password; - -insert into members (email, password) values ('mdorman@wombat.org','pinochle'); - -update members set email='mdorman@lemur.org', password='wombat' where id = 1; - -delete from members where id = 1; ------>8----- - ----------------------------(end of broadcast)--------------------------- -TIP 6: Have you searched our list archives? - -http://archives.postgresql.org - -From pgsql-hackers-owner+M22589@postgresql.org Wed May 8 20:15:34 2002 -Return-path: <pgsql-hackers-owner+M22589@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g490FY417439 - for <pgman@candle.pha.pa.us>; Wed, 8 May 2002 20:15:34 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id DFD6E4762E1; Wed, 8 May 2002 20:15:21 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 26D52476140; Wed, 8 May 2002 20:15:05 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 86B5947593F - for <pgsql-hackers@postgresql.org>; Wed, 8 May 2002 20:14:51 -0400 (EDT) -Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) - by postgresql.org (Postfix) with SMTP id 2BA754758F6 - for <pgsql-hackers@postgresql.org>; Wed, 8 May 2002 20:14:46 -0400 (EDT) -Received: (qmail 25269 invoked from network); 9 May 2002 00:14:48 -0000 -Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108) - by sd2.tpf-fw-c.co.jp with SMTP; 9 May 2002 00:14:48 -0000 -Received: from tpf.co.jp ([126.0.1.68]) - by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id JAA09255; - Thu, 9 May 2002 09:14:46 +0900 (JST) -Message-ID: <3CD9BFCC.268A52E0@tpf.co.jp> -Date: Thu, 09 May 2002 09:16:12 +0900 -From: Hiroshi Inoue <Inoue@tpf.co.jp> -X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U) -X-Accept-Language: ja -MIME-Version: 1.0 -To: Michael Alan Dorman <mdorman@debian.org> -cc: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> -Content-Type: text/plain; charset=iso-2022-jp -Content-Transfer-Encoding: 7bit -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Michael Alan Dorman wrote: -> -> I'm using 7.2.1 on a Debian system. -> -> If I do an insert or update or delete on a table, postgres tells me -> how many rows were affected. -> -> Using the following input to psql, I got the results: -> -> INSERT 0 0 -> UPDATE 0 -> DELETE 0 -> -> Is this expected? The principle of least suprise suggests to me that -> regardless of the query being rewritten, there is some number of -> tuples being affected, and it would thus still be appropriate to -> return that number. - -You are right. It's a bug introduced in 7.2. -Please check the thread [GENERAL]([HACKERS]) -Using views and MS access via odbc. -If there's no objection, I would commit the patch -in the thread to both 7.2-stable and the current. - -regards, -Hiroshi Inoue - http://w2422.nsk.ne.jp/~inoue/ - ----------------------------(end of broadcast)--------------------------- -TIP 2: you can get off all lists at once with the unregister command - (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) - -From pgsql-hackers-owner+M22600@postgresql.org Thu May 9 01:26:14 2002 -Return-path: <pgsql-hackers-owner+M22600@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g495QD420463 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 01:26:14 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id 1CE274759BC; Thu, 9 May 2002 01:26:14 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 130AE476107; Thu, 9 May 2002 01:25:47 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 8E2AC475EFB - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 01:25:35 -0400 (EDT) -Received: from sss.pgh.pa.us (unknown [192.204.191.242]) - by postgresql.org (Postfix) with ESMTP id 52D5D475F20 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 01:24:28 -0400 (EDT) -Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) - by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g495O6W16675; - Thu, 9 May 2002 01:24:06 -0400 (EDT) -To: Hiroshi Inoue <Inoue@tpf.co.jp> -cc: Michael Alan Dorman <mdorman@debian.org>, pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -In-Reply-To: <3CD9BFCC.268A52E0@tpf.co.jp> -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> <3CD9BFCC.268A52E0@tpf.co.jp> -Comments: In-reply-to Hiroshi Inoue <Inoue@tpf.co.jp> - message dated "Thu, 09 May 2002 09:16:12 +0900" -Date: Thu, 09 May 2002 01:24:05 -0400 -Message-ID: <16672.1020921845@sss.pgh.pa.us> -From: Tom Lane <tgl@sss.pgh.pa.us> -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Hiroshi Inoue <Inoue@tpf.co.jp> writes: -> If there's no objection, I would commit the patch -> in the thread to both 7.2-stable and the current. - -Last I checked, I objected to your solution and you objected to mine -... so I think it's on hold until we get some more votes. - - regards, tom lane - ----------------------------(end of broadcast)--------------------------- -TIP 3: if posting/reading through Usenet, please send an appropriate -subscribe-nomail command to majordomo@postgresql.org so that your -message can get through to the mailing list cleanly - -From pgsql-hackers-owner+M22625@postgresql.org Thu May 9 10:08:57 2002 -Return-path: <pgsql-hackers-owner+M22625@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g49E8u401598 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 10:08:56 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id 69C16476171; Thu, 9 May 2002 10:08:37 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id C9CE1476629; Thu, 9 May 2002 09:58:53 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id C57B4476528 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 09:58:42 -0400 (EDT) -Received: from amanda.mallet-assembly.org (durham-24-086.biz.dsl.gtei.net [4.3.24.86]) - by postgresql.org (Postfix) with ESMTP id 2CA4147630F - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 09:56:00 -0400 (EDT) -Received: from localhost.localdomain (amanda.mallet-assembly.org [127.0.0.1]) - by localhost.mallet-assembly.org (Postfix) with ESMTP id E959512F78C - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 09:55:49 -0400 (EDT) -Received: by amanda.mallet-assembly.org (Postfix, from userid 1000) - id D2B9A12F685; Thu, 9 May 2002 09:55:48 -0400 (EDT) -To: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> - <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> -From: Michael Alan Dorman <mdorman@debian.org> -Date: 09 May 2002 09:55:48 -0400 -In-Reply-To: <16672.1020921845@sss.pgh.pa.us> -Message-ID: <87pu05s9wb.fsf@amanda.mallet-assembly.org> -Lines: 57 -User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 -MIME-Version: 1.0 -Content-Type: text/plain; charset=us-ascii -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Tom Lane <tgl@sss.pgh.pa.us> writes: -> Last I checked, I objected to your solution and you objected to mine -> ... so I think it's on hold until we get some more votes. - -Well, If I'm reading this code from DBD::Pg's dbdimp.c correctly, I -think that the perl module, at least, feels that the number is much -more important than the actual command that is returned: - - if (PGRES_TUPLES_OK == status) { - [...] - } else if (PGRES_COMMAND_OK == status) { - /* non-select statement */ - if (! strncmp(cmdStatus, "DELETE", 6) || ! strncmp(cmdStatus, "INSERT", 6) || ! strncmp(cmdStatus, "UPDATE", 6)) { - ret = atoi(cmdTuples); - } else { - ret = -1; - } - -It appears that while the implementation does look to make sure the -return string is recognizable, it doesn't care too much beyond that -which one it is---not suprising as that string is, as far as the DBI -interface is concerned, just "extra information" that has no defined -interface to get back out to the user. More important, at least from -the standpoint of a user of the module seems to be that the cmdTuples -(gotten from PQcmdTuples) represents number affected so it can be -returned. - -In fact, now that I look at it, this change has in fact broken the -DBD::Pg interface with respect to the DBI when used in the presence of -rules, because the DBI spec states that it will either return the -number of tuples affected or -1 if that is unknown, rather than 0, -which breaks as a result of this change. - -I guess there's an argument to be made as to whether PostgreSQL -provides any guarantees about this number being correct or even valid, -but the fact that the library interface makes it available, and I see -nothing in the documentation of the function that suggests that that -number is unreliable suggests that it is not an error to depend on it. - -So, If I understood the proposals correctly, I think that means that -this implementation argues for, or at least would work well with, -Hiroshi's solution, since yours, Tom, would return a false zero in -certain (perhaps rare) situations, arguably loosing information that -the perl module, at least, could use, and the library purports to make -available, in order to preserve information it does not. - -I guess there is one other possibility, though I don't know how -radical it would be in either implementation or effects: return the -empty string from PQcmdTuples in this situation. It serves as -something of an acknowledgement that what went on was not necessarily -fish or fowl, while still being, from my reading of the docs, a valid -return. The perl module certainly regards it as one, albeit one that -transmits precious little information. Well-written interfaces should -already be able to cope with it, given that it is documented as a -possiblity in the docs, right? - -Mike. - ----------------------------(end of broadcast)--------------------------- -TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org - -From pgsql-hackers-owner+M22633@postgresql.org Thu May 9 11:00:49 2002 -Return-path: <pgsql-hackers-owner+M22633@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g49F0m416710 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 11:00:48 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id 62204476171; Thu, 9 May 2002 11:00:43 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 097C44767A0; Thu, 9 May 2002 10:44:34 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 2D9044766F0 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 10:44:21 -0400 (EDT) -Received: from sss.pgh.pa.us (unknown [192.204.191.242]) - by postgresql.org (Postfix) with ESMTP id D786C476306 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 10:43:27 -0400 (EDT) -Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) - by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g49EhUW19441; - Thu, 9 May 2002 10:43:30 -0400 (EDT) -To: Michael Alan Dorman <mdorman@debian.org> -cc: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -In-Reply-To: <87pu05s9wb.fsf@amanda.mallet-assembly.org> -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> <87pu05s9wb.fsf@amanda.mallet-assembly.org> -Comments: In-reply-to Michael Alan Dorman <mdorman@debian.org> - message dated "09 May 2002 09:55:48 -0400" -Date: Thu, 09 May 2002 10:43:30 -0400 -Message-ID: <19438.1020955410@sss.pgh.pa.us> -From: Tom Lane <tgl@sss.pgh.pa.us> -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Michael Alan Dorman <mdorman@debian.org> writes: -> So, If I understood the proposals correctly, I think that means that -> this implementation argues for, or at least would work well with, -> Hiroshi's solution, since yours, Tom, would return a false zero in -> certain (perhaps rare) situations, - -IMHO Hiroshi's solution would return false information in more cases -than mine. - -The basic argument in favor of a patch like this is that if a rule -replaces (DO INSTEAD) a command with another command of the same general -type, it is useful to return the tag for the replacement command not the -original. I agree with that. I do not agree with the claim that we -should return a tag from the underlying implementation when a rule -rewrites a query into a form totally unrecognizable to the client. -Consider again the example of transforming an UPDATE on a view into -an INSERT on some underlying table --- but let's reverse it now and -suppose it's the other way, the client sends INSERT and the rule -replaces it with an UPDATE. If the client is expecting to get back -"INSERT m n" and actually gets back "UPDATE n", isn't that client -likely to break? - -Another issue is that the whole thing falls down if the rewriting -generates more than one query; both Hiroshi's proposal and mine will -not return any substitute tag then. This seems rather restrictive. -Maybe we could have behavior like this: if the original command is -replaced, then use the tag from the last substituted command of the -same class (eg, if you rewrite an UPDATE into an INSERT and an UPDATE, -you get the tag from the UPDATE). If there is *no* substitute command -of the same class, I still believe that returning "UPDATE 0" is correct -behavior. You sent an update, zero tuples were updated, end of story. -There is not scope in this API to tell you about how many tuples might -have been inserted or deleted. - -Note that as of CVS tip, the firing order of rules is predictable, -so the rule author can control which substituted command is "the last -one". Without this I don't think that the above would work, but with -it, it seems like a moderately clean answer. Moreover it's at least -somewhat compatible with the pre-7.2.1 behavior --- where you got the -tag from the last command *executed* regardless of any other -considerations. That was definitely broken. - - regards, tom lane - ----------------------------(end of broadcast)--------------------------- -TIP 2: you can get off all lists at once with the unregister command - (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) - -From pgsql-hackers-owner+M22639@postgresql.org Thu May 9 12:16:27 2002 -Return-path: <pgsql-hackers-owner+M22639@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g49GGP423508 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 12:16:26 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id 0E20447622F; Thu, 9 May 2002 12:16:15 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 3317647656F; Thu, 9 May 2002 12:14:17 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id DBD2B476486 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 12:14:05 -0400 (EDT) -Received: from amanda.mallet-assembly.org (durham-24-086.biz.dsl.gtei.net [4.3.24.86]) - by postgresql.org (Postfix) with ESMTP id 95F62476371 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 12:13:33 -0400 (EDT) -Received: from localhost.localdomain (amanda.mallet-assembly.org [127.0.0.1]) - by localhost.mallet-assembly.org (Postfix) with ESMTP id 2031312F78C - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 12:13:24 -0400 (EDT) -Received: by amanda.mallet-assembly.org (Postfix, from userid 1000) - id 075D312F685; Thu, 9 May 2002 12:13:22 -0400 (EDT) -To: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> - <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> - <87pu05s9wb.fsf@amanda.mallet-assembly.org> - <19438.1020955410@sss.pgh.pa.us> -From: Michael Alan Dorman <mdorman@debian.org> -Date: 09 May 2002 12:13:22 -0400 -In-Reply-To: <19438.1020955410@sss.pgh.pa.us> -Message-ID: <87y9etqoyl.fsf@amanda.mallet-assembly.org> -Lines: 97 -User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 -MIME-Version: 1.0 -Content-Type: text/plain; charset=us-ascii -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Tom Lane <tgl@sss.pgh.pa.us> writes: -> The basic argument in favor of a patch like this is that if a rule -> replaces (DO INSTEAD) a command with another command of the same -> general type, it is useful to return the tag for the replacement -> command not the original. I agree with that. - -I would argue that the argument in favor of a patch is that there's no -documentation anywhere that behavior changed, or that PQcmdTuples will -not return the expected result in the presence of rules. :-) - -Is the change behaviorou propose implementable as a patch to 7.2.1? - -> If the client is expecting to get back "INSERT m n" and actually -> gets back "UPDATE n", isn't that client likely to break? - -Perhaps. How many clients are checking that the string returned -matches the query it sent? - -I've checked DBD::Pg, it doesn't. I've checked psycopg, it doesn't, -though it looks like its handling of the value might be a bit bogus. -ecpg doesn't, though it looks like it might choke on an empty string. -PHP doesn't. QT3 doesn't. PoPY (another Python interface) doesn't. -The TCL library doesn't even look at the return, it just passes it -back, so I suppose there might be applications doing a direct look. -The python lib included with postgresql doesn't. In fact, the idiom -is either (in pseudocode): - - if (temp = PQcmdTuples (result)) { - numTuples = atoi (temp); - } else { - numTuples = some other arbitrary value; - } - -or: - - numTuples = atoi (PQcmdTuples (result)); - -So, no, my *very* unscientific and non-comprehensive survey suggests -that your fears are mostly groundless. But I haven't seen a single -interface that *is* depending on that being correct, but many of them -return misleading results if PQcmdTuples does. - -Which is, if I haven't hammered this enough, not mentioned anywhere in -the documentation. - -> Another issue is that the whole thing falls down if the rewriting -> generates more than one query; both Hiroshi's proposal and mine will -> not return any substitute tag then. This seems rather restrictive. - -If, when you say, "will not return any substitute tag then.", you mean -that, as an end result PQcmdTuple would return an empty string, well, -that seems reasonable---it keeps the DB from returning bogus info, and -an empty string returned from PQcmdTuple _is_ documented as a valid -response, and it looks like most interfaces would handle it just fine -(except maybe for ecpg, which I would argue either has a bug or I'm -not reading right). - -I guess there's the argument to be made that any overly-zealous -interface that might choke on getting a different tag back might also -choke on getting no tag back. But, again, I don't see any doing any -of this. And they *all* seem to expect PQcmdTuples to either return -legitimate data or nothing at all. - -> Maybe we could have behavior like this: if the original command is -> replaced, then use the tag from the last substituted command of the -> same class (eg, if you rewrite an UPDATE into an INSERT and an -> UPDATE, you get the tag from the UPDATE). If there is *no* -> substitute command of the same class, I still believe that returning -> "UPDATE 0" is correct behavior. You sent an update, zero tuples -> were updated, end of story. - -As long as you document that PQcmdTuples cannot be relied on when -using rules, since the rules might change the query sufficiently to -make it unrecognizable, that's probably OK, though it'll require -significant changes to just about all interface libraries. - -> Note that as of CVS tip, the firing order of rules is predictable, -> so the rule author can control which substituted command is "the -> last one". Without this I don't think that the above would work, -> but with it, it seems like a moderately clean answer. Moreover it's -> at least somewhat compatible with the pre-7.2.1 behavior --- where -> you got the tag from the last command *executed* regardless of any -> other considerations. That was definitely broken. - -So should I interpret these references to CVS tip as suggesting that -the fix for this change in behavior is not going to be seen until 7.3, -or just that a most-complete fix that tries to deal with multi-rule -invocations would have to wait for 7.3, but that a fix for the simpler -'do instead' case could show up in a 7.2.X release? - -Because it seems to me that if we're not going to see a release with a -fix for this change in behavior, we need to make sure that maintainers -of all interfaces know that all bets are off regarding PQcmdTuples in -the (I believe undetectable) presence of rules so they'll make no -effort to use it. - -Mike. - ----------------------------(end of broadcast)--------------------------- -TIP 4: Don't 'kill -9' the postmaster - -From pgsql-hackers-owner+M22651@postgresql.org Thu May 9 13:48:04 2002 -Return-path: <pgsql-hackers-owner+M22651@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g49Hm3424831 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 13:48:03 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id 85BBD4764FF; Thu, 9 May 2002 13:48:00 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id A414347676B; Thu, 9 May 2002 13:39:19 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 5F794476652 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 13:39:05 -0400 (EDT) -Received: from sss.pgh.pa.us (unknown [192.204.191.242]) - by postgresql.org (Postfix) with ESMTP id 09688475BAD - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 13:35:21 -0400 (EDT) -Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) - by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g49HZKW20799; - Thu, 9 May 2002 13:35:20 -0400 (EDT) -To: Michael Alan Dorman <mdorman@debian.org> -cc: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -In-Reply-To: <87y9etqoyl.fsf@amanda.mallet-assembly.org> -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> <87pu05s9wb.fsf@amanda.mallet-assembly.org> <19438.1020955410@sss.pgh.pa.us> <87y9etqoyl.fsf@amanda.mallet-assembly.org> -Comments: In-reply-to Michael Alan Dorman <mdorman@debian.org> - message dated "09 May 2002 12:13:22 -0400" -Date: Thu, 09 May 2002 13:35:19 -0400 -Message-ID: <20796.1020965719@sss.pgh.pa.us> -From: Tom Lane <tgl@sss.pgh.pa.us> -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Michael Alan Dorman <mdorman@debian.org> writes: -> Tom Lane <tgl@sss.pgh.pa.us> writes: ->> If the client is expecting to get back "INSERT m n" and actually ->> gets back "UPDATE n", isn't that client likely to break? - -> Perhaps. How many clients are checking that the string returned -> matches the query it sent? - -> I've checked DBD::Pg, it doesn't. - -You are confusing client behavior (by which I meant application) -with library behavior. In libpq terms, an application that's sent -an INSERT command might expect to be able to retrieve an OID with -PQoidValue(). Whether the library avoids core-dumping doesn't mean -that the calling app will behave sanely. - -> I would argue that the argument in favor of a patch is that there's no -> documentation anywhere that behavior changed, or that PQcmdTuples will -> not return the expected result in the presence of rules. :-) - -The motivation for making a change was to try to *preserve* pre-7.2 -behavior in the case of INSERTs, where formerly you got back an INSERT -tag even in the presence of ON INSERT DO not-INSTEAD rules. 7.2 broke -that; 7.2.1 fixed that case but changed the behavior for INSTEAD cases. -What we're realizing now is that we need an actually designed behavior, -rather than the implementation artifact that happened to yield pleasant -results most of the time before 7.2. - -I'm arguing that the "designed behavior" ought to include the -stipulation that the tag you get back will match the command you sent. -I think that anything else is more likely to confuse clients than help -them. - -> Which is, if I haven't hammered this enough, not mentioned anywhere in -> the documentation. - -Mainly because no one ever designed the behavior; the pre-7.2 -implementation didn't really think about what should happen. - -> I guess there's the argument to be made that any overly-zealous -> interface that might choke on getting a different tag back might also -> choke on getting no tag back. But, again, I don't see any doing any -> of this. And they *all* seem to expect PQcmdTuples to either return -> legitimate data or nothing at all. - -No, you're still missing the point. PQcmdTuples isn't going to dump -core, because it has no context about what was expected: it sees a tag -and interprets it as best it can, without any idea about what the -calling app might be expecting. What we need to think about here is -what linkage an *application* can reasonably expect between the command -it sends and the tag it gets back (and, hence, the info it can expect to -retrieve from the tag). - -> As long as you document that PQcmdTuples cannot be relied on when -> using rules, since the rules might change the query sufficiently to -> make it unrecognizable, that's probably OK, though it'll require -> significant changes to just about all interface libraries. - -One more time: there will be zero change in any interface library, -no matter what we do here. The libraries operate at too low a level -to be affected; they have no idea what command you sent. I'm not even -convinced that PQcmdTuples is where to document the issue --- it seems -to me to be a rule question, instead. - -> So should I interpret these references to CVS tip as suggesting that -> the fix for this change in behavior is not going to be seen until 7.3, -> or just that a most-complete fix that tries to deal with multi-rule -> invocations would have to wait for 7.3, but that a fix for the simpler -> 'do instead' case could show up in a 7.2.X release? - -Until we've decided what *should* happen, it's premature to discuss -whether we can fix it correctly in 7.2.X or should install a quick-hack -change instead. I'd prefer to fix it correctly but we must not let -ourselves be seduced by a quick hack into not thinking about what the -behavior really ideally ought to be. We've done that once too often -already ;-) - -FWIW, I'm not at all sure that there will *be* any 7.2.2 release -before 7.3. There hasn't so far been enough volume of fixes to -justify one (no, this problem doesn't justify one IMHO...) - - regards, tom lane - ----------------------------(end of broadcast)--------------------------- -TIP 5: Have you checked our extensive FAQ? - -http://www.postgresql.org/users-lounge/docs/faq.html - -From pgsql-hackers-owner+M22663@postgresql.org Thu May 9 14:49:40 2002 -Return-path: <pgsql-hackers-owner+M22663@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g49Ind425928 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 14:49:39 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id CD0D1476627; Thu, 9 May 2002 14:49:37 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 5E96847673E; Thu, 9 May 2002 14:44:15 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 9FD43476470 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 14:44:04 -0400 (EDT) -Received: from amanda.mallet-assembly.org (durham-24-086.biz.dsl.gtei.net [4.3.24.86]) - by postgresql.org (Postfix) with ESMTP id BD99947663F - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 14:38:03 -0400 (EDT) -Received: from localhost.localdomain (amanda.mallet-assembly.org [127.0.0.1]) - by localhost.mallet-assembly.org (Postfix) with ESMTP id 4904C12F78C - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 14:37:50 -0400 (EDT) -Received: by amanda.mallet-assembly.org (Postfix, from userid 1000) - id A06BF12F685; Thu, 9 May 2002 14:37:47 -0400 (EDT) -To: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> - <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> - <87pu05s9wb.fsf@amanda.mallet-assembly.org> - <19438.1020955410@sss.pgh.pa.us> - <87y9etqoyl.fsf@amanda.mallet-assembly.org> - <20796.1020965719@sss.pgh.pa.us> -From: Michael Alan Dorman <mdorman@debian.org> -Date: 09 May 2002 14:37:47 -0400 -In-Reply-To: <20796.1020965719@sss.pgh.pa.us> -Message-ID: <87znz9p3pg.fsf@amanda.mallet-assembly.org> -Lines: 71 -User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 -MIME-Version: 1.0 -Content-Type: text/plain; charset=us-ascii -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Tom Lane <tgl@sss.pgh.pa.us> writes: -> You are confusing client behavior (by which I meant application) -> with library behavior. In libpq terms, an application that's sent -> an INSERT command might expect to be able to retrieve an OID with -> PQoidValue(). Whether the library avoids core-dumping doesn't mean -> that the calling app will behave sanely. - -No, Tom, I'm not confusing them. I'm in no way concerned with -PQcmdTuple coredumping because the published interface specifies that -it can return a null string if it finds it necessary, which implies -that somewhere down there it's doing some decent error handling to -figure out if it's gotten something back it can make sense of and -acting appropriately. - -You brought up core dumps. My concern has been exclusively with the -potential change in behavior this can cause in applications. - -So I've been doing is going and downloading the source to, and looking -at the behavior of, some of the libraries that some---probably many, -maybe even most---clients are using, those for perl and python and -php, and I am finding that most of them do not even expose the -information whose (mis-)interpretation concerns you. - -So, for those interfaces, at least, there was no problem to be fixed -in the first place. - -Still, you don't have to have something actively breaking to warrant -fixing a bug, so there's no reason to have not made the change that -was made. - -The problem is that, at the same time, I am finding that the change to -postgresql 7.2 may make application code using those interfaces begin -to operate in new and different ways because, although they aren't -paying attention to the string, which you are concerned with, they -*are* paying attention to the numbers. - -Many of those interfaces, where they used to return 1 or 10 or 5000 or -6432456, will now be returning 0, which thanks to the great C -tradition, is often interpreted to mean "false", which may lead an -application to question why "nothing happened." As mine did. - -And this isn't necessarily application programmers making bad choices; -the Perl interface, at least, documents the fact that it returns the -number of rows affected or -1 if that is unknowable---but the change -in behavior leads the perl interface to think it knows, when in fact -it doesn't. - -If I knew java better, I'd check the JDBC driver. I mean, imagine: -Perl, python, php and java, all with undocumented unpredictable -behavior in the presence of 'update do instead' rules. Break all four -and you've just created a potential problem for everyone who does web -development. - -That, I think, is one of the more egregious changes in behavior I've -seen in the few years I've been following PostgreSQL, and yet not only -is there any documentation, I feel like I'm having to fight to even -get it acknowledge that it is the bigger problem than the blasted -strings not matching because it affects a heck of a lot more stuff in -a much more direct manner. - -Still, handle this however you want. I'll go fix the Perl driver to -pretend PQcmdTuples doesn't exist, since it can't be trusted to -deliver reliable information, and just have it return -1, and *my* -apps will be OK. Maybe some months down the road when 7.3 finally -straggles into view there will be a solution. Hopefully no one will -have been burned. - -Anyway, I'm done beating this dead horse, since the display is -obviously bothering people. - -Mike. - ----------------------------(end of broadcast)--------------------------- -TIP 4: Don't 'kill -9' the postmaster - -From pgsql-hackers-owner+M22695@postgresql.org Thu May 9 21:16:21 2002 -Return-path: <pgsql-hackers-owner+M22695@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g4A1GK400881 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 21:16:20 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id ADD4F4767FA; Thu, 9 May 2002 21:16:06 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 643D247691E; Thu, 9 May 2002 21:15:49 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 4B0CF47630B - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 21:15:29 -0400 (EDT) -Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) - by postgresql.org (Postfix) with SMTP id B9FB2475F13 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 21:15:22 -0400 (EDT) -Received: (qmail 1794 invoked from network); 10 May 2002 01:15:25 -0000 -Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108) - by sd2.tpf-fw-c.co.jp with SMTP; 10 May 2002 01:15:25 -0000 -Received: from tpf.co.jp ([126.0.1.68]) - by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id KAA09847; - Fri, 10 May 2002 10:15:23 +0900 (JST) -Message-ID: <3CDB1F82.BFE2CC5C@tpf.co.jp> -Date: Fri, 10 May 2002 10:16:50 +0900 -From: Hiroshi Inoue <Inoue@tpf.co.jp> -X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U) -X-Accept-Language: ja -MIME-Version: 1.0 -To: Tom Lane <tgl@sss.pgh.pa.us> -cc: Michael Alan Dorman <mdorman@debian.org>, pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> <87pu05s9wb.fsf@amanda.mallet-assembly.org> <19438.1020955410@sss.pgh.pa.us> -Content-Type: text/plain; charset=iso-2022-jp -Content-Transfer-Encoding: 7bit -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Tom Lane wrote: -> -> Michael Alan Dorman <mdorman@debian.org> writes: -> > So, If I understood the proposals correctly, I think that means that -> > this implementation argues for, or at least would work well with, -> > Hiroshi's solution, since yours, Tom, would return a false zero in -> > certain (perhaps rare) situations, -> -> IMHO Hiroshi's solution would return false information in more cases -> than mine. - - -My solution never returns false information as to -patched cases though the returned result may be -different from the one clients expect. -Probably your solution doesn't return false -information either if 'UPDATE 0' means UPDATE 0 -but unknown INSERT/DELETEs. But few(maybe no ?) -clients seem to think of it and what could clients -do with such infos in the first place ? -Of cource it is nice to have a complete solution -immediately but it doesn't seem easy. My patch is -only a makeshift solution but fixes the most -siginificant case(typical updatable views). - -regards, -Hiroshi Inoue - http://w2422.nsk.ne.jp/~inoue/ - ----------------------------(end of broadcast)--------------------------- -TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org - -From pgsql-hackers-owner+M22696@postgresql.org Thu May 9 21:28:00 2002 -Return-path: <pgsql-hackers-owner+M22696@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g4A1S0400995 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 21:28:00 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id 8D3EC4768F7; Thu, 9 May 2002 21:27:54 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id C60E347686A; Thu, 9 May 2002 21:27:40 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 9DDB6475EF2 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 21:27:22 -0400 (EDT) -Received: from sss.pgh.pa.us (unknown [192.204.191.242]) - by postgresql.org (Postfix) with ESMTP id 8D51A475D9D - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 21:27:21 -0400 (EDT) -Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) - by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g4A1R8W24994; - Thu, 9 May 2002 21:27:08 -0400 (EDT) -To: Hiroshi Inoue <Inoue@tpf.co.jp> -cc: Michael Alan Dorman <mdorman@debian.org>, pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -In-Reply-To: <3CDB1F82.BFE2CC5C@tpf.co.jp> -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> <87pu05s9wb.fsf@amanda.mallet-assembly.org> <19438.1020955410@sss.pgh.pa.us> <3CDB1F82.BFE2CC5C@tpf.co.jp> -Comments: In-reply-to Hiroshi Inoue <Inoue@tpf.co.jp> - message dated "Fri, 10 May 2002 10:16:50 +0900" -Date: Thu, 09 May 2002 21:27:08 -0400 -Message-ID: <24991.1020994028@sss.pgh.pa.us> -From: Tom Lane <tgl@sss.pgh.pa.us> -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Hiroshi Inoue <Inoue@tpf.co.jp> writes: -> Of cource it is nice to have a complete solution -> immediately but it doesn't seem easy. My patch is -> only a makeshift solution but fixes the most -> siginificant case(typical updatable views). - -I would like to devise a complete solution *before* we consider -installing makeshift solutions (which will institutionalize wrong -behavior). - -There seems to be some feeling here that in the presence of rewrites -you only want to know that "something happened". Are you suggesting -that the returned tuple count should be the sum of all counts from -insert, update, and delete actions that happened as a result of the -query? We could certainly implement that, but it does not seem like -a good idea to me. - -I'm also concerned about having an understandable definition for the -OID returned for an INSERT query --- if there are additional INSERTs -triggered by rules, does that mean you don't get to see the OID assigned -to the single row you tried to insert? You'll definitely get push-back -if you propose that. But if we add up all the actions for the generated -queries, we are quite likely to be returning an OID along with an insert -count greater than one --- which is certainly confusing, as well as -contrary to the existing documentation about how it works. - -Let's please quit worrying about "can we install a hack today" and -instead try to figure out what a sensible behavior is. I don't think -it's likely to be hard to implement anything we might come up with, -considering how tiny this API is. - - regards, tom lane - ----------------------------(end of broadcast)--------------------------- -TIP 5: Have you checked our extensive FAQ? - -http://www.postgresql.org/users-lounge/docs/faq.html - -From pgsql-hackers-owner+M22699@postgresql.org Thu May 9 22:36:27 2002 -Return-path: <pgsql-hackers-owner+M22699@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g4A2aR401628 - for <pgman@candle.pha.pa.us>; Thu, 9 May 2002 22:36:27 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id EECB1476843; Thu, 9 May 2002 22:36:22 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 2B1B34768E5; Thu, 9 May 2002 22:35:39 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 267D2476860 - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 22:35:28 -0400 (EDT) -Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34]) - by postgresql.org (Postfix) with SMTP id 1BE8447590B - for <pgsql-hackers@postgresql.org>; Thu, 9 May 2002 22:34:30 -0400 (EDT) -Received: (qmail 10177 invoked from network); 10 May 2002 02:34:34 -0000 -Received: from unknown (HELO viscomail.tpf.co.jp) (100.0.0.108) - by sd2.tpf-fw-c.co.jp with SMTP; 10 May 2002 02:34:34 -0000 -Received: from tpf.co.jp ([126.0.1.68]) - by viscomail.tpf.co.jp (8.8.8+Sun/8.8.8) with ESMTP id LAA09898; - Fri, 10 May 2002 11:34:33 +0900 (JST) -Message-ID: <3CDB320F.55B00318@tpf.co.jp> -Date: Fri, 10 May 2002 11:35:59 +0900 -From: Hiroshi Inoue <Inoue@tpf.co.jp> -X-Mailer: Mozilla 4.73 [ja] (Windows NT 5.0; U) -X-Accept-Language: ja -MIME-Version: 1.0 -To: Tom Lane <tgl@sss.pgh.pa.us> -cc: Michael Alan Dorman <mdorman@debian.org>, pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> <87pu05s9wb.fsf@amanda.mallet-assembly.org> <19438.1020955410@sss.pgh.pa.us> <3CDB1F82.BFE2CC5C@tpf.co.jp> <24991.1020994028@sss.pgh.pa.us> -Content-Type: text/plain; charset=iso-2022-jp -Content-Transfer-Encoding: 7bit -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Tom Lane wrote: -> -> Hiroshi Inoue <Inoue@tpf.co.jp> writes: -> > Of cource it is nice to have a complete solution -> > immediately but it doesn't seem easy. My patch is -> > only a makeshift solution but fixes the most -> > siginificant case(typical updatable views). -> -> I would like to devise a complete solution *before* we consider -> installing makeshift solutions (which will institutionalize wrong -> behavior). -> -> There seems to be some feeling here that in the presence of rewrites -> you only want to know that "something happened". Are you suggesting -> that the returned tuple count should be the sum of all counts from -> insert, update, and delete actions that happened as a result of the -> query? We could certainly implement that, but it does not seem like -> a good idea to me. - -What should the backends return for complicated rewrites ? -And how should/could clients handle the results ? -It doesn't seem easy to me and it seems a flaw of rule -system. Honestly I don't think that the psqlodbc driver -can guarantee to handle such cases properly. -However both Ron's case and Michael's one are ordinary -updatable views. If we can't handle the case properly, -we could never recommend users to use (updatable) views. - - -regards, -Hiroshi Inoue - http://w2422.nsk.ne.jp/~inoue/ - ----------------------------(end of broadcast)--------------------------- -TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org - -From pgsql-hackers-owner+M22704@postgresql.org Fri May 10 06:34:07 2002 -Return-path: <pgsql-hackers-owner+M22704@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g4AAY6406914 - for <pgman@candle.pha.pa.us>; Fri, 10 May 2002 06:34:07 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id E649F476131; Fri, 10 May 2002 06:33:54 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 77EB447672A; Fri, 10 May 2002 06:30:40 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id A391F4762CD - for <pgsql-hackers@postgresql.org>; Fri, 10 May 2002 06:30:24 -0400 (EDT) -Received: from smtp017.mail.yahoo.com (smtp017.mail.yahoo.com [216.136.174.114]) - by postgresql.org (Postfix) with SMTP id 75A4A47620B - for <pgsql-hackers@postgresql.org>; Fri, 10 May 2002 06:30:18 -0400 (EDT) -Received: from h00045a2e4e7c.ne.client2.attbi.com (HELO saturn.janwieck.net) (janwieck@24.61.137.137 with login) - by smtp.mail.vip.sc5.yahoo.com with SMTP; 10 May 2002 10:30:19 -0000 -Received: (from wieck@localhost) - by saturn.janwieck.net (8.11.2/8.11.2) id g4AAJGD03410; - Fri, 10 May 2002 06:19:16 -0400 -From: Jan Wieck <janwieck@yahoo.com> -Message-ID: <200205101019.g4AAJGD03410@saturn.janwieck.net> -Subject: Re: [HACKERS] Queries using rules show no rows modified? -In-Reply-To: <24991.1020994028@sss.pgh.pa.us> from Tom Lane at "May 9, 2002 09:27:08 - pm" -To: Tom Lane <tgl@sss.pgh.pa.us> -Date: Fri, 10 May 2002 06:19:16 -0400 (EDT) -cc: Hiroshi Inoue <Inoue@tpf.co.jp>, Michael Alan Dorman <mdorman@debian.org>, - pgsql-hackers@postgresql.org -X-Mailer: ELM [version 2.4ME+ PL68 (25)] -MIME-Version: 1.0 -Content-Type: text/plain; charset=US-ASCII -Content-Transfer-Encoding: 7bit -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: ORr - -Tom Lane wrote: -> Hiroshi Inoue <Inoue@tpf.co.jp> writes: -> > Of cource it is nice to have a complete solution -> > immediately but it doesn't seem easy. My patch is -> > only a makeshift solution but fixes the most -> > siginificant case(typical updatable views). -> -> I would like to devise a complete solution *before* we consider -> installing makeshift solutions (which will institutionalize wrong -> behavior). -> -> There seems to be some feeling here that in the presence of rewrites -> you only want to know that "something happened". Are you suggesting -> that the returned tuple count should be the sum of all counts from -> insert, update, and delete actions that happened as a result of the -> query? We could certainly implement that, but it does not seem like -> a good idea to me. - - IMHO the answer should only be a number if the rewritten - querytree list consists of one query of the same command - type. everything else has to lead into "unknown". - - -Jan - --- - -#======================================================================# -# It's easier to get forgiveness for being wrong than for being right. # -# Let's break this rule - forgive me. # -#================================================== JanWieck@Yahoo.com # - - - ----------------------------(end of broadcast)--------------------------- -TIP 3: if posting/reading through Usenet, please send an appropriate -subscribe-nomail command to majordomo@postgresql.org so that your -message can get through to the mailing list cleanly - -From pgsql-hackers-owner+M22723@postgresql.org Fri May 10 10:56:10 2002 -Return-path: <pgsql-hackers-owner+M22723@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g4AEu9418485 - for <pgman@candle.pha.pa.us>; Fri, 10 May 2002 10:56:09 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id AA205475C2C; Fri, 10 May 2002 10:55:46 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id E96B447667B; Fri, 10 May 2002 10:51:45 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id D46CF475A00 - for <pgsql-hackers@postgresql.org>; Fri, 10 May 2002 10:51:34 -0400 (EDT) -Received: from sss.pgh.pa.us (unknown [192.204.191.242]) - by postgresql.org (Postfix) with ESMTP id 09489475A04 - for <pgsql-hackers@postgresql.org>; Fri, 10 May 2002 10:51:30 -0400 (EDT) -Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) - by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g4AEp5W28246; - Fri, 10 May 2002 10:51:06 -0400 (EDT) -To: Hiroshi Inoue <Inoue@tpf.co.jp> -cc: Michael Alan Dorman <mdorman@debian.org>, pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -In-Reply-To: <3CDB320F.55B00318@tpf.co.jp> -References: <87znzaqlv2.fsf@amanda.mallet-assembly.org> <3CD9BFCC.268A52E0@tpf.co.jp> <16672.1020921845@sss.pgh.pa.us> <87pu05s9wb.fsf@amanda.mallet-assembly.org> <19438.1020955410@sss.pgh.pa.us> <3CDB1F82.BFE2CC5C@tpf.co.jp> <24991.1020994028@sss.pgh.pa.us> <3CDB320F.55B00318@tpf.co.jp> -Comments: In-reply-to Hiroshi Inoue <Inoue@tpf.co.jp> - message dated "Fri, 10 May 2002 11:35:59 +0900" -Date: Fri, 10 May 2002 10:51:05 -0400 -Message-ID: <28243.1021042265@sss.pgh.pa.us> -From: Tom Lane <tgl@sss.pgh.pa.us> -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Hiroshi Inoue <Inoue@tpf.co.jp> writes: -> What should the backends return for complicated rewrites ? - -Well, given that we have only two or three fields to work in, -it obviously has to be a very simplified view of what happened. -But we have to define *something*. - -> And how should/could clients handle the results ? -> It doesn't seem easy to me and it seems a flaw of rule -> system. - -No, the problem is that the command tag API was designed without any -thought for rule rewriting. But I don't think it's worth revising -that API completely. Even if we did, we'd still have to define what -behavior would be seen by clients that use the existing PQcmdTuples, -etc, calls; so we'd still have to solve these same issues. - -Come on, guys, work with me a little here. I've thrown out several -alternative suggestions already, and all I've gotten from either of -you is refusal to think about the problem. - -I was thinking last night that it might help to break down the issue a -little bit. We have either two or three result fields to think about: -the tag name, the tuple count, and in the case of INSERT the inserted -row OID. Let's consider each one independently. - -1. The tag name: AFAICS, this ought *always* to match the type of the -original command submitted by the client. Doing otherwise could confuse -clients that are submitting multiple commands per query string. -Besides, the only possible downside from making this requirement is that -we couldn't send back an insertion OID when the original command was -an update or delete. How likely is it that a client would expect to -be able to get an insertion OID from such a command? - -2. The inserted row OID: per above, will be supplied only if the -original command was an INSERT. If the original insert command is -not removed (no INSTEAD rule), then I think this result should clearly -come from the execution of the original command, regardless of any -additional INSERTs added by rules. If the original command is removed -by INSTEAD, then we can distinguish three sub-cases: - a. No INSERTs in rewriter output: easy, we must return 0. - b. Exactly one INSERT in rewriter output: pretty easy to agree that - we should return this command's result. - c: More than one INSERT in rewriter output: we have a couple of - possibilities here. It'd be reasonable to directly use the - result of the last INSERT, or we could total the results of - all the INSERTs (ie, if taken together they insert a sum total - of one row, return that row OID; else return 0). Maybe there - are other possible behaviors. Any thoughts? - -3. The tuple count: this seems the most contentious issue. Again, -if there is no INSTEAD rule I'd be strongly inclined to say we -should just return the count from the original command, ignoring any -commands added by rules. If there is an INSTEAD, we've discussed -several possibilities: use result of last command in the rewritten -series, use result of last command of same type as original command, -sum up the results of all the rewritten commands, maybe some others -that I forgot. - -Given Michael's concern about being able to "tell that something -happened", I'm inclined to go with the summing-up behavior in the -INSTEAD cases. This would lead to the following boiled-down behavior: - -A. If original command is executed (no INSTEAD), return its tag as-is, -regardless of commands added by rules. - -B. If original command is not executed, then return its tag name -plus required fields defined as follows: tuple count is sum of tuple -counts of all replacement commands. For an INSERT, if the replacement -commands taken together inserted a grand total of exactly one tuple, -return that tuple's OID; else return 0. - -This is not completely consistent in pathological cases: you could get -a tuple OID returned even when the returned tuple count is greater -than one, which is not a possible case currently. (This would happen -given a rewrite consisting of a single-row INSERT plus additional -update or delete actions that affect some rows.) But that seems -pretty oddball. In all the simple cases I think this proposal gives -reasonable behavior. - -A tighter definition for case B would use the sum of the tuple counts -of only the replacement actions that are of the same type as the -original command. This would eliminate the possible inconsistency -between tuple count and insert OID results, and it's arguably saner -than the above proposal: "if it says UPDATE 4, that should mean that -four rows were updated, not that something else happened to four rows". -But it would not meet Michael's concern about using PQcmdTuples to -tell that "something happened". I could live with either definition. - -Thoughts, different proposals, alternative ways of breaking down -the problem? - - regards, tom lane - ----------------------------(end of broadcast)--------------------------- -TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org - -From pgsql-hackers-owner+M22899@postgresql.org Thu May 16 16:31:02 2002 -Return-path: <pgsql-hackers-owner+M22899@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g4GKV2B23639 - for <pgman@candle.pha.pa.us>; Thu, 16 May 2002 16:31:02 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id EDDEF4762F9; Thu, 16 May 2002 16:30:59 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 52CE1476519; Thu, 16 May 2002 16:30:28 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id 92A34475C8A - for <pgsql-hackers@postgresql.org>; Thu, 16 May 2002 16:30:14 -0400 (EDT) -Received: from amanda.mallet-assembly.org (durham-24-086.biz.dsl.gtei.net [4.3.24.86]) - by postgresql.org (Postfix) with ESMTP id 0E8A3475BBE - for <pgsql-hackers@postgresql.org>; Thu, 16 May 2002 16:30:13 -0400 (EDT) -Received: from localhost.localdomain (amanda.mallet-assembly.org [127.0.0.1]) - by localhost.mallet-assembly.org (Postfix) with ESMTP id DDA4412F78C - for <pgsql-hackers@postgresql.org>; Thu, 16 May 2002 16:30:03 -0400 (EDT) -Received: by amanda.mallet-assembly.org (Postfix, from userid 1000) - id CF2A912F685; Thu, 16 May 2002 16:30:01 -0400 (EDT) -To: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -References: <200205101019.g4AAJGD03410@saturn.janwieck.net> - <28286.1021042653@sss.pgh.pa.us> -From: Michael Alan Dorman <mdorman@debian.org> -Date: 16 May 2002 16:30:01 -0400 -In-Reply-To: <28286.1021042653@sss.pgh.pa.us> -Message-ID: <874rh7rg3a.fsf@amanda.mallet-assembly.org> -Lines: 47 -User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.2 -MIME-Version: 1.0 -Content-Type: text/plain; charset=us-ascii -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: OR - -Tom Lane <tgl@sss.pgh.pa.us> writes: -> Michael seems to feel that the tuple count should be nonzero if any -> of the replacement operations did anything at all. This does not -> make a lot of sense at the command tag level ("UPDATE 4" might not -> mean that 4 tuples were updated) but if you look at the definition -> of PQcmdTuples ("returns the number of rows affected by the SQL -> command") it's not so unreasonable. And I can see the point of -> wanting to know whether anything happened. - -Close. - -It's not so much that I want to know exactly what happened, it's that -I want to know that if PostgreSQL says nothing happened, then I can be -sure that nothing happened, rather than being told that nothing -happened when something happened, and vice versa. - -In fact, my suggestion---which might suffer from issues that I am not -aware of, perhaps the ones that led to the patch in the first -place---would be that, given ambiguity, have the system return -something that would cause PQcmdTuples to return an empty string (I'm -assuing this would be a result string with no numbers attached at -all). - -It is documented, after all, as being the return value when the system -cannot determine an otherwise correct number, and all of the code I -looked at would, I believe, cope gracefully with it, returning what -I'm guessing (except in the Perl case, where I'm sure) is a sentinel -value indicating, "it worked, but I have no idea how many tuples were -involved". - -But I'm not wedded to that---I just don't want to get an answer back -that might lead me off into the woods. - -As for the issue of whether the tag is the same or not, I am utterly -pragmatic---I don't use it, and don't really have a way to get to it -from the interfaces I use, so I think the best option is probably -something where the rules to describe it are straightforward to -minimize confusion and support issues. And it should be documented -appropriately. - -I mean, even when this is resolved, we should probably be putting -something in the documentation that says that PQcmdTuples can really -only really be depended upon as a tri-state value: 0 ("nothing -happened"), >0 ("something happened"), empty string ("heck if I -know"). - -Mike. - ----------------------------(end of broadcast)--------------------------- -TIP 6: Have you searched our list archives? - -http://archives.postgresql.org - -From pgsql-hackers-owner+M22911@postgresql.org Fri May 17 13:56:43 2002 -Return-path: <pgsql-hackers-owner+M22911@postgresql.org> -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by candle.pha.pa.us (8.11.6/8.10.1) with ESMTP id g4HHugB12591 - for <pgman@candle.pha.pa.us>; Fri, 17 May 2002 13:56:43 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP - id D1FCF475FB7; Fri, 17 May 2002 13:44:22 -0400 (EDT) -Received: from postgresql.org (postgresql.org [64.49.215.8]) - by postgresql.org (Postfix) with SMTP - id 391334765F6; Fri, 17 May 2002 13:37:17 -0400 (EDT) -Received: from localhost.localdomain (postgresql.org [64.49.215.8]) - by localhost (Postfix) with ESMTP id ADCE94764EF - for <pgsql-hackers@postgresql.org>; Fri, 17 May 2002 13:37:04 -0400 (EDT) -Received: from sss.pgh.pa.us (unknown [192.204.191.242]) - by postgresql.org (Postfix) with ESMTP id 6E63F476886 - for <pgsql-hackers@postgresql.org>; Fri, 17 May 2002 12:59:21 -0400 (EDT) -Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) - by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id g4HDV8W03879; - Fri, 17 May 2002 09:31:08 -0400 (EDT) -To: "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> -cc: pgsql-hackers@postgresql.org -Subject: Re: [HACKERS] Queries using rules show no rows modified? -In-Reply-To: <46C15C39FEB2C44BA555E356FBCD6FA4961DD1@m0114.s-mxs.net> -References: <46C15C39FEB2C44BA555E356FBCD6FA4961DD1@m0114.s-mxs.net> -Comments: In-reply-to "Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> - message dated "Fri, 17 May 2002 08:53:04 +0200" -Date: Fri, 17 May 2002 09:31:08 -0400 -Message-ID: <3876.1021642268@sss.pgh.pa.us> -From: Tom Lane <tgl@sss.pgh.pa.us> -Precedence: bulk -Sender: pgsql-hackers-owner@postgresql.org -Status: ORr - -"Zeugswetter Andreas SB SD" <ZeugswetterA@spardat.at> writes: -> Tom Lane <tgl@sss.pgh.pa.us> writes: ->> Michael seems to feel that the tuple count should be nonzero if any ->> of the replacement operations did anything at all. - -> Here we usually add triggers, for replication, accounting, setting of -> calculated rows ... In all of our cases we want the addition of a trigger -> (or rule on a table) to be transparent to the client. - -Yeah. Triggers wouldn't affect this anyway, unless they tell the system -to suppress insertion/update/deletion of some tuples, in which case I -think it is correct not to count those tuples (certainly that's how the -code has always acted). As far as rules go, the last proposal that I -made would return the tuple count of the original query as long as there -were no INSTEAD rules --- if you have only actions *added* by rules then -they are transparent. - -The hard case is where the original query is not executed because of an -INSTEAD rule. As the code presently stands, you get "UPDATE 0" (or -INSERT or DELETE 0) in that case, regardless of what else was done -instead by the rule. I thought that was OK when we put the change in, -but it seems clear that people do not like that behavior. The notion -of "keep it transparent" doesn't seem to help here. - - regards, tom lane - ----------------------------(end of broadcast)--------------------------- -TIP 4: Don't 'kill -9' the postmaster - |