Re: Why do OLD and NEW have special internal names?

So I was testing the next step of plpgsql modification, namely actually
letting the parser hooks do something, and it promptly blew up in
trigger functions, like so:

+ ERROR: OLD used in query that is not in a rule
+ LINE 1: SELECT OLD
+ ^
+ QUERY: SELECT OLD
+ CONTEXT: SQL statement in PL/PgSQL function "trigger_data" near line 35

The reason is that because plpgsql is no longer translating references
to its names into Params before letting the core parser see them, the
kluge in gram.y that changes "OLD" to "*OLD*" and "NEW" to "*NEW*"
kicks in, or actually decides to throw an error instead of kicking in.

I am wondering what is the point at all of having that kluge. It
certainly doesn't manage to make OLD/NEW not act like reserved words,
in fact rather more the opposite, as shown here. If we just made those
names be ordinary table alias names in rule queries, wouldn't things
work as well or better?

BTW, this brings up another point, which is that up to now it's often
been possible to use plpgsql variable names that conflict with
core-parser reserved words, so long as you didn't need to use the
reserved word with its special meaning. That will stop working when
this patch goes in. Doesn't bother me any, but if anyone thinks it's
a serious problem, speak now.

regards, tom lane

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> been possible to use plpgsql variable names that conflict with
> core-parser reserved words, so long as you didn't need to use the
> reserved word with its special meaning. That will stop working when
> this patch goes in. Doesn't bother me any, but if anyone thinks
it's
> a serious problem, speak now.

As long as I can use anything I want when it's quoted, I don't care.

-Kevin

On Thu, Nov 5, 2009 at 4:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> So I was testing the next step of plpgsql modification, namely actually
> letting the parser hooks do something, and it promptly blew up in
> trigger functions, like so:
>
> + ERROR:  OLD used in query that is not in a rule
> + LINE 1: SELECT  OLD
> +                 ^
> + QUERY:  SELECT  OLD
> + CONTEXT:  SQL statement in PL/PgSQL function "trigger_data" near line 35
>
> The reason is that because plpgsql is no longer translating references
> to its names into Params before letting the core parser see them, the
> kluge in gram.y that changes "OLD" to "*OLD*" and "NEW" to "*NEW*"
> kicks in, or actually decides to throw an error instead of kicking in.
>
> I am wondering what is the point at all of having that kluge.  It
> certainly doesn't manage to make OLD/NEW not act like reserved words,
> in fact rather more the opposite, as shown here.  If we just made those
> names be ordinary table alias names in rule queries, wouldn't things
> work as well or better?
>
> BTW, this brings up another point, which is that up to now it's often
> been possible to use plpgsql variable names that conflict with
> core-parser reserved words, so long as you didn't need to use the
> reserved word with its special meaning.  That will stop working when
> this patch goes in.  Doesn't bother me any, but if anyone thinks it's
> a serious problem, speak now.

Any keyword or just fully reserved keywords?

...Robert

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> BTW, this brings up another point, which is that up to now it's often
>> been possible to use plpgsql variable names that conflict with
>> core-parser reserved words, so long as you didn't need to use the
>> reserved word with its special meaning. That will stop working when
>> this patch goes in. Doesn't bother me any, but if anyone thinks it's
>> a serious problem, speak now.

> Any keyword or just fully reserved keywords?

Anything that's not allowed as a column name will be at issue.

regards, tom lane

On Thu, Nov 5, 2009 at 5:33 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> BTW, this brings up another point, which is that up to now it's often
> been possible to use plpgsql variable names that conflict with
> core-parser reserved words, so long as you didn't need to use the
> reserved word with its special meaning.  That will stop working when
> this patch goes in.  Doesn't bother me any, but if anyone thinks it's
> a serious problem, speak now.

I imagine there will be a small percentage of PL/pgSQL users that will
be afected, so these changes must be well written up in the PL/pgSQL
documentation.

Roberto

On Fri, Nov 6, 2009 at 1:02 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>>> BTW, this brings up another point, which is that up to now it's often
>>> been possible to use plpgsql variable names that conflict with
>>> core-parser reserved words, so long as you didn't need to use the
>>> reserved word with its special meaning.  That will stop working when
>>> this patch goes in.  Doesn't bother me any, but if anyone thinks it's
>>> a serious problem, speak now.
>
>> Any keyword or just fully reserved keywords?
>
> Anything that's not allowed as a column name will be at issue.

Well, that's not so bad. If it included unreserved keywords I think
that would be more of an issue.

...Robert

On Thu, Nov 05, 2009 at 04:33:07PM -0500, Tom Lane wrote:
> So I was testing the next step of plpgsql modification, namely actually
> letting the parser hooks do something, and it promptly blew up in
> trigger functions, like so:
>
> + ERROR: OLD used in query that is not in a rule
> + LINE 1: SELECT OLD
> + ^
> + QUERY: SELECT OLD
> + CONTEXT: SQL statement in PL/PgSQL function "trigger_data" near line 35
>
> The reason is that because plpgsql is no longer translating references
> to its names into Params before letting the core parser see them, the
> kluge in gram.y that changes "OLD" to "*OLD*" and "NEW" to "*NEW*"
> kicks in, or actually decides to throw an error instead of kicking in.
>
> I am wondering what is the point at all of having that kluge. It
> certainly doesn't manage to make OLD/NEW not act like reserved words,
> in fact rather more the opposite, as shown here. If we just made those
> names be ordinary table alias names in rule queries, wouldn't things
> work as well or better?
>
> BTW, this brings up another point, which is that up to now it's often
> been possible to use plpgsql variable names that conflict with
> core-parser reserved words, so long as you didn't need to use the
> reserved word with its special meaning. That will stop working when
> this patch goes in. Doesn't bother me any, but if anyone thinks it's
> a serious problem, speak now.

Sorry to be late on this :(

There's been a suggestion, to be filled in later, that
UPDATE...RETURNING be able to return (expressions on) columns from
both the old row and the new one. The syntax would involve either
using OLD to specify old rows, or both NEW and OLD if we want to break
current behavior. Would this make that easier to do? Harder? Is it
orthogonal?

Cheers,
David (who thinks that both NEW and OLD are object names so poor we
should force quoting them just on aesthetic principle ;)
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david(dot)fetter(at)gmail(dot)com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Tom,

> BTW, this brings up another point, which is that up to now it's often
> been possible to use plpgsql variable names that conflict with
> core-parser reserved words, so long as you didn't need to use the
> reserved word with its special meaning. That will stop working when
> this patch goes in. Doesn't bother me any, but if anyone thinks it's
> a serious problem, speak now.

Sounds like a potential *big* blocker to upgrading; anyone with several
thousand lines of plpgsql can't really afford to refactor away all of
the accidental uses of reserved words.

That being said, reusing reserved words in this way was always wonky, so
I'm not sure how many people will have done so. Best way is to commit
it to alpha3, and try to get people to test.

--Josh Berkus

On 11/5/2009 4:33 PM, Tom Lane wrote:
> So I was testing the next step of plpgsql modification, namely actually
> letting the parser hooks do something, and it promptly blew up in
> trigger functions, like so:
>
> + ERROR: OLD used in query that is not in a rule
> + LINE 1: SELECT OLD
> + ^
> + QUERY: SELECT OLD
> + CONTEXT: SQL statement in PL/PgSQL function "trigger_data" near line 35
>
> The reason is that because plpgsql is no longer translating references
> to its names into Params before letting the core parser see them, the
> kluge in gram.y that changes "OLD" to "*OLD*" and "NEW" to "*NEW*"
> kicks in, or actually decides to throw an error instead of kicking in.
>
> I am wondering what is the point at all of having that kluge. It
> certainly doesn't manage to make OLD/NEW not act like reserved words,
> in fact rather more the opposite, as shown here. If we just made those
> names be ordinary table alias names in rule queries, wouldn't things
> work as well or better?

Sorry, I don't recall what the exact point back then, when plpgsql was
created for 6.WHAT_VERSION, really was.

But this brings up another point about the recent discussion of what
RENAME is good for. Removing RENAME may conflict with using OLD/NEW in
UPDATE ... RETURNING. No?

Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

On 11/20/2009 1:12 AM, Tom Lane wrote:
> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>> But this brings up another point about the recent discussion of what
>> RENAME is good for. Removing RENAME may conflict with using OLD/NEW in
>> UPDATE ... RETURNING. No?
>
> Um ... not sure why. Specific example please?
>
> regards, tom lane

Inside a trigger proc, NEW is supposed to mean the new row for the table
that fired the trigger. However, inside an UPDATE RETURNING for example,
there is another set of NEW and OLD. Let's call the trigger call's NEW
NEW_a and the UPDATE RETURNING NEW NEW_b. How would the developer
specify something like

INSERT ... RETURNING (NEW_a.value - NEW_b.value)?

Jan

--
Anyone who trades liberty for security deserves neither
liberty nor security. -- Benjamin Franklin

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> But this brings up another point about the recent discussion of what
> RENAME is good for. Removing RENAME may conflict with using OLD/NEW in
> UPDATE ... RETURNING. No?

Um ... not sure why. Specific example please?

regards, tom lane

Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
> On 11/20/2009 1:12 AM, Tom Lane wrote:
>> Jan Wieck <JanWieck(at)Yahoo(dot)com> writes:
>>> But this brings up another point about the recent discussion of what
>>> RENAME is good for. Removing RENAME may conflict with using OLD/NEW in
>>> UPDATE ... RETURNING. No?
>>
>> Um ... not sure why. Specific example please?

> Inside a trigger proc, NEW is supposed to mean the new row for the table
> that fired the trigger. However, inside an UPDATE RETURNING for example,
> there is another set of NEW and OLD.

Uh, no there isn't ... and if there were I suppose it'd act much like
a query-local alias.

> Let's call the trigger call's NEW
> NEW_a and the UPDATE RETURNING NEW NEW_b. How would the developer
> specify something like

> INSERT ... RETURNING (NEW_a.value - NEW_b.value)?

They could use ALIAS to rename the trigger's NEW to something else.

regards, tom lane