From: | Pawel Veselov <pawel(dot)veselov(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | bigserial continuity safety |
Date: | 2015-04-13 22:05:30 |
Message-ID: | CAMnJ+BcCrRiBKZCn2zarqo9xBNajfbzxTWmo11y1Ua2hdbk+rg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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)
From | Date | Subject | |
---|---|---|---|
Next Message | Guillaume Drolet | 2015-04-13 23:37:06 | recovery of a windows archive in linux |
Previous Message | Guillaume Lelarge | 2015-04-13 20:05:40 | Re: Hot standby problems: consistent state not reached, no connection to master server. |