pg_log

"Andrew W. Nosenko" wrote:

Question: what is logged in pg_log table and how I can cleanup it? (And
is this possible at all?)

Don't touch it !!!

It records the status of all your tuples. If you wipe it, you destroy
your data.

Developers:
I've seen this question several times; wouldn't it be better to rename
pg_log to pg_tuplestatus? Calling something ...log is asking for it
to be wiped.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Jesus said unto her, I am the resurrection, and the
life; he that believeth in me, though he were dead,
yet shall he live" John 11:25

"Andrew W. Nosenko" <awn@bcs.zp.ua> writes:

Question: what is logged in pg_log table and how I can cleanup it?

Transaction commit-status bits, and don't even *think* of touching it.
Easiest way to hose your database that I know of :-(

If it gets big enough to be really annoying, you can
dumpall/initdb/reload. There is no shortcut.

regards, tom lane

"Oliver Elphick" <olly@lfix.co.uk> writes:

Developers:
I've seen this question several times; wouldn't it be better to rename
pg_log to pg_tuplestatus? Calling something ...log is asking for it
to be wiped.

In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually)
which should reduce the tendency for DBAs to assume there's nothing
important in it. I regard this as one of the major advantages of
having switched to numeric filenames ;-)

regards, tom lane

Tom Lane wrote:

"Oliver Elphick" <olly@lfix.co.uk> writes:

Developers:
I've seen this question several times; wouldn't it be better to rename
pg_log to pg_tuplestatus? Calling something ...log is asking for it
to be wiped.

In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually)
which should reduce the tendency for DBAs to assume there's nothing
important in it. I regard this as one of the major advantages of
having switched to numeric filenames ;-)

What are the others? Is the point of moving to numeric filenames to
discourage people mucking with the files? With the current system for
example if a backend coredumps I know to look for it in the directory of
the database. But how will I know what that is in the future?

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com

In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually)
which should reduce the tendency for DBAs to assume there's nothing
important in it. I regard this as one of the major advantages of
having switched to numeric filenames ;-)

What are the others? Is the point of moving to numeric filenames to
discourage people mucking with the files? With the current system for
example if a backend coredumps I know to look for it in the directory of
the database. But how will I know what that is in the future?

I am planning to write a utility to map file name/number to database
objects, hopefully for 7.1.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Joseph Shraibman <jks@selectacast.net> writes:

Tom Lane wrote:

In 7.1 it'll have a numeric filename ($PGDATA/global/1269, actually)
which should reduce the tendency for DBAs to assume there's nothing
important in it. I regard this as one of the major advantages of
having switched to numeric filenames ;-)

What are the others? Is the point of moving to numeric filenames to
discourage people mucking with the files?

Er, you did recognize that as a joke, right son?

The point of using OID-based filenames is to support rollback of
create/drop/rename table commands. Consider

create table foo (f1 int);
<< load foo with a bunch o' data >>

begin;
drop table foo;
create table foo (bar text, baz numeric);
<< load foo with a bunch o' data >>
rollback;

Under the 7.0-and-before file naming convention, it's almost impossible
to cope with this. 7.0 deletes the file $PGDATA/base/yourdb/foo as soon
as it sees the DROP TABLE, and at that point you're already screwed for
rollback, never mind the subsequent CREATE of a completely different
table also named foo. You could try postponing the physical delete of
foo until commit, but that still fails if the transaction rewrites foo
with different data as above.

If the physical file names are based on unique, never-reassigned OIDs,
then all these problems go away at a stroke. 7.1 does indeed support
rollback of drop and rename table, and the reason is that it doesn't
have to rename the physical files under the hood.

With the current system for
example if a backend coredumps I know to look for it in the directory of
the database. But how will I know what that is in the future?

'core' is still 'core' ;-). Beyond that, the OID and name columns of
pg_database and each database's pg_class give you the mapping. Bruce
is threatening to write some utilities to make the mapping more readily
available to a DBA, but that's not done yet.

regards, tom lane

* Tom Lane <tgl@sss.pgh.pa.us> [010116 16:38] wrote:

"Andrew W. Nosenko" <awn@bcs.zp.ua> writes:

Question: what is logged in pg_log table and how I can cleanup it?

Transaction commit-status bits, and don't even *think* of touching it.
Easiest way to hose your database that I know of :-(

If it gets big enough to be really annoying, you can
dumpall/initdb/reload. There is no shortcut.

My pg_log file is about 3MB on one server and 5MB on another. If
3-6MB is a "pretty large" size for pg_log and people are deleting
it, perhaps a name change is in order?

pg_commitstatus? or something?

I think pg_'log' screams 'delete me please' to um... "power users"

:)

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Alfred Perlstein wrote:
: * Tom Lane <tgl@sss.pgh.pa.us> [010116 16:38] wrote:
: > "Andrew W. Nosenko" <awn@bcs.zp.ua> writes:
: > > Question: what is logged in pg_log table and how I can cleanup it?
: >
: > Transaction commit-status bits, and don't even *think* of touching it.
: > Easiest way to hose your database that I know of :-(
: >
: > If it gets big enough to be really annoying, you can
: > dumpall/initdb/reload. There is no shortcut.

Thanks to Tom for answer, this is exactly what I want to know.

:
: My pg_log file is about 3MB on one server and 5MB on another. If
: 3-6MB is a "pretty large" size for pg_log and people are deleting
: it, perhaps a name change is in order?
:
: pg_commitstatus? or something?
:
: I think pg_'log' screams 'delete me please' to um... "power users"
:
: :)
:

Sorry for my English, when I say `cleanup' I don't say about deleting
file nor about any other changes directly/"in evade"/without PostrgreSQL
backend. But simple about `is this file/table/etc have data what never
be used anymore (like deleted tuples in regular tables)?' and if this
sort of data exist then `is exist some analog of VACUUM that can return
this space into filesystem (decrease size of file)?'

--
Andrew W. Nosenko (awn@bcs.zp.ua)

* Andrew W. Nosenko <awn@bcs.zp.ua> [010117 01:16] wrote:

Alfred Perlstein wrote:
: * Tom Lane <tgl@sss.pgh.pa.us> [010116 16:38] wrote:
: > "Andrew W. Nosenko" <awn@bcs.zp.ua> writes:
: > > Question: what is logged in pg_log table and how I can cleanup it?
: >
: > Transaction commit-status bits, and don't even *think* of touching it.
: > Easiest way to hose your database that I know of :-(
: >
: > If it gets big enough to be really annoying, you can
: > dumpall/initdb/reload. There is no shortcut.

Thanks to Tom for answer, this is exactly what I want to know.

:
: My pg_log file is about 3MB on one server and 5MB on another. If
: 3-6MB is a "pretty large" size for pg_log and people are deleting
: it, perhaps a name change is in order?
:
: pg_commitstatus? or something?
:
: I think pg_'log' screams 'delete me please' to um... "power users"
:
: :)
:

Sorry for my English, when I say `cleanup' I don't say about deleting
file nor about any other changes directly/"in evade"/without PostrgreSQL
backend. But simple about `is this file/table/etc have data what never
be used anymore (like deleted tuples in regular tables)?' and if this
sort of data exist then `is exist some analog of VACUUM that can return
this space into filesystem (decrease size of file)?'

We get a lot of "I deleted pg_log and now everything is broke" emails
on this list.

Just curious, how large is this file on your system?

--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]
"I have the heart of a child; I keep it in a jar on my desk."

Tom Lane wrote:

'core' is still 'core' ;-). Beyond that, the OID and name columns of
pg_database and each database's pg_class give you the mapping. Bruce
is threatening to write some utilities to make the mapping more readily
available to a DBA, but that's not done yet.

regards, tom lane

Simple: symlink made out a table name pointing to a file with a number
name. Won't work with windows, but simple on unix.

--
Joseph Shraibman
jks@selectacast.net
Increase signal to noise ratio. http://www.targabot.com