How to prevent vacuum and reindex from deadlocking.

On Sun, 10 Aug 2003 14:50:10 -0600
Robert Creager <Robert_Creager@LogicalChaos.org> said something like:

I'm running 7.4Beta1 with pg_autovacuum. In one of my operations
which is executed frequently, a REINDEX is done after a COPY. Well,
VACUUM's are being executed by pg_autovacuum, and my app is loosing
the deadlock.

Is there a way I can prevent this from happening? I can obviously
acquire a LOCK before doing the REINDEX (which one?), but it looks
like this should be happening anyway by both the VACUUM and REINDEX
statements.

Opps, if it helps, the log of the deadlock:

Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected

Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by
proc 18815.

Aug 10 14:19:36 thunder postgres[18735]: [2-3] Proc 18815 waits for
AccessExclusiveLock on relation 18101 of database 17140; blocked by proc
18735.

--
14:55:41 up 9 days, 7:41, 4 users, load average: 1.56, 1.27, 1.08

Robert Creager <Robert_Creager@LogicalChaos.org> writes:

Opps, if it helps, the log of the deadlock:

Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected

Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by
proc 18815.

Aug 10 14:19:36 thunder postgres[18735]: [2-3] Proc 18815 waits for
AccessExclusiveLock on relation 18101 of database 17140; blocked by proc
18735.

What tables do the two referenced OIDs correspond to? Also, which
process was doing what, exactly?

regards, tom lane

On Mon, 11 Aug 2003 10:11:37 -0400
Tom Lane <tgl@sss.pgh.pa.us> said something like:

Robert Creager <Robert_Creager@LogicalChaos.org> writes:

Opps, if it helps, the log of the deadlock:

Aug 10 14:19:36 thunder postgres[18735]: [2-1] ERROR: deadlock detected

Aug 10 14:19:36 thunder postgres[18735]: [2-2] DETAIL: Proc 18735 waits
for AccessExclusiveLock on relation 18028 of database 17140; blocked by
proc 18815.

Aug 10 14:19:36 thunder postgres[18735]: [2-3] Proc 18815 waits for
AccessExclusiveLock on relation 18101 of database 17140; blocked by proc
18735.

What tables do the two referenced OIDs correspond to? Also, which
process was doing what, exactly?

18028 is table temp_obs_i
18101 is index temp_obs_i_loc_index

So, my guess is that 18735 is the vacuum process (likely vacuum analyze, driven
from pg_autovacuum), and 188735 is a 'REINDEX INDEX temp_obs_i_loc_index'.

Cheers,
Rob

PS. Please keep CC'ing me, as I'm trying to get re-subscribed to the lists. It
looks like I was dropped...

--
08:25:16 up 10 days, 1:10, 4 users, load average: 3.44, 3.49, 3.44

Robert Creager <Robert_Creager@LogicalChaos.org> writes:

So, my guess is that 18735 is the vacuum process (likely vacuum analyze, driven
from pg_autovacuum), and 188735 is a 'REINDEX INDEX temp_obs_i_loc_index'.

Can you use a "REINDEX TABLE" instead? REINDEX INDEX is problematic
since it first finds/locks the index and then has to find/lock the
table. Everything else (except perhaps DROP INDEX) goes the other way.

If you really want to rebuild only the one index, I think this will work:

begin;
lock table tab;
reindex index ndx;
commit;

I don't see a good system-level solution to this other than changing the
REINDEX syntax to include the table name (cf. CLUSTER).

regards, tom lane

On Mon, 11 Aug 2003 11:05:57 -0400
Tom Lane <tgl@sss.pgh.pa.us> said something like:

Robert Creager <Robert_Creager@LogicalChaos.org> writes:

So, my guess is that 18735 is the vacuum process (likely vacuum analyze,
driven from pg_autovacuum), and 188735 is a 'REINDEX INDEX
temp_obs_i_loc_index'.

Can you use a "REINDEX TABLE" instead? REINDEX INDEX is problematic
since it first finds/locks the index and then has to find/lock the
table. Everything else (except perhaps DROP INDEX) goes the other way.

I'll try. There are other indexes on the table. I'll see what the performance
does.

If you really want to rebuild only the one index, I think this will work:

begin;
lock table tab;
reindex index ndx;
commit;

Duh. I quickly glanced at the docs, and saw the warning on the nested
transaction, and thought that wasn't supported. I remember that it is
supported now...

Cheers,
Rob

--
09:38:17 up 10 days, 2:23, 4 users, load average: 2.13, 2.90, 3.18

On Mon, 11 Aug 2003 11:05:57 -0400
Tom Lane <tgl@sss.pgh.pa.us> said something like:

If you really want to rebuild only the one index, I think this will work:

begin;
lock table tab;
reindex index ndx;
commit;

Figures. It appears that DBD::Pg doesn't supported nested transactions (I was
already in a transaction).

DBD::Pg::db begin_work failed: Already in a transaction at
/tass/bin/importSList.pl line 445.

Unfortunatly, it does take longer overall because of the second index on the
table, but it always works ;-) 'Nother duh moment, delete the second index,
and then it works just fine speed wise. Guess I need to re-examine my index
usage...

Thanks,
Rob

--
21:16:30 up 10 days, 14:01, 4 users, load average: 3.37, 3.34, 3.19

Postgres itself doesn't support nested transactions.

Robert Creager wrote:

On Tue, 12 Aug 2003 08:13:59 -0700
Dennis Gearon <gearond@cvc.net> said something like:

Postgres itself doesn't support nested transactions.

Yea, I just convinced myself of that. The first time I read the docs, that's
what I thought. Then I convinced myself when I re-read them that it was just a
warning, and a new transaction was started. But I just tried it, and they're
not...

Thanks for point that out to me. I dare say that the BEGIN documentation is
unclear to me, even knowing what I know now. Why not just explictly say "Nested
transactions are not supported", rather than "... The current transaction is
not affected". Alright, maybe I still read at Junior High level.

Cheers,
Rob

--
21:59:11 up 11 days, 14:43, 4 users, load average: 2.17, 2.10, 2.02

On Tue, Aug 12, 2003 at 10:06:04PM -0600, Robert Creager wrote:

Thanks for point that out to me. I dare say that the BEGIN documentation is
unclear to me, even knowing what I know now. Why not just explictly say "Nested
transactions are not supported", rather than "... The current transaction is
not affected". Alright, maybe I still read at Junior High level.

You have to keep in mind that inside PL/pgSQL, the "BEGIN" keyword has a
different meaning, unrelated to the BEGIN keyword in SQL. In PL/pgSQL,
BEGIN starts a code block, and has nothing to do at all with
transactions.

(I _think_ the original question had something to do with a PL/pgSQL
function -- if this is not the case, please ignore.)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La espina, desde que nace, ya pincha" (Proverbio africano)

On Wed, 13 Aug 2003 01:45:07 -0400
Alvaro Herrera <alvherre@dcc.uchile.cl> said something like:

You have to keep in mind that inside PL/pgSQL, the "BEGIN" keyword has a
different meaning, unrelated to the BEGIN keyword in SQL. In PL/pgSQL,
BEGIN starts a code block, and has nothing to do at all with
transactions.

(I _think_ the original question had something to do with a PL/pgSQL
function -- if this is not the case, please ignore.)

Na, it was Tom's suggestion of using a begin/lock table/reindex/commit to
resolve my deadlock problem. Unfortunately, I was already within a transaction.

Thanks,
Rob

--
07:26:01 up 12 days, 10 min, 4 users, load average: 5.38, 4.25, 3.74

In general, the documentation is not direct. When it is, it often is using database theory language, masters level database engineer language.

Robert Creager wrote: