Re: [GENERAL] Problems with sequences
On 9/6/06, Arturo Perez <[EMAIL PROTECTED]> 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 ;
select pg_catalog.setval(seq, , 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
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Problems with sequences
From: Alban Hertroys [mailto:[EMAIL PROTECTED] >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 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Problems with sequences
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 [EMAIL PROTECTED] 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 // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Problems with sequences
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
[EMAIL PROTECTED]
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 //
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problems with sequences
From: Alban Hertroys [mailto:[EMAIL PROTECTED]
>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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Problems with sequences
On 9/7/06, Arturo Perez <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problems with sequences
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 http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to
> litigate.
signature.asc
Description: Digital signature
Re: [GENERAL] Problems with sequences
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 ;
select pg_catalog.setval(seq, , 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
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Problems with sequences
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 ;
select pg_catalog.setval(seq, , 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
[EMAIL PROTECTED]
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 //
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Problems with sequences
On 9/6/06, Arturo Perez <[EMAIL PROTECTED]> wrote:
On Sep 6, 2006, at 8:48 PM, Merlin Moncure wrote:
> On 9/6/06, Arturo Perez <[EMAIL PROTECTED]> 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 ;
>>select pg_catalog.setval(seq, , 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Problems with sequences
On Sep 6, 2006, at 8:48 PM, Merlin Moncure wrote:
On 9/6/06, Arturo Perez <[EMAIL PROTECTED]> 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 ;
select pg_catalog.setval(seq, , 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
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problems with sequences
On 9/6/06, Arturo Perez <[EMAIL PROTECTED]> 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 ;
select pg_catalog.setval(seq, , 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
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Problems with sequences
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (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 ;
> >select pg_catalog.setval(seq, , 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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problems with sequences
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 ;
>select pg_catalog.setval(seq, , 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.
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[GENERAL] 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 ;
select pg_catalog.setval(seq, , 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
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
