Skip to main content


Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    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 22:43 Go to next message
Adam Chalkley is currently offline Adam ChalkleyFriend
Messages: 3
Registered: July 2020
Junior Member
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 05:30]

Report message to a moderator

Re: Issue with Primary Key with Postgresql and Audit Trigger 91+ [message #1830379 is a reply to message #1830216] Fri, 24 July 2020 12:45 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris DelahuntFriend
Messages: 1388
Registered: July 2009
Senior Member
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 12:46]

Report message to a moderator

Re: Issue with Primary Key with Postgresql and Audit Trigger 91+ [message #1830423 is a reply to message #1830379] Sat, 25 July 2020 19:54 Go to previous message
Adam Chalkley is currently offline Adam ChalkleyFriend
Messages: 3
Registered: July 2020
Junior Member
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: Fri Jun 25 07:59:47 GMT 2021

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

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

Back to the top