--- 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?
