Re: [GENERAL] Test CMake build

[Teodor Sigaev]

I tried it on FreeBSD 64-bit, 16Gb, SSD, Core i7

( ./configure && gmake all; )  168,99s user 15,46s system 97% cpu 3:09,61 total
( cmake . && gmake all; )  75,11s user 11,34s system 100% cpu 1:26,30 total

Cmake 2 times faster, that is good, but I don't understand why. Which 
optimization level does cmake buld use by default? Which compiler does it take? 
It's not obvious, because cmake build hides actual compiler command line.


Yury, pls, return back check target...
--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/


--
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] PosgreSQL Security Architecture

[Albe Laurenz]

Lesley Kimmel wrote:
> I'm working to secure a PosgreSQL database according to a DoD security guide. 
> It has many very generic
> requirements that get more toward the internal architecture of the system 
> that wouldn't be apparent to
> the average admin. I was hoping someone might have some insight to the 
> following requirements:
> 
> 
> a) The DBMS must maintain the authenticity of communications sessions by 
> guarding against man-in-the-
> middle attacks that guess at Session ID values.

You can have that if you use SSL encryption which is available in PostgreSQL:
http://www.postgresql.org/docs/current/static/ssl-tcp.html

It uses that widely-used OpenSSL software, so an encrypted database connection
is as safe from man-in-the-middle attacks as OpenSSL is.

> b) Check DBMS settings and vendor documentation to verify the DBMS properly 
> handles transactions in
> the event of a system failure. The consistent state must include a security 
> configuration that is at
> least as restrictive as before the system failure. This must be guaranteed.

I don't understand what is meant by "security configuration" here.
Is that defined somewhere?

PostgreSQL handles system failures well, it uses a "Write Ahead Log" (WAL) to 
record
transactions as they get committed. In the case of a system failure, the
recovery process starts at the latest checkpoint (known consistent state) 
before the
failure and exactly replays all WAL logged committed transactions up to the 
point of
the crash:
http://www.postgresql.org/docs/current/static/wal-intro.html

After crash recovery, the database is in the same state as it was after the last
successful transaction.
The (unrecovered) database files of a crashed database are no less secure than
any file system backup is.

Yours,
Laurenz Albe

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

[GENERAL] database corruption

[Oliver Stöneberg]

We are running a 64-bit PostgreSQL 9.4.5 server on Windows Server 
2012. The system is a virtual machine on a VMware ESX 6.0 server and 
has 24 GB of memory. The database server is only accessed locally by 
two services and there is only a single database in the server. The 
disk is located on a storage that is shared with lots of other 
servers. The database server has fsync enabled.

A few weeks ago we already had a data corruption when the disk was 
full. There are other services running on the same machine that could 
cause the disk to fill up (e.g. local chaching when the network is 
acting up). It happened a few times so far but the database was never 
compromised. In that case thought it was but fortunately we only lost 
a huge table/toast (300+ GB) that has very verbose data stored which 
is not essential. That happened with an earlier 9.4 version.

Today we encountered another data corruption after the disk was full. 
It's much worse this time around since data that is essential for the 
applications using it to run. After truncating that 300+ GB table 
already mentioned above all the services were restarted and one of 
the applications failed to start with the following database error:

Caused by: org.postgresql.util.PSQLException: ERROR: invalid memory alloc 
request size 18446744073709551613
at 
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
at 
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
at 
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:622)
at 
org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:472)
at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:386)
at 
joulex.cloud.data.tables.JxObjectStoreJsonTable.getAllJsonsAsHashMap(JxObjectStoreJsonTable.java:215)
... 16 more

Checking the database logs the problems seem to have started two days 
ago:
2016-02-10 16:00:34 GMTERROR:  invalid page in block 1255 of relation 
base/16387/3634911224
2016-02-10 16:00:34 GMTCONTEXT:  automatic vacuum of table 
"cloud.t_63d1c1eb806f4e25904b659b75176093.y2016_utilization"

For some reason it doesn't seem to be caused by the disk being full 
since the database server was still able to write another 8 GB of log 
files for the next two days and the system was still working 
yesterday afternoon.
It also doesn't appear to be a hardware problem since all the other 
systems sharing the virtual hostsystem and the storage show no issues 
at all.

Unfortunately we don't have a recent backup of the database (a tool 
to back up all the relevant data was just finished recently and was 
not set up for this system yet).

Something else worth noting is that we disabled the autovacuum on the 
toast table of the 300+ GB table since we perform INSERT INTO on that 
tbale and the vacuum on the table was causing a performance hit. The 
autovacuum for it is still being performed to prevent wraparound from 
time to and that autovacuum was still running after the machine run 
out of disk space and the services was restarted.

Any help in recovering the data is appreciated and if there is more 
information necessary on this I will try to provide it. Thanks in 
advance.


-- 
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] memory problem with refresh materialized view

[Albe Laurenz]

Enrico Pirozzi wrote:
> I have a new postgresql 9.5.0 installation on a new virtual server debian 8.3 
> x64 with 4gb RAM, I have
> compiled postgresql from source.
> 
> When I import a dump with materialized views I see that postgres process 
> takes about all 4 Gb and then
> I have this error
> 
>  fork: Cannot allocate memory

What are the memory related settings of the database into which you restore?

Yours,
Laurenz Albe

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

[GENERAL] PostgreSQL 9.5 and process REST calls enquiry

[Peter van Eck]

Hi, We are looking into setting up a PostgreSQL environment for an 
application that inputs JSON through via http rest calls.


Now that we havent been using Postgres for these kind of setups I was 
wondering what the course of action is in setting this up.


The development team is coding this in srpingboot with an embedded 
mongodb version. Do we have to configure pgrest for instance to enable 
postgres for processing JSON rest calls via HTTP or is that not necessary ?

Again just puzzling in how to approach this...


thanks in advance.

Peter


--
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] database corruption

[Bill Moran]

On Fri, 12 Feb 2016 10:56:04 +0100
"Oliver Stöneberg"  wrote:

> We are running a 64-bit PostgreSQL 9.4.5 server on Windows Server 
> 2012. The system is a virtual machine on a VMware ESX 6.0 server and 
> has 24 GB of memory. The database server is only accessed locally by 
> two services and there is only a single database in the server. The 
> disk is located on a storage that is shared with lots of other 
> servers. The database server has fsync enabled.
> 
> A few weeks ago we already had a data corruption when the disk was 
> full. There are other services running on the same machine that could 
> cause the disk to fill up (e.g. local chaching when the network is 
> acting up). It happened a few times so far but the database was never 
> compromised. In that case thought it was but fortunately we only lost 
> a huge table/toast (300+ GB) that has very verbose data stored which 
> is not essential. That happened with an earlier 9.4 version.
> 
> Today we encountered another data corruption after the disk was full. 
> It's much worse this time around since data that is essential for the 
> applications using it to run. After truncating that 300+ GB table 
> already mentioned above all the services were restarted and one of 
> the applications failed to start with the following database error:
> 
> Caused by: org.postgresql.util.PSQLException: ERROR: invalid memory alloc 
> request size 18446744073709551613
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2182)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1911)
>   at 
> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:173)
>   at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:622)
>   at 
> org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:472)
>   at org.postgresql.jdbc.PgStatement.executeQuery(PgStatement.java:386)
>   at 
> joulex.cloud.data.tables.JxObjectStoreJsonTable.getAllJsonsAsHashMap(JxObjectStoreJsonTable.java:215)
>   ... 16 more
> 
> Checking the database logs the problems seem to have started two days 
> ago:
> 2016-02-10 16:00:34 GMTERROR:  invalid page in block 1255 of relation 
> base/16387/3634911224
> 2016-02-10 16:00:34 GMTCONTEXT:  automatic vacuum of table 
> "cloud.t_63d1c1eb806f4e25904b659b75176093.y2016_utilization"
> 
> For some reason it doesn't seem to be caused by the disk being full 
> since the database server was still able to write another 8 GB of log 
> files for the next two days and the system was still working 
> yesterday afternoon.
> It also doesn't appear to be a hardware problem since all the other 
> systems sharing the virtual hostsystem and the storage show no issues 
> at all.
> 
> Unfortunately we don't have a recent backup of the database (a tool 
> to back up all the relevant data was just finished recently and was 
> not set up for this system yet).
> 
> Something else worth noting is that we disabled the autovacuum on the 
> toast table of the 300+ GB table since we perform INSERT INTO on that 
> tbale and the vacuum on the table was causing a performance hit. The 
> autovacuum for it is still being performed to prevent wraparound from 
> time to and that autovacuum was still running after the machine run 
> out of disk space and the services was restarted.
> 
> Any help in recovering the data is appreciated and if there is more 
> information necessary on this I will try to provide it. Thanks in 
> advance.

You most likely have byte-level corruption. If you need that data back,
your best bet is to hire a company with PostgreSQL experts who know
the structure of how the data is stored on disk and can manipulate
the files directly to recover whatever hasn't been destroyed. If you
want to do it yourself, it will require you to understand the actual
byte sequences as they are stored on disk, as well as the system
PostgreSQL uses to identify database pages within the file system.
First you will have to indentify the file that contains the corrupt
page, then you will have to modify the bytes in the page to make the
page non-corrupt. (do this with Postgres shut down) In any event,
that sort of thing is touchy work, even if you do understand it well,
so make sure you have a full copy of all database files so you can
roll back if you make things worse.

Long term, you need to fix your hardware. Postgres doesn't corrupt
itself just because the disks fill up, so your hardware must be lying
about what writes completed successfully, otherwise, Postgres would
be able to recover after a restart.

Beyond that, running Postgres on a filesystem that frequently fills up
is going to be problematic all around anyway. If you don't improve the
hardware situation, you're going to continue to have problems like this.

-- 
Bill Moran


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your 

Re: [GENERAL] PosgreSQL Security Architecture

[Lesley Kimmel]

Thanks for the reply Laurenz. Of course the first thing that I thought of
to prevent man-in-the-middle was SSL. However, I also like to try to
address the issue in a way that seems to get at what they are intending. It
seemed to me that they wanted to do some configuration within the database
related to session IDs.

Regarding what is meant by "security configuration", I couldn't say for
sure. These guides are very much open to interpretation. In any case your
answers are helpful. Thanks again!

On Fri, Feb 12, 2016 at 3:02 AM, Albe Laurenz 
wrote:

> Lesley Kimmel wrote:
> > I'm working to secure a PosgreSQL database according to a DoD security
> guide. It has many very generic
> > requirements that get more toward the internal architecture of the
> system that wouldn't be apparent to
> > the average admin. I was hoping someone might have some insight to the
> following requirements:
> >
> >
> > a) The DBMS must maintain the authenticity of communications sessions by
> guarding against man-in-the-
> > middle attacks that guess at Session ID values.
>
> You can have that if you use SSL encryption which is available in
> PostgreSQL:
> http://www.postgresql.org/docs/current/static/ssl-tcp.html
>
> It uses that widely-used OpenSSL software, so an encrypted database
> connection
> is as safe from man-in-the-middle attacks as OpenSSL is.
>
> > b) Check DBMS settings and vendor documentation to verify the DBMS
> properly handles transactions in
> > the event of a system failure. The consistent state must include a
> security configuration that is at
> > least as restrictive as before the system failure. This must be
> guaranteed.
>
> I don't understand what is meant by "security configuration" here.
> Is that defined somewhere?
>
> PostgreSQL handles system failures well, it uses a "Write Ahead Log" (WAL)
> to record
> transactions as they get committed. In the case of a system failure, the
> recovery process starts at the latest checkpoint (known consistent state)
> before the
> failure and exactly replays all WAL logged committed transactions up to
> the point of
> the crash:
> http://www.postgresql.org/docs/current/static/wal-intro.html
>
> After crash recovery, the database is in the same state as it was after
> the last
> successful transaction.
> The (unrecovered) database files of a crashed database are no less secure
> than
> any file system backup is.
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] Test CMake build

[Teodor Sigaev]

Teodor Sigaev wrote:

I tried it on FreeBSD 64-bit, 16Gb, SSD, Core i7

( ./configure && gmake all; )  168,99s user 15,46s system 97% cpu 3:09,61 total
( cmake . && gmake all; )  75,11s user 11,34s system 100% cpu 1:26,30 total
( CFLAGS='-O2' cmake . && gmake all; )  141,87s user 12,18s system 97% cpu 
2:37,40 total


Oops, cmake default target is compiled with -O0. With -O2 cmake is still faster 
but not so much.




Cmake 2 times faster, that is good, but I don't understand why. Which
optimization level does cmake buld use by default? Which compiler does it take?
It's not obvious, because cmake build hides actual compiler command line.

Yury, pls, return back check target...


--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/


--
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] Test CMake build

[Alvaro Herrera]

Teodor Sigaev wrote:

> Cmake 2 times faster, that is good, but I don't understand why. Which
> optimization level does cmake buld use by default? Which compiler does it
> take? It's not obvious, because cmake build hides actual compiler command
> line.

Hm, I don't think having the compile/link lines be hidden up is
acceptable.  Many times we need to debug some compile problem, and the
output is mandatory.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] database corruption

[Andrew Sullivan]

On Fri, Feb 12, 2016 at 07:46:25AM -0500, Bill Moran wrote:
> Long term, you need to fix your hardware. Postgres doesn't corrupt
> itself just because the disks fill up, so your hardware must be lying
> about what writes completed successfully, otherwise, Postgres would
> be able to recover after a restart.

It may not be the hardware.  Depending on how vmware is configured, it
could just be a setting.  Also, something in the OP's message made me
think that this was _actually_ a network-attached disk, which can also
have such problems.  (But in general, I agree.)

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
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] Test CMake build

[Alvaro Herrera]

Teodor Sigaev wrote:
> >Hm, I don't think having the compile/link lines be hidden up is
> >acceptable.  Many times we need to debug some compile problem, and the
> >output is mandatory.
> 
> +1
> 
> Although it could be fixed by
> VERBOSE=1 make

Verbose needs to be the default.   Having a QUIET mode would be nice
sometime in the future, but if I were you, I wouldn't propose that for
the first cut of this patch -- I think it's easier to sell if you keep
the current behavior unchanged.  We can discuss further improvements
later.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
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] Test CMake build

[Teodor Sigaev]

Hm, I don't think having the compile/link lines be hidden up is
acceptable.  Many times we need to debug some compile problem, and the
output is mandatory.


+1

Although it could be fixed by
VERBOSE=1 make

--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/


--
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] 9.4 -> 9.5 dump size reduction

[FarjadFarid(ChkNet)]

Before transferring the data. 

Compare the default settings and logging configuration. 



 
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Tom Lane
Sent: 12 February 2016 04:49
To: Seb
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 9.4 -> 9.5 dump size reduction

Seb  writes:
> I recently upgraded a server from 9.4 to 9.5 (Debian) via 
> pg_upgradecluster.  The upgrade finished well, and so far the 
> databases in the upgraded cluster seem in good form.  However, I 
> noticed a dramatic reduction in the size of the dump created by the new
server.
> One of the databases was 34G when dumped by the 9.4 server is now 
> dumped at 1.1G in the new 9.5 version (using pg_dump -Fc in both 
> cases).  What has caused such remarkable improvement?!

That seems really fishy.  Better check to see if all your data is still
there :-(

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



-- 
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] Test CMake build

[Tom Lane]

Alvaro Herrera  writes:
> Teodor Sigaev wrote:
>> Cmake 2 times faster, that is good, but I don't understand why. Which
>> optimization level does cmake buld use by default? Which compiler does it
>> take? It's not obvious, because cmake build hides actual compiler command
>> line.

> Hm, I don't think having the compile/link lines be hidden up is
> acceptable.  Many times we need to debug some compile problem, and the
> output is mandatory.

As long as it's *possible* to expose the commands, I see nothing wrong
with hiding them by default.  I personally almost always use "make -s"
these days, and would not mind if that became the default behavior.
But there had better be a switch to do the other thing.

The other make switch I use all the time is -jN (with varying values of N
depending on what machine I'm on).  If cmake can't provide an equivalent
feature, that would be a large minus, because if you have a decent number
of cores -j makes a huge difference in build time.

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] PostgreSQL 9.5 and process REST calls enquiry

[David G. Johnston]

On Fri, Feb 12, 2016 at 4:00 AM, Peter van Eck  wrote:

>
>
> Hi, We are looking into setting up a PostgreSQL environment for an
> application that inputs JSON through via http rest calls.
>
> Now that we havent been using Postgres for these kind of setups I was
> wondering what the course of action is in setting this up.
>
> The development team is coding this in srpingboot with an embedded mongodb
> version. Do we have to configure pgrest for instance to enable postgres for
> processing JSON rest calls via HTTP or is that not necessary ?
> Again just puzzling in how to approach this...
>

​PostgreSQL doesn't speak HTTP so you will need some piece of
software/middleware that is capable of speaking both HTTP and PostgreSQL.​
 You already have eas access to a HTTP understanding container in
Spring/Java and a JDBC driver but there may be some extensions and/or
applications out there - like this pgrest you speak of though its GitHub
page shows little recent activity - that can provide an abstraction layer
for you.  I'll leave it to your favorite search engine and others to
provide recommendations in this area.

David J.

Re: [GENERAL] ERROR: missing FROM-clause entry for table

[bigkev]

Yep, that nailed it.
Thanks for you time and help.



--
View this message in context: 
http://postgresql.nabble.com/ERROR-missing-FROM-clause-entry-for-table-tp5886750p5887183.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] XMLEXISTS on legacy XML with malformed xmlns

[Edson Richter]

2016-02-12 1:53 GMT+01:00 Edson Richter >:


Hi!

I've some (about 1M records) containing legacy XML I would like to
parse and apply XMLEXISTS.

This is the query:

select * from xmllog
 where xpath_exists(('//MyDocument[@DocNum =
''000411828'']'::text), xmlparse(document cdataout));

This is the error:

ERRO: could not parse XML document
SQL state: 2200M
Detail: line 2: xmlns:leg: 'LEGACYAPP - SEND MSG EVENTS ABOUT' is
not a valid URI

^

This is the sample XML with malformed xmlns (I've shortenet the
data, but the important thing here is the malformed xmlns):

"

  

  0
  PostgreSQL uses libxml2, but the usage isn't too configurable. So my 
advice is using defensive strategy and clean/fix wrong namespace with 
string tools - replace function.


Regards

Pavel


Thanks, Pavel.

I did suspect that. But then I have about 10.000 new records each week, 
and I've no control over the system that generates it.

It is a shame, but sometimes we have to live with such problems.

Regards,

Edson Richter

Re: [GENERAL] PostgreSQL 9.5 and process REST calls enquiry

[Peter van Eck]

On 12/02/16 17:34, Adrian Klaver wrote:

On 02/12/2016 08:06 AM, James Keener wrote:


https://github.com/begriffs/postgrest also looks interesting!


I thought the purpose of Spring/Spring Boot was to provide the REST
services in front of your choice of data store. Not sure how putting
another server in the stack is going to help things.


I was simply responding to the original question about accessing the DB
over HTTP.


Then I am not sure what the question is.

Do you want to know how to use Spring Boot with Postgres or do you 
want suggestions for programs/frameworks/applications that provide 
REST services over Postgres?


Or is something else that I am entirely missing:)?



Hi Adrian, James,

Thanks for your replies sofar.

It is exactly my point that for us the request from the developers 
wasn't clear to us as we don't have the knowledge to apparently fully 
understand the question.
I think it is as you describe Adrian  "Do you want to know how to use 
Spring Boot with Postgres"
So we'll go with that and see when the first release is pushed from the 
developers.


thnaks again.


Peter




--
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] Code of Conduct plan

[Tom Lane]

Josh Berkus  wrote:
> 1. The Core Team will appoint an exploration committee which will look
> at various proposals (including the one drafted on pgsql-general) for
> CoCs and discuss them.

To follow up on this ...

The Core Team are pleased to announce that Stacey Haysler has accepted
our invitation to chair the exploratory committee on a Postgres Code of
Conduct.  Stacey is very well qualified to do this, since she is a well
known member of the Postgres community and has had an extended career in
human resources, including creation and implementation of
anti-discrimination and anti-harassment policies.

Stacey will be reaching out to potential committee members over the next
few days or weeks.  Once the committee is assembled, they will devise
some way (possibly a new mailing list, though I don't want to pre-judge
it) for the wider community to have input into the discussions.
In the meantime, we ask that people continue to refrain from flooding
pgsql-general or other existing PG lists with CoC-related threads.
There will be a time and a place for those discussions, but not yet.

If you have interest or concerns about this process, you can contact
Stacey at shaysler...@gmail.com or the Core Team at
pgsql-c...@postgresql.org.

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] Test CMake build

[Andy Colson]

On 2/12/2016 9:47 AM, Yury Zhuravlev wrote:

Andy Colson wrote:

Is the installcheck important to you?


Hello!
You can try new make check. Also "make install" started support DESTDIR.



cmake and make -j2 fine, but then

andy@mapper:~/projects/postgres_cmake/build$ make check
Scanning dependencies of target check
CMake Error: cmake version 2.8.12
Usage: /usr/bin/cmake -E [command] [arguments ...]
Available commands:
  chdir dir cmd [args]...   - run command in a given directory
  compare_files file1 file2 - check if file1 is same as file2
  copy file destination - copy file to destination (either file or 
directory)
  copy_directory source destination   - copy directory 'source' content 
to directory 'destination'

  copy_if_different in-file out-file  - copy file if input has changed
  echo [string]...  - displays arguments as text
  echo_append [string]...   - displays arguments as text but no new line
  environment   - display the current environment
  make_directory dir- create a directory
  md5sum file1 [...]- compute md5sum of files
  remove [-f] file1 file2 ... - remove the file(s), use -f to force it
  remove_directory dir  - remove a directory and its contents
  rename oldname newname- rename a file or directory (on one volume)
  tar [cxt][vfz][cvfj] file.tar [file/dir1 file/dir2 ...]
- create or extract a tar or zip archive
  time command [args] ...   - run command and return elapsed time
  touch file- touch a file.
  touch_nocreate file   - touch a file but do not create it.
Available on UNIX only:
  create_symlink old new- create a symbolic link new -> old

make[3]: *** [src/test/regress/CMakeFiles/check] Error 1
make[2]: *** [src/test/regress/CMakeFiles/check.dir/all] Error 2
make[1]: *** [src/test/regress/CMakeFiles/check.dir/rule] Error 2
make: *** [check] Error 2





--
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] Test CMake build

[Yury Zhuravlev]

Tom Lane wrote:

The other make switch I use all the time is -jN (with varying values of N
depending on what machine I'm on).  If cmake can't provide an equivalent
feature, that would be a large minus, because if you have a decent number
of cores -j makes a huge difference in build time.


Of course supported.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
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] Test CMake build

[Yury Zhuravlev]

Andy Colson wrote:

Its not important, but is pretty nice.

It's not hard and I think I will do soon.


Anyway, thanks for all your work on this.  Looking good.


Thanks!  


--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
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] PostgreSQL 9.5 and process REST calls enquiry

[Adrian Klaver]

On 02/12/2016 03:00 AM, Peter van Eck wrote:



Hi, We are looking into setting up a PostgreSQL environment for an
application that inputs JSON through via http rest calls.

Now that we havent been using Postgres for these kind of setups I was
wondering what the course of action is in setting this up.

The development team is coding this in srpingboot with an embedded
mongodb version. Do we have to configure pgrest for instance to enable
postgres for processing JSON rest calls via HTTP or is that not necessary ?
Again just puzzling in how to approach this...


The below looks like a good start:

http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html




thanks in advance.

Peter





--
Adrian Klaver
adrian.kla...@aklaver.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] Windows performance

[Sterpu Victor]

Hello

Why is Postgres so slow on Windows compared to linux?
Can I do something to match the performance?
I have 2 servers:
- one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs 
in 17 seconds
- the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs 
in 2 seconds

I run the query on the same database.

CPU is not used at max on the servers, RAM is fine.
Is there a problem with Windows? My gues is that Windows is not using 
the hardware resources as it should be.

Can I do something to fix this?

Thank you.

Re: [GENERAL] PostgreSQL 9.5 and process REST calls enquiry

[James Keener]

>
>
> https://github.com/begriffs/postgrest also looks interesting!
>>
>
> I thought the purpose of Spring/Spring Boot was to provide the REST
> services in front of your choice of data store. Not sure how putting
> another server in the stack is going to help things.


I was simply responding to the original question about accessing the DB
over HTTP.

Re: [GENERAL] PostgreSQL 9.5 and process REST calls enquiry

[Adrian Klaver]

On 02/12/2016 08:06 AM, James Keener wrote:


https://github.com/begriffs/postgrest also looks interesting!


I thought the purpose of Spring/Spring Boot was to provide the REST
services in front of your choice of data store. Not sure how putting
another server in the stack is going to help things.


I was simply responding to the original question about accessing the DB
over HTTP.


Then I am not sure what the question is.

Do you want to know how to use Spring Boot with Postgres or do you want 
suggestions for programs/frameworks/applications that provide REST 
services over Postgres?


Or is something else that I am entirely missing:)?


--
Adrian Klaver
adrian.kla...@aklaver.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] Test CMake build

[Yury Zhuravlev]

Andy Colson wrote:

Is the installcheck important to you?


Hello!
You can try new make check. Also "make install" started support DESTDIR.

--
Yury Zhuravlev
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company


--
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] Windows performance

[Sterpu Victor]

There are some differences that I haven't mentioned.
Postgres on Linux is PostgreSQL 9.1.4 64 bit
Postgres on Windows is PostgreSQL 9.5.0, compiled by Visual C++ build 
1800, 64-bit


The query is very big but I pasted it at the end of the mail with the 
EXPLAIN ANALYZE.
I runned the queries many times on every machine, the cache is not 
involved.


postgresql.conf are defaults, I haven't compared them yet but I will.


"Limit  (cost=163011.25..163011.63 rows=1 width=3640) (actual 
time=2811.693..2812.109 rows=20 loops=1)"
"  ->  WindowAgg  (cost=163011.25..163011.63 rows=1 width=3640) (actual 
time=2811.691..2812.103 rows=20 loops=1)"
"->  Group  (cost=163011.25..163011.60 rows=1 width=3640) 
(actual time=2711.668..2711.823 rows=20 loops=1)"
"  ->  Sort  (cost=163011.25..163011.26 rows=1 width=3640) 
(actual time=2711.662..2711.685 rows=21 loops=1)"
"Sort Key: j1031101.validfrom, j1033386.name, 
j1033387.name, j1033359.validto, j1031076.name, j1031074.id, 
j1031074.siui_appid, j1031074.data_adeverinta, j1031074.is_paliativ, 
j1031074.cardno, j1031074.cardno_externare, j1031074.sign_date, 
j1031074.sign_date_externare, j1031074.unsigned_string, 
j1031074.unsigned_string_externare, j1031074.signhash, 
j1031074.signhash_externare, j1031074.signature, 
j1031074.signature_externare, j1031074.send_xml, 
j1031074.send_xml_externare, j1031074.received_xml, 
j1031074.received_xml_externare, j1031074.error, 
j1031074.error_externare, j1031074.validat, j1031074.validat_externare, 
j1031074.online, j1031074.online_externare, 
j1031074.serie_bilet_internare, j1031074.nr_bilet_internare, 
j1031074.idpatient, j1031075.cnp, j1031075.name, j1031075.surname, 
j1031074.nrfo, j1031074.greutate_nastere, j1031078.value, 
j1031074.idensuredstatustype, j1031079.value, j1031074.idensuredstatus, 
j1031080.code, j1031074.id_org_unit, j1031081.code, 
j1031074.id_categorie_asigurat, j1031082.name, 
j1031074.id_focg_tip_internare, j1031083.name, 
j1031074.id_focg_criteriu_internare, j1031084.stencil_no, j1031084.id, 
j1031089.value, j1031074.id_education_level, j1031074.greutate, 
j1031090.nume, j1031074.id_focg_situatii_speciale, j1031091.nume, 
j1031091.id, j1031092.nume, j1031074.id_formulare_europene, 
j1031074.id_cnp_mama, j1031094.cnp, j1031093.nrfo, j1031074.id_focg, 
j1031074.nr_card_euro, j1031074.nr_pasaport, j1031074.nr_card_national, 
j1031088.id, j1031088.name, j1031074.export_drg, j1031074.drgcaseid, 
j1031074.export_ecosoft, j1031074.mesaj_drg, j1031074.uid, 
j1031074.mesaj_ecosoft, j1031074.id_address_domiciliu, 
j1031074.id_address_resedinta, j1031095.id, j1031095.denumire, 
j1031096.id, j1031096.code, j1031097.id, j1031097.name, j1031098.id, 
j1031098.description, j1031099.id, j1031099.name, j1031100.id, 
j1031100.code, j1031074.scrisoare_medicala_parafa, 
j1031074.scrisoare_medicala_contract, 
j1031074.scrisoare_medicala_tip_contract, j1031074.export_siui, 
j1031074.mesaj_siui, j1031087.id, j1031087.stencil_no, 
j1031074.diagnostic_trimitere_text, j1031074.greutate_externare, 
j1031074.data_decesului, j1031736.id, j1031736.descriere, j1031737.id, 
j1031737.descriere, j1033295.id, j1033295.stencil_no, j1033299.id, 
j1033299.description, j1031074.text_ore_ventilatie, 
j1031074.drg_cod_grupa, j1031074.drg_relative_value, 
j1031074.data_2500g, j1031074.prematur_gr_i, j1033304.id, 
j1033304.description, j1033358.id, j1033358.name, 
j1031074.reinternat_transfer, j1031074.aviz_comisie, 
j1031074.criteriu_urgenta1, j1031074.criteriu_urgenta2, 
j1031074.criteriu_urgenta3, j1031074.criteriu_urgenta4, 
j1031074.criteriu_urgenta5, j1031074.criteriu_urgenta6, 
j1031074.criteriu_urgenta7, j1031074.criteriu_urgenta8, 
j1031074.criteriu_urgenta9a, j1031074.criteriu_urgenta9b, 
j1031074.criteriu_urgenta10, j1031074.criteriu_urgenta11, 
j1031074.criteriu_urgenta12, j1031074.criteriu_urgenta13"

"Sort Method: external merge  Disk: 3192kB"
"->  Nested Loop  (cost=87.71..163011.24 rows=1 
width=3640) (actual time=204.650..2579.588 rows=3075 loops=1)"
"  ->  Nested Loop Left Join  
(cost=87.71..163002.94 rows=1 width=3640) (actual time=204.646..2573.701 
rows=2674 loops=1)"

"Filter: (j1033360.id IS NULL)"
"->  Nested Loop Left Join  
(cost=87.71..162994.64 rows=1 width=3648) (actual time=204.642..2568.543 
rows=2736 loops=1)"
"  ->  Nested Loop Left Join  
(cost=87.71..162994.36 rows=1 width=3631) (actual time=204.636..2563.826 
rows=2736 loops=1)"
"Filter: 
(((date(j1031101.validfrom) >= '2016-02-01'::date) AND 
(date(j1031101.validfrom) <= '2016-02-29'::date)) OR ((j1033359.validto 
IS NOT NULL) AND (date(j1033359.validto) >= '2016-02-01'::date) AND 
(date(j1033359.validto) <= '2016-02-29'::date)) OR ((j1033359.validto IS 
NULL) AND (date(j1031101.validfrom) <= '2016-02-01'::date)))"
" 

Re: [GENERAL] PostgreSQL 9.5 and process REST calls enquiry

[James Keener]

https://github.com/begriffs/postgrest also looks interesting!

On Fri, Feb 12, 2016 at 10:24 AM, Adrian Klaver 
wrote:

> On 02/12/2016 03:00 AM, Peter van Eck wrote:
>
>>
>>
>> Hi, We are looking into setting up a PostgreSQL environment for an
>> application that inputs JSON through via http rest calls.
>>
>> Now that we havent been using Postgres for these kind of setups I was
>> wondering what the course of action is in setting this up.
>>
>> The development team is coding this in srpingboot with an embedded
>> mongodb version. Do we have to configure pgrest for instance to enable
>> postgres for processing JSON rest calls via HTTP or is that not necessary
>> ?
>> Again just puzzling in how to approach this...
>>
>
> The below looks like a good start:
>
>
> http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html
>
>
>>
>> thanks in advance.
>>
>> Peter
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.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] Windows performance

[Adrian Klaver]

On 02/12/2016 07:37 AM, Sterpu Victor wrote:

Hello
Why is Postgres so slow on Windows compared to linux?
Can I do something to match the performance?
I have 2 servers:
- one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs
in 17 seconds
- the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs
in 2 seconds


Assuming the version of Postgres is the same on both machine.
In any case what are the versions on the machines?


I run the query on the same database.


What is the query and what is the output of EXPLAIN ANALYZE?


CPU is not used at max on the servers, RAM is fine.
Is there a problem with Windows?My gues is that Windows is not using


I will pass on this.


the hardware resources as it should be.
Can I do something to fix this?


Are the settings in the postgresql.conf files the same?





Thank you.



--
Adrian Klaver
adrian.kla...@aklaver.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] Windows performance

[Tim Clarke]

On 12/02/16 15:45, Adrian Klaver wrote:
> On 02/12/2016 07:37 AM, Sterpu Victor wrote:
>> Hello
>> Why is Postgres so slow on Windows compared to linux?
>> Can I do something to match the performance?
>> I have 2 servers:
>> - one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs
>> in 17 seconds
>> - the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs
>> in 2 seconds
> Assuming the version of Postgres is the same on both machine.
> In any case what are the versions on the machines?
>
>> I run the query on the same database.
> What is the query and what is the output of EXPLAIN ANALYZE?
>
>> CPU is not used at max on the servers, RAM is fine.

But that doesn't mean the servers aren't busy. To really compare, wait
until your servers are actually quiescent - ideally no other users at
all. Then assuming that Adrian's questions about configuration below
don't yield any differences *and* your databases are actually identical
on both machines you can really do a comparative test. Remember that
your first run will load the data into cache on the respective machine
so subsequent runs will be less dependent on disk sub-system speed.

>> Is there a problem with Windows?My gues is that Windows is not using
> I will pass on this.
>
>> the hardware resources as it should be.
>> Can I do something to fix this?
> Are the settings in the postgresql.conf files the same?
>
>
>
>
>> Thank you.
>>



-- 
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] PostgreSQL 9.5 and process REST calls enquiry

[Adrian Klaver]

On 02/12/2016 07:43 AM, James Keener wrote:

https://github.com/begriffs/postgrest also looks interesting!


I thought the purpose of Spring/Spring Boot was to provide the REST 
services in front of your choice of data store. Not sure how putting 
another server in the stack is going to help things.




On Fri, Feb 12, 2016 at 10:24 AM, Adrian Klaver
> wrote:

On 02/12/2016 03:00 AM, Peter van Eck wrote:



Hi, We are looking into setting up a PostgreSQL environment for an
application that inputs JSON through via http rest calls.

Now that we havent been using Postgres for these kind of setups
I was
wondering what the course of action is in setting this up.

The development team is coding this in srpingboot with an embedded
mongodb version. Do we have to configure pgrest for instance to
enable
postgres for processing JSON rest calls via HTTP or is that not
necessary ?
Again just puzzling in how to approach this...


The below looks like a good start:


http://docs.spring.io/spring-boot/docs/current/reference/html/boot-features-sql.html



thanks in advance.

Peter




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



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





--
Adrian Klaver
adrian.kla...@aklaver.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] Unrecognized configuration parameter in bdr 0.9.3

[Kaushal Shriyan]

On Fri, Feb 12, 2016 at 7:40 AM, Craig Ringer  wrote:

> On 11 February 2016 at 20:16, Kaushal Shriyan 
> wrote:
>
>> Hi,
>>
>> I am following http://bdr-project.org/docs/stable/index.html for setting
>> up multimaster replication.
>>
>
> Are you sure that's the documentation you were using?
>
> You seem to have settings that only applied to the older 0.7.x versions:
>
>
>> # (BEGIN) BDR connection settings for node 1, port 5600
>> bdr.connections = 'secondarymasterport5601'
>> bdr.secondarymasterport5601_dsn = 'dbname=bdrdemo user=postgres port=5601'
>> # (END) BDR connection settings for node 1, port 5600
>>
>> # (BEGIN) BDR connection settings for node 2, port 5601
>> #bdr.connections = '{DevEnvName}node01port5600'
>> #bdr.{DevEnvName}node01port5600_dsn = 'dbname={DevDBName} user=postgres
>> port=5600'
>> #bdr.{DevEnvName}node01port5600_init_replica = on
>> #bdr.{DevEnvName}node01port5600_replica_local_dsn = 'dbname={DevDBName}
>> user=postgres port=5601'
>> # (END) BDR connection settings for node 2, port 5601
>>
>
> The above is not used in BDR 0.9.x. Configuration is done at the SQL level.
>
> --
>

Hi Craig,

Primary Master Postgresql Node ( IP :- 192.168.10.1)

1)psql --port 5600 --username postgres --dbname bdrdemo

2)CREATE EXTENSION btree_gist;
3)CREATE EXTENSION bdr;

*4)SELECT bdr.bdr_group_create(local_node_name := 'node1',node_external_dsn
:= 'port=5600 dbname=bdrdemo');*
5)SELECT bdr.bdr_node_join_wait_for_ready();


Secondary Master Postgresql Node ( IP :- 192.168.10.2)

6)psql --port 5601 --username postgres --dbname bdrdemo

7)CREATE EXTENSION btree_gist;
8)CREATE EXTENSION bdr;

*9)SELECT bdr.bdr_group_join(local_node_name := 'node2',node_external_dsn
:= 'port=5601 dbname=bdrdemo', join_using_dsn := 'port=5600 dbname=bdrdemo'
);*
10)SELECT bdr.bdr_node_join_wait_for_ready();

Based on
http://blog.2ndquadrant.com/dynamic-sql-level-configuration-for-bdr-0-9-0/
since my setup is hosted on two different servers. So if i execute the step
no. 4 in Primary Master Node (IP :- 192.168.10.1) as below

*SELECT bdr.bdr_group_create(local_node_name := 'node1',node_external_dsn
:= 'host **192.168.10.1* *port=5600 dbname=bdrdemo');*

and Step No. 9 in Secondary Master Postgresql Node ( IP :- 192.168.10.2) as
below.

*SELECT bdr.bdr_group_join(local_node_name := 'node2',node_external_dsn :=
'port=5601 dbname=bdrdemo', join_using_dsn := 'host 192.168.10.2 port=5600
dbname=bdrdemo' );*

Are those select statements highlighted in red are supported and correct
and executed at the SQL Level meaning after adding *host **192.168.10.1 in
dsn of Primary Master* and *host **192.168.10.2 in dsn of Secondary Master*?


Please comment.

Thanks in Advance

Regards,

Kaushal

Re: [GENERAL] XMLEXISTS on legacy XML with malformed xmlns

[Pavel Stehule]

2016-02-12 17:53 GMT+01:00 Edson Richter :

>
> 2016-02-12 1:53 GMT+01:00 Edson Richter :
>
>> Hi!
>>
>> I've some (about 1M records) containing legacy XML I would like to parse
>> and apply XMLEXISTS.
>>
>> This is the query:
>>
>> select * from xmllog
>>  where xpath_exists(('//MyDocument[@DocNum = ''000411828'']'::text),
>> xmlparse(document cdataout));
>>
>> This is the error:
>>
>> ERRO: could not parse XML document
>> SQL state: 2200M
>> Detail: line 2: xmlns:leg: 'LEGACYAPP - SEND MSG EVENTS ABOUT' is not a
>> valid URI
>> 
>> ^
>>
>> This is the sample XML with malformed xmlns (I've shortenet the data, but
>> the important thing here is the malformed xmlns):
>>
>> "
>> 
>>   
>> 
>>   0
>>   >
>>
>> I can easly read this XML in Notepad++, and also in Java - but PostgreSQL
>> always throw error.
>>
>> Can you plase tell me how can make PostgreSQL ignore this malformed xmlns
>> and proceed processing the XML?
>>
>
> PostgreSQL uses libxml2, but the usage isn't too configurable. So my
> advice is using defensive strategy and clean/fix wrong namespace with
> string tools - replace function.
>
> Regards
>
> Pavel
>
>
> Thanks, Pavel.
>
> I did suspect that. But then I have about 10.000 new records each week,
> and I've no control over the system that generates it.
> It is a shame, but sometimes we have to live with such problems.
>

I understand - you can handle this error and broken xml you can ignore. Or
you can use a parser from Python, Perl - PLPerlu or PLPythonu is great for
it.

Regards

Pavel


>
> Regards,
>
> Edson Richter
>
>
>

Re: [GENERAL] PosgreSQL Security Architecture

[John R Pierce]

On 2/12/2016 5:20 AM, Lesley Kimmel wrote:
Thanks for the reply Laurenz. Of course the first thing that I thought 
of to prevent man-in-the-middle was SSL. However, I also like to try 
to address the issue in a way that seems to get at what they are 
intending. It seemed to me that they wanted to do some configuration 
within the database related to session IDs.


when the connection is broken, the process exits and the session ceases 
to exist. there are no 'session IDs' to speak of (they are process 
IDs instead, but a new process mandates new authentication, there's no 
residual authorizations associated with a PID).



--
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] Windows performance

[John R Pierce]

On 2/12/2016 8:09 AM, Sterpu Victor wrote:
The query is very big but I pasted it at the end of the mail with the 
EXPLAIN ANALYZE. 


I ran that explain through Depesz's 'explain' analyzer.
http://explain.depesz.com/s/c7ts

there's a lot(!) of small steps in that query.   I'm going to hazard a 
guess that the issue is related to semaphore overhead or something like 
that.


certainly, ti would be good to benchmark both OS's on the same version 
of postgres, comparing 9.1 to 9.5 isn't fair.


--
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] Windows performance

[Jeff Janes]

On Fri, Feb 12, 2016 at 8:09 AM, Sterpu Victor  wrote:
> There are some differences that I haven't mentioned.
> Postgres on Linux is PostgreSQL 9.1.4 64 bit
> Postgres on Windows is PostgreSQL 9.5.0, compiled by Visual C++ build 1800,
> 64-bit
>
> The query is very big but I pasted it at the end of the mail with the
> EXPLAIN ANALYZE.
> I runned the queries many times on every machine, the cache is not involved.
>
> postgresql.conf are defaults, I haven't compared them yet but I will.
>
>
> "Limit  (cost=163011.25..163011.63 rows=1 width=3640) (actual
> time=2811.693..2812.109 rows=20 loops=1)"

This seems to be the `explain analyze` for the fast one.  Can you also
post it for the slow one?

Also, for version 9.5 can you turn on track_io_timing and post the
output of EXPLAIN (analyze, buffers)?  Please do that separately from
the regular EXPLAIN ANALYZE though, to make it one of them easier to
compare to the one from version 9.1.


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

[GENERAL] Trying to move away from Firebird

[ioan ghip]

I was able to create all the domains, tables, views, etc, but I have
trouble creating stored procedures and triggers. Also, a question, does
Postgres support events, for example in Firebird I could do something like
this and then receive the event in the GUI:

  if ((NEW.MUSED_M_>=NEW.MLIMIT_M_) and (NEW.MLIMIT_M_>0) and
(NEW.ISACTIVE=1) and (NEW.FAXTOFAXFLAG=1)) then
  begin
POST_EVENT 'deactivate_f2f';
  end

Please help me translate the examples bellow so I can understand the
differences:


CREATE GENERATOR GENADMINID START WITH 0 INCREMENT BY 1;
SET GENERATOR GENADMINID TO 108;

CREATE TRIGGER B_UPDATE_COMPANY FOR COMPANY
ACTIVE AFTER UPDATE POSITION 10
AS
begin
  if (old.AGENTID != new.AGENTID) then
  begin
update USERS set USERS.AGENTID=new.AGENTID where USERS.COMPANYID =
new.COMPANYID;
  end
end

CREATE PROCEDURE GET_ATA_STATUS (
MAC VARCHAR(128),
NOW_D_ INTEGER)
RETURNS (
ATA_STATUS INTEGER)
AS
begin
  SELECT IIF((a.TIMESTAMP_D_ + a.EXPIRE) > :NOW_D_, 1, 0) FROM ATA a WHERE
a.ATAMAC = :MAC into :ATA_STATUS;
  SUSPEND;
end

Thanks a lot.

Re: [GENERAL] 9.4 -> 9.5 dump size reduction

[Sebastian P . Luque]

On Thu, 11 Feb 2016 23:48:53 -0500,
Tom Lane  wrote:

> Seb  writes:
>> I recently upgraded a server from 9.4 to 9.5 (Debian) via
>> pg_upgradecluster.  The upgrade finished well, and so far the
>> databases in the upgraded cluster seem in good form.  However, I
>> noticed a dramatic reduction in the size of the dump created by the
>> new server.  One of the databases was 34G when dumped by the 9.4
>> server is now dumped at 1.1G in the new 9.5 version (using pg_dump
>> -Fc in both cases).  What has caused such remarkable improvement?!

> That seems really fishy.  Better check to see if all your data is
> still there :-(

Sure enough, upon closer inspection I see that the pgagent job that was
generating these dumps had aborted before finishing.  It looks like the
upgrade interrupted the job.  I've restarted the agent, reran the job
right away, and now see that the dumps with the new server haven't
changed size, and all data are there.

Sorry for the premature post,

-- 
Seb



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

[GENERAL] template1 database is facing high datfrozenxid

[AI Rumman]

Hi,

I am running Postgresql 9.1 and I can see the datfrozenxid is going high
and vacuum process is not bringing it down. And this has been happening on
template1 database.

2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:  oldest xmin is
> far in the past
> 2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:  Close open
> transactions soon to avoid wraparound problems.
> 2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:  automatic vacuum of
> table "template1.pg_catalog.pg_database": index scans: 0
> pages: 0 removed, 1 remain
> tuples: 0 removed, 9 remain
> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
> 2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:  oldest xmin is
> far in the past
> 2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:  Close open
> transactions soon to avoid wraparound problems.
> 2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:  automatic vacuum of
> table "template1.pg_catalog.pg_largeobject": index scans: 0
> pages: 0 removed, 0 remain
> tuples: 0 removed, 0 remain
> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec


I vacuum database manually but nothing is working out.
Please help,

Thanks.

Re: [GENERAL] Windows performance

[Adrian Klaver]

On 02/12/2016 11:22 AM, John R Pierce wrote:

On 2/12/2016 8:09 AM, Sterpu Victor wrote:

The query is very big but I pasted it at the end of the mail with the
EXPLAIN ANALYZE.


I ran that explain through Depesz's 'explain' analyzer.
http://explain.depesz.com/s/c7ts

there's a lot(!) of small steps in that query.   I'm going to hazard a
guess that the issue is related to semaphore overhead or something like
that.

certainly, ti would be good to benchmark both OS's on the same version
of postgres, comparing 9.1 to 9.5 isn't fair.


Something else that just dawned on me, the databases are not the same 
version, is the data being queried the same in content and size?




--
Adrian Klaver
adrian.kla...@aklaver.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] Trying to move away from Firebird

[Adrian Klaver]

On 02/12/2016 02:11 PM, ioan ghip wrote:

I was able to create all the domains, tables, views, etc, but I have
trouble creating stored procedures and triggers. Also, a question, does
Postgres support events, for example in Firebird I could do something
like this and then receive the event in the GUI:

   if ((NEW.MUSED_M_>=NEW.MLIMIT_M_) and (NEW.MLIMIT_M_>0) and
(NEW.ISACTIVE=1) and (NEW.FAXTOFAXFLAG=1)) then
   begin
 POST_EVENT 'deactivate_f2f';
   end


LISTEN/NOTIFY?

http://www.postgresql.org/docs/9.5/interactive/sql-listen.html

What exactly does POST_EVENT do?



Please help me translate the examples bellow so I can understand the
differences:


CREATE GENERATOR GENADMINID START WITH 0 INCREMENT BY 1;
SET GENERATOR GENADMINID TO 108;


http://www.postgresql.org/docs/9.5/interactive/sql-createsequence.html
http://www.postgresql.org/docs/9.5/interactive/functions-sequence.html



CREATE TRIGGER B_UPDATE_COMPANY FOR COMPANY
ACTIVE AFTER UPDATE POSITION 10
AS
begin
   if (old.AGENTID != new.AGENTID) then
   begin
 update USERS set USERS.AGENTID=new.AGENTID where USERS.COMPANYID =
new.COMPANYID;
   end
end


http://www.postgresql.org/docs/9.5/interactive/sql-createtrigger.html

Difference in Postgres, you specify a separate function you want the 
trigger to execute.




CREATE PROCEDURE GET_ATA_STATUS (
 MAC VARCHAR(128),
 NOW_D_ INTEGER)
RETURNS (
 ATA_STATUS INTEGER)
AS
begin
   SELECT IIF((a.TIMESTAMP_D_ + a.EXPIRE) > :NOW_D_, 1, 0) FROM ATA a
WHERE a.ATAMAC = :MAC into :ATA_STATUS;
   SUSPEND;
end


http://www.postgresql.org/docs/9.5/interactive/sql-createfunction.html

In Postgres you have a choice of languages. Built in as of recent 
versions are:

C
http://www.postgresql.org/docs/9.5/interactive/xfunc-c.html

SQL
http://www.postgresql.org/docs/9.5/interactive/sql-createfunction.html

and the one you probably want to start with

plpgsql
http://www.postgresql.org/docs/9.5/interactive/plpgsql.html



Thanks a lot.



--
Adrian Klaver
adrian.kla...@aklaver.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] template1 database is facing high datfrozenxid

[Adrian Klaver]

On 02/12/2016 03:10 PM, AI Rumman wrote:

I checked it and I did not find any log running sql or any open
transaction. Not even in pg_prepared_xacts.
And it looks like pg_catalog database is making the alarm.

Any other idea please, where I need to look into.


Should have added:

select * from pg_database



Thanks.


On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver
> wrote:

On 02/12/2016 02:56 PM, AI Rumman wrote:

Hi,

I am running Postgresql 9.1 and I can see the datfrozenxid is
going high
and vacuum process is not bringing it down. And this has been
happening
on template1 database.

 2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:
oldest
 xmin is far in the past
 2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:
Close open
 transactions soon to avoid wraparound problems.


The above seems to be the contributing factor.

Does:

select * from pg_stat_activity

show long running queries.


 2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:  automatic
 vacuum of table "template1.pg_catalog.pg_database": index
scans: 0
 pages: 0 removed, 1 remain
 tuples: 0 removed, 9 remain
 system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
 2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:
oldest
 xmin is far in the past
 2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:
Close open
 transactions soon to avoid wraparound problems.
 2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:  automatic
 vacuum of table "template1.pg_catalog.pg_largeobject":
index scans: 0
 pages: 0 removed, 0 remain
 tuples: 0 removed, 0 remain
 system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec


I vacuum database manually but nothing is working out.
Please help,

Thanks.



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





--
Adrian Klaver
adrian.kla...@aklaver.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] Windows performance

[George Neuner]

On Fri, 12 Feb 2016 15:37:37 +, "Sterpu Victor" 
wrote:

>Why is Postgres so slow on Windows compared to linux?
>Can I do something to match the performance?
>I have 2 servers:
>- one is Windows 8, CPU XEON, 8 CORES,  32G of RAM - my test query runs 
>in 17 seconds
>- the other is Linux CPU XEON, 4 cores, 4 Gh of RAM - my test query runs 
>in 2 seconds
>I run the query on the same database.
>
>CPU is not used at max on the servers, RAM is fine.
>Is there a problem with Windows? My gues is that Windows is not using 
>the hardware resources as it should be.
>Can I do something to fix this?
>
>Thank you.

Windows and Linux have very different cache, memory management, and
processor affinity behavior.   

Additionally, Windows has suboptimal support for many POSIX features.
John Pierce mentioned the possibility of semaphores (locks) being the
problem.  If Posgresql uses POSIX locks on Window, that could
significantly impact performace (vs using Windows native locks).

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.

Windows in general needs more RAM than Linux, but given comparable
resources, Windows server editions will have comparable performance.

Windows desktop editions are designed around the expectation that
there is a single interactive user.  They are not meant to be used as
servers.


That all said, I think 17 seconds vs 2 is far too much difference.
Assuming that Windows itself is running properly [e.g., not
accidentally using PIO mode for disk I/O or something similarly
stupid], it suggests that the servers are not configured the same, or
that the usage statistics for the Windows version are wildly incorrect
and that is throwing off query planning.  

Did you analyze your database after moving it to Windows?

Another possibility is that the Windows tablespace is highly
fragmented.  Moderate levels of fragmentation don't really matter for
either Windows or Linux, but high file fragmentation favors Linux.


For best performance Postgresql really should to be tuned differently
for Windows than for Linux.  Unfortunately, although I am aware of
many of the differences between the operating systems, I'm not any
kind of expert at tuning Postgresql.

Hope this helps,
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] Windows performance

[John R Pierce]

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







--
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] template1 database is facing high datfrozenxid

[Adrian Klaver]

On 02/12/2016 02:56 PM, AI Rumman wrote:

Hi,

I am running Postgresql 9.1 and I can see the datfrozenxid is going high
and vacuum process is not bringing it down. And this has been happening
on template1 database.

2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:  oldest
xmin is far in the past
2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:  Close open
transactions soon to avoid wraparound problems.


The above seems to be the contributing factor.

Does:

select * from pg_stat_activity

show long running queries.


2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:  automatic
vacuum of table "template1.pg_catalog.pg_database": index scans: 0
pages: 0 removed, 1 remain
tuples: 0 removed, 9 remain
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:  oldest
xmin is far in the past
2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:  Close open
transactions soon to avoid wraparound problems.
2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:  automatic
vacuum of table "template1.pg_catalog.pg_largeobject": index scans: 0
pages: 0 removed, 0 remain
tuples: 0 removed, 0 remain
system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec


I vacuum database manually but nothing is working out.
Please help,

Thanks.



--
Adrian Klaver
adrian.kla...@aklaver.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] template1 database is facing high datfrozenxid

[Adrian Klaver]

On 02/12/2016 03:10 PM, AI Rumman wrote:

I checked it and I did not find any log running sql or any open
transaction. Not even in pg_prepared_xacts.
And it looks like pg_catalog database is making the alarm.

Any other idea please, where I need to look into.


select * from pg_locks

http://www.postgresql.org/docs/9.1/static/view-pg-locks.html



Thanks.


On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver
> wrote:

On 02/12/2016 02:56 PM, AI Rumman wrote:

Hi,

I am running Postgresql 9.1 and I can see the datfrozenxid is
going high
and vacuum process is not bringing it down. And this has been
happening
on template1 database.

 2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:
oldest
 xmin is far in the past
 2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:
Close open
 transactions soon to avoid wraparound problems.


The above seems to be the contributing factor.

Does:

select * from pg_stat_activity

show long running queries.


 2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:  automatic
 vacuum of table "template1.pg_catalog.pg_database": index
scans: 0
 pages: 0 removed, 1 remain
 tuples: 0 removed, 9 remain
 system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
 2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:
oldest
 xmin is far in the past
 2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:
Close open
 transactions soon to avoid wraparound problems.
 2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:  automatic
 vacuum of table "template1.pg_catalog.pg_largeobject":
index scans: 0
 pages: 0 removed, 0 remain
 tuples: 0 removed, 0 remain
 system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec


I vacuum database manually but nothing is working out.
Please help,

Thanks.



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





--
Adrian Klaver
adrian.kla...@aklaver.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] template1 database is facing high datfrozenxid

[AI Rumman]

I checked it and I did not find any log running sql or any open
transaction. Not even in pg_prepared_xacts.
And it looks like pg_catalog database is making the alarm.

Any other idea please, where I need to look into.

Thanks.


On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver 
wrote:

> On 02/12/2016 02:56 PM, AI Rumman wrote:
>
>> Hi,
>>
>> I am running Postgresql 9.1 and I can see the datfrozenxid is going high
>> and vacuum process is not bringing it down. And this has been happening
>> on template1 database.
>>
>> 2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:  oldest
>> xmin is far in the past
>> 2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:  Close open
>> transactions soon to avoid wraparound problems.
>>
>
> The above seems to be the contributing factor.
>
> Does:
>
> select * from pg_stat_activity
>
> show long running queries.
>
>
> 2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:  automatic
>> vacuum of table "template1.pg_catalog.pg_database": index scans: 0
>> pages: 0 removed, 1 remain
>> tuples: 0 removed, 9 remain
>> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
>> 2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:  oldest
>> xmin is far in the past
>> 2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:  Close open
>> transactions soon to avoid wraparound problems.
>> 2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:  automatic
>> vacuum of table "template1.pg_catalog.pg_largeobject": index scans: 0
>> pages: 0 removed, 0 remain
>> tuples: 0 removed, 0 remain
>> system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
>>
>>
>> I vacuum database manually but nothing is working out.
>> Please help,
>>
>> Thanks.
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] template1 database is facing high datfrozenxid

[AI Rumman]

Used this query in each of the database::

SELECT t.relname, l.database, l.locktype, l.pid , l.mode, l.granted,
p.current_query, p.query_start ,p.waiting
FROM pg_locks as l
INNER JOIN pg_stat_all_tables t
on l.relation = t.relid
INNER JOIN pg_stat_activity as p
on l.pid = p.procpid ;

No luck. At present, db is working, but t is going towards wraparound.

On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver 
wrote:

> On 02/12/2016 03:10 PM, AI Rumman wrote:
>
>> I checked it and I did not find any log running sql or any open
>> transaction. Not even in pg_prepared_xacts.
>> And it looks like pg_catalog database is making the alarm.
>>
>> Any other idea please, where I need to look into.
>>
>
> Should have added:
>
> select * from pg_database
>
>
>> Thanks.
>>
>>
>> On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver
>> > wrote:
>>
>> On 02/12/2016 02:56 PM, AI Rumman wrote:
>>
>> Hi,
>>
>> I am running Postgresql 9.1 and I can see the datfrozenxid is
>> going high
>> and vacuum process is not bringing it down. And this has been
>> happening
>> on template1 database.
>>
>>  2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:
>> oldest
>>  xmin is far in the past
>>  2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:
>> Close open
>>  transactions soon to avoid wraparound problems.
>>
>>
>> The above seems to be the contributing factor.
>>
>> Does:
>>
>> select * from pg_stat_activity
>>
>> show long running queries.
>>
>>
>>  2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:
>> automatic
>>  vacuum of table "template1.pg_catalog.pg_database": index
>> scans: 0
>>  pages: 0 removed, 1 remain
>>  tuples: 0 removed, 9 remain
>>  system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
>>  2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:
>> oldest
>>  xmin is far in the past
>>  2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:
>> Close open
>>  transactions soon to avoid wraparound problems.
>>  2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:
>> automatic
>>  vacuum of table "template1.pg_catalog.pg_largeobject":
>> index scans: 0
>>  pages: 0 removed, 0 remain
>>  tuples: 0 removed, 0 remain
>>  system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
>>
>>
>> I vacuum database manually but nothing is working out.
>> Please help,
>>
>> Thanks.
>>
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] template1 database is facing high datfrozenxid

[AI Rumman]

Yes. And we solved the problem.
We looked into the pg_subtrans and found that we had subrans pending from
January 25th. We investigated more and found that I large sql was executed
on Streaming standby around that date.
More digging we found the date of the below alert is also near:

WARNING:  oldest xmin is far in the past

We stopped standby and the problem solved. :)

Thanks.

On Fri, Feb 12, 2016 at 4:11 PM, Adrian Klaver 
wrote:

> On 02/12/2016 04:03 PM, AI Rumman wrote:
>
>> In pg_subtrans, I have files like:
>>
>
> Are you sure you are looking at the same database cluster in all the cases?
>
> What does:
>
> SELECT datname, age(datfrozenxid) FROM pg_database;
>
> give you?
>
>
>>
>> $ ls -lrt | more
>> total 1269436
>> -rw--- 1 postgres postgres 262144 Jan 25 18:49 D907
>> -rw--- 1 postgres postgres 262144 Jan 25 18:54 D908
>> -rw--- 1 postgres postgres 262144 Jan 25 18:58 D909
>> -rw--- 1 postgres postgres 262144 Jan 25 18:59 D90A
>> -rw--- 1 postgres postgres 262144 Jan 25 19:04 D90B
>> -rw--- 1 postgres postgres 262144 Jan 25 19:09 D90C
>> -rw--- 1 postgres postgres 262144 Jan 25 19:14 D90D
>> -rw--- 1 postgres postgres 262144 Jan 25 19:18 D90E
>> -rw--- 1 postgres postgres 262144 Jan 25 19:19 D90F
>> -rw--- 1 postgres postgres 262144 Jan 25 19:24 D910
>> -rw--- 1 postgres postgres 262144 Jan 25 19:29 D911
>> -rw--- 1 postgres postgres 262144 Jan 25 19:33 D912
>> -rw--- 1 postgres postgres 262144 Jan 25 19:34 D913
>> -rw--- 1 postgres postgres 262144 Jan 25 19:39 D914
>> -rw--- 1 postgres postgres 262144 Jan 25 19:44 D915
>> -rw--- 1 postgres postgres 262144 Jan 25 19:49 D916
>> -rw--- 1 postgres postgres 262144 Jan 25 19:53 D917
>> -rw--- 1 postgres postgres 262144 Jan 25 19:54 D918
>> -rw--- 1 postgres postgres 262144 Jan 25 19:59 D919
>> -rw--- 1 postgres postgres 262144 Jan 25 20:04 D91A
>> -rw--- 1 postgres postgres 262144 Jan 25 20:09 D91B
>> -rw--- 1 postgres postgres 262144 Jan 25 20:14 D91C
>> -rw--- 1 postgres postgres 262144 Jan 25 20:19 D91D
>> -rw--- 1 postgres postgres 262144 Jan 25 20:23 D91E
>> -rw--- 1 postgres postgres 262144 Jan 25 20:24 D91F
>> -rw--- 1 postgres postgres 262144 Jan 25 20:29 D920
>> -rw--- 1 postgres postgres 262144 Jan 25 20:34 D921
>> -rw--- 1 postgres postgres 262144 Jan 25 20:39 D922
>> -rw--- 1 postgres postgres 262144 Jan 25 20:44 D923
>> -rw--- 1 postgres postgres 262144 Jan 25 20:49 D924
>> -rw--- 1 postgres postgres 262144 Jan 25 20:54 D925
>> -rw--- 1 postgres postgres 262144 Jan 25 20:59 D926
>> -rw--- 1 postgres postgres 262144 Jan 25 21:04 D927
>> .
>>
>>
>> Does it mean that I have too many open transactions? If yes, it is not
>> showing in pg_stat_activity.
>>
>> On Fri, Feb 12, 2016 at 3:38 PM, AI Rumman > > wrote:
>>
>> Used this query in each of the database::
>>
>> SELECT t.relname, l.database, l.locktype, l.pid , l.mode, l.granted,
>> p.current_query, p.query_start ,p.waiting
>> FROM pg_locks as l
>> INNER JOIN pg_stat_all_tables t
>> on l.relation = t.relid
>> INNER JOIN pg_stat_activity as p
>> on l.pid = p.procpid ;
>>
>> No luck. At present, db is working, but t is going towards wraparound.
>>
>> On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver
>> > wrote:
>>
>> On 02/12/2016 03:10 PM, AI Rumman wrote:
>>
>> I checked it and I did not find any log running sql or any
>> open
>> transaction. Not even in pg_prepared_xacts.
>> And it looks like pg_catalog database is making the alarm.
>>
>> Any other idea please, where I need to look into.
>>
>>
>> Should have added:
>>
>> select * from pg_database
>>
>>
>> Thanks.
>>
>>
>> On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver
>> > 
>> >
>> >> wrote:
>>
>>  On 02/12/2016 02:56 PM, AI Rumman wrote:
>>
>>  Hi,
>>
>>  I am running Postgresql 9.1 and I can see the
>> datfrozenxid is
>>  going high
>>  and vacuum process is not bringing it down. And
>> this has been
>>  happening
>>  on template1 database.
>>
>>   2016-02-12 16:51:50.400 CST [19445][@] :
>> [13-1] WARNING:
>>  oldest
>>   xmin is far in the past
>>   

Re: [GENERAL] template1 database is facing high datfrozenxid

[AI Rumman]

In pg_subtrans, I have files like:

>
> $ ls -lrt | more
> total 1269436
> -rw--- 1 postgres postgres 262144 Jan 25 18:49 D907
> -rw--- 1 postgres postgres 262144 Jan 25 18:54 D908
> -rw--- 1 postgres postgres 262144 Jan 25 18:58 D909
> -rw--- 1 postgres postgres 262144 Jan 25 18:59 D90A
> -rw--- 1 postgres postgres 262144 Jan 25 19:04 D90B
> -rw--- 1 postgres postgres 262144 Jan 25 19:09 D90C
> -rw--- 1 postgres postgres 262144 Jan 25 19:14 D90D
> -rw--- 1 postgres postgres 262144 Jan 25 19:18 D90E
> -rw--- 1 postgres postgres 262144 Jan 25 19:19 D90F
> -rw--- 1 postgres postgres 262144 Jan 25 19:24 D910
> -rw--- 1 postgres postgres 262144 Jan 25 19:29 D911
> -rw--- 1 postgres postgres 262144 Jan 25 19:33 D912
> -rw--- 1 postgres postgres 262144 Jan 25 19:34 D913
> -rw--- 1 postgres postgres 262144 Jan 25 19:39 D914
> -rw--- 1 postgres postgres 262144 Jan 25 19:44 D915
> -rw--- 1 postgres postgres 262144 Jan 25 19:49 D916
> -rw--- 1 postgres postgres 262144 Jan 25 19:53 D917
> -rw--- 1 postgres postgres 262144 Jan 25 19:54 D918
> -rw--- 1 postgres postgres 262144 Jan 25 19:59 D919
> -rw--- 1 postgres postgres 262144 Jan 25 20:04 D91A
> -rw--- 1 postgres postgres 262144 Jan 25 20:09 D91B
> -rw--- 1 postgres postgres 262144 Jan 25 20:14 D91C
> -rw--- 1 postgres postgres 262144 Jan 25 20:19 D91D
> -rw--- 1 postgres postgres 262144 Jan 25 20:23 D91E
> -rw--- 1 postgres postgres 262144 Jan 25 20:24 D91F
> -rw--- 1 postgres postgres 262144 Jan 25 20:29 D920
> -rw--- 1 postgres postgres 262144 Jan 25 20:34 D921
> -rw--- 1 postgres postgres 262144 Jan 25 20:39 D922
> -rw--- 1 postgres postgres 262144 Jan 25 20:44 D923
> -rw--- 1 postgres postgres 262144 Jan 25 20:49 D924
> -rw--- 1 postgres postgres 262144 Jan 25 20:54 D925
> -rw--- 1 postgres postgres 262144 Jan 25 20:59 D926
> -rw--- 1 postgres postgres 262144 Jan 25 21:04 D927
> .


