Cockroach: sql: support creating a database as duplicate of another ("create database ... with template ...")

[alex]

• What did you do?

Attempted to run: CREATE DATABASE "test__1" WITH TEMPLATE "test_"

• What did you expect to see?

A successful creation of a new database

• What did you see instead?

syntax error at or near "WITH"
CREATE DATABASE "test__1" WITH TEMPLATE "test_"

For reference: https://www.postgresql.org/docs/9.3/static/manage-ag-templatedbs.html

[knz]

Hi @alex thanks for reporting this issue. Could you tell us a bit more about the context in which you'd find this feature useful?

[alex]

This feature is used by Django's test runner.

[knz]

Thanks!

[knz]

cc @nvanbenschoten did you see this come up in other clients too?

[nvanbenschoten]

I have never seen this used elsewhere. If it's only ever used for testing, I don't think it needs to be prioritized very highly. That said, it would be nice to get Django's tests working against us, and I don't think this specific syntax would be very difficult to support.

[alex]

To be clear, this is used both in Django's own test suites, as well as the test suites for any projects using Django.

[nvanbenschoten]

I may have misspoken earlier when I said this would be simple to support, because I misread the issue as "create table ... with template ...". Permitting the copy of an existing database, with all of its current data, would be an involved change with a much larger scope than I had initially considered. The link you posted discusses some of the challenges duplicating a database faces, and then talks about PG's limitations.

While I would love to support this functionality right away, I don't see it on our immediate roadmap. Feel free to correct me @knz.

Also looping in @paperstreet and @mjibson, as this syntax may have some overlap with our (work-in-progress) backup/restore utility.

[mjibson]

Yes, I think our upcoming work on backup/restore would handle this case in a graceful way. However I would guess we are some months off from worrying about this specific use case because other work is a higher priority. If we have many django projects blocked on this feature we could reprioritize.

[bdarnell]

I don't think this is the only thing blocking django support (last time I looked into this, the wall I ran into #5950)

This is a case where it's probably easier to use a custom cockroachdb backend for the django ORM than to implement all the corners of sql the same way postgres does. CREATE DATABASE WITH TEMPLATE is a postgres extension; django doesn't use it for other databases. It just needs some way to clone a database (for mysql, it shells out to mysqldump). With a subclass of the postgresl backend we could make it walk over SHOW TABLE and use CREATE TABLE $1 AS SELECT * FROM $2