Schema change on existing transaction that's been written to

Caused by: org.postgresql.util.PSQLException: ERROR: schema change statement cannot follow a statement that has written in the same transaction: transaction anchor key already set
	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)
	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2189)
	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:300)

Are there plans to support this?

We haven’t decided whether we’re going to provide support for this yet. Doing so is not easy - so we’re waiting to see whether there’s a great demand for it.

What’s your use case for this kind of transaction? It’d be helpful for us to understand so we can improve our internal prioritization for this feature.

I am the author of Sqlg which is a implementation of TinkerPop (graph semantics) on a RDBMS backend. Currently it supports Postgresql, Hsqldb, H2 and MariaDB. I am starting with an implementation on Cockroachdb.

TinkerPop has Nosql/No schema semantics. Which means the system just creates whatever it needs on the fly and if supported do so transactionally.

Postgres is the only database that supports proper transactional ddl statements making it a joy to work with. In many ways transactional ddl support makes Postgresql behave like people expect from nosql/no schema databases with all the benefits of actually having a schema and ACID transactions.

This means that at anytime within a transaction the user may which to add a brand new label in TinkerPop’s graphs language which translates to a new table in sql language or a new property, i.e. a new column. For now the work around is for me to manually commit the transaction before any ddl statement. This violates the user’s transaction boundary but makes the nosql/no schema behaviour possible.

As an aside, Hsqldb, H2 and MariaDB do not support transactional ddl statements. However they all silently commit the transaction and immediately start a new one when a ddl statement is executed. This also violates the user’s transaction boundaries but makes it easy to work with. Where Cockroachdb now throws an exception and breaks my codebase they would have silently violated the transaction but continued going.