Ostatnio miałem ciekawy case’ik – musiałem w obrębie jednej transakcji zmienić dane rekordów. Wszystko odbywało się w kilku funkcjach plpgsql, funkcje mogły się wywoływać rekurencyjnie w triggerach a zmiany dotyczyły między innymi pól typu TIMESTAMP. Wartości pól TIMESTAMP rekordów były zmieniane i ich zmiany automatycznie wpływały na w działanie skryptu.
Jakież było moje zdziwienie gdy skrypt nie chciał działać, a wszystkie rekordy miały taką zamą wartość pola typu TIMESTAMP. Otóż okazało się, że po rozpoczęciu transakcji funkcje zwracające aktualny czas:
SELECT CURRENT_TIME; SELECT CURRENT_TIMESTAMP; SELECT NOW();
przestały zwracać aktualny czas a zamiast tego czas rozpoczęcia transakcji.
I o dziwo jest to rzeczywiście poprawne działanie. Na stronie http://www.postgresql.org/docs/8.3/static/functions-datetime.html można przeczytać:
Since these functions return the start time of the current transaction, their values do not change during the transaction. This is considered a feature: the intent is to allow a single transaction to have a consistent notion of the „current” time, so that multiple modifications within the same transaction bear the same time stamp.
Zatem wyjściem z tego problemu jest użycie postgres’owej funkcji timeofday() i zrzutowanie jej wyniku na TIMESTAMP, realizyje to zapytanie:
SELECT cast( timeofday() AS timestamp);
Edit: zamiast timeofday() można uzyć funkcji clock_timestamp(), która zwraca date w postaci TIMESTAMP’a
SELECT clock_timestamp();
Dla ciekawości sprawdziłem jak zachowują się inne dialekty SQL i okazało się, że zarówno MySQL jak i MSSQL realizują tą sytuację odmniennie niż PostgreSQL, zatem:
- MySQL – w transakcji zwracany jest bieżący czas (nie czas rozpoczęcia transakcji)
- MSSQL – w transakcji zwracany jest bieżący czas (nie czas rozpoczęcia transakcji)
- PgSQL – w transakcji zwracany jest czas rozpoczęcia transakcji