Hi Team,
I have tried writing regression tests for a PostgreSQL extension and encountered a limitation with pg_regress regarding session timezones.
The issue is as follows:
pg_regress internally calls putenv("PGTZ=PST8PDT") and putenv("PGDATESTYLE=Postgres, MDY") for each test session. This ensures that the core PostgreSQL regression tests are deterministic. However, it also overrides any timezone or datestyle settings, including those configured via ALTER SYSTEM or exported environment variables set before running make installcheck.
As a result, even if I export a different timezone (e.g., Asia/Kolkata) in my shell or set it globally via ALTER SYSTEM, the regression sessions still use PST8PDT.
Currently, the only reliable way I have found to run tests in a specific timezone is to add SET timezone = '...' at the top of each SQL test file. While this works, it is tedious and not ideal, especially when working with many test files or when the extension’s behavior needs to be validated across different time zones.
I am reaching out to the community to ask:
Is there any recommended approach to run regression tests in a user-specified timezone without modifying each SQL test file?
Any guidance, best practices, or alternative approaches would be greatly appreciated.
Thank you for your time and suggestions.
Best regards,
Sri Keerthi R.
