Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs
От | Atri Sharma |
---|---|
Тема | Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs |
Дата | |
Msg-id | CAOeZVifqzpj5yafnB9OqXdAypvBrFhV3Am=Q2GAwjk2JgctyVw@mail.gmail.com обсуждение исходный текст |
Ответы |
Re: Patch to add functionality to specify ORDER BY in CREATE
FUNCTION for SRFs
(David G Johnston <david.g.johnston@gmail.com>)
Re: Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs (Ashutosh Bapat <ashutosh.bapat@enterprisedb.com>) Re: Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs (Michael Paquier <michael.paquier@gmail.com>) Re: Patch to add functionality to specify ORDER BY in CREATE FUNCTION for SRFs (Jeff Janes <jeff.janes@gmail.com>) |
Список | pgsql-hackers |
Hi All,
Please forgive if this is a repost.
Please find attached patch for supporting ORDER BY clause in CREATE FUNCTION for SRFs. Specifically:
CREATE OR REPLACE FUNCTION func1(OUT e int, OUT f int) returns setof record as ' SELECT a,b FROM table1 ORDER BY a; ' language 'sql' ORDER BY e;
This shall allow for capturing information about existing preorder that might be present inherently in the SRF's input or algorithm (the above example and think generate_series).Please forgive if this is a repost.
Please find attached patch for supporting ORDER BY clause in CREATE FUNCTION for SRFs. Specifically:
CREATE OR REPLACE FUNCTION func1(OUT e int, OUT f int) returns setof record as ' SELECT a,b FROM table1 ORDER BY a; ' language 'sql' ORDER BY e;
SELECT * FROM correct_order_singlecol() ORDER BY e; # Does not need to sort by e since existing preorder is known.
EXPLAIN (COSTS OFF) SELECT * FROM correct_order_multicol() ORDER BY e;
QUERY PLAN
-----------------------------------------------
OrderCheck
-> Function Scan on correct_order_multicol
(2 rows)
If order of result rows is not the same as required, an error is raised:
SELECT * FROM incorrect_order_nulls() ORDER BY e NULLS LAST;
ERROR: Order not same as specified
SELECT * FROM incorrect_order_nulls() ORDER BY e NULLS LAST;
ERROR: Order not same as specified
Preorder columns are first transformed into SortGroupClauses first and then stored directly in pg_proc.
This functionality is a user case seen functionality, and is especially useful when SRF inputs are large and/or might be pipelined from another function (SRFs are used in pipelines in analytical systems many times, with large data).
The overhead of this patch is small. A new path is added for the preorder keys, and OrderCheck node's additional cost is pretty low, given that it only compares two rows and stores only a single row (previous row seen), hence the memory footprint is minuscule.
In the inner joins thread, Tom mentioned having a new node which has multiple plans and executor can decide which plan to execute given runtime conditions. I played around with the idea, and am open to experiment having a new node which has a Sort based plan and is executed in case OrderCheck node sees that the inherent order of result tuples is not correct. Feedback here would be very welcome.
I will add the patch to current commitfest.
Thoughts?
Regards,
Regards,
Atri
Вложения
В списке pgsql-hackers по дате отправления: