Virtual table row deletition on trigger

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

Virtual table row deletition on trigger

Tibor Balog
Hello,

I run into a little anoyance regarding virtual table.

I have a contentless table:

CREATE VIRTUAL TABLE "OCR" using fts5 (content='',FullText)

bind with a trigger for row deletition:

CREATE TRIGGER "ART_AD" AFTER DELETE ON "ART" BEGIN INSERT INTO "OCR" ("OCR",rowid) VALUES('DELETE',old.rowid);END

It works as advertised however if I am deleting rows in transaction above ~20.000 rows put SQLite himself in “busy” state.

Is this a known limitation ?
That the virtual table row deletition triggered on normal table deletition interfers with the normal table in transaction?

Info:
Im not using VAL, jurnal is in memory.

Latest build.

Thank You,
Tibor
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Virtual table row deletition on trigger

Tibor Balog
All right,

I think I was missing a potentialy important info.

I am deleting rows as ranges.

It is up to SQLite how to do the deletation.

I am pushing ranges in a loop in the transaction.

Than the loop breaks signaling "busy" or "overwhelmed" state.

Without the transaction frame it is working fine.

-----Ursprüngliche Nachricht-----
From: Tibor Balog
Sent: Friday, December 8, 2017 7:11 PM
To: SQLite
Subject: [sqlite] Virtual table row deletition on trigger

Hello,

I run into a little anoyance regarding virtual table.

I have a contentless table:

CREATE VIRTUAL TABLE "OCR" using fts5 (content='',FullText)

bind with a trigger for row deletition:

CREATE TRIGGER "ART_AD" AFTER DELETE ON "ART" BEGIN INSERT INTO "OCR"
("OCR",rowid) VALUES('DELETE',old.rowid);END

It works as advertised however if I am deleting rows in transaction above
~20.000 rows put SQLite himself in “busy” state.

Is this a known limitation ?
That the virtual table row deletition triggered on normal table deletition
interfers with the normal table in transaction?

Info:
Im not using VAL, jurnal is in memory.

Latest build.

Thank You,
Tibor
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users 

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Virtual table row deletition on trigger

Tibor Balog
In reply to this post by Tibor Balog
Hi,

I have realized that this quetion went awry so I give it a second run.

My scenario:

    Enviroment:

DotNet, no 3d party library just pinvoke.
SQLite:
Sqlite library version:3022000
-COMPILER=msvc-1911
-ENABLE_FTS5
-LIKE_DOESNT_MATCH_BLOBS
-MAX_EXPR_DEPTH=0
-OMIT_DECLTYPE
-OMIT_DEPRECATED
-OMIT_PROGRESS_CALLBACK
-OMIT_SHARED_CACHE
-TEMP_STORE=3
-THREADSAFE=1

Using:Synchronous connection.
"PRAGMA journal_mode=MEMORY"

    My assumption:
Opening just one (Read/Write|Create) conection SQLite should never signal
busy to that connection after it successfuly acquired it.

Since you can use thight loops and it should be up to SQLite how long its
takes for the individual command execution and how many threads its opens up
to accomplish the job at hand,
it should come back sync. otherwise it will break the loop forcing to launch
a signal handling thread.

    My task setup:
A table "ART"
+
A virtual fts5 contentless table:
CREATE VIRTUAL TABLE "OCR" using fts5 (content='',FullText)
+
Trigger:
CREATE TRIGGER "ART_AD" AFTER DELETE ON "ART" BEGIN INSERT INTO "OCR"
("OCR",rowid) VALUES('DELETE',old.rowid);END
to keep the virtual table sync with row deletation of "ART"

    My task.:
0 Collecting user input from grid selection into a list of SQL DELETE range
statements (BETWEEN value1 AND value2;)

1 Open connection
2 BEGIN_TRANSACTION
3 the delete loop on the list.
4 COMIT

SQLite breaks the loop with signal "busy" if one or more of the delete
ranges are bigger than ~20.000 rows.

Without the transaction frame seems to be ok.

    My quetion:
Is my "assumption" above wrong and I allways have to take care of eventual
interraptions from SQLite?
Is this a limitation because of the trigger forces a separate thread for the
virtual table, which is maybe not that thightly integrated?
Is this a bug?

KR,
Tibor

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users