Bug: Postgresql createTable if not exists with multiple schemas

[mawi1077]

PHP Version

8.1

CodeIgniter4 Version

4.2.4

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

fpm-fcgi

Database

PostgreSQL 13.7

What happened?

to use multiple schema I set in database config 'schema' => 'schema1,schema2' that's working fine so far.

but when use createTable with option "if not exist" then I get Error Table Exists that happens because the query in Connection::_listTables has an syntax error in WHERE CLAUSE

Steps to Reproduce

'schema' => 'schema1,schema2' $forge = \Config\Database::forge(); $forge->createTable($tablename, TRUE);

Expected Output

the function should return object or false, without generating an error

Anything else?

No response

[kenjis]

Thank you for the report.

But the Steps to Reproduce can not run. Please provide minimum running code to reproduce the error. And you didn't provide the exact error message, too.

'schema' => 'schema1,schema2'
$forge = \Config\Database::forge();
$forge->createTable($tablename, TRUE);

[mawi1077]

Error Message: ErrorException

pg_query(): Query failed: ERROR: relation "dummy" already exists

Backtrace:

1. {PHP internal code}   —  CodeIgniter\Debug\Exceptions->errorHandler ()

2. SYSTEMPATH/Database/Postgre/Connection.php : 140   —   pg_query()

3. SYSTEMPATH/Database/BaseConnection.php : 693   —  CodeIgniter\Database\Postgre\Connection->execute () 
4. SYSTEMPATH/Database/BaseConnection.php : 620   —  CodeIgniter\Database\BaseConnection->simpleQuery () 
5. SYSTEMPATH/Database/Forge.php : 509   —  CodeIgniter\Database\BaseConnection->query () 
6. APPPATH/Models/Mxxx.php : xx   —  CodeIgniter\Database\Forge->createTable ()

Reproduce function

    function checkOrCreateTable( $tablename )
     {
        $forge = \Config\Database::forge();
        $fields = array(
               'id' => array( 'type' => 'int',
                              'constraint' => 5, 
                              'auto_increment' => TRUE),
                'name' => array( 'type' => 'VARCHAR','constraint' => '50', 'auto_increment' => FALSE),
                );
          $forge->addField( $fields );
          $forge->addKey('id');
          $forge->createTable($tablename, TRUE);
}

1. call table get created
2. call the table exists and should not get created, but when in database config 'schema' => 'schema1,schema2',

[kenjis]

How did you call checkOrCreateTable()? What's the exact parameter value?

[mawi1077]

public function testDatabase(){
	$this->Mxxxx->checkOrCreateTable("dummy");
}

First call is creating table without error. second call generates error. but when in database config 'schema' => 'schema1,schema2',

[mawi1077]

i fix it for me with an dirty hack in Connection.php 208 _listTables(bool $prefixLimit = false): string

   $array = explode(',', $this->schema); 
    $whc = '';
    foreach ($array as $sc){
        if ($whc != '' ) $whc .= " OR ";
        $whc .= '"table_schema" = \'' . $sc . "'";
    } 
    $sql = 'SELECT "table_name" FROM "information_schema"."tables" WHERE '.$whc;

[kenjis]

Is 'schema' => 'schema1,schema2' valid config? It is a string and the default value is public. https://github.com/codeigniter4/CodeIgniter4/blob/57385ea1bf64fb31c235f9c15eff4d39a69cbbc1/system/Database/Postgre/Connection.php#L32-L36

[mawi1077]

It's part of the configuration. app/config/database.php In the documentation is not documented how to handle multiple schemas. The code shows no clear handling of this data. In the Connection::connect function the search path is set to $schema extended with "public". In Connection::_listTables only the $schema is used. At least with the default settings the search path get set to "public, public".

[kenjis]

It is $schema, not $schemas. I don't think 'schema1,schema2' is a valid value.

At least with the default settings the search path get set to "public, public".

It seems a bug, but no harm.

[sclubricants]

Forge is designed to use one schema at a time. We could add a method to set schema but this could get messy. This listTables() method would have have to use schema and table as cache names. But like I said this gets messy.

I'd recommend using a separate connection for each schema.

The same table name can exist in multiple schemas in the same database. Without selecting a specific schema Forge would not know what schema you are trying to work with when trying to create tables etc.

https://www.postgresql.org/docs/current/ddl-schemas.html

[kenjis]

No plan to support multiple schema. Close.