CREATE DATABASE fails when template1 being accessed ...

ERROR: source database "template1" is being accessed by other users

Why is this not allowed? Not that there is generally a reason to be in
template1, but am curious as to why it prevents a new DB from being
created if someone is connected to it ...

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664

"Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> ERROR: source database "template1" is being accessed by other users

> Why is this not allowed?

It's a rather lame attempt to ensure that you don't get a corrupt copy
due to the database changing while you copy it ... I'd like to find
a better way to do it ...

regards, tom lane

On Tue, May 31, 2005 at 14:53:41 -0300,
"Marc G. Fournier" <scrappy(at)postgresql(dot)org> wrote:
>
>
> ERROR: source database "template1" is being accessed by other users
>
> Why is this not allowed? Not that there is generally a reason to be in
> template1, but am curious as to why it prevents a new DB from being
> created if someone is connected to it ...

Because if someone changes something in the database while it is being
copied, the newly created database will probably be messed up.

On Tue, May 31, 2005 at 02:53:41PM -0300, Marc G. Fournier wrote:

> ERROR: source database "template1" is being accessed by other users
>
> Why is this not allowed? Not that there is generally a reason to be in
> template1, but am curious as to why it prevents a new DB from being
> created if someone is connected to it ...

Because we copy the files directly. So if someone modifies one file in
the middle of the copy, or, say, we copy one index and later the table
and someone inserts into a table in the middle, then the new database is
in an inconsistent state.

There has been talk about fixing this in the past, but conclusion seems
to be "too hard to fix for too little benefit."

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"No hay hombre que no aspire a la plenitud, es decir,
la suma de experiencias de que un hombre es capaz"

On Tue, May 31, 2005 at 02:49:09PM -0400, Tom Lane wrote:
> "Marc G. Fournier" <scrappy(at)postgresql(dot)org> writes:
> > ERROR: source database "template1" is being accessed by other users
>
> > Why is this not allowed?
>
> It's a rather lame attempt to ensure that you don't get a corrupt copy
> due to the database changing while you copy it ... I'd like to find
> a better way to do it ...

You sounded less enthusiastic about it on january:

http://archives.postgresql.org/pgsql-bugs/2005-01/msg00395.php

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"Investigación es lo que hago cuando no sé lo que estoy haciendo"
(Wernher von Braun)

Alvaro Herrera <alvherre(at)surnet(dot)cl> writes:
> On Tue, May 31, 2005 at 02:49:09PM -0400, Tom Lane wrote:
>> It's a rather lame attempt to ensure that you don't get a corrupt copy
>> due to the database changing while you copy it ... I'd like to find
>> a better way to do it ...

> You sounded less enthusiastic about it on january:
> http://archives.postgresql.org/pgsql-bugs/2005-01/msg00395.php

Well, I was expressing dissatisfaction with the obvious way of fixing
it. If we knew a low-overhead way to fix it I'd be much more
enthusiastic ...

regards, tom lane

Hey everyone,

I'm sure this has been thought of but was wondering whether anyone had
discussed the allowance of run-time block size specifications at the
tablespace level? I know that a change such as this would substantially
impact buffer operations, transactions, access methods, the storage
manager, and a lot of other stuff, however it would give an
administrator the ability to inhance performance for specific applications.

Arguably, one can set the block size at compile-time, but for a system
running multiple databases it *may* be a nice feature. Would it be used
a lot? Probably not. Would I use it? Certainly! Would some of my
clients use it? Yes.

Perhaps a TODO item for some advantageous company to fund?

-Jonah

Jonah H. Harris wrote:
> Hey everyone,
>
> I'm sure this has been thought of but was wondering whether anyone had
> discussed the allowance of run-time block size specifications at the
> tablespace level? I know that a change such as this would substantially
> impact buffer operations, transactions, access methods, the storage
> manager, and a lot of other stuff, however it would give an
> administrator the ability to inhance performance for specific applications.
>
> Arguably, one can set the block size at compile-time, but for a system
> running multiple databases it *may* be a nice feature. Would it be used
> a lot? Probably not. Would I use it? Certainly! Would some of my
> clients use it? Yes.
>
> Perhaps a TODO item for some advantageous company to fund?
>
> -Jonah

Have you used Oracle's version as well?

--
_______________________________

This e-mail may be privileged and/or confidential, and the sender does
not waive any related rights and obligations. Any distribution, use or
copying of this e-mail or the information it contains by other than an
intended recipient is unauthorized. If you received this e-mail in
error, please advise me (by return e-mail or otherwise) immediately.
_______________________________

"Jonah H. Harris" <jharris(at)tvi(dot)edu> writes:
> I'm sure this has been thought of but was wondering whether anyone had
> discussed the allowance of run-time block size specifications at the
> tablespace level?

Can you produce any evidence whatsoever that this could be worth the cost?
Aside from the nontrivial development effort needed, there would be
runtime inefficiencies created --- for instance, inefficient use of
buffer pool storage because it'd no longer be true that any buffer could
hold any block. Without some pretty compelling evidence, I wouldn't
even waste any time thinking about it ...

regards, tom lane

Yes,

That is what I/my clients have been discussing. It is a nifty
performance feature.

Bricklen Anderson wrote:

> Jonah H. Harris wrote:
>
>> Hey everyone,
>>
>> I'm sure this has been thought of but was wondering whether anyone
>> had discussed the allowance of run-time block size specifications at
>> the tablespace level? I know that a change such as this would
>> substantially impact buffer operations, transactions, access methods,
>> the storage manager, and a lot of other stuff, however it would give
>> an administrator the ability to inhance performance for specific
>> applications.
>>
>> Arguably, one can set the block size at compile-time, but for a
>> system running multiple databases it *may* be a nice feature. Would
>> it be used a lot? Probably not. Would I use it? Certainly! Would
>> some of my clients use it? Yes.
>>
>> Perhaps a TODO item for some advantageous company to fund?
>>
>> -Jonah
>
>
> Have you used Oracle's version as well?
>

On Tue, May 31, 2005 at 02:55:29PM -0600, Jonah H. Harris wrote:
> Hey everyone,
>
> I'm sure this has been thought of but was wondering whether anyone had
> discussed the allowance of run-time block size specifications at the
> tablespace level? I know that a change such as this would substantially
> impact buffer operations, transactions, access methods, the storage
> manager, and a lot of other stuff, however it would give an
> administrator the ability to inhance performance for specific applications.

The problem I see with this proposal is that the buffer manager knows
how to handle only a equally-sized pages. And the shared memory stuff
gets sized according to size * num_pages. So what happens if a certain
tablespace A with pagesize=X gets to have a lot of its pages cached,
evicting pages from tablespace B with pagesize=Y, where Y < X?

While I think it could be workable to make the buffer manager handle
variable-sized pages, it could prove difficult to handle the shared
memory. (We can't resize it while the server is running.)

--
Alvaro Herrera (<alvherre[a]surnet.cl>)
"La principal característica humana es la tontería"
(Augusto Monterroso)

Tom,

You and I both know that depending on the application and data,
different block sizes are beneficial. As for actual statistics due to
overhead, I don't know what I can give you.

I can provide stats from an application which fits the case for multiple
block sizes on Oracle, but Oracle accounts for this overhead anyway. I
can give you academic research studies, which may be fairly unreliable
in a real-world setting.

I don't disagree at all that supporting multiple block sizes would be
one big PITA to implement and that it would add overhead. I am just
saying that it would be a useful feature for *some* people. Granted,
this isn't a large population (at this point in time), but applications
have been written and optimized using these features.

You are all really smart and I'm just putting this suggestion out there
to stew on. I don't want you guys to think that I'm just throwing out
every Oracle feature I can find, just that when I'm working on an
application which benefits from a feature which would similarly be
useful in PostgreSQL, I suggest it. You guys have been working on pgsql
far longer than I, so for my ideas, chew 'em up and spit 'em out, I
don't take offense. As I stated initially, this wouldn't even be a
low-priority thing, just a nicety that IMHO would be well-placed in a
TODO (possibly as "investigate usability and feasability of block sizes
by tablespace").

Tom, I respect your insight and would be more than happy to get you any
information you'd like concerning this subject or any other I may
suggest. I don't want to waste your time, so if there is anything in
particular you want to see, just let me know. Thanks.

-Jonah

Tom Lane wrote:

>"Jonah H. Harris" <jharris(at)tvi(dot)edu> writes:
>
>
>>I'm sure this has been thought of but was wondering whether anyone had
>>discussed the allowance of run-time block size specifications at the
>>tablespace level?
>>
>>
>
>Can you produce any evidence whatsoever that this could be worth the cost?
>Aside from the nontrivial development effort needed, there would be
>runtime inefficiencies created --- for instance, inefficient use of
>buffer pool storage because it'd no longer be true that any buffer could
>hold any block. Without some pretty compelling evidence, I wouldn't
>even waste any time thinking about it ...
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
>
>

On Tue, 2005-05-31 at 17:05 -0400, Tom Lane wrote:
> "Jonah H. Harris" <jharris(at)tvi(dot)edu> writes:
> > I'm sure this has been thought of but was wondering whether anyone had
> > discussed the allowance of run-time block size specifications at the
> > tablespace level?
>
> Can you produce any evidence whatsoever that this could be worth the cost?
> Aside from the nontrivial development effort needed, there would be
> runtime inefficiencies created --- for instance, inefficient use of
> buffer pool storage because it'd no longer be true that any buffer could
> hold any block. Without some pretty compelling evidence, I wouldn't
> even waste any time thinking about it ...

DB2 has had multiple page size support for some time, though the default
was always 4KB. They have just reintroduced the option to have a single
page size > 4KB across the database. They would not do this if there was
not clear evidence that multiple block sizes were inefficient in some
reasonably common cases.

I must admit when I cam here, I thought the same as Jonah. But the I
haven't seen any recent evidence for any benefit. Its a real pain trying
to test this and very difficult to change once its been setup. There's a
great deal more benefit to be had from many other areas, IMHO.

Best Regards, Simon Riggs