Citus Compatibility

[sstubbs]

Is this compatible with the citus extension? I am currently using janusgraph. This looks interesting but I am worried how it would scale if I can't use citus.

[emotionbug]

+1 I think this is a good point. If AGE support Citus, it would be a great distributed graph database.

[emotionbug]

-- testing script
LOAD 'age';
SET search_path = ag_catalog, "$user", public;
SELECT create_graph('g');

SELECT create_distributed_table( 'g.ag_label_vertex', 'id' );
-- throw: data type ag_catalog.graphid has no default operator class for specified partition method

I tested follow script with below dockerfile script.

and, it need to support from citus, because of currently only support using the default operator class https://github.com/citusdata/citus/blob/67bc990c1caaf6aa174b85253e1e154d8b368044/src/backend/distributed/commands/create_distributed_table.c#L1266

# original script from citusdata/docker
FROM postgres:11.11
ARG VERSION=10.0.3

ENV CITUS_VERSION ${VERSION}.citus-1

RUN apt-get update
RUN apt-get install -y --no-install-recommends \
  bison \
  build-essential \
  ca-certificates \
  flex \
  git \
  postgresql-plpython3-11 \
  postgresql-server-dev-11 \
  curl

RUN git clone https://github.com/apache/incubator-age /age \
  && cd /age && make install

# install Citus
RUN curl -s https://install.citusdata.com/community/deb.sh | bash \
    && apt-get install -y postgresql-$PG_MAJOR-citus-10.0.=$CITUS_VERSION \
                          postgresql-$PG_MAJOR-hll=2.15.citus-1 \
                          postgresql-$PG_MAJOR-topn=2.3.1 \
    && apt-get purge -y --auto-remove curl \
    && rm -rf /var/lib/apt/lists/*

# add citus to default PostgreSQL config
RUN echo "shared_preload_libraries='citus,age'" >> /usr/share/postgresql/postgresql.conf.sample

# add scripts to run after initdb
COPY 001-create-age-extension.sql /docker-entrypoint-initdb.d/
COPY 002-create-citus-extension.sql /docker-entrypoint-initdb.d/

# add health check script
COPY pg_healthcheck wait-for-manager.sh /
RUN chmod +x /wait-for-manager.sh

# entry point unsets PGPASSWORD, but we need it to connect to workers
# https://github.com/docker-library/postgres/blob/33bccfcaddd0679f55ee1028c012d26cd196537d/12/docker-entrypoint.sh#L303
RUN sed "/unset PGPASSWORD/d" -i /usr/local/bin/docker-entrypoint.sh

HEALTHCHECK --interval=4s --start-period=6s CMD ./pg_healthcheck

-- 001-create-age-extension.sql
CREATE EXTENSION age;

-- 002-create-citus-extension.sql
-- original script from citusdata/docker

-- wrap in transaction to ensure Docker flag always visible
BEGIN;
CREATE EXTENSION citus;

-- add Docker flag to node metadata
UPDATE pg_dist_node_metadata SET metadata=jsonb_insert(metadata, '{docker}', 'true');
COMMIT;

[yjhjstz]

@emotionbug try https://github.com/bitnine-oss/agensgraph instead extension version.

[emotionbug]

@yjhjstz of course

CREATE GRAPH g;
SET GRAPH_PATH="g";
CREATE VLABEL people;
SELECT create_distributed_table('g.people', 'id');
-- ERROR:  people is not a regular, foreign or partitioned table

SELECT create_distributed_table('g.ag_edge', 'id');
-- ERROR:  cannot create trigger on graph label

in this time, currently only support using the default operator class has not thrown. but, citus recognized it as an invalid table or AgensGraph cannot create trigger.

Dockerfile https://github.com/emotionbug/ag-citus-docker/tree/AgensGraph

FROM bitnine/agensgraph:v2.1.3-debian
ARG VERSION=8.3.2

ENV CITUS_VERSION ${VERSION}.citus-1

# install Citus
RUN apt-get update \
    && build_deps='build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt1-dev libssl-dev libxml2-utils xsltproc pkg-config uuid-dev' \
    && apt-get install -y $build_deps git libcurl4-openssl-dev pax-utils \
    && git clone git clone https://github.com/citusdata/citus.git \
    && git checkout release-8.3 \
    && cd /citus/citus \
    && ./configure \
    && make install \
    && apt-get purge -y --auto-remove curl \
    && rm -rf /var/lib/apt/lists/*

# add citus to default PostgreSQL config
RUN echo "shared_preload_libraries='citus'" >> /usr/local/share/postgresql/postgresql.conf.sample

# add scripts to run after initdb
COPY 000-configure-stats.sh 001-create-citus-extension.sql /docker-entrypoint-initdb.d/

# add health check script
COPY pg_healthcheck /

HEALTHCHECK --interval=4s --start-period=6s CMD ./pg_healthcheck

[yjhjstz]

Citus doesn't support bare inherited tables (i.e., not a partition or partitioned table).

[yjhjstz]

@emotionbug https://github.com/yjhjstz/citus-age/tree/age , here is branch which I removed inherited table limitation. We can work together.

postgres=# SELECT create_distributed_table('g.person', 'id');
NOTICE:  Copying data from local table...
 create_distributed_table 
--------------------------

(1 row)

postgres=# table pg_dist_node;
 nodeid | groupid | nodename  | nodeport | noderack | hasmetadata | isactive | noderole | nodecluster 
--------+---------+-----------+----------+----------+-------------+----------+----------+-------------
      1 |       1 | 127.0.0.1 |     5433 | default  | f           | t        | primary  | default
(1 row)

postgres=# table pg_dist_shard
pg_dist_shard                      pg_dist_shard_logical_relid_index  pg_dist_shard_placement            pg_dist_shard_shardid_index        pg_dist_shardid_seq
postgres=# table pg_dist_shard;
 logicalrelid | shardid | shardstorage | shardminvalue | shardmaxvalue 
--------------+---------+--------------+---------------+---------------
 g.person     |  102136 | t            | -2147483648   | -2013265921
 g.person     |  102137 | t            | -2013265920   | -1879048193
 g.person     |  102138 | t            | -1879048192   | -1744830465
 g.person     |  102139 | t            | -1744830464   | -1610612737
 g.person     |  102140 | t            | -1610612736   | -1476395009
 g.person     |  102141 | t            | -1476395008   | -1342177281
 g.person     |  102142 | t            | -1342177280   | -1207959553
 g.person     |  102143 | t            | -1207959552   | -1073741825
 g.person     |  102144 | t            | -1073741824   | -939524097
 g.person     |  102145 | t            | -939524096    | -805306369
 g.person     |  102146 | t            | -805306368    | -671088641
 g.person     |  102147 | t            | -671088640    | -536870913
 g.person     |  102148 | t            | -536870912    | -402653185
 g.person     |  102149 | t            | -402653184    | -268435457
 g.person     |  102150 | t            | -268435456    | -134217729
 g.person     |  102151 | t            | -134217728    | -1
 g.person     |  102152 | t            | 0             | 134217727
 g.person     |  102153 | t            | 134217728     | 268435455
 g.person     |  102154 | t            | 268435456     | 402653183
 g.person     |  102155 | t            | 402653184     | 536870911
 g.person     |  102156 | t            | 536870912     | 671088639
 g.person     |  102157 | t            | 671088640     | 805306367
 g.person     |  102158 | t            | 805306368     | 939524095
 g.person     |  102159 | t            | 939524096     | 1073741823
 g.person     |  102160 | t            | 1073741824    | 1207959551
 g.person     |  102161 | t            | 1207959552    | 1342177279
 g.person     |  102162 | t            | 1342177280    | 1476395007
 g.person     |  102163 | t            | 1476395008    | 1610612735
 g.person     |  102164 | t            | 1610612736    | 1744830463
 g.person     |  102165 | t            | 1744830464    | 1879048191
 g.person     |  102166 | t            | 1879048192    | 2013265919
 g.person     |  102167 | t            | 2013265920    | 2147483647
(32 rows)

[yjhjstz]

@emotionbug can I touch you with Line or WeChat or something?

[emotionbug]

@yjhjstz my wechat id is emotionbug.

[sstubbs]

Is there anything I can test with this to help?

[joefagan]

@sstubbs Perhaps you could try to load both Citus and AGE extension and see whether they can both load together? Is order important?

[vilkinsons]

Has anyone dug around this further? Citus + AGE would be quite the killer combo.

[aked21]

I know some tests have been done for ShardingSphere + AGE. https://shardingsphere.apache.org/

[vilkinsons]

I know some tests have been done for ShardingSphere + AGE. https://shardingsphere.apache.org/

Do you have any more info on those? We're revisiting this question.

[rafsun42]

Has anyone found a workaround to make age work with citus?

I see that the following works if _ag_label_vertex does not have any child table:

SELECT create_distributed_table('qwe._ag_label_vertex', 'id');

However, the CREATE command does not work afterwards. I tried multiple times.

postgres=# SELECT * FROM ag_catalog.cypher('qwe', $$ CREATE ({age:2}) $$) as (a ag_catalog.agtype);
ERROR:  unhandled cypher(cstring) function call
DETAIL:  qwe
postgres=# SELECT * FROM ag_catalog.cypher('qwe', $$ CREATE () $$) as (a ag_catalog.agtype);
ERROR:  cannot display a value of type internal
postgres=# SELECT * FROM ag_catalog.cypher('qwe', $$ CREATE ({age:2}) $$) as (a ag_catalog.agtype);
ERROR:  cannot display a value of type internal