Streaming Replication in PostgreSQL9.0 b4: Standby dropping connections - Mailing list pgsql-novice

From Chip Steele
Subject Streaming Replication in PostgreSQL9.0 b4: Standby dropping connections
Date
Msg-id AANLkTimtYNphFQ0pAHT5oZjCzn9eYG+Bfv2=75_t-Pkp@mail.gmail.com
Whole thread Raw
List pgsql-novice
Hi,

 --- Reposting with more information ---
  In PG9.0-b4 I have successfully set up a primary-secondary pair with streaming replication, with the secondary in hot_standby mode.
 
I have an application on the standby (and primary) which has constant connections open to the database. Occasionally it refreshes its cache of data from the hot_standby secondary server.
I am intermittently getting and error when updating the application's cache.

The cache update involves 2 queries:
SELECT COUNT(*)  FROM core_list; -- this table has about 5 Million records
and
SELECT line, COUNT(*) FROM core_state WHERE state='normal' GROUP BY line ORDER BY line;   -- this table has about 50million records

normal operation of the database would not update the core_list table, and would update maybe up to 10% of the core_state table between application cache updates

table definitions follow:
CREATE TABLE core_list(
   id         int              NOT NULL DEFAULT nextval('core_list_id_seq'),
   name   varchar(64)  NOT NULL,
   valid     BOOLEAN  NOT NULL DEFAULT '1'
);

unique index on the first 2 fields, index on the 3rd.

CREATE TABLE core_state(
   core_id   int            NOT NULL,
   line         smallint    NOT NULL,
   state       core_state NOT NULL
);

CREATE INDEX cs_core_id_idx ON core_state(core_id);
CREATE INDEX cs_state_line_idx ON core_state(state,line);
CREATE UNIQUE INDEX cs_core_id_line_idx ON core_state(core_id,line)

When I check the PostgreSQL logs with the same timestamp, I see the following messages:

FATAL: terminating connection due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
HINT: In a moment you should be able to reconnect to the database and repeat your command.

Now, this message explains that there is a problem, but I couldn't find anything in the streaming replication/hot standby documentation indicating that this sort of thing might occur.
Is there any way to avoid it? Any further information about this specific problem? Can I provide anything else?
 
Thanks in advance,

Chip

pgsql-novice by date:

Previous
From: Rikard Bosnjakovic
Date:
Subject: Re: Easiest way to extract owner-id from a third table
Next
From: Frank Bax
Date:
Subject: create a box from point?