[GENERAL] row oids as foreign keys in other tables ?

[Matt McClure]

I'm relatively new to postgres and I've had a couple of questions for a
while now.  This post made me worry about them again:

 2. the server currently doesn't "reuse" deleted rows, but just keeps
appending them to the end.  running a straight VACUUM will perform a
de-fragmentation by essentially re-writing the database and then
performing equivalent to an 'ftruncate()' at the end to shrink the
table size back down again.  The only time you should have to do a full
VACUUM is after a massive amount of DELETEs to a table...and,
hopefully, the requirement for that will decrease over time too, as
there has been talk about adding in functionality to reuse delete
rows.. 

I started to make a database and I wanted to simulate foreign keys
somehow.  So I decided to simply insert the oid of a row in one table into
the "foreign key" column in another table.

For example,

create table concert (
day_of_show date,
venue text);

create table song (
song_name text,
author_fname text,
author_lname text);

create table concert_song (
concert_oid oid,
song_oid oid);

Then I have a perl script that does my inserts so that whenever I insert a
concert and the songs played, I take the appropriate row oids from concert
and song and insert them into concert_song.

You say that vacuum "re-writes" the database.  Does it alter row oids???
If so, my scheme completely corrupts my database whenever I do a vacuum,
since in concert and song the row oids would change, but my inserted
values would remain the same in concert_song, right?

If vacuum does not alter row oids, then I have another question.  How does
postgres re-use oids?  I've seen the numbers grow and grow, but despite
deletes, etc, I have never seen a lower oid get re-used.  How does this
work?

Thanks a bunch,
Matt

Re: [GENERAL] Nested tables

[Matt McClure]

On Fri, 24 Jul 1998, Ferruccio Zamuner wrote:

 CREATE TABLE foo (
  x  int2
 );
 
 CREATE TABLE bar (
  y  foo,
  z  int
 );
 
 I read on some Postgres mailing digest that I had to put the OID of instance
 of foo into y field of bar.
 
 So I tried.
 
 The problem is that when I submit the following query:
 
SELECT foo.x, bar.z WHERE foo.oid=bar.y;
 
 trying to catch int fields either from bar and from its instance of foo,
 Postgres replies that bar.y (of type foo) and foo.oid (of type oid) cannot be
 compared.
 I've also tried to cast them in many ways but the answer never changes!


You could declare bar.y to be of type oid instead of type foo.

Re: [GENERAL] returning oid of last insert?

[Matt McClure]

Thanks for the help.  I checked libpq, and just to follow up, it is quite
easy to get the oid of the last insert using the routine oidStatus. 

On Sun, 19 Jul 1998, Peter T Mount wrote:

 On Sat, 18 Jul 1998, Matt McClure wrote:
 
  Is there a way to return the oid of the last insert command?
 
 The protocol does return the oid, as a string. You can see this when using
 psql. I'm not sure what libpq function returns this value though.
 
 In JDBC, there isn't an official way, but the 6.4 driver does have a
 method to retrieve the oid (It's used internally with the new object
 serialization code). Earler versions store the string, but don't use it,
 and have no method to retrieve it. 
 
 
 -- 
 Peter T Mount [EMAIL PROTECTED] or [EMAIL PROTECTED]
 Main Homepage: http://www.retep.org.uk
  Someday I may rebuild this signature completely ;-) 
 Work Homepage: http://www.maidstone.gov.uk Work EMail: [EMAIL PROTECTED]