Thread: Prepping to break every past release...

Prepping to break every past release...

From
"Joshua D. Drake"
Date:
Hello,

Something that continues to grind my teeth about our software is that we
are horribly inconsistent with our system catalogs. Now I am fully and
100% aware that changing this will break things in user land but I want
to do it anyway. In order to do that I believe we need to come up with a
very loud, extremely verbose method of communicating to people that 8.5
*will* break things. 

It seems to me that the best method would be to follow the
information_schema naming conventions as information_schema is standard
compliant (right?).

Thoughts?

Examples:

postgres=# \d pg_class     Table "pg_catalog.pg_class"    Column     |   Type    | Modifiers 
----------------+-----------+-----------relname        | name      | not nullrelnamespace   | oid       | not null
[...]

postgres=# \d pg_tables   View "pg_catalog.pg_tables"  Column    |  Type   | Modifiers 
-------------+---------+-----------schemaname  | name    | tablename   | name    | 

postgres=# \d pg_stat_user_tables         View "pg_catalog.pg_stat_user_tables"     Column      |           Type
  | Modifiers 
 
------------------+--------------------------+-----------relid            | oid                      | schemaname
|name                     | relname          | name                     | 
 


postgres=# \d information_schema.tables                      View "information_schema.tables"           Column
 |               Type                |
 
Modifiers 
------------------------------+-----------------------------------+-----------table_catalog                |
information_schema.sql_identifier| table_schema                 | information_schema.sql_identifier | table_name
          | information_schema.sql_identifier | 
 


-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
Andrew Dunstan
Date:

Joshua D. Drake wrote:
> Hello,
>
> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs. Now I am fully and
> 100% aware that changing this will break things in user land but I want
> to do it anyway. In order to do that I believe we need to come up with a
> very loud, extremely verbose method of communicating to people that 8.5
> *will* break things. 
>
> It seems to me that the best method would be to follow the
> information_schema naming conventions as information_schema is standard
> compliant (right?).
>
> Thoughts?
>   


What would be the benefit? Apart from satisfying a passion for consistency?

cheers

andrew




Re: Prepping to break every past release...

From
Dave Page
Date:
On Wed, Mar 4, 2009 at 8:32 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Hello,
>
> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs. Now I am fully and
> 100% aware that changing this will break things in user land but I want
> to do it anyway. In order to do that I believe we need to come up with a
> very loud, extremely verbose method of communicating to people that 8.5
> *will* break things.

I assume you'll be putting in the weeks/months of work required to fix
pgAdmin & phpPgAdmin which would be far better spent on new features
than uglifying the code in far nastier ways than the current state of
the catalogs?

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com


Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Wed, 2009-03-04 at 15:50 -0500, Andrew Dunstan wrote:

> > It seems to me that the best method would be to follow the
> > information_schema naming conventions as information_schema is standard
> > compliant (right?).
> >
> > Thoughts?
> >   
> 
> 
> What would be the benefit? Apart from satisfying a passion for consistency?

It would lower the cost (intellectually as well as dollars) of
development and administration for every single user of the database.

I can't count how many times I accidentally type tablename versus
table_name or worse relname, etc... 

Sincerely,

Joshua D. Drake





-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Wed, 2009-03-04 at 21:01 +0000, Dave Page wrote:
> On Wed, Mar 4, 2009 at 8:32 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> > Hello,
> >
> > Something that continues to grind my teeth about our software is that we
> > are horribly inconsistent with our system catalogs. Now I am fully and
> > 100% aware that changing this will break things in user land but I want
> > to do it anyway. In order to do that I believe we need to come up with a
> > very loud, extremely verbose method of communicating to people that 8.5
> > *will* break things.
> 
> I assume you'll be putting in the weeks/months of work required to fix
> pgAdmin & phpPgAdmin which would be far better spent on new features
> than uglifying the code in far nastier ways than the current state of
> the catalogs?

*Shrug* long term consistency is the better choice. 

Sincerely,

Joshua D. Drake


> 
-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
Dave Page
Date:
On Wed, Mar 4, 2009 at 9:09 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

>> I assume you'll be putting in the weeks/months of work required to fix
>> pgAdmin & phpPgAdmin which would be far better spent on new features
>> than uglifying the code in far nastier ways than the current state of
>> the catalogs?
>
> *Shrug* long term consistency is the better choice.

Easy to say if you're not one of the people for whom such a change
would mean weeks of recoding, the need to start QA'ing everything from
scratch again and a hideous mess of code to cope with after adding
support for a new version with a different catalog schema.

Besides - what percentage of users ever go anywhere near the
catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5%
of developers.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com


Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Wed, 2009-03-04 at 21:14 +0000, Dave Page wrote:
> On Wed, Mar 4, 2009 at 9:09 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

> > *Shrug* long term consistency is the better choice.
> 
> Easy to say if you're not one of the people for whom such a change
> would mean weeks of recoding, the need to start QA'ing everything from
> scratch again and a hideous mess of code to cope with after adding
> support for a new version with a different catalog schema.
> 

It is not easy to say. It is correct to say. I am under no illusion that
this will not be painful. As far as "man weeks of recoding". Sorry, I
know that will be tough. 

> Besides - what percentage of users ever go anywhere near the
> catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5%
> of developers.
> 

Any DBA worth his salt uses system catalogs. Lowering the barrier on
uses these catalogs will lead to better and more useful tools as well.

Sincerely,

Joshua D. Drake



-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
Bill Moran
Date:
In response to Dave Page <dpage@pgadmin.org>:

> On Wed, Mar 4, 2009 at 9:09 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> 
> >> I assume you'll be putting in the weeks/months of work required to fix
> >> pgAdmin & phpPgAdmin which would be far better spent on new features
> >> than uglifying the code in far nastier ways than the current state of
> >> the catalogs?
> >
> > *Shrug* long term consistency is the better choice.
> 
> Easy to say if you're not one of the people for whom such a change
> would mean weeks of recoding

Don't those folks have to tweak their code with each new release anyway?
Because those tables are constantly changing?  I know we hit problems
with the way triggers are stored in 8.3 compared to earlier versions.

Granted, a sweeping change will necessitate a much larger tweak than
a few changed columns, but the long-term benefit should be cleaner
client code.

> Besides - what percentage of users ever go anywhere near the
> catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5%
> of developers.

You could always take a survey ... bosses love surveys ...

I, for one, know of lots of code that I've written that accesses those
catalogs.  I can't speak for other people.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/


Re: Prepping to break every past release...

From
Dave Page
Date:
On Wed, Mar 4, 2009 at 9:23 PM, Joshua D. Drake <jd@commandprompt.com> wrote:

> It is not easy to say. It is correct to say. I am under no illusion that
> this will not be painful. As far as "man weeks of recoding". Sorry, I
> know that will be tough.

It'll be a complete and utter waste of time, and make a horrible mess
of any client code that has to support multiple versions.

>> Besides - what percentage of users ever go anywhere near the
>> catalogs? I'd guess a fraction of a percent of users, and maybe 1 - 5%
>> of developers.
>>
>
> Any DBA worth his salt uses system catalogs. Lowering the barrier on
> uses these catalogs will lead to better and more useful tools as well.

Then psql and pgAdmin aren't doing their jobs properly. Tell us what you need.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com


Re: Prepping to break every past release...

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs. Now I am fully and
> 100% aware that changing this will break things in user land but I want
> to do it anyway. In order to do that I believe we need to come up with a
> very loud, extremely verbose method of communicating to people that 8.5
> *will* break things. 

> It seems to me that the best method would be to follow the
> information_schema naming conventions as information_schema is standard
> compliant (right?).

This isn't happening.  Don't waste our time suggesting it.
        regards, tom lane


Re: Prepping to break every past release...

From
Dave Page
Date:
On Wed, Mar 4, 2009 at 9:24 PM, Bill Moran <wmoran@potentialtech.com> wrote:
> In response to Dave Page <dpage@pgadmin.org>:
>
> Don't those folks have to tweak their code with each new release anyway?
> Because those tables are constantly changing?  I know we hit problems
> with the way triggers are stored in 8.3 compared to earlier versions.

pg_trigger.tgenabled changed type in 8.3. That's an extremely rare
kind of change however - for example, triggers havent really changed
since at least 7.3 aside from that.

The sort of change JD seems to be suggesting /could/ involve rewriting
virtually every query in pgAdmin, as well as surrounding code.

> Granted, a sweeping change will necessitate a much larger tweak than
> a few changed columns, but the long-term benefit should be cleaner
> client code.

Only if your app only targets one version of the server.

--
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com


Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Wed, 2009-03-04 at 21:27 +0000, Dave Page wrote:
> On Wed, Mar 4, 2009 at 9:23 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> 
> > It is not easy to say. It is correct to say. I am under no illusion that
> > this will not be painful. As far as "man weeks of recoding". Sorry, I
> > know that will be tough.
> 
> It'll be a complete and utter waste of time, and make a horrible mess
> of any client code that has to support multiple versions.

No actually it won't. It will make it easier for every product that uses
8.5 and above. Sometimes you have to cut the cord.

> > Any DBA worth his salt uses system catalogs. Lowering the barrier on
> > uses these catalogs will lead to better and more useful tools as well.
> 
> Then psql and pgAdmin aren't doing their jobs properly. Tell us what you need.
> 

That is an interesting argument but honestly off topic for this thread
because that would be a series of new views and functions etc...

Sincerely,

Joshua D. Drake


-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Wed, 2009-03-04 at 16:28 -0500, Tom Lane wrote:
> "Joshua D. Drake" <jd@commandprompt.com> writes:
> > Something that continues to grind my teeth about our software is that we
> > are horribly inconsistent with our system catalogs. Now I am fully and
> > 100% aware that changing this will break things in user land but I want
> > to do it anyway. In order to do that I believe we need to come up with a
> > very loud, extremely verbose method of communicating to people that 8.5
> > *will* break things. 
> 
> > It seems to me that the best method would be to follow the
> > information_schema naming conventions as information_schema is standard
> > compliant (right?).
> 
> This isn't happening.  Don't waste our time suggesting it.

