PostgreSQL reserved word column names w/ guarded attributes broken in native column attributes implementation

[damiantw]

Laravel Version

10.30.0

PHP Version

8.2.11

Database Driver & Version

PostgreSQL 15.1

Description

The native column attributes implementation introduced in #48357 has broken inserts using Postgres models with guarded attributes and column names matching PostgreSQL reserved words in my application.

The compileColumns() query in the Postgres schema grammar makes use of PostgreSQL's quote_ident() function which quotes reserves words.

SELECT quote_ident('foo') as value; -> foo
SELECT quote_ident('end') as value; -> "end"

This is causing the GuardsAttributes trait to improperly detect fillable columns.

Changing select quote_ident(a.attname) as name to select a.attname as name in the Postgres Grammar resolves the issue, but I'm not sure if there are other downstream consequences to that.

Ex:

Given a table like the following:

Schema::create('my_table', function (Blueprint $table) {
   $table->id();
   $table->string('label');
   $table->timestamp('start');
   $table->timestamp('end');
   $table->boolean('analyze');
});

The output of Schema::getColumns('my_table'); is the following.

array:5 [
  0 => array:8 [
    "name" => "id"
    "type_name" => "int8"
    "type" => "bigint"
    "collation" => null
    "nullable" => false
    "default" => null
    "auto_increment" => true
    "comment" => null
  ]
  1 => array:8 [
    "name" => "label"
    "type_name" => "varchar"
    "type" => "character varying(255)"
    "collation" => null
    "nullable" => false
    "default" => null
    "auto_increment" => false
    "comment" => null
  ]
  2 => array:8 [
    "name" => "start"
    "type_name" => "timestamp"
    "type" => "timestamp(0) without time zone"
    "collation" => null
    "nullable" => false
    "default" => null
    "auto_increment" => false
    "comment" => null
  ]
  3 => array:8 [
    "name" => ""end""
    "type_name" => "timestamp"
    "type" => "timestamp(0) without time zone"
    "collation" => null
    "nullable" => false
    "default" => null
    "auto_increment" => false
    "comment" => null
  ]
  4 => array:8 [
    "name" => ""analyze""
    "type_name" => "bool"
    "type" => "boolean"
    "collation" => null
    "nullable" => false
    "default" => null
    "auto_increment" => false
    "comment" => null
  ]
]

Steps To Reproduce

class LaravelTest extends TestCase
{
    public function testInsertRecordWithReservedWordFieldName()
    {
        Schema::create('my_table', function (Blueprint $table) {
            $table->id();
            $table->string('label');
            $table->timestamp('start');
            $table->timestamp('end');
            $table->boolean('analyze');
        });

        $model = new class extends \Illuminate\Database\Eloquent\Model {
            protected $table = 'my_table';
            protected $guarded = ['id'];
            public $timestamps = false;
        };
        
        $result = $model::query()->create([
            'label' => 'test',
            'start' => '2023-01-01 00:00:00',
            'end' => '2024-01-01 00:00:00',
            'analyze' => true,
        ]);

        $this->assertTrue($result->exists);
    }
}

Fails with error...

SQLSTATE[23502]: Not null violation: 7 ERROR:  null value in column "end" of relation "my_table" violates not-null constraint
DETAIL:  Failing row contains (1, test, 2023-01-01 00:00:00, null, null). (Connection: pgsql, SQL: insert into "my_table" ("label", "start") values (test, 2023-01-01 00:00:00) returning "id")

Passes under Laravel 10.29.0.

[scottzirkel]

Seems like a side-effect of this fix is that the compileColumns query now appears in tools like debugbar. It may be a "cosmetic issue" only, but in the debugbar timeline those queries not only appear but seem to be a bit slower than the rest.