Re: [HACKERS] possibility to specify template database for pg_regress

[Jim Nasby]

On 2/14/17 2:49 PM, Pavel Stehule wrote:

> Tom's use case might be more easily served by specifying a
> template database. I don't think Pavel ever posted his use case.

Wait, that's precisely what Pavel asked?


I would to use regress test environment in my current case. 99% code in
plpgsql, but there is pretty complex schema. About 300 tables. 1k views.
2k functions. Import schema is slow. Database clonning is much faster.


FWIW, for actual production environments (which I assume this is), I 
find pg_regress to be completely useless. Some simple shell scripts to 
build the database (possibly using sqitch) and then a script around 
pg_prove is what I normally use. https://github.com/BlueTreble/db_tools 
gives you the general idea.



Speaking for myself, my normal pattern is to have a number of separate
pg_regress suites, each of which ends up loading the extension under test.
Loading a large extension can end up being very time consuming; enough so
that I'd expect it to be much faster to create the temp cluster, load all
the prereq's once in some template database, and then use that template for
most/all of the tests.


I seriously doubt that. CREATE DATABASE is ridiculously expensive,
copies everything on the file-level and requires checkpoints.  If your
extension is more expensive than that, I'd say you're likely doing
something wrong.


That depends on the extension. pgTap for example contains over 900 
functions. A quick test on my laptop shows it's faster to create a 
database from a template containing the extension than it is to create 
the extension itself.


decibel@decina:[12:33]~$time createdb t

real0m0.433s
user0m0.004s
sys 0m0.009s
decibel@decina:[12:34]~$time psql -c 'create extension pgtap' t
CREATE EXTENSION

real0m0.559s
user0m0.002s
sys 0m0.007s
decibel@decina:[12:34]~$time createdb -T t t2

real0m0.441s
user0m0.002s
sys 0m0.005s
decibel@decina:[12:34]~$time psql -c 'drop extension pgtap' t
DROP EXTENSION

real0m0.197s
user0m0.002s
sys 0m0.006s
decibel@decina:[12:34]~$time dropdb t

real0m0.189s
user0m0.003s
sys 0m0.007s
decibel@decina:[12:34]~$time dropdb t2

real0m0.154s
user0m0.002s
sys 0m0.005s
decibel@decina:[12:34]~$

Interestingly, CREATE EXTENSION is 2x faster than simply running the file:

decibel@decina:[12:38]~$time psql -qtf 
$PGDATA/../share/extension/pgtap--0.97.0.sql t


real0m1.225s
user0m0.044s
sys 0m0.028s
decibel@decina:[12:39]~$

decibel@decina:[12:41]~$(echo 'begin;'; echo "\i 
$PGDATA/../share/extension/pgtap--0.97.0.sql"; echo 'commit;') | time 
psql -qt t

1.12 real 0.04 user 0.02 sys
decibel@decina:[12:41]~$
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

Dne 14. 2. 2017 21:35 napsal uživatel "Andres Freund" :

On 2017-02-14 14:29:56 -0600, Jim Nasby wrote:
> On 2/14/17 1:59 PM, Andres Freund wrote:
> > > AFAIK if you're doing make check (as opposed to installcheck) it's
> > > significantly more complicated than that since you'd have to create a
temp
> > > cluster/install yourself.
> >
> > But in that case you can't have useful templates in the regression test
> > either, so the whole discussion is moot?
>
> At that point it depends on what you're trying to do. Presumably
separating
> cluster control would make it much easier to script createdb/dropdb as you
> suggested.

That's not what I responded to...


> Tom's use case might be more easily served by specifying a
> template database. I don't think Pavel ever posted his use case.

Wait, that's precisely what Pavel asked?


I would to use regress test environment in my current case. 99% code in
plpgsql, but there is pretty complex schema. About 300 tables. 1k views. 2k
functions. Import schema is slow. Database clonning is much faster.


On 2017-02-07 16:43:47 +0100, Pavel Stehule wrote:
> Is possible to specify template database for pg_regress?
>
> I have to run tests on database with thousands database objects. Using
> template is much faster than import these objects.

Obviously that only makes sense with installcheck.


> Speaking for myself, my normal pattern is to have a number of separate
> pg_regress suites, each of which ends up loading the extension under test.
> Loading a large extension can end up being very time consuming; enough so
> that I'd expect it to be much faster to create the temp cluster, load all
> the prereq's once in some template database, and then use that template
for
> most/all of the tests.

I seriously doubt that. CREATE DATABASE is ridiculously expensive,
copies everything on the file-level and requires checkpoints.  If your
extension is more expensive than that, I'd say you're likely doing
something wrong.

- Andres

Re: [HACKERS] possibility to specify template database for pg_regress

[Andres Freund]

On 2017-02-14 14:29:56 -0600, Jim Nasby wrote:
> On 2/14/17 1:59 PM, Andres Freund wrote:
> > > AFAIK if you're doing make check (as opposed to installcheck) it's
> > > significantly more complicated than that since you'd have to create a temp
> > > cluster/install yourself.
> >
> > But in that case you can't have useful templates in the regression test
> > either, so the whole discussion is moot?
> 
> At that point it depends on what you're trying to do. Presumably separating
> cluster control would make it much easier to script createdb/dropdb as you
> suggested.

That's not what I responded to...


> Tom's use case might be more easily served by specifying a
> template database. I don't think Pavel ever posted his use case.

Wait, that's precisely what Pavel asked?

On 2017-02-07 16:43:47 +0100, Pavel Stehule wrote:
> Is possible to specify template database for pg_regress?
> 
> I have to run tests on database with thousands database objects. Using
> template is much faster than import these objects.

Obviously that only makes sense with installcheck.


> Speaking for myself, my normal pattern is to have a number of separate
> pg_regress suites, each of which ends up loading the extension under test.
> Loading a large extension can end up being very time consuming; enough so
> that I'd expect it to be much faster to create the temp cluster, load all
> the prereq's once in some template database, and then use that template for
> most/all of the tests.

I seriously doubt that. CREATE DATABASE is ridiculously expensive,
copies everything on the file-level and requires checkpoints.  If your
extension is more expensive than that, I'd say you're likely doing
something wrong.

- Andres


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Jim Nasby]

On 2/14/17 1:59 PM, Andres Freund wrote:

AFAIK if you're doing make check (as opposed to installcheck) it's
significantly more complicated than that since you'd have to create a temp
cluster/install yourself.

>

But in that case you can't have useful templates in the regression test
either, so the whole discussion is moot?


At that point it depends on what you're trying to do. Presumably 
separating cluster control would make it much easier to script 
createdb/dropdb as you suggested. Tom's use case might be more easily 
served by specifying a template database. I don't think Pavel ever 
posted his use case.


Speaking for myself, my normal pattern is to have a number of separate 
pg_regress suites, each of which ends up loading the extension under 
test. Loading a large extension can end up being very time consuming; 
enough so that I'd expect it to be much faster to create the temp 
cluster, load all the prereq's once in some template database, and then 
use that template for most/all of the tests. In that scenario separating 
cluster create/drop would certainly be useful, but the template option 
would probably be helpful as well (though since pg_regress' diff-based 
methodology just gets in my way I'd likely use some other harness to 
actually run the tests).

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Andres Freund]

On 2017-02-14 12:33:35 -0600, Jim Nasby wrote:
> On 2/13/17 8:50 PM, Andres Freund wrote:
> > On 2017-02-14 11:46:52 +0900, Michael Paquier wrote:
> > > > I still fail to see why --use-existing as suggested in
> > > > https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvq...@alap3.anarazel.de
> > > > isn't sufficient.
> > > 
> > > Some tests create objects without removing them, meaning that
> > > continuous runs would fail with only --use-existing. This patch brings
> > > value in such cases.
> > 
> > You can trivially script the CREATE/DROP DB outside with
> > --use-existing. Which seems a lot more flexible than adding more and
> > more options to pg_regress.
> 
> AFAIK if you're doing make check (as opposed to installcheck) it's
> significantly more complicated than that since you'd have to create a temp
> cluster/install yourself.

But in that case you can't have useful templates in the regression test
either, so the whole discussion is moot?


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Jim Nasby]

On 2/13/17 8:50 PM, Andres Freund wrote:

On 2017-02-14 11:46:52 +0900, Michael Paquier wrote:

I still fail to see why --use-existing as suggested in
https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvq...@alap3.anarazel.de
isn't sufficient.


Some tests create objects without removing them, meaning that
continuous runs would fail with only --use-existing. This patch brings
value in such cases.


You can trivially script the CREATE/DROP DB outside with
--use-existing. Which seems a lot more flexible than adding more and
more options to pg_regress.


AFAIK if you're doing make check (as opposed to installcheck) it's 
significantly more complicated than that since you'd have to create a 
temp cluster/install yourself.


As an extension author, I'd *love* to have the cluster management stuff 
in pg_regress broken out: it's the only reason I use pg_regress, and 
pg_regress's idea of what a test failure is just gets in my way. But 
breaking that out is far more invasive than allowing a template database.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

2017-02-14 3:36 GMT+01:00 Andres Freund :

> On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:
> > Hi
> >
> > 2017-02-13 6:46 GMT+01:00 Michael Paquier :
> >
> > > On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <
> pavel.steh...@gmail.com>
> > > wrote:
> > > > here is new update - check is done before any creating
> > >
> > > It may be better to do any checks before dropping existing databases
> > > as well... It would be as well just simpler to complain with a single
> > > error message like "database and template list lengths do not match".
> > >
> >
> > next step
>
> I still fail to see why --use-existing as suggested in
> https://www.postgresql.org/message-id/20170208002900.
> vkldujzfkwbvq...@alap3.anarazel.de
> isn't sufficient.
>
>
I checked it - and it is not hard - but you have to overwrite some makefile
rules - and then you spend some time with makefile hacking

Possibility to set template removes all this dirty work. Setting
"REGRESS_OPTS += --template=mytests-template" is simple, clean and readable

Regards

Pavel



> - Andres
>

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

2017-02-14 3:50 GMT+01:00 Andres Freund :

> On 2017-02-14 11:46:52 +0900, Michael Paquier wrote:
> > > I still fail to see why --use-existing as suggested in
> > > https://www.postgresql.org/message-id/20170208002900.
> vkldujzfkwbvq...@alap3.anarazel.de
> > > isn't sufficient.
> >
> > Some tests create objects without removing them, meaning that
> > continuous runs would fail with only --use-existing. This patch brings
> > value in such cases.
>
> You can trivially script the CREATE/DROP DB outside with
> --use-existing. Which seems a lot more flexible than adding more and
> more options to pg_regress.
>

Using template is natural and very simply solution - more it doesn't need
any outer scripts - so infrastructure for test can be pretty simply in this
case.

Regards

Pavel

Re: [HACKERS] possibility to specify template database for pg_regress

[Andres Freund]

On 2017-02-14 11:46:52 +0900, Michael Paquier wrote:
> > I still fail to see why --use-existing as suggested in
> > https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvq...@alap3.anarazel.de
> > isn't sufficient.
> 
> Some tests create objects without removing them, meaning that
> continuous runs would fail with only --use-existing. This patch brings
> value in such cases.

You can trivially script the CREATE/DROP DB outside with
--use-existing. Which seems a lot more flexible than adding more and
more options to pg_regress.


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Michael Paquier]

On Tue, Feb 14, 2017 at 11:36 AM, Andres Freund  wrote:
> On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:
>> Hi
>>
>> 2017-02-13 6:46 GMT+01:00 Michael Paquier :
>>
>> > On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule 
>> > wrote:
>> > > here is new update - check is done before any creating
>> >
>> > It may be better to do any checks before dropping existing databases
>> > as well... It would be as well just simpler to complain with a single
>> > error message like "database and template list lengths do not match".
>> >
>>
>> next step

This looks fine to me.

> I still fail to see why --use-existing as suggested in
> https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvq...@alap3.anarazel.de
> isn't sufficient.

Some tests create objects without removing them, meaning that
continuous runs would fail with only --use-existing. This patch brings
value in such cases.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Andres Freund]