I wasn't try to waste anyone's time. I thought doing things correctly
was a goal of this project. Sorry for the noise.

Sincerely,

Joshua D. Drake

> 
>             regards, tom lane
> 
-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
Magnus Hagander
Date:
Joshua D. Drake wrote:
> On Wed, 2009-03-04 at 21:27 +0000, Dave Page wrote:
>> On Wed, Mar 4, 2009 at 9:23 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
>>
>>> It is not easy to say. It is correct to say. I am under no illusion that
>>> this will not be painful. As far as "man weeks of recoding". Sorry, I
>>> know that will be tough.
>> It'll be a complete and utter waste of time, and make a horrible mess
>> of any client code that has to support multiple versions.
> 
> No actually it won't. It will make it easier for every product that uses
> 8.5 and above. Sometimes you have to cut the cord.

I think this sounds a lot like another request for a set of system views
with nicer names. That way, there'd be no break with backwards
compatibility, and you could use the easier names if you were on the
newer versions.

//Magnus


Re: Prepping to break every past release...

From
"Kevin Grittner"
Date:
>>> "Joshua D. Drake" <jd@commandprompt.com> wrote: 
> It would lower the cost (intellectually as well as dollars) of
> development and administration for every single user of the
> database.
-1
Any savings couldn't possibly pay for the cost and pain of this, at
least in our shop.
> I can't count how many times I accidentally type tablename versus
> table_name or worse relname, etc... 
I can count the times I have done so on one finger.  I don't find it
that hard to keep track of what column names are used in what table.
-Kevin


Re: Prepping to break every past release...

From
Tom Lane
Date:
Magnus Hagander <magnus@hagander.net> writes:
> I think this sounds a lot like another request for a set of system views
> with nicer names.

What's the state of the newsysviews project, anyway?  I don't recall
hearing much about it lately.
        regards, tom lane


Re: Prepping to break every past release...

From
Josh Berkus
Date:
Joshua D. Drake wrote:
> Hello,
> 
> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs. Now I am fully and
> 100% aware that changing this will break things in user land but I want
> to do it anyway. In order to do that I believe we need to come up with a
> very loud, extremely verbose method of communicating to people that 8.5
> *will* break things. 
> 
> It seems to me that the best method would be to follow the
> information_schema naming conventions as information_schema is standard
> compliant (right?).

Well, honestly, this was one of the reasons why AndrewSN, David and I 
pushed NewSysViews.  Non-hackers really shouldn't be accessing the 
system catalogs directly, and if information_schema doesn't give them 
enough data, we should start adding new views to I_S.

--Josh


Re: Prepping to break every past release...

From
Josh Berkus
Date:
Tom Lane wrote:
> Magnus Hagander <magnus@hagander.net> writes:
>> I think this sounds a lot like another request for a set of system views
>> with nicer names.
> 
> What's the state of the newsysviews project, anyway?  I don't recall
> hearing much about it lately.

Final verdict was that we need to make it integrate better with 
information_schema.  At that point, our crew kinda ran out of energy and 
it's been on hold ever since.   But we've been talking about reviving it 
again.

However, given the previous experience, I think we'd like some 
reassurance that if NewSysviews was made conistent with 
information_schema (and had good code, of course) that Hackers would be 
interested in taking it.  What nobody has any enthusiasm for is spending 
a few dozen hours refactoring it, and then having to justify why we want 
it *again*.

--Josh



Re: Prepping to break every past release...

From
Josh Berkus
Date:
> Well, honestly, this was one of the reasons why AndrewSN, David and I 
... and Elein ...

> pushed NewSysViews.  Non-hackers really shouldn't be accessing the 
> system catalogs directly, and if information_schema doesn't give them 
> enough data, we should start adding new views to I_S.




Re: Prepping to break every past release...

From
Josh Berkus
Date:
Bill,

> You could always take a survey ... bosses love surveys ...

Done: http://wwwmaster.postgresql.org/community/

--Josh



Re: Prepping to break every past release...

From
Andrew Dunstan
Date:

Josh Berkus wrote:
>
>> Well, honestly, this was one of the reasons why AndrewSN, David and I 
> ... and Elein ...
>
>> pushed NewSysViews.  Non-hackers really shouldn't be accessing the 
>> system catalogs directly, and if information_schema doesn't give them 
>> enough data, we should start adding new views to I_S.
>


This is really a long way from JD's original suggestion, though.

Back on that track, I'd like to see a facility whereby we could provide 
an alias (or synonym, to use a nearby subject) columns and other 
objects. That would help to overcome naming glitches without breaking 
things quite so much.

cheers

andrew


Re: Prepping to break every past release...

From
"Kevin Grittner"
Date:
>>> Andrew Dunstan <andrew@dunslane.net> wrote: 
> Back on that track, I'd like to see a facility whereby we could
provide 
> an alias (or synonym, to use a nearby subject) columns and other 
> objects. That would help to overcome naming glitches without breaking

> things quite so much.
How is that different from what you can do with a view?
-Kevin


