Having Updatable View

Help Procedural SQL
1

We want to replicate our SingleStore database using SingleStore internal replicate database feature only. The issue we have is that there are few tables that are very volatile where data are being inserted, updated and deleted very frequently using stored procedures. These tables are being used as scratch pads. The data is valid for the specific session and for the local cluster only. We want to avoid replicating them to another cluster for this obvious reason. Other tables are mostly read only. We have done our performance test using temp tables that is very slow compared to using these staging tables.

The solution we were thinking is to move these volatile tables into another staging database and create views that are pointing to these tables. Also we make sure the view names in the ODS database remains the same as the table names in staging database and then recreate all the SPs in ODS DB. This is to minimize other changes. This will not be possible as SingleStore currently does not have updatable view.

Currently we have done our replication test using replicate software for selective table replication. For this we need an extra hardware for replicate to run and also sometimes encountering issues.

Do we already have a feature request for updatable views?

2

Your approach of putting those volatile tables in a separate database makes good sense. The downside is that you can’t do collocated joins with those tables to tables in other databases.

Support for view updates can’t be universal because some view updates don’t map to a unique set of updates to the underlying tables. E.g. certain aggregate and join views are not updatable in a well-defined, unique fashion.

Can you give examples of the kind of views you want to update?

I have opened an internal feature request “support view updates” to track this.

1 Like
3

This is typically what we are doing on the volatile tables thru SP. For example the table name is VOLTAB01.

  1. INSERT into VOLTAB01
    SELECT ID = connection_id, Table_Data
    FROM bunch of tables that may have joins;

  2. SELECT data from VOLTAB01;

  3. DELETE from VOLTAB01 WHERE ID = connection_id;

The above SP is executed from hundreds of connections and the typical execution time is about 100ms.

So, the views I am trying to create are not multi-Table views. INSERT,UPDATE & DELETE may not be allowed on multi-table views. Generally there are more restrictions on multi-table views.

1 Like