Re: [HACKERS] Dynamic result sets from procedures

From: Michael Paquier <michael(dot)paquier(at)gmail(dot)com>
To: Daniel Verite <daniel(at)manitou-mail(dot)org>
Cc: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: [HACKERS] Dynamic result sets from procedures
Date: 2017-11-30 04:37:35
Message-ID: CAB7nPqTh1+Cp-Guz3JHUm=ZFiBP5s0LYCPhBaAdv7-iAiAMNCw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Thu, Nov 16, 2017 at 8:27 PM, Daniel Verite <daniel(at)manitou-mail(dot)org> wrote:
> Peter Eisentraut wrote:
>
>> There is also one need error that needs further investigation.
>
> I've looked at this bit in the regression tests about \gexec:
>
> --- a/src/test/regress/expected/psql.out
> +++ b/src/test/regress/expected/psql.out
> @@ -232,11 +232,7 @@ union all
> select 'drop table gexec_test', 'select ''2000-01-01''::date as party_over'
> \gexec
> select 1 as ones
> - ones
> -------
> - 1
> -(1 row)
> -
> +ERROR: DECLARE CURSOR can only be used in transaction blocks
>
> This can be interpreted as two separate errors:
>
> * \gexec ignores the first result
>
> postgres=# select 'select 1','select 2' \gexec
> ?column?
> ----------
> 2
> (1 row)
>
> * \gexec fails with FETCH_COUNT
>
> postgres=# \set FETCH_COUNT 1
> postgres=# select 'select 1','select 2' \gexec
> ERROR: DECLARE CURSOR can only be used in transaction blocks
> ?column?
> ----------
> 2
> (1 row)
>
> The two issues are due to SendQuery() being reentered
> for the gexec'd queries when it hasn't finished yet with the
> main query.
> I believe that just collecting all results of \gexec before
> executing any of them would solve both errors.
>
> Also doing a bit more testing I've seen these other issues:
>
> * combining multiple result sets and FETCH_COUNT doesn't work:
>
> postgres=# \set FETCH_COUNT 1
> postgres=# select 1 \; select 2;
> postgres=#
>
>
> * last error is not recorded for \errverbose :
>
> postgres=# select foo;
> ERROR: column "foo" does not exist
> LINE 1: select foo;
> ^
> postgres=# \errverbose
> There is no previous error.
>
> * memory leaks on PGResults.

Peter, are you planning to answer to those complains? For now I am
switching the patch as returned with feedback as this thread has no
activity for two weeks.
--
Michael

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Geoghegan 2017-11-30 04:39:20 Re: [HACKERS] INSERT .. ON CONFLICT DO SELECT [FOR ..]
Previous Message Michael Paquier 2017-11-30 04:35:33 Re: [HACKERS] Support to COMMENT ON DATABASE CURRENT_DATABASE