3 ms·
I'm sure someone will correct me if I'm wrong, but isn't it simpler to manage this sort of functionality by putting a counter in a table and using a CTE to incr
by Merad 5y ago
I'm sure someone will correct me if I'm wrong, but isn't it simpler to manage this sort of functionality by putting a counter in a table and using a CTE to increment it along with the insert? Something like
with u as (
update organizations
set last_external_id = last_external_id + 1
where id = 123
returning last_external_id
)
insert into incidents (organization_id, external_id, title)
select 123, last_external_id, 'Blah blah blah'
from u;
The risk here of course is running into lock contention around the organization table there's a high volume of incident creation for the same organization, but considering the context (incident management) that seems pretty low risk.
- stubish 5y agoThis approach depends on the level of transaction isolation being used, and relying on the higher levels is a foot gun best avoided unless you really know what you are doing and the consequences you are... erm... locking yourself into.
- Merad 5y agoCan you elaborate? The Postgres documentation leads me to believe this would work fine with the default (READ COMMITTED) isolation level. If multiple transactions attempt to modify the same row (incrementing the counter) they're forced to line up until previous updates to that row have been committed or rolled back, hence the risk of lock contention, but each transaction should consistent view of the counter.
- stubish 5y agoYes, I think you are right. The update locks the row containing the last id, and concurrent transactions block if they too need to grab the lock. (so performance wise, a sequence is still better due to less locking, but if you can't have holes it seems fine)