Re: Parameter in SQL query being misinterpreted

On 12/5/24 17:27, Daniel Johnson wrote:
> Good day!  I've run into a quirk executing an SQL statement and am not
> sure if it's a fundamental issue with my query or a bug in psycopg3.
>
> In short, I'm inserting/updating a row using both NOW() and 'NOW() plus
> a varying value'.  The NOW() value is fine, but what ends up in the
> database for the second one is just "NOW() + 4 seconds".  I finally
> realized this is because of how the command is being processed, the
> variable becomes "$4" and apparently the dollar sign is lost.
>
> I'm guessing this is an example of why one of the bullet points at
> https://www.psycopg.org/psycopg3/docs/basic/params.html says "The
> placeholder must not be quoted".  :-/  I would appreciate any advice on
> how to work around this while still passing in the value safely

Pretty sure the below does not work because the placeholder is becoming
part of the literal string:

NOW() + INTERVAL '%(NextDBLog)s SECOND'

You might try something like:

NOW() + INTERVAL || ' ' || %(NextDBLog)s || ' SECOND'

If that does work then you will need to use the psycopg.sql module to
build that part of the query
>
>
> Here's the command from Python.  The value we're looking at is for
> "nextupdate".  "Stats" is a dictionary which contains "NextDBLog" as an
> integer whose value is something between 120 and 800.
> =-=-=-=-=-=-=-=-=-
> dbRWcur.execute('''
>     INSERT INTO backendstatus
>         (nodeid, debug, started, lastupdated, nextupdate, version,
> platform, python, cpucores, state, uptime, taskstats)
>     VALUES
>         (%(nodeid)s, %(debug)s, %(laststarteddb)s, NOW(), NOW() +
> INTERVAL '%(NextDBLog)s SECOND', %(version)s, %(platform)s, %(python)s,
> %(cpucores)s, %(state)s, %(uptime)s, %(statsstring)s)
>     ON CONFLICT
>         (nodeid)
>     DO UPDATE SET
>         debug       = excluded.debug,
>         started     = excluded.started,
>         lastupdated = excluded.lastupdated,
>         nextupdate  = excluded.nextupdate,
>         version     = excluded.version,
>         platform    = excluded.platform,
>         python      = excluded.python,
>         cpucores    = excluded.cpucores,
>         state       = excluded.state,
>         uptime      = excluded.uptime,
>         taskstats   = excluded.taskstats;
>     ''', Stats)
> =-=-=-=-=-=-=-=-=-
>
>
> After the command is processed this is the contents of
> dbRWcur._query.query.decode('utf-8').
> =-=-=-=-=-=-=-=-=-
> INSERT INTO backendstatus
>     (nodeid, debug, started, lastupdated, nextupdate, version,
> platform, python, cpucores, state, uptime, taskstats)
> VALUES
>     ($1, $2, $3, NOW(), NOW() + INTERVAL '$4 SECOND', $5, $6, $7, $8,
> $9, $10, $11)
> ON CONFLICT
>     (nodeid)
> DO UPDATE SET
>     debug       = excluded.debug,
>     started     = excluded.started,
>     lastupdated = excluded.lastupdated,
>     nextupdate  = excluded.nextupdate,
>     version     = excluded.version,
>     platform    = excluded.platform,
>     python      = excluded.python,
>     cpucores    = excluded.cpucores,
>     state       = excluded.state,
>     uptime      = excluded.uptime,
>     taskstats   = excluded.taskstats;
> =-=-=-=-=-=-=-=-=-
>
> This is being run on Rocky Linux v9.5 (essentially Red Hat / RHEL).  The
> relevant installed packages are:
>     python3-3.9.19-8.el9_5.1.x86_64
>     python3-psycopg3-3.1.18-4.el9.noarch  (from EPEL)
>     postgresql-server-15.8-2.module+el9.5.0+28955+a22540b0.x86_64
>
>
>
> Daniel Johnson
> djohnson(at)progman(dot)us
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com