Message-ID: <gh-postgresql-interfaces-psqlodbc-89-c2636865300@github.com>
From: "vadz (@vadz)" <noreply+vadz@github.com>
To: "postgresql-interfaces/psqlodbc" <noreply+postgresql-interfaces-psqlodbc@github.com>
Date: Wed, 05 Feb 2025 13:32:10 +0000
Subject: Re: [postgresql-interfaces/psqlodbc] issue #89: Batch update with some invalid values fails for all rows
In-Reply-To: <gh-postgresql-interfaces-psqlodbc-89@github.com>
References: <gh-postgresql-interfaces-psqlodbc-89@github.com>
List-Id: <gh-postgresql-interfaces-psqlodbc.github.com>
X-GitHub-Author-Login: vadz
X-GitHub-Comment-Id: 2636865300
X-GitHub-Comment-Type: issue_comment
X-GitHub-Issue: 89
X-GitHub-Repo: postgresql-interfaces/psqlodbc
X-GitHub-Type: comment
X-GitHub-Url: https://github.com/postgresql-interfaces/psqlodbc/issues/89#issuecomment-2636865300
Content-Type: text/plain; charset=utf-8

> I would just enable logging on the server to see how it does this.

Good point, thanks, this is indeed even simpler. And what I see is that the driver (at least 13.02 version from Debian Bookworm) just sends multiple semicolon separated statements to the server in a single request, e.g. for [my test](https://github.com/SOCI/soci/blob/b7b470e66d81be1346224f68206738e0ff872e59/tests/common/test-common.cpp#L3265-L3270) I see the following

```
LOG:  00000: statement: insert into soci_test(val) values('1');insert into soci_test(val) values('a')
ERROR:  22P02: invalid input syntax for integer: "a" at character 74
```

If the "at character N" part is always present (but I'm not at all sure about this), it looks like it would be possible to find the row which resulted in the error by remembering the positions of the statement boundaries for each row.

But now I also wonder if this is really the most efficient way to do bulk inserts/updates with Postgres, at the very least repeating the entire query many times like this should consume much more bandwidth than preparing it once/reusing it it for all rows?