Whats New In PostgreSQL 14 => Demos Only

postgres14demos.sql

\q
/*
What's New In PostgreSQL 14: All Demo Edition
Robert Treat (@robtreat2) - v5 - 2022-08-21

Requirements:
Postgres 14
Pagila Sample Database (https://github.com/xzilla/pagila)

## Table of Contents ##

Internal Query Id
pg_locks wait time
pg_stat_database enhancements
New Internal System Views

Use OUT params with procedures
SQL Standard syntax for sql funcs/procs

detach partition concurrently

OR REPLACE trigger
allow multi-row DEFAULT inserts on generated columns

bit_count function
bit_xor function

split_part
trim_array

JSON Subscripting

JOIN USING AS

check for lost connection during query
idle_session_timeout
checkpoint_completion_target
enable_memoize is not a hint

pause replay

vaccuum and vacuum related accessories

psql tricks

pg_surgery

pg_stat_statements

## END TOC
*/


/* Internal Query Id */

set compute_query_id = on;
select query_id, query from pg_stat_activity;
explain (verbose) select query_id, query from pg_stat_activity;



/* pg_locks wait time */

\d pg_locks



/* pg_stat_database enhancements */

select datname, numbackends, session_time, active_time, idle_in_transaction_time, sessions, sessions_abandoned, sessions_fatal, sessions_killed from pg_stat_database where datname='pagila';



/* New Internal System Views */

SELECT * FROM pg_backend_memory_contexts ORDER BY used_bytes DESC LIMIT 5;
select * from pg_stat_wal;
\d pg_stat_progress_copy
\d pg_stat_replication_slots



/* Use OUT params with procedures */
CREATE PROCEDURE film_at_store(p_film_id int, p_store_id int, OUT p_total_inventory int, OUT p_currently_rented int)
AS $$
BEGIN
  p_total_inventory := count(*) from inventory where film_id = p_film_id and store_id = p_store_id;
  p_currently_rented := count(*) filter (where return_date is null) from inventory left join rental using (inventory_id)
    where film_id = p_film_id and store_id = p_store_id;
END $$
LANGUAGE plpgsql;

CALL film_at_store(420,1,NULL,NULL);



/* SQL Standard syntax for sql funcs/procs */
CREATE FUNCTION public.film_in_system(p_film_id integer, OUT p_film_count integer)
LANGUAGE sql
BEGIN ATOMIC
  SELECT count(*)
    FROM inventory
   WHERE film_id = $1
     AND inventory_in_stock(inventory_id);
END;

select film_in_system(420);

select proname, prosrc::char(75), prosqlbody::char(75) from pg_proc where proname ~* 'film_in_';

begin; drop table inventory ; rollback;


/* detach partition concurrently */
\d+ payment
alter table payment detach partition payment_p0000_default;
alter table payment detach partition payment_p2007_04 concurrently;



/* OR REPLACE trigger */
\d actor
CREATE OR REPLACE TRIGGER last_updated BEFORE INSERT or UPDATE ON public.actor FOR EACH ROW EXECUTE FUNCTION public.last_updated();
\d actor 


/* allow multi-row DEFAULT inserts on generated columns */
insert into film (title, language_id, revenue_projection) values ('CLUB FIGHT', 1, DEFAULT), ('FICTIONAL PLUP', 1, DEFAULT);
SELECT 'v13 => ERROR:  cannot insert into column "revenue_projection" ';



/* bit_count */
select bit_count(picture) from staff;



/* bit_xor */
select bit_xor(staff_id) from staff;



/* date_bin */
select date_bin('4 hours'::interval, rental_date::timestamptz, rental_date::date), count(*) from rental group by 1 order by 1 limit 10;



/* split_part */
select distinct(split_part(email,'.',-1)) from staff;



/* trim_array */
select special_features, trim_array(special_features, 2) from film where array_length(special_features,1) = 3 limit 10;



/* JSON Subscripting */
select * from rental_report offset 21 limit 12;
select report['rental_date'] from rental_report offset 21 limit 12;
select report['rentals'][0] from rental_report offset 21 limit 12;



/* JOIN USING AS */
with geo as (select * from country join city using (country_id) ) select * from customer_list join geo using (city, country) as customer_geo;
with geo as (select * from country join city using (country_id) ) select customer_geo from customer_list join geo using (city, country) as customer_geo;
with geo as (select * from country join city using (country_id) ) select customer_geo.city, customer_geo.country, * from customer_list join geo using (city, country) as customer_geo;



/* check for lost connection during query */
select setting, unit, short_desc, context, source, boot_val from pg_settings where name = 'client_connection_check_interval';
set client_connection_check_interval=2112;



/* idle_session_timeout */
select setting, unit, short_desc, context, source, boot_val from pg_settings where name = 'idle_session_timeout';

\set VERBOSITY verbose
SET idle_session_timeout = 2112;
SELECT now();



/* checkpoint_completion_target */
select setting, unit, short_desc, context, source, boot_val from pg_settings where name = 'checkpoint_completion_target';



/* enable_memoize is not a hint */

create table duck as select x%10 as fight from generate_series(1,100000) as d(x);
create table horse as select x%10000 as fight from generate_series(1,100000) as d(x);
create index on horse(fight);
set enable_memoize = off;
explain select * from duck join horse using (fight);
set enable_memoize = default;
explain select * from duck join horse using (fight);



/* pause replay */
SELECT pg_wal_replay_pause() ;



/* vaccuum and vacuum related accessories */
VACUUM (PROCESS_TOAST FALSE, VERBOSE) film;

select name, setting, short_desc, min_val, max_val from pg_settings where name in ('vacuum_failsafe_age','vacuum_multixact_failsafe_age');



/* psql tricks */

\df sum
\df sum (smallint)

\dT int

create statistics whatwhere on city_id, postal_code from address;
analyze address;
\dX

\di+ film*



/* pg_surgery */

create extension if not exists pg_surgery;

select xmin, ctid, actor_id from actor limit 1;
select heap_force_freeze('actor'::regclass, array['(0,1)']::tid[]);
select xmin, ctid, actor_id from actor limit 1;
select * from actor where actor_id = 1;

select heap_force_kill('actor'::regclass, array['(0,1)']::tid[]);
select xmin, ctid, actor_id from actor limit 1;
select * from actor where actor_id = 1;



/* pg_stat_statements */
create extension if not exists pg_stat_statements;
show shared_preload_libraries;

refresh materialized view nicer_but_slower_film_list;

select queryid, rows, query::char(50) from pg_stat_statements;

select query_id as "yes really query_id not queryid", query from pg_stat_activity where pid = pg_backend_pid();

select name, setting, short_desc, enumvals from pg_settings where name = 'compute_query_id';

\d pg_stat_statements_info



-- FIN
/*
PostgreSQL License
Copyright (c) 2021,2022 Robert Treat
Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE AUTHOR BE LIABLE TO ANY PARTY FOR DIRECT, INDIRECT,
SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING LOST PROFITS, ARISING
OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION, EVEN IF THE AUTHOR HAS
BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.

THE AUTHOR SPECIFICALLY DISCLAIMS ANY WARRANTIES, INCLUDING, BUT NOT LIMITED TO,
THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.
THE SOFTWARE PROVIDED HEREUNDER IS ON AN "AS IS" BASIS, AND THE AUTHOR HAS NO
OBLIGATIONS TO PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR
MODIFICATIONS.
*/