test_wal_reduction_update_sql.txt

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