Thread: 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@hub.org Yahoo!: yscrappy ICQ: 7615664
"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
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.
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@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-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@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-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@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
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@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 > >
On Tue, 2005-05-31 at 17:05 -0400, Tom Lane wrote: > "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 ... 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