On August 27, 2012 7:00 PM Amit Kapila wrote:
On August 27, 2012 5:58 PM Heikki Linnakangas wrote:
On 27.08.2012 15:18, Amit kapila wrote:
>>> I have implemented the WAL Reduction Patch for the case of HOT Update as
>> Let's do it for HOT updates only. Simon & Robert made good arguments on
>> why this is a bad idea for non-HOT updates.
> Okay, I shall do it that way.
> So now I shall send information about all the testing I have done for this
> Patch and then Upload it in CF.
Test Scenario's are below and testcases for same are attached with this mail.
Scenario1:
Recover the data where the field data is updated with different value from an
exisitng data of an integer field.
Steps:
1. Start the server, create table, insert one record into the table.
2. update the integer field with other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario2:
Recover the data where the field data is updated with different value from an
exisitng data of char and varchar fields.
Steps:
1. Start the server, create table, insert one record into the table.
2. update both char and varchar fields with other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario3:
Recover the data where the field data is updated with NULL value from an
exisitng data of a field.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with NULL value.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario4:
Recover the data where the field data is updated with a proper value from an
exisitng data of a field where the row contains NULL data.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with a different value other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario5:
Recover the data where all fields data is updated with NULL value from an
exisitng data of a fields.
Steps:
1. Start the server, create table, insert one record into the table.
2. update all fields with NULL values.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario6:
Recover the data of updated field of a table where the table contains a toast
table.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with a different value other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario7:
Recover the data of updated field of a table where the row length is less than
128 bytes.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field with a different value other than existing data.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario8:
Recover the data of updated field of a table where the before trigger modifies
the tuple before the tuple updates.
Steps:
1. Start the server, create table, insert one record into the table.
2. create a before trigger which modifies the same record.
3. update a field with a different value other than existing data.
4. Shutdown the server immediately.
5. Start the server and connect the client and check the data in the table.
Expected behavior:
The updated data should present in the table after database recovery.
Scenario9:
Recover the data where the update operation fails because of trigger returns
NULL.
Steps:
1. Start the server, create table, insert one record into the table.
2. update a field fails as before trigger returns NULL.
3. Shutdown the server immediately.
4. Start the server and connect the client and check the data in the table.
Expected behavior:
The update command shouldn't be effective after recovery also.
With Regards,
Amit Kapila.
-- Test case 1
drop table if exists tbl;
create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200));
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f1 = 10;
-- restart the server
select * from tbl;
drop table tbl;
-- Test case 2
drop table if exists tbl;
create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200));
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f2 = 'haribabu', f4 = 'test123';
-- restart the server
select * from tbl;
drop table tbl;
-- Test case 3
drop table if exists tbl;
create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200));
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f3 = NULL;
-- restart the server
select * from tbl;
drop table tbl;
-- Test case 4
drop table if exists tbl;
create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200));
insert into tbl values(1,'hari',NULL,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f4 = 'test123';
-- restart the server
select * from tbl;
drop table tbl;
-- Test case 5
drop table if exists tbl;
create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200));
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f1 = NULL, f2 = NULL, f3 = NULL, f4 = NULL;
-- restart the server
select * from tbl;
drop table tbl;
-- Test case 6
drop table if exists tbl;
create table tbl(f1 int, f2 varchar(1000), f3 float8, f4 char(2000));
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f1 = 10;
-- restart the server
select * from tbl;
drop table tbl;
-- Test case 7
drop table if exists tbl;
create table tbl(f1 int, f2 varchar(10), f3 float8, f4 char(20));
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f1 = 10;
-- restart the server
select * from tbl;
drop table tbl;
-- Test case 8
drop table if exists tbl;
drop table if exists tbl1;
create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200));
create table tbl1(f1 int);
create or replace function trig_func() returns trigger as $$
declare
psql varchar(100);
begin
psql := 'insert into tbl1 values(10);';
execute psql;
return old;
end;
$$ language plpgsql;
create trigger trigger_test before update on tbl for each row
execute procedure trig_func();
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f1 = 10;
-- restart the server
select * from tbl;
select * from tbl1;
drop table tbl;
drop table tbl1;
-- Test case 9
drop table if exists tbl;
drop table if exists tbl1;
create table tbl(f1 int, f2 varchar(100), f3 float8, f4 char(200));
create table tbl1(f1 int);
create or replace function trig_func() returns trigger as $$
declare
psql varchar(100);
begin
psql := 'insert into tbl1 values(10);';
execute psql;
return NULL;
end;
$$ language plpgsql;
create trigger trigger_test before update on tbl for each row
execute procedure trig_func();
insert into tbl values(1,'hari',2.1,'test');
checkpoint;
-- first update is as it creates a backup block.
update tbl set f1 = f1;
update tbl set f1 = 10;
-- restart the server
select * from tbl;
select * from tbl1;
drop table tbl;
drop table tbl1;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
