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 1ouL16-00011o-JL for pgsql-general@arkaria.postgresql.org; Sun, 13 Nov 2022 21:59:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1ouL06-0008Fs-3o for pgsql-general@arkaria.postgresql.org; Sun, 13 Nov 2022 21:58:50 +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 1ouL05-0008Fh-P8 for pgsql-general@lists.postgresql.org; Sun, 13 Nov 2022 21:58:49 +0000 Received: from mout.gmx.net ([212.227.15.15]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1ouKzz-0004dg-6U for pgsql-general@lists.postgresql.org; Sun, 13 Nov 2022 21:58:48 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=gmx.net; s=s31663417; t=1668376719; bh=PWV6ACInBCXUXgAjqjQmRXrinB1bIso6ZOLXD5zKwXM=; h=X-UI-Sender-Class:Date:From:To:Cc:Subject:References:In-Reply-To; b=IWiXMemZnejCqfE0GZdyLDbBPVvFNliyg3nnS17v6crvQeMs7ed3iOBdIiQElmht0 722F6motqAW2XOdScpF1L3i+tPZcXpXitD1vQJ18lcdqxvOob2G3Xx/TNF9NikrjaE ZpkcLbCITWtYkkIe2m1Agnjuydk1lmUwGeO45XWmBB+8A284QfC9Lb+Xl/QZYdMlvE oB1RI78f24CwKoQZIGc0afbPjgTQgUHISUMPnLxE942HiHDz/SbFhbVwarI4L5kJVP t+yzJ6MMjiEVOOteRuXDDnsRi7eoLSfO8I1IPoBptLHV41cfWCsrGT53eYmK3JTgO8 qM2VxsHYulZew== X-UI-Sender-Class: 724b4f7f-cbec-4199-ad4e-598c01a50d3a Received: from hermes ([91.9.16.86]) by mail.gmx.net (mrgmx004 [212.227.17.190]) with ESMTPSA (Nemesis) id 1MirjS-1pQOUs3U0q-00eyHY; Sun, 13 Nov 2022 22:58:39 +0100 Received: from ncq by hermes with local (Exim 4.94.2) (envelope-from ) id 1ouKzt-000652-En; Sun, 13 Nov 2022 22:58:37 +0100 Date: Sun, 13 Nov 2022 22:58:37 +0100 From: Karsten Hilbert To: Christophe Pettus Cc: pgsql-general@lists.postgresql.org Subject: Re: Q: fixing collation version mismatches Message-ID: References: MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: Ma_X_il-Followup-to: d Re_X_turn-receipt-to: Karsten.Hilbert@gmx.net Di_X_sposition-Notification-To: Karsten.Hilbert@gmx.net X-Confi_X_rm-Reading-To: Karsten.Hilbert@gmx.net X-Pri_X_ority: 2 (High) Sender: X-Provags-ID: V03:K1:pUko3wQIK5Yq9wklFWUDwNBQVwJqGsyNgv783GrtPmcTebrzy2R 00mAjpwdp/PdVDyqJkIUaM+FZK+K2Qa2HuorH2VPcMxYtzQ8tCMZv1QU7TG91J8llz0qCBk fN1xOLW4OAhdz/vJ4+AMiwNr73hWmaQq7Yh8jdFvZMt6QTrFn83nsgKrhgL3S09jjRuYjwg R7U0rZQMU42znw70Pv8oQ== X-Spam-Flag: NO UI-OutboundReport: notjunk:1;M01:P0:SR98EjuV8Eg=;yiwpbGPBPv4QMTCmlzZXzssokfb YlDdx7H9yBnyzLiMLRE+PIZGQ+L+B7txFtjSUDy9H2cCeKsV1lyMObJ9xW6l3RNOBl1woMHhA NKShZgUBtYYUxmjZ2nz046bdAXdiSPnBogbTw3oUSu2eCgiNP9OV1z93xGQTe6VlDeomb4Rr+ S7T6SWiB+VtxtKfsa0mLWnuI/vp0Adr7z4z9CVOY6KYQBodFHZoerYBEikMGEHRgzWZv6l8wt fI+ePhWnQA4u+mRT2EhdYtcV9wLJgWeo2RKnxycR7fs2gqFpjfjVq5uqYVK6OLpDOOBMFD3BE 9wlpN9i9P7MT9KuXsvOgrwFk11HK/CIPycNSDCPYogHpMO8rOgwBjjHd2g65IWeZoEr2khfIC 7NBQo2zpaMh78tVhdtFm5+7QIU23zB/jk70UPm7pTnYgReKaJT4TxxKiKLzDWUHD4RAxXSqUN qFxuMVnM6+goh086sqFkuuXXjcZXX9Bu9Y7/ZIMyg9A0wm0VMVoErIDotyCNyHoJf3SE7lRGt 4oq27oYGmBQOHLOBuK5CbgxkkVm96IF8lWHV6xlgG+ealYHFsJ9xKOm3l8mvKb+afPRfF94aW IfNI4mFP8iqqPYmDkb75CNnOI1kJkrC3o25HrikyAy1spUUzvPs90/lh4/Ln74HisGYcbUVI/ Ay4nlRE4ZkZlJNkoPvalyGe7qIJ7bBDhL8++3RM35hQ5g/fYxrJwebP/XoCK8hjrPSO2kvVhB KkHBq5PBPErNlB5lNIe9QC8Q+K6ob8jOMLDftSn1SHMu1rJtus1XPuZI+OQ8XXp1aFABFUd/z L5Fz2T4jMmA8R7RCzP1yC6cKNLBwdEiu71fje8lqos3RPDowSZ2e8JCF6eZ19LfnihIJfQDu5 vO5ovQ9G23mcknhdNuEbqiR5mp9XUkJ83xpayzXtD6L0dHWzDKCjGZXF7H0BeEF6SyEYm67Nl SQeEtQ== Content-Transfer-Encoding: quoted-printable List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk Am Sun, Nov 13, 2022 at 12:46:53PM -0800 schrieb Christophe Pettus: > > On Nov 13, 2022, at 12:45, Karsten Hilbert w= rote: > > REINDEX DATABASE db_in_question; > > ALTER DATABASE db_in_question REFRESH COLLATION VERSION; > > ALTER COLLATION every_collation_from_pg_collation REFRESH VERSION; > > I may be totally off-base here, but shouldn't the REINDEX be the last st= ep? To my understanding, the REFRESH statements "merely" update the version information stored in the related objects. They do not change anything else; and the REINDEX does not reference them in any way. I suppose the REINDEX goes first as it does the actual fixing of now-invalid objects by rebuilding them. After that one is back to a usable database state, even if left with pesky (albeit harmless) warnings on version mismatches -- which to get rid of one runs the REFRESH statements. Or so my understanding... Which is why my question still stands: does the above three-strikes operation safely take care of any collation issues that may currently exist in a database ? Karsten =2D- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B