Обсуждение: 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.ignatov@postgrespro.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.ignatov@postgrespro.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$
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@postgresql.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.ignatov@postgrespro.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@postgresql.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.ignatov@postgrespro.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@postgresql.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.ignatov@postgrespro.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.ignatov@postgrespro.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.ignatov@postgrespro.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.klaver@aklaver.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.ignatov@postgrespro.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
>>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.
On 12.04.2016 18:01, Adrian Klaver wrote:
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:
>>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.
On 12.04.2016 18:01, Adrian Klaver wrote:
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
I know that we can always write some wrappers etc, etc.On 12.04.2016 19:45, David G. Johnston 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.
On 12.04.2016 18:01, Adrian Klaver wrote:
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.
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.ignatov@postgrespro.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.ignatov@postgrespro.ru>a.ignatov@postgrespro.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.klaver@aklaver.com
On 12.04.2016 20:50, Tom Lane wrote: > Alex Ignatov <a.ignatov@postgrespro.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.ignatov@postgrespro.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.klaver@aklaver.com
On 13.04.2016 17:26, Adrian Klaver wrote:
On 04/13/2016 04:16 AM, Alex Ignatov wrote:>> "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) "
On 12.04.2016 20:50, Tom Lane wrote:Alex Ignatov <a.ignatov@postgrespro.ru> writes:1. background jobs in pg?? cron you mean or may be EnterpriseDB vesion?You always should keep in mind that your application may run in testWe're not really in the business of being Oracle --- which in this
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).
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
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 ;)
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:
Some quick and dirty issue resolution is simple:On 13.04.2016 17:26, Adrian Klaver wrote:On 04/13/2016 04:16 AM, Alex Ignatov wrote:>> "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) "
On 12.04.2016 20:50, Tom Lane wrote:Alex Ignatov <a.ignatov@postgrespro.ru> writes:1. background jobs in pg?? cron you mean or may be EnterpriseDB vesion?You always should keep in mind that your application may run in testWe're not really in the business of being Oracle --- which in this
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).
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
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 ;)
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
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
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 =)Some quick and dirty issue resolution is simple:
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.
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.