pglogical
pglogical copied to clipboard
Published
20 hours ago •
2ndQuadrant
2ndQuadrant
Could not open relation with OID
Pglogical 2.3.4 + postgresql 12
Open new psql connection to database. Run commands:
create extension pglogical;
select * from pglogical.create_node(
node_name := 'node1',
dsn := 'host=/tmp/ dbname=my_database'
);
select * from pglogical.create_replication_set(
set_name := 'test_set',
replicate_insert := true, replicate_update := true,
replicate_delete := true, replicate_truncate := true);
create table table_test( value bigint primary key );
select * from pglogical.replication_set_add_table(
set_name := 'test_set', relation := 'table_test',
synchronize_data := true);
drop extension pglogical;
create extension pglogical;
select * from pglogical.create_node(
node_name := 'test',
dsn := 'host=/tmp/ dbname=my_database'
);
select * from pglogical.create_replication_set(
set_name := 'test_set',
replicate_insert := true, replicate_update := true,
replicate_delete := true, replicate_truncate := true);
select * from pglogical.replication_set_add_table(
set_name := 'test_set', relation := 'table_test',
synchronize_data := true);
Here I got error:
[XX000] ERROR: could not open relation with OID 1512172
Backtrace of postgres connection:
#0 relation_open (relationId=1512172, lockmode=lockmode@entry=3) at relation.c:62
#1 0x0000563980bbd816 in table_open (relationId=<optimized out>, lockmode=lockmode@entry=3) at table.c:43
#2 0x00007fbde2efffae in pglogical_recordMultipleDependencies (depender=depender@entry=0x7ffdfb88b8f4, referenced=referenced@entry=0x7ffdfb88b8e8, nreferenced=nreferenced@entry=1, behavior=behavior@entry=DEPENDENCY_NORMAL)
at pglogical_dependency.c:313
#3 0x00007fbde2f0009c in pglogical_recordDependencyOn (depender=depender@entry=0x7ffdfb88b8f4, referenced=referenced@entry=0x7ffdfb88b8e8, behavior=behavior@entry=DEPENDENCY_NORMAL) at pglogical_dependency.c:291
#4 0x00007fbde2ef2db3 in replication_set_add_table (setid=2420282416, reloid=reloid@entry=1512223, att_list=att_list@entry=0x0, row_filter=row_filter@entry=0x0, sync_clear_filter=0x0) at pglogical_repset.c:1114
#5 0x00007fbde2ef7212 in pglogical_replication_set_add_table (fcinfo=0x5639816600b8) at pglogical_functions.c:1543
#6 0x0000563980ce5b77 in ExecMakeTableFunctionResult (setexpr=0x563981607be0, econtext=0x563981607ab0, argContext=<optimized out>, expectedDesc=0x5639816094f0, randomAccess=false) at execSRF.c:233
#7 0x0000563980cf372c in FunctionNext (node=node@entry=0x5639816079a0) at nodeFunctionscan.c:94
#8 0x0000563980ce51b6 in ExecScanFetch (recheckMtd=0x563980cf3440 <FunctionRecheck>, accessMtd=0x563980cf3470 <FunctionNext>, node=0x5639816079a0) at execScan.c:133
#9 ExecScan (node=0x5639816079a0, accessMtd=0x563980cf3470 <FunctionNext>, recheckMtd=0x563980cf3440 <FunctionRecheck>) at execScan.c:183
#10 0x0000563980cdcffa in ExecProcNode (node=0x5639816079a0) at ../../../src/include/executor/executor.h:239
#11 ExecutePlan (execute_once=<optimized out>, dest=0x5639815689a0, direction=<optimized out>, numberTuples=501, sendTuples=<optimized out>, operation=CMD_SELECT, use_parallel_mode=<optimized out>, planstate=0x5639816079a0,
estate=0x563981607760) at execMain.c:1646
#12 standard_ExecutorRun (queryDesc=0x563981642fe0, direction=<optimized out>, count=501, execute_once=<optimized out>) at execMain.c:364
#13 0x0000563980e23cdc in PortalRunSelect (portal=portal@entry=0x5639815f1200, forward=forward@entry=true, count=count@entry=501, dest=dest@entry=0x5639815689a0) at pquery.c:929
#14 0x0000563980e250f8 in PortalRun (portal=portal@entry=0x5639815f1200, count=count@entry=501, isTopLevel=isTopLevel@entry=true, run_once=<optimized out>, dest=dest@entry=0x5639815689a0,
altdest=altdest@entry=0x5639815689a0, completionTag=0x7ffdfb88c000 "") at pquery.c:770
#15 0x0000563980e229bd in exec_execute_message (max_rows=501, portal_name=0x563981568590 "") at postgres.c:2090
#16 PostgresMain (argc=<optimized out>, argv=argv@entry=0x563981591808, dbname=<optimized out>, username=<optimized out>) at postgres.c:4297
#17 0x0000563980b4d777 in BackendRun (port=0x56398158d4c0, port=0x56398158d4c0) at postmaster.c:4431
#18 BackendStartup (port=0x56398158d4c0) at postmaster.c:4122
#19 ServerLoop () at postmaster.c:1704
#20 0x0000563980db0e29 in PostmasterMain (argc=3, argv=0x563981563210) at postmaster.c:1377
#21 0x0000563980b4e69f in main (argc=3, argv=0x563981563210) at main.c:228
Pglogical caches some tables OIDs and does not reset it:
/*
* Get (cached) oid of the dependency catalog
*/
static Oid
get_pglogical_depend_rel_oid(void)
{
static Oid dependrelationoid = InvalidOid;
if (dependrelationoid == InvalidOid)
dependrelationoid = get_pglogical_table_oid(CATALOG_REPSET_RELATION);
return dependrelationoid;
}
Additional info:
I got similar issue in a process which creates and drops temporary tables.
#0 relation_open (relationId=3902682, lockmode=lockmode@entry=3) at relation.c:62
#1 0x00005564413f9816 in table_open (relationId=, lockmode=lockmode@entry=3) at table.c:43
#2 0x00007f7038f363a9 in pglogical_tryDropDependencies (object=object@entry=0x7ffe3bea25b4, behavior=DROP_RESTRICT) at pglogical_dependency.c:1923
#3 0x00007f7038f36a1e in pglogical_checkDependency (object=0x7ffe3bea25b4, behavior=) at pglogical_dependency.c:2135
#4 0x00007f7038f34843 in pglogical_object_access (access=, classId=1259, objectId=4107152, subId=0, arg=0x7ffe3bea25fc) at pglogical_executor.c:364
#5 0x000055644143ef7e in RunObjectDropHook (classId=, objectId=, subId=, dropflags=dropflags@entry=0) at objectaccess.c:62
#6 0x000055644142931d in deleteOneObject (flags=0, depRel=0x7ffe3bea2858, object=) at dependency.c:1241
#7 deleteObjectsInList (targetObjects=targetObjects@entry=0x5564432b07f8, depRel=depRel@entry=0x7ffe3bea2858, flags=flags@entry=0) at dependency.c:271
#8 0x00005564414299d8 in performMultipleDeletions (objects=objects@entry=0x5564432b0868, behavior=DROP_RESTRICT, flags=flags@entry=0) at dependency.c:432
#9 0x00005564414e54f1 in RemoveRelations (drop=drop@entry=0x55644333d290) at tablecmds.c:1334
#10 0x0000556441661701 in ExecDropStmt (stmt=stmt@entry=0x55644333d290, isTopLevel=isTopLevel@entry=false) at utility.c:1746
#11 0x00005564416636be in ProcessUtilitySlow (pstate=pstate@entry=0x5564432b08d8, pstmt=pstmt@entry=0x55644333d200, queryString=queryString@entry=0x55644332b650 "drop table if exists tt_license_objects",
context=context@entry=PROCESS_UTILITY_QUERY, params=params@entry=0x0, queryEnv=queryEnv@entry=0x0, completionTag=0x7ffe3bea2d00 "", dest=0x556441c05580 <spi_printtupDR>) at utility.c:1591
#12 0x00005564416623f2 in standard_ProcessUtility (pstmt=0x55644333d200, queryString=0x55644332b650 "drop table if exists tt_license_objects", context=PROCESS_UTILITY_QUERY, params=0x0, queryEnv=0x0,
dest=0x556441c05580 <spi_printtupDR>, completionTag=0x7ffe3bea2d00 "") at utility.c:927
#13 0x00007f7038f34629 in pglogical_ProcessUtility (pstmt=, queryString=, context=, params=, queryEnv=, dest=, qc=0x7ffe3bea2d00 "")
at pglogical_executor.c:287
#14 0x000055644154cba0 in _SPI_execute_plan (plan=plan@entry=0x55644333cd50, paramLI=paramLI@entry=0x0, snapshot=snapshot@entry=0x0, crosscheck_snapshot=crosscheck_snapshot@entry=0x0, read_only=read_only@entry=false,
fire_triggers=fire_triggers@entry=true, tcount=) at spi.c:2317
#15 0x000055644154d56b in SPI_execute_plan_with_paramlist (plan=0x55644333cd50, params=0x0, read_only=, tcount=0) at spi.c:577
#16 0x00007f70127827fb in exec_stmt_execsql (estate=estate@entry=0x7ffe3bea31e0, stmt=stmt@entry=0x5564431a1538) at pl_exec.c:4162
#17 0x00007f70127838d3 in exec_stmt (estate=estate@entry=0x7ffe3bea31e0, stmt=0x5564431a1538) at pl_exec.c:2033
#18 0x00007f70127863e4 in exec_stmts (estate=0x7ffe3bea31e0, stmts=) at pl_exec.c:1924
#19 0x00007f70127869e4 in exec_stmt_block (estate=estate@entry=0x7ffe3bea31e0, block=block@entry=0x5564431a17b8) at pl_exec.c:1865
#20 0x00007f70127840e7 in exec_stmt (estate=estate@entry=0x7ffe3bea31e0, stmt=0x5564431a17b8) at pl_exec.c:1957
#21 0x00007f7012785f50 in plpgsql_exec_function (func=func@entry=0x556443349538, fcinfo=fcinfo@entry=0x5564433947f0, simple_eval_estate=simple_eval_estate@entry=0x0, atomic=) at pl_exec.c:589
#22 0x00007f7012779c55 in plpgsql_call_handler (fcinfo=0x5564433947f0) at pl_handler.c:267
#23 0x000055644177312b in fmgr_security_definer (fcinfo=0x5564433947f0) at fmgr.c:748
#24 0x0000556441521b77 in ExecMakeTableFunctionResult (setexpr=0x5564433942d0, econtext=0x5564433941a0, argContext=, expectedDesc=0x5564433943e0, randomAccess=true) at execSRF.c:233
#25 0x000055644152f72c in FunctionNext (node=node@entry=0x556443394090) at nodeFunctionscan.c:94
#26 0x00005564415211b6 in ExecScanFetch (recheckMtd=0x55644152f440 , accessMtd=0x55644152f470 , node=0x556443394090) at execScan.c:133
#27 ExecScan (node=0x556443394090, accessMtd=0x55644152f470 , recheckMtd=0x55644152f440 ) at execScan.c:183
#28 0x0000556441518ffa in ExecProcNode (node=0x556443394090) at ../../../src/include/executor/executor.h:239
#29 ExecutePlan (execute_once=, dest=0x556441bfcc40 , direction=, numberTuples=0, sendTuples=, operation=CMD_SELECT, use_parallel_mode=,
planstate=0x556443394090, estate=0x556443393e50) at execMain.c:1646
#30 standard_ExecutorRun (queryDesc=0x5564434c1cc8, direction=, count=0, execute_once=) at execMain.c:364
#31 0x000055644165fcdc in PortalRunSelect (portal=portal@entry=0x5564431371f0, forward=, count=0, dest=) at pquery.c:929
#32 0x000055644165fe94 in DoPortalRunFetch (portal=portal@entry=0x5564431371f0, fdirection=, fdirection@entry=FETCH_FORWARD, count=, count@entry=9223372036854775807,
dest=dest@entry=0x556441bfcc40 ) at pquery.c:1672
#33 0x0000556441661373 in PortalRunFetch (portal=0x5564431371f0, fdirection=FETCH_FORWARD, count=9223372036854775807, dest=dest@entry=0x556441bfcc40 ) at pquery.c:1448
#34 0x00005564414d2717 in PerformPortalFetch (stmt=stmt@entry=0x5564430aeff8, dest=0x556441bfcc40 , dest@entry=0x5564430af3f8, completionTag=completionTag@entry=0x7ffe3bea3cb0 "") at portalcmds.c:199
#35 0x00005564416629b2 in standard_ProcessUtility (pstmt=0x5564430af318, queryString=0x5564430ae580 "MOVE ALL IN "eef4b11b1bc891ad_start_writing_license_objects_4047"", context=PROCESS_UTILITY_TOPLEVEL, params=0x0,
queryEnv=0x0, dest=0x5564430af3f8, completionTag=0x7ffe3bea3cb0 "") at utility.c:518
#36 0x00007f7038f34629 in pglogical_ProcessUtility (pstmt=, queryString=, context=, params=, queryEnv=, dest=, qc=0x7ffe3bea3cb0 "")
---Type to continue, or q to quit---
at pglogical_executor.c:287
#37 0x000055644165f9cf in PortalRunUtility (portal=0x556443137300, pstmt=0x5564430af318, isTopLevel=, setHoldSnapshot=, dest=, completionTag=) at pquery.c:1175
#38 0x0000556441660427 in PortalRunMulti (portal=portal@entry=0x556443137300, isTopLevel=isTopLevel@entry=true, setHoldSnapshot=setHoldSnapshot@entry=false, dest=dest@entry=0x5564430af3f8,
altdest=altdest@entry=0x5564430af3f8, completionTag=completionTag@entry=0x7ffe3bea3cb0 "") at pquery.c:1328
#39 0x0000556441660fed in PortalRun (portal=portal@entry=0x556443137300, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run_once=run_once@entry=true, dest=dest@entry=0x5564430af3f8,
altdest=altdest@entry=0x5564430af3f8, completionTag=0x7ffe3bea3cb0 "") at pquery.c:796
#40 0x000055644165d0bb in exec_simple_query (query_string=0x5564430ae580 "MOVE ALL IN "eef4b11b1bc891ad_start_writing_license_objects_4047"") at postgres.c:1215
#41 0x000055644165edf2 in PostgresMain (argc=, argv=argv@entry=0x5564430d76b0, dbname=, username=) at postgres.c:4236
#42 0x0000556441389777 in BackendRun (port=0x5564430d3050, port=0x5564430d3050) at postmaster.c:4431
#43 BackendStartup (port=0x5564430d3050) at postmaster.c:4122
#44 ServerLoop () at postmaster.c:1704
#45 0x00005564415ece29 in PostmasterMain (argc=3, argv=0x5564430a9200) at postmaster.c:1377
#46 0x000055644138a69f in main (argc=3, argv=0x5564430a9200) at main.c:228
Workaround if you're stuck on a pglogical version (i.e. on RDS): reboot instance will clear cache.
Another workaround -> is to reconnect to DB on the node host. It should solve the issue. Thank you. TY
