[GENERAL] psql + autocommit

[John Sidney-Woollett]

With the advent of postgres v8, would it be possible to change the 
default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?

Although this might break backward compatibility, it might be acceptable 
on the basis that v8 is such a major release.

Also adding a new command line parameter to control the AUTOCOMMIT 
setting for those users that will experience broken scripts executed 
(especially using the -c command) might help ease the pain, since they 
would only have to add a new switch to their existing scripts, or 
explictly set the AUTOCOMMIT variable in their scripts. Otherwise they 
could add a final COMMIT at the end of the script.

In Oracle's SQLPlus, AUTOCOMMIT=OFF is the default behaviour and is (in 
my view) preferable to the current situation.

I know the AUTOCOMMIT can be set in an active session, but I sometimes 
forget leading to an un-rollback-able data loss/damage. Using the 
.psqlrc file can lead to inconsistancies between different accounts 
where some have the setting defined and others don't.

The final reason for doing so would be to closer to the SQL spec.
John Sidney-Woollett
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
 subscribe-nomail command to [EMAIL PROTECTED] so that your
 message can get through to the mailing list cleanly

[GENERAL] OS X Mac pgAdmin equivalent?

[Vic Cekvenich]

What can I run on OS X as a pgAdmin equivalment (other than the Java 
solutions)?
.V
--
Please post on Rich Internet Applications User Interface (RiA/SoA) 


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

Re: [GENERAL] psql + autocommit

[Peter Eisentraut]

John Sidney-Woollett wrote:
> With the advent of postgres v8, would it be possible to change the
> default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?

Absolutely not.  This will break every psql use in existence.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] OS X Mac pgAdmin equivalent?

[Peter Eisentraut]

Vic Cekvenich wrote:
> What can I run on OS X as a pgAdmin equivalment (other than the Java
> solutions)?

phpPgAdmin might be worth a try.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [GENERAL] psql + autocommit

[Tom Lane]

John Sidney-Woollett <[EMAIL PROTECTED]> writes:
> With the advent of postgres v8, would it be possible to change the 
> default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?

If that's what you want, set it in your ~/.psqlrc.

regards, tom lane

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

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

Re: [GENERAL] OS X Mac pgAdmin equivalent?

[Tom Lane]

Vic Cekvenich <[EMAIL PROTECTED]> writes:
> What can I run on OS X as a pgAdmin equivalment (other than the Java 
> solutions)?

In principle pgAdmin 3 should work on OS X.  I dunno if anyone's tried
though ... there may be some rough edges to sand off ...

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] schema level variables

[Shridhar Daithankar]

On Wednesday 15 Sep 2004 6:12 pm, [EMAIL PROTECTED] wrote:
> Hi!
> I am trying to port an oracle app to postgres, an I don't know what to do
> with package scope variables.
> I was looking up some documentation and it seems (IMHO) that schemas would
> be a nice place to put the variables in(as they already have functions,
> operators and types).
> Is this feasible? Is the dev team interested in doing this at some point in
> the future?

Can you replace the package level variable name with a function? The function 
would run a select against a table that stores name/value pair. Of course the 
table has to be limited to the schema itself..

Would that be an acceptable work-around?

 Shridhar

---(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

[GENERAL] Updating another table using a trigger

[Robert Fitzpatrick]

I am running PostgreSQL 7.4.5 and have a trigger on a table called
tblriskassessors which inserts, updates or delete a corresponding record
in tblinspectors by lookup of a contact id and license number match. The
INSERT and DELETE work fine. The UPDATE works good unless I update the
license number. The error, at the bottom of this message, suggests the
primary key violation. But my UPDATE in no way alters the primary key,
which is inspector_contact_id. A manual update on tblinspectors using
the same values works fine. There is a foreign key on tblriskassessors
assessor_contact_id field to the primary key above. The structures of
the two tables can be found below as well.

Can anyone see here what may be causing my problem?

CREATE TABLE "public"."tblriskassessors" (
  "assessor_contact_id" INTEGER NOT NULL, 
  "assessor_certification_state" CHAR(2) NOT NULL, 
  "assessor_license" VARCHAR(50) NOT NULL, 
  "assessor_certificate" TEXT, 
  "assessor_expiration_date" DATE, 
  CONSTRAINT "tblriskassessors_assessor_license_key"
UNIQUE("assessor_license"), 
  CONSTRAINT "tblriskassessors_pkey" PRIMARY KEY("assessor_contact_id"),
  CONSTRAINT "tblinspectors_tblriskassessors_fk" FOREIGN KEY
("assessor_contact_id")
REFERENCES "public"."tblinspectors"("inspector_contact_id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE, 
  CONSTRAINT "tblriskassessorstblstates_fk" FOREIGN KEY
("assessor_certification_state")
REFERENCES "public"."tblstates"("state_abbreviation")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;

CREATE TRIGGER "tblriskassessors_set_inspecor_trigger" BEFORE INSERT OR
UPDATE OR DELETE 
ON "public"."tblriskassessors" FOR EACH ROW 
EXECUTE PROCEDURE
"public"."tblriskassessors_set_inspecor_trigger_func"();

CREATE TABLE "public"."tblinspectors" (
  "inspector_contact_id" INTEGER NOT NULL, 
  "inspector_certification_state" CHAR(2) NOT NULL, 
  "inspector_license" VARCHAR(50) NOT NULL, 
  "inspector_certificate" TEXT, 
  "inspector_expiration_date" DATE, 
  CONSTRAINT "tblinsepectors_pkey" PRIMARY KEY("inspector_contact_id"), 
  CONSTRAINT "tblcontacts_tblinspectors_fk" FOREIGN KEY
("inspector_contact_id")
REFERENCES "public"."tblcontacts"("contact_id")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE, 
  CONSTRAINT "tblinsepectorstblstates_fk" FOREIGN KEY
("inspector_certification_state")
REFERENCES "public"."tblstates"("state_abbreviation")
ON DELETE RESTRICT
ON UPDATE CASCADE
NOT DEFERRABLE
) WITH OIDS;

COMMENT ON TABLE "public"."tblinspectors"
IS 'Risk assessors details tied to contact entry.';

CREATE UNIQUE INDEX "tblinspectors_activity_license_key" ON
"public"."tblinspectors"
USING btree ("inspector_license");

CREATE TRIGGER "tblriskassessors_set_inspecor_trigger" BEFORE INSERT OR
UPDATE OR DELETE 
ON "public"."tblriskassessors" FOR EACH ROW 
EXECUTE PROCEDURE
"public"."tblriskassessors_set_inspecor_trigger_func"();

CREATE OR REPLACE FUNCTION
"public"."tblriskassessors_set_inspecor_trigger_func" () RETURNS trigger
AS'
DECLARE
  checkit record;
  contactid integer;
  license varchar;
  
BEGIN
  IF (TG_OP = ''DELETE'') THEN
contactid := OLD.assessor_contact_id;
license := OLD.assessor_license;
  ELSE
contactid := NEW.assessor_contact_id;
license := NEW.assessor_license;
  END IF;

  SELECT into checkit
public.tblinspectors.inspector_contact_id,
public.tblinspectors.inspector_certification_state,
public.tblinspectors.inspector_license,
public.tblinspectors.inspector_certificate,
public.tblinspectors.inspector_expiration_date,
public.tblcontacts.displayas
  FROM
public.tblinspectors
  INNER JOIN public.tblcontacts ON
(public.tblinspectors.inspector_contact_id =
public.tblcontacts.contact_id)
  WHERE
(public.tblinspectors.inspector_contact_id = contactid) AND
(public.tblinspectors.inspector_license = license);

  IF NOT FOUND THEN
-- insert inspector if id does not exist
INSERT INTO tblinspectors VALUES (NEW.assessor_contact_id,
NEW.assessor_certification_state, NEW.assessor_license, NULL,
NEW.assessor_expiration_date);
IF NOT FOUND THEN
  RAISE EXCEPTION ''Could not insert inspector'';
END IF;
  ELSE
-- update inspector if id does not exist
IF (TG_OP = ''UPDATE'') THEN
 UPDATE tblinspectors set inspector_certification_state =
NEW.assessor_certification_state, inspector_license =
NEW.assessor_license, inspector_expiration_date =
NEW.assessor_expiration_date WHERE inspector_contact_id =
NEW.assessor_contact_id;
   IF NOT FOUND THEN
 RAISE EXCEPTION ''Could not update inspector'';
   END IF;
END IF;
IF (TG_OP = ''DELETE'') THEN
   DELETE FROM tblinspectors WHERE inspector_contact_id =
OLD.assessor_contact_id;
   IF NOT FOUND THEN
 RAISE EXCEPTION ''Could not update inspector'';
   END IF;
END IF;
  END IF;

  IF (TG_OP = ''DELETE'') THEN
RETURN OLD;
  ELSE
RETURN NEW;
  EN

[GENERAL] what is flushed?

[Leonardo Francalanci]

I was reading "Don't be lazy, be consistent: Postgres-R,
a new way to implement Database Replication"
and I found this:

"5.1 General configuration
PostgreSQL uses a force strategy to avoid redo recov­ery,
flushing all dirty buffer pages at the end of each
transaction. With this strategy, response times are
very poor. This makes it difficult to compare with
commercial systems which only flush redo logs to disk.
To allow us to use a more ``realistic'' setting we used
the no­flush option offered by PostgreSQL. With this
option nothing is forced to disk, not even a log record.
This, of course, violates the ACID properties, how­
ever the measured response time was better compara­ble
to standard database systems."

The doc uses Postgresql version 6.4.2.
Has this behaviour been changed?
>From the docs of the 7.4 I got that only the redo logs are flushed...

Or am I wrong?



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

Re: [GENERAL] Updating another table using a trigger

[Stephan Szabo]

On Wed, 15 Sep 2004, Robert Fitzpatrick wrote:

> I am running PostgreSQL 7.4.5 and have a trigger on a table called
> tblriskassessors which inserts, updates or delete a corresponding record
> in tblinspectors by lookup of a contact id and license number match. The
> INSERT and DELETE work fine. The UPDATE works good unless I update the
> license number. The error, at the bottom of this message, suggests the
> primary key violation. But my UPDATE in no way alters the primary key,
> which is inspector_contact_id. A manual update on tblinspectors using
> the same values works fine. There is a foreign key on tblriskassessors
> assessor_contact_id field to the primary key above. The structures of
> the two tables can be found below as well.

Are you sure that you're going in the update path and not the insert path
inside the function? Could the select/if not found be taking effect at
which point the insert occurs rather than the else block?
RAISE NOTICE might be useful to determine thise.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] Spacing in output

[David Fetter]

On Tue, Sep 14, 2004 at 06:37:40PM -1000, Jerome Lyles wrote:
> On Tuesday 14 September 2004 11:27 am, David Fetter wrote:
> > On Tue, Sep 14, 2004 at 11:05:46AM -1000, Jerome Lyles wrote:
> > > I have a small training database: sql_tutorial.  It works fine
> > > but the spacing between the output lines is too much.  This is
> > > the way it looks when I copy and paste from the Konsole to this
> > > email:

> > > sql_tutorial=> SELECT prod_name FROM Products;
> >
> > Is prod_name a char(n) for some large n?
> >
> > Cheers,
> > D
> char(255)

Well, Don't Do That, Then.  TEXT is a great type for storing text.
VARCHAR(n) is a TEXT with a maximum length constraint, and CHAR(n)
(fixed length) is pretty nearly obsolete.

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---(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

Re: [GENERAL] Auto increment/sequence on multiple columns?

[Bruno Wolff III]

On Sun, Sep 12, 2004 at 09:16:37 -0700,
  Nick <[EMAIL PROTECTED]> wrote:
> This is actually a table that holds message threads for message
> boards. Column A is really 'message_board_id' and column B is
> 'thread_id'. I would like every new thread for a message board to have
> a 'thread_id' of 1 and increment from there on. -Nick

Since thread ids should be opaque just use one sequence that generates
all thread ids rather than having separate thread id sets for each message
board id.

If you really want to have thread ids start at 1 and increase by 1 for
each new thread per message boad, sequences aren't the right tool.
The simplest way to do this is when adding a new thread to lock the
table against concurrent updates and then use a new thread id that
is one higher than the current highest for the message board of interest.
This doesn't handle what to do if you need to delete a thread.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] disk performance benchmarks

[Jeffrey W. Baker]

On Wed, 2004-09-15 at 02:39, Michael Paesold wrote:
> Jeffrey W. Baker wrote:
> 
> > Current issue:
> >
> > A dual 64-bit Opteron 244 machine with 8GB main memory, two 4-disk RAID5
> > arrays (one for database, one for xlogs).  PG's config is extremely
> > generous, and in isolated benchmarks it's very fast.
> 
> It depends on the controller, but usually I would expect a better
> performance if xlogs are just on a two-disk mirror and the rest of the disks
> for data (6 splindles instead of 4 then).
> 
> I don't think RAID5 is a benefit for xlogs.

All these replies are really interesting, but the point is not that my
RAIDs are too slow, or that my CPUs are too slow.  My point is that, for
long stretches of time, by database doesn't come anywhere near using the
capacity of the hardware.  And I think that's odd and would like to
config it to "false".

-jwb

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

[GENERAL] Converting varchar() to text

[Steve Atkins]

Is there a safe way to convert varchar(n) to text, other than create
a new column, update, delete column, rename?

I have a number of databases that were built with varvhar(n) and which
should have been done with text. They're in production, and I'd rather
not take the downtime needed to convert some rather large tables - the
bulk update hitting every row of the large table makes it effectively
unvacuumable, and vacuum full requires locks that effectively shut
down the entire system.

They're the same format on disk, so I'm guessing that some diddling
with pg_attribute may be possible. Does anyone have any experience
doing this?

Cheers,
  Steve


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

[GENERAL] division by zero issue

[Greg Donald]

Converting some MySQL code to work with Postgres here.

I have this query:

SELECT
  tasks.task_id,
  (tasks.task_duration * tasks.task_duration_type /
count(user_tasks.task_id)) as hours_allocated
FROM tasks
LEFT JOIN user_tasks
  ON tasks.task_id = user_tasks.task_id
WHERE tasks.task_milestone = '0'
GROUP BY
  tasks.task_id,
  task_duration,
  task_duration_type
;

The problem is that sometimes count(user_tasks.task_id) equals zero,
so I get the division by zero error.  Is there a simple way to make
that part of the query fail silently and just equal zero instead of
dividing and producing the error?

TIA..

-- 
Greg Donald
http://gdconsultants.com/
http://destiney.com/

---(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

Re: [GENERAL] division by zero issue

[Jean-Luc Lachance]

Add :
AND count(user_tasks.task_id) > 0 in the where clause.
Greg Donald wrote:
Converting some MySQL code to work with Postgres here.
I have this query:
SELECT
  tasks.task_id,
  (tasks.task_duration * tasks.task_duration_type /
count(user_tasks.task_id)) as hours_allocated
FROM tasks
LEFT JOIN user_tasks
  ON tasks.task_id = user_tasks.task_id
WHERE tasks.task_milestone = '0'
GROUP BY
  tasks.task_id,
  task_duration,
  task_duration_type
;
The problem is that sometimes count(user_tasks.task_id) equals zero,
so I get the division by zero error.  Is there a simple way to make
that part of the query fail silently and just equal zero instead of
dividing and producing the error?
TIA..
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] division by zero issue

[Guy Fraser]

Maybe try something like this :
SELECT
 task_id,
 CASE
  WHEN task_count = '0'
  THEN '0'::int4
  ELSE (task_duration * 
   task_duration_type / 
   task_count) as hours_allocated
 END
FROM
 (SELECT
   task_id,
   task_duration,
   task_duration_type,
   count(user_tasks.task_id) as task_count
 FROM tasks
 LEFT JOIN user_tasks
   ON tasks.task_id = user_tasks.task_id
 WHERE tasks.task_milestone = '0'
 GROUP BY
   tasks.task_id,
   task_duration,
   task_duration_type
 ) as intermediate
;

This was done off the cuff so it may not work as is.
Greg Donald wrote:
Converting some MySQL code to work with Postgres here.
I have this query:
SELECT
 tasks.task_id,
 (tasks.task_duration * tasks.task_duration_type /
count(user_tasks.task_id)) as hours_allocated
FROM tasks
LEFT JOIN user_tasks
 ON tasks.task_id = user_tasks.task_id
WHERE tasks.task_milestone = '0'
GROUP BY
 tasks.task_id,
 task_duration,
 task_duration_type
;
The problem is that sometimes count(user_tasks.task_id) equals zero,
so I get the division by zero error.  Is there a simple way to make
that part of the query fail silently and just equal zero instead of
dividing and producing the error?
TIA..
 


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] psql + autocommit

[John Sidney-Woollett]

No it won't!
It will break any newly compiled version of psql expecting to work to 
the old behaviour. It won't affect anyone using an older version of 
postgres or psql (ie pre version 8).

To ease any pain, what about a configuration setting for the build 
script for postgres (and psql) which changes the default behaviour for 
the AUTOCOMMIT setting.

I personally would want to build it with AUTOCOMMIT=OFF is I had the 
setting to do so.

This change apart from being more standards compliant would help make 
psql "safer" than it currently is.

John Sidney-Woollett
Peter Eisentraut wrote:
John Sidney-Woollett wrote:
With the advent of postgres v8, would it be possible to change the
default behaviour of psql from AUTOCOMMIT=ON to AUTOCOMMIT=OFF ?

Absolutely not.  This will break every psql use in existence.
---(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

Re: [GENERAL] psql + autocommit

[Peter Eisentraut]

John Sidney-Woollett wrote:
> It will break any newly compiled version of psql expecting to work to
> the old behaviour. It won't affect anyone using an older version of
> postgres or psql (ie pre version 8).

Of course there are no backward compatibility issues when you keep using 
the old version.  The problem is that people will use the new psql 
expecting it to behave like the old one.  This isn't a small secondary 
change; it fundamentally changes the interaction with the program.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


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

Re: [GENERAL] division by zero issue

[Greg Donald]

On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
<[EMAIL PROTECTED]> wrote:
> Add :
> 
> AND count(user_tasks.task_id) > 0 in the where clause.

I get the error:
aggregates not allowed in WHERE clause


-- 
Greg Donald
http://gdconsultants.com/
http://destiney.com/

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

Re: [GENERAL] psql + autocommit

[John Sidney-Woollett]

I agree with you 100% about this - whoever it won't affect new users 
starting with v8 (including many new Windows users), and those migrating 
from other dbs (like Oracle).

If a config switch was available for the build process that could 
preserve the "old" behavior - it wouldn't really pose a problem for 
existing users migrating their systems to v8 provided they set the 
switch appropriately.

A warning message on psql start might help:
++
Welcome to psql 8.0, the PostgreSQL interactive terminal.
Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help on internal slash commands
   \g or terminate with semicolon to execute query
   \q to quit
AUTOCOMMIT is ON/OFF <-- depending on the way it is built
dbname=#
++
This is an opportunity here to make psql more standards compliant and it 
can be done in such a way so as not to p*ss off the existing user base, 
and break their applications.

John Sidney-Woollett
Peter Eisentraut wrote:
John Sidney-Woollett wrote:
It will break any newly compiled version of psql expecting to work to
the old behaviour. It won't affect anyone using an older version of
postgres or psql (ie pre version 8).

Of course there are no backward compatibility issues when you keep using 
the old version.  The problem is that people will use the new psql 
expecting it to behave like the old one.  This isn't a small secondary 
change; it fundamentally changes the interaction with the program.

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

Re: [GENERAL] psql + autocommit

[Peter Eisentraut]

John Sidney-Woollett wrote:
> If a config switch was available for the build process that could
> preserve the "old" behavior - it wouldn't really pose a problem for
> existing users migrating their systems to v8 provided they set the
> switch appropriately.

Such a switch exists: you put \set AUTOCOMMIT in your psql configuration 
file.  We don't put feature-altering switches in the build process if 
we can help it.  Since most people use prebuilt binaries, such a switch 
would be mostly useless at best.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


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

Re: [GENERAL] division by zero issue

[Peter Eisentraut]

Greg Donald wrote:
> I get the error:
> aggregates not allowed in WHERE clause

Try HAVING then.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/


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

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

Re: [GENERAL] Boxes

[Bruno Wolff III]

Please don't reply to existing threads to start a new thread. This makes the
archives less usable.

On Tue, Sep 14, 2004 at 14:00:43 +0200,
  Pierre-Frédéric Caillaud <[EMAIL PROTECTED]> wrote:
> 
>   I have a table containing coordinates and I want to insert these 
>   into  another table, converting them to boxes.
> 
>   I would like to use the same syntax as arrays :
> 
>   INSERT INTO ... (coords) SELECT ARRAY[a,b,c] FROM ...
> 
>   But I want boxes. Is there a way to do this ?
> 
>   SELECT BOX[[a,b],[c,d]] FROM ...

You want something like box(point(a,b),point(c,d)) .


>   raises a Syntax Error.
>   And I'd like to avoid to concatenate strings to build a box  
> representation like
>   '((1,2),(3,4))'::box;
> 
>   Is there a way ?
> 
>   Thanks !
> 
> ---(end of broadcast)---
> TIP 7: don't forget to increase your free space map settings

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

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

Re: [GENERAL] disk performance benchmarks

[Vivek Khera]

> "GS" == Greg Stark <[EMAIL PROTECTED]> writes:

GS> For write heavy application I would expect RAID5 to be a lose on
GS> any software-raid based solution. Only with good hardware raid
GS> systems with very large battery-backed cache would it begin to be
GS> effective.

Who in their right mind would run a 14 spindle RAID in software? :-)

Battery backed write-back cache is definitely mandatory for performance.

-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED]   Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] disk performance benchmarks

[Vivek Khera]

> "JWB" == Jeffrey W Baker <[EMAIL PROTECTED]> writes:

JWB> All these replies are really interesting, but the point is not that my
JWB> RAIDs are too slow, or that my CPUs are too slow.  My point is that, for
JWB> long stretches of time, by database doesn't come anywhere near using the
JWB> capacity of the hardware.  And I think that's odd and would like to
JWB> config it to "false".

Have you tried to increase your checkpoing_segments?  I get the
suspicion that you're checkpointing every 3 minutes constantly.
You'll have to restart the postmaster for this setting to take effect,
I believe.


-- 
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D.Khera Communications, Inc.
Internet: [EMAIL PROTECTED]   Rockville, MD  +1-301-869-4449 x806
AIM: vivekkhera Y!: vivek_khera   http://www.khera.org/~vivek/

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] division by zero issue

[David Fetter]

On Wed, Sep 15, 2004 at 12:23:55PM -0500, Greg Donald wrote:
> On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
> <[EMAIL PROTECTED]> wrote:
> > Add :
> > 
> > AND count(user_tasks.task_id) > 0 in the where clause.
> 
> I get the error:
> aggregates not allowed in WHERE clause

HAVING count(user_tasks.task_id) > 0

I know it's a little weird to have WHERE for non-aggregate and HAVING
for aggregates, but that's the SQL standard...

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] psql + autocommit

