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

Why do OLD and NEW have special internal names?

From
Tom Lane
Date:
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


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

From
"Kevin Grittner"
Date:
Tom Lane <tgl@sss.pgh.pa.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


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

From
Robert Haas
Date:
On Thu, Nov 5, 2009 at 4:33 PM, Tom Lane <tgl@sss.pgh.pa.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


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

From
Tom Lane
Date:
Robert Haas <robertmhaas@gmail.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


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

From
Roberto Mello
Date:
On Thu, Nov 5, 2009 at 5:33 PM, Tom Lane <tgl@sss.pgh.pa.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


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

From
Robert Haas
Date:
On Fri, Nov 6, 2009 at 1:02 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.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


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

From
David Fetter
Date:
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@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.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


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

From
Josh Berkus
Date:
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


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

From
Jan Wieck
Date:
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



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

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.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


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

From
Jan Wieck
Date:
On 11/20/2009 1:12 AM, Tom Lane wrote:
> Jan Wieck <JanWieck@Yahoo.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



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

From
Tom Lane
Date:
Jan Wieck <JanWieck@Yahoo.com> writes:
> On 11/20/2009 1:12 AM, Tom Lane wrote:
>> Jan Wieck <JanWieck@Yahoo.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