RE: Request for official clarification on SQL parameter parsing changes in PostgreSQL 15 and 16

[王 博]

Dear Tom, Laurenz, and Dominique,

Thank you all very much for your helpful and detailed explanations.

Your insights clarified the behavior change in PostgreSQL 15 perfectly, and I 
now have a clear understanding of the issue I was encountering. I really 
appreciate your time and support.

Best regards,  
Wang Bo


-Original Message-
From: Tom Lane  
Sent: Thursday, April 17, 2025 11:16 PM
To: Laurenz Albe 
Cc: 王　博 ; pgsql-general@lists.postgresql.org; 李　浩 

Subject: Re: Request for official clarification on SQL parameter parsing 
changes in PostgreSQL 15 and 16

Laurenz Albe  writes:
> On Thu, 2025-04-17 at 05:17 +, 王　博 wrote:
>> 1. In PostgreSQL 15 and later:
>>    The following SQL causes a syntax error unless a space is added after the 
>> `?`:
>>  SELECT * FROM table WHERE a = ?AND b = 123;
>>    → Adding a space (`? AND`) resolves the issue.

> I'd say it is this change:
> https://postgr.es/c/2549f0661bd28571d7200d6f82f752a7ee5d47e1

Yeah.  This looks like "?" ought to be parsable as a separate token ... but as 
Dominique noted, it's not actually legal syntax in any version of Postgres.  
Something in your client stack must be translating "?" to "$1", "$2", etc, and 
so the new prohibition against junk trailing a number applies.

You could fix this without application-level changes if you fixed whatever is 
making that substitution to add spaces around the parameter symbol.  It's 
really a bug that it didn't do so already, since closely-adjacent cases like 
digits immediately after the "?" would already have caused failures.

regards, tom lane

Re: Error while updating a table

[sivapostg...@yahoo.com]

 If the query caused an error 
Does it mean that other reasons might also be there?
Nevertheless, I'll check the log file to find the query...
Happiness Always
BKR Sivaprakash
On Friday 18 April, 2025 at 03:53:06 pm IST, Laurenz Albe 
 wrote:  
 
 On Fri, 2025-04-18 at 07:31 +, sivapostg...@yahoo.com wrote:
> For the second part, how to identify that culprit query?

If the query caused an error, and you left "log_min_messages" at the
default setting, the error and the statement that caused it should
be in the PostgreSQL log.

Yours,
Laurenz Albe


  

Re: Cannot turn track_counts on

[Anton Shepelev]

Adrian Klaver to Anton Shepelev:

> >db=# show shared_preload_libraries;
> > shared_preload_libraries
> >---
> > online_analyze, plantuner
>
> Are you running PostgresPro?
>
> Both those modules are associated with it:
>
> https://postgrespro.com/docs/postgrespro/17/contrib.html

Not at all.  Whereas `pg_config --version' answers with an
irrelevant quip:

   You need to install postgresql-server-dev-NN for building
   a server-side extension or libpq-dev for building a
   client-side application.

The version() SQL function returns:

   PostgreSQL 11.21 (Debian 1:11.21-astra.se6+ci1)
   on x86_64-pc-linux-gnu,
   compiled by gcc (AstraLinuxSE 8.3.0-6) 8.3.0, 64-bit

I will test if clearing shared_preload_libraries and
restarting Postgres has any effect on track_counts, just in
case.

-- 
()  ascii ribbon campaign -- against html e-mail
/\  www.asciiribbon.org   -- against proprietary attachments

Re: verify checksums online

[Jeremy Schneider]

On Fri, 18 Apr 2025 17:32:19 -0400
Ron Johnson  wrote:

> On Fri, Apr 18, 2025 at 5:18 PM Jeremy Schneider
>  wrote:
> 
> > i think there had been some mailing list discussions years ago? the
> > pg_checksum utility in core still can't do an online check right?
> >
> 
> https://www.postgresql.org/docs/17/app-pgchecksums.html
> 
> "The server must be shut down cleanly before running pg_checksums."

Yes I read the docs. This utility has evolved over time including a
complete rename, and I was just doublechecking here that we didn't miss
a doc update in case this sentence applied to enable/disable but not to
verify. As I said I'm assuming it's all accurate but just doing the
paranoid double-check

-Jeremy

Why is an error not thrown when the time exceeds the lock timeout for an ExclusiveLock on a transaction ?

[Mansky, Edmund J. (GSFC-671.0)[ADNET Affiliate]]

Hi Everyone,

I have an SQL that updates a table in our database. The SQL in question is:

UPDATE public.sum_partn_alloc AS T1 SET effective_date = 
to_char(CURRENT_TIMESTAMP + interval '6 days', 'MMDDHH24MI') FROM 
public.sum_main AS T2
WHERE T1.status != 8 AND (T1.effective_date = '0' OR CURRENT_TIMESTAMP 
+ interval '3 days' >  to_timestamp(T1.effective_date, 'MMDDHH24MI'))
AND T1.ds_index IN (1741052236) AND T1.ds_index = T2.ds_index AND 
T2.online_status = 'Y'

where the number in BOLD in the list can be just a single number, or a long 
list of numbers (20-50 or so).

Normally, this SQL is executed without problem thousands of times a day.

Then, we see at certain times when that SQL exceeds the timeout for the 
ShareLock on the transaction. No error is thrown from the DB and processing on 
the driving Python script stops.
I've seen this in the case of a single number in the list, or a long list of 
20-50 numbers or so.

I see in the log at that point : process 683860 still waiting for ShareLock on 
transaction 492777941 after 1000.140 ms
  while updating tuple (1282991,25) 
in relation "sum_partn_alloc"

The Postgres server (12.22), running on RHEL 8.10 is configured with a default 
lock timeout of 1 sec.

Why is Postgres not throwing an error when the ShareLock time has exceeded 1 
sec. ?

The Python script driving this SQL never gets an error from Postgres in it's 
exception block, and hence the script simply stops at that point and processing 
stops.

Are there other parameters one can set in the configuration that would result 
in the Python script getting an error from Postgres so that this case can be 
handled and processing continues ?

Any ideas or tips would be greatly appreciated.

Thanks,

--Ed






[signature_1379146699]

Ed Mansky
Software Engineer
SDAC / VSO
NASA Goddard Space Flight Center
ADNET SYSTEMS, Inc.
8800 Greenbelt Rd, Greenbelt MD 20771
edmund.j.man...@nasa.gov

verify checksums online

[Jeremy Schneider]

i should know the answer to this... but asking anyway

i think there had been some mailing list discussions years ago? the
pg_checksum utility in core still can't do an online check right?

looking around, i think that michael's pg_checksums_ext still seems to
be available in debian repos, and it advertises online checks, which
makes me think that the core pg_checksum might still not have merged
any code for online checking yet

but i wanted to confirm with someone - my easiest options for online
verifying checksums would be to trigger pg_basebackup then check
pg_stat_database, or to install michael's utility?

-Jeremy

Re: verify checksums online

[Ron Johnson]

On Fri, Apr 18, 2025 at 5:18 PM Jeremy Schneider 
wrote:

> i should know the answer to this... but asking anyway
>
> i think there had been some mailing list discussions years ago? the
> pg_checksum utility in core still can't do an online check right?
>

https://www.postgresql.org/docs/17/app-pgchecksums.html

"The server must be shut down cleanly before running pg_checksums."


> looking around, i think that michael's pg_checksums_ext still seems to
> be available in debian repos, and it advertises online checks, which
> makes me think that the core pg_checksum might still not have merged
> any code for online checking yet
>
> but i wanted to confirm with someone - my easiest options for online
> verifying checksums would be to trigger pg_basebackup then check
> pg_stat_database, or to install michael's utility?
>

-- 
Death to , and butter sauce.
Don't boil me, I'm still alive.
 lobster!

Re: Error while updating a table

[sivapostg...@yahoo.com]

 Thanks Laurenz,
Now I need to do 2 things.
1.  Correct my application to catch errors and do either Rollback or commit.   
This part is running smoothly from all other computers, so far.  Nevertheless 
I'll check that for any missing catch.2.  Find the faulty entry and do rectify.
For the second part, how to identify that culprit query?   Seems this has 
happened a few days back.  At that time, we had some power issues that made 
network to fail.  
To proceed, we need to find the culprit query?  How? Where?  No idea what was 
the pid then, as all computers were shut down every night.
Any possibilities?  steps?
Happiness Always
BKR Sivaprakash

On Friday 18 April, 2025 at 12:24:54 pm IST, Laurenz Albe 
 wrote:  
 
 On Fri, 2025-04-18 at 05:49 +, sivapostg...@yahoo.com wrote:
> We use PowerBuilder along with PostgreSQL.
> 
> PostgreSQL 15.7 (Ubuntu 15.7-1.pgdg24.04+1) on x86_64-pc-linux-gnu, compiled 
> by gcc (Ubuntu 13.2.0-23ubuntu4) 13.2.0, 64-bit
> 
> Suddenly, one system starts throwing an error while updating a record from 
> PowerBuilder.
> The same update statement (window) works fine, when run from other computers. 
>  Error
> occurs only when the statement is run from a particular computer.  The SQL 
> Statement is
> 
> Update public.co_voucherseries_transaction_branch
> Set    vouchernumber   = 
> Where  companycode     = '100'
> And    branchcode      = '001'
> And    accountperiodid = 1
> And    voucherseries   = 'SERIES'
> And    voucherversion  = 'version'
> And    activestatus    = 'Y'  ;
> 
> The error is
> SQLSTATE=25P02
> ERROR: current transaction is aborted, commands ignored until end of 
> transaction block;
> Error while executing the query
> 
> Through googling, I found that last transaction was not properly rolled back.
> The (Postgresql) Linux server will be shut down every night and re-booted 
> next day morning.   
> 
> Don't know when this error has occurred.  
> 
> Now I need to rectify this error?  How to do it?  Any help is really 
> appreciated.

You would have to improve the code quality of the application, which does not 
seem
to do correct error handling or transaction management.

That error means that the *previous* statement (or one of the statements 
earlier in
the same database transaction) has caused an error.  In PostgreSQL, that means 
that
the transaction is aborted, and the following statements until the end of the
transaction will receive the error you experience.

So one of the following must be the case:

- the previous statement caused an error, but you didn't detect or handle that

- the previous transaction run on the same database connection caused an error,
  but you forgot to run ROLLBACK

Yours,
Laurenz Albe


  

Re: Error while updating a table

[Laurenz Albe]

On Fri, 2025-04-18 at 07:31 +, sivapostg...@yahoo.com wrote:
> For the second part, how to identify that culprit query?

If the query caused an error, and you left "log_min_messages" at the
default setting, the error and the statement that caused it should
be in the PostgreSQL log.

Yours,
Laurenz Albe

Re: Why is an error not thrown when the time exceeds the lock timeout for an ExclusiveLock on a transaction ?

[Martin Gainty]

display server side postgresql.conf varables from postgresql.conf
Server-Side Timeout Configuration:

  1.
1. 
statement_timeout:
  2.
2. 
lock_timeout:
  3.
3. 
idle_in_transaction_session_timeout:

Then dump the client-side timeout configuration thru Node.js code

Node.js
client.query(sql, { timeout:  }).
2. Wrap Queries with a try...catch Block:

  4.
something like this should work thru javascript Node.js
const { Pool } = require('pg');

const pool = new Pool({
connectionString: 'your_connection_string'
});

async function executeQuery(sql) {
const client = await pool.connect();
try {
const result = await client.query(sql, { timeout: 5000 }); // Timeout after 5 
seconds
return result;
} catch (err) {
console.error('Error executing query:', err);
throw err; // Re-throw to propagate the error
} finally {
client.release();
}
}

if none of the server timeout parameters or client side timeout debug 
statements help you identify
you may need to install pg_timeout extension to Postgres
https://github.com/pierreforstmann/pg_timeout

HTH


From: Mansky, Edmund J. (GSFC-671.0)[ADNET Affiliate] 
Sent: Friday, April 18, 2025 1:28 PM
To: pgsql-gene...@postgresql.org 
Cc: Mansky, Edmund J. (GSFC-671.0)[ADNET Affiliate] 
Subject: Why is an error not thrown when the time exceeds the lock timeout for 
an ExclusiveLock on a transaction ?


Hi Everyone,



I have an SQL that updates a table in our database. The SQL in question is:



UPDATE public.sum_partn_alloc AS T1 SET effective_date = 
to_char(CURRENT_TIMESTAMP + interval '6 days', 'MMDDHH24MI') FROM 
public.sum_main AS T2

WHERE T1.status != 8 AND (T1.effective_date = '0' OR CURRENT_TIMESTAMP 
+ interval '3 days' >  to_timestamp(T1.effective_date, 'MMDDHH24MI'))

AND T1.ds_index IN (1741052236) AND T1.ds_index = T2.ds_index AND 
T2.online_status = 'Y'



where the number in BOLD in the list can be just a single number, or a long 
list of numbers (20-50 or so).



Normally, this SQL is executed without problem thousands of times a day.



Then, we see at certain times when that SQL exceeds the timeout for the 
ShareLock on the transaction. No error is thrown from the DB and processing on 
the driving Python script stops.

I've seen this in the case of a single number in the list, or a long list of 
20-50 numbers or so.



I see in the log at that point : process 683860 still waiting for ShareLock on 
transaction 492777941 after 1000.140 ms

  while updating tuple (1282991,25) 
in relation "sum_partn_alloc"



The Postgres server (12.22), running on RHEL 8.10 is configured with a default 
lock timeout of 1 sec.



Why is Postgres not throwing an error when the ShareLock time has exceeded 1 
sec. ?



The Python script driving this SQL never gets an error from Postgres in it's 
exception block, and hence the script simply stops at that point and processing 
stops.



Are there other parameters one can set in the configuration that would result 
in the Python script getting an error from Postgres so that this case can be 
handled and processing continues ?



Any ideas or tips would be greatly appreciated.



Thanks,



--Ed













[signature_1379146699]



Ed Mansky

Software Engineer

SDAC / VSO

NASA Goddard Space Flight Center

ADNET SYSTEMS, Inc.

8800 Greenbelt Rd, Greenbelt MD 20771

edmund.j.man...@nasa.gov

Re: Why is an error not thrown when the time exceeds the lock timeout for an ExclusiveLock on a transaction ?

[Tom Lane]

"Mansky, Edmund J. (GSFC-671.0)[ADNET Affiliate]"  
writes:
> I see in the log at that point : process 683860 still waiting for ShareLock 
> on transaction 492777941 after 1000.140 ms
>   while updating tuple 
> (1282991,25) in relation "sum_partn_alloc"

> The Postgres server (12.22), running on RHEL 8.10 is configured with a 
> default lock timeout of 1 sec.

> Why is Postgres not throwing an error when the ShareLock time has exceeded 1 
> sec. ?

I think you have misread the description of deadlock_timeout: it is
the lock wait time after which we check to see if there's a deadlock.
If there's not, we just log the above message (if configured to do so)
and keep waiting.

If you want to fail after X amount of time, lock_timeout or perhaps
statement_timeout is what to set for that.

regards, tom lane

Re: Cannot turn track_counts on

[Adrian Klaver]

On 4/17/25 15:25, Anton Shepelev wrote:

Adrian Klaver to Anton Shepelev:



Yet that is what we get for `pg_config --version' on the
affected system.  A nearly identical error message seems to
come up while installing psycopg2 -- a Python library for
Postgres, e.g.:





