Re: [GENERAL] Deadlock with single update statement?

Justin Pryzby wrote: > detail|Process 26871 waits for ShareLock on transaction 13693505; blocked by > process 26646. > Process 26646 waits for ShareLock on transaction 13693504; blocked by process > 26871. > Process 26871: SELECT db_column_name,table_name FROM > huawei_m2000_counter_details ORD

Re: [GENERAL] Deadlock with single update statement?

On Sat, Jun 10, 2017 at 03:16:26PM -0400, Tom Lane wrote: > Rob Nikander writes: > >> On Jun 10, 2017, at 10:34 AM, Tom Lane wrote: > >> […] but it'd be better to adjust the query to ensure a deterministic > >> update order. > > > Thank you for the answer. Since `update` has no `order by` clause

Re: [GENERAL] Deadlock with single update statement?

Rob Nikander writes: >> On Jun 10, 2017, at 10:34 AM, Tom Lane wrote: >> […] but it'd be better to adjust the query to ensure a deterministic >> update order. > Thank you for the answer. Since `update` has no `order by` clause, I’m > guessing there’s no way to do this with just the `update` sta

Re: [GENERAL] Deadlock with single update statement?

> On Jun 10, 2017, at 10:34 AM, Tom Lane wrote: > […] but it'd be better to adjust the query to ensure a deterministic > update order. Thank you for the answer. Since `update` has no `order by` clause, I’m guessing there’s no way to do this with just the `update` statement, and that I should u

Re: [GENERAL] Deadlock with single update statement?

Rob Nikander writes: > I’m trying to track down a deadlock happening in a live app. I’m wondering > about statements like this, which select more than one row to update: > update t set num = 1 where name = ‘foo’; > It appears to be causing a deadlock, but I can’t reproduce it on my test > d

[GENERAL] Deadlock with single update statement?

Hi, I’m trying to track down a deadlock happening in a live app. I’m wondering about statements like this, which select more than one row to update: update t set num = 1 where name = ‘foo’; It appears to be causing a deadlock, but I can’t reproduce it on my test database. Could two thread

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 10:25 AM, Tom Lane wrote: Steve Clark writes: On 10/28/2016 09:48 AM, Tom Lane wrote: Retrying might be a usable band-aid, but really this is an application logic error. The code that is trying to do "lock table t_unit in exclusive mode" must already hold some lower-level lock o

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

Steve Clark writes: > On 10/28/2016 09:48 AM, Tom Lane wrote: >> Retrying might be a usable band-aid, but really this is an application >> logic error. The code that is trying to do "lock table t_unit in >> exclusive mode" must already hold some lower-level lock on t_unit, which >> is blocking wh

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 09:48 AM, Tom Lane wrote: Steve Clark writes: No. But I examined the pg_log/log_file and saw an error indicating it was autovacuum: 2016-10-27 09:47:02 EDT:srm2api:12968:LOG: sending cancel to blocking autovacuum PID 12874 2016-10-27 09:47:02 EDT:srm2api:12968:DETAIL: Process

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

Steve Clark writes: > No. But I examined the pg_log/log_file and saw an error indicating it was > autovacuum: > 2016-10-27 09:47:02 EDT:srm2api:12968:LOG: sending cancel to blocking > autovacuum PID 12874 > 2016-10-27 09:47:02 EDT:srm2api:12968:DETAIL: Process 12968 waits for > ExclusiveLock

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/16 9:27 AM, Steve Clark wrote: > On 10/28/2016 09:15 AM, Adrian Klaver wrote: >> On 10/28/2016 05:28 AM, Steve Clark wrote: >>> Hello List, >>> >>> I am occasionally seeing the following error: >>> ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 >> So what exactly is it doing

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

Steve Clark writes: > I am occasionally seeing the following error: > ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 This isn't nearly enough information to determine what is going on. But if that is a report of a server-detected deadlock error, there should be more information about

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 09:15 AM, Adrian Klaver wrote: On 10/28/2016 05:28 AM, Steve Clark wrote: Hello List, I am occasionally seeing the following error: ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 So what exactly is it doing at line 3351? from an application written using ecpg when

Re: [GENERAL] deadlock error - version 8.4 on CentOS 6

On 10/28/2016 05:28 AM, Steve Clark wrote: Hello List, I am occasionally seeing the following error: ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 So what exactly is it doing at line 3351? from an application written using ecpg when trying an update to the table. Can autovacuum

[GENERAL] deadlock error - version 8.4 on CentOS 6

Hello List, I am occasionally seeing the following error: ALERT 3 sqlcode=-400 errmsg=deadlock detected on line 3351 from an application written using ecpg when trying an update to the table. Can autovacuum be causing this, since no one else is updating this database table. Thanks, -- Stephen

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

I've already switched to 'UPSERT', it didn't resolved deadlock issue by itself... Added LOCK TABLE ... IN EXCLUSIVE MODE; to one session, hope it will help. You did not mention what version of Postgres you are using, if it is 9.5+ you have the 'UPSERT' option available instead of using the WIT

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

On 07/02/2016 09:54 AM, trafdev wrote: Hello. I have two transactions (trans1 and trans2) updating tables T1 and T2 in the same order, but in a different way. trans1 creates temp table, copies data from a file and updates tables T1 and T2 from this temp table (using basic UPDATE form). It even

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

On 07/02/2016 09:01 PM, trafdev wrote: I've also replaced "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" to "INSERT INTO .. ON CONFLICT DO UPDATE ...", but no success - row level deadlocks still occur... Is there a way to tell Postgres to update rows in a specified order? Or maybe

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

I've also replaced "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" to "INSERT INTO .. ON CONFLICT DO UPDATE ...", but no success - row level deadlocks still occur... Is there a way to tell Postgres to update rows in a specified order? Or maybe LOCK TABLE should be used? Sessions

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

Best guess you are running into what is described here: https://www.postgresql.org/docs/9.5/static/explicit-locking.html#LOCKING-DEADLOCKS Both transactions are holding locks on rows in T1 that the other wants also. I may be missing something, but I am not sure why it is necessary to run both

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

On 07/02/2016 11:38 AM, trafdev wrote: Yes, you are right about sessions. Here is the case from the server log: "deadlock detected","Process 2588 waits for ShareLock on transaction 1939192; blocked by process 16399. Process 16399 waits for ShareLock on transaction 1939195; blocked by process 25

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

Yes, you are right about sessions. Here is the case from the server log: "deadlock detected","Process 2588 waits for ShareLock on transaction 1939192; blocked by process 16399. Process 16399 waits for ShareLock on transaction 1939195; blocked by process 2588. Process 2588: UPDATE T1 SET

