Using Postgresql Notify and Listen
Moderators: gto, cipto_kh, EgonHugeist, olehs
- mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
Not implemented at the moment.
I'm looking for a brave programmer who wants to
- Split the TZIBEventAlerter into a general component and a interbase specific backend
- Write the postgres specific backend.
I think it might be possible to do a 'quick' hack and make a similar TZIBNotifyAlerter. This may be a quicker solution for you, but I refuse to integrate it into the component suite as it's against all principles behind zeoslib.
Mark
I'm looking for a brave programmer who wants to
- Split the TZIBEventAlerter into a general component and a interbase specific backend
- Write the postgres specific backend.
I think it might be possible to do a 'quick' hack and make a similar TZIBNotifyAlerter. This may be a quicker solution for you, but I refuse to integrate it into the component suite as it's against all principles behind zeoslib.
Mark
-
- Expert Boarder
- Posts: 164
- Joined: 18.03.2008, 13:03
- Contact:
Hello, mrLion,
It's nice to have someone who is willing to enrich the Zeos functionality for Postgresql. I have a question for you - how are you going to listen for event notifies? http://www.postgresql.org/docs/current/ ... isten.html states that "With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received." Maybe you have done some experiments already and know what is performance penalty for that?
Also I'd like to warn you not to use other thread for calling PQnotifies, as only one thread should use connection handle in libpq.
Don't hesitate to ask anything - I'll be happy to advise.
It's nice to have someone who is willing to enrich the Zeos functionality for Postgresql. I have a question for you - how are you going to listen for event notifies? http://www.postgresql.org/docs/current/ ... isten.html states that "With the libpq library, the application issues LISTEN as an ordinary SQL command, and then must periodically call the function PQnotifies to find out whether any notification events have been received." Maybe you have done some experiments already and know what is performance penalty for that?
Also I'd like to warn you not to use other thread for calling PQnotifies, as only one thread should use connection handle in libpq.
Don't hesitate to ask anything - I'll be happy to advise.
So, i`m in debugging process ....
code for TZPGEventAlerter (it`s work, but tested only for PostgreSQL 8.3.10):
Version lover 8.3.x not tested and not planned for test. If anybody have any comments and patches for this code i`m be vary glad...
code for TZPGEventAlerter (it`s work, but tested only for PostgreSQL 8.3.10):
Code: Select all
//-- (C) Ivan Rog - 2010 Russia
//-- Tested only for 8.3.10 PostgreSQL!
//-- version PGSQL <8.3.x not supperted now
unit ZPGEventAlerter;
{$I ..\dbc\ZDbc.inc}
interface
uses
SysUtils, Classes, Math,
{$IFNDEF UNIX}
Windows, ExtCtrls,
{$ELSE}
{$IFNDEF FPC}
libc,
{$ENDIF}
{$ENDIF}
ZDbcPostgreSQL, ZPlainPostgreSQLDriver, ZConnection, ZDbcIntfs,
ZPlainPostgreSQL7, ZPlainPostgreSQL8;
//****************************************************************************//
// TZPGEventAlerter Object //
// Asynchronous notifying //
//****************************************************************************//
type
//-- структура, возвращаемая сервером при получении оповещений
// typedef struct pgNotify {
// char *relname; /* notification condition name */
// int be_pid; /* process ID of notifying server process */
// char *extra; /* notification parameter */
// } PGnotify_my;
//-- Define for PostgreSQL 8.3.x and above!
TZPostgreSQLNotify_83x = record
relname : PChar; { name of relation containing data }
be_pid : Integer; { process id of backend }
extra : PChar; { notification parameter }
end;
PZPostgreSQLNotify_83x =^TZPostgreSQLNotify_83x;
TZPGNotifyEvent = procedure (Sender: TObject; Event: string; ProcessID : Integer) of object;
TZPGEventAlerter = class (TComponent)
private
FActive : Boolean;
FEvents : TStrings;
FTimer : TTimer;
FConnection : TZConnection; //-- соединение
FNotifyFired : TZPGNotifyEvent;
res : PGresult; //-- результат выполнения команд сервера
protected
procedure SetActive (Value: Boolean);
function GetInterval : Cardinal;
procedure SetInterval (Value: Cardinal);
procedure SetEvents (Value: TStrings);
procedure SetConnection (Value: TZConnection);
procedure TimerTick (Sender: TObject);
procedure CheckEvents;
procedure OpenNotify;
procedure CloseNotify;
public
constructor Create (AOwner: TComponent); override;
destructor Destroy; override;
published
property Connection: TZConnection read FConnection write SetConnection;
property Active: Boolean read FActive write SetActive;
property Events: TStrings read FEvents write SetEvents;
property Interval: Cardinal read GetInterval write SetInterval default 250;
property OnNotify: TZPGNotifyEvent read FNotifyFired write FNotifyFired;
end;
implementation
constructor TZPGEventAlerter.Create(AOwner: TComponent);
var I: integer;
begin
inherited Create(AOwner);
FEvents := TStringList.Create;
with TStringList(FEvents) do
begin
Duplicates := dupIgnore;
end;
FTimer := TTimer.Create(Self);
FTimer.Enabled := False;
SetInterval(250);
FTimer.OnTimer := TimerTick;
FActive := False;
if (csDesigning in ComponentState) and Assigned(AOwner) then
for I := AOwner.ComponentCount - 1 downto 0 do
if AOwner.Components[I] is TZConnection then
begin
Connection := AOwner.Components[I] as TZConnection;
Break;
end;
end;
destructor TZPGEventAlerter.Destroy;
begin
CloseNotify;
FEvents.Free;
FTimer.Free;
inherited Destroy;
end;
procedure TZPGEventAlerter.SetInterval(Value: Cardinal);
begin
FTimer.Interval := Value;
end;
function TZPGEventAlerter.GetInterval;
begin
Result := FTimer.Interval;
end;
procedure TZPGEventAlerter.SetEvents(Value: TStrings);
var
I: Integer;
begin
FEvents.Assign(Value);
for I := 0 to FEvents.Count -1 do
FEvents[I] := Trim(FEvents[I]);
end;
procedure TZPGEventAlerter.SetActive(Value: Boolean);
begin
if FActive <> Value then
begin
if Value then
OpenNotify else
CloseNotify;
end;
end;
procedure TZPGEventAlerter.SetConnection(Value: TZConnection);
begin
if FConnection <> Value then
begin
CloseNotify;
FConnection := Value;
end;
end;
procedure TZPGEventAlerter.TimerTick(Sender: TObject);
begin
if not Active then
FTimer.Enabled := False
else
CheckEvents;
end;
procedure TZPGEventAlerter.OpenNotify;
var
I : Integer;
tmp : array [0..255] of Char;
Handle : PZPostgreSQLConnect;
begin
if Active then Exit;
if not Assigned(FConnection) then Exit;
if ((csLoading in ComponentState) or (csDesigning in ComponentState)) then Exit;
if not FConnection.Connected then Exit;
Handle:=(FConnection.DbcConnection as IZPostgreSQLConnection).GetConnectionHandle;
if Handle=nil then Exit;
for I := 0 to FEvents.Count-1 do
begin
StrPCopy(tmp, 'listen '+FEvents.Strings[i]);
res:=Zplainpostgresql8.PQexec(Handle,tmp);
if (Zplainpostgresql8.PQresultStatus(res) <> Zplainpostgresql8.PGRES_COMMAND_OK)
then
begin
//-- произошла ошибка! ошибка!!!
Zplainpostgresql8.PQclear(res);
Exit;
end;
end;
Zplainpostgresql8.PQclear(res);
FActive := True;
FTimer.Enabled := True; //-- запуск таймера опроса
end;
procedure TZPGEventAlerter.CloseNotify;
var
I: Integer;
tmp : array [0..255] of Char;
Handle : PZPostgreSQLConnect;
begin
if not Active then Exit;
FActive := False;
FTimer.Enabled := False;
Handle:=(FConnection.DbcConnection as IZPostgreSQLConnection).GetConnectionHandle;
if Handle=nil then Exit;
for I := 0 to FEvents.Count-1 do
begin
StrPCopy(tmp, 'unlisten '+FEvents.Strings[i]);
res:=Zplainpostgresql8.PQexec(Handle,tmp);
if (Zplainpostgresql8.PQresultStatus(res) <> Zplainpostgresql8.PGRES_COMMAND_OK)
then
begin
//-- произошла ошибка! ошибка!!!
Zplainpostgresql8.PQclear(res);
Exit;
end;
end;
Zplainpostgresql8.PQclear(res);
end;
procedure TZPGEventAlerter.CheckEvents;
var
Pid : Integer;
notify : PZPostgreSQLNotify_83x;
Handle : PZPostgreSQLConnect;
begin
Handle:=(FConnection.DbcConnection as IZPostgreSQLConnection).GetConnectionHandle;
if Handle=nil then Exit;
if Zplainpostgresql8.PQconsumeInput(Handle)=1 then
begin
while true do
begin
///!!!!!! НЕОБХОДИМО ПРЕОБРАЗОВАНИЕ ТИПОВ / NEEDS FOR TYPE CONVERT
notify:=PZPostgreSQLNotify_83x(Zplainpostgresql8.PQnotifies(Handle));
if Notify=nil then break;
if Assigned(FNotifyFired) then FNotifyFired(Self, Notify.relname, Notify.be_pid);
end;
Zplainpostgresql8.PQFreemem(notify);
end;
end;
end.
Last edited by mrLion on 26.03.2010, 22:02, edited 1 time in total.
- mdaems
- Zeos Project Manager
- Posts: 2766
- Joined: 20.09.2005, 15:28
- Location: Brussels, Belgium
- Contact:
Hi,
Good job, thanks. I hope we get some comments from other users.
Why can't you use the code defined in ZPlainPostgreSqlDriver.pas?
I don't think you're using the extra field and as it's only some pointers that are passed around that should do the job.
To get this unit included into the official zeos library, can you send a full patch for the 6.6-patches branch or the 7.x testing branch?
Beware, for inclusion in 6.6 it must be disabled by default and should only be enabled by uncommenting a line in zeos.inc. This restriction isn't needed for 7.x
Mark
Good job, thanks. I hope we get some comments from other users.
Why can't you use the code defined in ZPlainPostgreSqlDriver.pas?
Code: Select all
TZPostgreSQLNotify = packed record
relname: PAnsiChar; { name of relation containing data }
be_pid: Integer; { process id of backend }
end;
PZPostgreSQLNotify = ^TZPostgreSQLNotify;
To get this unit included into the official zeos library, can you send a full patch for the 6.6-patches branch or the 7.x testing branch?
Beware, for inclusion in 6.6 it must be disabled by default and should only be enabled by uncommenting a line in zeos.inc. This restriction isn't needed for 7.x
Mark
Generally, I use those definitions, which gave developers PostgreSQL in their documentation. And besides function PQnotify, downloadable in pline-driver ZEOS, returns another data type, which is defined in general for the devil knows what version of Postgre ... In C + +, which I use in their projects, type conversion is not such a problem, as in Pascal!mdaems wrote:Hi,
Why can't you use the code defined in ZPlainPostgreSqlDriver.pas?
Also, please note that in version 9.x Postgre developers promise to add new functionality to the system messages. So the use of a precise definition of this structure to me is crucial.
Yes, i`m can. At what address to send?To get this unit included into the official zeos library, can you send a full patch for the 6.6-patches branch or the 7.x testing branch?
I have already made the necessary changes in the library itself (my local copy). changed only a couple of files. But the icon of the component not do it.Beware, for inclusion in 6.6 it must be disabled by default and should only be enabled by uncommenting a line in zeos.inc. This restriction isn't needed for 7.x
With version 7.0 is not yet understood. Maybe a bit later. In addition, my work requires integration ZEOS system to generate reports "FastReport". I think that a bit later I'll do it too. What do you think is it worth it to include in the overall project?
Ivan.
-
- Expert Boarder
- Posts: 164
- Joined: 18.03.2008, 13:03
- Contact:
Hello mdaems,
I wanted to ask if there will be separate components like ZIBEventAlerter, ZPGEventAlerter, or the effort should be made to put them into single ZEventAlerter? First option is bad as it destroys the ease of protocol selection in TZConnection as there would be separate components for every protocol. The second option brings questions too - what is the future of ZIBEventAlerter? Should it be removed? Should it be maintained along with ZEventAlerter?
Either way the questions should be answered now, before more implementations of EventAlerter appear.
I wanted to ask if there will be separate components like ZIBEventAlerter, ZPGEventAlerter, or the effort should be made to put them into single ZEventAlerter? First option is bad as it destroys the ease of protocol selection in TZConnection as there would be separate components for every protocol. The second option brings questions too - what is the future of ZIBEventAlerter? Should it be removed? Should it be maintained along with ZEventAlerter?
Either way the questions should be answered now, before more implementations of EventAlerter appear.
mdaems, Wild_Pointer, Greetings.
I need about a week in order to make code refactoring. Then I lay out a version and will be ready for further discussion. I want to take into account the wishes voiced above, as well as to make the components independent of the server version of PostgreSQL.
Yes, I forgot one more thing: as far as I know Oracle also provides the possibility of receiving messages.
In addition, I want to say that writing components in Delphi is not my forte. Hence the union code in one component I would not be able to do. Likely ...
I need about a week in order to make code refactoring. Then I lay out a version and will be ready for further discussion. I want to take into account the wishes voiced above, as well as to make the components independent of the server version of PostgreSQL.
Yes, I forgot one more thing: as far as I know Oracle also provides the possibility of receiving messages.
In addition, I want to say that writing components in Delphi is not my forte. Hence the union code in one component I would not be able to do. Likely ...
Last edited by mrLion on 25.03.2010, 19:48, edited 1 time in total.