Hello,
We're preparing to upgrade postgresql from 12.8 to 14.2 but still we have some
bugs and issues. Most of them are our fault, but now we have problem wedon't
know how to play with it.
-- dmesg message
[Sat Apr 2 08:26:24 2022] postmaster[1939250]: segfault at 7f2998f2c000 ip
7f4a56cfeaaa sp 7fff5a3e36a8 error 6 in
libc-2.28.so[7f4a56b9e000+1bc000][Sat Apr 2 08:26:24 2022] Code: 8e c0 01 00
00 c5 fe 6f 06 c5 fe 6f 4e 20 c5 fe 6f 56 40 c5 fe 6f 5e 60 48 81 c6 80 00 00
00 48 81 ea 80 00 00 00 c5 fd e7 07 fd e7 4f 20 c5 fd e7 57 40 c5 fd e75f 60 48
81 c7 80 00 00 00
-- PostgreSQL Log message
2022-04-02 08:26:24 MSK 0LOG: server process (PID 1939250) was
terminated by signal 11: Segmentation fault2022-04-02 08:26:24 MSK
0DETAIL: Failed process was running: SELECT "SubdivisionId", "UserId",
"UserLogin"FROM powerbi."GetReportRights1002_VIEW" ;2022-04-02 08:26:24 MSK
0LOG: terminating any other active server processes
-- User error message
54000: index row requires 554440232 bytes, maximum size is 8191
-- We have compiled from the source code with modified macros
sed -i.gres "s/#define NAMEDATALEN 64/#define NAMEDATALEN 320/g"
./src/include/pg_config_manual.h
sed -i.gres "s/#define NAMEDATALEN 64/#define NAMEDATALEN 320/g"
./src/interfaces/ecpg/include/sqlda-native.h
-- In the attachment files
-- DDL
-- The result of the command execution: gdb /usr/pgsql-14/bin/postgres --ex 'bt full' --batch
CREATE OR REPLACE VIEW powerbi."GetReportRights1002_VIEW" AS
SELECT "FT_GetReportRights"."r_SubdivisionId" AS "SubdivisionId",
"FT_GetReportRights"."r_UserId" AS "UserId",
concat("FT_GetReportRights"."r_UserLogin", '@example.com') AS "UserLogin"
FROM rights."FT_GetReportRights"(1002)
"FT_GetReportRights"("r_SubdivisionId", "r_UserId", "r_UserLogin")
UNION ALL
SELECT DISTINCT '-999'::integer AS "SubdivisionId",
"FT_GetReportRights"."r_UserId" AS "UserId",
concat("FT_GetReportRights"."r_UserLogin", '@example.com') AS "UserLogin"
FROM rights."FT_GetReportRights"(1002)
"FT_GetReportRights"("r_SubdivisionId", "r_UserId", "r_UserLogin");
CREATE OR REPLACE FUNCTION rights."FT_GetReportRights"(IN "par_ReportId"
integer DEFAULT NULL::integer)
RETURNS TABLE("r_SubdivisionId" integer, "r_UserId" integer, "r_UserLogin"
character varying) AS
$BODY$
DECLARE
--"par_ReportId" integer = 1002;
BEGIN
DROP TABLE IF EXISTS "ListObject" ;
CREATE temp TABLE IF NOT EXISTS "ListObject"(
"idObject" INTEGER
);
WITH RECURSIVE "get_idObjects" AS (
SELECT
o.id
,o."ParentID"
,o.tag_int as "idObject_Subj"
FROM
rights."Objects" o
WHERE
o."idObjectType" = 1
UNION
SELECT
o_parent.id
,o_parent."ParentID"
,o_slave."idObject_Subj"
FROM
"get_idObjects" as o_slave
INNER JOIN rights."Objects" as o_parent
ON o_parent.id = o_slave."ParentID"
AND o_parent."idObjectType" = 2
)
INSERT INTO "ListObject"(
"idObject"
)
SELECT
gio.id
FROM
"get_idObjects" gio
WHERE
gio."idObject_Subj" = "par_ReportId"
;
DROP TABLE IF EXISTS "FullRightsOfStructure" ;
CREATE TEMP TABLE IF NOT EXISTS "FullRightsOfStructure"(
"SubdivisionId" INTEGER
,"UserId" integer
,"UserLogin" VARCHAR(20)
);
INSERT INTO "FullRightsOfStructure"(
"SubdivisionId"
,"UserId"
,"UserLogin"
)
SELECT DISTINCT
(CASE
WHEN o."idObjectType" = 3 THEN o.tag_int
--WHEN o."idObjectType" = 4 THEN ou."SubdivisionId"
ELSE NULL::INTEGER
END) as "SubdivisionId"
,ou."id"
,ou."Login"
FROM
"ListObject" lo
JOIN rights."Rights" r ON r."idObject_Subj" = lo."idObject"
JOIN rights."Users" u ON u.id = r."idRightsUser"
JOIN rights."Objects" o ON o.id = r."idObject"
JOIN dwh."OFAS_Org_User" ou ON ou.id = u."idUser_OFAS"
union
select
os.id as "SubdivisionId",
ou.id as "UserId",
ou."Login" as "UserLogin"
from
dwh."User_See_All" as u_sall
join dwh."OFAS_Org_User" as ou
on u_sall."UserLogin" = ou."Login"
full join dwh."OFAS_Org_Structure" as os
on true
;
DROP TABLE IF EXISTS "tt_Result";
CREATE TEMP TABLE "tt_Result"(
"SubdivisionId" INTEGER
,"UserId" integer
,"UserLogin" VARCHAR(20)
);
insert into "tt_Result" (
"SubdivisionId"
,"UserId"
,"UserLogin"
)
SELECT
coalesce(os_in."id",os.id) as "SubdivisionId"
,fros."UserId"
,fros."UserLogin"
from
"FullRightsOfStructure" as fros
join dwh."OFAS_Org_Structure" as OS on os.id = fros."SubdivisionId"
left join dwh."OFAS_Org_Structure" as OS_in on os_in."id_hierarchy" <@
os."id_hierarchy"
