[GENERAL] corrupted statistics file "pg_stat_tmp/pgstat.stat"

[Carl von Clausewitz]

Hi All,

 

I’ve just made a clean install for PostgreSQL 9.1 (postgresql-server-9.1.4, 
postgresql-contrib-9.1.4) on a FreeBSD 9 (FreeBSD 9.0-RELEASE-p3 FreeBSD 
9.0-RELEASE-p3 #0: Tue Jun 12 02:52:29 UTC 2012 
r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  amd64). 

 

I’ve restored from TAR backup our databases, and everything looked fine. 
Without changing any setting in postgresql.conf (or in kernel settings) – only 
“track_counts=on”, after 2-3 days, I’m receiving huge number (~5-10 PCS in 
every second) of error messages like that in /var/log/postgresql.log:

 

*** Aug 15 06:27:26 eurodb postgres[77652]: [43-1] WARNING:  corrupted 
statistics file "pg_stat_tmp/pgstat.stat"

 

The logs are going to the syslog, and there are no more Warning, Error, or 
other messages. If I do a full Vacuum on the database, and I restart the 
system, everything works fine, no more error messages, until 2-3 days – and 
everything starts from the beginning. 

 

Two weeks ago, we just bought a new server, I’ve installed the same version of 
FreeBSD and PostgreSQL, and there is the same problem on that machine too – 
without any usage, or load, this is a test server for us now. 

 

The disks (SATA3 HDD), and the controllers has been tested, they are OK. All 
the directories’ permissons are the defaults, pg_stat_temp is owned by 
pgsql:pgsql – so I think this is not a permission issue. 

 

I’m trying to tune my config with pgtune, and I’ve made some changes on sysctl 
for the following: 

 

kern.ipc.semmni="512"

kern.ipc.semmns="1024"

kern.ipc.semume="64"

kern.ipc.semmnu="512"

 

I will restart the machines today, and will see, what’s going on. Did anybody 
has seen this problem before? On our early version 9.0.8 PostgreSQL server, 
there wasn’t any kind of problem, but it was a FreeBSD 8.2. How can I 
investigate, what could be the problem? Kernel settings? Or any other idea? 

 

Thank you in advance. 

 

Best regards,

Csaba

[GENERAL] More probs with BLOBs

[Neanderthelle Jones]

We are getting a strange thing happening if the lo_export(attr, path)
destination is a fifo.

First, in the normal case, there is output to the file but also
feedback to stdout (or somewhere, appearing on the VT screen) of the
number 1.

---
 1
(1 row)

Now, if the destination file is a fifo and a psql -c command is given
from the shell to lo_export(attr, '/path/to/fifo') we don't get that
feedback, but then a cat of the fifo to a normal file (cat fifo >
file) writes the BLOB content to the destination file but also sends

---
 1
(1 row)

(or just "1" with psql -t) to the console, apparently (not stderr).

So it seems to us that the lo_export output can't be piped (via a
named pipe) to another process that reads from the pipe and expects
the valid BLOB contents.

Beyond my understanding.  What is happening here, and can the echoing
to the fifo be avoided?  Ideas, anyone?

(PostgreSQL 9.1.2, Linux 2.6.29.)

Elle


-- 
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] More probs with BLOBs

[Neanderthelle Jones]

On Wed, 15 Aug 2012, Neanderthelle Jones wrote:

> So it seems to us that the lo_export output can't be piped (via a
> named pipe) to another process that reads from the pipe and expects
> the valid BLOB contents.

I should have said that the BLOB is a PDF and the file that results
from cat fifo >my.pdf is a valid PDF file (containing only an image
object) that xpdf displays and prints.

But that is after the adornment has gone to the console.

Elle





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

[GENERAL] PG Installer - Licensing Issues

[Andrew Hastie]

As I understand it, I am allowed to redistribute Postgres so long as I 
include the copyright notice plus paragraphs as detailed on 
http://www.postgresql.org/about/licence/.


What I want to confirm is that the one-click installer (which I 
understand was supplied by EnterpriseDB) can also be redistributed such 
that I can bundle Postgres with my "product", and also use the 
non-interactive installer 
(http://www.enterprisedb.com/resources-community/pginst-guide#interactive) 
such that I can make the installation as easy as possible for my users. 
I've scanned the EnterpriseDB site, but I see nothing that confirms or 
denies my thoughts.


Anyone care to comment?

Thanks
Andrew


--
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] Visualize database schema

[Wolfgang Keller]

> >> Can anyone advice about a tool to visualize a database schema?
> >
> > SQLalchemy, a Python module, can produce dot (Graphviz) output
> > which you can load into your favourite diagramming application such
> > as e.g. Omnigraffle, yEd or Dia:
> >
> > http://www.sqlalchemy.org/trac/wiki/UsageRecipes/SchemaDisplay
> 
> You just made my day -- great stuff.  ERD generation should be
> automatic -- whenever I lay them out I feel like I'm fingerpainting.



Unlike postgresql_autodoc, which cannot be "embedded" with Pgadmin,
since it is implemented in Perl, which has an embedding-hostile
License as someone once told me, this tiny script could easily be
embedded with Pgadmin, since Python's license is deliberately
embedding-friendly.

And with dot2tex, it would be even possible to generate a complete,
printable, well-typographed PDF documentation of any PostgreSQL database
from within Pgadmin, through LaTeX. 



Sincerely,

Wolfgang


-- 
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] corrupted statistics file "pg_stat_tmp/pgstat.stat"

[Amitabh Kant]

I run the same config (FreeBSD 9 with PG 9.1.x) on couple of servers, and
they seem to be working fine without any error messages. The only other
setting I have in my sysctl.conf is kern.maxfiles .

Amitabh

On Wed, Aug 15, 2012 at 1:27 PM, Carl von Clausewitz  wrote:

> Hi All,
>
> ** **
>
> I’ve just made a clean install for PostgreSQL 9.1
> (postgresql-server-9.1.4, postgresql-contrib-9.1.4) on a FreeBSD 9 (FreeBSD
> 9.0-RELEASE-p3 FreeBSD 9.0-RELEASE-p3 #0: Tue Jun 12 02:52:29 UTC 2012
> r...@amd64-builder.daemonology.net:/usr/obj/usr/src/sys/GENERIC  amd64). *
> ***
>
> ** **
>
> I’ve restored from TAR backup our databases, and everything looked fine.
> Without changing any setting in postgresql.conf (or in kernel settings) –
> only “track_counts=on”, after 2-3 days, I’m receiving huge number (~5-10
> PCS in every second) of error messages like that in /var/log/postgresql.log:
> 
>
> ** **
>
> *** Aug 15 06:27:26 eurodb postgres[77652]: [43-1] WARNING:  corrupted
> statistics file "pg_stat_tmp/pgstat.stat"
>
> ** **
>
> The logs are going to the syslog, and there are no more Warning, Error, or
> other messages. If I do a full Vacuum on the database, and I restart the
> system, everything works fine, no more error messages, until 2-3 days – and
> everything starts from the beginning. 
>
> ** **
>
> Two weeks ago, we just bought a new server, I’ve installed the same
> version of FreeBSD and PostgreSQL, and there is the same problem on that
> machine too – without any usage, or load, this is a test server for us now.
> 
>
> ** **
>
> The disks (SATA3 HDD), and the controllers has been tested, they are OK.
> All the directories’ permissons are the defaults, pg_stat_temp is owned by
> pgsql:pgsql – so I think this is not a permission issue. 
>
> ** **
>
> I’m trying to tune my config with pgtune, and I’ve made some changes on
> sysctl for the following: 
>
> ** **
>
> kern.ipc.semmni="512"
>
> kern.ipc.semmns="1024"
>
> kern.ipc.semume="64"
>
> kern.ipc.semmnu="512"
>
> ** **
>
> I will restart the machines today, and will see, what’s going on. Did
> anybody has seen this problem before? On our early version 9.0.8 PostgreSQL
> server, there wasn’t any kind of problem, but it was a FreeBSD 8.2. How can
> I investigate, what could be the problem? Kernel settings? Or any other
> idea? 
>
> ** **
>
> Thank you in advance. 
>
> ** **
>
> Best regards,
>
> Csaba
>

Re: [GENERAL] corrupted statistics file "pg_stat_tmp/pgstat.stat"

[Alban Hertroys]

On 15 August 2012 09:57, Carl von Clausewitz  wrote:
> I’ve restored from TAR backup our databases, and everything looked fine.

What exactly is in that TAR backup? Is that a tar/gzipped PG dump or a
file-system snapshot?
If the latter:
- did you halt the database while creating the snapshot or at least
force a checkpoint?
- is the version of PG that you're restoring to exactly the same (same
CPU type, same major/minor version of PG) as the one that the backup
is from?

I suspect that you restored a database with corrupt statistics and
that those only get found out once the corrupted statistics are
needed.

-- 
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.


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

Re: [GENERAL] corrupted statistics file "pg_stat_tmp/pgstat.stat"

[Tom Lane]

"Carl von Clausewitz"  writes:
> I’ve restored from TAR backup our databases, and everything looked fine. 
> Without changing any setting in postgresql.conf (or in kernel settings) – 
> only “track_counts=on”, after 2-3 days, I’m receiving huge number 
> (~5-10 PCS in every second) of error messages like that in 
> /var/log/postgresql.log:
> *** Aug 15 06:27:26 eurodb postgres[77652]: [43-1] WARNING:  corrupted 
> statistics file "pg_stat_tmp/pgstat.stat"

Huh.  The stats collector process ought to rewrite that file fairly
often, so this suggests it's consistently failing to rewrite it.

You might take a look at what the file looks like after a day or so
of normal operation (eg, how big is it, how often does it get updated)
and then compare to what it looks like after the errors start.

Also, try strace'ing the stats collector process for a little while
(long enough to capture a stats file rewrite sequence) during normal
operation, and then again after the errors start.

I don't want to speculate too much in advance of the data, but I'm
wondering about a ulimit setting that limits how much data the stats
collector can write during its lifetime (ulimit -f or local equivalent).
That would eventually cause problems for any postgres process, but if
you did accidentally have one in place when starting the postmaster,
maybe the stats collector would be first to show symptoms.

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] PG Installer - Licensing Issues

[Bruce Momjian]

On Wed, Aug 15, 2012 at 11:45:21AM +0100, Andrew Hastie wrote:
> As I understand it, I am allowed to redistribute Postgres so long as
> I include the copyright notice plus paragraphs as detailed on
> http://www.postgresql.org/about/licence/.
> 
> What I want to confirm is that the one-click installer (which I
> understand was supplied by EnterpriseDB) can also be redistributed
> such that I can bundle Postgres with my "product", and also use the
> non-interactive installer 
> (http://www.enterprisedb.com/resources-community/pginst-guide#interactive)
> such that I can make the installation as easy as possible for my
> users. I've scanned the EnterpriseDB site, but I see nothing that
> confirms or denies my thoughts.

I dug into this and found this at the top of the installation notes file
that is created once you install the product, at
/opt/PostgreSQL/9.1/doc/installation-notes.html:

   Legal Bits

   First the boring legal stuff. The software bundled together in this
   package is released under a number of different Open Source licences.
   By using any component of this installation package, you agree to
   abide by the terms and conditions of it's licence.

   The PostgreSQL Server and pgAdmin are released under the PostgreSQL
   License.

   This product includes software developed by the OpenSSL Project for
   use in the OpenSSL Toolkit. (http://www.openssl.org/)

I also found this:


http://www.enterprisedb.com/products-services-training/products-overview/licensing
http://www.enterprisedb.com/ba/foss-licenses

Now, this doesn't directly mention the installer, which is gone at the
time this is installed.  I know it has the same license as Postgres, but
where is that documented.  Dave, any ideas?

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

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


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

Re: [GENERAL] PG Installer - Licensing Issues

[Dave Page]

On Wed, Aug 15, 2012 at 3:17 PM, Bruce Momjian  wrote:
> On Wed, Aug 15, 2012 at 11:45:21AM +0100, Andrew Hastie wrote:
>> As I understand it, I am allowed to redistribute Postgres so long as
>> I include the copyright notice plus paragraphs as detailed on
>> http://www.postgresql.org/about/licence/.
>>
>> What I want to confirm is that the one-click installer (which I
>> understand was supplied by EnterpriseDB) can also be redistributed
>> such that I can bundle Postgres with my "product", and also use the
>> non-interactive installer 
>> (http://www.enterprisedb.com/resources-community/pginst-guide#interactive)
>> such that I can make the installation as easy as possible for my
>> users. I've scanned the EnterpriseDB site, but I see nothing that
>> confirms or denies my thoughts.
>
> I dug into this and found this at the top of the installation notes file
> that is created once you install the product, at
> /opt/PostgreSQL/9.1/doc/installation-notes.html:
>
>Legal Bits
>
>First the boring legal stuff. The software bundled together in this
>package is released under a number of different Open Source licences.
>By using any component of this installation package, you agree to
>abide by the terms and conditions of it's licence.
>
>The PostgreSQL Server and pgAdmin are released under the PostgreSQL
>License.
>
>This product includes software developed by the OpenSSL Project for
>use in the OpenSSL Toolkit. (http://www.openssl.org/)
>
> I also found this:
>
> 
> http://www.enterprisedb.com/products-services-training/products-overview/licensing
> http://www.enterprisedb.com/ba/foss-licenses
>
> Now, this doesn't directly mention the installer, which is gone at the
> time this is installed.  I know it has the same license as Postgres, but
> where is that documented.  Dave, any ideas?

The installer code uses the PostgreSQL licence. I'll clarify the
installation notes doc.


-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL 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] PG Installer - Licensing Issues

[Bruce Momjian]

On Wed, Aug 15, 2012 at 03:20:52PM +0100, Dave Page wrote:
> On Wed, Aug 15, 2012 at 3:17 PM, Bruce Momjian  wrote:
> > On Wed, Aug 15, 2012 at 11:45:21AM +0100, Andrew Hastie wrote:
> >> As I understand it, I am allowed to redistribute Postgres so long as
> >> I include the copyright notice plus paragraphs as detailed on
> >> http://www.postgresql.org/about/licence/.
> >>
> >> What I want to confirm is that the one-click installer (which I
> >> understand was supplied by EnterpriseDB) can also be redistributed
> >> such that I can bundle Postgres with my "product", and also use the
> >> non-interactive installer 
> >> (http://www.enterprisedb.com/resources-community/pginst-guide#interactive)
> >> such that I can make the installation as easy as possible for my
> >> users. I've scanned the EnterpriseDB site, but I see nothing that
> >> confirms or denies my thoughts.
> >
> > I dug into this and found this at the top of the installation notes file
> > that is created once you install the product, at
> > /opt/PostgreSQL/9.1/doc/installation-notes.html:
> >
> >Legal Bits
> >
> >First the boring legal stuff. The software bundled together in this
> >package is released under a number of different Open Source licences.
> >By using any component of this installation package, you agree to
> >abide by the terms and conditions of it's licence.
> >
> >The PostgreSQL Server and pgAdmin are released under the PostgreSQL
> >License.
> >
> >This product includes software developed by the OpenSSL Project for
> >use in the OpenSSL Toolkit. (http://www.openssl.org/)
> >
> > I also found this:
> >
> > 
> > http://www.enterprisedb.com/products-services-training/products-overview/licensing
> > http://www.enterprisedb.com/ba/foss-licenses
> >
> > Now, this doesn't directly mention the installer, which is gone at the
> > time this is installed.  I know it has the same license as Postgres, but
> > where is that documented.  Dave, any ideas?
> 
> The installer code uses the PostgreSQL licence. I'll clarify the
> installation notes doc.

Thanks.

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

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


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

[GENERAL] Check PostgreSQL status using MS-DOS bat file?

[Loughrey, Hugh]

Hi All,

I want to write a MS-DOS command to check that the PostgreSQL database is up 
and running and able to accept data being pushed to it. From a bit of reading 
I've identified the pg_ctl status command, but can this be incorporated into a 
*.bat file and can the resulting status be recorded in a *.txt file? If so does 
anyone have a command which would enable this?

Regards


Hugh Loughrey
GIS Application Analyst
Hoople Ltd | Thorn Office Centre | Hereford HR2 6JT
Tel: 01432 383566 | Email: 
hugh.lough...@hoopleltd.co.uk
General email: enquir...@hoopleltd.co.uk
Website: www.hoopleltd.co.uk


"Any opinion expressed in this e-mail or any attached files are those of the 
individual and not necessarily those of Hoople Ltd. You should be aware that 
Hoople Ltd. monitors its email service. This e-mail and any attached files are 
confidential and intended solely for the use of the addressee. This 
communication may contain material protected by law from being passed on. If 
you are not the intended recipient and have received this e-mail in error, you 
are advised that any use, dissemination, forwarding, printing or copying of 
this e-mail is strictly prohibited. If you have received this e-mail in error 
please contact the sender immediately and destroy all copies of it.

Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?

[Bruce Momjian]

On Wed, Aug 15, 2012 at 09:52:17AM +, Loughrey, Hugh wrote:
> Hi All,
>  
> I want to write a MS-DOS command to check that the PostgreSQL database is up
> and running and able to accept data being pushed to it. From a bit of reading
> I’ve identified the pg_ctl status command, but can this be incorporated into a
> *.bat file and can the resulting status be recorded in a *.txt file? If so 
> does
> anyone have a command which would enable this?

As I remember it is pretty tricky to call pg_ctl from a Windows batch
file.  I know the Windows installers do it somehow --- you might want to
downlaod it and see if you can find the shell script they use.  Dave
Page might know more --- CC'ing him.

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

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


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

Re: [GENERAL] Check PostgreSQL status using MS-DOS bat file?

[Dave Page]

On Wed, Aug 15, 2012 at 4:04 PM, Bruce Momjian  wrote:
> On Wed, Aug 15, 2012 at 09:52:17AM +, Loughrey, Hugh wrote:
>> Hi All,
>>
>> I want to write a MS-DOS command to check that the PostgreSQL database is up
>> and running and able to accept data being pushed to it. From a bit of reading
>> I’ve identified the pg_ctl status command, but can this be incorporated into 
>> a
>> *.bat file and can the resulting status be recorded in a *.txt file? If so 
>> does
>> anyone have a command which would enable this?
>
> As I remember it is pretty tricky to call pg_ctl from a Windows batch
> file.  I know the Windows installers do it somehow --- you might want to
> downlaod it and see if you can find the shell script they use.  Dave
> Page might know more --- CC'ing him.

You shouldn't try to start/stop the server with pg_ctl if it's
configured to run as a service (use "net start xxx", "net stop xxx"
for that), but you can check the status:

C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program
Files\PostgreSQL\9.2\data" status
pg_ctl: server is running (PID: 1040)
C:/Program Files/PostgreSQL/9.2/bin/postgres.exe "-D" "C:/Program
Files/PostgreSQL/9.2/data"


C:\>net stop postgresql-x64-9.2
The postgresql-x64-9.2 service is stopping.
The postgresql-x64-9.2 service was stopped successfully.


C:\>"C:\Program Files\PostgreSQL\9.2\bin\pg_ctl.exe" -D "C:\Program
Files\PostgreSQL\9.2\data" status
pg_ctl: no server running

-- 
Dave Page
Blog: http://pgsnake.blogspot.com
Twitter: @pgsnake

EnterpriseDB UK: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


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

[GENERAL] ProblemWithCharsOSX

[Angel Zúñiga]

Hi there,

This is my first post in the list.
I'm a PostgreSQL user in the linux world but at the office there is a Mac
computer and I have a problem with postgres in it.

The problem is when I use characters with tildes in spanish like in the
follow word "países".
The next is ilustrates my problem:

test=# SHOW SERVER_ENCODING;
 server_encoding
-
 UTF8
(1 row)

test=# SELECT pg_client_encoding();
 pg_client_encoding

 UTF8
(1 row)

test=# CREATE TABLE países (nombre VARCHAR(10));
CREATE TABLE
test=# \d
List of relations
 Schema | Name  | Type  | Owner
+---+---+-
---
 public | pases | table | dragon
(1 row)

The table name is created without the "í" character but if I enclose the
string between quotes postgres works fine.
test=# CREATE TABLE "países" (nombre VARCHAR(10));
CREATE TABLE
test=# \d
List of relations
 Schema |  Name  | Type  | Owner
++---+
 public | países | table | dragon
 public | pases  | table | dragon
(2 rows)
test=# \l
 List of databases
  Name   |  Owner   | Encoding |  Collation  |Ctype|
Access privileges
-+--+--+-+-+---
 template0   | postgres | UTF8 | es_MX.UTF-8 | es_MX.UTF-8 |
=c/postgres  +
 |  |  | | |
postgres=CTc/postgres
 template1   | postgres | UTF8 | es_MX.UTF-8 | es_MX.UTF-8 |
postgres=CTc/postgres+
 |  |  | | |
=c/postgres
 test| dragon   | UTF8 | es_MX.UTF-8 | es_MX.UTF-8 |


In the linux box all works fine, so I think the problem is something
particular to Mac,
The Mac OS X version is 10.7.4.
Darwin Users-iMac.local 11.4.0 Darwin Kernel Version 11.4.0: Mon Apr  9
19:32:15 PDT 2012; root:xnu-1699.26.8~1/RELEASE_X86_64 x86_64

and the postgres version is 9.0.8.

Can Anybody help me with my problem?

Re: [GENERAL] Postgres 9.2 beta2 one-click installer on windows

[Marc Watson]

Original message:

 

De : pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] De la part de Marc Watson
Envoyé : June-11-12 8:36 AM
À : Craig Ringer
Cc : pgsql-general@postgresql.org; dp...@pgadmin.org; 
sachin.srivast...@enterprisedb.com
Objet : Re: [GENERAL] Postgres 9.2 beta2 one-click installer on windows

 

De : Craig Ringer [mailto:ring...@ringerc.id.au] 
  
Envoyé : June-09-12 3:50 AM

(CCing Dave Page & Sachin Srivastava who work on the installer):

Short version is that the Pg installer appears to need to:

- Include the latest release of the redist because there's a security fix in it
- use /passive not /q when invoking it
- treat return 5100 as success
- swear at Microsoft for not documenting this

Continued below.

On 06/08/2012 11:55 PM, Marc Watson wrote: 

 

Executing C:\Users\Mark 
Watson\AppData\Local\Temp\postgresql_installer\vcredist_x64.exe /q

Script exit code: 5100

 


Is there any chance you can get the version of the currently installed MSVC++ 
redistributible(s) on your computer? They're listed in programs and features in 
the control panel.

I originally had version 10.0.40219 installed in both 32 and 64 bit,  which 
gave me the error. I uninstalled these versions just to get around the 9.2 
beta2 install, which installed 10.0.30319.01.

 

I suspect you have a newer version of the same general version (eg 2010 SP1) of 
the VC++ redist installed, and rather than silently ignore the newer version 
it's failing.

That was probably the case, since it's a new computer.


This appears to be a fairly common issue. It's even encountered by the Visual 
C++ IDE installer its self. The short-term workaround appears to be for the Pg 
installer to ignore error code 5100. Longer term, I'd like a way to tell the 
VC++ redist "don't be stupid, just silently take no action if a newer version 
is present."

We should also probably be using "/passive /norestart" not "/q /norestart" when 
invoking the installer, since that lets the redist display useful error 
messages to the user but doesn't demand interaction from them.

BTW, exit code 3100 ("succeeded but a reboot is required) should also be 
handled if it isn't already.

People are using some scary workarounds, like:

http://notepad.patheticcockroach.com/1666/installing-visual-c-2010-and-windows-sdk-for-windows-7-offline-installer-and-installation-troubleshooting/
(Warning: comments are content-free zone full of me-too, plus people not 
understanding what the redist does, confusing the installation of the runtime 
for the us of VC++ with the "install redistributables" option that copies them 
for _you_ to use in _your_ software, etc). 

Also:
http://ta.speot.is/2012/04/09/visual-studio-2010-sp1-windows-sdk-7-1-install-order/
http://social.microsoft.com/Forums/en/crm/thread/db84d4c3-c58e-4ff9-997f-f4e0116bf043

http://blogs.msdn.com/b/astebner/archive/2010/10/20/10078468.aspx

BTW, IBM seems to install the redist as:

vcredist_x86.exe /q /c:"msiexec /i vcredist.msi /qn /l*v 
  %temp%\vcredist_x86.log"

... not sure why yet.

--
Craig Ringer

Mark Watson

 

Just for a follow-up, the 9.2 beta-3 install is behaving correctly.

-Mark

[GENERAL] success with postgresql on beaglebone

[Tomas Hlavaty]

Hi all,

I managed to compile postgresql on BeagleBone.

The only problem I encountered was insufficient memory for compiling
grammars by bison (BeagleBone has 256MB RAM).

As a workaround, I compiled the same sources on a bigger machine and
copied the following files to BeagleBone:

postgresql/src/backend/parser/gram.h
postgresql/src/backend/parser/gram.c
postgresql/src/backend/parser/scan.c
postgresql/src/backend/bootstrap/bootparse.c
postgresql/src/backend/bootstrap/bootscanner.c
postgresql/src/backend/replication/repl_gram.c
postgresql/src/backend/replication/repl_scanner.c
postgresql/src/backend/utils/misc/guc-file.c
postgresql/src/interfaces/ecpg/preproc/preproc.c
postgresql/src/interfaces/ecpg/preproc/pgc.c
postgresql/src/interfaces/ecpg/preproc/preproc.h
postgresql/src/bin/psql/psqlscan.c
postgresql/src/pl/plpgsql/src/pl_gram.c
postgresql/src/pl/plpgsql/src/pl_gram.h

All tests passed.

I wonder, does anybody know how to compile the sources without copying
the bison generated files?

Thank you,

Tomas


-- 
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] corrupted statistics file "pg_stat_tmp/pgstat.stat"

[Carl von Clausewitz]

Hi,

I've made the backups with pg_dump, compressed, and there wasn't any kind of 
error messages. I've made the restoration with pgAdmin (but it also uses 
pg_dump for restore). 

How can I check if the statistics are corrupt? 

regards,
Csaba

-Original Message-
From: Alban Hertroys [mailto:haram...@gmail.com] 
Sent: Wednesday, August 15, 2012 3:33 PM
To: Carl von Clausewitz
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] corrupted statistics file "pg_stat_tmp/pgstat.stat"

On 15 August 2012 09:57, Carl von Clausewitz  wrote:
> I’ve restored from TAR backup our databases, and everything looked fine.

What exactly is in that TAR backup? Is that a tar/gzipped PG dump or a 
file-system snapshot?
If the latter:
- did you halt the database while creating the snapshot or at least force a 
checkpoint?
- is the version of PG that you're restoring to exactly the same (same CPU 
type, same major/minor version of PG) as the one that the backup is from?

I suspect that you restored a database with corrupt statistics and that those 
only get found out once the corrupted statistics are needed.

--
If you can't see the forest for the trees, Cut the trees and you'll see there 
is no forest.



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

Re: [GENERAL] corrupted statistics file "pg_stat_tmp/pgstat.stat"

[Carl von Clausewitz]

Hi,

I've made the kernel changes that I wrote in my original e-mail, and I've 
created some additional logging (both csvlog and syslog), to gather more 
informations. 

/boot/loader.conf:

kern.ipc.semmni="512"
kern.ipc.semmns="1024"
kern.ipc.semume="64"
kern.ipc.semmnu="512"

/etc/sysctl.cong:

kern.ipc.shmall=262144
kern.ipc.shmmax=1073742336
kern.ipc.semmap=256

pgTune made this config changes for me in /usr/local/pgsql/data/postgresql.conf 
(the server has 4GB RAM)
default_statistics_target   = 50# pgtune wizard 
2012-08-15
maintenance_work_mem= 240MB # pgtune wizard 2012-08-15
constraint_exclusion= on# pgtune wizard 
2012-08-15
checkpoint_completion_target= 0.9   # pgtune wizard 2012-08-15
effective_cache_size= 2816MB# pgtune wizard 
2012-08-15
work_mem= 24MB  # pgtune wizard 2012-08-15
wal_buffers = 8MB   # pgtune wizard 
2012-08-15
checkpoint_segments = 16# pgtune wizard 
2012-08-15
shared_buffers  = 960MB # pgtune wizard 2012-08-15
max_connections = 80# pgtune wizard 
2012-08-15

After a day, the file is 412kb large. I've just installed strace, and I try to 
capture a 2-4 hours work, and check what is going on. 

ulimit (& ulimit -f) output is unlimited. 

I'll be back (:-)) within few days with the results. Thank you all the 
informations.

Regards,
Csaba

-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Wednesday, August 15, 2012 3:34 PM
To: Carl von Clausewitz
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] corrupted statistics file "pg_stat_tmp/pgstat.stat"

"Carl von Clausewitz"  writes:
> I’ve restored from TAR backup our databases, and everything looked fine. 
> Without changing any setting in postgresql.conf (or in kernel settings) – 
> only “track_counts=on”, after 2-3 days, I’m receiving huge number 
> (~5-10 PCS in every second) of error messages like that in 
> /var/log/postgresql.log:
> *** Aug 15 06:27:26 eurodb postgres[77652]: [43-1] WARNING:  corrupted 
> statistics file "pg_stat_tmp/pgstat.stat"

Huh.  The stats collector process ought to rewrite that file fairly often, so 
this suggests it's consistently failing to rewrite it.

You might take a look at what the file looks like after a day or so of normal 
operation (eg, how big is it, how often does it get updated) and then compare 
to what it looks like after the errors start.

Also, try strace'ing the stats collector process for a little while (long 
enough to capture a stats file rewrite sequence) during normal operation, and 
then again after the errors start.

I don't want to speculate too much in advance of the data, but I'm wondering 
about a ulimit setting that limits how much data the stats collector can write 
during its lifetime (ulimit -f or local equivalent).
That would eventually cause problems for any postgres process, but if you did 
accidentally have one in place when starting the postmaster, maybe the stats 
collector would be first to show symptoms.

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

[GENERAL] Set Returning Functions and joins

[David Greco]

I'm porting some code from an Oracle application and we have many uses of set 
returning function. In particular, we are using them in joins of the form:

CREATE TABLE dave ( id integer, field1 integer );
INSERT INTO dave VALUES (1, 10);

SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)


In reality, the examples are not trivial like this, and the set returning 
function returns sets of records, not single values.
Now, in the case of a LEFT JOIN and a function returning a setoff a simple 
value, I can rewrite it simply as:
SELECT
id, generate_series(1, dave.field1)
FROM
dave


In the case of a LEFT JOIN and  a function returning a setoff a record, I can 
rewrite it as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave



I then figured I can rewrite INNER JOINs as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave
WHERE
Exists ( SELECT 1 FROM getRecord(1, dave.field1) )

Though I suppose this is running getRecord once for every row in dave, then 
another time for every row being returned.

Now in some non-trivial examples involving multiple joins on set returning 
functions, this gets pretty complicated.




Is there any alternative? Or I can suggest that a query the original form 
should be allowed?

SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)

Re: [GENERAL] Set Returning Functions and joins

[David Johnston]

On Aug 15, 2012, at 15:55, David Greco  wrote:

> I’m porting some code from an Oracle application and we have many uses of set 
> returning function. In particular, we are using them in joins of the form:
>  
> CREATE TABLE dave ( id integer, field1 integer );
> INSERT INTO dave VALUES (1, 10);
>  
> SELECT
> id, g.*
> FROM
> dave
> INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)
>  
>  
> In reality, the examples are not trivial like this, and the set returning 
> function returns sets of records, not single values.
> Now, in the case of a LEFT JOIN and a function returning a setoff a simple 
> value, I can rewrite it simply as:
> SELECT
> id, generate_series(1, dave.field1)
> FROM
> dave
>  
>  
> In the case of a LEFT JOIN and  a function returning a setoff a record, I can 
> rewrite it as:
> SELECT
> id, ( getRecord(1, dave.field1) ).*
> FROM
> dave
>
>  
>  
> I then figured I can rewrite INNER JOINs as:
> SELECT
> id, ( getRecord(1, dave.field1) ).*
> FROM
> dave
> WHERE
> Exists ( SELECT 1 FROM getRecord(1, dave.field1) )
>  
> Though I suppose this is running getRecord once for every row in dave, then 
> another time for every row being returned.
>  
> Now in some non-trivial examples involving multiple joins on set returning 
> functions, this gets pretty complicated.
>  
>  
>  
>  
> Is there any alternative? Or I can suggest that a query the original form 
> should be allowed?
>  
> SELECT
> id, g.*
> FROM
> dave
> INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)
>  

I suggest putting your function calls within a CTE (common table expression: 
SQL command WITH) structure.

I do not get why you think you need an "EXISTS" in the INNER JOIN situation.  
At worse you should make it a sub-query and add a IS NOT NULL condition on one 
of the function result columns.  There is no way to avoid evaluating once per 
record in dave but you should never have to evaluate more frequently than that. 
 CTE and sub-selects are your friends.

The original form is currently being developed but will not be available until 
at least 9.3

David J.

Re: [GENERAL] Set Returning Functions and joins

[David Greco]

Not sure how to write the CTE form of this. This query gives an error that dave 
is missing a from clause entry.
WITH o as (
SELECT getRecord(1, dave.field1)
)
SELECT
id, o.*
FROM
dave
;




Regarding the INNER JOIN, actually it appears the opposite is true:
SELECT
id, generate_series(1, dave.field1)
FROM
Dave
Works great if generate_series returns rows, but does not return any rows if 
generate_series does not return rows (i.e. if dave has a row with field1=null). 
So in fact, I need to figure out how to rewrite a LEFT JOIN version of this 
query.





From: David Johnston [mailto:pol...@yahoo.com]
Sent: Wednesday, August 15, 2012 4:16 PM
To: David Greco
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Set Returning Functions and joins

On Aug 15, 2012, at 15:55, David Greco 
mailto:david_gr...@harte-hanks.com>> wrote:
I’m porting some code from an Oracle application and we have many uses of set 
returning function. In particular, we are using them in joins of the form:

CREATE TABLE dave ( id integer, field1 integer );
INSERT INTO dave VALUES (1, 10);

SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)


In reality, the examples are not trivial like this, and the set returning 
function returns sets of records, not single values.
Now, in the case of a LEFT JOIN and a function returning a setoff a simple 
value, I can rewrite it simply as:
SELECT
id, generate_series(1, dave.field1)
FROM
dave


In the case of a LEFT JOIN and  a function returning a setoff a record, I can 
rewrite it as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave



I then figured I can rewrite INNER JOINs as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave
WHERE
Exists ( SELECT 1 FROM getRecord(1, dave.field1) )

Though I suppose this is running getRecord once for every row in dave, then 
another time for every row being returned.

Now in some non-trivial examples involving multiple joins on set returning 
functions, this gets pretty complicated.




Is there any alternative? Or I can suggest that a query the original form 
should be allowed?

SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)


I suggest putting your function calls within a CTE (common table expression: 
SQL command WITH) structure.

I do not get why you think you need an "EXISTS" in the INNER JOIN situation.  
At worse you should make it a sub-query and add a IS NOT NULL condition on one 
of the function result columns.  There is no way to avoid evaluating once per 
record in dave but you should never have to evaluate more frequently than that. 
 CTE and sub-selects are your friends.

The original form is currently being developed but will not be available until 
at least 9.3

David J.

Re: [GENERAL] Interval to months

[Aram Fingal]

On Aug 7, 2012, at 11:55 AM, Steve Atkins wrote:

> Something like this?
> 
> select 12 * extract(year from ?) + extract(month from ?) + extract(epoch from 
> ? - date_trunc('month', ?)) / 2592000
> 
> Ugly, but likely closer to accurate. You can't get actually accurate, of 
> course, as you don't know how long a month is.

Thanks very much.  I checked several examples of short, medium and long 
intervals (in the context of my data set) and your solution returns all of them 
exactly as they were in the text files which I used to import the data.  That's 
as much accuracy as I need.  I don't even know the exact standard used by the 
source for a month, though I could ask if it becomes an issue.   

-Aram Fingal

Re: [GENERAL] Set Returning Functions and joins

[David Johnston]

Inline comments:

On Aug 15, 2012, at 16:24, David Greco  wrote:

> Not sure how to write the CTE form of this. This query gives an error that 
> dave is missing a from clause entry.
> WITH o as (
> SELECT getRecord(1, dave.field1)
> )
> SELECT
> id, o.*
> FROM
> dave
> ;
>  
>  


With o (id, result) as (select id, getRecord(...) from dave)
Select id, (o.result).*
From dave
Left? Join o Using (id)


>  
> Regarding the INNER JOIN, actually it appears the opposite is true:
> SELECT
> id, generate_series(1, dave.field1)
> FROM
> Dave
> Works great if generate_series returns rows, but does not return any rows if 
> generate_series does not return rows (i.e. if dave has a row with 
> field1=null). So in fact, I need to figure out how to rewrite a LEFT JOIN 
> version of this query

Agreed.  See above example that can handle both.

