Re: [HACKERS] Transactions involving multiple postgres foreign servers

On 06.11.2015 21:37, Robert Haas wrote: On Wed, Aug 12, 2015 at 6:25 AM, Ashutosh Bapat wrote: The previous patch would not compile on the latest HEAD. Here's updated patch. Perhaps unsurprisingly, this doesn't apply any more. But we have bigger things to

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On 09.11.2015 09:59, Ashutosh Bapat wrote: Since the foreign server (referred to in the slides as secondary server) requires to call "create extension pg_dtm" and select dtm_join_transaction(xid);, I assume that the foreign server has to be a PostgreSQL server and one which has this

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sat, Nov 7, 2015 at 12:07 AM, Robert Haas wrote: > On Wed, Aug 12, 2015 at 6:25 AM, Ashutosh Bapat > wrote: > > The previous patch would not compile on the latest HEAD. Here's updated > > patch. > > Perhaps unsurprisingly, this doesn't

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sat, Nov 7, 2015 at 12:52 PM, Amit Kapila wrote: > > On Sat, Nov 7, 2015 at 12:07 AM, Robert Haas wrote: > > > > On the whole, I'm inclined to think that the XTM-based approach is > > probably more useful and more general, if we can work out the

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sat, Nov 7, 2015 at 12:07 AM, Robert Haas wrote: > > On Wed, Aug 12, 2015 at 6:25 AM, Ashutosh Bapat > wrote: > > The previous patch would not compile on the latest HEAD. Here's updated > > patch. > > Perhaps unsurprisingly, this doesn't

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Wed, Aug 12, 2015 at 6:25 AM, Ashutosh Bapat wrote: > The previous patch would not compile on the latest HEAD. Here's updated > patch. Perhaps unsurprisingly, this doesn't apply any more. But we have bigger things to worry about. The recent eXtensible

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On 2015-08-05 AM 06:11, Robert Haas wrote: On Mon, Aug 3, 2015 at 8:19 PM, Amit Langote langote_amit...@lab.ntt.co.jp wrote: On 2015-08-03 PM 09:24, Ashutosh Bapat wrote: For postgres_fdw it's a boolean server-level option 'twophase_compliant' (suggestions for name welcome). How about just

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Mon, Aug 3, 2015 at 8:19 PM, Amit Langote langote_amit...@lab.ntt.co.jp wrote: On 2015-08-03 PM 09:24, Ashutosh Bapat wrote: On Sat, Aug 1, 2015 at 12:18 AM, Robert Haas robertmh...@gmail.com wrote: OK, sure. But let's make sure postgres_fdw gets a server-level option to control this.

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On 2015-08-03 PM 09:24, Ashutosh Bapat wrote: On Sat, Aug 1, 2015 at 12:18 AM, Robert Haas robertmh...@gmail.com wrote: OK, sure. But let's make sure postgres_fdw gets a server-level option to control this. For postgres_fdw it's a boolean server-level option 'twophase_compliant'

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sat, Aug 1, 2015 at 12:18 AM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jul 31, 2015 at 6:33 AM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: I'm not hung up on the table-level attribute, but I think having a server-level attribute rather than a global GUC is a good idea.

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Tue, Feb 17, 2015 at 2:56 PM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: 2. New catalog - This method takes out the need to have separate method for C1, C5 and even C2, also the synchronization will be taken care of by row locks, there will be no limit on the number of foreign

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Fri, Jul 31, 2015 at 6:33 AM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: I'm not hung up on the table-level attribute, but I think having a server-level attribute rather than a global GUC is a good idea. However, I welcome other thoughts on that. The patch supports server level

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Thu, Jul 30, 2015 at 1:52 AM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jul 29, 2015 at 6:58 AM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: A user may set atomic_foreign_transaction to ON to guarantee atomicity, IOW it throws error when atomicity can not be guaranteed.

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Wed, Jul 29, 2015 at 6:58 AM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: A user may set atomic_foreign_transaction to ON to guarantee atomicity, IOW it throws error when atomicity can not be guaranteed. Thus if application accidentally does something to a foreign server, which

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Overall, you seem to have made some significant progress on the design since the last version of this patch. There's probably a lot left to do, but the design seems more mature now. I haven't read the code, but here are some comments based on the email. On Thu, Jul 9, 2015 at 6:18 AM, Ashutosh

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On 02/17/2015 11:26 AM, Ashutosh Bapat wrote: Hi All, Here are the steps and infrastructure for achieving atomic commits across multiple foreign servers. I have tried to address most of the concerns raised in this mail thread before. Let me know, if I have left something. Attached is a WIP

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Added to 2015-06 commitfest to attract some reviews and comments. On Tue, Feb 17, 2015 at 2:56 PM, Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: Hi All, Here are the steps and infrastructure for achieving atomic commits across multiple foreign servers. I have tried to address most

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Hi All, Here are the steps and infrastructure for achieving atomic commits across multiple foreign servers. I have tried to address most of the concerns raised in this mail thread before. Let me know, if I have left something. Attached is a WIP patch implementing the same for postgres_fdw. I have

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Thu, Jan 8, 2015 at 1:00 PM, Kevin Grittner kgri...@ymail.com wrote: Clearly, all the nodes other than the local one need to use 2PC. I am unconvinced that the local node must write a 2PC state file only to turn around and remove it again almost immediately thereafter. The key point is

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sun, Jan 11, 2015 at 3:36 AM, Michael Paquier michael.paqu...@gmail.com wrote: My understanding is that once you get a successful PREPARE that should mean that it's basically impossible for the transaction to fail to commit. If that's not the case, I fail to see how you can get any decent

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sun, Jan 11, 2015 at 10:37 AM, Jim Nasby jim.na...@bluetreble.com wrote: On 1/10/15, 7:11 AM, Michael Paquier wrote: If we had an independent transaction coordinator then I agree with you Kevin. I think Robert is proposing that if we are controlling one of the nodes that's participating as

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sat, Jan 10, 2015 at 9:02 AM, Jim Nasby jim.na...@bluetreble.com wrote: On 1/8/15, 12:00 PM, Kevin Grittner wrote: The key point is that the distributed transaction data must be flagged as needing to commit rather than roll back between the prepare phase and the final commit. If you try to

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On 1/10/15, 7:11 AM, Michael Paquier wrote: If we had an independent transaction coordinator then I agree with you Kevin. I think Robert is proposing that if we are controlling one of the nodes that's participating as well as coordinating the overall transaction that we can take some shortcuts.

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On 1/8/15, 12:00 PM, Kevin Grittner wrote: Robert Haas robertmh...@gmail.com wrote: On Thu, Jan 8, 2015 at 10:19 AM, Kevin Grittner kgri...@ymail.com wrote: Robert Haas robertmh...@gmail.com wrote: Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Thu, Jan 8, 2015 at 8:24 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Jan 7, 2015 at 11:20 AM, Kevin Grittner kgri...@ymail.com wrote: If we are going to include a distributed transaction manager with PostgreSQL, it *must* persist enough information about the transaction ID and

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Thu, Jan 8, 2015 at 7:02 PM, Kevin Grittner kgri...@ymail.com wrote: Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: On Wed, Jan 7, 2015 at 9:50 PM, Kevin Grittner kgri...@ymail.com wrote: Also, as previously mentioned, it must behave in some reasonable way if a database is not

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: On Wed, Jan 7, 2015 at 9:50 PM, Kevin Grittner kgri...@ymail.com wrote: Also, as previously mentioned, it must behave in some reasonable way if a database is not configured to support 2PC, especially since 2PC is off by default in

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Thu, Jan 8, 2015 at 10:19 AM, Kevin Grittner kgri...@ymail.com wrote: Robert Haas robertmh...@gmail.com wrote: Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. If you mean that the primary transaction and all FDWs in the

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Robert Haas robertmh...@gmail.com wrote: On Thu, Jan 8, 2015 at 10:19 AM, Kevin Grittner kgri...@ymail.com wrote: Robert Haas robertmh...@gmail.com wrote: Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. If you mean that the primary

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Wed, Jan 7, 2015 at 11:20 AM, Kevin Grittner kgri...@ymail.com wrote: If we are going to include a distributed transaction manager with PostgreSQL, it *must* persist enough information about the transaction ID and where it is used in a way that will survive a subsequent crash before

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Robert Haas robertmh...@gmail.com wrote: Andres is talking in my other ear suggesting that we ought to reuse the 2PC infrastructure to do all this. If you mean that the primary transaction and all FDWs in the transaction must use 2PC, that is what I was saying, although apparently not clearly

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Sat, Jan 3, 2015 at 2:15 AM, Tom Lane t...@sss.pgh.pa.us wrote: Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes: While looking at the patch for supporting inheritance on foreign tables, I noticed that if a transaction makes changes to more than two foreign servers the current

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Tue, Jan 6, 2015 at 11:55 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Jan 5, 2015 at 3:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: Well, we intentionally didn't couple the FDW stuff closely into transaction commit, because of the thought that the far end would not necessarily

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Ashutosh Bapat ashutosh.ba...@enterprisedb.com wrote: I don't see why would my patch cause inconsistencies. It can cause dangling PREPAREd transactions and I have already acknowledged that fact. Am I missing something? To me that is the big problem. Where I have run into ad hoc distributed

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Mon, Jan 5, 2015 at 11:55 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Jan 2, 2015 at 3:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: In short, you can't force 2PC technology on people who aren't using it already; while for those who are using it already, this isn't nearly good

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Mon, Jan 5, 2015 at 2:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: That's a laudable goal, but I would bet that nothing built on the FDW infrastructure will ever get there. Why? It would be surprising to me if, given that we have gone to some pains to create a system that allows cross-system

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Robert Haas robertmh...@gmail.com writes: On Mon, Jan 5, 2015 at 2:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: That's a laudable goal, but I would bet that nothing built on the FDW infrastructure will ever get there. Why? It would be surprising to me if, given that we have gone to some pains

Re: [HACKERS] Transactions involving multiple postgres foreign servers

On Fri, Jan 2, 2015 at 3:45 PM, Tom Lane t...@sss.pgh.pa.us wrote: In short, you can't force 2PC technology on people who aren't using it already; while for those who are using it already, this isn't nearly good enough as-is. I was involved in some internal discussions related to this patch,

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Robert Haas robertmh...@gmail.com writes: I was involved in some internal discussions related to this patch, so I have some opinions on it. The long-term, high-level goal here is to facilitate sharding. If we've got a bunch of PostgreSQL servers interlinked via postgres_fdw, it should be

Re: [HACKERS] Transactions involving multiple postgres foreign servers

Ashutosh Bapat ashutosh.ba...@enterprisedb.com writes: While looking at the patch for supporting inheritance on foreign tables, I noticed that if a transaction makes changes to more than two foreign servers the current implementation in postgres_fdw doesn't make sure that either all of them

[HACKERS] Transactions involving multiple postgres foreign servers

Hi All, While looking at the patch for supporting inheritance on foreign tables, I noticed that if a transaction makes changes to more than two foreign servers the current implementation in postgres_fdw doesn't make sure that either all of them rollback or all of them commit their changes, IOW

<    1   2