ext/pdo_pgsql: Add `Pdo\Pgsql::ATTR_CHUNK_SIZE` for chunked result fetching by KentarouTakeda · Pull Request #23210 · php/php-src

KentarouTakeda · 2026-08-11T07:08:52Z

Note: this PR currently also contains the bug fix commits from #23065. The feature is independent of them, but the existing lazy fetch defects break this feature's tests too. Once #23065 is merged, rebasing will leave only the last commit here.

Summary

Adds chunked fetching through Pdo\Pgsql::ATTR_CHUNK_SIZE. It generalizes the lazy fetch from #15287, and the chunk size lets you choose the balance between memory and speed. ext/pgsql got this as pg_set_chunked_rows_size in #14571, but there was no way to use it from ext/pdo_pgsql.

Benchmark

Benchmark code

<?php
$pdo = new Pdo\Pgsql('pgsql:');

$pdo->exec(<<<SQL
    drop table if exists bench;

    create temp table bench as select
        g as id,
        md5(g::text) c1,
        'user_'||g as c2,
        (g%997)::int as c3,
        (g*1.5)::numeric(12,2) as c4,
        timestamp '2020-01-01'+(g||' seconds')::interval as c5
    from
        generate_series(1,500000) g;

    analyze bench;
SQL);

$out = fopen('/dev/null', 'w');

$modes = [
    'buffered' => [],
    'ATTR_PREFETCH => 0' => [PDO::ATTR_PREFETCH => 0],
    'ATTR_CHUNK_SIZE => 1' => [Pdo\Pgsql::ATTR_CHUNK_SIZE => 1],
    'ATTR_CHUNK_SIZE => 10' => [Pdo\Pgsql::ATTR_CHUNK_SIZE => 10],
    'ATTR_CHUNK_SIZE => 100' => [Pdo\Pgsql::ATTR_CHUNK_SIZE => 100],
    'ATTR_CHUNK_SIZE => 1000' => [Pdo\Pgsql::ATTR_CHUNK_SIZE => 1000],
];

$held = $rows = $ms = [];

for ($round = -1; $round < 9; $round++) {
    foreach ($modes as $name => $options) {
        $t0 = hrtime(true);

        $statement = $pdo->prepare('select * from bench', $options);
        $statement->execute();

        $held[$name] = $statement->getAttribute(Pdo\Pgsql::ATTR_RESULT_MEMORY_SIZE);
        $rows[$name] = $statement->rowCount();

        while ($row = $statement->fetch(PDO::FETCH_ASSOC)) {
            fputcsv($out, $row, escape: '');
        }

        unset($statement);

        // round -1 warms up
        if ($round >= 0) {
            $ms[$name][] = (hrtime(true) - $t0) / 1e6;
        }
    }
}

fputcsv(STDOUT, ['mode', 'median_ms', 'bytes_held', 'rows_per_fetch'], escape: '');

foreach ($ms as $name => $times) {
    sort($times);
    fputcsv(STDOUT, [$name, round($times[4]), $held[$name], $rows[$name]], escape: '');
}

Run on a build without --enable-debug (PostgreSQL 18.4 / libpq 18.4):

mode	median_ms	bytes_held	rows_per_fetch
`buffered`	527	97,231,064	500,000
`ATTR_PREFETCH => 0`	522	3,288	1
`ATTR_CHUNK_SIZE => 1`	518	3,288	1
`ATTR_CHUNK_SIZE => 10`	484	3,288	10
`ATTR_CHUNK_SIZE => 100`	442	19,672	100
`ATTR_CHUNK_SIZE => 1000`	429	188,632	1,000

Usage

Set on the connection:

$pdo->setAttribute(Pdo\Pgsql::ATTR_CHUNK_SIZE, 1000);

Set on the statement, overriding the connection level:

$pdo->prepare(
    'select * from bench',
    [Pdo\Pgsql::ATTR_CHUNK_SIZE => 1000]
);

Design note

The part in bold is where I am not confident that the behavior is right. I would appreciate feedback.

ATTR_CHUNK_SIZE overrides ATTR_PREFETCH.
- When ATTR_PREFETCH is left unset and only ATTR_CHUNK_SIZE is given, this is treated as a request for chunked fetching.
- It also overrides an explicit ATTR_PREFETCH => 0. The behavior changes from lazy to chunk. ATTR_CHUNK_SIZE => 1 behaves the same as lazy, and once a chunk size is given, I think honoring it is the natural behavior.
ATTR_CHUNK_SIZE and ATTR_CURSOR => CURSOR_SCROLL are mutually exclusive.
- A statement-level option takes precedence over the connection level, so a statement-level CURSOR_SCROLL disables a connection-level ATTR_CHUNK_SIZE.
- When both are given at the statement level, there is no basis for deciding precedence, so a ValueError is thrown.

NickSdot · 2026-08-11T08:28:49Z

+try {
+    $pdo->setAttribute(Pdo\Pgsql::ATTR_CHUNK_SIZE, -1);
+} catch (ValueError $e) {
+    echo $e->getMessage(), "\n";


Suggested change

echo $e->getMessage(), "\n";

echo $e::class, ': ', $e->getMessage(), "\n";

NickSdot · 2026-08-11T08:28:52Z

+try {
+    $pdo->prepare("SELECT 1", [Pdo\Pgsql::ATTR_CHUNK_SIZE => -42]);
+} catch (ValueError $e) {
+    echo $e->getMessage(), "\n";


Suggested change

echo $e->getMessage(), "\n";

echo $e::class, ': ', $e->getMessage(), "\n";

NickSdot · 2026-08-11T08:28:55Z

+    new PDO(getenv('PDOTEST_DSN'), $user === false ? null : $user,
+        $pass === false ? null : $pass, [Pdo\Pgsql::ATTR_CHUNK_SIZE => -1]);
+} catch (ValueError $e) {
+    echo $e->getMessage(), "\n";


Suggested change

echo $e->getMessage(), "\n";

echo $e::class, ': ', $e->getMessage(), "\n";

NickSdot · 2026-08-11T08:28:59Z

+        printf("%-8s accepted as %d\n", get_debug_type($value),
+            $pdo->getAttribute(Pdo\Pgsql::ATTR_CHUNK_SIZE));
+    } catch (TypeError $e) {
+        printf("%-8s %s\n", get_debug_type($value), $e->getMessage());


Suggested change

printf("%-8s %s\n", get_debug_type($value), $e->getMessage());

printf("%-8s %s: %s\n", get_debug_type($value), $e::class, $e->getMessage());

NickSdot · 2026-08-11T08:29:05Z

+        Pdo\Pgsql::ATTR_CHUNK_SIZE => 4,
+    ]);
+} catch (ValueError $e) {
+    echo $e->getMessage(), "\n";


Suggested change

echo $e->getMessage(), "\n";

echo $e::class, ': ', $e->getMessage(), "\n";

NickSdot · 2026-08-11T08:29:11Z

+?>
+--EXPECT--
+=== a negative value is refused by setAttribute() ===
+Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647


Suggested change

Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

NickSdot · 2026-08-11T08:29:16Z

+Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647
+the attribute kept its previous value: int(0)
+=== and by the prepare() driver options ===
+Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647


Suggested change

Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

NickSdot · 2026-08-11T08:29:19Z

+=== and by the prepare() driver options ===
+Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647
+=== and by the constructor options ===
+Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647


Suggested change

Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

NickSdot · 2026-08-11T08:29:24Z

+float    Attribute value must be of type int for selected attribute, float given
+string   accepted as 8
+array    Attribute value must be of type int for selected attribute, array given
+null     Attribute value must be of type int for selected attribute, null given
+stdClass Attribute value must be of type int for selected attribute, stdClass given


Suggested change

float Attribute value must be of type int for selected attribute, float given

string accepted as 8

array Attribute value must be of type int for selected attribute, array given

null Attribute value must be of type int for selected attribute, null given

stdClass Attribute value must be of type int for selected attribute, stdClass given

float TypeError: Attribute value must be of type int for selected attribute, float given

string accepted as 8

array TypeError: Attribute value must be of type int for selected attribute, array given

null TypeError: Attribute value must be of type int for selected attribute, null given

stdClass TypeError: Attribute value must be of type int for selected attribute, stdClass given

string is accepted, so this line comes from the try branch, not the catch. Prefixing it with TypeError: fails the test.

You are right. Updated!

NickSdot · 2026-08-11T08:29:44Z

+null     Attribute value must be of type int for selected attribute, null given
+stdClass Attribute value must be of type int for selected attribute, stdClass given
+=== scrollable cursors have nothing to stream ===
+Pdo\Pgsql::ATTR_CHUNK_SIZE cannot be combined with PDO::ATTR_CURSOR set to PDO::CURSOR_SCROLL


Suggested change

Pdo\Pgsql::ATTR_CHUNK_SIZE cannot be combined with PDO::ATTR_CURSOR set to PDO::CURSOR_SCROLL

ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE cannot be combined with PDO::ATTR_CURSOR set to PDO::CURSOR_SCROLL

NickSdot · 2026-08-11T08:29:52Z

+try {
+    $pdo->setAttribute(Pdo\Pgsql::ATTR_CHUNK_SIZE, 2147483648);
+} catch (ValueError $e) {
+    echo "setAttribute(): ", $e->getMessage(), "\n";


Suggested change

echo "setAttribute(): ", $e->getMessage(), "\n";

echo 'setAttribute(): ', $e::class, ': ', $e->getMessage(), "\n";

NickSdot · 2026-08-11T08:30:01Z

+try {
+    $pdo->prepare("SELECT 1", [Pdo\Pgsql::ATTR_CHUNK_SIZE => PHP_INT_MAX]);
+} catch (ValueError $e) {
+    echo "prepare(): ", $e->getMessage(), "\n";


Suggested change

echo "prepare(): ", $e->getMessage(), "\n";

echo 'prepare(): ', $e::class, ': ', $e->getMessage(), "\n";

NickSdot · 2026-08-11T08:30:06Z

+=== INT_MAX is the largest chunk size ===
+accepted as: int(2147483647)
+=== one more is refused ===
+setAttribute(): Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647


Suggested change

setAttribute(): Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

setAttribute(): ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

NickSdot · 2026-08-11T08:30:16Z

+accepted as: int(2147483647)
+=== one more is refused ===
+setAttribute(): Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647
+prepare(): Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647


Suggested change

prepare(): Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

prepare(): ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

KentarouTakeda · 2026-08-11T10:06:48Z

Left one correction inline.

catch (ValueError $e) already fails the test if anything else is thrown, so printing the class does not strengthen the assertion. I would rather not preempt #22799 while it is still under review, and will follow whatever it settles on.

NickSdot · 2026-08-11T10:12:16Z

Left one correction inline.

catch (ValueError $e) already fails the test if anything else is thrown, so printing the class does not strengthen the assertion. I would rather not preempt #22799 while it is still under review, and will follow whatever it settles on.

A good chunk of #22799 is merged in extracted PRs. The changes I proposed here are not part of the still "under discussion" questions -- what I propose here gets regularly merged, last one here today: #23184.

Sorry about the one I missed!

…tching

KentarouTakeda · 2026-08-11T10:26:09Z

Applied.

KentarouTakeda added 4 commits August 11, 2026 01:38

ext/pdo_pgsql: End a COPY before draining a lazy fetch

b530708

ext/pdo_pgsql: Clear the connection's pointer to a destroyed statement

6846622

ext/pdo_pgsql: Drain the connection when a lazy fetch ends

53ba616

ext/pdo_pgsql: Do not deliver rows of a result another statement freed

853bf6f

KentarouTakeda requested review from SakiTakamachi, devnexen and kocsismate as code owners August 11, 2026 07:08

github-actions Bot added Category: Build System Extension: pdo_pgsql labels Aug 11, 2026

NickSdot reviewed Aug 11, 2026

View reviewed changes

ext/pdo_pgsql: Add Pdo\Pgsql::ATTR_CHUNK_SIZE for chunked result fe…

62266ca

…tching

KentarouTakeda force-pushed the pdo-pgsql-attr-chunk-size branch from ff41006 to 62266ca Compare August 11, 2026 10:25

	echo $e->getMessage(), "\n";
	echo $e::class, ': ', $e->getMessage(), "\n";

	printf("%-8s %s\n", get_debug_type($value), $e->getMessage());
	printf("%-8s %s: %s\n", get_debug_type($value), $e::class, $e->getMessage());

	Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647
	ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

	Pdo\Pgsql::ATTR_CHUNK_SIZE cannot be combined with PDO::ATTR_CURSOR set to PDO::CURSOR_SCROLL
	ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE cannot be combined with PDO::ATTR_CURSOR set to PDO::CURSOR_SCROLL

	echo "setAttribute(): ", $e->getMessage(), "\n";
	echo 'setAttribute(): ', $e::class, ': ', $e->getMessage(), "\n";

	echo "prepare(): ", $e->getMessage(), "\n";
	echo 'prepare(): ', $e::class, ': ', $e->getMessage(), "\n";

	setAttribute(): Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647
	setAttribute(): ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

	prepare(): Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647
	prepare(): ValueError: Pdo\Pgsql::ATTR_CHUNK_SIZE must be between 0 and 2147483647

Uh oh!

Conversation

KentarouTakeda commented Aug 11, 2026

Summary

Benchmark

Usage

Design note

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

NickSdot Aug 11, 2026 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

Choose a reason for hiding this comment

Uh oh!

KentarouTakeda commented Aug 11, 2026

Uh oh!

NickSdot commented Aug 11, 2026

Uh oh!

KentarouTakeda commented Aug 11, 2026

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

2 participants

NickSdot Aug 11, 2026 •

edited

Loading