Re: [HACKERS] Pretty bad bug in Postgres.
Any comments on this?
Following is I believe evidence of a pretty bad bug in postgres. This is
the 990329 snapshot.When doing an insert into a table where one of the fields comes from a
SELECT from another table, it seems that if the table has the "*" after
it, to indicate sub-classes it doesn't work.The database is fresh and there are no indexes.
The table looks like this....
CREATE TABLE category (
name text,
image text,
url text,
parent oid
);httpd=> select * from category;
name |image|url|parent
--------+-----+---+------
foo |foo | | 0
bar |bar | |158321
Products|.gif | |
(3 rows)httpd=> select oid, * FROM category* where name = 'foo';
oid|name|image|url|parent
------+----+-----+---+------
158321|foo |foo | | 0
(1 row)httpd=> insert into category(name, image, parent) SELECT 'boo', 'boo',
oid FROM category* where name = 'foo';
INSERT 158370 1
httpd=> select * from category;
name |image|url|parent
--------+-----+---+------
foo |foo | | 0
bar |bar | |158321
Products|.gif | |
(3 rows)Ok, what's going on here. The 'boo' record did not appear!
httpd=> insert into category(name, image, parent) SELECT 'boo', 'boo',
oid FROM category where name = 'foo';
INSERT 158374 1
httpd=> select * from category;
name |image|url|parent
--------+-----+---+------
foo |foo | | 0
bar |bar | |158321
Products|.gif | |
boo |boo | |158321
(4 rows)We dropped the "*" from the FROM clause and now the record does appear.
A bug?
--
Chris Bitmead
http://www.bigfoot.com/~chris.bitmead
mailto:chris.bitmead@bigfoot.com
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Import Notes
Reply to msg id not found: 3717DE64.991E67C8@bigfoot.com
Here is Tom's comment on the issue.
Chris Bitmead <chris.bitmead@bigfoot.com> writes:
Following is I believe evidence of a pretty bad bug in postgres. This is
the 990329 snapshot.httpd=> insert into category(name, image, parent) SELECT 'boo', 'boo',
oid FROM category* where name = 'foo';
INSERT 158370 1
httpd=> select * from category;
name |image|url|parent
--------+-----+---+------
foo |foo | | 0
bar |bar | |158321
Products|.gif | |
(3 rows)Ok, what's going on here. The 'boo' record did not appear!
Interesting. You'll notice the INSERT response claims that a tuple was
inserted, and even gives you the OID for it. Wonder where it went?Anyway, that sure suggests that the SELECT part worked, and the problem
is that the new tuple got dropped on the floor later. (Or could it have
been inserted into another table? Are there other tables that category*
includes?)I'm guessing this has something to do with Vadim's recent work, but
I don't pretend to know what's wrong...regards, tom lane
--
Bruce Momjian | http://www.op.net/~candle
maillist@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026Import Notes
Reply to msg id not found: 23695.924364850@sss.pgh.pa.us | Resolved by subject fallback
Bruce Momjian wrote:
Any comments on this?
Following is I believe evidence of a pretty bad bug in postgres. This is
the 990329 snapshot.
^^^^^^
Snapshot's too old?
httpd=> insert into category(name, image, parent) SELECT 'boo', 'boo',
oid FROM category* where name = 'foo';
INSERT 158370 1
httpd=> select * from category;
name |image|url|parent
--------+-----+---+------
foo |foo | | 0
bar |bar | |158321
Products|.gif | |
(3 rows)Ok, what's going on here. The 'boo' record did not appear!
I can't reproduce this in current...
Vadim