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
--
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 909022468)
--
-- Name: forum_agg_msg_count Type: TABLE Owner: postgres
--
CREATE TABLE "forum_agg_msg_count" (
"group_forum_id" integer DEFAULT '0' NOT NULL,
"count" integer DEFAULT '0' NOT NULL,
Constraint "forum_agg_msg_count_pkey" Primary Key ("group_forum_id")
);
CREATE SEQUENCE forum_pk_seq;
--
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 909023141)
--
-- Name: forum_group_list Type: TABLE Owner: postgres
--
CREATE TABLE "forum_group_list" (
"group_forum_id" integer DEFAULT nextval('forum_group_list_pk_seq'::text) NOT
NULL,
"group_id" integer DEFAULT '0' NOT NULL,
"forum_name" text DEFAULT '' NOT NULL,
"is_public" integer DEFAULT '0' NOT NULL,
"description" text,
"allow_anonymous" integer DEFAULT '0' NOT NULL,
"send_all_posts_to" text,
Constraint "forum_group_list_pkey" Primary Key ("group_forum_id")
);
--
-- TOC Entry ID 3 (OID 909063891)
--
-- Name: "forum_group_list_group_id" Type: INDEX Owner: postgres
--
CREATE INDEX forum_group_list_group_id ON forum_group_list USING btree (group_id);
--
-- TOC Entry ID 4 (OID 909063892)
--
-- Name: "forum_group_list_forum_name" Type: INDEX Owner: postgres
--
CREATE INDEX forum_group_list_forum_name ON forum_group_list USING btree (forum_name);
--
-- Selected TOC Entries:
--
\connect - postgres
--
-- TOC Entry ID 2 (OID 909023147)
--
-- Name: forum Type: TABLE Owner: postgres
--
CREATE TABLE "forum" (
"msg_id" integer DEFAULT nextval('forum_pk_seq'::text) NOT NULL,
"group_forum_id" integer DEFAULT '0' NOT NULL,
"posted_by" integer DEFAULT '0' NOT NULL,
"subject" text DEFAULT '' NOT NULL,
"body" text DEFAULT '' NOT NULL,
"date" integer DEFAULT '0' NOT NULL,
"is_followup_to" integer DEFAULT '0' NOT NULL,
"thread_id" integer DEFAULT '0' NOT NULL,
"has_followups" integer DEFAULT '0',
"most_recent_date" integer DEFAULT '0' NOT NULL,
Constraint "forum_pkey" Primary Key ("msg_id")
);
--
-- TOC Entry ID 3 (OID 909023147)
--
-- Name: forum Type: ACL Owner:
--
REVOKE ALL on "forum" from PUBLIC;
GRANT ALL on "forum" to "postgres";
GRANT ALL on "forum" to "tperdue";
--
-- TOC Entry ID 4 (OID 909063893)
--
-- Name: "forum_threadid_index" Type: INDEX Owner: postgres
--
CREATE INDEX forum_threadid_index ON forum USING btree (thread_id);
--
-- TOC Entry ID 5 (OID 909063894)
--
-- Name: "forum_group_forum_id" Type: INDEX Owner: postgres
--
CREATE INDEX forum_group_forum_id ON forum USING btree (group_forum_id);
--
-- TOC Entry ID 6 (OID 909063895)
--
-- Name: "forum_mostrecentdate_index" Type: INDEX Owner: postgres
--
CREATE INDEX forum_mostrecentdate_index ON forum USING btree (most_recent_date);
--
-- TOC Entry ID 7 (OID 909063896)
--
-- Name: "forum_forumid_isfollto_mostrece" Type: INDEX Owner: postgres
--
CREATE INDEX forum_forumid_isfollto_mostrece ON forum USING btree (group_forum_id,
is_followup_to, most_recent_date);
--
-- TOC Entry ID 8 (OID 909063897)
--
-- Name: "forum_threadid_isfollowupto" Type: INDEX Owner: postgres
--
CREATE INDEX forum_threadid_isfollowupto ON forum USING btree (thread_id,
is_followup_to);
--
-- TOC Entry ID 9 (OID 909063898)
--
-- Name: "forum_forumid_threadid_mostrece" Type: INDEX Owner: postgres
--
CREATE INDEX forum_forumid_threadid_mostrece ON forum USING btree (group_forum_id,
thread_id, most_recent_date);
--
-- TOC Entry ID 10 (OID 909063899)
--
-- Name: "forum_forumid_isfollowupto" Type: INDEX Owner: postgres
--
CREATE INDEX forum_forumid_isfollowupto ON forum USING btree (group_forum_id,
is_followup_to);
--
-- TOC Entry ID 11 (OID 909063900)
--
-- Name: "forum_forumid_msgid" Type: INDEX Owner: postgres
--
CREATE INDEX forum_forumid_msgid ON forum USING btree (group_forum_id, msg_id);
--
-- TOC Entry ID 33 (OID 909064186)
--
-- Name: forum_insert_agg Type: RULE Owner: postgres
--
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);
--
-- TOC Entry ID 34 (OID 909064187)
--
-- Name: forum_delete_agg Type: RULE Owner: postgres
--
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);
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
