Sessions keeping metadata in cache/missing DML operations.

Bug #1329358 reported by Guy Groulx on 2014-06-12
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Trafodion
Fix Released
High
Mike Hanlon

Bug Description

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) wrote :

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.

Changed in trafodion:
assignee: nobody → Mike Hanlon (mike-hanlon)
Changed in trafodion:
status: New → Confirmed
Changed in trafodion:
milestone: none → r0.8
milestone: r0.8 → r0.9
Changed in trafodion:
status: Confirmed → In Progress

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

Changed in trafodion:
status: In Progress → Fix Committed
Guy Groulx (guy-groulx) wrote :

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

Changed in trafodion:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers