Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? See also: http://www.postgresql.org/docs/current/static/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
2010/3/1 Robert Haas robertmh...@gmail.com: On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? I thing so it is very hard restriction based on using and architecture of our SPI interface. Our stored procedures are executed inside one SELECT statement - it is reason for limit. There cannot be two or more outer transactions. Different implementations has different place of runtime - it is more near to top of pipeline. Pavel See also: http://www.postgresql.org/docs/current/static/plpgsql-porting.html#PLPGSQL-PORTING-EXCEPTIONS ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Mon, Mar 1, 2010 at 4:43 PM, Robert Haas robertmh...@gmail.com wrote: On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? yeah...that's what I meant. plpgsql exceptions are no help because there are many cases where you simply don't want the whole sequence of operations to run in a single transaction. loading lots of data to many tables is one. any operation that depends on transaction commit to do something (like notifications) and then hook on the results is another. you always have the heavy hitting administrative functions like vacuum, etc. another case is if you want a procedure to simply run forever...trivially done in a procedure, impossible in a function. The way people do this stuff now is to involve an 1) external scheduler such as cron and 2) .sql scripts for relatively simple things and/or a external scripting language like bash/perl. The external scheduler has a couple of annoying issues...completely not portable to code against and scheduling sub minute accuracy is a big headache. Also, adjusting the scheduling based on database events is, while not impossible, more difficult than it should be. External .sql scripts are portable but extremely limited. Involving something like perl just so I can jump outside the database to do manual transaction management is fine but ISTM these type of things are much better when done inside the database IMNSHO. Another factor here is that a sizable percentage of our user base is bargain hunters coming in from other systems like oracle and ms sql and having to rely in o/s scheduler is very distasteful to them. It's a hole, one of the last remaining IMO, in postgres being able to provide a complete server side development environment without having to deal with the o/s at all. I stand by my statements earlier. Any moderate level and up complexity database has all kinds of scheduling and scripting going on supporting it. These things really should be part of the database, dump with it, and run in a regular way irregardless of platform and server environment etc. With that, 90% of the code I have to write outside of the database goes away. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Robert Haas robertmh...@gmail.com writes: On Sat, Feb 20, 2010 at 4:41 PM, Merlin Moncure mmonc...@gmail.com wrote: IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. Did you mean that you can't manage the transaction state yourself? Has anyone given any thought to what would be required to relax this restriction? Is this totally impossible given our architecture, or just a lack of round tuits? There is lots and lots of discussion of that in the archives. It's fundamentally impossible for PL functions done in the current style to start or commit transactions, unless you resort to dblink-style kluges. What's been discussed is some sort of structure that would allow a chunk of PL code to execute outside a transaction and thus issue its own begin and commit commands. This idea is what Merlin is calling a stored procedure, though personally I dislike that terminology. Anyway, nothing's got past the arm-waving stage as yet. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Tue, Feb 23, 2010 at 11:08 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Steve Atkins wrote: Would having a higher level process manager be adequate - one that spawns the postmaster and a list of associated processes (queue manager, job scheduler, random user daemons that are used for database application maintenance). It sounds like something like that would be able to start up and shut down an entire family of daemons, of which the postmaster is the major one, gracefully. Sort of a super-pg_ctl, eh? Hmm, that sounds like it could work ... Summarizing: so we want some kind of super postmaster that starts some processes (including the postgres' postmaster itself), and track their availability. - processes that doesn't need to connect to shared memory should start here (ie: pgagent, slony daemons, pgbouncer, LISTEN applications, etc) - processes that need to connect to shared memory should be childs of postgres' postmaster is this so different from what the postgres' postmaster itself does? i mean, can we reuse that code? this project of course growth beyond my known abilities, so while i will try it (if anyone seems like he can takle it please go for it)... maybe we can add this to the TODO if seems acceptable? specially, i'd love to hear Tom's opinion on this one... -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Tom Lane t...@sss.pgh.pa.us writes: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. I was under the illusion that having a separate supervisor process child of postmaster to care about the user daemons would protect postmaster itself. At least the only thing it'd have to do is start a new child. Then let it care. How much that would give us as far as postmaster reliability is concerned? -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. I was under the illusion that having a separate supervisor process child of postmaster to care about the user daemons would protect postmaster itself. At least the only thing it'd have to do is start a new child. Then let it care. The problem I have with this design is that those processes are then not direct children of postmaster itself, which is a problem when it wants them to stop and such. (This is why autovacuum workers are started by postmaster and not by the launcher directly. If I knew of a way to make it work reliably, I wouldn't have bothered with that signalling mechanism, which is quite fragile and gets its fair share of bug reports.) (Hmm, but then, autovacuum workers are backends and so they need to be more closely linked to postmaster. These other processes needn't be.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Tue, 2010-02-23 at 00:02 -0500, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. Salt and pepper allowed but that's about it -- more complex ingredients are out of the question due to added code to postmaster, which we want to be as robust as possible and thus not able to cook much of anything else. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. There are some things I'm willing to accept extra postmaster complexity for, but I say again that not one single one of the arguments made in this thread are convincing reasons to take that risk. Nobody wants to weigh down and sink the postmaster. What is wanted is a means to integrate parts of a solution that are already intimately tied to Postgres. Non-integration makes the whole Postgres-based solution less reliable and harder to operate. Postgres should not assume that it is the only aspect of the server: in almost all other DBMS features are built into the database: session pools, trigger-based replication, scheduling, etc.. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Simon Riggs wrote: What is wanted is a means to integrate parts of a solution that are already intimately tied to Postgres. Non-integration makes the whole Postgres-based solution less reliable and harder to operate. Postgres should not assume that it is the only aspect of the server: in almost all other DBMS features are built into the database: session pools, trigger-based replication, scheduling, etc.. Yeah, back when autovac wasn't integrated, it was a pain to work with -- the need to start and stop it separately from postmaster was a hard task to manage. The Debian init script used to have some very ugly hacks to work with it. Having it now integrated makes thing *so* much easier. Giving postmaster the ability to manage other processes (whether directly or through a supervisor) would make people lives simpler as well. I think it was Dimitri who said that even if postmaster is running but the connection pooler is down, the system is effectively down for some users, and thus you really want postmaster to be able to do something about it. I cannot agree more. (You can set up monitoring and such, but this is merely working around the fact that it doesn't work in the first place.) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Feb 22, 2010, at 9:02 PM, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. Salt and pepper allowed but that's about it -- more complex ingredients are out of the question due to added code to postmaster, which we want to be as robust as possible and thus not able to cook much of anything else. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. There are some things I'm willing to accept extra postmaster complexity for, but I say again that not one single one of the arguments made in this thread are convincing reasons to take that risk. Would having a higher level process manager be adequate - one that spawns the postmaster and a list of associated processes (queue manager, job scheduler, random user daemons that are used for database application maintenance). It sounds like something like that would be able to start up and shut down an entire family of daemons, of which the postmaster is the major one, gracefully. It could also be developed almost independently of core code, at most it might benefit from a way for the postmaster to tell it when it's started up successfully. Cheers, Steve -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Steve Atkins wrote: Would having a higher level process manager be adequate - one that spawns the postmaster and a list of associated processes (queue manager, job scheduler, random user daemons that are used for database application maintenance). It sounds like something like that would be able to start up and shut down an entire family of daemons, of which the postmaster is the major one, gracefully. Sort of a super-pg_ctl, eh? Hmm, that sounds like it could work ... It could also be developed almost independently of core code, at most it might benefit from a way for the postmaster to tell it when it's started up successfully. Right -- pg_ping pops up again ... I think it'd also want to be signalled when postmaster undergoes a restart cycle, so that it can handle the other daemons appropriately. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, 2010-02-21 at 20:46 +0100, Dimitri Fontaine wrote: Simon Riggs si...@2ndquadrant.com writes: There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. Please do include the Skytools / PGQ ticker as one use case in the design discussion, and pgbouncer too. Having user daemons as part as the PostgreSQL extensibility would be awesome indeed! Bonus point if you build them with PGXS and install them from SQL, so that the current extension packaging design applies. I guess we can say that the archive and restore command are precursors of managed user daemons, or say, integrated processes. So adding them to the use cases to cover would make sense. Yes, I think so. Rough design... integrated_user_processes = 'x, y, z' would run x(), y() and z() in their own processes. These would execute after startup, or at consistent point in recovery. The code for these would come from preload_libraries etc. They would not block smart shutdown, though their shudown sequence might delay it. User code would be executed last at startup and first thing at shutdown. API would be user_process_startup(), user_process_shutdown(). -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 1:11 PM, Simon Riggs si...@2ndquadrant.com wrote: [...] Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, [...] There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. ah! that could get rid of one of my complaints, and then i could just work the rest in pgAgent... so, is this idea (having some user processes be tied to postmaster start/stop) going to somewhere? it also could help if we you have processes LISTENing for NOTIFYs -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Jaime Casanova wrote: On Sun, Feb 21, 2010 at 1:11 PM, Simon Riggs si...@2ndquadrant.com wrote: There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. ah! that could get rid of one of my complaints, and then i could just work the rest in pgAgent... Yeah, seems like a good idea. Slon daemon and similar daemons could also use it. so, is this idea (having some user processes be tied to postmaster start/stop) going to somewhere? I've added this to the TODO list. Now we just need someone to write it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
2010/2/22 Heikki Linnakangas heikki.linnakan...@enterprisedb.com: Jaime Casanova wrote: On Sun, Feb 21, 2010 at 1:11 PM, Simon Riggs si...@2ndquadrant.com wrote: There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. ah! that could get rid of one of my complaints, and then i could just work the rest in pgAgent... Yeah, seems like a good idea. Slon daemon and similar daemons could also use it. I like it. I thought about some workflow system integrated with scheduler. Regards Pavel so, is this idea (having some user processes be tied to postmaster start/stop) going to somewhere? I've added this to the TODO list. Now we just need someone to write it. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Mon, Feb 22, 2010 at 1:18 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Jaime Casanova wrote: so, is this idea (having some user processes be tied to postmaster start/stop) going to somewhere? I've added this to the TODO list. Now we just need someone to write it. if we can do this, how should it work? Simon said: Yes, I think so. Rough design... integrated_user_processes = 'x, y, z' would run x(), y() and z() in their own processes. These would execute after startup, or at consistent point in recovery. The code for these would come from preload_libraries etc. They would not block smart shutdown, though their shudown sequence might delay it. User code would be executed last at startup and first thing at shutdown. API would be user_process_startup(), user_process_shutdown(). so it should be a GUC, that is settable only at start time. we need those integrated processes at all when in a standby server? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 8:06 PM, Joshua D. Drake j...@commandprompt.com wrote: That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. +1! Aa scheduler is an extremely common thing to have to integrate with the database. All of our commercial competitors have them, and they are heavily used. Like I noted above, what people want to schedule is going to be stored procedures. Having both would virtually eliminate the need for scripting outside the database, which is a pretty big deal since external scripts are a real pain to keep cross platform. Since there's probably a lot of overlapping problems in those two features, why not tackle both at once? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Jaime Casanova wrote: if we can do this, how should it work? Simon said: Yes, I think so. Rough design... integrated_user_processes = 'x, y, z' would run x(), y() and z() in their own processes. These would execute after startup, or at consistent point in recovery. The code for these would come from preload_libraries etc. They would not block smart shutdown, though their shudown sequence might delay it. User code would be executed last at startup and first thing at shutdown. API would be user_process_startup(), user_process_shutdown(). so it should be a GUC, that is settable only at start time. A GUC like that was my first thought too. We've already come up with many uses for it, so whatever the interface is, will need to make sure it's flexible enough to cater for all the use cases. we need those integrated processes at all when in a standby server? Yes. You might want to run e.g. scheduled reports from a standby reporting server, launched by a scheduler process. Or backups. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Mon, Feb 22, 2010 at 1:50 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: we need those integrated processes at all when in a standby server? Yes. You might want to run e.g. scheduled reports from a standby reporting server, launched by a scheduler process. Or backups. ah! fair enough! -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Jaime Casanova jcasa...@systemguards.com.ec writes: heikki.linnakan...@enterprisedb.com wrote: API would be user_process_startup(), user_process_shutdown(). so it should be a GUC, that is settable only at start time. we need those integrated processes at all when in a standby server? This seems like a solution in search of a problem to me. The most salient aspect of such processes is that they would necessarily run as the postgres user, which means that you could never run any untrusted code in them. That cuts the space of user problems they could solve way down. I still haven't seen a good reason for not using cron or Task Scheduler or other standard tools. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Mon, Feb 22, 2010 at 2:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: I still haven't seen a good reason for not using cron or Task Scheduler or other standard tools. - marketing? don't you hate when people say: Oracle has it!? - user dumbness: they forgot to start daemons they need (yes, i have seen that) or they simply don't know about them... it's amazing the amount of people how ask me just after i tell them to use cron or the windows task scheduler: and how i use that? Yes, in Latin America are still very primitive... we use only those things that are very very easy ;) the ability to have processes that start when postmaster starts and stop when postmaster stops is just one more way to be extensible without integrating every piece of code into core -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Mon, Feb 22, 2010 at 3:08 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Mon, Feb 22, 2010 at 2:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: I still haven't seen a good reason for not using cron or Task Scheduler or other standard tools. - marketing? don't you hate when people say: Oracle has it!? just before someone insult me... this comment was thought about the in-core scheduler, something we can live without with this and still doesn't have to hear that -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Mon, Feb 22, 2010 at 2:29 PM, Alvaro Herrera alvhe...@commandprompt.com wrote: Merlin Moncure escribió: Like I noted above, what people want to schedule is going to be stored procedures. Having both would virtually eliminate the need for scripting outside the database, which is a pretty big deal since external scripts are a real pain to keep cross platform. Since there's probably a lot of overlapping problems in those two features, why not tackle both at once? Divide and conquer? When I meant 'tackle', it is more of a 'come to an understanding' thing. Normally I would agree with you anyways, but I think what most people would want to schedule would be stored procedures (sorry to continually repeat myself here, but I really think this should be critical to any scheduling proposal), not functions or ad hoc scripts. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Tom Lane t...@sss.pgh.pa.us writes: This seems like a solution in search of a problem to me. The most salient aspect of such processes is that they would necessarily run as the postgres user I happen to run my PGQ tickers and londiste daemons as londiste user and make it a superuser (at least while installing, as they need to install some PL/C stuff). Then there's pgbouncer too, which I always run as postgres system user, if only to be able to open a socket in the same directory where postgres opens them (/var/run/postgresql on my system). The precedent are archive and restore command. They do run as postgres user too, don't they? I think we could have made walreceiver and walsender some generic out-of-core facilities too, within this model. The other common use case is to schedule maintenance (vacuum, cluster some table, maintain a materialized view, backup), all of which can be run as postgres user too, only adaptation could be to have a security definer function. So, out of the only scheduler use case, if you want to see some C code that I'd like to be able to run as a postmaster's child, have a look at pgqd, the next skytools version ticker daemon, here: http://github.com/markokr/skytools-dev/blob/master/sql/ticker/pgqd.c http://github.com/markokr/skytools-dev/blob/master/sql/ticker/ticker.c You'll see mainly a C daemon which connects to some database and calls stored procedures there. There could be separate schedules in fact, the main loop for ticking the snapshots, another one for managing the retry event queue, and yet another one for managing the maintenance operations. What I think I'd like to have is a user process supervisor as a postmaster child, its job would be to start and stop the user processes at the right time frames, and notice their death. A restart policy should be attached to each child, which is either permanent, transient or temporary. To avoid infinitely restarting a process, the supervisor has 2 GUCs, supervisor_max_restarts in supervisor_max_time. Being unable to manage a user permanent child process (worker) would trigger a postmaster stop. All of this is heavily inspired by the Erlang approach, which I've found simple and effective: http://erlang.org/doc/man/supervisor.html The supervised processes will have to offer a main entry point, which will get called once the supervisor has forked, in the child process, and must be prepared to receive SIGHUP, SIGINT and SIGTERM signals. The setup will get done with the existing custom_variable_classes, and more generally I guess we're reusing the PGXS and custom .so infrastructure (shared_preload_libraries). The main good reason to have this is to allow extension authors to develop autonomous daemon in a portable way, benefiting from all those efforts PostgreSQL made to have a fork() model available on windows et al. I guess we need a way to start the same supervised daemon extension code more than once too, for example several pgbouncer setups on different ports in different pooling modes. I still haven't seen a good reason for not using cron or Task Scheduler or other standard tools. We're past the scheduler alone. You won't turn archive_command, restore_command, walsender, walreceiver, pgbouncer or PGQ as a cron job, but you could have them managed by the postmaster, as plugins. Your good reason would be less code to keep an eye on :) Back to the scheduling, you can backup the maintenance schedule with the database itself. If all they do is call some function, which in my case the only exception is pg_dump, then you don't need to re-validate then when you upgrade your OS, or migrate from CentOS to debian or from developer station running windows to production server running some Unix variant. Once more, nothing you couldn't implement already. Maybe PostgreSQL is growing fast enough that now is the time to look at how to enable non core things to be easily shipped with the core product? Do we need a PostgreSQL distribution? I know David Wheeler's opinion on that, and think PGAN + pg_restore friendly extensions + supervised helper daemons will be huge enablers. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Mon, Feb 22, 2010 at 2:53 PM, Tom Lane t...@sss.pgh.pa.us wrote: I still haven't seen a good reason for not using cron or Task Scheduler or other standard tools. *) provided and popular feature in higher end databases *) the audience you cater to expects it *) IMO, it should simply not be necessary to incorporate a secondary scripting environment to do things like vacuum and backups *) portable. for example, you can dump a database on linux and restore to windows without re-implementing your scheduler/scripts as a consequence, *) off the shelf utilities/pgfoundry projects, etc can rely and utilize scheduling behavior merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Dimitri Fontaine dfonta...@hi-media.com writes: Tom Lane t...@sss.pgh.pa.us writes: This seems like a solution in search of a problem to me. The most salient aspect of such processes is that they would necessarily run as the postgres user The precedent are archive and restore command. They do run as postgres user too, don't they? Well, yeah, but you *must* trust those commands because every last bit of your database content passes through their hands. That is not an argument why you need to trust a scheduling facility --- much less the tasks it schedules. I still say that every use case so far presented here would be equally if not better served outside the database. Putting it inside just creates more failure scenarios and security risks. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Tom Lane t...@sss.pgh.pa.us writes: Well, yeah, but you *must* trust those commands because every last bit of your database content passes through their hands. That is not an argument why you need to trust a scheduling facility --- much less the tasks it schedules. It seems to me that CREATE FUNCTION maintenance.foo() ... SECURITY DEFINER means that I can schedule tasks that will not run a superuser. On the reliability, see above. I still say that every use case so far presented here would be equally if not better served outside the database. Putting it inside just creates more failure scenarios and security risks. I can understand why you say that, but I'll have to disagree. The fact that the database server is still available when pgbouncer crashes, for example, still means that none of my applications are able to connect. When the current PGQ (or slony) code crashes, it's already C loaded code that crashes, and it already takes PostgreSQL down with it. I'm not the security oriented paranoid^W guy, so I won't ever try to argue about that world, and not with you. All in all, when the daemons I'm considering running as user processes do crash, the fact that PostgreSQL is still alive means nothing for me. Have its supervisor trigger a fast shutdown and restart sounds way more reliable from here, the alternative being some alerting system wakes me up and I get to restart the failed services while my application is not available, but PostgreSQL is (but for no one). Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Merlin Moncure escribió: Like I noted above, what people want to schedule is going to be stored procedures. Having both would virtually eliminate the need for scripting outside the database, which is a pretty big deal since external scripts are a real pain to keep cross platform. Since there's probably a lot of overlapping problems in those two features, why not tackle both at once? Divide and conquer? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Feb 22, 2010, at 5:22 PM, Jaime Casanova wrote: On Mon, Feb 22, 2010 at 4:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Tom Lane t...@sss.pgh.pa.us writes: This seems like a solution in search of a problem to me. The most salient aspect of such processes is that they would necessarily run as the postgres user The precedent are archive and restore command. They do run as postgres user too, don't they? Well, yeah, but you *must* trust those commands because every last bit of your database content passes through their hands. That is not an argument why you need to trust a scheduling facility --- much less the tasks it schedules. Ok, let's forget the scheduler for a minute... this is not about that anymore, is about having the ability to launch user processes when the postmaster is ready to accept connections, this could be used for launching an scheduler but also for launching other tools (ie: pgbouncer, slon daemons, etc) Just a few questions off the top of my head: What are the semantics? If you launch a process and it crashes, is the postmaster responsible for relaunching it? Is there any additional monitoring of that process it would be expected to do? What defined hooks/events would you want to launch these processes from? If you have to kill a backend postmaster, do the auxiliary processes get killed as well, and with what signal? Are they killed when you stop the postmaster, and are they guaranteed to have stopped at this point? Can failing to stop prevent/delay the shutdown/restart of the server? Etc. Regards, David -- David Christensen End Point Corporation da...@endpoint.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
On Mon, Feb 22, 2010 at 4:37 PM, Tom Lane t...@sss.pgh.pa.us wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Tom Lane t...@sss.pgh.pa.us writes: This seems like a solution in search of a problem to me. The most salient aspect of such processes is that they would necessarily run as the postgres user The precedent are archive and restore command. They do run as postgres user too, don't they? Well, yeah, but you *must* trust those commands because every last bit of your database content passes through their hands. That is not an argument why you need to trust a scheduling facility --- much less the tasks it schedules. Ok, let's forget the scheduler for a minute... this is not about that anymore, is about having the ability to launch user processes when the postmaster is ready to accept connections, this could be used for launching an scheduler but also for launching other tools (ie: pgbouncer, slon daemons, etc) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
David Christensen wrote: What are the semantics? If you launch a process and it crashes, is the postmaster responsible for relaunching it? Is there any additional monitoring of that process it would be expected to do? What defined hooks/events would you want to launch these processes from? If you have to kill a backend postmaster, do the auxiliary processes get killed as well, and with what signal? Are they killed when you stop the postmaster, and are they guaranteed to have stopped at this point? Can failing to stop prevent/delay the shutdown/restart of the server? Etc. I think most of these should be defined by the called process, i.e. there needs to be a way to pass flags to postmaster. For example, some processes will need to cause a full postmaster restart, while most will not. For those that do, we need some robustness check; for example we could require that they participate in the PMChildSlot mechanism. Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. Salt and pepper allowed but that's about it -- more complex ingredients are out of the question due to added code to postmaster, which we want to be as robust as possible and thus not able to cook much of anything else. Now, if you run a postmaster with such a thing attached, you get no support here on crash reports unless you can prove the crash can be reproduced with it turned off (i.e. taint mode). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] tie user processes to postmaster was:(Re: [HACKERS] scheduler in core)
Alvaro Herrera alvhe...@commandprompt.com writes: Regarding hooks or events, I think postmaster should be kept simple: launch at start, reset at crash recovery, kill at stop. Salt and pepper allowed but that's about it -- more complex ingredients are out of the question due to added code to postmaster, which we want to be as robust as possible and thus not able to cook much of anything else. This is exactly why I think the whole proposal is a nonstarter. It is necessarily pushing more complexity into the postmaster, which means an overall reduction in system reliability. There are some things I'm willing to accept extra postmaster complexity for, but I say again that not one single one of the arguments made in this thread are convincing reasons to take that risk. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Joshua D. Drake j...@commandprompt.com writes: On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is zero technical reason for this to be in core. That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. Maybe what we need, on the technical level, is a way to distribute this code with the main product but without draining too much effort from core members there. Like we do with contribs I guess, but on a larger scale. I guess git submodules, PGAN, extensions and all that jazz are going to help. Meanwhile I'll have to learn enough of pgAgent to figure out how much it's tied to pgadmin, and we'll have to make those other facilities something real. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Greg Stark gsst...@mit.edu writes: It'll always be another (set of) processes even if it's in core. All it means to be in core is that it will be harder to make modifications and you'll be tied to the Postgres release cycle. Another set of processes all right, but that postmaster is responsible of, that it starts and ends at the right time. Main advantage over cron or another scheduler being that it'd be part of my transactional backups, of course. All you need for that is to store the schedule in a database table. This has nothing to do with where the scheduler code lives. Not true. You need custom scripts that will read what's in this database table and run it at the right timing, care about running more than one job at the same time when necessary, reports what the outcome was somewhere, etc. The simplest would be a query that writes out in cron format the setup you've made in the database, I suppose. When do you run that query? You need an untrusted trigger? What happens if your query or script writes a file cron will not be able to read, or on a server where cron is not running? I'm not saying this is any harder that other admin sys stuff we have to do to operate the systems, just that it seems it would be simpler, easier and less error prone to be able to schedule database maintenance from within the database itself, in such a way that the classic dump and restore process restores the maintenance scripts too. That would allow for automatic creation of partitions in dev and pre-prod environments where you install more than one copy of the same database at once, but would like to avoid maintaining one set of cron entries per copy. As said Tom, technically, it's obviously possible not to depend on a PostgreSQL integrated scheduler. As said JD, it still is a pretty good idea to provide one in core. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Pavel Stehule wrote: This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. What I remember - this is exactly same discus like was about replication thre years ago fiirst strategy - we doesn't need it in core next we was last with replacation That's a pretty poor analogy IMNSHO. There are very good technical reasons to have replication in the core. That is much less clear for a scheduler. But in any case, I didn't say that we shouldn't have a scheduler. I specifically said there might be a case for it - read the first clause of my last sentence. What I said was that the reason given, namely that Corporations didn't want to use add-on modules, was not a good reason. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Pavel Stehule wrote: 2010/2/21 Andrew Dunstan and...@dunslane.net: ? ?I believe that in core may be installed by default in case of ? ?the pgAgent or similar solution... ? ?Many big companies does not allow the developers to configure and ? ?install components we need to request everthing in 10 copies ? ?of forms... ? ?By making it in core or installed by default means that we ? ?have more chance that the db scheduler would be widely accepted... This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. ?The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. What I remember - this is exactly same discus like was about replication thre years ago fiirst strategy - we doesn't need it in core next we was last with replacation We resisted putting replication into the core until we needed some facilities that were only available from the core. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com PG East: http://www.enterprisedb.com/community/nav-pg-east-2010.do + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
2010/2/20 Andrew Dunstan and...@dunslane.net We're not going to change that because some companies have insane corporate policies. I agree, Andrew... This is an outside benefit... not a reason or justification... I believe that a general purpose scheduler is similar to the autovacuum... it is not really needed, we can always configure an external scheduler. But I liked a LOT... For me is not a question of must be in core is a question of cost/benefit. I do not see much cost, but a lot of benefits: Like Joshua said abstract away from external solutions and operating system dependencies. Like Dimitri said Main advantage over cron or another scheduler being that it'd be part of my transactional backups. To me is the reliability of having the partition creation/removal being part of the database, be able of make consolidations, cleanups and periodic consistency checks and diagnostics without external dependencies. I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? - - Lucas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Lucas wrote: I believe that in core may be installed by default in case of Those seem like totally orthogonal concepts to me. A feature may be in core but not installed by default (like many PLs). A feature might not be in core but installed by many installers (say postgis). It seems like half the people here are arguing for the former concept. It seems the other half are arguing against the latter concept. Is the real need here for a convenient way to enable and/or recommend packagers to install some non-core modules by default? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Ron Mayer rm...@cheapcomplexdevices.com writes: Is the real need here for a convenient way to enable and/or recommend packagers to install some non-core modules by default? It would certainly help us resist assorted requests to put everything including the kitchen sink into core. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 12:33 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ron Mayer rm...@cheapcomplexdevices.com writes: Is the real need here for a convenient way to enable and/or recommend packagers to install some non-core modules by default? It would certainly help us resist assorted requests to put everything including the kitchen sink into core. If you don't want people to keep requesting more features in core, you should stop doing such a good job making the functionality that gets put into core awesome. That's partly tongue-in-cheek, but there's some real truth to it. Stuff doesn't go into core unless it just works. And having things in core is appealing because it means they're available everywhere, they work the same way everywhere, and they can be fully managed within the database without a lot of futzing around. Having an extensible system is a good thing and I'm glad we do, but having a rich feature set available in core is also a very good thing for a lot of reasons, at least IMHO. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 10:17 AM, Lucas luca...@gmail.com wrote: I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? The real genius of autovacuum is that it works out when there has been enough activity in particular tables that they need to be vacuumed. We might be able to use an in-core scheduler to wake it up every minute to look at the stats, or whatever it is that we do, but that's not all that exciting. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Robert Haas robertmh...@gmail.com writes: On Sun, Feb 21, 2010 at 10:17 AM, Lucas luca...@gmail.com wrote: I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? The real genius of autovacuum is that it works out when there has been enough activity in particular tables that they need to be vacuumed. We might be able to use an in-core scheduler to wake it up every minute to look at the stats, or whatever it is that we do, but that's not all that exciting. The wake-up-every-N-seconds part of it is actually the weakest part (search the archives for questions about autovacuum_naptime). To my mind, the killer reason why autovac needed to be integrated is so that the system itself could trigger autovac runs in response to threatened XID wraparound conditions. A facility for scheduling user jobs, almost by definition, won't have any system-internal trigger conditions. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 1:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Feb 21, 2010 at 10:17 AM, Lucas luca...@gmail.com wrote: I wonder if the scheduler already existed before the implementation of the autovacuum, its implementation would not be a function executed by the in-core scheduler? The real genius of autovacuum is that it works out when there has been enough activity in particular tables that they need to be vacuumed. We might be able to use an in-core scheduler to wake it up every minute to look at the stats, or whatever it is that we do, but that's not all that exciting. The wake-up-every-N-seconds part of it is actually the weakest part (search the archives for questions about autovacuum_naptime). To my mind, the killer reason why autovac needed to be integrated is so that the system itself could trigger autovac runs in response to threatened XID wraparound conditions. A facility for scheduling user jobs, almost by definition, won't have any system-internal trigger conditions. Right. Without prejudice to my earlier statements that I think this might possibly be a good thing to do anyway, the case for it would be a lot stronger if it provided some genuine additional functionality. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 1:11 PM, Simon Riggs si...@2ndquadrant.com wrote: On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. Good point. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. That might be a little overly optimistic, but I get the point. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Simon Riggs si...@2ndquadrant.com writes: There is currently no way to run a separate daemon process that runs user code as part of Postgres, so that the startup code gets run immediately we startup, re-run if we crash and shut down cleanly when the server does. If there were some way to run arbitrary code in a daemon using an extensibility API then we wouldn't ever get any requests for the scheduler, cos you could write it yourself without troubling anybody here. Please do include the Skytools / PGQ ticker as one use case in the design discussion, and pgbouncer too. Having user daemons as part as the PostgreSQL extensibility would be awesome indeed! Bonus point if you build them with PGXS and install them from SQL, so that the current extension packaging design applies. I guess we can say that the archive and restore command are precursors of managed user daemons, or say, integrated processes. So adding them to the use cases to cover would make sense. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] scheduler in core
Hi, I'm trying to figure out how difficult is this What we need: - a shared catalog - an API for filling the catalog - a scheduler daemon - pg_dump support A shared catalog - Why shared? obviously because we don't want to scan all database's pg_job every time the daemon wake up. Maybe something like: pg_job ( oid-- use the oid as pk jobname jobdatoid -- job database oid jobowner -- for permission's checking jobstarttime -- year to minute jobfrequency -- an interval? jobnexttime or joblasttime jobtype -- if we are going to allow plain sql or executable/shell job types jobexecute or jobscript ) comments about the catalog? An API for filling the catalog - do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to create/remove jobs. An scheduler daemon I think we can use 8.3's autovacuum daemon as a reference for this... AFAIK, it's a child of postmaster that sleep for $naptime and then looks for something to do (it also looks in a catalog) and the send a worker to do it that's what we need to do but... for the $naptime i think we can autoconfigure it, when we execute a job look for the next job in queue and sleep until we are going to reach the time to execute it i don't think we need a max_worker parameter, it should launch as many workers as it needs pg_dump support -- dump every entry of the pg_job catalog as a CREATE JOB SQL statement or a create_job() function depending on what we decided ideas? comments? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 4:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this What we need: - a shared catalog - an API for filling the catalog - a scheduler daemon - pg_dump support A shared catalog - Why shared? obviously because we don't want to scan all database's pg_job every time the daemon wake up. Maybe something like: pg_job ( oid -- use the oid as pk jobname jobdatoid -- job database oid jobowner -- for permission's checking jobstarttime -- year to minute jobfrequency -- an interval? jobnexttime or joblasttime jobtype -- if we are going to allow plain sql or executable/shell job types jobexecute or jobscript ) comments about the catalog? An API for filling the catalog - do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to create/remove jobs. An scheduler daemon I think we can use 8.3's autovacuum daemon as a reference for this... AFAIK, it's a child of postmaster that sleep for $naptime and then looks for something to do (it also looks in a catalog) and the send a worker to do it that's what we need to do but... for the $naptime i think we can autoconfigure it, when we execute a job look for the next job in queue and sleep until we are going to reach the time to execute it i don't think we need a max_worker parameter, it should launch as many workers as it needs pg_dump support -- dump every entry of the pg_job catalog as a CREATE JOB SQL statement or a create_job() function depending on what we decided ideas? comments? IMNSHO, an 'in core' scheduler would be useful. however, I think before you tackle a scheduler, we need proper stored procedures. Our existing functions don't cut it because you can manage the transaction state yourself. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. Main advantage over cron or another scheduler being that it'd be part of my transactional backups, of course. Use cases, in case it's needed already, include creating new partitions, materializing views at known intervals, more general maintenance like vacuum and clusters operations, some reporting that could be done in the database itself, etc. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
pg_job ( oid -- use the oid as pk jobname jobdatoid -- job database oid jobowner -- for permission's checking jobstarttime -- year to minute jobfrequency -- an interval? jobnexttime or joblasttime jobtype -- if we are going to allow plain sql or executable/shell job types jobexecute or jobscript ) comments about the catalog? + success_action +failure_action An API for filling the catalog - do we want a CREATE JOB SQL synatx? FWIW, Oracle uses functions to create/remove jobs. An scheduler daemon I think we can use 8.3's autovacuum daemon as a reference for this... AFAIK, it's a child of postmaster that sleep for $naptime and then looks for something to do (it also looks in a catalog) and the send a worker to do it that's what we need to do but... for the $naptime i think we can autoconfigure it, when we execute a job look for the next job in queue and sleep until we are going to reach the time to execute it i don't think we need a max_worker parameter, it should launch as many workers as it needs pg_dump support -- dump every entry of the pg_job catalog as a CREATE JOB SQL statement or a create_job() function depending on what we decided ideas? comments? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 10:03 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. It'll always be another (set of) processes even if it's in core. All it means to be in core is that it will be harder to make modifications and you'll be tied to the Postgres release cycle. Main advantage over cron or another scheduler being that it'd be part of my transactional backups, of course. All you need for that is to store the schedule in a database table. This has nothing to do with where the scheduler code lives. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Tom, I believe that in core may be installed by default in case of the pgAgent or similar solution... Many big companies does not allow the developers to configure and install components we need to request everthing in 10 copies of forms... By making it in core or installed by default means that we have more chance that the db scheduler would be widely accepted... And more important... we would not have to check its availability on the setup and provide an alternate scheduler if the database scheduler is off... I believe that a database scheduler would allow me to drop 20 thousand lines of java code in my server... 2010/2/20 Tom Lane t...@sss.pgh.pa.us Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Lucas
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin - i need to backup postgres database to backup the schedules - the use pgagent here is not very extended but the few a know have tried desisted because they said: not always executed the jobs... i don't have any real evidence of that and probably what happens was that the pgagent daemon wasn't working (error prone), but being it started by the postmaster get rid of that problem... The first one could be rid out with a set of functions in pgagent and clear docs... i can live with the other two at some degree... but getting rid of the third one should be nice :) -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 12:03 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin That's easily changed. EDB's Advanced Server emulates Oracles DBMS_JOB interface with it for example. - i need to backup postgres database to backup the schedules Only if you put the control schema in that database. If you don't want to do that, stick it somewhere else. With your proposed scheme, you'd probably have to use pg_dumpall --backup-globals (or whatever it's called) - the use pgagent here is not very extended but the few a know have tried desisted because they said: not always executed the jobs... i don't have any real evidence of that and probably what happens was that the pgagent daemon wasn't working (error prone), but being it started by the postmaster get rid of that problem... Noone has ever reported such a bug that I'm aware of. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 11:55 PM, Lucas luca...@gmail.com wrote: I believe that a database scheduler would allow me to drop 20 thousand lines of java code in my server... How does that work? If you don't have a scheduler in the database, or pgAgent, why aren't you using cron or Windows task scheduler, neither of which would require 20K lines of Java code. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 7:32 PM, Dave Page dp...@pgadmin.org wrote: On Sun, Feb 21, 2010 at 12:03 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin That's easily changed. EDB's Advanced Server emulates Oracles DBMS_JOB interface with it for example. maybe i can work on that, then -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, Feb 20, 2010 at 10:03 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. It's currently written in C++/pl/pgsql and uses wxWidgets, none of which couldn't be changed with a little work. Having it in core will almost certainly result in reduced functionality though - there are use cases in which you may have multiple agents running against one control database, or executing jobs on remote databases for example. We originally wrote the code such that it might be easily included in core in the future, but every time this topic comes up in -hackers, there are a significant number of people who don't think a scheduler should be tied to the core code so we stopped assuming it ever would be. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Lucas wrote: Tom, I believe that in core may be installed by default in case of the pgAgent or similar solution... Many big companies does not allow the developers to configure and install components we need to request everthing in 10 copies of forms... By making it in core or installed by default means that we have more chance that the db scheduler would be widely accepted... This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sun, Feb 21, 2010 at 12:38 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 7:32 PM, Dave Page dp...@pgadmin.org wrote: On Sun, Feb 21, 2010 at 12:03 AM, Jaime Casanova jcasa...@systemguards.com.ec wrote: On Sat, Feb 20, 2010 at 4:37 PM, Dave Page dp...@pgadmin.org wrote: On Sat, Feb 20, 2010 at 9:33 PM, Jaime Casanova jcasa...@systemguards.com.ec wrote: Hi, I'm trying to figure out how difficult is this Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. - it's not that easy if you don't have pgadmin That's easily changed. EDB's Advanced Server emulates Oracles DBMS_JOB interface with it for example. maybe i can work on that, then I'd love to add a management API to pgAgent if you'd like to work on it. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Sat, 2010-02-20 at 18:19 -0500, Tom Lane wrote: Dimitri Fontaine dfonta...@hi-media.com writes: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, I don't think this really makes sense. There's basically no argument for having it in core other than I'm too lazy to install a separate package. Unlike the case for autovacuum, there isn't anything an in-core implementation could do that an external one doesn't do as well or better. So I'm not eager to take on additional maintenance burden for such a thing. There is zero technical reason for this to be in core. That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. Joshua D. Drake regards, tom lane -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering Respect is earned, not gained through arbitrary and repetitive use or Mr. or Sir. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
On Feb 20, 2010, at 8:06 PM, Joshua D. Drake j...@commandprompt.com wrote: There is zero technical reason for this to be in core. That doesn't mean it isn't a really good idea. It would be nice to have a comprehensive job scheduling solution that allows me to continue abstract away from external solutions and operating system dependencies. Well put. That pretty much sums up my feelings on this perfectly. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
Ah! wxWidgets... Yes, i knew there was something i didn't like about pgAgent. So is not as simple as installing it 2010/2/20, Dave Page dp...@pgadmin.org: On Sat, Feb 20, 2010 at 10:03 PM, Dimitri Fontaine dfonta...@hi-media.com wrote: Dave Page dp...@pgadmin.org writes: Why not just use pgAgent? It's far more flexible than the design you've suggested, and already exists. What would it take to have it included in core, so that it's not a separate install to do? I'd love to have some support for running my maintenance pl functions directly from the database. I mean without installing, running and monitoring another (set of) process. It's currently written in C++/pl/pgsql and uses wxWidgets, none of which couldn't be changed with a little work. Having it in core will almost certainly result in reduced functionality though - there are use cases in which you may have multiple agents running against one control database, or executing jobs on remote databases for example. We originally wrote the code such that it might be easily included in core in the future, but every time this topic comes up in -hackers, there are a significant number of people who don't think a scheduler should be tied to the core code so we stopped assuming it ever would be. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.com -- Enviado desde mi dispositivo móvil Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] scheduler in core
2010/2/21 Andrew Dunstan and...@dunslane.net: Lucas wrote: Tom, I believe that in core may be installed by default in case of the pgAgent or similar solution... Many big companies does not allow the developers to configure and install components we need to request everthing in 10 copies of forms... By making it in core or installed by default means that we have more chance that the db scheduler would be widely accepted... This reasoning just doesn't fly in the PostgreSQL world. PostgreSQL is designed to be extensible, not a monolithic product. We're not going to change that because some companies have insane corporate policies. The answer, as Jefferson said in another context, is to inform their ignorance. That isn't to say that there isn't a case for an in core scheduler, but this at least isn't a good reason for it. What I remember - this is exactly same discus like was about replication thre years ago fiirst strategy - we doesn't need it in core next we was last with replacation Regards Pavel Stehule cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
