Thread: OID's
Hi, I have a question about OID. How (where) I can get all OID's of the PostgeSQL installation? In other words where OID's is stored? Is it stored in special table? I would like use some SQL queries with the all OID's. Is this possible? I hadn't find the answer in the FAQ ( 4.16 What is an OID? What is a TID?). -- С уважением,Mihail mailto:m.nasedkin.perm@mail.ru
On Thu, Jan 20, 2005 at 03:45:58PM +0500, Mihail Nasedkin wrote: > How (where) I can get all OID's of the PostgeSQL > installation? > In other words where OID's is stored? Is it stored in special table? See the "System Columns" section in the "Data Definition" chapter of the PostgreSQL documentation. Tables that store objects with OIDs should have an oid column; you could query pg_attribute to find out what tables those are. > I would like use some SQL queries with the all OID's. To what end? Are you aware that PostgreSQL allows tables to be created without OIDs? What problem are you trying to solve? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Hello, Michael. Thank you for answer January, 20 2005, 21:48:30: MF> On Thu, Jan 20, 2005 at 03:45:58PM +0500, Mihail Nasedkin wrote: >> How (where) I can get all OID's of the PostgeSQL >> installation? >> In other words where OID's is stored? Is it stored in special table? MF> See the "System Columns" section in the "Data Definition" chapter MF> of the PostgreSQL documentation. Tables that store objects with MF> OIDs should have an oid column; you could query pg_attribute to MF> find out what tables those are. I have already read about "System Columns" of the PostgreSQL documentation. In the table "pg_catalog.pg_attribute" column "attrelid" contain only "system OID's" but not OID's from records of the user tables. But I would like to use OID's of all records of the all my tables. ^^^ ^^^ I try to use rules on INSERT action of my tables to store last insert oid, but at the moment of the INSERT row into table OID value inaccessible (unknown). >> I would like use some SQL queries with the all OID's. MF> To what end? Are you aware that PostgreSQL allows tables to be MF> created without OIDs? Yes, of course, but in my case I create tables with OID and then want use OID of all records of the all tables as one column in some query. I think what system of OID's is very useful for application! MF> What problem are you trying to solve? For example, I want to fetching all rows of the several tables in one query by means of LEFT JOIN, but not use UNION operator. -- Mihail Nasedkin mailto:m.nasedkin.perm@mail.ru
On Fri, Jan 21, 2005 at 11:17:34AM +0500, Mihail Nasedkin wrote: > I have already read about "System Columns" of the PostgreSQL documentation. > In the table "pg_catalog.pg_attribute" column "attrelid" contain > only "system OID's" but not OID's from records of the user tables. > > But I would like to use OID's of all records of the all my tables. > ^^^ ^^^ If you want to do that then you'll need to know which tables have OIDs. If you just want rows then you could query pg_class and filter on the relhasoids column, but if you also want things like large objects then you might need to query pg_attribute and look for all columns having an "oid" type. It sounds like you're not interested in the latter, however. > I try to use rules on INSERT action of my tables to store last insert > oid, but at the moment of the INSERT row into table OID value > inaccessible (unknown). A row's OID should be visible in an AFTER trigger. > >> I would like use some SQL queries with the all OID's. > > MF> To what end? Are you aware that PostgreSQL allows tables to be > MF> created without OIDs? > > Yes, of course, but in my case I create tables with OID and then want use > OID of all records of the all tables as one column in some query. Are you aware that OIDs aren't guaranteed to be unique due to wraparound? If you have a UNIQUE constraint on each table's oid column then the combination of (tableoid, oid) might serve your needs. > I think what system of OID's is very useful for application! Assigning row IDs from a common sequence could serve the same purpose, and since sequences are 64 bits you wouldn't be as subject to a wraparound problem (OIDs are 32 bits). > MF> What problem are you trying to solve? > > For example, I want to fetching all rows of the several tables in one > query by means of LEFT JOIN, but not use UNION operator. Again, what problem are you trying to solve? Using OIDs might not be the best solution, and if we knew what you're trying to do then we might be able to suggest alternatives. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Mihail Nasedkin wrote: > Hello, Michael. > > Thank you for answer January, 20 2005, 21:48:30: > > MF> On Thu, Jan 20, 2005 at 03:45:58PM +0500, Mihail Nasedkin wrote: > > >>>How (where) I can get all OID's of the PostgeSQL >>>installation? >>>In other words where OID's is stored? Is it stored in special table? > > > MF> See the "System Columns" section in the "Data Definition" chapter > MF> of the PostgreSQL documentation. Tables that store objects with > MF> OIDs should have an oid column; you could query pg_attribute to > MF> find out what tables those are. > I have already read about "System Columns" of the PostgreSQL documentation. > In the table "pg_catalog.pg_attribute" column "attrelid" contain > only "system OID's" but not OID's from records of the user tables. > > But I would like to use OID's of all records of the all my tables. > ^^^ ^^^ > I try to use rules on INSERT action of my tables to store last insert > oid, but at the moment of the INSERT row into table OID value > inaccessible (unknown). You are aware that OIDs aren't guaranteed to be unique, aren't you? >>>I would like use some SQL queries with the all OID's. > > > MF> To what end? Are you aware that PostgreSQL allows tables to be > MF> created without OIDs? > Yes, of course, but in my case I create tables with OID and then want use > OID of all records of the all tables as one column in some query. > > I think what system of OID's is very useful for application! > > MF> What problem are you trying to solve? > For example, I want to fetching all rows of the several tables in one > query by means of LEFT JOIN, but not use UNION operator. Joins are designed to be over primary keys, you should make sure you have valid pkeys defined and use those instead. -- Richard Huxton Archonet Ltd
Hello, pgsql-sql and Michael.
MF> On Fri, Jan 21, 2005 at 11:17:34AM +0500, Mihail Nasedkin wrote:
>> I have already read about "System Columns" of the PostgreSQL documentation.
>> In the table "pg_catalog.pg_attribute" column "attrelid" contain
>> only "system OID's" but not OID's from records of the user tables.
>>
>> But I would like to use OID's of all records of the all my tables.
>> ^^^ ^^^
MF> If you want to do that then you'll need to know which tables have
MF> OIDs. If you just want rows then you could query pg_class and
MF> filter on the relhasoids column, but if you also want things like
MF> large objects then you might need to query pg_attribute and look
MF> for all columns having an "oid" type. It sounds like you're not
MF> interested in the latter, however.
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. Or is
there system function that return last insert oid like
$sth->{'pg_oid_status'} in the DBD::Pg?
>> I try to use rules on INSERT action of my tables to store last insert
>> oid, but at the moment of the INSERT row into table OID value
>> inaccessible (unknown).
MF> A row's OID should be visible in an AFTER trigger.
OK.
>> >> I would like use some SQL queries with the all OID's.
>>
>> MF> To what end? Are you aware that PostgreSQL allows tables to be
>> MF> created without OIDs?
>>
>> Yes, of course, but in my case I create tables with OID and then want use
>> OID of all records of the all tables as one column in some query.
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?
>> I think what system of OID's is very useful for application!
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> What problem are you trying to solve?
>>
>> For example, I want to fetching all rows of the several tables in one
>> query by means of LEFT JOIN, but not use UNION operator.
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.
--
Regards,Mihail Nasedkin mailto:m.nasedkin.perm@mail.ru
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/
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