Tracking: Introduce Sqlancer

[lmatz]

https://github.com/sqlancer/sqlancer has already supported Postgres.
We can run the test against batch queries first.

Running it for each PR seems unnecessary, maybe set it up as a daily job? Probably also allow people to manually trigger it.

Running it for stream queries? Require some extra effort by extending the existing Postgres module of Sqlancer.

Prerequisite:

• feat(pgwire): support row limit in Execute message(Extended query protocol) #3294
• Support configuration parameter extra_float_digits #3406 (Haven't fully support, but enough to run Sqlancer)
• Support configuration application_name #3421
• Support pg_opclass #3431
• Support pg_collation
• Support pg_operator
• Support pg_am
• Add RisingWave sqlancer implementation: Remove ENCODING, TEMPLATE parameters.

References:
See: https://github.com/sqlancer/sqlancer/blob/78506a83a4b77d2c9536980a4d3b4d47eb7e9317/src/sqlancer/postgres/PostgresGlobalState.java#L44-L94

[liurenjie1024]

+1 for running daily job against batch query.

For streaming query, does it support materialized view?

[lmatz]

For streaming query, does it support materialized view?

Yes(at least in code), https://github.com/sqlancer/sqlancer/blob/master/src/sqlancer/postgres/gen/PostgresViewGenerator.java

When I use 8 threads for testing PG with Sqlancer, the throughput is around 6000 queries per second.

Suspect that if we wait for the mv's flush synchronously by blocking, the throughput won't be this high.

[liurenjie1024]

cc @sumittal

[mrigger]

Let me know if you have any questions or need input from my side about integrating SQLancer!

[skyzh]

Suspect that if we wait for the mv's flush synchronously by blocking, the throughput won't be this high.

You may adjust checkpoint interval to a relatively small value (or just using the mock frontend / compute node as we've done in most unit tests). This might lead to better throughput.

Suspect that if we wait for the mv's flush synchronously by blocking, the throughput won't be this high.

Absolutely, we're even not done with parallel ddl! #2692 #3084

[kwannoel]

Few more system tables needed:

• pg_collation
• pg_operator
• pg_am

See: https://github.com/sqlancer/sqlancer/blob/78506a83a4b77d2c9536980a4d3b4d47eb7e9317/src/sqlancer/postgres/PostgresGlobalState.java#L44-L94

Updated the description.

[lmatz]

Few more system tables needed:

• pg_collation
• pg_operator
• pg_am

See: https://github.com/sqlancer/sqlancer/blob/78506a83a4b77d2c9536980a4d3b4d47eb7e9317/src/sqlancer/postgres/PostgresGlobalState.java#L44-L94

Updated the description.

Thanks for the info!

We can also hard-code these PG system tables just enough to support SQLancer.
We try to spend as little effort as possible on the compatibility of these PG system tables.

[mrigger]

I think none of these three system tables is essential for testing. As an alternative, you could also consider adding a flag to the SQLancer Postgres implementation that specifies whether these system tables are supported (see https://github.com/sqlancer/sqlancer/blob/master/src/sqlancer/postgres/PostgresOptions.java).

[kwannoel]

Have continued work on this, you may follow the progress here:

• Compatibility work on RW: https://github.com/risingwavelabs/risingwave/tree/kwannoel/sqlancer
• Compatibility work on Sqlancer: https://github.com/kwannoel/sqlancer/tree/rw

Plan:

1. (WIP in first two branches above) First try to get minimal working with NoREC since it seems simplest to support. This is just investigative, may hardcode some things in sqlancer:postgres, just to get it running first.
2. Once we know all limitations etc... we can include a variant e.g. https://github.com/sqlancer/sqlancer/blob/master/src/sqlancer/citus/CitusProvider.java since there are differences in SQL features.

Many thanks to @mrigger for providing his thoughts and suggestions on these.