Обсуждение: Clarify "allow_system_table_mods"

On 04/25/2016 09:30 AM, Melvin Davidson wrote:
> I need clarification on allow_system_table_mods parameter
> Per the documentation:
> *Allows modification of the structure of system tables.* This is used by
> initdb. This parameter can only be set at server start.

I am reading the above as meaning that the parameter only takes affect
if it has been set in the conf file and you then initdb a new database
cluster. So is that what you did or did you add the parameter to a
previously initialized cluster conf file?

>
> However, attempting to modify pg_class to add another column fails with
> "STATUS_ACCESS_VIOLATION" as below.
> So either only certain system catalogs may be changed, or only certain
> types of structure changes are allowed.
> eg: add indexes, triggers
>
> Windows 10
> PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit
>
> Current user is postgres
> allow_system_table_mods = on
>
> ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
> current_timestamp;
>
> Fails with:
>
> 2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
> COLUMN relcreated timestamp DEFAULT current_timestamp;
> 2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated
> by exception 0xC0000005
> 2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
> pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
> current_timestamp;
> 2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
> 2016-04-25 12:08:14 EDT LOG:  terminating any other active server processes
>
>  From ntstatus.h
> *#define STATUS_ACCESS_VIOLATION          ((NTSTATUS)0xC0000005L)    //
> winnt*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

On 04/25/2016 09:30 AM, Melvin Davidson wrote:
> I need clarification on allow_system_table_mods parameter
> Per the documentation:
> *Allows modification of the structure of system tables.* This is used by
> initdb. This parameter can only be set at server start.

A millisecond after hitting enter I realized my previous answer did not
make sense as postgresql.conf is set up by initdb. Then I found this:

http://www.postgresql.org/docs/9.2/static/runtime-config-short.html

-O     allow_system_table_mods = on

So use that option when doing initdb?

>
> However, attempting to modify pg_class to add another column fails with
> "STATUS_ACCESS_VIOLATION" as below.
> So either only certain system catalogs may be changed, or only certain
> types of structure changes are allowed.
> eg: add indexes, triggers
>
> Windows 10
> PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit
>
> Current user is postgres
> allow_system_table_mods = on
>
> ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
> current_timestamp;
>
> Fails with:
>
> 2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD
> COLUMN relcreated timestamp DEFAULT current_timestamp;
> 2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated
> by exception 0xC0000005
> 2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE
> pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT
> current_timestamp;
> 2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a
> description of the hexadecimal value.
> 2016-04-25 12:08:14 EDT LOG:  terminating any other active server processes
>
>  From ntstatus.h
> *#define STATUS_ACCESS_VIOLATION          ((NTSTATUS)0xC0000005L)    //
> winnt*
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.


--
Adrian Klaver
adrian.klaver@aklaver.com

I need clarification on allow_system_table_mods parameter
Per the documentation:
Allows modification of the structure of system tables. This is used by initdb. This parameter can only be set at server start.

However, attempting to modify pg_class to add another column fails with "STATUS_ACCESS_VIOLATION" as below.
So either only certain system catalogs may be changed, or only certain types of structure changes are allowed.
eg: add indexes, triggers

Windows 10
PostgreSQL 9.2.15, compiled by Visual C++ build 1600, 32-bit

Current user is postgres
allow_system_table_mods = on

ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT current_timestamp;

Fails with:

2016-04-25 12:08:00 EDT STATEMENT:  ALTER TABLE pg_catalog.pgclass ADD COLUMN relcreated timestamp DEFAULT current_timestamp;
2016-04-25 12:08:14 EDT LOG:  server process (PID 7760) was terminated by exception 0xC0000005
2016-04-25 12:08:14 EDT DETAIL:  Failed process was running: ALTER TABLE pg_catalog.pg_class ADD COLUMN relcreated timestamp DEFAULT current_timestamp;
2016-04-25 12:08:14 EDT HINT:  See C include file "ntstatus.h" for a description of the hexadecimal value.
2016-04-25 12:08:14 EDT LOG:  terminating any other active server processes

From ntstatus.h
#define STATUS_ACCESS_VIOLATION          ((NTSTATUS)0xC0000005L)    // winnt

David, Melvin,

* David G. Johnston (david.g.johnston@gmail.com) wrote:
> On Monday, April 25, 2016, Melvin Davidson <melvin6925@gmail.com> wrote:
> > I need clarification on allow_system_table_mods parameter
> > Per the documentation:
> > *Allows modification of the structure of system tables.* This is used by
> > initdb. This parameter can only be set at server start.
> >
> > However, attempting to modify pg_class to add another column fails with
> > "STATUS_ACCESS_VIOLATION" as below.
> > So either only certain system catalogs may be changed, or only certain
> > types of structure changes are allowed.

There might be some things about system catalogs you can modify, but
generally speaking, you can't add a column or otherwise change the
structure.  System catalogs are represented in memory by C structures
(and more), so it's not too surprising that adding a column causes a
crash.

> If it's going to prevent the action it should be able to do so without
> crashing the server..and I doubt it's intended to be opinionated aside from
> covering all DDL.  What little there is on Google seems to support this.

Sorry, but as a superuser, there's a lot of things you can do to crash
the server, this is just one way.  The effort required to prevent
anything bad from happening when a user is running as a superuser is far
from trivial.

In short, no, you can't just add a column to pg_class via SQL, and I
don't think we're going to be very interested in trying to "fix" such
cases.

Thanks!

Stephen

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Monday, April 25, 2016, Melvin Davidson <melvin6925@gmail.com> wrote:
>> I need clarification on allow_system_table_mods parameter

> If it's going to prevent the action it should be able to do so without
> crashing the server..and I doubt it's intended to be opinionated aside from
> covering all DDL.  What little there is on Google seems to support this.

You've got that backwards.  Turning allow_system_table_mods on simply
disables a bunch of safety/permissions checks, allowing incautious
superusers to break their databases by changing system catalog structure.
It does not promise to make things work that are incapable of being
supported by the underlying C code.  For the most part, no change in the
rowtype of any system catalog is going to work; the C code has compiled-in
notions about the numbers and types of the columns in each catalog.

allow_system_table_mods only exists so that initdb can do some setup steps
that would otherwise be inconvenient to manage.  It's not intended to be
used by average mortals; and that's the reason for the lack of
documentation, as well as the lack of concern about whether you can crash
the system with it.

            regards, tom lane

* Melvin Davidson (melvin6925@gmail.com) wrote:
> Then could you please clarify exactly what structural mods are permitted by
> *"*
> *Allows modification of the structure of system tables" ?*

I would say, in short, those modifications which are implemented by PG
developers who know what's safe to do with the catalog tables.

Beyond that, I'm afriad you'd need to read the source code.  I don't
know offhand the complete set of "what's safe to do", though it's
probably a pretty short list and certainly doesn't include adding
columns.

Ultimately, that switch isn't for end users to use to modify the
catalogs.  If you'd like to modify the structure of pg_class, you would
start by looking at src/include/catalog/pg_class.h, though there's quite
a few other bits that would need to change too.

Thanks!

Stephen

Melvin,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> So in essence "*Allows modification of the structure of system tables" does
> NOT allow _structural_ changes (probably only dml changes)
> and the documentation should be changed to clarify.

That would imply that other changes are acceptable.  That is not the
case.  Certain DML changes could cause crashes too.

The documentation is pretty clear that this option is for initdb, and
not anyone else.  I'm unconvinced that we need anything more.

Thanks!

Stephen

Melvin,

* Melvin Davidson (melvin6925@gmail.com) wrote:
> So in essence "*Allows modification of the structure of system tables" does
> NOT allow _structural_ changes (probably only dml changes)
> and the documentation should be changed to clarify.

That would imply that other changes are acceptable.  That is not the
case.  Certain DML changes could cause crashes too.

The documentation is pretty clear that this option is for initdb, and
not anyone else.  I'm unconvinced that we need anything more.

* Melvin Davidson (melvin6925@gmail.com) wrote:
> Hmmm, if you go back a few comments, you will note that per initdb --help
> there is no such option available.

It's not an option *to* initdb, it's an option which is used *by*
initdb.

I'm afraid I'm done with this particular discussion.  Hopefully it's
purpose is now clear and you understand a bit better what is required to
actually add a column to pg_class.

Thanks!

Stephen

On 04/25/2016 05:29 PM, Stephen Frost wrote:
> * Melvin Davidson (melvin6925@gmail.com) wrote:
>> Hmmm, if you go back a few comments, you will note that per initdb --help
>> there is no such option available.
>
> It's not an option *to* initdb, it's an option which is used *by*
> initdb.

That really did not clear things up:) Does it mean that you can
pre-populate the $DATA directory with a postgresql.conf that has
allow_system_table_mods set to on and initdb will pick it up?

Personally, I think tampering with the system catalogs is foolish. Still
if you have documentation for something(even if it is a foot gun) it
should be understandable. If the intent is for end users/dba's not use
these options I would say take then out of the user docs and put them in
the developer Wiki section.

>
> I'm afraid I'm done with this particular discussion.  Hopefully it's
> purpose is now clear and you understand a bit better what is required to
> actually add a column to pg_class.
>
> Thanks!
>
> Stephen
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, Apr 25, 2016 at 8:41 PM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 04/25/2016 05:29 PM, Stephen Frost wrote:

* Melvin Davidson (melvin6925@gmail.com) wrote:

Hmmm, if you go back a few comments, you will note that per initdb --help
there is no such option available.

It's not an option *to* initdb, it's an option which is used *by*
initdb.

That really did not clear things up:) Does it mean that you can pre-populate the $DATA directory with a postgresql.conf that has allow_system_table_mods set to on and initdb will pick it up?

I am completely in agreement with what Adrian said.
I only started this conversation because my enhancement request to add relcreated to pg_class seemingly was going nowhere. I was told if I wanted to do that, I should write the patch myself
and submit it, but I am not a developer, so that option is out.

I also pointed to a "Customer Feedback" url that has apparently been active for quite awhile, but the developers deny knowledge of that.

So if we are going to use this list as a medium for enhancement requests, I feel it is only fair that
1. A formal committee to review all such requests be created.
2. That committee should meet (or review) periodically all enhancement requests.
3. If an enhancement request is approved, then the list should be updated with it, or else
   if rejected, then a reason (such as dangerous code or negative performance) should be stated.
4. Using, "it won't work in all cases" is not a reason for rejection. Think of positive benefits!
  

Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 04/25/2016 05:29 PM, Stephen Frost wrote:
>> It's not an option *to* initdb, it's an option which is used *by*
>> initdb.

> That really did not clear things up:) Does it mean that you can
> pre-populate the $DATA directory with a postgresql.conf that has
> allow_system_table_mods set to on and initdb will pick it up?

No, initdb will complain if you point it at a nonempty directory...

The way that initdb uses this option is to pass it as a command-line
switch to the standalone backend it invokes.

> Personally, I think tampering with the system catalogs is foolish. Still
> if you have documentation for something(even if it is a foot gun) it
> should be understandable. If the intent is for end users/dba's not use
> these options I would say take then out of the user docs and put them in
> the developer Wiki section.

It's already in a section titled "Developer Options", with the heading

    The following parameters are intended for work on the PostgreSQL
    source code, and in some cases to assist with recovery of severely
    damaged databases. There should be no reason to use them on a
    production database.

I'm not sure what else we should do, short of writing "HERE BE DRAGONS"
set in blackletter type.

(Having said that, we could change "This is used by initdb." to "This
is meant only for use by initdb".  But I don't know that that really
would discourage anybody.)

            regards, tom lane

Hi all,
Is there a query I can run that will scan through all the tables of a
database and give me a list of all tables without a primary key? Im not
having any luck with this.

Thanks in advance!

On 04/29/2016 11:07 AM, Dustin Kempter wrote:
> Hi all,
> Is there a query I can run that will scan through all the tables of a
> database and give me a list of all tables without a primary key? Im not
> having any luck with this.

Two options:

First
http://www.postgresql.org/docs/9.5/interactive/catalog-pg-class.html

select * from pg_class where relhaspkey ='f' and relkind ='r' and
relname not like 'pg_%';


*NOTE* from above link:
"
relhaspkey     bool           True if the table has (or once had) a primary key
"
So it may not totally reflect current reality.


Second

http://www.postgresql.org/docs/9.5/interactive/information-schema.html

http://www.postgresql.org/docs/9.5/interactive/infoschema-table-constraints.html

http://www.postgresql.org/docs/9.5/interactive/infoschema-tables.html

I restricted the below to exclude system and information_schema tables:

select * from information_schema.tables where table_catalog = 'test' and
table_schema !='pg_catalog' and table_schema != 'information_schema'
and table_name not in(select table_name from
information_schema.table_constraints where constraint_type = 'PRIMARY KEY');


*NOTE* The information returned is dependent on the privileges of the
user running the query, so if you want to see everything run as a superuser.

>
> Thanks in advance!
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com