1

I have this client where we installed a PGSQL server on his server (PGSQL 13 on port 5432) There already was a postgresql server installed (8.3, port 15432).

No problems here. Our application has a few 'bytea' columns where we store some encrypted data. Strange things happen when I launch a SQL on this column.

Exactly the same code gives different results on different computer. For example: "Select ByteAColumn from RandomTable"

Results on my computer:

"E712F7671E929600C926003C61C4C696C27E89C8D836F85898737799DFAB1CC4"

Result on server where the Postgresql is installed:

"\x45373132463736373145393239363030433932363030334336314334433639364332374538394338443833364638353839383733373739394446414231434334"

Result on other computer in the network of the client:

"E712F7671E929600C926003C61C4C696C27E89C8D836F85898737799DFAB1CC4"

Result on "Select ByteAColumn::varchar from RandomTable" in PGAdmin 4:

\x45373132463736373145393239363030433932363030334336314334433639364332374538394338443833364638353839383733373739394446414231434334

My application expects the "E712F7671E929600C926003C61C4C696C27E89C8D836F85898737799DFAB1CC4" one and throws error on the result with '\x4...'. I can avoid this using "Select encode(ByteAColumn,'escape') from RandomTable", this gives the right results. Modifying our entire application is near impossible.

In other words: the application runs fine on my computer, works fine on client computers / terminal server but not on the server where the postgreSQL database is hosted.

I've been using this application for over a year now. I have done many installations of this application and never have had this issue before. I've tested the query on other installations where everything is fine, and there I also get the '\x45...' result, but using the query in my application works great.

What causes this? Is it the PostgreSQL 8.3 server which interferes somehow? I've been testing and searching a whole day now and I can't seem to find the answer.

Help would be greatly appreciated. Thanks!

Morph
  • 33
  • 3
  • 1
    Unrelated to your problem, but: Postgres 8.3 is [no longer supported](https://www.postgresql.org/support/versioning/) you should plan an upgrade as soon as possible. –  Feb 24 '21 at 13:10
  • 1
    The default output format of `bytea` columns [changed in 9.0](https://www.postgresql.org/docs/release/9.0.0/#AEN132670) - you can control it through [bytea_output](https://www.postgresql.org/docs/current/runtime-config-client.html#GUC-BYTEA-OUTPUT) –  Feb 24 '21 at 13:10
  • Thanks! This already helps me a great deal. Only thing I don't understand: i'm using the postgresql 13, not the 8.3. Why do I get those strange results when executing on the server? It's like the database loads the wrong settings or something. Only the server is a problem, all the other computers work perfectly. – Morph Feb 24 '21 at 13:31

0 Answers0