Re: Prepping to break every past release...

From
"Kevin Grittner"
Date:
>>> Josh Berkus <josh@agliodbs.com> wrote: 
>> You could always take a survey ... bosses love surveys ...
> 
> Done: http://wwwmaster.postgresql.org/community/
Is there some assumed relationship between those options and whether
they'd like to see the names changed?
Also, it's hard to pick what option to choose there -- I typically
have cause about once or twice a month to poke around in those tables,
and we have development tools which reference these tables
directly....
I guess the closest option would be "access them all the time."  I
hope that wouldn't be interpreted as supporting the proposed change.
-Kevin


Re: Prepping to break every past release...

From
Josh Berkus
Date:
Andrew,

> Back on that track, I'd like to see a facility whereby we could provide 
> an alias (or synonym, to use a nearby subject) columns and other 
> objects. That would help to overcome naming glitches without breaking 
> things quite so much.

Believe it or not, a large PostgreSQL user in LA just buttonholed me 
about that particular feature idea at SCALE.  So it might be generally 
useful as well -- not just for the system catalogs, bug to allow 
businesses with long-use databases to manage change over time.

--Josh



Re: Prepping to break every past release...

From
Josh Berkus
Date:
Kevin Grittner wrote:
>>>> Josh Berkus <josh@agliodbs.com> wrote: 
>>> You could always take a survey ... bosses love surveys ...
>> Done: http://wwwmaster.postgresql.org/community/
>  
> Is there some assumed relationship between those options and whether
> they'd like to see the names changed?
>  
> Also, it's hard to pick what option to choose there -- I typically
> have cause about once or twice a month to poke around in those tables,
> and we have development tools which reference these tables
> directly....
>  
> I guess the closest option would be "access them all the time."  I
> hope that wouldn't be interpreted as supporting the proposed change.

I'm not taking a survey on how/whether we want to change it.  Just 
whether people are accessing them directly.

--Josh



Re: Prepping to break every past release...

From
"A.M."
Date:
On Mar 4, 2009, at 6:07 PM, Josh Berkus wrote:

> Andrew,
>
>> Back on that track, I'd like to see a facility whereby we could  
>> provide an alias (or synonym, to use a nearby subject) columns and  
>> other objects. That would help to overcome naming glitches without  
>> breaking things quite so much.
>
> Believe it or not, a large PostgreSQL user in LA just buttonholed me  
> about that particular feature idea at SCALE.  So it might be  
> generally useful as well -- not just for the system catalogs, bug to  
> allow businesses with long-use databases to manage change over time.

Schema change is a strong motivator for applications to access the  
database through views and functions only. A column with multiple  
names would likely make it *more* painful to migrate schemata.

Cheers,
M


Re: Prepping to break every past release...

From
Andrew Gierth
Date:
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> Magnus Hagander <magnus@hagander.net> writes:>> I think this sounds a lot like another request for a set of system>>
viewswith nicer names.
 
Tom> What's the state of the newsysviews project, anyway?  I don'tTom> recall hearing much about it lately.

At the time it was proposed for inclusion (pre 8.1, when it was mostly
but not entirely complete), you personally, as I recall, expressed the
opinion that its objective was impossible; that there was no way to
produce a sufficiently complete set of views that was more stable and
compatible between releases than the system catalogs themselves were.
I believe these sum up your response:

http://archives.postgresql.org/pgsql-hackers/2005-05/msg00351.php
http://archives.postgresql.org/pgsql-hackers/2005-05/msg00891.php
http://archives.postgresql.org/pgsql-hackers/2005-05/msg00940.php

Another common response at the time was "just use information_schema".
(Which is of course inadequate for a huge number of reasons - speed,
hiding of "implementation details", mismatches between pg's behaviour
and the SQL spec, and so on.)

Given the extent to which this criticism of the project was based on
speculation ("we might make changes in future releases that would
break the views"), there was no better answer at the time than "we
think the design is flexible enough to handle that", and very little
of the pushback we got actually showed any signs of having reviewed
the design and (admittedly incomplete) implementation. Accordingly I
stopped spending any time on it and diverted my attention elsewhere.

Now, of course, counting the upcoming 8.4 there have been three (and a
bit - the original design predates 8.1, though it did anticipate some
8.1 features) new releases against which the original concept can be
tested. And, guess what, nothing in those releases has even come close
to invalidating the original design concept (as we knew all along).

If you're still not convinced of that fact, it would be possible to
take the original design and update it to 8.4 following the original
plan. But I'm not prepared to spend any time on this if the only result
is going to be more argument.

-- 
Andrew (irc:RhodiumToad)


Re: Prepping to break every past release...

From
Robert Haas
Date:
On Wed, Mar 4, 2009 at 3:32 PM, Joshua D. Drake <jd@commandprompt.com> wrote:
> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs. Now I am fully and
> 100% aware that changing this will break things in user land but I want
> to do it anyway. In order to do that I believe we need to come up with a
> very loud, extremely verbose method of communicating to people that 8.5
> *will* break things.
>
> It seems to me that the best method would be to follow the
> information_schema naming conventions as information_schema is standard
> compliant (right?).
>
> Thoughts?

Like everyone else who has responded to this thread, I think this is a
pretty terrible idea. It's possible that there are some specific
columns in some specific tables that could stand to be renamed for
consistency, and perhaps if you come up with some specific proposals
with careful justifications someone might support the idea of doing
some limited renaming.  But too much renaming is not likely to be
popular with anyone for reasons that are somewhat summed up by your
subject line.

And, really, how much better would the new names be than the old ones
anyway?  The idea that a casual user will be able to query the system
catalogs and gain some sort of useful information without reading the
documentation or at least cracking out a couple of \d commands strikes
me as a pipe dream.  I'll admit that I'm a little mystified by why we
use pg_class to store relations (why not pg_relation?), relnamespace
to store the schema oid (why not relschema?), and so on, so some
improvement is probably possible.  But I'm not sure you're going to be
able to come up with a name that's substantially clearer than
proargmodes.  Sure, you could call it argument_modes, but that's not
really any clearer, it's just longer.  In fact, it's my experience
that exercises of this type almost always end up replacing shorter
names with longer names without really making anything any better.  In
the end you still have to RTFM.

...Robert


Re: Prepping to break every past release...

From
Greg Smith
Date:
On Wed, 4 Mar 2009, Joshua D. Drake wrote:

> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs.

I'd suggest staring at the gyrations required to do an in-place upgrade, 
then considering how the changes you're suggesting will make that even 
more difficult, until this inconsistency no longer seems very important.

> It seems to me that the best method would be to follow the
> information_schema naming conventions as information_schema is standard
> compliant (right?).

You could make a case for changing pg_tables.tablename to 
pg_tables.table_name, so that it better matched the information_schema. 
But it's not like that general approach makes this problem go away. 
You'll still have pg_class.rel_name or relname, because pg_class contains 
several types of relations:  tables, indexes, etc.  Since that particular 
mismatch is impossible to resolve, you can't completely simplify this area 
no matter how hard you try.  That makes it hard to get excited about just 
reducing the number of inconsistencies here.

> Any DBA worth his salt uses system catalogs. Lowering the barrier on 
> uses these catalogs will lead to better and more useful tools as well.

I would wager that putting 1% of the total effort needed to actually 
change the catalog schema names into a documentation/sample code push in 
this area would give a bigger payback.  I never write catalog stuff from 
scratch anymore; I go back to the same couple of template pieces I always 
end up needing for the various types of joins that always pop up and 
customize from there.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD


Re: Prepping to break every past release...

From
Stephen Frost
Date:
* Robert Haas (robertmhaas@gmail.com) wrote:
> Like everyone else who has responded to this thread, I think this is a
> pretty terrible idea.

[...]

+1 on Robert's assessment from me.  I'm generally a huge fan of doing
the right thing, but as my boss often likes to point out, you need to
pick your battles wisely.
Thanks,
    Stephen

Re: Prepping to break every past release...

From
David Fetter
Date:
On Wed, Mar 04, 2009 at 02:35:52PM -0800, Josh Berkus wrote:
> Tom Lane wrote:
>> Magnus Hagander <magnus@hagander.net> writes:
>>> I think this sounds a lot like another request for a set of system views
>>> with nicer names.
>>
>> What's the state of the newsysviews project, anyway?  I don't recall
>> hearing much about it lately.
>
> Final verdict was that we need to make it integrate better with  
> information_schema.  At that point, our crew kinda ran out of energy and  
> it's been on hold ever since.   But we've been talking about reviving it  
> again.

The information schema has things mandated by the SQL standard, and so
we really need to stay inside the lines with it.  By its nature, it
must hide "implementation details" which newsysviews can expose, so
now that there's a track record of 5 versions of compatible
newsysviews, I think we should make it a schema that ships with every
database, starting with 8.5.

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Mon, 2009-03-09 at 13:59 -0400, Bruce Momjian wrote:
> If this is the worst inconsistency you can find in our system tables
> after +20 years of development, I feel pretty good.

I was using a single example. This would be a large project I am sure
and of course we should feel good. In all I would say we are likely one
of the more consistent pieces of software in terms of our age. That
doesn't mean we shouldn't try to continue to improve.

Sincerely,

Joshua D. Drake

-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
Bruce Momjian
Date:
If this is the worst inconsistency you can find in our system tables
after +20 years of development, I feel pretty good.

---------------------------------------------------------------------------

Joshua D. Drake wrote:
> Hello,
> 
> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs. Now I am fully and
> 100% aware that changing this will break things in user land but I want
> to do it anyway. In order to do that I believe we need to come up with a
> very loud, extremely verbose method of communicating to people that 8.5
> *will* break things. 
> 
> It seems to me that the best method would be to follow the
> information_schema naming conventions as information_schema is standard
> compliant (right?).
> 
> Thoughts?
> 
> Examples:
> 
> postgres=# \d pg_class
>       Table "pg_catalog.pg_class"
>      Column     |   Type    | Modifiers 
> ----------------+-----------+-----------
>  relname        | name      | not null
>  relnamespace   | oid       | not null
> [...]
> 
> postgres=# \d pg_tables
>     View "pg_catalog.pg_tables"
>    Column    |  Type   | Modifiers 
> -------------+---------+-----------
>  schemaname  | name    | 
>  tablename   | name    | 
> 
> postgres=# \d pg_stat_user_tables
>           View "pg_catalog.pg_stat_user_tables"
>       Column      |           Type           | Modifiers 
> ------------------+--------------------------+-----------
>  relid            | oid                      | 
>  schemaname       | name                     | 
>  relname          | name                     | 
> 
> 
> postgres=# \d information_schema.tables
>                        View "information_schema.tables"
>             Column            |               Type                |
> Modifiers 
> ------------------------------+-----------------------------------+-----------
>  table_catalog                | information_schema.sql_identifier | 
>  table_schema                 | information_schema.sql_identifier | 
>  table_name                   | information_schema.sql_identifier | 
> 
> 
> -- 
> PostgreSQL - XMPP: jdrake@jabber.postgresql.org
>    Consulting, Development, Support, Training
>    503-667-4564 - http://www.commandprompt.com/
>    The PostgreSQL Company, serving since 1997
> 
> 
> -- 
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

--  Bruce Momjian  <bruce@momjian.us>        http://momjian.us EnterpriseDB
http://enterprisedb.com
 + If your life is a hard drive, Christ can be your backup. +


Re: Prepping to break every past release...

From
Simon Riggs
Date:
On Wed, 2009-03-04 at 12:32 -0800, Joshua D. Drake wrote:

> Something that continues to grind my teeth about our software is that we
> are horribly inconsistent with our system catalogs. Now I am fully and
> 100% aware that changing this will break things in user land but I want
> to do it anyway. In order to do that I believe we need to come up with a
> very loud, extremely verbose method of communicating to people that 8.5
> *will* break things. 

I agree strongly with your general point.

The most consistent negative feedback I receive about Postgres is that
we make minor changes from release to release that make it extremely
difficult to upgrade without re-testing the applications. So we write
great software, then make it difficult for people to upgrade to it.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support



Re: Prepping to break every past release...

From
Simon Riggs
Date:
On Thu, 2009-03-05 at 01:27 +0000, Andrew Gierth wrote:

> Now, of course, counting the upcoming 8.4 there have been three (and a
> bit - the original design predates 8.1, though it did anticipate some
> 8.1 features) new releases against which the original concept can be
> tested. And, guess what, nothing in those releases has even come close
> to invalidating the original design concept (as we knew all along).
> 
> If you're still not convinced of that fact, it would be possible to
> take the original design and update it to 8.4 following the original
> plan. But I'm not prepared to spend any time on this if the only
> result is going to be more argument.

I see the use for some more stable views.

Would it be better to publish them as an external project? That way we
can still use them for both old and new releases. Once the project takes
hold it might then be included in core - but that's not hugely important
if you can persuade people to include the project with the Windows
installer.

The problem with anything included in core is that we don't/can't
quickly fix design flaws, so even if we did get something in now it
might not do everything we want (and then we'd have to change it...).

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support



Re: Prepping to break every past release...

From
Andrew Gierth
Date:
>>>>> "Simon" == Simon Riggs <simon@2ndQuadrant.com> writes:
>> Now, of course, counting the upcoming 8.4 there have been three>> (and a bit - the original design predates 8.1,
thoughit did>> anticipate some 8.1 features) new releases against which the>> original concept can be tested. And,
guesswhat, nothing in those>> releases has even come close to invalidating the original design>> concept (as we knew
allalong).>> >> If you're still not convinced of that fact, it would be possible>> to take the original design and
updateit to 8.4 following the>> original plan. But I'm not prepared to spend any time on this if>> the only result is
goingto be more argument.
 
Simon> I see the use for some more stable views.
Simon> Would it be better to publish them as an external project?

They already are, though they are not complete and have not been
maintained much for 8.1 and later;
http://pgfoundry.org/projects/newsysviews/
Simon> That way we can still use them for both old and new releases.

It was always expected that they would be available on pgfoundry for
use on releases prior to their inclusion in core.
Simon> Once the project takes hold it might then be included in core

Speaking purely for myself, I'm not prepared to spend any time on it
without an assurance that it will go into core if the project goals
are reasonably met.

As for Tom's opinion that this is impossible, there's an old saying:
"The one who says it cannot be done should not interrupt the one who
is doing it."
Simon> The problem with anything included in core is that weSimon> don't/can't quickly fix design flaws, so even if we
didgetSimon> something in now it might not do everything we want (and thenSimon> we'd have to change it...).
 

I'm not proposing that it go into core quickly; and certainly not
before the design is properly reviewed, criticised, whatever.

-- 
Andrew.


Re: Prepping to break every past release...

From
David Fetter
Date:
On Tue, Mar 10, 2009 at 08:46:28AM +0000, Simon Riggs wrote:
> 
> On Thu, 2009-03-05 at 01:27 +0000, Andrew Gierth wrote:
> 
> > Now, of course, counting the upcoming 8.4 there have been three (and a
> > bit - the original design predates 8.1, though it did anticipate some
> > 8.1 features) new releases against which the original concept can be
> > tested. And, guess what, nothing in those releases has even come close
> > to invalidating the original design concept (as we knew all along).
> > 
> > If you're still not convinced of that fact, it would be possible to
> > take the original design and update it to 8.4 following the original
> > plan. But I'm not prepared to spend any time on this if the only
> > result is going to be more argument.
> 
> I see the use for some more stable views.
> 
> Would it be better to publish them as an external project?

It's been an external project, newsysviews, since before 8.1 came out.
I think it's time to bring it in from the cold.  Call the new schema
pg_sysviews, plop it in there, and call it done :)

Cheers,
David.
-- 
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


Re: Prepping to break every past release...

From
Simon Riggs
Date:
On Tue, 2009-03-10 at 07:28 -0700, David Fetter wrote:

> > Would it be better to publish them as an external project?
> 
> It's been an external project, newsysviews, since before 8.1 came out.
> I think it's time to bring it in from the cold.  Call the new schema
> pg_sysviews, plop it in there, and call it done :)

Yeh Andrew said. That I never noticed in the last 3+ years makes me
think there's not many people using it...

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support



Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Tue, 2009-03-10 at 15:02 +0000, Simon Riggs wrote:
> On Tue, 2009-03-10 at 07:28 -0700, David Fetter wrote:
> 
> > > Would it be better to publish them as an external project?
> > 
> > It's been an external project, newsysviews, since before 8.1 came out.
> > I think it's time to bring it in from the cold.  Call the new schema
> > pg_sysviews, plop it in there, and call it done :)
> 
> Yeh Andrew said. That I never noticed in the last 3+ years makes me
> think there's not many people using it...

Well I know of it and have never used it. Mainly because I didn't (and
still don't) really know what it does. From an outsider looking in, the
project is dead. The home page isn't updated (it talks about 8.1) and
the CVS repo appears to not have had a commit in 2 years.

How is anyone in the general community supposed to have any idea if this
is a good idea or not?

Sincerely,

Joshua D. Drake

-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
Andrew Gierth
Date:
>>>>> "Joshua" == Joshua D Drake <jd@commandprompt.com> writes:
> On Tue, 2009-03-10 at 15:02 +0000, Simon Riggs wrote:>> Yeh Andrew said. That I never noticed in the last 3+ years
makes>>me think there's not many people using it...
 

The fact that it never got beyond an early incomplete alpha version is
a big factor in that.
Joshua> Well I know of it and have never used it. Mainly because IJoshua> didn't (and still don't) really know what it
does.From anJoshua> outsider looking in, the project is dead. The home page isn'tJoshua> updated (it talks about 8.1)
andthe CVS repo appears to notJoshua> have had a commit in 2 years.
 

Other than some experiments in getting it to load on 8.2, there hasn't
been any serious work done on it since May 2005, which is when it was
presented (and shot down) on -hackers.

The lack of useful feedback from -hackers also means that the design
hasn't had much criticism, and therefore I don't regard the current
definitions, the naming conventions, etc., as being cast in stone;
which is another reason for people not to use it as it stands.

(The plan we had when we started on it was to produce an alpha version
as a proof-of-concept, present it on -hackers, get feedback, use that
to sort out the naming conventions and a definitive set of
definitions, and produce a beta version intended to be in the final
form.)

-- 
Andrew.


Re: Prepping to break every past release...

From
Dave Page
Date:
On Tue, Mar 10, 2009 at 5:23 PM, Andrew Gierth
<andrew@tao11.riddles.org.uk> wrote:

> Other than some experiments in getting it to load on 8.2, there hasn't
> been any serious work done on it since May 2005, which is when it was
> presented (and shot down) on -hackers.

If memory serves (and it may not - I'm practically brain dead from
reviewing a large pgAdmin patch all day) - much of the 'shooting down'
was at the suggestion that pgAdmin (and the like) should stop using
the catalogues directly and should use newsysviews instead. I still
maintain that'll never happen, but that doesn't mean that newsysviews
wouldn't be useful for other classes of user. Perhaps pgsql-general
would be a better place to poll.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com


Re: Prepping to break every past release...

From
Peter Eisentraut
Date:
Simon Riggs wrote:
> The most consistent negative feedback I receive about Postgres is that
> we make minor changes from release to release that make it extremely
> difficult to upgrade without re-testing the applications. So we write
> great software, then make it difficult for people to upgrade to it.

Then I would maintain that part of that makes the software great is that 
we have the ability to make incompatible changes once in a while, 
avoiding the accumulation of cruft.  We do maintain old releases for 5 
years as compensation.

I did propose a deprecation policy that would address your concern to 
some degree by issuing warnings in release N-1, so the testing after 
upgrade can be taken care of for the most part by hunting down these 
warnings while running the previous release.  That didn't receive 
universal support, but I think we should still look for a compromise in 
that area.

The argument against was that this would slow down PostgreSQL 
development too much.  And note that the one-year major release cycle of 
PostgreSQL is already pretty much the shortest one of any software of 
this complexity.

So everyone has different expectations, it seems.


Re: Prepping to break every past release...

From
Simon Riggs
Date:
On Wed, 2009-03-11 at 08:33 +0200, Peter Eisentraut wrote:

> Simon Riggs wrote:
> > The most consistent negative feedback I receive about Postgres is that
> > we make minor changes from release to release that make it extremely
> > difficult to upgrade without re-testing the applications. So we write
> > great software, then make it difficult for people to upgrade to it.
> 
> Then I would maintain that part of that makes the software great is that 
> we have the ability to make incompatible changes once in a while, 
> avoiding the accumulation of cruft.  We do maintain old releases for 5 
> years as compensation.

Please remember I'm just the messenger, passing on client feedback. It
hasn't ever been my way to act this way, but the reality is that
difficult upgrades make for more consulting income. The cost to the
client is much higher because of re-test costs, difficulty in supporting
applications across different sites running different PG releases and
general delay.

We're getting very good at doing upgrades now...

> I did propose a deprecation policy that would address your concern to 
> some degree by issuing warnings in release N-1, so the testing after 
> upgrade can be taken care of for the most part by hunting down these 
> warnings while running the previous release.  That didn't receive 
> universal support, but I think we should still look for a compromise in 
> that area.

I agree with the need for a deprecation policy or approach to this
issue.

I think that particular deprecation policy was too strong, but where
possible, it would be good to have a way to avoid niggly changes of
behaviour. We have done that sometimes, e.g. sort_mem is now a synonym
for work_mem, just not consistently. An example solution might be a
parameter that allowed us to act like the previous release in some
aspects. A parameter for every behaviour change would be bad because
that's just another minefield to cross.

The first step is to record incompatibilities as they occur and record
them somewhere, so that people can say "that'll break my app". Often the
first people hear about these things is when we compile the release
notes, which is far too late either to complain or to fix.

> The argument against was that this would slow down PostgreSQL 
> development too much.  And note that the one-year major release cycle of 
> PostgreSQL is already pretty much the shortest one of any software of 
> this complexity.

You know I would not agree to that.

-- Simon Riggs           www.2ndQuadrant.comPostgreSQL Training, Services and Support



Re: Prepping to break every past release...

From
"Joshua D. Drake"
Date:
On Wed, 2009-03-11 at 08:41 +0000, Simon Riggs wrote:
> On Wed, 2009-03-11 at 08:33 +0200, Peter Eisentraut wrote:

> The first step is to record incompatibilities as they occur and record
> them somewhere, so that people can say "that'll break my app". Often the
> first people hear about these things is when we compile the release
> notes, which is far too late either to complain or to fix.
> 

That is a simple modification of the release notes and something that
really should be done regardless of a deprecation policy.

Joshua D. Drake


-- 
PostgreSQL - XMPP: jdrake@jabber.postgresql.org  Consulting, Development, Support, Training  503-667-4564 -
http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997
 



Re: Prepping to break every past release...

From
decibel
Date:
On Mar 4, 2009, at 5:07 PM, Josh Berkus wrote:
>> Back on that track, I'd like to see a facility whereby we could  
>> provide an alias (or synonym, to use a nearby subject) columns and  
>> other objects. That would help to overcome naming glitches without  
>> breaking things quite so much.
>
> Believe it or not, a large PostgreSQL user in LA just buttonholed  
> me about that particular feature idea at SCALE.  So it might be  
> generally useful as well -- not just for the system catalogs, bug  
> to allow businesses with long-use databases to manage change over  
> time.


Yes, I think aliasing (especially at the table level) would be handy.

And +1 on reviving newsysviews, but of course I'm biased... ;P
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828




Re: Column Aliases WAS: Prepping to break every past release...

From
Josh Berkus
Date:
Jim,

> 
> Yes, I think aliasing (especially at the table level) would be handy.

We already *have* table aliases.  They're called "views".  What we don't 
have is column aliases.

However, for column aliases to be really useful for more than just 
application refactoring, we'd have to support calculated column aliases, 
which is much more complicated.

> 
> And +1 on reviving newsysviews, but of course I'm biased... ;P

Oh, right, forgot to credit you as well.  Sorry.

--Josh


Re: Prepping to break every past release...

From
Josh Berkus
Date:
All,

For anyone who cares, we have some unscientific results on the system 
views survey:

http://www.postgresql.org/community/survey.60

--Josh



Re: Column Aliases WAS: Prepping to break every past release...

From
decibel
Date:
On Mar 14, 2009, at 1:26 PM, Josh Berkus wrote:
>> Yes, I think aliasing (especially at the table level) would be handy.
>
> We already *have* table aliases.  They're called "views".  What we  
> don't have is column aliases.


A view is not the same as a table alias. Even if you take into  
account the new updatable views, you're still hosed if you add a  
column to the table. I see that being a lot more useful than a simple  
column alias (you're correct that we'd need to support calculated  
ones, which is indeed a lot harder).
-- 
Decibel!, aka Jim C. Nasby, Database Architect  decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828