1

Do PostgreSQL have variables at transaction level? If not, is it possible to implement something like that relatively easily? Unfortunately, I am still relatively inexperienced with PostgreSQL and have only found out that you can define settings at session level.

I am currently trying to implement a historization like Hibernate has directly in PostgreSQL. A short info for which they do not know Hibernate: Hibernate has a table for revisions. It contains an ID, the time and other information. For each table there is a "_AUD" table. Every change within the same transaction gets the same revision ID.

To implement this directly in PostgreSQL, I would need a variable that I can use within a transaction and whose value can only be used within the same transaction. My idea is that every time a transaction is historized, a function queries if an ID exists for the transaction, if not, it creates one, otherwise it returns the already created one.

I use the latest PostgreSQL version.

Improve this question
5
  • 2
    "Every change within the same transaction gets the same revision ID" - what about using Postgres' internal transaction ID for that? postgresql.org/docs/current/…
    – a_horse_with_no_name
    Jan 31, 2020 at 21:49
  • @a_horse_with_no_name Omg... you're right. I was so fixated on settings/variables, I never even thought about it. Thank you! Do you want to post it as an answer so I can close the question?
    – Sven M.
    Jan 31, 2020 at 21:57
  • 2
    be aware that transaction IDs do get recycled eventually, so if you want it to be permanently unique include a timestamp like now() (which also has a fixed value for the duration of the transaction)
    – Jasen
    Feb 1, 2020 at 10:28
  • 2
    @Jasen "The internal transaction ID type (xid) is 32 bits wide and wraps around every 4 billion transactions. However, these functions export a 64-bit format that is extended with an “epoch” counter so it will not wrap around during the life of an installation. " - Doesn't that guarantee uniqueness within an installation? I already have a timestamp in it, but putting it in the primary key wouldn't make this thing any faster.
    – Sven M.
    Feb 1, 2020 at 10:38
  • 1
    I didn't know that, thanks.
    – Jasen
    Feb 2, 2020 at 4:23

1 Answer 1

Reset to default
0

From today's point of view it was rather a stupid idea or bad design and I would pass this parameter directly to the database.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.