Thread: change in behaviour? Is this a regression in function?

change in behaviour? Is this a regression in function?

From
Gavin Atkinson
Date:
Hi all,

Sorry if this is the wrong mailing list, I couldn't see a "help" type
list.

Under Postgresql 7.1.3, I could use "create table foo as select * from
bar" and the table would be created with OIDs.

Under Postgres 7.3.1, this no longer seems to be the case:

> test=> select oid from bar;
>   oid
> --------
>  17168
>  17175
> (2 rows)
>
> test=> create table foo as select * from bar;
> SELECT
> test=> select oid from foo;
> ERROR:  Attribute "oid" not found

Compare this with version 7.1.3, on a different box:

> test=> select oid from bar;
>   oid
> --------
>  102406
>  102526
> (2 rows)
>
> test=> create table foo as select * from bar;
> SELECT
> test=> select oid from foo;
>   oid
> --------
>  104307
>  104308
> (2 rows)

Is this a configuration problem at my end (I can't see anything wrong, but
the two versions are on different machines) or is it a problem with
version 7.3.1. Or was I simply relying on an undocumented feature? If the
latter, what is the best way of getting round this? Both versions are
running on FreeBSD, and were compiled from source, if it helps. They are
using the standard config files, but have the pg_hba access permissions
locked down to one specific IP address.

Thanks,

Gavin

Re: change in behaviour? Is this a regression in function?

From
Tom Lane
Date:
Gavin Atkinson <gavin.atkinson@ury.york.ac.uk> writes:
> Under Postgresql 7.1.3, I could use "create table foo as select * from
> bar" and the table would be created with OIDs.

7.3 builds it without OIDs.  Sorry about that.

            regards, tom lane

Re: change in behaviour? Is this a regression in function?

From
Gavin Atkinson
Date:
On Fri, 17 Jan 2003, Tom Lane wrote:

> Gavin Atkinson <gavin.atkinson@ury.york.ac.uk> writes:
> > Under Postgresql 7.1.3, I could use "create table foo as select * from
> > bar" and the table would be created with OIDs.
>
> 7.3 builds it without OIDs.  Sorry about that.

Was this an intentional change or will it be fixed in 7.4? Do you know
which version was first affected by this change? Is there any way around
it? I'm trying to update postgres but I can't at the moment as the app
makes extensive use of the oids after a "select table as".

Gavin

Re: change in behaviour? Is this a regression in function?

From
Bruce Momjian
Date:
Gavin Atkinson wrote:
> On Fri, 17 Jan 2003, Tom Lane wrote:
>
> > Gavin Atkinson <gavin.atkinson@ury.york.ac.uk> writes:
> > > Under Postgresql 7.1.3, I could use "create table foo as select * from
> > > bar" and the table would be created with OIDs.
> >
> > 7.3 builds it without OIDs.  Sorry about that.
>
> Was this an intentional change or will it be fixed in 7.4? Do you know
> which version was first affected by this change? Is there any way around
> it? I'm trying to update postgres but I can't at the moment as the app
> makes extensive use of the oids after a "select table as".

It was an accident and will be fixed in 7.4.  I don't think the fix is
in CVS yet.  On workaround is to explicitly select the oid column as
part of the SELECT.

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

Re: change in behaviour? Is this a regression in function?

From
Gavin Atkinson
Date:
On Sat, 18 Jan 2003, Tom Lane wrote:

> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> So I guess the question at this point is do we want to flip-flop the
> >> behavior yet again, or leave well enough alone?
>
> > I don't have a problem with making no oids the default.  I just think
> > CREATE TABLE and CREATE TABLE AS should behave similarly in terms of
> > oids.
>
> Well, that is a good point.  If we are going to change it back, I'd vote
> for back-patching the change into 7.3.2 so as to minimize the exposure
> of the current behavior.
>
> Any other opinions out there?

I'm in favour of this, as it came as quite a suprise when my application
stopped working and I spent quite a bit of time debugging it (I
originally thought it was a configuration option difference). I like the
idea of having WITH and WITHOUT OIDS options to CREATE TABLE AS as well -
there have been quite a few times when I have not needed them. But, like
CREATE TABLE, i think oids should be created by default.

Gavin

Re: change in behaviour? Is this a regression in function?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> So I guess the question at this point is do we want to flip-flop the
>> behavior yet again, or leave well enough alone?

> I don't have a problem with making no oids the default.  I just think
> CREATE TABLE and CREATE TABLE AS should behave similarly in terms of
> oids.

Well, that is a good point.  If we are going to change it back, I'd vote
for back-patching the change into 7.3.2 so as to minimize the exposure
of the current behavior.

Any other opinions out there?

            regards, tom lane

