Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

Yes, manually buffering the resultset or buffering the updates is of course
a possible workaround.
But I would like to avoid that as much as possible.

Another approach is to use limit/offset and then page through the resultset
to control the amount of buffering needed.
But this just feels like a lot of complexity for such simple task.

Anyway - since WAL mode seems to work I will stick with that for now.

cheers,
Torsten

On Mon, May 28, 2018 at 9:10 PM Simon Slavin  wrote:

> On 28 May 2018, at 7:56pm, Torsten Curdt  wrote:
>
> > Just to clarify: I have a single thread - so intermixing the stepping
> > through a resultset and doing an update requires WAL mode but should be
> > fine. Correct?
>
> Yes, this should work fine.  Obviously, one thread is not going to be
> trying to do two database accesses at the same time, especially since your
> software design uses the result from one SELECT row in order to figure out
> what UPDATE to issue.
>
> You could, course, build up an array of pairs in memory while doing the
> SELECT, then consult the array to create all the UPDATE commands once you
> have finalized the SELECT.  If you do do this don't forget to surround the
> UPDATE commands with BEGIN;...COMMIT; .
>
> One technique I have used in a similar situation was to write all my
> UPDATE commands to a long text buffer.  This was in an unusual situation
> where I needed to get the SELECT done as quickly as possible to avoid
> locking up lab equipment.  So the software figured out all the UPDATE
> commands and concatenated them in a text variable:
>
> UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...
>
> The buffer could get as big as half a megabyte or so.  Then I
> sqlite3_reset() the SELECT command.  Then I submit the entire piece of text
> as one long parameter to sqlite3_exec().  Worked perfectly, very quickly,
> and didn't take up much more memory than storing the parameters in an array.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Final preparations for the release of System.Data.SQLite v1.0.109.0 have begun...

[Joe Mistachkin]

If you have any issues with the current trunk code, please
report them via this mailing list (and/or by creating a ticket
on "https://system.data.sqlite.org/;) prior to next Monday,
June 4th.

Thanks.

--
Joe Mistachkin

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

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Simon Slavin]

On 28 May 2018, at 7:56pm, Torsten Curdt  wrote:

> Just to clarify: I have a single thread - so intermixing the stepping
> through a resultset and doing an update requires WAL mode but should be
> fine. Correct?

Yes, this should work fine.  Obviously, one thread is not going to be trying to 
do two database accesses at the same time, especially since your software 
design uses the result from one SELECT row in order to figure out what UPDATE 
to issue.

You could, course, build up an array of pairs in memory while doing the SELECT, 
then consult the array to create all the UPDATE commands once you have 
finalized the SELECT.  If you do do this don't forget to surround the UPDATE 
commands with BEGIN;...COMMIT; .

One technique I have used in a similar situation was to write all my UPDATE 
commands to a long text buffer.  This was in an unusual situation where I 
needed to get the SELECT done as quickly as possible to avoid locking up lab 
equipment.  So the software figured out all the UPDATE commands and 
concatenated them in a text variable:

UPDATE for_row_1;UPDATE for_row_2;UPDATE for_row_3;...

The buffer could get as big as half a megabyte or so.  Then I sqlite3_reset() 
the SELECT command.  Then I submit the entire piece of text as one long 
parameter to sqlite3_exec().  Worked perfectly, very quickly, and didn't take 
up much more memory than storing the parameters in an array.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

Just to clarify: I have a single thread - so intermixing the stepping
through a resultset and doing an update requires WAL mode but should be
fine. Correct?

On Mon, May 28, 2018 at 11:01 AM Hick Gunter  wrote:

> As long as you are and remain in serialized mode, you can re-use the same
> connection from different threads.
>
> If you can guarantee that each thread will have it's own, personal,
> connection, you may achieve a performance increase by using multithread
> mode.
>
> In single thread mode, all the checks are turned off and it is up to you
> to make sure that exactly 1 thread does all the calls to SQLite. This mode
> is fastest.
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Torsten Curdt
> Gesendet: Montag, 28. Mai 2018 10:38
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: Re: [sqlite] [EXTERNAL] Re: database locked on select
>
> Thanks for the suggestion but I feel that will just make things more
> complex.
> Right now I have one function that does it work and then decides whether
> to update or not.
> That way I would have to split that one function into two (new_data,
> needs_update)
> which is not so easy.
> Plus that also makes things less portable (not this really is a
> requirement though).
>
> WAL mode seems to solve this for me much better. At least I don't see any
> drawbacks yet.
> I am just wondering if having both statements on the same connection is a
> valid way of using this.
>
> cheers,
> Torsten
>
> On Mon, May 28, 2018 at 10:25 AM Hick Gunter  wrote:
>
> > It is possible to bring an external resource into SQlite by writing
> > user-defined functions and/or virtual tables. This would allow
> > something
> > like:
> >
> > UPDATE  set () = new_data() where
> > needs_update();
> >
> > With the UDF returning 1 (TRUE) if the current row (identified by the
> > arguments) needs an update and the row-valued function new_data()
> > returns the new values.
> >
> >
> > -Ursprüngliche Nachricht-
> > Von: sqlite-users
> > [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> > Im Auftrag von Torsten Curdt
> > Gesendet: Montag, 28. Mai 2018 10:04
> > An: sqlite-users@mailinglists.sqlite.org
> > Betreff: [EXTERNAL] Re: [sqlite] database locked on select
> >
> > I have to query an external resource for each row.
> > Unfortunately nothing I can do in a single update query.
> > Would mean keeping a lot of data manually in memory.
> >
> > On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
> > abrozynieprzelozy314...@gmail.com> wrote:
> >
> > > BTW why not to update all rows by single update query?
> > >
> > > 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > > > I am doing a select, then iterate through the resultset and on
> > > > each row call update on that row.
> > > > I am using the golang driver and ran into the issue that on the
> > > > update
> > > the
> > > > database is still locked from the select.
> > > >
> > > >   https://github.com/mattn/go-sqlite3/issues/569
> > > >
> > > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > > > and
> > > IIUC
> > > > these types of updates should be possible since version 3.3.7
> > > > though
> > > > -
> > > and
> > > > I am using 3.19.3.
> > > >
> > > > Any suggestion on how to track down why the updates fail?
> > > >
> > > > cheers,
> > > > Torsten
> > > > ___
> > > > sqlite-users mailing list
> > > > sqlite-users@mailinglists.sqlite.org
> > > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use
> > > > rs
> > > >
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > 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
> >
> ___
> 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. 

Re: [sqlite] sqlite-users Digest, Vol 125, Issue 28

[Abhishek Ginani]

I asked the same question on stackoverflow and got the answer. Please have
a look here  -

*https://stackoverflow.com/questions/50551624/sqlite-is-creating-a-disk-file-for-in-memory-db
*

Thanks for your help.

On Mon 28 May, 2018, 10:26 PM dmp,  wrote:

>
> > I tried to create a shareable in-memory database as per the documentation
> >  provided on SQLite  Site. But I end up finding the solution to the
> > problem.
> >
> > *Here is my code in C#*:
> >
> >
> > var connectionString = "Data
> > Source=sharedmemdb;Mode=Memory;Cache=Shared";
> >
> >
> > using (var connection1 = new SQLiteConnection(connectionString)) {
> > connection1.Open();
>
> I know nothing of C# or the argument requirements for SQLiteConnection(),
> but did you try:
>
> var connectionString = "Data Source=:memory:;Mode=Memory;Cache=Shared";
>
> or
>
> var connectionString = "Data
> Source=file::memory:;Mode=Memory;Cache=Shared";
>
> per the documentation.
>
> danap.
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] sqlite-users Digest, Vol 125, Issue 28

[dmp]

> I tried to create a shareable in-memory database as per the documentation
>  provided on SQLite  Site. But I end up finding the solution to the
> problem.
>
> *Here is my code in C#*:
>
>
> var connectionString = "Data
> Source=sharedmemdb;Mode=Memory;Cache=Shared";
>
>
> using (var connection1 = new SQLiteConnection(connectionString)) {
> connection1.Open();

I know nothing of C# or the argument requirements for SQLiteConnection(),
but did you try:

var connectionString = "Data Source=:memory:;Mode=Memory;Cache=Shared";

or

var connectionString = "Data Source=file::memory:;Mode=Memory;Cache=Shared";

per the documentation.

danap.

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

Re: [sqlite] This is driving me nuts

[x]

> why, when you've got 16 GB ram, does a 6.4 GB vector cause any problems?

Jim, it was only 3.2 GB and it was compiled as 64 bit just in case you’re 
thinking 32.


>Either this statement is wrong, or you've misattributed the 4 GB of memory.

Rowan, I’ve misattributed. There’s a bug in the thread. As the data is assigned 
to the vector in the thread the available memory keeps decreasing but I’ve yet 
to determine how this is happening.

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

Re: [sqlite] This is driving me nuts

[Warren Young]

On May 28, 2018, at 3:35 AM, Rowan Worth  wrote:
> 
> On 28 May 2018 at 17:29, x  wrote:
> 
>> I’ve just discovered the thread in the original app decreases the
>> available memory by around 4 GB. Are they really that expensive?
> 
> A thread itself is not expensive in terms of memory.

If you keep to ~1 thread per CPU core, then yes, threads are cheap.  

However, some people advocate strategies like 1 thread per TCP connection in a 
networked server, so that if each thread takes 2 MiB for a stack and assorted 
other small bits of RAM for thread-local storage and such, you’ve limited 
yourself to under 2000 TCP connections on a 32-bit OS because you run out of 
addressable VM beyond that with a typical 2/2 GiB OS/userland split.

This is why you see so many languages and application frameworks moving to 
post-thread models, generically under the “green threads” umbrella: Erlang’s 
processes, Go’s goroutines, F#’s async mechanism, etc.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[Jim Callahan]

> why, when you've got 16 GB ram, does a 6.4 GB vector cause any problems?

Architecturally, 4 GB is like the sound barrier -- you may get turbulence
or a sonic boom.

Because 4 GB was the limit for 32 bit word size any residual 32 bit code or
32 bit assumptions anywhere in the stack (or CPU) could create issues.
The operating system attempts to present a unified linear address space,
but under the hood all sorts of kludges may exist.

Windows AWE exists because of 4 GB.
https://en.wikipedia.org/wiki/Address_Windowing_Extensions

Thus, from an architectural standpoint, I would try to avoid allocating 4
GB of RAM  to one object under ANY operating system, but if I had to do it
I would try Linux because that is what the supercomputers use
(supercomputers have big memory in addition to fast speed and tens of
thousands of cpus).
https://www.zdnet.com/article/linux-totally-dominates-supercomputers/

Twenty-five years ago developers were more concerned about Y2K, than beyond
4 GB.
To the extent anyone in the Windows NT world thought about 4 GB of RAM, it
was allocating memory
between the operating system and applications WITHIN 4 GB (ie. do we split
4 GB of RAM between opsys:apps 2:2 or 1:3).

Jim Callahan
Orlando, FL




On Mon, May 28, 2018 at 5:35 AM, Rowan Worth  wrote:

> On 28 May 2018 at 17:29, x  wrote:
>
> > I’ve just discovered the thread in the original app decreases the
> > available memory by around 4 GB. Are they really that expensive?
>
>
> A thread itself is not expensive in terms of memory.
>
>
> > It has very little data of its own
>
>
> Either this statement is wrong, or you've misattributed the 4 GB of memory.
> -Rowan
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[Rowan Worth]

On 28 May 2018 at 17:29, x  wrote:

> I’ve just discovered the thread in the original app decreases the
> available memory by around 4 GB. Are they really that expensive?


A thread itself is not expensive in terms of memory.


> It has very little data of its own


Either this statement is wrong, or you've misattributed the 4 GB of memory.
-Rowan
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

I’ve just discovered the thread in the original app decreases the available 
memory by around 4 GB. Are they really that expensive? It has very little data 
of its own and just calls a function declared in the main thread.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] This is driving me nuts

[x]

Many thanks for your efforts Abrozy. I will attempt to understand it later but 
the artificially created ‘Big’ table is something of a worst case scenario for 
me so I’m not sure it would be the best use of my time. If THEY can’t get 
memory management right who am I to think I will.



I have to say this has severely dented my confidence though. The example 
console app is the thin edge of a much bigger wedge. The original problem (part 
of a relatively small c++ sqlite wrapper test app) is more complex because the 
storing of RowIDs was taking place in a separate thread. Increasing the size of 
the vector in that (112 million to 400 million) increases the running time of 
the thread from 22 secs to around 44. That’s the only change required to bring 
about that increase. How can a 3+ GB vector cause such mayhem on a pc with 16 
GB RAM?






From: sqlite-users  on behalf of 
Abroży Nieprzełoży 
Sent: Monday, May 28, 2018 1:26:13 AM
To: SQLite mailing list
Subject: Re: [sqlite] This is driving me nuts

Alternatively you can alloc physical memory using AWE mechanism.
AWE requires SeLockMemoryPrivilege, so you may have to run the
application on an administator account (or other account with
sufficient rigths).
When using AWE there is no need to increase working set size because
AWE pages are not included in the working set.

---
int enableLockMemoryPrivilege()
{
int rc;
DWORD err;
HANDLE hToken;
TOKEN_PRIVILEGES privilege;
memset(, 0, sizeof(privilege));
privilege.PrivilegeCount = 1;
privilege.Privileges[0].Attributes = SE_PRIVILEGE_ENABLED;
rc = LookupPrivilegeValue(NULL, SE_LOCK_MEMORY_NAME,
[0].Luid);
if (!rc)
return -1;
rc = OpenProcessToken(GetCurrentProcess(), TOKEN_ADJUST_PRIVILEGES, 
);
if (!rc)
return -2;
rc = AdjustTokenPrivileges(hToken, 0, , sizeof(privilege),
NULL, NULL);
err = GetLastError();
CloseHandle(hToken);
if (!rc || err)
return -3;
return 0;
}

struct PhysMem
{
void* pMem;
SIZE_T sMem;
ULONG_PTR nPages;
ULONG_PTR* aPages;
};

void unmapAndFreePhysicalPages(struct PhysMem* mem)
{
if(mem->pMem)
VirtualFree(mem->pMem, 0, MEM_RELEASE);
if (mem->aPages)
{
FreeUserPhysicalPages(GetCurrentProcess(), >nPages, 
mem->aPages);
free(mem->aPages);
}
memset(mem, 0, sizeof(*mem));
}

int allocAndMapPhysicalPages(struct PhysMem* mem, SIZE_T size)
{
int rc;
SYSTEM_INFO sysInfo;
memset(mem, 0, sizeof(*mem));
GetNativeSystemInfo();
mem->nPages = size / sysInfo.dwPageSize;
if (size % sysInfo.dwPageSize > 0)
mem->nPages++;
size = mem->nPages * sysInfo.dwPageSize;
mem->aPages = (ULONG_PTR*)calloc(mem->nPages, sizeof(ULONG_PTR));
if (!mem->aPages)
return -1;
rc = AllocateUserPhysicalPages(GetCurrentProcess(), >nPages, 
mem->aPages);
if (!rc)
{
free(mem->aPages);
memset(mem, 0, sizeof(*mem));
return -2;
}
mem->sMem = size = mem->nPages * sysInfo.dwPageSize;
mem->pMem = VirtualAlloc(NULL, mem->sMem, MEM_RESERVE | MEM_PHYSICAL,
PAGE_READWRITE);
if (!mem->pMem)
{
unmapAndFreePhysicalPages(mem);
return -3;
}
rc = MapUserPhysicalPages(mem->pMem, mem->nPages, mem->aPages);
if (!rc)
{
unmapAndFreePhysicalPages(mem);
return -4;
}
return 0;
}

int configSqlitePhysMemory()
{
int rc;
struct PhysMem mem;
rc = allocAndMapPhysicalPages(, 64 * 1024 * 1024);
if (rc < 0)
return -1;
rc = sqlite3_config(SQLITE_CONFIG_HEAP, mem.pMem, (int)mem.sMem, 64);
if (rc != SQLITE_OK)
{
unmapAndFreePhysicalPages();
return -2;
}
return 0;
}

int enableLockMemPrivilegeAndConfigSqliteMemory()
{
int rc;
rc = enableLockMemoryPrivilege();
if (rc < 0)
return -1;
rc = configSqlitePhysMemory();
if (rc < 0)
return -2;
return 0;
}
---



2018-05-28 0:23 GMT+02:00, Abroży Nieprzełoży
:
> Firstly you have to enable SeIncreaseWorkingSetPrivilege - to do this
> you may use the function enableIncreaseWorkingSetPrivilege listed
> below, it should return 0 on success - then you can use
> SetProcessWorkingSetSize(GetCurrentProcess(), *NewMinSize*, *NewMaxSize*);
> Working set sizes must be 

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Hick Gunter]

As long as you are and remain in serialized mode, you can re-use the same 
connection from different threads.

If you can guarantee that each thread will have it's own, personal, connection, 
you may achieve a performance increase by using multithread mode.

In single thread mode, all the checks are turned off and it is up to you to 
make sure that exactly 1 thread does all the calls to SQLite. This mode is 
fastest.

-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Torsten Curdt
Gesendet: Montag, 28. Mai 2018 10:38
An: sqlite-users@mailinglists.sqlite.org
Betreff: Re: [sqlite] [EXTERNAL] Re: database locked on select

Thanks for the suggestion but I feel that will just make things more complex.
Right now I have one function that does it work and then decides whether to 
update or not.
That way I would have to split that one function into two (new_data,
needs_update)
which is not so easy.
Plus that also makes things less portable (not this really is a requirement 
though).

WAL mode seems to solve this for me much better. At least I don't see any 
drawbacks yet.
I am just wondering if having both statements on the same connection is a valid 
way of using this.

cheers,
Torsten

On Mon, May 28, 2018 at 10:25 AM Hick Gunter  wrote:

> It is possible to bring an external resource into SQlite by writing
> user-defined functions and/or virtual tables. This would allow
> something
> like:
>
> UPDATE  set () = new_data() where
> needs_update();
>
> With the UDF returning 1 (TRUE) if the current row (identified by the
> arguments) needs an update and the row-valued function new_data()
> returns the new values.
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users
> [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Torsten Curdt
> Gesendet: Montag, 28. Mai 2018 10:04
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] database locked on select
>
> I have to query an external resource for each row.
> Unfortunately nothing I can do in a single update query.
> Would mean keeping a lot of data manually in memory.
>
> On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
> abrozynieprzelozy314...@gmail.com> wrote:
>
> > BTW why not to update all rows by single update query?
> >
> > 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > > I am doing a select, then iterate through the resultset and on
> > > each row call update on that row.
> > > I am using the golang driver and ran into the issue that on the
> > > update
> > the
> > > database is still locked from the select.
> > >
> > >   https://github.com/mattn/go-sqlite3/issues/569
> > >
> > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > > and
> > IIUC
> > > these types of updates should be possible since version 3.3.7
> > > though
> > > -
> > and
> > > I am using 3.19.3.
> > >
> > > Any suggestion on how to track down why the updates fail?
> > >
> > > cheers,
> > > Torsten
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-use
> > > rs
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> 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
>
___
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

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Torsten Curdt]

Thanks for the suggestion but I feel that will just make things more
complex.
Right now I have one function that does it work and then decides whether to
update or not.
That way I would have to split that one function into two (new_data,
needs_update)
which is not so easy.
Plus that also makes things less portable (not this really is a requirement
though).

WAL mode seems to solve this for me much better. At least I don't see any
drawbacks yet.
I am just wondering if having both statements on the same connection is a
valid way of using this.

cheers,
Torsten

On Mon, May 28, 2018 at 10:25 AM Hick Gunter  wrote:

> It is possible to bring an external resource into SQlite by writing
> user-defined functions and/or virtual tables. This would allow something
> like:
>
> UPDATE  set () = new_data() where
> needs_update();
>
> With the UDF returning 1 (TRUE) if the current row (identified by the
> arguments) needs an update and the row-valued function new_data() returns
> the new values.
>
>
> -Ursprüngliche Nachricht-
> Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org]
> Im Auftrag von Torsten Curdt
> Gesendet: Montag, 28. Mai 2018 10:04
> An: sqlite-users@mailinglists.sqlite.org
> Betreff: [EXTERNAL] Re: [sqlite] database locked on select
>
> I have to query an external resource for each row.
> Unfortunately nothing I can do in a single update query.
> Would mean keeping a lot of data manually in memory.
>
> On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
> abrozynieprzelozy314...@gmail.com> wrote:
>
> > BTW why not to update all rows by single update query?
> >
> > 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > > I am doing a select, then iterate through the resultset and on each
> > > row call update on that row.
> > > I am using the golang driver and ran into the issue that on the
> > > update
> > the
> > > database is still locked from the select.
> > >
> > >   https://github.com/mattn/go-sqlite3/issues/569
> > >
> > > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > > and
> > IIUC
> > > these types of updates should be possible since version 3.3.7 though
> > > -
> > and
> > > I am using 3.19.3.
> > >
> > > Any suggestion on how to track down why the updates fail?
> > >
> > > cheers,
> > > Torsten
> > > ___
> > > sqlite-users mailing list
> > > sqlite-users@mailinglists.sqlite.org
> > > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > >
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> 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
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] [EXTERNAL] Re: database locked on select

[Hick Gunter]

It is possible to bring an external resource into SQlite by writing 
user-defined functions and/or virtual tables. This would allow something like:

UPDATE  set () = new_data() where 
needs_update();

With the UDF returning 1 (TRUE) if the current row (identified by the 
arguments) needs an update and the row-valued function new_data() returns the 
new values.


-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im 
Auftrag von Torsten Curdt
Gesendet: Montag, 28. Mai 2018 10:04
An: sqlite-users@mailinglists.sqlite.org
Betreff: [EXTERNAL] Re: [sqlite] database locked on select

I have to query an external resource for each row.
Unfortunately nothing I can do in a single update query.
Would mean keeping a lot of data manually in memory.

On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży < 
abrozynieprzelozy314...@gmail.com> wrote:

> BTW why not to update all rows by single update query?
>
> 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > I am doing a select, then iterate through the resultset and on each
> > row call update on that row.
> > I am using the golang driver and ran into the issue that on the
> > update
> the
> > database is still locked from the select.
> >
> >   https://github.com/mattn/go-sqlite3/issues/569
> >
> > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked
> > and
> IIUC
> > these types of updates should be possible since version 3.3.7 though
> > -
> and
> > I am using 3.19.3.
> >
> > Any suggestion on how to track down why the updates fail?
> >
> > cheers,
> > Torsten
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
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

Re: [sqlite] database locked on select

[Torsten Curdt]

I have to query an external resource for each row.
Unfortunately nothing I can do in a single update query.
Would mean keeping a lot of data manually in memory.

On Mon, May 28, 2018 at 2:33 AM Abroży Nieprzełoży <
abrozynieprzelozy314...@gmail.com> wrote:

> BTW why not to update all rows by single update query?
>
> 2018-05-27 20:30 GMT+02:00, Torsten Curdt :
> > I am doing a select, then iterate through the resultset and on each row
> > call update on that row.
> > I am using the golang driver and ran into the issue that on the update
> the
> > database is still locked from the select.
> >
> >   https://github.com/mattn/go-sqlite3/issues/569
> >
> > I have read http://www.sqlite.org/cvstrac/wiki?p=DatabaseIsLocked and
> IIUC
> > these types of updates should be possible since version 3.3.7 though -
> and
> > I am using 3.19.3.
> >
> > Any suggestion on how to track down why the updates fail?
> >
> > cheers,
> > Torsten
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users