Awesome! Thank you for you contribution; I am not the owner of the project, but let me share my thoughts for Aurora DB supports:

Looks like it's a variant of PostgreSQL's CREATE INDEX CONCURRENTLY, and there's aready an configuration nob to add CONCURRENTLY to create indexes: create_index_concurrently: true (cf. https://github.com/sqldef/sqldef/blob/master/cmd-psqldef.md#create-index-concurrently).

So how about to add flavor: aurora to tweak generated DDLs to match the Aurora dialect? That is, when create_index_concurrently: true with flavor: aurora, psqldef generates CREATE INDEX ASYNC. Also would be nice add create_index_async: true as just an alias to create_index_concurrently.

Supporting ASYNC keywords in DDL souds great as well, as the users of Aurora would want to write it in desired DDLs.

Regadting the transaction separations, CREATE INDEX CONCURRENTLYs are already executed outside of transactions. However, transaction separations, or no transaction mode, is requrested in #656 as well, so I'll vote up for the feature.

@k0kubun what do you think?

So how about to add flavor: aurora to tweak generated DDLs to match the Aurora dialect? That is, when create_index_concurrently: true with flavor: aurora, psqldef generates CREATE INDEX ASYNC. Also would be nice add create_index_async: true as just an alias to create_index_concurrently.

The design would prevent Aurora users from using CREATE INDEX CONCURRENTLY if flavor: aurora gets used for other purposes and they rely on it. How about just adding a separate flag create_index_async to add ASYNC, not as an alias to create_index_concurrently?

Regadting the transaction separations, CREATE INDEX CONCURRENTLYs are already executed outside of transactions. However, transaction separations, or no transaction mode, is requrested in #656 as well, so I'll vote up for the feature.

Sounds good.

P.S.

I am not the owner of the project

You're literally an owner of this organization. Given the amount of contributions you've made to sqldef, I have no problem with you acting as an owner of this project.

Note that DSQL is a different product and has unique dialect and characteristics, unlike traditional Aurora instances which is based on MySQL and PostgreSQL. Therefore if we would add flavor option, that must be named ‘dsql’.

Generator option to add ‘ASYNC’ flag should work, but IIRC ASYNC flag is retained on exports - I need to double check later

@sorah

Ah, I misunderstood ... I see, Aurora DSQL is different from Aurora Pg nor Aurora MySQL, and only Aurora DSQL supports CREATE INDEX ASYNC. So even though sqldef may introduce flavor: aurora-* in the future, it's not now, right?

Okay, create_index_async (not the alias to create_index_cuncurrently) may be useful because of my expecience that CREATE INDEXes without CONCURRENTLY resulted in serious performance degradations, but it's a separate concern.

LGTM.

@k0kubun

You're literally an owner of this organization. Given the amount of contributions you've made to sqldef, I have no problem with you acting as an owner of this project.

🙆‍♀️ 🌟

@sorah

I'm thinking about CI -- Now that a slightly different dialect of PostgreSQL has been introduced without tests, we'd better setting up CI for Aurora DSQL. Are you interested in it?

It's really important because I am planning to migrate psqldef's parser from pgquery to the generic parser (e.g. preliminary survey: #868).

Okay, create_index_async (not the alias to create_index_cuncurrently) may be useful because of my expecience that CREATE INDEXes without CONCURRENTLY resulted in serious performance degradations, but it's a separate concern.

After I told you all here need to double check later earlier, this option was determined possible. so I was preparing the another version of this patch with this direction… but now the initial patch has been merged (thanks!) I am happy with either way. Maybe having generator option is much simpler in term of parser.

I'm thinking about CI -- Now that a slightly different dialect of PostgreSQL has been introduced without tests, we'd better setting up CI for Aurora DSQL. Are you interested in it?

It'd be helpful, but how do we? Wondering about who owes cost, and how to establish AWS access in pull requests.

It'd be helpful, but how do we? Wondering about who owes cost, and how to establish AWS access in pull requests.

I think there are two ways (in any ways, you must have an admin permisson of this repository):

1. requesting AWS to get credits for maintain this project
2. using only free tier -- IIUC Aurora DSQL has free tier

The latter may be good enough, but I can't imagine how much trouble it will be (setting up a dedicated AWS account, OIDC, Aurora DSQL, and so on ...).