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.
No system table contains a list of all OIDs, if that's what you're
asking.
> Or is there system function that return last insert oid like
> $sth->{'pg_oid_status'} in the DBD::Pg?
In a PL/pgSQL function you can use GET DIAGNOSTICS to get RESULT_OID
after an INSERT; in an AFTER trigger you can refer to a row's oid
column; in client code that uses libpq you can call PQoidValue().
If you're using another interface then see its documentation. I'm
not aware of a function that you can call directly from SQL.
> 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?
Actually it's FAQ 4.15:
http://www.postgresql.org/files/documentation/faqs/FAQ.html#4.15
The wording is misleading when it says that OIDs are unique; it
should probably be corrected, although it does mention that OIDs
can overflow. For more information see "Object Identifier Types"
in the "Data Types" chapter of the documentation:
http://www.postgresql.org/docs/8.0/static/datatype-oid.html
"The oid type is currently implemented as an unsigned four-byte
integer. Therefore, it is not large enough to provide database-wide
uniqueness in large databases, or even in large individual tables.
So, using a user-created table's OID column as a primary key is
discouraged. OIDs are best used only for references to system
tables."
See also "System Columns" in the "Data Definition" chapter:
http://www.postgresql.org/docs/8.0/static/ddl-system-columns.html
"OIDs are 32-bit quantities and are assigned from a single cluster-wide
counter. In a large or long-lived database, it is possible for the
counter to wrap around. Hence, it is bad practice to assume that
OIDs are unique, unless you take steps to ensure that this is the
case."
The documentation gives advice on how to use OIDs as unique identifiers
but recommends using a sequence (serial) instead.
> 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)
That's what I said. 4 bytes = 32 bits, assuming the 8-bit bytes
that are nearly universal. (Would PostgreSQL even run on systems
with, say, 9-bit bytes?)
> 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.
Because that system doesn't guarantee uniqueness, at least not
without special care. You might be able to use the combination of
(tableoid, oid) as a unique row identifier if each table has a
unique constraint on its oid column, but you'll need to handle cases
where the oid has wrapped around and the constraint is violated
when you insert a new row. If your database isn't heavily used
then the chance of that happening might be unlikely, but it's
precisely the unlikely that can cause strange, hard-to-debug problems
because you weren't expecting it and the circumstances are difficult
to duplicate.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
