citusdata / citusdata/citus

Relation sometimes does not exist in function

Open
#3,827 2 comments 0 reactions 0 assignees View on GitHub
usability
Dominant language
C
Stars
12.8k
Forks
794
Avg merge
2d 14h
Merged PRs (30d)
31

Description

[_Running PostgreSQL 12.2, Citus 9.2-4_]

I have the following tables:
```
CREATE TABLE "_base" (
"id" UUID DEFAULT UUID_GENERATE_V4()
, "created" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE "users" (
LIKE "_base" INCLUDING DEFAULTS INCLUDING INDEXES

, "administrator" BOOLEAN NOT NULL DEFAULT FALSE
, "enabled" BOOLEAN NOT NULL DEFAULT FALSE
, "email" CITEXT NOT NULL
, "username" CITEXT NOT NULL
, "password" CHARACTER VARYING DEFAULT NULL
, "passwordReset" CHARACTER VARYING DEFAULT NULL

, PRIMARY KEY ( "id" )
);

CREATE TABLE "units" (
LIKE "_base" INCLUDING DEFAULTS INCLUDING INDEXES

, "users.id" UUID NOT NULL
, "location" GEOGRAPHY NOT NULL
, "longitude" DOUBLE PRECISION GENERATED ALWAYS AS ( ST_X( "location"::GEOMETRY ) ) STORED
, "latitude" DOUBLE PRECISION GENERATED ALWAYS AS ( ST_Y( "location"::GEOMETRY ) ) STORED

, PRIMARY KEY ( "id", "users.id" )
);
```
(`"units"."users.id"` is a foreign key to `"users"."id"`.)

The tables are distributed like so:
```
SELECT CREATE_REFERENCE_TABLE( 'users' );
SELECT CREATE_DISTRIBUTED_TABLE( 'units', 'users.id' );
```

I have the following function:
```
CREATE FUNCTION "units.distances" (
"longitudeInput" DOUBLE PRECISION,
"latitudeInput" DOUBLE PRECISION,
"distanceInput" DOUBLE PRECISION
)
RETURNS TABLE (
"id" UUID,
"users.id" UUID,
"longitude" DOUBLE PRECISION,
"latitude" DOUBLE PRECISION,
"distance" DOUBLE PRECISION
)
AS
$$
BEGIN
RETURN QUERY
SELECT
"units"."id",
"units"."users.id",
"units"."longitude",
"units"."latitude",
ST_Distance( "units"."location", ST_MakePoint( $1, $2 ) ) AS "distance"
FROM
"units"
WHERE
ST_DWithin( "units"."location" , ST_MakePoint( $1, $2 ), $3 )
AND
ST_Distance( "units"."location", ST_MakePoint( $1, $2 ) ) != 0
;
END
$$
LANGUAGE PLPGSQL;
```

If I run:
```
SELECT
*
FROM
"units.distances"( 0, 0, 100000 )
;
```
from the coordinator it works great.

If, instead, from the coordinator I do:
```
SELECT
*
FROM
"units.distances"( 0, 0, 100000 ) AS "u"
INNER JOIN
"users"
ON
"u"."users.id" = "users"."id"
;
```

I get:
```
ERROR: relation "units" does not exist
CONTEXT: while executing command on postgres1:5432
PL/pgSQL function "units.distances"(double precision,double precision,double precision) line 3 at RETURN QUERY
```

The function gets loaded onto each node directly, not via `CREATE_DISTRIBUTED_FUNCTION()` (I was having issues with it, I forget what they were). I have confirmed they are all identical. The strange part to me is the query/function needs to access the `units` relation in both examples.

Am I doing something wrong? I assume the coordinator parses the query and then the call goes out to the nodes (thus it not complaining in the first example)? Why would that not be so in the second example as well?

**Edit**
If I do:
```
SELECT
"u"."id",
"units"."longitude",
"units"."latitude",
"u"."distance"
FROM
"units.distances"( 0, 0, 100000 ) AS "u"
INNER JOIN
"units"
ON
"u"."id" = "units"."id"
;
```
everything works fine.

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.