Postgresql TEMP tables grid update problem

[markus]

Thats why the STMT is prepared and cached too. One of the performance goals. Simply close the DataSets which accessing the changed table. After your changes are done, the Cache is cleared you can reopen them again. We do NOT cache the Stmts it's selves in a list or so (for updates i'm doing this btw.).
The more i wonder why PG allows doing such things while prepared smts are active and compiled to use such objects!

Is there any parameter to be set that clears cache after dataset is closed?
I use

Code: Select all

ZQuery1->Active = false;
ZQuery1->Active = true;

to refresh data after structure change, and i need to clear cache first manually.
Prepared statements are cached by PG itself, so when i use SQL like: select * from table
and then i change it's structure - resultset is changed and postgres raises exception.
I just got brilliant idea , and changed code to:

Code: Select all

ZQuery1->Active = false;
  ZQuery1->Unprepare();
  ZQuery1->Active = true;

voila! it works:)

i'm starting from the premisse you know what dou are doing, Marek.

I've always thought that randomly altering DB structure is postgres best practice;)
But, with full seriusness i had few queries that were on "live changing" table in previous application, but it was all working on zeos 7.0 with only emulated statements

Michael - you are right, Zeos will not have solution for everything - it's not the goal of library.
Ability of clearing selected portion of cache would be nice feature:)

[EgonHugeist]

Did i for got the

Code: Select all

TZDataSet.Unprepare;

Advice?!

Thanks for the ticket.

(everything that was cached for it)

It might be possible except cached stmts it's selves... IMHO is there no way around the Unprepare command...

[marsupilami]

Hello

I am sorry for the long delay - I had to go on a business travel on short notice...

I had a look at the code today. It should be quite simple to make the TZPostgreSQLConnection internal meta data chache depend on a parameter. I have just one question to Egonhugeist: Do you remember why you used a pointer as the result of TZPGTableInfoCache.GetTableInfo?

As for the question wether it would be possible to clear both caches at the same time - hmm - it seems that the Zeos-Cache should know when to clear the DBC-Cache and call the clear method?

With best regards,

Jan

[EgonHugeist]

Hello Jan,

I have just one question to Egonhugeist: Do you remember why you used a pointer as the result of TZPGTableInfoCache.GetTableInfo?

wasn't it because we need a empty record if Informations are not equal or couldn't be found? AFAIR was this way faster than return a newly initialized empty record.

hmm - it seems that the Zeos-Cache should know when to clear the DBC-Cache and call the clear method?

That won't be possible. Maybe this is a misunderstanding. IMO we need a ClearCache(Zeos-Metatdata) for named and type known objects it's selves.

Just an example:
if Marek did play with table "foo" Zeos did load the TableInfo-cache and did cahce the Metadata-Info's. Now Marek is altering some columns ... It would be nice to clear the Zeos cache for this table only(including your TableInfo-Cache)..

Issues i see: We're using Hash codes because comparing Cardinals is faster than comparing strings(Zeos all time code as long as i know Zeos), which than means -> a little bad Typo or non-casesensitive searched Object-name could clear the cache for a differnet object... Any ideas?

[marsupilami]

Hello Michael,

I used records in my first code because using them means that I don't have to think about when to clean up memory for them. Also I was thinking that records are reference counted and have copy on write semantics like strings have. But I might be wrong there. I didn't see the speed of the local program as a problem because reading of table meta data should be a quite rare opertation in comparison to other operations that are done in a database based application.

Anyway - if we want to keep the pointer there we will have to make sure, the record will still be available when we don't want to cache meta data. Basically that means we will need a cache with exactly one member that we have to overwrite every time we look up data from the database or something similar.

Cleaning the cache of specific information is hard if you only have numbers to rely on. Basically Zeos would have to convert the case of every identifier to the database conventions of the underlying database if it is not quoted. Let me give an example for that. select * from TeSt will have a different meaning, depending on the database:
Firebird: select * from TEST
PostgreSQL: select * from test
Sybase ASE (12.5 version): select * from TeSt
Zeos would have to use the different ways the databases handle this for creating its hash codes. I don't know if ist is already done like that. If you have that, you could implement a procedure like this:
procedure TZConnection.DeleteTableFromCache(TableName: String; CaseSensitive: boolean = false);
This procedure could then convert the table name, if necessary and calculate the necessary hash for deleting the meta data. Also it could give the corrected table name to the DBC layer for the cache deletion:
procedure IZPostgreSQLConnection.DeleteTableFromCache(TableName: String);

I hope that helps?

Jan