I have not the slightest idea why pg_config should print
this message, unless it is not a genuine pg_config.  I will
be checking its binary against the one that works as expected
on our reference system.


Found it. It is coming from the Debian postgresql-common packaging.

/usr/bin/pg_config  is  wrapper that contains:

#!/bin/sh

# If postgresql-server-dev-* is installed, call pg_config from the latest
# available one. Otherwise fall back to libpq-dev's version.
#
# (C) 2011 Martin Pitt 
# (C) 2014-2018 Christoph Berg 
#
#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.
#
#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.

set -e
PGBINROOT="/usr/lib/postgresql/"
#redhat# PGBINROOT="/usr/pgsql-"
LATEST_SERVER_DEV=`ls -v $PGBINROOT*/bin/pg_config 2>/dev/null|tail -n1`

if [ -n "$LATEST_SERVER_DEV" ]; then
exec "$LATEST_SERVER_DEV" "$@"
else
if [ -x /usr/bin/pg_config.libpq-dev ]; then
exec /usr/bin/pg_config.libpq-dev "$@"
else
echo "You need to install postgresql-server-dev-NN for building 
a server-side extension or libpq-dev for building a client-side 
application." >&2

exit 1
fi
fi


The message is telling you that on the machine you ran pg_config you 
have not installed either:


postgresql-server-dev-X where X is a major version

or

libpq-dev


If you want to get information from pg_config you will need to install 
one or the other. I would suggest libpq-dev.






--
Adrian Klaver
adrian.kla...@aklaver.com