Document how to use DML and DDL batches with PGAdapter

[olavloite]

DML and DDL batches are important features to improve the performance when working with PGAdapter. The documentation should clearly show how users can use these features.

[ltoshev]

For me they just don't work, I tried with the following code:

func executeBatched(conn *pgx.Conn) {
	body := readFile("000001_init.up.sql")

	batch := &pgx.Batch{}
	sql := strings.Split(string(body), ";")

	for _, element := range sql {
		element = strings.Replace(element, "//", "--", -1)
		log.Println(element)
		batch.Queue(element)
	}
	br := conn.SendBatch(context.Background(), batch)
	_, err := br.Exec()

	if err != nil {
		log.Fatalf("Batch failed: %v", err)
	}
} ```
and I get this error:

2024/04/18 11:47:29 Batch failed: ERROR: DDL statements are not allowed in mixed batches or transactions. (SQLSTATE 25000)
make: *** [run] Error 1

My entire sql is formed of DDL, example:

CREATE SEQUENCE IF NOT EXISTS seq_merchants bit_reversed_positive;
CREATE SEQUENCE IF NOT EXISTS seq_users bit_reversed_positive;
CREATE SEQUENCE IF NOT EXISTS seq_roles bit_reversed_positive;

CREATE TABLE IF NOT EXISTS merchants (
  merchant_id   varchar(36) DEFAULT spanner.generate_uuid() NOT NULL,
  seq_id bigint DEFAULT nextval('seq_merchants'),
  name  varchar(255) NOT NULL,
  created   timestamptz DEFAULT CURRENT_TIMESTAMP,
  created_by varchar(36),
  modified  timestamptz DEFAULT CURRENT_TIMESTAMP,
  modified_by varchar(36),
  PRIMARY KEY(merchant_id)
);

CREATE TABLE IF NOT EXISTS users (
  user_id   VARCHAR(36) DEFAULT spanner.generate_uuid() NOT NULL,
  seq_id bigint DEFAULT nextval('seq_users'),
  fname  VARCHAR(30) NOT NULL,
  lname  VARCHAR(30) NOT NULL,
  email  VARCHAR(255) NOT NULL,
  mobile VARCHAR(30),
  country VARCHAR(90),
  timezone VARCHAR(128),
  lang VARCHAR(2) DEFAULT 'en' NOT NULL,
  is_enabled   BOOL DEFAULT TRUE,
  created   timestamptz DEFAULT CURRENT_TIMESTAMP,
  created_by VARCHAR(36),
  modified  timestamptz DEFAULT CURRENT_TIMESTAMP,
  modified_by VARCHAR(36),
  PRIMARY KEY (user_id)
);

CREATE UNIQUE INDEX idx_uq_email ON users(email);

[olavloite]

@ltoshev The error seems to indicate that your connection is in an active transaction. Spanner does not support DDL statements in a transaction, which is why you are getting this error.

I've just added two tests using pgx to execute DDL batches:

1. A test that executes a DDL batch without a transaction. This works.
2. A test that executes a DDL batch in a transaction. This fails with the same error as your application

[ltoshev]

Hi, yes I found out that I have to start the adapter with -ddl=AutocommitImplicitTransaction flag to be able to run the batches. It's good you added tests. Do you know when will be next release of the adapter and what could we expect as features?

[olavloite]

Hi, yes I found out that I have to start the adapter with -ddl=AutocommitImplicitTransaction flag to be able to run the batches.

You're right that this flag will also fix the problem, however it does that by automatically committing the ongoing transaction before executing the DDL batch. Note that the test mentioned above does not use that flag. Instead, it just makes sure that there is no ongoing transaction on the connection before executing the DDL batch. The fact that you need the flag might indicate that there is something unexpected going on in your application. (Or are you using some framework on top of pgx that manages the transactions for you?)

Do you know when will be next release of the adapter and what could we expect as features?

The next release is likely to be early next week. There are no big features planned for the near future. One planned change is logging. Currently, all logs are sent to stderr (this is a quirk of the logging framework java.util.logging that is used by PGAdapter). We will modify this to redirect informational log output to stdout and only warnings and errors to stderr.

Are there any specific features you are looking for / missing? If so, please feel free to open a feature request in this repository.