# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1570639633 -7200
# Node ID 209b10f7bb2c3f633f85e0846ac10739dbbbe379
# Parent  30bb2d819d57ed88b99375cc0083f7e0faa8fba6
Add config for GeoServer database session startup SQL

Prepended to setting the role in order to avoid any configuration
overwriting it.

diff -r 30bb2d819d57 -r 209b10f7bb2c example_conf.toml
--- a/example_conf.toml	Wed Oct 09 16:40:18 2019 +0200
+++ b/example_conf.toml	Wed Oct 09 18:47:13 2019 +0200
@@ -56,6 +56,17 @@
 # an explicit --geoserver-clean on the command line.
 #geoserver-clean = true
 
+# geoserver-startup-sql can be used to add SQL commands to GeoServers database
+# session start script. See
+# https://docs.geoserver.org/stable/en/user/data/database/sqlsession.html
+# Note that the commands will be executed as the user configured as 'db-user'.
+# This can be used for example to set query planer configurations that fix
+# bad query plans during GeoServer accessing the published layers.
+# Currently, the published layer 'bottlenecks_geoserver' is known to trigger
+# inefficient nested loop joins due to underestimation of the number of entries
+# in the bottlenecks table. Hence the example setting:
+geoserver-startup-sql = "SET enable_nestloop TO off"
+
 # Proxy settings for external OGC services
 #proxy-key = "SECRET"
 #proxy-prefix = "http://localhost:8000"
diff -r 30bb2d819d57 -r 209b10f7bb2c pkg/config/config.go
--- a/pkg/config/config.go	Wed Oct 09 16:40:18 2019 +0200
+++ b/pkg/config/config.go	Wed Oct 09 18:47:13 2019 +0200
@@ -103,6 +103,14 @@
 // rebooting.
 func GeoServerClean() bool { return viper.GetBool("geoserver-clean") }
 
+// GeoServerStartupSQL can be used to add SQL commands to GeoServers database
+// session start script. See
+// https://docs.geoserver.org/stable/en/user/data/database/sqlsession.html
+// Note that the commands will be executed as the user configured as 'db-user'.
+func GeoServerStartupSQL() string {
+	return viper.GetString("geoserver-startup-sql")
+}
+
 // TmpDir is the path where to store temporary files.
 // If left empty the system default for temporary files is used.
 func TmpDir() string { return viper.GetString("tmp-dir") }
diff -r 30bb2d819d57 -r 209b10f7bb2c pkg/geoserver/boot.go
--- a/pkg/geoserver/boot.go	Wed Oct 09 16:40:18 2019 +0200
+++ b/pkg/geoserver/boot.go	Wed Oct 09 18:47:13 2019 +0200
@@ -209,7 +209,8 @@
 					{"dbtype", databaseType},
 					{"Primary key metadata table", primaryKeyMetadataTbl},
 					{"Expose primary keys", true},
-					{"Session startup SQL", startupSQL},
+					{"Session startup SQL",
+						config.GeoServerStartupSQL() + ";" + startupSQL},
 					{"Session close-up SQL", closeupSQL},
 					{"validate connections", true},
 					{"Estimated extends", false},