How to prevent different connections treating bytea in same query differently?

Question

I find that identical postgresql queries issued by two different clients are handled differently. Specifically, bytea values are inserted differently.

A query that demonstrated the behaviour is this:

INSERT INTO "TestTable" ("Input") VALUES (decode('74657374', 'hex'))

74657374 is hexadecimal for 'test'. In one client, 'test' is inserted into the "Input" field, whether that field is text/varchar or bytea. That is the behaviour I desire. In another client, '\x74657374' is inserted into the "Input" field, whether it is text/varchar or bytea. This string is the postgresql literal representation of the bytea bytes of ASCII 'test'. But the sql literal syntax itself is inserted. I do not desire this behaviour.

I use a single piece of hand-written SQL, the bytea value only occurs "within" the query (if column "Input" has type Text then none of literals nor the receiving column have bytea type), and it seems unlikely to me that either client is parsing then rebuilding the query. Therefore it seems that the difference must be happening on the server where the query is executed. That means there must be some connection specific configuration setting which is altering the server behaviour.

Can anyone please tell me what connection specific settings could be altering this behaviour?

I know the queries are really behaving differently and it is not a display issue etc., because I can see the rows in the same table having different values ('test' and '\x74657374'). I have tried various alternative bytea handling methods, but they are all affected by this problem. For those who are interested, the "good" client is pgAdminIII and the "bad" client is Ruby PG gem. Though for the reason I gave above I believe there must be some built-in feature of postgresql supporting this behaviour.

Answer 1

I suspect that the difference is the appearance of a bytea depending on the current setting of bytea_output:

CREATE TABLE test (id integer PRIMARY KEY, t text, b bytea);

SHOW bytea_output;

 bytea_output 
--------------
 hex
(1 row)

INSERT INTO test VALUES (1, decode('74657374', 'hex'), decode('74657374', 'hex'));

TABLE test;

 id |     t      |     b      
----+------------+------------
  1 | \x74657374 | \x74657374
(1 row)

SET bytea_output = 'escape';

TABLE test;

 id |     t      |  b   
----+------------+------
  1 | \x74657374 | test
(1 row)

INSERT INTO test VALUES (2, decode('74657374', 'hex'), decode('74657374', 'hex'));

TABLE test;
 id |     t      |  b   
----+------------+------
  1 | \x74657374 | test
  2 | test       | test
(2 rows)

RESET bytea_output;

TABLE test;

 id |     t      |     b      
----+------------+------------
  1 | \x74657374 | \x74657374
  2 | test       | \x74657374
(2 rows)

The text column actually contains the result of the type cast from bytea to text, which depends on the current setting of bytea_output.

The bytea column contains the correct four bytes, but is displayed differently, depending on the current setting of bytea_output.