SELECT after RPC function call does not JOIN new data

[jdgamble555]

I have this function to upsert a post with the post's tags:

-- upsert_post
DROP FUNCTION IF EXISTS upsert_post;
CREATE OR REPLACE FUNCTION upsert_post(
  title text,
  content text,
  tags text[],
  id uuid DEFAULT NULL,
  parent uuid DEFAULT NULL,
  status post_status DEFAULT 'draft',
  type post_type DEFAULT 'post',
  updated_at timestamptz DEFAULT now(),
  published_at timestamptz DEFAULT NULL
)
RETURNS posts
LANGUAGE plpgsql
AS $$
DECLARE
  affected_post posts%ROWTYPE;
BEGIN

  -- Check if post has id
  IF upsert_post.id IS NOT NULL THEN

    -- Update the existing post
    UPDATE posts AS p
    SET title = upsert_post.title,
        content = upsert_post.content,
        status = upsert_post.status,
        type = upsert_post.type,
        parent = upsert_post.parent,        
        updated_at = upsert_post.updated_at,
        published_at = COALESCE(upsert_post.published_at, p.published_at)
    WHERE p.id = upsert_post.id
    RETURNING * INTO affected_post;

    -- Remove existing tags for the post
    DELETE FROM tags WHERE pid = affected_post.id;

  ELSE

    -- Insert a new post
    INSERT INTO posts AS i (
        title,
        content,
        status,
        type,
        parent,
        updated_at,
        published_at
    )
    VALUES (
        upsert_post.title,
        upsert_post.content,
        upsert_post.status,
        upsert_post.type,
        upsert_post.parent,
        upsert_post.updated_at,
        upsert_post.published_at        
    )
    RETURNING * INTO affected_post;

  END IF;

  -- Insert new tags with the post
  INSERT INTO tags (name, pid)
  SELECT unnest(upsert_post.tags), affected_post.id;

  -- Return the updated post
  RETURN affected_post;

END;
$$;

I call in Supabase like so:

const { error: _error, data } = await supabase
    .rpc('upsert_post', {
        title,
        content,
        parent,
        status,
        tags
    })
    .select(`*, tags(*)`)
    .single();

The function works great, and the database gets the tags updated correctly. However, the SELECT option contains an EMPTY array of tags. If I run a second call to the database with the select the exact same way, it will return the tags as expected.

Is there any reason why the select after the rpc would not correctly grab the tabs, yet a another select call right after works correctly? Again, the data is in the database.

J

[laurenceisla]

I tried it and got these results:
When id is present, I get an empty array for tags [] consistently (even after a second select). PostgreSQL behaves the same way when doing a join:

SELECT * FROM upsert_posts(...) p
LEFT JOIN tags t ON p.id = t.pid;

And when I include the id, then I don't get an empty array, but the data belongs to the tags before the update. It might be because the INSERT done to tags is not committed yet, that's why it's using the old values when doing the JOIN. Don't know if there's a workaround, but maybe an extra request is needed here, or make the function return the tags alongside the posts.

[jdgamble555]

Interesting, but makes sense. I can't return the tags with the posts, as I need to specifically return a posts type. My real query looks something like this:

.select(`*, author(*), tags(name), hearts(count)${session ? ', bookmarks(count), liked:hearts(uid)' : ''}`, { count: 'exact' })

which gets a lot more complicated, and I'm still adding features. I need to return a posts type to do my joins with postgREST, as I don't want to maintain a complicated sql function and have to redeploy my migration every time there is an update.

For the moment I'm making two calls: an rpc function, then a separate from function right after. This is not ideal as it requires two different calls or trips across the internet universe, but not a deal breaker for the moment.

Ideas...

• I'm wondering if I could have two functions: upsert_post, and do_upsert, which would run inside the upsert_post function. Although if they both are a transaction, maybe it would have the same result?
• Somehow COMMIT inside the function?
• Any other ideas?

Trying to think outside the box.

@steve-chavez
Note: If relational inserts ever get implemented, and someone wants to return the select data immediately after, this would need to be considered.

J

[wolfgangwalther]

You seem to consider "tags" as a part of "post" on your api level, i.e. you consider them "one thing" together. But you still want to normalize correctly on the data level.

In cases like this, I do the following:

• My data schema is normalized like yours. Posts and tags are separate tables.
• A view for posts on the api level already aggregates the list of tags into an array. I don't use resource embedding for this purpose, but only for things that I consider related, but not "part of the same entity". That means, everything that I want to update "at once", is joined on the api level, and everything where I'd like to make separate requests to update related rows, is using resource embedding.
• Some INSTEAD OF triggers on that view take care of all the upserting logic you have there... and they RETURN the new row for that view, which already includes all the aggregations mentioned above. Those always reflect the latest state, no empty arrays or old tags.

To make this performant, you need to take care to design the view in a way that parts of the query are not executed, when you don't request the related columns. So for example when you select from the view without the tags column, you don't want the tags join to happen at all. This is best achieved by putting all those joins into a LATERAL join. Those are then optimized away nicely.

This also gives you the nice benefit of being able to just use plain POST / PUT / PATCH on this view for inserts, updates etc. - no need to write your own custom RPC, which is kind of unrelated to the actual endpoint. Everything will happen on /posts.

[jdgamble555]

I should add, I'm not a big fan of VIEWs, because every time I update the littlest thing, I have to redeploy it instead of it updating automatically.

[wolfgangwalther]

I should add, I'm not a big fan of VIEWs, because every time I update the littlest thing, I have to redeploy it instead of it updating automatically.

For me, it's the opposite. I don't expose a single table directly. I have two schemas, data (all the tables) and api (the exposed schema). When it's time to update I do:

• DROP the whole schema api.
• Migrate stuff in data via ALTER ....
• Recreate the whole api schema.

All in one transaction. That's seamless and there is no errors at all for those migration commands.

[wolfgangwalther]

I'm not sure I understand. I do see a VIEW helping, but I would still need a second call.

Here's a redacted example straight from my code-base, which should give you the right idea:

(I replaced the actual names posts and tags)

CREATE VIEW api.posts (post, tags)
WITH (security_invoker) AS
SELECT post,
       title,
       COALESCE(tags, '{}')
  FROM data.posts AS p
  LEFT JOIN LATERAL (
              SELECT array_agg(tag ORDER BY tag ASC) AS tags
                FROM data.tags
               WHERE post = p.post
            ) AS _tags
         ON TRUE;

CREATE FUNCTION insert_post() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
DECLARE
  new_id data.posts.post%TYPE;
BEGIN
  INSERT INTO data.posts (title) VALUES (NEW.title)
  RETURNING post INTO new_id;

  IF NEW.tags IS NOT NULL THEN
    INSERT INTO data.tags (post, tag)
         SELECT new_id, unnest(NEW.tags);
  END IF;

  RETURN posts
    FROM api.posts
   WHERE post = new_id;
END$$;

CREATE TRIGGER ins INSTEAD OF INSERT ON api.posts
FOR EACH ROW
EXECUTE PROCEDURE insert_post();

CREATE FUNCTION update_post() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN
  MERGE INTO data.tags AS target
  USING (SELECT OLD.post AS post,
                tag,
                new_tag
           FROM unnest(NEW.tags) AS new_tag
           FULL JOIN (SELECT * FROM data.tags WHERE post = OLD.post) AS _
                  ON tag = new_tag
        ) AS source
     ON target.post = source.post
        AND target.tag = source.tag
   WHEN MATCHED AND new_tag IS NULL THEN
        DELETE
   WHEN NOT MATCHED THEN
        INSERT
        (       post,            tag) VALUES
        (source.post, source.new_tag);

  UPDATE data.posts
     SET title = NEW.title
   WHERE post = OLD.post;

  RETURN posts
    FROM api.posts
   WHERE post = OLD.post;
END$$;

CREATE TRIGGER upd INSTEAD OF UPDATE ON api.posts
FOR EACH ROW
EXECUTE PROCEDURE update_post();

The MERGE could probably be written a bit simpler, now that I removed a ton of stuff and simplified things for this example. You could also just use your DELETE + INSERT logic or whatever. But this should give you the idea.

This will then just allow you to do:

PATCH /posts?post=eq.1

{
  "title": "new title",
  "tags": ["a", "b"]
}

If you really wanted to get UPSERT semantics, then you could just put all of that in the INSERT trigger and do either, e.g. with the logic you already have or so.

[jdgamble555]

Interesting... how would you call this in Supabase?

[wolfgangwalther]

I have never used Supabase, so I can't tell.