Re: [sqlite] [EXTERNAL] Preupdate hook column name

[João Ramos]

I can't do that (execute other statements) inside a sqlite3_preupdate_hook
callback.

On Fri, Apr 13, 2018 at 6:50 AM, Hick Gunter <h...@scigames.at> wrote:

> See pragma table_info;
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von João Ramos
> Gesendet: Donnerstag, 12. April 2018 20:54
> An: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
> Betreff: [EXTERNAL] [sqlite] Preupdate hook column name
>
> Hi,
>
> I've successfully added support for the sqlite3_preupdate_hook(), but I
> also need to get the column name when calling either
> sqlite3_preupdate_old() or sqlite3_preupdate_new().
> I know that I can obtain the type of the value of the column at index i by
> calling sqlite3_value_type() but I also need the name of the column and I
> can't find a way to do this.
>
> Is this even possible?
>
> Thank you,
>
> --
> *João Ramos*
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick | Software Engineer | Scientific Games International GmbH |
> Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O)
> +43 1 80100 - 0
>
> May be privileged. May be confidential. Please delete if not the addressee.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*João Ramos*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Preupdate hook column name

[João Ramos]

Hi,

I've successfully added support for the sqlite3_preupdate_hook(), but I
also need to get the column name when calling either
sqlite3_preupdate_old() or sqlite3_preupdate_new().
I know that I can obtain the type of the value of the column at index i by
calling sqlite3_value_type() but I also need the name of the column and I
can't find a way to do this.

Is this even possible?

Thank you,

-- 
*João Ramos*
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Possible bug in the SQL parser

[João Ramos]

Sorry for the late reply. That output (--1 etc.) was me manually
"formatting" the results.
I came across this issue using SQLiteStudio v3.0.7 on Windows. I just
create a new DB and run that script: it outputs two rows, with one column
each, with the values 1 and 2 respectively, instead of an error.


On Mon, Feb 29, 2016 at 11:18 AM, R Smith  wrote:

>
>
> On 2016/02/29 12:49 PM, Jo?o Ramos wrote:
>
>> Maybe this has been fixed then? This is what I'm getting:
>>
>> select sqlite_version(); -- 3.8.10
>>
>> select sqlite_source_id(); -- 2015-05-04 19:13:25
>> 850c11866686a7b39d7b163fb60898c11283688e
>>
>>
>> WITH
>>
>> tA(id, name) AS
>>
>> (
>>
>> SELECT 1, "a" UNION ALL SELECT 2, "b"
>>
>> ),
>>
>> tB(name) AS
>>
>> (
>>
>> SELECT "a" UNION ALL SELECT "b"
>>
>> )
>>
>> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>>
>>
>> -- 1
>>
>> -- 2
>>
>
> This output ( -- 1 etc.) looks like it is produced by some SQLite
> interface type thing or perhaps an admin tool, it doesn't look like the
> SQLite cli, so maybe there's possibly something wrong there? Many of those
> tools substitute the column names a bit in queries.
>
> Show us the exact tool you use to get it and also the OS version etc.
> please.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Jo?o Ramos*

[sqlite] Possible bug in the SQL parser

[João Ramos]

Maybe this has been fixed then? This is what I'm getting:

select sqlite_version(); -- 3.8.10

select sqlite_source_id(); -- 2015-05-04 19:13:25
850c11866686a7b39d7b163fb60898c11283688e


WITH

tA(id, name) AS

(

SELECT 1, "a" UNION ALL SELECT 2, "b"

),

tB(name) AS

(

SELECT "a" UNION ALL SELECT "b"

)

SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);


-- 1

-- 2


On Fri, Feb 26, 2016 at 6:19 PM, Dan Kennedy  wrote:

> On 02/27/2016 12:49 AM, Jo?o Ramos wrote:
>
>> I'm using SQLite 3.8.10.2 and the following query illustrates the problem:
>>
>> WITH
>> tA(id, name) AS
>> (
>>  SELECT 1, "a" UNION ALL SELECT 2, "b"
>> ),
>> tB(name) AS
>> (
>>  SELECT "a" UNION ALL SELECT "b"
>> )
>> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
>>
>>
>> There is no _id_ column in the tB table, yet the statement doesn't produce
>> any error and in fact will return the ids of table tA. This doesn't seem
>> correct to me.
>>
>>
>>
> Cannot reproduce this problem here:
>
> dan at darkstar:~/work/sqlite/bld$ ./sqlite3
> SQLite version 3.8.10.2 2015-05-20 18:17:19
> Enter ".help" for usage hints.
> Connected to a transient in-memory database.
> Use ".open FILENAME" to reopen on a persistent database.
> sqlite> WITH tA(id, name) AS (
>...>   SELECT 1, "a" UNION ALL SELECT 2, "b"
>...> ),
>...> tB(name) AS (
>...>   SELECT "a" UNION ALL SELECT "b"
>...> )
>...> SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);
> Error: no such column: tB.id
>
>
> If you were using "oid", "rowid" or "_rowid_" instead of "id", then it
> might look like it was returning the values from table tA. All views and
> CTEs in SQLite have such columns for historical reasons, but the contents
> of them is both undefined and unstable. Sometimes it's a sequence of
> integers starting at 1.
>
> Dan.
>
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
*Jo?o Ramos*

[sqlite] Possible bug in the SQL parser

[João Ramos]

I'm using SQLite 3.8.10.2 and the following query illustrates the problem:

WITH
tA(id, name) AS
(
SELECT 1, "a" UNION ALL SELECT 2, "b"
),
tB(name) AS
(
SELECT "a" UNION ALL SELECT "b"
)
SELECT tB.id FROM tA INNER JOIN tB ON (tA.name = tB.name);


There is no _id_ column in the tB table, yet the statement doesn't produce
any error and in fact will return the ids of table tA. This doesn't seem
correct to me.


-- 
*Jo?o Ramos*

Re: [sqlite] Best approach for "notifications"

[João Ramos]

On Mon, Jul 14, 2014 at 3:57 PM, Simon Slavin  wrote:

>
> The update hook function is an ideal way to do what you're doing.  There
> is also a pre-update hook:
>
> 
>
> If you cannot use these, then my instinct is to try to figure out why,
> rather than to do something else that cannot possibly be as useful as they
> are.
>

The update hook says "The update hook implementation must not do anything
that will modify the database connection that invoked the update hook. Any
actions to modify the database connection must be deferred until after the
completion of the sqlite3_step() call that triggered the update hook. Note
that sqlite3_prepare_v2() and sqlite3_step() both modify their database
connections for the meaning of "modify" in this paragraph."

Because the update hook only supplies the rowId, I cannot extract the
information necessary with this callback.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Best approach for "notifications"

[João Ramos]

Hi,

In a project I'm working on we need to have a mechanism that creates
notifications when something happens in any given table.
For example, for updates, we implemented something like:
 - create a ON UPDATE trigger in the target table
 - the trigger calls a custom function with every necessary column as an
argument (old and new data)
 - the custom function stores the old and new data
 - on commit (sqlite3_commit_hook), the data is sent as an event
 - on rollback the data is discarded

The insert and delete events are exactly the same except without the old
and new data respectively.
So my question is: is this the best approach? This method works but is also
a bit complex and I wasn't able to come up with something simpler (for
example I know we cannot use sqlite3_update_hook to retrieve data).

Thanks,
João Ramos
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Planner chooses incorrect index

[João Ramos]

When I can I'll try with the latest release.

Thanks for all your help.


