New in PostgreSQL 9.3: Server Side languages

In series of blogging about new features coming in PostgreSQL 9.3, I thought to blog about the server side language improvements in PostgreSQL. Lets see whats coming in server side language.

As PostgreSQL user, you know, PostgreSQL supports multiple server side language. In 9.3, there are some interesting features are coming. Lets look at the new improvements about to come.

1. SPI access to number of rows processed by COPY command.

This is more like new feature which is introduced in 9.3. Before 9.3, this feature was missing. i.e if user uses COPY command inside the plperl/plpython functions, then there was no way inside the procedure to trace the number of rows processed by COPY. However in 9.3, this limitation is no more exists with procedural language. There are many languages supported in PostgreSQL, However I chose to test this with mostly used language plperl and plpython. Below are some snapshot pre-9.3 and in 9.3.

Lets check with plperl. Following is a plperl function which can be use:

Definition of table is given below:

   Table "public.test_copy"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | numeric | 

Content of data file:

cat /tmp/test.data
1
2
3
4
5

Following is a plperl function which can be use for testing in pre-9.3 and in 9.3

CREATE OR REPLACE FUNCTION test_copy() RETURNS integer
AS $$
    my $rv = spi_exec_query("COPY test_copy FROM '/tmp/test.data'");
    my $status = $rv->{status};
    my $nrows = $rv->{processed};
    return $nrows;
$$ LANGUAGE plperl;

If we execute COPY command on psql prompt, user will get message like given below:

worktest=# COPY test_copy from '/tmp/test.data';
COPY 5

which shows COPY has processed 5 rows and accordingly loaded in table.

If we use above plperl function in pre-9.3, user will get following result:

worktest=# select split_part(version(),' ',2) as version;
 version  
----------
 9.2.4.10
(1 row)

worktest=# select test_copy();
 test_copy 
-----------
         0
(1 row)

which shows function was not able to get the number of rows processed by COPY command.
However if we use same plperl function in 9.3, we will get following result:

postgres=# select split_part(version(),' ',2) as version;
 version  
----------
 9.3beta2
(1 row)

postgres=# select test_copy();
 test_copy 
-----------
         5
(1 row)

which shows that plperl function in 9.3 is able to get the number of rows processed.

Similarly we can use following plpython function to test this new feature:

CREATE FUNCTION result_copy_test(cmd text) RETURNS int
AS $$
plan = plpy.prepare(cmd)
plpy.info(plan.status()) 
result = plpy.execute(plan)
return result.nrows()
$$ LANGUAGE plpythonu;

pre-9.3

worktest=#  SELECT result_copy_test($$ COPY test_copy FROM '/tmp/test.data' $$);
INFO:  True
CONTEXT:  PL/Python function "result_copy_test"
 result_copy_test 
------------------
                0
(1 row)

which shows function was not able to get the processed rows by COPY command.

In 9.3

postgres=# SELECT result_copy_test($$ COPY test_copy FROM '/tmp/test.data' $$);
INFO:  True
CONTEXT:  PL/Python function "result_copy_test"
 result_copy_test 
------------------
                5
(1 row)

which shows function was able to get the processed rows of COPY command.

2. Allow GET DIAGNOSTICS x = ROW_COUNT to access rows processed by COPY
This is an enhancement in plpgsql, if user wants to access the rows processed by COPY command in plpgsql Block, then he can use
GET DIAGNOSTICS variable = ROW_COUNT, which was not possible before 9.3.

To check with plpgsql, user can use either create a sample function or can use following anonymous block to verify this feature. I will be using DO block of plpgsql to verify this feature.
Example is given below:
PL/pgSQL anonymous block

DO $$
  DECLARE
    r int; 
  BEGIN
     COPY test_copy FROM '/tmp/test.data';
     GET DIAGNOSTICS r = row_count;
     RAISE NOTICE 'processed rows => %',r;
  END;
$$ language plpgsql;

pre-9.3

worktest=# DO $$
worktest$#   DECLARE
worktest$#     r int; 
worktest$#   BEGIN
worktest$#      COPY test_copy FROM '/tmp/test.data';
worktest$#      GET DIAGNOSTICS r = row_count;
worktest$#      RAISE NOTICE 'processed rows => %',r;
worktest$#   END;
worktest$# $$ language plpgsql;
NOTICE:  processed rows => 0
DO

which shows pre-9.3 was not able to get processed rows.

In 9.3

postgres=# DO $$
postgres$#   DECLARE
postgres$#     r int; 
postgres$#   BEGIN
postgres$#      COPY test_copy FROM '/tmp/test.data';
postgres$#      GET DIAGNOSTICS r = row_count;
postgres$#      RAISE NOTICE 'processed rows => %',r;
postgres$#   END;
postgres$# $$ language plpgsql;
NOTICE:  processed rows => 5
DO
postgres=

In 9.3, plpgsql is able to get processed rows. Interesting.

3. Allow use of RETURN with a composite expression in PL/pgSQL.
This is new addition in PL/pgSQL and also useful for user who is reluctant to declare variable too much and wants RETURN in PL/pgSQL to return the expression.

This feature was not available pre-9.3 PL/pgSQL. However, in 9.3, user can use this easily. Below is an example.

create type footype as (x int, y varchar);

create or replace function foo() returns footype as $$
begin
  return (1, 'hello')::footype;
end;
$$ language plpgsql;

Lets try with pre-9.3 first ,
when user will try to create above function user will get following error message in pre-9.3:

worktest=# create or replace function foo() returns footype as $$
worktest$# begin
worktest$#   return (1, 'hello')::footype;
worktest$# end;
worktest$# $$ language plpgsql;
ERROR:  RETURN must specify a record or row variable in function returning row
LINE 3:   return (1, 'hello')::footype;

In 9.3, due to addition of above feature, this works great.

postgres=# create or replace function foo() returns footype as $$
postgres$# begin
postgres$#   return (1, 'hello')::footype;
postgres$# end;
postgres$# $$ language plpgsql;
CREATE FUNCTION
postgres=# 

And user can see the working output too:

postgres=# select foo();
    foo    
-----------
 (1,hello)
(1 row)

4. New in PL/Python
There are some good improvement coming in plpython too. These improvement will be useful for users who like to make plpython and wants to debug the function or wants to print some useful information. This was missing in pre-9.3. Lets look at whats new in plptyhon.

i. Addition of object string in Pl/Python
Before 9.3, whenever user wants to look at the information provided by object handler in Pl/Python, it doesn’t used to give useful information. However, In 9.3, this is going to give some useful information which will be helpful for Pl/Python users.

Lets look at example, how this change is useful. Following is small code which can be use for testing this new addition:

CREATE FUNCTION test_debug_info() RETURNS text AS $$
    try:
        rv=plpy.execute("SELECT datname FROM pg_catalog.pg_database",5)
        plpy.info(rv);
    except plpy.SPIError:
        return "Not working"
    else:
        return "Working good"
$$ LANGUAGE plpythonu;

pre 9.3

worktest=# select test_debug_info() ;
INFO:  <PLyResult object at 0x7f3594b8f270>
CONTEXT:  PL/Python function "test_debug_info"
 test_debug_info 
-----------------
 Working good
(1 row)

Above user can see INFO hasn’t given the very much useful information.

In 9.3

postgres=# select test_debug_info() ;
INFO:  <PLyResult status=5 nrows=3 rows=[{'datname': 'template1'}, {'datname': 'template0'}, {'datname': 'postgres'}]>
CONTEXT:  PL/Python function "test_debug_info"
 test_debug_info 
-----------------
 Working good
(1 row)

Above you can see INFO has given some useful information about status, number rows and rows return by the query. Very useful, if somebody wants to know what handler contains in Pl/Python.

ii. Conversion of OID values to proper Pl/Python numeric type
Before 9.3, Database type OID used to be treated as string in Pl/Python, which makes developers to convert string into number data type in plptyhon and do some processing on it, which is kind of extra coding. However in 9.3 they don’t have to worry any more.
Below is an example to test this feature.

CREATE FUNCTION test_type_conversion_oid(x oid) RETURNS oid AS $$
   plpy.info(x, type(x))
   return x +1
$$ LANGUAGE plpythonu;

Lets look at the pre 9.3

worktest=# select test_type_conversion_oid(123456);
INFO:  ('123456', <type 'str'>)
CONTEXT:  PL/Python function "test_type_conversion_oid"
ERROR:  TypeError: cannot concatenate 'str' and 'int' objects
CONTEXT:  Traceback (most recent call last):
  PL/Python function "test_type_conversion_oid", line 3, in <module>
    return x +1
PL/Python function "test_type_conversion_oid"

