potential bug in trigger with boolean params

Started by Szymon Guzover 14 years ago13 messages

Szymon Guz

mabewlun@gmail.com

over 14 years ago

Hi,
I was trying to create a trigger with parameters. I've found a potential bug
when the param is boolean.

Here is code replicating the bug:

CREATE TABLE x(x TEXT);

CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$
BEGIN
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x('text');
CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x(10);
CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE trigger_x(42.0);
CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x(true);

ERROR: syntax error at or near "true"
LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true);

I've already checked that on:
'PostgreSQL 9.0.1, compiled by Visual C++ build 1500, 32-bit'
'PostgreSQL 9.0.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.1.2 20080704 (Red Hat 4.1.2-46), 64-bit'

If this is intended behavior, then the documentation doesn't say anything
about that.
The only information is that:

TG_ARGV[]

Data type array of text; the arguments from the CREATE TRIGGER statement.
but the below line throws the same error:

CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x( true::text );

while this obviously works:

SELECT true::text;

and this works as well:

CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x('true');
regards
Szymon

Noname

tv@fuzzy.cz

over 14 years ago

In reply to: Szymon Guz (#1)

Re: potential bug in trigger with boolean params

Hi,
I was trying to create a trigger with parameters. I've found a potential
bug
when the param is boolean.

Here is code replicating the bug:

CREATE TABLE x(x TEXT);

CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$
BEGIN
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x('text');
CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(10);
CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE trigger_x(42.0);
CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(true);

ERROR: syntax error at or near "true"
LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true);

The docs clearly state what the valid values are and the literal 'true' is
not one of them (TRUE is). See this:

http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

regards
Tomas

Andreas Joseph Krogh

andreak@officenet.no

over 14 years ago

In reply to: Noname (#2)

Re: potential bug in trigger with boolean params

På onsdag 11. mai 2011 kl 10:56:19 skrev <tv@fuzzy.cz>:

Hi,
I was trying to create a trigger with parameters. I've found a potential
bug
when the param is boolean.

Here is code replicating the bug:

CREATE TABLE x(x TEXT);

CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$
BEGIN
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x('text');
CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(10);
CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE trigger_x(42.0);
CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(true);

ERROR: syntax error at or near "true"
LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true);

The docs clearly state what the valid values are and the literal 'true' is
not one of them (TRUE is). See this:

http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

What are you trying to accomplish? "CREATE OR REPLACE FUNCTION trigger_x()" does not declare any formal-parameters, so calling it with arguments doesn't make sense. I'm surprised creating the other triggers didn't produce an error stating "No function defined with the name trigger_ix and the given argument-type".

--
Andreas Joseph Krogh <andreak@officenet.no>
Senior Software Developer / CTO
Public key: http://home.officenet.no/~andreak/public_key.asc
------------------------+---------------------------------------------+
OfficeNet AS | The most difficult thing in the world is to |
Rosenholmveien 25 | know how to do a thing and to watch |
1414 Trollåsen | somebody else doing it wrong, without |
NORWAY | comment. |
Org.nr: NO 981 479 076 | |
| |
Tlf: +47 24 15 38 90 | |
Fax: +47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
------------------------+---------------------------------------------+

Szymon Guz

mabewlun@gmail.com

over 14 years ago

In reply to: Noname (#2)

Re: potential bug in trigger with boolean params

On 11 May 2011 10:56, <tv@fuzzy.cz> wrote:

Hi,
I was trying to create a trigger with parameters. I've found a potential
bug
when the param is boolean.

Here is code replicating the bug:

CREATE TABLE x(x TEXT);

CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$
BEGIN
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x('text');
CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(10);
CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE trigger_x(42.0);
CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(true);

ERROR: syntax error at or near "true"
LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true);

The docs clearly state what the valid values are and the literal 'true' is
not one of them (TRUE is). See this:

http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

regards
Tomas

Well... no.

In the link you've provided there is something different:

Valid literal values for the "true" state are:

TRUE't''true''y''yes''on''1'

so I could use 'true'... and this doesn't work.

And SQL is not case sensitive... but I will check it for you anyway:

CREATE TRIGGER trig_x_2 BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x(TRUE);

