OIDs (Or: another RTFM question?)

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

"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

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>

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

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

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

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

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

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

"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

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

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

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

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