overriding current_timestamp

For (unit) testing, I have often had the need to override the current
timestamp in the database system. For example, a column default,
function, or views would make use of the current timestamp in some way,
and to test the behavior, it's sometimes useful to tweak the current
timestamp.

What might be a good way to do that?

Just overwrite xactStartTimestamp? Is that safe? If it weren't static,
a user-loaded function could do it.

Overwrite pg_catalog.now() in the test database?

Other ideas?

Some semi-official support for this sort of thing would be good.

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> For (unit) testing, I have often had the need to override the current
> timestamp in the database system. For example, a column default,
> function, or views would make use of the current timestamp in some way,
> and to test the behavior, it's sometimes useful to tweak the current
> timestamp.

> What might be a good way to do that?

> Just overwrite xactStartTimestamp? Is that safe? If it weren't static,
> a user-loaded function could do it.

I think it's safe enough if you can figure out where/when to do it. Do
you need this to happen invisibly, or is it okay to require the test
script to call a set-the-timestamp function in each transaction?
If the former, it'd likely be necessary to hook into the transaction
start/end hooks.

> Overwrite pg_catalog.now() in the test database?

Yeah, that would work too if you'd rather do it at that end.

> Some semi-official support for this sort of thing would be good.

Mumble. It's not hard to think of applications where monkeying with the
system clock would amount to a security breach. So I'm not that excited
about providing a way to do it even semi-officially.

regards, tom lane

On Feb 23, 2012, at 3:08 AM, Peter Eisentraut wrote:

> For (unit) testing, I have often had the need to override the current
> timestamp in the database system. For example, a column default,
> function, or views would make use of the current timestamp in some way,
> and to test the behavior, it's sometimes useful to tweak the current
> timestamp.
>
> What might be a good way to do that?
>
> Just overwrite xactStartTimestamp? Is that safe? If it weren't static,
> a user-loaded function could do it.
>
> Overwrite pg_catalog.now() in the test database?
>
> Other ideas?
>
> Some semi-official support for this sort of thing would be good.

I create a "mock" schema, add the function to it, and then put it in the search_path ahead of pg_catalog. See the example starting at slide 48 on http://www.slideshare.net/justatheory/pgtap-best-practices.

Best,

David

On Feb 23, 2012, at 10:54 AM, David E. Wheeler wrote:

> I create a "mock" schema, add the function to it, and then put it in the search_path ahead of pg_catalog. See the example starting at slide 48 on http://www.slideshare.net/justatheory/pgtap-best-practices.

Sorry, starting at slide 480.

David

On tor, 2012-02-23 at 10:54 -0800, David E. Wheeler wrote:
> I create a "mock" schema, add the function to it, and then put it in the search_path ahead of pg_catalog.

That doesn't work for current_timestamp and similar built-in functions,
because they are always mapped to the pg_catalog schema.

On tor, 2012-02-23 at 10:55 -0800, David E. Wheeler wrote:
> On Feb 23, 2012, at 10:54 AM, David E. Wheeler wrote:
>
> > I create a "mock" schema, add the function to it, and then put it in the search_path ahead of pg_catalog. See the example starting at slide 48 on http://www.slideshare.net/justatheory/pgtap-best-practices.
>
> Sorry, starting at slide 480.

That presentation only goes to slide 394.

On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:

>> I create a "mock" schema, add the function to it, and then put it in the search_path ahead of pg_catalog.
>
> That doesn't work for current_timestamp and similar built-in functions,
> because they are always mapped to the pg_catalog schema.

I use it for NOW() all the time.

David

On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:

>> Sorry, starting at slide 480.
>
> That presentation only goes to slide 394.

Crimony, sorry, this presentation:

http://www.slideshare.net/justatheory/test-drivern-database-development

Note that I put pg_catalog at the end of the search_path, so that my mocked function will be found before it gets found in pg_catalog. If you don't add it to the end, it's implicitly the first item in the search path.

Best,

David

On mån, 2012-02-27 at 08:48 -0800, David E. Wheeler wrote:
> On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:
>
> >> I create a "mock" schema, add the function to it, and then put it in the search_path ahead of pg_catalog.
> >
> > That doesn't work for current_timestamp and similar built-in functions,
> > because they are always mapped to the pg_catalog schema.
>
> I use it for NOW() all the time.

But it won't work for current_timestamp.

On Feb 27, 2012, at 11:39 AM, Peter Eisentraut wrote:

>> I use it for NOW() all the time.
>
> But it won't work for current_timestamp.

Why not? Not challenging your assertion here, just curious why it’s different.

David

On mån, 2012-02-27 at 11:40 -0800, David E. Wheeler wrote:
> On Feb 27, 2012, at 11:39 AM, Peter Eisentraut wrote:
>
> >> I use it for NOW() all the time.
> >
> > But it won't work for current_timestamp.
>
> Why not? Not challenging your assertion here, just curious why it’s different.

Because it's not actually a function, it's hardcoded in the grammar to
call pg_catalog.now().

On Feb 27, 2012, at 11:43 AM, Peter Eisentraut wrote:

>> Why not? Not challenging your assertion here, just curious why it’s different.
>
> Because it's not actually a function, it's hardcoded in the grammar to
> call pg_catalog.now().

Ah, I see. Pity.

David

so I used this method here:
https://gist.github.com/thehesiod/d0314d599c721216f075375c667e2d9a
<https://gist.github.com/thehesiod/d0314d599c721216f075375c667e2d9a> and
indeed does not work for current_timestamp and the like functions, and
there's another major problems, it doesn't seem to work for existing
triggers either, it seems like functions referenced by triggers are bound
when the function is created :(. I created an account to post this tid-bit
for others trying to achieve the same

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-hackers-f1928748.html