Thread: open and close columns in the NEW record not allowed
Hello One of our users is having a problem with a trigger in a system running postgresql 9.3. The problem is that pl/pgsql does not accept open and close as column names when used in the NEW record in a trigger function. This page: http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html does not say that they are reserved words in postgresql (although they are reserved words in the sql standard) In the other hand, postgres allows to create and update tables with columns named open/close without problems. We think the behavior should be consistent, either it is allow to use them or not, but not like it is today. --------------------------------------------- Test case: --------------------------------------------- CREATE TABLE test_open(id integer,open timestamp); CREATE TABLE test_close(id integer,close timestamp); CREATE TABLE test_close_trigger(id integer,close timestamp); CREATE TABLE test_open_trigger(id integer,open timestamp); CREATE OR REPLACE FUNCTION test_open()RETURNS triggerLANGUAGE plpgsql AS $function$ BEGININSERT INTO test_open_trigger (id, open)VALUES (NEW.id, NEW.open);RETURN NEW; END; $function$; CREATE OR REPLACE FUNCTION test_close()RETURNS triggerLANGUAGE plpgsql AS $function$ BEGININSERT INTO test_close_trigger (id, close)VALUES (NEW.id, NEW.close);RETURN NEW; END; $function$; # INSERT INTO test_open (id,open) VALUES (1,now()); INSERT 0 1 # INSERT INTO test_close (id,close) VALUES (1,now()); INSERT 0 1 # SELECT * FROM test_open;id | open ----+---------------------------- 1 | 2014-02-06 15:17:52.654977 (1 row) # SELECT * FROM test_close;id | close ----+---------------------------- 1 | 2014-02-06 15:17:53.893911 (1 row) CREATE TRIGGER test_open AFTER INSERT ON test_open FOR EACH ROW EXECUTE PROCEDURE test_open(); CREATE TRIGGER test_close AFTER INSERT ON test_close FOR EACH ROW EXECUTE PROCEDURE test_close(); # INSERT INTO test_open (id,open) VALUES (1,now()); ERROR: record "new" has no field "open" LINE 3: VALUES (NEW.id, NEW.open) ^ QUERY: INSERT INTO public.test_open_trigger(id, open)VALUES (NEW.id, NEW.open) CONTEXT: PL/pgSQL function test_open() line 3 at SQL statement # INSERT INTO test_close (id,close) VALUES (1,now()); ERROR: record "new" has no field "close" LINE 3: VALUES (NEW.id, NEW.close) ^ QUERY: INSERT INTO public.test_close_trigger(id, close)VALUES (NEW.id, NEW.close) CONTEXT: PL/pgSQL function test_close() line 3 at SQL statement --------------------------------------------- Thanks in advance. regards, -- Rafael Martinez Guerrero Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/
On 02/06/2014 06:35 AM, Rafael Martinez Guerrero wrote: > Hello > > One of our users is having a problem with a trigger in a system running > postgresql 9.3. > > The problem is that pl/pgsql does not accept open and close as column > names when used in the NEW record in a trigger function. > > This page: > http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html > does not say that they are reserved words in postgresql (although they > are reserved words in the sql standard) > > In the other hand, postgres allows to create and update tables with > columns named open/close without problems. > > We think the behavior should be consistent, either it is allow to use > them or not, but not like it is today. > The catch all from here: http://www.postgresql.org/docs/9.3/interactive/sql-keywords-appendix.html is: " As a general rule, if you get spurious parser errors for commands that contain any of the listed key words as an identifier you should try to quote the identifier to see if the problem goes away." Which indeed solves the problem on my end at least: test=> CREATE OR REPLACE FUNCTION public.test_open() RETURNS trigger LANGUAGE plpgsql AS $function$ BEGIN INSERT INTO test_open_trigger (id, open) VALUES (NEW.id, NEW."open"); RETURN NEW; END; $function$ ; test=> \d test_open Table "public.test_open" Column | Type | Modifiers --------+-----------------------------+----------- id | integer | open | timestamp without timezone | Triggers: test_open AFTER INSERT ON test_open FOR EACH ROW EXECUTE PROCEDURE test_open() test=> INSERT INTO test_open (id,open) VALUES (1,now()); INSERT 0 1 > > Thanks in advance. > > regards, > -- Adrian Klaver adrian.klaver@gmail.com
Rafael Martinez Guerrero <r.m.guerrero@usit.uio.no> writes: > The problem is that pl/pgsql does not accept open and close as column > names when used in the NEW record in a trigger function. Yup. Those words (and other words that can start a plpgsql statement) are reserved so far as plpgsql is concerned. > This page: > http://www.postgresql.org/docs/9.3/static/sql-keywords-appendix.html > does not say that they are reserved words in postgresql (although they > are reserved words in the sql standard) It is not the business of that page to document the behavior of plpgsql. Perhaps the plpgsql chapter should document what it considers to be reserved words, but for now, you could look at the list in http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob_plain;f=src/pl/plpgsql/src/pl_scanner.c > We think the behavior should be consistent, either it is allow to use > them or not, but not like it is today. That would require giving plpgsql a privileged position over all other PLs, which isn't going to happen ... regards, tom lane
On Thu, 2014-02-06 at 07:11 -0800, Adrian Klaver wrote: > On 02/06/2014 06:35 AM, Rafael Martinez Guerrero wrote: > > We think the behavior should be consistent, either it is allow to use > > them or not, but not like it is today. > > > > " As a general rule, if you get spurious parser errors for commands that > contain any of the listed key words as an identifier you should try to > quote the identifier to see if the problem goes away." > > > Which indeed solves the problem on my end at least: > Hello Thanks for the feedback. Our problem is that an application decides the name of the columns in the tables and "XDB replication" from EnterpriseDB decides the triggers. We have no control over the code :-( regards, -- Rafael Martinez Guerrero Center for Information Technology Services University of Oslo, Norway
On Thu, Feb 06, 2014 at 04:21:41PM +0100, Rafael Martinez Guerrero wrote: > On Thu, 2014-02-06 at 07:11 -0800, Adrian Klaver wrote: > > On 02/06/2014 06:35 AM, Rafael Martinez Guerrero wrote: > > > > We think the behavior should be consistent, either it is allow to use > > > them or not, but not like it is today. > > > > > > > " As a general rule, if you get spurious parser errors for commands that > > contain any of the listed key words as an identifier you should try to > > quote the identifier to see if the problem goes away." > > > > > > Which indeed solves the problem on my end at least: > > > > Hello > > Thanks for the feedback. > > Our problem is that an application decides the name of the columns in > the tables and "XDB replication" from EnterpriseDB decides the triggers. > We have no control over the code :-( > > regards, > -- > Rafael Martinez Guerrero > Center for Information Technology Services > University of Oslo, Norway > Hi Rafael, It sounds like a bug in the XDB trigger generation code. Maybe file a bug report. Regards, Ken
On Thu, Feb 6, 2014 at 10:27 AM, ktm@rice.edu <ktm@rice.edu> wrote: >> Thanks for the feedback. >> >> Our problem is that an application decides the name of the columns in >> the tables and "XDB replication" from EnterpriseDB decides the triggers. >> We have no control over the code :-( >> > > It sounds like a bug in the XDB trigger generation code. Maybe file a bug > report. +1. If you are using XDB, presumably that means you are an EntepriseDB customer and can file a support ticket. Zahid's team is usually very responsive, and this definitely sounds like a bug. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company