On 12-04-04 11:40 AM, David TECHER wrote:
> Steve
>
> Using a query taken from
>

Also, I ONLY see sequences listed in the below output.  I don't see any 
examples of tables.  Are you only seeing this for sequences or did you 
just not paste enough lines to cover tables.




> 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, &nbs p; substr(pg_stat_activity.current_query,1,30),
> pg_stat_activity.query_start, & nbsp;
> age(now(),pg_stat_activity.query_start) as "age",
> pg_stat_activity.procpid from pg_stat_activity,pg_locks left &n bsp;
> 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 <mailto:Slony1-hackers at lists.slony.info>
>  > http://lists.slony.info/mailman/listinfo/slony1-hackers
>
>
>