plpgsql: DETAIL: stmt_dyn_exec is not yet supported

[paigeadelethompson]

CREATE OR REPLACE FUNCTION diesel_manage_updated_at (_tbl regclass)
    RETURNS VOID
    AS $$
BEGIN
    EXECUTE format('CREATE TRIGGER set_updated_at BEFORE UPDATE ON %s
                    FOR EACH ROW EXECUTE PROCEDURE diesel_set_updated_at()', _tbl);
END;
$$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION diesel_set_updated_at ()
    RETURNS TRIGGER
    AS $$
BEGIN
    IF (NEW IS DISTINCT FROM OLD AND NEW.updated_at IS NOT DISTINCT FROM OLD.updated_at) THEN
        NEW.updated_at := CURRENT_TIMESTAMP;
    END IF;
    RETURN NEW;
END;
$$
LANGUAGE plpgsql;

ERROR: unimplemented: attempted to use a PL/pgSQL statement that is not yet supported
SQLSTATE: 0A000
DETAIL: stmt_dyn_exec is not yet supported
HINT: You have attempted to use a feature that is not yet implemented.

Please check the public issue tracker to check whether this problem is
already tracked. If you cannot find it there, please report the error
with details by creating a new issue.

If you would rather not post publicly, please contact us directly
using the support form.

We appreciate your feedback.
CREATE FUNCTION

Time: 741ms total (execution 740ms / network 1ms)

Jira issue: CRDB-45652

[blathers-crl]

Hi @paigeadelethompson, please add branch-* labels to identify which branch(es) this C-bug affects.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

It looks like you have not filled out the issue in the format of any of our templates. To best assist you, we advise you to use one of these templates.

I have CC'd a few people who may be able to assist you:

• @cockroachdb/sql-foundations (found keywords: SQL statement)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.}

[paigeadelethompson]

Hi @paigeadelethompson, please add branch-* labels to identify which branch(es) this C-bug affects.

🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is dev-inf.

Build Tag:        v24.3.0
Build Time:       2024/11/21 17:04:13
Distribution:     CCL
Platform:         linux amd64 (x86_64-pc-linux-gnu)
Go Version:       go1.22.8 X:nocoverageredesign
C Compiler:       gcc 6.5.0
Build Commit ID:  ef2ebe96f3a26fd6d6716e2ec4a0c8c6fe0fb567
Build Type:       release
Enabled Assertions: false

[paigeadelethompson]

also found the regression test for the stmt_dyn_exec but I'm not really that much of an expert in sprocs

cockroach/pkg/ccl/logictestccl/testdata/logic_test/plpgsql_unsupported

Line 27 in effc6b2

statement error pgcode 0A000 DETAIL: stmt_dyn_exec is not yet supported

this is just the first of 242 migrations that need to be applied for this application to work correctly. I don't even know sprocs that well to say whether or not this is something I can workaround (I wanna try but I'm also looking at this list of 241 migrations that need to be applied and thinking my god what am I getting myself into? Moreover I'm not sure how I arrived at the conclusion that sprocs would be limited in scope (My only frame of reference is MSSQL and admittedly it's been a really long time but the only time I had problems like this was going into a migration from one version to another. Is that the point I'm missing here? I know there are different versions of pgsql so I'm wondering if there's a compatibility level for which I should assume cockroach should work fine for or is it always just hit or miss?

Thanks
-P

[paigeadelethompson]

Stale issue, but apparently a more broad effort to address the problem (mine also originates from migrations created with diesel) #13787

[paigeadelethompson]

Just looking through this regression test a little bit more it looks like I would also be hitting a wall with RECORD types:

CREATE OR REPLACE FUNCTION drop_ccnew_indexes ()
    RETURNS integer
    AS $$
DECLARE
    i RECORD;
BEGIN
    FOR i IN (
        SELECT
            relname
        FROM
            pg_class
        WHERE
            relname LIKE '%ccnew%')
        LOOP
            EXECUTE 'DROP INDEX ' || i.relname;
        END LOOP;
    RETURN 1;
END;
$$
LANGUAGE plpgsql;

and possibly several set returning sprocs:

2024-11-12-090437_move-triggers/down.sql:    RETURNS TABLE (
2023-12-06-180359_edit_active_users/up.sql:    RETURNS TABLE (
2023-12-06-180359_edit_active_users/down.sql:    RETURNS TABLE (
2021-01-27-202728_active_users_monthly/up.sql:    RETURNS TABLE (
2021-08-16-004209_fix_remove_bots_from_aggregates/up.sql:    RETURNS TABLE (
2021-08-16-004209_fix_remove_bots_from_aggregates/down.sql:    RETURNS TABLE (

I don't know whether or not RETURNS TABLE necessarily implies that a SET type will be returned ...

EDIT: again no big deal I'm not complaining also I see that regression test was recently (in the past 4 days modified) so presumably the plan is to support this and it would just be nice to know for certain that the intention here is to support this so I can set my expectations accordingly for what I can do with CRDB going forward

[yuzefovich]

Thanks for opening up the issue! Indeed, you ran into a known limitation that we don't yet support EXECUTE commands in PLpgSQL - this work is tracked by #115300. More generally, #117744 is the main tracking issue to see which PLpgSQL features are unimplemented still.

We did implement RETURNS TABLE syntax very recently (last week), so that feature should be available in 25.1 release coming out early next year.

[paigeadelethompson]

Oh cool thank you for clearing that up some, I started looking at the code and found this: 7505583#diff-f8265107fda3aef59cddcab4cdf6b229312224027fe3db5264879cd2659afbbbR164

which looks like it might already be in place (I see a tag that says this is already in 24.3.1, and it looks like I am running build: CCL v24.3.0 @ 2024/11/21 17:04:13 (go1.22.8 X:nocoverageredesign) which is surprising because I'm running the Docker container cockroachdb/cockroach latest 296e6e763a75

I'll keep an eye on this maybe this will work with 24.3.1 if that's an option (I'll have to see what I can get from https://hub.docker.com/r/cockroachdb/cockroach/tags?name=latest and it looks like these were updated in the past two days so I'll have to check later.)

[yuzefovich]

The commit that you linked only added the parser support, and it merged last year, so it's been included in versions 23.2+ or so. v24.3.1 won't have any new features supported I think - we generally only include new features in major releases (so either 24.3.0 or 25.1.0, but not in the minor release like 24.3.1.

I'm going to close this issue as a dup of #117744 - please follow that issue for updates about PLpgSQL functionality that we add full support for.