0

I have to change cache for all sequences in schema and database. I can not change one by one. Is there some script or query which can change cache size for all sequences?

closed as unclear what you're asking by mustaccio, kevinsky, hot2use, marcello miorelli, Vérace Jun 2 at 12:32

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

1

You can use a DO statement so that you can run a PL/pgSQL block in a single SQL statement:

DO
$$DECLARE
   v_schema text;
   v_sequence text;
BEGIN
   FOR v_schema, v_sequence IN
      SELECT sequence_schema, sequence_name
      FROM information_schema.sequences
      WHERE sequence_schema = 'myschema'
   LOOP
      EXECUTE format('ALTER SEQUENCE %I.%I CACHE 20',
                     v_schema, v_sequence);
   END LOOP;
END;$$;
  • Yes I changed to public schema and I get the following: Error report - Unterminated dollar quote started at position 283 in SQL BEGIN FOR v_schema, v_sequence IN.....I have hundreds of sequences but just give example for two.THNX SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema = 'public' LOOP EXECUTE format('ALTER SEQUENCE %I.%I CACHE 20', v_schema, v_sequence); END LOOP; END;$$;. Expected terminating $$ – arminm May 29 at 9:05
  • Are you using psql as client? If not, maybe your client cannot digest dollar quoted strings correctly. In that case, use a different client. I'll edit the answer to avoid dollar quoting. – Laurenz Albe May 29 at 9:10
  • Dear Laurenz, i have connection to my postgres database via sql developer 18.4.Thank you – arminm May 29 at 9:12
  • That's probably your problem. – Laurenz Albe May 29 at 9:14
  • When I run code i get this: DECLARE v_schema text, v_sequence text; BEGIN FOR v_schema, v_sequence IN SELECT sequence_schema, sequence_name FROM information_schema.sequences WHERE sequence_schema = ''public'' LOOP EXECUTE format(''ALTER SEQUENCE %I.%I CACHE 20'', v_schema, v_sequence); END LOOP; END;; Error report - ERROR: syntax error at or near "text" Position: 21 (I have to remove (') before DECLARE statement because with this all my code is blue color and unusable)... – arminm May 29 at 9:18
0

A simple approach which is often adequate is to generate all the statements in a query and then just copy and paste them to run them. This way you can customize the statements for each occasion. The queries are simple enough that you don't even need to save the code for future use.

SELECT 'alter sequence ' || sequence_schema || '.' || sequence_name || ' cache 20;'
FROM information_schema.sequences;

It should be obvious you can use this "trick" for other object types too.

Not the answer you're looking for? Browse other questions tagged or ask your own question.