Re: Window functions can be created with defaults, but they don't work

On Fri, Aug 30, 2013 at 6:14 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I noticed this while poking at the variadic-aggregates issue:
>
> regression=# create function nth_value_def(anyelement, integer = 1) returns anyelement language internal window
immutablestrict as 'window_nth_value';
 
> CREATE FUNCTION
> regression=# SELECT nth_value_def(ten) OVER (PARTITION BY four), ten, four
> FROM (SELECT * FROM tenk1 WHERE unique2 < 10 ORDER BY four, ten)s;
> The connection to the server was lost. Attempting reset: Failed.
>
> The reason this crashes is that the planner doesn't apply
> default-insertion to WindowFunc nodes, only to FuncExprs.  We could make
> it do that, probably, but that seems to me like a feature addition.
> I think a more reasonable approach for back-patching is to fix function
> creation to disallow attaching defaults to window functions (or
> aggregates, for that matter, which would have the same issue if CREATE
> AGGREGATE had the syntax option to specify defaults).  ProcedureCreate
> seems like an appropriate place, since it already contains a lot of sanity
> checks of this sort.

I'm not sure I agree.  Under that approach, any functions that have
already been created like that will still crash the server.  A
malicious user could create a function like this now and wait to
crontab it until the day he's leaving the company.  Or there are more
accidental scenarios as well.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company