headers
Brian Sherwood | 17 May 2013 17:26
Picon

How to right justify text in psql?

I am running postgresql 9.2.

I am assuming it would be a function of psql to right justify text, but I can't find any way to do this.

Is there a way to right justify just one text column?


Thanks

Brian
Permalink | Reply | 2 comments |
headers
George Woodring | 14 May 2013 16:08
Favicon

array_agg() with join question

To summarize my question at the top, why is it that when I did the JOIN, the array_agg results reversed order? 

I had a function that ran the following query:

SELECT timeslot, pollgrpid, array_agg(outval) 
   FROM 
      (SELECT * FROM rrd.a_current WHERE pollgrpid=8 ORDER BY timeslot, dsnum) AS foo 
   WHERE timeslot >= now() - '1 hour'::interval AND timeslot <= now() + '1 hour'::interval 
   GROUP BY timeslot, pollgrpid 
   ORDER BY timeslot;

        timeslot        | pollgrpid |         array_agg          
------------------------+-----------+----------------------------
 2013-05-14 08:58:00-04 |         8 | {1,1,0.00125217437744141}
 2013-05-14 09:00:00-04 |         8 | {1,1,0.000999927520751953}
 2013-05-14 09:02:00-04 |         8 | {1,1,0.00318312644958496}
 2013-05-14 09:04:00-04 |         8 | {1,1,0.000761985778808594}
 2013-05-14 09:06:00-04 |         8 | {1,1,0.000777959823608398}
 2013-05-14 09:08:00-04 |         8 | {1,1,0.101096868515015}
 2013-05-14 09:10:00-04 |         8 | {1,1,0.86168384552002}
 2013-05-14 09:12:00-04 |         8 | {1,1,0.00656795501708984}
 2013-05-14 09:14:00-04 |         8 | {1,1,0.102259159088135}
 2013-05-14 09:16:00-04 |         8 | {1,1,0.000636100769042969}

I wanted to include missing timestamps in my results, so I joined it with generate_series.

SELECT timeslot, pollgrpid, array_agg(outval) 
   FROM 
      ( SELECT generate_series(rrd_timeslot('avail', now() - '58 minutes'::interval), now() + '1 hour'::interval, '2 minutes') AS timeslot ) AS bar 
   LEFT JOIN 
      (SELECT * FROM rrd.a_current WHERE pollgrpid=8 AND timeslot >= now() - '1 hour'::interval AND timeslot <= now() + '1 hour'::interval ORDER BY timeslot, dsnum) AS foo 
   USING(timeslot) 
   GROUP BY timeslot, pollgrpid 
   ORDER BY timeslot;

        timeslot        | pollgrpid |         array_agg          
------------------------+-----------+----------------------------
 2013-05-14 09:02:00-04 |         8 | {0.00318312644958496,1,1}
 2013-05-14 09:04:00-04 |         8 | {0.000761985778808594,1,1}
 2013-05-14 09:06:00-04 |         8 | {0.000777959823608398,1,1}
 2013-05-14 09:08:00-04 |         8 | {0.101096868515015,1,1}
 2013-05-14 09:10:00-04 |         8 | {0.86168384552002,1,1}
 2013-05-14 09:12:00-04 |         8 | {0.00656795501708984,1,1}
 2013-05-14 09:14:00-04 |         8 | {0.102259159088135,1,1}
 2013-05-14 09:16:00-04 |         8 | {0.000636100769042969,1,1}
 2013-05-14 09:18:00-04 |         8 | {0.000638008117675781,1,1}
 2013-05-14 09:20:00-04 |         8 | {0.174574136734009,1,1}
 2013-05-14 09:22:00-04 |         8 | {0.1006920337677,1,1}
 2013-05-14 09:24:00-04 |         8 | {0.00069117546081543,1,1}
 2013-05-14 09:26:00-04 |         8 | {0.114289045333862,1,1}
 2013-05-14 09:28:00-04 |         8 | {0.116230010986328,1,1}
 2013-05-14 09:30:00-04 |         8 | {0.0349528789520264,1,1}

The array_agg results are reversed.  I had to ODER BY timeslot, dsnum desc on the right of the join to make it match. I am curious as to why this happened.  I am running 9.2.4.

Thanks,
Woody 

iGLASS Networks
www.iglass.net
Permalink | Reply | 2 comments |
headers
Marcin Krawczyk | 13 May 2013 11:22
Picon

transaction isolationa level - SERIALIZABLE

Hi list,

I have some problems with SERIALIZABLE isolation level, namely my users are plagued with concurrency errors. As of postgres 9.1 (which I'm running) there has been a change to SERIALIZABLE logic, unfortunately my application has not been updated to work with the new logic. I don't have an access to it's code and the only thing I can do is to report the issue to the authors. But before I do it, since I don't actually need SERIALIZABLE for my use, is it possible to have transactions always run in default READ COMMITTED mode, regardless of application level SET SESSION CHARACTERISTICS AS TRANSACTION command ... ? (like e.g in postgres 8.1 where SERIALIZABLE = READ COMMITED)


regards
mk
Permalink | Reply | 2 comments |
headers
JORGE MALDONADO | 6 May 2013 02:04
Picon

Order of execution

I have an UPDATE query which performs several opertions in one table.

UPDATE table01 SET
field1 = (query1 may contain any field),
field2 = (query1 may contain any field),
field3 = (query1 may contain any field)
WHERE (condition)

query1, query2 and query3 perform a logic to determine which records will be updated. Does field1 updates first, then  field2 and, lastly, field3? or What is the order in which updates are executed? If I need fields to be updated in a certain order, should I use 3 UPDATE commands instead?

Respectfully,
Jorge Maldonado
Permalink | Reply | 1 comment |
headers
Wolfgang Keller | 30 Apr 2013 16:39
Picon

Correct implementation of 1:n relationship with n>0?

It hit me today that a 1:n relationship can't be implemented just by a
single foreign key constraint if n>0. I must have been sleeping very
deeply not to notice this.

E.g. if there is a table "list" and another table "list_item" and the
relationship can be described as "every list has at least one
list_item" (and every list_item can only be part of one list, but
this is trivial).

A "correct" solution would require (at least?):

1. A foreign key pointing from each list_item to its list

2. Another foreign key pointing from each list to one of its list_item.
But this must be a list_item that itself points to the same list, so
just a simple foreign key constraint doesn't do it.

3. When a list has more than one list_item, and you want to delete the
list_item that its list points to, you have to "re-point" the foreign
key constraint on the list first. Do I need to use stored proceures
then for all insert, update, delete actions?

(4. Anything else that I've not seen?)

Is there a "straight" (and tested) solution for this in PostgreSQL, that
someone has already implemented and that can be re-used?

No, I definitely don't want to get into programming PL/PgSQL myself.
especially if the solution has to warrant data integrity under all
circumstances. Such as concurrent update, insert, delete etc.

TIA,

Sincerely,

Wolfgang

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Permalink | Reply | 11 comments |
headers
Mauricio Cruz | 25 Apr 2013 18:17
Picon

execute

Hi every one, 

 

I'm trying to do the follow:

 

declare 

   aCad text[][]

begin

...

execute 'aCad:=aCad array[['||var1||','||var2||']]'

...

end; language plpgsql

but I get the follow error: 

sintax erro near to "aCad"

aCad := aCad || array[rCad.cmp,rSql.codfor]  

^

 

I guess I should not use "execute" besides of  insert, update, delete ...  but how could I do in this case ?

 

Thanks every one.

 

-- Grato, Mauricio Cruz Sygecom Informática 51 3442-3975 / 3442-2345
Permalink | Reply | 3 comments |
headers
shabeer ece | 23 Apr 2013 10:01
Picon

Not Able to Delink the old tables

Hi,

When i tried to delink the old tables i'm getting the following error

NOTICE: SELECT COUNT(*) AS count from pg_tables where schemaname='fr' and tablename='check_deny_2013_04_17' CONTEXT: PL/pgSQL function "delink_partitions_tables" line 16 at IF NOTICE: Lock SqlLOCK TABLE fr.check_deny_2013_04_17 NOWAIT ; NOTICE: -20000

delink_partitions_tables

(1 row)

But in the pg_stat_activity there is no locked process & there is no process running related to this process...

Thanks in Advance :) 

SHABEER.

Permalink | Reply | 0 comments |
headers
Wolfgang Meiners | 22 Apr 2013 12:19
Picon

check for overlapping time intervals

Hi,

I am on postgresql 9.1 and use at table like

CREATE TABLE timetable(
 tid   INTEGER PRIMARY KEY,
 gid   INTEGER REFERENCES groups(gid),
 day   DATE,
 s     TIME NOT NULL, --- start
 e     TIME NOT NULL, --- end
 CHECK (e > s));

Now, i need a constraint to prevent overlapping timeintervals in this
table. For this, i use a trigger:

CREATE OR REPLACE FUNCTION validate_timetable() RETURNS trigger AS $$
BEGIN
 IF TG_OP = 'INSERT' THEN
  IF EXISTS(
   SELECT * FROM timetable
   WHERE gid = NEW.gid AND day = NEW.day
   AND s < NEW.e AND e > NEW.s)
   THEN
    RAISE EXCEPTION 'overlapping intervals';
   END IF;
 ELSIF TG_OP = 'UPDATE' THEN
  IF EXISTS(
   SELECT * FROM timetable
   WHERE gid = NEW.gid AND day = NEW.day
   AND tid <> OLD. tid
   AND s < NEW.e AND e > NEW.s)
   THEN
    RAISE EXCEPTION 'overlapping intervals';
   END IF;
 END IF;
 RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER validate_timetable
BEFORE INSERT OR UPDATE ON timetable
FOR EACH ROW EXECUTE PROCEDURE
validate_timetable();

Is there a simpler way to check for overlapping timeintervals? I ask
this question, because i have more similar tables with similar layout
and would have to write similar functions again and again.

Thank you for any hints
Wolfgang

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Permalink | Reply | 2 comments |
headers
JORGE MALDONADO | 20 Apr 2013 18:32
Picon

Table indexes in a SELECT with JOIN´s

Let´s suppose that I have a SELECT statement that joins more than one table and such a statement is order by fields that belong not only to the table in the FROM but also by fields in the tables that are part of the JOIN´s. How does indexes should be considered in a case like this? For example:

SELECT artist_name, author_name, producer_name, song_name
FROM tbl_songs
INNER JOIN tbl_artists ON tbl_artists.artistid = tbl_songs_artistid
INNER JOIN tbl_authors ON tbl_authors.authorid = tbl_songs_authorid
INNER JOIN tbl_producers ON tbl_producers.producerid = tbl_songs_producerid
ORDER BY song_name

Respectfully,
Jorge Maldonado
Permalink | Reply | 1 comment |
headers
chinnaobi | 19 Apr 2013 05:41
Picon

SQL query with Overlapping date time ranges

I have a temporary table with columns zone_name, nodeid, nodelabel, nodegainedservice, nodelostservice Zone1, 3, Windows-SRV1, "2012-11-27 13:10:30+08", "2012-11-27 13:00:40+08" Zone1, 5, Windows-SRV2, "2012-12-20 13:10:30+08", "2012-12-18 13:00:40+08" .... .... Many zones and many nodes and same nodes with gained service and lost service many times. `nodegainedservice` meaning node has come alive and `nodelostservice` meaning node has gone down. How could I make a query to fetch each zone availability in a period? e.g., Zone1 have Windows-SRV1, Windows-SRV2. Find how many times and how long both servers are down at the same time or Zone1 is down. Please use the below sample data zonename, nodeid, nodelabel, noderegainedservice, nodelostservice Zone1 27 Srv1 2013-02-21 10:04:56+08 2013-02-21 09:48:48+08 Zone1 27 Srv1 2013-02-21 10:14:01+08 2013-02-21 10:09:27+08 Zone1 27 Srv1 2013-02-21 10:26:29+08 2013-02-21 10:24:20+08 Zone1 27 Srv1 2013-02-21 11:27:24+08 2013-02-21 11:25:15+08 Zone1 27 Srv1 2013-02-28 16:24:59+08 2013-02-28 15:52:59+08 Zone1 27 Srv1 2013-02-28 16:56:19+08 2013-02-28 16:40:18+08 Zone1 27 Srv1 2013-02-28 17:09:28+08 2013-02-28 16:58:38+08 Zone1 27 Srv1 2013-02-28 17:39:50+08 2013-02-28 17:29:47+08 Zone1 27 Srv1 2013-03-01 09:39:36+08 2013-02-28 19:12:26+08 Zone1 27 Srv1 2013-03-01 13:35:07+08 2013-03-01 12:10:03+08 Zone1 27 Srv1 2013-03-04 11:04:14+08 2013-03-04 10:48:07+08 Zone1 27 Srv1 2013-03-06 16:36:56+08 2013-03-06 16:33:10+08 Zone1 27 Srv1 2013-03-13 13:54:11+08 2013-03-06 16:43:51+08 Zone1 27 Srv1 2013-03-14 11:43:28+08 2013-03-13 19:09:31+08 Zone1 27 Srv1 2013-03-18 18:38:16+08 2013-03-15 18:55:31+08 Zone1 27 Srv1 2013-03-22 11:18:57+08 2013-03-22 09:53:38+08 Zone1 27 Srv1 2013-03-28 16:48:27+08 2013-03-26 10:23:47+08 Zone1 27 Srv1 2013-04-04 10:33:24+08 2013-04-04 10:32:51+08 Zone1 27 Srv1 2013-04-04 11:48:54+08 2013-04-04 11:48:23+08 Zone1 27 Srv1 2013-04-08 19:01:34+08 2013-04-08 19:01:03+08 Zone1 27 Srv1 2013-04-08 19:37:05+08 2013-04-08 19:31:38+08 Zone1 27 Srv1 2013-04-08 21:48:07+08 2013-04-08 21:47:35+08 Zone1 27 Srv1 2013-04-08 21:54:02+08 2013-04-08 21:52:29+08 Zone1 27 Srv1 2013-04-10 09:33:53+08 2013-04-10 09:32:34+08 Zone1 27 Srv1 2013-04-10 12:01:01+08 2013-04-10 12:00:30+08 Zone1 27 Srv1 2013-04-10 14:57:25+08 2013-04-10 14:56:53+08 Zone1 27 Srv1 2013-04-10 16:25:50+08 2013-04-10 16:24:31+08 Zone1 27 Srv1 2013-04-10 16:57:02+08 2013-04-10 16:56:19+08 Zone1 27 Srv1 2013-04-10 17:17:37+08 2013-04-10 17:15:18+08 Zone1 27 Srv1 2013-04-11 21:35:43+08 2013-04-11 21:31:50+08 Zone1 39 Srv2 2013-04-05 13:15:53+08 2013-04-05 12:26:04+08 Zone1 39 Srv2 2013-04-05 13:23:10+08 2013-04-05 13:21:14+08 Zone1 39 Srv2 2013-04-05 13:35:23+08 2013-04-05 13:33:32+08 Zone1 39 Srv2 2013-04-05 15:17:25+08 2013-04-05 14:25:51+08 Zone1 39 Srv2 2013-04-07 16:49:56+08 2013-04-05 17:43:01+08 Zone1 39 Srv2 2013-04-09 22:32:19+08 2013-04-07 20:00:44+08 Zone1 39 Srv2 2013-04-09 22:38:02+08 2013-04-09 22:37:40+08 Zone1 39 Srv2 2013-04-10 11:16:21+08 2013-04-10 11:13:32+08 Zone1 39 Srv2 2013-04-10 16:15:37+08 2013-04-10 15:44:05+08 Zone1 39 Srv2 2013-04-10 16:23:07+08 2013-04-10 16:20:59+08 Zone1 39 Srv2 2013-04-10 16:48:46+08 2013-04-10 16:33:29+08 Zone1 39 Srv2 2013-04-10 17:19:11+08 2013-04-10 17:04:10+08 Zone1 39 Srv2 2013-04-11 21:39:21+08 2013-04-11 21:28:51+08 Zone1 39 Srv2 2013-04-11 22:05:02+08 2013-04-11 21:49:44+08 Zone1 39 Srv2 2013-04-15 14:02:11+08 2013-04-12 16:41:48+08 Zone1 39 Srv2 2013-04-17 00:00:00+08 2013-04-15 20:50:40+08 Zone1 29 Srv3 2013-03-12 17:20:02+08 2013-03-12 17:16:49+08 Zone1 29 Srv3 2013-03-12 18:08:30+08 2013-03-12 17:55:43+08 Zone1 13 Srv4 2013-01-09 17:23:59+08 2013-01-09 17:19:13+08 Zone1 13 Srv4 2013-01-10 16:54:27+08 2013-01-10 16:53:48+08 Zone1 13 Srv4 2013-01-10 16:59:55+08 2013-01-10 16:56:56+08 Zone1 13 Srv4 2013-01-10 17:07:10+08 2013-01-10 17:04:11+08 Zone1 13 Srv4 2013-01-10 17:13:54+08 2013-01-10 17:10:42+08 Zone1 13 Srv4 2013-01-16 10:31:45+08 2013-01-15 14:47:25+08 Zone1 13 Srv4 2013-01-24 17:52:35+08 2013-01-24 17:20:31+08 Zone1 13 Srv4 2013-01-28 17:24:25+08 2013-01-28 16:53:10+08 Zone1 13 Srv4 2013-02-18 12:16:45+08 2013-02-18 12:10:05+08 Zone1 13 Srv4 2013-02-18 15:00:26+08 2013-02-18 14:12:04+08 Zone1 13 Srv4 2013-02-18 17:11:10+08 2013-02-18 17:00:58+08 Zone1 13 Srv4 2013-02-21 10:14:24+08 2013-02-21 10:13:45+08 Zone1 13 Srv4 2013-02-25 14:29:39+08 2013-02-25 13:44:50+08 Zone1 13 Srv4 2013-02-26 10:40:08+08 2013-02-26 10:19:33+08 Zone1 13 Srv4 2013-03-04 11:37:34+08 2013-03-04 11:00:56+08 Zone1 13 Srv4 2013-04-10 16:25:27+08 2013-04-10 16:24:07+08 Zone1 13 Srv4 2013-04-10 17:17:39+08 2013-04-10 17:14:40+08 Zone1 13 Srv4 2013-04-11 21:39:05+08 2013-04-11 21:28:22+08

View this message in context: SQL query with Overlapping date time ranges
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Permalink | Reply | 0 comments |
headers
Sofer, Yuval | 18 Apr 2013 17:41
Picon
Favicon

Primary DB stuck becuase of unavailable standby (synchronized streaming) - please help

Hi,

 

I am using Postgres DB with stand by database, configured with streaming in synchronized mode (each commit on primary DB waits for commit on secondary DB).

 

Sometimes we suffer from network issues and as consequences, secondary machine is not available.

 

In these situations, our application is stuck and do not respond anymore, since no confirmation comes from the secondary Postgres (obviously… it is down or unavailable….).

 

Is there any parameter that I can use, to enable recovering from hanging on until someone of us re-establish connection to secondary DB?

Maybe some configuration parameter that sets the time interval for being stuck because of unavailable secondary Postgres?

 

Please help,

 

Thanks,

 

 

Yuval Sofer

BMC Software

CTM&D Business Unit

DBA Team

972-52-4286-282

yuval_sofer <at> bmc.com

 

 

 

 

Permalink | Reply | 1 comment |

Gmane