Hrm still no luck.
I created a snapshot of the database, moved it onto another server so
i could play with it...
Ive tried using just prepare on the console using the query that fails:
prepare worker (bigint, bigint) as select w.worker_id, w.worker_id as
printerid, w.worker, w.alias, coalesce(w.alias, w.worker) as name,
w.active, w.last_active, w.last_deactive, round(extract(epoch from
now()) - extract(epoch from w.last_deactive)) as time_off from workers
as w left join worker_vis as wv on wv.worker_id = w.worker_id and
wv.defunct = 0 and ( ((wv.auth_id = $1) and (wv.auth_class =
data_class('user_id'))) or ((wv.auth_id = $2) and (wv.auth_class =
data_class('clinic_id')))) where wv.worker_vis_id is not null and
w.defunct = 0 order by coalesce(w.alias, w.worker);
update workers set last_active = now();
vacuum analyze workers;
update worker_vis set worker_id = worker_id;
vacuum analyze worker_vis;
update data_classes set defunct = 0 where defunct = 0;
vacuum analyze data_classes;
execute wrk;
That works as expected. I also tried each of those updates/vacuums separately.
So now I'm trying the the "bad" query in the simple perl script i
posted before, Ive tried just one instance, and multiple instances...
I guess next ill try running all the sql each web session generates
before it crashes... unless anyone has any other bright ideas for me
to try. Perhaps my simple updates are not enough for analyze to
invalidate the query plan? Should I be doing inserts/deletes or just
more updates?
Below are the table counts and the definition of data_classes. That
should be everything the query uses, except for the actually data.
Which I'm more than willing to provide (privately) if anyone thinks
they have a great idea on how to reproduce it.
SELECT count(1) from workers;
count
-------
716
SELECT count(1) from worker_vis;
count
-------
577
SELECT count(1) from data_classes;
count
-------
75
\d data_classes
Table "public.data_classes"
Column | Type |
Modifiers
--------------+--------------------------+-------------------------------------------------------------------
data_class | integer | not null default
nextval('data_classes_data_class_seq'::regclass)
data_id | character varying(80) |
data_table | text |
date_created | timestamp with time zone | default now()
defunct | smallint | default 0
description | character varying(80) |
Indexes:
"data_classes_pkey" PRIMARY KEY, btree (data_class)
"data_class_data_id_idx" UNIQUE, btree (data_id)
"data_class_data_idx" btree (data_id) WHERE defunct = 0
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
