Postgresql – way to make an OOo form for PostgreSQL updateable view

postgresqlview

I have a PostgreSQL database, with data from some 8 tables (not counting enums implemented as a table referenced by foreign key) summarized in a view. I made an INSTEAD OF trigger, so the view is in fact completely updateable. I connected the database to OpenOffice Base through jdbc so that I could make forms for it in OpenOffice. There is an option to connect a form to the view as though it was a table, but since the view has no primary key (it can't in PostgreSQL), the view is read-only.

Is there any way to make the form active for the view? Or do I have to decompose the form into one primary and seven sub-forms, so that I could use it to insert data? Or is there any other solution? Or is it not possible to make such a complex form in OOo Base at all?

Best Answer

According to this bug report, there is currently no way to insert data into updatable views through SDBC in LibreOffice Base (I assume it's the same about OpenOffice Base). I tested JDBC and ODBC as well, and no luck. There is no way how to point the Base to a (fake) primary key in the Base, and Base won't insert data into tables without primary key. The author of the report promised to solve this issue himself, but this project looks dead now.

However, the post pointed me to a workaround: when you add a serial-like column to the view (like ROW_NUMBER() OVER (ORDER BY table.id ASC) AS oid), you can assing it as the primary key while importing the view to MS Access through ODBC. This works with MS Access 2003 for me - the data can be inserted into database, with only minor bugs. Access is as good at making forms as OpenOffice base, so this is a good workaround for anyone who have the Access.

Related Solutions

PostgreSQL: reuse of complex intermediate result in same query

(Apologies for answering my own question, but after reading this unrelated question and answer, it occurred to me I should try using a CTE instead. It works.)

Here is another view, similar to testview1 in the question, but that uses a Common Table Expression:

CREATE VIEW testview2 AS
    WITH testcte AS (SELECT
       t1.id,
       t1.log_timestamp,
       (SELECT SUM(t2.col1) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='A') AS a,
       (SELECT SUM(t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='B') AS b,
       (SELECT SUM(t2.col1 - t2.col2) FROM test2 t2 WHERE t2.test1_id=t1.id AND category='C') AS c
      FROM test1 t1)
    SELECT * FROM testcte;

(This is just an example, I'm not suggesting that combining a view and a CTE are a necessarily a good idea: a CTE might be enough.)

Unlike testview1, the query plan for SELECT a FROM testview2 now also computes b and c, which were ignored since unused in testview1:

Subquery Scan testview2  (cost=395272.42..395535.25 rows=8761 width=8) (actual time=0.256..607.941 rows=8761 loops=1)
   ->  CTE Scan on testcte  (cost=395272.42..395447.64 rows=8761 width=36) (actual time=0.255..604.106 rows=8761 loops=1)
         CTE testcte
           ->  Seq Scan on test1 t1  (cost=0.00..395272.42 rows=8761 width=12) (actual time=0.252..589.358 rows=8761 loops=1)
                 SubPlan 1
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.021..0.021 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.015..0.015 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'A'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.009..0.009 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 2
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'B'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 3
                   ->  Aggregate  (cost=15.02..15.04 rows=1 width=8) (actual time=0.020..0.020 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=8) (actual time=0.013..0.014 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'C'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)

However, it doesn't re-compute the results that are used multiple times within the same query (which was the objective).

Unlike testview1 with which SELECT a, a, a, a, a took 5 times longer than SELECT a, here SELECT a, a, a, a, a, b, c, a+b, a+c, b+c FROM testview2 takes just as long as SELECT a FROM testview2 or SELECT a, b, c FROM testview2. It only goes through a, b and c once:

 Subquery Scan testview2  (cost=395272.42..395600.96 rows=8761 width=24) (actual time=0.147..562.790 rows=8761 loops=1)
   ->  CTE Scan on testcte  (cost=395272.42..395447.64 rows=8761 width=36) (actual time=0.144..554.194 rows=8761 loops=1)
         CTE testcte
           ->  Seq Scan on test1 t1  (cost=0.00..395272.42 rows=8761 width=12) (actual time=0.140..542.657 rows=8761 loops=1)
                 SubPlan 1
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.012..0.013 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'A'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 2
                   ->  Aggregate  (cost=15.02..15.03 rows=1 width=4) (actual time=0.019..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'B'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.006..0.006 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)
                 SubPlan 3
                   ->  Aggregate  (cost=15.02..15.04 rows=1 width=8) (actual time=0.018..0.019 rows=1 loops=8761)
                         ->  Bitmap Heap Scan on test2 t2  (cost=4.28..15.02 rows=1 width=8) (actual time=0.012..0.012 rows=0 loops=8761)
                               Recheck Cond: (test1_id = $0)
                               Filter: ((category)::text = 'C'::text)
                               ->  Bitmap Index Scan on test_if_idx  (cost=0.00..4.28 rows=3 width=0) (actual time=0.007..0.007 rows=3 loops=8761)
                                     Index Cond: (test1_id = $0)

Postgresql – Optimising query on view that merges similar tables with a clear discriminator

There are a few problems with your query. It is quite clear that your 'wrapper' view - while looks like an elegant solution at first - kills the performance involving 7.7M completely unnecessary rows. This is because UNION requires all this data being sorted, and since those don't fit into memory (you can see this from Sort Method: external merge Disk: 300792kB), it gets 'swapped' to disk and sorted there, which is a very slow process.

As a first attempt, try recreating the 'wrapper' view with UNION ALL instead of simple UNION (you can find the difference here - note that in order to make the rows distinct, Postgres has to sort them first); this way you may avoid the sort. If the result is not good enough, try joining the five tables on the 'main' query one by one and UNION ALL the results.

Best Answer

Related Solutions

PostgreSQL: reuse of complex intermediate result in same query

Postgresql – Optimising query on view that merges similar tables with a clear discriminator

Related Question