Postgres Plus Advanced Server 9.3 Features

21 Nov

Postgres Plus Advanced Server 9.3, released on Tuesday for general availability, contains all of the innovation that was part of PostgreSQL 9.3, which was released in September.
These features include the following:

1. Configuration directive ‘include_dir’
2. COPY FREEZE for more efficient bulk loading
3. Custom Background Workers
4. Data Checksums
5. JSON: Additional functionality
6. LATERAL JOIN
7. Parallel pg_dump for faster backups
8. ‘pg_isready’ server monitoring tool
9. Switch to Posix shared memory and mmap()
10. Event Triggers
11. VIEW Features:
                   Materialized Views
                   Recursive View Syntax
                   Updatable Views
12. Writeable Foreign Tables
                   postgres_fdw
13. Replication Improvements
                    Streaming-Only Remastering
                    Fast Failover
                    Architecture-Independent Streaming
                    pg_basebackup conf setup

For Postgres Plus Advanced Server, we integrated into the core PostgreSQL additional performance improvements, new packages and Object oriented features so that our database can address a wider range of enterprise use cases.

Partitioning enhancements to boost performance for INSERTS/UPDATES/SELECT was a major development for Postgres Plus. Below are graphs illustrating the performance increases of Postgres Plus Advanced Server 9.3 compared to the 9.2 version.

TPS_Select

Figure: TPS SELECT

TPS_Update

 Figure TPS UPDATE

The release features some important functions that can make a developer’s life easier, such as:

1. DBMS_RANDOM package. This packages helps users to create Random numbers, Random strings and Random dates. PostgreSQL supports random functions, which enable users to have their own function on top of random for random string and random date.

With this package, users can easily use the built-in functions for those two purposes. Below are some examples:
DBMS_RANDOM package can be used to easily generate random strings and dates, and users don’t need to make their own wrapper function for these two activities. Below is one simple example of using it.

user=# BEGIN
user$#   DBMS_OUTPUT.put_line('Run 1 : seed=0');
user$#   DBMS_RANDOM.seed (val => 0);
user$#   FOR i IN 1 ..5 LOOP
user$#     DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
user$#   END LOOP;
user$#
user$#   DBMS_OUTPUT.put_line('Run 2 : seed=0');
user$#   DBMS_RANDOM.seed (val => 0);
user$#   FOR i IN 1 ..5 LOOP
user$#     DBMS_OUTPUT.put_line('i=' || i || ' : value=' || DBMS_RANDOM.value(low => 1, high => 10));
user$#   END LOOP;
user$#
user$# END;
Run 1 : seed=0
i=1 : value=6.756044853478672
i=2 : value=7.806749390438203
i=3 : value=1.1173334638588129
i=4 : value=5.221515491604802
i=5 : value=8.445010517258194
Run 2 : seed=0
i=1 : value=6.756044853478672
i=2 : value=7.806749390438203
i=3 : value=1.1173334638588129
i=4 : value=5.221515491604802
i=5 : value=8.445010517258194

EDB-SPL Procedure successfully completed
user=# BEGIN
user$#   FOR i IN 1 .. 5 LOOP
user$#     DBMS_OUTPUT.put_line('string(''x'',10)= ' || DBMS_RANDOM.string('x',10));
user$#   END LOOP;
user$# END;
string('x',10)= 1TT23XR8X2
string('x',10)= DO5D2KUUVD
string('x',10)= AGNPAXDECT
string('x',10)= 7JC6RMU9KX
string('x',10)= 13BW6JM6KN

EDB-SPL Procedure successfully completed
user=# BEGIN
user$#   FOR i IN 1 .. 5 LOOP
user$#     DBMS_OUTPUT.put_line('date= ' || TRUNC(SYSDATE + DBMS_RANDOM.value(0,366)));
user$#   END LOOP;
user$# END;
date= 20-JUN-14 00:00:00
date= 26-MAY-14 00:00:00
date= 11-JAN-14 00:00:00
date= 27-JUN-14 00:00:00
date= 21-DEC-13 00:00:00

EDB-SPL Procedure successfully completed

2. DBMS_LOCK.sleep: Similar to pg_sleep function, the DBMS_LOCK.sleep package is meant for Oracle users/developers who are more familiar with Oracle packages.

3. DBMS_CRYPTO: DMBMS_CRYPTO is new in Postgres Plus Advanced Server 9.3 and it provides the interface to encrypt and decrypt stored data, and can be used in conjunction with PL/SQL programs for running network communications. It provides support for several industry-standard encryption and hashing algorithms.

DECLARE
   input_string       VARCHAR2 (200) :=  'Secret Message';
   output_string      VARCHAR2 (200);
   encrypted_raw      RAW (2000);             -- stores encrypted binary text
   decrypted_raw      RAW (2000);             -- stores decrypted binary text
   num_key_bytes      NUMBER := 256/8;        -- key length 256 bits (32 bytes)
   key_bytes_raw      RAW (32);               -- stores 256-bit encryption key
   encryption_type   INTEGER :=          -- total encryption type
                            DBMS_CRYPTO.ENCRYPT_DES
                          + DBMS_CRYPTO.CHAIN_CBC
                          + DBMS_CRYPTO.PAD_PKCS5;
