[postgis-tickets] r15274 - Use a cached prepared statement for loading topology records

[Sandro Santilli]

Author: strk
Date: 2016-12-10 01:33:42 -0800 (Sat, 10 Dec 2016)
New Revision: 15274

Modified:
   trunk/topology/postgis_topology.c
Log:
Use a cached prepared statement for loading topology records

Modified: trunk/topology/postgis_topology.c
===================================================================
--- trunk/topology/postgis_topology.c	2016-12-10 09:33:36 UTC (rev 15273)
+++ trunk/topology/postgis_topology.c	2016-12-10 09:33:42 UTC (rev 15274)
@@ -159,27 +159,42 @@
 cb_loadTopologyByName(const LWT_BE_DATA* be, const char *name)
 {
   int spi_result;
-  StringInfoData sqldata;
-  StringInfo sql = &sqldata;
+  const char *sql;
   Datum dat;
   bool isnull;
   LWT_BE_TOPOLOGY *topo;
   MemoryContext oldcontext = CurrentMemoryContext;
+  Datum values[1];
+  Oid argtypes[1];
+  static SPIPlanPtr plan = NULL;
 
-  initStringInfo(sql);
-  appendStringInfo(sql, "SELECT id,srid,precision,null::geometry"
-                        " FROM topology.topology "
-                        "WHERE name = '%s'", name);
-  spi_result = SPI_execute(sql->data, !be->data_changed, 0);
+  // prepare
+  if ( ! plan ) {
+    sql = "SELECT id,srid,precision,null::geometry"
+                          " FROM topology.topology "
+                          "WHERE name = $1::varchar";
+    argtypes[0] = CSTRINGOID;
+    plan = SPI_prepare(sql, 1, argtypes);
+    if ( ! plan )
+    {
+      cberror(be, "unexpected return (%d) from query preparation: %s",
+              SPI_result, sql);
+      return NULL;
+    }
+    SPI_keepplan(plan);
+    // SPI_freeplan to free, eventually
+  }
+
+  // execute
+  values[0] = CStringGetDatum(name);
+  spi_result = SPI_execute_plan(plan, values, NULL, !be->data_changed, 1);
   MemoryContextSwitchTo( oldcontext ); /* switch back */
   if ( spi_result != SPI_OK_SELECT ) {
-		cberror(be, "unexpected return (%d) from query execution: %s", spi_result, sql->data);
-    pfree(sqldata.data);
+		cberror(be, "unexpected return (%d) from query execution: %s", spi_result, sql);
 	  return NULL;
   }
   if ( ! SPI_processed )
   {
-    pfree(sqldata.data);
 		//cberror(be, "no topology named '%s' was found", name);
     if ( be->topoLoadFailMessageFlavor == 1 ) {
       cberror(be, "No topology with name \"%s\" in topology.topology", name);
@@ -190,11 +205,9 @@
   }
   if ( SPI_processed > 1 )
   {
-    pfree(sqldata.data);
 		cberror(be, "multiple topologies named '%s' were found", name);
 	  return NULL;
   }
-  pfree(sqldata.data);
 
   topo = palloc(sizeof(LWT_BE_TOPOLOGY));
   topo->be_data = (LWT_BE_DATA *)be; /* const cast.. */