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

On Sun, Nov 29, 2009 at 1:57 PM, Craig Ringer
<craig@postnewspapers.com.au> wrote:

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."

And hopefully a URL to a page that gives more details on
INFORMATION_SCHEMA, including the specific commands for the equivalent
of SHOW TABLES etc? Okay, fair enough, that would still provide most
of the benefit.

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.

eek indeed! Right, it's definitely not worth that sort of headache. An
error message explaining the situation would provide much of the
benefit for orders of magnitude less cost.

On Sat, Nov 28, 2009 at 5:34 PM, Russell Wallace
<russell.wallace@gmail.com> wrote:

The following bug has been logged online:

Bug reference:      5218
Logged by:          Russell Wallace
Email address:      russell.wallace@gmail.com
PostgreSQL version: 8.4.1
Operating system:   Windows
Description:        Easy strategic feature requests
Details:

(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.)

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.

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. 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"; again that would make life easier for newbies, and that matters
a lot for competitive advantage.

Features requests are usually discussed on pgsql-hackers. I'm not
sure there will be much support for this proposal, although I it did
take me a while to figure out the PostgreSQL equivalents of those
commands when I made the switch. For interactive use, the psql
backslash-commands are fine, but for scripted access to the schema,
the MySQL way is definitely easier. One of the problems is that
"show" is already a PostgreSQL verb with an incompatible meaning.

Rather than trying to be compatible with MySQL directly, I think we
might be better off adding a chapter to our documentation explaining
to ex-MySQL users how to accomplish the same things in PostgreSQL. We
might even think about adding a contrib module with user-defined
functions like show_databases(), show_tables(), describe(text), so
that someone could do SELECT describe('foo'); and get a familiar sort
of output.

...Robert

Rather than trying to be compatible with MySQL directly, I think we
might be better off adding a chapter to our documentation explaining
to ex-MySQL users how to accomplish the same things in PostgreSQL.  We
might even think about adding a contrib module with user-defined
functions like show_databases(), show_tables(), describe(text), so
that someone could do SELECT describe('foo'); and get a familiar sort
of output.

I thing so ex-MySQL needs exact DESCRIBE, exact SHOW statement. It is
some, what they used long time and then they missing it.
Hypothetically we could have a "super hook" on server side - or hook
on psql and we can implement these statements in some contrib module.
Personally I don't thing, so this can have any strong effect. If these
modules are not default, then ex-MySQL will have a problem. And if
ex-MySQL people will be happy with SHOW support, then they will be
dissatisfied for PostgreSQL user's rights.

p.s. super hook should be useful not only for mysql's statement

regards
pavel

On Mon, Nov 30, 2009 at 12:28 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Rather than trying to be compatible with MySQL directly, I think we
might be better off adding a chapter to our documentation explaining
to ex-MySQL users how to accomplish the same things in PostgreSQL.  We
might even think about adding a contrib module with user-defined
functions like show_databases(), show_tables(), describe(text), so
that someone could do SELECT describe('foo'); and get a familiar sort
of output.

I thing so ex-MySQL needs exact DESCRIBE, exact SHOW statement. It is
some, what they used long time and then they missing it.

Well, if people aren't willing to make ANY changes to their code or
habits when porting to a new database, they're likely hosed no matter
what we do about this particular issue. That having been said, I
wouldn't personally strongly object to implementing these commands as
a convenience for MySQL users, but I suspect others will consider them
unnecessary warts, which in some sense they are.

Hypothetically we could have a "super hook" on server side - or hook
on psql and we can implement these statements in some contrib module.

With all respect, I think this is quite similar to several things
you've proposed previously that have been rejected. At least, I
believe it suffers from many of the same problems.

...Robert

2009/11/30 Robert Haas <robertmhaas@gmail.com>:

On Mon, Nov 30, 2009 at 12:28 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Rather than trying to be compatible with MySQL directly, I think we
might be better off adding a chapter to our documentation explaining
to ex-MySQL users how to accomplish the same things in PostgreSQL.  We
might even think about adding a contrib module with user-defined
functions like show_databases(), show_tables(), describe(text), so
that someone could do SELECT describe('foo'); and get a familiar sort
of output.

I thing so ex-MySQL needs exact DESCRIBE, exact SHOW statement. It is
some, what they used long time and then they missing it.

Well, if people aren't willing to make ANY changes to their code or
habits when porting to a new database, they're likely hosed no matter
what we do about this particular issue.  That having been said, I
wouldn't personally strongly object to implementing these commands as
a convenience for MySQL users, but I suspect others will consider them
unnecessary warts, which in some sense they are.

Hypothetically we could have a "super hook" on server side - or hook
on psql and we can implement these statements in some contrib module.

With all respect, I think this is quite similar to several things
you've proposed previously that have been rejected.  At least, I
believe it suffers from many of the same problems.

I didn't speak about parser hook. This hook could exist on top. And it
can solve similar request. But It isn't my job.

Pavel

Pavel Stehule <pavel.stehule@gmail.com> writes:

I thing so ex-MySQL needs exact DESCRIBE, exact SHOW statement. It is
some, what they used long time and then they missing it.

You know, if they're not willing to make any adaptations, they're never
really going to make the transition anyhow. If we provide
bug-compatible versions of SHOW or whatever, people like that will just
move on to complaining about some other difference.

I'm in favor of trying to develop some documentation aimed at helping
new MySQL converts, but I don't think there's much to be gained by
doing more than that.

regards, tom lane

2009/11/30 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I thing so ex-MySQL needs exact DESCRIBE, exact SHOW statement. It is
some, what they used long time and then they missing it.

You know, if they're not willing to make any adaptations, they're never
really going to make the transition anyhow.  If we provide
bug-compatible versions of SHOW or whatever, people like that will just
move on to complaining about some other difference.

I'm in favor of trying to develop some documentation aimed at helping
new MySQL converts, but I don't think there's much to be gained by
doing more than that.

+1

Any official place has sense - minimally for people who can read documentation.

Regards
Pavel

On Mon, Nov 30, 2009 at 1:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2009/11/30 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I thing so ex-MySQL needs exact DESCRIBE, exact SHOW statement. It is
some, what they used long time and then they missing it.

You know, if they're not willing to make any adaptations, they're never
really going to make the transition anyhow.  If we provide
bug-compatible versions of SHOW or whatever, people like that will just
move on to complaining about some other difference.

I'm in favor of trying to develop some documentation aimed at helping
new MySQL converts, but I don't think there's much to be gained by
doing more than that.

+1

Any official place has sense - minimally for people who can read documentation.

This seems to be the exact opposite of what you said an hour and a
half ago, but, hey, at least we're all more or less in agreement now.
There remains the issue of finding a qualified person who is willing
to spend the time to write this documentation...

...Robert

2009/11/30 Robert Haas <robertmhaas@gmail.com>:

On Mon, Nov 30, 2009 at 1:42 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

2009/11/30 Tom Lane <tgl@sss.pgh.pa.us>:

Pavel Stehule <pavel.stehule@gmail.com> writes:

I thing so ex-MySQL needs exact DESCRIBE, exact SHOW statement. It is
some, what they used long time and then they missing it.

You know, if they're not willing to make any adaptations, they're never
really going to make the transition anyhow.  If we provide
bug-compatible versions of SHOW or whatever, people like that will just
move on to complaining about some other difference.

I'm in favor of trying to develop some documentation aimed at helping
new MySQL converts, but I don't think there's much to be gained by
doing more than that.

+1

Any official place has sense - minimally for people who can read documentation.

This seems to be the exact opposite of what you said an hour and a
half ago, but, hey, at least we're all more or less in agreement now.
There remains the issue of finding a qualified person who is willing
to spend the time to write this documentation...

I spoke about possible general solution about this request. What I
know people who use or who used MySQL, these people need exact same
behave. SELECT describe() is maybe similar, but it is strong
postgresql-ism - nobody ex MySQL use function for returning a
recordset. It's need a look to documentation - and nobody reads
documentation - so successful design is one (for ex-MySQL) - exact
MySQL behave. For these people - good PostgreSQL is MySQL :)

Personally, I thing, so DESCRIBE, SHOW is very bad design. psql
metastatements are cleaner - and information schema more intuitive.
MySQL has some good points what I would to see in Pg (like SQL/PSM),
real procedures, but not this.

Documentation can help to some people - without some uglyfication pg
source code. We see same problem - who will write it, and who will
maintain it. This documentation will be related to MySQL or to some
specified release?

Regards
Pavel Stehule