Commit after schema creation for GC tool

[Highstead]

By default the connections from the JDBC datasource do not have autocommit on, so when we create schema the results of that creation are then rolled back. This change is intentional about committing the schema changes.

[Highstead]

I dont know if this is desirable or by design? But to me it seems like if i want to create a schema explicitly in the command prompt it should be committed to the database.

[adutra]

Hi @Highstead thanks for reporting this. What database are you using? I am a bit surprised because DDL statements are usually committed right away (regardless of JDBC's auto-commit flag), and cannot be rolled back.

[Highstead]

I'm running Google's cloudsql in postgresql 15. This functionality maybe specific to google's cloudsql postgres 15?

My setup is I port-forwarded localhost:5432 to cloudsql proxy(cloud-sql-proxy:2.7.0) inside the nessie's namespace.
ex: kubectl -n nessie port-forward service/sqlproxy 5432:5432

Then its just a simple follow the tutorial and watch it not apply the transactions. Below is the logs:

java -jar nessie-gc.jar create-sql-schema --jdbc-url jdbc:postgresql://localhost:5432/nessie_gc --jdbc-user nessie --jdbc-password password
09:44:44,931 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Could NOT find resource [logback-test.xml]
09:44:44,931 |-INFO in ch.qos.logback.classic.LoggerContext[default] - Found resource [logback.xml] at [jar:file:/Users/highstead/environments/localhost/nessie-gc.jar!/logback.xml]
09:44:44,935 |-INFO in ch.qos.logback.core.joran.spi.ConfigurationWatchList@50a7bc6e - URL [jar:file:/Users/highstead/environments/localhost/nessie-gc.jar!/logback.xml] is not of type file
09:44:44,973 |-INFO in ch.qos.logback.classic.joran.action.LoggerContextListenerAction - Adding LoggerContextListener of type [ch.qos.logback.classic.jul.LevelChangePropagator] to the object stack
09:44:44,973 |-INFO in ch.qos.logback.classic.jul.LevelChangePropagator@23282c25 - Propagating DEBUG level on Logger[ROOT] onto the JUL framework
09:44:44,975 |-INFO in ch.qos.logback.classic.joran.action.LoggerContextListenerAction - Starting LoggerContextListener
09:44:44,975 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - About to instantiate appender of type [ch.qos.logback.core.ConsoleAppender]
09:44:44,976 |-INFO in ch.qos.logback.core.joran.action.AppenderAction - Naming appender as [console]
09:44:44,978 |-INFO in ch.qos.logback.core.joran.action.NestedComplexPropertyIA - Assuming default type [ch.qos.logback.classic.encoder.PatternLayoutEncoder] for [encoder] property
09:44:44,984 |-INFO in ch.qos.logback.classic.jul.LevelChangePropagator@23282c25 - Propagating INFO level on Logger[ROOT] onto the JUL framework
09:44:44,984 |-INFO in ch.qos.logback.classic.joran.action.LevelAction - ROOT level set to INFO
09:44:44,984 |-INFO in ch.qos.logback.core.joran.action.AppenderRefAction - Attaching appender named [console] to Logger[ROOT]
09:44:44,984 |-INFO in ch.qos.logback.classic.joran.action.ConfigurationAction - End of configuration.
09:44:44,985 |-INFO in ch.qos.logback.classic.joran.JoranConfigurator@21de60b4 - Registering current configuration as safe fallback point
Nessie GC tables for live-content-set storage created.
 ~/environments/localhost>  psql
psql (14.9 (Homebrew), server 15.5)
WARNING: psql major version 14, server major version 15.
         Some psql features might not work.
Type "help" for help.

nessie_gc=> \dt
Did not find any relations.

So with this line of code, i see the tables committed afterwards. Without it i see nothing.
Additionally If i manually create the tables and run main branch implementation of create schema i get an error suggesting the tables already exist.

So its not like i've put it into a different database/schema.

If this breaks any other implementation i'm happy to just close.

[dimas-b]

IIRC, PostreSQL, indeed, may put DDL in a normal Tx and not commit automatically.

[adutra]

TIL Postgres has transactional DDL :-)

The changes make totally sense, thanks for fixing this @Highstead!

[adutra]

Per #8282 the CLA has been signed.