Re: [sqlite] sqlite3_get_auxdata() defect

[Stadin, Benjamin]

Hi Clemens,

Even though the documentation makes no guaranty about the lifetime, it is was 
also for me rather useless. 

I need to do some spatial calculations in my custom SQL function, and use the 
proj4 projection library within it. This SQL function is called very often, and 
having to recreate the proj4 object with projection params each time was a huge 
performance issue for me. I wasn’t able to figure out when the data was 
actually cached – during my tests I simply got the free callback fired 
immediately after each step. 

I also had a second use case which actually requires a guaranty for the aux 
data to exist (providing the current position of a 3D camera. Somewhat 
comparable to a date function – you get current data whenever you call it).

I eventually hacked the VDBE struct, added a pointer to it, and altered some 
places in order to be able to pass a pointer to the sqlite3_step which I then 
retrieve from the custom sql function from the context via something like 
sqlite3_get_custom_ptr. 

I would call my implementation a hack, though I simply required the performance 
improvement and the guaranty for the instance be available whenever the 
function gets called. It would be great if there was a (probably new) API which 
does that in a proper way.

Regards
Ben

Am 27.11.17, 22:28 schrieb "sqlite-users im Auftrag von Clemens Ladisch" 
:

petern wrote:
> Evidently the sqlite3_get_auxdata() API is very buggy.

The documentation says that
| under some circumstances the associated metadata may be preserved.

You have found circumstances where the metadata is not preserved.

This is not a bug.

I can see that you are unhappy, but that is just because you are trying
to make it do something that it was never intended to do.

If you want a function with mutable state over multiple invocations,
do not use sqlite3_set_auxdata() but an aggregation function, or store
the data elsewhere.


Regards,
Clemens
___
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] sqlite3_get_auxdata() defect

[petern]

Clemens,

Indeed.  It doesn't work as a basis for reliable performance improvement of
user code.  Basically, that API is practically useless except for the
narrow in-house use case for which it was created.  And yes, I already make
heavy use of the natural aggregate cache functionality but for the
limitation of only producing only one final output value.  A statement
level function cache obviously has more general use.

So, as I pointed out to Ben, the other poster who also discovered the
limitations of sqlite3_get_auxdata(), it would be trivial to get the
desired statement level cache speedup if only there were a per-connection
version of the API, say sqlite3_set_data(sqlite3*,void*) and void*
sqlite3_get_data(sqlite3*).

A connection level data area is definitely missing from the API.  To
implement one's own thread aware global connection to cache map wrapper
seems a lot to ask when it could so easily be added to the SQLite API.
Such an SQLite API implementation has a huge advantage in already having an
internal hash map implementation and total awareness of current operational
threading mode of SQLite.

That being said, there is still a documentation bug that is wasting
people's time.  A face value reading of the documentation at
https://www.sqlite.org/c3ref/get_auxdata.html gives the impression that
caching is solely dependent on the index and value of the function argument
when in fact there are 3 more very important and unstated limitations.
Documenting those limitations would only add one or two sentences but would
save people a lot of time.

Peter





On Mon, Nov 27, 2017 at 1:27 PM, Clemens Ladisch  wrote:

> petern wrote:
> > Evidently the sqlite3_get_auxdata() API is very buggy.
>
> The documentation says that
> | under some circumstances the associated metadata may be preserved.
>
> You have found circumstances where the metadata is not preserved.
>
> This is not a bug.
>
> I can see that you are unhappy, but that is just because you are trying
> to make it do something that it was never intended to do.
>
> If you want a function with mutable state over multiple invocations,
> do not use sqlite3_set_auxdata() but an aggregation function, or store
> the data elsewhere.
>
>
> Regards,
> Clemens
> ___
> 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] sqlite3_get_auxdata() defect

[Clemens Ladisch]

petern wrote:
> Evidently the sqlite3_get_auxdata() API is very buggy.

The documentation says that
| under some circumstances the associated metadata may be preserved.

You have found circumstances where the metadata is not preserved.

This is not a bug.

I can see that you are unhappy, but that is just because you are trying
to make it do something that it was never intended to do.

If you want a function with mutable state over multiple invocations,
do not use sqlite3_set_auxdata() but an aggregation function, or store
the data elsewhere.


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

Re: [sqlite] sqlite3_get_auxdata() defect

[petern]

Clemens.

Here it that example again, but with the argument factored out so there is
no question about value or memory location of the cache argument target.
Remarkably, caching doesn't work at all when one does this!

sqlite> WITH arg(x) AS (SELECT 'x'), t(id) AS (VALUES(1),(2)) SELECT
id,auxint(x,id),auxint(x,1),auxint(x,null),auxint(x) FROM arg,t;
id  auxint(x,id)  auxint(x,1)  auxint(x,null)  auxint(x)
--    ---  --  --
1   1 1
2   2 1

Compare with the original example using literal value for the cached
argument:

sqlite> WITH t(id) AS (VALUES(1),(2)) SELECT
id,auxint('x',id),auxint('x',1),auxint('x',null),auxint('x') FROM t;
id  auxint('x',id)  auxint('x',1)  auxint('x',null)  auxint('x')
--  --  -    ---
1   1   1
2   3   2

I think the poster Ben, from last week, bumped into the above bug and was
seeking advice about why his function argument cache was being flushed for
every row.

Evidently the sqlite3_get_auxdata() API is very buggy.

Clemens, I'm a bit surprised you can't see or don't want to see the defects
here.  Very curious to read your reply.

For completeness, here is the testing extension auxint.c once again:
--
/*
Further to https://sqlite.org/c3ref/get_auxdata.html
UDF auxint(X,I) - stores I plus the function argument X's current metadata
INT and returns the new value.
UDF auxint(X) - returns the current value of the function meta-data INT
associated with X.
*/
#include "sqlite3ext.h"
SQLITE_EXTENSION_INIT1
static void reclaim(void*p) {if (p) sqlite3_free(p);}
static void auxint(sqlite3_context *context, int argc, sqlite3_value
**argv) {
  int* pInt = sqlite3_get_auxdata(context, 0);
  if (argc>1 && sqlite3_value_type(argv[1]) != SQLITE_NULL){
if (!pInt) {pInt = sqlite3_malloc(sizeof(int)); *pInt=0;
sqlite3_set_auxdata(context, 0, pInt, reclaim);}
*pInt += sqlite3_value_int(argv[1]);
  }
  if (pInt) sqlite3_result_int(context,*pInt);
}
#ifdef _WIN32
__declspec(dllexport)
#endif
int sqlite3_auxint_init(sqlite3 *db, char **pzErrMsg, const
sqlite3_api_routines *pApi) {
  SQLITE_EXTENSION_INIT2(pApi);
  int rc = sqlite3_create_function(db, "auxint", 1, SQLITE_UTF8, 0, auxint,
0, 0);
  if (SQLITE_OK == rc) sqlite3_create_function(db, "auxint", 2,
SQLITE_UTF8, 0, auxint, 0, 0);
  return rc;
}
--

On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch  wrote:

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away.  And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere.  And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> ___
> 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] sqlite3_get_auxdata() defect

[petern]

>The auxdata is intended as a cache, so it must depend only on the
>argument value, not on any other state.

I'm not following.  Where is the argument different in my example.  The
zeroth argument is always 'x'.  Are you saying the argument has to be the
same memory location as well as the same value?

Take another look at the example.



On Mon, Nov 27, 2017 at 1:15 AM, Clemens Ladisch  wrote:

> petern wrote:
> > So, at the very least, the documentation at
> > https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete
> because:
> >
> > 1. The identical function in a different column of the same SELECT has a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > 2. Functions with identical names and different arg count, yet sharing
> > common identical meta data argument at the specified argument index, use
> a
> > separate meta-data cache.
>
> This is an implementation detail of the current version.
>
> > Ideally, these problems should be fixed instead of simply being
> documented
> > away.  And, if they are fixed, SELECT columns can have an efficient,
> > thread-safe, and powerful new expressiveness for the idioms of running
> > sums, running products, running concatenations, synthetic id columns, and
> > so on.
>
> The auxdata is intended as a cache, so it must depend only on the
> argument value, not on any other state.
>
> If you want to do something different, store the data elsewhere.  And
> there is not much of a guarantee when or how often user-defined functions
> are called, so even with your desired changes to the auxdata mechanism,
> you would not get the desired result.
>
>
> Regards,
> Clemens
> ___
> 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] sqlite3_get_auxdata() defect

[Clemens Ladisch]

petern wrote:
> So, at the very least, the documentation at
> https://sqlite.org/c3ref/get_auxdata.html is woefully incomplete because:
>
> 1. The identical function in a different column of the same SELECT has a
> separate meta-data cache.

This is an implementation detail of the current version.

> 2. Functions with identical names and different arg count, yet sharing
> common identical meta data argument at the specified argument index, use a
> separate meta-data cache.

This is an implementation detail of the current version.

> Ideally, these problems should be fixed instead of simply being documented
> away.  And, if they are fixed, SELECT columns can have an efficient,
> thread-safe, and powerful new expressiveness for the idioms of running
> sums, running products, running concatenations, synthetic id columns, and
> so on.

The auxdata is intended as a cache, so it must depend only on the
argument value, not on any other state.

If you want to do something different, store the data elsewhere.  And
there is not much of a guarantee when or how often user-defined functions
are called, so even with your desired changes to the auxdata mechanism,
you would not get the desired result.


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