pg.ddx.io  psycopg/psycopg GitHub issues and pull requests  
help / color / mirror / Atom feed
[psycopg/psycopg] PR #1324: Faster C Load Sequence
23+ messages / 4 participants
[nested] [flat]

* [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 19:45  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  0 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-13 19:45 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Fixes #1191. 

Also encapsulates the API for use in other work (e.g. replication and #1323 or perf follow-up). 

I haven't done a proper benchmark, but I updated the `tests/scripts/copytest.py` to check some basic numbers.  Looks like about a 20% to 35% speedup.   Interestingly, there seems to be an unrelated pessimization in the async code path that doesn't appear on the "COPY IN" side of things.... 

EDIT:  I think the async pessimization is entirely due to the `wait_async` method.  I suspect it could be written in Cython for a healthy speedup in all things async. 

Question:  Why does the `Copy` class take a binary argument? It doesn't work unless the query itself specifies format binary, in which case it can be inferred (as it is by default).  Is the hope that someday there will be support for setting it on the protocol level?   

## On Master --> This Branch (200,000 rows, width 11 (1 int4, 10 text))
#### SYNC
0.757028 sec --> 0.498472 sec
#### SYNC BINARY
0.710254 sec --> 0.444449 sec
#### ASYNC
1.110847 sec --> 0.887244 sec
#### ASYNC BINARY
1.069361 sec  --> 0.834796 sec

## with `set_types`:

#### SYNC
0.873483 sec --> 0.532475 sec
#### SYNC BINARY
0.767204 sec --> 0.477692 sec
#### ASYNC
1.135291 sec --> 0.854667 sec
#### ASYNC BINARY
1.165879 sec --> 0.853913 sec

inline patch 1: psycopg_c/psycopg_c/_psycopg/copy.pyx, psycopg_c/psycopg_c/_psycopg/transform.pyx, tests/scripts/copytest.py (+497/-45 lines) download inline-1.patchdiff --git a/psycopg_c/psycopg_c/_psycopg/copy.pyx b/psycopg_c/psycopg_c/_psycopg/copy.pyx index ff1d213ca..31aa11827 100644 --- a/psycopg_c/psycopg_c/_psycopg/copy.pyx +++ b/psycopg_c/psycopg_c/_psycopg/copy.pyx @@ -7,13 +7,12 @@ C optimised functions for the copy system. from libc.stdint cimport int32_t, uint16_t, uint32_t from libc.string cimport memcpy +from cpython.tuple cimport PyTuple_New, PyTuple_SET_ITEM from cpython.sequence cimport PySequence_Fast, PySequence_Fast_GET_ITEM from cpython.sequence cimport PySequence_Fast_GET_SIZE -from cpython.bytearray cimport PyByteArray_AS_STRING, PyByteArray_FromStringAndSize -from cpython.bytearray cimport PyByteArray_GET_SIZE, PyByteArray_Resize -from cpython.memoryview cimport PyMemoryView_FromObject +from cpython.bytearray cimport PyByteArray_AS_STRING, PyByteArray_GET_SIZE +from cpython.bytearray cimport PyByteArray_Resize -from psycopg_c.pq cimport ViewBuffer from psycopg_c._psycopg cimport endian from psycopg import errors as e @@ -244,8 +243,10 @@ def parse_row_binary(data, tx: Transformer) -> tuple[Any, ...]: memcpy(&benfields, ptr, sizeof(benfields)) cdef int nfields = endian.be16toh(benfields) ptr += sizeof(benfields) - cdef list row = PyList_New(nfields) + tx._c_check_num_loaders(nfields) + + cdef tuple row = PyTuple_New(nfields) cdef int col cdef int32_t belength cdef Py_ssize_t length @@ -259,14 +260,13 @@ def parse_row_binary(data, tx: Transformer) -> tuple[Any, ...]: length = endian.be32toh(belength) if ptr + length > bufend: raise e.DataError("bad copy data: length exceeding data") - field = PyMemoryView_FromObject( - ViewBuffer._from_buffer(data, ptr, length)) + field = tx._c_load_item(ptr, length, col) ptr += length Py_INCREF(field) - PyList_SET_ITEM(row, col, field) + PyTuple_SET_ITEM(row, col, field) - return tx.load_sequence(row) + return row def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: @@ -276,11 +276,15 @@ def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: # politely assume that the number of fields will be what in the result cdef int nfields = tx._nfields - cdef list row = PyList_New(nfields) + tx._c_check_num_loaders(nfields) + + cdef tuple row = PyTuple_New(nfields) cdef unsigned char *fend cdef unsigned char *rowend = fstart + size cdef unsigned char *src + cdef unsigned char *scratch = NULL + cdef size_t sclen = 0 cdef unsigned char *tgt cdef int col cdef int num_bs @@ -312,15 +316,18 @@ def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: # Is this a field with no backslash? elif num_bs == 0: # Nothing to unescape: we don't need a copy - field = PyMemoryView_FromObject( - ViewBuffer._from_buffer(data, fstart, fend - fstart)) + field = tx._c_load_item(fstart, fend - fstart, col) # This is a field containing backslashes else: # We need a copy of the buffer to unescape - field = PyByteArray_FromStringAndSize("", 0) - PyByteArray_Resize(field, fend - fstart - num_bs) - tgt = <unsigned char *>PyByteArray_AS_STRING(field) + if <unsigned int>(fend - fstart - num_bs) > sclen: + # Above condition will always be true on the first run + # since sclen is 0. After that, we only extend scratch + # if it's too small. + sclen = fend - fstart - num_bs + scratch = <unsigned char *>PyMem_Realloc(scratch, sclen) + tgt = scratch src = fstart while (src < fend): if src[0] != b'\\': @@ -330,15 +337,18 @@ def parse_row_text(data, tx: Transformer) -> tuple[Any, ...]: tgt[0] = copy_unescape_lut[src[0]] src += 1 tgt += 1 + field = tx._c_load_item(scratch, fend - fstart - num_bs, col) Py_INCREF(field) - PyList_SET_ITEM(row, col, field) + PyTuple_SET_ITEM(row, col, field) # Start of the field fstart = fend + 1 - # Convert the array of buffers into Python objects - return tx.load_sequence(row) + if sclen > 0: + PyMem_Free(scratch) + + return row cdef extern from *: diff --git a/psycopg_c/psycopg_c/_psycopg/transform.pyx b/psycopg_c/psycopg_c/_psycopg/transform.pyx index 2b61c6443..9880a712b 100644 --- a/psycopg_c/psycopg_c/_psycopg/transform.pyx +++ b/psycopg_c/psycopg_c/_psycopg/transform.pyx @@ -17,9 +17,11 @@ from cpython.tuple cimport PyTuple_New, PyTuple_SET_ITEM from cpython.object cimport PyObject, PyObject_CallFunctionObjArgs from cpython.sequence cimport PySequence_Fast, PySequence_Fast_GET_ITEM from cpython.sequence cimport PySequence_Fast_GET_SIZE +from cpython.memoryview cimport PyMemoryView_FromObject from typing import Sequence +from psycopg_c.pq cimport ViewBuffer from psycopg import errors as e from psycopg.pq import Format as PqFormat from psycopg.rows import Row @@ -541,10 +543,7 @@ cdef class Transformer: out = PyTuple_New(nfields) row_loaders = self._row_loaders # avoid an incref/decref per item - if PyList_GET_SIZE(row_loaders) != nfields: - raise e.ProgrammingError( - f"cannot load sequence of {nfields} items:" - f" {len(self._row_loaders)} loaders registered") + self._c_check_num_loaders(nfields) for col in range(nfields): item = PySequence_Fast_GET_ITEM(record_fast, col) @@ -564,6 +563,29 @@ cdef class Transformer: return out + cdef inline _c_check_num_loaders(self, nfields): + if PyList_GET_SIZE(self._row_loaders) != nfields: + raise e.ProgrammingError( + f"cannot load sequence of {nfields} items:" + f" {len(self._row_loaders)} loaders registered") + + cdef inline _c_load_item( + self, unsigned char *ptr, Py_ssize_t size, Py_ssize_t index + ): + cdef RowLoader loader + + row_loaders = self._row_loaders + loader = <RowLoader>PyList_GET_ITEM(row_loaders, index) + if (<RowLoader>loader).cloader is not None: + pyval = loader.cloader.cload(<char *>ptr, size) + else: + item = PyMemoryView_FromObject( + ViewBuffer._from_buffer(self, ptr, size) + ) + pyval = PyObject_CallFunctionObjArgs( + loader.loadfunc, <PyObject*>item, NULL) + return pyval + def get_loader(self, oid: int, format: PqFormat) -> "Loader": cdef PyObject *row_loader = self._c_get_loader( <PyObject *>oid, <PyObject *>format) diff --git a/tests/scripts/copytest.py b/tests/scripts/copytest.py index 4325d080f..590509807 100755 --- a/tests/scripts/copytest.py +++ b/tests/scripts/copytest.py @@ -4,11 +4,15 @@ from __future__ import annotations import sys +import pstats import asyncio import logging -from time import time +import cProfile +import statistics +from time import perf_counter from typing import Any -from argparse import ArgumentParser, Namespace +from argparse import ArgumentParser, BooleanOptionalAction, Namespace +from contextlib import nullcontext import psycopg from psycopg import sql @@ -20,45 +24,365 @@ format="%(asctime)s %(levelname)s %(message)s", ) +pr = cProfile.Profile() + def main(): args = parse_cmdline() logger.setLevel(args.loglevel) if getattr(args, "async"): - asyncio.run(main_async(args)) + t_ins, t_outs, t_inserts, t_selects = asyncio.run(main_async(args)) + else: + t_ins, t_outs, t_inserts, t_selects = main_sync(args) + + if args.executemany: + execute_descr = "executemany" else: - main_sync(args) + execute_descr = "execute" + if args.pipeline: + execute_descr = "pipelined execute" + else: + execute_descr = "execute" + + if t_ins: + output_timings(args, t_ins, "copy in") + if t_outs: + output_timings(args, t_outs, "copy out") + if t_inserts: + output_timings(args, t_inserts, f"{execute_descr} insert") + if t_selects: + output_timings(args, t_selects, f"{execute_descr} select") + + if args.cprofile: + pr.dump_stats("output.prof") + stats = pstats.Stats(pr) + stats.sort_stats("cumulative") + stats.print_stats(10) + + with psycopg.Connection.connect(args.dsn, autocommit=True) as conn: + conn.execute("DROP TABLE IF EXISTS testcopy") -def main_sync(args: Namespace) -> None: +def main_sync(args: Namespace) -> tuple[list[float], ...]: test = CopyPutTest(args) + t_ins, t_outs, t_inserts, t_selects = [], [], [], [] + if args.executemany: + insert = executemany_insert_sync + select = executemany_select_sync + else: + insert = execute_insert_sync + select = execute_select_sync with psycopg.Connection.connect(args.dsn) as conn: with conn.cursor() as cur: - writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None cur.execute(test.get_table_stmt()) - t0 = time() - with cur.copy(test.get_copy_stmt(), writer=writer) as copy: - for i in range(args.nrecs): - copy.write_row(test.get_record()) - tf = time() - - logger.info("time to copy: %.6f sec", tf - t0) + n_in, n_out, n_insert, n_select = get_effective_repeats(args) + for i in range(n_insert): + t_insert = insert(args, test, cur) + t_inserts.append(t_insert) + for i in range(n_in): + t_in = copy_in_sync(args, test, cur) + if args.copy_in: + t_ins.append(t_in) + if i != n_in - 1: + cur.execute(test.get_truncate_stmt()) + for i in range(n_select): + t_select = select(args, test, cur) + t_selects.append(t_select) + for i in range(n_out): + t_out = copy_out_sync(args, test, cur) + t_outs.append(t_out) + return t_ins, t_outs, t_inserts, t_selects -async def main_async(args: Namespace) -> None: +async def main_async(args: Namespace) -> tuple[list[float], ...]: test = CopyPutTest(args) + t_ins, t_outs, t_inserts, t_selects = [], [], [], [] + if args.executemany: + insert = executemany_insert_async + select = executemany_select_async + else: + insert = execute_insert_async + select = execute_select_async async with await psycopg.AsyncConnection.connect(args.dsn) as conn: async with conn.cursor() as cur: await cur.execute(test.get_table_stmt()) - writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None - t0 = time() - async with cur.copy(test.get_copy_stmt(), writer=writer) as copy: - for i in range(args.nrecs): - await copy.write_row(test.get_record()) - tf = time() + n_in, n_out, n_insert, n_select = get_effective_repeats(args) + for i in range(n_insert): + t_insert = await insert(args, test, cur) + t_inserts.append(t_insert) + await cur.execute(test.get_truncate_stmt()) + for i in range(n_in): + t_in = await copy_in_async(args, test, cur) + if args.copy_in: + t_ins.append(t_in) + if not i == n_in - 1: + await cur.execute(test.get_truncate_stmt()) + for i in range(n_select): + t_select = await select(args, test, cur) + t_selects.append(t_select) + for i in range(n_out): + t_out = await copy_out_async(args, test, cur) + t_outs.append(t_out) + return t_ins, t_outs, t_inserts, t_selects + + +def get_effective_repeats(args): + # copy in always runs once or we have nothing to copy out + n_in = args.repeat if args.copy_in else 1 if args.copy_out or args.select else 0 + n_out = args.repeat if args.copy_out else 0 + n_insert = args.repeat if args.insert else 0 + n_select = args.repeat if args.select else 0 + + if all(r == 0 for r in (n_in, n_out, n_insert, n_select)): + print("Nothing to do.") + return n_in, n_out, n_insert, n_select + + +def output_timings(args, timings, description): + if args.repeat > 1: + logger.info( + f"time to {description}: %.6f sec [%.6f +/- %.6f from %i results]", + *get_statistics(timings), + ) + else: + logger.info(f"time to {description}: %.6f sec", timings[0]) + + +def get_statistics(timings): + orig_timings = timings + min_val = min(timings) + mean_val = statistics.mean(timings) + stddev = statistics.stdev(timings) + + if len(timings) <= 4: + return (min_val, mean_val, stddev, len(timings)) + + # trim upper outliers + robust_timings = [t for t in timings if t <= statistics.quantiles(timings)[-1]] + threshold = statistics.mean(robust_timings) + 3 * statistics.stdev(robust_timings) + while max(timings) > threshold: + timings = [t for t in timings if t <= threshold] + if len(timings) < 4: + logger.warning( + "Not trimming outliers as it would result in less than 4 timings" + ) + timings = orig_timings + break + mean_val = statistics.mean(timings) + stddev = statistics.stdev(timings) + threshold = mean_val + 3 * stddev + + return (min_val, mean_val, stddev, len(timings)) + + +def copy_in_sync(args, test, cur): + writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None + with cur.copy(test.get_copy_stmt(), writer=writer) as copy: + if args.set_types: + copy.set_types(["text"] * args.nfields) + records = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.copy_in and args.cprofile: + pr.enable() + for record in records: + copy.write_row(record) + if args.copy_in and args.cprofile: + pr.disable() + tf = perf_counter() + + return tf - t0 - logger.info("time to copy: %.6f sec", tf - t0) + +def copy_out_sync(args, test, cur): + with cur.copy(test.get_copy_out_stmt()) as copy: + if args.set_types: + copy.set_types(["int4"] + ["text"] * args.nfields) + t0 = perf_counter() + if args.cprofile: + pr.enable() + while copy.read_row(): + pass + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def executemany_insert_sync(args, test, cur): + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.cprofile: + pr.enable() + cur.executemany(insert, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def executemany_select_sync(args, test, cur): + select = test.get_select_stmt() + params = test.get_record_ids(cur) + t0 = perf_counter() + if args.cprofile: + pr.enable() + cur.executemany(select, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def execute_insert_sync(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + cur.execute(insert, param) + if pipeline is not None: + pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +def execute_select_sync(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + select = test.get_select_stmt() + params = test.get_record_ids(cur) + with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + cur.execute(select, param) + if pipeline is not None: + pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def copy_in_async(args, test, cur): + writer = getattr(psycopg.copy, args.writer)(cur) if args.writer else None + async with cur.copy(test.get_copy_stmt(), writer=writer) as copy: + if args.set_types: + copy.set_types(["text"] * args.nfields) + records = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.copy_in and args.cprofile: + pr.enable() + for record in records: + await copy.write_row(record) + if args.copy_in and args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def copy_out_async(args, test, cur): + async with cur.copy(test.get_copy_out_stmt()) as copy: + if args.set_types: + copy.set_types(["int4"] + ["text"] * args.nfields) + t0 = perf_counter() + if args.cprofile: + pr.enable() + while await copy.read_row(): + pass + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def executemany_insert_async(args, test, cur): + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + t0 = perf_counter() + if args.cprofile: + pr.enable() + await cur.executemany(insert, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def executemany_select_async(args, test, cur): + select = test.get_select_stmt() + params = await test.get_record_ids_async(cur) + t0 = perf_counter() + if args.cprofile: + pr.enable() + await cur.executemany(select, params) + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def execute_insert_async(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + insert = test.get_insert_stmt() + params = [test.get_record() for _ in range(args.nrecs)] + async with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + await cur.execute(insert, param) + if pipeline is not None: + await pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 + + +async def execute_select_async(args, test, cur): + if args.pipeline: + context = cur.connection.pipeline() + else: + context = nullcontext() + select = test.get_select_stmt() + params = await test.get_record_ids_async(cur) + async with context as pipeline: + t0 = perf_counter() + if args.cprofile: + pr.enable() + + for param in params: + await cur.execute(select, param) + if pipeline is not None: + await pipeline.sync() + + if args.cprofile: + pr.disable() + tf = perf_counter() + return tf - t0 class CopyPutTest: @@ -79,13 +403,49 @@ def get_copy_stmt(self) -> Query: [sql.Identifier(f"f{i}") for i in range(self.args.nfields)] ) stmt = sql.SQL("""\ -copy testcopy ({}) from stdin -""").format(fields) +copy testcopy ({}) from stdin{} +""").format(fields, sql.SQL(" WITH (FORMAT BINARY)" if self.args.binary else "")) + return stmt + + def get_select_stmt(self) -> Query: + stmt = sql.SQL("""\ +SELECT * FROM testcopy WHERE id = {} +""").format(sql.SQL("%b" if self.args.binary else "%t")) + return stmt + + def get_insert_stmt(self) -> Query: + fields = sql.SQL(", ").join( + [sql.Identifier(f"f{i}") for i in range(self.args.nfields)] + ) + formatter = sql.SQL("%b" if self.args.binary else "%t") + stmt = sql.SQL("""\ +INSERT INTO testcopy ({}) VALUES ({}) +""").format( + fields, + sql.SQL(", ").join(formatter for _ in range(self.args.nfields)), + ) + return stmt + + def get_truncate_stmt(self) -> Query: + return sql.SQL("TRUNCATE testcopy") + + def get_copy_out_stmt(self) -> Query: + stmt = sql.SQL("""\ +COPY testcopy TO STDOUT{} +""").format(sql.SQL(" WITH (FORMAT BINARY)" if self.args.binary else "")) return stmt def get_record(self) -> tuple[Any, ...]: return tuple("x" * self.args.colsize for _ in range(self.args.nfields)) + def get_record_ids(self, cur: psycopg.Cursor) -> list[tuple[int]]: + cur.execute("SELECT id FROM testcopy") + return cur.fetchall() + + async def get_record_ids_async(self, cur: psycopg.AsyncCursor) -> list[tuple[int]]: + await cur.execute("SELECT id FROM testcopy") + return await cur.fetchall() + def parse_cmdline() -> Namespace: parser = ArgumentParser(description=__doc__) @@ -93,6 +453,66 @@ def parse_cmdline() -> Namespace: parser.add_argument( "--async", action="store_true", default=False, help="test async objects" ) + parser.add_argument( + "--repeat", + type=int, + default=1, + help="number of repeats [default: %(default)s]", + ) + parser.add_argument( + "--cprofile", + action=BooleanOptionalAction, + default=False, + help="output cProfile information and save profile to output.prof", + ) + parser.add_argument( + "--copy-in", + action=BooleanOptionalAction, + default=True, + help="output copy in timings and profile", + ) + parser.add_argument( + "--copy-out", + action=BooleanOptionalAction, + default=False, + help="output copy out timings and profile", + ) + parser.add_argument( + "--insert", + action=BooleanOptionalAction, + default=False, + help="output insert timings and profile", + ) + parser.add_argument( + "--select", + action=BooleanOptionalAction, + default=False, + help="output select timings and profile", + ) + parser.add_argument( + "--pipeline", + action=BooleanOptionalAction, + default=False, + help="use pipeline mode for --select and --insert with --no-executemany", + ) + parser.add_argument( + "--executemany", + action=BooleanOptionalAction, + default=True, + help="use executemany instead of execute", + ) + parser.add_argument( + "--binary", + action=BooleanOptionalAction, + default=False, + help="binary or text output format", + ) + parser.add_argument( + "--set-types", + action=BooleanOptionalAction, + default=False, + help="binary or text output format", + ) parser.add_argument( "--nrecs", type=int,
^ permalink raw [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 21:01  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-13 21:01 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx)

This code looks wrong to me - `scratch` gets initialized as NULL, but only set to a valid pointer behind this condition. The block afterwards uses it in any case as `tgt`.

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 21:07  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-13 21:07 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx:319)

Is this replacement correct? `_c_load_item` does a lot more?

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 21:08  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-13 21:08 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx:340)

Why is `_c_load_item` now needed here?

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 21:13  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-13 21:13 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/transform.pyx:535)

Is this function now obsolete? Haven't checked the caller sides. Could also be, that @dvarrazzo wants it for API purposes.

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 21:15  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-13 21:15 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/transform.pyx:584)

Did you try the `PyMemoryView_FromMemory` approach and it failed? I think it would give additional performance, but it could be that it does not cover all type edge cases / is less versatile.

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 21:17  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-13 21:17 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@DylanYoung Left a few comments, see below. I think the `scratch` pointer handling is faulty, but maybe I have missed a crucial detail.

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-13 21:22  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-13 21:22 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

On a sidenote - I am surprised that with `set_types` the runtime is worse. Without `set_types` the loader allocation happens on-the-fly, but with a few indirections. I expected the `set_types` tests to be faster - could be a hint that `set_types` itself does something overly explicit?

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 01:44  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 01:44 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/transform.pyx:584)

I just tried it, and didn't see an improvement, but I don't have a proper benchmark running yet, so it's possible there's some small gain.  I'm not sure it's worth the cost though.  I worry that some loader might be holding onto the memoryview...  I may revisit it at a later date, though I suspect there are lower-lieing fruit. 

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 01:45  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 01:45 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

> On a sidenote - I am surprised that with `set_types` the runtime is worse. Without `set_types` the loader allocation happens on-the-fly, but with a few indirections. I expected the `set_types` tests to be faster - could be a hint that `set_types` itself does something overly explicit?

