Steve

Using a query taken from 

http://wiki.postgresql.org/wiki/Lock_Monitoring


I've got (the 10 first lines)


biopacs_production=#  select                                                                                                                                                                                                                      pg_class.relname,pg_locks.transactionid, pg_locks.mode,                                                                                                                                                                                      substr(pg_stat_activity.current_query,1,30),
 pg_stat_activity.query_start,                                                                                                                                                                   age(now(),pg_stat_activity.query_start) as "age", pg_stat_activity.procpid                                                                                                                                                                 from pg_stat_activity,pg_locks
 left                                                                                                                                                                                                            outer join pg_class on (pg_locks.relation = pg_class.oid)                                                                                                                                                                                  where pg_locks.pid=pg_stat_activity.procpid order by query_start limit 10;
                         relname                         | transactionid |      mode       |             substr             |           query_start            |     age     | procpid
---------------------------------------------------------+---------------+-----------------+--------------------------------+----------------------------------+-------------+---------
 AuditTimePoint_ID_seq                                   |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 AuditDegradedImage_ID_seq                               |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 Screenshot_ScreenshotID_seq                             |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 QuestionRule_QuestionRuleID_seq                         |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 AuditAllocationStatus_ID_seq                            |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 AuditQCCheckList_ID_seq                                 |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 QueueItemType_QueueItemTypeID_seq                       |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 AuditInstitutionType_ID_seq                             |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 AuditExportPublishTables_AuditExportPublishTablesID_seq |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
 AuditCheckParameterRule_ExpectedProtocolSequence_ID_seq |               | AccessShareLock | select "_biocluster".createEve | 04-APR-12 11:38:11.809813 -04:00 | @ 0.97 secs |   25021
(10 rows)


Please let me understand



________________________________
 De : Steve Singer <ssinger at ca.afilias.info>
À : David TECHER <davidtecher at yahoo.fr> 
Cc : Slony Hackers <slony1-hackers at lists.slony.info> 
Envoyé le : Mercredi 4 avril 2012 17h25
Objet : Re: [Slony1-hackers] CreateEvent and Lokcs on replicated objects
 
On 12-04-04 06:25 AM, David TECHER wrote:
> Hi


I can't think of an obvious reason why a generic SYNC event would get a 
lock on all of your tables. I don't think the SYNC event looks at 
replicated tables .

What transactions are holding the locks?  You should be able to see this 
in your pg_locks output (joining back to pg_class to get the tablenames 
from the oids)






>
> I am using Slony 1.2.22
>
> I've noticed that when a CreateEvent occurs
>
> biopacs_production=# select * from pg_stat_activity where procpid=25021;
> datid | datname | procpid | usesysid | usename | current_query | waiting
> | query_start | backend_start | client_addr | client_port
> -------+--------------------+---------+----------+--------------+----------------------------------------------------------------+---------+----------------------------------+----------------------------------+-------------+-------------
> 16387 | biopacs_production | 25021 | 10 | enterprisedb | select
> "_biocluster".createEvent('_biocluster', 'SYNC', NULL); | f | 04-APR-12
> 06:22:24.804654 -04:00 | 01-APR-12 06:53:33.363283 -04:00 | 127.0.1.1 |
> 42050
> (1 row)
>
>
> Then by querying pg_locks I've noticed that there is a 'AccessSharelock'
> on all replicated objects (tables and sequences).
>
> It is normal?
>
> Since our database used intensive CPU (high load) I asked myself if it
> could explained my issue.
>
> Thanks for letting me know.
>
> Kind regards.
>
> David.
>
>
>
> _______________________________________________
> Slony1-hackers mailing list
> Slony1-hackers at lists.slony.info
> http://lists.slony.info/mailman/listinfo/slony1-hackers
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-hackers/attachments/20120404/f2c3aeb3/attachment-0001.htm