BEGIN
   DBMS_OUTPUT.PUT_LINE ( 'Original string: ' || input_string);
   key_bytes_raw := DBMS_CRYPTO.RANDOMBYTES (num_key_bytes::INTEGER);
   encrypted_raw := DBMS_CRYPTO.ENCRYPT
      (
         src => convert_to(input_string, 'LATIN1'),
         typ => encryption_type,
         key => key_bytes_raw
      );
    -- The encrypted value "encrypted_raw" can be used here
   decrypted_raw := DBMS_CRYPTO.DECRYPT
      (
         src => encrypted_raw,
         typ => encryption_type,
         key => key_bytes_raw
      );
   output_string := convert_from(decrypted_raw,'LATIN1');

   DBMS_OUTPUT.PUT_LINE ('Decrypted string: ' || output_string);
END;

4. DBMS_SCHEDULER: With Postgres Plus 9.2, we got DBMS_JOB package, and now Postgres Plus 9.3 has come out with DBMS_SCHEDULER, which has more control options for jobs and better visibility of scheduled jobs.
5. UTL_ENCODE: This is another important package for keeping sensitive information in the database. Using this package, users can easily encode and decode their data and keep it in database. This package can be utilized in new applications, and gives flexibility around important encode/decode functions for developers and users where data is very important. This is particularly useful for users working applications that were written for Oracle but migrated onto Postgres Plus Advanced Server. .

Let’s see an example:

user=# DECLARE
user-#     v_str VARCHAR2(100);
user$#   BEGIN
user$#      --generate encoded value
user$#     v_str := utl_encode.text_encode('EnterpriseDB','gb18030', UTL_ENCODE.BASE64);
user$#     dbms_output.put_line('Encoded string => '||v_str);
user$#
user$#      --take the encoded value and decode it
user$#    v_str := utl_encode.text_decode(v_str,'gb18030', UTL_ENCODE.BASE64);
user$#    dbms_output.put_line('Decoded string => '||v_str);
user$# END;
Encoded string => RW50ZXJwcmlzZURC
Decoded string => EnterpriseDB

EDB-SPL Procedure successfully completed

6. UTL_HTTP: Advanced Server 9.3 has an UTL_HTTP package. This package provides functions for HTTP callouts from SQL/SPL. Developers/users can use this package and associated functions for accessing data on the Internet over HTTP.

7. UTL_URL: This is one of additions we developed for Postgres Plus 9.3. This package has two functions, escape and unescape mechanisms for URL characters. The escape function helps to escape a URL before the URL can be used to fetch data from a website. The unescape function can unescape any escaped character used in the URL, before fetching the data from a website. These two packages with UTL_HTTP allow users to direct the fetching of data from a website without having to write complex code in the application for handling specific data from a website.

8. New in EDB*loader. In Postgres Plus 9.3 EDB*loader has more control options for bulk loading.
a. ROWS parameter: prior to version 9.3, EDB*Loader processed entire data files as a single transaction. With parameter, users can control the processing of large amounts of data after which COMMIT needs to be executed. More control for processing/Loading data files.
b. Error Codes: EDB*loader now supports some additional exit/error codes, which will help users/developers include proper exit code checking while using a higher speed bulk data loader with parallel processing. Exit codes are shown below:

      0: Success
      1: Failure
      2: Warning
      3: Fatal

c. Streaming output files to client: Prior to version 9.3, users had to check the logfile of EDB*loader on the server side. Files: logfile, Bad file and discard file used to be created on the server side, and for troubleshooting users had to log in on the server to verify these files. Now, Postgres Plus 9.3 enables these files to be created at the client site. Users are no longer required to log into the server to verify/check these files..
d. New GUC for empty string. EDB*loader now has one GUC which users can utilize to control the default behavior of an empty_string in their datafile. edbldr.empty string has the following valid values:
1. null: empty field is treated as a null if the raw field contains no characters or a pair of delimiters with nothing in between.
2. empty_string: empty field is treated as a empty_string, if the raw field contains no characters or a pair of delimiters with nothing in between.
3. pgsql: empty field is treated as a null if the raw field contains no characters, but as an empty string if it contains a pair of delimiters with nothing in between.
Default is pgsql.

9. New REGEXP functions. Postgres Plus 9.3 nowhas three new REGEXP functions for developers.
a. REGEXP_COUNT: This searches a string for a regular expression, and returns a count of the times that the regular expression occurs. Here’s a simple example:

          user=# SELECT REGEXP_COUNT('reinitializing', 'i', 1);
          regexp_count
           --------------
            5
          (1 row)
 

b. REGEXP_INSTR: This function searches a string for a POSIX-style regular expression and returns the position within the string where the match was located.

user=# SELECT REGEXP_INSTR('800-555-1212', '[0-9][0-9][0-9]', 1, 1) ;
 regexp_instr
--------------
            1
(1 row)

c. REGEXP_SUBSTR: This function searches a string for a pattern specified by a POSIX compliant regular expression and returns the string that matches the pattern specified in the call to the function.


user=# SELECT REGEXP_SUBSTR('800-555-1212', '[0-9][0-9][0-9]', 1, 2);
 regexp_substr
---------------
 555
(1 row)

10. New exception codes for UTL_FILE package: The UTL_FILE package provides the capability to read from, and write to files on the operating system’s file system. To provide error control capabilities to developers/users, Postgres Plus 9.3 has added some new exception codes, which users can utilize in their PL/SPL code for better exception handling. One simple example is given below:

