SQLException error codes when using PostgreSQL

[archie...@gmail.com]

I'm planning to access CockroachDB from Java using the PostgreSQL JDBC connector.

I have a couple of questions.

Question #1
-----------

I need to be able to detect SQLExceptions that represents "retryable" errors (e.g., conflict loser) so that I can retry them at the business logic level.

Normally with PostgreSQL, the return from SQLException.getSqlState() is used to determine the type of error.

See their list here: http://www.postgresql.org/docs/9.3/static/errcodes-appendix.html

What are all the possible return values from SQLException.getSqlState() that represent retryable errors from CockroachDB?

Question #2
-----------

Suppose there is network flakiness or something. How long before a transaction times out? Is this configurable on a per-transaction basis? If so, how? And where are all the session/transaction SET variables documented?

Thanks.

[Andrei Matei]

Hey Archie,

About the retryable errors - I see we haven't included a Java sample for that on our docs. We really need to get on it.

CockroachDB returns one "Postgres" error code for retryable error: "CR000". It wasn't immediately clear to me from the docs how this translates to what that SQLException.getSqlState() gives you. But whatever way JDBC gives you access to error codes, this is what you should be looking for. If you don't have access to the error code, you can look for this string in the error message: "restart transaction:".

You can take a look at the samples for other languages at the bottom of this page for inspiration:
https://www.cockroachlabs.com/docs/build-a-test-app.html

On timeouts, there are no user controllable transaction timeouts at the moment. But, if the TCP connection from the client to the server has (is detected to have) died, the ongoing transaction is rolled back (and drivers also generally explicitly close a connection once the client has tried to send something and it failed, for example). If you have a distributed transaction in a multi-node cluster, some of the nodes involved have to heartbeat each other periodically. If they fail to do so for 10s I think, an ongoing transaction is again rolled back.

Hope this helps,

- a_m

--
You received this message because you are subscribed to the Google Groups "CockroachDB" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cockroach-db...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[archie...@gmail.com]

On Thursday, April 7, 2016 at 11:47:18 AM UTC-5, Andrei Matei wrote:
> CockroachDB returns one "Postgres" error code for retryable error: "CR000".

Thanks, that's exactly what I needed to know.

> On timeouts, there are no user controllable transaction timeouts at the moment.

I think this is an important feature. Almost all software that performs transactions needs to be able to specify a limit for how long a thread performing such a a transaction will block.

Ideally, there would be a SET variable for this, and you could say e.g.:

SET transaction_timeout = 2500

and then if a transaction took longer than this limit to commit, an error would occur (it must have a well-defined error code like "CT000" or whatever).

Also, is there an answer to this question: ?

[Radu Berinde]

Hi Archie,

Thanks for the interest!

> I think this is an important feature. Almost all software that performs transactions needs to be able to specify a limit for how long a thread performing such a a transaction will block.

We may add this in the future. But note that in our system, there is no thread blocked because of an in-progress transaction. More importantly, our serialization is not based on locks so an in-progress transaction will not strictly block all other transactions; if there are a lot of conflicting transactions, the long running transaction will eventually fail with a retryable error.

> where are all the session/transaction SET variables documented?

Unfortunately I don't think we have specific documentation on that yet but we have the entire SQL grammar available which should help:

https://www.cockroachlabs.com/docs/sql-grammar.html#set_stmt

https://www.cockroachlabs.com/docs/sql-grammar.html#set_rest

https://www.cockroachlabs.com/docs/sql-grammar.html#transaction_mode_list

Other than the stuff that is in the grammar we don't have many variables (just SYNTAX and DATABASE as far as I can tell, code here: https://github.com/cockroachdb/cockroach/blob/master/sql/set.go#L33)

-Radu

[archie...@gmail.com]

On Thursday, April 7, 2016 at 3:43:08 PM UTC-5, Radu Berinde wrote:
> > I think this is an important feature. Almost all software that performs transactions needs to be able to specify a limit for how long a thread performing such a a transaction will block.
>
> We may add this in the future. But note that in our system, there is no thread blocked because of an in-progress transaction. More importantly, our serialization is not based on locks so an in-progress transaction will not strictly block all other transactions; if there are a lot of conflicting transactions, the long running transaction will eventually fail with a retryable error.

That good to know, but I'm worried about network problems as well as conflict handling. Obviously, in order for a node to commit an ACID transaction, it will have to talk to the other nodes, and that means the potential for arbitrarily long delays unless there are some timeouts being enforced on that network communication. The client will of course inherit these delays while waiting for confirmation that the transaction committed.

In other words, what the client cares about is being able to set an upper bound on all possible sources of delay together, no matter what their source.

> > where are all the session/transaction SET variables documented?
>

> Other than the stuff that is in the grammar we don't have many variables (just SYNTAX and DATABASE as far as I can tell, code here: https://github.com/cockroachdb/cockroach/blob/master/sql/set.go#L33)

Thanks, the code is always authoritative :)

It would be nice to start a documentation page listing these variables, to document them and provide a place for new ones that will inevitably appear in the future. Obviously not a serious problem now because there's not much there yet.

-Archie

[Peter Mattis]

Hi Archie,

On Thu, Apr 7, 2016 at 6:21 PM, <archie...@gmail.com> wrote:

