select/update performance?

[Bjørn T Johansen]

I need to maintain a manually counter for an id-field, but I can do this
two ways. Either make a counter table (which means one select and one
update) or just selecting the largest id from existing table and
increment by one (just one select + one table lock). Which one is
fastest?
Regards,

BTJ
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Rob Fielding]

Bjørn T Johansen wrote:

I need to maintain a manually counter for an id-field, but I can do this
two ways. Either make a counter table (which means one select and one
update) or just selecting the largest id from existing table and
increment by one (just one select + one table lock). Which one is
fastest?

Is would be better to create a SEQUENCE and simply call nextval on it.
Then you are assured that you'll get a unique sequence when working in a
concurrent environment.

It would also be guaranteed faster than interrogating tables.

Hope this helps,

--

Rob Fielding
Development
Designer Servers Ltd
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Bjørn T Johansen]

Yes, but the table in question have 3 PK and only one that needs this
"sequence" so I just thought instead of getting holes in the IDs I just
manually handle this counter somehow.. Not a big deal but... :)
BTJ

On Wed, 2003-11-05 at 10:42, Rob Fielding wrote:

Bjørn T Johansen wrote:

I need to maintain a manually counter for an id-field, but I can do this
two ways. Either make a counter table (which means one select and one
update) or just selecting the largest id from existing table and
increment by one (just one select + one table lock). Which one is
fastest?

Is would be better to create a SEQUENCE and simply call nextval on it.
Then you are assured that you'll get a unique sequence when working in a
concurrent environment.

It would also be guaranteed faster than interrogating tables.

Hope this helps,

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Csaba Nagy]

You are buying yourself trouble. The holes in the IDs are the least
problem, and a generated sequence should not have any business meaning
anyway. BTW, what happens when you delete a record ? That would surely
leave you a hole in the IDs...
Just a quick thought: if you are going to handle manually the ID
creation, and lock rows/tables to do that, you will effectively
serialize all transactions which create ids, because the locks are held
until the transactions finish. Not to mention increased deadlock
probability.
Sequences were designed to overcome all these problems, so why not just
use them ?

Cheers,
Csaba.
On Wed, 2003-11-05 at 10:49, Bjørn T Johansen wrote:

Yes, but the table in question have 3 PK and only one that needs this
"sequence" so I just thought instead of getting holes in the IDs I just
manually handle this counter somehow.. Not a big deal but... :)
BTJ

On Wed, 2003-11-05 at 10:42, Rob Fielding wrote:

Bjørn T Johansen wrote:

I need to maintain a manually counter for an id-field, but I can do this
two ways. Either make a counter table (which means one select and one
update) or just selecting the largest id from existing table and
increment by one (just one select + one table lock). Which one is
fastest?

Is would be better to create a SEQUENCE and simply call nextval on it.
Then you are assured that you'll get a unique sequence when working in a
concurrent environment.

It would also be guaranteed faster than interrogating tables.

Hope this helps,

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Richard Huxton]

On Wednesday 05 November 2003 09:49, Bjørn T Johansen wrote:

Yes, but the table in question have 3 PK and only one that needs this
"sequence" so I just thought instead of getting holes in the IDs I just
manually handle this counter somehow.. Not a big deal but... :)

Do you mean a 3-column primary key? By definition you can't have more than one
primary key.

If you absolutely need to have no holes in your sequence numbers (e.g. for
invoices) then you will have to handle it yourself. If you can live with
them, it is much easier and quicker to use a sequence.

If you don't want holes, the simplest way is probably to have a "next_id"
column in a system-settings table. You'll need to lock it before
reading/updating and this will be a bottleneck when inserting new rows.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Rob Fielding]

Bjørn T Johansen wrote:

Yes, but the table in question have 3 PK and only one that needs this
"sequence" so I just thought instead of getting holes in the IDs I just
manually handle this counter somehow.. Not a big deal but... :)

You'd only get holes if you keep making nextval requests without using
the value - say by issuing rollback. The problem with holes is actually
the feature of uniqueness SEQUENCES provides. Perhaps you judge that
there is too high a chance of rollback to create a sufficient number of
holes to warrant not using a SEQUENCE.

It's all down to your application and specific situation I guess however
your counter table idea sounds exactly like what SEQUENCE provides,
without any of the guarantees.

I think I'd still recommend using a SEQUENCE for anything but the most
profound reason :)

--

Rob Fielding
ro*@dsvr.net Development Designer Servers Ltd
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Bjørn T Johansen]

Ok you all, I cave... I will use sequences.... :)

BTJ

On Wed, 2003-11-05 at 11:46, Rob Fielding wrote:

Bjørn T Johansen wrote:

Yes, but the table in question have 3 PK and only one that needs this
"sequence" so I just thought instead of getting holes in the IDs I just
manually handle this counter somehow.. Not a big deal but... :)

You'd only get holes if you keep making nextval requests without using
the value - say by issuing rollback. The problem with holes is actually
the feature of uniqueness SEQUENCES provides. Perhaps you judge that
there is too high a chance of rollback to create a sufficient number of
holes to warrant not using a SEQUENCE.

It's all down to your application and specific situation I guess however
your counter table idea sounds exactly like what SEQUENCE provides,
without any of the guarantees.

I think I'd still recommend using a SEQUENCE for anything but the most
profound reason :)

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)