Re: [sqlite] Problem with SELECT by rowid after INSERT

[James K. Lowden]

On Fri, 12 Apr 2019 11:40:13 -0400
Jim Dossey  wrote:

> CREATE TABLE "sessiond" (
> "journal" VARCHAR(4) DEFAULT '' NOT NULL,
> "session" VARCHAR(16) DEFAULT '' NOT NULL,
> "pid" INTEGER DEFAULT 0 NOT NULL,
> rowid INTEGER PRIMARY KEY
> );

Although it has nothing to do with the problem you posed, at some
point you might want to reconsider this design.  

A row in this table is allowed to exist even if contains no
information.  The journal and session can be empty strings, and the pid
0 (invalid).  That, in fact, is the default.  

I assume the rowid is meaningless, just a way to refer to the row from
another table.  

The table has the hallmark of an application design that inserts "empty
records" and updates them.  That's a whole other ball of wax, and I
don't want to critique an assumption.  But it's easy to see how that
kind of design is inefficient and defeats features provided by the
DBMS. 

The DEFAULT '' NOT NULL construct is particularly pernicious, because
NULL is useful in ways a zero-length string is not.  NULL can always
be converted to something else with coalesce, and is usually obvious
when interrogating the database interactively.  The same cannot be said
of zero-length strings.  

Instead of allowing any number of meaningless duplicate rows, why not
let the DBMS do a little work for you?  For example: 

CREATE TABLE sessiond (
journal VARCHAR(4) NOT NULL,
session VARCHAR(16) NOT NULL,
pid INTEGER NOT NULL check (pid > 0), 
primary key( pid, journal, session )
);

If the rowid actually helps, sure use it.  If it's  better as the
primary key, that's fine; it's an arbitrary choice, logically
speaking.  But the {pid, journal, session} tuple should be unique
regardless.  If not, what to 2 identical rows represent, and how are
they different?  (I don't need to know, but you do.)  

When you need to track two indistigushable events, the solution isn't
to allow two undifferentiated rows with an arbitrary discriminator.
The solution is to add a "quantity" column, and count them.  It's easy
to delete the row when quantity reaches zero, or cull zero-quantity
rows on selection.  

For extra credit, add CHECK constraints to enforce the lengths of
journal and session.  

With a table like that, you can prevent applications from inserting
nonsense into the database.  I've used that strategy to good effect.
Once the developers have agreed they don't want garbage in the
database, it's pretty rare, when an insert fails, for the programmer to
say, "yeah, I meant to do that".  Nearly always, it's a bug.  

--jkl







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

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Keith Medcalf]

On Friday, 12 April, 2019 14:48, Jim Dossey  wrote:

>On Apr 12, 2019, at 3:27 PM, Keith Medcalf  wrote:

>>> To be a little more specific, the problem happens when I try to do
>>> sqlite3_bind_int() on the prepared statement using the new rowid.
>It
>>> doesn't use the rowid it uses NULL.
>>>
>>> The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>>> Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>>> SELECT command is
>>> SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>>> Which is obtained by calling sqlite3_expanded_sql().
>>
>> This does not make sense.  It indicates that you did not actually
>bind a value to the parameter in question
>>
>
>It may not make sense, but that is what happened.  I tried it
>repeatedly with different values for rowid.  In every case, if the
>rowid did not exist in the table, sqlite3_bind_int() would insert
>NULL in place of the '?' Instead of the rowid.  The rowid's I was
>using were in the range of 10 to 25, so there were no extreme values.

Interesting because it works for me.  Everytime.  And there does not even need 
to be any records in the table at all (you just need a table definition that 
allows the prepare to succeed):


#include 
#include 
#include 
#include 

int main(int argc, char **argv)
{
sqlite3 *db;
sqlite3_stmt * stmt;
char *exp;

int rc;

rc = sqlite3_open("test.db", );
if (rc != SQLITE_OK) return 1;
rc = sqlite3_prepare_v2(db, "select * from t where rowid=?;", -1, , 0);
if (rc != SQLITE_OK) return 1;
rc = sqlite3_bind_int(stmt, 1, atoi(argv[1]));
if (rc != SQLITE_OK) return 1;
exp = sqlite3_expanded_sql(stmt);
printf("%s\n", exp);
return 0;
}

>test 1
select * from t where rowid=1;

>test 42
select * from t where rowid=42;

>test 57
select * from t where rowid=57;

>test 0
select * from t where rowid=0;

>test -5
select * from t where rowid=-5;

The only way I can get "where rowid=NULL" is if I do not bind anything at all 
to parameter 1 ...

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

On Apr 12, 2019, at 3:27 PM, Keith Medcalf  wrote:
> 
> 
>> To be a little more specific, the problem happens when I try to do
>> sqlite3_bind_int() on the prepared statement using the new rowid.  It
>> doesn't use the rowid it uses NULL.
>> 
>> The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>> Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>> SELECT command is
>> SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>> Which is obtained by calling sqlite3_expanded_sql().
> 
> This does not make sense.  It indicates that you did not actually bind a 
> value to the parameter in question
> 

It may not make sense, but that is what happened.  I tried it repeatedly with 
different values for rowid.  In every case, if the rowid did not exist in the 
table, sqlite3_bind_int() would insert NULL in place of the '?' Instead of the 
rowid.  The rowid's I was using were in the range of 10 to 25, so there were no 
extreme values.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

That was it.  There was another SELECT going on that had not been finalized.

Thank you Richard, Simon, and Graham for you help on this.

> On Apr 12, 2019, at 2:36 PM, Richard Hipp  wrote:
> 
> On 4/12/19, Jim Dossey  wrote:
>> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm
>> doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make
>> the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around
>> the INSERT?
> 
> Perhaps the SELECT is running inside of a transaction that was started
> before you did the INSERT.  For example, perhaps you didn't
> sqlite3_finalize() the previous SELECT, which caused it to hold the
> read transaction open.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Problem with SELECT by rowid after INSERT

[Keith Medcalf]

On Friday, 12 April, 2019 09:40, Jim Dossey  wrote"

This does not really make a lot of sense at all for the following reasons:


>I have a table define like this:

>CREATE TABLE "sessiond" (
>"journal" VARCHAR(4) DEFAULT '' NOT NULL,
>"session" VARCHAR(16) DEFAULT '' NOT NULL,
>"pid" INTEGER DEFAULT 0 NOT NULL,
>rowid INTEGER PRIMARY KEY
>);

>In my application I open 2 connections to this table, one for reading
>and one for writing.  I do that because sometimes we do a SELECT, and
>while reading through those rows we'll UPDATE them.  With only 1
>connection the SELECT will block the UPDATE.

This (the above) is incorrect.  The single connection will be upgraded to a 
writer when the update is step'ed on that connection.  It will not block.  
Whether transmogification of the database underneath an "in progress" read is a 
good idea or not is a completely separate issue.

>I also have WAL mode turned on.

>The problem is when I do an INSERT and then try to SELECT that record
>by rowid it doesn't find it.  The INSERT is done on the write
>connection and the SELECT is done on the read connection.  However,
>if I open a new connection and do the SELECT, the new row is there.

What is the transaction state of the "read connection"?  Did you remember to 
RESET all prior statements that were "reading" from that connection?

WAL mode creates transactions so that readers are in a mode called 
REPEATABLE-READ.  They will not see any changes COMMIT'ed by other connections 
until all statements are completed and reset and the read transaction 
commit'ed.  This is so that a crapload of overlapping statements reading data 
through the same connection will always see THE SAME IDENTICAL AND UNCHANGING 
VIEW of the database until they are all RESET and FINALIZED (for implicit 
transactions, for explicit transactions you have to end the transaction as 
well, since it is the "opening" of the transaction that determines the 
REPEATABLE-READ state).

>To be a little more specific, the problem happens when I try to do
>sqlite3_bind_int() on the prepared statement using the new rowid.  It
>doesn't use the rowid it uses NULL.
>
>The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
>Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting
>SELECT command is
>SELECT * FROM "sessiond" WHERE "rowid"=NULL;
>Which is obtained by calling sqlite3_expanded_sql().

This does not make sense.  It indicates that you did not actually bind a value 
to the parameter in question

>If I use an older rowid in the SELECT, it works just fine.  It just
>doesn't work when using the rowid that was just created.

Define "older rowid".  a rowid is just a number.  Therefore is impossible for a 
rowid to be new or old as all numbers have been in use for thousands of years.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.




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

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Graham Holden]

Friday, April 12, 2019, 7:23:31 PM, Jim Dossey  wrote:

> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction,
> then I'm doing the SELECT.  Shouldn't the INSERT do it's own COMMIT
> which should make the new row visible to the SELECT?   Should I add
> a BEGIN ... COMMIT around the INSERT?

Have you "finished" executing the SELECT statement? If you're not
using explicit BEGIN...COMMIT, then each statement runs in its own
transaction. However, the implicit transaction started with a SELECT
does not complete until either sqlite3_step() returns SQLITE_DONE or
you explicitly call sqlite3_finalize(). As I understand it, if, for
example, you only expect one row from your SELECT statement and execute
sqlite3_step() once to retrieve that row, then the SELECT statement
(and the implicit transaction it is running in) is still active. As
such, any NEW SELECT statements you prepare/step will still be in the
same transaction and so wouldn't see the result of any INSERTs.



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

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Richard Hipp]

On 4/12/19, Jim Dossey  wrote:
> I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm
> doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make
> the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around
> the INSERT?

Perhaps the SELECT is running inside of a transaction that was started
before you did the INSERT.  For example, perhaps you didn't
sqlite3_finalize() the previous SELECT, which caused it to hold the
read transaction open.

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

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I'm doing the INSERT first, without a BEGIN ... COMMIT transaction, then I'm 
doing the SELECT.  Shouldn't the INSERT do it's own COMMIT which should make 
the new row visible to the SELECT?   Should I add a BEGIN ... COMMIT around the 
INSERT?

The INSERT is done with sqlite3_exec().  Do I need to call any other functions 
after that to make the new row visible to other connections?

> On Apr 12, 2019, at 2:15 PM, Richard Hipp  wrote:
> 
> On 4/12/19, Jim Dossey  wrote:
>> 
>> The problem is when I do an INSERT and then try to SELECT that record by
>> rowid it doesn't find it.
> 
> Yes, because the SELECT is working inside a single transaction, but
> the INSERT is adding content in a separate, subsequent transaction
> which the SELECT never sees.  This is by design.
> 
> -- 
> D. Richard Hipp
> d...@sqlite.org
> ___
> 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] Problem with SELECT by rowid after INSERT

[Richard Hipp]

On 4/12/19, Jim Dossey  wrote:
>
> The problem is when I do an INSERT and then try to SELECT that record by
> rowid it doesn't find it.

Yes, because the SELECT is working inside a single transaction, but
the INSERT is adding content in a separate, subsequent transaction
which the SELECT never sees.  This is by design.

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

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I do not have shared-cache mode enabled, or anything else that I can see, other 
than WAL mode.  I am not doing any transactions, so the INSERT and SELECT are 
self contained transactions.  This particular database file is opened using 
ATTACH rather than sqlite3_open(), and it is attached to the 2 database 
connections that I have open.

I did another test where I removed the '?' Parameter and the call to 
seqlite3_bind_int() and just created a SELECT statement with the rowid 
hard-coded in, like 'SELECT * FROM sessiond WHERE rowid=20;', and that also did 
not work.  So it isn't the sqlite3_bind_int() function that is causing the 
problem.

As as side note, apparently sqlite3_bind_int() inserts a NULL when doing 'WHERE 
rowid=?" If the rowid you are looking for doesn't exist in the table.  I tried 
several SELECTs (using the same code) with various rowid values, some existent 
and some not, and for the non existent rowid's sqlite3_bind_int() always 
inserts a NULL.

> On Apr 12, 2019, at 1:38 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 6:32pm, Jim Dossey  wrote:
> 
>> It's just when I use 2 different connections that the second connection does 
>> not see the rowid that was just added. 
> 
> Okay, I see you're using WAL mode, and two connections.  Have you set up 
> Shared-Cache Mode or anything else that might 'merge' the two connections ?
> 
> Have you defined any transactions, or are both the INSERT and the SELECT 
> creating their own transactions ?
> ___
> 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] Problem with SELECT by rowid after INSERT

[Simon Slavin]

