Hello,
The result is when I run the function; "Reindex concurrently cannot run inside a transaction block. "
Of course, it works as follows, but in my building, I taken the scripts same like as follows but when I run this in function, I got the error message.
The result is when I run the function; "Reindex concurrently cannot run inside a transaction block. "
Of course, it works as follows, but in my building, I taken the scripts same like as follows but when I run this in function, I got the error message.
REINDEX INDEX CONCURRENTLY "players_id_idx";
commit;
REINDEX INDEX CONCURRENTLY "players_about_idx";
commit;
According to my research, the only way to do this is to write a bash script in Linux and insert it with the loop. But since I want to manage this in the database system, I wanted to ask here.
Holger Jakobs <holger@xxxxxxxxxx>, 13 Ara 2023 Çar, 16:49 tarihinde şunu yazdı:
Am 13.12.23 um 12:27 schrieb SOzcn:
Hello team,
We've been building a index maintenance scripts but in the function it doesn't work with concurrently, is there a way for manipulate that ? Just for example ; The reindexes are working without function. Have a nice day!
select index_stat.test ();
CREATE OR REPLACE FUNCTION index_stat.test()
RETURNS void
LANGUAGE plpgsql
AS $function$
begin
REINDEX INDEX CONCURRENTLY "players_id_idx";
commit;
REINDEX INDEX 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
-- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012