[John Sidney-Woollett]

I can see this is going nowhere fast! :)
I'd like to see a global setting that I could change, not one on a user 
by user basis...

I'd also like a message in the Welcome banner telling me what the 
current AUTOCOMMIT setting is...

John Sidney-Woollett
Peter Eisentraut wrote:
John Sidney-Woollett wrote:
If a config switch was available for the build process that could
preserve the "old" behavior - it wouldn't really pose a problem for
existing users migrating their systems to v8 provided they set the
switch appropriately.

Such a switch exists: you put \set AUTOCOMMIT in your psql configuration 
file.  We don't put feature-altering switches in the build process if 
we can help it.  Since most people use prebuilt binaries, such a switch 
would be mostly useless at best.

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

Re: [GENERAL] division by zero issue

[Tom Lane]

Greg Donald <[EMAIL PROTECTED]> writes:
> On Wed, 15 Sep 2004 12:55:24 -0400, Jean-Luc Lachance
> <[EMAIL PROTECTED]> wrote:
>> Add :
>> AND count(user_tasks.task_id) > 0 in the where clause.

> I get the error:
> aggregates not allowed in WHERE clause

You need to put it in HAVING, instead.

Note also this 7.4.4 bug fix:

* Check HAVING restriction before evaluating result list of an aggregate plan

which means that this isn't really gonna work unless you are on 7.4.5.
(It's fairly astonishing that no one noticed we were doing this in the
wrong order until recently, but no one did ...)

regards, tom lane

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

Re: [GENERAL] disk performance benchmarks

[Ron St-Pierre]

oops, sent this to performance by mistake.
Jeffrey W. Baker wrote:
All these replies are really interesting, but the point is not that my
RAIDs are too slow, or that my CPUs are too slow.  My point is that, for
long stretches of time, by database doesn't come anywhere near using the
capacity of the hardware.  And I think that's odd and would like to
config it to "false".
 

What motherboard are you using, and what distro? Earlier you mentioned 
that you're on linux 2.6.7 and
a 64-bit Opteron 244 machine with 8GB main memory, two 4-disk RAID5  
arrays (one for
database, one for xlogs).

Also, did you have a chance to test performance before you implemented RAID?
Ron


---(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

[GENERAL] support on postgres

[Vincent . Desloges]

Hello,

We develop SMS application using a proprietary framework installed on Linux
server. This framework installs and creates Postgres 7.1 data base under
/var/lib partition.
Client notices that /var/lib partition seems too small for SMS application
activity. Client would like to move postgres data files from /var/lib to a
larger one.
How can we manage ?

best regards,

subscribe
end



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

Re: [GENERAL] psql + autocommit

[tmp]

> To ease any pain, what about a configuration setting for the build 
> script for postgres (and psql) which changes the default behaviour for 
> the AUTOCOMMIT setting.

I really agree on the need for a posibility to set autocommit = off
*regardless* of the client:
On a system using both psql, php and perl on a large bunch of clients it
is a pain - real pain - to set this behavior up individually.

It could be really nice if I had a choise of ensuring that any default
behavior was the more "secure" autommit = off. So please bring back this
setting in postgresql.conf. Please.


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

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

[GENERAL] plpgsql assigning RECORD := RECORD

[MK]

Hi all,

I have a question regarding the RECORD type in plpgsql functions.

How do I assign a RECORD variable to another RECORD variable?

For example,

DECLARE
rs1 RECORD;
rs2 RECORD;
BEGIN

FOR rs1 IN SELECT * FROM mytable LOOP

  rs2 := rs1; --IS THIS POSSIBLE!?
END LOOP;
END;

When executing my function I  receive 'syntax error at or near "rs2"'.
So, obviously my syntax is not correct. Is there another way to
perform this?

In my loop I need to have access to the PREVIOUS record. There are A
LOT of fields being selected so storing each in its own variable is
not a desirable solution.

Any ides?

Thanks in advance

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] Returning Errors from User Defined C-functions

[Otto Blomqvist]

Hello !

I have several functions that are compiled as .so and runs pretty well
under PSQL. However I would like to be able to return errors (for
debugging) using something like this

fprintf(stderr, "BEGIN command failed\n");

Problem is I don't know where this would show up..? In some Postgres
log maybe ? I am not directly calling these functions, they are called
from a Daemon, also written in C running on the same box.

Any ideas ? 

Thanks a lot

/Otto Blomqvist

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

[GENERAL] Getting track of foreign keys

[tmp]

I have the following problem:

1) I have one master table with a primary key.

2) In addition I have *several* slave tables, all refering to a primary
key in the master table (no two slave tables refer to the same master key)

I wan't to make sure that no keys in the master table are unreferred, that
is:

* If a slave row is deleted, I wan't to delete the corresponding master
row too. Also if the slave row should be modified as a result of some
cascading effect, I wan't all unreferred master keys to be deleted.

* I have tried to create a trigger, that ensures the above. But this is,
for some reason, not getting fired if the deletion of a slave row is a
result of a foreign key cascading effect. So this is only of limited use.


How can I keep my master table clean - preventing any "leaks"?


For the curios: The master table is a dictionary table and all the slave
tables contains all (dynamically created) content. Hereby all content
can be localised easily.

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

Re: [GENERAL] Returning Errors from User Defined C-functions

[Joe Conway]

Otto Blomqvist wrote:
I have several functions that are compiled as .so and runs pretty well
under PSQL. However I would like to be able to return errors (for
debugging) using something like this
See:
http://www.postgresql.org/docs/current/static/error-message-reporting.html
Joe
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[GENERAL] pg_dump in cycle

[Ilia Chipitsine]

Dear Sirs,
I want to dump all databases, but separately each database in its own 
file, not all databases in one single file as pg_dumpall does.

How can I implement that ?
Cheers,
Ilia Chipitsine
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [GENERAL] Auto increment/sequence on multiple columns?

[Thomas F . O'Connell]

You'll probably need a sequence per thread. A sequence is not 
necessarily tied to a column.

-tfo
On Sep 12, 2004, at 11:16 AM, Nick wrote:
This is actually a table that holds message threads for message
boards. Column A is really 'message_board_id' and column B is
'thread_id'. I would like every new thread for a message board to have
a 'thread_id' of 1 and increment from there on. -Nick

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]