search

2ndQuadrant / pglogical

Logical Replication extension for PostgreSQL 17, 16, 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
http://2ndquadrant.com/en/resources/pglogical/
Other
1.01k stars 153 forks source link

Could not open relation with OID #347

Open DenisRazinkin opened 3 years ago

DenisRazinkin commented 3 years ago

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;
}
DenisRazinkin commented 2 years ago

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
tomharrisonjr commented 2 years ago

Workaround if you're stuck on a pglogical version (i.e. on RDS): reboot instance will clear cache.

Co0olCat commented 2 years ago

Another workaround -> is to reconnect to DB on the node host. It should solve the issue. Thank you. TY