I am a first time ZEOSLib user and thought it was great (if a little slow) till I started getting "0 record(s) updated. Only one record should have been updated" errors.
After searching the forum without finding an answer to my particular scenario I decided I would have to track it down myself. I did get some clues - use ZSQLMonitor to look at the actual queries being used which led me to find that the WHERE clauses of the UPDATE statements use all the fields of the table, not just the primary key field ID.
Turns out it is caused by TZPostgreSQLDatabaseMetadata.UncachedGetPrimaryKeys calling ConstructNameCondition which calls NormalizePatternCase which, because StoresLowerCaseIdentifiers returns True and the name is not quoted, converts the table name into all lower case. UncachedGetPrimaryKeys uses the returned TableNameCondition to construct a metadata query. The results of this query eventually gets used by DefineWhereKeyColumns which because WhereColumns.Count is 0 (i.e. didn't find a defined primary key for the table because it used the lower case name) uses all the fields of the table.
UncachedGetPrimaryKeys just uses the Table name passed down. Ultimately it comes down from DefineWhereKeyColumns which gets it from a columns metadata via TZAbstractResultSetMetadata.GetTableName.
I can see two ways to fix the problem:
1. Quote the table name somewhere along the chain or;
2. Change TZPostgreSQLDatabaseInfo.StoresLowerCaseIdentifiers to return False.
Because the code involved in solution 1 is generic and abstract, its probably best not to mess with that. So solution 2 seems the best specially as PostgreSQL does store its identifiers in mixed case when quoted. But then there is TZPostgreSQLDatabaseInfo.StoresMixedCaseQuotedIdentifiers. So would it be better to go with solution 1 and use TZPostgreSQLDatabaseInfo.StoresMixedCaseQuotedIdentifiers (which returns true) somewhere to quote the table name it gets from the metadata before using it in the query? Are there other similar areas?
Now for the questions:
Which solution do you guys think is best?
Has this already been identified and fixed in a later version?
Should I just patch my own code as not sure what other issues might arise moving to 7.2?
If so how do I go about doing this?
Would this be affecting performance significantly - using the entire table to identify the row (there are 47 fields in some tables) instead of the indexed Primary Key and is this why I think it is running slow?
