0

Let's say I need to run some queries AND send an email in an atomic way.

A typical example is for a user sign-up form, I need to create the user and send the welcome email.

I can use a transaction :

begin transaction
create user
if creation failed, rollback transaction, bailout
send email
if email sending failed, rollback transaction, bailout
commit transaction

but, in PostgreSQL, a commit can fail (for example when using DEFERRED constraints).

So the solution would be to use a two-phase commit :

begin transaction
create user
PREPARE TRANSACTION
if creation failed, rollback transaction, bailout
send email
if email sending failed, rollback transaction, bailout
COMMIT PREPARED // this one is guaranteed to work by Postgres

but the Postgres doc says : It is unwise to leave transactions in the prepared state for a long time.

Moreover :

The intended usage of the feature is that a prepared transaction will normally be committed or rolled back as soon as an external transaction manager has verified that other databases are also prepared to commit.

So,
Is sending an email too much time ?
What problems could arise if we do so ?
What would be an acceptable timeout if it takes too long ?
Are database locks different in prepared state than in idle state (ie. before the commit) ?

Emmanuel Meric de Bellefon
  • 2,319
  • 1
  • 11
  • 25

1 Answers1

1

Sending an email is not transactional. It is possible you will never know whether it was successfully delivered or not and clearly "forever" is too long to hold onto a prepared transaction.

You will want to structure the table so that partially created users can be committed, but still known to be unconfirmed. Like with a column indicating as much. That way troubleshooters can actually see the partially created user to decide what to do about them. With prepared transactions, the semi-committed rows are invisible, so no one can figure out what is going on.

To put it a somewhat different way, to use two phase commit effectively, you need to have a transaction manager. Do you? You didn't describe one. Are you planning to write your own? Do you know how much work that will be?

jjanes
  • 37,812
  • 5
  • 27
  • 34
  • Sending an email is just an example, or I could use a third-party service like SendGrid and consider once we receive an HTTP 200 from SendGrid we can commit the transaction. I don't plan to write a transaction manager. I'm just wondering if TPC is ok to use with something else than querying other databases. I updated the question. – Emmanuel Meric de Bellefon Jul 29 '22 at 09:11
  • TPC sounds like the wrong tool to me as well. having rows in a database are very useful to track down issues. e.g. maybe have an `emails_sent` table that has `user_id`, `mail_to` (in case it changes in the future), `smtp_message_id` (so you can track it through your mail system logs / reply to it later), `date_sent`), `response_code` (e.g. from sendgrid, maybe also with a timestamp). you'd have one transaction to create the `emails_sent` table before you send, and then another transaction to update the response_code when you get a response code back from your mail system – Sam Mason Jul 29 '22 at 10:55