set enable_save_datachanged_timestamp=on;
create temp table test_history(no int, act text, last_data_changed timestamptz);
insert into test_history values (0, 'init', NULL);
-- row table
drop table if exists trow1;
NOTICE: table "trow1" does not exist, skipping
create table trow1(c1 int, c2 int) distribute by hash(c1);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'create table trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
insert into trow1 values (0, 0),(1, 1);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'insert into trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
update trow1 set c2=5 where c2=1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'update trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
update trow1 set c2=6 where c2=1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'update trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
delete trow1 where c2=0;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'delete trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
delete trow1 where c2=100;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'delete trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
explain insert into trow1 values (3,3);
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain insert', last_data_changed from pg_stat_all_tables where relname='trow1';
explain update trow1 set c2=200 where c2=100;
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain update trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
explain delete trow1 where c2=300;
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain delete trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
explain analyze delete trow1 where c2=300;
--?.*
--?.*
--?.*
--?.*
--?.*
(3 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain analyze delete trow1', last_data_changed from pg_stat_all_tables where relname='trow1';
select count(*) from trow1;
count
-------
1
(1 row)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'select count', last_data_changed from pg_stat_all_tables where relname='trow1';
-- partition table
drop table if exists tpart;
NOTICE: table "tpart" does not exist, skipping
create table tpart(c1 int, c2 int) distribute by hash(c1)
partition by range(c2)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300),
partition p4 values less than(400),
partition p5 values less than(maxvalue)
);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'create table tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
insert into tpart values (0, 50),(0, 150);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'insert into tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
update tpart set c2=90 where c2=50;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'update tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
update tpart set c2=6 where c2=1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'update tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
delete tpart where c2=150;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'delete tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
delete tpart where c2=100;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'delete tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
explain insert into tpart values (0,300);
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain insert', last_data_changed from pg_stat_all_tables where relname='tpart';
explain update tpart set c2=200 where c2=100;
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain update', last_data_changed from pg_stat_all_tables where relname='tpart';
explain delete tpart where c2=300;
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain delete', last_data_changed from pg_stat_all_tables where relname='tpart';
explain analyze delete tpart where c2=300;
--?.*
--?.*
--?.*
--?.*
--?.*
(3 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain analyze delete tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
select * from tpart;
c1 | c2
----+----
0 | 90
(1 row)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'select * from tpart', last_data_changed from pg_stat_all_tables where relname='tpart';
alter table tpart exchange partition (p1) with table only trow1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'alter table tpart exchange partition', last_data_changed from pg_stat_all_tables where relname='tpart';
alter table tpart truncate partition p2;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'alter table tpart truncate partition', last_data_changed from pg_stat_all_tables where relname='tpart';
alter table tpart drop partition p2;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'alter table tpart drop partition', last_data_changed from pg_stat_all_tables where relname='tpart';
alter table tpart merge partitions p3,p4 into partition p34;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'alter table tpart merge partitions', last_data_changed from pg_stat_all_tables where relname='tpart';
-- column unlogged table
drop table if exists tlog;
NOTICE: table "tlog" does not exist, skipping
create unlogged table tlog(c1 int, c2 int) with(orientation=column) distribute by hash(c1);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'create unlogged table tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
insert into tlog values (0, 0),(1, 1);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'insert into tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
update tlog set c2=5 where c2=1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'update tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
update tlog set c2=6 where c2=1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'update tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
delete tlog where c2=0;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'delete tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
delete tlog where c2=100;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'delete tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
explain insert into tlog values (3,3);
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain insert into tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
explain update tlog set c2=200 where c2=100;
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain update tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
explain delete tlog where c2=300;
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain delete tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
explain analyze delete tlog where c2=300;
--?.*
--?.*
--?.*
--?.*
--?.*
(3 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'explain analyze delete tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
select * from tlog;
c1 | c2
----+----
1 | 5
(1 row)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'select * from tlog', last_data_changed from pg_stat_all_tables where relname='tlog';
-- temp table
drop table if exists ttemp;
NOTICE: table "ttemp" does not exist, skipping
create temp table ttemp(c1 int, c2 int);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'create temp table ttemp', last_data_changed from pg_stat_all_tables where relname='ttemp';
insert into ttemp values (1,1), (2,2);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'insert into ttemp', last_data_changed from pg_stat_all_tables where relname='ttemp';
-- copy from
--?.*
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'copy trow1 to', last_data_changed from pg_stat_all_tables where relname='trow1';
--?.*
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'copy trow1 from', last_data_changed from pg_stat_all_tables where relname='trow1';
--?.*
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'copy tlog to', last_data_changed from pg_stat_all_tables where relname='tlog';
--?.*
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'copy tlog from', last_data_changed from pg_stat_all_tables where relname='tlog';
--?.*
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'copy ttemp to', last_data_changed from pg_stat_all_tables where relname='ttemp';
--?.*
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'copy ttemp from', last_data_changed from pg_stat_all_tables where relname='ttemp';
-- select into table and create table as
select * into temp table t_into_temp from trow1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'select * into temp table t_into_temp', last_data_changed from pg_stat_all_tables where relname='t_into_temp';
drop table if exists t_into;
NOTICE: table "t_into" does not exist, skipping
select * into table t_into from trow1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'select * into table t_into', last_data_changed from pg_stat_all_tables where relname='t_into';
create temp table t_temp_cre as select * from trow1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'create temp table t_temp_cre as', last_data_changed from pg_stat_all_tables where relname='t_temp_cre';
drop table if exists t_cre;
NOTICE: table "t_cre" does not exist, skipping
create table t_cre as select * from trow1;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'create table t_cre as', last_data_changed from pg_stat_all_tables where relname='t_cre';
-- transaction
create table t_xact(c1 int, c2 int);
start transaction;
insert into t_xact select * from trow1;
commit;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'insert into t_xact - commit', last_data_changed from pg_stat_all_tables where relname='t_xact';
start transaction;
insert into t_xact select * from trow1;
rollback;
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'insert into t_xact - rollback', last_data_changed from pg_stat_all_tables where relname='t_xact';
-- drop partition on temp partition table
drop table if exists tpart_temp;
NOTICE: table "tpart_temp" does not exist, skipping
create unlogged table tpart_temp(c1 int, c2 int) distribute by hash(c1)
partition by range(c2)
(
partition p1 values less than(100),
partition p2 values less than(200),
partition p3 values less than(300),
partition p4 values less than(400),
partition p5 values less than(maxvalue)
);
ERROR: unsupported feature with temporary/unlogged table for partitioned table
-- enable_save_datachanged_timestamp=off
set enable_save_datachanged_timestamp=off;
create table t_off(c1 int, c2 int);
insert into t_off values (1,1);
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
insert into test_history
select (select max(no)+1 from test_history), 'insert into t_off', last_data_changed from pg_stat_all_tables where relname='t_off';
select * from test_history order by no;
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
--?.*
(53 rows)
-- set iud timestamp by func
drop schema if exists abc cascade;
NOTICE: schema "abc" does not exist, skipping
create schema abc;
drop table if exists t_set_fn;
NOTICE: table "t_set_fn" does not exist, skipping
drop table if exists abc.t_set_fn;
NOTICE: table "t_set_fn" does not exist, skipping
create table t_set_fn(c1 int, c2 int);
create table abc.t_set_fn(c1 int, c2 int);
select pg_stat_set_last_data_changed_time(C.oid) from pg_class C left join pg_namespace N on N.oid=C.relnamespace where C.relname='t_set_fn' and N.nspname='public';
pg_stat_set_last_data_changed_time
------------------------------------
(1 row)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
select last_data_changed from pg_stat_all_tables where relname='t_set_fn';
--?.*
--?.*
--?.*
--?.*
(2 rows)
select pg_stat_set_last_data_changed_time(C.oid) from pg_class C where C.relname='t_set_fn';
pg_stat_set_last_data_changed_time
------------------------------------
(2 rows)
select pg_sleep(0.1);
pg_sleep
----------
(1 row)
select last_data_changed from pg_stat_all_tables where relname='t_set_fn';
--?.*
--?.*
--?.*
--?.*
(2 rows)
-- merge into
drop table if exists iud_products;
NOTICE: table "iud_products" does not exist, skipping
drop table if exists iud_newproducts;
NOTICE: table "iud_newproducts" does not exist, skipping
create table iud_products(product_id int, product_name varchar2(60), category varchar2(60));
create table iud_newproducts as select * from iud_products;
insert into iud_products values (1601, 'lamaze', 'toys');
insert into iud_newproducts values (1601, 'lamaze', 'toys'), (1700, 'wait interface', 'books');
merge into iud_products p
using iud_newproducts np on (p.product_id = np.product_id)
when matched then
update set p.product_name = np.product_name, p.category = np.category where p.product_name != 'play gym'
when not matched then
insert values (np.product_id, np.product_name, np.category) where np.category = 'books';
select pg_sleep(0.3);
pg_sleep
----------
--?.*
(1 row)
select last_data_changed from pg_stat_all_tables where relname='iud_products';
last_data_changed
-------------------
--?.*
(1 row)