possibility to specify template database for pg_regress

On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule <pavel.stehule@gmail.com> 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

Michael Paquier <michael.paquier@gmail.com> writes:

On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule <pavel.stehule@gmail.com> 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

On 2017-02-07 19:23:45 -0500, Tom Lane wrote:

Michael Paquier <michael.paquier@gmail.com> writes:

On Wed, Feb 8, 2017 at 12:43 AM, Pavel Stehule <pavel.stehule@gmail.com> 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

On Wed, Feb 8, 2017 at 9:23 AM, Tom Lane <tgl@sss.pgh.pa.us> 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

2017-02-08 1:30 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Wed, Feb 8, 2017 at 9:23 AM, Tom Lane <tgl@sss.pgh.pa.us> 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

On Wed, Feb 8, 2017 at 4:24 PM, Pavel Stehule <pavel.stehule@gmail.com> 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

2017-02-08 8:30 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Wed, Feb 8, 2017 at 4:24 PM, Pavel Stehule <pavel.stehule@gmail.com>
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

Hi

2017-02-08 8:33 GMT+01:00 Pavel Stehule <pavel.stehule@gmail.com>:

2017-02-08 8:30 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Wed, Feb 8, 2017 at 4:24 PM, Pavel Stehule <pavel.stehule@gmail.com>
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

On Thu, Feb 9, 2017 at 5:13 AM, Pavel Stehule <pavel.stehule@gmail.com> 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

Hi

2017-02-10 6:00 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Thu, Feb 9, 2017 at 5:13 AM, Pavel Stehule <pavel.stehule@gmail.com>
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

On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <pavel.stehule@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".
--
Michael

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

Hi

2017-02-13 6:46 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <pavel.stehule@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

Regards

Pavel

On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:

Hi

2017-02-13 6:46 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <pavel.stehule@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
/messages/by-id/20170208002900.vkldujzfkwbvqqq7@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

On Tue, Feb 14, 2017 at 11:36 AM, Andres Freund <andres@anarazel.de> wrote:

On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:

Hi

2017-02-13 6:46 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <pavel.stehule@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

This looks fine to me.

I still fail to see why --use-existing as suggested in
/messages/by-id/20170208002900.vkldujzfkwbvqqq7@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

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

I still fail to see why --use-existing as suggested in
/messages/by-id/20170208002900.vkldujzfkwbvqqq7@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

2017-02-14 3:50 GMT+01:00 Andres Freund <andres@anarazel.de>:

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

I still fail to see why --use-existing as suggested in
/messages/by-id/20170208002900.

vkldujzfkwbvqqq7@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

2017-02-14 3:36 GMT+01:00 Andres Freund <andres@anarazel.de>:

On 2017-02-13 20:59:43 +0100, Pavel Stehule wrote:

Hi

2017-02-13 6:46 GMT+01:00 Michael Paquier <michael.paquier@gmail.com>:

On Sat, Feb 11, 2017 at 3:03 PM, Pavel Stehule <

pavel.stehule@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
/messages/by-id/20170208002900.
vkldujzfkwbvqqq7@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

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
/messages/by-id/20170208002900.vkldujzfkwbvqqq7@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

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
/messages/by-id/20170208002900.vkldujzfkwbvqqq7@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