Обсуждение: polymorphic table functions light

I want to address the issue that calling a record-returning function 
always requires specifying a  result column list, even though there are 
cases where the function could be self-aware enough to know the result 
column list of a particular call.  For example, most of the functions in 
contrib/tablefunc are like that.

SQL:2016 has a feature called polymorphic table functions (PTF) that 
addresses this.  The full PTF feature is much larger, so I just carved 
out this particular piece of functionality.  Here is a link to some 
related information: 
https://modern-sql.com/blog/2018-11/whats-new-in-oracle-database-18c#ptf

The idea is that you attach a helper function to the main function.  The 
helper function is called at parse time with the constant arguments of 
the main function call and can compute a result row description (a 
TupleDesc in our case).

Example from the patch:

CREATE FUNCTION connectby_describe(internal)
RETURNS internal
AS 'MODULE_PATHNAME', 'connectby_describe'
LANGUAGE C;

CREATE FUNCTION connectby(text,text,text,text,int,text)
RETURNS setof record
DESCRIBE WITH connectby_describe
AS 'MODULE_PATHNAME','connectby_text'
LANGUAGE C STABLE STRICT;

(The general idea is very similar to Pavel's patch "parse time support 
function"[0] but addressing a disjoint problem.)

The original SQL:2016 syntax is a bit different: There, you'd first 
create two separate functions: a "describe" and a "fulfill" and then 
create the callable PTF referencing those two (similar to how an 
aggregate is composed of several component functions).  I think 
deviating from this makes some sense because we can then more easily 
"upgrade" existing record-returning functions with this functionality.

Another difference is that AFAICT, the standard specifies that if the 
describe function cannot resolve the call, the call fails.  Again, in 
order to be able to upgrade existing functions (instead of having to 
create a second set of functions with a different name), I have made it 
so that you can still specify an explicit column list if the describe 
function does not succeed.

In this prototype patch, I have written the C interface and several 
examples using existing functions in the source tree.  Eventually, I'd 
like to also add PL-level support for this.

Thoughts so far?


[0]: 
https://www.postgresql.org/message-id/flat/CAFj8pRARh+r4=HNwQ+hws-D6msus01Dw_6zjNYur6tPk1+W0rA@mail.gmail.com

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Peter Eisentraut <peter.eisentraut@2ndquadrant.com> writes:
> I want to address the issue that calling a record-returning function 
> always requires specifying a  result column list, even though there are 
> cases where the function could be self-aware enough to know the result 
> column list of a particular call.  For example, most of the functions in 
> contrib/tablefunc are like that.

Seems like a reasonable goal.

> SQL:2016 has a feature called polymorphic table functions (PTF) that 
> addresses this.  The full PTF feature is much larger, so I just carved 
> out this particular piece of functionality.  Here is a link to some 
> related information: 
> https://modern-sql.com/blog/2018-11/whats-new-in-oracle-database-18c#ptf

> The idea is that you attach a helper function to the main function.  The 
> helper function is called at parse time with the constant arguments of 
> the main function call and can compute a result row description (a 
> TupleDesc in our case).

> Example from the patch:

> CREATE FUNCTION connectby_describe(internal)
> RETURNS internal
> AS 'MODULE_PATHNAME', 'connectby_describe'
> LANGUAGE C;

> CREATE FUNCTION connectby(text,text,text,text,int,text)
> RETURNS setof record
> DESCRIBE WITH connectby_describe
> AS 'MODULE_PATHNAME','connectby_text'
> LANGUAGE C STABLE STRICT;

> (The general idea is very similar to Pavel's patch "parse time support 
> function"[0] but addressing a disjoint problem.)

Hm.  Given that this involves a function-taking-and-returning-internal,
I think it's fairly silly to claim that it is implementing a SQL-standard
feature, or even a subset or related feature.  Nor do I see a pathway
whereby this might end in a feature you could use without writing C code.

That being the case, I'm not in favor of using up SQL syntax space for it
if we don't have to.  Moreover, this approach requires a whole lot of
duplicative-seeming new infrastructure, such as a new pg_proc column.
And you're not even done yet --- where's the pg_dump support?

I think we'd be better off to address this by extending the existing
"support function" infrastructure by inventing a new support request type,
much as Pavel's patch did.  I've not gotten around to reviewing the latest
version of his patch, so I'm not sure if it provides enough flexibility to
solve this particular problem, or if we'd need a different request type
than he proposes.  But I'd rather go down that path than this one.
It should provide the same amount of functionality with a whole lot less
overhead code.

            regards, tom lane

On 16/12/2019 22:13, Tom Lane wrote:
> That being the case, I'm not in favor of using up SQL syntax space for it
> if we don't have to.


Do I understand correctly that you are advocating *against* using
standard SQL syntax for a feature that is defined by the SQL Standard
and that we have no similar implementation for?


If so, I would like to stand up to it.  We are known as (at least one
of) the most conforming implementations and I hope we will continue to
be so.  I would rather we remove from rather than add to this page:
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard

-- 

Vik Fearing

Vik Fearing <vik.fearing@2ndquadrant.com> writes:
> On 16/12/2019 22:13, Tom Lane wrote:
>> That being the case, I'm not in favor of using up SQL syntax space for it
>> if we don't have to.

> Do I understand correctly that you are advocating *against* using
> standard SQL syntax for a feature that is defined by the SQL Standard
> and that we have no similar implementation for?

My point is that what Peter is proposing is exactly *not* the standard's
feature.  We generally avoid using up standard syntax for not-standard
semantics, especially if there's any chance that somebody might come along
and build a more-conformant version later.  (Having said that, I had the
impression that what he was proposing wasn't the standard's syntax either,
but just a homegrown CREATE FUNCTION addition.  I don't really see the
point of doing it like that when we can do it below the level of SQL.)

            regards, tom lane

On 2019-12-16 19:53, Peter Eisentraut wrote:
> SQL:2016 has a feature called polymorphic table functions (PTF) that
> addresses this.  The full PTF feature is much larger, so I just carved
> out this particular piece of functionality.  Here is a link to some
> related information:
> https://modern-sql.com/blog/2018-11/whats-new-in-oracle-database-18c#ptf
> 
> The idea is that you attach a helper function to the main function.  The
> helper function is called at parse time with the constant arguments of
> the main function call and can compute a result row description (a
> TupleDesc in our case).

Here is an updated patch for the record, since the previous patch had 
accumulated some significant merge conflicts.

I will reply to the discussions elsewhere in the thread.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-12-16 22:13, Tom Lane wrote:
> Hm.  Given that this involves a function-taking-and-returning-internal,
> I think it's fairly silly to claim that it is implementing a SQL-standard
> feature, or even a subset or related feature.  Nor do I see a pathway
> whereby this might end in a feature you could use without writing C code.

> I think we'd be better off to address this by extending the existing
> "support function" infrastructure by inventing a new support request type,

I definitely want to make it work in a way that does not require writing 
C code.  My idea was to create a new type, perhaps called "descriptor", 
that represents essentially a tuple descriptor.  (It could be exactly a 
TupleDesc, as this patch does, or something similar.)  For the sake of 
discussion, we could use JSON as the text representation of this.  Then 
a PL/pgSQL function or something else high level could easily be written 
to assemble this.  Interesting use cases are for example in the area of 
using PL/Perl or PL/Python for unpacking some serialization format using 
existing modules in those languages.

The SQL standard has the option of leaving the call signatures of the 
PTF support functions implementation defined, so this approach would 
appear to be within the spirit of the specification.

Obviously, there is a lot of leg work to be done between here and there, 
but it seems doable.  The purpose of this initial patch submission was 
to get some opinions on the basic idea of "determine result tuple 
structure by calling helper function at parse time", and so far no one 
has fallen off their chair from that, so I'm encouraged. ;-)

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-12-20 01:30, Vik Fearing wrote:
> On 16/12/2019 22:13, Tom Lane wrote:
>> That being the case, I'm not in favor of using up SQL syntax space for it
>> if we don't have to.
> 
> Do I understand correctly that you are advocating *against* using
> standard SQL syntax for a feature that is defined by the SQL Standard
> and that we have no similar implementation for?

On the question of using SQL syntax or not for this, there are a couple 
of arguments I'm considering.

First, the SQL standard explicitly permits not implementing the exact 
signatures of the PTF component procedures; see feature code B208. 
While this does not literally permit diverging on the CREATE FUNCTION 
syntax, it's clear that they expect that the creation side of this will 
have some incompatibilities.  The existing practices of other vendors 
support this observation.  What's more interesting in practice is making 
the invocation side compatible.

Second, set-returning functions in PostgreSQL already exist and in my 
mind it would make sense to make this feature work with existing 
functions or allow easy "upgrades" rather than introducing another 
completely new syntax to do something very similar to what already 
exists.  This wouldn't be a good user experience.  And the full standard 
syntax is also complicated and different enough that it wouldn't be 
trivial to add.

But I'm open to other ideas.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

> On 24 Jan 2020, at 08:27, Peter Eisentraut <peter.eisentraut@2ndquadrant.com> wrote:
>
> I definitely want to make it work in a way that does not require writing C code.  My idea was to create a new type,
perhapscalled "descriptor", that represents essentially a tuple descriptor.  (It could be exactly a TupleDesc, as this
patchdoes, or something similar.)  For the sake of discussion, we could use JSON as the text representation of this.
Thena PL/pgSQL function or something else high level could easily be written to assemble this.  Interesting use cases
arefor example in the area of using PL/Perl or PL/Python for unpacking some serialization format using existing modules
inthose languages. 

I do think it’s very desirable to make it usable outside of C code.

> Obviously, there is a lot of leg work to be done between here and there, but it seems doable.  The purpose of this
initialpatch submission was to get some opinions on the basic idea of "determine result tuple structure by calling
helperfunction at parse time", and so far no one has fallen off their chair from that, so I'm encouraged. ;-) 

I’m interested in this development, as it makes RECORD-returning SRFs in the SELECT list a viable proposition, and that
inturn allows a ValuePerCall SRF to get meaningful benefit from pipelining. (They could always pipeline, but there is
noway to extract information from the RECORD that’s returned, with the sole exception of row_to_json.) 

I couldn’t check out that it would work though because I couldn’t apply the v2 (or v1) patch against either 12.0 or
530609a(which I think was sometime around 25th Jan). Against 12.0, I got a few rejections (prepjointree.c and
clauses.c).I figured they might be inconsequential, but no: initdb then fails at CREATE VIEW pg_policies. Different
rejectionsagainst 530609a, but still initdb fails. 

But I’m definitely very much encouraged.

denty.