Best Open Source OS for Postgresql

[Giovanni Biscontini]

Hello everyone,
 we're looking for a Open Source alternative to Rhel for our VM server
dedicated to Postgresql (14->15) installations. We're testing Alma, Rocky,
and Oracle distributions as they're compatible with Rhel package systems.
Can you share your experience on a similar choice?
Thanks in advance and best regards, Giovanni

-- 





*Cordiali Saluti - Best Regards*






 *Dott. Giovanni Biscontini*

* [Divisone Software]*



  WEB: https://www.es2000.it 









Privacy e riservatezza: il presente messaggio, così come i relativi
allegati, contengono dati ed informazioni da considerarsi strettamente
riservate ed è indirizzato esclusivamente al destinatario sopra indicato,
il quale è l'unico autorizzato a trattarlo in osservanza delle norme del
Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque ricevesse questo
messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a
terzi e di dare notizia al mittente dell’errato invio, distruggendone poi
l'eventuale copia cartacea e la copia in formato elettronico.

Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 del
suddetto decreto tra cui quelli di accesso, rettifica, aggiornamento,
opposizione al trattamento e cancellazione

*Please, print this e-mail only if necessary*

Re: any wait event for a commit in sync replication?

[qihua wu]

Sorry, the pg_stat_activity sql was executed from a session connecting to a
wrong postgresql. It DOES show in the view, and the wait event is SyncRep.

postgres=# select * from pg_stat_activity where application_name='psql' and
datname='example';
-[ RECORD 1 ]+--

wait_event_type  | IPC
wait_event   | SyncRep
state| active


On Tue, Jan 31, 2023 at 3:35 PM qihua wu  wrote:

> We are using sync replication, if a session runs an insert, and then
> commit, the client is actually waiting for commit to complete, but looks
> like this wait doesn't show in pg_stat_activity.
>
> In one session I inserted a row (auto commit), it hangs there.
>
> example=# insert into test_timteout select '1';
> ^CCancel request sent
> WARNING:  canceling wait for synchronous replication due to user request
> DETAIL:  The transaction has already committed locally, but might not have
> been replicated to the standby.
> INSERT 0 1
> example=# select pg_backend_pid();
>  pg_backend_pid
> 
>   19325
> (1 row)
>
>
> During the hung period.
> postgres=# select * from pg_stat_activity where pid=19325;
> (0 rows)
>
> postgres=#
>
>
> If there is no event for such commit, anyway to find out such session
> which pending on commit(sync replication)
>

Re: How to control pg_catalog results for each users?

[廣瀬 繁雄 □SWC○ACT]

There have been a ton of discussions around this area over the years.
The short answer is that if you think you need to prevent people
from seeing the contents of the system catalogs, Postgres is not
the database for you.  I don't really foresee that changing, because
it would break at least as many use-cases as it would enable.  The
thread David referenced only talks about side-effects on pg_dump,
but there are many other applications that would be just as broken
if we restricted this.


Thank you for information.
I understood community's concern and policy.

Regards,
Shigeo Hirose

Re: timestamptz, local time in the future, and Don't do it wiki

[Max Nikulin]

On 28/01/2023 01:03, Peter J. Holzer wrote:

On 2023-01-27 19:12:08 +0700, Max Nikulin wrote:

https://wiki.postgresql.org/wiki/Don%27t_Do_This#Date.2FTime_storage

Could you, please, add a case when the timestamptz type should not be used?
UTC timestamps of forthcoming events may change due to an updates of tzdata
if they really scheduled at specific local time. An example:


Yes. I could argue that this isn't really a "timestamp", though. The
time when the future event will happen isn't fixed yet - it depends on
future decisions (e.g. an update to DST rules or even a complete switch
to a different time zone).


Generally I would agree that another term instead of timestamp may help 
to explain people that timestamps in the future are more complex than 
they usually expect. Do you have an idea what word should be used 
instead? Interestingly EcmaScript proposal for `Temporal` (intended to 
fix issues with `Date` objects) avoids using of "timestamp". "Instant" 
is chosen instead and the reason is confusion caused by discrepancy of 
interpretation by various databases:


https://tc39.es/proposal-temporal/docs/ambiguity.html#understanding-clock-time-vs-exact-time
"Understanding Clock Time vs. Exact Time" in "Time Zones and Resolving 
Ambiguity"


By the way, approach chosen for JavaScript is even more versatile than 
the one implemented in Python. Instead of just `fold={0,1}` `datetime` 
object field, conversion of local time + TZ identifier may be controlled 
by `disambiguation={earlier,later,compatible,reject}`. If time string 
contains both offset and TZ identifier than another options are 
available: `offset={use,ignore,prefer,reject}`. However timestamp 
property in some cases may be convenient as well.


https://peps.python.org/pep-0495/
"PEP 495 – Local Time Disambiguation"

What I do not like with distinction of known UTC time vs. UTC time that 
might change is that in real applications the difference might be quite 
subtle. The same event, e.g. a conference with local and remote 
(on-line) participants may be scheduled in both ways: in respect to 
local time zone and with fixed UTC time. Moreover the committee may 
reconsider their early decision. I am in doubts if it is reasonable to 
use 2 tables with different column types (timestamptz for UTC or strings 
for local time and TZ) and to move entry between these tables just 
because time zone is changed from fixed UTC offset to a location-based 
one or vice versa.

Re: Best Open Source OS for Postgresql

[Surya Widyanto]

Hi,

From 2019, I've been using AlmaLinux from version 8.4 up to now version 
8.7. This January, I've been trying to upgrade PostgreSQL from v14.6 to 
v15.1.
When I install PostgreSQL v14.6 along side with v15.1 in the same 
server, it run without trouble at all, but because I'm using the PostGIS 
extension, there is one package *ogr_fdw_14 *than cannot be along side 
with the *ogr_fdw_15 *version, specifically on this file:


Error: Transaction test error:

file /usr/lib/.build-id/a0/a43182caa55ed9d3562e2d9b74a1dda150e2c4 from
install of ogr_fdw_15-1.1.3-1.rhel8.x86_64 conflicts with file from
package ogr_fdw_14-1.1.3-1.rhel8.x86_64

because of this problem, I'm currently stuck on upgrading the PostgreSQL 
to v15.1. Last week, on my development server I can install the 
*ogr_fdw_15 *on by using manual extraction of the RPM package to each 
individual directory with exception the conflicted file, and the 
extension is successfully installed and detected on the PostgreSQL 
v15.1.  Next weekend, I planned on upgrading the production server to 
v15.1 using the same method, unless anyone here can show me other method 
than manually extracting the RPM package.


May be someone here on this mailing-list have access to the package 
manager that build the *ogr_fdw_14 *and *ogr_fdw_15* package, and 
release the package update to resolve the conflicted file, I'll be 
waiting for the package release update before I rollout the update to my 
production server.


Best Regards,
Surya Widyanto


On 31-01-2023 15:02, Giovanni Biscontini wrote:

Hello everyone,
 we're looking for a Open Source alternative to Rhel for our VM server 
dedicated to Postgresql (14->15) installations. We're testing Alma, 
Rocky, and Oracle distributions as they're compatible with Rhel 
package systems.

Can you share your experience on a similar choice?
Thanks in advance and best regards, Giovanni

--

*Cordiali Saluti - Best Regards*


***Dott. Giovanni Biscontini*

* [Divisone Software]*

**WEB: https://www.es2000.it 



Privacy e riservatezza: il presente messaggio, così come i relativi 
allegati, contengono dati ed informazioni da considerarsi strettamente 
riservate ed è indirizzato esclusivamente al destinatario sopra 
indicato, il quale è l'unico autorizzato a trattarlo in osservanza 
delle norme del Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque 
ricevesse questo messaggio per errore di evitare di copiarlo, 
divulgarlo, distribuirlo a terzi e di dare notizia al mittente 
dell’errato invio, distruggendone poi l'eventuale copia cartacea e la 
copia in formato elettronico.


Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 
del suddetto decreto tra cui quelli di accesso, rettifica, 
aggiornamento, opposizione al trattamento e cancellazione


*Please, print this e-mail only if necessary*

Re: Best Open Source OS for Postgresql

[hubert depesz lubaczewski]

On Tue, Jan 31, 2023 at 09:02:53AM +0100, Giovanni Biscontini wrote:
> Hello everyone,
>  we're looking for a Open Source alternative to Rhel for our VM server
> dedicated to Postgresql (14->15) installations. We're testing Alma, Rocky,
> and Oracle distributions as they're compatible with Rhel package systems.
> Can you share your experience on a similar choice?
> Thanks in advance and best regards, Giovanni

It will be os that you know.

I, for one, love all things debian based, so I tend to use debian or
ubuntu. It helps that the Pg apt repo is absolutely AMAZING.

depesz

Re: Best Open Source OS for Postgresql

[hubert depesz lubaczewski]

On Tue, Jan 31, 2023 at 01:18:08PM +0100, Marc Millas wrote:
> Did you check postgis debian repo? ??

Not sure why:
1. you ask me that
2. you ask me that off list

but no, i haven't.

depesz

Re: toast useless

[Marcos Pegoraro]

Simon Riggs  escreveu:

> On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro  wrote:
> >>
> >> What problem do they cause you?
> >
> >
> > They don't cause any problem, I was just trying to get my database as
> clean as possible.
> > I have some thousands of these toast tables with 8k bytes, so I was
> trying to eliminate them
> > But if there is no way, ok
>
> They might be optimized away one day, but for now, they are essential.
>

When version 16 comes in it´ll be possible to define [1] field STORAGE on
CREATE TABLE step.
I have some thousands of toast files which are completely useless on my
database, so how to remove them.

pg_upgrade ? Those unneeded toast files will be dropped automatically ?
If not, if I do "alter table x alter column y set storage main" then toast
file will still exist ?

[1]
https://www.postgresql.org/message-id/flat/de83407a-ae3d-a8e1-a788-920eb334f...@sigaev.ru

Download file from COPY ... TO with pgadmin

[Marco Lechner]

Hi,

Using COPY …TO it is possible to store e.g. the “value” of a bytea cell in a 
directory on the server. E.g. by this:

