Thread: poc - possibility to write window function in PL languages
Hi
I wrote a proof concept for the support window function from plpgsql.
Window function API - functions named WinFuncArg* are polymorphic and it is not easy to wrap these functions for usage from SQL level. I wrote an enhancement of the GET statement - for this case GET WINDOW_CONTEXT, that allows safe and fast access to the result of these functions.
Custom variant of row_number can look like:
create or replace function pl_row_number()
returns bigint as $$
declare pos int8;
begin
pos := get_current_position(windowobject);
pos := pos + 1;
perform set_mark_position(windowobject, pos);
return pos;
end
$$
language plpgsql window;
returns bigint as $$
declare pos int8;
begin
pos := get_current_position(windowobject);
pos := pos + 1;
perform set_mark_position(windowobject, pos);
return pos;
end
$$
language plpgsql window;
Custom variant of lag function can look like:
create or replace function pl_lag(numeric)
returns numeric as $$
declare
v numeric;
begin
perform get_input_value_in_partition(windowobject, 1, -1, 'seek_current', false);
get pg_window_context v = PG_INPUT_VALUE;
return v;
end;
$$ language plpgsql window;
returns numeric as $$
declare
v numeric;
begin
perform get_input_value_in_partition(windowobject, 1, -1, 'seek_current', false);
get pg_window_context v = PG_INPUT_VALUE;
return v;
end;
$$ language plpgsql window;
Custom window functions can be used for generating missing data in time series
create table test_missing_values(id int, v integer);
insert into test_missing_values values(1,10),(2,11),(3,12),(4,null),(5,null),(6,15),(7,16);
create or replace function pl_pcontext_test(numeric)
returns numeric as $$
declare
n numeric;
v numeric;
begin
perform get_input_value_for_row(windowobject, 1);
get pg_window_context v = PG_INPUT_VALUE;
if v is null then
v := get_partition_context_value(windowobject, null::numeric);
else
perform set_partition_context_value(windowobject, v);
end if;
return v;
end
$$
language plpgsql window;
insert into test_missing_values values(1,10),(2,11),(3,12),(4,null),(5,null),(6,15),(7,16);
create or replace function pl_pcontext_test(numeric)
returns numeric as $$
declare
n numeric;
v numeric;
begin
perform get_input_value_for_row(windowobject, 1);
get pg_window_context v = PG_INPUT_VALUE;
if v is null then
v := get_partition_context_value(windowobject, null::numeric);
else
perform set_partition_context_value(windowobject, v);
end if;
return v;
end
$$
language plpgsql window;
select id, v, pl_pcontext_test(v) over (order by id) from test_missing_values;
id | v | pl_pcontext_test.
----+----+------------------
1 | 10 | 10
2 | 11 | 11
3 | 12 | 12
4 | | 12
5 | | 12
6 | 15 | 15
7 | 16 | 16
(7 rows)
I think about another variant for WinFuncArg functions where polymorphic argument is used similarly like in get_partition_context_value - this patch is prototype, but it works and I think so support of custom window functions in PL languages is possible and probably useful.
Comments, notes, ideas, objections?
Regards
Pavel
Attachment
Hi
I simplified access to results of winfuncargs functions by proxy type "typedvalue". This type can hold any Datum value, and allows fast cast to basic buildin types or it can use (slower) generic cast functions. It is used in cooperation with a plpgsql assign statement that can choose the correct cast implicitly. When the winfuncarg function returns a value of the same type, that is expected by the variable on the left side of the assign statement, then (for basic types), the value is just copied without casts. With this proxy type is not necessary to have special statement for assigning returned value from winfuncargs functions, so source code of window function in plpgsql looks intuitive to me.
Example - implementation of "lag" function in plpgsql
create or replace function pl_lag(numeric)
returns numeric as $$
declare v numeric;
begin
v := get_input_value_in_partition(windowobject, 1, -1, 'seek_current', false);
return v;
end;
$$ language plpgsql window;
returns numeric as $$
declare v numeric;
begin
v := get_input_value_in_partition(windowobject, 1, -1, 'seek_current', false);
return v;
end;
$$ language plpgsql window;
I think this code is usable, and I assign this patch to commitfest.
Regards
Pavel
Attachment
st 26. 8. 2020 v 17:06 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
HiI simplified access to results of winfuncargs functions by proxy type "typedvalue". This type can hold any Datum value, and allows fast cast to basic buildin types or it can use (slower) generic cast functions. It is used in cooperation with a plpgsql assign statement that can choose the correct cast implicitly. When the winfuncarg function returns a value of the same type, that is expected by the variable on the left side of the assign statement, then (for basic types), the value is just copied without casts. With this proxy type is not necessary to have special statement for assigning returned value from winfuncargs functions, so source code of window function in plpgsql looks intuitive to me.Example - implementation of "lag" function in plpgsqlcreate or replace function pl_lag(numeric)
returns numeric as $$
declare v numeric;
begin
v := get_input_value_in_partition(windowobject, 1, -1, 'seek_current', false);
return v;
end;
$$ language plpgsql window;I think this code is usable, and I assign this patch to commitfest.RegardsPavel
fix regress tests and some doc
Attachment
pá 28. 8. 2020 v 8:14 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
st 26. 8. 2020 v 17:06 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:HiI simplified access to results of winfuncargs functions by proxy type "typedvalue". This type can hold any Datum value, and allows fast cast to basic buildin types or it can use (slower) generic cast functions. It is used in cooperation with a plpgsql assign statement that can choose the correct cast implicitly. When the winfuncarg function returns a value of the same type, that is expected by the variable on the left side of the assign statement, then (for basic types), the value is just copied without casts. With this proxy type is not necessary to have special statement for assigning returned value from winfuncargs functions, so source code of window function in plpgsql looks intuitive to me.Example - implementation of "lag" function in plpgsqlcreate or replace function pl_lag(numeric)
returns numeric as $$
declare v numeric;
begin
v := get_input_value_in_partition(windowobject, 1, -1, 'seek_current', false);
return v;
end;
$$ language plpgsql window;I think this code is usable, and I assign this patch to commitfest.RegardsPavelfix regress tests and some doc
update - refactored implementation typedvalue type
Attachment
Hi
rebase
Regards
Pavel
Attachment
Hi, Pavel:
Happy New Year.
+ command with clause <literal>WINDOW</literal>. The specific feature of
+ this functions is a possibility to two special storages with
+ this functions is a possibility to two special storages with
this functions -> this function
possibility to two special storages: there is no verb.
'store with stored one value': store is repeated.
+ * Portions Copyright (c) 1996-2020, PostgreSQL Global Development Group
It would be better to change 2020 to 2021 in the new files.
For some functions, such as windowobject_get_func_arg_frame, it would be better to add comment explaining their purposes.
For estimate_partition_context_size():
+ errmsg("size of value is greather than limit (1024 bytes)")));
Please include the value of typlen in the message. There is similar error message in the else block where value of size should be included.
+ return *realsize;
+ }
+ else
+ }
+ else
The 'else' is not needed since the if block ends with return.
+ size += size / 3;
Please add a comment for the choice of constant 3.
+ /* by default we allocate 30 bytes */
+ *realsize = 0;
+ *realsize = 0;
The value 30 may not be accurate - from the caller:
+ if (PG_ARGISNULL(2))
+ minsize = VARLENA_MINSIZE;
+ else
+ minsize = PG_GETARG_INT32(2);
+ minsize = VARLENA_MINSIZE;
+ else
+ minsize = PG_GETARG_INT32(2);
VARLENA_MINSIZE is 32.
Cheers
On Fri, Jan 1, 2021 at 3:29 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
HirebaseRegardsPavel
Hi
pá 1. 1. 2021 v 18:57 odesílatel Zhihong Yu <zyu@yugabyte.com> napsal:
Hi, Pavel:Happy New Year.+ command with clause <literal>WINDOW</literal>. The specific feature of
+ this functions is a possibility to two special storages withthis functions -> this functionpossibility to two special storages: there is no verb.'store with stored one value': store is repeated.+ * Portions Copyright (c) 1996-2020, PostgreSQL Global Development GroupIt would be better to change 2020 to 2021 in the new files.
fixed
For some functions, such as windowobject_get_func_arg_frame, it would be better to add comment explaining their purposes.
It is commented before. These functions just call WinAPI functions
/*
* High level access function. These functions are wrappers for windows API
* for PL languages based on usage WindowObjectProxy.
*/
* High level access function. These functions are wrappers for windows API
* for PL languages based on usage WindowObjectProxy.
*/
For estimate_partition_context_size():+ errmsg("size of value is greather than limit (1024 bytes)")));Please include the value of typlen in the message. There is similar error message in the else block where value of size should be included.+ return *realsize;
+ }
+ elseThe 'else' is not needed since the if block ends with return.
yes, but it is there for better readability (symmetry)
+ size += size / 3;Please add a comment for the choice of constant 3.+ /* by default we allocate 30 bytes */
+ *realsize = 0;The value 30 may not be accurate - from the caller:+ if (PG_ARGISNULL(2))
+ minsize = VARLENA_MINSIZE;
+ else
+ minsize = PG_GETARG_INT32(2);VARLENA_MINSIZE is 32.CheersOn Fri, Jan 1, 2021 at 3:29 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:HirebaseRegardsPavel
I am sending updated patch
Thank you for comments
Regards
Pavel
Attachment
Hi, Pavel:
Thanks for the update.
I don't have other comment.
Cheers
On Mon, Jan 4, 2021 at 3:15 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:
Hipá 1. 1. 2021 v 18:57 odesílatel Zhihong Yu <zyu@yugabyte.com> napsal:Hi, Pavel:Happy New Year.+ command with clause <literal>WINDOW</literal>. The specific feature of
+ this functions is a possibility to two special storages withthis functions -> this functionpossibility to two special storages: there is no verb.'store with stored one value': store is repeated.+ * Portions Copyright (c) 1996-2020, PostgreSQL Global Development GroupIt would be better to change 2020 to 2021 in the new files.fixedFor some functions, such as windowobject_get_func_arg_frame, it would be better to add comment explaining their purposes.It is commented before. These functions just call WinAPI functions/*
* High level access function. These functions are wrappers for windows API
* for PL languages based on usage WindowObjectProxy.
*/For estimate_partition_context_size():+ errmsg("size of value is greather than limit (1024 bytes)")));Please include the value of typlen in the message. There is similar error message in the else block where value of size should be included.+ return *realsize;
+ }
+ elseThe 'else' is not needed since the if block ends with return.yes, but it is there for better readability (symmetry)+ size += size / 3;Please add a comment for the choice of constant 3.+ /* by default we allocate 30 bytes */
+ *realsize = 0;The value 30 may not be accurate - from the caller:+ if (PG_ARGISNULL(2))
+ minsize = VARLENA_MINSIZE;
+ else
+ minsize = PG_GETARG_INT32(2);VARLENA_MINSIZE is 32.CheersOn Fri, Jan 1, 2021 at 3:29 AM Pavel Stehule <pavel.stehule@gmail.com> wrote:HirebaseRegardsPavelI am sending updated patchThank you for commentsRegardsPavel
Pavel Stehule <pavel.stehule@gmail.com> writes:
> [ plpgsql-window-functions-20210104.patch.gz ]
I spent some time looking at this patch. It would certainly be
appealing to have some ability to write custom window functions
without descending into C; but I'm not very happy about the details.
I'm okay with the idea of having a special variable of a new pseudotype.
That's not exactly pretty, but it descends directly from how we handle
the arguments of trigger functions, so at least there's precedent.
What's bugging me though is the "typedvalue" stuff. That seems like a
conceptual mess, a performance loss, and a permanent maintenance time
sink. To avoid performance complaints, eventually this hard-wired set
of conversions would have to bloom to cover every built-in cast, and
as for extension types, you're just out of luck.
One way to avoid that would be to declare the argument-fetching
functions as polymorphics with a dummy argument that just provides
the expected result type. So users would write something like
create function pl_lag(x numeric)
...
v := get_input_value_in_partition(windowobject, x, 1, -1,
'seek_current', false);
where the argument-fetching function is declared
get_input_value_in_partition(windowobject, anyelement, int, ...)
returns anyelement
and internally it could verify that the n'th window function argument
matches the type of its second argument. While this could be made
to work, it's kind of unsatisfying because the argument number "1" is
so obviously redundant with the reference to "x". Ideally one should
only have to write "x". I don't quite see how to make that work,
but maybe there's a way?
On the whole though, I think your original idea of bespoke plpgsql
syntax is better, ie let's write something like
GET WINDOW VALUE v := x AT PARTITION CURRENT(-1);
and hide all the mechanism behind that. The reference to "x" is enough
to provide the argument number and type, and the window object doesn't
have to be explicitly visible at all.
Yeah, this will mean that anybody who wants to provide equivalent
functionality in some other PL will have to do more work. But it's
not like it was going to be zero effort for them before. Furthermore,
it's not clear to me that other PLs would want to adopt your current
design anyway. For example, I bet PL/R would like to somehow make
window arguments map into vectors on the R side, but there's no chance
of that with this SQL layer in between.
regards, tom lane
Hi
so 16. 1. 2021 v 0:09 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> [ plpgsql-window-functions-20210104.patch.gz ]
I spent some time looking at this patch. It would certainly be
appealing to have some ability to write custom window functions
without descending into C; but I'm not very happy about the details.
I'm okay with the idea of having a special variable of a new pseudotype.
That's not exactly pretty, but it descends directly from how we handle
the arguments of trigger functions, so at least there's precedent.
What's bugging me though is the "typedvalue" stuff. That seems like a
conceptual mess, a performance loss, and a permanent maintenance time
sink. To avoid performance complaints, eventually this hard-wired set
of conversions would have to bloom to cover every built-in cast, and
as for extension types, you're just out of luck.
I invited typed values with an idea of larger usability. With this type we can implement dynamic iteration over records better than now, when the fields of records should be cast to text or json before operation. With this type I can hold typed value longer time and I can do some like:
DECLARE var typedvalue;
var := fx(..);
IF var IS OF integer THEN
var_int := CAST(var AS int);
ELSEIF var IS OF date THEN
var_date := CAST(var AS date);
ELSE
var_text := CAST(var AS text);
END;
Sometimes (when you process some external data) this late (lazy) cast can be better and allows you to use typed values. When I read external data, sometimes I don't know types of these data before reading. I would like to inject a possibility of more dynamic work with values and variables (but still cleanly and safely). It should be more safe and faster than now, when people should use the "text" type.
But I understand and I agree with your objections. Probably a lot of people will use this type badly.
One way to avoid that would be to declare the argument-fetching
functions as polymorphics with a dummy argument that just provides
the expected result type. So users would write something like
create function pl_lag(x numeric)
...
v := get_input_value_in_partition(windowobject, x, 1, -1,
'seek_current', false);
where the argument-fetching function is declared
get_input_value_in_partition(windowobject, anyelement, int, ...)
returns anyelement
and internally it could verify that the n'th window function argument
matches the type of its second argument. While this could be made
to work, it's kind of unsatisfying because the argument number "1" is
so obviously redundant with the reference to "x". Ideally one should
only have to write "x". I don't quite see how to make that work,
but maybe there's a way?
On the whole though, I think your original idea of bespoke plpgsql
syntax is better, ie let's write something like
GET WINDOW VALUE v := x AT PARTITION CURRENT(-1);
and hide all the mechanism behind that. The reference to "x" is enough
to provide the argument number and type, and the window object doesn't
have to be explicitly visible at all.
The second question is work with partition context value. This should be only one value, and of only one but of any type per function. In this case we cannot use GET statements. I had an idea of enhancing declaration. Some like
DECLARE
pcx PARTITION CONTEXT (int); -- read partition context
BEGIN
pcx := 10; -- set partition context
What do you think about it?
Regards
Pavel
Yeah, this will mean that anybody who wants to provide equivalent
functionality in some other PL will have to do more work. But it's
not like it was going to be zero effort for them before. Furthermore,
it's not clear to me that other PLs would want to adopt your current
design anyway. For example, I bet PL/R would like to somehow make
window arguments map into vectors on the R side, but there's no chance
of that with this SQL layer in between.
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
> The second question is work with partition context value. This should be
> only one value, and of only one but of any type per function. In this case
> we cannot use GET statements. I had an idea of enhancing declaration. Some
> like
> DECLARE
> pcx PARTITION CONTEXT (int); -- read partition context
> BEGIN
> pcx := 10; -- set partition context
> What do you think about it?
Uh, what? I don't understand what this "partition context" is.
regards, tom lane
st 20. 1. 2021 v 21:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> The second question is work with partition context value. This should be
> only one value, and of only one but of any type per function. In this case
> we cannot use GET statements. I had an idea of enhancing declaration. Some
> like
> DECLARE
> pcx PARTITION CONTEXT (int); -- read partition context
> BEGIN
> pcx := 10; -- set partition context
> What do you think about it?
Uh, what? I don't understand what this "partition context" is.
It was my name for an access to window partition local memory - WinGetPartitionLocalMemory
We need some interface for this cache
Regards
Pavel
regards, tom lane
Pavel Stehule <pavel.stehule@gmail.com> writes:
> st 20. 1. 2021 v 21:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>> Uh, what? I don't understand what this "partition context" is.
> It was my name for an access to window partition local memory -
> WinGetPartitionLocalMemory
Ah.
> We need some interface for this cache
I'm not convinced we need to expose that, or that it'd be very
satisfactory to plpgsql users if we did. The fact that it's fixed-size
and initializes to zeroes are both things that are okay for C programmers
but might be awkward to deal with in plpgsql code. At the very least it
would greatly constrain what data types you could usefully store.
So I'd be inclined to leave that out, at least for the first version.
regards, tom lane
st 20. 1. 2021 v 21:32 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
Pavel Stehule <pavel.stehule@gmail.com> writes:
> st 20. 1. 2021 v 21:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:
>> Uh, what? I don't understand what this "partition context" is.
> It was my name for an access to window partition local memory -
> WinGetPartitionLocalMemory
Ah.
> We need some interface for this cache
I'm not convinced we need to expose that, or that it'd be very
satisfactory to plpgsql users if we did. The fact that it's fixed-size
and initializes to zeroes are both things that are okay for C programmers
but might be awkward to deal with in plpgsql code. At the very least it
would greatly constrain what data types you could usefully store.
So I'd be inclined to leave that out, at least for the first version.
I think this functionality is relatively important. If somebody tries to implement own window function, then he starts with some variation of the row_num function.
We can support only types of fixed length to begin.
Regards
Pavel
regards, tom lane
st 20. 1. 2021 v 21:14 odesílatel Pavel Stehule <pavel.stehule@gmail.com> napsal:
st 20. 1. 2021 v 21:07 odesílatel Tom Lane <tgl@sss.pgh.pa.us> napsal:Pavel Stehule <pavel.stehule@gmail.com> writes:
> The second question is work with partition context value. This should be
> only one value, and of only one but of any type per function. In this case
> we cannot use GET statements. I had an idea of enhancing declaration. Some
> like
> DECLARE
> pcx PARTITION CONTEXT (int); -- read partition context
> BEGIN
> pcx := 10; -- set partition context
> What do you think about it?
Uh, what? I don't understand what this "partition context" is.It was my name for an access to window partition local memory - WinGetPartitionLocalMemoryWe need some interface for this cache
I have to think more about declarative syntax. When I try to transform our WindowObject API directly, then it looks like Cobol. It needs a different concept to be user friendly.
Regards
Pavel
RegardsPavel
regards, tom lane