Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1px3DT-0001SJ-9F for pgsql-bugs@arkaria.postgresql.org; Thu, 11 May 2023 10:08:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1px3DQ-0003FW-AZ for pgsql-bugs@arkaria.postgresql.org; Thu, 11 May 2023 10:08:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1px3DP-0003FM-QB for pgsql-bugs@lists.postgresql.org; Thu, 11 May 2023 10:08:04 +0000 Received: from ma03-relay.lansolnet.com ([176.95.46.36]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1px3DJ-001j3V-Db for pgsql-bugs@lists.postgresql.org; Thu, 11 May 2023 10:08:02 +0000 Received: from unknown ([192.168.8.230]) by ma03-relay.lansolnet.com; Thu, 11 May 2023 12:07:53 +0200 Received: from MBX200E.cloud4partner.com (192.168.8.234) by MBX200A.cloud4partner.com (192.168.8.230) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384_P384) id 15.1.669.32; Thu, 11 May 2023 12:07:41 +0200 Received: from MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634]) by MBX200E.cloud4partner.com ([fe80::881b:2a52:f04f:d634%13]) with mapi id 15.01.0669.032; Thu, 11 May 2023 12:07:41 +0200 From: Hans Buschmann To: Peter Eisentraut , "pgsql-bugs@lists.postgresql.org" Subject: AW: BUG #17923: Excessive warnings of collation version mismatch in logs Thread-Topic: BUG #17923: Excessive warnings of collation version mismatch in logs Thread-Index: AQHZgMys4E+UGAM12kitXbk9IBlyr69S3f6AgAH1e7c= Date: Thu, 11 May 2023 10:07:41 +0000 Message-ID: <5d3fa6cf2dd7489dba0e087e7e6a164e@nidsa.net> References: <17923-c791d260d83aa587@postgresql.org>,<6c53254e-3e03-aa73-6007-bc67397fd507@enterprisedb.com> In-Reply-To: <6c53254e-3e03-aa73-6007-bc67397fd507@enterprisedb.com> Accept-Language: de-DE, en-US Content-Language: de-DE X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [192.168.8.240] Content-Type: multipart/alternative; boundary="_000_5d3fa6cf2dd7489dba0e087e7e6a164enidsanet_" MIME-Version: 1.0 X-cloud-security-sender: buschmann@nidsa.net X-cloud-security-recipient: pgsql-bugs@lists.postgresql.org X-cloud-security-Virusscan: CLEAN X-cloud-security-disclaimer: This E-Mail was scanned by E-Mailservice on ma03-relay.lansolnet.com with DC3BE1984547 X-cloud-security-connect: unknown[192.168.8.230], TLS=1, IP=192.168.8.230 X-cloud-security: scantime:.5649 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_5d3fa6cf2dd7489dba0e087e7e6a164enidsanet_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello Peter, Thank you for looking at. I think, there is a little misunderstanding: The problem is not at connection time (I only mentioned it for info). The problem is the endless repeating of these log entries without user logi= n (cut a little bit at line end): 17:09:08 CEST 01000 1:> WARNING: database "template1" has a collation ve= rsion mismatch 17:09:08 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:09:08 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:09:38 CEST 01000 1:> WARNING: database "postgres" has a collation ver= sion mismatch 17:09:38 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:09:38 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:10:08 CEST 01000 1:> WARNING: database "template1" has a collation ve= rsion mismatch 17:10:08 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:10:08 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:10:38 CEST 01000 1:> WARNING: database "postgres" has a collation ver= sion mismatch 17:10:38 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:10:38 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:11:08 CEST 01000 1:> WARNING: database "template1" has a collation ve= rsion mismatch 17:11:08 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:11:08 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:11:38 CEST 01000 1:> WARNING: database "postgres" has a collation ver= sion mismatch 17:11:38 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:11:38 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:12:08 CEST 01000 1:> WARNING: database "template1" has a collation ve= rsion mismatch 17:12:08 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:12:08 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:12:38 CEST 01000 1:> WARNING: database "postgres" has a collation ver= sion mismatch 17:12:38 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:12:38 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul 17:13:08 CEST 01000 1:> WARNING: database "template1" has a collation ve= rsion mismatch 17:13:08 CEST 01000 2:> DETAIL: The database was created using collation= version 2.36, 17:13:08 CEST 01000 3:> HINT: Rebuild all objects in this database that = use the defaul This is repeated for about 48000 cases!! in a period of roughly 20 days!!!!= ! Let me explain my suggestions (Please remind that I am not familiar with th= e source code, internal data structures or extensive C hackking, so I'll pr= esent it in a more conceptual way): 1. Current situation: The server checks every minute for every database if there is a collation v= ersion mismatch. If it doesn't match, it emits every minute a warning (without user login). This hits performance and floods the logs. 2. My simple suggestion (as by question 1): Check this mismatch only at server startup (the glic cannot be exchanged on= a running server!) and report the warnings only once per database. It should be investigated, by which algorithm the server checks that every = minute and this should be avoided if possible. 3. Further suggestion: When the mismatch occurs at server startup, check if the database has any o= bjects (more selective: if it has any indexes or materialized views) This is a very short one-time operation only at server startup. If there are no such objects in the mentioned database, change the collatio= n version silently to the current version (do the ALTER DATABASE xxxxx REFR= ESH COLLATION VERSION automatically) 4. Elaborated premium solution with focus to easy administration: With the ever growing databases easy recovery of collation problems is not = easy for the user (see other discussions on ICU defaults etc. on hackers). So for every collation a hash value could be computed and stored in the cat= alogs. If there is now a mismatch, the warnings can inform the users of the change= d collations (not every collation is subject to frequent changes!). This circumvents the indeterminable changes of a glibc version change. A query to the catalogs for such a database can then provide the objects wh= ere the altered collations are used and the user can take appropriate actio= n (reindexing a big database is also very expensive!). This certainly is much more work then only stopping the endless warnings bu= t gives a fair amount of self-administration guide to the user. PS: perhaps you may move the discussion to hackers to reach a broader audie= nce. Hans Buschmann ________________________________ Von: Peter Eisentraut Gesendet: Mittwoch, 10. Mai 2023 07:30 An: Hans Buschmann; pgsql-bugs@lists.postgresql.org Betreff: Re: BUG #17923: Excessive warnings of collation version mismatch i= n logs On 07.05.23 12:13, PG Bug reporting form wrote: > 1. Why isn't it sufficient to report this message only at server startup = ? > The messages are sent to psql too, but only when logging into one of thes= e > mostly unused databases, so it is not observed in a regular routine. Each database is separate. The server doesn't look at each database on startup. This is only done when you connect to a given database. > 2. Why is this message reported for databases with no objects or similar > cases? When you connect to a database, the server doesn't scan all objects in the database to see if they might be affected. That would be too slow. If you have no objects in the database, you can just run the provided REFRESH command to make the warning go away. > 3. Is there a real collation change in glibc 2.37 or is it only a version > number mismatch with changes only in other parts of the library? We don't know, because glibc isn't reliable at telling us about it. So we take the safe course. > 4. Could the version information be shown in \l output of psql? That's a valid idea. > 5. Wouldn't it make sense to automatically update the collation version > number of such databases (without any collation-related objects) at serve= r > startup? See #2. We are not going to start scanning each database on each connection to check whether it might be empty. --_000_5d3fa6cf2dd7489dba0e087e7e6a164enidsanet_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Hello Peter,


Thank you for looking at.


I think, there is a little misunderstanding:


The problem is not at connection time (I only mentioned it for info).


The problem is the endless repeating of these log entries without user l= ogin (cut a little bit at line end):


17:09:08 CEST  01000  1:>= ; WARNING:  database "template1" has a collation version mis= match
17:09:08 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:09:08 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:09:38 CEST  01000  1:> WARNING:  database "p= ostgres" has a collation version mismatch
17:09:38 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:09:38 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:10:08 CEST  01000  1:> WARNING:  database "t= emplate1" has a collation version mismatch
17:10:08 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:10:08 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:10:38 CEST  01000  1:> WARNING:  database "p= ostgres" has a collation version mismatch
17:10:38 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:10:38 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:11:08 CEST  01000  1:> WARNING:  database "t= emplate1" has a collation version mismatch
17:11:08 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:11:08 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:11:38 CEST  01000  1:> WARNING:  database "p= ostgres" has a collation version mismatch
17:11:38 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:11:38 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:12:08 CEST  01000  1:> WARNING:  database "t= emplate1" has a collation version mismatch
17:12:08 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:12:08 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:12:38 CEST  01000  1:> WARNING:  database "p= ostgres" has a collation version mismatch
17:12:38 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:12:38 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul
17:13:08 CEST  01000  1:> WARNING:  database "t= emplate1" has a collation version mismatch
17:13:08 CEST  01000  2:> DETAIL:  The database was = created using collation version 2.36,
17:13:08 CEST  01000  3:> HINT:  Rebuild all objects= in this database that use the defaul

