Re: [HACKERS] TODO: You can alter it, but you can't view it
bruce wrote:
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.
Oh, here is an example usage:
test= select pg_options_to_table(reloptions) from pg_class;
pg_options_to_table
--
(fillfactor,50)
(autovacuum_freeze_table_age,10)
(2 rows)
It also works for pg_attribute.attoptions, so I documented that too.
I also fixed the documented return type in my previous patch.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index edb7795..9c8e19f 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** SELECT pg_type_is_visible('myschema.widg
*** 13385,13392
/row
row
entryliteralfunctionpg_options_to_table(parameterreloptions/parameter)/function/literal/entry
!entrytypename, option/type/entry
!entryget the set of option name/value pairs from structnamepg_class/.structfieldreloptions//entry
/row
row
entryliteralfunctionpg_tablespace_databases(parametertablespace_oid/parameter)/function/literal/entry
--- 13385,13392
/row
row
entryliteralfunctionpg_options_to_table(parameterreloptions/parameter)/function/literal/entry
!entrytypesetof record/type/entry
!entryget the set of storage option name/value pairs//entry
/row
row
entryliteralfunctionpg_tablespace_databases(parametertablespace_oid/parameter)/function/literal/entry
*** SELECT pg_type_is_visible('myschema.widg
*** 13484,13492
/para
para
!functionpg_options_to_table/function returns the set of option
name/value pairs when passed
!structnamepg_class/.structfieldreloptions/.
/para
para
--- 13484,13493
/para
para
!functionpg_options_to_table/function returns the set of storage option
name/value pairs when passed
!structnamepg_class/.structfieldreloptions/ or
!structnamepg_attribute/.structfieldattoptions/.
/para
para
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: You can alter it, but you can't view it
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 br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 736eb67..c620142 100644
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
*** SELECT pg_type_is_visible('myschema.widg
*** 13244,13249
--- 13244,13253
/indexterm
indexterm
+ primarypg_options_to_table/primary
+/indexterm
+
+indexterm
primarypg_tablespace_databases/primary
/indexterm
*** SELECT pg_type_is_visible('myschema.widg
*** 13380,13385
--- 13384,13394
entryget underlying commandSELECT/command command for view/entry
/row
row
+entryliteralfunctionpg_options_to_table(parameterreloptions/parameter)/function/literal/entry
+entrytypename, option/type/entry
+entryget the set of option name/value pairs from structnamepg_class/.structfieldreloptions//entry
+ /row
+ row
entryliteralfunctionpg_tablespace_databases(parametertablespace_oid/parameter)/function/literal/entry
entrytypesetof oid/type/entry
entryget the set of database OIDs that have objects in the tablespace/entry
*** SELECT pg_type_is_visible('myschema.widg
*** 13475,13480
--- 13484,13495
/para
para
+functionpg_options_to_table/function returns the set of option
+name/value pairs when passed
+structnamepg_class/.structfieldreloptions/.
+ /para
+
+ para
functionpg_tablespace_databases/function allows a tablespace to be
examined. It returns the set of OIDs of databases that have objects stored
in the tablespace. If this function returns any rows, the tablespace is not
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: You can alter it, but you can't view it
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: You can alter it, but you can't view it
Itagaki Takahiro wrote: On Mon, Sep 27, 2010 at 2:19 PM, Josh Berkus j...@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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: You can alter it, but you can't view it
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: You can alter it, but you can't view it
On Mon, Sep 27, 2010 at 4:39 PM, Josh Berkus j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: You can alter it, but you can't view it
--On 27. September 2010 16:54:32 +0900 Itagaki Takahiro itagaki.takah...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] TODO: You can alter it, but you can't view it
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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: You can alter it, but you can't view it
On Mon, Sep 27, 2010 at 2:19 PM, Josh Berkus j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
