Thread: Problems with sequences
Hi all,
My environment is Tomcat5.0, Cayenne 1.2 and PostgreSQL 8.1.4 on a
RedHat ES3 machine.
My webapplication is reusing sequence numbers and getting duplicate
primary key
failures because of it (error is "duplicate key violates unique
constraint"). The
columns are not defined as SERIAL for historical reasons so it fetches
nextval and
uses that.
The webapp stays connected for days at a time. It's only using a
handful (usually 2) connections.
What happens is that if I do a select nextval('seq') I get a number
that's lower than the
max primary key id. This is inspite of my doing
SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
ALTER SEQUENCE seq RESTART WITH <max + 1>;
select pg_catalog.setval(seq, <max+1>, true);
I've learned that the first thing is only good for the current session
and I've no idea why the second and third aren't working.
Mostly what I'm hoping for is some debugging tips. I tried setting
log_statement = 'all' but that doesn't show the parameters to prepared
statements nor any access to the sequence.
Does anyone have any experience helping me to pinpoint the cause of
this? Tomcat JDBC pooling? Cayenne caching?
tia
arturo
On Wed, 2006-09-06 at 16:56, Arturo Perez wrote:
> Hi all,
>
> My environment is Tomcat5.0, Cayenne 1.2 and PostgreSQL 8.1.4 on a
> RedHat ES3 machine.
>
> My webapplication is reusing sequence numbers and getting duplicate
> primary key
> failures because of it (error is "duplicate key violates unique
> constraint"). The
> columns are not defined as SERIAL for historical reasons so it fetches
> nextval and
> uses that.
>
> The webapp stays connected for days at a time. It's only using a
> handful (usually 2) connections.
>
> What happens is that if I do a select nextval('seq') I get a number
> that's lower than the
> max primary key id. This is inspite of my doing
> SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
> ALTER SEQUENCE seq RESTART WITH <max + 1>;
> select pg_catalog.setval(seq, <max+1>, true);
When are you doing these statements? You shouldn't really need to set a
sequence to a new number except right after a data load or something
like that. definitely not when anyone else is using the db.
In article <1157581398.20424.32.camel@state.g2switchworks.com>,
smarlowe@g2switchworks.com (Scott Marlowe) wrote:
> On Wed, 2006-09-06 at 16:56, Arturo Perez wrote:
> > Hi all,
> >
> > My environment is Tomcat5.0, Cayenne 1.2 and PostgreSQL 8.1.4 on a
> > RedHat ES3 machine.
> >
> > My webapplication is reusing sequence numbers and getting duplicate
> > primary key
> > failures because of it (error is "duplicate key violates unique
> > constraint"). The
> > columns are not defined as SERIAL for historical reasons so it fetches
> > nextval and
> > uses that.
> >
> > The webapp stays connected for days at a time. It's only using a
> > handful (usually 2) connections.
> >
> > What happens is that if I do a select nextval('seq') I get a number
> > that's lower than the
> > max primary key id. This is inspite of my doing
> > SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
> > ALTER SEQUENCE seq RESTART WITH <max + 1>;
> > select pg_catalog.setval(seq, <max+1>, true);
>
> When are you doing these statements? You shouldn't really need to set a
> sequence to a new number except right after a data load or something
> like that. definitely not when anyone else is using the db.
We (me!) just converted our app from MySQL to PostgreSQL. We wrote a
perl script to copy the data from the MySQL instance to the new
PostgreSQL instance. As part of that data copy we did the first thing
as that was recommended by a comment in the online manual for PostgreSQL.
Ever since then the problem described has been happening. The other two
statements were done in an attempt to correct the problem without
restarting the whole application (ie without bouncing tomcat).
I just had the bounce the app anyway (mgmt :-) so I'm hoping the problem
won't reoccur but I need steps to take if it does.
-arturo
On 9/6/06, Arturo Perez <aperez@hayesinc.com> wrote:
> What happens is that if I do a select nextval('seq') I get a number
> that's lower than the
> max primary key id. This is inspite of my doing
> SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
> ALTER SEQUENCE seq RESTART WITH <max + 1>;
> select pg_catalog.setval(seq, <max+1>, true);
>
are you running those statements to fetch the next key in the table?
you might have a race condition there. try wrappnig in a userlock.
merlin
On Sep 6, 2006, at 8:48 PM, Merlin Moncure wrote:
> On 9/6/06, Arturo Perez <aperez@hayesinc.com> wrote:
>> What happens is that if I do a select nextval('seq') I get a number
>> that's lower than the
>> max primary key id. This is inspite of my doing
>> SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
>> ALTER SEQUENCE seq RESTART WITH <max + 1>;
>> select pg_catalog.setval(seq, <max+1>, true);
>>
>
> are you running those statements to fetch the next key in the table?
> you might have a race condition there. try wrappnig in a userlock.
>
> merlin
No, not running them to get the next key. Just trying to reset the
sequence so that I stop getting duplicates.
A race condition is unlikely as only one person can actually add
these things to the system.
-arturo
On 9/6/06, Arturo Perez <aperez@hayesinc.com> wrote:
>
> On Sep 6, 2006, at 8:48 PM, Merlin Moncure wrote:
>
> > On 9/6/06, Arturo Perez <aperez@hayesinc.com> wrote:
> >> What happens is that if I do a select nextval('seq') I get a number
> >> that's lower than the
> >> max primary key id. This is inspite of my doing
> >> SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
> >> ALTER SEQUENCE seq RESTART WITH <max + 1>;
> >> select pg_catalog.setval(seq, <max+1>, true);
> >>
> >
> > are you running those statements to fetch the next key in the table?
> > you might have a race condition there. try wrappnig in a userlock.
> >
> > merlin
>
> No, not running them to get the next key. Just trying to reset the
> sequence so that I stop getting duplicates.
>
> A race condition is unlikely as only one person can actually add
> these things to the system.
If you can reproduce this, it would be of great interest to me and a
lot of other people. Can you turn sql logging on the server and make
it happen? We need to absolutely eliminate any application generated
bugs here.
merlin
Arturo Perez wrote:
> What happens is that if I do a select nextval('seq') I get a number
> that's lower than the
> max primary key id. This is inspite of my doing
> SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
> ALTER SEQUENCE seq RESTART WITH <max + 1>;
> select pg_catalog.setval(seq, <max+1>, true);
This seems to be a bit over the top;
SELECT setval('seq', (SELECT MAX(seq_ID) FROM table)
should be enough. Even the +1 isn't necessary, as the first value the
sequence will return is already 1 higher than the value retrieved from MAX.
Are you sure you're using the correct sequence(s) to retrieve your
column values for the problematic table(s)? How do you set the values
for seqID?
Also note that a SERIAL type column is simply a macro for creating an
INT4 type column with a DEFAULT nextval('...'). You can easily change
your schema to include the defaults.
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
On Sep 7, 2006, at 5:35 AM, Alban Hertroys wrote:
> Arturo Perez wrote:
>> What happens is that if I do a select nextval('seq') I get a number
>> that's lower than the
>> max primary key id. This is inspite of my doing
>> SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
>> ALTER SEQUENCE seq RESTART WITH <max + 1>;
>> select pg_catalog.setval(seq, <max+1>, true);
>
> This seems to be a bit over the top;
> SELECT setval('seq', (SELECT MAX(seq_ID) FROM table)
> should be enough. Even the +1 isn't necessary, as the first value
> the sequence will return is already 1 higher than the value
> retrieved from MAX.
Note that all of the above was in an attempt to reset the sequence to
the proper value. I'm beginning to think that it's a library problem
as this morning I get:
iht=> select max(article_id) from article;
max
------
4992
(1 row)
iht=> select nextval('pk_article');
nextval
---------
4986
(1 row)
Assuming the sequence number is being used correctly why would they
be 6 apart?
>
> Are you sure you're using the correct sequence(s) to retrieve your
> column values for the problematic table(s)? How do you set the
> values for seqID?
I tried statement logging but I am not sure it reported anything
useful. When I get into work I'll send in those logs.
-arturo
On Thu, Sep 07, 2006 at 07:15:43AM -0400, Arturo Perez wrote:
> Note that all of the above was in an attempt to reset the sequence to
> the proper value. I'm beginning to think that it's a library problem
> as this morning I get:
>
> iht=> select max(article_id) from article;
> max
> ------
> 4992
> (1 row)
>
> iht=> select nextval('pk_article');
> nextval
> ---------
> 4986
> (1 row)
>
> Assuming the sequence number is being used correctly why would they
> be 6 apart?
The last four transactions could be rolled back, or not committed yet.
I'd suggest looking at your insert statements. If the actual inserts
are insterting actual numbers, look very very carefully where the numbers
are coming from.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.
Attachment
On 9/7/06, Arturo Perez <aperez@hayesinc.com> wrote: > I tried statement logging but I am not sure it reported anything > useful. When I get into work I'll send in those logs. More than likely they are large and should not be sent through the mailing list. contact me off list and I'll arrange it. merlin
From: Alban Hertroys [mailto:alban@magproductions.nl]
>Martijn van Oosterhout wrote:
>> On Thu, Sep 07, 2006 at 07:15:43AM -0400, Arturo Perez wrote:
>>> iht=> select max(article_id) from article;
>>> max
>>> ------
>>> 4992
>>> (1 row)
>>>
>>> iht=> select nextval('pk_article');
>>> nextval
>>> ---------
>>> 4986
>>> (1 row)
>>>
>>> Assuming the sequence number is being used correctly why would they
>> be 6 apart?
>>
>> The last four transactions could be rolled back, or not committed yet.
>
> b) the OP inserted numbers not coming from the sequence; he shot his
>own feet.
I prefer to think that my feet were shot off by a library I'm using :-)
Some many layers, so little time (to debug).
In any case, at this point in time it's looking like Cayenne doesn't honor
the rules of the sequence. It appears to (and is documented as) internally
incrementing rather than fetching the sequence for each insert.
I would still like more debugging tips for this sort of thing. As I mentioned,
statement logging did not show the relevant details. What other things could
I have done?
-arturo
Martijn van Oosterhout wrote:
> On Thu, Sep 07, 2006 at 07:15:43AM -0400, Arturo Perez wrote:
>> iht=> select max(article_id) from article;
>> max
>> ------
>> 4992
>> (1 row)
>>
>> iht=> select nextval('pk_article');
>> nextval
>> ---------
>> 4986
>> (1 row)
>>
>> Assuming the sequence number is being used correctly why would they
>> be 6 apart?
>
> The last four transactions could be rolled back, or not committed yet.
Could you elaborate on that? I'm confused...
AFAIK after the sequence was initialised at max(article_id),
nextval(article_id) could never return a number that's lower than
max(article_id).
Unless:
a) the OP managed to query max(article_id) and nextval(article_id)
from different transactions, the one querying nextval being older than
the one querying max.
b) the OP inserted numbers not coming from the sequence; he shot his
own feet.
c) the sequence wrapped around due to reaching numbers exceeding 2^32.
Or are you short on caffeine perhaps? ;)
Regards,
--
Alban Hertroys
alban@magproductions.nl
magproductions b.v.
T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
7500 AK Enschede
// Integrate Your World //
Arturo Perez wrote: > In any case, at this point in time it's looking like Cayenne doesn't honor > the rules of the sequence. It appears to (and is documented as) internally > incrementing rather than fetching the sequence for each insert. I have no experience with Cayenne, but reading http://cwiki.apache.org/CAYDOC/primary-key-generation.html it seems possible to use database sequences instead of Cayenne-generated ones: "... Generation mechanism depends on the DbAdapter used and can be customized by users by subclassing one of the included adapters." Regards, -- Alban Hertroys alban@magproductions.nl magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World //
From: Alban Hertroys [mailto:alban@magproductions.nl] >Arturo Perez wrote: >> In any case, at this point in time it's looking like Cayenne doesn't honor >> the rules of the sequence. It appears to (and is documented as) internally >> incrementing rather than fetching the sequence for each insert. > >I have no experience with Cayenne, but reading >http://cwiki.apache.org/CAYDOC/primary-key-generation.html it seems >possible to use database sequences instead of Cayenne-generated ones: > >"... Generation mechanism depends on the DbAdapter used and can be >customized by users by subclassing one of the included adapters." > Yes. I think I am being bitten by a desire to minimize changes required when migrating from MySQL to PostgreSQL. Contrary to my belief, it appears that the pgSQL schema creation script was not created by Cayenne configured to work with pgSQL but rather was based on the script Cayenne used to create the MySQL database. Looks like I will be uncovering nits as we go for a bit yet. I did modify the sequences to increment by 20 as required by Cayenne. Hopefully, this particular issue will be laid to rest by that. thanks all, -arturo
On 9/6/06, Arturo Perez <aperez@hayesinc.com> wrote:
> What happens is that if I do a select nextval('seq') I get a number
> that's lower than the
> max primary key id. This is inspite of my doing
> SELECT setval('seq', ((SELECT MAX(seq_ID) FROM table)+1))
> ALTER SEQUENCE seq RESTART WITH <max + 1>;
> select pg_catalog.setval(seq, <max+1>, true);
Your sequence was probably created with the CACHE parameter. This
will cause each session to cache n values from the sequence.
Resetting the sequence from another session will not affect the others
until they've gone through all their cached values.
-K