1

I am trying to generate a code that performs 2 postgresql commands simultaneously, in one transaction. I have in my DB 2 rules with different rule order, and I want to switch the rule order between them. I am using r2dbc-postgresql (v0.8.4) and spring-data-r2dbc (v1.1.1).

I have defined DatabaseClient and TransactionalOperator and tried to use that piece of code:

public Mono<Void> insertRows() {
    return databaseClient.execute("update rules set rule_order = 2 where rule_order = 1")
            .fetch().rowsUpdated()
            .then(databaseClient.execute("update rules set rule_order = 1 where rule_order = 2")
                .fetch().rowsUpdated())
            .then()
            .as(transactionalOperator::transactional);
}

but the result was that the commands ran one after another, so I finished with having both rules with rule order = 1.

What am I doing wrong, and how can I fix this?

eladya
  • 83
  • 1
  • 5

1 Answers1

0

Not sure what you're expecting here.

The first statement updates the rules rows to rule_order=2(update rules set rule_order = 2 where rule_order = 1). The second one updates rules to rule_order=1 (update rules set rule_order = 1 where rule_order = 2).

mp911de
  • 17,546
  • 2
  • 55
  • 95
  • Is there a way to run the commands simultaneously in order to perform this switch? – eladya Sep 08 '20 at 10:13
  • No. All databases (at least to my knowledge) require sequential command execution when operating in a single transaction. – mp911de Sep 08 '20 at 10:24
  • Can I perform this command using the code? UPDATE rules SET rule_order = CASE rule_order WHEN 1 THEN 2 WHEN 2 THEN 1 END WHERE rule_order IN (1, 2); – eladya Sep 08 '20 at 10:43