I suspect this is just a relic of my ad-hoc benchmarking.  What really surprises me is the performance degradation of async.  I might investigate that. 

EDIT: I think the async pessimization is entirely due to the `wait_async` function.  I suspect it could be written in Cython for a healthy speedup in all things async. 

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 01:48  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 01:48 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/transform.pyx:535)

It's still used on the python end (e.g. in the composite and record loaders).  I tried replacing it there, but the extra tuple allocation + c-level load-sequence wins, though just marginally.   Once those loaders are written in C, it could be removed, but I suspect it may still be useful for people implementing new loaders in pure-python, so I probably wouldn't remove the API even then. 

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 01:53  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 01:53 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx)

`sclen` is initialized to zero, so on the first pass, this will always allocate memory, then it only allocates more memory if the next string is larger than the largest string seen so far.  `scratch` is initialized as `NULL` so that the first allocation is a malloc, rather than a realloc. 

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 01:54  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 01:54 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx:340)

_c_load_item encapsulates all the code you added.  it replaces `tx.load_sequence(row)`

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 01:55  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 01:55 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx:319)

Because `_c_load_item` also replaces the work done in `tx.load_sequence(row)`

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 01:56  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 01:56 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/transform.pyx:584)

If you wanted to pull my code and try it out, I'd be happy to see if there's a measurable difference. 

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 02:47  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 02:47 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx)

It's the same logic as in array.pyx and my other PR for composite/record loaders. 

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 03:07  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 03:07 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

Latest push fixes a clang warning, adds come more niceties to `tests/scripts/copytest.py`, and removes a check on the index being valid (as the caller of _c_load_item should have previously verified this, e.g. by calling _c_check_num_loaders) .  

I think this last will allow us to use it in `load_row` and `load_rows` without a regression.  I haven't done this yet, because it's a hugely global change and I'd like to check with @dvarrazzo as to whether there's a good way to run a performance regression.  I do have a commit making that change on my local branch. 

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 09:52  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-14 09:52 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx)

Could you add a comment that the condition is always true at first run?

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 10:41  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-14 10:41 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/transform.pyx:535)

Yes loaders are hookable with custom impls, so this has to stay for that purpose.

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 10:42  "jerch (@jerch)" <noreply+jerch@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: jerch (@jerch) @ 2026-06-14 10:42 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/transform.pyx:584)

I am a bit short of time currently, so will prolly not get to test it within the next days.

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-06-14 16:05  "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: DylanYoung (@DylanYoung) @ 2026-06-14 16:05 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

(on psycopg_c/psycopg_c/_psycopg/copy.pyx)

Will do!

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-11-12 11:57  "Binary-Refinery (@Binary-Refinery)" <noreply+Binary-Refinery@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: Binary-Refinery (@Binary-Refinery) @ 2026-11-12 11:57 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

I can check the current patch with a custom binary loader that raises repeatedly. Would a small regression case covering exception propagation and cleanup be useful on this PR?

^ permalink  raw  [nested|flat] 23+ messages in thread

* Re: [psycopg/psycopg] PR #1324: Faster C Load Sequence
@ 2026-11-12 15:24  "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@github.com>
  21 siblings, 0 replies; 23+ messages in thread

From: dvarrazzo (@dvarrazzo) @ 2026-11-12 15:24 UTC (permalink / raw)
  To: psycopg/psycopg <noreply+psycopg-psycopg@github.com>

@Binary-Refinery no, it wouldn't, but thank you very much. Same for the other PR you commented on.

^ permalink  raw  [nested|flat] 23+ messages in thread


end of thread, other threads:[~2026-11-12 15:24 UTC | newest]

Thread overview: 23+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-13 19:45 [psycopg/psycopg] PR #1324: Faster C Load Sequence "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-13 21:01 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-13 21:07 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-13 21:08 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-13 21:13 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-13 21:15 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-13 21:17 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-13 21:22 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-14 01:44 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 01:45 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 01:48 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 01:53 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 01:54 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 01:55 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 01:56 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 02:47 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 03:07 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-06-14 09:52 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-14 10:41 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-14 10:42 ` "jerch (@jerch)" <noreply+jerch@github.com>
2026-06-14 16:05 ` "DylanYoung (@DylanYoung)" <noreply+DylanYoung@github.com>
2026-11-12 11:57 ` "Binary-Refinery (@Binary-Refinery)" <noreply+Binary-Refinery@github.com>
2026-11-12 15:24 ` "dvarrazzo (@dvarrazzo)" <noreply+dvarrazzo@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