ERROR: syntax error at or near "TRUE"
LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(TRUE);

regards
Szymon

Andres Freund

andres@anarazel.de

over 14 years ago

In reply to: Andreas Joseph Krogh (#3)

Re: potential bug in trigger with boolean params

On Wednesday, May 11, 2011 11:01:56 AM Andreas Joseph Krogh wrote:

På onsdag 11. mai 2011 kl 10:56:19 skrev <tv@fuzzy.cz>:

CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(true);

The docs clearly state what the valid values are and the literal 'true'
is not one of them (TRUE is). See this:

http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

What are you trying to accomplish? "CREATE OR REPLACE FUNCTION trigger_x()"
does not declare any formal-parameters, so calling it with arguments
doesn't make sense. I'm surprised creating the other triggers didn't
produce an error stating "No function defined with the name trigger_ix and
the given argument-type".

Read the docs. Parameters for triggers are not passed as normal function
parameters. Thats why you access them via via TG_ARGV in plpgsql.

The grammar accepts only a very limited amount of parameters there:

Andres Freund

andres@anarazel.de

over 14 years ago

In reply to: Andres Freund (#5)

Re: potential bug in trigger with boolean params

On Wednesday, May 11, 2011 11:21:34 AM Andres Freund wrote:

On Wednesday, May 11, 2011 11:01:56 AM Andreas Joseph Krogh wrote:

På onsdag 11. mai 2011 kl 10:56:19 skrev <tv@fuzzy.cz>:

CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(true);

The docs clearly state what the valid values are and the literal 'true'
is not one of them (TRUE is). See this:

http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

What are you trying to accomplish? "CREATE OR REPLACE FUNCTION
trigger_x()" does not declare any formal-parameters, so calling it with
arguments doesn't make sense. I'm surprised creating the other triggers
didn't produce an error stating "No function defined with the name
trigger_ix and the given argument-type".

Read the docs. Parameters for triggers are not passed as normal function
parameters. Thats why you access them via via TG_ARGV in plpgsql.

The grammar accepts only a very limited amount of parameters there:

Err....

TriggerFuncArg:
Iconst
{
char buf[64];
snprintf(buf, sizeof(buf), "%d", $1);
$$ = makeString(pstrdup(buf));
}
| FCONST { $$ = makeString($1); }
| Sconst { $$ = makeString($1); }
| BCONST { $$ = makeString($1); }
| XCONST { $$ = makeString($1); }
| ColId { $$ = makeString($1); }

That is integers, floating point, strings, bitstrings, hexstrings and column references (???).

How that exact list came to exist I do not know.

Andres

Andreas Joseph Krogh

andreak@officenet.no

over 14 years ago

In reply to: Andres Freund (#6)

Re: potential bug in trigger with boolean params

På onsdag 11. mai 2011 kl 11:30:51 skrev Szymon Guz <<a href="mailto:mabewlun@gmail.com">mabewlun@gmail.com</a>>:
<blockquote style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"> 
 
<div class="gmail_quote">On 11 May 2011 11:01, Andreas Joseph Krogh <<a href="mailto:andreak@officenet.no">andreak@officenet.no</a>> wrote: 
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;"> På onsdag 11. mai 2011 kl 10:56:19 skrev <<a href="mailto:tv@fuzzy.cz">tv@fuzzy.cz</a>>: 
<div>
<div> </div>
<div class="h5">> > Hi, 
> > I was trying to create a trigger with parameters. I've found a potential 
> > bug 
> > when the param is boolean. 
> > 
> > Here is code replicating the bug: 
> > 
> > CREATE TABLE x(x TEXT); 
> > 
> > CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$ 
> > BEGIN 
> >         RETURN NEW; 
> > END; $$ LANGUAGE PLPGSQL; 
> > 
> > CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE 
> > PROCEDURE 
> > trigger_x('text'); 
> > CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE 
> > PROCEDURE 
> > trigger_x(10); 
> > CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE 
> > PROCEDURE trigger_x(42.0); 
> > CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE 
> > PROCEDURE 
> > trigger_x(true); 
> > 
> > ERROR:  syntax error at or near "true" 
> > LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true); 
> 
> The docs clearly state what the valid values are and the literal 'true' is 
> not one of them (TRUE is). See this: 
> 
> <a target="_blank" href="http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html">http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html</a> 
</div>
</div>
What are you trying to accomplish? "CREATE OR REPLACE FUNCTION trigger_x()" does not declare any formal-parameters, so calling it with arguments doesn't make sense. I'm surprised creating the other triggers didn't produce an error stating "No function defined with the name trigger_ix and the given argument-type". 
 
</blockquote>
<div> </div>
<div>That's how you define trigger function. Later you can use params when defining trigger.</div>
</div>
</blockquote>
Pardon my ignorance:-) 
 
-- 
Andreas Joseph Krogh <andreak@officenet.no> 
Senior Software Developer / CTO 
Public key: http://home.officenet.no/~andreak/public_key.asc 
------------------------+---------------------------------------------+ 
OfficeNet AS            | The most difficult thing in the world is to | 
Rosenholmveien 25       | know how to do a thing and to watch         | 
1414 Trollåsen          | somebody else doing it wrong, without       | 
NORWAY                  | comment.                                    | 
Org.nr: NO 981 479 076  |                                             | 
                        |                                             | 
Tlf:    +47 24 15 38 90 |                                             | 
Fax:    +47 24 15 38 91 |                                             | 
Mobile: +47 909  56 963 |                                             | 
------------------------+---------------------------------------------+

From pgsql-hackers-owner@postgresql.org Wed May 11 06:51:00 2011

Received: from maia.hub.org (maia-2.hub.org [200.46.204.251])
by mail.postgresql.org (Postfix) with ESMTP id E65541337B47
for <pgsql-hackers-postgresql.org@mail.postgresql.org>; Wed, 11 May 2011 06:50:59 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024)
with ESMTP id 01606-05-2
for <pgsql-hackers-postgresql.org@mail.postgresql.org>;
Wed, 11 May 2011 09:50:42 +0000 (UTC)
X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6
Received: from exprod7og115.obsmtp.com (exprod7og115.obsmtp.com [64.18.2.217])
by mail.postgresql.org (Postfix) with ESMTP id 7CF7A1337BBB
for <pgsql-hackers@postgresql.org>; Wed, 11 May 2011 06:50:41 -0300 (ADT)
Received: from mail-ey0-f181.google.com ([209.85.215.181]) (using TLSv1) by exprod7ob115.postini.com ([64.18.6.12]) with SMTP
ID DSNKTcpb8Vg949PvnYB3Ayl45TPvvxtkYYKn@postini.com; Wed, 11 May 2011 02:50:42 PDT
Received: by eyh5 with SMTP id 5so102603eyh.26
for <pgsql-hackers@postgresql.org>; Wed, 11 May 2011 02:50:40 -0700 (PDT)
Received: by 10.213.25.82 with SMTP id y18mr134026ebb.76.1305107439750;
Wed, 11 May 2011 02:50:39 -0700 (PDT)
Received: from [192.168.1.183] (dsl-hkibrasgw2-ff7ac300-240.dhcp.inet.fi [88.195.122.240])
by mx.google.com with ESMTPS id y9sm4966885eeh.8.2011.05.11.02.50.37
(version=TLSv1/SSLv3 cipher=OTHER);
Wed, 11 May 2011 02:50:38 -0700 (PDT)
Message-ID: <4DCA5BEC.2020805@enterprisedb.com>
Date: Wed, 11 May 2011 12:50:36 +0300
From: Heikki Linnakangas <heikki.linnakangas@enterprisedb.com>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; fi-FI; rv:1.9.1.16) Gecko/20110307 Icedove/3.0.11
MIME-Version: 1.0
To: Fujii Masao <masao.fujii@gmail.com>
CC: Robert Haas <robertmhaas@gmail.com>,
Jaime Casanova <jaime@2ndquadrant.com>,
pgsql-hackers@postgresql.org
Subject: Re: time-delayed standbys
References: <BANLkTi==TTzHDqWzwJDjmOf__8YuA7L1jw@mail.gmail.com> <BANLkTikHcD8H=iS=xZCTnyNAYMbDu3Tj1Q@mail.gmail.com> <BANLkTinfrgsVK_8o9+mw6kWRcC4BxiR4jw@mail.gmail.com> <BANLkTimDUswEE5nAjr31DQ=6GxRPU758kQ@mail.gmail.com>
In-Reply-To: <BANLkTimDUswEE5nAjr31DQ=6GxRPU758kQ@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=-1.9 tagged_above=-5 required=5 tests=BAYES_00=-1.9
X-Spam-Level:
X-Archive-Number: 201105/623
X-Sequence-Number: 187657

