Thread: OIDs (Or: another RTFM question?)

OIDs (Or: another RTFM question?)

From
Adrian 'Dagurashibanipal' von Bidder
Date:
Yo!

Browsing through the online docs, I see the special columns documented
in http://www.postgresql.org/idocs/index.php?sql-syntax-columns.html .

But I'm missing discussion about significance of the oids - something
along the lines of 'Creating tables WITHOUT OIDS does *not* safe any
disk space, but does <give you what?> at the expense of <what? being not
backward compatible, I guess>.'

Such documentation seems necessary to me, since pg creates tables with
oids per default (does it still?), but intuitively I feel this somewhat
silly - as oids are obviously optional, why have them built in?

(I'm sure these issues have been beaten to death when the WITHOUT OIDS
feature was introduced, but I couldn't find the relevant discussion in
the archives. Pointers welcome.)

cheers
-- vbi

--
secure email with gpg                         http://fortytwo.ch/gpg

Attachment

Re: OIDs (Or: another RTFM question?)

From
Bruce Momjian
Date:
Adrian 'Dagurashibanipal' von Bidder wrote:

Checking application/pgp-signature: FAILURE
-- Start of PGP signed section.
> Yo!
>
> Browsing through the online docs, I see the special columns documented
> in http://www.postgresql.org/idocs/index.php?sql-syntax-columns.html .
>
> But I'm missing discussion about significance of the oids - something
> along the lines of 'Creating tables WITHOUT OIDS does *not* safe any
> disk space, but does <give you what?> at the expense of <what? being not
> backward compatible, I guess>.'

Yes, WITHOUT OIDs just prevents the oid counter from incrementing as
quickly and perhaps rolling over.  In 7.3 I think WITHOUT OID may save
storage so I am going to avoid documenting it at this point.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: OIDs (Or: another RTFM question?)

From
Tom Lane
Date:
"Adrian 'Dagurashibanipal' von Bidder" <avbidder@fortytwo.ch> writes:
> But I'm missing discussion about significance of the oids - something
> along the lines of 'Creating tables WITHOUT OIDS does *not* safe any
> disk space, but does <give you what?> at the expense of <what? being not
> backward compatible, I guess>.'

Basically what WITHOUT OIDS does for you is to reduce consumption of
OIDs, thereby postponing wraparound of the 32-bit OID counter.  While
the system itself isn't fazed by such a wraparound, user programs that
look at OIDs might be.  Also, you might get some transient command
failures due to duplicated OIDs --- eg, a CREATE TABLE might fail if the
generated OID for the table matches one already in pg_class.  (If so,
you can just keep trying till you get a non-duplicate OID, but the
annoyance factor could be considerable ... especially if the CREATE is
issued by an application that's not prepared for it to fail.)

In the long run there is talk of assigning OIDs per-table instead of
globally, so that consumption of OIDs in user tables wouldn't cause
problems for any other table.

Also, there is work being done to make WITHOUT OIDS actually save space
in row headers; that may happen for 7.3.

            regards, tom lane

Re: OIDs (Or: another RTFM question?)

From
Joel Rees
Date:
Tom Lane explained:

> Basically what WITHOUT OIDS does for you is to reduce consumption of
> OIDs, thereby postponing wraparound of the 32-bit OID counter.  While
> the system itself isn't fazed by such a wraparound, user programs that
> look at OIDs might be.

How much of a pain would it be to make that a 64-bit counter? Would that
create conflicts with the SQL standard?

(No, I don't contribute code, so if that's a really stupid idea, just tell
me so.)

--
Joel Rees <joel@alpsgiken.gr.jp>


Re: OIDs (Or: another RTFM question?)

From
Bruce Momjian
Date:
Joel Rees wrote:
> Tom Lane explained:
>
> > Basically what WITHOUT OIDS does for you is to reduce consumption of
> > OIDs, thereby postponing wraparound of the 32-bit OID counter.  While
> > the system itself isn't fazed by such a wraparound, user programs that
> > look at OIDs might be.
>
> How much of a pain would it be to make that a 64-bit counter? Would that
> create conflicts with the SQL standard?
>
> (No, I don't contribute code, so if that's a really stupid idea, just tell
> me so.)

Not hard, but another 4 bytes per row and some small performance
penalty.  Also, not all system support 64-bit ints.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Re: OIDs (Or: another RTFM question?)

From
Tom Lane
Date:
Joel Rees <joel@alpsgiken.gr.jp> writes:
> Tom Lane explained:
>> Basically what WITHOUT OIDS does for you is to reduce consumption of
>> OIDs, thereby postponing wraparound of the 32-bit OID counter.

> How much of a pain would it be to make that a 64-bit counter?

It'd be nontrivial, primarily because of portability issues: not all
platforms even have 64-bit ints, much less 64-bit ints that are fast
enough to justify making a core datatype be 64 bits.  (Not only OID,
but also Datum, would have to become 64 bits.  That is a *very*
pervasive change, and one with serious implications for performance.)

            regards, tom lane

Re: OIDs (Or: another RTFM question?)

From
Thomas Lockhart
Date:
> > How much of a pain would it be to make that a 64-bit counter?
> It'd be nontrivial, primarily because of portability issues...

This stuff should be configurable, depending on platform support. Very
pervasive in the code, but very possible to do for someone looking for a
new project imho. Getting partway would be enough to get others to help
on some of the issues...

                - Thomas

Re: OIDs (Or: another RTFM question?)

From
Tom Lane
Date:
Thomas Lockhart <lockhart@fourpalms.org> writes:
>>> How much of a pain would it be to make that a 64-bit counter?
>> It'd be nontrivial, primarily because of portability issues...

> This stuff should be configurable, depending on platform support.

We do already support platforms where Datum is 64 bits because pointers
are 64 bits (eg, Alpha); on such hardware I think 64-bit OIDs would
have near-zero added execution cost.  But I'm troubled by the notion of
having OID be 32 bits on some platforms and 64 on others.  We have more
than enough platform-dependency issues already...

            regards, tom lane

Re: OIDs (Or: another RTFM question?)

From
Adrian 'Dagurashibanipal' von Bidder
Date:
On Tue, 2002-07-16 at 18:12, Bruce Momjian wrote:

> Yes, WITHOUT OIDs just prevents the oid counter from incrementing as
> quickly and perhaps rolling over.  In 7.3 I think WITHOUT OID may save
> storage so I am going to avoid documenting it at this point.

Thanks for the answers, folks (I added a docnote pointing to this thread
so others may not need to ask the same question again).

Remaining question: if OIDs are optional for normal tables, why are they
created per default? Mandated by SQL? Backward compatibility?

cheers
-- vbi

--
secure email with gpg                         http://fortytwo.ch/gpg

Attachment

Re: OIDs (Or: another RTFM question?)

From
nconway@klamath.dyndns.org (Neil Conway)
Date:
On Wed, Jul 17, 2002 at 01:59:53AM -0400, Tom Lane wrote:
> We do already support platforms where Datum is 64 bits because pointers
> are 64 bits (eg, Alpha); on such hardware I think 64-bit OIDs would
> have near-zero added execution cost.  But I'm troubled by the notion of
> having OID be 32 bits on some platforms and 64 on others.  We have more
> than enough platform-dependency issues already...

I believe Peter already tried this, and concluded it wasn't worth
the trouble & performance hit:

    http://www.ca.postgresql.org/~petere/oid8.html

Cheers,

Neil

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Re: OIDs (Or: another RTFM question?)

From
Tom Lane
Date:
"Adrian 'Dagurashibanipal' von Bidder" <avbidder@fortytwo.ch> writes:
> Remaining question: if OIDs are optional for normal tables, why are they
> created per default? Mandated by SQL? Backward compatibility?

Backward compatibility.  The SQL spec has no concept of OIDs at all.

            regards, tom lane

Re: OIDs (Or: another RTFM question?)

From
Curt Sampson
Date:
On Wed, 17 Jul 2002, Tom Lane wrote:

> "Adrian 'Dagurashibanipal' von Bidder" <avbidder@fortytwo.ch> writes:
> > Remaining question: if OIDs are optional for normal tables, why are they
> > created per default? Mandated by SQL? Backward compatibility?
>
> Backward compatibility.  The SQL spec has no concept of OIDs at all.

I have grave doubts as to how practical this would be, but what
you do think of just getting rid of OIDs entirely? How hard would
this be to implement? How many applications would it really affect?

It seems a bit unclean to me to have this special OID mechanism
doing something that standard SQL/relational mechanisms already do
just fine.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: OIDs (Or: another RTFM question?)

From
Tom Lane
Date:
Curt Sampson <cjs@cynic.net> writes:
> I have grave doubts as to how practical this would be, but what
> you do think of just getting rid of OIDs entirely?

It's quite impractical as far as the system's internal uses go; if you
tried, you'd just have to invent some other, equivalent concept.

An OID counter per table seems within reach, however, and that would
go a long way towards eliminating the problems.

            regards, tom lane

Re: OIDs (Or: another RTFM question?)

From
Curt Sampson
Date:
On Thu, 18 Jul 2002, Tom Lane wrote:

> Curt Sampson <cjs@cynic.net> writes:
> > I have grave doubts as to how practical this would be, but what
> > you do think of just getting rid of OIDs entirely?
>
> It's quite impractical as far as the system's internal uses go; if you
> tried, you'd just have to invent some other, equivalent concept.

Why couldn't you just add an ID column to the system tables, and
use the number you put in that?

I also think it would make sense to have OIDs off by default, if
we're going to keep them, as they sort of invisibly take up space
if you're not using them. But on the other hand, that may not be
practical from a backwards compatability point of view.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: OIDs (Or: another RTFM question?)

From
Tom Lane
Date:
Curt Sampson <cjs@cynic.net> writes:
> On Thu, 18 Jul 2002, Tom Lane wrote:
>> Curt Sampson <cjs@cynic.net> writes:
> I have grave doubts as to how practical this would be, but what
> you do think of just getting rid of OIDs entirely?
>>
>> It's quite impractical as far as the system's internal uses go; if you
>> tried, you'd just have to invent some other, equivalent concept.

> Why couldn't you just add an ID column to the system tables, and
> use the number you put in that?

That's what I meant by "equivalent concept".

            regards, tom lane