Late Binding Views

In my view, the most significant feature missing from PostgreSQL is the ability to create views without hard dependencies. Views reference objects by oid not by name, hence creating a view always has the potential to break automated schema updates.

Rules

Internally PostgreSQL implements views using the rule system. Prior to PostgreSQL 16 tables were converted to views when an ON SELECT rule was added

CREATE TABLE tag (
  tag_id integer NOT NULL,
  name varchar(20) NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now()
);

CREATE TABLE tag2 (
  tag_id integer NOT NULL,
  name varchar(20) NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT now()
);

CREATE RULE "_RETURN" AS
    ON SELECT TO tag
    DO INSTEAD
        SELECT * FROM tag2;

After 16 this is not permitted

ERROR:  relation "tag" cannot have ON SELECT rules
DETAIL:  This operation is not supported for tables.

Solutions

Option 1: Warn when column is removed or column type is changed

View defenition will show "?dropped?column?"
Add invalid column to the pg_rewrite table indicating that OIDs cannot be trusted

Option 2: Drop dependent views automatically

Record view definition in the table public.autoremove_views
Include a timestamp and the DDL that triggered the change

Option 3: Create a copy of a dependent table definition in a special schema referenced_objects.

Point views to new/empty table
Raise runtime errors for SELECT by restricting permissions to the referenced_objects schema

Event Triggers

It is possible to intercept changes to a table, including column type changes

CREATE FUNCTION scan_view_trigger()
  RETURNS event_trigger
  LANGUAGE plpgsql AS
$$
DECLARE
  table_oid oid := pg_event_trigger_table_rewrite_oid();
  reason text := pg_event_trigger_table_rewrite_reason();
BEGIN
  RAISE NOTICE 'rewriting table % with reason %', table_oid::regclass, reason;
END;
$$;

CREATE EVENT TRIGGER scan_view_trigger
  ON table_rewrite
  EXECUTE FUNCTION scan_view_trigger();

However this has two flaws: there is no pg_event_trigger_rewrite_subid() function to get the column attnum and it is too late for the trigger to modify system dependencies

ERROR:  cannot alter type of a column used by a view or rule
DETAIL:  rule _RETURN on view employee_v depends on column "name"

Other Databases

SQLite

Tables may be dropped; a runtime error is raised when selecting from the dependent view

  Parse error: no such table: __

Droping a referenced column is not allowed

Runtime error: error in view __ after drop column: no such column: __

Amazon Redshift

Redshift implements late binding views

CREATE VIEW ... WITH NO SCHEMA BINDING;

A record of column types is kept in a separate table

SELECT pg_get_late_binding_view_cols();

(public,myview,tag_id,integer,1)
(public,myview,name,"character varying(20)",2)
(public,myview,created,"timestamp with time zone",3)

OracleDB

Documentation seems to indicate that a view is stored as text, and then compiled. If a dependent table is changed the view is then rendered INVALID until refreshed.

Color Coding Numbers

Trying to compare OIDs visually is taxing. Assigning a color to each integer may prove to be handy

#!/usr/bin/awk -f

function hl(num) {
    if (!bins[num]) {
        if (!color || color > 231) { color = 182 }
        color = color + 1
        bins[num] = color
    }
    return "\033[38;5;" bins[num] "m" num "\033[0m"
}

BEGIN {
    FS="[ ]"  # split on each space
}

{
    for (i =1; i<=NF ; i++) {
        match($i, /^[0-9]{3,}$/)  # 3 digit numbers or larger
        if (RLENGTH > 0) { printf "%s ", hl($i) }
        else { printf "%s ", $i }
    }
    printf "\n"
}