Re: [SQL] Issue with postgres connectivity
On 2011-01-21, Arindam Hore wrote: > We are accessing database using ip address. try adding the IP addresses of some of the clients as seen by the server to /etc/hosts on the server. see if that helps. try connecting to the server locally using 'su postgres -c psql' see if that's slow too. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Control reached end of trigger procedure without RETURN
Buenos días: Tengo una función que se dispara en un trigger cuya finalidad es; crear un registro en otra tabla o actualizarlo y devolviendo el id del registro creado/modificado para guardarlo en la tabla desde la que se llama. Intentaré explicarlo mejor: tablaA --> Es la que dispara el trigger y en la que quiero guardar el id del registro modificado / creado en la tabla B. tablaB --> Digamos que es una tabla resumen de la tablaA y se mantiene desde el trigger de la misma, creando modificando y/o borrando los registros. Finalidad --> Eso, actualizar la tablaB desde la A y guardar en el registro de de la tablaA a que registro de la tablaB "pertenece". Cuando hago el insert me devuelve el error del asunto Control reached end of trigger procedure without RETURN y me indica la línea correspondiente al INSERT. ¿Acaso no continúa el flujo if (not found) y debería hacer el return NEW tras el insert? ¿y lo mismo en el update del exception? Agradezco de antemano vuestro tiempo y ayudas. Un saludo. P.D. Estructura de la función. DECLARE id_tablaB integer;-- Id de la tabla B. BEGIN IF (TG_OP = 'DELETE') THEN -- Descontar valores ELSIF (TG_OP = 'UPDATE') THEN -- Modificar valores. ELSIF (TG_OP = 'INSERT') THEN -- Asignar valores END IF; -- Update con nuevos valores y retornamos el id para actualizar detalle. UPDATE tablaB SET campo1=valor1, campo2=valor2 WHERE tablaA_id=NEW.tblaB_id RETURNING tablaB_id INTO id_tablaB; -- Si no existe, hacemos el insert y retornamos el id para actualizar detalle. IF (NOT FOUND) THEN BEGIN -- No existÌa el registro y lo creamos. -- -- AQUÍ DEVUELVEL EL ERROR. -- INSERT INTO tablaB ( campo1, campo2) VALUES ( valor1, valor2 ) RETURNING tablaB_id INTO id_tablaB; EXCEPTION WHEN UNIQUE_VIOLATION THEN UPDATE tablaB SET campo1=valor1, campo2=valor2 WHERE tablaA_id=NEW.tblaB_id RETURNING tablaB_id INTO id_tablaB; END; END IF; -- Borrado de ventas_imp si el resumen del % y factura es 0. DELETE FROM tablaB WHERE tablaA_id=id_tablaA AND (campo3=0 OR campo3 IS NULL); -- Guardamos el id de la tabla ventas_imp y retornamos el record modificado. NEW.tablaB_id := id_tablaB return NEW; END -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] question about reg. expression
On 2011-01-18, andrew1 wrote:
> hi all,
>
> these return t:
> select 'ab' ~ '[a-z]$'
this matches the b and the end of the string
> select 'ab' ~ '^[a-z]'
this matches the start of the string and the a
> select 'ab' ~ '^[a-z]$' returns f
> Can't I use ^ and $ at the same time to match, in this case?
> thanks.
the above expression only succeeds if the string is one character long
use '+' '*' or '{2}' etc after the '[a-z]' to allow it to match several letters
or use '^[a-z]|[a-z]$' to match any sting that starts or ends with a
letter.
what are you trying to find?
--
⚂⚃ 100% natural
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Control reached end of trigger procedure without RETURN
Buenos días: Tengo una función que se dispara en un trigger cuya finalidad es; crear un registro en otra tabla o actualizarlo y devolviendo el id del registro creado/modificado para guardarlo en la tabla desde la que se llama. Intentaré explicarlo mejor: tablaA --> Es la que dispara el trigger y en la que quiero guardar el id del registro modificado / creado en la tabla B. tablaB --> Digamos que es una tabla resumen de la tablaA y se mantiene desde el trigger de la misma, creando modificando y/o borrando los registros. Finalidad --> Eso, actualizar la tablaB desde la A y guardar en el registro de de la tablaA a que registro de la tablaB "pertenece". Cuando hago el insert me devuelve el error del asunto Control reached end of trigger procedure without RETURN y me indica la línea correspondiente al INSERT. ¿Acaso no continúa el flujo if (not found) y debería hacer el return NEW tras el insert? ¿y lo mismo en el update del exception? Agradezco de antemano vuestro tiempo y ayudas. Un saludo. P.D. Estructura de la función. DECLARE id_tablaB integer;-- Id de la tabla B. BEGIN IF (TG_OP = 'DELETE') THEN -- Descontar valores ELSIF (TG_OP = 'UPDATE') THEN -- Modificar valores. ELSIF (TG_OP = 'INSERT') THEN -- Asignar valores END IF; -- Update con nuevos valores y retornamos el id para actualizar detalle. UPDATE tablaB SET campo1=valor1, campo2=valor2 WHERE tablaA_id=NEW.tblaB_id RETURNING tablaB_id INTO id_tablaB; -- Si no existe, hacemos el insert y retornamos el id para actualizar detalle. IF (NOT FOUND) THEN BEGIN -- No existÌa el registro y lo creamos. -- -- AQUÍ DEVUELVEL EL ERROR. -- INSERT INTO tablaB ( campo1, campo2) VALUES ( valor1, valor2 ) RETURNING tablaB_id INTO id_tablaB; EXCEPTION WHEN UNIQUE_VIOLATION THEN UPDATE tablaB SET campo1=valor1, campo2=valor2 WHERE tablaA_id=NEW.tblaB_id RETURNING tablaB_id INTO id_tablaB; END; END IF; -- Borrado de ventas_imp si el resumen del % y factura es 0. DELETE FROM tablaB WHERE tablaA_id=id_tablaA AND (campo3=0 OR campo3 IS NULL); -- Guardamos el id de la tabla ventas_imp y retornamos el record modificado. NEW.tablaB_id := id_tablaB return NEW; END -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Issue with postgres connectivity
Sorry for late reply. We usually do vacuum analyze daily basis using cron. -Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Edward W. Rouse Sent: Friday, January 21, 2011 8:17 PM To: [email protected] Subject: Re: [SQL] Issue with postgres connectivity When was the last time you did a vacuum analyze? Edward W. Rouse -Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Arindam Hore Sent: Friday, January 21, 2011 6:44 AM To: Reinoud van Leeuwen; [email protected] Subject: Re: [SQL] Issue with postgres connectivity We are accessing database using ip address. -Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Reinoud van Leeuwen Sent: Friday, January 21, 2011 4:27 PM To: [email protected] Subject: Re: [SQL] Issue with postgres connectivity On Fri, Jan 21, 2011 at 10:45:37AM +, Arindam Hore wrote: > Hello All, > > I am facing a big problem in postgres connectivity using php application. It is taking almost 10 sec for connection establishment. All my applications were working perfectly 2 days before. Yesterday just it started giving problem. Using pg-admin also it is taking time to connect as well as same with opening server status window or with sql query window. > > Postgres is installed in linux environment. Don't know whether is it due to some virus or something else. > > Please provide me with some guidelines. Ask me any queries. this smells like DNS resolving... Reinoud -- __ "Nothing is as subjective as reality" Reinoud van [email protected] http://reinoud.van.leeuwen.net kvk 27320762 __ -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Issue with postgres connectivity
What I have seen is that, when I am trying to connect the db server from application server using ssh, the response was very slow. While from db server to application server it is very quick as usually. -Original Message- From: [email protected] [mailto:[email protected]] On Behalf Of Jasen Betts Sent: Saturday, January 22, 2011 2:24 PM To: [email protected] Subject: Re: [SQL] Issue with postgres connectivity On 2011-01-21, Arindam Hore wrote: > We are accessing database using ip address. try adding the IP addresses of some of the clients as seen by the server to /etc/hosts on the server. see if that helps. try connecting to the server locally using 'su postgres -c psql' see if that's slow too. -- ⚂⚃ 100% natural -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the system manager. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
