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.
I suspect that maintaining a large index could eventually cause this
to happen, particularly as the hardware that this embedded application
will run on is typically modest. Can someone suggest a solution? Are
my concerns about this happening well founded? At the moment, the
application doesn't lock at all, but it only has a few thousand rows.
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.
Thanks,
Peter Geoghegan
