how to start a procedure after postgresql started.

Hello

2011/5/22 jun yang <slickqt@gmail.com>:

now all the question:
1.how start a procedure or a script after postgresql start.
2.how to get notify when a table created.
3.how to get notify when a database created.

Probably it isn't possible with Pg 9.0 and older. Maybe it is possible
with callbacks for SE-Linux support in 9.1, but you have to write
module in C.

Regards

Pavel Stehule

Pavel Stehule wrote:

Hello

2011/5/22 jun yang <slickqt@gmail.com>:

now all the question:
1.how start a procedure or a script after postgresql start.
2.how to get notify when a table created.
3.how to get notify when a database created.

Probably it isn't possible with Pg 9.0 and older. Maybe it is possible
with callbacks for SE-Linux support in 9.1, but you have to write
module in C.

Well, if you can run a stored procedure automatically when Postgres starts, that
looks like a necessary step to being able to implement an entire application
inside Postgres.

Starting Postgres is running the application. The analogy is that Postgres is
the VM/language interpreter and the stored procedure is the script to run.

Now if said stored procedure has access to features that collectively let it be
computationally complete, including arbitrary user I/O, then you're done.

-- Darren Duncan

On Sat, May 21, 2011 at 10:57 PM, jun yang <slickqt@gmail.com> wrote:

now all the question:
1.how start a procedure or a script after postgresql start.

Do you need this stored procedure or script to always run at pg
database start? Or every time a client connects to the database? Or
every minute, or every hour or every day?

2.how to get notify when a table created.
3.how to get notify when a database created.

Set db to log all dll, scrape logs, email timestamp from table or db
creation. Do you need more info other than just that it was created?

On 05/21/11 10:41 PM, Darren Duncan wrote:

Well, if you can run a stored procedure automatically when Postgres
starts, that looks like a necessary step to being able to implement an
entire application inside Postgres.

Starting Postgres is running the application. The analogy is that
Postgres is the VM/language interpreter and the stored procedure is
the script to run.

Now if said stored procedure has access to features that collectively
let it be computationally complete, including arbitrary user I/O, then
you're done.

adding a

psql -d dbname -c "select myfunc()" &

to your postgres service start script would satisfy this....

...but your entire application would be running in a single
transaction. I don't think thats a good thing.

--
john r pierce N 37, W 123
santa cruz ca mid-left coast

we don't need se-linux function now ,so it is not the good idea, and
se-linux don't available on windows.

2011/5/22 Pavel Stehule <pavel.stehule@gmail.com>:

2011/5/22 Scott Marlowe <scott.marlowe@gmail.com>:

On Sat, May 21, 2011 at 10:57 PM, jun yang <slickqt@gmail.com> wrote:

now all the question:
1.how start a procedure or a script after postgresql start.

Do you need this stored procedure or script to always run at pg
database start?  Or every time a client connects to the database?  Or
every  minute, or every hour or every day?

i am just want pg execute the given stored procedure every time after
pg sucess start.

2.how to get notify when a table created.
3.how to get notify when a database created.

Set db to log all dll, scrape logs, email timestamp from table or db
creation.  Do you need more info other than just that it was created?

well,it may be not what i want,
if i can start a procedure after pg start,then i would like to get
notify when new object create,of course,i should be subcribe first.
now what i interest is the database created, when a new database
created,i want to init some schme and table,index in the it.

When I install the 9.0.x version on my Window Visita Laptop, I get the error. The "Windows Scripting Host" is up. So it is not the topic of this article: http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html

How to resolve this problem?

On Sun, May 22, 2011 at 6:49 AM, jun yang <slickqt@gmail.com> wrote:

2011/5/22 Scott Marlowe <scott.marlowe@gmail.com>:

On Sat, May 21, 2011 at 10:57 PM, jun yang <slickqt@gmail.com> wrote:

now all the question:
1.how start a procedure or a script after postgresql start.

Do you need this stored procedure or script to always run at pg
database start?  Or every time a client connects to the database?  Or
every  minute, or every hour or every day?

i am just want pg execute the given stored procedure every time after
pg sucess start.

Then just add the logic to run it from your init script. If you're
running something like ubuntu there's a script in /etc/init.d to start
postgres you can hack up to add a new step at the end. Not sure
that's the best place but it'll work.

2.how to get notify when a table created.
3.how to get notify when a database created.

Set db to log all dll, scrape logs, email timestamp from table or db
creation.  Do you need more info other than just that it was created?

well,it may be not what i want,
if i can start a procedure after pg start,then i would like to get
notify when new object create,of course,i should be subcribe first.
now what i interest is the database created, when a new database
created,i want to init some schme and table,index in the it.

If you just need each new db to have certain tables, then put them in
template1 and when you create a new db they'll be there. As is often
the case, instead of asking how to do very specific things, you're
better off telling us what you're trying to accomplish in the bigger
sense so we can recommend better ways to accomplish what you want.

Note that PostgreSQL does NOT support triggers on system tables and
objects so you'll have to find some way of monitoring pg logs if you
want to know when things like new tables are created. Again, give us
the bigger picture and we can likely be more helpful.

John R Pierce wrote:

On 05/21/11 10:41 PM, Darren Duncan wrote:

Well, if you can run a stored procedure automatically when Postgres
starts, that looks like a necessary step to being able to implement an
entire application inside Postgres.

Starting Postgres is running the application. The analogy is that
Postgres is the VM/language interpreter and the stored procedure is
the script to run.

Now if said stored procedure has access to features that collectively
let it be computationally complete, including arbitrary user I/O, then
you're done.

adding a

psql -d dbname -c "select myfunc()" &

to your postgres service start script would satisfy this....

Good that this at least is possible, and ostensibly it is good enough.

I was actually thinking of something more on the line of a trigger, such that
the system allows triggers to respond to a wide variety of stimulus, such as the
stimulus of the DBMS starting up, rather than just the stimulus of
data-manipulating a table. For the purpose I mention, ideally the user wouldn't
have to know the name of the main program routine, but would just know, its the
database or cluster. You could package your database cluster and say that *is*
the application.

...but your entire application would be running in a single
transaction. I don't think thats a good thing.

Absolutely. But if the kind of stored procedures were supported that can do
anything a database client can do, including transaction control statements,
then the main program routine would typically be one of those.

-- Darren Duncan

2011/5/23 Darren Duncan <darren@darrenduncan.net>:

John R Pierce wrote:

On 05/21/11 10:41 PM, Darren Duncan wrote:

Well, if you can run a stored procedure automatically when Postgres
starts, that looks like a necessary step to being able to implement an
entire application inside Postgres.

Starting Postgres is running the application.  The analogy is that
Postgres is the VM/language interpreter and the stored procedure is the
script to run.

Now if said stored procedure has access to features that collectively let
it be computationally complete, including arbitrary user I/O, then you're
done.

adding a

   psql -d dbname -c "select myfunc()" &

to your postgres service start script would satisfy this....

Good that this at least is possible, and ostensibly it is good enough.

I was actually thinking of something more on the line of a trigger, such
that the system allows triggers to respond to a wide variety of stimulus,
such as the stimulus of the DBMS starting up, rather than just the stimulus
of data-manipulating a table.  For the purpose I mention, ideally the user
wouldn't have to know the name of the main program routine, but would just
know, its the database or cluster.  You could package your database cluster
and say that *is* the application.

wow,you go far ahead of me,it's interesting. since pg has so many
procedure language,pl/perl,pl/python etc.

On 05/22/11 10:45 AM, Darren Duncan wrote:

...but your entire application would be running in a single
transaction. I don't think thats a good thing.

Absolutely. But if the kind of stored procedures were supported that
can do anything a database client can do, including transaction
control statements, then the main program routine would typically be
one of those.

yes, but postgres doesn't support the idea of stored procedures callable
outside of transactions, so I don't know how this could be implemented
without some major rework of the core engine.

for the sake of the novices amongst us, let me clarify my earlier
statement that a single long running transaction is not a good thing.
Vacuum can not free up tuples newer than the oldest pending
transaction. This will put quite a lot of hurt on a update intensive
database over a period of hours or days.

--
john r pierce N 37, W 123
santa cruz ca mid-left coast

2011/5/22 Scott Marlowe <scott.marlowe@gmail.com>:

On Sun, May 22, 2011 at 6:49 AM, jun yang <slickqt@gmail.com> wrote:

2011/5/22 Scott Marlowe <scott.marlowe@gmail.com>:

On Sat, May 21, 2011 at 10:57 PM, jun yang <slickqt@gmail.com> wrote:

now all the question:
1.how start a procedure or a script after postgresql start.

Do you need this stored procedure or script to always run at pg
database start?  Or every time a client connects to the database?  Or
every  minute, or every hour or every day?

i am just want pg execute the given stored procedure every time after
pg sucess start.

Then just add the logic to run it from your init script.    If you're
running something like ubuntu there's a script in /etc/init.d to start
postgres you can hack up to add a new step at the end.  Not sure
that's the best place but it'll work.

2.how to get notify when a table created.
3.how to get notify when a database created.

Set db to log all dll, scrape logs, email timestamp from table or db
creation.  Do you need more info other than just that it was created?

well,it may be not what i want,
if i can start a procedure after pg start,then i would like to get
notify when new object create,of course,i should be subcribe first.
now what i interest is the database created, when a new database
created,i want to init some schme and table,index in the it.

If you just need each new db to have certain tables, then put them in
template1 and when you create a new db they'll be there.  As is often
the case, instead of asking how to do very specific things, you're
better off telling us what you're trying to accomplish in the bigger
sense so we can recommend better ways to accomplish what you want.

Note that PostgreSQL does NOT support triggers on system tables and
objects so you'll have to find some way of monitoring pg logs if you
want to know when things like new tables are created.  Again, give us
the bigger picture and we can likely be more helpful.

what we want to do is explore the ability to move the system to the
architecture like below:
some pg---message broker(qpid)---(web front and some collect data
terminal,some business logic server,some system status monitor)
when pg start it subscribe to qpid then process the message send to it.
when pg is down,the important message is saved in message broker.
when web front has activity,the data send to message broker and will
route to pg then saved,it may be fire some trigger and some messge
send to message broker then to web front or collect data terminal
when collect data terminal has data,it send to broker.
through the route ability of message broker,the message send to it
will be send to right pg instance,log data to log pg instance,finance
data to finance pg instance,repair data to repair pg instance etc.
message broker has cluster and federation,pg has hot standby and sync
replication, deployment will be easier for the life after the first
time.
the prupose of get notify of object create result,first is to monitor
the system status,second is want to do some special init action with
such a object.

On 22/05/2011 10:28 PM, Wei wrote:

When I install the 9.0.x version on my Window Visita Laptop, I get the error. The "Windows Scripting Host" is up. So it is not the topic of this article: http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html

Tip for mailing list communication: Don't say "the error". Explain the
exact text of the error. You *did* do one of the other really important
things, which is to always link to any article or documentation when you
mention it so people know exactly what you are talking about.

The usual culprit in this situation is virus scanning software. Consider
disabling it for the installation.

By the way, my previous message to you was a brisk to the point of being
borderline rude. That wasn't fair; you've clearly done at least a bit of
reading, and just need to work on your communication skills. It's easy
for me as a lifetime English speaker who's been on mailing lists for a
long time to think things are easy and obvious when they are not and to
get frustrated - but that frustration isn't always reasonable when I
look at things from where you might be coming from.

Another random little tip: You will also discover that many people will
not see your message if you reply to an existing, unrelated thread
they're not interested in. This is true even if you change the subject
line, because many email programs "thread" messages using hidden
information on which message is a reply to which other message. Nobody
expects you to know that and it's not important, but it *is* helpful to
know, because it means that creating a blank new message to the mailing
list will be more likely to be read than a reply to an unrelated topic.
Weird, isn't it?

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

On 23/05/2011 9:37 AM, jun yang wrote:

what we want to do is explore the ability to move the system to the
architecture like below:
some pg---message broker(qpid)---(web front and some collect data
terminal,some business logic server,some system status monitor)
when pg start it subscribe to qpid then process the message send to it.
when pg is down,the important message is saved in message broker.

It's probably going to be a *lot* easier and more reliable to have
something sitting between Pg and the message broker. It can monitor Pg,
and unsubscribe from the broker when Pg is unavailable, then
re-subscribe when Pg becomes available again.

Putting it inside the PostgreSQL process space won't be especially easy,
because PostgreSQL doesn't support true stored procedures and doesn't
have any kind of built-in scheduler/event handler that can invoke them
without application involvement. Because of that, you'd probably have to
make significant changes to Pg's innards to make it work how you want.

There's been discussion of adding the ability for the postmaster to
start helper daemons, and if that were merged you could use a helper
started alongside the postmaster to do the work. Right now, though,
you're better off doing things how PgAgent etc do it, that is
out-of-process via a regular Pg connection.

--
Craig Ringer

Tech-related writing at http://soapyfrogs.blogspot.com/

---------- Forwarded message ----------
From: jun yang <slickqt@gmail.com>
Date: 2011/5/23
Subject: Re: [GENERAL] how to start a procedure after postgresql started.
To: Craig Ringer <craig@postnewspapers.com.au>

2011/5/23 Craig Ringer <craig@postnewspapers.com.au>:

On 23/05/2011 9:37 AM, jun yang wrote:

what we want to do is explore the ability to move the system to the
architecture like below:
some pg---message broker(qpid)---(web front and some collect data
terminal,some business logic server,some system status monitor)
when pg start it subscribe to qpid then process the message send to it.
when pg is down,the important message is saved in message broker.

It's probably going to be a *lot* easier and more reliable to have something
sitting between Pg and the message broker. It can monitor Pg, and
unsubscribe from the broker when Pg is unavailable, then re-subscribe when
Pg becomes available again.

yes,you are right,so it will be nice if  pg can start the one between
pg  and broker.

Putting it inside the PostgreSQL process space won't be especially easy,
because PostgreSQL doesn't support true stored procedures and doesn't have
any kind of built-in scheduler/event handler that can invoke them without
application involvement. Because of that, you'd probably have to make
significant changes to Pg's innards to make it work how you want.

actually, we will write the procedure in pl/python,then fork a new
thread or a new process which is easy.

There's been discussion of adding the ability for the postmaster to start
helper daemons, and if that were merged you could use a helper started
alongside the postmaster to do the work. Right now, though, you're better
off doing things how PgAgent etc do it, that is out-of-process via a regular
Pg connection.

then the one sitting between pg and borker is a helper daemon,it is
great,more info about that?
PgAgent is nice,i am just wondering why it can't be integrated in
standard pg install,cause security? functionality?
if helper daemon integrated in pg,the PgAgent can be a helper daemon too.
i'd like helper daemon can operate like windows service,you can
disable it,make it mannual start, or auto start with pg.

John R Pierce wrote:

On 05/22/11 10:45 AM, Darren Duncan wrote:

Absolutely. But if the kind of stored procedures were supported that
can do anything a database client can do, including transaction
control statements, then the main program routine would typically be
one of those.

yes, but postgres doesn't support the idea of stored procedures callable
outside of transactions, so I don't know how this could be implemented
without some major rework of the core engine.

Well, one can dream. I also anticipate that this limitation will be gone some
day. I may even help remove it some day if it comes to that. -- Darren Duncan

On 05/22/11 7:14 PM, jun yang wrote:

actually, we will write the procedure in pl/python,then fork a new
thread or a new process which is easy

it would have to be a top level process, as you can't have multiple
threads within a single postgres service connection interacting with
postgres unless you want it all to blow up in your face.

--
john r pierce N 37, W 123
santa cruz ca mid-left coast

2011/5/23 Craig Ringer <craig@postnewspapers.com.au>:

On 23/05/2011 10:13 AM, jun yang wrote:

actually, we will write the procedure in pl/python,then fork a new
thread or a new process which is easy.

Yikes. Be careful there - it's not as easy as you think it is.

Spawning a new thread within a PostgreSQL backend is a very, very, very bad
idea unless you know EXACTLY what you are doing. Do not do it if there is
any alternative.

As for spawning a new process: a PostgreSQL backend's environment isn't
guaranteed to be what you expect. I don't just mean environment variables.
The most likely surprise will be finding yourself running in quite a
limiting SELinux context if SELinux is present, but I'm sure there are more
possible quirks. Also, on unix/linux, if the backend process that invoked
your helper dies, your helper will be re-parented to init not to the
postmaster, which won't be what you expected.

thanks for the info,i am just not have such deep learn of pg internal,
i am on user level,not hacker,so the mail is in pgsql-general,not
hacker list.

There's been discussion of adding the ability for the postmaster to start
helper daemons, and if that were merged you could use a helper started
alongside the postmaster to do the work. Right now, though, you're better
off doing things how PgAgent etc do it, that is out-of-process via a
regular
Pg connection.

then the one sitting between pg and borker is a helper daemon,it is
great,more info about that?
PgAgent is nice,i am just wondering why it can't be integrated in
standard pg install,cause security? functionality?

Doing just that is sometimes discussed, and I think it'll happen eventually.
First, though, PostgreSQL's postmaster needs to be altered so that it can
start and manage helper programs and daemons. As of now, that hasn't
happened yet, or at least nobody has written a good enough patch that the
core team have been willing to accept it.

if helper daemon integrated in pg,the PgAgent can be a helper daemon too.
i'd like helper daemon can operate like windows service,you can
disable it,make it mannual start, or auto start with pg.

Your best bet at the moment is to integrate with operating system service
mechanisms. On Windows, use services. On UNIX/Linux, use the init system. On
Mac OS X, use launchd.

Part of the reason the postmaster hasn't been altered to support managing
daemons is because some people (understandably) think that that's the OS's
job, and not something PostgreSQL should duplicate.

well,from user viewpoint,i prefer that pg bundle with such
function,like extension in pg,the function default is disable.make it
easier for those who need it will be a promotion for pg.
many commercial db production include such a schedule function, not
only for making money,there is user need in practice.

In an ideal world I'd agree with them, but the current computing world is
far from ideal.  Every OS is annoyingly different in how it manages daemons,
and many init systems are painfully limited in terms of the kind of events
they can handle. Most can't even handle "If service <x> exits, do <y>".
Monitoring capabilities and the like must be individually provided by each
service if they want to be even a little bit portable.

Nonetheless, I think that's your best bet right now.

yes,it is so complicated for a common user to do such things.

On May 23, 2011, at 9:46 PM, jun yang wrote:

thanks for the info,i am just not have such deep learn of pg internal,
i am on user level,not hacker,so the mail is in pgsql-general,not
hacker list.

What you are asking to do is not a typical user function. It would be more appropriate for a "hacker list".
--
Rick Genter
rick.genter@gmail.com