# pgsql: Binary data support improvement - Thread: https://externals.io/message/67837 - Original archive: https://news-web.php.net/php.internals/67837 - Started by Yasuo Ohgaki on 2013-06-25T22:44:06+00:00 - Messages: 10 - Reply by email to internals@lists.php.net - More for agents: search with https://externals.io/search.md?q={query} · JSON API at https://externals.io/api · documentation at https://externals.io/llms.txt --- ## #67837 — Yasuo Ohgaki — 2013-06-25T22:44:06+00:00 URL: https://externals.io/message/67837#67837 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 --- ## #67847 — Michael Wallner — 2013-06-26T08:12:35+00:00 URL: https://externals.io/message/67837#67847 Sorry, missed the list... > Hi Make, > > 2013/6/26 Michael Wallner > > […] > > Users can only specify if the result is returned as text or binary. i.e. > If a user set $binary_result to TRUE, then all results(fields) are returned > as binary. > > pgsql module does not care what binary is returned and users are > responsible for correct handling. Therefore, it's only useful for bytea > data type for almost all users. -- Regards, Mike --- ## #67848 — Matteo Beccati — 2013-06-26T09:15:01+00:00 URL: https://externals.io/message/67837#67848 · In reply to: #67837 > 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/ --- ## #67851 — Yasuo Ohgaki — 2013-06-26T09:51:05+00:00 URL: https://externals.io/message/67837#67851 · In reply to: #67848 Hi Matteo, 2013/6/26 Matteo Beccati > 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 --- ## #68031 — Matteo Beccati — 2013-07-02T08:46:13+00:00 URL: https://externals.io/message/67837#68031 · In reply to: #67851 > 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/ --- ## #68032 — Yasuo Ohgaki — 2013-07-02T10:21:53+00:00 URL: https://externals.io/message/67837#68032 · In reply to: #68031 Hi Matteo, 2013/7/2 Matteo Beccati > > […] > > 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. > > > […] > > 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 --- ## #68033 — Matteo Beccati — 2013-07-02T10:33:28+00:00 URL: https://externals.io/message/67837#68033 · In reply to: #68032 > 2013/7/2 Matteo Beccati > > 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/ --- ## #68035 — Yasuo Ohgaki — 2013-07-02T10:52:02+00:00 URL: https://externals.io/message/67837#68035 · In reply to: #68033 Hi Matteo, 2013/7/2 Matteo Beccati > > > […] eliminate > > […] > > 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 --- ## #68036 — Matteo Beccati — 2013-07-02T11:03:29+00:00 URL: https://externals.io/message/67837#68036 · In reply to: #68035 > Hi Matteo, > > 2013/7/2 Matteo Beccati > > > […] > eliminate > > […] > > > 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/ --- ## #68039 — Yasuo Ohgaki — 2013-07-02T18:17:30+00:00 URL: https://externals.io/message/67837#68039 · In reply to: #68036 Hi Matteo, 2013/7/2 Matteo Beccati > 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