Possible bug concerning LASTOID in 7.2

Hiya! I think i've found a bug in postgres 7.2.

I have tested this on 1 box, running redhat 7.2 w/ both pg7.1.2 and pg7.2.

I've attached a dumpfile that allows you to execute this query:

INSERT INTO forum (group_forum_id,posted_by,subject,body,date,is_followup_to,thread_id,most_recent_date) VALUES ('88760', '192470', 'asdfasdfsqdf', 'blah', '1014405669','0','625864','1014405669')

Here's the output of PG7.1.2:
test=# INSERT INTO forum (group_forum_id,posted_by,subject,body,date,is_followup_to,thread_id,most_recent_date) VALUES ('88760', '192470', 'asdfasdfsqdf', 'blah', '1014405669','0','625864','1014405669');
INSERT 80942390 1
test=# \echo :LASTOID
80942390

However, here's the output from PG7.2:

test=# INSERT INTO forum (group_forum_id,posted_by,subject,body,date,is_followup_to,thread_id,most_recent_date) VALUES ('88760', '192470', 'asdfasdfsqdf', 'blah', '1014405669','0','625864','1014405669')
test-# ;
UPDATE 0
test=# \echo :LASTOID
0

(Note: No OID, and actually returns the results from the UPDATE, and not
that of the INSERT!!!)

So, here's the problem:

My application looks for the OID of the record it just inserted, with PHP's
pg_getlastoid() function.

With postgres 7.1.2, it works great. However, with postgres 7.2, the function
returns 0 (because postgres returns 0!) - and screws up the application.

In the dumpfile, there are some rules generated:

CREATE RULE forum_insert_agg AS ON INSERT TO forum DO UPDATE forum_agg_msg_count SET count = (forum_agg_msg_count.count + 1) WHERE (forum_agg_msg_count.group_forum_id = new.group_forum_id);

CREATE RULE forum_delete_agg AS ON DELETE TO forum DO UPDATE forum_agg_msg_count SET count = (forum_agg_msg_count.count - 1) WHERE (forum_agg_msg_count.group_forum_id = old.group_forum_id);

These rules seem to trigger the behavior. it LOOKS like postgres is returning
the UPDATE procedure's results, rather than the INSERT results, which is what
i need it to return. (if the rules arent present, postgres returns the right
thing.)

Is this a bug?

(LASTOID is questionable here, because the rules in question really DID
generate an UPDATE sql, and run it... Therefore, the return, depending
on one's interpretation, could definitely be correct.)

Thanks for whatever help you might be able to provide.

FOOTNOTE: I'm not a dba. I'm just a sysadmin. So, if you need any further
information, please, do not hesitate to ask. (just dont know what to present
to start with.)

Thanks.

Chad Schwartz
CornerNet System Administration

Coax <coax(at)shell1(dot)cornernet(dot)com> writes:
> [ insert with an ON INSERT DO UPDATE ... rule ]

> Here's the output of PG7.1.2:
> test=# INSERT INTO forum (group_forum_id,posted_by,subject,body,date,is_followup_to,thread_id,most_recent_date) VALUES ('88760', '192470', 'asdfasdfsqdf', 'blah', '1014405669','0','625864','1014405669');
> INSERT 80942390 1
> test=# \echo :LASTOID
> 80942390

> However, here's the output from PG7.2:

> test=# INSERT INTO forum (group_forum_id,posted_by,subject,body,date,is_followup_to,thread_id,most_recent_date) VALUES ('88760', '192470', 'asdfasdfsqdf', 'blah', '1014405669','0','625864','1014405669')
> test-# ;
> UPDATE 0
> test=# \echo :LASTOID
> 0

I suspect the proximate cause of this is that we changed 7.2 so that
rule actions for an INSERT are done after the INSERT, not beforehand.
This is a good idea from most points of view, but it's annoying that
it's affecting the returned command tag. I tend to agree that that's
a bug, and that the tag ought to reflect the given command not any
queries generated by rules. However, it might be difficult to fix.

Fernando, does your command-tag patch help with this at all?

Another thing to think about: if the given query is suppressed entirely
by an ON INSERT DO INSTEAD rule, what tag should be returned?

regards, tom lane