Postgres 9.3

• Branch 9.2 ==> 2012-06-01
• Commit Fest 1 --> June 2012
• Commit Fest 2 --> Sept 2012
• Commit Fest 3 --> Nov 2012
• Commit Fest 4 --> Jan 2013

• 1901 Files Changed
• 69730 Additions
• 187838 Deletions

• May => 0%
• June => 3%
• July => 21%
• August => 27%
• September => 34%
• October => 14%

 $ 2013-04-28 12:00:33 EDT [6429]: [1-1] FATAL:  XX000: could not 
create shared memory segment: Cannot allocate memory
2012-04-28 12:00:33 EDT [6429]: [2-1] DETAIL:  Failed system call was shmget(key=5432001, size=7700914176, 03600).
2012-04-28 12:00:33 EDT [6429]: [3-1] HINT:  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space. To reduce the request size (currently 7700914176 bytes), reduce PostgreSQL's shared_buffers parameter (currently 917504) and/or its max_connections parameter (currently 503).
         The PostgreSQL documentation contains more information about shared memory configuration. 

• move from sysv to mmap 

• no more shmmax tom-foolery!

• UPDATE to parent required "for update" row lock
• UPDATE to child required "for share" row lock

• Performance
• Ease of Use
• Enhance Functionality

• Improve performance of streaming log shipping w/ synchronous commit

• Allow much faster promotion of streaming standby to primary  

• pg_is_in_backup()
• pg_backup_start_time()

• Add checkpoint's last REDO location to pg_controldata output (used to determine WAL files needed for restore)

• "pg_basebackup --write-recovery-conf" outputs a minimal recovery.conf
  

• MALLEABLE
  
• UPDATEABLE
  
• MATERIALIZEABLE
  

• "always" been possible using
• rules for insert/delete/update
• triggers (9.1+)


• no longer needed for simple views
• simple == SQL92 definition


• can still create your own if you want (or need)

• combines rule definition of view (for updating)
• with heap properties of a table (used by planner)

• increase maximum length of large objects from 2GB to 4TB
• includes new libpq and server-side 64-bit-enabled functions
  

 JSON: { "rush":[2,4,6,9] }

• "->", extract data from JSON

SELECT '{"rush":[2,4,6,9]}'::json -> 'rush' -> 2   returns:: 6

• "#>", extract chained data from JSON

SELECT '{"rush":[2,4,6,9]}'::json #> ARRAY['rush','2']   returns:: 6

SELECT * FROM json_object_keys(    '{"name":"dylan","game":"put put","score":[2,1,1,2]}' ); 
json_object_keys----------------namegamescore

 SELECT * FROM json_each_text(    '{"name":"dylan","game":"put put","score":[2,1,1,2]}' );
  key  | value-------+------- name  | dylan game  | put put score | [2,1,1,2]

• json_array_elements
• json_array_length
• json_each
• json_extract_path
• json_populate_recordset
• json_agg
• to_json
• hstore_to_json
• hstore_to_json_loose

 SELECT array_remove('{rick,phil,keith}'::text[],'phil');     

 returns: {rick,keith}

 SELECT array_replace('{rick,phil,keith}'::text[],'phil','payal');
 returns: {rick,payal,keith}

• csv
• oracle
• mysql
• redis
• mongodb
• odbc

• access remote postgres databases
• supports remote cost estimates
• basic qual pushdown support
• reference implementation
• replaces dblink (but still there)

• ability to write to foreign tables
• insert/update/delete
• transaction aware (!)
• speed can be an issue

• new commands
• new options
• performance improvements

            \watch SELECT now();
        

             SELECT now(); \watch 5
       

• Attaching SELECT in FROM to sub-SELECT in FROM

SELECT     c.relname, p.incrementFROM    pg_class c,    pg_sequence_parameters(c.oid) p WHERE    c.relkind = 'S' limit 3

         relname          | increment 
--------------------------+-----------
 actor_actor_id_seq       |         1
 category_category_id_seq |         1
 film_film_id_seq         |         1

SELECT    customer_id, rentalsFROM    customer c,    LATERAL(select count(distinct(rental_date)) as rentals             from rental where customer_id = c.customer_id) as xWHERE    store_id = 1ORDER BY rentals desc LIMIT 5
 first_name | last_name | rentals 
------------+-----------+---------
 ELEANOR    | HUNT      |      46
 MARCIA     | DEAN      |      42
 CLARA      | SHAW      |      42
 TIM        | CARY      |      39
 DAISY      | BATES     |      38

• postmaster subprocesses
• run arbitrary C code
• access shared memory
• use backend database connections
• includes sample implementation

• parallel pg_dump
• parallel pg_upgrade
• pg_xlogdump 
  
• COPY FREEZE
• page level checksums
• index based regex searches