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 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Johnson | 1998-07-28 15:27:49 | Re: [GENERAL] How to know when to vacuum |
Previous Message | The Hermit Hacker | 1998-07-28 14:59:05 | Re: [GENERAL] How to know when to vacuum |