pg.ddx.io  GitHub: postgresql-interfaces/psqlodbc (issues + PRs as email threads)  
help / color / mirror / Atom feed
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.c index 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.html index 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 />&nbsp;</li> </ul> diff --git a/execute.c b/execute.c index 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.c index 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.h index 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.out new file mode 100644 index 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 +disconnecting diff --git a/test/src/insert-batch-fold-test.c b/test/src/insert-batch-fold-test.c new file mode 100644 index 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/tests index 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 \

Message-ID: <gh-postgresql-interfaces-psqlodbc-241@github.com>
Permalink:  ../gh-postgresql-interfaces-psqlodbc-241@github.com/

 ·  · 


This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox