[DOCS] Doc BUG and/or Change Request: Include Postgres's system columns in reserved words in docs, Chnage to previx sytem colums with 2 underscores?

[dan d]

Vitals:

Postgres 9.6 on Windows 7

by way of NpgSql C# library (v4.0.30319)



DOC BUG:
I'm curious if PostgreSQL's own special columns should be included on the 
"Reserved Words" list.  This might be obvious to those familiar with the inner 
working of Postgres, for us that are more application level developers this may 
not be as obvious..  Easy to know the core sql tokens are reserved such as 
"select, min, like, from, and others..." are all reserved.  The issue was 
encountered when trying to create a table with cmax as a column name.   
Following the instructions on filing bugs I searched the main site and 
eventually found it is documented but not clearly.  Found that cmax is a system 
column reserved by Postgres.  At first when i got the error specifically this 
one:


Npgsql.PostgresException (0x80004005): 42701: column name "cmax" conflicts with 
a system column name

My first step was to google "Postgres Reserved Words"   will eventually get you 
to Appendix C in the doc but not mention about any system columns, url for help 
page:

<https://www.postgresql.org/docs/9.6/static/sql-keywords-appendix.html>https://www.postgresql.org/docs/9.6/static/sql-keywords-appendix.html

title on page shows (This page in other versions: 
9.2<https://www.postgresql.org/docs/9.2/static/ddl-system-columns.html> / 
9.3<https://www.postgresql.org/docs/9.3/static/ddl-system-columns.html> / 
9.4<https://www.postgresql.org/docs/9.4/static/ddl-system-columns.html> / 
9.5<https://www.postgresql.org/docs/9.5/static/ddl-system-columns.html> / 
current (9.6) )

It wasn't until i looked at the process to open a bug, did it get to the main 
Postgres site and searched for cmax.  Searching on the Postgres site, bingo, I 
found cmax instead is a system column.  So it seems at first to be a 
documentation bug that Postgres's own reserved column names are not put in the 
"Reserved Words" list.


Change Request:


Got me thinking what the convention use to be in C starting out 30 years ago, 
should work good in Postgres as well?  Most all C compilers would prefix 
variables or items within system  struct that were considered system/reserved 
with an underscores or two.  This worked great before C++ which brought classes 
and namespaces to assist in scoping.  This practice worked well to prevent name 
collisions between users code the compiler itself should work nicely in 
Postgres too.  Finally if this convention is adapted there could be one generic 
note in the docs saying "User columns should never being with an underscore."  
This convention worked well for C before C++ exists, should work good for 
Postgres too?

Thanks for your consideration.

dan d:)

Re: [DOCS] Doc BUG and/or Change Request: Include Postgres's system columns in reserved words in docs, Chnage to previx sytem colums with 2 underscores?

[dan d]

There is one other issue that I forgot to mention,  my program is taking 
existing databases and converting and loading them into a new Postgres DB, 
strange that one of my first test database actually had cmax as a column, 
unfortunately i'm not at liberty to go back and tell my customer you need to 
change your database columns...   I'd argue we can be assured SQL reserved 
words won't be used for the obvious reason, but not Postgres's system columns,  
because of a short sighted design flaw.   Hard for me to go back and tell a 
MSSQL or Oracle DBA use you need to change your database and stop using 
Postgres's reserved column names...   That isn't gonna happen, and I would 
argue they have a valid point Postgres shouldn't impose any additional 
restrictions on reserved words besides what SQL imposes, especially when it 
would be easily for Postges just as compilers did to prefix system required 
column/variables with underscores.

The fix is really simply prefix system reserved column names with one or two 
underscores.  Simple clean prevents the issue between user code and database 
code...



From: David G. Johnston 
Sent: Tuesday, October 3, 2017 7:25 AM
To: Euler Taveira
Cc: dan d; pgsql-docs@postgresql.org
Subject: Re: [DOCS] Doc BUG and/or Change Request: Include Postgres's system 
columns in reserved words in docs, Chnage to previx sytem colums with 2 
underscores?

On Tue, Oct 3, 2017 at 6:13 AM, Euler Taveira 
mailto:eu...@timbira.com.br>> wrote:
2017-10-02 22:30 GMT-03:00 dan d 
mailto:dano...@hotmail.com>>:
> Npgsql.PostgresException (0x80004005): 42701: column name "cmax" conflicts
> with a system column name
>
> My first step was to google "Postgres Reserved Words"   will eventually get
> you to Appendix C in the doc but not mention about any system columns, url
> for help page:
>
> https://www.postgresql.org/docs/9.6/static/sql-keywords-appendix.html
>
The title is clear: *SQL* Key Words. It is not *PostgreSQL* Key Words.
BTW, system columns are documented [1].

That's a bit unconvincing since there are words on that table that are not 
marked as being reserved in any of the three SQL standard columns.

Its user-error that an error message clearly indicating "system column name" 
prompted a search for reserved words instead of "system columns".

The main distinction here is that "cmax" is a valid identifier expect when used 
as a table column name (you can, for instance, name columns of CTEs cmax) due 
to it already being in use.  Given the clarity of the error message and the 
fact that this is first I've seen the particular complaint in many years of 
monitoring these lists, I'd say trying to add system columns to the listing of 
reserved words in the interest of "comprehensiveness" doesn't outweigh the 
complexity that would be introduced explaining how it differs from other 
reserved words.

I wouldn't be opposed to adding a sentence and cross-reference link to the 
system columns page and pointing out that specific column names are also 
already reserved for system use.

David J.