This is repeated for about 48000 cases!! in a period of roughly 20 day= s!!!!!


Let me explain my suggestions (Please remind that I am not familiar wi= th the source code, internal data structures or extensive C hackking, so I'= ll present it in a more conceptual way):

1. Current situation:
The server checks every minute for ev= ery database if there is a collation version mismatch.
If it doesn't match, it emits every minute a warning (without user log= in).

This hits performance and floods the logs.

2. My simple suggestion (as by question 1):
Check this mismatch only at server startup (the glic cannot be exchang= ed on a running server!)
and report the warnings only once per database.
It should be investigated, by which algorithm the server checks that e= very minute and this should be avoided if possible.

3. Further suggestion:
When the mismatch occurs at server startup, check if the database has = any objects (more selective: if it has any indexes or materialized views)
This is a very short one-time operation only at server startup.
If there are no such objects in the mentioned database, change the col= lation version silently to the current version (do the ALTER DAT= ABASE xxxxx REFRESH COLLATION VERSION automatically)

4. Elaborated premium solution with focus to easy adminis= tration:
With the ever growing databases easy recovery of collation problems is not = easy for the user (see other discussions on ICU defaults etc. on hackers).<= /div>

So for every collation a hash value could be computed and stored in th= e catalogs.

If there is now a mismatch, the warnings can inform the users of the c= hanged collations (not every collation is subject to frequent changes!).
This circumvents the indeterminable changes of a glibc version change.=

A query to the catalogs for such a database can then provide the objec= ts where the altered collations are used and the user can take appropriate = action (reindexing a big database is also very expensive!).

This certainly is much more work then only stopping the endless warnin= gs but gives a fair amount of self-administration guide to the user.

PS: perhaps you may move the discussion to hackers to reach a broader = audience.

Hans Buschmann






Von: Peter Eisentraut <= ;peter.eisentraut@enterprisedb.com>
Gesendet: Mittwoch, 10. Mai 2023 07:30
An: Hans Buschmann; pgsql-bugs@lists.postgresql.org
Betreff: Re: BUG #17923: Excessive warnings of collation version mis= match in logs
 
On 07.05.23 12:13, PG Bug reporting form wrote: > 1. Why isn't it sufficient to report this message only at server start= up ?
> The messages are sent to psql too, but only when logging into one of t= hese
> mostly unused databases, so it is not observed in a regular routine.
Each database is separate.  The server doesn't look at each database o= n
startup.  This is only done when you connect to a given database.

> 2. Why is this message reported for databases with no objects or simil= ar
> cases?

When you connect to a database, the server doesn't scan all objects in
the database to see if they might be affected.  That would be too slow= .

If you have no objects in the database, you can just run the provided
REFRESH command to make the warning go away.

> 3. Is there a real collation change in glibc 2.37 or is it only a vers= ion
> number mismatch with changes only in other parts of the library?

We don't know, because glibc isn't reliable at telling us about it.  S= o
we take the safe course.

> 4. Could the version information be shown in \l output of psql?

That's a valid idea.

> 5. Wouldn't it make sense to automatically update the collation versio= n
> number of such databases (without any collation-related objects) at se= rver
> startup?

See #2.  We are not going to start scanning each database on each
connection to check whether it might be empty.

--_000_5d3fa6cf2dd7489dba0e087e7e6a164enidsanet_--