Re: [SQL] raise exception and transaction handling
Alternative that always works no matter how many raise exceptions there are:
Create a (perlu) function that opens a second connection to your database and
does the insert into m_proba.
Since this is a seperate connection, it's also a seperate transaction and thus
not rolled back by your "main" transaction.
>>> "Michal Kedziora" <[EMAIL PROTECTED]> 2007-07-29 13:33 >>>
Hi, Marcin
I'm not familiar with PREPARE TRANSACTION maby it could be done in that way.
But, you can use a EXCEPTION clause, and there put your insert.
IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = NEW.id) THEN
RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %',
rtrim(bledne);
END IF;
EXCEPTION
when RAISE_EXCEPTION then
INSERT INTO g.m_proba VALUES (1,2);
END;
It will work corect if you have only one RAISE EXCEPTION,becouse
RAISE_EXCEPTION concern every exception called by RAISE command.
I hope that will help.
- Origi
nal Message -
From: Marcin Krawczyk ( mailto:[EMAIL PROTECTED] )
To: pgsql-sql@postgresql.org
Sent: Saturday, July 28, 2007 10:54 PM
Subject: [SQL] raise exception and transaction handling
Hi,
I have a problem with transaction handling. What I need to do is execute an
INSERT command that would not be canceled by the
RAISE EXCEPTION command in AFTER UPDATE TRIGGER. A piece of code:
BEGIN
-- some computations
bledne := (SELECT g.q_sumka('Poz.' || lps || ' - min. cena: ' || cena || ' ' ||
waluta ||'; ') FROM g.m_lista WHERE idf = NEW.id);
IF EXISTS (SELECT 1 FROM g.m_lista WHERE idf = NEW.id) THEN
RAISE EXCEPTION 'CENY NIE SPELNIAJA WARUNKOW! %', rtrim(bledne);
BEGIN
INSERT INTO g.m_proba VALUES (1,2); -- this is the operation I need to perform
but the RAISE EXCEPTION above cancels it out
PREPARE TRANSACTION 'a';
COMMIT PREPARED 'a';
END;
END IF;
I tried to do it as shown above, with PREPARE and COMMIT but it's not working.
[SQL] hi
I want to fix the pay based on joining date and antedate...antedate is the last date... Joining date is 01-07-2007 Antedate is 04-07-2005.. On joining date I will fix the pay but before fixing the pay I will check whether antedate is there or not, if its there then I will see difference between 2 dates..if difference is 2 years then I will fix the pay with increments...if difference is less than 2 years then I will fix the pay with out increments... Take an example of above 2 dates..there is no 2 years difference between two dates... 01-07-2007 I will fix the pay...after 3 days current date = antedate...so again I have to fix the pay with out my intervention... Is there any jobs or schedules or triggers to call that function when current date and antedate is equal... Thanks & Regards Penchal Reddy Information transmitted by this e-mail is proprietary to Infinite Computer Solutions and / or its Customers and is intended for use only by the individual or the entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient or it appears that this mail has been forwarded to you without proper authority, you are notified that any use or dissemination of this information in any manner is strictly prohibited. In such cases, please notify us immediately at [EMAIL PROTECTED] and delete this email from your records.
Re: [SQL] Tunning PostgreSQL performance for views on Windows
How big are the underlying tables? If they are large, are you partitioning? Since the values only change daily, if the end result is a reasonable size, have you considered using a CTAS rather than views? LewisC --- Ranieri Mazili <[EMAIL PROTECTED]> wrote: > Hello, > > I'm developing a BI and as database it's using postgresql 8.2, how > data > are very detailed, I'm creating a view to consolidate the most > important > data, but the performance of view is very poor, 1 minute to perform > more > or less without where clause. > I need to know how I can increase the performance, if exist some > option > to do cache, because the view will change only one time per day. > My configuration is default, without modifications after install. > I'm using windows 2003 server with a dell server with 4GB of > memory. > > To create the view, I created some functions, and then perform they > on > one select like: > select A.field1, B.field2, ... from function_A() A, function_B() > B... > Is this the best way to do it? > > I appreciate any help. > > Thanks > > > ---(end of > broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq > --- Lewis R Cunningham An Expert's Guide to Oracle Technology http://blogs.ittoolbox.com/oracle/guide/ EnterpriseDB: The Definitive Reference http://tinyurl.com/39246e -- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[SQL] Connection Limit
Hello, I have a server running postgres 7.4.13 and am starting to see errors "FATAL: connection limit exceeded for non-superusers". I'm not sure which one of my applications are hogging all of the connections, is there a way debug this somehow? Thanks, Jon. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Connection Limit
I have another related question when running ps -fax i see the following 7476 ?S 0:00 | \_ postgres: mobileuser mobileextension [local] idle 8046 ?S 0:00 | \_ postgres: mobileuser mobileextension 127.0.0.1 idle I have a few processes that are running with host = 127.0.0.1 and then about 90% of all the processes are running as [local]. What does [local] mean, since in fact 127.0.0.1 is obviously local, how do they differ? Thanks, Jon. On 7/30/07, Jon Horsman <[EMAIL PROTECTED]> wrote: > Hello, > > I have a server running postgres 7.4.13 and am starting to see errors > "FATAL: connection limit exceeded for non-superusers". > > I'm not sure which one of my applications are hogging all of the > connections, is there a way debug this somehow? > > Thanks, > > Jon. > ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [SQL] Connection Limit
On Mon, Jul 30, 2007 at 05:41:51PM -0400, Jon Horsman wrote: > > I have a few processes that are running with host = 127.0.0.1 and then > about 90% of all the processes are running as [local]. What does > [local] mean, since in fact 127.0.0.1 is obviously local, how do they > differ? I _believe_ [local] means UNIX domain socket. As to your other question, either ps or pg_stat_activity is your friend. A -- Andrew Sullivan | [EMAIL PROTECTED] The very definition of "news" is "something that hardly ever happens." --Bruce Schneier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] Table-name as parameter to plpgsql
The following function failes to compile:
CREATE OR REPLACE FUNCTION test_func(p_table_name VARCHAR) RETURNS VOID AS $$
BEGIN
INSERT INTO p_table_name(some_field) VALUES('some_value');
END;
$$ LANGUAGE plpgsql;
Gives:
ERROR: syntax error at or near "$1"
LINE 1: INSERT INTO $1 (some_field) VALUES('some_value')
Any hints on how to use function-parameters as table-names like I'm trying to
above?
--
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11 | know how to do a thing and to watch |
PO. Box 529 Skøyen | somebody else doing it wrong, without |
0214 Oslo | comment.|
NORWAY | |
Tlf:+47 24 15 38 90 | |
Fax:+47 24 15 38 91 | |
Mobile: +47 909 56 963 | |
+-+
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Connection Limit
On 7/30/07, Jon Horsman <[EMAIL PROTECTED]> wrote: > Hello, > > I have a server running postgres 7.4.13 and am starting to see errors > "FATAL: connection limit exceeded for non-superusers". > > I'm not sure which one of my applications are hogging all of the > connections, is there a way debug this somehow? try netstat -an|grep 5432 assuming you're running pgsql on port 5432. The nice thing about this trick is that since postgresql uses /tmp/.s.PGSQL.5432 for local connections, you'll see those too. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Table-name as parameter to plpgsql
Andreas Joseph Krogh написа:
The following function failes to compile:
CREATE OR REPLACE FUNCTION test_func(p_table_name VARCHAR) RETURNS VOID AS $$
BEGIN
INSERT INTO p_table_name(some_field) VALUES('some_value');
END;
$$ LANGUAGE plpgsql;
Gives:
ERROR: syntax error at or near "$1"
LINE 1: INSERT INTO $1 (some_field) VALUES('some_value')
Any hints on how to use function-parameters as table-names like I'm trying to
above?
Use EXECUTE
(http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN).
--
Milen A. Radev
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[SQL] alter table table add column
Hi, Anybody knows how to add column with reference to BEFORE or AFTER any given column? Let say here's my table structure: Column | Type| Modifiers --+---+--- surname | character varying | lastname | character varying | address | character varying | And, I want to add the field name age with type integer after lastname OR before the address field. How to I do that? I would really appreciate your response. Thanks in advance. == Ronald Rojas Systems Administrator Linux Registered User #427229 == Arnold's Laws of Documentation: (1) If it should exist, it doesn't. (2) If it does exist, it's out of date. (3) Only documentation for useless programs transcends the first two laws.
Re: [SQL] [NOVICE] alter table table add column
[Please don't post the same question to many lists. Choose one. If you're unsure if it's the correct list for your question, ask if there's a more appropriate one. This question is find for -novice or - general. Thanks.] On Jul 30, 2007, at 23:19 , Ronald Rojas wrote: And, I want to add the field name age with type integer after lastname OR before the address field. How to I do that? Can't without dumping the database, altering the schema in the dump, and reloading. But why does it matter? Just call the columns in the order you want. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Migration from SQLite Help (Left Join)
=?ISO-8859-1?Q?Nis_J=F8rgensen?= <[EMAIL PROTECTED]> writes: > The problem seems to be that you expect > SELECT a > FROM b,c LEFT JOIN d > to be interpreted as > SELECT a > FROM (b CROSS JOIN c) LEFT JOIN d The depressing part of this report is that it sounds like sqlite has emulated this bit of mysql brain-damage ... regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
