postgres 9.6 ? problem with bytea format v9 and after

Hi group,

I have a postgres database on a remote server , and it is a 8.4 version.
I store pictures in a bytea field, and everything work fine.
I recently moved to another server, so I pgdumped the database, and imported it in the new server
the server is a 9.6 postgres version, it is first sight the only difference.
and my bytea field are wrongly stored !
I encodehex the picture before saving, the program is strictly the same, and the bytes stored in the database are not the same, and so unreadable back.

any hint ?

edit: there is only one user, and the rights are good. if I try to write to another field in the same table, I can do it.
edit2: changed 9.4 to 8.4

I’d check the cluster encoding to see if they match on the old and new server. I recently set up a postgres server while ssh’d remotely and I was caught off guard by the locale not being correct in the SSH session. When postgres installed it set it up as a posix encoding I think.

I eventually figured out what went wrong and fixed the sshd config so it would use the server locale no matter what the remote wanted. I then dumped the database and recreated the cluster as UTF_8 and loaded the data back in. It’s worked fine since.

I don’t have 9.6 setup anywhere, but from your description it sounds like it could be a db encoding problem so I just throw this out there to hopefully save you some time if that’s what it is. Took me a while to notice the problem with my DB and a while longer to figure out what actually caused it.

old database (in fact 8.4 postgres version)

Owner postgres ACL {=Tc/postgres,postgres=CTc/postgres,qualio=CTc/postgres} Tablespace pg_default Default tablespace pg_default Encoding UTF8 Collation C Character type C Default schema public Allow connections? Yes Connected? Yes Connection limit -1 System database? No

new database (postgres 9.6)

Owner postgres ACL Tablespace pg_default Default tablespace pg_default Encoding UTF8 Collation C Character type C Default schema Default table ACL Default sequence ACL Default function ACL Default type ACL Allow connections? Yes Connected? Yes Connection limit -1 System database? No

so both encodings are UTF8…

To make sure that the backup is ok, I would start by installing an 8.4 locally and see if I can restore the backup to that one.
Than at least you will know if the problem is with the backup or the 8.4 vs 9.6
If you can’t find a solution at all, I would suggest you join irc channel #postgresql on freenode and ask if they have any ideas.

Are both 64 bit Postgres installations?

the old database is on a linux centos v7 64 bits
the new database is on a macos 10.12 so 64 bits
the client os is mac os I tried 10.9 and 10.12 same problem.
the xojo app is 32 bits.

Are you dumping to a plain sql file and using psql to restore it. I think you cannot restore from an older version with a custom dump and pg_restore

I do sqlexecute on the new database
what I dont understand is the rest of the database is fine, there are 30 tables …
only this portion, the only to store pictures with bytea does not work.

Maybe this is related: http://stackoverflow.com/a/28633839

Dirk, seems you found it …
on the old database my picture is the escape format
on the new one it is the hex format.
how can I convert the hex format to the escape without changing the postgres.conf file ?
I would like to handle it directly in my client app would be easier .

found it :
remove the “\x” at the beginning of the hex string returned by postgres plugin
then apply xojo build-in decodehex method to it
thanks to all.

you can change the default output for bytea columns with db.SQLExecute("SET bytea_output TO 'escape';") or 'hex' respectively on the session level. No need to alter the cluster configuration for this.

[quote]remove the “\x” at the beginning of the hex string returned by postgres plugin
then apply xojo build-in decodehex method to it[/quote]
while I do the same, DecodeHex can actually deal with a \\x prefix on it’s own, it is in fact ignored.

I thought the issues with blob handling between Xojo and PostgreSQL are well known, but since this has come up several times recently, I did an extensive analysis of what the Xojo plugin is actually doing and how to workaround this a few days ago. This finally resulted in a Feedback ticket, you may want to have a look to: <https://xojo.com/issue/48106>

Further, regarding backup / restore using pg_dump, esp. if you skip several versions, it is recommended to do the dump with the pg_dump util of the target version as this already knows what details have changed in between and how to handle some of the issues.

[quote=331768:@Tobias Bussmann]you can change the default output for bytea columns with db.SQLExecute(“SET bytea_output TO ‘escape’;”) or ‘hex’ respectively on the session level. No need to alter the cluster configuration for this.

[/quote]
I prefer to adapt my methods to all the possibilities, instead of forcing a may be deprecated format on opening the connexion…

[quote=331768:@Tobias Bussmann]while I do the same, DecodeHex can actually deal with a \x prefix on it’s own, it is in fact ignored.

[/quote]
confirmed it works without removing the “\x”
thanks for the tip !