headers
Ujwal S. Setlur | 1 Feb 2006 03:25
Picon
Favicon

Permissions on slony schema

Hi,

My application generally connects to the database
using a specific user. This user does not have view
permissions on the slony schema. However, my
application needs to look at some slony information to
make some decisions. I also do not want the
application to use the postgres super user account.

Is it OK to give the application user "read only"
privs on the slony schema? I can't imagine that would
be a problem, but I thought I would ask.

Thanks,

Ujwal

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com
Permalink | Reply | 2 comments |
headers
Michael Crozier | 1 Feb 2006 05:14

duplicate key error, thread safety problem?


Hi,

I encountered some duplicate key errors in my slony cluster today.  Clearly, 
an event/log was replicated more than once.

I believe that this may be due to "the Solaris threading issue", but I can't 
find enough clear information about this problem to determine whether I 
failed to avoid it in the build of Postgresql and Slony.

Detais:
 Solaris 9 sparc, 7.3.13, compiled with --thread-safety 
 Solaris 10 opteron, 8.0.6, compiled with --thread-safety
 All the slon's were running from the 8.0.6 instance/build.

I was able to manually remove the offending rows and get the slon's processing 
events again, but I'm worried about a few things:

1. How is my data?  Do I need to re-sync?
2. How can I prove that this problem is related to threading issue?
3. What IS the threading issue?  I can't find a good description of the
    problem and the solution.
4. If the problem still exists on the 8.0.X build, how do I correct it?

Any input and assistance would be appreciated.

-Michael
Permalink | Reply | 10 comments |
headers
Jan Wieck | 1 Feb 2006 07:16
Picon
Favicon

Re: Permissions on slony schema

On 1/31/2006 9:25 PM, Ujwal S. Setlur wrote:
> Hi,
> 
> My application generally connects to the database
> using a specific user. This user does not have view
> permissions on the slony schema. However, my
> application needs to look at some slony information to
> make some decisions. I also do not want the
> application to use the postgres super user account.
> 
> Is it OK to give the application user "read only"
> privs on the slony schema? I can't imagine that would
> be a problem, but I thought I would ask.

I don't see any problem with giving your application read permission on 
certain slony specific tables.

That said, slony does not change the ownership (origin) of sets by 
itself. Some mechanism you have setup yourself explicitly instructs 
slony to switchover or failover. Can't you incorporate telling your 
application where the current origin is into that process? I have done 
that in my test cases usually with replacing the pgpool config file 
during switchover or failover.

Jan

--

-- 
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
(Continue reading)

Permalink | Reply | 1 comment |
headers
Hannu Krosing | 1 Feb 2006 09:02
Picon

Re: duplicate key error, thread safety problem?

Ühel kenal päeval, T, 2006-01-31 kell 20:14, kirjutas Michael Crozier:
> Hi,
> 
> I encountered some duplicate key errors in my slony cluster today.  Clearly, 
> an event/log was replicated more than once.
> 
> I believe that this may be due to "the Solaris threading issue", but I can't 
> find enough clear information about this problem to determine whether I 
> failed to avoid it in the build of Postgresql and Slony.
> 
> Detais:
>  Solaris 9 sparc, 7.3.13, compiled with --thread-safety 
>  Solaris 10 opteron, 8.0.6, compiled with --thread-safety
>  All the slon's were running from the 8.0.6 instance/build.
>
> I was able to manually remove the offending rows and get the slon's processing 
> events again, but I'm worried about a few things:
> 
> 1. How is my data?  Do I need to re-sync?

Possible. Check your data :)

> 2. How can I prove that this problem is related to threading issue?

I don't think it is related to threading issue. 

If you have had more than 2G (_xxx_cluster_.sl_log_1.log_xid > 2G)
transactions executed during the replication, without reindexing
sl_log_1, then indexes on xxid starts misbehaving, resulting both in
duplicate key errors *and* some events not being replicated (i.e. data
(Continue reading)

Permalink | Reply | 1 comment |
headers
Ujwal S. Setlur | 1 Feb 2006 11:24
Picon
Favicon

Re: Permissions on slony schema


--- Jan Wieck <JanWieck@...> wrote:

> On 1/31/2006 9:25 PM, Ujwal S. Setlur wrote:
> > Hi,
> > 
> > My application generally connects to the database
> > using a specific user. This user does not have
> view
> > permissions on the slony schema. However, my
> > application needs to look at some slony
> information to
> > make some decisions. I also do not want the
> > application to use the postgres super user
> account.
> > 
> > Is it OK to give the application user "read only"
> > privs on the slony schema? I can't imagine that
> would
> > be a problem, but I thought I would ask.
> 
> I don't see any problem with giving your application
> read permission on 
> certain slony specific tables.
> 
> That said, slony does not change the ownership
> (origin) of sets by 
> itself. Some mechanism you have setup yourself
> explicitly instructs 
> slony to switchover or failover. Can't you
(Continue reading)

Permalink | Reply | 0 comments |
headers
Brad Nicholson | 1 Feb 2006 15:56

Re: duplicate key error, thread safety problem?

Michael Crozier wrote:

>Hi,
>
>I encountered some duplicate key errors in my slony cluster today.  Clearly, 
>an event/log was replicated more than once.
>
>I believe that this may be due to "the Solaris threading issue", but I can't 
>find enough clear information about this problem to determine whether I 
>failed to avoid it in the build of Postgresql and Slony.
>
>Detais:
> Solaris 9 sparc, 7.3.13, compiled with --thread-safety 
>  
>
Upgrade this version ASAP.  The following bug, fixed in 7.4.8, reared 
it's ugly head around here corrupting replica's with a duplicate key 
violation errors and making several DBA's very cranky...

http://www.postgresql.org/docs/7.4/static/release-7-4-8.html

"Repair ancient race condition that allowed a transaction to be seen as 
committed for some purposes (eg SELECT FOR UPDATE) slightly sooner than 
for other purposes This is an extremely serious bug since it could lead 
to apparent data inconsistencies being briefly visible to applications."

> Solaris 10 opteron, 8.0.6, compiled with --thread-safety
> All the slon's were running from the 8.0.6 instance/build.
>
>I was able to manually remove the offending rows and get the slon's processing
(Continue reading)

Permalink | Reply | 3 comments |
headers
Michael Crozier | 1 Feb 2006 20:14

Re: duplicate key error, thread safety problem?


> >Details:
> > Solaris 9 sparc, 7.3.13, compiled with --thread-safety
>
> Upgrade this version ASAP.  The following bug, fixed in 7.4.8, reared
> it's ugly head around here corrupting replica's with a duplicate key
> violation errors and making several DBA's very cranky...
>
> http://www.postgresql.org/docs/7.4/static/release-7-4-8.html

I believe that this issue was fixed in 7.3 around 7.3.10.  7.3.13 is the 
latest in the 7.3 series.  Unless I'm looking at the wrong bug/fix?

> >I was able to manually remove the offending rows and get the slon's
> > processing events again, but I'm worried about a few things:
> >
> >1. How is my data?  Do I need to re-sync?
>
> Where did you remove the offending rows from?  If you manually removed
> the row from sl_log_1, leaving the data on the master, but not the
> subscriber, then your replica is shot.

I removed the offending row in the slave table:
 begin;
     update pg_class set reltriggers=0 where relname = 'the_table';
     delete from the_table where primary_key = 'the_rows_pk_val';
     update pg_class set reltriggers=1 where relname = 'the_table';
 commit;

After this, slon successfully processing the event group.
(Continue reading)

Permalink | Reply | 2 comments |
headers
Michael Crozier | 1 Feb 2006 20:22

Re: duplicate key error, thread safety problem?


> > 1. How is my data?  Do I need to re-sync?
>
> Possible. Check your data :)

Only a few hundred million rows...  I better get started :-)

> > 2. How can I prove that this problem is related to threading issue?
>
> I don't think it is related to threading issue.
>
> If you have had more than 2G (_xxx_cluster_.sl_log_1.log_xid > 2G)
> transactions executed during the replication, without reindexing
> sl_log_1, then indexes on xxid starts misbehaving, resulting both in
> duplicate key errors *and* some events not being replicated (i.e. data
> loss).

This could be it.  The problem has occurred three times, all after adding a 
new table which took some time to COPY and create indexes, but there were no 
pending events when the COPY started and it caught up quickly after the 
addition/merge was complete.

I very much doubt we've done 2G transactions yet, as this is a new cluster 
with only master->slave replication.  I would estimated ~40 million 
transactions.

> If you want to know a little more about the issue look for my recent
> posts on this list.

I will read this and continue to investigate.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Brad Nicholson | 1 Feb 2006 20:37

Re: duplicate key error, thread safety problem?

Michael Crozier wrote:

>>>Details:
>>>Solaris 9 sparc, 7.3.13, compiled with --thread-safety
>>>      
>>>
>>Upgrade this version ASAP.  The following bug, fixed in 7.4.8, reared
>>it's ugly head around here corrupting replica's with a duplicate key
>>violation errors and making several DBA's very cranky...
>>
>>http://www.postgresql.org/docs/7.4/static/release-7-4-8.html
>>    
>>
>
>I believe that this issue was fixed in 7.3 around 7.3.10.  7.3.13 is the 
>latest in the 7.3 series.  Unless I'm looking at the wrong bug/fix?
>  
>

Checked the release notes, looks like you're right, that fix was backported.

>  
>
>>>I was able to manually remove the offending rows and get the slon's
>>>processing events again, but I'm worried about a few things:
>>>
>>>1. How is my data?  Do I need to re-sync?
>>>      
>>>
>>Where did you remove the offending rows from?  If you manually removed
(Continue reading)

Permalink | Reply | 1 comment |
headers
Michael Crozier | 1 Feb 2006 20:52

Re: duplicate key error, thread safety problem?


> Hmmm.... As much as I dislike rebuilding nodes, I certainly wouldn't
> trust one after messing with it manually like that.  I'd be rebuilding it.

I know what you mean.  Still, if I can explain the problem, I might be able to 
reason that the effects were such that all updates were applied in order, 
even if they were done twice in succession.  In that case I wouldn't have to 
start the week-long backup and vacuum-free rebuild....
Permalink | Reply | 0 comments |

Gmane