Cannot create table as .. distribute by replication

[yazun]

When creating a table with

create table X by replication as select on sharded table with group by we get

ERROR: XX000 - Shard tables from different groups should not be invloved in one Query, Shard tables should not be invloved in one Query with other tables, such as hash table.

There is a typo in the error message, but also Postgres-XL supported this so it is a regression..

[yazun]

Checking the code unveils that setting new GUC enable_group_across_query allows for such queries.

So it's more of a documentation problem: this new GUC could be mentioned in the error message and it would be good to read why such a query is problematic and guarded by this GUC.

But then testing with it shows Tbase is unable to perform the create table as... create table test_1 distribute by replication as select i from generate_series(1,1000) i;

ERROR: node:datanode12, backend_pid:12239, nodename:datanode1,backend_pid:6473,message:node:datanode9, backend_pid:3464, nodename:datanode9,backend_pid:3464,message:permission denied for relation (null) Time: 2454.169 ms (00:02.454)

[yazun]

Not much better with specifying shard(..)

create table test_1 distribute by shard(i)  as select i from generate_series(1,1000) i;
ERROR:  Invalid distribution type

[yazun]

The half-baked workaround is to: create table x distribute by replication as select i from generate_series(1,1000) i limit 0; then do insert into table select... This is quite a limitation in comparison to PG-XL and PG - do you plan to fix it? Thanks, Krzysztof

[chenaisheng]

postgres=# create table t_rep (f1 int,f2 int) distribute by replication; CREATE TABLE

postgres=# create table test_1 distribute by replication as select * from t_rep; INSERT 0 0 postgres=# \d+ test_1 Table "public.test_1" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- f1 | integer | | | | plain | | f2 | integer | | | | plain | | Distribute By: REPLICATION Location Nodes: ALL DATANODES

[yazun]

We still have to segfault on the v2.3 branch, i.e.

create table gaia_data_segment distribute by replication as with d as
( select    '2010-01-01T00:00:00'::date JDGaiaEpoch, 
            '2014-07-24T00:00:00'::date Segment_0_start, 
            '2015-06-03T24:00:00'::date Segment_0_end, 
            '2015-06-03T24:00:00'::date Segment_1_start, 
            '2015-09-16T24:00:00'::date Segment_1_end, 
            '2015-09-16T24:00:00'::date Segment_2_start, 
            '2016-05-23T24:00:00'::date Segment_2_end ) 
select JDGaiaEpoch,0::int as segment, Segment_0_start as start,Segment_0_end as end, Segment_0_end-Segment_0_start segment_length 
from d 
union all 
select JDGaiaEpoch,1::int as segment, Segment_1_start,Segment_1_end ,Segment_1_end-Segment_1_start segment_length   from d 
union all 
select JDGaiaEpoch,2::int as segment, Segment_2_start,Segment_2_end,Segment_2_end-Segment_2_start segment_length   from d;

Crashes datanodes..

[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
Core was generated by `postgres: cu7user surveys ::1(46532) REMOTE SUBPLAN (cn:15'.
Program terminated with signal 11, Segmentation fault.
#0  plantree_walk_initplans (context=0xd9ede6, walker=0x720c80 <ResetRemoteSubplanCursor>, subplans=0x1dc8140, plans=<optimized out>) at ../../../../xz.git/src/backend/nodes/nodeFuncs.c:3917
3917    ../../../../xz.git/src/backend/nodes/nodeFuncs.c: No such file or directory.
Missing separate debuginfos, use: debuginfo-install cyrus-sasl-lib-2.1.26-23.el7.x86_64 glibc-2.17-325.el7_9.x86_64 keyutils-libs-1.5.8-3.el7.x86_64 krb5-libs-1.15.1-51.el7_9.x86_64 libcom_err-1.42.9-19.el7.x86_64 libselinux-2.5-15.el7.x86_64 libxml2-2.9.1-6.el7_9.6.
x86_64 nspr-4.32.0-1.el7_9.x86_64 nss-3.67.0-4.el7_9.x86_64 nss-softokn-freebl-3.67.0-3.el7_9.x86_64 nss-util-3.67.0-1.el7_9.x86_64 openldap-2.4.44-24.el7_9.x86_64 openssl-libs-1.0.2k-24.el7_9.x86_64 pcre-8.32-17.el7.x86_64 xz-libs-5.2.2-1.el7.x86_64 zlib-1.2.7-19.el
7_9.x86_64
(gdb) bt
#0  plantree_walk_initplans (context=0xd9ede6, walker=0x720c80 <ResetRemoteSubplanCursor>, subplans=0x1dc8140, plans=<optimized out>) at ../../../../xz.git/src/backend/nodes/nodeFuncs.c:3917
#1  plantree_walker (context=0xd9ede6, walker=0x720c80 <ResetRemoteSubplanCursor>, top_subplans=0x1dc8140, plan=0x1db95c8) at ../../../../xz.git/src/backend/nodes/nodeFuncs.c:3942
#2  ResetRemoteSubplanCursor (plan=0x1db95c8, subplans=subplans@entry=0x1dc8140, context=context@entry=0xd9ede6) at ../../../../xz.git/src/backend/executor/execMain.c:3900
#3  0x000000000072a05c in plantree_walker (context=0xd9ede6, walker=0x720c80 <ResetRemoteSubplanCursor>, top_subplans=0x1dc8140, plan=0x1db90c0) at ../../../../xz.git/src/backend/nodes/nodeFuncs.c:3946
#4  ResetRemoteSubplanCursor (context=0xd9ede6, subplans=0x1dc8140, plan=0x1db90c0) at ../../../../xz.git/src/backend/executor/execMain.c:3900
#5  EvalPlanQualSetPlan (epqstate=0x1db0820, subplan=<optimized out>, auxrowmarks=0x0) at ../../../../xz.git/src/backend/executor/execMain.c:3213
#6  0x000000000075e25f in ExecInitModifyTable () at ../../../../xz.git/src/backend/executor/nodeModifyTable.c:3231
#7  0x0000000000726218 in ExecInitNode (node=0x1bee9b8, estate=0x1daf728, eflags=0) at ../../../../xz.git/src/backend/executor/execProcnode.c:177
#8  0x0000000000720392 in InitPlan (eflags=0, queryDesc=0x1da5af8) at ../../../../xz.git/src/backend/executor/execMain.c:1213
#9  standard_ExecutorStart () at ../../../../xz.git/src/backend/executor/execMain.c:359
#10 0x00000000009acced in ExecutorStart (eflags=0, queryDesc=0x1da5af8) at ../../../../xz.git/src/backend/executor/execMain.c:568
#11 ProcessQuery (plan=0x1dcf4a8, sourceText=<optimized out>, params=<optimized out>, queryEnv=<optimized out>, dest=<optimized out>, completionTag=0x7ffdb8bfe740 "", instrument=0) at ../../../../xz.git/src/backend/tcop/pquery.c:212
#12 0x00000000009b3fd3 in PortalRunMulti (portal=portal@entry=0x1d97ce8, isTopLevel=isTopLevel@entry=1 '\001', setHoldSnapshot=setHoldSnapshot@entry=0 '\000', dest=0xf413e0 <donothingDR.lto_priv.0>, dest@entry=0x1cdc7c8, altdest=0xf413e0 <donothingDR.lto_priv.0>,
    altdest@entry=0x1cdc7c8, completionTag=completionTag@entry=0x7ffdb8bfe740 "") at ../../../../xz.git/src/backend/tcop/pquery.c:2124
#13 0x00000000009b4973 in PortalRun (portal=portal@entry=0x1d97ce8, count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=1 '\001', run_once=<optimized out>, dest=dest@entry=0x1cdc7c8, altdest=altdest@entry=0x1cdc7c8, completionTag=0x7ffdb8bfe740 "")
    at ../../../../xz.git/src/backend/tcop/pquery.c:1382
#14 0x00000000009b615d in exec_execute_message (max_rows=9223372036854775807, portal_name=0x1cdc3b8 "p_1_3b8b_2_735dd249") at ../../../../xz.git/src/backend/tcop/postgres.c:2995
#15 PostgresMain (argc=<optimized out>, argv=<optimized out>, dbname=<optimized out>, username=<optimized out>) at ../../../../xz.git/src/backend/tcop/postgres.c:5571
#16 0x00000000008f7507 in BackendRun (port=0x1be2490) at ../../../../xz.git/src/backend/postmaster/postmaster.c:4982
#17 BackendStartup (port=0x1be2490) at ../../../../xz.git/src/backend/postmaster/postmaster.c:4654
#18 ServerLoop () at ../../../../xz.git/src/backend/postmaster/postmaster.c:1959
#19 0x00000000008f8400 in PostmasterMain () at ../../../../xz.git/src/backend/postmaster/postmaster.c:1567
#20 0x00000000004f97ed in main (argc=5, argv=0x1bb6ac0) at ../../../../xz.git/src/backend/main/main.c:233

[JennyJennyChen]

Can you give a specific case? I have no problem with the simple test case here:

[JennyJennyChen]

hi, Similar problems occasionally occur when we do complex tests, and it have been resolved. The commit is: d11ae8a64554f9c74dc830825e75af5608d47355

[yazun]

Excellent, Thanks a lot!