Bug: [Postgres] Can't use $builder->replace() multiple times in a method

[nalakapws]

PHP Version

8.1

CodeIgniter4 Version

4.2.6

CodeIgniter4 Installation Method

Manual (zip or tar.gz)

Which operating systems have you tested for this bug?

Windows

Which server did you use?

apache

Database

PostgreSQL

What happened?

Can't use $builder->replace(); statement multiple times in a method. I don't know if that is by design. But in CI3 we can use replace statement multiple times, like in a loop.

Thanks.

Steps to Reproduce

Just kind of this type method,

public function replaceStuff(array $rows)
{
        $builder = $this->db->table('records');

        $builder->replace($rows[0]);
        $builder->replace($rows[1]);
}

Expected Output

Replace multiple records at once.

Anything else?

No response

[kenjis]

Can't use $builder->replace(); statement multiple times in a method.

Why can't? What exactly happened?

[nalakapws]

I get pg_query(): Query failed: ERROR: when I use multiple replace statements.

CRITICAL - 2022-10-20 06:41:28 --> pg_query(): Query failed: ERROR:  column "setting" of relation "app__settings" does not exist
LINE 1: ...ing_value" = 'new value', "module_slug" = 'tera', "...

[kenjis]

Cannot reproduce. The following test passes.

I've confirmed this bug on PostgreSQL:

CodeIgniter\Database\Exceptions\DatabaseException : pg_query(): Query failed: ERROR: column "db_id" of relation "db_job" does not exist
LINE 1: ...name" = 'John Smith', "description" = 'American', "db_id"."1...

2022-10-20 12:29:37.306 UTC [59] ERROR: column "db_id" of relation "db_job" does not exist at character 72
2022-10-20 12:29:37.306 UTC [59] STATEMENT: UPDATE "db_job" SET "name" = 'John Smith', "description" = 'American', "db_id"."1" = 2, "db_name"."1" = 'Hans Schmidt', "db_description"."1" = 'German'
WHERE "id" = 1

--- a/tests/system/Database/Live/InsertTest.php
+++ b/tests/system/Database/Live/InsertTest.php
@@ -92,6 +92,35 @@ final class InsertTest extends CIUnitTestCase
         $this->assertSame('Cab Driver', $row->name);
     }
 
+    public function testReplaceTwice()
+    {
+        $builder = $this->db->table('job');
+
+        $data = [
+            'id'          => 1,
+            'name'        => 'John Smith',
+            'description' => 'American',
+        ];
+        $builder->replace($data);
+
+        $row = $this->db->table('job')
+            ->getwhere(['id' => 1])
+            ->getRow();
+        $this->assertSame('John Smith', $row->name);
+
+        $data = [
+            'id'          => 2,
+            'name'        => 'Hans Schmidt',
+            'description' => 'German',
+        ];
+        $builder->replace($data);
+
+        $row = $this->db->table('job')
+            ->getwhere(['id' => 2])
+            ->getRow();
+        $this->assertSame('Hans Schmidt', $row->name);
+    }
+
     public function testBug302()
     {
         $code = "my code \\'CodeIgniter\\Autoloader\\'";

[kenjis]

@nalakapws Workaround:

$this->db->table('records')->replace($rows[0]);
$this->db->table('records')->replace($rows[1]);

[nalakapws]

Thanks, @kenjis appreciate it!

[kenjis]

@codeigniter4/database-team If someone who are using Postgres, please check this and send a PR if you please.

--- a/system/Database/Postgre/Builder.php
+++ b/system/Database/Postgre/Builder.php
@@ -179,6 +179,7 @@ class Builder extends BaseBuilder
 
         unset($builder);
         $this->resetWrite();
+        $this->binds = [];
 
         return $result;
     }