Re: [GENERAL] Trying to move away from Firebird

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

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

Re: [GENERAL] template1 database is facing high datfrozenxid

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 postgre

Re: [GENERAL] template1 database is facing high datfrozenxid

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

Re: [GENERAL] template1 database is facing high datfrozenxid

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 p

Re: [GENERAL] template1 database is facing high datfrozenxid

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

Re: [GENERAL] template1 database is facing high datfrozenxid

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

Re: [GENERAL] template1 database is facing high datfrozenxid

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

Re: [GENERAL] template1 database is facing high datfrozenxid

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 f

Re: [GENERAL] Windows performance

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 ste

[GENERAL] template1 database is facing high datfrozenxid

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

Re: [GENERAL] Trying to move away from Firebird

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 G

Re: [GENERAL] Windows performance

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

Re: [GENERAL] Windows performance

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 XEO

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

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 t

[GENERAL] Trying to move away from Firebird

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_) an

Re: [GENERAL] Windows performance

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

Re: [GENERAL] Windows performance

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 gu

Re: [GENERAL] PosgreSQL Security Architecture

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

Re: [GENERAL] Unrecognized configuration parameter in bdr 0.9.3

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

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

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

Re: [GENERAL] Test CMake build

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

Re: [GENERAL] Test CMake build

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 cour

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

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 = ''000

Re: [GENERAL] Test CMake build

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 depend

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

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

Re: [GENERAL] Code of Conduct plan

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 invitat

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

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

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

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

Re: [GENERAL] Windows performance

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

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

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 thing

Re: [GENERAL] Windows performance

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

Re: [GENERAL] Test CMake build

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

Re: [GENERAL] Windows performance

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 G

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

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

[GENERAL] Windows performance

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

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

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

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

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 actio

Re: [GENERAL] Test CMake build

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 mo

Re: [GENERAL] Test CMake build

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 t

Re: [GENERAL] Test CMake build

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

Re: [GENERAL] database corruption

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

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

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 S

Re: [GENERAL] PosgreSQL Security Architecture

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 relat

Re: [GENERAL] database corruption

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 ther

Re: [GENERAL] Test CMake build

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 li

Re: [GENERAL] Test CMake build

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 9

[GENERAL] PostgreSQL 9.5 and process REST calls enquiry

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

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

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

[GENERAL] database corruption

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 st

Re: [GENERAL] Test CMake build

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 d

Re: [GENERAL] memory problem with refresh materialized view

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 >

Re: [GENERAL] PosgreSQL Security Architecture

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 insi