Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

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

Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Peter Michaux
Hi,

I have read that INSERT OR REPLACE is equivalent to a DELETE followed by an
INSERT. I came across a case where that is not true.

Set up a test case

    $ rm -f asdf.sqlite && sqlite3 asdf.sqlite

    sqlite> .mode columns
    sqlite> .headers on
    sqlite> PRAGMA foreign_keys=OFF;

    sqlite> CREATE TABLE users (
       ...>     id INT UNSIGNED NOT NULL PRIMARY KEY,
       ...>     username TEXT NOT NULL
       ...> );

    sqlite> CREATE TABLE user_extras (
       ...>     user_id INT UNSIGNED NOT NULL PRIMARY KEY,
       ...>     other INT NULL DEFAULT NULL,
       ...>     FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE
CASCADE ON DELETE CASCADE
       ...> );

    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');

    sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33);

    sqlite> SELECT * FROM users;
    id          username
    ----------  ----------
    1           asdf

    sqlite> SELECT * FROM user_extras;
    user_id     other
    ----------  ----------
    1           33


Let's try a delete followed by an insert.

    sqlite> DELETE FROM users WHERE id = 1;

    sqlite> SELECT * FROM users;

    sqlite> SELECT * FROM user_extras;
    user_id     other
    ----------  ----------
    1           33

    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');

    sqlite> SELECT * FROM users;
    id          username
    ----------  ----------
    1           asdf

    sqlite> SELECT * FROM user_extras;
    user_id     other
    ----------  ----------
    1           33

Notice that the value `user_extras.other` is still 33. That's good.

    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, 'asdf');

    sqlite> SELECT * FROM users;
    id          username
    ----------  ----------
    1           asdf

    sqlite> SELECT * FROM user_extras;
    user_id     other
    ----------  ----------
    1           33

Still 33. That's good.

Based on the above, it looks like `INSERT OR REPLACE` is equivalent to a
`DELETE` followed by an `INSERT`.

If we add a trigger, things change...

    sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users
       ...> BEGIN
       ...>     INSERT OR IGNORE INTO user_extras (user_id) VALUES (new.id);
       ...> END;

A `DELETE` followed by an `INSERT` still works as before.

    sqlite> DELETE FROM users WHERE id = 1;

    sqlite> SELECT * FROM users;

    sqlite> SELECT * FROM user_extras;
    user_id     other
    ----------  ----------
    1           33

    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');

    sqlite> SELECT * FROM users;
    id          username
    ----------  ----------
    1           asdf

    sqlite> SELECT * FROM user_extras;
    user_id     other
    ----------  ----------
    1           33

See above that the value of `user_extras.other` survived the `DELETE`
followed by the `INSERT`.

When we use `INSERT OR REPLACE` the `user_extras.other` value is cleared
out.

    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1, 'asdf');

    sqlite> SELECT * FROM users;
    id          username
    ----------  ----------
    1           asdf

    sqlite> SELECT * FROM user_extras;
    user_id     other
    ----------  ----------
    1

Because of the trigger, `INSERT OR REPLACE` is no longer equivalent to
`DELETE` followed by `INSERT`.


Peter
_______________________________________________
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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Keith Medcalf

The parent is still being deleted and inserted, however, the trigger now uses the REPLACE conflict resolution method rather than the IGNORE resolution method and that resolution method causes the deletion and insertion of a new child record.

---
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-
>[hidden email]] On Behalf Of Peter Michaux
>Sent: Monday, 12 March, 2018 21:09
>To: [hidden email]
>Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
>followed by INSERT
>
>Hi,
>
>I have read that INSERT OR REPLACE is equivalent to a DELETE followed
>by an
>INSERT. I came across a case where that is not true.
>
>Set up a test case
>
>    $ rm -f asdf.sqlite && sqlite3 asdf.sqlite
>
>    sqlite> .mode columns
>    sqlite> .headers on
>    sqlite> PRAGMA foreign_keys=OFF;
>
>    sqlite> CREATE TABLE users (
>       ...>     id INT UNSIGNED NOT NULL PRIMARY KEY,
>       ...>     username TEXT NOT NULL
>       ...> );
>
>    sqlite> CREATE TABLE user_extras (
>       ...>     user_id INT UNSIGNED NOT NULL PRIMARY KEY,
>       ...>     other INT NULL DEFAULT NULL,
>       ...>     FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE
>CASCADE ON DELETE CASCADE
>       ...> );
>
>    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>
>    sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33);
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>
>Let's try a delete followed by an insert.
>
>    sqlite> DELETE FROM users WHERE id = 1;
>
>    sqlite> SELECT * FROM users;
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>Notice that the value `user_extras.other` is still 33. That's good.
>
>    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
>'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>Still 33. That's good.
>
>Based on the above, it looks like `INSERT OR REPLACE` is equivalent
>to a
>`DELETE` followed by an `INSERT`.
>
>If we add a trigger, things change...
>
>    sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users
>       ...> BEGIN
>       ...>     INSERT OR IGNORE INTO user_extras (user_id) VALUES
>(new.id);
>       ...> END;
>
>A `DELETE` followed by an `INSERT` still works as before.
>
>    sqlite> DELETE FROM users WHERE id = 1;
>
>    sqlite> SELECT * FROM users;
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>See above that the value of `user_extras.other` survived the `DELETE`
>followed by the `INSERT`.
>
>When we use `INSERT OR REPLACE` the `user_extras.other` value is
>cleared
>out.
>
>    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
>'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1
>
>Because of the trigger, `INSERT OR REPLACE` is no longer equivalent
>to
>`DELETE` followed by `INSERT`.
>
>
>Peter
>_______________________________________________
>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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Keith Medcalf
In reply to this post by Peter Michaux

On this page, 7th paragrph:

https://www.sqlite.org/lang_createtrigger.html

See that:

An ON CONFLICT clause may be specified as part of an UPDATE or INSERT action within the body of the trigger. However if an ON CONFLICT clause is specified as part of the statement causing the trigger to fire, then conflict handling policy of the outer statement is used instead.

---
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-
>[hidden email]] On Behalf Of Peter Michaux
>Sent: Monday, 12 March, 2018 21:09
>To: [hidden email]
>Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
>followed by INSERT
>
>Hi,
>
>I have read that INSERT OR REPLACE is equivalent to a DELETE followed
>by an
>INSERT. I came across a case where that is not true.
>
>Set up a test case
>
>    $ rm -f asdf.sqlite && sqlite3 asdf.sqlite
>
>    sqlite> .mode columns
>    sqlite> .headers on
>    sqlite> PRAGMA foreign_keys=OFF;
>
>    sqlite> CREATE TABLE users (
>       ...>     id INT UNSIGNED NOT NULL PRIMARY KEY,
>       ...>     username TEXT NOT NULL
>       ...> );
>
>    sqlite> CREATE TABLE user_extras (
>       ...>     user_id INT UNSIGNED NOT NULL PRIMARY KEY,
>       ...>     other INT NULL DEFAULT NULL,
>       ...>     FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE
>CASCADE ON DELETE CASCADE
>       ...> );
>
>    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>
>    sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33);
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>
>Let's try a delete followed by an insert.
>
>    sqlite> DELETE FROM users WHERE id = 1;
>
>    sqlite> SELECT * FROM users;
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>Notice that the value `user_extras.other` is still 33. That's good.
>
>    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
>'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>Still 33. That's good.
>
>Based on the above, it looks like `INSERT OR REPLACE` is equivalent
>to a
>`DELETE` followed by an `INSERT`.
>
>If we add a trigger, things change...
>
>    sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users
>       ...> BEGIN
>       ...>     INSERT OR IGNORE INTO user_extras (user_id) VALUES
>(new.id);
>       ...> END;
>
>A `DELETE` followed by an `INSERT` still works as before.
>
>    sqlite> DELETE FROM users WHERE id = 1;
>
>    sqlite> SELECT * FROM users;
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1           33
>
>See above that the value of `user_extras.other` survived the `DELETE`
>followed by the `INSERT`.
>
>When we use `INSERT OR REPLACE` the `user_extras.other` value is
>cleared
>out.
>
>    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
>'asdf');
>
>    sqlite> SELECT * FROM users;
>    id          username
>    ----------  ----------
>    1           asdf
>
>    sqlite> SELECT * FROM user_extras;
>    user_id     other
>    ----------  ----------
>    1
>
>Because of the trigger, `INSERT OR REPLACE` is no longer equivalent
>to
>`DELETE` followed by `INSERT`.
>
>
>Peter
>_______________________________________________
>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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Peter Michaux
Thank you for the information, Keith.

It comes as a surprise to me that the conflict resolution clause of the
statement that causes a trigger to fire can override an explicit conflict
resolution clause in the body of a trigger. But, as you pointed out, it is
documented.

How can I write the trigger to not use a conflict resolution clause? I'm
trying a bunch of different things but with no luck. Something like the
following is possible?

CREATE TRIGGER users_after_insert AFTER INSERT ON users
BEGIN
    CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id = new.id)) =
0 THEN
        INSERT INTO user_extras (user_id) VALUES (new.id)
    END;
END;

Thanks.

Peter



On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf <[hidden email]> wrote:

>
> On this page, 7th paragrph:
>
> https://www.sqlite.org/lang_createtrigger.html
>
> See that:
>
> An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
> action within the body of the trigger. However if an ON CONFLICT clause is
> specified as part of the statement causing the trigger to fire, then
> conflict handling policy of the outer statement is used instead.
>
> ---
> 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-
> >[hidden email]] On Behalf Of Peter Michaux
> >Sent: Monday, 12 March, 2018 21:09
> >To: [hidden email]
> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
> >followed by INSERT
> >
> >Hi,
> >
> >I have read that INSERT OR REPLACE is equivalent to a DELETE followed
> >by an
> >INSERT. I came across a case where that is not true.
> >
> >Set up a test case
> >
> >    $ rm -f asdf.sqlite && sqlite3 asdf.sqlite
> >
> >    sqlite> .mode columns
> >    sqlite> .headers on
> >    sqlite> PRAGMA foreign_keys=OFF;
> >
> >    sqlite> CREATE TABLE users (
> >       ...>     id INT UNSIGNED NOT NULL PRIMARY KEY,
> >       ...>     username TEXT NOT NULL
> >       ...> );
> >
> >    sqlite> CREATE TABLE user_extras (
> >       ...>     user_id INT UNSIGNED NOT NULL PRIMARY KEY,
> >       ...>     other INT NULL DEFAULT NULL,
> >       ...>     FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE
> >CASCADE ON DELETE CASCADE
> >       ...> );
> >
> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >
> >    sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33);
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >
> >Let's try a delete followed by an insert.
> >
> >    sqlite> DELETE FROM users WHERE id = 1;
> >
> >    sqlite> SELECT * FROM users;
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >Notice that the value `user_extras.other` is still 33. That's good.
> >
> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
> >'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >Still 33. That's good.
> >
> >Based on the above, it looks like `INSERT OR REPLACE` is equivalent
> >to a
> >`DELETE` followed by an `INSERT`.
> >
> >If we add a trigger, things change...
> >
> >    sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users
> >       ...> BEGIN
> >       ...>     INSERT OR IGNORE INTO user_extras (user_id) VALUES
> >(new.id);
> >       ...> END;
> >
> >A `DELETE` followed by an `INSERT` still works as before.
> >
> >    sqlite> DELETE FROM users WHERE id = 1;
> >
> >    sqlite> SELECT * FROM users;
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >See above that the value of `user_extras.other` survived the `DELETE`
> >followed by the `INSERT`.
> >
> >When we use `INSERT OR REPLACE` the `user_extras.other` value is
> >cleared
> >out.
> >
> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
> >'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1
> >
> >Because of the trigger, `INSERT OR REPLACE` is no longer equivalent
> >to
> >`DELETE` followed by `INSERT`.
> >
> >
> >Peter
> >_______________________________________________
> >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
>
_______________________________________________
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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

David Raymond
create trigger users_after_insert
after insert on users
when not exists (
  select 1 from user_extras
  where user_id = new.id
)
begin
  insert into user_extras (user_id)
  values (new.id);
end;

-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Peter Michaux
Sent: Friday, March 16, 2018 2:42 PM
To: SQLite mailing list
Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Thank you for the information, Keith.

It comes as a surprise to me that the conflict resolution clause of the
statement that causes a trigger to fire can override an explicit conflict
resolution clause in the body of a trigger. But, as you pointed out, it is
documented.

How can I write the trigger to not use a conflict resolution clause? I'm
trying a bunch of different things but with no luck. Something like the
following is possible?

CREATE TRIGGER users_after_insert AFTER INSERT ON users
BEGIN
    CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id = new.id)) =
0 THEN
        INSERT INTO user_extras (user_id) VALUES (new.id)
    END;
END;

Thanks.

Peter



On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf <[hidden email]> wrote:

>
> On this page, 7th paragrph:
>
> https://www.sqlite.org/lang_createtrigger.html
>
> See that:
>
> An ON CONFLICT clause may be specified as part of an UPDATE or INSERT
> action within the body of the trigger. However if an ON CONFLICT clause is
> specified as part of the statement causing the trigger to fire, then
> conflict handling policy of the outer statement is used instead.
>
> ---
> 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-
> >[hidden email]] On Behalf Of Peter Michaux
> >Sent: Monday, 12 March, 2018 21:09
> >To: [hidden email]
> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
> >followed by INSERT
> >
> >Hi,
> >
> >I have read that INSERT OR REPLACE is equivalent to a DELETE followed
> >by an
> >INSERT. I came across a case where that is not true.
> >
> >Set up a test case
> >
> >    $ rm -f asdf.sqlite && sqlite3 asdf.sqlite
> >
> >    sqlite> .mode columns
> >    sqlite> .headers on
> >    sqlite> PRAGMA foreign_keys=OFF;
> >
> >    sqlite> CREATE TABLE users (
> >       ...>     id INT UNSIGNED NOT NULL PRIMARY KEY,
> >       ...>     username TEXT NOT NULL
> >       ...> );
> >
> >    sqlite> CREATE TABLE user_extras (
> >       ...>     user_id INT UNSIGNED NOT NULL PRIMARY KEY,
> >       ...>     other INT NULL DEFAULT NULL,
> >       ...>     FOREIGN KEY (user_id) REFERENCES users(id) ON UPDATE
> >CASCADE ON DELETE CASCADE
> >       ...> );
> >
> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >
> >    sqlite> INSERT INTO user_extras (user_id, other) VALUES (1, 33);
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >
> >Let's try a delete followed by an insert.
> >
> >    sqlite> DELETE FROM users WHERE id = 1;
> >
> >    sqlite> SELECT * FROM users;
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >Notice that the value `user_extras.other` is still 33. That's good.
> >
> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
> >'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >Still 33. That's good.
> >
> >Based on the above, it looks like `INSERT OR REPLACE` is equivalent
> >to a
> >`DELETE` followed by an `INSERT`.
> >
> >If we add a trigger, things change...
> >
> >    sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON users
> >       ...> BEGIN
> >       ...>     INSERT OR IGNORE INTO user_extras (user_id) VALUES
> >(new.id);
> >       ...> END;
> >
> >A `DELETE` followed by an `INSERT` still works as before.
> >
> >    sqlite> DELETE FROM users WHERE id = 1;
> >
> >    sqlite> SELECT * FROM users;
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1           33
> >
> >See above that the value of `user_extras.other` survived the `DELETE`
> >followed by the `INSERT`.
> >
> >When we use `INSERT OR REPLACE` the `user_extras.other` value is
> >cleared
> >out.
> >
> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
> >'asdf');
> >
> >    sqlite> SELECT * FROM users;
> >    id          username
> >    ----------  ----------
> >    1           asdf
> >
> >    sqlite> SELECT * FROM user_extras;
> >    user_id     other
> >    ----------  ----------
> >    1
> >
> >Because of the trigger, `INSERT OR REPLACE` is no longer equivalent
> >to
> >`DELETE` followed by `INSERT`.
> >
> >
> >Peter
> >_______________________________________________
> >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
>
_______________________________________________
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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Keith Medcalf
In reply to this post by Peter Michaux

It is kind of hard to write a specific trigger since your "purpose" is confusing.  You have defined some referential integrity and are then ignoring it.  If your goal is to update the parent, then why not use the statement designed to do that (UPDATE) ... ?

