Re: [GENERAL] Getting rows in statement-level triggers

On Fri, Oct 3, 2008 at 11:42 AM, Artacus <[EMAIL PROTECTED]> wrote: > > So the manual says there is no way for a statement-level trigger to >> examine the row(s) modified by the statement. >> >> Is there any way to get the xmin or cmin of the transaction that fired the >> trigger? Or can I look u

Re: [HACKERS] [GENERAL] Transactions within a function body

On Thu, Oct 2, 2008 at 6:46 PM, Bob Henkel <[EMAIL PROTECTED]> wrote: > Have you looked at creating a function in perl and creating a new > connection? Or using a dblink query which can create a new connection? > These two methods work. I have used them to insert to a log table regardless > of the

Re: [GENERAL] Getting rows in statement-level triggers

So the manual says there is no way for a statement-level trigger to examine the row(s) modified by the statement. Is there any way to get the xmin or cmin of the transaction that fired the trigger? Or can I look up the last xid for a table some where? Ok, so it took a lot of googling to fig

Re: [GENERAL] How do I save data and then raise an exception?

Gurjeet Singh wrote: On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera <[EMAIL PROTECTED] > wrote: Rob Richardson wrote: > Here's what I need to do: > > IF query_check_fails THEN > UPDATE some_table SET some_value = 0 WHERE some_conditio

[GENERAL] Getting rows in statement-level triggers

So the manual says there is no way for a statement-level trigger to examine the row(s) modified by the statement. Is there any way to get the xmin or cmin of the transaction that fired the trigger? Or can I look up the last xid for a table some where? -- Sent via pgsql-general mailing list (p

Re: [GENERAL] How do I save data and then raise an exception?

On Fri, Oct 3, 2008 at 7:14 AM, Alvaro Herrera <[EMAIL PROTECTED]>wrote: > Rob Richardson wrote: > > > Here's what I need to do: > > > > IF query_check_fails THEN > > UPDATE some_table SET some_value = 0 WHERE some_condition_is_true; > > RAISE EXCEPTION 'Look, you idiot, do it right next t

Re: [GENERAL] How do I save data and then raise an exception?

On Fri, Oct 3, 2008 at 7:41 AM, Jaime Casanova <[EMAIL PROTECTED] > wrote: > On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera > <[EMAIL PROTECTED]> wrote: > > Rob Richardson wrote: > > > >> Here's what I need to do: > >> > >> IF query_check_fails THEN > >> UPDATE some_table SET some_value = 0 WH

Re: [GENERAL] How do I save data and then raise an exception?

On Thu, Oct 2, 2008 at 8:44 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Rob Richardson wrote: > >> Here's what I need to do: >> >> IF query_check_fails THEN >> UPDATE some_table SET some_value = 0 WHERE some_condition_is_true; >> RAISE EXCEPTION 'Look, you idiot, do it right next time!'

Re: [GENERAL] How do I save data and then raise an exception?

Rob Richardson wrote: > Here's what I need to do: > > IF query_check_fails THEN > UPDATE some_table SET some_value = 0 WHERE some_condition_is_true; > RAISE EXCEPTION 'Look, you idiot, do it right next time!'; > END; > > I need the update to work, but I need to raise the exception so

[GENERAL] How do I save data and then raise an exception?

Greetings! The people who originally wrote the system I'm trying to work with did not know as much as they should have about working with databases, so I'm stuck with the following situation: The applicaton is written in C++ (MS Visual C++ 6, Windows XP, in case it matters). At one point, a re

Re: [GENERAL] Trigger disable for table

Andreas and Terry, thanks for answering and pointing me in the direction. Unfortunately i found out, that even without the trigger the updates take too much time for interactive applications. So i go for a batch-update. Kindly regards, Frank -- Sent via pgsql-general mailing list (pgsql-gen

[GENERAL] function returning setof..select versus select * from

What is the difference between: select foo(); and select * from foo(); Foo is defined as: CREATE OR REPLACE FUNCTION foo() RETURNS SETOF integer AS 'SELECT column from foo_table;' LANGUAGE 'sql' STABLE; Explain shows difference... explain select * from foo() "Function Scan on foo (cos

Re: [GENERAL] Transactions within a function body

No, in Oracle too SAVEPOINT and AUTONOMOUS transaction are different beasts. On Thu, Oct 2, 2008 at 9:27 PM, Bob Henkel <[EMAIL PROTECTED]> wrote: > Coming from an Oracle background my understanding is they're one in the > same. > > > > > On Thu, Oct 2, 2008 at 10:37 AM, Alvaro Herrera < > [EMAIL

Re: [GENERAL] tsearch 2 query

ok, you calculate headline() 19861 times, while you need only 20. Use subselect and will be surprized Oleg On Thu, 2 Oct 2008, Matthew Terenzio wrote: Thanks Tom, Sorry if that last post went over multiple times. I was getting a mailing failure (or so I thought) here is EXPLAIN ANALYZE . I re

Re: [GENERAL] tsearch2 query

On Thu, 2 Oct 2008, Matthew Terenzio wrote: There are less than 20,000 records being searched here, but the query takes several minutes. I know this may not be enough info, but would one suggest I optimize the query or put my attention towards other areas. SELECT id,date,headline as head,head

[GENERAL] Re: [Pkg-postgresql-public] Postgres major version support policy on Debian

Hi Markus, Markus Wanner [2008-10-02 12:49 +0200]: > first of all: thanks for packaging Postgres for Debian. I'm willing to > help with that. Nice! > Unfortunately we are stuck with several Postgres 8.2 installations from > etch backports, which are no longer maintained by the backports, because

Re: [GENERAL] Transactions within a function body

Coming from an Oracle background my understanding is they're one in the same. On Thu, Oct 2, 2008 at 10:37 AM, Alvaro Herrera <[EMAIL PROTECTED]>wrote: > Bob Henkel escribió: > > Have you looked at creating a function in perl and creating a new > > connection? Or using a dblink query which can

Re: [GENERAL] tsearch 2 query

"Matthew Terenzio" <[EMAIL PROTECTED]> writes: > here is EXPLAIN ANALYZE . I really need to work on my skills at analyzing > these: > Limit (cost=105505.78..105505.83 rows=20 width=655) (actual > time=74806.973..74807.037 rows=20 loops=1) >-> Sort (cost=105505.78..10.44 rows=19861 width

Re: [GENERAL] Transactions within a function body

Have you looked at creating a function in perl and creating a new connection? Or using a dblink query which can create a new connection? These two methods work. I have used them to insert to a log table regardless of the parent transaction being commited or rolled back. A old example I posted of u

Re: [GENERAL] Transactions within a function body

Gurjeet Singh escribió: > I have seen this feature being asked for, and this work-around suggested so > many times. If plpgql does it internally, why not provide a clean interface > for this? Is there some road-block, or that nobody has ever tried it? Initially we aimed at just exposing SAVEPOINT

Re: [GENERAL] Transactions within a function body

Bob Henkel escribió: > Have you looked at creating a function in perl and creating a new > connection? Or using a dblink query which can create a new connection? > These two methods work. I have used them to insert to a log table regardless > of the parent transaction being commited or rolled back.

Re: [GENERAL] tsearch 2 query

Thanks Tom, Sorry if that last post went over multiple times. I was getting a mailing failure (or so I thought) here is EXPLAIN ANALYZE . I really need to work on my skills at analyzing these: Limit (cost=105505.78..105505.83 rows=20 width=655) (actual time=74806.973..74807.037 rows=20 loops=1)

Re: [GENERAL] Transactions within a function body

On Thu, Oct 2, 2008 at 8:40 PM, Alvaro Herrera <[EMAIL PROTECTED]>wrote: > Reg Me Please escribió: > > Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto: > > > > You can nest blocks arbitrarily, giving you the chance to selectively > > > rollback pieces of the function. It's only a b

Re: [GENERAL] Transactions within a function body

Have you looked at creating a function in perl and creating a new connection? Or using a dblink query which can create a new connection? These two methods work. I have used them to insert to a log table regardless of the parent transaction being commited or rolled back. A old example I posted of u

Re: [GENERAL] Transactions within a function body

Alvaro Herrera <[EMAIL PROTECTED]> writes: > Reg Me Please escribió: >> You mean I can issue a ROLLBACK command within a BEGIN...END; block to roll >> it >> back? > No -- I mean you can use BEGIN/EXCEPTION/END blocks as you like, nesting > them or putting one after another. Complementing this w

Re: [GENERAL] Transactions within a function body

Il Thursday 02 October 2008 17:10:23 Alvaro Herrera ha scritto: > Reg Me Please escribió: > > Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto: > > > You can nest blocks arbitrarily, giving you the chance to selectively > > > rollback pieces of the function. It's only a bit more awkw

Re: [GENERAL] Transactions within a function body

Reg Me Please escribió: > Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto: > > You can nest blocks arbitrarily, giving you the chance to selectively > > rollback pieces of the function. It's only a bit more awkward. > > You mean I can issue a ROLLBACK command within a BEGIN...END;

Re: [GENERAL] tsearch 2 query

"Matthew Terenzio" <[EMAIL PROTECTED]> writes: > There are less than 20,000 records being searched here, but the query takes > several minutes. > I know this may not be enough info, but would one suggest I optimize the > query or put my attention towards other areas. What does EXPLAIN ANALYZE show

Re: [GENERAL] That was easy

It's strange that you decided to make comparison to Oracle on this particular matter. Oracle doesn't require during upgrades exporting all the data from the database and then importing - Oracle does upgrades "in-place", while with PG you can't upgrade without dump/restore. And this becomes a pr

[GENERAL] tsearch 2 query

There are less than 20,000 records being searched here, but the query takes several minutes. I know this may not be enough info, but would one suggest I optimize the query or put my attention towards other areas. SELECT id,date,headline as head,headline(body,q),rank(vectors,q),timestamp FROM sto

Re: [GENERAL] W2K8

We have a server running in our test environment under 2k8 and it is working for what we need to. It's not under heavy load, though. We have quite a few 2k3 64bit installations that postgres keeps on keepin' on. -Raul Carolus Dave Page wrote: On Thu, Oct 2, 2008 at 2:02 PM, Howard Cole <[EM

[GENERAL] tsearch2 query

There are less than 20,000 records being searched here, but the query takes several minutes. I know this may not be enough info, but would one suggest I optimize the query or put my attention towards other areas. SELECT id,date,headline as head,headline(body,q),rank(vectors,q),timestamp FROM sto

Re: [GENERAL] Transactions within a function body

Il Thursday 02 October 2008 16:15:10 Alvaro Herrera ha scritto: > Reg Me Please escribió: > > Well, if it is a limitation, and having it would lead to a "better > > product", why not making it a feature for the next still-open release? > > Because no one is working on implementing it? > > > In my o

Re: [GENERAL] Transactions within a function body

Reg Me Please escribió: > Well, if it is a limitation, and having it would lead to a "better product", > why not making it a feature for the next still-open release? Because no one is working on implementing it? > In my opinion that's more than a limitation, it's a missing feature. > In your code

Re: [GENERAL] Import German Number Format

On Thu, Oct 2, 2008 at 9:35 AM, Tim Semmelhaack <[EMAIL PROTECTED]> wrote: > The numbers are formatted with decimal comma ',' (as usual in Germany) > instead of the decimal point '.' > > When I try to import this data Postgres crashes, so I think I have to > change a parameter with SET? Does anybod

Re: [GENERAL] Import German Number Format

am Thu, dem 02.10.2008, um 15:35:44 +0200 mailte Tim Semmelhaack folgendes: > Hello, > > I have to import a huge number of data sets of data sets with "Copy from". > > The numbers are formatted with decimal comma ',' (as usual in Germany) > instead of the decimal point '.' > > When I try to imp

[GENERAL] Import German Number Format

Hello, I have to import a huge number of data sets of data sets with "Copy from". The numbers are formatted with decimal comma ',' (as usual in Germany) instead of the decimal point '.' When I try to import this data Postgres crashes, so I think I have to change a parameter with SET? Does anybod

Re: [GENERAL] W2K8

On Thu, Oct 2, 2008 at 2:02 PM, Howard Cole <[EMAIL PROTECTED]> wrote: > >> I think it does. It certaily works on w2k3 x64 and it should work on w2k8 >> on x86. I haven't heard a specific report about it working on w2k8 x64, >> but >> since I have heard no report that it *doesn't* work, I think you

Re: [GENERAL] W2K8

I think it does. It certaily works on w2k3 x64 and it should work on w2k8 on x86. I haven't heard a specific report about it working on w2k8 x64, but since I have heard no report that it *doesn't* work, I think you can safely assume that it works :-) Thanks Magnus, Dave. On those assertive co

Re: [GENERAL] W2K8

On Thu, Oct 02, 2008 at 01:25:40PM +0100, Howard Cole wrote: > Hello all, > > Does postgres 8.3.* work on Windows w2k8 x64? I could not find any > reference to this on the website. I think it does. It certaily works on w2k3 x64 and it should work on w2k8 on x86. I haven't heard a specific report

Re: [GENERAL] W2K8

On Thu, Oct 2, 2008 at 1:25 PM, Howard Cole <[EMAIL PROTECTED]> wrote: > Hello all, > > Does postgres 8.3.* work on Windows w2k8 x64? I could not find any reference > to this on the website. It should work just fine. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql

[GENERAL] W2K8

Hello all, Does postgres 8.3.* work on Windows w2k8 x64? I could not find any reference to this on the website. Thanks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postmaster exit code 128 on Windows 2003 Server

Hi, I am running Postgresql 8.3.0 in a Windows 2003 Server (64bit). Database is being replicated with Slony-I. The main application was developed in php 5.2 and uses the php standard pgsql.dll connector to database. There are a few client-server processes that also uses the database. Everythin

Re: [GENERAL] Transactions within a function body

Richard Huxton wrote: >> After a discussion on comp.databases.postgresql I realized that this >> is actually a limitation. >> >> Consider the following: >> >> BEGIN >>UPDATE ... >>UPDATE ... >>UPDATE ... >> EXCEPTION >>WHEN integrity_constraint_violation THEN >> ... >> END;

Re: [GENERAL] Trigger disable for table

On Thursday 02 October 2008 06:26, Frank Durstewitz wrote: > Hi list. > > A fairly complex problem: > > - Table A has a before insert/update trigger, which look up table B and > use field C from table B. > - Table B has a after insert/update trigger, which update table A with > field C. > > The upd

Re: [GENERAL] Trigger disable for table

am Thu, dem 02.10.2008, um 12:26:20 +0200 mailte Frank Durstewitz folgendes: > My idea is to have it like > ... > IF NEW.published = TRUE THEN >ALTER TABLE a DISABLE TRIGGER mytrigger USER; >(do update here) >ALTER TABLE a ENABLE TRIGGER mytrigger USER; > ... > > Will a construct like

[GENERAL] Postgres major version support policy on Debian

Hi, first of all: thanks for packaging Postgres for Debian. I'm willing to help with that. Unfortunately we are stuck with several Postgres 8.2 installations from etch backports, which are no longer maintained by the backports, because only 8.2 got dropped from testing. I'm providing upgraded pa

Re: [GENERAL] Transactions within a function body

Hi. My humble opinion follows. One point here is that the decision for the ROLLBACK could possibly be different from errors. It could simply be based upon a generic expression, not just the conditions seen in "Appendix A" of the manual. An exception is something different from a transaction, de

[GENERAL] Trigger disable for table

Hi list. A fairly complex problem: - Table A has a before insert/update trigger, which look up table B and use field C from table B. - Table B has a after insert/update trigger, which update table A with field C. The update on table B triggers the trigger from table A, so the same thing is

Re: [GENERAL] Transactions within a function body

Well, if it is a limitation, and having it would lead to a "better product", why not making it a feature for the next still-open release? In my opinion that's more than a limitation, it's a missing feature. In your code you often need to create savepoints to delay the decision for the commitment.

Re: [GENERAL] Transactions within a function body

Albe Laurenz wrote: > After a discussion on comp.databases.postgresql I realized that this > is actually a limitation. > > Consider the following: > > BEGIN >UPDATE ... >UPDATE ... >UPDATE ... > EXCEPTION >WHEN integrity_constraint_violation THEN > ... > END; > > If the fir

Re: [GENERAL] Transactions within a function body

Alvaro Herrera wrote: > > > Is there a way to have (sub)transactions within a function body? > > > I'd like to execute some code (a transaction!) inside a function and later > > > decide whether that transaction is to be committed or not. > > > > You could issue a "SAVEPOINT name". If at the end y

Re: [GENERAL] That was easy

Gauthier, Dave wrote: > That was far and away the easiest DB upgrade I ever did. It was fast > and 100% clean. Shh! Some of us try to make a living supporting PG ;-) -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to you