Project

General

Profile

Actions

Task #6054

closed

Explore the use of PostgreSQL views for easier event storing and querying

Added by Jan Mach almost 2 years ago. Updated almost 2 years ago.

Status:
Rejected
Priority:
Normal
Assignee:
Category:
Development - Core
Target version:
Start date:
11/12/2019
Due date:
% Done:

0%

Estimated time:
To be discussed:
Yes

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

Related to Mentat - Feature #4274: Minimize whole JSON IDEA events usage (jsonb column)ClosedRadko Krkoš08/22/201808/22/2018

Actions
Related to Mentat - Feature #4275: Split jsonb column into its own tableClosedPavel Kácha

Actions
Actions #1

Updated by Jan Mach almost 2 years ago

  • Related to Feature #4274: Minimize whole JSON IDEA events usage (jsonb column) added
Actions #2

Updated by Jan Mach almost 2 years ago

  • Related to Feature #4275: Split jsonb column into its own table added
Actions #3

Updated by Radko Krkoš almost 2 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();

Actions #4

Updated by Pavel Kácha almost 2 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 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.

Actions #5

Updated by Jan Mach almost 2 years ago

  • Status changed from New to Rejected

After deep consideration we decided to abandon this approach.

Actions #6

Updated by Jan Mach almost 2 years ago

  • Target version changed from Backlog to Rejected
Actions

Also available in: Atom PDF