How to Change System time and timezone in linux ( IST )

1. check current time .
2.change the date and time using below date command.

[root@pgham postgresql-9.3beta2]# date -s "5 Jul 2013 18:18:14"

Fri Jul  5 18:18:14 PDT 2013

3.change the time zone of the computer.

[root@pgham postgresql-9.3beta2]# cd /etc/

[root@pgham etc]# ln -sf /usr/share/zoneinfo/Asia/Calcutta localtime

4. verify date changed or not.

[root@pgham etc]# date

Fri Jul  5 18:22:29 IST 2013

[root@pgham etc]#

Table Partitioning on PostgreSQL

Table Partitioning on PostgreSQL:
+++++++++++++++++++++++
1)We are using Inheritance consept here to achive partitioning tables.
2)And also we have to use (function and triggers) or Rules to populate the data into partitioned tables.
3)Recomended to dont create any check onstarints or indexes on master table,if those are not comman for all child tables.

All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constr
aints) are not inherited.

Master Table:
---------------
DB1=# create table master1(id int,name text);
CREATE TABLE

Child tables:
---------------
DB1 =# CREATE  TABLE child100(check(id >0 and id<=100))inherits(master1);
CREATE TABLE
DB1 =# CREATE  TABLE child200(check(id >100 and id<=200))inherits(master1);
CREATE TABLE
DB1 =# CREATE  TABLE child300(check(id >200 and id<=300))inherits(master1);
CREATE TABLE

Indexes:
------------
DB1 =# CREATE INDEX child100_id_indx on child100(id);
CREATE INDEX
DB1 =# CREATE INDEX child200_id_indx on child200(id);
CREATE INDEX
DB1 =# CREATE INDEX child300_id_indx on child300(id);

Function & Trigger to achive the data to be populated into partationed tables:
------------------------------------------------------------------------------
CREATE or replace function partirion_check() returns trigger as $$
begin
if (NEW.id >0 and NEW.id <= 100)
then
insert into child100 values(NEW.*);
elsif (NEW.id >100 and NEW.id <= 200) 
then
insert into child200 values(NEW.*);
elsif (NEW.id >200 and NEW.id <= 300)
then
insert into child300 values(NEW.*);
else
RAISE EXCEPTION 'fix the partirion_check() function';
end if;
return null;
end;
$$ language plpgsql;

Triggers:
---------
DB1 =# CREATE  trigger partation_insert before insert on master1  for each row execute procedure partirion_check();
CREATE TRIGGER
DB1 =#

(OR)Instead of trigger and function we can use Rules to achive  partitioned data here:
--------------------------------------------------------------------------------------
DB1 =# CREATE RULE child100_insert as on insert to master1 where (id >0 and id<=100) do instead insert into child100 values(NEW.*);
CREATE RULE

Testing paratitioned tables:
------------------------------------
DB1 =# INSERT INTO master1 VALUES (100,'OPEN');
INSERT 0 0
DB1 =# INSERT INTO master1 VALUES (200,'OPEN');
INSERT 0 0
DB1 =# INSERT INTO master1 VALUES (300,'OPEN');
INSERT 0 0
DB1 =# SELECT * FROM master1;
 id  | name 
-----+------
 100 | OPEN
 200 | OPEN
 300 | OPEN
(3 rows)

DB1 =# SELECT * from child100;
 id  | name 
-----+------
 100 | OPEN
(1 row)

DB1 =# SELECT * from child200;
 id  | name 
-----+------
 200 | OPEN
(1 row)

DB1 =# SELECT * from child300;
 id  | name 
-----+------
 300 | OPEN
(1 row)


Note :

1) Maintenance Operations we needed to perform individually for all child tables tables.

Vacuum,Analyze,Freeze,Reidex .....etc.

2) Make sure that constraint_exclusion parameter should be "on"  or  "partition".


Case 1)  off ,it will give bad performance on queries.

postgres=# show constraint_exclusion;
 constraint_exclusion
----------------------
 off
(1 row)

test=# explain select * from master1 where id=240;
                                 QUERY PLAN                                
-----------------------------------------------------------------------------
 Result  (cost=0.00..31.50 rows=9 width=27)
   ->  Append  (cost=0.00..31.50 rows=9 width=27)
         ->  Seq Scan on master1  (cost=0.00..25.38 rows=6 width=36)
               Filter: (id = 240)
         ->  Seq Scan on child100 master1  (cost=0.00..2.25 rows=1 width=10)
               Filter: (id = 240)
         ->  Seq Scan on child200 master1  (cost=0.00..2.25 rows=1 width=10)
               Filter: (id = 240)
         ->  Seq Scan on child300 master1  (cost=0.00..1.62 rows=1 width=10)
               Filter: (id = 240)
(10 rows)

Case 2)  on or partition ,it will give good performance on queries.

test=# set constraint_exclusion=on;
SET
test=# explain select * from master1 where id=240;
                                 QUERY PLAN                                
-----------------------------------------------------------------------------
 Result  (cost=0.00..27.00 rows=7 width=32)
   ->  Append  (cost=0.00..27.00 rows=7 width=32)
         ->  Seq Scan on master1  (cost=0.00..25.38 rows=6 width=36)
               Filter: (id = 240)
         ->  Seq Scan on child300 master1  (cost=0.00..1.62 rows=1 width=10)
               Filter: (id = 240)
(6 rows)

       (OR)

test=# set constraint_exclusion=partition;
SET
test=# explain select * from master1 where id=240;
                                 QUERY PLAN                                
-----------------------------------------------------------------------------
 Result  (cost=0.00..27.00 rows=7 width=32)
   ->  Append  (cost=0.00..27.00 rows=7 width=32)
         ->  Seq Scan on master1  (cost=0.00..25.38 rows=6 width=36)
               Filter: (id = 240)
         ->  Seq Scan on child300 master1  (cost=0.00..1.62 rows=1 width=10)
               Filter: (id = 240)
(6 rows)

----

How to create Partitions on bigint based column,it is storing date and time :

my_db=# \d my_test

                                        Table "public. my_test"

        Column        |          Type          |                             Modifiers                             

----------------------+------------------------+-------------------------------------------------------------------

 id                   | integer                | not null default nextval('my_test_id_seq'::regclass)

 received_timestamp   | bigint                 | 

 sourceip             | numeric                | 

 destip               | numeric                | 

 my_db=# 

BEGIN;

Create table my_test_20140219 (check( ('1970-01-01 GMT'::timestamp + ((received_timestamp::bigint)::text)::interval)::date='2014-02-19')) inherits(my_test);

CREATE RULE my_test_20140219 as on insert to my_test where ( ('1970-01-01 GMT'::timestamp + ((received_timestamp::bigint)::text)::interval)::date='2014-02-19') do instead insert into my_test_20140219 values(NEW.*);

CREATE unique INDEX my_test_20140219_idx on my_test_20140219(id);

CREATE  index timestamp_sourceip_destip_20140219_idx on  my_test_20140219(sourceip, destip, received_timestamp);

END;

pg_dump --inserts  --data-only  -f /home/postgres/my_test2.dump  my_db

my_db# \i  my_test2.dump

my_db=# \dt+

                                 List of relations

 Schema |             Name              | Type  |  Owner   |  Size   | Description 

--------+-------------------------------+-------+----------+---------+-------------

 public | my_test          | table | postgres | 0 bytes | 

 public | my_test_20140219 | table | postgres | 696 kB  | 

(2 rows)

Prepared Transactions in PostgreSQL 9.2

Prepared Transactions:

PREPARE TRANSACTION prepares the current transaction for two-phase commit. After this command, the transaction is no longer associated with the current session; instead, its state is fully stored on disk, and there is a very high probability that it can be committed successfully, even if a database crash occurs before the commit is requested.

Once prepared, a transaction can later be committed or rolled back with COMMIT PREPARED or ROLLBACK PREPARED, respectively. Those commands can be issued from any session, not only the one that executed the original transaction.

Prepared transactions are session independent,we can create/prepare a prepared transaction on one session and the we can commit/rollback the prepared transaction in different session.

NOTE1: The commit/rollback user should be owner  of the transaction or superuser of he database,otherwise we will get errores.


NOTE2: If we have prepared transaction on the database it will prevent the database maintenance operations like "Reindex/Vacuum Full" .

