[sqlite] Autoincrement with rollback

Hi, Is it a bug or feature that the autoincrement value is being reused when a rollback is issued? --- Begin --- sqlite> drop table if exists demo; sqlite> create table demo (id integer primary key autoincrement, value text); sqlite> begin transaction; sqlite> insert into demo (value) VALUES

Re: [sqlite] Autoincrement with rollback

Koen Van Exem wrote: > Is it a bug or feature that the autoincrement > value is being reused when a rollback is issued? > > The documentation on https://www.sqlite.org/autoinc.html is a bit unclear > > ... it says it prevents reuse of ROWIDs from previously deleted rows. Only a DELETE statement

Re: [sqlite] Autoincrement with rollback

--- Begin --- sqlite> drop table if exists demo; sqlite> create table demo (id integer primary key autoincrement, value text); sqlite> begin transaction; sqlite> insert into demo (value) VALUES ('value'); sqlite> select last_insert_rowid(); 1 sqlite> delete from demo where id = 1; sqlite>

Re: [sqlite] Autoincrement with rollback

> --- Begin --- > > sqlite> drop table if exists demo; > sqlite> create table demo (id integer primary key autoincrement, value > text); > sqlite> begin transaction; > sqlite> insert into demo (value) VALUES ('value'); > sqlite> select last_insert_rowid(); > 1 > sqlite> delete from demo where id

Re: [sqlite] Autoincrement with rollback

delete it and commit it... rollback is 'undo' and if anything was differen't it wouldn't be a very good undo. assign the key yourself? on failure keep incrementing? if it's supposed to have been inserted and deleted... then rollback is not the correct solution. On Tue, Nov 11, 2014 at 2:04 AM,

Re: [sqlite] Autoincrement with rollback

I find it a bit confusing because when you create a PRIMARY KEY AUTOINCREMENT then a table named sqlite_sequence is created. According to the SQL (2003) standard multiple sessions are guaranteed to allocate distinct sequence values. (even when rollbacks are involved) 2014-11-11 11:14 GMT+01:00

Re: [sqlite] Autoincrement with rollback

On 2014-11-11 2:41 AM, Koen Van Exem wrote: I find it a bit confusing because when you create a PRIMARY KEY AUTOINCREMENT then a table named sqlite_sequence is created. According to the SQL (2003) standard multiple sessions are guaranteed to allocate distinct sequence values. (even when

[sqlite] Multiple threads reading different data

I'm accessing a database from multiple threads, where each thread has a separate database connection. When some specific event happens, I want the threads to evaluate the situation and act accordingly. I have setup a temporary trigger which fires at the event and which in turn calls a

Re: [sqlite] Autoincrement with rollback

Hi Darren, Thanks for explaining the internals. I already assumed by my 2 small experiments that this was the case but it's nice to have it confirmed. With kind regards, Koen 2014-11-11 12:05 GMT+01:00 Darren Duncan : > On 2014-11-11 2:41 AM, Koen Van Exem wrote: >

Re: [sqlite] Multiple threads reading different data

Daniel Polski wrote: > this maybe has to do with me using WAL mode, and that the update is > not yet "processed enough" for the other threads to fetch the new > data (so they still selects the old data), even though the trigger is > set to fire "after update". Is this maybe the case? Yes; other

Re: [sqlite] Multiple threads reading different data

This is the I (Isolation) in ACID. WAL mode allows the writer to pretend that no transactions are outstanding and begin and even commit a write transaction. This change sits in the Wal file until all prior transactions have been completed and the change can be copied to the db. As long as your

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

> Okay, for my simplified example analyze does improve the times. But for our application this does not help, and it also harmed a couple of other queries. I'll have to see if I can make a better example schema showing the problem because I can't

Re: [sqlite] Change UPDATE with JOIN sintax

I works like a charm! Thanks for the answer and for the explanation -- View this message in context: http://sqlite.1065341.n5.nabble.com/Change-UPDATE-with-JOIN-sintax-tp79105p79153.html Sent from the SQLite mailing list archive at Nabble.com. ___

Re: [sqlite] Discrepancy with indexing and WHERE clause with AND/OR

On 10 Nov 2014, at 10:55pm, RP McMurphy wrote: >> > > Okay, for my simplified example analyze does improve the times. But for our > application this does not help, and it also harmed a couple of other queries. If you can provide

[sqlite] Triggers and CTE's

All, Is it possible to have CTE's within triggers? The way I read the 'SQL As Understood By SQLite', one can, but I could be misinterpreting it. My DDL for my trigger is as follows: CREATE TRIGGER t_populate_zweeks AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1 BEGIN DELETE FROM

Re: [sqlite] Triggers and CTE's

On 11 Nov 2014, at 11:15pm, Ben Newberg wrote: > WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks > limit 10) > INSERT INTO zWeeks (Week) select wk from Weeks; Just use 10 INSERT commands. I don't know what's causing your error message, but your

Re: [sqlite] Triggers and CTE's

Thanks Simon. The 10 is just an arbitrary value I chose for this example. The user actually determines the value at run-time, so this value could be any integer. I have a way to settle that, if only I could figure out how I can get this trigger working. BEN On Tue, Nov 11, 2014 at 5:41 PM,

Re: [sqlite] Triggers and CTE's

On 11/11/2014 6:15 PM, Ben Newberg wrote: CREATE TRIGGER t_populate_zweeks AFTER UPDATE ON zSPs WHEN new.Procedure = 6 AND new.Flag = 1 BEGIN DELETE FROM zWeeks; WITH RECURSIVE Weeks(wk) as (select 1 union all select wk + 1 from Weeks limit 10) INSERT INTO zWeeks (Week) select wk from Weeks;

Re: [sqlite] Triggers and CTE's

On 11 Nov 2014, at 11:59pm, Ben Newberg wrote: > The 10 is just an arbitrary value I chose for this example. The user > actually determines the value at run-time, so this value could be any > integer. I have a way to settle that, if only I could figure out how I can > get

Re: [sqlite] Triggers and CTE's

On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik wrote: On 11/11/2014 6:15 PM, Ben Newberg wrote: > > Looks like a bug to me. The statement works standalone, but not within a > trigger. > There are many limitations and restrictions on the statements inside of triggers. See

Re: [sqlite] Triggers and CTE's

Thanks everyone. I will have the programming language do the work on this one instead of going the trigger route. On Nov 11, 2014 7:39 PM, "Richard Hipp" wrote: > On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik > wrote: > > On 11/11/2014 6:15 PM, Ben Newberg

Re: [sqlite] Triggers and CTE's

On 11/11/2014 8:37 PM, Richard Hipp wrote: On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik wrote: On 11/11/2014 6:15 PM, Ben Newberg wrote: Looks like a bug to me. The statement works standalone, but not within a trigger. There are many limitations and restrictions on

Re: [sqlite] Triggers and CTE's

On Tue, Nov 11, 2014 at 9:01 PM, Igor Tandetnik wrote: > On 11/11/2014 8:37 PM, Richard Hipp wrote: > >> On Tue, Nov 11, 2014 at 8:22 PM, Igor Tandetnik >> wrote: >> >> On 11/11/2014 6:15 PM, Ben Newberg wrote: >> >>> >>> Looks like a bug to me. The