pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedReindex concurrently
8+ messages / 4 participants
[nested] [flat]
* Reindex concurrently
@ 2023-12-13 11:27 SOzcn <selahattinozcnma@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: SOzcn @ 2023-12-13 11:27 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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$
;
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex concurrently
@ 2023-12-13 13:48 Holger Jakobs <holger@jakobs.com>
parent: SOzcn <selahattinozcnma@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Holger Jakobs @ 2023-12-13 13:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
Attachments:
[application/pgp-signature] OpenPGP_signature (202B, ../../5c6950cb-5ce2-2740-a447-5d9ff8418edd@jakobs.com/3-OpenPGP_signature)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex concurrently
@ 2023-12-13 17:27 SOzcn <selahattinozcnma@gmail.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 2 replies; 8+ messages in thread
From: SOzcn @ 2023-12-13 17:27 UTC (permalink / raw)
To: Holger Jakobs <holger@jakobs.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex concurrently
@ 2023-12-13 17:53 Ron Johnson <ronljohnsonjr@gmail.com>
parent: SOzcn <selahattinozcnma@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ron Johnson @ 2023-12-13 17:53 UTC (permalink / raw)
To: SOzcn <selahattinozcnma@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org
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.)
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex concurrently
@ 2023-12-13 17:59 SOzcn <selahattinozcnma@gmail.com>
parent: Ron Johnson <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: SOzcn @ 2023-12-13 17:59 UTC (permalink / raw)
To: Ron Johnson <ronljohnsonjr@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org
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.)
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex concurrently
@ 2023-12-13 18:40 Ron Johnson <ronljohnsonjr@gmail.com>
parent: SOzcn <selahattinozcnma@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ron Johnson @ 2023-12-13 18:40 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
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.)
>>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex concurrently
@ 2023-12-13 18:46 David G. Johnston <david.g.johnston@gmail.com>
parent: SOzcn <selahattinozcnma@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: David G. Johnston @ 2023-12-13 18:46 UTC (permalink / raw)
To: SOzcn <selahattinozcnma@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Reindex concurrently
@ 2023-12-13 18:52 SOzcn <selahattinozcnma@gmail.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: SOzcn @ 2023-12-13 18:52 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Holger Jakobs <holger@jakobs.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
>
>
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-12-13 18:52 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-12-13 11:27 Reindex concurrently SOzcn <selahattinozcnma@gmail.com>
2023-12-13 13:48 ` Holger Jakobs <holger@jakobs.com>
2023-12-13 17:27 ` SOzcn <selahattinozcnma@gmail.com>
2023-12-13 17:53 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 17:59 ` SOzcn <selahattinozcnma@gmail.com>
2023-12-13 18:40 ` Ron Johnson <ronljohnsonjr@gmail.com>
2023-12-13 18:46 ` David G. Johnston <david.g.johnston@gmail.com>
2023-12-13 18:52 ` SOzcn <selahattinozcnma@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox