overriding current_timestamp

[Peter Eisentraut]

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.

[Tom Lane]

Post by Peter Eisentraut
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.

Post by Peter Eisentraut
Overwrite pg_catalog.now() in the test database?

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

Post by Peter Eisentraut
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

[David E. Wheeler]

Post by Peter Eisentraut
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

[David E. Wheeler]

Post by David E. Wheeler
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

[Peter Eisentraut]

Post by David E. Wheeler

Post by David E. Wheeler
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.

[David E. Wheeler]

Post by Peter Eisentraut

Post by David E. Wheeler
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

[Peter Eisentraut]

Post by David E. Wheeler
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.

[David E. Wheeler]

Post by Peter Eisentraut

Post by David E. Wheeler
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

[Peter Eisentraut]

Post by David E. Wheeler

Post by Peter Eisentraut

Post by David E. Wheeler
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.

[David E. Wheeler]

Post by Peter Eisentraut

Post by David E. Wheeler
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

[Peter Eisentraut]

Post by David E. Wheeler

Post by Peter Eisentraut

Post by David E. Wheeler
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().

[David E. Wheeler]

Post by Peter Eisentraut

Post by David E. Wheeler
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