"SELECT FOR UPDATE" is needed for PostgreSQL

[yangmarcyang]

Hi All,

In #2770, we encountered "extreme cases" where a JWT key exists in local journal.pem but does not exist in DB bundle table. Recently the issue happened again in our setup where a spire-server generated a new key, stored in DB bundle table without any error, saved into local journal.pem file, but unfortunately that key did not exist in DB bundle table.

After we set up testing environment and could reproduce the issue consistently, we found that the root cause is related with PostgreSQL hot standby mode. In our setup, hot standby mode is turned on, and according to the spec, "those using hot standby may want to use Repeatable Read and explicit locking on the master". So, "SELECT FOR UPDATE" is needed in this case.

We just simply copied this line out of the if block and ran it in the testing environment, the issue became non-reproducible.

So, to have consistency guarantees for PostgreSQL with hot standby mode, IMHO "SELECT FOR UPDATE" is still needed.

[amartinezfayo]

@azdagron This behavior was introduced in #2605 to fix #2587. As @yangmarcyang points, data consistency checks have special considerations in PostgreSQL when hot standby mode is turned on. May we able to detect when this mode is turned on and have SELECT .. FOR UPDATE there also?

[loveyana]

@azdagron This behavior was introduced in #2605 to fix #2587. As @yangmarcyang points, data consistency checks have special considerations in PostgreSQL when hot standby mode is turned on. May we able to detect when this mode is turned on and have SELECT .. FOR UPDATE there also?

@amartinezfayo I think we can tell if psql is in hot standby mode by show hot_standby is on and show wal_level is not minimal, but I'm not sure if this problem occurs in other psql configurations. If SELECT ... FOR UPDATE is currently fully enabled under mysql and it has less impact on performance, can we accept it as the default query option.

[azdagron]

I don't see a problem just enabling SELECT FOR UPDATE on all read-modify-write tx's for both MySQL and PostgreSQL.

[amartinezfayo]

Thanks @azdagron. I also think that there shouldn't be any problem enabling SELECT ... FOR UPDATE on all read-modify-write transactions for both MySQL and PostgreSQL.
I've added this to the 1.3.1 milestone.