Re: BUG #1592: "with hold" cursor problem

On 2005-04-10, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Gabor Berenyi" <ber_@freemail.hu> writes:
>> begin;
>> declare t cursor with hold for select function() as x from R;
>> commit;
>
>> Documentation says that the rows represented by a held cursor are copied
>> into a temporary file or memory area, but they are not, if they are results
>> of a function call. Instead, "commit" aborts the connection.
>
> Works for me.  Could we see a complete, self-contained example?
> In particular I suppose that the details of function() might be
> relevant.

Someone mentioned this on IRC; I was going to post an analysis but I got
sidetracked with other stuff. The critical detail is that function()
has to be an SQL function declared stable (or possibly immutable, not
sure) and it must invoke a volatile function such as timeofday(). The
crash happens in CopySnapshot trying to copy a null snapshot.

Obviously the function definition is in itself illegal, so the workaround
is "don't do that", but the backend should probably do something more
useful than segfaulting.

Complete example:

test=# create function crashme() returns text as 'select timeofday()'
test-#   language sql stable;
CREATE FUNCTION
test=# begin;
BEGIN
test=# declare t cursor with hold for select crashme() as x from pg_class;
DECLARE CURSOR
test=# commit;
server closed the connection unexpectedly

--
Andrew, Supernews
http://www.supernews.com - individual and corporate NNTP services