Re: Freezing localtimestamp and other time function on some value

Hello!
Is there any method to freeze localtimestamp and other time function value.
Say after freezing on some value sequential calls to these functions
give you the same value over and over again.
This is useful primarily for testing.

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

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

2016-04-12 13:50 GMT+03:00 Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>:

> Hello!
> Is there any method to freeze localtimestamp and other time function value.
> Say after freezing on some value sequential calls to these functions give
> you the same value over and over again.
> This is useful primarily for testing.
>

Hello!

I hope, the following snippet will be helpful.
```
begin;
select now(), current_timestamp, clock_timestamp();
select pg_sleep(5);
select now(), current_timestamp, clock_timestamp();
commit;
```

Hi

2016-04-12 12:50 GMT+02:00 Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>:

> Hello!
> Is there any method to freeze localtimestamp and other time function value.
> Say after freezing on some value sequential calls to these functions give
> you the same value over and over again.
> This is useful primarily for testing.
>
> In oracle there is alter system set fixed_date command. Have Postgres this
> functionality?
>

It is not possible in Postgres

PostgreSQL solution is using working time as function parameter. This
parameter can have default value.

postgres=# select test('2016-03-10 10:00:00');

NOTICE: current time is: 2016-03-10 10:00:00

postgres=# select test();
NOTICE: current time is: 2016-04-12 13:47:21.644488

postgres=# select test();
NOTICE: current time is: 2016-04-12 13:47:22.633711

CREATE OR REPLACE FUNCTION public.test(t timestamp without time zone
DEFAULT now())
RETURNS void
LANGUAGE plpgsql
AS $function$
BEGIN
RAISE NOTICE 'current time is: %', t;
END;
$function$

Regards

Pavel

>
> --
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Sorry.
I have re-read my previous message.
It looks unclean.

For sequential calls in same transaction `now()` and `current_timestamp`
will produce the same output.

```
begin; -- start a transaction

select
now() immutable_now,
current_timestamp immutable_current_ts,
clock_timestamp() mutable_clock_ts;

select pg_sleep(1); -- wait a couple of time

select
now() immutable_now, -- same as above
current_timestamp immutable_current_ts, -- same as above
clock_timestamp() mutable_clock_ts; -- value changed

select pg_sleep(1); -- wait a couple of time again

select
now() immutable_now, -- same as above
current_timestamp immutable_current_ts, -- same as above
clock_timestamp() mutable_clock_ts; -- value changed

commit; -- commit or rollback
```

I think PG does fixed time within a tran. check the output of the following sql

begin;
select now() ;
select pg_sleep(10);
select now() ;
commit;
select now() ;
select pg_sleep(10);
select now() ;
~

On Tue, Apr 12, 2016 at 6:50 AM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru> wrote:
> Hello!
> Is there any method to freeze localtimestamp and other time function value.
> Say after freezing on some value sequential calls to these functions give
> you the same value over and over again.
> This is useful primarily for testing.
>
> In oracle there is alter system set fixed_date command. Have Postgres this
> functionality?
>
> --
> Alex Ignatov
> Postgres Professional: http://www.postgrespro.com
> The Russian Postgres Company
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

On 12.04.2016 15:13, Rakesh Kumar wrote:
> I think PG does fixed time within a tran. check the output of the following sql
>
> begin;
> select now() ;
> select pg_sleep(10);
> select now() ;
> commit;
> select now() ;
> select pg_sleep(10);
> select now() ;
> ~
>
> On Tue, Apr 12, 2016 at 6:50 AM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru> wrote:
>> Hello!
>> Is there any method to freeze localtimestamp and other time function value.
>> Say after freezing on some value sequential calls to these functions give
>> you the same value over and over again.
>> This is useful primarily for testing.
>>
>> In oracle there is alter system set fixed_date command. Have Postgres this
>> functionality?
>>
>> --
>> Alex Ignatov
>> Postgres Professional: http://www.postgrespro.com
>> The Russian Postgres Company
>>
>>
>>
>> --
>> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>
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.

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

On Tue, 12 Apr 2016 13:50:11 +0300, Alex Ignatov
<a(dot)ignatov(at)postgrespro(dot)ru> wrote:

>Is there any method to freeze localtimestamp and other time function value.
>Say after freezing on some value sequential calls to these functions
>give you the same value over and over again.
>This is useful primarily for testing.
>
>In oracle there is alter system set fixed_date command. Have Postgres
>this functionality?

I'm missing how this is useful. Even having such a feature there is
not any way to duplicate a test trace: execution time of a request is
not guaranteed even if it's issue time is repeatable wrt some epoch.
And if there are concurrent requests, their completion order is not
guaranteed.

It is also true in Oracle, and in every general purpose DBMS that I
know of. So what exactly do you "test" using a fixed date/time?

George

On 12.04.2016 16:57, George Neuner wrote:
> On Tue, 12 Apr 2016 13:50:11 +0300, Alex Ignatov
> <a(dot)ignatov(at)postgrespro(dot)ru> wrote:
>
>> Is there any method to freeze localtimestamp and other time function value.
>> Say after freezing on some value sequential calls to these functions
>> give you the same value over and over again.
>> This is useful primarily for testing.
>>
>> In oracle there is alter system set fixed_date command. Have Postgres
>> this functionality?
> I'm missing how this is useful. Even having such a feature there is
> not any way to duplicate a test trace: execution time of a request is
> not guaranteed even if it's issue time is repeatable wrt some epoch.
> And if there are concurrent requests, their completion order is not
> guaranteed.
>
> It is also true in Oracle, and in every general purpose DBMS that I
> know of. So what exactly do you "test" using a fixed date/time?
>
> George
>
>
>

This is useful if your application written say on stored function on PG
and it works differently on working days and on vacations or weekends.
How can you test your application without this ability? Changing system
time and affect all application on server or write your own
localtimestamp implementation keep in mind of test functionality?
Also yesterday we have issue while comparing Pg function output
converted from Oracle and its Oracle equivalent on the same data. You
now what - we cant do it, because function depends on
localtimestamp(Pg) and sysdate (Ora) =/

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

On 04/12/2016 07:36 AM, Alex Ignatov wrote:
> On 12.04.2016 16:57, George Neuner wrote:
>> On Tue, 12 Apr 2016 13:50:11 +0300, Alex Ignatov
>> <a(dot)ignatov(at)postgrespro(dot)ru> wrote:
>>
>>> Is there any method to freeze localtimestamp and other time function
>>> value.
>>> Say after freezing on some value sequential calls to these functions
>>> give you the same value over and over again.
>>> This is useful primarily for testing.
>>>
>>> In oracle there is alter system set fixed_date command. Have Postgres
>>> this functionality?
>> I'm missing how this is useful. Even having such a feature there is
>> not any way to duplicate a test trace: execution time of a request is
>> not guaranteed even if it's issue time is repeatable wrt some epoch.
>> And if there are concurrent requests, their completion order is not
>> guaranteed.
>>
>> It is also true in Oracle, and in every general purpose DBMS that I
>> know of. So what exactly do you "test" using a fixed date/time?
>>
>> George
>>
>>
>>
>
> This is useful if your application written say on stored function on PG
> and it works differently on working days and on vacations or weekends.
> How can you test your application without this ability? Changing system

I do it by having the date be one of the function arguments and have the
default be something like current_date. When I test I supply a date to
override the default. This allows for testing the various scenarios by
changing the supplied date.

> time and affect all application on server or write your own
> localtimestamp implementation keep in mind of test functionality?
> Also yesterday we have issue while comparing Pg function output
> converted from Oracle and its Oracle equivalent on the same data. You
> now what - we cant do it, because function depends on
> localtimestamp(Pg) and sysdate (Ora) =/

Because the Postgres and Oracle servers are on different machines and
are getting different times, because the time functions return different
values from the same time. or something else?

>
>

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

On 12.04.2016 18:01, Adrian Klaver wrote:
> On 04/12/2016 07:36 AM, Alex Ignatov wrote:
>> On 12.04.2016 16:57, George Neuner wrote:
>>> On Tue, 12 Apr 2016 13:50:11 +0300, Alex Ignatov
>>> <a(dot)ignatov(at)postgrespro(dot)ru> wrote:
>>>
>>>> Is there any method to freeze localtimestamp and other time function
>>>> value.
>>>> Say after freezing on some value sequential calls to these functions
>>>> give you the same value over and over again.
>>>> This is useful primarily for testing.
>>>>
>>>> In oracle there is alter system set fixed_date command. Have Postgres
>>>> this functionality?
>>> I'm missing how this is useful. Even having such a feature there is
>>> not any way to duplicate a test trace: execution time of a request is
>>> not guaranteed even if it's issue time is repeatable wrt some epoch.
>>> And if there are concurrent requests, their completion order is not
>>> guaranteed.
>>>
>>> It is also true in Oracle, and in every general purpose DBMS that I
>>> know of. So what exactly do you "test" using a fixed date/time?
>>>
>>> George
>>>
>>>
>>>
>>
>> This is useful if your application written say on stored function on PG
>> and it works differently on working days and on vacations or weekends.
>> How can you test your application without this ability? Changing system
>
> I do it by having the date be one of the function arguments and have
> the default be something like current_date. When I test I supply a
> date to override the default. This allows for testing the various
> scenarios by changing the supplied date.
>
>> time and affect all application on server or write your own
>> localtimestamp implementation keep in mind of test functionality?
>> Also yesterday we have issue while comparing Pg function output
>> converted from Oracle and its Oracle equivalent on the same data. You
>> now what - we cant do it, because function depends on
>> localtimestamp(Pg) and sysdate (Ora) =/
>
> Because the Postgres and Oracle servers are on different machines and
> are getting different times, because the time functions return
> different values from the same time. or something else?
>
>>
>>
>
>
>>Because the Postgres and Oracle servers are on different machines and
are getting different times, because the time functions return different
values from the same time. or something else?

Because while test we ran this function on different time. And you
cant start it in exactly one time even on same server.

>>I do it by having the date be one of the function arguments and have
the default be something like current_date. When I test I supply a date
to override the default. This allows for testing the various scenarios
by changing the supplied date.

With that approach you have to say application programmer - 'Hey dude,
please edit this piece of code for my purpose and after that rollback
it'. I think that it is unacceptable in large project...

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

On Tue, Apr 12, 2016 at 8:37 AM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>
wrote:

>
> On 12.04.2016 18:01, Adrian Klaver wrote:
>
>>
>> >>I do it by having the date be one of the function arguments and have
> the default be something like current_date. When I test I supply a date to
> override the default. This allows for testing the various scenarios by
> changing the supplied date.
>
> With that approach you have to say application programmer - 'Hey dude,
> please edit this piece of code for my purpose and after that rollback it'.
> I think that it is unacceptable in large project...

​CREATE FUNCTION do_some_date_based_stuff(reference_date date, other_args)
[...]

CREATE FUNCTION production_wrapper_for_above(other_args) [...]
AS $$
SELECT do_some_date_based_stuff(now(), other_args);
$$​;

Easy to test do_some_date_based_stuff since it has fewer if any external
dependencies. Shouldn't need to test the wrapper that simply calls the
"do_some..." with a default value of the current date.

You might be able to define an appropriate function signature that avoids
having to write the wrapper though regardless there is no need to have a
different environment for testing versus production if approached in this
manner. You just need to decide on the most desirable way to make it work.

David J.

On 12.04.2016 19:45, David G. Johnston wrote:
> On Tue, Apr 12, 2016 at 8:37 AM, Alex Ignatov
> <a(dot)ignatov(at)postgrespro(dot)ru <mailto:a(dot)ignatov(at)postgrespro(dot)ru>>wrote:
>
>
> On 12.04.2016 18:01, Adrian Klaver wrote:
>
>
> >>I do it by having the date be one of the function arguments and
> have the default be something like current_date. When I test I
> supply a date to override the default. This allows for testing the
> various scenarios by changing the supplied date.
>
> With that approach you have to say application programmer - 'Hey
> dude, please edit this piece of code for my purpose and after that
> rollback it'. I think that it is unacceptable in large project...
>
>
> ​ CREATE FUNCTION do_some_date_based_stuff(reference_date date,
> other_args) [...]
>
> CREATE FUNCTION production_wrapper_for_above(other_args) [...]
> AS $$
> SELECT do_some_date_based_stuff(now(), other_args);
> $$ ​;
>
> Easy to test do_some_date_based_stuff since it has fewer if any
> external dependencies. Shouldn't need to test the wrapper that simply
> calls the "do_some..." with a default value of the current date.
>
> You might be able to define an appropriate function signature that
> avoids having to write the wrapper though regardless there is no need
> to have a different environment for testing versus production if
> approached in this manner. You just need to decide on the most
> desirable way to make it work.
>
> David J.
>

I know that we can always write some wrappers etc, etc.
This approach would failed if your do_some_date_based_stuff have no date
args and contains calls say to now()(or other time function what
possible can have fix value ) inside it.

Also wrappers lead to multiple code base,yours client side code needs
to know what function we should use - test or production. Also with
your approach application server needs to know its working mode test / prod

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).

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

On Tue, Apr 12, 2016 at 10:14 AM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>
wrote:

>
>
> On 12.04.2016 19:45, David G. Johnston wrote:
>
> On Tue, Apr 12, 2016 at 8:37 AM, Alex Ignatov < <a(dot)ignatov(at)postgrespro(dot)ru>
> a(dot)ignatov(at)postgrespro(dot)ru> wrote:
>
>>
>> On 12.04.2016 18:01, Adrian Klaver wrote:
>>
>>>
>>> >>I do it by having the date be one of the function arguments and have
>> the default be something like current_date. When I test I supply a date to
>> override the default. This allows for testing the various scenarios by
>> changing the supplied date.
>>
>> With that approach you have to say application programmer - 'Hey dude,
>> please edit this piece of code for my purpose and after that rollback it'.
>> I think that it is unacceptable in large project...
>
>
> ​ CREATE FUNCTION do_some_date_based_stuff(reference_date date,
> other_args) [...]
>
> CREATE FUNCTION production_wrapper_for_above(other_args) [...]
> AS $$
> SELECT do_some_date_based_stuff(now(), other_args);
> $$ ​;
>
> Easy to test do_some_date_based_stuff since it has fewer if any external
> dependencies. Shouldn't need to test the wrapper that simply calls the
> "do_some..." with a default value of the current date.
>
> You might be able to define an appropriate function signature that avoids
> having to write the wrapper though regardless there is no need to have a
> different environment for testing versus production if approached in this
> manner. You just need to decide on the most desirable way to make it work.
>
> David J.
>
>
> I know that we can always write some wrappers etc, etc.
> This approach would failed if your do_some_date_based_stuff have no date
> args and contains calls say to now()(or other time function what possible
> can have fix value ) inside it.
>
>
This makes no sense and I'm not sure how to explain it better. The point
is for any functions where you think you need to "freeze/specify" time​ you
write the procedure function so that is always uses a caller-specified
reference time. If you want to provide access to the logic without
requiring the caller to supply a reference time then do so. Or just make
callers supply the time they care about. But you'd never put "now()" into
such a function - wherever you would use "now()" you'd use the argument
date instead. IOW, we don't support the feature in question so you have to
modify code if you want similar functionality in PostgreSQL.

Also wrappers lead to multiple code base,yours client side code needs to
> know what function we should use - test or production. Also with your
> approach application server needs to know its working mode test / prod
>
> 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).
>
>
​My proposal has nothing to do with test versus development. Both are
always present and the choice of which function to call​

​is up to the code needing to perform the work.
​

I'm not sure what you are looking for here. PostgreSQL doesn't have this
particular feature. You can either convince others to write it, write it
yourself and work to get it committed, or learn a new approach to solving
the problem that you describe. My intent is to aiding effort toward the
third option.

David J.

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

I wrote:
> 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.

BTW, one possible way of meeting that particular requirement is to fool
with your timezone setting.

regression=# select timeofday();
timeofday
-------------------------------------
Tue Apr 12 14:01:53.254286 2016 EDT
(1 row)

regression=# set time zone interval '+120 hours';
SET
regression=# select timeofday();
timeofday
--------------------------------------
Sun Apr 17 18:01:58.293623 2016 +120
(1 row)

regards, tom lane

On 04/12/2016 10:14 AM, Alex Ignatov wrote:
>
>
> On 12.04.2016 19:45, David G. Johnston wrote:
>> On Tue, Apr 12, 2016 at 8:37 AM, Alex Ignatov
>> <<mailto:a(dot)ignatov(at)postgrespro(dot)ru>a(dot)ignatov(at)postgrespro(dot)ru>wrote:
>>
>>
>> On 12.04.2016 18:01, Adrian Klaver wrote:
>>
>>
>> >>I do it by having the date be one of the function arguments and
>> have the default be something like current_date. When I test I
>> supply a date to override the default. This allows for testing the
>> various scenarios by changing the supplied date.
>>
>> With that approach you have to say application programmer - 'Hey
>> dude, please edit this piece of code for my purpose and after that
>> rollback it'. I think that it is unacceptable in large project...
>>
>>
>> ​ CREATE FUNCTION do_some_date_based_stuff(reference_date date,
>> other_args) [...]
>>
>> CREATE FUNCTION production_wrapper_for_above(other_args) [...]
>> AS $$
>> SELECT do_some_date_based_stuff(now(), other_args);
>> $$ ​;
>>
>> Easy to test do_some_date_based_stuff since it has fewer if any
>> external dependencies. Shouldn't need to test the wrapper that simply
>> calls the "do_some..." with a default value of the current date.
>>
>> You might be able to define an appropriate function signature that
>> avoids having to write the wrapper though regardless there is no need
>> to have a different environment for testing versus production if
>> approached in this manner. You just need to decide on the most
>> desirable way to make it work.
>>
>> David J.
>>
>
> I know that we can always write some wrappers etc, etc.
> This approach would failed if your do_some_date_based_stuff have no date
> args and contains calls say to now()(or other time function what
> possible can have fix value ) inside it.
>
> Also wrappers lead to multiple code base,yours client side code needs
> to know what function we should use - test or production. Also with
> your approach application server needs to know its working mode test / prod
>
> 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).

The basic problem I see is that time does not stand still and a test
setup that assumes it does is not testing the real world your
application lives in. I see no real application for your proposal, I
know you disagree, I just cannot see it being useful to the majority of
users.

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

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

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!
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 ;)

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

On 12.04.2016 21:05, Tom Lane wrote:
> I wrote:
>> 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.
> BTW, one possible way of meeting that particular requirement is to fool
> with your timezone setting.
>
> regression=# select timeofday();
> timeofday
> -------------------------------------
> Tue Apr 12 14:01:53.254286 2016 EDT
> (1 row)
>
> regression=# set time zone interval '+120 hours';
> SET
> regression=# select timeofday();
> timeofday
> --------------------------------------
> Sun Apr 17 18:01:58.293623 2016 +120
> (1 row)
>
> regards, tom lane
>
>
Oh!
This is better than nothing =)!

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

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

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

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

On Wed, Apr 13, 2016 at 10:14 AM, Alex Ignatov <a(dot)ignatov(at)postgrespro(dot)ru>
wrote:

> 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?
>

​"localtimestamp" isn't really a function but a keyword​

​that somewhat behaves as one.

http://www.postgresql.org/docs/current/static/functions-datetime.html

​Specifically those defined in 9.9.4​

The fact that they can be used without adding the parenthesis is a big
give-away. All true functions must use them. Note that these time
function do allow parentheses but they have a different meaning - to
specify precision as opposed to passing arguments - though the do look
similar.

​David J.​
​