Re: [PHP-DEV] pgsql: Binary data support improvement
On 26/06/2013 11:51, Yasuo Ohgaki wrote: 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/ -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] pgsql: Binary data support improvement
Hi Matteo, 2013/7/2 Matteo Beccati p...@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 yohg...@ohgaki.net
Re: [PHP-DEV] pgsql: Binary data support improvement
On 02/07/2013 12:21, Yasuo Ohgaki wrote: 2013/7/2 Matteo Beccati p...@beccati.com mailto:p...@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/ -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] pgsql: Binary data support improvement
Hi Matteo, 2013/7/2 Matteo Beccati p...@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 yohg...@ohgaki.net
Re: [PHP-DEV] pgsql: Binary data support improvement
On 02/07/2013 12:52, Yasuo Ohgaki wrote: Hi Matteo, 2013/7/2 Matteo Beccati p...@beccati.com mailto:p...@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/ -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] pgsql: Binary data support improvement
Hi Matteo, 2013/7/2 Matteo Beccati p...@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 yohg...@ohgaki.net
Re: [PHP-DEV] pgsql: Binary data support improvement
Sorry, missed the list... On 26 June 2013 09:54, Yasuo Ohgaki yohg...@ohgaki.net wrote: Hi Make, 2013/6/26 Michael Wallner m...@php.net I didn't look at the code yet, but how do you know about binary format conventions of all possible types returned? 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 -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] pgsql: Binary data support improvement
On 26/06/2013 00:44, Yasuo Ohgaki wrote: 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/ -- PHP Internals - PHP Runtime Development Mailing List To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DEV] pgsql: Binary data support improvement
Hi Matteo, 2013/6/26 Matteo Beccati p...@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 yohg...@ohgaki.net
[PHP-DEV] pgsql: Binary data support improvement
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 yohg...@ohgaki.net
