WithLockRetries can raise an error if all attempts fail

What does this MR do?

Relates to #230829 (closed)

The linked issue covers details to create a rake task that can be used to rebuild an existing index. Currently pg_repack is used for that in our production environment, but that solution that can't easily be rolled out to self-managed. As we have customers with large self-managed installations, it would be helpful to have this logic available for them to be able to reduce index bloat.

Rebuilding an index is a three step process:

1. Create a new index matching the existing index definition
2. Atomically swap the existing and new index names
3. Remove the original index

For step 2, the with_lock_retries helper will be used to safely acquire the locks. Currently with_lock_retries makes multiple attempts to acquire the locks, but if it's unable it disables the lock_timeout altogether for its final attempt. This makes sense for migrations, as it's required they eventually complete. Since rebuilding an index is a non-critical operation, it's not necessary to wait indefinitely for the lock. Instead, an error can be raised, and the index rebuilding operation can be rolled back and retried later.

The follow-up MR to this that implements the rake task: !39245 (merged)

Does this MR meet the acceptance criteria?

Conformity

• Changelog entry
• Documentation (if required)
• Code review guidelines
• Merge request performance guidelines
• Style guides
• Database guides
• Separation of EE specific content

Availability and Testing

• Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
• Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

• Label as security and @ mention @gitlab-com/gl-security/appsec
• The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
• Security reports checked/validated by a reviewer from the AppSec team