headers
Daniele Varrazzo | 2 Aug 2010 02:33
Picon
Gravatar

Re: Psycopg 2.2.x issue with PgBouncer

On Wed, Jul 28, 2010 at 4:28 PM, Marko Kreen <markokr@...> wrote:

> This behavior is bad.  The original user code was buggy - it closed
> the connection while in mid-transaction.  PgBouncer was correct
> to drop the connection to force the server to do rollback.
>
> I recently had use-case in production env where such behaviour
> (making buggy client act as non-buggy client) was clearly broken:
>
> There was COPY-to-COPY pipeline, where target side got error.
> Now if I do .close() on source side, what happens?  It starts
> to consume data until command end... Which is silly.
>
> Same can happen with big (async?) SELECT, where it's impossible
> to get rid of the connection.  Even __del__ will consume data...
>
> Only way to get out of such situation would be:
>
>  os.close(db.cursor().fileno())
>
> but it seems silly to force users to use such workaround.
>
> My conclusion: .close() and .__del__() should close the
> connection immediately, without any workarounds, to guarantee
> predictable behavior.  There is no need to drain data to
> issue ROLLBACK, because mid-trancation disconnect will
> do that anyway.

For me doing the least possible on __del__, mostly don't try to issue
queries, is a sensible approach.
(Continue reading)

Permalink | Reply | 3 comments |
headers
Daniele Varrazzo | 2 Aug 2010 02:56
Picon
Gravatar

Re: Infinite loop on error

On Wed, Jul 28, 2010 at 4:54 PM, Marko Kreen <markokr@...> wrote:
> Psycopg has several loops in form:
>
> while ((curs->pgres = PQgetResult()) != NULL)
> {
> }
>
> or
>
> do {
>  pgres = PQgetResult();
> } while (pgres != NULL);
>
>
> The problem is that if libpq gets so fatal error it decides
> to close the connection, PQgetResult() will never return NULL
> from that connection, thus infinite loop.

Are you sure about that? I don't have the libpq source code handy, I
will surely check that. But, if it doesn't return a NULL with the
connection in broken state... what does it return?

PQgetResult() also returns null when there is no result to fetch. I
may have extrapolated excessively assuming that it returns null when
the connection is closed too, but it doesn't seem an excessively
stretched interpretation.

I'm curious to do some test in the next days.

-- Daniele
(Continue reading)

Permalink | Reply | 0 comments |
headers
Marko Kreen | 2 Aug 2010 08:56
Picon

Re: Infinite loop on error

On 8/2/10, Daniele Varrazzo <daniele.varrazzo@...> wrote:
> On Wed, Jul 28, 2010 at 4:54 PM, Marko Kreen <markokr@...> wrote:
>  > Psycopg has several loops in form:
>  >
>  > while ((curs->pgres = PQgetResult()) != NULL)
>  > {
>  > }
>  >
>  > or
>  >
>  > do {
>  >  pgres = PQgetResult();
>  > } while (pgres != NULL);
>  >
>  >
>  > The problem is that if libpq gets so fatal error it decides
>  > to close the connection, PQgetResult() will never return NULL
>  > from that connection, thus infinite loop.
>
>
> Are you sure about that? I don't have the libpq source code handy, I
>  will surely check that.

Yeah, found out via strace/gdb...  strace was showing infinite
close(-1) - thus libpq was trying to close already closed connection.

> But, if it doesn't return a NULL with the
>  connection in broken state... what does it return?

Probably PQmakeEmptyPGresult(conn, PGRES_FATAL_ERROR)
(Continue reading)

Permalink | Reply | 2 comments |
headers
Marko Kreen | 2 Aug 2010 09:21
Picon

Re: Psycopg 2.2.x issue with PgBouncer

On 8/2/10, Daniele Varrazzo <daniele.varrazzo@...> wrote:
> On Wed, Jul 28, 2010 at 4:28 PM, Marko Kreen <markokr@...> wrote:
>
>  > This behavior is bad.  The original user code was buggy - it closed
>  > the connection while in mid-transaction.  PgBouncer was correct
>  > to drop the connection to force the server to do rollback.
>  >
>  > I recently had use-case in production env where such behaviour
>  > (making buggy client act as non-buggy client) was clearly broken:
>  >
>  > There was COPY-to-COPY pipeline, where target side got error.
>  > Now if I do .close() on source side, what happens?  It starts
>  > to consume data until command end... Which is silly.
>  >
>  > Same can happen with big (async?) SELECT, where it's impossible
>  > to get rid of the connection.  Even __del__ will consume data...
>  >
>  > Only way to get out of such situation would be:
>  >
>  >  os.close(db.cursor().fileno())
>  >
>  > but it seems silly to force users to use such workaround.
>  >
>  > My conclusion: .close() and .__del__() should close the
>  > connection immediately, without any workarounds, to guarantee
>  > predictable behavior.  There is no need to drain data to
>  > issue ROLLBACK, because mid-trancation disconnect will
>  > do that anyway.
>
>
(Continue reading)

Permalink | Reply | 2 comments |
headers
Daniele Varrazzo | 2 Aug 2010 13:45
Picon
Gravatar

Re: Psycopg 2.2.x issue with PgBouncer

On Mon, Aug 2, 2010 at 8:21 AM, Marko Kreen <markokr@...> wrote:
> On 8/2/10, Daniele Varrazzo <daniele.varrazzo@...> wrote:

>>  I don't know PgBouncer at all so I'm asking Jason -- or whoever can
>>  answer: is just the badly-closed connection to be forced out of the
>>  pool or is the "unclean server" is serious enough to make it consider
>>  the entire pool inconsistent?
>
> Only one connection would be dropped.
>
>>  On the base that you can always call rollback() before closing/gc-ing
>>  the connection, I'm actually tempted to make the buggy behaviour "a
>>  feature".
>
> You seem to forget that it can also call .commit().  And in
> autocommit mode the issue should not even arise, but I'm
> not sure about current code...

No, I just mentioned rollback because this would replicate, from a
data state PoV, what happens to the server both if the client closes
the connection with a straight PQfinish and what used to happen in
psycopg on close()/GC before introducing the bug. commit() would
obviously close the transaction too but replacing close() with
rollback() (or prepending a rollback() call) would keep the semantic
of existing programs untouched, while giving PgBouncer the correct
message.

If this is agreed, I can change the documentation to better explain
what happens on close() and underline the need of closing with a
closed transaction if middleware such as PgBouncer is involved. I'd
(Continue reading)

Permalink | Reply | 0 comments |
headers
Daniel França | 3 Aug 2010 15:52
Picon
Gravatar

Re: error psycopg in Django/MacOSX

ok, this error doesn't happen anymore, I installed python32bits
My website is running, but when I try to insert some data at the
database I get the error:
Can't adapt type <<timezone>>
I tried to use timezone as 'US/Eastern', 'America/Sao_Paulo'
but the problem remains.
Maybe it's because my postgresql is 64 bits? How do I know if it is?
can anyone help me?

2010/7/18 Daniel França <daniel.franca <at> gmail.com>:
> The results of the commands running:
> Daniel-Francas-MacBook-Pro:psycopg2-2.2.1 danielfranca$ python -c 'import
> pprint; from distutils import sysconfig;
> pprint.pprint(sysconfig.parse_makefile(sysconfig.get_makefile_filename()))'
> {'AR': 'ar',
>  'ARFLAGS': 'rc',
>  'ASDLGEN': '/Users/sysadmin/X/r27/Parser/asdl_c.py',
>  'ASDLGEN_FILES': '/Users/sysadmin/X/r27/Parser/asdl.py
> /Users/sysadmin/X/r27/Parser/asdl_c.py',
>  'AST_ASDL': '/Users/sysadmin/X/r27/Parser/Python.asdl',
>  'AST_C': '/Users/sysadmin/X/r27/Python/Python-ast.c',
>  'AST_C_DIR': '/Users/sysadmin/X/r27/Python',
>  'AST_H': '/Users/sysadmin/X/r27/Include/Python-ast.h',
>  'AST_H_DIR': '/Users/sysadmin/X/r27/Include',
>  'BASECFLAGS': '-fno-strict-aliasing -fno-common -dynamic',
>  'BASEMODLIBS': '',
>  'BINDIR': '/Library/Frameworks/Python.framework/Versions/2.7/bin',
>  'BINLIBDEST':
> '/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7',
>  'BLDLIBRARY': '',
(Continue reading)

Permalink | Reply | 4 comments |
headers
Daniele Varrazzo | 3 Aug 2010 16:52
Picon
Gravatar

Re: error psycopg in Django/MacOSX

2010/8/3 Daniel França <daniel.franca@...>:
> ok, this error doesn't happen anymore, I installed python32bits
> My website is running, but when I try to insert some data at the
> database I get the error:
> Can't adapt type <<timezone>>
> I tried to use timezone as 'US/Eastern', 'America/Sao_Paulo'
> but the problem remains.
> Maybe it's because my postgresql is 64 bits? How do I know if it is?
> can anyone help me?

Hello,

no, you are past the 32/64 bits problem: now psycopg seems running fine.

Psycopg doesn't know what the object you are trying to pass to the
query is (timezone). Is it a class of yours or something else? Can you
provide an example of the query you are trying to run and the objects
you are passing it?

I don't think postgres has a "timezone" data type: maybe you want to
save a timestamp with time zone instead.

Regards,

-- Daniele
Permalink | Reply | 2 comments |
headers
Daniel França | 3 Aug 2010 16:59
Picon
Gravatar

Re: error psycopg in Django/MacOSX

Hi Daniele, I'm not at home right now(where the error happens), but
I'll try to explain.
I'm using psycopg2 w/ django.
in django there's a timezone env. variable that I set in a settings file.
something like this:
TIMEZONE="US/Eastern"

the problem is, whatever I put there I get a message error like this:
"Can't adapt type 'US/Eastern'"
if I change to TIMEZONE="America/Sao_Paulo" I get: "Can't adapt type
'America/Sao_Paulo'"
it was working fine this way in GNU/Linux, but now in Mac OSX I got
this error. Perhaps  it's something internal to django framework?

2010/8/3 Daniele Varrazzo <daniele.varrazzo@...>:
> 2010/8/3 Daniel França <daniel.franca@...>:
>> ok, this error doesn't happen anymore, I installed python32bits
>> My website is running, but when I try to insert some data at the
>> database I get the error:
>> Can't adapt type <<timezone>>
>> I tried to use timezone as 'US/Eastern', 'America/Sao_Paulo'
>> but the problem remains.
>> Maybe it's because my postgresql is 64 bits? How do I know if it is?
>> can anyone help me?
>
> Hello,
>
> no, you are past the 32/64 bits problem: now psycopg seems running fine.
>
> Psycopg doesn't know what the object you are trying to pass to the
(Continue reading)

Permalink | Reply | 1 comment |
headers
Joe Abbate | 3 Aug 2010 16:16
Favicon
Gravatar

Re: error psycopg in Django/MacOSX

On 08/03/2010 09:52 AM, Daniel França wrote:
> Maybe it's because my postgresql is 64 bits? How do I know if it is?
> can anyone help me?
>    

select version();  issued against any database should (usually) tell 
you:  PG version, compiler used and OS.

Joe
_______________________________________________
Psycopg mailing list
Psycopg <at> lists.initd.org
http://lists.initd.org/mailman/listinfo/psycopg
Permalink | Reply | 0 comments |
headers
Daniele Varrazzo | 3 Aug 2010 17:49
Picon
Gravatar

Re: error psycopg in Django/MacOSX

2010/8/3 Daniel França <daniel.franca@...>:
> Hi Daniele, I'm not at home right now(where the error happens), but
> I'll try to explain.
> I'm using psycopg2 w/ django.
> in django there's a timezone env. variable that I set in a settings file.
> something like this:
> TIMEZONE="US/Eastern"
>
> the problem is, whatever I put there I get a message error like this:
> "Can't adapt type 'US/Eastern'"
> if I change to TIMEZONE="America/Sao_Paulo" I get: "Can't adapt type
> 'America/Sao_Paulo'"
> it was working fine this way in GNU/Linux, but now in Mac OSX I got
> this error. Perhaps  it's something internal to django framework?

Yes, looks like every timezone in django is a type on its own. Maybe
on Linux you tested with a previous version where the timezones were
just strings. If this is the case, maybe passing
str(settings.TIMEZONE) will work.

I've django 1.2 installed anyway and here:

 - the setting I have is called TIME_ZONE, not TIMEZONE
 - this setting is a string.

Are you testing with django trunk? Can you report what the setting
really is from an interactive shell?

    python /path/to/project/manage.py shell
    >>> from django.conf import settings
(Continue reading)

Permalink | Reply | 0 comments |

Gmane