On Tue, Jul 8, 2014 at 4:05 PM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 06/25/2014 04:44 PM, João Ramos wrote:
>
>> The sample column has some sensitive data that I can't share, so I'll have
>> to change it and then try to reproduce the problem (I removed it before
>> because I assumed that it wouldn't be important).
>>
>> On Mon, Jun 23, 2014 at 3:26 PM, Dan Kennedy <danielk1...@gmail.com>
>> wrote:
>>
>>  On 06/23/2014 05:48 AM, João Ramos wrote:
>>>
>>>  Here you go:
>>>>
>>>> sqlite_stat1 (before - good planning)
>>>> HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5
>>>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2
>>>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2
>>>>
>>>> sqlite_stat1 (after - bad planning)
>>>> HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5
>>>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2
>>>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 15492 3099 2
>>>>
>>>>
> The issue, it turned out, is in the sqlite_stat1 data. The first set of
> stat1 data above suggests that using index "idx_HistoryEntry_uri_historyOrder"
> for a (uri=? AND historyOrder=?) lookup returns 5 rows. But using
> "idx_HistoryEntry_historyOrder_historyTimestamp" for a (historyOrder=?)
> lookup returns 6 rows. So in this case SQLite prefers the former.
>
> But using the second set of sqlite_stat1 data, both lookups appear to
> return 5 rows. Both strategies are considered equally good. So SQLite just
> picks either. In this case, clearly it's making an unlucky choice.
>
> Version 3.8.5 includes logic to prefer the (uri=? AND historyOrder=?)
> lookup over the (historyOrder=?) one on the basis that it uses a superset
> of the WHERE clause terms. Introduced here:
>
>   http://www.sqlite.org/src/info/683dd379a293b2f3
>
> So upgrading to 3.8.5 might fix the problem.
>
> Dan.
>
>
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

OK, thank you all for your support.


On Thu, Jun 26, 2014 at 3:22 AM, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 6/25/2014 8:48 PM, João Ramos wrote:
>
>> Now that you mentioned the WAL, shouldn't this actually help prevent this
>> scenario?
>>
>
> Quite the opposite - WAL helps enable this scenario. With traditional
> rollback journal, the writer would be unable to write at all while a reader
> is active, so issues of visibility fail to arise. Thus, as long as at least
> one connection to the shared cache has an open statement, so does the
> cache's connection to the underlying file, and the writer will be locked
> out.
>
>
>  I've never looked at SQLite implementation, but if a transaction
>> starts after a successful commit (T1), the new data it tries to access
>> shouldn't be cached because it was just now written to the WAL, correct?
>>
>
> Yes. But remember - this is true for "real" connections to the underlying
> file. All "pseudo"-connections to the shared cache use the same underlying
> "real" connection. The transaction on the "real" connection starts when the
> number of "pseudo" transactions on "pseudo" connections goes from 0 up to
> 1, and ends when that number goes from 1 down to 0.
>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

On Wed, Jun 25, 2014 at 8:04 PM, Sohail Somani <soh...@taggedtype.net>
wrote:

> On 22/06/2014 6:33 PM, João Ramos wrote:
>
>> The issue appeared with the following scenario: - using SQLite v3.8.4.3 -
>> the DB is in WAL mode and running with multi-thread mode - every thread
>> has
>> a read-only DB connection (using thread-local-storage) - a single DB
>> connection is shared between threads to write
>>
>
> I had a similar issue and while I haven't yet resolved it, the culprit is
> a SQL statement not being finalized somewhere, not sure where. Make sure
> you have no open SQL statements.


I'm certain that no other statements were open in that connection. That's
one of the first things I rechecked.


>
>
> Sohail
>
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

On Wed, Jun 25, 2014 at 2:19 PM, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 6/25/2014 5:19 AM, João Ramos wrote:
>
>> And I understand the limitations a shared-cache mode may have, but if this
>> is to be the case, it should be documented. I can be wrong, but I didn't
>> find anywhere in the docs where this scenario is presented (or has some
>> sort of warning).
>>
>
> Well, one can't document every possible scenario, every possible
> interaction of various features. There's a documentation on shared cache,
> complete with a nice picture explaining how all connections to the cache
> share a single connection to the underlying database file:
>
> http://sqlite.org/sharedcache.html
> "Externally, from the point of view of another process or thread, two or
> more database connections using a shared-cache appear as a single
> connection. The locking protocol used to arbitrate between multiple
> shared-caches or regular database users is described elsewhere."
>

Of course and I understand what your saying, and I'm not asking to document
every single feature interaction. However, the documentation never mentions
the possibility of data visibility changes caused by the shared-cache mode
and, in my opinion, it's a big enough issue that should merit being
referenced in the documentation.


>
> Then there's documentation on WAL mode, explaining how it is possible for
> a writer to co-exist with readers, via page versioning.
>
> http://www.sqlite.org/wal.html


Now that you mentioned the WAL, shouldn't this actually help prevent this
scenario? I've never looked at SQLite implementation, but if a transaction
starts after a successful commit (T1), the new data it tries to access
shouldn't be cached because it was just now written to the WAL, correct?


>
>
> If you use both features, you kind of have to put two and two together to
> see how they would interact.


Well, the way I see it, it's more like a 10 variable equation: not that
obvious. But that could be just me.


>
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Planner chooses incorrect index

[João Ramos]

The sample column has some sensitive data that I can't share, so I'll have
to change it and then try to reproduce the problem (I removed it before
because I assumed that it wouldn't be important).

On Mon, Jun 23, 2014 at 3:26 PM, Dan Kennedy <danielk1...@gmail.com> wrote:

> On 06/23/2014 05:48 AM, João Ramos wrote:
>
>> Here you go:
>>
>> sqlite_stat1 (before - good planning)
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 14992 44 5
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 14992 6 2
>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 14992 2999 2
>>
>> sqlite_stat1 (after - bad planning)
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 15492 45 5
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 15492 5 2
>> HistoryEntry idx_HistoryEntry_sourceType_sourceId 15492 3099 2
>>
>
> Unfortunately the last column of the sqlite_stat4 data is missing, likely
> because it contains embedded 0x00 bytes. And without the sqlite_stat4 data,
> it seems that SQLite picks the "good" query plan in either case.
>
> Can you dump the sqlite_stat1 and sqlite_stat4 tables using the shell
> ".dump" command instead?
>
> Thanks,
> Dan.
>
>
>
>
>
>> sqlite_stat4 (before - good planning)
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 340 1 78 78 81 17 17
>> 81
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 722 489 1 566 566 661 38
>> 96
>> 661
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 25 25 1 1650 1650 1665 56
>> 352 1665
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 747 1 2569 2569 2899
>> 106
>> 447 2899
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 862 1 1 2569 3331 3331 106
>> 462 3331
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 2026 1789 1 3825 3825
>> 4997 131
>> 660 4997
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 143 113 1 6178 6178 6232
>> 154
>> 931 6232
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 181 180 1 6412 6412 6477
>> 162
>> 974 6477
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 2 2 1 6663 6663 6663 169
>> 984
>> 6663
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 1251 988 1 6953 6953 7488
>> 186
>> 1062 7488
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 379 370 1 8260 8260 8329
>> 195
>> 1361 8329
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 135 92 1 8915 8915 8965
>> 218
>> 1439 8965
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 196 194 1 9128 9128 9129
>> 227
>> 1501 9129
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 280 170 1 9622 9622 9650
>> 237
>> 1579 9650
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 1228 1045 1 9954 9954
>> 9995 244
>> 1709 9995
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 178 170 1 11192 11192
>> 11322 246
>> 1898 11322
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 312 299 1 11503 11503
>> 11661 252
>> 1963 11661
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 546 543 1 11921 11921
>> 12037 258
>> 1994 12037
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 169 113 1 12574 12574
>> 12586 261
>> 2013 12586
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 216 115 1 12862 12862
>> 12973 265
>> 2187 12973
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 388 379 1 13101 13101
>> 13327 270
>> 2291 13327
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 366 351 1 13722 13722
>> 13973 295
>> 2388 13973
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 200 1 1 14482 14530 14530
>> 331
>> 2749 14530
>> HistoryEntry idx_HistoryEntry_uri_historyOrder 233 2 1 14684 14848 14849
>> 333
>> 3067 14849
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 1665 1665 0 1663 1665
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 3331 3331 0 3329 3331
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 4997 4997 0 4995 4997
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 6663 6663 0 6661 6663
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 8329 8329 0 8327 8329
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 9995 9995 0 9993 9995
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 12006 1 1 0
>> 11661 11661 0 11659 11661
>> HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 12392
>> 12392 12392 377 12

Re: [sqlite] Data visibility problem

[João Ramos]

On Mon, Jun 23, 2014 at 3:31 PM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 23 Jun 2014, at 11:17am, João Ramos <joao.si...@gmail.com> wrote:
>
> > On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin <slav...@bigfraud.org>
> wrote:
> >
> >> Are you finalizing all these SQL commands (or using _exec() which
> amounts
> >> to the same thing) ?
> >
> > Yes, I double checked and everything is being terminated properly (e.g.:
> > sqlite3_reset, etc.) and without any error codes.
>
> Excellent.
>
> >> What do you mean by 'releases' here ?  If you have other threads using
> the
> >> same connection, you're obviously not closing it.
> >
> > No, the SQLite connection isn't closed. By "release" I mean release back
> to
> > the thread pool.
>
> Okay.  I would temporarily, for testing purposes, turn off shared cache
> mode.  Another thing to try messing with is read_uncommitted:
>

Turning off shared cache mode solved this (read uncommitted has always been
off).


>
> <http://www.sqlite.org/pragma.html#pragma_read_uncommitted>
>
> Which of these does or doesn't change how your app behaves might help you
> diagnose the problem.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

On Mon, Jun 23, 2014 at 3:03 PM, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 6/23/2014 5:43 AM, João Ramos wrote:
>
>> If multiple connections in shared cache mode are treated as a single
>> "real"
>> connection, should I change the thread mode to Serialized?
>>
>
> What mode do you use now?
>
> In any case, I doubt the thread mode has any bearing on the problem. The
> scenario may occur even if no two threads ever run in parallel. It's only
> necessary that SELECTs on different connections to shared cache interleave:
> you call sqlite3_step on connection A, then later call sqlite3_step on
> connection B (while the statement in A has not been reset) - that second
> traversal would see the data as of the start of the first traversal.
>
>
I'm using multi-thread because DB connections aren't used simultaneously in
two or more threads. But since you said that in shared cache mode multiple
connections behave as a "single" connection, I was afraid that this mode
would somehow change the meaning of "simultaneously in two or more threads".

For example, imagine a scenario with two threads, each with its own
connection, both with shared cache and in multi-thread mode. According to
the documentation, I don't need the serialized thread mode because the
connections aren't being used simultaneously. However, internally, they
will simultaneously access the shared cache. I'm sure the shared cache is
protected against this, and I was just making sure that this is the case
(and not an omission in the docs, like for example: "Multi-thread. In this
mode, SQLite can be safely used by multiple threads provided that no single
database connection is used simultaneously in two or more threads *and* one
or more connections aren't in shared cache mode.")


>
>  I tough this
>> "single" connection was in terms of page cache
>>
>
> Well, yes. So you have a version of a page in this cache - that version
> must be as of the time of the oldest read transaction still outstanding. So
> all connections to that shared cache see the same version of that page, and
> cannot observe any changes made to it later.


Ok, I see what you mean.


>
>  and would not have any
>> implications on how the connections are accessed (or not) concurrently,
>> especially transaction control.
>>
>
> Concurrent access and transaction control are unrelated concepts. I'm not
> sure why you are bringing them up together. You can observe transaction
> isolation effects with a single thread interleaving access to two database
> connections. Start traversing a SELECT statement on one connection; between
> two calls to sqlite3_step, modify the data on the other (WAL journal mode
> would let you); continue traversal of the statement - it would not observe
> the changes the same thread has just made.


I never meant to imply that they are related somehow (see the first
response). Only asking if I could be stumbling against an improper use of
thread-mode + shared cache, and that could cause a connection not seeing
committed data.


>
>  Either way, the example you gave does not apply. What was happening with
>> my
>> code was that a transaction A started at T1, inserted a row and was
>> committed successfully at T2. Another transaction B started at T3 and
>> didn't see the new row.
>>
>
> Was there, by any chance, a seemingly unrelated transaction C using the
> same shared cache that started reading at T0, and has not completed by T3?


Yes, this could very possible be the case.


>
>
>  I can't see how the fact that transaction A was in
>> shared cache mode and B in private cache mode
>>
>
> Shouldn't it be the other way round? I thought you said all readers (B
> among them) use shared cache, while a writer (like A) uses a private
> connection.


Your're correct, sorry for my mistake.


>
>
>  could influence the new row
>> not being seen in transaction B, when its transaction started after
>> transaction A commit. Doesn't this break ACID?
>>
>
> ACID provides guarantees on when changes made in one transaction will
> *not* be seen by another. It says nothing about when changes in one
> transaction *will* be seen by another.
>
> Yes, connections sharing a cache are not quite as isolated from each other
> as independent private connections (it's even possible to enable
> read-uncommitted mode, whereby one such connection can see
> not-yet-committed changes made by another). As any other option, shared
> cache brings some benefits and some limitations (if it were all benefits,
> it wouldn't be an option).


Sorry, but I'm going to disagree (in part) with what you're saying. I agree
with what you say concerning "consistency"

Re: [sqlite] Planner chooses incorrect index

[João Ramos]

HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 5 1 1 13042
13045 13045 998 13043 13045
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13210
13210 13210 1150 13208 13210
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 13411
13412 13412 1344 13410 13412
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 6 1 1 13572
13575 13575 1498 13573 13575
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13582
13583 13583 1503 13581 13583
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 13726
13729 13729 1643 13727 13729
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 1 1 1 13775
13775 13775 1688 13773 13775
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 8 1 1 13978
13980 13980 1890 13978 13980
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14203
14204 14204 2098 14202 14204
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 14317
14320 14320 2208 14318 14320
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 14452
14452 14452 2332 14450 14452
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 15057
15057 15057 2909 15055 15057
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 3 1 1 15189
15189 15189 3039 15187 15189
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 15266
15266 15266 3114 15264 15266
HistoryEntry idx_HistoryEntry_historyOrder_historyTimestamp 4 1 1 15272
15273 15273 3117 15271 15273
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 230 230 0 230
230
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 1580 1580 0
1580 1580
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 1721 1721 0
1721 1721
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 3443 3443 0
3443 3443
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 3524 3524 0
3524 3524
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5028 5028 0
5028 5028
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5032 5032 0
5032 5032
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5165 5165 0
5165 5165
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5272 5272 0
5272 5272
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 5305 5305 0
5305 5305
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 6887 6887 0
6887 6887
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 8036 8036 0
8036 8036
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 8609 8609 0
8609 8609
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 8653 8653 0
8653 8653
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 9306 9306 0
9306 9306
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 10331 10331 0
10331 10331
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 11098 11098 0
11098 11098
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 11261 11261 0
11261 11261
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 12053 12053 0
12053 12053
HistoryEntry idx_HistoryEntry_sourceType_sourceId 14924 1 1 0 13775 13775 0
13775 13775
HistoryEntry idx_HistoryEntry_sourceType_sourceId 468 1 1 14924 15266 15266 1
15266 15266
HistoryEntry idx_HistoryEntry_sourceType_sourceId 12 1 1 15392 15395 15395 2
15395 15395
HistoryEntry idx_HistoryEntry_sourceType_sourceId 64 64 1 15404 15404 15409 3
15404 15409
HistoryEntry idx_HistoryEntry_sourceType_sourceId 24 1 1 15468 15478 15478 4
15415 15478



On Sun, Jun 22, 2014 at 9:46 PM, Richard Hipp <d...@sqlite.org> wrote:

> On Sun, Jun 22, 2014 at 2:05 PM, João Ramos <joao.si...@gmail.com> wrote:
>
> >
> > Everything looks fine and the query runs smoothly, but at around 15000
> > entries, the query plan changes...
> >
>
>
> Please send the content of the sqlite_stat1 table.  Also sqlite_stat3 or
> sqlite_stat4, if they exist.  Do this both for before and after the
> slowdown.  Thanks.
>
> --
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

On Mon, Jun 23, 2014 at 10:58 AM, Simon Slavin <slav...@bigfraud.org> wrote:

>
> On 22 Jun 2014, at 11:33pm, João Ramos <joao.si...@gmail.com> wrote:
>
> > What happens is this: - a thread acquires the write DB connection - that
> > thread inserts a new row in a table and gets its id -
>
> Are you finalizing all these SQL commands (or using _exec() which amounts
> to the same thing) ?
>
>
Yes, I double checked and everything is being terminated properly (e.g.:
sqlite3_reset, etc.) and without any error codes.


> > the thread releases
> > the write DB connection (so that other threads can use it)
>
> What do you mean by 'releases' here ?  If you have other threads using the
> same connection, you're obviously not closing it.
>
>
No, the SQLite connection isn't closed. By "release" I mean release back to
the thread pool.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Data visibility problem

[João Ramos]

If multiple connections in shared cache mode are treated as a single "real"
connection, should I change the thread mode to Serialized? I tough this
"single" connection was in terms of page cache and would not have any
implications on how the connections are accessed (or not) concurrently,
especially transaction control.

Either way, the example you gave does not apply. What was happening with my
code was that a transaction A started at T1, inserted a row and was
committed successfully at T2. Another transaction B started at T3 and
didn't see the new row. I can't see how the fact that transaction A was in
shared cache mode and B in private cache mode could influence the new row
not being seen in transaction B, when its transaction started after
transaction A commit. Doesn't this break ACID?


On Mon, Jun 23, 2014 at 3:34 AM, Igor Tandetnik <i...@tandetnik.org> wrote:

> On 6/22/2014 6:33 PM, João Ramos wrote:
>
>> What happens is this: - a thread acquires the write DB connection - that
>> thread inserts a new row in a table and gets its id - the thread releases
>> the write DB connection (so that other threads can use it) - the same
>> thread acquires (its own) read-only DB connection - thread tries to read
>> the row it just inserted and it returns NULL
>>
>
> Well, it's a *shared* cache. All connections reading from the cache must
> needs see the same data - namely, the data observed by the oldest
> still-open transaction.
>
> A shared cache is an in-memory construct that holds a single "real"
> connection to the actual database file; all connections to the shared cache
> go through this single "real" connection. Say, connection A starts reading
> at time T - this initiates a read transaction on the "real" connection.
> Connection B starts reading at time T+2 - that becomes part of the same
> transaction. A may stop reading (e.g. reset its statement) at time T+3 -
> but B still reads the data as it existed at time T, and cannot observe any
> changes made at T+1.
> --
> Igor Tandetnik
>
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Data visibility problem

[João Ramos]

Hi,
The issue appeared with the following scenario: - using SQLite v3.8.4.3 -
the DB is in WAL mode and running with multi-thread mode - every thread has
a read-only DB connection (using thread-local-storage) - a single DB
connection is shared between threads to write - this write DB
connection, at any given time, can only be used by one thread (it's
essentially a connection pool with only one entry) - the read-only DB
connections have SQLITE_OPEN_SHAREDCACHE while the write DB connection has
SQLITE_OPEN_PRIVATECACHE
What happens is this: - a thread acquires the write DB connection - that
thread inserts a new row in a table and gets its id - the thread releases
the write DB connection (so that other threads can use it) - the same
thread acquires (its own) read-only DB connection - thread tries to read
the row it just inserted and it returns NULL
And no, no one is deleting rows from this table and the new row appears
eventually.I was able to fix this by making the read-only DB connections
SQLITE_OPEN_PRIVATECACHE, and nothing else.
I checked the documentation and I didn't find anything that would suggests
that I'm doing anything wrong. Any data change after a successful commit
should bevisible to any transaction that starts after it, correct? The
cache mode (or any other setting for that matter) shouldn't influence this.
I can't give you any example code (the project where I detected this is
rather big and private), so if necessary I would have to create a small
example where I would try to simulate this (I'm rather hopping I won't have
to :).
Thank you,João
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Planner chooses incorrect index

[João Ramos]

Hi,

I'm using SQLite v3.8.4.3 and the issue happens in a table with the
following schema:

CREATE TABLE HistoryEntry (
id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
sourceType INTEGER NOT NULL,
sourceId INTEGER,
uri TEXT NOT NULL,
historyOrder BIGINT NOT NULL CHECK (historyOrder >= 0) DEFAULT
9223372036854775807,
historyTimestamp BIGINT NOT NULL CHECK (historyTimestamp > 0),
entryState INTEGER,
entryIsSpam INTEGER,
entryRelatedTech INTEGER,
entryDisplayed INTEGER,
entryPayload TEXT
);
CREATE INDEX idx_HistoryEntry_historyOrder_historyTimestamp ON
HistoryEntry(historyOrder, historyTimestamp);
CREATE INDEX idx_HistoryEntry_sourceType_sourceId ON
HistoryEntry(sourceType, sourceId);
CREATE INDEX idx_HistoryEntry_uri_historyOrder ON HistoryEntry(uri,
historyOrder);

The query being used is the following:

WITH maxHist (uri, historyOrder) AS (
SELECT uri AS uri, max(historyOrder) AS historyOrder FROM HistoryEntry
WHERE ((sourceType & 8191) <> 0)
GROUP BY uri
)
SELECT opH.uri AS uri, opH.historyOrder AS historyOrder,
max(opH.historyTimestamp) AS historyTimestamp, opH.id AS historyId
FROM HistoryEntry opH, maxHist
WHERE (((opH.sourceType & 8191) <> 0) AND (opH.uri = maxHist.uri) AND
(opH.historyOrder = maxHist.historyOrder))
GROUP BY opH.uri, opH.historyOrder;

This is the associated query plan:

SCAN TABLE HistoryEntry USING INDEX idx_HistoryEntry_uri_historyOrder
SCAN SUBQUERY 1
SEARCH TABLE HistoryEntry AS opH USING INDEX
idx_HistoryEntry_uri_historyOrder (uri=? AND historyOrder=?)
USE TEMP B-TREE FOR GROUP BY

Everything looks fine and the query runs smoothly, but at around 15000
entries, the query plan changes to:

SCAN TABLE HistoryEntry USING INDEX idx_HistoryEntry_uri_historyOrder
SCAN SUBQUERY 1
SEARCH TABLE HistoryEntry AS opH USING INDEX
idx_HistoryEntry_historyOrder_historyTimestamp (historyOrder=?)
USE TEMP B-TREE FOR GROUP BY

This causes the query to take 2.2s instead 47,20ms!
The issues is rather obvious: the planner decided to change to a much worse
index, which in turn causes the slowdown.
I was able to bypass the problem by providing an hint in the query, like so:

WITH maxHist (uri, historyOrder) AS (
SELECT uri AS uri, max(historyOrder) AS historyOrder FROM HistoryEntry
WHERE ((sourceType & 8191) <> 0)
GROUP BY uri
)
SELECT opH.uri AS uri, opH.historyOrder AS historyOrder,
max(opH.historyTimestamp) AS historyTimestamp, opH.id AS historyId
FROM HistoryEntry opH, maxHist
WHERE (((opH.sourceType & 8191) <> 0) AND (opH.uri = maxHist.uri) AND
+(opH.historyOrder = maxHist.historyOrder))
GROUP BY opH.uri, opH.historyOrder;

This isn't optimal because the query plan changes to:

SCAN TABLE HistoryEntry USING INDEX idx_HistoryEntry_uri_historyOrder
SCAN SUBQUERY 1
SEARCH TABLE HistoryEntry AS opH USING INDEX
idx_HistoryEntry_uri_historyOrder (uri=?)
USE TEMP B-TREE FOR GROUP BY

But at least the correct index gets chosen.
The statistics where all updated before running every query and the DB
vacuumed: yet the planner always chooses the incorrect index after reaching
around 15000 entries.
If necessary, I can provide a DB where this happens.

Thank you,
João
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users