execute/perform and FOUND

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/

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