Re: Question on inserting and querying at the same time.

Richard Huxton wrote:
> Wei Weng wrote:
>> I have a database table that has about 90k entries, they are all
>> straightfoward text, and there is only one ID field that I use as
>> primary key for this table.
>>
>> I have two threads working on this table. One of them inserting new
>> content constantly, (about every second) another one idles and only
>> wakes up when I want to query(select) the table.
>>
>> My problem is that from the log it seems to me that since the insertion
>> is done far frequently than query, the query thread never finishes its
>> job. It just hangs there for a long time until I kill it by restarting
>> postgresql. (Even statements like EXPLAIN ANALYZE will hang)
>
> Define "threads". If you mean a threaded client application, are you
> sure your libpq was compiled with threading enabled?
>
> Define "hangs" please. What do the logs show?
>
Along with what Richard asked, what kind of query are you running?
INSERT commands should never prevent you from doing a basic SELECT on
the table even if they're running concurrently as INSERTs take out ROW
EXCLUSIVE locks for the rows that they are inserting, while SELECT just
takes out an ACCESS SHARE lock on the table, and the two do not
conflict. So, if by threaded you mean two different processes accessing
the same table I'm guessing that your queries are not 'simple' and you
should share them if you want help there, and if by threaded you are
referring to an actual threaded application, double check how libpq was
compiled and double check you in application threading logic.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)