The pgsql driver doesn't support streams as BLOB values

[morozov]

Bug Report

Q	A
Version	3.6.x-dev

Summary

The pgsql driver doesn't support streams as BLOB values while at least mysqli, pdo_mysql and pdo_pgsql do.

Current behaviour

An attempt to bind a PHP stream to a ParameterType::LARGE_OBJECT parameter of a prepared statement causes type error:

TypeError: pg_escape_bytea(): Argument #‎​2 ($string) must be of type string, resource given in dbal/src/Driver/PgSQL/Statement.php on line 153

How to reproduce

use Doctrine\DBAL\DriverManager;
use Doctrine\DBAL\ParameterType;

$connection = DriverManager::getConnection([
    'driver' => 'pgsql',
    'host'   => '127.0.0.1',
    'user'   => 'postgres',
    'password' => 'Passw0rd',
]);

$stream = fopen('data://text/plain,test','r');
echo $connection->fetchOne('SELECT ?', [$stream], [ParameterType::LARGE_OBJECT]), PHP_EOL;

Expected behaviour

The script outputs "test".

[derrabus]

That is correct. Reading blobs from resources is not supported yet.

But I think we should throw an exception in that case.

[morozov]

As I understand, PostgreSQL doesn't support streaming from arbitrary PHP streams to the database (see the source). I believe the stream needs to be read into memory and then handled as a string (as well as pdo_pgsql does it).

[derrabus]

As I understand, PostgreSQL doesn't support streaming from arbitrary PHP streams to the database

Well, there's pg_lo_create() and friends, that interface is not really easy to use.

I believe the stream needs to be read into memory and then handled as a string (as well as pdo_pgsql does it).

Let's try that: #5942

[morozov]

I recently learned that BLOB and BYTEA are different types in PostgreSQL (documentation). The pg_lo_* functions work with BLOBs and allow streaming them to and from the database but BLOB is not a column type, it's a separate type of object (i.e. tables, views, blobs). DBAL maps BLOB columns to BYTEA, which can be written to via an intermediate BLOB and probably can be read from in a similar way but it won't be portable by any means.

[derrabus]

Makes sense. In that case, reading the whole resource into a string is probably the best we can do. Thank you!

[github-actions]

This thread has been automatically locked since there has not been any recent activity after it was closed. Please open a new issue for related bugs.