Обсуждение: Re: Auto incrementing primary keys
On Feb 18, 6:08 pm, Paul Boddie <p...@boddie.org.uk> wrote: > On 18 Feb, 13:36, django_user <amalt...@gmail.com> wrote: > > > > > How can stop postgresql from incrementing the primary key value, so > > that even after many failed insert statements it get the next id val. > > "Auto-incrementing" columns, typically implemented using the serial > data type [1], employ sequences. > > From the manual: > > "To avoid blocking of concurrent transactions that obtain numbers from > the same sequence, a nextval operation is never rolled back; that is, > once a value has been fetched it is considered used, even if the > transaction that did the nextval later aborts. This means that aborted > transactions may leave unused "holes" in the sequence of assigned > values. setval operations are never rolled back, either." > > http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html > > In other words, to permit a decent level of concurrency, PostgreSQL > doesn't wait to see if a transaction succeeds with a value from a > sequence before updating the sequence. If you want to reset a sequence > so that it always uses the next unused value as determined by looking > at the table, I suppose you could do something like this: > > select setval('mytable_id_seq', x) from (select max(id) as x from > mytable) as y; > > But I doubt that you would want to do this too often in any system > with any reasonable level of concurrent access to the table or the > sequence concerned. > > Paul > > [1]http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE... so wouldnt I run out of ids one day, if there are lot of failed insert statements, lets say for every successful insert there are 50 unsuccessful inserts, so ids would be 1, 50, 100, and once I have thousands of rows, I will run out of IDs ? should I use bigserial instead ? Thanks
On 18/02/2008 13:14, pgsql_user wrote: > so wouldnt I run out of ids one day, if there are lot of failed insert > statements, lets say for every successful insert there are 50 > unsuccessful inserts, so ids would be 1, 50, 100, and once I have > thousands of rows, I will run out of IDs ? should I use bigserial > instead ? Well, that depends on your usage, so only you can answer that. According to the docs, "serial" creates an integer column, which will give you 2147483647 values - how quickly will you use that lot up? If you think you will run out, by all means use bigserial. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
On 18/02/2008 17:46, Raymond O'Donnell wrote: > Well, that depends on your usage, so only you can answer that. According > to the docs, "serial" creates an integer column, which will give you > 2147483647 values - how quickly will you use that lot up? If you think > you will run out, by all means use bigserial. Actually, that isn't quite right - just looked at the docs for CREATE SEQUENCE, and the default maximum value is 2^63-1. Ray. --------------------------------------------------------------- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie ---------------------------------------------------------------
On Feb 18, 2008, at 9:56 AM, Raymond O'Donnell wrote: > On 18/02/2008 17:46, Raymond O'Donnell wrote: > >> Well, that depends on your usage, so only you can answer that. >> According to the docs, "serial" creates an integer column, which >> will give you 2147483647 values - how quickly will you use that lot >> up? If you think you will run out, by all means use bigserial. > > Actually, that isn't quite right - just looked at the docs for > CREATE SEQUENCE, and the default maximum value is 2^63-1. That's the maximum value for the sequence itself, but a "serial" is just an integer, so the 2^31-1 limit before wraparound still applies. Cheers, Steve
On Feb 18, 2008 12:16 PM, Steve Atkins <steve@blighty.com> wrote: > > On Feb 18, 2008, at 9:56 AM, Raymond O'Donnell wrote: > > > On 18/02/2008 17:46, Raymond O'Donnell wrote: > > > >> Well, that depends on your usage, so only you can answer that. > >> According to the docs, "serial" creates an integer column, which > >> will give you 2147483647 values - how quickly will you use that lot > >> up? If you think you will run out, by all means use bigserial. > > > > Actually, that isn't quite right - just looked at the docs for > > CREATE SEQUENCE, and the default maximum value is 2^63-1. > > That's the maximum value for the sequence itself, but a "serial" is > just an integer, so the 2^31-1 limit before wraparound still applies. Look up bigserial
On Mon, 2008-02-18 at 17:46 +0000, Raymond O'Donnell wrote: > On 18/02/2008 13:14, pgsql_user wrote: > > so wouldnt I run out of ids one day, if there are lot of failed insert > > statements, lets say for every successful insert there are 50 > > unsuccessful inserts, so ids would be 1, 50, 100, and once I have > > thousands of rows, I will run out of IDs ? should I use bigserial > > instead ? > > Well, that depends on your usage, so only you can answer that. According > to the docs, "serial" creates an integer column, which will give you > 2147483647 values - how quickly will you use that lot up? If you think > you will run out, by all means use bigserial. > > Ray. > > --------------------------------------------------------------- > Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland > rod@iol.ie > --------------------------------------------------------------- > > ---------------------------(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 http://www.google.com/search?q=2147483647+seconds+to +years&ie=utf-8&oe=utf-8&aq=t&rls=com.ubuntu:en-US:official&client=firefox-a at one per second. 2 147 483 647 seconds = 68.0511039 years
On Feb 18, 1:14 pm, pgsql_user <amalt...@gmail.com> wrote: > On Feb 18, 6:08 pm, Paul Boddie <p...@boddie.org.uk> wrote: > > > > > On 18 Feb, 13:36, django_user <amalt...@gmail.com> wrote: > > > > How can stop postgresql from incrementing the primary key value, so > > > that even after many failed insert statements it get the next id val. > > > "Auto-incrementing" columns, typically implemented using the serial > > data type [1], employ sequences. > > > From the manual: > > > "To avoid blocking of concurrent transactions that obtain numbers from > > the same sequence, a nextval operation is never rolled back; that is, > > once a value has been fetched it is considered used, even if the > > transaction that did the nextval later aborts. This means that aborted > > transactions may leave unused "holes" in the sequence of assigned > > values. setval operations are never rolled back, either." > > >http://www.postgresql.org/docs/8.1/interactive/functions-sequence.html > > > In other words, to permit a decent level of concurrency, PostgreSQL > > doesn't wait to see if a transaction succeeds with a value from a > > sequence before updating the sequence. If you want to reset a sequence > > so that it always uses the next unused value as determined by looking > > at the table, I suppose you could do something like this: > > > select setval('mytable_id_seq', x) from (select max(id) as x from > > mytable) as y; > > > But I doubt that you would want to do this too often in any system > > with any reasonable level of concurrent access to the table or the > > sequence concerned. > > > Paul > > > [1]http://www.postgresql.org/docs/8.1/interactive/datatype.html#DATATYPE... > > so wouldnt I run out of ids one day, if there are lot of failed insert > statements, lets say for every successful insert there are 50 > unsuccessful inserts, so ids would be 1, 50, 100, and once I have > thousands of rows, I will run out of IDs ? should I use bigserial > instead ? > > Thanks In theory, yes. but the standard 4 byte integer can represent about 2 billion positive numbers so even with a lot of failed inserts you're probably not going to run out for years.
django_user wrote: >>>> How can stop postgresql from incrementing the primary key value, so >>>> that even after many failed insert statements it get the next id val. ,,, >> so wouldnt I run out of ids one day, if there are lot of failed insert >> statements, lets say for every successful insert there are 50 >> unsuccessful inserts, so ids would be 1, 50, 100, and once I have >> thousands of rows, I will run out of IDs ? should I use bigserial >> instead ? Gordon wrote: > In theory, yes. but the standard 4 byte integer can represent about 2 > billion positive numbers so even with a lot of failed inserts you're > probably not going to run out for years. First of all, sequenced keys are an artificial (surrogate) key. They should carry no meaning whatsoever to the data model or business logic. If they do carry meaning, then your code should manage the values rather than using an automatic sequence. Also, if you (after careful review of your analysis) still have restrictions that are not part of the auto-generated sequence mechanism, such as leaving no gaps in the sequence, you should implement your own sequence instead of wishing the automatic mechanism were different from what it is. Chances are good that the auto-genned sequence will work for you, if you think about it. If not, would you share why you anticipate that gaps will cause trouble for you? -- Lew