From: davecramer (@davecramer) <noreply+davecramer@github.com>
To: postgresql-interfaces/psqlodbc <noreply+postgresql-interfaces-psqlodbc@github.com>
Subject: [postgresql-interfaces/psqlodbc] PR #241: Fold the rows of a batched INSERT into one multi-row INSERT (#88)
Date: Tue, 17 Nov 2026 12:33:48 +0000
Message-ID: <gh-postgresql-interfaces-psqlodbc-241@github.com> (raw)
Fixes #88 for inserts.
## What the server sees today
The reporter's server logs show both drivers sending 100 rows in a single query, so round trips are not the difference. psqlODBC sends 100 separate statements joined by `;`:
```
INSERT INTO public.cities VALUES (1,'City000');INSERT INTO public.cities VALUES (2,'City001'); ...
duration: 4.292 ms
```
The third party driver sends one statement with 100 value tuples:
```
INSERT INTO public.cities VALUES(1,'City000'),(2,'City001'), ... ,(100,'City099')
duration: 1.412 ms
```
That is 3.04x on server time (4.292 / 1.412) against the 3.53x they measured on insert rate (163041.5 / 46190.0). The server parses, plans and executes 100 statements for us and one for them; the commit record in their log confirms it with `cid: 99`.
## The change
Where the statement is an INSERT that ends with a single VALUES tuple, the deferred batch keeps the text up to the tuple once and appends only `,(...)` for each later row. The batch becomes one multi-row INSERT.
Finding the tuple reuses the scan in `copy_statement_with_parameters()`, which already tracks literals, quoted identifiers, dollar quoting, comments and ODBC escapes. `track_values_tuple()` sees only characters that come from the statement text, so a parenthesis inside a parameter value or inside the expansion of an escape cannot be taken for the end of the tuple. The scan records the offsets of the tuple's `(` and `)` in the generated text, and `append_deferred_row()` compares each later row's text up to the tuple against the first row's before folding it.
A statement is refused when any text follows the tuple:
- `ON CONFLICT DO UPDATE` needs one row per command. The server rejects a command whose multi-row VALUES names the same key twice, so folding would turn a working batch into an error. The existing `params-batch-exec` test covers exactly that shape with duplicate ids, and its output is unchanged.
- `RETURNING` gives the caller one result row per command.
- Anything the scan does not recognise, including `INSERT ... SELECT`, keeps the old one statement per row.
A `;` that ends the statement is dropped along with the white space around it, so a trailing semicolon still folds.
## Behavior a caller can see
A trigger declared `FOR EACH STATEMENT` fires once per batch rather than once per row.
`SQLRowCount` reports the tuples of the last batch rather than the single row of the last statement. Neither value is the array total that ODBC asks for: today an array insert of 10000 rows reports 1. Making it report the total means accumulating the count across the flushes of a batch, which I left out of this change.
Both are documented under Batch Size in `docs/config.html`.
## Measurements
10000 rows in one array execute, `BatchSize=100`, PostgreSQL 18 over a local Unix socket, three runs each:
| | rows/sec |
|---|---|
| before | 60574, 69056, 71954 |
| after | 148650, 180180, 175193 |
About 2.5x with no network latency at all, which is the parse and plan work the fold removes. A connection with real latency saves the round trips on top of that.
## Tests
New `insert-batch-fold` test. A `FOR EACH STATEMENT` trigger counts the INSERT commands the server runs, so each case shows whether its rows folded: 6 rows with `BatchSize=3` come to 2 commands when they fold and 6 when they do not. It covers a plain tuple, a trailing `;`, a function call inside the tuple, a null parameter among the values, `ON CONFLICT`, and `INSERT ... SELECT`, and checks that every row reaches the table in all six.
`make installcheck-all` passes in all 12 combinations of `UseDeclareFetch`, `UseServerSidePrepare` and `Protocol`.
## The update case
Issue #88 also reports updates at 26060 against 59786 rows/sec. A multi-row update means rewriting to `UPDATE ... SET ... FROM (VALUES ...)`, which is a different transform and is not in this PR.
inline patch 1: convert.c, docs/config.html, execute.c and 5 more (+558/-7 lines) download inline-1.patchdiff --git a/convert.c b/convert.cindex 829071f0..07972d6a 100644--- a/convert.c+++ b/convert.c@@ -2196,8 +2196,31 @@ typedef struct _QueryParse {
size_t declare_pos;
UInt4 flags, comment_level;
encoded_str encstr;
+ int paren_level; /* ( ) depth outside quotes and comments */+ int values_state;+ ssize_t values_tuple_pos;+ ssize_t values_tuple_end;
} QueryParse;
+/*+ * How far the scan has come in recognizing an INSERT whose parameter rows+ * can fold into one multi-row INSERT, and where that statement's single+ * VALUES tuple lands in the generated text. VST_TUPLE_DONE at the end of+ * the scan is what allows the fold.+ *+ * Text behind the tuple refuses the fold. An ON CONFLICT DO UPDATE clause+ * needs one row per command, because the server rejects a command whose+ * VALUES names the same key twice, and a RETURNING clause gives the caller+ * one result row per command.+ */+enum {+ VST_BEFORE_VALUES = 0 /* no VALUES keyword at paren level 0 yet */+ , VST_AFTER_VALUES /* VALUES read, its '(' not reached */+ , VST_IN_TUPLE /* inside the tuple */+ , VST_TUPLE_DONE /* the tuple is closed and ends the statement */+ , VST_NO_FOLD /* the statement cannot fold */+};+
static void
QP_initialize(QueryParse *q, const StatementClass *stmt)
{
@@ -2217,6 +2240,10 @@ QP_initialize(QueryParse *q, const StatementClass *stmt)
q->declare_pos = 0;
q->flags = 0;
q->comment_level = 0;
+ q->paren_level = 0;+ q->values_state = VST_BEFORE_VALUES;+ q->values_tuple_pos = -1;+ q->values_tuple_end = -1;
make_encoded_str(&q->encstr, SC_get_conn(stmt), q->statement);
}
@@ -2855,6 +2882,26 @@ insert_without_target(const char *stmt, size_t *endpos)
|| ';' == wstmt[0];
}
+/*+ * Check if the character at the scan position is the ';' that ends the+ * statement, with only white space behind it. A ';' that separates two+ * statements is not one of these.+ */+static BOOL+at_final_semicolon(const QueryParse *qp)+{+ const char *ptr;++ if (';' != qp->statement[qp->opos])+ return FALSE;+ for (ptr = qp->statement + qp->opos + 1; *ptr; ptr++)+ {+ if (IS_NOT_SPACE(*ptr))+ return FALSE;+ }+ return TRUE;+}+
static ProcessedStmt *
buildProcessedStmt(const char *srvquery, ssize_t endp, int num_params)
{
@@ -3105,6 +3152,14 @@ MYLOG(DETAIL_LOG_LEVEL, "entering prepared=%d\n", stmt->prepared);
return SQL_ERROR;
}
+ /*+ * Every return below this point leaves the tuple undefined unless the+ * scan has found one, so that a caller can never read the offsets of+ * the statement built by an earlier call.+ */+ stmt->values_tuple_pos = -1;+ stmt->values_tuple_end = -1;+
qp = &query_org;
QP_initialize(qp, stmt);
@@ -3355,6 +3410,16 @@ MYLOG(DETAIL_LOG_LEVEL, "type=" FORMAT_UINTEGER " concur=" FORMAT_UINTEGER "\n",
}
stmt->stmt_with_params = qb->query_statement;
+ /*+ * A translation DLL rewrites the text in place after the scan recorded+ * the offsets, so the tuple it found may no longer be there.+ */+ if (VST_TUPLE_DONE == qp->values_state &&+ NULL == conn->DriverToDataSource)+ {+ stmt->values_tuple_pos = qp->values_tuple_pos;+ stmt->values_tuple_end = qp->values_tuple_end;+ }
retval = SQL_SUCCESS;
cleanup:
return retval;
@@ -3664,6 +3729,66 @@ static int PT_token_continue(ParseToken *pt, char oldchar)
}
#define PT_TOKEN_IGNORE(pt) ((pt)->curchar_processed = TRUE)
+/*+ * Follow an INSERT ... VALUES (...) statement through the scan, so that+ * Exec_with_parameters_resolved() can fold the parameter rows of one into+ * a single multi-row INSERT. Called once per character of the statement+ * text that is not a parameter marker, with qb->npos at the position+ * oldchar is about to take in the generated text.+ *+ * Only characters of the statement text arrive here, so a parenthesis in+ * a parameter value or in the expansion of an ODBC escape cannot move+ * paren_level.+ */+static void+track_values_tuple(QueryParse *qp, const QueryBuild *qb,+ const ParseToken *pt, char oldchar)+{+ if (STMT_TYPE_INSERT != qp->statement_type ||+ VST_NO_FOLD == qp->values_state)+ return;++ if (VST_BEFORE_VALUES == qp->values_state &&+ 0 == qp->paren_level &&+ 6 == pt->token_len &&+ QP_IN_IDENT_KEYWORD == pt->in_status &&+ stricmp(pt->finished_token, "values") == 0)+ qp->values_state = VST_AFTER_VALUES;++ switch (oldchar)+ {+ case '(':+ if (VST_AFTER_VALUES == qp->values_state &&+ 0 == qp->paren_level)+ {+ qp->values_tuple_pos = qb->npos;+ qp->values_state = VST_IN_TUPLE;+ }+ qp->paren_level++;+ break;+ case ')':+ qp->paren_level--;+ if (VST_IN_TUPLE == qp->values_state &&+ 0 == qp->paren_level)+ {+ qp->values_tuple_end = qb->npos + 1;+ qp->values_state = VST_TUPLE_DONE;+ }+ break;+ default:+ /*+ * A ';' that ends the statement is dropped along with the+ * white space around it when the rows fold, so it leaves+ * the statement foldable.+ */+ if (VST_TUPLE_DONE == qp->values_state &&+ IS_NOT_SPACE(oldchar) &&+ !at_final_semicolon(qp))+ qp->values_state = VST_NO_FOLD;+ break;+ }+}+
static int
inner_process_tokens(QueryParse *qp, QueryBuild *qb)
{
@@ -4164,6 +4289,7 @@ inner_process_tokens(QueryParse *qp, QueryBuild *qb)
}
}
+ track_values_tuple(qp, qb, pt, oldchar);
CVT_APPEND_CHAR(qb, oldchar);
return SQL_SUCCESS;
}
diff --git a/docs/config.html b/docs/config.htmlindex fdaa984b..8ce72eb5 100644--- a/docs/config.html+++ b/docs/config.html@@ -132,6 +132,13 @@ <h2>Advanced Options 1/3 Dialog Box</h2>
<li><b>Batch Size:</b>Chunk size when executing batches with arrays of
parameters. Setting 1 to this option forces one by one execution (the
behavior before).
+The rows of a chunk go to the server as one query. Where the statement is+an INSERT that ends with a single VALUES tuple, they become one INSERT+carrying one tuple per row, which the server parses and plans once for the+whole chunk. A trigger declared FOR EACH STATEMENT then fires once per+chunk rather than once per row, and SQLRowCount reports the rows of the+last chunk. Any other statement, including an INSERT with an ON CONFLICT+or RETURNING clause, goes as one statement per row.
<br /> </li>
</ul>
diff --git a/execute.c b/execute.cindex 6edc81c6..4080410b 100644--- a/execute.c+++ b/execute.c@@ -449,6 +449,65 @@ void param_status_batch_update(IPDFields *ipdopts, RETCODE retval, SQLLEN target
}
}
+/*+ * Add one parameter row's statement text to the deferred batch.+ *+ * The rows of an INSERT that ends with a single VALUES tuple fold into one+ * multi-row INSERT, which the server parses and plans once instead of once+ * per row. Any other statement, and any row whose text does not repeat the+ * first row's text up to the tuple, becomes a statement of its own,+ * separated from the row before it by ';'.+ *+ * The caller has checked that the buffer is usable.+ */+static void+append_deferred_row(StatementClass *stmt, const char *stmt_with_params)+{+ PQExpBuffer buf = &stmt->stmt_deferred;++ if (!buf->data[0])+ {+ if (stmt->values_tuple_pos >= 0)+ {+ /*+ * Keep the text through the end of the tuple only. What+ * follows is white space and at most the ';' that ends the+ * statement, and the folded INSERT is the whole query.+ */+ appendBinaryPQExpBuffer(buf, stmt_with_params,+ (size_t) stmt->values_tuple_end);+ stmt->deferred_fold_pos = stmt->values_tuple_pos;+ }+ else+ {+ appendPQExpBufferStr(buf, stmt_with_params);+ stmt->deferred_fold_pos = -1;+ }+ return;+ }++ if (stmt->deferred_fold_pos >= 0 &&+ stmt->values_tuple_pos == stmt->deferred_fold_pos &&+ memcmp(buf->data, stmt_with_params,+ (size_t) stmt->deferred_fold_pos) == 0)+ {+ appendPQExpBufferChar(buf, ',');+ appendBinaryPQExpBuffer(buf,+ stmt_with_params + stmt->values_tuple_pos,+ (size_t) (stmt->values_tuple_end -+ stmt->values_tuple_pos));+ return;+ }++ /*+ * This row belongs to another statement, so it and every row behind it+ * in this batch stands on its own.+ */+ stmt->deferred_fold_pos = -1;+ appendPQExpBufferChar(buf, ';');+ appendPQExpBufferStr(buf, stmt_with_params);+}+
static
RETCODE Exec_with_parameters_resolved(StatementClass *stmt, EXEC_TYPE exec_type, BOOL *exec_end)
{
@@ -557,12 +616,7 @@ MYLOG(0, "about to begin SC_execute exec_type=%d\n", exec_type);
else
{
if (NULL != stmt_with_params)
- {- if (stmt->stmt_deferred.data[0])- appendPQExpBuffer(&stmt->stmt_deferred, ";%s", stmt_with_params);- else- printfPQExpBuffer(&stmt->stmt_deferred, "%s", stmt_with_params);- }+ append_deferred_row(stmt, stmt_with_params);
if (NULL != ipdopts->param_status_ptr)
ipdopts->param_status_ptr[stmt->exec_current_row] = SQL_PARAM_SUCCESS; // set without exec
stmt->count_of_deffered++;
@@ -575,14 +629,17 @@ MYLOG(0, "about to begin SC_execute exec_type=%d\n", exec_type);
if (VALID_EXPBUFFER)
{
if (NULL != stmt_with_params)
- appendPQExpBuffer(&stmt->stmt_deferred, ";%s", stmt_with_params);+ append_deferred_row(stmt, stmt_with_params);
stmt->stmt_with_params = stmt->stmt_deferred.data;
}
retval = SC_execute(stmt);
stmt->stmt_with_params = stmt_with_params;
stmt->count_of_deffered = 0;
if (VALID_EXPBUFFER)
+ {
resetPQExpBuffer(&stmt->stmt_deferred);
+ stmt->deferred_fold_pos = -1;+ }
}
if (retval == SQL_ERROR)
{
diff --git a/statement.c b/statement.cindex 76c9ba7b..9467dbc2 100644--- a/statement.c+++ b/statement.c@@ -482,6 +482,9 @@ SC_Constructor(ConnectionClass *conn)
rv->batch_size = 1;
rv->exec_type = DIRECT_EXEC;
rv->count_of_deffered = 0;
+ rv->deferred_fold_pos = -1;+ rv->values_tuple_pos = -1;+ rv->values_tuple_end = -1;
rv->has_notice = 0;
INIT_STMT_CS(rv);
}
diff --git a/statement.h b/statement.hindex 2311b04b..167956e3 100644--- a/statement.h+++ b/statement.h@@ -284,6 +284,15 @@ struct StatementClass_
char *stmt_with_params; /* statement after parameter
* substitution */
+ /*+ * Where the VALUES tuple of an INSERT sits in stmt_with_params:+ * values_tuple_pos is the offset of its '(' and values_tuple_end the+ * offset one past its ')'. The parameter rows of such a statement fold+ * into one multi-row INSERT. Both are -1 for every other statement,+ * and copy_statement_with_parameters() decides which it is.+ */+ ssize_t values_tuple_pos;+ ssize_t values_tuple_end;
SQLLEN exec_start_row;
SQLLEN exec_end_row;
SQLLEN exec_current_row;
@@ -305,6 +314,12 @@ struct StatementClass_
EXEC_TYPE exec_type;
int count_of_deffered;
PQExpBufferData stmt_deferred;
+ /*+ * Offset of the VALUES tuple's '(' in stmt_deferred while the deferred+ * rows are folding into one multi-row INSERT, and -1 while they are+ * separate statements joined by ';'.+ */+ ssize_t deferred_fold_pos;
/* SQL_NEED_DATA Callback list */
StatementClass *execute_delegate;
StatementClass *execute_parent;
diff --git a/test/expected/insert-batch-fold.out b/test/expected/insert-batch-fold.outnew file mode 100644index 00000000..804b296b--- /dev/null+++ b/test/expected/insert-batch-fold.out@@ -0,0 +1,110 @@+connected++executing "INSERT INTO test_fold VALUES (?, ?)" for 6 rows+rows processed=6, SQLRowCount=3+row 0 status=success+row 1 status=success+row 2 status=success+row 3 status=success+row 4 status=success+row 5 status=success+Result set:+2+Result set:+1 one+2 two+3 three+4 four+5 five+6 six++executing "INSERT INTO test_fold (id, dt) VALUES (?, ?) ;" for 6 rows+rows processed=6, SQLRowCount=3+row 0 status=success+row 1 status=success+row 2 status=success+row 3 status=success+row 4 status=success+row 5 status=success+Result set:+2+Result set:+1 one+2 two+3 three+4 four+5 five+6 six++executing "INSERT INTO test_fold VALUES (?, upper(?))" for 6 rows+rows processed=6, SQLRowCount=3+row 0 status=success+row 1 status=success+row 2 status=success+row 3 status=success+row 4 status=success+row 5 status=success+Result set:+2+Result set:+1 ONE+2 TWO+3 THREE+4 FOUR+5 FIVE+6 SIX++executing "INSERT INTO test_fold VALUES (?, ?)" for 6 rows, row 3 null+rows processed=6, SQLRowCount=3+row 0 status=success+row 1 status=success+row 2 status=success+row 3 status=success+row 4 status=success+row 5 status=success+Result set:+2+Result set:+1 one+2 two+3 three+4 NULL+5 five+6 six++executing "INSERT INTO test_fold VALUES (?, ?) ON CONFLICT (id) DO NOTHING" for 6 rows+rows processed=6, SQLRowCount=1+row 0 status=success+row 1 status=success+row 2 status=success+row 3 status=success+row 4 status=success+row 5 status=success+Result set:+6+Result set:+1 one+2 two+3 three+4 four+5 five+6 six++executing "INSERT INTO test_fold SELECT ?, ?" for 6 rows+rows processed=6, SQLRowCount=1+row 0 status=success+row 1 status=success+row 2 status=success+row 3 status=success+row 4 status=success+row 5 status=success+Result set:+6+Result set:+1 one+2 two+3 three+4 four+5 five+6 six+disconnectingdiff --git a/test/src/insert-batch-fold-test.c b/test/src/insert-batch-fold-test.cnew file mode 100644index 00000000..8defe66c--- /dev/null+++ b/test/src/insert-batch-fold-test.c@@ -0,0 +1,232 @@+/*+ * Test the batch execution of an array of parameters for an INSERT.+ *+ * The rows of an INSERT that ends with a single VALUES tuple fold into one+ * multi-row INSERT, so that the server parses and plans the statement once+ * for the batch instead of once per row. A statement of any other shape is+ * sent as one statement per row.+ *+ * A statement level trigger counts the INSERT commands the server runs,+ * which is how each case below shows whether its rows folded. Either way+ * every row has to reach the table.+ */+#include <stdio.h>+#include <stdlib.h>++/* Must come before sql.h (declared in common.h) to suppress a warning */+#include "../../pgapifunc.h"++#include "common.h"++#define ROWCNT 6+#define BATCHSIZE 3++static const char *+status_str(SQLUSMALLINT status)+{+ switch (status)+ {+ case SQL_PARAM_SUCCESS:+ return "success";+ case SQL_PARAM_SUCCESS_WITH_INFO:+ return "success_with_info";+ case SQL_PARAM_ERROR:+ return "error";+ case SQL_PARAM_UNUSED:+ return "unused";+ }+ return "????";+}++/*+ * Insert ROWCNT rows with one array execution of the given statement. Where+ * null_row is not negative, the second parameter of that row is NULL, which+ * is a row whose text differs from the rest beyond the parameter values.+ */+static void+array_insert(const char *sql, int null_row)+{+ SQLRETURN rc;+ HSTMT hstmt;+ SQLINTEGER ids[ROWCNT] = { 1, 2, 3, 4, 5, 6 };+ SQLCHAR dts[ROWCNT][8] = { "one", "two", "three",+ "four", "five", "six" };+ SQLLEN dtlen[ROWCNT];+ SQLUSMALLINT status[ROWCNT];+ SQLULEN processed = 0;+ SQLLEN rowcount = -1;+ int i;++ printf("\nexecuting \"%s\" for %d rows", sql, ROWCNT);+ if (null_row >= 0)+ printf(", row %d null", null_row);+ printf("\n");++ for (i = 0; i < ROWCNT; i++)+ dtlen[i] = (i == null_row) ? SQL_NULL_DATA : SQL_NTS;++ rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);+ CHECK_CONN_RESULT(rc, "SQLAllocHandle failed", conn);++ rc = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMSET_SIZE,+ (SQLPOINTER) ROWCNT, 0);+ CHECK_STMT_RESULT(rc, "SQLSetStmtAttr PARAMSET_SIZE failed", hstmt);+ rc = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAM_STATUS_PTR, status, 0);+ CHECK_STMT_RESULT(rc, "SQLSetStmtAttr PARAM_STATUS_PTR failed", hstmt);+ rc = SQLSetStmtAttr(hstmt, SQL_ATTR_PARAMS_PROCESSED_PTR,+ &processed, 0);+ CHECK_STMT_RESULT(rc, "SQLSetStmtAttr PARAMS_PROCESSED_PTR failed",+ hstmt);++ rc = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_LONG,+ SQL_INTEGER, 0, 0, ids, 0, NULL);+ CHECK_STMT_RESULT(rc, "SQLBindParameter 1 failed", hstmt);+ rc = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR, SQL_CHAR,+ sizeof(dts[0]), 0, dts, sizeof(dts[0]), dtlen);+ CHECK_STMT_RESULT(rc, "SQLBindParameter 2 failed", hstmt);++ rc = SQLExecDirect(hstmt, (SQLCHAR *) sql, SQL_NTS);+ CHECK_STMT_RESULT(rc, "SQLExecDirect failed", hstmt);++ /*+ * SQLRowCount reports the rows of the last batch the driver sent, so+ * a folded batch reports its whole tuple list where one statement per+ * row reports the single row of the last statement.+ */+ rc = SQLRowCount(hstmt, &rowcount);+ CHECK_STMT_RESULT(rc, "SQLRowCount failed", hstmt);+ printf("rows processed=%d, SQLRowCount=%d\n",+ (int) processed, (int) rowcount);+ for (i = 0; i < ROWCNT; i++)+ printf("row %d status=%s\n", i, status_str(status[i]));++ rc = SQLFreeStmt(hstmt, SQL_DROP);+ CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);+}++/*+ * Report the rows of the table and the number of INSERT commands that put+ * them there, then empty the table for the next case.+ */+static void+report_and_reset(HSTMT hstmt)+{+ SQLRETURN rc;++ rc = SQLExecDirect(hstmt,+ "SELECT commands FROM test_fold_commands",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "select of the command count failed", hstmt);+ print_result(hstmt);+ rc = SQLCloseCursor(hstmt);+ CHECK_STMT_RESULT(rc, "SQLCloseCursor failed", hstmt);++ rc = SQLExecDirect(hstmt,+ "SELECT id, dt FROM test_fold ORDER BY id",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "select of the rows failed", hstmt);+ print_result(hstmt);+ rc = SQLCloseCursor(hstmt);+ CHECK_STMT_RESULT(rc, "SQLCloseCursor failed", hstmt);++ rc = SQLExecDirect(hstmt, "DELETE FROM test_fold", SQL_NTS);+ CHECK_STMT_RESULT(rc, "delete failed", hstmt);+ rc = SQLExecDirect(hstmt,+ "UPDATE test_fold_commands SET commands = 0",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "reset of the command count failed", hstmt);+}++int main(int argc, char **argv)+{+ SQLRETURN rc;+ HSTMT hstmt = SQL_NULL_HSTMT;++ test_connect();++ rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);+ CHECK_CONN_RESULT(rc, "failed to allocate stmt handle", conn);++ /*+ * Batch BATCHSIZE rows at a time, so that ROWCNT rows take more than+ * one batch and the count of a batch cannot be mistaken for the count+ * of the whole array.+ */+ rc = SQLSetConnectAttr(conn, SQL_ATTR_PGOPT_BATCHSIZE,+ (SQLPOINTER)(SQLLEN) BATCHSIZE, 0);+ CHECK_CONN_RESULT(rc, "SQLSetConnectAttr BATCHSIZE failed", conn);++ rc = SQLExecDirect(hstmt,+ "CREATE TEMPORARY TABLE test_fold"+ "(id int4 PRIMARY KEY, dt varchar(8))",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "create table failed", hstmt);+ rc = SQLExecDirect(hstmt,+ "CREATE TEMPORARY TABLE test_fold_commands"+ "(commands int4)",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "create count table failed", hstmt);+ rc = SQLExecDirect(hstmt,+ "INSERT INTO test_fold_commands VALUES (0)",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "initialize count failed", hstmt);+ rc = SQLExecDirect(hstmt,+ "CREATE OR REPLACE FUNCTION test_fold_count()"+ " RETURNS TRIGGER AS $$"+ " BEGIN"+ " UPDATE test_fold_commands"+ " SET commands = commands + 1;"+ " RETURN NULL;"+ " END;"+ " $$ LANGUAGE plpgsql",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "create function failed", hstmt);+ rc = SQLExecDirect(hstmt,+ "CREATE TRIGGER test_fold_count"+ " AFTER INSERT ON test_fold"+ " FOR EACH STATEMENT"+ " EXECUTE PROCEDURE test_fold_count()",+ SQL_NTS);+ CHECK_STMT_RESULT(rc, "create trigger failed", hstmt);++ /* The tuple ends the statement, so the rows fold */+ array_insert("INSERT INTO test_fold VALUES (?, ?)", -1);+ report_and_reset(hstmt);++ /* A ';' that ends the statement is dropped, so the rows still fold */+ array_insert("INSERT INTO test_fold (id, dt) VALUES (?, ?) ;", -1);+ report_and_reset(hstmt);++ /* Parentheses of their own inside the tuple keep it foldable */+ array_insert("INSERT INTO test_fold VALUES (?, upper(?))", -1);+ report_and_reset(hstmt);++ /* A null parameter is one tuple of the fold like any other */+ array_insert("INSERT INTO test_fold VALUES (?, ?)", 3);+ report_and_reset(hstmt);++ /*+ * Text behind the tuple refuses the fold, because a command whose+ * VALUES names a key twice is one the ON CONFLICT clause rejects.+ */+ array_insert("INSERT INTO test_fold VALUES (?, ?)"+ " ON CONFLICT (id) DO NOTHING", -1);+ report_and_reset(hstmt);++ /* A statement without a VALUES tuple refuses the fold as well */+ array_insert("INSERT INTO test_fold SELECT ?, ?", -1);+ report_and_reset(hstmt);++ /* The trigger holds the function, so the table goes first */+ rc = SQLExecDirect(hstmt, "DROP TABLE test_fold", SQL_NTS);+ CHECK_STMT_RESULT(rc, "drop table failed", hstmt);+ rc = SQLExecDirect(hstmt, "DROP FUNCTION test_fold_count()", SQL_NTS);+ CHECK_STMT_RESULT(rc, "drop function failed", hstmt);++ rc = SQLFreeStmt(hstmt, SQL_DROP);+ CHECK_STMT_RESULT(rc, "SQLFreeStmt failed", hstmt);++ test_disconnect();++ return 0;+}diff --git a/test/tests b/test/testsindex e18bc7ed..20e901c8 100644--- a/test/tests+++ b/test/tests@@ -60,6 +60,7 @@ TESTBINS = exe/connect-test \
exe/odbc-conformance-test \
exe/wchar-char-test \
exe/params-batch-exec-test \
+ exe/insert-batch-fold-test \
exe/fetch-refcursors-test \
exe/descrec-test \
exe/descriptors-free-test \