Date: Mon, 15 Apr 2024 17:24:31 +0200
From: Jan Behrens <jbe-mlist@magnetkern.de>
To: pgsql-general@lists.postgresql.org
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: (When) can a single SQL statement return multiple result sets?
Message-Id: <20240415172431.161a1f95abeca4a21a7ff57e@magnetkern.de>
In-Reply-To: <7504.1712790168@sss.pgh.pa.us>
References: <20240410232247.b48cdf2677f87d37b167e140@magnetkern.de>
	<7504.1712790168@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit
Archived-At: <https://www.postgresql.org/message-id/20240415172431.161a1f95abeca4a21a7ff57e%40magnetkern.de>
Precedence: bulk

On Wed, 10 Apr 2024 19:02:48 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Jan Behrens <jbe-mlist@magnetkern.de> writes:
> > While writing a PostgreSQL client library for Lua supporting
> > Pipelining (using PQsendQueryParams), I have been wondering if there
> > are any single SQL commands that return multiple result sets.
> 
> Right now, I don't think so.  I believe the current protocol design
> intends to support that, and I think this may trace back to some
> ancient idea at Berkeley that if you select from an inheritance
> hierarchy where the child tables aren't all alike, you should be
> able to see all the child data, which'd require changing tuple
> descriptors midstream.  But our current interpretation of SQL
> SELECT forbids that.

I thought multiple result sets are supported for commands like PQexec,
where "Multiple queries sent in a single PQexec call" are explictly
supported, and which then return multiple result set. This, however,
doesn't apply to pipelining because PQexec is not available in
pipelining mode.

> 
> > Here, "DELETE FROM magic" returns multiple result sets, even though it
> > is only a single SQL statement.
> 
> Right, so it's kind of a case that you have to support.  We're not
> likely to rip out rules anytime soon, even if they're a bit
> deprecated.

As it seems to be a corner case that rarely occurs in practice, I was
considering to simply not support this case in my client library. I
don't know which SQL error code I could return in that case though.
Maybe "0A000" (feature_not_supported) or
"21000" (cardinality_violation). Not sure if either of those is a good
choice. Any better idea?

> 
> > The case outlined above seems to be a somewhat special case. I haven't
> > found any other way to return multiple results (other than sending
> > several semicolon-separated statements, which is not supported by
> > PQsendQueryParams). So is there any (other) case where I reasonably
> > should expect several result sets returned by PQgetResult (before
> > PQgetResult returns NULL)? Wouldn't it make sense to disallow such
> > behavior altogether?
> 
> No.  For one thing, there's too much overlap between what you're
> suggesting and pipelined queries.

To which question was "no" the answer to. I'm not sure if I understand.

> 
> 			regards, tom lane
> 

Regards,
Jan Behrens