Does pg_shard support user defined functions?

[keshriasthana]

Hi,

If I create a UDF which will INSERT/DELETE/UPDATE/SELECT data from tables, is that UDF supposed to work in pg_shard?

I have created a functions in pg_shard:

CREATE FUNCTION test_shard(p1 UUID, p2 INTEGER)
RETURNS VOID AS $$
BEGIN
  INSERT INTO customer_reviews(customer_id, review_rating) VALUES(p1, p2);
END; $$
LANGUAGE PLPGSQL;

When I run this:

SELECT test_shard('d541ab8d-37de-49b1-b242-1e0982c86559', 7)

I get error:

ERROR: unrecognized node type: 2100 CONTEXT: SQL statement "INSERT INTO customer_reviews(customer_id, review_rating) VALUES(p1, p2)" PL/pgSQL function test_shard(uuid,integer) line 3 at SQL statement

Thanks

[jasonmp85]

Yes, it will work, but statements referencing distributed tables cannot have their plans cached. Because PL/pgSQL automatically caches plans for any bare SQL expressions, pg_shard users must be careful to always interact with distributed tables using the EXECUTE keyword:

CREATE FUNCTION test_shard(p1 UUID, p2 INTEGER)
RETURNS VOID AS $$
BEGIN
  EXECUTE 'INSERT INTO customer_reviews(customer_id, review_rating) VALUES ($1, $2)'
          USING p1, p2;
END; $$
LANGUAGE PLPGSQL;

That should fix this problem.

[jasonmp85]

I'll try to figure out whether we can present a better error message for this. It will be hard but could help our users out, since many run into this issue.

[jasonmp85]

I've merged #121, so the latest pg_shard will produce an error like the following:

ERROR:  cannot cache distributed plan
DETAIL:  PL/pgSQL's statement caching is unsupported by pg_shard.
HINT:  Bypass caching by using the EXECUTE keyword instead.
CONTEXT:  <THE ORIGINAL PL/PGSQL>

Hopefully this is far less confusing than the old message :wink: