Re: OID's - Mailing list pgsql-sql

Thanks Michael for answer January, 24 2005, 9:58:35:

MF> On Mon, Jan 24, 2005 at 08:57:43AM +0500, Mihail Nasedkin wrote:

>> I don't need to know which tables have OIDS. I want know which system
>> table contain column OID with all OID's inserted into my tables.

MF> No system table contains a list of all OIDs, if that's what you're
MF> asking.

Yes, ok.

>> Or is there system function that return last insert oid like
>> $sth->{'pg_oid_status'} in the DBD::Pg?

MF> In a PL/pgSQL function you can use GET DIAGNOSTICS to get RESULT_OID
MF> after an INSERT; in an AFTER trigger you can refer to a row's oid
MF> column; in client code that uses libpq you can call PQoidValue().
MF> If you're using another interface then see its documentation.  I'm
MF> not aware of a function that you can call directly from SQL.

Ok.

>> MF> Are you aware that OIDs aren't guaranteed to be unique due to
>> MF> wraparound?  If you have a UNIQUE constraint on each table's oid
>> MF> column then the combination of (tableoid, oid) might serve your
>> MF> needs.
>>
>> I think that OIDs are guaranteed to be unique according to FAQ 4.16)
>> What is an OID? What is a TID?

MF> Actually it's FAQ 4.15:

MF> http://www.postgresql.org/files/documentation/faqs/FAQ.html#4.15

MF> The wording is misleading when it says that OIDs are unique; it
MF> should probably be corrected, although it does mention that OIDs
MF> can overflow.  For more information see "Object Identifier Types"
MF> in the "Data Types" chapter of the documentation:

MF> http://www.postgresql.org/docs/8.0/static/datatype-oid.html

MF> "The oid type is currently implemented as an unsigned four-byte
MF> integer.  Therefore, it is not large enough to provide database-wide
MF> uniqueness in large databases, or even in large individual tables.
MF> So, using a user-created table's OID column as a primary key is
MF> discouraged.  OIDs are best used only for references to system
MF> tables."

MF> See also "System Columns" in the "Data Definition" chapter:

MF> http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html

MF> "OIDs are 32-bit quantities and are assigned from a single cluster-wide
MF> counter.  In a large or long-lived database, it is possible for the
MF> counter to wrap around.  Hence, it is bad practice to assume that
MF> OIDs are unique, unless you take steps to ensure that this is the
MF> case."

MF> The documentation gives advice on how to use OIDs as unique identifiers
MF> but recommends using a sequence (serial) instead.
Persuasively, ok.


>> MF> Assigning row IDs from a common sequence could serve the same
>> MF> purpose, and since sequences are 64 bits you wouldn't be as subject
>> MF> to a wraparound problem (OIDs are 32 bits).
>>
>> OIDs are stored as 4-byte integers (see FAQ)

MF> That's what I said.  4 bytes = 32 bits, assuming the 8-bit bytes
MF> that are nearly universal.  (Would PostgreSQL even run on systems
MF> with, say, 9-bit bytes?)
My mistake, ok.

>> MF> Again, what problem are you trying to solve?  Using OIDs might not
>> MF> be the best solution, and if we knew what you're trying to do then
>> MF> we might be able to suggest alternatives.
>>
>> Why alternatives if already exists system of the identification of all
>> rows from all tables.

MF> Because that system doesn't guarantee uniqueness, at least not
MF> without special care.  You might be able to use the combination of
MF> (tableoid, oid) as a unique row identifier if each table has a
MF> unique constraint on its oid column, but you'll need to handle cases
MF> where the oid has wrapped around and the constraint is violated
MF> when you insert a new row.  If your database isn't heavily used
MF> then the chance of that happening might be unlikely, but it's
MF> precisely the unlikely that can cause strange, hard-to-debug problems
MF> because you weren't expecting it and the circumstances are difficult
MF> to duplicate.
Ok.

I think, that we close the theme of the OIDs for next time.

-- Mihail Nasedkin                         mailto:m.nasedkin.perm@mail.ru