2

I'm thinking about creating archive tables in our database.

I can create an after delete trigger that would move row to archive table, but I need to fill deleted_by field which has id of the user that removed the data. This user is an entity in our application and not a internal postgres user to be clear.

If postgres would have a way to attach some metadata to the transaction I could've used it inside of the trigger to fill this field. Maybe I can use variables for that? Is there existing solution to this problem?

user1685095
  • 5,787
  • 9
  • 51
  • 100
  • 1
    See [Is it possible to define global variables in postgresql](https://stackoverflow.com/q/31316053/1995738) and [Setting a configuration parameter for functions implemented in PL/pgSQL](https://stackoverflow.com/q/34476062/1995738) – klin Feb 21 '19 at 20:56
  • First solution is obvious and I don't like it (you would need to query this table for each row in a trigger, maybe database will optimize this maybe not). Configuration parameter can be interesting. – user1685095 Feb 22 '19 at 06:40

1 Answers1

0

I suggest you to write a stored procedure that that inserts the row to the archive table and deletes it from the table. Then the API shall use only that procedure to delete a row. The user id is passed as an argument.
You can still write a trigger that inserts the row to the archive table with a NULL user id if someone attempts to use DELETE instead of the procedure. In that case, the row in the archive must have the primary key from the original table in a UNIQUE NULL column to prevent duplicates.

Nathan Xabedi
  • 1,097
  • 1
  • 11
  • 18
  • Haven't thought about that solution but It doesn't suits us. We use ORM and the whole idea is that the deletion process shouldn't require changes in our usage of ORM nor in ORM itself. – user1685095 Feb 22 '19 at 06:38