SELECT a.*, s.*
FROM   pg_stat_activity a
       JOIN pg_stat_statements s USING (queryid);

SHOW compute_query_id;

compute_query_id
------------------
off

SELECT queryid,query
FROM   pg_stat_activity
WHERE pid=pg_backend_pid();

queryid |            query
---------+-----------------------------
         | SELECT queryid,query       +
         | FROM   pg_stat_activity    +
         | WHERE pid=pg_backend_pid();

SET compute_query_id=on;
SELECT queryid,query
FROM   pg_stat_activity
WHERE pid=pg_backend_pid();

queryid       |            query
---------------------+-----------------------------
5840773237036126161 | SELECT queryid,query       +
                     | FROM   pg_stat_activity    +
                     | WHERE pid=pg_backend_pid();

SELECT queryid, query
FROM   pg_stat_statements
WHERE  queryid = 5840773237036126161\gx

-[ RECORD 1 ]-------------------------------------------------------------
queryid | 5840773237036126161
query   | SELECT queryid,query FROM pg_stat_activity WHERE pid=pg_backend_pid()

EXPLAIN (VERBOSE, COSTS OFF) SELECT queryid,query
FROM   pg_stat_activity
WHERE pid=pg_backend_pid();

QUERY PLAN
------------------------------------------------------
Function Scan on pg_catalog.pg_stat_get_activity s
   Output: s.queryid, s.query
   Function Call: pg_stat_get_activity(NULL::integer)
   Filter: (s.pid = pg_backend_pid())
Query Identifier: 5840773237036126161

CREATE TABLE t ();
SELECT pg_stat_statements_reset();
SET pg_stat_statements.track = 'all';
TRUNCATE t;
DO 'BEGIN TRUNCATE t; END;';
SELECT query, toplevel, calls
FROM   pg_stat_statements
WHERE  query LIKE '%TRUNCATE%';

query            | toplevel | calls
-----------------------------+----------+-------
TRUNCATE t                  | f        |     1
DO 'BEGIN TRUNCATE t; END;' | t        |     1
TRUNCATE t                  | t        |     1

SELECT * FROM pg_stat_wal\gx

-[ RECORD 1 ]----+------------------------------
wal_records      | 756763
wal_fpi          | 455817
wal_bytes        | 4719435190
wal_buffers_full | 159387
wal_write        | 163909
wal_sync         | 4186
wal_write_time   | 3211.339
wal_sync_time    | 118864.601
stats_reset      | 2021-04-12 12:50:30.878238+03

2021-03-30 16:32:00.534 MSK [28993] LOG:  automatic analyze of table "demo.bookings.bookings"
    buffer usage: 10423 hits, 16704 misses, 5 dirtied
    avg read rate: 138.978 MB/s, avg write rate: 0.042 MB/s
    I/O Timings: read=58.504 write=0.175
    system usage: CPU: user: 0.41 s, system: 0.05 s, elapsed: 0.93 s

local     all     all         peer map=m1

m1     pluzanov     postgres

LOG:  connection received: host=[local]
LOG:  connection authenticated: identity="pluzanov" method=peer (/home/pluzanov/pg14/data/pg_hba.conf:84)
LOG:  connection authorized: user=postgres database=postgres application_name=psql

SELECT * FROM pg_backend_memory_contexts LIMIT 1 \gx

-[ RECORD 1 ]-+-----------------
name          | TopMemoryContext
ident         |
parent        |
level         | 0
total_bytes   | 80776
total_nblocks | 6
free_bytes    | 11984
free_chunks   | 12
used_bytes    | 68792

SELECT pg_log_backend_memory_contexts(3003);

LOG:  logging memory contexts of PID 3003
LOG:  level: 0; TopMemoryContext: 80776 total in 6 blocks; 14784 free (19 chunks); 65992 used
...

BEGIN;
LOCK TABLE tickets;

SELECT * FROM tickets LIMIT 1;

SELECT *, now()-waitstart AS wait_time
FROM   pg_locks
WHERE  NOT granted\gx

-[ RECORD 1 ]------+------------------------------
locktype           | relation
database           | 16384
relation           | 16439
page               |
tuple              |
virtualxid         |
transactionid      |
classid            |
objid              |
objsubid           |
virtualtransaction  | 5/31
pid                | 8525
mode               | AccessShareLock
granted            | f
fastpath           | f
waitstart          | 2021-02-25 12:21:53.236232+03
wait_time          | 00:00:03.106643

CREATE TABLE flights_krr (LIKE flights);

COPY flights TO '/home/pluzanov/pg14/flights.txt';

CREATE FUNCTION slow_ins() RETURNS TRIGGER AS $$
BEGIN
    PERFORM pg_sleep(0.1);
    RETURN NEW;
END;$$ LANGUAGE plpgsql;
CREATE TRIGGER flights_krr_slow_ins
    BEFORE INSERT ON flights_krr
    FOR EACH ROW EXECUTE FUNCTION slow_ins();

COPY flights_krr FROM '/home/pluzanov/pg14/flights.txt'
    WHERE departure_airport = 'KRR';

SELECT * FROM pg_stat_progress_copy\gx

-[ RECORD 1 ]----+----------
pid              | 29657
datid            | 16384
datname          | demo
relid            | 16720
command          | COPY FROM
type             | FILE
bytes_processed  | 19333118
bytes_total      | 26338755
tuples_processed | 1385
tuples_excluded  | 156235

EXPLAIN (analyze, costs off, timing off)
SELECT * FROM flights_bi
WHERE passenger_name = 'ALEFTINA EFREMOVA';

QUERY PLAN
--------------------------------------------------------
Seq Scan on flights_bi (actual rows=28 loops=1)
   Filter: (passenger_name = 'ALEFTINA EFREMOVA'::text)
   Rows Removed by Filter: 30517048
Planning Time: 0.063 ms
Execution Time: 16559.124 ms

CREATE INDEX ON flights_bi USING brin(passenger_name text_bloom_ops);
EXPLAIN (analyze, costs off, timing off)
SELECT * FROM flights_bi
WHERE passenger_name = 'ALEFTINA EFREMOVA';

QUERY PLAN
--------------------------------------------------------------
Bitmap Heap Scan on flights_bi (actual rows=28 loops=1)
   Recheck Cond: (passenger_name = 'ALEFTINA EFREMOVA'::text)
   Rows Removed by Index Recheck: 325426
   Heap Blocks: lossy=5632
   ->  Bitmap Index Scan on flights_bi_passenger_name_idx
       (actual rows=56320 loops=1)
         Index Cond: (passenger_name = 'ALEFTINA EFREMOVA'::text)
Planning Time: 0.077 ms
Execution Time: 83.796 ms

CREATE INDEX ON flights_bi USING brin(scheduled_time);
\set d 'bookings.now()::date - interval \'7 days\''
EXPLAIN (analyze, costs off, timing off)
SELECT * FROM flights_bi
WHERE scheduled_time >= :d AND scheduled_time < :d + interval '1 day';

QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on flights_bi (actual rows=83954 loops=1)
   Recheck Cond: ...
   Rows Removed by Index Recheck: 12290
   Heap Blocks: lossy=1664
   ->  Bitmap Index Scan on flights_bi_scheduled_time_idx
       (actual rows=16640 loops=1)
         Index Cond: ...
Planning Time: 0.475 ms
Execution Time: 77.029 ms

UPDATE flights_bi
  SET airport_coord = point '(56.0211982727,57.9145011902)'
  WHERE airport_code = 'PEE';
VACUUM flights_bi;
UPDATE flights_bi
  SET airport_coord = point '(39.9566001892,43.4499015808)'
  WHERE airport_code = 'AER';
VACUUM flights_bi;

QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on flights_bi (actual rows=83954 loops=1)
   Recheck Cond: ...
   Rows Removed by Index Recheck: 4004401
   Heap Blocks: lossy=71033
   ->  Bitmap Index Scan on flights_bi_scheduled_time_idx
       (actual rows=710600 loops=1)
         Index Cond: ...
Planning Time: 9.322 ms
Execution Time: 17385.129 ms

CREATE INDEX ON flights_bi USING brin(scheduled_time timestamptz_minmax_multi_ops);

QUERY PLAN
------------------------------------------------------------
Bitmap Heap Scan on flights_bi (actual rows=83954 loops=1)
   Recheck Cond: ...
   Rows Removed by Index Recheck: 672976
   Heap Blocks: lossy=13157
   ->  Bitmap Index Scan on flights_bi_scheduled_time_idx1
       (actual rows=131840 loops=1)
         Index Cond: ...
Planning Time: 0.291 ms
Execution Time: 378.037 ms

CREATE TABLESPACE new_place LOCATION '/home/pluzanov/pg14/data2';
\db

List of tablespaces
    Name    |  Owner   |         Location
------------+----------+---------------------------
new_place  | postgres | /home/pluzanov/pg14/data2
pg_default | postgres |
pg_global  | postgres |
(3 rows)

REINDEX (TABLESPACE new_place) TABLE flights;
SELECT c.relname, c.relkind, ts.spcname
FROM   pg_class c
       LEFT JOIN pg_tablespace ts ON c.reltablespace = ts.oid
WHERE  relname LIKE 'flights%' AND relkind IN ('r','i');

relname                  | relkind |  spcname
-------------------------------------------+---------+-----------
flights                                   | r       |
flights_flight_no_scheduled_departure_key | i       | new_place
flights_pkey                              | i       | new_place
(3 rows)

EXPLAIN SELECT * FROM tickets WHERE ctid >= '(0,0)' AND ctid < '(1,0)';

QUERY PLAN
----------------------------------------------------------------
Tid Range Scan on tickets  (cost=0.01..4.59 rows=59 width=104)
   TID Cond: ((ctid >= '(0,0)'::tid) AND (ctid < '(1,0)'::tid))

QUERY PLAN
-----------------------------------------------------------------------------
Gather  (cost=1000.00..68857.51 rows=59 width=104)
   Workers Planned: 2
   ->  Parallel Seq Scan on tickets  (cost=0.00..67851.61 rows=25 width=104)
         Filter: ((ctid >= '(0,0)'::tid) AND (ctid < '(1,0)'::tid))

\d bookings_range

Partitioned table "bookings.bookings_range"
    Column    |           Type           | Collation | Nullable | Default
--------------+--------------------------+-----------+----------+---------
book_ref     | character(6)             |           |          |
book_date    | timestamp with time zone |           |          |
total_amount | numeric(10,2)            |           |          |
Partition key: RANGE (book_date)
Number of partitions: 3 (Use \d+ to list them.)

EXPLAIN (COSTS OFF) SELECT COUNT(*) FROM bookings_range;

QUERY PLAN
------------------------------------------------------------
Aggregate
   ->  Append
         ->  Seq Scan on p201607 bookings_range_1
         ->  Async Foreign Scan on p201608 bookings_range_2
         ->  Async Foreign Scan on p201609 bookings_range_3

\det

List of foreign tables
  Schema  |  Table  |   Server
----------+---------+------------
bookings | p201608 | srv_201608
bookings | p201609 | srv_201609

CREATE MATERIALIZED VIEW top10 AS
SELECT book_ref, ticket_no
FROM   tickets
WHERE  book_ref IN (
           SELECT book_ref
           FROM   tickets
           GROUP BY book_ref
           ORDER BY count(*) DESC LIMIT 10
);
ANALYZE top10;

SELECT book_ref, COUNT(*) FROM top10 GROUP BY 1;

book_ref | count
----------+-------
581B6D   |     5
831982   |     5
88C866   |     5
D6B5B2   |     5
0D15DF   |     5
E9F768   |     5
876B11   |     5
10EAF6   |     5
A4CCF6   |     5
A1A654   |     5

EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM bookings JOIN top10 USING (book_ref);

QUERY PLAN
----------------------------------------------------------------------------
Nested Loop (actual rows=50 loops=1)
   ->  Seq Scan on top10 (actual rows=50 loops=1)
   ->  Result Cache (actual rows=1 loops=50)
         Cache Key: top10.book_ref
         Hits: 40  Misses: 10  Evictions: 0  Overflows: 0  Memory Usage: 2kB
         ->  Index Scan using bookings_pkey on bookings (actual rows=1 loops=10)
               Index Cond: (book_ref = top10.book_ref)

SET enable_resultcache = OFF;
EXPLAIN (COSTS OFF, ANALYZE, TIMING OFF, SUMMARY OFF)
SELECT * FROM bookings JOIN top10 USING (book_ref);

QUERY PLAN
---------------------------------------------------------------------------
Nested Loop (actual rows=50 loops=1)
   ->  Seq Scan on top10 (actual rows=50 loops=1)
   ->  Index Scan using bookings_pkey on bookings (actual rows=1 loops=50)
         Index Cond: (book_ref = top10.book_ref)

EXPLAIN (ANALYZE, TIMING OFF, COSTS OFF, SUMMARY OFF)
SELECT * FROM tickets WHERE NULLIF(1, 2) = 2;

QUERY PLAN
--------------------------------------------
Result (actual rows=0 loops=1)
   One-Time Filter: (NULLIF(1, 2) = 2)
   ->  Seq Scan on tickets (never executed)

QUERY PLAN
--------------------------------
Result (actual rows=0 loops=1)
   One-Time Filter: false

CREATE SUBSCRIPTION sub1 .. PUBLICATION pub1, pub2, pub3;

ALTER SUBSCRIPTION sub1 SET PUBLICATION новый_список_публикаций;

ALTER SUBSCRIPTION sub1 ADD PUBLICATION pub4;
ALTER SUBSCRIPTION sub1 DROP PUBLICATION pub1;

\c template1 postgres
GRANT SELECT ON pg_statistic TO pg_database_owner;

\c postgres postgres
CREATE ROLE app_owner LOGIN;
CREATE DATABASE app_db OWNER app_owner;

\c app_db app_owner
SELECT COUNT(*) FROM pg_statistic;

count
-------
   405

CREATE ROLE readonly LOGIN BYPASSRLS;
GRANT pg_read_all_data TO readonly;

CREATE TABLE t (col1 int primary key, col2 int unique)
    WITH (autovacuum_enabled=false);
INSERT INTO t SELECT f.x, f.x FROM generate_series(1,100000) AS f(x);
UPDATE t SET col2 = -col2 WHERE col1 < 100;

VACUUM VERBOSE t;

INFO:  vacuuming "public.t"
INFO:  "t": index scan bypassed: 443 pages from table (0.23% of total) have 99 dead item identifiers
INFO:  launched 1 parallel vacuum worker for index cleanup (planned: 1)
INFO:  "t": found 99 removable, 100000 nonremovable row versions in 443 out of 443 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 27459
0 pages removed.
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.02 s.

VACUUM (PROCESS_TOAST OFF) tickets;

SELECT date_bin('6 hours', scheduled_departure, '1970-01-01') AS day_part,
       count(*)
FROM   flights
WHERE  scheduled_departure::date = bookings.now()::date
GROUP BY day_part
ORDER BY day_part;

day_part        | count
------------------------+-------
2017-08-15 00:00:00+03 |    13
2017-08-15 06:00:00+03 |   224
2017-08-15 12:00:00+03 |   255
2017-08-15 18:00:00+03 |    53

SELECT bit_xor(n) FROM (
VALUES
  (b'100'),
  (b'110'),
  (b'111')
) t(n);

bit_xor
---------
101
(1 row)

SELECT bit_count(B'001'), bit_count(B'011'), bit_count(B'111');

bit_count | bit_count | bit_count
-----------+-----------+-----------
         1 |         2 |         3

SELECT unistr('\u0400\u0401\u0402\u0403\u0404');

unistr
--------
ЀЁЂЃЄ

SELECT trim_array(ARRAY['A','B','C','D','E'], 2);

trim_array
------------
{A,B,C}

SELECT pg_typeof(extract(epoch from now()));

pg_typeof
------------------
double precision

SELECT pg_typeof(extract(epoch from now()));

pg_typeof
-----------
numeric

CREATE TABLE json_data (name text, data jsonb);
\d+ json_data

Table "public.json_data"
Column | Type  | Collation | Nullable | Default | Storage  | Compression ...
--------+-------+-----------+----------+---------+----------+-------------
name   | text  |           |          |         | extended | pglz
data   | jsonb |           |          |         | extended | pglz
Access method: heap

ALTER TABLE json_data ALTER COLUMN data SET COMPRESSION lz4;
\d+ json_data

Table "public.json_data"
Column | Type  | Collation | Nullable | Default | Storage  | Compression ...
--------+-------+-----------+----------+---------+----------+-------------
name   | text  |           |          |         | extended | pglz
data   | jsonb |           |          |         | extended | lz4
Access method: heap

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM flights WHERE to_char(scheduled_departure, 'DY') = 'MON';

QUERY PLAN
--------------------------------------------------------------------------
Gather  (cost=1000.00..5627.29 rows=1074 width=63)
         (actual rows=30552 loops=1)
...

CREATE STATISTICS s_flights ON to_char(scheduled_departure, 'DY')
    FROM flights;
ANALYZE flights;

SELECT * FROM pg_stats_ext_exprs WHERE statistics_name = 's_flights'\gx

-[ RECORD 1 ]----------+--------------------------------------------------
schemaname             | bookings
tablename              | flights
statistics_schemaname  | bookings
statistics_name        | s_flights
statistics_owner       | postgres
expr                   | to_char(scheduled_departure, 'DY'::text)
null_frac              | 0
avg_width              | 7
n_distinct             | 7
most_common_vals       | {SAT,TUE,WED,THU,MON,SUN,FRI}
most_common_freqs      | {0.1483,0.14606667,0.14523333,0.14496666,0.14436667,0.14196667,0.1291}
histogram_bounds       |
correlation            | 0.1514369
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM flights WHERE to_char(scheduled_departure, 'DY') = 'MON';

QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on flights  (cost=0.00..5847.00 rows=31020 width=63)
                      (actual rows=30552 loops=1)
...

CREATE EXTENSION postgres_fdw;
CREATE SERVER demo_srv_cached FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'demo');
CREATE SERVER demo_srv_not_cached FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (dbname 'demo', keep_connections 'off');
CREATE USER MAPPING FOR postgres SERVER demo_srv_cached
OPTIONS (user 'postgres', password 'postgres');
CREATE USER MAPPING FOR postgres SERVER demo_srv_not_cached
OPTIONS (user 'postgres', password 'postgres');

CREATE FOREIGN TABLE bookings_cached (
    book_ref char(6),
    book_date timestamptz,
    total_amount numeric(10,2)
) SERVER demo_srv_cached
  OPTIONS (schema_name 'bookings', table_name 'bookings');
CREATE FOREIGN TABLE bookings_not_cached (
    book_ref char(6),
    book_date timestamptz,
    total_amount numeric(10,2)
) SERVER demo_srv_not_cached
  OPTIONS (schema_name 'bookings', table_name 'bookings');

SELECT count(*) FROM bookings_not_cached;

count
---------
2111110

SELECT datname, pid  FROM pg_stat_activity
WHERE application_name = 'postgres_fdw';

datname | pid
---------+-----
(0 rows)

SELECT count(*) FROM bookings_cached;

count
---------
2111110

SELECT datname, pid  FROM pg_stat_activity
WHERE application_name = 'postgres_fdw';

datname |  pid
---------+-------
demo    | 17876

CREATE TABLE logdata (ts date) PARTITION BY RANGE (ts);
CREATE TABLE ts_20210401 PARTITION OF logdata
    FOR VALUES FROM ('2021-04-01'::date) TO ('2021-04-02'::date);
CREATE TABLE ts_20210402 PARTITION OF logdata
    FOR VALUES FROM ('2021-04-02'::date) TO ('2021-04-03'::date);

|    BEGIN;
|    INSERT INTO logdata VALUES ('2021-04-02'::date);

ALTER TABLE logdata DETACH PARTITION ts_20210401 CONCURRENTLY;

||    INSERT INTO logdata VALUES ('2021-04-01'::date);

ERROR:  no partition of relation "logdata" found for row
DETAIL:  Partition key of the failing row contains (ts) = (2021-04-01).

||    BEGIN;
||    INSERT INTO logdata VALUES ('2021-04-02'::date);
||    INSERT 0 1

ALTER TABLE .. DETACH PARTITION .. FINALYZE;

CREATE TABLE bookings_range (
       book_ref     character(6),
       book_date    timestamptz,
       total_amount numeric(10,2)
   ) PARTITION BY RANGE(book_date);
CREATE TABLE p201607 PARTITION OF bookings_range
       FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
CREATE TABLE p201608 PARTITION OF bookings_range
       FOR VALUES FROM ('2016-08-01') TO ('2016-09-01');
CREATE TABLE p201609 PARTITION OF bookings_range
       FOR VALUES FROM ('2016-09-01') TO ('2016-10-01');
INSERT INTO bookings_range
SELECT * FROM bookings WHERE book_date < '2016-10-01';

EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
SELECT * FROM bookings_range
WHERE  book_date = to_timestamp('01.08.2016','DD.MM.YYYY');

QUERY PLAN
--------------------------------------------------------------------------
Gather (actual rows=3 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Append (actual rows=1 loops=3)
         Subplans Removed: 2
         ->  Parallel Seq Scan on p201608 bookings_range_1
             (actual rows=1 loops=3)
               Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
               Rows Removed by Filter: 56109

-- BEGIN;
EXPLAIN (ANALYZE, COSTS OFF, TIMING OFF, SUMMARY OFF)
DELETE FROM bookings_range
WHERE  book_date = to_timestamp('01.08.2016','DD.MM.YYYY');
-- ROLLBACK;

QUERY PLAN
--------------------------------------------------------------------------
Delete on bookings_range (actual rows=0 loops=1)
   Delete on p201607 bookings_range_1
   Delete on p201608 bookings_range_2
   Delete on p201609 bookings_range_3
   ->  Seq Scan on p201607 bookings_range_1 (actual rows=0 loops=1)
         Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
         Rows Removed by Filter: 10878
   ->  Seq Scan on p201608 bookings_range_2 (actual rows=3 loops=1)
         Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
         Rows Removed by Filter: 168326
   ->  Seq Scan on p201609 bookings_range_3 (actual rows=0 loops=1)
         Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
         Rows Removed by Filter: 165421

QUERY PLAN
--------------------------------------------------------------------------
Delete on bookings_range (actual rows=0 loops=1)
   Delete on p201607 bookings_range
   Delete on p201608 bookings_range_1
   Delete on p201609 bookings_range
   ->  Append (actual rows=3 loops=1)
         Subplans Removed: 2
         ->  Seq Scan on p201608 bookings_range_1 (actual rows=3 loops=1)
               Filter: (book_date = to_timestamp('01.08.2016'::text, 'DD.MM.YYYY'::text))
               Rows Removed by Filter: 168326

WITH RECURSIVE r(pktable, fktable, is_cycle, path) AS (
    SELECT pktable, fktable, false, ARRAY[pktable]
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_constraint'::regclass
  UNION ALL
    SELECT f.pktable, f.fktable, f.pktable = ANY(path), r.path||f.pktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
    AND    NOT r.is_cycle
)
SELECT * FROM r WHERE NOT is_cycle;

pktable    |    fktable    | is_cycle |            path
---------------+---------------+----------+----------------------------
pg_constraint | pg_constraint | f        | {pg_constraint}
pg_constraint | pg_trigger    | f        | {pg_constraint}
pg_trigger    | pg_trigger    | f        | {pg_constraint,pg_trigger}
(3 rows)

WITH RECURSIVE r(pktable, fktable) AS (
    SELECT pktable, fktable
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_constraint'::regclass
  UNION ALL
    SELECT f.pktable, f.fktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
)
  CYCLE pktable SET is_cycle USING path
SELECT * FROM r;

pktable    |    fktable    | is_cycle |              path
---------------+---------------+----------+-------------------------------
pg_constraint | pg_constraint | f        | {(pg_constraint)}
pg_constraint | pg_trigger    | f        | {(pg_constraint)}
pg_constraint | pg_constraint | t        | {(pg_constraint),(pg_constraint)}
pg_trigger    | pg_trigger    | f        | {(pg_constraint),(pg_trigger)}
pg_constraint | pg_trigger    | t        | {(pg_constraint),(pg_constraint)}
pg_trigger    | pg_trigger    | t        | {(pg_constraint),(pg_trigger),(pg_trigger)}
(6 rows)

WITH RECURSIVE r(pktable, fktable) AS (
    SELECT pktable, fktable
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_attribute'::regclass
    GROUP BY pktable, fktable
  UNION ALL
    SELECT f.pktable, f.fktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
    GROUP BY f.pktable, f.fktable
) SEARCH DEPTH FIRST BY pktable SET sort_by
  CYCLE pktable SET is_cycle USING path
SELECT pktable, fktable, sort_by FROM r WHERE NOT is_cycle
ORDER BY sort_by;

pktable      |        fktable        |                    sort_by
------------------+-----------------------+-----------------------------------------------
pg_attribute     | pg_attrdef            | {(pg_attribute)}
pg_attribute     | pg_constraint         | {(pg_attribute)}
pg_attribute     | pg_index              | {(pg_attribute)}
pg_attribute     | pg_statistic          | {(pg_attribute)}
pg_attribute     | pg_trigger            | {(pg_attribute)}
pg_attribute     | pg_partitioned_table  | {(pg_attribute)}
pg_attribute     | pg_statistic_ext      | {(pg_attribute)}
pg_constraint    | pg_constraint         | {(pg_attribute),(pg_constraint)}
pg_constraint    | pg_trigger            | {(pg_attribute),(pg_constraint)}
pg_trigger       | pg_trigger            | {(pg_attribute),(pg_constraint),(pg_trigger)}
pg_trigger       | pg_trigger            | {(pg_attribute),(pg_trigger)}
pg_statistic_ext | pg_statistic_ext_data | {(pg_attribute),(pg_statistic_ext)}

WITH RECURSIVE r(pktable, fktable) AS (
    SELECT pktable, fktable
    FROM   pg_get_catalog_foreign_keys()
    WHERE  pktable = 'pg_attribute'::regclass
    GROUP BY pktable, fktable
  UNION ALL
    SELECT f.pktable, f.fktable
    FROM   pg_get_catalog_foreign_keys() f, r
    WHERE  f.pktable = r.fktable
    GROUP BY f.pktable, f.fktable
) SEARCH BREADTH FIRST BY pktable SET sort_by
  CYCLE pktable SET is_cycle USING path
SELECT pktable, fktable, sort_by FROM r WHERE NOT is_cycle
ORDER BY sort_by;

pktable      |        fktable        |       sort_by
------------------+-----------------------+----------------------
pg_attribute     | pg_attrdef            | (0,pg_attribute)
pg_attribute     | pg_constraint         | (0,pg_attribute)
pg_attribute     | pg_index              | (0,pg_attribute)
pg_attribute     | pg_statistic          | (0,pg_attribute)
pg_attribute     | pg_trigger            | (0,pg_attribute)
pg_attribute     | pg_partitioned_table  | (0,pg_attribute)
pg_attribute     | pg_statistic_ext      | (0,pg_attribute)
pg_constraint    | pg_constraint         | (1,pg_constraint)
pg_constraint    | pg_trigger            | (1,pg_constraint)
pg_trigger       | pg_trigger            | (1,pg_trigger)
pg_statistic_ext | pg_statistic_ext_data | (1,pg_statistic_ext)
pg_trigger       | pg_trigger            | (2,pg_trigger)

SELECT status, departure_airport, count(*)
FROM   flights_v
WHERE  departure_city = 'Усть-Кут'
GROUP BY ROLLUP(status, departure_airport);

status   | departure_airport | count
-----------+-------------------+-------
           |                   |   396
Arrived   | UKX               |   366
On Time   | UKX               |     1
Scheduled | UKX               |    28
Cancelled | UKX               |     1
Arrived   |                   |   366
On Time   |                   |     1
Cancelled |                   |     1
Scheduled |                   |    28

GROUP BY ROLLUP(status,departure_airport), ROLLUP(status,arrival_airport);

GROUP BY DISTINCT ROLLUP(status,departure_airport), ROLLUP(status,arrival_airport);

SELECT x.*
FROM   ticket_flights JOIN boarding_passes USING (ticket_no, flight_id) AS x
LIMIT 1;

ticket_no   | flight_id
---------------+-----------
0005435189093 |    198393

CREATE FUNCTION maximum(a integer, b integer) RETURNS integer
LANGUAGE SQL
    RETURN CASE WHEN a > b THEN a ELSE b END;
SELECT maximum(10,12);

maximum
---------
      12

CREATE FUNCTION maximum(a anyelement, b anyelement) RETURNS anyelement
LANGUAGE SQL
    RETURN CASE WHEN a > b THEN a ELSE b END;

ERROR:  SQL function with unquoted function body cannot have polymorphic arguments

CREATE FUNCTION maximum(a anyelement, b anyelement) RETURNS anyelement
LANGUAGE SQL
    AS $$SELECT CASE WHEN a > b THEN a ELSE b END$$;

CREATE FUNCTION

CREATE TABLE t (id int);
CREATE FUNCTION add_and_count (p int) RETURNS bigint
LANGUAGE SQL
BEGIN ATOMIC
    INSERT INTO t VALUES(p);
    SELECT count(*) FROM t;
END;
SELECT add_and_count(100);

add_and_count
---------------
             1

ALTER TABLE t RENAME TO t_new;
SELECT add_and_count(101);

add_and_count
---------------
             2

DROP TABLE t_new;

ERROR:  cannot drop table t_new because other objects depend on it
DETAIL:  function add_and_count(integer) depends on table t_new
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

\sf add_and_count

CREATE OR REPLACE FUNCTION public.add_and_count(p integer)
RETURNS bigint
LANGUAGE sql
BEGIN ATOMIC
INSERT INTO t_new (id)
   VALUES (add_and_count.p);
SELECT count(*) AS count
    FROM t_new;
END

CREATE SCHEMA app;
CREATE EXTENSION ltree SCHEMA app;
CREATE TABLE t (id int, data app.ltree);

$ pg_dump -d demo | grep 'CREATE EXTENSION'
CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA app;

$ pg_dump -d demo -n app | grep 'CREATE EXTENSION'
$

$ pg_dump -d demo -n app --extension ltree | grep 'CREATE EXTENSION'
CREATE EXTENSION IF NOT EXISTS ltree WITH SCHEMA app;

\df min int

List of functions
   Schema   | Name | Result data type | Argument data types | Type
------------+------+------------------+---------------------+------
pg_catalog | min  | integer          | integer             | agg
(1 row)

select 1\; select 2;

?column?
----------
        2

select 1\; select 2;

?column?
----------
        1
(1 row)
?column?
----------
        2

\set r random_zipfian(0, 100000000, 1.07)
\set k abs(hash(:r)) % 1000000

\set size 1000000
\set r random_zipfian(1, :size, 1.07)
\set k 1 + permute(:r, :size)

COPY bookings TO '/home/pluzanov/pg13/bookings.txt';
BEGIN;
CREATE TABLE bookings_copy (LIKE bookings) WITH (autovacuum_enabled=off);
COPY bookings_copy FROM '/home/pluzanov/pg13/bookings.txt' WITH FREEZE;
COMMIT;

CHECKPOINT;
SELECT pg_stat_reset_shared('bgwriter');

VACUUM VERBOSE bookings_copy;

INFO:  vacuuming "bookings.bookings_copy"
INFO:  "bookings_copy": found 0 removable, 2111110 nonremovable row versions in 13447 out of 13447 pages
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 1197
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.49 s, system: 0.26 s, elapsed: 5.01 s.

CHECKPOINT;
SELECT buffers_checkpoint + buffers_clean + buffers_backend
FROM   pg_stat_bgwriter;

?column?
----------
    13460

SELECT regexp_match('foof', '(^f)o*\1');

SELECT regexp_match('foof', '(^f)o*\1');

regexp_match
--------------
{f}