Bug: [Postgres] `QueryBuilder::updateBatch()` pg_affected_rows(): Argument #1 ($result) must be of type PgSql\Result, bool given

[Onerosolutions]

PHP Version

8.1

CodeIgniter4 Version

4.3.3

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

PostgeSQL 15

What happened?

Error after run $this->modelName->updateBatch($updatedData, 'id');

{
    "title": "TypeError",
    "type": "TypeError",
    "code": 500,
    "message": "pg_affected_rows(): Argument #1 ($result) must be of type PgSql\\Result, bool given",
    "file": "D:\\onero\\onero-crm-ci4-api\\vendor\\codeigniter4\\framework\\system\\Database\\Postgre\\Connection.php",
    "line": 172,
    "trace": [
        {
            "file": "D:\\onero\\onero-crm-ci4-api\\vendor\\codeigniter4\\framework\\system\\Database\\Postgre\\Connection.php",
            "line": 172,
            "function": "pg_affected_rows",
            "args": [
                false
            ]
        },
        {
            "file": "D:\\onero\\onero-crm-ci4-api\\vendor\\codeigniter4\\framework\\system\\Database\\BaseBuilder.php",
            "line": 1802,
            "function": "affectedRows",
            "class": "CodeIgniter\\Database\\Postgre\\Connection",
            "type": "->",
            "args": []
        },
        {
            "file": "D:\\onero\\onero-crm-ci4-api\\vendor\\codeigniter4\\framework\\system\\Database\\BaseBuilder.php",
            "line": 2562,
            "function": "batchExecute",
            "class": "CodeIgniter\\Database\\BaseBuilder",
            "type": "->",
            "args": [
                "_updateBatch",
                100
            ]
        },
        {
            "file": "D:\\onero\\onero-crm-ci4-api\\vendor\\codeigniter4\\framework\\system\\Model.php",
            "line": 414,
            "function": "updateBatch",
            "class": "CodeIgniter\\Database\\BaseBuilder",
            "type": "->",
            "args": [
                [
                    {
                        "package_plan_id": "PP63aa9a4a1f221",
                        "package_plan_name": "Basic",
                        "package_plan_description": "The ideal solution for small businesses with an affordable price, easy to use, even for beginners.",
                        "updated_at": "2023-03-27 05:08:21"
                    },
                    {
                        "package_plan_id": "PP63aa9a4a1f252",
                        "package_plan_name": "Business",
                        "package_plan_description": "The perfect package for mid-size companies, various additional features to market, sell & support your business.",
                        "updated_at": "2023-03-27 05:08:21"
                    },
                    {
                        "package_plan_id": "PP63aa9a4a1f26d",
                        "package_plan_name": "Professional",
                        "package_plan_description": "Complete features for complex needs, bring value to customers and create the best user experience.",
                        "updated_at": "2023-03-27 05:08:21"
                    }
                ],
                "package_plan_id",
                100
            ]
        },

Steps to Reproduce

Run updateBatch method
$this->modelName->updateBatch($updatedData, 'id');

Expected Output

No error.

Anything else?

No response

[kenjis]

It seems there is no id in $updatedData.

[kenjis]

This test passes:

CodeIgniter4/tests/system/Models/EventsModelTest.php

Line 198 in 10e3300

public function testUpdateBatchEvent(): void

Can you write a failed test case?

[Onerosolutions]

It seems there is no id in $updatedData.

i mean $this->packagePlanModel->updateBatch($updatedData, 'package_plan_id');

id is using package_plan_id

$updatedData =   [
                    [
                        "package_plan_id" => "PP63aa9a4a1f221",
                        "package_plan_name" =>  "Basic",
                        "package_plan_description" => "The ideal solution for small businesses with an affordable price, easy to use, even for beginners.",
                    ],
                    {
                        "package_plan_id"=>"PP63aa9a4a1f252",
                        "package_plan_name"=>"Business",
                        "package_plan_description"=>"The perfect package for mid-size companies, various additional features to market, sell & support your business.",
                    ],
                    [
                        "package_plan_id"=>"PP63aa9a4a1f26d",
                        "package_plan_name"=>"Professional",
                        "package_plan_description"=>"Complete features for complex needs, bring value to customers and create the best user experience.",
                    ]
                ]

[Onerosolutions]

Same issue when im running insertBatch:

            $modules = [
                [
                    'module_uuid' => $uuidGenerator::uuid4()->toString(),
                    'module_name' => 'Contact',
                    'module_description' => 'Module for contact',
                    'created_at' => date('Y-m-d H:i:s'),
                ],
                [
                    'module_uuid' => $uuidGenerator::uuid4()->toString(),
                    'module_name' => 'Deal',
                    'module_description' => 'Module for Sales Funnel',
                    'created_at' => date('Y-m-d H:i:s'),
                ],
                [
                    'module_uuid' => $uuidGenerator::uuid4()->toString(),
                    'module_name' => 'Product',
                    'module_description' => 'Module for Product',
                    'created_at' => date('Y-m-d H:i:s'),
                ]
            ];

            $moduleModel->insertBatch($modules);

it's throw error:

$ php spark db:seed CustomFieldInitialSeeder

CodeIgniter v4.3.3 Command Line Tool - Server Time: 2023-03-28 03:57:43 UTC+00:00


[TypeError]

pg_affected_rows(): Argument #1 ($result) must be of type PgSql\Result, bool given

at SYSTEMPATH\Database\Postgre\Connection.php:172

Backtrace:
  1    SYSTEMPATH\Database\Postgre\Connection.php:172
       pg_affected_rows(false)

  2    SYSTEMPATH\Database\BaseBuilder.php:1802
       CodeIgniter\Database\Postgre\Connection()->affectedRows()

  3    SYSTEMPATH\Database\BaseBuilder.php:2192
       CodeIgniter\Database\BaseBuilder()->batchExecute('_insertBatch', 100)

  4    APPPATH\Database\Seeds\CustomFieldInitialSeeder.php:43
       CodeIgniter\Database\BaseBuilder()->insertBatch([...])

  5    SYSTEMPATH\Database\Seeder.php:146
       App\Database\Seeds\CustomFieldInitialSeeder()->run()

  6    SYSTEMPATH\Commands\Database\Seed.php:77
       CodeIgniter\Database\Seeder()->call('App\\Database\\Seeds\\CustomFieldInitialSeeder')

  7    SYSTEMPATH\CLI\Commands.php:65
       CodeIgniter\Commands\Database\Seed()->run([])

  8    SYSTEMPATH\CLI\Console.php:37
       CodeIgniter\CLI\Commands()->run('db:seed', [...])

  9    ROOTPATH\spark:87
       CodeIgniter\CLI\Console()->run()

[kenjis]

We cannot reproduce.
Please show us minimum working code that reproduces the error.

[iRedds]

As I can see from the code, such an error can be caused by a broken SQL query.
Therefore, as @kenjis wrote, we need more information.

I'm not sure, but try wrapping your code in a try...catch

try {
    $moduleModel->insertBatch($modules);
} catch (Exception $e) {
    dd($moduleModel->error());
}

[kenjis]

@Onerosolutions Can you provide minimum code that reproduces the error?
Or we will close this because we cannot reproduce it.

[Onerosolutions]

Hello, sorry for the late reply. @kenjis @iRedds

The possible cause of the error may be that I added updated_at to the $allowedFields attribute as shown below:

class PackagePlanModel extends BaseModel
{
  protected $table = 'package_plan_ms';
  protected $primaryKey = 'package_plan_id';
  protected $returnType = 'App\Entities\PackageBuilder\PackagePlan';
  protected $useSoftDeletes = true;

  protected $allowedFields = [
    'package_plan_id',
    'package_id',
    'package_plan_name',
    'package_plan_description',
    'created_at',
    'updated_at',
  ];

....

I tried to run updateBatch with the returnSQL parameter set to true to get the raw SQL, and running it manually in PostgreSQL using DBbeaver:

 $packagePlanModel = new PackagePlanModel();
    try {
      $sql = $packagePlanModel->updateBatch($packagePlanUpdateBatchData, 'package_plan_id', 100, true);
      print_r($sql); die;

....

Array
(
[0] => UPDATE "package_plan_ms"
SET
"package_plan_description" = _u."package_plan_description",
"package_plan_name" = _u."package_plan_name",
"updated_at" = _u."updated_at"
FROM (
SELECT 'The ideal solution for small businesses with an affordable price, easy to use, even for beginners.' "package_plan_description", 'PP63aa9a4a1f221' "package_plan_id", 'Basic' "package_plan_name", '2023-06-13 04:11:37' "updated_at" UNION ALL
SELECT 'The perfect package for mid-size companies, various additional features to market, sell & support your business.' "package_plan_description", 'PP63aa9a4a1f252' "package_plan_id", 'Business' "package_plan_name", '2023-06-13 04:11:37' "updated_at" UNION ALL
SELECT 'Complete features for complex needs, bring value to customers and create the best user experience.' "package_plan_description", 'PP63aa9a4a1f26d' "package_plan_id", 'Professional' "package_plan_name", '2023-06-13 04:11:37' "updated_at"
) _u
WHERE "package_plan_ms"."package_plan_id" = _u."package_plan_id"
)

but it resulted in an error like this:

"SQL Error [42804]: ERROR: column "updated_at" is of type timestamp without time zone but expression is of type text
Hint: You will need to rewrite or cast the expression.
Position: 155"

So the issue could be due to the failure when updating the data, where the updated_at column and the value to be updated have different types.
My question is, how can I cast the updated_at column when running updateBatch to become:

UPDATE "package_plan_ms"
SET
"package_plan_description" = _u."package_plan_description",
"package_plan_name" = _u."package_plan_name",
"updated_at" = CAST(_u."updated_at" AS timestamp without time zone)
FROM (
SELECT 'The ideal solution for small businesses with an affordable price, easy to use, even for beginners.' "package_plan_description", 'PP63aa9a4a1f221' "package_plan_id", 'Basic' "package_plan_name", '2023-06-13 04:00:57'::timestamp without time zone "updated_at" UNION ALL
SELECT 'The perfect package for mid-size companies, various additional features to market, sell & support your business.' "package_plan_description", 'PP63aa9a4a1f252' "package_plan_id", 'Business' "package_plan_name", '2023-06-13 04:00:57'::timestamp without time zone "updated_at" UNION ALL
SELECT 'Complete features for complex needs, bring value to customers and create the best user experience.' "package_plan_description", 'PP63aa9a4a1f26d' "package_plan_id", 'Professional' "package_plan_name", '2023-06-13 04:00:57'::timestamp without time zone "updated_at"
) _u
WHERE "package_plan_ms"."package_plan_id" = _u."package_plan_id";