date:20160214

Re: [GENERAL] four template0 databases after vacuum

2016-02-14 Thread David G. Johnston

On Sun, Feb 7, 2016 at 8:14 AM, Melvin Davidson 
wrote:

> With regards to Karsten's thought, here is a query to find any pg_catalog
> indexes that are corrupt.
>
> SELECT n.nspname as schema,
>i.relname as table,
>i.indexrelname as index,
>i.idx_scan,
>i.idx_tup_read,
>i.idx_tup_fetch,
>CASE WHEN idx.indisprimary
> THEN 'pkey'
> WHEN idx.indisunique
> THEN 'uidx'
> ELSE 'idx'
> END AS type,
>pg_get_indexdef(idx.indexrelid),
>CASE WHEN idx.indisvalid
> THEN 'valid'
> ELSE 'INVALID'
> END as statusi,
>pg_relation_size(quote_ident(n.nspname)|| '.' ||
> quote_ident(i.relname)) as size_in_bytes,
>pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
> quote_ident(i.relname))) as size
>   FROM pg_stat_all_indexes i
>   JOIN pg_class c ON (c.oid = i.relid)
>   JOIN pg_namespace n ON (n.oid = c.relnamespace)
>   JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
>  WHERE n.nspname = 'pg_catalog'
>AND NOT idx.indisvalid
>  ORDER BY 1, 2, 3;
>
>
This only returns "invalid" indexes but couldn't some forms of corruption
result in errors without the "indisvalid" flag being removed?

Given that OID is a PK I don't see how there isn't some form of corruption
going on here.

Aside from their presence indicating that something is wrong, somewhere,
the fact that there are four of these template0's doesn't seem that
problematic.  They neither depend upon nor are depended upon within the
cluster.  Though I am a bit curious how "createdb -T template0" still
works.  I guess it must ignore the extra records during its search...

I've trimmed a lot here but both up and down thread two observations seem
meaningful when considering that as far as PostgreSQL is concerned only one
of the 4 template0s is even visible.  There isn't enough use of "ctid" in
the other queries to confirm that they are all talking about the same
physical entry.

1)
"""
But, the age of three template0 did not change. Only [one] of 4 template0
had successfully changed the age young.
"""
and 2)
"""
 12772 | (36,25) | template0 | 10 |6 | C  | C|
t | f|   -1 | 12772 |   2412920847
|  1663 | {=c/pos
tgres,postgres=CTc/postgres}
 12772 | (36,26) | template0 | 10 |6 | C  | C|
t | f|   -1 | 12772 |   2264969019
|  1663 | {=c/pos
tgres,postgres=CTc/postgres}
"""

Note that the first row recognizes that the maximum "datlastsysoid" has
significantly increased compared to what the other three rows report (all
identical to the second record).

(36,25) seems to be in play while all of others, while present, are simply
ignored.

Now, that said, if not every part of the system (like
autovacuum-calculations) truly is ignoring them then that discrepancy is
quite likely to cause a problem.

The question is whether its worth any effort exploring this further or
should we just advise that the system is presently in an inconsistent state
and that said inconsistency needs to be corrected by any means possible -
in this case the obvious answer is DELETE FROM pg_database WHERE oid = #
AND ctid != (36,25);followed by a REINDEX on pg_database.

I do not suggest this myself - I would only do this myself if a hacker
agreed.

The rest of it hints to a potential bug or improvement, somewhere, but I
suspect the cost/benefit of finding it is going to be prohibitive based
upon a single report on an unpatched release.  But that is coming from a
non-hacker.

David J.

Re: [GENERAL] four template0 databases after vacuum

2016-02-14 Thread Kazuaki Fujikura

Firstly, I apologise for the delay in replying to you.


>On which of the database clusters are you seeing the 4 template0
databases?
template0 looks the same on both the master and slave,

===
$ psql -U user -l -h master | grep template0
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
$ psql -U user -l -h slave1 | grep template0
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
$ psql -U user -l -h slave2 | grep template0
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
 template0  | postgres   | UTF8 | C
  | C  | =c/postgres  +
===


>How are the databases created?

We create database at createdb command.
ex) createdb -O user -E UTF8 --lc-ctype ja_JP.UTF8  -T template0 


Best regards,
Kazuaki Fujikura


2016-02-11 4:41 GMT+09:00 Adrian Klaver :

> On 02/10/2016 07:19 AM, Kazuaki Fujikura wrote:
>
>> Thank you for your comments.
>>
>>  >Can you explain what your replication set up is?
>>
>> Streaming Replication.
>> =>
>> masterslave1 (async)
>> masterslave2 (async)
>>
>>  >So are you doing the below on the master, the slaves or all?
>> =>master
>>
>> Our current plan at this moment is
>> - Upgrade 9.1.19 from 9.1.6 at the end of March at our scheduled
>> maintenance
>> - Export and import pg_dump files ( to eliminate the effect of template0
>> and xid, which are all reset at import)
>>
>> We are still afraid that it is too late (as we still have 4 strange
>> template0 files). So, if you have any ideas/suggestions which we can try
>> before scheduled maintenance, that is much appreciated.
>>
>
> On which of the database clusters are you seeing the 4 template0 databases?
>
> From a previous post:
>
> "We have more than 1100 databases and create new database every day
> whenever new customer comes. .."
>
> How are the databases created?
>
>
>
>>
>> Best regards,
>> Kazuaki Fujikura
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

2016-02-14 Thread John R Pierce


On 2/14/2016 7:45 PM, Augori wrote:

This matches what Steve was seeing on this page:
http://lists.osgeo.org/pipermail/postgis-users/2014-August/039520.html
But his question seemed to reach a dead end without resolution.


must be something specific about amazon.  I'm on a centos 6.recent box, 
and ran..


[root@new ~]# yum install postgis2_93
Loaded plugins: fastestmirror, refresh-packagekit, security
Setting up Install Process
Loading mirror speeds from cached hostfile
 * base: mirrors.sonic.net
 * epel: linux.mirrors.es.net
 * extras: centos.vwtonline.net
 * nux-dextop: mirror.li.nux.ro
 * updates: centos.mirrors.hoobly.com
Resolving Dependencies
--> Running transaction check
---> Package postgis2_93.x86_64 0:2.1.8-1.rhel6 will be installed
--> Processing Dependency: postgis-client = 2.1.8-1.rhel6 for package: 
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: json-c for package: 
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libjson-c.so.2()(64bit) for package: 
postgis2_93-2.1.8-1.rhel6.x86_64

--> Running transaction check
---> Package json-c.x86_64 0:0.11-12.el6 will be installed
---> Package postgis2_93-client.x86_64 0:2.1.8-1.rhel6 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

==
 PackageArch   Version RepositorySize
==
Installing:
 postgis2_93x86_64 2.1.8-1.rhel6 pgdg936.4 M
Installing for dependencies:
 json-c x86_64 0.11-12.el6 base  51 k
 postgis2_93-client x86_64 2.1.8-1.rhel6 pgdg93124 k

Transaction Summary
==
Install   3 Package(s)

Total download size: 6.6 M
Installed size: 54 M
Is this ok [y/N]: y
Downloading Packages:
(1/3): json-c-0.11-12.el6.x86_64.rpm | 51 kB 00:00
(2/3): postgis2_93-2.1.8-1.rhel6.x86_64.rpm  | 6.4 MB 00:01
(3/3): postgis2_93-client-2.1.8-1.rhel6.x86_64.rpm   | 124 kB 00:00
-
Total   4.6 MB/s | 6.6 MB 00:01
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing : 
json-c-0.11-12.el6.x86_64 1/3
  Installing : 
postgis2_93-client-2.1.8-1.rhel6.x86_64   2/3
  Installing : 
postgis2_93-2.1.8-1.rhel6.x86_64  3/3
  Verifying  : 
json-c-0.11-12.el6.x86_64 1/3
  Verifying  : 
postgis2_93-client-2.1.8-1.rhel6.x86_64   2/3
  Verifying  : 
postgis2_93-2.1.8-1.rhel6.x86_64  3/3


Installed:
  postgis2_93.x86_64 0:2.1.8-1.rhel6

Dependency Installed:
  json-c.x86_64 0:0.11-12.el6 postgis2_93-client.x86_64 0:2.1.8-1.rhel6

Complete!


[root@new ~]# ls -l /usr/lib64/libpop*
lrwxrwxrwx. 1 root root  24 May 21  2014 
/usr/lib64/libpoppler-glib.so.4 -> libpoppler-glib.so.4.0.0
-rwxr-xr-x  1 root root  213736 Jun 24  2011 
/usr/lib64/libpoppler-glib.so.4.0.0
lrwxrwxrwx. 1 root root  19 May 21  2014 /usr/lib64/libpoppler.so.5 
-> libpoppler.so.5.0.0

-rwxr-xr-x  1 root root 1947720 Jun 24  2011 /usr/lib64/libpoppler.so.5.0.0
lrwxrwxrwx  1 root root  28 Jun  1  2014 /usr/lib64/libpopt.so -> 
../../lib64/libpopt.so.0.0.0

[root@new ~]# rpm -qf /usr/lib64/libpoppler.so.5
poppler-0.12.4-3.el6_0.1.x86_64

and poppler-0.12.4-3.el6_0.1.x86_64.rpm is in the base repository.


--
john r pierce, recycling bits in santa cruz



--
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] Trouble installing PostGIS on Amazon Linux server

2016-02-14 Thread Chris Mair




Error: Package: gdal-libs-1.9.2-6.rhel6.x86_64 (pgdg93)
Requires: libpoppler.so.5()(64bit)
  You could try using --skip-broken to work around the problem
  You could try running: rpm -Va --nofiles --nodigest


Hi,

what happens if you try to install libpoppler (it is in the standard Amazon 
repo)?

yum install poppler poppler-devel poppler-cpp poppler-cpp-devel

Bye,
Chris.




--
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] Trouble installing PostGIS on Amazon Linux server

2016-02-14 Thread Augori

Thanks, Devrim.  It looks like epel is already installed:

 rpm -ivh epel-release-6-8.noarch.rpm
warning: epel-release-6-8.noarch.rpm: Header V3 RSA/SHA256 Signature, key
ID 0608b895: NOKEY
Preparing...  #
[100%]
package epel-release-6-8.noarch is already installed

I tried installing with the enablerepo flag, but I got the same error.

# sudo yum install postgis2_93 --enablerepo=epel
Loaded plugins: priorities, update-motd, upgrade-helper
amzn-main/latest | 2.1 kB 00:00
amzn-updates/latest  | 2.3 kB 00:00
epel/x86_64/metalink |  13 kB 00:00
955 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package postgis2_93.x86_64 0:2.1.8-1.rhel6 will be installed
--> Processing Dependency: postgis-client = 2.1.8-1.rhel6 for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: geos >= 3.4.2 for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: proj for package: postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: json-c for package: postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: hdf5 for package: postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libproj.so.0()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libjson-c.so.2()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libgeos_c.so.1()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libgdal.so.1()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Running transaction check
---> Package gdal-libs.x86_64 0:1.9.2-6.rhel6 will be installed
--> Processing Dependency:
libmysqlclient.so.16(libmysqlclient_16)(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libxerces-c-3.0.so()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libtiff.so.3()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libspatialite.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libpoppler.so.5()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libodbcinst.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libodbc.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libnetcdf.so.6()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: liblzma.so.0()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libjasper.so.1()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libgta.so.0()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libgeotiff.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libfreexl.so.1()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libdapserver.so.7()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libdapclient.so.3()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libdap.so.11()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libcfitsio.so.0()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libarmadillo.so.4()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libCharLS.so.1()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
---> Package geos.x86_64 0:3.4.2-1.4.amzn1 will be installed
---> Package hdf5.x86_64 0:1.8.5.patch1-9.el6 will be installed
---> Package json-c.x86_64 0:0.11-6.8.amzn1 will be installed
---> Package postgis2_93-client.x86_64 0:2.1.8-1.rhel6 will be installed
---> Package proj.x86_64 0:4.8.0-2.rhel6 will be installed
--> Running transaction check
---> Package CharLS.x86_64 0:1.0-1.el6 will be installed
---> Package armadillo.x86_64 0:4.550.2-1.el6 will be installed
--> Processing Dependency: liblapack.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libclapack.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libcblas.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libblas.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libarpack.so.2()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
---> Package cfitsio.x86_64 0:3.240-3.el6 will be installed
---> Package compat-libtiff3.x86_64 0:3.9.4-10.13.amzn1 will be installed
---> Package freexl.x86_64 0:1.0.0d-1.el6 will be installed
---> Package gdal-libs.x86_64 0:1.9.2-6.rhel6 will be installed
--> Processing Dependency:

Re: [GENERAL] Windows performance

2016-02-14 Thread George Neuner

On Fri, 12 Feb 2016 14:43:55 -0800, John R Pierce
 wrote:

>On 2/12/2016 2:28 PM, George Neuner wrote:
>> In Linux the distinction between a "workstation" and a "server" is
>> largely a matter of system configuration.  Windows "desktop" and
>> "server" editions are different code bases: there are no magic
>> settings that can make one equivalent to the other.
>
>thats not actually true, the kernels are built from the same code base, 

Technicality: the "code base" may be the same but the _code_ is not.

Corresponding[*] desktop and server editions install different code
for a number of key modules.  This is easily verified by comparing the
installations.

[*] server 2008  <>  windows 7
 server 2012  <>  windows 8

Haven't seen server 2016 yet.

>but there are internal settings that change the behavior defaults in the 
>scheduler, like prioritizing services vs the desktop.   these settings 
>have been obfuscated, at one time you could tweak them in the registry.

You still can tweak a great many things IFF you know how.  But it
isn't (and never was) possible to tweak a desktop into a server.

George



-- 
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] Optimize Query

2016-02-14 Thread Alban Hertroys

> On 14 Feb 2016, at 20:40, drum.lu...@gmail.com wrote:
> 
> Hi Alban! Sorry.. that was my mistake

Okay, first advice on that query: Trim it down to something that people can 
wrap their minds around.

You have a silly amount of code repetition in there, much of which doesn't even 
seem to serve a purpose. This is some kind of generated query, I gather?
For example, you don't use most of the fields from your first subquery.

Another example is the nested subquery in your left join, which can be reduced 
to a single subquery with just the fields summed that you actually need (and 
the customer_id, obviously).

The same goes for most of the joins inside that left join, definitely the left 
joins - but that depends a bit on your table definitions and contents.
For example, the fields you're summing come from account (but you can use 
customer instead, since you only use the account_id, which equals client_id 
anyway) and bill_item. Some fields in your where-clause come from job, some 
others I can't tell where they're from.

Trim, trim, trim, until you're left with a more readable query that gives you 
the same results and then put it through explain analyze again. It wouldn't 
surprise me if that query is already significantly faster.

If you're still having problems at that point, post that query and the analysis 
again.

> Explain analyze link:
> http://explain.depesz.com/s/5WJy

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

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

[GENERAL] Trouble installing PostGIS on Amazon Linux server

2016-02-14 Thread Augori

I'm trying to install postgis on an Amazon Linux AMI  2015.09 - x86_64
machine. I was able to install PostgreSQL 9.3, however, when I try to
install postgis2_93, I get a notice that gdal requires a libpopper
library.  When I --skip-broken it misses a whole bunch of dependencies and
though it doesn't throw any errors,  it doesn't install.I found people
complaining about this problem back in Apr 2015, but I couldn't find any
resolution.  Do you folks have any suggestions?

# yum install postgis2_93
Loaded plugins: priorities, update-motd, upgrade-helper
955 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package postgis2_93.x86_64 0:2.1.8-1.rhel6 will be installed
--> Processing Dependency: postgis-client = 2.1.8-1.rhel6 for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: geos >= 3.4.2 for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: proj for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: json-c for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: hdf5 for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libproj.so.0()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libjson-c.so.2()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libgeos_c.so.1()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Processing Dependency: libgdal.so.1()(64bit) for package:
postgis2_93-2.1.8-1.rhel6.x86_64
--> Running transaction check
---> Package gdal-libs.x86_64 0:1.9.2-6.rhel6 will be installed
--> Processing Dependency: libmysqlclient.so.16(libmysqlclient_16)(64bit)
for package: gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libxerces-c-3.0.so()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libtiff.so.3()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libspatialite.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libpoppler.so.5()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libodbcinst.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libodbc.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libnetcdf.so.6()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libmysqlclient.so.16()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: liblzma.so.0()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libjasper.so.1()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libgta.so.0()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libgeotiff.so.2()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libfreexl.so.1()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libdapserver.so.7()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libdapclient.so.3()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libdap.so.11()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libcfitsio.so.0()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libarmadillo.so.4()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
--> Processing Dependency: libCharLS.so.1()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
---> Package geos.x86_64 0:3.4.2-1.4.amzn1 will be installed
---> Package hdf5.x86_64 0:1.8.5.patch1-9.el6 will be installed
---> Package json-c.x86_64 0:0.11-6.8.amzn1 will be installed
---> Package postgis2_93-client.x86_64 0:2.1.8-1.rhel6 will be installed
---> Package proj.x86_64 0:4.8.0-2.rhel6 will be installed
--> Running transaction check
---> Package CharLS.x86_64 0:1.0-1.el6 will be installed
---> Package armadillo.x86_64 0:4.550.2-1.el6 will be installed
--> Processing Dependency: liblapack.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libclapack.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libcblas.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libblas.so.3()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
--> Processing Dependency: libarpack.so.2()(64bit) for package:
armadillo-4.550.2-1.el6.x86_64
---> Package cfitsio.x86_64 0:3.240-3.el6 will be installed
---> Package compat-libtiff3.x86_64 0:3.9.4-10.13.amzn1 will be installed
---> Package freexl.x86_64 0:1.0.0d-1.el6 will be installed
---> Package gdal-libs.x86_64 0:1.9.2-6.rhel6 will be installed
--> Processing Dependency: libpoppler.so.5()(64bit) for package:
gdal-libs-1.9.2-6.rhel6.x86_64
---> Package jasper-libs.x86_64 0:1.900.1-16.9.amzn1 will be installed
---> Package libdap.x86_64 0:3.11.0-1.el6

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

2016-02-14 Thread Devrim Gündüz

Hi,

I think you also need to add/enable EPEL repo, too.

Regards,Devrim

On February 15, 2016 12:09:01 AM GMT+02:00, Augori  wrote:
>I'm trying to install postgis on an Amazon Linux AMI  2015.09 - x86_64
>machine. I was able to install PostgreSQL 9.3, however, when I try to
>install postgis2_93, I get a notice that gdal requires a libpopper
>library.  When I --skip-broken it misses a whole bunch of dependencies
>and
>though it doesn't throw any errors,  it doesn't install.I found
>people
>complaining about this problem back in Apr 2015, but I couldn't find
>any
>resolution.  Do you folks have any suggestions?
>
># yum install postgis2_93
>Loaded plugins: priorities, update-motd, upgrade-helper
>955 packages excluded due to repository priority protections
>Resolving Dependencies
>--> Running transaction check
>---> Package postgis2_93.x86_64 0:2.1.8-1.rhel6 will be installed
>--> Processing Dependency: postgis-client = 2.1.8-1.rhel6 for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: geos >= 3.4.2 for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: proj for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: json-c for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: hdf5 for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: libproj.so.0()(64bit) for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: libjson-c.so.2()(64bit) for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: libgeos_c.so.1()(64bit) for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Processing Dependency: libgdal.so.1()(64bit) for package:
>postgis2_93-2.1.8-1.rhel6.x86_64
>--> Running transaction check
>---> Package gdal-libs.x86_64 0:1.9.2-6.rhel6 will be installed
>--> Processing Dependency:
>libmysqlclient.so.16(libmysqlclient_16)(64bit)
>for package: gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libxerces-c-3.0.so()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libtiff.so.3()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libspatialite.so.2()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libpoppler.so.5()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libodbcinst.so.2()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libodbc.so.2()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libnetcdf.so.6()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libmysqlclient.so.16()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: liblzma.so.0()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libjasper.so.1()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libgta.so.0()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libgeotiff.so.2()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libfreexl.so.1()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libdapserver.so.7()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libdapclient.so.3()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libdap.so.11()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libcfitsio.so.0()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libarmadillo.so.4()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>--> Processing Dependency: libCharLS.so.1()(64bit) for package:
>gdal-libs-1.9.2-6.rhel6.x86_64
>---> Package geos.x86_64 0:3.4.2-1.4.amzn1 will be installed
>---> Package hdf5.x86_64 0:1.8.5.patch1-9.el6 will be installed
>---> Package json-c.x86_64 0:0.11-6.8.amzn1 will be installed
>---> Package postgis2_93-client.x86_64 0:2.1.8-1.rhel6 will be
>installed
>---> Package proj.x86_64 0:4.8.0-2.rhel6 will be installed
>--> Running transaction check
>---> Package CharLS.x86_64 0:1.0-1.el6 will be installed
>---> Package armadillo.x86_64 0:4.550.2-1.el6 will be installed
>--> Processing Dependency: liblapack.so.3()(64bit) for package:
>armadillo-4.550.2-1.el6.x86_64
>--> Processing Dependency: libclapack.so.3()(64bit) for package:
>armadillo-4.550.2-1.el6.x86_64
>--> Processing Dependency: libcblas.so.3()(64bit) for package:
>armadillo-4.550.2-1.el6.x86_64
>--> Processing Dependency: libblas.so.3()(64bit) for package:
>armadillo-4.550.2-1.el6.x86_64
>--> Processing Dependency: libarpack.so.2()(64bit) for package:
>armadillo-4.550.2-1.el6.x86_64
>---> Package cfitsio.x86_64 0:3.240-3.el6 will be installed
>---> Package compat-libtiff3.x86_64 0:3.9.4-10.13.amzn1 will be
>installed
>---> Package freexl.x86_64 0:1.0.0d-1.el6 will be installed
>---> Package

