Thread: About sequences that works BAD !!!!

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello everyone:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size:
10.0pt;font-family:Arial">We are working with serials fields and we found a problem with then: When we insert in a
tablethat has e unique restrict, and this makes insert fails, the sequence increments anyway…¿What we can do about
it?</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial">We hope you can help us…..</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanlang="EN-GB" style="font-size: 
10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Times New Roman" size="3"><span
style="font-size:12.0pt">AlexisPalma Espinosa</span></font>. <p class="MsoNormal"><font face="Times New Roman"
size="3"><spanstyle="font-size: 
12.0pt">Ingeniero en Informática.</span></font><p class="MsoNormal"><i><font face="Times New Roman" size="4"><span
lang="EN-GB"style="font-size:14.0pt;font-style:italic">"If you are not part of the solution...you are part of the
problem"</span></font></i><i><fontsize="4"><span lang="EN-GB"
style="font-size:14.0pt;font-style:italic"></span></font></i><pclass="MsoNormal"><font face="Times New Roman"
size="3"><spanlang="EN-GB" style="font-size:12.0pt"> </span></font></div> 

Alexis Palma Espinosa wrote:
> Hello everyone:
>
>
>
> We are working with serials fields and we found a problem with then:
> When we insert in a table that has e unique restrict, and this makes
> insert fails, the sequence increments anyway...¿What we can do about
> it?

Nothing. The whole point of sequences is that they don't lock. They *do*
guarantee unique numbers, but they *do not* guarantee no gaps.

If you really want a series of ID numbers with no gaps you'll want to do
something like:

1. Begin transaction
2. Lock table exclusively
3. Find highest existing ID (...ORDER BY id DESC LIMIT 1)
4. Add one to it
5. Store new row.
6. commit transaction, freeing the lock

--   Richard Huxton  Archonet Ltd

On Wednesday 14 June 2006 02:02 pm, "Alexis Palma Espinosa" <apalma@uci.cu>
thus communicated:
--> Hello everyone:
-->
-->
-->
--> We are working with serials fields and we found a problem with then: Whenwe insert in a table that has e unique
restrict,and this makes insertfails, the sequence increments anyway...¿What we can do about it? --> 
-->
-->
--> We hope you can help us.....
-->
-->
-->

This is doing exactly what it is supposed to do. See the docs:
http://www.postgresql.org/docs/7.4/static/functions-sequence.html