Reset pglogical.depend OID cache

[eulerto]

When you drop and create the pglogical extension after previously using a pglogical function, there could be some function call that will reference a pglogical.depend OID from the previous pglogical extension (before dropping the pglogical extension). See the test case in issue

Replace the static variable with a global variable and reset it after dropping the extension.

Fix issue #347

[DenisRazinkin]

I got similar issue with "could not open relation with OID" in a process which creates temporary tables during drop temp table. And this commit does not fix it.

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=<optimized out>) 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 <donothingDR>) 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

[eulerto]

Could you provide a test case? Or explain what you mean by "creates temporary tables during drop temp table"?

[DenisRazinkin]

Create two database connections:

1st connection

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);

drop table if exists table_test;
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);

2nd connection:

create temporary table tt_test( test_value bigint );
insert into tt_test( test_value ) values ( 1 );
drop table if exists tt_test;

1st connection

drop extension pglogical;
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);

drop table if exists table_test;
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);

2nd:

create temporary table tt_test( test_value bigint );
insert into tt_test( test_value ) values ( 1 );
drop table if exists tt_test; -- here got [XX000] ERROR: could not open relation with OID 140876

[DenisRazinkin]

@eulerto probably caching of pglogical.depend is a bad idea

[tomharrisonjr]

Workaround for those stuck on prior version with RDS: reboot server.