Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #566

Re: serializable transactions across partitioned table

Show all headers | View raw

On 2014-05-14, arsenal <naren.chainani@gmail.com> wrote:
> Hi everyone,
>
> Do serializable transactions work across a partitioned table?
>
> Below is an example that demonstrates my question.
>
> 1. Assume table T with two columns (id, amount)
> 2. We partition the table T daily at midnight
> 3. T has one row in the starting state
> id: 1, amount: 10
> 4. Two events come in around two seconds apart (right around partitioning)
> 4a. Event 1 at 23:59:59 runs this in serializable transaction
> begin transaction (Tx 1)
> select sum(amount) as new_amount from T where id=1
> # new_amount is 10
> insert into T1 values (id, new_amount) if new_amount=10
> 4b. Event 2 at 00:00:01 runs this in serializable transaction
> begin transaction (Tx 2)
> select sum(amount) as new_amount from T where id=1
> # new_amount is 10 because Tx 1 hasn't committed yet
> insert into T2 values (id, new_amount) if new_amount=10
> 5. Given that the two transactions were inserting in different
> partitioned tables, when the two serial transactions commit
> eventually, will they be serialized? If they are serialized, one of
> them will be rolled back - correct?   

the select however hits both tables, inheritace effectivley rewrites
it as a union.

select sum(amount) as new_amount from  ( select amount from only T1
union all select amount from only T2 only union all select amount from only T )

so one of those is going to fail, probably the second one.

-- 
umop apisdn


--- news://freenews.netfront.net/ - complaints: news@netfront.net ---

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

serializable transactions across partitioned table arsenal <naren.chainani@gmail.com> - 2014-05-14 10:09 -0700
  Re: serializable transactions across partitioned table Jasen Betts <jasen@xnet.co.nz> - 2014-05-15 12:50 +0000
  Re: serializable transactions across partitioned table Robert Klemme <shortcutter@googlemail.com> - 2014-05-17 12:52 +0200

csiph-web