Is it a bug or "as designed"?

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

Is it a bug or "as designed"?

John Found

I have two tables with foreign constraint:

    create table A ( id primary key not null, single_data );
    create table B ( aid references A(id) on delete cascade, multi_data);

Now I am periodically inserting data in A and B with the following queries:

    insert or replace into A values (?1, ?2);
    insert into B values (?1, ?2);

Unfortunately, after replacing some row in A, all previously inserted rows in B got deleted, even if the value of ID does not changes. Here SQLite works exactly as it first deletes the conflicting row from A and then inserting new.

Now, if I define the table B without "on delete":

    create table B ( aid references A(id), multi_data);

"insert or replace" succeed without deleting the old rows from B.

In my opinion this behaviour is not consistent. The consistent solutions IMHO are two:

1. "insert or replace" succeed both in the first and in the second case without deleting rows from B,

2. "insert or delete" succeed in the first case, deleting all constrained rows from B and fails in the second case with "FOREIGN KEY constraint failed".

The first case IMHO is more intuitive and natural. At least this was my expectation when writing the code.


--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
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: Is it a bug or "as designed"?

Jay Kreibich


Foreign keys enforcement can get tricky depending on the enforcement policy, transactions, and a lot of things.  I don’t have enough experience to comment on that fully.


I will say this, however, because it is a common mistake with a lot of different aspects of database behavior:

        “Insert or replace” is NOT “insert or update.”

Insert always inserts a new row.  The only question is if it deletes conflicting rows (yes, plural) first.



With that in mind, the FK concept gets a little fuzzy.  It is a new row that just happens to have the same ID as an old row.  Does that mean the FK in B should still reference it?  I’d say no, because it is a brand new row… a different entity. If you want B to keep referencing the existing row, then update the row that is already there, don’t delete it and insert a new row on top of it.  I’d look into deferrable constraints to see if the behavior you’re looking for is supported.  Hopefully others can comment more on that.

 -j





> On Mar 9, 2018, at 1:11 PM, John Found <[hidden email]> wrote:
>
>
> I have two tables with foreign constraint:
>
>    create table A ( id primary key not null, single_data );
>    create table B ( aid references A(id) on delete cascade, multi_data);
>
> Now I am periodically inserting data in A and B with the following queries:
>
>    insert or replace into A values (?1, ?2);
>    insert into B values (?1, ?2);
>
> Unfortunately, after replacing some row in A, all previously inserted rows in B got deleted, even if the value of ID does not changes. Here SQLite works exactly as it first deletes the conflicting row from A and then inserting new.
>
> Now, if I define the table B without "on delete":
>
>    create table B ( aid references A(id), multi_data);
>
> "insert or replace" succeed without deleting the old rows from B.
>
> In my opinion this behaviour is not consistent. The consistent solutions IMHO are two:
>
> 1. "insert or replace" succeed both in the first and in the second case without deleting rows from B,
>
> 2. "insert or delete" succeed in the first case, deleting all constrained rows from B and fails in the second case with "FOREIGN KEY constraint failed".
>
> The first case IMHO is more intuitive and natural. At least this was my expectation when writing the code.
>
>
> --
> http://fresh.flatassembler.net
> http://asm32.info
> John Found <[hidden email]>
> _______________________________________________
> 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: Is it a bug or "as designed"?

Simon Slavin-3
In reply to this post by John Found
On 9 Mar 2018, at 7:11pm, John Found <[hidden email]> wrote:

> "insert or replace" succeed without deleting the old rows from B.

"replace" means "delete the original row, then insert a new one".

In your code, figure out whether you need INSERT or UPDATE, and do the appropriate one.

Simon.
_______________________________________________
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: Is it a bug or "as designed"?

John Found
On Fri, 9 Mar 2018 19:42:19 +0000
Simon Slavin <[hidden email]> wrote:

> On 9 Mar 2018, at 7:11pm, John Found <[hidden email]> wrote:
>
> > "insert or replace" succeed without deleting the old rows from B.
>
> "replace" means "delete the original row, then insert a new one".
>
> In your code, figure out whether you need INSERT or UPDATE, and do the appropriate one.
>

You are right. And Jay Kreibich in his post above. But then the second solution from my post should be the correct behavior.

In the current implementation "insert or replace" behave as the foreign constraint is deferred.
But according to documentation, all foreign constraints in SQLite are immediate by default.


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


--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
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: Is it a bug or "as designed"?

Jay Kreibich
In reply to this post by Simon Slavin-3

> On Mar 9, 2018, at 1:42 PM, Simon Slavin <[hidden email]> wrote:
>

> "replace" means "delete the original row, then insert a new one”.

More properly, it means “delete any and all rows that might cause any conflict with inserting the new row.”  There really isn’t a concept of an “original” row, it just happens that the most common conflict is primary key.  It’s also true that inserting a single row with “insert or replace” can cause multiple rows to be deleted (if there are multiple constraints across multiple columns, for example).

 -j

_______________________________________________
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: Is it a bug or "as designed"?

R Smith-2
In reply to this post by John Found

On 2018/03/09 9:49 PM, John Found wrote:
> On Fri, 9 Mar 2018 19:42:19 +0000
> Simon Slavin <[hidden email]> wrote:
>
> You are right. And Jay Kreibich in his post above. But then the second
> solution from my post should be the correct behavior.
> In the current implementation "insert or replace" behave as the foreign constraint is deferred.
> But according to documentation, all foreign constraints in SQLite are immediate by default.


If an FK is created as immediate, it will fail at the conclusion (read:
END OF) the statement that hits the FK check. If it is declared as
DEFERRED it will wait all the way until the end of the Transaction when
you try to commit.

Either way, many people get confused with REPLACE because it feels like
UPDATE, it is not, REPLACE means (as have been pointed out) "REMOVE
constraint-violating rows, THEN, ADD the given new row" - and more
importantly - all of this happens INSIDE one single statement so that
any FK checks will happen at the end of said statement AFTER the new row
is added back, and so no constraint violation exists come checking time.

Triggers are a whole different matter, they are like EVENT handlers, and
have to fire by binding contract the very moment the triggerable offense
happens, which in the case of REPLACE is right in the middle of the
statement, if (and only if) there was one or more rows in there that
needed ousting.

What you probably wanted is an "UPSERT", which in theory is more an
UPDATE OR INSERT than a REPLACE, and will cause at best an UPDATE
Trigger to fire and no deletions will happen.  SQLite doesn't have a
command like that, but you can easily simulate it by just issuing two
commands:
First do the UPDATE... WHERE Key = X - which, if the record doesn't
exist yet, will fail quietly because of the WHERE clause,
then do the INSERT OR IGNORE(...) - which will again fail quietly if it
did already exist,
both of which won't break a Transaction, mess with the wrong triggers or
constraint checks, will be very fast (considering the PK is used and
only one of the two statements gets to do any work in IO terms)... and
will always work perfectly and reliably fire only ON INSERT, ON UPDATE
and ON DELETE triggers when those really happen.

The obvious downside to it being you having to formulate both an UPDATE
and an INSERT statement in your code, which can be irritating, but then,
if it's the best tool for the job...


I hope this shed some light on how it works and why the triggers and FKs
fire differently (by design) and how to get the results you want.

Cheers,
Ryan


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

Site error

R Smith-2
In reply to this post by John Found
It seems to me this is a temporary thing, perhaps due to someone working
on the site, but...

I've opened sqlite.org, clicked "Search", then entered "foreign key" as
the search term and clicked Go.

It produced the error below.
I've re-tried several times, different searches, problem remained.
Example url: https://sqlite.org/search?s=d&q=table


  Wapp Application Error

attempt to write a readonly database
     while executing
"db2 eval {
     PRAGMA synchronous=OFF;
     PRAGMA journal_mode=OFF;
     BEGIN;
       CREATE TABLE IF NOT EXISTS log(
         ip,                  -- I..."
     (procedure "search_add_log_entry" line 7)
     invoked from within
"search_add_log_entry $nRes"
     (procedure "searchresults" line 49)
     invoked from within
"$cmd"
     ("uplevel" body line 1)
     invoked from within
"uplevel {$cmd}"
     invoked from within
"time [list uplevel $script]"
     (procedure "ttime" line 2)
     invoked from within
"ttime {$cmd}"
     invoked from within
"db transaction {
     set t [ttime {$cmd}]
   }"
     (procedure "wapp-default" line 46)
     invoked from within
"wapp-default"




_______________________________________________
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: Is it a bug or "as designed"?

Simon Slavin-3
In reply to this post by John Found
On 9 Mar 2018, at 7:49pm, John Found <[hidden email]> wrote:

> In the current implementation "insert or replace" behave as the foreign constraint is deferred.
> But according to documentation, all foreign constraints in SQLite are immediate by default.

John,

The documentation suggests that in SQLite foreign keys are not deferred by default.  Section 4.2 of

<https://sqlite.org/foreignkeys.html>

talks about this and shows how to set up a foreign key to be deferred:

    create table B (
        aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED,
        ulti_data)

Assuming you are using atomic commit (not using BEGIN/COMMIT, so SQLite "inserts" them around that single statement) I /think/ this should allow your code to work the way you intended.

Simon.
_______________________________________________
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: Is it a bug or "as designed"?

John Found
On Sat, 10 Mar 2018 01:17:38 +0000
Simon Slavin <[hidden email]> wrote:

> On 9 Mar 2018, at 7:49pm, John Found <[hidden email]> wrote:
>
> > In the current implementation "insert or replace" behave as the foreign constraint is deferred.
> > But according to documentation, all foreign constraints in SQLite are immediate by default.
>
> John,
>
> The documentation suggests that in SQLite foreign keys are not deferred by default.  Section 4.2 of
>
> <https://sqlite.org/foreignkeys.html>
>
> talks about this and shows how to set up a foreign key to be deferred:
>
>     create table B (
>         aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED,
>         ulti_data)
>

No difference at all. Because "insert or replace" always works as if the constraint is deferred.
"insert or replace" always succeed to delete rows that are referenced by B and defers the constraint enforcement until
the end of the internal transaction. But at the end, a new row with the same ID is inserted, so there is no
constraint violation anymore.


> Assuming you are using atomic commit (not using BEGIN/COMMIT, so SQLite "inserts" them around that single statement) I /think/ this should allow your code to work the way you intended.
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
http://fresh.flatassembler.net
http://asm32.info
John Found <[hidden email]>
_______________________________________________
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: Is it a bug or "as designed"?

Simon Slavin-3


On 10 Mar 2018, at 7:15am, John Found <[hidden email]> wrote:

> Simon Slavin <[hidden email]> wrote:
>
>> On 9 Mar 2018, at 7:49pm, John Found <[hidden email]> wrote:
>>
>>> In the current implementation "insert or replace" behave as the foreign constraint is deferred.
>>> But according to documentation, all foreign constraints in SQLite are immediate by default.
>>
>>    create table B (
>>        aid references A(id) on delete cascade DEFERRABLE INITIALLY DEFERRED,
>>        ulti_data)
>
> No difference at all. Because "insert or replace" always works as if the constraint is deferred.
> "insert or replace" always succeed to delete rows that are referenced by B and defers the constraint enforcement until
> the end of the internal transaction. But at the end, a new row with the same ID is inserted, so there is no
> constraint violation anymore.

John, I apologise.  I missed a paragraph in the documentation:

"If the current statement is not inside an explicit transaction (a BEGIN/COMMIT/ROLLBACK block), then an implicit transaction is committed as soon as the statement has finished executing. In this case deferred constraints behave the same as immediate constraints."

So please keep the CREATE TABLE statement the same as quoted above, but change your data commands to

    BEGIN;
       insert or replace into A values (?1, ?2);
       insert into B values (?1, ?2);
    COMMIT;

or even

    BEGIN;
       insert or replace into A values (?1, ?2);
    COMMIT;
    insert into B values (?1, ?2);

Do these make things work the way you expect ?

Simon.
_______________________________________________
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: Site error

David Raymond
In reply to this post by R Smith-2
Getting same error message trying to search today.


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of R Smith
Sent: Friday, March 09, 2018 7:47 PM
To: [hidden email]
Subject: [sqlite] Site error

It seems to me this is a temporary thing, perhaps due to someone working
on the site, but...

I've opened sqlite.org, clicked "Search", then entered "foreign key" as
the search term and clicked Go.

It produced the error below.
I've re-tried several times, different searches, problem remained.
Example url: https://sqlite.org/search?s=d&q=table


  Wapp Application Error

attempt to write a readonly database
     while executing
"db2 eval {
     PRAGMA synchronous=OFF;
     PRAGMA journal_mode=OFF;
     BEGIN;
       CREATE TABLE IF NOT EXISTS log(
         ip,                  -- I..."
     (procedure "search_add_log_entry" line 7)
     invoked from within
"search_add_log_entry $nRes"
     (procedure "searchresults" line 49)
     invoked from within
"$cmd"
     ("uplevel" body line 1)
     invoked from within
"uplevel {$cmd}"
     invoked from within
"time [list uplevel $script]"
     (procedure "ttime" line 2)
     invoked from within
"ttime {$cmd}"
     invoked from within
"db transaction {
     set t [ttime {$cmd}]
   }"
     (procedure "wapp-default" line 46)
     invoked from within
"wapp-default"




_______________________________________________
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: Site error

Richard Hipp-3
On 3/16/18, David Raymond <[hidden email]> wrote:
> Getting same error message trying to search today.

Fixed.

Apparently one of the automatic site-update scripts is messing up the
file permissions on the full-text search databases...

--
D. Richard Hipp
[hidden email]
_______________________________________________
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: Site error

Richard Rousselot
The error is back I think.  I am getting same message.

Also, since I have your attention, why not just make the search box always
visible vs. having to click it.  Seems like an unnessary step.

Richard

On Fri, Mar 16, 2018 at 1:03 PM Richard Hipp <[hidden email]> wrote:

> On 3/16/18, David Raymond <[hidden email]> wrote:
> > Getting same error message trying to search today.
>
> Fixed.
>
> Apparently one of the automatic site-update scripts is messing up the
> file permissions on the full-text search databases...
>
> --
> D. Richard Hipp
> [hidden email]
> _______________________________________________
> 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