>  
>  
> From: David Johnston [mailto:pol...@yahoo.com] 
> Sent: Wednesday, August 15, 2012 4:16 PM
> To: David Greco
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Set Returning Functions and joins
>  
> On Aug 15, 2012, at 15:55, David Greco  wrote:
> 
> I’m porting some code from an Oracle application and we have many uses of set 
> returning function. In particular, we are using them in joins of the form:
>  
> CREATE TABLE dave ( id integer, field1 integer );
> INSERT INTO dave VALUES (1, 10);
>  
> SELECT
> id, g.*
> FROM
> dave
> INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)
>  
>  
> In reality, the examples are not trivial like this, and the set returning 
> function returns sets of records, not single values.
> Now, in the case of a LEFT JOIN and a function returning a setoff a simple 
> value, I can rewrite it simply as:
> SELECT
> id, generate_series(1, dave.field1)
> FROM
> dave
>  
>  
> In the case of a LEFT JOIN and  a function returning a setoff a record, I can 
> rewrite it as:
> SELECT
> id, ( getRecord(1, dave.field1) ).*
> FROM
> dave
>
>  
>  
> I then figured I can rewrite INNER JOINs as:
> SELECT
> id, ( getRecord(1, dave.field1) ).*
> FROM
> dave
> WHERE
> Exists ( SELECT 1 FROM getRecord(1, dave.field1) )
>  
> Though I suppose this is running getRecord once for every row in dave, then 
> another time for every row being returned.
>  
> Now in some non-trivial examples involving multiple joins on set returning 
> functions, this gets pretty complicated.
>  
>  
>  
>  
> Is there any alternative? Or I can suggest that a query the original form 
> should be allowed?
>  
> SELECT
> id, g.*
> FROM
> dave
> INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)
>  
>  
> I suggest putting your function calls within a CTE (common table expression: 
> SQL command WITH) structure.
>  
> I do not get why you think you need an "EXISTS" in the INNER JOIN situation.  
> At worse you should make it a sub-query and add a IS NOT NULL condition on 
> one of the function result columns.  There is no way to avoid evaluating once 
> per record in dave but you should never have to evaluate more frequently than 
> that.  CTE and sub-selects are your friends.
>  
> The original form is currently being developed but will not be available 
> until at least 9.3
>  
> David J.
>  
>  
>  
>  
>  
>  

Re: [GENERAL] Set Returning Functions and joins

[David Greco]

Awesome, thanks that works and is quite clear.  The plan looks a bit funny on 
this. Any high-level synopsis on the performance of this?


From: David Johnston [mailto:pol...@yahoo.com]
Sent: Wednesday, August 15, 2012 4:57 PM
To: David Greco
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Set Returning Functions and joins

Inline comments:

On Aug 15, 2012, at 16:24, David Greco 
mailto:david_gr...@harte-hanks.com>> wrote:
Not sure how to write the CTE form of this. This query gives an error that dave 
is missing a from clause entry.
WITH o as (
SELECT getRecord(1, dave.field1)
)
SELECT
id, o.*
FROM
dave
;




With o (id, result) as (select id, getRecord(...) from dave)
Select id, (o.result).*
From dave
Left? Join o Using (id)




Regarding the INNER JOIN, actually it appears the opposite is true:
SELECT
id, generate_series(1, dave.field1)
FROM
Dave
Works great if generate_series returns rows, but does not return any rows if 
generate_series does not return rows (i.e. if dave has a row with field1=null). 
So in fact, I need to figure out how to rewrite a LEFT JOIN version of this 
query

Agreed.  See above example that can handle both.




From: David Johnston [mailto:pol...@yahoo.com]
Sent: Wednesday, August 15, 2012 4:16 PM
To: David Greco
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Set Returning Functions and joins

On Aug 15, 2012, at 15:55, David Greco 
mailto:david_gr...@harte-hanks.com>> wrote:
I’m porting some code from an Oracle application and we have many uses of set 
returning function. In particular, we are using them in joins of the form:

CREATE TABLE dave ( id integer, field1 integer );
INSERT INTO dave VALUES (1, 10);

SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)


In reality, the examples are not trivial like this, and the set returning 
function returns sets of records, not single values.
Now, in the case of a LEFT JOIN and a function returning a setoff a simple 
value, I can rewrite it simply as:
SELECT
id, generate_series(1, dave.field1)
FROM
dave


In the case of a LEFT JOIN and  a function returning a setoff a record, I can 
rewrite it as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave



I then figured I can rewrite INNER JOINs as:
SELECT
id, ( getRecord(1, dave.field1) ).*
FROM
dave
WHERE
Exists ( SELECT 1 FROM getRecord(1, dave.field1) )

Though I suppose this is running getRecord once for every row in dave, then 
another time for every row being returned.

Now in some non-trivial examples involving multiple joins on set returning 
functions, this gets pretty complicated.




Is there any alternative? Or I can suggest that a query the original form 
should be allowed?

SELECT
id, g.*
FROM
dave
INNER JOIN generate_series( 1, dave.field1 ) ON (1=1)


I suggest putting your function calls within a CTE (common table expression: 
SQL command WITH) structure.

I do not get why you think you need an "EXISTS" in the INNER JOIN situation.  
At worse you should make it a sub-query and add a IS NOT NULL condition on one 
of the function result columns.  There is no way to avoid evaluating once per 
record in dave but you should never have to evaluate more frequently than that. 
 CTE and sub-selects are your friends.

The original form is currently being developed but will not be available until 
at least 9.3

David J.