Tom Lane wrote:
We are using separate table for sequences and not sequences from pg direct, because this is built in application method for makingpginfo <[EMAIL PROTECTED]> 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?
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 ):.
Yes, exact.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.
If I know !To me the obvious question is "what have you changed recently?"
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).
I am sure (not 100%) that it is bug. That is the reason to report the problem.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 idea was that we have "vacuum full" + update or select for update in the same time.You need to be thinking in terms of how to reproduce the problem so that it can be studied and fixed.
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.
Can we set some log parameters to collect the needet data?"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 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,regards, tom lane
ivan.
