Thread: 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