Re: slow, long-running 'commit prepared'

"John Smith" <sodgodofall(at)gmail(dot)com> writes:
> 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?

It was the number of locks I was speculating about. But that pattern
shouldn't result in more than 20 or so locks, so it's still not clear
what's happening.

In any case, a prepared xact is already holding all the locks it needs,
so COMMIT PREPARED shouldn't have to block on anything.

Have you tried watching the committing process with top, vmstat,
"select * from pg_stat_activity", etc? That should at least give you a
clue whether it's CPU-bound or IO-bound or (against the above theory)
blocked on something.

regards, tom lane