Thread: 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@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
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
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
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
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
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
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
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
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
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