Re: pg primary key bug?

Tom Lane wrote:

>pginfo <pginfo(at)t1(dot)unisoftbg(dot)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.