PostgreSQL, create new database and transactions

[antoineL]

Hi,

This is the very fiorst time I am using PostgreSQL, so the likeliest possibility is that I missed something.
I am trying to create a new Gestinux instance on a PostgreSQL server (I am using 9.2.24 if you wonders; also I rebuild the cluster to use UTF-8 as encoding).
I have a connection between the program and the server. I go to the Database menu, select postgresql as protocol, gestinux as database name, feed user and password. I got as expected a validation because that database is "not created or not visible to me. OK to create?" Fine, let's try...

No. I cannot create it.

(SQL Error: ERROR: current transaction is aborted, commands ignored until end of transaction block)

I went a bit deeper using the development version, and enabled some logging.
This is what the (regular) server logfile has to say (after switching from Spanish to English):

ERROR: CREATE DATABASE cannot run inside a transaction block
STATEMENT: CREATE DATABASE "gestinux" WITH ENCODING='UTF8' CONNECTION LIMIT=-1

ERROR: current transaction is aborted, commands ignored until end of transaction block
STATEMENT: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED

This is what is registered within SqlDebug.log file:

CREATE DATABASE "gestinux" WITH ENCODING='UTF8' CONNECTION LIMIT=-1
SQL Error: ERROR: CREATE DATABASE cannot run inside a transaction block

And this is what I got with a TZSQLMonitor put in the Database form:

Code: Select all

2021-10-19 17:17:39 cat: Connect, proto: postgresql-9, msg: CONNECT TO "postgres" AS USER ""
2021-10-19 17:17:39 cat: Transaction, proto: postgresql-9, msg: BEGIN
2021-10-19 17:17:39 cat: Execute, proto: postgresql-9, msg: select setting from pg_settings where name = 'standard_conforming_strings'
2021-10-19 17:17:39 cat: Execute, proto: postgresql-9, msg: select setting from pg_settings where name = 'bytea_output'
2021-10-19 17:17:39 cat: Execute, proto: postgresql-9, msg: SELECT version()
2021-10-19 17:17:39 cat: Prepare, proto: postgresql-9, msg: Statement 1 : SELECT datname FROM pg_database WHERE datname <> 'postgres' AND datname NOT LIKE 'template%'

2021-10-19 17:17:39 cat: Execute prepared, proto: postgresql-9, msg: Statement 1
2021-10-19 17:17:39 cat: Prepare, proto: postgresql-9, msg: Statement 2 : SELECT n.nspname,c.relname,a.attname,a.atttypid,a.attnotnull,a.atttypmod,a.attlen,a.attnum,pg_get_expr(def.adbin, def.adrelid) as adsrc,dsc.description, dn.nspname as cnspname FROM pg_catalog.pg_namespace n  JOIN pg_catalog.pg_class c ON (c.relnamespace = n.oid)  JOIN pg_catalog.pg_attribute a ON (a.attrelid=c.oid)  LEFT JOIN pg_catalog.pg_attrdef def ON (a.attrelid=def.adrelid AND a.attnum = def.adnum) LEFT JOIN pg_catalog.pg_description dsc ON (c.oid=dsc.objoid AND a.attnum = dsc.objsubid)  LEFT JOIN pg_catalog.pg_class dc ON (dc.oid=dsc.classoid AND dc.relname='pg_class')  LEFT JOIN pg_catalog.pg_namespace dn ON (dc.relnamespace=dn.oid AND dn.nspname='pg_catalog')  WHERE a.attnum > 0 AND c.oid = 1262 ORDER BY nspname,relname,attnum
2021-10-19 17:17:39 cat: Execute prepared, proto: postgresql-9, msg: Statement 2
2021-10-19 17:17:39 cat: Execute, proto: postgresql-9, msg: SELECT oid, typname, typbasetype,typtype FROM pg_type WHERE (typtype = 'b' and oid < 10000) OR typtype = 'p' OR typtype = 'e' OR typbasetype<>0 ORDER BY oid
2021-10-19 17:17:42 cat: Transaction, proto: postgresql-9, msg: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED
2021-10-19 17:17:42 cat: Prepare, proto: postgresql-9, msg: Statement 3 : CREATE DATABASE "gestinux" WITH ENCODING='UTF8' CONNECTION LIMIT=-1

2021-10-19 17:17:42 cat: Execute, proto: postgresql-9, msg: CREATE DATABASE "gestinux" WITH ENCODING='UTF8' CONNECTION LIMIT=-1
, errcode: 0, error: ERROR:  CREATE DATABASE cannot run inside a transaction block

2021-10-19 17:17:49 cat: Execute, proto: some PostgreSQL protocol, msg: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED, errcode: 0, error: ERROR:  current transaction is aborted, commands ignored until end of transaction block

I see there is code within TGDbConnection.CreateDatabase_PgSql (lines 420-450 of util/gdbconnection.pas) which may be relevant, but I do not understand what is the purpose of it:

Code: Select all

procedure TGDbConnection.CreateDatabase_PgSql(const aDatabaseName: string);
var
  OldTransactIsolationLevel: TZTransactIsolationLevel;
begin
  try
    OldTransactIsolationLevel := TransactIsolationLevel;
    TransactIsolationLevel := tiNone;
    // create database, connected to server
    ExecSqlDataDefinition('CREATE DATABASE "' + aDatabaseName +
      '" WITH ENCODING=''UTF8'' CONNECTION LIMIT=-1', aDatabaseName, 'ErrorCreatingDatabase');
    // must connect to the created database, not to the server
    Connected := False;
    Database := aDatabaseName;
    Connected := True;
    // create language if not existing
    if not ExistingQuery('SELECT 1 FROM pg_language WHERE lanname = ' +
      StringSql('plpgsql')) then
      ExecSqlRaise(...

I tried the obvious (?) hack to move the TransactIsolationLevel := tiNone; instruction to occur after the CREATE DATABASE statement, but it does not work.

[antoineL]

Mmmm... Did not try enough, it seems.

After a half-minute session in Google, I found https://stackoverflow.com/questions/26482777 which clearly yells for having AutoCommit to be ON when creating a PostgreSQL database.

The following patch let me pass over the stumble:

Code: Select all

--- backup/gdbconnection.pas.bak	2021-10-19 18:24:52.273170900 +0200
+++ gdbconnection.pas	2021-10-19 18:17:39.698163400 +0200
@@ -453,9 +453,11 @@
   try
     OldTransactIsolationLevel := TransactIsolationLevel;
     TransactIsolationLevel := tiNone;
+setAutoCommit(True);
     // create database, connected to server
     ExecSqlDataDefinition('CREATE DATABASE "' + aDatabaseName +
       '" WITH ENCODING=''UTF8'' CONNECTION LIMIT=-1', aDatabaseName, 'ErrorCreatingDatabase');
+setAutoCommit(False);
     // must connect to the created database, not to the server
     Connected := False;
     Database := aDatabaseName;

Please note that I only tested on a 9.2 server, which is quite old now, and probably not representative of what is usually in use these days (but I have seen in the wiki that 9.3 is the upper version to use as a client.)

[tintinux]

Hi

I have not tested for several years with PostgreSQL and I have no more an installed server.
I think that Gastounet has tested with this server last year, but I'm afraid he is not very active on Gestinux currently.

I'm sure the present code was working in the past, and that the issue is due to a change in PostgreSql.
I would say an unintentional change because I can't see any reason for it !

Anyway, we must adapt, and the patch proposed should work on any older and future versions, since it is limited to one CREATE DATABASE instruction.

Do you want to commit it yourself in Gestinux 1.6 ? (maybe later in 1.5-stable-2, after more tests)

Regards