Re: [GENERAL] Problem with volatile function

2008-06-19 Thread Tom Lane
Craig Ringer [EMAIL PROTECTED] writes: Personally I'd expect that to only evaluate once. It's saying where f_name.counter in this row is equal to some single random value generated at the start of the query. The parameters of the random() function do not depend on the input, so Pg evaluates it

[GENERAL] PITR base backup -- stop server or not?

2008-06-19 Thread Rob Adams
The docs for Making a Base Backup (tar) say that it can be done live without stopping the server: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP (step #3) However, the docs for straight File System Level Backup (tar) say the server must be shut

Re: [GENERAL] Problem with volatile function

2008-06-19 Thread Artacus
You can force Pg to re-evaluate random() by adding a dummy parameter that depends on the input record, or (probably better) by writing a variant of it that tests the input against a randomly generated value and returns a boolean. Eg: Thanks all. So here's the situation. I added a dummy

Re: [GENERAL] Problem with volatile function

2008-06-19 Thread Craig Ringer
Tom Lane wrote: Craig Ringer [EMAIL PROTECTED] writes: Personally I'd expect that to only evaluate once. It's saying where f_name.counter in this row is equal to some single random value generated at the start of the query. The parameters of the random() function do not depend on the input, so

Re: [GENERAL] PITR base backup -- stop server or not?

2008-06-19 Thread Scott Marlowe
On Thu, Jun 19, 2008 at 12:14 AM, Rob Adams [EMAIL PROTECTED] wrote: The docs for Making a Base Backup (tar) say that it can be done live without stopping the server: http://www.postgresql.org/docs/8.3/interactive/continuous-archiving.html#BACKUP-BASE-BACKUP (step #3) However, the docs for

Re: [GENERAL] Database design: Storing app defaults

2008-06-19 Thread David
On Wed, Jun 18, 2008 at 3:24 PM, Jonathan Bond-Caron [EMAIL PROTECTED] wrote: Application defaults go in the application code not in the database (my opinion). That's fine, until you want the defaults to be customizable, without making an new app version. That's what my question is about :-)

Re: [GENERAL] Sequences

2008-06-19 Thread Artacus
I want to create a sequence that increases in unit column 3 for each record individually in column 2 How do i create a sequence that can manage this? Is there a solution for this? Yeah, depesz shows how to do this here

Re: [GENERAL] Database design: Storing app defaults

2008-06-19 Thread David
On Wed, Jun 18, 2008 at 9:30 PM, Shane Ambler [EMAIL PROTECTED] wrote: David wrote: Hi list. If you have a table like this: table1 - id - field1 - field2 - field3 table2 - id - table1_id - field1 - field2 - field3 table1 table2 are setup as 1-to-many. If I want to

Re: [GENERAL] Database design: Backwards-compatible field addition

2008-06-19 Thread David
Problem with this is that some RDBMS (Postgresql specifically) don't let you run update statements on views. Given 1) the view will be fairly uncomplicated and hence fairly straightforward ON INSERT/UPDATE/DELETE rule can likely be added to it allowing for an apparently writable view.

Re: [GENERAL] Database design: Backwards-compatible field addition

2008-06-19 Thread David
Thanks for you reply. On Wed, Jun 18, 2008 at 9:15 PM, Shane Ambler [EMAIL PROTECTED] wrote: David wrote: Hi list. If you have an existing table, and apps which use it, then how do you add new fields to the table (for new apps), but which might affect existing apps negatively? If you

[GENERAL] Inter-app communication via DB

2008-06-19 Thread David
Hi list. One pattern I've used is for apps to communicate events to each other through the database. ie: - App 1 sents a boolean value to True - App 2 queries the field every 10s, sets the value to False, and does something. Is this reasonable, or should apps avoid this pattern? I have seen

Re: [GENERAL] Inter-app communication via DB

2008-06-19 Thread Karsten Hilbert
On Thu, Jun 19, 2008 at 11:09:12AM +0200, David wrote: One pattern I've used is for apps to communicate events to each other through the database. Works nicely with LISTEN/NOTIFY. We use it a lot in GNUmed. - App 1 sents a boolean value to True - App 2 queries the field every 10s, sets the

Re: [GENERAL] Inter-app communication via DB

2008-06-19 Thread Pavel Stehule
hello look to orafce package http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29 regards Pavel 2008/6/19 David [EMAIL PROTECTED]: Hi list. One pattern I've used is for apps to communicate events to each other through the database. ie: - App 1 sents a boolean value to True -

Re: [GENERAL] Inter-app communication via DB

2008-06-19 Thread David
On Thu, Jun 19, 2008 at 11:25 AM, Karsten Hilbert [EMAIL PROTECTED] wrote: On Thu, Jun 19, 2008 at 11:09:12AM +0200, David wrote: [...] One pattern I've used is for apps to communicate events to each other through the database. Works nicely with LISTEN/NOTIFY. We use it a lot in GNUmed. -

Re: [GENERAL] Understanding fsync

2008-06-19 Thread Sam Mason
On Thu, Jun 19, 2008 at 08:56:20AM +0800, Craig Ringer wrote: My original note was mainly in response to Craig's comment that implied fsync doing far more than it actually does. I remember seeing a few comments recently saying similar things about fsync, so sorry for picking specifically on

Re: [GENERAL] renumber table

2008-06-19 Thread Steve Clark
Scott Marlowe wrote: On Wed, Jun 18, 2008 at 3:50 PM, Steve Clark [EMAIL PROTECTED] wrote: I realize this is certainly not the best design - but at this point in time it can't be changed. The table is rarely updated and never concurrently and is very small, typically less than 100 rows so

Re: [GENERAL] Dump and restore problem

2008-06-19 Thread David
On Wed, Jun 18, 2008 at 11:41 PM, Stuart Luppescu [EMAIL PROTECTED] wrote: [...] pg_restore: [tar archiver] could not open TOC file for input: No such file or directory It sounds like the tar file is no longer being created. Try manually running the commands, and verify that the dump,

Re: [GENERAL] pg_locks at-a-glance view

2008-06-19 Thread David Fetter
On Wed, Jun 18, 2008 at 05:39:59PM -0700, Jeff Davis wrote: I was trying to create a more at-a-glance view of the pg_locks table. I included the SQL I came up with (after talking to Merlin) at the bottom of this message. The idea is to show any queries that are waiting on a lock, and the

Re: [GENERAL] Inter-app communication via DB

2008-06-19 Thread Dimitri Fontaine
Le jeudi 19 juin 2008, David a écrit : One pattern I've used is for apps to communicate events to each other through the database. ie: - App 1 sents a boolean value to True - App 2 queries the field every 10s, sets the value to False, and does something. Is this reasonable, or should

Re: [GENERAL] Inter-app communication via DB

2008-06-19 Thread Karsten Hilbert
On Thu, Jun 19, 2008 at 11:46:42AM +0200, David wrote: That will happen anyway, no matter what the message transport is like. Apps will have to read state at startup anyway, no ? I have a small problem with this. If app1 wants to tell app2 to perform an expensive operation (which you

Re: [GENERAL] pg_locks at-a-glance view

2008-06-19 Thread Merlin Moncure
On Thu, Jun 19, 2008 at 9:07 AM, David Fetter [EMAIL PROTECTED] There's stuff in the aforementioned lock.c, but I don't see anything visible to SQL. Maybe it should be...via C. if you use an enum for lockmode, you don't need pl/pgsql at all... merlin -- Sent via pgsql-general mailing list

[GENERAL] postgres generates too much processes per minute

2008-06-19 Thread [EMAIL PROTECTED]
Hello, i have the problem that postgres ist starting and stopping several (up to 4) processes per minute, so that the error log in windows is running full, with more than 14 entries every minute. Does someone know, how to reduce the start and the end of so many processes, is there a variable or

Re: [GENERAL] problem with to_ascii() function in version 8.3.3

2008-06-19 Thread Pavel Arnošt
Could you run the following queries and compare with my results: test= select ascii(c) from chartest;  ascii ---    225 (1 row) test= select encode(convert_to(c,'LATIN9'),'hex') from chartest;  encode  e1 (1 row) test= select

Re: [GENERAL] Error when trying to drop a tablespace

2008-06-19 Thread Cyril SCETBON
Tom Lane wrote: Cyril SCETBON [EMAIL PROTECTED] writes: Tom Lane wrote: So which database has OID 100456? select datname from pg_database where oid = 100456; it's not a database oid but a tablespace oid [ squint... ] There shouldn't be any files directly under a

Re: [GENERAL] Error when trying to drop a tablespace

2008-06-19 Thread Cyril SCETBON
Tom Lane wrote: Cyril SCETBON [EMAIL PROTECTED] writes: Albe Laurenz wrote: is there anything in this directory? find . . ./100456 ./100456/100738 ./100456/102333 ./100456/103442 ./100456/102618 ./100456/104159 ./100456/101234 ./100456/102658 ./100456/104477 So

[GENERAL] Vacuum and inserts

2008-06-19 Thread Mark Wilden
My coworker and I are having an argument about whether it's necessary to VACUUM an insert-only table. My theory is that since there are no outdated nor deleted rows, VACUUM doesn't do anything. I just loaded a TRUNCATEd table with no indexes with 4 million records, indexed it, then ran VACUUM.

Re: [GENERAL] Error when trying to drop a tablespace

2008-06-19 Thread Cyril SCETBON
Albe Laurenz wrote: Cyril SCETBON wrote: I get the following error : postgres=# DROP TABLESPACE IF EXISTS my_tbs; ERROR: tablespace my_tbs is not empty I've searched in pg_class and I'm not able to find a relation which refers to my_tbs with : Find out the directory:

Re: [GENERAL] Error when trying to drop a tablespace

2008-06-19 Thread Cyril SCETBON
Albe Laurenz wrote: Cyril SCETBON wrote: I get the following error : postgres=# DROP TABLESPACE IF EXISTS my_tbs; ERROR: tablespace my_tbs is not empty I've searched in pg_class and I'm not able to find a relation which refers to my_tbs with :

Re: [GENERAL] Forcibly vacating locks

2008-06-19 Thread Laurent Birtz
Bruce Momjian wrote: Laurent Birtz wrote: Hello, I am using Postgres in a high-availability environment and I'd like to know whether Postgres has provisions to kick off a misbehaving client that has obtained an advisory lock on the database and won't release it in a timely fashion. I am not

Re: [GENERAL] HA best pratices with postgreSQL

2008-06-19 Thread Albretch Mueller
On Wed, Jun 18, 2008 at 10:36 PM, Douglas McNaught [EMAIL PROTECTED] wrote: . . . SQL permissions should be all you need. -Doug ~ What about the security implications? Is the J2EE server enough to control access to the DB? ~ Java does not allow for buffer overruns and such hacking venues, but

Re: [GENERAL] Vacuum and inserts

2008-06-19 Thread Michael Fuhr
On Wed, Jun 18, 2008 at 12:29:46PM -0700, Mark Wilden wrote: My coworker and I are having an argument about whether it's necessary to VACUUM an insert-only table. My theory is that since there are no outdated nor deleted rows, VACUUM doesn't do anything. Rolled back transactions on an

Re: [GENERAL] postgres generates too much processes per minute

2008-06-19 Thread Bill Moran
In response to [EMAIL PROTECTED] [EMAIL PROTECTED]: Hello, i have the problem that postgres ist starting and stopping several (up to 4) processes per minute, so that the error log in windows is running full, with more than 14 entries every minute. Does someone know, how to reduce the

Re: [GENERAL] Vacuum and inserts

2008-06-19 Thread Bill Moran
In response to Mark Wilden [EMAIL PROTECTED]: My coworker and I are having an argument about whether it's necessary to VACUUM an insert-only table. My theory is that since there are no outdated nor deleted rows, VACUUM doesn't do anything. I just loaded a TRUNCATEd table with no indexes

[GENERAL] Logging Parameter Values

2008-06-19 Thread Volkan YAZICI
Hi, While log_statements logs parameter values with the logged queries, I cannot see parameter values logged for erronous queries and queries catched by log_min_duration_statements. Here are our logging settings: # grep ^log postgresql.conf logging_collector = on # Enable

Re: [GENERAL] pg_locks at-a-glance view

2008-06-19 Thread Jeff Davis
On Thu, 2008-06-19 at 06:07 -0700, David Fetter wrote: I'm not exactly sure, but it appears to match, at first blush, what's in src/backend/storage/lmgr/lock.c: static const LOCKMASK LockConflicts[] = { I was more interested in the view itself. Is the view an accurate way to interpret

Re: [GENERAL] Logging Parameter Values

2008-06-19 Thread Tom Lane
Volkan YAZICI [EMAIL PROTECTED] writes: While log_statements logs parameter values with the logged queries, I cannot see parameter values logged for erronous queries and queries catched by log_min_duration_statements. What PG version are you using? Since 8.2 log_duration should show parameter

Re: [GENERAL] Vacuum and inserts

2008-06-19 Thread Tom Lane
Michael Fuhr [EMAIL PROTECTED] writes: On Wed, Jun 18, 2008 at 12:29:46PM -0700, Mark Wilden wrote: My theory is that since there are no outdated nor deleted rows, VACUUM doesn't do anything. Rolled back transactions on an insert-only table can leave behind dead rows. Also, even if the

Re: [GENERAL] Logging Parameter Values

2008-06-19 Thread Volkan YAZICI
On Thu, 19 Jun 2008, Tom Lane [EMAIL PROTECTED] writes: What PG version are you using? Since 8.2 log_duration should show parameter values. I don't want to interrupt your work, but as far as I see from logs -- with the configurations I sent previously -- PostgreSQL doesn't log parameter values

Re: [GENERAL] Logging Parameter Values

2008-06-19 Thread Tom Lane
Volkan YAZICI [EMAIL PROTECTED] writes: PostgreSQL 8.3.1.) Consider this test case: test# PREPARE foo (int) AS ] SELECT S.i * T.i ] FROM generate_series(1, $1) AS S(i), ]generate_series(1, $1) AS T(i); test# EXECUTE foo (1000); ... # tail -n 2

Re: [GENERAL] Logging Parameter Values

2008-06-19 Thread Alvaro Herrera
Volkan YAZICI wrote: I don't want to interrupt your work, but as far as I see from logs -- with the configurations I sent previously -- PostgreSQL doesn't log parameter values for queries dropped into query duration limit. (Using PostgreSQL 8.3.1.) Consider this test case: test# PREPARE

[GENERAL] Losing data

2008-06-19 Thread Garry Saddington
I have had a serious loss of data and wondered if anyone could shed any light on what may have happened. My users have been writing reports on students. No error messages have been produced and when called back up the reports seem to be present at the time of writing. However, next day they

Re: [GENERAL] Losing data

2008-06-19 Thread Joshua D. Drake
On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: I have had a serious loss of data and wondered if anyone could shed any light on what may have happened. My users have been writing reports on students. No error messages have been produced and when called back up the reports seem

Re: [GENERAL] Database design: Data synchronization

2008-06-19 Thread Decibel!
On Jun 18, 2008, at 7:07 AM, David wrote: - Many foreign keys weren't enforced - Some fields needed special treatment (eg: should be unique, or behave like a foreign key ref, even if db schema doesn't specify it. In other cases they need to be updated during the migration). - Most

