Re: Implementing Frontend/Backend Protocol TCP/IP

On 27/10/2009 3:20 PM, Raimon Fernandez wrote:

> REALbasic has plugin for PostgreSQL, but they are synchronous and
> freeze the GUI when interacting with PG. This is not a problem noramlly,
> as the SELECTS/UPDATES/... are fast enopugh, but sometimes we need to
> fetch 1000, 5000 or more rows and the application stops to respond, I
> can't have a progressbar because all is freeze, until all data has come
> from PG, so we need a better way.

You're tackling a pretty big project given the problem you're trying to
solve. The ongoing maintenance burden is likely to be significant. I'd
be really, REALLY surprised if it was worth it in the long run.

Can you not do the Pg operations in another thread? libpq is safe to use
in a multi-threaded program so long as you never try to share a
connection, result set, etc between threads. In most cases, you never
want to use any of libpq outside one "database worker" thread, in which
case it's dead safe. You can have your worker thread raise flags / post
events / whatever to notify the main thread when it's done some work.

If that approach isn't palatable to you or isn't suitable in your
environment, another option is to just use a cursor. If you have a big
fetch to do, instead of:

SELECT * FROM customer;

issue:

BEGIN;
DECLARE customer_curs CURSOR FOR SELECT * FROM customer;

... then progressively FETCH blocks of results from the cursor:

FETCH 100 FROM customer_curs;

... until there's nothing left and you can close the transaction or, if
you want to keep using the transaction, just close the cursor.

See:

http://www.postgresql.org/docs/8.4/static/sql-declare.html
http://www.postgresql.org/docs/8.4/static/sql-fetch.html
http://www.postgresql.org/docs/8.4/static/sql-close.html

--
Craig Ringer