which shows direct number operations is not possible with OID in pre9.3, since OID in pre 9.3 is treated as string.

In 9.3

postgres=# select test_type_conversion_oid(123456)
postgres-# ;
INFO:  (123456L, <type 'long'>)
CONTEXT:  PL/Python function "test_type_conversion_oid"
 test_type_conversion_oid 
--------------------------
                   123457
(1 row)

This works great and direct number operation with OID is possible. A good addition in plpython.

iii. Handle SPI errors raised explicitly (with PL/Python’s RAISE) the same as internal SPI errors
This is new addition to plpython. Now in plpython function’s body, user can raise SPIError/exceptions using “raise” statement of plpython, which was missing in pre 9.3. Now in 9.3, user will be able to use “raise” statement to raise exceptions. Addition to this, if user sets the sqlstate attribute, plpython preserver that change.
Below is an example:

Following is definition of function which can be use for testing this addition in pre 9.3 and in 9.3

CREATE OR REPLACE FUNCTION plpy_raise_spiexception() RETURNS void AS $$
   raise plpy.spiexceptions.DivisionByZero()
$$ LANGUAGE plpythonu;

Lets use anonymous block of plpgsql to test how raise exception can be handle.
Before 9.3

DO $$
BEGIN
   SELECT plpy_raise_spiexception();
EXCEPTION WHEN division_by_zero THEN
   RAISE NOTICE 'Found Exception';
END
$$ LANGUAGE plpgsql;

ERROR:  spiexceptions.DivisionByZero: 
CONTEXT:  Traceback (most recent call last):
  PL/Python function "plpy_raise_spiexception", line 2, in <module>
    raise plpy.spiexceptions.DivisionByZero()
PL/Python function "plpy_raise_spiexception"
SQL statement "SELECT plpy_raise_spiexception()"
PL/pgSQL function inline_code_block line 3 at SQL statement

As you can see Before 9.3, user will raise statement was not working properly for raising exception in plpython.

In 9.3

postgres=# DO $$
postgres$# BEGIN
postgres$#    SELECT plpy_raise_spiexception();
postgres$# EXCEPTION WHEN division_by_zero THEN
postgres$#    RAISE NOTICE 'Found Exception';
postgres$# END
postgres$# $$ LANGUAGE plpgsql;
NOTICE:  Found Exception
DO

As you can see in 9.3. it works great!

New in PostgreSQL 9.3: Client Application improvements

Long long time. I haven’t blogged at all. So, I have decided whenever I get chance I will blog about the cool things going in Database Technology market. I have lot of topics to share. Some got lost in air. Some I retained in mind. Before I lost more. I decided to continue from PostgreSQL 9.3 features and accordingly will go towards about new things as it starts to popup.

PostgreSQL users must have heard about release of PostgreSQL 9.3 and they must have started testing PostgreSQL 9.3. In today series, I am going to blog about client application improvements done in PostgreSQL 9.3. Lets take it one by one.

1. New binary/command pg_isready.

PostgreSQL 9.3 is coming with new binary called pg_isready. this command helps user to know the status of server/PostgreSQL cluster. Before 9.3, user used to have their own tool/script to check the status of PostgreSQL, for that they used execute some random SQL like “SELECT 1” and if the status is successful, PostgreSQL is running or if status is unsuccessful, then PostgreSQL 9.3.
However, pg_isready utilit/command is much better than old methods in the sense it covers following scenario for checking the status of PostgreSQL 9.3:

   a. Check if PostgreSQL is ready and accepting connections.
   b. Check if PostgreSQL is ready and connection is acception/rejecting.
   c. Check if PostgreSQL is non-responsive.
   d. it provide exit code and also has option being quiet about message, which be useful for users using exit code which create their own health check and messaging in application.

different exit codes and Message use by pg_isready to tell the status of PostgreSQL is given below:

1. exit code 0,
   Message: "host:port - accepting connections"
2. exit code: 1
   Message: "host:port - rejecting connections"
3. exit code: 2
   Message: "host:port - no response"

usage example is given below:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_isready -h localhost
localhost:5445 - accepting connections

2. –table (multiple table option) in pg_restore, clusterdb, reindexdb and vacuumdb

–table (multiple table option) was missing in pre-9.3 PostgreSQL. I personally missed an option for specifying multiple tables in single command.
Now user can use multiple table option with following commands in PostgreSQL 9.3 onwards. Example is given below:

