Horton

Thread

Commits

  1. Fix search_path cache initialization.

  2. Use generateClonedIndexStmt to propagate CREATE INDEX to partitions.

  1. Error when setting default_text_search_config

    Murat Efendioğlu <mrtdmrts001@gmail.com> — 2024-10-05T17:46:02Z

    *Hello everyone,*

I've encountered some strange behavior in the new PostgreSQL 17 version.
Previously, in versions before 17, I was able to set the
default_text_search_config like this:

sql
Copy code
ALTER DATABASE "MyDatabase" SET default_text_search_config TO 'german';

Everything worked fine. However, after upgrading to PostgreSQL 17, when I
ran the same ALTER statement on the new database, my database entered
recovery mode.

The solution was to update the statement as follows:

sql
Copy code
ALTER DATABASE "MyDatabase" SET default_text_search_config TO
'pg_catalog.german';

It turns out I had to prefix the language with pg_catalog to resolve the
issue.

Environment: Windows Server 2022 Standard 21H2 with PostgreSQL 17 installer

Best regards m.d.

  2. Re: Error when setting default_text_search_config

    Erik Wienhold <ewie@ewie.name> — 2024-10-06T14:49:49Z

    On 2024-10-05 19:46 +0200, Murat Efendioğlu wrote:
> I've encountered some strange behavior in the new PostgreSQL 17 version.
> Previously, in versions before 17, I was able to set the
> default_text_search_config like this:
> 
> sql
> Copy code
> ALTER DATABASE "MyDatabase" SET default_text_search_config TO 'german';
> 
> Everything worked fine. However, after upgrading to PostgreSQL 17, when I
> ran the same ALTER statement on the new database, my database entered
> recovery mode.

Is it entering recovery mode just by running that ALTER DATABASE?  Are
you already connected to MyDatabase?

> The solution was to update the statement as follows:
> 
> sql
> Copy code
> ALTER DATABASE "MyDatabase" SET default_text_search_config TO
> 'pg_catalog.german';
> 
> It turns out I had to prefix the language with pg_catalog to resolve the
> issue.
> 
> Environment: Windows Server 2022 Standard 21H2 with PostgreSQL 17 installer

I can reproduce a segfault on my Arch machine with 17.0, but only by
also connecting to that database afterwards:

    postgres=# create database regress;
    postgres=# alter database regress set default_text_search_config to 'german';
    postgres=# \c regress
    connection to server at "localhost" (::1), port 15432 failed: server closed the connection unexpectedly
            This probably means the server terminated abnormally
            before or while processing the request.

The core dump shows this backtrace:

    #0  0x0000619056403314 in MemoryContextReset ()
    #1  0x0000619056016faa in spcache_init ()
    #2  0x0000619056017706 in recomputeNamespacePath ()
    #3  0x000061905601a705 in get_ts_config_oid ()
    #4  0x00006190563d5f08 in check_default_text_search_config ()
    #5  0x00006190563ee3c0 in call_string_check_hook ()
    #6  0x00006190563f107b in parse_and_validate_value ()
    #7  0x00006190563f1faa in set_config_with_handle ()
    #8  0x00006190563f6545 in ProcessGUCArray ()
    #9  0x0000619056027e97 in ApplySetting ()
    #10 0x00006190563ea256 in InitPostgres ()
    #11 0x000061905629eb66 in PostgresMain ()
    #12 0x000061905629ac9f in BackendMain ()
    #13 0x000061905620af9b in postmaster_child_launch ()
    #14 0x000061905620ea69 in ServerLoop.isra.0 ()
    #15 0x000061905621077d in PostmasterMain ()
    #16 0x0000619055f4ba03 in main ()

-- 
Erik

  3. Re: Error when setting default_text_search_config

    Erik Wienhold <ewie@ewie.name> — 2024-10-06T18:08:35Z

    I wrote:
> I can reproduce a segfault on my Arch machine with 17.0, but only by
> also connecting to that database afterwards:
> 
>     postgres=# create database regress;
>     postgres=# alter database regress set default_text_search_config to 'german';
>     postgres=# \c regress
>     connection to server at "localhost" (::1), port 15432 failed: server closed the connection unexpectedly
>             This probably means the server terminated abnormally
>             before or while processing the request.

