0

I have a log file, which holds a customerid, and an event type. I want to add a new column called sequence, which should be a increasing number, and should be unique per customer.

The table is append-only, record-by-record, no transactions, and I'd like the sequence number to be computed by a default function on the column. What is the least CPU intensive way to accomplish this?

I tried googling but there are just too many hits on 'auto-increment', 'sequence', and 'serial' for the simple cases.

raarts
  • 101
  • 2
  • No this does not answer my question. I need the records numbered per customer, so for each customer the counter value should start at 1 and increment separately. The next record with the same customerid would be #2 etcetera. I don't see how this can be accomplished using identity. – raarts Jun 16 '20 at 23:07
  • 1
    Why do you think you need the records numbered per customer? What is your use case? I am asking you this, because yours is potentially an X-Y Problem™ and you might get better answers if you descrbe what you're trying to achieve instead of how you're trying to achieve that. – mustaccio Jun 17 '20 at 00:00
  • I want to use postgres as the stream for event sourcing, and number the events for each stream (customer) seperately so I can use optimistic concurrency control, so I can be sure I am working with the latest event. https://youtu.be/GzrZworHpIk?t=863 – raarts Jun 17 '20 at 10:29
  • Well, for this use case a single sequence common for all customers fits the bill all right, because it will guarantee that for each customer the value will be monotonically increasing (though not necessarily without gaps, but that's irrelevant). – mustaccio Jun 17 '20 at 11:28
  • It is very relevant because precisely when there's a gap you assume another event was inserted in the mean time, and you stop processing. BTW I've had another use case in the past. It had to do with phone calls. If a call if transferred, the call keeps the same uniqueid, but you want the second connection top have a sequence number '2' within the callflow. And '3 after the call is transferred again. At the time I did it in software but that's not possible here. I really think this is a legit use case. – raarts Jun 18 '20 at 19:04

0 Answers0