Re: Possible bug from 6.3.2t

> Hi,
>
> I am having problems with the sub-select in PostgreSQL. As I have not
> tried to use these before, I am not sure if the problem is with me, SQL
> or PostgreSQL.
>
> I have two tables:
>
> CREATE TABLE tab1 (
> locus char16,
> shortname char8
> -- lots of other data ...
> );
>
> -- This table flags some loci in tab1 as having errors.
> CREATE TABLE tab2 (
> id oid, -- foreign key into TABLE tab1
> errno int4 -- An error indicator
> );
>
> If I say:
> SELECT * FROM tab1 WHERE shortname='AciSPP';
> I retrieve 36 rows out of ~100,000 in seconds. This is okay.
>
> If I say:
> SELECT id, locus, errno
> FROM tab1, tab2
> WHERE tab1.oid=tab2.id
> AND shortname='AciSPP';
>
> I retrieve 22 rows out of the 100,000 in tab1 and 80,000 in tab2 in much
> less than a minute.
>
> Now, assume I wish to remove all traces of 'AciSPP' from my database.
> I need to remove those from within TABLE tab2 first. I tried this:
> DELETE FROM tab2
> WHERE id in (SELECT oid FROM tab1 WHERE shortname='AciSPP');

Mark, there is a bug in 6.3.2 where you can not us oid in a subselect.
This is fixed in 6.4, and beta starts on September 1, or on Friday, we
are not sure. Feel free to get the current snapshot from
ftp.postgresql.org and try it out.

I know this particular bug was reported, with the same behaviour you
describe, and a fix made.

--
Bruce Momjian | 830 Blythe Avenue
maillist(at)candle(dot)pha(dot)pa(dot)us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)