Thread: Transaction control in shards through PLPROXY

Hi,

Even though this mailing list is for postgresql users only, I am sending this query 

related to postgresql sharding in case anyone has already faced this problem while 

sharding their database. 

Inspite of being aware that PLPROXY does autocommit for DML functions called
on shards, I was wondering if there is any way around to put a set of DML
functions called from Router on shards inside a transaction so that all
updates on shards can be rolled back if any one among the set fails?

It would be great if anyone has already done this in their sharded database.
I was looking into transaction manager but it handles only one DML function
at a time.

Many Thanks,
GB

On Wed, Jul 10, 2013 at 10:20 PM, Granthana Biswas
<granthana.biswas@gmail.com> wrote:
> Inspite of being aware that PLPROXY does autocommit for DML functions called
> on shards, I was wondering if there is any way around to put a set of DML
> functions called from Router on shards inside a transaction so that all
> updates on shards can be rolled back if any one among the set fails?

It is called two-phase commit. You need to consult with this [1]
section of documentation.

[1] http://www.postgresql.org/docs/9.2/static/sql-prepare-transaction.html


--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com

On Sun, Jul 14, 2013 at 10:56 PM, Granthana Biswas
<granthana.biswas@gmail.com> wrote:
> Thank you for your reply. Have you implemented this while sharding your
> database? Did it cause any performance issues?

I used it for just several mission critical featured in one project,
not widely. What about performance issues - it could cause ones,
mostly if somebody forget to do commit/rollback as it still holds
locks. Moreover it involves application<->DBs communications and
persistence, so it is surely might affect performance.

>
> Warm regards,
> GB
>
>
> On Mon, Jul 15, 2013 at 10:51 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
>>
>> On Wed, Jul 10, 2013 at 10:20 PM, Granthana Biswas
>> <granthana.biswas@gmail.com> wrote:
>> > Inspite of being aware that PLPROXY does autocommit for DML functions
>> > called
>> > on shards, I was wondering if there is any way around to put a set of
>> > DML
>> > functions called from Router on shards inside a transaction so that all
>> > updates on shards can be rolled back if any one among the set fails?
>>
>> It is called two-phase commit. You need to consult with this [1]
>> section of documentation.
>>
>> [1] http://www.postgresql.org/docs/9.2/static/sql-prepare-transaction.html
>>
>>
>> --
>> Kind regards,
>> Sergey Konoplev
>> PostgreSQL Consultant and DBA
>>
>> Profile: http://www.linkedin.com/in/grayhemp
>> Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
>> Skype: gray-hemp
>> Jabber: gray.ru@gmail.com
>
>



--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray.ru@gmail.com