Some examples of how to use the function pg_partition_tree

From PostgreSQL 11, the fuction pg_partition_tree has been available

Usage, in its simplest form:

SELECT *
FROM pg_partition_tree('ldksf.entitet');

select * from pg_partition_tree('ldksf.entitet');
       relid        | parentrelid | isleaf | level
--------------------+-------------+--------+-------
 entitet            |             | f      |     0
 entitet_default    | entitet     | t      |     1
 entitet_p0         | entitet     | t      |     1
 entitet_p120000000 | entitet     | t      |     1
 entitet_p150000000 | entitet     | t      |     1

Make it a bit more informativ, together with other tables in the data dictionary. Put the following into a file called pg_tree_info.sql:

\echo myschema = :myschema
\echo mytable  = :mytable

SELECT
    s.schemaname,
    s.relname AS table_name,
    s.n_live_tup,
    s.last_analyze,
    s.last_autoanalyze
FROM pg_partition_tree(format('%I.%I', :'myschema', :'mytable')::regclass) pt
JOIN pg_class c
  ON c.oid = pt.relid
JOIN pg_namespace n
  ON n.oid = c.relnamespace
JOIN pg_stat_all_tables s
  ON s.schemaname = n.nspname
 AND s.relname = c.relname
ORDER BY s.n_live_tup DESC, s.last_analyze;

SELECT
    pt.level,
    pt.isleaf,
    n.nspname,
    c.relname
FROM pg_partition_tree(
        format('%I.%I', :'myschema', :'mytable')::regclass
     ) pt
JOIN pg_class c ON c.oid = pt.relid
JOIN pg_namespace n ON n.oid = c.relnamespace
ORDER BY pt.level, c.relname;

Example output:

myschema = scott
mytable = entitet
 schemaname |     table_name     | n_live_tup |         last_analyze          |       last_autoanalyze
------------+--------------------+------------+-------------------------------+-------------------------------
 ldksf      | entitet_p30000000  |   14706380 | 2026-03-10 22:15:50.390363+01 | 2026-03-10 16:29:36.398134+01
 ldksf      | entitet_p0         |   12193064 | 2026-03-10 22:15:50.749426+01 | 2026-03-10 16:27:35.272815+01
 ldksf      | entitet_p60000000  |    5481387 | 2026-03-10 22:15:51.069335+01 | 2026-03-10 16:31:35.842357+01
 ldksf      | entitet_default    |          0 | 2026-03-10 22:15:53.688216+01 |
 ldksf      | entitet_p180000000 |          0 | 2026-03-10 22:15:53.68893+01  |
 
 
 level | isleaf | nspname  |      relname
-------+--------+----------+--------------------
     0 | f      |   ldksf  | entitet
     1 | t      |   ldksf  | entitet_default
     1 | t      |   ldksf  | entitet_p0
     1 | t      |   ldksf  | entitet_p120000000
     1 | t      |   ldksf  | entitet_p150000000

Excute it like this:

psql -h prod1.pgsql01.oric.no -d mydb -U scott -v myschema=ldksf -v mytable=entitet -f pg_tree_info.sql

Find active queries in PostgreSQL

SELECT pid, state, now() - query_start AS duration, left(query, 80) AS query
FROM pg_stat_activity
WHERE state != 'idle' AND usename = 'myuser'
ORDER BY query_start;

Example output:

   pid   | state  |    duration     |                                    query
---------+--------+-----------------+------------------------------------------------------------------------------
 1648516 | active | 01:29:28.979693 |                                                                             +
         |        |                 | UPDATE myschema.mytable1 eeu                                   +
         |        |                 | SET aggregated_value = (SELECT e.aggregated_value FROM

This is the query I am looking for, started in another session. The full query text is

UPDATE myschema.mytable1 eeu
SET aggregated_value = (SELECT e.aggregated_value FROM myschema.mytable2 e WHERE e.systemid = eeu.entitet AND e.instansid = eeu.instansid);

Identity columns vs free-standing sequences in PostgreSQL

Are the columns typically used in primary key columns, defined as IDENTITY columns, or as standalone columns with a default value generated from a free-standing sequence?

SELECT table_name, column_name, is_identity, column_default
FROM information_schema.columns
WHERE table_schema = 'myschema'
AND column_name IN ('sekvnr', 'id')
AND table_name IN ('table1','table2','table3','table4',
                   'table5','table6',
                   'table7','table8')
ORDER BY table_name, column_name;

Result:

        table_name          |  column_name  | is_identity | column_default
----------------------------+---------------+-------------+----------------
 table1                     | id            | YES         |
 table2                     | id            | YES         |
 table3                     | sekvnr        | NO          |
 table4                     | id            | YES         |
 table5                     | id            | YES         |
 table6                     | id            | YES         |
 table7                     | id            | YES         |
 table8                     | id            | YES         |
(8 rows)

It's clear from the query that the only table that is still supported by a free-standing sequence to generate its primary key values, is table3!

How to find the current value for a PostgreSQL sequence

SELECT last_value FROM myschema.mysequence;

or

SELECT nextval('myschema.mysequence');

Note: nextval() advances the sequence — use last_value if you just want to inspect without side effects.

How to find triggers in a PostgreSQL schema

Execute the SQL below as a user with ownership to the schema. To verify that you have schema privileges, execute:

SELECT has_schema_privilege('myuser', 'myschema', 'USAGE');

If the reply is

has_schema_privilege
----------------------
 t

You can login

psql -h localhost -d mydb -U myuser

and execute

\x
Expanded display is on.

SELECT
    'TRIGGER' AS object_type,
    t.tgname AS trigger_name,
    n.nspname AS schema_name,
    c.relname AS table_name,

    -- Decode tgenabled letters
    CASE t.tgenabled
        WHEN 'O' THEN 'ENABLE'
        WHEN 'A' THEN 'ENABLE ALWAYS'
        WHEN 'R' THEN 'ENABLE REPLICA'
        WHEN 'D' THEN 'DISABLED'
        ELSE t.tgenabled
    END AS enabled_state,

    -- Trigger timing from tgtype
    CASE
        WHEN (t.tgtype & 2)  <> 0 THEN 'BEFORE'
        WHEN (t.tgtype & 64) <> 0 THEN 'INSTEAD OF'
        ELSE 'AFTER'
    END AS timing,

    -- Events
    (SELECT string_agg(event, ', ')
     FROM (
            SELECT unnest(
                ARRAY[
                    CASE WHEN (t.tgtype & 4)  <> 0 THEN 'INSERT'   END,
                    CASE WHEN (t.tgtype & 8)  <> 0 THEN 'DELETE'   END,
                    CASE WHEN (t.tgtype & 16) <> 0 THEN 'UPDATE'   END,
                    CASE WHEN (t.tgtype & 32) <> 0 THEN 'TRUNCATE' END
                ]
            ) AS event
        ) ev
    ) AS events,

    pron.nspname || '.' || p.proname AS trigger_function

FROM pg_trigger t
JOIN pg_class c        ON c.oid = t.tgrelid
JOIN pg_namespace n    ON n.oid = c.relnamespace
JOIN pg_proc p         ON p.oid = t.tgfoid
JOIN pg_namespace pron ON pron.oid = p.pronamespace

WHERE n.nspname = current_schema()
  AND NOT t.tgisinternal
  AND t.tgconstraint = 0
  AND c.relkind <> 'p'
ORDER BY trigger_name;

Which will list your triggers:

-[ RECORD 1 ]----+------------------------------------------------------------------
object_type      | TRIGGER
trigger_name     | mytrg1
schema_name      | myschema
table_name       | mytable1
enabled_state    | E
timing           | BEFORE
events           | UPDATE
trigger_function | myschema.trigger_fct_upd_mytable1
-[ RECORD 2 ]----+------------------------------------------------------------------
object_type      | TRIGGER
trigger_name     | mytrg2
schema_name      | myschema
table_name       | mytable2
enabled_state    | E
timing           | AFTER
events           | DELETE
trigger_function | myschema.trg_close_fct

Useful query against v$archived_log in recovery situations

SELECT thread#,
         COUNT(*)                        AS cnt,
         MIN(sequence#)                  AS min_seq,
         MAX(sequence#)                  AS max_seq,
         MIN(first_time)                 AS min_time,
         MAX(next_time)                  AS max_time
  FROM   v$archived_log
  WHERE  deleted = 'NO'
  GROUP  BY thread#
  ORDER  BY thread#;

   THREAD#    CNT    MIN_SEQ    MAX_SEQ               MIN_TIME               MAX_TIME
__________ ______ __________ __________ ______________________ ______________________
         1     80       7936       8015 2026-02-16 15:38:02    2026-02-18 14:15:42

Dropping an audit policy for a specific user

You have the following audit policy:

CREATE AUDIT POLICY monitor_sys_everything
  ACTIONS ALL;

AUDIT POLICY monitor_sys_everything BY SYS;

I wanted to drop the auditing policy:

SQL> noaudit policy monitor_sys_everything;

Noaudit succeeded.

SQL> drop audit policy monitor_Sys_everything;
drop audit policy monitor_Sys_everything
*
ERROR at line 1:
ORA-46361: Audit policy cannot be dropped as it is currently enabled.

Reason:

The policy is enabled for a specific user. The "noaudit" statement used above will disable it for everyone.

Solution:

Disable it for the specific user:

SQL> NOAUDIT POLICY monitor_sys_everything BY SYS;

It can now be dropped:

drop audit policy monitor_sys_everything;