change in behaviour? Is this a regression in function?
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
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
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
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 19073Bruce 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
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.phpWe'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 19073Bruce 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
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 19073Bruce 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
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
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