/ 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 Unified Diffs Ignore Whitespace Patch

Changes to src/attach.c.

228
229
230
231
232
233
234
235
236

237
238
239
240
241
242
243
  /* If the file was opened successfully, read the schema for the new database.
  ** If this fails, or if opening the file failed, then close the file and 
  ** remove the entry from the db->aDb[] array. i.e. put everything back the
  ** way we found it.
  */
  if( rc==SQLITE_OK ){
    db->init.iDb = 0;
    db->mDbFlags &= ~(DBFLAG_SchemaKnownOk);
    if( !IsReuseSchema(db) ){

      sqlite3BtreeEnterAll(db);
      rc = sqlite3Init(db, &zErrDyn);
      sqlite3BtreeLeaveAll(db);
      assert( zErrDyn==0 || rc!=SQLITE_OK );
    }
  }
#ifdef SQLITE_USER_AUTHENTICATION







<

>







228
229
230
231
232
233
234

235
236
237
238
239
240
241
242
243
  /* If the file was opened successfully, read the schema for the new database.
  ** If this fails, or if opening the file failed, then close the file and 
  ** remove the entry from the db->aDb[] array. i.e. put everything back the
  ** way we found it.
  */
  if( rc==SQLITE_OK ){
    db->init.iDb = 0;

    if( !IsReuseSchema(db) ){
      db->mDbFlags &= ~(DBFLAG_SchemaKnownOk);
      sqlite3BtreeEnterAll(db);
      rc = sqlite3Init(db, &zErrDyn);
      sqlite3BtreeLeaveAll(db);
      assert( zErrDyn==0 || rc!=SQLITE_OK );
    }
  }
#ifdef SQLITE_USER_AUTHENTICATION

Changes to src/build.c.

396
397
398
399
400
401
402
403



404
405
406
407
408
409
410
      Module *pMod = (Module*)sqlite3HashFind(&db->aModule, zName);
      if( pMod==0 && sqlite3_strnicmp(zName, "pragma_", 7)==0 ){
        pMod = sqlite3PragmaVtabRegister(db, zName);
      }
      if( pMod ){
        sqlite3SchemaLoad(db, 0);
        if( sqlite3VtabEponymousTableInit(pParse, pMod) ){
          return pMod->pEpoTab;



        }
      }
    }
#endif
    if( flags & LOCATE_NOERR ) return 0;
    pParse->checkSchema = 1;
  }else if( IsVirtual(p) && pParse->disableVtab ){







|
>
>
>







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

Changes to src/callback.c.

599
600
601
602
603
604
605
606





607

608
609
610














611
612
613
614
615
616
617
618
619
620
621
622
...
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
...
770
771
772
773
774
775
776
777
778
779
780

781
782
783
784
785
786
787
...
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
** When this function is called, the database connection Db must be
** using a schema-pool (Db.pSPool!=0) and must currently have Db.pSchema
** set to point to a populated schema object checked out from the 
** schema-pool. It is also assumed that the STATIC_MASTER mutex is held.
** This function returns the Schema object to the schema-pool and sets
** Db.pSchema to point to the schema-pool's static, empty, Schema object.
*/
static void schemaRelease(Db *pDb){





  assert( pDb->pSPool && pDb->pSchema );

  assert( pDb->pSchema->schemaFlags & DB_SchemaLoaded );
  assert( sqlite3_mutex_held(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER)) );















  pDb->pSchema->pNext = pDb->pSPool->pSchema;
  pDb->pSPool->pSchema = pDb->pSchema;
  pDb->pSchema = &pDb->pSPool->sSchema;

  assert( (pDb->pSchema->schemaFlags & DB_SchemaLoaded)==0 );
}

/*
** The schema for database iDb of database handle db, which was opened
** with SQLITE_OPEN_SHARED_SCHEMA, has just been parsed. This function either
** finds a matching SchemaPool object on the global list (schemaPoolList) or
** else allocates a new one and sets the Db.pSPool variable accordingly.
................................................................................
      for(p=pDb->pVTable; p; p=pNext){
        pNext = p->pNext;
        sqlite3VtabUnlock(p);
      }
      pDb->pVTable = 0;
      sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
      if( DbHasProperty(db, iDb, DB_SchemaLoaded) ){
        schemaRelease(pDb);
      }
      if( bNew ){
        Schema *pNew = sqlite3SchemaGet(db, 0);
        if( pNew==0 ){
          rc = SQLITE_NOMEM;
        }else{
          pDb->pSchema = pNew;
................................................................................
  int i;
  assert_schema_state_ok(db);
  sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
  for(i=0; i<db->nDb; i++){
    if( i!=1 ){
      Db *pDb = &db->aDb[i];
      if( pDb->pSPool && DbHasProperty(db,i,DB_SchemaLoaded) ){
        schemaRelease(pDb);
      }
    }
  }

  sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
}

/*
** Release any sharable schema held by connection iDb of database handle
** db. Db.pSchema is left pointing to the static, empty, Schema object
** owned by the schema-pool.
................................................................................
*/
void sqlite3SchemaRelease(sqlite3 *db, int iDb){
  Db *pDb = &db->aDb[iDb];
  assert( iDb!=1 );
  assert_schema_state_ok(db);
  sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
  if( pDb->pSPool && DbHasProperty(db, iDb, DB_SchemaLoaded) ){
    schemaRelease(pDb);
  }
  sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
}

/*
** In most cases, this function finds and returns the schema associated 
** with BTree handle pBt, creating a new one if necessary. However, if







|
>
>
>
>
>
|
>
|


>
>
>
>
>
>
>
>
>
>
>
>
>
>
|
|
<
<
<







 







|







 







|



>







 







|







599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632



633
634
635
636
637
638
639
...
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
...
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
...
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
** When this function is called, the database connection Db must be
** using a schema-pool (Db.pSPool!=0) and must currently have Db.pSchema
** set to point to a populated schema object checked out from the 
** schema-pool. It is also assumed that the STATIC_MASTER mutex is held.
** This function returns the Schema object to the schema-pool and sets
** Db.pSchema to point to the schema-pool's static, empty, Schema object.
*/
static void schemaRelease(sqlite3 *db, Db *pDb){
  Schema *pRelease = pDb->pSchema;
  SchemaPool *pSPool = pDb->pSPool;

  pDb->pSchema = &pSPool->sSchema;

  assert( pDb->pSPool && pRelease );
  assert( pRelease->schemaFlags & DB_SchemaLoaded );
  assert( (pDb->pSchema->schemaFlags & DB_SchemaLoaded)==0 );
  assert( sqlite3_mutex_held(sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER)) );

  /* If the DBFLAG_FreeSchema flag is set and the database connection holds
  ** at least one other copy of the schema being released, delete it instead
  ** of returning it to the schema-pool.  */
  if( db->mDbFlags & DBFLAG_FreeSchema ){
    int i;
    for(i=0; i<db->nDb; i++){
      Db *p = &db->aDb[i];
      if( p!=pDb && p->pSchema!=&pSPool->sSchema && pDb->pSPool==p->pSPool ){
        schemaDelete(pRelease);
        return;
      }
    }
  }

  pRelease->pNext = pDb->pSPool->pSchema;
  pDb->pSPool->pSchema = pRelease;



}

/*
** The schema for database iDb of database handle db, which was opened
** with SQLITE_OPEN_SHARED_SCHEMA, has just been parsed. This function either
** finds a matching SchemaPool object on the global list (schemaPoolList) or
** else allocates a new one and sets the Db.pSPool variable accordingly.
................................................................................
      for(p=pDb->pVTable; p; p=pNext){
        pNext = p->pNext;
        sqlite3VtabUnlock(p);
      }
      pDb->pVTable = 0;
      sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
      if( DbHasProperty(db, iDb, DB_SchemaLoaded) ){
        schemaRelease(db, pDb);
      }
      if( bNew ){
        Schema *pNew = sqlite3SchemaGet(db, 0);
        if( pNew==0 ){
          rc = SQLITE_NOMEM;
        }else{
          pDb->pSchema = pNew;
................................................................................
  int i;
  assert_schema_state_ok(db);
  sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
  for(i=0; i<db->nDb; i++){
    if( i!=1 ){
      Db *pDb = &db->aDb[i];
      if( pDb->pSPool && DbHasProperty(db,i,DB_SchemaLoaded) ){
        schemaRelease(db, pDb);
      }
    }
  }
  db->flags &= ~DBFLAG_FreeSchema;
  sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
}

/*
** Release any sharable schema held by connection iDb of database handle
** db. Db.pSchema is left pointing to the static, empty, Schema object
** owned by the schema-pool.
................................................................................
*/
void sqlite3SchemaRelease(sqlite3 *db, int iDb){
  Db *pDb = &db->aDb[iDb];
  assert( iDb!=1 );
  assert_schema_state_ok(db);
  sqlite3_mutex_enter( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
  if( pDb->pSPool && DbHasProperty(db, iDb, DB_SchemaLoaded) ){
    schemaRelease(db, pDb);
  }
  sqlite3_mutex_leave( sqlite3_mutex_alloc(SQLITE_MUTEX_STATIC_MASTER) );
}

/*
** In most cases, this function finds and returns the schema associated 
** with BTree handle pBt, creating a new one if necessary. However, if

Changes to src/prepare.c.

477
478
479
480
481
482
483

484
485
486
487
488
489
490
** Otherwise, the schema is loaded. An error code is returned.
*/
int sqlite3ReadSchema(Parse *pParse){
  int rc = SQLITE_OK;
  sqlite3 *db = pParse->db;
  assert( sqlite3_mutex_held(db->mutex) );
  if( !db->init.busy ){

    rc = sqlite3Init(db, &pParse->zErrMsg);
    if( rc!=SQLITE_OK ){
      pParse->rc = rc;
      pParse->nErr++;
    }else if( db->noSharedCache && !IsReuseSchema(db) ){
      db->mDbFlags |= DBFLAG_SchemaKnownOk;
    }







>







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

Changes to src/sqliteInt.h.

1569
1570
1571
1572
1573
1574
1575
1576

1577
1578
1579
1580
1581
1582
1583
** Allowed values for sqlite3.mDbFlags
*/
#define DBFLAG_SchemaChange   0x0001  /* Uncommitted Hash table changes */
#define DBFLAG_PreferBuiltin  0x0002  /* Preference to built-in funcs */
#define DBFLAG_Vacuum         0x0004  /* Currently in a VACUUM */
#define DBFLAG_SchemaKnownOk  0x0008  /* Schema is known to be valid */

#define DBFLAG_SchemaInuse    0x0010  /* Do not free schemas */


/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/
#define SQLITE_QueryFlattener 0x0001   /* Query flattening */







|
>







1569
1570
1571
1572
1573
1574
1575
1576
1577
1578
1579
1580
1581
1582
1583
1584
** Allowed values for sqlite3.mDbFlags
*/
#define DBFLAG_SchemaChange   0x0001  /* Uncommitted Hash table changes */
#define DBFLAG_PreferBuiltin  0x0002  /* Preference to built-in funcs */
#define DBFLAG_Vacuum         0x0004  /* Currently in a VACUUM */
#define DBFLAG_SchemaKnownOk  0x0008  /* Schema is known to be valid */

#define DBFLAG_SchemaInuse    0x0010  /* Do not release sharable schemas */
#define DBFLAG_FreeSchema     0x0020  /* Free extra shared schemas on release */

/*
** Bits of the sqlite3.dbOptFlags field that are used by the
** sqlite3_test_control(SQLITE_TESTCTRL_OPTIMIZATIONS,...) interface to
** selectively disable various optimizations.
*/
#define SQLITE_QueryFlattener 0x0001   /* Query flattening */

Changes to test/reuse2.test.

294
295
296
297
298
299
300






301
302
  sqlite3_table_column_metadata db2 main bbb b
} {{} BINARY 0 0 0}

do_execsql_test -db db2 5.2.3 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=6 nschema=1}







finish_test








>
>
>
>
>
>


294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
  sqlite3_table_column_metadata db2 main bbb b
} {{} BINARY 0 0 0}

do_execsql_test -db db2 5.2.3 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {nref=6 nschema=1}

breakpoint
do_execsql_test -db db2 5.2.4 {
  PRAGMA integrity_check;
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool ORDER BY 1;
} {ok nref=6 nschema=1}

finish_test

Changes to test/reuse3.test.

105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
...
120
121
122
123
124
125
126



















































127
128
  set N [lindex [sqlite3_db_status db1 SCHEMA_USED 0] 1]
  expr $N==$N
} 1

sqlite3 db2 test.db -shared-schema 1
do_test 3.2 {
  execsql { SELECT * FROM x1 } db2
  breakpoint
  set N2 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
  expr $N2>($N/2) && $N2<($N/2)+400
} 1

sqlite3 db3 test.db -shared-schema 1
sqlite3 db4 test.db -shared-schema 1
do_test 3.3 {
................................................................................
  execsql { SELECT * FROM x1 } db3
  execsql { SELECT * FROM x1 } db4
  set N4 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
  set M [expr 2*($N-$N2)]
  expr {$N4 == (($M / 4) + $N-$M)}
} 1




















































finish_test








<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>


105
106
107
108
109
110
111

112
113
114
115
116
117
118
...
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
  set N [lindex [sqlite3_db_status db1 SCHEMA_USED 0] 1]
  expr $N==$N
} 1

sqlite3 db2 test.db -shared-schema 1
do_test 3.2 {
  execsql { SELECT * FROM x1 } db2

  set N2 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
  expr $N2>($N/2) && $N2<($N/2)+400
} 1

sqlite3 db3 test.db -shared-schema 1
sqlite3 db4 test.db -shared-schema 1
do_test 3.3 {
................................................................................
  execsql { SELECT * FROM x1 } db3
  execsql { SELECT * FROM x1 } db4
  set N4 [lindex [sqlite3_db_status db2 SCHEMA_USED 0] 1]
  set M [expr 2*($N-$N2)]
  expr {$N4 == (($M / 4) + $N-$M)}
} 1

catch { db1 close }
catch { db2 close }
catch { db3 close }
catch { db4 close }

#-------------------------------------------------------------------------
# Test the REINDEX command.
reset_db
do_execsql_test 4.1.0 {
  CREATE TABLE x1(a, b, c);
  CREATE INDEX x1a ON x1(a);
  CREATE INDEX x1b ON x1(b);
  CREATE INDEX x1c ON x1(c);
}
db close
sqlite3 db test.db -shared-schema 1

do_execsql_test 4.1.1 {
  REINDEX x1;
  REINDEX x1a;
  REINDEX x1b;
  REINDEX x1c;
  REINDEX;
}

do_test 4.1.2 {
  for {set i 1} {$i < 5} {incr i} {
    forcedelete test.db${i} test.db${i}-wal test.db${i}-journal
    forcecopy test.db test.db${i}
    execsql "ATTACH 'test.db${i}' AS db${i}"
  }
  register_schemapool_module db
  set {} {}
  execsql { SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool }
} {nref=5 nschema=1}

do_execsql_test 4.1.3 {
  REINDEX x1;
  REINDEX x1a;
  REINDEX x1b;
  REINDEX x1c;
  REINDEX db1.x1a;
  REINDEX db2.x1b;
  REINDEX db3.x1c;
  REINDEX;
}

do_execsql_test 4.1.4 {
  SELECT 'nref=' || nRef, 'nschema=' || nSchema FROM schemapool;
} {nref=5 nschema=1}

finish_test