SoftTree Technologies SoftTree Technologies
Technical Support Forums
RegisterSearchFAQMemberlistUsergroupsLog in
[12.0.167 BETA] - Refresh cache

 
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant View previous topic
View next topic
[12.0.167 BETA] - Refresh cache
Author Message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post [12.0.167 BETA] - Refresh cache Reply with quote
Automatic refreshing the cache is not reliable. The tooltip does warn: "after executing SQL code which contains DDL statements". But if that means it only checks for DDL statements in the query text that is passed to the server instead of checking the code that is actually executed, it will not trigger that refresh every time the DDL statements are disguised as a stored procedure call or are wickedly hidden in a dynamic SQL.

I know that explicitly checking everything to an arbitrary depth (the stored procedures could call other stored procedures which could call...) is definitely not a cool thing to even try (performance-wise, at least), but isn't there a cheap way to check if anything DDL-like was covertly executed? I don't have the slightest idea how that could be implemented, I just hope you have an ace or two up your sleeve.
Fri Oct 01, 2021 3:31 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
My 2 cents, I believe in some database it can be done with a very little performance hit, for example, in MSSQL, in some it can't be done even if there was such a thing as last modification date.

SELECT MAX(o.modify_date) FROM sys.objects AS o

In comparison, in PostgreSQL there is no last modification date, worse there is no create date for schema objects, one is left to do full mapping and comparison to find out what has changed recently. With Oracle, everything is there, available, but too performance expensive to query on last modification date without filters.
Fri Oct 01, 2021 7:38 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I was afraid you were going to say that :( I guess Postgres is a lost cause then but perhaps Oracle is not. Would a "check if records exist with last modification date greater than execution starting date and only refresh if they do" still be too expensive? Isn't there an index on that column?
Sun Oct 03, 2021 2:20 am View user's profile Send private message
SysOp
Site Admin


Joined: 26 Nov 2006
Posts: 7840

Post Reply with quote
In my opinion PostgreSQL is a lost cause. It's a very long running discussion in the PostgreSQL community as far as I know. PostgreSQL core developers refusing to add dates to the system catalog and insisting that users don't need them, and users are wrong when they ask for dates, users should be relying on their source control systems for the change history instead of the database catalog. I personally think that is a retarded point of view. there might be some other workaround, like querying max(oid) in pg_attribute, but that is very unreliable, because temporary objects, toast tables, and other things also add stuff to that table, and it changes very often.

I'm unable to answer your question in regard to Oracle without experimenting and I suspect the answer won't be conclusive. It depends on the size of the database, state of the statistics collected on the system tables, and other factors. Instead of using very complex multi-level catalog views joining many tables many times (ALL_OBJECTS is a very good example) one can query directly # tables, but to be able to go against raw tables need to have a DBA role, or SELECT ANY privileges, which normally regular users don't have.
Sun Oct 03, 2021 8:41 am View user's profile Send private message
gemisigo



Joined: 11 Mar 2010
Posts: 2102

Post Reply with quote
I agree. I instantly began to like Postgres for many things when I started learning it, but there were an almost equal number of ridiculously stupid things done (eg. inconsistently named objects/columns and now this no-date in system catalog). Telling your "customers" they're wrong without providing a working alternative is never a wise thing to do. Always comparing to source control system contents is not a cheap operation. And what if there is no source control? You can fall back to what? Void?

It seems that Oracle is a lost cause too. And I'm no longer confident it is worth the bother to implement a minor improvement that would only work in a single RDBMS. Perhaps a more "aggressive" approach by making this a three-state setting (do not refresh, refresh if DDL detected, refresh always) could work for the more fanatic users like myself, but I don't think most of the other users would use it much.
Sun Oct 03, 2021 11:24 am View user's profile Send private message
Display posts from previous:    
Reply to topic    SoftTree Technologies Forum Index » SQL Assistant All times are GMT - 4 Hours
Page 1 of 1

 
Jump to: 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


 

 

Powered by phpBB © 2001, 2005 phpBB Group
Design by Freestyle XL / Flowers Online.