Issue: Does peewee support PostgreSQL `CREATE INDEX CONCURRENTLY`?
Author: ilyakamens
State: closed
Created: 2022-11-21 18:30:58

I didn't see any mention of [this](https://www.postgresql.org/docs/current/sql-createindex.html#:~:text=Normally%20PostgreSQL%20locks%20the%20table,the%20index%20build%20is%20finished.) in code, docs, or past issues (apologies if I didn't search thoroughly enough). My guess is it doesn't, but figured I'd ask just in case. If that's so, would you be open to a PR?

We don't have that right now, but the use-case is probably a one-off migration (if you need to do it concurrently, without locking things). In those situations its probably better to just write and run the SQL directly, e.g.

```python
db.execute_sql('create index concurrently ...')
```

Yes, that's what I'm currently doing. Would you accept a PR to add `concurrently=True` to `add_index()`?

Hi, @coleifer. Following up with something related:

As you probably know, concurrent indexes [can't be run inside of a transaction](https://www.postgresql.org/docs/current/sql-createindex.html#SQL-CREATEINDEX-CONCURRENTLY):

>Another difference is that a regular CREATE INDEX command can be performed within a transaction block, but CREATE INDEX CONCURRENTLY cannot.

When I try to create the index:

```python
db.database.execute_sql("CREATE INDEX CONCURRENTLY foo_bar ON foo (bar);")
```

I receive the following error as expected:

```sh
Traceback (most recent call last):
  File "/usr/local/lib/python3.7/site-packages/peewee.py", line 3144, in execute_sql
    cursor.execute(sql, params or ())
psycopg2.errors.ActiveSqlTransaction: CREATE INDEX CONCURRENTLY cannot run inside a transaction block


During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "./manage.py", line 611, in <module>
    manager.run()
  ...
  File "/usr/local/lib/python3.7/site-packages/peewee.py", line 3144, in execute_sql
    cursor.execute(sql, params or ())
peewee.InternalError: CREATE INDEX CONCURRENTLY cannot run inside a transaction block
```

I receive the same error with the following:

```python
with db.database.manual_commit():
    db.database.execute_sql("CREATE INDEX CONCURRENTLY foo_bar ON foo (bar);")
```

Only the following works (found [here](https://www.psycopg.org/docs/connection.html#connection.autocommit)):

```python
db.database._state.conn.set_session(autocommit=True)
db.database.execute_sql("CREATE INDEX CONCURRENTLY foo_bar ON foo (bar);")
```

Is there some other peewee API I'm missing so I don't have to reach so far down the stack? Version details:

>peewee==3.14.4
>psycopg2==2.8.2

The `db` object above is created via:

```python
from playhouse.flask_utils import FlaskDB

db = FlaskDB(app)
```

Thank you.

Oh interesting, no you're not doing anything wrong! This looks to me more like a DB-API 2.0 / psycopg2 side-effect. I've never seen this before, actually - and like you, would have assumed that the changes would be run in autocommit mode by default. Unfortunately, the DB-API 2.0 specifies some sort of strange behavior, so explicitly setting the underlying connection to `autocommit` for the purposes of this script isn't too bad.

Here's a slightly better version you can use:

```python
pwdb = db.database
pwdb.connection().set_session(autocommit=True)
pwdb.execute_sql("CREATE INDEX CONCURRENTLY foo_bar ON foo (bar);")
```

Awesome. Thank you, and happy 🦃.