hi Martin,

I know the binary copy utility in postgresql there is a similar function PQputCopyData, it can reduce time by 20 times .

the following is the function i write my result into database PostgreSQL:

void writeToDBBinary(struct SAMPLE *points, char *fileName, int fileType) { //my process result is in input parameter struct SAMPLE

PGconn *conn = NULL;

PGresult *pgrst = NULL;

conn = PQsetdbLogin(host, port, options, tty, dbname, user, password);

char *buf = (char*) malloc(1024 * sizeof (char));

unsigned short fieldNum = 21;

char *sendHeader = "PGCOPY\n\377\r\n\0";

unsigned int zero = '\0';

struct strBuffer strBuf;

strBuf.data = buf;

strBuf.len = MAX_BUFFER;

int i = 0;

int j = 0;

int k = 0;

struct SAMPLE *tSample;

if (fileType) { //insert reference file

//total 19 column, not include cid

sprintf(sqlBuf, "COPY %s(\

starid,crossid,catid,magnorm,ra,dec,background,classstar,ellipticity,flags,mag,mage,fwhm,pixx,pixy,thetaimage,vignet,\

magcalib,magcalibe,pixx1,pixy1 \

)FROM STDIN WITH BINARY", match_table);

pgrst = PQexec(conn, sqlBuf);

if (PQresultStatus(pgrst) == PGRES_COPY_IN) {

strBuf.cursor = 0;

memcpy(strBuf.data, sendHeader, 11);

strBuf.cursor += 11;

memcpy(strBuf.data + strBuf.cursor, (char*) &zero, 4);

strBuf.cursor += 4;

memcpy(strBuf.data + strBuf.cursor, (char*) &zero, 4);

strBuf.cursor += 4;

tSample = points->next;

while (tSample) {

addInt16(&strBuf, fieldNum); //column number, when add or delete colume, must change this number

addInt64(&strBuf, tSample->id);

addInt64(&strBuf, tSample->crossid);

addInt64(&strBuf, catid);

addFloat8(&strBuf, -1.0);

addFloat8(&strBuf, tSample->alpha);

addFloat8(&strBuf, tSample->delta);

addFloat8(&strBuf, tSample->background);

addFloat8(&strBuf, tSample->classstar);

addFloat8(&strBuf, tSample->ellipticity);

addFloat8(&strBuf, tSample->flags);

addFloat8(&strBuf, tSample->mag);

addFloat8(&strBuf, tSample->mage);

addFloat8(&strBuf, tSample->fwhm);

addFloat8(&strBuf, tSample->pixx);

addFloat8(&strBuf, tSample->pixy);

addFloat8(&strBuf, tSample->thetaimage);

addFloat8(&strBuf, tSample->vignet);

addFloat8(&strBuf, tSample->magcalib);

addFloat8(&strBuf, tSample->magcalibe);

addFloat8(&strBuf, tSample->pixx);

addFloat8(&strBuf, tSample->pixy);

int copydatares = PQputCopyData(conn, strBuf.data, strBuf.cursor);

//if(i >5) break;

i++;

tSample = tSample->next;

strBuf.cursor = 0;

}

PQputCopyEnd(conn, NULL);

} else {

printf("can not copy in!\n");

}

PQclear(pgrst);

so i am sure it's worthy to implement binarycopy in monetdb.

2013/6/28 Martin Kersten <martin@monetdb.org>

Hi

On 6/28/13 7:25 AM, hlfwm hlfwm wrote:

i want to create a monetdb function to help write my astronomical *binary* processing result into database,this function will load the binary result from memory directly into monetdb to save time, so i defined a UDF binaryCopy() ,

I am not sure such a function would improve your system much, because
it depends on the relative time compared to your astronomical processing.
Furthermore, to make sense you have to go all the way to link your
astro library as UDF as well.
Another issue is that you bypass the transaction management layers,
which makes the system sensitive for incorrectness.

In general, it is better to first identify 'how' bad the (BINARY) COPY
into would be in the total setup.

Martin

svom.c

int binaryCopy(str columnname, str buffer, int count)
{
mvc *sql = NULL;
sql_table *tmatch = NULL;
/*BATs in which the data of the columns of the output table will be stored*/
BAT *tmatch_column = NULL;
BUN nr_rows;
/* pointer to the tail of above BAT, which is just a normal C-array */
lng *tmatch_column_lng_t = NULL;
flt *tmatch_column_flt_t = NULL;
/*_bind_table is just a wrapper that tries to see if it can find the table
match_table under three default schemas*/
if(!(tmatch = _bind_table(sql, NULL, "testtable")))
//return sql_message("42S02!BINARYLOAD():no such table test");
return 0;
//nr_match = 20000;
nr_rows = count;
/*
create BATs for all columns of the output table 'tmatch'
*/
if(strcmp(columnname,"starid")==0 || strcmp(columnname,"crossid")==0 ||strcmp(columnname,"catid")==0 ) {
tmatch_column = BATnew(TYPE_void, TYPE_lng,nr_rows);
}else {
tmatch_column = BATnew(TYPE_void,TYPE_flt,nr_rows);
}

/*
now tmatch_starid_t points to the first element of the BAT's tails
*/
tmatch_column_lng_t = (lng*)Tloc(tmatch_column,BUNfirst(tmatch_column));
/*
now we can write data into the BAT tail
*/
if((strcmp(columnname,"starid")==0) || (strcmp(columnname,"crossid")==0) ||(strcmp(columnname,"catid")==0) ) {
*memcpy(tmatch_column_lng_t,buffer,nr_rows*sizeof(lng)); //is it right to write binary data into BAT???*

}else {
memcpy(tmatch_column_flt_t, buffer, nr_rows*sizeof(flt));
}

/* Set proper properties for all BATs */
BATsetcount(tmatch_column,nr_rows);

BATseqbase(tmatch_column,0);
/*
sorted:1,column is sorted in ascending order

*/
tmatch_column->tsorted = 0;

/*
if the tail is reverse sorted.
*/
tmatch_column->trevsorted = 0;

/*
if the tail contains NIL values
*/
tmatch_column->T->nil = 0;
/*
if the tail doesn't contain NIL values
*/
tmatch_column->T->nonil = 0;

/*
if the values in the tail are unique.Since BATkey only works with BAT heads,
* BATmirror is used to switch the head and tail of the BATs
*/
if(strcmp(columnname,"starid")==0) {
BATkey(BATmirror(tmatch_column),1); //only starid is 1
}else {
BATkey(BATmirror(tmatch_column),0);
}
/*
finally, append the result BATs to the result table
* ignore errors, since we are finishing
*/
_append_bat(sql,tmatch,columnname,tmatch_column);
/*
The BATs now contain the data for the columns of the output tables,
* append_bat registers in the system where to find the BAT for those columns.
*/
/*
To avoid memory leak, BAT reference counter must be decreased after use.
* That can be done using BBPunfix.
*/
if(tmatch_column) BBPunfix(tmatch_column->batCacheid);

return 1; //success
}

Thanks!

_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list

_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list