Re: [HACKERS] postgres inode q's

Bryan Ingram wrote:

> I apologize if this is the wrong group for this message, but I'm not
> sure where else this would go.

Topshot - absolutely the right group.

> I don't have a specific problem, but I would like to ask some questions
> about how postgres works.
>
> But first, some backfground info:
> I have two identical servers each running postgres 6.5.1 and each has an
> identical database called zipfind. This is a pretty static, mostly read
> ...

> It occurred to me that there may be some system tables which were
> causing the erratic behaviour, I searched for something relevant but
> found nothing.

Warm, warm, hot - missed!

> The only theory that I could come up with was that postgres latched on
> to an inode for the original files ..but how it would keep that inode
> info across daemon invocations seems a mystery to me.

Deep frozen :-)

I assume from this description, that one of the servers is
created more or less by a similar copy operation, but that
time it was the entire ./data directory that got copied, or
maybe the entire installation directory - right? If not, the
two installations must have been treated absolutely identical
until all the data was inserted into the zipfind databases.

Anyway, the system file causing this is pg_log. It's not a
table, it's a bitmap telling which transaction have committed
and which ones not. There are some transaction ID fields in
the header information of each data tuple in PostgreSQL. One
tells in which transaction this tuple appeared, and the other
when it disappeared. But they are ignored if the transaction
in question isn't marked as committed in pg_log. So on a
DELETE operation, the deleted tuples simply get the DELETE's
transaction ID stamped into the ending field, and on an
UPDATE, the same is done and a new tuple with this XID as the
beginning is appended at the end of the table. Can you
imagine now, what a ROLLBACK in PostgreSQL means? Simple -
eh? Just mark the transaction in pg_log as rolled back and
the stamps will get ignored. So the old tuple is still valid
and the new tuple at the end is ignored.

Vacuum now is the utility, that (dramatically simplified)
whipes out all the tuples with a committed XID in the ending
field and truncates the datafile.

Since you didn't copy pg_log (AND DON'T DO SO, IT WOULD
CORRUPT ALL DATABASES IN THE INSTALLATION) from PostgreSQL's
point of view all the UPDATES/INSERTS found in the copied
zipfind database files never committed, so the where ignored.

Either you copy the entire ./data directory, or you do it
with pg_dump. No other chance.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #