Horton

Thread

  1. List schema contents

    Jim C. Nasby <jnasby@pervasive.com> — 2006-06-09T19:33:57Z

    Currently, the only way to get a listing of tables in a schema via psql
is to modify your search_path, which is both non-intuitive and a PITA.
You can do \d schemaname., but that's the equivalent of

set search_path = schemaname
\d *

I'd like to propose that the behavior of \d schemaname. be changed to
match

set search_path = schemaname
\d

That avoids issues with table name conflicts that could arise if the
trailing . wasn't required. And the old behavior is still available as
\d schemaname.*

One problem I see is that this will break the ability to search for
tablename. via regex. Since that's easy to do via tablename?, I don't
think it's a big deal, unless someone has better ideas on how to
indicate we want to list something for a specific schema.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  2. Re: List schema contents

    Martijn van Oosterhout <kleptog@svana.org> — 2006-06-09T19:47:00Z

    On Fri, Jun 09, 2006 at 02:33:57PM -0500, Jim C. Nasby wrote:
> Currently, the only way to get a listing of tables in a schema via psql
> is to modify your search_path, which is both non-intuitive and a PITA.
> You can do \d schemaname., but that's the equivalent of

Isn't this \dt schemaname.*?

The only irritating thing is that is only displays table, not other
objects...

Have a nice day,
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

  3. Re: List schema contents

    Neil Conway <neilc@samurai.com> — 2006-06-09T19:52:52Z

    On Fri, 2006-06-09 at 14:33 -0500, Jim C. Nasby wrote:
> Currently, the only way to get a listing of tables in a schema via psql
> is to modify your search_path, which is both non-intuitive and a PITA.

I've griped about psql's limited support for schemas in the past:

http://archives.postgresql.org/pgsql-hackers/2004-10/msg00989.php
http://archives.postgresql.org/pgsql-hackers/2004-11/msg00014.php

I never actually got around to making any behavioral changes, but I
agree there is certainly room for improvement.

(The fact that archives.p.o can't properly link between threads that
cross month boundaries is pretty annoying...)

-Neil

  4. Re: List schema contents

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-06-09T20:20:16Z

    "Jim C. Nasby" <jnasby@pervasive.com> writes:
> I'd like to propose that the behavior of \d schemaname. be changed to
> match

> set search_path = schemaname
> \d

I'm not sure what your reasoning is here, but AFAICS this would move the
behavior away from what you say you want.  What exactly have you got
in mind, and why?  In particular, why do you think that "\d schemaname."
doesn't already produce exactly what you asked for, namely a list of the
tables in that schema?  The above would *not* produce such a list
(counterexample: temp tables).

> One problem I see is that this will break the ability to search for
> tablename. via regex.

You are aware of the double-quoting option in \d search patterns, no?

			regards, tom lane

  5. archive threads across months (was Re: [HACKERS] List schema contents)

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-06-09T20:22:18Z

    Neil Conway <neilc@samurai.com> writes:
> (The fact that archives.p.o can't properly link between threads that
> cross month boundaries is pretty annoying...)

Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
on -www have an idea how to fix it?

			regards, tom lane

  6. Re: List schema contents

    Jim C. Nasby <jnasby@pervasive.com> — 2006-06-09T20:27:41Z

    On Fri, Jun 09, 2006 at 04:20:16PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > I'd like to propose that the behavior of \d schemaname. be changed to
> > match
> 
> > set search_path = schemaname
> > \d
> 
> I'm not sure what your reasoning is here, but AFAICS this would move the
> behavior away from what you say you want.  What exactly have you got
> in mind, and why?  In particular, why do you think that "\d schemaname."
> doesn't already produce exactly what you asked for, namely a list of the
> tables in that schema?  The above would *not* produce such a list
> (counterexample: temp tables).
 
What I'm looking for is what "\d" provides you, only limited to a
specific schema. "\d information_schema." (for example) doesn't provide
that; it provides the details for every table/view in
information_schema. Changing the search path does what I want, but is a
pain:

decibel=# set search_path=information_schema;
SET
decibel=# \d
                            List of relations
       Schema       |              Name               | Type  |   Owner   
--------------------+---------------------------------+-------+-----------
 information_schema | applicable_roles                | view  | postgres8
 information_schema | check_constraints               | view  | postgres8
 information_schema | column_domain_usage             | view  | postgres8
...
 information_schema | usage_privileges                | view  | postgres8
 information_schema | view_column_usage               | view  | postgres8
 information_schema | view_table_usage                | view  | postgres8
 information_schema | views                           | view  | postgres8
(40 rows)

decibel=# 

> > One problem I see is that this will break the ability to search for
> > tablename. via regex.
> 
> You are aware of the double-quoting option in \d search patterns, no?

No, but I'm not really sure how that would help...
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  7. Re: archive threads across months (was Re: [HACKERS] List

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-09T20:33:17Z

    Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > (The fact that archives.p.o can't properly link between threads that
> > cross month boundaries is pretty annoying...)
> 
> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> on -www have an idea how to fix it?

I am also having problems that the date sorting often has a few entries
at the end of out of order.  I think that needs to be fixed too.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  8. Re: List schema contents

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-06-09T20:55:07Z

    "Jim C. Nasby" <jnasby@pervasive.com> writes:
> What I'm looking for is what "\d" provides you, only limited to a
> specific schema. "\d information_schema." (for example) doesn't provide
> that; it provides the details for every table/view in
> information_schema.

What you're looking for is \dt, or perhaps \dtsv or one of those forms.

I'd be the first to agree that the behavior of \d isn't particularly
orthogonal, but it's not the pattern language that's the problem, it's
the command itself.

			regards, tom lane

  9. Re: List schema contents

    Jim C. Nasby <jnasby@pervasive.com> — 2006-06-09T21:00:01Z

    On Fri, Jun 09, 2006 at 04:55:07PM -0400, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
> > What I'm looking for is what "\d" provides you, only limited to a
> > specific schema. "\d information_schema." (for example) doesn't provide
> > that; it provides the details for every table/view in
> > information_schema.
> 
> What you're looking for is \dt, or perhaps \dtsv or one of those forms.

\dtsv produces exactly what I'd want/expect.

> I'd be the first to agree that the behavior of \d isn't particularly
> orthogonal, but it's not the pattern language that's the problem, it's
> the command itself.

Perhaps \d without an argument should just do whatever \dtsv does?
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  10. Re: List schema contents

    Tom Lane <tgl@sss.pgh.pa.us> — 2006-06-09T21:08:40Z

    "Jim C. Nasby" <jnasby@pervasive.com> writes:
> On Fri, Jun 09, 2006 at 04:55:07PM -0400, Tom Lane wrote:
>> I'd be the first to agree that the behavior of \d isn't particularly
>> orthogonal, but it's not the pattern language that's the problem, it's
>> the command itself.

> Perhaps \d without an argument should just do whatever \dtsv does?

That's exactly what it does ... without an argument.  The
non-orthogonality is that adding an argument changes the printout style,
instead of just determining which objects are displayed.  See the psql
man page:

         Note: If \d is used without a pattern argument, it is
         equivalent to \dtvs which will show a list of all 
         tables, views, and sequences. This is purely a convenience measure. 

			regards, tom lane

  11. Re: archive threads across months (was Re: [HACKERS]

    Marc G. Fournier <scrappy@postgresql.org> — 2006-06-10T05:20:05Z

    On Fri, 9 Jun 2006, Tom Lane wrote:

> Neil Conway <neilc@samurai.com> writes:
>> (The fact that archives.p.o can't properly link between threads that
>> cross month boundaries is pretty annoying...)
>
> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> on -www have an idea how to fix it?

Someone that knows mhonarc better then I might know of a way, but, I don't 
believe cross-month linking is actually possible ...

majordomo stores archives by month in mbox format ... mhonarc processes 
each month and deposits it into a directory YYYY-MM ... without merging 
all of the mbox files into one huge archive file, and eliminating the 
YYYY-MM subdirectories, I don't believe that you could do this ...

But, as I said, someone with more knowledge about mhonarc may know a way 
of doing this ... ?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org                              MSN . scrappy@hub.org
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

  12. Re: archive threads across months (was Re: [HACKERS]

    Marc G. Fournier <scrappy@postgresql.org> — 2006-06-10T05:20:27Z

    On Fri, 9 Jun 2006, Bruce Momjian wrote:

> Tom Lane wrote:
>> Neil Conway <neilc@samurai.com> writes:
>>> (The fact that archives.p.o can't properly link between threads that
>>> cross month boundaries is pretty annoying...)
>>
>> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
>> on -www have an idea how to fix it?
>
> I am also having problems that the date sorting often has a few entries
> at the end of out of order.  I think that needs to be fixed too.

please provide a sample of what you are talking about ... :)

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org                              MSN . scrappy@hub.org
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

  13. Re: archive threads across months (was Re: [HACKERS]

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-10T12:48:18Z

    Marc G. Fournier wrote:
> On Fri, 9 Jun 2006, Bruce Momjian wrote:
> 
> > Tom Lane wrote:
> >> Neil Conway <neilc@samurai.com> writes:
> >>> (The fact that archives.p.o can't properly link between threads that
> >>> cross month boundaries is pretty annoying...)
> >>
> >> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> >> on -www have an idea how to fix it?
> >
> > I am also having problems that the date sorting often has a few entries
> > at the end of out of order.  I think that needs to be fixed too.
> 
> please provide a sample of what you are talking about ... :)

Sure, search for "misnomer" and sorty by date.  Look at the last page.
You will see 2005 entries that should have appeared earlier.

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  14. Re: archive threads across months (was Re: [HACKERS]

    Marc G. Fournier <scrappy@postgresql.org> — 2006-06-10T13:52:58Z

    On Sat, 10 Jun 2006, Bruce Momjian wrote:

> Marc G. Fournier wrote:
>> On Fri, 9 Jun 2006, Bruce Momjian wrote:
>>
>>> Tom Lane wrote:
>>>> Neil Conway <neilc@samurai.com> writes:
>>>>> (The fact that archives.p.o can't properly link between threads that
>>>>> cross month boundaries is pretty annoying...)
>>>>
>>>> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
>>>> on -www have an idea how to fix it?
>>>
>>> I am also having problems that the date sorting often has a few entries
>>> at the end of out of order.  I think that needs to be fixed too.
>>
>> please provide a sample of what you are talking about ... :)
>
> Sure, search for "misnomer" and sorty by date.  Look at the last page.
> You will see 2005 entries that should have appeared earlier.

Wait, you are talking Search results, not ordering in the archives 
themselves ... can't help you with that one, sorry ...

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org                              MSN . scrappy@hub.org
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

  15. Re: archive threads across months (was Re: [HACKERS]

    Jim C. Nasby <jnasby@pervasive.com> — 2006-06-10T17:57:35Z

    On Jun 10, 2006, at 12:20 AM, Marc G. Fournier wrote:
> On Fri, 9 Jun 2006, Tom Lane wrote:
>
>> Neil Conway <neilc@samurai.com> writes:
>>> (The fact that archives.p.o can't properly link between threads that
>>> cross month boundaries is pretty annoying...)
>>
>> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
>> on -www have an idea how to fix it?
>
> Someone that knows mhonarc better then I might know of a way, but,  
> I don't believe cross-month linking is actually possible ...
>
> majordomo stores archives by month in mbox format ... mhonarc  
> processes each month and deposits it into a directory YYYY-MM ...  
> without merging all of the mbox files into one huge archive file,  
> and eliminating the YYYY-MM subdirectories, I don't believe that  
> you could do this ...
>
> But, as I said, someone with more knowledge about mhonarc may know  
> a way of doing this ... ?

Are we wed to mhonarc? Is there another archiving tool we could use?
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

  16. Re: archive threads across months (was Re: [HACKERS]

    Marc G. Fournier <scrappy@postgresql.org> — 2006-06-10T18:09:32Z

    On Sat, 10 Jun 2006, Jim Nasby wrote:

> On Jun 10, 2006, at 12:20 AM, Marc G. Fournier wrote:
>> On Fri, 9 Jun 2006, Tom Lane wrote:
>> 
>>> Neil Conway <neilc@samurai.com> writes:
>>>> (The fact that archives.p.o can't properly link between threads that
>>>> cross month boundaries is pretty annoying...)
>>> 
>>> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
>>> on -www have an idea how to fix it?
>> 
>> Someone that knows mhonarc better then I might know of a way, but, I don't 
>> believe cross-month linking is actually possible ...
>> 
>> majordomo stores archives by month in mbox format ... mhonarc processes 
>> each month and deposits it into a directory YYYY-MM ... without merging all 
>> of the mbox files into one huge archive file, and eliminating the YYYY-MM 
>> subdirectories, I don't believe that you could do this ...
>> 
>> But, as I said, someone with more knowledge about mhonarc may know a way of 
>> doing this ... ?
>
> Are we wed to mhonarc? Is there another archiving tool we could use?

I'll never say we're *wed* to anything ... do you know of another 
archiving tool?

I think the first question is are there any other ones that offer the 
functionality that Tom is asking for ... ?

----
Marc G. Fournier           Hub.Org Networking Services (http://www.hub.org)
Email . scrappy@hub.org                              MSN . scrappy@hub.org
Yahoo . yscrappy               Skype: hub.org        ICQ . 7615664

  17. Re: archive threads across months (was Re: [HACKERS]

    John Hansen <john@geeknet.com.au> — 2006-06-15T01:27:43Z

    On Sat, 2006-06-10 at 08:48 -0400, Bruce Momjian wrote:
> Marc G. Fournier wrote:
> > On Fri, 9 Jun 2006, Bruce Momjian wrote:
> > 
> > > Tom Lane wrote:
> > >> Neil Conway <neilc@samurai.com> writes:
> > >>> (The fact that archives.p.o can't properly link between threads that
> > >>> cross month boundaries is pretty annoying...)
> > >>
> > >> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> > >> on -www have an idea how to fix it?
> > >
> > > I am also having problems that the date sorting often has a few entries
> > > at the end of out of order.  I think that needs to be fixed too.
> > 
> > please provide a sample of what you are talking about ... :)
> 
> Sure, search for "misnomer" and sorty by date.  Look at the last page.
> You will see 2005 entries that should have appeared earlier.
> 

Thats due to a bug in the search engine, not the archives themselves.

... John

  18. Re: archive threads across months (was Re: [HACKERS]

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-15T01:34:08Z

    John Hansen wrote:
> On Sat, 2006-06-10 at 08:48 -0400, Bruce Momjian wrote:
> > Marc G. Fournier wrote:
> > > On Fri, 9 Jun 2006, Bruce Momjian wrote:
> > > 
> > > > Tom Lane wrote:
> > > >> Neil Conway <neilc@samurai.com> writes:
> > > >>> (The fact that archives.p.o can't properly link between threads that
> > > >>> cross month boundaries is pretty annoying...)
> > > >>
> > > >> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> > > >> on -www have an idea how to fix it?
> > > >
> > > > I am also having problems that the date sorting often has a few entries
> > > > at the end of out of order.  I think that needs to be fixed too.
> > > 
> > > please provide a sample of what you are talking about ... :)
> > 
> > Sure, search for "misnomer" and sorty by date.  Look at the last page.
> > You will see 2005 entries that should have appeared earlier.
> > 
> 
> Thats due to a bug in the search engine, not the archives themselves.

OK, how does it get fixed?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  19. Re: archive threads across months (was Re: [HACKERS]

    John Hansen <john@geeknet.com.au> — 2006-06-15T01:47:34Z

    On Wed, 2006-06-14 at 21:34 -0400, Bruce Momjian wrote:
> John Hansen wrote:
> > On Sat, 2006-06-10 at 08:48 -0400, Bruce Momjian wrote:
> > > Marc G. Fournier wrote:
> > > > On Fri, 9 Jun 2006, Bruce Momjian wrote:
> > > > 
> > > > > Tom Lane wrote:
> > > > >> Neil Conway <neilc@samurai.com> writes:
> > > > >>> (The fact that archives.p.o can't properly link between threads that
> > > > >>> cross month boundaries is pretty annoying...)
> > > > >>
> > > > >> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> > > > >> on -www have an idea how to fix it?
> > > > >
> > > > > I am also having problems that the date sorting often has a few entries
> > > > > at the end of out of order.  I think that needs to be fixed too.
> > > > 
> > > > please provide a sample of what you are talking about ... :)
> > > 
> > > Sure, search for "misnomer" and sorty by date.  Look at the last page.
> > > You will see 2005 entries that should have appeared earlier.
> > > 
> > 
> > Thats due to a bug in the search engine, not the archives themselves.
> 
> OK, how does it get fixed?

Someone needs to fix the ASPseek source...

>

  20. Re: archive threads across months (was Re: [HACKERS]

    Bruce Momjian <pgman@candle.pha.pa.us> — 2006-06-15T02:01:46Z

    John Hansen wrote:
> On Wed, 2006-06-14 at 21:34 -0400, Bruce Momjian wrote:
> > John Hansen wrote:
> > > On Sat, 2006-06-10 at 08:48 -0400, Bruce Momjian wrote:
> > > > Marc G. Fournier wrote:
> > > > > On Fri, 9 Jun 2006, Bruce Momjian wrote:
> > > > > 
> > > > > > Tom Lane wrote:
> > > > > >> Neil Conway <neilc@samurai.com> writes:
> > > > > >>> (The fact that archives.p.o can't properly link between threads that
> > > > > >>> cross month boundaries is pretty annoying...)
> > > > > >>
> > > > > >> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> > > > > >> on -www have an idea how to fix it?
> > > > > >
> > > > > > I am also having problems that the date sorting often has a few entries
> > > > > > at the end of out of order.  I think that needs to be fixed too.
> > > > > 
> > > > > please provide a sample of what you are talking about ... :)
> > > > 
> > > > Sure, search for "misnomer" and sorty by date.  Look at the last page.
> > > > You will see 2005 entries that should have appeared earlier.
> > > > 
> > > 
> > > Thats due to a bug in the search engine, not the archives themselves.
> > 
> > OK, how does it get fixed?
> 
> Someone needs to fix the ASPseek source...

Have we submitted a bug report to them?

-- 
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

  21. Re: archive threads across months (was Re: [HACKERS]

    John Hansen <john@geeknet.com.au> — 2006-06-15T02:04:21Z

    On Wed, 2006-06-14 at 22:01 -0400, Bruce Momjian wrote:
> John Hansen wrote:
> > On Wed, 2006-06-14 at 21:34 -0400, Bruce Momjian wrote:
> > > John Hansen wrote:
> > > > On Sat, 2006-06-10 at 08:48 -0400, Bruce Momjian wrote:
> > > > > Marc G. Fournier wrote:
> > > > > > On Fri, 9 Jun 2006, Bruce Momjian wrote:
> > > > > > 
> > > > > > > Tom Lane wrote:
> > > > > > >> Neil Conway <neilc@samurai.com> writes:
> > > > > > >>> (The fact that archives.p.o can't properly link between threads that
> > > > > > >>> cross month boundaries is pretty annoying...)
> > > > > > >>
> > > > > > >> Indeed, I was just annoyed by that (again) a few minutes ago.  Anyone
> > > > > > >> on -www have an idea how to fix it?
> > > > > > >
> > > > > > > I am also having problems that the date sorting often has a few entries
> > > > > > > at the end of out of order.  I think that needs to be fixed too.
> > > > > > 
> > > > > > please provide a sample of what you are talking about ... :)
> > > > > 
> > > > > Sure, search for "misnomer" and sorty by date.  Look at the last page.
> > > > > You will see 2005 entries that should have appeared earlier.
> > > > > 
> > > > 
> > > > Thats due to a bug in the search engine, not the archives themselves.
> > > 
> > > OK, how does it get fixed?
> > 
> > Someone needs to fix the ASPseek source...
> 
> Have we submitted a bug report to them?
> 

The project is more or less dead, only the userbase still exists.
And the version we're running is heavily modified.

I will get around to fixing this, I just don't have the time at present.

... John