Issue with Primary Key with Postgresql and Audit Trigger 91+ [message #1830216] |
Mon, 20 July 2020 18:43  |
Eclipse User |
|
|
|
Hi,
I'm setting up a simple web app using JPA with EclipseLink (2.7) and Postgresql 9.6.
I installed the Audit Trigger 91+ for postgresql to create an audit log, and enabled on the table.
When I call a simple store command:
entityManager.persist(entity);
entityManager.flush();
System.out.println(entity.getPrimaryKey());
The primary key that is returned to the entity is the audit event_id and not the primary key of the record.
If I query for the entity again the returned item has the correct pkey
If I disable the auditing, then the primary key of the entity is returned as normal.
Is this a known issue, I haven't been able to find any similar threads.
Any ideas why the entity manager would be picking up the wrong ID, and how to resolve?
i thought it was odd when it needed me to add a GRANT on the audit table's sequence before I could even call persist!
[Edit as might be pertinent, keys are bigserial, mapped with GenerationType.IDENTITY.
I'm also using a mapped superclass with @PreUpdate and @PrePersist to generate create/modified timestamps and it also has an @Version for optimistic locking]
(Dev env. Netbeans 8.2, Oracle Glassfish 5.0, jdk 8u151 - not had a chance to try with other libs.)
[Updated on: Wed, 22 July 2020 01:30] by Moderator
|
|
|
|
Re: Issue with Primary Key with Postgresql and Audit Trigger 91+ [message #1830423 is a reply to message #1830379] |
Sat, 25 July 2020 15:54  |
Eclipse User |
|
|
|
HI Chris,
Thanks for the response.
I switched to using HistoryPolicy with a SessionCustomizer, which ran into a similar issue which was tracked to GenerationType.IDENTITY and (BIG)SERIAL. So thought this was likely the cause.
There was a workaround for the HistoryPolicy (https://bugs.eclipse.org/bugs/show_bug.cgi?id=347539) so I've used this.
I've not tried switching to .SEQUENCE, but I am sure this would work.
Somehow auditing at the DB level seems more robust, but the Tables from the HistoryPolicy are a lot easier to work with to display the history to the user via JPA.
I might give PGAudit, which audits to file rather than db, a try if I'm feeling very paranoid ...
Thanks,
Adam
|
|
|
Powered by
FUDForum. Page generated in 0.03580 seconds