agora inbox for pgsql-interfaces@postgresql.org
help / color / mirror / Atom feedFrom: Craig Ringer <craig@postnewspapers.com.au>
To: Raimon Fernandez <coder@montx.com>
Cc: pgsql-general@postgresql.org
Subject: Re: Implementing Frontend/Backend Protocol TCP/IP
Date: Tue, 27 Oct 2009 18:10:43 +0800
Message-ID: <4AE6C723.105@postnewspapers.com.au> (raw)
In-Reply-To: <F9B13903-C8D7-4A92-825D-EB12FE646A63@montx.com>
References: <92781008-8A70-42CE-A49B-6EE67103FACB@montx.com>
<FC6CBC8C-A398-4835-B3A3-C16C6F4F62D9@pgedit.com>
<E084987E-A212-4B70-8CFF-D83AA00629C5@montx.com>
<C670629A-1E54-4F80-9497-6CCAC1A8BDC0@montx.com>
<20091026231516.GN8812@alvh.no-ip.org>
<4AE62E21.7040600@hogranch.com>
<F9B13903-C8D7-4A92-825D-EB12FE646A63@montx.com>
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
view thread (20+ messages) latest in thread
Message-ID: <4AE6C723.105@postnewspapers.com.au>
Permalink: ../4AE6C723.105@postnewspapers.com.au/
Also on: postgresql.org/message-id/4AE6C723.105@postnewspapers.com.au
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: craig@postnewspapers.com.au, coder@montx.com, pgsql-general@postgresql.org
Subject: Re: Implementing Frontend/Backend Protocol TCP/IP
In-Reply-To: <4AE6C723.105@postnewspapers.com.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox