On Tue, 22 Apr 2003, Dustin Sallings wrote:

> Around 14:26 on Apr 21, 2003, scott.marlowe said:
> 
> # Why are those two seperate issues?  Why can't the same answer be easily
> # and readily available to both the DBA and the programmer?  Why does one
> # have to first use psql -E to figure out the queries needed then figure
> # out which ones to use and not use etc...?  I'm not saying the \ commands
> # are bad, I'm saying they're implemented in the wrong place.  Having \ in
> # the psql monitor is fine.  But it should really be hitting views in the
> # background where possible.
> 
> 	That's part of your database abstraction layer.  JDBC does all of
> this stuff for you in a clean way.  If you're doing this without a
> database abstraction layer, then you're writing non-portable code unless
> you try to create identical views on every DB you use.
> 
> 	That said, it's not hard to create a view to do what \dt does.  I
> still haven't ever had a need to do it, though.

I'm talking more about a setup like what we have here at work.  A dozen or 
fewer Unix/Linux geeks running the postgresql boxes via ssh with psql who 
know SQL and prefer a command line, and about three or four dozen 
sales and marketing folks who use Windows programs that access the 
database through ODBC.

To the Windows guys, how do I tell them to just create a view 
encapsulating:

SELECT c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 
'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
WHERE c.relkind IN ('r','v','S','')
  AND c.relname !~ '^pg_'
ORDER BY 1;

if they want a list of the tables, sequences, views and indexes in 
postgresql.

It's like asking someone if they want an engine with their car, they just 
stare at you for a second wondering if you're joking or something.


Tue, 22 Apr 2003 11:17:45 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Are we losing momentum?
On Tuesday 22 April 2003 22:47, scott.marlowe wrote:
> To the Windows guys, how do I tell them to just create a view
> encapsulating:
>
> SELECT c.relname as "Name",
>   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
> 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
>   u.usename as "Owner"
> FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
> WHERE c.relkind IN ('r','v','S','')
>   AND c.relname !~ '^pg_'
> ORDER BY 1;
>
> if they want a list of the tables, sequences, views and indexes in
> postgresql.

Have you used TORA any times? It does support postgresql and it does it pretty 
well..

 Shridhar


Wed, 23 Apr 2003 11:45:58 +0530
Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
Re: Are we losing momentum?
On Wed, 2003-04-23 at 02:15, Shridhar Daithankar wrote:
> On Tuesday 22 April 2003 22:47, scott.marlowe wrote:
> > To the Windows guys, how do I tell them to just create a view
> > encapsulating:
> >
> > SELECT c.relname as "Name",
> >   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
> > 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
> >   u.usename as "Owner"
> > FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
> > WHERE c.relkind IN ('r','v','S','')
> >   AND c.relname !~ '^pg_'
> > ORDER BY 1;
> >
> > if they want a list of the tables, sequences, views and indexes in
> > postgresql.
> 
> Have you used TORA any times? It does support postgresql and it does it pretty 
> well..

http://techdocs.postgresql.org/guides/GUITools

A rather handy list of GUITools available for PostgreSQL.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
23 Apr 2003 08:44:13 -0400
Rod Taylor <rbt@rbt.ca>
Re: Are we losing momentum?
On Wed, 23 Apr 2003, Shridhar Daithankar wrote:

> On Tuesday 22 April 2003 22:47, scott.marlowe wrote:
> > To the Windows guys, how do I tell them to just create a view
> > encapsulating:
> >
> > SELECT c.relname as "Name",
> >   CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN
> > 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
> >   u.usename as "Owner"
> > FROM pg_class c LEFT JOIN pg_user u ON c.relowner = u.usesysid
> > WHERE c.relkind IN ('r','v','S','')
> >   AND c.relname !~ '^pg_'
> > ORDER BY 1;
> >
> > if they want a list of the tables, sequences, views and indexes in
> > postgresql.
> 
> Have you used TORA any times? It does support postgresql and it does it pretty 
> well..

Actually, most of the Unix guys are happy with psql, while most of the 
Windows guys seem happy with pgadmin II.  But some of the developer types 
are writing things that issue DDL, and they need to look at the structure 
of the database in their code, and for them, the current implementation of 
system tables is a bit awkward to grasp.  

Plus the fact that the underlying pg_ tables are stable from release to 
release makes it a bit awkward to upgrade the servers they play on. 
Most of them have gone ahead and created views that give them a consistent 
view of the parts of the database they need.  

but it looks like there's a good chance of having views in a future 
version of pgsql that are just standard built-ins that won't change 
(much???) from version to version, so my problems are really not that 
great over time.


Wed, 23 Apr 2003 09:27:04 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: Are we losing momentum?
>>>>> "scott" == scott marlowe <scott.marlowe> writes:

    scott> Plus the fact that the underlying pg_ tables are stable
    scott> from release to release makes it a bit awkward to upgrade
    scott> the servers they play on.  Most of them have gone ahead and
    scott> created views that give them a consistent view of the parts
    scott> of the database they need.

This is exactly the reason why in db2 _no_ guarantees are made
regarding the constancy of the system catalogs (that are in the SYSIBM
schema). Instead, the equivalent views (in the SYSCAT schema) are
_never_ broken (whether in a point release or a new version). In fact,
the SYSCAT views correspond to the ISO SQL standard. 

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh


23 Apr 2003 22:49:20 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: Are we losing momentum?
On Thu, 2003-04-24 at 01:49, Sailesh Krishnamurthy wrote:
> >>>>> "scott" == scott marlowe <scott.marlowe> writes:
> 
>     scott> Plus the fact that the underlying pg_ tables are stable
>     scott> from release to release makes it a bit awkward to upgrade
>     scott> the servers they play on.  Most of them have gone ahead and
>     scott> created views that give them a consistent view of the parts
>     scott> of the database they need.
> 
> This is exactly the reason why in db2 _no_ guarantees are made
> regarding the constancy of the system catalogs (that are in the SYSIBM
> schema). Instead, the equivalent views (in the SYSCAT schema) are
> _never_ broken (whether in a point release or a new version). In fact,
> the SYSCAT views correspond to the ISO SQL standard.

The INFORMATION_SCHEMA?  Out of curiousity, how do they handle DB2
extensions?  Do they create new views in that schema?  Do they ignore
them?

I'm going with the assumptions DB2 has extended SQL specs in some shape
or form.

 
-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
24 Apr 2003 09:06:37 -0400
Rod Taylor <rbt@rbt.ca>
Re: Are we losing momentum?
>>>>> "Rod" == Rod Taylor <rbt@rbt.ca> writes:

    >>  This is exactly the reason why in db2 _no_ guarantees are made
    >> regarding the constancy of the system catalogs (that are in the
    >> SYSIBM schema). Instead, the equivalent views (in the SYSCAT
    >> schema) are _never_ broken (whether in a point release or a new
    >> version). In fact, the SYSCAT views correspond to the ISO SQL
    >> standard.

    Rod> The INFORMATION_SCHEMA?  Out of curiousity, how do they
    Rod> handle DB2 extensions?  Do they create new views in that
    Rod> schema?  Do they ignore them?

Yes, the INFO SCHEMA - thankfully it's long enough since I looked at
the SQL specs .. I've started forgetting terms. If I never have to
write or read any spec material again, it won't be too soon. 

Why extensions, even for things like indexes that aren't in the
standard, they create views (SYSCAT.INDEXES, SYSCAT.INDEXAUTH etc.) 

    Rod> I'm going with the assumptions DB2 has extended SQL specs in
    Rod> some shape or form.

Certainly - it's just that the meaning and number of existing columns
and rows in the syscat views are always backward compatible. That
includes support of the info schema - for the sql standard features
that db2 supports.

So if there's something new in the catalog tables that is a result of
an extension and doesn't appear as a column in the syscat views (or
the info schema) then an appropriate column may be added to the view -
provided that this doesn't break the info schema compatibility.

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh


24 Apr 2003 15:12:47 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: Are we losing momentum?
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
>     Rod> The INFORMATION_SCHEMA?  Out of curiousity, how do they
>     Rod> handle DB2 extensions?  Do they create new views in that
>     Rod> schema?  Do they ignore them?

> Why extensions, even for things like indexes that aren't in the
> standard, they create views (SYSCAT.INDEXES, SYSCAT.INDEXAUTH etc.) 
> ...
> Certainly - it's just that the meaning and number of existing columns
> and rows in the syscat views are always backward compatible. That
> includes support of the info schema - for the sql standard features
> that db2 supports.

> So if there's something new in the catalog tables that is a result of
> an extension and doesn't appear as a column in the syscat views (or
> the info schema) then an appropriate column may be added to the view -
> provided that this doesn't break the info schema compatibility.

Of course, IBM can afford to keep reps on the SQL standards committee to
make sure that no future spec extension conflicts with the names they've
used for their additions to INFORMATION_SCHEMA.  We, on the other hand,
could easily get burnt by spec changes.

			regards, tom lane


Thu, 24 Apr 2003 18:28:27 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Are we losing momentum? 
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:

    Tom> Of course, IBM can afford to keep reps on the SQL standards
    Tom> committee to make sure that no future spec extension
    Tom> conflicts with the names they've used for their additions to
    Tom> INFORMATION_SCHEMA.  We, on the other hand, could easily get
    Tom> burnt by spec changes.

Right it's pretty unfair. I'm not beating any drums here. It's more
than just making sure that no extensions conflict with what they've
used. It's also about makign their extensions the default. 

One thing that they _do_ try though is to use very ibm-centric when
possible.

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh


24 Apr 2003 15:46:44 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: Are we losing momentum?
On Thu, 2003-04-24 at 18:28, Tom Lane wrote:
> Sailesh Krishnamurthy <sailesh@cs.berkeley.edu> writes:
> >     Rod> The INFORMATION_SCHEMA?  Out of curiousity, how do they
> >     Rod> handle DB2 extensions?  Do they create new views in that
> >     Rod> schema?  Do they ignore them?
> 
> > Why extensions, even for things like indexes that aren't in the
> > standard, they create views (SYSCAT.INDEXES, SYSCAT.INDEXAUTH etc.) 
> > ...
> > Certainly - it's just that the meaning and number of existing columns
> > and rows in the syscat views are always backward compatible. That
> > includes support of the info schema - for the sql standard features
> > that db2 supports.
> 
> > So if there's something new in the catalog tables that is a result of
> > an extension and doesn't appear as a column in the syscat views (or
> > the info schema) then an appropriate column may be added to the view -
> > provided that this doesn't break the info schema compatibility.
> 
> Of course, IBM can afford to keep reps on the SQL standards committee to
> make sure that no future spec extension conflicts with the names they've
> used for their additions to INFORMATION_SCHEMA.  We, on the other hand,
> could easily get burnt by spec changes.

We could probably get away with adding pg_ views to the information
schema though.   For extensions of an existing view, simply inherit the
real view into a pg_ labelled view and add the new columns.

-- 
Rod Taylor <rbt@rbt.ca>

PGP Key: http://www.rbt.ca/rbtpub.asc
24 Apr 2003 19:14:02 -0400
Rod Taylor <rbt@rbt.ca>
Re: Are we losing momentum?