Re: [HACKERS] postgres inode q's - Mailing list pgsql-hackers

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@debis.com (Jan Wieck) #