On Thursday, April 7, 2016 at 3:43:08 PM UTC-5, Radu Berinde wrote:
> > I think this is an important feature. Almost all software that performs transactions needs to be able to specify a limit for how long a thread performing such a a transaction will block.
>
> We may add this in the future. But note that in our system, there is no thread blocked because of an in-progress transaction. More importantly, our serialization is not based on locks so an in-progress transaction will not strictly block all other transactions; if there are a lot of conflicting transactions, the long running transaction will eventually fail with a retryable error.

That good to know, but I'm worried about network problems as well as conflict handling. Obviously, in order for a node to commit an ACID transaction, it will have to talk to the other nodes, and that means the potential for arbitrarily long delays unless there are some timeouts being enforced on that network communication. The client will of course inherit these delays while waiting for confirmation that the transaction committed.

In other words, what the client cares about is being able to set an upper bound on all possible sources of delay together, no matter what their source.

Got it. We do have all the pieces in place to provide a user controlled timeout for transactions and/or statements.

 

> > where are all the session/transaction SET variables documented?
>
> Other than the stuff that is in the grammar we don't have many variables (just SYNTAX and DATABASE as far as I can tell, code here: https://github.com/cockroachdb/cockroach/blob/master/sql/set.go#L33)

Thanks, the code is always authoritative :)

It would be nice to start a documentation page listing these variables, to document them and provide a place for new ones that will inevitably appear in the future. Obviously not a serious problem now because there's not much there yet.

We're definitely planning to document everything, just haven't gotten around to these variables yet. 

 

-Archie

[Tobias Schottdorf]

This was also requested today at the talk Marc and I gave. Wonder if session var is the best way or perhaps a cap per user, or both.

--

-- Tobias

[Ben Darnell]

On Thu, Apr 7, 2016 at 6:21 PM, <archie...@gmail.com> wrote:

On Thursday, April 7, 2016 at 3:43:08 PM UTC-5, Radu Berinde wrote:
> > I think this is an important feature. Almost all software that performs transactions needs to be able to specify a limit for how long a thread performing such a a transaction will block.
>
> We may add this in the future. But note that in our system, there is no thread blocked because of an in-progress transaction. More importantly, our serialization is not based on locks so an in-progress transaction will not strictly block all other transactions; if there are a lot of conflicting transactions, the long running transaction will eventually fail with a retryable error.

That good to know, but I'm worried about network problems as well as conflict handling.

If you're worried about network problems then it sounds like you want a client-side feature rather than anything on the server. If you don't get a response in time, kill the connection on the client side and start over. A server-side timeout can be an optimization to let you avoid the cost of reestablishing the connection, but even if the server times out there is no guarantee that word will get back to the client in a timely fashion. One drawback to a server-side timeout in a distributed database is that it is possible for a transaction to complete even after returning a timeout to the client; if a server-side timeout fires in a non-distributed database you're generally safe in assuming that the transaction did not and will not complete.

One reason why many people want to prevent long-running queries is that MySQL is notoriously bad at noticing that a client has disconnected and a runaway query can hold locks for a long time. We should be better at noticing disconnected clients than mysql is (because we keep a goroutine watching the network connection; mysql's thread-per-connection model doesn't support this). Our transactions are not lock-based so we don't have the same failure modes, although an update with an overly-broad WHERE clause could still cause a lot of interference by aborting other transactions as it gets automatically retried, so it would be good to have some sort of safety there.

-Ben

 

Obviously, in order for a node to commit an ACID transaction, it will have to talk to the other nodes, and that means the potential for arbitrarily long delays unless there are some timeouts being enforced on that network communication. The client will of course inherit these delays while waiting for confirmation that the transaction committed.

In other words, what the client cares about is being able to set an upper bound on all possible sources of delay together, no matter what their source.

> > where are all the session/transaction SET variables documented?
>
> Other than the stuff that is in the grammar we don't have many variables (just SYNTAX and DATABASE as far as I can tell, code here: https://github.com/cockroachdb/cockroach/blob/master/sql/set.go#L33)

Thanks, the code is always authoritative :)

It would be nice to start a documentation page listing these variables, to document them and provide a place for new ones that will inevitably appear in the future. Obviously not a serious problem now because there's not much there yet.

-Archie

[Andrei Matei]

If you're worried about network problems then it sounds like you want a client-side feature rather than anything on the server. If you don't get a response in time, kill the connection on the client side and start over. A server-side timeout can be an optimization to let you avoid the cost of reestablishing the connection, but even if the server times out there is no guarantee that word will get back to the client in a timely fashion. One drawback to a server-side timeout in a distributed database is that it is possible for a transaction to complete even after returning a timeout to the client; if a server-side timeout fires in a non-distributed database you're generally safe in assuming that the transaction did not and will not complete.

Well, a server-side timeout is un-ambiguous if it happens before a COMMIT/RELEASE is issued. The transaction will not be committed in this case. Usually the COMMIT itself would not be the most likely to timeout. So the client gets the benefit of not having to re-establish the connection. The server can get benefits from cancelling some work and not relying on the client to promptly close the connection.

Note that some errors are already ambiguous: if you get a communication error from RELEASE, you don't know if the transaction committed or not. As with errors on COMMIT in other databases.