[GENERAL]
Can we have Auto Backup facility to schedule backup of PostgreSQL Database I am using version 8.2.0 With Regads Ashish Karalkar Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index ---(end of broadcast)--- TIP 1: 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] Auto Backup facility?
Can we have Auto Backup facility to schedule backup of PostgreSQL Database I am using version 8.2.0 With Regads Ashish Karalkar Have a burning question? Go to www.Answers.yahoo.com and get answers from real people who know. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Auto Backup facility?
On Friday 08 December 2006 09:16, Ashish Karalkar wrote: | Can we have Auto Backup facility to schedule backup of | PostgreSQL Database | I am using version 8.2.0 why don't you use cron to set up a backup script? Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Auto Backup facility?
Yes, setup a cron job using pg_dump utility and in case you need incremental backups PITR can serve the purpose Thanks, Shoaib On 12/8/06, Thomas Pundt [EMAIL PROTECTED] wrote: On Friday 08 December 2006 09:16, Ashish Karalkar wrote: | Can we have Auto Backup facility to schedule backup of | PostgreSQL Database | I am using version 8.2.0 why don't you use cron to set up a backup script? Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to use outer join in update
Andrus wrote: In my current DBMS I can use create table t1 ( f1 int, f2 int ); create table t2 ( f3 int, f4 int ); update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL]
[please include a meaningful subject] On Dec 8, 2006, at 17:10 , Ashish Karalkar wrote: Can we have Auto Backup facility to schedule backup of PostgreSQL Database I am using version 8.2.0 cron pg_dump or pg_dumpall on unix works great. I'm not sure on Windows, but I bet there's something. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] How to use outer join in update
You can use a view for that join query and then create a rule over it to insert in the referenced tables for the inserts in view. Thanks, Shoaib On 12/8/06, Alban Hertroys [EMAIL PROTECTED] wrote: Andrus wrote: In my current DBMS I can use create table t1 ( f1 int, f2 int ); create table t2 ( f3 int, f4 int ); update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Excluding schema from backup
Hi all, I tried the knewly introduced feature allowing one to exclude a schema from a backup with pg_dump, but I got a really strange error : pg_dump -U postgres MYDB -N _MYDB gives me a dump including that schema. I then tried pg_dump -U postgres MYDB -n _MYDB and then got pg_dump: No matching schemas were found Dumping the only public schema works. But, by doing so, I miss some other schema I really need. Is there a limitation I didn't catch ? Thanks by advance. Stéphane Schildknecht ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Internal function call from C-language function
On Thu, Dec 07, 2006 at 06:01:13PM +0100, Zoltan Boszormenyi wrote: I have just one more question: How can I get an Oid out of a Datum, i.e. how do I know what type I get in a given Datum? DatumGetObjectId() seems to give me an Oid that was specifically stored as a Datum. I have found the alternative solution. If t is HeapTupleHeader then: snip There is no way to tell what type is in a Datum, it's just that, nothing else. The information about the actual type can come from elsewhere, for example: - If extracting from a tuple, the tuple descriptor has the type (as you found) - If passed as argument, the fcinfo struct *may* have the type information - The SPI interface provide ways to get information also On the other side, a Datum is abstract, and you can receive a Datum as argument and pass it to other functions without needing to know what type it is. But you better so it right because there is no type checking on that level. As for the backtrace, you can get gdb to attach to the backend after you connect. Then when you get the segfault, gdb will catch it and show you exactly where. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] How to use outer join in update
On fös, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote: Andrus wrote: In my current DBMS I can use create table t1 ( f1 int, f2 int ); create table t2 ( f3 int, f4 int ); update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 is this not effectively an INNER JOIN ? the OP needed a LEFT JOIN. gnari ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to use outer join in update
On fös, 2006-12-08 at 10:17 +, Ragnar wrote: On fös, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote: Andrus wrote: In my current DBMS I can use create table t1 ( f1 int, f2 int ); create table t2 ( f3 int, f4 int ); update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 is this not effectively an INNER JOIN ? the OP needed a LEFT JOIN. this can be done in 2 operations easily: update t1 set f1=t2.f3 from t2 where f2 = t2.f4; update t1 set f1=null where not exists (select f3 from t2 where f2=f4); it can also be done in one operation with a self join: update t1 set f1=j.f3 from (t1 t1b left join t2 on t1b.f2=t2.f4) as j where t1.f2=j.f2; gnari ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] loading data, creating indexes, clustering, vacuum...
On Thu, Dec 07, 2006 at 02:52:11PM -0800, Alan Hodgson wrote: On Thursday 07 December 2006 08:38, Angva [EMAIL PROTECTED] wrote: three commands. For instance I have a hunch that creating the indexes first (as I do now) could slow down the clustering - perhaps the row locations in the indexes all have to be updated as the cluster command shifts their locations? And perhaps vacuuming should be done before clustering so that dead tuples aren't in the way? clustering also removes the dead tuples. I would just: - create one index, the one to be clustered - cluster the table - create the remaining indexes And then run ANALYSE. No need to vacuum because the cluster did that already. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] Questions about postgresql-8.2.0-2PGDG.src.rpm
Hello List, I am still trying to generate RPM for an IA-64 server with Red Hat Enterprise Linux 4 AS and today, I got a question about the postgresql-8.2.0-2PGDG.src.rpm. This src.rpm contains : -rw-r--r-- 1 postgres postgres 12459207 Dec 2 20:25 postgresql-8.2.0.tar.bz2 -rw-r--r-- 1 postgres postgres 897 Dec 2 20:28 pg_config.h -rw-r--r-- 1 postgres postgres 1539 Dec 2 20:28 Makefile.regress -rwxr-xr-x 1 postgres postgres 56 Dec 2 20:28 filter-requires-perl-Pg.sh -rw-r--r-- 1 postgres postgres 1631 Dec 2 20:28 postgresql-logging.patch -rw-r--r-- 1 postgres postgres 7529 Dec 2 20:28 postgresql.init -rw-r--r-- 1 postgres postgres 85 Dec 2 20:28 postgresql-bashprofile -rw-r--r-- 1 postgres postgres 1757 Dec 2 20:28 rpm-pgsql.patch -rw-r--r-- 1 postgres postgres15598 Dec 2 20:28 README.rpm-dist -rw-r--r-- 1 postgres postgres 2563 Dec 2 20:28 postgresql-test.patch -rw-r--r-- 1 postgres postgres 919 Dec 2 20:28 postgresql-perl-rpath.patch -rw-r--r-- 1 postgres postgres 141 Dec 2 20:28 postgresql.pam -rw-r--r-- 1 postgres postgres 12198114 Dec 6 17:18 postgresql-8.2.0-2PGDG.src.rpm I got a question with the postgresql-test.patch as in it the changes applied seem to be link to the release postgresql-8.2beta1... Is it normal ? The functions defined in this file are those which are failed in my regression test... What are the functionnalities of all others patchs ? Second problem : if I launch : root# ./configure root# make root# chmod -R a+w src/test/regress root# chmod -R a+w contrib/spi root# su - postgres postgres # make check on the same server with gcc, all the regression tests are OK ! Moreover I see differences between the execution of ./configure via the commands above and via the src.rpm. With the commands above , the environnement variables are : echo #define PGBINDIR \/usr/local/pgsql/bin\ pg_config_paths.h echo #define PGSHAREDIR \/usr/local/pgsql/share\ pg_config_paths.h echo #define SYSCONFDIR \/usr/local/pgsql/etc\ pg_config_paths.h echo #define INCLUDEDIR \/usr/local/pgsql/include\ pg_config_paths.h echo #define PKGINCLUDEDIR \/usr/local/pgsql/include\ pg_config_paths.h echo #define INCLUDEDIRSERVER \/usr/local/pgsql/include/server\ pg_config_paths.h echo #define LIBDIR \/usr/local/pgsql/lib\ pg_config_paths.h echo #define PKGLIBDIR \/usr/local/pgsql/lib\ pg_config_paths.h echo #define LOCALEDIR \\ pg_config_paths.h echo #define DOCDIR \/usr/local/pgsql/doc\ pg_config_paths.h echo #define MANDIR \/usr/local/pgsql/man\ pg_config_paths.h and via the src.rpm, I got : echo #define PGBINDIR \/usr/bin\ pg_config_paths.h echo #define PGSHAREDIR \/usr/share/pgsql\ pg_config_paths.h echo #define SYSCONFDIR \/etc/sysconfig/pgsql\ pg_config_paths.h echo #define INCLUDEDIR \/usr/include\ pg_config_paths.h echo #define PKGINCLUDEDIR \/usr/include/pgsql\ pg_config_paths.h echo #define INCLUDEDIRSERVER \/usr/include/pgsql/server\ pg_config_paths.h echo #define LIBDIR \/usr/lib\ pg_config_paths.h echo #define PKGLIBDIR \/usr/lib/pgsql\ pg_config_paths.h echo #define LOCALEDIR \/usr/share/locale\ pg_config_paths.h echo #define DOCDIR \/usr/share/doc/pgsql\ pg_config_paths.h echo #define MANDIR \/usr/share/man\ pg_config_paths.h = Is it normal ? = Has someone already used the src.rpm to compile the 8.2 release ? Thank you for your help. Regards, Alexandra ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] error with Subquery
Hi! error with Subquery alias... help... SELECT *,(SELECT COUNT(id) FROM articles a WHERE a.lft articles.lft AND a.rgt articles.rgt) AS depth FROM articles where (depth 3) ORDER BY lft Max mailto:[EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2: pg8.1 to pg8.2
TSearch does change from version to version. - Create your database with template=template0 - Load tsearch2.sql - Truncate pg_ts_* tables - Try to restore Ignore the already exists errors and examine the rest carefully. On 08.12.2006 00:11, Rick Schumeyer wrote: It was my understanding that running pgdump creates a file that contains all the necessary commands to use tsearch2. That approach has worked for me to transfer my database from one pg8.1 server to another. I now see that is does *not* work from pg8.1 to pg8.2. At your suggestion I loaded tsearch2.sql before loading the pgdump output. I get some errors in the second part, I believe because it attempts to load tsearch2 stuff from the pg8.1 database that conflicts with the pg8.2 stuff from tsearch2.sql. But, the queries seem to work. So perhaps the answer is, load tsearch2.sql, then load the result of running pgdump on the 8.1 database, and ignore the errors? -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] tsearch2: pg8.1 to pg8.2
Sorry, not only load tsearch2.sql. Load your dictionaries as well. (The SQL-Files generated after Gendict and make in contrib/dict_xx) On 08.12.2006 12:08, Hannes Dorbath wrote: TSearch does change from version to version. - Create your database with template=template0 - Load tsearch2.sql - Truncate pg_ts_* tables - Try to restore Ignore the already exists errors and examine the rest carefully. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 1: 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] delete in PG 8.1.5 is slow
I have two similar datasets, on two diffrent m/cs. One m/c has PG 8.0.0 installed the other has PG 8.1.5 installed i vaccum analyzed both and i see that the delete performance in PG 8.1.5 is one third and sometimes one fifth of PG 8.0.0 please see that the number of rows etc is similar and both were vacuum analyzed. I dont understand whts causing it? The stored procedure that I am trying to execute is as follows: CREATE OR REPLACE FUNCTION remove_exam(bigint) RETURNS text AS ' DECLARE cnt INTEGER; pideid text; pid bigint; BEGIN select into pid patient_id from exam where exam_id = $1; // exam_id the primary key in the exam table. if not found then raise notice ''exam not found''; pideid := ''e''; else SELECT INTO cnt count(*) from exam where patient_id = pid; IF(cnt = 1) THEN -- this is the last exam under the patient pideid := remove_patient(pid); //this procedure just does delete from patient where patient_id = pid else delete from exam where exam_id = $1; pideid := ''p'' || pid || ''/e'' || $1; END IF; end if; RETURN pideid; END; 'LANGUAGE 'plpgsql'; Please tell if this is an issue with PG 8.1.5 thanks, regards Surabhi This message has been scanned by the Trend Micro IGSA and found to be free of known security risks.
Re: [GENERAL] porting time calcs to PG
On Thu, Dec 07, 2006 at 04:44:35PM -0700, [EMAIL PROTECTED] wrote: fields. The WHERE clause that I use in SQL Server is: getdate() + ((2100 + 5 + (9*Points)) / 86400) = DueTime Where the numbers are actually parameters passed in to the function. Other than changine getdate() to now(), I'm not sure how to change the + interval to be effective. All the docs I see use something like interval '1 hour' - not sure how to put a calculated value in the quotes. Is this possible? Sure. Something like SELECT CURRENT_TIMESTAMP + (((2100 + 5 + (9*Points)) / 86400) || 'seconds')::interval = DueTime oughta work. -- Andrew Sullivan | [EMAIL PROTECTED] The plural of anecdote is not data. --Roger Brinner ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] error with Subquery
On fös, 2006-12-08 at 13:58 +0300, Max Bondaruk wrote: Hi! error with Subquery alias... SELECT *,(SELECT COUNT(id) FROM articles a WHERE a.lft articles.lft AND a.rgt articles.rgt) AS depth FROM articles where (depth 3) ORDER BY lft you cannot refer to depth in the where because it is not an attribute of the table in the FROM list. it may be more obvious if we replace the subquery with a constant: SELECT *, 999 as depth FROM articles WHERE (depth 3) however you should be able to do SELECT * FROM ( SELECT *, (SELECT COUNT(id) FROM articles a WHERE a.lft articles.lft AND a.rgt articles.rgt ) AS depth FROM articles ) AS foo WHERE (depth 3) ORDER BY lft gnari ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL]
On 8 Dec 2006 at 18:10, Michael Glaesemann wrote: cron pg_dump or pg_dumpall on unix works great. I'm not sure on Windows, but I bet there's something. pgAdmin comes with pgAgent - I haven't used it, but it's a job scheduler for postgreSQL. Alternatively, use the Windows scheduler with pg_dumpall. --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Questions about postgresql-8.2.0-2PGDG.src.rpm
Hello, On Fri, 2006-12-08 at 11:59 +0100, DANTE Alexandra wrote: I am still trying to generate RPM for an IA-64 server with Red Hat Enterprise Linux 4 AS and today, I got a question about the postgresql-8.2.0-2PGDG.src.rpm. We are working off-list with Alexandra and will inform the list as soon as we successfully build IA-64 RPMs. Regards, -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] The relative stability of different procedural languages
On Dec 7, 11:42 pm, [EMAIL PROTECTED] (Tony Caduto) wrote: BigSmoke wrote: On Dec 7, 11:07 pm, [EMAIL PROTECTED] (Merlin Moncure) wrote: On 7 Dec 2006 14:02:53 -0800, BigSmoke [EMAIL PROTECTED] wrote: I'm facing a particular task for which I need any procedural language but PL/PgSQL. I can't use PL/PgSQL because it doesn't allow me to use local variables such as new and old from a dynamic command. could you clarify what you are trying to do and why pl/pgsql cant do it? I'm dealing with a trigger function which needs to check the nullness of a column in 'new' and 'old'. The catch is that the trigger function needs to take the name of that column as an argument. (I've tried a kludge which stores 'new' and 'old' in a temporary table, but this kludge seems too unreliable to trust.)Why can't you just use something like this: IF new.yourcolumnname IS NULL THEN END IF; I test for null in PLpgsql all the time. Am I missing something? Yes, you're missing something. ;-) Your example doesn't work in my case where mycolumnname is in argument that is passed to the function. - Rowan ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How to use outer join in update
In article [EMAIL PROTECTED], Alban Hertroys [EMAIL PROTECTED] writes: Andrus wrote: In my current DBMS I can use create table t1 ( f1 int, f2 int ); create table t2 ( f3 int, f4 int ); update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 Or this one: UPDATE t1 SET f1 = t2.f3 FROM t1 x LEFT JOIN t2 ON x.f2 = t2.f4 WHERE x.f2 = t1.f2 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Excluding schema from backup
In response to Stéphane Schildknecht [EMAIL PROTECTED]: Hi all, I tried the knewly introduced feature allowing one to exclude a schema from a backup with pg_dump, but I got a really strange error : pg_dump -U postgres MYDB -N _MYDB gives me a dump including that schema. I then tried pg_dump -U postgres MYDB -n _MYDB and then got pg_dump: No matching schemas were found Dumping the only public schema works. But, by doing so, I miss some other schema I really need. Is there a limitation I didn't catch ? My guess is that you're hitting case-folding issues. Try: pg_dump -U postgres MYDB -n \_MYDB\ -- Bill Moran Collaborative Fusion Inc. [EMAIL PROTECTED] Phone: 412-422-3463x4023 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] can this be done in one SQL selcet statement?!
First, I think the table design is probably not the best way to do this. In the relational database world, Table 2 probably should look like this: NODE1 NODE2 NODE1 NODE3 NODE2 NODE4 NODE2 NODE3 Then you could do: INSERT INTO table1 SELECT DISTINCT column2 FROM table2 WHERE column2 NOT IN (SELECT column1 FROM table1); Greg [EMAIL PROTECTED] wrote: I have two table: -Table1: one column of type TEXT containing label for nodes in a graph -Table 2: two columns of type TEXT. first column contains node labels in a graph. second a list of node labels that the node label in column one is connected to. Example: Table1: NODE1 NODE2 Table 2: NODE1NODE2 NODE3 NODE2NODE4 NODE3 Goal: split column2 in table2 to individual node names, find a unique list of all node names obtained after splitting column2 of table2 and insert the ones not already in table1 in table1. Thanks in advance. S ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Auto Backup facility?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/06 02:36, Thomas Pundt wrote: On Friday 08 December 2006 09:16, Ashish Karalkar wrote: | Can we have Auto Backup facility to schedule backup of | PostgreSQL Database | I am using version 8.2.0 why don't you use cron to set up a backup script? But, but, but, but... that's not GUI! It makes me need to learn shell!!! :( - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFeW/3S9HxQb37XmcRAgFDAJ46PMzlO42Z1YymRc9vXIG/soUDvACg1ruc qIZOtAOiCG4LSJMyEOApFcM= =Jv59 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] LISTEN / NOTIFY
Is there a way I can have notifications to be streamed to the listener, so I don't need to poll with LISTEN? LISTEN foo; LISTEN NOTIFY foo; NOTIFY Asynchronous notification foo received from server process with PID 3593. This does work for the same backend, but not for notifications issued from another one. Thanks! -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] LISTEN / NOTIFY
On Fri, Dec 08, 2006 at 03:38:49PM +0100, Hannes Dorbath wrote: Is there a way I can have notifications to be streamed to the listener, so I don't need to poll with LISTEN? LISTEN foo; LISTEN NOTIFY foo; NOTIFY Asynchronous notification foo received from server process with PID 3593. This does work for the same backend, but not for notifications issued from another one. AIUI they are, it's just that the client needs to be looking for incoming data. If you're using psql for example, I don't beleive it checks for incoming data until you send a command. How you actually acheive that in your code is a seperate question and depends on the language you're using. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
[GENERAL] creating functions with variable argument lists
Hi - I am trying to make use of table partitions. In doing so I would like to use a rule to call a functioning which inserts the data into the proper partition. To do so, I believe that I need to find a way to opaquely pass NEW from the rule to a function which then passes it to INSERT. (Well, I could spell out all of the columns in the table as arguments to the function, but that is not as maintainable, e.g. every time the table columns change, so to the function and rule change.) I am not finding any way to do this in the proceedural languages. That said, I would happily believe that I am just missing something, and am hoping that someone on this list has already figured out an answer. For consideration, here is an example: create table foobars ( id bigserial, created_at timestamp not null, name text ); create table foobars_200612 ( check (created_at = timestamp '2006-12-01 00:00:00' and created_at timestamp '2007-01-01 00:00:00') ) inherits (foobars); create table foobars_200701 ( check (created_at = timestamp '2007-01-01 00:00:00' and created_at timestamp '2007-02-01 00:00:00') ) inherits (foobars); -- Warning, pseudo code follows (e.g. NEW): create or replace function foo_insert(NEW) returns void as $$ begin execute 'insert into foobars_' || (select extract(year from $1) || extract(month from $1)) || ' values (' || NEW || ')'; end; $$ language plpgsql; create rule foobars_insert as on insert to foobars do instead select foo_insert(NEW); The key to my success for the above is to find a way for NEW to be used something like the pseudo code shown. Suggestions? - Marc ---(end of broadcast)--- TIP 1: 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] Excluding schema from backup
Bill Moran [EMAIL PROTECTED] writes: In response to Stéphane Schildknecht [EMAIL PROTECTED]: pg_dump -U postgres MYDB -N _MYDB gives me a dump including that schema. I then tried pg_dump -U postgres MYDB -n _MYDB and then got pg_dump: No matching schemas were found My guess is that you're hitting case-folding issues. Try: pg_dump -U postgres MYDB -n \_MYDB\ Yeah, see the last example in the 8.2 pg_dump reference page: To specify an upper-case or mixed-case name in -t and related switches, you need to double-quote the name; else it will be folded to lower case (see Patterns). But double quotes are special to the shell, so in turn they must be quoted. Thus, to dump a single table with a mixed-case name, you need something like $ pg_dump -t 'MixedCaseName' mydb mytab.sql regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Male/female
Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) I personally prefer the second, as it's self-documenting...is there any other/better way of doing it? --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Male/female
Second method might be better. Of course, you could also do a one chracter gender M/F if you want to save space. Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) I personally prefer the second, as it's self-documenting...is there any other/better way of doing it? --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/06 09:23, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) I personally prefer the second, as it's self-documenting...is there any other/better way of doing it? I've only ever seen a CHAR(1) restricted to 'M'/'F'. - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFeYXkS9HxQb37XmcRAtoeAKCmupJdzyH7MzEqfmWGI9lPtM6MfwCg13X6 wdPnXc1DrLN+8oKPSusVk0g= =5Xwk -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Male/female
Raymond O'Donnell [EMAIL PROTECTED] schrieb: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) What about with Hermaphroditism? SCNR. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Male/female
Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) I personally prefer the second, as it's self-documenting...is there any other/better way of doing it? -- -- PostgreSQL database dump -- CREATE TABLE gender ( gender_pk SERIAL, gender character varying(9) NOT NULL ); COMMENT ON TABLE gender IS 'This table defines currently valid gender types (and allows for god knows what..).'; COPY gender (gender_pk, gender) FROM stdin; 0(unknown) 1Male 2Female 3Trans \. ALTER TABLE ONLY gender ADD CONSTRAINT gender_pkey PRIMARY KEY (gender_pk); ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
FW: [GENERAL] Male/female
-Oorspronkelijk bericht- Van: H.J. Sanders [mailto:[EMAIL PROTECTED] Verzonden: vrijdag 8 december 2006 16:33 Aan: Raymond O'Donnell Onderwerp: RE: [GENERAL] Male/female Hi ray. We have done it with a integer whereby 0 = woman 1 = man also self-documenting :-) Henk Sanders -Oorspronkelijk bericht- Van: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Raymond O'Donnell Verzonden: vrijdag 8 december 2006 16:23 Aan: pgsql-general@postgresql.org Onderwerp: [GENERAL] Male/female Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) I personally prefer the second, as it's self-documenting...is there any other/better way of doing it? --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] How to use outer join in update
Ragnar [EMAIL PROTECTED] writes: On fös, 2006-12-08 at 10:09 +0100, Alban Hertroys wrote: Andrus wrote: update t1 set f1=t2.f3 from t1 left join t2 on t1.f2=t2.f4 That looks like a self-join on t1 without using an alias for the second instance of t1. I think you meant: update t1 set f1=t2.f3 from t2 where f2 = t2.f4 is this not effectively an INNER JOIN ? the OP needed a LEFT JOIN. I think using a join for this at all is bad style. What if there is more than one t2 match for a specific t1 row? You'll get indeterminate results, which is not a very good thing for an UPDATE. In this particular example you could do update t1 set f1 = (select f3 from t2 where t1.f2=t2.f4); This will update to f3 if there's exactly one match, update to NULL if there's no match (which is what I assume the OP wants, since he's using a left join), and raise an error if there's multiple matches. If you need to not fail when there's multiple matches, think of a way to choose which one you want, perhaps the largest f3: update t1 set f1 = (select max(f3) from t2 where t1.f2=t2.f4); Of course, you could work out a way to make the join determinate too. My point is that if you're in the habit of doing this sort of thing via join, some day you will get careless and get screwed by an indeterminate update. If you're in the habit of doing it via subselects then the notation protects you against failing to think about the possibility of multiple matches. (Possibly this explains why there is no such construct as UPDATE FROM in the SQL standard...) The problem with the subselect approach of course is what if you need to transfer multiple columns from the other table row? You could do update t1 set f1 = (select f3 from t2 where t1.f2=t2.f4), f2 = (select f7 from t2 where t1.f2=t2.f4), f3 = (select f9 from t2 where t1.f2=t2.f4); This works but is just as inefficient as it looks. The SQL spec does have an answer: update t1 set (f1,f2,f3) = (select f3,f7,f9 from t2 where t1.f2=t2.f4); but PG does not support that syntax yet :-(. I'd like to see it in 8.3 though ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Male/female
On Friday 8. December 2006 16:23, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) I personally prefer the second, as it's self-documenting...is there any other/better way of doing it? There's actually an ISO standard (ISO 5218) for representing gender with numeric values: 0 = Unknown, 1 = Male, 2 = Female, 9 = not specified (or N/A). -- Leif Biberg Kristensen | Registered Linux User #338009 http://solumslekt.org/ | Cruising with Gentoo/KDE ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] strange (maybe) behaviour of table lock
I have a question regarding a strange behaviour (for me, maybe that this is desidered feature) of LOCK on tables. I am using postgres 8.2 I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. The first 4 executions of the servlet goes well, the 5th hangs. Afther that I use psql to do the last query (the one that hangs the system) of the servlet, also psql hangs. I did a modify of the servlet LOCK table,table2,table3,table4 IN EXCLUSIVE MODE and no other modifications. I stop and restart my system, all works well. Can someone tell me if this is a desidered behaviour? (and if is possible why). If may help I can post all the queryes and table structure, they are a little long. Thank you Edoardo Panfili -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 1: 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: FW: [GENERAL] Male/female
H.J. Sanders wrote: We have done it with a integer whereby 0 = woman 1 = man also self-documenting :-) Why not use unicode symbols 0x2640 and 0x2642? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 1: 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: FW: [GENERAL] Male/female
Just wondering.how do list members represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. [snip] We have done it with a integer whereby 0 = woman 1 = man also self-documenting :-) [snip] This gave me my first good laugh of the day... I will never accuse DBAs of not having a sense of humor albeit unique! Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: FW: [GENERAL] Male/female
0 = woman 1 = man This gave me my first good laugh of the day... I will never accuse DBAs of not having a sense of humor albeit unique! Richard, gmail extended my laugh with the sponsored links: How To Be A woman How To Be The Girl That Every Man Secretly Wishes He Was Married To! Relationship-Advice.com PostgreSQL Replication Stable, fast and native replication for PostgreSQL 8.0 and 8.1 www.commandprompt.com/ PostgreSQL GUI admin tool Manage, Sync, Backup, Schedule Task Import/ Export, Report, Download! pgsql.navicat.com Now we just need fast, stable and native replication for The Girl That Every Man Secretly Wishes He Was Married To! Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - Python: the only language with more web frameworks than keywords. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
COPY gender (gender_pk, gender) FROM stdin; 0(unknown) 1Male 2Female 3Trans \. Not to take this completely off track, but isn't transgendered not so much a gender as it is a process of moving from one gender to another? ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] creating functions with variable argument lists
Marc Evans [EMAIL PROTECTED] writes: I am trying to make use of table partitions. In doing so I would like to use a rule to call a functioning which inserts the data into the proper partition. Basically, you're guaranteeing yourself large amounts of pain by insisting on using a rule for this. I'd suggest using a trigger instead. A BEFORE INSERT trigger on the parent table can redirect the data to the appropriate place and then return NULL to prevent the insertion into the parent. Given your example, I'd do something like create or replace function foobars_insert() returns trigger language plpgsql as $$ begin if new.created_at = timestamp '2006-12-01 00:00:00' and new.created_at timestamp '2007-01-01 00:00:00' then insert into foobars_200612 values(new.*); elsif new.created_at = timestamp '2007-01-01 00:00:00' and new.created_at timestamp '2007-02-01 00:00:00' then insert into foobars_200701 values(new.*); elsif ... else raise exception 'No partition for timestamp %', new.created_at; end if; return null; end$$; create trigger foobars_insert before insert on foobars for each row execute procedure foobars_insert(); Obviously you have to adjust the function definition every time you add or remove a partition, but you'll have a script for that anyway, no? BTW, I think using new.* this way only works as of 8.2. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Male/female
On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: 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] Re: Male/female
On Fri, Dec 08, 2006 at 05:26:22PM +0100, Harald Armin Massa [EMAIL PROTECTED] wrote: Now we just need fast, stable and native replication for The Girl That Every Man Secretly Wishes He Was Married To! I want replication WITH that girl! Any chance for 8.3? bkw ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Male/female
Andreas Kretschmer wrote: What about with Hermaphroditism? More seriously - is the gender something you always know? There are situations in the US where you cannot force someone to divulge their gender. So you may need an 'unreported' value of some sort. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Male/female
Seven genders? Even San Fransisco thinks that's over the top. David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Cheers, D ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Re: Male/female
On Fri, 2006-12-08 at 17:39, Bernhard Weisshuhn wrote: On Fri, Dec 08, 2006 at 05:26:22PM +0100, Harald Armin Massa [EMAIL PROTECTED] wrote: Now we just need fast, stable and native replication for The Girl That Every Man Secretly Wishes He Was Married To! I want replication WITH that girl! Any chance for 8.3? Well, all of you who ask for this, don't forget that the main mantra of open source is scratch your own itch ;-) So go out and get it yourself... Cheers, Csaba. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] strange (maybe) behaviour of table lock
Edoardo Panfili [EMAIL PROTECTED] writes: I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. The first 4 executions of the servlet goes well, the 5th hangs. Afther that I use psql to do the last query (the one that hangs the system) of the servlet, also psql hangs. Look into the pg_locks view to find out what it's blocked on. Note that LOCK by default gets an ACCESS EXCLUSIVE lock, which means it will be blocked by *any* pre-existing access, even an open transaction that merely read the table awhile back. Are you sure you really need such a strong lock? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Male/female
On Fri, 2006-12-08 at 09:31 -0700, John Meyer wrote: COPY gender (gender_pk, gender) FROM stdin; 0(unknown) 1Male 2Female 3Trans \. Not to take this completely off track, but isn't transgendered not so much a gender as it is a process of moving from one gender to another? Yes, but further I don't know of any country that recognizes anything but Male or Female. Joshua D. Drake ---(end of broadcast)--- TIP 6: explain analyze is your friend -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Indexes and Inheritance
Keary Suska wrote: Thanks to Erik, Jeff, Richard for their help. I have a further inheritance question: do child tables inherit the indexes created on parent columns, or do they need to be specified separately for each child table? I.e., created via CREATE INDEX. I assume at least that the implicit index created by a primary key would inherit, but I don't know if that assumption is safe. In addition to what the others have replied, this is how i was told to handle this (from this list): -- create your parent table CREATE TABLE parent_table ( id SERIAL PRIMARY KEY, thisVARCHAR(64) NOT NULL, thatVARCHAR(4) NOT NULL ); -- create your child table(s) CREATE TABLE child_table ( foo VARCHAR(64) NOT NULL, bar VARCHAR(4) NOT NULL ) INHERITS (parent_table); -- set the child table's id (from the parent) to take -- the next value of the parent's SERIAL ALTER TABLE child_table ALTER COLUMN id SET DEFAULT nextval('parent_table_id_seq'); -- now create an index on that (so that you have as many indexes -- on the parent's SERIAL as child tables) CREATE UNIQUE INDEX child_table_pk ON child_table (id); Do those last two for each child table and then make sure that you perform your INSERTs on the child table(s). brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] restoring pgdump.sql
Hi I am trying to restore a pgdump.sql file. i am very much in need of help. i first went to pgAdmin III and created two database ( postgres and anuradha)... properties of anuradha :: 1) owner = ofbiz 2) encoding = 'UTF8' 3)connected ? yes 4) allow connections? yes properties of postgres ::: 1) owner = postgres 2) encoding = SQL_ASCII 3)connected ? yes 4) allow connections? yes the encoding of my pgdump.sql is UNICODE...so i thought i should try to restore it in the database anuradha as it has UTF8 encoding.i select anuradha database and click on Execute SQL queries icon on the toolbar.i then opened my pgdmp.sql file but it gave me an error ERROR: syntax error at or near \ SQL state: 42601 Character: 387817. So i seperated my pgdump.sql into two tables.one is creation.doc which consists of only the commands to create new tables and the other is datas.doc which contains the datas i.e. the COPY ..FROM STDIN; statments and lots of data. i opened creation.doc in the SQL query page of pgAdmin and executed the commands and the tables got created in the database anuradha. but i haven't been able to do the same with datas.doc. So i tried a different option .I went to the command prompt of postgreSQL and typed the command psql -U postgres pgdump.sql ( pgdump.sql is the real file with create and copy commands and data). but i got the following message on the command prompt C:\Program Files\PostgreSQL\8.2\binpsql -U postgres pgdump.sql psql: could not connect to server: Invalid argument (0x2726/10022) Is the server running on host ??? and accepting TCP/IP connections on port 5432? i also tried by changing the username to ofbiz and achinta(my postmaster is logged on as achinta.I checked this out by goin to the services of control panel). Any help would be appreciated.Thanks in advance. -Anuradha - Need a quick answer? Get one in minutes from people who know. Ask your question on Yahoo! Answers.
Re: [GENERAL] Male/female
Joshua D. Drake [EMAIL PROTECTED] writes: Yes, but further I don't know of any country that recognizes anything but Male or Female. I haven't read the beginning of the thread, but will this table be used only for humans? There are animals that are hermafrodites (I hope I got the English correct...) or whose sex is only identifiable after a period of time (days or months, usually). So, for researchers it would be interesting to have more options. Also, if you're doing statistics on something where the sexual option (and transgerderness) is important, then there should be some way to point that. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: 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] Male/female
On Fri, 2006-12-08 at 10:44, John Meyer wrote: David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Seven genders? Even San Fransisco thinks that's over the top. Let's see. Male Female Hermaphrodite Trans (MTF) Trans (FTM) Neuter and... I can't think of a seventh possibility. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Male/female
On Fri, 2006-12-08 at 11:05, Joshua D. Drake wrote: On Fri, 2006-12-08 at 09:31 -0700, John Meyer wrote: COPY gender (gender_pk, gender) FROM stdin; 0(unknown) 1Male 2Female 3Trans \. Not to take this completely off track, but isn't transgendered not so much a gender as it is a process of moving from one gender to another? Yes, but further I don't know of any country that recognizes anything but Male or Female. In thailand, there are highschools with bathrooms for the transgendered MTF girls. Not sure if the country itself recognized MTF trans as a gender or not though. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Male/female
Joshua D. Drake wrote: On Fri, 2006-12-08 at 09:31 -0700, John Meyer wrote: COPY gender (gender_pk, gender) FROM stdin; 0(unknown) 1Male 2Female 3Trans \. Not to take this completely off track, but isn't transgendered not so much a gender as it is a process of moving from one gender to another? Yes, but further I don't know of any country that recognizes anything but Male or Female. ... Yet. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Male/female
Scott Marlowe wrote: On Fri, 2006-12-08 at 10:44, John Meyer wrote: David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Seven genders? Even San Fransisco thinks that's over the top. Let's see. Male Female Hermaphrodite Trans (MTF) Trans (FTM) Neuter and... I can't think of a seventh possibility. Unspecified ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Male/female
Scott Marlowe wrote: On Fri, 2006-12-08 at 10:44, John Meyer wrote: David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Seven genders? Even San Fransisco thinks that's over the top. Let's see. Male Female Hermaphrodite Trans (MTF) Trans (FTM) Neuter and... I can't think of a seventh possibility. As has been pointed out, some governments forbid the collection of gender information, so the seventh would be unknown/unreported. brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
I guess in the end it really depends on what the client wants to track and what they don't. But this does actually have a serious implication, and that is how do you code for something that is mutable vs. something that supposedly is or very nearly immutable (i.e. the alphabet). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Male/female
That not including Genetics, where and individual could have multiple X Chromomes individuals Or be XY - female times those other 6 (or 7). - Original Message - From: brian [EMAIL PROTECTED] To: pgsql-general@postgresql.org Sent: Friday, December 08, 2006 9:19 AM Subject: Re: [GENERAL] Male/female Scott Marlowe wrote: On Fri, 2006-12-08 at 10:44, John Meyer wrote: David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Seven genders? Even San Fransisco thinks that's over the top. Let's see. Male Female Hermaphrodite Trans (MTF) Trans (FTM) Neuter and... I can't think of a seventh possibility. As has been pointed out, some governments forbid the collection of gender information, so the seventh would be unknown/unreported. brian ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Male/female
Jorge Godoy wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Yes, but further I don't know of any country that recognizes anything but Male or Female. I haven't read the beginning of the thread, but will this table be used only for humans? There are animals that are hermafrodites (I hope I got the English correct...) or whose sex is only identifiable after a period of time (days or months, usually). So, for researchers it would be interesting to have more options. Also, if you're doing statistics on something where the sexual option (and transgerderness) is important, then there should be some way to point that. Some people argue that gender is a spectrum. If you want to be very inclusive. Maybe you could use a 'float' and stick with 0 = woman, 1 = man (self documenting after all) with the option of '0.1 - 0.9' for people who feel in between. How efficient is 'float'? This would also work for animals that fall outside then normal male/female designation. Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Re: Male/female
Csaba Nagy wrote: On Fri, 2006-12-08 at 17:39, Bernhard Weisshuhn wrote: On Fri, Dec 08, 2006 at 05:26:22PM +0100, Harald Armin Massa [EMAIL PROTECTED] wrote: Now we just need fast, stable and native replication for The Girl That Every Man Secretly Wishes He Was Married To! I want replication WITH that girl! Any chance for 8.3? Well, all of you who ask for this, don't forget that the main mantra of open source is scratch your own itch ;-) You know, here in the US not that many years ago we had a Surgeon General who lost their job because she suggested that people scratch their own itch ;-) ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili [EMAIL PROTECTED] writes: I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. The first 4 executions of the servlet goes well, the 5th hangs. Afther that I use psql to do the last query (the one that hangs the system) of the servlet, also psql hangs. Look into the pg_locks view to find out what it's blocked on. Note that LOCK by default gets an ACCESS EXCLUSIVE lock, which means it will be blocked by *any* pre-existing access, even an open transaction that merely read the table awhile back. Are you sure you really need such a strong lock? I don't need a ACCESS EXCLUSIVE thanks a lot for your help. I luk at pg_locks, when my system hangs there are (it's right) loocked tables. It seems that if I put some delay between calls to the servlet all goes well. I can change lock level but ther is something wrong. Obviously I am doiung something wrong. To unlock the tables is not sufficient close the Statement and the Connection? tanks again Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange (maybe) behaviour of table lock
Edoardo Panfili [EMAIL PROTECTED] writes: It seems that if I put some delay between calls to the servlet all goes well. I can change lock level but ther is something wrong. Obviously I am doiung something wrong. To unlock the tables is not sufficient close the Statement and the Connection? Um, possibly not, if you're using connection-pooling software ... and even if you're not, I think closing the connection is asynchronous; it'd be possible to establish a new connection before the old one has terminated and released its locks. Rather than closing the connection, I think you need to do something explicit to commit your transaction. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance figures from DbMail list
Quick follow up on this, the guy who ran this test retested with a much newer version of MySQL and sent this message to the DBMail mailing list today. Ok, I just did the test on mysql 5.0.27. It took 73 seconds to deliver the 1000 messages. So, it's a good bit faster than 4.1.20's 95 seconds, but still pales in comparison to postgres' 9 seconds. Mysql was still peaking both cpu cores during delivery. On Thu, 07 Dec 2006 11:23:58 -0800 Michael Dean [EMAIL PROTECTED] wrote: Lars Kneschke wrote: Justin McAleer [EMAIL PROTECTED] schrieb: I think a test of 5.0 and 8.2 would be great! Recent benchmarks of the two show pg really blows the socks off mysql, so a confirmation of that in the email segmnent would be terrific!!! Michael ___ DBmail mailing list [EMAIL PROTECTED] https://mailman.fastxs.nl/mailman/listinfo/dbmail ___ DBmail mailing list [EMAIL PROTECTED] https://mailman.fastxs.nl/mailman/listinfo/dbmail David Goodenough wrote: The following appeared this afternoon on the DbMail list. As someone replied the MySql used is old, and the newer one is faster, but then 8.2 is faster than the older Postgresql versions. This was posted by:- Justin McAleer [EMAIL PROTECTED] I figured I would go ahead and toss this out for anybody that may be interested, since I was so shocked by the results. I have two servers set up for testing, one running postfix/dbmail and one running the database servers. The database machine is a dual core AMD (4400+ I believe) with 4 gigs of memory, with the database files living on a fiber connected Apple SAN (XRaid). I have dbmail compiled with mysql and pgsql, so all I need to do to switch between the two is change the driver in the conf file and restart. I'm using dbmail-lmtpd running on a unix socket. Finally, I have the postfix delivery concurrency set to 5. For mysql, I'm using a 4GB InnoDB sample config that comes in the CentOS rpm (increased the buffer pool to 2.5 gigs though). Version is 4.1.20. For postgres, I'm using the default variables except for increasing the shared buffers to 256MB, setting effective cache size to 3 GB, and random page cost to 2. Version is 8.1.4. I've sent a good amount of real mail to each setup as well, but for quantifiable results I have a perl script that sends gibberish of a configurable size (3kb here) to a single recipient. Since we're inserting into a DB, the recipient of the messages should have no bearing on delivery performance, barring postfix concurrency. For the test, I sent one batch of mail through so postfix would already have a full lmtp connection pool when I began the real test. I had 10 perl processes each sending 100 messages as fast as postfix would accept them, for a total of 1000 3KB messages. Results... Mysql: 95 seconds to deliver all 1000 messages. Both cores on the DB server were effectively peaked during delivery. Postgres: 10 seconds to deliver all 1000 messages. DBMail was really close to being able to deliver as fast as postfix could queue to local disk (within a second or two for 1000th message). The cores on the DB server looked to average around 45%/30% usage during delivery. The CPU usage is just based on watching top output, so keep that in mind... however with such a huge variance, even eyeballing it I'm confident in reporting it. ---(end of broadcast)--- TIP 1: 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 -- Matthew T. O'Connor V.P. Operations Terrie O'Connor Realtors 201-934-4900 begin:vcard fn:Matthew O'Connor n:O'Connor;Matthew org:Terrie O'Connor Realtors adr:;;75 E. Allendale Rd;Saddle River;NJ;07450;USA email;internet:[EMAIL PROTECTED] title:V.P. Operations tel;work:201-934-4900 x-mozilla-html:FALSE url:http://www.tocr.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Male/female
Scott Marlowe wrote: On Fri, 2006-12-08 at 10:44, John Meyer wrote: David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Seven genders? Even San Fransisco thinks that's over the top. Let's see. Male Female Hermaphrodite Trans (MTF) Trans (FTM) Neuter Just went in for my every-8-week blood donation. They have a new question in the screening form: gender at birth. So if you decide that you can classify gender (or more properly sex, as gender primarily relates to grammar) into a data type consisting of male and female, you can create whatever columns are necessary for your app: anatomical_sex_at_birth anatomical_sex_current anatomical_sex_desired_for_self chromosomal_sex preferred_anatomical_sex_of_partner Of course this breaks apart when dealing with that very rare syndrome (name escapes me) where the child appears female at birth but is actually a male whose male sex-organs descend and appear at puberty so I guess we need to add apparent_sex_at_birth. I realize that preferred_anatomical_sex_of_partner leaves a variety of unresolved possibilities but none as severe as those introduced by tetragametic chimerism. And there are others still resulting from the situation of in-progress transgender. But nobody said database design was easy. :) Cheers, Steve ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Male/female
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/06 09:31, John Meyer wrote: Second method might be better. Too much heat from declaring Males are True, Females are False? Of course, you could also do a one chracter gender M/F if you want to save space. Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFebelS9HxQb37XmcRAqBEAKC+j4K1JBaGmDT97ZZTWzkH9mnHLACg2nhZ dc5p75EU28La2LM7blNseEg= =/Wt6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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] Male/female
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/06 09:40, Leif B. Kristensen wrote: On Friday 8. December 2006 16:23, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I've done it two ways: * A bool column, with the understanding that true/false represents one gender or the other. * Create a domain, something like: CREATE DOMAIN gender_domain AS character varying(7) NOT NULL CONSTRAINT gender_domain_check CHECK VALUE)::text = 'male'::text) OR ((VALUE)::text = 'Female'::text))) I personally prefer the second, as it's self-documenting...is there any other/better way of doing it? There's actually an ISO standard (ISO 5218) for representing gender with numeric values: 0 = Unknown, 1 = Male, 2 = Female, 9 = not specified (or N/A). Well, I guess that's what I'll be using next time. - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFebhKS9HxQb37XmcRApRrAJ9nzAPIsJEDfKEv1SmIOCxQYV7sjACZAUZc RTxnJcStattu74wwPcp/VR8= =1IYS -END PGP SIGNATURE- ---(end of broadcast)--- TIP 1: 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] Re: Male/female
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/06 12:38, Matthew O'Connor wrote: Csaba Nagy wrote: On Fri, 2006-12-08 at 17:39, Bernhard Weisshuhn wrote: On Fri, Dec 08, 2006 at 05:26:22PM +0100, Harald Armin Massa [EMAIL PROTECTED] wrote: [snip] You know, here in the US not that many years ago we had a Surgeon General who lost their job because she suggested that people scratch their own itch ;-) She lost her job because she advocated schools teaching children how to scratch their own itch. As if they need instruction... - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFebknS9HxQb37XmcRAkZyAJ9W/iRiBbCJM3ojokyBf1jH1UMrjACdGE9Y hKRdTKdKppz6es2eMN36blM= =3PNr -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili [EMAIL PROTECTED] writes: It seems that if I put some delay between calls to the servlet all goes well. I can change lock level but ther is something wrong. Obviously I am doiung something wrong. To unlock the tables is not sufficient close the Statement and the Connection? Um, possibly not, if you're using connection-pooling software ... and even if you're not, I think closing the connection is asynchronous; it'd be possible to establish a new connection before the old one has terminated and released its locks. Tnaks again. Rather than closing the connection, I think you need to do something explicit to commit your transaction. I use connection.commit(); I spend many time to explain the bahaviour of the system: I some occasions the system use another connection to retrieve some information during the main connection. This explain the hangs but... why sometimes the system works. I do more tests. Thanks a lot again Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] strange (maybe) behaviour of table lock
Edoardo Panfili [EMAIL PROTECTED] writes: I spend many time to explain the bahaviour of the system: I some occasions the system use another connection to retrieve some information during the main connection. This explain the hangs but... why sometimes the system works. I do more tests. Try turning on statement logging in the server. Looking at the sequence of SQL commands actually issued to the server by the different clients would probably be informative. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] strange (maybe) behaviour of table lock
On Fri, 2006-12-08 at 09:16, Edoardo Panfili wrote: I have a question regarding a strange behaviour (for me, maybe that this is desidered feature) of LOCK on tables. I am using postgres 8.2 I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. Before we go any further, what are you trying to accomplish by this lock? Perhaps there's a better postgresqlish approach than a table lock. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] strange (maybe) behaviour of table lock
Scott Marlowe wrote: On Fri, 2006-12-08 at 09:16, Edoardo Panfili wrote: I have a question regarding a strange behaviour (for me, maybe that this is desidered feature) of LOCK on tables. I am using postgres 8.2 I have a servlet that uses connection pools. The servlet do LOCK table,table2,table3,table4 then do some select (I am testing the code, I will put the update in the future) an then I close instruction and connection. Before we go any further, what are you trying to accomplish by this lock? Perhaps there's a better postgresqlish approach than a table lock. You are right, also Tom said that. At a lower level of lock all goes well but I'd like to know what is going wrong. This is a bug of my code (obviously) and I am investigating. thak you Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
On Fri, Dec 08, 2006 at 11:13:03AM -0600, Scott Marlowe wrote: On Fri, 2006-12-08 at 10:44, John Meyer wrote: David Fetter wrote: On Fri, Dec 08, 2006 at 03:23:11PM -, Raymond O'Donnell wrote: Just wondering.how do list member represent gender when storing details of people in a database? I usually use a table called gender which has one TEXT column, that being its primary key. For one client I had, there were seven rows in this table. Seven genders? Even San Fransisco thinks that's over the top. Let's see. Male Female Hermaphrodite This read, Intersexed Trans (MTF) Trans (FTM) Neuter and... I can't think of a seventh possibility. Decline to state Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Male/female
Isn't that why we have null? On Fri, 8 Dec 2006, Steve Wampler wrote: Andreas Kretschmer wrote: What about with Hermaphroditism? More seriously - is the gender something you always know? There are situations in the US where you cannot force someone to divulge their gender. So you may need an 'unreported' value of some sort. -- Steve Wampler -- [EMAIL PROTECTED] The gods that smiled on your birth are now laughing out loud. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Male/female
On 8 Dec 2006 at 15:12, Jorge Godoy wrote: I haven't read the beginning of the thread, but will this table be used only for humans? There are animals that are hermafrodites (I hope Many thanks to all who responded - I had no idea of the monster I was creating in starting this thread! Yes, the table is used only for humans; it's part of some administrative software I'm writing for an educational institution, and the primary purpose of the gender column is to help the users cope with a problem new to the west of Ireland - the large influx of immigrants from Africa, eastern Europe and elsewhere means that it's no longer possible to tell a student's gender just from their name! --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] strange (maybe) behaviour of table lock
Tom Lane wrote: Edoardo Panfili [EMAIL PROTECTED] writes: I spend many time to explain the bahaviour of the system: I some occasions the system use another connection to retrieve some information during the main connection. This explain the hangs but... why sometimes the system works. I do more tests. Try turning on statement logging in the server. Looking at the sequence of SQL commands actually issued to the server by the different clients would probably be informative. I did some debug. I did non consider the execution of a second transaction inside the first, this is my error. The only way to avoid problem with my code is to lower the lock level, I can't avoid the nesting of the transactions. The systems works anyway two hours ago, I can't figure how to reproduce that occasion. thanks a lot to all Edoardo -- [EMAIL PROTECTED] AIM: edoardopn Jabber: [EMAIL PROTECTED] tel:075 9142766 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
On 8 Dec 2006 at 11:13, Scott Marlowe wrote: Male Female Hermaphrodite Trans (MTF) Trans (FTM) Neuter and... I can't think of a seventh possibility. How about just plain confused?? --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Male/female
On Fri, 8 Dec 2006, Raymond O'Donnell wrote: Yes, the table is used only for humans; it's part of some administrative software I'm writing for an educational institution, and the primary purpose of the gender column is to help the users cope with a problem new to the west of Ireland - the large influx of immigrants from Africa, eastern Europe and elsewhere means that it's no longer possible to tell a student's gender just from their name! --Ray. Ray, darest I point out that that's never been possible in English anyway? There are dozens if not hundreds of androgenous names - Pat and Tracy come immediately to mind, and there are countless others! RT -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] concatenation operator || with null array
NULL concatenated to anything is NULL. Try this: UPDATE test SET myint = COALESCE(myint || ARRAY[123], ARRAY[123]) WHERE id = 1; Or: UPDATE test SET myint = CASE WHEN myint IS NULL THEN ARRAY[123] ELSE myint || ARRAY[123] END WHERE id = 1; An empty array can be displayed as ARRAY[NULL], but defaults to type TEXT. An explicit empty integer array would be ARRAY[NULL]::INTEGER[]. NULL arrays are not handled entirely consistently, though. Sometimes it acts like a NULL, and sometimes it acts like a container of NULL. -- Brandon Aiken CS/IT Systems Engineer -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of stroncococcus Sent: Wednesday, December 06, 2006 5:43 PM To: pgsql-general@postgresql.org Subject: [GENERAL] concatenation operator || with null array Hello! When I try to fill an array with the concatenation operator, like UPDATE test SET myint = myint || ARRAY[123] WHERE id = 1 that before that statement was null, then it is also null after that statement. But if there is already something in that array and I execute that statement, then everything works fine and one can find the 123 there, too. Is this the normal behavior? Is there a way to concatenate to null arrays as well, or do I have to test this inside my script, and if it is null fill it normal for the first time? Best regards, Kai ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
On 8 Dec 2006 at 12:17, Richard Troy wrote: Ray, darest I point out that that's never been possible in English anyway? There are dozens if not hundreds of androgenous names - Pat and Tracy come immediately to mind, and there are countless others! You're correct, of course - but this is the reason I was given when asked to include it. --Ray. -- Raymond O'Donnell Director of Music, Galway Cathedral, Galway, Ireland [EMAIL PROTECTED] -- ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
On 12/8/06, Joshua D. Drake [EMAIL PROTECTED] wrote: On Fri, 2006-12-08 at 09:31 -0700, John Meyer wrote: COPY gender (gender_pk, gender) FROM stdin; 0(unknown) 1Male 2Female 3Trans \. Not to take this completely off track, but isn't transgendered not so much a gender as it is a process of moving from one gender to another? Yes, but further I don't know of any country that recognizes anything but Male or Female. what if you are maintaining a database for a surgeon who performs sex changes? we may have to consider the element of time here! I'd go with a composite type with custom input and output functions (for privacy)! What about simple bacteria (unisexual)? hm. maybe a new branch of calculus is in order here. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Proposed ISO solution to Male/female
big-snip Male Female Hermaphrodite This read, Intersexed Trans (MTF) Trans (FTM) Neuter and... I can't think of a seventh possibility. Decline to state ISO 5218 takes 22 pages to give us four oddly placed values for male, female, and two versions of null, unknown and not aplicable. Interestingly, it doesn't include declined to state. The values are as previously stated: 0 = unknown 1 = male 2 = female 9 = not aplicable As pointed out above, there really are more legitimate values. To track all of them and still be aproximagely ISO compatible, I propose the following. Based on the observation that ISO 5318 mathematically specifies male as odd and female as even, the y-chromosome containing sexes (which include hermaphrodites), shall be odd. This leaves unknown, as even, and perhaps neuter can be not aplicable, since we don't know. ... This does leave declined to state as a valid form of null. From this I propose the following: 0 = unknown 1 = male 2 = female 3 = hermaphrodite 4 = female to male transgender 5 = male to female transgender 6 = 7 = 8 = declined to state 9 = Neuter - Not applicable One could also move the blanks around like this, which might be useful: 0 = unknown 1 = male 2 = female 3 = 4 = female to male transgender 5 = male to female transgender 6 = 7 = hermaphrodite 8 = declined to state 9 = Neuter - Not applicable Hmmm... Easy to write the various functions making this a new datatype... Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 or 202-747-1263 [EMAIL PROTECTED], http://ScienceTools.com/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Male/female
Steve Crawford wrote: Of course this breaks apart when dealing with that very rare syndrome (name escapes me) where the child appears female at birth but is actually a male whose male sex-organs descend and appear at puberty so I guess we need to add apparent_sex_at_birth. It turns out there are lots of ways apparent and genetic gender can differ - some experts estimate that as many as 2% of all births do not fall within strict definitions of M/F, although many might never be discovered. That and the increasing number of elective transsexuals argues that the kind of discussion we're having now may be de rigueur for DBAs in the future, at least in the medical field. - John Burger MITRE ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Marking indexes out of date (WAS: loading data, creating indexes, clustering, vacuum) feature request?
Angva wrote: Looking for a small bit of advice... I have a script that updates several tables with large amounts of data. Before running the updates, it drops all indexes for optimal performance. When the updates have finished, I run the following procedure: recreate the indexes cluster the tables vacuum full analyze on the tables Hi all, I'd like to see a general way to take indexes off line without actually losing their definitions. For example, something like ALTER TABLE [EN | DIS] ABLE INDEXES, ALTER INDEX [EN | DIS] ABLE, etc. This could also be used internally when a backend encounters an error reading/writing an index. Rather than refusing to execute queries, it could just ignore indexes it knows are disabled or bad in some way and re-plan as needed. This would have two benefits. First, the above scenerio would be much simpler. Rather than dropping and re-creating new indexes, you could just disable and then re-enable them without having any knowledge of their structure. Secondly, it would allow us to put indexes in an alternate table space on a non-redundant volume and, in the case of a drive failure, be able to limp along, and get the system back to normal simply by replacing the disk and issuing a REINDEX command. I realize there are a couple gotchas with this. For example, what to do with unique indexes? Perhaps a backend would still need to refuse to do update/inserts on a table with degraded unique indexes, unless the index was disabled explicitly? And then, refuse to rebuild/re-enable the index as normal if non-unique values found? Thx for considering :-) -Glen Parker ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pg_dump: a way to not dump indexes and other objects, or a way to not create them on restore, feature request?
Hi all, Since PITR works well, my use of pg_dump has shifted. Rather than using it as a backup tool, I now use it as a snapshotting tool. At the end of each month we do an ASCII dump to keep around, so if we ever need to, we can see the data as it was any number of months or years ago. Not a backup at all, just a raw data archive. These archives do not need to hold all our data, for example, system logs would be useless later. There also is no reason to include indexes. Ignoring extranious tables and indexes is a great way to keep the archive small and keep the time to restore as low as possible. So, it would be great if pg_dump could accept some sort of argument to make it simply not dump certain types of objects. Indexes, views, functions, etc. Thx for considering :-) -Glen Parker ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance figures from DbMail list
This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... -Original Message- From: Matthew T. O'Connor [mailto:[EMAIL PROTECTED] Sent: den 8 december 2006 19:35 To: David Goodenough Cc: pgsql-general@postgresql.org Subject: Re: Performance figures from DbMail list Quick follow up on this, the guy who ran this test retested with a much newer version of MySQL and sent this message to the DBMail mailing list today. Ok, I just did the test on mysql 5.0.27. It took 73 seconds to deliver the 1000 messages. So, it's a good bit faster than 4.1.20's 95 seconds, but still pales in comparison to postgres' 9 seconds. Mysql was still peaking both cpu cores during delivery. On Thu, 07 Dec 2006 11:23:58 -0800 Michael Dean [EMAIL PROTECTED] wrote: Lars Kneschke wrote: Justin McAleer [EMAIL PROTECTED] schrieb: I think a test of 5.0 and 8.2 would be great! Recent benchmarks of the two show pg really blows the socks off mysql, so a confirmation of that in the email segmnent would be terrific!!! Michael ___ DBmail mailing list [EMAIL PROTECTED] https://mailman.fastxs.nl/mailman/listinfo/dbmail ___ DBmail mailing list [EMAIL PROTECTED] https://mailman.fastxs.nl/mailman/listinfo/dbmail David Goodenough wrote: The following appeared this afternoon on the DbMail list. As someone replied the MySql used is old, and the newer one is faster, but then 8.2 is faster than the older Postgresql versions. This was posted by:- Justin McAleer [EMAIL PROTECTED] I figured I would go ahead and toss this out for anybody that may be interested, since I was so shocked by the results. I have two servers set up for testing, one running postfix/dbmail and one running the database servers. The database machine is a dual core AMD (4400+ I believe) with 4 gigs of memory, with the database files living on a fiber connected Apple SAN (XRaid). I have dbmail compiled with mysql and pgsql, so all I need to do to switch between the two is change the driver in the conf file and restart. I'm using dbmail-lmtpd running on a unix socket. Finally, I have the postfix delivery concurrency set to 5. For mysql, I'm using a 4GB InnoDB sample config that comes in the CentOS rpm (increased the buffer pool to 2.5 gigs though). Version is 4.1.20. For postgres, I'm using the default variables except for increasing the shared buffers to 256MB, setting effective cache size to 3 GB, and random page cost to 2. Version is 8.1.4. I've sent a good amount of real mail to each setup as well, but for quantifiable results I have a perl script that sends gibberish of a configurable size (3kb here) to a single recipient. Since we're inserting into a DB, the recipient of the messages should have no bearing on delivery performance, barring postfix concurrency. For the test, I sent one batch of mail through so postfix would already have a full lmtp connection pool when I began the real test. I had 10 perl processes each sending 100 messages as fast as postfix would accept them, for a total of 1000 3KB messages. Results... Mysql: 95 seconds to deliver all 1000 messages. Both cores on the DB server were effectively peaked during delivery. Postgres: 10 seconds to deliver all 1000 messages. DBMail was really close to being able to deliver as fast as postfix could queue to local disk (within a second or two for 1000th message). The cores on the DB server looked to average around 45%/30% usage during delivery. The CPU usage is just based on watching top output, so keep that in mind... however with such a huge variance, even eyeballing it I'm confident in reporting it. ---(end of broadcast)--- TIP 1: 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 -- Matthew T. O'Connor V.P. Operations Terrie O'Connor Realtors 201-934-4900 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Performance figures from DbMail list
On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... Oh man, that poor guy. He's got 4 or 5 machines in a cluster, and he's still not catching up to the one machine postgresql server. And he's switching because he wants better reliability? Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a half dozen other ways to get high reliability with postgresql. I wonder what version of postgresql he was testing. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Male/female
Richard Troy wrote: On Fri, 8 Dec 2006, Raymond O'Donnell wrote: Yes, the table is used only for humans; it's part of some administrative software I'm writing for an educational institution, and the primary purpose of the gender column is to help the users cope with a problem new to the west of Ireland - the large influx of immigrants from Africa, eastern Europe and elsewhere means that it's no longer possible to tell a student's gender just from their name! --Ray. Ray, darest I point out that that's never been possible in English anyway? There are dozens if not hundreds of androgenous names - Pat and Tracy come immediately to mind, and there are countless others! Or with Irish names: Sheridan, Tara, Shay, Shannon, Rory, Ronan, Riley, Renny, Regan, Quinn, Murphy, Keverne, Keeley, Kane, Erin, Darby, Dara, Cary, ... Yes, I was researching baby names not all that long ago... :) Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Performance figures from DbMail list
Scott Marlowe wrote: On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... Oh man, that poor guy. He's got 4 or 5 machines in a cluster, and he's still not catching up to the one machine postgresql server. And he's switching because he wants better reliability? Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a half dozen other ways to get high reliability with postgresql. I wonder what version of postgresql he was testing. Please, remove pgpool from your list of reliable postgresql tools. It's decent, but child procs tend to go zombie from time to time. -- erik jones [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Performance figures from DbMail list
On Fri, 2006-12-08 at 15:44, Erik Jones wrote: Scott Marlowe wrote: On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... Oh man, that poor guy. He's got 4 or 5 machines in a cluster, and he's still not catching up to the one machine postgresql server. And he's switching because he wants better reliability? Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a half dozen other ways to get high reliability with postgresql. I wonder what version of postgresql he was testing. Please, remove pgpool from your list of reliable postgresql tools. It's decent, but child procs tend to go zombie from time to time. No, I don't think I will. I've used it and tested it quite thoroughly, and have never had that happen. Bad hardware on your end maybe? Or an older version, or a bad compiler? I've found it to be very stable and reliable. If you've got a reproduceable test case I'm sure Tatsuo (sp) would love to see it. ---(end of broadcast)--- TIP 1: 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] Male/female
Steve Crawford wrote: Richard Troy wrote: On Fri, 8 Dec 2006, Raymond O'Donnell wrote: Yes, the table is used only for humans; it's part of some administrative software I'm writing for an educational institution, and the primary purpose of the gender column is to help the users cope with a problem new to the west of Ireland - the large influx of immigrants from Africa, eastern Europe and elsewhere means that it's no longer possible to tell a student's gender just from their name! --Ray. Ray, darest I point out that that's never been possible in English anyway? There are dozens if not hundreds of androgenous names - Pat and Tracy come immediately to mind, and there are countless others! Or with Irish names: Sheridan, Tara, Shay, Shannon, Rory, Ronan, Riley, Renny, Regan, Quinn, Murphy, Keverne, Keeley, Kane, Erin, Darby, Dara, Cary, ... Yes, I was researching baby names not all that long ago... :) Cheers, Steve ---(end of broadcast)--- TIP 6: explain analyze is your friend Man this thing has strayed off topic and I am joining in! Most of these Irish Names are family names that have been assumed as first names (Murphy, Quinn, Riley, etc) that FYI you will almost never find in Ireland! Although never heard of Ronán being used for a girl but I there is a boy called Eve and a girl called Adam somewhere! Oisín (A *real* Irish Name) smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] Marking indexes out of date (WAS: loading data, creating indexes, clustering, vacuum) feature request?
On Fri, Dec 08, 2006 at 12:49:30PM -0800, Glen Parker wrote: I'd like to see a general way to take indexes off line without actually losing their definitions. For example, something like ALTER TABLE [EN | DIS] ABLE INDEXES, ALTER INDEX [EN | DIS] ABLE, etc. This could also be used internally when a backend encounters an error reading/writing an index. Rather than refusing to execute queries, it could just ignore indexes it knows are disabled or bad in some way and re-plan as needed. One issue would be that even disabled indexes would need to be updated when there are new rows. If you don't update the index when it's disabled, then re-enabling will essentially need to rebuild the index. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Performance figures from DbMail list
Scott Marlowe wrote: On Fri, 2006-12-08 at 15:44, Erik Jones wrote: Scott Marlowe wrote: On Fri, 2006-12-08 at 15:04, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... Oh man, that poor guy. He's got 4 or 5 machines in a cluster, and he's still not catching up to the one machine postgresql server. And he's switching because he wants better reliability? Guess he's never heard of pgpool, slony, mammoth replicator, cjdbc, or a half dozen other ways to get high reliability with postgresql. I wonder what version of postgresql he was testing. Please, remove pgpool from your list of reliable postgresql tools. It's decent, but child procs tend to go zombie from time to time. No, I don't think I will. I've used it and tested it quite thoroughly, and have never had that happen. Bad hardware on your end maybe? Or an older version, or a bad compiler? I've found it to be very stable and reliable. If you've got a reproduceable test case I'm sure Tatsuo (sp) would love to see it. pgpool -h reports v. 3.1. Note that this is pgpool-I and that the release notes for the version we have say that an issue with procs dying was fixed -- while it is certainly much better than it was in version previous to 3.1, we have seen it happen on occasion. Test case? Hah. This tends to happen during off hours on our high-load web servers so the best we can do is keep an eye on things and restart when we catch it. I see that pgpool-II has been released and since been integrated with heartbeat which definitely sounds promising. I'm going to show it to our deciders... -- erik jones [EMAIL PROTECTED] software development emma(r) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Marking indexes out of date (WAS: loading data, creating
Martijn van Oosterhout wrote: On Fri, Dec 08, 2006 at 12:49:30PM -0800, Glen Parker wrote: I'd like to see a general way to take indexes off line without actually losing their definitions. For example, something like ALTER TABLE [EN | DIS] ABLE INDEXES, ALTER INDEX [EN | DIS] ABLE, etc. This could also be used internally when a backend encounters an error reading/writing an index. Rather than refusing to execute queries, it could just ignore indexes it knows are disabled or bad in some way and re-plan as needed. One issue would be that even disabled indexes would need to be updated when there are new rows. If you don't update the index when it's disabled, then re-enabling will essentially need to rebuild the index. That's what I had in mind. You could just as easily blow away the index file(s). It's just that I don't want it to toss the index *definition*. To continued to update such an index would be to completely negate the benefit of disabling it! -Glen ---(end of broadcast)--- TIP 1: 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] Performance figures from DbMail list
On Fri, 2006-12-08 at 22:04 +0100, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... To be fair, he was running the cluster on a 100Mbps network. Depending on his setup, that may have been his bottleneck. However, there's a good chance that's not his only problem. Especially if he's so sold on MySQL Cluster that he's trying to find a place to use it. Regards, Jeff Davis ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Proposed ISO solution to Male/female
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 12/08/06 14:40, Richard Troy wrote: big-snip [snip] 0 = unknown 1 = male 2 = female 3 = 4 = female to male transgender 5 = male to female transgender 6 = 7 = hermaphrodite 8 = declined to state 9 = Neuter - Not applicable Hmmm... Easy to write the various functions making this a new datatype... Is TG a biological state or a social state? - -- Ron Johnson, Jr. Jefferson LA USA Is common sense really valid? For example, it is common sense to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that common sense is obviously wrong. -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFeePuS9HxQb37XmcRAmlqAKClbhVBWXtc0QPrrg5dju4+EknmYQCgmwmo UpoNGTbY1o6zcygdKivlh5w= =vTq9 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Performance figures from DbMail list
On Fri, 2006-12-08 at 16:13, Jeff Davis wrote: On Fri, 2006-12-08 at 22:04 +0100, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... To be fair, he was running the cluster on a 100Mbps network. Depending on his setup, that may have been his bottleneck. However, there's a good chance that's not his only problem. Especially if he's so sold on MySQL Cluster that he's trying to find a place to use it. No, read on, he upgraded to gigabit halfway through the thread, and went from 50 to 70 tps. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Performance figures from DbMail list
Jeff Davis wrote: On Fri, 2006-12-08 at 22:04 +0100, Mikael Carneholm wrote: This link adds to the joy... http://forums.mysql.com/read.php?25,93181,93181 So the most popular free database in the world is a lousy performing product that accepts 'gabba gabba hey' as a valid timestamp. Someone please, give me a reason not to get cynical... To be fair, he was running the cluster on a 100Mbps network. Depending on his setup, that may have been his bottleneck. However, there's a good chance that's not his only problem. Especially if he's so sold on MySQL Cluster that he's trying to find a place to use it. Later in the thread he gets gigabit working which does help things somewhat, but not enough. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq