Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

sql: Add support for TEMP tables #5807

Closed
dt opened this issue Apr 1, 2016 · 12 comments
Closed

sql: Add support for TEMP tables #5807

dt opened this issue Apr 1, 2016 · 12 comments
Assignees
@solongordon
Labels
A-sql-pgcompat Semantic compatibility with PostgreSQL A-sql-semantics A-tools-hibernate Issues that pertain to Hibernate integration. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) X-anchored-telemetry The issue number is anchored by telemetry references.

Comments

@dt
Copy link
Member

dt commented Apr 1, 2016

Passing the modifier TEMP or TEMPORARY to CREATE TABLE would indicate the the table should be dropped when the session ends.

Like other DBs, using the unqualified table name for the remainder of the session would reference the temporary table -- an existing, non-temporary table of the same name would need to be accessed using a qualified name.

Postgres docs, for reference:

TEMPORARY or TEMP
If specified, the table is created as a temporary table. Temporary tables are automatically dropped at the end of a session, or optionally at the end of the current transaction (see ON COMMIT below). Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names. Any indexes created on a temporary table are automatically temporary as well.

...

Optionally, GLOBAL or LOCAL can be written before TEMPORARY or TEMP. This presently makes no difference in PostgreSQL and is deprecated; see Compatibility.

http://www.postgresql.org/docs/current/static/sql-createtable.html
@dt dt added C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) E-easy Easy issue to tackle, requires little or no CockroachDB experience labels Apr 1, 2016
@dt
Copy link
Member Author

dt commented Apr 1, 2016

From discussion in gitter with @bdarnell and @petermattis:

Where to actually store a temp table?

Small temp tables might make sense to keep in-memory on the gateway (in the session), skipping replication/raft overhead/rocks.
Larger tables, or larger numbers of clients using temp tables against a single gateway, though will require storing on them !gateway. To remain resilient to non-gateway node death, those will still need to be replicated normally.

@petermattis
Copy link
Collaborator

Storing the temp table in-memory on the gateway will add a wrinkle when we distribute SQL processing. The planner will have to know about the temporary table and arrange for JOINs against it to be done on the gateway node. Not insurmountable, but yet another consideration.

@dt
Copy link
Member Author

dt commented Apr 1, 2016

Yeah, my inclination is to start with the storing temp tables in regular, globally replicated tables in a special system DB, and just intercept unqualified name resolution (plus add a heartbeat from the session to something that keeps them from being GCed), then consider added the special-case of gateway-local, in-memory tables later.

That said, if we wanted to leave room for the more performant, gateway-local version to be the default at a later time, we'd might want to artificially limit the size unless eg the GLOBAL modifier is used to prevent anyone relying on it in the mean time.

@dt dt added the help wanted Help is requested / needed by the one who filed the issue to fix it. label May 2, 2016
@dt dt mentioned this issue May 2, 2016
Closed
@ghost
Copy link

ghost commented May 12, 2016

I'm learning Go and I would like to do so by helping with this project. Can I try to work on this issue?

@vivekmenezes
Copy link
Contributor

@alexlovescoding please do send us a PR for this issue. If you need any help you can ping this issue or hit us on gitter https://gitter.im/cockroachdb/cockroach

@tbg
Copy link
Member

tbg commented Jul 7, 2016

I'm untagging this as easy because I don't think it qualifies.

@tbg tbg removed the E-easy Easy issue to tackle, requires little or no CockroachDB experience label Jul 7, 2016
@knz knz mentioned this issue Aug 30, 2016
Merged
@knz knz mentioned this issue Nov 29, 2016
Closed
@petermattis petermattis added this to the Later milestone Feb 22, 2017
@knz knz mentioned this issue May 8, 2017
Merged
@justinj justinj mentioned this issue Jun 13, 2017
Closed
18 tasks
@pkit pkit mentioned this issue Sep 16, 2017
Closed
@BramGruneir BramGruneir added the A-tools-hibernate Issues that pertain to Hibernate integration. label Jun 14, 2018
@BramGruneir BramGruneir mentioned this issue Jun 14, 2018
Closed
14 tasks
@petermattis petermattis removed this from the Later milestone Oct 5, 2018
@knz knz mentioned this issue Oct 23, 2018
Closed
@knz knz added the X-anchored-telemetry The issue number is anchored by telemetry references. label Nov 22, 2018
@JorritSalverda JorritSalverda mentioned this issue Mar 18, 2019
Closed
@BramGruneir BramGruneir mentioned this issue Mar 27, 2019
Closed
@jordanlewis
Copy link
Member

I've talked to a number of people about this feature recently. I think we should aim to implement temporary tables as ordinary tables that are automatically dropped at the end of the session, without thinking about any in-memory optimizations or anything like that.

I think for now we should ignore the ON COMMIT modifier, which produces transaction-scoped temporary tables.

There are a number of requirements.

  1. Only the current session may observe the temporary table.
  2. The temporary table must live in a namespace that is isolated from other sessions - other sessions must be able to create a temporary table of the same name as a temporary table in another session.
  3. The temporary tables must be dropped at the end of a session.
  4. Temporary tables may not reference permanent tables via foreign key relations. Likewise, permanent tables may not reference temporary tables via foreign key relations.
  5. DistSQL flows on remote nodes must be able to observe the temporary tables for the session that initiated them.

I think this is very doable.

cc @rafiss as a potential project for you.

@petermattis
Copy link
Collaborator

  • Can the schema of a temporary table be altered? Can the zone config?
  • Are temporary tables completely inaccessible from other sessions? This might follow from your point 2, but I can imagine a design which doesn't prohibit such access. For example, each session gets its own unique "schema" which only it can write to, but everyone can read from.
  • Will temporary tables increase table ID / namespace churn and the size of the system config range? I think the answer is yes, and fixing this is the biggest obstacle I see.

@jordanlewis
Copy link
Member

Can the schema of a temporary table be altered? Can the zone config?

I would imagine yes on both points, but just by accident. In this proposed design, temporary tables would simply be ordinary tables. I don't think it's important that we think about this too carefully.

Are temporary tables completely inaccessible from other sessions? This might follow from your point 2, but I can imagine a design which doesn't prohibit such access. For example, each session gets its own unique "schema" which only it can write to, but everyone can read from.

Yes, we would make them completely inaccessible by design, for compatibility (and to decrease complexity).

Will temporary tables increase table ID / namespace churn and the size of the system config range? I think the answer is yes, and fixing this is the biggest obstacle I see.

Yeah, I don't know how to deal with this. How bad is it to use lots of table ids? That doesn't sound too bad. But writing too much to system config range sounds bad. Unfortunately, I have no idea how to fix that. Isn't it a bigger problem that all of this stuff is relegated to one range?

@jordanlewis jordanlewis removed the help wanted Help is requested / needed by the one who filed the issue to fix it. label May 30, 2019
@leafji
Copy link

leafji commented Jul 29, 2019

Hello @jordanlewis :

Is there any plan of this temp table feature now? I would like to use this feature in our application project, if have any short-term planning， plz Let me know that.

thanks
leafji

@jordanlewis jordanlewis added the A-sql-pgcompat Semantic compatibility with PostgreSQL label Sep 3, 2019
jordanlewis added a commit to jordanlewis/cockroach that referenced this issue Oct 2, 2019
@jordanlewis
roachtest: add test that aggregates orm blacklist failures
cebc811 
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
jordanlewis added a commit to jordanlewis/cockroach that referenced this issue Oct 24, 2019
@jordanlewis
roachtest: add test that aggregates orm blacklist failures
4010eac 
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
craig bot pushed a commit that referenced this issue Nov 7, 2019
@jordanlewis
Merge #41252
27fd417 
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>
@jseldess jseldess mentioned this issue Dec 13, 2019
Closed
@solongordon solongordon assigned solongordon and unassigned arulajmani Jan 7, 2020
@awoods187
Copy link
Contributor

@otan do you think we can close this now?

@otan
Copy link
Contributor

otan commented Mar 17, 2020 •
edited
Loading

Yes. We support TEMP tables, views and sequences in the upcoming release.

@otan otan closed this as completed Mar 17, 2020
@rafiss rafiss mentioned this issue Apr 23, 2020
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@solongordon solongordon

Labels
A-sql-pgcompat Semantic compatibility with PostgreSQL A-sql-semantics A-tools-hibernate Issues that pertain to Hibernate integration. C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) X-anchored-telemetry The issue number is anchored by telemetry references.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests