Subject: [postgresql-interfaces/psqlodbc] issue #89: Batch update with some invalid values fails for all rows

From: "vadz (@vadz)" <[email-masked]>
Date: Tue, 28 Jan 2025 17:03:53 +0000

Suppose there is the following table in the database:

```sql
create table t(x integer check (x<100));
```

and consider a program which does something like the following (simplified):

```cpp
constexpr int ARRAY_SIZE = 2;
SQLSetStmtAttr(h, SQL_ATTR_PARAMSET_SIZE, (void *)ARRAY_SIZE, 0);

SQLINTEGER values[ARRAY_SIZE] = { 1, 101 }; // First value is valid, second one is not.
SQLLEN inds[ARRAY_SIZE];
SQLBindParameter(h, 1, SQL_PARAM_INPUT, SQL_C_INTEGER, SQL_INTEGER, 0, 0, values, 0, inds);

SQLUSMALLINT status[ARRAY_SIZE];
SQLSetStmtAttr(h, SQL_ATTR_PARAM_STATUS_PTR, status, 0);

SQLPrepare(h, "insert into t(x) values(?)", SQL_NTS);
SQLRETURN rc = SQLExecute(h);
```

When using SQL Server with either Microsoft ODBC driver or FreeTDS, `rc` is `SQL_SUCCESS_WITH_INFO` and `status` array is filled with `{ SQL_PARAM_SUCCESS, SQL_PARAM_ERROR }` which is nice because it allows the application to determine which row(s) contained values that resulted in an error.

When using PostgreSQL ODBC driver (v13.02, but from examining Git history it doesn't look like there have been any changes here even in the latest version), `rc` is `SQL_ERROR` and `status` contains `SQL_PARAM_ERROR` for both elements, which doesn't provide any useful information.

Is this behaviour intentional and, if not, would it be possible to change it to be more consistent with other ODBC drivers and, also, more useful?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sat, 01 Feb 2025 17:16:19 +0000

So if I understand correctly, you would like the insert for the first to succeed and the second to fail.
Since the insert for both is a single transaction when the first fails the second fails as postgres does not commit the transaction. 
This is by design.

see https://www.postgresql.org/docs/current/tutorial-transactions.html for more details

Dave

---

From: "vadz (@vadz)" <[email-masked]>
Date: Sat, 01 Feb 2025 17:23:12 +0000

Not quite, I'm perfectly fine with the insert failing and actually think this is more correct than doing a partial insert as some other databases/ODBC drivers do. But it would be great to have some information about which rows resulted in an error and this is something that `SQL_ATTR_PARAM_STATUS_PTR` is supposed to provide, but this driver returns `SQL_PARAM_ERROR` in it even for the first row, which would have succeeded.

I.e. the problem is not (so much) returning `SQL_ERROR` instead of `SQL_SUCCESS_WITH_INFO` (although this is inconsistent with the other ODBC drivers I've tried), but not providing any useful information in the status array.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 02 Feb 2025 05:08:51 +0000

Unfortunately the server does not provide that information. It doesn't know that the first one succeeded since it was all done in one insert.

```
insert into testtab1 values (1,'blah');
ERROR:  duplicate key value violates unique constraint "testtab1_pkey"
DETAIL:  Key (id)=(1) already exists.
```
While we do get the error message, we don't know if it is the 1st or 2nd value that failed

---

From: "vadz (@vadz)" <[email-masked]>
Date: Sun, 02 Feb 2025 16:47:20 +0000

Just trying to understand: how does the driver manage to send multiple parameters to the server? From looking at the code it seems like it does `PQsendQuery()` for each row in a loop, but I must be missing something...

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Sun, 02 Feb 2025 20:40:05 +0000

Can you show me the code you are thinking it is using. 
Without looking at the code I would think it does
```
insert into testtab1 (id) values (1),(2);
```

---

From: "vadz (@vadz)" <[email-masked]>
Date: Sun, 02 Feb 2025 20:49:22 +0000

> Can you show me the code you are thinking it is using.

I've been looking at the part of `PGAPI_Execute()` between https://github.com/postgresql-interfaces/psqlodbc/blob/1fbc3b2bd31c9719763cc5d232ac0c0ea5a02f71/execute.c#L1140 and https://github.com/postgresql-interfaces/psqlodbc/blob/1fbc3b2bd31c9719763cc5d232ac0c0ea5a02f71/execute.c#L1241-L1244 but I could easily be wrong as I mostly just looked at the label name. I probably should understand how to enable tracing in this code to see what it's really doing during tun-time...

> `insert into testtab1 (id) values (1),(2);`

Interesting, wouldn't this run into some kind of limitation (number of parameters or total length of the command) for even relatively small amounts of data?

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Tue, 04 Feb 2025 18:37:08 +0000

it does, but not as much as one would think. It will handle quite a few values.

---

From: "davecramer (@davecramer)" <[email-masked]>
Date: Wed, 05 Feb 2025 10:07:19 +0000

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

---

From: "vadz (@vadz)" <[email-masked]>
Date: Wed, 05 Feb 2025 13:32:10 +0000

> 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?