1--TEST-- 2PDO PgSQL #15287 (Pdo\Pgsql has no real lazy fetch mode) 3--EXTENSIONS-- 4pdo 5pdo_pgsql 6--SKIPIF-- 7<?php 8require __DIR__ . '/config.inc'; 9require __DIR__ . '/../../../ext/pdo/tests/pdo_test.inc'; 10PDOTest::skip(); 11?> 12--FILE-- 13<?php 14 15require __DIR__ . '/../../../ext/pdo/tests/pdo_test.inc'; 16$pdo = PDOTest::test_factory(__DIR__ . '/common.phpt'); 17$pdo->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC); 18 19// We need a dataset of several KB so that memory gain is significant. 20// See https://www.postgresql.org/message-id/1140652.1687950987%40sss.pgh.pa.us 21$pdo->exec("create temp table t (n int, t text)"); 22$pdo->exec("insert into t values (0, 'original')"); 23for ($i = -1; ++$i < 8;) { 24 $pdo->exec("insert into t select n + 1, 'non '||t from t"); 25} 26 27$reqOf3 = 'select 79 n union all select 80 union all select 81'; 28$reqOfBig = 'select * from t'; 29 30function display($res) 31{ 32 echo implode("\n", array_map(fn($row) => implode("\t", $row), $res))."\n"; 33} 34 35echo "=== non regression ===\n"; 36 37// libpq explicitely requires single-row-mode statements to run one at a time (one stmt must 38// be fully read, or aborted, before another one can be launched). 39// Ensure that integration does not break the ability of the traditional, prefetched mode, 40// to mix fetching of multiple statements' result. 41$stmt1 = $pdo->query($reqOf3); 42$stmt2 = $pdo->query("select * from ($reqOf3) t order by n desc"); 43for ($i = -1; ++$i < 3;) { 44 display([ $stmt1->fetch() ]); 45 display([ $stmt2->fetch() ]); 46} 47 48echo "=== mem test ===\n"; 49 50// First execute without lazy fetching, as a reference and non-regression; 51// execute twice: in case warmup reduces memory consumption, we want the stabilized consumption. 52for ($i = -1; ++$i < 5;) { 53 $attrs = []; 54 $lazy = false; 55 switch ($i) { 56 case 0: 57 case 3: 58 echo "Without lazy fetching:\n"; 59 break; 60 case 2: 61 echo "With statement-scoped lazy fetching:\n"; 62 $attrs = [ PDO::ATTR_PREFETCH => 0 ]; 63 $lazy = true; 64 break; 65 case 4: 66 echo "With connection-scoped lazy fetching:\n"; 67 $pdo->setAttribute(PDO::ATTR_PREFETCH, 0); 68 $lazy = true; 69 break; 70 } 71 $stmt = $pdo->prepare($reqOfBig, $attrs); 72 $stmt->execute(); 73 $res = []; 74 // No fetchAll because we want the memory of the result of the FORElast call (the last one is empty). 75 while (($re = $stmt->fetch())) { 76 $res[] = $re; 77 // Memory introspection relies on an optionally-compiled constant. 78 if (defined('PDO::PGSQL_ATTR_RESULT_MEMORY_SIZE')) { 79 $mem = $stmt->getAttribute(PDO::PGSQL_ATTR_RESULT_MEMORY_SIZE); 80 } else { 81 // If not there emulate a return value which validates our test. 82 $mem = $lazy ? 0 : 1; 83 } 84 } 85 echo "ResultSet is $mem bytes long\n"; 86 if ($i >= 2) { 87 echo "ResultSet is " . ($mem > $mem0 ? "longer" : ($mem == $mem0 ? "not shorter" : ($mem <= $mem0 / 2 ? "more than twice shorter" : "a bit shorter"))) . " than without lazy fetching\n"; 88 } else { 89 $mem0 = $mem; 90 } 91} 92 93$pdo->setAttribute(PDO::ATTR_PREFETCH, 0); 94 95foreach ([ 96 [ 'query', 'fetch' ], 97 [ 'query', 'fetchAll' ], 98 [ 'prepare', 'fetch' ], 99 [ 'prepare', 'fetchAll' ], 100] as $mode) { 101 echo "=== with " . implode(' / ', $mode). " ===\n"; 102 switch ($mode[0]) { 103 case 'query': 104 $stmt = $pdo->query($reqOf3); 105 break; 106 case 'prepare': 107 $stmt = $pdo->prepare($reqOf3); 108 $stmt->execute(); 109 break; 110 } 111 switch ($mode[1]) { 112 case 'fetch': 113 $res = []; 114 while (($re = $stmt->fetch())) { 115 $res[] = $re; 116 } 117 break; 118 case 'fetchAll': 119 $res = $stmt->fetchAll(); 120 break; 121 } 122 display($res); 123} 124echo "DML works too:\n"; 125$pdo->exec("create temp table t2 as select 678 n, 'ok' status"); 126echo "multiple calls to the same prepared statement, some interrupted before having read all results:\n"; 127$stmt = $pdo->prepare("select :1 n union all select :1 + 1 union all select :1 + 2 union all select :1 + 3"); 128$stmt->execute([ 32 ]); 129$res = []; for ($i = -1; ++$i < 2;) $res[] = $stmt->fetch(); display($res); 130$stmt->execute([ 15 ]); 131$res = []; while (($re = $stmt->fetch())) $res[] = $re; display($res); 132$stmt->execute([ 0 ]); 133$res = []; for ($i = -1; ++$i < 2;) $res[] = $stmt->fetch(); display($res); 134display($pdo->query("select * from t2")->fetchAll()); 135?> 136--EXPECTF-- 137=== non regression === 13879 13981 14080 14180 14281 14379 144=== mem test === 145Without lazy fetching: 146ResultSet is %d bytes long 147ResultSet is %d bytes long 148With statement-scoped lazy fetching: 149ResultSet is %d bytes long 150ResultSet is more than twice shorter than without lazy fetching 151Without lazy fetching: 152ResultSet is %d bytes long 153ResultSet is not shorter than without lazy fetching 154With connection-scoped lazy fetching: 155ResultSet is %d bytes long 156ResultSet is more than twice shorter than without lazy fetching 157=== with query / fetch === 15879 15980 16081 161=== with query / fetchAll === 16279 16380 16481 165=== with prepare / fetch === 16679 16780 16881 169=== with prepare / fetchAll === 17079 17180 17281 173DML works too: 174multiple calls to the same prepared statement, some interrupted before having read all results: 17532 17633 17715 17816 17917 18018 1810 1821 183678 ok 184
