Migrate PostgreSQL serial columns to identity, pg_toast error

Adam Johnson has an excellent post which suggests a management command to migrate legacy PostgreSQL serial columns to identity columns.

Unfortunately, when I tried to use it on my first database, I got a permission error when running the cursor.execute(find_serial_columns, (like,)) line:

django.db.utils.ProgrammingError: permission denied for schema pg_toast

I guess I could try to change the database user to temporarily use a PostgreSQL admin account just to run the script. However, I wonder if someone has an idea about writing a find_serial_columns query which allows less-privileged accounts to collect serial columns of a database.

Claude

I couldn’t find much information around about pg_toast, it isn’t exactly covered well in the PostgreSQL docs: PostgreSQL: Search results . But I guess it’s a schema implicitly created by PostgreSQL for large value storage.

I did find this SO answer:

Using the pg_class table to limit the search to ordinary and partitioned tables sounds legitimate to me.

Here’s an updated version of the query that does so:

# Adapted from: https://dba.stackexchange.com/a/90567
find_serial_columns = """\
SELECT
     a.attrelid::regclass::text AS table_name,
     a.attname AS column_name
FROM pg_attribute a
     JOIN pg_class c ON c.oid = a.attrelid
WHERE
    a.attrelid::regclass::text LIKE %s
    AND c.relkind IN ('r', 'p')  /* regular and partitioned tables */
    AND a.attnum > 0
    AND NOT a.attisdropped
    AND a.atttypid = ANY ('{int,int8,int2}'::regtype[])
    AND EXISTS (
        SELECT FROM pg_attrdef ad
        WHERE
            ad.adrelid = a.attrelid
            AND ad.adnum = a.attnum
            AND (
                pg_get_expr(ad.adbin, ad.adrelid)
                =
                'nextval('''
                || (
                    pg_get_serial_sequence(a.attrelid::regclass::text, a.attname)
                )::regclass
                || '''::regclass)'
            )
    )
ORDER BY a.attnum
"""
``

Can you try using that? I haven’t tried to reproduce your issue locally, since I'm not quite sure how to do so. But if this works for you then I can update the post.

Awesome, that did it! Thanks a lot.

Great, post updated!