Re: [SQL] prepared query plan did not update

-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Emi Lu
Sent: Wednesday, September 17, 2008 2:55 PM
To: pgsql-sql@postgresql.org
Subject: [SQL] prepared query plan did not update

Good morning,

I tried to use prepared query plan to update columns, but it did not
update at all.

PREPARE pname(varchar) AS
UPDATE t1
SET col1 = false
WHERE col1 AND
col2 = '$1' ;
EXECUTE pname( 'value' )

Could someone tell me where I did wrong please?

Thanks alot!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make
changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

WHERE col1 AND
col2 = '$1' ;
Are you looking for both: col1 and col2 - to be equal to '$1'?
Then it should be:
WHERE col1 = '$1' AND
col2 = '$1';

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [GENERAL] Design question

On Wed, Sep 17, 2008 at 11:29 AM, Mike Diehl <mdiehl@diehlnet.com> wrote:
> Hi all,
>
> I've got a design question that I need to ask before I go too far down what
> might be the wrong road.
>
> I've got a customer, who has multiple customers, who need to be able to upload
> an excel spreadsheet into Postgres. Then they want to be able to slice and
> dice that data.
>
> The problem is that probably none of these spreadsheets will have the same
> fields in them.
>
> There are two ways to do this, that I can think of...
>
> 1. Create a table for each spreadsheet, using column headings as field names.
> Every field would be a char/varchar. We might have a table to track which
> client owns which table. This could amount to 10's of tables being added to
> the db.

If you choose this method, you might want to split out customers by
schema, to make it easier to manage their tables. Then, all you have
to do is either prefix the customer name in front of the table
reference or set it in your search_path after connecting.

> 2. Create a table in which we store individual cells and associate them with
> an owner. Then each client would essentially have one (huge?) table that
> they can work with.

This is basically going to be an EAV (entity, attribute, value) type
setup. They are notoriously hard to write useful queries against, and
generally a poor performer.

I'd go with option 1 myself.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[pgsql-www] What happened to pgfoundry?

I am getting 404 errors for each project? Does anyone know what
happened?

--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
http://www.gunduz.org

Re: [GENERAL] Design question

Have you considered one large table with all of the columns from the various spreadsheets, then a separate view  for each customer?

----- Original Message ----
From: Mike Diehl <mdiehl@diehlnet.com>
To: pgsql-general@postgresql.org
Sent: Wednesday, September 17, 2008 12:29:15 PM
Subject: [GENERAL] Design question

Hi all,

I've got a design question that I need to ask before I go too far down what
might be the wrong road.

I've got a customer, who has multiple customers, who need to be able to upload
an excel spreadsheet into Postgres.  Then they want to be able to slice and
dice that data.

The problem is that probably none of these spreadsheets will have the same
fields in them.

There are two ways to do this, that I can think of...

1.  Create a table for each spreadsheet, using column headings as field names. 
Every field would be a char/varchar.  We might have a table to track which
client owns which table.  This could amount to 10's of tables being added to
the db.

2.  Create a table in which we store individual cells and associate them with
an owner.  Then each client would essentially have one (huge?) table that
they can work with.

Design #1 is easy to implement, but might make management more difficult. 
Design #2 is easy to manage, but the SQL needed to generate reports would
be "tricky."  I'm intending to provide a report generator, so the complexity
of the reporting SQL can be mitigated.

So, which road should I travel down?

TIA,

--
Mike Diehl

--
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] Index order

> Hmm, I understand what you're saying, but how ion earth do I create
> a function that reorders the result based on all the different
> characters ^ . * etc that could cause this?

Write a function that strips out the characters you want to ignore
(returning the rest of the given string) and then create an index on
that function.

(Please note: that's an answer to the question that you asked above,
not necessarily a recommendation that you choose this particular
collation strategy).

-- Korry

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [ADMIN] Heavy postgres process

On Wed, Sep 17, 2008 at 12:31 PM, Kenneth Marshall <ktm@rice.edu> wrote:
> Gee,
>
> Going to Oracle does seem a bit like throwing the baby out with
> the bath water.

Especially considering the performance increase from pgsql 7.4 to 8.3
is humongous. I'd say most operations are 2 to 4 times as fast and
some operations are many 10 to 100 times faster.

But for Vivek the real, first priority is to get his 7.4.5 server
updated to the latest 7.4 version to make sure his data stays safe.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

[SQL] prepared query plan did not update

Good morning,

I tried to use prepared query plan to update columns, but it did not
update at all.

PREPARE pname(varchar) AS
UPDATE t1
SET col1 = false
WHERE col1 AND
col2 = '$1' ;
EXECUTE pname( 'value' )

Could someone tell me where I did wrong please?

Thanks alot!

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [PATCHES] still alive?

--On Donnerstag, September 11, 2008 15:39:01 +0300 Peter Eisentraut
<peter_e@gmx.net> wrote:

>
> Hmm, let's try this:
>
> Anyone who thinks the patches list should remain as separate from
> hackers, shout now (with rationale)!

Seems i've missed something, what's then supposed to hold patches now?

--
Thanks

Bernd

--
Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-patches

RE: [pgsql-es-ayuda] Hola

Hola

Para mi el tema de las bases de datos de objetos y los otros metodos de mapear objetos tiene un eje de diferencia muy grande y conceptual
que termina siendo por donde pasa todo. la identidad.
En el paradigmade objetos esta asegurada la identidad todo objeto es identico a si mismo y esa identidad es unica,( perdon por la redundancia).
Ahi radica toda la diferencia y no es poca , es mas es la que crea problemas irresolubles o de muy costosa resolucion.
Las bases relacionales utilizan para esto la pk . es la forma de asegurar la identidad por especie (clase / tabla).
Las bases de objetos no tienen esto porque el objeto no necesita identidad. por lo tanto mucha simplicidad ocurre en el codigo o en el uso.
Por ejemplo supongamos que tenemos un objeto perro como objetos.
cada vez que reconstruyo un "perro" desde la base de objetos el no tiene que preguntarse por el id de su cola a ver si coincide con el.
Por lo tanto muchas de las llamadas validaciones no existe,
REcuedro haber leido en un libro de bases de objetos- lo siguiente:
la diferencia entre mapear en una base de objetos y una base relacional es como si por ejemplo ,
todas las mañanas tomara mi auto para ir al trabajo y luego al llegar a casa lo desarmo todo en cajitas numeradas y a la mañana siguiente lo volviera a ensamblar y asi ... siempre ...
Volviendo al tema , creo que el pivot esta en la identidad.
saludos
MDC

--- El mié 17-sep-08, Danier Marante Jacas <djacas@estudiantes.uci.cu> escribió:

> De: Danier Marante Jacas <djacas@estudiantes.uci.cu>
> Asunto: RE: [pgsql-es-ayuda] Hola
> Para: pgsql-es-ayuda@postgresql.org
> Fecha: miércoles, 17 de septiembre de 2008, 1:26 am
> Según mi conocimiento las bases en la historia han
> evolucionado, inicialmente eran jerárquicas, luego fueron
> relacionales, después objeto relacionales (postgre) pero se
> quiere hacer bases de datos objétales, es decir que tengan
> la misma filosofía de diseño que los lenguajes de
> programación orientada a objeto (java). Eso es un sueño
> iniciado por IBM según tengo entendido. No creo que eso
> funcione ya que atentaría contra la velocidad.
>
> Lo que quiero es que si alguien tiñe algo acerca de eso
> y desea compartirlo conmigo o con la comunidad.
> Se que el tema no es especifico de postgre pero eso es
> parte la filosofía de las DB.
>
> El idioma en que este la documentación no es problema(
> siempre que sea derivado del latín).
>
> Y gracias por la ayuda que me puedan dar.
> ________________________________
>
> From: Guido Barosio [mailto:gbarosio@gmail.com]
> Sent: Tue 16.09.2008 11:25 pm
> To: Danier Marante Jacas
> Cc: pgsql-es-ayuda@postgresql.org
> Subject: Re: [pgsql-es-ayuda] Hola
>
>
>
> Que son las bases de datos objetuales?
>
> Documentacion en ingles sobre PostgreSQL:
> Hola Danier, a ver si te podemos ayudar un poco. Buscas
> documentacion
> en ingles o en otro idioma?
>
> http://www.postgresql.org/docs/ -> documentacion en
> ingles
>
> Algunos documentos y notas relacionadas en espanhol:
>
> http://www.postgresql.cl <http://www.postgresql.cl/>
>
> Saludos cordiales,
>
> gb.-
>
> On Tue, Sep 16, 2008 at 10:17 PM, Danier Marante Jacas
> <djacas@estudiantes.uci.cu> wrote:
> > Amigo estoy haciendo una investigación sobre las
> bases de datos objetuales, he buscado en google pero no he
> encontrado nada interesante sobre el tema. Soy partidario y
> defensor de las bases de datos objeto-relacionales pero es
> algo que me mandaron hacer. Me gustaría que me dieran algo
> de documentación sobre el tema.
> > Gracias de antemano.
> > Saludos Danier.
> > --
> > TIP 8: explain analyze es tu amigo
> >
>
>
>
> --
> http://www.linkedin.com/in/gbarosio
>
>
> --
> TIP 1: para suscribirte y desuscribirte, visita
> http://archives.postgresql.org/pgsql-es-ayuda

Yahoo! Cocina
Recetas prácticas y comida saludable
http://ar.mujer.yahoo.com/cocina/
--
TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán

Re: [ADMIN] Recommended number of connections

The maximum number of Postgres connections you can have may be controlled by adjusting the max_connections PG parameter based on your anticipated connections needs and requirements.  

 

Make sure that your Kernel’s related parameters are also configured properly to accommodate your max_connections settings (e.g, in Linux, SEM* kernel’s parameters) when increasing the max_connections parameter.

 

http://www.postgresql.org/docs/8.2/static/runtime-config-connection.html

http://www.postgresql.org/docs/8.2/static/kernel-resources.html#SYSVIPC

 

Regards,

      Husam

---

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of M C
Sent: Wednesday, September 17, 2008 10:01 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Recommended number of connections

 

What are the "maximum" amount of "connections" allowed to "postgres"? and what would be a resonable amount of connection/processes to see based on user connections?

 

**********************************************************************
This message contains confidential information intended only for the use of the addressee(s) named above and may contain information that is legally privileged.  If you are not the addressee, or the person responsible for delivering it to the addressee, you are hereby notified that reading, disseminating, distributing or copying this message is strictly prohibited.  If you have received this message by mistake, please immediately notify us by replying to the message and delete the original message immediately thereafter.

Thank you.

                                                                                                                         FADLD Tag
**********************************************************************

[GENERAL] Design question

Hi all,

I've got a design question that I need to ask before I go too far down what
might be the wrong road.

I've got a customer, who has multiple customers, who need to be able to upload
an excel spreadsheet into Postgres. Then they want to be able to slice and
dice that data.

The problem is that probably none of these spreadsheets will have the same
fields in them.

There are two ways to do this, that I can think of...

1. Create a table for each spreadsheet, using column headings as field names.
Every field would be a char/varchar. We might have a table to track which
client owns which table. This could amount to 10's of tables being added to
the db.

2. Create a table in which we store individual cells and associate them with
an owner. Then each client would essentially have one (huge?) table that
they can work with.

Design #1 is easy to implement, but might make management more difficult.
Design #2 is easy to manage, but the SQL needed to generate reports would
be "tricky." I'm intending to provide a report generator, so the complexity
of the reporting SQL can be mitigated.

So, which road should I travel down?

TIA,

--
Mike Diehl

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [SQL] Trigger Procedure Error: NEW used in query that is not in a rule

On Wed, Sep 17, 2008 at 5:45 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Wed, Sep 17, 2008 at 05:08:39PM +0200, Raphael Bauduin wrote:
>> Would you have a little example on how you would do it?
>
> show us what you have done - it will be easier to find/fix/explain than
> to write code for you.

Well, I experimented a lot but didn't come to any useful result.
Actually I'm working on table partitioning as described at
http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html , and
I wanted to write a trigger that would insert the data in the correct
table, and so I got the same problem with plpsql's NEW.* not usable in
a dynamically created query to be run by EXECUTE:

CREATE OR REPLACE FUNCTION part_test() RETURNS TRIGGER AS $$
DECLARE
current_time timestamp := now();
suffix text := date_part('month', now())||'_'||date_part('day', now()) ;
BEGIN
RAISE NOTICE '%', suffix;
execute 'insert into t1_'||suffix||' values( NEW.* )';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

I searched the archives here and after reading your previous mail in
this thread, I started to look at plperl, with which I have no
experience at all.
As $_TD{new}{column} gives the value of field column, I thought to
extract all columns from keys($_TD{new}), but it doesn't seem to see
$_TD{new} as a hash:

Type of arg 1 to keys must be hash (not hash element)

And that's where I'm at now.

Raph

>
> Best regards,
>
> depesz
>
> --
> Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
> jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>

--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[ADMIN] Recommended number of connections

What are the "maximum" amount of "connections" allowed to "postgres"? and what would be a resonable amount of connection/processes to see based on user connections?

[COMMITTERS] pgloader - pgloader: FIX RRR bug wrt offsets, allow python 2.3 to run

Log Message:
-----------
FIX RRR bug wrt offsets, allow python 2.3 to run pgloader when it does not need collections.deque (no Round Robin Reader), and prepare packaging for 2.3.2

Modified Files:
--------------
pgloader/debian:
changelog (r1.28 -> r1.29)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgloader/pgloader/debian/changelog.diff?r1=1.28&r2=1.29)
pgloader/pgloader:
options.py (r1.23 -> r1.24)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgloader/pgloader/pgloader/options.py.diff?r1=1.23&r2=1.24)
pgloader.py (r1.45 -> r1.46)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgloader/pgloader/pgloader/pgloader.py.diff?r1=1.45&r2=1.46)
tools.py (r1.13 -> r1.14)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgloader/pgloader/pgloader/tools.py.diff?r1=1.13&r2=1.14)

Added Files:
-----------
pgloader/pgloader:
RRRtools.py (r1.1)
(http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/pgloader/pgloader/pgloader/RRRtools.py?rev=1.1&content-type=text/x-cvsweb-markup)

--
Sent via pgsql-committers mailing list (pgsql-committers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-committers

Re: [GENERAL] FAQ: how to read EXPLAYN ANALYZE and act upon

On Wed, 17 Sep 2008, Ivan Sergio Borgonovo wrote:

> I'm looking for a tutorial that will teach me how to read explain
> analyze and maybe point me to some solutions (rewriting the query,
> adding some indexes, tune postgres.conf...).

All of the EXPLAIN tutorials and tools I'm aware of are listed at
http://wiki.postgresql.org/wiki/Using_EXPLAIN and the primary things to
tune in the postgresql.conf are described at
http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [JDBC] Best Practice to Create a Connection Pool in PostgreSQL

On Tue, Sep 16, 2008 at 7:47 PM, Greg James <gregcjames@comcast.net> wrote:

I would like to ask question on this subject as well.

I've read a OOP design patterns book that features Java when
illustrating various patterns. The book shows show to create various
mechanisms for pooling and releasing various resources (including
connections). The book demonstrates this by using a hash map that
reference many linked lists for a particular kind of connection and
the link lists can reference many connection objects for one kind of
connection.

Would a hand crafted pooling mechanism not compete well with some of
the existing solutions mentioned?

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

Re: [SQL] surrogate vs natural primary keys

On Wed, Sep 17, 2008 at 7:45 AM, Scott Marlowe <scott.marlowe@gmail.com> wrote:

>> CREATE TABLE t2 (
>> d1 varchar(200),
>> d2 int8,
>> d3 varchar(1000),
>> PRIMARY KEY (d1, d2)
>> FOREIGN KEY (d1, d2) REFERENCES t1(c1, c2) );
>>
>> thereby avoiding repeating multiple pieces of
>> information?
>
> Yes and no. If you're gonna hit table t2 a lot by itself, then it's
> more efficient to have the data there in t2 and not have to join to t1
> to get it. There are always use cases that go either way in this kind
> of situation.

Also, doing this allows you to apply more constraints to T2 (if you
ever wanted to add them). For example, what if you only wanted to
allow a sub-set of T1(c1,c2) in T2(d1,d2), you could use a check
constraint to enforce this more restrictive relationship. This
wouldn't be possible (without adding custom triggers) if you only used
a surrogate key. My experience is that many more validation
constraints are possible if use natural keys are used. Whether this
feature is a good thing or not is up to you.

My opinion is that the database constraints are the last line of
defense to ensure business rules and data integrity are not violated.
Since I highly value the ability to enforce business rules using
ordinary table DDL, I try to use natural keys as often as I can.

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [HACKERS] text search patch status update?

I remember about that but right now I havn't time to make final review. Sorry.
Will return soon.

Sushant Sinha wrote:
> Any status updates on the following patches?
>
> 1. Fragments in tsearch2 headlines:
> http://archives.postgresql.org/pgsql-hackers/2008-08/msg00043.php
>
> 2. Bug in hlCover:
> http://archives.postgresql.org/pgsql-hackers/2008-08/msg00089.php
>
> -Sushant.
>
>

--
Teodor Sigaev E-mail: teodor@sigaev.ru
WWW: http://www.sigaev.ru/

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-es-ayuda] Compatibilidad Visual Basic 6.0

Hola.

 

 Si utilizaron sentencias SQL standard no hay ningun problema, personalmente tengo varios años programando en Visual y PostgreSQL y te digo es muy bueno el desempeño y el resultado final. En la web hay algunos howto, para migrar access a postgresql, personalmente te recomiendo exportar los datos de acces como insert, crear las tablas en postgreSQL e importar los datos.

Debes considerar que PostgreSQL, es un RDBMS muy poderoso con una amplia gama de herramientas(funciones, tipos de datos, esquemes,etc), que acces no posee, por lo que debes considerar todo esto al momento de migrar para aprovechar postgreSQL al máximo

 

Suerte

 

El 17 de septiembre de 2008 11:25, GRUPO SIC, S.A. DE C.V. <gsic@prodigy.net.mx> escribió:

Saludos cordiales, somos desarrolladores de sistemas en la CD de México.

Queremos saber la compatibilidad para emigrar un sistema desarrollado en visual Basic 6.0 (utilizamos base datos ACCESS)  y utilizar su Postgre SQL como servidor de base de datos.

 Atentamente

Ing Efraín Caporali C.

TEL (0155) 5970-4698

Mail: Efraín.caporali@prodigy.net.mx

 

 

Re: [pgsql-es-ayuda] Compatibilidad Visual Basic 6.0

El día 17 de septiembre de 2008 12:54, Jose Luis Balle
<joseluisballe@gmail.com> escribió:
> El único inconveniente que veo es si utilizas procedimientos
> almacenados (es decir Funciones del Usuario) o funciones propias de MS
> Access, en cuyo caso vas a tener que desarrollarlas en pl/pgsql o
> alguno de los lenguajes que soporta postgresql.
> Por lo demás yo he migrado varios sistemas de Access a Postgres sin
> inconveniente.
> Saludos
>
> El día 17 de septiembre de 2008 12:25, GRUPO SIC, S.A. DE C.V.
> <gsic@prodigy.net.mx> escribió:
>> Saludos cordiales, somos desarrolladores de sistemas en la CD de México.
>>
>> Queremos saber la compatibilidad para emigrar un sistema desarrollado en
>> visual Basic 6.0 (utilizamos base datos ACCESS) y utilizar su Postgre SQL
>> como servidor de base de datos.
>>
>> Atentamente
>>
>> Ing Efraín Caporali C.
>>
>> TEL (0155) 5970-4698
>>
>> Mail: Efraín.caporali@prodigy.net.mx
>>
>>
>>
>>
>
--
TIP 9: visita nuestro canal de IRC #postgresql-es en irc.freenode.net

Re: [pgsql-es-ayuda] Compatibilidad Visual Basic 6.0

alguna vez estuve en un proyecto similar,
primero que nada, debes revisar los objetos de conectividad que usaron
en el aplicativo VB , si usaron ADO el proceso se simplifica pero si usaron DAO
van a tener muchos dolores de cabeza.

De todas maneras, es totalmente factible una migración de estas e indudablemente
es lo recomendable si los accesos se van incrementando asi como el
tamaño de la BD.

El día 17 de septiembre de 2008 10:25, GRUPO SIC, S.A. DE C.V.
<gsic@prodigy.net.mx> escribió:
> Saludos cordiales, somos desarrolladores de sistemas en la CD de México.
>
> Queremos saber la compatibilidad para emigrar un sistema desarrollado en
> visual Basic 6.0 (utilizamos base datos ACCESS) y utilizar su Postgre SQL
> como servidor de base de datos.
>
> Atentamente
>
> Ing Efraín Caporali C.
>
> TEL (0155) 5970-4698
>
> Mail: Efraín.caporali@prodigy.net.mx
>
>
>
>

--
Atentamente,
Moisés Alberto Lindo Gutarra
Asesor - Desarrollador Java / Open Source
Linux Registered User #431131 - http://counter.li.org/
Cel: (511) 995081720
MSN: mlindo@tumisolutions.com
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [pgsql-es-ayuda] framework webtranslator

Colegas:

En correos anteriores le comenté que si me dan el usuario y clave ....
más los fuentes para continuar desarrollando el sitio ... nosotros desde
Cuba ... lo hacemos.

Estamos a la orden .... fuerza de trabajo y deseos sobran.

Saludos,
Gilberto.
El mié, 17-09-2008 a las 16:33 +0100, Javier Chávez B. escribió:
> 2008/9/17 Teófilo Oviedo <teus79@active.com.py>:
> > Alvaro,
> >
> > Es a modo de poder darle ya un inicio a todo esto.
> > Por lo que he visto y seguido la seguidilla de mensajes, no hay gente
> > que quiera dar continuidad al webtraslator, que a mi parecer era una
> > herramienta excelente. Pero que lastimosamente no conozco ni Python ni
> > Jango para poder dar una manito.
> >
> > Por de pronto, mi ofrecimiento es:
> > - Colaborar con la traducción de los textos del manual y;
> > - Colaborar con un espacio de hosting con o sin dominio, cualquiera sea.
> > Si quieren puede ser de puchero incluído.
> >
> > Pero esto ya está lléndose a la deriva y nadie toma de la piola que se
> > nos escapa de vuelta la oportunidad de hacer la traducción.
> >
> > Está mi intención de colaboración, basta que los demás dejen de discutir
> > por sensibilidades emocionales y dar definiciones más profundas que el
> > mar como el de cómo va a ser el punto a parte, si en negrita o tipo
> > cursiva.
> >
> > Mis gratos saludos a la lista,
> >
> >
> >
> > El mié, 17-09-2008 a las 10:30 -0400, Alvaro Herrera escribió:
> >> Teófilo Oviedo escribió:
> >> > Alvaro,
> >> >
> >> > Si hace falta un lugar donde hostear el sitio para la traducción tengo
> >> > máquinas a disposición.
> >> > Si mantienes el dominio postgresql.cl avisame para iniciar ya con esto
> >> > de la traducción, que ya está llevando su tiempo.
> >>
> >> Hola,
> >>
> >> No mantengo ni postgresql.cl ni el sitio de webtranslator. Con respecto
> >> a hostear l10n.postgresql.cl no creo que sea necesario, ya que es un
> >> proyecto inconcluso (digamos, concluido hasta un 90%) y sin mantenedor,
> >> pero dado que las personas que se quieren hacer cargo de la traducción
> >> no están interesadas en mantenerlo, creo que no vale la pena gastar
> >> espacio y tiempo en ese sitio.
> >>
> > --
> > Teófilo Oviedo
> > ACTIVE S.R.L.
> > http://www.active.com.py
> >
> > --
> > TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán
> >
>
> Me sumo !
>
> Slds
>
> J.
>

--
TIP 2: puedes desuscribirte de todas las listas simult�neamente
(env�a "unregister TuDirecci�nDeCorreo" a majordomo@postgresql.org)

Re: [pgsql-es-ayuda] Compatibilidad Visual Basic 6.0

On 9/17/08, GRUPO SIC, S.A. DE C.V. <gsic@prodigy.net.mx> wrote:
>
> Queremos saber la compatibilidad para emigrar un sistema desarrollado en
> visual Basic 6.0 (utilizamos base datos ACCESS) y utilizar su Postgre SQL
> como servidor de base de datos.
>

ACCESS tiene su propio operador de concatenacion en lugar de usar '||'
como dice el estandar sql... no se que otras cosas mas alla del
estandar tenga access

por el resto, como access no tiene triggers, funciones almacenadas ni
nada de eso no creo que exista mucho problema...

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [pgsql-fr-generale] fusionner des bases

SOULABAILLE Samantha a écrit :
> Bonjour,

Bonjour,

> J'ai un (énorme) souci avec deux bases hébergées sur deux serveurs pour
> deux sites web.
>
> Elles ont exactement la même structure (schéma, tables), seules les
> données sont différentes, même version de postgresql pour le serveur.
>
> Cela fait quelques années qu'elles sont en prod et qu'elle se sont
> remplies allègrement.
>
> On me demande maintenant si on peut les fusionner pour ne garder qu'un site.
>
> Les bases sont énormes, une bonne soixantaine de tables, des
> serials/foreign key/blob/oids un peu partout, bref un joyeux petit monde.
>
>
> Tout d'abord une telle opération est-elle possible ? Sachant qu'on a
> aussi certaines obligations par rapport à l'indisponibilité de ces sites
> web (je ne suis pas sure qu'on puisse se permettre plus d'une journée
> d'arrêt ou ½ journée).

Rien d'évident, qui pourrait se faire via quelques ordres SQL, ne me
vient à l'esprit pour fusionner vos bases.

Le principale problème que je vois est celui des clés automatiques.
Chacune de vos bases ayant son propre vécue, inévitablement les clés de
l'une vont déjà exister dans l'autre (référencent des tuples différents).

Peut-être pourriez-vous exporter les données de chaque base (pgdump).
Puis expérimenter une fusion sur ces données via un script intégrant
votre logique métier pour gérer la fusion. Les données ainsi traitées
seront ensuite remonter dans une base. Oui, c'est facile à dire ;)

> Est-ce qu'il existe des outils, ou des procédures, au moins quelque
> chose qui permette de faire cela ?

Pyton, PHP, Perl^W, ...

Stéphane.
(...)

--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale

Re: [pgsql-es-ayuda] framework webtranslator

Teófilo Oviedo escribió:
> Pero que lastimosamente no conozco ni Python ni
> Jango para poder dar una manito.
>

Hace mas de una semana me ofreci para el tema de python ya que yo si trabajo con el, pero no se que
pasa con mis mensajes que ultimamente no se si llegan a la lista, asi que no se si el mensaje llegó
o no.

Pue eso, si seguis con el tema de webtranslator, aunque no dispongo de mucho tiempo quizá pueda
echar una manita.

Saludos.

--
*****************************************
Oswaldo Hernández
oswaldo (@) soft-com (.) es
*****************************************
PD:
Antes de imprimir este mensaje, asegúrese de que es necesario.
El medio ambiente está en nuestra mano.
--
TIP 10: no uses HTML en tu pregunta, seguro que quien responda no podr� leerlo

Re: [pgsql-es-ayuda] Compatibilidad Visual Basic 6.0

Hola No deberian tener problemas. Pero si quieren aprovechar las bondades del postgres deberian modificar su sistema para que los procesos se hagan dentro de funciones de la base de datos y no en el cliente, como supongo estan haciendo ahora al usar Access *********************************************************** Carlos Márquez *********************************************************** --- El mié 17-sep-08, GRUPO SIC, S.A. DE C.V. <gsic@prodigy.net.mx> escribió: De: GRUPO SIC, S.A. DE C.V. <gsic@prodigy.net.mx> Asunto: [pgsql-es-ayuda] Compatibilidad Visual Basic 6.0 A: pgsql-es-ayuda@postgresql.org Cc: pgsql-es-ayuda-owner@postgresql.org Fecha: miércoles, 17 septiembre, 2008, 10:25 am Saludos cordiales, somos desarrolladores de sistemas en la CD de México. Queremos saber la compatibilidad para emigrar un sistema desarrollado en visual Basic 6.0 (utilizamos base datos ACCESS)  y utilizar su Postgre SQL como servidor de base de datos.  Atentamente Ing Efraín Caporali C. TEL (0155) 5970-4698 Mail: Efraín.caporali@prodigy.net.mx

---

Yahoo! MTV Blog & Rock
¡Cuéntanos tu historia, inspira una canción y gánate un viaje a los Premios MTV!
Participa aquí http://mtvla.yahoo.com/

Re: [BUGS] BUG #4422: select ... where ... NOT EXISTS / NOT IN

On Wed, Sep 17, 2008 at 02:53:51PM +0000, vasile wrote:
> I have this 2 similar queries.
>
> Why the 1st query is returning 1000+ rows and the 2nd one no rows ?
> The col1 is not empty in both tables.

there is no bug.
check this:

http://www.depesz.com/index.php/2008/08/13/nulls-vs-not-in/

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [ADMIN] unable to backup database -- psql not up to date

--- On Wed, 17/9/08, Jonathan Nalley <jnalley@jnalley.com> wrote:

> From: Jonathan Nalley <jnalley@jnalley.com>
> Subject: Re: [ADMIN] unable to backup database -- psql not up to date
> To: "dbchristopher" <dbchristopher@gmail.com>, pgsql-admin@postgresql.org
> Date: Wednesday, 17 September, 2008, 3:23 PM
> On Tue, Sep 16, 2008 at 5:53 PM, dbchristopher
> <dbchristopher@gmail.com> wrote:
> >
> > I upgraded to postgresql 8.3.3 a few weeks ago, and it
> seems that while the
> > server was upgraded, but for some reason none of the
> accompanying software
> > was (pg_dump, psql, etc)
> >
> > I get this error when I try to back up the database:
> > pg_dump: server version: 8.3.3; pg_dump version: 8.2.6
> > pg_dump: aborting because of version mismatch (Use the
> -i option to proceed
> > anyway.)
> > pg_dump: *** aborted because of error
> >
> > and likewise when I log into the psql client:
> > WARNING: You are connected to a server with major
> version 8.3,
> > but your psql client is major version 8.2. Some
> backslash commands,
> > such as \d, might not work properly.
> >
> > How could this have happened? I really need to be able
> to back up my
> > information. I reinstalled the software and it
> didn't change anything. What
> > do I do to upgrade pg_dump to the newest version?
>
> What OS are you running on?
>
> How did you perform this upgrade? In a packaged form like
> rpm? or
> did you compile from source?

yup, the postgres clients are usually shipped in their own packages, separate from the server package. I do have to manually upgrade the client packages on a Debian system.

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [pgsql-fr-generale] fusionner des bases

Première idée qui me viens... comme ça. Utiliser un ETL comme talend
par exemple. mais à mon avis c'est un vrai chantier d'analyse en
amont... Quel est la taille sur disque des bases ? Histoire d'avoir une
petite idée...

Le Wed, 17 Sep 2008 16:56:22 +0200,
"SOULABAILLE Samantha" <soulabaille.s@mipih.fr> a écrit :

> Bonjour,
>
>
>
> J'ai un (énorme) souci avec deux bases hébergées sur deux serveurs
> pour deux sites web.
>
>
>
> Elles ont exactement la même structure (schéma, tables), seules les
> données sont différentes, même version de postgresql pour le serveur.
>
> Cela fait quelques années qu'elles sont en prod et qu'elle se sont
> remplies allègrement.
>
> On me demande maintenant si on peut les fusionner pour ne garder
> qu'un site.
>
>
>
> Les bases sont énormes, une bonne soixantaine de tables, des
> serials/foreign key/blob/oids un peu partout, bref un joyeux petit
> monde.
>
>
>
> Tout d'abord une telle opération est-elle possible ? Sachant qu'on a
> aussi certaines obligations par rapport à l'indisponibilité de ces
> sites web (je ne suis pas sure qu'on puisse se permettre plus d'une
> journée d'arrêt ou ½ journée).
>
> Est-ce qu'il existe des outils, ou des procédures, au moins quelque
> chose qui permette de faire cela ?
>
>
>
>
>
>
>
> Samantha
>
> N'imprimez ce message que si vous en avez l'utilité
>
>
>

--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale

Re: [GENERAL] FAQ: how to read EXPLAYN ANALYZE and act upon

On Wed, Sep 17, 2008 at 9:43 AM, Ivan Sergio Borgonovo
<mail@webthatworks.it> wrote:
> The subjects says it all.

A couple of links that may be of use:

http://pooteeweet.org/files/phpworks06/explaining_explain.pdf

http://redivi.com/~bob/oscon2005_pgsql_pdf/OSCON_Explaining_Explain_Public.pdf

Good luck.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [pgsql-es-ayuda] framework webtranslator

2008/9/17 Teófilo Oviedo <teus79@active.com.py>:
> Alvaro,
>
> Es a modo de poder darle ya un inicio a todo esto.
> Por lo que he visto y seguido la seguidilla de mensajes, no hay gente
> que quiera dar continuidad al webtraslator, que a mi parecer era una
> herramienta excelente. Pero que lastimosamente no conozco ni Python ni
> Jango para poder dar una manito.
>
> Por de pronto, mi ofrecimiento es:
> - Colaborar con la traducción de los textos del manual y;
> - Colaborar con un espacio de hosting con o sin dominio, cualquiera sea.
> Si quieren puede ser de puchero incluído.
>
> Pero esto ya está lléndose a la deriva y nadie toma de la piola que se
> nos escapa de vuelta la oportunidad de hacer la traducción.
>
> Está mi intención de colaboración, basta que los demás dejen de discutir
> por sensibilidades emocionales y dar definiciones más profundas que el
> mar como el de cómo va a ser el punto a parte, si en negrita o tipo
> cursiva.
>
> Mis gratos saludos a la lista,
>
>
>
> El mié, 17-09-2008 a las 10:30 -0400, Alvaro Herrera escribió:
>> Teófilo Oviedo escribió:
>> > Alvaro,
>> >
>> > Si hace falta un lugar donde hostear el sitio para la traducción tengo
>> > máquinas a disposición.
>> > Si mantienes el dominio postgresql.cl avisame para iniciar ya con esto
>> > de la traducción, que ya está llevando su tiempo.
>>
>> Hola,
>>
>> No mantengo ni postgresql.cl ni el sitio de webtranslator. Con respecto
>> a hostear l10n.postgresql.cl no creo que sea necesario, ya que es un
>> proyecto inconcluso (digamos, concluido hasta un 90%) y sin mantenedor,
>> pero dado que las personas que se quieren hacer cargo de la traducción
>> no están interesadas en mantenerlo, creo que no vale la pena gastar
>> espacio y tiempo en ese sitio.
>>
> --
> Teófilo Oviedo
> ACTIVE S.R.L.
> http://www.active.com.py
>
> --
> TIP 3: Si encontraste la respuesta a tu problema, publícala, otros te lo agradecerán
>

Me sumo !

Slds

J.

--
Cumprimentos
jchavez
linux User #397972 on http://counter.li.org/
--
TIP 1: para suscribirte y desuscribirte, visita http://archives.postgresql.org/pgsql-es-ayuda

Re: Column level privileges was:(Re: [HACKERS] Extending grant insert on tables to sequences)

On 9/17/08, Stephen Frost <sfrost@snowman.net> wrote:
>
> > A piece which can be broken off pretty easily is adding support to track
> > the columns used through to the executor so we can check the permissions
> > in the right place.
>
> Jamie, have you had a chance to work on this? It's next on my list and
> I'll start working on it tonight unless you've had a chance to get to
> it. Please let me know.
>

not really, i start to read the code... but was interrupted for a new
task... (if we only could send kill -9 signals to work tasks ;)

--
regards,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-es-ayuda] ¿Como Se puede Pedir Ayuda en PGsql-es-ayuda?

On 9/17/08, Gabriel Gañán <posnofo@hotmail.com> wrote:
> Buenas de Nuevo. Soy Gabriel !
> Al no obtener respuesta a mi pregunta, y ver que os contestabais entre
> vosotros. Me he puesto a recapacitar, si un newbe guidowsero, puede hacer
> preguntas basicas en estas listas.

Personalmente empeze a leer tu mail anterior y vi un parrafo grande en
el que al menos las primeras lineas (saludo, introduccion, historia ;)
me resultaron sumamente aburridas y perdi el interes en el resto del
mail...

el unico requisito que debes tener presente es que tenemos nuestros
trabajos y nuestro tiempo copado... trata de ser directo en tus
preguntas (sin mucha vuelta) y claro de mostrar que hiciste tu
tarea... (la verdad no se si investigaste porque nunca termine de leer
tu correo)...

PS.. si son como yo, nunca terminaron de leer este tampoco ;)

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
TIP 4: No hagas 'kill -9' a postmaster

[pgsql-es-ayuda] Compatibilidad Visual Basic 6.0

Saludos cordiales, somos desarrolladores de sistemas en la CD de México.

Queremos saber la compatibilidad para emigrar un sistema desarrollado en visual Basic 6.0 (utilizamos base datos ACCESS)  y utilizar su Postgre SQL como servidor de base de datos.

 Atentamente

Ing Efraín Caporali C.

TEL (0155) 5970-4698

Mail: Efraín.caporali@prodigy.net.mx

 

 

Re: [BUGS] BUG #4421: convert_to() should be immutable

andipeer@gmx.net wrote:
> PostgreSQL version: 8.3.3
> Operating system: Linux
> Description: convert_to() should be immutable
> Details:
>
> The function convert_to(string text, dest_encoding name) is not allowed to
> be used in a index expression, because it is not marked as "IMMUTABLE".
> According to the documentation, a function is immutable if it does not
> modify the database, and for the same arguments, it returns always the same
> results. I think that all of these conditions can be applied to the
> convert_to()-function, therefore it should be marked as "IMMUTABLE".

You can change the way a conversion is done with CREATE/DROP CONVERSION.
That's why it can't be IMMUTABLE.

(I doubt any sane person would actually do that, but that's another debate)

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [pgsql-es-ayuda] framework webtranslator

Alvaro,

Es a modo de poder darle ya un inicio a todo esto.
Por lo que he visto y seguido la seguidilla de mensajes, no hay gente
que quiera dar continuidad al webtraslator, que a mi parecer era una
herramienta excelente. Pero que lastimosamente no conozco ni Python ni
Jango para poder dar una manito.

Por de pronto, mi ofrecimiento es:
- Colaborar con la traducción de los textos del manual y;
- Colaborar con un espacio de hosting con o sin dominio, cualquiera sea.
Si quieren puede ser de puchero incluído.

Pero esto ya está lléndose a la deriva y nadie toma de la piola que se
nos escapa de vuelta la oportunidad de hacer la traducción.

Está mi intención de colaboración, basta que los demás dejen de discutir
por sensibilidades emocionales y dar definiciones más profundas que el
mar como el de cómo va a ser el punto a parte, si en negrita o tipo
cursiva.

Mis gratos saludos a la lista,

El mié, 17-09-2008 a las 10:30 -0400, Alvaro Herrera escribió:
> Teófilo Oviedo escribió:
> > Alvaro,
> >
> > Si hace falta un lugar donde hostear el sitio para la traducción tengo
> > máquinas a disposición.
> > Si mantienes el dominio postgresql.cl avisame para iniciar ya con esto
> > de la traducción, que ya está llevando su tiempo.
>
> Hola,
>
> No mantengo ni postgresql.cl ni el sitio de webtranslator. Con respecto
> a hostear l10n.postgresql.cl no creo que sea necesario, ya que es un
> proyecto inconcluso (digamos, concluido hasta un 90%) y sin mantenedor,
> pero dado que las personas que se quieren hacer cargo de la traducción
> no están interesadas en mantenerlo, creo que no vale la pena gastar
> espacio y tiempo en ese sitio.
>
--
Teófilo Oviedo
ACTIVE S.R.L.
http://www.active.com.py

--
TIP 3: Si encontraste la respuesta a tu problema, publ�cala, otros te lo agradecer�n

Re: [pgsql-es-ayuda] Editar los datos de Consultas y que se guarden automaticamente en la BD....

On 9/16/08, Gabriel Gañán <posnofo@hotmail.com> wrote:
> Quiero tener Una consulta de 3 o 4 tablas, y que si
> hago un cambio en la vista o en la consulta se guarden en las tablas.

Es decir, quieres vistas actualizables...

En Postgres las vistas no son actualizables de forma automática,
necesitas crear reglas que transformen el update en la vista a updates
en las tablas.

http://www.postgresql.org/docs/8.3/static/rules-update.html

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
TIP 8: explain analyze es tu amigo

Re: [HACKERS] Patch for SQL-standard negative valued year-month literals

Tom Lane wrote:
>"Stephen R. van den Berg" <srb@cuci.nl> writes:
>> Intervals are a scalar, not an addition of assorted values, alternating signs
>> between fields would be wrong.

>Sorry, you're the one who's wrong on that. We've treated intervals as
>three independent fields for years now (and before that it was two
>independent fields).

Ok, didn't know that.
Let's put it differently then: I can understand that the standard
considers it a scalar and not an addition, but apparently the addition
characteristic is being used in Postgres code already; that makes it
undesirable to change it indeed.
--
Sincerely,
Stephen R. van den Berg.

He did a quarter of the work in *half* the time!

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [pgsql-fr-generale] Importation

Hop,

> C'est toujours la bonne façon de procéder?

Je te conseille vivement pgLoader que Dimitri développe. Ça fait
exactement ce que tu cherches:

Charger des tables vides ou compléter des tables avec des données
existantes, en rejetant les tuples malformés ou déjà présents (il te
faudra donc t'assurer que dans tes fichiers tu as une clé (primaire?)
identique à une clé unique de la table (la clé primaire?) permettant
d'identifier les enregistrements de façon unique).

Tu dois tout d'abord LIRE ceci:
http://pgloader.projects.postgresql.org/

Le reste est là:
http://pgfoundry.org/projects/pgloader/

Si tu as des suggestions, tu peux joindre Dimitri de plein de façons. La
plus sympa c'est probablement de le "pinguer" sur l'irc:
serveur irc.freenode.net
channel #postgresqlfr
-> dim

a+

--
Jean-Paul Argudo
www.PostgreSQLFr.org
www.Dalibo.com

--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale

Re: [pgsql-es-ayuda] Evitar que se ejecute un trigger on update

el-PRiNCiPiTo escribió:

> Tengo un trigger creado que se ejecuta al hacer un update en la
> tabla. El caso es que ahora se da una situación en la que la tabla puede
> recibir actualizaciones y dicho trigger no es necesario que se ejecute
> (si se ejecuta altera el update que pretendemos hacer resultando
> inútil). Así que para no tener que modificar el trigger que es bastante
> complejo se me ha ocurrido que a lo mejor hay alguna forma para hacer el
> update avisándole que quiero que ignore los posibles triggers que haya,
> ¿eso sería posible?
> Si no existe nada así se me ha ocurrido añadir a la tabla un campo
> más y meter todo el trigger en un if que evalúe ese campo algo así:

No existe que yo sepa ...

Lo malo de la solución que propones es que requerirás almacenar esa
nueva columna en todos los registros de la tabla, cuando en realidad por
sí misma no sirve ningún propósito útil.

¿Has pensado en diseñar de alguna forma más conveniente los datos?
Quizás el problema que tienes es que el diseño no es apropiado al
problema que quieres modelar.

--
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
"Now I have my system running, not a byte was off the shelf;
It rarely breaks and when it does I fix the code myself.
It's stable, clean and elegant, and lightning fast as well,
And it doesn't cost a nickel, so Bill Gates can go to hell."
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

Re: [pgsql-es-ayuda] Evitar que se ejecute un trigger on update

On 9/17/08, el-PRiNCiPiTo <el-PRiNCiPiTo@terra.es> wrote:
> Hola.
> Tengo un trigger creado que se ejecuta al hacer un update en la tabla. El
> caso es que ahora se da una situación en la que la tabla puede recibir
> actualizaciones y dicho trigger no es necesario que se ejecute (si se
> ejecuta altera el update que pretendemos hacer resultando inútil). Así que
> para no tener que modificar el trigger que es bastante complejo se me ha
> ocurrido que a lo mejor hay alguna forma para hacer el update avisándole que
> quiero que ignore los posibles triggers que haya, ¿eso sería posible?
> Si no existe nada así se me ha ocurrido añadir a la tabla un campo más y
> meter todo el trigger en un if que evalúe ese campo algo así:
>

personalmente me parece una falla en el diseño. la idea de un trigger
es dejar que el servidor se encargue de ciertas tareas que deben
realizarse siempre que se inserte/actualize/borre un registro de ese
modo no dependes de que los programadores *recuerden* que eso se debe
hacer... pero como lo tienes ahora, nuevamente dependes del
programador (que se acuerde de "apagar" el trigger).

Para mi lo mas coherente es tener un if dentro del trigger que se
encargue de determinar si se deben hacer cambios o no.

si realmente quieres apagar el trigger a partir del 8.1 tienes
ALTER TABLE tablita DISABLE TRIGGER [nombre_trigger | USER]

pero creo que eso afecta a todas las sesiones no estoy seguro...

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157
--
TIP 6: ¿Has buscado en los archivos de nuestra lista de correo?
http://archives.postgresql.org/pgsql-es-ayuda

Re: [SQL] Trigger Procedure Error: NEW used in query that is not in a rule

HI,

On Sat, Aug 11, 2007 at 9:07 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:
> On Sat, Aug 11, 2007 at 02:45:09AM -0500, Javier Fonseca V. wrote:
>> I think that it's working alright except for the next line:
>
> doing this in plpgsql is very complicated (or even impossible assuming
> that any table can have the same trigger). i would rather suggest using
> pl/perl - writing something like this in pl/perl is very simple.
>

I am in the same situation where I would like to execute a query similar to
EXECUTE 'INSERT INTO ' || quote_ident(SOMEDYNAMICNTABLENAME) || '
SELECT new.*';

I've looked at the plperl documentation, and experimented a bit, but
I'm not even sure how to start this in pl/perl. I hoped to extract
columns from $_TD->{new} but it doesn't seem to work.
Would you have a little example on how you would do it?

Thanks in advance!

Raph

> depesz
>
> --
> quicksil1er: "postgres is excellent, but like any DB it requires a
> highly paid DBA. here's my CV!" :)
> http://www.depesz.com/ - blog dla ciebie (i moje CV)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>

--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [HACKERS] Common Table Expressions (WITH RECURSIVE) patch

> > To enable physical_tlist optimization, it seems build_physical_tlist,
> > use_physical_tlist and disuse_physical_tlist need to be
> > changed. build_physical_tlist and use_physical_tlist have been already
> > patched and only disuse_physical_tlist needs to be patched. Any other
> > place I miss to enable the optimization?
>
> IIRC, the comment for build_physical_tlist hadn't been patched, but
> yeah that seems like about it.

Yeah, I need to fix sloppy comments in the existing patches all over
the places:-)
--
Tatsuo Ishii
SRA OSS, Inc. Japan

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[pgsql-fr-generale] Importation

Bonjour,

Régulièrement j'aurais un fichier au format csv à incorporer dans une
table postgreSql.
Je peux développer un truc en PHP, mais il y a peut-être des outils
postgresql.

Il y a la documentation postgresql:
> sous psql
> COPY matable FROM '/repertoire/fichier.csv' WITH DELIMITER ';' ;

Puis pour les mises à jour:
> je te suggère d'utiliser une table temporaire.
> puis d'insérer les records absents de table principale.

C'est toujours la bonne façon de procéder?

A+
--
Pascal

--
Sent via pgsql-fr-generale mailing list (pgsql-fr-generale@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-fr-generale

[pgsql-fr-generale] fusionner des bases

Bonjour,

 

J’ai un (énorme) souci avec deux bases hébergées sur deux serveurs pour deux sites web.

 

Elles ont exactement la même structure (schéma, tables), seules les données sont différentes, même version de postgresql pour le serveur.

Cela fait quelques années qu’elles sont en prod et qu’elle se sont remplies allègrement.

On me demande maintenant si on peut les fusionner pour ne garder qu’un site.

 

Les bases sont énormes, une bonne soixantaine de tables, des serials/foreign key/blob/oids un peu partout,  bref un joyeux petit monde.

 

Tout d’abord une telle opération est-elle possible ? Sachant qu’on a aussi certaines obligations par rapport à l’indisponibilité de ces sites web (je ne suis pas sure qu’on puisse se permettre plus d’une journée d’arrêt ou ½ journée).

Est-ce qu’il existe des outils, ou des procédures, au moins quelque chose qui permette de faire cela ?

 

 

 

Samantha

N'imprimez ce message que si vous en avez l'utilité

 

[BUGS] BUG #4422: select ... where ... NOT EXISTS / NOT IN

The following bug has been logged online:

Bug reference: 4422
Logged by: vasile
Email address: vasile@gmail.com
PostgreSQL version: 8.2.4
Operating system: Centos 4
Description: select ... where ... NOT EXISTS / NOT IN
Details:

I have this 2 similar queries.

Why the 1st query is returning 1000+ rows and the 2nd one no rows ?
The col1 is not empty in both tables.

1)
SELECT col1, col2, col3
FROM table1 t1
WHERE NOT EXISTS (SELECT t2.col1 FROM table2 t2 WHERE t1.col1 = t2.col1 );

2)
SELECT col1, col2, col3
FROM table1 t1
WHERE t1.col1 NOT IN (SELECT t2.col1 FROM table2 t2 );

If I build the query with "LEFT JOIN" I have the same result set like in the
1st query:

SELECT t1.col1, t1.col2, t1.col3
FROM table1 t1
LEFT JOIN table2 t2 ON (t1.col1 = t2.col1)
WHERE t2.col1 is null;

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4421: convert_to() should be immutable

The following bug has been logged online:

Bug reference: 4421
Logged by:
Email address: andipeer@gmx.net
PostgreSQL version: 8.3.3
Operating system: Linux
Description: convert_to() should be immutable
Details:

The function convert_to(string text, dest_encoding name) is not allowed to
be used in a index expression, because it is not marked as "IMMUTABLE".
According to the documentation, a function is immutable if it does not
modify the database, and for the same arguments, it returns always the same
results. I think that all of these conditions can be applied to the
convert_to()-function, therefore it should be marked as "IMMUTABLE".

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [ADMIN] Heavy postgres process

Yes that's true and that's planned. We will migrate to Oracle. But as of now need some pointers on solving the problem in hand.

Regards,
Vivek

-----Original Message-----
From: Guido Barosio [mailto:gbarosio@gmail.com]
Sent: Wednesday, September 17, 2008 6:39 PM
To: Vivek_Sharan
Cc: Scott Marlowe; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Heavy postgres process

Well, the answer is shor Vivekt:

Upgrade that postgresql ASAP, it's too way old.

gb.-

On Wed, Sep 17, 2008 at 9:29 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote:
> I'm using postgres 7.4.5
>
> Regards,
> Vivek
>
>
>
> -----Original Message-----
> From: Guido Barosio [mailto:gbarosio@gmail.com]
> Sent: Tuesday, September 16, 2008 8:08 PM
> To: Vivek_Sharan
> Cc: Scott Marlowe; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Heavy postgres process
>
> On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote:
>> Thanks for the information so far
>> My Application runs on FreeBSd box and main technological component are Apache and mod Perl, database is postgres. I have already scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idle processes but command (current_query) column is empty. So I cannot make out what these processes are doing.
>> TOP on this server doesn't have any option available to further break down processes. And hitting 'M; did change anything because this is not available with top on this server. Following is the output of top if filtered for only postgres user
>>
>> *****************************************************************************
>> last pid: 92308; load averages: 0.00, 0.03, 0.05
>> 78 processes: 2 running, 76 sleeping
>> CPU states: 1.6% user, 0.0% nice, 3.4% system, 0.0% interrupt, 94.9% idle
>> Mem: 413M Active, 2122M Inact, 534M Wired, 140M Cache, 199M Buf, 533M Free
>> Swap: 4096M Total, 3880K Used, 4092M Free
>>
>> PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
>> 90976 postgres 2 0 83568K 76016K sbwait 2 0:32 2.83% 2.83% postgres
>> 90963 postgres 2 0 83396K 75876K sbwait 2 0:25 1.37% 1.37% postgres
>> 90919 postgres 2 0 83808K 76244K sbwait 1 0:32 0.39% 0.39% postgres
>> 87341 postgres 2 0 6388K 756K select 3 2:35 0.00% 0.00% postgres
>> 87340 postgres 2 0 7200K 1224K select 0 1:41 0.00% 0.00% postgres
>> 90961 postgres 2 0 83580K 76008K sbwait 0 0:30 0.00% 0.00% postgres
>> 90920 postgres 2 0 83636K 76068K sbwait 0 0:29 0.00% 0.00% postgres
>> 90934 postgres 2 0 83664K 76012K sbwait 0 0:27 0.00% 0.00% postgres
>> 90924 postgres 2 0 83408K 75872K sbwait 0 0:25 0.00% 0.00% postgres
>> 90915 postgres 2 0 79292K 72664K sbwait 0 0:23 0.00% 0.00% postgres
>> 90955 postgres 2 0 79644K 73040K sbwait 0 0:22 0.00% 0.00% postgres
>> 90979 postgres 2 0 78904K 72260K sbwait 0 0:17 0.00% 0.00% postgres
>> 87339 postgres 2 0 74756K 672K select 1 0:12 0.00% 0.00% postgres
>> 90921 postgres 2 0 75504K 59848K sbwait 3 0:01 0.00% 0.00% postgres
>> 90927 postgres 2 0 75540K 59296K sbwait 3 0:01 0.00% 0.00% postgres
>> 90962 postgres 2 0 75524K 56960K sbwait 0 0:01 0.00% 0.00% postgres
>> 90923 postgres 2 0 75540K 57584K sbwait 1 0:01 0.00% 0.00% postgres
>> 90914 postgres 2 0 75552K 57776K sbwait 1 0:01 0.00% 0.00% postgres
>> 90917 postgres 2 0 75524K 57256K sbwait 3 0:01 0.00% 0.00% postgres
>> 90922 postgres 2 0 75504K 57352K sbwait 1 0:01 0.00% 0.00% postgres
>> 90918 postgres 2 0 75508K 57748K sbwait 3 0:01 0.00% 0.00% postgres
>> 90933 postgres 2 0 75540K 53728K sbwait 2 0:01 0.00% 0.00% postgres
>> 90926 postgres 2 0 75484K 54928K sbwait 3 0:01 0.00% 0.00% postgres
>> 90931 postgres 2 0 75512K 20880K sbwait 3 0:00 0.00% 0.00% postgres
>> 90977 postgres 2 0 75512K 20584K sbwait 0 0:00 0.00% 0.00% postgres
>> 91005 postgres 2 0 75512K 19956K sbwait 0 0:00 0.00% 0.00% postgres
>> 90966 postgres 2 0 75488K 19056K sbwait 1 0:00 0.00% 0.00% postgres
>> 90986 postgres 2 0 75512K 19348K sbwait 1 0:00 0.00% 0.00% postgres
>> 90973 postgres 2 0 75512K 18140K sbwait 1 0:00 0.00% 0.00% postgres
>> 90989 postgres 2 0 75512K 18668K sbwait 2 0:00 0.00% 0.00% postgres
>> 90956 postgres 2 0 75488K 18320K sbwait 2 0:00 0.00% 0.00% postgres
>> 90998 postgres 2 0 75512K 17564K sbwait 3 0:00 0.00% 0.00% postgres
>> 90925 postgres 2 0 75488K 17412K sbwait 1 0:00 0.00% 0.00% postgres
>> 88881 postgres 2 0 75528K 7920K sbwait 0 0:00 0.00% 0.00% postgres
>> *****************************************************************************
>>
>> Output of vmstat command
>>
>> procs memory page disks faults cpu
>> r b w avm fre flt re pi po fr sr da0 da1 in sy cs us sy id
>> 0 0 0 423492 688492 40 0 0 0 52 57 0 0 50 11 50 53 47 -0
>>
>> *****************************************************************************
>> Output of systat command
>>
>>> systat
>>
>>
>> /0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10
>> Load Average |
>>
>> /0 /10 /20 /30 /40 /50 /60 /70 /80 /90 /100
>> postgres postgres X
>> *****************************************************************************
>> entries in pg_stat_activities
>>
>> datid | datname | procpid | usesysid | usename | current_query | query_start
>> -------+---------+---------+----------+----------+---------------+-------------
>> 17142 | wasdb | 90914 | 103 | was | |
>> 17142 | wasdb | 90917 | 103 | was | |
>> 17142 | wasdb | 90915 | 103 | was | |
>> 17142 | wasdb | 90918 | 103 | was | |
>> 17142 | wasdb | 90919 | 103 | was | |
>> 17142 | wasdb | 90920 | 103 | was | |
>> 17142 | wasdb | 90921 | 103 | was | |
>> 17142 | wasdb | 90922 | 103 | was | |
>> 17142 | wasdb | 90923 | 103 | was | |
>> 17142 | wasdb | 90924 | 103 | was | |
>> 17142 | wasdb | 90925 | 104 | audit | |
>> 17142 | wasdb | 90926 | 103 | was | |
>> 17142 | wasdb | 90927 | 103 | was | |
>> 17142 | wasdb | 90955 | 103 | was | |
>> 17142 | wasdb | 90956 | 104 | audit | |
>> 17142 | wasdb | 90961 | 103 | was | |
>> 17142 | wasdb | 90931 | 104 | audit | |
>> 17142 | wasdb | 90933 | 103 | was | |
>> 17142 | wasdb | 90934 | 103 | was | |
>> 17142 | wasdb | 90962 | 103 | was | |
>> 17142 | wasdb | 90963 | 103 | was | |
>> 17142 | wasdb | 90966 | 104 | audit | |
>> 17142 | wasdb | 90973 | 104 | audit | |
>> 17142 | wasdb | 90976 | 103 | was | |
>> 17142 | wasdb | 90977 | 104 | audit | |
>> 17142 | wasdb | 90979 | 103 | was | |
>> 17142 | wasdb | 90986 | 104 | audit | |
>> 17142 | wasdb | 90989 | 104 | audit | |
>> 17142 | wasdb | 92353 | 1 | postgres | |
>> 17142 | wasdb | 90998 | 104 | audit | |
>> 17142 | wasdb | 88881 | 1 | postgres | |
>> 17142 | wasdb | 91005 | 104 | audit | |
>> (32 rows)
>>
>> *****************************************************************************
>> Regards,
>> Vivek Sharan
>>
>>
>>
>> -----Original Message-----
>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>> Sent: Monday, September 15, 2008 9:24 PM
>> To: Vivek_Sharan
>> Cc: pgsql-admin@postgresql.org
>> Subject: Re: [ADMIN] Heavy postgres process
>>
>> Run top, hit M and the attach the output to a reply here and we'll take a look.
>>
>> On Mon, Sep 15, 2008 at 5:33 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote:
>>> Thanks for your reply but how would I calculate which processes are eating up more memory, When I check process list, its only postgres and apache processes running on my system and only postgres processes are heavy. System runs out of memory quickly.

>>>
>>> Regards,
>>> ~Vivek
>>>
>>>
>>> -----Original Message-----
>>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>>> Sent: Friday, September 12, 2008 11:18 PM
>>> To: Vivek_Sharan
>>> Cc: pgsql-admin@postgresql.org
>>> Subject: Re: [ADMIN] Heavy postgres process
>>>
>>> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote:
>>>> Hi Admin,
>>>>
>>>> I'm new to this I have few queries as listed below
>>>>
>>>> 1) Number of connections made with a particular database.
>>>
>>> Wait, how to find out how many connections there are, or how many can
>>> a particular db handle.
>>>
>>> For this kind of thing, look at the admin functions in the pgsql-sql docs:
>>>
>>> http://www.postgresql.org/docs/8.3/interactive/functions-admin.html
>>>
>>> specifically you want something like:
>>>
>>> select datname from pg_stat_activity;
>>> select datname, count(datname) from pg_stat_activity group by datname;
>>>
>>>> 2) And how can I check which process (PID) is responsible for the
>>>> connection and
>>>
>>> That table up there ^^^
>>>
>>>> 3) what all can make a postgres process as heavy as 70-80 MB in size
>>>
>>> you may not be measuring properly. When you say it's using 70-80 MB
>>> how do you know this? The numbers you see in top aren't necessarily
>>> what some folks think they ar.
>>>
>>> **************** CAUTION - Disclaimer *****************
>>> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
>>> for the use of the addressee(s). If you are not the intended recipient, please
>>> notify the sender by e-mail and delete the original message. Further, you are not
>>> to copy, disclose, or distribute this e-mail or its contents to any other person and
>>> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
>>> every reasonable precaution to minimize this risk, but is not liable for any damage
>>> you may sustain as a result of any virus in this e-mail. You should carry out your
>>> own virus checks before opening the e-mail or attachment. Infosys reserves the
>>> right to monitor and review the content of all messages sent to or from this e-mail
>>> address. Messages sent to or from this e-mail address may be stored on the
>>> Infosys e-mail system.
>>> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>>>
>>
>> --
>> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-admin
>>
>
> Vivek, which version of postgres you are using?
>
> gb.-
>
> --
> http://www.linkedin.com/in/gbarosio
>

--
http://www.linkedin.com/in/gbarosio

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [ADMIN] Heavy postgres process

I'm using postgres 7.4.5

Regards,
Vivek

-----Original Message-----
From: Guido Barosio [mailto:gbarosio@gmail.com]
Sent: Tuesday, September 16, 2008 8:08 PM
To: Vivek_Sharan
Cc: Scott Marlowe; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Heavy postgres process

On Tue, Sep 16, 2008 at 1:41 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote:
> Thanks for the information so far
> My Application runs on FreeBSd box and main technological component are Apache and mod Perl, database is postgres. I have already scanned pg_stat_activity and pg_listener table but could get any clue. Pg_stat_activity shows list of all idle processes but command (current_query) column is empty. So I cannot make out what these processes are doing.
> TOP on this server doesn't have any option available to further break down processes. And hitting 'M; did change anything because this is not available with top on this server. Following is the output of top if filtered for only postgres user
>
> *****************************************************************************
> last pid: 92308; load averages: 0.00, 0.03, 0.05
> 78 processes: 2 running, 76 sleeping
> CPU states: 1.6% user, 0.0% nice, 3.4% system, 0.0% interrupt, 94.9% idle
> Mem: 413M Active, 2122M Inact, 534M Wired, 140M Cache, 199M Buf, 533M Free
> Swap: 4096M Total, 3880K Used, 4092M Free
>
> PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
> 90976 postgres 2 0 83568K 76016K sbwait 2 0:32 2.83% 2.83% postgres
> 90963 postgres 2 0 83396K 75876K sbwait 2 0:25 1.37% 1.37% postgres
> 90919 postgres 2 0 83808K 76244K sbwait 1 0:32 0.39% 0.39% postgres
> 87341 postgres 2 0 6388K 756K select 3 2:35 0.00% 0.00% postgres
> 87340 postgres 2 0 7200K 1224K select 0 1:41 0.00% 0.00% postgres
> 90961 postgres 2 0 83580K 76008K sbwait 0 0:30 0.00% 0.00% postgres
> 90920 postgres 2 0 83636K 76068K sbwait 0 0:29 0.00% 0.00% postgres
> 90934 postgres 2 0 83664K 76012K sbwait 0 0:27 0.00% 0.00% postgres
> 90924 postgres 2 0 83408K 75872K sbwait 0 0:25 0.00% 0.00% postgres
> 90915 postgres 2 0 79292K 72664K sbwait 0 0:23 0.00% 0.00% postgres
> 90955 postgres 2 0 79644K 73040K sbwait 0 0:22 0.00% 0.00% postgres
> 90979 postgres 2 0 78904K 72260K sbwait 0 0:17 0.00% 0.00% postgres
> 87339 postgres 2 0 74756K 672K select 1 0:12 0.00% 0.00% postgres
> 90921 postgres 2 0 75504K 59848K sbwait 3 0:01 0.00% 0.00% postgres
> 90927 postgres 2 0 75540K 59296K sbwait 3 0:01 0.00% 0.00% postgres
> 90962 postgres 2 0 75524K 56960K sbwait 0 0:01 0.00% 0.00% postgres
> 90923 postgres 2 0 75540K 57584K sbwait 1 0:01 0.00% 0.00% postgres
> 90914 postgres 2 0 75552K 57776K sbwait 1 0:01 0.00% 0.00% postgres
> 90917 postgres 2 0 75524K 57256K sbwait 3 0:01 0.00% 0.00% postgres
> 90922 postgres 2 0 75504K 57352K sbwait 1 0:01 0.00% 0.00% postgres
> 90918 postgres 2 0 75508K 57748K sbwait 3 0:01 0.00% 0.00% postgres
> 90933 postgres 2 0 75540K 53728K sbwait 2 0:01 0.00% 0.00% postgres
> 90926 postgres 2 0 75484K 54928K sbwait 3 0:01 0.00% 0.00% postgres
> 90931 postgres 2 0 75512K 20880K sbwait 3 0:00 0.00% 0.00% postgres
> 90977 postgres 2 0 75512K 20584K sbwait 0 0:00 0.00% 0.00% postgres
> 91005 postgres 2 0 75512K 19956K sbwait 0 0:00 0.00% 0.00% postgres
> 90966 postgres 2 0 75488K 19056K sbwait 1 0:00 0.00% 0.00% postgres
> 90986 postgres 2 0 75512K 19348K sbwait 1 0:00 0.00% 0.00% postgres
> 90973 postgres 2 0 75512K 18140K sbwait 1 0:00 0.00% 0.00% postgres
> 90989 postgres 2 0 75512K 18668K sbwait 2 0:00 0.00% 0.00% postgres
> 90956 postgres 2 0 75488K 18320K sbwait 2 0:00 0.00% 0.00% postgres
> 90998 postgres 2 0 75512K 17564K sbwait 3 0:00 0.00% 0.00% postgres
> 90925 postgres 2 0 75488K 17412K sbwait 1 0:00 0.00% 0.00% postgres
> 88881 postgres 2 0 75528K 7920K sbwait 0 0:00 0.00% 0.00% postgres
> *****************************************************************************
>
> Output of vmstat command
>
> procs memory page disks faults cpu
> r b w avm fre flt re pi po fr sr da0 da1 in sy cs us sy id
> 0 0 0 423492 688492 40 0 0 0 52 57 0 0 50 11 50 53 47 -0
>
> *****************************************************************************
> Output of systat command
>
>> systat
>
>
> /0 /1 /2 /3 /4 /5 /6 /7 /8 /9 /10
> Load Average |
>
> /0 /10 /20 /30 /40 /50 /60 /70 /80 /90 /100
> postgres postgres X
> *****************************************************************************
> entries in pg_stat_activities
>
> datid | datname | procpid | usesysid | usename | current_query | query_start
> -------+---------+---------+----------+----------+---------------+-------------
> 17142 | wasdb | 90914 | 103 | was | |
> 17142 | wasdb | 90917 | 103 | was | |
> 17142 | wasdb | 90915 | 103 | was | |
> 17142 | wasdb | 90918 | 103 | was | |
> 17142 | wasdb | 90919 | 103 | was | |
> 17142 | wasdb | 90920 | 103 | was | |
> 17142 | wasdb | 90921 | 103 | was | |
> 17142 | wasdb | 90922 | 103 | was | |
> 17142 | wasdb | 90923 | 103 | was | |
> 17142 | wasdb | 90924 | 103 | was | |
> 17142 | wasdb | 90925 | 104 | audit | |
> 17142 | wasdb | 90926 | 103 | was | |
> 17142 | wasdb | 90927 | 103 | was | |
> 17142 | wasdb | 90955 | 103 | was | |
> 17142 | wasdb | 90956 | 104 | audit | |
> 17142 | wasdb | 90961 | 103 | was | |
> 17142 | wasdb | 90931 | 104 | audit | |
> 17142 | wasdb | 90933 | 103 | was | |
> 17142 | wasdb | 90934 | 103 | was | |
> 17142 | wasdb | 90962 | 103 | was | |
> 17142 | wasdb | 90963 | 103 | was | |
> 17142 | wasdb | 90966 | 104 | audit | |
> 17142 | wasdb | 90973 | 104 | audit | |
> 17142 | wasdb | 90976 | 103 | was | |
> 17142 | wasdb | 90977 | 104 | audit | |
> 17142 | wasdb | 90979 | 103 | was | |
> 17142 | wasdb | 90986 | 104 | audit | |
> 17142 | wasdb | 90989 | 104 | audit | |
> 17142 | wasdb | 92353 | 1 | postgres | |
> 17142 | wasdb | 90998 | 104 | audit | |
> 17142 | wasdb | 88881 | 1 | postgres | |
> 17142 | wasdb | 91005 | 104 | audit | |
> (32 rows)
>
> *****************************************************************************
> Regards,
> Vivek Sharan
>
>
>
> -----Original Message-----
> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
> Sent: Monday, September 15, 2008 9:24 PM
> To: Vivek_Sharan
> Cc: pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] Heavy postgres process
>
> Run top, hit M and the attach the output to a reply here and we'll take a look.
>
> On Mon, Sep 15, 2008 at 5:33 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote:
>> Thanks for your reply but how would I calculate which processes are eating up more memory, When I check process list, its only postgres and apache processes running on my system and only postgres processes are heavy. System runs out of memory quickly.
>>
>> Regards,
>> ~Vivek
>>
>>
>> -----Original Message-----
>> From: Scott Marlowe [mailto:scott.marlowe@gmail.com]
>> Sent: Friday, September 12, 2008 11:18 PM
>> To: Vivek_Sharan
>> Cc: pgsql-admin@postgresql.org
>> Subject: Re: [ADMIN] Heavy postgres process
>>
>> On Fri, Sep 12, 2008 at 2:12 AM, Vivek_Sharan <Vivek_Sharan@infosys.com> wrote:
>>> Hi Admin,
>>>
>>> I'm new to this I have few queries as listed below
>>>
>>> 1) Number of connections made with a particular database.
>>
>> Wait, how to find out how many connections there are, or how many can
>> a particular db handle.
>>
>> For this kind of thing, look at the admin functions in the pgsql-sql docs:
>>
>> http://www.postgresql.org/docs/8.3/interactive/functions-admin.html
>>
>> specifically you want something like:
>>
>> select datname from pg_stat_activity;
>> select datname, count(datname) from pg_stat_activity group by datname;
>>
>>> 2) And how can I check which process (PID) is responsible for the
>>> connection and
>>
>> That table up there ^^^
>>
>>> 3) what all can make a postgres process as heavy as 70-80 MB in size
>>
>> you may not be measuring properly. When you say it's using 70-80 MB
>> how do you know this? The numbers you see in top aren't necessarily
>> what some folks think they ar.
>>
>> **************** CAUTION - Disclaimer *****************
>> This e-mail contains PRIVILEGED AND CONFIDENTIAL INFORMATION intended solely
>> for the use of the addressee(s). If you are not the intended recipient, please
>> notify the sender by e-mail and delete the original message. Further, you are not
>> to copy, disclose, or distribute this e-mail or its contents to any other person and
>> any such actions are unlawful. This e-mail may contain viruses. Infosys has taken
>> every reasonable precaution to minimize this risk, but is not liable for any damage
>> you may sustain as a result of any virus in this e-mail. You should carry out your
>> own virus checks before opening the e-mail or attachment. Infosys reserves the
>> right to monitor and review the content of all messages sent to or from this e-mail
>> address. Messages sent to or from this e-mail address may be stored on the
>> Infosys e-mail system.
>> ***INFOSYS******** End of Disclaimer ********INFOSYS***
>>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-admin
>

Vivek, which version of postgres you are using?

gb.-

--
http://www.linkedin.com/in/gbarosio

--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin