Обсуждение: Questions regarding OID
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) 2) Can it be used together with NOT NULL or PRIMARY KEY? 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?) 4) What happens if you CREATE a table with more than one field with oids? 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. Thanks for any help. L L Richi Plana 8^) ,-,-. ,-,-. ,-,-. ,-,-. ,- LL LL Systems Administrator / / \ \ / / \ \ / / \ \ / / \ \ / / LLLLL Mosaic Communications, Inc. \ \ / / \ \ / / \ \ / / \ \ / / LLLLL mailto:richip@mozcom.com `-'-' `-'-' `-'-' `-'-'
Richi Plana wrote: > I'm trying to use PostgreSQL data type oid and I have a couple > 2) Can it be used together with NOT NULL or PRIMARY KEY? oid data type is a 4 byte integer and as far as I know can be used pretty much the same way.
The last time I tried to use OID I wanted to :- select max(oid) from table; apparently max isn't defined for OID and I didn't seem to be able to cast it as an int... Am I doing somthing wrong? James On Sat, 26 Jun 1999, Chris Bitmead wrote: > Richi Plana wrote: > > I'm trying to use PostgreSQL data type oid and I have a couple > > 2) Can it be used together with NOT NULL or PRIMARY KEY? > > oid data type is a 4 byte integer and as far as I know can be used > pretty much the same way. > A.J. (james@fsck.co.uk) Sometimes you're ahead, somtimes you're behind. The race is long, and in the end it's only with yourself.
> 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
Hi, On Fri, 25 Jun 1999, Karl DeBisschop wrote: |o| > 3) How does one INSERT data in a table with an oid field? (what does one |o| > put in the VALUES () entry assigned to the OID?) |o| |o| We've never tried to INSERT with a predetermined OID, and I'm |o| pretty sure it would break things if you tried. What happens is |o| that a free OID is assigned, which you can then look up. The last |o| OID assigned is generally shown by psql. PHP and perl's DBD::Pg |o| provide methods for accessing the last OID after an INSERT. You |o| can then write this value into another table. I'm sorry. What I meant was, given a the ff. definition of a table: CREATE TABLE foo ( id oid, ... ); how does one INSERT data into that table? Should I do INSERT INTO foo VALUES (???, ...); |o| > 4) What happens if you CREATE a table with more than one field with oids? |o| |o| Let's be precise here. There is an OID for each row in the |o| database, but there is also an oid datatype. By definition, you |o| cannot have more than one OID for a given row. But you may have |o| an arbitrary number of fields in a table that have oid types. |o| These fields would typically point to other rows in other tables. I think I seem to have misunderstood OIDs. So there's an oid datatype, and there's an associated OID for every record. Hmmm ... dunno how that helps me. What I really want is simple: I wish to assign a unique ID for each record. I was hoping OIDs would by my solution because otherwise I'd have to use a numeric idea increasing each by one for every INSERT. That would require that I look for the MAX() ID value and increment that before INSERTing. Then there's the problem of DELETEing records which would leave blanks in my ID sequence. I just assumed that by adding an oid datatype, it'd be assigned a unique idea at every INSERT. So is the OID guaranteed to never change? |o| You're welcome. But remeber, this is a rather limited sort of |o| help. Well, this is the best explanation I've seen so far. Thank you again. L L Richi Plana 8^) ,-,-. ,-,-. ,-,-. ,-,-. ,- LL LL Systems Administrator / / \ \ / / \ \ / / \ \ / / \ \ / / LLLLL Mosaic Communications, Inc. \ \ / / \ \ / / \ \ / / \ \ / / LLLLL mailto:richip@mozcom.com `-'-' `-'-' `-'-' `-'-'
<not repeating alot of stuff> Each record has an oid, which is assured to be unique in that database. Thus you can use it for your purpose, I believe. To fine the oid with any record, just: SELECT oid,* from mytable; The OID is always there, and you don't have to add it - you just don't see it uless you specifically ask for it. The if you are creating another table that links to the OIDs of mytable, say as CREATE TABLE anothertable (firstcol int4, pointer_to_mytable oid, thirdcol text); Then you can use the oids you selected above. As I mentioned before, see perldoc DBI::Pg for perl interface to the last inserted oid, and the PHP manual for PHP's equivalent. C lib and other interfaces can also do this - I just don't know exactly wher ethat documentation is. The OID will never change. With one caveat. If you dump/restore a database, you must use -o on pg_dump to preserve OIDs Hope this clarifies. -- 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