COPY (SELECT content FROM d_doc WHERE id = 'x123456') TO 
CONCAT('/tmp/mydoc.pdf’) (FORMAT binary);

As we do not have access to the postgresql fileserver (ssh, …), but can SELECT 
the value of the bytea cell, is It possible to download the bytea directly into 
a file on a client computer when using pgadmin? Is there a solution with the 
COPY … TO command, any pgadmin specific feature or any other simple solution?

Regards
Marco




i.A. Dr. Marco Lechner
Leiter Fachgebiet RN 1 │ Head RN 1

--
Bundesamt für Strahlenschutz │ Federal Office for Radiation Protection
Koordination Notfallschutzsysteme │ Coordination Emergency Systems │ RN 1
Rosastr. 9
D-79098 Freiburg

Tel.: +49 30 18333-6724
E-Mail: mlech...@bfs.de
www.bfs.de
🌐 Besuchen Sie unsere Website, folgen Sie uns auf 
Twitter und 
abonnieren Sie unseren 📢 Newsletter.
🔒 Informationen zum Datenschutz gemäß Artikel 
13 DSGVO
💚 E-Mail drucken? Lieber die Umwelt schonen!

--
Hinweis zu Anhängen die auf .p7m/.p7c/.p7s oder .asc/.asc.sig enden:
Die .p7?- und .asc-Dateien sind ungefährliche Signaturdateien (digitale 
Unterschriften). In E-Mail-Clients mit S/MIME Konfiguration (.p7?) oder 
PGP-Erweiterung (.asc) dienen sie zur:
- Überprüfung des Absenders
- Überprüfung einer evtl. Veränderung des Inhalts während der Übermittlung über 
das Internet
Die Signaturdateien können ebenso dazu verwendet werden dem Absender dieser 
Signatur eine E-Mail mit verschlüsseltem Inhalt zu senden. In E-Mail-Clients 
ohne S/MIME Konfiguration oder PGP-Erweiterung erscheinen die Dateien als 
Anhang und können ignoriert werden.

Re: Best Open Source OS for Postgresql

[Marc Millas]

Sorry for inappropriate "reply".

if you do check the debian postgis repo, you ll find that its NOT possible
to choose a postgis version.
its possible for postgis 2.4 and 2.5, then ALL 3.x versions are
inaccessible but one, that did change from time to time.
(you MUST ask for postgis 3 without being able to ask for 3.0 or 3.1 or...
its like asking for postgres 9 without .5 or .6)
2 of my customers reverse to a RH family linux because they have been
unable to install the requested postgres/postgis version on debian.
when I did ask the team, the reply was: we cannot package for all cross
possibilities (ie. 5 postgres x 6 postgis, less some impossibilities
according to postgis matrix)

so...


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com



On Tue, Jan 31, 2023 at 1:23 PM hubert depesz lubaczewski 
wrote:

> On Tue, Jan 31, 2023 at 01:18:08PM +0100, Marc Millas wrote:
> > Did you check postgis debian repo? ??
>
> Not sure why:
> 1. you ask me that
> 2. you ask me that off list
>
> but no, i haven't.
>
> depesz
>

Re: Best Open Source OS for Postgresql

[hubert depesz lubaczewski]

On Tue, Jan 31, 2023 at 02:09:40PM +0100, Marc Millas wrote:
> if you do check the debian postgis repo, you ll find that its NOT possible
> to choose a postgis version.
> its possible for postgis 2.4 and 2.5, then ALL 3.x versions are
> inaccessible but one, that did change from time to time.
> (you MUST ask for postgis 3 without being able to ask for 3.0 or 3.1 or...
> its like asking for postgres 9 without .5 or .6)
> 2 of my customers reverse to a RH family linux because they have been
> unable to install the requested postgres/postgis version on debian.
> when I did ask the team, the reply was: we cannot package for all cross
> possibilities (ie. 5 postgres x 6 postgis, less some impossibilities
> according to postgis matrix)

While this is definitely a factor that has to be considered, OP never
said anything about postgis. So, while it is perfectly possible that
they do, and they have to stick to some version that is not available in
debian/ubuntu, it kinda seems like a jump to assume that.

Best regards,

depesz

pgBackrest Error : authentication method 10 not supported

[Daulat]

Hello,

I am getting an error message " authentication method 10 not supported"
while trying to create stanza on postgres14 after upgrading postgres from
v.10 to Postgres v.14.

I am using pgbackrest version:  v.43 and the scram-sha-256 for hba
authentication.

pgbackrest.conf entry:

 cat /etc/pgbackrest/pgbackrest.conf

[global]
repo1-path=/opt/backups
repo1-retention-full=2

[test14]
pg1-path=/opt/PostgreSQL-14/data
pg1-port=5432
pg1-user=postgres
pg1-socket-path=/tmp


Error Message:

pgbackrest stanza-create --stanza=test14 --log-level-console=info
--log-path=/etc/pgbackrest/log
2023-01-31 08:46:37.683 P00   INFO: stanza-create command begin 2.43:
--exec-id=15887-3e1eecf6 --log-level-console=info
--log-path=/etc/pgbackrest/log --pg1-path=/opt/PostgreSQL-14/data
--pg1-port=5432 --pg1-socket-path=/tmp --pg1-user=postgres
--repo1-path=/opt/backups --stanza=test14
WARN: unable to check pg1: [DbConnectError] unable to connect to
'dbname='postgres' port=5432 user='postgres' host='/tmp'': authentication
method 10 not supported
ERROR: [056]: unable to find primary cluster - cannot proceed
   HINT: are all available clusters in recovery?
2023-01-31 08:46:37.686 P00   INFO: stanza-create command end: aborted with
exception [056]
[root@ip-172-31-43-98 ~]#

Re: Download file from COPY ... TO with pgadmin

[Erik Wienhold]

> On 31/01/2023 14:02 CET Marco Lechner  wrote:
>
> Using COPY …TO it is possible to store e.g. the “value” of a bytea cell in a
> directory on the server. E.g. by this:
>
> COPY (SELECT content FROM d_doc WHERE id = 'x123456') TO 
> CONCAT('/tmp/mydoc.pdf’) (FORMAT binary);
>
> As we do not have access to the postgresql fileserver (ssh, …), but can SELECT
> the value of the bytea cell, is It possible to download the bytea directly
> into a file on a client computer when using pgadmin? Is there a solution with
> the COPY … TO command, any pgadmin specific feature or any other simple 
> solution?

Use psql with \copy which targets the client file system.  pgAdmin 5.4+ can also
launch psql.

--
Erik

Re: pgBackrest Error : authentication method 10 not supported

[Magnus Hagander]

On Tue, Jan 31, 2023 at 2:58 PM Daulat  wrote:

> Hello,
>
> I am getting an error message " authentication method 10 not supported"
> while trying to create stanza on postgres14 after upgrading postgres from
> v.10 to Postgres v.14.
>
> I am using pgbackrest version:  v.43 and the scram-sha-256 for hba
> authentication.
>

This sounds like your libpq is too old, and is not related to backrest
itself.

Make sure you have libpq of at least version 10 as well, and not just the
server. (Exact package name will of course depend on what distribution
you're on, which you didn't specify)

-- 
 Magnus Hagander
 Me: https://www.hagander.net/ 
 Work: https://www.redpill-linpro.com/ 

AW: Download file from COPY ... TO with pgadmin

[Marco Lechner]

Works as expected on both ways. Thnx.

-Ursprüngliche Nachricht-
Von: Erik Wienhold  
Gesendet: Dienstag, 31. Januar 2023 14:58
An: Marco Lechner ; pgsql-general@lists.postgresql.org
Betreff: Re: Download file from COPY ... TO with pgadmin

> On 31/01/2023 14:02 CET Marco Lechner  wrote:
>
> Using COPY …TO it is possible to store e.g. the “value” of a bytea 
> cell in a directory on the server. E.g. by this:
>
> COPY (SELECT content FROM d_doc WHERE id = 'x123456') TO 
> CONCAT('/tmp/mydoc.pdf’) (FORMAT binary);
>
> As we do not have access to the postgresql fileserver (ssh, …), but 
> can SELECT the value of the bytea cell, is It possible to download the 
> bytea directly into a file on a client computer when using pgadmin? Is 
> there a solution with the COPY … TO command, any pgadmin specific feature or 
> any other simple solution?

Use psql with \copy which targets the client file system.  pgAdmin 5.4+ can 
also launch psql.

--
Erik

Re: toast useless

[Laurenz Albe]

On Tue, 2023-01-31 at 09:34 -0300, Marcos Pegoraro wrote:
> Simon Riggs  escreveu:
> > On Tue, 13 Sept 2022 at 13:29, Marcos Pegoraro  wrote:
> > > > 
> > > > What problem do they cause you?
> > > 
> > > 
> > > They don't cause any problem, I was just trying to get my database as 
> > > clean as possible.
> > > I have some thousands of these toast tables with 8k bytes, so I was 
> > > trying to eliminate them
> > > But if there is no way, ok
> > 
> > They might be optimized away one day, but for now, they are essential.
> 
> When version 16 comes in it´ll be possible to define [1] field STORAGE on 
> CREATE TABLE step. 
> I have some thousands of toast files which are completely useless on my 
> database, so how to remove them.
> 
> pg_upgrade ? Those unneeded toast files will be dropped automatically ?
> If not, if I do "alter table x alter column y set storage main" then toast 
> file will still exist ?
> 
> [1] 
> https://www.postgresql.org/message-id/flat/de83407a-ae3d-a8e1-a788-920eb334f...@sigaev.ru

Leave them.  They won't bother you, and they won't occupy much space.

Yours,
Laurenz Albe

SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[Dimitrios Apostolou]

Hello list,

I run a very simple SELECT on a huge table (350M rows, 10 columns of type
integer or timestamp). The server is an old Mac Mini with 4GB RAM and a
slow HDD. All caches are cold either via reboot, or by restarting postgres
and using the `purge` command to flush the OS cache.

The question is why this simple query is taking so long to complete.
Most of the time I see constant 30MB/s read I/O. The memory usage is low
and the system is not swapping or in other ways unhealthy.  Postgres is
not serving any other requests.

Is postgres reading all the table sequentially, just to return 10 rows in
no particular order?  I'm trying to understand what PostgreSQL is doing
and the data access patterns involved.  All feedback is appreciated.


# SELECT version();

 PostgreSQL 15.0 on x86_64-apple-darwin20.6.0, compiled by Apple clang version 
12.0.0 (clang-1200.0.32.29), 64-bit

# EXPLAIN (ANALYZE, VERBOSE,BUFFERS,SETTINGS)  SELECT * FROM test_runs_raw 
LIMIT 10;

 Limit  (cost=0.00..0.29 rows=10 width=42) (actual time=746005.251..746014.910 
rows=10 loops=1)
   Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
   Buffers: shared read=2334526
   I/O Timings: shared/local read=691137.029
   ->  Seq Scan on public.test_runs_raw  (cost=0.00..9250235.80 rows=317603680 
width=42) (actual time=745910.672..745910.677 rows=10 loops=1)
 Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
 Buffers: shared read=2334526
 I/O Timings: shared/local read=691137.029
 Settings: effective_cache_size = '2GB', max_parallel_workers_per_gather = '0', 
work_mem = '64MB'
 Planning:
   Buffers: shared hit=69 read=18
   I/O Timings: shared/local read=265.550
 Planning Time: 271.719 ms
 Execution Time: 746057.424 ms
(14 rows)


Repeating the query returns instantaneously, and EXPLAIN shows shared
hit=14 and no reads. Changing the LIMIT to 100 takes another 3min to
respond, with constant read I/O while it runs:


# EXPLAIN (ANALYZE, VERBOSE,BUFFERS,SETTINGS)  SELECT * FROM test_runs_raw 
LIMIT 100;

 Limit  (cost=0.00..2.91 rows=100 width=42) (actual time=0.030..184692.698 
rows=100 loops=1)
   Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
   Buffers: shared hit=14 read=772427
   I/O Timings: shared/local read=171484.072
   ->  Seq Scan on public.test_runs_raw  (cost=0.00..9250235.80 rows=317603680 
width=42) (actual time=0.029..184692.664 rows=100 loops=1)
 Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
 Buffers: shared hit=14 read=772427
 I/O Timings: shared/local read=171484.072
 Settings: effective_cache_size = '2GB', max_parallel_workers_per_gather = '0', 
work_mem = '64MB'
 Planning Time: 0.083 ms
 Execution Time: 184692.748 ms
(11 rows)


Reruning this is instantaneous again.
Other postgres settings:

shared_buffers = 1024MB
effective_io_concurrency = 0
wal_compression = zstd
wal_recycle = on
min_wal_size = 1GB
max_wal_size = 10GB
checkpoint_timeout = 15min


Bonus question: some time ago the server crashed and rebooted, and after a
long WAL recovery and some hours of idle time, I run the same query.  It
took even longer to respond, and besides reading, there was also a lot
writing involved.  What kind of writes was postgres doing?


# EXPLAIN (ANALYZE, VERBOSE,BUFFERS,SETTINGS)  SELECT * FROM test_runs_raw 
LIMIT 10;

 Limit  (cost=0.00..0.29 rows=10 width=42) (actual 
time=1284114.810..1284114.819 rows=10 loops=1)
   Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
   Buffers: shared hit=47490 read=2287036 dirtied=1809974 written=1762484
   I/O Timings: shared/local read=104.264 write=107258.150
   ->  Seq Scan on public.test_runs_raw  (cost=0.00..9250235.80 rows=317603680 
width=42) (actual time=1283955.130..1283955.135 rows=10 loops=1)
 Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
 Buffers: shared hit=47490 read=2287036 dirtied=1809974 written=1762484
 I/O Timings: shared/local read=104.264 write=107258.150
 Settings: effective_cache_size = '2GB', max_parallel_workers_per_gather = '0', 
work_mem = '64MB'
 Planning Time: 0.545 ms
 Execution Time: 1284114.972 ms
(11 rows)


Relevant settings:

synchronous_commit = off
wal_recycle = on
min_wal_size = 1GB
max_wal_size = 10GB
track_io_timing = on
track_wal_io_timing = on
wal_compression = zstd


Thanks in advance,
Dimitris

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[Dimitrios Apostolou]

I managed to double-paste different subset of my settings. FWIW all of
them are true for all the experiments. Sorry for the confusion.


On Tue, 31 Jan 2023, Dimitrios Apostolou wrote:


Other postgres settings:

shared_buffers = 1024MB
effective_io_concurrency = 0
wal_compression = zstd
wal_recycle = on
min_wal_size = 1GB
max_wal_size = 10GB
checkpoint_timeout = 15min


[...]


Relevant settings:

synchronous_commit = off
wal_recycle = on
min_wal_size = 1GB
max_wal_size = 10GB
track_io_timing = on
track_wal_io_timing = on
wal_compression = zstd

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[David G. Johnston]

On Tue, Jan 31, 2023 at 8:07 AM Dimitrios Apostolou  wrote:


> ->  Seq Scan on public.test_runs_raw  (cost=0.00..9250235.80
> rows=317603680 width=42) (actual time=745910.672..745910.677 rows=10
> loops=1)
>   Output: run_n, test_name_n, workitem_n, started_on, duration_ms,
> test_result_n, test_executable_n, test_function_n, test_datatag_n
>   Buffers: shared read=2334526
>   I/O Timings: shared/local read=691137.029
>

The system has to return 10 live rows to you.  If it needs to search
through that many buffers to find 10 live rows you most likely have a large
bloating problem going on.  Seems like it is time to vacuum full.

David J.

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[Tom Lane]

Dimitrios Apostolou  writes:
> The question is why this simple query is taking so long to complete.

Do you get the same 10 rows when you repeat the command?

On the basis of the limited info you provided, I'm going to guess that

(1) there are huge subranges of the table containing no live rows,
so that a seqscan might have to pass over many blocks before it finds
some to return;

(2) once you do reach an area having live rows, the next SELECT picks
up scanning in that same area due to the effects of
"synchronize_seqscans", so you get immediate answers until you reach
the next desert of dead tuples.

If turning synchronize_seqscans off changes the behavior, that'd
be a good clue that this is the right theory.  As for a real fix,
it might be time for a VACUUM FULL or CLUSTER on that table.

regards, tom lane

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[Dimitrios Apostolou]

On Tue, 31 Jan 2023, David G. Johnston wrote:


On Tue, Jan 31, 2023 at 8:07 AM Dimitrios Apostolou  wrote:
 
      ->  Seq Scan on public.test_runs_raw  (cost=0.00..9250235.80 
rows=317603680 width=42) (actual time=745910.672..745910.677 rows=10 loops=1)
            Output: run_n, test_name_n, workitem_n, started_on, 
duration_ms, test_result_n, test_executable_n, test_function_n, test_datatag_n
            Buffers: shared read=2334526
            I/O Timings: shared/local read=691137.029


The system has to return 10 live rows to you.  If it needs to search through 
that many buffers to find 10 live rows you most likely have a large bloating 
problem going on.  Seems like it is time to vacuum full.


I looked up on how to measure bloat, so I run the query found at [1].

[1] https://wiki.postgresql.org/wiki/Show_database_bloat

The first two rows show huge bloat on the two indices of this table:

... ORDER BY wastedbytes DESC LIMIT 2;
 current_database | schemaname |   tablename   | tbloat | wastedbytes | 
   iname | ibloat | wastedibytes
--++---++-+--++--
 coin | public | test_runs_raw |1.8 | 21742305280 | 
test_runs_raw_pkey   |1.0 |0
 coin | public | test_runs_raw |1.8 | 21742305280 | 
test_runs_raw_idx_workitem_n |0.3 |0
(2 rows)

Is this bloat even affecting queries that do not use the index?

It seems I have to add VACUUM FULL to nightly maintainance. I had run some
schema restructuring (several ADD COLUMN followed by UPDATE SET on all
rows) some days ago, and I was not aware this degraded the table.
Thanks for the useful info!


Dimitris

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[Christophe Pettus]

> On Jan 31, 2023, at 07:40, Dimitrios Apostolou  wrote:
> Is this bloat even affecting queries that do not use the index?

No, but a bloated index often (although not always) goes along with a bloated 
table.

> It seems I have to add VACUUM FULL to nightly maintainance.

I wouldn't go that far; that's basically changing your oil every time you get 
gas.  However, monitoring bloat and either rebuilding the indexes (if they're 
all that's bloated) or using pg_repack [1] periodically is a good practice.

[1] https://github.com/reorg/pg_repack

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[David G. Johnston]

On Tue, Jan 31, 2023 at 8:28 AM Tom Lane  wrote:

> Dimitrios Apostolou  writes:
> > The question is why this simple query is taking so long to complete.
>
> Do you get the same 10 rows when you repeat the command?
>
> On the basis of the limited info you provided, I'm going to guess that
>
> (1) there are huge subranges of the table containing no live rows,
> so that a seqscan might have to pass over many blocks before it finds
> some to return;
>

It feels like there is room for improvement here using table statistics and
the visibility map to significantly reduce the number of pages retrieved
that turn out to be all dead.  A limit without an order is not a query to
optimize for of course...


> (2) once you do reach an area having live rows, the next SELECT picks
> up scanning in that same area due to the effects of
> "synchronize_seqscans", so you get immediate answers until you reach
> the next desert of dead tuples.
>

Interesting, I was assuming that the behavior on repeated calls was more
due to intelligent buffer pool usage.  When doing a sequential scan (I
suppose the key word being sequential...) the system decides which pages to
fetch before checking to see if they are already in shared buffers (as
opposed to returning the ones in shared buffers first then fetching more if
needed)?

David J.

Re: Re: How could I elog the tupleTableSlot to the fronted terminal?

[jack...@gmail.com]

>On 2023-Jan-30, jack...@gmail.com wrote:



>



>> For example, I use "insert into t values(1)"; and I 'll get a tupleTableSlot,



>> 



>> And Now I want to get the real data , that's 1, and then use elog() func



>> to print it. Could you give me some codes to realize that? futhermore,



>> what If the data type is text or other types? What do I need to change?



>



>Maybe have a look at the 'debugtup()' function.  It doesn't do exactly



>what you want, but it may inspire you to write the code you need.



>



>-- 



>Álvaro Herrera   48°01'N 7°57'E  —  https://www.EnterpriseDB.com/



>"All rings of power are equal,



>But some rings of power are more equal than others."



> (George Orwell's The Lord of the Rings)


I use the debugtup to print, and I find out there are "printf", it doesn't 
print anything
to the terminal. I need to know how to use this debugtup func. I think I use it 
as a mistake
--

jack...@gmail.com

A Small psql Suggestion

[Raymond Brinzer]

Greetings,

There is (for me) a small speed bump in psql.  I think it's worth
mentioning, minor though it is, because psql is such a polished tool
generally, and because it's something which affects me many, many times a
day.

As it is, \d is a shortcut for \dtmvs.  What I actually want to see, on a
regular basis, are my relations:  \dtmv.  Most of the time, the sequences
are clutter.  If my habits are like most people's in this (and I suspect
they are), excluding sequences from \d would optimize for the common case.

Just $0.02, submitted for your gracious consideration.

-- 
Ray Brinzer

RE: [EXT] DBeaver session populating pg_stat_activity.backend_xmin

[Dirschel, Steve]

Hi Christophe,

Thanks for the reply.   I am fairly new to Postgres and based on your reply 
below I am not understanding something.  Please see my test case below.  I can 
show where a session is "idle in transaction" but backend_xmin is NOT populated 
and I show vacuuming a table with 10 dead rows will work fine.  I then show a 
session is "idle in transaction" with backend_xmin populated and vacuuming a 
table with 10 dead rows will not work because of that session having 
backend_xmin populated.  Note in all cases here these sessions have not 
executed any DML.  Based on your reply below in both of these cases the vacuum 
should not have been able to cleanup these 10 dead rows.  What am I missing 
here?  

Regards
Steve

> -Original Message-
> From: Christophe Pettus  
> Sent: Wednesday, January 25, 2023 4:49 PM
> To: Dirschel, Steve 
> Cc: Thomas Kellerer ; pgsql-general@lists.postgresql.org
> Subject: Re: [EXT] DBeaver session populating pg_stat_activity.backend_xmin



> > On Jan 25, 2023, at 14:21, Dirschel, Steve 
> >  wrote:
> > From my perspective "idle in transaction" isn't necessarily a problem 
> > (although I don't like seeing sessions sitting like that for a long time).  
> > The problem is when pg_stat_activity.backend_xmin is populated-  that can 
> > prevent autovacuum from cleaning up old records.

> I don't think that analysis is quite correct.  There's nothing about 
> backend_xmin that blocks tuple cleanup *if the transaction has been 
> committed*.  The problem is that the session is sitting in idle in 
> transaction state, and *that* blocks tuple cleanup.  The fix is to not leave 
> sessions open in idle in transaction state.


Test case:

create table test1(a numeric, b numeric);

***
* TEST #1 *
***

Dbeaver setup:  
Connection setting, Initialization, Auto-commit not checked 
Connection setting, Metadata, Open separate connection for metadata 
read is checked.

Connect to the database through DBeaver.  Here are the 2 sessions:

select pid, application_name, xact_start, backend_xmin, state, query from 
pg_stat_activity where usename = 'postgres' and application_name like 
'DBeaver%';

dbtest=> select pid, application_name, xact_start, backend_xmin, state, query 
from pg_stat_activity where usename = 'postgres' and application_name like 
'DBeaver%';
  pid  |  application_name  | xact_start | backend_xmin | state 
| query
---+++--+---+
 30229 | DBeaver 22.0.0 - Main  ||  | idle  
| SHOW TRANSACTION ISOLATION LEVEL
 30231 | DBeaver 22.0.0 - Metadata  ||  | idle  
| SELECT t.oid,t.*,c.relkind,format_type(nullif(t.typbasetype, 0), t.typtypmod) 
as base_type_name, d.description+
   |||  |   
| FROM pg_catalog.pg_type t 
+
   |||  |   
| LEFT OUTER JOIN pg_catalog.pg_type et ON et.oid=t.typelem 
+
   |||  |   
| LEFT OUTER JOIN pg_catalog.pg_class c ON c.oid=t.typrelid 
+
   |||  |   
| LEFT OUTER JOIN pg_catalog.pg_description d ON t.oid=d.objoid 
+
   |||  |   
| WHERE t.typname IS NOT NULL   
+
   |||  |   
| AND (c.relkind IS NULL OR c.relkind = 'c') AND (et.typcategory IS NULL OR 
et.typcategory <> 'C')

***  Note neither session is in a transaction.  State for both is idle.

Leave the 2 DBeaver sessions as is.  In psql I run this:

dbtest=> do $$
dbtest$> begin
dbtest$>for cnt in 1..10 loop
dbtest$> insert into test1 values (cnt, cnt);
dbtest$> delete from test1;
dbtest$>end loop;
dbtest$> end; $$
dbtest-> ;
DO
dbtest=>
dbtest=> VACUUM (VERBOSE) test1;
INFO:  vacuuming "public.test1"
INFO:  table "test1": removed 10 dead item identifiers in 1 pages
INFO:  table "test1": found 10 removable, 0 nonremovable row versions in 1 out 
of 1 pages   <--- the 10 dead rows are removable and removed.  
This is expected.
DETAIL:  0 dead row versions cannot be removed yet, oldest xmin: 19368520
Skipped 0 pages due to buffer pins, 0 frozen pages.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO:  table "test1": tr

Re: Download file from COPY ... TO with pgadmin

[Adrian Klaver]

On 1/31/23 05:02, Marco Lechner wrote:

Hi,

Using COPY …TO it is possible to store e.g. the “value” of a bytea cell 
in a directory on the server. E.g. by this:


COPY (SELECT content FROM d_doc WHERE id = 'x123456') TO 
CONCAT('/tmp/mydoc.pdf’) (FORMAT binary);


As we do not have access to the postgresql fileserver (ssh, …), but can 
SELECT the value of the bytea cell, is It possible to download the bytea 
directly into a file on a client computer when using pgadmin? Is there a 
solution with the COPY … TO command, any pgadmin specific feature or any 
other simple solution?


Read the docs:

https://www.pgadmin.org/docs/pgadmin4/6.19/import_export_data.html



Regards

Marco

i.A. Dr. Marco Lechner

Leiter Fachgebiet RN 1 │ Head RN 1

--


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

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[Dimitrios Apostolou]

Thanks for the insight on the internals. Regarding your questions:

On Tue, 31 Jan 2023, Tom Lane wrote:


Do you get the same 10 rows when you repeat the command?


Yes. Just tested with both cold and hot caches. The first 10 rows are
exactly the same, either they return slowly or immediately.


If turning synchronize_seqscans off changes the behavior, that'd
be a good clue that this is the right theory.


Turning it off makes the query slow no matter how many times I re-run it.
The system is doing lots of read I/O with both hot and cold caches. Here
is the EXPLAIN output from the hot cache run (that previously had only 14
hits and no reads):

 Limit  (cost=0.00..0.29 rows=10 width=42) (actual time=620510.813..620510.821 
rows=10 loops=1)
   Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
   Buffers: shared hit=64 read=2334462
   I/O Timings: shared/local read=567846.559
   ->  Seq Scan on public.test_runs_raw  (cost=0.00..9250235.80 rows=317603680 
width=42) (actual time=620510.800..620510.804 rows=10 loops=1)
 Output: run_n, test_name_n, workitem_n, started_on, duration_ms, 
test_result_n, test_executable_n, test_function_n, test_datatag_n
 Buffers: shared hit=64 read=2334462
 I/O Timings: shared/local read=567846.559
 Settings: effective_cache_size = '2GB', max_parallel_workers_per_gather = '0', 
work_mem = '64MB'
 Planning Time: 0.099 ms
 Execution Time: 620510.855 ms


After reading the docs, I'm surprised this setting affects my case given
that I have no parallelism in my setup.



As for a real fix,
it might be time for a VACUUM FULL or CLUSTER on that table.


Regarding CLUSTER, would it help with a seqscan on a bloated table?
Furthermore, given that the table is expected to grow every day by a few
million rows, do you suggest running CLUSTER every night? Will postgres
remember that the rows up to N are clustered, even after appending more
rows?


Dimitris

Re: Best Open Source OS for Postgresql

[Adrian Klaver]

On 1/31/23 05:09, Marc Millas wrote:

Sorry for inappropriate "reply".

if you do check the debian postgis repo, you ll find that its NOT 
possible to choose a postgis version.


Are you talking about the repo hosted by Debian or the Postgres 
community repo's here:


https://www.postgresql.org/download/linux/debian/

its possible for postgis 2.4 and 2.5, then ALL 3.x versions are 
inaccessible but one, that did change from time to time.
(you MUST ask for postgis 3 without being able to ask for 3.0 or 3.1 
or...  its like asking for postgres 9 without .5 or .6)
2 of my customers reverse to a RH family linux because they have been 
unable to install the requested postgres/postgis version on debian.
when I did ask the team, the reply was: we cannot package for all cross 
possibilities (ie. 5 postgres x 6 postgis, less some impossibilities 
according to postgis matrix)


so...


Marc MILLAS
Senior Architect
+33607850334
www.mokadb.com 




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

Re: SELECT * FROM huge_table LIMIT 10; Why does it take more than 10 min to complete, with cold caches

[Tom Lane]

Dimitrios Apostolou  writes:
> On Tue, 31 Jan 2023, Tom Lane wrote:
>> Do you get the same 10 rows when you repeat the command?

> Yes. Just tested with both cold and hot caches. The first 10 rows are
> exactly the same, either they return slowly or immediately.

Hm.  I don't recall exactly how synchronize_seqscans works --- it
definitely changes the point in the table where a seqscan begins
scanning, but I don't remember where/when that point gets updated
(and I'm too lazy to go look).  This result suggests that the first
query advances the table's sync start point to someplace a bit before
the first live tuple, not after all the returned tuples as I'd been
thinking.  It might be that the start point is always the beginning
of a block, so that if you fetch only a few tuples that are all in
the same block then the next attempt will re-fetch them.  If you
increase the LIMIT to say 1000, does the behavior (with
synchronize_seqscans on) change?

>> If turning synchronize_seqscans off changes the behavior, that'd
>> be a good clue that this is the right theory.

> Turning it off makes the query slow no matter how many times I re-run it.

OK, well that's enough of a smoking gun to say that it is the sync
start point that's the relevant state, and not the contents of your
data caches at all.

Anyway, the follow-on discussion makes it clear that you do indeed
have a badly bloated table, and fixing that situation should fix
this performance issue.

regards, tom lane

Logical Replication - "invalid ordering of speculative insertion changes"

[Joe Wildish]

Hello,

We have a logical replication publisher (13.7) and subscriber (14.6) where we 
are seeing the following error on the subscriber. IP address and publication 
name changed, otherwise verbatim:

2023-01-31 15:24:49 UTC:x.x.x.x(56276):super@pubdb:[1040971]: WARNING:  tables 
were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH 
PUBLICATION to subscribe the tables
2023-01-31 15:24:50 UTC::@:[1040975]: LOG:  logical replication apply worker 
for subscription "pub" has started
2023-01-31 15:24:50 UTC::@:[1040975]: ERROR:  could not receive data from WAL 
stream: ERROR:  invalid ordering of speculative insertion changes

This error occurs during the initial set up of the subscription.  We hit 
REFRESH, and then immediately it goes into this error state. It then repeats as 
it is retrying from here onwards and keeps hitting the same error.

My understanding is that the subscriber is performing some kind of reordering 
of the events contained within the WAL message. As it cannot then consume the 
message, it aborts, retries, and gets the same message and errors again.  
Looking in the source code it seems there is only one place where this error 
can be emitted --- reorderbuffer.c:2179.  Moreover I can't tell if this is an 
error that I can be expected to recover from as a user.

We see this error only sometimes. Other times, we REFRESH the subscription and 
it makes progress as one would expect.

Can anyone advise on what we are doing wrong here?

-Joe

How to create a new operator inpg for spec data type?

[jack...@gmail.com]

I need to create a new operator like '<->' and its syntax is that text1 <-> 
text2,
for the usage like this: 'a' <-> 'b' = 'a1b1', so how could I realize this one?
Can you give me some exmaples.

--
jack...@gmail.com

Re: Best Open Source OS for Postgresql

[Tony Shelver]

Copied to the list
On Wed, 1 Feb 2023 at 08:18, Tony Shelver  wrote:

>
>
> On Wed, 1 Feb 2023 at 08:04, Tony Shelver  wrote:
>
>>
>> On Tue, 31 Jan 2023 at 15:10, Marc Millas  wrote:
>>
>>> Sorry for inappropriate "reply".
>>>
>>> if you do check the debian postgis repo, you ll find that its NOT
>>> possible to choose a postgis version.
>>> its possible for postgis 2.4 and 2.5, then ALL 3.x versions are
>>> inaccessible but one, that did change from time to time.
>>> (you MUST ask for postgis 3 without being able to ask for 3.0 or 3.1
>>> or...  its like asking for postgres 9 without .5 or .6)
>>> 2 of my customers reverse to a RH family linux because they have been
>>> unable to install the requested postgres/postgis version on debian.
>>> when I did ask the team, the reply was: we cannot package for all cross
>>> possibilities (ie. 5 postgres x 6 postgis, less some impossibilities
>>> according to postgis matrix)
>>>
>>>
> Maybe I am not understanding this, but have you checked the UbuntuGIS
> source?  I know not pure Debian, but...
>
> Sent before complete:
>
> UbuntuGIS stable shows PostGIS 2.4 and 2.5 available, as well as 3.1 and
> 3.2  :
> https://launchpad.net/%7Eubuntugis/+archive/ubuntu/ppa/+index?batch=75&memo=75&start=75
> Got this link from https://wiki.ubuntu.com/UbuntuGIS  via
> https://postgis.net/install/
> Also note that UbuntuGIS is based off the DebianGIS project:
> https://wiki.debian.org/DebianGis
>
> We run both Ubuntu and Centos servers.  The change to Centos licensing has
> led us to support it only for one specific application that is embedded in
> Centos that we are trying to end-of-life ASAP.  At least Ubuntu server has
> a 5 year support window, and Ubuntu has now announced a limited 'free' Pro
> option for smaller businesses that provides a 10 year window.
>
>
> Regards
>
>
>>

Re: How to create a new operator inpg for spec data type?

[Erik Wienhold]

> On 01/02/2023 06:40 CET jack...@gmail.com  wrote:
>
> I need to create a new operator like '<->' and its syntax is that text1 <-> 
> text2,
> for the usage like this: 'a' <-> 'b' = 'a1b1', so how could I realize this 
> one?
> Can you give me some exmaples.

https://www.postgresql.org/docs/current/sql-createoperator.html

But why use an operator and not just the function that you must create anyway?

--
Erik

Re: Best Open Source OS for Postgresql

[Brad White]

On 1/31/2023 6:23 AM, hubert depesz lubaczewski wrote:

Not sure why:

2. you ask me that off list
In a lot of email clients, it is very easy to accidentally reply to the 
author instead of the list without realizing it.


Hope that helps,
Brad.
--
Quote Signature I talk with clients, find out where their pain points 
are, and solve those.

On-call IT Management for small companies and non-profits.
SCP, Inc.
bwh...@inebraska.com
402-601-7990


Quote of the Day
   There is a huge difference between fathering a child and being a 
father.

   One produces a child. The other produces an adult.
    -- John Eldredge

invisible commit question for sync replication

[qihua wu]

When run a cluster with sync replication, if DML is done on primary, but
primary is isolated from all slave, then the DML will hang, if cancel it
DML, it will say:
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have
been replicated to the standby

So the workflow is
1: commit to local.
2: waiting for ACK from remote sync.

When cancel the DML at step 2. the data are arealy on local, that's why
it's warning.

But when runs an insert which is waiting for remote ACK, and then query
from another session, I didn't find that row. Why this happen? If the
insert is already one locally, whey another session can't read it?

Re: invisible commit question for sync replication

[Julien Rouhaud]

Hi,

On Wed, Feb 01, 2023 at 02:52:49PM +0800, qihua wu wrote:
> When run a cluster with sync replication, if DML is done on primary, but
> primary is isolated from all slave, then the DML will hang, if cancel it
> DML, it will say:
> WARNING:  canceling wait for synchronous replication due to user request
> DETAIL:  The transaction has already committed locally, but might not have
> been replicated to the standby
>
> So the workflow is
> 1: commit to local.
> 2: waiting for ACK from remote sync.
>
> When cancel the DML at step 2. the data are arealy on local, that's why
> it's warning.
>
> But when runs an insert which is waiting for remote ACK, and then query
> from another session, I didn't find that row. Why this happen? If the
> insert is already one locally, whey another session can't read it?

It works as expected for me, are you sure both sessions are actually connected
to the same server and/or querying the same table?

[1456]rjuju@127.0.0.1:14295) rjuju=# select * from tt;
 id | val
+-
(0 rows)

[1456]rjuju@127.0.0.1:14295) rjuju=# insert into tt select 1;
^CCancel request sent
WARNING:  01000: canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been 
replicated to the standby.
LOCATION:  SyncRepWaitForLSN, syncrep.c:287
INSERT 0 1

[1456]rjuju@127.0.0.1:14295) rjuju=# select pg_backend_pid(), * from tt;
 pg_backend_pid | id |  val
++
   1456 |  1 | 
(1 row)


and another session:

[3327]rjuju@127.0.0.1:14295) rjuju=# select pg_backend_pid(), * from tt;
 pg_backend_pid | id |  val
++
   3327 |  1 | 
(1 row)

Re: invisible commit question for sync replication

[David G. Johnston]

On Wednesday, February 1, 2023, Julien Rouhaud  wrote:

> Hi,
>
> On Wed, Feb 01, 2023 at 02:52:49PM +0800, qihua wu wrote:
> > When run a cluster with sync replication, if DML is done on primary, but
> > primary is isolated from all slave, then the DML will hang, if cancel it
> > DML, it will say:
> > WARNING:  canceling wait for synchronous replication due to user request
> > DETAIL:  The transaction has already committed locally, but might not
> have
> > been replicated to the standby
> >
> > So the workflow is
> > 1: commit to local.
> > 2: waiting for ACK from remote sync.
> >
> > When cancel the DML at step 2. the data are arealy on local, that's why
> > it's warning.
> >
> > But when runs an insert which is waiting for remote ACK, and then query
> > from another session, I didn't find that row. Why this happen? If the
> > insert is already one locally, whey another session can't read it?
>
> It works as expected for me, are you sure both sessions are actually
> connected
> to the same server and/or querying the same table?
>
> [1456]rjuju@127.0.0.1:14295) rjuju=# select * from tt;
>  id | val
> +-
> (0 rows)
>
> [1456]rjuju@127.0.0.1:14295) rjuju=# insert into tt select 1;
> ^CCancel request sent
> WARNING:  01000: canceling wait for synchronous replication due to user
> request
> DETAIL:  The transaction has already committed locally, but might not have
> been replicated to the standby.
> LOCATION:  SyncRepWaitForLSN, syncrep.c:287
> INSERT 0 1
>
> [1456]rjuju@127.0.0.1:14295) rjuju=# select pg_backend_pid(), * from tt;
>  pg_backend_pid | id |  val
> ++
>1456 |  1 | 
> (1 row)
>
>
> and another session:
>
> [3327]rjuju@127.0.0.1:14295) rjuju=# select pg_backend_pid(), * from tt;
>  pg_backend_pid | id |  val
> ++
>3327 |  1 | 
> (1 row)
>
>
>
This wasn’t the question though.  Can the second session see the inserted
row before you cancel the insert that is waiting for sync ack?

Supposedly it can (not able to test myself).  Basically, the primary waits
to make the local transaction visible until either sync ack or until the
wait for sync ack is cancelled.  It doesn’t make sense to make it visible
while waiting for sync ack since that would defeat the very behavior sync
ack provides for.

David J.