Re: OID's

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(dot)nasedkin(dot)perm(at)mail(dot)ru