CREATE DATABASE

Rafal Pietrak wrote:

Hi All,

I'd like to cast a small 'feature request' here for discussion/
evaluation. The case is the following:

When working on a new database application I quite frequently:
-------------------------------
test_xx# \c template1
template1# DROP DATABASE test_xx;
template1# CREATE DATABASE test_xx TEMPLATE = earlier_version;
template1# \c test_xx
template1# \i some_cooked_dataset.sql-dump
-------------------------------

All this is of cource issued from psql utility, and using psql_history
buffer.

Now, the thing is, that I have to be very carefull and focused when
going back in psql_history, as several times I've skiped the "\c
test_xx" stage .... and ended up in a mass.

The quickest solution is to put the whole thing in one script and
execute that with \i or -f from the command-prompt.

--
Richard Huxton
Archonet Ltd

On Thu, 2006-08-03 at 18:07 +0100, Richard Huxton wrote:

Rafal Pietrak wrote:

When working on a new database application I quite frequently:
-------------------------------
test_xx# \c template1
template1# DROP DATABASE test_xx;
template1# CREATE DATABASE test_xx TEMPLATE = earlier_version;
template1# \c test_xx
template1# \i some_cooked_dataset.sql-dump
-------------------------------

The quickest solution is to put the whole thing in one script and
execute that with \i or -f from the command-prompt.

Well, the trick is, that when I refere back to previous CREATE DATABASE,
I quite often adjust it: name or owner, or template.

To put it the other way around: I don't remember me creating a database
and *needing* to stay within the initiator (like template1) - that's why
I've ventured the feature request. If not a 'CONNECT' option, may be z
"SET parameter" for a session user? This way pg_dumpall, which does
CREATE DATABASE and stays with the old one, since it is supposed to be
run as user postgres will continue to work correctly.

Frankly, when posting the original e-mail I feared to receive: "NO!!
that'll break a lot of scripts/applications". But if that's not the
case, I'd vote to put this request into (event very low priority)
to-be-implementes list. postgres is awesome to work with, part of the
quality of a 'product' is good set of defaults - having "CREATE DB"
connect to the new instance is GoodThing(tm) :).

just my 2c. not that I'm eager to elaborate this case any further.

Thenx.
--
-R

Rafal Pietrak <rafal@zorro.isa-geek.com> writes:

To put it the other way around: I don't remember me creating a database
and *needing* to stay within the initiator (like template1) - that's why
I've ventured the feature request. If not a 'CONNECT' option, may be z
"SET parameter" for a session user?

The reason neither of these are going to happen is that you're confusing
a server-side SQL command (viz CREATE DATABASE) with a client-side
operation (viz choosing to drop the server connection and make a new one
to a different database). The server cannot force the client to do
that, and we're unlikely to look favorably on adding syntax that the
server is supposed to ignore while the client starts parsing every
command to see if it's in there.

You could imagine inventing a psql operation like

\create_and_connect_to dbname ... other createdb parameters ...

but it still seems more like a kluge than a useful feature.

regards, tom lane

On 8/4/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

The reason neither of these are going to happen is that you're confusing
a server-side SQL command (viz CREATE DATABASE) with a client-side
operation (viz choosing to drop the server connection and make a new one
to a different database).

Hmm.. I always thought that creating connection is two-sided operation
(client sends requests, server accepts and sends that all is OK,
including that client sent proper user/pwd and wants to connect to
proper DB).

You're breaking my knowledge of client-server architecture.

What is the difference between the procedure of connection
establishing and database creation from user POV? Client asks server
and server does some work and then replies. What kind of work?
Permissions checking or creating of DB - yes, very different things,
but the difference is significant for backend! Notice, that for client
there is no such huge difference. The only difference is the order of
operations. BTW, difference vanishes due to expression power of SQL -
it supports session comands in the same context as DDL commands and
data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
statements').

If I'm wrong, I'd be very glad to see your contradiction.

--
Best regards,
Nikolay

On 8/4/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
[...]

BTW, difference vanishes due to expression power of SQL -
it supports session comands in the same context as DDL commands and
data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
statements').

Sorry, I already see my failure. It is in the fact that Postgres
doesn't support that connection statements yet (but some connection
things are supported - like changing the CURRENT_ROLE).

So, my previous message is interesting only from theoretical point of
view. I always try to thing in the manner of SQL standard or use
knowledge from books/university ...

But the logic is clear, isn't it? Connection is not client operation.
I think that Rafal's proposal is quite interesting (I experience the
same difficulties every time. There was several wrong DROP DATABASE in
my career... :-) )

--
Best regards,
Nikolay

On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote:

On 8/4/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
[...]

BTW, difference vanishes due to expression power of SQL -
it supports session comands in the same context as DDL commands and
data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
statements').

Sorry, I already see my failure. It is in the fact that Postgres
doesn't support that connection statements yet (but some connection
things are supported - like changing the CURRENT_ROLE).

So, my previous message is interesting only from theoretical point of
view. I always try to thing in the manner of SQL standard or use
knowledge from books/university ...

But the logic is clear, isn't it? Connection is not client operation.

I'd read 4.39 differently which seems to imply that the SQL-client handles
the connection statements.

Thanks. 'connect to' cannot be sent to server as plain text, surely.
I'm stupid :-(

On 8/4/06, Stephan Szabo <sszabo@megazone.bigpanda.com> wrote:

On Fri, 4 Aug 2006, Nikolay Samokhvalov wrote:

On 8/4/06, Nikolay Samokhvalov <samokhvalov@gmail.com> wrote:
[...]

BTW, difference vanishes due to expression power of SQL -
it supports session comands in the same context as DDL commands and
data manipulation stmts (SQL:200n, 4.33.2.5 'SQL-connection
statements').

Sorry, I already see my failure. It is in the fact that Postgres
doesn't support that connection statements yet (but some connection
things are supported - like changing the CURRENT_ROLE).

So, my previous message is interesting only from theoretical point of
view. I always try to thing in the manner of SQL standard or use
knowledge from books/university ...

But the logic is clear, isn't it? Connection is not client operation.

I'd read 4.39 differently which seems to imply that the SQL-client handles
the connection statements.

--
Best regards,
Nikolay

On Fri, 2006-08-04 at 00:44 +0400, Nikolay Samokhvalov wrote:

But the logic is clear, isn't it? Connection is not client operation.
I think that Rafal's proposal is quite interesting (I experience the
same difficulties every time. There was several wrong DROP DATABASE in
my career... :-) )

Glad to see at leas some backing :) So I'd push the case a little
further.

I'm not very proficient with the stadard per se. So I'm not sure what
exactly the 4.33... define, but in any case, my original intention was
to have 'some extra magic' behind the screen (meaning, not neceserly to
the letter of a standard - a natural newbee behavior :).

Originally I though that having just CONNECT option for CREATE DATABASE,
or some sort of "SET connect2_fresh_database = true" in session
parameters would suffice. But now I realise, that client side library is
supposed to know which database it's talking to (at least the psql
command line utility should be able to change its prompt accordingly,
like it does today) which escalates the problem technicaly. Namely, even
if the 'extra magic' is implemented in the visinity of 'CREATE DATABASE'
code, there have to be a way for the backend to pass that information to
the frontend (over the backend-frontend protocol) .... which may not be
so easy to implement... I gues.

Anyway. I'd be good to have :)

--
-R