Incorrect deferred handling with Postgres

[markuskooche]

I created a table according to the following principle:

| id | post_id |      post_type      | picture_path | sequence |
|----|---------|---------------------|--------------|----------|
|  1 |       1 |  App/.../PublicPost |   pic_01.jpg |        1 |
|  2 |       1 |  App/.../PublicPost |   pic_02.jpg |        2 |

It should not be possible to use the same sequence at the same post twice.
I have written this on the migration as follows:

Schema::create('locations', function (Blueprint $table) {
    $table->id();
    $table->int('post_id');
    $table->string('post_type');
    $table->int('sequence');
    $table->unique(['post_id', 'post_type', 'sequence']);
    // ...
});

However, it should be possible to swap the sequences.
Therefore, you need to use a transaction, which looks like this in plain SQL:

BEGIN;
    UPDATE post_pictures SET sequence = 2 WHERE id = 1;
    UPDATE post_pictures SET sequence = 1 WHERE id = 2;
COMMIT;

You have to create the UNIQUE key as follows:

UNIQUE (post_id, post_type, sequence)
    DEFERRABLE INITIALLY DEFERRED

However, this is exactly where the problem is. You can easily apply this to a primary key with the following function:

$table->foreignId('foo')->deferrable();

This works because it is already observed in the primary keys.
https://github.com/laravel/framework/blob/9.x/src/Illuminate/Database/Schema/ForeignKeyDefinition.php#L8

framework/src/Illuminate/Database/Schema/Grammars/PostgresGrammar.php

Lines 217 to 241 in 4c7cd8c

	/**
	* Compile a foreign key command.
	*
	* @param \Illuminate\Database\Schema\Blueprint $blueprint
	* @param \Illuminate\Support\Fluent $command
	* @return string
	*/
	public function compileForeign(Blueprint $blueprint, Fluent $command)
	{
	$sql = parent::compileForeign($blueprint, $command);
	if (! is_null($command->deferrable)) {
	$sql .= $command->deferrable ? ' deferrable' : ' not deferrable';
	}
	if ($command->deferrable && ! is_null($command->initiallyImmediate)) {
	$sql .= $command->initiallyImmediate ? ' initially immediate' : ' initially deferred';
	}
	if (! is_null($command->notValid)) {
	$sql .= ' not valid';
	}
	return $sql;
	}

However, not with the UNIQUE Keys.

framework/src/Illuminate/Database/Schema/Grammars/PostgresGrammar.php

Lines 146 to 160 in 4c7cd8c

	/**
	* Compile a unique key command.
	*
	* @param \Illuminate\Database\Schema\Blueprint $blueprint
	* @param \Illuminate\Support\Fluent $command
	* @return string
	*/
	public function compileUnique(Blueprint $blueprint, Fluent $command)
	{
	return sprintf('alter table %s add constraint %s unique (%s)',
	$this->wrapTable($blueprint),
	$this->wrap($command->index),
	$this->columnize($command->columns)
	);
	}

It would be great if this could then also be called quite simply as follows:

$table->unique(['post_id', 'post_type', 'sequence'])->deferrable();