retriving views name
hi,
I must retrieve views names in a very short time. what can I use.
for the moment, I use:
SELECT c.relname FROM pg_class c, pg_rewrite r WHERE (((c.relname !~
'^pg_'::text) AND (r.ev_class = c.oid)) AND (r.ev_type = '1'::"char"));
is there a more performant way to do this? (I have more than 50.000
views in my database (those views are generated on the fly to optimize a
search engine and keep results in memory)). I am running a sort of
"garbage collector" via cron/psql -c every minutes to destroy views
older than 10 minutes and I must do this very rapidly!
I there a way to know the creation date of a postgresql object (for the
moment, I concatenate hours/minutes in the view name and I use a
EXTRACT(MINUTE FROM (current_time - TIME(substr(mviews.relname,
length(mviews.relname) - 3, 2) || ':' || substr(mviews.relname,
length(mviews.relname) - 1, 2))) in a FOR LOOP with a EXECUTE 'DROP
VIEW' || mviews.relname to remove each view corresponding to my criterium)?
here is my plpgsql function:
---------------------
DECLARE
var_duree ALIAS FOR $1;
mviews RECORD;
BEGIN
FOR mviews IN SELECT relname FROM view_get_vstviews LOOP
IF (
EXTRACT(
MINUTE FROM (
current_time -
TIME(substr(mviews.relname, length(mviews.relname) - 3, 2) || ':' ||
substr(mviews.relname, length(mviews.relname) - 1, 2))
)
) >= var_duree
) THEN
EXECUTE 'DROP VIEW "' || mviews.relname ||'"';
END IF;
END LOOP;
RETURN 0;
END;
---------------------
if I do the same function in C language, will it be faster?
thanks
--
Emmanuel SARACCO
Email: esaracco@noos.fr
Emmanuel SARACCO <esaracco@noos.fr> writes:
I must retrieve views names in a very short time. what can I use.
for the moment, I use:
SELECT c.relname FROM pg_class c, pg_rewrite r WHERE (((c.relname !~
'^pg_'::text) AND (r.ev_class = c.oid)) AND (r.ev_type = '1'::"char"));
If you're using a reasonably recent PG release, there's no need to join
against pg_rewrite. Just look for pg_class entries with relkind = 'v'.
is there a more performant way to do this? (I have more than 50.000
views in my database (those views are generated on the fly to optimize a
search engine and keep results in memory)). I am running a sort of
"garbage collector" via cron/psql -c every minutes to destroy views
older than 10 minutes and I must do this very rapidly!
One must wonder why you create views at all, if you don't intend them
to stick around for long. Why not just write out the SELECTs in full?
(If you think there's some performance benefit to using a view, you are
quite mistaken.)
regards, tom lane
hi tom,
thanks for your answer; it is much more efficient your way.
I have two levels of views:
1/ generated on the fly and persistants
2/ generated on the fly just for a user session (I need to destroy these
views, but not the others).
do you know a way to access the creation time of a postgresql object?
bye
Tom Lane wrote:
Emmanuel SARACCO <esaracco@noos.fr> writes:
I must retrieve views names in a very short time. what can I use.
for the moment, I use:SELECT c.relname FROM pg_class c, pg_rewrite r WHERE (((c.relname !~
'^pg_'::text) AND (r.ev_class = c.oid)) AND (r.ev_type = '1'::"char"));If you're using a reasonably recent PG release, there's no need to join
against pg_rewrite. Just look for pg_class entries with relkind = 'v'.is there a more performant way to do this? (I have more than 50.000
views in my database (those views are generated on the fly to optimize a
search engine and keep results in memory)). I am running a sort of
"garbage collector" via cron/psql -c every minutes to destroy views
older than 10 minutes and I must do this very rapidly!One must wonder why you create views at all, if you don't intend them
to stick around for long. Why not just write out the SELECTs in full?
(If you think there's some performance benefit to using a view, you are
quite mistaken.)regards, tom lane
--
Emmanuel SARACCO
Email: esaracco@noos.fr
Tom Lane wrote:
One must wonder why you create views at all, if you don't intend them
to stick around for long. Why not just write out the SELECTs in full?
(If you think there's some performance benefit to using a view, you are
quite mistaken.)
There are no cached query plans in any way?
--
Alessio F. Bragadini alessio@albourne.com
APL Financial Services http://village.albourne.com
Nicosia, Cyprus phone: +357-2-755750
"It is more complicated than you think"
-- The Eighth Networking Truth from RFC 1925
Alessio Bragadini <alessio@albourne.com> writes:
Tom Lane wrote:
One must wonder why you create views at all, if you don't intend them
to stick around for long. Why not just write out the SELECTs in full?
(If you think there's some performance benefit to using a view, you are
quite mistaken.)
There are no cached query plans in any way?
Certainly none associated with views. Views would make a very poor
basis for caching plans, since they typically form only a part of
the eventual query. Example: suppose you write
create view v as select * from foo;
select * from v where indexedcol = 42;
You'd be fairly unhappy if the system were unable to use an indexscan
over foo.indexedcol for this query, no? But a plan associated with the
bare view definition couldn't exploit this possibility, because the
potential to use that index doesn't arise in the context of the view
itself, only in the context of the complete query.
regards, tom lane