Does PHP's pg_fetch_assoc() use an Iterator/Cursor?

Question

Does the PHP function pg_fetch_assoc() internally

use an Iterator / Cursor, or does it get all data at once, network-wise?

Would \PDO be using a Cursor?

My context is fetching a large amount of data and I want to know if it gets pulled as a whole or row by row (or limited rows by rows when the cursor holds a subset of the full dataset).

My experience is that it fetches all at once because I've received memory allocation errors using it if I received too many rows. However, MySQL and ORA versions of fetch_assoc have never given me memory allocation errors. Disclaimer: I haven't used PG functions since 2009, so the function implementation may be different now. — kainaw, Jan 21 '20 at 17:32
@treyBake does the whole dataset get pulled from the db server or only the data in the cursor (I've not downvoted your answer); (changed wording from "memory allocation" to "data getting pulled as a whole or partially"); — Daniel W., Jan 21 '20 at 18:18
@DanielW. data in the cursor I believe, as we're passing in the $result as a param instead of creating it all in the function :) — treyBake, Jan 22 '20 at 09:01

score 2 · Answer 1 · answered Jan 22 '20 at 10:48

pg_fetch_assoc() does neither. It is a function which fetches an associative array from a PHP resource. The resource can be for example the result of the call to PQexecPrepared.

On API level PHP-pgsql does not have cursor functions. You could try using PDO, which emulates cursors for PostgreSQL if you enable the scrollable cursor mode. See prepare() for more details. Another alternative is to just use the SQL cursor statements yourself.

This answer explains a little bit more: php postgres from query to fetching rows in theory

Here is a link to PDO source code, which is responsible for emulating cursors with SQL statements.

treyBake · Answer 2 · 2020-01-22T09:04:29.060

The data is passed via $result:

(Taken from the pgsql.php file that opens (CTRL + Click on function name in PHPStorm)):

/**
 * Fetch a row as an associative array
 * @link https://php.net/manual/en/function.pg-fetch-assoc.php
 * @param resource $result <p>
 * PostgreSQL query result resource, returned by <b>pg_query</b>,
 * <b>pg_query_params</b> or <b>pg_execute</b>
 * (among others).
 * </p>
 * @param int $row [optional] <p>
 * Row number in result to fetch. Rows are numbered from 0 upwards. If
 * omitted or <b>NULL</b>, the next row is fetched.
 * </p>
 * @return array An array indexed associatively (by field name).
 * Each value in the array is represented as a
 * string. Database NULL
 * values are returned as <b>NULL</b>.
 * </p>
 * <p>
 * <b>FALSE</b> is returned if <i>row</i> exceeds the number
 * of rows in the set, there are no more rows, or on any other error.
 * @since 4.3
 * @since 5.0
 */
function pg_fetch_assoc ($result, $row = null) {}

The rows in $result is a numbered array/object, containing all data it has. It simply returns the row in that array/object, so it deals with whatever data is in the passed cursor. We can get this from one of the lines from the above code (formatted for better reading):

@param resource $result
PostgreSQL query result resource, returned by
pg_query, pg_query_params or pg_execute

This is a correct answer, but I think you missed a point a little bit. Reading between the lines I think OP wants to know how to use cursor mode with PG api. — Dharman, Jan 22 '20 at 10:57
@Dharman ahh, I'm a bit too literal sometimes haha :) I'll wait for OP to comment/respond and I'll amend accordingly (if I can) :) — treyBake, Jan 22 '20 at 11:03

Does PHP's pg_fetch_assoc() use an Iterator/Cursor?

2 Answers2