Task #6054
closed
Explore the use of PostgreSQL views for easier event storing and querying
Added by Jan Mach about 5 years ago.
Updated almost 5 years ago.
Category:
Development - Core
Description
Based on quick research PostgreSQL views can in some conditions support inserts. Currently all data for IDEA events are stored within two tables, which requires two inserts and joining when searching. Usage of modifiable views could simplify both inserts and selects, because single insert statement could be used and select could omit joins.
- Related to Feature #4274: Minimize whole JSON IDEA events usage (jsonb column) added
- Related to Feature #4275: Split jsonb column into its own table added
- Assignee changed from Radko Krkoš to Jan Mach
- To be discussed changed from No to Yes
Unfortunately the conditions for automatically updatable views are not met in the discussed case. As documented in https://www.postgresql.org/docs/12/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS , such views can only select from a single table (so no JOINs
or UNIONs
).
The VIEW
can be made updatable anyway, using a TRIGGER
:
CREATE VIEW events_view AS SELECT ... FROM events INNER JOIN events_json on events.id = events_json.id;
CREATE FUNCTION events_insert_decompose() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO events VALUES (NEW.id, ...
INSERT INTO events_json VALUES (NEW.id, NEW.event);
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER events_insert
INSTEAD OF INSERT ON events_view
FOR EACH ROW
EXECUTE FUNCTION events_insert_decompose();
Radko Krkoš wrote:
Unfortunately the conditions for automatically updatable views are not met in the discussed case. As documented in https://www.postgresql.org/docs/12/sql-createview.html#SQL-CREATEVIEW-UPDATABLE-VIEWS , such views can only select from a single table (so no JOINs
or UNIONs
).
The VIEW
can be made updatable anyway, using a TRIGGER
:
[...]
And does it make sense then? Won't additional triggers add some barriering or whatever and hurt performance and/or error handling (comparing to just two naked inserts)?
Also seems that RULEs would be (harder but) less intrusive than TRIGGERs, as they rewrite the insert just once.
- Status changed from New to Rejected
After deep consideration we decided to abandon this approach.
- Target version changed from Backlog to Rejected
Also available in: Atom
PDF