Re: [BUGS] BUG #5218: Easy strategic feature requests

On 29/11/2009 6:34 AM, Russell Wallace wrote:

> (There wasn't a separate form for feature requests, so I'm assuming that
> like most projects, this one files them together with bugs, please let me
> know if that is not the case.)

http://wiki.postgresql.org/wiki/Todo

... but it's generally preferred to discuss the issue on the -general 
mailing list. I've replied to that list, where discussion will carry on. 
Please don't respond to the post on -bugs.

> Can you make Show Databases, Show Tables and Describe (table) work the way
> they do in MySQL? This would make things easier for newbies and would also
> make it easier to port code from MySQL; they would only need to be syntax
> sugar for the corresponding information schema queries, so it would probably
> only take a few lines of code, and would improve PostgreSQL's competitive
> position versus MySQL out of all proportion to the apparent significance of
> these features.

I'm not sure it's as easy as you think in terms of backend 
implementation (and I suspect it'd be uglier than would be desirable 
too), but I do agree that something like that behaviour might be a 
useful helping hand for migrating users.

Rather than implementing SHOW (blah) as wrappers for selects from views 
in the backend, though, IMO it'd make a lot more sense to provide 
minimal stubs that raised an exception telling the user the right way to 
do it.

"SHOW TABLES is a MySQL-specific command that isn't part of the SQL 
standard and isn't used by other databases. Please query the 
SQL-standard INFORMATION_SCHEMA instead, or for interactive work use 
psql's \d command."

etc.

Opinions, anybody?

> It would be nice if Use (database) could also be made to work, but I'm
> guessing this would not be just a few lines of code.

It can't be made to work *cleanly*. What MySQL calls a "database", 
PostgreSQL calls a "schema" within a single database. PostgreSQL has a 
schema search path that it uses to find objects when they are mentioned 
with unqualified names. Modifying this search path is roughly equivalent 
to USE-ing a database in MySQL.

This is one of those things where you've just got to learn the right way 
to do it.

In theory, PostgreSQL could be reworked to support changing databases 
within a connection. In practice ... eek. From my minmimal understanding 
of the codebase, enabling a backend to switch databases would require a 
huge overhaul of authentication, backend startup and management, shared 
memory management, and more.

It'd make more sense to let a backend hand a TCP/IP socket over to a 
newly created backend on a different database. I don't know if that's 
reasonable or practical. I'm also not sure it makes sense to do.

You see, in Pg, if you're switching databases a lot this is a hint 
you're doing something wrong - you should be using separate schema in 
the same database instead.

The backend could implement USE as a dummy command that raises an 
exception, as I suggested for other MySQL-isms, saying something like:

"USE  is a MySQL-specific command that does not make sense in 
PostgreSQL. Please see mysql-use.html in the PostgreSQL documentation."

The docs file in question would explain schema-vs-database, psql's \c 
command, etc.

> What would be easy and
> still useful, however, would be if it could return a specific error message:
> "you can't do this in Postgres, you always need to supply a database name on
> connection"

Pg (well, libpq to be more accurate) defaults to connecting to a db with 
the same name as the running user if nothing else is specified.

--
Craig Ringer