On 12 Apr 2019, at 6:32pm, Jim Dossey  wrote:

> It's just when I use 2 different connections that the second connection does 
> not see the rowid that was just added. 

Okay, I see you're using WAL mode, and two connections.  Have you set up 
Shared-Cache Mode or anything else that might 'merge' the two connections ?

Have you defined any transactions, or are both the INSERT and the SELECT 
creating their own transactions ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

Yes, I've done extensive debugging to make sure the rowid is valid.

I even did a test where I did the INSERT and the SELECT on the same connection 
and that works okay.  It's just when I use 2 different connections that the 
second connection does not see the rowid that was just added.  I ran the CLI on 
the database file and did a SELECT there and I know that the record exists and 
the rowid that I'm searching for does exist.  But sqlite3_bind_int() inserts a 
NULL instead of the rowid.

> On Apr 12, 2019, at 1:26 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 6:23pm, Jim Dossey  wrote:
> 
>> I did use sqlite3_last_insert_rowid() to get the last rowid. But I used 
>> sqlite3_expanded_sql to get the actual SQL statement that was processed to 
>> find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I 
>> was looking for.
> 
> Sorry I misunderstood your post.  As a debugging test, have you 
> printed/logged the value received from sqlite3_last_insert_rowid() to make 
> sure it's the expected value ?
> ___
> 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] Problem with SELECT by rowid after INSERT

[Simon Slavin]

On 12 Apr 2019, at 6:23pm, Jim Dossey  wrote:

> I did use sqlite3_last_insert_rowid() to get the last rowid. But I used 
> sqlite3_expanded_sql to get the actual SQL statement that was processed to 
> find out that sqlite3_bind_int() had inserted a NULL instead of the rowid I 
> was looking for.

Sorry I misunderstood your post.  As a debugging test, have you printed/logged 
the value received from sqlite3_last_insert_rowid() to make sure it's the 
expected value ?
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I did use sqlite3_last_insert_rowid() to get the last rowid.  But I used 
sqlite3_expanded_sql to get the actual SQL statement that was processed to find 
out that sqlite3_bind_int() had inserted a NULL instead of the rowid I was 
looking for.

> On Apr 12, 2019, at 12:09 PM, Simon Slavin  wrote:
> 
> On 12 Apr 2019, at 4:40pm, Jim Dossey  wrote:
> 
>> Which is obtained by calling sqlite3_expanded_sql().
> 
> Was that a copy-paste error or do you actually want
> 
> 
> ___
> 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] Problem with SELECT by rowid after INSERT

[Simon Slavin]

On 12 Apr 2019, at 4:40pm, Jim Dossey  wrote:

> Which is obtained by calling sqlite3_expanded_sql().

Was that a copy-paste error or do you actually want


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

[sqlite] Problem with SELECT by rowid after INSERT

[Jim Dossey]

I have a table define like this:

CREATE TABLE "sessiond" (
"journal" VARCHAR(4) DEFAULT '' NOT NULL,
"session" VARCHAR(16) DEFAULT '' NOT NULL,
"pid" INTEGER DEFAULT 0 NOT NULL,
rowid INTEGER PRIMARY KEY
);

In my application I open 2 connections to this table, one for reading and one 
for writing.  I do that because sometimes we do a SELECT, and while reading 
through those rows we'll UPDATE them.  With only 1 connection the SELECT will 
block the UPDATE.  I also have WAL mode turned on.

The problem is when I do an INSERT and then try to SELECT that record by rowid 
it doesn't find it.  The INSERT is done on the write connection and the SELECT 
is done on the read connection.  However, if I open a new connection and do the 
SELECT, the new row is there.

To be a little more specific, the problem happens when I try to do 
sqlite3_bind_int() on the prepared statement using the new rowid.  It doesn't 
use the rowid it uses NULL.

The prepared statement is "SELECT * FROM sessiond WHERE rowid=?;"
Then I call sqlite3_bind_int(ppStmt, 1, rowid) and the resulting SELECT command 
is
SELECT * FROM "sessiond" WHERE "rowid"=NULL;
Which is obtained by calling sqlite3_expanded_sql().

If I use an older rowid in the SELECT, it works just fine.  It just doesn't 
work when using the rowid that was just created.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users