CODEWITHSUNDEEP
postgresqlcitus
Alex
Alex

Reputation: 691

I get error when create distributed table on citus (postgres)

I created a table on Citus: CREATE TABLE myschema.mytable

Then I created function that gets data from table myschema.mytable:

CREATE FUNCTION myschema.myfunction(id INT)
RETURNS INT AS $$
DECLARE approved_count INT;
BEGIN
SELECT COUNT(id) INTO approved_count 
FROM myschema.mytable
WHERE id = $1 AND is_deleted = FALSE AND is_flagged = TRUE;
RETURN approved_count;
END;
$$ LANGUAGE plpgsql

Then I added the constraint on table myschema.mytable:

ALTER TABLE myschema.mytable ADD CONSTRAINT myconstraint CHECK ((myschema.myfunction()=(0)));

All queries above were executed successfully.

Finally, I created a distributed table:

SELECT create_distributed_table('myschema.mytable', 'tenant_id');

And I got the error:

ERROR:  function myschema.myfunction() does not exist
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
CONTEXT:  while executing command on 10.0.0.***:****
SQL state: 42883

I tried to run queries in the other order. I created a table, distributed table, and function successfully. But I couldn't add constraint and got the same error.

As I understand my Worker does not see my function. How can I resolve this problem?

Upvotes: 1

Views: 1874

Answers (1)

Hanefi
Hanefi

Reputation: 114

Citus currently does not propagate CREATE FUNCTION queries to worker nodes. You need to manually create the function in the workers yourself.

You can also use the following query to manually create your function in your worker nodes:

SELECT *
FROM run_command_on_workers($cmd$
  CREATE FUNCTION myschema.myfunction(id INT) RETURNS INT AS
  $$ DECLARE approved_count INT;
    BEGIN 
    SELECT 
      COUNT(id)
    INTO
      approved_count 
    FROM 
      myschema.mytable 
    WHERE 
      id = $1 
      AND is_deleted = FALSE 
      AND is_flagged = TRUE;
    RETURN approved_count;
    END;
  $$ LANGUAGE plpgsql
$cmd$);

You may need to create myschema on the worker nodes as well (depending on your Citus version). You can also do this using run_command_on_workers() udf with a CREATE SCHEMA IF NOT EXISTS command.

You can check out the documentation for run_command_on_workers() here.

FYI: I am currently working on a feature to distribute functions in worker nodes. We plan to ship it in the next Citus major release

Edit: create_distributed_function UDF is there in Citus >= v9.0 You can see the docs at https://docs.citusdata.com/en/v9.0/develop/api_udf.html#create-distributed-function and some more in https://docs.citusdata.com/en/v9.0/faq/faq.html#how-do-i-create-database-roles-functions-extensions-etc-in-a-citus-cluster

Upvotes: 3

Related Questions