Whats New In PostgreSQL 12 => Demos Only

postgres12demos.sql

\q
/*

What's New In PostgreSQL 12: All Demo Edition

Robert Treat (@robtreat2) - v5 - 2020-05-23


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

## Table of Contents ##

 Automagically Handle Extra Spaces in TO_DATE 
 WITH OIDS removed 
 Foreign Keys now named with all columns 
 Increase Default Set of FTS Languages 
 Friendlier Config Sizing 
 Create or Replace Aggregate now supported
 Add entries to ENUM type 
 CHAINED COMMITS
 Create Statistics for Multiple Columns 
 GENERATED COLUMN
 REINDEX CONCURRENTLY
 New Per Table Vacuum Options 
 New System Views 
 Strict Modes for Plpgsql 
 COMMON MATERIALIZED EXPRESSIONS 
 EXPLAIN (settings on) 
 forced plan caching 
 Pluggable Storage
 Log Transaction Sample 
 COPY With Where Clause 
 SQL Callable Standby Promotion 
 Better Default Recovery Target 
 psql Tricks
 Partition Introspection 
 JSON Path 

## ENDTOC
*/



/* Automagically Handle Extra Spaces in TO_DATE */
SELECT TO_DATE('2019/10/03', ' YYYY/ MM/ DD ') ;




/* WITH OIDS removed */
create table withoids (x int) WITH OIDS;  
 
\d pg_class 
-- Note oids have always been there.
SELECT oid, relname FROM pg_class LIMIT 5;




/* New System Views */
\d pg_stat_progress_cluster
\d pg_stat_progress_create_index




/* Foreign Keys now named with all columns */
create table fknaming (a smallint, f smallint, foreign key (a,f) references film_actor(actor_id, film_id));

\d fknaming 




/*  Increase Default Set of FTS Languages */
\dF 




/* Friendlier Config Sizing */ 
show maintenance_work_mem ;

set maintenance_work_mem = '21.12MB';

show maintenance_work_mem ;




/* Create or Replace Aggregate now supported 
* Also, psql now shows help urls
*/ 
\h create aggregate 




/* REINDEX CONCURRENTLY */
\h reindex

reindex (verbose) table concurrently actor;




/* CHAINED COMMITS
* You can now add 'AND CHAIN' clause to txn commands
* This will preserve txn specific settings across commit or rollback 
* This is also available in stored procedures
*/ 
SHOW transaction_isolation;


BEGIN ISOLATION LEVEL SERIALIZABLE;
SHOW transaction_isolation;


INSERT INTO actor(first_name, last_name) 
    VALUES ('DYLAN','LEE');


COMMIT AND CHAIN; 
INSERT INTO actor(first_name, last_name) 
    VALUES ('ROBERT','LEE');  


ROLLBACK AND CHAIN;
INSERT INTO actor(first_name, last_name) 
    VALUES ('EMMA','LEE');

  
COMMIT AND CHAIN ;
SHOW transaction_isolation ;

select * from actor where last_name = 'LEE';

COMMIT;
SHOW transaction_isolation ;




/* Create Statistics for Multiple Columns */
SELECT * FROM city, LATERAL (SELECT country FROM country WHERE country_id=city.country_id) country WHERE city LIKE 'L%n';

CREATE STATISTICS geopoints(mcv) 
    ON city,country_id FROM city;

SET default_statistics_target = 1000;
ANALYZE city; 

SELECT s.* FROM pg_statistic_ext_data, pg_mcv_list_items(stxdmcv) s WHERE array_to_string(values,':') ~* 'Lon';




/* GENERATED COLUMN 

# Must be based on computed values
# Cannot refence SQL queries
# Cannot reference other Generated Columns 

*/
alter table film add column revenue_potential numeric(5,2) generated always as (rental_duration * rental_rate) stored;

select title, rental_duration, rental_rate, revenue_potential from film limit 5;
 
insert into film (title,language_id,rental_duration,rental_rate,revenue_potential) values ('The Rise Of SQLWalker',1,21,12.00,1000000);


SELECT * FROM film WHERE film_id = 1;
UPDATE film SET rental_duration = 5 WHERE film_id = 1;
SELECT * FROM film WHERE film_id = 1;


SELECT attname, attgenerated 
    FROM pg_attribute WHERE attrelid='film'::regclass;

SELECT * FROM information_schema.column_column_usage ;




/* New Per Table Vacuum Options */
alter table rental set 
    (vacuum_index_cleanup = off, vacuum_truncate = off );

\d+ rental 

vacuum (verbose on) rental;

vacuum (verbose on, index_cleanup on) rental;




/* Strict Modes for Plpgsql */

DO $$
    DECLARE x INTEGER ;
    DECLARE y INTEGER ;
    DECLARE z INTEGER ;
BEGIN
    SELECT 1 INTO x,y; 
    RAISE NOTICE 'test #1 complete';

    SELECT 1,2,3 INTO x,y; 
    RAISE NOTICE 'test #2 complete';

    SELECT generate_series(1,2) INTO z ;
    RAISE NOTICE 'test #3 complete' ;
END ;
 $$ ;


SET plpgsql.extra_warnings = 'strict_multi_assignment';
SET plpgsql.extra_errors to 'too_many_rows' ;




/* COMMON MATERIALIZED EXPRESSIONS */
EXPLAIN WITH a AS MATERIALIZED (SELECT * FROM actor) SELECT * FROM a WHERE first_name = 'ROBERT';

EXPLAIN WITH a AS (SELECT * FROM actor) SELECT * FROM a WHERE first_name = 'ROBERT';





/* EXPLAIN (settings on) */
show random_page_cost ;

set random_page_cost = 1;
explain (settings on) select * from actor;




/* forced plan caching */
show plan_cache_mode;

create index idx_example_p2007_03_amount on payment_p2007_03(amount);

prepare ex(numeric(5,2)) as select * from payment_p2007_03 where amount = $1;

explain (settings) execute ex('4.99'::numeric(5,2));
explain (settings) execute ex('11.99'::numeric(5,2));

set enable_bitmapscan = off;

explain (settings) execute ex('4.99'::numeric(5,2));
explain (settings) execute ex('11.99'::numeric(5,2));

set plan_cache_mode = 'force_generic_plan';

explain (settings) execute ex('4.99'::numeric(5,2));
explain (settings) execute ex('11.99'::numeric(5,2));

set enable_bitmapscan = on;
explain (settings) execute ex('11.99'::numeric(5,2));

set plan_cache_mode = 'force_custom_plan';
explain (settings) execute ex('11.99'::numeric(5,2));




/* JSON Path */ 


CREATE TABLE rental_report (report jsonb);

INSERT INTO rental_report VALUES
('{ "customer": "KEN", "rental_date": "2005-07-08", "rentals": { "title": "SORORITY QUEEN", "mpaa-rating" : "R" } }')
,
('{ "customer": "ELSIE", "rental_date": "2005-08-17", "rentals": { "title": "CRAZY HOME", "mpaa-rating" : "PG-13" } } ')
,
('{ "customer": "JIMMY", "rental_date": "2005-08-18", "rentals": { "title": "POCUS PULP", "mpaa-rating" : "G" } } ')
,
('{ "customer": "KATHY", "rental_date": "2005-07-29", "rentals": { "title": "OUTFIELD MASSACRE", "mpaa-rating": "R" } } ')
,
('{ "customer": "GLADYS", "rental_date": "2005-08-23", "rentals": { "title": "OUTBREAK DIVINE", "mpaa-rating": "PG-13" } } ') 
,
('{ "customer": "SHAWN", "rental_date": "2005-08-20", "rentals": { "title": "SCALAWAG DUCK", "mpaa-rating": "PG" } } ') 
,
('{ "customer": "DIANE", "rental_date": "2005-07-31", "rentals": { "title": "MIDSUMMER GROUNDHOG", "mpaa-rating": "G" } } ')
;

select report->'customer' as customer from rental_report;

select jsonb_path_query(report,'$.customer') from rental_report;

select jsonb_path_query(report,'$.rentals[0].title') from rental_report;

select jsonb_path_query(report,'$.rentals[0].*') from rental_report; 




/* Add entries to ENUM types */ 

\dT+ mpaa_rating 

ALTER TYPE mpaa_rating ADD value 'XXX';

\dT+ mpaa_rating 




/* Pluggable Storage */ 
\h create access method

\d actor 
\set HIDE_TABLEAM on

\dA




/* Log Transaction Sample */
select * from pg_settings where name = 'log_transaction_sample_rate';




/* COPY With Where Clause */
COPY (select * from customer_list) to '/demo/pg12/customer_list.csv' WITH csv;

\! head /demo/pg12/customer_list.csv

CREATE TABLE us_customers AS SELECT * FROM customer_list LIMIT 0;

COPY us_customers from '/demo/pg12/customer_list.csv' with csv WHERE country = 'United States';




/* psql Tricks */

\conninfo 

\pset format csv 
select * from actor limit 5;
\pset format aligned 
select * from actor limit 5;

// Label partitioned tables
\d payment 




/* SQL Callable Standby Promotion */
\df pg_promote




/* Better Default Recovery Target */ 
select * from pg_settings where name = 'recovery_target_timeline';




/* Partition Introspection */ 
SELECT pg_partition_root('payment_p2007_06');
SELECT pg_partition_ancestors('payment_p2007_06');
SELECT pg_partition_tree('payment');
SELECT * FROM pg_partition_tree('payment');




-- FIN 
/* 
PostgreSQL License

Copyright (c) 2019,2020 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.
*/