[pyDAL] really strange issue with psycopg2

[Giovanni Barillari]

Hi all,

I was working with the latest version of pyDAL (16.06) with postgresql 9.4 and psycopg2.

Now the issue is this: the boolean parser fails, converting True values to False. 

I inspected a bit the code and saw that on the tests we have on travis, the values returned by psycopg2 are strings: [('T',), ('F',)]

While on my server the values returned by psycopg2 are booleans: [(True,), (False,)]

Now, the parser fails because is expecting a string, but the question is: why the hell the type change on different system? This is python 2.7, postgre 9.4, psycopg 2.6.1 on both systems.

Anybody can help me understanding this? Also because I'm afraid other people are affected by this.

/Giovanni

[Niphlod]

really strange indeed, I can't replicate at all, although I'm on windows.

db.executesql() returns T,F , pydal's returns correctly True,False.

psycopg2 raw doesn't do conversions.

sure your table is created with character(1) as the boolean field ?

[Giovanni Barillari]

Thank you niphlod for your time.

I think I got it. 

First of all, the database I'm working with wasn't created with pydal, so I think the boolean field isn't a char(1) type but instead a postgre boolean type ones: https://www.postgresql.org/docs/9.4/static/datatype-boolean.html

Secondarily, in pyDAL 16.06, the line of the parse converting booleans to strings in order to make the comparison is commented out since I thought it was redundant: https://github.com/web2py/pydal/blob/master/pydal/parsers/base.py#L34

Now, I can make a bugfix release of pyDAL where I put back the commented line, but, actually I have some concerns:

1) why pydal is using char(1) instead of boolean type on fields since it would benefit from automatic parse of boolean values from the driver?

2) if that line was present, then anybody who wrote it must knew the problem, am I right?

3) I think the "correct" behaviour is the one I am seeing on my side, where psycopg2 is returning booleans for booleans, not strings.

Given all of this,

should we start a discussion about it?

/Giovanni

[Giovanni Barillari]

@all this is my proposal:

Release a bugfix release of pyDAL with:

1) the line I've commented out from the basic parser back in

2) a 'postgres3' adapter that uses 'BOOLEAN' type for 'boolean' fields and doesn't parse the values from the driver

If you all agree on this I would like to publish this today/tomorrow.

A part from this, I would really like to start a discussion on all of this, since I'm not happy with the current behaviour, feels wrong.

@niphlod @massimo @ilvalle @mcomitini and others: thoughts?

/Giovanni

[Michele Comitini]

That stuff of the boolean T|F keeps biting me also.  I think it should be handled in a portable way if possible.

 IMHO in newly created tables would be smarter to use the BOOLEAN type under, as that solves a lot of problems and that is something one would expect to find when accessed in the db.

I do not know if another adapter is needed or a type, that could be used in other db also?

mic

--
-- mail from:GoogleGroups "web2py-developers" mailing list
make speech: web2py-d...@googlegroups.com
unsubscribe: web2py-develop...@googlegroups.com
details : http://groups.google.com/group/web2py-developers
the project: http://code.google.com/p/web2py/
official : http://www.web2py.com/
---
You received this message because you are subscribed to the Google Groups "web2py-developers" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py-develop...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Massimo DiPierro]

we can always make another adapter and recommend the new one

[Richard Vézina]

Personnally, I always override web2py bool defined field as bool into postgres db model... I am pretty sure in the past (long time ago) web2py use to create bool in postgres, but it had been reverted to use char(1) as maybe because it the way bool are represent in most backend like Oracle for instance. I prefer dealing with bool in postgres as I often have to make manual intervention in postgres directly and find boolean type field more convenient.

Richard

[Massimo DiPierro]

If we could detect the type from the .table file we can leave it as strings if defined as such, use bool for newly created fields/tables.

[Richard Vézina]

It would be great... How to tell web2py to create postgres to create bool field from the start though?

[Giovanni Barillari]

This is not operable since it won't be applicable on every table created outside web2py.

I think creating a new adapter and reccomend it for new applications is the best option we have right now.

/Giovanni

[Massimo DiPierro]

if the table is created outside we2py there is no risk of migration and the current method of parsing the response can be made robust and accept both T,F and true, false. 

[Giovanni Barillari]

Whoops, you're right :)

Since this will impact the migrator, let's plan it accurately and schedule this for the next release.

[Giovanni Barillari]

I've just pushed this PR in the meantime: https://github.com/web2py/pydal/pull/373

/Giovanni

[Niphlod]

char(1) is all things considered in 2016 something to avoid for a boolean.

That being said to break out from backward-compatibility and to avoid wasting cpu parsing over and over de-encouraging the - at this point - legacy adapter and promoting a new one with better defaults IMHO is the only choice to keep pushing forward.

BTW: if you ever wondered why niphlod keeps pushing mssqlx adapters, you got it now :-P Also, pushing for separating as much as we can from the base adapter (parse(), in this instance) has the same underlying motivations.

I prefer having the user making a choice one time than let the code handle all the possible variations while parsing each field for each row.