[GENERAL] What is this doing? SELECT (a,b,c) FROM mytable ...
What syntax or operator did I (accidentally) invoke by putting parentheses around my column list? SELECT (a, b, c) FROM mytable... It gets me a single result column with comma-separated values in parentheses (see 2nd SELECT below). I can't find an explanation in the PostgreSQL manual. It doesn't seem to be an array, a subquery, row constructor, etc. What sort of thing is it? test= CREATE TABLE mytable (a INTEGER, b INTEGER, c INTEGER); test= INSERT INTO mytable VALUES (1, 2, 3), (4, 5, 6), (7, 8, 9); test= SELECT a, b, c FROM mytable ORDER BY 1; a | b | c ---+---+--- 1 | 2 | 3 4 | 5 | 6 7 | 8 | 9 (3 rows) test= SELECT (a, b, c) FROM mytable ORDER BY 1; row - (1,2,3) (4,5,6) (7,8,9) (3 rows) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Gripe: bytea_output default = data corruption
vi...@khera.org wrote: ... I had some hoops thru which I had to jump to make our app compatible with both 8.x and 9.x so we could safely migrate our servers without having to coordinate a code push. It wasn't that bad, but part of the problem is that the DBD::Pg driver does not understand the new format... but it does unescape the \x marker for me to a simple x, since that follows the traditional un-escaping rules for values returned from Pg. I don't like overriding defaults in the DB settings unless I *really* have to. Although DBD::Pg uses libpq, linking it with the PostgreSQL-9.0 libpq doesn't fix the escape/unescape problem. (I'm sure you already know this.) That's because DBD::Pg implements its own string and bytea escape and unescape functions, for some reason, and does not use PQescapeStringConn etc. (I wonder if that makes it vulnerable to the security problems fixed 8.1.4?) I probably would have just gone ahead and deployed 9.0 servers with bytea_output='escape' in their postgresql.conf files. Permanent work-around. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Gripe: bytea_output default = data corruption
br...@momjian.us wrote: ... Yes, we mentioned that setting in the release notes too: ... While the Incompatibilities section mentions only the first paragraph, this remention lower down has even more details. Not sure what else you wanted us to do. Here's how I would have written that first paragraph. My addition starts at Libpq's PQunescapeBytea() function... | * Allow bytea values to be written in hex notation (Peter Eisentraut) | | The server parameter bytea_output controls whether hex or | traditional format is used for bytea output. Libpq's PQescapeByteaConn() | function automatically uses the hex format when connected to PostgreSQL | 9.0 or newer servers. Libpq's PQunescapeBytea() function from version 9.0 | and newer will properly decode both hex and traditional format. However, | in versions of Libpq older than 9.0, the PQunescapeByte() function can only | decode traditional format, and will corrupt bytea data received in hex | format without reporting an error. To avoid loss of data, you must either | upgrade all clients to 9.0.x, or set the server's bytea_output parameter | to 'escape'. Again: My complaint is that pre-9.0 libpq-based clients mis-decode the new default hex format bytea data without reporting an error, and this danger is insufficiently documented in the release notes. Speaking of documentation, go read the 9.0.x reference manual sections for Libpq's PQescapeByteaConn() and PQunescapeBytea(). These descriptions of escaping and unescaping are incorrect for 9.0, which can add to any confusion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Gripe: bytea_output default = data corruption
r...@iol.ie wrote: ... In fairness, it *is* flagged in the release note - it's the first item under data types in the list of incompatibilities. Quote: bytea output now appears in hex format by default (Peter Eisentraut) The server parameter bytea_output can be used to select the traditional output format if needed for compatibility. This is inadequate, because it fails to warn that pre-9.0 clients will decode the data incorrectly without reporting an error. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Gripe: bytea_output default = data corruption
Defaulting bytea output from the backend to use hex mode encoding, which is incompatible with pre-9.0 interfaces, wasn't a friendly thing to do. The default should have been escape mode. Or else you needed a big warning in HISTORY that we must either change bytea_output, or upgrade all clients before servers. Because using a 9.0 server with a 8.x libpq-based client results in undetected data corruption when selecting BYTEA objects. By default, the 9.0 server encodes a bytea using hex mode, but an 8.x libpq-based client will decode that using escape mode, with no error detected on either end. For example, start with A, encode to \x40 decode to x40. There are good reasons to break backward compatibility, like security or standards compliance, but not performance. Please think twice next time you consider breaking stuff just because you think the new way should be faster. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Anyone testing changes to libpq/bcc32.mak?
br...@momjian.us wrote: Magnus Hagander wrote: I don't know that anybody does. We usually get a report a couple of minor versions in and fix it then, which backs that guess. It's also not tested by the buildfarm. So I think you can call it semi- maintained at best. So if you want to become the maintainer and test/send patches at an earlier stage, please go right ahead! Yep, send us the changes and we will blindly apply them. ;-) I'll post a patch that restores libpq building with BCC. But, to repeat from a thread back in Jan 2007, I only test the resulting blibpq.dll through pgtcl-ng and its test suite. It works for that, but may not work for anything else. At one point I tried to also build psql with BCC; it built but didn't work, and I see you removed bin/psql/bcc32.mak now. So I think it's best to continue to call libpq/bcc32.mak semi-maintained at best. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Anyone testing changes to libpq/bcc32.mak?
There were some recent changes to libpq/bcc32.mak that broke my attempt to build libpq with the Borland compiler. (OK, not so recent. I've been busy.) Five new modules were added from ports/: dirent dirmod pgsleep open win32error I had to removed two, dirent.c and open.c to get the compilation to work. CVS comment on the changes (Rev 1.30, 1.28.2.1) is: Synchronize Borland libpq makefile to match MSVC. Backpatch to 8.3.X. So: Are changes to libpq/bcc32.mak being tested, or just applied assuming they are correct? Because if they are tested, I'm getting different results and we need to compare notes. If the changes are not tested, I will submit a patch that works for me. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 8.3 INSTALL: Why must I backup while my database is busy?
From the 8.3.0 INSTALL file: | Upgrading | | 1. If making a backup, make sure that your database is being updated. Ouch. Revert to the language in 8.2, perhaps: 1. Make sure that your database is not updated during or after the backup. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] 8.1.4: Who says PHP deprecated addslashes since 4.0?
[EMAIL PROTECTED] wrote: ljb [EMAIL PROTECTED] writes: | addslashes() or magic_quotes. We note that these tools have been deprecated | by the PHP group since version 4.0. Can anyone provide a source for the statement? I'm not going to put words in Josh's mouth about where he got that from, but anyone who reads all of the comments at http://us3.php.net/manual/en/function.addslashes.php ought to come away suitably unimpressed with the security of that function. Yes, sorry, I did see those comments, although I don't think they are from the PHP group themselves. But I missed the statement on the pg_escape_string manual page saying use of this function is recommended instead of addslashes(). I still think since version 4.0 is wrong. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] 8.1.4: Who says PHP deprecated addslashes since 4.0?
The PostgreSQL-8.1.4 release documentation says we should be using PostgreSQL-supplied string escaping routines, not homebrew methods. No argument from me on this. But in the User Guide to the 8.1.4 Security Update, it says: | An example of an application at risk is a PHP program that uses | addslashes() or magic_quotes. We note that these tools have been deprecated | by the PHP group since version 4.0. Can anyone provide a source for the statement? It's odd, since PHP-4.0 was released on 2000-05-22, shortly after PostgreSQL-7.0, and the PQescapeString() function wasn't even added to libpq until PostgreSQL-7.2 almost 2 years later. The current PHP reference manual doesn't discourage use of addslashes() for database input. I agree with you - this is wrong - but where did the We note... deprecated by the PHP group since version 4.0 line come from? ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Default and PQexecParams
[EMAIL PROTECTED] wrote: Hi, How could one differentiate between DEFAULT and 'DEFAULT' as parameters to PQexecuteParams? I assume you mean the libpq function PQexecParams(), and you want to use a parameterized query for INSERT or UPDATE. I don't think it is possible to use DEFAULT because it is a keyword, and only a value expression will work as a parameter. With NULL vs 'NULL', PQexecParams() has a special case to handle NULL (null pointer in the paramValues array). ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] change the last bit
[EMAIL PROTECTED] wrote: I have a int4 coloumn, and I want to change the last bit the the number in this column to 0. How can I do it? Last bit = least significant bit (LSB)? update mytable set thecolumn = thecolumn ~1 where ... ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] What does error code PGSQL_TUPLES_OK (2) actually mean?
[EMAIL PROTECTED] wrote: I have ported a PHP MySQL app to PostgreSQL, and a pg_send_query() call is returning error code 2 (PGSQL_TUPLES_OK). Unfortunately the PHP manual lists the error codes but doesn't indicate what they mean, and pg_result_error() returns a blank message. As the token ends in '_OK', I suspect 'error' 2 isn't an error at all, but some kind of OK status. However, I'd like to confirm that. Does anyone have a reference where PostgreSQL error codes are listed along with the meaning of each? (You might be better off with this in comp.databases.postgresql.interfaces.php) I don't understand - pg_send_query() returns true/false, not an error code, and there's no result handle to use with pg_result_error until you call pg_get_result. Are you really trying to do asynchronous queries? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
