Re: [GENERAL] Using partial index in combination with prepared statement parameters

[Tom Lane]

Steven Schlansker ste...@likeness.com writes:
 It's been covered a few times in the past,
 http://www.postgresql.org/message-id/banlktimft4ohqkb6y7m4wqrffpwnutp...@mail.gmail.com
 http://postgresql.1045698.n5.nabble.com/partial-indexes-not-used-on-parameterized-queries-td2121027.html
 but in a nutshell, partial indices do not play nicely with prepared
 statements because whether the index is valid or not cannot be known
 at query plan time.

This should be pretty much a non-issue in 9.2 and up; if the partial
index is actually useful enough to be worth worrying about, the
plancache choice logic will realize that it should use custom not
generic plans.

http://git.postgresql.org/gitweb/?p=postgresql.gita=commitdiffh=e6faf910d

You might still have an issue if the partial index is only sometimes
usable --- the choice logic might decide to go for the generic-plan
approach anyway.  But if you've got a case where the optimal plan
is all over the map like that, I wonder why you're using a prepared
statement at all ...

regards, tom lane


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

Re: [GENERAL] Weird explain output

[wd]

Thanks for your reply.


On Mon, Feb 4, 2013 at 3:48 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 You're worrying about the wrong thing entirely.  The right thing to be
 worrying about is why are some of those row estimates off by four orders
 of magnitude, and what you can do to fix that.  The planner will never
 deliver sane plans when its estimates are so far off from reality.


You mean the rows from explain is bigger then the rows real returned?
It caused by a sub query with a filter like ( a and b ) or ( c and d ),
I've tried to create a temp table for it, the explained rows and the real
rows will similarly.

Here is the full output, http://explain.depesz.com/s/M7oo


 From my perspective, you snipped all the interesting parts of this
 output, because the misestimation is evidently happening somewhere down
 in there.

 regards, tom lane

[GENERAL] grouping consecutive records

[Morus Walter]

Hallo,

I have a question regarding a selection.

I'd like to group and merge certain records having the same values in
some columns, but only if they are contiguous with regard to some sort
order.

So for a table
create table foo ( 
   id int,
   user_id int, 
   key varchar, 
   sort int );

and values e.g.
insert into foo 
   values ( 1, 1, 'foo', 1), 
  ( 2, 1, 'foo', 2), 
  ( 3, 1, 'bar', 3),
  ( 4, 1, 'foo', 4),
  ( 5, 1, 'foo', 5),
  ( 6, 1, 'foo', 6),
  ( 7, 1, 'bla', 7),
  ( 8, 2, 'bar', 1),
  ( 9, 2, 'foo', 2),
  (10, 2, 'foo', 3),
  (11, 2, 'bla', 4);

I'd like to merge all consecutive records (ordered by sort, user_id) 
having the same value in user_id and key and keep the first/last
value of sort of the merged records (and probably some more values 
from the first or last merged record).

So the result should be something like
user_id, key, sort_first, sort_last 
1, 'foo', 1, 2
1, 'bar', 3, 3
1, 'foo', 4, 6
1, 'bla', 7, 7
2, 'bar', 1, 1
2, 'foo', 2, 3
2, 'bla', 4, 4

I was trying to do that using window functions, which works great -
except it merges non consecutive occurences (key foo for user_id 1 in
my sample) as well.

select user_id, key, sort_first, sort_last
from ( 
  select user_id, 
 key,
 first_value(sort) over w as sort_first, 
 last_value(sort) over w as sort_last, 
 lead(key) over w as next_key 
  from foo 
  window w as (partition by user_id, key order by sort 
   range between unbounded preceding and unbounded following) 
) as foo 
where next_key is null 
order by user_id, sort_first;

user_id | key | sort_first | sort_last 
-+-++---
   1 | foo |  1 | 6 -- would like to have two records 
1/2 and 4/6 here
   1 | bar |  3 | 3
   1 | bla |  7 | 7
   2 | bar |  1 | 1
   2 | foo |  2 | 3
   2 | bla |  4 | 4

Introducing another window on user_id only allows me to keep two records for
1/foo but I still cannot determine the intended sort_first/sort_last.

select user_id, key, sort_first, sort_last 
from ( 
  select user_id, 
 key, 
 first_value(sort) over w as sort_first, 
 last_value(sort) over w as sort_last, 
 lead(key) over u as next_key 
  from foo 
  window u as (partition by user_id order by sort), 
 w as (partition by user_id, key order by sort 
   range between unbounded preceding and unbounded following) 
) as foo 
where next_key is null or key != next_key 
order by user_id, sort_first;

 user_id | key | sort_first | sort_last 
-+-++---
   1 | foo |  1 | 6
   1 | foo |  1 | 6
   1 | bar |  3 | 3
   1 | bla |  7 | 7
   2 | bar |  1 | 1
   2 | foo |  2 | 3
   2 | bla |  4 | 4

So the question is: is this doable with a selection?
Can I use window functions for this type of grouping?
Are there other options?

I do have an alternative plan to select records into a temporary table first,
and then do updates merging two consecutive records and repeat that until
all groups are completely merged, but I'd still like to know if I miss 
something regarding selection options.

best
Morus

PS: the alternative plan is something like

select id, user_id, 
 key, 
 sort, 
 sort as sort_last, 
 lead(key) over u as next_key,
 lead(id) over u as next_id,
 lag(key) over u as prev_key
into temp table footmp
  from foo 
  window u as (partition by user_id order by sort);


update footmp set sort = f2.sort, prev_key = f2.prev_key 
from footmp f2 
where footmp.id = f2.next_id and 
  footmp.key = f2.key and 
  f2.key = f2.next_key and 
  ( f2.prev_key is null or f2.prev_key != f2.key );

delete from footmp
where id in (
  select id from ( 
select first_value(id) over w as id, 
   count(*) over w as cnt 
from footmp
window w as ( partition by user_id, sort ) 
  ) as foo where cnt  1
);

(repeat update/delete until no row is affected)

select user_id, 
   key, 
   sort as sort_first, 
   sort_last 
from footmp 
order by user_id, sort_first;


pretty ugly and complicated but at least gives me what I want...


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

Re: [GENERAL] Diferences between IN and EXISTS?

[zeljko]

Edson Richter wrote:

 Hi!
 
 Assumptions: PostgreSQL 9.2.2, Win64, already run vacumm full freeze
 analyze. No problems in the database. I know there are 1247 records to
 be found.
 Why does these queries return different results:
 
 
 select count(*) from parcela
 where id not in (select parcela_id from cadastroservicoparcela);

I'm always using
WHERE NOT id in (blabla) and never had such problems.

zeljko


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

Re: [GENERAL] Diferences between IN and EXISTS?

[Thomas Kellerer]

zeljko, 04.02.2013 10:35:

Edson Richter wrote:


Hi!

Assumptions: PostgreSQL 9.2.2, Win64, already run vacumm full freeze
analyze. No problems in the database. I know there are 1247 records to
be found.
Why does these queries return different results:


select count(*) from parcela
where id not in (select parcela_id from cadastroservicoparcela);


I'm always using
WHERE NOT id in (blabla) and never had such problems.



If blabla returns NULL values, then you will have problems eventually.




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

Re: [GENERAL] grouping consecutive records

[Виктор Егоров]

2013/2/4 Morus Walter morus.walter...@googlemail.com:
 I'd like to merge all consecutive records (ordered by sort, user_id)
 having the same value in user_id and key and keep the first/last
 value of sort of the merged records (and probably some more values
 from the first or last merged record).

 So the result should be something like
 user_id, key, sort_first, sort_last
 1, 'foo', 1, 2
 1, 'bar', 3, 3
 1, 'foo', 4, 6
 1, 'bla', 7, 7
 2, 'bar', 1, 1
 2, 'foo', 2, 3
 2, 'bla', 4, 4

This example corresponds to the ORDER BY user_id, sort
while you claim you need to ORDER BY sort, user_id.

I will explain this for the ordering that matches your sample.

You need to group your data, but you should first create an artificial
grouping column.

First, detect ranges of your buckets:
WITH ranges AS (
SELECT id, user_id, key, sort,
   CASE WHEN lag(key) OVER
(PARTITION BY user_id ORDER BY user_id, sort) = key
THEN NULL ELSE 1 END r
  FROM foo
)
SELECT * FROM ranges;

Here each time a new “range” is found, «r» is 1, otherwise it is NULL.

Now, form your grouping column:
WITH ranges AS (
SELECT id, user_id, key, sort,
   CASE WHEN lag(key) OVER
(PARTITION BY user_id ORDER BY user_id, sort) = key
THEN NULL ELSE 1 END r
  FROM foo
)
, groups AS (
SELECT id, user_id, key, sort, r,
   sum(r) OVER (ORDER BY user_id, sort) grp
  FROM ranges
)
SELECT * FROM groups;

Here sum() is used as running total to produce new “grp” values.

Final query looks like this:
WITH ranges AS (
SELECT id, user_id, key, sort,
   CASE WHEN lag(key) OVER
(PARTITION BY user_id ORDER BY user_id, sort) = key
THEN NULL ELSE 1 END r
  FROM foo
)
, groups AS (
SELECT id, user_id, key, sort, r,
   sum(r) OVER (ORDER BY user_id, sort) grp
  FROM ranges
)
SELECT min(user_id) user_id, min(key) key,
   min(sort) sort_first,
   max(sort) sort_last
  FROM groups
 GROUP BY grp
 ORDER BY user_id,sort_first;

Based on this SO answer: http://stackoverflow.com/a/10624628/1154462


-- 
Victor Y. Yegorov


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

Re: [GENERAL] grouping consecutive records

[Morus Walter]

Hallo Виктор,

thanks a lot for your explanation :-)
You rock!
 
 This example corresponds to the ORDER BY user_id, sort
 while you claim you need to ORDER BY sort, user_id.
 
right, I confused the order.

 I will explain this for the ordering that matches your sample.
 
 You need to group your data, but you should first create an artificial
 grouping column.
 
 First, detect ranges of your buckets:
 WITH ranges AS (
 SELECT id, user_id, key, sort,
CASE WHEN lag(key) OVER
 (PARTITION BY user_id ORDER BY user_id, sort) = key
 THEN NULL ELSE 1 END r
   FROM foo
 )
 SELECT * FROM ranges;
 
 Here each time a new “range” is found, «r» is 1, otherwise it is NULL.
 
 Now, form your grouping column:
 WITH ranges AS (
 SELECT id, user_id, key, sort,
CASE WHEN lag(key) OVER
 (PARTITION BY user_id ORDER BY user_id, sort) = key
 THEN NULL ELSE 1 END r
   FROM foo
 )
 , groups AS (
 SELECT id, user_id, key, sort, r,
sum(r) OVER (ORDER BY user_id, sort) grp
   FROM ranges
 )
 SELECT * FROM groups;
 
so the trick is to flag changes in key and afterwards count them using
the dynamic nature of a frame ending with the current row.
great :-)
Once you have a group column, it's pretty clear then.

thanks
Morus


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

[GENERAL] WARNING: pgstat wait timeout

[Jake Stride]

I have had some issues with a database on EC2 and I have restored it to a
new instance. When vacuuming the database I am getting the following in the
logs;

WARNING:  pgstat wait timeout

Is this normal/acceptable?

Thanks

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

[Hari Babu]

On Friday, February 01, 2013 7:53 PM Adrian Klaver wrote:
On 02/01/2013 06:06 AM, Hari Babu wrote:

 We tried the approach as suggested by you but still it is not working as 
 shown in the below log (I had enabled logLevel as 1)
 keystore passowrd is qwerty
 19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002)
 19:26:23.451 (1) Receive Buffer Size is 43808
 19:26:23.452 (1) Send Buffer Size is 25386
 getConnection returning 
 driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
 Connection1 successful! Conn1:org.postgresql.jdbc4.Jdbc4Connection@6baa9f99
 null
 wrongqwerty
 DriverManager.getConnection(jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
 driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun.jdbc.odbc.JdbcOdbcDriver@3597a37c]
 *Driver.connect (jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
 driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
 19:26:23.835 (2) PostgreSQL 9.2 JDBC4 (build 1002)
 19:26:23.847 (2) Receive Buffer Size is 43808
 19:26:23.848 (2) Send Buffer Size is 25386
 getConnection returning 
 driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
 Connection2 successful! Conn2:org.postgresql.jdbc4.Jdbc4Connection@2e958bb8

 Connect OK

 There is function as SSL_CTX_SETSESSIONCACHEMODE(ctxt, mode) in C library of 
 SSL.
 Can you please let us  know if there is some similar function in JSSE also.

Per a previous post, have you verified that pg_hba.conf is set up to 
properly handle SSL password connections?

Yes, I have modified the pg_hba.conf as follows to handle the SSL connections.

# IPv4 local connections: 
hostsslall all 10.18.0.0/16   cert 

please provide your suggestions.

Regards,
Hari babu.




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

Re: [GENERAL] WARNING: pgstat wait timeout

[dinesh kumar]

Hi,

As of now, i found the following cases where we can expect these kind of
WARNING message in pg_log.

Case 1 { Huge I/O }

==

When the postgresql autovacuum process is not able to get the required I/O
to write the statistics to stats_temp_location then we can get this kind
of WARNING Message. As discussed, the huge I/O may causing due to the
checkpoint occurs on the Database.


How to log the checkpoints information .

===

1) Edit the postgresql.conf file as log_checkpoints=on

2) Select Pg_Reload_Conf();


Case 2 {Invalid stats_temp_location}

==

When the postgresql stats_temp_location is invalid path, then in this
case also we can expect this kind of WARNING Message. If you want to change
this location to some other place, then we need to follow the below
approach.


1) Edit the postgresql.conf file as stats_temp_location='PATH'

2) Select Pg_Reload_Conf();


Case 3 {Invalid Localhost IP}

==

There might be a chance, we have an invalid Localhost IP. Please check the
localhost entires in Hosts file and rectify it if any thing wrong.

Once we made any changes in this file then we need to restart the
PostgreSQL Cluster to take it's Effect on autovacuum worker processes.



I hope some one will add more on this.


Regards,

Dinesh Kumar

manojadinesh.blogspot.com





On Mon, Feb 4, 2013 at 5:24 PM, Jake Stride j...@stride.me.uk wrote:

 I have had some issues with a database on EC2 and I have restored it to a
 new instance. When vacuuming the database I am getting the following in the
 logs;

 WARNING:  pgstat wait timeout

 Is this normal/acceptable?

 Thanks

[GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Alexander Farber]

Hello,

when trying to add a forgotten primary key pair
to a PostgreSQL 8.4.13 table I get the error:

# \d pref_rep
   Table public.pref_rep
  Column   |Type | Modifiers
---+-+---
 id| character varying(32)   |
 author| character varying(32)   |
 good  | boolean |
 fair  | boolean |
 nice  | boolean |
 about | character varying(256)  |
 stamp | timestamp without time zone | default now()
 author_ip | inet|
 rep_id| integer | not null default
nextval('pref_rep_rep_id_seq'::regclass)
Check constraints:
pref_rep_check CHECK (id::text  author::text)
Foreign-key constraints:
pref_rep_author_fkey FOREIGN KEY (author) REFERENCES
pref_users(id) ON DELETE CASCADE
pref_rep_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) ON
DELETE CASCADE

# alter table pref_rep add primary key(id, author);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
pref_rep_pkey for table pref_rep
ERROR:  could not create unique index pref_rep_pkey
DETAIL:  Table contains duplicated values.

How could I find those duplicated pairs of id and author?

I've tried following, but this of course doesn't give me pairs:

# select id, count(id) from pref_rep group by id order by count desc limit 5;
   id   | count
+---
 OK408547485023 |   706
 OK261593357402 |   582
 DE11198|   561
 DE13041|   560
 OK347613386893 |   556
(5 rows)

Thank you
Alex

P.S. I've also asked my question also at SO,
 hope it is okay to crosspost that way :-)

http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values


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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Adrian Klaver]

On 02/04/2013 06:17 AM, Alexander Farber wrote:

Hello,

when trying to add a forgotten primary key pair
to a PostgreSQL 8.4.13 table I get the error:

# \d pref_rep
Table public.pref_rep
   Column   |Type | Modifiers
---+-+---
  id| character varying(32)   |
  author| character varying(32)   |
  good  | boolean |
  fair  | boolean |
  nice  | boolean |
  about | character varying(256)  |
  stamp | timestamp without time zone | default now()
  author_ip | inet|
  rep_id| integer | not null default
nextval('pref_rep_rep_id_seq'::regclass)
Check constraints:
 pref_rep_check CHECK (id::text  author::text)
Foreign-key constraints:
 pref_rep_author_fkey FOREIGN KEY (author) REFERENCES
pref_users(id) ON DELETE CASCADE
 pref_rep_id_fkey FOREIGN KEY (id) REFERENCES pref_users(id) ON
DELETE CASCADE

# alter table pref_rep add primary key(id, author);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
pref_rep_pkey for table pref_rep
ERROR:  could not create unique index pref_rep_pkey
DETAIL:  Table contains duplicated values.

How could I find those duplicated pairs of id and author?

I've tried following, but this of course doesn't give me pairs:

# select id, count(id) from pref_rep group by id order by count desc limit 5;
id   | count
+---
  OK408547485023 |   706
  OK261593357402 |   582
  DE11198|   561
  DE13041|   560
  OK347613386893 |   556
(5 rows)



SELECT * FROM  (SELECT count(*) AS ct, id, author FROM pref_rep GROUP BY 
id, author) AS dup WHERE dup.ct 1;




Thank you
Alex




--
Adrian Klaver
adrian.kla...@gmail.com


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

Re: [GENERAL] What language is faster, C or PL/PgSQL?

[Merlin Moncure]

On Sat, Feb 2, 2013 at 11:36 AM, Carlo Stonebanks
stonec.regis...@sympatico.ca wrote:
 Here is an advantage Plpgsql has:
 http://www.postgresql.org/docs/9.1/static/plpgsql-expressions.html

 I guess you can offset this by creating your own prepared statements in C.
 Otherwise, I can’t think of how C could be slower. I would choose C for
 functions that don’t have SQL statements in them – e.g. math and string
 processing.

For cases involving data processing (SPI calls), C can be slower
because pl/pgsql has a lot of optimizations in it that can be very
easy to miss.  I don't suggest writing backend C functions at all
unless you are trying to interface with a C library to access
functionality currently not exposed in SQL.

merlin


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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Alexander Farber]

Thank you -

On Mon, Feb 4, 2013 at 3:26 PM, Andrew Jaimes andrewjai...@hotmail.com wrote:
 SELECT id, author, count(1)
   FROM pref_rep
 GROUP BY id, author
 HAVING count(1) 1

 From: alexander.far...@gmail.com
 http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values

this has worked and has delivered me 190 records
(I still wonder how they could have happened,
because I only used a stored procedure
with UPDATE - if NOT FOUND - INSERT
Is it maybe pgbouncer's fault?):

   id   | author | count
++---
 DE10598| OK495480409724 | 2
 DE12188| MR17925810634439466500 | 3
 DE13529| OK471161192902 | 2
 DE13963| OK434087948702 | 2
 DE14037| DE7692 | 2
..
 VK45132921 | DE3544 | 2
 VK6152782  | OK261593357402 | 2
 VK72883921 | OK506067284178 | 2
(190 rows)

And then I'm trying to construct a query which
would delete the older (the stamp column)
of such pairs - but this also doesn't work:

#  SELECT id, author, count(1), stamp
  FROM pref_rep
GROUP BY id, author, stamp
HAVING count(1) 1;
   id   |author | count |   stamp
+---+---+
 OK14832267156  | OK419052078016| 2 | 2012-04-11 12:54:02.980239
 OK333460361587 | VK151946174   | 2 | 2012-07-04 07:08:22.172663
 OK351109431016 | OK165881471481| 2 | 2011-09-18 18:29:33.51362
 OK367507493096 | OK342027384470| 5 | 2012-02-10 20:58:11.488184
 OK430882956135 | OK331014635822| 2 | 2012-11-21 18:38:23.141298
 OK446355841129 | OK353460633855| 2 | 2012-06-15 21:31:56.791688
 OK450700410618 | OK511055704249| 2 | 2012-03-16 15:19:50.27776
 OK458979640673 | OK165881471481| 2 | 2011-08-18 22:31:17.540112
 OK468333888972 | MR5100358507294433874 | 2 | 2012-12-05 14:16:15.870061
 OK485109177380 | DE12383   | 2 | 2011-09-16 16:00:38.625038
 OK505164304516 | OK165881471481| 2 | 2012-03-24 13:54:27.968482
(11 rows)

Any suggestions please? Should I use a temp table here?

Thank you
Alex


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

Re: [GENERAL] [JDBC] JDBC connection test with SSL on PG 9.2.1 server

[Adrian Klaver]

On 02/04/2013 04:46 AM, Hari Babu wrote:

On Friday, February 01, 2013 7:53 PM Adrian Klaver wrote:

On 02/01/2013 06:06 AM, Hari Babu wrote:


We tried the approach as suggested by you but still it is not working as shown 
in the below log (I had enabled logLevel as 1)
keystore passowrd is qwerty
19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.451 (1) Receive Buffer Size is 43808
19:26:23.452 (1) Send Buffer Size is 25386
getConnection returning 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection1 successful! Conn1:org.postgresql.jdbc4.Jdbc4Connection@6baa9f99
null
wrongqwerty
DriverManager.getConnection(jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun.jdbc.odbc.JdbcOdbcDriver@3597a37c]
*Driver.connect (jdbc:postgresql://127.0.0.1:15432/postgres)
  trying 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
19:26:23.835 (2) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.847 (2) Receive Buffer Size is 43808
19:26:23.848 (2) Send Buffer Size is 25386
getConnection returning 
driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection2 successful! Conn2:org.postgresql.jdbc4.Jdbc4Connection@2e958bb8

Connect OK

There is function as SSL_CTX_SETSESSIONCACHEMODE(ctxt, mode) in C library of 
SSL.
Can you please let us  know if there is some similar function in JSSE also.



Per a previous post, have you verified that pg_hba.conf is set up to
properly handle SSL password connections?


Yes, I have modified the pg_hba.conf as follows to handle the SSL connections.

# IPv4 local connections:
hostsslall all 10.18.0.0/16   cert



This is your entire pg_hba,conf?

If so, note the part about no password prompt below:

http://www.postgresql.org/docs/9.2/interactive/auth-methods.html#AUTH-CERT

19.3.10. Certificate Authentication

This authentication method uses SSL client certificates to perform 
authentication. It is therefore only available for SSL connections. When 
using this authentication method, the server will require that the 
client provide a valid certificate. No password prompt will be sent to 
the client. The cn (Common Name) attribute of the certificate will be 
compared to the requested database user name, and if they match the 
login will be allowed. User name mapping can be used to allow cn to be 
different from the database user name.


The following configuration options are supported for SSL certificate 
authentication:


map
Allows for mapping between system and database user names. See Section 
19.2 for details.


I am guessing what you want is:

hostsslall all 10.18.0.0/16   md5

See here for more detail:

http://www.postgresql.org/docs/9.2/interactive/auth-pg-hba-conf.html

In particular:

The first record with a matching connection type, client address, 
requested database, and user name is used to perform authentication. 
There is no fall-through or backup: if one record is chosen and the 
authentication fails, subsequent records are not considered. If no 
record matches, access is denied.







please provide your suggestions.

Regards,
Hari babu.







--
Adrian Klaver
adrian.kla...@gmail.com


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

[GENERAL] partial time stamp query

[Kirk Wythers]

I am trying to write a query that grabs one particular day from a timestamp 
column. The data are ordered in 15 minute chunks like this:

2010-07-07 12:45:00
2010-07-07 13:00:00
2010-07-07 13:15:00
2010-07-07 13:30:00
etc…

If I wanted all records from july 7th 2010, I would expect 4 x 24 = 96 records 
per day.

I have tried the '=' operator, like this 

WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*'

but that grabs nothing, and using the '~' operator grabs everything with a 2010 
or 07 in it… in other words all days from July of 2010. 

Any suggestions would be much appreciated. 




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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Alexander Farber]

Trying to delete the older of the duplicated pairs:

# SELECT id, author, count(1), max(stamp) as maxx
  FROM pref_rep
GROUP BY id, author
HAVING count(1) 1 and stamp  maxx;
ERROR:  column maxx does not exist
LINE 4: HAVING count(1) 1 and stamp  maxx;
   ^

On Mon, Feb 4, 2013 at 3:36 PM, Alexander Farber
alexander.far...@gmail.com wrote:
 http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values



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

[GENERAL] partial time stamp query

[Kirk Wythers]

I am trying to write a query that grabs one particular day from a timestamp 
column. The data are ordered in 15 minute chunks like this:

2010-07-07 12:45:00
2010-07-07 13:00:00
2010-07-07 13:15:00
2010-07-07 13:30:00
etc…

If I wanted all records from july 7th 2010, I would expect 4 x 24 = 96 records 
per day.

I have tried the '=' operator, like this 

WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*'

but that grabs nothing, and using the '~' operator grabs everything with a 2010 
or 07 in it… in other words all days from July of 2010. 

Any suggestions would be much appreciated. 

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

Re: [GENERAL] partial time stamp query

[Misa Simic]

WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'::date

On Monday, February 4, 2013, Kirk Wythers wrote:

 I am trying to write a query that grabs one particular day from a
 timestamp column. The data are ordered in 15 minute chunks like this:

 2010-07-07 12:45:00
 2010-07-07 13:00:00
 2010-07-07 13:15:00
 2010-07-07 13:30:00
 etc…

 If I wanted all records from july 7th 2010, I would expect 4 x 24 = 96
 records per day.

 I have tried the '=' operator, like this

 WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*'

 but that grabs nothing, and using the '~' operator grabs everything with a
 2010 or 07 in it… in other words all days from July of 2010.

 Any suggestions would be much appreciated.

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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Adrian Klaver]

On 02/04/2013 06:45 AM, Alexander Farber wrote:

Trying to delete the older of the duplicated pairs:

# SELECT id, author, count(1), max(stamp) as maxx
   FROM pref_rep
GROUP BY id, author
HAVING count(1) 1 and stamp  maxx;
ERROR:  column maxx does not exist
LINE 4: HAVING count(1) 1 and stamp  maxx;


How about:
SELECT id, author, count(1), max(stamp) as maxx
  FROM pref_rep
GROUP BY id, author
HAVING count(1) 1 and stamp  max(stamp);


^

On Mon, Feb 4, 2013 at 3:36 PM, Alexander Farber
alexander.far...@gmail.com wrote:

http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values








--
Adrian Klaver
adrian.kla...@gmail.com


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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Alexander Farber]

Unfortunately  that fails -

On Mon, Feb 4, 2013 at 3:55 PM, Adrian Klaver adrian.kla...@gmail.com wrote:
 On 02/04/2013 06:45 AM, Alexander Farber wrote:

 Trying to delete the older of the duplicated pairs:


 How about:

 SELECT id, author, count(1), max(stamp) as maxx
   FROM pref_rep
 GROUP BY id, author
 HAVING count(1) 1 and stamp  max(stamp);

 On Mon, Feb 4, 2013 at 3:36 PM, Alexander Farber
 alexander.far...@gmail.com wrote:
 http://stackoverflow.com/questions/14688523/adding-primary-key-table-contains-duplicated-values

# SELECT id, author, count(1), max(stamp) as maxx
pref-   FROM pref_rep
pref- GROUP BY id, author
pref- HAVING count(1) 1 and stamp  max(stamp);
ERROR:  column pref_rep.stamp must appear in the GROUP BY clause or
be used in an aggregate function
LINE 4: HAVING count(1) 1 and stamp  max(stamp);


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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Adrian Klaver]

On 02/04/2013 06:45 AM, Alexander Farber wrote:

Trying to delete the older of the duplicated pairs:

# SELECT id, author, count(1), max(stamp) as maxx
   FROM pref_rep
GROUP BY id, author
HAVING count(1) 1 and stamp  maxx;
ERROR:  column maxx does not exist
LINE 4: HAVING count(1) 1 and stamp  maxx;
^


Caffeine has not reached critical mass yet, so test before using:

 SELECT *  FROM pref_rep JOIN (SELECT id, author, count(1) AS ct, 
max(stamp) AS maxx FROM pref_rep GROUP BY id,author) max_time ON 
max_time.id=pref_rep.id WHERE ct  1 AND stamp  maxx;





--
Adrian Klaver
adrian.kla...@gmail.com


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

Re: [GENERAL] Diferences between IN and EXISTS?

[zeljko]

Thomas Kellerer wrote:

 zeljko, 04.02.2013 10:35:
 Edson Richter wrote:

 Hi!

 Assumptions: PostgreSQL 9.2.2, Win64, already run vacumm full freeze
 analyze. No problems in the database. I know there are 1247 records to
 be found.
 Why does these queries return different results:


 select count(*) from parcela
 where id not in (select parcela_id from cadastroservicoparcela);

 I'm always using
 WHERE NOT id in (blabla) and never had such problems.

 
 If blabla returns NULL values, then you will have problems eventually.

but it doesn't, then blabla should say WHERE NOT some ISNULL.

zeljko


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

Re: [GENERAL] Diferences between IN and EXISTS?

[Edson Richter]

Em 04/02/2013 07:35, zeljko escreveu:

Edson Richter wrote:


Hi!

Assumptions: PostgreSQL 9.2.2, Win64, already run vacumm full freeze
analyze. No problems in the database. I know there are 1247 records to
be found.
Why does these queries return different results:


select count(*) from parcela
where id not in (select parcela_id from cadastroservicoparcela);

I'm always using
WHERE NOT id in (blabla) and never had such problems.


There relevant portion of the problem is here:

|IN|predicate (unlike|EXISTS|) is trivalent, i. e. it can 
return|TRUE|,|FALSE|or|NULL|:


 * |TRUE|is returned when the non-|NULL|value in question is found in
   the list
 * |FALSE|is returned when the non-|NULL|value is not found in the
   list/and the list does not contain|NULL|values/
 * |NULL|is returned when the value is|NULL|, or the non-|NULL|value is
   not found in the list/and the list contains at least one|NULL|value/


The 3rd point is the one I was hitting.

Edson




zeljko

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Andreas Kretschmer]

Alexander Farber alexander.far...@gmail.com wrote:

 # alter table pref_rep add primary key(id, author);
 NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
 pref_rep_pkey for table pref_rep
 ERROR:  could not create unique index pref_rep_pkey
 DETAIL:  Table contains duplicated values.
 
 How could I find those duplicated pairs of id and author?

similar example:

test=*# select * from foo;
 id1 | id2
-+-
   1 |   1
   1 |   2
   1 |   3
   2 |   1
   2 |   2
   2 |   3
   1 |   2
   3 |   1
   3 |   2
   3 |   3
   3 |   1
(11 rows)

Time: 0,151 ms
test=*# alter table foo add primary key (id1,id2);
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index foo_pkey 
for table foo
ERROR:  could not create unique index foo_pkey
DETAIL:  Key (id1, id2)=(1, 2) is duplicated.
Time: 1,394 ms
test=*# select id1, id2, count(*) as c from foo group by id1, id2 having 
count(*)  1;
 id1 | id2 | c
-+-+---
   3 |   1 | 2
   1 |   2 | 2
(2 rows)

Time: 0,331 ms


HTH.


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Andreas Kretschmer]

Andreas Kretschmer akretsch...@spamfence.net wrote:

 Alexander Farber alexander.far...@gmail.com wrote:
 
  # alter table pref_rep add primary key(id, author);
  NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
  pref_rep_pkey for table pref_rep
  ERROR:  could not create unique index pref_rep_pkey
  DETAIL:  Table contains duplicated values.
  
  How could I find those duplicated pairs of id and author?
 
 similar example:
 
 test=*# select * from foo;
  id1 | id2
 -+-
1 |   1
1 |   2
1 |   3
2 |   1
2 |   2
2 |   3
1 |   2
3 |   1
3 |   2
3 |   3
3 |   1
 (11 rows)
 
 Time: 0,151 ms
 test=*# alter table foo add primary key (id1,id2);
 NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index foo_pkey 
 for table foo
 ERROR:  could not create unique index foo_pkey
 DETAIL:  Key (id1, id2)=(1, 2) is duplicated.
 Time: 1,394 ms
 test=*# select id1, id2, count(*) as c from foo group by id1, id2 having 
 count(*)  1;
  id1 | id2 | c
 -+-+---
3 |   1 | 2
1 |   2 | 2
 (2 rows)
 
 Time: 0,331 ms

If your next question is 'how to delete ...', my answer:
(yeah, reading SO ;-) )


test=*# select ctid,* from foo;
  ctid  | id1 | id2
+-+-
 (0,1)  |   1 |   1
 (0,2)  |   1 |   2
 (0,3)  |   1 |   3
 (0,4)  |   2 |   1
 (0,5)  |   2 |   2
 (0,6)  |   2 |   3
 (0,7)  |   1 |   2
 (0,8)  |   3 |   1
 (0,9)  |   3 |   2
 (0,10) |   3 |   3
 (0,11) |   3 |   1
(11 rows)

Time: 0,170 ms
test=*# delete from foo where ctid in (select min(ctid) from foo where 
(id1,id2) in (select id1, id2 from foo group by id1, id2 having count(*)  1) 
group by id1,id2);
DELETE 2
Time: 0,559 ms
test=*# select ctid,* from foo;
  ctid  | id1 | id2
+-+-
 (0,1)  |   1 |   1
 (0,3)  |   1 |   3
 (0,4)  |   2 |   1
 (0,5)  |   2 |   2
 (0,6)  |   2 |   3
 (0,7)  |   1 |   2
 (0,9)  |   3 |   2
 (0,10) |   3 |   3
 (0,11) |   3 |   1
(9 rows)





Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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

Re: [GENERAL] partial time stamp query

[Kirk Wythers]

Thanks. That worked great! Now I am trying to aggregate these same fifteen 
minute to hourly. I have tried using date_trunk:

date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),

but date_truck only seems to aggriage the timestamp. I thought I could use 

AVG(derived_tsoil_fifteen_min_stacked.value)

in combination with date_trunk, but I still get 15 minute values, not the 
hourly average from the four 15 minute records.

rowid   date_truck  
time2   sitecanopy  plot
variablenamevalue   avg
2010-07-07_00:00:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   21.06   
21.054659424
2010-07-07_00:15:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.96   
20.950844727
2010-07-07_00:30:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.88   
20.871607666
2010-07-07_00:45:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.8
20.792370605
2010-07-07_01:00:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.72   
20.713133545
2010-07-07_01:15:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.64   
20.633896484
2010-07-07_01:30:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.55   
20.542370605
2010-07-07_01:45:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.47   
20.463133545

I was tying to get two records out of this set, with the 'avg column 
representing the mean of the first and last four of each 15 minute records. 

Perhaps date_trunk only works for the timestamp? 



On Feb 4, 2013, at 8:50 AM, Misa Simic misa.si...@gmail.com wrote:

 WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'::date
 
 On Monday, February 4, 2013, Kirk Wythers wrote:
 I am trying to write a query that grabs one particular day from a timestamp 
 column. The data are ordered in 15 minute chunks like this:
 
 2010-07-07 12:45:00
 2010-07-07 13:00:00
 2010-07-07 13:15:00
 2010-07-07 13:30:00
 etc…
 
 If I wanted all records from july 7th 2010, I would expect 4 x 24 = 96 
 records per day.
 
 I have tried the '=' operator, like this
 
 WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*'
 
 but that grabs nothing, and using the '~' operator grabs everything with a 
 2010 or 07 in it… in other words all days from July of 2010.
 
 Any suggestions would be much appreciated.
 
 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

Fwd: [GENERAL] partial time stamp query

[Kirk Wythers]

Thanks. That worked great! Now I am trying to aggregate these same fifteen 
minute to hourly. I have tried using date_trunk:

date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),

but date_truck only seems to aggriage the timestamp. I thought I could use 

AVG(derived_tsoil_fifteen_min_stacked.value)

in combination with date_trunk, but I still get 15 minute values, not the 
hourly average from the four 15 minute records.

rowid   date_truck  
time2   sitecanopy  plot
variablenamevalue   avg
2010-07-07_00:00:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   21.06   
21.054659424
2010-07-07_00:15:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.96   
20.950844727
2010-07-07_00:30:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.88   
20.871607666
2010-07-07_00:45:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.8
20.792370605
2010-07-07_01:00:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.72   
20.713133545
2010-07-07_01:15:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.64   
20.633896484
2010-07-07_01:30:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.55   
20.542370605
2010-07-07_01:45:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.47   
20.463133545

I was tying to get two records out of this set, with the 'avg column 
representing the mean of the first and last four of each 15 minute records. 

Perhaps date_trunk only works for the timestamp? 



On Feb 4, 2013, at 8:50 AM, Misa Simic misa.si...@gmail.com wrote:

 WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'::date
 
 On Monday, February 4, 2013, Kirk Wythers wrote:
 I am trying to write a query that grabs one particular day from a timestamp 
 column. The data are ordered in 15 minute chunks like this:
 
 2010-07-07 12:45:00
 2010-07-07 13:00:00
 2010-07-07 13:15:00
 2010-07-07 13:30:00
 etc…
 
 If I wanted all records from july 7th 2010, I would expect 4 x 24 = 96 
 records per day.
 
 I have tried the '=' operator, like this
 
 WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*'
 
 but that grabs nothing, and using the '~' operator grabs everything with a 
 2010 or 07 in it… in other words all days from July of 2010.
 
 Any suggestions would be much appreciated.
 
 --
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] date_trunc to aggregate values?

[Kirk Wythers]

I am looking for suggestions on aggregation techniques using a timestamp 
column. In my case I have tried:

date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),

but date_truck only seems to aggregate the timestamp. I thought I could use 

AVG(derived_tsoil_fifteen_min_stacked.value)

in combination with date_trunk, but I still get 15 minute values, not the 
hourly average from the four 15 minute records.

rowid   date_truck  
time2   sitecanopy  plot
variablenamevalue   avg
2010-07-07_00:00:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   21.06   
21.054659424
2010-07-07_00:15:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.96   
20.950844727
2010-07-07_00:30:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.88   
20.871607666
2010-07-07_00:45:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
00:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.8
20.792370605
2010-07-07_01:00:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.72   
20.713133545
2010-07-07_01:15:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.64   
20.633896484
2010-07-07_01:30:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.55   
20.542370605
2010-07-07_01:45:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
01:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.47   
20.463133545

I was tying to get two records out of this set, with the 'avg column 
representing the mean of the first and last four of each 15 minute records. 

Suggestions?



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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Alexander Farber]

Thank you -

On Mon, Feb 4, 2013 at 5:06 PM, Andreas Kretschmer
akretsch...@spamfence.net wrote:
 # alter table pref_rep add primary key(id, author);
 NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
 pref_rep_pkey for table pref_rep
 ERROR:  could not create unique index pref_rep_pkey
 DETAIL:  Table contains duplicated values.

how do you get this DETAIL, is it a setting for psql prompt?

I've got a nice answer for my question at Stackoverflow:

DELETE FROM pref_rep p USING (
  SELECT id, author, max(stamp) stamp
FROM pref_rep
   GROUP BY id, author
  HAVING count(1)  1) AS f
WHERE p.id=f.id AND p.author=f.author AND p.stampf.stamp;

and learnt about SQL Fiddle too -
http://sqlfiddle.com/#!11/59fbc/11

Regards
Alex


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

[GENERAL] DEFERRABLE NOT NULL constraint

[Andreas Joseph Krogh]

It's currently (9.2) not possible to define DEFERRABLE NOT NULL constraints. Meaning the following is not valid:

 

CREATE TABLE my_table(

id varchar PRIMARY KEY,

stuff_id BIGINT NOT NULL DEFERRABLE INITIALLY DEFERRED

);

 

While it's possible to define a trigger to enforce this, like this:

 


CREATE CONSTRAINT TRIGGER my_table_t AFTER INSERT OR UPDATE ON onp_crm_relation DEFERRABLE INITIALLY DEFERRED

FOR EACH ROW EXECUTE PROCEDURE my_table_check_stuff_id_nn_tf();

 


And have the my_table_check_stuff_id_nn_tf() raise an exception if stuff_id is null.

 

Having deferrable constraints on FKs and UKs is really nice and when working with ORMs it's almost impossible to not use this feature.

 

Are there any plans to make NOT NULL constraints deferrable so one can avoid the trigger boilerplate?

 

--
Andreas Joseph Krogh andr...@officenet.no      mob: +47 909 56 963
Senior Software Developer / CTO - OfficeNet AS - http://www.officenet.no
Public key: http://home.officenet.no/~andreak/public_key.asc

[GENERAL] Options for passing values to triggers?

[org.postgresql]

Hello.

I'm modelling a system where I'd like to log inserts and deletes
to two or more tables (with foreign key references between them).

As a (contrived) example:

CREATE TABLE projects (
  project_id   SERIAL PRIMARY KEY,
  project_name TEXT UNIQUE NOT NULL
);

CREATE TABLE project_repositories (
  repos_id  SERIAL PRIMARY KEY,
  repos_project INTEGER NOT NULL,
  repos_url TEXT UNIQUE NOT NULL,

  FOREIGN KEY (repos_project) REFERENCES projects (project_id)
);

CREATE TABLE tasks (
  task_id SERIAL PRIMARY KEY,
  task_repos INTEGER NOT NULL,
  
  FOREIGN KEY (task_repos) REFERENCES project_repositories (repos_id)
);

And then the log table:

CREATE TABLE audit (
  audit_id  BIGSERIAL PRIMARY KEY,
  audit_timeTIMPSTAMP WITH TIME ZONE NOT NULL,
  audit_userTEXT NOT NULL,
  audit_session TEXT NOT NULL,
  audit_typeTEXT NOT NULL,
  audit_message TEXT NOT NULL
);

Note: The audit_user and audit_session columns are NOT postgresql roles
or sessions; they are from the external application.

So, the intention is that when something is deleted from the projects
table, an event will be recorded of type 'PROJECT_DELETE', including
the name of the project and user responsible for the deletion. Similar
events would be logged for the tasks and project_repositories tables.
Creation would be logged in the same manner.

I'd like to model this using triggers with cascading deletes (so that
when a project is deleted, each one of its repositories is deleted and
logged as having been deleted, and any tasks that depend on those
repositories too).

The problem: I'm not sure what the most pleasant way (or if it's
even possible) to pass 'audit_user' and 'audit_session' to the trigger
functions. The values are created by the external application that
queries the database and aren't otherwise present in the database in
any form.

Furthermore: I'm intending to partition the system into separate roles
such that the role that executes the database queries doesn't have read
or write permission to the audit table (meaning that any logging is
going to have to occur via a function with SECURITY DEFINER).

Any advice or you don't want to it that way abuse would be much
appreciated.

M



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

Re: [GENERAL] Options for passing values to triggers?

[Pavel Stehule]

2013/2/4  org.postgre...@io7m.com:
 Hello.

 I'm modelling a system where I'd like to log inserts and deletes
 to two or more tables (with foreign key references between them).

 As a (contrived) example:

 CREATE TABLE projects (
   project_id   SERIAL PRIMARY KEY,
   project_name TEXT UNIQUE NOT NULL
 );

 CREATE TABLE project_repositories (
   repos_id  SERIAL PRIMARY KEY,
   repos_project INTEGER NOT NULL,
   repos_url TEXT UNIQUE NOT NULL,

   FOREIGN KEY (repos_project) REFERENCES projects (project_id)
 );

 CREATE TABLE tasks (
   task_id SERIAL PRIMARY KEY,
   task_repos INTEGER NOT NULL,

   FOREIGN KEY (task_repos) REFERENCES project_repositories (repos_id)
 );

 And then the log table:

 CREATE TABLE audit (
   audit_id  BIGSERIAL PRIMARY KEY,
   audit_timeTIMPSTAMP WITH TIME ZONE NOT NULL,
   audit_userTEXT NOT NULL,
   audit_session TEXT NOT NULL,
   audit_typeTEXT NOT NULL,
   audit_message TEXT NOT NULL
 );

 Note: The audit_user and audit_session columns are NOT postgresql roles
 or sessions; they are from the external application.

 So, the intention is that when something is deleted from the projects
 table, an event will be recorded of type 'PROJECT_DELETE', including
 the name of the project and user responsible for the deletion. Similar
 events would be logged for the tasks and project_repositories tables.
 Creation would be logged in the same manner.

 I'd like to model this using triggers with cascading deletes (so that
 when a project is deleted, each one of its repositories is deleted and
 logged as having been deleted, and any tasks that depend on those
 repositories too).

 The problem: I'm not sure what the most pleasant way (or if it's
 even possible) to pass 'audit_user' and 'audit_session' to the trigger
 functions. The values are created by the external application that
 queries the database and aren't otherwise present in the database in
 any form.

 Furthermore: I'm intending to partition the system into separate roles
 such that the role that executes the database queries doesn't have read
 or write permission to the audit table (meaning that any logging is
 going to have to occur via a function with SECURITY DEFINER).

 Any advice or you don't want to it that way abuse would be much
 appreciated.

