cannot use createlang after removing public schema

[Lee Harr]

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 DATABASEpsql 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 DATABASEcreatedb foo CREATE DATABASEpsql foo -c "drop schema public" DROP SCHEMAcreatelang 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
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Froggy / Froggy Corp.]

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,

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

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

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

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

[scott.marlowe]

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.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Richard Huxton]

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

"Lee Harr" <mi*****@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

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

[Lincoln Yeoh]

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.
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly