Обсуждение: RE: AW: Re: OID wraparound: summary and proposal

> It seems to me, I guess and others too, that the OID mechanism should
be on a
> per table basis. That way OIDs are much more likely to be unique, and
TRUNCATE
> on a table should reset it's OID counter to zero.

Seems to me, that this would be no different than a performance improved
version
of SERIAL.
If you really need OID, you imho want the systemid tableid tupleid
combo.
A lot of people seem to use OID, when they really could use XTID. That
is
what I wanted to say.

Andreas

Zeugswetter Andreas SB SD wrote:
> 
> > It seems to me, I guess and others too, that the OID mechanism should
> be on a
> > per table basis. That way OIDs are much more likely to be unique, and
> TRUNCATE
> > on a table should reset it's OID counter to zero.
> 
> Seems to me, that this would be no different than a performance improved
> version
> of SERIAL.
> If you really need OID, you imho want the systemid tableid tupleid
> combo.
> A lot of people seem to use OID, when they really could use XTID. That
> is
> what I wanted to say.
> 

I don't care about having an OID or ROWID, I care that there is a 2^32 limit to
the current OID strategy and that a quick fix of allowing tables to exist
without OIDs may break some existing software. I was suggesting the OIDs be
managed on a "per table" basis as a better solution.

In reality, a 32 bit OID, even isolated per table, may be too small. Databases
are getting HUGE. 40G disk drives are less than $100 bucks, in a few months 80G
drives will be less than $200, one can put together 200G RAID systems for about
$1000, a terabyte for about $5000. A database that would have needed an
enterprise level system, just 7 years ago, can be run on a $500 desktop today.



-- 
5-4-3-2-1 Thunderbirds are GO!
------------------------
http://www.mohawksoft.com

On Mon, 6 Aug 2001, mlw wrote:

> Zeugswetter Andreas SB SD wrote:
> > 
> > > It seems to me, I guess and others too, that the OID mechanism should
> > be on a
> > > per table basis. That way OIDs are much more likely to be unique, and
> > TRUNCATE
> > > on a table should reset it's OID counter to zero.
> > 
> > Seems to me, that this would be no different than a performance improved
> > version
> > of SERIAL.
> > If you really need OID, you imho want the systemid tableid tupleid
> > combo.
> > A lot of people seem to use OID, when they really could use XTID. That
> > is
> > what I wanted to say.
> > 
> 
> I don't care about having an OID or ROWID, I care that there is a 2^32 limit to
> the current OID strategy and that a quick fix of allowing tables to exist
> without OIDs may break some existing software. I was suggesting the OIDs be
> managed on a "per table" basis as a better solution.
Again, what existing software demands per-table OID field? Isn't it what
primary keys are for?

> In reality, a 32 bit OID, even isolated per table, may be too small.
> Databases are getting HUGE. 40G disk drives are less than $100 bucks,
> in a few months 80G drives will be less than $200, one can put
> together 200G RAID systems for about $1000, a terabyte for about
> $5000. A database that would have needed an enterprise level system,
> just 7 years ago, can be run on a $500 desktop today.
If its too small for you, make a serial8 datatype (or something like
this), and use it for your tables. For me, I have tables which have very
few fields, and I don't want to waste 4 bytes/row (much less 8) for OID.

mlw wrote:
> 
> Zeugswetter Andreas SB SD wrote:
> >
> > > It seems to me, I guess and others too, that the OID mechanism should
> > be on a
> > > per table basis. That way OIDs are much more likely to be unique, and
> > TRUNCATE
> > > on a table should reset it's OID counter to zero.
> >
> > Seems to me, that this would be no different than a performance improved
> > version of SERIAL.
> > If you really need OID, you imho want the systemid tableid tupleid
> > combo.

having such an global_oid fits nicely with having table-uniqe oids.

just do 

select 'mysite.'||text(tableoid)||'.'||text(oid) as global_oid from
mytable;

to get it

> I don't care about having an OID or ROWID, I care that there is a 2^32 limit to
> the current OID strategy and that a quick fix of allowing tables to exist
> without OIDs may break some existing software. I was suggesting the OIDs be
> managed on a "per table" basis as a better solution.

Now that we have tableoid the need of globally unique oid is much
diminished.
> In reality, a 32 bit OID, even isolated per table, may be too small. Databases
> are getting HUGE. 40G disk drives are less than $100 bucks, in a few months 80G
> drives will be less than $200, one can put together 200G RAID systems for about
> $1000, a terabyte for about $5000. A database that would have needed an
> enterprise level system, just 7 years ago, can be run on a $500 desktop today.

And my PalmPilot has more memory adn storage and processor power than
PDP-11 
where UNIX was developed ;)

So the real solution will be going to 64-bit OID's and XIDS, just that
some 
platforms (I'd like to know which) dont have a good "long long"
implementation yet;



------------------
Hannu

Zeugswetter Andreas SB SD wrote:
> 
> > It seems to me, I guess and others too, that the OID mechanism should
> be on a
> > per table basis. That way OIDs are much more likely to be unique, and
> TRUNCATE
> > on a table should reset it's OID counter to zero.
> 
> Seems to me, that this would be no different than a performance improved
> version
> of SERIAL.
> If you really need OID, you imho want the systemid tableid tupleid
> combo.

or (systemid.tableid.tupleid.versioninterval) if you want to be able to
time-travel

---------------
Hannu

> -----Original Message-----
> From: Alex Pilosov
>
> On Mon, 6 Aug 2001, mlw wrote:
>
> > Zeugswetter Andreas SB SD wrote:
> > >
> > > > It seems to me, I guess and others too, that the OID
> mechanism should
> > > be on a
> > > > per table basis. That way OIDs are much more likely to be
> unique, and
> > > TRUNCATE
> > > > on a table should reset it's OID counter to zero.
> > >
> > > Seems to me, that this would be no different than a
> performance improved
> > > version
> > > of SERIAL.
> > > If you really need OID, you imho want the systemid tableid tupleid
> > > combo.
> > > A lot of people seem to use OID, when they really could use XTID. That
> > > is
> > > what I wanted to say.
> > >
> >
> > I don't care about having an OID or ROWID, I care that there is
> a 2^32 limit to
> > the current OID strategy and that a quick fix of allowing
> tables to exist
> > without OIDs may break some existing software. I was suggesting
> the OIDs be
> > managed on a "per table" basis as a better solution.
> Again, what existing software demands per-table OID field? Isn't it what
> primary keys are for?
>

I was just about to implement updatable cursors in psqlODBC using
TID and OID. I've half done it but the rest is pending now. I've had the
the plan since I introduced Tid scan in 7.0.

regards,
Hiroshi Inoue

Hmm, this has proven more contentious than I expected ;-).  It seems the
one thing that absolutely everybody agrees on is that 4-byte OIDs are no
longer workable as global identifiers.

My feeling after reading the discussions is that the best way to go
in the long run is to change from a database-wide OID generator to
per-table OID generators, and to say that if you want a database-wide
unique identifier then you should use <table oid, row oid> as that
identifier.  If you want cluster-wide or universe-wide uniqueness then
you stick additional fields on the front of that.  Unique IDs formed
in this way are a lot more useful than IDs taken from a simple global
sequence, because you can use the subfields to determine where to look
for the object.

