\c template1 drop database postgres; create database postgres; \c postgres -- update the partition key select version(); create table part ( a int, list text ) partition by list (list); create table part_1 partition of part for values in ('beer'); create table part_2 partition of part for values in ('wine'); \d+ part insert into part (a,list) select i,'beer' from generate_series(1,10) i; insert into part (a,list) select i,'wine' from generate_series(11,20) i; select * from part; update part set list = 'beer' where a = 15; -- creating an index on a partitioned table create index i_test on part (a); alter table part add constraint part_pk primary key(a,list); -- create index i_test_1 on part_1 (a); -- create index i_test_2 on part_2 (a); -- alter table part_1 add constraint part1_pk primary key(a,list); -- alter table part_2 add constraint part2_pk primary key(a,list); -- insert on conflict insert into part(a,list) values (1,'beer') on conflict (a,list) do nothing; -- partition pruning explain select * from part where list = 'beer'; explain select * from part where list = ( select 'beer' ); explain (analyze) select * from part where list = ( select 'beer' ); show enable_partition_pruning; -- default partitions create table part_3 partition of part default; insert into part values (3,'grog'); select * from only part_3; -- hash partitioning create table part2 ( a int, list varchar(10) ) partition by hash (a); create table part2_1 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 0); create table part2_2 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 1); create table part2_3 partition of part2 FOR VALUES WITH (MODULUS 3, REMAINDER 2); \d+ part2 insert into part2 (a,list) values (1,'beer'); insert into part2 (a,list) values (2,'whine'); insert into part2 (a,list) values (3,'schnaps'); select * from only part2_1; select * from only part2_2; select * from only part2_3; -- new sha functions \df *sha* select sha224(list::bytea) from part; select sha512(list::bytea) from part; -- covering unique indexes create table tt (a int, b text, c date ); insert into tt (a,b,c) select aa.*, md5(aa::text), now() from generate_series(1,1000000) aa; create unique index u1 on tt(a); create unique index i1 on tt(a) include(b); \d+ tt -- procedures and transaction control CREATE FUNCTION dummy_func (id int) RETURNS VOID AS $$ DECLARE BEGIN RAISE NOTICE 'id is %', id; END; $$ LANGUAGE plpgsql; CREATE PROCEDURE dummy_proc (id int) AS $$ DECLARE BEGIN raise notice 'id is %', id; END; $$ LANGUAGE plpgsql; select dummy_proc(1); call dummy_proc(1); create table t1 ( a int primary key ); CREATE OR REPLACE PROCEDURE dummy_proc2 (id int) AS $$ DECLARE l_id int := id; BEGIN raise notice 'id is %', l_id; insert into t1 (a) values (l_id); commit; l_id := l_id + 1; raise notice 'id is %', l_id; insert into t1 (a) values (l_id); commit; raise notice 'id is %', l_id; insert into t1 (a) values (l_id); END; $$ LANGUAGE plpgsql; call dummy_proc2(1); select * from t1; -- adding a column with a default value to a table create table ttt (a int, b text, c date ); insert into ttt (a,b,c) select aa.*, md5(aa::text), now() from generate_series(1,1000000) aa; \timing on alter table ttt add column d text default 'a'; \d pg_attribute select attmissingval,atthasmissing from pg_attribute where attrelid = 'ttt'::regclass and attname = 'd'; -- psql variables to track sqlstate, error and rowcount select count(*) from ttt; \echo :ROW_COUNT \echo :SQLSTATE select count(*.) from ttt; \echo :ERROR \echo :SQLSTATE \! grep 42601 $PGHOME/share/errcodes.txt \! vi $PGHOME/share/errcodes.txt -- parallel btree index builds create table iptest (a text, b text, c text); insert into iptest(a,b,c) select md5(a::text),md5(a::text),md5(a::text) from generate_series(1,5000000) a ; -- two sessions select query,state,backend_type from pg_stat_activity; \watch -- other session create index ip on iptest (a,b,c); show max_parallel_maintenance_workes; -- parallel hash explain select max(t1.a) from iptest t1 , iptest t2 where t1.a = t2.a; -- JIT compilation select name,setting from pg_settings where name like 'jit%'; set jit_above_cost=5; set jit_optimize_above_cost=5; set jit_inline_above_cost=5; explain select sum(a) from ttt; -- changeable WAL SEGMENT size postgres@pgbox:# ./configure --prefix=${PGHOME} \ --with-wal-segsize=${WALSEGSIZE} initdb --help -- multiple tables for vacuum and analyze vacuum part; vacuum ttt; vacuum ttt,part; analyze ttt,part; -- new environment variable for specifiying the PAGER psql will use echo $PAGER psql -c "select * from pg_attribute" postgres export PSQL_PAGER=cat psql -c "select * from pg_attribute" postgres create extension pg_prewarm; alter system set shared_preload_libraries='pg_prewarm'; create extension pg_buffercache; \! pg_ctl -D $PGDATA restart -m fast \! ps -ef | grep warm select * from ttt limit 30; SELECT pg_relation_filepath('ttt'); select count(*) from pg_buffercache where relfilenode = 16680; \! pg_ctl -D $PGDATA restart -m fast select count(*) from pg_buffercache where relfilenode = 16680; select 'database,tablespace,table,fork,block'; \! cat $PGDATA/autoprewarm.blocks | more -- locking a table which is used in a view locks the view in PostgreSQL 11 create table tt1 ( a int ); create view vv1 as select * from tt1; begin; lock view tt1; -- second session select count(*) from vv1;