[GENERAL] Multiple postgres.exe On Processes

Dear All, I have fear that sufficient increase in number of postgres.exe after one or two logins on servers may down the speed of the server or even server. Is there any solution or technique to overcome this overhead. Regards, Abdul Rehman.

Re: [GENERAL] No MD5SUM for 8.1.16 RHEL5 rpms

On Wed, Feb 11, 2009 at 3:05 PM, Devrim GÜNDÜZ wrote: > Hi, > > > I downloaded RHEL5 rpm files for the latest 8.1.16 release and could not > > find the MD5 file for them. > > > http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.1.16/linux/rpms/redhat/rhel-5-i386/ > > > > 8.1.15 RHEL5

Re: [GENERAL] encoding of PostgreSQL messages

On Wednesday 11 February 2009 18:00:31 Tom Lane wrote: > Peter Eisentraut writes: > > Tom Lane wrote: > >> Reflecting on the bigger picture ... I would imagine that the vast > >> majority of existing applications depend on client_encoding settings > >> that come from postgresql.conf, ALTER USER S

Re: [GENERAL] Making a result of transaction visible to everyone, saving the ability for a rollback

Igor Katson wrote: > I need to execute a rather complex plpgsql function on a production > server, the result of which is hard to test by myself. > > I want to make the result of a transaction, in which this function is > executed, visible to the outer clients, but to have the ability of > maki

Re: [GENERAL] Query Question

On 02/11/2009 01:10 AM, Ioana Danes wrote: > Try working with this: > > SELECT m.key AS mailings_key, >m.name AS mailings_name, >COALESCE(u.key,'') AS userdata_key, >COALESCE(u.uid,'') AS userdata_uid, >COALESCE(u.name,'') AS userdata_name > FROM (SELECT m0.key, m

Re: [GENERAL] Two-phase commmit, plpgsql and plproxy

Jeff Davis wrote: On Thu, 2009-02-12 at 00:57 +0300, Igor Katson wrote: Thanks, Jeff. That's not good news, cause I am not able to do that. There may be some creative solution, but I don't know plproxy well enough to suggest one. The postgres manual says, that " The intended usage

[GENERAL] Casting text to reg*

I needed to do $SUBJECT in 8.2, which doesn't have the built-in casts. In case anyone else needs to, the following works: deci...@platter.local=# create function regprocedure(text) returns regprocedure language sql as 'select regprocedurein(textout($1))'; CREATE FUNCTION deci...@platter.loca

Re: [GENERAL] createdb.exe and psql.exe without Promting Password

Hi Dennis, all. You are right. I know that sounds stupid, but the problem why the environment variable didn't work was due that 'P' :). I don't know why but I didn't realize that I was writing the name of the variable wrong :S Now, I've tried both options and both works correctly. I think for the

Re: [GENERAL] Two-phase commmit, plpgsql and plproxy

On Thu, 2009-02-12 at 00:57 +0300, Igor Katson wrote: > Thanks, Jeff. That's not good news, cause I am not able to do that. There may be some creative solution, but I don't know plproxy well enough to suggest one. > The postgres manual says, that > " The intended usage of the feature is that a pr

Re: [GENERAL] Saber cuando se dispara el trigger (After: insert,update,delete)

Hola, no olvides que esta es la lista de correo para usuarios de habla inglesa. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Two-phase commmit, plpgsql and plproxy

Jeff Davis wrote: On Wed, 2009-02-11 at 12:43 +0300, Igor Katson wrote: ERROR: XX000: SPI_execute_plan failed executing query "PREPARE TRANSACTION '2pctest'": SPI_ERROR_TRANSACTION It's probably treating the word PREPARE specially. You can avoid this problem by using EXECUTE and speci

Re: [GENERAL] Two-phase commmit, plpgsql and plproxy

On Wed, 2009-02-11 at 12:43 +0300, Igor Katson wrote: > ERROR: XX000: SPI_execute_plan failed executing query "PREPARE > TRANSACTION '2pctest'": SPI_ERROR_TRANSACTION It's probably treating the word PREPARE specially. You can avoid this problem by using EXECUTE and specifying the command as a st

Re: [GENERAL] Killing OIDs

On Wed, Feb 11, 2009 at 12:59 PM, Steve Crawford wrote: > Raymond O'Donnell wrote: >> >> >>> >>> Since 8.3 creates tables WITHOUT OIDS by default, I'd think just >>> dumping from 7.4 to 8.3 with 8.3's pg_dump should do the trick. Or am >>> I missing something? >>> >> >> I could be wrong, but I th

[GENERAL] PostgreSQL "freebsd ports" package description outdated

Hi, While browsing around I came upon the FreeBSD postgresql-server package description: http://www.freebsd.org/cgi/url.cgi?ports/databases/postgresql83-server/pkg-descr I find this description outdated on at least two counts: 1. Marc Fournier is no longer the "development team coordinator"; or

Re: [GENERAL] Killing OIDs

Daniel Verite wrote: I seems to me that pg_dump does it for you anyway, unless you ask otherwise. See the -o option. No, as I understand it this does not influence whether or not the table is recreated with OIDs, it determines whether the _values_ of the OIDs are included in the dumped data.

Re: [GENERAL] Killing OIDs

Steve Crawford wrote: Although I assume a cluster would reclaim space, I don't actually care if the space used by the OIDs is reclaimed in the 7.4 database as long as the OIDs are not created when the data is restored in 8.3. I seems to me that pg_dump does it for you anyway, unles

Re: [GENERAL] createdb.exe and psql.exe without Promting Password

On Tue, Feb 10, 2009 at 7:04 PM, Inigo Barandiaran wrote: > I've read that if I define an environment variable like SET > PGPPASSWORD=MyPosgresUserPassword, both calls dont ask for password. I've > tested this variable in different platforms and does not work. > Do you know how so solve it? As yo

Re: [GENERAL] Killing OIDs

Raymond O'Donnell wrote: Since 8.3 creates tables WITHOUT OIDS by default, I'd think just dumping from 7.4 to 8.3 with 8.3's pg_dump should do the trick. Or am I missing something? I could be wrong, but I think 8.3's pg_dump will append WITH (OIDS=TRUE) if it finds tables with OIDs.

Re: [GENERAL] Killing OIDs

On 11/02/2009 19:40, Scott Marlowe wrote: > Since 8.3 creates tables WITHOUT OIDS by default, I'd think just > dumping from 7.4 to 8.3 with 8.3's pg_dump should do the trick. Or am > I missing something? I could be wrong, but I think 8.3's pg_dump will append WITH (OIDS=TRUE) if it finds tables

Re: [GENERAL] Killing OIDs

On Wed, Feb 11, 2009 at 10:41 AM, Steve Crawford wrote: > I'm about to deal with an upgrade of a server running 7.4. I have checked > with the developers and they are not using OIDs so I'd like to remove them > so they aren't carried forward to 8.3. > > My plan is to do the OID removal on 7.4. I c

Re: [GENERAL] COPY command question

Yes should work perfectly as suggested by Sam, chk this for jdbc support: http://kato.iki.fi/sw/db/postgresql/jdbc/copy/ Sam Mason wrote: On Wed, Feb 11, 2009 at 10:22:23AM -0800, Sharmila Jothirajah wrote: I want to migrate my tables from Oracle to Postgres. The COPY FROM command can tak

Re: [GENERAL] Killing OIDs

I wrote: > Sure it will. I'd be a little worried about whether he shouldn't > be using quote_identifier and/or schema-qualifying the names, but > SET WITHOUT OIDS is the right command to be issuing. BTW, the lazy man's way to deal with both of those issues is to cast the OID to regclass, ie the b

Re: [GENERAL] Killing OIDs

Tom Lane wrote: ... I'd be a little worried about whether he shouldn't be using quote_identifier and/or schema-qualifying the names, but SET WITHOUT OIDS is the right command to be issuing. It may not make any difference in this case, but for completeness and correctness: SELECT 'ALTER T

Re: [GENERAL] Saber cuando se dispara el trigger (After: insert,update,delete)

Ing. Eris Gomez wrote: > Saludos a todos! > En una tabla de mi base de datos tengo un trigger que se dispara despues de > insertar, actualizar o borrar. > No obstante, necesito saber si estoy borrando para saber a cual registro > hacer referencia, si al new o el old. > Este trigger me sirve par

Re: [GENERAL] postmaster LISTENing on UDP port 32938

Richard Huxton writes: > rhubbell wrote: >> Found this on a recent install. I don't find anything documented on why >> postmaster is LISTENing on this port. What's the purpose? >> If it's not required how to disable? > It should just be localhost and I believe it's the stats collector > talking

Re: [GENERAL] COPY command question

On Wed, Feb 11, 2009 at 10:22:23AM -0800, Sharmila Jothirajah wrote: > I want to migrate my tables from Oracle to Postgres. > The COPY FROM command can take input from 'file' or 'STDIN'. > Is it possible for the COPY command to take its input from a > java program(which contains the oracle results

Re: [GENERAL] postmaster LISTENing on UDP port 32938

rhubbell wrote: > Found this on a recent install. I don't find anything documented on why > postmaster is LISTENing on this port. What's the purpose? > If it's not required how to disable? It should just be localhost and I believe it's the stats collector talking to the rest of the system. --

[GENERAL] COPy command question

Hi, A question about the Postgresql's COPY command. This is the syntax of this command from the manual COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] . I want to migrate my tables from Oracle to Postgres. The COPY FROM command can take input fro

[GENERAL] Saber cuando se dispara el trigger (After: insert,update,delete)

Saludos a todos! En una tabla de mi base de datos tengo un trigger que se dispara despues de insertar, actualizar o borrar. No obstante, necesito saber si estoy borrando para saber a cual registro hacer referencia, si al new o el old. Este trigger me sirve para tener mi existencia actualizada.

Re: [GENERAL] Killing OIDs

Joshua D. Drake wrote: On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote: I'm about to deal with an upgrade of a server running 7.4. I have checked with the developers and they are not using OIDs so I'd like to remove them so they aren't carried forward to 8.3. My plan is to do the O

Re: [GENERAL] Killing OIDs

"Joshua D. Drake" writes: > On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote: >> My plan is to do the OID removal on 7.4. I can get a script with: >> SELECT >> 'ALTER TABLE ' || relname || ' SET WITHOUT OIDS;' > That won't drop the OID columns. Sure it will. I'd be a little worried about

[GENERAL] postmaster LISTENing on UDP port 32938

Found this on a recent install. I don't find anything documented on why postmaster is LISTENing on this port. What's the purpose? If it's not required how to disable? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql

Re: [GENERAL] Killing OIDs

On Wed, 2009-02-11 at 18:01 +, Adrian Klaver wrote: > - "Joshua D. Drake" wrote: > Now I am confused. From the docs I get: > My bad. The docs are obviously correct. I think I was thinking about the postgresql.conf option. Joshua D. Drae > SET WITHOUT OIDS > > This form removes t

Re: [GENERAL] Killing OIDs

- "Joshua D. Drake" wrote: > On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote: > > I'm about to deal with an upgrade of a server running 7.4. I have > > checked with the developers and they are not using OIDs so I'd like > to > > remove them so they aren't carried forward to 8.3. > >

Re: [GENERAL] Killing OIDs

On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote: > I'm about to deal with an upgrade of a server running 7.4. I have > checked with the developers and they are not using OIDs so I'd like to > remove them so they aren't carried forward to 8.3. > > My plan is to do the OID removal on 7.4.

[GENERAL] Killing OIDs

I'm about to deal with an upgrade of a server running 7.4. I have checked with the developers and they are not using OIDs so I'd like to remove them so they aren't carried forward to 8.3. My plan is to do the OID removal on 7.4. I can get a script with: SELECT 'ALTER TABLE ' || relname || '

Re: [GENERAL] Pet Peeves?

On Wed, Feb 11, 2009 at 4:45 PM, Grzegorz Jaśkiewicz wrote: > On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera > wrote: > >> Apparently nobody saw the talk ... ?? > http://blog.hagander.net/archives/137-FOSDEM-is-done.html > > Acording to that page, one of Greg's talks didn't happen. I wasn't > th

Re: [GENERAL] Referencing Cursor/Row/Record Fields in PL/PgSQL

2009/2/11 Lee Hughes : > I got it to work in plpgsql through the use of pg_attribute and a temporary > table with known field names like "field0", "field1", etc. Works very nicely > and performance seems fine so far. > you don't need temp. table. If you know fix sets of columns. Attentions. These

Re: [GENERAL] Pet Peeves?

On Wed, Feb 11, 2009 at 4:26 PM, Alvaro Herrera wrote: > Apparently nobody saw the talk ... ?? http://blog.hagander.net/archives/137-FOSDEM-is-done.html Acording to that page, one of Greg's talks didn't happen. I wasn't there, but was it the one ? -- GJ -- Sent via pgsql-general mailing lis

Re: [GENERAL] Referencing Cursor/Row/Record Fields in PL/PgSQL

I got it to work in plpgsql through the use of pg_attribute and a temporary table with known field names like "field0", "field1", etc. Works very nicely and performance seems fine so far. Thanks for your help. Lee On Tue, Feb 10, 2009 at 8:21 PM, Pavel Stehule wrote: > Hello > > 2009/2/11 Lee H

Re: [GENERAL] Pet Peeves?

Peter Eisentraut wrote: > Gregory Stark wrote: >> I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at >> FOSDEM 2009 this year. > > Perhaps you could post a conclusion to this, with some "worst of" > statistics or something. I didn't see your talk, but I was getting a > se

Re: [GENERAL] Tsearch2 Trigger Problem: pg_catalog.simple does not exist

Hello this bug was reported two weeks ago and it is fixed in 8.3.6. regards Pavel Stehule 2009/2/11 Howard Cole : > Hi, > > I am in the process of updating a database from 8.2 to 8.3 and need a little > help with the tsearch2 update. > > Prior to restoring my 8.3 backup, I ran the tsearch2.sql o

Re: [GENERAL] encoding of PostgreSQL messages

Peter Eisentraut writes: > Tom Lane wrote: >> Reflecting on the bigger picture ... I would imagine that the vast >> majority of existing applications depend on client_encoding settings >> that come from postgresql.conf, ALTER USER SET, ALTER DATABASE SET, or >> just the default (== database encod

Re: [GENERAL] dbi_link help

Hi, Another try to get a reply for this dbi-link problem... I was trying to look into the dbi_link.sql code and try to figure out the problem This is from the dbi_link.sql code CREATE OR REPLACE FUNCTION dbi_link.add_dbi_connection_environment(     in_data_source_id BIGINT,     in_settings YAML )

Re: [GENERAL] Tsearch2 Trigger Problem: pg_catalog.simple does not exist

RTFM! Just read the part about ditching the tsearch2 function. Sincere apologies. Howard. Howard Cole wrote: execute procedure tsearch2('fts','column1','column2'); -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgre

Re: [GENERAL] Pl/PgSQL String formatting like raise?

Pavel, thank you very much for your answer. -- Best Regards, Csaba Együd there is nothing similar. You should to use module plvsubst from Orafce http://www.postgres.cz/index.php/Oracle_functionality_(en)#PLVsubst regards Pavel Stehule 2009/2/11 Csaba Együd : Hi, is there a way in a Pl/PgSQL f

[GENERAL] Tsearch2 Trigger Problem: pg_catalog.simple does not exist

Hi, I am in the process of updating a database from 8.2 to 8.3 and need a little help with the tsearch2 update. Prior to restoring my 8.3 backup, I ran the tsearch2.sql on the new database, however I am having a little problem with triggers - when I run an update on a table I get the followi

Re: [GENERAL] PostgreSQL clustering with DRBD

> > Serge Fonville wrote: > >> So, DRBD dual primary and Cybercluster multimaster, cannot be combined? >> > > If you have questions on Cybercluster, you should perhaps ask there for > details. For normal PostgreSQL, using DRBD dual primary is not possible. > You are right, thanks > My e

Re: [GENERAL] Pet Peeves?

Gregory Stark wrote: I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at FOSDEM 2009 this year. Perhaps you could post a conclusion to this, with some "worst of" statistics or something. I didn't see your talk, but I was getting a sense that the feedback seen on this lis

Re: [GENERAL] PostgreSQL clustering with DRBD

Serge Fonville wrote: So, DRBD dual primary and Cybercluster multimaster, cannot be combined? If you have questions on Cybercluster, you should perhaps ask there for details. For normal PostgreSQL, using DRBD dual primary is not possible. My endgoal is a two node cluster with load

Re: [GENERAL] Intel SSD

On Tue, Feb 10, 2009 at 9:24 PM, Sebastian Böhm wrote: > I consider buying one of these Intel SSDs for my Database (MLC). > > I have a few questions regarding this, maybe some of you already have some > experience with that. > > - which files should not be put onto the SSD (pg_clog ? pg_xlog ? ) t

Re: [GENERAL] small doubt

Kusuma, On Wed, Feb 11, 2009 at 13:43, Kusuma Pabba wrote: > what does postgres management gui mean ? > How is it useful ? > can i have it on ubuntu? propably pgAdmin III, www.pgadmin.org on ubuntu you can have pgadmin via apt-get install pgadmin best wishes, Harald -- GHUM Harald Massa

Re: [GENERAL] Difference between Windows pgsql and Linux pgsql?

On Wednesday 11. February 2009, Leif B. Kristensen wrote: FYI, I have included the following paragraph in the README.txt: You're welcome to ask questions about the project provided that they follow the general guidelines in the article "How To Ask Questions The Smart Way"

[GENERAL] small doubt

what does postgres management gui mean ? How is it useful ? can i have it on ubuntu? Thanks & Regards kusuma.p -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] PostgreSQL clustering with DRBD

Hi, Thanks a lot for the reply > Since the cluster will be multi-master/dual-primary, do I need to have a >> separate block device for each PostgreSQL instance or can it use the DRBD >> device? >> > > I don't understand how you want to have a multimaster PostgreSQL setup with > DRBD. DRBD can

Re: [GENERAL] PostgreSQL clustering with DRBD

Serge Fonville wrote: Can PostgreSQL use DRBD as its storage? Yes, many deployments use this. Since the in-memory database would be synchronized with the on-disk database. If this would be done with every query, this would greatly impact performance. Performance with DRBD is usually accept

Re: [GENERAL] Difference between Windows pgsql and Linux pgsql?

On Wednesday 11. February 2009, Russell Hltn wrote: >Unfortunately the author doesn't work with Windows and can't help me. As I have stated in the README.txt, the software is at a very early stage, and should be considered "proof of concept" or "prototype" stuff. I have published it "as is" for

[GENERAL] PostgreSQL clustering with DRBD

Hi, I am in the process of setting up a two node cluster. Can PostgreSQL use DRBD as its storage? Since the in-memory database would be synchronized with the on-disk database. If this would be done with every query, this would greatly impact performance. Since the cluster will be multi-master/dual-

Re: [GENERAL] tablelog

Hi, On Tuesday 10 February 2009 17:36:38 Bruno Lavoie wrote: > Oracle Flashback Query seems to use undo logs to return in a point in > time if the undo retention param permit it. You can do it with a date, > or with the SCN (system change number), a kind of transaction number. It > saves you admin

[GENERAL] Making a result of transaction visible to everyone, saving the ability for a rollback

I need to execute a rather complex plpgsql function on a production server, the result of which is hard to test by myself. I want to make the result of a transaction, in which this function is executed, visible to the outer clients, but to have the ability of making a rollback in case one of t

[GENERAL] Two-phase commmit, plpgsql and plproxy

I would like to call several plproxy functions one after another (which will call plpgsql functions in different target partitions), and in case one of them fails, i want to roll back changes in every one. That is exactly how two-phase-commit (2PC) should work. As far as I understand, the post

Re: [GENERAL] In need of help with message fetching query

Hey, thanks for your quick response On Wed, Feb 11, 2009 at 12:00 AM, Sam Mason wrote: > On Tue, Feb 10, 2009 at 11:19:13PM +0100, Max Strrrmberg wrote: >> [...] messages are to be ordered in a very standard fashion of single-depth >> threads. That is, a message can be a reply, and a so-called "h

Re: [GENERAL] Key Vs Index

On 11/02/2009 07:37, Abdul Rahman wrote: > But PG_Admin-III does not show this index. Sorry to say that I faced > several problems because of PG_Admin-III. And I advise you to use > psql prompt instead of GUI. I think that's a little unfair. PgAdmin is IMHO a great tool, and I've found it invaluab

Re: [GENERAL] No MD5SUM for 8.1.16 RHEL5 rpms

Hi, > I downloaded RHEL5 rpm files for the latest 8.1.16 release and could not > find the MD5 file for them. > http://ftp9.us.postgresql.org/pub/mirrors/postgresql/binary/v8.1.16/linux/rpms/redhat/rhel-5-i386/ > > 8.1.15 RHEL5 roms had them, > http://ftp9.us.postgresql.org/pub/mirrors/postgresql/b

Re: [GENERAL] Key Vs Index

On Wed, Feb 11, 2009 at 7:37 AM, Abdul Rahman wrote: > I have found the answer. PostgreSQL creates index on primary key implicitly > and can be seen via \d tablename; command on psql prompt. But PG_Admin-III > does not show this index. pgAdmin shows the primary key which is the index. -- Dave P

Re: [GENERAL] Deleting conflicting rows when creating a foreign key

Craig Ringer wrote: Richard Huxton wrote: DELETE FROM table1 WHERE col1 NOT IN (SELECT id from table2); Just as a side note: If you have a large number of missing IDs and don't want to wait a long time, you may be better off with something like (untested, but I think it's right - TEST

Re: [GENERAL] Difference between Windows pgsql and Linux pgsql?

Russell Hltn wrote: I'm trying to set up a program called Exodus (http://solumslekt.org/forays/exodus.tgz). It was built on a PHP, Apache, and Postgres running on Linux. Since all these programs also have Windows versions, I decided to try and install it on Windows. (Go with what I know.) Howe

Re: [GENERAL] Difference between Windows pgsql and Linux pgsql?

Russell Hltn wrote: > I'm trying to set up a program called Exodus > (http://solumslekt.org/forays/exodus.tgz). It was built on a PHP, > Apache, and Postgres running on Linux. Since all these programs also > have Windows versions, I decided to try and install it on Windows. > (Go with what I know

Re: [GENERAL] createdb.exe and psql.exe without Promting Password

Iñigo Barandiaran wrote: > Thank you all! > > and Thanks Sam for the explanation about environment variable. I didn't > know about that :) > > I've also tried the option of .pgpass file, that as far as I understand > in Windows is pgpass.conf. I did a file containing only this line > > /*/:/*//

[GENERAL] Difference between Windows pgsql and Linux pgsql?

I'm trying to set up a program called Exodus (http://solumslekt.org/forays/exodus.tgz). It was built on a PHP, Apache, and Postgres running on Linux. Since all these programs also have Windows versions, I decided to try and install it on Windows. (Go with what I know.) However, I ran into proble