sql: test/evaluate compatibility with Flowable

[awoods187]

Test flowable with CockroachDB
https://www.flowable.org/

[rolandcrosby]

Right off the bat, #32098 blocks several of Flowable's CREATE TABLE statements. I ran Flowable against Postgres for a while and captured a sample of queries that it uses; will continue to work with the SQL team to determine how to identify other compatibility gaps.

[awoods187]

Any update on this after Friday's testing @rolandcrosby ?

[drewdeally]

@awoods187 updates are https://github.com/cockroachlabs/support/issues/223

[awoods187]

Remaining items:

• Request Postgres compatibility to support Flowable Engine: Set commands #35109
• Request Postgres compatibility to support Flowable Engine: pg_catalog.set_config() #35108
• sql: support optional TIMESTAMP precision #32098

[knz]

#35109 we can probably work around easily. #35108 is a little more work but is probably doable for 19.1, or perhaps 19.1.x. I wouldn't recommend backports to 2.1.

[knz]

The linked support issue also mentions #32098.

This is a well-scoped, well-contained new feature that's approximately M-sized (compare to S-sized for the previous two)

[rolandcrosby]

I'm 90% sure that #32098 is the only query here that's specific to Flowable. The other two are caused by pg_dump and do not occur in the Flowable application.

(What happened here: we ran Flowable against a Postgres instance that was configured to write all queries to a log file, then ran pg_dump to save the state of the database for later import/analysis; Postgres still had query logging turned on when we ran pg_dump, so all the SQL queries issued by pg_dump also showed up in the log, along with all the queries that were actually issued by the Flowable application.)

[knz]

For #32098 it would be good to know whether the customer uses any other precision than 6 in their app.

[rolandcrosby]

The Postgres schemas in Flowable only use TIMESTAMP and TIMESTAMP(6). You can search that repo online here; I downloaded the code locally to look in more detail. There are a couple occurrences of TIMESTAMP(3) in the repo, but those are only in the MySQL-specific DDL code.

[awoods187]

Does that impact your idea @knz ?

[knz]

we won't be able to do anything about TIMESTAMP(3) in the near future. For the other cases, my PR #35128 might help. It's only going to work as long as they don't expect the precision to be preserved in information_schema/pg_catalog.

[rolandcrosby]

I believe your PR will solve the problem. Thanks!

[drewdeally]

Roland, we can work around these with the create script - if we don't go that route then Flowable needs to have logic to add indexes for RI constraints and they will also fail.
Unless we have a plan for FK constraint to automate the index build on the reference col and table when not present.

[vy-ton]

Flowable is supported with CockroachDB, cockroachdb/docs#6865 tracks updating documentation.