September 9th
With every new release, we get performance enhancements that do not require changes to your application. These are changes under the hood that you get
"for free"
$ 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.
speeds lock release at statement completion in transactions that hold many locks
(pg_dump)
(complex queries)
info now stored:
Constraint Violations Now Report Separately
CREATE MATERIALIZED VIEW
REFRESH MATERIALIZED VIEW
JSON: { "rush":[2,4,6,9] }
SELECT '{"rush":[2,4,6,9]}'::json -> 'rush' -> 2
returns:: 6
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
----------------
name
game
score
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]
SELECT array_remove('{rick,phil,keith}'::text[],'phil');
returns: {rick,keith}
SELECT array_replace('{rick,phil,keith}'::text[],'phil','payal');
returns: {rick,payal,keith}
#syntacticsugar
NOTE:
\watch SELECT now();
defaults to every 1 second
SELECT now(); \watch 5
runs every n seconds
allows the use of database name patterns in \l
pagila=# \l template*
List of databases
Name | Owner | Encoding | Collate | Ctype |
-----------+----------+----------+-------------+-------------+
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(2 rows)
SELECT
c.relname, p.increment
FROM
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, rentals
FROM
customer c,
LATERAL(select count(distinct(rental_date)) as rentals
from rental where customer_id = c.customer_id) as x
WHERE
store_id = 1
ORDER 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
CREATE OR REPLACE FUNCTION snitch( )
RETURNS event_trigger
AS $$
BEGIN
RAISE NOTICE 'snitch: % %' , tg_event, tg_tag;
END;
$$ LANGUAGE plpgsql;
CREATE EVENT TRIGGER snitch ON ddl_command_start EXECUTE PROCEDURE snitch();
http://www.depesz.com/tag/pg93/
http://michael.otacoo.com/tag/9-3/
http://wiki.postgresql.org/wiki/What%27s_new_in_PostgreSQL_9.3
http://wiki.postgresql.org/wiki/PostgreSQL_9.3_Blog_Posts