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 trigger
LANGUAGE plpgsql
AS $function$
BEGIN
INSERT INTO test_open_trigger (id, open)
VALUES (NEW.id, NEW.open);
RETURN NEW;
END;
$function$;
CREATE OR REPLACE FUNCTION test_close()
RETURNS trigger
LANGUAGE plpgsql
AS $function$
BEGIN
INSERT 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/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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 time zone |
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers