Thread: Error on pg_settings.bytea_output for pg9.1
Hi pgsql developers:
1 . Postgres 9.1, database server OS platform: Linux x86_64 and Windows
XP Professional Version 2002 SP3
Client OS platform: Linux x86_64 , Windows XP Professional Version
2002 SP3, Windows Server 2003 Standard Edition SP2
2. pgAdminIII: version 1.14.3, or the default version of Postgres 9.1
Windows x86_32
Steps:
1. Install Postgres 9.1 for Windows x86_32
2. Install the Spatial Extension for Postgis 2.0 by Stack Builder
after installing Postgres 9.1
3. Restart my computer.
4. Start pgAdminIII immediately after restart , pgAmin3 is the
default version of PG9.1, and then connect to the pg server by pgadmin3.
5. Run the SQL query: "SELECT setting FROM pg_settings WHERE name
=3D 'bytea_output'; " in pgAdmin3. It will show you the value 'escape'.
6. Run the client application 'psql' to connect to the same DB
server and database with the same user account. And in psql interactive
terminal, run the same SQL:
"SELECT setting FROM pg_settings WHERE name =3D 'bytea_output'; ". It
will show you the value 'hex', NOT the previous value 'escape'.
7. That means, the same environment, the same SQL query, but
different output. And I don't touch the file <postgresql.conf> any more
ever before.
Is this a bug of pgAdmin3?
Thanks,
Jinwei
CONFIDENTIALITY NOTICE: The information contained in this message may be pr=
ivileged and/or confidential. If you are not the intended recipient, or res=
ponsible for delivering this message to the intended recipient, any review,=
forwarding, dissemination, distribution or copying of this communication o=
r any attachment(s) is strictly prohibited. If you have received this messa=
ge in error, please notify the sender immediately, and delete it and all at=
tachments from your computer and network.
On 07/30/2012 11:49 AM, Emcisc (JinWei) Zhao wrote: > 5.Run the SQL query: "SELECT setting FROM pg_settings WHERE name = > 'bytea_output'; " in pgAdmin3. It will show you the value 'escape'. > > 6.Run the client application 'psql' to connect to the same DB server > and database with the same user account. And in psql interactive > terminal, run the same SQL: > > "SELECT setting FROM pg_settings WHERE name = 'bytea_output'; ". It > will show you the value 'hex', NOT the previous value 'escape'. > > 7.That means, the same environment, the same SQL query, but different > output. And I don't touch the file <postgresql.conf> any more ever before. > > Is this a bug of pgAdmin3? > I don't know if it's a bug as such, but it's certainly a curious decision if what you describe is the intended behaviour. It sounds like PgAdmin-III might be sending a SET bytea_output = 'escape' query during connection setup. bytea_output is a per-session parameter. Each session (connection) to PostgreSQL can have a different value, and it can be changed within the session. Check the PgAdmin-III preferences; there may be an option to control its preferred bytea format. It may also be worth turning on log_statement = 'all' in postgresql.conf, starting PgAdmin-III, then looking at the PostgreSQL logs to see if PgAdmin-III is in fact sending a `SET bytea_output` command. -- Craig Ringer
On Tue, 2012-07-31 at 10:19 +0800, Craig Ringer wrote: > On 07/30/2012 11:49 AM, Emcisc (JinWei) Zhao wrote: > > 5.Run the SQL query: "SELECT setting FROM pg_settings WHERE name = > > 'bytea_output'; " in pgAdmin3. It will show you the value 'escape'. > > > > 6.Run the client application 'psql' to connect to the same DB server > > and database with the same user account. And in psql interactive > > terminal, run the same SQL: > > > > "SELECT setting FROM pg_settings WHERE name = 'bytea_output'; ". It > > will show you the value 'hex', NOT the previous value 'escape'. > > > > 7.That means, the same environment, the same SQL query, but different > > output. And I don't touch the file <postgresql.conf> any more ever before. > > > > Is this a bug of pgAdmin3? > > > > I don't know if it's a bug as such, but it's certainly a curious > decision if what you describe is the intended behaviour. It sounds like > PgAdmin-III might be sending a SET bytea_output = 'escape' query during > connection setup. > Yes, pgAdmin sets bytea_output, and also DateStyle, and client_min_messages, right after the connection is done. > bytea_output is a per-session parameter. Each session (connection) to > PostgreSQL can have a different value, and it can be changed within the > session. > > Check the PgAdmin-III preferences; there may be an option to control its > preferred bytea format. It may also be worth turning on log_statement = > 'all' in postgresql.conf, starting PgAdmin-III, then looking at the > PostgreSQL logs to see if PgAdmin-III is in fact sending a `SET > bytea_output` command. > There's no option to control this. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On 07/31/2012 01:50 PM, Guillaume Lelarge wrote: >> Check the PgAdmin-III preferences; there may be an option to control its >> preferred bytea format. >> > There's no option to control this. > Thanks for confirming that. Is it really best for PgAdmin-III to have a different default than Pg its self? -- Craig Ringer
On Tue, 2012-07-31 at 14:06 +0800, Craig Ringer wrote: > On 07/31/2012 01:50 PM, Guillaume Lelarge wrote: > >> Check the PgAdmin-III preferences; there may be an option to control its > >> preferred bytea format. > >> > > There's no option to control this. > > > Thanks for confirming that. > > Is it really best for PgAdmin-III to have a different default than Pg > its self? > Well, we didn't until we had an issue to get informations from the tgargs column of the pg_trigger catalog. I don't remember the details right now, and I still didn't check how psql works with this, so I may be wrong. The only way I could find to fix the issue was to set bytea_output to escape. It probably is specific to pgAdmin. If we can't fix this another way, we can have a workaround. We only need this setting for pgAdmin's browser. Not the query tool. It would be a nice and quick workaround. -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.com
On Tue, Jul 31, 2012 at 9:07 AM, Guillaume Lelarge <guillaume@lelarge.info> wrote: > On Tue, 2012-07-31 at 14:06 +0800, Craig Ringer wrote: >> On 07/31/2012 01:50 PM, Guillaume Lelarge wrote: >> >> Check the PgAdmin-III preferences; there may be an option to control its >> >> preferred bytea format. >> >> >> > There's no option to control this. >> > >> Thanks for confirming that. >> >> Is it really best for PgAdmin-III to have a different default than Pg >> its self? >> > > Well, we didn't until we had an issue to get informations from the > tgargs column of the pg_trigger catalog. I don't remember the details > right now, and I still didn't check how psql works with this, so I may > be wrong. The only way I could find to fix the issue was to set > bytea_output to escape. It probably is specific to pgAdmin. > > If we can't fix this another way, we can have a workaround. We only need > this setting for pgAdmin's browser. Not the query tool. It would be a > nice and quick workaround. I wouldn't call that a workaround, I would call it an actual fix. And I would call the current state a bug, because it overrides the users settings. Even if the user has changed it with e.g. ALTER ROLE SET, pgadmin will override it. We should try to keep the environment for the query tool connection as clean as possible. So if it's reasonably easy to do this (I haven't looked at the code, but I would assume it is), then I suggest doing that *and* backpatching it so it'll go into the next version of the stable branch. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/