Re: slow, long-running 'commit prepared'

The transaction where COMMIT PREPARED was slow basically did the
following 20 times:
- create a child table T1 as select from another child table T2
- drops child table T2
- renames T1 to T2

What here would cause the 2PC state file to grow large? In my rough
experiments, its size seems constant in the number of row locks held,
and linear in the number of table locks held. Is there any state in
that file that grows linearly with the size of the data touched in the
transaction?

On Wed, Nov 26, 2008 at 5:52 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "John Smith" <sodgodofall(at)gmail(dot)com> writes:
>> I have a pg instance with 700GB of data, almost all of which is in one
>> table. When I PREPARE and then COMMIT PREPARED a transaction that
>> reads & writes to a large fraction of that data (about 10%,
>> effectively randomly chosen rows and so every file in the table is
>> modified), the COMMIT PREPARED sometimes takes a very long time--2 to
>> 5 minutes. Is this expected?
>
> It's impossible to say without knowing more about what the transaction
> did. But one piece of data you could check easily is the size of the
> 2PC state file (look into $PGDATA/pg_twophase/).
>
> regards, tom lane
>