How to use triggers on schema based sharding?

[Youssef AbouEgla (YoussefEgla)]

I'm following this example and using run_command_on_colocated_placements function.

SET search_path TO myapp;

CREATE TABLE IF NOT EXISTS little_vals (key int, val int);
CREATE TABLE IF NOT EXISTS big_vals    (key int, val int);

CREATE OR REPLACE FUNCTION embiggen() RETURNS TRIGGER AS $$
  BEGIN
    IF (TG_OP = 'INSERT') THEN
      EXECUTE format(
        'INSERT INTO %s (key, val) SELECT ($1).key, ($1).val*2;',
        TG_ARGV[0]
      ) USING NEW;
    END IF;
    RETURN NULL;
  END;
$$ LANGUAGE plpgsql;

SELECT citus_schema_distribute(myapp);

SELECT run_command_on_colocated_placements(
  'little_vals',
  'big_vals',
  $cmd$
    CREATE TRIGGER after_insert AFTER INSERT ON %s
      FOR EACH ROW EXECUTE PROCEDURE embiggen(%L)
  $cmd$
);

Then I run this query

INSERT INTO little_vals (key, val) VALUES (1, 2);
SELECT * FROM big_vals;

• big_vals is empty
• little_vals row added as expected.

Also I tried exactly as the example from the docs and got the same result.

I'm running on docker compose

services:
  master:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_pg_master"
    image: "citusdata/citus:12.1.3"
    ports: ["${COORDINATOR_EXTERNAL_PORT:-5432}:5432"]
    volumes:
      - pg_master:/var/lib/postgresql/data
    labels: [com.citusdata.role=Master]
    environment: &PG_ENV
      PGDATA: "/var/lib/postgresql/data/pgdata"
      POSTGRES_USER: "${POSTGRES_USER:-postgres}"
      POSTGRES_PASSWORD: "${POSTGRES_PASSWORD:-postgres}"
      POSTGRES_DB: "${POSTGRES_DB:-postgres}"
      PGUSER: "${POSTGRES_USER:-postgres}"
      PGPASSWORD: "${POSTGRES_PASSWORD}"
      POSTGRES_HOST_AUTH_METHOD: "${POSTGRES_HOST_AUTH_METHOD:-trust}"
  manager:
    container_name: "${COMPOSE_PROJECT_NAME:-citus}_pg_manager"
    image: "citusdata/membership-manager:0.3.0"
    volumes:
      - "${DOCKER_SOCK:-/var/run/docker.sock}:/var/run/docker.sock"
      - healthcheck-volume:/healthcheck
      - pg_manager:/var/lib/postgresql/data
    depends_on: [master]
    environment: *PG_ENV
  pg_worker_0:
    image: "citusdata/citus:12.1.3"
    labels: ["com.citusdata.role=Worker"]
    depends_on: [manager]
    command: "/wait-for-manager.sh"
    volumes:
      - healthcheck-volume:/healthcheck
      - pg_worker_0:/var/lib/postgresql/data
    environment: *PG_ENV
  pg_worker_1:
    image: "citusdata/citus:12.1.3"
    labels: ["com.citusdata.role=Worker"]
    depends_on: [manager]
    command: "/wait-for-manager.sh"
    volumes:
      - healthcheck-volume:/healthcheck
      - pg_worker_1:/var/lib/postgresql/data
    environment: *PG_ENV

[Emel Şimşek (emelsimsek)]

Youssef AbouEgla (@YoussefEgla) this sounds like a bug. Can you check if triggers are created for the shards of the table?
Onur Tirtir (@onurctirtir) what do you think?