my$insertQuery ="INSERT INTO\"$tableName\" (";

my$insertQuery ="INSERT INTO$tableName (";

my$valuesQuery =") VALUES (";

foreach$column (keys (%recordValues)) {

if($firstIteration==0) {

%dataHash = extractData($pendingResult,$currentTuple);

my$counter=0;

my$deleteQuery ="DELETE FROM\"$tableName\" WHERE";

my$deleteQuery ="DELETE FROM$tableName WHERE";

foreach$currentField (keys%dataHash) {

if($firstField==0) {

$deleteQuery .=" AND";

my$counter;

my$quotedValue;

my$updateQuery ="UPDATE\"$tableName\" SET";

my$updateQuery ="UPDATE$tableName SET";

my$currentField;

CREATETABLE "MirrorHost" (

"MirrorHostId"serial,

"HostName"varcharNOT NULL,

PRIMARY KEY("MirrorHostId")

);

Installation Instructions

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

1) Compile pending.c

The file pending.c contains the recordchange trigger. This runs every

To build the trigger run make on the "Makefile" in the DBMirror directory.

The Makefile supplied assumes that the postgres include files are in

/usr/local/pgsql/include/server.

Postgres-7.3 Make Instructions:

If you have already run "configure" in the pgsql-server directory

then run "make" in the dbmirror directory to compile the trigger.

Postgres-7.1 & Postgres-7.2 Make Instructions:

The included Makefile is not compatible with postgres 7.1 and 7.2

The trigger will need to be built by hand.

Run the following commands

gcc -fpic -I/usr/local/pgsql/include/server -c pending.c -DNOSCHEMAS

ld -shared -o pending.so pending.o

Assuming the postgres include files are in /usr/local/pgsql/include/server.

Postgres-7.1.x installations should change this to

/usr/local/pgsql/include (The server part is for 7.2+)

If you have installed the postgres include files to another location then

modify the Makefile to reflect this.

modify the include path to reflect this.

Compiling the trigger by hand requires that all postgres headers be installed

,this is accomplished in postgresql(7.1 or 7.2) by running

"make install-all-headers" in the postgres source directory.

The trigger requires that all postgres headers be installed, this is

accomplished in postgresql(7.1 or 7.2) by running "make install-all-headers"

in the postgres source directory.

The Makefileshouldcreate a file named pending.so that contains the trigger.

Youshouldnow have a file named pending.so that contains the trigger.

Install this file in /usr/local/pgsql/lib (or another suitable location).

where MyDatabaseName is the name of the database you wish to install mirroring

on(Your master).

Postgres-7.1 and 7.2 Notes:

-The syntax for creating a trigger function changed in Postgres-7.3.

Change the line in MirrorSetup.sql from

CREATE FUNCTION "recordchange" () RETURNS trigger AS

to

CREATE FUNCTION "recordchange" () RETURNS OPAQUE AS

3) Create slaveDatabase.conf files.

Mandrake Linux 8.0(Limited Testing)

-Postgres 7.2

-Postgres 7.3

-Perl 5.6

{

PRIMARY=0,NONPRIMARY,ALL,NUM_FIELDUSAGE

#defineBUFFER_SIZE 256

#defineMAX_OID_LEN 10

externDatumrecordchange(PG_FUNCTION_ARGS);

HeapTupleretTuple=NULL;

char*tblname;

charop=0;

char*schemaname;

char*fullyqualtblname;

if (fcinfo->context!=NULL)

{

trigdata= (TriggerData*)fcinfo->context;

tblname=SPI_getrelname(trigdata->tg_relation);

schemaname=get_namespace_name(RelationGetNamespace(trigdata->tg_relation));

fullyqualtblname=SPI_palloc(strlen(tblname)+

strlen(schemaname)+4);

sprintf(fullyqualtblname,"\"%s\".\"%s\"",

schemaname,tblname);

fullyqualtblname=SPI_palloc(strlen(tblname+3));

sprintf(fullyqualtblname,"\"%s\"",tblname);

tupdesc=trigdata->tg_relation->rd_att;

if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))

{

op='d';

}

if (storePending(tblname,beforeTuple,afterTuple,tupdesc,trigdata,op))

if (storePending(fullyqualtblname,beforeTuple,afterTuple,tupdesc,trigdata,op))

{

elog(ERROR,"Operation could not be mirrored");

#if definedDEBUG_OUTPUT

elog(NOTICE,"Returning on success");

SPI_pfree(fullyqualtblname);

SPI_finish();

returnPointerGetDatum(retTuple);

}

#if definedDEBUG_OUTPUT

elog(NOTICE,cpFieldName);

while (iDataBlockSize-iUsedDataBlock<strlen(cpFieldName)+4)

while (iDataBlockSize-iUsedDataBlock<strlen(cpFieldName)+6)

{

cpDataBlock=SPI_repalloc(cpDataBlock,iDataBlockSize+BUFFER_SIZE);

iDataBlockSize=iDataBlockSize+BUFFER_SIZE;

}

{

*cpFormatedPtr=' ';

sprintf(cpFormatedPtr," ");

iUsedDataBlock++;

cpFormatedPtr++;

continue;

if (tpPKeys!=NULL)

SPI_pfree(tpPKeys);

#if definedDEBUG_OUTPUT

elog(NOTICE,"Returning");

elog(NOTICE,"Returning: DataBlockSize:%d iUsedDataBlock:%d",iDataBlockSize,

iUsedDataBlock);

memset(cpDataBlock+iUsedDataBlock,0,iDataBlockSize-iUsedDataBlock);