READ-ONLY user,READ-ONLY Database,READ-ONLY backup user in PostgreSQL/PPAS

This has been asked me many times. Many users has asked me how they can create READ-ONLY user, READ-ONLY Database or How they can create backup user which can be use only for backup (no modification)

Well answer is in parameter called default_transaction_read_only.

If you want to make a user READ-ONLY, then you can follow steps given below:
1. CREATE normal user.
2. Use ALTER USER command to set this parameter for this user as given below:

ALTER USER  set default_transaction_read_only = on;

3. GRANT SELECT to this user for tables/object using GRANT. And you are good to go.
Below is snapshot:

postgres=# create user readonly password 'test';
CREATE ROLE
postgres=# alter user readonly set default_transaction_read_only = on;
ALTER ROLE
postgres=# GRANT select on employees to readonly;
GRANT
edbs-MacBook-Pro:data postgres$ psql -U readonly -W
Password for user readonly:
psql (9.1.1)
Type "help" for help.

postgres=> select * from employees ;
 employee_name | entity_name
---------------+-------------
 Smith         | HR
 Jones         | HR
 Taylor        | SALES
 Brown         | SALES
(4 rows)

postgres=> CREATE table test_readonly(id numeric);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
postgres=>

Similarly, If you want to make a Database READ-ONLY, then you can use following command

ALTER DATABASE  set default_transaction_read_only=on;

Below is snapshot:

postgres=# CREATE database readonly;
CREATE DATABASE
postgres=# alter database readonly set default_transaction_read_only = on;
ALTER DATABASE
postgres=# \q
edbs-MacBook-Pro:data postgres$ psql readonly
psql (9.1.1)
Type "help" for help.

readonly=# create table test_readonly(id numeric);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
readonly=#

Now, if you want a seperate backup user which you want to use for Online/Hot Backup,Logical Backup (using pg_dump), then you can create a super user with default_transaction_read_only = on and can use it for backup purpose. As given below:

CREATE USER backupuser SUPERUSER  password 'backupuser';
ALTER USER backupuser set default_transaction_read_only = on;

Using default_transaction_read_only parameter, user can also make a Session Readonly by executing following command:

set default_transaction_read_only=on;

I hope above would help someone who is interested in having READ-ONLY user,database or backupuser.

Partition Syntax Support in PPAS 9.1

In Series of New Features in Advanced Server 9.1, today I tought to write about Parition Table Syntax Supported in PPAS 9.1. In PostgreSQL and till PPAS 9.0, user has to follow method given below for partitioning a table:
1. CREATE PARENT Table,
2. Create Child tables using Inherit feature
3. Create Trigger on Parent table, so that trigger can re-direct insert to Right Partition.
4. And if user has to add new child table, then it has to do 2 and 3 steps again.

Now, in PPAS 9.0, user doesn’t have to perform above activities. PPAS 9.1 supports PARTITION TABLE syntax.

Lets see how PARTITION syntax in PPAS can make users/DBAs life easier.

We know that PPAS/PostgreSQL supports two types of partition (Range and List). So, we will see how its simple with CREATE PARTITION SYNTAX.
Lets CREATE RANGE PARTITION as we used to do in PPAS 9.0/PostgreSQL
1. Create Master table as given below:

CREATE TABLE partition_master(id numeric primary key,val text);
CREATE TABLE partition_child1(CHECK (id > 0 and id <=10)) inherits(partition_master);
CREATE TABLE partition_child2(CHECK (id >10 and id <=20)) inherits(partition_master);

2. Then create check constraints on Both Child tables

alter table partition_child1 add primary key (id);
alter table partition_child2 add primary key (id);

3. Create Trigger, which redirect Inserts to correct child table:

CREATE OR REPLACE FUNCTION part_trig_insrt() RETURNS trigger
AS
$$
BEGIN
   IF TG_OP='INSERT' THEN
      IF NEW.id >0 and NEW.id <=10 THEN
           INSERT INTO partition_child1 VALUES(NEW.*);
      ELSIF NEW.id >10 and NEW.id <=20 THEN
           INSERT INTO partition_child2 VALUES(NEW.*);
      ELSE
          RAISE 'inserted partition key doesnt map to any partition';      
     END IF;
  END IF;
END;
$$ language plpgsql;
CREATE TRIGGER partition_trig_insert BEFORE INSERT ON partition_master FOR EACH ROW execute procedure part_trig_insrt();

similarly, you have to write trigger which can handle partition key update.

And whenever user wants to add new partition, he has to update the trigger function and create new partition table etc.

Now, in 9.1AS on-wards, user can run single command for partition and PPAS will take care of all things, as given below:

CREATE TABLE partition_master (id numeric primary key,val text)
PARTITION BY RANGE(id)
(PARTITION partition_child1 VALUES LESS THAN (11),
PARTITION partition_child2 VALUES LESS THAN (21));

edb=# insert into partition_master values(1,'First');
INSERT 0 0
edb=# insert into partition_master values(11,'Eleventh');
INSERT 0 0
edb=# select * from partition_master;
 id |   val    
----+----------
  1 | First
 11 | Eleventh
(2 rows)

edb=# select * from only partition_master_partition_child1;
 id |  val  
----+-------
  1 | First
(1 row)

edb=# select * from partition_master_partition_child2;
 id |   val    
----+----------
 11 | Eleventh
(1 row)

That was easy. With single command you can have your partition table in PPAS.

Suppose later, user wants to add one more partition then he can execute following single command:

ALTER TABLE partition_master add partition partition_child3 VALUES LESS THAN (31);

edb=# insert into partition_master values(30,'Thirty');
INSERT 0 0
edb=# select * from partition_master;
 id |   val    
----+----------
  1 | First
 11 | Eleventh
 30 | Thirty
(3 rows)

edb=# select * from partition_master_partition_child3;
 id |  val   
----+--------
 30 | Thirty
(1 row)

Thats simple. isn’t?

With this PPAS 9.1 Partition Syntax also allows swaping an existing table with a partition or subpartition, as given below:

CREATE TABLE single_table (id numeric primary key,val text)
insert into single_master select generate_series(1,10);
INSERT 0 10

ALTER TABLE partition_master 
EXCHANGE PARTITION partition_child1
WITH TABLE single_table;

Other syntax which are supported is given below:

1.  ALTER TABLE… ADD PARTITION
2.  ALTER TABLE… ADD SUBPARTITION
3.  ALTER TABLE… DROP PARTITION
4.  ALTER TABLE… DROP SUBPARTITION
5.  ALTER TABLE… SPLIT PARTITION
6.  ALTER TABLE… SPLIT SUBPARTITION
7.  ALTER TABLE… TRUNCATE PARTITION
8.  ALTER TABLE… TRUNCATE SUBPARTITION
9.  ALTER TABLE… EXCHANGE PARTITION
10. ALTER TABLE… MOVE PARTITION
11. ALTER TABLE… RENAME PARTITION

Support of above partition syntaxes have really made management of partition table easier in PPAS 9.1!

Virtual Private Database (VPD) in PPAS 9.1

Great News is Postgres Plus Advanced 9.1 is now available for users. So, I thought to write something about Virtual Private Database features, which is part of 9.1

Virtual Private Database (VPD) is a feature which enables Administrator to create security around actual data (i.e row/columns) so that multiple users can access data which is relevant to them. Steps which is require to create Virtual Private database is given below:
1. Create an Application Context
2. Create security policies functions
3. Apply security policies to tables

Lets see how user can implement it in Advanced Server.
1. Setup an environment as given below:

 CREATE user merry identified by edb;
 CREATE user john identified by edb;
 CREATE TABLE public.john_merry(userid varchar2(200),val numeric);
 grant select,update,delete,insert on john_merry to john;
 grant select,update,delete,insert on john_merry to merry;

2. Now create a Policy Function as given below:

 CREATE OR REPLACE FUNCTION verify_user (
 p_schema VARCHAR2,
 p_object VARCHAR2
 )
 RETURN VARCHAR2
 IS
 BEGIN
 RETURN 'userid = SYS_CONTEXT(''USERENV'', ''SESSION_USER'')';
 END;

3. Apply Security Policy using Policy Functions as given below:

 DECLARE
 v_object_schema VARCHAR2(30) := 'public';
 v_object_name VARCHAR2(30) := 'john_merry';
 v_policy_name VARCHAR2(30) := 'secure_data';
 v_function_schema VARCHAR2(30) := 'enterprisedb';
 v_policy_function VARCHAR2(30) := 'verify_user';
 v_statement_types VARCHAR2(30) := 'INSERT,UPDATE,DELETE,SELECT';
 v_update_check BOOLEAN := TRUE;
 v_enable BOOLEAN := TRUE;
 BEGIN
 DBMS_RLS.ADD_POLICY(
 v_object_schema,
 v_object_name,
 v_policy_name,
 v_function_schema,
 v_policy_function,
 v_statement_types,
 v_update_check,
 v_enable
 );
 END;

Now, lets see how it works.
First insert some records for John and Merry as given below:
1. Connect as John user and do some insert as john, as given below:

 edb=> insert into john_merry values('john',1);
 INSERT 0 1
 edb=> insert into john_merry values('john',2);
 INSERT 0 1
 edb=> insert into john_merry values('john',3);
 INSERT 0 1

2. Now connect as merry and insert some records as merry:

 edb=> insert into john_merry values('merry',1);
 INSERT 0 1
 edb=> insert into john_merry values('merry',2);
 INSERT 0 1
 edb=> insert into john_merry values('merry',3);
 INSERT 0 1

Ok. Now we have some data for both users. lets see how VPD works:
1. Connect as John User and try to insert some record for merry:

 edb=> insert into john_merry values('merry',4);
 ERROR: policy with check option violation
 DETAIL: Policy predicate was evaluated to FALSE with the updated values

hmm this is expected, VPD security policy won’t allow me to do this activity.

2. Now try to update Merry data as John User:

 edb=> update john_merry set val=4 where val=3 and userid='merry';
 UPDATE 0

No rows updated. This is expected since as per security policy merry’s data is not visible to john. However, john can update his record as given below:

 edb=> update john_merry set val=4 where val=3 and userid='john';
 UPDATE 1

3. Now, lets execute SELECT query on table to view data:

 edb=> select current_user;
 current_user
 --------------
 john
 (1 row)

edb=> select * from john_merry ;
 userid | val
 --------+-----
 john | 1
 john | 2
 john | 4
 (3 rows)

As you can see, john can see his records not merry’s. Similary user can try for Merry. Below is a snapshot of SELECT query executed by merry:
using dumb terminal settings.

 edb=> select current_user;
 current_user
 --------------
 merry
 (1 row)

edb=> select * from john_merry ;
 userid | val
 --------+-----
 merry | 1
 merry | 2
 merry | 3
 (3 rows)

Interesting!!

Configure PEM Server on Postgres Plus Cloud

Postgres Plus Cloud is now available for users, so I thought to write a short tutorial on How to create Instance on Postgres Plus Cloud and How to configure PEM Server on Postgres Plus Cloud.

Creating and Installing on Postgres Plus Cloud is very simple. Steps are given below with screen shot:

1. Initialize Cluster on Postgres Plus Cloud (If you are already registered.) Link which can be use is given below:

http://ec2-107-20-5-197.compute-1.amazonaws.com:8080/

2. Login Screen is given in First Snapshot

3. As mention in Snapshot use your registered Username and Password:

4. After login with your username and password.  user will get following Initial Screen:

5. After login user will get a Dashboard of Postgres Plus Cloud. Now user can click on LauchDB Cluster to Initalize Cluster and he will get following  popup window

7. Enter Cluster name and information, as given in below Screen Shot:

 

8. Please note, for PEM Server, user would need to PostgreSQL 9.0 Cluster. After completing above information user can click NEXT button and he will get following screen

 

9. Click on Lauch and Postgres Plus Cloud will Create a PostgreSQL Cluster as given below:

10. To view on information of Created Cluster user can go to Dashboard and Click on Cluster tab and user will get all information

11. Note down your host name as given in above Snapshot.

12. After noting down hostname, user can click on USER tab and click on “Download SSH KEY” button to download ssh key.

13. After downloading the SSH Key, user would be able to connect to Cloud Instance using ssh as given below:

edbs-MacBook-Pro:Downloads vibhor$ ssh  -i vibhor.kumar\@enterprisedb.com.pem root@ec2-107-21-226-121.compute-1.amazonaws.com

Last login: Tue Jan 24 10:35:27 2012 from 70.91.216.177

[root@ip-10-99-86-162 ~]#

[root@ip-10-99-86-162 ~]# hostname

ip-10-99-86-162.ec2.internal

[root@ip-10-99-86-162 ~]#

If you have followed above steps, then you have one Postgres Plus Instance running.

For PEM Server Installation, user can follow steps given below:

1. Download PEM Server Installer from Following link:

http://www.enterprisedb.com/download-postgres-enterprise-manager

2. After Download, Installer use SCP command to copy installer in Postgres Plus Cloud instance, as given below:

edbs-MacBook-Pro:Downloads vibhor$ scp -i   vibhor.kumar\@enterprisedb.com.pem pem_server-2.0.2-1-linux.run root@ec2-107-21-226-121.compute-1.amazonaws.com:~/

pem_server-2.0.2-1-linux.run                                                                                      100%   87MB 702.3KB/s   02:07

3. After copying PEM Server Installer to Postgres Plus Cloud Instance, login at PostgresPlus Cloud using ssh command as given below:

ssh  -i vibhor.kumar\@enterprisedb.com.pem root@ec2-107-21-226-121.compute-1.amazonaws.com

Last login: Tue Jan 24 11:15:00 2012 from 70.91.216.177

[root@ip-10-99-86-162 ~]# ls

pem_server-2.0.2-1-linux.run  start.log

[root@ip-10-99-86-162 ~]#

4. Execute following command on PEM Server Installer to make it executable:

chmod +x pem_server-2.0.2-1-linux.run

5. After executing above command user has to first install Apache-php on server. For installing Apache-php, use can execute following to extract Apache-php installer from PEM Server installer, as given below:

./pem_server-2.0.2-1-linux.run --extract-apache-php .

[root@ip-10-99-86-162 ~]# ls -ltr

total 114076

-rwxr-xr-x 1 root root 25333622 Nov 28 02:43 apachephp-2.2.20-5.3.8-1-linux.run

-rw-r--r-- 1 root root    18846 Jan 24 10:29 start.log

-rwxr-xr-x 1 root root 91332150 Jan 24 11:15 pem_server-2.0.2-1-linux.run

[root@ip-10-99-86-162 ~]#

6. After Extracting Apache-php installer, user can install Apache-php as given below:

./apachephp-2.2.20-5.3.8-1-linux.run

----------------------------------------------------------------------------

Welcome to the ApachePhp Setup Wizard.

----------------------------------------------------------------------------

Please specify the directory where ApachePhp will be installed.

Installation Directory [/opt/PostgreSQL/EnterpriseDB-ApachePhp]:

----------------------------------------------------------------------------

Apache Server Details

Please specify a port on which Apache will run

Apache Port [8080]:

----------------------------------------------------------------------------

Setup is now ready to begin installing ApachePhp on your computer.

Do you want to continue? [Y/n]: Y

----------------------------------------------------------------------------

Please wait while Setup installs ApachePhp on your computer.

Installing ApachePhp

0% ______________ 50% ______________ 100%

#########################################

----------------------------------------------------------------------------

EnterpriseDB is the leading provider of value-added products and services for

the Postgres community.

Please visit our website at <a href="http://www.enterprisedb.com">www.enterprisedb.com</a>

7. After installing Apache-php, now install postgreSQL 9.0 on Server. For installing postgresql 90, use following command to extract and install postgresql 90.

./pem_server-2.0.2-1-linux.run --extract-postgresql .

./postgresql-9.0.5-2-linux.run

----------------------------------------------------------------------------

Welcome to the PostgreSQL Setup Wizard.

----------------------------------------------------------------------------

Please specify the directory where PostgreSQL will be installed.

Installation Directory [/opt/PostgreSQL/9.0]:

----------------------------------------------------------------------------

Please select a directory under which to store your data.

Data Directory [/opt/PostgreSQL/9.0/data]:

----------------------------------------------------------------------------

Please provide a password for the database superuser (postgres). A locked Unix

user account (postgres) will be created if not present.

Password :

Retype password :

----------------------------------------------------------------------------

Please select the port number the server should listen on.

Port [5432]:

----------------------------------------------------------------------------

Advanced Options

Select the locale to be used by the new database cluster.

Locale

[1] [Default locale]

[2] aa_DJ

[3] aa_DJ.iso88591

[4] aa_DJ.utf8

[5] aa_ER

[6] aa_ER@saaho

[7] aa_ER.utf8

[8] aa_ER.utf8@saaho

[9] aa_ET

[10] aa_ET.utf8

[11] af_ZA

[12] af_ZA.iso88591

[13] af_ZA.utf8

[14] am_ET

[15] am_ET.utf8

[16] an_ES

[17] an_ES.iso885915

[18] an_ES.utf8

[19] ar_AE

[20] ar_AE.iso88596

[21] ar_AE.utf8

[22] ar_BH

[23] ar_BH.iso88596

[24] ar_BH.utf8

[25] ar_DZ

[26] ar_DZ.iso88596

[27] ar_DZ.utf8

[28] ar_EG

[29] ar_EG.iso88596

[30] ar_EG.utf8

[31] ar_IN

[32] ar_IN.utf8

[33] ar_IQ

[34] ar_IQ.iso88596

[35] ar_IQ.utf8

[36] ar_JO

[37] ar_JO.iso88596

[38] ar_JO.utf8

[39] ar_KW

[40] ar_KW.iso88596

[41] ar_KW.utf8

[42] ar_LB

[43] ar_LB.iso88596

[44] ar_LB.utf8

[45] ar_LY

[46] ar_LY.iso88596

[47] ar_LY.utf8

[48] ar_MA

[49] ar_MA.iso88596

[50] ar_MA.utf8

[51] ar_OM

[52] ar_OM.iso88596

[53] ar_OM.utf8

[54] ar_QA

[55] ar_QA.iso88596

[56] ar_QA.utf8

[57] ar_SA

[58] ar_SA.iso88596

[59] ar_SA.utf8

[60] ar_SD

[61] ar_SD.iso88596

[62] ar_SD.utf8

[63] ar_SY

[64] ar_SY.iso88596

[65] ar_SY.utf8

[66] ar_TN

[67] ar_TN.iso88596

[68] ar_TN.utf8

[69] ar_YE

[70] ar_YE.iso88596

[71] ar_YE.utf8

[72] as_IN.utf8

[73] az_AZ.utf8

[74] be_BY

[75] be_BY.cp1251

[76] be_BY@latin

[77] be_BY.utf8

[78] be_BY.utf8@latin

[79] bg_BG

[80] bg_BG.cp1251

[81] bg_BG.utf8

[82] bn_BD

[83] bn_BD.utf8

[84] bn_IN

[85] bn_IN.utf8

[86] bokmal

[87] bokmål

[88] br_FR

[89] br_FR@euro

[90] br_FR.iso88591

[91] br_FR.iso885915@euro

[92] br_FR.utf8

[93] bs_BA

[94] bs_BA.iso88592

[95] bs_BA.utf8

[96] byn_ER

[97] byn_ER.utf8

[98] C

[99] ca_AD

[100] ca_AD.iso885915

[101] ca_AD.utf8

[102] ca_ES

[103] ca_ES@euro

[104] ca_ES.iso88591

[105] ca_ES.iso885915@euro

[106] ca_ES.utf8

[107] ca_FR

[108] ca_FR.iso885915

[109] ca_FR.utf8

[110] ca_IT

[111] ca_IT.iso885915

[112] ca_IT.utf8

[113] catalan

[114] croatian

[115] csb_PL

[116] csb_PL.utf8

[117] cs_CZ

[118] cs_CZ.iso88592

[119] cs_CZ.utf8

[120] cy_GB

[121] cy_GB.iso885914

[122] cy_GB.utf8

[123] czech

[124] da_DK

[125] da_DK.iso88591

[126] da_DK.iso885915

[127] da_DK.utf8

[128] danish

[129] dansk

[130] de_AT

[131] de_AT@euro

[132] de_AT.iso88591

[133] de_AT.iso885915@euro

[134] de_AT.utf8

[135] de_BE

[136] de_BE@euro

[137] de_BE.iso88591

[138] de_BE.iso885915@euro

[139] de_BE.utf8

[140] de_CH

[141] de_CH.iso88591

[142] de_CH.utf8

[143] de_DE

[144] de_DE@euro

[145] de_DE.iso88591

[146] de_DE.iso885915@euro

[147] de_DE.utf8

[148] de_LU

[149] de_LU@euro

[150] de_LU.iso88591

[151] de_LU.iso885915@euro

[152] de_LU.utf8

[153] deutsch

[154] dutch

[155] dz_BT

[156] dz_BT.utf8

[157] eesti

[158] el_CY

[159] el_CY.iso88597

[160] el_CY.utf8

[161] el_GR

[162] el_GR.iso88597

[163] el_GR.utf8

[164] en_AU

[165] en_AU.iso88591

[166] en_AU.utf8

[167] en_BW

[168] en_BW.iso88591

[169] en_BW.utf8

[170] en_CA

[171] en_CA.iso88591

[172] en_CA.utf8

[173] en_DK

[174] en_DK.iso88591

[175] en_DK.utf8

[176] en_GB

[177] en_GB.iso88591

[178] en_GB.iso885915

[179] en_GB.utf8

[180] en_HK

[181] en_HK.iso88591

[182] en_HK.utf8

[183] en_IE

[184] en_IE@euro

[185] en_IE.iso88591

[186] en_IE.iso885915@euro

[187] en_IE.utf8

[188] en_IN

[189] en_IN.utf8

[190] en_NZ

[191] en_NZ.iso88591

[192] en_NZ.utf8

[193] en_PH

[194] en_PH.iso88591

[195] en_PH.utf8

[196] en_SG

[197] en_SG.iso88591

[198] en_SG.utf8

[199] en_US

[200] en_US.iso88591

[201] en_US.iso885915

[202] en_US.utf8

[203] en_ZA

[204] en_ZA.iso88591

[205] en_ZA.utf8

[206] en_ZW

[207] en_ZW.iso88591

[208] en_ZW.utf8

[209] es_AR