On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:
> Hi
> 
> 2017-02-13 6:46 GMT+01:00 Michael Paquier :
> 
> > On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule 
> > wrote:
> > > here is new update - check is done before any creating
> >
> > It may be better to do any checks before dropping existing databases
> > as well... It would be as well just simpler to complain with a single
> > error message like "database and template list lengths do not match".
> >
> 
> next step

I still fail to see why --use-existing as suggested in
https://www.postgresql.org/message-id/20170208002900.vkldujzfkwbvq...@alap3.anarazel.de
isn't sufficient.

- Andres


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

Hi

2017-02-13 6:46 GMT+01:00 Michael Paquier :

> On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule 
> wrote:
> > here is new update - check is done before any creating
>
> It may be better to do any checks before dropping existing databases
> as well... It would be as well just simpler to complain with a single
> error message like "database and template list lengths do not match".
>

next step

Regards

Pavel


> --
> Michael
>
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index d4d00d9c66..ef0542ad0c 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -68,6 +68,7 @@ const char *pretty_diff_opts = "-w -C3";
 
 /* options settable from command line */
 _stringlist *dblist = NULL;
+_stringlist *templatelist = NULL;
 bool		debug = false;
 char	   *inputdir = ".";
 char	   *outputdir = ".";
@@ -1907,7 +1908,7 @@ drop_database_if_exists(const char *dbname)
 }
 
 static void
-create_database(const char *dbname)
+create_database(const char *dbname, const char *template)
 {
 	_stringlist *sl;
 
@@ -1917,10 +1918,12 @@ create_database(const char *dbname)
 	 */
 	header(_("creating database \"%s\""), dbname);
 	if (encoding)
-		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=template0 ENCODING='%s'%s", dbname, encoding,
+		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=\"%s\" ENCODING='%s'%s",
+	 dbname, template, encoding,
 	 (nolocale) ? " LC_COLLATE='C' LC_CTYPE='C'" : "");
 	else
-		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=template0%s", dbname,
+		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=\"%s\"%s",
+	 dbname, template,
 	 (nolocale) ? " LC_COLLATE='C' LC_CTYPE='C'" : "");
 	psql_command(dbname,
  "ALTER DATABASE \"%s\" SET lc_messages TO 'C';"
@@ -1995,6 +1998,7 @@ help(void)
 	printf(_("  --outputdir=DIR   place output files in DIR (default \".\")\n"));
 	printf(_("  --schedule=FILE   use test ordering schedule from FILE\n"));
 	printf(_("(can be used multiple times to concatenate)\n"));
+	printf(_("  --template=DB use template DB (default \"template0\")\n"));
 	printf(_("  --temp-instance=DIR   create a temporary instance in DIR\n"));
 	printf(_("  --use-existinguse an existing installation\n"));
 	printf(_("\n"));
@@ -2041,10 +2045,12 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 		{"launcher", required_argument, NULL, 21},
 		{"load-extension", required_argument, NULL, 22},
 		{"config-auth", required_argument, NULL, 24},
+		{"template", required_argument, NULL, 25},
 		{NULL, 0, NULL, 0}
 	};
 
 	_stringlist *sl;
+	_stringlist *tl;
 	int			c;
 	int			i;
 	int			option_index;
@@ -2154,6 +2160,16 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 			case 24:
 config_auth_datadir = pg_strdup(optarg);
 break;
+			case 25:
+
+/*
+ * If a default template was specified, we need to remove it
+ * before we add the specified one.
+ */
+free_stringlist();
+split_to_stringlist(optarg, ",", );
+break;
+
 			default:
 /* getopt_long already emitted a complaint */
 fprintf(stderr, _("\nTry \"%s -h\" for more information.\n"),
@@ -2205,6 +2221,18 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 	unlimit_core_size();
 #endif
 
+	/* The length of template list should be same like db list */
+	if (templatelist != NULL)
+	{
+		for (sl = dblist, tl = templatelist; sl && tl; sl = sl->next, tl = tl->next);
+		if (sl || tl)
+		{
+			fprintf(stderr, _("%s: database and template list lengths do not match\n"),
+	progname);
+			exit(2);
+		}
+	}
+
 	if (temp_instance)
 	{
 		FILE	   *pg_conf;
@@ -2454,8 +2482,17 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 	 */
 	if (!use_existing)
 	{
-		for (sl = dblist; sl; sl = sl->next)
-			create_database(sl->str);
+		if (templatelist != NULL)
+		{
+			for (sl = dblist, tl = templatelist; sl; sl = sl->next, tl = tl->next)
+create_database(sl->str, tl->str);
+		}
+		else
+		{
+			for (sl = dblist; sl; sl = sl->next)
+create_database(sl->str, "template0");
+		}
+
 		for (sl = extraroles; sl; sl = sl->next)
 			create_role(sl->str, dblist);
 	}

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Michael Paquier]

On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule  wrote:
> here is new update - check is done before any creating

It may be better to do any checks before dropping existing databases
as well... It would be as well just simpler to complain with a single
error message like "database and template list lengths do not match".
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

Hi

2017-02-10 6:00 GMT+01:00 Michael Paquier :

> On Thu, Feb 9, 2017 at 5:13 AM, Pavel Stehule 
> wrote:
> > here is a patch
>
> Thanks.
>
> -   for (sl = dblist; sl; sl = sl->next)
> -   create_database(sl->str);
> +   if (templatelist != NULL)
> +   {
> +   _stringlist *tl;
> +
> +   for (sl = dblist, tl = templatelist; sl; sl = sl->next, tl
> = tl->next)
> +   {
> +   if (tl != NULL)
> +   create_database(sl->str, tl->str);
> +   else
> +   {
> +   fprintf(stderr, _("%s: the template list is
> shorter than database list\n"),
> +   progname);
> +   exit(2);
> +   }
> +   }
> +   }
> +   else
> +   for (sl = dblist; sl; sl = sl->next)
> +   create_database(sl->str, "template0");
> There is one problem here: if the length of the template list is
> shorter than the database list, databases get halfly created, then
> pg_regress complains, letting the instance in a half-way state. I
> think that you had better do any sanity checks before creating or even
> dropping existing databases.
>

here is new update - check is done before any creating

Regards

Pavel


> --
> Michael
>
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index d4d00d9..b5f5c2f 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -68,6 +68,7 @@ const char *pretty_diff_opts = "-w -C3";
 
 /* options settable from command line */
 _stringlist *dblist = NULL;
+_stringlist *templatelist = NULL;
 bool		debug = false;
 char	   *inputdir = ".";
 char	   *outputdir = ".";
@@ -1907,7 +1908,7 @@ drop_database_if_exists(const char *dbname)
 }
 
 static void
-create_database(const char *dbname)
+create_database(const char *dbname, const char *template)
 {
 	_stringlist *sl;
 
@@ -1917,10 +1918,12 @@ create_database(const char *dbname)
 	 */
 	header(_("creating database \"%s\""), dbname);
 	if (encoding)
-		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=template0 ENCODING='%s'%s", dbname, encoding,
+		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=\"%s\" ENCODING='%s'%s",
+	 dbname, template, encoding,
 	 (nolocale) ? " LC_COLLATE='C' LC_CTYPE='C'" : "");
 	else
-		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=template0%s", dbname,
+		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=\"%s\"%s",
+	 dbname, template,
 	 (nolocale) ? " LC_COLLATE='C' LC_CTYPE='C'" : "");
 	psql_command(dbname,
  "ALTER DATABASE \"%s\" SET lc_messages TO 'C';"
@@ -1995,6 +1998,7 @@ help(void)
 	printf(_("  --outputdir=DIR   place output files in DIR (default \".\")\n"));
 	printf(_("  --schedule=FILE   use test ordering schedule from FILE\n"));
 	printf(_("(can be used multiple times to concatenate)\n"));
+	printf(_("  --template=DB use template DB (default \"template0\")\n"));
 	printf(_("  --temp-instance=DIR   create a temporary instance in DIR\n"));
 	printf(_("  --use-existinguse an existing installation\n"));
 	printf(_("\n"));
@@ -2041,6 +2045,7 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 		{"launcher", required_argument, NULL, 21},
 		{"load-extension", required_argument, NULL, 22},
 		{"config-auth", required_argument, NULL, 24},
+		{"template", required_argument, NULL, 25},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -2154,6 +2159,16 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 			case 24:
 config_auth_datadir = pg_strdup(optarg);
 break;
+			case 25:
+
+/*
+ * If a default template was specified, we need to remove it
+ * before we add the specified one.
+ */
+free_stringlist();
+split_to_stringlist(optarg, ",", );
+break;
+
 			default:
 /* getopt_long already emitted a complaint */
 fprintf(stderr, _("\nTry \"%s -h\" for more information.\n"),
@@ -2454,8 +2469,35 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 	 */
 	if (!use_existing)
 	{
-		for (sl = dblist; sl; sl = sl->next)
-			create_database(sl->str);
+		if (templatelist != NULL)
+		{
+			_stringlist *tl;
+
+			/*
+			 * The template list should to have same length as database list.
+			 * Check it before any database creation.
+			 */
+			for (sl = dblist, tl = templatelist; sl; sl = sl->next, tl = tl->next)
+if (tl == NULL)
+{
+	fprintf(stderr, _("%s: the template list is shorter than database list\n"),
+			progname);
+	exit(2);
+}
+			if (tl != NULL)
+			{
+fprintf(stderr, _("%s: the template list is longer than database list\n"),
+		progname);
+exit(2);
+			}
+
+			for (sl = dblist, tl = templatelist; sl; sl = sl->next, tl = tl->next)
+create_database(sl->str, tl->str);
+		}
+		

Re: [HACKERS] possibility to specify template database for pg_regress

[Michael Paquier]

On Thu, Feb 9, 2017 at 5:13 AM, Pavel Stehule  wrote:
> here is a patch

Thanks.

-   for (sl = dblist; sl; sl = sl->next)
-   create_database(sl->str);
+   if (templatelist != NULL)
+   {
+   _stringlist *tl;
+
+   for (sl = dblist, tl = templatelist; sl; sl = sl->next, tl
= tl->next)
+   {
+   if (tl != NULL)
+   create_database(sl->str, tl->str);
+   else
+   {
+   fprintf(stderr, _("%s: the template list is
shorter than database list\n"),
+   progname);
+   exit(2);
+   }
+   }
+   }
+   else
+   for (sl = dblist; sl; sl = sl->next)
+   create_database(sl->str, "template0");
There is one problem here: if the length of the template list is
shorter than the database list, databases get halfly created, then
pg_regress complains, letting the instance in a half-way state. I
think that you had better do any sanity checks before creating or even
dropping existing databases.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

Hi

2017-02-08 8:33 GMT+01:00 Pavel Stehule :

>
>
> 2017-02-08 8:30 GMT+01:00 Michael Paquier :
>
>> On Wed, Feb 8, 2017 at 4:24 PM, Pavel Stehule 
>> wrote:
>> > What is sense for list of databases?
>>
>> ECPG uses it for example, see 0992259.
>>
>> > Some option --template can be great - with backpatch if it is possible.
>>
>> That's not really complicated to patch... That could be a nice task
>> for a starter.
>>
>
> Today I am doing some training - I can look on it at evening
>

here is a patch

Regards

Pavel


>
> Regards
>
> Pavel
>
>
>> --
>> Michael
>>
>
>
diff --git a/src/test/regress/pg_regress.c b/src/test/regress/pg_regress.c
index d4d00d9..354b918 100644
--- a/src/test/regress/pg_regress.c
+++ b/src/test/regress/pg_regress.c
@@ -68,6 +68,7 @@ const char *pretty_diff_opts = "-w -C3";
 
 /* options settable from command line */
 _stringlist *dblist = NULL;
+_stringlist *templatelist = NULL;
 bool		debug = false;
 char	   *inputdir = ".";
 char	   *outputdir = ".";
@@ -1907,7 +1908,7 @@ drop_database_if_exists(const char *dbname)
 }
 
 static void
-create_database(const char *dbname)
+create_database(const char *dbname, const char *template)
 {
 	_stringlist *sl;
 
@@ -1917,10 +1918,12 @@ create_database(const char *dbname)
 	 */
 	header(_("creating database \"%s\""), dbname);
 	if (encoding)
-		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=template0 ENCODING='%s'%s", dbname, encoding,
+		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=\"%s\" ENCODING='%s'%s",
+	 dbname, template, encoding,
 	 (nolocale) ? " LC_COLLATE='C' LC_CTYPE='C'" : "");
 	else
-		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=template0%s", dbname,
+		psql_command("postgres", "CREATE DATABASE \"%s\" TEMPLATE=\"%s\"%s",
+	 dbname, template,
 	 (nolocale) ? " LC_COLLATE='C' LC_CTYPE='C'" : "");
 	psql_command(dbname,
  "ALTER DATABASE \"%s\" SET lc_messages TO 'C';"
@@ -1995,6 +1998,7 @@ help(void)
 	printf(_("  --outputdir=DIR   place output files in DIR (default \".\")\n"));
 	printf(_("  --schedule=FILE   use test ordering schedule from FILE\n"));
 	printf(_("(can be used multiple times to concatenate)\n"));
+	printf(_("  --template=DB use template DB (default \"template0\")\n"));
 	printf(_("  --temp-instance=DIR   create a temporary instance in DIR\n"));
 	printf(_("  --use-existinguse an existing installation\n"));
 	printf(_("\n"));
@@ -2041,6 +2045,7 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 		{"launcher", required_argument, NULL, 21},
 		{"load-extension", required_argument, NULL, 22},
 		{"config-auth", required_argument, NULL, 24},
+		{"template", required_argument, NULL, 25},
 		{NULL, 0, NULL, 0}
 	};
 
@@ -2154,6 +2159,16 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 			case 24:
 config_auth_datadir = pg_strdup(optarg);
 break;
+			case 25:
+
+/*
+ * If a default template was specified, we need to remove it
+ * before we add the specified one.
+ */
+free_stringlist();
+split_to_stringlist(optarg, ",", );
+break;
+
 			default:
 /* getopt_long already emitted a complaint */
 fprintf(stderr, _("\nTry \"%s -h\" for more information.\n"),
@@ -2454,8 +2469,25 @@ regression_main(int argc, char *argv[], init_function ifunc, test_function tfunc
 	 */
 	if (!use_existing)
 	{
-		for (sl = dblist; sl; sl = sl->next)
-			create_database(sl->str);
+		if (templatelist != NULL)
+		{
+			_stringlist *tl;
+
+			for (sl = dblist, tl = templatelist; sl; sl = sl->next, tl = tl->next)
+			{
+if (tl != NULL)
+	create_database(sl->str, tl->str);
+else
+{
+	fprintf(stderr, _("%s: the template list is shorter than database list\n"),
+			progname);
+	exit(2);
+}
+			}
+		}
+		else
+			for (sl = dblist; sl; sl = sl->next)
+create_database(sl->str, "template0");
 		for (sl = extraroles; sl; sl = sl->next)
 			create_role(sl->str, dblist);
 	}

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

2017-02-08 8:30 GMT+01:00 Michael Paquier :

> On Wed, Feb 8, 2017 at 4:24 PM, Pavel Stehule 
> wrote:
> > What is sense for list of databases?
>
> ECPG uses it for example, see 0992259.
>
> > Some option --template can be great - with backpatch if it is possible.
>
> That's not really complicated to patch... That could be a nice task
> for a starter.
>

Today I am doing some training - I can look on it at evening

Regards

Pavel


> --
> Michael
>

Re: [HACKERS] possibility to specify template database for pg_regress

[Michael Paquier]

On Wed, Feb 8, 2017 at 4:24 PM, Pavel Stehule  wrote:
> What is sense for list of databases?

ECPG uses it for example, see 0992259.

> Some option --template can be great - with backpatch if it is possible.

That's not really complicated to patch... That could be a nice task
for a starter.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

2017-02-08 1:30 GMT+01:00 Michael Paquier :

> On Wed, Feb 8, 2017 at 9:23 AM, Tom Lane  wrote:
> > I ran into a use-case just today: I wanted to run one particular
> > regression test script under CLOBBER_CACHE_ALWAYS, but it needed
> > stuff created by earlier scripts, and I didn't especially want to
> > run all of those scripts under CCA.  With a way to select a template,
> > I could've run the earlier scripts in a normal build, renamed the
> > ending-state regression database to something else, and then installed
> > a CCA-enabled executable and run a test with just the script of
> > interest.  The way I actually got it done was considerably hackier :-(
>
> Looking at the code, --dbname can actually accept a list of databases.
> Perhaps we could just have the equivalent for templates? I think that
> we just need to be sure that the template list matches the length of
> the database list if the template list is longer than one.
>

What is sense for list of databases?

Some option --template can be great - with backpatch if it is possible.

Regards

Pavel



> --
> Michael
>

Re: [HACKERS] possibility to specify template database for pg_regress

[Michael Paquier]

On Wed, Feb 8, 2017 at 9:23 AM, Tom Lane  wrote:
> I ran into a use-case just today: I wanted to run one particular
> regression test script under CLOBBER_CACHE_ALWAYS, but it needed
> stuff created by earlier scripts, and I didn't especially want to
> run all of those scripts under CCA.  With a way to select a template,
> I could've run the earlier scripts in a normal build, renamed the
> ending-state regression database to something else, and then installed
> a CCA-enabled executable and run a test with just the script of
> interest.  The way I actually got it done was considerably hackier :-(

Looking at the code, --dbname can actually accept a list of databases.
Perhaps we could just have the equivalent for templates? I think that
we just need to be sure that the template list matches the length of
the database list if the template list is longer than one.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Andres Freund]

On 2017-02-07 19:23:45 -0500, Tom Lane wrote:
> Michael Paquier  writes:
> > On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule  
> > wrote:
> >> Is possible to specify template database for pg_regress?
> >> I have to run tests on database with thousands database objects. Using
> >> template is much faster than import these objects.
> 
> > Not directly, all the databases created by pg_regress are enforced
> > with template0.. Having a switch sounds useful though without seeing
> > in details your use case.
> 
> I ran into a use-case just today: I wanted to run one particular
> regression test script under CLOBBER_CACHE_ALWAYS, but it needed
> stuff created by earlier scripts, and I didn't especially want to
> run all of those scripts under CCA.  With a way to select a template,
> I could've run the earlier scripts in a normal build, renamed the
> ending-state regression database to something else, and then installed
> a CCA-enabled executable and run a test with just the script of
> interest.  The way I actually got it done was considerably hackier :-(

Can't you do that with --use-existing?  I'm pretty sure I used it for
very similar issues before.  And yes, the --help text for that is
misleading.

- Andres


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Tom Lane]

Michael Paquier  writes:
> On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule  
> wrote:
>> Is possible to specify template database for pg_regress?
>> I have to run tests on database with thousands database objects. Using
>> template is much faster than import these objects.

> Not directly, all the databases created by pg_regress are enforced
> with template0.. Having a switch sounds useful though without seeing
> in details your use case.

I ran into a use-case just today: I wanted to run one particular
regression test script under CLOBBER_CACHE_ALWAYS, but it needed
stuff created by earlier scripts, and I didn't especially want to
run all of those scripts under CCA.  With a way to select a template,
I could've run the earlier scripts in a normal build, renamed the
ending-state regression database to something else, and then installed
a CCA-enabled executable and run a test with just the script of
interest.  The way I actually got it done was considerably hackier :-(

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] possibility to specify template database for pg_regress

[Michael Paquier]

On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule  wrote:
> Is possible to specify template database for pg_regress?
> I have to run tests on database with thousands database objects. Using
> template is much faster than import these objects.

Not directly, all the databases created by pg_regress are enforced
with template0.. Having a switch sounds useful though without seeing
in details your use case.
-- 
Michael


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] possibility to specify template database for pg_regress

[Pavel Stehule]

Hi

Is possible to specify template database for pg_regress?

I have to run tests on database with thousands database objects. Using
template is much faster than import these objects.

Regards

Pavel