Roll Back dont roll back counters

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/

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/

-----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&gt;

iQA/AwUBO3vJQ3ympNV/C086EQIU7gCg39S8h0M2l24/DU5H9CVJetYczYIAn18W
Bqke7grgimtbw4tdrdKV1whH
=Ura+
-----END PGP SIGNATURE-----

--- "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

"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&gt;

iQA/AwUBO3zzwHympNV/C086EQLiCgCdHWBEvPlk+ueJUwMPm8PBdnuHUbMAoMt7
J6HkkM3TYof9ehs4S0pGYUwu
=pASp
-----END PGP SIGNATURE-----

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&gt;

iQA/AwUBO30TzHympNV/C086EQJaAwCg2bh+rZOH1vMlUy6a42N4T15AJ5EAoKWz
Q1d0jSw6YyxcALUhhktFWb9E
=xJ4l
-----END PGP SIGNATURE-----