Search code examples
c#postgresqlfloating-pointnpgsql

Npgsql: Old floating point representation for timestamps not supported

I've written a basic script that syncs data from a PostgreSQL database to another system, which executes in my test/development environment without issue, using:

  • PostgreSQL 9.4
  • Npgsql dll version 3.0.3

Once we ran the script in the live production environment, we hit the following error:

"Old floating point representation for timestamps not supported"

Researching the issue, I found this is related to a "deprecated compile-time option of PostgreSQL which switches to a floating-point representation of some date/time fields. Npgsql (currently) does not support this mode."

Here is a link to the relevant source code.

In production, the PostgreSQL server is running version 8.3, and for internal reasons, the server manager has declined to upgrade the PostgreSQL server at this time. (Current Npgsql version doesn't officially support PosgreSQL prior to version 9. doh!)

Question(s):

  • Is there any way to modify the PostgreSQL 8.3 "integer_datetimes" configuration setting without fully reinstalling the application or upgrading?
  • Alternately, is there any way to downgrade my Npgsql dlls to an older version where this configuration setting was supported?
  • Any recommendations for an alternative to Npgsql if necessary? I was thinking about refactoring the code to use ADO.NET (but I'm not sure if that would be recommended).
Solution

  • I'm not one hundred percent sure about this, but Npgsql 2.2.7 should be able to work properly with the PostgreSQL legacy floating point format since it uses text transfer to read and write values not binary. This should allow you to interact with the old database.

    However, it's severely discouraged to continue using PostgreSQL 8.3 in any way - 8.3 reached its end-of-life on February 2013... You should definitely experiment with migrating to 9.4 simply by dumping the data and restoring it into the new environment, it may work well.

    Also note that while new versions, like 3.0.4, in general do work on pre-9.0 PostgreSQL - it's just that they're not tested in any way and we probably won't fix any non-trivial bugs you may run across.