7 Contrib Modules

postgres=# CREATE INDEX idx1_gin1 ON gin1 USING GIN
(to_tsvector('english', title1)) ;
CREATE INDEX
postgres=# SELECT gin_index_check('idx1_gin1') ;
 gin_index_check
-----------------
(1 row)

postgres=> CREATE INDEX idx1_bloom ON data1 USING bloom (col1) ;
CREATE INDEX
postgres=> EXPLAIN ANALYZE SELECT * FROM data1 WHERE col1=10000 ;
                                 QUERY PLAN
------------------------------------------------------------------------------
 Bitmap Heap Scan on data1  (cost=15348.00..15352.01 rows=1 width=10) …
   Recheck Cond: (col1 = 10000)
   Rows Removed by Index Recheck: 350
   Heap Blocks: exact=341
   ->  Bitmap Index Scan on idx1_bloom  (cost=0.00..15348.00 rows=1 width=0)…
         Index Cond: (col1 = 10000)
 Planning Time: 0.101 ms
 Execution Time: 3.602 ms
(8 rows)
postgres=> SELECT indexrelname, idx_scan, idx_tup_read FROM
 pg_stat_user_indexes ;
 indexrelname | idx_scan | idx_tup_read
--------------+----------+--------------
 idx1_bloom   |        1 |          351
(1 row)

postgres=# CREATE SERVER link1 FOREIGN DATA WRAPPER dblink_fdw OPTIONS
(host 'dbsvr1', port '5432', dbname 'demodb', use_scram_passthrough 'true') ;
CREATE SERVER

postgres=# \! cat /tmp/fs1.csv
100,data1
ABC,data2
300,data3
postgres=# CREATE EXTENSION file_fdw ;
CREATE EXTENSION
postgres=# CREATE SERVER fs FOREIGN DATA WRAPPER file_fdw ;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE fs1 (id INT, val TEXT) SERVER fs
OPTIONS (filename '/tmp/fs1.csv', FORMAT 'csv',
ON_ERROR 'ignore', REJECT_LIMIT '1') ;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM fs1 ;
NOTICE:  1 row was skipped due to data type incompatibility
 id  |  val
-----+-------
 100 | data1
 300 | data3
(2 rows)

postgres=# CREATE FOREIGN TABLE fs2 (id INT, val TEXT) SERVER fs
  OPTIONS (filename '/tmp/fs1.csv', FORMAT 'csv', ON_ERROR 'ignore',
REJECT_LIMIT '1', LOG_VERBOSITY 'verbose') ;
CREATE FOREIGN TABLE
postgres=# SELECT * FROM fs2 ;
NOTICE:  skipping row due to data type incompatibility at line 2 for column
"id": "ABC"
NOTICE:  1 row was skipped due to data type incompatibility
 id  |  val
-----+-------
 100 | data1
 300 | data3
(2 rows)

postgres=# SET passwordcheck.min_password_length = 12 ;
SET
postgres=# ALTER ROLE demo PASSWORD 'ABCDEFGHIJK' ;
ERROR:  password is too short
DETAIL:  password must be at least "passwordcheck.min_password_length" (12)
bytes long

postgres=> SELECT fips_mode() ;
 fips_mode
-----------
 f
(1 row)

postgres=# SET pgcrypto.builtin_crypto_enabled = off ;
SET
postgres=# SELECT gen_salt('des') ;
ERROR:  use of built-in crypto functions is disabled
postgres=# SELECT crypt('data1', 'salt1') ;
ERROR:  use of built-in crypto functions is disabled

postgres=> SELECT encrypt('foo', '0123456789', 'aes-cfb') ;
 encrypt
----------
 \x6f8ced
(1 row)
postgres=> SELECT encode(decrypt(encrypt('foo', '0123456', 'aes-cfb'),
'0123456', 'aes-cfb'), 'escape') ;
 encode
--------
 foo
(1 row)

postgres=> SELECT gen_salt('sha256crypt') ;
            gen_salt
---------------------------------
 $5$rounds=5000$yTxNtev7NxPXhErT
(1 row)
postgres=> SELECT gen_salt('sha512crypt') ;
            gen_salt
---------------------------------
 $6$rounds=5000$NoKe0.dTtV9RHhBN
(1 row)
postgres=> SELECT crypt('test', gen_salt('sha512crypt')) ;
                                                         crypt
---------------------------------------------------------------------------…
$6$rounds=5000$B6qwrNDTAV6Zq5ZX$D8mfxoa3IO2BUjZWG4fMCs52wcb9wLigkDwfA4ZZ6CE3yW
Mwy5sIZ0xLCZI/dctvxQP9Grx7sk.ooQu5GI7hf1
(1 row)

postgres=# SELECT * FROM pg_buffercache_evict_relation('data1') ;
 buffers_evicted | buffers_flushed | buffers_skipped
-----------------+-----------------+-----------------
            5410 |            5406 |               0
(1 row)

postgres=# SELECT * FROM pg_get_logical_snapshot_meta('0-57004740.snap') ;
-[ RECORD 1 ]--------
magic    | 1369563137
checksum | 1831531145
version  | 6

 postgres=# SELECT * FROM pg_get_logical_snapshot_info('0-57004740.snap') ;
-[ RECORD 1 ]------------+-----------
state                    | consistent
xmin                     | 767
xmax                     | 766
start_decoding_at        | 0/570043B8
two_phase_at             | 0/0
initial_xmin_horizon     | 0
building_full_snapshot   | f
in_slot_creation         | f
last_serialized_snapshot | 0/57004708
next_phase_at            | 0
committed_count          | 0
committed_xip            |
catchange_count          | 0
catchange_xip

postgres=# LOAD 'pg_overexplain' ;
LOAD
postgres=# EXPLAIN (DEBUG) SELECT * FROM data1 WHERE col1=1000 ;
                               QUERY PLAN
-------------------------------------------------------------------------
 Index Scan using data1_pkey on data1  (cost=0.42..8.44 rows=1 width=10)
   Index Cond: (col1 = 1000)
   Disabled Nodes: 0
   Parallel Safe: true
   Plan Node ID: 0
 PlannedStmt:
   Command Type: select
   Flags: canSetTag
   Subplans Needing Rewind: none
   Relation OIDs: 16389
   Executor Parameter Types: none
   Parse Location: 16 for 33 bytes
(12 rows)

postgres=> SHOW pg_stat_statements.track ;
 pg_stat_statements.track
--------------------------
 all
(1 row)
postgres=> CREATE TABLE data2 AS SELECT * FROM data1 WHERE col1=1000 ;
SELECT 1
postgres=>  SELECT queryid, toplevel, query FROM pg_stat_statements WHERE query
LIKE '%data2%' ;
       queryid        | toplevel |                     query
----------------------+----------+--------------------------------------------
  3120039891713996032 | f        | CREATE TABLE data6 AS SELECT * FROM …
 -9061638578758616641 | t        | CREATE TABLE data6 AS SELECT * FROM …
(2 rows)

postgres=> SET work_mem = '8MB' ;
SET
postgres=> SET work_mem = '16MB' ;
SET
postgres=> SET work_mem = DEFAULT ;
SET
postgres=> SELECT query, calls FROM pg_stat_statements WHERE query LIKE 'SET%' ;
         query          | calls
------------------------+-------
 SET work_mem = DEFAULT |     1
 SET work_mem = $1      |     2
(2 rows)

postgres=> SELECT COUNT(*) FROM data1 ;
-[ RECORD 1 ]--
count | 1000000
postgres=> SELECT query, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_statements WHERE userid=16384 ;
-[ RECORD 1 ]--------------+---------------------------
query                      | SELECT COUNT(*) FROM data1
parallel_workers_to_launch | 4
parallel_workers_launched  | 4

SELECT * FROM data1 WHERE col1 IN (10, 20) ;
SELECT * FROM data1 WHERE col1 IN (10, 20, 30, 40, 50) ;

postgres=> SELECT calls, query FROM pg_stat_statements WHERE query LIKE
'%data1%' ;
 calls |                          query
-------+----------------------------------------------------------
     1 | SELECT * FROM data1 WHERE col1 IN ($1, $2)
     1 | SELECT * FROM data1 WHERE col1 IN ($1, $2, $3)
(2 rows)

postgres=> SELECT calls, query FROM pg_stat_statements WHERE query LIKE
'%data1%' ;
 calls |                      query
-------+---------------------------------------------------
     2 | SELECT * FROM data1 WHERE col1 IN ($1 /*, ... */)
(1 row)

postgres=# CREATE EXTENSION pageinspect ;
CREATE EXTENSION
postgres=# SELECT tuple_data_split('seq1'::regclass, t_data, t_infomask,
t_infomask2, t_bits) FROM heap_page_items(get_raw_page('seq1', 0)) ;
-[ RECORD 1 ]----+------------------------------------------------------
tuple_data_split | {"\\x0100000000000000","\\x0000000000000000","\\x00"}

postgres=# CREATE SEQUENCE seq1 ;
CREATE SEQUENCE
postgres=# CREATE EXTENSION pgstattuple ;
CREATE EXTENSION
postgres=# SELECT * FROM pgstattuple('seq1') ;
-[ RECORD 1 ]------+------
table_len          | 8192
tuple_count        | 1
tuple_len          | 41
tuple_percent      | 0.5
dead_tuple_count   | 0
dead_tuple_len     | 0
dead_tuple_percent | 0
free_space         | 8108
free_percent       | 98.97

postgres=# CREATE SERVER remsvr1 FOREIGN DATA WRAPPER postgres_fdw
OPTIONS (host 'dbsvr1', port '5432', dbname 'postgres') ;
CREATE SERVER
postgres=# CREATE USER MAPPING FOR demo SERVER remsvr1
OPTIONS (user 'demo', use_scram_passthrough 'true') ;
CREATE USER MAPPING

postgres=> BEGIN ;
BEGIN
postgres=*> INSERT INTO data1 VALUES (100, 'data1') ;
INSERT 0 1
postgres=*> SELECT * FROM postgres_fdw_get_connections(true) ;
 server_name | user_name | valid | used_in_xact | closed | remote_backend_pid
-------------+-----------+-------+--------------+--------+--------------------
 remsvr1     | public    | t     | t            | f      |             309160
(1 row)
postgres=*> COMMIT ;
COMMIT
postgres=> SELECT * FROM postgres_fdw_get_connections(true) ;
 server_name | user_name | valid | used_in_xact | closed | remote_backend_pid
-------------+-----------+-------+--------------+--------+--------------------
 remsvr1     | public    | t     | f            | f      |             309160
(1 row)

postgres=> CREATE FOREIGN TABLE data1(col1 INT, col2 VARCHAR(10))
SERVER remsvr1 ;
CREATE FOREIGN TABLE
postgres=> SELECT * FROM data1 ;
 col1 | col2
------+------
(0 rows)
postgres=> SELECT * FROM postgres_fdw_get_connections() ;
 server_name | user_name | valid | used_in_xact | closed | remote_backend_pid
-------------+-----------+-------+--------------+--------+--------------------
 remsvr1     | demo      | t     | f            |        |             370760
(1 row)