[210] es_AR.iso88591

[211] es_AR.utf8

[212] es_BO

[213] es_BO.iso88591

[214] es_BO.utf8

[215] es_CL

[216] es_CL.iso88591

[217] es_CL.utf8

[218] es_CO

[219] es_CO.iso88591

[220] es_CO.utf8

[221] es_CR

[222] es_CR.iso88591

[223] es_CR.utf8

[224] es_DO

[225] es_DO.iso88591

[226] es_DO.utf8

[227] es_EC

[228] es_EC.iso88591

[229] es_EC.utf8

[230] es_ES

[231] es_ES@euro

[232] es_ES.iso88591

[233] es_ES.iso885915@euro

[234] es_ES.utf8

[235] es_GT

[236] es_GT.iso88591

[237] es_GT.utf8

[238] es_HN

[239] es_HN.iso88591

[240] es_HN.utf8

[241] es_MX

[242] es_MX.iso88591

[243] es_MX.utf8

[244] es_NI

[245] es_NI.iso88591

[246] es_NI.utf8

[247] es_PA

[248] es_PA.iso88591

[249] es_PA.utf8

[250] es_PE

[251] es_PE.iso88591

[252] es_PE.utf8

[253] es_PR

[254] es_PR.iso88591

[255] es_PR.utf8

[256] es_PY

[257] es_PY.iso88591

[258] es_PY.utf8

[259] es_SV

[260] es_SV.iso88591

[261] es_SV.utf8

[262] estonian

[263] es_US

[264] es_US.iso88591

[265] es_US.utf8

[266] es_UY

[267] es_UY.iso88591

[268] es_UY.utf8

[269] es_VE

[270] es_VE.iso88591

[271] es_VE.utf8

[272] et_EE

[273] et_EE.iso88591

[274] et_EE.iso885915

[275] et_EE.utf8

[276] eu_ES

[277] eu_ES@euro

[278] eu_ES.iso88591

[279] eu_ES.iso885915@euro

[280] eu_ES.utf8

[281] fa_IR

[282] fa_IR.utf8

[283] fi_FI

[284] fi_FI@euro

[285] fi_FI.iso88591

[286] fi_FI.iso885915@euro

[287] fi_FI.utf8

[288] finnish

[289] fo_FO

[290] fo_FO.iso88591

[291] fo_FO.utf8

[292] français

[293] fr_BE

[294] fr_BE@euro

[295] fr_BE.iso88591

[296] fr_BE.iso885915@euro

[297] fr_BE.utf8

[298] fr_CA

[299] fr_CA.iso88591

[300] fr_CA.utf8

[301] fr_CH

[302] fr_CH.iso88591

[303] fr_CH.utf8

[304] french

[305] fr_FR

[306] fr_FR@euro

[307] fr_FR.iso88591

[308] fr_FR.iso885915@euro

[309] fr_FR.utf8

[310] fr_LU

[311] fr_LU@euro

[312] fr_LU.iso88591

[313] fr_LU.iso885915@euro

[314] fr_LU.utf8

[315] fy_NL

[316] fy_NL.utf8

[317] ga_IE

[318] ga_IE@euro

[319] ga_IE.iso88591

[320] ga_IE.iso885915@euro

[321] ga_IE.utf8

[322] galego

[323] galician

[324] gd_GB

[325] gd_GB.iso885915

[326] gd_GB.utf8

[327] german

[328] gez_ER

[329] gez_ER@abegede

[330] gez_ER.utf8

[331] gez_ER.utf8@abegede

[332] gez_ET

[333] gez_ET@abegede

[334] gez_ET.utf8

[335] gez_ET.utf8@abegede

[336] gl_ES

[337] gl_ES@euro

[338] gl_ES.iso88591

[339] gl_ES.iso885915@euro

[340] gl_ES.utf8

[341] greek

[342] gu_IN

[343] gu_IN.utf8

[344] gv_GB

[345] gv_GB.iso88591

[346] gv_GB.utf8

[347] hebrew

[348] he_IL

[349] he_IL.iso88598

[350] he_IL.utf8

[351] hi_IN

[352] hi_IN.utf8

[353] hr_HR

[354] hr_HR.iso88592

[355] hr_HR.utf8

[356] hrvatski

[357] hsb_DE

[358] hsb_DE.iso88592

[359] hsb_DE.utf8

[360] hu_HU

[361] hu_HU.iso88592

[362] hu_HU.utf8

[363] hungarian

[364] hy_AM

[365] hy_AM.utf8

[366] icelandic

[367] id_ID

[368] id_ID.iso88591

[369] id_ID.utf8

[370] is_IS

[371] is_IS.iso88591

[372] is_IS.utf8

[373] italian

[374] it_CH

[375] it_CH.iso88591

[376] it_CH.utf8

[377] it_IT

[378] it_IT@euro

[379] it_IT.iso88591

[380] it_IT.iso885915@euro

[381] it_IT.utf8

[382] iw_IL

[383] iw_IL.iso88598

[384] iw_IL.utf8

[385] ja_JP

[386] ja_JP.eucjp

[387] ja_JP.ujis

[388] ja_JP.utf8

[389] japanese

[390] japanese.euc

[391] ka_GE.utf8

[392] kk_KZ.utf8

[393] kl_GL

[394] kl_GL.iso88591

[395] kl_GL.utf8

[396] km_KH

[397] km_KH.utf8

[398] kn_IN

[399] kn_IN.utf8

[400] ko_KR

[401] ko_KR.euckr

[402] ko_KR.utf8

[403] korean

[404] korean.euc

[405] ku_TR

[406] ku_TR.iso88599

[407] ku_TR.utf8

[408] kw_GB

[409] kw_GB.iso88591

[410] kw_GB.utf8

[411] ky_KG

[412] ky_KG.utf8

[413] lg_UG

[414] lg_UG.iso885910

[415] lg_UG.utf8

[416] lithuanian

[417] lo_LA

[418] lo_LA.utf8

[419] lt_LT

[420] lt_LT.iso885913

[421] lt_LT.utf8

[422] lv_LV

[423] lv_LV.iso885913

[424] lv_LV.utf8

[425] mai_IN

[426] mai_IN.utf8

[427] mg_MG

[428] mg_MG.iso885915

[429] mg_MG.utf8

[430] mi_NZ

[431] mi_NZ.iso885913

[432] mi_NZ.utf8

[433] mk_MK

[434] mk_MK.iso88595

[435] mk_MK.utf8

[436] ml_IN

[437] ml_IN.utf8

[438] mn_MN

[439] mn_MN.utf8

[440] mr_IN

[441] mr_IN.utf8

[442] ms_MY

[443] ms_MY.iso88591

[444] ms_MY.utf8

[445] mt_MT

[446] mt_MT.iso88593

[447] mt_MT.utf8

[448] nb_NO

[449] nb_NO.iso88591

[450] nb_NO.utf8

[451] ne_NP

[452] ne_NP.utf8

[453] nl_BE

[454] nl_BE@euro

[455] nl_BE.iso88591

[456] nl_BE.iso885915@euro

[457] nl_BE.utf8

[458] nl_NL

[459] nl_NL@euro

[460] nl_NL.iso88591

[461] nl_NL.iso885915@euro

[462] nl_NL.utf8

[463] nn_NO

[464] nn_NO.iso88591

[465] nn_NO.utf8

[466] no_NO

[467] no_NO.iso88591

[468] no_NO.utf8

[469] norwegian

[470] nr_ZA

[471] nr_ZA.utf8

[472] nso_ZA

[473] nso_ZA.utf8

[474] nynorsk

