Thread

1. Re: postgresql rule bug

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-04-11T23:14:51Z

   Coranth Gryphon <gryphon@healer.com> writes:
   > Hi. From the contributors list, you seemed to be the most likely
   > people to send a postgresql bug report.
   
   There is a bugs list, which is the appropriate place.
   
   > When using a RULE that triggers off an INSERT on a table whose
   > primary key is a SERIAL, every time the rule accesses the new
   > serial id in the body of the rule, the serial number increments.
   
   Wouldn't surprise me.  A rule is basically like a macro --- it's
   expanded with the original inputs substituted in each appropriate place.
   This is either a bug or a feature depending on what sort of example you
   are looking at ...
   
   In this particular case it does seem rather annoying, since there's
   no obvious way to get the behavior you're after.  I dunno if we
   can do anything about it in the near future though.  Jan, this is
   your turf --- what do you think?
   
   			regards, tom lane
   
   
   > Here is an example of the problem. 
   
   > CREATE TABLE T_Comments
   > (
   >   id_note	SERIAL,
   >   txtnote	VARCHAR(80)	NOT NULL,
   >   PRIMARY KEY (id_note)
   > );
   
   > CREATE TABLE T_CommentHistory
   > (
   >   id_note	INT4		NOT NULL,	/* FK to Comments */
   >   dt_act	ABSTIME		NOT NULL,
   >   action	CHAR(6)		NOT NULL
   >   PRIMARY KEY (id_note, dt_act)
   > );
   
   > CREATE RULE T_CommentCreation
   >   AS ON INSERT TO T_Comments
   >   DO INSERT INTO T_CommentHistory
   >      VALUES (new.id_note, CURRENT_TIMESTAMP, 'create')
   > ;
   
   > INSERT INTO T_Comments (txtnote) VALUES ('This is a test')
   > ;
   
   > SELECT * FROM T_Comments;
   
   >   id_note | txtnote       
   >   --------+--------------
   >         2 | This is a test
   
   > SELECT * FROM T_CommentHistory;
   
   >   id_note | dt_act                       | actions
   >   --------+------------------------------+-------
   >         1 | Tue Apr 11 11:40:47 2000 GMT | create 
   
   > Or am I doing something wrong in the rule?
   
   > -coranth
   
   > PS> Thanx in advance for your time and for helping
   > to create such a great piece of software.
   
   

2. Re: Re: postgresql rule bug

   Mark Hollomon <mhh@nortelnetworks.com> — 2000-04-12T07:45:48Z

   Tom Lane wrote:
   > 
   > Coranth Gryphon <gryphon@healer.com> writes:
   > > CREATE RULE T_CommentCreation
   > >   AS ON INSERT TO T_Comments
   > >   DO INSERT INTO T_CommentHistory
   > >      VALUES (new.id_note, CURRENT_TIMESTAMP, 'create')
   
   Untested suggestion -
   
   Try using currval('T_Comments_id_note_seq') instead.
   
   -- 
   
   Mark Hollomon
   mhh@nortelnetworks.com
   ESN 451-9008 (302)454-9008
   
   

3. Re: postgresql rule bug

   Jan Wieck <wieck@debis.com> — 2000-04-12T12:12:21Z

   Tom Lane wrote:
   
   > Coranth Gryphon <gryphon@healer.com> writes:
   > > Hi. From the contributors list, you seemed to be the most likely
   > > people to send a postgresql bug report.
   >
   > There is a bugs list, which is the appropriate place.
   
       It's not a bug, it's a feature :-)
   
   > > When using a RULE that triggers off an INSERT on a table whose
   > > primary key is a SERIAL, every time the rule accesses the new
   > > serial id in the body of the rule, the serial number increments.
   >
   > Wouldn't surprise me.  A rule is basically like a macro --- it's
   > expanded with the original inputs substituted in each appropriate place.
   > This is either a bug or a feature depending on what sort of example you
   > are looking at ...
   >
   > In this particular case it does seem rather annoying, since there's
   > no obvious way to get the behavior you're after.  I dunno if we
   > can do anything about it in the near future though.
   
       Absolutely  right,  and I don't think we should/could fix it.
       Only a trigger will do it correctly in this case.
   
       The rule expands to a separate query, which is ran BEFORE the
       one  sent  to the backend. First of all, there's no guarantee
       that this additional query will produce the  same  number  of
       result rows (inserts) as the original one.
   
       Consider  the  case that someone does an INSERT ... SELECT to
       create new T_Comments.  Further take into  account  that  the
       rule  could  have  some  qualification  and  only some of the
       comments cause  additional  information  in  T_CommentHistory
       (where txtnote != '').
   
       Now  at  the time the INSERT for the history is executed, the
       system doesn't even know HOW MANY  rows  the  original  query
       will  produce.  How  should  it  preallocate  the serials and
       remember which ones to use when running the original query?
   
       Forget it and go for a trigger.
   
   
   Jan
   
   --
   
   #======================================================================#
   # It's easier to get forgiveness for being wrong than for being right. #
   # Let's break this rule - forgive me.                                  #
   #========================================= wieck@debis.com (Jan Wieck) #