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 emulating every feature in Postgres, and there are a lot of them. And we think we’ve done pretty well here — our compatibility tests encompass over two dozen tools and languages.
But we aren’t quite there yet. Postgres has been in active development for about 40 years, and its surface area of features is enormous. We are working around the clock to close those gaps, and our customers are being very helpful in filing issues so we know what’s impacting people today.
Today we’re excited to announce a new major feature, requested by a customer and released last month: cursor support.
What’s a cursor?#
Many SQL databases allow clients to operate in one of two major modes when fetching rows in a query. The simple one we’re all familiar with just spools rows to the client in a streaming fashion as they’re available. It’s easy to use and what most people want when they run a query.
But many databases, including Postgres and MySQL, allow clients to declare an explicit cursor over a result set, which then lets the client fetch rows in batches as big or as small as it wants, when it wants them. Postgres goes further and lets clients move a cursor forward or backward over the result set, to rewind it or to skip past results that have already been processed. It’s a powerful, advanced technique for client-side row manipulation. We’re proud to announce that Doltgres now supports server-side cursors as well.
Demo#
For this simple demo, we’ll be connecting to a Doltgres database with a popular Python connection library and showing off various features of cursors.
To begin, we will open a connection to our demo database.
import os
import psycopg
SERVER = os.environ.get(
"DOLTGRES_DSN",
"host=127.0.0.1 port=5432 user=postgres password=password",
)
DSN = SERVER + " dbname=cursor_demo"
def rows(conn, sql):
return conn.execute(sql).fetchall()
# Setup: a fresh database with a small table, committed to Dolt's history on main
with psycopg.connect(SERVER + " dbname=postgres", autocommit=True) as admin:
admin.execute("DROP DATABASE IF EXISTS cursor_demo")
admin.execute("CREATE DATABASE cursor_demo")
reader = psycopg.connect(DSN)
writer = psycopg.connect(DSN, autocommit=True)
writer.execute("CREATE TABLE orders (id int PRIMARY KEY, customer text, total numeric(10,2))")
writer.execute("""
INSERT INTO orders VALUES
(1, 'ann', 10.00), (2, 'bob', 20.00), (3, 'cat', 30.00),
(4, 'dan', 40.00), (5, 'eve', 50.00)
""")
writer.execute("SELECT dolt_commit('-Am', 'seed orders')")The above creates a database, adds a table, and inserts a couple of values. Finally, it uses
dolt_commit() to create a new snapshot.
Now let’s DECLARE a cursor, FETCH some values from it, then MOVE it to a different position.
# The basics: DECLARE, FETCH in batches, MOVE, CLOSE
with reader.transaction():
reader.execute("DECLARE order_cur CURSOR FOR SELECT id, customer, total FROM orders ORDER BY id")
while batch := rows(reader, "FETCH 2 FROM order_cur"):
print("batch:", batch)
reader.execute("MOVE ABSOLUTE 0 IN order_cur") # rewind to before the first row
print("after rewind:", rows(reader, "FETCH NEXT FROM order_cur"))
reader.execute("CLOSE order_cur")This produces this output:
batch: [(1, 'ann', Decimal('10.00')), (2, 'bob', Decimal('20.00'))]
batch: [(3, 'cat', Decimal('30.00')), (4, 'dan', Decimal('40.00'))]
batch: [(5, 'eve', Decimal('50.00'))]
after rewind: [(1, 'ann', Decimal('10.00'))]You can only DECLARE a cursor inside a BEGIN block.
# Cursors only exist inside a transaction block (unless declared WITH HOLD)
reader.autocommit = True
try:
reader.execute("DECLARE oops CURSOR FOR SELECT * FROM orders")
except psycopg.errors.NoActiveSqlTransaction as e:
print("error:", e)
reader.autocommit = FalseThis is because, by design, cursors always read from a particular consistent snapshot, i.e. the
state of the database when they were DECLAREd.
with reader.transaction():
reader.execute("DECLARE snap_cur CURSOR FOR SELECT id, total FROM orders ORDER BY id")
print("first two:", rows(reader, "FETCH 2 FROM snap_cur"))
writer.execute("INSERT INTO orders VALUES (6, 'fay', 60.00)")
writer.execute("UPDATE orders SET total = 0 WHERE id = 4")
writer.execute("DELETE FROM orders WHERE id = 5")
writer.execute("SELECT dolt_commit('-am', 'writer changes')")
print("rest of cursor:", rows(reader, "FETCH ALL FROM snap_cur"))The above code produces this output. As you can see, the cursor can’t see the orders created after it was declared.
first two: [(1, Decimal('10.00')), (2, Decimal('20.00'))]
rest of cursor: [(3, Decimal('30.00')), (4, Decimal('40.00')), (5, Decimal('50.00'))]There’s an even more advanced snapshot technique you can use with cursors. Cursors declared with
WITH HOLD outlive their transaction and can be used until you call CLOSE on them.
# WITH HOLD cursors outlive the transaction that declared them, but still read
# from the snapshot taken at DECLARE time
reader.execute("DECLARE held_cur CURSOR WITH HOLD FOR SELECT id, total FROM orders ORDER BY id")
reader.commit()
writer.execute("UPDATE orders SET total = total * 2")
writer.execute("SELECT dolt_commit('-am', 'double every total')")
print("held cursor:", rows(reader, "FETCH ALL FROM held_cur"))
print("pg_cursors:", rows(reader, "SELECT name, is_holdable, is_scrollable FROM pg_cursors"))
reader.execute("CLOSE held_cur")
reader.commit()Finally, cursors can be declared as SCROLL or NO SCROLL. If you specify neither, Postgres
decides based on the query whether the cursor can move backward. NO SCROLL cursors can’t move
backward. Doing it this way can conserve server resources, since the server has to keep track of
fewer things.
with reader.transaction():
reader.execute("DECLARE scroll_cur SCROLL CURSOR FOR SELECT id FROM orders ORDER BY id")
print("last:", rows(reader, "FETCH LAST FROM scroll_cur"))
print("prior:", rows(reader, "FETCH PRIOR FROM scroll_cur"))
print("absolute 2:", rows(reader, "FETCH ABSOLUTE 2 FROM scroll_cur"))
try:
with reader.transaction():
reader.execute("DECLARE fwd_cur NO SCROLL CURSOR FOR SELECT id FROM orders ORDER BY id")
reader.execute("FETCH 2 FROM fwd_cur")
reader.execute("FETCH BACKWARD 1 FROM fwd_cur")
except psycopg.errors.ObjectNotInPrerequisiteState as e:
print("error:", e)And since Doltgres is a version-controlled database, it has an additional quirk not applicable to
Postgres cursors. Doltgres cursors read from the branch your session is connected to. If you change
your session’s branch, the cursor is still reading from the branch it was DECLAREd on. This
applies to all version-control features, like selecting from dolt_log.
# A cursor stays on the branch it was declared on, even if the session
# checks out a different branch before fetching from it
writer.execute("SELECT dolt_branch('feature')")
writer.execute("SELECT dolt_checkout('feature')")
writer.execute("INSERT INTO orders VALUES (100, 'zed', 1000.00)")
writer.execute("SELECT dolt_commit('-am', 'feature-only order')")
writer.execute("SELECT dolt_checkout('main')")
with reader.transaction():
reader.execute("DECLARE branch_cur CURSOR FOR SELECT id FROM orders ORDER BY id")
reader.execute("SELECT dolt_checkout('feature')")
print("active branch:", rows(reader, "SELECT active_branch()"))
print("cursor (main):", rows(reader, "FETCH ALL FROM branch_cur"))
print("select (feature):", rows(reader, "SELECT id FROM orders ORDER BY id"))
reader.execute("SELECT dolt_checkout('main')")
reader.commit()
# Cursors work over Dolt system tables too, with the same snapshot semantics. These
# (and table functions like dolt_diff) are read in full at DECLARE time rather than lazily.
with reader.transaction():
reader.execute("DECLARE log_cur CURSOR FOR SELECT commit_hash, message FROM dolt_log")
writer.execute("SELECT dolt_commit('--allow-empty', '-m', 'too late for the cursor')")
while batch := rows(reader, "FETCH 2 FROM log_cur"):
print("log:", [message for _, message in batch])
reader.close()
writer.close()The above example produces this output:
active branch: [('feature',)]
cursor (main): [(1,), (2,), (3,), (4,), (6,)]
select (feature): [(1,), (2,), (3,), (4,), (6,), (100,)]
log: ['double every total', 'writer changes']
log: ['seed orders', 'CREATE DATABASE']
log: ['Initialize data repository']Conclusion#
Doltgres’s compatibility just keeps getting better and better. Keep the issues coming and we’ll keep knocking them down in 24 hours.
Have a Postgres feature you need implemented? Want to learn more about Doltgres? Visit us on the DoltHub Discord where our engineering team hangs out all day. Hope to see you there.