Moving and too smart logic to triggers is usually bad idea

better REVOKE DELETE rights for application users and implement
security definer stored procedures, that ensure correct deleting with
correct auditing.

Regards

Pavel Stehule


 M



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


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

Re: [GENERAL] Adding PRIMARY KEY: Table contains duplicated values

[Andreas Kretschmer]

Alexander Farber alexander.far...@gmail.com wrote:

 Thank you -
 
 On Mon, Feb 4, 2013 at 5:06 PM, Andreas Kretschmer
 akretsch...@spamfence.net wrote:
  # alter table pref_rep add primary key(id, author);
  NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
  pref_rep_pkey for table pref_rep
  ERROR:  could not create unique index pref_rep_pkey
  DETAIL:  Table contains duplicated values.
 
 how do you get this DETAIL, is it a setting for psql prompt?

You means the 'DETAIL:  Key (id1, id2)=(1, 2) is duplicated.'?

I'm using 9.2, i think, that's the reason.



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
If I was god, I would recompile penguin with --enable-fly.   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°


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

[GENERAL] Is it possible to migrate database from Postgresql 8.2 to 9.2 using pg_upgrade?

[AI Rumman]

Is it possible to migrate database from Postgresql 8.2 to 9.2 using
pg_upgrade?

Thanks.

Re: [GENERAL] Is it possible to migrate database from Postgresql 8.2 to 9.2 using pg_upgrade?

[Steve Crawford]

On 02/04/2013 12:06 PM, AI Rumman wrote:
Is it possible to migrate database from Postgresql 8.2 to 9.2 using 
pg_upgrade?


Thanks.


Per http://www.postgresql.org/docs/9.2/static/pgupgrade.html

...pg_upgrade supports upgrades from 8.3.X and later to the current 
major release of PostgreSQL...


So it looks like you will need one of the other methods for this one.

Cheers,
Steve


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

Re: [GENERAL] partial time stamp query

[Kirk Wythers]

Hi Brent, 

Nice to hear from you. I hope your world is good. 

On Feb 4, 2013, at 2:14 PM, Brent Wood brent.w...@niwa.co.nz wrote:

 Hi Kirk,
 
 We have a (near) real time data database for instrument observations from our 
 research vessels. All observations (summarised to one minute intervals - the 
 actual raw data is in netCDF, this database makes for easier access  meets 
 most users needs) go into a single table, with other tables for metadata 
 about platforms, instruments, etc. Now approaching 350m records, so 
 reasonably substantial.
 
 Underlying optimisations include
 
 partitioned readings table, with a separate partition for each year (now 23 
 years)
 clustered index on timestamp for the previous years partitions.
 largeish filesystem block size - tested to work well with the clustered index 
  small size records)
 
 These generally make a big difference to performance. To address one issue, 
 much like yours, where some users want hourly data for a year, some want 
 daily data for 10 years  some want 1 minute data for the last month ( some, 
 no doubt, want one minute data for 20+ years!) I introduced an integer column 
 called timer. This value is set according to the time (not date) of each 
 record.

Very similar to what I need to do. Our main table consists of records that have 
been standardized to 15 minute timestamps. Here is a simplified example

record  timestamp   variablevalue
1   12:00:00temp12.6
2   12:15:00temp12.3
3   12:30:00temp11.7
4   12:45:00temp12.3
5   13:00:00temp13.9
6   13:15:00temp12.5
7   13.30:00temp13.7
8   13:45:00temp12.0

You are exactly right, some people will want the original 15 minute version, 
some people will want these summarized to hourly data, and others will want 
these summarized to daily data. Still others may be satisfied with monthly 
summaries. 

 
 Along the lines of (from memory) :an even no of minutes after the hour is 2, 
 5 minutes is 4, 10 minutes is 8, 15 minute is 16, 30 minutes is 32, 60 
 minutes is 64, 6 hourly is 128, 12:00 AM is 256  12:00PM is 512.   When any 
 timestamp is in more than one category (eg: 12:00 is all of even, 5, 15m 30m 
 60 minutes), the timer value is set to the largest appropriate one.

I'm not quite following. In my case, if I want hourly data, I'd be looking for…

record  timestamp   variablevalue
1   12:00:00temp12.225
2   13:00:00temp13.025

Are you saying that I could use an approach that WHILE statement? Something 
like:

WHILE data_truc('hour', timestamp) = 12:00:00, then calulate AVG(value)?

 
 So a request for:
 1 minute data is select from table;
 2 minute data is select from table where timer =2 and timer !=15 and timer 
 !=4;
 hourly data is select from table where timer =64 and timer != 15 and timer 
 != 4;
 etc
 
 5  15 minute add a bit of complexity, but we gave the users what they 
 wanted. This has worked well for years now,  we have an internal web 
 (mapserver/openlayers based) application allowing users to visualise  
 download their selected data - they choose from an interval pick list  the 
 SQL is hidden. Some extra enhancements are the automatic collation of lat  
 lon gps readings into a Postgis point for each reading record,  the 
 automatic aggregation of daily points into daily track lines, so the track 
 for any selected set of dates can easily be displayed on a map (the platforms 
 are mobile vessels - not fixed sites)
 
 You might adapt some of these ideas for your use case?
 
 Cheers
 
 Brent Wood
 
 Programme leader: Environmental Information Delivery
 NIWA
 DDI:  +64 (4) 3860529
 
 From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
 on behalf of Kirk Wythers [wythe...@umn.edu]
 Sent: Tuesday, February 05, 2013 5:58 AM
 To: pgsql-general@postgresql.org
 Subject: Fwd: [GENERAL] partial time stamp query
 
 Thanks. That worked great! Now I am trying to aggregate these same fifteen 
 minute to hourly. I have tried using date_trunk:
 
 date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
 
 but date_truck only seems to aggriage the timestamp. I thought I could use
 
 AVG(derived_tsoil_fifteen_min_stacked.value)
 
 in combination with date_trunk, but I still get 15 minute values, not the 
 hourly average from the four 15 minute records.
 
 rowid date_truck time2 site canopy plot variable name value avg
 2010-07-07_00:00:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:00:00 cfc 
 closed a2 tsoil_sc tsoil_avg1_sc 21.06 21.054659424
 

[GENERAL] Passing dynamic parameters to a table-returning function

[Moshe Jacobson]

Hi all,

I know I'm probably missing something obvious here, but I have been unable
to figure this out or find any docs on it.

I have a function that takes in a postal address and normalizes it through
text manipulation etc.:

*fn_normalize_address*(*in_line_one* character varying, *in_line_two *character
varying, *in_line_three* character varying, *in_city* character varying, *
in_locality* character varying, *in_region* character varying, *
in_postal_code* character varying, *in_country* character varying)
*RETURNS TABLE*(*line_one* character varying, *line_two* character varying,
*line_three* character varying, *city* character varying,
*locality*character varying,
*region* integer, *region_string* character varying,
*postal_code*character varying,
*country* integer, *country_string* character varying)


I also have a table of addresses that I'd like to normalize:

  Table public.tb_address
Column|  Type  |
 Modifiers
--++-
 address  | integer| not null default
nextval('sq_pk_address'::regclass)
 name | character varying(64)  |
 line_one | character varying(256) | not null
 line_two | character varying(256) |
 line_three   | character varying(256) |
 city | character varying(256) | not null
 locality | character varying(256) |
 region   | integer| not null
 zip_postal   | character varying(32)  | not null
 zip_postal_extension | character varying(32)  |



I am trying to write a query to update the addresses in this table with
their normalized versions. Each address is uniquely identified by the
integer surrogate primary key tb_address.address.

I'm having trouble because I can't figure out how to select the primary key
from that table, as well as all the columns from the function's output. I'm
unsure of the syntax for passing in values from columns in the database as
the parameters of a set-returning function from which I want to select *.

Any help would be appreciated.

Thanks,
Moshe


-- 
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
mo...@neadwerx.com | www.neadwerx.com

People don't multitask because they're good at it. They do it because they
are more distracted -- David
Sanbonmatsuhttp://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0054402

[GENERAL] Aggregating inet subnets to supernets

[Sven Ulland]

How would I aggregate a lot of inet addresses/subnets to unique super-
networks? Simply doing a 'GROUP BY network(address)' will not do any
aggregation, and thus includes lots of /32s that are part of larger
networks. While I could add 'WHERE masklen(address)  32 and family
(address) = 4' (or ditto for IPv6), I'd rather avoid it. The addresses
are gathered from my servers/routers/etc and always include the cidr
mask length on INSERT into the db.

Bonus question: How would I construct a query for aggregating at an
arbitrary depth? E.g. if there are three levels of subnetting:
10.0.0.0/16 is split in a bunch of 10.0.0.0/22s, and some of those
are split in bunches of /24s; a 1st level aggregation would output
the /22s *and* the /16, and a 2nd level aggregation would then
output only the /16.

Input:
  address (inet)   |   comment
---+--
 10.0.0.12/25  | Host addr in 10.0.0.0/25 net
 10.0.0.22/25  | Host addr in 10.0.0.0/25 net
 10.0.0.54 | Virtual /32 IP in 10.0.0.0/25 net
 10.0.0.55 | Virtual /32 IP in 10.0.0.0/25 net
 10.0.0.56 | Virtual /32 IP in 10.0.0.0/25 net
 10.0.0.97/27  | Host addr in 10.0.0.96/27 subnet, in 10.0.0.0/25 net
 10.0.1.12/24  | Host addr in 10.0.1.0/24 net
 2001:1:2:3::12/64 | Host addr in 2001:1:2:3::/64 net
 2001:1:2:3::13| Virtual /128 IP in 2001:1:2:3::/64 net

Desired output:
  network
-
 10.0.0.0/25
 10.0.1.0/24
 2001:1:2:3::/64

regards,
Sven


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

Re: [GENERAL] Options for passing values to triggers?

[Gurjeet Singh]

On Mon, Feb 4, 2013 at 2:01 PM, org.postgre...@io7m.com wrote:

 Hello.

 I'm modelling a system where I'd like to log inserts and deletes
 to two or more tables (with foreign key references between them).

 As a (contrived) example:

 CREATE TABLE projects (
   project_id   SERIAL PRIMARY KEY,
   project_name TEXT UNIQUE NOT NULL
 );

 CREATE TABLE project_repositories (
   repos_id  SERIAL PRIMARY KEY,
   repos_project INTEGER NOT NULL,
   repos_url TEXT UNIQUE NOT NULL,

   FOREIGN KEY (repos_project) REFERENCES projects (project_id)
 );

 CREATE TABLE tasks (
   task_id SERIAL PRIMARY KEY,
   task_repos INTEGER NOT NULL,

   FOREIGN KEY (task_repos) REFERENCES project_repositories (repos_id)
 );

 And then the log table:

 CREATE TABLE audit (
   audit_id  BIGSERIAL PRIMARY KEY,
   audit_timeTIMPSTAMP WITH TIME ZONE NOT NULL,
   audit_userTEXT NOT NULL,
   audit_session TEXT NOT NULL,
   audit_typeTEXT NOT NULL,
   audit_message TEXT NOT NULL
 );

 Note: The audit_user and audit_session columns are NOT postgresql roles
 or sessions; they are from the external application.

 So, the intention is that when something is deleted from the projects
 table, an event will be recorded of type 'PROJECT_DELETE', including
 the name of the project and user responsible for the deletion. Similar
 events would be logged for the tasks and project_repositories tables.
 Creation would be logged in the same manner.

 I'd like to model this using triggers with cascading deletes (so that
 when a project is deleted, each one of its repositories is deleted and
 logged as having been deleted, and any tasks that depend on those
 repositories too).

 The problem: I'm not sure what the most pleasant way (or if it's
 even possible) to pass 'audit_user' and 'audit_session' to the trigger
 functions. The values are created by the external application that
 queries the database and aren't otherwise present in the database in
 any form.

 Furthermore: I'm intending to partition the system into separate roles
 such that the role that executes the database queries doesn't have read
 or write permission to the audit table (meaning that any logging is
 going to have to occur via a function with SECURITY DEFINER).

 Any advice or you don't want to it that way abuse would be much
 appreciated.


I have no opinion of whether this is the right way of going abut it, but
here's a way it can be done. Recent versions of postgres allow you to set
arbitrary session level variables, so you can use SQL commands to set/get
these variables.

.) At the start of a session, set the app user name in a variable

SET my_app.audit_user = 'app_user_1';

.) Inside your trigger function:

current_app_user = select current_setting('my_app.audit_user');

PS:
Question to PG-hackers: Why are such variables not visible in pg_settings
view?

[GENERAL] PGAdmin on Windows (yeah, i know!!) and registry/.ini questions

[David Wooffindin]

Hi,
My question: are there any real docs on how to ‘force’ registry values so that 
all users get some preconfigured servers . . .
That or, how to do it via an .ini file, cos the example ini doesn’t really say 
how to do what I’m looking to do.
I’m trying to use AD/GPO to configure predefined servers for different sites 
within our organization and I’m sort of going nuts trying to find how to do 
this.
Of course, mostly being screwed over due to GPO  AD, as opposed to pgadmin….
Thanks for your time

David Wooffindin


***
Consider the environment before printing this message.

To read Autoliv's Information and Confidentiality Notice, follow this link:
http://www.autoliv.com/disclaimer.html
***

Re: [GENERAL] PGAdmin on Windows (yeah, i know!!) and registry/.ini questions

[John R Pierce]

On 2/4/2013 12:03 AM, David Wooffindin wrote:


My question: are there any real docs on how to ‘force’ registry values 
so that all users get some preconfigured servers . . .


That or, how to do it via an .ini file, cos the example ini doesn’t 
really say how to do what I’m looking to do.


I’m trying to use AD/GPO to configure predefined servers for different 
sites within our organization and I’m sort of going nuts trying to 
find how to do this.


Of course, mostly being screwed over due to GPO  AD, as opposed to 
pgadmin….


Thanks for your time




what exactly do you mean by, 'preconfigured servers' ? Postgres 
makes no use of the registry except the service definition, and that 
should be created with the pg_ctl command, or with the system sc 
command.   creating databases and roles can/should be done with a sql 
script run through psql.exe




--
john r pierce  37N 122W
somewhere on the middle of the left coast

Re: [GENERAL] date_trunc to aggregate values?

[Jason Dusek]

2013/2/4 Kirk Wythers wythe...@umn.edu:
 I am looking for suggestions on aggregation techniques using a timestamp 
 column. In my case I have tried:

 date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),

 but date_truck only seems to aggregate the timestamp. I thought I could use

 AVG(derived_tsoil_fifteen_min_stacked.value)

 in combination with date_trunk, but I still get 15 minute values, not the 
 hourly average from the four 15 minute records.

 rowid   date_truck
   time2   sitecanopy  plot
 variablenamevalue   avg
 2010-07-07_00:00:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   21.06   
 21.054659424
 2010-07-07_00:15:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.96   
 20.950844727
 2010-07-07_00:30:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.88   
 20.871607666
 2010-07-07_00:45:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.8
 20.792370605
 2010-07-07_01:00:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.72   
 20.713133545
 2010-07-07_01:15:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.64   
 20.633896484
 2010-07-07_01:30:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.55   
 20.542370605
 2010-07-07_01:45:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.47   
 20.463133545

 I was tying to get two records out of this set, with the 'avg column 
 representing the mean of the first and last four of each 15 minute records.

 Suggestions?

Are you using an explicit GROUP BY?

--
Jason Dusek
pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B


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

Re: [GENERAL] date_trunc to aggregate values?

[Kirk Wythers]

On Feb 4, 2013, at 3:26 PM, Jason Dusek jason.du...@gmail.com wrote:

 2013/2/4 Kirk Wythers wythe...@umn.edu:
 I am looking for suggestions on aggregation techniques using a timestamp 
 column. In my case I have tried:
 
 date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
 
 but date_truck only seems to aggregate the timestamp. I thought I could use
 
 AVG(derived_tsoil_fifteen_min_stacked.value)
 
 in combination with date_trunk, but I still get 15 minute values, not the 
 hourly average from the four 15 minute records.
 
 rowid   date_truck   
time2   sitecanopy  plot
 variablenamevalue   avg
 2010-07-07_00:00:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   21.06   
 21.054659424
 2010-07-07_00:15:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.96   
 20.950844727
 2010-07-07_00:30:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.88   
 20.871607666
 2010-07-07_00:45:00_b4warm_a2010-07-07 00:00:00 1   2010-07-07 
 00:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.8
 20.792370605
 2010-07-07_01:00:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:00:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.72   
 20.713133545
 2010-07-07_01:15:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:15:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.64   
 20.633896484
 2010-07-07_01:30:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:30:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.55   
 20.542370605
 2010-07-07_01:45:00_b4warm_a2010-07-07 01:00:00 1   2010-07-07 
 01:45:00 cfc closed  a2  tsoil_sctsoil_avg1_sc   20.47   
 20.463133545
 
 I was tying to get two records out of this set, with the 'avg column 
 representing the mean of the first and last four of each 15 minute records.
 
 Suggestions?
 
 Are you using an explicit GROUP BY?
 

Here is what I have in the GROUP BY clause

GROUP BY
date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
derived_tsoil_fifteen_min_stacked.time2,
data_key.site,
data_key.canopy,
data_key.variable_name,
data_key.plot



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

Re: [GENERAL] PGAdmin on Windows (yeah, i know!!) and registry/.ini questions

[Carlo Stonebanks]

I would imagine he means, ‘Can PgAdmin be distributed in such a way that the 
user does not have to setup DB connections themselves. But are preconfigured’.

 

If so, then this is a PgAdmin question, not a PostgreSQL question.

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of John R Pierce
Sent: February 4, 2013 4:23 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PGAdmin on Windows (yeah, i know!!) and registry/.ini 
questions

 

On 2/4/2013 12:03 AM, David Wooffindin wrote:

My question: are there any real docs on how to ‘force’ registry values so that 
all users get some preconfigured servers . . .

That or, how to do it via an .ini file, cos the example ini doesn’t really say 
how to do what I’m looking to do.

I’m trying to use AD/GPO to configure predefined servers for different sites 
within our organization and I’m sort of going nuts trying to find how to do 
this.

Of course, mostly being screwed over due to GPO  AD, as opposed to pgadmin….

Thanks for your time 



what exactly do you mean by, 'preconfigured servers' ? Postgres makes no 
use of the registry except the service definition, and that should be created 
with the pg_ctl command, or with the system sc command.   creating databases 
and roles can/should be done with a sql script run through psql.exe






-- 
john r pierce  37N 122W
somewhere on the middle of the left coast

[GENERAL] Reverse Engr into erwin

[Little, Douglas]

Thanks in advance for thinking about my problem.

As I suspect you know,  CA Erwin doesn't support Postgres or greenplum.
But they do support ODBC for reverse engineering.

When I reverse, Erwin executes the standard ODBC metadata queries for the 
system catalog.
The process works fine, but I'm finding that the datatype that's returned is 
the internal PG type instead of the standard set of ANSI types.
Eg,   a BIGINT column returns as INT8.

I could live with the non-standard lables, if the resulting DDL would execute.  
But PGadmin doesn't know the data types.

I've traced the same function to PGadmin and find that it is submitting a query 
with the format_type(type oid) function to convert to ANSI types.

I've installed the newest PG ODBC driver and the problem remains.

I'm hoping that there is a setting in ODBC that I can use to have the ANSI 
datatypes returned.
Short of that,  Is there anyway to change the Query that's submitted

It's querying the pg_attribute, pg_type type with the schema name specified 
Pg_catalog,  so I don't think I can over ride the select. Perhaps a rule might 
work.

Any suggestions on moving forward?

Thanks



Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz 
Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.commailto:douglas.lit...@orbitz.com
 [cid:image001.jpg@01CE02F5.99F2EAC0]   orbitz.comhttp://www.orbitz.com/ | 
ebookers.comhttp://www.ebookers.com/ | 
hotelclub.comhttp://www.hotelclub.com/ | 
cheaptickets.comhttp://www.cheaptickets.com/ | 
ratestogo.comhttp://www.ratestogo.com/ | 
asiahotels.comhttp://www.asiahotels.com/

inline: image001.jpg

Re: [GENERAL] partial time stamp query

[Brent Wood]

Hi Kirk,

We have a (near) real time data database for instrument observations from our 
research vessels. All observations (summarised to one minute intervals - the 
actual raw data is in netCDF, this database makes for easier access  meets 
most users needs) go into a single table, with other tables for metadata about 
platforms, instruments, etc. Now approaching 350m records, so reasonably 
substantial.

Underlying optimisations include

partitioned readings table, with a separate partition for each year (now 23 
years)
clustered index on timestamp for the previous years partitions.
largeish filesystem block size - tested to work well with the clustered index  
small size records)

These generally make a big difference to performance. To address one issue, 
much like yours, where some users want hourly data for a year, some want daily 
data for 10 years  some want 1 minute data for the last month ( some, no 
doubt, want one minute data for 20+ years!) I introduced an integer column 
called timer. This value is set according to the time (not date) of each record.

Along the lines of (from memory) :an even no of minutes after the hour is 2, 5 
minutes is 4, 10 minutes is 8, 15 minute is 16, 30 minutes is 32, 60 minutes is 
64, 6 hourly is 128, 12:00 AM is 256  12:00PM is 512.   When any timestamp is 
in more than one category (eg: 12:00 is all of even, 5, 15m 30m 60 minutes), 
the timer value is set to the largest appropriate one.

So a request for:
 1 minute data is select from table;
 2 minute data is select from table where timer =2 and timer !=15 and timer 
!=4;
 hourly data is select from table where timer =64 and timer != 15 and timer != 
4;
etc

5  15 minute add a bit of complexity, but we gave the users what they wanted. 
This has worked well for years now,  we have an internal web 
(mapserver/openlayers based) application allowing users to visualise  download 
their selected data - they choose from an interval pick list  the SQL is 
hidden. Some extra enhancements are the automatic collation of lat  lon gps 
readings into a Postgis point for each reading record,  the automatic 
aggregation of daily points into daily track lines, so the track for any 
selected set of dates can easily be displayed on a map (the platforms are 
mobile vessels - not fixed sites)

You might adapt some of these ideas for your use case?

Cheers

Brent Wood

Programme leader: Environmental Information Delivery
NIWA
DDI:  +64 (4) 3860529

From: pgsql-general-ow...@postgresql.org [pgsql-general-ow...@postgresql.org] 
on behalf of Kirk Wythers [wythe...@umn.edu]
Sent: Tuesday, February 05, 2013 5:58 AM
To: pgsql-general@postgresql.org
Subject: Fwd: [GENERAL] partial time stamp query

Thanks. That worked great! Now I am trying to aggregate these same fifteen 
minute to hourly. I have tried using date_trunk:

date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),

but date_truck only seems to aggriage the timestamp. I thought I could use

AVG(derived_tsoil_fifteen_min_stacked.value)

in combination with date_trunk, but I still get 15 minute values, not the 
hourly average from the four 15 minute records.

rowid date_truck time2 site canopy plot variable name value avg
2010-07-07_00:00:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:00:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 21.06 21.054659424
2010-07-07_00:15:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:15:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 20.96 20.950844727
2010-07-07_00:30:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:30:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 20.88 20.871607666
2010-07-07_00:45:00_b4warm_a 2010-07-07 00:00:00 1 2010-07-07 00:45:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 20.8 20.792370605
2010-07-07_01:00:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:00:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 20.72 20.713133545
2010-07-07_01:15:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:15:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 20.64 20.633896484
2010-07-07_01:30:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:30:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 20.55 20.542370605
2010-07-07_01:45:00_b4warm_a 2010-07-07 01:00:00 1 2010-07-07 01:45:00 cfc 
closed a2 tsoil_sc tsoil_avg1_sc 20.47 20.463133545

I was tying to get two records out of this set, with the 'avg column 
representing the mean of the first and last four of each 15 minute records.

Perhaps date_trunk only works for the timestamp?



On Feb 4, 2013, at 8:50 AM, Misa Simic 
misa.si...@gmail.commailto:misa.si...@gmail.com wrote:

WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'::date

On Monday, February 4, 2013, Kirk Wythers wrote:
I am trying to write a query that grabs one particular day from a timestamp 
column. The data are ordered in 15 minute chunks like this:

2010-07-07 12:45:00
2010-07-07 13:00:00
2010-07-07 13:15:00
2010-07-07 13:30:00
etc…

If I wanted all records from july 7th 2010, I 

[GENERAL] Hot Standby has PANIC: WAL contains references to invalid pages

[Michael Harris]

Hi All,

We are having a thorny problem I'm hoping someone will be able to help with.

We have a pair of machines set up as an active / hot SB pair. The database they 
contain is quite large - approx. 9TB. They were working fine on 9.1, and we 
recently upgraded the active DB to 9.2.1.

After upgrading the active DB, we re-mirrored the standby (using pg_basebackup) 
and started it up. It began replaying the WAL files as expected.

After a few hours this happened:

WARNING:  page 1 of relation pg_tblspc/16408/PG_9.2_201204301/16409/1123460086 
is uninitialized
CONTEXT:  xlog redo vacuum: rel 16408/16409/1123460086; blk 4411, 
lastBlockVacuumed 0
PANIC:  WAL contains references to invalid pages
CONTEXT:  xlog redo vacuum: rel 16408/16409/1123460086; blk 4411, 
lastBlockVacuumed 0
LOG:  startup process (PID 24195) was terminated by signal 6: Aborted
LOG:  terminating any other active server processes

We tried starting it up again, the same thing happened.

After some googling and re-reading the release notes, we noticed the mention in 
the 9.2.1 release notes about the potential for corrupted visibility maps, so 
as per the recommendation we did a full VACUUM of the whole database (with 
vacuum_freeze_table_age set to zero), then re-mirrored the standby again.

After re-mirroring was completed we started the standby again. Strangely it 
reached consistency after only 33 WAL files - since the base backup took 5 days 
to complete this does not seem right to me. Anyway, WAL recovery continued, 
with occasional warnings like this:

[2013-02-04 10:30:51 EST]  13546@  WARNING:  xlog min recovery request 
1A13A/9BC425A0 is past current point 19F1E/725043E8
[2013-02-04 10:30:51 EST]  13546@  CONTEXT:  writing block 0 of relation 
pg_tblspc/16408/PG_9.2_201204301/16409/12525_vm

After a few hours, this happened:

[2013-02-04 13:43:24 EST]  13538@  WARNING:  page 1248 of relation 
pg_tblspc/16408/PG_9.2_201204301/16409/1128746393 does not exist
[2013-02-04 13:43:24 EST]  13538@  CONTEXT:  xlog redo visible: rel 
16408/16409/1128746393; blk 1248
[2013-02-04 13:43:24 EST]  13538@  PANIC:  WAL contains references to invalid 
pages
[2013-02-04 13:43:24 EST]  13538@  CONTEXT:  xlog redo visible: rel 
16408/16409/1128746393; blk 1248
[2013-02-04 13:43:25 EST]  13532@  LOG:  startup process (PID 13538) was 
terminated by signal 6: Aborted
[2013-02-04 13:43:25 EST]  13532@  LOG:  terminating any other active server 
processes

Looks similar to the first case, but a different context. We thought that 
perhaps an index had become corrupted (apparently also a possibility with the 
bug mentioned above) however the file mentioned belongs to a normal table, not 
an index. And 'redo visible' sounds like it might be to do with the visibility 
map?

We restarted it again with debugging cranked up. It didn't reveal anything more 
interesting. We then upgraded the standby to 9.2.2 and started it again. Again 
no dice. In each case it fails at exactly the same point with the same error.

Any ideas for a next troubleshooting step?

Regards // Mike



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

Re: [GENERAL] Reverse Engr into erwin

[Edson Richter]

  
  
I don't know about ErWin. If you look
  for alternatives that would include a tool change, then continue
  reading.
  I do use DBWrench, is working fairly well.
  
  Prs:
  - Multiple diagrams for same database/schema (can reduce the
  amount of tables you are looing), with some glyphs to help
  organize them
  - True reverse and forward engineering (work most of time)
  - Smart rename/recreate with data move when alter is not an option
  - Support triggers, tables, schemas, functions, indexes, unique
  constraints, primary and foreign keys
  - Intuitive interface
  - Capable of conversion between 4 databases supported (PostgreSQL,
  Oracle, MS SQL Server, and Access)
  - Use can tweak data types
  - Latest release (that I don't own) support PostgreSQL arrays and
  other special data types
  - Written in Java, runs everywhere (I do run in Windows and Linux)
  - Diagrams saved in XML format, it is easy to implement versioning
  
  Cons:
  - No check constraints support
  - No index operator class
  - Bugs in function/procedure reverse engineering/forward
  enginnering
  - Support is so-so, no big deal since the tool work most of time
  - Oriented to upgrades (need to buy new version to get some fixes
  for known bugs)
  
  You can get 30 day trial (at least, it was available at time I was
  evaluating for buy).
  
  PS: I don't work for the company that sell. I'm just a (almost
  happy) customer that feels if they have more huge user base, they
  would add more features quicker.
  
  
  Regards,
  
  Edson
  
  
  Em 04/02/2013 20:35, Little, Douglas escreveu:


  
  
  
  
  
Thanks in advance for thinking about my
  problem. 

As I suspect you know, CA Erwin doesnt
  support Postgres or greenplum. 
But they do support ODBC for reverse
  engineering.

When I reverse, Erwin executes the standard
  ODBC metadata queries for the system catalog.
The process works fine, but Im finding
  that the datatype thats returned is the internal PG type
  instead of the standard set of ANSI types. 
Eg, a BIGINT column returns as INT8. 

I could live with the non-standard lables,
  if the resulting DDL would execute. But PGadmin doesnt know
  the data types. 

Ive traced the same function to PGadmin
  and find that it is submitting a query with the
  format_type(type oid) function to convert to ANSI types. 

Ive installed the newest PG ODBC driver
  and the problem remains. 

Im hoping that there is a setting in ODBC
  that I can use to have the ANSI datatypes returned.
Short of that, Is there anyway to change
  the Query thats submitted

Its querying the pg_attribute, pg_type
  type with the schema name specified Pg_catalog, so I dont
  think I can over ride the select. Perhaps a rule might work.

Any suggestions on moving forward?

Thanks



Doug
  Little

Sr.
Data Warehouse Architect | Business Intelligence
Architecture | Orbitz Worldwide 
500
W. Madison, Suite 1000 Chicago IL 60661| Office
312.260.2588| Fax 312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com
 orbitz.com
  | ebookers.com
  | hotelclub.com
  | cheaptickets.com
  | ratestogo.com | asiahotels.com

  


  

Re: [GENERAL] partial time stamp query

[Misa Simic]

Select time2::date, extract('hour' from time2), AVG(avg) from tablename
group by time2::date, extract('hour' from time2)


On Monday, February 4, 2013, Kirk Wythers wrote:

 Hi Brent,

 Nice to hear from you. I hope your world is good.

 On Feb 4, 2013, at 2:14 PM, Brent Wood brent.w...@niwa.co.nzjavascript:;
 wrote:

  Hi Kirk,
 
  We have a (near) real time data database for instrument observations
 from our research vessels. All observations (summarised to one minute
 intervals - the actual raw data is in netCDF, this database makes for
 easier access  meets most users needs) go into a single table, with other
 tables for metadata about platforms, instruments, etc. Now approaching 350m
 records, so reasonably substantial.
 
  Underlying optimisations include
 
  partitioned readings table, with a separate partition for each year (now
 23 years)
  clustered index on timestamp for the previous years partitions.
  largeish filesystem block size - tested to work well with the clustered
 index  small size records)
 
  These generally make a big difference to performance. To address one
 issue, much like yours, where some users want hourly data for a year, some
 want daily data for 10 years  some want 1 minute data for the last month
 ( some, no doubt, want one minute data for 20+ years!) I introduced an
 integer column called timer. This value is set according to the time (not
 date) of each record.

 Very similar to what I need to do. Our main table consists of records that
 have been standardized to 15 minute timestamps. Here is a simplified example

 record  timestamp   variablevalue
 1   12:00:00temp12.6
 2   12:15:00temp12.3
 3   12:30:00temp11.7
 4   12:45:00temp12.3
 5   13:00:00temp13.9
 6   13:15:00temp12.5
 7   13.30:00temp13.7
 8   13:45:00temp12.0

 You are exactly right, some people will want the original 15 minute
 version, some people will want these summarized to hourly data, and others
 will want these summarized to daily data. Still others may be satisfied
 with monthly summaries.

 
  Along the lines of (from memory) :an even no of minutes after the hour
 is 2, 5 minutes is 4, 10 minutes is 8, 15 minute is 16, 30 minutes is 32,
 60 minutes is 64, 6 hourly is 128, 12:00 AM is 256  12:00PM is 512.   When
 any timestamp is in more than one category (eg: 12:00 is all of even, 5,
 15m 30m 60 minutes), the timer value is set to the largest appropriate one.

 I'm not quite following. In my case, if I want hourly data, I'd be looking
 for…

 record  timestamp   variablevalue
 1   12:00:00temp12.225
 2   13:00:00temp13.025

 Are you saying that I could use an approach that WHILE statement?
 Something like:

 WHILE data_truc('hour', timestamp) = 12:00:00, then calulate AVG(value)?

 
  So a request for:
  1 minute data is select from table;
  2 minute data is select from table where timer =2 and timer !=15 and
 timer !=4;
  hourly data is select from table where timer =64 and timer != 15 and
 timer != 4;
  etc
 
  5  15 minute add a bit of complexity, but we gave the users what they
 wanted. This has worked well for years now,  we have an internal web
 (mapserver/openlayers based) application allowing users to visualise 
 download their selected data - they choose from an interval pick list  the
 SQL is hidden. Some extra enhancements are the automatic collation of lat 
 lon gps readings into a Postgis point for each reading record,  the
 automatic aggregation of daily points into daily track lines, so the track
 for any selected set of dates can easily be displayed on a map (the
 platforms are mobile vessels - not fixed sites)
 
  You might adapt some of these ideas for your use case?
 
  Cheers
 
  Brent Wood
 
  Programme leader: Environmental Information Delivery
  NIWA
  DDI:  +64 (4) 3860529
  
  From: pgsql-general-ow...@postgresql.org [
 pgsql-general-ow...@postgresql.org] on behalf of Kirk Wythers [
 wythe...@umn.edu]
  Sent: Tuesday, February 05, 2013 5:58 AM
  To: pgsql-general@postgresql.org
  Subject: Fwd: [GENERAL] partial time stamp query
 
  Thanks. That worked great! Now I am trying to aggregate these same
 fifteen minute to hourly. I have tried using date_trunk:
 
  date_trunc('hour', derived_tsoil_fifteen_min_stacked.time2),
 
  but date_truck only seems to aggriage the timestamp. I thought I could
 use
 
  AVG(derived_tsoil_fifteen_min_stacked.value)
 
  in combination with date_trunk, but I still get 15 minute values, not
 the hourly average from the four 15 minute records.
 
  rowid 

