TYPE not being dropped on db:wipe with psql connection

[andreas-eisenmann]

Laravel Version

10.14.1

PHP Version

8.1.2

Database Driver & Version

psql (PostgreSQL) 15.3 (Debian 15.3-1.pgdg110+1)

Description

Using a PostgreSQL connection, a previously created type won't be dropped when artisan db:wipe --drop-types is called. As a result, when artisan migrate:fresh --drop-types is being called and a type is being defined in a migration, it will result in an error:

SQLSTATE[42710]: Duplicate object: 7 ERROR:  type "foo" already exists

Looking at \Illuminate\Database\Schema\Grammars\PostgresGrammar::compileGetAllTypes the root cause is obvious:

https://github.com/illuminate/database/blob/ae346d0a75aa87b83783d44b926d3fcc2d18f430/Schema/Grammars/PostgresGrammar.php#L455

select distinct pg_type.typname from pg_type inner join pg_enum on pg_enum.enumtypid = pg_type.oid

This statement will only select enum types. If you replace this statement by following statement, everything works fine:

SELECT      n.nspname as schema, t.typname as type
FROM        pg_type t
LEFT 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');

Steps To Reproduce

Create a migration:

<?php

use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;

return new class extends Migration
{
    /**
     * Run the migrations.
     */
    public function up(): void
    {
        DB::unprepared(<<<'EOF'
CREATE TYPE "foo" AS (
    bar TEXT
);
EOF);
    }

    /**
     * Reverse the migrations.
     */
    public function down(): void
    {
        DB::unprepared(<<<'EOF'
DROP TYPE "foo";
EOF
);
    }
};

Call artisan migrate:fresh --drop-types TWICE, the second time you will get this error:

   Illuminate\Database\QueryException 

  SQLSTATE[42710]: Duplicate object: 7 ERROR:  type "foo" already exists (Connection: pgsql, SQL: CREATE TYPE "foo" AS (
    bar TEXT
);)

  at vendor/laravel/framework/src/Illuminate/Database/Connection.php:795
    791▕         // If an exception occurs when attempting to run a query, we'll format the error
    792▕         // message to include the bindings with SQL, which will make this exception a
    793▕         // lot more helpful to the developer instead of just the database's errors.
    794▕         catch (Exception $e) {
  ➜ 795▕             throw new QueryException(
    796▕                 $this->getName(), $query, $this->prepareBindings($bindings), $e
    797▕             );
    798▕         }
    799▕     }

      +7 vendor frames 
  8   database/migrations/2023_11_30_144022_foo.php:15
      Illuminate\Support\Facades\Facade::__callStatic()

      +36 vendor frames 
  45  artisan:35
      Illuminate\Found

Let's try something:

$ sail tinker
> DB::unprepared(<<<'EOF'
. CREATE TYPE "foo" AS (
.     bar TEXT
. );
. EOF);
= true
> DB::select("select distinct pg_type.typname from pg_type inner join pg_enum on pg_enum.enumtypid = pg_type.oid");
= []
> DB::select(<<<'EOF'
. SELECT      n.nspname as schema, t.typname as type
. FROM        pg_type t
. LEFT 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');
. EOF);
= [
    {#8178
      +"schema": "public",
      +"type": "foo",
    }
  ]

[crynobone]

Seem like the feature was initially designed to cover using Enum for Type: #28382

[github-actions]

Thank you for reporting this issue!

As Laravel is an open source project, we rely on the community to help us diagnose and fix issues as it is not possible to research and fix every issue reported to us via GitHub.

If possible, please make a pull request fixing the issue you have described, along with corresponding tests. All pull requests are promptly reviewed by the Laravel team.

Thank you!

[andreas-eisenmann]

Thanks a lot 🙂