Re: change in behaviour? Is this a regression in function?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I find it uneven for CREATE TABLE AS not to have oids, while CREATE
> > TABLE does.  Was the problem that we had multiple tables in the query,
> > and some may have oids and some not, and we didn't know if we should
> > create an oid column?
>
> No, the reason for the change in behavior was strictly an implementation
> problem.  Because of the new OIDs-are-optional tuple header layout, the
> plan tree has to be built with an awareness of whether it's supposed to
> produce tuples with or without space for an OID.
>
> Now that I look at it again, there's a relatively simple solution after
> all: we could add a field to EState indicating that we're doing a SELECT
> INTO, which InitPlan could set before starting the plan tree
> initialization.  Then ExecAssignResultTypeFromTL could look at that to
> help it decide what to do.  (Annoying how the solution is only obvious
> months after the fact ...)
>
> So I guess the question at this point is do we want to flip-flop the
> behavior yet again, or leave well enough alone?  I don't think that the
> fact that the behavior changed is sufficient reason to label the new
> behavior a bug; there are people who like it this way (see original
> discussion back in September).  See also recent discussions about
> changing the default behavior of CREATE TABLE to not include OIDs.

I don't have a problem with making no oids the default.  I just think
CREATE TABLE and CREATE TABLE AS should behave similarly in terms of
oids.

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

Re: change in behaviour? Is this a regression in function?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I find it uneven for CREATE TABLE AS not to have oids, while CREATE
> TABLE does.  Was the problem that we had multiple tables in the query,
> and some may have oids and some not, and we didn't know if we should
> create an oid column?

No, the reason for the change in behavior was strictly an implementation
problem.  Because of the new OIDs-are-optional tuple header layout, the
plan tree has to be built with an awareness of whether it's supposed to
produce tuples with or without space for an OID.

Now that I look at it again, there's a relatively simple solution after
all: we could add a field to EState indicating that we're doing a SELECT
INTO, which InitPlan could set before starting the plan tree
initialization.  Then ExecAssignResultTypeFromTL could look at that to
help it decide what to do.  (Annoying how the solution is only obvious
months after the fact ...)

So I guess the question at this point is do we want to flip-flop the
behavior yet again, or leave well enough alone?  I don't think that the
fact that the behavior changed is sufficient reason to label the new
behavior a bug; there are people who like it this way (see original
discussion back in September).  See also recent discussions about
changing the default behavior of CREATE TABLE to not include OIDs.

            regards, tom lane

Re: change in behaviour? Is this a regression in function?

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Gavin Atkinson wrote:
> >> Was this an intentional change or will it be fixed in 7.4?
>
> > It was an accident and will be fixed in 7.4.
>
> No, it was deliberate --- the difficulty of preserving the old behavior
> seemed too high, and there were no complaints about it at the time.
> http://archives.postgresql.org/pgsql-hackers/2002-09/msg00031.php
>
> We've gotten a very small number of complaints subsequently, but AFAICT
> most people either haven't noticed or like the space savings.
>
> There has been some subsequent discussion of adding a WITH/WITHOUT OIDS
> option to CREATE TABLE AS, eg
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00336.php
> http://archives.postgresql.org/pgsql-hackers/2002-11/msg00357.php
> but AFAICT the amount of effort needed is well out of proportion to the
> value of the feature.  You can always do a plain CREATE TABLE and then
> fill the table with INSERT/SELECT, if you need to have OIDs.

I find it uneven for CREATE TABLE AS not to have oids, while CREATE
TABLE does.  Was the problem that we had multiple tables in the query,
and some may have oids and some not, and we didn't know if we should
create an oid column?

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

Re: change in behaviour? Is this a regression in function?

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Gavin Atkinson wrote:
>> Was this an intentional change or will it be fixed in 7.4?

> It was an accident and will be fixed in 7.4.

No, it was deliberate --- the difficulty of preserving the old behavior
seemed too high, and there were no complaints about it at the time.
http://archives.postgresql.org/pgsql-hackers/2002-09/msg00031.php

We've gotten a very small number of complaints subsequently, but AFAICT
most people either haven't noticed or like the space savings.

There has been some subsequent discussion of adding a WITH/WITHOUT OIDS
option to CREATE TABLE AS, eg
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00336.php
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00357.php
but AFAICT the amount of effort needed is well out of proportion to the
value of the feature.  You can always do a plain CREATE TABLE and then
fill the table with INSERT/SELECT, if you need to have OIDs.

            regards, tom lane

Re: change in behaviour? Is this a regression in function?

From
Bruce Momjian
Date:
This will be fixed in 7.3.2.

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

Gavin Atkinson wrote:
> On Sat, 18 Jan 2003, Tom Lane wrote:
>
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > Tom Lane wrote:
> > >> So I guess the question at this point is do we want to flip-flop the
> > >> behavior yet again, or leave well enough alone?
> >
> > > I don't have a problem with making no oids the default.  I just think
> > > CREATE TABLE and CREATE TABLE AS should behave similarly in terms of
> > > oids.
> >
> > Well, that is a good point.  If we are going to change it back, I'd vote
> > for back-patching the change into 7.3.2 so as to minimize the exposure
> > of the current behavior.
> >
> > Any other opinions out there?
>
> I'm in favour of this, as it came as quite a suprise when my application
> stopped working and I spent quite a bit of time debugging it (I
> originally thought it was a configuration option difference). I like the
> idea of having WITH and WITHOUT OIDS options to CREATE TABLE AS as well -
> there have been quite a few times when I have not needed them. But, like
> CREATE TABLE, i think oids should be created by default.
>
> Gavin
>

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