Re: BUG #5258: Unique and foreign key constraints fail on columns with reserved names, but not check constraints

On Sun, Jan 3, 2010 at 10:48 PM, Ben Woosley <ben(dot)woosley(at)gmail(dot)com> wrote:
> Check constraints successfully accept columns named with reserved words when
> they are qualified by table using the . syntax, e.g. "check (mod(table.as,
> 2) = 0)"
>
> However, unique and foreign key constraints added using the "alter table add
> constraint" syntax fail on the column name.  At this point the statement has
> enough information (the host table name) to properly identify the column
> despite the unorthodox name.  Alternatively, you could allow the . syntax
> qualification inside the argument to the constraint.
>
> Now, you may say that this is a reserved word and should never be used, but
> coming from the Ruby world, where reserved words are only reserved when
> they're truly ambiguous, I very much appreciate the freedom of using these
> names when it's unambiguous.  This particularly so given that keywords are
> often chosen for their terseness and overlap with the most appropriate
> column name.

I think what I'd instead say is that this isn't really a bug. The
behavior might not be what you'd like, and that's fair, and if a lot
of other people complain about it too, someone might be inclined to
put some legwork into seeing whether it can be fixed. However, it IS
documented to work as it does, and it doesn't seem totally ludicrous
to me, especially given that it's apparently written into the SQL
standard that way.

http://www.postgresql.org/docs/current/interactive/sql-keywords-appendix.html

From a technical point of view, allowing what you're asking for would
probably require doing undesirable things to our parser. I tried
changing AS to a type_func_name keyword just for kicks and it
unsurprisingly blows up... the problem seems to be basically that the
parser gets confused in a few cases about whether the word AS marks
the end of an expression or whether it's part of the expression, and
since it is limited to one token of look-ahead it can't see far enough
to figure out what's really going on. There are probably ways to
"fix" this but if the result would be that parsing overall is slower,
that's going to hurt a lot more people than the need to quote or
schema-qualify the word "as". Similarly, if we can retain the present
parsing speed but the error messages get less informative in some
situations, that's a much larger nuisance.

We actually put a fair amount of engineering effort into making sure
that we do not reserve keywords unnecessarily, and there are several
discussions about these topics in the pgsql-hackers archives,
including most recently with regard to CREATE INDEX CONCURRENTLY.
And, I think there is probably more that we can do in the future to
improve the situation over where we are today. But I suspect that
making AS less reserved would be fairly difficult and, even if it's
not, might garner opposition on the grounds that we might want to do
things in the future that would require us to re-reserve it, so I'm
not sure it's really worth putting a lot of work into it.

...Robert