Re: Freezing localtimestamp and other time function on some value

On 13.04.2016 17:26, Adrian Klaver wrote:
> 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 ;)
>>
>
>
>> "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) "

And if we use TL;DR tag on your link we'll see ;)
"This parameter did help us in testing future and in past but we had our
own share of issues also for application testing."

Did help us + issues = Did help us and ≠ issue ;)

Say if we don't need this feature- we dont use it, but if we need it
but we have nothing it makes us sad. I think that have feature > have
not =)..

--
Alex Ignatov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company