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]http://www.postgresql.org/docs/9.2/static/sql-prepare-transaction.html
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Hi Sergey,
Thank you for your reply. Have you implemented this while sharding your
database? Did it cause any performance issues?
Warm regards,
GB
On Mon, Jul 15, 2013 at 10:51 AM, Sergey Konoplev <gray.ru@gmail.com> wrote:
Show quoted text
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 DBAProfile: 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,
GBOn 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 DBAProfile: 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general