Horton

Thread

  1. TODO: You can alter it, but you can't view it

    Josh Berkus <josh@agliodbs.com> — 2010-09-27T05:19:04Z

    All,

While working on some database maintenance, I was just tripped up by the 
fact that there is no good way to query reloptions for tables.  By "no 
good way" I mean "no way which does not involve UNNEST and regexps or 
procedural code".

This puts us in the wierd place that while one can ALTER various 
reloptions, one cannot check them to see if they *need* to be altered. 
That's a particularly bad situation given that changing reloptions 
requires a lock on the table (though less of one in 9.1).

I propose that we have an additional system view, pg_class_reloptions 
(or pg_table_reloptions if reloptions aren't relevant for views and 
indexes).  It would have the following columns:

relid
name	
setting_numeric
setting_boolean
setting_text

comments/objections/something I missed in the internal functions or 9.1 
patches which already does this?

-- 
                                   -- Josh Berkus
                                      PostgreSQL Experts Inc.
                                      http://www.pgexperts.com

  2. Re: TODO: You can alter it, but you can't view it

    Itagaki Takahiro <itagaki.takahiro@gmail.com> — 2010-09-27T05:30:02Z

    On Mon, Sep 27, 2010 at 2:19 PM, Josh Berkus <josh@agliodbs.com> wrote:
> While working on some database maintenance, I was just tripped up by the
> fact that there is no good way to query reloptions for tables.  By "no good
> way" I mean "no way which does not involve UNNEST and regexps or procedural
> code".

Can you use pg_options_to_table() for your purpose?

=# CREATE TABLE tbl (i integer) with (fillfactor = 70);
=# SELECT (pg_options_to_table(reloptions)).* FROM pg_class WHERE oid
= 'tbl'::regclass;
 option_name | option_value
-------------+--------------
 fillfactor  | 70

-- 
Itagaki Takahiro

  3. Re: TODO: You can alter it, but you can't view it

    Josh Berkus <josh@agliodbs.com> — 2010-09-27T07:39:55Z

    > Can you use pg_options_to_table() for your purpose?

Yes, thanks.  What version did that get added in?  Even for 9.0, that 
function doesn't seem to appear in the docs.

-- 
                                   -- Josh Berkus
                                      PostgreSQL Experts Inc.
                                      http://www.pgexperts.com

  4. Re: TODO: You can alter it, but you can't view it

    Itagaki Takahiro <itagaki.takahiro@gmail.com> — 2010-09-27T07:54:32Z

    On Mon, Sep 27, 2010 at 4:39 PM, Josh Berkus <josh@agliodbs.com> wrote:
>> Can you use pg_options_to_table() for your purpose?
>
> Yes, thanks.  What version did that get added in?  Even for 9.0, that
> function doesn't seem to appear in the docs.

I found it in 8.4 and newer versions. It might be an internal API
(for pg_dump?), but it'd be better to add documentation for it.

-- 
Itagaki Takahiro

  5. Re: TODO: You can alter it, but you can't view it

    Bernd Helmle <mailings@oopsware.de> — 2010-09-27T09:24:48Z

    
--On 27. September 2010 16:54:32 +0900 Itagaki Takahiro 
<itagaki.takahiro@gmail.com> wrote:

> I found it in 8.4 and newer versions. It might be an internal API
> (for pg_dump?), but it'd be better to add documentation for it.

Additionally we could extend pg_tables with an additional column? This 
would make the query more user-friendly, too.

-- 
Thanks

	Bernd

  6. Re: TODO: You can alter it, but you can't view it

    Bruce Momjian <bruce@momjian.us> — 2011-02-22T02:25:04Z

    Itagaki Takahiro wrote:
> On Mon, Sep 27, 2010 at 2:19 PM, Josh Berkus <josh@agliodbs.com> wrote:
> > While working on some database maintenance, I was just tripped up by the
> > fact that there is no good way to query reloptions for tables. ?By "no good
> > way" I mean "no way which does not involve UNNEST and regexps or procedural
> > code".
> 
> Can you use pg_options_to_table() for your purpose?
> 
> =# CREATE TABLE tbl (i integer) with (fillfactor = 70);
> =# SELECT (pg_options_to_table(reloptions)).* FROM pg_class WHERE oid
> = 'tbl'::regclass;
>  option_name | option_value
> -------------+--------------
>  fillfactor  | 70

Right now pg_options_to_table() is not documented.  Should it be?

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

  7. Re: TODO: You can alter it, but you can't view it

    Josh Berkus <josh@agliodbs.com> — 2011-02-23T03:54:16Z

    > Right now pg_options_to_table() is not documented.  Should it be?

Yes, I think so.


-- 
                                  -- Josh Berkus
                                     PostgreSQL Experts Inc.
                                     http://www.pgexperts.com

  8. Re: TODO: You can alter it, but you can't view it

    Bruce Momjian <bruce@momjian.us> — 2011-02-26T06:55:39Z

    Josh Berkus wrote:
> 
> > Right now pg_options_to_table() is not documented.  Should it be?
> 
> Yes, I think so.

Done, with the attached, applied patch.

-- 
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +