Date: Thu, 3 Mar 2022 16:04:28 +0100
From: hubert depesz lubaczewski <depesz@depesz.com>
Sender: depesz@depesz.com
To: PostgreSQL General <pgsql-general@lists.postgresql.org>
Subject: Interesting fail when migrating Pg from Ubuntu Bionic to Focal
Message-ID: <20220303150428.GA26036@depesz.com>
Reply-To: depesz@depesz.com
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
User-Agent: Mutt/1.10.1 (2018-07-13)
Archived-At: 
 <https://www.postgresql.org/message-id/20220303150428.GA26036%40depesz.com>
Precedence: bulk

Hi,
I know it's going to be most likely due to glibc and locales, but I found
interesting case that I can't figure out how to fix.

We have pg 12.6 on bionic. Works. Added focal replica (binary).

Replicates OK, but then fails when I try to pg_dump -s.

Error is:

pg_dump: error: query failed: server closed the connection unexpectedly
        This probably means the server terminated abnormally
        before or while processing the request.
pg_dump: error: query was: SELECT p.tableoid, p.oid, p.proname, p.prolang, =
p.pronargs, p.proargtypes, p.prorettype, (SELECT pg_catalog.array_agg(acl O=
RDER BY row_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(p.pr=
oacl,pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS perm(acl,ro=
w_n) WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(pip.initpr=
ivs,pg_catalog.acldefault('f',p.proowner))) AS init(init_acl) WHERE acl =3D=
 init_acl)) as foo) AS proacl, (SELECT pg_catalog.array_agg(acl ORDER BY ro=
w_n) FROM (SELECT acl, row_n FROM pg_catalog.unnest(coalesce(pip.initprivs,=
pg_catalog.acldefault('f',p.proowner))) WITH ORDINALITY AS initp(acl,row_n)=
 WHERE NOT EXISTS ( SELECT 1 FROM pg_catalog.unnest(coalesce(p.proacl,pg_ca=
talog.acldefault('f',p.proowner))) AS permp(orig_acl) WHERE acl =3D orig_ac=
l)) as foo) AS rproacl, NULL AS initproacl, NULL AS initrproacl, p.pronames=
pace, (SELECT rolname FROM pg_catalog.pg_roles WHERE oid =3D p.proowner) AS=
 rolname FROM pg_proc p LEFT JOIN pg_init_privs pip ON (p.oid =3D pip.objoi=
d AND pip.classoid =3D 'pg_proc'::regclass AND pip.objsubid =3D 0) WHERE p.=
prokind <> 'a'
  AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid =3D 'pg_proc'::regc=
lass AND objid =3D p.oid AND deptype =3D 'i')
  AND (
  pronamespace !=3D (SELECT oid FROM pg_namespace WHERE nspname =3D 'pg_cat=
alog')
  OR EXISTS (SELECT 1 FROM pg_cast
  WHERE pg_cast.oid > 16383=20
  AND p.oid =3D pg_cast.castfunc)
  OR EXISTS (SELECT 1 FROM pg_transform
  WHERE pg_transform.oid > 16383 AND=20
  (p.oid =3D pg_transform.trffromsql
  OR p.oid =3D pg_transform.trftosql))
  OR p.proacl IS DISTINCT FROM pip.initprivs)


Based on https://wiki.postgresql.org/wiki/Locale_data_changes
I wrote:
SELECT 'reindex index ' || indexrelid::regclass::text=20
FROM (SELECT indexrelid, indrelid, indcollation[i] coll FROM pg_index, gene=
rate_subscripts(indcollation, 1) g(i)) s=20
  JOIN pg_collation c ON coll=3Dc.oid
WHERE collprovider IN ('d', 'c') AND collname NOT IN ('C', 'POSIX') \gexec

and let it run (80k+ indexes).

Afterwards - same problem.

So I did "reindex system".

And the query still fails.

I tried running simple "select * from table", for each of:

- pg_catalog.pg_roles
- pg_catalog.pg_proc
- pg_catalog.pg_init_privs
- pg_catalog.pg_depend
- pg_catalog.pg_namespace
- pg_catalog.pg_cast
- pg_catalog.pg_transform

and it worked, so I'm kinda at loss here.

I have test system, can test anything. Any idea on what could be the reason,
and if the system is fixable afterwards?

Ah, one more thing - straced backend when it was doing its thing. Last 50 l=
ines:

15:01:31.229198 futex(0xfffe49b8cb3c, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229233 futex(0xfffe49b8c41c, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229268 futex(0xfffe49b8cdfc, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229303 futex(0xfffe49b8ce00, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229338 futex(0xfffe49b8ce08, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229379 futex(0xfffe49b8dde4, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229415 futex(0xfffe49b91524, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229450 futex(0xfffe49b8998c, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229485 futex(0xfffe49b8cf9c, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229521 futex(0xfffe49b882e4, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229556 futex(0xfffe49b8e548, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229596 futex(0xfffe49b8d75c, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229634 futex(0xfffe49b8cfe8, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229670 futex(0xfffe49b8dfe8, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229705 futex(0xfffe49b8dff0, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229740 futex(0xfffe49b8dff8, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229775 futex(0xfffe49b8ddf0, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229810 futex(0xfffe49b8e0a8, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229845 futex(0xfffe49b8e200, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229881 futex(0xfffe49b8e534, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229925 futex(0xfffe49b8e734, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229961 futex(0xfffe49b8e7dc, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.229998 futex(0xfffe49b8e924, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230034 futex(0xfffe49b8ea6c, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230070 futex(0xfffe49b8ea74, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230105 futex(0xfffe49b91514, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230141 futex(0xfffe49b8ad84, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230177 futex(0xfffe49b91518, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230212 futex(0xfffe49b9152c, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230247 futex(0xfffe49b91530, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230283 futex(0xfffe49b91718, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230323 futex(0xfffe49b91720, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230384 futex(0xfffe49b8e488, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000013>
15:01:31.230423 futex(0xfffe49b946cc, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230464 futex(0xfffe49b884d4, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230515 futex(0xfffe49b955c0, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.230624 futex(0xfffe49ba8738, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000013>
15:01:31.230678 futex(0xfffe49b91288, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000012>
15:01:31.231031 brk(0xaaaae513e000)     =3D 0xaaaae513e000 <0.000016>
15:01:31.232480 brk(0xaaaae515f000)     =3D 0xaaaae515f000 <0.000018>
15:01:31.239133 brk(0xaaaae5180000)     =3D 0xaaaae5180000 <0.000019>
15:01:31.245463 brk(0xaaaae51a1000)     =3D 0xaaaae51a1000 <0.000020>
15:01:31.245847 brk(0xaaaae51c2000)     =3D 0xaaaae51c2000 <0.000016>
15:01:31.259644 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANON=
YMOUS, -1, 0) =3D 0xffff80da8000 <0.000025>
15:01:31.259705 mmap(NULL, 36864, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANO=
NYMOUS, -1, 0) =3D 0xfffe4a36a000 <0.000014>
15:01:31.260012 futex(0xaaaae4f7fcf8, FUTEX_WAKE_PRIVATE, 2147483647) =3D 0=
 <0.000014>
15:01:31.260074 mprotect(0xfffe4a36a000, 36864, PROT_READ|PROT_EXEC) =3D 0 =
<0.000017>
15:01:31.260120 mprotect(0xffff80da8000, 4096, PROT_READ|PROT_EXEC) =3D 0 <=
0.000013>
15:01:31.260165 --- SIGSEGV {si_signo=3DSIGSEGV, si_code=3DSEGV_MAPERR, si_=
addr=3D0xfffd80da80a8} ---
15:01:31.451341 +++ killed by SIGSEGV (core dumped) +++

depesz