Error on pg_settings.bytea_output for pg9.1

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/