Wal Files not removing automatically

[Devvrat Mittal]

Hello,

I have 3 nodes on which i have configured repmgr on 1st node primary, on
2nd standby and on 3rd witness. I have also configured pgbackrest on the
primary and witness node.
I'm using fivetran as a replication slot. i have also set the parameters
which are:
wal_level = 'logical'
synchronous_commit = local
wal_log_hints = 'on'
archive_mode = on
max_wal_senders = 10
wal_keep_segments = 32
max_replication_slots = 10
hot_standby = on
wal_sender_timeout = 300s
wal_receiver_timeout = 300s
max_connections = 1000
shared_buffers = 15GB
effective_cache_size = 45GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 3932kB
min_wal_size = 2GB
max_wal_size = 8GB

And these parameters also set on the other sites too. Now the issue is the
wal files are removing automatically. And the restart_lsn is also not
changing automatically. But when i drop the replication slot and recreate
it. After i create the replication slot the restart_lsn change but after
that i face the same issue that restart_lsn is not changing. So i wanna
know is it kind of bug or it's a issue from from database side?

Re: Upsert error "column reference is ambiguous"

[Tim Starling]

On 28/4/25 20:54, Tom Lane wrote:

Even if I were on board with arbitrarily adopting one of the two
possible interpretations, it's far from obvious to me that most people
would agree that "v" should mean the value from the existing row,
rather than the new value.  Better to make them say which they want.


OK sure, no way to tell, but if every other DBMS does it the same way 
then that might be a hint.


Also, I'm just saying, the upsert feature is fully useless to me with 
this name resolution policy.


In the single-row case, there's no need for EXCLUDED at all, because 
the client knows everything about the excluded row. Recall my example:


INSERT INTO t VALUES (1,1) ON CONFLICT (k) DO UPDATE SET v=v+1;

If I meant SET v=EXCLUDED.v+1 I would have just written v=2. The 
default policy (in other DBMSes) follows by analogy from the 
single-row case.


-- Tim Starling

Re: Upsert error "column reference is ambiguous"

[David G. Johnston]

On Monday, April 28, 2025, Tom Lane  wrote:

>
> AFAIK, "ON CONFLICT" is a Postgres-ism.  Exactly which constructs
> in exactly which other databases are you citing as precedent?
>

I confirmed the SQLite reference from the original email.

“The upsert above inserts the new vocabulary word "jovial" if that word is
not already in the dictionary, or if it is already in the dictionary, it
increments the counter. The "count+1" expression could also be written as
"vocabulary.count". PostgreSQL requires the second form, but SQLite accepts
either.”

https://sqlite.org/lang_upsert.html

David J.

Re: Fwd: SQL transactions executing from VMSS

[Adrian Klaver]

On 4/28/25 01:25, chandan Kumar wrote:

Hello Team,
I did not get any answer till now.  If someone has any idea please help 
me is getting this resolved. The issue is - database deployment , sql 
query to database is slow when we run database script from vmss to 
postgres database in flexible server.  both the servers are in US EAST 
under the same subnet


1) Unravel the acronyms. I'm pretty sure  VMSS and Flexible server are 
referring to Azure services, but it would be better if that was 
explicitly stated.


2) Show your work. In other words include the query plan, database 
script and the dynatrace results in your response.


3) What is "If i run single sql transaction from pg admin
it is executing in same time." trying to say? The same query as you 
refer to earlier and how does a single query relate to the issue of 
running transactions in batches?


My guess is that the Flexible Server(PaaS) is imposing more of an 
overhead then running your own Postgres instance on IaaS(Azure?).




-- Forwarded message -
From: *chandan Kumar* >

Date: Tue, Apr 22, 2025 at 4:59 PM
Subject: SQL transactions executing from VMSS
To: >




Hi Team,
Greetings!

Kindly help in below situation, where in I see slowness in response time 
while I do testing running SQL transaction in batches from VMSS to 
postgres database in flexible server(PaaS) 14.17 version. VMSS to 
IaaS(postgres db on ubuntu) is faster than VMSs to PaaS. 14 version is 
same on both the system. response size is same but response time is 
almost 2.5x more on PaaS. I see dynatrace is showing more time is on 
waiting time. Query plan is same on both the system. If i run single sql 
transaction from pg admin it is executing in same time.

  what can cause this slowness, any idea please.
--
*With warm regards*
* Chandan*


--
*With warm regards*
* Chandan*


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

Re: Fwd: SQL transactions executing from VMSS

[Laurenz Albe]

On Mon, 2025-04-28 at 13:55 +0530, chandan Kumar wrote:
> I did not get any answer till now.  If someone has any idea please help me is 
> getting this resolved.
> The issue is - database deployment , sql query to database is slow when we 
> run database script from
> vmss to postgres database in flexible server.  both the servers are in US 
> EAST under the same subnet
> 
> Kindly help in below situation, where in I see slowness in response time 
> while I do testing running
> SQL transaction in batches from VMSS to postgres database in flexible 
> server(PaaS) 14.17 version.
> VMSS to IaaS(postgres db on ubuntu) is faster than VMSs to PaaS. 14 version 
> is same on both the
> system. response size is same but response time is almost 2.5x more on PaaS. 
> I see dynatrace is
> showing more time is on waiting time. Query plan is same on both the system. 
> If i run single sql
> transaction from pg admin it is executing in same time.
> what can cause this slowness, any idea please.

I think that the answer you didn't get was as helpful as your problem 
description:

  "I got some statement that is slow, but I will neither tell you the statement 
nor
   show you the execution plan.  What is the cause of the slowness?"

Yours,
Laurenz Albe

Re: Upsert error "column reference is ambiguous"

[Laurenz Albe]

On Mon, 2025-04-28 at 21:22 +1000, Tim Starling wrote:
> On 28/4/25 20:54, Tom Lane wrote:
> > Even if I were on board with arbitrarily adopting one of the two
> > possible interpretations, it's far from obvious to me that most people
> > would agree that "v" should mean the value from the existing row,
> > rather than the new value.  Better to make them say which they want.
> 
> OK sure, no way to tell, but if every other DBMS does it the same way 
> then that might be a hint.

Which DBMS that supports INSERT .. ON CONFLICT do you have in mind?

> Also, I'm just saying, the upsert feature is fully useless to me with 
> this name resolution policy.

Because you cannot write EXCLUDED?

> In the single-row case, there's no need for EXCLUDED at all, because 
> the client knows everything about the excluded row. Recall my example:
> 
> INSERT INTO t VALUES (1,1) ON CONFLICT (k) DO UPDATE SET v=v+1;
> 
> If I meant SET v=EXCLUDED.v+1 I would have just written v=2. The 
> default policy (in other DBMSes) follows by analogy from the 
> single-row case.

Actually, for many people, the DWIM would be the other way around:

INSERT INTO tab (col)
SELECT something FROM othertab
ON CONFLICT (id)
/* "col" should get set to "something" */
DO UPDATE SET col = col;

I can follow your reasoning above, but if the SQL parser tried to
guess the user's intention like that, it is likely to go wrong
sometimes.  As Tom said, better force the user to be explicit.

Yours,
Laurenz Albe

Re: Upsert error "column reference is ambiguous"

[Peter Geoghegan]

On Mon, Apr 28, 2025 at 12:56 AM Tim Starling  wrote:
> Our application has an upsert method which takes the assignment
> "v=v+1" as a string. It is feasible to split it on the equals sign
> into the destination field and expression components, but it is not
> feasible to parse the expression or to require callers to supply an
> AST tree for the expressions they give us. It is not feasible to
> require callers to prefix all field names with the table name.

You can use an alias for the target table name. Is it feasible to
require callers to prefix all field names with a generic table name
alias?

-- 
Peter Geoghegan

Re: Upsert error "column reference is ambiguous"

[Tom Lane]

Tim Starling  writes:
> On 28/4/25 20:54, Tom Lane wrote:
>> Even if I were on board with arbitrarily adopting one of the two
>> possible interpretations, it's far from obvious to me that most people
>> would agree that "v" should mean the value from the existing row,
>> rather than the new value.  Better to make them say which they want.

> OK sure, no way to tell, but if every other DBMS does it the same way 
> then that might be a hint.

AFAIK, "ON CONFLICT" is a Postgres-ism.  Exactly which constructs
in exactly which other databases are you citing as precedent?

> In the single-row case, there's no need for EXCLUDED at all, because 
> the client knows everything about the excluded row.

Laurenz already provided the counter-example of an INSERT/SELECT,
but there's also the possibility of the INSERT supplying a computed
default value for a column, e.g., CURRENT_TIMESTAMP.  So you won't
get far with that argument.

I do actually have some sympathy for your proposal after thinking
about it a bit more, but the argument I would use is "the behavior
of the ON CONFLICT UPDATE SET list should be as much as possible like
the behavior of an ordinary UPDATE's SET list".  Since "v = v+1" would
refer to the existing row's "v" in regular UPDATE, it's sensible to
let that happen here too.  Of course the counter-argument is that this
should be compared not to a trivial UPDATE, but an "UPDATE ... FROM
othertable" where the othertable supplies some conflicting column
name(s).  In that situation we're going to make you resolve the
conflict by qualifying the column names.  The only thing that makes
that not a precise parallel is that EXCLUDED is not something the user
wrote into the query explicitly, so there's no opportunity to
substitute different column aliases, as a FROM clause would allow.
Perhaps that justifies demoting it to second-class citizenship whereby
EXCLUDED has to be qualified but the target table doesn't.  (I don't
find this argument hugely compelling, but it's an argument.)

BTW, I did wonder how hard it would be to make such a change.
On first glance it seems to be a one-liner:

diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index 1f4d6adda52..f11727adbaa 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -1306,7 +1306,7 @@ transformOnConflictClause(ParseState *pstate,
 * Add the EXCLUDED pseudo relation to the query namespace, 
making it
 * available in the UPDATE subexpressions.
 */
-   addNSItemToQuery(pstate, exclNSItem, false, true, true);
+   addNSItemToQuery(pstate, exclNSItem, false, true, false);
 
/*
 * Now transform the UPDATE subexpressions.

So this isn't about implementation difficulty but about whether
we think it's a good idea.

regards, tom lane

Re: Upsert error "column reference is ambiguous"

[Tim Starling]

On 28/4/25 23:54, Tom Lane wrote:

AFAIK, "ON CONFLICT" is a Postgres-ism.  Exactly which constructs
in exactly which other databases are you citing as precedent?


There's a list here:



Since that page was written in 2014, SQLite added upsert support, 
consciously following PG's syntax, except that unqualified names 
resolve to target rows.


My code would be like

function upsert( $table, $names, $values, $key, $set ) {
if ( $this->type === 'mysql' ) {
$conflict = 'ON DUPLICATE KEY UPDATE';
} else {
$conflict = "ON CONFLICT ($key) DO UPDATE SET";
}
return $this->query( "INSERT INTO $table ($names) " .
"VALUES ($values) $conflict $set" );
}

The parameters are a little bit more structured than that, but that 
gives you the idea.


MediaWiki has supported MySQL's ON DUPLICATE KEY UPDATE since 2013, 
and we've always had the conflict target parameter $key since then as 
a helper for emulation. So it's trivial to produce either MySQL and 
SQLite syntax.


-- Tim Starling

Re: Upsert error "column reference is ambiguous"

[Christophe Pettus]

> On Apr 28, 2025, at 15:58, Christophe Pettus  wrote:
> It does require knowing which of the VALUES is the key value being inserted 
> (pseudocode syntax above) [...]

The instant after I hit send, I realized that information is available to the 
function by lining up the $names and $values array, since the name of the key 
column is passed in.

Re: Upsert error "column reference is ambiguous"

[Christophe Pettus]

> On Apr 28, 2025, at 15:36, Tim Starling  wrote:
> function upsert( $table, $names, $values, $key, $set ) {
>if ( $this->type === 'mysql' ) {
>$conflict = 'ON DUPLICATE KEY UPDATE';
>} else {
>$conflict = "ON CONFLICT ($key) DO UPDATE SET";
>}
>return $this->query( "INSERT INTO $table ($names) " .
>"VALUES ($values) $conflict $set" );

I'll mention that you can do this without ON CONFLICT in PostgreSQL in a way 
that, while not nearly as clean as ON CONFLICT, isn't a huge hack, either:

"DO $$ BEGIN INSERT INTO $table($names) VALUES($values); EXCEPTION WHEN 
integrity_constraint_violation THEN UPDATE $table SET $set WHERE 
$key=$values[0]; END; $$ LANGUAGE plpgsql;"

It does require knowing which of the VALUES is the key value being inserted 
(pseudocode syntax above), but if that is stylized to always be the first 
value, that does not seem insurmountable.

Re: Upsert error "column reference is ambiguous"

[Tim Starling]

On 28/4/25 23:30, Peter Geoghegan wrote:

You can use an alias for the target table name. Is it feasible to
require callers to prefix all field names with a generic table name
alias?


No, primarily because MySQL does not support such an alias.

-- Tim Starling

Fwd: SQL transactions executing from VMSS

[chandan Kumar]

Hello Team,
I did not get any answer till now.  If someone has any idea please help me
is getting this resolved. The issue is - database deployment , sql query to
database is slow when we run database script from vmss to postgres
database in flexible server.  both the servers are in US EAST under the
same subnet

-- Forwarded message -
From: chandan Kumar 
Date: Tue, Apr 22, 2025 at 4:59 PM
Subject: SQL transactions executing from VMSS
To: 



Hi Team,
Greetings!

Kindly help in below situation, where in I see slowness in response time
while I do testing running SQL transaction in batches from VMSS to postgres
database in flexible server(PaaS) 14.17 version. VMSS to IaaS(postgres db
on ubuntu) is faster than VMSs to PaaS. 14 version is same on both the
system. response size is same but response time is almost 2.5x more on
PaaS. I see dynatrace is showing more time is on waiting time. Query plan
is same on both the system. If i run single sql transaction from pg admin
it is executing in same time.
 what can cause this slowness, any idea please.
-- 
*With warm regards*
* Chandan*


-- 
*With warm regards*
* Chandan*

Re: Upsert error "column reference is ambiguous"

[Tom Lane]

Tim Starling  writes:
> Regarding upsert syntax.
> => INSERT INTO t VALUES (1,1) ON CONFLICT (k) DO UPDATE SET v=v+1;
> ERROR:  column reference "v" is ambiguous

> Please convince me that this is not a bug.

It's not a bug.

> If I understand correctly, in the expression "v+1", both EXCLUDED.v 
> and t.v are present as the unqualified name "v". This is always the 
> case and it is never possible to reference an unqualified field name 
> in the expression of a conflict action.

Correct: it's not clear whether you mean to use "v" from the new
desired-to-be-inserted row or "v" from the existing row.

> Thus, any query with an unqualified name is statically known to be 
> invalid. It is not a b/c break to remove EXCLUDED.v from the list of 
> unqualified fields in a new major release of PG, thus allowing it to DWIM.

Even if I were on board with arbitrarily adopting one of the two
possible interpretations, it's far from obvious to me that most people
would agree that "v" should mean the value from the existing row,
rather than the new value.  Better to make them say which they want.

regards, tom lane

Re: Fwd: SQL transactions executing from VMSS

[Tom Lane]

Laurenz Albe  writes:
> I think that the answer you didn't get was as helpful as your problem 
> description:
>   "I got some statement that is slow, but I will neither tell you the 
> statement nor
>show you the execution plan.  What is the cause of the slowness?"

The OP did say that the execution plan was the same on both servers.
That would appear to imply that the problem is in the client-side
software stack.  Unfortunately, this is not the place to come for
expertise in VMSS (I for one have no idea what that even is).
Suggest asking in whatever community exists around that software.

regards, tom lane

Re: shared buffers

[Marc Millas]

I didnt know this.
thanks,

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Sat, Apr 26, 2025 at 12:46 AM Laurenz Albe 
wrote:

> On Fri, 2025-04-25 at 15:42 +0200, Marc Millas wrote:
> > got something strange to me:
> > Same db ie. same data, around 1.2TB,one on pg13, one on pg16
> > same 16 GB of shared_buffers,
> > I am the single user.
> > both have track_io_timing on
> >
> > on pg13, if I run a big request with explain (analyze,buffers),
> > I see around 6 GB read
> > if I do rerun the very same request, no more read(s), all data in the
> shared buffers cache. fine
> > If I check with pg_buffercache what's in it, I see the biggest tables of
> my request within
> > the biggest users (in number of blocks used). All this is fine.
> >
> > next, if I do the very same on the pg16 machine, whatever the number of
> times I rerun the
> > explain (analyze, buffers) of the same request, each time, the explain
> shows the same volume
> > of reads. again and again.
> > If I check with pg_buffercache, the set of objects stay the same,
> WITHOUT the objects of my
> > request, just like if those objects where sticky.
>
> I can't see the plans, so I can only guess.
>
> Perhaps the v16 plan uses a sequential scan on a table that is more than a
> quarter of
> shared_buffers in size, so that PostgreSQL uses a ring buffer to read it
> instead of
> blowing out more than a quarter of its buffer cache.
>
> Yours,
> Laurenz Albe
>

Re: Wal Files not removing automatically

[Tom Lane]

Devvrat Mittal  writes:
> And these parameters also set on the other sites too. Now the issue is the
> wal files are removing automatically. And the restart_lsn is also not
> changing automatically. But when i drop the replication slot and recreate
> it. After i create the replication slot the restart_lsn change but after
> that i face the same issue that restart_lsn is not changing. So i wanna
> know is it kind of bug or it's a issue from from database side?

The entire point of a replication slot is that it prevents WAL from
being discarded until whatever is supposed to read from the slot
has consumed that WAL.  So apparently nothing is reading from that
slot, which means there is something wrong with the way
you've connected up your replication setup.  I would have thought
you'd be using slots to feed your standby and witness servers
(and I don't understand why you don't have two slots, one for each).

regards, tom lane

Re: Upsert error "column reference is ambiguous"

[Laurenz Albe]

On Tue, 2025-04-29 at 08:36 +1000, Tim Starling wrote:
> My code would be like
> 
> function upsert( $table, $names, $values, $key, $set ) {
>  if ( $this->type === 'mysql' ) {
>  $conflict = 'ON DUPLICATE KEY UPDATE';
>  } else {
>  $conflict = "ON CONFLICT ($key) DO UPDATE SET";
>  }
>  return $this->query( "INSERT INTO $table ($names) " .
>  "VALUES ($values) $conflict $set" );
> }
> 
> The parameters are a little bit more structured than that, but that 
> gives you the idea.

Another litle "if" to cater for PostgreSQL's "EXCLUDED." would be
such a big problem?

Yours,
Laurenz Albe