Queries work with (query "...") but not when defprepared. ASDF Load order?

[nathanvy]

I have a web app that I am writing, and I am receiving syntax errors for queries when I use defprepared, but not if I use the same SQL statement with query.

For example, suppose I have a middleware that checks to see if the session ID supplied in an HTTP cookie exists in the "sessions" table. This fails with a 'syntax error near "#"':

(pomo:defprepared query-sessions "select account from fnlst.sessions where sid = $1")

;; and then elsewhere... 
(let ((account (query-sessions sess-id))) ... )

Whereas this works without issue:

(let ((account (pomo:query "select account from fnlst.sessions where sid = $1" sess-id)) ... )

However, if I execute the defprepared query manually at the REPL, then it works thereafter without throwing a syntax error. So, somehow when I quickload the project, the prepared statements are not properly being sent to postgres, perhaps. I'm not sure how to debug/investigate this any further.

[sabracrolleton]

Is your "middleware" in the same package as the package where you are calling the prepared statement?

[nathanvy]

Yes indeed, it's all one package.

[sabracrolleton]

I am trying to create a test program that will trigger the problem (unsuccessfully so far). Can I ask you to insert something like the following immediately after you make the call to defprepared (obviously in the same file) and then reload?

(pomo:with-connection ABC ;; where ABC is a valid connection parameter list
  (format t "~%Query-Session: ~a~%" (query-sessions XYZ)) ;; where XYZ is the same data type as a sess-id
  (format t "~%Postgresql Prepared Statements: ~a~%" (pomo:list-prepared-statements))
  (format t "~%Postmodern Prepared Statements: ~a~%" (pomo:list-postmodern-prepared-statements)))

I would expect the first format statement to show nil from the call to query-sessions (since you just made up a session-id) and the second and third format statements should print nested list showing info about the prepared statements in the connection sessions.

If that works but the later call to query-sessions elsewhere still triggers a syntax error, or if the second and third format statements are inconsistent (one reporting a list and the other reporting NIL) then I have a couple more questions:

1. What is the value for pomo:*allow-overwriting-prepared-statements*?
2. Are you using pooled connections?

If the call to query-sessions during the initial loading triggers a syntax error, we probably will not get anything useful from listing the prepared statements, but at least it would tell us that the problem is in that file.

[nathanvy]

CL-USER> (ql:quickload :finalist)
To load "finalist":
  Load 1 ASDF system:
    finalist
; Loading "finalist"
......................................
Query-Session: NIL

Postgresql Prepared Statements: (((NAME . QUERY-SESSIONS)
                                  (STATEMENT
                                   . select account from fnlst.sessions where sid = $1)
                                  (PREPARE-TIME . 3927459145)
                                  (PARAMETER-TYPES . {text}) (FROM-SQL)
                                  (GENERIC-PLANS . 0) (CUSTOM-PLANS . 1)))

Postmodern Prepared Statements: ((QUERY-SESSIONS
                                  select account from fnlst.sessions where sid = $1
                                  (fgsfds)))

CL-USER> pomo:*allow-overwriting-prepared-statements*
T

I am not (currently) using pooled connections, but I plan to.

[sabracrolleton]

OK. That was what it should be.

I assume that your later call to query-sessions still triggers a syntax error and your session-id is supposed to be text.

Will keep trying to create a reproducible failure here. If you have any bright ideas on the simplest way to trigger it, let me know.

[nathanvy]

Thanks. I appreciate your time and effort. I will keep tinkering on my end.

On Sat, Jun 15, 2024, 10:15 Sabra Crolleton @.***> wrote:

OK. That was what it should be.

I assume that your later call to query-sessions still triggers a syntax error and your session-id is supposed to be text.

Will keep trying to create a reproducible failure here. If you have any bright ideas on the simplest way to trigger it, let me know.

— Reply to this email directly, view it on GitHub https://github.com/marijnh/Postmodern/issues/352#issuecomment-2170388364, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAQHSBXAOCGF3ZOSVW5KVBTZHRZCFAVCNFSM6AAAAABJLL4RLSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNZQGM4DQMZWGQ . You are receiving this because you authored the thread.Message ID: @.***>

[nathanvy]

Okay so, I have determined that if I manually execute query-sessions or any other defprepared statement at the REPL, the bug does not reproduce.

Whereas if I just run the server after quickloading it, it causes these errors. It's as if the queries aren't actually being sent to postgres until I issue C-x C-e in emacs.

[sabracrolleton]

Another question as I try to re-create it on my end:

1. How are you connected to Postgresql in your initial call to query-sessions when you are quickloading everything? Are you using with-connection or connect-toplevel or something else?
2. How are you connected to Postgresql when you are using the REPL?

[nathanvy]

I use with-connection in all cases.

On Tue, Jun 18, 2024, 07:37 Sabra Crolleton @.***> wrote:

Another question as I try to re-create it on my end:

1. How are you connected to Postgresql in your initial call to query-sessions when you are quickloading everything? Are you using with-connection or connect-toplevel or something else?
2. How are you connected to Postgresql when you are using the REPL?

— Reply to this email directly, view it on GitHub https://github.com/marijnh/Postmodern/issues/352#issuecomment-2176266382, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAQHSBXJNY2KI322ZCCV74LZIBAZ7AVCNFSM6AAAAABJLL4RLSVHI2DSMVQWIX3LMV43OSLTON2WKQ3PNVWWK3TUHMZDCNZWGI3DMMZYGI . You are receiving this because you authored the thread.Message ID: @.***>

[sabracrolleton]

Can you look at a quick and dirty example at https://github.com/sabracrolleton/defprep and tell me what we are doing differently?

[nathanvy]

It sure looks like we're doing the same thing. I've sort of given up at this point.

[sabracrolleton]

Closing this as we cannot generate test case that reproduces the syntax error.