Psycopg psycopg

Comments and changes to this ticket

• 

  Daniele Varrazzo August 10th, 2014 @ 01:30 PM

  • State changed from “new” to “invalid”

  Reducing to empty is not the only normalization rule Postgres uses. PG for instance normalizes [10,20] to [10,21).

  Psycopg doesn't apply the rules PG uses because it would need more knowledge of the PG catalog, and even then the PG rule for canonicalization could be a C library psycopg cannot parse. Among the other things it doesn't due is enforcing the lower bound to be lesser than the upper bound, because the Python order operator could be different from the order in postgres (it happens e.g. if you create a range over strings, where you can choose any collation).

  So the psycopg range doesn't actually do all the transformations/checks that may happen in the server, because if they happen to be subtly different between Python and Postgres we may and up e.g. with records we cannot read, queries returning unexpected results etc. Psycopg ranges are instead small value objects designed to represent faithfully what is in the server and to transmit to the server exactly what the user asks for, regardless of what the server will do with this information (rejecting or canonicalizing).

  In your example Postgres would just canonicalize the range you created, so even if you INSERT a record with an unnormalized empty range it would be read as empty in further selects:

  >>> cur.execute("select %s::int4range", [psycopg2.extras.NumericRange(10,10,'[)')])
  >>> print cur.fetchone()[0]
  NumericRange(empty=True)
  

  We may add an explicit check so that ranges with lower == upper and at least one bound open would be converted to empty, but it would be only a partial implementation of the canonicalization rules the server applies and it would work with the implicit implication that the equality operator in Postgres works exactly the same way as in Python, which is more likely than with the ordering operators but still I don't feel to guarantee, especially because a range could be created on unexpected data types.

  To wrap up, psycopg's primary role is to represent faithfully what is in the server and make Python able to throw at the server everything it is able to accept. The Range objects are created with this intent, not with the one to reproduce faithfully everything the server does: it would be a client-side implementation of server-side rules and it would be wrong for a driver to go along this path.

  I'm happy to hear your use case to understand if there is a real need for ranges to be canonicalized, in which case we can think about adding this check; but if it's only because you assume psycopg ranges behave exactly like PG ones, it is not the case by design.