Is Two Phase Commit suitable to ensure atomicity when sending an email?

Question

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) ?

Answer 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?