Sessions keeping metadata in cache/missing DML operations.

[Guy Groulx]

sqlci and dcs sessions are caching metadata and not refreshing when dml operations are being performed.

Using 2 sqlci sessions.

SESSION 1
    /home/squser2> sqlci
    Trafodion Conversational Interface 0.8.0
    (c) Copyright 2014 Hewlett-Packard Development Company, LP.
    >>SET SCHEMA TRAFODION.TESTSCH;
    --- SQL operation complete.
   >>CREATE TABLE TESTTABLE (
  >>+ FIELD1 CHAR(10) NOT NULL
  >>+ , FIELD2 CHAR(15) NOT NULL
  >>+ , PRIMARY KEY (FIELD1 ASC)
  >>+ );
  --- SQL operation complete.
  >>invoke testtable;

  -- Definition of Trafodion table TRAFODION.TESTSCH.TESTTABLE
  -- Definition current Thu Jun 12 15:07:45 2014
  (
    FIELD1 CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FIELD2 CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (FIELD1 ASC)

  --- SQL operation complete.

SESSION 2 in a different window. Keep session 1's window opened.
    /home/squser2> sqlci
    Trafodion Conversational Interface 0.8.0
    (c) Copyright 2014 Hewlett-Packard Development Company, LP.
    >>SET SCHEMA TRAFODION.TESTSCH;

    --- SQL operation complete.
   >>get tables;

    Tables in Schema TRAFODION.TESTSCH
   ==================================

    TESTTABLE

   --- SQL operation complete.
   >>invoke testtable;

   -- Definition of Trafodion table TRAFODION.TESTSCH.TESTTABLE
   -- Definition current Thu Jun 12 15:07:38 2014

  (
    FIELD1 CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FIELD2 CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (FIELD1 ASC)

   --- SQL operation complete.

NOW BACK TO SESSION 1. Keep session 2 window opened.
    >>DROP TABLE TESTTABLE;
  -- SQL operation complete.
  >> CREATE TABLE TESTTABLE (
  >>+ FIELD1 CHAR(10) NOT NULL
  >>+ , FIELD2 CHAR(15) NOT NULL
  >>+ , FIELD3 CHAR(10) NOT NULL
  >>+ , FIELD4 CHAR(15) NOT NULL
  >>+, PRIMARY KEY (FIELD1 ASC)
  >>+ );

  --- SQL operation complete.
  >>invoke testtable;

  -- Definition of Trafodion table TRAFODION.TESTSCH.TESTTABLE
  -- Definition current Thu Jun 12 15:08:06 2014

  (
    FIELD1 CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FIELD2 CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FIELD3 CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FIELD4 CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (FIELD1 ASC)

--- SQL operation complete.
>>

BUT BACK ON SESSION 2:
>>invoke testtable;

-- Definition of Trafodion table TRAFODION.TESTSCH.TESTTABLE
-- Definition current Thu Jun 12 15:08:15 2014

  (
    FIELD1 CHAR(10) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  , FIELD2 CHAR(15) CHARACTER SET ISO88591 COLLATE
      DEFAULT NO DEFAULT NOT NULL NOT DROPPABLE
  )
  PRIMARY KEY (FIELD1 ASC)

--- SQL operation complete.
>>

Notice how session 2 still has the old form or table.

[Anoop Sharma (anoop-sharma)]

This is the issue of metadata invalidation that we do not currently have in Trafodion.
A metadata change from one session does not trigger other sessions to detect
that a change has happened.
This is being looked at and we may use Query Invalidation infrastructure to implement it.
Assigning to Mike Hanlon who is looking at this.

[Trafodion-Gerrit (neo-devtools)]

Fix proposed to branch: master
Review: https://review.trafodion.org/693

[Trafodion-Gerrit (neo-devtools)]

Reviewed: https://review.trafodion.org/693
Committed: https://github.com/trafodion/core/commit/1bd19cba4b2c4d43fd78a5a56172ad8bcb153e73
Submitter: Trafodion Jenkins
Branch: master

commit 1bd19cba4b2c4d43fd78a5a56172ad8bcb153e73
Author: Mike Hanlon <email address hidden>
Date: Fri Nov 14 17:44:16 2014 +0000

    Query Invalidation triggered by DDL, phase 1

    This first check-in implements most of the framework which will
    be used to complete the QI DDL feature. It redefines the old
    security invalidation key (SQL_SIKEY) to handle DDL operations in
    addition to REVOKE. In a limited number of DDL operations, the object
    UIDs of affected Seabase objects are propagated to all nodes for
    use by the compiler to invalidate NATable cache entries, as
    well as a limited number of types of cached queries. Later this
    month, the framework will be complete by allowing prepared queries
    that have already been returned from the compiler to be invalidated.
    Then the next step for the framework will be support for invalidating
    the HTable cache. Finally an effort will be made to cover all of
    the necessary DDL operations and all types of cached queries.

    The check-in include a new regression test (executor/TEST122) that
    demonstrates the cases that are covered. Specifically, a table will
    be dropped and recreated with the same name but different definition
    in one sqlci session. In another session, which has already populated
    NATable cache and query cache for INSERT, UPDATE, DELETE, SELECT,
    SELECT COUNT(*), INVOKE and SHOWDDL statements, those some types
    of statements will be resubmitted and correctly compiled.

    Change-Id: Ie61ce751089b57ce1894f1764c338e9400bb7b8a
    Closes-Bug: #1329358
    Implements: blueprint ddl-query-invalidation

[Guy Groulx (guy-groulx)]

Confirmed with git150112. Now works properly, second session sees changes from first session.