Re: [GENERAL] pg_locks at-a-glance view

2008-06-19 Thread Decibel!
On Jun 19, 2008, at 8:07 AM, David Fetter wrote: On Wed, Jun 18, 2008 at 05:39:59PM -0700, Jeff Davis wrote: I was trying to create a more at-a-glance view of the pg_locks table. I included the SQL I came up with (after talking to Merlin) at the bottom of this message. The idea is to show

Re: [GENERAL] pg_locks at-a-glance view

2008-06-19 Thread Alvaro Herrera
Decibel! escribió: Yeah, if you look at the code, locks are defined as numbers and I believe there's a very simple patter of what conflicts; a higher lock number conflicts with all those that are lower. So, it might be a lot cleaner to have a function that defines numbers for all the

[GENERAL] Error compiling postgresql UDT in visual studio 2008

2008-06-19 Thread Brijesh Shrivastav
Hi! We are in the process of moving our development environment to visual studio 2008 and as a part of that process I tried to build our postgresql extended UDT in that environment. I ran into typedef redefinition error because visual studio 2008 header file crtdefs.h typedef errcode

Re: [GENERAL] Losing data

2008-06-19 Thread Garry Saddington
On Thursday 19 June 2008 16:55, Joshua D. Drake wrote: On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: I have had a serious loss of data and wondered if anyone could shed any light on what may have happened. My users have been writing reports on students. No error messages have

