Re: [SQL] Job control in sql
W dniu 2012-05-25 14:52, Jan Lentfer pisze: Add a sort of status table where you insert your unique job identifer at the start of the function and remove it in the end? As seperate transactions of course. That might leave status set on forever in a case when a job crashes and does not reach the point where it removes the identifier. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Job control in sql
W dniu 2012-05-25 10:28, Svenne Krap pisze: Hi. I am building a system, where we have jobs that run at different times (and takes widely different lengths of time). Basically I have a jobs table: create table jobs( id serial, ready boolean, job_begun timestamptz, job_done timestamptz, primary key (id) ); This should run by cron, at it is my intention that the cronjob (basically) consists of / psql -c "select run_jobs()"/ My problem is, that the job should ensure that it is not running already, which would be to set job_begun when the job starts". That can easily happen as jobs should be started every 15 minutes (to lower latency from ready to done) but some jobs can run for hours.. The problem is that a later run of run_jobs() will not see the job_begun has been set by a prior run (that is unfinished - as all queries from the plpgsql-function runs in a single, huge transaction). My intitial idea was to set the isolation level to "read uncommitted" while doing the is-somebody-else-running-lookup, but I cannot change that in the plpgsql function (it complains that the session has to be empty - even when I have run nothing before it). Any ideas on how to solve the issue? I run it on Pgsql 9.1. Svenne I think you might try in your run_jobs() SELECT job_begun FROM jobs WHERE id = job_to_run FOR UPDATE NOWAIT; This in case of conflict would throw the exception: 55P03 could not obtain lock on row in relation "jobs" and you handle it (or not, which might be OK too) in EXCEPTION block. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] possible bug in psql
On 2012-05-29, chester c young wrote: > do not know if right mailing list > > in psql in Ubuntu, when beginning with a smaller terminal, eg, 80 > col wide, then moving to a larger terminal, eg, 132 col wide, the > readline(?) editor in psql still treats like 80 cols, making it > impossible to edit longer text. As far as I know (as this is the behavior in debian stable) the ubuntu people use libgettext and not libreadline. You can force it to use libreadline by using LD_PRELOAD. I reccomend that you do I did this by editing /usr/bin/psql which is a perl script In any case linux doesn't propogate SIG_WINCH to the other processes that are using the pty so even if you are using readline you have to force this by resizing the window again after exiting the pager, and then press ctrl-l to clear the screen. see also: ld.so(8) kill(7) /usr/share/doc/postgresql-common/* -- ⚂⚃ 100% natura -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Job control in sql
On 29-05-2012 12:32, Ireneusz Pluta wrote: > W dniu 2012-05-25 10:28, Svenne Krap pisze: >> Hi. >> >> I am building a system, where we have jobs that run at different >> times (and takes widely different lengths of time). >> >> Basically I have a jobs table: >> >> create table jobs( >> id serial, >> ready boolean, >> job_begun timestamptz, >> job_done timestamptz, >> primary key (id) >> ); >> >> This should run by cron, at it is my intention that the cronjob >> (basically) consists of >> / >> psql -c "select run_jobs()"/ >> >> My problem is, that the job should ensure that it is not running >> already, which would be to set job_begun when the job starts". That >> can easily happen as jobs should be started every 15 minutes (to >> lower latency from ready to done) but some jobs can run for hours.. >> >> The problem is that a later run of run_jobs() will not see the >> job_begun has been set by a prior run (that is unfinished - as all >> queries from the plpgsql-function runs in a single, huge transaction). >> >> My intitial idea was to set the isolation level to "read uncommitted" >> while doing the is-somebody-else-running-lookup, but I cannot change >> that in the plpgsql function (it complains that the session has to be >> empty - even when I have run nothing before it). >> >> Any ideas on how to solve the issue? >> >> I run it on Pgsql 9.1. >> >> Svenne > > I think you might try in your run_jobs() > SELECT job_begun FROM jobs WHERE id = job_to_run FOR UPDATE NOWAIT; > This in case of conflict would throw the exception: > 55P03 could not obtain lock on row in relation "jobs" > and you handle it (or not, which might be OK too) in EXCEPTION block. > Hehe.. good idea... In the mean time I had thought about using advisory locks for the same thing, but the old-fashioned locks work fine too. Svenne -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] possible bug in psql
On 05/29/2012 07:48 PM, Jasen Betts wrote: On 2012-05-29, chester c young wrote: do not know if right mailing list in psql in Ubuntu, when beginning with a smaller terminal, eg, 80 col wide, then moving to a larger terminal, eg, 132 col wide, the readline(?) editor in psql still treats like 80 cols, making it impossible to edit longer text. As far as I know (as this is the behavior in debian stable) the ubuntu people use libgettext and not libreadline. You can force it to use libreadline by using LD_PRELOAD. I reccomend that you do I did this by editing /usr/bin/psql which is a perl script In any case linux doesn't propogate SIG_WINCH to the other processes that are using the pty so even if you are using readline you have to force this by resizing the window again after exiting the pager, and then press ctrl-l to clear the screen. It'd be nice if `less` would kill(SIGWINCH) its ppid since it is so often invoked as a helper. I guess they probably have good reasons not to. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] possible bug in psql
> > do not know if right mailing list
> >
> > in psql in Ubuntu, when beginning with a smaller
> terminal, eg, 80
> > col wide, then moving to a larger terminal, eg, 132 col
> wide, the
> > readline(?) editor in psql still treats like 80 cols,
> making it
> > impossible to edit longer text.
>
> As far as I know (as this is the behavior in debian stable)
> the ubuntu people use libgettext and not libreadline.
>
> You can force it to use libreadline by using
> LD_PRELOAD. I reccomend
> that you do I did this by editing /usr/bin/psql which is a
> perl script
>
> In any case linux doesn't propogate SIG_WINCH to the other
> processes
> that are using the pty so even if you are using readline you
> have to
> force this by resizing the window again after exiting the
> pager, and
> then press ctrl-l to clear the screen.
seems to me that Ubuntu is using readline (but I might be wrong)
from /usr/bin/psql:
# libreadline is a lot better than libedit, so prefer that
if ($cmdname eq 'psql') {
my @readlines;
push @readlines, sort();
if (@readlines) {
$ENV{'LD_PRELOAD'} = ($ENV{'LD_PRELOAD'} or '') . ':' . $readlines[-1];
}
}
also, LD_PRELOAD is not referenced anywhere in /etc/postgrest/...
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
