Re: Clarify "allow_system_table_mods"

[Melvin Davidson <melvin6925@xxxxxxxxx>]

On Mon, Apr 25, 2016 at 4:26 PM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

>my previous answer did not make sense as postgresql.conf is set up by initdb

Yeah, that kind of puzzles me too.

>-O      allow_system_table_mods = on

Thanks, I'll give that a try and create a new cluster, then get back to you afterwards.

On Mon, Apr 25, 2016 at 4:05 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

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@xxxxxxxxxxx

>my previous answer did not make sense as postgresql.conf is set up by initdb

Yeah, that kind of puzzles me too.

>-O      allow_system_table_mods = on

Thanks, I'll give that a try and create a new cluster, then get back to you afterwards.

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

ok, so I tried

E:\PostgresDB\bin>initdb -U postgres -O -D E:\PGtest\data

and that failed with:

initdb: illegal option -- O
Try "initdb --help" for more information.

which confirmed -O is _not_ a valid option.

So it's back to square one (and apparently catch 22) of allow_system_table_mods :(

--

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