CDB_UserDataSize is not safe, might raise error

[rochoa]

It might raise ERROR: relation "*" does not exist.

pg_total_relation_size might run against an nonexistent relation the table list is previously populated in a CTE and between list generation and consumption it's possible some of the relations get deleted.

A safer version is required, handling the nonexistent relation case.

I've created a simpler snippet so the scenario is easier to reproduce:

DROP TABLE IF EXISTS public.foo;
CREATE table public.foo (a text);

CREATE OR REPLACE FUNCTION drop_table()
RETURNS bigint AS $$
BEGIN
  DROP TABLE public.foo;
  return 0;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;

WITH user_tables AS (
  SELECT table_name FROM information_schema.tables
  WHERE table_catalog = current_database() AND table_schema = 'public'
    AND table_name != 'spatial_ref_sys'
    AND table_name != 'cdb_tablemetadata'
    AND table_type = 'BASE TABLE'
),
dropping_table AS (
  SELECT drop_table() as wadus
)
SELECT table_name, pg_total_relation_size('"' || 'public' || '"."' || table_name || '"') table_size
FROM user_tables, dropping_table

p.s. just by removing the dropping_table in the last line from the FROM clause it will work as, I guess, the query planner knows it doesn't need to execute that CTE.

[javisantana]

maybe removing the CTE and doing it as a subquery?

[pramsey]

Huh, well color me surprised. The docs seem to indicate that all parts of a CTE work on independent contexts...

The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot (see Chapter 13), so they cannot "see" one another's effects on the target tables. This alleviates the effects of the unpredictability of the actual order of row updates, and means that RETURNING data is the only way to communicate changes between different WITH sub-statements and the main query.

I wonder if the system tables are not managed quite as strictly as the data table WRT to snapshots in MVCC. So, seems like a legitimate problem, and testing ahead of time is reasonable, or wrapping the call to pg_total_relation_size in an exception block.

[rochoa]

Thanks a lot @pramsey!

Considering that information and having in mind your comment about functions' transactions I guess it's enough to use the extra function with the if exists approach proposed in PR 110.

Or I'm missing something and we still need to wrap it with an exception block?

[pramsey]

I think the IF approach will be fine, or your all-in-one query, which is nice.