Re: Error on pg_settings.bytea_output for pg9.1

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