Re: Bug #857: [7.3] Attribute oid not found in a temporary table - Mailing list pgsql-bugs

Yes, it works. Thanks.

I will just have to alter some 50 scripts, but that's better than
having to revert my DBs (~18 GB) to 7.2.3 while waiting for 7.4.

On Wed, Dec 25, 2002 at 10:58:41PM -0500, Bruce Momjian wrote:
>
> This is a known problem with 7.3.X.  It doesn't create an oid column as
> part of SELECT INTO ....
>
> We have a TODO item:
>
>     * Fix SELECT ... INTO and CREATE TABLE AS to have appopriate OID column
>
> so this will be fixed, hopefully in 7.4.  We couldn't fix it in 7.3.X
> without causing more serious problems.  If you specify the oid column as
> part of the SELECT INTO, it will take the oid from the original table
> and put it in your new table.  Does that help you?
>
> ---------------------------------------------------------------------------
>
> pgsql-bugs@postgresql.org wrote:
> > jmm (j6m@cvni.net) reports a bug with a severity of 2
> > The lower the number the more severe it is.
> >
> > Short Description
> > [7.3] Attribute oid not found in a temporary table creted by a SELECT
> >
> > Long Description
> > I ran on a problem today while running a SQL script that used
> > to perfectly run from 7.0.2 to 7.2.3.
> >
> > (I dont know if it is exactly a bug or an intended design change as
> > searchable online 7.3 docs are still not yet available. I browsed
> > through the locally installed without success).
> >
> > In this SQL script, I create a temporary table with two columns from
> > another table where I poured all data collected from various sources.
> > My aim is to elmiminate doublons (id is a unique identifier in the
> > finally resulting table). Therefore I take the max oid for each
> > distinct id from the original table and do deletion for all rows
> > with same id and oids lower than max oid. I do this on a temporary
> > table processing it by chunks of 1000 ids for processing time reasons.
> >
> > It is quite simplistic way of doing it, but it used to work and does
> > not any longer with 7.3. Temporary tables do have select table oids
> > when explicitely created insted of using SELECT INTO.
> >
> > Sample Code
> > toto=# create table toto (f1 integer, f2 integer) ;
> > CREATE TABLE
> > toto=# insert into toto values (1,2);
> > INSERT 69204710 1
> > toto=# insert into toto values (3,4);
> > INSERT 69204711 1
> > toto=# select * into temp table titi from toto ;
> > SELECT
> > toto=# select oid from titi ;
> > ERROR:  Attribute "oid" not found
> >
> > toto=# create temp table titi (f1 integer, f2 integer) ;
> > ERROR:  Relation 'titi' already exists
> > toto=# create temp table tutu (f1 integer, f2 integer) ;
> > CREATE TABLE
> > toto=# insert into tutu select * from toto ;
> > INSERT 0 2
> > toto=# select oid from tutu ;
> >    oid
> > ----------
> >  69204716
> >  69204717
> > (2 rows)
> >
> >
> > No file was uploaded with this report
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
> --
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org