Thread: How to prevent vacuum and reindex from deadlocking.
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. Help? Rob -- 14:38:15 up 9 days, 7:23, 4 users, load average: 1.09, 1.10, 0.86
Attachment
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
Attachment
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
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
Attachment
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
Attachment
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
Attachment
Postgres itself doesn't support nested transactions. Robert Creager wrote: > 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 >
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
Attachment
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
Attachment
In general, the documentation is not direct. When it is, it often is using database theory language, masters level databaseengineer language. 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 >