Bad side-effect from making EXPLAIN return a select result

Bad side-effect from making EXPLAIN return a select result

Post by Tom La » Fri, 10 May 2002 04:49:05

In 7.2 and before it would work to do EXPLAIN in a plpgsql function:

regression=# create function foo(int) returns int as '
regression'# begin
regression'# explain select * from tenk1 where unique1  = $1;
regression'# return 1;
regression'# end;' language plpgsql;
regression=# select foo(1);

Index Scan using tenk1_unique1 on tenk1  (cost=0.00..6.00 rows=1 width=148)

(1 row)

which was useful for examining the behavior of the planner with
parameterized queries.

In current CVS tip this doesn't work anymore --- the EXPLAIN executes
just fine, but plpgsql discards the result, and you never get to see it.

Not sure what to do about this.  Probably plpgsql should be tweaked to
do something with EXPLAIN, but what?  Should it treat it like a SELECT?
Or just issue the output as a NOTICE (seems like a step backwards

I'm also strongly tempted to try to make the SQL-language equivalent work:

regression=# create function foo(int) returns setof text as
regression-# 'explain select * from tenk1 where unique1  = $1;'
regression-# language sql;
ERROR:  function declared to return text, but final statement is not a SELECT

                        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


1. Please explain server side cursor vs client side cursor and CacheSize=1

I am using ADO to access MS-SQL server.

I understand server side cursor cause the server to copy some data about
the rows into tempdb
also i have read that client side cursor bypass the tempdb.

My question are:

1.If i am using Recrodset.CacheSize=1 what exactly happen
  to the records..?
2.Can i see the tables which created in tempdb due to query processing...?
3.Two users open server side,read only cursor with the same
   connection property and same SQL query,how many table
   will tempdb create...?

thanks in advance

tomer dror

2. Very NEW....need direction

3. what settings can effect results of SELECT SQL

4. Possible memory leak when HTML attribute exceeds 4K

5. How can i make select that will return only 20 first results instead of all results

6. Multiuser Database Application

7. SQL Server request resulted in a bad return code

8. select count(*) and select * return incoherent result

9. Explain this behavior with NULL return value from select inside if boolean_expression

10. Views (server side) Vs joins (select side)

11. interesting side effect of autocommit = off

12. User-defined Functions and Side-effects