[LibPQ] Trying PQconnectStart: it doesn't seem to connect

On 23/01/2010 11:52 PM, Alessandro Agosto wrote:

Hi,
i'm a python/c developer and i need an asynchronous (python) interface
to communicate with a database. I like PostgreSQL so i'm trying to write
a mock-up for a python extension module to use LibPQ in my asynchronous
programs.

What's wrong with psycopg2 for this purpose?

Python's threading support (in the standard CPython interpreter) is
awful due to the global interpreter lock. However, it works for waiting
on blocking sockets as the GIL is released before entering most
C-language routines. So you should be able to use psycopg2 in dedicated
I/O worker threads just fine.

If you're trying to use non-blocking sockets and select(...) with libpq,
well, _then_ you'll have to go outside psycopg2. Be aware, though, that
using SSL sockets in a non-blocking manner can be ... complicated ... so
look into that in detail before deciding on this path. Multiple threads
with blocking connections is likely to be a LOT easier.

Frankly, though, you're never going to get wonderful results out of
this. Twisted tries, but you've probably seen the issues it has working
around the GIL and the limited success it has doing so. CPython's GIL
dooms it to be a pretty shoddy language for high concurrency use,
whether you use an async socket server model or a blocking threaded model.

Can someone tell me what i wrong? And if you know some resource that
explains with an example how i should to verify the connection during
the select/poll, would be appreciated.

You can't reliably verify that a connection is alive with select/poll.
TCP/IP timeouts are very long and until the connection times out at the
TCP/IP level, it might appear fine even though the peer died hours ago.

--
Craig Ringer

From: Alessandro Agosto <the.6one6@gmail.com>
Date: 2010/1/24
Subject: Re: [GENERAL] [LibPQ] Trying PQconnectStart: it doesn't seem to
connect
To: Craig Ringer <craig@postnewspapers.com.au>

Hi, thank you for your reply.

2010/1/24 Craig Ringer <craig@postnewspapers.com.au>

What's wrong with psycopg2 for this purpose?

Python's threading support (in the standard CPython interpreter) is awful
due to the global interpreter lock. However, it works for waiting on
blocking sockets as the GIL is released before entering most C-language
routines. So you should be able to use psycopg2 in dedicated I/O worker
threads just fine.

If you're trying to use non-blocking sockets and select(...) with libpq,
well, _then_ you'll have to go outside psycopg2. Be aware, though, that
using SSL sockets in a non-blocking manner can be ... complicated ... so
look into that in detail before deciding on this path. Multiple threads with
blocking connections is likely to be a LOT easier.

Yes, i know psycopg2 and i like it. But as you said i'm trying to use
non-blocking socket and epoll (but writing a mock up i can use poll and work
on events later). Honestly i'm not aware on every problem that async
programming comport (like specific ssl problems). Anyway multiplexing
programming is, yes complicated, but also powerful and a program that uses
multiplexing has a lower footprint than thread/fork programs. In python, as
you said there is the "evil" GIL.

Frankly, though, you're never going to get wonderful results out of this.

Twisted tries, but you've probably seen the issues it has working around the
GIL and the limited success it has doing so. CPython's GIL dooms it to be a
pretty shoddy language for high concurrency use, whether you use an async
socket server model or a blocking threaded model.

I know twisted but sincerely i don't like to write code that makes a lot of
callbacks, but this is my personal opinion. I've used it a lot of times.
Probably you are right but on network programming async servers are faster
than classic threaded versions, also if mean more problems. The lack of an
asynchronous database interface is a problem that i could solve using more
processes of my webserver but this isn't a real solution. So i'm on this
project.

Can someone tell me what i wrong? And if you know some resource that

explains with an example how i should to verify the connection during
the select/poll, would be appreciated.

You can't reliably verify that a connection is alive with select/poll.

TCP/IP timeouts are very long and until the connection times out at the
TCP/IP level, it might appear fine even though the peer died hours ago.

I'm not yet within select/poll cycle, this is the first call that should
return CONNECTION_OK or CONNECTION_BAD (refering to docs). Probably i wrong
but this call seems to return another status, or as you said i cannot know
(also if i'm out of any cycle) if the connection is ok or not.

If the async apis became a problem , can i call the *synchronous* apis
through my webserver's main event loop? This should work or not? (i mean to
make the connection asynchronous).

Thank you again,
Greetings.
--
Alessandro A.

--
Alessandro A.

Alessandro Agosto wrote:

I'm not yet within select/poll cycle, this is the first call that should
return CONNECTION_OK or CONNECTION_BAD (refering to docs).

That would be the behavior of PQconnectdb(), not PQconnectStart().

Have you read that part of the doc:

<quote>
Other states might also occur during (and only during) an asynchronous
connection procedure. These indicate the current stage of the connection
procedure and might be useful to provide feedback to the user for example.
These statuses are:

CONNECTION_STARTED

Waiting for connection to be made.
CONNECTION_MADE

Connection OK; waiting to send.
CONNECTION_AWAITING_RESPONSE

Waiting for a response from the server.
CONNECTION_AUTH_OK

Received authentication; waiting for backend start-up to finish.
CONNECTION_SSL_STARTUP

Negotiating SSL encryption.
CONNECTION_SETENV

Negotiating environment-driven parameter settings.

Note that, although these constants will remain (in order to maintain
compatibility), an application should never rely upon these occurring in a
particular order, or at all, or on the status always being one of these
documented values. An application might do something like this:

switch(PQstatus(conn))
{
case CONNECTION_STARTED:
feedback = "Connecting...";
break;

case CONNECTION_MADE:
feedback = "Connected to server...";
break;

Thank you for your reply.
2010/1/25 Daniel Verite <daniel@manitou-mail.org>

Alessandro Agosto wrote:

I'm not yet within select/poll cycle, this is the first call that should
return CONNECTION_OK or CONNECTION_BAD (refering to docs).

That would be the behavior of PQconnectdb(), not PQconnectStart().

Have you read that part of the doc:

No, i didn't read. My fault. I thought that the last section was just about
the cycle.
Thank you for the report, i'll try in this way.

Greetings,
Alex.
--
Alessandro A.