Re: Postgresql active-active nodes in cluster
Hi, * Is that multi master replication is not recommended to do ? * If we can do, can I get any resource for implementing that on PostgreSQL which is open-source? Thanks, Vidyshree H S From: Vidyashree H S Sent: Friday, May 10, 2024 10:48 AM To: Bruce Momjian ; Greg Sabino Mullane Cc: Kashif Zeeshan ; pgsql-general@lists.postgresql.org Subject: Re: Postgresql active-active nodes in cluster Thanks all for your inputs. * Currently I'm working on active-passive(Primary and standby scenario). * I'm preparing one study report on active-active node implementation which talks about active-active nodes background, theoretical explanation, implementation part which includes some trails on this. For that, I'm checking for the resources. Thanks Vidyashree H S From: Bruce Momjian Sent: Friday, May 10, 2024 4:53 AM To: Greg Sabino Mullane Cc: Kashif Zeeshan ; Vidyashree H S ; pgsql-general@lists.postgresql.org Subject: Re: Postgresql active-active nodes in cluster On Thu, May 9, 2024 at 06:33:39PM -0400, Greg Sabino Mullane wrote: > > https://wiki.postgresql.org/wiki/Multimaster > > > That is a pretty old page. There are other solutions, such as pgedge, but the > three most important questions when it comes to active-active replication are: > > * Do you really, really need it? (hint: the answer is no) > * What problem are you trying to solve? > * Are you really willing to suffer the tradeoffs? Agreed, see this: https://momjian.us/main/blogs/pgblog/2018.html#December_24_2018 -- Bruce Momjian https://momjian.us EDB https://enterprisedb.com Only you can decide what is important to you. [EXT]
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Sat, 11 May 2024, David Rowley wrote: On Sat, 11 May 2024 at 13:33, Tom Lane wrote: I do kind of wonder why it's producing both a hashagg and a Unique step --- seems like it should do one or the other. It still needs to make the duplicate groups from parallel workers unique. Range partitioning of the table guarantees that, since the ranges are not overlapping. Dimitris
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Sat, 11 May 2024, David Rowley wrote: On Sat, 11 May 2024 at 13:11, Dimitrios Apostolou wrote: Indeed that's an awful estimate, the table has more than 1M of unique values in that column. Looking into pg_stat_user_tables, I can't see the partitions having been vacuum'd or analyzed at all. I think they should have been auto-analyzed, since they get a ton of INSERTs (no deletes/updates though) and I have the default autovacuum settings. Could it be that autovacuum starts, but never finishes? I can't find something in the logs. It's not the partitions getting analyzed you need to worry about for an ndistinct estimate on the partitioned table. It's auto-analyze or ANALYZE on the partitioned table itself that you should care about. If you look at [1], it says "Tuples changed in partitions and inheritance children do not trigger analyze on the parent table." Thanks In any case, even after the planner decides to execute the terrible plan with the parallel seqscans, why doesn't it finish right when it finds 10 distinct values? It will. It's just that Sorting requires fetching everything from its subnode. Isn't it plain wrong to have a sort step in the plan than? The different partitions contain different value ranges with no overlap, and the last query I posted doesn't even contain an ORDER BY clause, just a DISTINCT clause on an indexed column. Even with bad estimates, even with seq scan instead of index scan, the plan should be such that it concludes all parallel work as soon as it finds the 10 distinct values. And this is actually achieved if I disable parallel plans. Could it be a bug in the parallel plan generation? Dimitris
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Mon, 13 May 2024, Dimitrios Apostolou wrote: On Sat, 11 May 2024, David Rowley wrote: On Sat, 11 May 2024 at 13:11, Dimitrios Apostolou wrote: Indeed that's an awful estimate, the table has more than 1M of unique values in that column. Looking into pg_stat_user_tables, I can't see the partitions having been vacuum'd or analyzed at all. I think they should have been auto-analyzed, since they get a ton of INSERTs (no deletes/updates though) and I have the default autovacuum settings. Could it be that autovacuum starts, but never finishes? I can't find something in the logs. It's not the partitions getting analyzed you need to worry about for an ndistinct estimate on the partitioned table. It's auto-analyze or ANALYZE on the partitioned table itself that you should care about. If you look at [1], it says "Tuples changed in partitions and inheritance children do not trigger analyze on the parent table." Thanks Do I read that correctly, that I have to setup cron jobs to manually analyze partitioned tables? Dimitris
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Tue, 14 May 2024 at 00:28, Dimitrios Apostolou wrote: > > On Sat, 11 May 2024, David Rowley wrote: > > > On Sat, 11 May 2024 at 13:33, Tom Lane wrote: > >> I do kind of wonder why it's producing both a hashagg and a Unique > >> step --- seems like it should do one or the other. > > > > It still needs to make the duplicate groups from parallel workers unique. > > Range partitioning of the table guarantees that, since the ranges are not > overlapping. That assumes the Append won't ever use > 1 worker per subnode, but that's not the case for your plan as the subnodes are "Parallel". That means all the workers could be working on the same subnode which could result in one group being split between 2 or more workers. Parallel Append can also run in a way that the Append child nodes will only get 1 worker each. However, even if that were the case for your plan, we have no code that would skip the final aggregate phase when the DISTINCT / GROUP contains all of the partition key columns. David
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Tue, 14 May 2024 at 00:41, Dimitrios Apostolou wrote: > > On Sat, 11 May 2024, David Rowley wrote: > > It will. It's just that Sorting requires fetching everything from its > > subnode. > > Isn't it plain wrong to have a sort step in the plan than? The different > partitions contain different value ranges with no overlap, and the last > query I posted doesn't even contain an ORDER BY clause, just a DISTINCT > clause on an indexed column. The query does contain an ORDER BY, so if the index is not chosen to provide pre-sorted input, then something has to put the results in the correct order before the LIMIT is applied. > Even with bad estimates, even with seq scan instead of index scan, the > plan should be such that it concludes all parallel work as soon as it > finds the 10 distinct values. And this is actually achieved if I disable > parallel plans. Could it be a bug in the parallel plan generation? If you were to put the n_distinct_inherited estimate back to 200 and disable sort, you should see the costs are higher for the index plan. If that's not the case then there might be a bug. It seems more likely that due to the n_distinct estimate being so low that the planner thought that a large enough fraction of the rows needed to be read and that made the non-index plan appear cheaper. I'd be interested in seeing what the costs are for the index plan. I think the following will give you that (untested): alter table test_runs_raw alter column workitem_n set (n_distinct_inherited=200); analyze test_runs_raw; set enable_sort=0; explain SELECT DISTINCT workitem_n FROM test_runs_raw ORDER BY workitem_n DESC LIMIT 10; -- undo alter table test_runs_raw alter column workitem_n set (n_distinct_inherited=-1); reset enable_sort; David
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Tue, 14 May 2024 at 00:46, Dimitrios Apostolou wrote: > > On Mon, 13 May 2024, Dimitrios Apostolou wrote: > > > On Sat, 11 May 2024, David Rowley wrote: > >> If you look at [1], it says "Tuples changed in partitions and > >> inheritance children do not trigger analyze on the parent table." > > > Do I read that correctly, that I have to setup cron jobs to manually > analyze partitioned tables? It means that auto-analyze won't touch it. Periodically doing an ANALYZE on the partitioned table is probably a good idea. David
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Tue, 14 May 2024, David Rowley wrote: On Tue, 14 May 2024 at 00:41, Dimitrios Apostolou wrote: On Sat, 11 May 2024, David Rowley wrote: It will. It's just that Sorting requires fetching everything from its subnode. Isn't it plain wrong to have a sort step in the plan than? The different partitions contain different value ranges with no overlap, and the last query I posted doesn't even contain an ORDER BY clause, just a DISTINCT clause on an indexed column. The query does contain an ORDER BY, so if the index is not chosen to provide pre-sorted input, then something has to put the results in the correct order before the LIMIT is applied. The last query I tried was: SELECT DISTINCT workitem_n FROM test_runs_raw LIMIT 10; See my message at [1] https://www.postgresql.org/message-id/69077f15-4125-2d63-733f-21ce6eac4f01%40gmx.net Will re-check things and report back with further debugging info you asked for later today. Dimitris
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Tue, 14 May 2024, David Rowley wrote: That assumes the Append won't ever use > 1 worker per subnode, but that's not the case for your plan as the subnodes are "Parallel". That means all the workers could be working on the same subnode which could result in one group being split between 2 or more workers. Didn't think of that, makes sense! Parallel Append can also run in a way that the Append child nodes will only get 1 worker each. How can I tell which case it is, from the EXPLAIN output (for example the output at [1]) ? [1] https://www.postgresql.org/message-id/69077f15-4125-2d63-733f-21ce6eac4f01%40gmx.net Dimitris
how to completely turn off statement error logging
I don't want to log statement errors in the server logfile - whether the statement string nor the error message. I set "log_min_error_statement = panic" according to the docs: To effectively turn off logging of failing statements, set this parameter to PANIC. But error messages are still logged: $ psql psql (12.17) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. pcl_l300=# show log_min_error_statement; log_min_error_statement - panic (1 row) pcl_l300=# select error_817 / 0; ERROR: column "error_817" does not exist LINE 1: select error_817 / 0; ^ pcl_l300=# exit $ fgrep error_817 pg_statsinfo.log 2024-05-13 16:01:14 CEST 36421 ERROR: column "error_817" does not exist Question: How can I turn off logging of this statement based error message?
Re: Postgresql active-active nodes in cluster
On Mon, May 13, 2024 at 2:53 AM Vidyashree H S < shreevidya...@exaleapsemi.com> wrote: > >- Is that multi master replication is not recommended to do ? > > No, it is not recommended, as it has a very, very narrow use case, and comes with a lot of drawbacks. Sure, you COULD configure your car to also be able to ride across the river like a boat, but it's far easier to use a bridge, or a barge. And now your car is harder to care for, and has a lot more ways in which it could break. > >- If we can do, can I get any resource for implementing that on >PostgreSQL which is open-source? > > You still have not told us your use case. That can help us to guide you to the right solution. But for totally open source solutions there are not many out there. Here are some: * https://github.com/JumpMind/symmetric-ds * https://github.com/bucardo/bucardo You can also in theory use logical replication if at least version 16: * https://www.crunchydata.com/blog/active-active-postgres-16 Cheers, Greg
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Tue, 14 May 2024 at 01:52, Dimitrios Apostolou wrote: > > On Tue, 14 May 2024, David Rowley wrote: > > The query does contain an ORDER BY, so if the index is not chosen to > > provide pre-sorted input, then something has to put the results in the > > correct order before the LIMIT is applied. > > The last query I tried was: > > SELECT DISTINCT workitem_n FROM test_runs_raw LIMIT 10; I was looking at the original query. In that case, we have 2 ways to remove duplicate rows with DISTINCT, "Hash Aggregate" and "Sort" -> "Unique". Both of these will consume all of their input rows before outputting any rows. DISTINCT with LIMIT is a special case that we don't have a good operator for. In theory, we could have some "Hash Distinct" node type that was less eager to consume all of its input rows. When invoked "Hash Distinct" could consume input rows until it found one that didn't exist in the hash table. I've no idea how that would work when we exceed work_mem. However, most queries with a LIMIT will have an ORDER BY, so such a node likely wouldn't get much use. David
Re: SELECT DISTINCT chooses parallel seqscan instead of indexscan on huge table with 1000 partitions
On Tue, 14 May 2024 at 02:07, Dimitrios Apostolou wrote: > > On Tue, 14 May 2024, David Rowley wrote: > > Parallel Append can also run in a way that the Append child nodes will > > only get 1 worker each. > > How can I tell which case it is, from the EXPLAIN output (for example > the output at [1]) ? IIRC, the planner does prefer to use Parallel aware child Paths when creating a Parallel Append. Given equivalent costs, there's no advantage to it choosing a non-parallel aware Path. The planner does not have any optimisations that that would enable. However, it is possible that the planner *could* generate these. All the Append subpaths would just have to all be parallel safe but not parallel aware. You could identify them in EXPLAIN by seeing a "Parallel Append" without the "Parallel" in front of the node names in any of the Parallel Append's subpaths. David
Re: how to completely turn off statement error logging
"Zwettler Markus (OIZ)" writes: > I don't want to log statement errors in the server logfile - whether the > statement string nor the error message. You need to set log_min_messages higher than ERROR. You might consider using LOG or FATAL rather than PANIC, though. > I set "log_min_error_statement = panic" according to the docs: > To effectively turn off logging of failing statements, set this parameter to > PANIC. This setting controls whether the STATEMENT: detail is appended to a message, but not the basic choice of whether to emit the message. regards, tom lane
AW: [Extern] Re: how to completely turn off statement error logging
> Von: Tom Lane > Gesendet: Montag, 13. Mai 2024 16:26 > An: Zwettler Markus (OIZ) > Cc: pgsql-general@lists.postgresql.org > Betreff: [Extern] Re: how to completely turn off statement error logging > > "Zwettler Markus (OIZ)" writes: > > I don't want to log statement errors in the server logfile - whether the > > statement > string nor the error message. > > You need to set log_min_messages higher than ERROR. You might consider > using LOG or FATAL rather than PANIC, though. > > > I set "log_min_error_statement = panic" according to the docs: > > To effectively turn off logging of failing statements, set this parameter to > PANIC. > > This setting controls whether the STATEMENT: detail is appended to a message, > but not the basic choice of whether to emit the message. > > regards, tom lane > --- Externe Email: Vorsicht mit Anhängen, Links oder dem Preisgeben von > Informationen --- please let me refine. I would like to suppress all errors in the server logfile coming from client applications, i.e. statement level errors such as "duplicate key violates..." but I do not want to suppress errors that are related to infrastructure problems, i.e. "could not open file..." if I set log_min_messages higher than ERROR, errors concerning the infrastructure would also be suppressed, wouldn't they? thanks, markus
Re: how to completely turn off statement error logging
On Monday, May 13, 2024, Zwettler Markus (OIZ) wrote: > > > but I do not want to suppress errors that are related to infrastructure > problems, i.e. "could not open file..." > > The server doesn’t classify the errors it emits into scope, “application errors” and ”infrastructure errors”, or otherwise. The only classification is severity. It does include an SQL Error Code that you could, in post-processing, act on. David J.
UTC is not a time zone?
PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a connection open the entire time) failed with: 2024-05-13 09:12:44.719 PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 06:26:17 PDT,3/60,0,ERROR,22023,"invalid value for parameter ""TimeZone"": ""UTC""","while setting parameter ""TimeZone"" to ""UTC"" parallel worker"," select count(1), count(1) filter (where visited > 0) from framework_seenchoice ",,,"","client backend",,0 It's not (easily) repeatable, and the system was not touched while the process was running (no installing new binaries, etc.). Does this look familiar to anyone?
Re: UTC is not a time zone?
On 5/13/24 10:37 AM, Christophe Pettus wrote: PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a connection open the entire time) failed with: 2024-05-13 09:12:44.719 PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 06:26:17 PDT,3/60,0,ERROR,22023,"invalid value for parameter ""TimeZone"": ""UTC""","while setting parameter ""TimeZone"" to ""UTC"" parallel worker"," select count(1), count(1) filter (where visited > 0) from framework_seenchoice ",,,"","client backend",,0 It's not (easily) repeatable, and the system was not touched while the process was running (no installing new binaries, etc.). Does this look familiar to anyone? Yes: https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org Answer: https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us -- Adrian Klaver adrian.kla...@aklaver.com
Re: UTC is not a time zone?
> On May 13, 2024, at 10:48, Adrian Klaver wrote: > Yes: > > https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org > > Answer: > > https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued a ton of ` SET TIMEZONE TO 'UTC'; ` before the failure.
Re: UTC is not a time zone?
On 2024-05-13 19:37 +0200, Christophe Pettus wrote: > PostgreSQL 16.3 on MacOS Sonoma. A long-running process (which held a > connection open the entire time) failed with: > > 2024-05-13 09:12:44.719 > PDT,"cyan","cyan",35926,"[local]",664214f9.8c56,3,"SELECT",2024-05-13 > 06:26:17 PDT,3/60,0,ERROR,22023,"invalid value for parameter > ""TimeZone"": ""UTC""","while setting parameter ""TimeZone"" to ""UTC"" > parallel worker"," select count(1), count(1) filter (where visited > 0) from > framework_seenchoice ",,,"","client backend",,0 > > It's not (easily) repeatable, and the system was not touched while the > process was running (no installing new binaries, etc.). Does this > look familiar to anyone? Could be the same issue as [1] (also on macOS). Can you trigger that error with: SET timezone = 'UTC'; And what's the output of: SELECT * FROM pg_timezone_names ORDER BY name; [1] https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org -- Erik
Re: UTC is not a time zone?
On 5/13/24 10:50 AM, Christophe Pettus wrote: On May 13, 2024, at 10:48, Adrian Klaver wrote: Yes: https://www.postgresql.org/message-id/5DF49366-10D1-42A4-99BF-F9A7DC3AB0F4%40mailbox.org Answer: https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us Thanks! Similar, but I don't think it's that. This was a sudden change in a long-running connection that had issued a ton of ` SET TIMEZONE TO 'UTC'; ` before the failure. You sure the timezone file did not get changed under the connection? Why is SET TIMEZONE TO 'UTC being constantly called? -- Adrian Klaver adrian.kla...@aklaver.com
Re: UTC is not a time zone?
> On May 13, 2024, at 10:53, Erik Wienhold wrote: > Can you trigger that error with: > > SET timezone = 'UTC'; No, that works correctly: psql (16.3) Type "help" for help. df=> SET timezone = 'UTC'; SET The error popped up during a long-running connection that had issued that SET many (many!) times before. > SELECT * FROM pg_timezone_names ORDER BY name; Attached below (it's long, of course). Most germane, however, is this: df=> SELECT * FROM pg_timezone_names WHERE name='UTC'; name | abbrev | utc_offset | is_dst --+++ UTC | UTC| 00:00:00 | f (1 row) -- df=> SELECT * FROM pg_timezone_names ORDER BY name; name | abbrev | utc_offset | is_dst --+++ Africa/Abidjan | GMT| 00:00:00 | f Africa/Accra | GMT| 00:00:00 | f Africa/Addis_Ababa | EAT| 03:00:00 | f Africa/Algiers | CET| 01:00:00 | f Africa/Asmara| EAT| 03:00:00 | f Africa/Asmera| EAT| 03:00:00 | f Africa/Bamako| GMT| 00:00:00 | f Africa/Bangui| WAT| 01:00:00 | f Africa/Banjul| GMT| 00:00:00 | f Africa/Bissau| GMT| 00:00:00 | f Africa/Blantyre | CAT| 02:00:00 | f Africa/Brazzaville | WAT| 01:00:00 | f Africa/Bujumbura | CAT| 02:00:00 | f Africa/Cairo | EEST | 03:00:00 | t Africa/Casablanca| +01| 01:00:00 | f Africa/Ceuta | CEST | 02:00:00 | t Africa/Conakry | GMT| 00:00:00 | f Africa/Dakar | GMT| 00:00:00 | f Africa/Dar_es_Salaam | EAT| 03:00:00 | f Africa/Djibouti | EAT| 03:00:00 | f Africa/Douala| WAT| 01:00:00 | f Africa/El_Aaiun | +01| 01:00:00 | f Africa/Freetown | GMT| 00:00:00 | f Africa/Gaborone | CAT| 02:00:00 | f Africa/Harare| CAT| 02:00:00 | f Africa/Johannesburg | SAST | 02:00:00 | f Africa/Juba | CAT| 02:00:00 | f Africa/Kampala | EAT| 03:00:00 | f Africa/Khartoum | CAT| 02:00:00 | f Africa/Kigali| CAT| 02:00:00 | f Africa/Kinshasa | WAT| 01:00:00 | f Africa/Lagos | WAT| 01:00:00 | f Africa/Libreville| WAT| 01:00:00 | f Africa/Lome | GMT| 00:00:00 | f Africa/Luanda| WAT| 01:00:00 | f Africa/Lubumbashi| CAT| 02:00:00 | f Africa/Lusaka| CAT| 02:00:00 | f Africa/Malabo| WAT| 01:00:00 | f Africa/Maputo| CAT| 02:00:00 | f Africa/Maseru| SAST | 02:00:00 | f Africa/Mbabane | SAST | 02:00:00 | f Africa/Mogadishu | EAT| 03:00:00 | f Africa/Monrovia | GMT| 00:00:00 | f Africa/Nairobi | EAT| 03:00:00 | f Africa/Ndjamena | WAT| 01:00:00 | f Africa/Niamey| WAT| 01:00:00 | f Africa/Nouakchott| GMT| 00:00:00 | f Africa/Ouagadougou | GMT| 00:00:00 | f Africa/Porto-Novo| WAT| 01:00:00 | f Africa/Sao_Tome | GMT| 00:00:00 | f Africa/Timbuktu | GMT| 00:00:00 | f Africa/Tripoli | EET| 02:00:00 | f Africa/Tunis | CET| 01:00:00 | f Africa/Windhoek | CAT| 02:00:00 | f America/Adak | HDT| -09:00:00 | t America/Anchorage| AKDT | -08:00:00 | t America/Anguilla | AST| -04:00:00 | f America/Antigua | AST| -04:00:00 | f America/Araguaina| -03| -03:00:00 | f America/Argentina/Buenos_Aires | -03| -03:00:00 | f America/Argentina/Catamarca | -03| -03:00:00 | f America/Argentina/ComodRivadavia | -03| -03:00:00 | f America/Argentina/Cordoba| -03| -03:00:00 | f America/Argentina/Jujuy | -03| -03:00:00 | f America/Argentina/La_Rioja | -03| -03:00:00 | f America/Argentina/Mendoza| -03| -03:00:00 | f America/Argentina/Rio_Gallegos | -03| -03:00:00 | f America/Argentina/Salta | -03| -03:00:00 | f America/Argentina/San_Juan | -03| -03:00:00 | f America/Argentina/San_Luis | -03| -03:00:00 | f America/Argentina/Tucuman| -03| -03:00:00 | f Americ
Re: UTC is not a time zone?
> On May 13, 2024, at 10:58, Adrian Klaver wrote: > > You sure the timezone file did not get changed under the connection? Yes (at least, nothing happened on the system that would indicate that). The system wasn't touched during the execution (and, as noted, it worked after as well as before). > Why is SET TIMEZONE TO 'UTC being constantly called? ORM noise.
Re: UTC is not a time zone?
On 5/13/24 11:02 AM, Christophe Pettus wrote: On May 13, 2024, at 10:58, Adrian Klaver wrote: You sure the timezone file did not get changed under the connection? Yes (at least, nothing happened on the system that would indicate that). The system wasn't touched during the execution (and, as noted, it worked after as well as before). Hmm. What does pg_config --configure show for '--with-system-tzdata' ? Why is SET TIMEZONE TO 'UTC being constantly called? ORM noise. -- Adrian Klaver adrian.kla...@aklaver.com
Re: UTC is not a time zone?
> On May 13, 2024, at 11:07, Adrian Klaver wrote: > > > What does pg_config --configure show for '--with-system-tzdata' ? It's a local compile, and was built without that. As an experiment, I'm just pounding the server with a single connection doing nothing but SET TIMEZONEs repeatedly. So far, no break, but it is *very* intermittent.
Re: UTC is not a time zone?
Christophe Pettus writes: > On May 13, 2024, at 10:48, Adrian Klaver wrote: >> https://www.postgresql.org/message-id/1273542.1712326418%40sss.pgh.pa.us > Thanks! Similar, but I don't think it's that. This was a sudden change in a > long-running connection that had issued a ton of ` SET TIMEZONE TO 'UTC'; ` > before the failure. The underlying cause is likely roughly similar, to wit failure to read /usr/share/zoneinfo/UTC (or the Postgres-private equivalent file). The parent process would long since have cached the zone data in its memory, but this error is in a parallel worker process, which'd have to read the file for itself during startup. What's causing that I can't say. It doesn't look like we log the errno anywhere when failing to read a zone file :-( regards, tom lane
Re: UTC is not a time zone?
> On May 13, 2024, at 11:17, Tom Lane wrote: > What's causing that I can't say. It doesn't look like we log the > errno anywhere when failing to read a zone file :-( File descriptor exhaustion? (Of course, that would mean something somewhere is leaking them, which is another problem.)
Re: UTC is not a time zone?
On 5/13/24 11:18 AM, Christophe Pettus wrote: On May 13, 2024, at 11:17, Tom Lane wrote: What's causing that I can't say. It doesn't look like we log the errno anywhere when failing to read a zone file :-( File descriptor exhaustion? (Of course, that would mean something somewhere is leaking them, which is another problem.) If I am understanding this correctly: "The parent process would long since have cached the zone data in its memory, but this error is in a parallel worker process, which'd have to read the file for itself during startup. " Then this: "As an experiment, I'm just pounding the server with a single connection doing nothing but SET TIMEZONEs repeatedly. So far, no break, but it is *very* intermittent." May not induce the error unless there are parallel workers involved. -- Adrian Klaver adrian.kla...@aklaver.com
Re: UTC is not a time zone?
Christophe Pettus writes: > On May 13, 2024, at 11:17, Tom Lane wrote: >> What's causing that I can't say. It doesn't look like we log the >> errno anywhere when failing to read a zone file :-( > File descriptor exhaustion? (Of course, that would mean something somewhere > is leaking them, which is another problem.) Since this is in a pretty-new process, it would have to be global FD exhaustion, that is ENFILE not EMFILE. (Assuming macOS even has that concept; its BSD roots sure do, but who knows what Apple's done to it over the years.) You'd likely have seen more side-effects of such a condition. I was wondering more about permissions checks gone wrong, comparable to the antivirus-induced misbehaviors we hear about on Windows. Not that that's a comforting answer. regards, tom lane
Re: UTC is not a time zone?
> On May 13, 2024, at 11:26, Adrian Klaver wrote: > May not induce the error unless there are parallel workers involved. Indeed. I'll see about pulling together a test case that forces that.
Re: UTC is not a time zone?
Christophe Pettus writes: >> On May 13, 2024, at 11:26, Adrian Klaver wrote: >> May not induce the error unless there are parallel workers involved. > Indeed. I'll see about pulling together a test case that forces that. Right. Once a backend process has loaded a zone file, it caches that in a hash table that it will never flush (which is arguably a bug for other reasons, since those files aren't really immutable, but that's how it behaves today). So you've got 0 chance of hitting this via repeat SET TIMEZONE in a single backend. regards, tom lane
