SELECT Query on DB table preventing inserts

From: Dan Scott <danieljamesscott(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: SELECT Query on DB table preventing inserts
Date: 2011-08-30 14:22:43
Message-ID: CANQKhhkNEaRxhrbOshDYQ2yKz1T=j5LH=VUJvGkjrkiYmtQojA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi,

I have a server running PostgreSQL 8.4 (Scientific Linux release 6.0).
I'm running a process which receives messages from a remote server and
logs them into a table. Here is the table structure:

CREATE TABLE messages.message_log
(
message_id text,
message_timestamp timestamp with time zone,
message_type text,
message text,
message_pkid bigserial NOT NULL,
CONSTRAINT hld_log_pk PRIMARY KEY (message_pkid)
)

I'm receiving a few messages per second. I have a problem when I query
the table and try and parse the message (a complex, read-only,
function which takes quite a long time to process) the insert process
is unable to insert new rows into the database and I end up losing
some messages. Perhaps because I'm locking the table with my query?
But I wouldn't have thought that a read-only query would lock the
table for insert operations.

The insert process is run as the owner of the DB. The query is run as
me (and other users who have select permission on the table). I'd like
to be able to change the priority of the inserts (or my user's query),
or adjust the table locks so that the inserts occur in preference to
the parsing query.

I'd like to ensure that the DB stops whatever else it's doing so that
the insert can occur. What is the best way of doing this?

Thanks,

Dan Scott
http://danieljamesscott.org

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Don 2011-08-30 14:28:28 out of memory - no sort
Previous Message shuaixf 2011-08-30 13:14:07 Re: Whether the function exists a in pgsql table or not?