Re: [GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

On 07/02/2016 09:54 AM, trafdev wrote: Hello. I have two transactions (trans1 and trans2) updating tables T1 and T2 in the same order, but in a different way. trans1 creates temp table, copies data from a file and updates tables T1 and T2 from this temp table (using basic UPDATE form). It even

[GENERAL] deadlock between "WITH agg_tmp AS ({sel_stmt}), upd AS ({upd_stmt}) {ins_stmt}" and pure UPDATE statements

Hello. I have two transactions (trans1 and trans2) updating tables T1 and T2 in the same order, but in a different way. trans1 creates temp table, copies data from a file and updates tables T1 and T2 from this temp table (using basic UPDATE form). It even commits changes in between T1 and T2

Re: [GENERAL] Deadlock between VACUUM and ALTER TABLE commands

On 14/04/16 18:34, Kevin Burke wrote: Unfortunately *I'm still seeing a very slow query which is affecting our tests. *It's happening with roughly the same frequency as the previous error. * * The query log is here: https://gist.github.com/kevinburkeshyp/f1a4f73f8933e027aebbc53283acced2** *

Re: [GENERAL] Deadlock between VACUUM and ALTER TABLE commands

On 14/04/16 18:34, Kevin Burke wrote: Unfortunately *I'm still seeing a very slow query which is affecting our tests. *It's happening with roughly the same frequency as the previous error. * * The query log is here: https://gist.github.com/kevinburkeshyp/f1a4f73f8933e027aebbc53283acced2** *

Re: [GENERAL] Deadlock between VACUUM and ALTER TABLE commands

Hi Alexey, Thank you for your suggestion. We had to rewrite our constraints to be DEFERRABLE INITIALLY IMMEDIATE, but after we did that we saw a nice speedup in our clear-all-tables query. Unfortunately *I'm still seeing a very slow query which is affecting our tests. *It's happening with roughly

Re: [GENERAL] Deadlock between VACUUM and ALTER TABLE commands

Hello Kevin, On 06/04/16 23:22, Kevin Burke wrote: /Why mess around with DISABLE/ENABLE TRIGGER instead of TRUNCATE?/ We observed that TRUNCATE took about 200ms, but this method takes about 13ms. Over a our test suite TRUNCATE is going to more or less double the length of the suite. We could

[GENERAL] Deadlock between VACUUM and ALTER TABLE commands

Hello, Occasionally our test suite gets into a deadlocked state. I was curious why this happens, and what our options are for dealing with it. We run ~2900 tests on an Ubuntu machine in sequence against a Postgres 9.4.6 database. There are about 60 tables; each test runs ~3 queries, and the larges

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On 11/5/15 12:14 AM, Jiří Hlinka wrote: I'm doing simple UPDATES, INSERTs and DELETEs on this table, but frequency of these DMLs is _very_ high (it is a queue table used for a one-way selective [just part of data are replicated] replication of queries between two instances of the database, lets s

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On Fri, Nov 6, 2015 at 4:08 PM, Jiří Hlinka wrote: > my point was, that pg_repack deadlocked itself - I think it should be > possible to guarantee deadlock-free behavior at least via advisory lock for > operations of pg_repack itself (I understand it is not possible to guarantee > this across more

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

Hi Kevin, my point was, that pg_repack deadlocked itself - I think it should be possible to guarantee deadlock-free behavior at least via advisory lock for operations of pg_repack itself (I understand it is not possible to guarantee this across more apps). If it is not true, I'd be glad to hear I'

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On Thursday, November 5, 2015 12:16 AM, Jiří Hlinka wrote: > My opinion is, that pg_repack should guarantee a consistent, > deadlock-free behaviour via proper locking policy I would be very interesting in seeing a description of what locking policy would guarantee deadlock-free behavior when ru

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

I'll check if the trigger activity was moving on or waiting for a lock from logs. I'm doing simple UPDATES, INSERTs and DELETEs on this table, but frequency of these DMLs is _very_ high (it is a queue table used for a one-way selective [just part of data are replicated] replication of queries betw

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On Wed, Nov 4, 2015 at 10:16 PM, Jiří Hlinka wrote: > I'm on pg_repack 1.3.2 (latest sable, no devel version available to check > if it is already fixed). > > Michael: your memories are fresh and clear :-), yes, it is part of a > cleanup rollback. The problem is, that the pgrepack_drop call this

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

Thanks Jim and Michael for comments. I'm on pg_repack 1.3.2 (latest sable, no devel version available to check if it is already fixed). Michael: your memories are fresh and clear :-), yes, it is part of a cleanup rollback. The problem is, that the pgrepack_drop call this statement: DROP TABLE IF

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On 11/3/15 7:44 AM, Michael Paquier wrote: I doubt there is anything involving Postgres here. It seems that some process is still holding a lock on a relation that is being dropped, caused by a race condition in pg_repack code. >PS: I was trying a mailing list of pg_repack >(http://lists.pgfoun

Re: [GENERAL] Deadlock detected after pg_repack receives SIGINT

On Tue, Nov 3, 2015 at 9:51 PM, Jiří Hlinka wrote: > After the 10 min timeout, the OS sends SIGINT to pg_repack process so the > pg_repack calls: > SELECT repack.repack_drop($1, $2) > and it causes a deadlock with other process which is INSERTing into > frequently_updated_table that has a pg_repac

[GENERAL] Deadlock detected after pg_repack receives SIGINT

I'm running a pg_repack from a bash script with timeout of 10 minutes like so (simplified version): timeout -s SIGINT 10m pg_repack --table=frequently_updated_table After the 10 min timeout, the OS sends SIGINT to pg_repack process so the pg_repack calls: SELECT repack.repack_drop($1, $2) and it c

Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement

Hi, retested: yes, this is still an issue in 9.3.5, same deadlock errors occured. Do you need to extract some simplified reproducible testcase? Best Regards, Andrej

Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement

Andrej Vanek wrote: > Hi, > > > now I've checked release-notes of 9.3.5 (my version 9.3.4)- found a fix > which probably could lead to my deadlocks: > > > Fix race condition when updating a tuple concurrently locked by another > > process (Andres Freund,Álvaro Herrera) > > How can I make sure I

Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement

Hi, now I've checked release-notes of 9.3.5 (my version 9.3.4)- found a fix which probably could lead to my deadlocks: > Fix race condition when updating a tuple concurrently locked by another process (Andres Freund,Álvaro Herrera) How can I make sure I've run into this bug?

Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement

Hi Bill, thanks for your answer. > most often caused by something earlier in the transactions > need all of the statements in each transaction It would be great if we could reveal an application error. Whole transactions I've already posted (in postgres log: log_min_duration_statement=0). Not

Re: [GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement

On Tue, 23 Sep 2014 20:00:27 +0200 Andrej Vanek wrote: > Hi, > > My application runs many concurrent sessions with the same transaction code > starting with an update statement. > I would expect locking and serialization of those transactions. But I get > unexpected deadlocks. > As opposed to *h

[GENERAL] deadlock of lock-waits (on transaction and on tuple) using same update statement

Hi, My application runs many concurrent sessions with the same transaction code starting with an update statement. I would expect locking and serialization of those transactions. But I get unexpected deadlocks. As opposed to *http://momjian.us/main/writings/pgsql/locking.pdf

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/25/2014 04:18 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 9:21 PM, Alvaro Herrera mailto:alvhe...@2ndquadrant.com>> wrote: FWIW this problem was reported also by Andrew Sackville-West at http://www.postgresql.org/message-id/20140731233051.GN17765@andrew-ThinkPad-X

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 6:55 PM, Jeff Janes wrote: > What transaction isolation level is being used? > Sorry for late reply - the user was away for parts of friday, I was away on weekend, and just now got answer - it's read committed. depesz

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 9:21 PM, Alvaro Herrera wrote: > FWIW this problem was reported also by Andrew Sackville-West at > > http://www.postgresql.org/message-id/20140731233051.GN17765@andrew-ThinkPad-X230 > I strongly suspect now that the problem is related to the locking of > updated versions a

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 8:33 PM, Adrian Klaver wrote: > Not sure, just the combination of parallel operations and remote > connections seemed to be an avenue to explore. Given that everything is > local, turns out it was dead end. > Looking at the pastebin log again, am I reading it right that th

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

hubert depesz lubaczewski wrote: > I have developer with pg 9.3.5, which is reporing something really strange. > > He runs importer, which does, in single transaction: > > begin; > select * from table where pkey = limit 1 for update; > update table set ... where pkey = ; > commit; > > and two b

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 11:14 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:54 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Which in itself might be a clue. Is all the code/data running on/coming from that machine or is some coming in remotely? Where netwo

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:54 PM, Adrian Klaver wrote: > Which in itself might be a clue. > > Is all the code/data running on/coming from that machine or is some coming > in remotely? > > Where network latency might be an issue? > All locally, but hey - how could network latency be a problem? Tra

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 10:50 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:43 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: Which begs the question, what is different about that machine? No idea. I can pass all the question you might have, but I'm ~ 6000 miles away fr

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:49 PM, Tom Lane wrote: > You have not shown us the full sequence of events leading up to the > deadlock failure, but I hypothesize that there were yet other transactions > that updated that same row in the very recent past. That might allow > there to be more than one t

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:43 PM, Adrian Klaver wrote: > Which begs the question, what is different about that machine? > No idea. I can pass all the question you might have, but I'm ~ 6000 miles away from any machine running this code. depesz

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

hubert depesz lubaczewski writes: > On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver > wrote: >> So process 66017 and 66014 are blocking each because they are running the >> exact same queries. The interesting part is the process with the lower pid >> is starting later then the none with the higher

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 10:36 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 7:20 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: So why are different processes running the exact same queries coming in on different ports? the importer is parallelized, and sometimes two

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:20 PM, Adrian Klaver wrote: > So why are different processes running the exact same queries coming in on > different ports? > the importer is parallelized, and sometimes two processes handle batches of data that happen to update the same "top level row". but the deadlo

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 7:29 PM, John R Pierce wrote: > On 8/22/2014 9:29 AM, hubert depesz lubaczewski wrote: > >> select * from table where pkey = limit 1 for update; >> > why is there a limit 1 in there?pkey=somevalue should only return a > single row. if it DID return multiple rows, y

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 8/22/2014 9:29 AM, hubert depesz lubaczewski wrote: select * from table where pkey = limit 1 for update; why is there a limit 1 in there?pkey=somevalue should only return a single row. if it DID return multiple rows, you don't have an ORDER BY, so the limit 1 would be indeterminate.

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 10:15 AM, hubert depesz lubaczewski wrote: On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver mailto:adrian.kla...@aklaver.com>> wrote: So process 66017 and 66014 are blocking each because they are running the exact same queries. The interesting part is the process with the

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 6:45 PM, Adrian Klaver wrote: > So process 66017 and 66014 are blocking each because they are running the > exact same queries. The interesting part is the process with the lower pid > is starting later then the none with the higher pid. > Locking is obvious. But why dead

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On Fri, Aug 22, 2014 at 9:29 AM, hubert depesz lubaczewski wrote: > I have developer with pg 9.3.5, which is reporing something really strange. > > He runs importer, which does, in single transaction: > > begin; > select * from table where pkey = limit 1 for update; > update table set ... where

Re: [GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

On 08/22/2014 09:29 AM, hubert depesz lubaczewski wrote: I have developer with pg 9.3.5, which is reporing something really strange. He runs importer, which does, in single transaction: begin; select * from table where pkey = limit 1 for update; update table set ... where pkey = ; commit; and

[GENERAL] deadlock in single-row select-for-update + update scenario? How could it happen?

I have developer with pg 9.3.5, which is reporing something really strange. He runs importer, which does, in single transaction: begin; select * from table where pkey = limit 1 for update; update table set ... where pkey = ; commit; and two backends running the same transaction deadlock. I che

Re: [GENERAL] deadlock detected

Richard Huxton writes: > On 05/11/12 18:39, AI Rumman wrote: >> ERROR: deadlock detected >> DETAIL: Process 20265 waits for ShareLock on transaction 27774015; >> blocked by process 20262. >> Process 20262 waits for ShareLock on transaction 27774018; >> blocked by process 20265. >> Process 2026

Re: [GENERAL] deadlock detected

On 05/11/12 18:39, AI Rumman wrote: Hi all, I am using Postrgesql 9.1 I got a message in my log: ERROR: deadlock detected DETAIL: Process 20265 waits for ShareLock on transaction 27774015; blocked by process 20262. Process 20262 waits for ShareLock on transaction 27774018; blocked b

[GENERAL] deadlock detected

Hi all, I am using Postrgesql 9.1 I got a message in my log: ERROR: deadlock detected DETAIL: Process 20265 waits for ShareLock on transaction 27774015; blocked by process 20262. Process 20262 waits for ShareLock on transaction 27774018; blocked by process 20265. Process 20265:

[GENERAL] Deadlock report

Hi, I found patch for 8.4: When reporting a deadlock, report the text of all queries involved in the deadlock to the server log (Itagaki Takahiro) My question is how to enable this feature in 9.1.2 - is it activated out of the box or do I have to enable it somehow? Regards, Bartek Pozdrawiam, Bar

Re: [GENERAL] Deadlock of REINDEX and SELECT queries in PostgresSQL 7.4

On Thursday, September 29, 2011 8:49:07 am Tendulker, Shivanand G Prabhu (SSTL) wrote: > Hello > > We are facing a deadlock kind of issue in PostgresSQL 7.4 > > We have 2 databases with 3 tables each. DB contains about 250 records. We > observed deadlock when 2 different clients are performing R

Re: [GENERAL] Deadlock of REINDEX and SELECT queries in PostgresSQL 7.4

On 09/29/11 8:49 AM, Tendulker, Shivanand G Prabhu (SSTL) wrote: Please provide help in resolving this issue. 7.4 is an ancient dead end release. update to something released this century, like 8.4 or 9.0... where, btw, vacuum, analyze, and reindex are now automatic and no longer need doin

[GENERAL] Deadlock of REINDEX and SELECT queries in PostgresSQL 7.4

Hello We are facing a deadlock kind of issue in PostgresSQL 7.4 We have 2 databases with 3 tables each. DB contains about 250 records. We observed deadlock when 2 different clients are performing REINDEX and SELECT start their operations near simultaneously. Client 1 performs following operat

Re: [GENERAL] deadlock problem

On 05/30/2011 10:04 PM, Sebastian Böhm wrote: Acquired by CREATE INDEX (without CONCURRENTLY). so where the "ShareLock" is acquired? I don't create an index here. There's some confusing historical terminology involved here, I'm afraid. The documentation you referred to talks about table-

[GENERAL] deadlock problem

Hi, I need a little help with a deadlock. when I execute this (end of the mail) function in parallel sometimes a deadlock happens. This function does implement a insert or update functionality. The error is: "DETAIL: Process 29464 waits for ShareLock on transaction 1293098; blocked by proces

Re: [GENERAL] Deadlock in libpq

On Fri, Mar 25, 2011 at 21:21, Merlin Moncure wrote: > On Fri, Mar 25, 2011 at 3:26 AM, Erik Hesselink wrote: >>> hm, ISTM (I don't know haskell) that the hdbc driver isn't doing any >>> type of synchronization at all unless it is using a non thread safe >>> libpq...and in that case it uses a glo

Re: [GENERAL] Deadlock in libpq

On Fri, Mar 25, 2011 at 3:26 AM, Erik Hesselink wrote: >> hm, ISTM (I don't know haskell) that the hdbc driver isn't doing any >> type of synchronization at all unless it is using a non thread safe >> libpq...and in that case it uses a global mutex.  That doesn't look >> correct -- the hdbc driver

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 20:38, Merlin Moncure wrote: > On Thu, Mar 24, 2011 at 11:57 AM, Merlin Moncure wrote: >> On Thu, Mar 24, 2011 at 11:52 AM, Merlin Moncure wrote: As far as connections getting dropped: yes, this sounds reasonable, but given that both the client and the server ar

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 11:57 AM, Merlin Moncure wrote: > On Thu, Mar 24, 2011 at 11:52 AM, Merlin Moncure wrote: >>> As far as connections getting dropped: yes, this sounds reasonable, >>> but given that both the client and the server are running on the same >>> machine, will connections (to 127

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 11:52 AM, Merlin Moncure wrote: >> As far as connections getting dropped: yes, this sounds reasonable, >> but given that both the client and the server are running on the same >> machine, will connections (to 127.0.0.1) really be dropped once every >> 100.000 or so? > > No,

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 11:27 AM, Erik Hesselink wrote: > On Thu, Mar 24, 2011 at 17:18, Merlin Moncure wrote: >> On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink wrote: >>> On Thu, Mar 24, 2011 at 16:43, Merlin Moncure wrote: He needs to rule out the most obvious problem first -- PQInitSS

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 17:18, Merlin Moncure wrote: > On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink wrote: >> On Thu, Mar 24, 2011 at 16:43, Merlin Moncure wrote: >>> He needs to rule out the most obvious problem first -- PQInitSSL being >>> called improperly or at the wrong time.  OP: It's

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 10:54 AM, Erik Hesselink wrote: > On Thu, Mar 24, 2011 at 16:43, Merlin Moncure wrote: >> He needs to rule out the most obvious problem first -- PQInitSSL being >> called improperly or at the wrong time.  OP: It's a library wide >> setting and must be called before the fir

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 16:43, Merlin Moncure wrote: > He needs to rule out the most obvious problem first -- PQInitSSL being > called improperly or at the wrong time.  OP: It's a library wide > setting and must be called before the first connection is established > and only once.  Perhaps the HDB

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 10:00 AM, Tom Lane wrote: > Merlin Moncure writes: >> *something* must be initializing ssl, or you can't make secure >> connections from libpq.  you need to find out which pq ssl init >> function is begin called, when it is being called, and with what >> arguments. One of

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 16:00, Tom Lane wrote: > Merlin Moncure writes: >> *something* must be initializing ssl, or you can't make secure >> connections from libpq.  you need to find out which pq ssl init >> function is begin called, when it is being called, and with what >> arguments. One of the

Re: [GENERAL] Deadlock in libpq

Merlin Moncure writes: > *something* must be initializing ssl, or you can't make secure > connections from libpq. you need to find out which pq ssl init > function is begin called, when it is being called, and with what > arguments. One of the main things PQInitSSL does is set up a lock > vector

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 15:21, Merlin Moncure wrote: > On Thu, Mar 24, 2011 at 9:07 AM, Erik Hesselink wrote: >> On Thu, Mar 24, 2011 at 14:23, Merlin Moncure wrote: >>> On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink wrote: Hi, We're getting a deadlock in our application (a web

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 9:07 AM, Erik Hesselink wrote: > On Thu, Mar 24, 2011 at 14:23, Merlin Moncure wrote: >> On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink wrote: >>> Hi, >>> >>> We're getting a deadlock in our application (a web application with a >>> PostgreSQL backend) which I've traced

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 14:23, Merlin Moncure wrote: > On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink wrote: >> Hi, >> >> We're getting a deadlock in our application (a web application with a >> PostgreSQL backend) which I've traced to libpq. I've started our >> application in gdb, and when it h

Re: [GENERAL] Deadlock in libpq

On Thu, Mar 24, 2011 at 4:17 AM, Erik Hesselink wrote: > Hi, > > We're getting a deadlock in our application (a web application with a > PostgreSQL backend) which I've traced to libpq. I've started our > application in gdb, and when it hangs, I've inspected the backtraces. > I've found a couple of

[GENERAL] Deadlock in libpq

Hi, We're getting a deadlock in our application (a web application with a PostgreSQL backend) which I've traced to libpq. I've started our application in gdb, and when it hangs, I've inspected the backtraces. I've found a couple of threads I can account for (listening for new connections, backgrou

Re: [GENERAL] Deadlock on the same select for update

On Mon, Feb 21, 2011 at 7:16 AM, Roman wrote: > Hi, > I have problem with deadlocks and don't know why it happens. Below is > the log (postgres 9.0, debian): > > [11882]DETAIL:  Process 11882 waits for ShareLock on transaction > 44324308; blocked by process 11884. >        Process 11884 waits for

Re: [GENERAL] Deadlock on the same select for update

In response to Roman : > Hi, > I have problem with deadlocks and don't know why it happens. Below is > the log (postgres 9.0, debian): > > [11882]DETAIL: Process 11882 waits for ShareLock on transaction > 44324308; blocked by process 11884. > Process 11884 waits for ShareLock on transact

[GENERAL] Deadlock on the same select for update

Hi, I have problem with deadlocks and don't know why it happens. Below is the log (postgres 9.0, debian): [11882]DETAIL: Process 11882 waits for ShareLock on transaction 44324308; blocked by process 11884. Process 11884 waits for ShareLock on transaction 44324307; blocked by process 11882

Re: [GENERAL] deadlock

On 08/11/10 6:32 PM, David Fetter wrote: does anyone have any suggestions for what to look for, or what sort of common partition management mistakes in the application could lead to this sort of deadlock? DDL is a "don't do it at peak load" event. More realistically, it's more like a "down tim

Re: [GENERAL] deadlock

On Tue, Aug 10, 2010 at 11:35:48PM -0700, John R Pierce wrote: > We've got an app, I don't know all the details of the schema > offhand, but its using date partitioned tables, its heavily > multithreaded and processing continuous events... Under load, > production (overseas) is getting a SQL dea

[GENERAL] deadlock

We've got an app, I don't know all the details of the schema offhand, but its using date partitioned tables, its heavily multithreaded and processing continuous events... Under load, production (overseas) is getting a SQL deadlock... Process 20333: DROP table data_details_20100718 Pro

Re: [GENERAL] deadlock on simple update

Well, the reason why deadlock happen is usually uncoordinated access to the same resource - in this case the resouce is a database row. This has nothing to do with the complexity of the queries, but with the order of the updates. According to the log process 8253 waits for 8230, and 8230 waits for

[GENERAL] deadlock on simple update

Hello, I have 8.4.2. I'm getting deadlock when multiple processes try to update a table. Strange is it is a simple table with no triggers firing etc. Just an id and a numerical field to update. So I'm at odds as to what could cause the deadlock? DETAIL: Process 8253 waits for ShareLock on tr

Re: [GENERAL] Deadlock occur while creating new table to be used in partition.

LOCK command required a dummy table. FUNCTION get_existing_or_create_lot BEGIN LOCK dummy_table ... ... END;$BODY$ Thanks and Regards Yan Cheng CHEOK --- On Mon, 4/26/10, Tom Lane wrote: > From: Tom Lane > Subject: Re: [GENERAL] Deadlock occur while creating new ta

  1   2   3   >