/ Check-in [ecf6251e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Fix a problem with eponymous virtual tables and SHARED_SCHEMA databases. Also, after preparing statements that require all database schemas (REINDEX, ANALYZE, CREATE, DROP and some PRAGMA statements), do not allow the database connection to return more than one schema to each schema-pool.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | reuse-schema
Files: files | file ages | folders
SHA3-256:ecf6251ec0bb745a4ef9bad9f9ecd3babb687a3806fd96554b265313625270c5
User & Date: dan 2019-02-15 19:00:41
Wiki:reuse-schema
Context
2019-02-15
19:36
Enhance the virtual table in test_schemapool.c so that it can be used to check that SHARED_SCHEMA connections are not allocating and freeing schemas when they should not be. check-in: cb236cb9 user: dan tags: reuse-schema
19:00
Fix a problem with eponymous virtual tables and SHARED_SCHEMA databases. Also, after preparing statements that require all database schemas (REINDEX, ANALYZE, CREATE, DROP and some PRAGMA statements), do not allow the database connection to return more than one schema to each schema-pool. check-in: ecf6251e user: dan tags: reuse-schema
11:54
Revert the rearrangement of VDBE code in [219b39e14] so that vdbe.c matches trunk. Since the new call to sqlite3Init() in OP_ParseSchema was removed, the rearrangement no longer provides any performance advantage. check-in: 03c4f003 user: dan tags: reuse-schema
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to src/attach.c.

   228    228     /* If the file was opened successfully, read the schema for the new database.
   229    229     ** If this fails, or if opening the file failed, then close the file and 
   230    230     ** remove the entry from the db->aDb[] array. i.e. put everything back the
   231    231     ** way we found it.
   232    232     */
   233    233     if( rc==SQLITE_OK ){
   234    234       db->init.iDb = 0;
   235         -    db->mDbFlags &= ~(DBFLAG_SchemaKnownOk);
   236    235       if( !IsReuseSchema(db) ){
          236  +      db->mDbFlags &= ~(DBFLAG_SchemaKnownOk);
   237    237         sqlite3BtreeEnterAll(db);
   238    238         rc = sqlite3Init(db, &zErrDyn);
   239    239         sqlite3BtreeLeaveAll(db);
   240    240         assert( zErrDyn==0 || rc!=SQLITE_OK );
   241    241       }
   242    242     }
   243    243   #ifdef SQLITE_USER_AUTHENTICATION

Changes to src/build.c.

   396    396         Module *pMod = (Module*)sqlite3HashFind(&db->aModule, zName);
   397    397         if( pMod==0 && sqlite3_strnicmp(zName, "pragma_", 7)==0 ){
   398    398           pMod = sqlite3PragmaVtabRegister(db, zName);
   399    399         }
   400    400         if( pMod ){
   401    401           sqlite3SchemaLoad(db, 0);
   402    402           if( sqlite3VtabEponymousTableInit(pParse, pMod) ){
   403         -          return pMod->pEpoTab;
          403  +          Table *pEpoTab = pMod->pEpoTab;
          404  +          assert( IsReuseSchema(db) || pEpoTab->pSchema==db->aDb[0].pSchema );
          405  +          pEpoTab->pSchema = db->aDb[0].pSchema;  /* For SHARED_SCHEMA mode */
          406  +          return pEpoTab;
   404    407           }
   405    408         }
   406    409       }
   407    410   #endif
   408    411       if( flags & LOCATE_NOERR ) return 0;
   409    412       pParse->checkSchema = 1;
   410    413     }else if( IsVirtual(p) && pParse->disableVtab ){

Changes to src/callback.c.

   599    599   ** When this function is called, the database connection Db must be
   600    600   ** using a schema-pool (Db.pSPool!=0) and must currently have Db.pSchema
   601    601   ** set to point to a populated schema object checked out from the 
   602    602   ** schema-pool. It is also assumed that the STATIC_MASTER mutex is held.
   603    603   ** This function returns the Schema object to the schema-pool and sets
   604    604   ** Db.pSchema to point to the schema-pool's static, empty, Schema object.
   605    605   */
   606         -static void schemaRelease(Db *pDb){
   607         -  assert( pDb->pSPool && pDb->pSchema );
   608         -  assert( pDb->pSchema->schemaFlags & DB_SchemaLoaded );
          606  +static void schemaRelease(sqlite3 *db, Db *pDb){
          607  +  Schema *pRelease = pDb->pSchema;
          608  +  SchemaPool *pSPool = pDb->pSPool;
          609  +
          610  +  pDb->pSchema = &pSPool->sSchema;
          611  +
          612  +  assert( pDb->pSPool && pRelease );
          613  +  assert( pRelease->schemaFlags & DB_SchemaLoaded );
          614  +  assert( (pDb->pSchema->schemaFlags & DB_SchemaLoaded)==0 );
   609    615     assert( sqlite3_mutex_held(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER)) );
   610    616   
   611         -  pDb->pSchema->pNext = pDb->pSPool->pSchema;
   612         -  pDb->pSPool->pSchema = pDb->pSchema;
   613         -  pDb->pSchema = &pDb->pSPool->sSchema;
          617  +  /* If the DBFLAG_FreeSchema flag is set and the database connection holds
          618  +  ** at least one other copy of the schema being released, delete it instead
          619  +  ** of returning it to the schema-pool.  */
          620  +  if( db->mDbFlags & DBFLAG_FreeSchema ){
          621  +    int i;
          622  +    for(i=0; i<db->nDb; i++){
          623  +      Db *p = &db->aDb[i];
          624  +      if( p!=pDb && p->pSchema!=&pSPool->sSchema && pDb->pSPool==p->pSPool ){
          625  +        schemaDelete(pRelease);
          626  +        return;
          627  +      }
          628  +    }
          629  +  }
   614    630   
   615         -  assert( (pDb->pSchema->schemaFlags & DB_SchemaLoaded)==0 );
          631  +  pRelease->pNext = pDb->pSPool->pSchema;
          632  +  pDb->pSPool->pSchema = pRelease;
   616    633   }
   617    634   
   618    635   /*
   619    636   ** The schema for database iDb of database handle db, which was opened
   620    637   ** with SQLITE_OPEN_SHARED_SCHEMA, has just been parsed. This function either
   621    638   ** finds a matching SchemaPool object on the global list (schemaPoolList) or
   622    639   ** else allocates a new one and sets the Db.pSPool variable accordingly.
................................................................................
   706    723         for(p=pDb->pVTable; p; p=pNext){
   707    724           pNext = p->pNext;
   708    725           sqlite3VtabUnlock(p);
   709    726         }
   710    727         pDb->pVTable = 0;
   711    728         sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
   712    729         if( DbHasProperty(db, iDb, DB_SchemaLoaded) ){
   713         -        schemaRelease(pDb);
          730  +        schemaRelease(db, pDb);
   714    731         }
   715    732         if( bNew ){
   716    733           Schema *pNew = sqlite3SchemaGet(db, 0);
   717    734           if( pNew==0 ){
   718    735             rc = SQLITE_NOMEM;
   719    736           }else{
   720    737             pDb->pSchema = pNew;
................................................................................
   770    787     int i;
   771    788     assert_schema_state_ok(db);
   772    789     sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
   773    790     for(i=0; i<db->nDb; i++){
   774    791       if( i!=1 ){
   775    792         Db *pDb = &db->aDb[i];
   776    793         if( pDb->pSPool && DbHasProperty(db,i,DB_SchemaLoaded) ){
   777         -        schemaRelease(pDb);
          794  +        schemaRelease(db, pDb);
   778    795         }
   779    796       }
   780    797     }
          798  +  db->flags &= ~DBFLAG_FreeSchema;
   781    799     sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
   782    800   }
   783    801   
   784    802   /*
   785    803   ** Release any sharable schema held by connection iDb of database handle
   786    804   ** db. Db.pSchema is left pointing to the static, empty, Schema object
   787    805   ** owned by the schema-pool.
................................................................................
   788    806   */
   789    807   void sqlite3SchemaRelease(sqlite3 *db, int iDb){
   790    808     Db *pDb = &db->aDb[iDb];
   791    809     assert( iDb!=1 );
   792    810     assert_schema_state_ok(db);
   793    811     sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
   794    812     if( pDb->pSPool && DbHasProperty(db, iDb, DB_SchemaLoaded) ){
   795         -    schemaRelease(pDb);
          813  +    schemaRelease(db, pDb);
   796    814     }
   797    815     sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
   798    816   }
   799    817   
   800    818   /*
   801    819   ** In most cases, this function finds and returns the schema associated 
   802    820   ** with BTree handle pBt, creating a new one if necessary. However, if

Changes to src/prepare.c.

   477    477   ** Otherwise, the schema is loaded. An error code is returned.
   478    478   */
   479    479   int sqlite3ReadSchema(Parse *pParse){
   480    480     int rc = SQLITE_OK;
   481    481     sqlite3 *db = pParse->db;
   482    482     assert( sqlite3_mutex_held(db->mutex) );
   483    483     if( !db->init.busy ){
          484  +    db->mDbFlags |= DBFLAG_FreeSchema;      /* For sharable-schema mode */
   484    485       rc = sqlite3Init(db, &pParse->zErrMsg);
   485    486       if( rc!=SQLITE_OK ){
   486    487         pParse->rc = rc;
   487    488         pParse->nErr++;
   488    489       }else if( db->noSharedCache && !IsReuseSchema(db) ){
   489    490         db->mDbFlags |= DBFLAG_SchemaKnownOk;
   490    491       }

Changes to src/sqliteInt.h.

  1569   1569   ** Allowed values for sqlite3.mDbFlags
  1570   1570   */
  1571   1571   #define DBFLAG_SchemaChange   0x0001  /* Uncommitted Hash table changes */
  1572   1572   #define DBFLAG_PreferBuiltin  0x0002  /* Preference to built-in funcs */
  1573   1573   #define DBFLAG_Vacuum         0x0004  /* Currently in a VACUUM */
  1574   1574   #define DBFLAG_SchemaKnownOk  0x0008  /* Schema is known to be valid */
  1575   1575   
  1576         -#define DBFLAG_SchemaInuse    0x0010  /* Do not free schemas */
         1576  +#define DBFLAG_SchemaInuse    0x0010  /* Do not release sharable schemas */
         1577  +#define DBFLAG_FreeSchema     0x0020  /* Free extra shared schemas on release */
  1577   1578   
  1578   1579   /*
  1579   1580   ** Bits of the sqlite3.dbOptFlags field that are used by the
  1580   1581   ** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
  1581   1582   ** selectively disable various optimizations.
  1582   1583   */
  1583   1584   #define SQLITE_QueryFlattener 0x0001   /* Query flattening */

Changes to test/reuse2.test.

   294    294     sqlite3_table_column_metadata db2 main bbb b
   295    295   } {{} BINARY 0 0 0}
   296    296   
   297    297   do_execsql_test -db db2 5.2.3 {
   298    298     SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
   299    299   } {nref=6 nschema=1}
   300    300   
          301  +breakpoint
          302  +do_execsql_test -db db2 5.2.4 {
          303  +  PRAGMA integrity_check;
          304  +  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
          305  +} {ok nref=6 nschema=1}
          306  +
   301    307   finish_test
   302    308   

Changes to test/reuse3.test.

   105    105     set N [lindex [sqlite3_db_status db1 SCHEMA_USED 0] 1]
   106    106     expr $N==$N
   107    107   } 1
   108    108   
   109    109   sqlite3 db2 test.db -shared-schema 1
   110    110   do_test 3.2 {
   111    111     execsql { SELECT * FROM x1 } db2
   112         -  breakpoint
   113    112     set N2 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
   114    113     expr $N2>($N/2) && $N2<($N/2)+400
   115    114   } 1
   116    115   
   117    116   sqlite3 db3 test.db -shared-schema 1
   118    117   sqlite3 db4 test.db -shared-schema 1
   119    118   do_test 3.3 {
................................................................................
   120    119     execsql { SELECT * FROM x1 } db3
   121    120     execsql { SELECT * FROM x1 } db4
   122    121     set N4 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
   123    122     set M [expr 2*($N-$N2)]
   124    123     expr {$N4 == (($M / 4) + $N-$M)}
   125    124   } 1
   126    125   
          126  +catch { db1 close }
          127  +catch { db2 close }
          128  +catch { db3 close }
          129  +catch { db4 close }
          130  +
          131  +#-------------------------------------------------------------------------
          132  +# Test the REINDEX command.
          133  +reset_db
          134  +do_execsql_test 4.1.0 {
          135  +  CREATE TABLE x1(a, b, c);
          136  +  CREATE INDEX x1a ON x1(a);
          137  +  CREATE INDEX x1b ON x1(b);
          138  +  CREATE INDEX x1c ON x1(c);
          139  +}
          140  +db close
          141  +sqlite3 db test.db -shared-schema 1
          142  +
          143  +do_execsql_test 4.1.1 {
          144  +  REINDEX x1;
          145  +  REINDEX x1a;
          146  +  REINDEX x1b;
          147  +  REINDEX x1c;
          148  +  REINDEX;
          149  +}
          150  +
          151  +do_test 4.1.2 {
          152  +  for {set i 1} {$i < 5} {incr i} {
          153  +    forcedelete test.db${i} test.db${i}-wal test.db${i}-journal
          154  +    forcecopy test.db test.db${i}
          155  +    execsql "ATTACH 'test.db${i}' AS db${i}"
          156  +  }
          157  +  register_schemapool_module db
          158  +  set {} {}
          159  +  execsql { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool }
          160  +} {nref=5 nschema=1}
          161  +
          162  +do_execsql_test 4.1.3 {
          163  +  REINDEX x1;
          164  +  REINDEX x1a;
          165  +  REINDEX x1b;
          166  +  REINDEX x1c;
          167  +  REINDEX db1.x1a;
          168  +  REINDEX db2.x1b;
          169  +  REINDEX db3.x1c;
          170  +  REINDEX;
          171  +}
          172  +
          173  +do_execsql_test 4.1.4 {
          174  +  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
          175  +} {nref=5 nschema=1}
          176  +
   127    177   finish_test
   128    178