postgres 9.6 ? problem with bytea format v9 and after

  1. last week

    Jean-Yves P

    May 18 Pre-Release Testers, Xojo Pro Europe (France, Besancon)
    Edited last week by Jean-Yves P

    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

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

    Specifically, Postgres 9.0 changed the handling of escape strings used with bytea: previous versions treated \ in regular string literals such as '\' as escape characters, whereas newer versions use the escape string syntax E'\'.

  2. Kevin W

    May 18 Pre-Release Testers, Xojo Pro

    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.

  3. Jean-Yves P

    May 18 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

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

  4. Dirk C

    May 18 Pre-Release Testers, Xojo Pro Galactic Sector ZZ9 Plural Z A...

    @Jean-YvesPochez old database (in fact 8.4 postgres version)

    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.

  5. Are both 64 bit Postgres installations?

  6. Jean-Yves P

    May 18 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    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.

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

  8. Jean-Yves P

    May 18 Pre-Release Testers, Xojo Pro Europe (France, Besancon)
    Edited last week by Jean-Yves P

    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.

  9. Dirk C

    May 19 Pre-Release Testers, Xojo Pro Answer Galactic Sector ZZ9 Plural Z A...

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

    Specifically, Postgres 9.0 changed the handling of escape strings used with bytea: previous versions treated \ in regular string literals such as '\' as escape characters, whereas newer versions use the escape string syntax E'\'.

  10. Jean-Yves P

    May 19 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    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 .

  11. Jean-Yves P

    May 19 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    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.

  12. 6 days ago

    Tobias B

    May 20 Pre-Release Testers, Xojo Pro Bern, Switzerland

    how can I convert the hex format to the escape without changing the postgres.conf file ?

    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.

    remove the "\x" at the beginning of the hex string returned by postgres plugin
    then apply xojo build-in decodehex method to it

    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: Feedback Case #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.

  13. Jean-Yves P

    May 20 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    @Tobias B 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.

    I prefer to adapt my methods to all the possibilities, instead of forcing a may be deprecated format on opening the connexion...

  14. Jean-Yves P

    May 20 Pre-Release Testers, Xojo Pro Europe (France, Besancon)

    @Tobias B while I do the same, DecodeHex can actually deal with a \x prefix on it's own, it is in fact ignored.

    confirmed it works without removing the "\x"
    thanks for the tip !

or Sign Up to reply!