[Fwd: Re: EXECUTE problems] - Mailing list pgsql-general

I presented this question to Neil on a one-to-one email...

Perhaps I should ask present this to all....

Thanks
Hi Neil.....

Actually I can use your help on the Async Query....
I have read the URL you provided but the problem with that page is that
it presents
a collection of services (function) with no or spratic inter-relation or
flow...
Its sort of like knowing about bind(2), listen(2), accept(2) without
knowing the order....

I have proposed this Async Query solution in a multi-threading
environment as an
alternative solution to having a separate thread do some purging and
vacuuming....
It has been crashing the database a few times and management is getting
antsy...

I'm using three sustained connection to my DB;

One for normal traffic including 20 inserts / sec into an index-less
logtable.
One for purging (or clipping) the above logtable using Async Query
One for vacuuming using Async Query

Here is how I use my Async path

- DB_Connect(conn)     // validate my connectiion, give me a new one if
the old one is dead
- PQconsumeInput( conn )  // just write some message out if any error
- PQisBusy( conn )  // if busy, just return, else go get the result
- while ( PQgetResult() )    // get the result, since its purge I only
expect xxx rows effected
- PQclear( res) //   make sure I don't cause any mem leak
- PQsendQuery( query ) // now send the next async purge query
- return

Application maintains a recordCount and when it hits a hi-water-mark, it
calls the
AsyncPurge() function that was presented above. Typically I hit this
point every 10 minutes.
Also application will call AsyncVaccuum() when it hits mid-water-mark.
I call this async and interleaved.....

My Questions are:

- Do you see anything wrong with this design
- If I loose my connection and get a new one, and then subsequently
check to see
  if PQisBusy(new conn) would it be able to tell or not.....
  I guess what I am asking is, when a async query is send down a pipe
(connection),
  does the backend associates this query with the connection. What
happens to the
  query and the result if the connection is droped.....one scenario is
  conn-1 sends a query
  conn-1 disconnects or gets droped
  conn-2 checks for data from backend via consumeInput and isBusy()
finds it empty
  will attempt to send another similar async query.....how would the
backend deal with
  this....

Thanks

Neil Conway wrote:

>Medi Montaseri <medi.montaseri@intransa.com> writes:
>
>
>>Before I head out to read those links....do I have to re-prepare if
>>I loose my connection.  Or could/would the backend use it regardless
>>of what connection I come in....
>>
>>
>
>No, prepared statements are currently stored locally, per-backend --
>that means that a prepared statement can only be EXECUTE'd by the same
>client that PREPARE'd it. We looked into storing prepared queries in
>shared memory (in fact, the first implementations of the idea did just
>that), but it turned out not to be worth the hassle.
>
>
>
>>Also I need some help using Async query technique....anyone used who
>>I can get some help from....I need to confirm my understanding of
>>how to use the Async Query technique
>>
>>
>
>Do you have a specific question that isn't addressed by the
>documentation?
>
>    http://developer.postgresql.org/docs/postgres/libpq-async.html
>
>Cheers,
>
>Neil
>
>
>