Thread

1. alter system appending to a value

   Luca Ferrari <fluca1978@gmail.com> — 2025-04-30T11:14:29Z

   Hi all,
   as trivial as it sounds, is there a smart way to use ALTER SYSTEM to
   append to a value?
   Something like: ALTER SYSTEM shared_preloaded_libraries =
   current_setting( 'shared_preloaded_libraries' ) || ',foo';
   
   Rationale: I'm using ansible to configure, thru different steps,
   PostgreSQL instances and I would like to have every part to append its
   configuration on the previous one.
   
   Thanks,
   Luca
   
   
   
   

2. Re: alter system appending to a value

   Victor Yegorov <vyegorov@gmail.com> — 2025-04-30T12:19:01Z

   ср, 30 апр. 2025 г. в 14:15, Luca Ferrari <fluca1978@gmail.com>:
   
   > as trivial as it sounds, is there a smart way to use ALTER SYSTEM to
   > append to a value?
   > Something like: ALTER SYSTEM shared_preloaded_libraries =
   > current_setting( 'shared_preloaded_libraries' ) || ',foo';
   >
   
   I would do smth like:
   SELECT format( 'ALTER SYSTEM SET shared_preload_libraries = %L;', setting )
     FROM pg_settings WHERE name = 'shared_preload_libraries' \gexec
   
   Of course, you should add new value to the existing setting, making sure
   there are no duplicates and the format is correct.
   
   -- 
   Victor Yegorov
   

3. Re: alter system appending to a value

   Greg Sabino Mullane <htamfids@gmail.com> — 2025-04-30T16:11:37Z

   On Wed, Apr 30, 2025 at 7:15 AM Luca Ferrari <fluca1978@gmail.com> wrote:
   
   > Rationale: I'm using ansible to configure, thru different steps,
   > PostgreSQL instances and I would like to have every part to append its
   > configuration on the previous one.
   >
   
   Ansible is good for bringing your systems to a known consistent state, so
   your best bet would be to maintain the list of libraries inside Ansible and
   then simply apply it. If you really don't know what might be in the
   database, I would have Ansible read the current value, build the new one if
   the desired library is not there, and apply the new value.
   
   Cheers,
   Greg
   
   --
   Crunchy Data - https://www.crunchydata.com
   Enterprise Postgres Software Products & Tech Support