date:20120525

[SQL] Job control in sql

2012-05-25 Thread Svenne Krap

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

[SQL] Select row cells as new columns

2012-05-25 Thread danycxxx

Hello. I hope you can help me with this or at least guide me into the right
direction:

I have 2 tables:

CREATE TABLE infos
(
  id integer NOT NULL DEFAULT nextval('info_id_seq'::regclass),
  name text NOT NULL,
  id_member integer NOT NULL,
  title text,
  min_length integer NOT NULL DEFAULT 0,
  max_length integer NOT NULL DEFAULT 30,
  required boolean NOT NULL DEFAULT false,
  type text NOT NULL DEFAULT 'text'::text,
  CONSTRAINT info_pkey PRIMARY KEY (id ),
  CONSTRAINT infos_id_member_fkey FOREIGN KEY (id_member)
  REFERENCES members (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE infos
  OWNER TO postgres;

-- Index: info_id_idx

-- DROP INDEX info_id_idx;

CREATE INDEX info_id_idx
  ON infos
  USING btree
  (id );

and 

CREATE TABLE info_data
(
  id serial NOT NULL,
  id_info integer,
  value text,
  CONSTRAINT info_data_pkey PRIMARY KEY (id ),
  CONSTRAINT info_data_id_info_fkey FOREIGN KEY (id_info)
  REFERENCES infos (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE info_data
  OWNER TO postgres;

-- Index: info_data_id_idx

-- DROP INDEX info_data_id_idx;

CREATE INDEX info_data_id_idx
  ON info_data
  USING btree
  (id );

with the following values:

infos: 
COPY infos (id, name, id_member, title, min_length, max_length, required,
type) FROM stdin;
1   nume1   Nume0   30  t   text
2   prenume 1   Prenume 0   30  t   text
3   cnp 1   C.N.P.  13  13  t   number
4   nume anterior   1   Nume anterior   0   30  f   text
5   stare civila1   Starea civila   0   30  f   text
6   cetatenie   1   Cetatenie   0   30  f   text
7   rezidenta   1   Rezidenta   0   30  f   text
9   tip act 1   C.I. / B.I. 0   10  t   text
10  serie ci1   Serie C.I. / B.I.   0   30  t   
text
11  numar ci1   Numar C.I. / B.I.   0   30  t   
text
12  data eliberarii 1   Data eliberarii 0   30  t   text
13  eliberat de 1   Eliberat de 0   30  t   text
8   adresa  1   Adresa  0   50  f   text
\.

info_data:
COPY info_data (id, id_info, value) FROM stdin;
1   1   a
2   2   a
3   3   100
4   4   
5   5   
6   6   
7   7   
8   8   
9   9   ci
10  10  sv
11  11  13
12  12  132
13  13  123
14  1   b
15  2   b
16  3   100
17  4   
18  5   
19  6   
20  7   
21  8   
22  9   BI
23  10  XT
24  11  123
25  12  10
26  13  10
\. 

The question:
How can I achive this output?

nume, prenume, cnp, nume anterior, stare civila, ... (as columns - built
from unique rows from infos)
a  , a, ...
b  , b, ... (as rows)


http://postgresql.1045698.n5.nabble.com/file/n5709987/info_data.sql
info_data.sql 
http://postgresql.1045698.n5.nabble.com/file/n5709987/infos.sql infos.sql 


--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Select-row-cells-as-new-columns-tp5709987.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
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] Select row cells as new columns

2012-05-25 Thread Jan Lentfer


On Fri, 25 May 2012 02:03:41 -0700 (PDT), danycxxx wrote:
[...]

The question:
How can I achive this output?

nume, prenume, cnp, nume anterior, stare civila, ... (as columns - 
built

from unique rows from infos)
a  , a, ...
b  , b, ... (as rows)


Did you look at crosstab functions?
http://www.postgresql.org/docs/9.1/static/tablefunc.html


Jan
--
professional: http://www.oscar-consult.de
private: http://neslonek.homeunix.org/drupal/

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

2012-05-25 Thread Jan Lentfer


On Fri, 25 May 2012 10:28:03 +0200, Svenne Krap wrote:
[...]

 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?


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.


Jan


--
professional: http://www.oscar-consult.de
private: http://neslonek.homeunix.org/drupal/

--
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] Select row cells as new columns

2012-05-25 Thread danycxxx

Yes, I've looked at it, but id doesn't create the desired output. After more
research I've found that I my design is similar to Entity, Attribute and
Value(EAV) design and I think I have to redesign. Any suggestion regarding
EAV? Is there any other approach?

--
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Select-row-cells-as-new-columns-tp5709987p5710005.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
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] Inherited table identification possible

2012-05-25 Thread George Woodring

Thanks for all of the help, this was exactly what I was looking for.

George

On Thu, May 24, 2012 at 2:11 PM, Stephen Belcher wrote:

> Yes, the system column "tableoid" identifies the actual table in which the
> row is stored. If you cast this to "regclass" you'll get the name of the
> table that the row is stored in:
>
> SELECT tableoid::regclass FROM base_table;
>
> There's more documentation on this available at
> http://www.postgresql.org/docs/9.1/static/ddl-inherit.html (for version
> 9.1, at any rate: season to taste with your version of PG)
>
> Hope this helps,
> --Stephen
>
>
>
> On Thu, May 24, 2012 at 2:01 PM, George Woodring <
> george.woodr...@iglass.net> wrote:
>
>> Is it possible to identify which inherited table data came from in a
>> query?  We have a table that has 3 inherited tables attached to it.  I am
>> looking for a way to identify the source of the data.
>>
>> My only thought would be to add a column to the tables that identify the
>> table.  I was just checking if there was a way to do it without the column.
>>
>> Thanks,
>> George
>>
>>
>>
>> --
>> iGLASS Networks
>> www.iglass.net
>>
>
>


-- 
iGLASS Networks
www.iglass.net

Re: [SQL] Flatten table using timestamp and source

2012-05-25 Thread Raj Mathur (राज माथुर)

On Friday 25 May 2012, Raj Mathur (राज माथुर) wrote:
> On Thursday 24 May 2012, Elrich Marx wrote:
> > If source changes, in this case from 1 to 2, then etime would be
> > the last value of stime for source =1; So for source 1 it starts
> > at stime 13:00 and continues till 13:02 (etime).
> > 
> > This should result in 3 records, because source is 1, then 2, then
> > 1 again. I hope this explains ?
> 
> I think I understand.  Here's a partially working example -- it
> doesn't compute the last interval.  Probably amenable to some severe
> optimisation too, but then I don't claim to be an SQL expert :)

With the last interval computation:

QUERY
-
with
  first_last as
  (
select * from
(
  select
source,
time,
case
  when
lag(source) over (order by time) != source
or lag(source) over (order by time) is null
  then 1 else 0
end as is_first,
case
  when
lead(source) over (order by time) != source
or lead(source) over (order by time) is null
  then 1 else 0
end as is_last
  from
p
) foo
where
  is_first != 0 or is_last != 0
  )
select
  t1.source,
  start_time,
  end_time
from
  (
select
  source,
  time as start_time
from
  first_last
where
  is_first = 1
  ) t1
  join
  (
select
  source,
  time as end_time,
  is_last
from
  first_last
where
  is_last = 1
  ) t2
  on
  (
t1.source = t2.source
and t2.end_time > t1.start_time
and
(
  (
t2.end_time < 
(
  select
time
  from
first_last
  where
source != t2.source
and time > t1.start_time
  order by
time
  limit
1
)
  )
  or
  (
t1.start_time = 
(
  select
time
  from
first_last
  where
is_first = 1
  order by
time desc
  limit
1
)
and t2.end_time =
(
  select
time
  from
first_last
  where
is_last = 1
  order by
time desc
  limit
1
)
  )
)
  )
;

RESULT (with same data set as before)
--
 source | start_time  |  end_time   
+-+-
  1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01
  6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01
  2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01
  6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01
  4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01
  0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01
  7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01
  8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01
  1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01
  8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01
  6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01
  4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01
  9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01
  2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01
  1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01
  4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01
  0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01
  9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01
  1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01
  8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01
  0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01
  2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01
  4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01
  2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01
  7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01
  9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01
  7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01

Would be interested in seeing how to shorten and/or optimise this query.

Regards,

-- Raj
-- 
Raj Mathur  || r...@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves   || http://schizoid.in   || D17F

-- 
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] Flatten table using timestamp and source

2012-05-25 Thread Raj Mathur (राज माथुर)

On Saturday 26 May 2012, Raj Mathur (राज माथुर) wrote:
> On Friday 25 May 2012, Raj Mathur (राज माथुर) wrote:
> > On Thursday 24 May 2012, Elrich Marx wrote:
> > > If source changes, in this case from 1 to 2, then etime would be
> > > the last value of stime for source =1; So for source 1 it starts
> > > at stime 13:00 and continues till 13:02 (etime).
> > > 
> > > This should result in 3 records, because source is 1, then 2,
> > > then 1 again. I hope this explains ?
> > 
> > I think I understand.  Here's a partially working example -- it
> > doesn't compute the last interval.  Probably amenable to some
> > severe optimisation too, but then I don't claim to be an SQL
> > expert :)
> 
> With the last interval computation:

Wokeh, much better solution (IMNSHO).  Results are the same as earlier, 
probably still amenable to optimisation and simplification.

Incidentally, thanks for handing out the problem!  It was a good brain-
teaser (and also a good opportunity to figure out window functions, 
which I hadn't worked with earlier).

QUERY
-
--
-- Compute rows that are the first or the last in an interval.
--
with
  first_last as
  (
select * from
(
  select
source,
time,
case
  when
lag(source) over (order by time) != source
or lag(source) over (order by time) is null
  then 1 else 0
end as is_first,
case
  when
lead(source) over (order by time) != source
or lead(source) over (order by time) is null
  then 1 else 0
end as is_last
  from
p
) foo
where
  is_first != 0 or is_last != 0
  )
--
-- Main query
--
select
  source,
  start_time,
  end_time
from
(
-- Get each row and the time from the next one
  select
source,
time as start_time,
lead(time)
  over(order by time) as end_time,
is_first
  from
first_last
) bar
-- Discard rows generated by the is_last row in the inner query
where
  is_first = 1;
;

> RESULT (with same data set as before)
> --
>  source | start_time  |  end_time
> +-+-
>   1 | 1970-01-01 05:30:01 | 1970-01-01 05:32:01
>   6 | 1970-01-01 05:33:01 | 1970-01-01 05:37:01
>   2 | 1970-01-01 05:38:01 | 1970-01-01 05:41:01
>   6 | 1970-01-01 05:42:01 | 1970-01-01 05:46:01
>   4 | 1970-01-01 05:47:01 | 1970-01-01 05:51:01
>   0 | 1970-01-01 05:52:01 | 1970-01-01 05:55:01
>   7 | 1970-01-01 05:56:01 | 1970-01-01 05:58:01
>   8 | 1970-01-01 05:59:01 | 1970-01-01 06:03:01
>   1 | 1970-01-01 06:04:01 | 1970-01-01 06:10:01
>   8 | 1970-01-01 06:11:01 | 1970-01-01 06:13:01
>   6 | 1970-01-01 06:14:01 | 1970-01-01 06:16:01
>   4 | 1970-01-01 06:17:01 | 1970-01-01 06:18:01
>   9 | 1970-01-01 06:19:01 | 1970-01-01 06:22:01
>   2 | 1970-01-01 06:23:01 | 1970-01-01 06:25:01
>   1 | 1970-01-01 06:26:01 | 1970-01-01 06:29:01
>   4 | 1970-01-01 06:30:01 | 1970-01-01 06:34:01
>   0 | 1970-01-01 06:35:01 | 1970-01-01 06:37:01
>   9 | 1970-01-01 06:38:01 | 1970-01-01 06:42:01
>   1 | 1970-01-01 06:43:01 | 1970-01-01 06:45:01
>   8 | 1970-01-01 06:46:01 | 1970-01-01 06:50:01
>   0 | 1970-01-01 06:51:01 | 1970-01-01 06:57:01
>   2 | 1970-01-01 06:58:01 | 1970-01-01 07:05:01
>   4 | 1970-01-01 07:06:01 | 1970-01-01 07:07:01
>   2 | 1970-01-01 07:08:01 | 1970-01-01 07:12:01
>   7 | 1970-01-01 07:13:01 | 1970-01-01 07:14:01
>   9 | 1970-01-01 07:15:01 | 1970-01-01 07:17:01
>   7 | 1970-01-01 07:18:01 | 1970-01-01 07:21:01

Regards,

-- Raj
-- 
Raj Mathur  || r...@kandalaya.org   || GPG:
http://otheronepercent.blogspot.com || http://kandalaya.org || CC68
It is the mind that moves   || http://schizoid.in   || D17F

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

[SQL] Job control in sql

[SQL] Select row cells as new columns

Re: [SQL] Select row cells as new columns

Re: [SQL] Job control in sql

Re: [SQL] Select row cells as new columns

Re: [SQL] Inherited table identification possible

Re: [SQL] Flatten table using timestamp and source

Re: [SQL] Flatten table using timestamp and source

8 matches

Site Navigation

Mail list logo

Footer information