Funktion zusätzlich aufrufen bei Select statement

Hallo allerseits

Ich möchte bei jedem SELECT gegen eine Tabelle zusätzlich vor der Rückgabe eine einfache Funktion aufrufen, die ein refresh() der Tabelle macht. Das Refresh braucht es zwecks Synchronisation mit einer Sybase Datenbank.

Meine erste Idee war eine Regel zu schreiben, aber das klappt natürlich so nicht da das System dann in eine Endlosschleife geht.

CREATE RULE myrule AS ON SELECT TO tabelle DO 
(refresh();
 SELECT * FROM tabelle;
);

Das refresh() braucht max. 30ms. Eine andere Idee wäre einen Cronjob zu machen, welcher das refresh() alle 200ms macht.

Hat jemand vielleicht einen anderen Ansatz?

Vielen Dank,
Manuel

Die Tabelle ändert sich bei einem SELECT ja nicht, sondern nur bei INSERT, UPDATE oder DELETE. Wie wäre es also mit einem TRIGGER, der bei diesen Ereignissen Dein refresh() macht?

Noch besser: Du stößt einen externen Prozeß an, via LISTEN/NOTIFY, via TRIGGER, wenn sich was ändert.

Andreas

Hallo Andreas

Die Tabelle sollte sich vor jedem SELECT updaten, wenn auf der Sybase Datenbank sich was geändert hat.
Das Problem ist, dass die Sybase Datenbank passiv ist und nicht auf die PostgreSQL Datenbank zugreifen kann. D.h. bevor ein SELECT aufgerufen wird, sollte eine Funktion in PostgreSQL bei der Sybase Datenbank nachschauen, ob sich etwas verändert hat, wenn ja, die Tabelle bei PostgreSQL updaten.

Zusatz:
Mit LISTEN/NOTIFY wäre es wahrscheinlich schön möglich, dass Sybase mit PostgreSQL kommuniziert. Das heisst Sybase schickt eine Meldung zu PostgreSQL, dass sich etwas verändert hat und dann kann auch eine Funktion gestartet werden. Ich schau mir das noch genauer an.
Vielen Dank für die Idee!

Ah so, so rum. Ja, dann sollte Sybase das anstoßen.

Andreas

CREATE [ OR REPLACE ] RULE name AS ON event
TO table [ WHERE condition ]
DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command … ) }
einfach das selekt in der rule weglassen … und stattdessen
DO ALSO … refresh()

außerdem:
ALSO ALSO indicates that the commands should be executed in addition to the original command.
If neither ALSO nor INSTEAD is specified, ALSO is the default.
also wie gesagt … das select auf die table weglassen, dann gibt es auch keine rekursion - was du so als “endlosschleife” bemerkst

wenn das natuerlich vor dem selekt passieren soll … jaja … dann wuerde ich versuchen mir eine view zu bauen … auf den deine anwendung schaut … du selektierst also den view → die rule macht ein refresh und einen selekt auf die eigentliche table … bei der du keine rule gesetzt hast

dann gibt es auch keine rekursion



test=# create rule rule_foo as on select to foo do also select now();
ERROR:  rules on SELECT must have action INSTEAD SELECT

Mit Deiner Lösung wäre es also nicht mehr mögleich, ein SELECT auf die Tabelle loszulassen. 5, setzen.


Andreas

scheiss postgres :slight_smile:

wer diesen halbherzigen RULE Müll gefrickelt hat - den sollte man zu den linux-kernel entwicklern schicken … da passt er gut hin

Nein.

Andreas

du baust dir eine funktion die ein setof <table%rowtype> zurueckliefert …

in dieser funktion rufst du zuerst das erwuenschte refresh() auf … und lieferst dann genau nichts zurueck …

als naechstes baust du dir ein view, das dann nichts weiter macht als ein

select * from mySetOftableX()
UNION
select * from tableX

liefert …

das view eben deshalb, dass du nicht in rekursive schwulidäd’n gerätst
mit ein wenig pech ist PG dumm genug die teil-selekts nacheinander auszufuehren …

Netter Versuch. Was genau ist nun der Vorteil, die refresh()-Funktion separat aufzurufen und dann das SELECT?

Andreas

nunja … wichtig ist ja wohl, dass VOR dem select auf die table das refresh aufgerufen wird … oder nicht ?

Ja. Aber das bewirkt Deine Lösung nicht.

Andreas

das hab ich befuerchtet … postgres is schlauer als es manchmal sein sollte :slight_smile:

Du hast wieder mal nur was zu meckern, obwohl du selber die Idee aufgebracht hast, ohne den Vorschlag vorher auszuprobieren.

Da steht richtigerweise DO ALSO und bei “tue auch” erwarte ich nicht dass der Befehl vorher sondern “auch” nachher ausgeführt wird.

Können wir jetzt vom Bashen zurück zum Problem kommen oder hast du noch mehr zu meckern?

Nun ja, das DO ALSO unterstützt nicht eine Rule wo SELECT gebraucht wird (siehe PostgreSQL Doku). Es muss zwingend ein DO INSTEAD gebraucht werden.

Was ich im Prinzip machen machen will ist sowas wie eine Materialized View PostgreSQL/Materialized Views - Jonathan Gardner’s Tech Wiki mit einer Refresh Strategie wie Jonathan das beschreibt:

We can improve performance by implementing a rule that will only refresh the materialized view if 1 second, 10 seconds, 1 minute, or 1 hour has passed since the last refresh and the data is being selected.

Aber eben, Jonathan möchte diese Materialized Views in PostgreSQL einbauen. Ich sehe keine Möglichkeit in SQL vor einem SELECT ein refresh() aufzurufen.

Meine Funktion ist in PL/Perl geschrieben und dort kann man mit RETURN SETOF type im Prinzip ein komplette Tabelle zurückgeben. Dann könnte man zum Beispiel folgendes ausführen:

select * from function();

Aber das Problem ist, dass es super langsam (fast eine Sekunde bei mir auf VMware) und CPU intensiv ist, wenn ich alle Werte auslesen muss, auch wenn ich ein statement habe wie

select * from function() where field=3;

:frowning:

ja deshalb wollt ich das nicht empfehlen … daher auch die idee die funktion() mit dem refresh ne leere menge liefern zu lassen und nen union mit dem eigentlichen select auf die table - und das ganze union in ein view packen … AK meint das ginge nicht … ich habs noch nicht probiert

heul doch … :wink: ach ja … vielleicht wars ja ein heimeliches kompliment ?

und wenn ich schon mit potentiellen loesungen um mich werfe, sehe ich mich absolut nicht in der verpflichtung es selbst zu probieren … immerhin ist es mit arbeit verbunden … das kann der fragesteller auch selbst tun, wenn er die idee fuer prüfungs-wuerdig haelt … wenn nicht - dann ist das sein ding

Dann schreib Dir ein refresh(id), und nutze den Parameter gleich für das Select, um nur das zu selecten, was gesucht ist. Du könntest ja auch einen String mit einer where-Bedingung übergeben und ein dynamisches SQL basteln.

@symbiont: ich sagte nicht, daß das nicht ginge.

Andreas

naja … ich bin ja auch neugierig … hier meine ersten forschungs ergebnisse:

CREATE OR REPLACE FUNCTION refresh()
  RETURNS SETOF atable AS
$BODY$  
BEGIN
END;
$BODY$
  LANGUAGE 'plpgsql' [color=Red]IMMUTABLE[/color] <-- falscher fehler .. muss heissen VOLATILE damit der Optimizer nicht auf die Idee kommt es wegzulassen :)
  COST 10 ROWS 1;



explain analyse
select * from refresh()
union
select * from atable



"Unique  (cost=192.85..204.86 rows=2401 width=4) (actual time=0.105..0.105 rows=0 loops=1)"
"  ->  Sort  (cost=192.85..198.86 rows=2401 width=4) (actual time=0.100..0.100 rows=0 loops=1)"
"        Sort Key: refresh.id"
"        Sort Method:  quicksort  Memory: 17kB"
"        ->  Append  (cost=0.00..58.05 rows=2401 width=4) (actual time=0.067..0.067 rows=0 loops=1)"
"              ->  Function Scan on refresh  (cost=0.00..0.04 rows=1 width=4) (actual time=0.057..0.057 rows=0 loops=1)"
"              ->  Seq Scan on atable  (cost=0.00..34.00 rows=2400 width=4) (actual time=0.002..0.002 rows=0 loops=1)"
"Total runtime: 0.227 ms"

sieht doch gut aus … oder?

wenn er allerdings die beiden geschichten parallel abarbeitet waere das kein gutes ding …

dann koennte man allerdings versuchen das refresh() in einer subquery unterzubringen

also:

explain analyse
select * from atable
where id not in (select id from refresh())



"Seq Scan on atable  (cost=0.04..40.04 rows=1200 width=4) (actual time=0.100..0.114 rows=8 loops=1)"
"  Filter: (NOT (hashed subplan))"
"  SubPlan"
"    ->  Function Scan on refresh  (cost=0.00..0.04 rows=1 width=4) (actual time=0.048..0.048 rows=0 loops=1)"
"Total runtime: 0.301 ms"

das view dazu koennte dann so ausschauen:

CREATE OR REPLACE VIEW vtable AS 
 SELECT atable.id
   FROM atable
  WHERE NOT (atable.id IN ( SELECT refresh.id
           FROM refresh() refresh(id)));

wenn er allerdings die beiden geschichten parallel abarbeitet waere das kein gutes ding …

dann koennte man allerdings versuchen das refresh() in einer subquery unterzubringen

symbiont,

Ich finde Deine Idee mit dem UNION im Prinzip super und habe ein paar Tests gemacht. Leider ist das

SELECT * FROM tabelle

immer schneller als die Funktion. D.h. erst beim zweiten Aufruf zeigt es die richtigen Werte an. Das ändert auch nicht, wenn ich die Reihenfolge vertausche. :frowning:
Was kann ich mit einem subquery anstellen um das Problem zu verhindern? Anscheinend werden beide parallel abgearbeitet wie Du vermutet hast.