Re: [GENERAL] Losing data

2008-06-19 Thread Scott Marlowe
On Thu, Jun 19, 2008 at 9:55 AM, Garry Saddington [EMAIL PROTECTED] wrote: I have had a serious loss of data and wondered if anyone could shed any light on what may have happened. My users have been writing reports on students. No error messages have been produced and when called back up the

Re: [GENERAL] Losing data

2008-06-19 Thread Bill Moran
In response to Garry Saddington [EMAIL PROTECTED]: On Thursday 19 June 2008 16:55, Joshua D. Drake wrote: On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: I have had a serious loss of data and wondered if anyone could shed any light on what may have happened. My users have

Re: [GENERAL] Losing data

2008-06-19 Thread Alan Hodgson
On Thursday 19 June 2008, Garry Saddington [EMAIL PROTECTED] wrote: I read in a Postgres manual that the hard disk may report to the OS that a write has occured when it actually has not, is this possible? Yeah. But unless the power suddenly turned off that wouldn't cause data loss. Oh, and

Re: [GENERAL] Losing data

2008-06-19 Thread Garry Saddington
On Thursday 19 June 2008 18:10, Bill Moran wrote: In response to Garry Saddington [EMAIL PROTECTED]: On Thursday 19 June 2008 16:55, Joshua D. Drake wrote: On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: I have had a serious loss of data and wondered if anyone could shed

Re: [GENERAL] Losing data

2008-06-19 Thread Adrian Klaver
-- Original message -- From: Garry Saddington [EMAIL PROTECTED] On Thursday 19 June 2008 16:55, Joshua D. Drake wrote: On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: I have had a serious loss of data and wondered if anyone could shed any light

Re: [GENERAL] Losing data

2008-06-19 Thread Garry Saddington
On Thursday 19 June 2008 18:15, Alan Hodgson wrote: On Thursday 19 June 2008, Garry Saddington [EMAIL PROTECTED] wrote: I read in a Postgres manual that the hard disk may report to the OS that a write has occured when it actually has not, is this possible? Yeah. But unless the power

Re: [GENERAL] inserting to a multi-table view

