CODEWITHSUNDEEP
postgresqlforeign-data-wrapperpostgres-fdwforeign-table
Evgenii Sokolov
Evgenii Sokolov

Reputation: 11

Postgresql: how to ignore unavailable foreign tables when selecting data from them

I have a view as a union of several external tables (postgres_fdw). When selected in this view, some of the external servers may be unavailable (there is no network connection), and the select raise an error. Is it possible to ignore such errors and return only data from available tables?

Upvotes: 0

Views: 140

Answers (2)

Paul Maxwell
Paul Maxwell

Reputation: 35603

You could try a function or stored procedure with error handling, along these lines:

CREATE OR REPLACE FUNCTION union_foreign_tables(ft_info text[][])
RETURNS SETOF record
AS $$
DECLARE
    ft_row text[];
    ft_name text;
    ft_col1 text;
    ft_col2 text;
    ft_col3 text;
    ft_connected boolean;
    first_table boolean := true;
    query text := '';
    i integer;
BEGIN
    FOR i IN 1..array_length(ft_info, 1)
    LOOP
        ft_row := ft_info[i];
        ft_name := ft_row[1];
        ft_col1 := ft_row[2];
        ft_col2 := ft_row[3];
        ft_col3 := ft_row[4];

        ft_connected := true;
        BEGIN
            EXECUTE format('SELECT 1 FROM %I LIMIT 1', ft_name);
        EXCEPTION WHEN others THEN
            ft_connected := false;
        END;

        IF ft_connected THEN
            IF first_table THEN
                query := format('SELECT %I AS col1, %I AS col2, %I AS col3 FROM %I', ft_col1, ft_col2, ft_col3, ft_name);
                first_table := false;
            ELSE
                query := query || format(' UNION SELECT %I AS col1, %I AS col2, %I AS col3 FROM %I', ft_col1, ft_col2, ft_col3, ft_name);
            END IF;
        END IF;
    END LOOP;

    IF query <> '' THEN
        RETURN QUERY EXECUTE query;
    END IF;
END;
$$ LANGUAGE plpgsql;

Note as I have no idea what your current view query looks like, I have allowed for both multiple tables and different column names sequences per table which are passed into that function as an array of arrays:

SELECT union_foreign_tables(ARRAY[
    ARRAY['foreign_table1', 'col1', 'col2', 'col3'],
    ARRAY['foreign_table2', 'col1a', 'col2a', 'col3a'],
    ARRAY['foreign_table3', 'col1b', 'col2b', 'col3b']
]);

see unit test: https://dbfiddle.uk/yBRur0Jr (Postgres 15)

nb: Using a procedure may suit better if data volumes are very large.

Upvotes: 1

JohnH
JohnH

Reputation: 3180

There isn't a mechanism that would allow a view to ignore missing foreign tables using pure SQL. Conceptually, foreign tables that are inaccessible due to network connection issues aren't any different than tables that can't be accessed because the storage they are on is unavailable. Silently ignoring inaccessible tables seems illogical. How would one know that data is missing? A lack of data because tables are empty isn't the same as a lack of data due to tables being unavailable.

If you insist on silently ignoring unavailable tables, then you could create table functions that return the results of querying the foreign tables or empty result sets when exceptions occur. Your view could then use the results of these functions instead of directly querying the foreign tables. Just because one can do this, doesn't mean that one should.

Upvotes: 0

Related Questions