Long living and expiring locks?
I have multiple processes connecting to the same database. In order to prevent race conditions the I would like the process to issue a SELECT FOR UPDATE to lock the records that need to be processed. The problem is that the locks are not inside of a single translation. The process is multi step. Records are selected and presented to the user, the user does some stuff and submits a form, the submitted form updates those records and frees the lock. Ideally I would do a select for update when the form is shown to the user but with an expiry time so that if the user decides to move on and not submit any changes the locks will expire. I could do this with a locked_at field that I can update on select but I would prefer to let postgres handle the locks if possible.
Re: No default for (user-specific) service file location on Windows?
On Thu, May 19, 2022 at 06:38:46PM -0400, Tom Lane wrote: > Julien Rouhaud writes: > > This time with the patch. > > Pushed, with some minor twiddling to make the .pgpass and .pg_service.conf > descriptions more alike. I figured that the .pgpass docs are fine since > (surely) many more people have looked at those passages and not > complained, so I made sure that the service file descriptions were worded > comparably. Thanks a lot!
Re: No default for (user-specific) service file location on Windows?
Julien Rouhaud writes: > This time with the patch. Pushed, with some minor twiddling to make the .pgpass and .pg_service.conf descriptions more alike. I figured that the .pgpass docs are fine since (surely) many more people have looked at those passages and not complained, so I made sure that the service file descriptions were worded comparably. regards, tom lane
Re: No default for (user-specific) service file location on Windows?
On Thu, May 19, 2022 at 11:53:22PM +0800, Julien Rouhaud wrote: > On Thu, May 19, 2022 at 10:11:06AM -0400, Tom Lane wrote: > > I do not like your proposed wording, as it seems way too dense. > > Can't we avoid the parenthetical remarks (plural) inside a sub-clause? > > You're asking the reader to keep track of about three levels of > > interrupt. > > Yes, I was a bit unhappy with this wording. > > > Maybe just put the Windows info in a separate sentence: > > > > ... located at ~/.pg_service.conf. > > On Microsoft Windows, it is located at > > %APPDATA%\postgresql\.pg_service.conf, where %APPDATA% means the > > Application Data subdirectory in the user's profile. > > In either case, the location can be overridden by setting ... > > > > ISTR there's precedent elsewhere for doing it about like that. > > I'm fine with it. I still kept the default location full description in both > places as those end up in quite different part of the documentation, which > seems more helpful. This time with the patch. >From 27f69c2f44564b47a8f0caa31f9406468e9e77a9 Mon Sep 17 00:00:00 2001 From: Julien Rouhaud Date: Thu, 19 May 2022 17:42:07 +0800 Subject: [PATCH v2] Document the default location of the PGSERVICEFILE on Windows. Per report from Dominique Devienne. Author: Julien Rouhaud Discussion: https://postgr.es/m/CAFCRh-_mdLrh8eYVzhRzu4c8bAFEBn=rwohomfjcqotscy5...@mail.gmail.com --- doc/src/sgml/libpq.sgml | 18 -- 1 file changed, 12 insertions(+), 6 deletions(-) diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 40035d7656..105b1bd94b 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -7787,9 +7787,12 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) PGSERVICEFILE PGSERVICEFILE specifies the name of the per-user - connection service file. If not set, it defaults - to ~/.pg_service.conf - (see ). + connection service file (see ). If not + set, it defaults to ~/.pg_service.conf. On + Microsoft Windows, it defaults to + %APPDATA%\postgresql\.pg_service.conf (where + %APPDATA% refers to the Application Data + subdirectory in the user's profile). @@ -8161,9 +8164,12 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) Service names can be defined in either a per-user service file or a system-wide file. If the same service name exists in both the user - and the system file, the user file takes precedence. - By default, the per-user service file is located - at ~/.pg_service.conf; this can be overridden by + and the system file, the user file takes precedence. By default, the + per-user service file is located at ~/.pg_service.conf. + On Microsoft Windows, it is located at + %APPDATA%\postgresql\.pg_service.conf (where + %APPDATA% refers to the Application Data subdirectory + in the user's profile). In either case, the location can be overridden by setting the environment variable PGSERVICEFILE. The system-wide file is named pg_service.conf. By default it is sought in the etc directory -- 2.33.1
Re: No default for (user-specific) service file location on Windows?
On Thu, May 19, 2022 at 10:11:06AM -0400, Tom Lane wrote: > I do not like your proposed wording, as it seems way too dense. > Can't we avoid the parenthetical remarks (plural) inside a sub-clause? > You're asking the reader to keep track of about three levels of > interrupt. Yes, I was a bit unhappy with this wording. > Maybe just put the Windows info in a separate sentence: > > ... located at ~/.pg_service.conf. > On Microsoft Windows, it is located at > %APPDATA%\postgresql\.pg_service.conf, where %APPDATA% means the > Application Data subdirectory in the user's profile. > In either case, the location can be overridden by setting ... > > ISTR there's precedent elsewhere for doing it about like that. I'm fine with it. I still kept the default location full description in both places as those end up in quite different part of the documentation, which seems more helpful.
Re: In case of network issues, how long before archive_command does retries
On Thu, 2022-05-19 at 15:43 +0200, Koen De Groote wrote: > On Thu, May 19, 2022 at 9:10 AM Laurenz Albe wrote: > > On Wed, 2022-05-18 at 22:51 +0200, Koen De Groote wrote: > > > When connection is gone or blocked, archive_command fails after the > > > timeout specified > > > by the NFS mount, as expected. (for a soft mount. hard mount hangs, as > > > expected) > > > > > > However, on restoring connection, it's not clear to me how long it takes > > > before the command is retried. > > > > > > Experience says "a few minutes", but I can't find documentation on an > > > exact algorithm. > > > > > > To be clear, the question is: if archive_command fails, what are the > > > specifics of retrying? > > > Is there a timeout? How is that timeout defined? > > > > > > Is this detailed somewhere? Perhaps in the source code? I couldn't find > > > it in the documentation. > > > > > > For detail, I'm using postgres 11, running on Ubuntu 20. > > > > You can find the details in "src/backend/postmaster/pgarch.c". > > > > The archiver will try to archive three times (NUM_ARCHIVE_RETRIES) in an > > interval > > of one second, then back off until it receives a signal, PostgreSQL shutd > > down > > or a minute has passed. > > Thanks for the reply. That would mean the source code is here: > https://github.com/postgres/postgres/blob/REL_11_0/src/backend/postmaster/pgarch.c For release 11.0, yes. > Just to be sure, the "signal" you speak of, this is the result of the command > executed by archive_command? No, that is an operating system signal. PostgreSQL processes communicate by sending signals to each other, and if anybody wakes up the archiver, it will try again. > If my understanding of the code is right, if no SIGTERM or other signal > arrives, it won't ever happen > that a walarchive is skipped if the archive_command fails too many times or > takes too long? It > will simply check again every 60 seconds(PGARCH_AUTOWAKE_INTERVAL) ? Or is > the 60 seconds the point > where it stops trying, waiting for the next time archive_command is invoked? Even if a signal arrives, PostgreSQL will keep trying to archive that same WAL segment that failed until it is done. This is a potential sequence of events: try to archive -> fail sleep 1 second try to archive -> fail sleep 1 second try to archive -> fail sleep 60 seconds try to archive -> fail sleep 1 second try to archive -> fail sleep 1 second try to archive -> fail sleep 60 seconds -> get woken up by a signal after 30 seconds try to archive -> fail sleep 1 second try to archive -> fail get shutdown request -> exit When PostgreSQL restarts, it will continue trying to archive the same segment. > I'm assuming that as long as the file is still in the pg_wal directory and as > long as there is no > ".done" file for that walarchive under pg_wal/archive_status, it will keep > trying forever(or until > someone forcefully switches the timeline with for instance a basebackup)? Yes, it will keep trying, and a timeline switch won't change that. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: No default for (user-specific) service file location on Windows?
Julien Rouhaud writes: > We can't change the default file in older branches obviously, but I'd be a bit > worried about changing the default in the next major version either as it > would > add unnecessary pain for users that do know and rely on the current default. > I > guess we have to live with it, and properly document it. Yeah, agreed. Documentation fix seems like the way to go. I do not like your proposed wording, as it seems way too dense. Can't we avoid the parenthetical remarks (plural) inside a sub-clause? You're asking the reader to keep track of about three levels of interrupt. Maybe just put the Windows info in a separate sentence: ... located at ~/.pg_service.conf. On Microsoft Windows, it is located at %APPDATA%\postgresql\.pg_service.conf, where %APPDATA% means the Application Data subdirectory in the user's profile. In either case, the location can be overridden by setting ... ISTR there's precedent elsewhere for doing it about like that. regards, tom lane
Re: In case of network issues, how long before archive_command does retries
Hello Laurenz, Thanks for the reply. That would mean the source code is here: https://github.com/postgres/postgres/blob/REL_11_0/src/backend/postmaster/pgarch.c Just to be sure, the "signal" you speak of, this is the result of the command executed by archive_command? If my understanding of the code is right, if no SIGTERM or other signal arrives, it won't ever happen that a walarchive is skipped if the archive_command fails too many times or takes too long? It will simply check again every 60 seconds(PGARCH_AUTOWAKE_INTERVAL) ? Or is the 60 seconds the point where it stops trying, waiting for the next time archive_command is invoked? I'm assuming that as long as the file is still in the pg_wal directory and as long as there is no ".done" file for that walarchive under pg_wal/archive_status, it will keep trying forever(or until someone forcefully switches the timeline with for instance a basebackup)? Apologies, I already sent this message once, but only to Laurenz. Sending again to have it in the archives. Regards, Koen On Thu, May 19, 2022 at 9:10 AM Laurenz Albe wrote: > On Wed, 2022-05-18 at 22:51 +0200, Koen De Groote wrote: > > I've got a setup where archive_command will gzip the wal archive to a > directory that is itself an NFS mount. > > > > When connection is gone or blocked, archive_command fails after the > timeout specified by the NFS mount, as expected. (for a soft mount. hard > mount hangs, as expected) > > > > However, on restoring connection, it's not clear to me how long it takes > before the command is retried. > > > > Experience says "a few minutes", but I can't find documentation on an > exact algorithm. > > > > To be clear, the question is: if archive_command fails, what are the > specifics of retrying? Is there a timeout? How is that timeout defined? > > > > Is this detailed somewhere? Perhaps in the source code? I couldn't find > it in the documentation. > > > > For detail, I'm using postgres 11, running on Ubuntu 20. > > You can find the details in "src/backend/postmaster/pgarch.c". > > The archiver will try to archive three times (NUM_ARCHIVE_RETRIES) in an > interval > of one second, then back off until it receives a signal, PostgreSQL shutd > down > or a minute has passed. > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com >
Re: No default for (user-specific) service file location on Windows?
On Thu, May 19, 2022 at 10:57:55AM +0200, Dominique Devienne wrote: > On Thu, May 19, 2022 at 10:17 AM Julien Rouhaud wrote: > > > Or is it instead a lack of symmetry in the implementations of these > > > two mechanisms? > > > > As far as I can see from the code it should be > > %APPDATA%\postgresql\.pg_service.conf > > > > Does this one work? > > Yes it does! Thanks. Ah great! > > There is a discrepancy (apart from the documentation) as the default > > file name is the same on Linux and Windows. > > The doc is explicit about Windows for the password file, > probably should be for the service file as well IMHO. Yeah I totally agree. I'm attaching a patch for that. > It's unfortunate that the naming conventions are different. > Might need to be warned about in the doc too. My $0.02. I agree that the discrepancy is annoying, but I don't think that we usually add this kind of warning. I didn't do anything about it for now. > > Could also be changed to try both on Windows, with or w/o the leading dot, > but I suspect this kind of change won't be considered worth implementing :). We can't change the default file in older branches obviously, but I'd be a bit worried about changing the default in the next major version either as it would add unnecessary pain for users that do know and rely on the current default. I guess we have to live with it, and properly document it. >From 27853c7df7ea03b47e0a7f7b609693f8b0789723 Mon Sep 17 00:00:00 2001 From: Julien Rouhaud Date: Thu, 19 May 2022 17:42:07 +0800 Subject: [PATCH v1] Document the default location of the PGSERVICEFILE on Windows. Per report from Dominique Devienne. Author: Julien Rouhaud Discussion: https://postgr.es/m/CAFCRh-_mdLrh8eYVzhRzu4c8bAFEBn=rwohomfjcqotscy5...@mail.gmail.com --- doc/src/sgml/libpq.sgml | 12 +--- 1 file changed, 9 insertions(+), 3 deletions(-) diff --git a/doc/src/sgml/libpq.sgml b/doc/src/sgml/libpq.sgml index 40035d7656..758e06e610 100644 --- a/doc/src/sgml/libpq.sgml +++ b/doc/src/sgml/libpq.sgml @@ -7788,8 +7788,11 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) PGSERVICEFILE specifies the name of the per-user connection service file. If not set, it defaults - to ~/.pg_service.conf - (see ). + to ~/.pg_service.conf, or + %APPDATA%\postgresql\.pg_service.conf (where + %APPDATA% refers to the Application Data + subdirectory in the user's profile) on Microsoft Windows (see ). @@ -8163,7 +8166,10 @@ myEventProc(PGEventId evtId, void *evtInfo, void *passThrough) system-wide file. If the same service name exists in both the user and the system file, the user file takes precedence. By default, the per-user service file is located - at ~/.pg_service.conf; this can be overridden by + at ~/.pg_service.conf, or + %APPDATA%\postgresql\.pg_service.conf (where + %APPDATA% refers to the Application Data subdirectory + in the user's profile) on Microsoft Windows; this can be overridden by setting the environment variable PGSERVICEFILE. The system-wide file is named pg_service.conf. By default it is sought in the etc directory -- 2.33.1
Re: No default for (user-specific) service file location on Windows?
On Thu, May 19, 2022 at 10:17 AM Julien Rouhaud wrote: > > Or is it instead a lack of symmetry in the implementations of these > > two mechanisms? > > As far as I can see from the code it should be > %APPDATA%\postgresql\.pg_service.conf > > Does this one work? Yes it does! Thanks. > There is a discrepancy (apart from the documentation) as the default > file name is the same on Linux and Windows. The doc is explicit about Windows for the password file, probably should be for the service file as well IMHO. It's unfortunate that the naming conventions are different. Might need to be warned about in the doc too. My $0.02. Could also be changed to try both on Windows, with or w/o the leading dot, but I suspect this kind of change won't be considered worth implementing :). Thanks again Julien, --DD
Re: No default for (user-specific) service file location on Windows?
Hi, On Thu, May 19, 2022 at 3:46 PM Dominique Devienne wrote: > > The doc is explicit about defaults for the password file: > From https://www.postgresql.org/docs/current/libpq-pgpass.html > Linux: ~/.pgpass > Windows: %APPDATA%\postgresql\pgpass.conf > > But for the service file OTOH, only the Linux default is documented: > From: https://www.postgresql.org/docs/current/libpq-pgservice.html > Linux: ~/.pg_service.conf > Windows: N/A > > I tried the obvious %APPDATA%\postgresql\pg_service.conf, with no luck. > > So is this a lack in the documentation for the service file? > Or is it instead a lack of symmetry in the implementations of these > two mechanisms? > > If the latter, any chance the symmetry would be restored? (with > corresponding doc update). As far as I can see from the code it should be %APPDATA%\postgresql\.pg_service.conf Does this one work? There is a discrepancy (apart from the documentation) as the default file name is the same on Linux and Windows.
No default for (user-specific) service file location on Windows?
Hi, The doc is explicit about defaults for the password file: >From https://www.postgresql.org/docs/current/libpq-pgpass.html Linux: ~/.pgpass Windows: %APPDATA%\postgresql\pgpass.conf But for the service file OTOH, only the Linux default is documented: From: https://www.postgresql.org/docs/current/libpq-pgservice.html Linux: ~/.pg_service.conf Windows: N/A I tried the obvious %APPDATA%\postgresql\pg_service.conf, with no luck. So is this a lack in the documentation for the service file? Or is it instead a lack of symmetry in the implementations of these two mechanisms? If the latter, any chance the symmetry would be restored? (with corresponding doc update). I guess the only work-around specific to Windows at this point, if the latter, would be defining the PGSERVICEFILE env-var explicitly? --DD
Re: In case of network issues, how long before archive_command does retries
On Wed, 2022-05-18 at 22:51 +0200, Koen De Groote wrote: > I've got a setup where archive_command will gzip the wal archive to a > directory that is itself an NFS mount. > > When connection is gone or blocked, archive_command fails after the timeout > specified by the NFS mount, as expected. (for a soft mount. hard mount hangs, > as expected) > > However, on restoring connection, it's not clear to me how long it takes > before the command is retried. > > Experience says "a few minutes", but I can't find documentation on an exact > algorithm. > > To be clear, the question is: if archive_command fails, what are the > specifics of retrying? Is there a timeout? How is that timeout defined? > > Is this detailed somewhere? Perhaps in the source code? I couldn't find it in > the documentation. > > For detail, I'm using postgres 11, running on Ubuntu 20. You can find the details in "src/backend/postmaster/pgarch.c". The archiver will try to archive three times (NUM_ARCHIVE_RETRIES) in an interval of one second, then back off until it receives a signal, PostgreSQL shutd down or a minute has passed. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: About pg_basebackup
On Thu, 2022-05-19 at 13:18 +0900, 菊池祐 wrote: > I executed the pg_basebackup command to set up a replication configuration in > postgresql, > but the following message appears and replication didn’t complete. > > 2741/2742 tablespaces (/var/lib/pgsql/9.2/bac12609150596/12609150596 kB > (100%), 2741/2742 tablespaces (/var/lib/pgsql/9.2/bac12609150596/12609150596 > kB (100%), 2742/2742 tablespaces > NOTICE: pg_stop_backup cleanup done, waiting for required WAL segments to be > archived > WARNING: pg_stop_backup still waiting for all required WAL segments to be > archived (60 seconds elapsed) > HINT: Check that your archive_command is executing properly. pg_stop_backup > can be canceled safely, but the database backup will not be usable without > all the WAL segments. > WARNING: pg_stop_backup still waiting for all required WAL segments to be > archived (120 seconds elapsed) I guess that your "archive_command" is hanging or failing. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
