Re: [GENERAL] Request to share information regarding deadlock in postgresql-8.1.18
On 11/13/2016 11:52 PM, Yogesh Sharma wrote: 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
Re: [GENERAL] Request to share information regarding deadlock in postgresql-8.1.18
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-ow...@postgresql.org [mailto:pgsql-general-ow...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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
[GENERAL] 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
