PostgreSQL `CREATE INDEX` rejects column sort options like `DESC`

[AlexS778]

Summary

The PostgreSQL grammar currently produces an ERROR node for valid index
definitions that specify per-column sort order:

CREATE INDEX tasks_created
    ON main.tasks
        USING btree (created DESC);

The error starts at DESC.

This affects ordinary btree index definitions such as:

(created DESC)
(updated DESC)
(created ASC NULLS FIRST)
(updated DESC NULLS LAST)

Minimal reproduction

From the repository root:

cat > /tmp/index_desc.sql <<'SQL'
CREATE INDEX tasks_created
    ON main.tasks
        USING btree (created DESC);
SQL

./node_modules/.bin/tree-sitter parse --grammar-path postgres /tmp/index_desc.sql

Actual behavior

The index statement is mostly parsed, but DESC is left under an ERROR node:

(IndexStmt
  ...
  (index_params
    (index_elem
      (ColId
        (identifier))))
  (ERROR
    (any_name
      (ColId
        (identifier)))))

The reported range is equivalent to:

ERROR at DESC

Expected behavior

The whole statement should parse without an ERROR node.

DESC, ASC, and optional NULLS FIRST / NULLS LAST should be part of the
index element options.

Expected valid examples:

CREATE INDEX idx_created_desc ON t USING btree (created DESC);
CREATE INDEX idx_created_asc ON t USING btree (created ASC);
CREATE INDEX idx_created_nf ON t USING btree (created ASC NULLS FIRST);
CREATE INDEX idx_created_nl ON t USING btree (created DESC NULLS LAST);

Why this matters

This is common PostgreSQL syntax for indexes used by ordered queries. In a
schema corpus, every file containing indexes like USING btree (created DESC)
fails to parse even though the SQL is valid PostgreSQL.

Likely location

The relevant rules appear to be around index element options:

postgres/grammar.js
  index_elem
  index_elem_options
  opt_asc_desc
  opt_nulls_order

Because the grammar is generated, the fix may need to happen in the generator
source as well, not only in the generated postgres/grammar.js.

Suggested regression tests

Add corpus cases for:

CREATE INDEX idx_a ON t USING btree (a DESC);
CREATE INDEX idx_b ON t USING btree (b ASC);
CREATE INDEX idx_c ON t USING btree (c DESC NULLS LAST);
CREATE INDEX idx_d ON t USING btree (d ASC NULLS FIRST);
CREATE INDEX idx_multi ON t USING btree (a DESC, b ASC NULLS FIRST);

[AlexS778]

Having a rough time testing new fixes, after changing codegen.js it takes around 30 minutes to generate grammars...

I'm using MacBook Air m1 with 8gb ram.

../node_modules/.bin/tree-sitter generate grammar.js --abi 15 I'm using this. @gmr do you have any ideas maybe to "speed up" generating grammars? Spent whole day just generating...

[gmr]

oof, we might be able to approach if you give me the end result and I can reverse engineer into the grammar.

[AlexS778]

oof, we might be able to approach if you give me the end result and I can reverse engineer into the grammar.

@gmr have you actually tried to generate tree sitter files from current postgres/grammar.js?

I thought my changes were the problem, but even without my new changes at the last commit 7da18b2, generating tree sitter artifacts gives me an OOM even on my pc with 16 gb ram.

Full log:

cd /home/user/tree-sitter-postgres/postgres

git restore src/parser.c

/usr/bin/time -v $HOME/.cargo/bin/tree-sitter generate grammar.js --abi 15 --output src --log
echo "exit=$?"

ls -lh src/parser.c src/grammar.json src/node-types.json
git -C .. status --short
Command terminated by signal 9
        Command being timed: "/home/user/.cargo/bin/tree-sitter generate grammar.js --abi 15 --output src --log"
        User time (seconds): 238.28
        System time (seconds): 9.45
        Percent of CPU this job got: 55%
        Elapsed (wall clock) time (h:mm:ss or m:ss): 7:22.72
        Average shared text size (kbytes): 0
        Average unshared data size (kbytes): 0
        Average stack size (kbytes): 0
        Average total size (kbytes): 0
        Maximum resident set size (kbytes): 12490168
        Average resident set size (kbytes): 0
        Major (requiring I/O) page faults: 14337
        Minor (reclaiming a frame) page faults: 3140442
        Voluntary context switches: 15326
        Involuntary context switches: 4983
        Swaps: 0
        File system inputs: 1875240
        File system outputs: 2680
        Socket messages sent: 0
        Socket messages received: 0
        Signals delivered: 0
        Page size (bytes): 4096
        Exit status: 0
exit=137
-rw-rw-r-- 1 user user 1,4M мая 12 00:08 src/grammar.json
-rw-rw-r-- 1 user user 490K мая 11 22:01 src/node-types.json
-rw-rw-r-- 1 user user  91M мая 11 23:57 src/parser.c
?? nodesource_setup.sh
?? postgres/rustup-init.sh
?? sss.txt

I use rust binary here, not the node one, but that's not the case. I was using node tree sitter binary and was getting the same stuff on my Mac.

It's a rather serious issue because we can't generate tree sitter stuff right now from current grammar.js (well maybe we can with 128gb ram but I don't have that much).

[gmr]

Yes I have but my machine is a bit of a beast.

[AlexS778]

Yes I have but my machine is a bit of a beast.

I thought we were already past "works on my machine" in 2026 😭

But jokes aside we need a fix to optimize codegen.js so it would generate such grammar.js which doesn't consume atrocious amounts of memory.

Otherwise it's impossible for me to test changes.

[AlexS778]

Found rather relevant issues:

tree-sitter/tree-sitter#1890: same symptom as I had — parser.c not updating because tree-sitter generate was OOM-killed.
tree-sitter-scala#102: reduced memory from ~34 GB to ~11 GB by factoring a large rule into a hidden helper.
tree-sitter-scala#106: reduced memory from ~15 GB to ~1 GB using the same trick more broadly.
nvim-treesitter#2177: mostly about parser compilation parallelism, less relevant to our generator OOM.
tree-sitter-python#207: runtime scanner-state memory bug, also less relevant to our current generator OOM.

[AlexS778]

related #23