Re: Freezing localtimestamp and other time function on some value

On 04/13/2016 04:16 AM, Alex Ignatov wrote:
>
>
> On 12.04.2016 20:50, Tom Lane wrote:
>> Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru> writes:
>>> You always should keep in mind that your application may run in test
>>> mode (future/past time) and maintain this code. While with my proposal
>>> you can always use some time function(now or localtimestamp or
>>> whatever) which you can freeze at anytime on DB level, not operation
>>> system(using some 3rd libs) or application(using wrappers and other
>>> hacks).
>> We're not really in the business of being Oracle --- which in this
>> particular context means not trying to duplicate tens of thousands of
>> bizarre little features with very narrow use-cases. If there's a
>> reasonable way for users to provide corner-case functionality for
>> themselves (and I'd say a wrapper function is a perfectly reasonable
>> way for this) then we don't really want to embed it in Postgres.
>>
>> This particular feature seems like a seriously-poorly-thought-out
>> one, too. Time stops advancing across the whole DB? Really?
>>
>> 1. That would break all manner of stuff, for example the timestamps
>> in automatically-taken dumps, if you've got background jobs running
>> pg_dump. Just about everything except the session running the test
>> case would be unhappy, AFAICS.
>>
>> 2. Would this extend to, say, preventing autovacuum from running?
>> Or changing the timestamps of messages in the postmaster log, or
>> timestamps appearing in places like pg_stat_activity? Or causing
>> pg_sleep() to wait forever, because time isn't passing? If your
>> answer is "yes" across the board, that makes problem #1 an order
>> of magnitude worse, while if you want to be selective then you
>> have a bunch of nitty-gritty (and rather arbitrary) decisions to
>> make about what's frozen and what's not. And you've weakened the
>> argument that your test is actually valid, since potentially the
>> app would see some of the non-frozen values and misbehave.
>>
>> 3. While I can see the point of wanting to, say, test weekend behavior
>> on a weekday, I do not see how a value of now() that doesn't advance
>> between transactions would represent a realistic test environment for
>> an app with time-dependent behavior. As an example, you might
>> accidentally write code that expects two successive transactions to
>> see identical values of now(), and such a testbed wouldn't detect
>> the problem.
>>
>> regards, tom lane
> 1. background jobs in pg?? cron you mean or may be EnterpriseDB vesion?
> 2. All i need is to freeze some(or may be one ) function for example
> now() or smth else =). I dont want to freeze time for the whole
> postmaster process!

That was not obvious:

http://www.postgresql.org/message-id/570CD2E3.4030400@postgrespro.ru

"In oracle there is alter system set fixed_date command. Have Postgres
this functionality?"

https://appsfromrajiv.wordpress.com/2011/06/14/oracle-fixed_date-parameter-helpful-during-testing/

"This parameter is useful primarily for testing. The value can be in the
format shown above or in the default Oracle date format, without a time.
Setting this parameter to a specified timestamp will make the time
constant for the database engine (the clock will not tick) "

http://www.postgresql.org/message-id/570CE996.30301@postgrespro.ru

"Hi!
It is not about localtimestamp in transactions. It is about global
localtimestamp value for all session new and existed no matter inside
transaction or outside."

> 3. In multithreaded applications it is possible that two transactions
> from different sessions started at the same time and to resolve this
> issue some sort of unique id(say serial) is used while inserting some
> value in some table ;)
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com