Thread: 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 19073
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 <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
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
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
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
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
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