Re: Freezing localtimestamp and other time function on some value

On 13.04.2016 18:40, Alex Ignatov wrote:
>
>
> 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
>
Some quick and dirty issue resolution is simple:
set search_path = my_time_schema on db layer. After that you dont need
to change any code. And can take for example freeze.fixed_date from
config =)
where my_time_schema contains all time function than I want to freeze.
Nevertheless i dont know how to deal with say localtimestamp with this
approach %). Where localtimestamp is defined? pg_catalog doesnt have it

Some thoughts about localtimestamp redifinition with search_path?

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