search

RekGRpth / pg_task

PostgreSQL and Greenplum job scheduler pg_task allows to execute any sql command at any specific time at background asynchronously
MIT License
55 stars 9 forks source link

Error on greenplum 6.23.4 #7

Open shurik005 opened 1 year ago

shurik005 commented 1 year ago

On startup, pg_task crashes with an error unrecognized node type: 239. I found https://github.com/greenplum-db/gpdb/issues/12111 and there doesn't seem to be a fix in 6.X. Can be replaced in all UPDATE: WHERE t.id = OPERATOR(pg_catalog.=) s.id to WHERE t.id IN (SELECT s.id FROM s), but not in query of function work_sleep. Source error log:

2023-07-24 12:42:20.233993 MSK,,,p1175175,th662768768,,,,0,con16510,cmd18,seg-1,,,,sx1,"ERROR","XX000","unrecognized node type: 239",,,,,,"WITH s AS ( SELECT ""id"" FROM public.task AS t LEFT JOIN ""pg_catalog"".""pg_locks"" AS l ON ""locktype"" OPERATOR(pg_catalog.=) 'userlock' AND ""mode"" OPERATOR(pg_catalog.=) 'AccessExclusiveLock' AND ""granted"" AND ""objsubid"" OPERATOR(pg_catalog.=) 4 AND ""database"" OPERATOR(pg_catalog.=) 16491 AND ""classid"" OPERATOR(pg_catalog.=) (""id"" OPERATOR(pg_catalog.>>) 32) AND ""objid"" OPERATOR(pg_catalog.=) (""id"" OPERATOR(pg_catalog.<<) 32 OPERATOR(pg_catalog.>>) 32) WHERE ""state"" OPERATOR(pg_catalog.=) ANY(ARRAY['TAKE', 'WORK']::public.state[]) AND l.pid IS NULL FOR UPDATE OF t  ) UPDATE public.task AS t SET ""state"" = 'PLAN', ""start"" = NULL, ""stop"" = NULL, ""pid"" = NULL FROM s WHERE t.id OPERATOR(pg_catalog.=) s.id RETURNING t.id::pg_catalog.int8",0,,"execAmi.c",724,"Stack trace:
1    0xbcedc2 postgres errstart + 0x2c2
2    0xbd0cd0 postgres elog_finish + 0xd0
3    0x8c5907 postgres <symbol not found> + 0x8c5907
4    0x8c5857 postgres ExecSquelchNode + 0xb7
5    0x8c58ac postgres ExecSquelchNode + 0x10c
6    0x8c5849 postgres ExecSquelchNode + 0xa9
7    0x8efe94 postgres ExecHashJoin + 0x6c4
8    0x8cff38 postgres ExecProcNode + 0x3b8
9    0x8f89d7 postgres ExecModifyTable + 0xd7
10   0x8cfd80 postgres ExecProcNode + 0x200
11   0x8c7739 postgres <symbol not found> + 0x8c7739
12   0x8c81e3 postgres standard_ExecutorRun + 0x313
13   0x8c83b5 postgres ExecutorRun + 0xc5
14   0xa89066 postgres <symbol not found> + 0xa89066
15   0xa894c4 postgres <symbol not found> + 0xa894c4
16   0xa897bc postgres <symbol not found> + 0xa897bc
17   0xa8aa82 postgres PortalRunFetch + 0x1d2
18   0x90833e postgres <symbol not found> + 0x90833e
19   0x7f9b27678f24 pg_task.so SPI_cursor_fetch_my + 0x74
20   0x7f9b2766390a pg_task.so <symbol not found> + 0x2766390a
21   0x7f9b2766ca61 pg_task.so work_main + 0x931
22   0x9fe17f postgres StartBackgroundWorker + 0x23f
23   0xa0b5b4 postgres <symbol not found> + 0xa0b5b4
24   0xa0cb33 postgres <symbol not found> + 0xa0cb33
25   0x7f9b2825d8b0 libpthread.so.0 <symbol not found> + 0x2825d8b0
26   0x7f9b27dc7437 libc.so.6 __select + 0x17
27   0xa0e418 postgres <symbol not found> + 0xa0e418
28   0xa1062e postgres PostmasterMain + 0xd0e
29   0x6d7fa2 postgres main + 0x432
30   0x7f9b27cfa08b libc.so.6 __libc_start_main + 0xeb
"
2023-07-24 12:42:20.238687 MSK,,,p3647437,th662768768,,,,0,,,seg-1,,,,,"LOG","00000","worker process: gpadmin nds pg_work public task 1000 (PID 1175175) exited with exit code 1",,,,,,,0,,"postmaster.c",3987,

After change queries error log:

2023-07-24 12:42:20.233993 MSK,,,p1175175,th662768768,,,,0,con16510,cmd18,seg-1,,,,sx1,"ERROR","XX000","unrecognized node type: 239",,,,,,"WITH s AS ( SELECT ""id"" FROM public.task AS t LEFT JOIN ""pg_catalog"".""pg_locks"" AS l ON ""locktype"" OPERATOR(pg_catalog.=) 'userlock' AND ""mode"" OPERATOR(pg_catalog.=) 'AccessExclusiveLock' AND ""granted"" AND ""objsubid"" OPERATOR(pg_catalog.=) 4 AND ""database"" OPERATOR(pg_catalog.=) 16491 AND ""classid"" OPERATOR(pg_catalog.=) (""id"" OPERATOR(pg_catalog.>>) 32) AND ""objid"" OPERATOR(pg_catalog.=) (""id"" OPERATOR(pg_catalog.<<) 32 OPERATOR(pg_catalog.>>) 32) WHERE ""state"" OPERATOR(pg_catalog.=) ANY(ARRAY['TAKE', 'WORK']::public.state[]) AND l.pid IS NULL FOR UPDATE OF t  ) UPDATE public.task AS t SET ""state"" = 'PLAN', ""start"" = NULL, ""stop"" = NULL, ""pid"" = NULL FROM s WHERE t.id OPERATOR(pg_catalog.=) s.id RETURNING t.id::pg_catalog.int8",0,,"execAmi.c",724,"Stack trace:
1    0xbcedc2 postgres errstart + 0x2c2
2    0xbd0cd0 postgres elog_finish + 0xd0
3    0x8c5907 postgres <symbol not found> + 0x8c5907
4    0x8c5857 postgres ExecSquelchNode + 0xb7
5    0x8c58ac postgres ExecSquelchNode + 0x10c
6    0x8c5849 postgres ExecSquelchNode + 0xa9
7    0x8efe94 postgres ExecHashJoin + 0x6c4
8    0x8cff38 postgres ExecProcNode + 0x3b8
9    0x8f89d7 postgres ExecModifyTable + 0xd7
10   0x8cfd80 postgres ExecProcNode + 0x200
11   0x8c7739 postgres <symbol not found> + 0x8c7739
12   0x8c81e3 postgres standard_ExecutorRun + 0x313
13   0x8c83b5 postgres ExecutorRun + 0xc5
14   0xa89066 postgres <symbol not found> + 0xa89066
15   0xa894c4 postgres <symbol not found> + 0xa894c4
16   0xa897bc postgres <symbol not found> + 0xa897bc
17   0xa8aa82 postgres PortalRunFetch + 0x1d2
18   0x90833e postgres <symbol not found> + 0x90833e
19   0x7f9b27678f24 pg_task.so SPI_cursor_fetch_my + 0x74
20   0x7f9b2766390a pg_task.so <symbol not found> + 0x2766390a
21   0x7f9b2766ca61 pg_task.so work_main + 0x931
22   0x9fe17f postgres StartBackgroundWorker + 0x23f
23   0xa0b5b4 postgres <symbol not found> + 0xa0b5b4
24   0xa0cb33 postgres <symbol not found> + 0xa0cb33
25   0x7f9b2825d8b0 libpthread.so.0 <symbol not found> + 0x2825d8b0
26   0x7f9b27dc7437 libc.so.6 __select + 0x17
27   0xa0e418 postgres <symbol not found> + 0xa0e418
28   0xa1062e postgres PostmasterMain + 0xd0e
29   0x6d7fa2 postgres main + 0x432
30   0x7f9b27cfa08b libc.so.6 __libc_start_main + 0xeb
"
2023-07-24 12:42:20.238687 MSK,,,p3647437,th662768768,,,,0,,,seg-1,,,,,"LOG","00000","worker process: gpadmin nds pg_work public task 1000 (PID 1175175) exited with exit code 1",,,,,,,0,,"postmaster.c",3987,
RekGRpth commented 1 year ago

Yeah, I know. When I checked, I manually made squelching changes. But https://github.com/greenplum-db/gpdb/issues/12111#issuecomment-1314506362 https://github.com/greenplum-db/gpdb/pull/12306#issuecomment-1071951428 https://github.com/greenplum-db/gpdb/pull/13220#issuecomment-1207209940 Maybe we can fix it in ADB...