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

[Gurjeet Singh]

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 up the last xid for a table some where?
>>
>
> Ok, so it took a lot of googling to figure this one out, but you can do it
> with something like so.
>
> SELECT *
> FROM strand_scores
> WHERE xmin::text = txid_current()::text
>
> It appears you can't convert a xid type to int or bigint, not sure why. I
> guess I should leave a comment on the manual page.


That's an interesting find, i'd say.

xmin::bigint doesn't work because that implicit CAST doesn't exist. If
needed, I'd use xmin::text::bigint; that should work.

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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

[Asko Oja]

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 parent transaction being commited or rolled back.
>
> A old example I posted of using pl/perl can be found here ->
> http://www.postgresqlforums.com/forums/viewtopic.php?f=4&t=647
>
> The key is opening a new session which using dblink or pl/perl dbi
> connection will do. This is not ideal or efficient.  It would be nice if you
> could just do autonomous transactions natively in pl/pgsql, but I find this
> method works for the cases where you need it(logging, huge batch processing
> tasks where it's not ideal to process everything in one transaction).
>
>
The same can be done with plProxy which is quite efficient but yes opening
connections is not. So if used extensively it would be clever to use
pgBouncer to reuse connections. Thanks for interesting idea.

>
> Bob
>
> "Hi all.
> 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.
> Thanks."
>
> On Thu, Oct 2, 2008 at 10:40 AM, Alvaro Herrera <
> [EMAIL PROTECTED]> wrote:
>
>> 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 and ROLLBACK TO in
>> functions, but ran into the problem that the SPI stack needs to be dealt
>> with appropriately and you can't do it if the user is able to modify it
>> arbitrarily by calling transaction-modifying commands.  That's when the
>> EXCEPTION idea came up.  We never went back and studied whether we could
>> have fixed the SPI limitation, but it's not trivial.
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/ 
>> The PostgreSQL Company - Command Prompt, Inc.
>>
>> --
>> 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] Getting rows in statement-level triggers

[Artacus]

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 figure this one out, but you can do 
it with something like so.


SELECT *
FROM strand_scores
WHERE xmin::text = txid_current()::text

It appears you can't convert a xid type to int or bigint, not sure why. 
I guess I should leave a comment on the manual page.




--
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] How do I save data and then raise an exception?

[Klint Gore]

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_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
the C++
> code recognizes the error.  How can I do both?

You need an autonomous transaction, which Postgres does not support
directly but you can implement using dblink or a plperl function that
connects back to the database.


I was also going to suggest that but did not, because autonomous 
transaction won't help here! The data has been INSERTed or UPDATEd in 
this transaction, and hence won't be visible to the autonomous 
transaction, because the main transaction hasn't committed yet.


Autonomous transactions in the oracle sense would do the job perfectly.
http://www.oracle-base.com/articles/misc/AutonomousTransactions.php

Faking that example with dblink isn't going to fly with PG - the select 
with 10 rows before the rollback is never going to see 10.


For Rob's need though, running his update thru dblink it should do the 
job.  If the data he's fixing with the update statement was in the same 
transaction, then the update wouldn't be needed at all and the whole 
thing could just rollback.  You have to assume that by the point where 
Rob's code fires, the bad data is already committed.  That update needs 
to commit to undo that previous transaction, but he still needs to get 
the 3rd party app to know that something went horribly wrong with its 
insert.


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  
Fax: 02 6773 3266

EMail: [EMAIL PROTECTED]


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

[GENERAL] Getting rows in statement-level triggers

[Artacus]

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 (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Gurjeet Singh]

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 time!';
> > END;
> >
> > I need the update to work, but I need to raise the exception so the C++
> > code recognizes the error.  How can I do both?
>
> You need an autonomous transaction, which Postgres does not support
> directly but you can implement using dblink or a plperl function that
> connects back to the database.


I was also going to suggest that but did not, because autonomous transaction
won't help here! The data has been INSERTed or UPDATEd in this transaction,
and hence won't be visible to the autonomous transaction, because the main
transaction hasn't committed yet.

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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

[Gurjeet Singh]

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 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 the C++
> >> code recognizes the error.  How can I do both?
> >
> > You need an autonomous transaction, which Postgres does not support
> > directly but you can implement using dblink or a plperl function that
> > connects back to the database.
> >
>
> what about RAISE NOTICE?


NOTICE wouldn't rollback any part of the transaction! OP needs mixed COMMIT
success in the same transaction.

-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

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

[Jaime Casanova]

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!';
>> END;
>>
>> I need the update to work, but I need to raise the exception so the C++
>> code recognizes the error.  How can I do both?
>
> You need an autonomous transaction, which Postgres does not support
> directly but you can implement using dblink or a plperl function that
> connects back to the database.
>

what about RAISE NOTICE?

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

-- 
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] How do I save data and then raise an exception?

[Alvaro Herrera]

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 the C++
> code recognizes the error.  How can I do both?

You need an autonomous transaction, which Postgres does not support
directly but you can implement using dblink or a plperl function that
connects back to the database.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

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

[Rob Richardson]

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 required check was not performed before
data was saved.  I cannot change this part of the C++ code, so I have to
perform the check in the database, and the insert query has to fail so
that the application will see that something bad happened.  However,
there's another query that gets performed before the one I have to
check.  If the check fails, the earlier query has to be undone.  The
only way I know to intentionally fail a query is to raise an exception.
However, raising an exception causes all earlier database changes to be
undone.  How can I avoid that?
 
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 the C++
code recognizes the error.  How can I do both?
 
Thanks very much!
 
RobR

Re: [GENERAL] Trigger disable for table

[Frank Durstewitz, Emporis GmbH]

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-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

[Jeff Amiel]

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  (cost=0.00..1.25 rows=1000 width=4)"

Explain select foo();
"Result  (cost=0.00..0.00 rows=1 width=0)"

They both return the same results..yet yield different plans...and
different speeds when using 'real' data.

Why come?



-- 
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] Transactions within a function body

[Gurjeet Singh]

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 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 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.
>>
>> That's a different thing, "autonomous transactions".
>>
>> --
>> Alvaro Herrera
>> http://www.CommandPrompt.com/ 
>> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>>
>
>


-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] tsearch 2 query

[Oleg Bartunov]

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 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=655) (actual
time=74806.968..74806.989 rows=20 loops=1)

Sort Key: stories."timestamp"

->  Nested Loop  (cost=0.00..90497.94 rows=19861 width=655)
(actual time=720.251..74798.672 rows=680 loops=1)

  ->  Function Scan on q  (cost=0.00..12.50 rows=1000
width=32) (actual time=0.013..0.017 rows=1 loops=1)

  ->  Index Scan using description_index on stories
(cost=0.00..90.14 rows=20 width=623) (actual time=700.633..63243.713
rows=680 loops=1)

Index Cond: (stories.vectors @@ "outer".q)

Total runtime: 74847.177 ms



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
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] tsearch2 query

[Oleg Bartunov]

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,headline(body,q),rank(vectors,q),timestamp
FROM stories,to_tsquery('$query') AS q WHERE vectors @@ q ORDER BY $sort
DESC OFFSET $offset LIMIT 20



This is common mistake, use subselect and you'll be happy.

Explanation:

headline() is very cost operation, so in your query you obtain headline()
for *ALL* results, while you need to do this only for 20 of them.
Feel the difference ?



Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

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

[Martin Pitt]

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
> only 8.2 got dropped from testing.

Indeed it was quite clear to me right from the beginning that Lenny
would ship with 8.3 only. I think from the POV of not supporting
several PostgreSQL versions in stable Debian releases there is no
disagreement. Etch is an exception because we needed 7.4 to get an
upgrade path from Sarge, but further Debian versions will only ever
support the latest PostgreSQL release.

Nevertheless I acknowledge the problem with the existing backport, of
course. I didn't request the 8.2 one, and personally I don't think it
is a wise idea to run a production server purely on a backport version
without being able to upgrade to 8.3 (or spending the necessary work
to upgrade to newer 8.2 versions, of course), but the world is as it
is, and people will do that.

> I'm providing upgraded packages for Postgres 8.2 on my own website [1].
> There are certainly other people who have run into the same issue, see
> for example [2] who dislikes using Postgres backports for exactly that
> reason.

Disliking backports for that reason is perfectly ok, IMHO. After all,
backports cannot make any support promises, thus not using them is
actually a *good* thing on critical infrastructure. :-)

> On the backports-users mailing list I've requested that Postgres 8.2
> gets re-added to etch-backports, with upgraded packages. So that
> existing installations can get bug- and security fixes for that Postgres
> versions. One argument for rejection [3] has been, that Postgres 8.2 is
> not in testing anymore and can thus not be backported. I'm arguing that
> Postgres 8.2 is a backport per se. Not from testing, but a backport of
> newer software to etch.

I'm personally ok with that argument, but I'm not the backports.org
maintainer. If they have a general policy that they don't *ever*
upload something manual to backports.org, I suppose changing that
policy just for PostgreSQL is hard to do.

Of course there is always the possibility of offering a private
archive. For example, I maintained 8.1 backports for Sarge on
people.debian.org for quite a while, until backports.org got them.

>  * Postgres major versions that once got included should continue to be
> supported and updated within the standard Debian infrastructure as long
> as supported by the Postgres project itself.

Not my favourite option, but if the postgresql maintenance team would
actually double in size (IOW, would not just be me), and
debian-{release,security}@ don't veto, it's ok with me.

I still maintain 8.2 for Ubuntu 7.04 and 7.10, which I will have to do
for the next 7 months still. But after that I can get that off my
plate, and just maintain 8.1 and 8.3.

>  * Postgres major versions dumped from testing, but once added to any
> backport should be maintained on backports even if it gets dumped from
> testing.

That would basically lift backports.org to be an officially supported
Debian archive, which it isn't, and shouldn't be.

>  * Never include Postgres major versions from testing in the backports,
> as those might get dumped from testing thus support cannot be guaranteed
> anymore. (Except perhaps when we can be very sure that this won't happen).

That's a viable option. When 8.3 was released, and Lenny's release
schedule got published (roughly at start of 2008), it was quite sure
that Lenny will ship with 8.3 only.

So, if the backports.org maintainers are ok with manual 8.2 uploads,
and you are willing to maintain them, that works for me. In that case
I'm happy to check your packages, and to discuss QA'ing procedures for
uploads.

If that violates the backports.org policy, I'd rather ask them to
remove the 8.2 backport altogether, since it just doesn't make sense
any more and just bitrots.

Thanks for starting the discussion!

Martin
-- 
Martin Pitt| http://www.piware.de
Ubuntu Developer (www.ubuntu.com)  | Debian Developer  (www.debian.org)


signature.asc
Description: Digital signature

Re: [GENERAL] Transactions within a function body

[Bob Henkel]

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 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.
>
> That's a different thing, "autonomous transactions".
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ 
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>

Re: [GENERAL] tsearch 2 query

[Tom Lane]

"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=655) (actual
> time=74806.968..74806.989 rows=20 loops=1)
>  Sort Key: stories."timestamp"
>  ->  Nested Loop  (cost=0.00..90497.94 rows=19861 width=655)
> (actual time=720.251..74798.672 rows=680 loops=1)
>->  Function Scan on q  (cost=0.00..12.50 rows=1000
> width=32) (actual time=0.013..0.017 rows=1 loops=1)
>->  Index Scan using description_index on stories
> (cost=0.00..90.14 rows=20 width=623) (actual time=700.633..63243.713
> rows=680 loops=1)
>  Index Cond: (stories.vectors @@ "outer".q)

>  Total runtime: 74847.177 ms

Huh.  The plan looks fine --- I had thought maybe the optimizer was
dropping the ball, but this seems to be more or less what you need.
The indexscan seems awfully slow though.

The only thought I have to offer is that you're apparently using quite
an old version of Postgres --- the 1000-row estimate for a scalar
function scan would only have happened in 8.0 or before.  Perhaps
updating to something newer would help.  I'm not sure if there are any
big performance improvements in GIST indexes per se, but in 8.2 or
8.3 you'd have the option to switch to a GIN index instead.  If this
table is read-mostly then that'd be a win.

regards, tom lane

-- 
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] Transactions within a function body

[Bob Henkel]

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 using pl/perl can be found here ->
http://www.postgresqlforums.com/forums/viewtopic.php?f=4&t=647

The key is opening a new session which using dblink or pl/perl dbi
connection will do. This is not ideal or efficient.  It would be nice if you
could just do autonomous transactions natively in pl/pgsql, but I find this
method works for the cases where you need it(logging, huge batch processing
tasks where it's not ideal to process everything in one transaction).

Bob

"Hi all.
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.
Thanks."

On Thu, Oct 2, 2008 at 10:40 AM, Alvaro Herrera
<[EMAIL PROTECTED]>wrote:

> 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 and ROLLBACK TO in
> functions, but ran into the problem that the SPI stack needs to be dealt
> with appropriately and you can't do it if the user is able to modify it
> arbitrarily by calling transaction-modifying commands.  That's when the
> EXCEPTION idea came up.  We never went back and studied whether we could
> have fixed the SPI limitation, but it's not trivial.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/ 
> The PostgreSQL Company - Command Prompt, Inc.
>
> --
> 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] Transactions within a function body

[Alvaro Herrera]

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 and ROLLBACK TO in
functions, but ran into the problem that the SPI stack needs to be dealt
with appropriately and you can't do it if the user is able to modify it
arbitrarily by calling transaction-modifying commands.  That's when the
EXCEPTION idea came up.  We never went back and studied whether we could
have fixed the SPI limitation, but it's not trivial.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] Transactions within a function body

[Alvaro Herrera]

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.

That's a different thing, "autonomous transactions".

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] tsearch 2 query

[Matthew Terenzio]

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)

   ->  Sort  (cost=105505.78..10.44 rows=19861 width=655) (actual
time=74806.968..74806.989 rows=20 loops=1)

 Sort Key: stories."timestamp"

 ->  Nested Loop  (cost=0.00..90497.94 rows=19861 width=655)
(actual time=720.251..74798.672 rows=680 loops=1)

   ->  Function Scan on q  (cost=0.00..12.50 rows=1000
width=32) (actual time=0.013..0.017 rows=1 loops=1)

   ->  Index Scan using description_index on stories
(cost=0.00..90.14 rows=20 width=623) (actual time=700.633..63243.713
rows=680 loops=1)

 Index Cond: (stories.vectors @@ "outer".q)

 Total runtime: 74847.177 ms

Re: [GENERAL] Transactions within a function body

[Gurjeet Singh]

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 bit more awkward.
> >
> > 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 with RAISE
> EXCEPTION you can cause savepoints to roll back at will.


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?

If there are no known limitations, I'd like to start work on it.

Best regards,
-- 
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com

EnterpriseDB  http://www.enterprisedb.com

Mail sent from my BlackLaptop device

Re: [GENERAL] Transactions within a function body

[Bob Henkel]

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 using pl/perl can be found here ->
http://www.postgresqlforums.com/forums/viewtopic.php?f=4&t=647

The key is opening a new session which using dblink or pl/perl dbi
connection will do. This is not ideal as it would be nice if you could just
do autonomous transactions, but I find this method works for the cases where
you need it.

Bob

"Hi all.

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.

Thanks."

Re: [GENERAL] Transactions within a function body

[Tom Lane]

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 with RAISE
> EXCEPTION you can cause savepoints to roll back at will.

Yeah, it's essentially the same functionality as savepoints, but
different syntax.

regards, tom lane

-- 
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] Transactions within a function body

[Reg Me Please]

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 awkward.
> >
> > 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 with RAISE
> EXCEPTION you can cause savepoints to roll back at will.

Now I understand. (Sorry, me dumb!)

Looks quirky, but I trust it's working. I'll give that a try.

-- 
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] Transactions within a function body

[Alvaro Herrera]

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; 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 with RAISE
EXCEPTION you can cause savepoints to roll back at will.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

-- 
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] tsearch 2 query

[Tom Lane]

"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 for it?

regards, tom lane

-- 
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] That was easy

[Igor Neyman]

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 problem (in PG) with large 
databases.

I'm not trying to start enother "war" compairing one DBMS to another (I have 
experience with both).
But, trashing Oracle that I'm seeing here lately, looks at least naïve - each 
db has it's advantages/disadvantages, but let's try to be at least somewhat 
objective.

Igor Neyman

-Original Message-
From: Gauthier, Dave [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 01, 2008 11:14 PM
To: pgsql-general@postgresql.org
Subject: That was easy

I just ported my whole DB instance, 4 db's inside with roles, triggers, stored 
procedures, etc... from v8.2.5 to v8.3.4. After the initdb, I mimiced the mods 
I had in the postgres.conf and pg_hba.conf files to v83.  Then I used 
pg_dumpall and psql to do the move. That was far and away the easiest DB 
upgrade I ever did.  It was fast and 100% clean.  Everything worked including 
remote attaches, all the perl/DBI scripts, even the odbc stuff on Windows 
worked... FIRST TRY !!!  I really don't miss the bad-ole days trying to do 
someting like this with Oracle.

 

Not many posts here are written to say "Great Job Guys".  This one is.  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] tsearch 2 query

[Matthew Terenzio]

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 stories,to_tsquery('$query') AS q WHERE vectors @@ q ORDER BY $sort
DESC OFFSET $offset LIMIT 20

Re: [GENERAL] W2K8

[Raul Carolus]

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 <[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 can
safely
assume that it works :-)


Thanks Magnus, Dave. On those assertive confirmations I shall get one
ordered! I'll assume you'll pay the bill to revert to W2k3 if it doesn't ;)


Probably not - but I am actually installing 2k8 x64 to do some
benchmarking right now. If you want to hold off for a day or so until
it's running and tested, I can let you know if it seems OK. I
seriously doubt there will be any issues though - we know it works on
Vista which is largely the same under the hood.



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

[GENERAL] tsearch2 query

[Matthew Terenzio]

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 stories,to_tsquery('$query') AS q WHERE vectors @@ q ORDER BY $sort
DESC OFFSET $offset LIMIT 20

Re: [GENERAL] Transactions within a function body

[Reg Me Please]

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 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.
> > A Pl/PgSQL function is just a bunch of code you want to move into the DB.
> > So the need for savepoints seems to me to be still there.
>
> 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; block to roll it 
back?

-- 
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] Transactions within a function body

[Alvaro Herrera]

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 you often need to create savepoints to delay the decision for 
> the 
> commitment.
> A Pl/PgSQL function is just a bunch of code you want to move into the DB.
> So the need for savepoints seems to me to be still there.

You can nest blocks arbitrarily, giving you the chance to selectively
rollback pieces of the function.  It's only a bit more awkward.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
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] Import German Number Format

[Jonah H. Harris]

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 anybody know which parameter I have
> to change?

Independent of locale-related settings, I don't believe PG will accept
a comma as input in this case.

-- 
Jonah H. Harris, Senior DBA
myYearbook.com

-- 
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] Import German Number Format

[A. Kretschmer]

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 import this data Postgres crashes, so I think I have to


PostgreSQL crashed? I disbelieve this, you got an error, right?


> change a parameter with SET? Does anybody know which parameter I have
> to change?

There isn't such a parameter. Change your data, change the ',' to '.'.
Or load the data into a temp. table as text and use build-in-functions to
convert this text to numeric and fill your destination table.

I would use sed or such tools.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

[GENERAL] Import German Number Format

[Tim Semmelhaack]

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 anybody know which parameter I have
to change?

Tim

--

-- 
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] W2K8

[Dave Page]

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 can
>> safely
>> assume that it works :-)
>>
>
> Thanks Magnus, Dave. On those assertive confirmations I shall get one
> ordered! I'll assume you'll pay the bill to revert to W2k3 if it doesn't ;)

Probably not - but I am actually installing 2k8 x64 to do some
benchmarking right now. If you want to hold off for a day or so until
it's running and tested, I can let you know if it seems OK. I
seriously doubt there will be any issues though - we know it works on
Vista which is largely the same under the hood.

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
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] W2K8

[Howard Cole]

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 confirmations I shall get one 
ordered! I'll assume you'll pay the bill to revert to W2k3 if it doesn't ;)


Howard.

--
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] W2K8

[Magnus Hagander]

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 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 :-)

//Magnus

-- 
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] W2K8

[Dave Page]

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 mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] W2K8

[Howard Cole]

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

[Roberto Mariano]

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.

Everything works fine, until in a while a server process fails with "exit code 
128". At this point the other postmaster finish and postgres reinitiates. But 
when reinitiating postgres finds shared buffers allocated by another process 
and the service can not recover.

Somebody had found this problem and can help me?

Thank you very much.

Roberto Mariano.




  Yahoo! Cocina
Recetas prácticas y comida saludable
http://ar.mujer.yahoo.com/cocina/

Re: [GENERAL] Transactions within a function body

[Albe Laurenz]

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;
>> 
>> If the first UPDATE succeeds but the second one bombs, there is no way
>> to undo the first update short of having the whole transaction cancelled.
> 
> No, I think you've got that backwards Albe. You can even nest exceptions.
> 
[...]
> 
> The BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".

You are right, and I'm happy to find myself wrong:

CREATE TABLE t1 (a integer PRIMARY KEY);

CREATE FUNCTION test_exception() RETURNS boolean LANGUAGE plpgsql AS
$$BEGIN
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (2);
   INSERT INTO t1 (a) VALUES (1);
   INSERT INTO t1 (a) VALUES (3);
   RETURN TRUE;
EXCEPTION
   WHEN integrity_constraint_violation THEN
  RAISE NOTICE 'Rollback to savepoint';
  RETURN FALSE;
END;$$;

BEGIN;

SELECT test_exception();
NOTICE:  Rollback to savepoint
 test_exception 

 f
(1 row)

COMMIT;

SELECT count(*) FROM t1;
 count 
---
 0
(1 row)

Great, thank you!

Yours,
Laurenz Albe

-- 
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] Trigger disable for table

[Terry Lee Tucker]

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 update on table B triggers the trigger from table A, so the same
> thing is done twice.
> Can one avoid to fire the trigger on table A, when updates are made to
> table B, because i know all fields already and can build the update sql
> for table A, so no need to call the trigger on table A?
>
> 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 this disable the trigger only inside the this
> function or is the trigger disabled outside (visiblility?) the function,
> too, which is unacceptable.
>
> (Hmm, sounds very confused, and so i am...)
>
> A helping hand on this topic is well accepted :-)
>
> Thanks, Frank

This should work but, if I remember correctly, it will lock table A. If that 
is OK in your environment, then go for it. It is not in ours. We have a table 
that we called override and when we want to override the firing of a certain 
trigger, we put code in that trigger that checks the override table for the 
existence of a record matching the trigger name and some other criteria. If 
we find it, we simply return from the trigger at that point. The trigger on 
table B would be responsible for inserting the record into override and then 
deleting the record after the update is done. We've build wrapper functions 
to make the inserts and deletes to override easy.

HTH...

-- 
Terry Lee Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 404-6987
[EMAIL PROTECTED]
www.turbocorp.com

-- 
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] Trigger disable for table

[A. Kretschmer]

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 this disable the trigger only inside the this 
> function or is the trigger disabled outside (visiblility?) the function, 
> too, which is unacceptable.

I think, you can do that, but an 'ALTER TABLE' produce a
AccessExclusiveLock on this table.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

[GENERAL] Postgres major version support policy on Debian

[Markus Wanner]

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 packages for Postgres 8.2 on my own website [1].
There are certainly other people who have run into the same issue, see
for example [2] who dislikes using Postgres backports for exactly that
reason.

Upgrading via pg_upgradecluster is definitely not an option due to
custom build extensions and because of the downtime involved.

On the backports-users mailing list I've requested that Postgres 8.2
gets re-added to etch-backports, with upgraded packages. So that
existing installations can get bug- and security fixes for that Postgres
versions. One argument for rejection [3] has been, that Postgres 8.2 is
not in testing anymore and can thus not be backported. I'm arguing that
Postgres 8.2 is a backport per se. Not from testing, but a backport of
newer software to etch.


Anyway, I'd like to reach an agreement on a decent policy about Postgres
major version support especially WRT the backports. I see these options:

 * Postgres major versions that once got included should continue to be
supported and updated within the standard Debian infrastructure as long
as supported by the Postgres project itself.

 * Postgres major versions dumped from testing, but once added to any
backport should be maintained on backports even if it gets dumped from
testing.

 * Never include Postgres major versions from testing in the backports,
as those might get dumped from testing thus support cannot be guaranteed
anymore. (Except perhaps when we can be very sure that this won't happen).


Looking at it that way, I'm favoring the first option. However, I'd be
fine with the second as well. The third would be a pity, but still
better than status quo (because backports currently makes false promises
about maintenance of backported Postgtres major versions, IMO).

As already mentioned, I'm offering help in maintaining these packages.
I'm somewhat experienced in Debian packaging, but not familiar with
uploading or maintaining "official" packages (keen to learn, though).

Regards

Markus Wanner


[1]: announcement of my Postgres 8.2 backports:
http://lists.backports.org/lurker-bpo/message/20080923.161529.60f37f97.en.html

[2]: newish complaint on the postgres performance mailing list:
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]

[3]: backport policy argument, see also rest of the thread
http://archives.postgresql.org/message-id/[EMAIL PROTECTED]

-- 
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] Transactions within a function body

[Reg Me Please]

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, despite the former is 
implemented with the latter.


On Thursday 02 October 2008 11:53:17 Richard Huxton wrote:
> 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 first UPDATE succeeds but the second one bombs, there is no way
> > to undo the first update short of having the whole transaction cancelled.
>
> No, I think you've got that backwards Albe. You can even nest exceptions.
>
> > If you need all three of these UPDATEs to either all succeed or fail,
> > but the whole transaction should continue, you cannot do that in
> > PL/pgSQL.
>
> Try the following script. By commenting out the second INSERT you can
> change whether you get one or no rows inserted into t1. The
> BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".
>
>
> BEGIN;
>
> CREATE TABLE t1 (a integer);
>
> CREATE OR REPLACE FUNCTION test_exception()
> RETURNS boolean AS $$
> DECLARE
> n integer;
> BEGIN
> INSERT INTO t1 (a) VALUES (1);
> -- INSERT INTO t1 (a) VALUES ('b');
> BEGIN
> INSERT INTO t1 (a) VALUES (2);
> INSERT INTO t1 (a) VALUES ('c');
> EXCEPTION
> WHEN OTHERS THEN
> SELECT INTO n count(*) FROM t1;
> RAISE NOTICE 'n2 = %', n;
> RETURN false;
> END;
> RETURN true;
> EXCEPTION
> WHEN OTHERS THEN
> SELECT INTO n count(*) FROM t1;
> RAISE NOTICE 'n1 = %', n;
> RETURN false;
> END;
> $$ LANGUAGE plpgsql;
>
> SELECT test_exception();
>
> SELECT count(*) FROM t1;
>
> ROLLBACK;



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

[GENERAL] Trigger disable for table

[Frank Durstewitz]

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 done twice.
Can one avoid to fire the trigger on table A, when updates are made to 
table B, because i know all fields already and can build the update sql 
for table A, so no need to call the trigger on table A?


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 this disable the trigger only inside the this 
function or is the trigger disabled outside (visiblility?) the function, 
too, which is unacceptable.


(Hmm, sounds very confused, and so i am...)

A helping hand on this topic is well accepted :-)

Thanks, Frank

--
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] Transactions within a function body

[Reg Me Please]

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.
A Pl/PgSQL function is just a bunch of code you want to move into the DB.
So the need for savepoints seems to me to be still there.

Useless to say I would vote for a "GO".

On Thursday 02 October 2008 11:01:37 Albe Laurenz wrote:
> 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 you don't want your
> > > changes to apply, you can issue a "ROLLBACK to name"
> >
> > Actually you can't use SAVEPOINT nor ROLLBACK TO within a function.  In
> > PL/pgSQL you can use EXCEPTION blocks (if you don't like the changes,
> > just do a RAISE EXCEPTION, and the exception block is run).
>
> 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 first UPDATE succeeds but the second one bombs, there is no way
> to undo the first update short of having the whole transaction cancelled.
>
> So while exceptions are implemented using savepoints, they give you only
> part of the functionality, namely to make a group of statements
> all-or-nothing within one transaction.
>
> If you need all three of these UPDATEs to either all succeed or fail,
> but the whole transaction should continue, you cannot do that in PL/pgSQL.
>
> Is there a chance to get savepoint support in PL/pgSQL at some point?
> Does it make sense to raise this on -hackers?
>
> Yours,
> Laurenz Albe



-- 
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] Transactions within a function body

[Richard Huxton]

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 first UPDATE succeeds but the second one bombs, there is no way
> to undo the first update short of having the whole transaction cancelled.

No, I think you've got that backwards Albe. You can even nest exceptions.

> If you need all three of these UPDATEs to either all succeed or fail,
> but the whole transaction should continue, you cannot do that in PL/pgSQL.

Try the following script. By commenting out the second INSERT you can
change whether you get one or no rows inserted into t1. The
BEGIN...EXCEPTION...END block has a savepoint set at the "BEGIN".


BEGIN;

CREATE TABLE t1 (a integer);

CREATE OR REPLACE FUNCTION test_exception()
RETURNS boolean AS $$
DECLARE
n integer;
BEGIN
INSERT INTO t1 (a) VALUES (1);
-- INSERT INTO t1 (a) VALUES ('b');
BEGIN
INSERT INTO t1 (a) VALUES (2);
INSERT INTO t1 (a) VALUES ('c');
EXCEPTION
WHEN OTHERS THEN
SELECT INTO n count(*) FROM t1;
RAISE NOTICE 'n2 = %', n;
RETURN false;
END;
RETURN true;
EXCEPTION
WHEN OTHERS THEN
SELECT INTO n count(*) FROM t1;
RAISE NOTICE 'n1 = %', n;
RETURN false;
END;
$$ LANGUAGE plpgsql;

SELECT test_exception();

SELECT count(*) FROM t1;

ROLLBACK;

-- 
  Richard Huxton
  Archonet Ltd

-- 
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] Transactions within a function body

[Albe Laurenz]

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 you don't want your
> > changes to apply, you can issue a "ROLLBACK to name"
> 
> Actually you can't use SAVEPOINT nor ROLLBACK TO within a function.  In
> PL/pgSQL you can use EXCEPTION blocks (if you don't like the changes,
> just do a RAISE EXCEPTION, and the exception block is run).

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 first UPDATE succeeds but the second one bombs, there is no way
to undo the first update short of having the whole transaction cancelled.

So while exceptions are implemented using savepoints, they give you only
part of the functionality, namely to make a group of statements
all-or-nothing within one transaction.

If you need all three of these UPDATEs to either all succeed or fail,
but the whole transaction should continue, you cannot do that in PL/pgSQL.

Is there a chance to get savepoint support in PL/pgSQL at some point?
Does it make sense to raise this on -hackers?

Yours,
Laurenz Albe

-- 
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] That was easy

[Richard Huxton]

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 your subscription:
http://www.postgresql.org/mailpref/pgsql-general