On 11.05.2011 08:29, Fujii Masao wrote:

On Sat, May 7, 2011 at 10:48 PM, Robert Haas<robertmhaas@gmail.com> wrote:

I was able to reproduce something very like this in unpatched master,
just by letting recovery pause at a named restore point, and then
resuming it.

I was able to reproduce the same problem even in 9.0. When the standby
reaches the recovery target, it always tries to end the recovery even
though walreceiver is still running, which causes the problem. This seems
to be an oversight in streaming replication. I should have considered how
the standby should work when recovery_target is specified.

What about the attached patch? Which stops walreceiver instead of
emitting PANIC there only if we've reached the recovery target.

I think we can just always call ShutdownWalRcv(). It should be gone if
the server was promoted while streaming, but that's just an
implementation detail of what the promotion code does. There's no hard
reason why it shouldn't be running at that point anymore, as long as we
kill it before going any further.

Committed a patch to do that.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Import Notes

Reply to msg id not found: BANLkTiko7krMtzCWX09BEk-wEDxdS8WhiQ@mail.gmail.com | Resolved by subject fallback

Szymon Guz

mabewlun@gmail.com

over 14 years ago

In reply to: Andreas Joseph Krogh (#3)

Re: potential bug in trigger with boolean params

On 11 May 2011 11:01, Andreas Joseph Krogh <andreak@officenet.no> wrote:

På onsdag 11. mai 2011 kl 10:56:19 skrev <tv@fuzzy.cz>:

Hi,
I was trying to create a trigger with parameters. I've found a

potential

bug
when the param is boolean.

Here is code replicating the bug:

CREATE TABLE x(x TEXT);

CREATE OR REPLACE FUNCTION trigger_x() RETURNS TRIGGER AS $$
BEGIN
RETURN NEW;
END; $$ LANGUAGE PLPGSQL;

CREATE TRIGGER trig_x_text BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x('text');
CREATE TRIGGER trig_x_int BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(10);
CREATE TRIGGER trig_x_float BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE trigger_x(42.0);
CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE
trigger_x(true);

ERROR: syntax error at or near "true"
LINE 1: ... INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(true);

The docs clearly state what the valid values are and the literal 'true'

is

not one of them (TRUE is). See this:

http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.html

What are you trying to accomplish? "CREATE OR REPLACE FUNCTION trigger_x()"
does not declare any formal-parameters, so calling it with arguments doesn't
make sense. I'm surprised creating the other triggers didn't produce an
error stating "No function defined with the name trigger_ix and the given
argument-type".

That's how you define trigger function. Later you can use params when
defining trigger.

regards
Szymon

Andres Freund

andres@anarazel.de

over 14 years ago

In reply to: Andreas Joseph Krogh (#3)

Re: potential bug in trigger with boolean params

On Wednesday, May 11, 2011 11:50:35 AM Szymon Guz wrote:

On 11 May 2011 11:29, Andres Freund <andres@anarazel.de> wrote:

On Wednesday, May 11, 2011 11:21:34 AM Andres Freund wrote:

On Wednesday, May 11, 2011 11:01:56 AM Andreas Joseph Krogh wrote:

På onsdag 11. mai 2011 kl 10:56:19 skrev <tv@fuzzy.cz>:

CREATE TRIGGER trig_x_bool BEFORE INSERT ON x FOR EACH ROW
EXECUTE PROCEDURE
trigger_x(true);

The docs clearly state what the valid values are and the literal

'true'

is not one of them (TRUE is). See this:

http://www.postgresql.org/docs/9.0/interactive/datatype-boolean.htm
l

What are you trying to accomplish? "CREATE OR REPLACE FUNCTION
trigger_x()" does not declare any formal-parameters, so calling it
with arguments doesn't make sense. I'm surprised creating the other
triggers didn't produce an error stating "No function defined with
the name trigger_ix and the given argument-type".

Read the docs. Parameters for triggers are not passed as normal
function parameters. Thats why you access them via via TG_ARGV in
plpgsql.

The grammar accepts only a very limited amount of parameters there:

Err....

TriggerFuncArg:
Iconst

{

char buf[64];
snprintf(buf, sizeof(buf), "%d",

$1);

$$ = makeString(pstrdup(buf));

}
|
| FCONST

{ $$ = makeString($1); }

| Sconst

{ $$ = makeString($1); }

| BCONST

{ $$ = makeString($1); }

| XCONST

{ $$ = makeString($1); }

| ColId

{ $$ = makeString($1); }

That is integers, floating point, strings, bitstrings, hexstrings and
column references (???).

How that exact list came to exist I do not know.

My two thoughts on that:

1. This list should be improved to allow booleans, and maybe other types

2. Why then is it like this:

it works:
CREATE TRIGGER trig_x_10 BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x('true');

it does not:
CREATE TRIGGER trig_x_11 BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x('true'::text);

the error is:
ERROR: syntax error at or near "::"

I think there is something wrong.

The grammar doesn't allow any form of expression. It only allows the above
listed types of literals directly.
I am not really sure why it was done that way, but its been that way for a
long time (only insignificant changes since 1997... bitstrings and hex strings
were added after that though).

Why do you wan't to use a boolean directly if you can't use it as the type
itself anyway?

Andres

Import Notes

Reply to msg id not found: BANLkTifvfqanxRTGE_uYwiSOEiN03kEQ@mail.gmail.com

#10

Szymon Guz

mabewlun@gmail.com

over 14 years ago

In reply to: Andres Freund (#9)

Re: potential bug in trigger with boolean params

On 11 May 2011 12:06, Andres Freund <andres@anarazel.de> wrote:

Why do you wan't to use a boolean directly if you can't use it as the type
itself anyway?

Yep, and this is a really good point :)
I wanted to have consistent api, so use true when I have a boolean value.
I will use 'true' and add some info on that to the procedure documentation.

regards
Szymon

#11

Tom Lane

tgl@sss.pgh.pa.us

over 14 years ago

In reply to: Andres Freund (#6)

Re: potential bug in trigger with boolean params

Andres Freund <andres@anarazel.de> writes:

The grammar accepts only a very limited amount of parameters there:

Err....

TriggerFuncArg:
Iconst
{
char buf[64];
snprintf(buf, sizeof(buf), "%d", $1);
$$ = makeString(pstrdup(buf));
}
| FCONST { $$ = makeString($1); }
| Sconst { $$ = makeString($1); }
| BCONST { $$ = makeString($1); }
| XCONST { $$ = makeString($1); }
| ColId { $$ = makeString($1); }

That is integers, floating point, strings, bitstrings, hexstrings and column references (???).

How that exact list came to exist I do not know.

The documentation for CREATE FUNCTION says

arguments:
An optional comma-separated list of arguments to be provided to
the function when the trigger is executed. The arguments are
literal string constants. Simple names and numeric constants can
be written here, too, but they will all be converted to
strings.

The ColId case is meant to cover the "simple names" proviso, but of
course it fails to cover reserved words. We could trivially fix that
by writing ColLabel instead of ColId. My initial expectation was that
this would bloat the parser, but it seems not to: the backend gram.o
is exactly the same size after making the change, and ecpg's preproc.o
actually gets smaller (more opportunity to share states?). So I'm
inclined to do it, rather than having to document that "simple names"
excludes reserved words.

A possibly more interesting question is why BCONST and XCONST were added
there. The documentation certainly does not say or suggest that those
are legal options, and what's more the behavior could be considered
surprising:

regression=# CREATE TRIGGER trig_x_bconst BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(b'1011');
CREATE TRIGGER
regression=# CREATE TRIGGER trig_x_xconst BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x(x'1234abcd');
CREATE TRIGGER
regression=# \d+ x
...
Triggers:
trig_x_bconst BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x('b1011')
trig_x_xconst BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE trigger_x('x1234abcd')

I'm inclined to take those out, because (1) I find it shrinks the
generated grammar a tad (these productions *do* add to the size of the
state tables), and (2) if we don't, we ought to document this behavior,
and I don't want to do that either.

I see this as just a change to make in HEAD, it's not appropriate for
a back-patch.

Objections anyone?

regards, tom lane

#12

Andres Freund

andres@anarazel.de

over 14 years ago

In reply to: Tom Lane (#11)

Re: potential bug in trigger with boolean params

On Wednesday, May 11, 2011 07:25:58 PM Tom Lane wrote:

Andres Freund <andres@anarazel.de> writes:

The grammar accepts only a very limited amount of parameters there:

Err....

TriggerFuncArg:
Iconst

{

char buf[64];
snprintf(buf, sizeof(buf), "%d", $1);
$$ = makeString(pstrdup(buf));

}
|
| FCONST { $$ =
| makeString($1); } Sconst {
| $$ = makeString($1); } BCONST
| { $$ = makeString($1); } XCONST
| { $$ = makeString($1); } ColId
| { $$ = makeString($1); }

That is integers, floating point, strings, bitstrings, hexstrings and
column references (???).

How that exact list came to exist I do not know.

The documentation for CREATE FUNCTION says

arguments:
An optional comma-separated list of arguments to be provided to
the function when the trigger is executed. The arguments are
literal string constants. Simple names and numeric constants can
be written here, too, but they will all be converted to
strings.

The ColId case is meant to cover the "simple names" proviso, but of
course it fails to cover reserved words. We could trivially fix that
by writing ColLabel instead of ColId. My initial expectation was that
this would bloat the parser, but it seems not to: the backend gram.o
is exactly the same size after making the change, and ecpg's preproc.o
actually gets smaller (more opportunity to share states?). So I'm
inclined to do it, rather than having to document that "simple names"
excludes reserved words.

Good.

A possibly more interesting question is why BCONST and XCONST were added
there. The documentation certainly does not say or suggest that those
are legal options, and what's more the behavior could be considered
surprising:

It seems to have originally been added there by Peter (as BITCONST) and then
split by Thomas Lockhart.

See 73874a06 and eb121ba2

regression=# CREATE TRIGGER trig_x_bconst BEFORE INSERT ON x FOR EACH ROW
EXECUTE PROCEDURE trigger_x(b'1011'); CREATE TRIGGER
regression=# CREATE TRIGGER trig_x_xconst BEFORE INSERT ON x FOR EACH ROW
EXECUTE PROCEDURE trigger_x(x'1234abcd'); CREATE TRIGGER
regression=# \d+ x
...
Triggers:
trig_x_bconst BEFORE INSERT ON x FOR EACH ROW EXECUTE PROCEDURE
trigger_x('b1011') trig_x_xconst BEFORE INSERT ON x FOR EACH ROW EXECUTE
PROCEDURE trigger_x('x1234abcd')

Err. Yes, that looks rather strange. And surprising.

I'm inclined to take those out, because (1) I find it shrinks the
generated grammar a tad (these productions *do* add to the size of the
state tables), and (2) if we don't, we ought to document this behavior,
and I don't want to do that either.
I see this as just a change to make in HEAD, it's not appropriate for
a back-patch.

I would say the above behaviour even is a bug. But given that I haven't
seen/found anybody complaining about it fixing it properly looks pointless.
So yes, HEAD only sounds fine.

Objections anyone?

Nope.

Is there a special reason for not using the normal function calling
mechanisms? It looks to me as it was just done to have an easy way to store it
in pg_trigger.tgargs.

Andres

#13

Tom Lane

tgl@sss.pgh.pa.us

over 14 years ago

In reply to: Andres Freund (#12)

Re: potential bug in trigger with boolean params

Andres Freund <andres@anarazel.de> writes:

Is there a special reason for not using the normal function calling
mechanisms? It looks to me as it was just done to have an easy way to store it
in pg_trigger.tgargs.

Well, this is all very historical, dating from Berkeley days AFAIK.
If we had it to do over, I bet we'd do it differently --- but the pain
of changing it seems to exceed any likely benefit.

regards, tom lane