Re: [sqlite] Problem with the new pointer-passing interface

Gwendal, > But... why don't you simply ask your users for a static string as well??? > C++ makes it trivial to support this requirement of the C API. I could do that, of course. But it wouldn't solve the issue. It would push the problem just one level up. > // pointerType should be a static

Re: [sqlite] sequencer

On Fri, Aug 4, 2017 at 7:41 AM, petern wrote: > Sylvain, are you happy with the performance? > > Maybe you are using it differently but, from my tests, the DEFAULT clause > is ignored for PRIMARY KEY columns. I had to use an ordinary column with > UNIQUE constraint

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

The main problem with multiple writers would be in conflict resolution, locking and deadlocks. Imagine a simple accounting transactions "update accounts set value = value + 1 where ..." if run at the same time from 2 threads (or close enough), then if you do not have conflict resolution in place

Re: [sqlite] Problem with the new pointer-passing interface

I agree that it's impossible to enforce "static strings" or "strings that live long enough" with the C/C++ type system. You chose to force key management down the throat of your users, with two problems : - All the potential memory leaks, thread races, etc that may occur if your key

[sqlite] Thinking about a way to extend the number of writers in WAL mode

Hello, I was thinking about a possible solution for sqlite "only single writer is allowed at the same time" and database lock. sqlite has WAL mode for better concurrency and this could maybe be used to extend the number of writters: Do you think it would be possible to create a

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

>Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im >Auftrag von Nico Williams > >On Tue, Aug 01, 2017 at 10:56:47AM -0700, Matt Chambers wrote: >> load_extension() has the very sensible behavior of: >> > So for example, if "samplelib" cannot be loaded, then names like >>

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

Yes this would be the case if no one closes the write transactions before reading. Would a possible solution be to use "read uncommited" or to include a kind of timestamp or autoInc identifier used internally by sqlite engine ? even if I am not sure it would be enough to avoid conflicts...

Re: [sqlite] sequencer

On Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 7:41 AM, petern > wrote: > >> Sylvain, are you happy with the performance? >> >> Maybe you are using it differently but, from my tests, the DEFAULT clause

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams wrote: > On Tue, Aug 01, 2017 at 10:56:47AM -0700, Matt Chambers wrote: > > load_extension() has the very sensible behavior of: > > > So for example, if "samplelib" cannot be loaded, then names like > > > "samplelib.so" or

Re: [sqlite] Checkin d8637bad produces bad amalgamation

On 08/04/2017 03:50 AM, Keith Medcalf wrote: Check-in d8637bad produces a bad amalgamation output. Thanks for reporting this. Should be fixed now. Dan. Index: tool/mksqlite3c-noext.tcl == --- tool/mksqlite3c-noext.tcl +++

Re: [sqlite] Problem with the new pointer-passing interface

Gwendal, > I agree that it's impossible to enforce "static strings" or "strings that > live long enough" with the C/C++ type system. > > You chose to force key management down the throat of your users, Hm, quite a tough expression ... the advantage is that the wrapper does not depend on the

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 06:49:42PM +, Peter Da Silva wrote: > On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" > n...@cryptonector.com> wrote: > > SQLite3's WAL is already log-structured. The main DB file isn't. > >

Re: [sqlite] sequencer

A "temp table" would only be visible in the session that creates it and would not live past the end of the session. Sequences should be persistent... -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Nico Williams Gesendet:

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" wrote: > Imagine a mode where there is only a WAL, and to checkpoint is to write a new > WAL with only live contents and... rename(2) into place. Such

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, 1:59 PM, "sqlite-users on behalf of Nico Williams" wrote: > The checkpoint process would look like this: > - make a new file in the same directory > - copy the DB to the new file > - rename the new

Re: [sqlite] Problem with the new pointer-passing interface

Dominique. strcmp alone won't care if zPType is code segment or data segment. As is, this release won't need the dynamic linker to perfectly consolidate constants. Much ado. [You are also free to clone three very tiny API functions substituting integer/etc PType if you're in the mood to ship

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 10:09:04PM +0200, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 10:01 PM, Nico Williams > wrote: > > The main issue is that you can't tell when a transaction has begun or > > ended, so you can't tell when curr_val() should raise an error. You can

Re: [sqlite] sequencer

> > You're welcome. Thanks for posting this on github. Make sure there's a > license, preferably a nice and friendly one such as a BSD license, or > else put it in the public domain like SQLite3 is -- but it's your code, > so you do what you like with it. > I will put a BSD license and it will

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 11:53:05AM -0500, Nico Williams wrote: > WAL mode still ends up having no read concurrency when it's time to > checkpoint the WAL. The same would happen with this concept. I don't > think this works well. Speaking of which... and I know I've mentioned this before and I

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 01:59:07PM -0500, Nico Williams wrote: > The checkpoint process would look like this: > > - make a new file in the same directory > - copy the DB to the new file The copy would basically be copying all the live data as a single transaction on the new DB/WAL file. At

Re: [sqlite] sequencer

On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams wrote: > On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote: > > I programmed the currval using a temp table, but the performance dropped > > slightly > > > > sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 06:43:08PM +, Peter Da Silva wrote: > On 8/4/17, 1:28 PM, "sqlite-users on behalf of Nico Williams" wrote: > > [...] > > A log-structured database, like a log-structured file system? Yes. Filesystems and databases are each other's dual. SQLite3's WAL is already

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 07:20:00PM +, Hick Gunter wrote: > A "temp table" would only be visible in the session that creates it > and would not live past the end of the session. Sequences should be > persistent... This is about the H2 curr_val() semantics -- that it only works if you've

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 09:55:03PM +0200, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams wrote: > > In general I would say: pthread_once() (Unix) or InitOnceExecuteOnce() > > (WIN32). But here, a global in combination with CREATE TEMP TABLE IF > >

Re: [sqlite] sequencer

On Fri, Aug 4, 2017 at 6:24 PM, Nico Williams wrote: > On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote: > > Please find below the last source code, I removed the check on the table > > (NOT NULL on both sql_val and seq_inc) > > Yeah, I saw. I think this

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 08:04:38PM +0200, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 6:24 PM, Nico Williams wrote: > > On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote: > > > Now I would like to implement the seq_currval: > > we could implement it

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, 1:45 PM, "sqlite-users on behalf of Nico Williams" wrote: > SQLite3's WAL is already log-structured. The main DB file isn't. So SQLite3 > is a hybrid. But it doesn't have to be a hybrid. One issue I

Re: [sqlite] sequencer

I programmed the currval using a temp table, but the performance dropped slightly sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s eq1') from T; Run Time: real 25.837 user 23.446950 sys 0.171601 I create the temp

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 07:05:06PM +, Peter Da Silva wrote: > Step 2 seems rather expensive, even if you’re filtering out dead blocks in > the process. It's no more expensive than WAL checkpointing is today. You could always do what LMDB does to reuse free blocks in a DB and avoid having to

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 09:09:10PM +0200, Sylvain Pointeau wrote: > I programmed the currval using a temp table, but the performance dropped > slightly > > sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE > i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('s > eq1')

Re: [sqlite] sequencer

On Fri, Aug 4, 2017 at 10:01 PM, Nico Williams wrote: > On Fri, Aug 04, 2017 at 09:55:03PM +0200, Sylvain Pointeau wrote: > > On Fri, Aug 4, 2017 at 9:16 PM, Nico Williams > wrote: > > > In general I would say: pthread_once() (Unix) or

[sqlite] Fixed: Checkin d8637bad produces bad amalgamation

Thanks Dan, that fixed it. Also, the current head of trunk is just fine as well... --- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. >-Original Message- >From: sqlite-users [mailto:sqlite-users-

Re: [sqlite] LSM1 extension

On 8/4/17, Charles Leifer wrote: > > My excitement quickly turned to disappointment as I realized that the > extension is unusable for all practical purposes: > Yes, it needs work. That is why it is unannounced. -- D. Richard Hipp d...@sqlite.org

[sqlite] LSM1 extension

Hi, I was pleased to see that 3.20 contained the source for the LSM1 virtual table, even if not included in the official announcements. My excitement quickly turned to disappointment as I realized that the extension is unusable for all practical purposes: - Single-key equality lookups are

Re: [sqlite] LSM1 extension

Right -- my question is still unanswered as to why the code was merged in. I have tremendous respect for this project and everyone involved in it, so please understand I'm not trying to call into question your decisions -- I'd just like clarification as to why this was included now? On Fri, Aug

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 8/4/17, Luc DAVID wrote: > Hello, > > I was thinking about a possible solution for sqlite "only single writer > is allowed at the same time" and database lock. > > sqlite has WAL mode for better concurrency and this could maybe be used > to extend the number of writters:

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On 4 Aug 2017, at 11:43am, Luc DAVID wrote: > sqlite was not designed for this kind of access but It would be great to have > a higher level of concurrency The problem with these things is that you have SQLite trying to read the minds of he programmer and user.

Re: [sqlite] sequencer

On Fri, Aug 4, 2017 at 9:21 AM, Sylvain Pointeau wrote: > On Fri, Aug 4, 2017 at 7:41 AM, petern > wrote: > >> Sylvain, are you happy with the performance? >> >> Maybe you are using it differently but, from my tests, the DEFAULT clause

Re: [sqlite] sequencer

> On Fri, Aug 4, 2017 at 7:41 AM, petern >> wrote: >> >>> Sylvain, are you happy with the performance? >>> >>> Maybe you are using it differently but, from my tests, the DEFAULT clause >>> is ignored for PRIMARY KEY columns. I had to use an ordinary column >>> with

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, 4 Aug 2017, Dominique Devienne wrote: I really don't see what's controversial with Matt's request :) It's not like load-extension is a performance-critical operation, that trying an extra load is that expensive. And the security consideration that an "attacker" could make it load his

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On 8/4/17, 8:29 AM, "sqlite-users on behalf of Bob Friesenhahn" wrote: > Lazy programmers who request such things are of the same ilk which use > programming practices resulting in SQL injection attacks.

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 4, 2017 at 8:11 AM, Nico Williams wrote: > On Fri, Aug 04, 2017 at 10:17:33AM +0200, Dominique Devienne wrote: > > On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams > wrote: > > > You're mistaken. > > > > > > lib.so is NOT "the default

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, 4 Aug 2017, Peter Da Silva wrote: On 8/4/17, 8:29 AM, "sqlite-users on behalf of Bob Friesenhahn" wrote: Lazy programmers who request such things are of the same ilk which use programming

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 09:33:31AM +0200, Eric Grange wrote: > The main problem with multiple writers would be in conflict resolution, > locking and deadlocks. There is plenty in the literature about this. You have to code more defensively, you may need things like "FOR UPDATE", etc. > Imagine

Re: [sqlite] sequencer

About the H2 test. That tester actually uses 10e6 is 10 x 10^6, 10 million rows. When I wrote you I forgot I bumped it up to 10 million since 1 million was only half a second on the native case. Give that a try on H2 and you'll see 22s goes to above 200s. On Fri, Aug 4, 2017 at 4:19 AM,

Re: [sqlite] Problem with the new pointer-passing interface

On Fri, Aug 4, 2017 at 11:37 AM, Ulrich Telle wrote: > Regarding the development of SQLite extensions (UDFs) and using the > pointer-binding interface to communicate between different extensions (AFAIK one of the reasons to introduce the new pointer-binding interface) > the

Re: [sqlite] sequencer

On Fri, Aug 4, 2017 at 6:03 PM, petern wrote: > About the H2 test. That tester actually uses 10e6 is 10 x 10^6, 10 million > rows. When I wrote you I forgot I bumped it up to 10 million since 1 > million was only half a second on the native case. Give that a try

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 06:05:53AM +, Hick Gunter wrote: > >Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im > >Auftrag von Nico Williams > >But loadable modules are almost never meant to be used that way. > >They're usually meant to be used only through dlopen() and

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 10:17:33AM +0200, Dominique Devienne wrote: > On Fri, Aug 4, 2017 at 2:46 AM, Nico Williams wrote: > > You're mistaken. > > > > lib.so is NOT "the default naming scheme on many *nix platforms". > > > > lib.so is the naming scheme when you want the

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 08:20:10AM -0700, J Decker wrote: > On Fri, Aug 4, 2017 at 8:11 AM, Nico Williams wrote: > > No, see, the ".so"/".dll" suffix is used in all cases, and it varies by > > platform, so it's best if SQLite3 adds it so you can keep your code more > >

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 08:50:05AM +0200, Luc DAVID wrote: > sqlite has WAL mode for better concurrency and this could maybe be used to > extend the number of writters: > > Do you think it would be possible to create a > MyDb.WAL001...MyDb.WAL.002...MyDb.WAL.nnn when a write operation is >

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

> Le 4 août 2017 à 14:15, Richard Hipp a écrit : > > Another alternative is the newer server-process-edition branch > (https://sqlite.org/src/timeline?n=all=server-process-edition) which > you can read about here: > https://sqlite.org/src/artifact/0c6bc6f55191b690 Looks

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 03:35:36AM +, Sylvain Pointeau wrote: > Le ven. 4 août 2017 à 02:42, Nico Williams a écrit : > > > sqlite3_prepare_v2(db, "insert or replace into sp_sequence (seq_name, > > > seq_val, seq_inc) values (?, ?, ?)", -1, , 0); > > > > Should this

Re: [sqlite] Suggestion: Pre-release Snapshots versioning

On 8/4/17, jose isaias cabrera wrote: > > Right now, when I went to a machine that I had upgraded with a > snapshot, I saw that the version was 3.20.0. But when I compared the DLL > file size and date, they were different. It would be nice for pre-releases > to have

Re: [sqlite] sequencer

Please find below the last source code, I removed the check on the table (NOT NULL on both sql_val and seq_inc) sqlite> WITH T(i) AS (SELECT (1)i UNION ALL SELECT i+1 FROM T WHERE i<100) INSERT INTO seq_test(seq_num) SELECT seq_nextval('seq1') from T; Run Time: real 18.829 user 16.146103 sys

Re: [sqlite] Thinking about a way to extend the number of writers in WAL mode

On Fri, Aug 04, 2017 at 12:58:43PM +0100, Simon Slavin wrote: > The problem you’re trying to fix is one of the big problems with > distributed databases. Nobody has found a good solution for it yet. It's impossible to solve for the eventually-consistent types. You just have to explicitly

Re: [sqlite] sequencer

On Fri, Aug 04, 2017 at 06:06:55PM +0200, Sylvain Pointeau wrote: > Please find below the last source code, I removed the check on the table > (NOT NULL on both sql_val and seq_inc) Yeah, I saw. I think this is another argument for SQLite3 needing a strict-type mode! (I very much prefer strong

[sqlite] Suggestion: Pre-release Snapshots versioning

Greetings! Thanks for this wonderful tool! Dr. Hipp and team, thanks. A suggestion I have is that the pre-release snapshots have some type of versioning. Right now, when I went to a machine that I had upgraded with a snapshot, I saw that the version was 3.20.0. But when I compared the DLL

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 4, 2017 at 6:29 AM, Bob Friesenhahn < bfrie...@simple.dallas.tx.us> wrote: > On Fri, 4 Aug 2017, Dominique Devienne wrote: > >> >> I really don't see what's controversial with Matt's request :) >> >> It's not like load-extension is a performance-critical operation, that >> trying an

Re: [sqlite] Feature request: check for 'lib' prefix for load_extension()

On Fri, Aug 04, 2017 at 10:55:57AM -0500, Bob Friesenhahn wrote: > Sqlite does not really have a way to know if a module in the current > directory (the directory which just happened to be current when the request > was made) should be trusted. To be secure, sqlite should insist that the > load