Skip site navigation (1) Skip section navigation (2)

row oids as "foreign keys" in other tables ?

From: Matt McClure <matthew(dot)mcclure(at)yale(dot)edu>
To: The Hermit Hacker <scrappy(at)hub(dot)org>
Cc: pgsql-general(at)postgreSQL(dot)org
Subject: row oids as "foreign keys" in other tables ?
Date: 1998-07-28 15:22:58
Message-ID: Pine.GSO.3.94.980728110744.1128E-100000@mercury.cis.yale.edu (view raw or flat)
Thread:
Lists: pgsql-general
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


In response to

Responses

pgsql-general by date

Next:From: Chris JohnsonDate: 1998-07-28 15:27:49
Subject: Re: [GENERAL] How to know when to vacuum
Previous:From: The Hermit HackerDate: 1998-07-28 14:59:05
Subject: Re: [GENERAL] How to know when to vacuum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group