Groups > comp.databases.postgresql > #566

Re: serializable transactions across partitioned table

Path	csiph.com!newsfeed.hal-mli.net!feeder3.hal-mli.net!news.netfront.net!gonzo.reversiblemaps.ath.cx!not-for-mail
From	Jasen Betts <jasen@xnet.co.nz>
Newsgroups	comp.databases.postgresql
Subject	Re: serializable transactions across partitioned table
Date	15 May 2014 12:50:51 GMT
Organization	JJ's own news server
Lines	40
Message-ID	<ll2d7b$nr9$5@gonzo.reversiblemaps.ath.cx> (permalink)
References	<4d116141-a892-4040-844b-6abac769993c@googlegroups.com>
NNTP-Posting-Host	118.90.57.19
X-Trace	adenine.netfront.net 1400158797 97720 118.90.57.19 (15 May 2014 12:59:57 GMT)
X-Complaints-To	news@netfront.net
NNTP-Posting-Date	Thu, 15 May 2014 12:59:57 +0000 (UTC)
X-Face	?)Aw4rXwN5u0~$nqKj`xPz>xHCwgi^q+^?Ri+R(&uv2=E1Q0Zk(>h!~o2ID@6{uf8s;a +M[5[U[QT7xFN%^gR"=tuJw%TXXR'Fp~W;(T"1(739R%m0YyyvgkGoPA.$b,D.w:z+<'"=-lVT?6 {T?=R^:W5g\|E2#EhjKCa+nt":4b}dU7GYBHBxn&Td$@f%.kl^:7X8rQWd[NTc"P"u6nkisze/Q;8 "9Z{peQF,w)7UjV$c\|RO/mQW/NMgWfr5$-Z%u46"/00mx-,\R'fLPe.)^
User-Agent	slrn/pre1.0.0-18 (Linux)
Xref	csiph.com comp.databases.postgresql:566

Show key headers only | 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