CodeIgniter4.4.5 Error when updateBatch() a single record containing TS TZ columns (PostgreSQL 9.2)

PHP 7.4.33
CodeIgniter 4.4.5
- Overwrite https://github.com/codeigniter4/CodeIgniter4/pull/8524/files
PostgreSQL 9.2.24, 13.11

複合主キーテーブル

CREATE TABLE test_table
(
    id1       integer not null,
    id2       integer not null,
    char      char(10),
    varchar   varchar(10),
    timestamp timestamp(0) with time zone,
    primary key (id1, id2)
);

naente 2ヶ月前に更新

複合主キーテーブルを扱う為、CodeIgniter Model は使用しない

app\Models\TestTableModel.php

<?php

namespace App\Models;

use CodeIgniter\Database\BaseBuilder;
use CodeIgniter\Database\BaseConnection;

class TestTableModel
{
    /**
     * @var BaseConnection
     */
    protected BaseConnection $db;

    /**
     * @var BaseBuilder
     */
    private BaseBuilder $builder;

    public function __construct()
    {
        $this->db = db_connect();
        $this->builder = $this->db->table('test_table');
    }

    /**
     * @param $set
     * @return void
     */
    public function insertBatch($set)
    {
        $this->builder->insertBatch($set);
    }

    /**
     * @param $set
     * @param $constraints
     * @return void
     */
    public function updateBatch($set, $constraints)
    {
        $this->builder->updateBatch($set, $constraints);
    }
}

naente 2ヶ月前に更新

app\Controllers\TestController.php

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\TestTableModel;
use CodeIgniter\HTTP\ResponseInterface;

class TestController extends BaseController
{
    public function index()
    {
        //
    }

    public function testInsertBatch1()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 1,
                'char'      => 'Foo',
                'varchar'   => 'Foo',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 2,
                'char'      => 'Bar',
                'varchar'   => 'Bar',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 3,
                'char'      => 'Baz',
                'varchar'   => 'Baz',
                'timestamp' => 'now()',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch2()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 4,
                'char'      => 'Qux',
                'varchar'   => 'Qux',
                'timestamp' => 'now()',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testUpdateBatch1()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 1,
                'char'      => 'Modify Foo',
                'varchar'   => 'Modify Foo',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 2,
                'char'      => 'Modify Bar',
                'varchar'   => 'Modify Bar',
                'timestamp' => 'now()',
            ],
            [
                'id1'       => 1,
                'id2'       => 3,
                'char'      => 'Modify Baz',
                'varchar'   => 'Modify Baz',
                'timestamp' => 'now()',
            ],
        ];
        $constraints = [
            'id1',
            'id2',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch2()
    {
        $model = new TestTableModel();
        $set = [
            [
                'id1'       => 1,
                'id2'       => 4,
                'char'      => 'Modify Qux',
                'varchar'   => 'Modify Qux',
                'timestamp' => 'now()',
            ],
        ];
        $constraints = [
            'id1',
            'id2',
        ];
        $model->updateBatch($set, $constraints);
    }
}

naente 2ヶ月前

Method	PostgreSQL 9.2.24	PostgreSQL 13.11
testInsertBatch1	success	success
testUpdateBatch1	success	success
testInsertBatch2	success	success
testUpdateBatch2	fail	success

CodeIgniter\Database\Exceptions\DatabaseException
pg_query(): Query failed: ERROR: failed to find conversion function from unknown to timestamp with time zone

SYSTEMPATH\Database\BaseConnection.php at line 647

naente 2ヶ月前

以下のように timestamp(0) with time zone カラムへの更新を除外すると
PostgreSQL 9.2.24 でも updateBatch による単一レコードの更新が可能

app\Controllers\TestController.php

public function testUpdateBatch3()
{
    $model = new TestTableModel();
    $set = [
        [
            'id1'       => 1,
            'id2'       => 4,
            'char'      => 'Modify Qux',
            'varchar'   => 'Modify Qux',
        ],
    ];
    $constraints = [
        'id1',
        'id2',
    ];
    $model->updateBatch($set, $constraints);
}

naente 2ヶ月前

CodeIgniter Forums へ報告した。
Error when updateBatch() updates a single record containing a timestamp and timezone

naente 2ヶ月前

アドバイスいただいた RawSql を使用することで解決した。

app\Controllers\TestController.php

use CodeIgniter\Database\RawSql;

app\Controllers\TestController.php

'timestamp' => new RawSql('now()'),

naente 2ヶ月前

CREATE TABLE test
(
    id         integer                     not null,
    created_at timestamp(0) with time zone not null,
    primary key (id)
);

naente 2ヶ月前

app\Models\TestModel.php

<?php

namespace App\Models;

use CodeIgniter\Database\BaseBuilder;
use CodeIgniter\Database\BaseConnection;

class TestModel
{
    /**
     * @var BaseConnection
     */
    protected BaseConnection $db;

    /**
     * @var BaseBuilder
     */
    private BaseBuilder $builder;

    public function __construct()
    {
        $this->db = db_connect();
        $this->builder = $this->db->table('test');
    }

    /**
     * @param $set
     * @return void
     */
    public function insertBatch($set)
    {
        $this->builder->insertBatch($set);
    }

    /**
     * @param $set
     * @param $constraints
     * @return void
     */
    public function updateBatch($set, $constraints)
    {
        $this->builder->updateBatch($set, $constraints);
    }
}

naente 2ヶ月前に更新

app\Controllers\TestController.php

<?php

namespace App\Controllers;

use App\Controllers\BaseController;
use App\Models\TestModel;
use CodeIgniter\Database\RawSql;
use CodeIgniter\HTTP\ResponseInterface;

class TestController extends BaseController
{
    public function testInsertBatch1()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 1,
                'created_at' => 'now',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch2()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 2,
                'created_at' => 'now()',
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch3()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 3,
                'created_at' => new RawSql('now'),
            ],
        ];
        $model->insertBatch($set);
    }

    public function testInsertBatch4()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 4,
                'created_at' => new RawSql('now()'),
            ],
        ];
        $model->insertBatch($set);
    }

    public function testUpdateBatch1()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 1,
                'created_at' => 'now',
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch2()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 2,
                'created_at' => 'now()',
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch3()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 3,
                'created_at' => new RawSql('now'),
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }

    public function testUpdateBatch4()
    {
        $model = new TestModel();
        $set = [
            [
                'id'         => 4,
                'created_at' => new RawSql('now()'),
            ],
        ];
        $constraints = [
            'id',
        ];
        $model->updateBatch($set, $constraints);
    }
}

naente 2ヶ月前に更新

PostgreSQL 13.11

insertBatch

INSERT INTO "test"("created_at", "id") VALUES ('now',1)

INSERT INTO "test"("created_at", "id") VALUES ('now()',2)

INSERT INTO "test"("created_at", "id") VALUES (now,3)

INSERT INTO "test"("created_at", "id") VALUES (now(),4)

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

naente 2ヶ月前に更新

PostgreSQL 13.11

updateBatch

UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT 'now' "created_at", 1 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)

UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT 'now()' "created_at", 2 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)

UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT now "created_at", 3 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)

UPDATE "test"
SET "created_at" = CAST(_u."created_at" AS TIMESTAMP WITH TIME ZONE)
FROM (SELECT now() "created_at", 4 "id") _u
WHERE "test"."id" = CAST(_u."id" AS INTEGER)

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

naente 2ヶ月前に更新

PostgreSQL 9.2.24

insertBatch

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

naente 2ヶ月前

PostgreSQL 9.2.24

updateBatch

1 と 2 は XX000 内部エラー
[XX000] ERROR: failed to find conversion function from unknown to timestamp with time zone

3 は 42703 未定義列エラー
[42703] ERROR: 列"now"は存在しません

このスクラップは2ヶ月前にクローズされました