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

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

[GENERAL] pg_dump in cycle

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 po

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

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

[GENERAL] Getting track of foreign keys

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, th

[GENERAL] Returning Errors from User Defined C-functions

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 lo

[GENERAL] plpgsql assigning RECORD := RECORD

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

Re: [GENERAL] psql + autocommit

> 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

[GENERAL] support on postgres

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 da

Re: [GENERAL] disk performance benchmarks

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

Re: [GENERAL] division by zero issue

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

Re: [GENERAL] psql + autocommit

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

Re: [GENERAL] division by zero issue

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

Re: [GENERAL] disk performance benchmarks

> "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> ca

Re: [GENERAL] disk performance benchmarks

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

Re: [GENERAL] Boxes

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,

Re: [GENERAL] division by zero issue

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:

Re: [GENERAL] psql + autocommit

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 AUTO

Re: [GENERAL] psql + autocommit

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 p

Re: [GENERAL] division by zero issue

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

Re: [GENERAL] psql + autocommit

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

Re: [GENERAL] psql + autocommit

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 cha

Re: [GENERAL] division by zero issue

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 FRO

Re: [GENERAL] division by zero issue

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 us

[GENERAL] division by zero issue

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 =

[GENERAL] Converting varchar() to text

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 so

Re: [GENERAL] disk performance benchmarks

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

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

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 a

Re: [GENERAL] Spacing in output

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

Re: [GENERAL] Updating another table using a trigger

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.

[GENERAL] what is flushed?

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

[GENERAL] Updating another table using a trigger

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.

Re: [GENERAL] schema level variables

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

Re: [GENERAL] OS X Mac pgAdmin equivalent?

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

Re: [GENERAL] psql + autocommit

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 -

Re: [GENERAL] OS X Mac pgAdmin equivalent?

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 of

Re: [GENERAL] psql + autocommit

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

[GENERAL] OS X Mac pgAdmin equivalent?

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 s

[GENERAL] psql + autocommit

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