Here's better backtrace based on commit fee8cb9473 (not sure why the
first one lacked the additional info):

    #0  0x0000654521e03394 in MemoryContextReset (context=0x0) at mcxt.c:388
    #1  0x0000654521a165ea in spcache_init () at namespace.c:318
    #2  0x0000654521a16d46 in cachedNamespacePath (searchPath=0x654541b0f3f0 "\"$user\", public", roleid=10) at namespace.c:4234
    #3  recomputeNamespacePath () at namespace.c:4294
    #4  0x0000654521a19d45 in get_ts_config_oid (names=0x654541b86860, missing_ok=missing_ok@entry=true) at namespace.c:3162
    #5  0x0000654521dd5f88 in check_default_text_search_config (newval=0x7ffcb598c928, extra=<optimized out>, source=PGC_S_DATABASE) at ts_cache.c:621
    #6  0x0000654521dee440 in call_string_check_hook (conf=conf@entry=0x65452215e830 <ConfigureNamesString+11600>, newval=newval@entry=0x7ffcb598c928, extra=extra@entry=0x7ffcb598c930, source=source@entry=PGC_S_DATABASE, elevel=elevel@entry=19) at guc.c:6890
    #7  0x0000654521df10fb in parse_and_validate_value (record=0x65452215e830 <ConfigureNamesString+11600>, name=0x654541b86468 "default_text_search_config", value=<optimized out>, source=PGC_S_DATABASE, elevel=19, newval=0x7ffcb598c928, newextra=0x7ffcb598c930) at guc.c:3260
    #8  0x0000654521df202a in set_config_with_handle (name=name@entry=0x654541b86468 "default_text_search_config", handle=handle@entry=0x0, value=value@entry=0x654541b867a0 "german", context=context@entry=PGC_SUSET, source=source@entry=PGC_S_DATABASE, srole=10, action=GUC_ACTION_SET, changeVal=<optimized out>, elevel=19, is_reload=false) at guc.c:4010
    #9  0x0000654521df65c5 in set_config_option (name=0x654541b86468 "default_text_search_config", value=0x654541b867a0 "german", context=<optimized out>, source=<optimized out>, action=<optimized out>, changeVal=true, elevel=0, is_reload=false) at guc.c:3363
    #10 ProcessGUCArray (array=<optimized out>, context=PGC_SUSET, source=PGC_S_DATABASE, action=GUC_ACTION_SET) at guc.c:6438
    #11 0x0000654521a274d7 in ApplySetting (snapshot=snapshot@entry=0x654541b855e8, databaseid=databaseid@entry=40965, roleid=roleid@entry=0, relsetting=relsetting@entry=0x7be2b87866e8, source=source@entry=PGC_S_DATABASE) at pg_db_role_setting.c:256
    #12 0x0000654521dea2d6 in process_settings (databaseid=40965, roleid=10) at postinit.c:1342
    #13 InitPostgres (in_dbname=in_dbname@entry=0x654541aeff60 "regress", dboid=<optimized out>, dboid@entry=0, username=username@entry=0x654541b2db98 "ewie", useroid=useroid@entry=0, flags=1, out_dbname=out_dbname@entry=0x0) at postinit.c:1217
    #14 0x0000654521c9e8a6 in PostgresMain (dbname=0x654541aeff60 "regress", username=0x654541b2db98 "ewie") at postgres.c:4324
    #15 0x0000654521c9a9df in BackendMain (startup_data=<optimized out>, startup_data_len=<optimized out>) at backend_startup.c:105
    #16 0x0000654521c0acdb in postmaster_child_launch (child_type=child_type@entry=B_BACKEND, startup_data=startup_data@entry=0x7ffcb598cfe0 "", startup_data_len=startup_data_len@entry=4, client_sock=client_sock@entry=0x7ffcb598d000) at launch_backend.c:277
    #17 0x0000654521c0e7a9 in BackendStartup (client_sock=0x7ffcb598d000) at postmaster.c:3593
    #18 ServerLoop () at postmaster.c:1674
    #19 0x0000654521c104bd in PostmasterMain (argc=argc@entry=4, argv=argv@entry=0x654541aedea0) at postmaster.c:1372
    #20 0x000065452194ab33 in main (argc=4, argv=0x654541aedea0) at main.c:197

InitPostgres calls process_settings before InitializeSearchPath, and so
SearchPathCacheContext is null.  Not sure how to fix this because
InitPostgres clearly states this before calling InitializeSearchPath:

    /*
     * Initialize various default states that can't be set up until we've
     * selected the active user and gotten the right GUC settings.
     */

Maybe just don't use the cache in that case?  For example,
check_search_path uses the cache only if SearchPathCacheContext is
initialized.

-- 
Erik

  4. Re: Error when setting default_text_search_config

    Erik Wienhold <ewie@ewie.name> — 2024-10-06T18:25:12Z

    Please reply to the list also.
CC'ing list.

On 2024-10-06 19:55 +0200, Murat Efendioğlu wrote:
> yes thats what I am talking abt. When I set default_text_search_config  I
> cannot connect to the database nor can I open the database treeview node in
> pgAdmin. It goes in recovery or the database connection is lost.
> 
> Am So., 6. Okt. 2024 um 16:49 Uhr schrieb Erik Wienhold <ewie@ewie.name>:
> > Is it entering recovery mode just by running that ALTER DATABASE?  Are
> > you already connected to MyDatabase?

Thanks.

-- 
Erik

  5. Re: Error when setting default_text_search_config

    Tom Lane <tgl@sss.pgh.pa.us> — 2024-10-06T18:39:50Z

    Erik Wienhold <ewie@ewie.name> writes:
>> I can reproduce a segfault on my Arch machine with 17.0, but only by
>> also connecting to that database afterwards:
>> 
>> postgres=# create database regress;
>> postgres=# alter database regress set default_text_search_config to 'german';
>> postgres=# \c regress
>> connection to server at "localhost" (::1), port 15432 failed: server closed the connection unexpectedly
>> This probably means the server terminated abnormally
>> before or while processing the request.

Yeah, I see that too (with any value of default_text_search_config).
The problem is that InitPostgres does

	/* Process pg_db_role_setting options */
	process_settings(MyDatabaseId, GetSessionUserId());

before it calls InitializeSearchPath(), and recomputeNamespacePath
isn't ready for that.  Evidently broken in f26c2368d.

I suppose we could just move the InitializeSearchPath call up
a bit (the comments in postinit.c are misleading about what it
actually does), but I wonder whether the cache-is-valid assumptions
in namespace.c ought to be rethought.  It seems unduly fragile
as-is.

			regards, tom lane

  6. Re: Error when setting default_text_search_config

    Murat Efendioğlu <mrtdmrts001@gmail.com> — 2024-10-06T20:22:34Z

    Hello,

I checked it again, when I set

*ALTER DATABASE "MyDatabase" SET default_text_search_config TO 'german';*

and I try to connect through psql, I get following:

*>psql postgresql://user1:admin@192.168.153.26:5432/MyDatabase
<http://user1:admin@192.168.153.26:5432/MyDatabase>*


*psql: Fehler: Verbindung zum Server auf »192.168.153.26«, Port 5432
fehlgeschlagen: Server beendete die Verbindung unerwartet        Das heißt
wahrscheinlich, dass der Server abnormal beendete        bevor oder während
die Anweisung bearbeitet wurde.*
In english it would be similar like following:

*>psql postgresql://user1:admin@192.168.153.26:5432/MyDatabase *
*psql: Error: Connection to server at "192.168.153.26", port 5432 failed:
The server unexpectedly closed the connection This likely means that the
server terminated abnormally before or while processing the request.  *

In *pgAdmin *I am loosing the connection to the database and when I click
on the server node in the treeview I am loosing the complete connection to
the server.

Strangely my alter statement has to be like following to prevent the
explained error:


*ALTER DATABASE "MyDatabase" SET default_text_search_config TO
'pg_catalog.german';*

Prefixed with the "*pg_catalog.*" literal.

Well, thats my 2 cents to this topic. I had no issues till version 17 :-)

So my question is: Whats the error? The missing prefix before version 17,
but the PostgreSQL was tolerant OR the new version bcz something changed to
be more stricttly????

best regards murat

Am So., 6. Okt. 2024 um 20:39 Uhr schrieb Tom Lane <tgl@sss.pgh.pa.us>:

> Erik Wienhold <ewie@ewie.name> writes:
> >> I can reproduce a segfault on my Arch machine with 17.0, but only by
> >> also connecting to that database afterwards:
> >>
> >> postgres=# create database regress;
> >> postgres=# alter database regress set default_text_search_config to
> 'german';
> >> postgres=# \c regress
> >> connection to server at "localhost" (::1), port 15432 failed: server
> closed the connection unexpectedly
> >> This probably means the server terminated abnormally
> >> before or while processing the request.
>
> Yeah, I see that too (with any value of default_text_search_config).
> The problem is that InitPostgres does
>
>         /* Process pg_db_role_setting options */
>         process_settings(MyDatabaseId, GetSessionUserId());
>
> before it calls InitializeSearchPath(), and recomputeNamespacePath
> isn't ready for that.  Evidently broken in f26c2368d.
>
> I suppose we could just move the InitializeSearchPath call up
> a bit (the comments in postinit.c are misleading about what it
> actually does), but I wonder whether the cache-is-valid assumptions
> in namespace.c ought to be rethought.  It seems unduly fragile
> as-is.
>
>                         regards, tom lane
>

  7. Re: Error when setting default_text_search_config

    Jeff Davis <pgsql@j-davis.com> — 2024-10-06T21:51:17Z

    On Sun, 2024-10-06 at 14:39 -0400, Tom Lane wrote:
> I suppose we could just move the InitializeSearchPath call up
> a bit (the comments in postinit.c are misleading about what it
> actually does), but I wonder whether the cache-is-valid assumptions
> in namespace.c ought to be rethought.  It seems unduly fragile
> as-is.

I attached a patch which fixes the observed problem and is hopefully
more robust to similar issues.

The test for use_cache in check_search_path() is also somewhat
indirect. The goal there is to avoid calling GetUserId() before
CurrentUserId is valid, but that could probably be solved other ways or
commented better. I will look into it.

Regards,
	Jeff Davis

  8. Re: Error when setting default_text_search_config

    David G. Johnston <david.g.johnston@gmail.com> — 2024-10-07T01:49:32Z

    On Sunday, October 6, 2024, Murat Efendioğlu <mrtdmrts001@gmail.com> wrote:

>
>
> Strangely my alter statement has to be like following to prevent the
> explained error:
>
>
> *ALTER DATABASE "MyDatabase" SET default_text_search_config TO
> 'pg_catalog.german';*
>
> Prefixed with the "*pg_catalog.*" literal.
>

Then either do that and continue to use v17.0, wait until this is fixed in
v17.1, or wait for the patch and deploy a custom build of v17.0 with the
patch applied.

David J.

  9. Re: Error when setting default_text_search_config

    Erik Wienhold <ewie@ewie.name> — 2024-10-08T01:03:00Z

    On 2024-10-06 23:51 +0200, Jeff Davis wrote:
> On Sun, 2024-10-06 at 14:39 -0400, Tom Lane wrote:
> > I suppose we could just move the InitializeSearchPath call up
> > a bit (the comments in postinit.c are misleading about what it
> > actually does), but I wonder whether the cache-is-valid assumptions
> > in namespace.c ought to be rethought.  It seems unduly fragile
> > as-is.
> 
> I attached a patch which fixes the observed problem and is hopefully
> more robust to similar issues.

WFM.  The regression tests pass and the segfault no longer occurs.

-- 
Erik

  10. Re: Error when setting default_text_search_config

    Jeff Davis <pgsql@j-davis.com> — 2024-10-08T01:06:02Z

    On Tue, 2024-10-08 at 03:03 +0200, Erik Wienhold wrote:
> WFM.  The regression tests pass and the segfault no longer occurs.

Committed and backported to 17. Thank you all for the report and
analysis!

Regards,
	Jeff Davis