Re: Clarify "allow_system_table_mods"

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Mon, Apr 25, 2016 at 7:50 PM, David G. Johnston <david.g.johnston@xxxxxxxxx> wrote:

On Monday, April 25, 2016, Melvin Davidson <melvin6925@xxxxxxxxx> 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.
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

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.

David J. 

>I doubt it's intended to be opinionated aside from covering all DDL

So, IOW "Allows modification of the structure of system tables" does not allow ALTER TABLE ADD column on syscats? That seems a bit contradictory.

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.