Query regarding deadlock

On 11/24/2016 5:44 PM, Yogesh Sharma wrote:

I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron script.

any cron scripts are your own doing, so this statement makes no sense at
all.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Dear John,

Thanks for your support.
I mean to say, the REINDEX calls hourly and insert query executes every minute to update.
So, it might be race condition that these queries can call at same time.

If there is any solution like we can add some check before REINDEX operation performed.
If it is possible?

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Friday, November 25, 2016 10:55 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query regarding deadlock

On 11/24/2016 5:44 PM, Yogesh Sharma wrote:

I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron script.

any cron scripts are your own doing, so this statement makes no sense at all.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 25 Nov 2016, 9:45 a.m. Yogesh Sharma, <
Yogesh1.Sharma@nectechnologies.in> wrote:

Dear All,

Thanks in advance.
I found below deadlock in postgresql logs.
I cannot change calling of REINDEX and insert query sequence because it is
execute automatically through some cron script.

Does this mean that you reindex quite often based on a schedule. Personally
I don't prefer that. To me it is like you are trying to fix something that
is not broken.

Ideally reindex only what needs to be reindexed. I would not want to
reindex a table in OLTP env.

ERROR: deadlock detected
DETAIL: Process 2234 waits for AccessShareLock on relation 16459 of
database 16385; blocked by process 4111.
Process 4111 waits for ShareLock on relation 16502 of database 16385;
blocked by process 2234.
Process 2234: INSERT INTO table1 ( id , unique_id )VALUES( '1', '4')
Process 4111: REINDEX TABLE table1

Could you please provide any solution to resolve this deadlock.

Regards,
Yogesh

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

--

Best Regards,

*Sameer Kumar | DB Solution Architect*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

T: +65 6438 3504 | www.ashnik.com

Skype: sameer.ashnik | T: +65 8110 0350

[image: www.ashnik.com] <http://www.ashnik.com/&gt;​

On 2016-11-24 9:06 PM, Yogesh Sharma wrote:

Dear John,

Thanks for your support.
I mean to say, the REINDEX calls hourly and insert query executes every minute to update.
So, it might be race condition that these queries can call at same time.

Why do you need to run REINDEX every hour? That sounds like a stopgap
solution for another problem.

If there is any solution like we can add some check before REINDEX operation performed.
If it is possible?

Try to find out why the cron script is there in the first place. Then go
from there - eliminate it, or do it only in down periods.

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Friday, November 25, 2016 10:55 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query regarding deadlock

On 11/24/2016 5:44 PM, Yogesh Sharma wrote:

I cannot change calling of REINDEX and insert query sequence because it is execute automatically through some cron script.

any cron scripts are your own doing, so this statement makes no sense at all.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, Nov 25, 2016 at 11:14 AM, Sameer Kumar <sameer.kumar@ashnik.com> wrote:

Does this mean that you reindex quite often based on a schedule. Personally I don't prefer that. To me it is like you are trying to fix something that is not broken.

Ideally reindex only what needs to be reindexed. I would not want to reindex a table in OLTP env.

Like VACUUM FULL, don't forget that REINDEX needs an exclusive lock
when working. As that's very intrusive, usually you take measures on
your database to be sure that you *never* require it, particularly if
this is a production instance.
--
Michael

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Fri, 25 Nov 2016, 10:07 a.m. Yogesh Sharma, <
Yogesh1.Sharma@nectechnologies.in> wrote:

Dear John,

Thanks for your support.
I mean to say, the REINDEX calls hourly and insert query executes every
minute to update.
So, it might be race condition that these queries can call at same time.

Reindex every hour? How did you end up with that. Looks like you got
another problem while fixing one with a dirty solution. Why do you need to
reindex every hour?

If there is any solution like we can add some check before REINDEX
operation performed.
If it is possible?

Regards,
Yogesh

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:
pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Friday, November 25, 2016 10:55 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Query regarding deadlock

On 11/24/2016 5:44 PM, Yogesh Sharma wrote:

I cannot change calling of REINDEX and insert query sequence because it

is execute automatically through some cron script.

any cron scripts are your own doing, so this statement makes no sense at
all.

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

--

Best Regards,

*Sameer Kumar | DB Solution Architect*

*ASHNIK PTE. LTD.*

101 Cecil Street, #11-11 Tong Eng Building, Singapore 069533

T: +65 6438 3504 | www.ashnik.com

Skype: sameer.ashnik | T: +65 8110 0350

[image: www.ashnik.com] <http://www.ashnik.com/&gt;​

Yogesh Sharma <Yogesh1.Sharma@nectechnologies.in> writes:

ERROR: deadlock detected
DETAIL: Process 2234 waits for AccessShareLock on relation 16459 of database 16385; blocked by process 4111.
Process 4111 waits for ShareLock on relation 16502 of database 16385; blocked by process 2234.
Process 2234: INSERT INTO table1 ( id , unique_id )VALUES( '1', '4')
Process 4111: REINDEX TABLE table1

Offhand I would not expect those two commands to deadlock in isolation.
Are they parts of larger transactions that take additional locks?

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Thu, Nov 24, 2016 at 5:44 PM, Yogesh Sharma <
Yogesh1.Sharma@nectechnologies.in> wrote:

Dear All,

Thanks in advance.
I found below deadlock in postgresql logs.
I cannot change calling of REINDEX and insert query sequence because it is
execute automatically through some cron script.

ERROR: deadlock detected
DETAIL: Process 2234 waits for AccessShareLock on relation 16459 of
database 16385; blocked by process 4111.
Process 4111 waits for ShareLock on relation 16502 of database 16385;
blocked by process 2234.
Process 2234: INSERT INTO table1 ( id , unique_id )VALUES( '1', '4')
Process 4111: REINDEX TABLE table1

Could you please provide any solution to resolve this deadlock.

What are tables 16459 and 16502? Are they related to each other through
triggers or FK constraints?

Are you reindexing multiple tables in the same transaction? If not, I
don't see why these should deadlock. One should win, and the other should
block.

If you are reindexing multiple tables in the same transaction, why are you
doing that? I can't think of a situation where you couldn't use separate
transactions per table.

Cheers,

Jeff