On 3/20/20 4:29 PM, Peter J. Holzer wrote:
On 2020-03-20 17:11:42 -0600, Rob Sargent wrote:
On Mar 20, 2020, at 4:59 PM, Peter J. Holzer <hjp-pgsql@xxxxxx> wrote:
On 2020-03-19 16:48:19 -0700, David G. Johnston wrote:
First, it sounds like you care about there being no gaps in the records you end
up saving. If that is the case then sequences will not work for you.
I think (but I would love to be proven wrong), that *nothing* will work
reliably, if
1) you need gapless numbers which are strictly allocated in sequence
2) you have transactions
3) you don't want to block
Rationale:
Regardless of how you get the next number, the following scenario is
always possible:
[...]
At this point you have a gap.
If you can afford to block, I think a simple approach like
[...]
should work. But that effectively serializes your transactions and may
cause some to be aborted to prevent deadlocks.
OP has said small gaps are ok.
Yes. This wasn't a response to the OP's requirements, but to David's
(rather knee-jerk, IMHO) "don't use sequences" response. Very often the
requirements which would preclude sequences also preclude any other
solution.
I don't see a knee-jerk reaction in this:
https://www.postgresql.org/message-id/CAKFQuwZ%3D%3Dri5_m2geFA-GPOdfnVggmJRu3zEi%2B1EwJdJA%3D9AeQ%40mail.gmail.com
The response was if you cared about gaps(not something the OP had
specified at that point) then a sequence would not work. If not then
they where something that would need testing to determine suitability.
(In the case of the OP's problem, I'd agree that sequences are probably
a bad idea for the reasons he anticipates)
To me that says the requirement
Which requirement? The OP's or the one I posed here?
is capricious but we haven’t heard the rationale for the requirement
yet (or I missed it)
The requirement is that (group, element) pairs have a sequence
number/code so:
1,1,1
1,1,2
1,1,3
2,2,1
2,2,2
The OP gave a rationale: He has to fit the counter into an 8-digit
field, and a global counter would overflow that. So he needs per-element
counters.
I must have missed that post. There was this(and alternates):
CREATE TABLE counter(
group INT NOT NULL,
element INT NOT NULL,
seq_number INT NOT NULL default 0,
CONSTRAINT PRIMARY KEY (group, element)
);
Nothing I saw that said int could not become bigint.
hp
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
