Thread

1. Why do OLD and NEW have special internal names?

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-05T21:33:07Z

   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
   
   

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

   Kevin Grittner <kevin.grittner@wicourts.gov> — 2009-11-05T22:10:51Z

   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
   
   

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

   Robert Haas <robertmhaas@gmail.com> — 2009-11-06T01:03:25Z

   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
   
   

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

   Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-06T06:02:50Z

   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
   
   

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

   Roberto Mello <roberto.mello@gmail.com> — 2009-11-06T12:06:12Z

   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
   
   

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

   Robert Haas <robertmhaas@gmail.com> — 2009-11-06T12:22:55Z

   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
   
   

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

   David Fetter <david@fetter.org> — 2009-11-07T07:11:22Z

   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
   
   

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

   Josh Berkus <josh@agliodbs.com> — 2009-11-08T17:32:44Z

   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
   
   

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

   Jan Wieck <janwieck@yahoo.com> — 2009-11-20T03:35:23Z

   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
   
   
   

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

    Jan Wieck <janwieck@yahoo.com> — 2009-11-20T05:30:54Z

    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
    
    
    

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-20T06:12:38Z

    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
    
    

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

    Tom Lane <tgl@sss.pgh.pa.us> — 2009-11-20T21:39:17Z

    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