CSQL : Postgres Configuration for Multiple bidirectional cache

For the Multiple bi-directional CSQL cache for Postgres as single data source in ,Be sure that Postgres database is installed and currently running.First create the log table in Postgres to hold the log records using the SQL statements given below using Postgres tool or isql tool.

CREATE TABLE csql_log_int(tablename varchar(64), pkid int, operation int, cacheid int);
ALTER TABLE csql_log_int add id serial;

Let us consider there are two CSQL cache node.Make changes in csql.conf file CACHE_ID = 1 for one cache node and CACHE_ID = 2 in other cache node and make sure that CACHE_TABLE,ENABLE_BIDIRECTIONAL_CACHE are set to true . Again DSN should set to psql for Postgres . Set “/etc/odbcinst.ini” and “/etc/odbc.ini” file properly.For help refer Uni-directional cache configuration.

Lets for a cached table “t” having primary key “f1” create trigger (say trigger.psql) as per following format in the Postgres database.

CREATE LANGUAGE plpgsql;
CREATE FUNCTION log_insert_t() RETURNS trigger AS $triggerinsertt$
BEGIN
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,2);
RETURN NEW;
END;
$triggerinsertt$ LANGUAGE plpgsql;
create trigger triggerinsertt
AFTER INSERT on t
FOR EACH ROW
EXECUTE PROCEDURE log_insert_t();

CREATE FUNCTION log_update_t() RETURNS trigger AS $triggerupdatet$
BEGIN
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,2);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, NEW.f1, 1,2);
RETURN NEW;
END;
$triggerupdatet$ LANGUAGE plpgsql;

create trigger triggerupdatet
AFTER UPDATE on t
FOR EACH ROW
EXECUTE PROCEDURE log_update_t();

CREATE FUNCTION log_delete_t() RETURNS trigger AS $triggerdeletet$
BEGIN
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,1);
insert into csql_log_int (tablename, pkid, operation,cacheid) values (‘t’, OLD.f1, 2,2);
RETURN NEW;
END;
$triggerdeletet$ LANGUAGE plpgsql;

create trigger triggerdeletet
AFTER DELETE on t
FOR EACH ROW
EXECUTE PROCEDURE log_delete_t();

Trigger name ends with the table name. Replace ‘t’ in the above script to the cached table name and ‘f1’ to the primary key fieldname of the cached table.

After writing the trigger file run this trigger in Postgres database .

$ psql test -f trigger.psql

In above command ,it is assumed that trigger file in current directory .

Advertisements

2 Responses to “CSQL : Postgres Configuration for Multiple bidirectional cache”

  1. Mubsunseple Says:

    Remarkable idea


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: