Hi all,
Current pgsql module lacks direct binary support. Therefore, users have to
use pg_escape_bytea/pg_unescape_bytea for binary handling even with
prepared statement type APIs. Use of
pg_escape_bytea/pg_unescape_bytea requires some overheads on both server
and client side.
Following patch enables pg_execute() to handle binary directly.
https://github.com/yohgaki/php-src/compare/pgsql_binary_support
NOTE: This patch is only for PoC and treats all parameters as binary,
returns binary result ALWAYS. i.e. Not only bytea, but int, float, numeric,
etc are returned as binary.
I'm planning to add $params_format(array) and $binary_result(bool)
parameter.
pg_execute() will look like
Current:
resource pg_execute ([ resource $connection ], string $stmtname , array
$params )
New:
resource pg_execute ([ resource $connection ], string $stmtname , array
$params [, array $params_format [, bool $binary_result]] )
Any thoughts?
--
Yasuo Ohgaki
yohgaki@ohgaki.net
New:
resource pg_execute ([ resource $connection ], string $stmtname , array
$params [, array $params_format [, bool $binary_result]] )Any thoughts?
Using binary format "requires knowledge of the internal representation
expected by the backend. For example, integers must be passed in network
byte order", quoting the libpq documentation.
Although it is possible to specify whether or not each parameter is
binary, the same is not possible for the result due to a limitation in
libpq. Meaning that $binary_result can only be used for queries
returning one or more bytea fields.
I would be happier if we could make this transparent to the user, but I
don't see any feasible way to do this, at least for the results.
I have mixed feelings... I see where this might come in handy, but I
think it would be a bit too user-unfriendly.
PDO_pgsql on the other hand, when used with explicit parameter binding,
could automatically use binary format for LOB fields.
Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
Hi Matteo,
2013/6/26 Matteo Beccati php@beccati.com
I have mixed feelings... I see where this might come in handy, but I
think it would be a bit too user-unfriendly.
I have mixed feelings for supporting direct binary support, too.
Main intension is faster bytea(binary) handling.
Without binary support, client has to encode binary via pg_escape_bytea
and server decode it before saving bytea data. When selecting, server encode
it by using PQescapeByeta algorithm, then client decode it by
pg_unescape_bytea.
PDO_pgsql on the other hand, when used with explicit parameter binding,
could automatically use binary format for LOB fields.
AFAIK, PDO doesn't handle bytea data natively.
There may be clever usage, but binary support is only for faster bytea
handling.
API isn't great, so if there are objections, I'll spend time rather than
this.
Regards,
--
Yasuo Ohgaki
yohgaki@ohgaki.net
Hi Matteo,
PDO_pgsql on the other hand, when used with explicit parameter binding, could automatically use binary format for LOB fields.AFAIK, PDO doesn't handle bytea data natively.
It does, as far as I remember. See PDO::PARAM_LOB
We could easily (and transparently) use binary for the parameters
explicitly bound as LOB, but I see no way we can use binary for results
-- which usually is the most common operation. Maybe it's better than
nothing though?
There may be clever usage, but binary support is only for faster bytea
handling.
API isn't great, so if there are objections, I'll spend time rather than
this.
I think the API would become cumbersome and error-prone, although I see
the benefits in bytea-intensive scenarios.
My vote is: +0
If we only could figure out a better API, that could turn into a +1...
Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
Hi Matteo,
2013/7/2 Matteo Beccati php@beccati.com
AFAIK, PDO doesn't handle bytea data natively.
It does, as far as I remember. See PDO::PARAM_LOB
We could easily (and transparently) use binary for the parameters
explicitly bound as LOB, but I see no way we can use binary for results
-- which usually is the most common operation. Maybe it's better than
nothing though?
The API is not for ease of use, but for better performance as it eliminate
needless hex conversions on both server and client side.
There may be clever usage, but binary support is only for faster bytea
handling.
API isn't great, so if there are objections, I'll spend time rather than
this.I think the API would become cumbersome and error-prone, although I see
the benefits in bytea-intensive scenarios.
Right.
My vote is: +0
If we only could figure out a better API, that could turn into a +1...
Since libpq does not support API that selectively return binary type,
there won't be any better API. We may wait until PostgreSQL supports
better API.
There may be better API in the future.
I'll close applicable bug reports as "won't fix".
Regards,
--
Yasuo Ohgaki
yohgaki@ohgaki.net
2013/7/2 Matteo Beccati <php@beccati.com mailto:php@beccati.com>
We could easily (and transparently) use binary for the parameters
explicitly bound as LOB, but I see no way we can use binary for results
-- which usually is the most common operation. Maybe it's better than
nothing though?The API is not for ease of use, but for better performance as it eliminate
needless hex conversions on both server and client side.
What I meant is that we might have that speed improvements for free in
PDO when bytea is used as a parameter (i.e. most likely insert/updates).
With no visible change for the user and full backwards compatibility.
Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
Hi Matteo,
2013/7/2 Matteo Beccati php@beccati.com
The API is not for ease of use, but for better performance as it
eliminate
needless hex conversions on both server and client side.What I meant is that we might have that speed improvements for free in
PDO when bytea is used as a parameter (i.e. most likely insert/updates).
With no visible change for the user and full backwards compatibility.
libpq's query API only has flag that returns all result as binary or text.
http://www.postgresql.org/docs/9.2/static/libpq-exec.html
PGresult *PQexecParams(PGconn *conn,
const char *command,
int nParams,
const Oid *paramTypes,
const char * const *paramValues,
const int *paramLengths,
const int *paramFormats,
int resultFormat);
resultFormat is the flag for returned values, binary or text.
resultFormat
Specify zero to obtain results in text format, or one to obtain results
in
binary format. (There is not currently a provision to obtain different
result columns in different formats, although that is possible in the
underlying protocol.)
How it could be done while there isn't API in libpq?
Does PDO pgsql driver use low level (protocol level) tweak?
I'll check code later, but I don't have time for now.
Regards,
--
Yasuo Ohgaki
yohgaki@ohgaki.net
Hi Matteo,
2013/7/2 Matteo Beccati <php@beccati.com mailto:php@beccati.com>
The API is not for ease of use, but for better performance as it
eliminate
needless hex conversions on both server and client side.What I meant is that we might have that speed improvements for free in
PDO when bytea is used as a parameter (i.e. most likely insert/updates).
With no visible change for the user and full backwards compatibility.libpq's query API only has flag that returns all result as binary or text.
I'm talking about parameters, not results. The speedup would be only for
inserts, not selects (which would require something that's not available
in libpq).
Cheers
Matteo Beccati
Development & Consulting - http://www.beccati.com/
Hi Matteo,
2013/7/2 Matteo Beccati php@beccati.com
I'm talking about parameters, not results. The speedup would be only for
inserts, not selects (which would require something that's not available
in libpq).
For insert or update, binary can be inserted/updated directly.
If we do this transparently to users, we need additional select query to
system catalog to find field type.
Since pgsql is not a db access abstraction layer, adding a array parameter
that indicates which field is binary or text may be the way to go. Lack of
reverse operation would be problematic, though.
We may wait until PostgreSQL implements appropriate API.
Regards,
--
Yasuo Ohgaki
yohgaki@ohgaki.net