libpgtcl modifications

Luke Myers wrote:

Gerhard Hintermayer wrote:

Attached is the patch with the described functionality. Oops , cannot
attach files with google, and my ISP does'nt have
comp.databases.postgresql.interfaces :-(, what shall I do ? My ISP
says, this NG is not listed in the BIG8 groups, is that correct ?

Gerhard

Can you use news.postgresql.org?

-- Luke Kale Myers

Indeed that works, and I even managed to use it with leafnode.

Here's the patch (hope i did it right)

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

Can I have some description of what this patch does?

---------------------------------------------------------------------------

Gerhard Hintermayer wrote:

Luke Myers wrote:

Gerhard Hintermayer wrote:

Attached is the patch with the described functionality. Oops , cannot
attach files with google, and my ISP does'nt have
comp.databases.postgresql.interfaces :-(, what shall I do ? My ISP
says, this NG is not listed in the BIG8 groups, is that correct ?

Gerhard

Can you use news.postgresql.org?

-- Luke Kale Myers

Indeed that works, and I even managed to use it with leafnode.

Here's the patch (hope i did it right)

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

[ application/x-gunzip is not supported, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Bruce Momjian wrote:

Can I have some description of what this patch does?

Unfortunately some of my postings seem to vanish in cyberspace ;-)

What I have done for libpgtcl:
Everytime if I do PQconsumeInput (when the backend channel gets
readable) I check for the return value. (0 == error) and generate a
notification manually, e.g. fixed string connection_closed) and pass it to the
TCL event queue. The only other thing I had to do is to comment out removing
all pending events in PgStopNotifyEventSource whenever the connection was
unexpectedly closed (so the manually generated event will not be deleted).

A broken backend connection triggers a notify event to the client (fixed
notification string "connection_closed") so proper action can be taken to switch
to another database server etc. Remember that this is event driven. If you have
applications, that have idle database connections most of the time, you'll get
immediate feedback of a dying server. Upon connection to the server issue a
pg_notify for notify event "connection_closed" and whenever the backend crashes
(which it does do in very very rare cases) you get an event driven recovery. (of
course the Tcl-Event loop has to be processed). Issuing a notification
"connection_closed" on a still working database could be used for switching to
another db-server (which I've actually impelemented right now).

I did this not because we have crashing db-servers all the time, in fact we have
running pg for many years without any major problems in a production environment
(recipe and batch data for petfood production).
One reason was to get notifications to applications, that run permanently and
have a persistent db connection, in case the primary server should be changed
for whatever reason (network problems, server shutdown, maintenance etc.)

Gerhard.

PS:

[ application/x-gunzip is not supported, skipping... ]

are you joking, which file format do you suggest for a patch ?

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

Bruce Momjian wrote:

Gerhard Hintermayer wrote:

Bruce Momjian wrote:

Can I have some description of what this patch does?

So this is a patch that will improve tcl for most users, and doesn't
have a down-side?

I didn't find one, I did some testing, but I'm still not on the
production system up to now.
Well one down-side is, if you operate on the connection in the notify
script, you could get seg faults (I think pg_disconnect does work, but
e.g. pg_unlisten will core dump, not sure now, have to check that on
monday, when I'm in the office), but that's predictable, if the
connection is broken, you should'nt use it any more.

pgaccess could benefit from that too.

Anybody, who does a connect to a db in his script, and want's to get
immediate feedback of a broken connection/fast server shutdown even if
you have long idle times between db operations, could benefit from that.
The easiest way would be to exit from the app with a short message.

Gerhard

PS: How's the plan for upgrade of libpgtcl to the new Tcl_Obj based API
(introduced in 8.0 I think). I'd be willing to do some of the
implementations. How about Tcl8.4 support (still beta, but will be
released in a few month hopefully). Is support for versions before 8.0
really necessary any more, this would result in both a string based
*and* object based implementation ?

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

--ELM1029601346-3976-0_--

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Gerhard Hintermayer wrote:

Luke Myers wrote:

Gerhard Hintermayer wrote:

Attached is the patch with the described functionality. Oops , cannot
attach files with google, and my ISP does'nt have
comp.databases.postgresql.interfaces :-(, what shall I do ? My ISP
says, this NG is not listed in the BIG8 groups, is that correct ?

Gerhard

Can you use news.postgresql.org?

-- Luke Kale Myers

Indeed that works, and I even managed to use it with leafnode.

Here's the patch (hope i did it right)

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

[ application/x-gunzip is not supported, skipping... ]

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Gerhard Hintermayer wrote:

Bruce Momjian wrote:

Can I have some description of what this patch does?

Unfortunately some of my postings seem to vanish in cyberspace ;-)

What I have done for libpgtcl:
Everytime if I do PQconsumeInput (when the backend channel gets
readable) I check for the return value. (0 == error) and generate a
notification manually, e.g. fixed string connection_closed) and pass it to the
TCL event queue. The only other thing I had to do is to comment out removing
all pending events in PgStopNotifyEventSource whenever the connection was
unexpectedly closed (so the manually generated event will not be deleted).

A broken backend connection triggers a notify event to the client (fixed
notification string "connection_closed") so proper action can be taken to switch
to another database server etc. Remember that this is event driven. If you have
applications, that have idle database connections most of the time, you'll get
immediate feedback of a dying server. Upon connection to the server issue a
pg_notify for notify event "connection_closed" and whenever the backend crashes
(which it does do in very very rare cases) you get an event driven recovery. (of
course the Tcl-Event loop has to be processed). Issuing a notification
"connection_closed" on a still working database could be used for switching to
another db-server (which I've actually impelemented right now).

I did this not because we have crashing db-servers all the time, in fact we have
running pg for many years without any major problems in a production environment
(recipe and batch data for petfood production).
One reason was to get notifications to applications, that run permanently and
have a persistent db connection, in case the primary server should be changed
for whatever reason (network problems, server shutdown, maintenance etc.)

Gerhard.

PS:

[ application/x-gunzip is not supported, skipping... ]

are you joking, which file format do you suggest for a patch ?

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Revision 2 was not so quick, but also not so dirty. Here's my first draft.
I have modified:
- Version number of Pgtcl changed to 1.4 (to reflect the additional command)
- added a command pg_on_connection_loss
Syntax: pg_on_connection_loss conn ?callback?
Use no callback parameter to query the actuall callback script and
with parameter to set/change callback. The callback is evaluated
whenever the connection to the backend gets lost (eof condition ?).

I do this by checking for a false return value from PQconsumeInput.
Today I did some quick testing, no segfaults, no errors. Broken backends
are detected both in idle connections and when you're in a COPY command
(these are the two possibilities, where PQconsumeInput is called).
I'm using the same readable fileevent on the database connection as
pg_listen does internally. The structure Pg_ConnectionId was extended by
two fields : char *conn_loss to store the callback and Tcl_Interp
*interp to store the interpreter, in which context the callback will be
evaluated.
Can someone with more experience on Tcl event programming take a look at
this, I mananged to write some Tcl extensions, but am new to modifying
the Tcl event-loop. (what I did, was copying most of the pg_listen stuff).
Basically PgStartNotifyEventSource() is called, when a callback proc is
supplied to pg_on_connection_loss, which (unmodified) does install a
readable fileevent on the db connection, then I check for false return
value of PQconsumeInput and add a ConnLossEvent type to the event queue.
(pg_)listen callbacks are deleted immediately from the the event queue
when the db connection is broken, just like in the unpatch version. What
This a point worth to discuss: Which commands will dump core when used
in the disconnect handler ? pg_disconnect works, but the best thing
might be to invoke pg_disconnect as the first command within the
disconnect handler per default (in C code, not in Tcl) so possible
subsequent operations on the just closed connection would at least
trigger a "... is not a valid postgresql connection" error, but not run
into any segfaults.

I will do some more testing tomorrow, but since I only can post via
google at work, I'd like to get some feedback more quickly.

Gerhard

PS: I need my patch also for an older version of Postgresql (7.0.2), can
I copy the whole libpgtcl directory into the 7.0.2 source path without
any risk ?

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

Last Thursday I posted revision 2 of my patch (directly to
news.postgresql.org with *no* error). Unfortuntely it does not show up
here. This are the longest 6 to 9 hours I've ever seen. I Will repost
in the evening :-(

Gerhard

Gerhard Hintermayer wrote:

Last Thursday I posted revision 2 of my patch (directly to
news.postgresql.org with *no* error). Unfortuntely it does not show up
here. This are the longest 6 to 9 hours I've ever seen. I Will repost
in the evening :-(

Gerhard

Damn, I posted that from the PC located at my girlfriend. Do'nt have the
description I wrote (It was quite long). Btw, is there a more save way to do
postings to comp.databases.postgresql.interfaces. I also do get strange replys

Sorry, forgot the attachment. Here it is.

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

Your patch has been added to the PostgreSQL unapplied patches list at:

http://candle.pha.pa.us/cgi-bin/pgpatches

I will try to apply it within the next 48 hours.

---------------------------------------------------------------------------

Gerhard Hintermayer wrote:

Gerhard Hintermayer wrote:

Last Thursday I posted revision 2 of my patch (directly to
news.postgresql.org with *no* error). Unfortuntely it does not show up
here. This are the longest 6 to 9 hours I've ever seen. I Will repost
in the evening :-(

Gerhard

Damn, I posted that from the PC located at my girlfriend. Do'nt have the
description I wrote (It was quite long). Btw, is there a more save way to do
postings to comp.databases.postgresql.interfaces. I also do get strange replys
from postmaster@afk.dk when I do post via Google ? Strange !

Here you have a short description, the long one you'll get when I'm at my other
PC next time.

I added a new command:

pg_on_connection_loss conn ?callback?

changed libpgtcl version to 1.4 to reflect the additional command.

No callback to query the current script, with callback to set/change callback
script. Script is executed whenever (all occurences of) PQconsumeInput return
false, i.e. both in an idle database connection and while processing a COPY
command. Tested a bit, no errors, alle db-operations in callback are allowed
(and trigger a "is not a valid connection" or so error)

Any comments ?

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Patch applied for 7.3. Thanks.

---------------------------------------------------------------------------

Gerhard Hintermayer wrote:

Gerhard Hintermayer wrote:

Last Thursday I posted revision 2 of my patch (directly to
news.postgresql.org with *no* error). Unfortuntely it does not show up
here. This are the longest 6 to 9 hours I've ever seen. I Will repost
in the evening :-(

Gerhard

Damn, I posted that from the PC located at my girlfriend. Do'nt have the
description I wrote (It was quite long). Btw, is there a more save way to do
postings to comp.databases.postgresql.interfaces. I also do get strange replys
from postmaster@afk.dk when I do post via Google ? Strange !

Here you have a short description, the long one you'll get when I'm at my other
PC next time.

I added a new command:

pg_on_connection_loss conn ?callback?

changed libpgtcl version to 1.4 to reflect the additional command.

No callback to query the current script, with callback to set/change callback
script. Script is executed whenever (all occurences of) PQconsumeInput return
false, i.e. both in an idle database connection and while processing a COPY
command. Tested a bit, no errors, alle db-operations in callback are allowed
(and trigger a "is not a valid connection" or so error)

Any comments ?

--
Gerhard Hintermayer
http://www.inode.at/g.hintermayer

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073