Re: Turn jit off for slow subquery in Postgres 12

[Thomas Kellerer]

Andrus schrieb am 16.02.2021 um 07:48:
> Last year I posted testcase [1] which shows that using jit makes execution
> magnitude slower ( 2.5s vs 0.4 sec) in typical shopping cart application 
> product search in Postgres 12.
>
> There are also other reports on this [2,3].
>
> I tried to turn jit off for subquery using
>
> select ...  from
> (
> set jit to off;
> select ... from cartdata, ...  where 
> set jit to on
> )
>
> But this cause syntax error.
>
> How to turn off jit for specific subquery in Postgres 12?

You can't.

You can only turn off JIT for the whole query (or the session).

In my experience, in OLTP type environments, JIT never improves a query.
We have turned it off globally.

Turn jit off for slow subquery in Postgres 12

[Andrus]

Hi!

Last year I posted testcase [1] which shows that using jit makes execution
magnitude slower ( 2.5s vs 0.4 sec) in typical shopping cart application 
product search in Postgres 12.


There are also other reports on this [2,3].

I tried to turn jit off for subquery using

select ...  from
(
set jit to off;
select ... from cartdata, ...  where 
set jit to on
)

But this cause syntax error.

How to turn off jit for specific subquery in Postgres 12 ?

Subquery is generated by EF Core and splitting it to multiple statements 
is not possible.


Postgres upgrade is not planned.

Andrus.

[1] 
https://www.postgresql.org/message-id/A2E2572094D4434CAEB57C80085B22C7@dell2
[2] 
https://www.postgresql.org/message-id/CAHOFxGo5xJt02RmwAWrtv2K0jcqqxG-cDiR8FQbvb0WxdKhcgw%40mail.gmail.com

[3]https://www.postgresql.org/message-id/629715.1595630222%40sss.pgh.pa.us

Re: ALTER ROLE ... SET in current database only

[Abdul Qoyyuum]

Wouldnt you need to connect to the database first before you can ALTER ROLE
anything?

On Tue, Feb 16, 2021 at 1:25 AM Wolfgang Walther 
wrote:

> Hi,
>
> I'm trying to set a GUC for a role in the current database only - but
> don't know the name of the database at the time of writing code. Could
> be development, staging, ...
>
> I would basically like to do something like this:
>
> ALTER ROLE a IN CURRENT DATABASE SET b TO c;
>
> Although that syntax doesn't exist (yet).
>
> I think I could wrap it in a DO block and create the statement
> dynamically. Alternatively, I could probably INSERT INTO / UPDATE
> pg_db_role_setting manually?
>
> Any other ideas how to achieve this easily? Does the proposed "IN
> CURRENT DATABASE" syntax sound useful to anyone else?
>
> Best,
>
> Wolfgang
>
>
>

-- 
Abdul Qoyyuum Bin Haji Abdul Kadir
HP No: +673 720 8043

Re: Replication sequence

[Paolo Saudin]

Il giorno lun 15 feb 2021 alle ore 20:40 Loles  ha
scritto:

> One thing is the number of records in the table and another is the current
> value of the sequence.
>
> Execute:
>
> select max (field) from bobo.menu_pages;
>
> In both master and slave server.
>
> What give you back?
>
> "field" is the data that belongs to the sequence.
>
>
>
> El lun., 15 feb. 2021 18:55, Paolo Saudin 
> escribió:
>
>> Hi all,
>> I have two servers, a primary and a secondary one with a streaming
>> replica setup.
>> Today I noticed that some sequences are not lined-up, the replica ones
>> are well ahead, while the records number is the same. How is it possible?
>>
>> Thanks,
>> Paolo
>>
>> --
>> -- primary server
>> --
>> postgres@db-srv1:~$ psql
>> psql (9.5.19)
>> cf-db-srv1:5432 postgres@rete_llpp=# SELECT last_value FROM
>> bobo.menu_pages_mp_id_seq;
>> ┌┐
>> │ last_value │
>> ├┤
>> │ 74 │
>> └┘
>>   cf-db-srv1:5432 postgres@rete_llpp=# select count(*) from
>> bobo.menu_pages;
>> ┌───┐
>> │ count │
>> ├───┤
>> │74 │
>> └───┘
>>
>> --
>> -- replica server
>> --
>> postgres@db-srv2:~$ psql
>> psql (9.5.24)
>> cf-db-srv2:5433 postgres@rete_llpp=# SELECT last_value FROM
>> bobo.menu_pages_mp_id_seq;
>> ┌┐
>> │ last_value │
>> ├┤
>> │105 │
>> └┘
>> cf-db-srv2:5433 postgres@rete_llpp=# select count(*) from
>> bobo.menu_pages;
>> ┌───┐
>> │ count │
>> ├───┤
>> │74 │
>> └───┘
>>
>>
Hi, I checked and the number of records are identical,

cf-db-srv1:5432 postgres@rete_llpp=# select max(mp_id) from bobo.menu_pages;
┌─┐
│ max │
├─┤
│  74 │
└─┘

cf-db-srv2:5433 postgres@rete_llpp=# select max(mp_id) from bobo.menu_pages;
┌─┐
│ max │
├─┤
│  74 │
└─┘
Thank you,
Paolo

Re: Set a specific database to log_statement='ddl' but others to be log_statement='all'

[Abdul Qoyyuum]

Thanks David. I tried that and this is what I get:

Feb 16 11:27:23 db1 postgres[27675]: [3595-1] etx_ecom>
LOG:  connection authorized: user=username database=etx_ecom
Feb 16 11:27:23 db1 postgres[26184]: [3598-1] core> LOG:
 disconnection: session time: 0:00:47.727 user=username database=core
host=[local]
Feb 16 11:27:31 db1 postgres[27675]: [3596-1] etx_ecom>
LOG:  statement: show log_statement;
Feb 16 11:27:46 db1 postgres[27675]: [3597-1] etx_ecom>
LOG:  statement: ALTER DATABASE etx_ecom SET log_statement='ddl';
Feb 16 11:28:54 db1 postgres[30323]: [3595-1] core> LOG:
 connection authorized: user=username database=core
Feb 16 11:28:54 db1 postgres[27675]: [3598-1] etx_ecom>
LOG:  disconnection: session time: 0:01:31.052 user=username
database=etx_ecom host=[local]
Feb 16 11:29:45 db1 postgres[31998]: [3595-1] etx_ecom>
LOG:  connection authorized: user=username database=etx_ecom
Feb 16 11:29:45 db1 postgres[30323]: [3596-1] core> LOG:
 disconnection: session time: 0:00:51.028 user=username database=core
host=[local]
Feb 16 11:29:56 db1 postgres[31998]: [3596-1] etx_ecom>
LOG:  statement: show log_statement;
Feb 16 11:30:05 db1 postgres[31998]: [3597-1] etx_ecom>
LOG:  statement: select * from card_type;
Feb 16 11:30:28 db1 postgres[31998]: [3598-1] etx_ecom>
LOG:  statement: INSERT INTO card_type VALUES('Z', 'QTEST');
Feb 16 11:30:57 db1 postgres[31998]: [3599-1] etx_ecom>
LOG:  statement: DELETE from card_type where type='Q' or type='Z';

Correction on the earlier statement about postgresql.conf. The
log_statement is configured inside postgresql_puppet_extras.conf. Not sure
if that makes any difference. The reason for this endeavor is to run a
simple SQL command in puppet to ALTER the database and log_statement
changed to 'ddl' for specific databases but somehow doesn't work. Hence the
short and small manual test to see.

Abdul Qoyyuum Bin Haji Abdul Kadir
System Engineer at Card Access Services
HP: +673 720 8043

On Tue, 16 Feb 2021, 11:23 David G. Johnston, 
wrote:

> On Monday, February 15, 2021, Abdul Qoyyuum 
> wrote:
>
>> Hi all,
>>
>> I have a Postgresql cluster with master and multiple slaves running on
>> version 9.6. I'm trying to adjust the log_statement from all to ddl on
>> specific databases (i.e. postgresql.conf has log_statement='all' but I need
>> a couple of databases set to log_statement='ddl').
>>
>> etx_ecom=# ALTER DATABASE etx_ecom SET log_statement='ddl';
>> ALTER DATABASE
>> etx_ecom=# show log_statement;
>>  log_statement
>> ---
>>  all
>> (1 row)
>>
>> etx_ecom=# INSERT into card_type VALUES('Q','TEST');
>> INSERT 0 1
>>
>> etx_ecom=#
>>
>> Feb 16 10:56:11 db1 postgres[21682]: [3602-1] etx_ecom>
>> LOG:  statement: INSERT into card_type VALUES('Q','TEST');
>>
>> Why doesn't the Alter Database work?
>>
>>
> It did - you just didn’t start a new session as the documentation
> directs.  You only altered a default that is only considered during user
> sign-in.
>
> David J.
>
>

Re: MultiXactMemberControlLock contention on a replica

[Laurenz Albe]

On Mon, 2021-02-15 at 12:40 -0800, Christophe Pettus wrote:
> > On Feb 15, 2021, at 08:15, Laurenz Albe  wrote:
> > Right.  I cannot think of any other reason, given that the standby only
> > allows reading.  It's just an "xmax", and PostgreSQL needs to read the
> > multixact to figure out if it can see the row or not.
> 
> 
> OK, I think I see the scenario: A very large number of sessions on the 
> primary all
>  touch or create rows which refer to a particular row in another table by 
> foreign
>  key, but they don't modify that row.  A lot of sessions on the secondary all 
> read
>  the row in the referred-to table, so it has to get all the members of the 
> multixact,
>  and if the multixact structure has spilled to disk, that gets very expensive.

You also get a multixact if you run something like

BEGIN;
SELECT ... FROM tab WHERE id = 42 FOR UPDATE;
SAVEPOINT a;
UPDATE tab SET ... WHERE id = 42;
ROLLBACK;

The multixact is also created if you commit, but it won't be visible.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Set a specific database to log_statement='ddl' but others to be log_statement='all'

[David G. Johnston]

On Monday, February 15, 2021, Abdul Qoyyuum 
wrote:

> Hi all,
>
> I have a Postgresql cluster with master and multiple slaves running on
> version 9.6. I'm trying to adjust the log_statement from all to ddl on
> specific databases (i.e. postgresql.conf has log_statement='all' but I need
> a couple of databases set to log_statement='ddl').
>
> etx_ecom=# ALTER DATABASE etx_ecom SET log_statement='ddl';
> ALTER DATABASE
> etx_ecom=# show log_statement;
>  log_statement
> ---
>  all
> (1 row)
>
> etx_ecom=# INSERT into card_type VALUES('Q','TEST');
> INSERT 0 1
>
> etx_ecom=#
>
> Feb 16 10:56:11 db1 postgres[21682]: [3602-1] etx_ecom>
> LOG:  statement: INSERT into card_type VALUES('Q','TEST');
>
> Why doesn't the Alter Database work?
>
>
It did - you just didn’t start a new session as the documentation directs.
You only altered a default that is only considered during user sign-in.

David J.

Re: Why is Postgres only using 8 cores for partitioned count? [Parallel Append]

[David Rowley]

On Tue, 16 Feb 2021 at 02:12, Fabio Pardi  wrote:
>
> On 14/02/2021 22:16, Gavin Flower wrote:
> > While I agree it might be good to be able specify the number of workers, 
> > sure it would be possible to derive a suitable default based on the number 
> > of effective processors available?
>
> I had the same problem and my conclusion was that it is not possible to go 
> above 8 cores because of Amdahl's law on parallel computing. More here: 
> https://en.wikipedia.org/wiki/Amdahl%27s_law

That would really depend on what the non-parallel part of the equation
was.  There are some plan shapes such as GROUP BY or aggregate queries
with very few or just 1 group where the serial portion of the
execution is very small indeed.

David

Re: Why is Postgres only using 8 cores for partitioned count? [Parallel Append]

[David Rowley]

On Mon, 15 Feb 2021 at 10:16, Gavin Flower
 wrote:
> Just wondering why there is a hard coded limit.

I don't see where the hardcoded limit is.  The total number is limited
to max_parallel_workers_per_gather, but there's nothing hardcoded
about the value of that.

> While I agree it might be good to be able specify the number of workers,
> sure it would be possible to derive a suitable default based on the
> number of effective processors available?

It's a pretty tricky thing to get right.  The problem is that
something has to rationalise the use of parallel worker processes.
Does it seem reasonable to you to use the sum of the Append child
parallel workers?  If so, I imagine someone else would think that
would be pretty insane. We do have to consider the fact that we're
trying to share those parallel worker processes with other backends
which also might want to get some use out of them.

As for if we need some rel option for partitioned tables.  I think
that's also tricky. Sure, we could go and add a "parallel_workers"
relopt to partitioned tables, but then that's going to be applied
regardless of how many partitions survive partition pruning.  There
could be as little as 2 subpaths in an Append, or the number could be
in the thousands. I can't imagine anyone really wants the same number
of parallel workers in each of those two cases.  So I can understand
why ab7271677 wanted to take into account the number of append
children.

Maybe there's some room for some other relopt that just changes the
behaviour of that code.  It does not seem too unreasonable that
someone might like to take the sum of the Append child parallel
workers.  That value would still be capped at
max_parallel_workers_per_gather, so it shouldn't ever go too insane
unless someone set that GUC to something insane, which would be their
choice.  I'm not too sure which such a relopt would be called.

Additionally, for the case being reported here.  Since all Append
children are foreign tables, there is actually some work going on to
make it so workers don't have to sit by and wait until the foreign
server returns the results.  I don't think anyone would disagree that
it's pretty poor use of a parallel worker to have it sit there doing
nothing for minutes at a time waiting for a single tuple from a
foreign data wrapper.  I'm not sure of the status of that work, but if
you want to learn more about it, please see [1]

David

[1] https://commitfest.postgresql.org/32/2491/

Set a specific database to log_statement='ddl' but others to be log_statement='all'

[Abdul Qoyyuum]

Hi all,

I have a Postgresql cluster with master and multiple slaves running on
version 9.6. I'm trying to adjust the log_statement from all to ddl on
specific databases (i.e. postgresql.conf has log_statement='all' but I need
a couple of databases set to log_statement='ddl').

etx_ecom=# ALTER DATABASE etx_ecom SET log_statement='ddl';
ALTER DATABASE
etx_ecom=# show log_statement;
 log_statement
---
 all
(1 row)

etx_ecom=# INSERT into card_type VALUES('Q','TEST');
INSERT 0 1

etx_ecom=#

Feb 16 10:56:11 db1 postgres[21682]: [3602-1] etx_ecom>
LOG:  statement: INSERT into card_type VALUES('Q','TEST');

Why doesn't the Alter Database work?

-- 
Abdul Qoyyuum Bin Haji Abdul Kadir
HP No: +673 720 8043

Re: How to post to this mailing list from a web based interface

[RaviKrishna]

> Nable didn't seem to work. I got a bounce iirc.

Not sure what you are doing?  My previous reply a day ago and this reply are
from Nable with no issues.



--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Issue initializing postgreSQL 13 on CentOS 7 docker

[H]

I am running postgreSQL 13 server/client successfully on my workstation but am 
having problems setting it up in a docker container, both running CentOS 7. I 
have previously run postgreSQL 9.6 successfully in docker but right now I am 
unable to initialize the database under pg 13. For some reason the following:

postgresql-13-setup initdb -E 'UTF8'

fails with:

systemctl: invalid option -- 'E'

The equivalent to the above worked fine when running pg 9.6.

I can add that:

postgresql-13-setup initdb

works fine but then the databases default to SQL ASCII which is not OK.

There seems to be some issue with Fedora and later pg versions with respect to 
locale settings as seen in:

https://bugzilla.redhat.com/show_bug.cgi?id=1872511

but I am not certain the same issue applies to my situation. The file 
glibc-langpack-en does not seem available for CentOS 7 although my solution 
might also rely on fixing the locale situation. Anyway, where would I file a 
bug? RedHat or here?

I'll be happy to supply more information as needed.

Re: checkpointer and other server processes crashing

[Adrian Klaver]

On 2/15/21 1:50 PM, Joe Abbate wrote:

On 15/2/21 16:29, Adrian Klaver wrote:

On 2/15/21 1:15 PM, Joe Abbate wrote:
We've been experiencing PG server process crashes about every other 
week on a mostly read only website (except for a single insert/update 
on page access).  Typical log entries look like


LOG:  checkpointer process (PID 11200) was terminated by signal 9: 
Killed

LOG:  terminating any other active server processes


Have you looked at the system logs to see if the OOM killer is involved?


No, I hadn't, but now I see that of the past three crashes, four 
postgres processes were OOM victims.  I guess I have to read the OOM 
articles I've been skipping (read one a long time ago).  If you have any 
specific suggestions, let me know.


There are suggestions here:

https://www.postgresql.org/docs/12/kernel-resources.html#LINUX-MEMORY-OVERCOMMIT

It probably would not hurt to figure why this seemed to happen with the 
Python -> Go switch. Even if you can get the OOM not to kick in, the 
fact that it was kicking in would indicate you now have memory hungry 
processes that did not exist before. The questions being is this 
strictly due to the language/framework change, natural growth of data 
set being worked with, or something else?




Thanks Adrian,

Joe



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

Re: [LDAPS] Test connection user with ldaps server

[Thomas Munro]

On Tue, Feb 16, 2021 at 4:32 AM Laurenz Albe  wrote:
> What I would do is experiment with the "ldapsearch" executable from OpenLDAP 
> and see
> if you can reproduce the problem from the command line.

Also, maybe try doing this as the "postgres" user (or whatever user
PostgreSQL runs as), just in case there are some environmental
differences affecting the behaviour.

Re: checkpointer and other server processes crashing

[Tim Cross]

Joe Abbate  writes:

> Hello,
>
> We've been experiencing PG server process crashes about every other week
> on a mostly read only website (except for a single insert/update on page
> access).  Typical log entries look like
>
> LOG:  checkpointer process (PID 11200) was terminated by signal 9: Killed
> LOG:  terminating any other active server processes
>
> Other than the checkpointer, the server process that was terminated was
> either doing a "BEGIN READ WRITE", a "COMMIT" or executing a specific
> SELECT.
>
> The database is always recovered within a second and everything else
> appears to resume normally.  We're not certain about what triggers this,
> but in several instances the web logs show an external bot issuing
> multiple HEAD requests on what is logically a single page.  The web
> server logs show "broken pipe" and EOF errors, and PG logs sometimes
> shows a number of "incomplete startup packet" messages before the
> termination message.
>
> This started roughly when the site was migrated to Go, whose web
> "processes" run as "goroutines", scheduled by Go's runtime (previously
> the site used Python and Gunicorn to serve the pages, which probably
> isolated the PG processes from a barrage of nearly simultaneous requests).
>
> As I understand it, the PG server processes doing a SELECT are spawned
> as children of the Go process, so presumably if a "goroutine" dies, the
> associated PG process would die too, but I'm not sure I grasp why that
> would cause a recovery/restart.  I also don't understand where the
> checkpointer process fits in the picture (and what would cause it to die).
>

A signal 9 typically means something is explicitly killing processes. I
would check your system logs in case something is killing processes due
to running out of some resource (like memory). If it is a fairly recent
Debian system, journalctl might be useful for checking.

--
Tim Cross

Re: checkpointer and other server processes crashing

[Adrian Klaver]

On 2/15/21 1:15 PM, Joe Abbate wrote:

Hello,

We've been experiencing PG server process crashes about every other week 
on a mostly read only website (except for a single insert/update on page 
access).  Typical log entries look like


LOG:  checkpointer process (PID 11200) was terminated by signal 9: Killed
LOG:  terminating any other active server processes


Have you looked at the system logs to see if the OOM killer is involved?



Other than the checkpointer, the server process that was terminated was 
either doing a "BEGIN READ WRITE", a "COMMIT" or executing a specific 
SELECT.


The database is always recovered within a second and everything else 
appears to resume normally.  We're not certain about what triggers this, 
but in several instances the web logs show an external bot issuing 
multiple HEAD requests on what is logically a single page.  The web 
server logs show "broken pipe" and EOF errors, and PG logs sometimes 
shows a number of "incomplete startup packet" messages before the 
termination message.


This started roughly when the site was migrated to Go, whose web 
"processes" run as "goroutines", scheduled by Go's runtime (previously 
the site used Python and Gunicorn to serve the pages, which probably 
isolated the PG processes from a barrage of nearly simultaneous requests).


As I understand it, the PG server processes doing a SELECT are spawned 
as children of the Go process, so presumably if a "goroutine" dies, the 
associated PG process would die too, but I'm not sure I grasp why that 
would cause a recovery/restart.  I also don't understand where the 
checkpointer process fits in the picture (and what would cause it to die).


For the record, this is on PG 11.9 running on Debian.

TIA,

Joe





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

checkpointer and other server processes crashing

[Joe Abbate]

Hello,

We've been experiencing PG server process crashes about every other week 
on a mostly read only website (except for a single insert/update on page 
access).  Typical log entries look like


LOG:  checkpointer process (PID 11200) was terminated by signal 9: Killed
LOG:  terminating any other active server processes

Other than the checkpointer, the server process that was terminated was 
either doing a "BEGIN READ WRITE", a "COMMIT" or executing a specific 
SELECT.


The database is always recovered within a second and everything else 
appears to resume normally.  We're not certain about what triggers this, 
but in several instances the web logs show an external bot issuing 
multiple HEAD requests on what is logically a single page.  The web 
server logs show "broken pipe" and EOF errors, and PG logs sometimes 
shows a number of "incomplete startup packet" messages before the 
termination message.


This started roughly when the site was migrated to Go, whose web 
"processes" run as "goroutines", scheduled by Go's runtime (previously 
the site used Python and Gunicorn to serve the pages, which probably 
isolated the PG processes from a barrage of nearly simultaneous requests).


As I understand it, the PG server processes doing a SELECT are spawned 
as children of the Go process, so presumably if a "goroutine" dies, the 
associated PG process would die too, but I'm not sure I grasp why that 
would cause a recovery/restart.  I also don't understand where the 
checkpointer process fits in the picture (and what would cause it to die).


For the record, this is on PG 11.9 running on Debian.

TIA,

Joe

Re: pg_stat_user_tables.n_mod_since_analyze persistence?

[Tom Lane]

Philip Semanchuk  writes:
> I saw some unexpected behavior that I'm trying to understand. I suspect it 
> might be a quirk specific to AWS Aurora and I'd like to confirm that.

> When I restart my local Postgres instance (on my Mac), the values in 
> pg_stat_user_tables.n_mod_since_analyze are preserved. In other words, if 
> table foo had n_mod_since_analyze=33 before the reboot, it still has 
> n_mod_since_analyze=33 after the restart. 

> When I restart an AWS Aurora instance, the values in 
> pg_stat_user_tables.n_mod_since_analyze all seem to be reset to 0. 

> Can anyone confirm (or refute) that the behavior I see on my Mac 
> (preservation of these values through a restart) is common & expected 
> behavior?

Yeah, in PG those stats would be preserved, at least as long as it's
a clean shutdown.

regards, tom lane

Re: MultiXactMemberControlLock contention on a replica

[Christophe Pettus]

> On Feb 15, 2021, at 08:15, Laurenz Albe  wrote:
> Right.  I cannot think of any other reason, given that the standby only
> allows reading.  It's just an "xmax", and PostgreSQL needs to read the
> multixact to figure out if it can see the row or not.

OK, I think I see the scenario: A very large number of sessions on the primary 
all touch or create rows which refer to a particular row in another table by 
foreign key, but they don't modify that row.  A lot of sessions on the 
secondary all read the row in the referred-to table, so it has to get all the 
members of the multixact, and if the multixact structure has spilled to disk, 
that gets very expensive.

--
-- Christophe Pettus
   x...@thebuild.com

Re: How to post to this mailing list from a web based interface

[Thomas Guyot]

On 2021-02-15 12:30, Adrian Klaver wrote:
> On 2/15/21 9:24 AM, Thomas Guyot wrote:
> 
>> The "download mbox" option doesn't work, I get asked for a user/password
>> every time (I would've downloaded archives for the lats two months to
>> get continuation on most threads).
> 
> The user/password is in the message in the prompt.

Ha, thanks! Turning on developer mode I could see it in the response
headers. It's a shame Chrome doesn't show it, yet I guess there may be a
good reason such as phishing sites trying to make the prompt more legit
while impersonating an organization...

Possible suggestions though - maybe allow cookie-based auth when logged
in, or show the user/password in the 401 error page.


I could get the archives this time, thanks!

--
Thomas

Re: Insert into on conflict, data size upto 3 billion records

[Rob Sargent]

On 2/15/21 12:22 PM, Karthik K wrote:

yes, I'm using \copy to load the batch table,

with the new design that we are doing, we expect updates to be less 
going forward and more inserts, one of the target columns I'm updating 
is indexed, so I will drop the index and try it out, also from your 
suggestion above splitting the on conflict into insert and update is 
performant but in order to split the record into batches( low, high) I 
need to do a count of primary key on the batch tables to first split it 
into batches



I don't think you need to do a count per se.  If you know the 
approximate range (or better, the min and max) in the incoming/batch 
data you can approximate the range values.

Re: Replication sequence

[Loles]

One thing is the number of records in the table and another is the current
value of the sequence.

Execute:

select max (field) from bobo.menu_pages;

In both master and slave server.

What give you back?

"field" is the data that belongs to the sequence.



El lun., 15 feb. 2021 18:55, Paolo Saudin  escribió:

> Hi all,
> I have two servers, a primary and a secondary one with a streaming replica
> setup.
> Today I noticed that some sequences are not lined-up, the replica ones are
> well ahead, while the records number is the same. How is it possible?
>
> Thanks,
> Paolo
>
> --
> -- primary server
> --
> postgres@db-srv1:~$ psql
> psql (9.5.19)
> cf-db-srv1:5432 postgres@rete_llpp=# SELECT last_value FROM
> bobo.menu_pages_mp_id_seq;
> ┌┐
> │ last_value │
> ├┤
> │ 74 │
> └┘
>   cf-db-srv1:5432 postgres@rete_llpp=# select count(*) from
> bobo.menu_pages;
> ┌───┐
> │ count │
> ├───┤
> │74 │
> └───┘
>
> --
> -- replica server
> --
> postgres@db-srv2:~$ psql
> psql (9.5.24)
> cf-db-srv2:5433 postgres@rete_llpp=# SELECT last_value FROM
> bobo.menu_pages_mp_id_seq;
> ┌┐
> │ last_value │
> ├┤
> │105 │
> └┘
> cf-db-srv2:5433 postgres@rete_llpp=# select count(*) from bobo.menu_pages;
> ┌───┐
> │ count │
> ├───┤
> │74 │
> └───┘
>
>

Re: Insert into on conflict, data size upto 3 billion records

[Tim Cross]

Karthik K  writes:

> exactly, for now, what I did was, as the table is already partitioned, I
> created 50 different connections and tried updating the target table by
> directly querying from the source partition tables. Are there any other
> techniques that I can use to speed this up? also when we use on conflict
> statement for both insert and update does Postgres uses batching internally
> (committing for every 1 records etc) or will it update all records at
> once, in that case, does it create a version for each record and do swap
> all at once? I'm wondering how atomicity is guaranteed, also if I have to
> do batching other than selecting from individual partitions does doing it
> batches of 1 records help?
>

I have had pretty good success with the following strategy. However, you
will need to profile/test each assumption as things vary greatly
depending on data and table structure. A bit of trial and error is
usually required.

1. Use the \COPY command to upload the batch data into a temporary table
or at least a table with logging turned off

2. Run a stored procedure which first does updates for existing rows
then one which does inserts for non-existing rows into your final table

3. If your table is partitioned, pre-process your batches into separate
batches that are divided by the partition key, so instead of one big
batch, multiple smaller batches. If this is not possible, break your
upload up into multiple batches rather than one huge batch.

4. Optimise the update/insert statement to suit your data and table
structure, dropping any unnecessary indexes and re-building them once
finished (the whole upload). Note that this will need profiling as
depending on the index and index structure, dropping and re-creating can
be overall slower than leaving index in place.

5. Determine best times to run analyze to update table stats. Probably
want to do it after each update and insert run, but sometimes, may be
overall faster to just do it after each 'job' (update + insert).

6. don't forget to check the logs and watch for WAL writes being too
frequent etc. Often things are tuned for 'normal' (outside bulk uploads)
and are very poor for the bulk uploads. Need to make sure it is the
right balance.

Re: Insert into on conflict, data size upto 3 billion records

[Karthik K]

yes, I'm using \copy to load the batch table,

with the new design that we are doing, we expect updates to be less going
forward and more inserts, one of the target columns I'm updating is
indexed, so I will drop the index and try it out, also from your suggestion
above splitting the on conflict into insert and update is performant but in
order to split the record into batches( low, high) I need to do a count of
primary key on the batch tables to first split it into batches


On Mon, Feb 15, 2021 at 11:06 AM Rob Sargent  wrote:

>
>
> On 2/15/21 11:41 AM, Karthik K wrote:
> > exactly, for now, what I did was, as the table is already partitioned, I
> > created 50 different connections and tried updating the target table by
> > directly querying from the source partition tables. Are there any other
> > techniques that I can use to speed this up? also when we use on conflict
> > statement for both insert and update does Postgres uses batching
> > internally (committing for every 1 records etc) or will it update
> > all records at once, in that case, does it create a version for each
> > record and do swap all at once? I'm wondering how atomicity is
> > guaranteed, also if I have to do batching other than selecting from
> > individual partitions does doing it batches of 1 records help?
> >
> >
>
> What is your ratio of inserts versus update?  Can you separate the
> inserts and updates?  Is the target table indexed other than on primary
> key? If so can they be dropped?
>
> Assuming you use \copy to load the batch tables
>
> I've found this strategy to be effective:
> index batch on id
>
> --update first
> begin
> update target t set "all fields" from batch b where t.id = b.id and b.id
> between "hi" and "low"
> commit
> increment hi low, avoid overlap; repeat
>
> --insert
> begin;
> insert into target as select b.* from from batch b where not exists
> (select 1 from target v where b.id = v.id) and b.id between "hi" and "low"
> commit
> increment hi, low, avoid overlap; repeat
>
>
>
>
>
>
>
>
>

-- 
Regards,

Karthik K Kondamudi

Re: Insert into on conflict, data size upto 3 billion records

[Rob Sargent]

On 2/15/21 11:41 AM, Karthik K wrote:
exactly, for now, what I did was, as the table is already partitioned, I 
created 50 different connections and tried updating the target table by 
directly querying from the source partition tables. Are there any other 
techniques that I can use to speed this up? also when we use on conflict 
statement for both insert and update does Postgres uses batching 
internally (committing for every 1 records etc) or will it update 
all records at once, in that case, does it create a version for each 
record and do swap all at once? I'm wondering how atomicity is 
guaranteed, also if I have to do batching other than selecting from 
individual partitions does doing it batches of 1 records help?





What is your ratio of inserts versus update?  Can you separate the 
inserts and updates?  Is the target table indexed other than on primary 
key? If so can they be dropped?


Assuming you use \copy to load the batch tables

I've found this strategy to be effective:
index batch on id

--update first
begin
update target t set "all fields" from batch b where t.id = b.id and b.id 
between "hi" and "low"

commit
increment hi low, avoid overlap; repeat

--insert
begin;
insert into target as select b.* from from batch b where not exists 
(select 1 from target v where b.id = v.id) and b.id between "hi" and "low"

commit
increment hi, low, avoid overlap; repeat

Re: How to post to this mailing list from a web based interface

[Alvaro Herrera]

On 2021-Feb-15, Adrian Klaver wrote:

> On 2/15/21 9:24 AM, Thomas Guyot wrote:
> 
> > The "download mbox" option doesn't work, I get asked for a user/password
> > every time (I would've downloaded archives for the lats two months to
> > get continuation on most threads).
> 
> The user/password is in the message in the prompt.

Sadly, some browsers choose not to display that message -- apparently
the message itself has been used somehow by certain sites to attack
something or other.

-- 
Álvaro Herrera39°49'30"S 73°17'W

Re: Insert into on conflict, data size upto 3 billion records

[Karthik K]

exactly, for now, what I did was, as the table is already partitioned, I
created 50 different connections and tried updating the target table by
directly querying from the source partition tables. Are there any other
techniques that I can use to speed this up? also when we use on conflict
statement for both insert and update does Postgres uses batching internally
(committing for every 1 records etc) or will it update all records at
once, in that case, does it create a version for each record and do swap
all at once? I'm wondering how atomicity is guaranteed, also if I have to
do batching other than selecting from individual partitions does doing it
batches of 1 records help?





On Sat, Feb 13, 2021 at 12:04 PM Ron  wrote:

> On 2/12/21 12:46 PM, Karthik Kumar Kondamudi wrote:
>
> Hi,
>
> I'm looking for suggestions on how I can improve the performance of the
> below merge statement, we have a batch process that batch load the data
> into the _batch tables using Postgres and the task is to update the main
> target tables if the record exists else into it, sometime these batch table
> could go up to 5 billion records. Here is the current scenario
>
> target_table_main has 700,070,247  records and is hash partitioned into
> 50 chunks, it has an index on logical_ts and the batch table has
> 2,715,020,546 close to 3 billion records, so I'm dealing with a huge set of
> data so looking of doing this in the most efficient way.
>
>
> Many times, I have drastically sped up batch processing by #1 partitioning
> on the same field as an index, and #2 pre-sorting the input data by that
> field.
>
> That way, you get excellent "locality of data" (meaning lots of writes to
> the same hot bits of cache, which later get asynchronously flushed to
> disk).  Unfortunately for your situation, the purpose of hash partitioning
> is to *reduce* locality of data.  (Sometimes that's useful, but *not*
> when processing batches.)
>
> --
> Angular momentum makes the world go 'round.
>


-- 
Regards,

Karthik K Kondamudi

Re: prepare in a do loop

[Adrian Klaver]

On 2/15/21 9:55 AM, Marc Millas wrote:

Hi Tom,

I do read the doc, and understand the caching behaviour of plpgsql.


This is not about plpgsql caching. It is about EXECUTE in plpgsql 
meaning something different then the PREPARE/EXECUTE combination in SQL. 
 You are trying to run EXECUTE moninsert(randname()) in plpgsql where 
moninsert was a PREPARE statement. In plpgsql EXECUTE is something 
different so it does not recognize  moninsert(randname()) as a prepared 
statement and fails.




if in psql I write begin;execute moninsert(randname()); execute 
moninsert(randname());end;
it does work.  And if I put this (begin execute end) inside a do loop it 
doesnt anymore.
ok the begin execute end is ""pure"" SQL, and the same thing within a do 
loop is plpgsql

so
postgres=# create function testexec()returns void as $$
postgres$# execute moninsert(randname());
postgres$# end;
postgres$# $$ language plpgsql;
ERREUR:  erreur de syntaxe sur ou près de « execute »
LIGNE 2 : execute moninsert(randname());
fine, quite coherent.
then





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

Re: certs in connection string

[Rob Sargent]

On 2/15/21 8:23 AM, Laurenz Albe wrote:

On Sat, 2021-02-13 at 09:57 -0700, Rob Sargent wrote:

I’m confused, as usual, about using a cert in a connection string.  I wish to 
connect form a
  “middle ware” piece to PG on be half of various clients.  Does each client 
need a corresponding
  cert/key or is the certification intended to say the sending machine is who 
it says it is
  (thereby needing only one cert)


They can share one certificate.

https://www.postgresql.org/docs/current/auth-cert.html:

   When using this authentication method, the server will require that the 
client provide a valid,
   trusted certificate. No password prompt will be sent to the client. The cn 
(Common Name)
   attribute of the certificate will be compared to the requested database user 
name, and if they
   match the login will be allowed.

Yours,
Laurenz Albe


Thank you.

Since I wish to make the jdbc connection using the role's login (for 
search_path, I take it I will make role-specific certs, setting the CN 
accordingly. (I do know which role I need for each connection request 
and can set the dbname as well).

Replication sequence

[Paolo Saudin]

Hi all,
I have two servers, a primary and a secondary one with a streaming replica
setup.
Today I noticed that some sequences are not lined-up, the replica ones are
well ahead, while the records number is the same. How is it possible?

Thanks,
Paolo

--
-- primary server
--
postgres@db-srv1:~$ psql
psql (9.5.19)
cf-db-srv1:5432 postgres@rete_llpp=# SELECT last_value FROM
bobo.menu_pages_mp_id_seq;
┌┐
│ last_value │
├┤
│ 74 │
└┘
  cf-db-srv1:5432 postgres@rete_llpp=# select count(*) from bobo.menu_pages;
┌───┐
│ count │
├───┤
│74 │
└───┘

--
-- replica server
--
postgres@db-srv2:~$ psql
psql (9.5.24)
cf-db-srv2:5433 postgres@rete_llpp=# SELECT last_value FROM
bobo.menu_pages_mp_id_seq;
┌┐
│ last_value │
├┤
│105 │
└┘
cf-db-srv2:5433 postgres@rete_llpp=# select count(*) from bobo.menu_pages;
┌───┐
│ count │
├───┤
│74 │
└───┘

Re: prepare in a do loop

[Marc Millas]

Hi Tom,

I do read the doc, and understand the caching behaviour of plpgsql.
if in psql I write begin;execute moninsert(randname()); execute
moninsert(randname());end;
it does work.  And if I put this (begin execute end) inside a do loop it
doesnt anymore.
ok the begin execute end is ""pure"" SQL, and the same thing within a do
loop is plpgsql
so
postgres=# create function testexec()returns void as $$
postgres$# execute moninsert(randname());
postgres$# end;
postgres$# $$ language plpgsql;
ERREUR:  erreur de syntaxe sur ou près de « execute »
LIGNE 2 : execute moninsert(randname());
fine, quite coherent.
then
postgres=# create function testexec()returns void as $$
execute moninsert(randname());
end;
$$ language sql;
CREATE FUNCTION
as SQL, legal syntax.. ok
but
postgres=# select testexec();
ERREUR:  COMMIT n'est pas autorisé dans une fonction SQL
CONTEXTE : fonction SQL « testexec » lors du lancement
a bit more difficult to understand, as such.(where is the commit ??)
so.. the prepare//execute thing can only be used in embedded SQL (as not in
any plpg, nor in sql functions.
The doc states :
The SQL standard includes a PREPARE statement, but it is only for use in
embedded SQL. This version of the PREPARE statement also uses a somewhat
different syntax.
??? where is the difference for the prepare context thing (I dont mean the
different syntax part) ??

thanks for clarification


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Mon, Feb 15, 2021 at 5:27 PM Tom Lane  wrote:

> Marc Millas  writes:
> > in psql, with a postgres 12.5 db on a centos 7 intel:
> > I do create a function named randname() returning a varchar, and a table
> > matable with a column prenom varchar(50). then
> > postgres=# prepare moninsert(varchar) as
> > postgres-# insert into matable(prenoms) values($1);
> > PREPARE
>
> > I test it:
> > postgres=# execute moninsert(randname());
> > INSERT 0 1
>
> > up to now, everything fine. then:
> > do $$ begin for counter in 1..100 loop execute
> > moninsert(randname());end loop;end;$$;
> > ERREUR:  la fonction moninsert(character varying) n'existe pas
> > LIGNE 1 : SELECT moninsert(randname())
>
> > someone can explain ?
>
> EXECUTE in plpgsql has nothing whatever to do with the SQL EXECUTE
> command.  See the respective documentation.
>
> You don't actually need to use SQL PREPARE/EXECUTE in plpgsql.
> If you just write "insert into ..." as a command in a
> plpgsql function, it's automatically prepared behind the scenes.
> Indeed, one of the common uses for plpgsql's EXECUTE is to stop
> a prepared plan from being used when you don't want that ... so
> far from being the same thing, they're more nearly opposites.
> Perhaps a different name should have been chosen, but we're
> stuck now.
>
> regards, tom lane
>

Re: How to post to this mailing list from a web based interface

[Adrian Klaver]

On 2/15/21 9:24 AM, Thomas Guyot wrote:


The "download mbox" option doesn't work, I get asked for a user/password
every time (I would've downloaded archives for the lats two months to
get continuation on most threads).


The user/password is in the message in the prompt.



The resend option does work, but you have to wait 30 seconds between
messages, and I like to have the entire thread as it makes it easier to
follow.

Regards,

--
Thomas






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

ALTER ROLE ... SET in current database only

[Wolfgang Walther]

Hi,

I'm trying to set a GUC for a role in the current database only - but 
don't know the name of the database at the time of writing code. Could 
be development, staging, ...


I would basically like to do something like this:

ALTER ROLE a IN CURRENT DATABASE SET b TO c;

Although that syntax doesn't exist (yet).

I think I could wrap it in a DO block and create the statement 
dynamically. Alternatively, I could probably INSERT INTO / UPDATE 
pg_db_role_setting manually?


Any other ideas how to achieve this easily? Does the proposed "IN 
CURRENT DATABASE" syntax sound useful to anyone else?


Best,

Wolfgang

Re: How to post to this mailing list from a web based interface

[Thomas Guyot]

On 2021-02-14 22:44, Thomas Munro wrote:
> On Fri, Jan 29, 2021 at 4:27 AM Alvaro Herrera  
> wrote:
> 
> While catching up with some interesting new threads just now I was
> quite confused by the opening sentence of this message (which also
> arrived in my mailbox):
> 
> https://www.postgresql.org/message-id/1611355191319-0.post%40n3.nabble.com
> 
> ... until I got to the last line.  I wonder if the "Resend" facility
> on our own archives could be better advertised, via a "Want to join
> this thread?" link in the Quick Links section that explains how to use
> it and what problem it solves, or something...
> 

Hi,

I haven't read the whole thread, but my take on it as a newjoiner:

Nable didn't seem to work. I got a bounce iirc. The reply/quoting format
was quite uncommon too..

As part of the process I had to join the ML anyway, so I went to the
archives to find the thread I wanted to reply to.

The "download mbox" option doesn't work, I get asked for a user/password
every time (I would've downloaded archives for the lats two months to
get continuation on most threads).

The resend option does work, but you have to wait 30 seconds between
messages, and I like to have the entire thread as it makes it easier to
follow.

Regards,

--
Thomas

Re: ADD FOREIGN KEY fails, but the records exist

[Adrian Klaver]

On 2/15/21 8:55 AM, Ron wrote:






The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
     ADD CONSTRAINT amended_response_fk FOREIGN KEY 
(amended_response_id, part_date)

     REFERENCES employer_response(employer_response_id, part_date)
     ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates 
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response".

sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
     103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
*103309154* | | 2021-01-06 *15:14:03*
(1 row)


To add to my previous post regarding the part about the data transfer 
process. You might look for code that did something like:


select current_date::timestamp;

current_date
-
 2021-02-15 00:00:00

In other words turned a date into a timestamp.




--
Angular momentum makes the world go 'round.



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

Re: ADD FOREIGN KEY fails, but the records exist

[Ron]

On 2/15/21 10:58 AM, Adrian Klaver wrote:

On 2/15/21 8:55 AM, Ron wrote:



On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5




The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response"


is pointing at 103309154 for amended_response_id = 
employer_response_id. You are showing an employer_response_id of 
103309156


But my query's WHERE clause specifies "amended_response_id = 
103309154;"  (I've highlighted it, if you have a GUI MUA.)


Yes but amended_response_id is referencing employer_response_id. So do 
you have a record that matches:


employer_response_id   part_date

103309154  2021-01-06 00:00:00


The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
 ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id, 
part_date)

 REFERENCES employer_response(employer_response_id, part_date)
 ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates 
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response".

sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
 103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
*103309154* | | 2021-01-06 *15:14:03*
(1 row)



Well since it is the same column(type) then it had to be something in the 
transfer of the data from Oracle to Postgres. What are the values on the 
Oracle end?


That's a good question, which I don't know the answer to.

--
Angular momentum makes the world go 'round.

Re: ADD FOREIGN KEY fails, but the records exist

[Adrian Klaver]

On 2/15/21 8:55 AM, Ron wrote:



On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5




The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response"


is pointing at 103309154 for amended_response_id = 
employer_response_id. You are showing an employer_response_id of 
103309156


But my query's WHERE clause specifies "amended_response_id = 
103309154;"  (I've highlighted it, if you have a GUI MUA.)


Yes but amended_response_id is referencing employer_response_id. So do 
you have a record that matches:


employer_response_id   part_date

103309154  2021-01-06 00:00:00


The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
     ADD CONSTRAINT amended_response_fk FOREIGN KEY 
(amended_response_id, part_date)

     REFERENCES employer_response(employer_response_id, part_date)
     ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates 
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response".

sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
     103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
*103309154* | | 2021-01-06 *15:14:03*
(1 row)



Well since it is the same column(type) then it had to be something in 
the transfer of the data from Oracle to Postgres. What are the values on 
the Oracle end?





--
Angular momentum makes the world go 'round.



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

pg_stat_user_tables.n_mod_since_analyze persistence?

[Philip Semanchuk]

Hi all,
I saw some unexpected behavior that I'm trying to understand. I suspect it 
might be a quirk specific to AWS Aurora and I'd like to confirm that.

When I restart my local Postgres instance (on my Mac), the values in 
pg_stat_user_tables.n_mod_since_analyze are preserved. In other words, if table 
foo had n_mod_since_analyze=33 before the reboot, it still has 
n_mod_since_analyze=33 after the restart. 

When I restart an AWS Aurora instance, the values in 
pg_stat_user_tables.n_mod_since_analyze all seem to be reset to 0. 

Can anyone confirm (or refute) that the behavior I see on my Mac (preservation 
of these values through a restart) is common & expected behavior?

Thanks
Philip

Re: ADD FOREIGN KEY fails, but the records exist

[Ron]

On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5




The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response"


is pointing at 103309154 for amended_response_id = employer_response_id. 
You are showing an employer_response_id of 103309156


But my query's WHERE clause specifies "amended_response_id = 103309154;"  
(I've highlighted it, if you have a GUI MUA.)


Yes but amended_response_id is referencing employer_response_id. So do you 
have a record that matches:


employer_response_id   part_date

103309154  2021-01-06 00:00:00


The time portions of the part_date fields don't match...

sides=> ALTER TABLE employer_response
    ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id, 
part_date)

    REFERENCES employer_response(employer_response_id, part_date)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates 
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response".

sides=>

sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where amended_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
    103309156 | *103309154 *| 2021-01-06*00:00:00*
(1 row)

sides=>
sides=>
sides=> select employer_response_id, amended_response_id, part_date
from strans.employer_response
where employer_response_id = 103309154;
employer_response_id | amended_response_id | part_date
--+-+-
*103309154* | | 2021-01-06 *15:14:03*
(1 row)


--
Angular momentum makes the world go 'round.

Re: ADD FOREIGN KEY fails, but the records exist

[Tom Lane]

Ron  writes:
> Postgresql 12.5
> It's a self-referential FK on a single (but partitioned) table.  The ALTER 
> TABLE command fails, but I queried it, and the record that it fails on 
> exists.  I modified the original INITIALLY IMMEDIATE clause to INITIALLY 
> DEFERRED but that did not help.

> What am I doing wrong?

As Adrian noted, the queries you showed don't actually prove that the
required employer_response_id exists in the table.  However, if the
identical data worked in Oracle then it should work in PG too, so for
the moment I'll assume that that was a thinko and the FK should be
valid.  In that case I'd go looking for "invisible" reasons for the
keys not to match.  You did not show the column data types, but if the
response ids are strings not numbers then I'd be wondering about extra
spaces and such.  Perhaps Oracle is more forgiving of such things than
PG is.

regards, tom lane

Re: ADD FOREIGN KEY fails, but the records exist

[Ron]

On 2/15/21 10:27 AM, Adrian Klaver wrote:

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5




The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response"


is pointing at 103309154 for amended_response_id = employer_response_id. 
You are showing an employer_response_id of 103309156


But my query's WHERE clause specifies "amended_response_id = 103309154;"  
(I've highlighted it, if you have a GUI MUA.)


Yes but amended_response_id is referencing employer_response_id. So do you 
have a record that matches:


employer_response_id   part_date

103309154  2021-01-06 00:00:00


Ah, I see now.  No, we don't/










sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where *amended_response_id = 103309154; *
employer_response_id |  part_date
--+-
 103309156 | 2021-01-06 00:00:00
(1 row)







--
Angular momentum makes the world go 'round.





--
Angular momentum makes the world go 'round.

Re: prepare in a do loop

[Tom Lane]

Marc Millas  writes:
> in psql, with a postgres 12.5 db on a centos 7 intel:
> I do create a function named randname() returning a varchar, and a table
> matable with a column prenom varchar(50). then
> postgres=# prepare moninsert(varchar) as
> postgres-# insert into matable(prenoms) values($1);
> PREPARE

> I test it:
> postgres=# execute moninsert(randname());
> INSERT 0 1

> up to now, everything fine. then:
> do $$ begin for counter in 1..100 loop execute
> moninsert(randname());end loop;end;$$;
> ERREUR:  la fonction moninsert(character varying) n'existe pas
> LIGNE 1 : SELECT moninsert(randname())

> someone can explain ?

EXECUTE in plpgsql has nothing whatever to do with the SQL EXECUTE
command.  See the respective documentation.

You don't actually need to use SQL PREPARE/EXECUTE in plpgsql.
If you just write "insert into ..." as a command in a
plpgsql function, it's automatically prepared behind the scenes.
Indeed, one of the common uses for plpgsql's EXECUTE is to stop
a prepared plan from being used when you don't want that ... so
far from being the same thing, they're more nearly opposites.
Perhaps a different name should have been chosen, but we're
stuck now.

regards, tom lane

Re: ADD FOREIGN KEY fails, but the records exist

[Adrian Klaver]

On 2/15/21 8:23 AM, Ron wrote:

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5




The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response"


is pointing at 103309154 for amended_response_id = 
employer_response_id. You are showing an employer_response_id of 
103309156


But my query's WHERE clause specifies "amended_response_id = 
103309154;"  (I've highlighted it, if you have a GUI MUA.)


Yes but amended_response_id is referencing employer_response_id. So do 
you have a record that matches:


employer_response_id   part_date

103309154  2021-01-06 00:00:00








sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where *amended_response_id = 103309154; *
employer_response_id |  part_date
--+-
 103309156 | 2021-01-06 00:00:00
(1 row)







--
Angular momentum makes the world go 'round.



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

Re: prepare in a do loop

[David G. Johnston]

On Mon, Feb 15, 2021 at 9:19 AM Marc Millas  wrote:

>
> postgres=# prepare moninsert(varchar) as
>
> do $$ begin for counter in 1..100 loop execute
> moninsert(randname());end loop;end;$$;
> ERREUR:  la fonction moninsert(character varying) n'existe pas
> someone can explain ?
>
>
>From the pl/pgsql docs:

"The PL/pgSQL EXECUTE statement is not related to the EXECUTE SQL statement
supported by the PostgreSQL server. The server's EXECUTE statement cannot
be used directly within PL/pgSQL functions (and is not needed)."

https://www.postgresql.org/docs/current/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

David J.

Re: ADD FOREIGN KEY fails, but the records exist

[Ron]

On 2/15/21 10:17 AM, Adrian Klaver wrote:

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5

It's a self-referential FK on a single (but partitioned) table. The ALTER 
TABLE command fails, but I queried it, and the record that it fails on 
exists.  I modified the original INITIALLY IMMEDIATE clause to INITIALLY 
DEFERRED but that did not help.


What am I doing wrong?

(We're migrating from Oracle to PostgreSQL, and this is working in Oracle.)

sides=> ALTER TABLE employer_response
 ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id, 
part_date)

 REFERENCES employer_response(employer_response_id, part_date)
 ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates 
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response".

sides=>
sides=> select employer_response_id, part_date
sides-> from strans.employer_response
sides-> *where amended_response_id = 103309154*;
employer_response_id |  part_date
--+-
 103309156 | 2021-01-06 00:00:00
(1 row)


The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response"


is pointing at 103309154 for amended_response_id = employer_response_id. 
You are showing an employer_response_id of 103309156


But my query's WHERE clause specifies "amended_response_id = 103309154;"  
(I've highlighted it, if you have a GUI MUA.)







sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where *amended_response_id = 103309154; *
employer_response_id |  part_date
--+-
 103309156 | 2021-01-06 00:00:00
(1 row)







--
Angular momentum makes the world go 'round.

Re: prepare in a do loop

[Adrian Klaver]

On 2/15/21 8:18 AM, Marc Millas wrote:

Hi,

in psql, with a postgres 12.5 db on a centos 7 intel:
I do create a function named randname() returning a varchar, and a table 
matable with a column prenom varchar(50). then

postgres=# prepare moninsert(varchar) as
postgres-# insert into matable(prenoms) values($1);
PREPARE

I test it:
postgres=# execute moninsert(randname());
INSERT 0 1

up to now, everything fine. then:
do $$ begin for counter in 1..100 loop execute 
moninsert(randname());end loop;end;$$;

ERREUR:  la fonction moninsert(character varying) n'existe pas
LIGNE 1 : SELECT moninsert(randname())

someone can explain ?


EXECUTE in plpgsql means something different:

https://www.postgresql.org/docs/12/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN



thanks

(its a french db, so error message in french :-)

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 




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

prepare in a do loop

[Marc Millas]

Hi,

in psql, with a postgres 12.5 db on a centos 7 intel:
I do create a function named randname() returning a varchar, and a table
matable with a column prenom varchar(50). then
postgres=# prepare moninsert(varchar) as
postgres-# insert into matable(prenoms) values($1);
PREPARE

I test it:
postgres=# execute moninsert(randname());
INSERT 0 1

up to now, everything fine. then:
do $$ begin for counter in 1..100 loop execute
moninsert(randname());end loop;end;$$;
ERREUR:  la fonction moninsert(character varying) n'existe pas
LIGNE 1 : SELECT moninsert(randname())

someone can explain ?

thanks

(its a french db, so error message in french :-)

Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com

Re: ADD FOREIGN KEY fails, but the records exist

[Adrian Klaver]

On 2/15/21 8:12 AM, Ron wrote:

Postgresql 12.5

It's a self-referential FK on a single (but partitioned) table.  The 
ALTER TABLE command fails, but I queried it, and the record that it 
fails on exists.  I modified the original INITIALLY IMMEDIATE clause to 
INITIALLY DEFERRED but that did not help.


What am I doing wrong?

(We're migrating from Oracle to PostgreSQL, and this is working in Oracle.)

sides=> ALTER TABLE employer_response
     ADD CONSTRAINT amended_response_fk FOREIGN KEY 
(amended_response_id, part_date)

     REFERENCES employer_response(employer_response_id, part_date)
     ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates 
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response".

sides=>
sides=> select employer_response_id, part_date
sides-> from strans.employer_response
sides-> where amended_response_id = 103309154;
employer_response_id |  part_date
--+-
     103309156 | 2021-01-06 00:00:00
(1 row)


The error:

DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response"


is pointing at 103309154 for amended_response_id = employer_response_id. 
You are showing an employer_response_id of 103309156





sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where amended_response_id = 103309154;
employer_response_id |  part_date
--+-
     103309156 | 2021-01-06 00:00:00
(1 row)





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

Re: MultiXactMemberControlLock contention on a replica

[Laurenz Albe]

On Mon, 2021-02-15 at 08:03 -0800, Christophe Pettus wrote:
> On Feb 15, 2021, at 07:47, Laurenz Albe  wrote:
> > So my guess would be that the difference between primary and standby is not 
> > that a
> > different number of multixacts are created, but that you need to read them 
> > on
> > the standby and not on the primary.
> 
> Why does the secondary need to read them?  Visibility?

Right.  I cannot think of any other reason, given that the standby only
allows reading.  It's just an "xmax", and PostgreSQL needs to read the
multixact to figure out if it can see the row or not.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

ADD FOREIGN KEY fails, but the records exist

[Ron]

Postgresql 12.5

It's a self-referential FK on a single (but partitioned) table.  The ALTER 
TABLE command fails, but I queried it, and the record that it fails on 
exists.  I modified the original INITIALLY IMMEDIATE clause to INITIALLY 
DEFERRED but that did not help.


What am I doing wrong?

(We're migrating from Oracle to PostgreSQL, and this is working in Oracle.)

sides=> ALTER TABLE employer_response
    ADD CONSTRAINT amended_response_fk FOREIGN KEY (amended_response_id, 
part_date)

    REFERENCES employer_response(employer_response_id, part_date)
    ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED ;
ERROR:  insert or update on table "employer_response_p2021_01" violates 
foreign key constraint "amended_response_fk"
DETAIL:  Key (amended_response_id, part_date)=(103309154, 2021-01-06 
00:00:00) is not present in table "employer_response".

sides=>
sides=> select employer_response_id, part_date
sides-> from strans.employer_response
sides-> where amended_response_id = 103309154;
employer_response_id |  part_date
--+-
    103309156 | 2021-01-06 00:00:00
(1 row)


sides=> select employer_response_id, part_date
from strans.employer_response_p2021_01
where amended_response_id = 103309154;
employer_response_id |  part_date
--+-
    103309156 | 2021-01-06 00:00:00
(1 row)


--
Angular momentum makes the world go 'round.

Re: MultiXactMemberControlLock contention on a replica

[Christophe Pettus]

> On Feb 15, 2021, at 07:47, Laurenz Albe  wrote:
> So my guess would be that the difference between primary and standby is not 
> that a
> different number of multixacts are created, but that you need to read them on
> the standby and not on the primary.

Why does the secondary need to read them?  Visibility?
--
-- Christophe Pettus
   x...@thebuild.com

Re: MultiXactMemberControlLock contention on a replica

[Laurenz Albe]

On Fri, 2021-02-12 at 11:11 -0800, Christophe Pettus wrote:
> On a whole fleet of load-balanced replicas, we saw an incident where one 
> particular query
>  started backing up on MultiXactMemberControlLock and multixact_member.  
> There was no sign
>  of this backup on the primary.  Under what conditions would there be enough 
> multixact
>  members on a replica (where you can't do UPDATE / SELECT FOR UPDATE / FOR 
> SHARE) to start
>  spilling to disk?

Multixacts are replicated, and they are only generated on the primary.

So my guess would be that the difference between primary and standby is not 
that a
different number of multixacts are created, but that you need to read them on
the standby and not on the primary.

Are the multixacts caused by foreign keys or by subtransactions?

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: [LDAPS] Test connection user with ldaps server

[Laurenz Albe]

On Sat, 2021-02-13 at 10:36 +, João Gaspar wrote:
> I have a PostgreSQL 13.1 (RHEL 8.3) Server and I want to configure the 
> pg_hba.conf with a remote ldaps server.
> 
> My steps:
> 
> I create a PostgreSQL user1 with superuser role to test the ldaps 
> authentication method in the terminal client.
> 
> Modify the pg_hba.conf to:
> 
> hostall all 0.0.0.0/0   ldap
> ldapurl="ldaps://serverurl:636/DC=company,DC=example,DC=com?sAMAccountName?sub"
>  ldapbinddn="user-to-do-autentication-ldap-
> connection" ldapbindpasswd=" user-ldap-connection password-autentication" 
> 
> Save and restart the PostgreSQL service.
> 
> Try to connect with the terminal client with psql -h 
> postgresqlremoteserverhost -U user1 and after putting the password give the 
> following error:
> psql: FATAL:  LDAP authentication failed for user "user1"
> 
> I validate the ldap user1 with ldapsearch (in the RHEL host) and the user1 
> appears in the ldapsearch correctly using the same ldapurl, ldapbinddn and 
> ldapbinpasswd.
> 
> Checking the remote postgresql logs, the connection to the remote ldaps do 
> the correct authentication but can´t search by the attribute sAMAccountName. 
> Here is the PostgreSQL log:
> could not search LDAP for filter "(sAMAccountName=user1)" on server 
> "serverurl": Operations error 2021-02-13 10:02:54.679 WET [1127801] DETAIL:  
> LDAP diagnostics: 04DC: LdapErr: DSID-0C0907E9,
> comment: To perform this operation a successful bind must be completed on the 
> connection., data 0, v2580
> 
> Info: The user1 was created as well in the ldaps server with sAMAccountName 
> user1.  
> 
> It seems that the problem is in the pg_hba.conf how to tell the search, can 
> anyone have similar problem ou resolution?

That error looks strange to me, but I am not an LDAP expert.

Your configuration seems fine to me, and if it gets to search, it must have 
bound to
"DC=company,DC=example,DC=com?sAMAccountName" as the "ldapbinddn" first.

What I would do is experiment with the "ldapsearch" executable from OpenLDAP 
and see
if you can reproduce the problem from the command line.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: certs in connection string

[Laurenz Albe]

On Sat, 2021-02-13 at 09:57 -0700, Rob Sargent wrote:
> I’m confused, as usual, about using a cert in a connection string.  I wish to 
> connect form a
>  “middle ware” piece to PG on be half of various clients.  Does each client 
> need a corresponding
>  cert/key or is the certification intended to say the sending machine is who 
> it says it is
>  (thereby needing only one cert)

They can share one certificate.

https://www.postgresql.org/docs/current/auth-cert.html:

  When using this authentication method, the server will require that the 
client provide a valid,
  trusted certificate. No password prompt will be sent to the client. The cn 
(Common Name)
  attribute of the certificate will be compared to the requested database user 
name, and if they
  match the login will be allowed.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: Why is Postgres only using 8 cores for partitioned count? [Parallel Append]

[Fabio Pardi]

On 14/02/2021 22:16, Gavin Flower wrote:
> On 14/02/2021 22:47, David Rowley wrote:
>> On Sun, 14 Feb 2021 at 13:15, Seamus Abshere
>>  wrote:
>>> The comment from Robert says: (src/backend/optimizer/path/allpaths.c)
>>>
>>>  /*
>>>   * If the use of parallel append is permitted, always 
>>> request at least
>>>   * log2(# of children) workers.
>>>
>>> In my case, every partition takes 1 second to scan, I have 64 cores, I have 
>>> 64 partitions, and the wall time is 8 seconds with 8 workers.
>>>
>>> I assume that if it it planned significantly more workers (16? 32? even 
>>> 64?), it would get significantly faster (even accounting for transaction 
>>> cost). So why doesn't it ask for more? Note that I've set 
>>> max_parallel_workers=512, etc. (postgresql.conf in my first message).
>> There's perhaps an argument for allowing ALTER TABLE > table> SET (parallel_workers=N); to be set on partitioned tables, but
>> we don't currently allow it.
> [...]
>> David
>
> Just wondering why there is a hard coded limit.
>
> While I agree it might be good to be able specify the number of workers, sure 
> it would be possible to derive a suitable default based on the number of 
> effective processors available?
>


I had the same problem and my conclusion was that it is not possible to go 
above 8 cores because of Amdahl's law on parallel computing. More here: 
https://en.wikipedia.org/wiki/Amdahl%27s_law

regards,

fabio pardi

Re: Why is Postgres only using 8 cores for partitioned count? [Parallel Append]

[Laurenz Albe]

On Sun, 2021-02-14 at 22:47 +1300, David Rowley wrote:
> On Sun, 14 Feb 2021 at 13:15, Seamus Abshere
> 
>  wrote:
> 
> > The comment from Robert says: (src/backend/optimizer/path/allpaths.c)
> >  /*
> >   * If the use of parallel append is permitted, always 
> > request at least
> >   * log2(# of children) workers.
> > In my case, every partition takes 1 second to scan, I have 64 cores, I have 
> > 64 partitions, and the wall time is 8 seconds with 8 workers.
> > I assume that if it it planned significantly more workers (16? 32? even 
> > 64?), it would get significantly faster (even accounting for transaction 
> > cost). So why doesn't it ask for more? Note that
> > I've set max_parallel_workers=512, etc. (postgresql.conf in my first 
> > message).
> 
> There's perhaps an argument for allowing ALTER TABLE  table> SET (parallel_workers=N); to be set on partitioned tables, but
> we don't currently allow it.

That would be great; I have been hit by this before.

> What you might want to try is setting that for any of those 64
> partitions.  Shortly above the code comment that you quoted above,
> there's some code that finds the path for the partition with the
> maximum number of parallel workers. If one of those partitions is
> using, say 64 workers because you set the partitions
> "parallel_workers" setting to 64, and providing you have
> max_parallel_workers_per_gather set highly enough, then your Append
> should get 64 workers.

Hmm - that didn't work when I tried it, but perhaps I should try again.

> You'll need to be careful though since changing the partitions
> parallel_workers may affect things for other queries too. Also, if you
> were to only change 1 partition and that partition were to be pruned,
> then you'd not get the 64 workers.

I think this is an area where parallel query could be improved.

One think is runtime partition pruning:  if the optimizer thinks that
it will have to scan a lot of partitions, it will plan a lot of workers.
But if the executor reduces that number to 1, we end up with way too
many workers.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

[Thorsten Schöning]

Guten Tag Guy Burgess,
am Montag, 15. Februar 2021 um 11:52 schrieben Sie:

> The mystery now is that the only process logged as touching the
> affected WAL files is postgres.exe (of which there are many separate
> processes). Could it be that one of the postgres.exe instances is
> holding the affected WAL files in use after another postgres.exe
> instance has flagged the file as deleted?[...]

I suggest checking your WAL-related and archive/backup settings for
Postgres again. There's e.g. "archive_command" optionally copying WALs
to some other place and postgres.exe would wait until that process has
finished, maybe locking the file to copy itself as well. Or
"archive_timeout" interfering with some other operations or alike.

Mit freundlichen Grüßen

Thorsten Schöning

-- 
Thorsten Schöning
AM-SoFT IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore 
Gruppe - Ihr Full-Service-Dienstleister für IT und TK

E-Mail: thorsten.schoen...@am-soft.de
Web:http://www.AM-SoFT.de/

Telefon: 05151-  9468- 0
Telefon: 05151-  9468-55
Fax: 05151-  9468-88
Mobil:0178-8 9468-04

AM-SoFT IT-Service - Bitstore Hameln GmbH i.G., Brandenburger Str. 7c, 31789 
Hameln
AG Hannover HRB neu - Geschäftsführer: Janine Galonska


Für Rückfragen stehe ich Ihnen sehr gerne zur Verfügung.

Mit freundlichen Grüßen

Thorsten Schöning


Tel: 05151 9468 0
Fax: 05151 9468 88
Mobil: 
Webseite: https://www.am-soft.de 

AM-Soft IT-Service - Bitstore Hameln GmbH i.G. ist ein Mitglied der Bitstore 
Gruppe - Ihr Full-Service-Dienstleister für IT und TK

AM-Soft IT-Service - Bitstore Hameln GmbH i.G.
Brandenburger Str. 7c
31789 Hameln
Tel: 05151 9468 0

Bitstore IT-Consulting GmbH
Zentrale - Berlin Lichtenberg
Frankfurter Allee 285
10317 Berlin
Tel: 030 453 087 80

CBS IT-Service - Bitstore Kaulsdorf UG
Tel: 030 453 087 880 1

Büro Dallgow-Döberitz
Tel: 03322 507 020

Büro Kloster Lehnin
Tel: 033207 566 530

PCE IT-Service - Bitstore Darmstadt UG
Darmstadt
Tel: 06151 392 973 0

Büro Neuruppin
Tel: 033932 606 090

ACI EDV Systemhaus Dresden GmbH
Dresden
Tel: 0351 254 410

Das Systemhaus - Bitstore Magdeburg GmbH
Magdeburg
Tel: 0391 636 651 0

Allerdata.IT - Bitstore Wittenberg GmbH
Wittenberg
Tel: 03491 876 735 7

Büro Liebenwalde
Tel: 033054 810 00

HSA - das Büro - Bitstore Altenburg UG
Altenburg
Tel: 0344 784 390 97

Bitstore IT – Consulting GmbH
NL Piesteritz 
Piesteritz
Tel: 03491 644 868 6

Solltec IT-Services - Bitstore Braunschweig UG
Braunschweig
Tel: 0531 206 068 0

MF Computer Service - Bitstore Gütersloh GmbH
Gütersloh
Tel: 05245 920 809 3

Firmensitz: MF Computer Service - Bitstore Gütersloh GmbH, Gildestraße 25, 
33442 Herzebrock-Clarholz
Geschäftsführer Janine Galonska

Re: PostgreSQL occasionally unable to rename WAL files (NTFS)

[Guy Burgess]

On 12/02/2021 4:33 am, Thorsten Schöning wrote:

The behaviour you describe happens exactly when two processes e.g.
concurrently hold HANDLEs on the same file and one of those deletes
the file then. Windows keeps file names until all open HANDLEs are
closed and depending on how those HANDLEs have been opened by the
first app, concurrent deletion is perferctly fine for Windows.

Though, a such deleted file can't be opened easily anymore and looks
like it has lost permissions only. But that's not the case, it's
deleted already. It might be that this happens for Postgres to itself
somehow when some other app has an open HANDLE. I don't think that
some other app is deleting that file by purpose instead, reading it
for some reason seems more likely to me.


Using Process Monitor, Thorsten's explanation above appears to correctly 
diagnose what is happening. ProcMon data shows postgres.exe performing 
"CreateFile" operations on the affected WAL files, with the result 
status "DELETE PENDING". Which according to 
https://stackoverflow.com/a/29892104 means:


   "Windows allows a process to delete a file, even though it is still
   opened by another process (e.g. Windows indexing service or
   Antivirus). It gets internally marked as "delete pending". The file
   does not actually get removed from the file system, it is still
   there after the File.Delete call. Anybody that tries to open the
   file after that gets an access denied error. The file doesn't
   actually get removed until the last handle to the file object gets
   closed"

which is the same behaviour Thorsten describes above (great info, thanks 
Thorsten).


The mystery now is that the only process logged as touching the affected 
WAL files is postgres.exe (of which there are many separate processes). 
Could it be that one of the postgres.exe instances is holding the 
affected WAL files in use after another postgres.exe instance has 
flagged the file as deleted? (or to put it the other way, a postgres.exe 
instance is flagging the file as deleted while another instance still 
has an open handle to the file)? If it is some other process such as the 
indexer (disabled) or AV (excluded from pgdata) is obtaining a handle on 
the WAL files, it isn't being logged by ProcMon.


Kind regards,

Guy