Re: [GENERAL] Optimize Query

2016-02-14 Thread drum.lu...@gmail.com

Hi Alban! Sorry.. that was my mistake


Original Query:

SELECT concat(company, ' ', customer_name_first, ' ',
customer_name_last) AS customer,
   sum(revenue) AS revenue,
   sum(i.quantity) AS quantity,
   sum(i.cost) AS costFROM
  ( SELECT account.id,
   job.customerid,
   job.title,
   job.gps_lat,
   job.gps_long,
   status.label AS status,
   status.status_type_id,
   job.status_label_id,
   client."position",
   bill_item.quantity,
   client.businesstype,
   account.id AS clientid,
   client.name_first AS customer_name_first,
   client.name_last AS customer_name_last,
   job.id AS jobid,
   note.mobiuserid,
   bill_item.for_invoicing AS invoice,
   COALESCE(bill_item.unit_price, billable.unit_price, 0) AS unit_price,
   note.n_quote_status,
   COALESCE(bill_item.unit_cost, billable.unit_cost, 0) AS unit_cost,
   job.time_job,
   "user".name_first,
   "user".name_last,
   role.id AS roleid,
   role.name AS role_name,
   billable.billable_id AS taskid,
   COALESCE(labs.tag, billable.code) AS task_name,
   note.time_start,
   client.company,
   job.refnum,
   (COALESCE(bill_item.unit_cost, billable.unit_cost, 0) *
bill_item.quantity) AS cost,
   (COALESCE(bill_item.unit_price, billable.unit_price, 0) *
bill_item.quantity) AS revenue,
   bill_item.for_invoicing AS invoiceable,
   COALESCE(extract('epoch'
FROM bill.ts_creation AT TIME ZONE 'UTC'),
bill_item.invoice_id, NULL) IS NOT NULL AS invoiced
   FROM ja_clients AS account
   JOIN ja_customers AS client ON client.clientid = account.id
   JOIN ja_jobs AS job ON client.id=job.customerid
   JOIN ja_notes AS note ON note.jobid = job.id
   JOIN dm.bill_items AS bill_item ON bill_item.bill_item_id=note.bill_item_id
   LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id
   LEFT JOIN dm.invoices AS invoice ON invoice.invoice_id=bill.invoice_id
   OR invoice.invoice_id=bill_item.invoice_id
   LEFT JOIN dm.billables AS billable ON billable.billable_id=note.billable_id
   LEFT JOIN dm.labors AS labs ON labs.billable_id = billable.billable_id
   JOIN ja_mobiusers AS "user" ON "user".id = note.mobiuserid
   JOIN ja_status AS status ON status.id = job.status_label_id
   JOIN ja_role AS ROLE ON ROLE.id="user".user_type
   WHERE note.note_type::text = ANY (ARRAY[ ('time'::CHARACTER VARYING)::text,
('part'::CHARACTER VARYING)::text ])
 AND NOT job.templated
 AND NOT job.deleted
 AND job.clientid = 6239
 AND time_job >= 1438351200
 AND time_job <= 144340
 AND bill_item.for_invoicing = TRUE) AS iLEFT JOIN
  (SELECT customerid,
  SUM(cost) AS cost,
  SUM(quantity) AS quantity
   FROM
 (SELECT account.id,
 job.customerid,
 job.title,
 job.gps_lat,
 job.gps_long,
 status.label AS status,
 status.status_type_id,
 job.status_label_id,
 client."position",
 bill_item.quantity,
 client.businesstype,
 account.id AS clientid,
 client.name_first AS customer_name_first,
 client.name_last AS customer_name_last,
 job.id AS jobid,
 note.mobiuserid,
 bill_item.for_invoicing AS invoice,
 COALESCE(bill_item.unit_price, billable.unit_price, 0) AS
unit_price,
 note.n_quote_status,
 COALESCE(bill_item.unit_cost, billable.unit_cost, 0) AS unit_cost,
 job.time_job,
 "user".name_first,
 "user".name_last,
 ROLE.id AS roleid,
  ROLE.name AS role_name,
   billable.billable_id AS taskid,
   COALESCE(labs.tag, billable.code) AS task_name,
   note.time_start,
   client.company,
   job.refnum,
   (COALESCE(bill_item.unit_cost,
billable.unit_cost, 0) * bill_item.quantity) AS cost,
   (COALESCE(bill_item.unit_price,
billable.unit_price, 0) * bill_item.quantity) AS revenue,
   bill_item.for_invoicing AS invoiceable,
   COALESCE(extract('epoch'
FROM bill.ts_creation AT TIME
ZONE 'UTC'), bill_item.invoice_id, NULL) IS NOT NULL AS invoiced
  FROM ja_clients AS account
  JOIN ja_customers AS client ON client.clientid = account.id
  JOIN ja_jobs AS job ON client.id=job.customerid
  JOIN ja_notes AS note ON note.jobid = job.id
  JOIN dm.bill_items AS bill_item ON
bill_item.bill_item_id=note.bill_item_id
  LEFT JOIN dm.bills AS bill ON bill.bill_id=bill_item.bill_id
  LEFT JOIN

Re: [GENERAL] Question on how to use to_timestamp()

2016-02-14 Thread Vitaly Burovoy

On 2/13/16, Adrian Klaver  wrote:
> On 02/13/2016 07:42 PM, Deven Phillips wrote:
>> I'm trying to convert a series of ISO8601 strings into TIMESTAMPs for
>> use with a function:
>>
>> CREATE OR REPLACE FUNCTION v1_nexus_vlan_count(id TEXT, start_time
>> TIMESTAMP, end_time TIMESTAMP)
>> RETURNS TEXT AS $$
>> SELECT jsonb_pretty(jsonb_agg(row_to_json(datapoints))) AS data_array FROM
>> (
>> SELECT
>>  data->>'timestamp' AS collection_time,
>>  data->'data'->'vlans'->>'available' AS available,
>>  data->'data'->'vlans'->>'total' AS total,
>>  data->'data'->'vlans'->>'used' AS used
>> FROM
>>  gathered_data
>> WHERE
>>  data->>'id'=$1 AND
>>  to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')>=$2 AND
>>  to_timestamp(data->>'timetsamp', '-MM-DDTHH24:MI:SSZ')<=$3
>> ORDER BY
>>  to_timestamp(data->>'timestamp', '-MM-DDTHH24:MI:SSZ')) AS
>> datapoints $$
>> LANGUAGE SQL;
>>
>> The conversions for to_timestamp() seems to be my problem. I keep
>> getting an error:
>>
>> # SELECT to_timestamp('2016-01-01T00:00:00Z', '-MM-DDTHH24:MI:SSZ');
>>
>> ERROR:  invalid value ":0" for "MI"
>> DETAIL:  Value must be an integer.
>> Time: 1.016 ms
>>
>> Could anyone suggest what it is that I might be doing wrong here?
>
> test=>  SELECT to_timestamp('2016-01-01T00:00:00Z',
> '-MM-DD"T"HH24:MI:SSZ');
>
>   to_timestamp
> 
>  2016-01-01 00:00:00-08

Oops. I've just discovered that letter.

Adrian, your answer is not fully correct, because
'2016-01-01T00:00:00Z' is *NOT* the same as '2016-01-01 00:00:00-08'!
Unfortunately, "to_timestamp" always returns timestamptz in a
time-zone offset from current "TIME ZONE" setting:

postgres=# SET TIME ZONE 'Europe/London';
SET
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'-MM-DD"T"HH24:MI:SSZ') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
   ts   |  to_timestamp
+
 2016-01-01 00:00:00+00 | 2016-01-01 00:00:00+00
(1 row)

postgres=# SET TIME ZONE 'Pacific/Honolulu';
SET
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'-MM-DD"T"HH24:MI:SSZ') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
   ts   |  to_timestamp
+
 2015-12-31 14:00:00-10 | 2016-01-01 00:00:00-10
(1 row)

postgres=# SET TIME ZONE 'Australia/Sydney';
SET
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'-MM-DD"T"HH24:MI:SSZ') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
   ts   |  to_timestamp
+
 2016-01-01 11:00:00+11 | 2016-01-01 00:00:00+11
(1 row)

... and it can't get time zone from an input string:
postgres=# SELECT ts::timestamptz, to_timestamp(ts,
'-MM-DD"T"HH24:MI:SSOF') FROM
(VALUES('2016-01-01T00:00:00Z'))t(ts);
ERROR:  "TZ"/"tz"/"OF" format patterns are not supported in to_date

So Deven's query can be rewritten as:

CREATE OR REPLACE FUNCTION v1_nexus_vlan_count(id TEXT, start_time
TIMESTAMP, end_time TIMESTAMP)
RETURNS TEXT AS $$
SELECT jsonb_pretty(jsonb_agg(row_to_json(datapoints))) AS data_array FROM
(
SELECT
 data->>'timestamp' AS collection_time,
 data->'data'->'vlans'->>'available' AS available,
 data->'data'->'vlans'->>'total' AS total,
 data->'data'->'vlans'->>'used' AS used
FROM
 gathered_data
WHERE
 data->>'id'=$1 AND
 $2 <= (data->>'timestamp')::timestamptz
   AND (data->>'timetsamp')::timestamptz <= $3  -- "<=" or just "<"?
ORDER BY
 (data->>'timestamp')::timestamptz
) AS datapoints
$$
LANGUAGE SQL;

Deven, pay attention "start_time" and "end_time" are "timestamp", not
"timestampTZ", so comparison uses "TIME ZONE" setting:
postgres=# SET TIME ZONE 'Pacific/Honolulu';
SET
postgres=# SELECT ts::timestamp, ts::timestampTZ, ts::timestamp <
ts::timestampTZ FROM (VALUES('2016-01-01T00:00:00Z'))t(ts);
 ts  |   ts   | ?column?
-++--
 2016-01-01 00:00:00 | 2015-12-31 14:00:00-10 | f
(1 row)

postgres=# SET TIME ZONE 'Europe/London';
SET
postgres=# SELECT ts::timestamp, ts::timestampTZ, ts::timestamp <
ts::timestampTZ FROM (VALUES('2016-01-01T00:00:00Z'))t(ts);
 ts  |   ts   | ?column?
-++--
 2016-01-01 00:00:00 | 2016-01-01 00:00:00+00 | f
(1 row)

postgres=# SET TIME ZONE 'Australia/Sydney';
SET
postgres=# SELECT ts::timestamp, ts::timestampTZ, ts::timestamp <
ts::timestampTZ FROM (VALUES('2016-01-01T00:00:00Z'))t(ts);
 ts  |   ts   | ?column?
-++--
 2016-01-01 00:00:00 | 2016-01-01 11:00:00+11 | t
(1 row)

If you want to compare using specific time zone, you have to convert
input values to it:
...
WHERE
 data->>'id'=$1 AND
 ($2 AT TIME ZONE 'America/New_York') <=

Re: [GENERAL] Optimize Query

2016-02-14 Thread Alban Hertroys

> On 13 Feb 2016, at 11:21, drum.lu...@gmail.com wrote:
> 
> Anyone can help with that please?
> 
> Cheers

What would help is:
1. to post an actual query that you need optimised and
2. an explain analyze of that query.

What you posted in your original message was some kind of query-template with 
enough placeholders and views that there is no way to predict how that's going 
to perform without at least knowing what goes into the placeholders and how the 
views are built up.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

-- 
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] four template0 databases after vacuum

Re: [GENERAL] four template0 databases after vacuum

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

Re: [GENERAL] Windows performance

Re: [GENERAL] Optimize Query

[GENERAL] Trouble installing PostGIS on Amazon Linux server

Re: [GENERAL] Trouble installing PostGIS on Amazon Linux server

Re: [GENERAL] Optimize Query

Re: [GENERAL] Question on how to use to_timestamp()

Re: [GENERAL] Optimize Query

12 matches

Site Navigation

Mail list logo

Footer information