Re: pg_log - Mailing list pgsql-general

From Tom Lane
Subject Re: pg_log
Date
Msg-id 15844.979707281@sss.pgh.pa.us
Whole thread Raw
In response to Re: pg_log  (Joseph Shraibman <jks@selectacast.net>)
List pgsql-general
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

pgsql-general by date:

Previous
From: hafiz
Date:
Subject: postgresql.conf ignored
Next
From: "Brett W. McCoy"
Date:
Subject: Re: pg_hba.conf edit