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

The following bug has been logged online:

Bug reference:      5258
Logged by:          Ben Woosley
Email address:      ben.woosley@gmail.com
PostgreSQL version: 8.4.2
Operating system:   Mac OS 10.6
Description:        Unique and foreign key constraints fail on columns with
reserved names, but not check constraints
Details:

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.

On 2010-01-04, Ben Woosley <ben.woosley@gmail.com> wrote:
>
> The following bug has been logged online:
>
> Bug reference:      5258
> Logged by:          Ben Woosley
> Email address:      ben.woosley@gmail.com
> PostgreSQL version: 8.4.2
> Operating system:   Mac OS 10.6
> Description:        Unique and foreign key constraints fail on columns with
> reserved names, but not check constraints
> Details:
>
> 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.

have you tried quoting the infringing identifier.

create table ben ( "as" integer );
alter table ben add constraint ben_ident_bug unique("as");

http://www.postgresql.org/docs/8.4/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS

On mån, 2010-01-04 at 03:48 +0000, Ben Woosley wrote:
> 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.

Example please?

On Sun, Jan 3, 2010 at 10:48 PM, Ben Woosley <ben.woosley@gmail.com> wrote:
> Check constraints successfully accept columns named with reserved words w=
hen
> they are qualified by table using the . syntax, e.g. "check (mod(table.as,
> 2) =3D 0)"
>
> However, unique and foreign key constraints added using the "alter table =
add
> constraint" syntax fail on the column name. =A0At this point the statemen=
t has
> enough information (the host table name) to properly identify the column
> despite the unorthodox name. =A0Alternatively, you could allow the . synt=
ax
> qualification inside the argument to the constraint.
>
> Now, you may say that this is a reserved word and should never be used, b=
ut
> 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. =A0This 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.ht=
ml

=46rom 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

These work:
alter table books add constraint books_from_within check (books.from >=3D 5
and books.from  <  11);
alter table books add constraint books_as_whitelist check (books.as in
('whitelisted1','whitelisted2','whitelisted3'));

These don't:
alter table books add constraint books_from_reference foreign key (from)
references authors (id) ;
alter table books add constraint books_as_unique unique (as);

The key being that the former are scoped by table while the latter are
standalone, and the parser doesn't allow scoping.  However, the statement
itself has the table information, and AFAIK, these constraints are always
table-specific, so you should be able to check whether the column exists on
the table.

Basically, though the words are reserved, in the scope they're being used,
it seems they're unambiguous as to what use they're taking.

On Mon, Jan 4, 2010 at 5:36 AM, Peter Eisentraut <peter_e@gmx.net> wrote:

> On m=E5n, 2010-01-04 at 03:48 +0000, Ben Woosley wrote:
> > 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.
>
> Example please?
>
>

Ben Woosley <ben.woosley@gmail.com> writes:
> These work:
> alter table books add constraint books_from_within check (books.from >= 5
> and books.from  <  11);
> alter table books add constraint books_as_whitelist check (books.as in
> ('whitelisted1','whitelisted2','whitelisted3'));

> These don't:
> alter table books add constraint books_from_reference foreign key (from)
> references authors (id) ;
> alter table books add constraint books_as_unique unique (as);

The point is that in a qualified name, we allow ColLabel for the second
and subsequent names, whereas the first name (or only name for an
unqualified name) has to be ColId.  The odds that we are going to relax
that in general are not distinguishable from zero --- it's already as
loose as we can practically make it.

Ben is correct that we could probably allow an unqualified ColLabel in
the restricted context of foreign key or unique constraint column name
lists, but I don't really see the point.  There would still be other
contexts where you'd have to double-quote the name in order to reference
it without qualification.  And as Robert points out, doing that could
come back to haunt us later if we ever wanted to allow any other syntax
in that area.  (This is not something that's totally under our control,
either; the SQL committee owns that syntax not us.)

The long and the short of it is: use double quotes if you want to use a
reserved word as a column name.

            regards, tom lane

Thanks Tom,

I hadn't tried double-quoting, which worked fine. I'm only so well
acquainted with SQL and had only tried single quotes and backticks.

So, there is a way, and I'm satisfied with it.  "Bug" retracted - thanks!

-Ben

On Mon, Jan 4, 2010 at 1:30 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Ben Woosley <ben.woosley@gmail.com> writes:
> > These work:
> > alter table books add constraint books_from_within check (books.from >= 5
> > and books.from  <  11);
> > alter table books add constraint books_as_whitelist check (books.as in
> > ('whitelisted1','whitelisted2','whitelisted3'));
>
> > These don't:
> > alter table books add constraint books_from_reference foreign key (from)
> > references authors (id) ;
> > alter table books add constraint books_as_unique unique (as);
>
> The point is that in a qualified name, we allow ColLabel for the second
> and subsequent names, whereas the first name (or only name for an
> unqualified name) has to be ColId.  The odds that we are going to relax
> that in general are not distinguishable from zero --- it's already as
> loose as we can practically make it.
>
> Ben is correct that we could probably allow an unqualified ColLabel in
> the restricted context of foreign key or unique constraint column name
> lists, but I don't really see the point.  There would still be other
> contexts where you'd have to double-quote the name in order to reference
> it without qualification.  And as Robert points out, doing that could
> come back to haunt us later if we ever wanted to allow any other syntax
> in that area.  (This is not something that's totally under our control,
> either; the SQL committee owns that syntax not us.)
>
> The long and the short of it is: use double quotes if you want to use a
> reserved word as a column name.
>
>                        regards, tom lane
>