---
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-
>[hidden email]] On Behalf Of Peter Michaux
>Sent: Friday, 16 March, 2018 12:42
>To: SQLite mailing list
>Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
>followed by INSERT
>
>Thank you for the information, Keith.
>
>It comes as a surprise to me that the conflict resolution clause of
>the
>statement that causes a trigger to fire can override an explicit
>conflict
>resolution clause in the body of a trigger. But, as you pointed out,
>it is
>documented.
>
>How can I write the trigger to not use a conflict resolution clause?
>I'm
>trying a bunch of different things but with no luck. Something like
>the
>following is possible?
>
>CREATE TRIGGER users_after_insert AFTER INSERT ON users
>BEGIN
>    CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id =
>new.id)) =
>0 THEN
>        INSERT INTO user_extras (user_id) VALUES (new.id)
>    END;
>END;
>
>Thanks.
>
>Peter
>
>
>
>On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf <[hidden email]>
>wrote:
>
>>
>> On this page, 7th paragrph:
>>
>> https://www.sqlite.org/lang_createtrigger.html
>>
>> See that:
>>
>> An ON CONFLICT clause may be specified as part of an UPDATE or
>INSERT
>> action within the body of the trigger. However if an ON CONFLICT
>clause is
>> specified as part of the statement causing the trigger to fire,
>then
>> conflict handling policy of the outer statement is used instead.
>>
>> ---
>> 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-
>> >[hidden email]] On Behalf Of Peter Michaux
>> >Sent: Monday, 12 March, 2018 21:09
>> >To: [hidden email]
>> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
>> >followed by INSERT
>> >
>> >Hi,
>> >
>> >I have read that INSERT OR REPLACE is equivalent to a DELETE
>followed
>> >by an
>> >INSERT. I came across a case where that is not true.
>> >
>> >Set up a test case
>> >
>> >    $ rm -f asdf.sqlite && sqlite3 asdf.sqlite
>> >
>> >    sqlite> .mode columns
>> >    sqlite> .headers on
>> >    sqlite> PRAGMA foreign_keys=OFF;
>> >
>> >    sqlite> CREATE TABLE users (
>> >       ...>     id INT UNSIGNED NOT NULL PRIMARY KEY,
>> >       ...>     username TEXT NOT NULL
>> >       ...> );
>> >
>> >    sqlite> CREATE TABLE user_extras (
>> >       ...>     user_id INT UNSIGNED NOT NULL PRIMARY KEY,
>> >       ...>     other INT NULL DEFAULT NULL,
>> >       ...>     FOREIGN KEY (user_id) REFERENCES users(id) ON
>UPDATE
>> >CASCADE ON DELETE CASCADE
>> >       ...> );
>> >
>> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>> >
>> >    sqlite> INSERT INTO user_extras (user_id, other) VALUES (1,
>33);
>> >
>> >    sqlite> SELECT * FROM users;
>> >    id          username
>> >    ----------  ----------
>> >    1           asdf
>> >
>> >    sqlite> SELECT * FROM user_extras;
>> >    user_id     other
>> >    ----------  ----------
>> >    1           33
>> >
>> >
>> >Let's try a delete followed by an insert.
>> >
>> >    sqlite> DELETE FROM users WHERE id = 1;
>> >
>> >    sqlite> SELECT * FROM users;
>> >
>> >    sqlite> SELECT * FROM user_extras;
>> >    user_id     other
>> >    ----------  ----------
>> >    1           33
>> >
>> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>> >
>> >    sqlite> SELECT * FROM users;
>> >    id          username
>> >    ----------  ----------
>> >    1           asdf
>> >
>> >    sqlite> SELECT * FROM user_extras;
>> >    user_id     other
>> >    ----------  ----------
>> >    1           33
>> >
>> >Notice that the value `user_extras.other` is still 33. That's
>good.
>> >
>> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
>> >'asdf');
>> >
>> >    sqlite> SELECT * FROM users;
>> >    id          username
>> >    ----------  ----------
>> >    1           asdf
>> >
>> >    sqlite> SELECT * FROM user_extras;
>> >    user_id     other
>> >    ----------  ----------
>> >    1           33
>> >
>> >Still 33. That's good.
>> >
>> >Based on the above, it looks like `INSERT OR REPLACE` is
>equivalent
>> >to a
>> >`DELETE` followed by an `INSERT`.
>> >
>> >If we add a trigger, things change...
>> >
>> >    sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON
>users
>> >       ...> BEGIN
>> >       ...>     INSERT OR IGNORE INTO user_extras (user_id) VALUES
>> >(new.id);
>> >       ...> END;
>> >
>> >A `DELETE` followed by an `INSERT` still works as before.
>> >
>> >    sqlite> DELETE FROM users WHERE id = 1;
>> >
>> >    sqlite> SELECT * FROM users;
>> >
>> >    sqlite> SELECT * FROM user_extras;
>> >    user_id     other
>> >    ----------  ----------
>> >    1           33
>> >
>> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
>> >
>> >    sqlite> SELECT * FROM users;
>> >    id          username
>> >    ----------  ----------
>> >    1           asdf
>> >
>> >    sqlite> SELECT * FROM user_extras;
>> >    user_id     other
>> >    ----------  ----------
>> >    1           33
>> >
>> >See above that the value of `user_extras.other` survived the
>`DELETE`
>> >followed by the `INSERT`.
>> >
>> >When we use `INSERT OR REPLACE` the `user_extras.other` value is
>> >cleared
>> >out.
>> >
>> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
>> >'asdf');
>> >
>> >    sqlite> SELECT * FROM users;
>> >    id          username
>> >    ----------  ----------
>> >    1           asdf
>> >
>> >    sqlite> SELECT * FROM user_extras;
>> >    user_id     other
>> >    ----------  ----------
>> >    1
>> >
>> >Because of the trigger, `INSERT OR REPLACE` is no longer
>equivalent
>> >to
>> >`DELETE` followed by `INSERT`.
>> >
>> >
>> >Peter
>> >_______________________________________________
>> >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
>>
>_______________________________________________
>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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Peter Michaux
​You are right that my purpose does seem kind of confusing.

What I really want is UPSERT to avoid doing an UPDATE, checking if the
number of rows affected is zero, then doing an INSERT. The lack of UPSERT
leads to a lot more application code using the UPDATE/INSERT combination.
UPSERT doesn't exist in SQLite so I was trying to work around that by using
INSERT OR REPLACE which is not the same thing. I can see from another
recent thread that some others also think that UPSERT would be a valuable
addition to SQLite.​

Peter


On Fri, Mar 16, 2018 at 2:07 PM, Keith Medcalf <[hidden email]> wrote:

>
> It is kind of hard to write a specific trigger since your "purpose" is
> confusing.  You have defined some referential integrity and are then
> ignoring it.  If your goal is to update the parent, then why not use the
> statement designed to do that (UPDATE) ... ?
>
> ---
> 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-
> >[hidden email]] On Behalf Of Peter Michaux
> >Sent: Friday, 16 March, 2018 12:42
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
> >followed by INSERT
> >
> >Thank you for the information, Keith.
> >
> >It comes as a surprise to me that the conflict resolution clause of
> >the
> >statement that causes a trigger to fire can override an explicit
> >conflict
> >resolution clause in the body of a trigger. But, as you pointed out,
> >it is
> >documented.
> >
> >How can I write the trigger to not use a conflict resolution clause?
> >I'm
> >trying a bunch of different things but with no luck. Something like
> >the
> >following is possible?
> >
> >CREATE TRIGGER users_after_insert AFTER INSERT ON users
> >BEGIN
> >    CASE WHEN (SELECT count(*) FROM user_extras WHERE user_id =
> >new.id)) =
> >0 THEN
> >        INSERT INTO user_extras (user_id) VALUES (new.id)
> >    END;
> >END;
> >
> >Thanks.
> >
> >Peter
> >
> >
> >
> >On Mon, Mar 12, 2018 at 8:50 PM, Keith Medcalf <[hidden email]>
> >wrote:
> >
> >>
> >> On this page, 7th paragrph:
> >>
> >> https://www.sqlite.org/lang_createtrigger.html
> >>
> >> See that:
> >>
> >> An ON CONFLICT clause may be specified as part of an UPDATE or
> >INSERT
> >> action within the body of the trigger. However if an ON CONFLICT
> >clause is
> >> specified as part of the statement causing the trigger to fire,
> >then
> >> conflict handling policy of the outer statement is used instead.
> >>
> >> ---
> >> 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-
> >> >[hidden email]] On Behalf Of Peter Michaux
> >> >Sent: Monday, 12 March, 2018 21:09
> >> >To: [hidden email]
> >> >Subject: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
> >> >followed by INSERT
> >> >
> >> >Hi,
> >> >
> >> >I have read that INSERT OR REPLACE is equivalent to a DELETE
> >followed
> >> >by an
> >> >INSERT. I came across a case where that is not true.
> >> >
> >> >Set up a test case
> >> >
> >> >    $ rm -f asdf.sqlite && sqlite3 asdf.sqlite
> >> >
> >> >    sqlite> .mode columns
> >> >    sqlite> .headers on
> >> >    sqlite> PRAGMA foreign_keys=OFF;
> >> >
> >> >    sqlite> CREATE TABLE users (
> >> >       ...>     id INT UNSIGNED NOT NULL PRIMARY KEY,
> >> >       ...>     username TEXT NOT NULL
> >> >       ...> );
> >> >
> >> >    sqlite> CREATE TABLE user_extras (
> >> >       ...>     user_id INT UNSIGNED NOT NULL PRIMARY KEY,
> >> >       ...>     other INT NULL DEFAULT NULL,
> >> >       ...>     FOREIGN KEY (user_id) REFERENCES users(id) ON
> >UPDATE
> >> >CASCADE ON DELETE CASCADE
> >> >       ...> );
> >> >
> >> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >> >
> >> >    sqlite> INSERT INTO user_extras (user_id, other) VALUES (1,
> >33);
> >> >
> >> >    sqlite> SELECT * FROM users;
> >> >    id          username
> >> >    ----------  ----------
> >> >    1           asdf
> >> >
> >> >    sqlite> SELECT * FROM user_extras;
> >> >    user_id     other
> >> >    ----------  ----------
> >> >    1           33
> >> >
> >> >
> >> >Let's try a delete followed by an insert.
> >> >
> >> >    sqlite> DELETE FROM users WHERE id = 1;
> >> >
> >> >    sqlite> SELECT * FROM users;
> >> >
> >> >    sqlite> SELECT * FROM user_extras;
> >> >    user_id     other
> >> >    ----------  ----------
> >> >    1           33
> >> >
> >> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >> >
> >> >    sqlite> SELECT * FROM users;
> >> >    id          username
> >> >    ----------  ----------
> >> >    1           asdf
> >> >
> >> >    sqlite> SELECT * FROM user_extras;
> >> >    user_id     other
> >> >    ----------  ----------
> >> >    1           33
> >> >
> >> >Notice that the value `user_extras.other` is still 33. That's
> >good.
> >> >
> >> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
> >> >'asdf');
> >> >
> >> >    sqlite> SELECT * FROM users;
> >> >    id          username
> >> >    ----------  ----------
> >> >    1           asdf
> >> >
> >> >    sqlite> SELECT * FROM user_extras;
> >> >    user_id     other
> >> >    ----------  ----------
> >> >    1           33
> >> >
> >> >Still 33. That's good.
> >> >
> >> >Based on the above, it looks like `INSERT OR REPLACE` is
> >equivalent
> >> >to a
> >> >`DELETE` followed by an `INSERT`.
> >> >
> >> >If we add a trigger, things change...
> >> >
> >> >    sqlite> CREATE TRIGGER users_after_insert AFTER INSERT ON
> >users
> >> >       ...> BEGIN
> >> >       ...>     INSERT OR IGNORE INTO user_extras (user_id) VALUES
> >> >(new.id);
> >> >       ...> END;
> >> >
> >> >A `DELETE` followed by an `INSERT` still works as before.
> >> >
> >> >    sqlite> DELETE FROM users WHERE id = 1;
> >> >
> >> >    sqlite> SELECT * FROM users;
> >> >
> >> >    sqlite> SELECT * FROM user_extras;
> >> >    user_id     other
> >> >    ----------  ----------
> >> >    1           33
> >> >
> >> >    sqlite> INSERT INTO users (id, username) VALUES (1, 'asdf');
> >> >
> >> >    sqlite> SELECT * FROM users;
> >> >    id          username
> >> >    ----------  ----------
> >> >    1           asdf
> >> >
> >> >    sqlite> SELECT * FROM user_extras;
> >> >    user_id     other
> >> >    ----------  ----------
> >> >    1           33
> >> >
> >> >See above that the value of `user_extras.other` survived the
> >`DELETE`
> >> >followed by the `INSERT`.
> >> >
> >> >When we use `INSERT OR REPLACE` the `user_extras.other` value is
> >> >cleared
> >> >out.
> >> >
> >> >    sqlite> INSERT OR REPLACE INTO users (id, username) VALUES (1,
> >> >'asdf');
> >> >
> >> >    sqlite> SELECT * FROM users;
> >> >    id          username
> >> >    ----------  ----------
> >> >    1           asdf
> >> >
> >> >    sqlite> SELECT * FROM user_extras;
> >> >    user_id     other
> >> >    ----------  ----------
> >> >    1
> >> >
> >> >Because of the trigger, `INSERT OR REPLACE` is no longer
> >equivalent
> >> >to
> >> >`DELETE` followed by `INSERT`.
> >> >
> >> >
> >> >Peter
> >> >_______________________________________________
> >> >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
> >>
> >_______________________________________________
> >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
>
_______________________________________________
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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Kees Nuyt
On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
<[hidden email]> wrote:

