Обсуждение: Reindex concurrently

Поиск
Список
Период
Сортировка

Reindex concurrently

От
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$

;

Re: Reindex concurrently

От
Holger Jakobs
Дата:
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
Вложения

Re: Reindex concurrently

От
SOzcn
Дата:
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.




Holger Jakobs <holger@jakobs.com>, 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

Re: Reindex concurrently

От
Ron Johnson
Дата:
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.)

Re: Reindex concurrently

От
SOzcn
Дата:
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.)

Re: Reindex concurrently

От
Ron Johnson
Дата:
With multiple databases and functions in every database, you must substantially duplicate code in every database.

With bash and cron, you write generalized scripts and pass parameters to them.

On Wed, Dec 13, 2023 at 12:59 PM SOzcn <selahattinozcnma@gmail.com> wrote:
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.)

Re: Reindex concurrently

От
"David G. Johnston"
Дата:
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.

Re: Reindex concurrently

От
SOzcn
Дата:
Cool!

I'll do it with bash. Cheers!

David G. Johnston <david.g.johnston@gmail.com>, 13 Ara 2023 Çar, 21:46 tarihinde şunu yazdı:
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.