Re: Immutable function WAY slower than Stable function? - Mailing list pgsql-general

CREATE OR REPLACE FUNCTION target_date() RETURNS date AS $$       SELECT target_date FROM target_date_current;
$$ LANGUAGE sql STABLE;

CREATE TABLE tbl_target_date (   target_date_id          SERIAL PRIMARY KEY,   target_date         DATE NOT NULL,   effective_at        TIMESTAMP NOT NULL DEFAULT current_timestamp,   comment             TEXT,   --system fields   added_by            INTEGER NOT NULL REFERENCES tbl_staff (staff_id),   added_at            TIMESTAMP(0)     NOT NULL DEFAULT CURRENT_TIMESTAMP,   changed_by          INTEGER NOT NULL  REFERENCES tbl_staff (staff_id),   changed_at          TIMESTAMP(0)     NOT NULL DEFAULT CURRENT_TIMESTAMP,   is_deleted          BOOLEAN NOT NULL DEFAULT FALSE,   deleted_at          TIMESTAMP(0),   deleted_by          INTEGER REFERENCES tbl_staff(staff_id),   deleted_comment     TEXT,   sys_log         TEXT
);

CREATE VIEW target_date AS SELECT * FROM tbl_target_date WHERE NOT is_deleted;

CREATE VIEW target_date_current AS   SELECT * FROM target_date ORDER BY effective_at DESC LIMIT 1;

CREATE OR REPLACE FUNCTION target_date_no_edit_or_delete() RETURNS TRIGGER AS $$
   BEGIN   IF (TG_OP <> 'INSERT')   THEN RAISE EXCEPTION 'Target records cannot be changed or deleted.  (Attempted operation: %)',TG_OP;   END IF;   IF (NEW.target_date <> date_trunc('month',NEW.target_date))   THEN RAISE EXCEPTION 'Target date must be the first of a month';   END IF;   IF (NEW.target_date <= target_date())   THEN RAISE EXCEPTION 'Target date can only be moved forward';   END IF;   RETURN NEW;   END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER protect_target_date BEFORE INSERT OR UPDATE OR DELETE ON tbl_target_date FOR EACH ROW EXECUTE PROCEDURE target_date_no_edit_or_delete();
CREATE TRIGGER target_date_no_trunacte BEFORE TRUNCATE ON tbl_target_date FOR STATEMENT EXECUTE PROCEDURE target_date_no_edit_or_delete();

spc=> \d tbl_residence_own                                                      Table "public.tbl_residence_own"           Column            |              Type              |                                  Modifiers
------------------------------+--------------------------------+------------------------------------------------------------------------------residence_own_id             | integer                        | not null default nextval('tbl_residence_own_residence_own_id_seq'::regclass)client_id                    | integer                        | not nullhousing_project_code         | character varying(10)          | not nullhousing_unit_code            | character varying(10)          | not nullresidence_date               | date                           | not nullresidence_date_end           | date                           |unit_rent_manual             | numeric(7,2)                   |utility_allowance_manual     | numeric(7,2)                   |is_active_manual             | boolean                        | not null default truewas_received_hap             | boolean                        |was_received_compliance      | boolean                        |moved_from_code              | character varying(10)          |chronic_homeless_status_code | character varying(10)          |lease_on_file                | boolean                        |moved_to_code                | character varying(10)          |departure_type_code          | character varying(10)          |departure_reason_code        | character varying(10)          |move_out_was_code            | character varying(10)          |returned_homeless            | boolean                        |was_deposit_returned         | boolean                        |comment_damage               | text                           |comment_deposit              | text                           |comment                      | text                           |old_access_id                | character varying              |old_utility_allowance        | numeric(9,2)                   |added_by                     | integer                        | not nulladded_at                     | timestamp(0) without time zone | not null default now()changed_by                   | integer                        | not nullchanged_at                   | timestamp(0) without time zone | not null default now()is_deleted                   | boolean                        | default falsedeleted_at                   | timestamp(0) without time zone |deleted_by                   | integer                        |deleted_comment              | text                           |sys_log                      | text                           |tenant_pays_deposit          | boolean                        | not null default falseis_coordinated_entry         | boolean                        |referral_source              | text                           |
Indexes:   "tbl_residence_own_pkey" PRIMARY KEY, btree (residence_own_id)   "tbl_residence_own_client_id" btree (client_id)   "tbl_residence_own_housing_project_code" btree (housing_project_code)   "tbl_residence_own_housing_unit_code" btree (housing_unit_code)   "tbl_residence_own_is_deleted" btree (is_deleted)   "tbl_residence_own_residence_date" btree (residence_date)   "tbl_residence_own_residence_date_end" btree (residence_date_end)
Check constraints:   "coordinated_entry_or_other" CHECK (xor(is_coordinated_entry, referral_source IS NOT NULL))   "date_sanity" CHECK (residence_date_end IS NULL OR residence_date <= residence_date_end)
Foreign-key constraints:   "tbl_residence_own_added_by_fkey" FOREIGN KEY (added_by) REFERENCES tbl_staff(staff_id)   "tbl_residence_own_changed_by_fkey" FOREIGN KEY (changed_by) REFERENCES tbl_staff(staff_id)   "tbl_residence_own_chronic_homeless_status_code_fkey" FOREIGN KEY (chronic_homeless_status_code) REFERENCES tbl_l_chronic_homeless_status(chronic_homeless_status_code)   "tbl_residence_own_client_id_fkey" FOREIGN KEY (client_id) REFERENCES tbl_client(client_id)   "tbl_residence_own_deleted_by_fkey" FOREIGN KEY (deleted_by) REFERENCES tbl_staff(staff_id)   "tbl_residence_own_departure_reason_code_fkey" FOREIGN KEY (departure_reason_code) REFERENCES tbl_l_departure_reason(departure_reason_code)   "tbl_residence_own_departure_type_code_fkey" FOREIGN KEY (departure_type_code) REFERENCES tbl_l_departure_type(departure_type_code)   "tbl_residence_own_housing_project_code_fkey" FOREIGN KEY (housing_project_code) REFERENCES tbl_l_housing_project(housing_project_code)   "tbl_residence_own_housing_unit_code_fkey" FOREIGN KEY (housing_unit_code) REFERENCES tbl_housing_unit(housing_unit_code)   "tbl_residence_own_move_out_was_code_fkey" FOREIGN KEY (move_out_was_code) REFERENCES tbl_l_exit_status(exit_status_code)   "tbl_residence_own_moved_from_code_fkey" FOREIGN KEY (moved_from_code) REFERENCES tbl_l_facility(facility_code)   "tbl_residence_own_moved_to_code_fkey" FOREIGN KEY (moved_to_code) REFERENCES tbl_l_facility(facility_code)
Triggers:   check_max_occupant AFTER INSERT OR UPDATE ON tbl_residence_own FOR EACH ROW EXECUTE PROCEDURE enforce_max_occupant()   tbl_residence_own_log_chg AFTER INSERT OR DELETE OR UPDATE ON tbl_residence_own FOR EACH ROW EXECUTE PROCEDURE table_log()   tbl_residence_own_no_unit_or_project_change BEFORE UPDATE ON tbl_residence_own FOR EACH ROW EXECUTE PROCEDURE tbl_residence_own_validate_modify()