Thread: cannot use createlang after removing public schema
I have a database where I remove the schema public. When I
try to use the createlang script, it fails like this ...
>createdb foo
CREATE DATABASE
>psql foo -c "select version()"
version
---------------------------------------------------------------------
PostgreSQL 7.4.1 on i386-portbld-freebsd4.9, compiled by GCC 2.95.4
(1 row)
>createlang plpgsql foo
>dropdb foo
DROP DATABASE
>createdb foo
CREATE DATABASE
>psql foo -c "drop schema public"
DROP SCHEMA
>createlang plpgsql foo
createlang: language installation failed: ERROR: no schema has been
selected to create in
I read through the man page, but I do not see how to specify the
schema to createlang ...
I can add the language using the manual syntax, I am just wondering if
there is a simple way to use the script.
_________________________________________________________________
Tired of spam? Get advanced junk mail protection with MSN 8.
http://join.msn.com/?page=features/junkmail
Hello,
I asked one time for more "benchmark" soft to know where is the cpu
average, and read the post about optimising the postgresql.conf (and use
them), but i allways get a load > 1 on fire time (dunno the right name,
"coup de feu" in french (10h00 -> 14h00, 18h00 -> 21h00).
For information, i have a Celeron 1.2Ghz with 256Mb, IDE drive, enough
bandewitch, and about 3000 hit per day. Its postgresql 7.2.lastone,
apache 1.3.lastone, linux (redhat), and the last 2.4 kernel after the
exploit problem. Its not 'my' server, so i cant upgrade anything of it
(RAM is very short i think). Im hosting a web site with apache/php. The
table are not huge, the biggest is aroung 3000rows and only 25-30
tables.
The problem is that on "fire time", the load go to > 1 and stay long
time. But with top (i use top -d 1 to have "real" load average) i can
see that the CPU is more than 50% idling.
For exemple, i have this kind of stat :
0s - load 1.5 - cpu idling 0%
5s - load 1.6 - cpu ilding 50%
6s -> 60s - load around 1.2 - cpu idling around 50%-100%
(Dunno if its very easy to understand).
With different software, i dont see anything wrong (or i dont understand
how to use them), the problem is the memory which make some nice road
around 12Mo Free and 3Mo Free, but the swap dont really grow up (but
linux make a lot of cache).
In fact, i hosted the old site with mysql/apache and i was very happy to
see the load going from 0.90 to 0.40 but the population growing up and
the problem came.
I made the common optimisation with VACUUM ANALYZE and some from the
documentation.
Maybe i dont understand what load average mean, but i dont understand
why with more than 50% cpu idling, the load average dont grow down. So i
thought i lose cpu from somewhere ... but the probleme is what is this
somewhere :).
If someone could help me, i need to put a new feature which will add
more than 2000 hit per day and im afraid about the life of the server
:).
Really thx in advance,
regards,
On Wednesday 18 February 2004 19:24, Lee Harr wrote: > I have a database where I remove the schema public. When I > try to use the createlang script, it fails like this ... > [snip] > DROP SCHEMA > > >createlang plpgsql foo > > createlang: language installation failed: ERROR: no schema has been > selected to create in > I read through the man page, but I do not see how to specify the > schema to createlang ... > > I can add the language using the manual syntax, I am just wondering if > there is a simple way to use the script. What if you use CREATE LANGUAGE as an SQL statement, but after setting search_path to the single schema you want? Incidentally, createlang is just a script that issues SQL, so you could probably hack that if you wanted to. Perhaps even post it as a bug with a patch. -- Richard Huxton Archonet Ltd
On Wed, 18 Feb 2004, Froggy / Froggy Corp. wrote: > The problem is that on "fire time", the load go to > 1 and stay long > time. But with top (i use top -d 1 to have "real" load average) i can > see that the CPU is more than 50% idling. > > For exemple, i have this kind of stat : > > 0s - load 1.5 - cpu idling 0% > 5s - load 1.6 - cpu ilding 50% > 6s -> 60s - load around 1.2 - cpu idling around 50%-100% sounds to me like you're starving for bandwidth on your I/O subsystem. any chance you can get a faster set of drives under it or go with a SCSI RAID controller with battery backed cache? also, turning off fsync may increase speed at the cost of data security in the event of a kernel crash or power failure. Are you doing a lot of writing or mostly just reading? If mostly reading, then you might do well with more memory in the machine. 256 meg is kinda puny. You'd do better with my old PIII750 machine that had 1.5 gig in it than a celeron with only 256 meg, no matter how fast the celeron.
On Wednesday 18 February 2004 20:18, Froggy / Froggy Corp. wrote: > Hello, > > I asked one time for more "benchmark" soft to know where is the cpu > average, and read the post about optimising the postgresql.conf (and use > them), but i allways get a load > 1 on fire time (dunno the right name, > "coup de feu" in french (10h00 -> 14h00, 18h00 -> 21h00). This is the period when you get the most hits, yes? > For information, i have a Celeron 1.2Ghz with 256Mb, IDE drive, enough > bandewitch, and about 3000 hit per day. Its postgresql 7.2.lastone, > apache 1.3.lastone, linux (redhat), and the last 2.4 kernel after the > exploit problem. Its not 'my' server, so i cant upgrade anything of it > (RAM is very short i think). Im hosting a web site with apache/php. The > table are not huge, the biggest is aroung 3000rows and only 25-30 > tables. Not a very big machine, but it's not a big database. > The problem is that on "fire time", the load go to > 1 and stay long > time. But with top (i use top -d 1 to have "real" load average) i can > see that the CPU is more than 50% idling. > > For exemple, i have this kind of stat : > > 0s - load 1.5 - cpu idling 0% > 5s - load 1.6 - cpu ilding 50% > 6s -> 60s - load around 1.2 - cpu idling around 50%-100% > > (Dunno if its very easy to understand). I would guess disk activity is the problem. > With different software, i dont see anything wrong (or i dont understand > how to use them), the problem is the memory which make some nice road > around 12Mo Free and 3Mo Free, but the swap dont really grow up (but > linux make a lot of cache). Not sure I understand you fully, but if swap isn't active you should be OK. See below for a good test. > In fact, i hosted the old site with mysql/apache and i was very happy to > see the load going from 0.90 to 0.40 but the population growing up and > the problem came. > > I made the common optimisation with VACUUM ANALYZE and some from the > documentation. OK - you have obviously done the basics. > Maybe i dont understand what load average mean, but i dont understand > why with more than 50% cpu idling, the load average dont grow down. So i > thought i lose cpu from somewhere ... but the probleme is what is this > somewhere :). Load average is a measure of how many processes are waiting. They might be waiting for CPU time, or disk I/O or network, or ... There are three things to look at while testing this: 1. The output of "vmstat 1" - this will show memory usage, swap activity disk blocks in/out, cpu usage etc. 2. The output from "top", press "M" to sort by memory usage - that way we can see how much memory is being used. 3. How many requests are you processing at the same time? > If someone could help me, i need to put a new feature which will add > more than 2000 hit per day and im afraid about the life of the server Only 5000 hits per day? I'm sure we can get that working. -- Richard Huxton Archonet Ltd
"Lee Harr" <missive@hotmail.com> writes:
>> createdb foo
> CREATE DATABASE
>> psql foo -c "drop schema public"
> DROP SCHEMA
>> createlang plpgsql foo
> createlang: language installation failed: ERROR: no schema has been
> selected to create in
This is not different from the error you'd get if you tried to create
any other sort of object at this point. You have no public schema, and
you have no per-user schema matching your user name, so there's simply
not anyplace to create anything, because there are no schemas in your
search path.
You need to make a schema in which you would like the plpgsql language
handler to live (no, I don't really recommend putting it in pg_catalog).
Then use ALTER DATABASE SET or ALTER USER SET to make your default
search_path begin with that schema. Then createlang will work, as will
"CREATE TABLE foo" and other creation commands.
regards, tom lane
At 09:18 PM 2/18/2004 +0100, Froggy / Froggy Corp. wrote: > For information, i have a Celeron 1.2Ghz with 256Mb, IDE drive, > enough >bandewitch, and about 3000 hit per day. Its postgresql 7.2.lastone, >apache 1.3.lastone, linux (redhat), and the last 2.4 kernel after the >exploit problem. Its not 'my' server, so i cant upgrade anything of it If it's a P4 family Celeron then that's not doing you any favours - those are crippled CPUs, but that doesn't seem to be your main problem. Assuming your IDE/ATA drive is on /dev/hda what does: hdparm -i /dev/hda say? Ideally your ATA drive should be using one of the udma modes. Make sure you are using the 80 pin ATA cables. If your DB is as small as you say, even if you get all your 2000 hits a day spread over just 20-200 seconds you should do ok. ATA hdds since the past 3 years can do 100 I/Os a sec. Even with random seeks you can get at least 5-7MB a sec (typically more - 11MB/sec?) on a 7200 rpm HDD - assuming 10-15msec average seek times, files that have at least 64KB or 128KB contiguous chunks and a smart enough O/S to realize that. 1MB/sec is pretty crappy for any desktop ATA HDD that's less than 3-4 years old. If your ATA HDD is crap, replace it. Link.