From: Mihail Nasedkin <m.nasedkin.perm@mail.ru>
To: pgsql-sql@postgresql.org
Subject: Re: OID's
Date: Mon, 24 Jan 2005 10:21:08 +0500
Message-ID: <1649304531.20050124102108@mail.ru> (raw)
In-Reply-To: <20050124045835.GA19959@winnie.fuhr.org>
References: <3628044953.20050120154558@mail.ru>
<20050120164830.GB41081@winnie.fuhr.org>
<8411982406.20050121111734@mail.ru>
<20050121170438.GA56550@winnie.fuhr.org>
<214299421.20050124085743@mail.ru>
<20050124045835.GA19959@winnie.fuhr.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: m.nasedkin.perm@mail.ru
Subject: Re: OID's
In-Reply-To: <1649304531.20050124102108@mail.ru>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox