powa_take_snapshot(0): function "pg_track_settings_snapshot_rds" failed: null value in column "ts" of relation "pg_track_db_role_settings_history" violates not-null constraint

[sajiljosephs]

Error: powa_take_snapshot(0): function "pg_track_settings_snapshot_rds" failed: null value in column "ts" of relation "pg_track_db_role_settings_history" violates not-null constraint

[rjuju]

Oh, that's very strange. Could you show the results of those queries executed on the powa database:

SELECT * FROM select * from pg_track_settings_rds_src(2) WHERE ts IS NULL;

WITH rds AS (
        SELECT * FROM pg_track_settings_rds_src(0)
),
    dropped AS (
        SELECT now() AS ts, l.setdatabase, l.setrole, l.name
        FROM pg_track_db_role_settings_list l
        LEFT JOIN rds s ON (
            s.setdatabase = l.setdatabase
            AND s.setrole = l.setrole
            AND s.name = l.name
        )
        WHERE l.srvid = 0
            AND s.setdatabase IS NULL
            AND s.setrole IS NULL
            AND s.name IS NULL
    )
        SELECT 0, ts, d.setdatabase, d.setrole, d.name, NULL, true
        FROM dropped AS d
        WHERE ts IS NULL;

WITH rds AS (
        SELECT * FROM pg_track_settings_rds_src(0)
    ),
    last_snapshot AS (
        SELECT setdatabase, setrole, name, setting
        FROM (
            SELECT setdatabase, setrole, name, setting,
                row_number() OVER (PARTITION BY name, setdatabase, setrole ORDER BY ts DESC) AS rn
            FROM pg_track_db_role_settings_history
            WHERE srvid = 0
        ) all_snapshots
        WHERE all_snapshots.rn = 1
    )
    SELECT 0, s.ts, s.setdatabase, s.setrole, s.name, s.setting
    FROM rds s
    LEFT JOIN last_snapshot l ON
        l.setdatabase = s.setdatabase
        AND l.setrole = s.setrole
        AND l.name = s.name
    WHERE ((l.setdatabase IS NULL
        AND l.setrole IS NULL
        AND l.name IS NULL)
    OR (l.setting IS DISTINCT FROM s.setting)) AND s.ts IS NULL;

[sajiljosephs]

All 3 query results are empty.

====================================
powa=# select * from pg_track_settings_rds_src(2) WHERE ts IS NULL;
ts | name | setting | setdatabase | setrole
----+------+---------+-------------+---------
(0 rows)

powa=# WITH rds AS (
powa(# SELECT * FROM pg_track_settings_rds_src(0)
powa(# ),
powa-# dropped AS (
powa(# SELECT now() AS ts, l.setdatabase, l.setrole, l.name
powa(# FROM pg_track_db_role_settings_list l
powa(# LEFT JOIN rds s ON (
powa(# s.setdatabase = l.setdatabase
powa(# AND s.setrole = l.setrole
powa(# AND s.name = l.name
powa(# )
powa(# WHERE l.srvid = 0
powa(# AND s.setdatabase IS NULL
powa(# AND s.setrole IS NULL
powa(# AND s.name IS NULL
powa(# )
powa-# SELECT 0, ts, d.setdatabase, d.setrole, d.name, NULL, true
powa-# FROM dropped AS d
powa-# WHERE ts IS NULL;
?column? | ts | setdatabase | setrole | name | ?column? | ?column?
----------+----+-------------+---------+------+----------+----------
(0 rows)

powa=# WITH rds AS (
powa(# SELECT * FROM pg_track_settings_rds_src(0)
powa(# ),
powa-# last_snapshot AS (
powa(# SELECT setdatabase, setrole, name, setting
powa(# FROM (
powa(# SELECT setdatabase, setrole, name, setting,
powa(# row_number() OVER (PARTITION BY name, setdatabase, setrole ORDER BY ts DESC) AS rn
powa(# FROM pg_track_db_role_settings_history
powa(# WHERE srvid = 0
powa(# ) all_snapshots
powa(# WHERE all_snapshots.rn = 1
powa(# )
powa-# SELECT 0, s.ts, s.setdatabase, s.setrole, s.name, s.setting
powa-# FROM rds s
powa-# LEFT JOIN last_snapshot l ON
powa-# l.setdatabase = s.setdatabase
powa-# AND l.setrole = s.setrole
powa-# AND l.name = s.name
powa-# WHERE ((l.setdatabase IS NULL
powa(# AND l.setrole IS NULL
powa(# AND l.name IS NULL)
powa(# OR (l.setting IS DISTINCT FROM s.setting)) AND s.ts IS NULL;
?column? | ts | setdatabase | setrole | name | setting
----------+----+-------------+---------+------+---------
(0 rows)

[rjuju]

Sorry I made a typo in the first query, it should have been

SELECT * FROM pg_track_settings_rds_src(0) WHERE ts IS NULL;

and while at it, could you also execute this one:

SELECT max(ts) FROM pg_track_settings_rds_src(0)

[rjuju]

Ok, I could reproduce it locally. This is happening when doing a snapshot where there isn't anything in pg_db_role_setting anymore when there was before. This was supposed to be tested but the postgres regression test framework automatically creates a regression database with some settings, which means that there are always settings when running the tests. I will work on a fix.

[sajiljosephs]

For the time being I have fixed it with pg_wait_sampling extension

===
$  make USE_PGXS=1 installcheck
echo "+++ regress install-check in  +++" && /usr/lib/postgresql/15/lib/pgxs/src/makefiles/../../src/test/regress/pg_regress --inputdir=./ --bindir='/usr/lib/postgresql/15/bin'   --temp-config=/usr/lib/postgresql/15/lib/pgxs/src/makefiles/../..//conf.add --dbname=contrib_regression load queries
+++ regress install-check in  +++
(using postmaster on Unix socket, default port)
============== dropping database "contrib_regression" ==============
SET
DROP DATABASE
============== creating database "contrib_regression" ==============
CREATE DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
ALTER DATABASE
============== running regression test queries        ==============
test load                         ... ok           56 ms
test queries                      ... ok           72 ms

 

=====================
All 2 tests passed. 

[rjuju]

I'm not sure how pg_wait_sampling is related here?

[sajiljosephs]

make USE_PGXS=1 installcheck in pg_wait_sampling creates a regression database and that seems to fix this error

[rjuju]

Ah, yes as I mentioned the bug can only happen if:

• you had some role / database setting
• had a pg_track_settings snapshot that recorded them
• remove ALL role / database settings

Doing any ALTER ROLE / ALTER DATABASE / ALTER ROLE IN DATABASE to set any parameter will remove the source of the bug.

[rjuju]

I just pushed a fix for that problem at rjuju/pg_track_settings@302d4cd (and found another issue that I also fixed). If you have the possibility to test I'm interested in some feedback, otherwise I will release a new version soon.

[sajiljosephs]

It works perfect

[rjuju]

Thanks for the feedback. I just released version 2.1.2, so closing this issue!