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

All we need is an easy explanation of the problem, so here it is.

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.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 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.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply