Thread: Roll Back dont roll back counters
If I design a table with SERIAL type and then try to insert few rows, but some of the rows are rolled back the roll back process does not roll back the counter status. Is there a way to do a roll back to the counter ? -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il --------------------------
On Thu, Aug 16, 2001 at 01:15:13PM +0300, Ben-Nes Michael wrote: > If I design a table with SERIAL type and then try to insert few rows, but > some of the rows are rolled back the roll back process does not roll back > the counter status. > > Is there a way to do a roll back to the counter ? Nope. You can use setval, but you have no guarentees. Think about it. If you had to roll back the counter if the transaction failed, you would lose concurrency. Every transaction would have to wait until the previous one finished to find out what number it will get. Why do people care about holes anyway? I've never understood that... -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
From: "Ben-Nes Michael" <miki@canaan.co.il> > If I design a table with SERIAL type and then try to insert few rows, but > some of the rows are rolled back the roll back process does not roll back > the counter status. > > Is there a way to do a roll back to the counter ? No - sequences (as used by the serial type) are merely guaranteed to provide unique (increasing) numbers to each backend. They aren't designed for producing (always) sequential numbers. Check the mail archives for a long discussion about this with regard to invoice numbers. - Richard Huxton
On Thu, 16 Aug 2001, Martijn van Oosterhout wrote: > Why do people care about holes anyway? I've never understood that... The single seat syndrome? (It's my database and I'm the only one using it.) Trying to put intelligence into the field? (I can tell the order the entries were made in the table using this field.) Rod --
You still can tell the order even if you have holes in the key... Carlos Felipe Zirbes DBServer Assessoria em Sistemas de Informação E-mail: carlosz@dbserver.com.br Fone: (51) 3342-8055 Fax: (51) 3342-4838 -----Original Message----- From: Roderick A. Anderson [mailto:raanders@tincan.org] Sent: quinta-feira, 16 de agosto de 2001 09:27 To: Martijn van Oosterhout Cc: Ben-Nes Michael; pgsql-general@postgresql.org Subject: Re: [GENERAL] Roll Back dont roll back counters On Thu, 16 Aug 2001, Martijn van Oosterhout wrote: > Why do people care about holes anyway? I've never understood that... The single seat syndrome? (It's my database and I'm the only one using it.) Trying to put intelligence into the field? (I can tell the order the entries were made in the table using this field.) Rod -- ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
On Thu, Aug 16, 2001 at 05:26:55AM -0700, Roderick A. Anderson wrote: > On Thu, 16 Aug 2001, Martijn van Oosterhout wrote: > > > Why do people care about holes anyway? I've never understood that... > > The single seat syndrome? (It's my database and I'm the only one using > it.) Well, there may be something to that. But that's just neatness issues. > Trying to put intelligence into the field? (I can tell the order the > entries were made in the table using this field.) You can tell the order anyway. The order doesn't change, it's only that there may be numbers missing, Unless they want to use it as a shortcut for count(*). Primarys keys should be opaque. Any meaning read into them is entirely coincidental. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > It would be nice if someone came up with a certification system that > actually separated those who can barely regurgitate what they crammed over > the last few weeks from those who command secret ninja networking powers.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
You can only guarantee the order if the sequence caching in the
session is 1 (You can set it higher, can't you?). If any sequence
numbers are cached, then you can't even guarantee the order, only
uniqueness.
Cheers...
MikeA
>> -----Original Message-----
>> From: Carlos Felipe Zirbes [mailto:carlosz@dbserver.com.br]
>> Sent: 16 August 2001 14:06
>> To: pgsql-general@postgresql.org
>> Subject: RE: [GENERAL] Roll Back dont roll back counters
>>
>>
>> You still can tell the order even if you have holes in the key...
>>
>> Carlos Felipe Zirbes
>> DBServer Assessoria em Sistemas de Informação
>> E-mail: carlosz@dbserver.com.br
>> Fone: (51) 3342-8055
>> Fax: (51) 3342-4838
>>
>>
>>
>> -----Original Message-----
>> From: Roderick A. Anderson [mailto:raanders@tincan.org]
>> Sent: quinta-feira, 16 de agosto de 2001 09:27
>> To: Martijn van Oosterhout
>> Cc: Ben-Nes Michael; pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Roll Back dont roll back counters
>>
>>
>> On Thu, 16 Aug 2001, Martijn van Oosterhout wrote:
>>
>> > Why do people care about holes anyway? I've never
>> understood that...
>>
>> The single seat syndrome? (It's my database and I'm the
>> only one using
>> it.)
>>
>> Trying to put intelligence into the field? (I can tell the order
>> the entries were made in the table using this field.)
>>
>>
>> Rod
>> --
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an
>> appropriate subscribe-nomail command to majordomo@postgresql.org
>> so that your message can get through to the mailing list cleanly
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an
>> appropriate subscribe-nomail command to majordomo@postgresql.org
>> so that your message can get through to the mailing list cleanly
>>
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>
iQA/AwUBO3vJQ3ympNV/C086EQIU7gCg39S8h0M2l24/DU5H9CVJetYczYIAn18W
Bqke7grgimtbw4tdrdKV1whH
=Ura+
-----END PGP SIGNATURE-----
_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________
--- "Roderick A. Anderson" <raanders@tincan.org> wrote: > On Thu, 16 Aug 2001, Martijn van Oosterhout wrote: > > > Why do people care about holes anyway? I've never > understood that... > > The single seat syndrome? (It's my database and I'm > the only one using > it.) If this is the case then simply use the setval command, insert with an explicit value in the serial column, or better yet, don't rollback or abort transactions :). > Trying to put intelligence into the field? (I can > tell the order the > entries were made in the table using this field.) You can tell the order the entries were made whether or not their are "holes" in your sequence. No matter how many aborted transactions you might have had the bigger sequence numbers were inserted last :). A simple "SELECT * FROM my_table ORDER BY my_serial_field" will happily sort your table chronologically. On the other hand, you could probably use a combination of explicit locks a non-SERIAL integer primary key, and a select statement like "SELECT my_primary_key FROM my_table ORDER BY my_primary_key DESC LIMIT 1" to fetch your current highest primary key value. You could then add one to this number on your insert. It would serialize inserts to your table (not a big deal if you are the only one using it), and it would require more work (and more discipline) when programming, but you wouldn't get any holes. Good Luck, Jason __________________________________________________ Do You Yahoo!? Make international calls for as low as $.04/minute with Yahoo! Messenger http://phonecard.yahoo.com/
On Thu, 16 Aug 2001, Martijn van Oosterhout wrote: > You can tell the order anyway. The order doesn't change, it's only that > there may be numbers missing, Please note the message from "Michael Ansley (UK)". If two of more connections get a cache from a sequence and the inserts are 'sporatic' then there will be a difference in the sequence number and the insert order. > Primarys keys should be opaque. Any meaning read into them is entirely > coincidental. I agree. Rod --
> If I design a table with SERIAL type and then try to insert few rows, but > some of the rows are rolled back the roll back process does not roll back > the counter status. > > Is there a way to do a roll back to the counter ? And I suppose if I deleted some rows you'd want all rows with higher values for the SERIAL data-typed column to renumber automatically... :-) Cheers, Colin
Its not that I care about holes, but if roll back mean to return the DB to its original status because a foul sql chain command then logically I expect that everything will roll back like the action was never accorded > Why do people care about holes anyway? I've never understood that... > -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il --------------------------
On one hand, I'm inclined to agree with you, maintaining state for a rolled back transaction makes sense. But by trying to track sequence values by transaction and reusing rolled back values, you destroy the advantages of using a sequence in the first place, as well as making things far, far more complicated in the long run. For example, you have two transactions, A and B. B is committed while A is rolled back. If you grab the sequence values when the queries are executed, A had a value of 1 and B used the value of 2. So the sequence should issue a nextval of 3. But A was rolled back. Does this mean that the sequence now has to track every value in use? Alternately, if the values are assigned when the transaction is committed, you have the problems of an undefined value during the transaction. How can you perform integrity checks on an undefined value? The idea of a database returning to a different state upset me at first. But after some thought and consideration, I realized that there really is no better way to handle it, other than creating your own, far slower, far more complicated system. Because you have several potential concurrent processes reading and writing to a single value, you just can't expect to return that value to its original state. Greg > Its not that I care about holes, but if roll back mean to return the DB to > its original status because a foul sql chain command then logically I expect > that everything will roll back like the action was never accorded > > > Why do people care about holes anyway? I've never understood that...
"Ben-Nes Michael" <miki@canaan.co.il> writes:
> Its not that I care about holes, but if roll back mean to return the DB to
> its original status because a foul sql chain command then logically I expect
> that everything will roll back like the action was never accorded
That's the general rule, but we specifically exempt sequences from the
rule, because it works better that way. Allowing sequence operators
to be non-blocking is considerably more useful for most people than
avoiding holes in the set of resulting row IDs.
All of this is documented in the CREATE SEQUENCE reference page, BTW.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Um, no, primary keys should not always be opaque. Technical primary
keys should always be opaque. Relational modelling has been built to
exploit primary keys actually being the business key, to create
proper identifying relationships. Using technical PKs undermines
this, although often this is offset by carrying large primary keys
through to child tables. For most simple to moderately complex
tables, I prefer using the business key as the primary key, and only
using a technical key when it's really (REALLY) warranted. How do I
differentiate? Well, common sense is probably the best. 'Address'
gets a technical ID, because it's business key would be cumbersome,
the same with 'person'. But 'Network Operator' already has a five
character business key, and that's adequate without a technical key,
as is the seven character 'Tier' business key, and the twenty
character 'Point of Interconnect' key, even though they could all
feasibly take a technical ID.
And when you're trying to read an ER model, technical keys are a pain
in the butt, because it becomes incredibly difficult to read meaning
into the model. With matching business and primary keys, reading an
ER model is a doddle. Almost...
MikeA
>> -----Original Message-----
>> From: Roderick A. Anderson [mailto:raanders@tincan.org]
>> Sent: 16 August 2001 15:37
>> To: Martijn van Oosterhout
>> Cc: Ben-Nes Michael; pgsql-general@postgresql.org
>> Subject: Re: [GENERAL] Roll Back dont roll back counters
>>
>>
>> On Thu, 16 Aug 2001, Martijn van Oosterhout wrote:
>>
>> > You can tell the order anyway. The order doesn't change,
>> it's only that
>> > there may be numbers missing,
>>
>> Please note the message from "Michael Ansley (UK)". If two of
>> more connections get a cache from a sequence and the inserts are
>> 'sporatic'
>> then there will be a difference in the sequence number and the
>> insert order.
>>
>> > Primarys keys should be opaque. Any meaning read into them
>> is entirely
>> > coincidental.
>>
>> I agree.
>>
>>
>> Rod
>> --
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 3: if posting/reading through Usenet, please send an
>> appropriate subscribe-nomail command to majordomo@postgresql.org
>> so that your message can get through to the mailing list cleanly
>>
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>
iQA/AwUBO3zzwHympNV/C086EQLiCgCdHWBEvPlk+ueJUwMPm8PBdnuHUbMAoMt7
J6HkkM3TYof9ehs4S0pGYUwu
=pASp
-----END PGP SIGNATURE-----
_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________
On Fri, Aug 17, 2001 at 11:37:33AM +0100, "Michael Ansley (UK)" <Michael.Ansley@intec-telecom-systems.com> wrote: > > Um, no, primary keys should not always be opaque. Technical primary > keys should always be opaque. Relational modelling has been built to I think it has more to do with efficiency and whether or not you ever expect the primary key values to change, then how hard it is to read an ER diagram.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Yes, of course. But you have to have pretty stringent performance
requirements to justify changing to technical IDs as a matter of
rule. Reading an ER model is pretty important too, from a
maintenance perspective, though. I've been involved in doing that
for a while now, and trying to read business meaning into some of the
data models that I've worked with is pretty near impossible, which
makes the maintenance designer's job really difficult. You have to
speak to all the original people involved in the system design, and
sometimes that is worse than simply redoing the whole thing.
Anyway, a discourse on ER design is a pretty subjective area, which
we should probably not indulge in at present ;-)
MikeA
>> -----Original Message-----
>> From: Bruno Wolff III [mailto:bruno@wolff.to]
>> Sent: 17 August 2001 13:09
>> To: Michael Ansley (UK)
>> Cc: pgsql-general@postgresql.org
>> Subject: Re: Roll Back dont roll back counters
>>
>>
>> On Fri, Aug 17, 2001 at 11:37:33AM +0100,
>> "Michael Ansley (UK)"
>> <Michael.Ansley@intec-telecom-systems.com> wrote:
>> >
>> > Um, no, primary keys should not always be opaque.
>> Technical primary
>> > keys should always be opaque. Relational modelling has
>> been built to
>>
>> I think it has more to do with efficiency and whether or not you
>> ever expect the primary key values to change, then how hard it is
>> to read an
>> ER diagram.
>>
-----BEGIN PGP SIGNATURE-----
Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com>
iQA/AwUBO30TzHympNV/C086EQJaAwCg2bh+rZOH1vMlUy6a42N4T15AJ5EAoKWz
Q1d0jSw6YyxcALUhhktFWb9E
=xJ4l
-----END PGP SIGNATURE-----
_________________________________________________________________________
This e-mail and any attachments are confidential and may also be privileged and/or copyright
material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an
intended or authorised recipient of this e-mail or have received it in error, please delete
it immediately and notify the sender by e-mail. In such a case, reading, reproducing,
printing or further dissemination of this e-mail is strictly prohibited and may be unlawful.
Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free
from computer viruses or other defects. The opinions expressed in this e-mail and any
attachments may be those of the author and are not necessarily those of Intec Telecom
Systems PLC.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
__________________________________________________________________________