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

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<j;++i){
+ *u++=tolower(optcustomschema[i]);
+ }
+ *u++ = '.'; //dot schema separator
+ }
if(optusequotedtablename) *u++ = '"';
while(*s) {
if(*s == '.') {

Who’s Birthday

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.

EXPLAIN ANALYZE
SELECT * FROM person
WHERE (
( 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(20000,(DATE_PART(‘MONTH’,’2012-12-31′::date)::integer*100)),DATE_PART(‘DAY’,’2012-12-31′::date)::integer) )
);

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

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 <postgres.h>
  #include <fmgr.h>
  #ifdef PG_MODULE_MAGIC
  PG_MODULE_MAGIC;
  #endif

  #include <utils/builtins.h>
  #include <funcapi.h>
  #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

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

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 <postgres.h>

#include <fmgr.h>

#ifdef PG_MODULE_MAGIC

PG_MODULE_MAGIC;

#endif

#include <lib/stringinfo.h>

#include <executor/spi.h>

#include <catalog/pg_type.h>

#include <utils/builtins.h>

#include <utils/bytea.h>

#include <catalog/pg_type.h>

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:

1. SQL file person.sql in PostgreSQL contrib directory.
2. 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

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:

1. 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);

2. 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;

   }

3. 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)
4. 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

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.