Thread: how to start a procedure after postgresql started.

how to start a procedure after postgresql started.

From
jun yang
Date:
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.

Re: how to start a procedure after postgresql started.

From
Pavel Stehule
Date:
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

> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: how to start a procedure after postgresql started.

From
Darren Duncan
Date:
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

Re: how to start a procedure after postgresql started.

From
Scott Marlowe
Date:
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?

Re: how to start a procedure after postgresql started.

From
John R Pierce
Date:
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


Re: how to start a procedure after postgresql started.

From
jun yang
Date:
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>:
> 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
>
>> --
>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-general
>>
>

Re: how to start a procedure after postgresql started.

From
jun yang
Date:
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.

repost: unable to install PG

From
Wei
Date:
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
isnot the topic of this article: http://1stopit.blogspot.com/2011/01/postgresql-83-and-84-fails-to-install.html  

How to resolve this problem?

Re: how to start a procedure after postgresql started.

From
Scott Marlowe
Date:
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.

Re: how to start a procedure after postgresql started.

From
Darren Duncan
Date:
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

Re: how to start a procedure after postgresql started.

From
jun yang
Date:
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.

>
>> ...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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: how to start a procedure after postgresql started.

From
John R Pierce
Date:
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


Re: how to start a procedure after postgresql started.

From
jun yang
Date:
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.

Re: repost: unable to install PG

From
Craig Ringer
Date:
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
itis 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/

Fwd: how to start a procedure after postgresql started.

From
jun yang
Date:
---------- 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.
> --
> Craig Ringer
>
> Tech-related writing at http://soapyfrogs.blogspot.com/
>

Re: how to start a procedure after postgresql started.

From
Darren Duncan
Date:
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

Re: how to start a procedure after postgresql started.

From
Craig Ringer
Date:
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/

Re: Fwd: how to start a procedure after postgresql started.

From
John R Pierce
Date:
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


Re: how to start a procedure after postgresql started.

From
jun yang
Date:
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.
> --
> Craig Ringer
>
> Tech-related writing at http://soapyfrogs.blogspot.com/
>

Re: how to start a procedure after postgresql started.

From
Rick Genter
Date:
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


Re: how to start a procedure after postgresql started.

From
Craig Ringer
Date:
On 24/05/11 12:46, 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.

Then you really, really, REALLY don't want to start a thread within the
backend, and should avoid spawning processes from backends too. To get
either approach right will require a much deeper understanding of how Pg
works.

>> 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.

Yep, I think it'd be nice. Nobody has volunteered to write such a
feature yet, though, and nobody is stepping up to pay someone else to
write it. Or at least any efforts so far haven't reached
production-quality committable code.

The downside of working with an open source database is that there's no
incentive to write marketing-checkbox features. Someone has to actually
want to put in the time and effort to implement it, usually because they
want to use it.

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

... which is why the VAST majority of people achieve what they need
using a separate daemon or just integrate this sort of functionality
into their middleware. Neither option is difficult to do.

What you want to do - integrate your app directly and completely into
the database - is not something that a common user typically wants to do
in the first place.

It's more common for people who want to hide the database behind a
messaging system to instead write a program that accepts messages and
embed a database like Berkeley DB, SQLite or Firebird directly into
their program, rather than the other way around. PostgreSQL cannot be
embedded that way, it's not designed for that kind of use.

--
Craig Ringer

Re: how to start a procedure after postgresql started.

From
jun yang
Date:
2011/5/24 Rick Genter <rick.genter@gmail.com>:
>
> 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".
i am just thinking pg as a special os,it should can be scripted when
some event happen. so much like a program has scripting function.
> --
> Rick Genter
> rick.genter@gmail.com
>
>

Re: how to start a procedure after postgresql started.

From
jun yang
Date:
2011/5/24 Craig Ringer <craig@postnewspapers.com.au>:
> On 24/05/11 12:46, 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.
>
> Then you really, really, REALLY don't want to start a thread within the
> backend, and should avoid spawning processes from backends too. To get
> either approach right will require a much deeper understanding of how Pg
> works.
>
thanks for your warnning again.

>>> 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.
>
> Yep, I think it'd be nice. Nobody has volunteered to write such a
> feature yet, though, and nobody is stepping up to pay someone else to
> write it. Or at least any efforts so far haven't reached
> production-quality committable code.
>
even a basic pim program has buildin schedule function,so it should
not be so hard for a big software like pg.
the important of buildin schedule function is that the whole schedule
is in pg ,you can dump and restore database,that's all,no need to
examine all external things run ok.
> The downside of working with an open source database is that there's no
> incentive to write marketing-checkbox features. Someone has to actually
> want to put in the time and effort to implement it, usually because they
> want to use it.
>
haha,it seems i demand too much and can't put in the time and effort.
i am terribly sorry about that.

>> yes,it is so complicated for a common user to do such things.
>
> ... which is why the VAST majority of people achieve what they need
> using a separate daemon or just integrate this sort of functionality
> into their middleware. Neither option is difficult to do.
>
> What you want to do - integrate your app directly and completely into
> the database - is not something that a common user typically wants to do
> in the first place.
>
yes,it is like hacking pg.

> It's more common for people who want to hide the database behind a
> messaging system to instead write a program that accepts messages and
> embed a database like Berkeley DB, SQLite or Firebird directly into
> their program, rather than the other way around. PostgreSQL cannot be
> embedded that way, it's not designed for that kind of use.
>
i found pg_amqp,some thing like what i want to do,though by write a pg
module in c,if pg support write pg module by it's procedure
language,there would be more people can hack pg.
> --
> Craig Ringer
>