Subject: how to clean up pg_log, pg_time and pg_variable?

From: [email-masked]
Date: Mon, 06 Jun 94 17:39:03 +0800

I discover that the size of $postgres/data/pg_log, pg_time and
pg_variable grow very large. Is there any command to clean up or reset these files ?

Thanks.

Alan S. H. Lam
Department of Information Engineering, CUHK, Hong Kong
E-mail: [email-masked] 
Tel: (852) 609 8364 Fax: (852) 603 5032

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 06 Jun 94 02:57:46 -0700

[email-masked] writes:
> pg_variable grow very large.

pg_variable should never grow beyond one disk page (8k).

with respect to the other two, pg_log grows at 2 bits (.25 bytes) per
transaction and pg_time grows at 4 bytes per transaction.

> Is there any command to clean up or reset these files ?

what pg_log and pg_time store are the commit status and commit time of
each transaction.  in theory, this could be compressed by something
like run-length encoding (since most transactions commit) or truncated
after vacuuming (if you could somehow arrange to vacuum the entire
installation..).  in fact, they just grow.

it would be a bad idea to remove them or truncate them.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: [email-masked]
Date: Mon, 06 Jun 94 18:14:43 +0800

>it would be a bad idea to remove them or truncate them.

However, the pg_time  eat up 40M disk space and pg_log also ~20M
something. Is there any tools to compress this files or expire some of
the information in these log files?

Thanks.

Alan S. H. Lam

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 06 Jun 94 03:28:38 -0700

[email-masked] writes:
> Is there any tools to compress this files or expire some of

sorry for being long-winded :-)  no.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki

---

From: [email-masked]
Date: Tue, 07 Jun 94 10:09:28 +0800

> sorry for being long-winded :-)  no.

Then I want to do what is the consequence if I accidentally delete these files?

Alan S. H. Lam

---

From: [email-masked] (Paul M. Aoki)
Date: Mon, 06 Jun 94 19:21:15 -0700

> Then I want to do what is the consequence if I accidentally delete these files?

any tuples that have not been processed by the "vacuum" command become
invisible/"uncommitted", as if they had been rolled back.

the reason is that until the vacuum cleaner processes a newly-
inserted tuple, the system has to consult pg_time and pg_log to see
if/when its transaction committed.  if a tuple has not been vacuumed
*and* there is no commit info (because the files were deleted) then
the system assumes the transaction aborted and that the tuple should
be ignored.

an anonymous hacker points out that if you are super-crafty you can
replace log/time pages that correspond to transactions for which *all*
tuples in *all* databases are guaranteed to have been vacuumed with
zero-filled pages (i.e., create a file with "holes," like those
created by dbm) since these do not take up actual file system space.
this is a nifty, tricky way to do the truncation i was talking about
before.  we don't currently have a tool to do this.
--
  Paul M. Aoki  |  CS Div., Dept. of EECS, UCB  |  [email-masked]
                |  Berkeley, CA 94720           |  ...!uunet!ucbvax!aoki