user=# CREATE DIRECTORY tempdir_fb22954 AS '/tmp/';
CREATE DIRECTORY
user=# SELECT dirname
user-# FROM edb_dir
user-# WHERE dirname='tempdir_fb22954';
     dirname
-----------------
 tempdir_fb22954
(1 row)

user=#
user=#  -- check "utl_file.invalid_operation" exception
user=# DECLARE v_testfile UTL_FILE.FILE_TYPE;
user$#
user$#  v_directory VARCHAR2(50) := 'tempdir_fb22954';
user$#
user$#  v_filename VARCHAR2(50) := 'test_file_exist.txt';
user$#
user$#  BEGIN -- Create file
user$#  v_testfile := UTL_FILE.FOPEN(v_directory,v_filename,'w');
user$#
user$#  UTL_FILE.PUT(v_testfile,'A');
user$#
user$#  UTL_FILE.NEW_LINE(v_testfile);
user$#
user$#  UTL_FILE.FCLOSE(v_testfile);
user$#
user$#  DBMS_OUTPUT.PUT_LINE('Created file: ' || v_filename);
user$#
user$#  -- It should throw exception because file is open for read.
user$#  v_testfile := UTL_FILE.FOPEN(v_directory,v_filename,'r');
user$#
user$#  UTL_FILE.PUT(v_testfile,'B');
user$#
user$#  UTL_FILE.NEW_LINE(v_testfile);
user$#
user$#  UTL_FILE.PUT(v_testfile,'C');
user$#
user$#  UTL_FILE.NEW_LINE(v_testfile);
user$#
user$#  exception
user$#  WHEN utl_file.invalid_operation THEN
user$# RAISE notice 'exception caught utl_file.invalid_operation : SQLERRM: %',
user$#                                                       sqlerrm;
user$# UTL_FILE.FCLOSE(v_testfile);
user$#  WHEN others THEN
user$# RAISE notice 'exception, others : SQLERRM: %',
user$#                         sqlerrm;
user$# UTL_FILE.FCLOSE(v_testfile);
user$#  END;
Created file: test_file_exist.txt
NOTICE:  exception caught utl_file.invalid_operation : SQLERRM: invalid file operation

EDB-SPL Procedure successfully completed

New in PostgreSQL 9.3: Data Types

12 Aug

In series of writing further on upcoming features in 9.3, I thought about including new improvements coming in data types in PostgreSQL.

1. Increase the maximum length of large objects from 2GB to 4TB

PostgreSQL has support of Large Objects from starting. However the limit of large objects in PostgreSQL was limited to 2GB.

From 9.3 onwards, PostgreSQL can store large objects up to 4TB. Thats happened due to lifting the limitation of API for large object. APIs like lo_seek(),lo_tell cannot return over 2GB offset and main reason was offset parameters defined for these function is of 4 bytes and results length provided by these functions is 4 bytes. If user do the calculation, he can see 2^31-1=2GB, it resulted in 2GB.

To overcome from this limitation, PostgreSQL is coming with new APIs: lo_seek64 and lo_tell64 functions. Libpq interface will check if those lo_tell64/seek64 exits then use it or use the 32 bit of lo_seek/lo_tell functions, this way compatibility has been maintained in PostgreSQL for older release and new upcoming release. This is good add-on for PostgreSQL. Thanks to Tatsuo Ishii (pgpool developer and developer for this add-on).

2. Text timezone designations using ISO “T” function for timestamptz

This is new for timestamtz. In 9.3, text timezone designation is allowed using ISO “T” format. This was not working in pre-9.3. Lets look at one example:

In pre-9.3


worktest=# select '2011-08-29T09:11:14.123 America/Chicago'::timestamptz;
ERROR:  invalid input syntax for type timestamp with time zone: "2011-08-29T09:11:14.123 America/Chicago"
LINE 1: select '2011-08-29T09:11:14.123 America/Chicago'::timestampt...

As user can see, pre-9.3 has complained about it. However in In 9.3 this has been fixed.

pgsqltest=# select '2011-08-29T09:11:14.123 America/Chicago'::timestamptz;
        timestamptz         
----------------------------
 2011-08-29 10:11:14.123-04
(1 row)

Good to see this fix.

3. New operators and Functions for JSON data strings

PostgreSQL 9.3, is coming withe some new functions and operators for JSON data types, which is add-ons for users who uses JSON data type in their application. Now, they can explore new functions and operators for their use case.
Pre-9.3, had following functions:

array_to_json
row_to_json

In 9.3, we have following new operators:

Operator Right Operand Type Description Example
-> int Get JSON array element '[1,2,3]'::json->2
-> text Get JSON object field '{"a":1,"b":2}'::json->'b'
->> int Get JSON array element as text '[1,2,3]'::json->>2
->> text Get JSON object field as text '{"a":1,"b":2}'::json->>'b'
#> array of text Get JSON object at specified path '{"a":[1,2,3],"b":[4,5,6]}'::json#>'{a,2}'
#>> array of text Get JSON object at specified path as text '{"a":[1,2,3],"b":[4,5,6]}'::json#>>'{a,2}'

And following new functions:

Function Return Type Example
to_json(anyelement) json to_json('Fred said
"Hi."'::text)
json_array_length(json) int json_array_length('[1,2,3,{"f1":1,"f2":[5,6]},4]')
json_each(json) SETOF key text, value json select * from
json_each('{"a":"foo", "b":"bar"}')
json_each_text(from_json
json)
SETOF key text, value text select * from
json_each_text('{"a":"foo", "b":"bar"}')
json_extract_path(from_json json,
VARIADIC path_elems text[])
json json_extract_path('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4')
json_extract_path_text(from_json
json, VARIADIC path_elems text[])
text json_extract_path_text('{"f2":{"f3":1},"f4":{"f5":99,"f6":"foo"}}','f4',
'f6')
json_object_keys(json) SETOF text json_object_keys('{"f1":"abc","f2":{"f3":"a",
"f4":"b"}}')
json_populate_record(base
anyelement, from_json json, [, use_json_as_text
bool=false]
anyelement select * from
json_populate_record(null::x, '{"a":1,"b":2}')
json_populate_recordset(base
anyelement, from_json json, [, use_json_as_text
bool=false]
SETOF anyelement select * from
json_populate_recordset(null::x,
'[{"a":1,"b":2},{"a":3,"b":4}]')
json_array_elements(json) SETOF json json_array_elements('[1,true,
[2,false]]')

4. New functions to support hstore to JSON

PostgreSQL 9.3, is also coming with new functions for converting values of hstore to JSON.
Following are new functions with example which is coming in for hstore to JSON data types.

Function Return Type Description Example Result
hstore_to_json(hstore) json get hstore as a json value hstore_to_json('"a key"=>1,
b=>t, c=>null, d=>12345, e=>012345,
f=>1.234, g=>2.345e+4')
{"a key": "1", "b": "t", "c":
null, "d": "12345", "e": "012345", "f": "1.234", "g":
"2.345e+4"}
hstore_to_json_loose(hstore) json get hstore as a json value, but attempting to distinguish
numerical and Boolean values so they are unquoted in
the JSON
hstore_to_json_loose('"a
key"=>1, b=>t, c=>null, d=>12345,
e=>012345, f=>1.234, g=>2.345e+4')
{"a key": 1, "b": true, "c":
null, "d": 12345, "e": "012345", "f": 1.234, "g":
2.345e+4}

New in PostgreSQL 9.3: New in Functions

4 Aug

In the series of blogging about new features in 9.3, today, I thought about blogging new functions and improvements coming in PostgreSQL.

Lets look whats new in 9.3, in terms of in build functions:

1. New in one array functions for one dimensional array

PostgreSQL 9.3, is coming with new functions which can help users to manipulate one dimensional arrays by calling simple functions at the place of crafting their own functions and following some methods to do the modification in it.

i. array_remove function

This is a new function added in 9.3, which provides ability for removing the elements from array. Function takes two arguments:
a. One dimensional array from which user wants to remove elements
b. element value which user wants to remove
.

Syntax of this function is given below:

ARRAY_REMOVE(<one dimensional array>, element)

Example of array_remove is given below:

postgres=# select array_remove(ARRAY['First','Second','Delete','Four'],'Delete');
    array_remove     
---------------------
 {First,Second,Four}
(1 row)

ii. array_replace function

This is a new in 9.3, this helps user to replace any element in array. This function can be use multi-dimensional array Or for single dimensional array.
Example of array_replace is given below:

pgsqltest=# select array_replace(ARRAY[ARRAY[1,2],ARRAY[3,4],ARRAY[56,6],ARRAY[7,8]],56,5);
       array_replace       
---------------------------
 {{1,2},{3,4},{5,6},{7,8}}
(1 row)

2. VARIADIC-labeled arguments expansion for concat and format functions

This is new addition to concat and format function. pre-9.3 concat and format, function used to ignore VARIADIC label and doesn’t used to print right output. However, this has been fixed in 9.3. Lets look at the example of pre-9.3 and in 9.3

In pre-9.3

worktest=# select concat(variadic array[1,2,3]);
 concat  
---------
 {1,2,3}
(1 row)

In 9.3

pgsqltest=# select concat(variadic array[1,2,3]);
 concat 
--------
 123
(1 row)

You can see above in 9.3, using VARIADIC label working properly.

Lets look at the format() function:

In pre-9.3

worktest=# select format(string_agg('%s',','), variadic array_agg(i)) from generate_series(1,3) g(i);
ERROR:  too few arguments for format

woow, its error out. However, this is fixed in 9.3 :-).

In 9.3

pgsqltest=# select format(string_agg('%s',','), variadic array_agg(i)) from generate_series(1,3) g(i);
 format 
--------
 1,2,3
(1 row)

3. Improvement in format() function to handle field width and left/right alignment

This new added in 9.3 format() function, which is going to increase the usability of format() function for developers. In 9.3, format function is coming with following enhancements:
i). Proper handling of field width.
ii). Proper handling of Left/right alignment.

Above two improvement was missing in pre-9.3. However, addition of above improvement made format() function to be follower of sprintf() C function.

Lets look at the example:
In pre-9.3, using of field width resulted into error message, as given below

ERROR:  unterminated conversion specifier
worktest=#  select format('>>%10s<<', 'Hello');
ERROR:  unterminated conversion specifier
worktest=# 

However, 9.3 is coming with proper field width support.

pgsqltest=#  select format('>>%10s<<', 'Hello');
     format     
----------------
 >>     Hello<<
(1 row)

9.3 is also coming proper handling left/right alignment. Examples are given below:
Left alignment

pgsqltest=# select format('>>%-10s<<', 'Hello');
     format     
----------------
 >>Hello     <<
(1 row)

Right alignment

pgsqltest=# select format('>>%1$10s<<', 'Hello');
     format     
----------------
 >>     Hello<<
(1 row)

4. Proper handling of Negative century in to_char, to_date and to_timestamp functions

In pre-9.3, following function behavior for negative century was wrong or inconsistent
a. to_char
b. to_date
c. to_timestamp.

However, 9.3 is coming with proper fix/handling for negative century. Lets look at the output in pre-9.3 and in 9.3 for each functions.

pre-9.3

worktest=# select to_timestamp('4713-01-08 BC','YYYY-DD-MM bc');
         to_timestamp         
------------------------------
 01-AUG-13 00:00:00 -04:56:02
(1 row)

Above you can see that its displaying wrong result for BC. In 9.3

pgsqltest=# select to_timestamp('4713-01-08 BC','YYYY-DD-MM bc');
          to_timestamp           
---------------------------------
 4713-08-01 00:00:00-04:56:02 BC
(1 row)

Lets see for to_date functions. In 9.2/pre-9.3

worktest=# SELECT to_date('4713-01-08 BC','YYYY-DD-MM bc');
ERROR:  full year must be between -4712 and +9999, and not be 0
worktest=# 

In 9.3

pgsqltest=# SELECT to_date('4713-01-08 BC','YYYY-DD-MM bc');
    to_date    
---------------
 4713-08-01 BC
(1 row)

Above, you can see in 9.3,to_date function is working right. However to_date in 9.2 didn’t able to handle it. Similar behavior you can see for to_char function.

5. Improvement in pg_get_viewdef() to print new line after each SELECT and FROM entry

This is kind of enhancement made in pg_get_viewdef function of 9.3. And user will be able to see this enhancement in pg_dump plain dump too.

This enhancement is more like readability of output of view definition in 9.3 and reducing the line length of view definition. Lets look at how this improvement makes user experience of getting/viewing view definition much better.

Before 9.3 or in pre-9.3

worktest=# select pg_get_viewdef('pg_tables'::regclass);
                                                                                                    
                                                                                  pg_get_viewdef    
                                                                                                    
                                                                              
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
 SELECT n.nspname AS schemaname, c.relname AS tablename, pg_get_userbyid(c.relowner) AS tableowner, 
t.spcname AS tablespace, c.relhasindex AS hasindexes, c.relhasrules AS hasrules, c.relhastriggers AS
 hastriggers FROM ((pg_class c LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace))) LEFT JOIN pg_
tablespace t ON ((t.oid = c.reltablespace))) WHERE (c.relkind = 'r'::"char");
(1 row)

Above you can see that pg_get_viewdef has printed defintion of view in one line, which not only make readability of view definition difficult. However also increase the line length.

Lets look at the pg_get_viewdef in 9.3

pgsqltest=# select pg_get_viewdef('pg_tables'::regclass);
                        pg_get_viewdef                        
--------------------------------------------------------------
  SELECT n.nspname AS schemaname,                            +
     c.relname AS tablename,                                 +
     pg_get_userbyid(c.relowner) AS tableowner,              +
     t.spcname AS tablespace,                                +
     c.relhasindex AS hasindexes,                            +
     c.relhasrules AS hasrules,                              +
     c.relhastriggers AS hastriggers                         +
    FROM ((pg_class c                                        +
    LEFT JOIN pg_namespace n ON ((n.oid = c.relnamespace)))  +
    LEFT JOIN pg_tablespace t ON ((t.oid = c.reltablespace)))+
   WHERE (c.relkind = 'r'::"char");
(1 row)

which seems me better in terms of readability and doesn’t have long line.

Enjoy!!

New in PostgreSQL 9.3: Server Side languages

30 Jul

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

28 Jul

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!!!

pg_xlog_location_diff function for PostgreSQL/PPAS

18 Feb

In PostgreSQL 9.2, community has added a function pg_xlog_location_diff(), which is very useful for finding the difference between two xlog location in bytes and also useful for monitoring replication. Detail of this function is given in following link:


http://www.postgresql.org/docs/9.2/static/functions-admin.html#FUNCTIONS-ADMIN-BACKUP

However this function is not available for users of PostgreSQL/PPAS 9.0/9.1 users. So, I thought to write same function plpgsql so, that users can take benefit of same in 9.0/9.1.

Before using formula and developing function, lets understand what is xlog and offset.
Let’s consider user has used function pg_current_xlog_location() function and he gets following information:

worktest=# select pg_current_xlog_location();
 pg_current_xlog_location 
--------------------------
 1/D1012B80
(1 row)

In above, first field before forward slash is the hexadecimal value of logical xlog file and second field i.e. D1012B80 is hexadecimal offset inside the logical xlogfile.

So, whenerver user sees information for xlog location, he gets xlog information in following format:

(hexadecimal) xlog/ (hexadecimal) offset

To calculate the difference between two xlog location, user can use following formula, which gives difference in hexadecimal:

(FF000000 * xlog + offset) - (FF000000 * xlog + offset)

where FF000000 is max value of offset i.e In PostgreSQL/PPAS offset value can go from 00000000 to FF000000 and if we convert that in decimal, it gives maximum 4278190080 bytes (4080MB)

Based on above formula, following is plpgsql function which can be use to get the difference:

CREATE OR REPLACE FUNCTION pg_xlog_location_diff_sql( text, text)
 RETURNS numeric
 LANGUAGE plpgsql
AS 
 $function$
    DECLARE
       offset1 text;
       offset2 text;
       xlog1 text;
       xlog2 text;
       SQL text;
       diff text;
    BEGIN
       /* Extract the Offset and xlog from input in
          offset and xlog variables */
          
       offset1=split_part($1,'/',2);
         xlog1=split_part($1,'/',1);
       offset2=split_part($2,'/',2);
         xlog2=split_part($2,'/',1);
       
       /* Prepare SQL query for calculation based on following formula
         (FF000000 * xlog + offset) - (FF000000 * xlog + offset)
         which gives value in hexadecimal. Since, hexadecimal calculation is cumbersome
         so convert into decimal and then calculate the difference */
       
       SQL='SELECT (x'''||'FF000000'||'''::bigint * x'''||xlog1||'''::bigint 
                                +  x'''||offset1||'''::bigint)'||' 
                - 
                   (x'''||'FF000000'||'''::bigint * x'''||xlog2||'''::bigint 
                                +  x'''||offset2||'''::bigint)';
       EXECUTE SQL into diff;
       
       /* Return the value in numeric by explicit casting  */
       
       RETURN diff::numeric;
    END;
 $function$;
 

Usage example is given below:

worktest=# select pg_xlog_location_diff_sql(pg_current_xlog_location(),'1/D009F578');
 pg_xlog_location_diff_sql 
---------------------------
                  16230472
(1 row)

I hope this will help PostgreSQL/PPAS 9.0/9.1 users.

New in Postgres Plus Advanced Server 9.2

7 Oct

Good News Postgres Plus Advanced Server Beta Version is now availabale, which has all the new feature of PostgreSQL 9.2, and it also has new features which are specific to Advanced Server.

I will cover/explain New features of PostgreSQL 9.2 later as per release Notes. However, the following link covers major features of PostgreSQL 9.2 and can be taken as reference.


http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.2

In this Blog, I am going to cover only specific features of Advanced Server 9.2 Core. Which are:

1. INSERT APPEN HINT in PPAS 9.2

PPAS 9.2, now supports INSERT append hint. This is a very interesting feature and it is very useful for users who frequently delete records in Bulk and do bulk of INSERTs.
This hint can provide some benefits in INSERTs. This hint makes PPAS not use Free Space Map and Append the rows at the end of relation(table).
Its usage is given below:

INSERT /*+append*/ INTO tab21115 VALUES(1,'abc',sysdate);

2. Procedure Called like Function Call.

PPAS 9.2 now allows calling procedure with following syntax:

SELECT * FROM procedure(arg1,arg2,…);

This type of Procedure call is allowed for Procedures which have OUT/INOUT Params. With this, user can use exec Function.

lets see how it works:

a. Create a Procedure as Given below:

CREATE OR REPLACE PROCEDURE foo_proc(A IN INT, B INOUT INT, C OUT INT)
AS
BEGIN
   b:=a+b;
   c:=b+1;
END;

Till 9.1 and even in 9.2, user can do something like given below:

DECLARE
  d int:=1;
  e int:=2;
  f int;
BEGIN
   foo_proc(d,e,f);
   DBMS_OUTPUT.PUT_LINE('e = '||e);
   DBMS_OUTPUT.PUT_LINE('d = '||d);
   DBMS_OUTPUT.PUT_LINE('f= '||f);
END;
e = 3
d = 1
f= 4

Now in 9.2, user can also do this:

edb=# select * from foo_proc(1,2);
 b | c 
---+---
 3 | 4
(1 row)

In case the user has Procedure and Function with a same name and the user wants to execute procedure using select command, then SELECT command is going to give preference to Function over Procedure as given below:

Function Definition:

CREATE OR REPLACE FUNCTION foo(a INT) RETURN INT
AS
BEGIN
  RETURN 2;
END;

CREATE PROCEDURE foo(A int, B OUT int)
AS
BEGIN
   B:=a+1;
END;

edb=# select * from foo(4);
 foo 
-----
   2

However, user can still use EXEC command to execute Procedure as given below:

edb=# exec foo(4);
 ?column? 
----------
        5
(1 row)

Or if you are using Anonymous function then PERFORM will also work with Procedure as given below:

edb=# DECLARE
edb-#     a int:=4;
edb$#     b int;
edb$# BEGIN
edb$#    PERFORM foo(a,b);
edb$#    DBMS_OUTPUT.PUT_LINE('b = '||b);
edb$# END;
b = 5

EDB-SPL Procedure successfully completed

3. Object Type Support:

PPAS already has Object Type support. However, the new version is coming with a new enhancement in that support. In New Version, user would be able to create Objects with Attributes, Functions, and Procedures. This gives an advantage of reduced coding in terms of defining Object types and maintaining it.

Example is given below:

a. Define a Type:

    CREATE OR REPLACE TYPE PersonObj AS OBJECT (
  first_name  VARCHAR2(50),
  last_name   VARCHAR2(50),
  date_of_birth  DATE,
  MEMBER FUNCTION getAge RETURN NUMBER
);

b. Define a Type Body:

CREATE OR REPLACE TYPE BODY PersonObj AS
  MEMBER FUNCTION getAge RETURN NUMBER AS
  BEGIN
    RETURN Trunc(Months_Between(Sysdate, date_of_birth)/12);
  END getAge;
END;

c. Define a Table based on Body type:

CREATE TABLE people (
  id      NUMBER(10) NOT NULL,
  person  PersonObj
);

d. To insert Data, Default constructor can be use as given below:

INSERT INTO people
VALUES (1, PersonObj('John','Doe',
        TO_DATE('01/01/1999','DD/MM/YYYY')));

e. With the following way, a user can access the data in the table:

SELECT p.id,
       (p.person).first_name,
       p.person.getAge() age
FROM   people p;
 id | first_name | age 
----+------------+-----
  1 | John       |  13
  2 | Jane       |  13
(2 rows)

4. PL/SQL Subtypes:

Subtypes is considered as a TYPE, which is defined on base/user defined types with some constraints, syntax for declaring SUBTYPE in PL/SQL is given below:

SUBTYPE <subtype_name> IS <type_name> [(constraint)] [NOT NULL];

Example is given below:

Example 1:

DECLARE
  SUBTYPE INT2 IS NUMBER;
  v2 INT2 (3,0);
BEGIN
  v2:= 123;
  DBMS_OUTPUT.PUT_LINE ('V2 = '|| v2);
END;

Example 2:

DECLARE
  SUBTYPE v_word IS VARCHAR2(10) NOT NULL;
  verb  v_word  := 'verb';
  noun  v_word  := 'noun';
BEGIN
  noun :='n1';
  DBMS_OUTPUT.PUT_LINE (UPPER(verb));
  DBMS_OUTPUT.PUT_LINE (UPPER(noun));
END;

5. DROP TABLE CASCADE CONSTRAINTS in 9.2:

PPAS 9.2 now supports DROP TABLE CASCADE CONSTRAINTS, this is a very intersting feature and is not available in PostgreSQL 9.2.

Using this command, user would be able to drop all referential integrity constraints that refer to primary and unique keys in the dropped table. If you omit this clause and such referential integrity constraints exist, then the database returns an error and does not drop the table.

This is useful, when somebody wants to drop Master table but doesn’t want to drop child table, which has Foreign Key Constraint and is referencing the Master. An example is given below:

CREATE TABLE master(id numeric primary key);
CREATE table child(id numeric references master(id));
insert into master values(1);
insert into child values(1);
edb=# drop table master cascade constraints;
NOTICE:  drop cascades to constraint child_id_fkey on table child
DROP TABLE
edb=# \d child
  Table "enterprisedb.child"
 Column |  Type   | Modifiers 
--------+---------+-----------
 id     | numeric | 

As you can see above, I have dropped the Master table, on which child table has referential integrity constaint. However, my Child table still exists. PostgreSQL 9.2, doesn’t have CASCADE CONSTRAINTS. Therefore, user has to first drop the constraints on table which has referential integrity constraints and then drop the referenced table. This process requires user to find all tables which has referential integrity constraints that refer to primary key of master table. This commands helps DBAs/Users not to worry about finding the child tables.

6. Allow throwing Multiple Errors as Warning, while compiling Procedures/Functions using SPL.

This is also a very interesting enhancement found for SPL. Whenever the user wants to compile a function/procedure in SPL, it will show all the error messages in the particular Function/Procedure Body, so that the user doesn’t have to re-visit their code everytime to fix the other issue. An example is given below:

CREATE OR REPLACE FUNCTION foo() RETURN integer
AS
BEGIN
   a:=a=1
;
B:=b + 1;
I am making fool;
END;

Output:
ERROR:  "a" is not a known variable
LINE 4:    a:=a=1
           ^
ERROR:  "b" is not a known variable
LINE 6: B:=b + 1;
        ^
ERROR:  syntax error at or near "I"
LINE 7: I am making fool;
        ^
ERROR:  compilation of SPL function/procedure "foo" failed due to 3 errors

As you can see above, it has given all the mistakes that I made in Function Body, and now I can fix all of them in one go, to reduce the overhead of executing again and again.

This is very useful for Developers/DBAs.

7. DEFAULT for Types declared in Packages:

Now, in PPAS 9.2, user would be able to define DEFAULT value for particular type, as given below:

CREATE OR REPLACE PACKAGE INITPKG_pro_b4_default AS
PROCEDURE show_default_values;
 n1 NUMBER DEFAULT 20;
 c1 VARCHAR2 DEFAULT 'Default';
END;

CREATE OR REPLACE PACKAGE BODY INITPKG_pro_b4_default AS
 PROCEDURE show_default_values IS
  n number;
 BEGIN
  dbms_output.put_line(c1);
  dbms_output.put_line(n1);
  n1 := n1*n1;
  n1 := SQRT(n1);
  dbms_output.put_line(n1);
 END;
END;

edb=# exec INITPKG_pro_b4_default.show_default_values;
Default
20
20.000000000000000

EDB-SPL Procedure successfully completed

8. TABLE Expression support for Nested Tables.

Now, in 9.2, user would be able to use TABLE Expressions for Nested tables. This feature was missing and asked by many Oracle DBAs. Table Expressions allows the user to query a collection in the FROM Clause like a Table.

edb=# CREATE OR REPLACE TYPE comp_typ_str_21189 IS TABLE OF VARCHAR2(100);
CREATE TYPE
edb=# 
edb=# select * from table(comp_typ_str_21189('PPP','QQQ')) ;
 column_value 
--------------
 PPP
 QQQ
(2 rows)

9. INPUT/OUTPUT Functions for NESTED TABLES:

PPAS 9.2 supports user defined input/output functions for NESTED TABLE. This feature was missing in 9.1.

Some examples are given below:

edb=# create or replace type mytab as table of varchar2(90);
CREATE TYPE
edb=# Create or replace function fun return mytab
edb-# as
edb$# begin
edb$#  return mytab('a','b',3);
edb$# end;
CREATE FUNCTION
edb=# select fun;
 fun21168 
----------
 {a,b,3}
(1 row)

edb=# 
edb=# create or replace function fun return mytab
edb-# as
edb$#  nvar mytab;
edb$# begin
edb$#  nvar := mytab();
edb$#  nvar.extend(4);
edb$#  nvar(1) := 'foo';
edb$#  nvar(2) := NULL;
edb$#  nvar(3) := 'deleteme';
edb$#  nvar(4) := 'bar';
edb$#  return nvar;
edb$# end;
CREATE FUNCTION
edb=# --Verify User's is able to see that data
edb=# select fun;
        fun21168         
-------------------------
 {foo,NULL,deleteme,bar}
(1 row)

9. LOG is no more a reserved keyword for functions.
Till PPAS 9.1, LOG was a reserved keyword and users were not allowed to create functions using this Keyword. In 9.2, a user would be able to use this keyword.

edb=# CREATE OR REPLACE FUNCTION log( a int ) return int as
edb$# BEGIN
edb$# dbms_output.put_line('Function LOG is called');
edb$# return a*2;
edb$# END;
CREATE FUNCTION
edb=# SELECT LOG(10);
Function LOG is called
 log 
-----
  20
(1 row)

10. Variables can be named as current_date.

We know that current_date is special function which returns current date of PostgreSQL/PPAS DB. Till PPAS 9.1, users were not allowed to use this reserved name in variables. Now, in 9.2, users would be able to use it. As given below:

edb=# create table t_currentdate(current_date int);
CREATE TABLE
edb=# desc t_currentdate
    Table "enterprisedb.t_currentdate"
    Column    |  Type   | Modifiers 
--------------+---------+-----------
 current_date | integer | 

create or replace procedure proc_currentdate(current_date date) is
begin
 dbms_output.put_line(current_date);
end;

edb=# exec proc_currentdate(current_date);
05-OCT-12 00:00:00

EDB-SPL Procedure successfully completed

11. New Data Type STRING.

Since Oracle supports string as data type and this datatype wasn’t available in PPAS 9.1, we have included it in 9.2. String is an alias to VARCHAR Data type. Example is given below:

edb=# CREATE TABLE test_string(col string)
edb-# ;
CREATE TABLE
edb=# insert into test_string values('As an Example');
INSERT 0 1
edb=# select * from test_string;
      col      
---------------
 As an Example
(1 row)

edb=# \d test_string
    Table "enterprisedb.test_string"
 Column |       Type        | Modifiers 
--------+-------------------+-----------
 col    | character varying | 

12. NVARCHAR2 data type support in 9.2

PPAS 9.2 allowes users to use NVARCHAR2 as a datatype. In PPAS 9.2, NVARCHAR2 is mapped to VARCHAR data type. This is only for an Oracle user, who always asked question on having this in PPAS.

Example is given below:

edb=# CREATE TABLE test_nvarchar(col NVARCHAR2(10))
edb-# ;
CREATE TABLE
edb=# \d test_nvarchar
     Table "enterprisedb.test_nvarchar"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 col    | character varying(10) | 

13. MultiByte and string delimiter support in EDB*loader.

EDB*Loader in PPAS 9.2, is more efficient, compared to 9.1, in loading data. There were some cases, in which it has been observed that it is difficult to find single character delimiter for data in file as the data in a file has all possible characters. To load those kind of data files, users can now define there own STRING Delimiter. An example is given below:

Data File: data.log:
1$$abc
2$$ccc
3$$dddddddd

Control FIle:
LOAD DATA
INFILE '/tmp/data.log'
BADFILE '/tmp/data.bad'
truncate INTO table edb_loader
FIELDS TERMINATED BY '$$' optionally enclosed by '"'
(id , col )

-bash-4.1$ edbldr userid=enterprisedb/ control=/tmp/edb.ctl log=/tmp/edb.log
EDB*Loader: Copyright (c) 2007-2012, EnterpriseDB Corporation.

Successfully processed (3) records
-bash-4.1$ psql
psql (9.2.0.1)
Type "help" for help.

edb=# select * from edb_loader ;
 id |   col    
----+----------
  1 | abc
  2 | ccc
  3 | dddddddd
(3 rows)
Follow

Get every new post delivered to your Inbox.