Is it possible to somehow select a certain number of records from a table, and in the absence of the required number, not to choose anything?

This request is made in a transaction, and it would not be desirable for unnecessary records to be blocked. Under the unnecessary, meaning records, in insufficient quantities.

The procedure looks like this:

1) We start the transaction.

2) We check if there are necessary records in a certain amount. If so, update them and complete the transaction.

3) If not, look for other records for other parameters, and also in a certain amount.

4) Repeat steps 2 and 3 until the necessary entries are found (the number of interactions is limited).

5) We complete the transaction.

In queries, it would look something like this:

BEGIN TRANSACTION SELECT * FROM table WHERE param1 = value1 LIMIT 10 FOR UPDATE

If the query did not give results, make another query until we get the desired result.

 SELECT * FROM table WHERE param2 = value2 LIMIT 20 FOR UPDATE

    2 answers 2

    Try this:

     with Q as( select * from table where param1 = value1 limit 10 ) select * from Q where (select count(1) from Q)=10 for update

      will not work?

       DECLARE .... BEGIN LOOP IF EXISTS ( SELECT count(*),0 x FROM table WHERE param1 = value1 group by x having count(*)> 10 ) THEN делаем что надо END IF; если условие true выход END LOOP; END
      • It is necessary to exclude the situation when FOR UPDATE has already been done, but there are not enough records. In this case, it is not desirable to allow blocking by transaction. - Dima Gvozdev