Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDPbh-001wXU-HE for pgsql-admin@arkaria.postgresql.org; Wed, 13 Dec 2023 13:49:01 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rDPbf-00EcyK-Q3 for pgsql-admin@arkaria.postgresql.org; Wed, 13 Dec 2023 13:48:59 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDPbf-00Ecxl-EN for pgsql-admin@lists.postgresql.org; Wed, 13 Dec 2023 13:48:59 +0000 Received: from jakobs.com ([85.214.83.89] helo=rs.plausibolo.de) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rDPbc-00BnZH-WD for pgsql-admin@lists.postgresql.org; Wed, 13 Dec 2023 13:48:58 +0000 Received: from localhost (localhost [127.0.0.1]) by rs.plausibolo.de (Postfix) with ESMTP id 8580F380396; Wed, 13 Dec 2023 14:48:54 +0100 (CET) Received: from rs.plausibolo.de ([IPv6:::1]) by localhost (h2367442.stratoserver.net [IPv6:::1]) (amavisd-new, port 10024) with ESMTP id V1wWYRUneEfZ; Wed, 13 Dec 2023 14:48:54 +0100 (CET) Received: from [192.168.222.58] (unknown [145.253.118.26]) by rs.plausibolo.de (Postfix) with ESMTPSA id EC0F6380152; Wed, 13 Dec 2023 14:48:53 +0100 (CET) Message-ID: <5c6950cb-5ce2-2740-a447-5d9ff8418edd@jakobs.com> Date: Wed, 13 Dec 2023 14:48:53 +0100 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.12.0 Subject: Re: Reindex concurrently Content-Language: de-DE To: pgsql-admin@lists.postgresql.org References: From: Holger Jakobs In-Reply-To: Content-Type: multipart/signed; micalg=pgp-sha256; protocol="application/pgp-signature"; boundary="------------B1bYDFAPl3CJLoWhiYMBGfO6" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is an OpenPGP/MIME signed message (RFC 4880 and 3156) --------------B1bYDFAPl3CJLoWhiYMBGfO6 Content-Type: multipart/mixed; boundary="------------r2GGEhlEa8RjOd5K9QUgRXnK"; protected-headers="v1" From: Holger Jakobs To: pgsql-admin@lists.postgresql.org Message-ID: <5c6950cb-5ce2-2740-a447-5d9ff8418edd@jakobs.com> Subject: Re: Reindex concurrently References: In-Reply-To: --------------r2GGEhlEa8RjOd5K9QUgRXnK Content-Type: multipart/alternative; boundary="------------lGUfNKDDuZCX9uDnOTH0K2tp" --------------lGUfNKDDuZCX9uDnOTH0K2tp Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: base64 QW0gMTMuMTIuMjMgdW0gMTI6Mjcgc2NocmllYiBTT3pjbjoNCj4gSGVsbG8gdGVhbSwNCj4N Cj4gV2UndmUgYmVlbiBidWlsZGluZyBhIGluZGV4IG1haW50ZW5hbmNlIHNjcmlwdHMgYnV0 IGluIHRoZSBmdW5jdGlvbiBpdCANCj4gZG9lc24ndCB3b3JrIHdpdGggY29uY3VycmVudGx5 LCBpcyB0aGVyZSBhIHdheSBmb3IgbWFuaXB1bGF0ZcKgdGhhdCA/IA0KPiBKdXN0IGZvciBl eGFtcGxlIDsgVGhlIHJlaW5kZXhlcyBhcmUgd29ya2luZ8Kgd2l0aG91dMKgZnVuY3Rpb24u IEhhdmUgYSANCj4gbmljZSBkYXkhDQo+DQo+IHNlbGVjdCBpbmRleF9zdGF0LnRlc3QgKCk7 DQo+DQo+IENSRUFURSBPUiBSRVBMQUNFIEZVTkNUSU9OIGluZGV4X3N0YXQudGVzdCgpDQo+ DQo+IFJFVFVSTlMgdm9pZA0KPg0KPiBMQU5HVUFHRSBwbHBnc3FsDQo+DQo+IEFTICRmdW5j dGlvbiQNCj4NCj4gYmVnaW4NCj4NCj4gUkVJTkRFWCBJTkRFWCBDT05DVVJSRU5UTFkgInBs YXllcnNfaWRfaWR4IjsNCj4NCj4gY29tbWl0Ow0KPg0KPiBSRUlOREVYIElOREVYIENPTkNV UlJFTlRMWSAicGxheWVyc19hYm91dF9pZHgiOw0KPg0KPiBjb21taXQ7DQo+DQo+IGVuZDsN Cj4NCj4gJGZ1bmN0aW9uJA0KPg0KPiA7DQo+DQpIZWxsbywNCg0KImRvZXNuJ3Qgd29yayB3 aXRoIiBpc24ndCBhIGdvb2QgZGVzY3JpcHRpb24uIFBsZWFzZSBhbHdheXMgaW5jbHVkZSB0 aGUgDQplcnJvciBtZXNzYWdlIGdpdmVuLg0KDQpXaGF0IEkgd291bGQgc3VnZ2VzdCBpbnN0 ZWFkIGlzIHRyeSB1c2luZyBhIHByb2NlZHVyZSBpbnN0ZWFkIG9mIGEgZnVuY3Rpb24uDQoN CkNoZWVycw0KDQpIb2xnZXINCg0KDQoNCi0tIA0KSG9sZ2VyIEpha29icywgQmVyZ2lzY2gg R2xhZGJhY2gsIFRlbC4gKzQ5LTE3OC05NzU5MDEyDQoNCg== --------------lGUfNKDDuZCX9uDnOTH0K2tp Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Am 13.12.23 um 12:27 schrieb SOzcn:
Hello team,=C2=A0

We've been building a index maintenance scripts but in the function it doesn't work with concurrently, is there a way for manipulate=C2=A0that ? Just for example ; The reindexes are working=C2=A0without=C2=A0function. Have a nice day!

select index_stat.test ();

CREATE = OR REPLACE FUNCTION i= ndex_stat.test()

RETURNS void

LANGUAGE plpgsql

AS $function$

begin

REINDEX INDEX CONCURRENTLY "pla= yers_id_idx";

co= mmit;

REINDEX<= /span> INDEX <= span style=3D"color:rgb(128,0,0);font-weight:bold">CONCURRENTLY "players_about_idx";

commit;

end= ;

$function$

;

Hello,

"doesn't work with" isn't a good description. Please always include the error message given.

What I would suggest instead is try using a procedure instead of a function.

Cheers

Holger



--=20
Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012
--------------lGUfNKDDuZCX9uDnOTH0K2tp-- --------------r2GGEhlEa8RjOd5K9QUgRXnK-- --------------B1bYDFAPl3CJLoWhiYMBGfO6 Content-Type: application/pgp-signature; name="OpenPGP_signature.asc" Content-Description: OpenPGP digital signature Content-Disposition: attachment; filename="OpenPGP_signature" -----BEGIN PGP SIGNATURE----- wmMEABEIACMWIQScuJCxWJK1p5BOebGKV5yTsxlhugUCZXm2RQUDAAAAAAAKCRCKV5yTsxlhusnc AJ9KOedYir5qkAHSKbmcy4yi3l+Q+wCfbrmWJP/C0QlaO5UvfEt/xhZlAuI= =QBpv -----END PGP SIGNATURE----- --------------B1bYDFAPl3CJLoWhiYMBGfO6--