Doltgres, the world’s first version-controlled Postgres-compatible database, just hit 1.0, meaning that it’s ready for production use. We want Doltgres to be a drop-in replacement for Postgres so that customers can use the entire ecosystem of Postgres-compatible tools and libraries, or port their existing database application to Doltgres without changing any code. This means getting all the nuanced semantics of Postgres’s behavior correct in our own emulation. And we think we’ve done pretty well here — our compatibility tests encompass over two dozen tools and languages.

But that doesn’t mean there aren’t gaps that customers find. We just addressed a pretty major one: how Doltgres handles implicit transactions of multiple statements.

What’s an implicit transaction?#

In SQL databases like Postgres, you can control transactions explicitly with the use of BEGIN, COMMIT, ROLLBACK, etc. But what happens when you don’t do that? When does a transaction get committed or rolled back?

As it turns out, the rules for this behavior in Postgres were much more nuanced than we originally understood. Single statements in Postgres that don’t take place in an explicit transaction block (after BEGIN) are each automatically committed on a per-statement basis. So if you’re in a psql shell, you’ll see behavior like this:

psql> insert into mytable values (1);
psql> select 1/0; -- error!
psql> select * from mytable where id = 1; -- success, previous statement was committed

This is very semantically similar to the autocommit session setting in MySQL, which is on by default. So you can think of Postgres implicit transactions to be the same as MySQL with autocommit on, right? Well, no.

This is what Doltgres pre-1.1.0 did, and as it turns out, there are a couple very major exceptions to this behavior that depend on which wire protocol you’re using. Like a lot of databases that have been around for a while, Postgres supports multiple wire protocol formats. The important thing to know for this conversation is that the two protocols work differently with respect to implicit transactions.

The Simple Query protocol#

The simpler protocol is appropriately called the Simple Query protocol. In this protocol, the client sends query strings to the server to be executed. Normally, each of these queries gets its own implicit transaction, unless the session issued a BEGIN to start manual transaction control. But there’s a twist: a Query message can accept multiple queries separated by semicolons, and the entire set of queries in a single Query message succeeds or fails atomically in its own transaction. For example, consider this single Query message containing multiple statements.

INSERT INTO mytable VALUES(1);
SELECT 1/0;
INSERT INTO mytable VALUES(2);

According to the docs,

… the divide-by-zero failure in the SELECT will force rollback of the first INSERT. Furthermore, because execution of the message is abandoned at the first error, the second INSERT is never attempted at all.

The initial release of Doltgres 1.0 did not correctly capture these semantics, instead committing each statement individually. But it gets even subtler when you introduce explicit transaction control. A single Query message again:

BEGIN;
INSERT INTO mytable VALUES(1);
COMMIT;
INSERT INTO mytable VALUES(2);
SELECT 1/0;

The second INSERT and the SELECT are still treated as a single transaction, so that the divide-by-zero failure will roll back the second INSERT, but not the first one.

Doltgres now correctly emulates this behavior as of 1.1.0. Thanks to the customer who reported this deviation.

The Extended Query protocol#

The simple query protocol came first in Postgres’s evolution as a product, but if you connect to the database with a GUI or a library it’s probably using the newer protocol, called the extended query protocol. It’s the one that supports prepared statements, cursors, and a variety of other features, as well as being faster for many common patterns.

If you use this protocol, chances are very good you’re using a library that does it for you. Therefore many of the subtle details of implicit transaction management are being handled for you invisibly behind the scenes. But those details still have to be correct, or else those libraries will make bad assumptions about what work has been completed and what hasn’t, leading to strange bugs. And, as you might have guessed, implicit transactions in the extended protocol work quite differently than in the simple one.

The reason for this is that an application can streamline communication by sending multiple messages without waiting for a response from the server, thereby reducing network round trips. But this comes at the cost of trickier semantics for implicit transactions.

Use of the extended query protocol allows pipelining, which means sending a series of queries without waiting for earlier ones to complete. This reduces the number of network round trips needed to complete a given series of operations. However, the user must carefully consider the required behavior if one of the steps fails, since later queries will already be in flight to the server.

One way to deal with that is to make the whole query series be a single transaction, that is wrap it in BEGIN … COMMIT. However, this does not help if one wishes for some of the commands to commit independently of others.

Basically: the extended protocol treats a Sync message as an implicit transaction boundary. If a batch of statements before a Sync had an error, then Sync rolls it back. Otherwise, Sync commits it. It’s typical for a client library to Sync after every query, which leads to single-statement implicit transactions, as in the psql shell. But it’s also possible to write a message pipeline that sends Sync messages at carefully selected times to control which batches of statements get committed together atomically, but without wrapping such batches in BEGIN blocks.

The extended query protocol provides another way to manage this concern, which is to omit sending Sync messages between steps that are dependent. Since, after an error, the backend will skip command messages until it finds Sync, this allows later commands in a pipeline to be skipped automatically when an earlier one fails, without the client having to manage that explicitly with BEGIN and COMMIT. Independently-committable segments of the pipeline can be separated by Sync messages.

One has to wonder what kind of madman would write their application this way instead of using explicit BEGIN and COMMIT statements. But it’s supported by Postgres, which means Doltgres has to support it too or else somebody’s library will misbehave. As of release 1.1.0, Doltgres does.

Conclusion#

1.0 has come and gone, but Doltgres’s compatibility story is definitely not over. Postgres is 30 years old, so we have our work cut out for us to correctly emulate the entirety of its gigantic surface area. We’ll keep iterating to get us closer and closer with every release.

Want to discuss Postgres transactions, or learn more about Doltgres? Visit us on the DoltHub Discord where our engineering team hangs out all day. Hope to see you there.