Обсуждение: how to start a procedure after postgresql started.
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.
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 >
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>: > 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 >> >
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 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?
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. > >> ...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 >
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 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/
---------- 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/ >
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 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/
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. > -- > Craig Ringer > > Tech-related writing at http://soapyfrogs.blogspot.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". -- Rick Genter rick.genter@gmail.com
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
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 > >
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 >