Cache Option for POSTGRES
<< Cache option for MySQL - TOC - Cache Option for ORACLE >>
This chapter shows a step-by-step method for caching PostgreSQL database into CSQL and demonstrates automatic update propagation to PostgreSQL database.
Setting up CSQL and PostgreSQL
This section demonstrates how CSQL can be configured to work as data cache for Postgres database.
Pre-requisites
The “ UnixODBC ” package and “ PostgreSQL “ have to be installed in System. Follow below links to download the packages.
- Postgres Database
(http://www.postgresql.org/download/)
PostgreSQL Database
Follow the below commands for starting the database server. The following command assumes that PostgreSQL is installed under /usr/local/pgsql ' directory.
$export PGDATA=/usr/local/pgsql/data $/usr/local/pgsql/bin/postmaster /tmp >/postgreslog 2>/tmp/postgreslog &
Unlike MySQL, PostgreSQL installs the ODBC driver library while installing the server software. Usually this library is named as libodbcpsql.so and will be present in /usr/lib or /usr/local/lib directory.
UnixODBC
The driver library of unixODBC is named as libodbc.so and will be available at /usr/lib or /usr/local/lib directory.
Configure –odbc.ini file
Below odbc.ini file contains the properties for DSN (Data Source Name), named psql. The below lines could be appended in odbc.ini file at home directory (~/. odbc.ini)
[psql] Description = Postgres ODBC Data source Driver = /usr/lib/libodbcpsql.so Database = test Servername = localhost UserName = lakshya Password = lakshya123 Port = 5432 Protocol = 6.4 ReadOnly = No
This guide assumes that the driver library is present in /usr/lib directory. If it is installed in some other directory change the DRIVER parameter value accordingly. Check the connection using DSN name with isql tool.
$isql psql It should display the following +---------------------------------------+ | Connected! | | | | sql-statement | | help [tablename] | | quit | | | +---------------------------------------+ SQL>
Unidirectional
Configure csql.conf file
Some cache variables needs to be modified in csql.conf file. Refer 4.1.1. Cache Section variables. Cache section variables should contain below specified value.
CACHE_TABLE=true SITE_ID=1 DSN = psql CACHE_MODE=SYNC ENABLE_BIDIRECTIONAL_CACHE=false CACHE_RECEIVER_WAIT_SECS=10 TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf DS_CONFIG_FILE=/tmp/csq/csqlds.conf
Steps for unidirectional cache
This section describes how to create a unidirectional cache that caches the contents of a single table from PostgreSQL database. Although CSQL supports multiple cache tables, one PostgreSQL table could be cached to keep the example simple. The step-by-step procedure discussed here for caching a table and to make some update and see caching in action.
Create a table in Postgres
Using the command isql in Postgres, the SQL prompt could be achieved. Through this prompt the DDL and DML statements can be executed in Postgres database.
Connect to the database and create a table:
$isql psql <postgres_user name> <postgres_password> SQL> CREATE TABLE T1(f1 int, f2 char (196), primary key (f1));
Now insert some records and commit the changes:
SQL> INSERT INTO T1 VALUES (1, 'Hi'); SQL> INSERT INTO T1 VALUES (2, 'All'); SQL> COMMIT;
Load Postgres table into Cache
Before using the cachetable tool to cache the table from Postgres, the data sources needs to be added into csqlds.conf file by using “csqlds” tool. Mainly the data source name from which the table belongs will be entered into the csqlds.conf file followed by DS’s user name, password and Target DB name. Refer the section csqlds tool and csqlds.conf file. Open another terminal and move to the CSQL root directory and set up the environment using the command (. ./setupenv.ksh) .
$csqlds -U lakshya -P lakshya123 -D psql -N postgres -a
here "psql" is used as DSN and "postgres" is used as DB name After execution of this command, the entries for ‘Postgres’ data source will be present in csqlds.conf file, which will be used by the cachetable tool to cache the table from this DS. Now, use the cachetable tool to load the Postgres table into Cache. Make sure that CSQL Server is running before the cachetable tool is used. Refer the Section 3.7. (Starting and Stopping csqlserver) in CSQL UserManual for starting and stopping the CSQL Server.
Open another terminal and move to the CSQL root directory and set up the environment using the command $. ./setupenv.ksh.
$ cachetable -U root -P manager -t T1
Now check the contents of the cache table, which is present in main memory database using CSQL tool.
CSQL>SELECT * FROM T1;
----------------------------------------------------
f1 f2
----------------------------------------------------
1 Hi
2 All
Refer the section cachetable for more information on cachetable tool.
CSQL - to - Postgres Updates
The unidirectional feature makes sure that if any modification operations (INSERT, UPDATE, DELETE) performed on the table in Cache will propagate to the original table at Postgres automatically.
Now update one record of cache table, it should propagate to the Postgres. The ‘T1’ table would have been updated in both the database.
Run csql tool with -g option, it creates an isql session which acts as gateway for CSQL, Postgres, MyQsl and Oracle changes to the original tables at target database.
Follow the below instructions:
Create gateway connection
$csql -g CSQL>
Update first row in CSQL
CSQL>update T1 set f2='Hi Smith' where f1=1; Statement Executed: Rows Affected = 1
Checks the update in CSQL
CSQL>select * from T1;
-----------------------------
f1 f2
-----------------------------
1 Hi Smith
2 All
Checks the update in Postgres
SQL> select * from T1; +------+----------+ | f1 | f2 | +------+----------+ | 1 | Hi Smith | | 2 | All | +------+----------+
If the INSERT or DELETE operation is performed, the effect of that operation will be done at both cache and target database. For Example, if new record is inserted into the cache, it will be inserted in target database as well.
Unload the cache table
Cached tables can be unloaded (removed from the cache) using –u option of cachetable tool
Unload the Table using “cachetable -u” option
$cachetable -U root -P manager -t T1 -u
Note: CSQL restricts dropping the cache table using DROP statement. Application is expected to unload the table first and then drop it.
Bi-directional Cache
Bi-directional caching is implemented using triggers of the target database. This requires additional tables and triggers, which needs to be installed on the tables in target database. Sample trigger code is available in the file trigger.psql under the CSQL root directory
Configuring csql.conf file
For bi-directional cache, some changes are required in cache section parameters of csql.conf file. Refer 4.1.1. Cache Section variables.
Cache section variables should contain below specified value
CACHE_TABLE=true SITE_ID=1 DSN = psql CACHE_MODE=SYNC ENABLE_BIDIRECTIONAL_CACHE=true CACHE_RECEIVER_WAIT_SECS=10 TABLE_CONFIG_FILE=/tmp/csql/csqltable.conf DS_CONFIG_FILE=/tmp/csql/csqlds.conf
The only difference between unidirectional and bi-directional configuration is the value of ENABLE_BIDIRECTIONAL_CACHE parameter, which is set to true for bi-directional caching.
Setting up PostgreSQL
After configuring csql.conf file, a log table needs to be created in target database to hold the operation log records for all the cached tables. The triggers should be installed on cached table for INSERT, UPDATE and DELETE operation.
Create a table in Postgres for Caching
$isql psql SQL> CREATE TABLE t1 (f1 int, f2 char (196), primary key (f1)); SQL> INSERT INTO p1 VALUES (1, 'Hi'); SQL> INSERT INTO p1 VALUES (2, 'All'); SQL> COMMIT;
Create csql_log_int table in Postgres
As mentioned before, for propagating direct updates on PostgreSQL, a log table needs to be created to hold operation logs for all cached tables.
Follows the below statements for creating log table:
$isql psql
SQL>CREATE TABLE csql_log_int (
tablename varchar(64),
pkid int,
operation int,
cacheid int);
ALTER TABLE csql_log_int add id serial;
Execute trigger
Let’s say for cached table ‘p1’ having primary key field ‘f1’, following is the format for creating the triggers in postgres database. A sample file (trigger.psql) is available at the CSQL root directory; that shall be modified with cached table name and its primary key field.
CREATE LANGUAGE plpgsql;
CREATE FUNCTION log_insert_t1() RETURNS trigger AS $triggerinsertt1$
BEGIN
insert into csql_log_int (tablename, pkid, operation, cacheid) values ('t1', NEW.f1, 1, 1);
RETURN NEW;
END;
$triggerinsertt1$ LANGUAGE plpgsql;
create trigger triggerinsertt1
AFTER INSERT on t1
FOR EACH ROW
EXECUTE PROCEDURE log_insert_t1();
CREATE FUNCTION log_update_t1() RETURNS trigger AS $triggerupdatet1$
BEGIN
insert into csql_log_int (tablename, pkid, operation, cacheid) values ('t1', OLD.f1, 2, 1);
insert into csql_log_int (tablename, pkid, operation, cacheid) values ('t1', NEW.f1, 1, 1);
RETURN NEW;
END;
$triggerupdatet1$ LANGUAGE plpgsql;
create trigger triggerupdatet1
AFTER UPDATE on t1
FOR EACH ROW
EXECUTE PROCEDURE log_update_t1();
CREATE FUNCTION log_delete_t1() RETURNS trigger AS $triggerdeletet1$
BEGIN
insert into csql_log_int (tablename, pkid, operation, cacheid) values ('t1', OLD.f1, 2, 1);
RETURN NEW;
END;
$triggerdeletet1$ LANGUAGE plpgsql;
create trigger triggerdeletet1
AFTER DELETE on t1
FOR EACH ROW
EXECUTE PROCEDURE log_delete_t1();
Note: Trigger name ends with the table name. Replace ‘t1’ in the above script to the cached table name and ‘f1’ to the primary key fieldname of the cached table. After editing the trigger.psql file as per the requirement, this could be executed by running the below command under ‘postgres’ login.
After a successful editing of trigger.psql file, now its ready for execution. Follow the below command to execute the trigger which is present under csql root directory.
$ isql psql <trigger.psql
Steps for bi-directional cache
After setting up the configurations, the t1 table can be cached from Postgres and use bi-directional cache. This means the modification happened on original table, which is present in Postgres, will be automatically propagated to the cached table in CSQL.
Load Postgres table to Cache
Now run the csqlserver and use the cachetable tool to cache Postgres table into cache.
Create another terminal and move to csql root directory and set up the environment (. ./setupenv.ksh). Make the entries in csqlds.conf file for your appropriate Data Source. Refer the Section csqlds tool
$ cachetable -U root -P manager -t p1
Now check the contents of the cache table, which is present in Cache using CSQL tool.
CSQL>SELECT * FROM p1;
-------------------------
f1 f2
-------------------------
1 Hi
2 All
Refer the section cachetable for cachetable tool.
Postgres – to – CSQL Update propagation
Insert some records in p1 table in Postgres, and the record should be propagated to the cached table at CSQL automatically based on the time specified in CACHE_RECEIVER_WAIT_SECS variable. The update will appear in cache before CACHE_RECEIVER_WAIT_SECS.
After this query the cached table in CSQL and the original table in Postgres database will contain the same number of records.
Insert Records in Postgres
$isql psql <user name> <password> SQL> insert into p1 values (3,'NoOne'); SQLRowCount returns 1 SQL> insert into p1 values (4,'EveryOne'); SQLRowCount returns 1
two more records are inserted.
Query the p1 table in Postgres
SQL> select * from p1; +----+----------+ | f1 | f2 | +----+----------+ | 4 | EveryOne | | 3 | NoOne | | 2 | All | | 1 | Hi | +----+----------+
Query the p1 table in CSQL with -g option
CSQL>select * from p1;
----------------------------------
f1 f2
----------------------------------
1 Hi
2 All
3 NoOne
4 EveryOne
The records will be found in cache, those are inserted into the original table at Postgres
Bi-directional cache on non-integer primary key
Let us consider a table having non-integer (say char) primary key field to be cached for the purpose of bi-directional. For example:
$isql psql <user name> <password> SQL>create table t1 (f1 int, f2 char (10), f3 float, primary key(f2));
For bi-directional cache on table ‘t1’ having non-integer primary key field f2, we need to add a extra key field “f4” which is not-null and unique Auto_Increment field.
$alter table t1 add column f4 int not null unique auto_increment;
After adding an extra key field, make necessary changes on trigger file for the field ‘ f4’ as key field. Run the trigger and do the operation as per the requirement.
Note: For bi-directional cache on a table having non-integer primary key, add a NOT NULL UNIQUE indexed field in target table and run trigger assuming this new field as primary key. For oracle cache this type of table by -F option.
<< Cache option for MySQL - TOC - Cache Option for ORACLE >>