[475] oc_FR

[476] oc_FR.iso88591

[477] oc_FR.utf8

[478] om_ET

[479] om_ET.utf8

[480] om_KE

[481] om_KE.iso88591

[482] om_KE.utf8

[483] or_IN

[484] or_IN.utf8

[485] pa_IN

[486] pa_IN.utf8

[487] pa_PK

[488] pa_PK.utf8

[489] pl_PL

[490] pl_PL.iso88592

[491] pl_PL.utf8

[492] polish

[493] portuguese

[494] POSIX

[495] pt_BR

[496] pt_BR.iso88591

[497] pt_BR.utf8

[498] pt_PT

[499] pt_PT@euro

[500] pt_PT.iso88591

[501] pt_PT.iso885915@euro

[502] pt_PT.utf8

[503] romanian

[504] ro_RO

[505] ro_RO.iso88592

[506] ro_RO.utf8

[507] ru_RU

[508] ru_RU.iso88595

[509] ru_RU.koi8r

[510] ru_RU.utf8

[511] russian

[512] ru_UA

[513] ru_UA.koi8u

[514] ru_UA.utf8

[515] rw_RW

[516] rw_RW.utf8

[517] se_NO

[518] se_NO.utf8

[519] sid_ET

[520] sid_ET.utf8

[521] si_LK

[522] si_LK.utf8

[523] sk_SK

[524] sk_SK.iso88592

[525] sk_SK.utf8

[526] slovak

[527] slovene

[528] slovenian

[529] sl_SI

[530] sl_SI.iso88592

[531] sl_SI.utf8

[532] so_DJ

[533] so_DJ.iso88591

[534] so_DJ.utf8

[535] so_ET

[536] so_ET.utf8

[537] so_KE

[538] so_KE.iso88591

[539] so_KE.utf8

[540] so_SO

[541] so_SO.iso88591

[542] so_SO.utf8

[543] spanish

[544] sq_AL

[545] sq_AL.iso88591

[546] sq_AL.utf8

[547] sr_CS

[548] sr_CS.iso88595

[549] sr_CS.utf8

[550] sr_ME

[551] sr_ME.utf8

[552] sr_RS

[553] sr_RS@latin

[554] sr_RS.utf8

[555] sr_RS.utf8@latin

[556] ss_ZA

[557] ss_ZA.utf8

[558] st_ZA

[559] st_ZA.iso88591

[560] st_ZA.utf8

[561] sv_FI

[562] sv_FI@euro

[563] sv_FI.iso88591

[564] sv_FI.iso885915@euro

[565] sv_FI.utf8

[566] sv_SE

[567] sv_SE.iso88591

[568] sv_SE.iso885915

[569] sv_SE.utf8

[570] swedish

[571] ta_IN

[572] ta_IN.utf8

[573] te_IN

[574] te_IN.utf8

[575] tg_TJ.utf8

[576] th_TH.utf8

[577] ti_ER

[578] ti_ER.utf8

[579] ti_ET

[580] ti_ET.utf8

[581] tig_ER

[582] tig_ER.utf8

[583] tl_PH

[584] tl_PH.iso88591

[585] tl_PH.utf8

[586] tn_ZA

[587] tn_ZA.utf8

[588] tr_CY

[589] tr_CY.iso88599

[590] tr_CY.utf8

[591] tr_TR

[592] tr_TR.iso88599

[593] tr_TR.utf8

[594] ts_ZA

[595] ts_ZA.utf8

[596] tt_RU.utf8

[597] turkish

[598] uk_UA

[599] uk_UA.koi8u

[600] uk_UA.utf8

[601] ur_PK

[602] ur_PK.utf8

[603] uz_UZ

[604] uz_UZ@cyrillic

[605] uz_UZ.iso88591

[606] uz_UZ.utf8@cyrillic

[607] ve_ZA

[608] ve_ZA.utf8

[609] vi_VN

[610] vi_VN.utf8

[611] wa_BE

[612] wa_BE@euro

[613] wa_BE.iso88591

[614] wa_BE.iso885915@euro

[615] wa_BE.utf8

[616] xh_ZA

[617] xh_ZA.iso88591

[618] xh_ZA.utf8

[619] yi_US

[620] yi_US.cp1255

[621] yi_US.utf8

[622] zh_CN

[623] zh_CN.gb2312

[624] zh_CN.utf8

[625] zh_HK.utf8

[626] zh_SG

[627] zh_SG.gb2312

[628] zh_SG.utf8

[629] zh_TW.euctw

[630] zh_TW.utf8

[631] zu_ZA

[632] zu_ZA.iso88591

[633] zu_ZA.utf8

Please choose an option [1] :

----------------------------------------------------------------------------

Setup is now ready to begin installing PostgreSQL on your computer.

Do you want to continue? [Y/n]:

----------------------------------------------------------------------------

Please wait while Setup installs PostgreSQL on your computer.

Installing

0% ______________ 50% ______________ 100%

#########################################

----------------------------------------------------------------------------

Setup has finished installing PostgreSQL on your computer.

8. Now, user can install PEM Server on Postgres Plus Cloud, using following command,( snapshot is given below):

./pem_server-2.0.2-1-linux.run

Snapshot of Installation process is given below:

/pem_server-2.0.2-1-linux.run

----------------------------------------------------------------------------

Welcome to the Postgres Enterprise Manager (PEM) Server Setup Wizard.

----------------------------------------------------------------------------

Please read the following License Agreement. You must accept the terms of this

agreement before continuing with the installation.

Press [Enter] to continue :

Limited Use Software License Agreement

Version 2.4

IMPORTANT - READ CAREFULLY

This Limited Use Software License Agreement ("Agreement") is a legal document

between you ("Customer") and EnterpriseDB Corporation ("EnterpriseDB"). It is

important that you read this document before using the EnterpriseDB-provided

software ("Software"). By clicking the "I ACCEPT" button, or by installing, or
<ul>
	<li>otherwise using the Software, Customer agrees to be bound by the terms of this</li>
</ul>
Agreement, including, without limitation, the warranty disclaimers, limitations
<ul>
	<li>of liability and termination provisions below. Customer agrees that this</li>
</ul>
Agreement is enforceable like any written agreement negotiated and signed by

Customer. If Customer does not agree with the terms and conditions of this

Agreement, Customer is not licensed to use the Software, and Customer must

destroy any downloaded copies of the Software in its possession or control. This

Agreement will not apply if Customer has: a) a valid, paid subscription to

PostgreSQL, Postgres Plus Standard Server, or Postgres Plus Advanced Server, and

in that case, the terms of the Full Use Software License Agreement will apply,
<ul>
	<li>or b) purchased a Full Use Software License, and in that case, the terms of the</li>
</ul>
Full Use Software License Agreement will apply.
<ol>
	<li>     Scope of License. Subject to the terms and conditions of this Agreement,</li>
</ol>
Press [Enter] to continue :

EnterpriseDB grants to Customer a non-exclusive, non-transferable right to

install a single instance of the Software on one (1) server. Notwithstanding the

previous sentence, under the terms of this Agreement, Postgres Enterprise

Manager may be installed on multiple computers. The Software may only be used

solely for internal evaluation purposes ("Authorized Use") during the time that

Customer is current in the payment of the applicable subscription fees or if no

subscription fees are due, then this license will remain in effect until this

agreement is terminated as set forth in Section 9. Evaluation purposes do not

include the right to use the Software for production use, sublicensing, resale,
<ul>
<ul>
	<li>or distribution, including without limitation, operation on a time sharing,</li>
</ul>
</ul>
software as a service or service bureau basis or distributing the Software as

part of an ASP, VAR, OEM, distributor or reseller arrangement.
<ol>
	<li>     License Restrictions. Customer agrees not to: (a) copy or use the Software</li>
</ol>
in any manner except as expressly permitted in this Agreement; (b) transfer,

sell, rent, lease, distribute, or sublicense the Software; (c) use the Software

for providing time-sharing services, service bureau services or as part of an

application services provider or software as a service offering; (d) reverse

engineer, disassemble, decompile the Software; (e) alter or remove any

proprietary notices in the Software; (f) make available to any third party any

analysis of the results of operation of the Software, including benchmarking

results, without the prior written consent of EnterpriseDB. Customer may make
<ul>
	<li>one additional copy of the Software for backup or archival purposes provided</li>
</ul>
Press [Enter] to continue :

that the Authorized Use is not exceeded. If Customer would like to change the

level of Authorized Use, Customer will need to enter into the appropriate

EnterpriseDB license and pay the applicable fees.
<ol>
<ul>
	<li>     Ownership. EnterpriseDB and its licensors retain all right, title and</li>
</ul>
</ol>
interest in and to the Software and any modifications and enhancements to the

Software and all Upgrades, including all intellectual property rights that are

not expressly granted in this Agreement.
<ol>
<ul>
	<li>     Open Source Programs. The Software is distributed with third party open</li>
</ul>
</ol>
source software programs as described in the licenses directory of the Software.

These open source programs are distributed under open source licenses and not

this Agreement.
<ol>
	<li>Verification. Customer acknowledges that the Software may include</li>
</ol>
functionality that notifies Customer of the availability of updates and collects

and reports certain information about the use of the Software to EnterpriseDB.

Customer will provide EnterpriseDB with documentation concerning transactions

related to the Software within thirty (30) days after written request.  In

addition, upon at least thirty (30) days prior written notice, EnterpriseDB or

its designated agent may inspect and review CustomerÕs facilities and records

in order to verify CustomerÕs compliance with this Agreement.

Press [Enter] to continue :
<ol>
<ul>
	<li>DISCLAIMER OF WARRANTIES.  ENTERPRISEDB PROVIDES THE SOFTWARE TO YOU "AS IS".</li>
</ul>
</ol>
ENTERPRISEDB DISCLAIMS ALL WARRANTIES OF ANY KIND, WHETHER EXPRESS OR IMPLIED,

INCLUDING, WITHOUT LIMITATION, THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A

PARTICULAR PURPOSE, AND NONINFRINGEMENT, AND ALL WARRANTIES THAT MAY ARISE FROM

COURSE OF PERFORMANCE, COURSE OF DEALING OR USAGE OF TRADE.
<ol>
	<li>LIMITATIONS OF LIABILITY.  Notwithstanding any other clause in this</li>
</ol>
Agreement, in no event will EnterpriseDB be liable for any special, indirect,

incidental, punitive or consequential damages (including, without limitation,

any failure to realize savings or other benefits; any loss of use; or any claims

made by or any payments made to any third person), any loss of revenue or

profits, any loss and/or damage arising from or in connection with a virus, or

any loss of data and/or damage arising there from or relating thereto, in each

case arising from or in connection with this Agreement or the use or performance
<ul>
<ul>
	<li>of any Software whether in an action based on contract, tort or any other legal</li>
</ul>
</ul>
theory, whether or not EnterpriseDB has been notified of the possibility

thereof. Notwithstanding any other clause in this Agreement, in no event will

EnterpriseDB's total aggregate liability for any damages arising from or in

connection with this Agreement or the use or performance of any Software whether

in actions based on contract, tort or any other legal theory, and whether or not

EnterpriseDB has been notified of the possibility thereof, exceed the amount

paid under this Agreement during the twelve (12) month period preceding the date
<ul>
	<li>of the claim.  The foregoing limitations, exclusions and disclaimers are an</li>
</ul>
Press [Enter] to continue :

allocation of the risk between the parties and will apply to the maximum extent

permitted by applicable law, even if any remedy fails in its essential purpose.
<ol>
	<li>Government Rights.  The Software under this Agreement is "commercial computer</li>
</ol>
software" as that term is described in DFAR 252.227-7014(a) (1).  If acquired by
<ul>
	<li>or on behalf of a civilian agency, the U.S. Government acquires this commercial</li>
</ul>
computer software and/or commercial computer software documentation subject to

the terms and this Agreement as specified in 48C.F.R. 12.212 (Computer Software)

and 12.11 (Technical Data) of the Federal Acquisition Regulations ("FAR") and

its successors.  If acquired by or on behalf of any agency within the Department
<ul>
	<li>of Defense ("DOD"), the U.S. Government acquires this commercial computer</li>
</ul>
software and/or commercial computer software documentation subject to the terms
<ul>
	<li>of this Agreement as specified in 48 C.F.R. 227.7202 of the DOD FAR Supplement</li>
</ul>
and its successors.

9. Term and Termination. This Agreement is effective as of the date this

Agreement is accepted by Customer and will continue for (i) sixty (60) days,

(ii) forty-five (45) days for Postgres Enterprise Manager, or (iii) until

terminated by either party by giving written notice to the other, whichever

circumstance occurs first. In the event of a termination of this Agreement,

Customer must de-install all Software and cease all use of the Software.

Sections 2, 3, 4, 5, 6, 7, 8, 9 and 10 will survive the termination of this
<ol>
<ul>
	<li>In addition, Customer will pay EnterpriseDB all monies that become</li>
</ul>
</ol>
Press [Enter] to continue :

due prior to the date of termination.
<ol>
	<li>Miscellaneous.</li>
</ol>
10.1     Entire Agreement. This Agreement constitutes the entire agreement between

the parties concerning the subject matter hereof, notwithstanding any different
<ul>
	<li>or additional terms that may be contained in the form of purchase order or other</li>
</ul>
document used by Customer to place orders or otherwise effect transactions
<ol>
	<li>This Agreement supersedes all prior or contemporaneous discussions,</li>
</ol>
proposals and agreements between the parties relating to the subject matter of

this Agreement. No amendment, modification or waiver of any provision of this

Agreement will be effective unless in writing and signed by both parties.

10.2     Severability. If any provision of this Agreement is held to be invalid or

unenforceable, the remaining portions will remain in full force and effect and

such provision will be enforced to the maximum extent possible so as to effect

the intent of the parties and will be reformed to the extent necessary to make

such provision valid and enforceable provided, however, that if Sections 6 and 7

cannot be modified to be valid and enforceable, this Agreement will be deemed

invalid in its entirety.

10.3     Force Majeure. Neither party will be liable or deemed to be in breach for

any delay or failure in performance of this Agreement (except for the payment of

Press [Enter] to continue :

money) or interruption of services resulting directly or indirectly from acts of

God, civil or military authority, war, riots, civil disturbances, accidents,

fire, earthquake, floods, strikes, lock-outs, labor disturbances, foreign or

governmental order, or any other cause beyond the reasonable control of such

party.

10.4     Governing Law and Venue. This Agreement will be governed by the laws of New

York without regard for its choice of law provisions.  All disputes arising out
<ul>
	<li>of or relating to this Agreement will be submitted to the exclusive jurisdiction</li>
	<li>of the state or federal courts of New York, and each party irrevocably consents</li>
</ul>
to such personal jurisdiction and waives all objections to this venue.

10.5     Export Regulations. Customer will comply fully with all export control laws

and regulations of the United States and all other jurisdictions.

10.6     Assignment. Neither party may assign this Agreement without the prior

written consent of the other party, which consent will not be unreasonably

withheld, provided that no consent will be necessary if this Agreement is being

assigned by a party to an acquirer of all or substantially all of the party's

assets (or the assets of the party's applicable business unit), whether by

merger, sale or exchange of stock, sale of assets or otherwise and in this case,

the party may assign this Agreement by providing written notice to the other

party.

Press [Enter] to continue :

10.7     Marketing. EnterpriseDB may use CustomerÕs name and company logo on its

customer list and web site, and link to CustomerÕs web site.

10.8     Independent Contractor. The relationship of the parties is that of

independent contractors.  Neither party will be deemed to be the legal

representative of the other nor will it have any right to bind the other party

to any contract or commitment.  This Agreement does not, and will not, be

construed to create an employer-employee, agency, joint venture or partnership

relationship between the parties.  Each party agrees to assume complete

responsibility for its own employees regarding federal or state laws, including

employers' liability and tax withholding, worker's compensation, social

security, unemployment insurance, and OSHA requirements.

10.9     Notice. All notices and other communications herein permitted or required

under this Agreement will be sent by postage prepaid, via registered or

certified mail or overnight courier, return receipt requested, or delivered

personally to the parties at their respective addresses, or to such other

address as either party will give to the other party in the manner provided

herein for giving notice.  Notice will be considered given upon receipt.

Press [Enter] to continue :

Do you accept this license? [y/n]: y

----------------------------------------------------------------------------

Please select a directory for the PEM server installation.

Installation Directory [/opt/PEM]:

----------------------------------------------------------------------------

EnterpriseDB User Account Information

Please enter the email address and password for your enterprisedb.com user

account.

Email address []: vibhor.aim@gmail.com

Password :

----------------------------------------------------------------------------

PostgreSQL Installation Details

Please verify the password for the user 'postgres' of the local PostgreSQL

server installation running on port 5432.

Password :

----------------------------------------------------------------------------

Network Details

Please enter the CIDR formatted network address range that agents will connect

to the server from, to be added to the server's pg_hba.conf file. For example,

192.168.1.0/24.

Network address [127.0.0.1/32]: 0.0.0.0/0   --- To allow access from other Servers.

----------------------------------------------------------------------------

Agent Details.

Please specify a description for the agent.

Description. [Postgres Enterprise Manager Host]:

----------------------------------------------------------------------------

Setup is now ready to begin installing the PEM server on your computer.

Do you want to continue? [Y/n]:

----------------------------------------------------------------------------

Please wait while Setup installs the PEM server on your computer.

Installing

0% ______________ 50% ______________ 100%

#########################################

9. After installing PEM Server, user can use PEM Client to access/ Monitor Server.

Some Snapshots are given below:

Its very easy to Create and Install PEM Server on Postgres Plus Cloud.

2011 in review

The WordPress.com stats helper prepared a 2011 annual report for my blog. Stats seems good to me, so sharing this report with readers.

Here’s an excerpt:

A New York City subway train holds 1,200 people. This blog was viewed about 7,500 times in 2011. If it were a NYC subway train, it would take about 6 trips to carry that many people.

Click here to see the complete report.

Workaround for SMTP Exceptions in PPAS9.0

Till PPAS 9.0, UTL_SMTP package has no specific named Exceptions for Transient_error, Invalid_operation and Permanent Error. However, Oracle provide following types of named exceptions for SMTP:
1. INVALID_OPERATION:
Raised when an invalid operation is made. In other words, calling API other than write_data(), write_raw_data() or close_data() after open_data() is called, or calling write_data(), write_raw_data() or close_data() without first calling open_data().

2. TRANSIENT_ERROR:
Raised when receiving a reply code in 400 range.

3. PERMANENT_ERROR:
Raised when receiving a reply code in 500 range.

Oracle users who use SMTP packages, they also use above exceptions extensively to track the SMTP Error/Message and perform some handling on basis of exception.

Since, till now PPAS doesn’t have these exceptions, therefore people stuck on finding workaround for such exception.

To make this easy, I did some research and made following workaround, which user can use in PPAS for SMTP Exceptions as they do in Oracle.

Following are workarounds for SMTP named Exceptions:

There is no direct way to trap SMTP reply code, However PPAS SQLERRM does have description of SMTP Reply code. So using SQLERRM, user can implement or defined named Exceptions, which is pretty simple and straight.

Easiest way of doing is to create functions, which can decide if exception error is TRANSIENT, PERMANENT or INVALID_OPERATION. So, user can create following functions in PPAS:

1. Function For transient Error Message:

CREATE OR REPLACE FUNCTION SMTP_TRANSIENT_ERROR(text) RETURNS boolean
As
$$
SELECT CASE WHEN
$1 ILIKE '%Service not available%' OR 
$1 ILIKE '%Requested mail action not taken%' OR 
$1 ILIKE '%Requested action terminated%' OR
$1 ILIKE '%Requested action not taken%' OR
$1 ILIKE '%You have no mail%' OR
$1 ILIKE '%TLS not available due to temporary reason%. Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Unable to queue messages for %'  OR
$1 ILIKE '%Node%not allowed%' THEN true
ELSE false END from dual;
$$ language sql;

2. Function for Permanent Error Message:

CREATE OR REPLACE FUNCTION SMTP_PERMANENT_ERROR(text) RETURNS Boolean 
AS
$$
SELECT CASE WHEN 
$1 ILIKE '%Syntax error%command unrecognized%' OR 
$1 ILIKE '%Syntax error in parameters or arguments%' OR
$1 ILIKE '%Command not implemented%' OR
$1 ILIKE '%Bad sequence of commands%' OR
$1 ILIKE '%Command parameter not implemented%' OR
$1 ILIKE '%does not accept mail%' OR
$1 ILIKE '%Must issue a STARTTLS command first. Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Authentication mechanism is too weak%' OR
$1 ILIKE '%Encryption required for requested authentication mechanism%' OR
$1 ILIKE '%Requested action not taken%' OR
$1 ILIKE '%User not local; please try%' OR 
$1 ILIKE '%Requested mail action terminated: exceeded storage allocation%' OR
$1 ILIKE '%Requested action not taken:%' OR
$1 ILIKE '%Transaction failed%' THEN true ELSE false END FROM DUAL;
$$ language sql

3. Function for INVALID_OPERATION

CREATE OR REPLACE FUNCTION SMTP_INVALID_OPERATION(TEXT) RETURNS BOOL
AS
$$ SELECT CASE WHEN $ ILIKE '%INVALID%OPERATION%STATE%' THEN TRUE ELSE FALSE END FROM DUAL;
$$ Language sql;

Below are some working examples:

In Oracle (INVALID_OPERATION) :

CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'Oracle9.2';
 mailhost    VARCHAR2(30) := '127.0.0.1';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN UTL_SMTP.INVALID_OPERATION THEN
      dbms_output.put_line(' Invalid Operation in Mail attempt    
                             using UTL_SMTP.');
   WHEN UTL_SMTP.TRANSIENT_ERROR THEN
      dbms_output.put_line(' Temporary e-mail issue - try again'); 
   WHEN UTL_SMTP.PERMANENT_ERROR THEN
      dbms_output.put_line(' Permanent Error Encountered.'); 
END;

SQL> exec send_mail(msg_to=>'vibhor.aim@gmail.com', -
              msg_subject => 'Hello from Oracle', -
              msg_text    => 'This is the body of the message'-
           );
Invalid Operation in Mail attempt
			      using UTL_SMTP.

PL/SQL procedure successfully completed.

In PPAS:

CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'PPAS 9.0';
 mailhost    VARCHAR2(30) := '127.0.0.1';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN OTHERS THEN
          IF SMTP_INVALID_OPERATION(SQLERRM) THEN
         DBMS_OUTPUT.PUT_LINE('Invalid Operation in Mail attempt using UTL_SMTP.');
         ELSIF SMTP_TRANSIENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Temporary e-mail issue - try again'); 
         ELSIF SMTP_PERMANENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Permanent Error Encountered.');
         ELSE
            DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' Error Message: '||SQLERRM);
        END IF;
END;


EDB-SPL Procedure successfully completed
edb=# exec send_mail(msg_to=>'vibhor.aim@gmail.com', 
edb(#                msg_subject => 'Hello from PPAS', 
edb(#                msg_text    => 'This is the body of the message');
Invalid Operation in Mail attempt using UTL_SMTP.

EDB-SPL Procedure successfully completed

Example 2: In Oracle (UTL_SMTP.TRANSIENT_ERROR ):

CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'Oracle9.2';
 mailhost    VARCHAR2(30) := '192.168.23.25';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN UTL_SMTP.INVALID_OPERATION THEN
      dbms_output.put_line(' Invalid Operation in Mail attempt    
                             using UTL_SMTP.');
   WHEN UTL_SMTP.TRANSIENT_ERROR THEN
      dbms_output.put_line(' Temporary e-mail issue - try again'); 
   WHEN UTL_SMTP.PERMANENT_ERROR THEN
      dbms_output.put_line(' Permanent Error Encountered.'); 
END;



SQL> exec send_mail(msg_to=>'vibhor.aim@gmail.com', -
              msg_subject => 'Hello from Oracle', -
              msg_text    => 'This is the body of the message'-
           );> > > 
Temporary e-mail issue - try again

PL/SQL procedure successfully completed.

In PPAS:

CREATE OR REPLACE PROCEDURE SEND_MAIL (
 msg_to      varchar2,
 msg_subject varchar2,
 msg_text    varchar2   )
IS
 c  utl_smtp.connection;
 rc integer;
 msg_from    varchar2(50) := 'PPAS 9.0';
 mailhost    VARCHAR2(30) := '192.168.23.25';   -- local database host

BEGIN
 c := utl_smtp.open_connection(mailhost, 25); -- SMTP on port 25    
 utl_smtp.helo(c, mailhost);
 utl_smtp.mail(c, msg_from);
 utl_smtp.rcpt(c, msg_to);
utl_smtp.write_data(c, 'MESSG'||UTL_TCP.CRLF); --Added By me

 utl_smtp.data(c,'From: Oracle Database' || utl_tcp.crlf ||
                        'To: ' || msg_to || utl_tcp.crlf ||
                        'Subject: ' || msg_subject ||
                         utl_tcp.crlf || msg_text);
 utl_smtp.quit(c);

 EXCEPTION
   WHEN OTHERS THEN
          IF SMTP_INVALID_OPERATION(SQLERRM) THEN
         DBMS_OUTPUT.PUT_LINE('Invalid Operation in Mail attempt using UTL_SMTP.');
         ELSIF SMTP_TRANSIENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Temporary e-mail issue - try again'); 
         ELSIF SMTP_PERMANENT_ERROR(SQLERRM) THEN
              DBMS_OUTPUT.PUT_LINE(' Permanent Error Encountered.');
         ELSE
            DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE||' Error Message: '||SQLERRM);
        END IF;
END;


edb=# exec send_mail(msg_to=>'vibhor.aim@gmail.com', 
edb(#                msg_subject => 'Hello from PPAS', 
edb(#                msg_text    => 'This is the body of the message');
Temporary e-mail issue - try again

EDB-SPL Procedure successfully completed
edb=# 

Have fun!

Postgres Plus Advanced Server 9.1 Beta 1

PPAS 9.1 Beta 1 has been released. So, I thought to write about it and New Features which are coming.

Best part of PPAS 9.1 is that it has all features of PostgreSQL 9.1, which I have already discribed in my series of 9.1. List is given below:

1. PostgreSQL Object Manipulation Features

2. New Replication and Recovery Features

3. Queries improvement of Core PostgreSQL 9.1

4. Updateable Views using INSTEADOF Trigger in PostgreSQL 9.1

5. Utility Operation Improvements

6. Upsert/Merge using Writeable CTE

7. New Functions/Improvement

8. Object Functions Improvement in PostgreSQL 9.1

9. Client Application Improvement in PostgreSQL 9.1

10. Asynchronous/Synchronous Replication

There are some other features in PostgreSQL 9.1. List of those Improvements/features is given below:

1. Unlogged Tables

2. FOREACH IN ARRAY in PL/pgSQL

3. Pl/Perl Improvement:

 Record type support
 Pl/perl array argument map to Perl Argument.
 Pl/perl Composite-type array to Perl Hash.</strong>

4. Pl/Python Improvement:

 Table Support in Pl/Python Function
 New Validator for Pl/Python
 SQL Exception handling in Pl/Python Exception Blocks
 Subtransactions in Pl/Python
 New Pl/Ptyhon functions for Quoting String (plpy.quote_ident, plpy.quote_literal, and plpy.quote_nullable)
 Traceback support for Plpython errors</strong><strong>
 Exception Handling in Python3</strong>

5. ECPG Improvements:

 WHERE CURRENT OF in Dynamic Cursor
 Double digit with percision of 15</strong>

6. Libpq Improvements:

client_encoding option in Connection
PQlibVersion() function for knowing Version of Library
 PQping and PQpingParams for knowing Server Status.

With Above Features, PPAS 9.1 has additional Features and Improvement, which users would like to have.

List of those Features is given below:

1.VPD (Virtual Private Database) Support 

2. Parition Syntax Support

CREATE TABLE employees
 (empno numeric(4,0),
 ename varchar(10),
 job varchar(9),
 hiredate timestamp,
 )
PARTITION BY RANGE (hiredate)
 (PARTITION ten_year_staff
 VALUES LESS THAN('01-JAN-2001'),
 (PARTITION five_year_staff
 VALUES LESS THAN('01-JAN-2006'),
 (PARTITION one_year_staff
 VALUES LESS THAN('01-JAN-2011'));

CREATE TABLE employees
 (empno numeric(4,0),
 ename varchar(10),
 job varchar(9),
 hiredate timestamp,
)
PARTITION BY RANGE (hiredate)
SUBPARTITION BY LIST (job)
(PARTITION ten_year_staff VALUES LESS THAN('01-JAN-2001)
(SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'), SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'), SUBPARTITION support VALUES ('ANALYST')),
(PARTITION five_year_staff VALUES LESS THAN('01-JAN-2006') (SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'),
 SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'),
SUBPARTITION support VALUES ('ANALYST')),
(PARTITION one_year_staff VALUES LESS THAN('01-JAN-2011')
(SUBPARTITION mgmt VALUES ('MANAGER', 'PRESIDENT'), SUBPARTITION sales VALUES ('SALESMAN', 'CLERK'), SUBPARTITION support VALUES ('ANALYST'));

etc.

3. HextoRaw and RAWToHEX function support

4. New Hint: ORDERED

5. Ref Cursor as OUT in Procedure and Functions

6. WHEVEVER SQLERROR Syntax Support

7. Advanced Server Performance

Improvement in Lock Management for Read and Write workloads with more CPUs
Index Advisor Support for Composite Indexes

8. New Catalog Views for Partition Table

9. OCI Function: OCIBreak() and OCIReset() for sever connections on Blocking and nonBlocking Mode.

I would be discussing about above features in coming Blogs. So, stay tune!