Re: [BUGS] BUG #5218: Easy strategic feature requests - Mailing list pgsql-general

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 <database> 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