WIP: Allow SQL-language functions to reference parameters by parameter name

Matthew Draper <matthew@trebex.net> writes:

Attached is a WIP patch that allows SQL-language functions to reference
their parameters by name.

It uses p_post_columnref_hook, so potentially ambiguous references
prefer the column... that seems to make the most sense, both because it
avoids a backwards incompatibility, and it conforms with SQL's usual
notion of assuming you mean the "nearest" name.

Personally I'd vote for *not* having any such dangerous semantics as
that. We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period. That means you do need ways to disambiguate in both directions.
For column references you can just qualify with the table name/alias.
If the parameter reference is intended, allow qualification with the
function name.

regards, tom lane

2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:

Matthew Draper <matthew@trebex.net> writes:

Attached is a WIP patch that allows SQL-language functions to reference
their parameters by name.

It uses p_post_columnref_hook, so potentially ambiguous references
prefer the column... that seems to make the most sense, both because it
avoids a backwards incompatibility, and it conforms with SQL's usual
notion of assuming you mean the "nearest" name.

Personally I'd vote for *not* having any such dangerous semantics as
that.  We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.  That means you do need ways to disambiguate in both directions.
For column references you can just qualify with the table name/alias.
If the parameter reference is intended, allow qualification with the
function name.

I agree with Tom.

There can be GUC for controlling use or don't use a parameter names. I
am for GUC, because there will be a bilion conflicts. But a talk about
priorities - sql identifier or parameter is useless.

Regards

Pavel Stehule

Pavel Stehule <pavel.stehule@gmail.com> writes:

2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:

I think the best idea is to throw error for ambiguous references,
period.

There can be GUC for controlling use or don't use a parameter names. I
am for GUC, because there will be a bilion conflicts. But a talk about
priorities - sql identifier or parameter is useless.

GUCs are not tremendously helpful for problems such as this. If we
actually wanted to preserve full backwards compatibility, we'd need to
think of a way to mark SQL functions per-function as to what to do.
But I don't think that's necessary. Up to now there's been relatively
little use for naming the parameters of SQL functions, so I think there
will be few conflicts in the field if we just change the behavior. The
mess and complication we have for the comparable behavior in plpgsql
seemed necessary because of the number of existing usages that would
certainly break --- but I doubt that SQL-language functions will have
anywhere near as big a problem.

regards, tom lane

2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

2011/3/25 Tom Lane <tgl@sss.pgh.pa.us>:

I think the best idea is to throw error for ambiguous references,
period.

There can be GUC for controlling use or don't use a parameter names. I
am for GUC, because there will be a bilion conflicts. But a talk about
priorities - sql identifier or parameter is useless.

GUCs are not tremendously helpful for problems such as this.  If we
actually wanted to preserve full backwards compatibility, we'd need to
think of a way to mark SQL functions per-function as to what to do.
But I don't think that's necessary.  Up to now there's been relatively
little use for naming the parameters of SQL functions, so I think there
will be few conflicts in the field if we just change the behavior.  The
mess and complication we have for the comparable behavior in plpgsql
seemed necessary because of the number of existing usages that would
certainly break --- but I doubt that SQL-language functions will have
anywhere near as big a problem.

should be nice some converting tool for pg_dump or pg_upgrade. It can
dump SQL functions with only qualified identifiers.

Pavel

On Mar 25, 2011, at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

GUCs are not tremendously helpful for problems such as this. If we
actually wanted to preserve full backwards compatibility, we'd need to
think of a way to mark SQL functions per-function as to what to do.
But I don't think that's necessary. Up to now there's been relatively
little use for naming the parameters of SQL functions, so I think there
will be few conflicts in the field if we just change the behavior.

Oh wow, I don't agree with that at all. People may name the parameters for documentation purposes, and then have things like WHERE foo = $1, foo happening also to be the name associated with $1. Boom!

In any case, I think this is 9.2 material. We need to get a beta out the door, and I emphatically think we should be focusing on resolving the issues with features already committed, not committing new ones.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

On Mar 25, 2011, at 4:20 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

But I don't think that's necessary. Up to now there's been relatively
little use for naming the parameters of SQL functions, so I think there
will be few conflicts in the field if we just change the behavior.

Oh wow, I don't agree with that at all. People may name the parameters for documentation purposes, and then have things like WHERE foo = $1, foo happening also to be the name associated with $1. Boom!

Well, maybe, but it's not like it's subtle or hard to fix.

In any case, I think this is 9.2 material.

Oh, of course. It *is* just a WIP patch, anyway.

regards, tom lane

On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, maybe, but it's not like it's subtle or hard to fix.

Depends how much of it you have. I've become very skeptical of anything that breaks pg_dump-and-reload-ability. And doubly so now that such problems also mean breaking pg_upgrade after the old cluster has been shut down.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, maybe, but it's not like it's subtle or hard to fix.

Depends how much of it you have. I've become very skeptical of
anything that breaks pg_dump-and-reload-ability.

This wouldn't break pg_dump scripts, because they disable
check_function_bodies. You would get a failure on first *use*
of a function, which is something different.

Basically my concern here is that in the name of easing a short-term
conversion issue, we'll be condemning users to a future of subtle,
hard-to-find bugs due to ambiguous names. How many hundreds of
reports have we seen about the equivalent problem in plpgsql?

You could argue that the frequency of plpgsql issues was at least partly
due to having a poor choice of which way to resolve the ambiguity, but
I don't think it can be entirely blamed on that.

regards, tom lane

On Fri, Mar 25, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, maybe, but it's not like it's subtle or hard to fix.

Depends how much of it you have. I've become very skeptical of
anything that breaks pg_dump-and-reload-ability.

This wouldn't break pg_dump scripts, because they disable
check_function_bodies.  You would get a failure on first *use*
of a function, which is something different.

Basically my concern here is that in the name of easing a short-term
conversion issue, we'll be condemning users to a future of subtle,
hard-to-find bugs due to ambiguous names.  How many hundreds of
reports have we seen about the equivalent problem in plpgsql?

You could argue that the frequency of plpgsql issues was at least partly
due to having a poor choice of which way to resolve the ambiguity, but
I don't think it can be entirely blamed on that.

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place. If we used $foo or ?foo or ${foo} or $.foo
or &&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

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

Tom,

Personally I'd vote for *not* having any such dangerous semantics as
that. We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.

As a likely heavy user of this feature, I agree with Tom here. I really don't want the column being silently preferred in SQL functions, when PL/pgSQL functions are throwing an error. I'd end up spending hours debugging this.

Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
San Francisco

On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:

Tom,

Personally I'd vote for *not* having any such dangerous semantics as
that. We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.

As a likely heavy user of this feature, I agree with Tom here. I really don't want the column being silently preferred in SQL functions, when PL/pgSQL functions are throwing an error. I'd end up spending hours debugging this.

Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.

Because an identifier that previously referred unambiguously to a column might now be ambiguous, if there is a parameter with the same name.

...Robert

Robert Haas <robertmhaas@gmail.com> writes:

On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:

Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.

Because an identifier that previously referred unambiguously to a column might now be ambiguous, if there is a parameter with the same name.

Yes, a function that previously worked might now throw error, if we make
ambiguous names be errors. But this is *not* a failure that would occur
during dump/reload. You'd have to actually run the function.

regards, tom lane

On Mar 25, 2011, at 9:12 PM, Robert Haas wrote:

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place. If we used $foo or ?foo or ${foo} or $.foo
or &&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

Yes *please*. Man that would make maintenance of such functions easier.

Best,

David

Robert Haas <robertmhaas@gmail.com> writes:

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place. If we used $foo or ?foo or ${foo} or $.foo
or &&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

If this were PL/perl, or PL/almost-anything-except-SQL, I could get
behind such a proposal. But it's not, it's SQL; and SQL doesn't do
things that way. SQL's idea of disambiguation is qualified names.

And even more to the point: to the extent you think that weird syntax
might be a suitable solution, you have to keep in mind that the SQL
committee could take over any such syntax at the drop of a hat.
See the recent unpleasantness concerning => ...

regards, tom lane

Robert Haas wrote:

On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:

Tom,

Personally I'd vote for *not* having any such dangerous semantics as
that. We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.

As a likely heavy user of this feature, I agree with Tom here. I really don't want the column being silently preferred in SQL functions, when PL/pgSQL functions are throwing an error. I'd end up spending hours debugging this.

Also, I don't understand why this would be a dump/reload issue if $1 and $2 continue to work.

Because an identifier that previously referred unambiguously to a column might now be ambiguous, if there is a parameter with the same name.

I mention 2 possible solutions here, both which involve syntax alterations, each
between the ---------- lines. I personally like the second/lower option more.

------------

Might it be reasonable, perhaps as a 9.2 feature, to add top-level-namespaces so
that one could always explicitly qualify what they are referring to?

For example, you could have the 3 "sch", "lex", "attr" (I may have missed some
useful ones).

The "sch" TLN would unambiguously refer directly to a schema object, such as a
database table.

The "lex" TLN would unambiguously refer directly to a lexical, either a
parameter of the current routine or to a lexical variable.

The "attr" TLN would unambiguously refer to a table/etc column/attribute in the
manner typical for SQL.

Use them like:

sch.foo - the table/etc foo
lex.foo - the lexical variable foo
attr.foo - the column foo

Use of these TLN are optional where there is no ambiguity.

The TLN are not reserved words, but if one has an entity named the same, then
references to it must be TLN-qualified; eg:

lex.sch
lex.lex
lex.attr

Now these are just examples. You may find a different set works better.

--------------

There are also alternate solutions.

For example, it could be mandated that lexical-scope aliases for any
data/var-like schema object are required in routines, where the aliases are
distinct from all lexical vars/params/etc, and then all SQL/code in the routines
may only refer to the schema objects by the aliases.

Effectively this makes it so that routines can no longer see non-lexical vars
but for those from parameters, and this aliasing is defining a parameter whose
argument is supplied by the DBMS automatically rather than as an explicit
routine caller argument.

That way, inside a routine body there are only lexical names for things, and so
no namespace-qualification is ever needed by the regular SQL.

Similarly, if you always think of table column names as referring to an
attribute or element of a table variable, then just reference the column
qualified by the table name (or the lexical alias thereof). Same as you do in
any other programming language. Of course, sometimes you don't have to qualify
column name references as context could make it unambiguous. Or, a shorthand
like a simple leading "." could unambiguously say you're referring to a column
of the particular table in context.

With those in place, all unqualified references are straight to lexical
variables or parameters.

And so, this is also an effective way to resolve the ambiguity and I prefer the
latter design personally.

Here's an example in quasi-PL/PgSQL:

create function myrtn (myparam integer, mytbl ::= mydb.myschema.mytbl) as
declare
myvar integer := 5;
$body$
begin
select (.mycol + myvar * myparam) as mynewcol from mytbl;
end;
$body$

Note that I've already thought through this last example as these methods of
avoiding ambiguity are loosely-speaking how my language Muldis D avoids the
problem faced by many SQL procedures.

The ".mycol" syntax specifically was inspired originally for me by Perl 6 where
the lack of something just before the "." means that the implicit topic variable
is referred to, like if you said "$_.mycol".

A Perl 6 analogy being something like:

$mytbl.map:{ .mycol + $myvar * $myparam }

aka:

$mytbl.map:{ $_.mycol + $myvar * $myparam }

--------------

-- Darren Duncan

On Mar 25, 2011, at 11:23 PM, Tom Lane wrote:

If this were PL/perl, or PL/almost-anything-except-SQL, I could get
behind such a proposal. But it's not, it's SQL; and SQL doesn't do
things that way. SQL's idea of disambiguation is qualified names.

And even more to the point: to the extent you think that weird syntax
might be a suitable solution, you have to keep in mind that the SQL
committee could take over any such syntax at the drop of a hat.
See the recent unpleasantness concerning => ...

Perhaps we could use `=>varname`. ;-P

David

2011/3/26 Darren Duncan <darren@darrenduncan.net>:

Robert Haas wrote:

On Mar 25, 2011, at 9:22 PM, Joshua Berkus <josh@agliodbs.com> wrote:

Tom,

Personally I'd vote for *not* having any such dangerous semantics as
that. We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.

As a likely heavy user of this feature, I agree with Tom here.  I really
don't want the column being silently preferred in SQL functions, when
PL/pgSQL functions are throwing an error.  I'd end up spending hours
debugging this.

Also, I don't understand why this would be a dump/reload issue if $1 and
$2 continue to work.

Because an identifier that previously referred unambiguously to a column
might now be ambiguous, if there is a parameter with the same name.

I mention 2 possible solutions here, both which involve syntax alterations,
each between the ---------- lines.  I personally like the second/lower
option more.

------------

Might it be reasonable, perhaps as a 9.2 feature, to add
top-level-namespaces so that one could always explicitly qualify what they
are referring to?

For example, you could have the 3 "sch", "lex", "attr" (I may have missed
some useful ones).

The "sch" TLN would unambiguously refer directly to a schema object, such as
a database table.

The "lex" TLN would unambiguously refer directly to a lexical, either a
parameter of the current routine or to a lexical variable.

The "attr" TLN would unambiguously refer to a table/etc column/attribute in
the manner typical for SQL.

Use them like:

 sch.foo - the table/etc foo
 lex.foo - the lexical variable foo
 attr.foo - the column foo

Use of these TLN are optional where there is no ambiguity.

The TLN are not reserved words, but if one has an entity named the same,
then references to it must be TLN-qualified; eg:

 lex.sch
 lex.lex
 lex.attr

Now these are just examples.  You may find a different set works better.

-1

this is not based on any pattern on SQL. It's not simple, and it
introduce a reserved keywords

Regards

Pavel

On Mar 25, 2011, at 11:23 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

If this were PL/perl, or PL/almost-anything-except-SQL, I could get
behind such a proposal. But it's not, it's SQL; and SQL doesn't do
things that way. SQL's idea of disambiguation is qualified names.

And even more to the point: to the extent you think that weird syntax
might be a suitable solution, you have to keep in mind that the SQL
committee could take over any such syntax at the drop of a hat.
See the recent unpleasantness concerning => ...

You can't be guaranteed that they won't standardize something incompatible no matter what we do. We could choose to do it as you've proposed and they could then standardize some weird syntax - the => is a fairly relevant example of exactly that.

...Robert

Pavel Stehule wrote:

2011/3/26 Darren Duncan <darren@darrenduncan.net>:

I mention 2 possible solutions here, both which involve syntax alterations,
each between the ---------- lines. I personally like the second/lower
option more.

-1

this is not based on any pattern on SQL. It's not simple, and it
introduce a reserved keywords

Okay, here's a much simpler proposal with the most important bit of the old one.

1. In all situations where there is ambiguity such that an identifier reference
(not declaration) may be referring to either a lexical variable/parameter of the
current routine, or to the name of the table column of the contextually current
table of the current SQL statement, the ambiguity is always resolved in favor of
the lexical var/param. If I am not mistaken, that is what PL/PgSQL already does
since 9.0.

2. If an identifier reference has a leading "." then that will force it to be
interpreted as a column instead (and the code will fail if there is no such
column), and so ".colname" is a shorthand for "tablename.colname"; but like with
the old "colname" it only works when just 1 of the source tables has "colname"
else it is still ambiguous like before.

Example:

select (.mycol + myvar * myparam) as mynewcol from mytbl;

This solution is a very terse and understandable change.

There are no reserved keywords. Legacy user code has no change where there were
no conflicts before. Legacy user code has no change in the case of conflict if
it was previously resolved to favor the lexical var/param.

Legacy user code only gains a leading "." in the few places where conflict was
resolved in favor of a column name before where a same-named lexical/param existed.

So what's not to like about this?

-- Darren Duncan

2011/3/26 Darren Duncan <darren@darrenduncan.net>:

Pavel Stehule wrote:

2011/3/26 Darren Duncan <darren@darrenduncan.net>:

I mention 2 possible solutions here, both which involve syntax
alterations,
each between the ---------- lines.  I personally like the second/lower
option more.

-1

this is not based on any pattern on SQL. It's not simple, and it
introduce a reserved keywords

Okay, here's a much simpler proposal with the most important bit of the old
one.

1.  In all situations where there is ambiguity such that an identifier
reference (not declaration) may be referring to either a lexical
variable/parameter of the current routine, or to the name of the table
column of the contextually current table of the current SQL statement, the
ambiguity is always resolved in favor of the lexical var/param.  If I am not
mistaken, that is what PL/PgSQL already does since 9.0.

2.  If an identifier reference has a leading "." then that will force it to
be interpreted as a column instead (and the code will fail if there is no
such column), and so ".colname" is a shorthand for "tablename.colname"; but
like with the old "colname" it only works when just 1 of the source tables
has "colname" else it is still ambiguous like before.

Example:

   select (.mycol + myvar * myparam) as mynewcol from mytbl;

This solution is a very terse and understandable change.

There are no reserved keywords.  Legacy user code has no change where there
were no conflicts before.  Legacy user code has no change in the case of
conflict if it was previously resolved to favor the lexical var/param.

Legacy user code only gains a leading "." in the few places where conflict
was resolved in favor of a column name before where a same-named
lexical/param existed.

So what's not to like about this?

sorry - I dislike this. The design is correct, but it is against to
SQL verbosity. A reader must to thinking about missing tablenames. I
dont't think so it is good solution, because it doesn't solve a
backing compatibility problem - somebody must to fix a function still,
and I think so it is much preferable to fix like:

select (mytbl.mycol + myvar * myparam) as mynewcol from mytbl;

your proposal saves a five chars, but it has a negative impacts on
readability - there should be more tables.

There are no reason to introduce a new concepts - SQL knows a aliases.

Regards

Pavel

Pavel Stehule wrote:

sorry - I dislike this. The design is correct, but it is against to
SQL verbosity. A reader must to thinking about missing tablenames. I
dont't think so it is good solution, because it doesn't solve a
backing compatibility problem - somebody must to fix a function still,
and I think so it is much preferable to fix like:

select (mytbl.mycol + myvar * myparam) as mynewcol from mytbl;

your proposal saves a five chars, but it has a negative impacts on
readability - there should be more tables.

There are no reason to introduce a new concepts - SQL knows a aliases.

Well, going forward, I know I would much rather have to say "mytbl.mycol" than
have to say "myfunc.myparam". And I certainly would want to expect that when
one says "... as foo" that this "foo" is treated as a declaration unambiguously
and is never substituted for some parameter or there be other grief as I seem to
recall having in 8.4. -- Darren Duncan

Joshua Berkus <josh@agliodbs.com> writes:

Personally I'd vote for *not* having any such dangerous semantics as
that. We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.

As a likely heavy user of this feature, I agree with Tom here. I really
don't want the column being silently preferred in SQL functions, when
PL/pgSQL functions are throwing an error. I'd end up spending hours
debugging this.

+1

I think the best choice is to only accept qualified parameter names in
SQL functions (function_name.parameter_name). If a referenced table
share the function's name, ERROR out and HINT to alias the table name.

If we allow more than that, we're opening the door to ambiguity, bug
reports, and more than that costly migrations. I don't see any benefit
in having to audit all SQL functions for ambiguity on a flag day, when
this could be avoided easily.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

2011/3/26 Dimitri Fontaine <dimitri@2ndquadrant.fr>:

Joshua Berkus <josh@agliodbs.com> writes:

Personally I'd vote for *not* having any such dangerous semantics as
that. We should have learned better by now from plpgsql experience.
I think the best idea is to throw error for ambiguous references,
period.

As a likely heavy user of this feature, I agree with Tom here.  I really
don't want the column being silently preferred in SQL functions, when
PL/pgSQL functions are throwing an error.  I'd end up spending hours
debugging this.

+1

I think the best choice is to only accept qualified parameter names in
SQL functions (function_name.parameter_name).  If a referenced table
share the function's name, ERROR out and HINT to alias the table name.

it's maybe too hard. I agree so we should to use a function_name alias
when collision is possible. Still there are more use cases, where SQL
function is used as macro, and there a alias isn't necessary

CREATE OR REPLACE FUNCTION greatest(VARIADIC "values" anyarray)
RETURNS anyelement AS $$
SELECT max(v) FROM unnest("values")
$$ LANGUAGE sql;

Regards

Pavel

On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:

I think the best choice is to only accept qualified parameter names in
SQL functions (function_name.parameter_name).  If a referenced table
share the function's name, ERROR out and HINT to alias the table name.

If we allow more than that, we're opening the door to ambiguity, bug
reports, and more than that costly migrations.  I don't see any benefit
in having to audit all SQL functions for ambiguity on a flag day, when
this could be avoided easily.

That syntax is sufficiently unwieldly that few people will want to use
it in real life, but certainly the backward compatibility problem is
much less than with what Tom proposed.

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

Robert Haas <robertmhaas@gmail.com> writes:

