Abdul Yadi's Blog

PgDBF: convert FoxPro to PostgreSQL’s non-Public Schema

abdulyadi — Sun, 06 Jan 2013 05:42:45 +0000

PgDBF: convert FoxPro to PostgreSQL’s non-Public Schema

Recently, I have to migrate Foxpro tables into PostgreSQL. I have downloaded the latest version of PgDBF originally written by Kirk Strauser (http://sourceforge.net/projects/pgdbf/files/latest/download?source=files), and successfully built the package. It run well. All foxpro data can only be stored in public schema, PgDBF does not offer option for other schema.

For that purpose I have created a patch. Once the package rebuilt, I can issue command with “upper-cased S” option -S myschema (assuming I have already had the lowercase-typed schema in my PostgreSQL database):

pgdbf -S myschema myfox.dbf

pgdbf_patch:

— pgdbf.c	2013-01-06 10:18:41.268248051 +0700

+++ pgdbf_new.c	2013-01-06 11:14:17.766415632 +0700

@@ -31,7 +31,7 @@
 #include “pgdbf.h”
-#define STANDARDOPTS “cCdDeEhm:nNpPqQtTuU”

+#define STANDARDOPTS “cCdDeEhm:nNpPqQtTuUS:”
 int main(int argc, char **argv) {

     /* Describing the DBF file */

@@ -121,6 +121,8 @@

     char *optinputcharset = NULL;

 #endif
+	char *optcustomschema= NULL;

+

     strcpy(optvalidargs, STANDARDOPTS);

 #if defined(HAVE_ICONV)

     /* Note that the declaration for optvalidargs currently reserves exactly

@@ -193,6 +195,9 @@

         case ‘U’:

             optusetruncatetable = 0;

             break;

+	case ‘S’:

+		optcustomschema= optarg;

+		break;

         case ‘h’:

         default:

             /* If we got here because someone requested ‘-h’, exit

@@ -211,9 +216,9 @@

     if(optexitcode != -1) {

         printf(

 #if defined(HAVE_ICONV)

-               “Usage: %s [-cCdDeEhtTuU] [-s encoding] [-m memofilename] filename [indexcolumn ...]\n”

+               “Usage: %s [-cCdDeEhtTuU] [-s encoding] [-S customschema] [-m memofilename] filename [indexcolumn ...]\n”

 #else

-               “Usage: %s [-cCdDeEhtTuU] [-m memofilename] filename [indexcolumn ...]\n”

+               “Usage: %s [-cCdDeEhtTuU] [-S customschema] [-m memofilename] filename [indexcolumn ...]\n”

 #endif

                “Convert the named XBase file into PostgreSQL format\n”

                “\n”

@@ -234,6 +239,7 @@

 #if defined(HAVE_ICONV)

                ”  -s  the encoding used in the file, to be converted to UTF-8\n”

 #endif

+		”  -S  the custom postgresql schema\n”

                ”  -t  wrap a transaction around the entire series of statements (default)\n”

                ”  -T  do not use an enclosing transaction\n”

                ”  -u  issue a ‘TRUNCATE’ command before inserting data\n”

@@ -288,7 +294,7 @@

      * is used for other things, like creating the names of indexes. Despite

      * its name, baretablename may be surrounded by quote marks if the “-q”

      * option for optusequotedtablename is given. */

-    baretablename = malloc(strlen(dbffilename) + 1 + optusequotedtablename * 2);

+    baretablename = malloc( (optcustomschema!=NULL ? (strlen(optcustomschema) + 1/*dot schema separator*/) : 0) + strlen(dbffilename) + 1 + optusequotedtablename * 2);

     if(baretablename == NULL) {

         exitwitherror(“Unable to allocate the bare tablename buffer”, 1);

     }

@@ -304,6 +310,12 @@

     /* Create tablename and baretablename at the same time. */

     t = tablename;

     u = baretablename;

+	if(optcustomschema!=NULL) {

+		for(i=0,j=strlen(optcustomschema);i
+			*u++=tolower(optcustomschema[i]);

+		}

+		*u++ = '.'; //dot schema separator

+	}

     if(optusequotedtablename) *u++ = '"';

     while(*s) {

         if(*s == '.') {

Who’s Birthday

abdulyadi — Wed, 24 Oct 2012 17:17:13 +0000

Finding who’s birthday on a day or over a period of days is always interesting. Finding it from a computer database is even more interesting.

Here it is a table to start with (all works below is on PostgreSQL version 9.1.3):

CREATE TABLE person
(
id integer NOT NULL,
dob date NOT NULL,
CONSTRAINT “id must be unique” PRIMARY KEY (id)
)
WITH (OIDS=FALSE);

Fill it with 500,000 dummy records with random birthday:

INSERT INTO person
WITH RECURSIVE t AS (
SELECT 1 AS id, DATE_PLI(’1970-01-01′::date, (RANDOM() * 5000)::integer ) AS dob
UNION ALL
SELECT t.id+1 AS id, DATE_PLI(’1970-01-01′::date, (RANDOM() * 5000)::integer ) AS dob FROM t
WHERE t.id<500000
)
SELECT t.id, t.dob FROM t;

Now let us examine a query to find out who’s birthday on Dec 30th 2012:

EXPLAIN ANALYZE
SELECT * FROM person WHERE DATE_PART(‘MONTH’,dob)=DATE_PART(‘MONTH’,’2012-12-30′::date) AND DATE_PART(‘DAY’,dob)=DATE_PART(‘DAY’,’2012-12-30′::date);

Result:

Seq Scan on person (cost=0.00..14713.00 rows=12 width=8) (actual time=0.226..284.272 rows=1365 loops=1)
Filter: ((date_part(‘MONTH’::text, (dob)::timestamp without time zone) = 12::double precision) AND (date_part(‘DAY’::text, (dob)::timestamp without time zone) = 30::double precision))
Total runtime: 286.369 ms

Can we do it faster? with the help of index.

CREATE INDEX “person_idx_dob1″ ON person USING btree
( INT4PL( DATE_PART(‘MONTH’,dob)::integer*100, DATE_PART(‘DAY’,dob)::integer ) );

Let us examine a query in different way so the index works:

EXPLAIN ANALYZE
SELECT * FROM person
WHERE INT4PL(DATE_PART(‘MONTH’,dob)::integer*100,DATE_PART(‘DAY’,dob)::integer)=
INT4PL(DATE_PART(‘MONTH’,’2012-12-30′::date)::integer*100,DATE_PART(‘DAY’,’2012-12-30′::date)::integer);

Result:

Bitmap Heap Scan on person (cost=43.69..2443.18 rows=2500 width=8) (actual time=0.481..3.805 rows=1365 loops=1)
Recheck Cond: (int4pl(((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) = 1230)
-> Bitmap Index Scan on person_idx_dob1 (cost=0.00..43.06 rows=2500 width=0) (actual time=0.305..0.305 rows=1365 loops=1)
Index Cond: (int4pl(((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) = 1230)
Total runtime: 5.783 ms (previously 286.369 ms)

Yes, the index works and it is faster now.

More Challenges?

Finding who’s birthday over a period of days is more complex than what we have done on a single day. Specially when the period starts from a day close to end of year to a day next year.
I have a trick to transform a date-of-birth to the form of:

20000 + (month-part-of-a-date * 100) plus (day-part-of-a-date).

If the whole period is within a single year, then the start and stop date is transformed into:

20000 + (month-part-of-the-start-date * 100) plus (day-part-of-the-start-date)
20000 + (month-part-of-the-stop-date * 100) plus (day-part-of-the-stop-date)

But, if it crosses year border then it has lower and higher range detection. The lower range detection:

10000 + (month-part-of-the-start-date * 100) plus (day-part-of-the-start-date)
20000 + (month-part-of-the-stop-date * 100) plus (day-part-of-the-stop-date)

And for higher range detection:

20000 + (month-part-of-the-start-date * 100) plus (day-part-of-the-start-date)
30000 + (month-part-of-the-stop-date * 100) plus (day-part-of-the-stop-date)

Give it a try. To query who’s birthday between 1) Dec 30 2012 and Dec 31 2012, 2) Dec 30 2012 and Jan 01 2013

Period within single year: Dec 30 2012 and Dec 31 2012.

Result:

Seq Scan on person (cost=0.00..32213.00 rows=2500 width=8) (actual time=0.504..611.400 rows=2715 loops=1)
Filter: ((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 21230) AND
(int4pl(int4pl(20000,((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 21231))
Total runtime: 614.999 ms

Period crosses years: Dec 30 2012 and Jan 01 2013:

EXPLAIN ANALYZE
SELECT * FROM person
WHERE (
( INT4PL( INT4PL(20000,(DATE_PART(‘MONTH’,dob)::integer*100)), DATE_PART(‘DAY’,dob)::integer) )
BETWEEN
( INT4PL( INT4PL(10000,(DATE_PART(‘MONTH’,’2012-12-30′::date)::integer*100)),DATE_PART(‘DAY’,’2012-12-30′::date)::integer) )
AND
( INT4PL( INT4PL(20000,(DATE_PART(‘MONTH’,’2013-01-01′::date)::integer*100)),DATE_PART(‘DAY’,’2013-01-01′::date)::integer) )
)
OR
(
( INT4PL( INT4PL(20000,(DATE_PART(‘MONTH’,dob)::integer*100)), DATE_PART(‘DAY’,dob)::integer) )
BETWEEN
( INT4PL( INT4PL(20000,(DATE_PART(‘MONTH’,’2012-12-30′::date)::integer*100)),DATE_PART(‘DAY’,’2012-12-30′::date)::integer) )
AND
( INT4PL( INT4PL(30000,(DATE_PART(‘MONTH’,’2013-01-01′::date)::integer*100)),DATE_PART(‘DAY’,’2013-01-01′::date)::integer) )
);

Result:

Seq Scan on person (cost=0.00..57213.00 rows=4988 width=8) (actual time=1.232..1785.936 rows=4054 loops=1)
Filter: (((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 11230) AND
(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 20101)) OR
((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 21230) AND
(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 30101)))
Total runtime: 1791.009 ms

Can it be faster?

Create index:

CREATE INDEX “person_idx_dob2″ ON person USING btree
INT4PL( INT4PL(20000,(DATE_PART(‘MONTH’,dob)::integer*100)), DATE_PART(‘DAY’,dob)::integer) );

Re-run exactly the same query as above and see how the index works:

Period within single year: Dec 30 2012 and Dec 31 2012:

Result:

Bitmap Heap Scan on person (cost=49.96..2518.20 rows=2500 width=8) (actual time=0.829..6.969 rows=2715 loops=1)
Recheck Cond: ((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 21230) AND
(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 21231))
-> Bitmap Index Scan on person_idx_dob2 (cost=0.00..49.34 rows=2500 width=0) (actual time=0.549..0.549 rows=2715 loops=1)
Index Cond: ((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 21230) AND
(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 21231))
Total runtime: 10.649 ms (previously 614.999 ms)

Period crosses years: Dec 30 2012 and Jan 01 2013:

Result:

Bitmap Heap Scan on person (cost=101.17..2864.17 rows=4988 width=8) (actual time=1.552..9.987 rows=4054 loops=1)
Recheck Cond: (((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 11230) AND
(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 20101)) OR
((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 21230) AND

(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 30101)))
-> BitmapOr (cost=101.17..101.17 rows=5000 width=0) (actual time=1.210..1.210 rows=0 loops=1)
-> Bitmap Index Scan on person_idx_dob2 (cost=0.00..49.34 rows=2500 width=0) (actual time=0.495..0.495 rows=1339 loops=1)
Index Cond: ((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 11230) AND
(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 20101))
-> Bitmap Index Scan on person_idx_dob2 (cost=0.00..49.34 rows=2500 width=0) (actual time=0.710..0.710 rows=2715 loops=1)
Index Cond: ((int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) >= 21230) AND
(int4pl(int4pl(20000, ((date_part(‘MONTH’::text, (dob)::timestamp without time zone))::integer * 100)), (date_part(‘DAY’::text, (dob)::timestamp without time zone))::integer) <= 30101))
Total runtime: 15.224 ms (previously 1791.009 ms)

What about Feb 29th?

It must be filtered out if the year of period detection is not a leap year. And I do not have time to show you because I am in a hurry to say:

Happy Birthday!

Consume SOAP Web Service from PostgreSQL User Defined Function in C

abdulyadi — Sat, 03 Mar 2012 19:14:17 +0000

In memoriam, Pipit F.Y.H (Oct 30, 1968 – Jan 15, 2012), we have passed precious moments, once in my lifetime.

I have took a look directory of public SOAP web services at http://www.service-repository.com. Then an idea came across my mind to utilize gSOAP toolkit to develop some C code for a User Defined Function in PostgreSQL, so I can consume service provided by http://www.currencyserver.de/webservice/currencyserverwebservice.asmx?WSDL to get currency rate through SQL command, such as SELECT provider, source, dest, rate FROM cy(‘USD’, ‘IDR’).

I download gSOAP on my Ubuntu Laptop. Build and install it into /opt/gsoap directory.
Use gSOAP wsdl2h to obtain the gSOAP header file cy.h specification of the web service’s WSDL document:
/opt/gsoap/bin/wsdl2h -c -o cy.h http://www.currencyserver.de/webservice/currencyserverwebservice.asmx?WSDL
Generate a bunch of source, header, xml and nsmap files based on cy.h header file:
/opt/gsoap/bin/soapcpp2 -1 -c -C -pcy cy.h
Copy stdsoap2.c from gSOAP source code distribution into current work directory.
Create C source code cy.c for PostgreSQL User Defined Functions with Version 1 Calling Conventions:
#include
#include
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

#include
#include
#include “cyH.h”
#include “CurrencyServerWebServiceSoap.nsmap”

PG_FUNCTION_INFO_V1(cy);
Datum cy(PG_FUNCTION_ARGS);

void errExit(struct soap* soap);
void cleanExit(struct soap* soap);
struct cyData{ struct soap* soap; char** provList; };

Datum cy(PG_FUNCTION_ARGS) {

struct FuncCallContext* funcctx;
TupleDesc tupdesc;

if (SRF_IS_FIRSTCALL()) {

MemoryContext oldcontext;
funcctx = SRF_FIRSTCALL_INIT();
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
if(get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE) {

ereport(ERROR,

( errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(“function returning record called in context “
“that cannot accept type record”)
)

);

}
funcctx->tuple_desc=BlessTupleDesc(tupdesc);
if(PG_NARGS()!=2)

elog(ERROR, “argument count must be %d”, 2);

struct cyData* pData=(struct cyData*)palloc(sizeof(struct cyData));
pData->soap=soap_new();
struct _ns1__getProviderList reqProvList;
struct _ns1__getProviderListResponse respProvList;
soap_default__ns1__getProviderList(pData->soap, &reqProvList);
soap_default__ns1__getProviderListResponse(pData->soap, &respProvList);
if(soap_call___ns2__getProviderList(pData->soap, NULL, NULL, &reqProvList, &respProvList)){

errExit(pData->soap);

}
char* delim=”, “;
char* ch=strtok(respProvList.getProviderListResult,delim);
int count=0;
while(ch){

pData->provList= count==0 ? (char**)palloc(++count*sizeof(char*))
: (char**)repalloc(pData->provList, ++count*sizeof(char*));
pData->provList[count-1]=pstrdup(ch);
ch=strtok(NULL,delim);

}
funcctx->max_calls=count;
funcctx->user_fctx=(void*)pData;

MemoryContextSwitchTo(oldcontext);

}

funcctx = SRF_PERCALL_SETUP();

if (funcctx->call_cntr max_calls) {

Datum result;
HeapTuple tuple;
bool isnull[]={false,false,false,false};

struct cyData* pData=(struct cyData*)funcctx->user_fctx;

struct _ns1__getCurrencyValue req;
struct _ns1__getCurrencyValueResponse resp;
soap_default__ns1__getCurrencyValue(pData->soap, &req);

req.provider=pData->provList[funcctx->call_cntr];
req.srcCurrency=TextDatumGetCString(PG_GETARG_DATUM(0));
req.dstCurrency=TextDatumGetCString(PG_GETARG_DATUM(1));
if(soap_call___ns2__getCurrencyValue(pData->soap, NULL, NULL, &req, &resp)){

errExit(pData->soap);

}
Datum values[]={

DirectFunctionCall1(textin, CStringGetDatum(pData->provList[funcctx->call_cntr])),
PG_GETARG_DATUM(0),
PG_GETARG_DATUM(1),
DirectFunctionCall1(float8_numeric, Float8GetDatum(resp.getCurrencyValueResult))

};

tuple=heap_form_tuple(funcctx->tuple_desc, values, isnull);
result = HeapTupleGetDatum(tuple);

SRF_RETURN_NEXT(funcctx, result);

} else {

struct cyData* pData=(struct cyData*)funcctx->user_fctx;
cleanExit(pData->soap);
SRF_RETURN_DONE(funcctx);

}

}

void errExit(struct soap* soap) {

char buf[201]=”";
soap_sprint_fault(soap, buf, 200);
cleanExit(soap);
elog(ERROR, “%s”, buf);

}

void cleanExit(struct soap* soap) {

soap_end(soap);
soap_free(soap);

}
Create SQL skeleton for User Defined Functions cy.sql.in:
CREATE OR REPLACE FUNCTION cy(OUT provider text, INOUT source text, INOUT dest text, OUT rate numeric)
RETURNS SETOF record
AS ‘MODULE_PATHNAME’, ‘cy’ LANGUAGE C IMMUTABLE STRICT;
GRANT EXECUTE ON FUNCTION cy(text, text) TO public;
Create Makefile to comply with PostgreSQL build infrastructure for extensions (called PGXS):
MODULE_big = cy
OBJS = stdsoap2.o cyC.o cyClient.o cy.o
DATA_built = cy.sql
PG_CONFIG = /opt/pgsql/bin/pg_config
PG_CPPFLAGS = -I/opt/gsoap/include
PGXS := $(shell $(PG_CONFIG) –pgxs)
include $(PGXS)

Plase take note that my PostgreSQL resides in /opt/pgsql.
Build and install everything:
abdul@mylaptop:~/wsdl/cy$ make
abdul@mylaptop:~/wsdl/cy$ sudo make install
abdul@mylaptop:~/wsdl/cy$ /opt/pgsql/bin/psql -U admin -h localhost -p 5432 -d test -f cy.sql
Login to database server:
abdul@mylaptop:~/wsdl/cy$ /opt/pgsql/bin/psql -U slony -h localhost -p 5432 test
Password for user slony:
psql (9.0.4)
Type “help” for help.

test=#
Check my currency rate…
test=# SELECT * FROM cy(‘USD’,'IDR’);
provider | source | dest | rate
———-+——–+——+————
3 | USD | IDR | 9094.34819
4 | USD | IDR | 9087.5
AVERAGE | USD | IDR | 9090.92409
(3 rows)

test=# SELECT * FROM cy(‘EUR’,'IDR’);
provider | source | dest | rate

———-+——–+——+——————
3 | EUR | IDR | 12020.0213983611
4 | EUR | IDR | 11993.8496462887
AVERAGE | EUR | IDR | 12006.9261827403
(3 rows)

test=# SELECT * FROM cy(‘SGD’,'IDR’);
provider | source | dest | rate

———-+——–+——+——————
3 | SGD | IDR | 7272.91848469339
4 | SGD | IDR | 7257.45911065679
AVERAGE | SGD | IDR | 7265.18348117957
(3 rows)

test=#

Hi, it comes from the Public Web Service thousands miles away from my house, not from my database.

Detect Inactive Slony Node

abdulyadi — Mon, 18 Jul 2011 07:01:53 +0000

One concern in implementing Slony as PostgreSQL Replication System is that table sl_event easily growing to a huge number of record. So, I keep monitor its size on weekly basis. Its size ever reaches more than a million record.

After doing some troubleshoot, I have found that it is caused by some inactive replication node which does not confirm the changes message conveyed by the event. By re-activating slony daemon on the nodes, the problem goes away.

How to find out which nodes inactive? This is my query (assuimng the slony cluster name is clstr):

SELECT t.con_received, t.ts FROM (

    SELECT con_received, MAX(con_timestamp) ts

    FROM    _clstr.sl_confirm GROUP BY con_received

) t ORDER BY t.ts;

Following is example of the query outcome:
con_received | ts
————–+—————————-
24 | 2011-07-11 08:49:18.004292
26 | 2011-07-18 05:59:33.483955
30 | 2011-07-18 10:31:55.235895
31 | 2011-07-18 12:26:35.889352
22 | 2011-07-18 12:27:02.932156
21 | 2011-07-18 12:27:21.21836
15 | 2011-07-18 12:27:23.298656
18 | 2011-07-18 12:27:23.701799
17 | 2011-07-18 12:27:27.052192
28 | 2011-07-18 13:43:19.826342
11 | 2011-07-18 13:58:25.891667
1 | 2011-07-18 13:58:37.566528
29 | 2011-07-18 13:58:47.321221
16 | 2011-07-18 14:01:08.206147
13 | 2011-07-18 14:01:10.92797
19 | 2011-07-18 14:02:02.476146
14 | 2011-07-18 14:02:02.51104
20 | 2011-07-18 14:02:05.63391
27 | 2011-07-18 14:14:55.750242

It tells us that node 24 has been inactive for 7 days relative to other nodes.

Transfer Binary from Photo File to Database Table

abdulyadi — Sat, 25 Dec 2010 11:11:02 +0000

In many web application, personal photo is saved as [*.jpg] file in a folder in server’s disk. The relation with personal ID in database is established by set the filename accordingly in format: nnnnn.jpg, e.g: 00001.jpg, 00002.jpg and so on.

Once upon a time, I was in charge to move content of photo file into database’s field, integrated with personal identity table. All works in Linux environment and PostgreSQL 9.0.

I want to share the story….

Original setting: photo file is saved in folder: /home/webuser/photo/ and person table resides in persondb database with DDL as follows:

CREATE TABLE person
(
id serial NOT NULL,
fullname text NOT NULL,
addres text NOT NULL,
CONSTRAINT “person: id must be unique” PRIMARY KEY (id)
) WITH (OIDS=FALSE);

The table has been populated with hundreds of person records.

Part 1: Alter Table

Add a field to store photo bytearray:

ALTER TABLE person ADD COLUMN photo bytea;

Part 2: Create C Source Code

Create person.c:

#include 
#include 
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
#include 
#include 
#include 
#include 
#include 
#include 
PG_FUNCTION_INFO_V1(person_photo);
Datum person_photo(PG_FUNCTION_ARGS);
Datum
person_photo(PG_FUNCTION_ARGS)
{
int32 personId;
StringInfoData photoFile;
StringInfoData photoBytea;
const char* PHOTO_PATH=”/home/webuser/photo/”;
const char* PHOTO_EXT=”jpg”;
FILE* f;
const size_t BUFFER_SIZE=5*1024;
char buf[BUFFER_SIZE];
size_t byteReadCount;
const int32 SQL_PARAM_COUNT=2;
Oid argTypes[SQL_PARAM_COUNT];
Datum vals[SQL_PARAM_COUNT];

		////get function argument:BEGIN
if(PG_ARGISNULL(0)) {
elog(ERROR,”person id can not be NULL”);
i}
personId=PG_GETARG_INT32(0);
		////get function argument:END
		////read photo file:BEGIN			
photoBytea.data=0;
photoBytea.len=0;
initStringInfo(&photoFile);
appendStringInfo(&photoFile, “%s%05d.%s”, PHOTO_PATH, personId, PHOTO_EXT);
f=fopen(photoFile.data, “rb”);//open photo file
pfree(photoFile.data);
if(f){
initStringInfo(&photoBytea);
while(!feof(f)){//keep reading until end of file
byteReadCount=fread(buf, 1, BUFFER_SIZE, f);
appendBinaryStringInfo(&photoBytea, buf, byteReadCount);
}
fclose(f);
} else {
elog(NOTICE, “person with id %d does not have photo”, personId);
}
		////read photo file:END
		////save to database:BEGIN
if(photoBytea.len){
SPI_connect();
argTypes[0]=INT4OID;
argTypes[1]=BYTEAOID;
vals[0]=Int32GetDatum(personId);
vals[1]=DirectFunctionCall1(bytearecv, PointerGetDatum(&photoBytea));
SPI_execute_with_args(“UPDATE person SET photo=$2 WHERE id=$1″, 
SQL_PARAM_COUNT, argTypes, vals, NULL, FALSE, 1);
SPI_finish();
}
if(photoBytea.data){
pfree(photoBytea.data);
}
		////save to database:END
PG_RETURN_VOID();
}

Part 3: Create SQL to Install Our Custom Library

Create person.sql.in in the same folder as person.c:

CREATE OR REPLACE FUNCTION person_photo(person_id integer)
RETURNS VOID
AS ‘MODULE_PATHNAME’ LANGUAGE C VOLATILE;

Part 4: Create Makefile

Create Makefile in the same folder as person.c:

MODULE_big = person
OBJS = person.o
DATA_built = person.sql
PG_CPPFLAGS := $(shell pg_config –includedir-server)
PGXS := $(shell pg_config –pgxs)
include $(PGXS)

Part 5: Compile and Install

In the same folder as Makefile, run command make then make install. Make sure we have sufficient privilege to create file in PostgreSQL contrib and library folder. If everything goes fine then we will have two files:

SQL file person.sql in PostgreSQL contrib directory.
Library file person.so in PostgreSQL library directory.

Part 6: Install Stored Procedure

Assuming we are in the same folder as person.sql and want to install stored procedure in persondb database (where table person exists) at host server1:

psql -U postgres -h server1 -f person.sql -d persondb

Part 7: Move Photo From File to Table

All preparation has been set. It is time to login to PostgreSQL server and run the stored procedure (make sure folder /home/webuser/photo/ is accessible by postgres user):

psql -U postgres -h server1 persondb
Password for user postgres:[your password]
postgres=#SELECT person_photo(id) FROM person;

Wait a moment, let PostgreSQL read photo files and store the binaries into field photo.

My story ends….

PgPool II 3.0: bug fixes for md5 authentication

abdulyadi — Sat, 25 Sep 2010 07:39:12 +0000

I have problem with PgPool II 3.0. I have downloaded the source code from http://pgfoundry.org/projects/pgpool. Extract and compile it successfully. Configure it just to have connection pooling enabled (other features: Replication, LoadBalance, Parallel Query are disabled). But, I can not connect to PostgreSQL 9.0 via PgPool port at 9999 with authentication mode set to md5 in pg_hba.conf (pool_hba.conf is disabled).

After take some time to trace the source code, I have found bug in file pool_auth.c. Inside function:

static int do_md5(POOL_CONNECTION *backend, POOL_CONNECTION *frontend, int reauth, int protoMajor)

variable int size is declared but it never have the length of password.

4 steps to fix the problem:

Modify forward declaration of function int read_password_packet(…), add int *pwdSize as the fourth argument:

//static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password);

static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password, int *pwdSize);
Modify function body of function int read_password_packet(…). Watch that *pwdSize has the password length at the end of the function body:

/*

* Read password packet from frontend

*/

//static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password)

static int read_password_packet(POOL_CONNECTION *frontend, int protoMajor, char *password, int *pwdSize)

{

  int size;

  /* Read password packet */

  if (protoMajor == PROTO_MAJOR_V2)

  {

    if (pool_read(frontend, &size, sizeof(size)))

    {

      pool_error(“read_password_packet: failed to read password packet size”);

      return -1;

    }

  }

  else

  {

    char k;

    if (pool_read(frontend, &k, sizeof(k)))

    {

      pool_debug(“read_password_packet_password: failed to read password packet \”p\”");

      return -1;

    }

    if (k != ‘p’)

    {

      pool_error(“read_password_packet_password: password packet does not start with \”p\”");

      return -1;

    }

    if (pool_read(frontend, &size, sizeof(size)))

    {

      pool_error(“read_password_packet_password: failed to read password packet size”);

      return -1;

    }

  }

  if (pool_read(frontend, password, ntohl(size) – 4))

  {

      pool_error(“read_password_packet: failed to read password (size: %d)”, ntohl(size) – 4);

      return -1;

  }

  *pwdSize=size; //now *pwdSize has password length

  return 0;

}
Inside function
static int do_md5(POOL_CONNECTION *backend, POOL_CONNECTION *frontend, int reauth, int protoMajor)

add &size as fourth argument to every read_password_packet function call, for example:

read_password_packet(frontend, protoMajor, password, &size)
Recompile and reinstall pgpool project.

Done. Now. I can login to PostgreSQL 9.0 smoothly through PgPool port: 9999.

dbi-link: fix error on insert/update/delete into/from remote mysql table

abdulyadi — Wed, 28 Jul 2010 02:33:58 +0000

I have installed dbi-link version 2.0.0 into my PostgreSQL 8.4 environment. The purpose is to query or modify remote mysql table from within postgresql user-defined-function. And whenever I try to insert/update/delete record into/from remote mysql table, I consistently get error message:

ERROR: error from Perl function “shadow_trigger_func”: Can’t call method “quote” on an undefined value at line 61

So i go to the mentioned function and found out sub functions: make_pairs, do_insert, do_update and do_delete try to access variables: $data_source_id and $table which are declared locally in main function.

Knowing that problem, I have modified parameter passing as follows:

Within main function:

…

if ($iud->{ $_TD->{new}{iud_action} }) {

   $iud->{ $_TD->{new}{iud_action} }->({

      payload => $_TD->{new},

      tbl => $table,

      source_id => $data_source_id

   });

}

else {

   die “Trigger event was $_TD->{new}{iud_action}<, but should have been one of I, U or D!"

}

…

And inside sub functions: modify $table and $data_source_id into $params->{tbl} and $params->{source_id}, respectively.

Integrated Networks for Educational Research

abdulyadi — Mon, 24 May 2010 15:13:31 +0000

Linux Community at Batam, Indonesia has launched a web application dedicated for educational research by Indonesian teachers. We call it INFER: Integrated Networks For Educational Research (http://infer.gelora-batam.org). It is a Rich Internet Application developed using Ext-GWT, deployed on Tomcat5 and backed by PostgreSQL 8.4 (for its CTE feature). Statistic Probability Density function is calculated within stored procedure written in C, to wrap library provided by GSL (GNU Scientific Library).

At this moment, the service provides analytical tools for: Correlation and One way Analyses Of Variance (ANOVA) study, as well as Exams Item Validation tool (as online alternative for ITEMAN.EXE by Assessments Systems Corporation).

The project will be continually enhanced by Batam Linux Community for more analytical tools.

PLSQL for Curve Smoothing

abdulyadi — Sat, 16 May 2009 03:39:19 +0000

In an engineering project, I have been assigned to a task constructing smooth curve for given sets of sample points. This is accomplished by implementing Cubic Splines and Thomas algorithm to solve linear algebraic equation. So, I write PLSQL stored procedure as follows:

CREATE OR REPLACE FUNCTION spline(

	IN x_arr numeric[], IN y_arr numeric[], IN resolution numeric,

	OUT xs numeric, OUT ys numeric)

  RETURNS SETOF record AS

$BODY$

DECLARE

  x numeric[];

  y numeric[];

  e numeric[] DEFAULT ‘{}’::numeric[];

  f numeric[] DEFAULT ‘{}’::numeric[];

  g numeric[] DEFAULT ‘{}’::numeric[];

  r numeric[] DEFAULT ‘{}’::numeric[];

  f2 numeric[] DEFAULT ‘{}’::numeric[];

  count integer;

  i integer;

  j integer;

  idx integer;

  tmp numeric;

BEGIN

  x:=x_arr;

  y:=y_arr;

  count:=ARRAY_UPPER(x,1);

  IF count<3 THEN

    RAISE EXCEPTION ‘sample size must be >=3′;

  END IF;

  IF ARRAY_UPPER(y,1)<>count THEN

    RAISE EXCEPTION ‘x and y array size must be identical’;

  END IF;
——–sort x, ascendingly:

  FOR i IN 1..count-1 LOOP

    idx:=i+1;

    FOR j IN i+2..count LOOP

      IF x[j]
        idx:=j;

      END IF;

    END LOOP;

    IF x[idx]=x[i] THEN

      RAISE EXCEPTION ‘x values must be unique’;

    ELSIF x[idx]
      tmp=x[i];

      x[i]=x[idx];

      x[idx]=tmp;

      tmp=y[i];

      y[i]=y[idx];

      y[idx]=tmp;

    END IF;

  END LOOP;
——–prepare variables for Thomas algorithm

  FOR i IN 1..count LOOP

    IF i=1 OR i=count THEN

      e:= e || 0.0;

      f:= f || 0.0;

      g:= g || 0.0;

      r:= r || 0.0;

      f2:=f2 || 0.0;

      CONTINUE;

    END IF;

    e:= e || x[i] – x[i-1];

    f:= f || 2*(x[i+1]-x[i-1]);

    g:= g || x[i+1] – x[i];

    r:= r || ( (6/(x[i+1]-x[i])) * (y[i+1]-y[i]) )

      + ( (6/(x[i]-x[i-1])) * (y[i-1]-y[i]) );

    f2:=f2 || 0.0;

  END LOOP;
——–decomposition

  FOR i IN 2..count LOOP

    IF f[i-1]<>0 THEN

      e[i]:=e[i]/f[i-1];

    END IF;

    f[i]:=f[i]-(e[i]*g[i-1]);

  END LOOP;
——–forward substitution

  FOR i IN 2..count LOOP

    r[i]:=r[i]-(e[i]*r[i-1]);

  END LOOP;
——–backward substitution

  FOR i IN REVERSE count-1..1 LOOP

    IF f[i]<>0 THEN

      f2[i]:=(r[i] – (g[i]*f2[i+1])) / f[i];

    END IF;

  END LOOP;
——–do interpolation

  FOR i IN 2..count LOOP

    xs:=x[i-1]-resolution;

    LOOP

      xs:=xs+resolution;

      EXIT WHEN i=count AND xs>x[i];

      EXIT WHEN i=x[i];

      ys:= ( (f2[i-1] * (x[i]-xs)^3)

        + (f2[i] * (xs-x[i-1])^3)

        ) / (6*(x[i]-x[i-1]));

      ys:= ys +  (

        (y[i-1]/(x[i]-x[i-1])) -

        (f2[i-1]*(x[i]-x[i-1])/6)

        ) * (x[i]-xs);

      ys:= ys + (

        (y[i]/(x[i]-x[i-1])) -

        (f2[i]*(x[i]-x[i-1])/6)

        ) * (xs-x[i-1]);

      RETURN NEXT;

      EXIT WHEN xs>=x[i];

    END LOOP;

  END LOOP;
  RETURN;

END;

$BODY$

  LANGUAGE ‘plpgsql’ IMMUTABLE STRICT;

The stored procedure takes 3 input arguments and 2 outs. x_arr and y_arr are sample points’ x and y. With resolution, you can determine the resolution of smoothed curve’s x.

Output arguments: xs and ys contain the set of x and y of smoothed curve, respectively.

Let us use the function. At first, we have sample points:

#	x	y
1	3.3	1.2
2	4.2	-1.1
3	6.8	1.5
4	8.2	1.2
5	10.2	2.5

Call the function, supply x and y array as displayed on above table, set resolution to 0.1:

SELECT * FROM spline(

  ’{3.3,4.2,6.8,8.2,10.2}’::numeric[],

   ‘{1.2,-1.1,1.5,1.2,2.5}’::numeric[],

  0.1);

We will have set of records containing xs and ys.:

xs  ys

3  1.199999999999999970

4  0.8860175045157231811

5  0.5764170295261004851

6  0.2755805955257859750

7  -0.0121097769905662565

8  -0.28227206752830211620

… and so on

Let us put them in graph:

The blue-y1 and red-y2 are sample and interpolation points as returned by the spline function, respectively.

Tree-structured data and Nested Hash

abdulyadi — Fri, 06 Feb 2009 09:12:34 +0000

I have just accomplished a task to transfer tree-formed data structure from client application environment into PostgreSQL PLSQL body function. The tree at client side is structured as shown below:

Instead of send the item pieces one by one, I want to send all the items at once.

PLSQL Function (convert array of ltree into nested hash)

I decide to package the tree items in the array of LTREE field type provided in PostgreSQL contrib module by Teodor Sigaev and Oleg Bartunov. Later in the function body, I convert the LTREE array into HSTORE (again thanks to Teodor Sigaev and Oleg Bartunov). HSTORE maps key to value. Both are string types. But in my case, the hash contains key which point to another hash, creating nested hash.

Following is the stored procedure in PLSQL created on Postgresql 8.3.1:

CREATE OR REPLACE FUNCTION organization(IN trees ltree[]) RETURNS void AS

$BODY$

DECLARE

——–ltree[] will be converted to nested hash:

————-hash_mgr=>hash_snr=>array(Juniors)

    i integer;

    count integer;

    hash_mgr hstore;

    hash_snr hstore;

    tree ltree;

    arr text[];

    mgr text;

    snr text;

    jnr text;

    k1 text; v1 text;

    k2 text; v2 text;

BEGIN

    count:=ARRAY_UPPER(trees,1);

——–convert array of trees into nested hash:

    FOR i IN 1..count LOOP

        tree:=trees[i];

        mgr:=SUBLTREE(tree,0,1);

        snr:=SUBLTREE(tree,1,2);

        jnr:=SUBLTREE(tree,2,3);

        hash_snr:=(hash_mgr->mgr)::hstore;

        arr:=(hash_snr->snr)::text[];

        arr:=COALESCE(arr,’{}’::text[]) || jnr;

        hash_snr:=COALESCE( hash_snr||(snr=>arr::text),snr=>arr::text);

        hash_mgr:=COALESCE( hash_mgr||(mgr=>hash_snr::text),mgr=>hash_snr::text);

    END LOOP;

——–now we hava nested hash

——–usage 1: get Junior1 and Junior2:

    arr:=((hash_mgr->’Manager1′)::hstore)->’Senior1′;

    RAISE NOTICE ‘% %’,arr[1],arr[2];

——–navigate all items

    FOR k1, v1 IN SELECT key, value FROM EACH(hash_mgr) LOOP

        RAISE NOTICE ‘manager: %’,k1;

        FOR k2, v2 IN SELECT key, value FROM EACH(v1::hstore) LOOP

            RAISE NOTICE ‘    senior: %’,k2;

            RAISE NOTICE ‘      junior: %’,(v2::text[])[1];

            RAISE NOTICE ‘      junior: %’,(v2::text[])[2];

        END LOOP;

    END LOOP;

    RETURN;

END;$BODY$

LANGUAGE ‘plpgsql’ STABLE;

Let’s start from lowest level. Hash hash_snr maps “senior” to array of juniors.
Go up one level, hash hash_mgr maps “manager” to hash hash_snr.

To use the function, client application should invoke it as follows:

SELECT organization(‘{

    Manager1.Senior1.Junior1,

    Manager1.Senior1.Junior2,

    Manager1.Senior2.Junior3,

    Manager1.Senior2.Junior4,

        Manager2.Senior3.Junior5,

        Manager2.Senior3.Junior6,

        Manager2.Senior4.Junior7,

        Manager2.Senior4.Junior8

    }’);

Take note RAISE NOTICE at the lower portion of function body. It will give us messages as follows:

NOTICE:    Junior1 Junior2

NOTICE:    manager: Manager1

NOTICE:        senior: Senior1

NOTICE:                junior: Junior1

NOTICE:                junior: Junior2

NOTICE:        senior: Senior2

NOTICE:                junior: Junior3

NOTICE:                junior: Junior4

NOTICE:    manager: Manager2

NOTICE:        senior: Senior3

NOTICE:                junior: Junior5

NOTICE:                junior: Junior6

NOTICE:        senior: Senior4

NOTICE:                junior: Junior7

NOTICE:                junior: Junior8