APL ⎕SQL Interaction with Transactional DDL in PostgreSQL

Answer

Yes – if the APL driver supports explicit transaction control, you can wrap a DDL statement in a BEGIN … COMMIT block and roll it back. PostgreSQL treats DDL as ordinary SQL inside a transaction, so the change is only committed when COMMIT is issued.

How it works

1. Start a transaction – BEGIN. 2. Send the DDL – e.g., CREATE TABLE …. 3. Commit or rollback – COMMIT to persist, ROLLBACK to undo.

PostgreSQL holds the DDL until the transaction ends. If the driver auto‑commits each statement, the BEGIN will have no effect and the DDL will be permanently applied. Therefore, verify that autocommit is disabled or that the driver honors BEGIN and COMMIT.

Exact APL command sequence

// 1. Start a transaction
⎕SQL⎕EXECUTE 'BEGIN';

// 2. Issue a DDL statement
⎕SQL⎕EXECUTE 'CREATE TABLE t_test(id INT);';

// 3. Commit the transaction
⎕SQL⎕EXECUTE 'COMMIT';

// 4. Verify the table exists (outside APL)
SELECT * FROM t_test;

To test rollback:

// Start a transaction
⎕SQL⎕EXECUTE 'BEGIN';

// Issue DDL
⎕SQL⎕EXECUTE 'CREATE TABLE t_temp(id INT);';

// Rollback instead of commit
⎕SQL⎕EXECUTE 'ROLLBACK';

// Verify the table was not created
SELECT * FROM t_temp;  -- should return 0 rows or error

Things to verify before relying on this pattern

• PostgreSQL version – transactional DDL is supported from 9.0 onwards.
• Driver autocommit behavior – most JDBC‑style drivers honour BEGIN and COMMIT, but some legacy APL providers may auto‑commit each statement.
• Locking – a transaction that holds a DDL lock can block other sessions; plan accordingly.
• WAL usage – large DDL changes consume transaction log space; monitor if you run many such operations.

Missing diagnostic detail

To give a final recommendation, we need to know whether your APL driver defaults to autocommit. If it does, the BEGIN/COMMIT sequence will have no effect. Please confirm:

Does your APL ⎕SQL driver automatically commit each statement, or does it honor explicit BEGIN and COMMIT commands?

Conclusion

When the driver supports explicit transactions, PostgreSQL will allow you to roll back DDL changes made via ⎕SQL⎕EXECUTE. If autocommit is forced, you cannot rely on rollback for DDL and must use database‑specific features or manual cleanup.