Subject: vacuum problem & oid's

From: Gerry Stringer <[email-masked]>
Date: Fri, 15 Nov 91 04:21:51 -0800

I've a problem with the vacuum cleaner blowing a fuse:

schema:

create cs_pobject(id = int4 ,pobtype = int4 ,username = char[10],
timestamp = abstime)
define index pobindex on cs_pobject using btree (id int4_ops)
purge cs_pobject 

actions:

append cs_pobject(id=1234,pobtype=5,username="gerry",timestamp="now")
delete cs_pobject

vacuum $PGDATABASE

this crashes the backend.

1. Is this is known problem? Are there any trace flags I can put on 
to debug futher?

2. Do the UNIX files ever decrease in size after vacuuming (or any 
other time)? When do pages become reused?

On a totally different subject, might it be useful to have an option
on copy to allow it output the oid along with all the other attributes?
I'm thinking ahead to where I might want to dump out all the data and
then reload. Any references to oid's would have to be replaced by their
new value hence I need to be able to build an old/new mapping. 

This may be a major undertaking as the DB gets bigger. Is it wise
to use system generated oid's as foreign keys?

	Many thanks,

	      gerry

Postgres v3.0 on SPARC & SUN C++ v2.0

+--------------------------------+---------------------------------+
|Gerry Stringer                  |Phone: +44 954 780926            |
|Cambridge Scanning Company Ltd  |Fax:   +44 954 789829            |
|Saxon Way                       |UUCP : [email-masked]       |
|Bar Hill                        |       [email-masked]        |
|Cambridge CB3 8SL               |       			   |
|United Kingdom                  |                                 |
+--------------------------------+---------------------------------+

---

From: postarch (Postgres Mailing Archive)
Date: Fri, 15 Nov 91 17:11:50 PST

> 1. Is this is known problem? Are there any trace flags I can put on 

I have been able to reproduce the problem and our resident vacuum cleaner
expert says he will look into it.

> other time)? When do pages become reused?

No, currently the vacuum cleaner doesn't pack pages so file sizes won't
decrease. Reusing the holes left after vacuuming requires the yet 
unimplemented clustering command.

> new value hence I need to be able to build an old/new mapping. 

you can do:

	retrieve into temp (oidval = emp.oid, emp.all)
	copy emp to "/usr/foo/emp.data"

> to use system generated oid's as foreign keys?

there needs to be a general mechanism for doing oid mapping on copy in
to make this feasible for large databases.

Jeff Meredith
[email-masked]