Zeoslib Portal

Aha! Thanks for that fix! This pointed out one other place where I wasn't checking the return value of GetLob for nil. In procedure TZCachedResultSet.CacheAllLobs; could you add the check as follows? Current := FRowAccessor.GetBlob(ColumnIndex, LastNull); if (Current <> nil) and not Current.IsCached...

No worries. I was absolutely not suggesting there would be two properties in any kind of release version. I just meant that I can put in the ability to defer lob caching without breaking compatibility so that it can be tested and final decisions made.

-Mark

I totally understand and agree! Right now I've made the changes to the dbc layer, such that you can set the LobCacheMode to a default value in code, but keep all existing behavior of CachedLobs. So the initial value of LobCacheMode would be lcmNone, causing the CachedLobs setting to be used instead....

Just wanted to say that I have an initial implementation of this that looks pretty good. I'll post my code after some testing and cleanup.

-Mark

The changes to the timezone stuff for Pg are great! A few issues: in TZAbstractPostgreSQLPreparedStatementV3.GetRawEncodedSQL it does: if (FTokenMatchIndex = -1) and (FPQResultFormat = ParamFormatBin) then However FPQResultFormat hasn't been set yet and since it's not initialized it is always zero (...

And their response (no surprise here!) Pg bug report response: A quick look at timestamptz_send and timetz_send shows that they both just send the internal representation; there isn't any intent to do any sort of timezone adjustment. Even if there were some reason to argue that this is wrong, the be...

Update! I've posted the bug to Pg's bug reporting system (which looks like it feeds the bug report mailing list.) Id #16923.

-Mark

Very nice! My particular instance never sends initial comments to the SQL property so I'm not affected by that (and I use the case insensitive .StartsWith call for the case part.) That's an interesting point about summer/winter time, we also have that (though there's talk of getting rid of it lol!) ...

I'm not sure if this is useful, but just in case note that you can get the offset milliseconds directly using:

select extract(timezone from current_timestamp) * 1000000;

It looks like extract timezone was added in PostgreSQL 7.4.

-Mark

Michael, Your fix is fantastic! Nicely done! I've verified that it works well in all the cases I've thrown at it so far. I have the need to update the offset anytime a "set timezone xxx" or "set time zone xxx" statement is run, so I put the UpdateTimestampOffset procedure into IZ...

Exactly! We all know how much of a can of worms dealing with timezones is! I looked into making a Pg bug report on this, but I have a feeling that the answer would be "working as designed" or "won't fix since it will affect backwards compatibility."

-Mark

Excellent points!

Currently if CachedLobs is false, then lobs are read every time they are accessed. If CachedLobs is true, lobs are read on record load and cached. My suggestion is to add an additional "DeferredLobs" option which will cache lobs, but only after they've been read/accessed and only when Cach...

My testing shows that if the postgresql protocol is set for binary return values, that timestamptz fields are always returned as UTC. This appears to be contradictory to the documentation which states that they are output in the current sessions timezone setting. If the protocol is set for string re...

Thanks Jan. It's pretty interesting. I'm aware of the various set time zone settings and have included that in my testing. As you mention the time zone defaults to the server tz, but I'm seeing all timestamptz datatypes are returned in UTC regardless of other settings (unless they are converted to t...