Does it mean that I have too many open transactions? If yes, it is not
showing in pg_stat_activity.

On Fri, Feb 12, 2016 at 3:38 PM, AI Rumman  wrote:

> Used this query in each of the database::
>
> SELECT t.relname, l.database, l.locktype, l.pid , l.mode, l.granted,
> p.current_query, p.query_start ,p.waiting
> FROM pg_locks as l
> INNER JOIN pg_stat_all_tables t
> on l.relation = t.relid
> INNER JOIN pg_stat_activity as p
> on l.pid = p.procpid ;
>
> No luck. At present, db is working, but t is going towards wraparound.
>
> On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver 
> wrote:
>
>> On 02/12/2016 03:10 PM, AI Rumman wrote:
>>
>>> I checked it and I did not find any log running sql or any open
>>> transaction. Not even in pg_prepared_xacts.
>>> And it looks like pg_catalog database is making the alarm.
>>>
>>> Any other idea please, where I need to look into.
>>>
>>
>> Should have added:
>>
>> select * from pg_database
>>
>>
>>> Thanks.
>>>
>>>
>>> On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver
>>> > wrote:
>>>
>>> On 02/12/2016 02:56 PM, AI Rumman wrote:
>>>
>>> Hi,
>>>
>>> I am running Postgresql 9.1 and I can see the datfrozenxid is
>>> going high
>>> and vacuum process is not bringing it down. And this has been
>>> happening
>>> on template1 database.
>>>
>>>  2016-02-12 16:51:50.400 CST [19445][@] : [13-1] WARNING:
>>> oldest
>>>  xmin is far in the past
>>>  2016-02-12 16:51:50.400 CST [19445][@] : [14-1] HINT:
>>> Close open
>>>  transactions soon to avoid wraparound problems.
>>>
>>>
>>> The above seems to be the contributing factor.
>>>
>>> Does:
>>>
>>> select * from pg_stat_activity
>>>
>>> show long running queries.
>>>
>>>
>>>  2016-02-12 16:51:50.400 CST [19445][@] : [15-1] LOG:
>>> automatic
>>>  vacuum of table "template1.pg_catalog.pg_database": index
>>> scans: 0
>>>  pages: 0 removed, 1 remain
>>>  tuples: 0 removed, 9 remain
>>>  system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
>>>  2016-02-12 16:51:50.411 CST [19435][@] : [82-1] WARNING:
>>> oldest
>>>  xmin is far in the past
>>>  2016-02-12 16:51:50.411 CST [19435][@] : [83-1] HINT:
>>> Close open
>>>  transactions soon to avoid wraparound problems.
>>>  2016-02-12 16:51:50.411 CST [19435][@] : [84-1] LOG:
>>> automatic
>>>  vacuum of table "template1.pg_catalog.pg_largeobject":
>>> index scans: 0
>>>  pages: 0 removed, 0 remain
>>>  tuples: 0 removed, 0 remain
>>>  system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec
>>>
>>>
>>> I vacuum database manually but nothing is working out.
>>> Please help,
>>>
>>> 

Re: [GENERAL] Trying to move away from Firebird

[ioan ghip]

Thanks for the links Adrian. I was able to convert a bunch of triggers
after reading the documentation.

Yes, it looks like POST_EVENT is the smaller brother of NOTIFY.

http://www.postgresql.org/docs/9.0/static/sql-notify.html

Re: [GENERAL] template1 database is facing high datfrozenxid

[Adrian Klaver]

On 02/12/2016 04:03 PM, AI Rumman wrote:

In pg_subtrans, I have files like:


Are you sure you are looking at the same database cluster in all the cases?

What does:

SELECT datname, age(datfrozenxid) FROM pg_database;

give you?




$ ls -lrt | more
total 1269436
-rw--- 1 postgres postgres 262144 Jan 25 18:49 D907
-rw--- 1 postgres postgres 262144 Jan 25 18:54 D908
-rw--- 1 postgres postgres 262144 Jan 25 18:58 D909
-rw--- 1 postgres postgres 262144 Jan 25 18:59 D90A
-rw--- 1 postgres postgres 262144 Jan 25 19:04 D90B
-rw--- 1 postgres postgres 262144 Jan 25 19:09 D90C
-rw--- 1 postgres postgres 262144 Jan 25 19:14 D90D
-rw--- 1 postgres postgres 262144 Jan 25 19:18 D90E
-rw--- 1 postgres postgres 262144 Jan 25 19:19 D90F
-rw--- 1 postgres postgres 262144 Jan 25 19:24 D910
-rw--- 1 postgres postgres 262144 Jan 25 19:29 D911
-rw--- 1 postgres postgres 262144 Jan 25 19:33 D912
-rw--- 1 postgres postgres 262144 Jan 25 19:34 D913
-rw--- 1 postgres postgres 262144 Jan 25 19:39 D914
-rw--- 1 postgres postgres 262144 Jan 25 19:44 D915
-rw--- 1 postgres postgres 262144 Jan 25 19:49 D916
-rw--- 1 postgres postgres 262144 Jan 25 19:53 D917
-rw--- 1 postgres postgres 262144 Jan 25 19:54 D918
-rw--- 1 postgres postgres 262144 Jan 25 19:59 D919
-rw--- 1 postgres postgres 262144 Jan 25 20:04 D91A
-rw--- 1 postgres postgres 262144 Jan 25 20:09 D91B
-rw--- 1 postgres postgres 262144 Jan 25 20:14 D91C
-rw--- 1 postgres postgres 262144 Jan 25 20:19 D91D
-rw--- 1 postgres postgres 262144 Jan 25 20:23 D91E
-rw--- 1 postgres postgres 262144 Jan 25 20:24 D91F
-rw--- 1 postgres postgres 262144 Jan 25 20:29 D920
-rw--- 1 postgres postgres 262144 Jan 25 20:34 D921
-rw--- 1 postgres postgres 262144 Jan 25 20:39 D922
-rw--- 1 postgres postgres 262144 Jan 25 20:44 D923
-rw--- 1 postgres postgres 262144 Jan 25 20:49 D924
-rw--- 1 postgres postgres 262144 Jan 25 20:54 D925
-rw--- 1 postgres postgres 262144 Jan 25 20:59 D926
-rw--- 1 postgres postgres 262144 Jan 25 21:04 D927
.


Does it mean that I have too many open transactions? If yes, it is not
showing in pg_stat_activity.

On Fri, Feb 12, 2016 at 3:38 PM, AI Rumman > wrote:

Used this query in each of the database::

SELECT t.relname, l.database, l.locktype, l.pid , l.mode, l.granted,
p.current_query, p.query_start ,p.waiting
FROM pg_locks as l
INNER JOIN pg_stat_all_tables t
on l.relation = t.relid
INNER JOIN pg_stat_activity as p
on l.pid = p.procpid ;

No luck. At present, db is working, but t is going towards wraparound.

On Fri, Feb 12, 2016 at 3:28 PM, Adrian Klaver
> wrote:

On 02/12/2016 03:10 PM, AI Rumman wrote:

I checked it and I did not find any log running sql or any open
transaction. Not even in pg_prepared_xacts.
And it looks like pg_catalog database is making the alarm.

Any other idea please, where I need to look into.


Should have added:

select * from pg_database


Thanks.


On Fri, Feb 12, 2016 at 3:05 PM, Adrian Klaver

>> wrote:

 On 02/12/2016 02:56 PM, AI Rumman wrote:

 Hi,

 I am running Postgresql 9.1 and I can see the
datfrozenxid is
 going high
 and vacuum process is not bringing it down. And
this has been
 happening
 on template1 database.

  2016-02-12 16:51:50.400 CST [19445][@] :
[13-1] WARNING:
 oldest
  xmin is far in the past
  2016-02-12 16:51:50.400 CST [19445][@] :
[14-1] HINT:
 Close open
  transactions soon to avoid wraparound problems.


 The above seems to be the contributing factor.

 Does:

 select * from pg_stat_activity

 show long running queries.


  2016-02-12 16:51:50.400 CST [19445][@] :
[15-1] LOG:  automatic
  vacuum of table
"template1.pg_catalog.pg_database": index
 scans: 0
  pages: 0 removed, 1 remain
  tuples: 0 removed, 9 remain
  system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec