3 Architecture

postgres=# SELECT name, parent, level, total_bytes FROM
 pg_backend_memory_contexts ;
           name           |       parent       | level | total_bytes
--------------------------+--------------------+-------+------------
TopMemoryContext         |                    |     0 |       68704
 TopTransactionContext    | TopMemoryContext   |     1 |        8192
 Record information cache | TopMemoryContext   |     1 |        8192
 TableSpace cache         | TopMemoryContext   |     1 |        8192
 Type information cache   | TopMemoryContext   |     1 |       24376
 Operator lookup cache    | TopMemoryContext   |     1 |       24576
 RowDescriptionContext    | TopMemoryContext   |     1 |        8192
 MessageContext           | TopMemoryContext   |     1 |       32768
 Operator class cache     | TopMemoryContext   |     1 |        8192
…

postgres=> SELECT * FROM pg_stat_progress_copy ;
-[ RECORD 1 ]---+----------
pid              | 83409
datid            | 13887
datname          | postgres
relid            | 16385
command          | COPY FROM
type             | FILE
bytes_processed  | 22806528
bytes_total      | 138888897
tuples_processed | 1703999
tuples_excluded  | 0

postgres=> SELECT * FROM pg_stat_replication_slots ;
-[ RECORD 1 ]+----------
slot_name    | sub1
spill_txns   | 3
spill_count  | 9
spill_bytes  | 416939406
stream_txns  | 0
stream_count | 0
stream_bytes | 0
total_txns   | 0
total_bytes  | 0
stats_reset  |

postgres=> SELECT * FROM pg_stat_wal ;
-[ RECORD 1 ]----+------------------------------
wal_records      | 52417581
wal_fpi          | 98
wal_bytes        | 4139801371
wal_buffers_full | 341903
wal_write        | 342723
wal_sync         | 876
wal_write_time   | 0
wal_sync_time    | 0
stats_reset      | 2021-10-01 10:21:41.173493+09

postgres=# CREATE SUBSCRIPTION sub1 CONNECTION 'host=remhost1
dbname=postgres user=postgres password=<<PASSWORD>>'
PUBLICATION pub1 WITH (streaming = on) ;
CREATE SUBSCRIPTION
postgres=# SELECT subname, substream  FROM pg_subscription
WHERE subname = 'sub1' ;
subname | substream
---------+-----------
 sub1    | t
(1 row)

postgres=# CREATE SUBSCRIPTION sub2 CONNECTION 'host=remhost1
dbname=postgres user=postgres password=<<PASSWORD>>'
PUBLICATION pub2 WITH (binary = on) ;
CREATE SUBSCRIPTION
postgres=# SELECT subname, subbinary  FROM pg_subscription
WHERE subname = 'sub2' ;
subname | subbinary
---------+-----------
 sub2    | t
(1 row)

LOG:  duration: 6649.485 ms  plan:
        Query Text: REFRESH MATERIALIZED VIEW mview1;
        Gather  (cost=0.00..95721.67 rows=10000000 width=12)
          Workers Planned: 2
          ->  Parallel Seq Scan on data1  (cost=0.00..95721.67
rows=4166667 width=12)

postgres=> CREATE TABLE compress1(c1 INT, c2 TEXT COMPRESSION lz4) ;
CREATE TABLE
postgres=> \d+ compress1
                                        Table "public.compress1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | …
--------+---------+-----------+----------+---------+----------+-------------+ …
 c1     | integer |           |          |         | plain    |             | …
 c2     | text    |           |          |         | extended | lz4         | …
Access method: heap
postgres=> ALTER TABLE compress1 ALTER COLUMN c2 SET COMPRESSION pglz ;
ALTER TABLE
postgres=> \d+ compress1
                                        Table "public.compress1"
 Column |  Type   | Collation | Nullable | Default | Storage  | Compression | …
--------+---------+-----------+----------+---------+----------+-------------+ …
 c1     | integer |           |          |         | plain    |             | …
 c2     | text    |           |          |         | extended | pglz        | …
Access method: heap

In the CREATE TABLE (LIKE) statement, specify the "LIKE table name INCLUDING
COMPRESSION" clause to copy the table definition up to the compressed definition. Compression
attribute of each column can be found in the attcompression column of pg_attribute catalog. 'p' is pglz
compression and 'l' is LZ4 compression. To check the compression method of the column, execute the
pg_column_compression function with the column name.

postgres=> SELECT c1, pg_column_compression(c2) FROM compress1 ;
 c1  | pg_column_compression
-----+-----------------------
 100 | pglz
 200 | lz4
(2 rows)

postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'remhost1', port '5432', dbname 'postgres',
async_capable 'on') ;
CREATE SERVER

It is displayed as Async Foreign Scan in the execution plan. In the example below, the partitions
part1v1 and part1v2 contained in the partition table part1 are implemented as FOREIGN TABLE
respectively.

postgres=> EXPLAIN SELECT * FROM part1 ;
                                      QUERY PLAN
---------------------------------------------------------------------------
 Append  (cost=100.00..306.87 rows=2482 width=42)
   ->  Async Foreign Scan on part1v1 part1_1  (cost=100.00..147.23 rows=1241
width=42)
   ->  Async Foreign Scan on part1v2 part1_2  (cost=100.00..147.23 rows=1241
width=42)
(3 rows)

postgres=> EXPLAIN SELECT COUNT(*),AVG(t1.unique1) FROM tenk1 t1 INNER JOIN
tenk1 t2 ON t1.unique1 = t2.twenty WHERE t2.unique1 < 1000 ;
                                             QUERY PLAN
---------------------------------------------------------------------------
Aggregate  (cost=450.10..450.11 rows=1 width=40)
   ->  Nested Loop  (cost=24.33..445.10 rows=1000 width=4)
         ->  Bitmap Heap Scan on tenk1 t2  (cost=24.04..381.54 …
               Recheck Cond: (unique1 < 1000)
               ->  Bitmap Index Scan on tenk1_unique1  (cost=0.00..23.79 …
                     Index Cond: (unique1 < 1000)
         ->  Memoize  (cost=0.30..1.85 rows=1 width=4)
               Cache Key: t2.twenty
               ->  Index Only Scan using tenk1_unique1 on tenk1 t1  (cost=…
                     Index Cond: (unique1 = t2.twenty)
(10 rows)

postgres=> CREATE STATISTICS extstat1_data1 ON MOD(c1, 10),
MOD(c2, 10) FROM data1 ;
CREATE STATISTICS
postgres=> ANALYZE data1 ;
ANALYZE
postgres=> SELECT * FROM pg_stats_ext_exprs ;
-[ RECORD 1 ]----------+-----------------------------------------
schemaname             | public
tablename              | data1
statistics_schemaname  | public
statistics_name        | extstat1_data1
statistics_owner       | demo
expr                   | mod(c1, 10)
null_frac              | 0
avg_width              | 4
…

postgres=> CREATE TABLE numeric1(c1 NUMERIC, c2 float) ;
CREATE TABLE
postgres=> INSERT INTO numeric1 VALUES ('Infinity', '-Infinity') ;
INSERT 0 1
postgres=> SELECT * FROM numeric1 WHERE c1='Infinity' ;
    c1    |    c2
----------+-----------
 Infinity | -Infinity
(1 row)
postgres=> SELECT 0.1 / '-Infinity', exp('-Infinity'),
power(2, '-Infinity') ;
 ?column? | exp | power
----------+-----+-------
        0 |   0 |     0
(1 row)

postgres=> SELECT '{[1, 5), (10, 20]}'::int8multirange ;
int8multirange
-----------------
 {[1,5),[11,21)}
(1 row)
postgres=> SELECT nummultirange( numrange(1, 10, '()'),
numrange(15, 20, '[]')) ;
  nummultirange
------------------
 {(1,10),[15,20]}
(1 row)

postgres=> SELECT ('[1, "2", null]'::jsonb)[1] ;
 jsonb
-------
 "2"
(1 row)
postgres=> SELECT ('{"age": 25}'::jsonb)['age'] ;
 jsonb
-------
 25
(1 row)
postgres=> SELECT ('{"email":"pgsql-hackers@postgresql.org",
"phone":"+3012345678"}'::jsonb) ['phone'] ;
     jsonb
----------------
 "+3012345678"
(1 row)

postgres=> SELECT pg_current_wal_lsn(), pg_current_wal_lsn()
+ 256 ;
 pg_current_wal_lsn | ?column?
--------------------+-----------
 0/15D1F88          | 0/15D2088
(1 row)

postgres=> CREATE INDEX idx1_data1 ON data1 USING brin
(c1 numeric_bloom_ops (false_positive_rate = 0.05,
 n_distinct_per_range = 100)) ;
CREATE INDEX

# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    postgres       postgres       192.168.1.219/32        \
                                                                md5

EXEC SQL BEGIN DECLARE SECTION;
    char *selectStr = "SELECT c1 FROM data1";
    long f1;
EXEC SQL END DECLARE SECTION;
int main()
{
    EXEC SQL CONNECT TO postgres USER postgres;
    EXEC SQL DECLARE stmt1 STATEMENT;
    EXEC SQL PREPARE stmt1 FROM :selectStr;
    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;
    EXEC SQL OPEN cur1;
    …
}

LOG:  automatic vacuum of table "postgres.public.data1": index
scans: 1
        pages: 0 removed, 5406 remain, 0 skipped due to pins, 0
skipped frozen
        tuples: 500000 removed, 500000 remain, 0 are dead but not
yet removable, oldest xmin: 566
        buffer usage: 21775 hits, 0 misses, 1 dirtied
        index scan needed: 5406 pages from table (100.00% of total)
had 500000 dead item identifiers removed
        index "idx1_data1": pages: 2745 in total, 0 newly deleted,
0 currently deleted, 0 reusable
        index "idx2_data1": pages: 2745 in total, 0 newly deleted,
0 currently deleted, 0 reusable
        avg read rate: 20.330 MB/s, avg write rate: 20.239 MB/s
…

LOG:  connection received: host=192.168.1.219 port=51524
LOG:
connection
authenticated:
identity="demo"
method=md5
(/usr/local/pgsql/data/pg_hba.conf:91)
LOG:
connection
authorized:
user=demo
database=postgres
application_name=psql

$ ps -ef|grep receiver | grep -v grep
postgres  93255  93249  0 14:34 ?        00:00:01 postgres:
walreceiver streaming 0/5D744F8
$

$ ps -ef|grep sender | grep -v grep
postgres  92995  92944  0 14:28 ?        00:00:00 postgres:
walsender postgres [local] START_REPLICATION
$

postgres=> SELECT 'abc def' ~ '[[:word:]] [[:word:]]' ;
 ?column?
----------
 t
(1 row)

$ cd src/test/modules/plsample
$ ls -l
total 24
drwxrwxr-x. 2 postgres postgres   26 Sep 28 06:00 expected
-rw-r--r--. 1 postgres postgres  440 Sep 28 05:57 Makefile
-rw-r--r--. 1 postgres postgres  469 Sep 28 05:57 plsample--1.0.sql
-rw-r--r--. 1 postgres postgres 5046 Sep 28 05:57 plsample.c
-rw-r--r--. 1 postgres postgres  177 Sep 28 05:57 plsample.control
-rw-r--r--. 1 postgres postgres  228 Sep 28 05:57 README
drwxrwxr-x. 2 postgres postgres   26 Sep 28 06:00 sql