Re: More PostgreSQL stuff

Martin Schulze wrote:

Hi Oliver!

I have some more questions wrt. PostgreSQL.

Forwarded to PostgreSQL list, since I cannot answer them.

. Why does pg_dump only write some SQL commands in capital letters
but not all? I wonder if that's intentional. Please look at the
following excerpt. I would have expected CHAR, VARCHAR, INSERT
INTO, VALUES etc. to occur in capital letters.

CREATE TABLE zeitungen (name char(30), typ char(10), ...

insert into zeitungen values ('Mallorca Immobilien ',...

. I wonder how one could add or remove columns from existing tables.

With mSQL this was possible with a trick. You had to dump the
whole table but you could tell the dump program to add dummy fields
or to leave out existing fields. Is there any such possibility
with PostgreSQL?

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Set your affection on things above, not on things on
the earth." Colossians 3:2

Oliver Elphick wrote:

Copied to PostgreSQL lists, in the hope of comments from the experts...

Martin Schulze wrote:

Is there a way to speed up postgres? I'm converting one of my
major apps from mSQL to PostgreSQL and PostgreSQL is at least three
times slower. That's horrible. With this slowlyness I cannot
install PostgreSQL in the office but only at home.

So, is there a way to speed it up? I have turned off debugging
since I hoped that it was the reason for the slowliness but
apparently it isn't.

It depends what you are doing: every update or insert is a separate
transaction, unless you declare transactions yourself. So use
BEGIN TRANSACTION ... COMMIT ... END TRANSACTION to enclose related
updates and you should get a speed improvement.

If you are loading a lot of items, COPY is much faster than
successive INSERTs.

Consider whether to disable fsync; balance the speed improvement against the
slightly increased risk of corrupting your database in the event of a
system crash.

I'm making massive use of SELECT statements. Insert/update is only rare.
Thanks for the hint wrt insert/update. There is an INDEX on the main
select field already.

Regards,

Joey

--
A mathematician is a machine for converting coffee into theorems.

Martin Schulze wrote:

Hi Oliver!

I have some more questions wrt. PostgreSQL.

Forwarded to PostgreSQL list, since I cannot answer them.

. Why does pg_dump only write some SQL commands in capital letters
but not all? I wonder if that's intentional. Please look at

the

following excerpt. I would have expected CHAR, VARCHAR, INSERT
INTO, VALUES etc. to occur in capital letters.

CREATE TABLE zeitungen (name char(30), typ char(10), ...

insert into zeitungen values ('Mallorca Immobilien

',...

CAPS/no-CAPS are just a preference of the coder's of pg_dump. If you
want you can go through the source of pg_dump and uppercase all the SQL
and recompile.

. I wonder how one could add or remove columns from existing

tables.

With mSQL this was possible with a trick. You had to dump the
whole table but you could tell the dump program to add dummy

fields

or to leave out existing fields. Is there any such possibility
with PostgreSQL?

You can do a select into a temp table with the added columns, drop the
old table the rename the temp table to the old name. You'll also have
to recreate your indexes, triggers, and rules.
-DEJ

Jackson, DeJuan wrote:

Martin Schulze wrote:

Hi Oliver!

I have some more questions wrt. PostgreSQL.

Forwarded to PostgreSQL list, since I cannot answer them.

. Why does pg_dump only write some SQL commands in capital letters
but not all? I wonder if that's intentional. Please look at

the

following excerpt. I would have expected CHAR, VARCHAR, INSERT
INTO, VALUES etc. to occur in capital letters.

CREATE TABLE zeitungen (name char(30), typ char(10), ...

insert into zeitungen values ('Mallorca Immobilien

',...

CAPS/no-CAPS are just a preference of the coder's of pg_dump. If you
want you can go through the source of pg_dump and uppercase all the SQL
and recompile.

Sure, I would do this, but it means work. I might get it included in the
Debian package, but will it be included in the upstream package as well?

. I wonder how one could add or remove columns from existing tables.

With mSQL this was possible with a trick. You had to dump the
whole table but you could tell the dump program to add dummy fields
or to leave out existing fields. Is there any such possibility
with PostgreSQL?

You can do a select into a temp table with the added columns, drop the
old table the rename the temp table to the old name. You'll also have
to recreate your indexes, triggers, and rules.

I understand. Looks like PostgreSQL is more preconceived than
other databases. I figure out how to do this, though. I you would
have an example laying around I'd be very happy receiving it.

Thanks,

Joey

--
A mathematician is a machine for converting coffee into theorems.

At 8:20 AM 10/2/98, Oliver Elphick wrote:

Copied to PostgreSQL lists, in the hope of comments from the experts...

Martin Schulze wrote:

Is there a way to speed up postgres? I'm converting one of my
major apps from mSQL to PostgreSQL and PostgreSQL is at least three
times slower. That's horrible. With this slowlyness I cannot
install PostgreSQL in the office but only at home.

Also be sure that you have indexed the fields you use most for queries.

And be sure to do a vaccuum after major data insertion/updates.

--
--
-- "TANSTAAFL" Rich lynch@lscorp.com

Richard Lynch wrote:

At 8:20 AM 10/2/98, Oliver Elphick wrote:

Copied to PostgreSQL lists, in the hope of comments from the experts...

Martin Schulze wrote:

Is there a way to speed up postgres? I'm converting one of my
major apps from mSQL to PostgreSQL and PostgreSQL is at least three
times slower. That's horrible. With this slowlyness I cannot
install PostgreSQL in the office but only at home.

Also be sure that you have indexed the fields you use most for queries.

I mainly use queries with "WHERE nr = %d" and nr is an index
field.

And be sure to do a vaccuum after major data insertion/updates.

I haven't done this. *This* was a *very* good idea. First
tries showed that PostgreSQL is only 1/3 slower than the old SQL
database. This is much more acceptable.

Thank you very much.

Regards,

Joey

--
A mathematician is a machine for converting coffee into theorems.

On Fri, 2 Oct 1998, Oliver Elphick wrote:

Copied to PostgreSQL lists, in the hope of comments from the experts...

Martin Schulze wrote:

Is there a way to speed up postgres? I'm converting one of my
major apps from mSQL to PostgreSQL and PostgreSQL is at least three
times slower. That's horrible. With this slowlyness I cannot
install PostgreSQL in the office but only at home.

So, is there a way to speed it up? I have turned off debugging
since I hoped that it was the reason for the slowliness but
apparently it isn't.

What version of PostgreSQL is being used? Each one has gotten
progressively more efficient/faster. Also, check out the -B and -S
options...one allows you to increase the SHM_* Buffers used, so that more
'data' gets cached to RAM, and the other increaess the amount of RAM used
for doing sort functions (ORDER BY and GROUP BY)...

Also, use the 'EXPLAIN' function to determine how the query is
being performed...in particular, are there parts that creating an index
would help improve speed and performance, but you don't have an index
created? I hit this one once, where I *thought* I had an index created on
one of the fields used in the query, but turned out I didn't. Performance
difference with it added was dramatic...

It depends what you are doing: every update or insert is a separate
transaction, unless you declare transactions yourself. So use
BEGIN TRANSACTION ... COMMIT ... END TRANSACTION to enclose related
updates and you should get a speed improvement.

If you are loading a lot of items, COPY is much faster than
successive INSERTs.

Consider whether to disable fsync; balance the speed improvement against the
slightly increased risk of corrupting your database in the event of a
system crash.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Set your affection on things above, not on things on
the earth." Colossians 3:2

Marc G. Fournier scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664

On Fri, 2 Oct 1998, Oliver Elphick wrote:

. I wonder how one could add or remove columns from existing tables.

With mSQL this was possible with a trick. You had to dump the
whole table but you could tell the dump program to add dummy fields
or to leave out existing fields. Is there any such possibility
with PostgreSQL?

chk out the 'ALTER TABLE' man page...you can add columns quite
easily using this. removing coloumsn is a little more tricky at this
point in time, but we're hoping to add it to the ALTER TABLE syntax 'in
the future'.

Basically, to drop a column, do an 'INSERT INTO...SELECT FROM' (my
syntax may be slightly off, please check man pages) from the table you
want to remove the column from, such that you only insert the columns you
want to remain. Then, when finished, do a ... damn, haven't used this one
yet, so don't remember what the 'command' is...but there is one to do a
RENAME of a table...rename the old to something different, and rename the
new to the old...

Marc G. Fournier scrappy@hub.org
Systems Administrator @ hub.org
scrappy@{postgresql|isc}.org ICQ#7615664

On Fri, 2 Oct 1998, Oliver Elphick wrote:

. I wonder how one could add or remove columns from existing tables.

With mSQL this was possible with a trick. You had to dump the
whole table but you could tell the dump program to add dummy fields
or to leave out existing fields. Is there any such possibility
with PostgreSQL?

chk out the 'ALTER TABLE' man page...you can add columns quite
easily using this. removing coloumsn is a little more tricky at this
point in time, but we're hoping to add it to the ALTER TABLE syntax 'in
the future'.

Basically, to drop a column, do an 'INSERT INTO...SELECT FROM' (my
syntax may be slightly off, please check man pages) from the table you
want to remove the column from, such that you only insert the columns you
want to remain. Then, when finished, do a ... damn, haven't used this one
yet, so don't remember what the 'command' is...but there is one to do a
RENAME of a table...rename the old to something different, and rename the
new to the old...

This is an FAQ item.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026