X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 627EE8B9D5A for ; Tue, 22 Feb 2005 08:42:53 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 65802-08 for ; Tue, 22 Feb 2005 08:42:50 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id B1EEA8B9E8B for ; Tue, 22 Feb 2005 08:42:50 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j1M8go3O007013; Tue, 22 Feb 2005 03:42:50 -0500 (EST) To: pginfo Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann , pgsql-sql@postgresql.org Subject: Re: pg primary key bug? In-reply-to: <421AC251.7060806@t1.unisoftbg.com> References: <4214B466.20908@t1.unisoftbg.com> <8125.1108661189@sss.pgh.pa.us> <4214C94F.2050806@t1.unisoftbg.com> <9340.1108664352@sss.pgh.pa.us> <4214D5C3.4070107@t1.unisoftbg.com> <9785.1108666900@sss.pgh.pa.us> <421992FE.3030108@t1.unisoftbg.com> <3293.1109026294@sss.pgh.pa.us> <421AC251.7060806@t1.unisoftbg.com> Comments: In-reply-to pginfo message dated "Tue, 22 Feb 2005 06:25:37 +0100" Date: Tue, 22 Feb 2005 03:42:50 -0500 Message-ID: <7012.1109061770@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.006 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200502/234 X-Sequence-Number: 20345 pginfo writes: > In this table we store the last value for the ID of part from other tables. > For each table we have one constant in this table. We are using the > table as sequence. > For Example if we nee to insert the next record in some table we make: > select constvalue from a_constants_str where constname ='...' for update; > increase the value and make > update a_constants_str set constvalue= (new value) where... > It is not so easy as I described, but in general this is the case. > Al this is connected with replications and data syncronisation and so on. "Connected"? What exactly is hiding under that last comment? One way I could take your report is that you've found a weird interaction between SELECT FOR UPDATE and VACUUM FULL that no one else has seen before. Another way is that you're using some nonstandard backend extension that has nasty bugs in it. It is interesting that you say this system has been working well for years and only recently have you seen problems. To me the obvious question is "what have you changed recently?" It might not be a bogus change in itself, but it could have triggered a bug at lower levels. It's certainly possible that you have your finger on a backend bug, but if so there's not nearly enough information here for anyone to find and fix it. You need to be thinking in terms of how to reproduce the problem so that it can be studied and fixed. "How can I avoid this problem" is exactly the wrong question to be asking, because even if avoiding it is all that concerns you, no one can answer with any confidence until we understand what the failure mechanism is. regards, tom lane