Till 9.2, when user uses command like

pg_restore -t emp -t dept test.dmp

User will get information of table which is last table mention in command, as given below:

[root@ip-10-159-51-181 ~]# pg_restore --version
pg_restore (EnterpriseDB) 9.2.4.10
[root@ip-10-159-51-181 ~]# pg_restore -Fc -t emp -t dept test.dmp
--
-- EnterpriseDB database dump
--

SET statement_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog, sys;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: dept; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace:
--

CREATE TABLE dept (
    deptno numeric(2,0) NOT NULL,
    dname character varying(14),
    loc character varying(13)
);

ALTER TABLE public.dept OWNER TO enterprisedb;

--
-- EnterpriseDB database dump complete
--

You can see in pre-9.3, pg_restore has shown the result of last table in list in pg_restore command.

Now in 9.3,  user can mention multiple table names and will be able to listed all tables in command:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_restore  -Fc -t emp -t dept test.dmp
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'SQL_ASCII';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: dept; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace:
--

CREATE TABLE dept (
    deptno numeric(2,0) NOT NULL,
    dname character varying(14),
    loc character varying(13)
);

ALTER TABLE public.dept OWNER TO enterprisedb;

--
-- Name: emp; Type: TABLE; Schema: public; Owner: enterprisedb; Tablespace:
--

CREATE TABLE emp (
    empno numeric(4,0) NOT NULL,
    ename character varying(10),
    job character varying(9),
    mgr numeric(4,0),
    hiredate timestamp without time zone,
    sal numeric(7,2),
    comm numeric(7,2),
    deptno numeric(2,0),
    CONSTRAINT emp_sal_ck CHECK ((sal > (0)::numeric))
);

ALTER TABLE public.emp OWNER TO enterprisedb;

--
-- PostgreSQL database dump complete
--

similarly this option is enable for reindexdb. Example is given below:

Before 9.3:

[root@ip-10-159-51-181 ~]# reindexdb --version
reindexdb (EnterpriseDB) 9.2.4.10
[root@ip-10-159-51-181 ~]# reindexdb -t emp -t dept -e
REINDEX TABLE dept;

In 9.3:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/reindexdb  -t emp -t dept -e
REINDEX TABLE emp;

REINDEX TABLE dept;

User can observer usage of this switch for clusterdb and vacuumdb too.

Addition of this, it has reduced lot of effort for defining script which can recursively call the reindexdb/vacuumdb/clusterdb for each tables.

9.3 gives option to user for using these binaries more efficiently in their environment and tools.

3. –dbname/-d option for binaries: pg_dumpall, pg_basebackup and pg_receivexlog.
Before 9.3, –dbname and -d option was available in psql, clusterdb, reindexdb command etc.

However this option wasn’t available for command like pg_dumpall, pg_basebackup and pg_receivexlog, which made users to define connection string for this binaries differently from other binaries. For example:

Before 9.3:

[root@ip-10-159-51-181 ~]# pg_basebackup --version
pg_basebackup (PostgreSQL) 9.2.4.10
[root@ip-10-159-51-181 ~]# pg_basebackup --help|grep dbname

In 9.3, user can see this option available:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/pg_basebackup --help|grep -i dbname
  -d, --dbname=CONNSTR   connection string

4. Remove warning message for psql, when psql uses to connect to old server.
Example is given below:
Before 9.3:

User used to get warning message something like given below:

[root@ip-10-159-51-181 ~]# psql -p 5444
psql (9.2.4.10, server 9.1.9.17)
WARNING: psql version 9.2, server version 9.1.
         Some psql features might not work.
Type "help" for help.

Now 9.3 onwards:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql  -p5444
psql (9.3beta2, server 9.1.9.17)
Type "help" for help.

worktest=#

Note:: Warning message is kept if user uses psql to connect to higher major version of postgresql.

5. psql –single-transaction mode for STDIN:
Before 9.3, –single-transaction mode doesn’t work for STDIN. For example if you have a set of commands which you want to pass to psql through STDIN, then it doesn’t used to work. To make it work, people used to put the commands in a file and then they used to use the –single-transaction mode. However 9.3 onwards, it won’t require any more. Example is given below:

Before 9.3:

[root@ip-10-159-51-181 ~]# psql --version
psql (EnterpriseDB) 9.2.4.10

[root@ip-10-159-51-181 ~]# echo "
CREATE TABLE (id numeric);
CREATE TABLE a;
CREATE DATABASE test;"|psql --single-transaction
ERROR:  syntax error at or near "("
LINE 1: CREATE TABLE (id numeric);
                     ^
ERROR:  syntax error at or near ";"
LINE 1: CREATE TABLE a;
                      ^
CREATE DATABASE

Above example shows that, there was error on 1st and 2nd command, however psql executed third command successfully.

worktest=# select datname from pg_database where datname='test';
 datname
---------
 test
(1 row)

9.3 on wards:

[root@ip-10-159-51-181 ~]# echo "
CREATE TABLE (id numeric);
CREATE TABLE a;
CREATE DATABASE test;"|/usr/pgsql-9.3/bin/psql --single-transaction
ERROR:  syntax error at or near "("
LINE 1: CREATE TABLE (id numeric);
                     ^
ERROR:  syntax error at or near ";"
LINE 1: CREATE TABLE a;
                      ^
ERROR:  current transaction is aborted, commands ignored until end of transaction block
[root@ip-10-159-51-181 ~]#

You can see transaction was aborted when user uses the STDIN with psql –single-transaction.

6. Other improvements in psql.

In 9.3, psql has further improved for tab completion and patter searching. This has been done by tuning of functions cost settings.

7. New addition in Backslash commands:

9.3 also coming with some new backslash commands. Those are going to help in lot ways for user to monitor and using psql in efficient way in there script/monitoring.
Following are new in backslash commands:

i. \watch.

Similar to Linux system watch command, psql is also coming with \watch command. Using this command user can execute current buffer query repeatedly. For example, if user wants to monitor/watch the sessions made to postgresql database, then he/she can use command something like given below in psql to do that:

a. Start a psql session as given below:

           [root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -p 5445 -U enterprisedb -d worktest
psql (9.3beta2, server 9.2.4.10)
Type "help" for help.

worktest=#

b. Now execute a query which you want to use repeatedly as given below

        worktest=# select datname, usename, application_name, query from pg_stat_activity ;
 datname  |   usename    | application_name |                                  query

----------+--------------+------------------+-------------------------------------------------------
-------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from
pg_stat_activity ;
(1 row)

c. Now use command “\watch [seconds]”

worktest=# \watch 1
                                       Watch every 1s	Sat Jul 27 04:15:21 2013

 datname  |   usename    | application_name |                                  query
----------+--------------+------------------+--------------------------------------------------------------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)

                                       Watch every 1s	Sat Jul 27 04:15:22 2013

 datname  |   usename    | application_name |                                  query
----------+--------------+------------------+--------------------------------------------------------------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)

Or user can combine above two in one command to use it something like given below:

worktest=# select datname, usename, application_name, query from pg_stat_activity \watch 1
 datname  |   usename    | application_name |                                  query

----------+--------------+------------------+-------------------------------------------------------
-------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from
pg_stat_activity ;
(1 row)

                                       Watch every 1s	Sat Jul 27 04:36:00 2013

 datname  |   usename    | application_name |                                  query
----------+--------------+------------------+--------------------------------------------------------------------------
 worktest | enterprisedb | psql             | select datname, usename, application_name, query from pg_stat_activity ;
(1 row)

In above, I wanted to use query to monitor the pg_stat_statement every seconds.

\watch commands interrupted/stop in following condition:

if user has interrupted the running watch.
if query fails.

ii. \gset command to store query results in psql variables.

New command is added in backslash. This is very interesting command and user can use it in multiple purpose.

This backslash command allows users to set value of a variable based on single row/output of SQL. If SQL returns multiple output, then \gset will send proper message and will not set variable.

There are multiple normal example of using this backslash, for example you can store the output of now query in a variable and can use in psql session as given below:

worktest=# select now() as timestamp
worktest-# \gset
worktest=# \echo :timestamp
27-JUL-13 04:53:13.214203 -04:00
worktest=#

Some, user like me, can also use this command to prepare dynamic commands and can execute in session. For example I have users like test which I want to drop from PG instance, so, I can do something like given below:

worktest=# SELECT replace(array_to_string(ARRAY(SELECT 'DROP USER '||usename||';' FROM pg_user where usename ~ 'test'),', '),',','') as drop_test_user
worktest-# \gset
worktest=# \echo "Verify the Drop test user command"
"Verify the Drop test user command"
worktest=# \echo :drop_test_user
DROP USER test; DROP USER test_su;
worktest=# :drop_test_user
DROP ROLE
DROP ROLE

iii. improvement in \conninfo to show ssl information
Before 9.3, backslash command conninfo used to show information about user,database, port and host. Even if user is using ssl connection. However psql in 9.3 will give ssl information too. Example is given below:
Before 9.3

edb=> \conninfo
You are connected to database "edb" as user "vibhor" on host "localhost" at port "5444".

In 9.3:

edb=> \conninfo
You are connected to database "edb" as user "vibhor" on host "localhost" at port "5444".
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

iv. database name patter support in \l

Backslash \l command people use to list the database in psql. However, before 9.3, this command didn’t have the pattern search support. Now, in 9.3, this command has been improved in terms of displaying/listing the database based on pattern provided by user. Snapshot is given below:

Before 9.3:

edb=> \l pe*
                                        List of databases
   Name    |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges
-----------+--------------+----------+-------------+-------------+-------------------------------
 edb       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 pem       | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | enterprisedb=CTc/enterprisedb+
           |              |          |             |             | pem_user=Tc/enterprisedb     +
           |              |          |             |             | pem_agent=Tc/enterprisedb
 postgres  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb              +
           |              |          |             |             | enterprisedb=CTc/enterprisedb
 template1 | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/enterprisedb              +
           |              |          |             |             | enterprisedb=CTc/enterprisedb
(5 rows)

\l: extra argument "pe*" ignored

As you can see it has ignored the pattern based listing and listed all database.

Now in 9.3:

edb=> \l pe*
                                     List of databases
 Name |    Owner     | Encoding |   Collate   |    Ctype    |       Access privileges
------+--------------+----------+-------------+-------------+-------------------------------
 pem  | enterprisedb | UTF8     | en_US.UTF-8 | en_US.UTF-8 | enterprisedb=CTc/enterprisedb+
      |              |          |             |             | pem_user=Tc/enterprisedb     +
      |              |          |             |             | pem_agent=Tc/enterprisedb
(1 row)

Thats really a good enhancement in backslash command.

iv. Fix in “\g filename”, which was affecting subsequent commands after an error.

Before 9.3, if “\g filename” commands fails, then it was also affecting the execution of query after this backslash command. For example, if user executes query something like given below, then subsequent query which output user doesn’t want to store in a file, will go in the filename mentioned in first command:

       worktest=# select 'a'/1 \g /tmp/test
ERROR:  invalid input syntax for integer: "a"
LINE 1: select 'a'/1
               ^
worktest=# select 1/2;
worktest=#

As you can see 1st SQL failed. However second SQL output not displayed on screen and it went in /tmp/test file. which is inconsistent. Now in 9.3 this has been fixed:

worktest=# select 'a'/1 \g /tmp/test
ERROR:  invalid input syntax for integer: "a"
LINE 1: select 'a'/1
               ^
worktest=# select 1/2;
        ?column?
------------------------
 0.50000000000000000000
(1 row)

v. Improvement in \df+ command to show Security label.

If user creates following function with security definer

CREATE FUNCTION test_func()
RETURNS integer
SECURITY DEFINER
AS
$$ SELECT 1;
$$ language sql;

before 9.3,  \df+ command never used to give information on SECURITY label. Like in above case, SECURITY DEFINER. Below is output

before 9.3:

-[ RECORD 1 ]-------+-------------
Schema              | oma
Name                | test_func
Result data type    | integer
Argument data types |
Type                | normal
Volatility          | volatile
Owner               | enterprisedb
Language            | sql
Source code         |  SELECT 1;
                    |
Description         |

In 9.3:

Schema              | oma
Name                | test_func
Result data type    | integer
Argument data types |
Type                | normal
Security            | definer
Volatility          | volatile
Owner               | enterprisedb
Language            | sql
Source code         |  SELECT 1;
                    |
Description         |

In above you can see column name Security.

8. New improvement psql Output:

9.3 is also coming with some new improvements in psql commands below are details of those:

i. latex-longtable support and border=3 style for latex output.

Those who are not familiar with LaTex, I would recommend following two links:

        http://www.latex-project.org
        http://en.wikipedia.org/wiki/LaTeX
      

So,those, who are familiar with Latex, for them this new feature will be helpful. latex-longtable allows tabular format output to in multiple pages. With this there border=3 defined for latex output in psql 9.3

ii. –tuples-only (-t) with –expanded (-x) not to show “(No rows)”

before 9.3, if someone uses –tuples-only with –expanded switch in psql and query executed doesn’t return any record,then combination two switches used to print “(No rows)”. As given below:

      [root@ip-10-159-51-181 ~]# psql -t -x -c "select datname from pg_database where datname ~ 'temporar'"
(No rows)
     

which used to give some inconvenience in scripting psql. However now, 9.3 onwards, if query doesn’t return any record/row, then psql will return nothing. as given below:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -t -x -c "select datname from pg_database where datname ~ 'temporar'"
[root@ip-10-159-51-181 ~]#

which helps in better scripting.

ii. –no-align (-A) with –expanded (-x) not to print empty lines.

Before 9.3, combination of –no-align (-A) with –expanded (-x) used to print empty line, if query doesn’t return any value. However this is fixed. In 9.3 it will return nothing, as shown below:

Before 9.3

[root@ip-10-159-51-181 ~]# psql -t -c "select datname from pg_database where datname ~ 'temporar'" -x -A

[root@ip-10-159-51-181 ~]#

In 9.3:

[root@ip-10-159-51-181 ~]# /usr/pgsql-9.3/bin/psql -t -c "select datname from pg_database where datname ~ 'temporar'" -x -A
[root@ip-10-159-51-181 ~]#

From scripting perspective, this is good improvement.

9. Features/imporvement in pg_dump

i. parallel dump option using –jobs in pg_dump

9.3 has added new performance improvement in pg_dump. pg_dump can be faster in unloading data from PG database. It has been improved to do with parallel jobs.

There are some notes, which user should know:

  1. parallel dump is supported if user uses dump format directory. i.e -Fd.
  2. parallel dump can increase load on server, so user has to be cautious about choosing number of jobs for pg_dump.
  3. --jobs will open a n+1 connections to database, so, user would like to set the max_connections appropriately. Out of n+1 connections, one connection will be used by master process of pg_dump and n connections will be used by master workers.
  4. Worker process in pg_dump takes SHARED LOCK with NOWAIT option. so, if there is any exclusive lock on table then worker process will not be able to acquire SHARED LOCK will exit and accordingly inform to master process,which will abort the pg_dump.
  5. for consistent backup, database needs to support synchronized snapshots which was introduced in 9.2. Therefore if user is planning to use pg_dump of 9.3 to take backup of pre-9.2 postgresql, then they have to make sure that database content doesn't change.
 

Example of parallel dump is given below:

pg_dump -U username -j4 -Fd -f  databasename

ii. –dbname switch in pg_dump.

Before 9.3, pg_dump didn’t have the –dbname option. Since it was assummed that user will use database name at the end of pg_dump, as given below:

 pg_dump -U username -p port -h host [database name]

To keep the consistency of connection option with other utilities/binaries, in 9.3 –dbname option has been introduced. Now, user can also execute command like given below:

     pg_dump -U username -p port -h host -d [database name]
   

10. New in initdb

9.3 has also added new functionality and feature in initdb command too.

i. fsync the newly created data directory.

in 9.3, initdb now make sure data directly is safely written to disk. which makes data directory created by initdb is more durable. There will be slight performance impact of using default fysnc. However user can disable this option by choosing –nosync (-N) option, which is not recommended.

Below are some stats:

With default:
real	0m4.304s
user	0m2.736s
sys	0m1.604s

with --nosync
real	0m4.129s
user	0m2.667s
sys	0m1.493s

you can see there is not much difference as per performance.

ii. –sync-only option in initdb.

9.3 has also added new switch called sync-only. Using this option user can make sure existing data directory to be written safely to disk. Example of usage is given below:

-bash-4.1$ /usr/pgsql-9.3/bin/initdb --sync-only -D /var/lib/pgsql/test
syncing data to disk ... ok
-bash-4.1$

iii. warning message if initdb is used to place data directory in top of file filesystem mount point.

In 9.3, initdb has been made more sensible on warning user, if user is trying to create data directory top of filesystem mount point.

Following are some warning which user will get:

It contains a dot-prefixed/invisible file, perhaps due to it being a mount point.
It contains a lost+found directory, perhaps due to it being a mount point.
Using a mount point directly as the data directory is not recommended.                          Create a subdirectory under the mount point

Enjoy Learning more about 9.3!!!