postgres=# \dt+
                    List of relations
 Schema | Name | Type  |  Owner   |  Size   | Description 
--------+------+-------+----------+---------+-------------
 public | test | table | postgres | 0 bytes | 
(1 row)

postgres=# BEGIN ;
BEGIN
postgres=# COPY test from '/tmp/test_copy.sql';
COPY 7
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# PREPARE transaction 'trx1';
PREPARE TRANSACTION
postgres=# select * from test;
 id 
----
(0 rows)

postgres=# SELECT * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1716 | trx1 | 2013-05-07 04:04:56.620985-07 | postgres | postgres
(1 row)

postgres=# COMMIT PREPARED 'trx1';
COMMIT PREPARED
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# SELECT * from pg_prepared_xacts ;
 transaction | gid | prepared | owner | database 
-------------+-----+----------+-------+----------
(0 rows)

postgres=# PREPARE transaction 'trx2';
WARNING:  there is no transaction in progress
WARNING:  there is no transaction in progress
ROLLBACK
postgres=# BEGIN ;
BEGIN
postgres=# DELETE FROM test  where id=10;
DELETE 2
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
(5 rows)

postgres=# 
postgres=# PREPARE transaction 'trx2';
PREPARE TRANSACTION
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# 
postgres=# ROLLBACK PREPARED 'trx2';
ROLLBACK PREPARED
postgres=# SELECT * from test;
 id 
----
  1
  1
  1
  2
  2
 10
 10
(7 rows)

postgres=# 

Errors: we will get if we use prepared transaction in different users.

The below transactions created by  chiru2 and postgres users.i have tried to commit/rollbacked in different CHIRU users and received below errors.

Session 1:



postgres=> select current_user;
 current_user 
--------------
 chiru2
(1 row)

postgres=> select * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1723 | trx3 | 2013-05-07 04:19:45.99662-07  | postgres | postgres
        1730 | trx4 | 2013-05-07 04:30:54.648643-07 | chiru2   | postgres
(2 rows)

Session 2:

postgres=> select current_user;
 current_user 
--------------
 chiru
(1 row)

postgres=> select * from pg_prepared_xacts ;
 transaction | gid  |           prepared            |  owner   | database 
-------------+------+-------------------------------+----------+----------
        1723 | trx3 | 2013-05-07 04:19:45.99662-07  | postgres | postgres
        1730 | trx4 | 2013-05-07 04:30:54.648643-07 | chiru2   | postgres
(2 rows)

postgres=> 
postgres=> 
postgres=> COMMIT  prepared 'trx3';
ERROR:  permission denied to finish prepared transaction
HINT:  Must be superuser or the user that prepared the transaction.
postgres=> COMMIT  prepared 'trx4';
ERROR:  permission denied to finish prepared transaction
HINT:  Must be superuser or the user that prepared the transaction.
postgres=>  

Prepared Statements on PostgreSQL 8.4/9.0/9.1/9.2

Prepared Statements: 

PREPARE creates a prepared statement. A prepared statement is a server-side object that can be used to optimize performance. When the PREPARE statement is executed, the specified statement is parsed, analyzed, and rewritten. When an EXECUTE command is subsequently issued, the prepared statement is planned and executed. This division of labor avoids repetitive parse analysis work, while allowing the execution plan to depend on the specific parameter values supplied.



Prepared Statements usage examples on select/Update/Delete/Insert :  


Insert Prepared statement:

postgres=# PREPARE ins(int) as INSERT INTO test  values($1);
PREPARE
postgres=# select * from pg_prepared_statements ;
 name |                     statement                     |         prepare_time         | parameter_types | from_sql 
------+---------------------------------------------------+------------------------------+-----------------+----------
 ins  | PREPARE ins(int) as INSERT INTO test  values($1); | 2013-05-07 09:42:27.64714-07 | {integer}       | t
(1 row)

postgres=# select * from test;
 id 
----
(0 rows)

postgres=# EXECUTE ins(1);
INSERT 0 1
postgres=# EXECUTE ins(10);
INSERT 0 1
postgres=# select * from test;
 id 
----
  1
 10
(2 rows)

Update Prepared statement:

postgres=# PREPARE up(int,int) as UPDATE test set id=$1 where id=$2;
PREPARE
postgres=# EXECUTE up(100,10);
UPDATE 1
postgres=# select * from test;
 id  
-----
   1
 100
(2 rows)

Delete Prepared statement:
postgres=# PREPARE del(int) as DELETE FROM test where id=$1;
PREPARE
postgres=# EXECUTE del(1);
DELETE 1
postgres=# select * from test;
 id  
-----
 100
(1 row)

Select Prepared statement:

postgres=# 
postgres=# PREPARE sel(int) as select * from test where id=$1;
PREPARE
postgres=# EXECUTE sel(100);
 id  
-----
 100
(1 row)

View the existing prepared statements:

postgres=# select * from pg_prepared_statements ;
 name |                         statement                         |         prepare_time          |  parameter_types  | from_sql 
------+-----------------------------------------------------------+-------------------------------+-------------------+----------
 up   | PREPARE up(int,int) as UPDATE test set id=$1 where id=$2; | 2013-05-07 09:44:20.745465-07 | {integer,integer} | t
 del  | PREPARE del(int) as DELETE FROM test where id=$1;         | 2013-05-07 09:45:39.987117-07 | {integer}         | t
 ins  | PREPARE ins(int) as INSERT INTO test  values($1);         | 2013-05-07 09:42:27.64714-07  | {integer}         | t
 sel  | PREPARE sel(int) as select * from test where id=$1;       | 2013-05-07 09:47:08.865568-07 | {integer}         | t
(4 rows)

Remove/Deallocate the existing prepared statements:


postgres=# DEALLOCATE prepare sel;
DEALLOCATE
postgres=# select * from pg_prepared_statements ;
 name |                         statement                         |         prepare_time          |  parameter_types  | from_sql 
------+-----------------------------------------------------------+-------------------------------+-------------------+----------
 up   | PREPARE up(int,int) as UPDATE test set id=$1 where id=$2; | 2013-05-07 09:44:20.745465-07 | {integer,integer} | t
 del  | PREPARE del(int) as DELETE FROM test where id=$1;         | 2013-05-07 09:45:39.987117-07 | {integer}         | t
 ins  | PREPARE ins(int) as INSERT INTO test  values($1);         | 2013-05-07 09:42:27.64714-07  | {integer}         | t
(3 rows)

Remove/Deallocate All the existing prepared statements:

postgres=# DEALLOCATE prepare all;
DEALLOCATE ALL
postgres=# select * from pg_prepared_statements ;
 name | statement | prepare_time | parameter_types | from_sql 
------+-----------+--------------+-----------------+----------
(0 rows)

postgres=# 

======Thank You======

JDBC connectivity with Postgresql9.1 configuration

1. Download JDBC jar file from Postgresql.org  .

http://jdbc.postgresql.org/download.html

2. place this file at PostgreSQL lib location.

cp -r postgresql-9.1-903.jdbc3.jar   /opt/PostgreSQL/9.1/lib/

Execution of Java Sample program.

a. create table and insert sample lines.

create table av_test(id int).

insert into av_test values(1),(2),(3);

b.create sample JavaTest.java program

=================================

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.SQLException;

public class JavaTest {

 public static void main(String[] args) throws ClassNotFoundException, SQLException{

  Class.forName("org.postgresql.Driver");

  Connection con = DriverManager.getConnection("jdbc:postgresql://127.0.0.1:5222/postgres", "postgres", "postgres");

  PreparedStatement ps = con.prepareStatement("SELECT id FROM PUBLIC.AV_TEST");

      System.out.println("Statement output " + ps.toString());

        ResultSet rs = ps.executeQuery();

          while (rs.next()) {

             System.out.println("Done!!!" +rs.getString("id"));

                  }

                   }

                   }

==============================

c.Execution.

[root@localhost java_test]# javac JavaTest.java

[root@localhost java_test]# java -cp :/opt/PostgreSQL/9.1/lib/postgresql-9.1-903.jdbc3.jar  JavaTest

Statement output SELECT id FROM PUBLIC.AV_TEST

Done!!!1

Done!!!2

Done!!!3

Done!!!4

[root@localhost java_test]#