sqlparser-rs can not parse pgvector SQL

[xieydd]

I want to use pg_later to create pgvector index as an async job. But it looks like sqlparser-rs can not parse the SQL.

postgres=# select pglater.exec('CREATE INDEX ON items USING hnsw (embedding vector_l2_ops)') as job_id;
ERROR:  Query parsing failed, please submit a valid query: ParserError("Expected ), found: vector_l2_ops at Line: 1, Column 45")

$ cargo run
   Compiling sql_parse_test v0.1.0 (/Users/xieyuandong/Workspace/rust/sql_parse_test)
    Finished dev [unoptimized + debuginfo] target(s) in 1.32s
     Running `target/debug/sql_parse_test`
thread 'main' panicked at src/main.rs:8:48:
called `Result::unwrap()` on an `Err` value: ParserError("Expected ), found: vector_l2_ops at Line: 1, Column 45")
note: run with `RUST_BACKTRACE=1` environment variable to display a backtrace

(base)
~/Workspace/rust/sql_parse_test on  main! ⌚ 15:20:19
$ cat src/main.rs
use sqlparser::dialect::GenericDialect;
use sqlparser::parser::Parser;

fn main() {
    let sql="CREATE INDEX ON items USING hnsw (embedding vector_l2_ops);";
    let dialect = GenericDialect {}; // or AnsiDialect, or your own dialect ...

    let ast = Parser::parse_sql(&dialect, sql).unwrap();

    println!("AST: {:?}", ast);
}

[xieydd]

@ChuckHend I found you leverage sqlparser-rs for query parsing in this pr https://github.com/tembo-io/pg_later/pull/48/files.

[ChuckHend]

I see what you mean. Thats not intended / definitely a bug.

[xieydd]

I see what you mean. Thats not intended / definitely a bug.

Thank you for your reply, will we fix this bug?

[xieydd]

Currently, I am using version 0.0.14 and have skipped the validate process.

However, I encountered a problem. After creating the task, I am unable to obtain the results.

postgres=# select pglater.init();
NOTICE:  relation "q_pg_later_jobs" already exists, skipping
NOTICE:  relation "a_pg_later_jobs" already exists, skipping
NOTICE:  relation "q_pg_later_jobs_vt_idx" already exists, skipping
NOTICE:  relation "archived_at_idx_pg_later_jobs" already exists, skipping
NOTICE:  relation "q_pg_later_results" already exists, skipping
NOTICE:  relation "a_pg_later_results" already exists, skipping
NOTICE:  relation "q_pg_later_results_vt_idx" already exists, skipping
NOTICE:  relation "archived_at_idx_pg_later_results" already exists, skipping
 init
------
 t
(1 row)

postgres=# select pglater.exec(                                                                                                                                                                                                                 'select * from pg_available_extensions order by name limit 2'                                                                                                                                                                               ) as job_id;
 job_id
--------
      5
(1 row)

postgres=# select pglater.fetch_results(5);
 fetch_results
---------------

(1 row)

postgres=# select * from pgmq.q_pg_later_jobs;
postgres=# select * from pgmq.q_pg_later_jobs where msg_id=5;
 msg_id | read_ct |          enqueued_at          |              vt               |                                 message
--------+---------+-------------------------------+-------------------------------+--------------------------------------------------------------------------
      5 |       0 | 2024-12-12 03:46:15.867772+00 | 2024-12-12 03:46:15.868156+00 | {"query": "select * from pg_available_extensions order by name limit 2"}
(1 row)

Why the query is not executed?

[xieydd]

Currently, I am using version 0.0.14 and have skipped the validate process.

However, I encountered a problem. After creating the task, I am unable to obtain the results.

postgres=# select pglater.init();
NOTICE:  relation "q_pg_later_jobs" already exists, skipping
NOTICE:  relation "a_pg_later_jobs" already exists, skipping
NOTICE:  relation "q_pg_later_jobs_vt_idx" already exists, skipping
NOTICE:  relation "archived_at_idx_pg_later_jobs" already exists, skipping
NOTICE:  relation "q_pg_later_results" already exists, skipping
NOTICE:  relation "a_pg_later_results" already exists, skipping
NOTICE:  relation "q_pg_later_results_vt_idx" already exists, skipping
NOTICE:  relation "archived_at_idx_pg_later_results" already exists, skipping
 init
------
 t
(1 row)

postgres=# select pglater.exec(                                                                                                                                                                                                                 'select * from pg_available_extensions order by name limit 2'                                                                                                                                                                               ) as job_id;
 job_id
--------
      5
(1 row)

postgres=# select pglater.fetch_results(5);
 fetch_results
---------------

(1 row)

postgres=# select * from pgmq.q_pg_later_jobs;
postgres=# select * from pgmq.q_pg_later_jobs where msg_id=5;
 msg_id | read_ct |          enqueued_at          |              vt               |                                 message
--------+---------+-------------------------------+-------------------------------+--------------------------------------------------------------------------
      5 |       0 | 2024-12-12 03:46:15.867772+00 | 2024-12-12 03:46:15.868156+00 | {"query": "select * from pg_available_extensions order by name limit 2"}
(1 row)

Why the query is not executed?

permission error cause BGWorker not running, ignore it

[ChuckHend]

, will we fix this bug?

Yes. I think we want to keep the SQL parser, or find an alternative to it, because it is a great way to reject invalid queries before they are executed. I could use some help finding a solution. Or I can look at it sometime in the next couple weeks.