Re: BUG #16040: PL/PGSQL RETURN QUERY statement never uses a parallel plan

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: jeremy(at)musicsmith(dot)net
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Re: BUG #16040: PL/PGSQL RETURN QUERY statement never uses a parallel plan
Date: 2020-06-09 00:11:43
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-bugs pgsql-hackers

I wrote:
> ... attached is a draft patch to enable that. By getting rid of the
> intermediate SPITupleTable, this should improve the performance of
> RETURN QUERY somewhat even without considering the possibility of
> parallelizing the source query. I've not tried to measure that though.
> I've also not looked for other places that could use this new
> infrastructure, but there may well be some.

> One thing I'm not totally pleased about with this is adding another
> SPI interface routine using the old parameter-values API (that is,
> null flags as char ' '/'n'). That was the path of least resistance
> given the other moving parts in pl_exec.c and spi.c, but maybe we
> should try to modernize that before we set it in stone.

Here's a revised patch that does the additional legwork needed to
use ParamListInfo throughout the newly-added code. I was able to
get pl_exec.c out of the business of using old-style null flags
entirely, which seems like a nice improvement.

> Another thing standing between this patch and committability is suitable
> additions to the SPI documentation. But I saw no value in writing that
> before the previous point is settled.

Took care of that too.

I looked around for other places that could use this infrastructure.
It turns out that most places that are fetching via SPITupleTables
don't really have much of an issue, because they are only expecting
to get one or so tuples anyway. There are a few where it might be
worth changing, but it's hard to get really excited because they all
have other constraints on the max amount of data. As an example,
the various table-to-xml thingies in utils/adt/xml.c could be converted,
but they're still funneling their output into an XML string. As long
as that has a hard limit at 1GB, it's not very realistic to expect that
you can shove huge tables into it.

A different sort of cleanup we could undertake is to deprecate and
eventually remove some of the SPI API functions. As of this patch,
for example, SPI_cursor_open_with_args and SPI_execute_with_args are
unused anywhere in our code. But since we document them, it's hard
to guess whether any external code is relying on them. I suppose
deprecation would be a multi-year project in any case.

I think this is committable now. Any objections?

regards, tom lane

Attachment Content-Type Size
plpgsql-return-query-results-directly-2.patch text/x-diff 45.2 KB

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Peter Geoghegan 2020-06-09 01:26:01 Re: Potential G2-item cycles under serializable isolation
Previous Message Michael Hudson-Doyle 2020-06-09 00:04:00 pg_filedump tests fail on big endian systems

Browse pgsql-hackers by date

  From Date Subject
Next Message Melanie Plageman 2020-06-09 00:12:25 Re: Avoiding hash join batch explosions with extreme skew and weird stats
Previous Message Tom Lane 2020-06-08 23:00:26 Re: Remove SpinLockFree() / S_LOCK_FREE()?