Search Postgresql Archives

bigserial continuity safety

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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)


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux