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 0EF098B9E6E for ; Tue, 22 Feb 2005 10:28:19 +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 97714-09 for ; Tue, 22 Feb 2005 10:28:10 +0000 (GMT) Received: from unisoft.unisoftbg.com (unisoft.unisoftbg.com [194.12.229.193]) by svr1.postgresql.org (Postfix) with SMTP id 754B98B9E02 for ; Tue, 22 Feb 2005 10:28:08 +0000 (GMT) Received: (qmail 93774 invoked by uid 1011); 22 Feb 2005 10:32:50 -0000 Received: from pginfo@t1.unisoftbg.com by unisoft.unisoftbg.com by uid 1009 with qmail-scanner-1.22-st-qms (clamdscan: 0.75. spamassassin: 2.60. Clear:RC:0(192.168.1.201):SA:0(-4.7/5.0):. Processed in 15.845673 secs); 22 Feb 2005 10:32:50 -0000 X-Antivirus-MYDOMAIN-Mail-From: pginfo@t1.unisoftbg.com via unisoft.unisoftbg.com X-Antivirus-MYDOMAIN: 1.22-st-qms (Clear:RC:0(192.168.1.201):SA:0(-4.7/5.0):. Processed in 15.845673 secs Process 93766) Received: from unknown (HELO t1.unisoftbg.com) (pginfo@t1.unisoftbg.com@192.168.1.201) by unisoft.unisoftbg.com with SMTP; 22 Feb 2005 10:32:34 -0000 Message-ID: <421AFC7F.406@t1.unisoftbg.com> Date: Tue, 22 Feb 2005 10:33:51 +0100 From: pginfo User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: Richard_D_Levine@raytheon.com, Michael Glaesemann , pgsql-sql@postgresql.org Subject: Re: pg primary key bug? 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> <7012.1109061770@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------030609050802050208070201" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=2.289 tagged_above=0 required=5 tests=AWL, HTML_20_30, HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_XBL X-Spam-Level: ** X-Archive-Number: 200502/235 X-Sequence-Number: 20346 --------------030609050802050208070201 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Tom Lane wrote: >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? > > We are using separate table for sequences and not sequences from pg direct, because this is built in application method for making replication and data syncro. I wish only to clarify the reason of using the table and to describe the groud for so many updates and select for updates. Sorry for my bad english ):. >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. > Yes, exact. > To me the obvious >question is "what have you changed recently?" > If I know ! In general we do not make any global changes connected to database access method. We are using jdbc (jdbc driver from pg) + jboss (java based application server) + connection pool (biult in jboss). We are using jdbc with Statement.executeBatch(...) and also direct with Statement.executeUpdate(...) . We are using exact the same ide with oracle without any problem ( oracle have anoder problems and I prefer pg). > 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. > I am sure (not 100%) that it is bug. That is the reason to report the problem. > You need to be thinking in terms of how to reproduce >the problem so that it can be studied and fixed. > You idea was that we have "vacuum full" + update or select for update in the same time. I think it is not the case, because we start vacuum full at 1:00 AM and no one is working in this time. Will vacuum full generate this problem if we have locked table in this time? (It is possible to have locked table in theory) At this time we do not have info about how to reproduce the problem. As the first step we will stop using "vacum full" (if needet we will stop using vacuum analyze too) to try to collect more info. > "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. > > Can we set some log parameters to collect the needet data? Can you describe more detailed the idea of problem with "vacuum full" + "update" and can some one make patch if this problem exists in theory (if I understand you right)? We can start using this patch and see if the problem will be again . If you have anoder Idea we are ready to collect the needet data. > regards, tom lane > > > > regards, ivan. --------------030609050802050208070201 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit

Tom Lane wrote:
pginfo <pginfo@t1.unisoftbg.com> 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?
We are using separate table for sequences and not sequences from pg direct, because this is built in application method for making
replication and data syncro.
I wish only to clarify the reason of using the table and to describe the groud for so many updates and select for updates.
Sorry for my bad english ):.
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.
Yes, exact.
  To me the obvious
question is "what have you changed recently?"
If I know !
In general we do not make any global changes connected to database access method.
We are using  jdbc (jdbc driver from pg)  + jboss (java based application server) + connection pool (biult in jboss).
We are using jdbc with Statement.executeBatch(...) and also direct with Statement.executeUpdate(...) .
We are using exact the same ide with oracle without any problem ( oracle have anoder problems and I prefer pg).
  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.
I am sure (not 100%) that it is bug. That is the reason to report the problem.

  You need to be thinking in terms of how to reproduce
the problem so that it can be studied and fixed.
You idea was that we have "vacuum full" + update or select for update in the same time.
I think it is not the case, because we start vacuum full at 1:00 AM and no one is working in this time.

Will vacuum full generate this problem if we have locked table in this time? (It is possible to have locked table in theory)

At this time we do not have info about how to reproduce the problem.
As the first step we will stop using "vacum full" (if needet we will stop using vacuum analyze too) to try to collect more info.


  "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.
Can we set some log parameters to collect the needet data?
Can you describe more detailed the idea of  problem with "vacuum full" + "update" and can some one make patch if this problem exists in theory (if I  understand you right)?
We can start using this patch and see if the problem will be again .

If you have anoder Idea we are ready to collect the needet data.
			regards, tom lane
regards,
ivan.
--------------030609050802050208070201--