On Sat, Mar 26, 2011 at 5:19 PM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:

I think the best choice is to only accept qualified parameter names in
SQL functions (function_name.parameter_name).  If a referenced table
share the function's name, ERROR out and HINT to alias the table name.

If we allow more than that, we're opening the door to ambiguity, bug
reports, and more than that costly migrations.  I don't see any benefit
in having to audit all SQL functions for ambiguity on a flag day, when
this could be avoided easily.

That syntax is sufficiently unwieldly that few people will want to use
it in real life, but certainly the backward compatibility problem is
much less than with what Tom proposed.

Well, we would still support positional arguments like $1 $2 etc, right?

In Pavel's example I wouldn't mind about using the "values" parameter
name but would stick to using $1.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Mar 27, 2011, at 6:11 AM, Dimitri Fontaine <dimitri@2ndQuadrant.fr> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

That syntax is sufficiently unwieldly that few people will want to use
it in real life, but certainly the backward compatibility problem is
much less than with what Tom proposed.

Well, we would still support positional arguments like $1 $2 etc, right?

Yeah, that's not going away.

...Robert

On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote:

On Fri, Mar 25, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Mar 25, 2011, at 7:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, maybe, but it's not like it's subtle or hard to fix.

Depends how much of it you have. I've become very skeptical of
anything that breaks pg_dump-and-reload-ability.

This wouldn't break pg_dump scripts, because they disable
check_function_bodies. �You would get a failure on first *use*
of a function, which is something different.

Basically my concern here is that in the name of easing a short-term
conversion issue, we'll be condemning users to a future of subtle,
hard-to-find bugs due to ambiguous names. �How many hundreds of
reports have we seen about the equivalent problem in plpgsql?

You could argue that the frequency of plpgsql issues was at least partly
due to having a poor choice of which way to resolve the ambiguity, but
I don't think it can be entirely blamed on that.

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place. If we used $foo or ?foo or ${foo} or $.foo
or &&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

How about psql's :foo syntax?

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On 03/27/2011 09:42 AM, David Fetter wrote:

On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote:

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place. If we used $foo or ?foo or ${foo} or $.foo
or&&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

How about psql's :foo syntax?

Surely the time has long gone when we could have made such a choice. And
the choice was not made in a vacuum.

cheers

andrew

2011/3/27 Andrew Dunstan <andrew@dunslane.net>:

On 03/27/2011 09:42 AM, David Fetter wrote:

On Fri, Mar 25, 2011 at 09:12:33PM -0400, Robert Haas wrote:

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
or&&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

How about psql's :foo syntax?

Surely the time has long gone when we could have made such a choice. And the
choice was not made in a vacuum.

Syntax for named parameters should be consistent with prepared
statement. Is there any comments in standard?

Regards

Pavel

SQL/PSM doesn't use any prefix - but it is little bit different
chapter then prepared statements.

On Sun, Mar 27, 2011 at 06:52:03PM +0200, Pavel Stehule wrote:

Syntax for named parameters should be consistent with prepared
statement. Is there any comments in standard?

Well, there's section 4.24 which says:

"In SQL-statements that are executed dynamically, the parameters are
called dynamic parameters (<dynamic parameter specification>s) and are
represented in SQL language by a <question mark> (?)."

which I think we can all agree is not a useful guide.

Have a nice day,

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

2011/3/27 Martijn van Oosterhout <kleptog@svana.org>:

On Sun, Mar 27, 2011 at 06:52:03PM +0200, Pavel Stehule wrote:

Syntax for named parameters should be consistent with prepared
statement. Is there any comments in standard?

Well, there's section 4.24 which says:

"In SQL-statements that are executed dynamically, the parameters are
called dynamic parameters (<dynamic parameter specification>s) and are
represented in SQL language by a <question mark> (?)."

ok, so my preferences:

1. no prefix - using a qualified identifiers - it's consistent with
plpgsql and sql/psm
2 :name - it uses a Oracle and it can help to people who migrate from Oracle
3. $name - it is like enhancing of $number parameters - lot of
languages uses this notation

Regards

Pavel Stehule

On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote:

You can't be guaranteed that they won't standardize something
incompatible no matter what we do. We could choose to do it as you've
proposed and they could then standardize some weird syntax - the => is
a fairly relevant example of exactly that.

The matter of how to resolve SQL parameter names is already
standardized. See clause on <identifier chain>.

On Mar 28, 2011, at 3:18 PM, Peter Eisentraut wrote:

On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote:

You can't be guaranteed that they won't standardize something
incompatible no matter what we do. We could choose to do it as you've
proposed and they could then standardize some weird syntax - the => is
a fairly relevant example of exactly that.

The matter of how to resolve SQL parameter names is already
standardized. See clause on <identifier chain>.

Was there a final consensus on this?

FWIW, if we go with using function name, it'd be nice to be allowed to alias that. I don't have a strong opinion between that and using : or $ or whatever. I do feel strongly that we must continue to support existing SQL functions in a reasonable fashion. Having the function blow up on the first invocation is no better than breaking the dump. There should be either a backwards-compatibility mode, or better yet, a way to automatically convert functions to be compatible with the new syntax.
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler <david@kineticode.com> wrote:

On Mar 25, 2011, at 9:12 PM, Robert Haas wrote:

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place.  If we used $foo or ?foo or ${foo} or $.foo
or &&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

Yes *please*. Man that would make maintenance of such functions easier.

+1 on using $foo. Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

merlin

On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote:

+1 on using $foo. Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

What standardization risk? The standard has already existed for >10
years and is widely implemented.

On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote:

+1 on using $foo.  Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

What standardization risk?  The standard has already existed for >10
years and is widely implemented.

What is the standard, and who is it that has implemented it that way?

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

Merlin Moncure wrote:

On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler <david@kineticode.com> wrote:

On Mar 25, 2011, at 9:12 PM, Robert Haas wrote:

As I've said before, I believe that the root cause of this problem is
that using the same syntax for variables and column names is a bad
idea in the first place. If we used $foo or ?foo or ${foo} or $.foo
or &&foo!!$#? to mean "the parameter called foo", then this would all
be a non-issue.

Yes *please*. Man that would make maintenance of such functions easier.

+1 on using $foo. Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

The "foo" syntax should be orthogonal to everything else and not have anything
specifically to do with parameters. Rather, "foo" anywhere is just a delimited
case-sensitive identifier and can be used anywhere that foo can where the latter
is a case-insensitive identifier.

As for the SQL standard for bind parameters, as I recall they use :foo and so
:"foo" would be the sensitive more general case of that.

-- Darren Duncan

On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:

On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote:

+1 on using $foo. Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

What standardization risk? The standard has already existed for >10
years and is widely implemented.

What is the standard, and who is it that has implemented it that way?

As mentioned earlier, see under clause on <identifier chain>. The
summary is that in

CREATE FUNCTION foo(a int)

you can refer to the parameter as either of

a
foo.a

with some scoping rules to resolve ambiguities with column references.
(These are essentially the same scoping rules that tell you what "a"
refers to when you have multiple tables with an "a" column in a query.)

As far as I can tell, the syntax is implemented, more or less, at least
in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they
do with the scoping rules, of course.

On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:

On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote:

+1 on using $foo.  Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

What standardization risk?  The standard has already existed for >10
years and is widely implemented.

What is the standard, and who is it that has implemented it that way?

As mentioned earlier, see under clause on <identifier chain>.  The
summary is that in

   CREATE FUNCTION foo(a int)

you can refer to the parameter as either of

   a
   foo.a

with some scoping rules to resolve ambiguities with column references.
(These are essentially the same scoping rules that tell you what "a"
refers to when you have multiple tables with an "a" column in a query.)

As far as I can tell, the syntax is implemented, more or less, at least
in Oracle, DB2, MySQL, Firebird, and HSQL.  I haven't checked what they
do with the scoping rules, of course.

Talking about the standards compliance of functions is a bit silly:
our implementation of functions isn't even close to approximating what
looks to be the standard (according to this at least:
http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html)
and there is no point pretending that it is. In practice, database
functions and procedures are 100% vendor incompatible with each other,
and with the standard. I was just talking about $ getting reserved
for some special meaning in the future.

mysql supports psm, which we don't. oracle supports pl/sql, which is
similar to pl/pgsql, but means nothing in terms of postgresql sql
language argument disambiguation afaict. It's our language and we
should be able to extend it.

merlin

2011/4/5 Peter Eisentraut <peter_e@gmx.net>:

On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:

On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote:

+1 on using $foo.  Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

What standardization risk?  The standard has already existed for >10
years and is widely implemented.

What is the standard, and who is it that has implemented it that way?

As mentioned earlier, see under clause on <identifier chain>.  The
summary is that in

   CREATE FUNCTION foo(a int)

you can refer to the parameter as either of

   a
   foo.a

with some scoping rules to resolve ambiguities with column references.
(These are essentially the same scoping rules that tell you what "a"
refers to when you have multiple tables with an "a" column in a query.)

This is a good design. If we disallow a ambiguities, there isn't a
space for bugs. And if anybody needs to accent any parameter, then
there are still $n notation.

There is lot of notation and I don't think so it is necessary to add new one

MySQL, MSSQL uses @, DB2, ANSI SQL no prefix, Oracle and Firebird uses
":", but in different context.

simply - chaos.

There was request for some alias on function name. It could be.
PL/pgSQL knows a #option, so there can be some similar in SQL.

CREATE OR REPLACE FUNCTION longnamefunc(param integer)
RETURNS ... AS $$
#alias longnamefunc ln
SELECT ln.param;
$$

Regards

Pavel Stehule

2011/4/5 Merlin Moncure <mmoncure@gmail.com>:

On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote:

On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote:

+1 on using $foo.  Even with the standardization risk I think it's the
best choice. Prefer $"foo" to ${foo} though.

What standardization risk?  The standard has already existed for >10
years and is widely implemented.

What is the standard, and who is it that has implemented it that way?

As mentioned earlier, see under clause on <identifier chain>.  The
summary is that in

   CREATE FUNCTION foo(a int)

you can refer to the parameter as either of

   a
   foo.a

with some scoping rules to resolve ambiguities with column references.
(These are essentially the same scoping rules that tell you what "a"
refers to when you have multiple tables with an "a" column in a query.)

As far as I can tell, the syntax is implemented, more or less, at least
in Oracle, DB2, MySQL, Firebird, and HSQL.  I haven't checked what they
do with the scoping rules, of course.

Talking about the standards compliance of functions is a bit silly:
our implementation of functions isn't even close to approximating what
looks to be the standard (according to this at least:
http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html)
and there is no point pretending that it is.  In practice, database
functions and procedures are 100% vendor incompatible with each other,
and with the standard.  I was just talking about $ getting reserved
for some special meaning in the future.

mysql supports psm, which we don't.

A PSM support for PostgreSQL is almost done. I expect a production
quality for 9.2.

MySQL support own language based on PSM with lot of inspiration in T-SQL.

In MySQL - local variables are clasic, only session variables has a prefix @.

Regards

Pavel

 oracle supports pl/sql, which is

On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote:

Talking about the standards compliance of functions is a bit silly:
our implementation of functions isn't even close to approximating what
looks to be the standard

That doesn't mean it couldn't be better in the future. We shouldn't
take it further away, in any case.

As long as we use LANGUAGE SQL, we are both technically and morally in
standards-space.

On Tue, Apr 5, 2011 at 3:47 PM, Peter Eisentraut <peter_e@gmx.net> wrote:

On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote:

Talking about the standards compliance of functions is a bit silly:
our implementation of functions isn't even close to approximating what
looks to be the standard

That doesn't mean it couldn't be better in the future.  We shouldn't
take it further away, in any case.

As long as we use LANGUAGE SQL, we are both technically and morally in
standards-space.

sql standard functions are psm routines aiui. Are you making the case
that 'language sql' in postgresql could or should in fact be psm at
some point in the future? I say that's not the case -- our 'language
sql' is not psm.

That said, if you well and truly stated that it was project objective
to allow psm constructions in 'language sql', and you could figure out
a way to do that without breaking current sql code, I would have to
say i'm coming around to your point of view. Either way, our $N
notation is already non-standard and highly in use - what's the big
deal about making it more useful?

merlin

On 04/05/2011 03:45 PM, Merlin Moncure wrote:

Talking about the standards compliance of functions is a bit silly:
our implementation of functions isn't even close to approximating what
looks to be the standard (according to this at least:
http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html)
and there is no point pretending that it is. In practice, database
functions and procedures are 100% vendor incompatible with each other,
and with the standard. I was just talking about $ getting reserved
for some special meaning in the future.

mysql supports psm, which we don't. oracle supports pl/sql, which is
similar to pl/pgsql, but means nothing in terms of postgresql sql
language argument disambiguation afaict. It's our language and we
should be able to extend it.

That doesn't mean we should arbitrarily break compatibility with pl/sql,
nor that we should feel free to add on warts such as $varname that are
completely at odds with the style of the rest of the language. That
doesn't do anything except produce a mess.

cheers

andrew

On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
that we should feel free to add on warts such as $varname that are
completely at odds with the style of the rest of the language. That doesn't
do anything except produce a mess.

Well, what it does is avoid breaking compatibility with previous
versions of PostgreSQL. I think that actually does have some value.
Otherwise, we'd be folding to upper-case by default.

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

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
that we should feel free to add on warts such as $varname that are
completely at odds with the style of the rest of the language. That doesn't
do anything except produce a mess.

Well, what it does is avoid breaking compatibility with previous
versions of PostgreSQL. I think that actually does have some value.
Otherwise, we'd be folding to upper-case by default.

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match. This is also per spec if I interpreted
Peter's comments correctly. The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

regards, tom lane

On Apr 7, 2011, at 6:58 PM, Tom Lane wrote:

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match. This is also per spec if I interpreted
Peter's comments correctly. The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

I agree with you that it should throw an error, at least optionally. Could we not recycle the settings that control this for plpgsql functions?

Best,

David

On Thu, Apr 7, 2011 at 9:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
that we should feel free to add on warts such as $varname that are
completely at odds with the style of the rest of the language. That doesn't
do anything except produce a mess.

Well, what it does is avoid breaking compatibility with previous
versions of PostgreSQL.  I think that actually does have some value.
Otherwise, we'd be folding to upper-case by default.

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match.  This is also per spec if I interpreted
Peter's comments correctly.  The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

Not really. But if you're going to shout down my proposal because
it's not in the spec, then it's a bit hard to see how you can argue
that we should implement your non-spec-compliant behavior instead,
especially at the cost of a painful backward compatibility break. I
actually am 100% in agreement with you that allowing ambiguous
references to resolve either way is a recipe for bugs, bugs, and more
bugs. But breaking people's code is not a better answer. We still
have people on 8.2 because the pain of upgrading to 8.3 is more than
they can bear, and how many releases have we spent trying to get
standard_conforming_strings worked out? I admit this probably
wouldn't be as bad, but we've managed to put out several releases in a
row now that are relatively painless to upgrade between, and I think
that's a trend we should try to keep going.

I am halfway tempted to say that we need to invent our own procedural
language that is designed not for compatibility with the SQL standard
or Oracle, but for non-crappiness.

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

Robert Haas wrote:

I am halfway tempted to say that we need to invent our own procedural
language that is designed not for compatibility with the SQL standard
or Oracle, but for non-crappiness.

I'm way ahead of you on that one. -- Darren Duncan

On 04/07/2011 09:58 PM, Tom Lane wrote:

Robert Haas<robertmhaas@gmail.com> writes:

On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan<andrew@dunslane.net> wrote:

That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
that we should feel free to add on warts such as $varname that are
completely at odds with the style of the rest of the language. That doesn't
do anything except produce a mess.

Well, what it does is avoid breaking compatibility with previous
versions of PostgreSQL. I think that actually does have some value.
Otherwise, we'd be folding to upper-case by default.

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match. This is also per spec if I interpreted
Peter's comments correctly. The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

I think the discussion went off into the weeds somewhat, and I'm guilty
of responding to suggestions that don't refer to the original subject.

For SQL language functions, I think you're right. The only caveat I have
is that if your function name is very long, having to use it as a
disambiguating qualifier can be a bit ugly.

cheers

andrew

Hello

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match.  This is also per spec if I interpreted
Peter's comments correctly.  The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

I think the discussion went off into the weeds somewhat, and I'm guilty of
responding to suggestions that don't refer to the original subject.

For SQL language functions, I think you're right. The only caveat I have is
that if your function name is very long, having to use it as a
disambiguating qualifier can be a bit ugly.

same mechanism works well in plpgsql and nobody requested a some
special shortcut.

Regards

Pavel

On 04/08/2011 10:53 AM, Pavel Stehule wrote:

For SQL language functions, I think you're right. The only caveat I have is
that if your function name is very long, having to use it as a
disambiguating qualifier can be a bit ugly.

same mechanism works well in plpgsql and nobody requested a some
special shortcut.

I get annoyed by it there too, that's why I mentioned it :-)

cheers

andrew

On Fri, Apr 8, 2011 at 10:53 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

same mechanism works well in plpgsql and nobody requested a some
special shortcut.

I did. That mechanism sucks. But I think we're committed to doing
what the standard and/or Oracle do, so oh well.

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

On Friday, April 08, 2011 04:53:27 PM Pavel Stehule wrote:

same mechanism works well in plpgsql and nobody requested a some
special shortcut.

Well, for one it sucks there as well. For another it has been introduced for
quite some time and most people have introduced naming like p_param or v_param
for parameternames.

That has not been the case for sql functions. So I find it way much more
painfull there...

Andres

On Apr 8, 2011, at 8:05 AM, Robert Haas wrote:

same mechanism works well in plpgsql and nobody requested a some
special shortcut.

I did. That mechanism sucks. But I think we're committed to doing
what the standard and/or Oracle do, so oh well.

I think I've worked around that in PL/pgSQL using ALIAS…

David

On Fri, Apr 8, 2011 at 11:51 AM, David E. Wheeler <david@kineticode.com> wrote:

On Apr 8, 2011, at 8:05 AM, Robert Haas wrote:

same mechanism works well in plpgsql and nobody requested a some
special shortcut.

I did.  That mechanism sucks.  But I think we're committed to doing
what the standard and/or Oracle do, so oh well.

I think I've worked around that in PL/pgSQL using ALIAS…

I've worked around it, too, using various techniques. That doesn't
mean it doesn't suck.

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

On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan <andrew@dunslane.net> wrote:

That doesn't mean we should arbitrarily break compatibility with pl/sql, nor
that we should feel free to add on warts such as $varname that are
completely at odds with the style of the rest of the language. That doesn't
do anything except produce a mess.

Well, what it does is avoid breaking compatibility with previous
versions of PostgreSQL.  I think that actually does have some value.
Otherwise, we'd be folding to upper-case by default.

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match.  This is also per spec if I interpreted
Peter's comments correctly.  The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

+1, as long as you are 100.0% sure this is not going to break any
existing code. For example, what happens if the argument is named the
same as a table?

merlin

Merlin Moncure <mmoncure@gmail.com> writes:

On Thu, Apr 7, 2011 at 8:58 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Well, if we're going to consider 100% backwards compatibility a "must",
then we should just stick with what the submitted patch does, ie,
unqualified names are matched first to query columns, and to parameters
only if there's no column match. �This is also per spec if I interpreted
Peter's comments correctly. �The whole thread started because I
suggested that throwing an error for ambiguous cases might be a better
design in the long run, but apparently long term ease of code
maintenance is far down our list of priorities ...

+1, as long as you are 100.0% sure this is not going to break any
existing code. For example, what happens if the argument is named the
same as a table?

I was a bit sloppy in my statement above --- what the code is actually
doing (or should be doing) is matching to parameters only after the core
parser fails to find any match. So "unqualified reference to whole-row"
would take precedence too.

regards, tom lane

But breaking people's code is not a better answer. We still
have people on 8.2 because the pain of upgrading to 8.3 is more than
they can bear, and how many releases have we spent trying to get
standard_conforming_strings worked out? I admit this probably
wouldn't be as bad, but we've managed to put out several releases in a
row now that are relatively painless to upgrade between, and I think
that's a trend we should try to keep going.

I guess I'm not understanding the backwards compatibility problem. I've
looked up the thread, and I still don't see a real-world issue. If we
(by default) throw an error on ambiguity, and have GUC to turn that off
(in which case, it resolves column-first), I really don't see what
problem anyone could have upgrading.

Can you explain it to me?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Fri, Apr 8, 2011 at 3:56 PM, Josh Berkus <josh@agliodbs.com> wrote:

But breaking people's code is not a better answer.  We still
have people on 8.2 because the pain of upgrading to 8.3 is more than
they can bear, and how many releases have we spent trying to get
standard_conforming_strings worked out?  I admit this probably
wouldn't be as bad, but we've managed to put out several releases in a
row now that are relatively painless to upgrade between, and I think
that's a trend we should try to keep going.

I guess I'm not understanding the backwards compatibility problem.  I've
looked up the thread, and I still don't see a real-world issue.  If we
(by default) throw an error on ambiguity, and have GUC to turn that off
(in which case, it resolves column-first), I really don't see what
problem anyone could have upgrading.

Can you explain it to me?

Consider:

rhaas=# CREATE TABLE developer (id serial primary key, name text not null);
NOTICE: CREATE TABLE will create implicit sequence "developer_id_seq"
for serial column "developer.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"developer_pkey" for table "developer"
CREATE TABLE
rhaas=# insert into developer (name) values ('Tom'), ('Bruce');
INSERT 0 2
rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer)
RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE
sql STABLE;
CREATE FUNCTION
rhaas=# SELECT developer_lookup(1);
developer_lookup
------------------
Tom
(1 row)

Now, when this person attempts to recreate this function on a
hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
doesn't work.

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

Now, when this person attempts to recreate this function on a
hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
doesn't work.

Hence the GUC. Where's the issue?

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus <josh@agliodbs.com> wrote:

Now, when this person attempts to recreate this function on a
hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
doesn't work.

Hence the GUC.   Where's the issue?

Behavior-changing GUCs for this kind of thing cause a lot of problems.
If you need one GUC setting for your application to work, and the
extension you have installed needs the other setting, you're screwed.
In the worst case, if a security-definer function is involved, you can
create a security hole, for example by convincing the system that id =
$1 is intended to mean $1 = $1, or some such. You can of course
attach the GUC settings to each individual function, but that doesn't
really work either unless you do it for every function in the system.
The fundamental problem here is that GUCs are dynamically scoped,
while this problem is lexically scoped.

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

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus <josh@agliodbs.com> wrote:

Hence the GUC. � Where's the issue?

Behavior-changing GUCs for this kind of thing cause a lot of problems.
If you need one GUC setting for your application to work, and the
extension you have installed needs the other setting, you're screwed.
In the worst case, if a security-definer function is involved, you can
create a security hole, for example by convincing the system that id =
$1 is intended to mean $1 = $1, or some such. You can of course
attach the GUC settings to each individual function, but that doesn't
really work either unless you do it for every function in the system.
The fundamental problem here is that GUCs are dynamically scoped,
while this problem is lexically scoped.

Yeah. In the plpgsql case, we did make provisions to control the
behavior per-function. In principle we could do the same for SQL
functions, but it'd be rather a PITA I think. (In particular, the "easy
way out" of attaching SET clauses to the functions would be a bad idea
because it would defeat inlining.)

regards, tom lane

2011/4/9 Tom Lane <tgl@sss.pgh.pa.us>:

Robert Haas <robertmhaas@gmail.com> writes:

On Fri, Apr 8, 2011 at 4:32 PM, Josh Berkus <josh@agliodbs.com> wrote:

Hence the GUC.   Where's the issue?

Behavior-changing GUCs for this kind of thing cause a lot of problems.
 If you need one GUC setting for your application to work, and the
extension you have installed needs the other setting, you're screwed.
In the worst case, if a security-definer function is involved, you can
create a security hole, for example by convincing the system that id =
$1 is intended to mean $1 = $1, or some such.  You can of course
attach the GUC settings to each individual function, but that doesn't
really work either unless you do it for every function in the system.
The fundamental problem here is that GUCs are dynamically scoped,
while this problem is lexically scoped.

Yeah.  In the plpgsql case, we did make provisions to control the
behavior per-function.  In principle we could do the same for SQL
functions, but it'd be rather a PITA I think.  (In particular, the "easy
way out" of attaching SET clauses to the functions would be a bad idea
because it would defeat inlining.)

what about a new language like SQLc? - like SQL compatibility.
pg_upgrade can move old code into this compatibility language when
detect some posible problems.

Pavel

Robert Haas <robertmhaas@gmail.com> writes:

rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer)
RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE
sql STABLE;

Now, when this person attempts to recreate this function on a
hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
doesn't work.

Unless we make it so that no such version ever exists. Meaning that the
code works fine as is or using WHERE id = developer_lookup.id. AS id
can't ever be the parameter in this case, you're just fine.

Bearing in mind that $1 etc shortcuts still are available, I don't
really see this qualification of parameter names with function names so
big a problem that we should find a way to avoid it and risk breaking
compatibility.

Don't forget that any ambiguity here will mean *huge* migration costs.

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

rhaas=# CREATE OR REPLACE FUNCTION developer_lookup(id integer)
RETURNS text AS $$SELECT name FROM developer WHERE id = $1$$ LANGUAGE
sql STABLE;

Now, when this person attempts to recreate this function on a
hypothetical version of PostgreSQL that thinks "id" is ambiguous, it
doesn't work.

Unless we make it so that no such version ever exists.  Meaning that the
code works fine as is or using WHERE id = developer_lookup.id.  AS id
can't ever be the parameter in this case, you're just fine.

Bearing in mind that $1 etc shortcuts still are available, I don't
really see this qualification of parameter names with function names so
big a problem that we should find a way to avoid it and risk breaking
compatibility.

Don't forget that any ambiguity here will mean *huge* migration costs.

If I'm reading your email correctly, we're in agreement.

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

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:

Unless we make it so that no such version ever exists. �Meaning that the
code works fine as is or using WHERE id = developer_lookup.id. �AS id
can't ever be the parameter in this case, you're just fine.

Bearing in mind that $1 etc shortcuts still are available, I don't
really see this qualification of parameter names with function names so
big a problem that we should find a way to avoid it and risk breaking
compatibility.

Don't forget that any ambiguity here will mean *huge* migration costs.

If I'm reading your email correctly, we're in agreement.

Hmm, what I read Dimitri to be proposing is that we *require* parameter
names to be qualified with the function name. I don't recall hearing
that before. It would solve the problem perhaps, but I think the moans
and groans will be numerous.

regards, tom lane

On Thu, Apr 14, 2011 at 1:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Apr 14, 2011 at 11:37 AM, Dimitri Fontaine
<dimitri@2ndquadrant.fr> wrote:

Unless we make it so that no such version ever exists.  Meaning that the
code works fine as is or using WHERE id = developer_lookup.id.  AS id
can't ever be the parameter in this case, you're just fine.

Bearing in mind that $1 etc shortcuts still are available, I don't
really see this qualification of parameter names with function names so
big a problem that we should find a way to avoid it and risk breaking
compatibility.

Don't forget that any ambiguity here will mean *huge* migration costs.

If I'm reading your email correctly, we're in agreement.

Hmm, what I read Dimitri to be proposing is that we *require* parameter
names to be qualified with the function name.  I don't recall hearing
that before.  It would solve the problem perhaps, but I think the moans
and groans will be numerous.

So far the most promising proposal I've seen seems to be to let id
mean the parameter called id only when it can't refer to anything in
the query.

Tabula raza, I'd prefer your proposal to make any ambiguity an error,
but it's not worth the breakage. I'd be fine with having a way to
explicitly request that behavior though, a la Perl's "use strict".

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

Robert Haas <robertmhaas@gmail.com> writes:

On Thu, Apr 14, 2011 at 1:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Hmm, what I read Dimitri to be proposing is that we *require* parameter
names to be qualified with the function name. �I don't recall hearing
that before. �It would solve the problem perhaps, but I think the moans
and groans will be numerous.

So far the most promising proposal I've seen seems to be to let id
mean the parameter called id only when it can't refer to anything in
the query.

Tabula raza, I'd prefer your proposal to make any ambiguity an error,
but it's not worth the breakage.

Yeah, I've come round to that position too. I think allowing parameter
names to be checked only after query names is probably the best answer.

I'd be fine with having a way to
explicitly request that behavior though, a la Perl's "use strict".

This is possible but it's not clear it's worth the work.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

So far the most promising proposal I've seen seems to be to let
id mean the parameter called id only when it can't refer to
anything in the query.

Yeah, I've come round to that position too. I think allowing
parameter names to be checked only after query names is probably
the best answer.

+1

That seems the most useful and least surprising approach to me.

-Kevin

On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

So far the most promising proposal I've seen seems to be to let
id mean the parameter called id only when it can't refer to
anything in the query.

Yeah, I've come round to that position too. I think allowing
parameter names to be checked only after query names is probably
the best answer.

+1

That seems the most useful and least surprising approach to me.

As part of this, can we also allow specifying an alias for the function name? That would make it far less onerous to disambiguate parameters. Unfortunately we obviously couldn't use AS as the keyword for this alias; maybe we could use ALIAS instead? IE:

CREATE FUNCTION function_with_really_really_descriptive_name (
some_parameter int
) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$
SELECT fwrrdn.some_parameter
$$;
--
Jim C. Nasby, Database Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net

2011/4/15 Jim Nasby <jim@nasby.net>:

On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

So far the most promising proposal I've seen seems to be to let
id mean the parameter called id only when it can't refer to
anything in the query.

Yeah, I've come round to that position too.  I think allowing
parameter names to be checked only after query names is probably
the best answer.

+1

That seems the most useful and least surprising approach to me.

As part of this, can we also allow specifying an alias for the function name? That would make it far less onerous to disambiguate parameters. Unfortunately we obviously couldn't use AS as the keyword for this alias; maybe we could use ALIAS instead? IE:

CREATE FUNCTION function_with_really_really_descriptive_name (
 some_parameter int
) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$
       SELECT fwrrdn.some_parameter
$$;
--

I see this can be problem for other languages - mainly for PLpgSQL.
There should be aliases supported too. And this small feature can be
terible when somebody will try to port your code to other platforms.
Personally I am thinking, so it isn't necessary

-1

Regards

Pavel Stehule

(oops this mail never reached out, it seems, resending)

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah, I've come round to that position too. I think allowing
parameter names to be checked only after query names is probably
the best answer.

That seems the most useful and least surprising approach to me.

Allow me to disagree. I don't want to recheck all my SQL functions
whenever I ALTER TABLE … ADD|DROP|RENAME COLUMN …;

We don't even have a nice way to list functions dependencies towards
objects that are part of their body to help me out here, or do we?

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support

On Thu, Apr 14, 2011 at 10:56 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2011/4/15 Jim Nasby <jim@nasby.net>:

On Apr 14, 2011, at 4:20 PM, Kevin Grittner wrote:

Tom Lane <tgl@sss.pgh.pa.us> wrote:

Robert Haas <robertmhaas@gmail.com> writes:

So far the most promising proposal I've seen seems to be to let
id mean the parameter called id only when it can't refer to
anything in the query.

Yeah, I've come round to that position too.  I think allowing
parameter names to be checked only after query names is probably
the best answer.

+1

That seems the most useful and least surprising approach to me.

As part of this, can we also allow specifying an alias for the function name? That would make it far less onerous to disambiguate parameters. Unfortunately we obviously couldn't use AS as the keyword for this alias; maybe we could use ALIAS instead? IE:

CREATE FUNCTION function_with_really_really_descriptive_name (
 some_parameter int
) RETURNS int LANGUAGE SQL ALIAS fwrrdn AS $$
       SELECT fwrrdn.some_parameter
$$;
--

I see this can be problem for other languages - mainly for PLpgSQL.
There should be aliases supported too. And this small feature can be
terible when somebody will try to port your code to other platforms.
Personally I am thinking, so it isn't necessary

-1

I don't much like Jim's syntax suggestion (the alias really ought to
be declared within the function body, I think, not added to the CREATE
FUNCTION statement) but I don't necessarily think it's a bad idea.
What would be even better, in my view, is having a short alias that is
defined by default, but all previous proposals in this vein have been
shot down by Tom and Andrew. As a practical matter, though, I think
what Jim is talking about speaks to a real need - people want to make
SQL function names long and descriptive, but they do NOT want to spell
out that long function name 16 times inside the function body.

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

Excerpts from Robert Haas's message of lun abr 18 09:33:06 -0300 2011:

I don't much like Jim's syntax suggestion (the alias really ought to
be declared within the function body, I think, not added to the CREATE
FUNCTION statement) but I don't necessarily think it's a bad idea.
What would be even better, in my view, is having a short alias that is
defined by default, but all previous proposals in this vein have been
shot down by Tom and Andrew. As a practical matter, though, I think
what Jim is talking about speaks to a real need - people want to make
SQL function names long and descriptive, but they do NOT want to spell
out that long function name 16 times inside the function body.

plpgsql has the #option thing in functions; why can't we have something
similar in SQL functions?

CREATE FUNCTION function_with_really_really_descriptive_name (
 some_parameter int
) RETURNS int LANGUAGE SQL AS $$
#option function_alias fwrrdn
       SELECT fwrrdn.some_parameter
$$;

Not necessarily that exact syntax.

--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

On Mon, Apr 18, 2011 at 11:33 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:

Excerpts from Robert Haas's message of lun abr 18 09:33:06 -0300 2011:

I don't much like Jim's syntax suggestion (the alias really ought to
be declared within the function body, I think, not added to the CREATE
FUNCTION statement) but I don't necessarily think it's a bad idea.
What would be even better, in my view, is having a short alias that is
defined by default, but all previous proposals in this vein have been
shot down by Tom and Andrew.  As a practical matter, though, I think
what Jim is talking about speaks to a real need - people want to make
SQL function names long and descriptive, but they do NOT want to spell
out that long function name 16 times inside the function body.

plpgsql has the #option thing in functions; why can't we have something
similar in SQL functions?

 CREATE FUNCTION function_with_really_really_descriptive_name (
  some_parameter int
 ) RETURNS int LANGUAGE SQL AS $$
    #option function_alias fwrrdn
        SELECT fwrrdn.some_parameter
 $$;

Not necessarily that exact syntax.

If we are rejecting $foo on grounds of deviating from sql standard,
shouldn't this be rejected on the same grounds? There is no such
syntax in sql/psm.

merlin