Re: [GENERAL] Questions regarding OID - Mailing list pgsql-general

>   Date: Fri, 25 Jun 1999 23:00:25 +0800 (PHT)
>   From: Richi Plana <richip@mozcom.com>
>   Content-Type: TEXT/PLAIN; charset=US-ASCII
>   Sender: owner-pgsql-general@postgreSQL.org
>   Precedence: bulk
>
>   Hi,
>
>   I'm trying to use PostgreSQL data type oid and I have a couple of
>   questions about it:
>
>   1) (Most important) Where can I find documentation covering the use of
>   OIDs in PostgreSQL? The only doc which discusses it somewhat that I've
>   seen is the FAQ (and it just tells people what OIDs are)

Documentation is scatterred.  This is only what we've found out
through use.

>   2) Can it be used together with NOT NULL or PRIMARY KEY?

Every row in a table is assigned an OID upon creation.  Thus, it is
already NOT NULL.  Each OID is unique throughout the database, thus it
is for most purposes PRIMARY KEY.  But niether of these have to be
declared (nor should they be).

>   3) How does one INSERT data in a table with an oid field? (what does one
>   put in the VALUES () entry assigned to the OID?)

We've never tried to INSERT with a predetermined OID, and I'm pretty
sure it would break things if you tried.  What happens is that a free
OID is assigned, which you can then look up.  The last OID assigned is
generally shown by psql.  PHP and perl's DBD::Pg provide methods for
accessing the last OID after an INSERT.  You can then write this value
into another table.

The only way I know to control OIDs is to do a pg_dump woth the -o
option, then you can restore the OIDs later - but this pretty much
assumes you are putting the data back into an empty database.
Otherwise, you could break things if a record has been created in the
database that has the same OID as a record you are trying to INSERT.

>   4) What happens if you CREATE a table with more than one field with oids?

Let's be precise here.  There is an OID for each row in the database,
but there is also an oid datatype.  By definition, you cannot have
more than one OID for a given row.  But you may have an arbitrary
number of fields in a table that have oid types.  These fields would
typically point to other rows in other tables.

>   5) How big are OIDs? If I use another field in another table to reference
>   the OID, I need to know how big a char() field I need to define.

We usually use the oid type to hold oids.  By using this type instead
of int4, we are explicit about what we are doing, we run a tad faster
because we don't have to convert types, and we don't need to know
about the size of and oid if it should change at some point in the future.

>   Thanks for any help.

You're welcome.  But remeber, this is a rather limited sort of help.
I'm sure it's not comperhensive, and I'd welcome any feedback others
have.  As I said, alot of this is just from use, rather than any
authoritative source.

Also, I'd think twice about using OID in tour database structure for a
few reasons.

   - First is that this is not standard SQL, and thus cannot be
     replicated in most other DBMS's.  You make things difficult if
     you have to port.

   - Second is that it can make life tricky sometimes when backing up
     and restoring (how many times have people out there forgot the -o
     switch? come on, you can admit it, we're all friends here.)

   - An oid as a reference can be unnecessarily cryptic to your users,
     should they ever see it.

In many cases, you'd might consider a sequence counter instead (check
the manual and do '\h create sequence' in psql if you aren't familiar
with this). That being said, they can be useful, they are already
there so they don't take up more sapce, and I seem to remember hearing
somepalce that they are a little faster than integers for searching
and/or access.  But if you want rapid lookup using an OID, make sure
you create an index for it:

    CREATE UNIQUE INDEX mytable_oid ON mytable;

As always, you can make sure that the index is being used with:

    EXPLAIN SELECT ....

>   L   L Richi Plana 8^)         ,-,-.     ,-,-.     ,-,-.     ,-,-.     ,-
>   LL LL Systems Administrator  / / \ \   / / \ \   / / \ \   / / \ \   / /
>   LLLLL Mosaic Communications, Inc. \ \ / /   \ \ / /   \ \ / /   \ \ / /
>   LLLLL mailto:richip@mozcom.com     `-'-'     `-'-'     `-'-'     `-'-'


Hope all this helps

--
Karl DeBisschop <kdebisschop@spaceheater.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com  - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper