Skip to main content



      Home
Home » Eclipse Projects » EclipseLink » Issue with Primary Key with Postgresql and Audit Trigger 91+(Incorrect PKEY returned on persist when using Audit Trigger 91+)
Issue with Primary Key with Postgresql and Audit Trigger 91+ [message #1830216] Mon, 20 July 2020 18:43 Go to next message
Eclipse UserFriend
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 #1830379 is a reply to message #1830216] Fri, 24 July 2020 08:45 Go to previous messageGo to next message
Eclipse UserFriend
You might want to switch to something that allows preallocation, as it seems the DB is returning the last assigned value, which because of auditing, isn't the one associated to the insert statement JPA is issuing.

[Updated on: Fri, 24 July 2020 08:46] 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 Go to previous message
Eclipse UserFriend
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
Previous Topic:How can I share a column between two FKs to the same reference table?
Next Topic:Unable to retrieve TIMESTAMPTZ from Oracle
Goto Forum:
  


Current Time: Sun Jul 13 11:08:58 EDT 2025

Powered by FUDForum. Page generated in 0.03580 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software

Back to the top