Re: [ADMIN] transaction error handling

2011-11-30 Thread Bèrto ëd Sèra
Hi On 30 November 2011 22:30, Kasia Tuszynska wrote: > With Postgres that is not the case, if the 50th sql statement in a long > transaction incurs an error, the whole transaction is rolled back for you > automatically, you the developer have no say in that unless you bracket > each statement wi

Re: [ADMIN] Read Only Role

2011-11-30 Thread M. D.
On 11/29/2011 11:52 PM, senthilnathan wrote: Is there any simple way to define read only roles. Basically that user should be able to login and be able to do *select* statements alone. It should be applied for all tables in all schemas in a database. -- View this message in context: http://pos

Re: [ADMIN] transaction error handling

2011-11-30 Thread Craig Ringer
On 11/30/2011 09:19 PM, Nicholson, Brad (Toronto, ON, CA) wrote: This functionality is something that Postgres can do today. We expose the ability to do this with explicit savepoints. The difference is that Oracle allows you to set it on a per transaction basis (I believe) and it will behave th

Re: [ADMIN] transaction error handling

2011-11-30 Thread Kasia Tuszynska
Hi, Oracle does not save the data, in mid transaction, in the sense of a commit. It keeps the association of the memory address related to the error free changes to the transaction and allows you – the developer to capture the error on that single incorrect change, and then continue with the sub

Re: [ADMIN] Deadlock on "select ... for update"?

2011-11-30 Thread Craig James
On 11/29/11 10:36 AM, Scott Marlowe wrote: On Tue, Nov 29, 2011 at 11:15 AM, Craig James wrote: Several times recently one of our databases has gotten stuck with the following situation: postgres=# select datname, procpid, usename, current_query from pg_stat_activity where current_query != ''

Re: [ADMIN] Database is in recovery mode.

2011-11-30 Thread Lukasz Brodziak
Hello, Thank You for the reply. I managed to delete the row the way below: 1. Using EMS Manager for PostgreSQL I selected all the rows except the corrupted one. 2. Extracted the data to INSERT script 3. SET zero_damaged_pages TO true; VACUUM FULL table; 4. DROP/CREATE 5. INSERT script without the

Re: [ADMIN] Database is in recovery mode.

2011-11-30 Thread Tom Lane
Lukasz Brodziak writes: > I have isolated the corrupted row of data and isolated column which > constains bad data. Unfortunately I can't do anything with the row. I have > an older backup of the db which contains correct row. Is there a way I can > restore only the given row or replace it in the

Re: [ADMIN] transaction error handling

2011-11-30 Thread Nicholson, Brad (Toronto, ON, CA)
> -Original Message- > From: pgsql-admin-ow...@postgresql.org [mailto:pgsql-admin- > ow...@postgresql.org] On Behalf Of Rob Richardson > Sent: Tuesday, November 29, 2011 1:35 PM > To: pgsql-admin@postgresql.org > Subject: Re: [ADMIN] transaction error handling > > Very naïve question here:

[ADMIN] Upgrade from 9.0.5 to 9.1.1 - Problems with citext indexes

2011-11-30 Thread Rudolf van der Leeden
Hi, upgrading Postgres 9.0.5 to 9.1.1 is done using: - pg_upgrade - CREATE EXTENSION citext FROM unpackaged (using the latest patch) This works fine even for indexes on citext columns. The problem comes with an index on LOWER(citext), e.g. the index idx_lower_login has been created on column '

Re: [ADMIN] transaction error handling

2011-11-30 Thread Bèrto ëd Sèra
> > On 29 November 2011 21:34, Rob Richardson > wrote: > >> If Oracle saves half of the data between the beginning and ending of the >> transaction, doesn't that defeat the purpose of the transaction? > > It sure enough kills Atomicity. I can see a use for this on importing data from external so

Re: [ADMIN] transaction error handling

2011-11-30 Thread Rural Hunter
I have the same confusion... 于 2011/11/30 2:34, Rob Richardson 写道: Very naïve question here: Why would you want to save the data from the first insert? I thought the purpose of a transaction was to make sure that all steps in the transaction executed, or none of them executed. If Oracle sav

[ADMIN] Read Only Role

2011-11-30 Thread senthilnathan
Is there any simple way to define read only roles. Basically that user should be able to login and be able to do *select* statements alone. It should be applied for all tables in all schemas in a database. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Read-Only-Role-tp5

Re: [ADMIN] transaction error handling

2011-11-30 Thread Rob Richardson
Very naïve question here: Why would you want to save the data from the first insert? I thought the purpose of a transaction was to make sure that all steps in the transaction executed, or none of them executed. If Oracle saves half of the data between the beginning and ending of the transact

Re: [ADMIN] streaming replication

2011-11-30 Thread senthilnathan
Just check the following thread for more details: http://postgresql.1045698.n5.nabble.com/Timeline-Conflict-td4657611.html > We have system(Cluster) with Master replicating to 2 stand by servers. > > i.e > > M |---> S1 > > |---> S2 > > If master failed, we do a trigger file at S1 to