Hi.
If I have a table created as:
CREATE TABLE xq_agr (
id BIGSERIAL PRIMARY KEY,
node text not null
);
and that multiple applications insert into. The applications never explicitly specify the value for 'id'.
Is it safe to, on a single connection, do:
- open transaction (default transaction isolation)
- Open cursor for select * from xq_agr order by id asc
- do something with current record
- advance the cursor (and repeat something), but stop at some point (id = LAST_ID), and
- delete from xq_agr where id <= LAST_ID;
- commit
"safe to" means - whether the cursor will not miss any records that were deleted at the end.
I'm suspecting that depending on the commit order, I may have situations when:
- TX1 insert ID 1
- TX2 insert ID 2
- TX2 commits
- TX3 scans 2
- TX1 commits
- TX3 deletes <= 2
- record ID1 is deleted, but never processed.
Is that sequence of events as listed above possible? If yes, is there a transaction isolation I can use to avoid that?
Table and sequence definition, as present in the DB:
db=> \d+ xq_agr_id_seq
Sequence "public.xq_agr_id_seq"
Column | Type | Value | Storage
---------------+---------+---------------------+---------
sequence_name | name | xq_agr_id_seq | plain
last_value | bigint | 139898829 | plain
start_value | bigint | 1 | plain
increment_by | bigint | 1 | plain
max_value | bigint | 9223372036854775807 | plain
min_value | bigint | 1 | plain
cache_value | bigint | 1 | plain
log_cnt | bigint | 27 | plain
is_cycled | boolean | f | plain
is_called | boolean | t | plain
db=> \d xq_agr
Table "public.xq_agr"
Column | Type | Modifiers
-------------------+---------+-----------------------------------------------------
id | bigint | not null default nextval('xq_agr_id_seq'::regclass)
node | text | not null
Indexes:
"xq_agr_pkey" PRIMARY KEY, btree (id)