डेविल्स डोजेन में कितना नया है?

SQL कमानों में परिवर्तन

CREATE DATABASE db_koi8r TEMPLATE template0 
    ENCODING 'KOI8R' LOCALE 'ru_RU.KOI8R';

CREATE VIEW uptime AS SELECT current_timestamp, current_timestamp - pg_postmaster_start_time();
SELECT * FROM uptime;

       current_timestamp       |    ?column?     
-------------------------------+-----------------
 2020-03-23 15:37:00.088824+03 | 04:18:24.897856

ALTER VIEW uptime RENAME COLUMN "?column?" TO uptime;
SELECT * FROM uptime;

       current_timestamp       |     uptime      
-------------------------------+-----------------
 2020-03-23 15:37:40.726516+03 | 04:19:05.535548

CREATE TABLE payments (
    id integer PRIMARY KEY,
    amount numeric(18,2),
    income_tax numeric(18,2) GENERATED ALWAYS AS (amount*0.13) STORED
);
INSERT INTO payments(id, amount) VALUES(1, 42);
\d payments

                                     Table "public.payments"
   Column   |     Type      | Collation | Nullable |                   Default            
        
------------+---------------+-----------+----------+--------------------------------------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          | generated always as ((amount * 0.13))
 stored
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)

ALTER TABLE payments ALTER COLUMN income_tax DROP EXPRESSION;
\d payments

                   Table "public.payments"
   Column   |     Type      | Collation | Nullable | Default
------------+---------------+-----------+----------+---------
 id         | integer       |           | not null |
 amount     | numeric(18,2) |           |          |
 income_tax | numeric(18,2) |           |          |
Indexes:
    "payments_pkey" PRIMARY KEY, btree (id)

SELECT * FROM payments;

 id | amount | income_tax
----+--------+------------
  1 |  42.00 |       5.46

CREATE DATABASE db;

CREATE EXTENSION IF NOT EXISTS dblink;
SELECT dblink_connect('dbname=db');

 dblink_connect
----------------
 OK

DROP DATABASE db WITH (FORCE);

CREATE DATABASE db;
\c db

SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';

 typname | typstorage
---------+------------
 tsquery | p

CREATE TABLE queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'queries'::regclass AND attname = 'query';

 attname | attstorage
---------+------------
 query   | p

ALTER TYPE tsquery SET (storage=external);
SELECT typname,typstorage FROM pg_type WHERE typname = 'tsquery';

 typname | typstorage
---------+------------
 tsquery | e

CREATE TABLE new_queries (query tsquery);
SELECT attname, attstorage FROM pg_attribute WHERE attrelid = 'new_queries'::regclass AND attname = 'query';

 attname | attstorage
---------+------------
 query   | e

ALTER TYPE tsquery SET (storage=plain);

ERROR:  cannot change type's storage to PLAIN

ALTER STATISTICS  SET STATISTICS _;

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS ONLY;

 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
(2 rows)

SELECT *
FROM (VALUES (1,'1.1'), (2,'2.1'), (2,'2.2'), (3,'3.1')) t(a,b)
ORDER BY a
FETCH FIRST 2 ROWS WITH TIES;

 a |  b  
---+-----
 1 | 1.1
 2 | 2.1
 2 | 2.2
(3 rows)

अंतर्निहित कार्य और डेटा प्रकार

SELECT gen_random_uuid();

           gen_random_uuid            
--------------------------------------
 25e02793-80c0-438c-be07-c94b966c43ab

SELECT n, min_scale(n), trim_scale(n) FROM (VALUES(42.42000)) as t(n);

    n     | min_scale | trim_scale
----------+-----------+------------
 42.42000 |         2 |      42.42

SELECT gcd(54,24), lcm(54,24);

 gcd | lcm
-----+-----
   6 | 216

SELECT min(restart_lsn) FROM pg_replication_slots;
SELECT min(sent_lsn) FROM pg_stat_replication;

CREATE TYPE currency AS (
    amount numeric(18,2),
    code   text
);
CREATE FUNCTION income_tax(quantity currency) RETURNS currency
    AS 'SELECT quantity.amount * 0.13, quantity.code' LANGUAGE SQL;

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, ''))\gx

-[ RECORD 1 ]--
amount | 5.5146
code   | 
amount | 5.5146

SELECT amount, code, amount::numeric(18,2)
FROM income_tax(ROW(42.42, ''))\gx

-[ RECORD 1 ]
amount | 5.51
code   | 
amount | 5.51

SELECT to_date(', 24  2020', 'TMDay, DD TMMonth YYYY');

  to_date   
------------
 2020-03-24

SELECT str, normalize(str, NFC) AS nfc,
       str IS NFC NORMALIZED AS is_nfc_normalized,
       normalize(str, NFKC) AS nfkc,
       str IS NFKC NORMALIZED AS is_nfkc_normalized
FROM (VALUES(U&'\0032\2075')) AS vals(str)\gx

-[ RECORD 1 ]------+---
str                | 2⁵
nfc                | 2⁵
is_nfc_normalized  | t
nfkc               | 25
is_nfkc_normalized | f

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

SELECT f, pg_typeof(f) FROM maximum(42, 42.42) f;

   f   | pg_typeof
-------+-----------
 42.42 | numeric

CREATE FUNCTION first_elems(a anyarray, b anycompatiblearray)
    RETURNS TABLE(a anyelement, b anycompatible) AS $$
        SELECT ROW(a[array_lower(a,1)], b[array_lower(b,1)]);
    $$ LANGUAGE sql;
SELECT first_elems(ARRAY[1,2,3], ARRAY['a','b','c']) AS str, 
       first_elems(ARRAY[1.1,2.2,3.3],ARRAY[true,false]) bool;

  str  |  bool   
-------+---------
 (1,a) | (1.1,t)

प्रक्रियात्मक भाषाएँ

CREATE OR REPLACE FUNCTION slow_pi() RETURNS double precision AS $$
    DECLARE
        a double precision := 1;
        s double precision := 1;
        r double precision := 0;
    BEGIN
        FOR i IN 1 .. 10000000 LOOP
            r := r + s/a; a := a + 2; s := -s;
        END LOOP;
        RETURN 4*r;
    END;
    $$ LANGUAGE plpgsql;

SELECT slow_pi();

          slow_pi      
    --------------------
     3.1415925535898497
    (1 row)
    Time: 13060,650 ms (00:13,061)

SELECT slow_pi();

          slow_pi      
    --------------------
     3.1415925535897915
    (1 row)
    Time: 2108,464 ms (00:02,108)

• विश्वसनीय एक्सटेंशन अन्य विश्वसनीय भाषाओं को बनाने के लिए तृतीय-पक्ष डेवलपर्स के लिए रास्ता खोलते हैं। अब हम केवल plpgsql, plperl और pltcl तक ही सीमित हैं।
• यह pg_pltemplateमुश्किल से लिखा गया था कि प्लिफथॉन भाषा के दूसरे संस्करण को संदर्भित करता है। pg_pltemplateऐसा करने में विफलता पायथन 3 के संक्रमण के लिए एक कदम (आवश्यक, हालांकि पर्याप्त नहीं) है।

सूचकांकों

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');

                                             QUERY PLAN                                             
------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5167
   Buffers: shared hit=24 read=27405
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=24 read=22238
 Planning Time: 0.283 ms
 Execution Time: 3258.234 ms

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF)
SELECT * FROM mail_messages WHERE tsv @@ to_tsquery('!vacuum') AND tsv @@ to_tsquery('analyze');

                                             QUERY PLAN                                             
---------------------------------------------------------------------------
Bitmap Heap Scan on mail_messages (actual rows=5864 loops=1)
   Recheck Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
   Heap Blocks: exact=5156
   Buffers: shared hit=5179
   ->  Bitmap Index Scan on mail_messages_tsv_idx (actual rows=5864 loops=1)
         Index Cond: ((tsv @@ to_tsquery('!vacuum'::text)) AND (tsv @@ to_tsquery('analyze'::text)))
         Buffers: shared hit=23
 Planning Time: 0.250 ms
 Execution Time: 8.779 ms

CREATE INDEX ON mail_messages USING gist(tsv tsvector_ops(siglen=1024));

CREATE TABLE points(point point);
CREATE TABLE boxes(box box);
INSERT INTO points VALUES('1,2','3,4');
INSERT INTO boxes VALUES(box(point(3,4),point(5,6)), box(point(13,14),point(15,16)));

SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;

:    : box <-> point

SELECT p.point, b.box, p.point  <-> b.box distance FROM points AS p, boxes AS b ORDER BY distance;

 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403

SELECT p.point, b.box, b.box  <-> p.point distance FROM points AS p, boxes AS b ORDER BY distance;

 point |       box       |      distance
-------+-----------------+--------------------
 (1,2) | (5,6),(3,4)     | 2.8284271247461903
 (2,1) | (5,6),(3,4)     | 3.1622776601683795
 (1,2) | (15,16),(13,14) | 16.970562748477143
 (2,1) | (15,16),(13,14) | 17.029386365926403

SELECT count(*) FROM pg_operator WHERE oprname = '<->';

 count 
-------
    28

Json

1. अनुकूलित फ़ंक्शन JsonbExtractScalar ();
2. अनुकूलित ऑपरेटर # >>, फ़ंक्शन jsonb_each_text (), jsonb_array_elements_text ();
3. Get_jsonb_path_all () में अनुकूलित JsonbContainer प्रकार की मान्यता अनुकूलित है;
4. JsonbIterator पुनरावृत्ति से पहला टोकन प्राप्त करना हल्के मैक्रो JsonbContainerIsXxx () द्वारा प्रतिस्थापित किया जाता है;
5. अधिक सुविधाजनक कुंजी निष्कर्षण - findJsonbKeyInObject ();
6. परिणाम का अनुकूलित संग्रहण ढूंढेंJsonbValueFromContainer () और getIthJsonbValueFromContainer ();
7. get_jsonb_path_all(), ;
   JsonbValueAsText.

pgbench

SELECT 1 AS one \; SELECT 2 AS two \aset

pg_dump

psql

• कई टीमों के लिए बेहतर टैब पूरा।
• \echoSTDOUT को एक स्ट्रिंग भेजने के अलावा , एक नया कमांड \warnमानक त्रुटि आउटपुट (STDERR) को एक स्ट्रिंग भेजता है।
• \d toast- . \d+ .
• \dt+ «Persistence» (unlogged) (permanent).
• \e , ; ( \g*). , psql .
• . PROMPT1 PROMPT2 %x.
• नए आदेश में अधिक जानकारी के लिए पहुँच तरीकों के बारे में: \dAc, \dAf, \dAo,\dAp
• \gअब आप कोष्ठक में कोई विकल्प निर्दिष्ट कर सकते हैं जो समर्थित हैं \pset। वे वर्तमान टीम पर ही कार्य करेंगे।

libpq

• मेजबान और hostadr मापदंडों के विवरण में अशुद्धि और \conninfopsql उपयोगिता कमांड के आउटपुट में परिणामी असंगति तय की गई है।
• यदि क्लाइंट प्रमाणपत्र कुंजी एन्क्रिप्टेड रूप में संग्रहीत की जाती है, तो आप केवल इंटरैक्टिव मोड में पासवर्ड दर्ज कर सकते हैं। नया sslpassword पैरामीटर आपको गैर-अंतःक्रियात्मक रूप से कुंजी को डिक्रिप्ट करने की अनुमति देता है ।
• दो नए पैरामीटर, sslminprotocolversion और sslmaxprotocolversion, आपको SSL / TCL प्रोटोकॉल के संस्करण पर प्रतिबंध निर्दिष्ट करने की अनुमति देते हैं जिसके साथ कनेक्शन की अनुमति है।

reindexdb

pg_rewind

pg_waldump

amcheck

pageinspect

postgres_fdw

ALTER USER MAPPING FOR  SERVER 
    OPTIONS (ADD password_required 'false');

adminpack

निगरानी

pg_stat_slru

pg_stat_activity

SELECT query, leader_pid, 
    array_agg(pid) filter(WHERE leader_pid != pid) AS members
  FROM pg_stat_activity
 WHERE leader_pid IS NOT NULL
 GROUP BY query, leader_pid;

pg_stat_statements

CREATE TABLE t (id int);
EXPLAIN (ANALYZE, WAL, COSTS OFF, TIMING OFF, SUMMARY OFF)
    INSERT INTO t VALUES(1);

              QUERY PLAN              
--------------------------------------
 Insert on t (actual rows=0 loops=1)
   WAL:  records=1  bytes=59
   ->  Result (actual rows=1 loops=1)

EXPLAIN (ANALYZE, BUFFERS, COSTS OFF, TIMING OFF) 
    SELECT * FROM pg_class;

                   QUERY PLAN                   
------------------------------------------------
 Seq Scan on pg_class (actual rows=386 loops=1)
   Buffers: shared hit=9 read=4
 Planning Time: 0.782 ms
   Buffers: shared hit=103 read=11
 Execution Time: 0.219 ms

पत्रिका

• log_statement_sample_rate SQL, , log_min_duration_sample( ).
  , log_min_duration_statement , .. log_min_duration_statement , log_min_duration_sample, , log_statement_sample_rate.
  , log_transaction_sample_rate , , .
• , ( log_min_error_statement), . log_parameter_max_length_on_error. 0, .
  log_parameter_max_length_on_error SQL, , .
  ( log_statements log_duration) , : log_parameter_max_length, , .
