GeezerVison

Purpose

PostgreSQL session scripts, connection-pool reset hooks and old dumps use statements that fail on master (12548b9da) with Dialect.POSTGRESQL and without a dialect, for example:

• DISCARD ALL, DISCARD PLANS, DISCARD SEQUENCES, DISCARD TEMP (the reset query of connection pools such as PgBouncer)
• LISTEN ch, UNLISTEN ch, UNLISTEN *
• COPY t TO STDOUT CSV HEADER, COPY t FROM STDIN WITH CSV HEADER DELIMITER ';' NULL '', COPY t TO STDOUT CSV FORCE QUOTE *, COPY BINARY t TO STDOUT: the COPY option syntax from before 9.0, which PostgreSQL 18 still accepts and older tools and scripts still write

SHOW TIME ZONE, SHOW TRANSACTION ISOLATION LEVEL and SHOW SESSION AUTHORIZATION parse, but only to UnsupportedStatement, and RESET TRANSACTION ISOLATION LEVEL and RESET SESSION AUTHORIZATION fail.

PostgreSQL 18 references: DISCARD, LISTEN, UNLISTEN, SHOW, RESET, COPY (section "Compatibility" for the older syntax), and gram.y (DiscardStmt, ListenStmt, UnlistenStmt, VariableShowStmt, reset_rest, CopyStmt, copy_opt_list, copy_opt_item, opt_binary).

Current design (As-is)

• No statement starts with DISCARD, LISTEN or UNLISTEN. DISCARD is a non-reserved keyword used only by MySQL's ALTER TABLE … DISCARD [PARTITION …] TABLESPACE and ALTER USER … DISCARD OLD PASSWORD; LISTEN and UNLISTEN are plain identifiers. NotifyStatement models NOTIFY channel [, 'payload'].
• Show() reads the typed MySQL forms and otherwise captures the rest of the statement: one word becomes ShowStatement(name) (SHOW search_path, SHOW ALL), several words an UnsupportedStatement, so SHOW TIME ZONE is unsupported.
• RESET reads TIME ZONE as the parameter Time Zone, a name or ALL; SET reads TIME ZONE with its own copy of the same rule. Both match any date/time keyword before ZONE, so RESET DATE ZONE and SET DATE ZONE 'UTC' are rewritten to RESET Time Zone and SET Time Zone 'UTC'.
• CopyStatement (DuckDB, and PostgreSQL since feat: support PostgreSQL COPY WITH options #2658) reads COPY {table [(columns)] | (query)} {FROM | TO} path [WITH] [(options)]; getOptions() keeps each option in parentheses as written. Options without parentheses and COPY BINARY table fail.

Target design (To-be)

The new forms are read with Dialect.POSTGRESQL and without a dialect; other dialects are unchanged.

• DISCARD {ALL | PLANS | SEQUENCES | TEMP | TEMPORARY} parses to the new DiscardStatement with the enum DiscardStatement.Target; TEMP and TEMPORARY stay separate constants so that the statement prints as written (as TemporaryOption does). Without a dialect a target must follow DISCARD, so DISCARD foo stays an unsupported statement.
• LISTEN channel and UNLISTEN {channel | *} parse to the new ListenStatement in statement.notify, next to NotifyStatement: getAction() (LISTEN, UNLISTEN), getChannel() (null for *) and the derived isAllChannels(). LISTEN and UNLISTEN become non-reserved keywords, like UNLOCK (feat: parse MySQL LOCK TABLES, UNLOCK TABLES and the backup lock statements #2888), and remain usable as names. As in gram.y (ColId), a channel cannot be a qualified name, a string or an unquoted word that PostgreSQL reserves or keeps for types and functions (LISTEN all, UNLISTEN binary); "all" is a channel. CREATE RULE keeps taking only NOTIFY as an action.
• SHOW / RESET parameter names: one production, ConfigurationParameterName(), reads TIME ZONE, TRANSACTION ISOLATION LEVEL, SESSION AUTHORIZATION or a name. RESET uses it, SHOW uses it when one of the three phrases makes up the whole statement, and SET uses it for TIME ZONE (its other phrases set a transaction mode or the session user, not a parameter). SHOW TIME ZONE parses to ShowStatement with getName() Time Zone (the name SET and RESET already give it), and the other two keep their words. TIME ZONE now requires the word TIME, so RESET DATE ZONE and SET DATE ZONE 'UTC' are rejected instead of rewritten, as PostgreSQL rejects them. SHOW TIME ZONE x and SHOW TRANSACTION ISOLATION keep the generic UnsupportedStatement.
• COPY: CopyStatement gains
  • getLegacyOptions(): the options of the syntax from before 9.0, in source order, each a CopyLegacyOption with a Kind (BINARY, FREEZE, DELIMITER, NULL, CSV, HEADER, QUOTE, ESCAPE, FORCE_QUOTE, FORCE_NOT_NULL, FORCE_NULL, ENCODING), the string (getValue(), a StringValue) and isAsKeyword(), or the columns of a FORCE option (getColumns(), isAllColumns() for *). As copy_opt_list, the options follow the file name and the optional WITH in any order, without commas, also after COPY (query) TO; ENCODING takes no AS, and a FORCE column is an unqualified name.
  • isBinary() for COPY BINARY table. A word after BINARY is needed, so COPY binary TO … still copies a table named binary.
  • The options in parentheses keep getOptions() (List<String>, DuckDB's free-form options included), unchanged. A statement takes either form; COPY t TO STDOUT CSV (FORMAT csv) and COPY t TO STDOUT (FORMAT csv) CSV are rejected, as in PostgreSQL.
  • DuckDB's FORCE INSTALL on the next line still starts the next statement; FORCE continues the options only before QUOTE, NOT or NULL.
• Wiring: StatementVisitor default methods and StatementVisitorAdapter; StatementDeParser renders through the models' appendTo, shared with toString(); TablesNamesFinder reports no table for DISCARD and LISTEN/UNLISTEN and the copied table for COPY; validation features discard and listen, enabled with show in the PostgreSQL capabilities (SHOW was missing there); feature analysis reports MODIFIES_SESSION for DISCARD, LISTEN and UNLISTEN, and a possible MODIFIES_SCHEMA for DISCARD ALL, TEMP and TEMPORARY, which drop the temporary tables of the session; isStatementStartAhead() lists DISCARD, LISTEN and UNLISTEN. The new objects carry AST nodes spanning their source (fix: give every parsed object a node spanning its source #2870). The usage guide, README and index list the statements.
• Public signatures are unchanged; the API is additive.
• Not included: COPY … TO PROGRAM 'cmd', COPY … FROM … WHERE condition and the pre-7.3 [USING] DELIMITERS 'x' before WITH, which are not option syntax; dotted parameter names in RESET (RESET a.b).

Validation

• 133 new tests: DiscardStatementTest 19, ListenStatementTest 32, 54 in PostgreSqlCopyTest, 12 in ShowStatementTest, 13 in ResetStatementTest and PostgreSqlSessionStatementValidatorTest 3; ASTNodeCoverageTest walks the new forms. They fail on master.
  • Every form: parse, toString(), StatementDeParser and reparse with Dialect.POSTGRESQL and without a dialect, plus the AST fields (target, action, channel, parameter name, option kinds, strings, AS, FORCE columns and *, BINARY).
  • Rejected forms: DISCARD, DISCARD TABLES, DISCARD ALL PLANS; LISTEN, LISTEN *, LISTEN a.b, LISTEN 'ch', LISTEN all, UNLISTEN a, b; RESET TRANSACTION ISOLATION, RESET DATE ZONE; COPY t TO STDOUT CSV (FORMAT csv), … (FORMAT csv) CSV, ENCODING AS 'x', DELIMITER without a string, NULL 1, HEADER true, CSV, HEADER, … CSV HEADER WITH, FORCE QUOTE without columns, FORCE QUOTE (a), FORCE QUOTE t.a, FORCE QUOTE a, *, COPY BINARY (SELECT 1) TO STDOUT, and LISTEN ch as a CREATE RULE action.
  • Kept readings: the other dialects (MySQL, MariaDB, Oracle, SQL Server and Snowflake in the tests) reject DISCARD, LISTEN, UNLISTEN, the PostgreSQL RESET phrases and the COPY forms, and keep SHOW TIME ZONE an UnsupportedStatement; ALTER TABLE t DISCARD TABLESPACE, DuckDB's COPY binary TO 'out.csv' (FORMAT CSV) and FORCE INSTALL after a COPY, the parenthesized COPY options, SHOW name, SET TIME ZONE, and listen, unlisten, discard as names.
  • Statement boundaries in scripts (also after an unsupported statement), visitors, table discovery, validation capabilities and statement features.
  • Five existing tests (CheckpointStatementTest, ClusterStatementTest, VacuumStatementTest, ReindexTest, SecurityLabelTest) used LISTEN ch as an example of an unsupported statement; they use IMPORT FOREIGN SCHEMA s FROM SERVER v INTO t now.
• PostgreSQL 18.6 accepts all 84 statements the tests accept, 64 of which master rejects. Some fail only when run, for example COPY FORCE_QUOTE requires CSV mode or a missing file. COPY BINARY s.t TO STDOUT CSV FORCE QUOTE a fails with SQLSTATE 42601 "conflicting or redundant options". The server raises this while it processes the options, not in its grammar, as it does for COPY t TO STDOUT CSV CSV, so the parser accepts it. It reports a syntax error for all 57 statements the tests reject, including the three RESET/SET … DATE ZONE forms that master rewrote to Time Zone. Malformed SHOW phrases such as SHOW TIME ZONE x stay UnsupportedStatement, as on master. MySQL 8.4.11 rejects every new form, and Dialect.MYSQL rejects them too.
• Java 17 offline Gradle spotlessApply cleanTest check jar: 12,922 tests, 12,897 passed, 25 skipped, zero failures/errors. The grammar choice conflict check, Checkstyle, PMD and SpotBugs pass.
• Compared with master (12548b9da) on a corpus of 188 MySQL 8.4 and 166 PostgreSQL 18 statements other than DDL/DCL, each accepted by the server, with the MySQL or PostgreSQL dialect and without a dialect:
  • Fixed with Dialect.POSTGRESQL and without a dialect: COPY t TO STDOUT CSV HEADER, SHOW TIME ZONE, SHOW TRANSACTION ISOLATION LEVEL (both were UnsupportedStatement), DISCARD ALL, DISCARD PLANS, LISTEN ch and UNLISTEN *.
  • 0 regressed and 0 changed.
• Compared with master (12548b9da) on a corpus of 635 MySQL 8.4 and PostgreSQL 18 DDL/DCL statements, each accepted by the server, plus variants, scripts and the keyword sweeps: 0 regressed, 0 changed.

AI assistance: prepared with Claude Code.