Fork me on GitHub
#sql
<
2020-07-24
>
nick05:07:11

If anyone might have an idea why 1st request returns some data and 2nd one fails, please share your thoughts

(next.jdbc/execute-one! t-conn
                        ["SELECT * FROM foo WHERE id = ?" 123])

(next.jdbc/execute-one! t-conn
                        ["COPY(SELECT * FROM foo WHERE id = ?) TO '/tmp/foo.csv' WITH CSV DELIMITER ',' HEADER" 123])
Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2532).
ERROR: there is no parameter $1
  Position: 36
A bit of context: I'm using honeysql to construct a huge structure which is used for displaying records(total+pagination) and also for exporting it as CSV file, so I'm passing this SELECT query inside COPY psql function.

nick05:07:23

These 2 queries look very similar.. same number of arguments. No idea why in 2nd case it fails to find a parameter

seancorfield05:07:58

@nfedyashev Just to clarify, since you talk about HoneySQL and a "huge structure", are the two queries above the repro case without all of that complexity? So, the actual queries that work and fail respectively?

seancorfield05:07:46

(i.e., the failing case is something I could actually add to the next.jdbc test suite for PG and expect it to "work" if I can figure out what the issue is ­čÖé )

nick05:07:14

@seancorfield this should be the shortest code to reproduce the issue(2nd function fails):

(next.jdbc/execute-one! t-conn
                          ["SELECT 1 WHERE 2 = ?" 2])

  (next.jdbc/execute-one! t-conn
                          ["COPY(SELECT 1 WHERE 2 = ?) TO '/tmp/foo.csv' WITH CSV DELIMITER ',' HEADER" 2])
Execution error (PSQLException) at org.postgresql.core.v3.QueryExecutorImpl/receiveErrorResponse (QueryExecutorImpl.java:2532).
ERROR: there is no parameter $1
  Position: 25

nick05:07:38

If I execute this copy command in psql, it works fine:

COPY(SELECT 1 WHERE 2 = 2) TO '/tmp/foo.csv' WITH CSV DELIMITER ',' HEADER;
COPY 1

nick05:07:59

PostgreSQL 11.7 next.jdbc "1.1.547"

nick06:07:17

@seancorfield thanks for your time Sean! Let me know if you need any additional info. Not sure if I fully understood your clarification question. That looks like next.jdbc level issue, not HoneySQL.

seancorfield06:07:42

Yup, you clarified. Thanks. I'll create an issue and investigate tomorrow or over the weekend. It's an odd one. But, hey, PostgreSQL is its own kind of special...

seancorfield06:07:59

@nfedyashev I can repro that in the test suite. Thank you! Now I just have to keep poking at it until I figure out why it breaks ­čÖé

­čÖĆ 3
seancorfield06:07:28

ERROR in (postgresql-issue-136) (QueryExecutorImpl.java:2510)
Uncaught exception, not in assertion.
expected: nil
  actual: org.postgresql.util.PSQLException: ERROR: there is no parameter $1
  Position: 25
 at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse (QueryExecutorImpl.java:2510)
    org.postgresql.core.v3.QueryExecutorImpl.processResults (QueryExecutorImpl.java:2245)
    org.postgresql.core.v3.QueryExecutorImpl.execute (QueryExecutorImpl.java:311)
    org.postgresql.jdbc.PgStatement.executeInternal (PgStatement.java:447)
    org.postgresql.jdbc.PgStatement.execute (PgStatement.java:368)
    org.postgresql.jdbc.PgPreparedStatement.executeWithFlags (PgPreparedStatement.java:159)
    org.postgresql.jdbc.PgPreparedStatement.execute (PgPreparedStatement.java:148)
    next.jdbc.result_set$stmt__GT_result_set.invokeStatic (result_set.clj:631)

seancorfield07:07:02

@nfedyashev After doing some research on this, it seems that the COPY command cannot have parameters (I found a node.js repo for PG that supports COPY and has closed out several issues asking for parameterized copy queries, saying that PG simply doesn't support it. I wondered if you could do it via EXECUTE format(...) USING ... but that doesn't seem to support the COPY command (only basic CRUD operations). So I'm not sure that this is actually possible.

seancorfield07:07:29

Do you have links to documentation indicating that it should be possible via a PreparedStatement?

nick07:07:32

yes, I was just trying to post it in the issue - https://www.postgresql.org/docs/9.3/sql-prepare.html

nick07:07:42

"Any SELECT, INSERT, UPDATE, DELETE, or VALUES statement."

nick07:07:23

I'll try to transform it into a regular string

seancorfield07:07:33

Right, but COPY is not a SELECT, INSERT, UPDATE, DELETE

seancorfield07:07:27

So... close the issue out as "not possible"?

nick07:07:16

yep. Thank you

nick07:07:58

As my PostgreSQL friend explained, same issue is with TRUNCATE, GRANT functions and some others. They function more like a utility rather than other regular commands so it's kind of hard to transform them into a prepared statement

bartuka13:07:33

hi, anyone has experience in hugsql? I need to convert this query into their syntax.

SELECT id
FROM (VALUES(4),(5),(6)) V(id)
EXCEPT
SELECT id 
FROM images;
The closer I got was
select id from (values (:v*:my-arguments)) v(id) except select id from images;
the problem is that (:v*:my-arguments) does produce (4,5,6) not (4) (5) (6)

curtis.summers15:07:07

@iagwanderson Take a look at the Tuple List parameter type: https://www.hugsql.org/#param-tuple-list

Ôťö´ŞĆ 3
seancorfield16:07:07

@iagwanderson Also, there's a #hugsql channel.

bartuka16:07:17

thanks @seancorfield I will check there too next time.

seancorfield16:07:23

Curtis is often pretty attentive here but you'll find more HugSQL users in that channel, in case he's not around ­čÖé

seancorfield16:07:54

(my experience of HugSQL extends solely to getting next.jdbc working with it, per the "Getting Started with HugSQL" section of the next.jdbc docs)