Dear Rich,
I try to communicate better this time.
If I go by calls to my VFS then Yes. The subsequent SQL executions (same
SQL ran multiple times) will not result in page fetches (only fetch is for
offset=24 length=16).
Here is the pseudo scenario:
1. attach 'portfolio.db' as pdb;
2. create temp view pnlview select pb,security,quantity,descr from
security s, pdb.portfolio p where s.security=p.security
3. Execute: select pb,sum(quantity) from pnlview group by pb
1. When this executed we get vfs read calls to pages in both main and
pdb datbases.
4. Update pdb.portfolio in a separate component
5. Repeat steps 3 and 4
1. When step 3 SQL is reran we get vfs read calls to pages in main
database.
2. and one read for portfolio on offset 24 with length 16.
3. The output of the SQL does not change even though portfolio is
updated in step4
On Wed, Oct 23, 2013 at 1:01 PM, Richard Hipp <[email protected]> wrote:
> On Wed, Oct 23, 2013 at 1:50 PM, Vinay Boocha <[email protected]> wrote:
>
> > Dear Rich,
> >
> > Thanks for the response. I guessed it might need some cache but thought
> it
> > will refetch every time to ensure consistency.
> >
> > My main issue is that temp views are not picking up the update after the
> > pages of joined tables got updated (tables are updated by another actor
> in
> > the system).
> >
>
> I don't understand. Manifested views should only last for a single SQL
> statement then be discarded. Are you claiming that a manifested view is
> persisting past a single SQL statement?
>
> Maybe you can provide more details about your problem?
>
>
>
> >
> > Any way to notify the temp views to refetch the pages or always refetch
> > pages before executing a query?
> >
> > My VFS is performing a multi-master replication without the knowledge of
> > SQLite layer.
> >
> >
> > On Wed, Oct 23, 2013 at 12:25 PM, Richard Hipp <[email protected]> wrote:
> >
> > > On Wed, Oct 23, 2013 at 1:20 PM, Vinay Boocha <[email protected]>
> wrote:
> > >
> > > > Dear All,
> > > >
> > > > We are observing that temp views are using page cache even after
> > setting
> > > > the cache_size= 0. I am stuck debugging this issue for the last two
> > days.
> > > > Any pointers will be greatly appreciated.
> > > >
> > >
> > > Depending on the query and the schema, the view might need to be
> > evaluated
> > > ("manifested"). Where is that manifested view to be stored, if not in
> > the
> > > page cache?
> > >
> > > Why is SQLite using memory to help it answer a query an "issue" for
> you?
> > >
> > >
> > > >
> > > > When we run a SQL query multiple times on a temp view our custom vfs
> is
> > > > getting read call only for offset=24 and length=16. I believe its
> > > checking
> > > > if the file is updated or not.
> > > >
> > >
> > > Correct. Just because the database is read-only to you doesn't mean it
> > is
> > > read-only to every other process in the system.
> > >
> > >
> > >
> > > >
> > > > *Details:*
> > > >
> > > >
> > > > 1. Temp view is created by joining two tables. One of the table is
> > in
> > > an
> > > > attached database.
> > > > 2. pragma cache_size= 0 is set immediately after opening the
> > > connection.
> > > > 3. The cache_size zero is confirmed based on vfs calls for the
> table
> > > in
> > > > the main database.
> > > > _______________________________________________
> > > > sqlite-users mailing list
> > > > [email protected]
> > > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > > >
> > >
> > >
> > >
> > > --
> > > D. Richard Hipp
> > > [email protected]
> > > _______________________________________________
> > > sqlite-users mailing list
> > > [email protected]
> > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > _______________________________________________
> > sqlite-users mailing list
> > [email protected]
> > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
> >
>
>
>
> --
> D. Richard Hipp
> [email protected]
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
