pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Palle Girgensohn <girgen@partitur.se>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Patrik Kudo <kudo@partitur.se>
Cc: pgsql-sql@postgreSQL.org <pgsql-sql@postgreSQL.org>
Subject: Re: [SQL] Duplicate tuples with unique index
Date: Wed, 26 Jan 2000 00:38:43 -0500
Message-ID: <16091.948865123@sss.pgh.pa.us> (raw)
In-Reply-To: <388E41AD.E94D52A5@partitur.se>
References: <200001260033.TAA04733@candle.pha.pa.us>
	<388E41AD.E94D52A5@partitur.se>

Palle Girgensohn <girgen@partitur.se> writes:
>>>> Nope. pg_upgrade was "disabled in this release because the
>>>> internal blahblahblah disk layout changed from previous versions".
>> 
>> Sorry, you have to edit the script to reenable it.

> That's OK. I didn't bother to check. the dump/restore sequence was pretty quick.
> The layout hasn't really changed since 6.5, right?

No; the disk layout is the same.  There is room for trouble nonetheless.
There is some doubt about whether pg_upgrade will work right under MVCC
semantics: in MVCC, whether a tuple stored in a table is considered
valid or not will depend on whether its creator transaction is marked
committed in pg_log (and, if it is marked with a deletor transaction,
whether the deletor is NOT committed).

pg_upgrade tries to deal with this by copying the old database
installation's pg_log into the new.  OK, that ensures that all the
user-table tuples preserve their commit state; but what about tuples
in the system tables?  The idea behind pg_upgrade is to paste together
a set of user tables with a set of system tables --- but unless the
transaction number history of the user tables is exactly the same as
the transaction number history of the system tables, there is clearly
a risk that committed tuples will suddenly be considered not-committed
or vice versa.  And ordinarily those histories will *not* be identical.

The only reason pg_upgrade has any chance at all of working is that
for efficiency reasons we don't want to go back and consult pg_log
for every single tuple we read.  So, there are really six states of
a tuple on disk, which may be described as:
	1. I was created by transaction N, but I dunno if it committed
	2. I was created by a transaction that definitely committed,
	   so I'm good; no need to look at pg_log
	3. I was created by a transaction that definitely aborted,
	   so I'm dead; no need to look at pg_log
	4. I was deleted by transaction N, but I dunno if it committed
	5. I was deleted by a transaction that definitely committed,
	   so I'm dead; no need to look at pg_log
	6. I was deleted by a transaction that definitely aborted,
	   so I'm still good; no need to look at pg_log
(I'm fuzzing over some fine points that arise when multiple transactions
try to delete the same tuple, but this level of detail will do for now.)
Whenever any backend examines a tuple in state 1 or 4, it will consult
pg_log to discover the state of the source transaction.  If the source
transaction has now committed or aborted, the tuple will be rewritten
with the new state (2,3,5,6 as appropriate) so that future readers of
the tuple don't have to look at pg_log again.

Now states 2,3,5,6 do not depend on what pg_log says.  Therefore,
pg_upgrade's copy of an old pg_log file into a new database will
work *if and only if* all the tuples in the system tables are in
one of these states, and none of them are in states 1 or 4.  We
can survive with user-table tuples that are in the uncertain states,
because the associated pg_log info will be copied over with them.
We cannot afford to have any uncertainly-committed system-table
tuples, because we will overwrite the pg_log data about their status.

Bruce thinks that the pg_upgrade script will ensure that the system-
table tuples are all in frozen states (by VACUUMing them).  I don't
trust it worth a dime, myself.  Maybe it will work, but it hasn't been
proven in the field.  So, if you'd like to try it, by all means do so
--- but make a pg_dump backup first!  And let us know whether you have
problems or not!

			regards, tom lane



view thread (13+ messages)  latest in thread

Message-ID: <16091.948865123@sss.pgh.pa.us>
Permalink:  ../16091.948865123@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/16091.948865123@sss.pgh.pa.us

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, girgen@partitur.se, pgman@candle.pha.pa.us, kudo@partitur.se
  Subject: Re: [SQL] Duplicate tuples with unique index
  In-Reply-To: <16091.948865123@sss.pgh.pa.us>
  References: <200001260033.TAA04733@candle.pha.pa.us> <388E41AD.E94D52A5@partitur.se> <16091.948865123@sss.pgh.pa.us>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox