Feedback-ID: i76984098:Fastmail
Message-ID: <cc13d120-44c1-4d13-b478-57015d54fc1e@aklaver.com>
Date: Thu, 5 Dec 2024 17:37:46 -0800
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Parameter in SQL query being misinterpreted
To: Daniel Johnson <djohnson@progman.us>, psycopg@lists.postgresql.org
References: <dfc11d8e-dc27-4c72-a2aa-87b2150dc331@progman.us>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <dfc11d8e-dc27-4c72-a2aa-87b2150dc331@progman.us>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: 
 <https://www.postgresql.org/message-id/cc13d120-44c1-4d13-b478-57015d54fc1e%40aklaver.com>
Precedence: bulk

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@progman.us
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com