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:
Recursive View Syntax
12. Writeable Foreign Tables
13. Replication Improvements
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.
Figure: TPS SELECT
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