Обсуждение: BUG #16026: default_tablespace in postgresql.conf is used instead of the database's default.

The following bug has been logged on the website:

Bug reference:      16026
Logged by:          virgile crevon
Email address:      virgile.crevon@dassault-aviation.com
PostgreSQL version: 10.5
Operating system:   redhat 7 64 bits
Description:

Hi,

To reproduce :

1- create a tablespace data  (whereever you want it to be). Note that data
is lowercase without quotation marks.
2- set default_tablespace='DATA' in postgresql.conf. Note this is un
UPPERCASE.
3- reload conf.
4- create a database with default tablespace data (lowercase).
5- create a query that is using parallel (gather step) to seq scan.

Since the worker work with default tablespace at postgresql.conf level, he
will break with the following message :

2019-09-25 16:42:50.444 GMT [71530] ERROR:  invalid value for parameter
"default_tablespace": "DATA"
2019-09-25 16:42:50.444 GMT [71530] DETAIL:  Tablespace "DATA" does not
exist.
::  ::  ::  ::  2019-09-25 18:42:50.445 CEST ::
 LOG:  worker process: parallel worker for PID 62413 (PID 71531) exited with
exit code 1
 ::  ::  ::  ::  2019-09-25 18:42:50.445 CEST ::
 LOG:  worker process: parallel worker for PID 62413 (PID 71530) exited with
exit code 1

(2 workers for this query, will end up with more error if parallel mode is
configured differently on your system).

Perhaps it's a documentation issue (default_tablespace is not mentionned to
be used by parallel_query).
But i think it is more a bug : it would definitively have to get the default
tablespace from database where the query is run.

Regards,

Virgile CREVON

PG Bug reporting form <noreply@postgresql.org> writes:
> 1- create a tablespace data  (whereever you want it to be). Note that data
> is lowercase without quotation marks.
> 2- set default_tablespace='DATA' in postgresql.conf. Note this is un
> UPPERCASE.
> 3- reload conf.
> 4- create a database with default tablespace data (lowercase).
> 5- create a query that is using parallel (gather step) to seq scan.

This is the same issue discussed in

https://www.postgresql.org/message-id/flat/15726-6d67e4fa14f027b3%40postgresql.org

I'm not sure why progress on that thread has come to a dead halt,
but in any case, the general opinion seems to be that all we're
going to do about it is improve the error reporting.  Wrong values
in postgresql.conf aren't a well-supported or encouraged situation,
even if it seems to mostly work for default_tablespace.

BTW, it occurred to me to wonder if we could make the situation
better by running RestoreGUCState() *outside* a transaction,
but that just moves the problems around:

+ERROR:  invalid value for parameter "session_authorization": "postgres"
+CONTEXT:  parallel worker

            regards, tom lane

On Fri, Sep 27, 2019 at 3:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> PG Bug reporting form <noreply@postgresql.org> writes:
> > 1- create a tablespace data  (whereever you want it to be). Note that data
> > is lowercase without quotation marks.
> > 2- set default_tablespace='DATA' in postgresql.conf. Note this is un
> > UPPERCASE.
> > 3- reload conf.
> > 4- create a database with default tablespace data (lowercase).
> > 5- create a query that is using parallel (gather step) to seq scan.
>
> This is the same issue discussed in
>
> https://www.postgresql.org/message-id/flat/15726-6d67e4fa14f027b3%40postgresql.org
>
> I'm not sure why progress on that thread has come to a dead halt,
> but in any case, the general opinion seems to be that all we're
> going to do about it is improve the error reporting.  Wrong values
> in postgresql.conf aren't a well-supported or encouraged situation,
> even if it seems to mostly work for default_tablespace.

Erm, yeah.  I will fix that early next week.

-- 
Thomas Munro
https://enterprisedb.com

On Fri, Sep 27, 2019 at 1:36 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> On Fri, Sep 27, 2019 at 3:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > PG Bug reporting form <noreply@postgresql.org> writes:
> > > 1- create a tablespace data  (whereever you want it to be). Note that data
> > > is lowercase without quotation marks.
> > > 2- set default_tablespace='DATA' in postgresql.conf. Note this is un
> > > UPPERCASE.
> > > 3- reload conf.
> > > 4- create a database with default tablespace data (lowercase).
> > > 5- create a query that is using parallel (gather step) to seq scan.
> >
> > This is the same issue discussed in
> >
> > https://www.postgresql.org/message-id/flat/15726-6d67e4fa14f027b3%40postgresql.org
> >
> > I'm not sure why progress on that thread has come to a dead halt,
> > but in any case, the general opinion seems to be that all we're
> > going to do about it is improve the error reporting.  Wrong values
> > in postgresql.conf aren't a well-supported or encouraged situation,
> > even if it seems to mostly work for default_tablespace.
>
> Erm, yeah.  I will fix that early next week.

That has now been done, but it doesn't help at all in this case.  It
was already pretty clear from the ERROR message here that the problem
was default_tablespace being set to an invalid value (which is ignored
in regular backend startup), so the new CONTEXT message just repeats
that information.

I wondered why we have to restore GUCs inside a transaction, anyway,
creating this difference in behaviour in parallel queries.  We have to
do that after restoring libraries, in case they defined new GUCs, and
in commit 6c3c9d41 we decided that we should restore libraries in a
transaction to avoid breaking a bunch of extensions (though GUCs were
already restored inside a transaction before that commit).

Hello Tom and Thomas,

First of all, i really appreciate that you take my issue into consideration. It is well appreciated.

Second, I just want to clarify the point of my issue :

- the invalid "default_tablespace" part was just for illustrating the issue : I TOTALLY agree that one MUST NOT fill in
invalidvalues (such as invalid "tablespace name" for the default).
 
- the real point of my issue is that parallel query engine is not picking the default tablespace of the database
itself.I mean many databases, perhaps many different default tablespaces. But parallel query will pick up the "global
defauttablespace". I think this can lead to unpredictable results. Really.
 

It is much a conceptual bug : what's the point of parallel query to execute something outside of the scope it was
designedfor ? I don't want a query to run OUTSIDE the limits of my database (and it's dedicated tablespace,
eventually).

Again, thank's for your support.

Virgile 

NB : By the way, it is really hard to monitor the issue online because the web representation lacks of visibility.
 



-----Message d'origine-----
De : Thomas Munro [mailto:thomas.munro@gmail.com] 
Envoyé : jeudi 17 octobre 2019 03:31
À : Tom Lane
Cc : Crevon Virgile; PostgreSQL mailing lists
Objet : Re: BUG #16026: default_tablespace in postgresql.conf is used instead of the database's default.

On Fri, Sep 27, 2019 at 1:36 PM Thomas Munro <thomas.munro@gmail.com> wrote:
> On Fri, Sep 27, 2019 at 3:01 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > PG Bug reporting form <noreply@postgresql.org> writes:
> > > 1- create a tablespace data  (whereever you want it to be). Note that data
> > > is lowercase without quotation marks.
> > > 2- set default_tablespace='DATA' in postgresql.conf. Note this is un
> > > UPPERCASE.
> > > 3- reload conf.
> > > 4- create a database with default tablespace data (lowercase).
> > > 5- create a query that is using parallel (gather step) to seq scan.
> >
> > This is the same issue discussed in
> >
> > https://www.postgresql.org/message-id/flat/15726-6d67e4fa14f027b3%40postgresql.org
> >
> > I'm not sure why progress on that thread has come to a dead halt,
> > but in any case, the general opinion seems to be that all we're
> > going to do about it is improve the error reporting.  Wrong values
> > in postgresql.conf aren't a well-supported or encouraged situation,
> > even if it seems to mostly work for default_tablespace.
>
> Erm, yeah.  I will fix that early next week.

That has now been done, but it doesn't help at all in this case.  It
was already pretty clear from the ERROR message here that the problem
was default_tablespace being set to an invalid value (which is ignored
in regular backend startup), so the new CONTEXT message just repeats
that information.

I wondered why we have to restore GUCs inside a transaction, anyway,
creating this difference in behaviour in parallel queries.  We have to
do that after restoring libraries, in case they defined new GUCs, and
in commit 6c3c9d41 we decided that we should restore libraries in a
transaction to avoid breaking a bunch of extensions (though GUCs were
already restored inside a transaction before that commit).

On 2019-Oct-17, Crevon Virgile wrote:

> NB : By the way, it is really hard to monitor the issue online because
> the web representation lacks of visibility.

That's a fair complaint.  We have an online mailing list archive, and
your message/thread can be seen here:
https://www.postgresql.org/message-id/flat/16026-7938df12bae60c5e@postgresql.org

I think at bug creation time maybe we could display such links, since
(AFAIK) we do have the Message-Id at that point.  Any takers?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2019-Oct-17, Crevon Virgile wrote:

> NB : By the way, it is really hard to monitor the issue online because
> the web representation lacks of visibility.

That's a fair complaint.  We have an online mailing list archive, and
your message/thread can be seen here:
https://www.postgresql.org/message-id/flat/16026-7938df12bae60c5e@postgresql.org

I think at bug creation time maybe we could display such links, since
(AFAIK) we do have the Message-Id at that point.  Any takers?

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services