2008-06-19 Thread Michael Shulman
On Tue, Jun 17, 2008 at 10:15 AM, Michael Shulman [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: CREATE RULE studentinro_insert AS ON INSERT TO studentinfo DO INSTEAD ( INSERT INTO person ...; INSERT INTO student(person_id,...) VALUES (currval('person_person_id_seq'),...); ); This

Re: [GENERAL] Losing data

2008-06-19 Thread Garry Saddington
On Thursday 19 June 2008 18:09, Scott Marlowe wrote: On Thu, Jun 19, 2008 at 9:55 AM, Garry Saddington [EMAIL PROTECTED] wrote: I have had a serious loss of data and wondered if anyone could shed any light on what may have happened. My users have been writing reports on students. No error

Re: [GENERAL] Losing data

2008-06-19 Thread Geoffrey
Garry Saddington wrote: On Thursday 19 June 2008 18:15, Alan Hodgson wrote: On Thursday 19 June 2008, Garry Saddington [EMAIL PROTECTED] wrote: I read in a Postgres manual that the hard disk may report to the OS that a write has occured when it actually has not, is this possible? Yeah. But

Re: [GENERAL] Losing data

2008-06-19 Thread Joshua D. Drake
On Thu, 2008-06-19 at 19:06 +0100, Garry Saddington wrote: On Thursday 19 June 2008 18:15, Alan Hodgson wrote: On Thursday 19 June 2008, Garry Saddington [EMAIL PROTECTED] Although I appreciate that this is a funky problem, the problem doesn't yet exist and we are operating in a diagnostic

Re: [GENERAL] Losing data

2008-06-19 Thread Garry Saddington
On Thursday 19 June 2008 18:52, Adrian Klaver wrote: -- Original message -- From: Garry Saddington [EMAIL PROTECTED] On Thursday 19 June 2008 16:55, Joshua D. Drake wrote: On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: I have had a serious

Re: [GENERAL] inserting to a multi-table view

2008-06-19 Thread Michael Shulman
Thanks to everyone who responded to this thread; although I have not gotten a complete solution I have learned a lot about how rules and triggers work. One particular question that is still unanswered: On Mon, Jun 16, 2008 at 9:49 PM, Michael Shulman [EMAIL PROTECTED] wrote: The Postgres

Re: [GENERAL] Losing data

2008-06-19 Thread Joshua D. Drake
On Thu, 2008-06-19 at 19:12 +0100, Garry Saddington wrote: On Thursday 19 June 2008 18:52, Adrian Klaver wrote: -- Original message -- From: Garry Saddington [EMAIL PROTECTED] Seems like a transaction with no commit. Basically along as the session is

Re: [GENERAL] Losing data

2008-06-19 Thread Adrian Klaver
-- Original message -- From: Garry Saddington [EMAIL PROTECTED] On Thursday 19 June 2008 18:52, Adrian Klaver wrote: -- Original message -- From: Garry Saddington [EMAIL PROTECTED] On Thursday 19 June 2008 16:55, Joshua D.

Re: [GENERAL] Losing data

2008-06-19 Thread Tom Lane
Garry Saddington [EMAIL PROTECTED] writes: Oh, and the problem has been intermittant. Another thing that happened this morning is that Postgres had today as 18/06/2008 when in fact it was 19/06/2008 and the OS reported this correctly. Two theories about that one: 1. Postgres' timezone setting

Re: [GENERAL] Losing data

2008-06-19 Thread Shane Ambler
Adrian Klaver wrote: Yes I thought of this but once the report is sent to the DB a separate query is run to get all of that teacher's reports and these are then displayed on a new page. They all appear here but then disappear later. Zope has transaction machinery that rolls everything back on

Re: [GENERAL] inserting to a multi-table view

2008-06-19 Thread Tom Lane
Michael Shulman [EMAIL PROTECTED] writes: http://www.postgresql.org/docs/8.3/static/rules-triggers.html says a trigger that is fired on INSERT on a view can do the same as a rule: put the data somewhere else and suppress the insert in the view. So what do I need to do to make an INSERT

Re: [GENERAL] inserting to a multi-table view

2008-06-19 Thread Michael Shulman
On Tue, Jun 17, 2008 at 3:46 AM, Klint Gore [EMAIL PROTECTED] wrote: The only way I could find to make this work is to use a rule and wrap the inner insert returning in a function. create or replace function newperson (studentinfo) returns setof person as $$ declare arec person%rowtype;

Re: [GENERAL] Database design: Storing app defaults

2008-06-19 Thread Shane Ambler
David wrote: One (of the many) dubious thing with the above schema, is that NULL employee.salary and employee.benefits_id means that apps should use a default from somewhere else (but this is not immediately obvious from the schema alone). So I would probably use a COALESCE and sub-query to get

Re: [GENERAL] Database design: Backwards-compatible field addition

2008-06-19 Thread Shane Ambler
David wrote: Later, you need to add an 'employed' boolean field, to reflect whether an employee is still working at the company Your new apps know the difference between employed and unemployed employee, but old apps all assume that all employees in the table are currently employed, and will

Re: [GENERAL] Losing data

2008-06-19 Thread Garry Saddington
On Thursday 19 June 2008 19:03, Joshua D. Drake wrote: On Thu, 2008-06-19 at 19:06 +0100, Garry Saddington wrote: On Thursday 19 June 2008 18:15, Alan Hodgson wrote: On Thursday 19 June 2008, Garry Saddington [EMAIL PROTECTED] Although I appreciate that this is a funky problem, the problem

[GENERAL] A plpgsql unidentifiable problem.

2008-06-19 Thread Ralph Smith
I'm baffled and have tried various variations but still nogo. From PgAdmin III I get: --- ** Error ** ERROR: syntax error at or near ; SQL state: 42601 Character: 19001 -- referring to the semi-colon after the 'END' statement.

[GENERAL] finding firstname + lastname groups

2008-06-19 Thread blackwater dev
I have to find the same firstname+ lastname combo in my db and see which name appears the most so I basically need to do the following: select name, count(name) from people group by name having count(name)1 The problem is name is not one column but made up of firstname, lastname...how do I do

Re: [GENERAL] A plpgsql unidentifiable problem.

2008-06-19 Thread Philippe Grégoire
And the semi-colon should be removed after the END Philippe Philippe wrote: I think that the IF clauses need END IF. IF uppergt = 'BOD' THEN RETURN 0; END IF; IF uppergt = 'MOD' THEN RETURN 86400/2; END IF; IF uppergt = 'EOD' THEN RETURN 86399; END IF; This should solve the problem.

Re: [GENERAL] A plpgsql unidentifiable problem.

2008-06-19 Thread Philippe Grégoire
I think that the IF clauses need END IF. IF uppergt = 'BOD' THEN RETURN 0; END IF; IF uppergt = 'MOD' THEN RETURN 86400/2; END IF; IF uppergt = 'EOD' THEN RETURN 86399; END IF; This should solve the problem. Philippe Gregoire Information Manager www.boreal-is.com Ralph Smith wrote: I'm

Re: [GENERAL] finding firstname + lastname groups

2008-06-19 Thread Scott Marlowe
On Thu, Jun 19, 2008 at 1:38 PM, blackwater dev [EMAIL PROTECTED] wrote: I have to find the same firstname+ lastname combo in my db and see which name appears the most so I basically need to do the following: select name, count(name) from people group by name having count(name)1 The problem

Re: [GENERAL] renumber table

2008-06-19 Thread David Wilson
On Thu, Jun 19, 2008 at 7:54 AM, Steve Clark [EMAIL PROTECTED] wrote: I am not sure that will do what I want. As an example suppose I have 5 rows and the idfield is 1,2,3,4,5 now row 1 is updated, not the idfield but another column, then row 3 is deleted. Now I would like to renumber them

FW: [GENERAL] finding firstname + lastname groups

2008-06-19 Thread Charles Simard
- -Original Message- - From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of blackwater dev - Sent: 19 juin 2008 15:38 - To: pgsql-general@postgresql.org - Subject: [GENERAL] finding firstname + lastname groups - - I have to find the same firstname+ lastname combo in my db and see

Re: [GENERAL] finding firstname + lastname groups

2008-06-19 Thread Sam Mason
On Thu, Jun 19, 2008 at 03:38:28PM -0400, blackwater dev wrote: The problem is name is not one column but made up of firstname, lastname...how do I do this? I'd probably do something like: SELECT firstname, lastname, COUNT(*) FROM people GROUP BY firstname, lastname HAVING COUNT(*) 1;

Re: [GENERAL] Losing data

2008-06-19 Thread Joshua D. Drake
On Thu, 2008-06-19 at 20:29 +0100, Garry Saddington wrote: On Thursday 19 June 2008 19:03, Joshua D. Drake wrote: On Thu, 2008-06-19 at 19:06 +0100, Garry Saddington wrote: On Thursday 19 June 2008 18:15, Alan Hodgson wrote: On Thursday 19 June 2008, Garry Saddington [EMAIL PROTECTED]

[GENERAL] Importing undelimited files (Flat Files or Fixed-Length records)

2008-06-19 Thread Bill Thoen
I've got to load some large fixed-legnth ASCII records into PG and I was wondering how this is done. The Copy command looks like it works only with delimited files, and I would hate to have to convert these files to INSERT-type SQL to run them through psql.. Is there a way one can specify a

Re: [GENERAL] renumber table

2008-06-19 Thread David Spadea
Steve, Here's your problem and its solution as I understand it: -- Given an example table like this (data isn't too important -- just the sequencing) create table meh ( idserial primary key , word varchar(10) ); -- Populate it with data insert into meh (word) values

Re: [GENERAL] renumber table

2008-06-19 Thread David Spadea
Steve, I'd just like to add that I agree with Scott that this is asking for trouble if the field being renumbered is used as a foreign key somewhere. If you have no way of changing this logic, you should at least look into 'on delete cascade' and 'on update cascade' on your dependent tables. You

Re: [GENERAL] Importing undelimited files (Flat Files or Fixed-Length records)

2008-06-19 Thread Douglas McNaught
On Thu, Jun 19, 2008 at 6:54 PM, Bill Thoen [EMAIL PROTECTED] wrote: I've got to load some large fixed-legnth ASCII records into PG and I was wondering how this is done. The Copy command looks like it works only with delimited files, and I would hate to have to convert these files to

Re: [GENERAL] Losing data

2008-06-19 Thread Tino Wildenhain
Hi, Garry Saddington wrote: On Thursday 19 June 2008 16:55, Joshua D. Drake wrote: On Thu, 2008-06-19 at 16:55 +0100, Garry Saddington wrote: ... Yes I thought of this but once the report is sent to the DB a separate query is run to get all of that teacher's reports and these are then

Re: [GENERAL] A plpgsql unidentifiable problem.

2008-06-19 Thread Ralph Smith
Yes, that was it. The other QUITE similar language I've used didn't require the END IF if it was a one-liner. You can bet I won't forget that one again - or longer than it takes to goof up and rediscover it! Thanks! Ralph On Jun 19, 2008, at

Re: [GENERAL] Importing undelimited files (Flat Files or Fixed-Length records)

2008-06-19 Thread Gregory Williamson
Bill Thoen asked: I've got to load some large fixed-legnth ASCII records into PG and I was wondering how this is done. The Copy command looks like it works only with delimited files, and I would hate to have to convert these files to INSERT-type SQL to run them through psql.. Is there a

Re: [GENERAL] Importing undelimited files (Flat Files or Fixed-Length records)

2008-06-19 Thread Adrian Klaver
On Thursday 19 June 2008 3:54 pm, Bill Thoen wrote: I've got to load some large fixed-legnth ASCII records into PG and I was wondering how this is done. The Copy command looks like it works only with delimited files, and I would hate to have to convert these files to INSERT-type SQL to run

Re: [GENERAL] finding firstname + lastname groups

2008-06-19 Thread blackwater dev
Great, thanks! On Thu, Jun 19, 2008 at 5:14 PM, Sam Mason [EMAIL PROTECTED] wrote: On Thu, Jun 19, 2008 at 03:38:28PM -0400, blackwater dev wrote: The problem is name is not one column but made up of firstname, lastname...how do I do this? I'd probably do something like: SELECT

Re: [GENERAL] Vacuum and inserts

2008-06-19 Thread Mark Wilden
Thanks to everyone for your comments. It does sound like my understanding was basically correct, but also that autovacuum is still worthwhile in my situation, for reasons other than concurrency. ///ark

Re: [GENERAL] Losing data

2008-06-19 Thread Robert Treat
On Thursday 19 June 2008 14:06:38 Garry Saddington wrote: In any case, however, if PostgreSQL reported the transaction complete and the machine didn't experience any hardware problems (like sudden power or disk failure), I would certainly not suspect PostgreSQL as the source of the

Re: [GENERAL] Losing data

2008-06-19 Thread Tom Lane
Robert Treat [EMAIL PROTECTED] writes: On Thursday 19 June 2008 14:06:38 Garry Saddington wrote: The problem seems to have started last friday, when reports started to go missing. Out of curiosity, what is your vacuum strategy? If you're thinking transaction ID wraparound, I believe we can