Dear Postgre co-users,
-------------------
I just submit to this mailing list and I am not a really good English speaker,
so excuse me if I don't respect some of the rules to apply. Just tell me!
I work on Win32 OS with Postgre/Postgis/PgAdmin and I build dlls with DevCpp to
create C functions.
I would like to create a C function which take a square table in entry, put it
in a matrix structure, do some operations (power, sum, etc?) and return the
result in a square table.
First, I just try to put the input table in a matrix data structure before to
return it in an output table. Not really useful at this time!
I take the pgRouting code as example. But I have two problems!
-------------------
First problem:
When I try to get the input table, the first element is forgotten, then, the
whole matrix is wrong! I have no idea of what happens?
Example:
Input table Matrix obtained
By reading tuple by tuple
1 0 2 0 0 1 0 2
0 1 0 0 0 0 1 0
0 0 1 0 0 0 0 1
0 0 0 1 0 0 0 0
If you are interested to read the code:
Look at the function called: compute_puissance_table
At the line containing : DBG(?Update of puissance_m?);
--------------------
Second problem:
When I try to give the output table, only the first line is correctly returned.
I have introduced an analyse stage which reveals that the problem comes from
the loss of the information in my matrix data structure when the function is
called for the second time.
My matrix data structure is the following :
typedef struct puissance_matrix
{
int nl; //number of lines
int nc; //number of columns
int** data; //2D integer data
} puissance_m;
I use palloc to allocate space to the element called data.
At the second call of the function, the element funcctx->user_fctx (that I use
to store my matrix data structure) references the good memory block. Moreover,
the nl and nc element references the good memory block too. But, in the block
pointed by nl and nc, the values stored have changed ! I don?t understand why.
I try to use malloc or pgport_palloc instead of palloc: it doesn?t work !
Example:
At the first call of my function:
1: BLOCK ANALYSE
1: pointer value of pm [11784980]
1: pointer value of pm->nc [11785068] stores (4)
1: pointer value of pm->nl [11785064] stores (4)
1: pointer value of pm->data [] and stored value () ::
[19517544] (0) ::
[19517548] (1) ::
[19517552] (0) ::
[19517556] (2) ::
[19517568] (0) ::
[19517572] (0) ::
[19517576] (1) ::
[19517580] (0) ::
etc?
At the second call of my function:
1: pointer value of pm [11784980]
1: pointer value of pm->nc [11785068] stores (0)
1: pointer value of pm->nl [11785064] stores (0)
If you are interested to read the code:
Look at the function called: puissance_table
At the line containing : DBG("PROBLEMATIK at step %i\n" , call_cntr);
--------------------
Only for interested people, here is the code of the ?dllmain.c? file.
Notice that there is a foreword due to the resolution of the conflicts generated
by the BUILDING_DLL macro on Win32 Platform (XP).
Just pass this step if you?re not interested! And directly go to the line
containing **Power Table**
Thanks for your reading! I hope, a day, I?ll help you in response!
CODE OF DLLMAIN.C
/**********************************************************************/
/* TEST DLL TO BUILD THE EXPONENTIATION OF A TABLE */
/* by FBM :: July 2008 */
/**********************************************************************/
/**********************************************************************/
/*************RESOLUTION OF THE MACRO CONFLICT FOR BUILDINGDLL*********/
/*************Adaptation needed for the platform Win32 OS**************/
/**********************************************************************/
#if defined(_MSC_VER) || defined(__MINGW32__)
#ifndef _USE_32BIT_TIME_T
#define _USE_32BIT_TIME_T
#endif
#endif
/* BUILDING_DLL ::*/
#ifdef BUILDING_DLL
#error Do not define BUILDING_DLL when building extension libraries
#endif
// Ensure that Pg_module_function are declared __declspec(dllexport)
#ifndef BUILDING_MODULE
#define BUILDING_MODULE
#endif
//Includes (more than needed)
#include "postgres.h"
#include <string.h>
#include "fmgr.h"
#include "utils/geo_decls.h"
#include "executor/executor.h"
#include "funcapi.h"
#include "executor/spi.h"
#include "utils/array.h"
#include "utils/lsyscache.h"
#include "miscadmin.h"
#include "commands/trigger.h"
#include <search.h>
#include <time.h>
#include <windows.h>
#include <stdio.h>
#include <stdlib.h>
/* PG MACROS :: */
#undef PG_MODULE_MAGIC
#undef PG_FUNCTION_INFO_V1
//User's PGMODULEEXPORT MACRO
#if defined(_MSC_VER) || defined(__MINGW32__)
#if defined(BUILDING_MODULE)
#define PGMODULEEXPORT __declspec (dllexport)
#else
#define PGMODULEEXPORT __declspec (dllimport)
#endif
#else
#define PGMODULEEXPORT
#endif
//Postgre PG_MODULE_MAGIC MACRO
#define PG_MODULE_MAGIC \
PGMODULEEXPORT Pg_magic_struct * \
PG_MAGIC_FUNCTION_NAME(void) \
{ \
static const Pg_magic_struct Pg_magic_data = PG_MODULE_MAGIC_DATA; \
return &Pg_magic_data; \
} \
extern int no_such_variable
//Postgre PG_FUNCTION_INFO_V1 MACRO
#define PG_FUNCTION_INFO_V1(funcname) \
PGMODULEEXPORT const Pg_finfo_record * \
CppConcat(pg_finfo_,funcname) (void) \
{ \
static const Pg_finfo_record my_finfo = { 1 }; \
return &my_finfo; \
} \
extern int no_such_variable
/*************************************************************************/
/****************END OF MACROS REDEFINITION*******************************/
/*************************************************************************/
//PG MAGIC
PG_MODULE_MAGIC;
/*************************************************************************/
/****************************Power Table**********************************/
/*************************************************************************/
//Secondary functions --------------------------------------------------
//#undef DEBUG
#define DEBUG 1
//Put debug comments on Postgre Messages Box and in a log file f
#ifdef DEBUG
#define DBG(format, arg...) elog(NOTICE, format , ## arg);\
fprintf(f, format, ## arg)
//Don't treat debug comments
#else
#define DBG(format, arg...) do { ; } while (0)
#endif
#define TUPLIMIT 1000
//Convert text to char
static char * text2char(text *in)
{
char *out = palloc(VARSIZE(in));
memcpy(out, VARDATA(in), VARSIZE(in) - VARHDRSZ);
out[VARSIZE(in) - VARHDRSZ] = '\0';
return out;
}
//finish SPI
static int finish(int code, int ret)
{
code = SPI_finish();
if (code != SPI_OK_FINISH )
{
elog(ERROR,"Unable to disconnect from SPI");
return -1 ;
}
return ret;
}
//Puissance_m Data Structure----------------------------------------------
//is a structure to store a matrix
typedef struct puissance_matrix
{
int nl; //number of lines
int nc; //number of columns
int** data; //2D integer data
} puissance_m;
//delete a puissance_m structure
void delete_puissance_m(puissance_m * p)
{
int i;
for(i=0;i<p->nl;++i) pfree(p->data[i]);
pfree(p->data);
pfree(p);
}
//create a puissance_m structure
puissance_m create_puissance_m(int nl, int nc, FILE* f)
{
DBG("create_puissance_m\n");
puissance_m A;
int i,j;
A.nc=nc;
A.nl=nl;
A.data = (int**) palloc(nl * sizeof(int *));
for(i=0;i<nl;i++)
{ A.data[i] = (int *) palloc( nc * sizeof(int));}
for (i=0; i<nl; i++)
for (j=0; j<nc; j++)
{A.data[i][j]=0;}
return A;
}
//show puissance_m structure's datas
void affiche_puissance_m(puissance_m *pm, FILE* f)
{
DBG("show_puissance_m\n");
int i,j;
DBG("matrix [%i] [%i] ::\n", pm->nc, pm->nl);
for (i = 0; i < pm->nl; i++)
{
for (j = 0; j < pm->nc; j++)
{
DBG("%i " ,pm->data[i][j]);
}
DBG("\n");
}
}
//show puissance_m structure's datas and pointer values
void analyse_puissance_m(int i, puissance_m *pm, FILE* f)
{
int a,b;
DBG("%i: BLOCK ANALYSE\n", i);
DBG("%i: pointer value of pm [%i]\n", i, &pm);
DBG("%i: pointer value of pm->nc [%i] stores (%i)\n", i,
&(pm->nc), pm->nc);
DBG("%i: pointer value of pm->nl [%i] stores (%i)\n", i,
&(pm->nl), pm->nl);
DBG("%i: pointer value of pm->data [] and stored value () ::", i);
for (a=0;a< pm->nl;a++)
for (b=0;b< pm->nc;b++)
{
DBG(" [%i] (%i) ::", &(pm->data[a][b]), pm->data[a][b]);
}
DBG("\n");
}
//compute_puissance_table ------------------------------------------------
static int compute_puissance_table(char* sql, puissance_m* pm,
int *path_count, FILE* f)
{
int SPIcode;
void *SPIplan;
Portal SPIportal;
bool moredata = TRUE;
int ntuples;
int total_tuples = 0;
int ret = -1;
register int z, t;
DBG("compute_puissance_table\n");
SPIcode = SPI_connect();
if (SPIcode != SPI_OK_CONNECT)
{
DBG("Unable to open SPI\n");
elog(ERROR, "compute_puissance_table: unable to open SPI\n");
return -1;
}
DBG("Connexion SPI OK\n");
SPIplan = SPI_prepare(sql, 0, NULL);
if (SPIplan == NULL)
{
DBG("Unable to create plan\n");
elog(ERROR,
"compute_puissance_table: unable to create plan for query %s\n",
sql);
return -1;
}
DBG("Plan SPI OK\n");
if ((SPIportal = SPI_cursor_open(NULL, SPIplan, NULL, NULL, true)) == NULL)
{
DBG("SPI cursor returns NULL\n");
elog(ERROR,
"compute_puissance_table: SPI_cursor_open('%s') returns NULL\n",
sql);
return -1;
}
DBG("Curseur SPI OK\n");
while (moredata == TRUE)
{
//I suppose that I'll do this step only one time
//cause I work with small tables
SPI_cursor_fetch(SPIportal, TRUE, TUPLIMIT);
ntuples = SPI_processed;
total_tuples += ntuples;
if (ntuples > 0)
{
DBG("There are tuples to treat :: ");
int t;
SPITupleTable *tuptable = SPI_tuptable;
TupleDesc tupdesc = SPI_tuptable->tupdesc;
int nc=tupdesc->natts; //number of columns
int nl=total_tuples; //number of lines
*path_count=nl; //copy nl in the memory block pointed by path_count
DBG("%i lines and %i columns\n", nl, nc);
if (nl!=nc)
{
DBG("Erreur : we want square tables and matrices\n");
elog(ERROR,
"compute_puissance_table: '%s' gives a non square table",
sql);
return -1;
}
DBG("Initialisation of puissance_m\n");
int i,j;
*pm=create_puissance_m(nl,nc,f);
DBG("Update of puissance_m\n");
bool isnull;
for (i = 0; i < nl; i++)
{
DBG("Tuple number %i\n", i+1);
HeapTuple tuple = tuptable->vals[i];
for (j = 0; j < nc; j++)
{
pm->data[i][j]= DatumGetInt32(SPI_getbinval(tuple,
tupdesc, j, &isnull));
}
}
DBG("There are no tuples to treat\n");
affiche_puissance_m(pm,f);
SPI_freetuptable(tuptable);
}
else
{
moredata = FALSE;
}
}
DBG("Exit of compute_puissance_table\n");
ret=1;
return finish(SPIcode, ret);
}
// Main function --------------------------------------------------
PG_FUNCTION_INFO_V1(puissance_table);
PGMODULEEXPORT Datum puissance_table(PG_FUNCTION_ARGS)
{
int a,b;
FILE* f;
f = fopen("log.txt", "w"); //This is my debug report file
DBG("Entry of puissance_table\n");
FuncCallContext *funcctx;
int call_cntr;
int max_calls;
TupleDesc tuple_desc;
puissance_m *pm;
if (SRF_IS_FIRSTCALL())
{
DBG("puissance_table::SRF IS FIRST CALL\n");
MemoryContext oldcontext;
int path_count = 0;
int ret;
DBG("puissance_table::SRF FIRST CALL INIT\n");
funcctx = SRF_FIRSTCALL_INIT();
DBG("puissance_table::SWITCH CONTEXT\n");
oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
DBG("puissance_table::compute puissance table\n");
ret = compute_puissance_table(text2char(PG_GETARG_TEXT_P(0)),
pm, &path_count, f);
DBG("puissance_table:: result of compute puissance table\n");
analyse_puissance_m(0,pm,f);
funcctx->max_calls = path_count;
funcctx->user_fctx = pm;
//I have a problem with the following line
//funcctx->tuple_desc = BlessTupleDesc(
// RelationNameGetTupleDesc("path_result"));
//so I use get_call_result_type to access my tupdsec
TupleDesc my_tupdesc;
get_call_result_type(fcinfo, NULL, &my_tupdesc);
funcctx->tuple_desc = BlessTupleDesc(my_tupdesc);
MemoryContextSwitchTo(oldcontext);
}
funcctx = SRF_PERCALL_SETUP();
call_cntr = funcctx->call_cntr;
max_calls = funcctx->max_calls;
tuple_desc = funcctx->tuple_desc;
pm = (puissance_m*) funcctx->user_fctx;
if (call_cntr < max_calls)
{
HeapTuple tuple;
Datum result;
Datum *values;
char* nulls;
/******************PROBLEMATIK************************/
DBG("PROBLEMATIK at step %i\n" , call_cntr);
analyse_puissance_m(1,pm,f);
DBG("Building nulls\n");
nulls = palloc(pm->nc * sizeof(bool));
analyse_puissance_m(2,pm,f);
DBG("2: pointer value of nulls [%i]\n", &nulls);
DBG("2: pointer value of nulls elements :");
for (b=0;b<pm->nc;b++)
{ DBG(" [%i] ::", &(nulls[b])); }
DBG("\n");
DBG("Building values\n");
values = palloc(pm->nc * sizeof(Datum));
analyse_puissance_m(2,pm,f);
DBG("3: pointer value of values [%i]\n", &values);
DBG("3: pointer value of values elements ::");
for (b=0;b<pm->nc;b++)
{DBG(" [%i] ::", &(values[b])); }
DBG("\n");
DBG("END OF PROBLEMATIK \n");
/******************PROBLEMATIK************************/
int i;
for (i=0; i<pm->nc;i++)
{
DBG("Values %i stores %i\n", i, pm->data[call_cntr][i]);
values[i] = Int32GetDatum(pm->data[call_cntr][i]);
nulls[i] = ' ';
}
tuple = heap_formtuple(tuple_desc, values, nulls);
result = HeapTupleGetDatum(tuple);
pfree(values);
pfree(nulls);
SRF_RETURN_NEXT(funcctx, result);
}
else
{
fclose(f);
SRF_RETURN_DONE(funcctx);
}
}
/*************************************************************************/
BOOL APIENTRY DllMain (HINSTANCE hInst ,
DWORD reason ,
LPVOID reserved )
{
switch (reason)
{
case DLL_PROCESS_ATTACH:
break;
case DLL_PROCESS_DETACH:
break;
case DLL_THREAD_ATTACH:
break;
case DLL_THREAD_DETACH:
break;
}
return TRUE;
}
END OF THE CODE OF DLLMAIN.C
----------------------
I USE THE FOLOWING CODE IN POSTGRE TO BUILD THE FUNCTION:
CREATE OR REPLACE FUNCTION __test_c_build_array_from_tuples(my_matrix_in, int)
RETURNS int[]
AS '$libdir/my_lib_c/test_set_of_clean','build_array_from_tuples'
LANGUAGE 'C' IMMUTABLE STRICT;
----------------------
I USE THE FOLOWING CODE IN POSTGRE TO TEST THE FUNCTION:
DROP TABLE my_matrix_in;
CREATE TABLE my_matrix_in(f1 int, f2 int, f3 int, f4 int);
INSERT INTO my_matrix_in VALUES(1, 0, 2, 0);
INSERT INTO my_matrix_in VALUES(0, 1, 0, 0);
INSERT INTO my_matrix_in VALUES(0, 0, 1, 0);
INSERT INTO my_matrix_in VALUES(0, 0, 0, 1);
select a,b,c,d from __test_c_puissance_table('select * from my_matrix_in')
as t(a int, b int, c int, d int);
--
M. François Benoît-Marand.
