Transacting with foreign servers
Bapat, Ashutosh
Managing transactions involving multiple foreign servers. PostgreSQL has Foreign Data Wrappers and they are writable too! Upcoming features like partitioning, foreign table inheritance and join-push down for foreign tables pave the path for sharding. One missing piece in the puzzle is the distributed transaction manager required to maintain the atomicity and consistency of transactions involving foreign servers. The presentation talks about the current status of such transactions and discusses the path forward towards distributed transaction manager. Support for writable foreign tables was added in PostgreSQL 9.3. As of now, atomicity and consistency is guaranteed, when a transaction makes changes to at most a single foreign server. It fails to do so when changes are made to multiple foreign servers. In order to achieve atomicity and consistency of transactions involving multiple foreign servers, PostgreSQL needs to take up the role of a distributed transaction manager. The talk covers the current status of distributed transactions. It further explores protocol to drive distributed transactions and infrastructure necessary to overcome various hardware, software and network failures during a distributed transaction. It also covers the use cases like data federation and sharding.
 Okay, let's start with the presentation. Welcome you all for discussion about transactions involving foreign servers. Now, as Robert pointed out in yesterday's lightning talk, there are two things when it comes to transaction manager. One is atomic commit and second is atomic visibility. Together they are known as the A4 atomicity in the asset property. So that's really the agenda. So we are going to talk about atomic commits and then I will touch upon atomic visibility. I will go in more detail about atomic commit. So let's see what atomicity is. I mean many of you must be knowing this but let's start with that. So we have some data, two tables. This is an example and this data is subjected to a transaction which basically updates both of these tables. Then if this transaction fails, what atomicity says is we should see the data as if the transaction was not running or it never ran. If the transaction succeeded, we should see all the changes. While the transaction is running, we should not see anything which is going on in the transaction as well as if the transaction completes or if it fails, even then we should not see an intermediate state in which the transaction was. Now these properties are known as all or none property, which means either we see all the changes by a transaction or we see none of the changes. The second property is called indivisibility from where the term atomicity comes. We should not see anything in the intermediate state of the transaction. The way Robert has put it is he has called all or none property as the atomic commit and indivisibility property as atomic visibility. Now we have foreign servers, foreign tables and FDWs. So if we expand this data or extend this data to foreign servers, this is how it will look like. So the coordinator is really the PostgreSQL server. I have named it as coordinator in the two-phase commit context. It's really PostgreSQL server with two tables which really are pointers to two tables on different foreign servers or same foreign servers with different FDWs, same FDW in any kind of combination. And still we want that the same properties that we expected to be obeyed in case of local data to be obeyed in case of this foreign data as well. So again the same thing. So either everything is seen or none of the changes are seen. Any in-between state should not be seen. So when we extend this notion of atomicity to foreign data, that's where I call it as distributed atomicity. There are multiple naming conventions for it. Now some applications may not want atomicity and some applications may want atomicity. There is difference and we'll touch upon this. Now this requirement becomes really important and serious because now we have foreign table inheritance. So we can have one parent table which is sitting in the PostgreSQL coordinator with two or many childs and each child sitting really on the foreign server. A single DML statement without any begin in there can now, so you just say update parent and start updating the parent table, it's going to update the children that it needs to touch whatever are qualified. So there is no explicit transaction but still you want the effects of this single DML to be atomic, either all of the foreign tables should be updated or none of them should be updated and all the atomicity rules. So that's what the notion of atomicity is and when we extend that atomicity to that notion to the foreign servers, that's what we expect it to work like. But as of now, as of 9.5, the thing is that the atomicity is not guaranteed and the transaction management is entirely implemented by each of the foreign data wrappers. So PostgreSQL FDW uses one phase commit which means that when the client says commit, it goes and tries to commit the transactions on foreign servers. If any of them fails, the rest of the foreign servers are requested to abort their transaction or if there is a connectivity failure, those are bought. So we cannot guarantee that when a transaction commits on local server or is supposed to commit, it gets committed on all the foreign servers or aborted on all the foreign servers. So what I'm going to cover is how do we achieve atomicity in PostgreSQL with foreign data wrapper and foreign server. There is a proposal already there in the hackers and there is a patch that I'm working on. So what I'm going to discuss here is some of the parts of that patch as well as the detailed solution. Any questions? I mean, it's fine if you ask questions in between. We have it. Before we enter into the two phase commit and the details of the implementation and solution, if anybody has questions. You talked about failure on one of the foreign servers causing the whole transaction to fail, potentially. The implications of multiple foreign servers, but given the foreign table, it only references one server. So how does the transaction apply to multiple foreign tables? Is this entirely true in comparison? No, it's a case that I'm taking. So if there is only one foreign server and you are saying that there is no local change that has happened, then it works perfectly because either you commit on that server or you abort on that server and all the changes are there on that foreign server. So as long as that foreign server guarantees acid property, atomicity, it's atomic. But when you... You're talking about a transaction on a local server which includes... Foreign server....over here, but I think getting over there. Yeah, so maybe this was not... Yeah, so this transaction is really what is fired on the local server or PostgreSQL and which goes to the foreign server. Yeah? What about the... Yeah, as I explained, there are two things. One is all or none property, atomic commit, and there is atomic visibility. I will touch upon that point at the end of the slides. Yeah, so let's come to the implementation now. Okay, so there is fairly standard solution to achieving atomicity in this case, and which is known as two-phase commit protocol. There are multiple variations of it. There are multiple protocols, but two-phase commit protocol is... It's a fairly accepted standard, so that's what we'll discuss now as a solution in this case. And what I will try to do is try to discuss two-phase commit in the context of PostgreSQL. So the way PostgreSQL transaction management works is it basically goes in multiple phases. At the time of commit, there are important two phases. One is the pre-commit phase and the commit phase. So during pre-commit phase, basically the deferred constraint application, trigger processing, all of that happens. And then the transaction is brought to a state where we can say that now I can commit the transaction, and that's where the commit processing starts. Now where does the two-phase commit fit into? Because two-phase commit, as I said, there are two phases in which it works. Phase one is when we poll all the foreign servers to say, okay, can you commit? Can you commit? Can you commit? Can you commit? And if everybody says yes, then we tell them to commit individually. If anybody says no, then we abort them individually. So the polling is the first phase and the actual commit or abort happens in the second phase, which is popularly known as prepare and commit-prepare or rollback-prepare. So once a foreign server has prepared a transaction and said, yes, I can commit, he cannot go back and say, no, I cannot commit now. Okay, so we get a guarantee that the foreign transaction is now going to commit. So that's what this flow chart is about, detailing these states. So basically we try to poll each foreign server, try to prepare the transaction on each foreign server. If everybody says yes, then we start the commit processing locally and record that this local transaction is going to commit or is committed. And then we move to the phase two where we start telling each foreign servers to commit their individual transactions. If anybody says no, then we abort any foreign transactions which are not prepared, we just abort them. And then we go to the second phase where we abort to prepare transactions on all the foreign servers. Is there a kind of scenario like foreign server time? Yeah, I'll come to that. I'll come to that. Now the problem here is if there is any connectivity failure in phase one, then we go and abort all the transactions. If there is any connectivity failure in phase two, we know that that foreign server has told us that I have prepared and I am ready to commit. So we have a guarantee in our hand that that guy is going to pay us. Okay, so we use that guarantee further. So that's where the atomic commit comes from. Once we get this guarantee from each of the servers, we can tell them now this is the guarantee that I am going to use. So if there is any failure in phase two, any connectivity failure, server crash, local server crash, anything, then we have to remember that this guy had given me the guarantee. This guy had prepared the transaction. Now whenever the connectivity is established, the failure is overcome, we tell that guy to commit the transaction or abort the transaction, which yes, it ensures atomic commit, but then it opens up two problems. One is as atomicity guarantees on a single server that all the changes will be visible at once. Once you say this transaction committed, all the changes that that transaction did become visible at one single point of time. It is not guaranteed here because we go tell each of the foreign servers that commit, commit, commit, commit, and this delay is going to, if anybody goes in between and tries to see that what's on that foreign server, he is going to get some data which was as if it was not committed on the transaction. And any network failure or crash is going to keep this prepared transaction dangling or unresolved on those foreign servers, which means we have to try hard to get them resolved, otherwise they have blocked the resources. Whatever that transaction has done, any logs that have been taken, any data has been modified, it's going to be there till we actually commit the transaction and then that foreign server is capable of releasing those resources. So a lot of code that has, a lot of effort, a lot of code that has been written is to take care of this particular case where we try to resolve unresolved foreign transactions and that's what I will start explaining next. Does that answer your question? Any question about two-phase commit? Because the one part I didn't really mention is what is some of the work that the local database is connecting into the transaction and where its commit fits into this. No, please repeat the question. Well, you're using Postgres as a transaction manager and some of the people that are accessed by the distributed transaction have the database management commands that you talked about to a lot of parts of the transaction. Yeah. Okay, so, well, okay. I think I'm going to discuss that, but so local server is asymmetric here in the sense that it knows what it's doing. So that commit or abort of the local changes are available locally which can be seen. So, well, if at the end of the presentation you still have this question, we'll discuss it further. Okay, so now what we are going to discuss is how do we make sure that we resolve this unresolved transaction if they happen. And so this block that you are saying, prepare transaction on foreign server, I'm going to explain what exactly is done in that block. So obviously we have to remember whatever we have done in phase one, we have to remember where all we have to, where all we have prepared the transactions, what was the information related to that, so that we can deal with them in phase two. So the first block is really there for everything. I mean, even if there is an unresolved transaction, we don't want to tackle them. The first block is really important to remember what we have done in phase one, where all we have prepared the transactions. The second, so in order to resolve unresolved prepared transaction, we have to take into consideration that the local server itself can crash or maybe run out of the in memory, run out of the memory available and so on. So we have to persist whatever information we had remembered in phase one to the disk, so that when if the server crashes or needs to be restarted between phase one and phase two or during phase two, it has that information. And it is in the implementation that I have, it's stored in under the data directory and PGFDW exact directory. The structure of this directory is very similar to PG2 phase directory, where for every connection, every foreign connection that is open and where the transaction is prepared, we store information in one file. Just like PG2 phase stores information about one prepared transaction in one file. But that's not enough. So what if the server crashes and there is a standby that is going to take over? So in order, we have to replicate whatever information we have saved to the standby so that when the standby takes over and fails over, it can take the responsibility of resolving the unresolved transaction. So we have to, now the only way to get this information to the standby is through logs and so we log this information in the, log this information in the wall and that also helps us in crash recovery. So we actually don't have to, you know, f-sync the information every time we try, write to the disk. Now once we have taken enough measures to remember the transaction, remember the foreign transaction, we can go and actually prepare the transaction on the foreign server. If we reverse this order, if we prepare the transaction and crash, then we have, we have not, I mean, once we come back again, we do not have information of where we have prepared the transaction. So even before we prepare, we have to remember this bit of information. So that was the first part about remembering the prepared transaction information. The second part is how do we resolve, it's the, what steps to take when we resolve the foreign transaction, which means we commit or abort foreign transaction, foreign prepared transaction. So it's now the reverse way we have to go. So we commit or abort the foreign transaction on the foreign server, we tell it to commit prepared or rollback prepared, whatever syntax it is. If it's successful, we first log the removal of foreign transaction information and then we actually remove it from our persistent disk because we do not require it anymore. In case of, in case we are unsuccessful, we have to try again in the same loop as long as we succeed and we'll see how I have implemented that as well. Now, before that, let's talk about what should, what needs to be remembered. So the first thing is the transaction ID, the local transaction ID, which will tell us what we should do with this prepared transaction, whether we should go and abort it or whether we should go and rollback it. So the local transaction, once it has prepared everything, the local transaction is committed, it records that commit and then that record can be used later to decide what to be done with the foreign prepared transaction when we come to resolve it. Then this database ID, foreign server and user tells us where we have prepared the transaction and the last bit is about foreign transaction ID, which really identifies that foreign transaction, which is essentially GID on PostgreSQL and every RDBMS has its own way of identifying prepared transaction, which needs to be taken care of, taken care by the FDW. Kevin, does that answer your question and you still have the question? No, we've talked prior to this and I know the answer, I just think it's sort of missing from the presentation, which is that the managing database can be part of this distributed transaction and it doesn't need to be used to base commit, but it needs to be simple to commit between the prepared phase of all the other data. Just a fine point, I thought you needed to do anything. Okay, so this flow chart in phase one actually happens, is executed by the back end, which is performing the local transaction. So if that's... Okay, any questions till now? Because I'm slightly going to digress here next and talk about something else and come back to the point of two phase commit. In the sense for the transaction, because here last slide where I committed on the foreign server, what if the master were to go down, say it hasn't logged, but it's actually the transaction, and if you try with the master boot? In this thing? The transaction was successful, yes, and then the master explodes. Okay. But it still takes it as the finished transaction. Yeah, yeah, yeah. So when the master reboots, it will find that there is an entry in here, which says that I have prepared the transaction on foreign server. Now, it retries and... Yeah, it should come... I mean, the foreign server should come back and say, no, I do not see this transaction anymore in my repository or database. There is a requirement to which I will come when we discuss it further. The master would roll back the change on its side then, or if it would just say, oh, no, no. I mean, it can't do that. Now, it has said commit. No. So it basically boils down to how do we generate these transaction IDs, since that's what we provide during prepare, and I will talk about that in a minute. Well, I think that's the same case here as well. So if we log, we persist and crash or log and crash, but we do not execute this tape, it's the other way around. We never prepared that transaction, but we have in our repository a record saying, okay, there is a transaction prepared. Yeah. Okay, there was... Well, obviously not all foreign data wrappers have support for two-phase commit. We cannot support two-phase commit protocol, and what we should do with them. I mean, well, the straight and unfortunate answer is probably we cannot provide the atomicity for the transactions which involve those kinds of foreign servers. But still, what I'm showing here is the blue flow chart shows what PostgreSQL-DW does today, and the orange flow chart shows maybe what we can do. So what it does today is during pre-commit processing, it commits the transactions on all the foreign servers. It tries to commit. If anybody says, anybody gives an error, throws error, then it aborts all the remaining transactions. Now, depending upon what is the order of foreign servers in which... In order in which foreign servers are pulled, you can get different results. Some of the foreign servers will get a commit, some of them will get a bot, and it's really kind... It seems undeterministic, but... It really depends upon in which order those transactions were started to begin with. So what we might want to do is something better is we record the local commit and then go on to commit the transactions. So if we cannot abort the local transaction, we abort all the foreign transactions, of course. But if we can commit the local transaction, we try to commit the transactions on foreign servers. If anybody fails to commit, we leave that guy aside, but try to commit on all the rest of the foreign servers rather than going this approach where we just abort... abort all the other foreign servers once one has failed. That, I think, gives a bit of deterministic behavior here, but that's something that can be discussed or debated about. Okay. Well, that was little digression. Now, we get back to some more details about the implementation of what the patch does. It introduces two FDW hooks that need to be implemented by the FDW. One is the getPrepairId, which should give the prepareTransactionId for a given foreign server and user pair. Now, this we do for each... I mean, this is the responsibility of FDW because each foreign server and FDW might have different rules for identifiers, what is an acceptable identifier, what is not length, and many things. So that is delegated to the FDW through the hook. And this is what... The output of this is what is persisted and wall logged, as I have said. Now, this function is responsible to make sure that it gives a unique transaction ID which does not conflict with any future transactions or even the previous transactions. Because if it starts giving transaction IDs which are conflicting, then what you have said will come into picture that we committed a transaction ID, we committed a prepared transaction with a given transaction ID, and the local server crashed. So it came back, it had an entry with that foreign transaction ID. So it tries to again go and resolve that. And if somebody in between has created a transaction with the same ID again, we actually are going to resolve a transaction which we have not prepared. So this is an important criteria for giving back the transaction IDs, and the prepared transaction ID is that it needs to be unique, or fairly unique. And the second hook is... So that was the first phase hook. Now the second phase hook is which would actually commit, roll back, or prepare the transactions, which basically... Yeah, so, well, yeah, prepare is part of the first phase which should be taken care by this hook. So it is responsible for preparing transactions with the ID that was given before, and or commit or commit, roll back the prepared transaction. So it basically ends the transaction completely. There are certain books that the patch introduces. One is Atomic Foreign Transaction, which is a Boolean book, which tells you whether you want to use two-phase commit or not, whether you want atomicity for the transactions or not. But when it is on, and you say, I want atomic foreign transactions, it needs that all the participating servers support two-PC. Otherwise, it will throw... I mean, the transaction cannot guarantee atomicity. Well, I have implemented it in such a way, and it was probably easier also that it can be set anytime during the transaction. The value at the time of commit is what matters, so that if a transaction is going to conditionally touch multiple foreign servers, it can choose, depending upon which foreign server it chooses, whether it wants the transaction to be atomic or not, even within the transaction. And the second is, since we are using shared memory to store the information, the next group tells how many maximum number of transactions that can be prepared on foreign servers at a given time. So it limits the shared memory that we can use. I mean, in similar way as the two-phase implementation that we have. Okay, so how is foreign transaction resolved, where implemented? So the back-end which is doing the transaction, it takes the responsibility of executing the phase two, if the foreign servers are available even after phase one, but it tries only once. If it fails, then that information is available to any back-end in the shared memory or on the disk if the server crashes. What I have provided in the patch is a built-in function, which when executed, tries to resolve all the foreign transactions that are unresolved in a given database, because it needs to access the database for foreign server and user ID. Now, since we said that we need to try hard to resolve it repeated number of times, I have implemented a contrib module which, when loaded, runs a background worker process which fires these resolvers, I mean, which basically fires this built-in function in different databases. Okay, before we go to atomic visibility, any questions, any suggestions, comments? Any foreign server which supports 2PC? Yeah, so as long as it can prepare the transaction when the corresponding FDW says prepare, and as long as it can commit or roll back that prepared transaction when FDW says so, it's fine. The FDW implemented the appropriate type of database to connect to the FDW, so that it will authorize the FDW. Yeah, that's why those two hooks, I mean, if you know, if you have seen the FDW, it works with hooks. So there are hooks for multiple functionality. So this introduces two new hooks for getting that done. What happens if you use 2PACE commit for the host transaction? That's one. It's not required. It's not required. As far as I have implemented it, that's not required. And that puts an extra, see, the prepared transactions, as I've said, they take shared memory out of the server, resources, and everything. So if you prepare the transaction locally, that you are unnecessarily wasting resources, we already go through that phases. We have this pre-commit phase, and then we have commit phase during the transaction commit. This pre-commit phase actually is equivalent to the prepare. But you might have 2PACE commit you apply an application. Yeah. Yeah, in which case we have to, we have to prepare the corresponding transaction and remember it. But only when the client has told us to prepare, not when there is direct commit from client. I think one of the things that was a crux for me when I really grasped it is that the point of no return is when the local transaction is absolutely committed. Because before that point, if the server crashes, you have to roll everybody back. After that point, you have to roll everybody back. That is the point of no return. And that transaction can't be the transaction controller for itself. So what you're talking about will only work if you have some other transaction manager managing the transaction, managing this kind of energy-serviced transaction. That seems to me like it would be a pretty unusual situation. It wouldn't have worked any too much, but it could happen again. Yeah. It would work as long as you had something as an outer transaction manager that made sure that the one that was managing what you were talking about was either a manager or a manager. Yeah. But I mean it's a whole extra layer of complexity. Yeah. I mean it's a... You can do it on purpose, but we know what trigger might run a foreign data wrapper. Like an application could be accessed in Qt databases, and they don't know that foreign data wrappers are being used. So that's where it would happen. No, but application, if it's doing something with the foreign tables, it should know that... What does the application might not know? It might be done through triggers. It might be writing a log entry, like a Luna. You know what's going to happen. But if they were... Yeah, I mean that's something that needs to be... I mean when we design something with foreign servers, I think that's something that needs to be taken care of. I mean if there is a valid case, yeah, I will consider that. I don't think it adds anything to what you have to do with what you're working on. It would just be an unusual work time. We're all still working on it. I think the application of the expected database to handle the local foreign server dot... The application doesn't care really. Yeah, I think that's what really... The database is doing something. Yeah. If I understand. Yeah, so for... I think application, once it has said that this is a foreign server and this is foreign data, it has already delegated all that processing to the postgres equals. So, I mean it's the postgres equals responsibility to take care of that, however it does. I think the exact case... You understand my case, but I think you might not. Yeah. I'm talking... I'm an application, I'm talking to Oracle plus Postgres. And I just do something, something simple. Underneath the seams, the Postgres happens to use 4D data wrappers. And does it replace the men? But I'm doing it to replace the men on top. Yeah, in which case the local transaction will be prepared, which will prepare the foreign transactions and it will wait for the application to tell me when to commit. And once that happens, that's what he said. The commit is no point of written, right? So, I mean it all hinges on the local commit. Whenever it comes, it will go and commit the preparation on sections. Okay, let's talk about atomic visibility. And this is atomic visibility. Jokes apart. So, I have some rough ideas about what atomic visibility should be. It's a harder problem to solve. But let's say it might be possible to solve that problem in certain cases. Now, where is the visibility broken? I have given one case where it takes to commit foreign prepared transaction. You go one by one and there is a delay in between. So, somebody comes in, you see an in-between state, which is actually the foreign transaction change. But the other possible problem that can happen is MVCC allows older versions to be read. So, those foreign servers which are using MVCC, if there is a prepared transaction, they will allow the older version of that data to be read. Whereas, while the prepared transaction is not committed or resolved, it is yet to be committed. So, basically what will happen is you commit on one foreign server. Before you go and commit on the other foreign server, somebody can come in, again access these two foreign servers. It will see committed data on foreign server one, but not on foreign server two. Foreign server two, it will still see the old data. And this can happen even when there is, I mean, the data is being accessed through the same master, through the same backend, sorry, through a different backend. So, the possible solution here is to wait for that prepared transaction to commit. And the way we wait is use stronger locking. So, we always do foreshare or serializable reads, which means that the transaction is, the data which has been modified is, which is in prepared state holds the lock and anybody who tries to read the data is made to wait. But that's going to affect performance because even if the transaction, even if there is no reader for that state, we are going to hold the locks, which I think is a performance impact. The second possibility is if it's all going through the same PostgreSQL, same master server, then it basically, so if we couldn't prepare the, resolve the transaction because the connectivity was lost or we couldn't access that transaction. And if somebody comes to access the same data, probably if we are able to reach the foreign server, then we should first commit the transaction that was prepared and then allow or then start querying for the other transactions. Now, the question is in concurrent case where there are multiple backends trying to access the same foreign data, how do we detect this situation and apply the solution? So I'm still working on it, thinking about it. So that's something that can be discussed. We have to solve that issue or not stand by. If you've got a connection to a primary and you're using how to stand by, you can see a transaction committed to everyone, not the other, and this is a much harder case. So I'm obviously, solvable except for certain special features. Yeah, that's, yeah, yeah. So, well, that's what, so basically, we basically now the current status of this patch, it has already gone through some discussions. And I'm really thankful to Kevin, Robert, Tom, who had given excellent suggestions on that thread or in our personal private email chains. So I submitted one patch in February, which had a lot of to-dos to just elaborate the idea that I had. And there is a patch that I have, which takes care of many to-dos and I am supposed to submit in the commit phase. Which addresses atomic commit? It does not address atomic visibility, but I think that's one step forward than what we have now. So once we get to that, then we'll start thinking about the atomic visibility. Yeah, that's it. If we have any questions. I want to get back to my two-phase commit. Okay. So here's the scenario. Top-level application does the prepare across two services, say, postgres with 20 data wrappers in Oracle. At that time, there's been no prepare propagated down to the 40 data wrappers, correct? So you don't actually have a guarantee that the 40 data wrappers will be able to commit. At the time that you prepare the postgres controlling transaction, it will prepare the other. Willem, you're going to say that? Will do we have a... The local database proceeds to prepare it as a post-stand. I don't know why it's not going to work. I didn't see that on the postgres. Well, the thing is, as it's pretty complicated, if I start putting all the integrities, I will get the whole hall sleeping. Anyway, so the thing is, so there is prepare that has client said prepare, which actually does... Prepare one, two, three, whatever it is. It's not going to get back and say I have prepared unless it gets a guarantee from each of these. And then when you commit, do the same. So if it comes, then you commit the prepared transactions and only then come back. I have a slide for next time. There's a small line in the master ascending. This is pushed down to all four. No, then in that case, I should just show my patch here, which is like thousand lines of code. And we should discuss the patch. I'm happy about it. I don't know if it's a thousand lines of code on that. Yeah, yeah. Any other questions? What kind of a latency is it? Are you seeing when you have the day three servers and the two days of the meeting? I'd say things are tangled. What kind of latency is it?
 Okay, let's start with the presentation.  Welcome you all for discussion about transactions involving foreign servers.  Now, as Robert pointed out in yesterday's lightning talk,  there are two things when it comes to transaction manager.  One is atomic commit and second is atomic visibility.  Together they are known as the A4 atomicity in the asset property.  So that's really the agenda.  So we are going to talk about atomic commits and then I will touch upon atomic visibility.  I will go in more detail about atomic commit.  So let's see what atomicity is.  I mean many of you must be knowing this but let's start with that.  So we have some data, two tables.  This is an example and this data is subjected to a transaction  which basically updates both of these tables.  Then if this transaction fails, what atomicity says is we should see the data  as if the transaction was not running or it never ran.  If the transaction succeeded, we should see all the changes.  While the transaction is running, we should not see anything which is going on in the transaction  as well as if the transaction completes or if it fails,  even then we should not see an intermediate state in which the transaction was.  Now these properties are known as all or none property,  which means either we see all the changes by a transaction or we see none of the changes.  The second property is called indivisibility from where the term atomicity comes.  We should not see anything in the intermediate state of the transaction.  The way Robert has put it is he has called all or none property as the atomic commit  and indivisibility property as atomic visibility.  Now we have foreign servers, foreign tables and FDWs.  So if we expand this data or extend this data to foreign servers,  this is how it will look like.  So the coordinator is really the PostgreSQL server.  I have named it as coordinator in the two-phase commit context.  It's really PostgreSQL server with two tables which really are pointers to two tables  on different foreign servers or same foreign servers with different FDWs,  same FDW in any kind of combination.  And still we want that the same properties that we expected to be obeyed in case of local data  to be obeyed in case of this foreign data as well.  So again the same thing.  So either everything is seen or none of the changes are seen.  Any in-between state should not be seen.  So when we extend this notion of atomicity to foreign data,  that's where I call it as distributed atomicity.  There are multiple naming conventions for it.  Now some applications may not want atomicity and some applications may want atomicity.  There is difference and we'll touch upon this.  Now this requirement becomes really important and serious because now we have foreign table inheritance.  So we can have one parent table which is sitting in the PostgreSQL coordinator  with two or many childs and each child sitting really on the foreign server.  A single DML statement without any begin in there can now,  so you just say update parent and start updating the parent table,  it's going to update the children that it needs to touch whatever are qualified.  So there is no explicit transaction but still you want the effects of this single DML to be atomic,  either all of the foreign tables should be updated or none of them should be updated and all the atomicity rules.  So that's what the notion of atomicity is and when we extend that atomicity to that notion to the foreign servers,  that's what we expect it to work like.  But as of now, as of 9.5, the thing is that the atomicity is not guaranteed  and the transaction management is entirely implemented by each of the foreign data wrappers.  So PostgreSQL FDW uses one phase commit which means that when the client says commit,  it goes and tries to commit the transactions on foreign servers.  If any of them fails, the rest of the foreign servers are requested to abort their transaction  or if there is a connectivity failure, those are bought.  So we cannot guarantee that when a transaction commits on local server or is supposed to commit,  it gets committed on all the foreign servers or aborted on all the foreign servers.  So what I'm going to cover is how do we achieve atomicity in PostgreSQL with foreign data wrapper and foreign server.  There is a proposal already there in the hackers and there is a patch that I'm working on.  So what I'm going to discuss here is some of the parts of that patch as well as the detailed solution.  Any questions? I mean, it's fine if you ask questions in between.  We have it. Before we enter into the two phase commit and the details of the implementation and solution,  if anybody has questions.  You talked about failure on one of the foreign servers causing the whole transaction to fail, potentially.  The implications of multiple foreign servers, but given the foreign table, it only references one server.  So how does the transaction apply to multiple foreign tables?  Is this entirely true in comparison?  No, it's a case that I'm taking.  So if there is only one foreign server and you are saying that there is no local change that has happened,  then it works perfectly because either you commit on that server or you abort on that server and all the changes are there on that foreign server.  So as long as that foreign server guarantees acid property, atomicity, it's atomic.  But when you...  You're talking about a transaction on a local server which includes...  Foreign server. ...over here, but I think getting over there.  Yeah, so maybe this was not...  Yeah, so this transaction is really what is fired on the local server or PostgreSQL and which goes to the foreign server.  Yeah?  What about the...  Yeah, as I explained, there are two things.  One is all or none property, atomic commit, and there is atomic visibility.  I will touch upon that point at the end of the slides.  Yeah, so let's come to the implementation now.  Okay, so there is fairly standard solution to achieving atomicity in this case,  and which is known as two-phase commit protocol.  There are multiple variations of it.  There are multiple protocols, but two-phase commit protocol is...  It's a fairly accepted standard, so that's what we'll discuss now as a solution in this case.  And what I will try to do is try to discuss two-phase commit in the context of PostgreSQL.  So the way PostgreSQL transaction management works is it basically goes in multiple phases.  At the time of commit, there are important two phases.  One is the pre-commit phase and the commit phase.  So during pre-commit phase, basically the deferred constraint application, trigger processing, all of that happens.  And then the transaction is brought to a state where we can say that now I can commit the transaction,  and that's where the commit processing starts.  Now where does the two-phase commit fit into?  Because two-phase commit, as I said, there are two phases in which it works.  Phase one is when we poll all the foreign servers to say,  okay, can you commit? Can you commit? Can you commit? Can you commit?  And if everybody says yes, then we tell them to commit individually.  If anybody says no, then we abort them individually.  So the polling is the first phase and the actual commit or abort happens in the second phase,  which is popularly known as prepare and commit-prepare or rollback-prepare.  So once a foreign server has prepared a transaction and said, yes, I can commit,  he cannot go back and say, no, I cannot commit now.  Okay, so we get a guarantee that the foreign transaction is now going to commit.  So that's what this flow chart is about, detailing these states.  So basically we try to poll each foreign server,  try to prepare the transaction on each foreign server.  If everybody says yes, then we start the commit processing locally  and record that this local transaction is going to commit or is committed.  And then we move to the phase two where we start telling each foreign servers  to commit their individual transactions.  If anybody says no, then we abort any foreign transactions which are not prepared,  we just abort them.  And then we go to the second phase where we abort to prepare transactions on all the foreign servers.  Is there a kind of scenario like foreign server time?  Yeah, I'll come to that.  I'll come to that.  Now the problem here is if there is any connectivity failure in phase one,  then we go and abort all the transactions.  If there is any connectivity failure in phase two,  we know that that foreign server has told us that I have prepared and I am ready to commit.  So we have a guarantee in our hand that that guy is going to pay us.  Okay, so we use that guarantee further.  So that's where the atomic commit comes from.  Once we get this guarantee from each of the servers,  we can tell them now this is the guarantee that I am going to use.  So if there is any failure in phase two, any connectivity failure,  server crash, local server crash, anything,  then we have to remember that this guy had given me the guarantee.  This guy had prepared the transaction.  Now whenever the connectivity is established, the failure is overcome,  we tell that guy to commit the transaction or abort the transaction,  which yes, it ensures atomic commit,  but then it opens up two problems.  One is as atomicity guarantees on a single server that all the changes will be visible at once.  Once you say this transaction committed,  all the changes that that transaction did become visible at one single point of time.  It is not guaranteed here because we go tell each of the foreign servers  that commit, commit, commit, commit, and this delay is going to,  if anybody goes in between and tries to see that what's on that foreign server,  he is going to get some data which was as if it was not committed on the transaction.  And any network failure or crash is going to keep this prepared transaction dangling  or unresolved on those foreign servers,  which means we have to try hard to get them resolved,  otherwise they have blocked the resources.  Whatever that transaction has done, any logs that have been taken,  any data has been modified, it's going to be there till we actually commit the transaction  and then that foreign server is capable of releasing those resources.  So a lot of code that has, a lot of effort,  a lot of code that has been written is to take care of this particular case  where we try to resolve unresolved foreign transactions  and that's what I will start explaining next.  Does that answer your question?  Any question about two-phase commit?  Because the one part I didn't really mention is what is some of the work  that the local database is connecting into the transaction and where its commit fits into this.  No, please repeat the question.  Well, you're using Postgres as a transaction manager  and some of the people that are accessed by the distributed transaction  have the database management commands that you talked about to a lot of parts of the transaction.  Yeah.  Okay, so, well, okay.  I think I'm going to discuss that, but so local server is asymmetric here  in the sense that it knows what it's doing.  So that commit or abort of the local changes are available locally which can be seen.  So, well, if at the end of the presentation you still have this question,  we'll discuss it further.  Okay, so now what we are going to discuss is how do we make sure that we resolve  this unresolved transaction if they happen.  And so this block that you are saying, prepare transaction on foreign server,  I'm going to explain what exactly is done in that block.  So obviously we have to remember whatever we have done in phase one,  we have to remember where all we have to, where all we have prepared the transactions,  what was the information related to that, so that we can deal with them in phase two.  So the first block is really there for everything.  I mean, even if there is an unresolved transaction, we don't want to tackle them.  The first block is really important to remember what we have done in phase one,  where all we have prepared the transactions.  The second, so in order to resolve unresolved prepared transaction,  we have to take into consideration that the local server itself can crash  or maybe run out of the in memory, run out of the memory available and so on.  So we have to persist whatever information we had remembered in phase one to the disk,  so that when if the server crashes or needs to be restarted between phase one and phase two  or during phase two, it has that information.  And it is in the implementation that I have, it's stored in under the data directory  and PGFDW exact directory.  The structure of this directory is very similar to PG2 phase directory,  where for every connection, every foreign connection that is open  and where the transaction is prepared, we store information in one file.  Just like PG2 phase stores information about one prepared transaction in one file.  But that's not enough.  So what if the server crashes and there is a standby that is going to take over?  So in order, we have to replicate whatever information we have saved to the standby  so that when the standby takes over and fails over,  it can take the responsibility of resolving the unresolved transaction.  So we have to, now the only way to get this information to the standby is through logs  and so we log this information in the, log this information in the wall  and that also helps us in crash recovery.  So we actually don't have to, you know, f-sync the information every time we try,  write to the disk.  Now once we have taken enough measures to remember the transaction,  remember the foreign transaction, we can go and actually prepare the transaction  on the foreign server.  If we reverse this order, if we prepare the transaction and crash,  then we have, we have not, I mean, once we come back again,  we do not have information of where we have prepared the transaction.  So even before we prepare, we have to remember this bit of information.  So that was the first part about remembering the prepared transaction information.  The second part is how do we resolve, it's the, what steps to take when we resolve  the foreign transaction, which means we commit or abort foreign transaction,  foreign prepared transaction.  So it's now the reverse way we have to go.  So we commit or abort the foreign transaction on the foreign server,  we tell it to commit prepared or rollback prepared, whatever syntax it is.  If it's successful, we first log the removal of foreign transaction information  and then we actually remove it from our persistent disk  because we do not require it anymore.  In case of, in case we are unsuccessful, we have to try again in the same loop  as long as we succeed and we'll see how I have implemented that as well.  Now, before that, let's talk about what should, what needs to be remembered.  So the first thing is the transaction ID, the local transaction ID,  which will tell us what we should do with this prepared transaction,  whether we should go and abort it or whether we should go and rollback it.  So the local transaction, once it has prepared everything,  the local transaction is committed, it records that commit  and then that record can be used later to decide what to be done  with the foreign prepared transaction when we come to resolve it.  Then this database ID, foreign server and user tells us where we have  prepared the transaction and the last bit is about foreign transaction ID,  which really identifies that foreign transaction,  which is essentially GID on PostgreSQL and every RDBMS has its own way  of identifying prepared transaction, which needs to be taken care of,  taken care by the FDW. Kevin, does that answer your question  and you still have the question?  No, we've talked prior to this and I know the answer,  I just think it's sort of missing from the presentation,  which is that the managing database can be part of this distributed transaction  and it doesn't need to be used to base commit,  but it needs to be simple to commit between the prepared phase of all the other data.  Just a fine point, I thought you needed to do anything.  Okay, so this flow chart in phase one actually happens,  is executed by the back end, which is performing the local transaction.  So if that's...  Okay, any questions till now?  Because I'm slightly going to digress here next  and talk about something else and come back to the point of two phase commit.  In the sense for the transaction,  because here last slide where I committed on the foreign server,  what if the master were to go down, say it hasn't logged,  but it's actually the transaction,  and if you try with the master boot?  In this thing?  The transaction was successful, yes, and then the master explodes.  Okay.  But it still takes it as the finished transaction.  Yeah, yeah, yeah.  So when the master reboots, it will find that there is an entry in here,  which says that I have prepared the transaction on foreign server.  Now, it retries and...  Yeah, it should come...  I mean, the foreign server should come back and say,  no, I do not see this transaction anymore in my repository or database.  There is a requirement to which I will come when we discuss it further.  The master would roll back the change on its side then,  or if it would just say, oh, no, no.  I mean, it can't do that.  Now, it has said commit.  No.  So it basically boils down to how do we generate these transaction IDs,  since that's what we provide during prepare,  and I will talk about that in a minute.  Well, I think that's the same case here as well.  So if we log, we persist and crash or log and crash,  but we do not execute this tape, it's the other way around.  We never prepared that transaction,  but we have in our repository a record saying,  okay, there is a transaction prepared.  Yeah.  Okay, there was...  Well, obviously not all foreign data wrappers have support for two-phase commit.  We cannot support two-phase commit protocol,  and what we should do with them.  I mean, well, the straight and unfortunate answer is probably we cannot provide  the atomicity for the transactions which involve those kinds of foreign servers.  But still, what I'm showing here is the blue flow chart shows  what PostgreSQL-DW does today,  and the orange flow chart shows maybe what we can do.  So what it does today is during pre-commit processing,  it commits the transactions on all the foreign servers.  It tries to commit.  If anybody says, anybody gives an error, throws error,  then it aborts all the remaining transactions.  Now, depending upon what is the order of foreign servers in which...  In order in which foreign servers are pulled, you can get different results.  Some of the foreign servers will get a commit,  some of them will get a bot,  and it's really kind...  It seems undeterministic, but...  It really depends upon in which order those transactions were started to begin with.  So what we might want to do is something better is we record the local commit  and then go on to commit the transactions.  So if we cannot abort the local transaction,  we abort all the foreign transactions, of course.  But if we can commit the local transaction,  we try to commit the transactions on foreign servers.  If anybody fails to commit, we leave that guy aside,  but try to commit on all the rest of the foreign servers  rather than going this approach where we just abort...  abort all the other foreign servers once one has failed.  That, I think, gives a bit of deterministic behavior here,  but that's something that can be discussed or debated about.  Okay. Well, that was little digression.  Now, we get back to some more details about the implementation of what the patch does.  It introduces two FDW hooks that need to be implemented by the FDW.  One is the getPrepairId, which should give the prepareTransactionId  for a given foreign server and user pair.  Now, this we do for each...  I mean, this is the responsibility of FDW because each foreign server  and FDW might have different rules for identifiers,  what is an acceptable identifier, what is not length, and many things.  So that is delegated to the FDW through the hook.  And this is what...  The output of this is what is persisted and wall logged, as I have said.  Now, this function is responsible to make sure that it gives a unique transaction ID  which does not conflict with any future transactions  or even the previous transactions.  Because if it starts giving transaction IDs which are conflicting,  then what you have said will come into picture that we committed a transaction ID,  we committed a prepared transaction with a given transaction ID,  and the local server crashed.  So it came back, it had an entry with that foreign transaction ID.  So it tries to again go and resolve that.  And if somebody in between has created a transaction with the same ID again,  we actually are going to resolve a transaction which we have not prepared.  So this is an important criteria for giving back the transaction IDs,  and the prepared transaction ID is that it needs to be unique, or fairly unique.  And the second hook is...  So that was the first phase hook.  Now the second phase hook is which would actually commit, roll back,  or prepare the transactions, which basically...  Yeah, so, well, yeah, prepare is part of the first phase which should be taken care by this hook.  So it is responsible for preparing transactions with the ID that was given before,  and or commit or commit, roll back the prepared transaction.  So it basically ends the transaction completely.  There are certain books that the patch introduces.  One is Atomic Foreign Transaction, which is a Boolean book,  which tells you whether you want to use two-phase commit or not,  whether you want atomicity for the transactions or not.  But when it is on, and you say, I want atomic foreign transactions,  it needs that all the participating servers support two-PC.  Otherwise, it will throw...  I mean, the transaction cannot guarantee atomicity.  Well, I have implemented it in such a way,  and it was probably easier also that it can be set anytime during the transaction.  The value at the time of commit is what matters,  so that if a transaction is going to conditionally touch multiple foreign servers,  it can choose, depending upon which foreign server it chooses,  whether it wants the transaction to be atomic or not, even within the transaction.  And the second is, since we are using shared memory to store the information,  the next group tells how many maximum number of transactions  that can be prepared on foreign servers at a given time.  So it limits the shared memory that we can use.  I mean, in similar way as the two-phase implementation that we have.  Okay, so how is foreign transaction resolved, where implemented?  So the back-end which is doing the transaction,  it takes the responsibility of executing the phase two,  if the foreign servers are available even after phase one,  but it tries only once.  If it fails, then that information is available to any back-end in the shared memory  or on the disk if the server crashes.  What I have provided in the patch is a built-in function,  which when executed, tries to resolve all the foreign transactions  that are unresolved in a given database,  because it needs to access the database for foreign server and user ID.  Now, since we said that we need to try hard to resolve it repeated number of times,  I have implemented a contrib module which, when loaded,  runs a background worker process which fires these resolvers,  I mean, which basically fires this built-in function in different databases.  Okay, before we go to atomic visibility, any questions, any suggestions, comments?  Any foreign server which supports 2PC?  Yeah, so as long as it can prepare the transaction when the corresponding FDW says prepare,  and as long as it can commit or roll back that prepared transaction when FDW says so, it's fine.  The FDW implemented the appropriate type of database to connect to the FDW,  so that it will authorize the FDW.  Yeah, that's why those two hooks, I mean, if you know,  if you have seen the FDW, it works with hooks.  So there are hooks for multiple functionality.  So this introduces two new hooks for getting that done.  What happens if you use 2PACE commit for the host transaction?  That's one.  It's not required.  It's not required.  As far as I have implemented it, that's not required.  And that puts an extra, see, the prepared transactions, as I've said,  they take shared memory out of the server, resources, and everything.  So if you prepare the transaction locally, that you are unnecessarily wasting resources,  we already go through that phases.  We have this pre-commit phase, and then we have commit phase during the transaction commit.  This pre-commit phase actually is equivalent to the prepare.  But you might have 2PACE commit you apply an application.  Yeah.  Yeah, in which case we have to,  we have to prepare the corresponding transaction and remember it.  But only when the client has told us to prepare,  not when there is direct commit from client.  I think one of the things that was a crux for me when I really grasped it is that  the point of no return is when the local transaction is absolutely committed.  Because before that point, if the server crashes, you have to roll everybody back.  After that point, you have to roll everybody back.  That is the point of no return.  And that transaction can't be the transaction controller for itself.  So what you're talking about will only work if you have some other transaction manager  managing the transaction, managing this kind of energy-serviced transaction.  That seems to me like it would be a pretty unusual situation.  It wouldn't have worked any too much, but it could happen again.  Yeah.  It would work as long as you had something as an outer transaction manager  that made sure that the one that was managing what you were talking about  was either a manager or a manager.  Yeah.  But I mean it's a whole extra layer of complexity.  Yeah.  I mean it's a...  You can do it on purpose, but we know what trigger might run a foreign data wrapper.  Like an application could be accessed in Qt databases,  and they don't know that foreign data wrappers are being used.  So that's where it would happen.  No, but application, if it's doing something with the foreign tables,  it should know that...  What does the application might not know?  It might be done through triggers.  It might be writing a log entry, like a Luna.  You know what's going to happen.  But if they were...  Yeah, I mean that's something that needs to be...  I mean when we design something with foreign servers,  I think that's something that needs to be taken care of.  I mean if there is a valid case, yeah, I will consider that.  I don't think it adds anything to what you have to do with what you're working on.  It would just be an unusual work time.  We're all still working on it.  I think the application of the expected database to handle the local foreign server dot...  The application doesn't care really.  Yeah, I think that's what really...  The database is doing something.  Yeah.  If I understand.  Yeah, so for...  I think application, once it has said that this is a foreign server  and this is foreign data,  it has already delegated all that processing to the postgres equals.  So, I mean it's the postgres equals responsibility to take care of that, however it does.  I think the exact case...  You understand my case, but I think you might not.  Yeah.  I'm talking...  I'm an application, I'm talking to Oracle plus Postgres.  And I just do something, something simple.  Underneath the seams, the Postgres happens to use 4D data wrappers.  And does it replace the men?  But I'm doing it to replace the men on top.  Yeah, in which case the local transaction will be prepared,  which will prepare the foreign transactions and it will wait for the application to tell me when to commit.  And once that happens, that's what he said.  The commit is no point of written, right?  So, I mean it all hinges on the local commit.  Whenever it comes, it will go and commit the preparation on sections.  Okay, let's talk about atomic visibility.  And this is atomic visibility.  Jokes apart.  So, I have some rough ideas about what atomic visibility should be.  It's a harder problem to solve.  But let's say it might be possible to solve that problem in certain cases.  Now, where is the visibility broken?  I have given one case where it takes to commit foreign prepared transaction.  You go one by one and there is a delay in between.  So, somebody comes in, you see an in-between state,  which is actually the foreign transaction change.  But the other possible problem that can happen is MVCC allows older versions to be read.  So, those foreign servers which are using MVCC,  if there is a prepared transaction, they will allow the older version of that data to be read.  Whereas, while the prepared transaction is not committed or resolved,  it is yet to be committed.  So, basically what will happen is you commit on one foreign server.  Before you go and commit on the other foreign server, somebody can come in,  again access these two foreign servers.  It will see committed data on foreign server one, but not on foreign server two.  Foreign server two, it will still see the old data.  And this can happen even when there is, I mean, the data is being accessed through the same master,  through the same backend, sorry, through a different backend.  So, the possible solution here is to wait for that prepared transaction to commit.  And the way we wait is use stronger locking.  So, we always do foreshare or serializable reads,  which means that the transaction is, the data which has been modified is,  which is in prepared state holds the lock and anybody who tries to read the data is made to wait.  But that's going to affect performance because even if the transaction,  even if there is no reader for that state, we are going to hold the locks,  which I think is a performance impact.  The second possibility is if it's all going through the same PostgreSQL, same master server,  then it basically, so if we couldn't prepare the, resolve the transaction because the connectivity was lost  or we couldn't access that transaction.  And if somebody comes to access the same data, probably if we are able to reach the foreign server,  then we should first commit the transaction that was prepared and then allow or then start querying for the other transactions.  Now, the question is in concurrent case where there are multiple backends trying to access the same foreign data,  how do we detect this situation and apply the solution?  So I'm still working on it, thinking about it.  So that's something that can be discussed.  We have to solve that issue or not stand by. If you've got a connection to a primary and you're using how to stand by, you can see a transaction committed to everyone,  not the other, and this is a much harder case.  So I'm obviously, solvable except for certain special features.  Yeah, that's, yeah, yeah.  So, well, that's what, so basically, we basically now the current status of this patch, it has already gone through some discussions.  And I'm really thankful to Kevin, Robert, Tom, who had given excellent suggestions on that thread or in our personal private email chains.  So I submitted one patch in February, which had a lot of to-dos to just elaborate the idea that I had.  And there is a patch that I have, which takes care of many to-dos and I am supposed to submit in the commit phase.  Which addresses atomic commit?  It does not address atomic visibility, but I think that's one step forward than what we have now.  So once we get to that, then we'll start thinking about the atomic visibility.  Yeah, that's it.  If we have any questions.  I want to get back to my two-phase commit.  Okay.  So here's the scenario.  Top-level application does the prepare across two services, say, postgres with 20 data wrappers in Oracle.  At that time, there's been no prepare propagated down to the 40 data wrappers, correct?  So you don't actually have a guarantee that the 40 data wrappers will be able to commit.  At the time that you prepare the postgres controlling transaction, it will prepare the other.  Willem, you're going to say that?  Will do we have a...  The local database proceeds to prepare it as a post-stand.  I don't know why it's not going to work.  I didn't see that on the postgres.  Well, the thing is, as it's pretty complicated, if I start putting all the integrities, I will get the whole hall sleeping.  Anyway, so the thing is, so there is prepare that has client said prepare, which actually does...  Prepare one, two, three, whatever it is.  It's not going to get back and say I have prepared unless it gets a guarantee from each of these.  And then when you commit, do the same.  So if it comes, then you commit the prepared transactions and only then come back.  I have a slide for next time.  There's a small line in the master ascending. This is pushed down to all four.  No, then in that case, I should just show my patch here, which is like thousand lines of code.  And we should discuss the patch. I'm happy about it.  I don't know if it's a thousand lines of code on that.  Yeah, yeah.  Any other questions?  What kind of a latency is it? Are you seeing when you have the day three servers and the two days of the meeting?  I'd say things are tangled. What kind of latency is it?
Transacting_with_foreign_servers_-_Two_s_company__three_is_____0_59.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____60_407.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____408_1283.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____1284_1643.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____1644_3959.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____3960_5351.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____5352_5435.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____5436_6671.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____6672_8039.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____8040_8087.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____8088_8171.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____8172_10019.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____10020_13031.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____13032_13091.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____13092_13415.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____13416_13463.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____13464_16451.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____16452_16499.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____16500_16547.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____16548_16607.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____16608_20759.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____20760_21647.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____21648_24659.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____24660_24707.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____24708_24815.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____24816_25787.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____25788_26819.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____26820_26867.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____26868_26915.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____26916_26975.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____26976_27563.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____27564_32663.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____32664_34283.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____34284_37223.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____37224_37271.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____37272_37319.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____37320_37775.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____37776_37823.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____37824_37931.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____37932_38615.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____38616_38723.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____38724_40907.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____40908_41567.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____41568_41639.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____41640_41687.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____41688_45635.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____45636_45695.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____45696_48767.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____48768_49919.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____49920_52211.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____52212_54227.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____54228_57623.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____57624_57683.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____57684_58187.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____58188_63851.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____63852_63911.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____63912_63959.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____63960_64007.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____64008_68171.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____68172_68267.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____68268_68315.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____68316_70487.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____70488_72047.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____72048_75899.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____75900_76031.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____76032_76199.png Transacting_with_foreign_servers_-_Two_s_company__three_is_____76200_76217.png
Okay, let's start with the presentation. Welcome you all for discussion about transactions involving foreign servers. Now, as Robert pointed out in yesterday's lightning talk, there are two things when it comes to transaction manager. One is atomic commit and second is atomic visibility. Together they are known as the A4 atomicity in the asset property. So that's really the agenda. So we are going to talk about atomic commits and then I will touch upon atomic visibility. I will go in more detail about atomic commit. So let's see what atomicity is. I mean many of you must be knowing this but let's start with that. So we have some data, two tables. This is an example and this data is subjected to a transaction which basically updates both of these tables. Then if this transaction fails, what atomicity says is we should see the data as if the transaction was not running or it never ran. If the transaction succeeded, we should see all the changes. While the transaction is running, we should not see anything which is going on in the transaction as well as if the transaction completes or if it fails, even then we should not see an intermediate state in which the transaction was. Now these properties are known as all or none property, which means either we see all the changes by a transaction or we see none of the changes. The second property is called indivisibility from where the term atomicity comes. We should not see anything in the intermediate state of the transaction. The way Robert has put it is he has called all or none property as the atomic commit and indivisibility property as atomic visibility. Now we have foreign servers, foreign tables and FDWs. So if we expand this data or extend this data to foreign servers, this is how it will look like. So the coordinator is really the PostgreSQL server. I have named it as coordinator in the two-phase commit context. It's really PostgreSQL server with two tables which really are pointers to two tables on different foreign servers or same foreign servers with different FDWs, same FDW in any kind of combination. And still we want that the same properties that we expected to be obeyed in case of local data to be obeyed in case of this foreign data as well. So again the same thing. So either everything is seen or none of the changes are seen. Any in-between state should not be seen. So when we extend this notion of atomicity to foreign data, that's where I call it as distributed atomicity. There are multiple naming conventions for it. Now some applications may not want atomicity and some applications may want atomicity. There is difference and we'll touch upon this. Now this requirement becomes really important and serious because now we have foreign table inheritance. So we can have one parent table which is sitting in the PostgreSQL coordinator with two or many childs and each child sitting really on the foreign server. A single DML statement without any begin in there can now, so you just say update parent and start updating the parent table, it's going to update the children that it needs to touch whatever are qualified. So there is no explicit transaction but still you want the effects of this single DML to be atomic, either all of the foreign tables should be updated or none of them should be updated and all the atomicity rules. So that's what the notion of atomicity is and when we extend that atomicity to that notion to the foreign servers, that's what we expect it to work like. But as of now, as of 9.5, the thing is that the atomicity is not guaranteed and the transaction management is entirely implemented by each of the foreign data wrappers. So PostgreSQL FDW uses one phase commit which means that when the client says commit, it goes and tries to commit the transactions on foreign servers. If any of them fails, the rest of the foreign servers are requested to abort their transaction or if there is a connectivity failure, those are bought. So we cannot guarantee that when a transaction commits on local server or is supposed to commit, it gets committed on all the foreign servers or aborted on all the foreign servers. So what I'm going to cover is how do we achieve atomicity in PostgreSQL with foreign data wrapper and foreign server. There is a proposal already there in the hackers and there is a patch that I'm working on. So what I'm going to discuss here is some of the parts of that patch as well as the detailed solution. Any questions? I mean, it's fine if you ask questions in between. We have it. Before we enter into the two phase commit and the details of the implementation and solution, if anybody has questions. You talked about failure on one of the foreign servers causing the whole transaction to fail, potentially. The implications of multiple foreign servers, but given the foreign table, it only references one server. So how does the transaction apply to multiple foreign tables? Is this entirely true in comparison? No, it's a case that I'm taking. So if there is only one foreign server and you are saying that there is no local change that has happened, then it works perfectly because either you commit on that server or you abort on that server and all the changes are there on that foreign server. So as long as that foreign server guarantees acid property, atomicity, it's atomic. But when you... You're talking about a transaction on a local server which includes... Foreign server....over here, but I think getting over there. Yeah, so maybe this was not... Yeah, so this transaction is really what is fired on the local server or PostgreSQL and which goes to the foreign server. Yeah? What about the... Yeah, as I explained, there are two things. One is all or none property, atomic commit, and there is atomic visibility. I will touch upon that point at the end of the slides. Yeah, so let's come to the implementation now. Okay, so there is fairly standard solution to achieving atomicity in this case, and which is known as two-phase commit protocol. There are multiple variations of it. There are multiple protocols, but two-phase commit protocol is... It's a fairly accepted standard, so that's what we'll discuss now as a solution in this case. And what I will try to do is try to discuss two-phase commit in the context of PostgreSQL. So the way PostgreSQL transaction management works is it basically goes in multiple phases. At the time of commit, there are important two phases. One is the pre-commit phase and the commit phase. So during pre-commit phase, basically the deferred constraint application, trigger processing, all of that happens. And then the transaction is brought to a state where we can say that now I can commit the transaction, and that's where the commit processing starts. Now where does the two-phase commit fit into? Because two-phase commit, as I said, there are two phases in which it works. Phase one is when we poll all the foreign servers to say, okay, can you commit? Can you commit? Can you commit? Can you commit? And if everybody says yes, then we tell them to commit individually. If anybody says no, then we abort them individually. So the polling is the first phase and the actual commit or abort happens in the second phase, which is popularly known as prepare and commit-prepare or rollback-prepare. So once a foreign server has prepared a transaction and said, yes, I can commit, he cannot go back and say, no, I cannot commit now. Okay, so we get a guarantee that the foreign transaction is now going to commit. So that's what this flow chart is about, detailing these states. So basically we try to poll each foreign server, try to prepare the transaction on each foreign server. If everybody says yes, then we start the commit processing locally and record that this local transaction is going to commit or is committed. And then we move to the phase two where we start telling each foreign servers to commit their individual transactions. If anybody says no, then we abort any foreign transactions which are not prepared, we just abort them. And then we go to the second phase where we abort to prepare transactions on all the foreign servers. Is there a kind of scenario like foreign server time? Yeah, I'll come to that. I'll come to that. Now the problem here is if there is any connectivity failure in phase one, then we go and abort all the transactions. If there is any connectivity failure in phase two, we know that that foreign server has told us that I have prepared and I am ready to commit. So we have a guarantee in our hand that that guy is going to pay us. Okay, so we use that guarantee further. So that's where the atomic commit comes from. Once we get this guarantee from each of the servers, we can tell them now this is the guarantee that I am going to use. So if there is any failure in phase two, any connectivity failure, server crash, local server crash, anything, then we have to remember that this guy had given me the guarantee. This guy had prepared the transaction. Now whenever the connectivity is established, the failure is overcome, we tell that guy to commit the transaction or abort the transaction, which yes, it ensures atomic commit, but then it opens up two problems. One is as atomicity guarantees on a single server that all the changes will be visible at once. Once you say this transaction committed, all the changes that that transaction did become visible at one single point of time. It is not guaranteed here because we go tell each of the foreign servers that commit, commit, commit, commit, and this delay is going to, if anybody goes in between and tries to see that what's on that foreign server, he is going to get some data which was as if it was not committed on the transaction. And any network failure or crash is going to keep this prepared transaction dangling or unresolved on those foreign servers, which means we have to try hard to get them resolved, otherwise they have blocked the resources. Whatever that transaction has done, any logs that have been taken, any data has been modified, it's going to be there till we actually commit the transaction and then that foreign server is capable of releasing those resources. So a lot of code that has, a lot of effort, a lot of code that has been written is to take care of this particular case where we try to resolve unresolved foreign transactions and that's what I will start explaining next. Does that answer your question? Any question about two-phase commit? Because the one part I didn't really mention is what is some of the work that the local database is connecting into the transaction and where its commit fits into this. No, please repeat the question. Well, you're using Postgres as a transaction manager and some of the people that are accessed by the distributed transaction have the database management commands that you talked about to a lot of parts of the transaction. Yeah. Okay, so, well, okay. I think I'm going to discuss that, but so local server is asymmetric here in the sense that it knows what it's doing. So that commit or abort of the local changes are available locally which can be seen. So, well, if at the end of the presentation you still have this question, we'll discuss it further. Okay, so now what we are going to discuss is how do we make sure that we resolve this unresolved transaction if they happen. And so this block that you are saying, prepare transaction on foreign server, I'm going to explain what exactly is done in that block. So obviously we have to remember whatever we have done in phase one, we have to remember where all we have to, where all we have prepared the transactions, what was the information related to that, so that we can deal with them in phase two. So the first block is really there for everything. I mean, even if there is an unresolved transaction, we don't want to tackle them. The first block is really important to remember what we have done in phase one, where all we have prepared the transactions. The second, so in order to resolve unresolved prepared transaction, we have to take into consideration that the local server itself can crash or maybe run out of the in memory, run out of the memory available and so on. So we have to persist whatever information we had remembered in phase one to the disk, so that when if the server crashes or needs to be restarted between phase one and phase two or during phase two, it has that information. And it is in the implementation that I have, it's stored in under the data directory and PGFDW exact directory. The structure of this directory is very similar to PG2 phase directory, where for every connection, every foreign connection that is open and where the transaction is prepared, we store information in one file. Just like PG2 phase stores information about one prepared transaction in one file. But that's not enough. So what if the server crashes and there is a standby that is going to take over? So in order, we have to replicate whatever information we have saved to the standby so that when the standby takes over and fails over, it can take the responsibility of resolving the unresolved transaction. So we have to, now the only way to get this information to the standby is through logs and so we log this information in the, log this information in the wall and that also helps us in crash recovery. So we actually don't have to, you know, f-sync the information every time we try, write to the disk. Now once we have taken enough measures to remember the transaction, remember the foreign transaction, we can go and actually prepare the transaction on the foreign server. If we reverse this order, if we prepare the transaction and crash, then we have, we have not, I mean, once we come back again, we do not have information of where we have prepared the transaction. So even before we prepare, we have to remember this bit of information. So that was the first part about remembering the prepared transaction information. The second part is how do we resolve, it's the, what steps to take when we resolve the foreign transaction, which means we commit or abort foreign transaction, foreign prepared transaction. So it's now the reverse way we have to go. So we commit or abort the foreign transaction on the foreign server, we tell it to commit prepared or rollback prepared, whatever syntax it is. If it's successful, we first log the removal of foreign transaction information and then we actually remove it from our persistent disk because we do not require it anymore. In case of, in case we are unsuccessful, we have to try again in the same loop as long as we succeed and we'll see how I have implemented that as well. Now, before that, let's talk about what should, what needs to be remembered. So the first thing is the transaction ID, the local transaction ID, which will tell us what we should do with this prepared transaction, whether we should go and abort it or whether we should go and rollback it. So the local transaction, once it has prepared everything, the local transaction is committed, it records that commit and then that record can be used later to decide what to be done with the foreign prepared transaction when we come to resolve it. Then this database ID, foreign server and user tells us where we have prepared the transaction and the last bit is about foreign transaction ID, which really identifies that foreign transaction, which is essentially GID on PostgreSQL and every RDBMS has its own way of identifying prepared transaction, which needs to be taken care of, taken care by the FDW. Kevin, does that answer your question and you still have the question? No, we've talked prior to this and I know the answer, I just think it's sort of missing from the presentation, which is that the managing database can be part of this distributed transaction and it doesn't need to be used to base commit, but it needs to be simple to commit between the prepared phase of all the other data. Just a fine point, I thought you needed to do anything. Okay, so this flow chart in phase one actually happens, is executed by the back end, which is performing the local transaction. So if that's... Okay, any questions till now? Because I'm slightly going to digress here next and talk about something else and come back to the point of two phase commit. In the sense for the transaction, because here last slide where I committed on the foreign server, what if the master were to go down, say it hasn't logged, but it's actually the transaction, and if you try with the master boot? In this thing? The transaction was successful, yes, and then the master explodes. Okay. But it still takes it as the finished transaction. Yeah, yeah, yeah. So when the master reboots, it will find that there is an entry in here, which says that I have prepared the transaction on foreign server. Now, it retries and... Yeah, it should come... I mean, the foreign server should come back and say, no, I do not see this transaction anymore in my repository or database. There is a requirement to which I will come when we discuss it further. The master would roll back the change on its side then, or if it would just say, oh, no, no. I mean, it can't do that. Now, it has said commit. No. So it basically boils down to how do we generate these transaction IDs, since that's what we provide during prepare, and I will talk about that in a minute. Well, I think that's the same case here as well. So if we log, we persist and crash or log and crash, but we do not execute this tape, it's the other way around. We never prepared that transaction, but we have in our repository a record saying, okay, there is a transaction prepared. Yeah. Okay, there was... Well, obviously not all foreign data wrappers have support for two-phase commit. We cannot support two-phase commit protocol, and what we should do with them. I mean, well, the straight and unfortunate answer is probably we cannot provide the atomicity for the transactions which involve those kinds of foreign servers. But still, what I'm showing here is the blue flow chart shows what PostgreSQL-DW does today, and the orange flow chart shows maybe what we can do. So what it does today is during pre-commit processing, it commits the transactions on all the foreign servers. It tries to commit. If anybody says, anybody gives an error, throws error, then it aborts all the remaining transactions. Now, depending upon what is the order of foreign servers in which... In order in which foreign servers are pulled, you can get different results. Some of the foreign servers will get a commit, some of them will get a bot, and it's really kind... It seems undeterministic, but... It really depends upon in which order those transactions were started to begin with. So what we might want to do is something better is we record the local commit and then go on to commit the transactions. So if we cannot abort the local transaction, we abort all the foreign transactions, of course. But if we can commit the local transaction, we try to commit the transactions on foreign servers. If anybody fails to commit, we leave that guy aside, but try to commit on all the rest of the foreign servers rather than going this approach where we just abort... abort all the other foreign servers once one has failed. That, I think, gives a bit of deterministic behavior here, but that's something that can be discussed or debated about. Okay. Well, that was little digression. Now, we get back to some more details about the implementation of what the patch does. It introduces two FDW hooks that need to be implemented by the FDW. One is the getPrepairId, which should give the prepareTransactionId for a given foreign server and user pair. Now, this we do for each... I mean, this is the responsibility of FDW because each foreign server and FDW might have different rules for identifiers, what is an acceptable identifier, what is not length, and many things. So that is delegated to the FDW through the hook. And this is what... The output of this is what is persisted and wall logged, as I have said. Now, this function is responsible to make sure that it gives a unique transaction ID which does not conflict with any future transactions or even the previous transactions. Because if it starts giving transaction IDs which are conflicting, then what you have said will come into picture that we committed a transaction ID, we committed a prepared transaction with a given transaction ID, and the local server crashed. So it came back, it had an entry with that foreign transaction ID. So it tries to again go and resolve that. And if somebody in between has created a transaction with the same ID again, we actually are going to resolve a transaction which we have not prepared. So this is an important criteria for giving back the transaction IDs, and the prepared transaction ID is that it needs to be unique, or fairly unique. And the second hook is... So that was the first phase hook. Now the second phase hook is which would actually commit, roll back, or prepare the transactions, which basically... Yeah, so, well, yeah, prepare is part of the first phase which should be taken care by this hook. So it is responsible for preparing transactions with the ID that was given before, and or commit or commit, roll back the prepared transaction. So it basically ends the transaction completely. There are certain books that the patch introduces. One is Atomic Foreign Transaction, which is a Boolean book, which tells you whether you want to use two-phase commit or not, whether you want atomicity for the transactions or not. But when it is on, and you say, I want atomic foreign transactions, it needs that all the participating servers support two-PC. Otherwise, it will throw... I mean, the transaction cannot guarantee atomicity. Well, I have implemented it in such a way, and it was probably easier also that it can be set anytime during the transaction. The value at the time of commit is what matters, so that if a transaction is going to conditionally touch multiple foreign servers, it can choose, depending upon which foreign server it chooses, whether it wants the transaction to be atomic or not, even within the transaction. And the second is, since we are using shared memory to store the information, the next group tells how many maximum number of transactions that can be prepared on foreign servers at a given time. So it limits the shared memory that we can use. I mean, in similar way as the two-phase implementation that we have. Okay, so how is foreign transaction resolved, where implemented? So the back-end which is doing the transaction, it takes the responsibility of executing the phase two, if the foreign servers are available even after phase one, but it tries only once. If it fails, then that information is available to any back-end in the shared memory or on the disk if the server crashes. What I have provided in the patch is a built-in function, which when executed, tries to resolve all the foreign transactions that are unresolved in a given database, because it needs to access the database for foreign server and user ID. Now, since we said that we need to try hard to resolve it repeated number of times, I have implemented a contrib module which, when loaded, runs a background worker process which fires these resolvers, I mean, which basically fires this built-in function in different databases. Okay, before we go to atomic visibility, any questions, any suggestions, comments? Any foreign server which supports 2PC? Yeah, so as long as it can prepare the transaction when the corresponding FDW says prepare, and as long as it can commit or roll back that prepared transaction when FDW says so, it's fine. The FDW implemented the appropriate type of database to connect to the FDW, so that it will authorize the FDW. Yeah, that's why those two hooks, I mean, if you know, if you have seen the FDW, it works with hooks. So there are hooks for multiple functionality. So this introduces two new hooks for getting that done. What happens if you use 2PACE commit for the host transaction? That's one. It's not required. It's not required. As far as I have implemented it, that's not required. And that puts an extra, see, the prepared transactions, as I've said, they take shared memory out of the server, resources, and everything. So if you prepare the transaction locally, that you are unnecessarily wasting resources, we already go through that phases. We have this pre-commit phase, and then we have commit phase during the transaction commit. This pre-commit phase actually is equivalent to the prepare. But you might have 2PACE commit you apply an application. Yeah. Yeah, in which case we have to, we have to prepare the corresponding transaction and remember it. But only when the client has told us to prepare, not when there is direct commit from client. I think one of the things that was a crux for me when I really grasped it is that the point of no return is when the local transaction is absolutely committed. Because before that point, if the server crashes, you have to roll everybody back. After that point, you have to roll everybody back. That is the point of no return. And that transaction can't be the transaction controller for itself. So what you're talking about will only work if you have some other transaction manager managing the transaction, managing this kind of energy-serviced transaction. That seems to me like it would be a pretty unusual situation. It wouldn't have worked any too much, but it could happen again. Yeah. It would work as long as you had something as an outer transaction manager that made sure that the one that was managing what you were talking about was either a manager or a manager. Yeah. But I mean it's a whole extra layer of complexity. Yeah. I mean it's a... You can do it on purpose, but we know what trigger might run a foreign data wrapper. Like an application could be accessed in Qt databases, and they don't know that foreign data wrappers are being used. So that's where it would happen. No, but application, if it's doing something with the foreign tables, it should know that... What does the application might not know? It might be done through triggers. It might be writing a log entry, like a Luna. You know what's going to happen. But if they were... Yeah, I mean that's something that needs to be... I mean when we design something with foreign servers, I think that's something that needs to be taken care of. I mean if there is a valid case, yeah, I will consider that. I don't think it adds anything to what you have to do with what you're working on. It would just be an unusual work time. We're all still working on it. I think the application of the expected database to handle the local foreign server dot... The application doesn't care really. Yeah, I think that's what really... The database is doing something. Yeah. If I understand. Yeah, so for... I think application, once it has said that this is a foreign server and this is foreign data, it has already delegated all that processing to the postgres equals. So, I mean it's the postgres equals responsibility to take care of that, however it does. I think the exact case... You understand my case, but I think you might not. Yeah. I'm talking... I'm an application, I'm talking to Oracle plus Postgres. And I just do something, something simple. Underneath the seams, the Postgres happens to use 4D data wrappers. And does it replace the men? But I'm doing it to replace the men on top. Yeah, in which case the local transaction will be prepared, which will prepare the foreign transactions and it will wait for the application to tell me when to commit. And once that happens, that's what he said. The commit is no point of written, right? So, I mean it all hinges on the local commit. Whenever it comes, it will go and commit the preparation on sections. Okay, let's talk about atomic visibility. And this is atomic visibility. Jokes apart. So, I have some rough ideas about what atomic visibility should be. It's a harder problem to solve. But let's say it might be possible to solve that problem in certain cases. Now, where is the visibility broken? I have given one case where it takes to commit foreign prepared transaction. You go one by one and there is a delay in between. So, somebody comes in, you see an in-between state, which is actually the foreign transaction change. But the other possible problem that can happen is MVCC allows older versions to be read. So, those foreign servers which are using MVCC, if there is a prepared transaction, they will allow the older version of that data to be read. Whereas, while the prepared transaction is not committed or resolved, it is yet to be committed. So, basically what will happen is you commit on one foreign server. Before you go and commit on the other foreign server, somebody can come in, again access these two foreign servers. It will see committed data on foreign server one, but not on foreign server two. Foreign server two, it will still see the old data. And this can happen even when there is, I mean, the data is being accessed through the same master, through the same backend, sorry, through a different backend. So, the possible solution here is to wait for that prepared transaction to commit. And the way we wait is use stronger locking. So, we always do foreshare or serializable reads, which means that the transaction is, the data which has been modified is, which is in prepared state holds the lock and anybody who tries to read the data is made to wait. But that's going to affect performance because even if the transaction, even if there is no reader for that state, we are going to hold the locks, which I think is a performance impact. The second possibility is if it's all going through the same PostgreSQL, same master server, then it basically, so if we couldn't prepare the, resolve the transaction because the connectivity was lost or we couldn't access that transaction. And if somebody comes to access the same data, probably if we are able to reach the foreign server, then we should first commit the transaction that was prepared and then allow or then start querying for the other transactions. Now, the question is in concurrent case where there are multiple backends trying to access the same foreign data, how do we detect this situation and apply the solution? So I'm still working on it, thinking about it. So that's something that can be discussed. We have to solve that issue or not stand by. If you've got a connection to a primary and you're using how to stand by, you can see a transaction committed to everyone, not the other, and this is a much harder case. So I'm obviously, solvable except for certain special features. Yeah, that's, yeah, yeah. So, well, that's what, so basically, we basically now the current status of this patch, it has already gone through some discussions. And I'm really thankful to Kevin, Robert, Tom, who had given excellent suggestions on that thread or in our personal private email chains. So I submitted one patch in February, which had a lot of to-dos to just elaborate the idea that I had. And there is a patch that I have, which takes care of many to-dos and I am supposed to submit in the commit phase. Which addresses atomic commit? It does not address atomic visibility, but I think that's one step forward than what we have now. So once we get to that, then we'll start thinking about the atomic visibility. Yeah, that's it. If we have any questions. I want to get back to my two-phase commit. Okay. So here's the scenario. Top-level application does the prepare across two services, say, postgres with 20 data wrappers in Oracle. At that time, there's been no prepare propagated down to the 40 data wrappers, correct? So you don't actually have a guarantee that the 40 data wrappers will be able to commit. At the time that you prepare the postgres controlling transaction, it will prepare the other. Willem, you're going to say that? Will do we have a... The local database proceeds to prepare it as a post-stand. I don't know why it's not going to work. I didn't see that on the postgres. Well, the thing is, as it's pretty complicated, if I start putting all the integrities, I will get the whole hall sleeping. Anyway, so the thing is, so there is prepare that has client said prepare, which actually does... Prepare one, two, three, whatever it is. It's not going to get back and say I have prepared unless it gets a guarantee from each of these. And then when you commit, do the same. So if it comes, then you commit the prepared transactions and only then come back. I have a slide for next time. There's a small line in the master ascending. This is pushed down to all four. No, then in that case, I should just show my patch here, which is like thousand lines of code. And we should discuss the patch. I'm happy about it. I don't know if it's a thousand lines of code on that. Yeah, yeah. Any other questions? What kind of a latency is it? Are you seeing when you have the day three servers and the two days of the meeting? I'd say things are tangled. What kind of latency is it?
involving foreign servers. Now, as Robert pointed things when it comes to transaction manager. One Together they are known as the A4 atomicity in foreign table inheritance. So we can have one like. But as of now, as of 9.5, the thing is that the atomicity is not guaranteed and the achieve atomicity in PostgreSQL with foreign data transaction apply to multiple foreign tables? Is abort on that server and all the changes are pay us. Okay, so we use that guarantee further. atomicity guarantees on a single server that all resolve unresolved foreign transactions and distributed transaction have the database The second, so in order to resolve unresolved going to take over? So in order, we have to and fails over, it can take the responsibility of it's sort of missing from the presentation, which we provide during prepare, and I will talk about provide the atomicity for the transactions which order of foreign servers in which... In order in do for each... I mean, this is the responsibility we actually are going to resolve a transaction way, and it was probably easier also that it can database, because it needs to access the database required. It's not required. As far as I have once it has said that this is a foreign server foreign server two. Foreign server two, it will made to wait. But that's going to affect basically now the current status of this patch,