Thread: Request to share information regarding deadlock in postgresql-8.1.18

Dear All,

Thanks in advance.

In my Linux system, below error is found and after 10~12 hours, WAL file in pg_xlog directory are increased
continuously.
PostgreSQL:ERROR:  deadlock detected
DETAIL:  Process 12345 waits for AccessShareLock on relation 16446 of database 16385; blocked by process 23136.
Process 23136 waits for ShareLock on relation 16482 of database 16385; blocked by process 12345.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."table name" x WHERE "id" = $1 AND "table_id" = $2 FOR SHARE OF
x"

Is this deadlock error is link with WAL file of pg_xlog?
Please share your opinion. 

Regards,
Yogesh

On 11/13/2016 11:01 PM, Yogesh Sharma wrote:
> DETAIL:  Process 12345 waits for AccessShareLock on relation 16446 of database 16385; blocked by process 23136.
> Process 23136 waits for ShareLock on relation 16482 of database 16385; blocked by process 12345.

you have two processes that are each waiting for locks the other have.

process 12345 is waiting for relation (table) with OID 16446, that table
is blocked by process 23136

and process 23136 is waiting for a lock on another table, OID 16482,
which the first process has a lock on.

this is a logic error in your application.

you can look those relation numbers up in the pg_catalog to see what
they are.   you can see what the processes are in pg_stat_activity.

THis has nothing to do with the growing WAL logs...    something is
blocking checkpoints if a single WAL file keeps growing, are you using
some form of wal archiving, is that working correctly ? could something
be preventing checkpoints?    what are the related checkpoint and WAL
settings?



--
john r pierce, recycling bits in santa cruz

Dear John,

Thanks for sharing information.
>this is a logic error in your application.
Ok, I will check.

>you can look those relation numbers up in the pg_catalog to see what they are.   you can see what the processes are in
pg_stat_activity.
Currently machine is not available. Please suggest if any other approach  to identify the same.


>THis has nothing to do with the growing WAL logs...    something is blocking checkpoints if a single WAL file keeps
>growing,are you using some form of wal archiving, is that working correctly ? could something 
 
>be preventing checkpoints?    what are the related checkpoint and WAL settings?

Many WAL files are generated in pg_xlog directory. I don’t know how I can check checkpoint and WAL settings?
By default setting of postgresql.conf is used.

Regards,
Yogesh



-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of John R Pierce
Sent: Monday, November 14, 2016 4:19 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Request to share information regarding deadlock in postgresql-8.1.18

On 11/13/2016 11:01 PM, Yogesh Sharma wrote:
> DETAIL:  Process 12345 waits for AccessShareLock on relation 16446 of database 16385; blocked by process 23136.
> Process 23136 waits for ShareLock on relation 16482 of database 16385; blocked by process 12345.

you have two processes that are each waiting for locks the other have.

process 12345 is waiting for relation (table) with OID 16446, that table is blocked by process 23136

and process 23136 is waiting for a lock on another table, OID 16482, which the first process has a lock on.

this is a logic error in your application.

you can look those relation numbers up in the pg_catalog to see what 
they are.   you can see what the processes are in pg_stat_activity.

THis has nothing to do with the growing WAL logs...    something is 
blocking checkpoints if a single WAL file keeps growing, are you using some form of wal archiving, is that working
correctly? could something 
 
be preventing checkpoints?    what are the related checkpoint and WAL 
settings?



-- 
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

you can look those relation numbers up in the pg_catalog to see what they are.   you can see what the processes are in pg_stat_activity.

Currently machine is not available. Please suggest if any other approach  to identify the same.

without access to the system thats generating the error, there's nothing you can learn.  

>THis has nothing to do with the growing WAL logs...    something is blocking checkpoints if a single WAL file keeps >growing, are you using some form of wal archiving, is that working correctly ? could something 
>be preventing checkpoints?    what are the related checkpoint and WAL settings?

Many WAL files are generated in pg_xlog directory. I don’t know how I can check checkpoint and WAL settings?
By default setting of postgresql.conf is used.

I just noticed, you're talking about postgresql 8.1.18?    thats an unsupported and obsolete version from 2009.

A standalone postgres installation that hasn't been configured for WAL archiving will normally only generate up to 2*CHECKPOINT_SEGMENTS + 1 wal files of 16MB each (assuming all defaults, thats 7 files).    The obsolete 8.1 documentation on this is here, https://www.postgresql.org/docs/8.1/static/wal-configuration.html

If you've configured wal archiving, AND this isn't working correctly, WAL files can stack up indefinitely.  See https://www.postgresql.org/docs/8.1/static/backup-online.html

-- 
john r pierce, recycling bits in santa cruz