Обсуждение: Reindex concurrently
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 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
Вложения
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;
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
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.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.
There is no problem with using a bash script.
Is the crontab useful for those who have multiple DBs in a cluster?
I thought it would be easier to manage in DB for ease of management. Before using the bash script option, I wanted to confirm whether this is possible on the DB.
On Wed, Dec 13, 2023 at 12:28 PM SOzcn <selahattinozcnma@gmail.com> wrote: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.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.What's wrong with a bash script run from cron? (You should see my crontab; it's ginormous, running scripts against databases on 8 different servers.)
Hello Ron,
There is no problem with using a bash script.
Is the crontab useful for those who have multiple DBs in a cluster?
I thought it would be easier to manage in DB for ease of management. Before using the bash script option, I wanted to confirm whether this is possible on the DB.Ron Johnson <ronljohnsonjr@gmail.com>, 13 Ara 2023 Çar, 20:53 tarihinde şunu yazdı:On Wed, Dec 13, 2023 at 12:28 PM SOzcn <selahattinozcnma@gmail.com> wrote: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.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.What's wrong with a bash script run from cron? (You should see my crontab; it's ginormous, running scripts against databases on 8 different servers.)
According to my research, the only way to do this is to write a bash script in Linux and insert it with the loop.
On Wednesday,According to my research, the only way to do this is to write a bash script in Linux and insert it with the loop.Right. It is fundamentally impossible to store something in the database capable of executing multiple commands and not execute it within a normal transaction. The maintenance work that cannot be done within a transaction must be sourced externally and executed in an implicit transaction that auto-commits after the command finishes.David J.