Thread: OIDs (Or: another RTFM question?)
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
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
Attachment
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