> You are right that my purpose does seem kind of confusing.
>
> What I really want is UPSERT to avoid doing an UPDATE, checking if the
> number of rows affected is zero, then doing an INSERT. The lack of UPSERT
> leads to a lot more application code using the UPDATE/INSERT combination.
> UPSERT doesn't exist in SQLite so I was trying to work around that by using
> INSERT OR REPLACE which is not the same thing. I can see from another
> recent thread that some others also think that UPSERT would be a valuable
> addition to SQLite.

I fail to see the problem in
BEGIN;
INSERT OR IGNORE ... ;
UPDATE .... ;
COMMIT;
Simple code, no need to test number of affected rows, and pretty
fast because the relevant pages will be in cache.

Or use an updatable view with an INSTEAD OF INSERT trigger.

I did notice that attempts to define a proper UPSERT syntax
opened a can of worms by itself because it (also) has to provide
two colum lists, one for a full INSERT if the row with that PK
doesn't exist, and another one for the columns to be updated
when the row already exists. So, I don't see a big advantage in
UPSERT.

My humble two cents,

--
Regards,
Kees Nuyt
_______________________________________________
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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Peter Michaux
I think there are a couple main offenders with

> BEGIN;
> INSERT OR IGNORE ... ;
> UPDATE .... ;
> COMMIT;

The first is that it is bulky. If this is in the application code then it
has to be repeated for each desired UPSERT and it has to be repeated in the
code of each application that uses the database.

The second is that it seems so inefficient in the case of a new row being
inserted. The row is inserted and then immediately updated. Why do both
operations when only one is needed?

Is it possible to write a stored procedure that checks a result of the
INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
That would at least move the bulky code out of the application and into the
database. Also it seems it would be more efficient.

Thanks.

Peter


Peter



On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt <[hidden email]> wrote:

> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
> <[hidden email]> wrote:
>
> > You are right that my purpose does seem kind of confusing.
> >
> > What I really want is UPSERT to avoid doing an UPDATE, checking if the
> > number of rows affected is zero, then doing an INSERT. The lack of UPSERT
> > leads to a lot more application code using the UPDATE/INSERT combination.
> > UPSERT doesn't exist in SQLite so I was trying to work around that by
> using
> > INSERT OR REPLACE which is not the same thing. I can see from another
> > recent thread that some others also think that UPSERT would be a valuable
> > addition to SQLite.
>
> I fail to see the problem in
> BEGIN;
> INSERT OR IGNORE ... ;
> UPDATE .... ;
> COMMIT;
> Simple code, no need to test number of affected rows, and pretty
> fast because the relevant pages will be in cache.
>
> Or use an updatable view with an INSTEAD OF INSERT trigger.
>
> I did notice that attempts to define a proper UPSERT syntax
> opened a can of worms by itself because it (also) has to provide
> two colum lists, one for a full INSERT if the row with that PK
> doesn't exist, and another one for the columns to be updated
> when the row already exists. So, I don't see a big advantage in
> UPSERT.
>
> My humble two cents,
>
> --
> Regards,
> Kees Nuyt
> _______________________________________________
> 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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Graham Holden
Thursday, March 22, 2018, 7:18:08 PM, Peter Michaux wrote:

> I think there are a couple main offenders with

>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE .... ;
>> COMMIT;

> The first is that it is bulky. If this is in the application code then it
> has to be repeated for each desired UPSERT and it has to be repeated in the
> code of each application that uses the database.

From what I can remember, most of the suggested formats for an UPSERT
command (except perhaps R. Smith's "NOT"/"KEEP" idea) involve two
lists of fields/values, so would be similarly "bulky" as separate
INSERT and UPDATE commands, and need similar amounts of application
code.

> The second is that it seems so inefficient in the case of a new row being
> inserted. The row is inserted and then immediately updated. Why do both
> operations when only one is needed?

There are at least two alternatives (hopefully I've given correct
attribution):

  o  "INSERT OR IGNORE" the "key" fields (ensures they now exist)
     followed by an UPDATE for the remaining fields (Simon Slavin).

  o  "INSERT OR IGNORE" all values; if sqlite3_changes() indicates
     nothing changed (because the record is already present), perform
     the UPDATE (Olivier Mascia).

> Is it possible to write a stored procedure that checks a result of the
> INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
> That would at least move the bulky code out of the application and into the
> database. Also it seems it would be more efficient.

In some cases, e.g. where the data to be UPSERTed is in a (possibly
transient) table you can use a TRIGGER to perform the UPDATE part. (At
the risk of blowing my own trumpet, see an answer of mine on
StackOverflow: https://stackoverflow.com/a/22481731/2096401).


Regards,
Graham



_______________________________________________
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: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Keith Medcalf
In reply to this post by Peter Michaux

Yes.  Here is a stored procedure written in Python that implements a "stored procedure" (that is, it is a procedure and it is indeed stored) that does an "upsert" operation.  You pass it the db connection object, the name of the table, a dictionary of the PrimaryKey fields, and a dictionary of the data fields.  It does the "upsert" and returns the associated rowid of the record ... and it only needs to be written once.  Of course, some source languages are not so nice and require far more complicated "stored procedures", however, you only have to design and write them once.


from __future__ import print_function

def upsert(db=None, table=None, pk=None, data=None, debug=False):
    if not (db and table and pk):
        raise ValueError('must specify db, table, pk')
    if not all(pk.values()):
        raise ValueError('PrimaryKeys must not be null')
    alldata = dict()
    alldata.update(pk)
    sqlUpdate = ''
    if data:
        alldata.update(data)
        sqlUpdate = ''.join(['UPDATE ',
                             table,
                             ' SET (',
                             ', '.join(data.keys()),
                             ') = (:',
                             ', :'.join(data.keys()),
                             ') WHERE (',
                             ', '.join(pk.keys()),
                             ') == (:',
                             ', :'.join(pk.keys()),
                             ');'
                            ])
    sqlInsert = ''.join(['INSERT OR IGNORE INTO ',
                         table,
                         ' (',
                         ', '.join(alldata.keys()),
                         ') values (:',
                         ', :'.join(alldata.keys()),
                         ');'
                        ])
    sqlSelect = ''.join(['SELECT id ',
                         'FROM ',
                         table,
                         ' WHERE (',
                         ', '.join(pk.keys()),
                         ') == (:',
                         ', :'.join(pk.keys()),
                         ');'
                        ])
    if debug:
        if sqlUpdate:
            print(sqlUpdate)
        print(sqlInsert)
        print(sqlSelect)
    cr = db.cursor()
    cr.execute('SAVEPOINT %s%s' % ('UpSert', table))
    if data:
        cr.execute(sqlUpdate, alldata)
    cr.execute(sqlInsert, alldata)
    id = None
    try:
        for row in cr.execute(sqlSelect):
            id = row[0]
    except:
        pass
    cr.execute('RELEASE %s%s;' % ('UpSert', table))
    cr.close()
    return id

---
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-
>[hidden email]] On Behalf Of Peter Michaux
>Sent: Thursday, 22 March, 2018 13:18
>To: SQLite mailing list
>Subject: Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE
>followed by INSERT
>
>I think there are a couple main offenders with
>
>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE .... ;
>> COMMIT;
>
>The first is that it is bulky. If this is in the application code
>then it
>has to be repeated for each desired UPSERT and it has to be repeated
>in the
>code of each application that uses the database.
>
>The second is that it seems so inefficient in the case of a new row
>being
>inserted. The row is inserted and then immediately updated. Why do
>both
>operations when only one is needed?
>
>Is it possible to write a stored procedure that checks a result of
>the
>INSERT OR IGNORE and only attempts the UPDATE if the row already
>existed?
>That would at least move the bulky code out of the application and
>into the
>database. Also it seems it would be more efficient.
>
>Thanks.
>
>Peter
>
>
>Peter
>
>
>
>On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt <[hidden email]> wrote:
>
>> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
>> <[hidden email]> wrote:
>>
>> > You are right that my purpose does seem kind of confusing.
>> >
>> > What I really want is UPSERT to avoid doing an UPDATE, checking
>if the
>> > number of rows affected is zero, then doing an INSERT. The lack
>of UPSERT
>> > leads to a lot more application code using the UPDATE/INSERT
>combination.
>> > UPSERT doesn't exist in SQLite so I was trying to work around
>that by
>> using
>> > INSERT OR REPLACE which is not the same thing. I can see from
>another
>> > recent thread that some others also think that UPSERT would be a
>valuable
>> > addition to SQLite.
>>
>> I fail to see the problem in
>> BEGIN;
>> INSERT OR IGNORE ... ;
>> UPDATE .... ;
>> COMMIT;
>> Simple code, no need to test number of affected rows, and pretty
>> fast because the relevant pages will be in cache.
>>
>> Or use an updatable view with an INSTEAD OF INSERT trigger.
>>
>> I did notice that attempts to define a proper UPSERT syntax
>> opened a can of worms by itself because it (also) has to provide
>> two colum lists, one for a full INSERT if the row with that PK
>> doesn't exist, and another one for the columns to be updated
>> when the row already exists. So, I don't see a big advantage in
>> UPSERT.
>>
>> My humble two cents,
>>
>> --
>> Regards,
>> Kees Nuyt
>> _______________________________________________
>> 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



_______________________________________________
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: [EXTERNAL] Re: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

Hick Gunter
In reply to this post by Peter Michaux
There are no stored procedures in SQLite. But you could try creating a trigger program. Not sure if this works...

CREATE TRIGGER BEFORE UPDATE ON <table> WHEN NOT EXISTS SELECT 1 FROM <table> WHERE <primary key> BEGIN INSERT INTO <table> (<key fields> VALUES (NEW. ...); END;

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Peter Michaux
Gesendet: Donnerstag, 22. März 2018 20:18
An: SQLite mailing list <[hidden email]>
Betreff: [EXTERNAL] Re: [sqlite] Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

I think there are a couple main offenders with

> BEGIN;
> INSERT OR IGNORE ... ;
> UPDATE .... ;
> COMMIT;

The first is that it is bulky. If this is in the application code then it has to be repeated for each desired UPSERT and it has to be repeated in the code of each application that uses the database.

The second is that it seems so inefficient in the case of a new row being inserted. The row is inserted and then immediately updated. Why do both operations when only one is needed?

Is it possible to write a stored procedure that checks a result of the INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
That would at least move the bulky code out of the application and into the database. Also it seems it would be more efficient.

Thanks.

Peter


Peter



On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt <[hidden email]> wrote:

> On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
> <[hidden email]> wrote:
>
> > You are right that my purpose does seem kind of confusing.
> >
> > What I really want is UPSERT to avoid doing an UPDATE, checking if
> > the number of rows affected is zero, then doing an INSERT. The lack
> > of UPSERT leads to a lot more application code using the UPDATE/INSERT combination.
> > UPSERT doesn't exist in SQLite so I was trying to work around that
> > by
> using
> > INSERT OR REPLACE which is not the same thing. I can see from
> > another recent thread that some others also think that UPSERT would
> > be a valuable addition to SQLite.
>
> I fail to see the problem in
> BEGIN;
> INSERT OR IGNORE ... ;
> UPDATE .... ;
> COMMIT;
> Simple code, no need to test number of affected rows, and pretty fast
> because the relevant pages will be in cache.
>
> Or use an updatable view with an INSTEAD OF INSERT trigger.
>
> I did notice that attempts to define a proper UPSERT syntax opened a
> can of worms by itself because it (also) has to provide two colum
> lists, one for a full INSERT if the row with that PK doesn't exist,
> and another one for the columns to be updated when the row already
> exists. So, I don't see a big advantage in UPSERT.
>
> My humble two cents,
>
> --
> Regards,
> Kees Nuyt
> _______________________________________________
> 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


___________________________________________
 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Bug? INSERT OR REPLACE not equivalent to DELETE followed by INSERT

petern
In reply to this post by Peter Michaux
Peter, is "INSTEAD OF" trigger not available on your version of SQLite?

https://sqlite.org/lang_createtrigger.html#instead_of_trigger

CREATE VIEW mytable_UPSERT AS SELECT * FROM mytable;
CREATE TRIGGER mytable_UPSERT INSTEAD OF INSERT ON mytable_UPSERT BEGIN
-->INSERT OR IGNORE ... ;
-->UPDATE .... ;
END;

INSERT INTO mytable_UPSERT ....

Peter

On Thu, Mar 22, 2018 at 12:18 PM, Peter Michaux <[hidden email]>
wrote:

> I think there are a couple main offenders with
>
> > BEGIN;
> > INSERT OR IGNORE ... ;
> > UPDATE .... ;
> > COMMIT;
>
> The first is that it is bulky. If this is in the application code then it
> has to be repeated for each desired UPSERT and it has to be repeated in the
> code of each application that uses the database.
>
> The second is that it seems so inefficient in the case of a new row being
> inserted. The row is inserted and then immediately updated. Why do both
> operations when only one is needed?
>
> Is it possible to write a stored procedure that checks a result of the
> INSERT OR IGNORE and only attempts the UPDATE if the row already existed?
> That would at least move the bulky code out of the application and into the
> database. Also it seems it would be more efficient.
>
> Thanks.
>
> Peter
>
>
> Peter
>
>
>
> On Wed, Mar 21, 2018 at 6:15 PM, Kees Nuyt <[hidden email]> wrote:
>
> > On Wed, 21 Mar 2018 14:05:07 -0700, Peter Michaux
> > <[hidden email]> wrote:
> >
> > > You are right that my purpose does seem kind of confusing.
> > >
> > > What I really want is UPSERT to avoid doing an UPDATE, checking if the
> > > number of rows affected is zero, then doing an INSERT. The lack of
> UPSERT
> > > leads to a lot more application code using the UPDATE/INSERT
> combination.
> > > UPSERT doesn't exist in SQLite so I was trying to work around that by
> > using
> > > INSERT OR REPLACE which is not the same thing. I can see from another
> > > recent thread that some others also think that UPSERT would be a
> valuable
> > > addition to SQLite.
> >
> > I fail to see the problem in
> > BEGIN;
> > INSERT OR IGNORE ... ;
> > UPDATE .... ;
> > COMMIT;
> > Simple code, no need to test number of affected rows, and pretty
> > fast because the relevant pages will be in cache.
> >
> > Or use an updatable view with an INSTEAD OF INSERT trigger.
> >
> > I did notice that attempts to define a proper UPSERT syntax
> > opened a can of worms by itself because it (also) has to provide
> > two colum lists, one for a full INSERT if the row with that PK
> > doesn't exist, and another one for the columns to be updated
> > when the row already exists. So, I don't see a big advantage in
> > UPSERT.
> >
> > My humble two cents,
> >
> > --
> > Regards,
> > Kees Nuyt
> > _______________________________________________
> > 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
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users