DatabaseRefresh with Postgres Multiple Schemas is not dropping all tables

[jhjm32087]

• Laravel Version: 8.25.0
• PHP Version: 8.0
• Database Driver & Version:
• Postgres 13.1 & Latest

Description:

I have a Postgres database with two schemas and during Unit Testing, with the RefreshDatabase Trait. It's dropping all tables correctly, but only to the default connection schema and leaving the rest of the tables.

This seems related to PostgresBuilder.php calling the getAllTables function and only using $this->connection->getConfig('schema') while only allows/filters the default connection schema.

Steps To Reproduce:

`CREATE SCHEMA s1;
CREATE SCHEMA s2;

SET SEARCH_PATH TO s1;

CREATE table test1
(
id INT
);

SET SEARCH_PATH TO s2;

CREATE table test2
(
id INT
);`

php artisan db:wipe

[jhjm32087]

I've looked into the issue and it's possible to include the schema without using the actual connection schema, but there might be some need/requirement to exclude certain schema like partman which is a Postgres Partition Extention.

/**
 * Compile the SQL needed to retrieve all table names.
 *
 * @param  string|array  $schema
 * @return string
 */
public function compileGetAllTables($schema)
{
    return "select schemaname || '.' || tablename from pg_catalog.pg_tables where schemaname not in ('pg_catalog', 'partman', 'information_schema')";
}

/**
 * Compile the SQL needed to retrieve all view names.
 *
 * @param  string|array  $schema
 * @return string
 */
public function compileGetAllViews($schema)
{
    return "select schemaname || '.' || viewname from pg_catalog.pg_views where schemaname not in ('pg_catalog', 'partman', 'information_schema')";
}

/**
 * Compile the SQL needed to retrieve all type names.
 *
 * @return string
 */
public function compileGetAllTypes()
{
    return "SELECT n.nspname || '.' || t.typname
            FROM pg_type t
                     INNER JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
            WHERE (t.typrelid = 0 OR ( SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid ))
              AND NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
              AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'partman')";
}

[driesvints]

If I understand correctly you're making use of multiple database connections? I don't think that works well with schema dumping?

[laurencei]

I was involved in some of the PR stuff for doing. The default of multiple schemas was not included by design. In most scenarions, you would only dump your main schema, and if you were accessing other databases you wouldnt want to be dumping those. If Laravel tried to dump every database connection it knew about, chaos would ensure.

There is a flag for the command you can do: artisan migrate:fresh --database==xxxx

So if you have a scenario where you need to always dump 2 diff databases, I would do it as a batched call to both, that should solve you need case if I understand it correctly?

[jhjm32087]

@laurencei, This is on one database connection and it's connected a Postgres Database, but it can be applied to MS SQL Server. I guess the root issue stems from the Database Structure and Unit/Feature Testing with DatabaseRefresh Trait, which calls the db:wipe artisan command, which then drops tables/view etc.. of the primary schema in this case its core. Then it tries to reapply the migrations, but since the not all database tables/views/types etc have not been dropped the migration fails during the test suite.

I would expect the behavior to be similar to as follows at the very least for Testing

• Database Wipe(All Schemas Tables/Views etc..) -> Migrate the database using each up of the migration.

Database Structure

• Postgres(Database)
  • Core (Schema)
    • Test1(Table)
    • migrations(table)
  • Source(Schema)
    • Test2(Table)

cc/ @driesvints

[driesvints]

Ah it seems this is already fixed but pending today's release: #36046

[jhjm32087]

@driesvints, I just upgraded to the latest version 8.26.1 this evening. It does seem to have fixed the issue while the running of the tests on the first round, but on the second it seems like it fails because it left behind a table.

[JorgenSolli]

Facing the same issue with Laravel 8.71
Every other test run is failing because a table already exists during the migration period. Run it again and then everything is fine.

This is using PostgreSQL 13.5

[localusercamp]

Same here, Laravel 10.18 and latest Postgres, very annoying.
Any thoughts @driesvints?