On 2019-May-30, Alvaro Herrera wrote:
> One thing I noticed while writing it, though, is that worker_spi uses
> the postgres database, instead of the contrib_regression database that
> was created for it. And we create a schema and a table there. This is
> going to get some eyebrows raised, I think, so I'll look into fixing
> that as a bugfix before getting this commit in.
Another thing I noticed when fixing *this*, in turn, is that if you load
worker_spi in shared_preload_libraries then the contrib_regression
database doesn't exist by the point that runs, so those workers fail to
start. The dynamic one does start in the configured database.
I guess we could just ignore the failures and just rely on the dynamic
worker.
I ended up with these two patches. I'm not sure about pushing
separately. It seems pointless to backport the "fix" to back branches
anyway.
--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/src/test/modules/worker_spi/worker_spi.c b/src/test/modules/worker_spi/worker_spi.c
index c1878dd694..bc9ef64a50 100644
--- a/src/test/modules/worker_spi/worker_spi.c
+++ b/src/test/modules/worker_spi/worker_spi.c
@@ -55,6 +55,7 @@ static volatile sig_atomic_t got_sigterm = false;
/* GUC variables */
static int worker_spi_naptime = 10;
static int worker_spi_total_workers = 2;
+static char *worker_spi_database = NULL;
typedef struct worktable
@@ -179,7 +180,7 @@ worker_spi_main(Datum main_arg)
BackgroundWorkerUnblockSignals();
/* Connect to our database */
- BackgroundWorkerInitializeConnection("postgres", NULL, 0);
+ BackgroundWorkerInitializeConnection(worker_spi_database, NULL, 0);
elog(LOG, "%s initialized with %s.%s",
MyBgworkerEntry->bgw_name, table->schema, table->name);
@@ -339,6 +340,15 @@ _PG_init(void)
NULL,
NULL);
+ DefineCustomStringVariable("worker_spi.database",
+ "Database to connect to.",
+ NULL,
+ &worker_spi_database,
+ "postgres",
+ PGC_POSTMASTER,
+ 0,
+ NULL, NULL, NULL);
+
/* set up common data for all our workers */
memset(&worker, 0, sizeof(worker));
worker.bgw_flags = BGWORKER_SHMEM_ACCESS |
--
2.17.1
diff --git a/src/test/modules/worker_spi/Makefile b/src/test/modules/worker_spi/Makefile
index 7cdb33c9df..cbf9b2e37f 100644
--- a/src/test/modules/worker_spi/Makefile
+++ b/src/test/modules/worker_spi/Makefile
@@ -6,6 +6,14 @@ EXTENSION = worker_spi
DATA = worker_spi--1.0.sql
PGFILEDESC = "worker_spi - background worker example"
+REGRESS = worker_spi
+
+# enable our module in shared_preload_libraries for dynamic bgworkers
+REGRESS_OPTS = --temp-config $(top_srcdir)/src/test/modules/worker_spi/dynamic.conf
+
+# Disable installcheck to ensure we cover dynamic bgworkers.
+NO_INSTALLCHECK = 1
+
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/src/test/modules/worker_spi/dynamic.conf b/src/test/modules/worker_spi/dynamic.conf
new file mode 100644
index 0000000000..bfe015f664
--- /dev/null
+++ b/src/test/modules/worker_spi/dynamic.conf
@@ -0,0 +1,2 @@
+shared_preload_libraries = worker_spi
+worker_spi.database = contrib_regression
diff --git a/src/test/modules/worker_spi/expected/worker_spi.out b/src/test/modules/worker_spi/expected/worker_spi.out
new file mode 100644
index 0000000000..dc0a79bf75
--- /dev/null
+++ b/src/test/modules/worker_spi/expected/worker_spi.out
@@ -0,0 +1,50 @@
+CREATE EXTENSION worker_spi;
+SELECT worker_spi_launch(4) IS NOT NULL;
+ ?column?
+----------
+ t
+(1 row)
+
+-- wait until the worker completes its initialization
+DO $$
+DECLARE
+ visible bool;
+ loops int := 0;
+BEGIN
+ LOOP
+ visible := table_name IS NOT NULL
+ FROM information_schema.tables
+ WHERE table_schema = 'schema4' AND table_name = 'counted';
+ IF visible OR loops > 120 * 10 THEN EXIT; END IF;
+ PERFORM pg_sleep(0.1);
+ loops := loops + 1;
+ END LOOP;
+END
+$$;
+INSERT INTO schema4.counted VALUES ('total', 0), ('delta', 1);
+SELECT pg_reload_conf();
+ pg_reload_conf
+----------------
+ t
+(1 row)
+
+-- wait until the worker has processed the tuple we just inserted
+DO $$
+DECLARE
+ count int;
+ loops int := 0;
+BEGIN
+ LOOP
+ count := count(*) FROM schema4.counted WHERE type = 'delta';
+ IF count = 0 OR loops > 120 * 10 THEN EXIT; END IF;
+ PERFORM pg_sleep(0.1);
+ loops := loops + 1;
+ END LOOP;
+END
+$$;
+SELECT * FROM schema4.counted;
+ type | value
+-------+-------
+ total | 1
+(1 row)
+
diff --git a/src/test/modules/worker_spi/sql/worker_spi.sql b/src/test/modules/worker_spi/sql/worker_spi.sql
new file mode 100644
index 0000000000..4683523b29
--- /dev/null
+++ b/src/test/modules/worker_spi/sql/worker_spi.sql
@@ -0,0 +1,35 @@
+CREATE EXTENSION worker_spi;
+SELECT worker_spi_launch(4) IS NOT NULL;
+-- wait until the worker completes its initialization
+DO $$
+DECLARE
+ visible bool;
+ loops int := 0;
+BEGIN
+ LOOP
+ visible := table_name IS NOT NULL
+ FROM information_schema.tables
+ WHERE table_schema = 'schema4' AND table_name = 'counted';
+ IF visible OR loops > 120 * 10 THEN EXIT; END IF;
+ PERFORM pg_sleep(0.1);
+ loops := loops + 1;
+ END LOOP;
+END
+$$;
+INSERT INTO schema4.counted VALUES ('total', 0), ('delta', 1);
+SELECT pg_reload_conf();
+-- wait until the worker has processed the tuple we just inserted
+DO $$
+DECLARE
+ count int;
+ loops int := 0;
+BEGIN
+ LOOP
+ count := count(*) FROM schema4.counted WHERE type = 'delta';
+ IF count = 0 OR loops > 120 * 10 THEN EXIT; END IF;
+ PERFORM pg_sleep(0.1);
+ loops := loops + 1;
+ END LOOP;
+END
+$$;
+SELECT * FROM schema4.counted;
--
2.17.1
