[HACKERS] Hot Standby: First integrated patch

[Simon Riggs]

First integrated patch for Hot Standby, allowing queries to be executed
while in recovery mode.

The patch tests successfully with the enclosed files:
* primary_setup_test.sql - run it on primary node
* standby_allowed.sql - run on standby - should all succeed
* standby_disallowed.sql - run on standby - should all fail
plus other manual testing.

This is still WIP - its good enough to release for comments, though I am
not yet confident enough to claim it bug free. 

What this doesn't do YET:
* cope fully with subxid cache overflows (some parts still to add)
* cope with prepared transactions on master
* work correctly when running queries AND replaying WAL
* work correctly with regard to AccessExclusiveLocks, which should
prevent access to tables

These last four points are what I'm working on over the next two weeks,
plus any other holes people point out along the way. I have worked out
designs for most of these aspects and will discuss them on -hackers,
though most design notes are in the Wiki. I'm still looking into
prepared transactions.

Comments appreciated.

-- 
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support

-- 
-- Hot Standby tests
--
-- primary_test_setup.sql
--
--

drop table if exists hs1;
create table hs1 (col1 integer primary key, col2 text);

drop table if exists hs2;
create table hs2 (col1 integer primary key, col2 text);

drop table if exists hs3;
create table hs3 (col1 integer primary key, col2 text);

insert into hs1 values (1, 'table1');

insert into hs2
select generate_series(1,1000), 'table2';

insert into hs3
select generate_series(1,100000), 'table3';

create sequence hsseq;

select pg_switch_xlog();

-- 
-- Hot Standby tests
--
-- standby_allowed.sql
--

-- SELECT

select count(*) from hs1;

select count(*) from hs2;

select count(*) from hs3;

-- Transactions

begin;
select count(*) from hs1;
end;

begin transaction read only;
select count(*) from hs1;
end;

begin transaction isolation level serializable;
select count(*) from hs1;
select count(*) from hs1;
select count(*) from hs1;
commit;

begin;
select count(*) from hs1;
commit;

begin;
select count(*) from hs1;
abort;

start transaction;
select count(*) from hs1;
commit;

begin;
select count(*) from hs1;
rollback;

begin;
select count(*) from hs1;
savepoint s;
select count(*) from hs2;
commit;

begin;
select count(*) from hs1;
savepoint s;
select count(*) from hs2;
release savepoint s;
select count(*) from hs2;
savepoint s;
select count(*) from hs2;
rollback to savepoint s;
select count(*) from hs2;
commit;

-- SET parameters

-- has no effect on read only transactions, but we can still set it
set synchronous_commit = on;
show synchronous_commit;
reset synchronous_commit;

discard temp;

discard all;

-- CURSOR commands

BEGIN;

DECLARE hsc CURSOR FOR select * from hs3;

FETCH next from hsc;

fetch first from hsc;

fetch last from hsc;

fetch 1 from hsc;

CLOSE hsc;

COMMIT;

-- Prepared plans

PREPARE hsp AS select count(*) from hs1;

EXECUTE hsp;

DEALLOCATE hsp;

-- LOCK

LOCK hs1;

-- LOAD
-- should work, easier if there is no test for that...

discard all;

-- 
-- Hot Standby tests
--
-- standby_disallowed.sql
--

SET default_transaction_read_only = on;

begin transaction read write;
commit;

-- SELECT

select * from hs1 FOR SHARE;

select * from hs1 FOR UPDATE;

-- DML

insert into hs1 values (37, 'a');
delete from hs1 where col1 = 1;
update hs1 set col2 = NULL where col1 > 0;
truncate hs3;

-- DDL

create temporary table hstemp1 (col1 integer);
drop table hs2;

-- Sequences

SELECT nextval('hsseq');

-- Two-phase commit transaction stuff

BEGIN;
SELECT count(*) FROM hs1;
PREPARE TRANSACTION 'foobar';
COMMIT PREPARED 'foobar';

BEGIN;
SELECT count(*) FROM hs1;
PREPARE TRANSACTION 'foobar';
ROLLBACK PREPARED 'foobar';

ROLLBACK;

-- Listen
listen a;

notify a;

unlisten a;

unlisten *;

-- disallowed commands

ANALYZE hs1;

VACUUM hs2;

CLUSTER hs2 using hs1_pkey;

REINDEX TABLE hs2;

CHECKPOINT;

GRANT SELECT ON hs1 TO PUBLIC;

REVOKE SELECT ON hs1 FROM PUBLIC;

hot_standby.v1.patch.bz2
Description: application/bzip

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers