BUG: close hdf store on any error
State: closed

NOTE: I'm opening the PR mainly to open a discussion about the current behaviour. If this is intended, please tell me. If not and you need me to write some tests, I'll do it :)

In `read_hdf`, if the `store.select()` call throws either a
`ValueError`, a `TypeError` or a `KeyError` then the store is closed.

However, if any other exception is thrown (e.g. an `AssertionError` if
there are gaps in blocks ref_loc) , the store is not closed and some
client code could end up trying to reopen the store and hit an
error like: `the file XXX is already opened. Please close it before
reopening in write mode`. Furthermore, the exception is re-raised in all
cases.

This commit just catches any exception.

Closes #28430 

- [x] closes #xxxx
- [x] tests added / passed
- [x] passes `black pandas`
- [x] passes `git diff upstream/master -u -- "*.py" | flake8 --diff`
- [x] whatsnew entry


Labels:
name: Error Reporting
description: Incorrect or improved errors from pandas
name: IO HDF5
description: read_hdf, HDFStore

Comments:
> Can you add a test and a whatsnew for v1.0.0 when you get the chance?

Sure, I will do that in the next days
> > Can you add a test and a whatsnew for v1.0.0 when you get the chance?
> 
> Sure, I will do that in the next days

I've just re-requested your review for a first version of the PR.

CI is failing… It looks like some exceptions are exceptions are expected _not_ to close the HDF store... Should we explicitly whitelist these exceptions or should we do the opposite, i.e. list all exceptions we should close the store on? In my specific case, I expected the store to be closed on `AssertionError`s.
Which tests are failing because of that? Something like `test_read_hdf_open_store`?

I also would have expected an exception to close an auto-opened store.
> Which tests are failing because of that? Something like `test_read_hdf_open_store`?

The failing tests are:

- `test_select_iterator`
- `test_read_hdf_iterator`
- `test_read_hdf_open_store`

> I also would have expected an exception to close an auto-opened store.

I didn't have the time to check for now, probably something going on with the iteration protocol. I will try to do that soon.

> In read_hdf, if the store.select() call throws either a
ValueError, a TypeError or a KeyError then the store is closed.

> However, if any other exception is thrown (e.g. an AssertionError if
there are gaps in blocks ref_loc) , the store is not closed and some
client code could end up trying to reopen the store and hit an
error like: the file XXX is already opened. Please close it before reopening in write mode. Furthermore, the exception is re-raised in all
cases.

we would want to close the store on all exceptions, likely this is an oversite.
> we would want to close the store on all exceptions, likely this is an oversite.

@jreback ok, thanks! Should I go and fix the other tests accordingly then?
I think the tests can be updated where needed.

One question: if a user does something like

```python
store = pd.HDFStore(...)
store.<something that causes an exception>
```

then will the `store` be closed? Or do we only close stores that we open? Ideally we would only ensure we close stores that we open.
this PR is slightly orthogonal to #28700 so if you want to rebase after we merge that would be great.
@adimascio can you merge master on this?
> @adimascio can you merge master on this?

@WillAyd @jreback I've just rebased on master. I will try to fix the tests as discussed earlier this week-end!

@adimascio can you fix the merge conflict on the whatsnew and see if you can get this green?
@adimascio can you fix the merge conflict on the whatsnew and see if you can get this green?
@adimascio can you fix the merge conflict and see if you can get the CI passing
> @adimascio can you fix the merge conflict and see if you can get the CI passing

I've just push-forced to fix the conflicts. I'll try to fix the CI soon.
@adimascio pls merge master and see if you can get passing
Closing as CI is red and I think this has gone stale, but ping if you’d like to pick this back up and can reopen