Closed yazun closed 1 month ago
yazun
commented
4 years ago Interestingly, it's hard to replicate this behaviour using pure SQL. The error appears using JDBC. But it is clear that JDBC passes proper, schema-confined table in the insert and somehow the parser removes it while pushing to datanodes with broken search_path..
yazun
commented
4 years ago Hello, Any hints for us how to tackle this one?
PMed some details.
yazun
commented
4 years ago This behaviour cannot be reproduced with a simple test case. The stacktrace of the unexpected search_path call that destroys the search_path for partitioned tables looks like:
#9 send_message_to_server_log (edata=<optimized out>) at elog.c:3228
#10 EmitErrorReport () at elog.c:1589
#11 0x0000000000a0c074 in errfinish (dummy=0) at elog.c:552
#12 0x0000000000a106bf in elog_finish (elevel=elevel@entry=15, fmt=fmt@entry=0xbc55a0 "[SAVEPOINT] node->sql_statement:%s") at elog.c:1488
#13 0x0000000000711e7d in ExecRemoteUtility (node=0x12337b0) at execRemote.c:6516
#14 0x0000000000a2758d in set_config_option (name=0x1237050 "search_path", value=<optimized out>, context=context@entry=PGC_SUSET, source=source@entry=PGC_S_SESSION, action=action@entry=GUC_ACTION_SAVE, changeVal=<optimized out>, changeVal@entry=1 '\001', elevel=<optimized out>, is_reload=0 '\000') at guc.c:8970
#15 0x0000000000a2bcb6 in ProcessGUCArray (array=0x1623da8, context=PGC_SUSET, source=source@entry=PGC_S_SESSION, action=action@entry=GUC_ACTION_SAVE) at guc.c:12022
#16 0x0000000000a12c0c in fmgr_security_definer (fcinfo=0x1623418) at fmgr.c:674
#17 0x0000000000696607 in ExecInterpExpr (state=<optimized out>, econtext=<optimized out>, isnull=<optimized out>) at execExprInterp.c:672
#18 0x00000000007de02c in ExecEvalExprSwitchContext (isNull=0x7fff6577960c "", econtext=<optimized out>, state=0x1623338) at ../../../../src/include/executor/executor.h:331
#19 evaluate_expr (expr=<optimized out>, result_type=result_type@entry=21, result_typmod=result_typmod@entry=-1, result_collation=result_collation@entry=0) at clauses.c:4769
#20 0x00000000007e0d77 in evaluate_function (context=0x7fff65779a30, func_tuple=0x7f8cf4caf480, funcvariadic=0 '\000', args=0x2deda10, input_collid=0, result_collid=0, result_typmod=-1, result_type=21, funcid=40365) at clauses.c:4326
#21 simplify_function (funcid=40365, result_type=21, result_typmod=-1, result_collid=0, input_collid=0, args_p=<optimized out>, funcvariadic=0 '\000', process_args=1 '\001', allow_non_const=1 '\001', context=0x7fff65779a30) at clauses.c:3966
#22 0x00000000007df9ba in eval_const_expressions_mutator (node=0x2dece20, context=0x7fff65779a30) at clauses.c:2625
#23 0x0000000000755b6b in expression_tree_mutator (node=<optimized out>, mutator=mutator@entry=0x7df710 <eval_const_expressions_mutator>, context=context@entry=0x7fff65779a30) at nodeFuncs.c:2939
#24 0x00000000007e12d9 in simplify_function (funcid=63, result_type=16, result_typmod=-1, result_collid=0, input_collid=0, args_p=0x7fff65779958, funcvariadic=0 '\000', process_args=1 '\001', allow_non_const=1 '\001', context=0x7fff65779a30) at clauses.c:3957
#25 0x00000000007df8ea in eval_const_expressions_mutator (node=0x2decd30, context=0x7fff65779a30) at clauses.c:2673
#26 0x00000000007df7bb in simplify_and_arguments (forceFalse=<synthetic pointer>, haveNull=<synthetic pointer>, context=0x7fff65779a30, args=<optimized out>) at clauses.c:3786
#27 eval_const_expressions_mutator (node=<optimized out>, context=0x7fff65779a30) at clauses.c:2856
#28 0x00000000007e2473 in eval_const_expressions (root=root@entry=0x1519778, node=<optimized out>) at clauses.c:2467
#29 0x00000000007c44a3 in preprocess_expression (kind=0, expr=<optimized out>, root=0x1519778) at planner.c:1117
#30 preprocess_qual_conditions (root=root@entry=0x1519778, jtnode=0x1415218) at planner.c:1179
#31 0x00000000007caaac in subquery_planner (glob=glob@entry=0x15196e8, parse=parse@entry=0x12f2048, parent_root=parent_root@entry=0x0, hasRecursion=hasRecursion@entry=0 '\000', tuple_fraction=0) at planner.c:757
#32 0x00000000007cc3c7 in standard_planner (parse=parse@entry=0x12f2048, cursorOptions=cursorOptions@entry=256, boundParams=boundParams@entry=0x15887c8) at planner.c:375
#33 0x00000000006f949e in pgxc_planner (query=0x12f2048, cursorOptions=256, boundParams=0x15887c8) at planner.c:242
#34 0x00000000008b79e3 in pg_plan_query (boundParams=0x15887c8, cursorOptions=256, querytree=0x12f2048) at postgres.c:1056
#35 pg_plan_queries (querytrees=querytrees@entry=0x12f1ff8, cursorOptions=256, boundParams=boundParams@entry=0x15887c8) at postgres.c:1127
#36 0x00000000009f025f in BuildCachedPlan (plansource=plansource@entry=0x15318b8, qlist=0x12f1ff8, qlist@entry=0x0, boundParams=boundParams@entry=0x15887c8, queryEnv=queryEnv@entry=0x0) at plancache.c:1005
#37 0x00000000009f0c5f in GetCachedPlan (plansource=plansource@entry=0x15318b8, boundParams=boundParams@entry=0x15887c8, useResOwner=useResOwner@entry=0 '\000', queryEnv=queryEnv@entry=0x0) at plancache.c:1348
#38 0x00000000008b7310 in exec_bind_message (input_message=<optimized out>) at postgres.c:2690
#39 0x00000000008ba699 in PostgresMain (argc=<optimized out>, argv=argv@entry=0x12297e0, dbname=<optimized out>, username=<optimized out>) at postgres.c:5481
#40 0x0000000000814fd5 in BackendRun (port=0x122f1b0) at postmaster.c:4979
#41 BackendStartup (port=0x122f1b0) at postmaster.c:4651
#42 ServerLoop () at postmaster.c:1956We have rr recording and could share it if it could be of help for you.
yazun
commented
4 years ago Looks like it's related with cached functions definition when used in a query. This of course works in XL, so quite a serious regression.
yazun
commented
4 years ago It turned out that tha cause was related to the fact that some queries before the insert had
... $_$ language plpgsql set search_path FROM CURRENT;
which was properly set at the time of the function creation (it was including partition schema as well).
TBase unfortunately was resetting it to main_schema,public
The workaround was to explicitly set search_path on the function creation to include partition schema.
Do you think you could fix this regression?
Thanks, Krzysztof
yazun
commented
4 years ago I did not check deeper, but it seems that the culprit was at getting cached environment of the procedure that has broken search_path set for functions defined with set search_path FROM CURRENT;
at
https://github.com/Tencent/TBase/blob/master/src/backend/utils/fmgr/fmgr.c#L672
JennyJennyChen
commented
4 years ago If it is not the table in the current search_path, it must be accessed with the schema, which is the design. If you do not want to access table with schema, you can set the path explicitly. search_path is valid at the session level.
JennyJennyChen
commented
4 years ago In my environment：
jenny=# create schema schema_partition;
CREATE SCHEMA
jenny=# create schema schema_base;
CREATE SCHEMA
jenny=# create table schema_base.order_range(id bigserial not null,userid integer,product text, createdate date not null) partition by range ( createdate );
CREATE TABLE
jenny=# set search_path=schema_base; SET
jenny=# create table schema_partition.order_range_201701 partition of order_range(id primary key,userid,product, createdate) for values from ('2017-01-01') to ('2017-02-01');
CREATE TABLE
jenny=# create table schema_partition.order_range_201702 partition of order_range(id primary key,userid,product, createdate) for values from ('2017-02-01') to ('2017-03-01');
CREATE TABLE
jenny=#
jenny=#
jenny=# \d
List of relations
Schema | Name | Type | Owner
-------------+--------------------+----------+---------
schema_base | order_range | table | jennyer
schema_base | order_range_id_seq | sequence | jennyer
(2 rows)
jenny=# \dt schema_partition.*
List of relations
Schema | Name | Type | Owner
------------------+--------------------+-------+---------
schema_partition | order_range_201701 | table | jennyer
schema_partition | order_range_201702 | table | jennyer
(2 rows)
schema_base (1 row)
jenny=# select * from order_range; id | userid | product | createdate ----+--------+---------+------------ 1 | 1 | abc | 2017-01-01 2 | 2 | abc | 2017-02-01 (2 rows)
jenny=# select * from schema_partition.order_range_201701; id | userid | product | createdate ----+--------+---------+------------ 1 | 1 | abc | 2017-01-01 (1 row)
jenny=# select * from schema_partition.order_range_201702; id | userid | product | createdate ----+--------+---------+------------ 2 | 2 | abc | 2017-02-01 (1 row)
what is your ?
yazun
commented
4 years ago Thanks for checking this. Yes, the simple tests worked for us as well.
The most likely problem has been traced in this comment: https://github.com/Tencent/TBase/issues/40#issuecomment-623778671 - which means functions, if setting search path - do reset search _path for all subsequent operations for the connection (I guess)..
JennyJennyChen
commented
4 years ago In my environment, a function example :
postgres=# create schema schema_partition;
CREATE SCHEMA
postgres=# create schema schema_base;
CREATE SCHEMA
postgres=# create table schema_base.order_range(id bigserial not null,userid integer,product text, createdate date not null) partition by range ( createdate );
CREATE TABLE
postgres=# create table schema_partition.order_range_201701 partition of schema_base.order_range(id primary key,userid,product, createdate) for values from ('2017-01-01') to ('2017-02-01');
CREATE TABLE
postgres=# create table schema_partition.order_range_201702 partition of schema_base.order_range(id primary key,userid,product, createdate) for values from ('2017-02-01') to ('2017-03-01');
CREATE TABLE
postgres=# create or replace function schm_part() returns void as $$
postgres$# begin
postgres$# insert into order_range_201701 values(1,1,'abc','2017-01-01');
postgres$# insert into order_range_201702 values(2,2,'abc','2017-02-01');
postgres$# end$$ language plpgsql SET search_path = schema_partition;
CREATE FUNCTION
postgres=# select public.schm_part();
schm_part
-----------
(1 row)
postgres=# select * from schema_partition.order_range_201701;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
(1 row)
postgres=# select * from schema_partition.order_range_201702;
id | userid | product | createdate
----+--------+---------+------------
2 | 2 | abc | 2017-02-01
(1 row)
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=# select * from schema_base.order_range;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
2 | 2 | abc | 2017-02-01
(2 rows)
postgres=# truncate table schema_base.order_range;
TRUNCATE TABLE
postgres=# create or replace function schm_part_2() returns void as $$
postgres$# begin
postgres$# insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01');
postgres$# insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01');
postgres$# end$$ language plpgsql set search_path FROM CURRENT;
postgres=# show search_path;
search_path
-----------------
"$user", public
(1 row)
postgres=# select schm_part_2();
schm_part_2
-------------
(1 row)
postgres=# select * from schema_base.order_range;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
2 | 2 | abc | 2017-02-01
(2 rows)
postgres=# select * from schema_partition.order_range_201701;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
(1 row)
postgres=# select * from schema_partition.order_range_201702;
id | userid | product | createdate
----+--------+---------+------------`
2 | 2 | abc | 2017-02-01
(1 row)
It's OK.
Can you provide some test cases and information about your environment？
yazun
commented
4 years ago Yes, we also tried to create tests via JDBC thinking this could be related to the client - but we cannot find a simple test case. What we see is that in our environment removal of schema setting via the function fixed the problem. The other idea is that it only happens when run in parallel..
JennyJennyChen
commented
4 years ago Theoretically, parallelism only works during select query. This is a data insertion problem and does not affect it. But I still tested the parallel case, the result is OK, the details are as follows:
postgres=# set force_parallel_mode = on;
SET
postgres=# set max_parallel_workers_per_gather = 2;
SET
postgres=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
2
(1 row)
postgres=# create schema schema_partition;
CREATE SCHEMA
postgres=# create schema schema_base;
CREATE SCHEMA
postgres=# set search_path=schema_base;
SET
postgres=# create table schema_base.order_range(id bigserial not null,userid integer,product text, createdate date not null) partition by range ( createdate );
CREATE TABLE
postgres=# create table schema_partition.order_range_201701 partition of schema_base.order_range(id primary key,userid,product, createdate) for values from ('2017-01-01') to ('2017-02-01');
CREATE TABLE
postgres=# create table schema_partition.order_range_201702 partition of schema_base.order_range(id primary key,userid,product, createdate) for values from ('2017-02-01') to ('2017-03-01');
CREATE TABLE
postgres=#
postgres=# insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01');
INSERT 0 1
postgres=# insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01');
INSERT 0 1
postgres=# show search_path;
search_path
-------------
schema_base
(1 row)
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
2 | 2 | abc | 2017-02-01
(2 rows)
postgres=# select * from schema_partition.order_range_201701;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
(1 row)
postgres=#
postgres=# select * from schema_partition.order_range_201702;
id | userid | product | createdate
----+--------+---------+------------
2 | 2 | abc | 2017-02-01
(1 row)
postgres=# explain select a.id from schema_partition.order_range_201701 a inner join schema_partition.order_range_201702 b on a.product=b.product;
QUERY PLAN
------------------------------------------------------------------------------------------------------
Gather (cost=1028.22..1491.16 rows=3280 width=8)
Workers Planned: 1
Single Copy: true
-> Hash Join (cost=28.23..163.16 rows=3280 width=8)
Hash Cond: (a.product = b.product)
-> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..154.55 rows=810 width=40)
-> Seq Scan on order_range_201701 a (cost=0.00..18.10 rows=810 width=40)
-> Hash (cost=148.07..148.07 rows=810 width=32)
-> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..148.07 rows=810 width=32)
-> Seq Scan on order_range_201702 b (cost=0.00..18.10 rows=810 width=32)
(10 rows)
postgres=# set max_parallel_workers_per_gather=0;
SET
postgres=# explain select a.id from schema_partition.order_range_201701 a inner join schema_partition.order_range_201702 b on a.product=b.product;
QUERY PLAN
------------------------------------------------------------------------------------------------
Hash Join (cost=28.23..163.16 rows=3280 width=8)
Hash Cond: (a.product = b.product)
-> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..154.55 rows=810 width=40)
-> Seq Scan on order_range_201701 a (cost=0.00..18.10 rows=810 width=40)
-> Hash (cost=148.07..148.07 rows=810 width=32)
-> Remote Subquery Scan on all (dn001,dn002) (cost=100.00..148.07 rows=810 width=32)
-> Seq Scan on order_range_201702 b (cost=0.00..18.10 rows=810 width=32)
(7 rows)
postgres=# select a.id from schema_partition.order_range_201701 a inner join schema_partition.order_range_201702 b on a.product=b.product;
id
----
1
(1 row)
postgres=# set max_parallel_workers_per_gather=2;
SET
postgres=# select a.id from schema_partition.order_range_201701 a inner join schema_partition.order_range_201702 b on a.product=b.product;
id
----
1
(1 row)
postgres=# show max_parallel_workers_per_gather;
max_parallel_workers_per_gather
---------------------------------
2
(1 row)
postgres=# truncate table schema_base.order_range;
TRUNCATE TABLE
postgres=#
postgres=# create or replace function schm_part() returns void as $$
postgres$# begin
postgres$# insert into order_range_201701 values(1,1,'abc','2017-01-01');
postgres$# insert into order_range_201702 values(2,2,'abc','2017-02-01');
postgres$# end$$ language plpgsql SET search_path = schema_partition;
CREATE FUNCTION
postgres=# select schm_part();
schm_part
-----------
(1 row)
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
2 | 2 | abc | 2017-02-01
(2 rows)
postgres=#
postgres=# select * from schema_partition.order_range_201701;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
(1 row)
postgres=#
postgres=# select * from schema_partition.order_range_201702;
id | userid | product | createdate
----+--------+---------+------------
2 | 2 | abc | 2017-02-01
(1 row)
postgres=# truncate order_range;
TRUNCATE TABLE
postgres=# create or replace function schm_part_2() returns void as $$
postgres$# begin
postgres$# insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01');
postgres$# insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01');
postgres$# end$$ language plpgsql set search_path FROM CURRENT;
CREATE FUNCTION
postgres=# select schm_part();
schm_part
-----------
(1 row)
postgres=# select * from order_range;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
2 | 2 | abc | 2017-02-01
(2 rows)
postgres=#
postgres=# select * from schema_partition.order_range_201701;
id | userid | product | createdate
----+--------+---------+------------
1 | 1 | abc | 2017-01-01
(1 row)
postgres=#
postgres=# select * from schema_partition.order_range_201702;
id | userid | product | createdate
----+--------+---------+------------
2 | 2 | abc | 2017-02-01
(1 row)
If you have other ideas, please let us know. Thanks
yazun
commented
4 years ago Thanks for double checking. I think we found the workaround by using rr: https://wiki.postgresql.org/wiki/Getting_a_stack_trace_of_a_running_PostgreSQL_backend_on_Linux/BSD#Recording_Postgres_using_rr_Record_and_Replay_Framework
Maybe you could use the rr dump to check it? otherwise we could try to get a core dump just before the error.. We will have to remove the workarounds, redeploy etc but I cannot see a better solution for now.. Thanks
yazun
commented
4 years ago The error looks like this when looking at the logs:
query that gets to the DB from JDBC/JPA:
14 12:14:11 gaiadb03 coord[35252]: [17] 2020-09-14 12:14:11 CEST [35252,coord(35252,19235)]:xid[66891-44/19235] [25-1] user=user_local,db=surveys,client=192.168.168.82,query=PostgreSQL JDBC Driver,ERROR: 42P01: node:datanode2, backend_pid:1319, nodename:datanode2,backend_pid:1319,message:relation "sourceresult_1_1" does not exist#0122020-09-14 12:14:11 CEST [35252,coord(35252,19235)]:xid[66891-44/19235] [26-1] user=user_local,db=surveys,client=192.168.168.82,query=PostgreSQL JDBC Driver,LOCATION: pgxc_node_report_error, execRemote.c:11432#0122020-09-14 12:14:11 CEST [35252,coord(35252,19235)]:xid[66891-44/19235] [27-1] user=user_local,db=surveys,client=192.168.168.82,query=PostgreSQL JDBC Driver,STATEMENT: INSERT INTO dr3_ops_cs36_part.sourceresult_1_1 (...)You can see the schema being part of the insert command. Then at the datanode, notice the stripped schema from the table:
gaiadb02 dn[1319]: [19] 2020-09-14 12:14:11 CEST [1319,coord(35252,19235)]:xid[0-678/6149] [1-1] user=user_local,db=surveys,client=192.168.168.147,query=PostgreSQL JDBC Driver,ERROR: 42P01: relation "sourceresult_1_1" does not exist at character 13#0122020-09-14 12:14:11 CEST [1319,coord(35252,19235)]:xid[0-678/6149] [2-1] user=user_local,db=surveys,client=192.168.168.147,query=PostgreSQL JDBC Driver,LOCATION: parserOpenTable, parse_relation.c:1219#0122020-09-14 12:14:11 CEST [1319,coord(35252,19235)]:xid[0-678/6149] [3-1] user=user_local,db=surveys,client=192.168.168.147,query=PostgreSQL JDBC Driver,STATEMENT: INSERT INTO sourceresult_1_1 (...)I tested the case of connecting to the database through JDBC：
1. psql connection to create the table
postgres=# create schema schema_partition;
CREATE SCHEMA
postgres=# create schema schema_base;
CREATE SCHEMA
postgres=# set search_path=schema_base;
SET
postgres=# create table schema_base.order_range(id bigserial not null,userid integer,product text, createdate date not null) partition by range ( createdate );
CREATE TABLE
postgres=# create table schema_partition.order_range_201701 partition of schema_base.order_range(id primary key,userid,product, createdate) for values from ('2017-01-01') to ('2017-02-01');
CREATE TABLE
postgres=# create table schema_partition.order_range_201702 partition of schema_base.order_range(id primary key,userid,product, createdate) for values from ('2017-02-01') to ('2017-03-01');
CREATE TABLE2. JDBC connection to create the function coordinate node logs:
2020-09-16 19:37:25.464 CST,"jennyer","postgres",9650,coord(9650,17),"9.45.201.22:42536",5f61f8f5.25b2,coord(9650,17),1,"CREATE FUNCTION",2020-09-16 19:37:25 CST,4/17,0,LOG,00000,"execute <unnamed>: create or replace function schm_part_4() returns void as $$ begin insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01'); insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01'); end$$ language plpgsql set search_path FROM CURRENT",,,,,,,,,"PostgreSQL JDBC Driver"
2020-09-16 19:37:25.466 CST,"jennyer","postgres",9650,coord(9650,17),"9.45.201.22:42536",5f61f8f5.25b2,coord(9650,17),2,"CREATE FUNCTION",2020-09-16 19:37:25 CST,4/17,621,LOG,00000,"[SAVEPOINT] node->sql_statement:SET search_path TO schema_base",,,,,,"create or replace function schm_part_4() returns void as $$ begin insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01'); insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01'); end$$ language plpgsql set search_path FROM CURRENT",,,"PostgreSQL JDBC Driver"
2020-09-16 19:37:25.468 CST,"jennyer","postgres",9650,coord(9650,17),"9.45.201.22:42536",5f61f8f5.25b2,coord(9650,17),3,"CREATE FUNCTION",2020-09-16 19:37:25 CST,4/17,621,LOG,00000,"[SAVEPOINT] node->sql_statement:create or replace function schm_part_4() returns void as $$ begin insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01'); insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01'); end$$ language plpgsql set search_path FROM CURRENT",,,,,,"create or replace function schm_part_4() returns void as $$ begin insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01'); insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01'); end$$ language plpgsql set search_path FROM CURRENT",,,"PostgreSQL JDBC Driver"data node logs:
2020-09-16 19:37:25.468 CST,"jennyer","postgres",32256,coord(9650,17),"100.105.39.155:6376",5f61f8c4.7e00,coord(9650,17),2,"idle in transaction",2020-09-16 19:36:36 CST,4/24,0,LOG,00000,"statement: create or replace function schm_part_4() returns void as $$ begin insert into schema_partition.order_range_201701 values(1,1,'abc','2017-01-01'); insert into schema_partition.order_range_201702 values(2,2,'abc','2017-02-01'); end$$ language plpgsql set search_path FROM CURRENT",,,,,,,,,"PostgreSQL JDBC Driver"3. JDBC connection to select the function coordinate node logs:
2020-09-16 19:52:41.197 CST,"jennyer","postgres",26329,coord(26329,68),"9.19.161.4:43373",5f61fc89.66d9,coord(26329,68),1,"SET",2020-09-16 19:52:41 CST,4/68,0,LOG,00000,"execute <unnamed>: SET extra_float_digits = 3",,,,,,,,,""
2020-09-16 19:52:41.234 CST,"jennyer","postgres",26329,coord(26329,69),"9.19.161.4:43373",5f61fc89.66d9,coord(26329,69),2,"SET",2020-09-16 19:52:41 CST,4/69,0,LOG,00000,"execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'",,,,,,,,,""
2020-09-16 19:52:41.328 CST,"jennyer","postgres",26329,coord(26329,70),"9.19.161.4:43373",5f61fc89.66d9,coord(26329,70),3,"SELECT",2020-09-16 19:52:41 CST,4/70,0,LOG,00000,"execute <unnamed>: select schm_part_4()",,,,,,,,,"PostgreSQL JDBC Driver"data node logs:
2020-09-16 19:52:41.332 CST,"jennyer","postgres",3525,coord(0,0),"100.105.39.155:7428",5f61fc61.dc5,coord(0,0),1,"idle",2020-09-16 19:52:01 CST,9/33,0,LOG,00000,"statement: SET global_session TO cn002_26329;SET client_encoding TO ""UTF8"";SET DateStyle TO ISO;SET TimeZone TO ""Asia/Shanghai"";SET extra_float_digits TO ""3"";SET application_name TO ""PostgreSQL JDBC Driver"";SET search_path TO schema_base;SET parentPGXCPid TO 26329;",,,,,,,,,"pgxc:cn002"
2020-09-16 19:52:41.333 CST,"jennyer","postgres",3525,coord(26329,70),"100.105.39.155:7428",5f61fc61.dc5,coord(26329,70),2,"idle",2020-09-16 19:52:01 CST,9/34,0,LOG,00000,"statement: BEGIN",,,,,,,,,"PostgreSQL JDBC Driver"
2020-09-16 19:52:41.333 CST,"jennyer","postgres",3525,coord(26329,70),"100.105.39.155:7428",5f61fc61.dc5,coord(26329,70),3,"idle in transaction",2020-09-16 19:52:01 CST,9/34,0,LOG,00000,"statement: SET LOCAL coordinator_lxid TO ""70"";",,,,,,,,,"PostgreSQL JDBC Driver"
2020-09-16 19:52:41.333 CST,"jennyer","postgres",3525,coord(26329,70),"100.105.39.155:7428",5f61fc61.dc5,coord(26329,70),4,"idle in transaction",2020-09-16 19:52:01 CST,9/34,0,LOG,00000,"statement: INSERT INTO schema_partition.order_range_201701 (id, userid, product, createdate) VALUES (1, 1, 'abc'::text, '2017-01-01'::date)",,,,,,,,,"PostgreSQL JDBC Driver"
2020-09-16 19:52:41.335 CST,"jennyer","postgres",3525,coord(26329,70),"100.105.39.155:7428",5f61fc61.dc5,coord(26329,70),5,"idle in transaction",2020-09-16 19:52:01 CST,9/34,620,LOG,00000,"statement: INSERT INTO schema_partition.order_range_201702 (id, userid, product, createdate) VALUES (2, 2, 'abc'::text, '2017-02-01'::date)",,,,,,,,,"PostgreSQL JDBC Driver"
2020-09-16 19:52:41.336 CST,"jennyer","postgres",3525,coord(26329,70),"100.105.39.155:7428",5f61fc61.dc5,coord(26329,70),6,"idle in transaction",2020-09-16 19:52:01 CST,9/34,620,LOG,00000,"statement: COMMIT TRANSACTION",,,,,,,,,"PostgreSQL JDBC Driver"
2020-09-16 19:52:41.352 CST,"jennyer","postgres",3525,coord(0,0),"100.105.39.155:7428",5f61fc61.dc5,coord(0,0),7,"idle",2020-09-16 19:52:01 CST,9/35,0,LOG,00000,"statement: RESET ALL;RESET SESSION AUTHORIZATION;RESET transaction_isolation;RESET global_session",,,,,,,,,"PostgreSQL JDBC Driver"4、 JDBC connection to select partition tables logs: coordinate node logs:
2020-09-16 19:59:47.872 CST,"jennyer","postgres",20628,coord(20628,92),"9.45.201.3:43458",5f61fe33.5094,coord(20628,92),1,"SET",2020-09-16 19:59:47 CST,4/92,0,LOG,00000,"execute <unnamed>: SET extra_float_digits = 3",,,,,,,,,""
2020-09-16 19:59:47.907 CST,"jennyer","postgres",20628,coord(20628,93),"9.45.201.3:43458",5f61fe33.5094,coord(20628,93),2,"SET",2020-09-16 19:59:47 CST,4/93,0,LOG,00000,"execute <unnamed>: SET application_name = 'PostgreSQL JDBC Driver'",,,,,,,,,""
2020-09-16 19:59:47.990 CST,"jennyer","postgres",20628,coord(20628,94),"9.45.201.3:43458",5f61fe33.5094,coord(20628,94),3,"SELECT",2020-09-16 19:59:47 CST,4/94,0,LOG,00000,"execute <unnamed>: select * from schema_base.order_range",,,,,,,,,"PostgreSQL JDBC Driver"data node logs:
2020-09-16 19:59:47.991 CST,"jennyer","postgres",7875,coord(0,0),"100.105.39.155:7913",5f61fe0f.1ec3,coord(0,0),1,"idle",2020-09-16 19:59:11 CST,12/47,0,LOG,00000,"statement: SET global_session TO cn002_20628;SET client_encoding TO ""UTF8"";SET DateStyle TO ISO;SET TimeZone TO ""Asia/Shanghai"";SET search_path TO schema_base;SET extra_float_digits TO ""3"";SET application_name TO ""PostgreSQL JDBC Driver"";SET parentPGXCPid TO 20628;",,,,,,,,,"pgxc:cn002"
2020-09-16 19:59:47.992 CST,"jennyer","postgres",7875,coord(0,0),"100.105.39.155:7913",5f61fe0f.1ec3,coord(0,0),2,"idle",2020-09-16 19:59:11 CST,12/48,0,LOG,00000,"statement: SELECT id, userid, product, createdate FROM order_range",,,,,,,,,"PostgreSQL JDBC Driver"
2020-09-16 19:59:47.994 CST,"jennyer","postgres",7875,coord(0,0),"100.105.39.155:7913",5f61fe0f.1ec3,coord(0,0),3,"idle",2020-09-16 19:59:11 CST,12/49,0,LOG,00000,"statement: RESET ALL;RESET SESSION AUTHORIZATION;RESET transaction_isolation;RESET global_session",,,,,,,,,"PostgreSQL JDBC Driver"It looks normal and there is no problem.
We have separate schema for partitions of partitioned tables and doing direct inserts into partitions.
While inserting
we see in the logs
and then query with schema stripped from the table on the insert:
Which of course cannot work as the table is in the other schema (schema_partition).
If relevant, we also issue
before and were wondering if this could cause this bug.
This is a blocker for us as well. Hope somebody can tune in for this one..
Thanks