#120 Postgresql, refresh table structure on every field every time.

[Spliter]

Hi,
I think i found problem in versions since 7.1.4.
In ZDbcPostgreSql there is function GetTableInfo. In new versions there is line - if Result^.ColCount <> CurrentFieldCount then //something changed ?
On slow internet connection this line it's killing me.
I have query, with 39 fields (from 10 differnt tables), on every field, it goes through this function (maybe there is mistake), but it compares query field count vs table field count on this specific field. It never matches, so on every field it selects table structure.So on every refresh program call 39 selects from postgres, to get table structure.
I dont know, maybe that should be called only on table, not on query?

I hope you got me.
Thx!

[marsupilami79]

Hello Spliter,

I think you are right, this is a problem and needs to be changed. In the meantime I can only suggest to set the ReadOnly property of your query to True as long as you only intend to show data. This will keep the PostgreSQL driver from loading the metadata.
Also you could simply remove the following part of GetTableInfo for now because it causes the problem and needs to be solved in another way:
else
begin
Result := @FTblInfo[Idx];
if Result^.ColCount <> CurrentFieldCount then //something changed ?
LoadTblInfo(TblOid, Idx, Result); //refresh all data
end;

Usually this part of the driver is intended to speed up everything on slow connections because it tries to cache meta data of the PostgreSQL database. But it will cause problems if one changes the database structure during runtime. If you don't change the structure of your database during runtime, everything should be good if you remove the suggested part.

@EgonHugeist: How does the component layer of Zeos treat its metadata cache? When is it cleared? Is there a method to do so?

[EgonHugeist]

Jan,

the the metadata cache will never be cleared until disconnecting or call /TZConnection.dbc)Connection.GetMetadata.ClearCache().

As a workaraound add NoTableInfoCache=True to the TZConnection/TZDataSet.Properties
this suppresses the horrable slow PostgresSQL column desciption.

From my POV this beahvior should be changed to UseTableInfoCache=True to enable this determination as feature.

Cheers, Michael

[EgonHugeist]

We had a long discussion according the TableInfo-Cache.

Original idea was a bugfix for broken updates from 6-Series to 7, where the IdentifierConverter has been used everywhere. So if the zeos-user code wasn't witten case-sensitive in all areas some Queries might be ReadOnly. Therefore Jan did introduce the Cache as bugfix..

However you can suppress querying meta-information by addin UseTableInfoCache=False to your Connection.Properties.
So no change is required.

Cheers, Michael