Skip to content

Adding Generic Plan for EXPLAIN in Postgres V16 Upgrade #1265

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
yaojiejia wants to merge 5 commits into
base: main
Choose a base branch
from
Open

Adding Generic Plan for EXPLAIN in Postgres V16 Upgrade #1265

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
5 commits
Select commit Hold shift + click to select a range
efe4bc5
Add support for Explain Analyze for Postgres v13
yaojiejia May 24, 2025
8860d92
Add seperate method for EXPLAIN
yaojiejia Jun 24, 2025
5903b1c
Add explain generator in postgresprovider
yaojiejia Jul 13, 2025
81f14c9
Reformat the code
yaojiejia Jul 13, 2025
e3d9cb0
Adding a generic plain for explain
yaojiejia Aug 11, 2025
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 4 additions & 0 deletions src/sqlancer/postgres/PostgresProvider.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -125,6 +125,7 @@ public enum Action implements AbstractAction<PostgresGlobalState> {
LISTEN((g) -> PostgresNotifyGenerator.createListen()), //
UNLISTEN((g) -> PostgresNotifyGenerator.createUnlisten()), //
CREATE_SEQUENCE(PostgresSequenceGenerator::createSequence), //
EXPLAIN(PostgresExplainGenerator::create), //
CREATE_VIEW(PostgresViewGenerator::create);

private final SQLQueryProvider<PostgresGlobalState> sqlQueryProvider;
Expand Down Expand Up @@ -192,6 +193,9 @@ protected static int mapActions(PostgresGlobalState globalState, Action a) {
case INSERT:
nrPerformed = r.getInteger(0, globalState.getOptions().getMaxNumberInserts());
break;
case EXPLAIN:
nrPerformed = r.getInteger(0, 1);
break;
default:
throw new AssertionError(a);
}
Expand Down
1 change: 1 addition & 0 deletions src/sqlancer/postgres/gen/PostgresAlterTableGenerator.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -298,6 +298,7 @@ public SQLQueryAdapter generate() {
errors.add("contains null values");
errors.add("insufficient columns in PRIMARY KEY constraint definition");
errors.add("which is part of the partition key");
errors.add("ALTER TABLE / ADD CONSTRAINT USING INDEX is not supported on partitioned tables");
break;
case VALIDATE_CONSTRAINT:
sb.append("VALIDATE CONSTRAINT asdf");
Expand Down
59 changes: 59 additions & 0 deletions src/sqlancer/postgres/gen/PostgresExplainGenerator.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,15 @@
package sqlancer.postgres.gen;

import java.util.Arrays;

import sqlancer.Randomly;
import sqlancer.common.query.SQLQueryAdapter;
import sqlancer.postgres.PostgresGlobalState;
import sqlancer.postgres.PostgresSchema;
import sqlancer.postgres.PostgresSchema.PostgresDataType;
import sqlancer.postgres.PostgresSchema.PostgresTables;
import sqlancer.postgres.ast.PostgresSelect;

public final class PostgresExplainGenerator {

private PostgresExplainGenerator() {
Expand All @@ -13,4 +23,53 @@ public static String explain(String selectStr) throws Exception {
return sb.toString();
}

public static String explainGeneral(String selectStr) throws Exception {
StringBuilder sb = new StringBuilder();
sb.append("EXPLAIN ");

// Add various EXPLAIN options randomly
if (Randomly.getBoolean()) {
sb.append("(ANALYZE) ");
}
if (Randomly.getBoolean()) {
sb.append("(FORMAT ");
sb.append(Randomly.fromOptions("TEXT", "XML", "JSON", "YAML"));
sb.append(") ");
}
if (Randomly.getBoolean()) {
sb.append("(VERBOSE) ");
}
if (Randomly.getBoolean()) {
sb.append("(COSTS) ");
}
if (Randomly.getBoolean()) {
sb.append("(BUFFERS) ");
}
if (Randomly.getBoolean()) {
sb.append("(TIMING) ");
}
if (Randomly.getBoolean()) {
sb.append("(SUMMARY) ");
}
if (Randomly.getBoolean()) {
sb.append("(GENERIC_PLAN) ");
}

sb.append(selectStr);
return sb.toString();
}

public static SQLQueryAdapter create(PostgresGlobalState globalState) throws Exception {
PostgresSchema.PostgresTable table = globalState.getSchema().getRandomTable(t -> !t.isView());
PostgresExpressionGenerator gen = new PostgresExpressionGenerator(globalState);
gen.setTablesAndColumns(new PostgresTables(Arrays.asList(table)));
PostgresSelect select = gen.generateSelect();
select.setFromList(gen.getTableRefs());
select.setFetchColumns(gen.generateFetchColumns(false));
if (Randomly.getBoolean()) {
select.setWhereClause(gen.generateExpression(PostgresDataType.BOOLEAN));
}
return new SQLQueryAdapter(explainGeneral(select.asString()));
}

}
2 changes: 1 addition & 1 deletion src/sqlancer/postgres/gen/PostgresSetGenerator.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -112,7 +112,7 @@ private enum ConfigurationOption {
JIT("jit", (r) -> Randomly.fromOptions(1, 0)),
JOIN_COLLAPSE_LIMIT("join_collapse_limit", (r) -> r.getInteger(1, Integer.MAX_VALUE)),
PARALLEL_LEADER_PARTICIPATION("parallel_leader_participation", (r) -> Randomly.fromOptions(1, 0)),
FORCE_PARALLEL_MODE("force_parallel_mode", (r) -> Randomly.fromOptions("off", "on", "regress")),
// FORCE_PARALLEL_MODE("force_parallel_mode", (r) -> Randomly.fromOptions("off", "on", "regress")),
Copy link
Copy Markdown
Contributor

@mrigger mrigger Aug 11, 2025

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Looks good to me except this unrelated change. It seems there is also a conflict, so we can't yet merge.

PLAN_CACHE_MODE("plan_cache_mode",
(r) -> Randomly.fromOptions("auto", "force_generic_plan", "force_custom_plan"));

Expand Down