If OID remains at 4 bytes then this still isn't very satisfactory for
tables that are likely to have more than 4 billion INSERTs in their
lifetime.  However, rather than imposing the cost of 8-byte OIDs
everywhere, I'd be inclined to say that people who need unique
identifiers in such tables should use user-defined columns generated
from int8 sequences.  (Obviously it would help if we created an
int8-based sequence type... but that's certainly doable.)  Perhaps in
another few years, when all portability and performance issues with int8
are history, we could think about changing OID to 8 bytes everywhere;
but I don't think that's a good idea just yet.

I do not think it is feasible to try to implement per-table OID
generation for 7.2.  What I'd like to do for 7.2 is continue with
my previous proposal of making OID generation optional on a per-table
basis (but the default is still to generate them).  This seems to fit
well with an eventual migration to per-table OIDs, since it still seems
to me that some tables don't need them at all --- particularly, tables
that are using an int8 column as key because wraparound is expected.
Also, I will change pg_description as previously discussed, since this
is clearly necessary in a per-table-OID world.

Comments, objections?
        regards, tom lane

mlw <markw@mohawksoft.com> writes:
> Am I being overly simplistic?

Yes.  For one thing, Relation structs are *not* shared, nor even
persistent (the relcache will happily discard them).  For another, you
haven't mentioned how we keep the counter up-to-date across system
restarts.
        regards, tom lane

Could we modify the Relation structure to hold an Oid counter? So every where
Postgres calls "newoid(void)" it gets changed to pass the relation structure it
will be associated with, i.e. newoid(Relation *). That way, every relation
could have its own counter, AND perhaps its own spinlock. Relations are shared
amongst the various processes, correct? If you pass NULL as the relation, you
get an OID out of the ShmemVariableCache->nextXid.

Am I being overly simplistic?

Tom Lane wrote:
>
> If OID remains at 4 bytes then this still isn't very satisfactory for
> tables that are likely to have more than 4 billion INSERTs in their
> lifetime.  However, rather than imposing the cost of 8-byte OIDs
> everywhere, I'd be inclined to say that people who need unique
> identifiers in such tables should use user-defined columns generated
> from int8 sequences.  (Obviously it would help if we created an
> int8-based sequence type... but that's certainly doable.)  Perhaps in
> another few years, when all portability and performance issues with int8
> are history, we could think about changing OID to 8 bytes everywhere;
> but I don't think that's a good idea just yet.

Which are those platforms that currently lack 8-byte ints or whose 
8-byte ints are limited to values below 2^31 ?

Managing huge tables on such platforms seems to be quite hard anyway .

I guess that the change of OID from 4 to 8 bytes could be carried out as
a 
compile time option ?

> I do not think it is feasible to try to implement per-table OID
> generation for 7.2.  What I'd like to do for 7.2 is continue with
> my previous proposal of making OID generation optional on a per-table
> basis (but the default is still to generate them). This seems to fit
> well with an eventual migration to per-table OIDs, since it still seems
> to me that some tables don't need them at all --- particularly, tables
> that are using an int8 column as key because wraparound is expected.
> Also, I will change pg_description as previously discussed, since this
> is clearly necessary in a per-table-OID world.

Changing pg_description to (table_oid,row_oid) seems reasonable for
other 
reasons too, like going from description to the describee. I dont think 
that pg_attribute is such a heavy OID-eater, except perhaps in case
where 
each transaction creates and destroys temporary tables with very high 
number of columns.

-----------------
Hannu

Tom Lane wrote:
> 
> mlw <markw@mohawksoft.com> writes:
> > Am I being overly simplistic?
> 
> Yes.  For one thing, Relation structs are *not* shared, nor even
> persistent (the relcache will happily discard them). 

Will it be easier to make Relation shared and persistent or creating 
a new shared structure that has just a counter+lock for each 
relation oid ?

> For another, you
> haven't mentioned how we keep the counter up-to-date across system
> restarts.

Perhaps write it to database at checkpoints and get the last INSERTED
record 
from WAL at restart ? 

Probably too simplistic as well ;)

Hannu Krosing <hannu@tm.ee> writes:
> I guess that the change of OID from 4 to 8 bytes could be carried out
> as a compile time option ?

Not unless you like the notion that the wire protocol depends on a
compile time option.
        regards, tom lane

Hannu Krosing <hannu@tm.ee> writes:
> Will it be easier to make Relation shared and persistent or creating 
> a new shared structure that has just a counter+lock for each 
> relation oid ?

The latter.  Relation (by which I mean a whole relcache entry with all
its subsidiary structure, not only struct RelationData) is too large,
complex and heavyweight a structure to be a good candidate for moving
into shared memory.  It also contains a lot of backend-local status
data in its current incarnation.

Some kind of shared cache for sequence generators (essentially,
generalizing the existing shared OID counter into N counters) is
probably the answer.  But it would have to be a cache, not the whole
truth, so there'd need to be an underlying table that holds counters not
currently swapped into cache.  That part we don't have a good model for
in the existing OID-generator code, nor in the existing sequence code.
        regards, tom lane

Tom,

If we have WITH NOOID, why not having a WITH OID32 and WITH OID64 (or
something of a sort)
as well (being OID32 the default and OID an alias to it)?  
The last would not be available on some systems 
(who will use a system that does not support long long as a database
server anyway?)

The wire protocol will always handle the (tableoid) long form,
references will always store
the long form...  The OID32 would exist only to allow people to save
space in tables that need
OIDs but not the 64 bit version.



-- 
Fernando Nasser
Red Hat Canada Ltd.                     E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Fernando Nasser <fnasser@redhat.com> writes:
> The wire protocol will always handle the (tableoid) long form,

I think you are handwaving away what is precisely the most painful
aspect.  To allow 64-bit type OIDs in the wire protocol, we must
(a) have a protocol version jump, and (b) force all servers and all
client libraries to be 64-bit-capable.  While I'm prepared to think
that "int8 is really only 32 bits wide" is tolerable within a single
server installation, I really don't want to deal with such headaches
between clients and servers.  Can you imagine how hard it will be
to track down a bug that arises because one old client is dropping
the high-order bits of type OIDs?  Only installations that had been
up for years would ever see a problem; how likely is it that anyone
would even remember that some of their clients were not 64-bit-ready?

When we're ready to make that jump, I think we should just move to
64 bit OIDs, full stop, no exceptions, no turning back, no "configure
time option", no backwards compatibility with old clients.  Anything
else is a time bomb.  I'd even be inclined to start running the OID
counter at 4-billion-plus-1, to help flush out anyplace that drops the
high half.
        regards, tom lane

Tom Lane wrote:
> 
> Hannu Krosing <hannu@tm.ee> writes:
> > I guess that the change of OID from 4 to 8 bytes could be carried out
> > as a compile time option ?
> 
> Not unless you like the notion that the wire protocol depends on a
> compile time option.

That could be a separate option, perhaps even a runtime one.

And yet another flag for determining weather to raise an error on
wire-oid 
overflow or just to masquerade it as rollower ;)

--------------
Hannu

Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > The wire protocol will always handle the (tableoid) long form,
> 
> I think you are handwaving away what is precisely the most painful
> aspect.  To allow 64-bit type OIDs in the wire protocol, we must
> (a) have a protocol version jump, and (b) force all servers and all
> client libraries to be 64-bit-capable.  While I'm prepared to think
> that "int8 is really only 32 bits wide" is tolerable within a single
> server installation, I really don't want to deal with such headaches
> between clients and servers.  Can you imagine how hard it will be
> to track down a bug that arises because one old client is dropping
> the high-order bits of type OIDs?  Only installations that had been
> up for years would ever see a problem; how likely is it that anyone
> would even remember that some of their clients were not 64-bit-ready?
> 

A protocol bump is inevitable if we ever want to deal with 64 bit OIDs,
so the sooner we do it the better.  

Someone pointed out that even with optional OIDs and per table OIDs,
we would still need to allow per table OIDs to be more than 32 bits 
(I am taking his word for it).  If that is the case, the scenario you
described above is inevitable.


> When we're ready to make that jump, I think we should just move to
> 64 bit OIDs, full stop, no exceptions, no turning back, no "configure
> time option", no backwards compatibility with old clients.  Anything
> else is a time bomb.  I'd even be inclined to start running the OID
> counter at 4-billion-plus-1, to help flush out anyplace that drops the
> high half.
> 

That would be the way to go.  We are just trying to buy some time with
the other measures.

But some folks are complaining of having to use 64 bit OIDs when they
don't really need them, so that is why I proposed the OID32/OID64 option.


-- 
Fernando Nasser
Red Hat - Toronto                       E-Mail:  fnasser@redhat.com
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

Tom Lane wrote:
> 
> Fernando Nasser <fnasser@redhat.com> writes:
> > The wire protocol will always handle the (tableoid) long form,
> 
> I think you are handwaving away what is precisely the most painful
> aspect.  To allow 64-bit type OIDs in the wire protocol, we must
> (a) have a protocol version jump, and (b) force all servers and all
> client libraries to be 64-bit-capable.  While I'm prepared to think
> that "int8 is really only 32 bits wide" is tolerable within a single
> server installation, I really don't want to deal with such headaches
> between clients and servers.  Can you imagine how hard it will be
> to track down a bug that arises because one old client is dropping
> the high-order bits of type OIDs? 

When I thought of it, my solution was to issue a NOTICE on each and 
very OID truncation - they should be visible enough to force upgrade ;)

> Only installations that had been
> up for years would ever see a problem; how likely is it that anyone
> would even remember that some of their clients were not 64-bit-ready?
> 
> When we're ready to make that jump, I think we should just move to
> 64 bit OIDs, full stop, no exceptions, no turning back, no "configure
> time option", no backwards compatibility with old clients.  Anything
> else is a time bomb.  I'd even be inclined to start running the OID
> counter at 4-billion-plus-1, to help flush out anyplace that drops the
> high half.
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly