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: CREATE OR REPLACE VIEW #24897

Closed
godofdream opened this issue Apr 18, 2018 · 15 comments · Fixed by #47051
Closed

sql: CREATE OR REPLACE VIEW #24897

godofdream opened this issue Apr 18, 2018 · 15 comments · Fixed by #47051
Labels
A-sql-pgcompat Semantic compatibility with PostgreSQL C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) O-community Originated from the community X-anchored-telemetry The issue number is anchored by telemetry references.

Comments

@godofdream
Copy link

FEATURE REQUEST
Postgresql supports CREATE OR REPLACE VIEW
(https://www.postgresql.org/docs/10/static/sql-createview.html)
Postgres limitates the replace view function to only add new columns.

CockroachDB could support this sqlstatement too, as it's very convenient way to replace views.
A Workaround would be dropping and creating a view in a transaction. (currently blocked by #12123)

Importance: Nice-to-have postgresql compatibility
@danhhz
Copy link
Contributor

danhhz commented Apr 18, 2018

Assigning to @knz for triage

@knz knz added the A-sql-pgcompat Semantic compatibility with PostgreSQL label Apr 18, 2018
@knz
Copy link
Contributor

knz commented Apr 18, 2018

@godofdream thanks for your interest in CockroachDB.

Do you have an example app or framework in mind that already uses this feature? Where compatibility is blocked because CockroachDB does not support it?

@knz knz added the O-community Originated from the community label Apr 18, 2018
@godofdream
Copy link
Author

godofdream commented Apr 20, 2018 •
edited
Loading

@knz Currently we use Postgres with the replace view function in a closed-source project. We use the replace as function for migrating to newer versions of the view. It's syntactic sugar as it could be done with a transaction alternativly.

Besides our usecase i didn't find any opensource projects using the create or replace view feature of postgres.

@a-robinson marked CREATE OR REPLACE as TODO in the create_view code

cockroach/pkg/sql/create_view.go

Line 122 in 36e3bc6

// TODO(a-robinson): Support CREATE OR REPLACE commands.

@knz
Copy link
Contributor

knz commented Apr 20, 2018

@godofdream thanks for your explanation. I wanted to hear the context of your request so that we know how to prioritize this appropriately.

@knz knz added the C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) label May 2, 2018
@knz knz mentioned this issue Oct 23, 2018
Closed
@knz knz removed their assignment Jan 3, 2019
@knz knz added the X-anchored-telemetry The issue number is anchored by telemetry references. label Jan 3, 2019
@BramGruneir BramGruneir mentioned this issue Jan 3, 2019
Open
18 tasks
@BramGruneir
Copy link
Member

@awoods187 This seems like a fairly straightforward ask. Can we get this into 20.1?

@knz
Copy link
Contributor

knz commented Sep 24, 2019

(Not completely straighforward, we still have to check the type compatibility, and update all the references.)

@BramGruneir
Copy link
Member

Yes, but that would just follow the alter view command that already works.

@knz
Copy link
Contributor

knz commented Sep 24, 2019

oh I did not know that alter view was already a thing.

@BramGruneir
Copy link
Member

Ah, we only support rename in alter view :( Let's get that fixed for 20.1 first.

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
@RoachietheSupportRoach
Copy link
Collaborator

Zendesk ticket #3675 has been linked to this issue.

@RoachietheSupportRoach
Copy link
Collaborator

Zendesk ticket #3752 has been linked to this issue.

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>
@lukaseder lukaseder mentioned this issue Nov 8, 2019
Closed
rohany added a commit to rohany/cockroach that referenced this issue Apr 5, 2020
@rohany
sql: add support for CREATE OR REPLACE VIEW
02c77fd 
Fixes cockroachdb#24897.

This PR adds support for the CREATE OR REPLACE VIEW
command by allowing the create view statement to optionally
overwrite an existing descriptor, rather than always write
a new descriptor k/v entry.

Release note (sql change): This PR adds support for the
CREATE OR REPLACE VIEW command.
@rohany rohany mentioned this issue Apr 5, 2020
Merged
rohany added a commit to rohany/cockroach that referenced this issue Apr 5, 2020
@rohany
sql: add support for CREATE OR REPLACE VIEW
574437d 
Fixes cockroachdb#24897.

This PR adds support for the CREATE OR REPLACE VIEW
command by allowing the create view statement to optionally
overwrite an existing descriptor, rather than always write
a new descriptor k/v entry.

Release note (sql change): This PR adds support for the
CREATE OR REPLACE VIEW command.
rohany added a commit to rohany/cockroach that referenced this issue Apr 6, 2020
@rohany
sql: add support for CREATE OR REPLACE VIEW
180dc77 
Fixes cockroachdb#24897.

This PR adds support for the CREATE OR REPLACE VIEW
command by allowing the create view statement to optionally
overwrite an existing descriptor, rather than always write
a new descriptor k/v entry.

Release note (sql change): This PR adds support for the
CREATE OR REPLACE VIEW command.
craig bot pushed a commit that referenced this issue Apr 11, 2020
@rohany
Merge #47051
94b624e 
47051: sql: add support for CREATE OR REPLACE VIEW r=rohany a=rohany

Fixes #24897.

This PR adds support for the CREATE OR REPLACE VIEW
command by allowing the create view statement to optionally
overwrite an existing descriptor, rather than always write
a new descriptor k/v entry.

Release note (sql change): This PR adds support for the
CREATE OR REPLACE VIEW command.

Co-authored-by: Rohan Yadav <rohany@alumni.cmu.edu>
@craig craig bot closed this as completed in 749351a Apr 11, 2020
@lukaseder
Copy link

Folks, is there a way to see from an issue like this in what version the feature will ship?

@lukaseder lukaseder mentioned this issue Apr 24, 2020
Closed
@knz
Copy link
Contributor

knz commented Apr 24, 2020

  1. find the PR number that addresses the issue
  2. check out each release-XX branch in turn
  3. in each branch, search for that PR number in the git log

If a branch has the PR, then that release has the fix.

@knz
Copy link
Contributor

knz commented Apr 24, 2020

FYI in this particular case the first release to have it will be v20.2

@lukaseder
Copy link

Great, thanks a lot, @knz

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
A-sql-pgcompat Semantic compatibility with PostgreSQL C-enhancement Solution expected to add code/behavior + preserve backward-compat (pg compat issues are exception) O-community Originated from the community X-anchored-telemetry The issue number is anchored by telemetry references.
Projects
None yet
Milestone
No milestone
Development

Successfully merging a pull request may close this issue.

sql: add support for CREATE OR REPLACE VIEW rohany/cockroach
6 participants
@danhhz @knz @lukaseder @BramGruneir @godofdream @RoachietheSupportRoach