Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Generate history row on UPDATE only if OLD is DISTINCT from NEW #32

Open
tmcdos opened this issue Jul 24, 2017 · 2 comments
Open

Generate history row on UPDATE only if OLD is DISTINCT from NEW #32

tmcdos opened this issue Jul 24, 2017 · 2 comments

Comments

@tmcdos
Copy link

tmcdos commented Jul 24, 2017

First, thank you for this excellent extension!
I have noticed that the versioning trigger creates new history rows even if they are the same as the previous one. I mean, if I execute UPDATE myTable SET column = 2 WHERE id = 1 5 times - I will have 5 rows in the history differing only in the sys_period column.
I tried to wrap the versioning() function with an SQL trigger but I faced the errors function versioning(text, text, text) does not exist and function "versioning" was not called by trigger manager.
I am not familiar enough with PostgreSQL extensions API and can not modify the source code myself.
Maybe someone can help with this ?
UPDATE:
I created 2 triggers instead of one - first trigger handles INSERT and DELETE, second trigger handles UPDATE WHEN old.* IS DISTINCT FROM new.* but this does not solve the problem completely - for example if the history table has fewer columns than the original table and you update a column which is not present in the history table then you get extra rows in the history.

@mlt
Copy link
Contributor

mlt commented Aug 23, 2017

I think this is by design. If you don't want new row and thus a history entry, use where restriction to check.

@tmcdos
Copy link
Author

tmcdos commented Aug 24, 2017

I see. It seems cheaper to have extra history entries than doing checks on each update.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

2 participants