Thread: CREATE TABLE/AS does not allow WITH OIDS?

... and the same for SELECT/INTO Also, the hasoids (or equivalent) 
attribute is not picked up from the source table which would be another 
way to push this property into the target table.

One or both of these mechanisms (I'd think both should be available) 
would seem to be required to make these constructs useful in 
environments requiring OIDs.

This seems to be a trivial but serious omission for this release. 
Comments and suggestions?
                  - Thomas
From a syntax PoV, CREATE TABLE name WITH OIDS AS ... works. Haven't 
worked out how to do the same for SELECT/INTO.

Thomas Lockhart <lockhart@fourpalms.org> writes:
> ... and the same for SELECT/INTO Also, the hasoids (or equivalent)
> attribute is not picked up from the source table which would be
> another way to push this property into the target table.

How would we make use of 'hasoids' in the case of multiple source
tables, or a source table defined by an SRF?

Unless we can define some consistent semantics for when we use the
hasoids of the source table(s), I'd say we should just add the
WITH/WITHOUT OIDS syntax.

Cheers,

Neil

-- 
Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC

> How would we make use of 'hasoids' in the case of multiple source
> tables, or a source table defined by an SRF?

Choose a convention. At the moment, the first source table provides more 
characteristics to the target than do the other sources, and that could 
be true for OIDs also. I would be inclined to have the logic be that if 
*any* source table has OIDs, then the target gets them. Unless it was 
explicitly specified using the WITH/WITHOUT syntax of course.

> Unless we can define some consistent semantics for when we use the
> hasoids of the source table(s), I'd say we should just add the
> WITH/WITHOUT OIDS syntax.

Right. There is no problem coming up with consistant semantics, though 
there may be a problem getting a consensus on what those should be.

In the meantime we have broken compatibility with the previous release 
(since targets are created without OIDs) and should think about fixing that.
               - Thomas

Neil Conway <neilc@samurai.com> writes:
> Unless we can define some consistent semantics for when we use the
> hasoids of the source table(s), I'd say we should just add the
> WITH/WITHOUT OIDS syntax.

I agree with Neil: in many situations it's not reasonable to try to
associate a unique "source table" with a CREATE AS or SELECT INTO, and
therefore automatically propagating hasoids is doomed to failure.

A WITH/WITHOUT OIDS option on CREATE TABLE AS might be reasonable.

There are implementation gotchas to worry about.  The reason the
behavior changed in 7.3 is that with the new OIDS-are-optional tuple
header layout, the top level plan node's tupdesc *must* have the correct
hasoids setting, and it turned out to be too difficult to back-patch
this decision into an already-built plan tree.  It's hard even to get it
to work for INSERT/UPDATE (see the notes in ExecAssignResultTypeFromTL),
and SELECT INTO is such a wart on the side of the system that that
solution doesn't work for it (see the notes in InitPlan).  This area
could use some rethinking in 7.4 (too late to mess with it for 7.3
though).

Someday it would be worth restructuring CREATE AS/SELECT INTO so that
they internally look like INSERT ... SELECT rather than a warty version
of SELECT.  (But INSERT ... SELECT is pretty warty itself; someday we
need to fix the querytree structure so that there's not a forced
one-to-one relationship between targetlists and rangetables.  INSERT
... SELECT would really prefer to have two tlists and only one rtable.)
        regards, tom lane

> I agree with Neil: in many situations it's not reasonable to try to
> associate a unique "source table" with a CREATE AS or SELECT INTO, and
> therefore automatically propagating hasoids is doomed to failure.

Well, it certainly is reasonable to have better behavior than currently. 
We do something now, which is at odds with the previous convention 
(which also did something).

> A WITH/WITHOUT OIDS option on CREATE TABLE AS might be reasonable.

Of course.

> There are implementation gotchas to worry about.  The reason the
> behavior changed in 7.3 is that with the new OIDS-are-optional tuple
> header layout, the top level plan node's tupdesc *must* have the correct
> hasoids setting, and it turned out to be too difficult to back-patch
> this decision into an already-built plan tree.  It's hard even to get it
> to work for INSERT/UPDATE (see the notes in ExecAssignResultTypeFromTL),
> and SELECT INTO is such a wart on the side of the system that that
> solution doesn't work for it (see the notes in InitPlan).  This area
> could use some rethinking in 7.4 (too late to mess with it for 7.3
> though).

It is unfortunate that the optional-OIDs has this drawback; it perhaps 
should have been more explicit in the initial discussions. But I'm 
hearing about difficulties, not impossibilities, so that is encouraging ;)

What would be required to have OIDs for all SELECT/INTO product tables 
for this release? That could fit into 7.3 and not break applications; 
the current convention for this (that is, dropping OID characteristics) 
might be characterized as an omission, not a choice.
                  - Thomas

Thomas Lockhart <lockhart@fourpalms.org> writes:
> What would be required to have OIDs for all SELECT/INTO product tables 
> for this release?

It *might* work to just reverse the default assumption in
ExecAssignResultTypeFromTL().  But I will vote against making such a
change at this late hour.  Quite possibly we'd introduce bugs in
features that are much more critical than whether a table created by
SELECT INTO has OIDs or not.
        regards, tom lane

Tom Lane wrote:
> Thomas Lockhart <lockhart@fourpalms.org> writes:
> > What would be required to have OIDs for all SELECT/INTO product tables 
> > for this release?
> 
> It *might* work to just reverse the default assumption in
> ExecAssignResultTypeFromTL().  But I will vote against making such a
> change at this late hour.  Quite possibly we'd introduce bugs in
> features that are much more critical than whether a table created by
> SELECT INTO has OIDs or not.

With no one concerned about this except Thomas and myself, added to
TODO:
* Fix SELECT ... INTO and CREATE TABLE AS to have appopriate 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,
Pennsylvania19073