Thread: CREATE DATABASE
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
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