sql: support stored procedures #17511

knz · 2017-08-08T16:44:30Z

CockroachDB needs stored procedures in the long term.
This is a placeholder issue to group action items:

support name scopes properly (work: planning)
support the Apply relational operator to be used by corr. subqueries, user-defined functions and proceudres (work: planning, execution)
support CTEs (work: planning)
support query compilation (work: planning, execution)
advance the deployment of auto-generated IR code in CockroachDB (work: language, planning)
research an encoding in KV for stored procedure descriptors, define versioning semantics and whether they are editable
research and design an AST for stored procedures. Ancillary questions: common AST for multiple languages? API if languages are provided externally (Lua, others)? Compile this AST. (work: language, planning)

gz#6812

gz#7833

knz · 2017-08-08T16:45:03Z

user @camuel has written a PDF about why it makes sense from a PM perspective: https://u23433218.dl.dropboxusercontent.com/u/23433218/PSM4CRDB%20EARLY%20DAFT%20BY%20CAMUEL%20GILYADOV.pdf

camuel · 2017-08-08T21:01:44Z

Thanks @knz, I have updated the document and here is the second version: https://tinyurl.com/ybq4tvqw

I'm impatiently waiting for a feedback and further guidance and I'm interested in implementing this feature. I do have a few questions:

Is there any big decisions which are already made? Such as commitment to support SQL/PSM or perhaps a firm decision not to. This will immediately narrow the scope of work for me.
Regarding 'support query compilation' mentioned above, what is meant here? Compiling the query to vectorized native code with perhaps LLVM for performance considerations? Impala did this for example. Or something entirely different?
How could common AST exist for multiple languages? Well some languages are similar but some are pretty different. Doing a common AST for Lua, SQL/PSM and Python is not feasible, does it? Will appreciate some elaboration.
It seems a lot of work, how we will go about it? Depth first or breadth first. I mean one way is to implement minimal UDF right now and then UDAF and a bit more constructs of PSM language, then SP, then triggers and etc.. Breadth first is to initially research the whole topic, write a design, will be a small book easily, do some experiments here and there, and then carry out the implementation. Given multiple existing SQL/PSM implementations in the open, almost zero requirement risk, perhaps waterfall approach is not too crazy. Also, is this the only RFC or some other RFCs would be created for a smaller tasks?

Sorry for nooby questions and thanks again

knz · 2017-08-08T21:16:44Z

Hold your horses! We are welcoming input but it is just too soon. If you really want to help with this, please wait at least until January 2018.

If you and your team would like to help improve CockroachDB in the mean time, especially in areas that will enable later work on stored procedures, please let us know. In particular it would be great if you could get in touch with Nate Stewart nate@cockroachlabs.com to discuss your overall strategy, how and why you plan to contribute to CockroachDB, and see how well your plans/intents/interests match our roadmap. (Also we can take input from you!)

tbg · 2017-12-19T20:32:00Z

If we introduce stored procedures, we'll also have to change FmtAnonymize to strip at least user-defined function names.

awoods187 · 2018-04-12T19:22:05Z

In Navicat

FROM information_schema.routines AS r 
LEFT JOIN information_schema.parameters AS p ON r.specific_name = p.specific_name GROUP BY p.specific_name, r.routine_schema, r.routine_name 
ORDER BY r.routine_schema```

I180409 21:52:10.217696 380 sql/conn_executor.go:1805  [n1,client=[::1]:56172,user=root] execution error: relation "information_schema.routines" does not exist

The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep up to date. If we write down blacklists in code, then we can use an approach like this to always have an up to date aggregation. So far it seems like there's just a lot of unknowns to categorize still. The output today: ``` === RUN TestBlacklists 648: unknown (unknown) 493: cockroachdb#5807 (sql: Add support for TEMP tables) 151: cockroachdb#17511 (sql: support stored procedures) 86: cockroachdb#26097 (sql: make TIMETZ more pg-compatible) 56: cockroachdb#10735 (sql: support SQL savepoints) 55: cockroachdb#32552 (multi-dim arrays) 55: cockroachdb#26508 (sql: restricted DDL / DML inside transactions) 52: cockroachdb#32565 (sql: support optional TIME precision) 39: cockroachdb#243 (roadmap: Blob storage) 33: cockroachdb#26725 (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea)) 31: cockroachdb#27793 (sql: support custom/user-defined base scalar (primitive) types) 24: cockroachdb#12123 (sql: Can't drop and replace a table within a transaction) 24: cockroachdb#26443 (sql: support user-defined schemas between database and table) 20: cockroachdb#21286 (sql: Add support for geometric types) 18: cockroachdb#6583 (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait})) 17: cockroachdb#22329 (Support XA distributed transactions in CockroachDB) 16: cockroachdb#24062 (sql: 32 bit SERIAL type) 16: cockroachdb#30352 (roadmap:when CockroachDB will support cursor?) 12: cockroachdb#27791 (sql: support RANGE types) 8: cockroachdb#40195 (pgwire: multiple active result sets (portals) not supported) 8: cockroachdb#6130 (sql: add support for key watches with notifications of changes) 5: Expected Failure (unknown) 5: cockroachdb#23468 (sql: support sql arrays of JSONB) 5: cockroachdb#40854 (sql: set application_name from connection string) 4: cockroachdb#35879 (sql: `default_transaction_read_only` should also accept 'on' and 'off') 4: cockroachdb#32610 (sql: can't insert self reference) 4: cockroachdb#40205 (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE) 4: cockroachdb#35897 (sql: unknown function: pg_terminate_backend()) 4: cockroachdb#4035 (sql/pgwire: missing support for row count limits in pgwire) 3: cockroachdb#27796 (sql: support user-defined DOMAIN types) 3: cockroachdb#3781 (sql: Add Data Type Formatting Functions) 3: cockroachdb#40476 (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`) 3: cockroachdb#35882 (sql: support other character sets) 2: cockroachdb#10028 (sql: Support view queries with star expansions) 2: cockroachdb#35807 (sql: INTERVAL output doesn't match PG) 2: cockroachdb#35902 (sql: large object support) 2: cockroachdb#40474 (sql: support `SELECT ... FOR UPDATE OF` syntax) 1: cockroachdb#18846 (sql: Support CIDR column type) 1: cockroachdb#9682 (sql: implement computed indexes) 1: cockroachdb#31632 (sql: FK options (deferrable, etc)) 1: cockroachdb#24897 (sql: CREATE OR REPLACE VIEW) 1: pass? (unknown) 1: cockroachdb#36215 (sql: enable setting standard_conforming_strings to off) 1: cockroachdb#32562 (sql: support SET LOCAL and txn-scoped session variable changes) 1: cockroachdb#36116 (sql: psychopg: investigate how `'infinity'::timestamp` is presented) 1: cockroachdb#26732 (sql: support the binary operator: <int> / <float>) 1: cockroachdb#23299 (sql: support coercing string literals to arrays) 1: cockroachdb#36115 (sql: psychopg: investigate if datetimetz is being returned instead of datetime) 1: cockroachdb#26925 (sql: make the CockroachDB integer types more compatible with postgres) 1: cockroachdb#21085 (sql: WITH RECURSIVE (recursive common table expressions)) 1: cockroachdb#36179 (sql: implicity convert date to timestamp) 1: cockroachdb#36118 (sql: Cannot parse '24:00' as type time) 1: cockroachdb#31708 (sql: support current_time) ``` Release justification: non-production change Release note: None

41252: roachtest: add test that aggregates orm blacklist failures r=jordanlewis a=jordanlewis The spreadsheet we discussed is unwieldy - hard to edit and impossible to keep up to date. If we write down blacklists in code, then we can use an approach like this to always have an up to date aggregation. So far it seems like there's just a lot of unknowns to categorize still. The output today: ``` === RUN TestBlacklists 648: unknown (unknown) 493: #5807 (sql: Add support for TEMP tables) 151: #17511 (sql: support stored procedures) 86: #26097 (sql: make TIMETZ more pg-compatible) 56: #10735 (sql: support SQL savepoints) 55: #32552 (multi-dim arrays) 55: #26508 (sql: restricted DDL / DML inside transactions) 52: #32565 (sql: support optional TIME precision) 39: #243 (roadmap: Blob storage) 33: #26725 (sql: support postgres' API to handle blob storage (incl lo_creat, lo_from_bytea)) 31: #27793 (sql: support custom/user-defined base scalar (primitive) types) 24: #12123 (sql: Can't drop and replace a table within a transaction) 24: #26443 (sql: support user-defined schemas between database and table) 20: #21286 (sql: Add support for geometric types) 18: #6583 (sql: explicit lock syntax (SELECT FOR {SHARE,UPDATE} {skip locked,nowait})) 17: #22329 (Support XA distributed transactions in CockroachDB) 16: #24062 (sql: 32 bit SERIAL type) 16: #30352 (roadmap:when CockroachDB will support cursor?) 12: #27791 (sql: support RANGE types) 8: #40195 (pgwire: multiple active result sets (portals) not supported) 8: #6130 (sql: add support for key watches with notifications of changes) 5: Expected Failure (unknown) 5: #23468 (sql: support sql arrays of JSONB) 5: #40854 (sql: set application_name from connection string) 4: #35879 (sql: `default_transaction_read_only` should also accept 'on' and 'off') 4: #32610 (sql: can't insert self reference) 4: #40205 (sql: add non-trivial implementations of FOR UPDATE, FOR NO KEY UPDATE, FOR SHARE, FOR NO KEY SHARE) 4: #35897 (sql: unknown function: pg_terminate_backend()) 4: #4035 (sql/pgwire: missing support for row count limits in pgwire) 3: #27796 (sql: support user-defined DOMAIN types) 3: #3781 (sql: Add Data Type Formatting Functions) 3: #40476 (sql: support `FOR {UPDATE,SHARE} {SKIP LOCKED,NOWAIT}`) 3: #35882 (sql: support other character sets) 2: #10028 (sql: Support view queries with star expansions) 2: #35807 (sql: INTERVAL output doesn't match PG) 2: #35902 (sql: large object support) 2: #40474 (sql: support `SELECT ... FOR UPDATE OF` syntax) 1: #18846 (sql: Support CIDR column type) 1: #9682 (sql: implement computed indexes) 1: #31632 (sql: FK options (deferrable, etc)) 1: #24897 (sql: CREATE OR REPLACE VIEW) 1: pass? (unknown) 1: #36215 (sql: enable setting standard_conforming_strings to off) 1: #32562 (sql: support SET LOCAL and txn-scoped session variable changes) 1: #36116 (sql: psychopg: investigate how `'infinity'::timestamp` is presented) 1: #26732 (sql: support the binary operator: <int> / <float>) 1: #23299 (sql: support coercing string literals to arrays) 1: #36115 (sql: psychopg: investigate if datetimetz is being returned instead of datetime) 1: #26925 (sql: make the CockroachDB integer types more compatible with postgres) 1: #21085 (sql: WITH RECURSIVE (recursive common table expressions)) 1: #36179 (sql: implicity convert date to timestamp) 1: #36118 (sql: Cannot parse '24:00' as type time) 1: #31708 (sql: support current_time) ``` Release justification: non-production change Release note: None Co-authored-by: Jordan Lewis <jordanthelewis@gmail.com>

fire · 2019-11-08T19:02:52Z

Is there a status update on this?

knz · 2019-11-08T19:08:20Z

Yes: "not yet".

tracker1 · 2021-10-28T02:24:12Z

On WASM... just a place to get started, maybe...

msdrigg · 2022-03-01T01:13:30Z

Hey team, it is one year since the last status update and I am here to see if there is any updates in the previously mentioned status? I am interested because I was linked here from this other issue: #28296

knz · 2022-03-01T09:33:06Z

This is currently queued, thanks for your interest!

We're considering looking at user-defined functions before stored procedures, as they are more commonly used and less complex to support.

kevinelliott · 2022-06-23T21:17:45Z

This is the one feature holding me back from migrating from postgres to cockroach.

rustrial · 2022-06-26T09:44:28Z

@knz user-defined functions (UDF) as kind of a MVP for stored-procedures sound like a solid plan. Especially, if such user-defined functions could also be used in check constraint and default expressions.

Is there any roadmap information for UDFs?

Example Use Case for UDFs

UDFs would allow us for example to enforce things like data domiciling (although that does not necessarily need UDF support, as it could also be achieved by supporting SQL select statements in check constraint and default expressions).

create table car_table
(
  -- Set data domiciling
  crdb_region  crdb_internal_region not null default lookup_region_of_tenant(tenant_id),
  tenant_id    uuid not null,
  ...
  constraint car_tfk foreign key (tenant_id) references tenant_table(id),
  -- Enforce data domiciling, 
  -- making sure the row is always stored in the region (country) associated with the tenant.
  constraint enforce_data_domiciling check (crdb_region = lookup_region_of_tenant(tenant_id)),
) regional by row as crdb_region;

wadeschulz · 2022-07-05T23:41:29Z

Also interested in timeline updates that would allow support for .NET/EF migrations (npgsql/efcore.pg#2270)

jordanlewis · 2022-07-07T03:06:30Z

We're currently making progress on user-defined functions. You can follow progress in this issue: #58356

Ranguna · 2022-09-02T08:34:45Z

@jordanlewis just FYI seem @mgartner closed that issue in favour of all the other open ones.

This PR removes some tests from the blocklist which have started passing now thanks to the recent work on UDFs and stored procedures (see cockroachdb#17511) Epic: none Fixes: cockroachdb#105915 Release note: None

105922: roachtest: Fix asyncpg test r=rafiss a=rimadeodhar This PR removes test_enum_function_return from the blocklist. This test has started passing now thanks to the work on stored procedues and UDFs (See #17511). Epic: None Fixes: #105692 Release note: None 105935: kvserver: skip `TestReliableIntentCleanup` r=erikgrinaker a=erikgrinaker Still flaky. Epic: none Release note: None Co-authored-by: rimadeodhar <rima@cockroachlabs.com> Co-authored-by: Erik Grinaker <grinaker@cockroachlabs.com>

105936: roachtest: Fix pgjdbc test r=rafiss a=rimadeodhar This PR removes some tests from the blocklist which have started passing now thanks to the recent work on UDFs and stored procedures (see #17511) Epic: none Fixes: #105915 Release note: None Co-authored-by: rimadeodhar <rima@cockroachlabs.com>

With recent work on UDFs(cockroachdb#17511) and cockroachdb#41872, some of the tests from pjdbc associated with these issues have started passing. This PR updates the blocklist to remove the newly passing tests. Epic: None Fixes: cockroachdb#111130 Release note: None

111464: pgjdbc: remove passing tests from blocklist r=rimadeodhar a=rimadeodhar With recent work on UDFs(#17511) and #41872, some of the tests from pjdbc associated with these issues have started passing. This PR updates the blocklist to remove the newly passing tests. Epic: None Fixes: #111130 Release note: None Co-authored-by: rimadeodhar <rima@cockroachlabs.com>

With recent work on UDFs(cockroachdb#17511) and cockroachdb#41872, some of the tests from pjdbc associated with these issues have started passing. This PR updates the blocklist to remove the newly passing tests. Epic: None Fixes: cockroachdb#111130 Release note: None

gerwim · 2024-02-16T14:30:59Z

@jordanlewis Is there an update? Since the UDF issue has been merged. I'm here because of a different but related issue (npgsql/efcore.pg#2270).

Another issue (same cause): hangfire-postgres/Hangfire.PostgreSql#152

mgartner · 2024-02-22T21:15:26Z

Yes. Support for SQL and PL/pgSQL stored procedures was added in v23.2. There may be some gaps in support compared to Postgres, but the core functionality should work. I'll close this issue.

knz added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Aug 8, 2017

knz added this to the Later milestone Aug 8, 2017

knz self-assigned this Aug 8, 2017

knz mentioned this issue Jan 30, 2018

Ability to Prepare multi-statement #20699

Closed

knz mentioned this issue Apr 12, 2018

sql: support vtables information_schema.{routines,parameters} #24759

Closed

knz added the A-sql-semantics label May 9, 2018

awoods187 mentioned this issue Aug 6, 2018

SQL: Add support for stored procedures as an unimplemented feature in SQL.Y #28295

Closed

tracker1 mentioned this issue Sep 6, 2018

sql: implement support for plv8 (javascript stored procedures) #29683

Open

petermattis removed this from the Later milestone Oct 5, 2018

knz mentioned this issue Oct 23, 2018

sql: review all known hibernate failures #30975

Closed

knz added the X-anchored-telemetry The issue number is anchored by telemetry references. label Nov 22, 2018

BramGruneir mentioned this issue Jan 2, 2019

sql: Ecto compatibility #33441

Open

18 tasks

knz removed their assignment Jan 3, 2019

lukaseder mentioned this issue Nov 8, 2019

Add support for CockroachDB jOOQ/jOOQ#8545

Closed

blathers-crl bot added the T-sql-queries SQL Queries Team label Sep 17, 2021

knz added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label Sep 17, 2021

rafiss mentioned this issue Feb 14, 2022

When using a custom schema in CockroachDb error - "At or near "do": syntax error" npgsql/efcore.pg#2270

Open

jordanlewis mentioned this issue Apr 21, 2022

User-defined built-in functions #77555

Closed

otan mentioned this issue May 11, 2022

GraphQL: Add support for pg_graphql #81188

Open

rimadeodhar mentioned this issue Jun 30, 2023

roachtest: Fix asyncpg test #105922

Merged

rimadeodhar mentioned this issue Jun 30, 2023

roachtest: Fix pgjdbc test #105936

Merged

rimadeodhar mentioned this issue Sep 28, 2023

pgjdbc: remove passing tests from blocklist #111464

Merged

mgartner closed this as completed Feb 22, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

sql: support stored procedures #17511

sql: support stored procedures #17511

knz commented Aug 8, 2017 •

edited

Loading

knz commented Aug 8, 2017 •

edited

Loading

camuel commented Aug 8, 2017

knz commented Aug 8, 2017

tbg commented Dec 19, 2017

awoods187 commented Apr 12, 2018

fire commented Nov 8, 2019

knz commented Nov 8, 2019

tracker1 commented Oct 28, 2021 •

edited

Loading

msdrigg commented Mar 1, 2022 •

edited

Loading

knz commented Mar 1, 2022

kevinelliott commented Jun 23, 2022

rustrial commented Jun 26, 2022

wadeschulz commented Jul 5, 2022

jordanlewis commented Jul 7, 2022

Ranguna commented Sep 2, 2022

gerwim commented Feb 16, 2024 •

edited

Loading

mgartner commented Feb 22, 2024 •

edited

Loading

sql: support stored procedures #17511

sql: support stored procedures #17511

Comments

knz commented Aug 8, 2017 • edited Loading

knz commented Aug 8, 2017 • edited Loading

camuel commented Aug 8, 2017

knz commented Aug 8, 2017

tbg commented Dec 19, 2017

awoods187 commented Apr 12, 2018

fire commented Nov 8, 2019

knz commented Nov 8, 2019

tracker1 commented Oct 28, 2021 • edited Loading

msdrigg commented Mar 1, 2022 • edited Loading

knz commented Mar 1, 2022

kevinelliott commented Jun 23, 2022

rustrial commented Jun 26, 2022

Example Use Case for UDFs

wadeschulz commented Jul 5, 2022

jordanlewis commented Jul 7, 2022

Ranguna commented Sep 2, 2022

gerwim commented Feb 16, 2024 • edited Loading

mgartner commented Feb 22, 2024 • edited Loading

knz commented Aug 8, 2017 •

edited

Loading

knz commented Aug 8, 2017 •

edited

Loading

tracker1 commented Oct 28, 2021 •

edited

Loading

msdrigg commented Mar 1, 2022 •

edited

Loading

gerwim commented Feb 16, 2024 •

edited

Loading

mgartner commented Feb 22, 2024 •

edited

Loading