• अब आप सर्वर लॉग के लिए प्रक्रिया प्रकार ( pg_stat_activity.backend_type) लिख सकते हैं । इसके लिए log_line_prefix, पैरामीटर में एक विशेष प्रतीक प्रदान किया जाता है %b। और अगर लॉग को सीएसवी ( log_destination=csvlog) प्रारूप में लिखा गया है , तो कॉलम backend_typeपहले से ही वहां शामिल है।

प्रगति

अनुकूलन

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q
ORDER BY rank DESC;

                              QUERY PLAN                              
------------------------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, '''tuple'''::tsquery)) DESC
   ->  Bitmap Heap Scan on mail_messages
         Recheck Cond: (tsv @@ '''tuple'''::tsquery)
         ->  Bitmap Index Scan on mail_messages_tsv_idx
               Index Cond: (tsv @@ '''tuple'''::tsquery)

EXPLAIN (COSTS OFF)
SELECT subject, ts_rank_cd(tsv, q) AS rank                            
FROM mail_messages, to_tsquery('english', 'tuple') q
WHERE tsv @@ q                            
ORDER BY rank DESC;

                          QUERY PLAN                         
-----------------------------------------------------
 Sort
   Sort Key: (ts_rank_cd(mail_messages.tsv, q.q)) DESC
   ->  Nested Loop
         ->  Function Scan on q
         ->  Bitmap Heap Scan on mail_messages
               Recheck Cond: (tsv @@ q.q)
               ->  Bitmap Index Scan on mail_messages_tsv_idx
                     Index Cond: (tsv @@ q.q)

EXPLAIN (analyze, costs off, timing off)
SELECT * FROM tickets ORDER BY ticket_no, passenger_id;

                                  QUERY PLAN                                  
------------------------------------------------------------------------------
 Incremental Sort (actual rows=2949857 loops=1)
   Sort Key: ticket_no, passenger_id
   Presorted Key: ticket_no
   Full-sort Groups: 92184 Sort Method: quicksort Memory: avg=31kB peak=31kB
   ->  Index Scan using tickets_pkey on tickets (actual rows=2949857 loops=1)
 Planning Time: 2.137 ms
 Execution Time: 2230.019 ms

SET enable_incrementalsort = off;
EXPLAIN (analyze, costs off, timing off)
SELECT * FROM tickets ORDER BY ticket_no, passenger_id;

                              QUERY PLAN                               
-----------------------------------------------------------------------
 Gather Merge (actual rows=2949857 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort (actual rows=983286 loops=3)
         Sort Key: ticket_no, passenger_id
         Sort Method: external merge  Disk: 107024kB
         Worker 0:  Sort Method: external merge  Disk: 116744kB
         Worker 1:  Sort Method: external merge  Disk: 107200kB
         ->  Parallel Seq Scan on tickets (actual rows=983286 loops=3)
 Planning Time: 3.994 ms
 Execution Time: 12291.844 ms

CREATE TABLE slicingtest (
     id serial PRIMARY KEY,
     a text
);
INSERT INTO slicingtest (a) SELECT
    repeat('1234567890-=abcdefghijklmnopqrstuvwxyz', 1000000) AS a FROM
    generate_series(1,100);
\timing
SELECT sum(length(substr(a, 0, 20))) FROM slicingtest;

Time: 28.123 ms

Time: 2.306 ms

• रिलेशन एक्सटेंशन लॉक, जिसे तब कैप्चर किया जाता है जब डेटा पेज के अंत में नए पेज जोड़े जाते हैं, और
• पृष्ठ लॉक, जिसका उपयोग प्रतीक्षा सूची से मुख्य पेड़ पर GIN सूचकांक वस्तुओं को ले जाने के दौरान किया जाता है।

सुरक्षा

स्थानीयकरण

SELECT collname, collversion FROM pg_collation WHERE collname = 'ru_RU';

 collname | collversion
----------+-------------
 ru_RU    | 2.27

ALTER COLLATION "ru_RU" REFRESH VERSION;

NOTICE:  version has not changed
ALTER COLLATION

पूरा पाठ खोजें

CREATE EXTENSION dict_int;
SELECT ts_lexize('intdict', '-123');

ts_lexize
-----------
 {-123}
(1 row)

ALTER TEXT SEARCH DICTIONARY intdict (ABSVAL = true);
SELECT ts_lexize('intdict', '-123');

ts_lexize
-----------
 {123}
(1 row)

विभाजन

CREATE PUBLICATION pub FOR TABLE p;

ERROR:  "p" is a partitioned table
DETAIL:  Adding partitioned tables to publications is not supported.
HINT:  You can add the table partitions individually.

tableam

fsync

बैकअप और प्रतिकृति

खिड़कियाँ

प्रलेखन