Re: [GENERAL] PostgreSQL Installer for Windows x64

2007-06-20 Thread Magnus Hagander
On Wed, Jun 20, 2007 at 10:17:10AM +0900, EBIHARA, Yuichiro wrote: Hi, Can I get a PostgreSQL Installer for Windows x64(EM64T)? That for 32bit Windows is available at http://www.postgresql.org/ftp/win32/ but I need x64 native version. There is no such thing. PostgreSQL 64-bit is currently

Re: [GENERAL] Excell

2007-06-20 Thread Garry Saddington
On Wednesday 20 June 2007 03:09, Bob Pawley wrote: Thanks Does one version of ODBC work for all versions of Excel and Postgresql. I am wanting to transfer one or two tables from Excel and manipulate the information in Postgresql then transfer the results back to Excel as a single table. I

Re: [GENERAL] Dynamic Log tigger (plpgsql)

2007-06-20 Thread Sergey Konoplev
My Question: How can I do OLD.columnName != NEW.columnName if I don't know what the columnNames are at Compile Time? I have the columnName in a variable. I suggest you use plpython. In this case you'll be able to do it. TD['old'][colNameVar] != TD['new'][colNameVar] -- Regards, Sergey

Re: [GENERAL] PostgreSQL Installer for Windows x64

2007-06-20 Thread EBIHARA, Yuichiro
Magnus, Thank you for your quick reply. Can I get a PostgreSQL Installer for Windows x64(EM64T)? That for 32bit Windows is available at http://www.postgresql.org/ftp/win32/ but I need x64 native version. There is no such thing. PostgreSQL 64-bit is currently only supported on Unix

Re: [GENERAL] [NOVICE] Recursive relationship - preventing cross-index entries.

2007-06-20 Thread Richard Huxton
Michael Glaesemann wrote: INSERT INTO accounts_receivable_receipts (accounts_receivable_id, receipt_id) VALUES (2, 1), (4, 3), (6, 1), (5, 3); I have not done much accounting-style design, and I don't think this is really the best way to set these up (for example, I think it's a bit odd to

Re: [GENERAL] PostgreSQL Installer for Windows x64

2007-06-20 Thread Magnus Hagander
On Wed, Jun 20, 2007 at 04:31:40PM +0900, EBIHARA, Yuichiro wrote: Magnus, Thank you for your quick reply. Can I get a PostgreSQL Installer for Windows x64(EM64T)? That for 32bit Windows is available at http://www.postgresql.org/ftp/win32/ but I need x64 native version. There

Re: [GENERAL] unexpected shutdown

2007-06-20 Thread Marco Colombo
[EMAIL PROTECTED] wrote: [EMAIL PROTECTED] writes: My database has shutdown several times in the last couple days. I have no idea why. I am running centos and I have not rebooted the server or made any configuration changes. Oh, I forgot. You do have plenty of swap space compared to RAM,

Re: [GENERAL] [NOVICE] Recursive relationship - preventing cross-index entries.

2007-06-20 Thread Sean Davis
Andrew Maclean wrote: I got no answer so I am trying again. In a nutshell, if I have a recrusive relationship as outlined below, how do I implement a rule for the adjustments table that prevents the entry of an Id into the Ref column if the id exists in the Id column and vice versa? If

[GENERAL] copying indexes and f.keys from a template table

2007-06-20 Thread Enrico Sirola
Hello, I'm trying to build a set of plpgsql functions in order to ease partitioning by date, let's say one table per day for a total of 30 days. I have plpgsql functions to create the partition tables (having a date suffix) and to manage a retention period, however I still need to solve a

Re: [GENERAL] regexp searching in arrays not working?

2007-06-20 Thread Rhys Stewart
thats cool, thanks. 2007/6/19, Tom Lane [EMAIL PROTECTED]: Rhys Stewart [EMAIL PROTECTED] writes: Is regex searching not functioning (as i expect it to?) ~ expects the pattern on the right, not the left. So it's taking your array entries as patterns, which don't match the data 'Trans'.

Re: [GENERAL] pg_restore out of memory

2007-06-20 Thread Francisco Reyes
Would it help at all to run a ktrace? Or are the logs I have supplied enough? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Need help with generic query

2007-06-20 Thread Rodrigo De León
On Jun 20, 5:55 am, David Abrahams [EMAIL PROTECTED] wrote: The problem is, psql is complaining: ERROR: wrong record type supplied in RETURN NEXT Try: ... RETURNS SETOF RECORD ... ---(end of broadcast)--- TIP 1: if posting/reading through

Re: [GENERAL] Problem compiling on CentOS

2007-06-20 Thread Robin Ericsson
On 6/20/07, Robin Ericsson [EMAIL PROTECTED] wrote: `-mcpu=' is deprecated. Use `-mtune=' or '-march=' instead. Ok solved. This is what the configure-script barfs on. Hacking the spec-file to change this flag removes this problem. -- regards, Robin

[GENERAL] Surrogate VS natural keys

2007-06-20 Thread Naz Gassiep
OK so which is the correct way to do it? E.g., Say I have a table with users, and a table with clubs, and a table that links them. Each user can be in more than one club and each club has more than one member. Standard M:M relationship. Which link table is the right way to do it? This: CREATE

Re: [GENERAL] Excell

2007-06-20 Thread Ireneusz Pluta
Bob Pawley napisał(a): Thanks Does one version of ODBC work for all versions of Excel and Postgresql. I am wanting to transfer one or two tables from Excel and manipulate the information in Postgresql then transfer the results back to Excel as a single table. I am using Excel 2000 and

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread brian
Naz Gassiep wrote: OK so which is the correct way to do it? E.g., Say I have a table with users, and a table with clubs, and a table that links them. Each user can be in more than one club and each club has more than one member. Standard M:M relationship. Which link table is the right way to do

Re: [GENERAL] PostgreSQL Installer for Windows x64

2007-06-20 Thread marcelo Cortez
Magnus , folks The only caveat i found in winxp64 is with psqlODBC my application dont work very well on it into xp64 platform, the work around was change odbc profile in favor of dns file , with dns file my application return to work, ok. BTW i using linux server without problems, but my

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Rich Shepard
On Wed, 20 Jun 2007, brian wrote: The former uses a primary key across both columns to enforce a unique constraint. In the latter, you have a seperate ID column, which does not enforce that constraint. And you have to ask yourself if you'll ever be referencing that ID column for anything at

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Martijn van Oosterhout
On Wed, Jun 20, 2007 at 08:39:23AM -0700, Rich Shepard wrote: Also, the reason for a third, M-M, table is to relate multiple players and multiple clubs. If you think of the logic involved, your third table has only one row for each player-club combination. Therefore, each row is unique by

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Joshua D. Drake
Martijn van Oosterhout wrote: On Wed, Jun 20, 2007 at 08:39:23AM -0700, Rich Shepard wrote: Also, the reason for a third, M-M, table is to relate multiple players and multiple clubs. If you think of the logic involved, your third table has only one row for each player-club combination.

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Rich Shepard
On Wed, 20 Jun 2007, Martijn van Oosterhout wrote: While true in this simple case, it can quickly become more complicated if your relationship starts gaining attributes. For example, if you add start and stop dates, so the (player,club) combination is not unique anymore. If you track invoices,

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Richard Broersma Jr
--- Joshua D. Drake [EMAIL PROTECTED] wrote: The value of a surrogate key is easy retrieval and really has nothing to do with normalization or proper modeling. I often add a surrogate key, even when one is not required just so I don't have to worry about have a 4 element where clause.

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Joshua D. Drake
Richard Broersma Jr wrote: --- Joshua D. Drake [EMAIL PROTECTED] wrote: The value of a surrogate key is easy retrieval and really has nothing to do with normalization or proper modeling. I often add a surrogate key, even when one is not required just so I don't have to worry about have a 4

Re: [GENERAL] [pgadmin-support] Problem editing tables (geom columns)

2007-06-20 Thread Pedro Doria Meunier
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 (First of all sorry for cross-posting but I feel this is a matter that interests all recipients) Thread on pgadmin support: http://www.pgadmin.org/archives/pgadmin-support/2007-06/msg00046.php Hello Dave, This behavior (trying to show the entire

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Michael Glaesemann
On Jun 20, 2007, at 11:28 , Joshua D. Drake wrote: Richard Broersma Jr wrote: I've often wondered about this. Since PostgreSQL allows FOREIGN KEYS to be referenced from UNIQUE (non-primary) natural keys, couldn't the schema be designed so that every table has a surrogate PRIMARY KEY and

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Joshua D. Drake
Michael Glaesemann wrote: On Jun 20, 2007, at 11:28 , Joshua D. Drake wrote: Richard Broersma Jr wrote: I've often wondered about this. Since PostgreSQL allows FOREIGN KEYS to be referenced from UNIQUE (non-primary) natural keys, couldn't the schema be designed so that every table has a

[GENERAL] A problem in inheritance

2007-06-20 Thread Talha Khan
Hi Guyz, I need some help in an inheritance issue . The scenario is as follows : THE SAMPLE DDL: CREATE TABLE account_login ( account_id int4 NOT NULL, account_login_time timestamptz NOT NULL DEFAULT now(), ip_address varchar(32) NOT NULL, originating_source varchar(32) NOT NULL

Re: [GENERAL] Surrogate VS natural keys

2007-06-20 Thread Merlin Moncure
On 6/20/07, Richard Broersma Jr [EMAIL PROTECTED] wrote: --- Joshua D. Drake [EMAIL PROTECTED] wrote: The value of a surrogate key is easy retrieval and really has nothing to do with normalization or proper modeling. I often add a surrogate key, even when one is not required just so I

[GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Sean Murphy
Maybe this is a well duh kind of question, or maybe there's no straightforward way to do it, but is there any way that I could have a pg function initiate a process on the host system? Specifically I'd like to script an email to send off on an insert trigger, but the ability to initiate system

Re: [GENERAL] allocate chunk of sequence

2007-06-20 Thread Scott Marlowe
PFC wrote: The chunk to be allocated is not the same size, so to set the increment value will not help. I'm sometimes not that subtle, so I'd just use a BIGINT sequence. Think about the largest chunk you'll ever get (probably less than 2^30 rows, yes ?), set this sequence increment to

Re: [GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Joshua D. Drake
Sean Murphy wrote: Maybe this is a well duh kind of question, or maybe there's no straightforward way to do it, but is there any way that I could have a pg function initiate a process on the host system? Yeah you can use any of the untrusted pl languages for that. Specifically I'd like to

Re: [GENERAL] persistent db connections in PHP

2007-06-20 Thread Scott Marlowe
[EMAIL PROTECTED] wrote: This seems to be a problem with PHP, or at least my set up. I'm writing pages in basically the same way. Each page has an include at the top that gets you a database session. The function, either pg_connect() or mysql_connect(), is supposed to either create a new

Re: [GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Shoaib Mir
If you can use tcl based pl function, the this might help you here -- http://sourceforge.net/projects/pgmail/ -- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 6/20/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Sean Murphy wrote: Maybe this is a well duh kind of question, or maybe there's

Re: [GENERAL] persistent db connections in PHP

2007-06-20 Thread Raymond O'Donnell
[EMAIL PROTECTED] wrote: However, with this new Postgres site, I don't have access to my temp tables after I've traversed another pg_connect. So PHP is either creating a new connection, or giving me another session, not the one which I created my tables in. You wouldn't expect to be given

Re: [GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Steve Atkins
On Jun 20, 2007, at 12:07 PM, Sean Murphy wrote: Maybe this is a well duh kind of question, or maybe there's no straightforward way to do it, but is there any way that I could have a pg function initiate a process on the host system? You can use pl/perlu or any of the other untrusted

Re: [GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Philip Hallstrom
Maybe this is a well duh kind of question, or maybe there's no straightforward way to do it, but is there any way that I could have a pg function initiate a process on the host system? Specifically I'd like to script an email to send off on an insert trigger, but the ability to initiate system

Re: [GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Andrew Sullivan
On Wed, Jun 20, 2007 at 12:55:23PM -0700, Steve Atkins wrote: Another approach, and one that can be more robust in the case of external failures, is to have the trigger put the message it wants to send into a queue table and have an external process that monitors the table (via polling or

Re: [GENERAL] standard LOB support

2007-06-20 Thread Scott Marlowe
EBIHARA, Yuichiro wrote: Hi, I'm developing a software that supports several RDBMSs including PostgreSQL. The software needs an ability to handle large objects and now it uses 'bytea' datatype for binary data and 'text' for text data. But for portability, I'd rather use BLOB and CLOB defined

[GENERAL] Experiences of PostgreSQL on-disk bitmap index patch

2007-06-20 Thread Christan Josefsson
Hi! I'm working on building a PostgreSQL based data warehouse, and I'm thus very interested in any experiences and usage of the PostgreSQL bitmap index patches (which I've found on pgsql-patches). Anyone using these patchese on production systems? Anyone know if the patches run on latest stable

Re: [GENERAL] VACUUM ANALYZE extremely slow

2007-06-20 Thread Scott Marlowe
Sergei Shelukhin wrote: This is my first (and, by the love of the God, last) project w/pgsql and everything but the simplest selects is so slow I want to cry. This is especially bad with vacuum analyze - it takes several hours for a database of mere 15 Gb on a fast double-core server w/2Gb of

Re: [GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Tom Lane
Andrew Sullivan [EMAIL PROTECTED] writes: On Wed, Jun 20, 2007 at 12:55:23PM -0700, Steve Atkins wrote: Another approach, and one that can be more robust in the case of external failures, is to have the trigger put the message it wants to send into a queue table and have an external process

Re: [GENERAL] Experiences of PostgreSQL on-disk bitmap index patch

2007-06-20 Thread Joshua D. Drake
Christan Josefsson wrote: Hi! I'm working on building a PostgreSQL based data warehouse, and I'm thus very interested in any experiences and usage of the PostgreSQL bitmap index patches (which I've found on pgsql-patches). Anyone using these patchese on production systems? Anyone know if

Re: [GENERAL] Experiences of PostgreSQL on-disk bitmap index patch

2007-06-20 Thread Alexander Staubo
On 6/20/07, Christan Josefsson [EMAIL PROTECTED] wrote: Anyone using these patchese on production systems? If these are the same patches that were made for Bizgres, then they are bound to be in use in some current production systems of that version of PostgreSQL. If there's any PgSQL

Re: [GENERAL] Experiences of PostgreSQL on-disk bitmap index patch

2007-06-20 Thread Tom Lane
Alexander Staubo [EMAIL PROTECTED] writes: On 6/20/07, Christan Josefsson [EMAIL PROTECTED] wrote: If there's any PgSQL developer reading this - when can on-disk bitmap indexes be expected to be included in stable PostgreSQL versions? It's scheduled for inclusion in 8.3, Not any more --- we

Re: [GENERAL] Experiences of PostgreSQL on-disk bitmap index patch

2007-06-20 Thread Joshua D. Drake
Alexander Staubo wrote: On 6/20/07, Christan Josefsson [EMAIL PROTECTED] wrote: Anyone using these patchese on production systems? If these are the same patches that were made for Bizgres, then they are bound to be in use in some current production systems of that version of PostgreSQL. If

Re: [GENERAL] insane index scan times

2007-06-20 Thread Scott Marlowe
Sergei Shelukhin wrote: This is just an example isolating the problem. Actual queries contain more tables and more joins and return reasonable amount of data. Performance of big indices however is appalling, with planner always reverting to seqscan with default settings. I tried to pre-filter

Re: [GENERAL] Experiences of PostgreSQL on-disk bitmap index patch

2007-06-20 Thread Alexander Staubo
On 6/20/07, Joshua D. Drake [EMAIL PROTECTED] wrote: Just so there is no confusion. These WILL NOT be in 8.3: http://developer.postgresql.org/index.php/Todo:PatchStatus Apologies. I didn't know they had been put on hold. Alexander. ---(end of

Re: [GENERAL] Running OS-level programs from Postgres?

2007-06-20 Thread Chris Browne
[EMAIL PROTECTED] (Sean Murphy) writes: My present need is for email notification; if there's a pg function or module that would handle this (I haven't turned up anything in my searches, but maybe I'm using the wrong search terms in the wrong places) I'd be OK for now, but I'd rather have the

Re: [GENERAL] standard LOB support

2007-06-20 Thread EBIHARA, Yuichiro
Hi Scott, Thank you for your comment. Is there any plan to support BLOB and CLOB in future releases? Looking at the spec, and postgresql's implementation, I can't see much reason you couldn't just use a domain to declare that a bytea is a blob and varchar is a clob. That sounds a