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
>
>
>
