AIX and EAGAIN on open()

[Christoph Berg]

Hello,

a customer running PG on AIX [1] is occasionally seeing "Resource
temporarily unavailable" (EAGAIN) returned by open() calls:

[1] We have PostgreSQL 11.13 on powerpc-ibm-aix7.2.5.0, compiled by 
/opt/IBM/xlc/13.1.0/bin/xlc, 64-bit

2022-05-19 03:28:13 CEST:127.0.0.1(63265):x@x:[64029168]: ERROR:  could not 
open file "base/16401/935915821_fsm": Resource temporarily unavailable
2022-05-19 03:28:13 CEST:127.0.0.1(63265):x@x:[64029168]: CONTEXT:  SQL 
statement "INSERT INTO s[...]"
PL/pgSQL function s...() line 12 at SQL statement
2022-05-19 03:28:13 CEST:127.0.0.1(63265):x@x:[64029168]: STATEMENT:  PREPARE 
... AS insert into ...


2022-04-16 01:45:31 CEST:127.0.0.1(58946):x@x:[20906970]: ERROR:  could not 
access status of transaction 0
2022-04-16 01:45:31 CEST:127.0.0.1(58946):x@x:[20906970]: DETAIL:  Could not 
open file "pg_subtrans/6158": Resource temporarily unavailable.
2022-04-16 01:45:31 CEST:127.0.0.1(58946):x@x:[20906970]: STATEMENT:  PREPARE 
... AS update ...


2020-12-01 09:24:30 CET:127.0.0.1(59898):x@x:[6227520]: ERROR:  could not 
access status of transaction 0
2020-12-01 09:24:30 CET:127.0.0.1(59898):x@x:[6227520]: DETAIL:  Could not open 
file "pg_subtrans/AC9E": Resource temporarily unavailable.
2020-12-01 09:24:30 CET:127.0.0.1(59898):x@x:[6227520]: STATEMENT:  PREPARE ... 
AS DELETE FROM 


open() should not return EAGAIN as per POSIX [2],

[2] 
https://pubs.opengroup.org/onlinepubs/9699919799/functions/open.html#tag_16_357_05

and the AIX documentation says it would only return EAGAIN if O_TRUNC
is used [3], but as far as I can tell, PG does not use that flag.

[3] 
https://www.ibm.com/docs/en/aix/7.2?topic=o-open-openat-openx-openxat-open64-open64at-open64x-open64xat-creat-creat64-subroutine

IBM's reply to the issue back in December 2020 was this:

  The man page / infocenter document is not intended as an exhaustive
  list of all possible error codes returned and their circumstances.
  "Resource temporarily unavailable" may also be returned for
  O_NSHARE, O_RSHARE with O_NONBLOCK.

Afaict, PG does not use these flags either.

We also ruled out that the system is using any anti-virus or similar
tooling that would intercept IO traffic.

Does anything of that ring a bell for someone? Is that an AIX bug, a
PG bug, or something else?

Christoph
-- 
Senior Consultant, Tel.: +49 2166 9901 187
credativ GmbH, HRB Mönchengladbach 12080, USt-ID-Nummer: DE204566209
Trompeterallee 108, 41189 Mönchengladbach
Geschäftsführung: Dr. Michael Meskes, Geoff Richardson, Peter Lilley
Unser Umgang mit personenbezogenen Daten unterliegt folgenden
Bestimmungen: https://www.credativ.de/datenschutz

RE: Postgresql error : PANIC: could not locate a valid checkpoint record

[Mahendrakar, Prabhakar - Dell Team]

Thanks for the response.
Yes, we have taken care of proper shut down of Postgres before initiating the 
Upgrade.
pg_resetwal - I have read that using pg_resetwal may cause the Database more 
inconsistent and should be used only as a last resort.

Also this problem ( checkpoint related issue -could not locate a valid 
checkpoint record ) is not happening frequently. This issue is seen with the 
large size of Data base.
Please let me know if you require any more information.

Thanks,
Prabhakar

From: Mateusz Henicz 
Sent: Friday, June 17, 2022 3:39 PM
To: Mahendrakar, Prabhakar - Dell Team
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Postgresql error : PANIC: could not locate a valid checkpoint 
record


[EXTERNAL EMAIL]
Assuming you have shut down your postgres properly before upgrading, it should 
be safe for you to run pg_resetwal.
https://www.postgresql.org/docs/current/app-pgresetwal.html 
[postgresql.org]
It should help in this case.

pt., 17 cze 2022 o 12:03 Mahendrakar, Prabhakar - Dell Team 
mailto:prabhakar.mahendr...@dellteam.com>> 
napisał(a):
Yes, We are using the pg_upgrade utility of Postgres.

From: Mateusz Henicz mailto:mateuszhen...@gmail.com>>
Sent: Friday, June 17, 2022 3:31 PM
To: Mahendrakar, Prabhakar - Dell Team
Cc: 
pgsql-general@lists.postgresql.org
Subject: Re: Postgresql error : PANIC: could not locate a valid checkpoint 
record


[EXTERNAL EMAIL]
Hi,
Have you done pg_upgrade post Postgres 13 installation?

https://www.postgresql.org/docs/13/pgupgrade.html 
[postgresql.org]

Cheers,
Mateusz

pt., 17 cze 2022 o 11:20 Mahendrakar, Prabhakar - Dell Team 
mailto:prabhakar.mahendr...@dellteam.com>> 
napisał(a):
Hello,

Good Morning !

We are facing checkpoint related issues from PostGreSQL 13.4 ( could not locate 
a valid checkpoint record) and Postgres service fails to come up.

LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 
4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit
LOG:  listening on IPv4 address "127.0.0.1", port 9003
LOG:  listening on IPv4 address "10.xx.xx.x..", port 9003
LOG:  listening on Unix socket "/tmp/.s.PGSQL.9003"
LOG:  database system was shut down at 2022-06-09 10:19:24 CEST
LOG:  invalid primary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 8773) was terminated by signal 6: Aborted
LOG:  aborting startup due to startup process failure
LOG:  database system is shut down

This issue is seen in both Windows and Linux OS platforms.

To Brief on the Scenario: Our product say example with Version A1 uses Postgres 
10 and in the latest version of our product (Say A2) we upgraded the Postgres 
to 13.
   When we try to upgrade our Product 
through InstallAnyWhere from A1 to A2, Postgres service fails with above 
mentioned error.

 Could you please suggest the probable cause of the issue. Let us know if you 
require any more information.

Thanks,
Prabhakar



Internal Use - Confidential


Internal Use - Confidential


Internal Use - Confidential

Re: Index creation

[Jeff Janes]

On Mon, Jun 20, 2022 at 1:17 AM Дмитрий Иванов 
wrote:

> Your statement seems obvious to me. But what I see doesn't seem like a
> conscious choice. It turns out that it is better to have a lighter
> general-purpose index than to strive to create a target covering index for
> a certain kind of operation.
>

If both indexes are expected to be hit only once in the query and return
only one row, their expected costs will be the same.  In this case, the tie
is broken arbitrarily, and that often means the most-recently created index
will get chosen.

As the expected number of leaf page accesses in a given query goes up, the
smaller index will start to look less expensive.

Cheers,

Jeff

>

Re: A error happend when I am clone the git repository

[Magnus Hagander]

On Mon, Jun 20, 2022 at 6:48 AM Tom Lane  wrote:

> Adrian Klaver  writes:
> > On 6/19/22 20:33, Wen Yi wrote:
> >> |[beginnerc@fedora Research]$ git clone
> >> ||https://git.postgresql.org/git/postgresql.git
> >> |
> >> |Cloning into 'postgresql'...|
> >> |remote: Enumerating objects: 30747, done.|
> >> |remote: Counting objects: 100% (30747/30747), done.|
> >> |remote: Compressing objects: 100% (13431/13431), done.|
> >> |error: RPC failed; curl 92 HTTP/2 stream 3 was not closed cleanly
> >> before end of the underlying stream|
>
> > I have to believe it is related to bleeding edge OS Fedora 36 in
> > combination with latest Git 2.36.1.
>
> No, I think it's more about this:
>
> >> I check the download speed,that is about 220kb/s.
>
> I've seen this failure multiple times on very slow machines.
> I think there's some sort of connection timeout somewhere in
> the git.postgresql.org infrastructure, causing a "git clone"
> that takes more than a couple of minutes to fail.  I've
> complained about it before, but we've not isolated the cause.
>

The last time we debugged it pretty much the only conclusion we managed to
come to I think was that it is *not* in the git.postgresql.org
infrastructure. I tried it from *many* different locations and it worked
fine from all of them, even when artificially slowing it down to something
much slower.

So yes, there is *something*, but it's not with in the pg.org
infrastructure.

One thing we got to work that time, I think, was to run:
git config --global http.version HTTP/1.1

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

accessing postgres from c++

[Rino Mardo]

hi. i found odbc.postgresql.org when i was looking for a way to hook up my
c++ project to postgresql server. installed it and tested to be successful
in connection. this was when i had postgresql running from a virtualbox vm
in Ubuntu.

i installed a postgresql container using Docker for Windows in my laptop.
from a Windows terminal i can connect to the postgresql container and from
there use psql client to create database.

when postgresql was running in an Ubuntu vm in virtualbox, i wasn't able to
test the c++ connection. now i want to but can't find a way how. either
from python or any programming language.

i guess my problem is more of the programming than db server side but i
thought to try asking here in case someone can help. thanks.



regards,

Re: Index creation

[Дмитрий Иванов]

Yes, you are right. The presented index usage data is caused by recursive
queries, which check the integrity of hierarchical structures from the
bottom up. Your explanation has clarified what is going on. Thank you.
My experiments with indexes are caused by the appearance of significant
variance (1-180ms) in these operations, which appeared when I switched from
version 12 to 14, which increased the checking time by ~250% with the
existing implementation and ~40% after I rewrote the functions to run as
dynamic SQL. This decision was due to the obvious correlation between the
level of variance and the primary dataset obtained when the non-dynamic
function was first called. I don't think my communication experience will
allow me to properly describe the problem, but the information I received
was useful. Thank you.
--
Regards, Dmitry!


пн, 20 июн. 2022 г. в 23:23, Jeff Janes :

> On Mon, Jun 20, 2022 at 1:17 AM Дмитрий Иванов 
> wrote:
>
>> Your statement seems obvious to me. But what I see doesn't seem like a
>> conscious choice. It turns out that it is better to have a lighter
>> general-purpose index than to strive to create a target covering index for
>> a certain kind of operation.
>>
>
> If both indexes are expected to be hit only once in the query and return
> only one row, their expected costs will be the same.  In this case, the tie
> is broken arbitrarily, and that often means the most-recently created index
> will get chosen.
>
> As the expected number of leaf page accesses in a given query goes up, the
> smaller index will start to look less expensive.
>
> Cheers,
>
> Jeff
>
>>

Re: accessing postgres from c++

[Laurenz Albe]

On Tue, 2022-06-21 at 10:56 +0800, Rino Mardo wrote:
> hi. i found odbc.postgresql.org when i was looking for a way to hook up my 
> c++ project to postgresql server.
> installed it and tested to be successful in connection. this was when i had 
> postgresql
> running from a virtualbox vm in Ubuntu. 
> 
> i installed a postgresql container using Docker for Windows in my laptop. 
> from a Windows terminal i can
> connect to the postgresql container and from there use psql client to create 
> database.
> 
> when postgresql was running in an Ubuntu vm in virtualbox, i wasn't able to 
> test the c++ connection.
> now i want to but can't find a way how. either from python or any programming 
> language.
> 
> i guess my problem is more of the programming than db server side but i 
> thought to try asking here
> in case someone can help. thanks.

I am surprised that you choose to be fettered by the constraints of a generic 
API like ODBC.
For me, that only makes sense if you want to be portable to different databases.

I would use the powerful C API of libpq, or, if you want some C++ boilerplate 
around it,
use libpqxx.

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