Re: Immutable function WAY slower than Stable function?

On Mon, Aug 6, 2018 at 4:36 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

>
> What is the definition for target_date()?
>

Hi Adrian. Happy to provide this info. Though on a side note, I don't
understand why it should matter, if functions are black box optimization
fences. But here are the definitions:

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

The target_date table and views:

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();

And the tbl_residence_own, which was referenced in my sample queries:

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 null
housing_project_code | character varying(10) | not null
housing_unit_code | character varying(10) | not null
residence_date | date | not null
residence_date_end | date |
unit_rent_manual | numeric(7,2) |
utility_allowance_manual | numeric(7,2) |
is_active_manual | boolean | not
null default true
was_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 null
added_at | timestamp(0) without time zone | not
null default now()
changed_by | integer | not null
changed_at | timestamp(0) without time zone | not
null default now()
is_deleted | boolean | default false
deleted_at | timestamp(0) without time zone |
deleted_by | integer |
deleted_comment | text |
sys_log | text |
tenant_pays_deposit | boolean | not
null default false
is_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()

Let me know if I can provide more info. Thanks!

Ken

--
AGENCY Software
A Free Software data system
By and for non-profits
*http://agency-software.org/ <http://agency-software.org/>*
*https://demo.agency-software.org/client
<https://demo.agency-software.org/client>*
ken(dot)tanzer(at)agency-software(dot)org
(253) 245-3801

Subscribe to the mailing list
<agency-general-request(at)lists(dot)sourceforge(dot)net?body=subscribe> to
learn more about AGENCY or
follow the discussion.