Re: [HACKERS] Are we losing momentum? - Mailing list pgsql-patches

> > That's a pretty reasonable thought. I work for a shop that sells
> > Postgres support, and even we install MySQL for the Q&D ticket
> > tracking system we recommend because we can't justify the cost to
> > port it to postgres. If the postgres support were there, we would
> > surely be using it.
>
> > How to fix such a situation, I'm not sure. "MySQL Compatability
> > Mode," anyone? :-)
>
> What issues are creating a compatibility problem for you?

I don't think these should be hacked into the backend/libpq, but I
think it'd be a huge win to hack in "show *" support into psql for
MySQL users so they can type:

     SHOW (databases|tables|views|functions|triggers|schemas);

I have yet to meet a MySQL user who understands the concept of system
catalogs even though it's just the 'mysql' database (this irritates me
enough as is)... gah, f- it: mysql users be damned, I have three
developers that think that postgresql is too hard to use because they
can't remember "\d [table name]" and I'm tired of hearing them bitch
when I push using PostgreSQL instead of MySQL.  I have better things
to do with my time than convert their output to PostgreSQL.  Here goes
nothing...

I've tainted psql and added a MySQL command compatibility layer for
the family of SHOW commands (psql [-m | --mysql]).


The attached patch does a few things:

1) Implements quite a number of SHOW commands (AGGREGATES, CASTS,
   CATALOGS, COLUMNS, COMMENTS, CONSTRAINTS, CONVERSIONS, DATABASES,
   DOMAINS, FUNCTIONS, HELP, INDEX, LARGEOBJECTS, NAMES, OPERATORS,
   PRIVILEGES, PROCESSLIST, SCHEMAS, SEQUENCES, SESSION, STATUS,
   TABLES, TRANSACTION, TYPES, USERS, VARIABLES, VIEWS)

   SHOW thing
   SHOW thing LIKE pattern
   SHOW thing FROM pattern
   SHOW HELP ON (topic || ALL);
   etc.

   Some of these don't have \ command eqiv's.  :( I was tempted to add
   them, but opted not to for now, but it'd certainly be a nice to
   have.

2) Implements the necessary tab completion for the SHOW commands for
   the tab happy newbies/folks out there.  psql is more friendly than
   mysql's CLI now in terms of tab completion for the show commands.

3) Few trailing whitespace characters were nuked

4) guc.c is now in sync with the list of available variables used for
   tab completion


Few things to note:

1) SHOW INDEXES is the same as SHOW INDEX, I think MySQL is wrong in
   this regard and that it should be INDEXES to be plural along with
   the rest of the types, but INDEX is preserved for compatibility.

2) There are two bugs that I have yet to address

   1) SHOW VARIABLES doesn't work, but "SHOW [TAB][TAB]y" does
   2) "SHOW [variable_of_choice];" doesn't work, but "SHOW
      [variable_of_choice]\n;" does work... not sure where this
      problem is coming from

3) I think psql is more usable as a result of this more verbose
   syntax, but it's not the prettiest thing on the planet (wrote a
   small parser outside of the backend or libraries: I don't want to
   get those dirty with MySQL's filth).

4) In an attempt to wean people over to PostgreSQL's syntax, I
   included translation tips on how to use the psql equiv of the SHOW
   commands.  Going from SHOW foo to \d foo is easy, going from \d foo
   to SHOW foo is hard and drives me nuts.  This'll help userbase
   retention of newbies/converts.  :)

5) The MySQL mode is just a bounce layer that provides different
   syntax wrapping exec_command() so it should provide little in the
   way of maintenance headaches.  Some of the SHOW commands, however,
   don't have \ couterparts, but once they do and that code is
   centralized, this feature should come for zero cost.

6) As an administrator, I'd be interested in having an environment
   variable that I could set that'd turn on MySQL mode for some of my
   bozo users that way they don't complain if they forget the -m
   switch.  Thoughts?


I'll try and iron out the last of those two bugs/features, but at this
point, would like to see this patch get wider testing/feedback.
Comments, as always, are welcome.

PostgreSQL_usability++

-sc

--
Sean Chittenden