Re: [GENERAL] partial time stamp query

[Moshe Jacobson]

On Mon, Feb 4, 2013 at 9:45 AM, Kirk Wythers kirk.wyth...@gmail.com wrote:

 I am trying to write a query that grabs one particular day from a
 timestamp column. The data are ordered in 15 minute chunks like this:

 2010-07-07 12:45:00
 2010-07-07 13:00:00
 2010-07-07 13:15:00
 2010-07-07 13:30:00
 etc…

 WHERE derived_tsoil_fifteen_min_stacked.time2 = '2010-07-07*'
 but that grabs nothing


Just cast the timestamp to a date before you compare it:

WHERE derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'

-- 
Moshe Jacobson
Nead Werx, Inc. | Manager of Systems Engineering
2323 Cumberland Parkway, Suite 201 | Atlanta, GA 30339
mo...@neadwerx.com | www.neadwerx.com

People don't multitask because they're good at it. They do it because they
are more distracted -- David
Sanbonmatsuhttp://www.plosone.org/article/info%3Adoi%2F10.1371%2Fjournal.pone.0054402

Re: [GENERAL] partial time stamp query

[Kirk Wythers]

On Feb 4, 2013, at 7:03 PM, Misa Simic misa.si...@gmail.com wrote:

 Select time2::date, extract('hour' from time2), AVG(avg) from tablename group 
 by time2::date, extract('hour' from time2)

Thanks Misa, 

But this gives the same result as the way I was using date_trunc (not GROUPING 
BY the hour portion of the timestamp, or in this case the re-cast date). I have 
simplified the query, as much as I can, and it is below:

--COPY (
SELECT
derived_tsoil_fifteen_min_stacked.time2::date,  
extract('hour' FROM derived_tsoil_fifteen_min_stacked.time2),
data_key.plot,
data_key.variable_name,
AVG(derived_tsoil_fifteen_min_stacked.value)
FROM
data_key,
derived_tsoil_fifteen_min_stacked
WHERE
data_key.variable_channel = derived_tsoil_fifteen_min_stacked.variable 
AND data_key.block_name = derived_tsoil_fifteen_min_stacked.block_name
AND data_key.plot = 'a2'
AND derived_tsoil_fifteen_min_stacked.time2::date = '2010-07-07'::date
AND derived_tsoil_fifteen_min_stacked.variable = 'tsoil_avg1_sc'
GROUP BY
derived_tsoil_fifteen_min_stacked.time2::date, 
extract('hour' FROM derived_tsoil_fifteen_min_stacked.time2),
derived_tsoil_fifteen_min_stacked.time2,
data_key.variable_name,
data_key.plot
ORDER BY
derived_tsoil_fifteen_min_stacked.time2
--) TO '/tmp/derived_tsoil_hourly.csv' WITH CSV HEADER
;

This query returns 96 records (again, one for each 15 minute interval in the 24 
hour day). 

2010-07-07  0   a2  tsoil_sc21.054659424
2010-07-07  0   a2  tsoil_sc20.950844727
2010-07-07  0   a2  tsoil_sc20.871607666
2010-07-07  0   a2  tsoil_sc20.792370605
2010-07-07  1   a2  tsoil_sc20.713133545
2010-07-07  1   a2  tsoil_sc20.633896484
2010-07-07  1   a2  tsoil_sc20.542370605
2010-07-07  1   a2  tsoil_sc20.463133545
2010-07-07  2   a2  tsoil_sc20.383896484
2010-07-07  2   a2  tsoil_sc20.304659424
2010-07-07  2   a2  tsoil_sc20.25
2010-07-07  2   a2  tsoil_sc20.158474121
2010-07-07  3   a2  tsoil_sc20.103814697
2010-07-07  3   a2  tsoil_sc20.012288818
2010-07-07  3   a2  tsoil_sc19.945340576
2010-07-07  3   a2  tsoil_sc19.866103516
2010-07-07  4   a2  tsoil_sc19.774577637
2010-07-07  4   a2  tsoil_sc19.713133545
2010-07-07  4   a2  tsoil_sc19.646185303
2010-07-07  4   a2  tsoil_sc19.554659424
etc….

Could there be anything in the JOIN part of this query that is causing 
problems? I'm really grasping at straws now!

Thanks again,

Kirk


 
 
 On Monday, February 4, 2013, Kirk Wythers wrote:
 Hi Brent,
 
 Nice to hear from you. I hope your world is good.
 
 On Feb 4, 2013, at 2:14 PM, Brent Wood brent.w...@niwa.co.nz wrote:
 
  Hi Kirk,
 
  We have a (near) real time data database for instrument observations from 
  our research vessels. All observations (summarised to one minute intervals 
  - the actual raw data is in netCDF, this database makes for easier access  
  meets most users needs) go into a single table, with other tables for 
  metadata about platforms, instruments, etc. Now approaching 350m records, 
  so reasonably substantial.
 
  Underlying optimisations include
 
  partitioned readings table, with a separate partition for each year (now 23 
  years)
  clustered index on timestamp for the previous years partitions.
  largeish filesystem block size - tested to work well with the clustered 
  index  small size records)
 
  These generally make a big difference to performance. To address one issue, 
  much like yours, where some users want hourly data for a year, some want 
  daily data for 10 years  some want 1 minute data for the last month ( 
  some, no doubt, want one minute data for 20+ years!) I introduced an 
  integer column called timer. This value is set according to the time (not 
  date) of each record.
 
 Very similar to what I need to do. Our main table consists of records that 
 have been standardized to 15 minute timestamps. Here is a simplified example
 
 record  timestamp   variablevalue
 1   12:00:00temp12.6
 2   12:15:00temp12.3
 3   12:30:00temp11.7
 4   12:45:00temp12.3
 5   13:00:00temp13.9
 6   13:15:00temp12.5
 7   13.30:00temp13.7
 8   13:45:00temp

Re: [GENERAL] What language is faster, C or PL/PgSQL?

[Bruce Momjian]

On Mon, Feb  4, 2013 at 08:33:02AM -0600, Merlin Moncure wrote:
 On Sat, Feb 2, 2013 at 11:36 AM, Carlo Stonebanks
 stonec.regis...@sympatico.ca wrote:
  Here is an advantage Plpgsql has:
  http://www.postgresql.org/docs/9.1/static/plpgsql-expressions.html
 
  I guess you can offset this by creating your own prepared statements in C.
  Otherwise, I can’t think of how C could be slower. I would choose C for
  functions that don’t have SQL statements in them – e.g. math and string
  processing.
 
 For cases involving data processing (SPI calls), C can be slower
 because pl/pgsql has a lot of optimizations in it that can be very
 easy to miss.  I don't suggest writing backend C functions at all
 unless you are trying to interface with a C library to access
 functionality currently not exposed in SQL.

How is PL/pgSQL faster than C?  I thought we had optimized PL/pgSQL to
save parsed functions, but I don't see how that would help with queries,
which use SPI.  Am I missing something?

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


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

Re: [GENERAL] What language is faster, C or PL/PgSQL?

[Carlo Stonebanks]

If a C function was a call to multiple (unprepared) SQL statements, could 
PL/PGSQL's prepare-once plan caching have an advantage?


-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Bruce Momjian
Sent: February 5, 2013 12:06 AM
To: Merlin Moncure
Cc: Carlo Stonebanks; kesco...@estudiantes.uci.cu; pgsql-general@postgresql.org
Subject: Re: [GENERAL] What language is faster, C or PL/PgSQL?

On Mon, Feb  4, 2013 at 08:33:02AM -0600, Merlin Moncure wrote:
 On Sat, Feb 2, 2013 at 11:36 AM, Carlo Stonebanks 
 stonec.regis...@sympatico.ca wrote:
  Here is an advantage Plpgsql has:
  http://www.postgresql.org/docs/9.1/static/plpgsql-expressions.html
 
  I guess you can offset this by creating your own prepared statements in C.
  Otherwise, I can t think of how C could be slower. I would choose C 
  for functions that don t have SQL statements in them   e.g. math and 
  string processing.
 
 For cases involving data processing (SPI calls), C can be slower 
 because pl/pgsql has a lot of optimizations in it that can be very 
 easy to miss.  I don't suggest writing backend C functions at all 
 unless you are trying to interface with a C library to access 
 functionality currently not exposed in SQL.

How is PL/pgSQL faster than C?  I thought we had optimized PL/pgSQL to save 
parsed functions, but I don't see how that would help with queries, which use 
SPI.  Am I missing something?

-- 
  Bruce Momjian  br...@momjian.ushttp://momjian.us
  EnterpriseDB http://enterprisedb.com

  + It's impossible for everything to be true. +


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



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

Re: [GENERAL] What language is faster, C or PL/PgSQL?

[Pavel Stehule]

2013/2/5 Bruce Momjian br...@momjian.us:
 On Mon, Feb  4, 2013 at 08:33:02AM -0600, Merlin Moncure wrote:
 On Sat, Feb 2, 2013 at 11:36 AM, Carlo Stonebanks
 stonec.regis...@sympatico.ca wrote:
  Here is an advantage Plpgsql has:
  http://www.postgresql.org/docs/9.1/static/plpgsql-expressions.html
 
  I guess you can offset this by creating your own prepared statements in C.
  Otherwise, I can’t think of how C could be slower. I would choose C for
  functions that don’t have SQL statements in them – e.g. math and string
  processing.

 For cases involving data processing (SPI calls), C can be slower
 because pl/pgsql has a lot of optimizations in it that can be very
 easy to miss.  I don't suggest writing backend C functions at all
 unless you are trying to interface with a C library to access
 functionality currently not exposed in SQL.

 How is PL/pgSQL faster than C?  I thought we had optimized PL/pgSQL to
 save parsed functions, but I don't see how that would help with queries,
 which use SPI.  Am I missing something?

PL/pgSQL can be faster than badly written C functions if there are
bottle neck is in server side routines. Any well written C code will
be faster then well written PL/pgSQL - how much depends on specific
use case. If bottle neck is in IO op, then not too much - PL/pgSQL has
not any specific optimization, that cannot be used in C.

Regards

Pavel


 --
   Bruce Momjian  br...@momjian.ushttp://momjian.us
   EnterpriseDB http://enterprisedb.com

   + It's impossible for everything to be true. +


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


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