Make all tables in database UNLOGGED

Question

I want to decrease local test execution time by making all tables UNLOGGED. I want to write sql script, which will run after all conversion and make them UNLOGGED. But I found problem - tables relate each other with FK, so postgresql prohibit make table UNLOGGED (through ALTER) if it related from other table which is not UNLOGGED yet.

Is there better way then list all ALTER in correct order - I have more then 150 tables? For example, apply it on database level.

Answer 1

You have to ALTER them in right order I'm afraid. You can select https://www.postgresql.org/docs/current/static/catalog-pg-constraint.html and loop for referencing tables first and then alter the rest:

begin;
do
$$
declare
 _r record;
 _t text;
begin
 for _r in (
    select relname,conrelid
    from pg_constraint
    join pg_class c on c.oid = conrelid
    where confkey is not null
    order by conrelid desc
    -- Order by oid with logic that you should start from latest added objects to earliest - of course it does not garantee anything
 ) loop
    _t := format('alter table %I set unlogged',_r.relname);
    raise info '%',_t;
    execute _t;
 end loop;

  for _r in (select tablename from pg_tables where tablename like 's%' and schemaname = 'public') loop
    _t := format('alter table %I set unlogged',_r.tablename);
    raise info '%',_t;
    execute _t;
 end loop;

end;
$$
;
rollback;

By all means it will fail if you have recursive FK:

t=# create table s134(i int primary key, e int);
CREATE TABLE
t=# create table s135(i int references s134(i), e int primary key);
CREATE TABLE
t=# alter table s134 add constraint c1 foreign key (e) references s135(e);
ALTER TABLE
t=# alter table s134 set unlogged;
ERROR:  could not change table "s134" to unlogged because it references logged table "s135"
t=# alter table s135 set unlogged;
ERROR:  could not change table "s135" to unlogged because it references logged table "s134"

But you would not achieve that any way I believe.

Also don't forget that after unclean shutdown or failure unlogged tables will be truncated.

And lastly you say "after all conversion" - if you create, convert etc, maybe you should just create them unlogged?..

Answer 2

I would drop and re-create all foreign keys. You can automate this.

The following query will generate the necessary DDL statements for all foreign keys. You need to save the output of that into a file, which you can later use to restore all foreign keys.

select format('alter table %I.%I add constraint %I ',  ns.nspname, tb.relname, conname)|| 
       pg_get_constraintdef(c.oid, true)||';' as ddl
from pg_constraint c
  join pg_class tb on tb.oid = c.conrelid
  join pg_namespace ns on ns.oid = tb.relnamespace
where ns.nspname not in ('pg_catalog', 'information_schema')
  and ns.nspname not like 'pg_temp%'
  and c.contype in ('f')

Then generate a script to drop all constraints:

select format('alter table %I.%I drop constraint %I cascade;', ns.nspname, tb.relname, c.conname) as ddl
from pg_constraint c
  join pg_class tb on tb.oid = c.conrelid
  join pg_namespace ns on ns.oid = tb.relnamespace
where ns.nspname not in ('pg_catalog', 'information_schema')
  and ns.nspname not like 'pg_temp%'
  and c.contype in ('f');

Of course you have to run the query to generate the FKs before you drop them ;)

Answer 3

I had difficulties with the accepted answer, because it relies on a heuristic (which may not apply) to get the ordering correct. I have reworked the suggested answer using a (somewhat verbose) recursive query which allows one to get an exact ordering required. I have not tested it with recursive FKs, I suspect that the recursive query will not terminate. Note that I have restricted the query to apply to tables in the public schema, modify to suit your own needs.

Unlog all tables:

do
$$
    declare
        _r record;
        _t text;
    begin
        for _r in (
            WITH RECURSIVE constraints AS (
                SELECT
                    tc.table_schema
                     , tc.table_name
                     , kcu.column_name
                     , ccu.table_name AS foreign_table_name
                FROM
                    information_schema.table_constraints AS tc
                        LEFT OUTER JOIN information_schema.key_column_usage AS kcu
                                        ON tc.constraint_name = kcu.constraint_name
                                            AND tc.table_schema = kcu.table_schema
                        LEFT OUTER JOIN information_schema.constraint_column_usage AS ccu
                                        ON ccu.constraint_name = tc.constraint_name
                                            AND ccu.table_schema = tc.table_schema
                WHERE (tc.constraint_type IS NULL OR tc.constraint_type = 'FOREIGN KEY'))
               , tables_and_constraints AS (
                SELECT
                    t.table_schema
                     , t.table_name
                     , c.foreign_table_name
                FROM information_schema.tables AS t
                         LEFT JOIN constraints AS c USING (table_name)
                WHERE t.table_type <> 'VIEW')
               , dependent_table_constraints AS (
                SELECT tc.table_schema
                     , tc.table_name
                     , tc.foreign_table_name
                     , 0 AS depth
                FROM tables_and_constraints AS tc
                WHERE foreign_table_name IS NULL
                UNION
                SELECT tc.table_schema
                     , tc.table_name
                     , tc.foreign_table_name
                     , dtc.depth + 1
                FROM tables_and_constraints AS tc
                         INNER JOIN dependent_table_constraints AS dtc ON tc.foreign_table_name = dtc.table_name
            ) SELECT table_name, max(depth) as depth FROM dependent_table_constraints
            WHERE table_schema = 'public'
            GROUP BY table_name
            ORDER BY depth DESC
        ) loop
                _t := format('ALTER TABLE %I SET UNLOGGED',_r.table_name);
                raise info '%',_t;
                execute _t;
            end loop;
    end;
$$
;

Relog all tables:

do
$$
    declare
        _r record;
        _t text;
    begin
        for _r in (
            WITH RECURSIVE constraints AS (
                SELECT
                    tc.table_schema
                     , tc.table_name
                     , kcu.column_name
                     , ccu.table_name AS foreign_table_name
                FROM
                    information_schema.table_constraints AS tc
                        LEFT OUTER JOIN information_schema.key_column_usage AS kcu
                                        ON tc.constraint_name = kcu.constraint_name
                                            AND tc.table_schema = kcu.table_schema
                        LEFT OUTER JOIN information_schema.constraint_column_usage AS ccu
                                        ON ccu.constraint_name = tc.constraint_name
                                            AND ccu.table_schema = tc.table_schema
                WHERE (tc.constraint_type IS NULL OR tc.constraint_type = 'FOREIGN KEY'))
               , tables_and_constraints AS (
                SELECT
                    t.table_schema
                     , t.table_name
                     , c.foreign_table_name
                FROM information_schema.tables AS t
                         LEFT JOIN constraints AS c USING (table_name)
                WHERE t.table_type <> 'VIEW')
               , dependent_table_constraints AS (
                SELECT tc.table_schema
                     , tc.table_name
                     , tc.foreign_table_name
                     , 0 AS depth
                FROM tables_and_constraints AS tc
                WHERE foreign_table_name IS NULL
                UNION
                SELECT tc.table_schema
                     , tc.table_name
                     , tc.foreign_table_name
                     , dtc.depth + 1
                FROM tables_and_constraints AS tc
                         INNER JOIN dependent_table_constraints AS dtc ON tc.foreign_table_name = dtc.table_name
            ) SELECT table_name, max(depth) as depth FROM dependent_table_constraints
            WHERE table_schema = 'public'
            GROUP BY table_name
            ORDER BY depth ASC
        ) loop
                _t := format('ALTER TABLE %I SET LOGGED',_r.table_name);
                raise info '%',_t;
                execute _t;
            end loop;
    end;
$$
;