Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23545

Add Trigger-View capability



    • Task
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • None
    • None
    • None


      This would be somewhat comparable to Oracle Database Continuous Query Notification, SQL Server waitfor/receive, and Postgresql Listen / Notify; but better.

      Instead of https://jira.mariadb.org/browse/MDEV-16590, this feature would provide a simpler and more developer-friendly SQL based API to be notified quickly when new records become available.

      The experience may be like the following

      select * from StreamView_Sales_Inserted where SaleAmount > 10000;

      Where an external process could use a client driver like this

      public async Task ProcessEventData(SqlCommand com, int cutIn)
      	//The following has already beed processed occurred:
          //$@"Select * from Sales where SaleAmount > 10000 and SaleDate >= '{cutIn.ToString("yyyy-MM-dd HH:mm:ss.fff")}' 
          com.CommandText = 
      		"select * from StreamView_Sales_Inserted where ID > cutIn and SaleAmount > 10000";
      	com.CommandTimeout = 0;
          while (true)
      			using (var reader = await com.ExecuteReaderAsync())
      				while (await reader.ReadAsync()) //Blocks if there are no rows
      					var sale = MaterializeSale(reader);
                                              cutIn = sale.ID;
      					await SendBigSaleEmail(sale);
      		catch (NetworkException nex)
      			//Some sort of disconnect
      		catch (TimeoutException tex)
      			//These shouldn't happen
      		catch (Exception Ex)
      			//Sort of unexpected
      			await Task.Delay(10000);

      I am not familiar with the source code for MySQL.

      Perhaps implementation could effectively be like a long-poll or cursor. I'm not sure if the internal mechanisms would require a complete overhaul though. At the highest database driver level, the client library is waiting for another network packet with more data for that resultset, so there should be no significant resources used up, except potentially for the resultset context on the serverside.

      System-defined Views would be available per table, or may be defined using new keywords like `WITH Trigger(Sales, Insert)`.

      Such Views may be joined to other tables, but MUST BE the first FROM-SOURCE of the View, and the only Trigger-View.

      Learning from SQL Server, perhaps these should be Queue-Views, but simpler, where triggers are enqueuing to active Views at the time; not requiring a broker.


        Issue Links



              Unassigned Unassigned
              merarischroeder Todd Hubers
              1 Vote for this issue
              3 Start watching this issue



                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.