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@hub.org Yahoo!: yscrappy ICQ: 7615664

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

"Marc G. Fournier" <scrappy@postgresql.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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

On Tue, May 31, 2005 at 14:53:41 -0300,
"Marc G. Fournier" <scrappy@postgresql.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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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"

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Alvaro Herrera <alvherre@surnet.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...1/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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, May 31, 2005 at 02:49:09PM -0400, Tom Lane wrote:
> "Marc G. Fournier" <scrappy@postgresql.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...1/msg00395.php

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

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


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

"Jonah H. Harris" <jharris@tvi.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

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.
_______________________________

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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)

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend