Zobrazení zdroje stránky PostgreSQL 8.5 alpha

Bodově z jednotlivých vývojových verzí PostgreSQL řady 8.5:
==Commitfest 8.5/1==
*odstraneni základních prekazek pro psani UDF v C++
*parametrizace vypisu provadeciho planu - umoznuje regresni testy planneru
*COPY WITH CSV FORCE QUOTE *
*fix "could not reattach to shared memory" on Windows
*ruční nastavení statistik sloupce ALTER TABLE ... ALTER COLUMN ... SET DISTINCT
*zobrazeni hodnot klicu, ktere zpusobuji chybu "duplicitni klice"
*vice vlaknovy pgbench
*Deferrable unique constraints 
*strojově rozpoznatelný formát pro výpis prováděcího plánu
<pre>postgres=# explain (format xml) select 10;
                        QUERY PLAN                        
----------------------------------------------------------
 <explain xmlns="http://www.postgresql.org/2009/explain">
   <Query>
     <Plan>
       <Node-Type>Result</Node-Type>
       <Startup-Cost>0.00</Startup-Cost>
       <Total-Cost>0.01</Total-Cost>
       <Plan-Rows>1</Plan-Rows>
       <Plan-Width>0</Plan-Width>
     </Plan>
   </Query>
 </explain>
(1 row)

postgres=# explain (format json) select 10;
          QUERY PLAN          
------------------------------
 [
   {
     "Plan": {
       "Node Type": "Result",
       "Startup Cost": 0.00,
       "Total Cost": 0.01,
       "Plan Rows": 1,
       "Plan Width": 0
     }
   }
 ]
(1 row)
</pre>
*podpora formátu EEEE ve funkci to_char,

==Commitfest 8.5/2==
* podpora UNICODE v PL/Pythonu,
* zobrazení chybového kontextu v PL/Perl-u,
* odstranění OUTER JOINu v případech, kdy vnitřní část spojení (ve výsledku nepoužitého) je dotaz do tabulky podmíněný podmínkou aktivující unikátní index (t. spojení nemá žádný efekt) - cílem je optimalizace primitivně generovaných SQL dotazů:
<pre>
postgres=# explain select x.a from x left join y on x.a = y.a;
                       QUERY PLAN                        
---------------------------------------------------------
 Seq Scan on x  (cost=0.00..1443.00 rows=100000 width=4)
(1 row)
</pre>
* povolen zápis do proměnných odpovídajícím IN parametrům v PL/pgSQL,
* možnost parametrizace příkazu COPY seznamem příznaků oddělených čárkou,
* anonymní PL bloky - možnost použít PL kód bez nutnosti registrace funkce,
<pre>
 DO $$
 DECLARE r record;
 BEGIN 
     FOR r IN SELECT rtrim(roomno) AS roomno, comment FROM Room ORDER BY roomno
     LOOP
         RAISE NOTICE '%, %', r.roomno, r.comment;
     END LOOP;
 END$$ LANGUAGE plpgsql;
 NOTICE:  001, Entrance
 NOTICE:  002, Office
 NOTICE:  003, Office
 NOTICE:  004, Technical
 NOTICE:  101, Office
 NOTICE:  102, Conference
 NOTICE:  103, Restroom
 NOTICE:  104, Technical
 NOTICE:  105, Office
 NOTICE:  106, Office
</pre>
* Vylepšení contrib modulu hstore - modul, který slouží k ukládání, indexaci a vyhledání v seznamech dvojic - klíč, hodnota. Odstraněn 64KB limit, konverze hodnot typu hstore z/do pole, recordu, řádku. Funkce pro iteraci nad seznamem klíčů,
<pre>
postgres=# SELECT * FROM foo;
 a  | b  |   c    
----+----+--------
 10 | 20 | Pavel
 30 | 40 | Zdenek
(2 rows)

postgres=# select (each(hstore(foo))).* from foo;
 key | value  
-----+--------
 a   | 10
 b   | 20
 c   | Pavel
 a   | 30
 b   | 40
 c   | Zdenek
(6 rows)

CREATE OR REPLACE FUNCTION trgfce() 
RETURNS trigger AS $$
DECLARE r record; 
BEGIN 
  FOR r IN SELECT (each(hstore(NEW))).* 
  LOOP 
    RAISE NOTICE 'key:%, value: %', r.key, r.value; 
  END LOOP; 
  RETURN new; 
END
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg BEFORE INSERT ON foo FOR EACH ROW EXECUTE PROCEDURE trgfce();

postgres=# INSERT INTO foo VALUES(80,90,'Zbynek');
NOTICE:  key:a, value: 80
NOTICE:  key:b, value: 90
NOTICE:  key:c, value: Zbynek
INSERT 0 1
</pre>
* [http://archives.postgresql.org/pgsql-committers/2009-10/msg00023.php ''ALTER DEFAULT PRIVILEGES''] - Možnost nastavit výchozí práva pro nově vytvářené objekty databáze, autor - Petr Jelínek,
* Podpora escape zápisu UNICODE znaků \Uxxxx nebo \Uxxxxxxxx (místo x... šestnáctibit nebo třicetdvabit hex číslo),
* Podpora kombinované a jmenné notace volání funkcí:
<pre>
create function dfunc(a int, b int, c int = 0, d int = 0)
  returns table (a int, b int, c int, d int) as $$
  select $1, $2, $3, $4;
$$ language sql;
select (dfunc(10,20,30)).*;
 a  | b  | c  | d 
----+----+----+---
 10 | 20 | 30 | 0
(1 row)

select (dfunc(10 as a, 20 as b, 30 as c)).*;
 a  | b  | c  | d 
----+----+----+---
 10 | 20 | 30 | 0
(1 row)
 
select * from dfunc(10 as a, 20 as b);
 a  | b  | c | d 
----+----+---+---
 10 | 20 | 0 | 0
(1 row)

select * from dfunc(10 as b, 20 as a);
 a  | b  | c | d 
----+----+---+---
 20 | 10 | 0 | 0
(1 row)

select * from dfunc(1,2);
 a | b | c | d 
---+---+---+---
 1 | 2 | 0 | 0
(1 row)

select * from dfunc(1,2,3 as c);
 a | b | c | d 
---+---+---+---
 1 | 2 | 3 | 0
(1 row)
</pre>
* možnost nastavit systémové proměnné databáze per databázi a uživatele:
<pre>
ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
</pre>
* hromadné nastavení práv (autor: Petr jelínek):
<pre>
GRANT/REVOKE ON ALL TABLES/SEQUENCES/FUNCTIONS IN SCHEMA
</pre>
* podpora UTF dekorací (rámečky) v psql
<pre>
postgres=# select * from (values(10,20,30),(40,50,60)) x(a,b,c);
 a  │ b  │ c  
────┼────┼────
 10 │ 20 │ 30
 40 │ 50 │ 60
(2 rows)

postgres=# \pset linestyle ascii
Line style is ascii.
postgres=# select * from (values(10,20,30),(40,50,60)) x(a,b,c);
 a  | b  | c  
----+----+----
 10 | 20 | 30
 40 | 50 | 60
(2 rows)
</pre>
*podpora UPDATE triggrů pro vybrané sloupce,
<pre>
CREATE TRIGGER before_upd_a_stmt_trig BEFORE UPDATE OF a ON main_table
  FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func('before_upd_a_stmt');
</pre>
==Meziobdobí==
*integrace SQL parseru do plpgsql - tento patch umožní identifikovat kolize plpgsql proměnných a SQL identifikátorů,
<pre>
postgres=# 
create or replace function foo(a integer) 
returns void as $$
declare r record; 
begin 
  for r in 
    select a from omega where omega.a = foo.a 
  loop 
    raise notice '%', i; 
  end loop; 
end; 
$$ language plpgsql;
CREATE FUNCTION
Time: 2,082 ms
postgres=# select foo(10);
ERROR:  column reference "a" is ambiguous
LINE 1: select a from omega where omega.a = foo.a
               ^
DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
QUERY:  select a from omega where omega.a = foo.a
CONTEXT:  PL/pgSQL function "foo" line 1 at FOR over SELECT rows
</pre>
*contrib modul unaccent - modul pro ořezávání UTF diakritiky, funkce a fulltext slovník
<pre>
postgres=# \i /usr/local/pgsql/share/contrib/unaccent.sql 
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION
CREATE TEXT SEARCH TEMPLATE
CREATE TEXT SEARCH DICTIONARY
postgres=# \df
                                       List of functions
 Schema │      Name       │ Result data type │          Argument data types          
────────┼─────────────────┼──────────────────┼───────────────────────────────────────
 public │ unaccent        │ text             │ regdictionary, text                   
 public │ unaccent        │ text             │ text                                  
 public │ unaccent_init   │ internal         │ internal                              
 public │ unaccent_lexize │ internal         │ internal, internal, internal, internal
(4 rows)

postgres=# select unaccent('žluťoučký kůň');
   unaccent    
───────────────
 zlutoucky kun
(1 row)
</pre>
==Commitfest 8.5/3==
*hook pro CREATE ROLE, contrib modul passwordcheck pro ověření síly hesla,
*podpora podmíněných triggerů - klauzle WHEN pro příkaz CREATE TRIGGER,
<pre>
CREATE TRIGGER modified_any 
   BEFORE UPDATE OF a 
   ON main_table
 FOR EACH ROW 
  WHEN (OLD.* IS DISTINCT FROM NEW.*) 
  EXECUTE PROCEDURE trigger_func('modified_any');
</pre>
* Použití UTF znaků pro signalizaci pokračování řádku - explicitní přechod na nový řádek
<pre>
                                  a                                  │                                  a                                   
─────────────────────────────────────────────────────────────────────┼──────────────────────────────────────────────────────────────────────
 PostgreSQL je šířen pod BSD licencí, která je nejliberálnější ze vš…│ PostgreSQL je šířen pod BSD licencí, která je nejliberálnější ze vše…
…ech open source licencí. Tato licence umožňuje neomezené bezplatné …│…ch open source licencí. Tato licence umožňuje neomezené bezplatné po…
…používání, modifikaci a distribuci PostgreSQL a to ať pro komerční …│…užívání, modifikaci a distribuci PostgreSQL a to ať pro komerční neb…
…nebo nekomerční využití. PostgreSQL můžete šířit se zdrojovými kódy…│…o nekomerční využití. PostgreSQL můžete šířit se zdrojovými kódy neb…
… nebo bez nich, zdarma nebo komerčně.                              ↵│…o bez nich, zdarma nebo komerčně.                                   ↵
 PostgreSQL umožňuje běh uložených procedur napsaných v několika pro…│ PostgreSQL umožňuje běh uložených procedur napsaných v několika prog…
…gramovacích jazycích, v Perlu, v Pyhon, v jazyku C nebo v speciální…│…ramovacích jazycích, v Perlu, v Pyhon, v jazyku C nebo v speciálním …
…m PL/pgSQL, jazyku vycházejícím z PL/SQL fy. Oracle. Existují Postg…│…PL/pgSQL, jazyku vycházejícím z PL/SQL fy. Oracle. Existují PostgreS…
…reSQL varianty JDBC, ODBC, dbExpress, Open Office, PHP, .NET, Perl …│…QL varianty JDBC, ODBC, dbExpress, Open Office, PHP, .NET, Perl nati…
…nativních rozhraní. K PostgreSQL existuje překladač Embedded SQL pr…│…vních rozhraní. K PostgreSQL existuje překladač Embedded SQL pro C a…
…o C a C++. Dále existuje experimentální podpora SQL/PSM - standardi…│… C++. Dále existuje experimentální podpora SQL/PSM - standardizované…
…zovaného jazyka pro návrh uložených procedur v ANSI SQL.            │…ho jazyka pro návrh uložených procedur v ANSI SQL.
(1 row)
</pre>
*ecpg:podpora dynamických kurzorů
<pre>
 strcpy(msg, "prepare");
 exec sql prepare st_id1 from :stmt1;

 strcpy(msg, "declare");
 exec sql declare :curname3 cursor for st_id1;

 strcpy(msg, "open");
 exec sql open :curname3;

 strcpy(msg, "fetch from");
 exec sql fetch from :curname3 into :id, :t;
 printf("%d %s\n", id, t);

 strcpy(msg, "fetch");
 exec sql fetch :curname3 into :id, :t;
 printf("%d %s\n", id, t);

 strcpy(msg, "fetch 1 from");
 exec sql fetch 1 from :curname3 into :id, :t;
 printf("%d %s\n", id, t);
</pre>
*anonymní procedurální blok v PLPerlu
<pre>
DO $$
  $a = 'This is a test';
  elog(NOTICE, $a);
$$ LANGUAGE plperl;
</pre>
*nový parametr pro CONNECT - application_name (APPNAME) - další dodatečná informace, kterou lze použít při monitorování provozu,
*opravy REGEXP pro UTF8 (case insensitive),
<pre>
-- pred opravou
postgres=# SELECT 'žluťoučký' ~* '.Luť.*';
 ?column? 
──────────
 t
(1 row)

postgres=# SELECT 'žluťoučký' ~* 'ŽLuť.*';
 ?column? 
──────────
 f
(1 row)

-- po oprave
postgres=# SELECT 'žluťoučký' ~* 'ŽLuť.*';
 ?column? 
----------
 t
(1 row)
</pre>
*knihovny klienta jsou překládány s podporou thread-safety jako default,
*podpora obecných vylučujících omezujících pravidel - zobecnění pravidla UNIQUE,
<pre>
CREATE TABLE circles (
    c circle,
    EXCLUDE USING gist (c WITH &&)
);

postgres=# create table omega(a integer, exclude (a with =));
NOTICE:  CREATE TABLE / EXCLUDE will create implicit index "omega_a_exclusion" for table "omega"
CREATE TABLE
Time: 56,056 ms
postgres=# insert into omega values(10);
INSERT 0 1
Time: 1,456 ms
postgres=# insert into omega values(10);
ERROR:  conflicting key value violates exclusion constraint "omega_a_exclusion"
DETAIL:  Key (a)=(10) conflicts with existing key (a)=(10).
</pre>
*výpis prováděcího plánu ve formátu YAML,
*možnost nastavení přístopových práv k BLOB objektům:
<pre>
SELECT lo_create(1001);
 lo_create 
-----------
      1001
(1 row)

GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC;
GRANT SELECT ON LARGE OBJECT 1003 TO regressuser2;
GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regressuser2;
GRANT ALL ON LARGE OBJECT 1005 TO regressuser2;
GRANT SELECT ON LARGE OBJECT 1005 TO regressuser2 WITH GRANT OPTION;
</pre>
*podpora polí pro PLPythonu
<pre>
CREATE FUNCTION test_type_conversion_array_int4(x int4[]) RETURNS int4[] AS $$
plpy.info(x, type(x))
return x
$$ LANGUAGE plpythonu;
SELECT * FROM test_type_conversion_array_int4(ARRAY[0, 100]);
INFO:  ([0, 100], <type 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {0,100}
(1 row)

SELECT * FROM test_type_conversion_array_int4(ARRAY[0,-100,55]);
INFO:  ([0, -100, 55], <type 'list'>)
CONTEXT:  PL/Python function "test_type_conversion_array_int4"
 test_type_conversion_array_int4 
---------------------------------
 {0,-100,55}
(1 row)
</pre>
* EXPLAIN ANALYZE (BUFFERS) - výpis prováděcího plánu zobrazuje využití cache,
<pre>
postgres=# explain (analyze, buffers) select * from foo where a = 10;
                                         QUERY PLAN                                          
---------------------------------------------------------------------------------------------
 Seq Scan on foo  (cost=0.00..2.25 rows=1 width=4) (actual time=0.060..0.060 rows=0 loops=1)
   Filter: (a = 10)
   Buffers: shared hit=1
 Total runtime: 0.105 ms
(4 rows)
</pre>
* explicitní určení pořadí záznamů v agregační funkci:
<pre>
postgres=# select array_agg(distinct a order by a desc), array_agg(a order by a) from foo;
-[ RECORD 1 ]------------------------------------------------
array_agg | {9652,8078,7671,7642,5048,4650,3886,2450,732,647}
array_agg | {647,732,2450,3886,4650,5048,7642,7671,8078,9652}
</pre>
*Bruce commitl zásadní patch - PL/pgSQL je dostupný bez dodatečné instalace - zapomeňte na CREATE LANGUAGE plpgsql,
*Hot Standby - počáteční patch povolující read only spojení do db během obnovy db (recovery) - jedná se o první formu vestevěné replikace do pg (pravděpodobně asynchronní master-slave), autorem je Simon Riggs,
==Meziobdobí==
*Možnost nezadávát název indexu a nechat systém vygenerovat unikátní normalizovaný název:
<pre>
-- nazev indexu neuveden - system pouzije concur_heap_expr_idx
CREATE INDEX CONCURRENTLY on concur_heap((f2||f1));
</pre>
*Zahrnutí statistik potomků do statistik rodiče,
*Parametry seq_page_cost a random_page_cost pro jednotlivé tablespaces,
*Modifikované VACUUM FULL - lépe řeší díry po velkých blokových operacích,
*Podpora 64bit na MS Windows,
==PostgreSQL 9.0/4==
*Podpora SQLDA v ecpg
*Původní full vacuum bylo úplně odstraněno
*Podpora DESCRIBE v ecpg
*Refaktoring plper
*Informace o vuyžití bufferů v tabulce pg_stat_statements
*Streaming replication
*out of scope cursors for ecpg
*escapované proměnné v psql