Redshift: Total Row Count for all tables for a particular user

Question

We use Periscope which charges(I think) by the total number of rows that are present in the tables that we give it access to.

My question is: Given a user periscope, how do I obtain the total count of rows in the tables that the user periscope has access to?

E.g. if the user periscope can only access two tables: t1 with count 8 and t2 with count 2, the total count should be 10.

I got all the tables with this answer(excluding 'pg_tables' and 'information_schema') but I don't know how to go about next:

SELECT CAST(schemaname as varchar), CAST(  objectname as varchar)
FROM 
    (
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AS ref
    FROM
        (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
    )
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and usename = 'periscope'
and schemaname not in ('information_schema', 'pg_catalog');

If you have an elegant solution, please let me know!

score 2 · Accepted Answer · answered Sep 07 '16 at 21:01

This should give you what you need:

SELECT SUM(b.reltuples)
FROM 
(
    SELECT 
        schemaname
        ,objectname
        ,usename
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'select') AS sel
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'insert') AS ins
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'update') AS upd
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'delete') AS del
        ,HAS_TABLE_PRIVILEGE(usrs.usename, fullobj, 'references') AS ref
    FROM
    (
        SELECT schemaname, 't' AS obj_type, tablename AS objectname, schemaname + '.' + tablename AS fullobj FROM pg_tables
        WHERE schemaname not in ('pg_internal')
        UNION
        SELECT schemaname, 'v' AS obj_type, viewname AS objectname, schemaname + '.' + viewname AS fullobj FROM pg_views
        WHERE schemaname not in ('pg_internal')
        ) AS objs
        ,(SELECT * FROM pg_user) AS usrs
    ORDER BY fullobj
) as a
JOIN 
(
    SELECT 
        nspname AS schemaname,relname,reltuples
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    ORDER BY reltuples DESC
) as b
ON (a.schemaname = b.schemaname AND a.objectname = b.relname)
WHERE (sel = true or ins = true or upd = true or del = true or ref = true)
and usename = 'periscope'
and a.schemaname not in ('information_schema', 'pg_catalog');

I've found the numbers from `pg_class.reltuples` in Redshift can be a long way off, I wonder if it's related to node partitioning or compression or something? The [`SVV_TABLE_INFO`](https://docs.aws.amazon.com/redshift/latest/dg/r_SVV_TABLE_INFO.html) system table has a `tbl_rows` property which is defined as "Total number of rows in the table. This value includes rows marked for deletion, but not yet vacuumed." — rcoup, Dec 18 '18 at 10:53

Redshift: Total Row Count for all tables for a particular user

1 Answers1