Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Mon, Oct 30, 2017 at 5:48 PM, Ashutosh Bapat
 wrote:
> On Thu, Oct 26, 2017 at 7:41 PM, Masahiko Sawada  
> wrote:
>>
>> Because I don't want to break the current user semantics. that is,
>> currently it's guaranteed that the subsequent reads can see the
>> committed result of previous writes even if the previous transactions
>> were distributed transactions. And it's ensured by writer side. If we
>> can make the reader side ensure it, the backend process don't need to
>> wait for the resolver process.
>>
>> The waiting backend process are released by resolver process after the
>> resolver process tried to resolve foreign transactions. Even if
>> resolver process failed to either connect to foreign server or to
>> resolve foreign transaction the backend process will be released and
>> the foreign transactions are leaved as dangling transaction in that
>> case, which are processed later. Also if resolver process takes a long
>> time to resolve foreign transactions for whatever reason the user can
>> cancel it by Ctl-c anytime.
>>
>
> So, there's no guarantee that the next command issued from the
> connection *will* see the committed data, since the foreign
> transaction might not have committed because of a network glitch
> (say). If we go this route of making backends wait for resolver to
> resolve the foreign transaction, we will have add complexity to make
> sure that the waiting backends are woken up in problematic events like
> crash of the resolver process OR if the resolver process hangs in a
> connection to a foreign server etc. I am not sure that the complexity
> is worth the half-guarantee.
>

Hmm, maybe I was wrong. I now think that the waiting backends can be
woken up only in following cases;
- The resolver process succeeded to resolve all foreign transactions.
- The user did the cancel (e.g. ctl-c).
- The resolver process failed to resolve foreign transaction for a
reason of there is no such prepared transaction on foreign server.

In other cases the resolver process should not release the waiters.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Thu, Oct 26, 2017 at 7:41 PM, Masahiko Sawada  wrote:
>
> Because I don't want to break the current user semantics. that is,
> currently it's guaranteed that the subsequent reads can see the
> committed result of previous writes even if the previous transactions
> were distributed transactions. And it's ensured by writer side. If we
> can make the reader side ensure it, the backend process don't need to
> wait for the resolver process.
>
> The waiting backend process are released by resolver process after the
> resolver process tried to resolve foreign transactions. Even if
> resolver process failed to either connect to foreign server or to
> resolve foreign transaction the backend process will be released and
> the foreign transactions are leaved as dangling transaction in that
> case, which are processed later. Also if resolver process takes a long
> time to resolve foreign transactions for whatever reason the user can
> cancel it by Ctl-c anytime.
>

So, there's no guarantee that the next command issued from the
connection *will* see the committed data, since the foreign
transaction might not have committed because of a network glitch
(say). If we go this route of making backends wait for resolver to
resolve the foreign transaction, we will have add complexity to make
sure that the waiting backends are woken up in problematic events like
crash of the resolver process OR if the resolver process hangs in a
connection to a foreign server etc. I am not sure that the complexity
is worth the half-guarantee.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Thu, Oct 26, 2017 at 4:11 PM, Masahiko Sawada  wrote:
>> Why do we want the the backend to linger behind, once it has added its
>> foreign transaction entries in the shared memory and informed resolver
>> about it? The foreign connections may take their own time and even
>> after that there is no guarantee that the foreign transactions will be
>> resolved in case the foreign server is not available. So, why to make
>> the backend wait?
>
> Because I don't want to break the current user semantics. that is,
> currently it's guaranteed that the subsequent reads can see the
> committed result of previous writes even if the previous transactions
> were distributed transactions.

Right, this is very important, and having the backend wait for the
resolver(s) is, I think, the right way to implement it.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Oct 26, 2017 at 2:36 PM, Ashutosh Bapat
 wrote:
> On Wed, Oct 25, 2017 at 3:15 AM, Masahiko Sawada  
> wrote:
>>
>> Foreign Transaction Resolver
>> ==
>> I introduced a new background worker called "foreign transaction
>> resolver" which is responsible for resolving the transaction prepared
>> on foreign servers. The foreign transaction resolver process is
>> launched by backend processes when commit/rollback transaction. And it
>> periodically resolves the queued transactions on a database as long as
>> the queue is not empty. If the queue has been empty for the certain
>> time specified by foreign_transaction_resolver_time GUC parameter, it
>> exits. It means that the backend doesn't launch a new resolver process
>> if the resolver process is already working. In this case, the backend
>> process just adds the entry to the queue on shared memory and wake it
>> up. The maximum number of resolver process we can launch is controlled
>> by max_foreign_transaction_resolvers. So we recommends to set larger
>> max_foreign_transaction_resolvers value than the number of databases.
>> The resolver process also tries to resolve dangling transaction as
>> well in a cycle.
>>
>> Processing Sequence
>> =
>> I've changed the processing sequence of resolving foreign transaction
>> so that the second phase of two-phase commit protocol (COMMIT/ROLLBACK
>> prepared) is executed by a resolver process, not by backend process.
>> The basic processing sequence is following;
>>
>> * Backend process
>> 1. In pre-commit phase, the backend process saves fdwxact entries, and
>> then prepares transaction on all foreign servers that can execute
>> two-phase commit protocol.
>> 2. Local commit.
>> 3. Enqueue itself to the shmem queue and change its status to WAITING
>> 4. launch or wakeup a resolver process and wait
>>
>> * Resolver process
>> 1. Dequeue the waiting process from shmem qeue
>> 2. Collect the fdwxact entries that are associated with the waiting 
>> process.
>> 3. Resolve foreign transactoins
>> 4. Release the waiting process
>
> Why do we want the the backend to linger behind, once it has added its
> foreign transaction entries in the shared memory and informed resolver
> about it? The foreign connections may take their own time and even
> after that there is no guarantee that the foreign transactions will be
> resolved in case the foreign server is not available. So, why to make
> the backend wait?

Because I don't want to break the current user semantics. that is,
currently it's guaranteed that the subsequent reads can see the
committed result of previous writes even if the previous transactions
were distributed transactions. And it's ensured by writer side. If we
can make the reader side ensure it, the backend process don't need to
wait for the resolver process.

The waiting backend process are released by resolver process after the
resolver process tried to resolve foreign transactions. Even if
resolver process failed to either connect to foreign server or to
resolve foreign transaction the backend process will be released and
the foreign transactions are leaved as dangling transaction in that
case, which are processed later. Also if resolver process takes a long
time to resolve foreign transactions for whatever reason the user can
cancel it by Ctl-c anytime.

>>
>> 5. Wake up and restart
>>
>> This is still under the design phase and I'm sure that there is room
>> for improvement and consider more sensitive behaviour but I'd like to
>> share the current status of the patch. The patch includes regression
>> tests but not includes fully documentation.
>
> Any background worker, backend should be child of the postmaster, so
> we should not let a backend start a resolver process. It should be the
> job of the postmaster.
>

Of course I won't. I used the term of "the backend process launches
the resolver process" for explaining easier. Sorry for confusing you.
The backend process calls RegisterDynamicBackgroundWorker() function
to launch a resolver process, so they are launched by postmaster.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Wed, Oct 25, 2017 at 3:15 AM, Masahiko Sawada  wrote:
>
> Foreign Transaction Resolver
> ==
> I introduced a new background worker called "foreign transaction
> resolver" which is responsible for resolving the transaction prepared
> on foreign servers. The foreign transaction resolver process is
> launched by backend processes when commit/rollback transaction. And it
> periodically resolves the queued transactions on a database as long as
> the queue is not empty. If the queue has been empty for the certain
> time specified by foreign_transaction_resolver_time GUC parameter, it
> exits. It means that the backend doesn't launch a new resolver process
> if the resolver process is already working. In this case, the backend
> process just adds the entry to the queue on shared memory and wake it
> up. The maximum number of resolver process we can launch is controlled
> by max_foreign_transaction_resolvers. So we recommends to set larger
> max_foreign_transaction_resolvers value than the number of databases.
> The resolver process also tries to resolve dangling transaction as
> well in a cycle.
>
> Processing Sequence
> =
> I've changed the processing sequence of resolving foreign transaction
> so that the second phase of two-phase commit protocol (COMMIT/ROLLBACK
> prepared) is executed by a resolver process, not by backend process.
> The basic processing sequence is following;
>
> * Backend process
> 1. In pre-commit phase, the backend process saves fdwxact entries, and
> then prepares transaction on all foreign servers that can execute
> two-phase commit protocol.
> 2. Local commit.
> 3. Enqueue itself to the shmem queue and change its status to WAITING
> 4. launch or wakeup a resolver process and wait
>
> * Resolver process
> 1. Dequeue the waiting process from shmem qeue
> 2. Collect the fdwxact entries that are associated with the waiting 
> process.
> 3. Resolve foreign transactoins
> 4. Release the waiting process

Why do we want the the backend to linger behind, once it has added its
foreign transaction entries in the shared memory and informed resolver
about it? The foreign connections may take their own time and even
after that there is no guarantee that the foreign transactions will be
resolved in case the foreign server is not available. So, why to make
the backend wait?

>
> 5. Wake up and restart
>
> This is still under the design phase and I'm sure that there is room
> for improvement and consider more sensitive behaviour but I'd like to
> share the current status of the patch. The patch includes regression
> tests but not includes fully documentation.

Any background worker, backend should be child of the postmaster, so
we should not let a backend start a resolver process. It should be the
job of the postmaster.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Fri, Sep 29, 2017 at 9:12 PM, Robert Haas  wrote:
>
> It's possible that we might find that neither of the above approaches
> are practical and that the performance benefits of resolving the
> transaction from the original connection are large enough that we want
> to try to make it work anyhow.  However, I think we can postpone that
> work to a future time.  Any general solution to this problem at least
> needs to be ABLE to resolve transactions at a later time from a
> different session, so let's get that working first, and then see what
> else we want to do.
>

 +1.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Daniel Gustafsson]

> On 02 Oct 2017, at 08:31, Masahiko Sawada  wrote:
> 
> On Sat, Sep 30, 2017 at 12:42 AM, Robert Haas  wrote:
>> On Wed, Sep 27, 2017 at 11:15 PM, Masahiko Sawada  
>> wrote:
>>> I think that making a resolver process have connection caches to each
>>> foreign server for a while can reduce the overhead of connection to
>>> foreign servers. These connections will be invalidated by DDLs. Also,
>>> most of the time we spend to commit a distributed transaction is the
>>> interaction between the coordinator and foreign servers using
>>> two-phase commit protocal. So I guess the time in signalling to a
>>> resolver process would not be a big overhead.
>> 
>> I agree.  Also, in the future, we might try to allow connections to be
>> shared across backends.  I did some research on this a number of years
>> ago and found that every operating system I investigated had some way
>> of passing a file descriptor from one process to another -- so a
>> shared connection cache might be possible.
> 
> It sounds good idea.
> 
>> Also, we might port the whole backend to use threads, and then this
>> problem goes way.  But I don't have time to write that patch this
>> week.  :-)
>> 
>> It's possible that we might find that neither of the above approaches
>> are practical and that the performance benefits of resolving the
>> transaction from the original connection are large enough that we want
>> to try to make it work anyhow.  However, I think we can postpone that
>> work to a future time.  Any general solution to this problem at least
>> needs to be ABLE to resolve transactions at a later time from a
>> different session, so let's get that working first, and then see what
>> else we want to do.
> 
> I understood and agreed. I'll post the first version patch of new
> design to next CF.

Closing this patch with Returned with feedback in this commitfest, looking
forward to a new version in an upcoming commitfest.

cheers ./daniel

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Sat, Sep 30, 2017 at 12:42 AM, Robert Haas  wrote:
> On Wed, Sep 27, 2017 at 11:15 PM, Masahiko Sawada  
> wrote:
>> I think that making a resolver process have connection caches to each
>> foreign server for a while can reduce the overhead of connection to
>> foreign servers. These connections will be invalidated by DDLs. Also,
>> most of the time we spend to commit a distributed transaction is the
>> interaction between the coordinator and foreign servers using
>> two-phase commit protocal. So I guess the time in signalling to a
>> resolver process would not be a big overhead.
>
> I agree.  Also, in the future, we might try to allow connections to be
> shared across backends.  I did some research on this a number of years
> ago and found that every operating system I investigated had some way
> of passing a file descriptor from one process to another -- so a
> shared connection cache might be possible.

It sounds good idea.

> Also, we might port the whole backend to use threads, and then this
> problem goes way.  But I don't have time to write that patch this
> week.  :-)
>
> It's possible that we might find that neither of the above approaches
> are practical and that the performance benefits of resolving the
> transaction from the original connection are large enough that we want
> to try to make it work anyhow.  However, I think we can postpone that
> work to a future time.  Any general solution to this problem at least
> needs to be ABLE to resolve transactions at a later time from a
> different session, so let's get that working first, and then see what
> else we want to do.
>

I understood and agreed. I'll post the first version patch of new
design to next CF.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Wed, Sep 27, 2017 at 11:15 PM, Masahiko Sawada  wrote:
> I think that making a resolver process have connection caches to each
> foreign server for a while can reduce the overhead of connection to
> foreign servers. These connections will be invalidated by DDLs. Also,
> most of the time we spend to commit a distributed transaction is the
> interaction between the coordinator and foreign servers using
> two-phase commit protocal. So I guess the time in signalling to a
> resolver process would not be a big overhead.

I agree.  Also, in the future, we might try to allow connections to be
shared across backends.  I did some research on this a number of years
ago and found that every operating system I investigated had some way
of passing a file descriptor from one process to another -- so a
shared connection cache might be possible.

Also, we might port the whole backend to use threads, and then this
problem goes way.  But I don't have time to write that patch this
week.  :-)

It's possible that we might find that neither of the above approaches
are practical and that the performance benefits of resolving the
transaction from the original connection are large enough that we want
to try to make it work anyhow.  However, I think we can postpone that
work to a future time.  Any general solution to this problem at least
needs to be ABLE to resolve transactions at a later time from a
different session, so let's get that working first, and then see what
else we want to do.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Wed, Sep 27, 2017 at 4:05 PM, Ashutosh Bapat
 wrote:
> On Wed, Sep 27, 2017 at 12:11 PM, Masahiko Sawada  
> wrote:
>> On Tue, Sep 26, 2017 at 9:50 PM, Robert Haas  wrote:
>>> On Tue, Sep 26, 2017 at 5:06 AM, Masahiko Sawada  
>>> wrote:
 Based on the review comment from Robert, I'm planning to do the big
 change to the architecture of this patch so that a backend process
 work together with a dedicated background worker that is responsible
 for resolving the foreign transactions. For the usage of this feature,
 it will be almost the same as what this patch has been doing except
 for adding a new GUC paramter that controls the number of resovler
 process launch. That is, we can have multiple resolver process to keep
 latency down.
>>>
>>> Multiple resolver processes is useful but gets a bit complicated.  For
>>> example, if process 1 has a connection open to foreign server A and
>>> process 2 does not, and a request arrives that needs to be handled on
>>> foreign server A, what happens?  If process 1 is already busy doing
>>> something else, probably we want process 2 to try to open a new
>>> connection to foreign server A and handle the request.  But if process
>>> 1 and 2 are both idle, ideally we'd like 1 to get that request rather
>>> than 2.  That seems a bit difficult to get working though.  Maybe we
>>> should just ignore such considerations in the first version.
>>
>> I understood. I keep it simple in the first version.
>
> While a resolver process is useful for resolving transaction later, it
> seems performance effective to try to resolve the prepared foreign
> transaction, in post-commit phase, in the same backend which prepared
> those for two reasons 1. the backend already has a connection to that
> foreign server 2. it has just run some commands to completion on that
> foreign server, so it's highly likely that a COMMIT PREPARED would
> succeed too. If we let a resolver process do that, we will spend time
> in 1. signalling resolver process 2. setting up a connection to the
> foreign server and 3. by the time resolver process tries to resolve
> the prepared transaction the foreign server may become unavailable,
> thus delaying the resolution.

I think that making a resolver process have connection caches to each
foreign server for a while can reduce the overhead of connection to
foreign servers. These connections will be invalidated by DDLs. Also,
most of the time we spend to commit a distributed transaction is the
interaction between the coordinator and foreign servers using
two-phase commit protocal. So I guess the time in signalling to a
resolver process would not be a big overhead.

> Said that, I agree that post-commit phase doesn't have a transaction
> of itself, and thus any catalog lookup, error reporting is not
> possible. We will need some different approach here, which may not be
> straight forward. So, we may need to delay this optimization for v2. I
> think we have discussed this before, but I don't find a mail off-hand.
>
>>
 * Resovler processes
 1. Fetch PGPROC entry from the shmem queue and get its XID (say, XID-a).
 2. Get the fdw_xact_state entry from shmem hash by XID-a.
 3. Iterate fdw_xact entries using the index, and resolve the foreign
 transactions.
 3-a. If even one foreign transaction failed to resolve, raise an error.
 4. Change the waiting backend state to FDWXACT_COMPLETED and release it.
>>>
>>> Comments:
>>>
>>> - Note that any error we raise here won't reach the user; this is a
>>> background process.  We don't want to get into a loop where we just
>>> error out repeatedly forever -- at least not if there's any other
>>> reasonable choice.
>>
>> Thank you for the comments.
>>
>> Agreed.
>
> We should probably log an error message in the server log, so that
> DBAs are aware of such a failure. Is that something you are
> considering to do?

Yes, a resolver process logs an error message in that case.

>
>>
>>> - I suggest that we ought to track the status for each XID separately
>>> on each server rather than just track the XID status overall.  That
>>> way, if transaction resolution fails on one server, we don't keep
>>> trying to reconnect to the others.
>>
>> Agreed. In the current patch we manage fdw_xact entries that track the
>> status for each XID separately on each server. I'm going to use the
>> same mechanism. The resolver process get an target XID from shmem
>> queue and get the all fdw_xact entries associated with the XID from
>> the fdw_xact array in shmem. But since the scanning the whole fdw_xact
>> entries could be slow because the number of entry of fdw_xact array
>> could be a large number (e.g, max_connections * # of foreign servers),
>>  I'm considering to have a linked list of the all fdw_xact entries
>> associated with same XID, and to have a shmem hash pointing to the
>> first 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Wed, Sep 27, 2017 at 12:11 PM, Masahiko Sawada  wrote:
> On Tue, Sep 26, 2017 at 9:50 PM, Robert Haas  wrote:
>> On Tue, Sep 26, 2017 at 5:06 AM, Masahiko Sawada  
>> wrote:
>>> Based on the review comment from Robert, I'm planning to do the big
>>> change to the architecture of this patch so that a backend process
>>> work together with a dedicated background worker that is responsible
>>> for resolving the foreign transactions. For the usage of this feature,
>>> it will be almost the same as what this patch has been doing except
>>> for adding a new GUC paramter that controls the number of resovler
>>> process launch. That is, we can have multiple resolver process to keep
>>> latency down.
>>
>> Multiple resolver processes is useful but gets a bit complicated.  For
>> example, if process 1 has a connection open to foreign server A and
>> process 2 does not, and a request arrives that needs to be handled on
>> foreign server A, what happens?  If process 1 is already busy doing
>> something else, probably we want process 2 to try to open a new
>> connection to foreign server A and handle the request.  But if process
>> 1 and 2 are both idle, ideally we'd like 1 to get that request rather
>> than 2.  That seems a bit difficult to get working though.  Maybe we
>> should just ignore such considerations in the first version.
>
> I understood. I keep it simple in the first version.

While a resolver process is useful for resolving transaction later, it
seems performance effective to try to resolve the prepared foreign
transaction, in post-commit phase, in the same backend which prepared
those for two reasons 1. the backend already has a connection to that
foreign server 2. it has just run some commands to completion on that
foreign server, so it's highly likely that a COMMIT PREPARED would
succeed too. If we let a resolver process do that, we will spend time
in 1. signalling resolver process 2. setting up a connection to the
foreign server and 3. by the time resolver process tries to resolve
the prepared transaction the foreign server may become unavailable,
thus delaying the resolution.

Said that, I agree that post-commit phase doesn't have a transaction
of itself, and thus any catalog lookup, error reporting is not
possible. We will need some different approach here, which may not be
straight forward. So, we may need to delay this optimization for v2. I
think we have discussed this before, but I don't find a mail off-hand.

>
>>> * Resovler processes
>>> 1. Fetch PGPROC entry from the shmem queue and get its XID (say, XID-a).
>>> 2. Get the fdw_xact_state entry from shmem hash by XID-a.
>>> 3. Iterate fdw_xact entries using the index, and resolve the foreign
>>> transactions.
>>> 3-a. If even one foreign transaction failed to resolve, raise an error.
>>> 4. Change the waiting backend state to FDWXACT_COMPLETED and release it.
>>
>> Comments:
>>
>> - Note that any error we raise here won't reach the user; this is a
>> background process.  We don't want to get into a loop where we just
>> error out repeatedly forever -- at least not if there's any other
>> reasonable choice.
>
> Thank you for the comments.
>
> Agreed.

We should probably log an error message in the server log, so that
DBAs are aware of such a failure. Is that something you are
considering to do?

>
>> - I suggest that we ought to track the status for each XID separately
>> on each server rather than just track the XID status overall.  That
>> way, if transaction resolution fails on one server, we don't keep
>> trying to reconnect to the others.
>
> Agreed. In the current patch we manage fdw_xact entries that track the
> status for each XID separately on each server. I'm going to use the
> same mechanism. The resolver process get an target XID from shmem
> queue and get the all fdw_xact entries associated with the XID from
> the fdw_xact array in shmem. But since the scanning the whole fdw_xact
> entries could be slow because the number of entry of fdw_xact array
> could be a large number (e.g, max_connections * # of foreign servers),
>  I'm considering to have a linked list of the all fdw_xact entries
> associated with same XID, and to have a shmem hash pointing to the
> first fdw_xact entry of the linked lists for each XID. That way, we
> can find the target fdw_xact entries from the array in O(1).
>

If we want to do something like this, would it be useful to use a data
structure similar to what is used for maintaining subtrasactions? Just
a thought.

>> - If we go to resolve a remote transaction and find that no such
>> remote transaction exists, what should we do?  I'm inclined to think
>> that we should regard that as if we had succeeded in resolving the
>> transaction.  Certainly, if we've retried the server repeatedly, it
>> might be that we previously succeeded in resolving the transaction but
>> then the network connection was broken before we got the success
>> 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Tue, Sep 26, 2017 at 9:50 PM, Robert Haas  wrote:
> On Tue, Sep 26, 2017 at 5:06 AM, Masahiko Sawada  
> wrote:
>> Based on the review comment from Robert, I'm planning to do the big
>> change to the architecture of this patch so that a backend process
>> work together with a dedicated background worker that is responsible
>> for resolving the foreign transactions. For the usage of this feature,
>> it will be almost the same as what this patch has been doing except
>> for adding a new GUC paramter that controls the number of resovler
>> process launch. That is, we can have multiple resolver process to keep
>> latency down.
>
> Multiple resolver processes is useful but gets a bit complicated.  For
> example, if process 1 has a connection open to foreign server A and
> process 2 does not, and a request arrives that needs to be handled on
> foreign server A, what happens?  If process 1 is already busy doing
> something else, probably we want process 2 to try to open a new
> connection to foreign server A and handle the request.  But if process
> 1 and 2 are both idle, ideally we'd like 1 to get that request rather
> than 2.  That seems a bit difficult to get working though.  Maybe we
> should just ignore such considerations in the first version.

I understood. I keep it simple in the first version.

>> * Resovler processes
>> 1. Fetch PGPROC entry from the shmem queue and get its XID (say, XID-a).
>> 2. Get the fdw_xact_state entry from shmem hash by XID-a.
>> 3. Iterate fdw_xact entries using the index, and resolve the foreign
>> transactions.
>> 3-a. If even one foreign transaction failed to resolve, raise an error.
>> 4. Change the waiting backend state to FDWXACT_COMPLETED and release it.
>
> Comments:
>
> - Note that any error we raise here won't reach the user; this is a
> background process.  We don't want to get into a loop where we just
> error out repeatedly forever -- at least not if there's any other
> reasonable choice.

Thank you for the comments.

Agreed.

> - I suggest that we ought to track the status for each XID separately
> on each server rather than just track the XID status overall.  That
> way, if transaction resolution fails on one server, we don't keep
> trying to reconnect to the others.

Agreed. In the current patch we manage fdw_xact entries that track the
status for each XID separately on each server. I'm going to use the
same mechanism. The resolver process get an target XID from shmem
queue and get the all fdw_xact entries associated with the XID from
the fdw_xact array in shmem. But since the scanning the whole fdw_xact
entries could be slow because the number of entry of fdw_xact array
could be a large number (e.g, max_connections * # of foreign servers),
 I'm considering to have a linked list of the all fdw_xact entries
associated with same XID, and to have a shmem hash pointing to the
first fdw_xact entry of the linked lists for each XID. That way, we
can find the target fdw_xact entries from the array in O(1).

> - If we go to resolve a remote transaction and find that no such
> remote transaction exists, what should we do?  I'm inclined to think
> that we should regard that as if we had succeeded in resolving the
> transaction.  Certainly, if we've retried the server repeatedly, it
> might be that we previously succeeded in resolving the transaction but
> then the network connection was broken before we got the success
> message back from the remote server.  But even if that's not the
> scenario, I think we should assume that the DBA or some other system
> resolved it and therefore we don't need to do anything further.  If we
> assume anything else, then we just go into an infinite error loop,
> which isn't useful behavior.  We could log a message, though (for
> example, LOG: unable to resolve foreign transaction ... because it
> does not exist).

Agreed.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Tue, Sep 26, 2017 at 5:06 AM, Masahiko Sawada  wrote:
> Based on the review comment from Robert, I'm planning to do the big
> change to the architecture of this patch so that a backend process
> work together with a dedicated background worker that is responsible
> for resolving the foreign transactions. For the usage of this feature,
> it will be almost the same as what this patch has been doing except
> for adding a new GUC paramter that controls the number of resovler
> process launch. That is, we can have multiple resolver process to keep
> latency down.

Multiple resolver processes is useful but gets a bit complicated.  For
example, if process 1 has a connection open to foreign server A and
process 2 does not, and a request arrives that needs to be handled on
foreign server A, what happens?  If process 1 is already busy doing
something else, probably we want process 2 to try to open a new
connection to foreign server A and handle the request.  But if process
1 and 2 are both idle, ideally we'd like 1 to get that request rather
than 2.  That seems a bit difficult to get working though.  Maybe we
should just ignore such considerations in the first version.

> * Resovler processes
> 1. Fetch PGPROC entry from the shmem queue and get its XID (say, XID-a).
> 2. Get the fdw_xact_state entry from shmem hash by XID-a.
> 3. Iterate fdw_xact entries using the index, and resolve the foreign
> transactions.
> 3-a. If even one foreign transaction failed to resolve, raise an error.
> 4. Change the waiting backend state to FDWXACT_COMPLETED and release it.

Comments:

- Note that any error we raise here won't reach the user; this is a
background process.  We don't want to get into a loop where we just
error out repeatedly forever -- at least not if there's any other
reasonable choice.

- I suggest that we ought to track the status for each XID separately
on each server rather than just track the XID status overall.  That
way, if transaction resolution fails on one server, we don't keep
trying to reconnect to the others.

- If we go to resolve a remote transaction and find that no such
remote transaction exists, what should we do?  I'm inclined to think
that we should regard that as if we had succeeded in resolving the
transaction.  Certainly, if we've retried the server repeatedly, it
might be that we previously succeeded in resolving the transaction but
then the network connection was broken before we got the success
message back from the remote server.  But even if that's not the
scenario, I think we should assume that the DBA or some other system
resolved it and therefore we don't need to do anything further.  If we
assume anything else, then we just go into an infinite error loop,
which isn't useful behavior.  We could log a message, though (for
example, LOG: unable to resolve foreign transaction ... because it
does not exist).

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Tue, Aug 1, 2017 at 1:40 AM, Robert Haas  wrote:
> On Thu, Jul 27, 2017 at 8:25 AM, Ashutosh Bapat
>  wrote:
>> The remote transaction can be committed/aborted only after the fate of
>> the local transaction is decided. If we commit remote transaction and
>> abort local transaction, that's not good. AtEOXact* functions are
>> called immediately after that decision in post-commit/abort phase. So,
>> if we want to commit/abort the remote transaction immediately it has
>> to be done in post-commit/abort processing. Instead if we delegate
>> that to the remote transaction resolved backend (introduced by the
>> patches) the delay between local commit and remote commits depends
>> upon when the resolve gets a chance to run and process those
>> transactions. One could argue that that delay would anyway exist when
>> post-commit/abort processing fails to resolve remote transaction. But
>> given the real high availability these days, in most of the cases
>> remote transaction will be resolved in the post-commit/abort phase. I
>> think we should optimize for most common case. Your concern is still
>> valid, that we shouldn't raise an error or do anything critical in
>> post-commit/abort phase. So we should device a way to send
>> COMMIT/ABORT prepared messages to the remote server in asynchronous
>> fashion carefully avoiding errors. Recent changes to 2PC have improved
>> performance in that area to a great extent. Relying on resolver
>> backend to resolve remote transactions would erode that performance
>> gain.
>
> I think there are two separate but interconnected issues here.  One is
> that if we give the user a new command prompt without resolving the
> remote transaction, then they might run a new query that sees their
> own work as committed, which would be bad.  Or, they might commit,
> wait for the acknowledgement, and then tell some other session to go
> look at the data, and find it not there.  That would also be bad.  I
> think the solution is likely to do something like what we did for
> synchronous replication in commit
> 9a56dc3389b9470031e9ef8e45c95a680982e01a -- wait for the remove
> transaction to be resolved (by the background process) but allow an
> interrupt to escape the wait-loop.
>
> The second issue is that having the resolver resolve transactions
> might be slower than doing it in the foreground.  I don't necessarily
> see a reason why that should be a big problem.  I mean, the resolver
> might need to establish a separate connection, but if it keeps that
> connection open for a while (say, 5 minutes) in case further
> transactions arrive then it won't be an issue except on really
> low-volume system which isn't really a case I think we need to worry
> about very much.  Also, the hand-off to the resolver might take some
> time, but that's equally true for sync rep and we're living with it
> there.  Anything else is presumably just the resolver itself being
> inefficient which seems like something that can simply be fixed.
>
> FWIW, I don't think the present resolver implementation is likely to
> be what we want.  IIRC, it's just calling an SQL function which
> doesn't seem like a good approach.  Ideally we should stick an entry
> into a shared memory queue and then ping the resolver via SetLatch,
> and it can directly invoke an FDW method on the data from the shared
> memory queue.  It should be possible to set things up so that a user
> who wishes to do so can run multiple copies of the resolver thread at
> the same time, which would be a good way to keep latency down if the
> system is very busy with distributed transactions.
>

Based on the review comment from Robert, I'm planning to do the big
change to the architecture of this patch so that a backend process
work together with a dedicated background worker that is responsible
for resolving the foreign transactions. For the usage of this feature,
it will be almost the same as what this patch has been doing except
for adding a new GUC paramter that controls the number of resovler
process launch. That is, we can have multiple resolver process to keep
latency down.

On technical view, the processing of the transaction involving
multiple foreign server will be changed as follows.

* Backend processes
1. In PreCommit phase, prepare the transaction on foreign servers and
save fdw_xact entries into the array on shmem. Also create a
fdw_xact_state entry on shmem hash that has the index of each fdw_xact
entry.
2. Local commit/abort.
3. Change its process state to FDWXACT_WAITING and enqueue the MyProc
to the shmem queue.
4. Ping to the resolver process via SetLatch.
5. Wait to be waken up.

* Resovler processes
1. Fetch PGPROC entry from the shmem queue and get its XID (say, XID-a).
2. Get the fdw_xact_state entry from shmem hash by XID-a.
3. Iterate fdw_xact entries using the index, and resolve the foreign
transactions.
3-a. If even one foreign transaction failed to resolve, raise an 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Michael Paquier]

On Mon, Jul 31, 2017 at 7:27 PM, Alvaro Herrera
 wrote:
> Robert Haas wrote:
>
>> An alternative approach is to have some kind of other identifier,
>> let's call it a distributed transaction ID (DXID) which is mapped by
>> each node onto a local XID.
>
> Postgres-XL seems to manage this problem by using a transaction manager
> node, which is in charge of assigning snapshots.  I don't know how that
> works, but perhaps adding that concept here could be useful too.  One
> critical point to that design is that the app connects not directly to
> the underlying Postgres server but instead to some other node which is
> or connects to the node that manages the snapshots.
>
> Maybe Michael can explain in better detail how it works, and/or how (and
> if) it could be applied here.

XL (and XC) use a transaction ID that plugs in directly with the
internal XID assigned by Postgres, actually bypassing what Postgres
assigns to each backend if a transaction needs one. So if transactions
are not heavenly shared among multiple nodes, performance gets
impacted. Now when we worked on this project we noticed that we gained
in performance by reducing the number of requests and grouping them
together, so a proxy layer has been added between the global
transaction manager and Postgres to group those requests. This does
not change the fact that read-committed transactions still need
snapshots for each query, which is consuming. So this approach hurts
less with analytic queries, and more with OLTP.

2PC transaction status was tracked as well in the GTM. This allows
fancy things like being able to prepare a transaction on node 1, and
commit it on node 2 for example. I am not honestly sure that you need
to add anything at clog level for example, but I think that having at
the FDW level the meta data of a transaction stored as a rather
correct approach on the matter. That's what greenplum actually does if
I recall correctly (Heikki save me!): it has one coordinator with such
metadata handling, and bunch of underlying nodes that store the data.
Citus does also that if I recall correctly. So instead of
decentralizing this information, this gets stored in a Postgres
coordinator instance.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Stas Kelvich]

> On 31 Jul 2017, at 20:03, Robert Haas  wrote:
> 
> Regardless of whether we share XIDs or DXIDs, we need a more complex
> concept of transaction state than we have now.

Seems that discussion shifted from 2PC itself to the general issues with 
distributed
transactions. So it is probably appropriate to share here resume of things that 
we
done in area of distributed visibility. During last two years we tried three 
quite different
approaches and finally settled with Clock-SI. 

At first, to test different approaches we did small patch that wrap calls to 
visibility-related
functions (SetTransactionStatus, GetSnapshot, etc. Described in detail at 
wiki[1] ) in order to
allow overload them from extension. Such approach allows to implement almost 
anything
related to distributed visibility since you have full control about how local 
visibility is done.
That API isn’t hard prerequisite, and if one wants to create some concrete 
implementation
it can be done just in place. However, I think it is good to have such API in 
some form.

So three approaches that we tried:

1) Postgres-XL-like:

That is most straightforward way. Basically we need separate network service 
(GTM/DTM) that is
responsible for xid generation, and managing running-list of transactions. So 
acquiring
xid and snapshot is done by network calls. Because of shared xid space it is 
possible
to compare them in ordinary way and get right order. Gap between 
non-simultaneous
commits by 2pc is covered by the fact that we getting our snapshots from GTM, 
and
it will remove xid from running list only when transaction committed on both 
nodes.

Such approach is okay for OLAP-style transactions where tps isn’t high. But 
OLTP with
high transaction rate GTM will immediately became a bottleneck since even write 
transactions
need to get snapshot from GTM. Even if they access only one node.


2) Incremental SI [2]

Approach with central coordinator, that can allow local reads without network
communications by slightly altering visibility rules.

Despite the fact that it is kind of patented, we also failed to achieve proper 
visibility
by implementing algorithms from that paper. It always showed some 
inconsistencies.
May be because of bugs in our implementation, may be because of some
typos/mistakes in algorithm description itself. Reasoning in paper wasn’t very
clear for us, as well as patent issues, so we just leaved that.


3) Clock-SI [3]

It is MS research paper, that describes algorithm similar to ones used in 
Spanner and
CockroachDB, without central GTM and with reads that do not require network 
roundtrip.

There are two ideas behind it:

* Assuming snapshot isolation and visibility on node are based on CSN, use 
local time as CSN,
then when you are doing 2PC, collect prepare time from all participating nodes 
and
commit transaction everywhere with maximum of that times. If node during read 
faces tuples
committed by tx with CSN greater then their snapshot CSN (that can happen due to
time desynchronisation on node) then it just waits until that time come. So 
time desynchronisation
can affect performance, but can’t affect correctness.

* During distributed commit transaction neither running (if it commits then 
tuple
should be already visible) nor committed/aborted (it still can be aborted, so 
it is illegal to read).
So here IN-DOUBT transaction state appears, when reader should wait for writers.

We managed to implement that using mentioned XTM api. XID<->CSN mapping is
accounted by extension itself. Speed/scalability are also good.

I want to resubmit implementation of that algorithm for FDW later in August, 
along with some
isolation tests based on set of queries in [4].


[1] https://wiki.postgresql.org/wiki/DTM#eXtensible_Transaction_Manager_API
[2] http://pi3.informatik.uni-mannheim.de/~norman/dsi_jour_2014.pdf
[3] 
https://www.microsoft.com/en-us/research/wp-content/uploads/2016/02/samehe-clocksi.srds2013.pdf
[4] https://github.com/ept/hermitage


Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Tue, Aug 1, 2017 at 1:40 AM, Robert Haas  wrote:
> On Thu, Jul 27, 2017 at 8:25 AM, Ashutosh Bapat
>  wrote:
>> The remote transaction can be committed/aborted only after the fate of
>> the local transaction is decided. If we commit remote transaction and
>> abort local transaction, that's not good. AtEOXact* functions are
>> called immediately after that decision in post-commit/abort phase. So,
>> if we want to commit/abort the remote transaction immediately it has
>> to be done in post-commit/abort processing. Instead if we delegate
>> that to the remote transaction resolved backend (introduced by the
>> patches) the delay between local commit and remote commits depends
>> upon when the resolve gets a chance to run and process those
>> transactions. One could argue that that delay would anyway exist when
>> post-commit/abort processing fails to resolve remote transaction. But
>> given the real high availability these days, in most of the cases
>> remote transaction will be resolved in the post-commit/abort phase. I
>> think we should optimize for most common case. Your concern is still
>> valid, that we shouldn't raise an error or do anything critical in
>> post-commit/abort phase. So we should device a way to send
>> COMMIT/ABORT prepared messages to the remote server in asynchronous
>> fashion carefully avoiding errors. Recent changes to 2PC have improved
>> performance in that area to a great extent. Relying on resolver
>> backend to resolve remote transactions would erode that performance
>> gain.
>
> I think there are two separate but interconnected issues here.  One is
> that if we give the user a new command prompt without resolving the
> remote transaction, then they might run a new query that sees their
> own work as committed, which would be bad.  Or, they might commit,
> wait for the acknowledgement, and then tell some other session to go
> look at the data, and find it not there.  That would also be bad.  I
> think the solution is likely to do something like what we did for
> synchronous replication in commit
> 9a56dc3389b9470031e9ef8e45c95a680982e01a -- wait for the remove
> transaction to be resolved (by the background process) but allow an
> interrupt to escape the wait-loop.
>
> The second issue is that having the resolver resolve transactions
> might be slower than doing it in the foreground.  I don't necessarily
> see a reason why that should be a big problem.  I mean, the resolver
> might need to establish a separate connection, but if it keeps that
> connection open for a while (say, 5 minutes) in case further
> transactions arrive then it won't be an issue except on really
> low-volume system which isn't really a case I think we need to worry
> about very much.  Also, the hand-off to the resolver might take some
> time, but that's equally true for sync rep and we're living with it
> there.  Anything else is presumably just the resolver itself being
> inefficient which seems like something that can simply be fixed.

I think using the solution similar to sync rep to wait for the
transaction to be resolved is a good way. One concern I have is that
if we have one resolver process per one backend process the switching
connection between participant nodes would be overhead. In current
implementation the backend process uses connection caches to the
remote server. On the other hand if we have one resolver process per
one database on remote server the backend process have to communicate
with multiple resolver processes.

> FWIW, I don't think the present resolver implementation is likely to
> be what we want.  IIRC, it's just calling an SQL function which
> doesn't seem like a good approach.  Ideally we should stick an entry
> into a shared memory queue and then ping the resolver via SetLatch,
> and it can directly invoke an FDW method on the data from the shared
> memory queue.  It should be possible to set things up so that a user
> who wishes to do so can run multiple copies of the resolver thread at
> the same time, which would be a good way to keep latency down if the
> system is very busy with distributed transactions.
>

In current implementation the resolver process exists for resolving
in-doubt transactions. That process periodically checks if there is
unresolved transaction on shared memory and tries to resolve it
according commit log. If we change it so that the backend process can
communicate with the resolver process via SetLatch the resolver
process is better to be implemented into core rather than as a contrib
module.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Tue, Aug 1, 2017 at 3:43 AM, Robert Haas  wrote:
> On Mon, Jul 31, 2017 at 1:27 PM, Alvaro Herrera
>  wrote:
>> Postgres-XL seems to manage this problem by using a transaction manager
>> node, which is in charge of assigning snapshots.  I don't know how that
>> works, but perhaps adding that concept here could be useful too.  One
>> critical point to that design is that the app connects not directly to
>> the underlying Postgres server but instead to some other node which is
>> or connects to the node that manages the snapshots.
>>
>> Maybe Michael can explain in better detail how it works, and/or how (and
>> if) it could be applied here.
>
> I suspect that if you've got a central coordinator server that is the
> jumping-off point for all distributed transactions, the Postgres-XL
> approach is hard to beat (at least in concept, not sure about the
> implementation).  That server is brokering all of the connections to
> the data nodes anyway, so it might as well tell them all what
> snapshots to use while it's there.  When you scale to multiple
> coordinators, though, it's less clear that it's the best approach.
> Now one coordinator has to be the GTM master, and that server is
> likely to become a bottleneck -- plus talking to it involves extra
> network hops for all the other coordinators. When you then move the
> needle a bit further and imagine a system where the idea of a
> coordinator doesn't even exist, and you've just got a loosely couple
> distributed system where distributed transactions might arrive on any
> node, all of which are also servicing local transactions, then it
> seems pretty likely that the Postgres-XL approach is not the best fit.
>
> We might want to support multiple models.  Which one to support first
> is a harder question.  The thing I like least about the Postgres-XC
> approach is it seems inevitable that, as Michael says, the central
> server handing out XIDs and snapshots is bound to become a bottleneck.
> That type of system implicitly constructs a total order of all
> distributed transactions, but we don't really need a total order.  If
> two transactions don't touch the same data and there's no overlapping
> transaction that can notice the commit order, then we could make those
> commit decisions independently on different nodes without caring which
> one "happens first".  The problem is that it might take so much
> bookkeeping to figure out whether that is in fact the case in a
> particular instance that it's even more expensive than having a
> central server that functions as a global bottleneck.
>
> It might be worth some study not only of Postgres-XL but also of other
> databases that claim to provide distributed transactional consistency
> across nodes.  I've found literature on this topic from time to time
> over the years, but I'm not sure what the best practices in this area
> actually are.

Yeah it's worth to study other databases and to consider the approach
that goes well with the PostgreSQL architecture. I've read some papers
related to distributed transaction management but I'm also not sure
what the best practices in this area are. However, one trend I've seen
is that some cloud-native databases such as Google Spanner[1] and
Cockroachdb employs the tecniques using timestamps to determine the
visibility without centralized coordination. Google Spanner uses GPS
clocks and atomic clocks but since these are not common hardware
Cockroachdb uses local timestamps with NTP instead. Also, other
transaction techniques using local timestamp have been discussed. For
example Clock-SI[2] derives snapshots and commit timestamps from
loosely synchronized physical clocks, though it doesn't support
serializable isolation level. IIUC postgrespro multi-master cluster
employs the technique based on that. I've not read deeply yet but I
found new paper[3] on last week which introduces new SI mechanism that
allows transactions to determine their timestamps autonomously,
without relying on centralized coordination. PostgreSQL uses XID to
determine visibility now but mapping XID to its timestamp using commit
timestmap feature might be able to allow PostgreSQL to use the
timestamp for that purpose.

> https://en.wikipedia.org/wiki/Global_serializability
> claims that a technique called Commitment Ordering (CO) is teh
> awesome, but I've got my doubts about whether that's really an
> objective description of the state of the art.  One clue is that the
> global serialiazability article says three separate times that the
> technique has been widely misunderstood.  I'm not sure exactly which
> Wikipedia guideline that violates, but I think Wikipedia is supposed
> to summarize the views that exist on a topic in accordance with their
> prevalence, not take a position on which view is correct.
> https://en.wikipedia.org/wiki/Commitment_ordering contains citations
> from the papers only of one guy, Yoav Raz, which is another hint that
> this 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Mon, Jul 31, 2017 at 1:27 PM, Alvaro Herrera
 wrote:
> Postgres-XL seems to manage this problem by using a transaction manager
> node, which is in charge of assigning snapshots.  I don't know how that
> works, but perhaps adding that concept here could be useful too.  One
> critical point to that design is that the app connects not directly to
> the underlying Postgres server but instead to some other node which is
> or connects to the node that manages the snapshots.
>
> Maybe Michael can explain in better detail how it works, and/or how (and
> if) it could be applied here.

I suspect that if you've got a central coordinator server that is the
jumping-off point for all distributed transactions, the Postgres-XL
approach is hard to beat (at least in concept, not sure about the
implementation).  That server is brokering all of the connections to
the data nodes anyway, so it might as well tell them all what
snapshots to use while it's there.  When you scale to multiple
coordinators, though, it's less clear that it's the best approach.
Now one coordinator has to be the GTM master, and that server is
likely to become a bottleneck -- plus talking to it involves extra
network hops for all the other coordinators. When you then move the
needle a bit further and imagine a system where the idea of a
coordinator doesn't even exist, and you've just got a loosely couple
distributed system where distributed transactions might arrive on any
node, all of which are also servicing local transactions, then it
seems pretty likely that the Postgres-XL approach is not the best fit.

We might want to support multiple models.  Which one to support first
is a harder question.  The thing I like least about the Postgres-XC
approach is it seems inevitable that, as Michael says, the central
server handing out XIDs and snapshots is bound to become a bottleneck.
That type of system implicitly constructs a total order of all
distributed transactions, but we don't really need a total order.  If
two transactions don't touch the same data and there's no overlapping
transaction that can notice the commit order, then we could make those
commit decisions independently on different nodes without caring which
one "happens first".  The problem is that it might take so much
bookkeeping to figure out whether that is in fact the case in a
particular instance that it's even more expensive than having a
central server that functions as a global bottleneck.

It might be worth some study not only of Postgres-XL but also of other
databases that claim to provide distributed transactional consistency
across nodes.  I've found literature on this topic from time to time
over the years, but I'm not sure what the best practices in this area
actually are. https://en.wikipedia.org/wiki/Global_serializability
claims that a technique called Commitment Ordering (CO) is teh
awesome, but I've got my doubts about whether that's really an
objective description of the state of the art.  One clue is that the
global serialiazability article says three separate times that the
technique has been widely misunderstood.  I'm not sure exactly which
Wikipedia guideline that violates, but I think Wikipedia is supposed
to summarize the views that exist on a topic in accordance with their
prevalence, not take a position on which view is correct.
https://en.wikipedia.org/wiki/Commitment_ordering contains citations
from the papers only of one guy, Yoav Raz, which is another hint that
this may not be as widely-regarded a technique as the person who wrote
these articles thinks it should be.  Anyway, it would be good to
understand what other well-regarded systems do before we choose what
we want to do.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Alvaro Herrera]

Robert Haas wrote:

> An alternative approach is to have some kind of other identifier,
> let's call it a distributed transaction ID (DXID) which is mapped by
> each node onto a local XID.

Postgres-XL seems to manage this problem by using a transaction manager
node, which is in charge of assigning snapshots.  I don't know how that
works, but perhaps adding that concept here could be useful too.  One
critical point to that design is that the app connects not directly to
the underlying Postgres server but instead to some other node which is
or connects to the node that manages the snapshots.

Maybe Michael can explain in better detail how it works, and/or how (and
if) it could be applied here.

-- 
Álvaro Herrerahttps://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Fri, Jul 28, 2017 at 10:14 AM, Michael Paquier
 wrote:
> On Fri, Jul 28, 2017 at 7:28 AM, Masahiko Sawada  
> wrote:
>> That also requires to share the same XID space with all remote nodes.
>
> You are putting your finger on the main bottleneck with global
> consistency that XC and XL has because of that. And the source feeding
> the XIDs is a SPOF.
>
>> Perhaps the CSN based snapshot can make this more simple.
>
> Hm. This needs a closer look.

With or without CSNs, sharing the same XID space across all nodes is
undesirable in a loosely-coupled network.  If only a small fraction of
transactions are distributed, incurring the overhead of synchronizing
XID assignment for every transaction is not good.  Suppose node A
processes many transactions and node B only a few transactions; then,
XID advancement caused by node A forces node B to perform vacuum for
wraparound.  Not fun.  Or, if you have an OLTP workload running on A
and an OLTP workload running B that are independent of each other, and
occasional reporting queries that scan both, you'll be incurring the
overhead of keeping A and B consistent for a lot of transactions that
don't need it.  Of course, when A and B are tightly coupled and
basically all transactions are scanning both, locking the XID space
together *may* be the best approach, but even then there are notable
disadvantages - e.g. they can't both continue processing write
transactions if the connection between the two is severed.

An alternative approach is to have some kind of other identifier,
let's call it a distributed transaction ID (DXID) which is mapped by
each node onto a local XID.

Regardless of whether we share XIDs or DXIDs, we need a more complex
concept of transaction state than we have now.  Right now,
transactions are basically in-progress, committed, or aborted, but
there's also the state where the status of the transaction is known by
someone but not locally.  You can imagine something like: during the
prepare phase, all nodes set the status in clog to "prepared".  Then,
if that succeeds, the leader changes the status to "committed" or
"aborted" and tells all nodes to do the same.  Thereafter, any time
someone inquires about the status of that transaction, we go ask all
of the other nodes in the cluster; if they all think it's prepared,
then it's prepared -- but if any of them think it's committed or
aborted, then we change our local status to match and return that
status.  So once one node changes the status to committed or aborted
it can propagate through the cluster even if connectivity is lost for
a while.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Thu, Jul 27, 2017 at 8:25 AM, Ashutosh Bapat
 wrote:
> The remote transaction can be committed/aborted only after the fate of
> the local transaction is decided. If we commit remote transaction and
> abort local transaction, that's not good. AtEOXact* functions are
> called immediately after that decision in post-commit/abort phase. So,
> if we want to commit/abort the remote transaction immediately it has
> to be done in post-commit/abort processing. Instead if we delegate
> that to the remote transaction resolved backend (introduced by the
> patches) the delay between local commit and remote commits depends
> upon when the resolve gets a chance to run and process those
> transactions. One could argue that that delay would anyway exist when
> post-commit/abort processing fails to resolve remote transaction. But
> given the real high availability these days, in most of the cases
> remote transaction will be resolved in the post-commit/abort phase. I
> think we should optimize for most common case. Your concern is still
> valid, that we shouldn't raise an error or do anything critical in
> post-commit/abort phase. So we should device a way to send
> COMMIT/ABORT prepared messages to the remote server in asynchronous
> fashion carefully avoiding errors. Recent changes to 2PC have improved
> performance in that area to a great extent. Relying on resolver
> backend to resolve remote transactions would erode that performance
> gain.

I think there are two separate but interconnected issues here.  One is
that if we give the user a new command prompt without resolving the
remote transaction, then they might run a new query that sees their
own work as committed, which would be bad.  Or, they might commit,
wait for the acknowledgement, and then tell some other session to go
look at the data, and find it not there.  That would also be bad.  I
think the solution is likely to do something like what we did for
synchronous replication in commit
9a56dc3389b9470031e9ef8e45c95a680982e01a -- wait for the remove
transaction to be resolved (by the background process) but allow an
interrupt to escape the wait-loop.

The second issue is that having the resolver resolve transactions
might be slower than doing it in the foreground.  I don't necessarily
see a reason why that should be a big problem.  I mean, the resolver
might need to establish a separate connection, but if it keeps that
connection open for a while (say, 5 minutes) in case further
transactions arrive then it won't be an issue except on really
low-volume system which isn't really a case I think we need to worry
about very much.  Also, the hand-off to the resolver might take some
time, but that's equally true for sync rep and we're living with it
there.  Anything else is presumably just the resolver itself being
inefficient which seems like something that can simply be fixed.

FWIW, I don't think the present resolver implementation is likely to
be what we want.  IIRC, it's just calling an SQL function which
doesn't seem like a good approach.  Ideally we should stick an entry
into a shared memory queue and then ping the resolver via SetLatch,
and it can directly invoke an FDW method on the data from the shared
memory queue.  It should be possible to set things up so that a user
who wishes to do so can run multiple copies of the resolver thread at
the same time, which would be a good way to keep latency down if the
system is very busy with distributed transactions.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Michael Paquier]

On Fri, Jul 28, 2017 at 7:28 AM, Masahiko Sawada  wrote:
> That also requires to share the same XID space with all remote nodes.

You are putting your finger on the main bottleneck with global
consistency that XC and XL has because of that. And the source feeding
the XIDs is a SPOF.

> Perhaps the CSN based snapshot can make this more simple.

Hm. This needs a closer look.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Jul 27, 2017 at 8:02 PM, Robert Haas  wrote:
> On Thu, Jul 27, 2017 at 5:08 AM, Stas Kelvich  
> wrote:
>> As far as I understand any solution that provides proper isolation for 
>> distributed
>> transactions in postgres will require distributed 2PC somewhere under the 
>> hood.
>> That is just consequence of parallelism that database allows — transactions 
>> can
>> abort due concurrent operations. So dichotomy is simple: either we need 2PC 
>> or
>> restrict write transactions to be physically serial.
>>
>> In particular both Postgres-XL/XC and postgrespro multimaster are using 2PC 
>> to
>> commit distributed transaction.
>
> Ah, OK.  I was imagining that a transaction manager might be
> responsible for managing both snapshots and distributed commit.  But
> if the transaction manager only handles the snapshots (how?) and the
> commit has to be done using 2PC, then we need this.

One way to provide snapshots to participant nodes is giving a snapshot
data to them using libpq protocol with the query when coordinator
nodes starts transaction on a remote node (or we now can use exporting
snapshot infrastructure?). IIUC Postgres-XL/XC uses this approach.
That also requires to share the same XID space with all remote nodes.
Perhaps the CSN based snapshot can make this more simple.

>> Also I see the quite a big value in this patch even without 
>> tm/snapshots/whatever.
>> Right now fdw doesn’t guarantee neither isolation nor atomicity. And if one 
>> isn’t
>> doing cross-node analytical transactions it will be safe to live without 
>> isolation.
>> But living without atomicity means that some parts of data can be lost 
>> without simple
>> way to detect and fix that.
>
> OK, thanks for weighing in.
>

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Thu, Jul 27, 2017 at 6:58 AM, Robert Haas  wrote:
>
> On a technical level, I am pretty sure that it is not OK to call
> AtEOXact_FDWXacts() from the sections of CommitTransaction,
> AbortTransaction, and PrepareTransaction that are described as
> "non-critical resource releasing".  At that point, it's too late to
> throw an error, and it is very difficult to imagine something that
> involves a TCP connection to another machine not being subject to
> error.  You might say "well, we can just make sure that any problems
> are reporting as a WARNING rather than an ERROR", but that's pretty
> hard to guarantee; most backend code assumes it can ERROR, so anything
> you call is a potential hazard.  There is a second problem, too: any
> code that runs from here is not interruptible.  The user can hit ^C
> all day and nothing will happen.  That's a bad situation when you're
> busy doing network I/O.  I'm not exactly sure what the best thing to
> do about this problem would be.
>

The remote transaction can be committed/aborted only after the fate of
the local transaction is decided. If we commit remote transaction and
abort local transaction, that's not good. AtEOXact* functions are
called immediately after that decision in post-commit/abort phase. So,
if we want to commit/abort the remote transaction immediately it has
to be done in post-commit/abort processing. Instead if we delegate
that to the remote transaction resolved backend (introduced by the
patches) the delay between local commit and remote commits depends
upon when the resolve gets a chance to run and process those
transactions. One could argue that that delay would anyway exist when
post-commit/abort processing fails to resolve remote transaction. But
given the real high availability these days, in most of the cases
remote transaction will be resolved in the post-commit/abort phase. I
think we should optimize for most common case. Your concern is still
valid, that we shouldn't raise an error or do anything critical in
post-commit/abort phase. So we should device a way to send
COMMIT/ABORT prepared messages to the remote server in asynchronous
fashion carefully avoiding errors. Recent changes to 2PC have improved
performance in that area to a great extent. Relying on resolver
backend to resolve remote transactions would erode that performance
gain.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Thu, Jul 27, 2017 at 5:08 AM, Stas Kelvich  wrote:
> As far as I understand any solution that provides proper isolation for 
> distributed
> transactions in postgres will require distributed 2PC somewhere under the 
> hood.
> That is just consequence of parallelism that database allows — transactions 
> can
> abort due concurrent operations. So dichotomy is simple: either we need 2PC or
> restrict write transactions to be physically serial.
>
> In particular both Postgres-XL/XC and postgrespro multimaster are using 2PC to
> commit distributed transaction.

Ah, OK.  I was imagining that a transaction manager might be
responsible for managing both snapshots and distributed commit.  But
if the transaction manager only handles the snapshots (how?) and the
commit has to be done using 2PC, then we need this.

> Also I see the quite a big value in this patch even without 
> tm/snapshots/whatever.
> Right now fdw doesn’t guarantee neither isolation nor atomicity. And if one 
> isn’t
> doing cross-node analytical transactions it will be safe to live without 
> isolation.
> But living without atomicity means that some parts of data can be lost 
> without simple
> way to detect and fix that.

OK, thanks for weighing in.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Stas Kelvich]

> On 27 Jul 2017, at 04:28, Robert Haas  wrote:
> 
>  However, you would not
> be guaranteed that all of those commits or rollbacks happen at
> anything like the same time.  So, you would have a sort of eventual
> consistency.

As far as I understand any solution that provides proper isolation for 
distributed
transactions in postgres will require distributed 2PC somewhere under the hood.
That is just consequence of parallelism that database allows — transactions can
abort due concurrent operations. So dichotomy is simple: either we need 2PC or
restrict write transactions to be physically serial.

In particular both Postgres-XL/XC and postgrespro multimaster are using 2PC to
commit distributed transaction.

Some years ago we created patches to implement transaction manager API and
that is just a way to inject consistent snapshots on different nodes, but atomic
commit itself is out of scope of TM API (hmm, may be it is better to call it 
snapshot
manager api?). That allows us to use it in quite different environments like 
fdw and
logical replication and both are using 2PC.

I want to submit TM API again during this release cycle along with 
implementation
for fdw. And I planned to base it on top of this patch. So I already rebased 
Masahiko’s
patch to current -master and started writing long list of nitpicks, but not 
finished yet.

Also I see the quite a big value in this patch even without 
tm/snapshots/whatever.
Right now fdw doesn’t guarantee neither isolation nor atomicity. And if one 
isn’t
doing cross-node analytical transactions it will be safe to live without 
isolation.
But living without atomicity means that some parts of data can be lost without 
simple
way to detect and fix that.


Stas Kelvich
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Jul 27, 2017 at 10:28 AM, Robert Haas  wrote:
> On Fri, Apr 7, 2017 at 10:56 AM, Masahiko Sawada  
> wrote:
>> Vinayak, why did you marked this patch as "Move to next CF"? AFAIU
>> there is not discussion yet.
>
> I'd like to discuss this patch.  Clearly, a lot of work has been done
> here, but I am not sure about the approach.

Thank you for the comment. I'd like to reply about the goal of this
feature first.

> If we were to commit this patch set, then you could optionally enable
> two_phase_commit for a postgres_fdw foreign server.  If you did, then,
> modulo bugs and administrator shenanigans, and given proper
> configuration, you would be guaranteed that a successful commit of a
> transaction which touched postgres_fdw foreign tables would eventually
> end up committed or rolled back on all of the nodes, rather than
> committed on some and rolled back on others.  However, you would not
> be guaranteed that all of those commits or rollbacks happen at
> anything like the same time.  So, you would have a sort of eventual
> consistency.  Any given snapshot might not be consistent, but if you
> waited long enough and with all nodes online, eventually all
> distributed transactions would be resolved in a consistent manner.
> That's kinda cool, but I think what people really want is a stronger
> guarantee, namely, that they will get consistent snapshots.  It's not
> clear to me that this patch gets us any closer to that goal.  Does
> anyone have a plan for how we'd get from here to that stronger goal?
> If not, is the patch useful enough to justify committing it for what
> it can already do?  It would be particularly good to hear some
> end-user views on this functionality and whether or not they would use
> it and find it valuable.

Yeah, this patch only guarantees that if you got a commit the
transaction either committed or rollback-ed on all relevant nodes.
And subsequent transactions can see a consistent result (if the server
failed we have to recover in-doubt transactions properly from a
crash). But it doesn't guarantees that a concurrent transaction can
see a consistent result. To provide seeing cluster-wide consistent
result, I think we need a transaction manager for distributed queries
which is responsible for providing consistent snapshots. There were
some discussions of the type of transaction manager but at least we
need a new transaction manager for distributed queries. I think the
providing a consistent result to concurrent transactions and the
committing or rollback-ing atomically a transaction should be
separated features, and should be discussed separately. It's not
useful and users would complain if we provide a consistent snapshot
but a distributed transaction could commit on part of nodes. So this
patch could be also an important feature for providing consistent
result.

> On a technical level, I am pretty sure that it is not OK to call
> AtEOXact_FDWXacts() from the sections of CommitTransaction,
> AbortTransaction, and PrepareTransaction that are described as
> "non-critical resource releasing".  At that point, it's too late to
> throw an error, and it is very difficult to imagine something that
> involves a TCP connection to another machine not being subject to
> error.  You might say "well, we can just make sure that any problems
> are reporting as a WARNING rather than an ERROR", but that's pretty
> hard to guarantee; most backend code assumes it can ERROR, so anything
> you call is a potential hazard.  There is a second problem, too: any
> code that runs from here is not interruptible.  The user can hit ^C
> all day and nothing will happen.  That's a bad situation when you're
> busy doing network I/O.  I'm not exactly sure what the best thing to
> do about this problem would be.
>

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Fri, Apr 7, 2017 at 10:56 AM, Masahiko Sawada  wrote:
> Vinayak, why did you marked this patch as "Move to next CF"? AFAIU
> there is not discussion yet.

I'd like to discuss this patch.  Clearly, a lot of work has been done
here, but I am not sure about the approach.

If we were to commit this patch set, then you could optionally enable
two_phase_commit for a postgres_fdw foreign server.  If you did, then,
modulo bugs and administrator shenanigans, and given proper
configuration, you would be guaranteed that a successful commit of a
transaction which touched postgres_fdw foreign tables would eventually
end up committed or rolled back on all of the nodes, rather than
committed on some and rolled back on others.  However, you would not
be guaranteed that all of those commits or rollbacks happen at
anything like the same time.  So, you would have a sort of eventual
consistency.  Any given snapshot might not be consistent, but if you
waited long enough and with all nodes online, eventually all
distributed transactions would be resolved in a consistent manner.
That's kinda cool, but I think what people really want is a stronger
guarantee, namely, that they will get consistent snapshots.  It's not
clear to me that this patch gets us any closer to that goal.  Does
anyone have a plan for how we'd get from here to that stronger goal?
If not, is the patch useful enough to justify committing it for what
it can already do?  It would be particularly good to hear some
end-user views on this functionality and whether or not they would use
it and find it valuable.

On a technical level, I am pretty sure that it is not OK to call
AtEOXact_FDWXacts() from the sections of CommitTransaction,
AbortTransaction, and PrepareTransaction that are described as
"non-critical resource releasing".  At that point, it's too late to
throw an error, and it is very difficult to imagine something that
involves a TCP connection to another machine not being subject to
error.  You might say "well, we can just make sure that any problems
are reporting as a WARNING rather than an ERROR", but that's pretty
hard to guarantee; most backend code assumes it can ERROR, so anything
you call is a potential hazard.  There is a second problem, too: any
code that runs from here is not interruptible.  The user can hit ^C
all day and nothing will happen.  That's a bad situation when you're
busy doing network I/O.  I'm not exactly sure what the best thing to
do about this problem would be.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Wed, Mar 29, 2017 at 11:14 PM, Masahiko Sawada  wrote:
> On Wed, Mar 22, 2017 at 2:49 AM, Masahiko Sawada  
> wrote:
>> On Thu, Mar 16, 2017 at 2:37 PM, Vinayak Pokale
>>  wrote:
>>> The following review has been posted through the commitfest application:
>>> make installcheck-world:  tested, passed
>>> Implements feature:   tested, passed
>>> Spec compliant:   tested, passed
>>> Documentation:tested, passed
>>>
>>> I have tested the latest patch and it looks good to me,
>>> so I marked it "Ready for committer".
>>> Anyway, it would be great if anyone could also have a look at the patches 
>>> and send comments.
>>>
>>> The new status of this patch is: Ready for Committer
>>>
>>
>> Thank you for updating but I found a bug in 001 patch. Attached latest 
>> patches.
>> The differences are
>>   * Fixed a bug.
>>   * Ran pgindent.
>>   * Separated the patch supporting GetPrepareID API.
>>
>
> Since previous patches conflict with current HEAD, I attached latest
> set of patches.
>

Vinayak, why did you marked this patch as "Move to next CF"? AFAIU
there is not discussion yet.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Vinayak Pokale]

The following review has been posted through the commitfest application:
make installcheck-world:  tested, passed
Implements feature:   tested, passed
Spec compliant:   tested, passed
Documentation:tested, passed

I have tested the latest patch and it looks good to me,
so I marked it "Ready for committer".
Anyway, it would be great if anyone could also have a look at the patches and 
send comments.

The new status of this patch is: Ready for Committer

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Mar 2, 2017 at 11:56 AM, vinayak
 wrote:
>
> On 2017/02/28 16:54, Masahiko Sawada wrote:
>
> I've created a wiki page[1] describing about the design and
> functionality of this feature. Also it has some examples of use case,
> so this page would be helpful for even testing. Please refer it if
> you're interested in testing this feature.
>
> [1] 2PC on FDW
> 
>
> Thank you for creating the wiki page.

Thank you for looking at this patch.

> In the "src/test/regress/pg_regress.c" file
> -* xacts.  (Note: to reduce the probability of unexpected
> shmmax
> -* failures, don't set max_prepared_transactions any higher
> than
> -* actually needed by the prepared_xacts regression test.)
> +* xacts. We also set max_fdw_transctions to enable testing
> of atomic
> +* foreign transactions. (Note: to reduce the probability of
> unexpected
> +* shmmax failures, don't set max_prepared_transactions or
> +* max_prepared_foreign_transactions any higher than
> actually needed by the
> +* corresponding regression tests.).
>
> I think we are not setting the "max_fdw_transctions" anywhere.
> Is this correct?

This comment is out of date. Will fix.

>
> In the "src/bin/pg_waldump/rmgrdesc.c" file following header file used two
> times.
> + #include "access/fdw_xact.h"
> I think we need to remove one line.
>

Not necessary. Will get rid of it.

Since these are not feature bugs I will incorporate these when making
update version patches.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[vinayak]

On 2017/02/28 16:54, Masahiko Sawada wrote:


I've created a wiki page[1] describing about the design and
functionality of this feature. Also it has some examples of use case,
so this page would be helpful for even testing. Please refer it if
you're interested in testing this feature.

[1] 2PC on FDW


Thank you for creating the wiki page.

In the "src/test/regress/pg_regress.c" file
-* xacts.  (Note: to reduce the probability of 
unexpected shmmax
-* failures, don't set max_prepared_transactions any 
higher than

-* actually needed by the prepared_xacts regression test.)
+* xacts. We also set *max_fdw_transctions* to enable 
testing of atomic
+* foreign transactions. (Note: to reduce the 
probability of unexpected

+* shmmax failures, don't set max_prepared_transactions or
+* max_prepared_foreign_transactions any higher than 
actually needed by the

+* corresponding regression tests.).

I think we are not setting the "*max_fdw_transctions" *anywhere.
Is this correct?

In the "src/bin/pg_waldump/rmgrdesc.c" file following header file used 
two times.

+ #include "access/fdw_xact.h"
I think we need to remove one line.

Regards,
Vinayak Pokale

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Wed, Feb 15, 2017 at 3:11 PM, Masahiko Sawada  wrote:
> On Mon, Feb 6, 2017 at 10:48 PM, Masahiko Sawada  
> wrote:
>> On Wed, Feb 1, 2017 at 8:25 PM, Robert Haas  wrote:
>>> On Mon, Jan 30, 2017 at 2:30 AM, Masahiko Sawada  
>>> wrote:
 "txn" can be used for abbreviation of "Transaction", so for example
 pg_fdw_txn_resolver?
 I'm also fine to change the module and function name.
>>>
>>> If we're judging the relative clarity of various ways of abbreviating
>>> the word "transaction", "txn" surely beats "x".
>>>
>>> To repeat my usual refrain, is there any merit to abbreviating at all?
>>>  Could we call it, say, "fdw_transaction_resolver" or
>>> "fdw_transaction_manager"?
>>>
>>
>> Almost modules in contrib are name with "pg_" prefix but I prefer
>> "fdw_transcation_resolver" if we don't need  "pg_" prefix.
>>
>
> Since previous patches conflict to current HEAD, attached latest
> version patches.
> Please review them.
>

I've created a wiki page[1] describing about the design and
functionality of this feature. Also it has some examples of use case,
so this page would be helpful for even testing. Please refer it if
you're interested in testing this feature.

[1] 2PC on FDW


Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Wed, Feb 1, 2017 at 8:25 PM, Robert Haas  wrote:
> On Mon, Jan 30, 2017 at 2:30 AM, Masahiko Sawada  
> wrote:
>> "txn" can be used for abbreviation of "Transaction", so for example
>> pg_fdw_txn_resolver?
>> I'm also fine to change the module and function name.
>
> If we're judging the relative clarity of various ways of abbreviating
> the word "transaction", "txn" surely beats "x".
>
> To repeat my usual refrain, is there any merit to abbreviating at all?
>  Could we call it, say, "fdw_transaction_resolver" or
> "fdw_transaction_manager"?
>

Almost modules in contrib are name with "pg_" prefix but I prefer
"fdw_transcation_resolver" if we don't need  "pg_" prefix.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Mon, Jan 30, 2017 at 2:30 AM, Masahiko Sawada  wrote:
> "txn" can be used for abbreviation of "Transaction", so for example
> pg_fdw_txn_resolver?
> I'm also fine to change the module and function name.

If we're judging the relative clarity of various ways of abbreviating
the word "transaction", "txn" surely beats "x".

To repeat my usual refrain, is there any merit to abbreviating at all?
 Could we call it, say, "fdw_transaction_resolver" or
"fdw_transaction_manager"?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Michael Paquier]

On Thu, Jan 26, 2017 at 6:49 PM, Masahiko Sawada  wrote:
> Sorry, I attached wrong version patch of pg_fdw_xact_resovler. Please
> use attached patch.

This patch has been moved to CF 2017-03.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Mon, Jan 30, 2017 at 12:50 PM, Ashutosh Bapat
 wrote:
> On Sat, Jan 28, 2017 at 8:41 PM, Peter Eisentraut
>  wrote:
>> On 1/26/17 4:49 AM, Masahiko Sawada wrote:
>>> Sorry, I attached wrong version patch of pg_fdw_xact_resovler. Please
>>> use attached patch.
>>
>> So in some other thread we are talking about renaming "xlog", because
>> nobody knows what the "x" means.  In the spirit of that, let's find
>> better names for new functions as well.
>
> It's common in English (not just the database jargon) to abbreviate
> "trans" by "x" [1]. xlog went a bit far by abbreviating whole
> "transaction" by "x". But here "xact" means "transact", which is fine.
> May be we should use 'X' instead of 'x', I don't know. Said that, I am
> fine with any other name which conveys what the function does.
>
> [1] https://en.wikipedia.org/wiki/X
>

"txn" can be used for abbreviation of "Transaction", so for example
pg_fdw_txn_resolver?
I'm also fine to change the module and function name.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Sat, Jan 28, 2017 at 8:41 PM, Peter Eisentraut
 wrote:
> On 1/26/17 4:49 AM, Masahiko Sawada wrote:
>> Sorry, I attached wrong version patch of pg_fdw_xact_resovler. Please
>> use attached patch.
>
> So in some other thread we are talking about renaming "xlog", because
> nobody knows what the "x" means.  In the spirit of that, let's find
> better names for new functions as well.

It's common in English (not just the database jargon) to abbreviate
"trans" by "x" [1]. xlog went a bit far by abbreviating whole
"transaction" by "x". But here "xact" means "transact", which is fine.
May be we should use 'X' instead of 'x', I don't know. Said that, I am
fine with any other name which conveys what the function does.

[1] https://en.wikipedia.org/wiki/X

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[vinayak]

On 2017/01/29 0:11, Peter Eisentraut wrote:

On 1/26/17 4:49 AM, Masahiko Sawada wrote:

Sorry, I attached wrong version patch of pg_fdw_xact_resovler. Please
use attached patch.

So in some other thread we are talking about renaming "xlog", because
nobody knows what the "x" means.  In the spirit of that, let's find
better names for new functions as well.

+1

Regards,
Vinayak Pokale
NTT Open Source Software Center



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Peter Eisentraut]

On 1/26/17 4:49 AM, Masahiko Sawada wrote:
> Sorry, I attached wrong version patch of pg_fdw_xact_resovler. Please
> use attached patch.

So in some other thread we are talking about renaming "xlog", because
nobody knows what the "x" means.  In the spirit of that, let's find
better names for new functions as well.

-- 
Peter Eisentraut  http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Jan 26, 2017 at 6:04 PM, vinayak
 wrote:
> Hi Sawada-san,
>
> On 2017/01/26 16:51, Masahiko Sawada wrote:
>
> Thank you for reviewing!
>
> I think this is a bug of pg_fdw_resolver contrib module. I had
> forgotten to change the SQL executed by pg_fdw_resolver process.
> Attached latest version 002 patch.
>
> As previous version patch conflicts to current HEAD, attached updated
> version patches. Also I fixed some bugs in pg_fdw_xact_resolver and
> added some documentations.
> Please review it.
>
> Thank you updating the patches.
>
> I have applied patches on Postgres HEAD.
> I have created the postgres=fdw extension in PostgreSQL and then I got
> segmentation fault.
> Details:
> =# 2017-01-26 17:52:56.156 JST [3411] LOG:  worker process: foreign
> transaction resolver launcher (PID 3418) was terminated by signal 11:
> Segmentation fault
> 2017-01-26 17:52:56.156 JST [3411] LOG:  terminating any other active server
> processes
> 2017-01-26 17:52:56.156 JST [3425] WARNING:  terminating connection because
> of crash of another server process
> 2017-01-26 17:52:56.156 JST [3425] DETAIL:  The postmaster has commanded
> this server process to roll back the current transaction and exit, because
> another server process exited abnormally and possibly corrupted shared
> memory.
> 2017-01-26 17:52:56.156 JST [3425] HINT:  In a moment you should be able to
> reconnect to the database and repeat your command.
>
> Is this a bug?
>

Thank you for testing!

Sorry, I attached wrong version patch of pg_fdw_xact_resovler. Please
use attached patch.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center
diff --git a/contrib/pg_fdw_xact_resolver/Makefile b/contrib/pg_fdw_xact_resolver/Makefile
new file mode 100644
index 000..f8924f0
--- /dev/null
+++ b/contrib/pg_fdw_xact_resolver/Makefile
@@ -0,0 +1,15 @@
+# contrib/pg_fdw_xact_resolver/Makefile
+
+MODULES = pg_fdw_xact_resolver
+PGFILEDESC = "pg_fdw_xact_resolver - foreign transaction resolver demon"
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = contrib/pg_fdw_xact_resolver
+top_builddir = ../../
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/contrib/pg_fdw_xact_resolver/pg_fdw_xact_resolver.c b/contrib/pg_fdw_xact_resolver/pg_fdw_xact_resolver.c
new file mode 100644
index 000..c57de0a
--- /dev/null
+++ b/contrib/pg_fdw_xact_resolver/pg_fdw_xact_resolver.c
@@ -0,0 +1,453 @@
+/* -
+ *
+ * pg_fdw_xact_resolver.c
+ *
+ * Contrib module to launch foreign transaction resolver to resolve unresolved
+ * transactions prepared on foreign servers.
+ *
+ * The extension launches foreign transaction resolver launcher process as a
+ * background worker. The launcher then launches separate background worker
+ * process to resolve the foreign transaction in each database. The worker
+ * process simply connects to the database specified and calls pg_fdw_xact_resolve()
+ * function, which tries to resolve the transactions. The launcher process
+ * launches at most one worker at a time.
+ *
+ * Copyright (C) 2017, PostgreSQL Global Development Group
+ *
+ * IDENTIFICATION
+ *		contrib/pg_fdw_xact_resolver/pg_fdw_xact_resolver.c
+ *
+ * -
+ */
+#include "postgres.h"
+
+/* These are always necessary for a bgworker */
+#include "miscadmin.h"
+#include "postmaster/bgworker.h"
+#include "storage/ipc.h"
+#include "storage/latch.h"
+#include "storage/lwlock.h"
+#include "storage/proc.h"
+#include "storage/shmem.h"
+
+/* these headers are used by this particular worker's code */
+#include "access/heapam.h"
+#include "access/htup_details.h"
+#include "access/xact.h"
+#include "access/fdw_xact.h"
+#include "catalog/pg_database.h"
+#include "executor/spi.h"
+#include "fmgr.h"
+#include "lib/stringinfo.h"
+#include "pgstat.h"
+#include "utils/builtins.h"
+#include "utils/snapmgr.h"
+#include "utils/timestamp.h"
+#include "tcop/utility.h"
+
+PG_MODULE_MAGIC;
+
+void		_PG_init(void);
+
+/*
+ * Flags set by interrupt handlers of foreign transaction resolver for later
+ * service in the main loop.
+ */
+static volatile sig_atomic_t got_sighup = false;
+static volatile sig_atomic_t got_sigterm = false;
+static volatile sig_atomic_t got_sigquit = false;
+static volatile sig_atomic_t got_sigusr1 = false;
+
+static void FDWXactResolver_worker_main(Datum dbid_datum);
+static void FDWXactResolverMain(Datum main_arg);
+static List *get_database_list(void);
+
+/* GUC variable */
+static int fx_resolver_naptime;
+
+/*
+ * Signal handler for SIGTERM
+ *		Set a flag to let the main loop to terminate, and set our latch to wake
+ *		it up.
+ */
+static void
+FDWXactResolver_SIGTERM(SIGNAL_ARGS)
+{
+	int			save_errno = errno;
+
+	got_sigterm = true;
+	

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[vinayak]

Hi Sawada-san,

On 2017/01/26 16:51, Masahiko Sawada wrote:

Thank you for reviewing!

I think this is a bug of pg_fdw_resolver contrib module. I had
forgotten to change the SQL executed by pg_fdw_resolver process.
Attached latest version 002 patch.


As previous version patch conflicts to current HEAD, attached updated
version patches. Also I fixed some bugs in pg_fdw_xact_resolver and
added some documentations.
Please review it.

Thank you updating the patches.

I have applied patches on Postgres HEAD.
I have created the postgres=fdw extension in PostgreSQL and then I got 
segmentation fault.*

**Details:*
=# 2017-01-26 17:52:56.156 JST [3411] LOG:  worker process: foreign 
transaction resolver launcher (PID 3418) was terminated by signal 11: 
*Segmentation fault*
2017-01-26 17:52:56.156 JST [3411] LOG:  terminating any other active 
server processes
2017-01-26 17:52:56.156 JST [3425] WARNING:  terminating connection 
because of crash of another server process
2017-01-26 17:52:56.156 JST [3425] DETAIL:  The postmaster has commanded 
this server process to roll back the current transaction and exit, 
because another server process exited abnormally and possibly corrupted 
shared memory.
2017-01-26 17:52:56.156 JST [3425] HINT:  In a moment you should be able 
to reconnect to the database and repeat your command.


Is this a bug?

Regards,
Vinayak Pokale
NTT Open Source Software Center

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Jan 19, 2017 at 4:04 PM, vinayak
 wrote:
>
> On 2017/01/16 17:35, Masahiko Sawada wrote:
>>
>> On Fri, Jan 13, 2017 at 3:48 PM, Masahiko Sawada 
>> wrote:
>>>
>>> On Fri, Jan 13, 2017 at 3:20 PM, Ashutosh Bapat
>>>  wrote:
>
> Long time passed since original patch proposed by Ashutosh, so I
> explain again about current design and functionality of this feature.
> If you have any question, please feel free to ask.

 Thanks for the summary.

> Parameters
> ==

 [ snip ]

> Cluster-wide atomic commit
> ===
> Since the distributed transaction commit on foreign servers are
> executed independently, the transaction that modified data on the
> multiple foreign servers is not ensured that transaction did either
> all of them commit or all of them rollback. The patch adds the
> functionality that guarantees distributed transaction did either
> commit or rollback on all foreign servers. IOW the goal of this patch
> is achieving the cluster-wide atomic commit across foreign server that
> is capable two phase commit protocol.

 In [1], I proposed that we solve the problem of supporting PREPARED
 transactions involving foreign servers and in subsequent mail Vinayak
 agreed to that. But this goal has wider scope than that proposal. I am
 fine widening the scope, but then it would again lead to the same
 discussion we had about the big picture. May be you want to share
 design (or point out the parts of this design that will help) for
 solving smaller problem and tone down the patch for the same.

>>> Sorry for confuse you. I'm still focusing on solving only that
>>> problem. What I was trying to say is that I think that supporting
>>> PREPARED transaction involving foreign server is the means, not the
>>> end. So once we supports PREPARED transaction involving foreign
>>> servers we can achieve cluster-wide atomic commit in a sense.
>>>
>> Attached updated patches. I fixed some bugs and add 003 patch that
>> adds TAP test for foreign transaction.
>> 003 patch depends 000 and 001 patch.
>>
>> Please give me feedback.
>
>
> I have tested prepared transactions with foreign servers but after preparing
> the transaction
> the following error occur infinitely.
> Test:
> =
> =#BEGIN;
> =#INSERT INTO ft1_lt VALUES (10);
> =#INSERT INTO ft2_lt VALUES (20);
> =#PREPARE TRANSACTION 'prep_xact_with_fdw';
>
> 2017-01-18 15:09:48.378 JST [4312] ERROR:  function pg_fdw_resolve() does
> not exist at character 8
> 2017-01-18 15:09:48.378 JST [4312] HINT:  No function matches the given name
> and argument types. You might need to add explicit type casts.
> 2017-01-18 15:09:48.378 JST [4312] QUERY:  SELECT pg_fdw_resolve()
> 2017-01-18 15:09:48.378 JST [29224] LOG:  worker process: foreign
> transaction resolver (dbid 13119) (PID 4312) exited with exit code 1
> .
>
> If we check the status on another session then it showing the status as
> prepared.
> =# select * from pg_fdw_xacts;
>  dbid  | transaction | serverid | userid |  status  | identifier
> ---+-+--++--+
>  13119 |1688 |16388 | 10 | prepared | px_2102366504_16388_10
>  13119 |1688 |16389 | 10 | prepared | px_749056984_16389_10
> (2 rows)
>
> I think this is a bug.
>

Thank you for reviewing!

I think this is a bug of pg_fdw_resolver contrib module. I had
forgotten to change the SQL executed by pg_fdw_resolver process.
Attached latest version 002 patch.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


002_pg_fdw_resolver_contrib_v5.patch
Description: binary/octet-stream

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[vinayak]

On 2017/01/16 17:35, Masahiko Sawada wrote:

On Fri, Jan 13, 2017 at 3:48 PM, Masahiko Sawada  wrote:

On Fri, Jan 13, 2017 at 3:20 PM, Ashutosh Bapat
 wrote:

Long time passed since original patch proposed by Ashutosh, so I
explain again about current design and functionality of this feature.
If you have any question, please feel free to ask.

Thanks for the summary.


Parameters
==

[ snip ]


Cluster-wide atomic commit
===
Since the distributed transaction commit on foreign servers are
executed independently, the transaction that modified data on the
multiple foreign servers is not ensured that transaction did either
all of them commit or all of them rollback. The patch adds the
functionality that guarantees distributed transaction did either
commit or rollback on all foreign servers. IOW the goal of this patch
is achieving the cluster-wide atomic commit across foreign server that
is capable two phase commit protocol.

In [1], I proposed that we solve the problem of supporting PREPARED
transactions involving foreign servers and in subsequent mail Vinayak
agreed to that. But this goal has wider scope than that proposal. I am
fine widening the scope, but then it would again lead to the same
discussion we had about the big picture. May be you want to share
design (or point out the parts of this design that will help) for
solving smaller problem and tone down the patch for the same.


Sorry for confuse you. I'm still focusing on solving only that
problem. What I was trying to say is that I think that supporting
PREPARED transaction involving foreign server is the means, not the
end. So once we supports PREPARED transaction involving foreign
servers we can achieve cluster-wide atomic commit in a sense.


Attached updated patches. I fixed some bugs and add 003 patch that
adds TAP test for foreign transaction.
003 patch depends 000 and 001 patch.

Please give me feedback.


I have tested prepared transactions with foreign servers but after 
preparing the transaction

the following error occur infinitely.
Test:
=
=#BEGIN;
=#INSERT INTO ft1_lt VALUES (10);
=#INSERT INTO ft2_lt VALUES (20);
=#PREPARE TRANSACTION 'prep_xact_with_fdw';

2017-01-18 15:09:48.378 JST [4312] ERROR:  function pg_fdw_resolve() 
does not exist at character 8
2017-01-18 15:09:48.378 JST [4312] HINT:  No function matches the given 
name and argument types. You might need to add explicit type casts.

2017-01-18 15:09:48.378 JST [4312] QUERY:  SELECT pg_fdw_resolve()
2017-01-18 15:09:48.378 JST [29224] LOG:  worker process: foreign 
transaction resolver (dbid 13119) (PID 4312) exited with exit code 1

.

If we check the status on another session then it showing the status as 
prepared.

=# select * from pg_fdw_xacts;
 dbid  | transaction | serverid | userid |  status  | identifier
---+-+--++--+ 

 13119 |1688 |16388 | 10 | prepared | 
px_2102366504_16388_10
 13119 |1688 |16389 | 10 | prepared | 
px_749056984_16389_10

(2 rows)

I think this is a bug.

Regards,
Vinayak Pokale
NTT Open Source Software Center



--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Fri, Jan 13, 2017 at 3:20 PM, Ashutosh Bapat
 wrote:
>>>
>>
>> Long time passed since original patch proposed by Ashutosh, so I
>> explain again about current design and functionality of this feature.
>> If you have any question, please feel free to ask.
>
> Thanks for the summary.
>
>>
>> Parameters
>> ==
>
> [ snip ]
>
>>
>> Cluster-wide atomic commit
>> ===
>> Since the distributed transaction commit on foreign servers are
>> executed independently, the transaction that modified data on the
>> multiple foreign servers is not ensured that transaction did either
>> all of them commit or all of them rollback. The patch adds the
>> functionality that guarantees distributed transaction did either
>> commit or rollback on all foreign servers. IOW the goal of this patch
>> is achieving the cluster-wide atomic commit across foreign server that
>> is capable two phase commit protocol.
>
> In [1], I proposed that we solve the problem of supporting PREPARED
> transactions involving foreign servers and in subsequent mail Vinayak
> agreed to that. But this goal has wider scope than that proposal. I am
> fine widening the scope, but then it would again lead to the same
> discussion we had about the big picture. May be you want to share
> design (or point out the parts of this design that will help) for
> solving smaller problem and tone down the patch for the same.
>

Sorry for confuse you. I'm still focusing on solving only that
problem. What I was trying to say is that I think that supporting
PREPARED transaction involving foreign server is the means, not the
end. So once we supports PREPARED transaction involving foreign
servers we can achieve cluster-wide atomic commit in a sense.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

>>
>
> Long time passed since original patch proposed by Ashutosh, so I
> explain again about current design and functionality of this feature.
> If you have any question, please feel free to ask.

Thanks for the summary.

>
> Parameters
> ==

[ snip ]

>
> Cluster-wide atomic commit
> ===
> Since the distributed transaction commit on foreign servers are
> executed independently, the transaction that modified data on the
> multiple foreign servers is not ensured that transaction did either
> all of them commit or all of them rollback. The patch adds the
> functionality that guarantees distributed transaction did either
> commit or rollback on all foreign servers. IOW the goal of this patch
> is achieving the cluster-wide atomic commit across foreign server that
> is capable two phase commit protocol.

In [1], I proposed that we solve the problem of supporting PREPARED
transactions involving foreign servers and in subsequent mail Vinayak
agreed to that. But this goal has wider scope than that proposal. I am
fine widening the scope, but then it would again lead to the same
discussion we had about the big picture. May be you want to share
design (or point out the parts of this design that will help) for
solving smaller problem and tone down the patch for the same.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Fri, Dec 23, 2016 at 1:49 AM, Masahiko Sawada  wrote:
> On Fri, Dec 9, 2016 at 4:02 PM, Masahiko Sawada  wrote:
>> On Fri, Dec 9, 2016 at 3:02 PM, vinayak  
>> wrote:
>>> On 2016/12/05 14:42, Ashutosh Bapat wrote:

 On Mon, Dec 5, 2016 at 11:04 AM, Haribabu Kommi
  wrote:


 On Fri, Nov 11, 2016 at 5:38 PM, Masahiko Sawada 
 wrote:
>>
>>
>> 2PC is a basic building block to support the atomic commit and there
>> are some optimizations way in order to reduce disadvantage of 2PC. As
>> you mentioned, it's hard to support a single model that would suit
>> several type of FDWs. But even if it's not a purpose for sharding,
>> because many other database which could be connected to PostgreSQL via
>> FDW supports 2PC, 2PC for FDW would be useful for not only sharding
>> purpose. That's why I was focusing on implementing 2PC for FDW so far.
>
>
> Moved to next CF with "needs review" status.

 I think this should be changed to "returned with feedback.". The
 design and approach itself needs to be discussed. I think, we should
 let authors decide whether they want it to be added to the next
 commitfest or not.

 When I first started with this work, Tom had suggested me to try to
 make PREPARE and COMMIT/ROLLBACK PREPARED involving foreign servers or
 at least postgres_fdw servers work. I think, most of my work that
 Vinayak and Sawada have rebased to the latest master will be required
 for getting what Tom suggested done. We wouldn't need a lot of changes
 to that design. PREPARE involving foreign servers errors out right
 now. If we start supporting prepared transactions involving foreign
 servers that will be a good improvement over the current status-quo.
 Once we get that done, we can continue working on the larger problem
 of supporting ACID transactions involving foreign servers.
>>>
>>> In the pgconf ASIA depelopers meeting Bruce Momjian and other developers
>>> discussed
>>> on FDW based sharding [1]. The suggestions from other hackers was that we
>>> need to discuss
>>> the big picture and use cases of sharding. Bruce has listed all the building
>>> blocks of built-in sharding
>>> on wiki [2]. IIUC,transaction manager involving foreign servers is one part
>>> of sharding.
>>
>> Yeah, the 2PC on FDW is a basic building block for FDW based sharding
>> and it would be useful not only FDW sharding but also other purposes.
>> As far as I surveyed some papers the many kinds of distributed
>> transaction management architectures use the 2PC for atomic commit
>> with some optimisations. And using 2PC to provide atomic commit on
>> distributed transaction has much affinity with current PostgreSQL
>> implementation from some perspective.
>>
>>> As per the Bruce's wiki page there are two use cases for transactions
>>> involved multiple foreign servers:
>>> 1. Cross-node read-only queries on read/write shards:
>>> This will require a global snapshot manager to make sure the shards
>>> return consistent data.
>>> 2. Cross-node read-write queries:
>>> This will require a global snapshot manager and global transaction
>>> manager.
>>>
>>> I agree with you that if we start supporting PREPARE and COMMIT/ROLLBACK
>>> PREPARED
>>> involving foreign servers that will be good improvement.
>>>
>>> [1] https://wiki.postgresql.org/wiki/PgConf.Asia_2016_Developer_Meeting
>>> [2] https://wiki.postgresql.org/wiki/Built-in_Sharding
>>>
>>
>> I also agree to work on implementing the atomic commit across the
>> foreign servers and then continue to work on the more larger problem.
>> I think that this will be large step forward. I'm going to submit the
>> updated version patch to CF3.
>
> Attached latest version patches. Almost design is the same as previous
> patches and I incorporated some optimisations and updated
> documentation. But the documentation and regression test is not still
> enough.
>
> 000 patch adds some new FDW APIs to achive the atomic commit involving
> the foreign servers using two-phase-commit. If more than one foreign
> servers involve with the transaction or the transaction changes local
> data and involves even one foreign server, local node executes PREPARE
> and COMMIT/ROLLBACK PREPARED on foreign servers at commit. A lot of
> part of this implementation is inspired by two phase commit code. So I
> incorporated recent changes of two phase commit code, for example
> recovery speed improvement, into this patch.
> 001 patch makes postgres_fdw support atomic commit. If
> two_phase_commit is set 'on' to a foreign server, the two-phase-commit
> will be used at commit. 002 patch adds the pg_fdw_resolver new contrib
> module that is a bgworker process that resolves the in-doubt
> transaction on foreign server if there is.
>
> The reply might be 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Fri, Dec 9, 2016 at 3:02 PM, vinayak  wrote:
> On 2016/12/05 14:42, Ashutosh Bapat wrote:
>>
>> On Mon, Dec 5, 2016 at 11:04 AM, Haribabu Kommi
>>  wrote:
>>
>>
>> On Fri, Nov 11, 2016 at 5:38 PM, Masahiko Sawada 
>> wrote:


 2PC is a basic building block to support the atomic commit and there
 are some optimizations way in order to reduce disadvantage of 2PC. As
 you mentioned, it's hard to support a single model that would suit
 several type of FDWs. But even if it's not a purpose for sharding,
 because many other database which could be connected to PostgreSQL via
 FDW supports 2PC, 2PC for FDW would be useful for not only sharding
 purpose. That's why I was focusing on implementing 2PC for FDW so far.
>>>
>>>
>>> Moved to next CF with "needs review" status.
>>
>> I think this should be changed to "returned with feedback.". The
>> design and approach itself needs to be discussed. I think, we should
>> let authors decide whether they want it to be added to the next
>> commitfest or not.
>>
>> When I first started with this work, Tom had suggested me to try to
>> make PREPARE and COMMIT/ROLLBACK PREPARED involving foreign servers or
>> at least postgres_fdw servers work. I think, most of my work that
>> Vinayak and Sawada have rebased to the latest master will be required
>> for getting what Tom suggested done. We wouldn't need a lot of changes
>> to that design. PREPARE involving foreign servers errors out right
>> now. If we start supporting prepared transactions involving foreign
>> servers that will be a good improvement over the current status-quo.
>> Once we get that done, we can continue working on the larger problem
>> of supporting ACID transactions involving foreign servers.
>
> In the pgconf ASIA depelopers meeting Bruce Momjian and other developers
> discussed
> on FDW based sharding [1]. The suggestions from other hackers was that we
> need to discuss
> the big picture and use cases of sharding. Bruce has listed all the building
> blocks of built-in sharding
> on wiki [2]. IIUC,transaction manager involving foreign servers is one part
> of sharding.

Yeah, the 2PC on FDW is a basic building block for FDW based sharding
and it would be useful not only FDW sharding but also other purposes.
As far as I surveyed some papers the many kinds of distributed
transaction management architectures use the 2PC for atomic commit
with some optimisations. And using 2PC to provide atomic commit on
distributed transaction has much affinity with current PostgreSQL
implementation from some perspective.

> As per the Bruce's wiki page there are two use cases for transactions
> involved multiple foreign servers:
> 1. Cross-node read-only queries on read/write shards:
> This will require a global snapshot manager to make sure the shards
> return consistent data.
> 2. Cross-node read-write queries:
> This will require a global snapshot manager and global transaction
> manager.
>
> I agree with you that if we start supporting PREPARE and COMMIT/ROLLBACK
> PREPARED
> involving foreign servers that will be good improvement.
>
> [1] https://wiki.postgresql.org/wiki/PgConf.Asia_2016_Developer_Meeting
> [2] https://wiki.postgresql.org/wiki/Built-in_Sharding
>

I also agree to work on implementing the atomic commit across the
foreign servers and then continue to work on the more larger problem.
I think that this will be large step forward. I'm going to submit the
updated version patch to CF3.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[vinayak]

On 2016/12/05 14:42, Ashutosh Bapat wrote:

On Mon, Dec 5, 2016 at 11:04 AM, Haribabu Kommi
 wrote:


On Fri, Nov 11, 2016 at 5:38 PM, Masahiko Sawada 
wrote:


2PC is a basic building block to support the atomic commit and there
are some optimizations way in order to reduce disadvantage of 2PC. As
you mentioned, it's hard to support a single model that would suit
several type of FDWs. But even if it's not a purpose for sharding,
because many other database which could be connected to PostgreSQL via
FDW supports 2PC, 2PC for FDW would be useful for not only sharding
purpose. That's why I was focusing on implementing 2PC for FDW so far.


Moved to next CF with "needs review" status.

I think this should be changed to "returned with feedback.". The
design and approach itself needs to be discussed. I think, we should
let authors decide whether they want it to be added to the next
commitfest or not.

When I first started with this work, Tom had suggested me to try to
make PREPARE and COMMIT/ROLLBACK PREPARED involving foreign servers or
at least postgres_fdw servers work. I think, most of my work that
Vinayak and Sawada have rebased to the latest master will be required
for getting what Tom suggested done. We wouldn't need a lot of changes
to that design. PREPARE involving foreign servers errors out right
now. If we start supporting prepared transactions involving foreign
servers that will be a good improvement over the current status-quo.
Once we get that done, we can continue working on the larger problem
of supporting ACID transactions involving foreign servers.
In the pgconf ASIA depelopers meeting Bruce Momjian and other developers 
discussed
on FDW based sharding [1]. The suggestions from other hackers was that 
we need to discuss
the big picture and use cases of sharding. Bruce has listed all the 
building blocks of built-in sharding
on wiki [2]. IIUC,transaction manager involving foreign servers is one 
part of sharding.
As per the Bruce's wiki page there are two use cases for transactions 
involved multiple foreign servers:

1. Cross-node read-only queries on read/write shards:
This will require a global snapshot manager to make sure the shards 
return consistent data.

2. Cross-node read-write queries:
This will require a global snapshot manager and global transaction 
manager.


I agree with you that if we start supporting PREPARE and COMMIT/ROLLBACK 
PREPARED

involving foreign servers that will be good improvement.

[1] https://wiki.postgresql.org/wiki/PgConf.Asia_2016_Developer_Meeting
[2] https://wiki.postgresql.org/wiki/Built-in_Sharding

Regards,
Vinayak Pokale
NTT Opern Source Software Center


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Haribabu Kommi]

On Mon, Dec 5, 2016 at 4:42 PM, Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com> wrote:

> On Mon, Dec 5, 2016 at 11:04 AM, Haribabu Kommi
>  wrote:
> >
> >
> > On Fri, Nov 11, 2016 at 5:38 PM, Masahiko Sawada 
> > wrote:
> >>
> >>
> >> 2PC is a basic building block to support the atomic commit and there
> >> are some optimizations way in order to reduce disadvantage of 2PC. As
> >> you mentioned, it's hard to support a single model that would suit
> >> several type of FDWs. But even if it's not a purpose for sharding,
> >> because many other database which could be connected to PostgreSQL via
> >> FDW supports 2PC, 2PC for FDW would be useful for not only sharding
> >> purpose. That's why I was focusing on implementing 2PC for FDW so far.
> >
> >
> > Moved to next CF with "needs review" status.
>
> I think this should be changed to "returned with feedback.". The
> design and approach itself needs to be discussed. I think, we should
> let authors decide whether they want it to be added to the next
> commitfest or not.
>
> When I first started with this work, Tom had suggested me to try to
> make PREPARE and COMMIT/ROLLBACK PREPARED involving foreign servers or
> at least postgres_fdw servers work. I think, most of my work that
> Vinayak and Sawada have rebased to the latest master will be required
> for getting what Tom suggested done. We wouldn't need a lot of changes
> to that design. PREPARE involving foreign servers errors out right
> now. If we start supporting prepared transactions involving foreign
> servers that will be a good improvement over the current status-quo.
> Once we get that done, we can continue working on the larger problem
> of supporting ACID transactions involving foreign servers.



Thanks for the update.
I closed it in commitfest 2017-01 with "returned with feedback". Author can
update it once the new patch is submitted.

Regards,
Hari Babu
Fujitsu Australia

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Mon, Dec 5, 2016 at 11:04 AM, Haribabu Kommi
 wrote:
>
>
> On Fri, Nov 11, 2016 at 5:38 PM, Masahiko Sawada 
> wrote:
>>
>>
>> 2PC is a basic building block to support the atomic commit and there
>> are some optimizations way in order to reduce disadvantage of 2PC. As
>> you mentioned, it's hard to support a single model that would suit
>> several type of FDWs. But even if it's not a purpose for sharding,
>> because many other database which could be connected to PostgreSQL via
>> FDW supports 2PC, 2PC for FDW would be useful for not only sharding
>> purpose. That's why I was focusing on implementing 2PC for FDW so far.
>
>
> Moved to next CF with "needs review" status.

I think this should be changed to "returned with feedback.". The
design and approach itself needs to be discussed. I think, we should
let authors decide whether they want it to be added to the next
commitfest or not.

When I first started with this work, Tom had suggested me to try to
make PREPARE and COMMIT/ROLLBACK PREPARED involving foreign servers or
at least postgres_fdw servers work. I think, most of my work that
Vinayak and Sawada have rebased to the latest master will be required
for getting what Tom suggested done. We wouldn't need a lot of changes
to that design. PREPARE involving foreign servers errors out right
now. If we start supporting prepared transactions involving foreign
servers that will be a good improvement over the current status-quo.
Once we get that done, we can continue working on the larger problem
of supporting ACID transactions involving foreign servers.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Haribabu Kommi]

On Fri, Nov 11, 2016 at 5:38 PM, Masahiko Sawada 
wrote:

>
> 2PC is a basic building block to support the atomic commit and there
> are some optimizations way in order to reduce disadvantage of 2PC. As
> you mentioned, it's hard to support a single model that would suit
> several type of FDWs. But even if it's not a purpose for sharding,
> because many other database which could be connected to PostgreSQL via
> FDW supports 2PC, 2PC for FDW would be useful for not only sharding
> purpose. That's why I was focusing on implementing 2PC for FDW so far.


Moved to next CF with "needs review" status.

Regards,
Hari Babu
Fujitsu Australia

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Wed, Nov 2, 2016 at 9:22 PM, Ashutosh Bapat
 wrote:
> On Mon, Oct 31, 2016 at 6:17 AM, Masahiko Sawada  
> wrote:
>> On Fri, Oct 28, 2016 at 3:19 AM, Robert Haas  wrote:
>>> On Wed, Oct 26, 2016 at 2:00 AM, Masahiko Sawada  
>>> wrote:
 I think we can consider the atomic commit and the atomic visibility
 separately, and the atomic visibility can build on the top of the
 atomic commit.
>>>
>>> It is true that we can do that, but I'm not sure whether it's the best 
>>> design.
>>
>> I'm not sure best design, too. We need to discuss more. But this is
>> not a particular feature for the sharing solution. The atomic commit
>> using 2PC is useful for other servers that can use 2PC, not only
>> postgres_fdw.
>>
>
> I think, we need to discuss the big picture i.e. architecture for
> distributed transaction manager for PostgreSQL. Divide it in smaller
> problems and then solve each of them as series of commits possibly
> producing a useful feature with each commit. I think, what Robert is
> pointing out is if we spend time solving smaller problems, we might
> end up with something which can not be used to solve the bigger
> problem. Instead, if we define the bigger problem and come up with
> clear subproblems that when solved would solve the bigger problem, we
> may not end up in such a situation.
>
> There are many distributed transaction models discussed in various
> papers like [1], [2], [3]. We need to assess which one/s, would suit
> PostgreSQL FDW infrastructure and may be specifically for
> postgres_fdw. There is some discussion at [4]. It lists a few
> approaches, but I could not find a discussion on pros and cons of each
> of them, and a conclusion as to which of the approaches suits
> PostgreSQL. May be we want to start that discussion.

Agreed. Let's start discussion.
I think that it's important to choose what type of transaction
coordination we employ; centralized or distributed.

> I know that it's hard to come up with a single model that would suit
> FDWs or would serve all kinds of applications. We may not be able to
> support a full distributed transaction manager for every FDW out
> there. It's possible that because of lack of the big picture, we will
> not see anything happen in this area for another release. Given that
> and since all of the models in those papers require 2PC as a basic
> building block, I was of the opinion that we could at least start with
> 2PC implementation. But I think request for bigger picture is also
> valid for reasons stated above.

2PC is a basic building block to support the atomic commit and there
are some optimizations way in order to reduce disadvantage of 2PC. As
you mentioned, it's hard to support a single model that would suit
several type of FDWs. But even if it's not a purpose for sharding,
because many other database which could be connected to PostgreSQL via
FDW supports 2PC, 2PC for FDW would be useful for not only sharding
purpose. That's why I was focusing on implementing 2PC for FDW so far.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Mon, Oct 31, 2016 at 6:17 AM, Masahiko Sawada  wrote:
> On Fri, Oct 28, 2016 at 3:19 AM, Robert Haas  wrote:
>> On Wed, Oct 26, 2016 at 2:00 AM, Masahiko Sawada  
>> wrote:
>>> I think we can consider the atomic commit and the atomic visibility
>>> separately, and the atomic visibility can build on the top of the
>>> atomic commit.
>>
>> It is true that we can do that, but I'm not sure whether it's the best 
>> design.
>
> I'm not sure best design, too. We need to discuss more. But this is
> not a particular feature for the sharing solution. The atomic commit
> using 2PC is useful for other servers that can use 2PC, not only
> postgres_fdw.
>

I think, we need to discuss the big picture i.e. architecture for
distributed transaction manager for PostgreSQL. Divide it in smaller
problems and then solve each of them as series of commits possibly
producing a useful feature with each commit. I think, what Robert is
pointing out is if we spend time solving smaller problems, we might
end up with something which can not be used to solve the bigger
problem. Instead, if we define the bigger problem and come up with
clear subproblems that when solved would solve the bigger problem, we
may not end up in such a situation.

There are many distributed transaction models discussed in various
papers like [1], [2], [3]. We need to assess which one/s, would suit
PostgreSQL FDW infrastructure and may be specifically for
postgres_fdw. There is some discussion at [4]. It lists a few
approaches, but I could not find a discussion on pros and cons of each
of them, and a conclusion as to which of the approaches suits
PostgreSQL. May be we want to start that discussion.

I know that it's hard to come up with a single model that would suit
FDWs or would serve all kinds of applications. We may not be able to
support a full distributed transaction manager for every FDW out
there. It's possible that because of lack of the big picture, we will
not see anything happen in this area for another release. Given that
and since all of the models in those papers require 2PC as a basic
building block, I was of the opinion that we could at least start with
2PC implementation. But I think request for bigger picture is also
valid for reasons stated above.

> Attached latest 3 patches that incorporated review comments so far.
> But recovery speed improvement that is discussed on another thread is
> not incorporated yet.
> Please give me feedback.
>


[1] http://link.springer.com/article/10.1007/s00778-014-0359-9
[2] 
https://domino.mpi-inf.mpg.de/intranet/ag5/ag5publ.nsf/1c0a12a383dd2cd8c125613300585c64/7684dd8109a5b3d5c1256de40051686f/$FILE/tdd99.pdf
[3] http://docs.lib.purdue.edu/cgi/viewcontent.cgi?article=1713=cstech
[4] https://wiki.postgresql.org/wiki/DTM

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Wed, Oct 26, 2016 at 2:00 AM, Masahiko Sawada  wrote:
> I think we can consider the atomic commit and the atomic visibility
> separately, and the atomic visibility can build on the top of the
> atomic commit.

It is true that we can do that, but I'm not sure whether it's the best design.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Fri, Oct 21, 2016 at 1:38 AM, Ashutosh Bapat
 wrote:
> Once we have that information, the foreign server can actively poll
> the local server to get the status of transaction xid and resolves the
> prepared transaction itself. It can go a step further and inform the
> local server that it has resolved the transaction, so that the local
> server can purge it from it's own state. It can remember the fate of
> xid, which can be consulted by another foreign server if the local
> server is down. If another transaction on the foreign server stumbles
> on a transaction prepared (but not resolved) by the local server,
> foreign server has two options - 1. consult the local server and
> resolve 2. if the first options fails to get the status of xid or that
> if that option is not workable, throw an error e.g. indoubt
> transaction. There is probably more network traffic happening here.
> Usually, the local server should be able to resolve the transaction
> before any other transaction stumbles upon it. The overhead is
> incurred only when necessary.

Yes, something like this could be done.  It's pretty complicated, though.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Fri, Oct 21, 2016 at 2:38 PM, Ashutosh Bapat
 wrote:
> On Wed, Oct 19, 2016 at 9:17 PM, Robert Haas  wrote:
>> On Thu, Oct 13, 2016 at 7:27 AM, Amit Langote
>>  wrote:
>>> However, when I briefly read the description in "Transaction Management in
>>> the R* Distributed Database Management System (C. Mohan et al)" [2], it
>>> seems that what Ashutosh is saying might be a correct way to proceed after
>>> all:
>>
>> I think Ashutosh is mostly right, but I think there's a lot of room to
>> doubt whether the design of this patch is good enough that we should
>> adopt it.
>>
>> Consider two possible designs.  In design #1, the leader performs the
>> commit locally and then tries to send COMMIT PREPARED to every standby
>> server afterward, and only then acknowledges the commit to the client.
>> In design #2, the leader performs the commit locally and then
>> acknowledges the commit to the client at once, leaving the task of
>> running COMMIT PREPARED to some background process.  Design #2
>> involves a race condition, because it's possible that the background
>> process might not complete COMMIT PREPARED on every node before the
>> user submits the next query, and that query might then fail to see
>> supposedly-committed changes.  This can't happen in design #1.  On the
>> other hand, there's always the possibility that the leader's session
>> is forcibly killed, even perhaps by pulling the plug.  If the
>> background process contemplated by design #2 is well-designed, it can
>> recover and finish sending COMMIT PREPARED to each relevant server
>> after the next restart.  In design #1, that background process doesn't
>> necessarily exist, so inevitably there is a possibility of orphaning
>> prepared transactions on the remote servers, which is not good. Even
>> if the DBA notices them, it won't be easy to figure out whether to
>> commit them or roll them back.
>>
>> I think this thought experiment shows that, on the one hand, there is
>> a point to waiting for commits on the foreign servers, because it can
>> avoid the anomaly of not seeing the effects of your own commits.  On
>> the other hand, it's ridiculous to suppose that every case can be
>> handled by waiting, because that just isn't true.  You can't be sure
>> that you'll be able to wait long enough for COMMIT PREPARED to
>> complete, and even if that works out, you may not want to wait
>> indefinitely for a dead server.  Waiting for a ROLLBACK PREPARED has
>> no value whatsoever unless the system design is such that failing to
>> wait for it results in the ROLLBACK PREPARED never getting performed
>> -- which is a pretty poor excuse.
>>
>> Moreover, there are good reasons to think that doing this kind of
>> cleanup work in the post-commit hooks is never going to be acceptable.
>> Generally, the post-commit hooks need to be no-fail, because it's too
>> late to throw an ERROR.  But there's very little hope that a
>> connection to a remote server can be no-fail; anything that involves a
>> network connection is, by definition, prone to failure.  We can try to
>> guarantee that every single bit of code that runs in the path that
>> sends COMMIT PREPARED only raises a WARNING or NOTICE rather than an
>> ERROR, but that's going to be quite difficult to do: even palloc() can
>> throw an error.  And what about interrupts?  We don't want to be stuck
>> inside this code for a long time without any hope of the user
>> recovering control of the session by pressing ^C, but of course the
>> way that works is it throws an ERROR, which we can't handle here.  We
>> fixed a similar issue for synchronous replication in
>> 9a56dc3389b9470031e9ef8e45c95a680982e01a by making an interrupt emit a
>> WARNING in that case and then return control to the user.  But if we
>> do that here, all of the code that every FDW emits has to be aware of
>> that rule and follow it, and it just adds to the list of ways that the
>> user backend can escape this code without having cleaned up all of the
>> prepared transactions on the remote side.
>
> Hmm, IIRC, my patch and possibly patch by Masahiko-san and Vinayak,
> tries to resolve prepared transactions in post-commit code. I agree
> with you here, that it should be avoided and the backend should take
> over the job of resolving transactions.
>
>>
>> It seems to me that the only way to really make this feature robust is
>> to have a background worker as part of the equation.  The background
>> worker launches at startup and looks around for local state that tells
>> it whether there are any COMMIT PREPARED or ROLLBACK PREPARED
>> operations pending that weren't completed during the last server
>> lifetime, whether because of a local crash or remote unavailability.
>> It attempts to complete those and retries periodically.  When a new
>> transaction needs this type of coordination, it adds the necessary
>> crash-proof state and then signals the 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Wed, Oct 19, 2016 at 9:17 PM, Robert Haas  wrote:
> On Thu, Oct 13, 2016 at 7:27 AM, Amit Langote
>  wrote:
>> However, when I briefly read the description in "Transaction Management in
>> the R* Distributed Database Management System (C. Mohan et al)" [2], it
>> seems that what Ashutosh is saying might be a correct way to proceed after
>> all:
>
> I think Ashutosh is mostly right, but I think there's a lot of room to
> doubt whether the design of this patch is good enough that we should
> adopt it.
>
> Consider two possible designs.  In design #1, the leader performs the
> commit locally and then tries to send COMMIT PREPARED to every standby
> server afterward, and only then acknowledges the commit to the client.
> In design #2, the leader performs the commit locally and then
> acknowledges the commit to the client at once, leaving the task of
> running COMMIT PREPARED to some background process.  Design #2
> involves a race condition, because it's possible that the background
> process might not complete COMMIT PREPARED on every node before the
> user submits the next query, and that query might then fail to see
> supposedly-committed changes.  This can't happen in design #1.  On the
> other hand, there's always the possibility that the leader's session
> is forcibly killed, even perhaps by pulling the plug.  If the
> background process contemplated by design #2 is well-designed, it can
> recover and finish sending COMMIT PREPARED to each relevant server
> after the next restart.  In design #1, that background process doesn't
> necessarily exist, so inevitably there is a possibility of orphaning
> prepared transactions on the remote servers, which is not good. Even
> if the DBA notices them, it won't be easy to figure out whether to
> commit them or roll them back.
>
> I think this thought experiment shows that, on the one hand, there is
> a point to waiting for commits on the foreign servers, because it can
> avoid the anomaly of not seeing the effects of your own commits.  On
> the other hand, it's ridiculous to suppose that every case can be
> handled by waiting, because that just isn't true.  You can't be sure
> that you'll be able to wait long enough for COMMIT PREPARED to
> complete, and even if that works out, you may not want to wait
> indefinitely for a dead server.  Waiting for a ROLLBACK PREPARED has
> no value whatsoever unless the system design is such that failing to
> wait for it results in the ROLLBACK PREPARED never getting performed
> -- which is a pretty poor excuse.
>
> Moreover, there are good reasons to think that doing this kind of
> cleanup work in the post-commit hooks is never going to be acceptable.
> Generally, the post-commit hooks need to be no-fail, because it's too
> late to throw an ERROR.  But there's very little hope that a
> connection to a remote server can be no-fail; anything that involves a
> network connection is, by definition, prone to failure.  We can try to
> guarantee that every single bit of code that runs in the path that
> sends COMMIT PREPARED only raises a WARNING or NOTICE rather than an
> ERROR, but that's going to be quite difficult to do: even palloc() can
> throw an error.  And what about interrupts?  We don't want to be stuck
> inside this code for a long time without any hope of the user
> recovering control of the session by pressing ^C, but of course the
> way that works is it throws an ERROR, which we can't handle here.  We
> fixed a similar issue for synchronous replication in
> 9a56dc3389b9470031e9ef8e45c95a680982e01a by making an interrupt emit a
> WARNING in that case and then return control to the user.  But if we
> do that here, all of the code that every FDW emits has to be aware of
> that rule and follow it, and it just adds to the list of ways that the
> user backend can escape this code without having cleaned up all of the
> prepared transactions on the remote side.

Hmm, IIRC, my patch and possibly patch by Masahiko-san and Vinayak,
tries to resolve prepared transactions in post-commit code. I agree
with you here, that it should be avoided and the backend should take
over the job of resolving transactions.

>
> It seems to me that the only way to really make this feature robust is
> to have a background worker as part of the equation.  The background
> worker launches at startup and looks around for local state that tells
> it whether there are any COMMIT PREPARED or ROLLBACK PREPARED
> operations pending that weren't completed during the last server
> lifetime, whether because of a local crash or remote unavailability.
> It attempts to complete those and retries periodically.  When a new
> transaction needs this type of coordination, it adds the necessary
> crash-proof state and then signals the background worker.  If
> appropriate, it can wait for the background worker to complete, just
> like a CHECKPOINT waits for the checkpointer to finish -- but if the
> CHECKPOINT 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Bruce Momjian]

On Wed, Oct 19, 2016 at 11:47:25AM -0400, Robert Haas wrote:
> It seems to me that the only way to really make this feature robust is
> to have a background worker as part of the equation.  The background
> worker launches at startup and looks around for local state that tells
> it whether there are any COMMIT PREPARED or ROLLBACK PREPARED
> operations pending that weren't completed during the last server
> lifetime, whether because of a local crash or remote unavailability.

Yes, you really need both commit on foreign servers before acknowledging
commit to the client, and a background process to clean things up from
an abandoned server.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+  Ancient Roman grave inscription +


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Robert Haas]

On Thu, Oct 13, 2016 at 7:27 AM, Amit Langote
 wrote:
> However, when I briefly read the description in "Transaction Management in
> the R* Distributed Database Management System (C. Mohan et al)" [2], it
> seems that what Ashutosh is saying might be a correct way to proceed after
> all:

I think Ashutosh is mostly right, but I think there's a lot of room to
doubt whether the design of this patch is good enough that we should
adopt it.

Consider two possible designs.  In design #1, the leader performs the
commit locally and then tries to send COMMIT PREPARED to every standby
server afterward, and only then acknowledges the commit to the client.
In design #2, the leader performs the commit locally and then
acknowledges the commit to the client at once, leaving the task of
running COMMIT PREPARED to some background process.  Design #2
involves a race condition, because it's possible that the background
process might not complete COMMIT PREPARED on every node before the
user submits the next query, and that query might then fail to see
supposedly-committed changes.  This can't happen in design #1.  On the
other hand, there's always the possibility that the leader's session
is forcibly killed, even perhaps by pulling the plug.  If the
background process contemplated by design #2 is well-designed, it can
recover and finish sending COMMIT PREPARED to each relevant server
after the next restart.  In design #1, that background process doesn't
necessarily exist, so inevitably there is a possibility of orphaning
prepared transactions on the remote servers, which is not good. Even
if the DBA notices them, it won't be easy to figure out whether to
commit them or roll them back.

I think this thought experiment shows that, on the one hand, there is
a point to waiting for commits on the foreign servers, because it can
avoid the anomaly of not seeing the effects of your own commits.  On
the other hand, it's ridiculous to suppose that every case can be
handled by waiting, because that just isn't true.  You can't be sure
that you'll be able to wait long enough for COMMIT PREPARED to
complete, and even if that works out, you may not want to wait
indefinitely for a dead server.  Waiting for a ROLLBACK PREPARED has
no value whatsoever unless the system design is such that failing to
wait for it results in the ROLLBACK PREPARED never getting performed
-- which is a pretty poor excuse.

Moreover, there are good reasons to think that doing this kind of
cleanup work in the post-commit hooks is never going to be acceptable.
Generally, the post-commit hooks need to be no-fail, because it's too
late to throw an ERROR.  But there's very little hope that a
connection to a remote server can be no-fail; anything that involves a
network connection is, by definition, prone to failure.  We can try to
guarantee that every single bit of code that runs in the path that
sends COMMIT PREPARED only raises a WARNING or NOTICE rather than an
ERROR, but that's going to be quite difficult to do: even palloc() can
throw an error.  And what about interrupts?  We don't want to be stuck
inside this code for a long time without any hope of the user
recovering control of the session by pressing ^C, but of course the
way that works is it throws an ERROR, which we can't handle here.  We
fixed a similar issue for synchronous replication in
9a56dc3389b9470031e9ef8e45c95a680982e01a by making an interrupt emit a
WARNING in that case and then return control to the user.  But if we
do that here, all of the code that every FDW emits has to be aware of
that rule and follow it, and it just adds to the list of ways that the
user backend can escape this code without having cleaned up all of the
prepared transactions on the remote side.

It seems to me that the only way to really make this feature robust is
to have a background worker as part of the equation.  The background
worker launches at startup and looks around for local state that tells
it whether there are any COMMIT PREPARED or ROLLBACK PREPARED
operations pending that weren't completed during the last server
lifetime, whether because of a local crash or remote unavailability.
It attempts to complete those and retries periodically.  When a new
transaction needs this type of coordination, it adds the necessary
crash-proof state and then signals the background worker.  If
appropriate, it can wait for the background worker to complete, just
like a CHECKPOINT waits for the checkpointer to finish -- but if the
CHECKPOINT command is interrupted, the actual checkpoint is
unaffected.

More broadly, the question has been raised as to whether it's right to
try to handle atomic commit and atomic visibility as two separate
problems.  The XTM API proposed by Postgres Pro aims to address both
with a single stroke.  I don't think that API was well-designed, but
maybe the idea is good even if the code is not.  Generally, there are
two ways in which you could imagine that a distributed version 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Oct 13, 2016 at 7:37 PM, Ashutosh Bapat
 wrote:
>>>
>>> If we are successful in COMMITTING foreign transactions during
>>> post-commit phase, COMMIT message will be returned after we have
>>> committed all foreign transactions. But in case we can not reach a
>>> foreign server, and request times out, we can not revert back our
>>> decision that we are going to commit the transaction. That's my answer
>>> to the timeout based heuristic.
>>
>> IIUC 2PC is the protocol that assumes that all of the foreign server live.
>
> Do you have any references? Take a look at [1]. The first paragraph
> itself mentions that 2PC can achieve its goals despite temporary
> failures.

I guess that It doesn't mention that 2PC can it by ignoring temporary failures.
Even by waiting for the crashed server revives, 2PC can achieve its goals.

>> In case we can not reach a foreign server during post-commit phase,
>> basically the transaction and following transaction should stop until
>> the crashed server revived.
>
> I have repeatedly given reasons why this is not correct. You and Amit
> seem to repeat this statement again and again in turns without giving
> any concrete reasons about why this is so.
>
>> This is the first place to implement 2PC
>> for FDW, I think. The heuristically determination approach I mentioned
>> is one of the optimization idea to avoid holding up transaction in
>> case a foreign server crashed.
>>
>>> I don't see much point in holding up post-commit processing for a
>>> non-responsive foreign server, which may not respond for days
>>> together. Can you please elaborate a use case? Which commercial
>>> transaction manager does that?
>>
>> For example, the client updates a data on foreign server and then
>> commits. And the next transaction from the same client selects new
>> data which was updated on previous transaction. In this case, because
>> the first transaction is committed the second transaction should be
>> able to see updated data, but it can see old data in your idea. Since
>> these is obviously order between first transaction and second
>> transaction I think that It's not problem of providing consistent
>> view.
>
> 2PC doesn't guarantee this. For that you need other methods and
> protocols. We have discussed this before. [2]
>

At any rate, I think that it would confuse the user that there is no
guarantee that the latest data updated by previous transaction can be
seen by following transaction. I don't think that it's worth enough to
immolate in order to get better performance.
Providing atomic visibility for concurrency transaction would be
supported later.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Amit Langote]

On 2016/10/13 19:37, Ashutosh Bapat wrote:
>> In case we can not reach a foreign server during post-commit phase,
>> basically the transaction and following transaction should stop until
>> the crashed server revived.
> 
> I have repeatedly given reasons why this is not correct. You and Amit
> seem to repeat this statement again and again in turns without giving
> any concrete reasons about why this is so.

As mentioned in description of the "Commit" or "Completion" phase in the
Wikipedia article [1]:

* Success

If the coordinator received an agreement message from all cohorts during
the commit-request phase:

1. The coordinator sends a commit message to all the cohorts.

2. Each cohort completes the operation, and releases all the locks and
   resources held during the transaction.

3. Each cohort sends an acknowledgment to the coordinator.

4. The coordinator completes the transaction when all acknowledgments
   have been received.

* Failure

If any cohort votes No during the commit-request phase (or the
coordinator's timeout expires):

1. The coordinator sends a rollback message to all the cohorts.

2. Each cohort undoes the transaction using the undo log, and releases
   the resources and locks held during the transaction.

3. Each cohort sends an acknowledgement to the coordinator.

4. The coordinator undoes the transaction when all acknowledgements have
   been received.

In point 4 of both commit and abort cases above, it's been said, "when
*all* acknowledgements have been received."


However, when I briefly read the description in "Transaction Management in
the R* Distributed Database Management System (C. Mohan et al)" [2], it
seems that what Ashutosh is saying might be a correct way to proceed after
all:

"""
2. THE TWO-PHASE COMMIT PROTOCOL

...

After the coordinator receives the votes from all its subordinates, it
initiates the second phase of the protocol. If all the votes were YES
VOTES, then the coordinator moves to the committing state by force-writing
a commit record and sending COMMIT messages to all the subordinates. The
completion of the force-write takes the transaction to its commit point.
Once this point is passed the user can be told that the transaction has
been committed.
...

"""

Sorry about the noise.

Thanks,
Amit

[1] https://en.wikipedia.org/wiki/Two-phase_commit_protocol#Commit_phase

[2] http://www.cs.cmu.edu/~natassa/courses/15-823/F02/papers/p378-mohan.pdf





-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

>>
>> If we are successful in COMMITTING foreign transactions during
>> post-commit phase, COMMIT message will be returned after we have
>> committed all foreign transactions. But in case we can not reach a
>> foreign server, and request times out, we can not revert back our
>> decision that we are going to commit the transaction. That's my answer
>> to the timeout based heuristic.
>
> IIUC 2PC is the protocol that assumes that all of the foreign server live.

Do you have any references? Take a look at [1]. The first paragraph
itself mentions that 2PC can achieve its goals despite temporary
failures.

> In case we can not reach a foreign server during post-commit phase,
> basically the transaction and following transaction should stop until
> the crashed server revived.

I have repeatedly given reasons why this is not correct. You and Amit
seem to repeat this statement again and again in turns without giving
any concrete reasons about why this is so.

> This is the first place to implement 2PC
> for FDW, I think. The heuristically determination approach I mentioned
> is one of the optimization idea to avoid holding up transaction in
> case a foreign server crashed.
>
>> I don't see much point in holding up post-commit processing for a
>> non-responsive foreign server, which may not respond for days
>> together. Can you please elaborate a use case? Which commercial
>> transaction manager does that?
>
> For example, the client updates a data on foreign server and then
> commits. And the next transaction from the same client selects new
> data which was updated on previous transaction. In this case, because
> the first transaction is committed the second transaction should be
> able to see updated data, but it can see old data in your idea. Since
> these is obviously order between first transaction and second
> transaction I think that It's not problem of providing consistent
> view.

2PC doesn't guarantee this. For that you need other methods and
protocols. We have discussed this before. [2]


[1] https://en.wikipedia.org/wiki/Two-phase_commit_protocol
[2] 
https://www.postgresql.org/message-id/CAD21AoCTe1CFfA9g1uqETvLaJZfFH6QoPSDf-L3KZQ-CDZ7q8g%40mail.gmail.com
-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Fri, Oct 7, 2016 at 4:25 PM, Ashutosh Bapat
 wrote:
> On Thu, Oct 6, 2016 at 2:52 PM, Amit Langote
>  wrote:
>> On 2016/10/06 17:45, Ashutosh Bapat wrote:
>>> On Thu, Oct 6, 2016 at 1:34 PM, Masahiko Sawada  
>>> wrote:
 On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat 
  wrote:
>> My understanding is that basically the local server can not return
>> COMMIT to the client until 2nd phase is completed.
>
> If we do that, the local server may not return to the client at all,
> if the foreign server crashes and never comes up. Practically, it may
> take much longer to finish a COMMIT, depending upon how long it takes
> for the foreign server to reply to a COMMIT message.

 Yes, I think 2PC behaves so, please refer to [1].
 To prevent local server stops forever due to communication failure.,
 we could provide the timeout on coordinator side or on participant
 side.
>>>
>>> This too, looks like a heuristic and shouldn't be the default
>>> behaviour and hence not part of the first version of this feature.
>>
>> At any rate, the coordinator should not return to the client until after
>> the 2nd phase is completed, which was the original point.  If COMMIT
>> taking longer is an issue, then it could be handled with one of the
>> approaches mentioned so far (even if not in the first version), but no
>> version of this feature should really return COMMIT to the client only
>> after finishing the first phase.  Am I missing something?
>
> There is small time window between actual COMMIT and a commit message
> returned. An actual commit happens when we insert a WAL saying
> transaction X committed and then we return to the client saying a
> COMMIT happened. Note that a transaction may be committed but we will
> never return to the client with a commit message, because connection
> was lost or the server crashed. I hope we agree on this.

Agree.

> COMMITTING the foreign prepared transactions happens after we COMMIT
> the local transaction. If we do it before COMMITTING local transaction
> and the local server crashes, we will roll back local transaction
> during subsequence recovery while the foreign segments have committed
> resulting in an inconsistent state.
>
> If we are successful in COMMITTING foreign transactions during
> post-commit phase, COMMIT message will be returned after we have
> committed all foreign transactions. But in case we can not reach a
> foreign server, and request times out, we can not revert back our
> decision that we are going to commit the transaction. That's my answer
> to the timeout based heuristic.

IIUC 2PC is the protocol that assumes that all of the foreign server live.
In case we can not reach a foreign server during post-commit phase,
basically the transaction and following transaction should stop until
the crashed server revived. This is the first place to implement 2PC
for FDW, I think. The heuristically determination approach I mentioned
is one of the optimization idea to avoid holding up transaction in
case a foreign server crashed.

> I don't see much point in holding up post-commit processing for a
> non-responsive foreign server, which may not respond for days
> together. Can you please elaborate a use case? Which commercial
> transaction manager does that?

For example, the client updates a data on foreign server and then
commits. And the next transaction from the same client selects new
data which was updated on previous transaction. In this case, because
the first transaction is committed the second transaction should be
able to see updated data, but it can see old data in your idea. Since
these is obviously order between first transaction and second
transaction I think that It's not problem of providing consistent
view.

I guess transaction manager of Postgres-XC behaves so, no?

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Thu, Oct 6, 2016 at 2:52 PM, Amit Langote
 wrote:
> On 2016/10/06 17:45, Ashutosh Bapat wrote:
>> On Thu, Oct 6, 2016 at 1:34 PM, Masahiko Sawada  
>> wrote:
>>> On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat 
>>>  wrote:
> My understanding is that basically the local server can not return
> COMMIT to the client until 2nd phase is completed.

 If we do that, the local server may not return to the client at all,
 if the foreign server crashes and never comes up. Practically, it may
 take much longer to finish a COMMIT, depending upon how long it takes
 for the foreign server to reply to a COMMIT message.
>>>
>>> Yes, I think 2PC behaves so, please refer to [1].
>>> To prevent local server stops forever due to communication failure.,
>>> we could provide the timeout on coordinator side or on participant
>>> side.
>>
>> This too, looks like a heuristic and shouldn't be the default
>> behaviour and hence not part of the first version of this feature.
>
> At any rate, the coordinator should not return to the client until after
> the 2nd phase is completed, which was the original point.  If COMMIT
> taking longer is an issue, then it could be handled with one of the
> approaches mentioned so far (even if not in the first version), but no
> version of this feature should really return COMMIT to the client only
> after finishing the first phase.  Am I missing something?

There is small time window between actual COMMIT and a commit message
returned. An actual commit happens when we insert a WAL saying
transaction X committed and then we return to the client saying a
COMMIT happened. Note that a transaction may be committed but we will
never return to the client with a commit message, because connection
was lost or the server crashed. I hope we agree on this.

COMMITTING the foreign prepared transactions happens after we COMMIT
the local transaction. If we do it before COMMITTING local transaction
and the local server crashes, we will roll back local transaction
during subsequence recovery while the foreign segments have committed
resulting in an inconsistent state.

If we are successful in COMMITTING foreign transactions during
post-commit phase, COMMIT message will be returned after we have
committed all foreign transactions. But in case we can not reach a
foreign server, and request times out, we can not revert back our
decision that we are going to commit the transaction. That's my answer
to the timeout based heuristic.

I don't see much point in holding up post-commit processing for a
non-responsive foreign server, which may not respond for days
together. Can you please elaborate a use case? Which commercial
transaction manager does that?

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Amit Langote]

On 2016/10/06 17:45, Ashutosh Bapat wrote:
> On Thu, Oct 6, 2016 at 1:34 PM, Masahiko Sawada  wrote:
>> On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat 
>>  wrote:
 My understanding is that basically the local server can not return
 COMMIT to the client until 2nd phase is completed.
>>>
>>> If we do that, the local server may not return to the client at all,
>>> if the foreign server crashes and never comes up. Practically, it may
>>> take much longer to finish a COMMIT, depending upon how long it takes
>>> for the foreign server to reply to a COMMIT message.
>>
>> Yes, I think 2PC behaves so, please refer to [1].
>> To prevent local server stops forever due to communication failure.,
>> we could provide the timeout on coordinator side or on participant
>> side.
> 
> This too, looks like a heuristic and shouldn't be the default
> behaviour and hence not part of the first version of this feature.

At any rate, the coordinator should not return to the client until after
the 2nd phase is completed, which was the original point.  If COMMIT
taking longer is an issue, then it could be handled with one of the
approaches mentioned so far (even if not in the first version), but no
version of this feature should really return COMMIT to the client only
after finishing the first phase.  Am I missing something?

I am saying this because I am assuming that this feature means the client
itself does not invoke 2PC, even knowing that there are multiple servers
involved, but rather rely on the involved FDW drivers and related core
code handling it transparently.  I may have misunderstood the feature
though, apologies if so.

Thanks,
Amit




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Thu, Oct 6, 2016 at 1:34 PM, Masahiko Sawada  wrote:
> On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat
>  wrote:

 No, the COMMIT returns after the first phase. It can not wait for all
 the foreign servers to complete their second phase
>>>
>>> Hm, it sounds like it's same as normal commit (not 2PC).
>>> What's the difference?
>>>
>>> My understanding is that basically the local server can not return
>>> COMMIT to the client until 2nd phase is completed.
>>
>>
>> If we do that, the local server may not return to the client at all,
>> if the foreign server crashes and never comes up. Practically, it may
>> take much longer to finish a COMMIT, depending upon how long it takes
>> for the foreign server to reply to a COMMIT message.
>
> Yes, I think 2PC behaves so, please refer to [1].
> To prevent local server stops forever due to communication failure.,
> we could provide the timeout on coordinator side or on participant
> side.
>

This too, looks like a heuristic and shouldn't be the default
behaviour and hence not part of the first version of this feature.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Thu, Oct 6, 2016 at 1:41 PM, Ashutosh Bapat
 wrote:
>>>
>>> No, the COMMIT returns after the first phase. It can not wait for all
>>> the foreign servers to complete their second phase
>>
>> Hm, it sounds like it's same as normal commit (not 2PC).
>> What's the difference?
>>
>> My understanding is that basically the local server can not return
>> COMMIT to the client until 2nd phase is completed.
>
>
> If we do that, the local server may not return to the client at all,
> if the foreign server crashes and never comes up. Practically, it may
> take much longer to finish a COMMIT, depending upon how long it takes
> for the foreign server to reply to a COMMIT message.

Yes, I think 2PC behaves so, please refer to [1].
To prevent local server stops forever due to communication failure.,
we could provide the timeout on coordinator side or on participant
side.

>
>> Otherwise the next transaction can see data that is not committed yet
>> on remote server.
>
> 2PC doesn't guarantee transactional consistency all by itself. It only
> guarantees that all legs of a distributed transaction are either all
> rolled back or all committed. IOW, it guarantees that a distributed
> transaction is not rolled back on some nodes and committed on the
> other node.
> Providing a transactionally consistent view is a very hard problem.
> Trying to solve all those problems in a single patch would be very
> difficult and the amount of changes required may be really huge. Then
> there are many possible consistency definitions when it comes to
> consistency of distributed system. I have not seen a consensus on what
> kind of consistency model/s we want to support in PostgreSQL. That's
> another large debate. We have had previous attempts where people have
> tried to complete everything in one go and nothing has been completed
> yet.

Yes, providing a atomic visibility is hard problem, and it's a
separated issue[2].

> 2PC implementation OR guaranteeing that all the legs of a transaction
> commit or roll back, is an essential block of any kind of distributed
> transaction manager. So, we should at least support that one, before
> attacking further problems.

I agree.

[1]https://en.wikipedia.org/wiki/Two-phase_commit_protocol
[2]http://www.bailis.org/papers/ramp-sigmod2014.pdf

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

>>
>> No, the COMMIT returns after the first phase. It can not wait for all
>> the foreign servers to complete their second phase
>
> Hm, it sounds like it's same as normal commit (not 2PC).
> What's the difference?
>
> My understanding is that basically the local server can not return
> COMMIT to the client until 2nd phase is completed.


If we do that, the local server may not return to the client at all,
if the foreign server crashes and never comes up. Practically, it may
take much longer to finish a COMMIT, depending upon how long it takes
for the foreign server to reply to a COMMIT message. I don't think
that's desirable.

> Otherwise the next transaction can see data that is not committed yet
> on remote server.

2PC doesn't guarantee transactional consistency all by itself. It only
guarantees that all legs of a distributed transaction are either all
rolled back or all committed. IOW, it guarantees that a distributed
transaction is not rolled back on some nodes and committed on the
other node.

Providing a transactionally consistent view is a very hard problem.
Trying to solve all those problems in a single patch would be very
difficult and the amount of changes required may be really huge. Then
there are many possible consistency definitions when it comes to
consistency of distributed system. I have not seen a consensus on what
kind of consistency model/s we want to support in PostgreSQL. That's
another large debate. We have had previous attempts where people have
tried to complete everything in one go and nothing has been completed
yet.

2PC implementation OR guaranteeing that all the legs of a transaction
commit or roll back, is an essential block of any kind of distributed
transaction manager. So, we should at least support that one, before
attacking further problems.
-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Tue, Oct 4, 2016 at 8:29 PM, Ashutosh Bapat
 wrote:
> On Tue, Oct 4, 2016 at 1:11 PM, Amit Langote
>  wrote:
>> On 2016/10/04 16:10, Ashutosh Bapat wrote:
> Heuristics can not become the default behavior. A user should be given
> an option to choose a heuristic, and he should be aware of the
> pitfalls when using this heuristic. I guess, first, we need to get a
> solution which ensures that the transaction gets committed on all the
> servers or is rolled back on all the foreign servers involved. AFAIR,
> my patch did that. Once we have that kind of solution, we can think
> about heuristics.

 I meant that we could determine it heuristically only when remote server
 crashed in 2nd phase of 2PC.
 For example, what does the local server returns to client when no one 
 remote
 server returns OK to local server in 2nd phase of 2PC for more than
 statement_timeout seconds? Ok or error?

>>>
>>> The local server doesn't wait for the completion of the second phase
>>> to finish the currently running statement. Once all the foreign
>>> servers have responded to PREPARE request in the first phase, the
>>> local server responds to the client. Am I missing something?
>>
>> PREPARE sent to foreign servers involved in a given transaction is
>> *transparent* to the user who started the transaction, no?  That is, user
>> just says COMMIT and if it is found that there are multiple servers
>> involved in the transaction, it must be handled using two-phase commit
>> protocol *behind the scenes*.  So the aforementioned COMMIT should not
>> return to the client until after the above two-phase commit processing has
>> finished.
>
> No, the COMMIT returns after the first phase. It can not wait for all
> the foreign servers to complete their second phase

Hm, it sounds like it's same as normal commit (not 2PC).
What's the difference?

My understanding is that basically the local server can not return
COMMIT to the client until 2nd phase is completed.
Otherwise the next transaction can see data that is not committed yet
on remote server.

> , which can take
> quite long (or never) if one of the servers has crashed in between.
>
>>
>> Or are you and Sawada-san talking about the case where the user issued
>> PREPARE and not COMMIT?
>
> I guess, Sawada-san is still talking about the user issued PREPARE.
> But my comment is applicable otherwise as well.
>

Yes, I'm considering the case where the local server tries to COMMIT
but the remote server crashed after the local server completes 1st
phase (PREPARE) on the all remote server.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Tue, Oct 4, 2016 at 1:11 PM, Amit Langote
 wrote:
> On 2016/10/04 16:10, Ashutosh Bapat wrote:
 Heuristics can not become the default behavior. A user should be given
 an option to choose a heuristic, and he should be aware of the
 pitfalls when using this heuristic. I guess, first, we need to get a
 solution which ensures that the transaction gets committed on all the
 servers or is rolled back on all the foreign servers involved. AFAIR,
 my patch did that. Once we have that kind of solution, we can think
 about heuristics.
>>>
>>> I meant that we could determine it heuristically only when remote server
>>> crashed in 2nd phase of 2PC.
>>> For example, what does the local server returns to client when no one remote
>>> server returns OK to local server in 2nd phase of 2PC for more than
>>> statement_timeout seconds? Ok or error?
>>>
>>
>> The local server doesn't wait for the completion of the second phase
>> to finish the currently running statement. Once all the foreign
>> servers have responded to PREPARE request in the first phase, the
>> local server responds to the client. Am I missing something?
>
> PREPARE sent to foreign servers involved in a given transaction is
> *transparent* to the user who started the transaction, no?  That is, user
> just says COMMIT and if it is found that there are multiple servers
> involved in the transaction, it must be handled using two-phase commit
> protocol *behind the scenes*.  So the aforementioned COMMIT should not
> return to the client until after the above two-phase commit processing has
> finished.

No, the COMMIT returns after the first phase. It can not wait for all
the foreign servers to complete their second phase, which can take
quite long (or never) if one of the servers has crashed in between.

>
> Or are you and Sawada-san talking about the case where the user issued
> PREPARE and not COMMIT?

I guess, Sawada-san is still talking about the user issued PREPARE.
But my comment is applicable otherwise as well.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Amit Langote]

On 2016/10/04 16:10, Ashutosh Bapat wrote:
>>> Heuristics can not become the default behavior. A user should be given
>>> an option to choose a heuristic, and he should be aware of the
>>> pitfalls when using this heuristic. I guess, first, we need to get a
>>> solution which ensures that the transaction gets committed on all the
>>> servers or is rolled back on all the foreign servers involved. AFAIR,
>>> my patch did that. Once we have that kind of solution, we can think
>>> about heuristics.
>>
>> I meant that we could determine it heuristically only when remote server
>> crashed in 2nd phase of 2PC.
>> For example, what does the local server returns to client when no one remote
>> server returns OK to local server in 2nd phase of 2PC for more than
>> statement_timeout seconds? Ok or error?
>>
> 
> The local server doesn't wait for the completion of the second phase
> to finish the currently running statement. Once all the foreign
> servers have responded to PREPARE request in the first phase, the
> local server responds to the client. Am I missing something?

PREPARE sent to foreign servers involved in a given transaction is
*transparent* to the user who started the transaction, no?  That is, user
just says COMMIT and if it is found that there are multiple servers
involved in the transaction, it must be handled using two-phase commit
protocol *behind the scenes*.  So the aforementioned COMMIT should not
return to the client until after the above two-phase commit processing has
finished.

Or are you and Sawada-san talking about the case where the user issued
PREPARE and not COMMIT?

Thanks,
Amit




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

>>
>> Heuristics can not become the default behavior. A user should be given
>> an option to choose a heuristic, and he should be aware of the
>> pitfalls when using this heuristic. I guess, first, we need to get a
>> solution which ensures that the transaction gets committed on all the
>> servers or is rolled back on all the foreign servers involved. AFAIR,
>> my patch did that. Once we have that kind of solution, we can think
>> about heuristics.
>
> I meant that we could determine it heuristically only when remote server
> crashed in 2nd phase of 2PC.
> For example, what does the local server returns to client when no one remote
> server returns OK to local server in 2nd phase of 2PC for more than
> statement_timeout seconds? Ok or error?
>

The local server doesn't wait for the completion of the second phase
to finish the currently running statement. Once all the foreign
servers have responded to PREPARE request in the first phase, the
local server responds to the client. Am I missing something?


>>
>> There will be many such XIDs. We don't want to dump so many things in
>> control file, esp. when that's not control data. System catalog is out
>> of question since a rollback of local transaction would make those
>> rows in the system catalog invisible. That's the reason, why I chose
>> to write the foreign prepared transactions to files rather than a
>> system catalog.
>>
>
> We can store the lowest in-doubt transaction id (say in-doubt XID) that
> needs to be resolved later into control file and the CLOG containing XID
> greater than in-doubt XID is never truncated.
> We need to try to solve such transaction only when in-doubt XID is not NULL.
>
IIRC, my patch takes care of this. If the oldest active transaction
happens to be later in the time line than the oldest in-doubt
transaction, it sets oldest active transaction id to that of the
oldest in-doubt transaction.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Tue, Oct 4, 2016 at 1:26 PM, Ashutosh Bapat <
ashutosh.ba...@enterprisedb.com > wrote:
>>>
>>> Why always rollback any dangling transaction? There can be a case that
>>> a foreign server has a dangling transaction which needs to be
>>> committed because the portions of that transaction on the other shards
>>> are committed.
>>
>> Right, we can heuristically make a decision whether we do COMMIT or
>> ABORT on local server.
>> For example, if COMMIT PREPARED succeeded on at least one foreign
>> server, the local server return OK to client and the other dangling
>> transactions should be committed later.
>> We can find out that we should do either commit or abort the dangling
>> transaction by checking CLOG.
>
> Heuristics can not become the default behavior. A user should be given
> an option to choose a heuristic, and he should be aware of the
> pitfalls when using this heuristic. I guess, first, we need to get a
> solution which ensures that the transaction gets committed on all the
> servers or is rolled back on all the foreign servers involved. AFAIR,
> my patch did that. Once we have that kind of solution, we can think
> about heuristics.

I meant that we could determine it heuristically only when remote server
crashed in 2nd phase of 2PC.
For example, what does the local server returns to client when no one
remote server returns OK to local server in 2nd phase of 2PC for more than
statement_timeout seconds? Ok or error?

>>
>> But we need to handle the case where the CLOG file containing XID
>> necessary for resolving dangling transaction is truncated.
>> If the user does VACUUM FREEZE just after remote server crashed, it
>> could be truncated.
>
> Hmm, this needs to be fixed. Even my patch relied on XID to determine
> whether the transaction committed or rolled back locally and thus to
> decide whether it should be committed or rolled back on all the
> foreign servers involved. I think I had taken care of the issue you
> have pointed out here. Can you please verify the same?
>
>>
>>> The way gid is crafted, there is no way to check whether the given
>>> prepared transaction was created by the local server or not. Probably
>>> the local server needs to add a unique signature in GID to identify
>>> the transactions prepared by itself. That signature should be
>>> transferred to standby to cope up with the fail-over of local server.
>>
>> Maybe we can use database system identifier in control file.
>
> may be.
>
>>
>>> In this idea, one has to keep on polling the foreign server to find
>>> any dangling transactions. In usual scenario, we shouldn't have a
>>> large number of dangling transactions, and thus periodic polling might
>>> be a waste.
>>
>> We can optimize it by storing the XID that is resolved heuristically
>> into the control file or system catalog, for example.
>>
>
> There will be many such XIDs. We don't want to dump so many things in
> control file, esp. when that's not control data. System catalog is out
> of question since a rollback of local transaction would make those
> rows in the system catalog invisible. That's the reason, why I chose
> to write the foreign prepared transactions to files rather than a
> system catalog.
>

We can store the lowest in-doubt transaction id (say in-doubt XID) that
needs to be resolved later into control file and the CLOG containing XID
greater than in-doubt XID is never truncated.
We need to try to solve such transaction only when in-doubt XID is not NULL.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Amit Langote]

Hi,

On 2016/10/04 13:26, Ashutosh Bapat wrote:
>>>
>>> Why always rollback any dangling transaction? There can be a case that
>>> a foreign server has a dangling transaction which needs to be
>>> committed because the portions of that transaction on the other shards
>>> are committed.
>>
>> Right, we can heuristically make a decision whether we do COMMIT or
>> ABORT on local server.
>> For example, if COMMIT PREPARED succeeded on at least one foreign
>> server, the local server return OK to client and the other dangling
>> transactions should be committed later.
>> We can find out that we should do either commit or abort the dangling
>> transaction by checking CLOG.
> 
> Heuristics can not become the default behavior. A user should be given
> an option to choose a heuristic, and he should be aware of the
> pitfalls when using this heuristic. I guess, first, we need to get a
> solution which ensures that the transaction gets committed on all the
> servers or is rolled back on all the foreign servers involved. AFAIR,
> my patch did that. Once we have that kind of solution, we can think
> about heuristics.

I wonder if Sawada-san is referring to some sort of quorum-based (atomic)
commitment protocol [1, 2], although I agree that that would be an
advanced technique for handling the limitations such as blocking nature of
the basic two-phase commit protocol in case of communication failures,
IOW, meant for better availability rather than correctness.

Thanks,
Amit

[1]
https://en.wikipedia.org/wiki/Quorum_(distributed_computing)#Quorum-based_voting_in_commit_protocols

[2] http://hub.hku.hk/bitstream/10722/158032/1/Content.pdf




-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

>>
>> Why always rollback any dangling transaction? There can be a case that
>> a foreign server has a dangling transaction which needs to be
>> committed because the portions of that transaction on the other shards
>> are committed.
>
> Right, we can heuristically make a decision whether we do COMMIT or
> ABORT on local server.
> For example, if COMMIT PREPARED succeeded on at least one foreign
> server, the local server return OK to client and the other dangling
> transactions should be committed later.
> We can find out that we should do either commit or abort the dangling
> transaction by checking CLOG.

Heuristics can not become the default behavior. A user should be given
an option to choose a heuristic, and he should be aware of the
pitfalls when using this heuristic. I guess, first, we need to get a
solution which ensures that the transaction gets committed on all the
servers or is rolled back on all the foreign servers involved. AFAIR,
my patch did that. Once we have that kind of solution, we can think
about heuristics.

>
> But we need to handle the case where the CLOG file containing XID
> necessary for resolving dangling transaction is truncated.
> If the user does VACUUM FREEZE just after remote server crashed, it
> could be truncated.

Hmm, this needs to be fixed. Even my patch relied on XID to determine
whether the transaction committed or rolled back locally and thus to
decide whether it should be committed or rolled back on all the
foreign servers involved. I think I had taken care of the issue you
have pointed out here. Can you please verify the same?

>
>> The way gid is crafted, there is no way to check whether the given
>> prepared transaction was created by the local server or not. Probably
>> the local server needs to add a unique signature in GID to identify
>> the transactions prepared by itself. That signature should be
>> transferred to standby to cope up with the fail-over of local server.
>
> Maybe we can use database system identifier in control file.

may be.

>
>> In this idea, one has to keep on polling the foreign server to find
>> any dangling transactions. In usual scenario, we shouldn't have a
>> large number of dangling transactions, and thus periodic polling might
>> be a waste.
>
> We can optimize it by storing the XID that is resolved heuristically
> into the control file or system catalog, for example.
>

There will be many such XIDs. We don't want to dump so many things in
control file, esp. when that's not control data. System catalog is out
of question since a rollback of local transaction would make those
rows in the system catalog invisible. That's the reason, why I chose
to write the foreign prepared transactions to files rather than a
system catalog.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Wed, Sep 28, 2016 at 3:30 PM, Ashutosh Bapat
 wrote:
> On Wed, Sep 28, 2016 at 10:43 AM, Masahiko Sawada  
> wrote:
>> On Tue, Sep 27, 2016 at 9:06 PM, Ashutosh Bapat
>>  wrote:
>>> On Tue, Sep 27, 2016 at 2:54 PM, Masahiko Sawada  
>>> wrote:
 On Mon, Sep 26, 2016 at 9:07 PM, Ashutosh Bapat
  wrote:
> On Mon, Sep 26, 2016 at 5:25 PM, Masahiko Sawada  
> wrote:
>> On Mon, Sep 26, 2016 at 7:28 PM, Ashutosh Bapat
>>  wrote:
>>> My original patch added code to manage the files for 2 phase
>>> transactions opened by the local server on the remote servers. This
>>> code was mostly inspired from the code in twophase.c which manages the
>>> file for prepared transactions. The logic to manage 2PC files has
>>> changed since [1] and has been optimized. One of the things I wanted
>>> to do is see, if those optimizations are applicable here as well. Have
>>> you considered that?
>>>
>>>
>>
>> Yeah, we're considering it.
>> After these changes are committed, we will post the patch incorporated
>> these changes.
>>
>> But what we need to do first is the discussion in order to get consensus.
>> Since current design of this patch is to transparently execute DCL of
>> 2PC on foreign server, this code changes lot of code and is
>> complicated.
>
> Can you please elaborate. I am not able to understand what DCL is
> involved here. According to [1], examples of DCL are GRANT and REVOKE
> command.

 I meant transaction management command such as PREPARE TRANSACTION and
 COMMIT/ABORT PREPARED command.
 The web page I refered might be wrong, sorry.

>> Another approach I have is to push down DCL to only foreign servers
>> that support 2PC protocol, which is similar to DML push down.
>> This approach would be more simpler than current idea and is easy to
>> use by distributed transaction manager.
>
> Again, can you please elaborate, how that would be different from the
> current approach and how does it simplify the code.
>

 The idea is just to push down PREPARE TRANSACTION, COMMIT/ROLLBACK
 PREPARED to foreign servers that support 2PC.
 With this idea, the client need to do following operation when foreign
 server is involved with transaction.

 BEGIN;
 UPDATE parent_table SET ...; -- update including foreign server
 PREPARE TRANSACTION 'xact_id';
 COMMIT PREPARED 'xact_id';

 The above PREPARE TRANSACTION and COMMIT PREPARED command are pushed
 down to foreign server.
 That is, the client needs to execute PREPARE TRANSACTION and

 In this idea, I think that we don't need to do followings,

 * Providing the prepare id of 2PC.
   Current patch adds new API prepare_id_provider() but we can use the
 prepare id of 2PC that is used on parent server.

 * Keeping track of status of foreign servers.
   Current patch keeps track of status of foreign servers involved with
 transaction but this idea is just to push down transaction management
 command to foreign server.
   So I think that we no longer need to do that.
>>>
 COMMIT/ROLLBACK PREPARED explicitly.
>>>
>>> The problem with this approach is same as one previously stated. If
>>> the connection between local and foreign server is lost between
>>> PREPARE and COMMIT the prepared transaction on the foreign server
>>> remains dangling, none other than the local server knows what to do
>>> with it and the local server has lost track of the prepared
>>> transaction on the foreign server. So, just pushing down those
>>> commands doesn't work.
>>
>> Yeah, my idea is one of the first step.
>> Mechanism that resolves the dangling foreign transaction and the
>> resolver worker process are necessary.
>>

 * Adding max_prepared_foreign_transactions parameter.
   It means that the number of transaction involving foreign server is
 the same as max_prepared_transactions.

>>>
>>> That isn't true exactly. max_prepared_foreign_transactions indicates
>>> how many transactions can be prepared on the foreign server, which in
>>> the method you propose should have a cap of max_prepared_transactions
>>> * number of foreign servers.
>>
>> Oh, I understood, thanks.
>>
>> Consider sharding solution using postgres_fdw (that is, the parent
>> postgres server has multiple shard postgres servers), we need to
>> increase max_prepared_foreign_transactions whenever new shard server
>> is added to cluster, or to allocate enough size in advance. But the
>> estimation of enough max_prepared_foreign_transactions would not be
>> easy, for example can we estimate it by (max throughput of the system)
>> * (the number of foreign 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Michael Paquier]

On Wed, Sep 28, 2016 at 3:30 PM, Ashutosh Bapat
 wrote:
> I agree, but we need to cope with above two problems.

I have marked the patch as returned with feedback per the last output
Ashutosh has provided.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Wed, Sep 28, 2016 at 10:43 AM, Masahiko Sawada  wrote:
> On Tue, Sep 27, 2016 at 9:06 PM, Ashutosh Bapat
>  wrote:
>> On Tue, Sep 27, 2016 at 2:54 PM, Masahiko Sawada  
>> wrote:
>>> On Mon, Sep 26, 2016 at 9:07 PM, Ashutosh Bapat
>>>  wrote:
 On Mon, Sep 26, 2016 at 5:25 PM, Masahiko Sawada  
 wrote:
> On Mon, Sep 26, 2016 at 7:28 PM, Ashutosh Bapat
>  wrote:
>> My original patch added code to manage the files for 2 phase
>> transactions opened by the local server on the remote servers. This
>> code was mostly inspired from the code in twophase.c which manages the
>> file for prepared transactions. The logic to manage 2PC files has
>> changed since [1] and has been optimized. One of the things I wanted
>> to do is see, if those optimizations are applicable here as well. Have
>> you considered that?
>>
>>
>
> Yeah, we're considering it.
> After these changes are committed, we will post the patch incorporated
> these changes.
>
> But what we need to do first is the discussion in order to get consensus.
> Since current design of this patch is to transparently execute DCL of
> 2PC on foreign server, this code changes lot of code and is
> complicated.

 Can you please elaborate. I am not able to understand what DCL is
 involved here. According to [1], examples of DCL are GRANT and REVOKE
 command.
>>>
>>> I meant transaction management command such as PREPARE TRANSACTION and
>>> COMMIT/ABORT PREPARED command.
>>> The web page I refered might be wrong, sorry.
>>>
> Another approach I have is to push down DCL to only foreign servers
> that support 2PC protocol, which is similar to DML push down.
> This approach would be more simpler than current idea and is easy to
> use by distributed transaction manager.

 Again, can you please elaborate, how that would be different from the
 current approach and how does it simplify the code.

>>>
>>> The idea is just to push down PREPARE TRANSACTION, COMMIT/ROLLBACK
>>> PREPARED to foreign servers that support 2PC.
>>> With this idea, the client need to do following operation when foreign
>>> server is involved with transaction.
>>>
>>> BEGIN;
>>> UPDATE parent_table SET ...; -- update including foreign server
>>> PREPARE TRANSACTION 'xact_id';
>>> COMMIT PREPARED 'xact_id';
>>>
>>> The above PREPARE TRANSACTION and COMMIT PREPARED command are pushed
>>> down to foreign server.
>>> That is, the client needs to execute PREPARE TRANSACTION and
>>>
>>> In this idea, I think that we don't need to do followings,
>>>
>>> * Providing the prepare id of 2PC.
>>>   Current patch adds new API prepare_id_provider() but we can use the
>>> prepare id of 2PC that is used on parent server.
>>>
>>> * Keeping track of status of foreign servers.
>>>   Current patch keeps track of status of foreign servers involved with
>>> transaction but this idea is just to push down transaction management
>>> command to foreign server.
>>>   So I think that we no longer need to do that.
>>
>>> COMMIT/ROLLBACK PREPARED explicitly.
>>
>> The problem with this approach is same as one previously stated. If
>> the connection between local and foreign server is lost between
>> PREPARE and COMMIT the prepared transaction on the foreign server
>> remains dangling, none other than the local server knows what to do
>> with it and the local server has lost track of the prepared
>> transaction on the foreign server. So, just pushing down those
>> commands doesn't work.
>
> Yeah, my idea is one of the first step.
> Mechanism that resolves the dangling foreign transaction and the
> resolver worker process are necessary.
>
>>>
>>> * Adding max_prepared_foreign_transactions parameter.
>>>   It means that the number of transaction involving foreign server is
>>> the same as max_prepared_transactions.
>>>
>>
>> That isn't true exactly. max_prepared_foreign_transactions indicates
>> how many transactions can be prepared on the foreign server, which in
>> the method you propose should have a cap of max_prepared_transactions
>> * number of foreign servers.
>
> Oh, I understood, thanks.
>
> Consider sharding solution using postgres_fdw (that is, the parent
> postgres server has multiple shard postgres servers), we need to
> increase max_prepared_foreign_transactions whenever new shard server
> is added to cluster, or to allocate enough size in advance. But the
> estimation of enough max_prepared_foreign_transactions would not be
> easy, for example can we estimate it by (max throughput of the system)
> * (the number of foreign servers)?
>
> One new idea I came up with is that we set transaction id on parent
> server to global transaction id (gid) that is prepared on shard
> server.
> And pg_fdw_resolver worker process 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Tue, Sep 27, 2016 at 9:06 PM, Ashutosh Bapat
 wrote:
> On Tue, Sep 27, 2016 at 2:54 PM, Masahiko Sawada  
> wrote:
>> On Mon, Sep 26, 2016 at 9:07 PM, Ashutosh Bapat
>>  wrote:
>>> On Mon, Sep 26, 2016 at 5:25 PM, Masahiko Sawada  
>>> wrote:
 On Mon, Sep 26, 2016 at 7:28 PM, Ashutosh Bapat
  wrote:
> My original patch added code to manage the files for 2 phase
> transactions opened by the local server on the remote servers. This
> code was mostly inspired from the code in twophase.c which manages the
> file for prepared transactions. The logic to manage 2PC files has
> changed since [1] and has been optimized. One of the things I wanted
> to do is see, if those optimizations are applicable here as well. Have
> you considered that?
>
>

 Yeah, we're considering it.
 After these changes are committed, we will post the patch incorporated
 these changes.

 But what we need to do first is the discussion in order to get consensus.
 Since current design of this patch is to transparently execute DCL of
 2PC on foreign server, this code changes lot of code and is
 complicated.
>>>
>>> Can you please elaborate. I am not able to understand what DCL is
>>> involved here. According to [1], examples of DCL are GRANT and REVOKE
>>> command.
>>
>> I meant transaction management command such as PREPARE TRANSACTION and
>> COMMIT/ABORT PREPARED command.
>> The web page I refered might be wrong, sorry.
>>
 Another approach I have is to push down DCL to only foreign servers
 that support 2PC protocol, which is similar to DML push down.
 This approach would be more simpler than current idea and is easy to
 use by distributed transaction manager.
>>>
>>> Again, can you please elaborate, how that would be different from the
>>> current approach and how does it simplify the code.
>>>
>>
>> The idea is just to push down PREPARE TRANSACTION, COMMIT/ROLLBACK
>> PREPARED to foreign servers that support 2PC.
>> With this idea, the client need to do following operation when foreign
>> server is involved with transaction.
>>
>> BEGIN;
>> UPDATE parent_table SET ...; -- update including foreign server
>> PREPARE TRANSACTION 'xact_id';
>> COMMIT PREPARED 'xact_id';
>>
>> The above PREPARE TRANSACTION and COMMIT PREPARED command are pushed
>> down to foreign server.
>> That is, the client needs to execute PREPARE TRANSACTION and
>>
>> In this idea, I think that we don't need to do followings,
>>
>> * Providing the prepare id of 2PC.
>>   Current patch adds new API prepare_id_provider() but we can use the
>> prepare id of 2PC that is used on parent server.
>>
>> * Keeping track of status of foreign servers.
>>   Current patch keeps track of status of foreign servers involved with
>> transaction but this idea is just to push down transaction management
>> command to foreign server.
>>   So I think that we no longer need to do that.
>
>> COMMIT/ROLLBACK PREPARED explicitly.
>
> The problem with this approach is same as one previously stated. If
> the connection between local and foreign server is lost between
> PREPARE and COMMIT the prepared transaction on the foreign server
> remains dangling, none other than the local server knows what to do
> with it and the local server has lost track of the prepared
> transaction on the foreign server. So, just pushing down those
> commands doesn't work.

Yeah, my idea is one of the first step.
Mechanism that resolves the dangling foreign transaction and the
resolver worker process are necessary.

>>
>> * Adding max_prepared_foreign_transactions parameter.
>>   It means that the number of transaction involving foreign server is
>> the same as max_prepared_transactions.
>>
>
> That isn't true exactly. max_prepared_foreign_transactions indicates
> how many transactions can be prepared on the foreign server, which in
> the method you propose should have a cap of max_prepared_transactions
> * number of foreign servers.

Oh, I understood, thanks.

Consider sharding solution using postgres_fdw (that is, the parent
postgres server has multiple shard postgres servers), we need to
increase max_prepared_foreign_transactions whenever new shard server
is added to cluster, or to allocate enough size in advance. But the
estimation of enough max_prepared_foreign_transactions would not be
easy, for example can we estimate it by (max throughput of the system)
* (the number of foreign servers)?

One new idea I came up with is that we set transaction id on parent
server to global transaction id (gid) that is prepared on shard
server.
And pg_fdw_resolver worker process periodically resolves the dangling
transaction on foreign server by comparing active lowest XID on parent
server with the XID in gid used by PREPARE TRANSACTION.

For example, suppose that there are one parent server 

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Michael Paquier]

On Tue, Sep 27, 2016 at 6:24 PM, Masahiko Sawada  wrote:
> * Providing the prepare id of 2PC.
>   Current patch adds new API prepare_id_provider() but we can use the
> prepare id of 2PC that is used on parent server.

And we assume that when this is used across many servers there will be
no GID conflict because each server is careful enough to generate
unique strings, say with UUIDs?
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Tue, Sep 27, 2016 at 2:54 PM, Masahiko Sawada  wrote:
> On Mon, Sep 26, 2016 at 9:07 PM, Ashutosh Bapat
>  wrote:
>> On Mon, Sep 26, 2016 at 5:25 PM, Masahiko Sawada  
>> wrote:
>>> On Mon, Sep 26, 2016 at 7:28 PM, Ashutosh Bapat
>>>  wrote:
 My original patch added code to manage the files for 2 phase
 transactions opened by the local server on the remote servers. This
 code was mostly inspired from the code in twophase.c which manages the
 file for prepared transactions. The logic to manage 2PC files has
 changed since [1] and has been optimized. One of the things I wanted
 to do is see, if those optimizations are applicable here as well. Have
 you considered that?


>>>
>>> Yeah, we're considering it.
>>> After these changes are committed, we will post the patch incorporated
>>> these changes.
>>>
>>> But what we need to do first is the discussion in order to get consensus.
>>> Since current design of this patch is to transparently execute DCL of
>>> 2PC on foreign server, this code changes lot of code and is
>>> complicated.
>>
>> Can you please elaborate. I am not able to understand what DCL is
>> involved here. According to [1], examples of DCL are GRANT and REVOKE
>> command.
>
> I meant transaction management command such as PREPARE TRANSACTION and
> COMMIT/ABORT PREPARED command.
> The web page I refered might be wrong, sorry.
>
>>> Another approach I have is to push down DCL to only foreign servers
>>> that support 2PC protocol, which is similar to DML push down.
>>> This approach would be more simpler than current idea and is easy to
>>> use by distributed transaction manager.
>>
>> Again, can you please elaborate, how that would be different from the
>> current approach and how does it simplify the code.
>>
>
> The idea is just to push down PREPARE TRANSACTION, COMMIT/ROLLBACK
> PREPARED to foreign servers that support 2PC.
> With this idea, the client need to do following operation when foreign
> server is involved with transaction.
>
> BEGIN;
> UPDATE parent_table SET ...; -- update including foreign server
> PREPARE TRANSACTION 'xact_id';
> COMMIT PREPARED 'xact_id';
>
> The above PREPARE TRANSACTION and COMMIT PREPARED command are pushed
> down to foreign server.
> That is, the client needs to execute PREPARE TRANSACTION and
>
> In this idea, I think that we don't need to do followings,
>
> * Providing the prepare id of 2PC.
>   Current patch adds new API prepare_id_provider() but we can use the
> prepare id of 2PC that is used on parent server.
>
> * Keeping track of status of foreign servers.
>   Current patch keeps track of status of foreign servers involved with
> transaction but this idea is just to push down transaction management
> command to foreign server.
>   So I think that we no longer need to do that.

> COMMIT/ROLLBACK PREPARED explicitly.

The problem with this approach is same as one previously stated. If
the connection between local and foreign server is lost between
PREPARE and COMMIT the prepared transaction on the foreign server
remains dangling, none other than the local server knows what to do
with it and the local server has lost track of the prepared
transaction on the foreign server. So, just pushing down those
commands doesn't work.

>
> * Adding max_prepared_foreign_transactions parameter.
>   It means that the number of transaction involving foreign server is
> the same as max_prepared_transactions.
>

That isn't true exactly. max_prepared_foreign_transactions indicates
how many transactions can be prepared on the foreign server, which in
the method you propose should have a cap of max_prepared_transactions
* number of foreign servers.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Mon, Sep 26, 2016 at 9:07 PM, Ashutosh Bapat
 wrote:
> On Mon, Sep 26, 2016 at 5:25 PM, Masahiko Sawada  
> wrote:
>> On Mon, Sep 26, 2016 at 7:28 PM, Ashutosh Bapat
>>  wrote:
>>> My original patch added code to manage the files for 2 phase
>>> transactions opened by the local server on the remote servers. This
>>> code was mostly inspired from the code in twophase.c which manages the
>>> file for prepared transactions. The logic to manage 2PC files has
>>> changed since [1] and has been optimized. One of the things I wanted
>>> to do is see, if those optimizations are applicable here as well. Have
>>> you considered that?
>>>
>>>
>>
>> Yeah, we're considering it.
>> After these changes are committed, we will post the patch incorporated
>> these changes.
>>
>> But what we need to do first is the discussion in order to get consensus.
>> Since current design of this patch is to transparently execute DCL of
>> 2PC on foreign server, this code changes lot of code and is
>> complicated.
>
> Can you please elaborate. I am not able to understand what DCL is
> involved here. According to [1], examples of DCL are GRANT and REVOKE
> command.

I meant transaction management command such as PREPARE TRANSACTION and
COMMIT/ABORT PREPARED command.
The web page I refered might be wrong, sorry.

>> Another approach I have is to push down DCL to only foreign servers
>> that support 2PC protocol, which is similar to DML push down.
>> This approach would be more simpler than current idea and is easy to
>> use by distributed transaction manager.
>
> Again, can you please elaborate, how that would be different from the
> current approach and how does it simplify the code.
>

The idea is just to push down PREPARE TRANSACTION, COMMIT/ROLLBACK
PREPARED to foreign servers that support 2PC.
With this idea, the client need to do following operation when foreign
server is involved with transaction.

BEGIN;
UPDATE parent_table SET ...; -- update including foreign server
PREPARE TRANSACTION 'xact_id';
COMMIT PREPARED 'xact_id';

The above PREPARE TRANSACTION and COMMIT PREPARED command are pushed
down to foreign server.
That is, the client needs to execute PREPARE TRANSACTION and
COMMIT/ROLLBACK PREPARED explicitly.

In this idea, I think that we don't need to do followings,

* Providing the prepare id of 2PC.
  Current patch adds new API prepare_id_provider() but we can use the
prepare id of 2PC that is used on parent server.

* Keeping track of status of foreign servers.
  Current patch keeps track of status of foreign servers involved with
transaction but this idea is just to push down transaction management
command to foreign server.
  So I think that we no longer need to do that.

* Adding max_prepared_foreign_transactions parameter.
  It means that the number of transaction involving foreign server is
the same as max_prepared_transactions.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Mon, Sep 26, 2016 at 5:25 PM, Masahiko Sawada  wrote:
> On Mon, Sep 26, 2016 at 7:28 PM, Ashutosh Bapat
>  wrote:
>> My original patch added code to manage the files for 2 phase
>> transactions opened by the local server on the remote servers. This
>> code was mostly inspired from the code in twophase.c which manages the
>> file for prepared transactions. The logic to manage 2PC files has
>> changed since [1] and has been optimized. One of the things I wanted
>> to do is see, if those optimizations are applicable here as well. Have
>> you considered that?
>>
>>
>
> Yeah, we're considering it.
> After these changes are committed, we will post the patch incorporated
> these changes.
>
> But what we need to do first is the discussion in order to get consensus.
> Since current design of this patch is to transparently execute DCL of
> 2PC on foreign server, this code changes lot of code and is
> complicated.

Can you please elaborate. I am not able to understand what DCL is
involved here. According to [1], examples of DCL are GRANT and REVOKE
command.

> Another approach I have is to push down DCL to only foreign servers
> that support 2PC protocol, which is similar to DML push down.
> This approach would be more simpler than current idea and is easy to
> use by distributed transaction manager.

Again, can you please elaborate, how that would be different from the
current approach and how does it simplify the code.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Mon, Sep 26, 2016 at 7:28 PM, Ashutosh Bapat
 wrote:
> My original patch added code to manage the files for 2 phase
> transactions opened by the local server on the remote servers. This
> code was mostly inspired from the code in twophase.c which manages the
> file for prepared transactions. The logic to manage 2PC files has
> changed since [1] and has been optimized. One of the things I wanted
> to do is see, if those optimizations are applicable here as well. Have
> you considered that?
>
>

Yeah, we're considering it.
After these changes are committed, we will post the patch incorporated
these changes.

But what we need to do first is the discussion in order to get consensus.
Since current design of this patch is to transparently execute DCL of
2PC on foreign server, this code changes lot of code and is
complicated.
Another approach I have is to push down DCL to only foreign servers
that support 2PC protocol, which is similar to DML push down.
This approach would be more simpler than current idea and is easy to
use by distributed transaction manager.
I think that it would be good place to start.

I'd like to discuss what the best approach is for transaction
involving foreign servers.

Regards,

--
Masahiko Sawada
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

My original patch added code to manage the files for 2 phase
transactions opened by the local server on the remote servers. This
code was mostly inspired from the code in twophase.c which manages the
file for prepared transactions. The logic to manage 2PC files has
changed since [1] and has been optimized. One of the things I wanted
to do is see, if those optimizations are applicable here as well. Have
you considered that?


[1]. 
https://www.postgresql.org/message-id/74355FCF-AADC-4E51-850B-47AF59E0B215%40postgrespro.ru

On Fri, Aug 26, 2016 at 11:43 AM, Ashutosh Bapat
 wrote:
>
>
> On Fri, Aug 26, 2016 at 11:37 AM, Masahiko Sawada 
> wrote:
>>
>> On Fri, Aug 26, 2016 at 3:03 PM, Ashutosh Bapat
>>  wrote:
>> >
>> >
>> > On Fri, Aug 26, 2016 at 11:22 AM, Masahiko Sawada
>> > 
>> > wrote:
>> >>
>> >> On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale 
>> >> wrote:
>> >> > Hi All,
>> >> >
>> >> > Ashutosh proposed the feature 2PC for FDW for achieving atomic
>> >> > commits
>> >> > across multiple foreign servers.
>> >> > If a transaction make changes to more than two foreign servers the
>> >> > current
>> >> > implementation in postgres_fdw doesn't make sure that either all of
>> >> > them
>> >> > commit or all of them rollback their changes.
>> >> >
>> >> > We (Masahiko Sawada and me) reopen this thread and trying to
>> >> > contribute
>> >> > in
>> >> > it.
>> >> >
>> >> > 2PC for FDW
>> >> > 
>> >> > The patch provides support for atomic commit for transactions
>> >> > involving
>> >> > foreign servers. when the transaction makes changes to foreign
>> >> > servers,
>> >> > either all the changes to all the foreign servers commit or rollback.
>> >> >
>> >> > The new patch 2PC for FDW include the following things:
>> >> > 1. The patch 0001 introduces a generic feature. All kinds of FDW that
>> >> > support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc. can
>> >> > involve
>> >> > in
>> >> > the transaction.
>> >> >
>> >> > Currently we can push some conditions down to shard nodes, especially
>> >> > in
>> >> > 9.6
>> >> > the directly modify feature has
>> >> > been introduced. But such a transaction modifying data on shard node
>> >> > is
>> >> > not
>> >> > executed surely.
>> >> > Using 0002 patch, that modify is executed with 2PC. It means that we
>> >> > almost
>> >> > can provide sharding solution using
>> >> > multiple PostgreSQL server (one parent node and several shared node).
>> >> >
>> >> > For multi master, we definitely need transaction manager but
>> >> > transaction
>> >> > manager probably can use this 2PC for FDW feature to manage
>> >> > distributed
>> >> > transaction.
>> >> >
>> >> > 2. 0002 patch makes postgres_fdw possible to use 2PC.
>> >> >
>> >> > 0002 patch makes postgres_fdw to use below APIs. These APIs are
>> >> > generic
>> >> > features which can be used by all kinds of FDWs.
>> >> >
>> >> > a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED instead
>> >> > of
>> >> > COMMIT/ABORT on foreign server which supports 2PC.
>> >> > b. Manage information of foreign prepared transactions resolver
>> >> >
>> >> > Masahiko Sawada will post the patch.
>> >> >
>> >> >
>> >>
>> >
>> > Thanks Vinayak and Sawada-san for taking this forward and basing your
>> > work
>> > on my patch.
>> >
>> >>
>> >> Still lot of work to do but attached latest patches.
>> >> These are based on the patch Ashutosh posted before, I revised it and
>> >> divided into two patches.
>> >> Compare with original patch, patch of pg_fdw_xact_resolver and
>> >> documentation are lacked.
>> >
>> >
>> > I am not able to understand the last statement.
>>
>> Sorry to confuse you.
>>
>> > Do you mean to say that your patches do not have pg_fdw_xact_resolver()
>> > and
>> > documentation that my patches had?
>>
>> Yes.
>> I'm confirming them that your patches had.
>
>
> Thanks for the clarification. I had added pg_fdw_xact_resolver() to resolve
> any transactions which can not be resolved immediately after they were
> prepared. There was a comment from Kevin (IIRC) that leaving transactions
> unresolved on the foreign server keeps the resources locked on those
> servers. That's not a very good situation. And nobody but the initiating
> server can resolve those. That functionality is important to make it a
> complete 2PC solution. So, please consider it to be included in your first
> set of patches.
>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company



-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[vinayak]

On 2016/09/07 10:54, vinayak wrote:


Thanks for the clarification. I had added pg_fdw_xact_resolver() to 
resolve any transactions which can not be resolved immediately after 
they were prepared. There was a comment from Kevin (IIRC) that 
leaving transactions unresolved on the foreign server keeps the 
resources locked on those servers. That's not a very good situation. 
And nobody but the initiating server can resolve those. That 
functionality is important to make it a complete 2PC solution. So, 
please consider it to be included in your first set of patches.

The attached patch included pg_fdw_xact_resolver.


The attached patch includes the documentation.

Regards,
Vinayak Pokale
NTT Open Source Software Center


0001-Support-transaction-with-foreign-servers.patch
Description: application/download

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[vinayak]

On 2016/08/26 15:13, Ashutosh Bapat wrote:



On Fri, Aug 26, 2016 at 11:37 AM, Masahiko Sawada 
> wrote:


On Fri, Aug 26, 2016 at 3:03 PM, Ashutosh Bapat
> wrote:
>
>
> On Fri, Aug 26, 2016 at 11:22 AM, Masahiko Sawada
>
> wrote:
>>
>> On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale
>
>> wrote:
>> > Hi All,
>> >
>> > Ashutosh proposed the feature 2PC for FDW for achieving
atomic commits
>> > across multiple foreign servers.
>> > If a transaction make changes to more than two foreign
servers the
>> > current
>> > implementation in postgres_fdw doesn't make sure that either
all of them
>> > commit or all of them rollback their changes.
>> >
>> > We (Masahiko Sawada and me) reopen this thread and trying to
contribute
>> > in
>> > it.
>> >
>> > 2PC for FDW
>> > 
>> > The patch provides support for atomic commit for transactions
involving
>> > foreign servers. when the transaction makes changes to
foreign servers,
>> > either all the changes to all the foreign servers commit or
rollback.
>> >
>> > The new patch 2PC for FDW include the following things:
>> > 1. The patch 0001 introduces a generic feature. All kinds of
FDW that
>> > support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc.
can involve
>> > in
>> > the transaction.
>> >
>> > Currently we can push some conditions down to shard nodes,
especially in
>> > 9.6
>> > the directly modify feature has
>> > been introduced. But such a transaction modifying data on
shard node is
>> > not
>> > executed surely.
>> > Using 0002 patch, that modify is executed with 2PC. It means
that we
>> > almost
>> > can provide sharding solution using
>> > multiple PostgreSQL server (one parent node and several
shared node).
>> >
>> > For multi master, we definitely need transaction manager but
transaction
>> > manager probably can use this 2PC for FDW feature to manage
distributed
>> > transaction.
>> >
>> > 2. 0002 patch makes postgres_fdw possible to use 2PC.
>> >
>> > 0002 patch makes postgres_fdw to use below APIs. These APIs
are generic
>> > features which can be used by all kinds of FDWs.
>> >
>> > a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED
instead of
>> > COMMIT/ABORT on foreign server which supports 2PC.
>> > b. Manage information of foreign prepared transactions
resolver
>> >
>> > Masahiko Sawada will post the patch.
>> >
>> >
>>
>
> Thanks Vinayak and Sawada-san for taking this forward and basing
your work
> on my patch.
>
>>
>> Still lot of work to do but attached latest patches.
>> These are based on the patch Ashutosh posted before, I revised
it and
>> divided into two patches.
>> Compare with original patch, patch of pg_fdw_xact_resolver and
>> documentation are lacked.
>
>
> I am not able to understand the last statement.

Sorry to confuse you.

> Do you mean to say that your patches do not have
pg_fdw_xact_resolver() and
> documentation that my patches had?

Yes.
I'm confirming them that your patches had.


Thanks for the clarification. I had added pg_fdw_xact_resolver() to 
resolve any transactions which can not be resolved immediately after 
they were prepared. There was a comment from Kevin (IIRC) that leaving 
transactions unresolved on the foreign server keeps the resources 
locked on those servers. That's not a very good situation. And nobody 
but the initiating server can resolve those. That functionality is 
important to make it a complete 2PC solution. So, please consider it 
to be included in your first set of patches.

The attached patch included pg_fdw_xact_resolver.

Regards,
Vinayak Pokale
NTT Open Source Software Center



0003-pg-fdw-xact-resolver.patch
Description: application/download

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Fri, Aug 26, 2016 at 3:13 PM, Ashutosh Bapat
 wrote:
>
>
> On Fri, Aug 26, 2016 at 11:37 AM, Masahiko Sawada 
> wrote:
>>
>> On Fri, Aug 26, 2016 at 3:03 PM, Ashutosh Bapat
>>  wrote:
>> >
>> >
>> > On Fri, Aug 26, 2016 at 11:22 AM, Masahiko Sawada
>> > 
>> > wrote:
>> >>
>> >> On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale 
>> >> wrote:
>> >> > Hi All,
>> >> >
>> >> > Ashutosh proposed the feature 2PC for FDW for achieving atomic
>> >> > commits
>> >> > across multiple foreign servers.
>> >> > If a transaction make changes to more than two foreign servers the
>> >> > current
>> >> > implementation in postgres_fdw doesn't make sure that either all of
>> >> > them
>> >> > commit or all of them rollback their changes.
>> >> >
>> >> > We (Masahiko Sawada and me) reopen this thread and trying to
>> >> > contribute
>> >> > in
>> >> > it.
>> >> >
>> >> > 2PC for FDW
>> >> > 
>> >> > The patch provides support for atomic commit for transactions
>> >> > involving
>> >> > foreign servers. when the transaction makes changes to foreign
>> >> > servers,
>> >> > either all the changes to all the foreign servers commit or rollback.
>> >> >
>> >> > The new patch 2PC for FDW include the following things:
>> >> > 1. The patch 0001 introduces a generic feature. All kinds of FDW that
>> >> > support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc. can
>> >> > involve
>> >> > in
>> >> > the transaction.
>> >> >
>> >> > Currently we can push some conditions down to shard nodes, especially
>> >> > in
>> >> > 9.6
>> >> > the directly modify feature has
>> >> > been introduced. But such a transaction modifying data on shard node
>> >> > is
>> >> > not
>> >> > executed surely.
>> >> > Using 0002 patch, that modify is executed with 2PC. It means that we
>> >> > almost
>> >> > can provide sharding solution using
>> >> > multiple PostgreSQL server (one parent node and several shared node).
>> >> >
>> >> > For multi master, we definitely need transaction manager but
>> >> > transaction
>> >> > manager probably can use this 2PC for FDW feature to manage
>> >> > distributed
>> >> > transaction.
>> >> >
>> >> > 2. 0002 patch makes postgres_fdw possible to use 2PC.
>> >> >
>> >> > 0002 patch makes postgres_fdw to use below APIs. These APIs are
>> >> > generic
>> >> > features which can be used by all kinds of FDWs.
>> >> >
>> >> > a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED instead
>> >> > of
>> >> > COMMIT/ABORT on foreign server which supports 2PC.
>> >> > b. Manage information of foreign prepared transactions resolver
>> >> >
>> >> > Masahiko Sawada will post the patch.
>> >> >
>> >> >
>> >>
>> >
>> > Thanks Vinayak and Sawada-san for taking this forward and basing your
>> > work
>> > on my patch.
>> >
>> >>
>> >> Still lot of work to do but attached latest patches.
>> >> These are based on the patch Ashutosh posted before, I revised it and
>> >> divided into two patches.
>> >> Compare with original patch, patch of pg_fdw_xact_resolver and
>> >> documentation are lacked.
>> >
>> >
>> > I am not able to understand the last statement.
>>
>> Sorry to confuse you.
>>
>> > Do you mean to say that your patches do not have pg_fdw_xact_resolver()
>> > and
>> > documentation that my patches had?
>>
>> Yes.
>> I'm confirming them that your patches had.
>
>
> Thanks for the clarification. I had added pg_fdw_xact_resolver() to resolve
> any transactions which can not be resolved immediately after they were
> prepared. There was a comment from Kevin (IIRC) that leaving transactions
> unresolved on the foreign server keeps the resources locked on those
> servers. That's not a very good situation. And nobody but the initiating
> server can resolve those. That functionality is important to make it a
> complete 2PC solution. So, please consider it to be included in your first
> set of patches.
>

Yeah, I know the reason why pg_fdw_xact_resolver is required.
I will add it as a separated patch.

Regards,

--
Masahiko Sawada


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Fri, Aug 26, 2016 at 11:37 AM, Masahiko Sawada 
wrote:

> On Fri, Aug 26, 2016 at 3:03 PM, Ashutosh Bapat
>  wrote:
> >
> >
> > On Fri, Aug 26, 2016 at 11:22 AM, Masahiko Sawada  >
> > wrote:
> >>
> >> On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale 
> >> wrote:
> >> > Hi All,
> >> >
> >> > Ashutosh proposed the feature 2PC for FDW for achieving atomic commits
> >> > across multiple foreign servers.
> >> > If a transaction make changes to more than two foreign servers the
> >> > current
> >> > implementation in postgres_fdw doesn't make sure that either all of
> them
> >> > commit or all of them rollback their changes.
> >> >
> >> > We (Masahiko Sawada and me) reopen this thread and trying to
> contribute
> >> > in
> >> > it.
> >> >
> >> > 2PC for FDW
> >> > 
> >> > The patch provides support for atomic commit for transactions
> involving
> >> > foreign servers. when the transaction makes changes to foreign
> servers,
> >> > either all the changes to all the foreign servers commit or rollback.
> >> >
> >> > The new patch 2PC for FDW include the following things:
> >> > 1. The patch 0001 introduces a generic feature. All kinds of FDW that
> >> > support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc. can
> involve
> >> > in
> >> > the transaction.
> >> >
> >> > Currently we can push some conditions down to shard nodes, especially
> in
> >> > 9.6
> >> > the directly modify feature has
> >> > been introduced. But such a transaction modifying data on shard node
> is
> >> > not
> >> > executed surely.
> >> > Using 0002 patch, that modify is executed with 2PC. It means that we
> >> > almost
> >> > can provide sharding solution using
> >> > multiple PostgreSQL server (one parent node and several shared node).
> >> >
> >> > For multi master, we definitely need transaction manager but
> transaction
> >> > manager probably can use this 2PC for FDW feature to manage
> distributed
> >> > transaction.
> >> >
> >> > 2. 0002 patch makes postgres_fdw possible to use 2PC.
> >> >
> >> > 0002 patch makes postgres_fdw to use below APIs. These APIs are
> generic
> >> > features which can be used by all kinds of FDWs.
> >> >
> >> > a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED instead
> of
> >> > COMMIT/ABORT on foreign server which supports 2PC.
> >> > b. Manage information of foreign prepared transactions resolver
> >> >
> >> > Masahiko Sawada will post the patch.
> >> >
> >> >
> >>
> >
> > Thanks Vinayak and Sawada-san for taking this forward and basing your
> work
> > on my patch.
> >
> >>
> >> Still lot of work to do but attached latest patches.
> >> These are based on the patch Ashutosh posted before, I revised it and
> >> divided into two patches.
> >> Compare with original patch, patch of pg_fdw_xact_resolver and
> >> documentation are lacked.
> >
> >
> > I am not able to understand the last statement.
>
> Sorry to confuse you.
>
> > Do you mean to say that your patches do not have pg_fdw_xact_resolver()
> and
> > documentation that my patches had?
>
> Yes.
> I'm confirming them that your patches had.
>

Thanks for the clarification. I had added pg_fdw_xact_resolver() to resolve
any transactions which can not be resolved immediately after they were
prepared. There was a comment from Kevin (IIRC) that leaving transactions
unresolved on the foreign server keeps the resources locked on those
servers. That's not a very good situation. And nobody but the initiating
server can resolve those. That functionality is important to make it a
complete 2PC solution. So, please consider it to be included in your first
set of patches.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Masahiko Sawada]

On Fri, Aug 26, 2016 at 3:03 PM, Ashutosh Bapat
 wrote:
>
>
> On Fri, Aug 26, 2016 at 11:22 AM, Masahiko Sawada 
> wrote:
>>
>> On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale 
>> wrote:
>> > Hi All,
>> >
>> > Ashutosh proposed the feature 2PC for FDW for achieving atomic commits
>> > across multiple foreign servers.
>> > If a transaction make changes to more than two foreign servers the
>> > current
>> > implementation in postgres_fdw doesn't make sure that either all of them
>> > commit or all of them rollback their changes.
>> >
>> > We (Masahiko Sawada and me) reopen this thread and trying to contribute
>> > in
>> > it.
>> >
>> > 2PC for FDW
>> > 
>> > The patch provides support for atomic commit for transactions involving
>> > foreign servers. when the transaction makes changes to foreign servers,
>> > either all the changes to all the foreign servers commit or rollback.
>> >
>> > The new patch 2PC for FDW include the following things:
>> > 1. The patch 0001 introduces a generic feature. All kinds of FDW that
>> > support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc. can involve
>> > in
>> > the transaction.
>> >
>> > Currently we can push some conditions down to shard nodes, especially in
>> > 9.6
>> > the directly modify feature has
>> > been introduced. But such a transaction modifying data on shard node is
>> > not
>> > executed surely.
>> > Using 0002 patch, that modify is executed with 2PC. It means that we
>> > almost
>> > can provide sharding solution using
>> > multiple PostgreSQL server (one parent node and several shared node).
>> >
>> > For multi master, we definitely need transaction manager but transaction
>> > manager probably can use this 2PC for FDW feature to manage distributed
>> > transaction.
>> >
>> > 2. 0002 patch makes postgres_fdw possible to use 2PC.
>> >
>> > 0002 patch makes postgres_fdw to use below APIs. These APIs are generic
>> > features which can be used by all kinds of FDWs.
>> >
>> > a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED instead of
>> > COMMIT/ABORT on foreign server which supports 2PC.
>> > b. Manage information of foreign prepared transactions resolver
>> >
>> > Masahiko Sawada will post the patch.
>> >
>> >
>>
>
> Thanks Vinayak and Sawada-san for taking this forward and basing your work
> on my patch.
>
>>
>> Still lot of work to do but attached latest patches.
>> These are based on the patch Ashutosh posted before, I revised it and
>> divided into two patches.
>> Compare with original patch, patch of pg_fdw_xact_resolver and
>> documentation are lacked.
>
>
> I am not able to understand the last statement.

Sorry to confuse you.

> Do you mean to say that your patches do not have pg_fdw_xact_resolver() and
> documentation that my patches had?

Yes.
I'm confirming them that your patches had.

Regards,

--
Masahiko Sawada


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Fri, Aug 26, 2016 at 11:22 AM, Masahiko Sawada 
wrote:

> On Fri, Aug 26, 2016 at 1:32 PM, Vinayak Pokale 
> wrote:
> > Hi All,
> >
> > Ashutosh proposed the feature 2PC for FDW for achieving atomic commits
> > across multiple foreign servers.
> > If a transaction make changes to more than two foreign servers the
> current
> > implementation in postgres_fdw doesn't make sure that either all of them
> > commit or all of them rollback their changes.
> >
> > We (Masahiko Sawada and me) reopen this thread and trying to contribute
> in
> > it.
> >
> > 2PC for FDW
> > 
> > The patch provides support for atomic commit for transactions involving
> > foreign servers. when the transaction makes changes to foreign servers,
> > either all the changes to all the foreign servers commit or rollback.
> >
> > The new patch 2PC for FDW include the following things:
> > 1. The patch 0001 introduces a generic feature. All kinds of FDW that
> > support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc. can involve
> in
> > the transaction.
> >
> > Currently we can push some conditions down to shard nodes, especially in
> 9.6
> > the directly modify feature has
> > been introduced. But such a transaction modifying data on shard node is
> not
> > executed surely.
> > Using 0002 patch, that modify is executed with 2PC. It means that we
> almost
> > can provide sharding solution using
> > multiple PostgreSQL server (one parent node and several shared node).
> >
> > For multi master, we definitely need transaction manager but transaction
> > manager probably can use this 2PC for FDW feature to manage distributed
> > transaction.
> >
> > 2. 0002 patch makes postgres_fdw possible to use 2PC.
> >
> > 0002 patch makes postgres_fdw to use below APIs. These APIs are generic
> > features which can be used by all kinds of FDWs.
> >
> > a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED instead of
> > COMMIT/ABORT on foreign server which supports 2PC.
> > b. Manage information of foreign prepared transactions resolver
> >
> > Masahiko Sawada will post the patch.
> >
> >
>
>
Thanks Vinayak and Sawada-san for taking this forward and basing your work
on my patch.


> Still lot of work to do but attached latest patches.
> These are based on the patch Ashutosh posted before, I revised it and
> divided into two patches.
> Compare with original patch, patch of pg_fdw_xact_resolver and
> documentation are lacked.
>

I am not able to understand the last statement.

Do you mean to say that your patches do not have pg_fdw_xact_resolver() and
documentation that my patches had?

OR

you mean to say that my patches did not have (lacked)
pg_fdw_xact_resolver() and documenation

OR some combination of those?
-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Vinayak Pokale]

Hi All,

Ashutosh proposed the feature 2PC for FDW for achieving atomic commits
across multiple foreign servers.
If a transaction make changes to more than two foreign servers the current
implementation in postgres_fdw doesn't make sure that either all of them
commit or all of them rollback their changes.

We (Masahiko Sawada and me) reopen this thread and trying to contribute in
it.

2PC for FDW

The patch provides support for atomic commit for transactions involving
foreign servers. when the transaction makes changes to foreign servers,
either all the changes to all the foreign servers commit or rollback.

The new patch 2PC for FDW include the following things:
1. The patch 0001 introduces a generic feature. All kinds of FDW that
support 2PC such as oracle_fdw, mysql_fdw, postgres_fdw etc. can involve in
the transaction.

Currently we can push some conditions down to shard nodes, especially in
9.6 the directly modify feature has
been introduced. But such a transaction modifying data on shard node is not
executed surely.
Using 0002 patch, that modify is executed with 2PC. It means that we almost
can provide sharding solution using
multiple PostgreSQL server (one parent node and several shared node).

For multi master, we definitely need transaction manager but transaction
manager probably can use this 2PC for FDW feature to manage distributed
transaction.

2. 0002 patch makes postgres_fdw possible to use 2PC.

0002 patch makes postgres_fdw to use below APIs. These APIs are generic
features which can be used by all kinds of FDWs.

a. Execute PREAPRE TRANSACTION and COMMIT/ABORT PREAPRED instead of
COMMIT/ABORT on foreign server which supports 2PC.
b. Manage information of foreign prepared transactions resolver

Masahiko Sawada will post the patch.

Suggestions and comments are helpful to implement this feature.

Regards,

Vinayak Pokale

On Mon, Feb 1, 2016 at 11:14 PM, Alvaro Herrera 
wrote:

> Alvaro Herrera wrote:
> > Ashutosh Bapat wrote:
> >
> > > Here's updated patch. I didn't use version numbers in file names in my
> > > previous patches. I am starting from this onwards.
> >
> > Um, I tried this patch and it doesn't apply at all.  There's a large
> > number of conflicts.  Please update it and resubmit to the next
> > commitfest.
>
> Also, please run "git show --check" of "git diff origin/master --check"
> and fix the whitespace problems that it shows.  It's an easy thing but
> there's a lot of red squares in my screen.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Alvaro Herrera]

Alvaro Herrera wrote:
> Ashutosh Bapat wrote:
> 
> > Here's updated patch. I didn't use version numbers in file names in my
> > previous patches. I am starting from this onwards.
> 
> Um, I tried this patch and it doesn't apply at all.  There's a large
> number of conflicts.  Please update it and resubmit to the next
> commitfest.

Also, please run "git show --check" of "git diff origin/master --check"
and fix the whitespace problems that it shows.  It's an easy thing but
there's a lot of red squares in my screen.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Alvaro Herrera]

Ashutosh Bapat wrote:

> Here's updated patch. I didn't use version numbers in file names in my
> previous patches. I am starting from this onwards.

Um, I tried this patch and it doesn't apply at all.  There's a large
number of conflicts.  Please update it and resubmit to the next
commitfest.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

On Thu, Dec 24, 2015 at 8:32 AM, Michael Paquier 
wrote:

> On Mon, Nov 9, 2015 at 8:55 PM, Ashutosh Bapat
>  wrote:
> >
> >
> > On Sat, Nov 7, 2015 at 12:07 AM, Robert Haas 
> wrote:
> >>
> >> On Wed, Aug 12, 2015 at 6:25 AM, Ashutosh Bapat
> >>  wrote:
> >> > The previous patch would not compile on the latest HEAD. Here's
> updated
> >> > patch.
> >>
> >> Perhaps unsurprisingly, this doesn't apply any more.  But we have
> >> bigger things to worry about.
> >>
> >
> > Here's updated patch. I didn't use version numbers in file names in my
> > previous patches. I am starting from this onwards.
>
> Ashutosh, others, this thread has been stalling for more than 1 month
> and a half. There is a new patch that still applies (be careful of
> whitespaces btw), but no reviews came in. So what should we do? I
> would tend to move this patch to the next CF because of a lack of
> reviews.
>

Yes, that would help. Thanks.

-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Michael Paquier]

On Thu, Dec 24, 2015 at 7:03 PM, Ashutosh Bapat
 wrote:
> On Thu, Dec 24, 2015 at 8:32 AM, Michael Paquier 
>> Ashutosh, others, this thread has been stalling for more than 1 month
>> and a half. There is a new patch that still applies (be careful of
>> whitespaces btw), but no reviews came in. So what should we do? I
>> would tend to move this patch to the next CF because of a lack of
>> reviews.
>
>
> Yes, that would help. Thanks.

Done.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Michael Paquier]

On Mon, Nov 9, 2015 at 8:55 PM, Ashutosh Bapat
 wrote:
>
>
> On Sat, Nov 7, 2015 at 12:07 AM, Robert Haas  wrote:
>>
>> On Wed, Aug 12, 2015 at 6:25 AM, Ashutosh Bapat
>>  wrote:
>> > The previous patch would not compile on the latest HEAD. Here's updated
>> > patch.
>>
>> Perhaps unsurprisingly, this doesn't apply any more.  But we have
>> bigger things to worry about.
>>
>
> Here's updated patch. I didn't use version numbers in file names in my
> previous patches. I am starting from this onwards.

Ashutosh, others, this thread has been stalling for more than 1 month
and a half. There is a new patch that still applies (be careful of
whitespaces btw), but no reviews came in. So what should we do? I
would tend to move this patch to the next CF because of a lack of
reviews.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Transactions involving multiple postgres foreign servers

[Ashutosh Bapat]

> Any distributed transaction management requires 2PC in some or other form.
> So, we should implement 2PC for FDW keeping in mind various forms of 2PC
> used practically. Use that infrastructure to build XTM like capabilities
> for restricted postgres_fdw uses. Previously, I have requested the authors
> of XTM to look at my patch and provide me feedback about their requirements
> for implementing 2PC part of XTM. But I have not heard anything from them.
>
> 1.
> https://domino.mpi-inf.mpg.de/intranet/ag5/ag5publ.nsf/1c0a12a383dd2cd8c125613300585c64/7684dd8109a5b3d5c1256de40051686f/$FILE/tdd99.pdf
>
>
>
> Sorry, may be I missed some message. but I have not received request from
> you to provide feedback concerning your patch.
>
>
See my mail on 31st August on hackers in the thread with subject
"Horizontal scalability/sharding".

>
> --
> Best Wishes,
> Ashutosh Bapat
> EnterpriseDB Corporation
> The Postgres Database Company
>
>
>


-- 
Best Wishes,
Ashutosh Bapat
EnterpriseDB Corporation
The Postgres Database Company