Обсуждение: Bug #857: [7.3] Attribute oid not found in a temporary table creted by a SELECT

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

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

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

Wrong ! The workaround works in 7.3 but is not backward compatible
with prior 7.x.y version.

I began reverting to 7.2.3 :( Anyway, I will survive ;)

On Thu, Dec 26, 2002 at 09:02:08AM +0100, JMM Moi-Meme Maitre du Monde wrote:
> 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?
> >

Sorry.  Interesting the workaround doesn't work in 7.2.X.  Hopefully we
will get this worked out in 7.4.

---------------------------------------------------------------------------

JMM Moi-Meme Maitre du Monde wrote:
> Wrong ! The workaround works in 7.3 but is not backward compatible
> with prior 7.x.y version.
>
> I began reverting to 7.2.3 :( Anyway, I will survive ;)
>
> On Thu, Dec 26, 2002 at 09:02:08AM +0100, JMM Moi-Meme Maitre du Monde wrote:
> > 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?
> > >
>

--
  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