3

I am working on a project which is made out of a couple of microservices. I am planning to use Transaction Outbox Pattern by calling a lambda function in Postgres after insert trigger.

I am thinking something like this

CREATE OR REPLACE FUNCTION tx_msg_func()    RETURNS trigger AS
$$
DECLARE newRecord JSON;
BEGIN
    newRecord :=  row_to_json(NEW.*);

    PERFORM * FROM aws_lambda.invoke(
        aws_commons.create_lambda_function_arn('my_lambda_function'),
        newRecord,
        'Event'
    );

    RETURN NEW;
END;

$$
LANGUAGE 'plpgsql';

CREATE TRIGGER tx_msg_insert AFTER INSERT ON tx_outbox_table
FOR EACH ROW EXECUTE PROCEDURE tx_msg_func();

Here, the lambda function will receive the new record as JSON and will send an SQS message. After sending the message successfully, it will delete the record from tx_outbox_table

I am wondering if there is any downside here that I am missing. Do you think this is a production-ready solution? Is there anything I should be aware of?

Optimus
  • 1,703
  • 4
  • 22
  • 41

1 Answers1

0

Well, what about transaction? It should be as short as possible. After insert is executed inside transaction, so... TCP call goes inside the transaction. What can be done with it? Here is an idea. Exceptions are processed outside current transaction. New transaction is lighter when nothing is written, so maybe that is the way to go?

Michał Zaborowski
  • 3,911
  • 2
  • 19
  • 39
  • So what you are saying is, if the TCP call fails, the whole transaction will roll back, am I right? If that's the case, then it is all good because committing to the db and calling the lambda MUST happen, if either one fails, both should fail. – Optimus Jul 08 '21 at 09:49
  • also if something goes wrong transaction will hang until timeout... – Michał Zaborowski Jul 09 '21 at 07:57