Thread: Sheduler in Postgres
Hi, I should schedule the execution of several stored procedures. Now I use an NT service for this, but as far as I know e.g. the Oracle has such a thing. It would be great if I could fire procedures on a timer basis. Is there a better solution for this than mine? Many thanks, -- Csaba --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
cron works pretty well in unix. Scheduled tasks on windows have, in my experience, been on the flakey side. On Tue, 7 Dec 2004, [iso-8859-2] Együd Csaba wrote: > Hi, > I should schedule the execution of several stored procedures. Now I use an > NT service for this, but as far as I know e.g. the Oracle has such a thing. > It would be great if I could fire procedures on a timer basis. > > Is there a better solution for this than mine? > > Many thanks, > > -- Csaba > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. > > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings >
The world rejoiced as csegyud@vnet.hu (Együd Csaba) wrote:
> Hi, I should schedule the execution of several stored
> procedures. Now I use an NT service for this, but as far as I know
> e.g. the Oracle has such a thing. It would be great if I could fire
> procedures on a timer basis.
>
> Is there a better solution for this than mine?
Traditionally, PostgreSQL has consciously omitted such things where
they would merely be replicating existing operating system
functionality.
On Unix, "cron" is the traditional service that provides this
functionality.
I think there's a port to Windows NT, so you could presumably use that
if you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)
OK, thank you. Yes we have a more native tool - the "at" service on NT/2K.
I wanted to boundle it into the application because I don't like to place
parts of the application outside the application. I wrote a scheduler
service sepcialy for the applications tasks. It works fine, but I thought
this should be inside the server.
Thak you,
-- Csaba
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Christopher Browne
Sent: Saturday, December 11, 2004 5:09 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Sheduler in Postgres
The world rejoiced as csegyud@vnet.hu (Együd Csaba) wrote:
> Hi, I should schedule the execution of several stored procedures. Now
> I use an NT service for this, but as far as I know e.g. the Oracle has
> such a thing. It would be great if I could fire procedures on a timer
> basis.
>
> Is there a better solution for this than mine?
Traditionally, PostgreSQL has consciously omitted such things where they
would merely be replicating existing operating system functionality.
On Unix, "cron" is the traditional service that provides this functionality.
I think there's a port to Windows NT, so you could presumably use that if
you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
Thank you Ben, I already have known these tools. I was wonder if Postgres has an integrated tool for scheduling sp's (like Oracle has!). OK it doesn't have. No problem just tried to make world more simpler... :) Bye, -- Csaba -----Original Message----- From: Ben [mailto:bench@silentmedia.com] Sent: Friday, December 10, 2004 11:29 PM To: Együd Csaba Cc: 'Pgsql-General@Postgresql.Org' Subject: Re: [GENERAL] Sheduler in Postgres cron works pretty well in unix. Scheduled tasks on windows have, in my experience, been on the flakey side. On Tue, 7 Dec 2004, [iso-8859-2] Együd Csaba wrote: > Hi, > I should schedule the execution of several stored procedures. Now I > use an NT service for this, but as far as I know e.g. the Oracle has such a thing. > It would be great if I could fire procedures on a timer basis. > > Is there a better solution for this than mine? > > Many thanks, > > -- Csaba > > > --- > Outgoing mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03. --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.805 / Virus Database: 547 - Release Date: 2004.12.03.
Traditionally, PostgreSQL has consciously omitted such things where
they would merely be replicating existing operating system
functionality.
On Unix, "cron" is the traditional service that provides this
functionality.
I think there's a port to Windows NT, so you could presumably use that
if you haven't got any more "native" job scheduler.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://www.ntlug.org/~cbbrowne/lsf.html
"How should I know if it works? That's what beta testers are for. I
only coded it." (Attributed to Linus Torvalds, somewhere in a
posting)
On Tue, 2004-12-14 at 07:49, Christopher Browne wrote: > Traditionally, PostgreSQL has consciously omitted such things where > they would merely be replicating existing operating system > functionality. > > On Unix, "cron" is the traditional service that provides this > functionality. > > I think there's a port to Windows NT, so you could presumably use that > if you haven't got any more "native" job scheduler. I haven't administered a Winbox in a few years, but I remember wincron being quite good back in the day. It's free, but registration gets you support. http://www.wincron.com/index.html
No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this is a question that comes up pretty often. I think the bulk of the work to make this happen could be done outside of the core database, so it seems a good candidate for pgfoundry. On Sat, Dec 11, 2004 at 07:44:03AM +0100, Egy?d Csaba wrote: > Thank you Ben, I already have known these tools. I was wonder if Postgres > has an integrated tool for scheduling sp's (like Oracle has!). OK it doesn't > have. No problem just tried to make world more simpler... :) > > Bye, > -- Csaba -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Wed, 15 Dec 2004, Jim C. Nasby wrote:
> No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this
> is a question that comes up pretty often. I think the bulk of the work
> to make this happen could be done outside of the core database, so it
> seems a good candidate for pgfoundry.
What's wrong with at, cron, and some trivial shell scripting?
(This is a real question, I wonder what advantages dbms_job has.)
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
The only advantage would be that an in-database solution would be OS independent and it could be managed using the same tools which manage the database itself, including the backup and management of it. I'm not sure how the Oracle thing is working, but I suppose you can manage it using plain SQL. This makes for a more homogeneous solution. Using cron makes your database solution OS dependent, and if you want to programatically manage the tasks, then your program will be also OS dependent. This is about the advantages I can see of an integrated scheduling service. That said, you can always shift that in your middle-tier (if you have a 3 tier system), possibly backed by some DB tables (this is how we do our scheduling here). Cheers, Csaba. On Thu, 2004-12-16 at 10:12, Marco Colombo wrote: > On Wed, 15 Dec 2004, Jim C. Nasby wrote: > > > No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this > > is a question that comes up pretty often. I think the bulk of the work > > to make this happen could be done outside of the core database, so it > > seems a good candidate for pgfoundry. > > What's wrong with at, cron, and some trivial shell scripting? > (This is a real question, I wonder what advantages dbms_job has.) > > .TM.
On Thu, Dec 16, 2004 at 10:47:46 +0100, Csaba Nagy <nagy@ecircle-ag.com> wrote: > The only advantage would be that an in-database solution would be OS > independent and it could be managed using the same tools which manage > the database itself, including the backup and management of it. I'm not > sure how the Oracle thing is working, but I suppose you can manage it > using plain SQL. This makes for a more homogeneous solution. > Using cron makes your database solution OS dependent, and if you want to > programatically manage the tasks, then your program will be also OS > dependent. > This is about the advantages I can see of an integrated scheduling > service. That said, you can always shift that in your middle-tier (if > you have a 3 tier system), possibly backed by some DB tables (this is > how we do our scheduling here). cron isn't really part of the OS. Up until 8.0, any OS that Postgres ran on had cron. I have seen claims that there is a version of cron that runs under windows, but haven't verified that. Given this I don't see how a dependence on cron is going to cause you portability problems.
Well, if you program in Java for example, it is a portability problem to call any native programs. It doesn't matter at all that the program itself is portable, it is just not portable enough to call it at all, not to mention other robustness problems arising from the Java-native interaction. Completely portable is nothing, but a solution which only requires you to call your data base library with plain SQL is a lot more portable (in any language you might use) than calling external programs. Cheers, Csaba. On Thu, 2004-12-16 at 14:53, Bruno Wolff III wrote: > On Thu, Dec 16, 2004 at 10:47:46 +0100, > Csaba Nagy <nagy@ecircle-ag.com> wrote: > > The only advantage would be that an in-database solution would be OS > > independent and it could be managed using the same tools which manage > > the database itself, including the backup and management of it. I'm not > > sure how the Oracle thing is working, but I suppose you can manage it > > using plain SQL. This makes for a more homogeneous solution. > > Using cron makes your database solution OS dependent, and if you want to > > programatically manage the tasks, then your program will be also OS > > dependent. > > This is about the advantages I can see of an integrated scheduling > > service. That said, you can always shift that in your middle-tier (if > > you have a 3 tier system), possibly backed by some DB tables (this is > > how we do our scheduling here). > > cron isn't really part of the OS. Up until 8.0, any OS that Postgres > ran on had cron. I have seen claims that there is a version of cron that > runs under windows, but haven't verified that. Given this I don't see > how a dependence on cron is going to cause you portability problems.
Bruno Wolff III wrote: > cron isn't really part of the OS. Up until 8.0, any OS that Postgres > ran on had cron. I have seen claims that there is a version of cron that > runs under windows, but haven't verified that. Given this I don't see > how a dependence on cron is going to cause you portability problems. I would prefer the development effort be applied to more specific database engine issues and enhancements. As Bruno has noted, you can get some variation of cron on virtually every OS that runs Postgresql. -- Until later, Geoffrey
--- Bruno Wolff III <__> wrote: > On Thu, Dec 16, 2004 at 10:47:46 +0100, > Csaba Nagy <nagy@ecircle-ag.com> wrote: > > The only advantage would be that an in-database solution would be > OS > > independent and it could be managed using the same tools which > manage > > the database itself, including the backup and management of it. I'm > not > > sure how the Oracle thing is working, but I suppose you can manage > it > > using plain SQL. This makes for a more homogeneous solution. > > Using cron makes your database solution OS dependent, and if you > want to > > programatically manage the tasks, then your program will be also OS > > dependent. > > This is about the advantages I can see of an integrated scheduling > > service. That said, you can always shift that in your middle-tier > (if > > you have a 3 tier system), possibly backed by some DB tables (this > is > > how we do our scheduling here). > > cron isn't really part of the OS. Up until 8.0, any OS that Postgres > ran on had cron. I have seen claims that there is a version of cron > that > runs under windows, but haven't verified that. Given this I don't see > how a dependence on cron is going to cause you portability problems. > the cron you mention is www.wincron.com. I'm just started testing it... seems a nice enough tool. Here are my two cents on a sheduling engine: I agree that a scheduling engine inside pgsql could be a useful tool, and the advantage is that, even if cron may be available on all the platforms pg runs, there are portability issues. The code cron runs may (and it will probably will) have to be changed for each platform. On the other side, having a sheduling engine inside postgresql would help keeping the code I develop using postgres portable inside postgresql. No matter on what platform I decide to setup postgresql, I know that the triggers, functions and other stuff works. regards, R.
No doubt about this, there are a lot of features which are way more important, but this was not the point at all. The question was if there is any advantage of having it in the DB, and the answer is: yes there is some advantage. It's a totally different problem how important it is - there are many ways to handle scheduled processes. I would myself vote with leaving it out in favor of other features (my favorit is shared row level locks, or anything else it would take to finally solve the too strong lock for FK check problem, which makes inserts in tables with FKs deadlock prone). Not that I could influence at all the development through contributions, nor funding... Cheers, Csaba. On Thu, 2004-12-16 at 14:58, Geoffrey wrote: > Bruno Wolff III wrote: > > > cron isn't really part of the OS. Up until 8.0, any OS that Postgres > > ran on had cron. I have seen claims that there is a version of cron that > > runs under windows, but haven't verified that. Given this I don't see > > how a dependence on cron is going to cause you portability problems. > > I would prefer the development effort be applied to more specific > database engine issues and enhancements. As Bruno has noted, you can > get some variation of cron on virtually every OS that runs Postgresql.
Csaba Nagy wrote: > No doubt about this, there are a lot of features which are way more > important, but this was not the point at all. > > The question was if there is any advantage of having it in the DB, and > the answer is: yes there is some advantage. It's a totally different > problem how important it is - there are many ways to handle scheduled > processes. Sure, and there's an advantage to traveling the speed of light, but you've got to set your priorities. > I would myself vote with leaving it out in favor of other features Agreed. -- Until later, Geoffrey
On Thu, Dec 16, 2004 at 10:12:46AM +0100, Marco Colombo wrote: > On Wed, 15 Dec 2004, Jim C. Nasby wrote: > > >No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this > >is a question that comes up pretty often. I think the bulk of the work > >to make this happen could be done outside of the core database, so it > >seems a good candidate for pgfoundry. > > What's wrong with at, cron, and some trivial shell scripting? > (This is a real question, I wonder what advantages dbms_job has.) I don't know about dbms_job, but I implemented a simple cron-a-like in postgresql for one of my apps so that database clients can schedule events. The clients don't have access to a shell on the DB server, it's a completely separate security domain. And the clients may not even be powered up when an event is due to fire, so running cron there isn't going to work either. Cheers, Steve
Csaba Nagy <nagy@ecircle-ag.com> writes: > The only advantage would be that an in-database solution would be OS > independent That kind of argument leads to monstrosities like Oracle that reimplement everything they can from the OS. What would be a neat project is if someone wrote a cron implementation that used postgres as a backend. You could run cron from the command-line and edit your cron jobs normally, but the resulting data would get stuffed into a postgres database. The cron daemon would connect to the database to get its information. This would be neat since it would let applications access and modify the data through a well defined programmatic interface, but the data could easily be queried using SQL to display in a nice GUI or HTTP interface. I suspect this would be very attractive to shared hosting systems. It would also be convenient for applications that want to provide more restricted access to application-specific routine jobs. This wouldn't really be a part of Postgres though, just another application using Postgres. It could be something Postgres could recommend for people who find cron too awkward for their application. -- greg
After a long battle with technology, gsstark@mit.edu (Greg Stark), an earthling, wrote:
> This wouldn't really be a part of Postgres though, just another
> application using Postgres. It could be something Postgres could
> recommend for people who find cron too awkward for their
> application.
This has considerable merit.
One thing that is unfortunate about cron is that it provides little
verifyable feedback. It logs some things, sort of...
A "cron implementation using PostgreSQL as data store" would have a
wonderfully natural place to record log information in a usefully
structured fashion.
When a job runs, it would be a splendid idea to record such things as:
- Job ID (perhaps an OID, or some other candidate primary key)
- PID
- Start time
- End time
- Exit code
Given all of the above, a job might look at the logs and
self-terminate if there's another instance still running from last
hour.
Jobs that are supposed to be mutually exclusive could detect as much.
You could _attempt_ to run a job every hour, and have it decide "Oh,
I've already run successfully in the last [interval], so I'll not
bother."
None of this means forcing it into the database implementation; it
just means that it would be useful. "pgcron" sounds like an utterly
splendid idea.
--
output = ("cbbrowne" "@" "gmail.com")
http://www3.sympatico.ca/cbbrowne/x.html
"Applicants must have *at least* five years experience with Windows
XCVIII..."
See my comments below. On Thu, 2004-12-16 at 21:01, Greg Stark wrote: > Csaba Nagy <nagy@ecircle-ag.com> writes: > > > The only advantage would be that an in-database solution would be OS > > independent > > That kind of argument leads to monstrosities like Oracle that reimplement > everything they can from the OS. > Now, cron isn't exactly part of the OS, is it ? I wouldn't care if the postgres implementation would use cron internally, as long as I can control it through plain SQL. The argument was that the data base could hide the OS, not that it should not use it, nor reimplement it. > What would be a neat project is if someone wrote a cron implementation that > used postgres as a backend. You could run cron from the command-line and edit > your cron jobs normally, but the resulting data would get stuffed into a > postgres database. The cron daemon would connect to the database to get its > information. > This actually is a very nice idea. It has the distinct advantage that it doesn't have to be limited to Postgres, it could connect to any data base, or any other data store providing the needed functionality. > This would be neat since it would let applications access and modify the data > through a well defined programmatic interface, but the data could easily be > queried using SQL to display in a nice GUI or HTTP interface. > > I suspect this would be very attractive to shared hosting systems. It would > also be convenient for applications that want to provide more restricted > access to application-specific routine jobs. > > This wouldn't really be a part of Postgres though, just another application > using Postgres. It could be something Postgres could recommend for people who > find cron too awkward for their application.
On Thu, 16 Dec 2004, Christopher Browne wrote:
> A "cron implementation using PostgreSQL as data store" would have a
> wonderfully natural place to record log information in a usefully
> structured fashion.
>
> When a job runs, it would be a splendid idea to record such things as:
> - Job ID (perhaps an OID, or some other candidate primary key)
> - PID
> - Start time
> - End time
> - Exit code
>
> Given all of the above, a job might look at the logs and
> self-terminate if there's another instance still running from last
> hour.
>
> Jobs that are supposed to be mutually exclusive could detect as much.
>
> You could _attempt_ to run a job every hour, and have it decide "Oh,
> I've already run successfully in the last [interval], so I'll not
> bother."
>
> None of this means forcing it into the database implementation; it
> just means that it would be useful. "pgcron" sounds like an utterly
> splendid idea.
Is the Oracle one _just_ that? A cron/at replacement? What about porting
every UNIX utility to the DB engine (that would be a cross-platfrom Unix
- wow)?
Why don't they put web and application server functionality (apache and
PHP) in the DB? No, wait... ehm... :-)
Seriously, such an application (scheduler) _will_ have to deal with OS
differences. Interesting things to log about the spawned jobs will be
different. They way you run then (I don't mean the actual system call,
think of nice level instead) may be different.
Now, the idea of different frontends to a DB-based backend may sound
cool, but either the backend supports only the minimum set of features
the various OSes offer when it comes at running jobs, or it implements
some OS-dependant features conditionally, which exposes to the applications
what it is supposed to hide. Overall I'm not impressed.
I wonder if limiting the application domain to DB-related jobs only
would help. I mean, it is quite common to run time based procedures
at DB level, like report generation or table summarization. Usually,
this activities are driven by _external_ schedulers (cron), via
scripts that need to connect and _authenticate_, which leads to
security nightmares.
An in-core scheduler, which runs in-core procedures only (if you want
to write procedures that invoke external programs go ahead, but it's
outside the scope of the scheduler), might be a good idea.
User X may schedule a query to be executed at a given time. I can see
some advantages:
- security: no need for authentication. The query was added to the
scheduler by an authenticated client and will run under the same
permissions (superusers will be able to alter the schedule of all
users of course);
- portability: no need for external tools. Note that the details
of running an external process don't matter here. Moreover, we need
only a timer from the OS, no other features. So either the OS provides
it (most do) or not, in which case the scheduler will be disabled
(and requests for adding jobs will fail gracefully).
- simplicity: it will log the query, start time, end time, success/failure
(maybe _what_ error if available). No PIDs, no OS-level details.
If you need more extensive logging, implement your own in your function.
- availability: the scheduler will be available to any client, in a standard
way. Right now, either you run a cron job on the server (if you have
access), or on the client (though you may need a 24/24 uptime), or
you code some hack in the middleware (think of PHP-based apps - they need
to piggyback procedures on web requests).
Final note: most of us, I believe, do well with cron and friends, or
already found a different solution. There's no _need_ for an in-core
scheduler. But, if such a feature existed, I think many of us would use it.
And be happier than now. :-)
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it
On Friday 17 Dec 2004 3:20 pm, Marco Colombo wrote: > Is the Oracle one _just_ that? A cron/at replacement? What about porting > every UNIX utility to the DB engine (that would be a cross-platfrom Unix > - wow)? > Why don't they put web and application server functionality (apache and > PHP) in the DB? No, wait... ehm... :-) If one looks at the database and thinks, 'This is a storage engine with programming abilities', then a in-DB scheduler isn't that much attractive proposition. Look at it and think 'This is an application server with flexbile data storage engine for free' and it sounds lot more sensible. It is all about perspective and it is not the same from one case to another. I mean why would one write a PL/SQL application that counts to 1.3+ million lines of code?..:-) But people do these sort of things.. Shridhar
--- Shridhar Daithankar <__> wrote: > On Friday 17 Dec 2004 3:20 pm, Marco Colombo wrote: > > Is the Oracle one _just_ that? A cron/at replacement? What about > porting > > every UNIX utility to the DB engine (that would be a cross-platfrom > Unix > > - wow)? > > Why don't they put web and application server functionality (apache > and > > PHP) in the DB? No, wait... ehm... :-) > > If one looks at the database and thinks, 'This is a storage engine > with > programming abilities', then a in-DB scheduler isn't that much > attractive > proposition. > > Look at it and think 'This is an application server with flexbile > data storage > engine for free' and it sounds lot more sensible. > Just look as Oracle came to life... started as Database Engine, and ended up being a complex set of application, business, database, workflow, etc etc etc. > It is all about perspective and it is not the same from one case to > another. > > I mean why would one write a PL/SQL application that counts to 1.3+ > million > lines of code?..:-) But people do these sort of things.. Why would someone write 6 million lines of code in Cobol or RPG? Because all applications grow in time. Many Financial, Manufacturing and other sorts of businesses still run on their Mainframes, and nobody complains about it. > > Shridhar > > > ---------------------------(end of > broadcast)--------------------------- > TIP 9: the planner will ignore your desire to choose an index scan if > your > joining column's datatypes do not match > Best, Riccardo
Hi ALL! I need to check before selection records from table - does this table exist How can i do that? Thanx in advance
ON.KG wrote: > Hi ALL! > > I need to check before selection records from table - does this table > exist > How can i do that? You could: 1. Look in the INFORMATION_SCHEMA 2. Check pg_class 3. Examine the output of "psql -E", \d to see how psql gets its information. -- Richard Huxton Archonet Ltd
--- "ON.KG" <__> wrote:
> Hi ALL!
>
> I need to check before selection records from table - does this table
> exist
> How can i do that?
>
> Thanx in advance
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
Option 1: tells you if the table exists in your database
select *
from pg_catalog.pg_tables as t
where t.schemaname = '<your schema name>' and
t.tablename = '<your table name';
Option 2: tells you if the table exists in your database and if is
visible in your search path (i.e. no need to select * from
schema.table, only select * from table)
select *
from pg_catalog.pg_class as c left outer join
pg_catalog.pg_namespace as n on
n.oid = c.relnamespace
where n.nspname = '<your schema name>' and
c.relname = '<your table name' and
pg_catalog.pg_table_is_visible(c.oid);
Richard Huxton & Riccardo G. Facchini Thank you very much!
Sorry: I forgot to add something to option 2
--- "ON.KG" <__> wrote:
> Hi ALL!
>
> I need to check before selection records from table - does this table
> exist
> How can i do that?
>
> Thanx in advance
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to
> majordomo@postgresql.org
>
Option 1: tells you if the table exists in your database
select *
from pg_catalog.pg_tables as t
where t.schemaname = '<your schema name>' and
t.tablename = '<your table name';
Option 2: tells you if the table exists in your database and if is
visible in your search path (i.e. no need to select * from
schema.table, only select * from table)
select *
from pg_catalog.pg_class as c left outer join
pg_catalog.pg_namespace as n on
n.oid = c.relnamespace
where n.nspname = '<your schema name>' and
c.relname = '<your table name' and
c.relkind = 'r' and
pg_catalog.pg_table_is_visible(c.oid);
This has considerable merit.
One thing that is unfortunate about cron is that it provides little
verifyable feedback. It logs some things, sort of...
A "cron implementation using PostgreSQL as data store" would have a
wonderfully natural place to record log information in a usefully
structured fashion.
When a job runs, it would be a splendid idea to record such things as:
- Job ID (perhaps an OID, or some other candidate primary key)
- PID
- Start time
- End time
- Exit code
Given all of the above, a job might look at the logs and
self-terminate if there's another instance still running from last
hour.
Jobs that are supposed to be mutually exclusive could detect as much.
You could _attempt_ to run a job every hour, and have it decide "Oh,
I've already run successfully in the last [interval], so I'll not
bother."
None of this means forcing it into the database implementation; it
just means that it would be useful. "pgcron" sounds like an utterly
splendid idea.
--
output = ("cbbrowne" "@" "gmail.com")
http://www3.sympatico.ca/cbbrowne/x.html
"Applicants must have *at least* five years experience with Windows
XCVIII..."
Csaba Nagy <nagy@ecircle-ag.com> writes: > Now, cron isn't exactly part of the OS, is it ? Yes, it is. -- greg
pgsql@esiway.net (Marco Colombo) writes: > On Thu, 16 Dec 2004, Christopher Browne wrote: >> None of this means forcing it into the database implementation; it >> just means that it would be useful. "pgcron" sounds like an >> utterly splendid idea. > > Is the Oracle one _just_ that? A cron/at replacement? What about > porting every UNIX utility to the DB engine (that would be a > cross-platfrom Unix - wow)? Why don't they put web and application > server functionality (apache and PHP) in the DB? No, wait... ehm... > :-) > > Seriously, such an application (scheduler) _will_ have to deal with > OS differences. Interesting things to log about the spawned jobs > will be different. They way you run then (I don't mean the actual > system call, think of nice level instead) may be different. That's well and nice; if I had a "database-driven" cron alternative, I would use it, possibly on several platforms, as it would provide compelling advantages over traditional cron. I think it would provide compelling advantages to my employer, too. I'm _not_ asking for some "Barneyfied All Encompassing Interface;" I'm _not_ asking for it to be treated as an integral part of PostgreSQL. You're picking a strawman argument there, and trying to suggest that's what I'm looking for. I'm certainly NOT. I don't want Oracle, but I could use a better cron, and anacron isn't the answer. We have some challenges concerning generating reports; I consider that having a "better scheduler" than cron is one of the pieces of that particular puzzle. > I wonder if limiting the application domain to DB-related jobs only > would help. I mean, it is quite common to run time based procedures > at DB level, like report generation or table summarization. Usually, > this activities are driven by _external_ schedulers (cron), via > scripts that need to connect and _authenticate_, which leads to > security nightmares. No, the sorts of things that "pgcron" enables are most certainly _NOT_ reasonably limited to just "DB-related" jobs. Having an interface providing access to history information about past jobs enables plenty of things that don't require that the application cares about databases at all. -- "cbbrowne","@","ca.afilias.info" <http://dev6.int.libertyrms.com/> Christopher Browne (416) 673-4124 (land)
As I mentioned before, I think most (if not all) of the development effort would be outside the core database, meaning any of us would be able to work on it (as opposed to internal stuff which requires a lot more knowledge). There are two features dbms_job has that would probably require some internal support: In oracle, jobs are fired off by the database engine. If the database is up and job support is enabled, your jobs will run. Without some kind of support for the database to fire up connections and execute some kind of code we would have to rely on some external means to do so. This is less robust, isn't cross-platform, and requires more work of the end-users. The other feature is that the connection running jobs in oracle has the ability to re-create the connection environment used to submit the job. This means jobs are run as the same user who submitted the job, and certain session settings are also duplicated. In PostgreSQL, there's currently no way to assume the identity of another user. Even with lack of support for these two features, I still think it would be very usefull to create a generic job system, probably as a pgFoundary project. Enough people have asked about it that I'm sure there's plenty of re-invented code out there. If we have a solid framework that people are using, we'll have a much stronger case for getting the two features I mentioned added to the back-end. On Thu, Dec 16, 2004 at 08:58:00AM -0500, Geoffrey wrote: > Bruno Wolff III wrote: > > >cron isn't really part of the OS. Up until 8.0, any OS that Postgres > >ran on had cron. I have seen claims that there is a version of cron that > >runs under windows, but haven't verified that. Given this I don't see > >how a dependence on cron is going to cause you portability problems. > > I would prefer the development effort be applied to more specific > database engine issues and enhancements. As Bruno has noted, you can > get some variation of cron on virtually every OS that runs Postgresql. > > -- > Until later, Geoffrey > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
Can you open-source your code? I'm sure others would benefit from it. On Thu, Dec 16, 2004 at 09:37:04AM -0800, Steve Atkins wrote: > On Thu, Dec 16, 2004 at 10:12:46AM +0100, Marco Colombo wrote: > > On Wed, 15 Dec 2004, Jim C. Nasby wrote: > > > > >No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this > > >is a question that comes up pretty often. I think the bulk of the work > > >to make this happen could be done outside of the core database, so it > > >seems a good candidate for pgfoundry. > > > > What's wrong with at, cron, and some trivial shell scripting? > > (This is a real question, I wonder what advantages dbms_job has.) > > I don't know about dbms_job, but I implemented a simple cron-a-like in > postgresql for one of my apps so that database clients can schedule > events. The clients don't have access to a shell on the DB server, > it's a completely separate security domain. And the clients may not > even be powered up when an event is due to fire, so running cron there > isn't going to work either. > > Cheers, > Steve > > ---------------------------(end of broadcast)--------------------------- > TIP 8: explain analyze is your friend > -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Fri, Dec 17, 2004 at 12:07:21PM -0500, Greg Stark wrote: > > Csaba Nagy <nagy@ecircle-ag.com> writes: > > > Now, cron isn't exactly part of the OS, is it ? > > Yes, it is. Depends on how you define 'OS'. Linux is nothing but a kernel afterall, so it's up to each distro to decide if they'll include cron (and gentoo doesn't include cron for example). I'm pretty sure it's part of the base install of every unix I've used, though. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Sat, Dec 18, 2004 at 03:01:54PM -0600, Jim C. Nasby wrote: > Can you open-source your code? I'm sure others would benefit from it. Eh. Big chunks of it are application specific, and a fair bit of the rest uses some commercial libraries. I'll see what I can do. (It'd be worth it to kill the monthly "why don't we do cron in the DB?" thread :) ) Cheers, Steve > On Thu, Dec 16, 2004 at 09:37:04AM -0800, Steve Atkins wrote: > > On Thu, Dec 16, 2004 at 10:12:46AM +0100, Marco Colombo wrote: > > > On Wed, 15 Dec 2004, Jim C. Nasby wrote: > > > > > > >No, PostgreSQL doesn't have an equivalent to Oracle's dbms_job, but this > > > >is a question that comes up pretty often. I think the bulk of the work > > > >to make this happen could be done outside of the core database, so it > > > >seems a good candidate for pgfoundry. > > > > > > What's wrong with at, cron, and some trivial shell scripting? > > > (This is a real question, I wonder what advantages dbms_job has.) > > > > I don't know about dbms_job, but I implemented a simple cron-a-like in > > postgresql for one of my apps so that database clients can schedule > > events. The clients don't have access to a shell on the DB server, > > it's a completely separate security domain. And the clients may not > > even be powered up when an event is due to fire, so running cron there > > isn't going to work either.
Centuries ago, Nostradamus foresaw when decibel@decibel.org ("Jim C. Nasby") would write:
> In PostgreSQL, there's currently no way to assume the identity of
> another user.
I'm confused at that...
There seem to be ways at time of connection establishment, whether via
the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
a psql session via "\c - newuser".
In what way do those not represent "assumption of another user's
identity"?
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"In the long run every program becomes rococo - then rubble."
-- Alan Perlis
Christopher Browne <cbbrowne@acm.org> writes:
> Centuries ago, Nostradamus foresaw when decibel@decibel.org ("Jim C. Nasby") would write:
>> In PostgreSQL, there's currently no way to assume the identity of
>> another user.
> I'm confused at that...
> There seem to be ways at time of connection establishment, whether via
> the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
> a psql session via "\c - newuser".
Not to mention SET SESSION AUTHORIZATION.
regards, tom lane
I'm confused at that...
There seem to be ways at time of connection establishment, whether via
the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
a psql session via "\c - newuser"
In what way do those not represent "assumption of a user's identity"?
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','gmail.com').
http://www3.sympatico.ca/cbbrowne/linuxxian.html
"In the long run every program becomes rococo - then rubble."
-- Alan Perlis
On Sat, Dec 18, 2004 at 15:00:17 -0600, "Jim C. Nasby" <decibel@decibel.org> wrote: > > In oracle, jobs are fired off by the database engine. If the database is > up and job support is enabled, your jobs will run. Without some kind of > support for the database to fire up connections and execute some kind of > code we would have to rely on some external means to do so. This is less > robust, isn't cross-platform, and requires more work of the end-users. In my experience, cron is never down, but databases are taken down on a regular basis. I don't think the reliability of cron is a problem. > The other feature is that the connection running jobs in oracle has the > ability to re-create the connection environment used to submit the job. > This means jobs are run as the same user who submitted the job, and > certain session settings are also duplicated. In PostgreSQL, there's > currently no way to assume the identity of another user. cron also runs jobs as the user that created them. There is an issue when you want to let people who don't have shell access to the database server run scheduled jobs. I am not sure how the identity issue applies here. If you are running a cron script you can connect as whoever you are authorized to and can open a separate session for each job. > Even with lack of support for these two features, I still think it would > be very usefull to create a generic job system, probably as a pgFoundary > project. Enough people have asked about it that I'm sure there's plenty > of re-invented code out there. If we have a solid framework that people > are using, we'll have a much stronger case for getting the two features > I mentioned added to the back-end. That seems like the way to start. If people think they need something better than cron, you should get some help. There is a lot you could do with logging and job dependencies that are not built in to cron.
On Sat, Dec 18, 2004 at 10:22:44PM -0500, Tom Lane wrote:
> Christopher Browne <cbbrowne@acm.org> writes:
> > Centuries ago, Nostradamus foresaw when decibel@decibel.org ("Jim C. Nasby") would write:
> >> In PostgreSQL, there's currently no way to assume the identity of
> >> another user.
>
> > I'm confused at that...
>
> > There seem to be ways at time of connection establishment, whether via
> > the psql "-U" option, or the PGconnect "dbuser=foo" option, or during
> > a psql session via "\c - newuser".
>
> Not to mention SET SESSION AUTHORIZATION.
Doh! Completely forgot about that.
--
Jim C. Nasby, Database Consultant decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
"Jim C. Nasby" <decibel@decibel.org> writes: > On Fri, Dec 17, 2004 at 12:07:21PM -0500, Greg Stark wrote: > > > > Csaba Nagy <nagy@ecircle-ag.com> writes: > > > > > Now, cron isn't exactly part of the OS, is it ? > > > > Yes, it is. > > Depends on how you define 'OS'. Linux is nothing but a kernel afterall, That just means Linux isn't an OS all by itself. A kernel isn't very usable without, say, init, a shell, etc. A periodic job scheduler has traditionally been in that list of base elements of the OS. -- greg
On Tue, Dec 21, 2004 at 03:42:22PM -0500, Greg Stark wrote: > > "Jim C. Nasby" <decibel@decibel.org> writes: > > > On Fri, Dec 17, 2004 at 12:07:21PM -0500, Greg Stark wrote: > > > > > > Csaba Nagy <nagy@ecircle-ag.com> writes: > > > > > > > Now, cron isn't exactly part of the OS, is it ? > > > > > > Yes, it is. > > > > Depends on how you define 'OS'. Linux is nothing but a kernel afterall, > > That just means Linux isn't an OS all by itself. A kernel isn't very usable > without, say, init, a shell, etc. A periodic job scheduler has traditionally > been in that list of base elements of the OS. Again, it depends on the distribution of linux. Gentoo, for example, does not include cron as part of their base install. I suspect there's other linux distro's that don't either. So, you can't assume that you will have cron available on a linux box. Arguably this is also true of all the unixes since you could remove cron, but I can't see why anyone would do that. As I mentioned before, I'm not aware of any unix that doesn't come with cron. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"