Issue with relations on postgres timestamps with the value "-infinity"

[Michel Albert]

I've run into an issue with tables containing the special "-infinity" value in postgres. More precisely, when resolving relations on such tables.

SQLAlchemy will convert the value "-infinity" into "datetime(1, 1, 1, 0 , 0)" and try to find rows in the related table with that value. This will obviously not match and return no rows. Using a "joinedload" on such relations will work, but then the backreference will have the same issue.

I've created a small runnable example here: https://gist.github.com/exhuma/3e7d0c7d176b41b208af684e55bcf020

It should be self explanatory.

I have not tested this with positive "infinity" but I assume it will be converted into "datetime.datetime.max" and thus fall victim to the same issue.

Any ideas for a workaround which does not involve modifying the values in the DB?

[Mike Bayer]

On Thu, Oct 26, 2017 at 11:16 AM, Michel Albert <exh...@gmail.com> wrote:
> I've run into an issue with tables containing the special "-infinity" value
> in postgres. More precisely, when resolving relations on such tables.
>
> SQLAlchemy will convert the value "-infinity" into "datetime(1, 1, 1, 0 ,
> 0)" and try to find rows in the related table with that value.

SQLAlchemy does not apply conversions of any kind to date values
except in the case of the SQLite dialect. In this case, you are
seeing the database driver's documented behavior:

http://initd.org/psycopg/docs/usage.html#infinite-dates-handling

The workaround they provide allows your case to work, note you need to
adapt to datetime, not date:

import psycopg2.extensions

class InfDateAdapter:
def __init__(self, wrapped):
self.wrapped = wrapped
def getquoted(self):
if self.wrapped == datetime.datetime.max:
return b"'infinity'::date"
elif self.wrapped == datetime.datetime.min:
return b"'-infinity'::date"
else:
return psycopg2.extensions.DateFromPy(self.wrapped).getquoted()

psycopg2.extensions.register_adapter(datetime.datetime, InfDateAdapter)

This same adaptation can be applied at the SQLAlchemy level using TypeDecorator.

This will
> obviously not match and return no rows. Using a "joinedload" on such
> relations will work, but then the backreference will have the same issue.
>
> I've created a small runnable example here:
> https://gist.github.com/exhuma/3e7d0c7d176b41b208af684e55bcf020
>
> It should be self explanatory.
>
> I have not tested this with positive "infinity" but I assume it will be
> converted into "datetime.datetime.max" and thus fall victim to the same
> issue.
>
> Any ideas for a workaround which does not involve modifying the values in
> the DB?
>

> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Michel Albert]

Thanks for the quick update.

I did not have the reflex to look into psycopg2 for this. But indeed, a custom SA type would help here too.