db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Satheesh Bandaram <sathe...@Sourcery.Org>
Subject Re: [jira] Updated: (DERBY-1107) For existing databases JDBC metadata queries do not get updated properly between maintenance versions.
Date Wed, 29 Mar 2006 20:26:31 GMT
Good write up, describing metadata design well... I think one of the
advantages of using SPS for metadata is performance. SPS statements are
pre-compiled and fast to execute. Compiling these (large) queries slow
down commonly used metadata queries. Statement caching reduces some
future need to recompile, but plans could go out of cache after some time.

Knut Anders Hatlen wrote:

>  - if you move from one version to a version with the same major and
>    minor version (say, from to, the SPSs are
>    invalidated, but not dropped. This means they will be recompiled,
>    but still use the old SQL code. Hence, changes to an existing
>    query in metadata.properties will not be visible to the users.
May be I missed some previous messages about this... Are there real
examples of need to change metadata between maintenance releases?
Probably to fix specific bugs?  I looked at DERBY-1107 and it talks
about general need to have a mechanism. Don't think I saw any specific

>To solve the problem for the last case, there are at least three
>  1) Drop and regenerate SPSs on all version changes.
>  2) Stop using SPSs (use ordinary prepared statements instead).
>  3) Instead of modifying an existing query, one could create a new
>     one with a new name.
>To me, option 1 seems like the best choice.
I think so too... but I would like to see specific reasons to need this.
Some concerns are 1) How would this affect read-only databases? If we
decided to drop them even for maintenance versions or point-releases,
need to handle read-only dbs 2) Performance considerations. Could we
instead make it as need arises? Like if version is going from 10.1.2 to
10.1.3 (just example) Hopefully these are rare cases.

>As you suggested, the client could invoke the SQL directly. This would
>however cause some problems:
>  - if there is a bug in a metadata query, it is not enough to upgrade
>    the server, you would also have to upgrade all the clients.
>  - if the server changes the name of a system table or a column in a
>    system table, old clients will be broken.
>The way it is now, if the bug is fixed on the server, it is also fixed
>on the client.
Right... I think we should keep SPS for metadata.


View raw message