Thread: CREATE DATABASE

CREATE DATABASE

From
Rafal Pietrak
Date:
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.

Now, the solution looks simple: if only "CREATE DATABASE" had an
additional keyword, like CONNECT, meaning "\c <newdb>" after successful
DB creation, the execution a similar sequence from history buffer, would
be much safer.

So I'd like to express here this 'feature request' - Regretably, I'm not
quite up to implementing such feature myself, but may be someone can?
Comments?

--
-R

Re: CREATE DATABASE

From
Richard Huxton
Date:
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

Re: CREATE DATABASE

From
Rafal Pietrak
Date:
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

Re: CREATE DATABASE

From
Tom Lane
Date:
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

Re: CREATE DATABASE

From
"Nikolay Samokhvalov"
Date:
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

Re: CREATE DATABASE

From
"Nikolay Samokhvalov"
Date:
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

Re: CREATE DATABASE

From
Stephan Szabo
Date:
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.

Re: CREATE DATABASE

From
"Nikolay Samokhvalov"
Date:
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

Re: CREATE DATABASE

From
Rafal Pietrak
Date:
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