public inbox for [email protected]
help / color / mirror / Atom feedThe document contains false statements
5+ messages / 4 participants
[nested] [flat]
* The document contains false statements
@ 2025-09-13 20:32 PG Doc comments form <[email protected]>
0 siblings, 2 replies; 5+ messages in thread
From: PG Doc comments form @ 2025-09-13 20:32 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/sql-createfunction.html
Description:
> This is effectively another way of declaring a named OUT parameter
Actually OUT works twice slower in compare to RETURNS TABLE
https://stackoverflow.com/q/79763947/4632019
DB<>fiddle for [`OUT`](https://dbfiddle.uk/fz9L_wm0) and [`RETURNS
TABLE`](https://dbfiddle.uk/uTkU1MT8) cases.
*I hope after the fix, data centers will consume 2 times less electricity
:D.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The document contains false statements
@ 2025-09-13 23:25 Tom Lane <[email protected]>
parent: PG Doc comments form <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2025-09-13 23:25 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
PG Doc comments form <[email protected]> writes:
> The following documentation comment has been logged on the website:
> Page: https://www.postgresql.org/docs/17/sql-createfunction.html
> Description:
>> This is effectively another way of declaring a named OUT parameter
> Actually OUT works twice slower in compare to RETURNS TABLE
You stopped reading halfway through that sentence... it says
This is effectively another way of declaring a named OUT parameter,
except that RETURNS TABLE also implies RETURNS SETOF.
The difference between a set-returning and not-set-returning
function might explain what you're seeing, particularly because
the rules for inlining SQL functions are different in the two
cases.
A documentation comment is not the place to pursue this further,
but you could ask for help on our pgsql-performance mailing list.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The document contains false statements
@ 2025-09-13 23:26 David G. Johnston <[email protected]>
parent: PG Doc comments form <[email protected]>
1 sibling, 1 reply; 5+ messages in thread
From: David G. Johnston @ 2025-09-13 23:26 UTC (permalink / raw)
To: [email protected] <[email protected]>; [email protected] <[email protected]>
On Saturday, September 13, 2025, PG Doc comments form <
[email protected]> wrote:
> The following documentation comment has been logged on the website:
>
> Page: https://www.postgresql.org/docs/17/sql-createfunction.html
> Description:
>
> > This is effectively another way of declaring a named OUT parameter
>
> Actually OUT works twice slower in compare to RETURNS TABLE
> https://stackoverflow.com/q/79763947/4632019
>
> DB<>fiddle for [`OUT`](https://dbfiddle.uk/fz9L_wm0) and [`RETURNS
> TABLE`](https://dbfiddle.uk/uTkU1MT8) cases.
>
> *I hope after the fix, data centers will consume 2 times less electricity
>
>
The statement is not false - it contains an “except” clause that you’ve
ignored which makes it true for exactly this reason. The fact you are
comparing a set-returning function to one that doesn’t return a set has
invalidated the test.
The fundamental issue here is “select (composite_func()).*” where the
function is not set-returning if known to be broken - the “*” expansion
during planning results in the function being executed multiple times once
for each output column. (I may be missing some nuances here as, since the
inclusion of lateral joins, this almost never comes up anymore.)
Non-trivial function calls should be placed in the FROM clause of a query;
in part to ensure avoidance of this problematic behavior.
This is not at all limited to RLS.
In short, I don’t know how to improve the documentation to prevent people
from writing bad queries of this type. Concrete suggestions are welcome,
but removing this sentence, or re-wording it, doesn’t seem like it would
make any difference.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The document contains false statements
@ 2025-09-16 20:55 Eugen Konkov <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Eugen Konkov @ 2025-09-16 20:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected]
Hello Tom,
Saturday, September 13, 2025, 7:25:08 PM, you wrote:
> PG Doc comments form <[email protected]> writes:
>> The following documentation comment has been logged on the website:
>> Page: https://www.postgresql.org/docs/17/sql-createfunction.html
>> Description:
>>> This is effectively another way of declaring a named OUT parameter
>> Actually OUT works twice slower in compare to RETURNS TABLE
> You stopped reading halfway through that sentence... it says
> This is effectively another way of declaring a named OUT parameter,
> except that RETURNS TABLE also implies RETURNS SETOF.
> The difference between a set-returning and not-set-returning
> function might explain what you're seeing, particularly because
> the rules for inlining SQL functions are different in the two
> cases.
> A documentation comment is not the place to pursue this further,
> but you could ask for help on our pgsql-performance mailing list.
> regards, tom lane
Let me continue how to improve this in David G. thread.
--
Best regards,
Eugen Konkov
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: The document contains false statements
@ 2025-09-16 22:39 Eugen Konkov <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 5+ messages in thread
From: Eugen Konkov @ 2025-09-16 22:39 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; [email protected] <[email protected]>
Hello David,
Saturday, September 13, 2025, 7:26:21 PM, you wrote:
> On Saturday, September 13, 2025, PG Doc comments form <[email protected]> wrote:
> The following documentation comment has been logged on the website:
> Page: https://www.postgresql.org/docs/17/sql-createfunction.html
> Description:
>> This is effectively another way of declaring a named OUT parameter
> Actually OUT works twice slower in compare to RETURNS TABLE
> https://stackoverflow.com/q/79763947/4632019
> DB<>fiddle for [`OUT`](https://dbfiddle.uk/fz9L_wm0) and [`RETURNS
> TABLE`](https://dbfiddle.uk/uTkU1MT8) cases.
> *I hope after the fix, data centers will consume 2 times less electricity
> The statement is not false - it contains an “except” clause that
> you’ve ignored which makes it true for exactly this reason. The
> fact you are comparing a set-returning function to one that doesn’t
> return a set has invalidated the test.
> The fundamental issue here is “select (composite_func()).*” where
> the function is not set-returning if known to be broken - the “*”
> expansion during planning results in the function being executed
> multiple times once for each output column. (I may be missing some
> nuances here as, since the inclusion of lateral joins, this almost never comes up anymore.)
> Non-trivial function calls should be placed in the FROM clause of a
> query; in part to ensure avoidance of this problematic behavior.
> This is not at all limited to RLS.
> In short, I don’t know how to improve the documentation to prevent
> people from writing bad queries of this type. Concrete suggestions
> are welcome, but removing this sentence, or re-wording it, doesn’t
> seem like it would make any difference.
> David J.
Thanks for more information on this. You and Tom both pointed me to `RETURNS SET OF` part. I agree with Tom that documentation is not the place to teach user how to write SQL.
But I would appeal that the documentation should be meaning full. And for me the part after "except" looks the same as it would be written in Arabic "إرجاع مجموعة من" (RETURNS SETOF).
The question from David: How it could be done better? is good. Let me describe how I see it from my point of view and experience.
The documentation above highlights as RETURNS SETOF as something special. I never used SETOF. I tried to google and find almost nothing in the official documentation, except these two
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET
https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE
with just a mention that it can return 0, 1 or more rows.
And the most informative one is https://www.postgresql.org/docs/current/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS
The latest one my my mind should be reffered from https://www.postgresql.org/docs/current/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-TABLE , Eg.
Please read 7.2.1.4 Table Functions for more information.
For the original problem:
>The name of an output column in the RETURNS TABLE syntax. This is effectively another way of declaring a named OUT parameter,
> except that RETURNS TABLE also implies RETURNS SETOF.
My proposition is to add link to 7.2.1.4 Table Functions for more information and extend "7.2.1.4 Table Functions for more information" with the information
that "in certain circumstances RETURNS TABLE is a subject for optimizer and could be inlined https://wiki.postgresql.org/wiki/Inlining_of_SQL_functions#Inlining_conditions_for_table_functions&#...;.
Having this it would be clear that `RETURNS TABLE` and `RETURNS SETOF` are sort of fuctions which are called Table functions.
Probably official documentation should have in future a section somewhere "How to optimize your queries" and links to benchmarks like this I did in my question on SO:
https://pastebin.com/n3sxBxt6
https://dbfiddle.uk/xfy-qw75
Without this information and statement that these two: OUT and RETURNS TABLE are just taste of syntax, users will use either without knowing consequences. (like me until I benchmarked it)
--
Best regards,
Eugen Konkov
--
Best regards,
Eugen Konkov
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-09-16 22:39 UTC | newest]
Thread overview: 5+ messages (download: mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2025-09-13 20:32 The document contains false statements PG Doc comments form <[email protected]>
2025-09-13 23:25 ` Tom Lane <[email protected]>
2025-09-16 20:55 ` Eugen Konkov <[email protected]>
2025-09-13 23:26 ` David G. Johnston <[email protected]>
2025-09-16 22:39 ` Eugen Konkov <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox