Last active
August 23, 2022 03:06
-
-
Save xzilla/7f614f2fcc7f67a4773b358ea14f9127 to your computer and use it in GitHub Desktop.
Revisions
-
xzilla revised this gist
Aug 23, 2022 . 1 changed file with 5 additions and 5 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,7 +1,7 @@ \q /* What's New In PostgreSQL 14: All Demo Edition Robert Treat (@robtreat2) - v5 - 2022-08-21 Requirements: Postgres 14 @@ -81,7 +81,7 @@ select * from pg_stat_wal; /* 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; @@ -121,12 +121,12 @@ 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" '; -
Jul 28, 2022 . 1 changed file with 3 additions and 3 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,7 +1,7 @@ \q /* What's New In PostgreSQL 14: All Demo Edition Robert Treat (@robtreat2) - v4 - 2022-07-28 Requirements: Postgres 14 @@ -194,9 +194,9 @@ select setting, unit, short_desc, context, source, boot_val from pg_settings whe 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); -
Jul 28, 2022 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,281 @@ \q /* What's New In PostgreSQL 14: All Demo Edition Robert Treat (@robtreat2) - v3 - 2022-07-25 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, OUT p_currently_rented) 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(); /* allow multi-row DEFAULT inserts on generated columns */ insert into film (title, language_id, rental_revenue) values ('CLUB FIGHT', 1, DEFAULT), ('FICTIONAL PLUP', 1, DEFAULT); SELECT 'v13 => ERROR: cannot insert into column "rental_revenue" '; /* 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_momoize = off; explain select * from duck join horse using (fight); set enable_momoize = 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. */