Thread: execute/perform and FOUND
Hi,
I am utterly confused now. Running the attached script on Debian:
postgresql:
Installiert:7.5.21
Mögliche Pakete:7.5.21
Versions-Tabelle:
*** 7.5.21 0
990 ftp://ftp.gwdg.de testing/main Packages
100 /var/lib/dpkg/status
7.4.7-6sarge3 0
500 ftp://ftp.gwdg.de stable/main Packages
500 ftp://ftp.de.debian.org stable/main Packages
7.4.7-6sarge2 0
500 http://security.debian.org stable/updates/main Packages
gives the result below. It seems inconsistent to me with
regard to the FOUND variable. I would expect FOUND to always
be false regardless of whether I use EXECUTE or PERFORM. I
certainly do not expect it to be true for the third EXECUTE
even assuming that PERFORM may have a bug. What is it that I
am missing out on here ?
BEGIN
CREATE TABLE
CREATE FUNCTION
select * from test;
fk_item
---------
(0 Zeilen)
select test();
psql:00-test.sql:33: NOTICE: running: select 1 from test where fk_item=1324314
psql:00-test.sql:33: NOTICE: found (execute 1): f
psql:00-test.sql:33: NOTICE: found (execute 2): f
psql:00-test.sql:33: NOTICE: found (perform): t
psql:00-test.sql:33: NOTICE: found (execute 3): t
test
------
t
(1 Zeile)
rollback;
ROLLBACK
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Attachment
On Thu, Sep 21, 2006 at 01:06:32PM +0200, Karsten Hilbert wrote: > I am utterly confused now. Running the attached script on Debian: > > postgresql: > Installiert:7.5.21 > Mögliche Pakete:7.5.21 > Versions-Tabelle: > *** 7.5.21 0 > 990 ftp://ftp.gwdg.de testing/main Packages > 100 /var/lib/dpkg/status > 7.4.7-6sarge3 0 > 500 ftp://ftp.gwdg.de stable/main Packages > 500 ftp://ftp.de.debian.org stable/main Packages > 7.4.7-6sarge2 0 > 500 http://security.debian.org stable/updates/main Packages PostgreSQL 7.4.13 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.1.2 20060729 (prerelease) (Debian 4.1.1-10) The 7.4 docs don't say anything about execute setting FOUND but regardless of that the PERFORM result still seems faulty: "A PERFORM statement sets FOUND true if it produces (and discards) a row, false if no row is produced." I don't expect a row to be produced by the example. Assuming EXECUTE does not touch FOUND at all the EXECUTE part behaves consistently (namely default FOUND=FALSE at the beginning and later whatever it was after the PERFORM). So, what about the PERFORM ? Why does it set FOUND to true? Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Sep 21, 2006 at 01:06:32PM +0200, Karsten Hilbert wrote: > gives the result below. It seems inconsistent to me with > regard to the FOUND variable. I would expect FOUND to always > be false regardless of whether I use EXECUTE or PERFORM. I > certainly do not expect it to be true for the third EXECUTE > even assuming that PERFORM may have a bug. What is it that I > am missing out on here ? > select * from test; > fk_item > --------- > (0 Zeilen) > > select test(); > psql:00-test.sql:33: NOTICE: running: select 1 from test where fk_item=1324314 > psql:00-test.sql:33: NOTICE: found (execute 1): f > psql:00-test.sql:33: NOTICE: found (execute 2): f > psql:00-test.sql:33: NOTICE: found (perform): t > psql:00-test.sql:33: NOTICE: found (execute 3): t ... > begin > cmd := ''select 1 from test where fk_item=1324314''; > raise notice ''running: %'', cmd; > > execute cmd; And, no, in the actual situation I cannot get rid of the execute or perform because I need a dynamically generated query. It's just that the example presented here was reduced to a static query that could simply be ran as is, too. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Sep 21, 2006 at 01:15:46PM +0200, Karsten Hilbert wrote: > Assuming EXECUTE does not touch FOUND at all the EXECUTE > part behaves consistently (namely default FOUND=FALSE at the > beginning and later whatever it was after the PERFORM). > > So, what about the PERFORM ? Why does it set FOUND to true? I beleive that since PERFORM doesn't return anything, it sets the FOUND variable to at least indicate whether it did something. OTOH EXECUTE can return something so it doesn't need that. It's a bit wierd, but I think later versions changed EXECUTE to set FOUND also, just to be consistant. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
On Thu, Sep 21, 2006 at 01:32:02PM +0200, Martijn van Oosterhout wrote: > > So, what about the PERFORM ? Why does it set FOUND to true? > > I beleive that since PERFORM doesn't return anything, it sets the FOUND > variable to at least indicate whether it did something. The docs say that it sets FOUND to true if it found rows and discarded them and sets FOUND to false if it did NOT find rows (and thus did not discard any, either). This would indeed make sense. However, reality seems to look different. In the example I posted it *sets* FOUND to true even though it couldn't find any rows. This is counter-intuitive. I just want to make sure this is the intended behaviour (in which case I will have to work around it) or whether it's me misunderstanding something - because it seems counter-intuitive. > It's a bit wierd, but I think later versions changed EXECUTE to set > FOUND also, just to be consistant. That would be good but doesn't influence the PERFORM issue. Thanks anyways, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
On Thu, Sep 21, 2006 at 01:32:02PM +0200, Martijn van Oosterhout wrote: > I beleive that since PERFORM doesn't return anything, it sets the FOUND > variable to at least indicate whether it did something. The weird thing is that in the example it sets FOUND to true even if it did NOT do anything. Setting FOUND to true because it *succeeded* in doing nothing is not helpful because not succeeding would abort the transaction anyways and throw an exception. Regards, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
> gives the result below. It seems inconsistent to me with > regard to the FOUND variable. I would expect FOUND to always > be false regardless of whether I use EXECUTE or PERFORM. I > certainly do not expect it to be true for the third EXECUTE > even assuming that PERFORM may have a bug. What is it that I > am missing out on here ? > With: perform cmd; raise notice ''found (perform): %'', found; You effectively do: select 'select 1 from test where fk_item=1324314' ; Try: perform 1 from test where fk_item=1324314 Greetings Marcin
On Thu, Sep 21, 2006 at 02:50:08PM +0200, Marcin Mank wrote:
> With:
>
> perform cmd;
> raise notice ''found (perform): %'', found;
>
> You effectively do:
> select 'select 1 from test where fk_item=1324314' ;
>
>
> Try:
>
> perform 1 from test where fk_item=1324314
Marcin, you saved my day. I knew I was being stupid
somewhere. It's not like I never used PERFORM before but,
hey, there you go :-))
The docs do hint at how to do it properly:
"PERFORM create_mv(''cs_session_page_requests_mv'', my_query);"
but this might be helpful to be pointed out explicitely:
"PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
Note that the PERFORM replaces the SELECT in the query."
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:
> The docs do hint at how to do it properly:
> "PERFORM create_mv(''cs_session_page_requests_mv'', my_query);"
> but this might be helpful to be pointed out explicitely:
> "PERFORM create_mv(''cs_session_page_requests_mv'', my_query);
> Note that the PERFORM replaces the SELECT in the query."
The 8.0 and up docs do indeed say that.
regards, tom lane