Eclipse Community Forums: EclipseLink » Issue with Primary Key with Postgresql and Audit Trigger 91+

Home » Eclipse Projects » EclipseLink » Issue with Primary Key with Postgresql and Audit Trigger 91+(Incorrect PKEY returned on persist when using Audit Trigger 91+)

Show: Today's Messages :: Show Polls :: Message Navigator

Issue with Primary Key with Postgresql and Audit Trigger 91+ [message #1830216]

Mon, 20 July 2020 22:43

Adam Chalkley

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

Chris Delahunt

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

Adam Chalkley

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

Report message to a moderator

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Jun 25 07:59:47 GMT 2021

.:: Contact :: Home ::.

Breadcrumbs

Sign up to our Newsletter