public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniel Johnson <[email protected]>
To: [email protected]
Subject: Parameter in SQL query being misinterpreted
Date: Thu, 5 Dec 2024 19:27:43 -0600
Message-ID: <[email protected]> (raw)

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.


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
[email protected]

view thread (6+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Parameter in SQL query being misinterpreted
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox