Task #6054
closedExplore the use of PostgreSQL views for easier event storing and querying
0%
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 issues
Updated by Jan Mach almost 5 years ago
- Related to Feature #4274: Minimize whole JSON IDEA events usage (jsonb column) added
Updated by Jan Mach almost 5 years ago
- Related to Feature #4275: Split jsonb column into its own table added
Updated by Radko Krkoš almost 5 years ago
- 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();
Updated by Pavel Kácha almost 5 years ago
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
orUNIONs
).
TheVIEW
can be made updatable anyway, using aTRIGGER
:
[...]
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.
Updated by Jan Mach almost 5 years ago
- Status changed from New to Rejected
After deep consideration we decided to abandon this approach.
Updated by Jan Mach almost 5 years ago
- Target version changed from Backlog to Rejected