Peter Geoghegan wrote: > Hello, > > I'm writing a C++ application that stores data in a table that may > ultimately become very large (think tens of millions of rows). It has > an index on one row, in addition to the index created on/as part of > its primary key. My concern is that a call to the pl/pgSQL function > that INSERTs data into this table might eventually lock the UI for an > annoyingly long time, as control flow in my application waits for that > Pl/PgSQL function to return. Database latencies aren't generally neatly bounded; you're always going to get the odd long job. PostgreSQL isn't a realtime system after all, and it's oriented toward throughput over latency. This means that your app really needs to be able to be responsive while queries are in flight. If nothing else, the user might want to cancel something or might know something you don't (say, they just unplugged the network cable). Unless libpq can be convinced to use asynchronous I/O and avoid blocking on reads that probably means threading. I've been doing lots with Pg and Java lately and I've found it very pleasant how relatively easy it is to produce responsive code using a background worker thread to access the DB. I have a nasty little script that runs in the background, sending a SIGSTOP to randomly chosen backends then a SIGCONT a few milliseconds, seconds, or sometimes minutes later. I chuck the odd SIGTERM in to liven up the mix and test error handling. I can strongly recommend testing this way as you go about your normal development if you can afford it ... it's extremely helpful for catching issues with responsiveness, error handling, or timing issues. I don't imagine it's much fun to take the same sort of GUI thread + background worker approach in C++ . I have lots more experience with C++ than Java, but fortunately for me very little with significantly multi-threaded C++ and no multi-threaded C++ database code, so I can't offer any useful advice on how to tackle it. All the C++ threading I've done has been via TrollTech's Qt library, anyway, which is basically cheating. Given the choice I'd want to take the async I/O option over threading in C++, but I don't get the impression that libpq is really built around that model. I'm assuming you're using libpq, of course, not an ODBC interface or something else. > I'm aware that I could create a second thread to make the call to my > database API, libpqxx, but I have reservations due to the possible > implications for thread safety - pqxx lacks "a flexible mechanism for > thread synchronization", so this might cause headaches. It just means that you should entirely encapsulate all libpq access within a module that, except for job submit/notification messaging, runs entirely on one thread. If there are no points where other code calls into libpq and the libpq-using worker sticks to one thread you should be just fine. Getting the completion notification, cancellation, error reporting, etc etc etc right in a concurrent environment could be fun though. I'd want to stick to a job queue as much as possible, personally. -- Craig Ringer
