Re: Occasional lengthy locking causing stalling on commit
G'day all, A quick follow-up on this issue for interest's sake. The stalling we were seeing turned out to be a Cloud SQL issue and not related to our listen/notify usage. Cloud SQL has an automatic storage increase process that resizes the underlying disk as required to account for cluster growth. As it turns out that process occasionally causes I/O to stall for a brief window. https://cloud.google.com/sql/docs/postgres/instance-settings#automatic-storage-increase-2ndgen The workaround supplied by Google is to manually provision slack storage in larger increments to prevent the more frequent automatic increases, which happen 25GB at a time on a large cluster. We didn't make the connection because disk resize events are not visible in any logs; Google Support found the issue by correlating the timestamps of our observed outages with their internal logs. Hopefully this is useful for someone else. Thanks again for your help Tom - your advice on listen/notify locking on commit was very useful despite not being the cause in this case. Cheers Ben On Mon, 1 Feb 2021 at 12:33, Ben Hoskings wrote: > > On Mon, 1 Feb 2021 at 10:33, Tom Lane wrote: > > > > One thing that just occurred to me is that you might find it > > interesting to keep tabs on what's in the $PGDATA/pg_notify > > directory. Do the performance burps correspond to transitory > > peaks in the amount of data there? Or (grasping at straws here...) > > wraparound of the file names back to ? > > We don't have filesystem access on Cloud SQL - the downside of the > managed route :) > > It sounds like it might be time to bump the pg13 upgrade up the TODO list. > > Cheers > Ben
Logical Replication: SELECT pg_catalog.set_config Statement
Hello everybody,
not sure where to post this, general seems most appropriate.
We have tried logical replication in a test-setup, and it appears to
work fine.
However, the following statement keeps running:
SELECT pg_catalog.set_config('search_path', '', false);
It is issued by the user 'subscriber' we have created for the subscription.
Originally it only had the 'Replication' role, but we have subsequently made it
Superuser.
However, the behaviour is the same.
List of roles
Role name | Attributes | Member of
++---
postgres | Superuser, Create role, Create DB, Replication | {}
subscriber | Superuser, Replication | {}
Strangely, when I log in as subscriber and query the searchpath, the result is
'public'
I can then execute the above statement, and it sets the search_path to ''
At the next restart, the statement pops up again, and hangs
Here is the log:
2021-05-17 16:08:03.595 CEST -usztestlogrepsub@10.139.0.41
: LOG: statement: SELECT
pg_catalog.set_config('search_path', '', false);
2021-05-17 16:08:03.596 CEST -usztestlogrepsub@10.139.0.41
: LOG: received replication command:
IDENTIFY_SYSTEM
2021-05-17 16:08:03.596 CEST -usztestlogrepsub@10.139.0.41 :
LOG: received replication command: START_REPLICATION SLOT "usztestlogrepsub" LOGICAL
E51/EC041228 (proto_version '1', publication_names '"usztestlogreppub"')
2021-05-17 16:08:03.597 CEST -usztestlogrepsub@10.139.0.41
: LOG: starting logical decoding for slot
"usztestlogrepsub"
2021-05-17 16:08:03.597 CEST -usztestlogrepsub@10.139.0.41
: DETAIL: Streaming transactions
committing after E51/EC06B668, reading WAL from E51/EC06B668.
2021-05-17 16:08:03.597 CEST -usztestlogrepsub@10.139.0.41
: LOG: logical decoding found consistent
point at E51/EC06B668
2021-05-17 16:08:03.597 CEST -usztestlogrepsub@10.139.0.41
: DETAIL: There are no running
transactions.
Can anybody explain why this happens, and how to avoid it?
regards
Hannes
--
Re: Logical Replication: SELECT pg_catalog.set_config Statement
=?UTF-8?Q?Hannes_K=c3=bchtreiber?= writes:
> We have tried logical replication in a test-setup, and it appears to
> work fine.
> However, the following statement keeps running:
> SELECT pg_catalog.set_config('search_path', '', false);
What makes you think it "keeps running"? It looks to me like the
replication client does that and then goes about its business:
> 2021-05-17 16:08:03.595 CEST -usztestlogrepsub@10.139.0.41
> : LOG: statement: SELECT
> pg_catalog.set_config('search_path', '', false);
> 2021-05-17 16:08:03.596 CEST -usztestlogrepsub@10.139.0.41
> : LOG: received replication command:
> IDENTIFY_SYSTEM
Admittedly, since you seem to have omitted the PID from your
log_line_prefix, it's hard to be 100% sure that these log entries
are from the same process. But I bet they are. The standard
walreceiver definitely does things this way.
In short, I think there's nothing to see here.
regards, tom lane
Any insights on Qlik Sense using CURSOR ?
Hi, I am using postgresql 12.7 on Ubunut as a datawarehouse, that is then queried by QlikSense to produce business analytics. One of my dataloaders, that runs multiple queries, sometimes takes about 3 hours to feed Qlik with the relevant records (about 10M records), but sometimes goes crazy and times out (as Qlik stops it when it takes more than 480 minutes). The point is that Qlik is using a CURSOR to retrive the data. I'm not familiar with CURSOR and postgresql documentation mainly cites functions as use case. I don't really know how Qlik creates these cursors when executing my queries... I tried load_min_duration to pinpoint the problem, but only shows things like that: ... LOG: duration : 294774.600 ms, instruction : fetch 10 in "SQL_CUR4" LOG: duration : 282867.279 ms, instruction : fetch 10 in "SQL_CUR4" ... So I don't know exactly which of my queries is hiding behind "SQL_CUR4"... Is there a way to log the actual query ? Is using a CURSOR a best practice to retrieve big datasets ? (it seems Qlik is using it for every connection on Postgresql) Does each FETCH re-run the query, or is the result somehow cached (on disk ?) ? Thanks for any insight on CURSOR and/or Qlik queries on Postgresql ! Best regards, Franck
Re: Logical Replication: SELECT pg_catalog.set_config Statement
Hello Tom, thanks for your answer!
We found out because we are monitoring long running queries, and saw it
had been running for a month before the restart yesterday.
I just queried pg_stat_activity and it seems to be running since then.
taimusz=# SELECT pid, query_start, usename, left(query,70)
FROM pg_stat_activity
WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start;
pid | query_start | usename
| left
-+---++
2321161 | 2021-05-17 16:15:13.906679+02 | subscriber | SELECT
pg_catalog.set_config('search_path', '', false);
Am 18.05.2021 um 14:24 schrieb Tom Lane:
SELECT pg_catalog.set_config('search_path', '', false);
What makes you think it "keeps running"? It looks to me like the
replication client does that and then goes about its business:
Admittedly, since you seem to have omitted the PID from your
log_line_prefix, it's hard to be 100% sure that these log entries
are from the same process. But I bet they are. The standard
walreceiver definitely does things this way.
Sorry for omitting the PIDs. But you are probably right, it is a test
environment where not much is happening
In short, I think there's nothing to see here.
regards, tom lane
I wish this is the case. There seem to be no bloated tables, but before
trying this on a production system I want to be sure
regards
Hannes
--
Re: Logical Replication: SELECT pg_catalog.set_config Statement
=?UTF-8?Q?Hannes_K=c3=bchtreiber?= writes: > Hello Tom, thanks for your answer! > We found out because we are monitoring long running queries, and saw it > had been running for a month before the restart yesterday. > I just queried pg_stat_activity and it seems to be running since then. Oh, that's because pg_stat_activity continues to show the last plain-SQL query executed by the session. I think we recently changed things so that replication commands would be shown in pg_stat_activity too, but evidently you're running a version older than that. regards, tom lane
Re: unicode match normal forms
Hi Gianni, many thanks for your detailed response. It turned out that my postgresql installation is too old for normalize,so I will probably a) use an external script to normalize existing data b) change application code to normalize data before inserting or searching Regards Wolfgang >> On 17 May 2021 13:27:40 - >> haman...@t-online.de wrote: >> > in unicode letter ä exists in two versions - linux and windows use a >> > composite whereas macos prefers the decomposed form. Is there any way >> > to make a semi-exact match that accepts both variants? >> >> You should probably normalise the strings in whatever application code >> handles the inserting. NFC is the "usually sensible" normal form to >> use. >> >> If you can't change the application code, you may use a trigger and >> apply the `normalize(text[,form])→text` function to the values >> >> https://www.postgresql.org/docs/13/functions-string.html#id-1.5.8.10.5.2.2.7.1.1.2 >> >> something vaguely like (totally untested!):: >> >> create function normalize_filename() returns trigger as $$ >> begin >> new.filename := normalize(new.filename); >> return new; >> end; >> $$ language plpgsql; >> >> create trigger normalize_filename >> before insert or update >> on that_table >> for each row >> execute function normalize_filename(); >> >> -- >> Dakkar - >> GPG public key fingerprint = A071 E618 DD2C 5901 9574 >> 6FE2 40EA 9883 7519 3F88 >> key id = 0x75193F88 >> >> >>
Re: unicode match normal forms
>> El día lunes, mayo 17, 2021 a las 01:27:40p. m. -, haman...@t-online.de >> escribió: >> >> > Hi, >> > >> > in unicode letter ä exists in two versions - linux and windows use a >> > >> > composite whereas macos prefers >> > the decomposed form. Is there any way to make a semi-exact match that >> > accepts both variants? >> > This question is not about fulltext but about matching filenames across a >> > network - I wish to avoid two equally-looking >> > filenames. >> >> There is only *one* codepoint for the German letter a Umlaut: >> LATIN SMALL LETTER A WITH DIAERESI U+00E4 >> Hi Matthias, unfortunately there also is letter a with combining dieretic - and it is used by MacOS The mac seems to prefer decomposed characters in other contexts as well, so in my everyday job I used to have fun with product catalogues from a few companies. Depending on the computer used for adding / editing a productthe relevant field could be iso-latin-1, utf8 normal, or utf8 decomposed >> Said that, having such chars (non ASCII) in file names, I count as a bad >> idea. I usually try to avoid whitespace and accented charactersin filenames, to be able to use ssh and scp without much hassle, but I am not the user in this case. Now, if I look at a music collection (stored as folders with mp3 files for the tracks), I would really prefer "Einstürzende Neubauten" over Einstuerzende_Neubauten Regards Wolfgang >>
Re:
On Mon, May 17, 2021 at 7:13 AM Tom Lane wrote: > "David G. Johnston" writes: > > On Monday, May 17, 2021, Tom Lane wrote: > >> It looks like it won't be started if you set > >> max_logical_replication_workers = 0. > > > I was wondering about that. Would you mind posting a link to the code > > where that is checked? I actually looked though admittedly not that > hard. > > See ApplyLauncherRegister() in src/backend/replication/logical/launcher.c. > That's called from PostmasterMain(). It doesn't actually *launch* the > process, but it creates the background worker record that causes it to > be started a little later. > > Thanks (to you to Dilip). Given this, should the configuration description for this include the "This parameter can only be set in postgresql.conf file or on the server command line." comment, as well as the fact that 0 disables the logical replication subscribing feature altogether, and precludes the background worker scheduler process from launching at startup? https://www.postgresql.org/docs/current/runtime-config-replication.html max_logical_replication_workers (int) David J.
[no subject]
"David G. Johnston" writes: > Given this, should the configuration description for this include the "This > parameter can only be set in postgresql.conf file or on the server command > line." comment, Hmm, yeah, it does lack the standard boilerplate for a PGC_POSTMASTER GUC. I wonder which other GUCs are misidentified that way. > as well as the fact that 0 disables the logical replication > subscribing feature altogether, and precludes the background worker > scheduler process from launching at startup? I'd be in favor of mentioning the former, but the latter seems like an implementation detail. regards, tom lane
Re:
On Tue, May 18, 2021 at 6:46 AM Tom Lane wrote: > "David G. Johnston" writes: > > > as well as the fact that 0 disables the logical replication > > subscribing feature altogether, and precludes the background worker > > scheduler process from launching at startup? > > I'd be in favor of mentioning the former, but the latter seems like > an implementation detail. > > I can see that point - but for me the deciding factor is that this detail ends up showing up in the O/S process list and talking about that is desirable to me. David J.
Re: Logical Replication: SELECT pg_catalog.set_config Statement
>
> We found out because we are monitoring long running queries, and saw it
> had been running for a month before the restart yesterday.
> I just queried pg_stat_activity and it seems to be running since then.
>
> taimusz=# SELECT pid, query_start, usename, left(query,70)
> FROM pg_stat_activity
> WHERE query != '' AND query NOT ILIKE '%pg_stat_activity%'
> ORDER BY query_start;
>pid | query_start | usename
> | left
>
> -+---++
> 2321161 | 2021-05-17 16:15:13.906679+02 | subscriber | SELECT
> pg_catalog.set_config('search_path', '', false);
>
>
>
You should add: AND state != 'idle' to filter out queries that are no
longer running and don't have an open transaction. Your query is finding
long running sessions, not necessarily long running queries.
Some abbrev values from pg_timezone_names are not found in pg_timezone_abbrevs
This query:
select distinct abbrev as a
from pg_timezone_names
where abbrev like '%+%' or abbrev like '%-%'
order by 1;
gets lots of rows with names like these:
+00
+01
+12
-07
-08
This query shows that none of these is found in pg_timezone_abbrevs:
with v as (
select distinct abbrev as a
from pg_timezone_names
where abbrev like '%+%' or abbrev like '%-%')
select abbrev
from pg_timezone_abbrevs
where abbrev in (select a from v);
I suppose that these strangely named abbreviations are special and that they
shouldn't appear in the official pg_timezone_abbrevs.
But try this:
select distinct abbrev
from pg_timezone_names
where
abbrev not like '%+%' and abbrev not like '%-%' and
abbrev not in (select abbrev from pg_timezone_abbrevs)
order by abbrev;
It gets this result:
CAT
ChST
HDT
SST
WEST
WIB
WIT
WITA
Is this a bug? If not, what's the rationale for omitting them?
From section “8.5.3. Time Zones” at
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
«
A time zone abbreviation, for example PST. Such a specification merely defines
a particular offset from UTC, in contrast to full time zone names which can
imply a set of daylight savings transition rules as well. The recognized
abbreviations are listed in the pg_timezone_abbrevs view (see Section 51.91).
You cannot set the configuration parameters TimeZone or log_timezone to a time
zone abbreviation, but you can use abbreviations in date/time input values and
with the AT TIME ZONE operator.
»
So I s'pose that it's to be expected that this:
select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'CAT';
causes this error:
time zone "CAT" not recognized
It's the same with the other abbrev values from pg_timezone_names that aren't
in pg_timezone_abbrevs.
If this is not a bug, then why are these eight abbreviations special?
The contents of the pg_timezone_names view bring some surprises
Some time zones have abbreviations that are identical to their names. This
query:
select name
from pg_timezone_names
where abbrev = name
order by name;
gets this result:
EST
GMT
HST
MST
UCT
UTC
This, in itself, doesn’t seem remarkable. I wondered if any time zones have
names that occur as time zone abbreviations but where the name and its
abbreviation differ.
select name
from
pg_timezone_names
where
name in (select abbrev from pg_timezone_names) and
name <> abbrev
order by name;
gets this result:
CET
EET
So I wondered what rows have CET or EET as either a name or an abbreviation and
yet the name and the abbreviation differ.
select name, abbrev, utc_offset, is_dst
from pg_timezone_names
where
(
name in ('CET', 'EET') or
abbrev in ('CET', 'EET')
)
and name <> abbrev
order by name;
gets this result:
Africa/Algiers | CET| 01:00:00 | f
Africa/Cairo | EET| 02:00:00 | f
Africa/Tripoli | EET| 02:00:00 | f
Africa/Tunis | CET| 01:00:00 | f
CET| CEST | 02:00:00 | t
EET| EEST | 03:00:00 | t
Egypt | EET| 02:00:00 | f
Europe/Kaliningrad | EET| 02:00:00 | f
Libya | EET| 02:00:00 | f
This tells me that when CET is used as a timezone name, it denotes an offset of
02:00—at least at some times of the year. And when the same text is used as an
abbrev, it denotes an offset of 01:00.
But you can use either a timezone name, or a timezone abbreviation in the `at
time zone` clause (see below).
There’s a similar story for EET where it denotes respectively offsets of 03:00
and 02:00.
Here’s what seems to me to be a closely related dilemma. I’d thought that an
abbrev uniquely specified the utc_offset. But this test shows that it doesn’t:
with
v1 as (
select distinct abbrev, utc_offset
from pg_timezone_names),
v2 as (
select abbrev, count(*)
from v1
group by abbrev
having count(*) > 1)
select name, abbrev, utc_offset, is_dst
from pg_timezone_names
where abbrev in (select abbrev from v2)
order by abbrev;
It gets 46 rows. Here’s an interesting subset:
America/Monterrey | CDT| -05:00:00 | t
America/Havana | CDT| -04:00:00 | t
ROC| CST| 08:00:00 | f
America/Costa_Rica | CST| -06:00:00 | f
Eire | IST| 01:00:00 | f
Asia/Kolkata | IST| 05:30:00 | f
So here, the same text, even when used as abbrev, can denote different
utc_offset values. (But note that there seems to be no way, in the 'at time
zone' clause, that I can say that I want a text value to be taken as a name and
not as an abbreviation, or vice versa.)
This seems to be at odds with what section “8.5.3. Time Zones” at
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-TIMEZONES
says:
«
A time zone abbreviation, for example PST. Such a specification merely defines
a particular offset from UTC, in contrast to full time zone names which can
imply a set of daylight savings transition rules as well. The recognized
abbreviations are listed in the pg_timezone_abbrevs view (see Section 51.91).
You cannot set the configuration parameters TimeZone or log_timezone to a time
zone abbreviation, but you can use abbreviations in date/time input values and
with the AT TIME ZONE operator.
»
This claims (as I read it) that a time zone abbreviation uniquely determines an
offset from UTC.
It seems that the result of this is therefore undefined because CDT denotes two
different utc_offset values.:
select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'CDT';
The same goes for this:
select ('2000-01-01 12:00:00 +00:00'::timestamptz) at time zone 'EET';
In summary, each of these texts, for two kinds of reason, can denote two
different utc_offset values.:
CET
EET
CDT
CST
IST
Am I missing an essential clue to resolving what seems to me to be a paradox?
Or am I seeing two kinds of bug?
