[Gdal-dev] Improving postgresql performance by using COPY
Charlie Savage
cfis at interserv.com
Fri Oct 14 03:22:53 EDT 2005
Hi everyone,
I've found that loading a lot of data into Postgresql/PostGIS is a bit
on the slow side. After some investigation, it turns out that using
Postgresql's COPY function is significantly faster than INSERT.
It turns out that libq, which OGR uses, provides API support for COPY,
So as an experiment I updated the OGR Postgresql driver to use it (its
added functionality, it does not change the current functionality). As
a test case, I loaded the Tiger 2004 data for the state of Colorado.
Timings on my WinXP IBM thinkpad are:
* 60 minutes to load using INSERT.
* 20 minutes to load using COPY.
I've also noticed a lot of activity in the postgresql groups in the last
few months about patches that make COPY significantly faster, so
hopefully in future release this difference becomes even more pronounced.
Anyway, as a comparison, loading the data from a text postgresql dump
file, which also uses COPY, takes 12 minutes - so that sets a the
minimum load time for this data using my setup.
Due to the great speed increase, I'd like to propose adding support for
COPY to the OGR postgresql driver.
Of course, there are some technical issues that need to be discussed
first though.
1. Start/End Copy
You have to tell Postgresql you are running a copy command before you
start, then you copy each feature, and then you tell Postgresql that you
are done. Right now, the ogr2ogr translate feature doesn't provide
hooks for doing that. ogr2ogr does do something similar though - it
call startransaction / endtransaction from the translate method when
starting / ending a data load.
What I did for my prototype was implement two new methods onOGRLayer -
StartTranslation and EndTranslation. In the general case they do
nothing, in the Postgresql they setup the COPY commands if a user has
requested that (see discussion below).
2. Transactions
COPY runs in one big transaction. While running COPY you cannot run
other SQL commands (like begin/commit). However, OGR automatically
commits every 200 transactions unless you specify otherwise using the
undocumented ogr2ogr -tg parameter and set it to -1. However, that's
obviously an ugly hack.
Proposal
It seems to me there is a concept of translation options that depends on
the destination data source. In the case of Postgresql, you may wish to
specify bulk loading of data using COPY or you may prefer using INSERT
if you have a small amount of data.
I think transaction grouping (i.e., the -tg parameter) is similar - it
applies only to some data sources, and effects how the translation
occurs. So, I would propose a generic solution which would be
implemented by adding a -lto option to ogr2ogr which would stand for
'layer specific translation options' and then folding the -tg option
into it. To make this more concrete:
-f PostgreSQL -lto bulk_load=true tg=0 PG:"dbname=test user=postgres"
C:\temp\tiger2004fe\CO
Then I would then update ogr2ogr to call StartTranslation /
EndTranslation in the translate method. I would also, and this might be
controversial, remove the StartTransaction/EndTransaction calls from the
the translate method. My reasoning is that these are datasource
specific, and specific for any particular data loading run, and
therefore should be called from StartTranslation/EndTranslation methods
where applicable.
Anyway, what I'm trying to achieve it to provide generic pre- and post-
processing hooks that make it easier to do things like what I'm trying
with the Postgresql driver.
The main argument I see against this is that drivers would have to
maintain a bit more state (the -lto options). For example, in
Postgresql, the driver will take different actions when CreateFeature is
called depending on whether COPY or INSERT should be called. Thus in
the StartTranslation method it would have to set a flag and do some
pre-processing, and then in the EndTranslation method perform the proper
cleanup. Maintaining the state could be avoided by also passing the
-lto options to CreateFeature if we wanted to go down that route.
Anyway, I think that transactions are similar in concept to this so that
boundary has already been crossed.
Note that these changes are all backwards compatible and will not break
existing code.
3. EWKB PostGIS Format
The last issue is that loading data via COPY requires putting geometry
in the HEX encoded EWKB format that PostGis uses. Unfortunately that
EWKB format is different than what OGR uses. The PostGIS format
includes support for embedding a SRS and a M Value. Thus I had to
create a OGR EWKB format, then pick it apart to add the SRS, then HEX
encode it. All doable, but it would be nice if OGR and PostGIS were in
line with each other. Any chance of that happening?
Interested in hearing feedback on this proposal. I've attached a patch
that implements most of what I describe above. Note I would not
consider this a "final" patch ready for committing (although it
successfully loads the Tiger data) - its just to show what I have in mind.
Thanks,
Charlie
----------------------------------------------
Index: ogr/ogr2ogr.cpp
===================================================================
RCS file: /cvs/maptools/cvsroot/gdal/ogr/ogr2ogr.cpp,v
retrieving revision 1.28
diff -u -w -b -r1.28 ogr2ogr.cpp
--- ogr/ogr2ogr.cpp 14 Apr 2005 14:20:24 -0000 1.28
+++ ogr/ogr2ogr.cpp 13 Oct 2005 06:46:32 -0000
@@ -126,6 +126,7 @@
OGRLayer * poSrcLayer,
OGRDataSource *poDstDS,
char ** papszLSCO,
+ char ** papszLTO,
const char *pszNewLayerName,
int bTransform,
OGRSpatialReference *poOutputSRS,
@@ -149,7 +150,7 @@
const char *pszDataSource = NULL;
const char *pszDestDataSource = NULL;
char **papszLayers = NULL;
- char **papszDSCO = NULL, **papszLCO = NULL;
+ char **papszDSCO = NULL, **papszLCO = NULL, **papszLTO = NULL;
int bTransform = FALSE;
int bAppend = FALSE, bUpdate = FALSE;
const char *pszOutputSRSDef = NULL;
@@ -186,6 +187,10 @@
{
papszLCO = CSLAddString(papszLCO, papszArgv[++iArg] );
}
+ else if( EQUAL(papszArgv[iArg],"-lto") && iArg < nArgc-1 )
+ {
+ papszLTO = CSLAddString(papszLTO, papszArgv[++iArg] );
+ }
else if( EQUAL(papszArgv[iArg],"-preserve_fid") )
{
bPreserveFID = TRUE;
@@ -456,7 +461,7 @@
if( poResultSet != NULL )
{
- if( !TranslateLayer( poDS, poResultSet, poODS, papszLCO,
+ if( !TranslateLayer( poDS, poResultSet, poODS, papszLCO,
papszLTO,
pszNewLayerName, bTransform, poOutputSRS,
poSourceSRS, papszSelFields, bAppend,
eGType))
{
@@ -496,7 +501,7 @@
if( poSpatialFilter != NULL )
poLayer->SetSpatialFilter( poSpatialFilter );
- if( !TranslateLayer( poDS, poLayer, poODS, papszLCO,
+ if( !TranslateLayer( poDS, poLayer, poODS, papszLCO, papszLTO,
pszNewLayerName, bTransform, poOutputSRS,
poSourceSRS, papszSelFields, bAppend,
eGType)
&& !bSkipFailures )
@@ -543,6 +548,7 @@
" [[-dsco NAME=VALUE] ...]
dst_datasource_name\n"
" src_datasource_name\n"
" [-lco NAME=VALUE] [-nln name] [-nlt type]
layer [layer ...]]\n"
+ " [-lto NAME=VALUE]\n"
"\n"
" -f format_name: output file format name, possible values
are:\n");
@@ -564,6 +570,7 @@
" -spat xmin ymin xmax ymax: spatial query extents\n"
" -dsco NAME=VALUE: Dataset creation option (format
specific)\n"
" -lco NAME=VALUE: Layer creation option (format specific)\n"
+ " -lto NAME=VALUE: Layer translation options (format
specific)\n"
" -nln name: Assign an alternate name to the new layer\n"
" -nlt type: Force a geometry type for new layer. One of
NONE, GEOMETRY,\n"
" POINT, LINESTRING, POLYGON, GEOMETRYCOLLECTION,
MULTIPOINT, MULTILINE,\n"
@@ -589,6 +596,7 @@
OGRLayer * poSrcLayer,
OGRDataSource *poDstDS,
char **papszLCO,
+ char **papszLTO,
const char *pszNewLayerName,
int bTransform,
OGRSpatialReference *poOutputSRS,
@@ -742,6 +750,8 @@
if( nGroupTransactions )
poDstLayer->StartTransaction();
+ poDstLayer->StartTranslation(papszLTO);
+
while( TRUE )
{
OGRFeature *poDstFeature = NULL;
@@ -772,6 +782,8 @@
if( poDstFeature->SetFrom( poFeature, TRUE ) != OGRERR_NONE )
{
+ poDstLayer->EndTranslation();
+
if( nGroupTransactions )
poDstLayer->CommitTransaction();
@@ -792,6 +804,8 @@
eErr = poDstFeature->GetGeometryRef()->transform( poCT );
if( eErr != OGRERR_NONE )
{
+ poDstLayer->EndTranslation();
+
if( nGroupTransactions )
poDstLayer->CommitTransaction();
@@ -826,6 +840,8 @@
if( poDstLayer->CreateFeature( poDstFeature ) != OGRERR_NONE
&& !bSkipFailures )
{
+ poDstLayer->EndTranslation();
+
if( nGroupTransactions )
poDstLayer->RollbackTransaction();
@@ -836,6 +852,8 @@
OGRFeature::DestroyFeature( poDstFeature );
}
+ poDstLayer->EndTranslation(papszLTO);
+
if( nGroupTransactions )
poDstLayer->CommitTransaction();
Index: ogr/ogrsf_frmts/generic/ogrlayer.cpp
===================================================================
RCS file: /cvs/maptools/cvsroot/gdal/ogr/ogrsf_frmts/generic/ogrlayer.cpp,v
retrieving revision 1.24
diff -u -w -b -r1.24 ogrlayer.cpp
--- ogr/ogrsf_frmts/generic/ogrlayer.cpp 22 Feb 2005 12:47:47 -0000 1.24
+++ ogr/ogrsf_frmts/generic/ogrlayer.cpp 13 Oct 2005 00:10:08 -0000
@@ -525,6 +525,7 @@
bApproxOK );
}
+
/************************************************************************/
/* StartTransaction() */
/************************************************************************/
@@ -619,6 +620,17 @@
/* GetSpatialFilter() */
/************************************************************************/
+OGRErr OGRLayer::StartTranslation(char ** papszOptions )
+{
+ return OGRERR_NONE;
+}
+
+OGRErr OGRLayer::EndTranslation(char ** papszOptions )
+{
+ return OGRERR_NONE;
+}
+
+
OGRGeometry *OGRLayer::GetSpatialFilter()
{
Index: ogr/ogrsf_frmts/pg/ogrpglayer.cpp
===================================================================
RCS file: /cvs/maptools/cvsroot/gdal/ogr/ogrsf_frmts/pg/ogrpglayer.cpp,v
retrieving revision 1.22
diff -u -w -b -r1.22 ogrpglayer.cpp
--- ogr/ogrsf_frmts/pg/ogrpglayer.cpp 30 Sep 2005 19:11:16 -0000 1.22
+++ ogr/ogrsf_frmts/pg/ogrpglayer.cpp 14 Oct 2005 00:45:30 -0000
@@ -115,6 +115,12 @@
#define INV_READ 0x00040000
#endif
+/* Flags for creating WKB format for PostGIS */
+#define WKBZOFFSET 0x80000000
+#define WKBMOFFSET 0x40000000
+#define WKBSRIDFLAG 0x20000000
+#define WKBBBOXFLAG 0x10000000
+
/************************************************************************/
/* OGRPGLayer() */
/************************************************************************/
@@ -561,6 +567,84 @@
}
/************************************************************************/
+/* GeometryToHex() */
+/************************************************************************/
+void OGRPGLayer::ByteToHex( GByte *pabyData, char *pszBuf, int count )
+{
+ const char* hex = "0123456789ABCDEF";
+
+ for( int i = 0; i < count; i++ )
+ {
+ GByte byte = pabyData[i];
+ pszBuf[i*2] = hex[byte >> 4];
+ pszBuf[i*2+1] = hex[byte & 0xF];
+ }
+}
+
+
+char *OGRPGLayer::GeometryToHex( OGRGeometry * poGeometry, int nSRSId )
+{
+ GByte *pabyWKB;
+ char *pszTextBuf;
+ char *pszTextBufCurrent;
+
+ int nWkbSize = poGeometry->WkbSize();
+ pabyWKB = (GByte *) CPLMalloc(nWkbSize);
+
+ if( poGeometry->exportToWkb( wkbNDR, pabyWKB ) != OGRERR_NONE )
+ {
+ CPLFree( pabyWKB );
+ return CPLStrdup("");
+ }
+
+ /* When converting to hex, each byte takes 2 hex characters. In
addition
+ we add in 8 characters to represent the SRID integer in hex, and
+ one for a null terminator */
+
+ int pszSize = nWkbSize*2 + 8 + 1;
+ pszTextBuf = (char *) CPLMalloc(pszSize);
+ pszTextBufCurrent = pszTextBuf;
+
+ /* Copy the 1st byte which is the endianess flag. */
+ ByteToHex(pabyWKB, pszTextBuf, 1);
+ pszTextBufCurrent += 2;
+
+ /* Next, get the geom type which is bytes 2 through 5 */
+ GUInt32 geomType = (GUInt32)(*(pabyWKB+1));
+
+ /* Now add the SRID flag if an SRID is provided */
+ if (nSRSId != -1)
+ {
+ /* Change the flag to wkbNDR (little) endianess */
+ GUInt32 nGSrsFlag = CPL_LSBWORD32( WKBSRIDFLAG );
+ /* Apply the flag */
+ geomType = geomType | nGSrsFlag;
+ }
+
+ /* Now write the geom type which is 4 bytes */
+ ByteToHex((GByte*)&geomType, pszTextBufCurrent, 4);
+ pszTextBufCurrent += 8;
+
+ /* Now include SRID if provided */
+ if (nSRSId != -1)
+ {
+ /* Force the srsid to wkbNDR (little) endianess */
+ GUInt32 nGSRSId = CPL_LSBWORD32( nSRSId );
+ ByteToHex((GByte*) &nGSRSId, pszTextBufCurrent, sizeof(nGSRSId));
+ pszTextBufCurrent += 8;
+ }
+
+ /* Copy the rest of the data over - subtract
+ 5 since we already copied 5 bytes above */
+ ByteToHex(pabyWKB + 5, pszTextBufCurrent, nWkbSize - 5);
+
+ CPLFree( pabyWKB );
+
+ return pszTextBuf;
+}
+
+
+/************************************************************************/
/* BYTEAToGeometry() */
/************************************************************************/
Index: ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp
===================================================================
RCS file:
/cvs/maptools/cvsroot/gdal/ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp,v
retrieving revision 1.37
diff -u -w -b -r1.37 ogrpgtablelayer.cpp
--- ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp 26 Sep 2005 04:37:17 -0000 1.37
+++ ogr/ogrsf_frmts/pg/ogrpgtablelayer.cpp 14 Oct 2005 04:53:24 -0000
@@ -730,8 +730,215 @@
/************************************************************************/
/* CreateFeature() */
/************************************************************************/
-
OGRErr OGRPGTableLayer::CreateFeature( OGRFeature *poFeature )
+{
+ if (bBulkLoad)
+ {
+ return CreateFeatureViaCopy( poFeature );
+ }
+ else
+ {
+ return CreateFeatureViaInsert( poFeature );
+ }
+}
+
+OGRErr OGRPGTableLayer::CreateFeatureViaCopy( OGRFeature *poFeature )
+{
+ int nCommandBufSize = 4000;
+
+ /* First process geometry */
+ OGRGeometry *poGeometry = (OGRGeometry *) poFeature->GetGeometryRef();
+
+ char *pszGeom = NULL;
+ if ( poGeometry )
+ {
+ poGeometry->closeRings();
+ poGeometry->setCoordinateDimension( nCoordDimension );
+
+ pszGeom = GeometryToHex( poGeometry, nSRSId );
+ nCommandBufSize = nCommandBufSize + strlen(pszGeom);
+ }
+
+ char *pszCommand = (char *) CPLMalloc(nCommandBufSize);
+
+ if ( poGeometry )
+ {
+ sprintf( pszCommand, "%s", pszGeom);
+ CPLFree( pszGeom );
+ }
+ else
+ {
+ sprintf( pszCommand, "\\N");
+ }
+ sprintf( pszCommand, "\\N");
+ strcat( pszCommand, "\t" );
+
+
+ /* Next process the field id column */
+ if( bHasFid && poFeatureDefn->GetFieldIndex( pszFIDColumn ) != -1 )
+ {
+ /* Set the FID */
+ if( poFeature->GetFID() != OGRNullFID )
+ {
+ sprintf( pszCommand + strlen(pszCommand), "%ld ",
poFeature->GetFID());
+ }
+ else
+ {
+ strcat( pszCommand, "\\N" );
+ }
+
+ strcat( pszCommand, "\t" );
+ }
+
+
+ /* Now process the remaining fields */
+ int nOffset = strlen(pszCommand);
+
+ int nFieldCount = poFeatureDefn->GetFieldCount();
+ for( int i = 0; i < nFieldCount; i++ )
+ {
+ const char *pszStrValue = poFeature->GetFieldAsString(i);
+ char *pszNeedToFree = NULL;
+
+ if( !poFeature->IsFieldSet( i ) )
+ {
+ strcat( pszCommand, "\\N" );
+
+ if( i < nFieldCount - 1 )
+ strcat( pszCommand, "\t" );
+
+ continue;
+ }
+
+ // We need special formatting for integer list values.
+ if( poFeatureDefn->GetFieldDefn(i)->GetType() == OFTIntegerList )
+ {
+ int nCount, nOff = 0, j;
+ const int *panItems =
poFeature->GetFieldAsIntegerList(i,&nCount);
+
+ pszNeedToFree = (char *) CPLMalloc(nCount * 13 + 10);
+ strcpy( pszNeedToFree, "{" );
+ for( j = 0; j < nCount; j++ )
+ {
+ if( j != 0 )
+ strcat( pszNeedToFree+nOff, "," );
+
+ nOff += strlen(pszNeedToFree+nOff);
+ sprintf( pszNeedToFree+nOff, "%d", panItems[j] );
+ }
+ strcat( pszNeedToFree+nOff, "}" );
+ pszStrValue = pszNeedToFree;
+ }
+
+ // We need special formatting for real list values.
+ if( poFeatureDefn->GetFieldDefn(i)->GetType() == OFTRealList )
+ {
+ int nCount, nOff = 0, j;
+ const double *padfItems
=poFeature->GetFieldAsDoubleList(i,&nCount);
+
+ pszNeedToFree = (char *) CPLMalloc(nCount * 40 + 10);
+ strcpy( pszNeedToFree, "{" );
+ for( j = 0; j < nCount; j++ )
+ {
+ if( j != 0 )
+ strcat( pszNeedToFree+nOff, "," );
+
+ nOff += strlen(pszNeedToFree+nOff);
+ sprintf( pszNeedToFree+nOff, "%.16g", padfItems[j] );
+ }
+ strcat( pszNeedToFree+nOff, "}" );
+ pszStrValue = pszNeedToFree;
+ }
+
+ // Grow the command buffer?
+ if( strlen(pszStrValue) + strlen(pszCommand+nOffset) + nOffset
+ > nCommandBufSize-50 )
+ {
+ nCommandBufSize = strlen(pszCommand) + strlen(pszStrValue)
+ 10000;
+ pszCommand = (char *) CPLRealloc(pszCommand, nCommandBufSize );
+ }
+
+ if( poFeatureDefn->GetFieldDefn(i)->GetType() != OFTInteger
+ && poFeatureDefn->GetFieldDefn(i)->GetType() != OFTReal )
+ {
+ int iChar;
+
+ nOffset += strlen(pszCommand+nOffset);
+
+ for( iChar = 0; pszStrValue[iChar] != '\0'; iChar++ )
+ {
+ if( poFeatureDefn->GetFieldDefn(i)->GetType() !=
OFTIntegerList
+ && poFeatureDefn->GetFieldDefn(i)->GetType() !=
OFTRealList
+ && poFeatureDefn->GetFieldDefn(i)->GetWidth() > 0
+ && iChar ==
poFeatureDefn->GetFieldDefn(i)->GetWidth() )
+ {
+ CPLDebug( "PG",
+ "Truncated %s field value, it was too long.",
+
poFeatureDefn->GetFieldDefn(i)->GetNameRef() );
+ break;
+ }
+
+ /* Escape embedded \, \t, \n, \r since they will cause COPY
+ to misinterpret a line of text and thus abort */
+ if( pszStrValue[iChar] == '\\' ||
+ pszStrValue[iChar] == '\t' ||
+ pszStrValue[iChar] == '\r' ||
+ pszStrValue[iChar] == '\n' )
+ {
+ pszCommand[nOffset++] = '\\';
+ }
+
+ pszCommand[nOffset++] = pszStrValue[iChar];
+ }
+
+ pszCommand[nOffset] = '\0';
+// strcat( pszCommand+nOffset, "'" );
+ }
+ else
+ {
+ strcat( pszCommand+nOffset, pszStrValue );
+ }
+
+ if( pszNeedToFree )
+ CPLFree( pszNeedToFree );
+
+ if( i < nFieldCount - 1 )
+ strcat( pszCommand, "\t" );
+ }
+
+ /* Add end of line marker */
+ strcat( pszCommand, "\n" );
+
+ /* ------------------------------------------------------------ */
+ /* Execute the copy. */
+ /* ------------------------------------------------------------ */
+ PGconn *hPGConn = poDS->GetPGConn();
+ int copyResult = PQputCopyData(hPGConn, pszCommand,
strlen(pszCommand));
+
+ OGRErr result = OGRERR_NONE;
+
+ switch (copyResult)
+ {
+ case 0:
+ CPLDebug( "OGR_PG", "PQexec(%s)\n", pszCommand );
+ CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
+ result = OGRERR_FAILURE;
+ break;
+ case -1:
+ CPLDebug( "OGR_PG", "PQexec(%s)\n", pszCommand );
+ CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
+ result = OGRERR_FAILURE;
+ break;
+ }
+
+ /* Free the buffer we allocated before returning */
+ CPLFree( pszCommand );
+
+ return result;
+}
+
+
+OGRErr OGRPGTableLayer::CreateFeatureViaInsert( OGRFeature *poFeature )
{
PGconn *hPGConn = poDS->GetPGConn();
@@ -1013,6 +1220,7 @@
}
+
/************************************************************************/
/* TestCapability() */
/************************************************************************/
@@ -1386,3 +1594,126 @@
return OGRLayer::GetExtent( psExtent, bForce );
}
+
+char *OGRPGTableLayer::BuildCopyFields()
+{
+ int i, nSize;
+ char *pszFieldList;
+
+ nSize = 25;
+ if( pszGeomColumn )
+ nSize += strlen(pszGeomColumn);
+
+ if( bHasFid && poFeatureDefn->GetFieldIndex( pszFIDColumn ) != -1 )
+ nSize += strlen(pszFIDColumn);
+
+ for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
+ nSize += strlen(poFeatureDefn->GetFieldDefn(i)->GetNameRef()) + 4;
+
+ pszFieldList = (char *) CPLMalloc(nSize);
+ pszFieldList[0] = '\0';
+
+ if( bHasFid && poFeatureDefn->GetFieldIndex( pszFIDColumn ) != -1 )
+ sprintf( pszFieldList, "\"%s\"", pszFIDColumn );
+
+ if( pszGeomColumn )
+ {
+ if( strlen(pszFieldList) > 0 )
+ strcat( pszFieldList, ", " );
+
+ sprintf( pszFieldList+strlen(pszFieldList),
+ "\"%s\"", pszGeomColumn );
+ }
+
+ for( i = 0; i < poFeatureDefn->GetFieldCount(); i++ )
+ {
+ const char *pszName = poFeatureDefn->GetFieldDefn(i)->GetNameRef();
+
+ if( strlen(pszFieldList) > 0 )
+ strcat( pszFieldList, ", " );
+
+ strcat( pszFieldList, "\"" );
+ strcat( pszFieldList, pszName );
+ strcat( pszFieldList, "\"" );
+ }
+
+ CPLAssert( (int) strlen(pszFieldList) < nSize );
+
+ return pszFieldList;
+}
+
+
+OGRErr OGRPGTableLayer::StartTranslation(char ** papszOptions )
+{
+ bBulkLoad = CSLFetchBoolean(papszOptions, "BULK_LOAD", FALSE);
+
+ if (bBulkLoad)
+ {
+ char *pszFields = BuildCopyFields();
+
+ int size = strlen(pszFields) + strlen(poFeatureDefn->GetName()) + 100;
+ char *pszCommand = (char *) CPLMalloc(size);
+
+ sprintf( pszCommand,
+ "COPY \"%s\" (%s) FROM STDIN;",
+ poFeatureDefn->GetName(), pszFields );
+
+ CPLFree( pszFields );
+
+ PGconn *hPGConn = poDS->GetPGConn();
+ PGresult *hResult = PQexec(hPGConn, pszCommand);
+
+ if ( !hResult || (PQresultStatus(hResult) != PGRES_COPY_IN))
+ {
+ CPLError( CE_Failure, CPLE_AppDefined,
+ "%s", PQerrorMessage(hPGConn) );
+ CPLFree( pszCommand );
+ return OGRERR_FAILURE;
+ }
+ else
+ {
+ PQclear( hResult );
+ }
+
+ CPLFree( pszCommand );
+ }
+ return OGRERR_NONE;
+}
+
+OGRErr OGRPGTableLayer::EndTranslation(char ** papszOptions )
+{
+ OGRErr result = OGRERR_NONE;
+
+ if (bBulkLoad)
+ {
+ PGconn *hPGConn = poDS->GetPGConn();
+ int result = PQputCopyEnd(hPGConn, NULL);
+
+ switch (result)
+ {
+ case 0:
+ CPLError( CE_Failure, CPLE_AppDefined, "Writing COPY data blocked.");
+ return OGRERR_FAILURE;
+ case -1:
+ CPLError( CE_Failure, CPLE_AppDefined, "%s", PQerrorMessage(hPGConn) );
+ return OGRERR_FAILURE;
+ }
+
+ /* Get the final result of the copy */
+ PGresult * hResult = PQgetResult( hPGConn );
+
+ if( PQresultStatus(hResult) != PGRES_COMMAND_OK )
+ {
+ CPLError( CE_Failure, CPLE_AppDefined,
+ "COPY statement failed.\n%s",
+ PQerrorMessage(hPGConn) );
+
+ result = OGRERR_FAILURE;
+ }
+
+ if (hResult)
+ PQclear(hResult);
+
+ return result;
+ }
+}
Index: ogr/ogrsf_frmts/pg/ogr_pg.h
===================================================================
RCS file: /cvs/maptools/cvsroot/gdal/ogr/ogrsf_frmts/pg/ogr_pg.h,v
retrieving revision 1.20
diff -u -w -b -r1.20 ogr_pg.h
--- ogr/ogrsf_frmts/pg/ogr_pg.h 6 Aug 2005 14:49:27 -0000 1.20
+++ ogr/ogrsf_frmts/pg/ogr_pg.h 13 Oct 2005 19:43:58 -0000
@@ -119,6 +119,8 @@
char *GeometryToBYTEA( OGRGeometry * );
OGRGeometry *BYTEAToGeometry( const char * );
OGRGeometry *HEXToGeometry( const char * );
+ void ByteToHex( GByte *pabyData, char *pszBuf, int
count );
+ char *GeometryToHex( OGRGeometry * poGeometry, int
nSRSId );
Oid GeometryToOID( OGRGeometry * );
OGRGeometry *OIDToGeometry( Oid );
@@ -185,6 +187,10 @@
int bLaunderColumnNames;
int bPreservePrecision;
+ bool bBulkLoad;
+ OGRErr CreateFeatureViaCopy( OGRFeature *poFeature );
+ OGRErr CreateFeatureViaInsert( OGRFeature *poFeature );
+ char *BuildCopyFields(void);
public:
OGRPGTableLayer( OGRPGDataSource *,
const char * pszName,
@@ -217,6 +223,9 @@
{ bLaunderColumnNames = bFlag; }
void SetPrecisionFlag( int bFlag )
{ bPreservePrecision = bFlag; }
+
+ virtual OGRErr StartTranslation(char ** papszOptions );
+ virtual OGRErr EndTranslation(char ** papszOptions );
};
/************************************************************************/
Index: ogr/ogrsf_frmts/ogrsf_frmts.h
===================================================================
RCS file: /cvs/maptools/cvsroot/gdal/ogr/ogrsf_frmts/ogrsf_frmts.h,v
retrieving revision 1.53
diff -u -w -b -r1.53 ogrsf_frmts.h
--- ogr/ogrsf_frmts/ogrsf_frmts.h 5 Sep 2005 19:31:45 -0000 1.53
+++ ogr/ogrsf_frmts/ogrsf_frmts.h 12 Oct 2005 23:44:53 -0000
@@ -264,6 +264,9 @@
virtual OGRErr CommitTransaction();
virtual OGRErr RollbackTransaction();
+ virtual OGRErr StartTranslation(char ** papszOptions = NULL );
+ virtual OGRErr EndTranslation(char ** papszOptions = NULL );
+
int Reference();
int Dereference();
int GetRefCount() const;
More information about the Gdal-dev
mailing list