Skip to content

Add expected errors for postgres 18 #1298

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

Merged
mrigger merged 7 commits into from
Mar 24, 2026
Merged

Add expected errors for postgres 18 #1298

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
7 commits
Select commit Hold shift + click to select a range
d751829
Add expected errors for postgres 18
albertZhangTJ Mar 3, 2026
d7b272b
Update postgres version in CI to 18
albertZhangTJ Mar 3, 2026
a4bf0a9
Disable SET_UNLOGGED_LOGGED for partitioned table for PG18; Fix NPE f…
albertZhangTJ Mar 22, 2026
2b8796c
Add cannot drop inherited constraint expected error
albertZhangTJ Mar 22, 2026
21ec5c7
Format the previous commits; Update PostgresBinaryComparisonOperator …
albertZhangTJ Mar 23, 2026
9a3d65e
Fix checkStyle errors
albertZhangTJ Mar 23, 2026
d4f459f
Fix checkStyle errors
albertZhangTJ Mar 23, 2026
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
2 changes: 1 addition & 1 deletion .github/workflows/main.yml
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -504,7 +504,7 @@ jobs:
- name: Set up PostgreSQL
uses: harmon758/postgresql-action@v1.0.0
with:
postgresql version: '13'
postgresql version: '18'
postgresql user: 'sqlancer'
postgresql password: 'sqlancer'
postgresql db: 'test'
Expand Down
22 changes: 20 additions & 2 deletions src/sqlancer/postgres/PostgresSchema.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -164,13 +164,26 @@ public enum TableType {
private final TableType tableType;
private final List<PostgresStatisticsObject> statistics;
private final boolean isInsertable;
private final boolean isPartitioned;

public PostgresTable(String tableName, List<PostgresColumn> columns, List<PostgresIndex> indexes,
TableType tableType, List<PostgresStatisticsObject> statistics, boolean isView, boolean isInsertable) {
super(tableName, columns, indexes, isView);
this.statistics = statistics;
this.isInsertable = isInsertable;
this.tableType = tableType;
// TODO: simple adapter for other implementations
this.isPartitioned = false;
}

public PostgresTable(String tableName, List<PostgresColumn> columns, List<PostgresIndex> indexes,
TableType tableType, List<PostgresStatisticsObject> statistics, boolean isView, boolean isInsertable,
boolean isPartitioned) {
super(tableName, columns, indexes, isView);
this.statistics = statistics;
this.isInsertable = isInsertable;
this.tableType = tableType;
this.isPartitioned = isPartitioned;
}

public List<PostgresStatisticsObject> getStatistics() {
Expand All @@ -185,6 +198,10 @@ public boolean isInsertable() {
return isInsertable;
}

public boolean isPartitioned() {
return isPartitioned;
}

}

public static final class PostgresStatisticsObject {
Expand Down Expand Up @@ -225,11 +242,12 @@ public static PostgresSchema fromConnection(SQLConnection con, String databaseNa
List<PostgresTable> databaseTables = new ArrayList<>();
try (Statement s = con.createStatement()) {
try (ResultSet rs = s.executeQuery(
"SELECT table_name, table_schema, table_type, is_insertable_into FROM information_schema.tables WHERE table_schema='public' OR table_schema LIKE 'pg_temp_%' ORDER BY table_name;")) {
"SELECT t.table_name, t.table_schema, t.table_type, t.is_insertable_into, c.relkind FROM information_schema.tables t JOIN pg_class c ON c.relname = t.table_name JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = t.table_schema WHERE t.table_schema='public' OR t.table_schema LIKE 'pg_temp_%' ORDER BY t.table_name;")) {
while (rs.next()) {
String tableName = rs.getString("table_name");
String tableTypeSchema = rs.getString("table_schema");
boolean isInsertable = rs.getBoolean("is_insertable_into");
boolean isPartitioned = "p".equals(rs.getString("relkind"));
// TODO: also check insertable
// TODO: insert into view?
boolean isView = tableName.startsWith("v"); // tableTypeStr.contains("VIEW") ||
Expand All @@ -240,7 +258,7 @@ public static PostgresSchema fromConnection(SQLConnection con, String databaseNa
List<PostgresIndex> indexes = getIndexes(con, tableName);
List<PostgresStatisticsObject> statistics = getStatistics(con);
PostgresTable t = new PostgresTable(tableName, databaseColumns, indexes, tableType, statistics,
isView, isInsertable);
isView, isInsertable, isPartitioned);
for (PostgresColumn c : databaseColumns) {
c.setTable(t);
}
Expand Down
3 changes: 2 additions & 1 deletion src/sqlancer/postgres/ast/PostgresBinaryComparisonOperation.java
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,5 +1,6 @@
package sqlancer.postgres.ast;

import sqlancer.IgnoreMeException;
import sqlancer.Randomly;
import sqlancer.common.ast.BinaryOperatorNode;
import sqlancer.common.ast.BinaryOperatorNode.Operator;
Expand Down Expand Up @@ -126,7 +127,7 @@ public PostgresConstant getExpectedValue() {
PostgresConstant leftExpectedValue = getLeft().getExpectedValue();
PostgresConstant rightExpectedValue = getRight().getExpectedValue();
if (leftExpectedValue == null || rightExpectedValue == null) {
return null;
throw new IgnoreMeException();
}
return getOp().getExpectedValue(leftExpectedValue, rightExpectedValue);
}
Expand Down
5 changes: 5 additions & 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 @@ -127,6 +127,9 @@ public List<Action> getActions(ExpectedErrors errors) {
if (!randomTable.hasIndexes()) {
action.remove(Action.ADD_TABLE_CONSTRAINT_USING_INDEX);
}
if (randomTable.isPartitioned()) {
action.remove(Action.SET_LOGGED_UNLOGGED);
}
if (action.isEmpty()) {
throw new IgnoreMeException();
}
Expand Down Expand Up @@ -235,6 +238,8 @@ public SQLQueryAdapter generate() {
errors.add("is in a primary key");
errors.add("is an identity column");
errors.add("is in index used as replica identity");
// PG18 update: otherwise we need to encode contraint inheritance info in PostgreColumn
errors.add("cannot drop inherited constraint");
}
break;
case ALTER_COLUMN_SET_STATISTICS:
Expand Down
3 changes: 3 additions & 0 deletions src/sqlancer/postgres/gen/PostgresCommon.java
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -48,6 +48,7 @@ public static List<String> getCommonTableErrors() {

errors.add("is not commutative"); // exclude
errors.add("operator requires run-time type coercion"); // exclude
errors.add("partitioned tables cannot be unlogged");

return errors;
}
Expand All @@ -59,6 +60,8 @@ public static void addCommonTableErrors(ExpectedErrors errors) {
public static List<String> getCommonExpressionErrors() {
ArrayList<String> errors = new ArrayList<>();

errors.add("for encoding \"SQL_ASCII\" does not exist");
Copy link
Copy Markdown
Contributor

@mrigger mrigger Mar 3, 2026

Choose a reason for hiding this comment

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

Hmm, why does this happen? Is this something that we should avoid generating instead?

Copy link
Copy Markdown
Collaborator Author

@albertZhangTJ albertZhangTJ Mar 3, 2026

Choose a reason for hiding this comment

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

I err towards adding this as expected error.

The root cause is if the database was created without specifying UTF8 encoding, then specifying COLLATE later will be invalid. e.g.

CREATE DATABASE database7  TEMPLATE template0;
CREATE UNLOGGED TABLE t3(c0 TEXT COLLATE "pg_c_utf8"  UNIQUE) INHERITS(t2, t1);

The other fix option would be enforcing UTF8 encoding in PostgresProvider instead of making it randomized, but I feel like it will lead to less diverse behaviors.

errors.add("invalid byte sequence for encoding");
errors.add("You might need to add explicit type casts");
errors.add("invalid regular expression");
errors.add("could not determine which collation to use");
Expand Down
Loading