Thread: BUG #12670: Can't create postgis extension in single-user mode
The following bug has been logged on the website: Bug reference: 12670 Logged by: ÐÐ¸Ñ Ð°Ð¸Ð» ÐоÑонин Email address: warrior2031@mail.ru PostgreSQL version: 9.3.5 Operating system: ubuntu Description: Some guy in your IRC told me this after some inquire. RhodiumToad: Guest78493: in single-user, doing CREATE TYPE ... AS ...; doesn't create the array type RhodiumToad: Guest78493: it creates only the non-array one, but I have no idea yet why RhodiumToad: Guest78493: so for now, any extension with a CREATE TYPE AS won't load correctly in single-user RhodiumToad: Guest78493: arguably if it doesn't work it should error rather than quietly getting it wrong, so I think you're justified in calling it a postgres bug Please don't hesitate to ask any additional information.
warrior2031@mail.ru writes:
> [ Can't create postgis extension in single-user mode ]
Why in the world would you think that's a good thing to do?
Single-user mode is a barely-documented disaster recovery aid.
It's not meant for routine activity. There are a *whole lot*
of behaviors you want that are turned off in single-user mode.
The specific reason why this doesn't work is this bit in
heap_create_with_catalog:
/*
* Decide whether to create an array type over the relation's rowtype. We
* do not create any array types for system catalogs (ie, those made
* during initdb). We do not create them where the use of a relation as
* such is an implementation detail: toast tables, sequences and indexes.
*/
if (IsUnderPostmaster && (relkind == RELKIND_RELATION ||
relkind == RELKIND_VIEW ||
relkind == RELKIND_MATVIEW ||
relkind == RELKIND_FOREIGN_TABLE ||
relkind == RELKIND_COMPOSITE_TYPE))
new_array_oid = AssignTypeArrayOid();
We could possibly develop some other mechanism for detecting whether
we're within the initdb sequence, but I can't get very excited about
treating this as a bug. Single-user mode hasn't been considered a
standard user environment since maybe the early 90s.
regards, tom lane
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: >> [ Can't create postgis extension in single-user mode ] Tom> Why in the world would you think that's a good thing to do? The context, for what it's worth, is setting up docker containers, so there's a reasonable desire to be able to do some basic database setup before starting any listener. (Not my project; my only involvement in this is in answering questions on irc, as usual) Tom> Single-user mode is a barely-documented disaster recovery aid. Tom> It's not meant for routine activity. There are a *whole lot* Tom> of behaviors you want that are turned off in single-user mode. Ok, I'll bite: what are they? (I did a quick review of uses of IsUnderPostmaster. Essentially none of them are visible at SQL level except this one (the only other one that comes close is the disabling of event triggers). Almost everything else is about process management; the only exceptions I found are one for accepting tsearch dictionary options and another for running as superuser in spite of missing role entries.) Tom> The specific reason why this doesn't work is this bit in Tom> heap_create_with_catalog: Which personally I think is misguided. There never was any good reason other than rowcount of pg_type not to make array types for catalog tables, and it's inconsistent not to be able to use arrays of system views or i_s views and so on. -- Andrew (irc:RhodiumToad)
Andrew Gierth <andrew@tao11.riddles.org.uk> writes:
> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes:
> Tom> Single-user mode is a barely-documented disaster recovery aid.
> Tom> It's not meant for routine activity. There are a *whole lot*
> Tom> of behaviors you want that are turned off in single-user mode.
> Ok, I'll bite: what are they?
By far the worst problem is the lack of any background checkpointing
behavior. But you probably don't want to be without autovacuum either
(which requires the stats collector). The other background processes
are more about performance than minimum expected functionality.
Now, I'll agree that if you just intend to install a couple of simple
extensions, you might be able to get away with doing that much without
any background support processes. But it's not a mode meant for
routine activity. And I rather wonder why you need to install
extensions in that way anyway.
regards, tom lane
On 01/28/2015 03:49 AM, Tom Lane wrote: > Now, I'll agree that if you just intend to install a couple of simple > extensions, you might be able to get away with doing that much without > any background support processes. But it's not a mode meant for > routine activity. And I rather wonder why you need to install > extensions in that way anyway. Using the single-user mode for installing extensions etc. is a sensible idea; or was if it worked. It'd be nice to run initdb, install extensions, even load data etc. all in single-user mode, before opening up the cluster for connections. Sure, you can hack pg_hba.conf / postgresql.conf to keep the real users away until you're done with the setup, but it's more hassle than just using the single-user mode. We had discussions some time ago about teaching the backend to speak the regular FE/BE protocol in single-user mode, and having libpq "connect" to that. That would still be nice... - Heikki
Heikki Linnakangas <hlinnakangas@vmware.com> writes:
> On 01/28/2015 03:49 AM, Tom Lane wrote:
>> Now, I'll agree that if you just intend to install a couple of simple
>> extensions, you might be able to get away with doing that much without
>> any background support processes. But it's not a mode meant for
>> routine activity. And I rather wonder why you need to install
>> extensions in that way anyway.
> Using the single-user mode for installing extensions etc. is a sensible
> idea; or was if it worked. It'd be nice to run initdb, install
> extensions, even load data etc. all in single-user mode,
... and right there is where the argument falls to the ground. Loading
any significant amount of data without background checkpointing, bgwriter,
wal writer, etc will be exceedingly slow, *and* have the risk of having
to replay a whole lot of WAL if you have any problems. It's not apparent
to me how this is superior to loading all the same definitions and data
after the system is up for real.
> We had discussions some time ago about teaching the backend to speak the
> regular FE/BE protocol in single-user mode, and having libpq "connect"
> to that. That would still be nice...
Yeah, I was one of the ones pushing it. But the idea stalled after it
was pointed out how unpleasant an operating environment it would be.
At this point I think a more practical solution to the lock-out-users-
for-maintenance problem would be to invent some postmaster command line
switch for overriding pg_hba.conf. Not sure exactly what it would look
like.
regards, tom lane
On 2015-01-28 01:05:20 +0000, Andrew Gierth wrote: > >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: > > >> [ Can't create postgis extension in single-user mode ] > > Tom> Why in the world would you think that's a good thing to do? > > The context, for what it's worth, is setting up docker containers, so > there's a reasonable desire to be able to do some basic database setup > before starting any listener. Well, that's about as easily done using a nonstandard/private socket directory. Has the advantage of being able to use a sane client, useful error handling, performance ... Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services
On Wed, Jan 28, 2015 at 10:23:25AM -0500, Tom Lane wrote: > At this point I think a more practical solution to the lock-out-users- > for-maintenance problem would be to invent some postmaster command line > switch for overriding pg_hba.conf. Not sure exactly what it would look > like. See pg_upgrade for the most popular current lockout method. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +
On Wed, Jan 28, 2015 at 04:26:03PM +0100, Andres Freund wrote: > On 2015-01-28 01:05:20 +0000, Andrew Gierth wrote: > > >>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: > > > > >> [ Can't create postgis extension in single-user mode ] > > > > Tom> Why in the world would you think that's a good thing to do? > > > > The context, for what it's worth, is setting up docker containers, so > > there's a reasonable desire to be able to do some basic database setup > > before starting any listener. > > Well, that's about as easily done using a nonstandard/private socket > directory. Has the advantage of being able to use a sane client, useful > error handling, performance ... Yes, that's what pg_upgrade does. Windows doesn't have sockets, which limits that option for general use. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. +