residual image publishing problems eating up postgres processing time

Hello-

I'm trying to understand why I have an enormous number of
image media  
files that are under the "Active Media" link in
the bricolage sidebar  
- and when I click on this link- the images are identified
as being  
on the 'Publish' Desk.

However  when I click the "publish" link from
this location, I am  
taken to the publish desk there is nothing there.

Now I thought this was a residual problem left over from
this problem:
http://
bugs.bricolage.cc/show_bug.cgi?id=1227


which I patched my bricolage 1.10.2 :
  http://marc.theaimsgroup.com/?l=bricolage
-commits&m=115509946401749

The patch changes /lib/Bric/Dist/Resource.pm

          # database with the index-defeating query
              # "WHERE outut_channel__id = NULL"
              return ($href ? {} : []) unless defined $v;
-            $tables .= ', job__resource jrr,
job__server_type jst, '
-                     . 'server_type__output_channel
stoc';
-            $wheres .= ' AND r.id = jrr.resource__id '
-                     . 'AND jrr.job__id = jst.job__id '
-                     . 'AND jst.server_type__id =  
stoc.server_type__id AND '
-                     . any_where $v,
'stoc.output_channel__id = ?',  
\params;
+            $wheres .= ' AND r.id IN (
+                    SELECT jrr.resource__id
+                    FROM   job__resource jrr,
+                           job__server_type jst,
+                           server_type__output_channel stoc
+                    WHERE  jrr.job__id = jst.job__id
+                           AND jst.server_type__id =  
stoc.server_type__id
+                           AND jrr.resource__id = r.id AND
'
+                . any_where($v, 'stoc.output_channel__id =
?',  
\params)
+                . ')';
          } elsif ($k eq 'is_dir') {
              # Check for directories or not.
              $wheres .= " AND r.$k = ?";


--- Sadly I am having even more problems publishing images
now.

At first I thought the problem was caused by me trying to
publish the  
old image media that were in the "Active Media"
area, however now I'm  
unable to publish all my new images.

When I query my postgres database (I'm using phpPGAdmin to
manage  
these things) to find out what processes are running:

SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
        pg_stat_get_backend_activity(s.backendid) AS
current_query
     FROM (SELECT pg_stat_get_backend_idset() AS backendid)
AS s;

I get this kind of response that this is the process that's
taking over:

procpid: 21142
	

current_query        SELECT r.id, r.parent_id, r.path,
r.uri, r.size,  
r.mod_time, r.is_dir, t.name
         FROM   media_type t, resource r LEFT OUTER JOIN  
job__resource jrno ON (r.id = jrno.resource__id AND
jrno.job__id =  
$1), media__resource sr
         WHERE  r.media_type__id = t.id AND
jrno.resource__id IS NULL  
AND r.id = sr.resource__id AND sr.media__id = $2 AND r.id IN
(
                     SELECT jrr.resource__id
                     FROM   job__resource jrr,
                            job__server_type jst,
                            server_type__output_channel stoc
                     WHERE  jrr.job__id = jst.job__id
                            AND jst.server_type__id =  
stoc.server_type__id
                            AND jrr.resource__id = r.id AND 

stoc.output_channel__id = $3)
         ORDER BY path


Unlike mysql there doesn't seem to be a way- that I know
of- to Kill  
a process using SQL. So we have to restart the whole
postgres server.

But we are knackered at this moment by this problem. Should
there  
have be other patches i should have applied to resolve this
issue  
perhaps?

thank you
Dawn

On Sep 14, 2006, at 13:41, Dawn Buie wrote:

> I'm trying to understand why I have an enormous number
of image  
> media files that are under the "Active
Media" link in the bricolage  
> sidebar - and when I click on this link- the images are
identified  
> as being on the 'Publish' Desk.
>
> However  when I click the "publish" link
from this location, I am  
> taken to the publish desk there is nothing there.

Grr. Things are disjointed, because unfortunately, Bricolage
has two  
methods for identifying what desk an asset is on. And
clearly, the  
Active link exploits one and the desk view exploits the
other.  
Looking at the code, the Active page uses the workflow__id
parameter  
to list, while the desk view looks for the assets that are
members of  
the group associated with the desk. So what you want to do
is set the  
workflow__id and desk__id columns in the media table to 0
for those  
media documents. This query should do the trick:

   UPDATE media
      SET workflow__id = 0, desk__id = 0
    WHERE (workflow__id <> 0 OR desk__id <> 0 )
      AND id NOT IN (
        SELECT object_id
          FROM media_member, member
         WHERE member.id = media_member.member__id
           AND member.grp__id = (SELECT asset_grp FROM desk
WHERE  
name = 'Publish')
    );

> Now I thought this was a residual problem left over
from this problem:
> http://
bugs.bricolage.cc/show_bug.cgi?id=1227

No, that wasn't related to what was on desks, only to what
was in the  
job and resource tables (that is, media files used for
distribution).

> --- Sadly I am having even more problems publishing
images now.

Yow. :-(

> At first I thought the problem was caused by me trying
to publish  
> the old image media that were in the "Active
Media" area, however  
> now I'm unable to publish all my new images.
>
> When I query my postgres database (I'm using
phpPGAdmin to manage  
> these things) to find out what processes are running:
>
> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
>        pg_stat_get_backend_activity(s.backendid) AS
current_query
>     FROM (SELECT pg_stat_get_backend_idset() AS
backendid) AS s;
>
> I get this kind of response that this is the process
that's taking  
> over:
>
> procpid: 21142
> 	
>
> current_query        SELECT r.id, r.parent_id, r.path,
r.uri,  
> r.size, r.mod_time, r.is_dir, t.name
>         FROM   media_type t, resource r LEFT OUTER JOIN
 
> job__resource jrno ON (r.id = jrno.resource__id AND
jrno.job__id =  
> $1), media__resource sr
>         WHERE  r.media_type__id = t.id AND
jrno.resource__id IS  
> NULL AND r.id = sr.resource__id AND sr.media__id = $2
AND r.id IN (
>                     SELECT jrr.resource__id
>                     FROM   job__resource jrr,
>                            job__server_type jst,
>                            server_type__output_channel
stoc
>                     WHERE  jrr.job__id = jst.job__id
>                            AND jst.server_type__id =  
> stoc.server_type__id
>                            AND jrr.resource__id = r.id
AND  
> stoc.output_channel__id = $3)
>         ORDER BY path
>

Hrm. Can you tell what Bricolage (or bric_queued) process is
causing  
that? It'd be useful to get a stack trace.

> Unlike mysql there doesn't seem to be a way- that I
know of- to  
> Kill a process using SQL. So we have to restart the
whole postgres  
> server.

Not in SQL, but according to this documentation:

   http://developer.postgresql.org/pgdocs/postgres
/app-postgres.html

"To cancel a running query, send the SIGINT signal to
the process  
running that command

So that should be `kill -2 21142`

> But we are knackered at this moment by this problem.
Should there  
> have be other patches i should have applied to resolve
this issue  
> perhaps?

Not that I know of. Your runaway process mystifies me.

Best,

David

On Sep 14, 2006, at 13:41, Dawn Buie wrote:

> I'm trying to understand why I have an enormous number
of image  
> media files that are under the "Active
Media" link in the bricolage  
> sidebar - and when I click on this link- the images are
identified  
> as being on the 'Publish' Desk.
>
> However  when I click the "publish" link
from this location, I am  
> taken to the publish desk there is nothing there.

Grr. Things are disjointed, because unfortunately, Bricolage
has two  
methods for identifying what desk an asset is on. And
clearly, the  
Active link exploits one and the desk view exploits the
other.  
Looking at the code, the Active page uses the workflow__id
parameter  
to list, while the desk view looks for the assets that are
members of  
the group associated with the desk. So what you want to do
is set the  
workflow__id and desk__id columns in the media table to 0
for those  
media documents. This query should do the trick:

   UPDATE media
      SET workflow__id = 0, desk__id = 0
    WHERE (workflow__id <> 0 OR desk__id <> 0 )
      AND id NOT IN (
        SELECT object_id
          FROM media_member, member
         WHERE member.id = media_member.member__id
           AND member.grp__id = (SELECT asset_grp FROM desk
WHERE  
name = 'Publish')
    );

> Now I thought this was a residual problem left over
from this problem:
> http://
bugs.bricolage.cc/show_bug.cgi?id=1227

No, that wasn't related to what was on desks, only to what
was in the  
job and resource tables (that is, media files used for
distribution).

> --- Sadly I am having even more problems publishing
images now.

Yow. :-(

> At first I thought the problem was caused by me trying
to publish  
> the old image media that were in the "Active
Media" area, however  
> now I'm unable to publish all my new images.
>
> When I query my postgres database (I'm using
phpPGAdmin to manage  
> these things) to find out what processes are running:
>
> SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
>        pg_stat_get_backend_activity(s.backendid) AS
current_query
>     FROM (SELECT pg_stat_get_backend_idset() AS
backendid) AS s;
>
> I get this kind of response that this is the process
that's taking  
> over:
>
> procpid: 21142
> 	
>
> current_query        SELECT r.id, r.parent_id, r.path,
r.uri,  
> r.size, r.mod_time, r.is_dir, t.name
>         FROM   media_type t, resource r LEFT OUTER JOIN
 
> job__resource jrno ON (r.id = jrno.resource__id AND
jrno.job__id =  
> $1), media__resource sr
>         WHERE  r.media_type__id = t.id AND
jrno.resource__id IS  
> NULL AND r.id = sr.resource__id AND sr.media__id = $2
AND r.id IN (
>                     SELECT jrr.resource__id
>                     FROM   job__resource jrr,
>                            job__server_type jst,
>                            server_type__output_channel
stoc
>                     WHERE  jrr.job__id = jst.job__id
>                            AND jst.server_type__id =  
> stoc.server_type__id
>                            AND jrr.resource__id = r.id
AND  
> stoc.output_channel__id = $3)
>         ORDER BY path
>

Hrm. Can you tell what Bricolage (or bric_queued) process is
causing  
that? It'd be useful to get a stack trace.

> Unlike mysql there doesn't seem to be a way- that I
know of- to  
> Kill a process using SQL. So we have to restart the
whole postgres  
> server.

Not in SQL, but according to this documentation:

   http://developer.postgresql.org/pgdocs/postgres
/app-postgres.html

"To cancel a running query, send the SIGINT signal to
the process  
running that command

So that should be `kill -2 21142`

> But we are knackered at this moment by this problem.
Should there  
> have be other patches i should have applied to resolve
this issue  
> perhaps?

Not that I know of. Your runaway process mystifies me.

Best,

David

On 15-Sep-06, at 10:50 AM, David E. Wheeler wrote:

> On Sep 14, 2006, at 13:41, Dawn Buie wrote:
>
>> I'm trying to understand why I have an enormous
number of image  
>> media files that are under the "Active
Media" link in the  
>> bricolage sidebar - and when I click on this link-
the images are  
>> identified as being on the 'Publish' Desk.
>>
>> However  when I click the "publish"
link from this location, I am  
>> taken to the publish desk there is nothing there.
>
> Grr. Things are disjointed, because unfortunately,
Bricolage has  
> two methods for identifying what desk an asset is on.
And clearly,  
> the Active link exploits one and the desk view exploits
the other.  
> Looking at the code, the Active page uses the
workflow__id  
> parameter to list, while the desk view looks for the
assets that  
> are members of the group associated with the desk. So
what you want  
> to do is set the workflow__id and desk__id columns in
the media  
> table to 0 for those media documents. This query should
do the trick:
>
>   UPDATE media
>      SET workflow__id = 0, desk__id = 0
>    WHERE (workflow__id <> 0 OR desk__id <>
0 )
>      AND id NOT IN (
>        SELECT object_id
>          FROM media_member, member
>         WHERE member.id = media_member.member__id
>           AND member.grp__id = (SELECT asset_grp FROM
desk WHERE  
> name = 'Publish')
>    );


Oh my GOD!!! that solved it.!! Oh relief is mine.

Have other people had that problem? I wonder if it happened
during  
one of the upgrade processes?

>
>> Now I thought this was a residual problem left over
from this  
>> problem:
>> http://
bugs.bricolage.cc/show_bug.cgi?id=1227
>
> No, that wasn't related to what was on desks, only to
what was in  
> the job and resource tables (that is, media files used
for  
> distribution).
>
>> --- Sadly I am having even more problems publishing
images now.
>
> Yow. :-(


I was just able to publish some images very quickly. Perhaps
the  
problem is solved now.

thank you so much-

Dawn

On 15-Sep-06, at 10:50 AM, David E. Wheeler wrote:

> On Sep 14, 2006, at 13:41, Dawn Buie wrote:
>
>> I'm trying to understand why I have an enormous
number of image  
>> media files that are under the "Active
Media" link in the  
>> bricolage sidebar - and when I click on this link-
the images are  
>> identified as being on the 'Publish' Desk.
>>
>> However  when I click the "publish"
link from this location, I am  
>> taken to the publish desk there is nothing there.
>
> Grr. Things are disjointed, because unfortunately,
Bricolage has  
> two methods for identifying what desk an asset is on.
And clearly,  
> the Active link exploits one and the desk view exploits
the other.  
> Looking at the code, the Active page uses the
workflow__id  
> parameter to list, while the desk view looks for the
assets that  
> are members of the group associated with the desk. So
what you want  
> to do is set the workflow__id and desk__id columns in
the media  
> table to 0 for those media documents. This query should
do the trick:
>
>   UPDATE media
>      SET workflow__id = 0, desk__id = 0
>    WHERE (workflow__id <> 0 OR desk__id <>
0 )
>      AND id NOT IN (
>        SELECT object_id
>          FROM media_member, member
>         WHERE member.id = media_member.member__id
>           AND member.grp__id = (SELECT asset_grp FROM
desk WHERE  
> name = 'Publish')
>    );


Oh my GOD!!! that solved it.!! Oh relief is mine.

Have other people had that problem? I wonder if it happened
during  
one of the upgrade processes?

>
>> Now I thought this was a residual problem left over
from this  
>> problem:
>> http://
bugs.bricolage.cc/show_bug.cgi?id=1227
>
> No, that wasn't related to what was on desks, only to
what was in  
> the job and resource tables (that is, media files used
for  
> distribution).
>
>> --- Sadly I am having even more problems publishing
images now.
>
> Yow. :-(


I was just able to publish some images very quickly. Perhaps
the  
problem is solved now.

thank you so much-

Dawn

On Sep 15, 2006, at 12:06, Dawn Buie wrote:

> Oh my GOD!!! that solved it.!! Oh relief is mine.

Heh. 

> Have other people had that problem? I wonder if it
happened during  
> one of the upgrade processes?

Things do periodically get screwed up, so that an asset is
still in a  
group but the workflow and desk id are not set anymore. No
idea why.  
Eventually we should eliminate the double storage of data so
that  
this problem goes away.

> I was just able to publish some images very quickly.
Perhaps the  
> problem is solved now.

That would be curious. If so, I wonder why the group
association  
stuff would have made a difference?

> thank you so much-

You're quite welcome.

Best,

David

>
>> Have other people had that problem? I wonder if it
happened during  
>> one of the upgrade processes?
>
> Things do periodically get screwed up, so that an asset
is still in  
> a group but the workflow and desk id are not set
anymore. No idea  
> why. Eventually we should eliminate the double storage
of data so  
> that this problem goes away.
>
>> I was just able to publish some images very
quickly. Perhaps the  
>> problem is solved now.
>
> That would be curious. If so, I wonder why the group
association  
> stuff would have made a difference?

Well when I tried publishing some of those 'double desked'
images,  
nothing would happen. I would restart bricolage eventually,
but it  
turned out the SQL query was still going on, and I had to
restart the  
postgres database in fact to stop it.

This was very annoying- as i was trying to figure out what
was  
causing the problem.

Then as i say- it seemed to be happening when i was publish
some- but  
not all- of new images i added to the system.

Anyway- my approach wasn't too scientific. But it is handy
to know  
how to put all the images back on the correct desk using
SQL.

Dawn

>

On Sep 15, 2006, at 13:02, Dawn Buie wrote:

> Well when I tried publishing some of those 'double
desked' images,  
> nothing would happen. I would restart bricolage
eventually, but it  
> turned out the SQL query was still going on, and I had
to restart  
> the postgres database in fact to stop it.

Very odd.

> This was very annoying- as i was trying to figure out
what was  
> causing the problem.
>
> Then as i say- it seemed to be happening when i was
publish some-  
> but not all- of new images i added to the system.

Very very odd.

> Anyway- my approach wasn't too scientific. But it is
handy to know  
> how to put all the images back on the correct desk
using SQL.

Actually, that query just removes the stories from any desk
or  
workflow, if they're not in a desk asset group.

Best,

David