date:20121106

Hi, all,

I have a question regarding the ident_file configuration entry.

Why the server refused to start without specifying the ident_file, but it
never cares whether the given ident_file is valid or not? In other word,
a misconfigured non-existent path for ident_file can also start the
server with a background message.

This does not make too much sense to me. I think the system behavior should
be opposite. If the user didn't specify the ident_file, the pg server
should ignore. But if the user specifies one and that one is not valid, the
user should stop because it clearly means the path is misconfigured.

Is there any concern here? Otherwise I would suggest and provide a patch to
check the validity of the ident_file like what pg is doing for
data_directory and hba_file.

Thanks a lot!
Tianyin

-- 
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

Re: [GENERAL] Does PostgreSQL have complete functional test cases?

Thanks, Pavel!

I see. So the regress test cases are the complete functional testing? Am I
right?

(I thought they're test cases for regression)

T



On Tue, Nov 6, 2012 at 12:00 AM, Pavel Stehule pavel.steh...@gmail.comwrote:

 hello

 2012/11/6 Tianyin Xu t...@cs.ucsd.edu:
  Hi,
 
  Does anyone know whether PostgreSQL has complete functional test cases?
 And
  where can I find them?
 
  Currently, I only find some test cases in PG_SOURCE/src/tests, but it
  seems that they are pretty simple and do not cover a lot of features of
  PostgreSQL.
 
  Thanks a lot!
  Tianyin
 

 did you see postgresql/src/test/regress/sql ?

 Regards

 Pavel Stehule


  --
  Tianyin XU,
  http://cseweb.ucsd.edu/~tixu/




-- 
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

Re: [GENERAL] Does PostgreSQL have complete functional test cases?

2012-11-06 Thread Pavel Stehule

2012/11/6 Tianyin Xu t...@cs.ucsd.edu:
 Thanks, Pavel!

 I see. So the regress test cases are the complete functional testing? Am I
 right?

yes


 (I thought they're test cases for regression)

 T




 On Tue, Nov 6, 2012 at 12:00 AM, Pavel Stehule pavel.steh...@gmail.com
 wrote:

 hello

 2012/11/6 Tianyin Xu t...@cs.ucsd.edu:
  Hi,
 
  Does anyone know whether PostgreSQL has complete functional test cases?
  And
  where can I find them?
 
  Currently, I only find some test cases in PG_SOURCE/src/tests, but it
  seems that they are pretty simple and do not cover a lot of features of
  PostgreSQL.
 
  Thanks a lot!
  Tianyin
 

 did you see postgresql/src/test/regress/sql ?

 Regards

 Pavel Stehule


  --
  Tianyin XU,
  http://cseweb.ucsd.edu/~tixu/




 --
 Tianyin XU,
 http://cseweb.ucsd.edu/~tixu/


-- 
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] Does PostgreSQL have complete functional test cases?

Thanks again!

t

On Tue, Nov 6, 2012 at 12:24 AM, Pavel Stehule pavel.steh...@gmail.comwrote:

 2012/11/6 Tianyin Xu t...@cs.ucsd.edu:
  Thanks, Pavel!
 
  I see. So the regress test cases are the complete functional testing? Am
 I
  right?

 yes

 
  (I thought they're test cases for regression)
 
  T
 
 
 
 
  On Tue, Nov 6, 2012 at 12:00 AM, Pavel Stehule pavel.steh...@gmail.com
  wrote:
 
  hello
 
  2012/11/6 Tianyin Xu t...@cs.ucsd.edu:
   Hi,
  
   Does anyone know whether PostgreSQL has complete functional test
 cases?
   And
   where can I find them?
  
   Currently, I only find some test cases in PG_SOURCE/src/tests, but
 it
   seems that they are pretty simple and do not cover a lot of features
 of
   PostgreSQL.
  
   Thanks a lot!
   Tianyin
  
 
  did you see postgresql/src/test/regress/sql ?
 
  Regards
 
  Pavel Stehule
 
 
   --
   Tianyin XU,
   http://cseweb.ucsd.edu/~tixu/
 
 
 
 
  --
  Tianyin XU,
  http://cseweb.ucsd.edu/~tixu/




-- 
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

[GENERAL] Problem with streaming replication over SSL

I have streaming replication configured over SSL, and
there seems to be a problem with SSL renegotiation.

This is from the primary's log:

2012-11-06 00:13:10.990
CET,replication,,5204,10.153.109.3:49889,509843df.1454,10,streami
ng 1E3/76D64000,2012-11-05 23:55:27 CET,4/0,0,LOG,08P01,SSL
renegotiation failure,walreceiver

2012-11-06 00:13:10.998
CET,replication,,5204,10.153.109.3:49889,509843df.1454,11,streami
ng 1E3/76D64000,2012-11-05 23:55:27 CET,4/0,0,LOG,08P01,SSL error:
unexpected record,walreceiver

2012-11-06 00:13:10.998
CET,replication,,5204,10.153.109.3:49889,509843df.1454,12,streami
ng 1E3/76D64000,2012-11-05 23:55:27 CET,4/0,0,LOG,08006,could not send
data to client: Connection reset by peer,walreceiver

This is what the standby has to say:

2012-11-06 00:13:11.001 CET,,,26789,,509843df.68a5,2,,2012-11-05
23:55:27 CET,,0,FATAL,XX000,could not receive data from WAL stream: SSL
error: sslv3 alert unexpected message
,

This is PostgreSQL 9.1.3 on RHEL 6, openssl-1.0.0-20.el6.x86_64,
kernel 2.6.32-220.el6.x86_64.


After that, streaming replication reconnects and resumes working.

Is this an oversight in the replication protocol, or is this
working as designed?

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

Re: [GENERAL] Exclusion constraints with time expressions

Thomas Munro wrote:
 I am using 9.1.6, and I've set up a partitioned table as described in
the manual, with partitions
 based on a timestamptz column called 'time'.  The exclusion
constraints work nicely when I select
 ranges of times with literal constants.  But why would a WHERE clause
like the following not benefit
 from exclusion constraints?
 
   time  TIMESTAMPTZ '2012-11-03 00:00:00Z' + INTERVAL '24 hours'
 
 
 Isn't that expression on the right reducible to a constant up front?
Obviously I can use a host
 language to do the arithmetic and provide a constant, but I am curious
to understand why that wouldn't
 be constant-folded.

I think the problem is that this + operator is implemented
by the function timestamptz_pl_interval, which is STABLE
but not IMMUTABLE.

I am not sure why this function cannot be IMMUTABLE, it
seems to me that it should be.

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

Re: [GENERAL] Problem with streaming replication over SSL

On Tue, Nov 6, 2012 at 10:47 AM, Albe Laurenz laurenz.a...@wien.gv.atwrote:

 I have streaming replication configured over SSL, and
 there seems to be a problem with SSL renegotiation.

 This is from the primary's log:

 2012-11-06 00:13:10.990
 CET,replication,,5204,10.153.109.3:49889,509843df.1454,10,streami
 ng 1E3/76D64000,2012-11-05 23:55:27 CET,4/0,0,LOG,08P01,SSL
 renegotiation failure,walreceiver

 2012-11-06 00:13:10.998
 CET,replication,,5204,10.153.109.3:49889,509843df.1454,11,streami
 ng 1E3/76D64000,2012-11-05 23:55:27 CET,4/0,0,LOG,08P01,SSL error:
 unexpected record,walreceiver

 2012-11-06 00:13:10.998
 CET,replication,,5204,10.153.109.3:49889,509843df.1454,12,streami
 ng 1E3/76D64000,2012-11-05 23:55:27 CET,4/0,0,LOG,08006,could not send
 data to client: Connection reset by peer,walreceiver

 This is what the standby has to say:

 2012-11-06 00:13:11.001 CET,,,26789,,509843df.68a5,2,,2012-11-05
 23:55:27 CET,,0,FATAL,XX000,could not receive data from WAL stream: SSL
 error: sslv3 alert unexpected message
 ,

 This is PostgreSQL 9.1.3 on RHEL 6, openssl-1.0.0-20.el6.x86_64,
 kernel 2.6.32-220.el6.x86_64.


 After that, streaming replication reconnects and resumes working.

 Is this an oversight in the replication protocol, or is this
 working as designed?


This sounds a lot like the general issue with SSL renegotiation, just that
it tends to show itself more often on replication connections since they
don't disconnect very often...

Have you tried disabling SSL renegotiation on the connection
(ssl_renegotation=0)? If that helps, then the SSL library on one of the
ends  still has the problem with renegotiation...

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Question about ident_file in postgres.conf

On Tue, Nov 6, 2012 at 9:08 AM, Tianyin Xu t...@cs.ucsd.edu wrote:

 Hi, all,

 I have a question regarding the ident_file configuration entry.

 Why the server refused to start without specifying the ident_file, but
 it never cares whether the given ident_file is valid or not? In other
 word, a misconfigured non-existent path for ident_file can also start the
 server with a background message.

 This does not make too much sense to me. I think the system behavior
 should be opposite. If the user didn't specify the ident_file, the pg
 server should ignore. But if the user specifies one and that one is not
 valid, the user should stop because it clearly means the path is
 misconfigured.

 Is there any concern here? Otherwise I would suggest and provide a patch
 to check the validity of the ident_file like what pg is doing for
 data_directory and hba_file.


This is definitely a known problem. It's actually on the TODO list already,
just not phrased in a way that makes it likely to be found.

It should be treated the same way as the hba_file. So sure, please do
provide a patch for that if you can.

-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Problem with streaming replication over SSL

Magnus Hagander wrote:
 I have streaming replication configured over SSL, and
 there seems to be a problem with SSL renegotiation.
[...]
 After that, streaming replication reconnects and resumes working.
 
 Is this an oversight in the replication protocol, or is this
 working as designed?

 This sounds a lot like the general issue with SSL renegotiation, just
that it tends to show itself
 more often on replication connections since they don't disconnect very
often...
 
 Have you tried disabling SSL renegotiation on the connection
(ssl_renegotation=0)? If that helps, then
 the SSL library on one of the ends  still has the problem with
renegotiation...

It can hardly be the CVE-2009-3555 renegotiation problem.

Both machines have OpenSSL 1.0.0, and RFC 5746 was implemented in
0.9.8m.

But I'll try to test if normal connections have the problem too.

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

Re: [GENERAL] Problem with streaming replication over SSL

On Tue, Nov 6, 2012 at 12:47 PM, Albe Laurenz laurenz.a...@wien.gv.atwrote:

 Magnus Hagander wrote:
  I have streaming replication configured over SSL, and
  there seems to be a problem with SSL renegotiation.
 [...]
  After that, streaming replication reconnects and resumes working.
 
  Is this an oversight in the replication protocol, or is this
  working as designed?

  This sounds a lot like the general issue with SSL renegotiation, just
 that it tends to show itself
  more often on replication connections since they don't disconnect very
 often...
 
  Have you tried disabling SSL renegotiation on the connection
 (ssl_renegotation=0)? If that helps, then
  the SSL library on one of the ends  still has the problem with
 renegotiation...

 It can hardly be the CVE-2009-3555 renegotiation problem.

 Both machines have OpenSSL 1.0.0, and RFC 5746 was implemented in
 0.9.8m.


It certainly *sounds* like that problem though. Maybe RedHat carried along
the broken fix? It would surprise me, but given that it's openssl, not
hugely much so :)

It would be worth trying with ssl_renegotiation=0 to see if the problem
goes away.


But I'll try to test if normal connections have the problem too.


That would be a useful datapoint. All settings around this *should* happen
at a lower layer than the difference between a replication connection and a
regular one, but it would be good to confir mit.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

[GENERAL] Tuning / performance questions

2012-11-06 Thread Bryan Montgomery

Hello experts! (and other like me).
**

We have a system, that due to recent events is getting a lot heavier use.
The application makes extensive use of functions. These functions would
typically run sub-second but now can take several seconds.

I'm wondering what general ways there are to monitor and improve
performance? We look at pgadmin's server status but that only sees the
function being run. Additionally, is there a good way to 'explain' a
function? Or do you have to execute the function steps individually and
explain those?

The server is running on suse 11.4 with 8 vcpu and 32Gb ram on a virtual
machine.

Running pg_version returns 'PostgreSQL 9.1.0 on x86_64-unknown-linux-gnu,
compiled by gcc (SUSE Linux) 4.5.1 20101208 [gcc-4_5-branch revision
167585], 64-bit' and select pg_size_pretty(pg_database_size('nrgdb'));
returns 63 GB.

The server typically has up to 500 connections with a max of 750
connections.

Below are the non-default values of our configuration file.

Any thoughts on what we should look at?

Thanks,

Bryan

listen_addresses = '*'  # what IP address(es) to listen on;

max_connections = 750   # (change requires restart)

superuser_reserved_connections = 9  # (change requires restart)

shared_buffers = 8192MB # min 128kB or max_connections*16kB

temp_buffers = 64MB # min 800kB

max_prepared_transactions = 250 # can be 0 or more

work_mem = 512MB# min 64kB

maintenance_work_mem = 1GB  # min 1MB

fsync = off # turns forced synchronization on
or off

full_page_writes = off  # recover from partial page writes

wal_buffers = 16MB  # min 32kB

commit_delay = 1000 # range 0-10, in microseconds

commit_siblings = 5 # range 1-1000

checkpoint_segments = 50# in logfile segments, min 1, 16MB
each

checkpoint_timeout = 5min   # range 30s-1h

checkpoint_warning = 1min   # 0 is off

effective_cache_size = 16GB

log_destination = 'stderr'  # Valid values are combinations of

logging_collector = on

Log_directory = '/var/log/postgres' # Directory where log files
are written

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.

log_rotation_age = 1d   # Automatic rotation of logfiles
will

log_rotation_size = 20MB# Automatic rotation of logfiles
will

log_min_messages = info # Values, in order of decreasing detail:

log_min_duration_statement = 1 # -1 is disabled, 0 logs all statements

log_line_prefix = '%t %p %u@%h: '   # Special values for Pgsi

log_statement = 'none'  # none, ddl, mod, all

log_duration = off

autovacuum = on # enable autovacuum subprocess?

datestyle = 'iso, mdy'

lc_messages = 'en_US.UTF-8' # locale for system error
message

lc_monetary = 'en_US.UTF-8' # locale for monetary
formatting

lc_numeric = 'en_US.UTF-8'  # locale for number
formatting

lc_time = 'en_US.UTF-8' # locale for time formatting

Re: [GENERAL] Exclusion constraints with time expressions

2012-11-06 Thread hari . fuchs

Albe Laurenz laurenz.a...@wien.gv.at writes:

 I think the problem is that this + operator is implemented
 by the function timestamptz_pl_interval, which is STABLE
 but not IMMUTABLE.

 I am not sure why this function cannot be IMMUTABLE, it
 seems to me that it should be.

No: the result of e.g.

  SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';

depends on the client's timezone and its DST rules.



-- 
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] Exclusion constraints with time expressions

hari.fu...@gmail.com wrote:
  I think the problem is that this + operator is implemented
  by the function timestamptz_pl_interval, which is STABLE
  but not IMMUTABLE.
 
  I am not sure why this function cannot be IMMUTABLE, it
  seems to me that it should be.
 
 No: the result of e.g.
 
   SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';
 
 depends on the client's timezone and its DST rules.

You are right; timestamptz_in itself is STABLE, and I forgot
about daylight savings time.

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

Re: [GENERAL] Hot Standby Not So Hot Anymore

Darren Duncan wrote:
 Ian Harding wrote:

 It says everything is happy as normal...
 
 2012-11-05 16:22:41.200 PST - :LOG: invalid record length at BA6/6DCBA48
 
 What does this log line mean? Is that happy as normal?

Note that the message level is LOG, not WARNING or ERROR or anything
more severe. It found the end of the valid WAL stream in WAL files it
was provided, and it's telling you how it decided it was at the end.

One thing I don't think you've really described is how you took your
base backup. You did follow all the instructions, like using
pg_start_backup() and pg_stop_backup(), excluding the pg_xlog
directory contents, and excluding the postmaster.pid file, right? If
you missed some details there you might see odd behavior.

-Kevin


-- 
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] Exclusion constraints with time expressions

hari.fu...@gmail.com

 No: the result of e.g.
 
  SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '24 hours';
 
 depends on the client's timezone and its DST rules.

Can you give an example of where adding an interval based on *hours*
to TIMESTAMP WITH TIME ZONE would give a different value based on
client's time zone and DST rules? If there is such a case, we have a
bug, IMO.

Now, if you wanted to argue that *this* query might depend on time
zone information, I'd be more willing to believe it, and maybe the
problem is that we use the same function for both:

SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '1 day';

-Kevin


-- 
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] Exclusion constraints with time expressions

Albe Laurenz wrote:
 Thomas Munro wrote:
 I am using 9.1.6, and I've set up a partitioned table as described
 in the manual, with partitions based on a timestamptz column
 called 'time'. The exclusion constraints work nicely when I select
 ranges of times with literal constants. But why would a WHERE
 clause like the following not benefit from exclusion constraints?
 
 time  TIMESTAMPTZ '2012-11-03 00:00:00Z' + INTERVAL '24 hours'
 
 
 Isn't that expression on the right reducible to a constant up
 front? Obviously I can use a host language to do the arithmetic
 and provide a constant, but I am curious to understand why that
 wouldn't be constant-folded.
 
 I think the problem is that this + operator is implemented
 by the function timestamptz_pl_interval, which is STABLE
 but not IMMUTABLE.
 
 I am not sure why this function cannot be IMMUTABLE, it
 seems to me that it should be.

For TIMESTAMP WITHOUT TIME ZONE it couldn't be IMMUTABLE, because the
result would be based on the time zone setting of the client
connection; but adding a fixed interval to a UTC time to get a UTC
time seems pretty immutable to me. That said, I'm not sure why STABLE
wouldn't be good enough for such an optimization, if it were
supported at all. I don't think we evaluate such expressions before
developing the plan, though.

If you run EXPLAIN ANALYZE on one of the queries involved, does it
actually perform the scan of partitions which can be skipped at
run-time, or does it show never executed?

-Kevin


-- 
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] Exclusion constraints with time expressions

2012-11-06 Thread Tom Lane

Kevin Grittner kgri...@mail.com writes:
 Now, if you wanted to argue that *this* query might depend on time
 zone information, I'd be more willing to believe it, and maybe the
 problem is that we use the same function for both:

 SELECT TIMESTAMPTZ '2012-10-28 01:30:00' + INTERVAL '1 day';

The problem is not with the function, but with the fact that both
kinds of interval are the same data type.  That's not something we have
the flexibility to change AFAICS.

It should be possible to use the protransform feature to allow
argument-value-dependent const folding, if anyone is annoyed enough
about this specific case to write some code for it.

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] Question about ident_file in postgres.conf

2012-11-06 Thread Jeff Janes

On Tue, Nov 6, 2012 at 3:16 AM, Magnus Hagander mag...@hagander.net wrote:
 On Tue, Nov 6, 2012 at 9:08 AM, Tianyin Xu t...@cs.ucsd.edu wrote:

 Hi, all,

 I have a question regarding the ident_file configuration entry.

 Why the server refused to start without specifying the ident_file, but
 it never cares whether the given ident_file is valid or not? In other
 word, a misconfigured non-existent path for ident_file can also start the
 server with a background message.

 This does not make too much sense to me. I think the system behavior
 should be opposite. If the user didn't specify the ident_file, the pg
 server should ignore. But if the user specifies one and that one is not
 valid, the user should stop because it clearly means the path is
 misconfigured.

 Is there any concern here? Otherwise I would suggest and provide a patch
 to check the validity of the ident_file like what pg is doing for
 data_directory and hba_file.


 This is definitely a known problem. It's actually on the TODO list already,
 just not phrased in a way that makes it likely to be found.

 It should be treated the same way as the hba_file. So sure, please do
 provide a patch for that if you can.


A patch for this is already committed for 9.3.

https://commitfest.postgresql.org/action/patch_view?id=889

Cheers,

Jeff


-- 
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] Question about ident_file in postgres.conf

On Tue, Nov 6, 2012 at 5:25 PM, Jeff Janes jeff.ja...@gmail.com wrote:

 On Tue, Nov 6, 2012 at 3:16 AM, Magnus Hagander mag...@hagander.net
 wrote:
  On Tue, Nov 6, 2012 at 9:08 AM, Tianyin Xu t...@cs.ucsd.edu wrote:
 
  Hi, all,
 
  I have a question regarding the ident_file configuration entry.
 
  Why the server refused to start without specifying the ident_file, but
  it never cares whether the given ident_file is valid or not? In other
  word, a misconfigured non-existent path for ident_file can also start
 the
  server with a background message.
 
  This does not make too much sense to me. I think the system behavior
  should be opposite. If the user didn't specify the ident_file, the pg
  server should ignore. But if the user specifies one and that one is not
  valid, the user should stop because it clearly means the path is
  misconfigured.
 
  Is there any concern here? Otherwise I would suggest and provide a patch
  to check the validity of the ident_file like what pg is doing for
  data_directory and hba_file.
 
 
  This is definitely a known problem. It's actually on the TODO list
 already,
  just not phrased in a way that makes it likely to be found.
 
  It should be treated the same way as the hba_file. So sure, please do
  provide a patch for that if you can.


 A patch for this is already committed for 9.3.

 https://commitfest.postgresql.org/action/patch_view?id=889



D'oh. I knew that. Can't believe I forgot. Apologies!


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Re: [GENERAL] Hot Standby Not So Hot Anymore

2012-11-06 Thread Ian Harding

On Tue, Nov 6, 2012 at 5:45 AM, Kevin Grittner kgri...@mail.com wrote:

 Darren Duncan wrote:
  Ian Harding wrote:

  It says everything is happy as normal...
 
  2012-11-05 16:22:41.200 PST - :LOG: invalid record length at BA6/6DCBA48
 
  What does this log line mean? Is that happy as normal?

 Note that the message level is LOG, not WARNING or ERROR or anything
 more severe. It found the end of the valid WAL stream in WAL files it
 was provided, and it's telling you how it decided it was at the end.

 One thing I don't think you've really described is how you took your
 base backup. You did follow all the instructions, like using
 pg_start_backup() and pg_stop_backup(), excluding the pg_xlog
 directory contents, and excluding the postmaster.pid file, right? If
 you missed some details there you might see odd behavior.

 -Kevin

 Yeah, I forgot the pg_stop_backup.  I knew it was my mistake, I just
 didn't know what... Now I know!  That's what I get for doing it by hand.
 My script never forgets...

 Thanks!

 Ian
 --
 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] Tuning / performance questions

Bryan Montgomery wrote:

 We have a system, that due to recent events is getting a lot
 heavier use. The application makes extensive use of functions.
 These functions would typically run sub-second but now can take
 several seconds.

 The server is running on suse 11.4 with 8 vcpu and 32Gb ram on a
 virtual machine.
 
 Running pg_version returns 'PostgreSQL 9.1.0 on
 x86_64-unknown-linux-gnu, compiled by gcc (SUSE Linux) 4.5.1
 20101208 [gcc-4_5-branch revision 167585], 64-bit' and select
 pg_size_pretty(pg_database_size('nrgdb')); returns 63 GB.

 The server typically has up to 500 connections with a max of 750
 connections.

 max_connections = 750 # (change requires restart)

 temp_buffers = 64MB # min 800kB

 work_mem = 512MB # min 64kB

750 * ((512 MB) + (64 MB)) = 421.87500 gigabytes

Once a connection uses memory for temp_buffers, it doesn't release it
for as long as that connection runs. Each connection can allocate
work_mem for each node of a query plan. Even though maximum usage can
be more than one allocation per connection, rule of thumb is to
assume just one. You are up to needing 422 GB + shared memory + OS
space (including some buffers and cache) + whatever else you run on
this VM. You have 32 GB. You will almost certainly have problems at
high load.

Try putting pgbouncer in front of the database, configured to use
transaction mode and accept 750 user connections while only keeping
20 or so database conections open.

 max_prepared_transactions = 250 # can be 0 or more

Are you really using two phase commit and a transaction manager?
(Don't confuse this setting with something related to prepared
*statements* -- prepared *transacitons* are a separate issue.)  Even
if you are using prepared transactions, do you really expect your
transaction manager to let 250 transactions pile up in the database
between the first and second phase of commit?

BTW, you should be monitoring this table for old prepared
transactions that to prevent problems with bloat.

 fsync = off
 full_page_writes = off

You didn't mention your backup scheme, but be prepared for the fact
that with these settings, if the VM (or its underlying OS or
hardward) fails, your database will be corrupted and you may have no
choice but to use your backup.

 commit_delay = 1000 # range 0-10, in microseconds
 
 commit_siblings = 5 # range 1-1000

These settings are notoriously hard to configure from the default
without actually making things worse. Be very sure you know what you
are doing and have carefully benchmarked this against your real
workload; otherwise it is probably better to put these back to the
defaults.

There may be some other fine-tuning opportunities, but these issues
should be fixed first, and it would be best to have an actual query
that is performing poorly to try to tune some of the other settings.

-Kevin


-- 
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] Question about ident_file in postgres.conf

I see. Thanks a lot for the explanation!

Tianyin


On Tue, Nov 6, 2012 at 8:32 AM, Magnus Hagander mag...@hagander.net wrote:

 On Tue, Nov 6, 2012 at 5:25 PM, Jeff Janes jeff.ja...@gmail.com wrote:

 On Tue, Nov 6, 2012 at 3:16 AM, Magnus Hagander mag...@hagander.net
 wrote:
  On Tue, Nov 6, 2012 at 9:08 AM, Tianyin Xu t...@cs.ucsd.edu wrote:
 
  Hi, all,
 
  I have a question regarding the ident_file configuration entry.
 
  Why the server refused to start without specifying the ident_file,
 but
  it never cares whether the given ident_file is valid or not? In other
  word, a misconfigured non-existent path for ident_file can also
 start the
  server with a background message.
 
  This does not make too much sense to me. I think the system behavior
  should be opposite. If the user didn't specify the ident_file, the pg
  server should ignore. But if the user specifies one and that one is not
  valid, the user should stop because it clearly means the path is
  misconfigured.
 
  Is there any concern here? Otherwise I would suggest and provide a
 patch
  to check the validity of the ident_file like what pg is doing for
  data_directory and hba_file.
 
 
  This is definitely a known problem. It's actually on the TODO list
 already,
  just not phrased in a way that makes it likely to be found.
 
  It should be treated the same way as the hba_file. So sure, please do
  provide a patch for that if you can.


 A patch for this is already committed for 9.3.

 https://commitfest.postgresql.org/action/patch_view?id=889



 D'oh. I knew that. Can't believe I forgot. Apologies!


 --
  Magnus Hagander
  Me: http://www.hagander.net/
  Work: http://www.redpill-linpro.com/




-- 
Tianyin XU,
http://cseweb.ucsd.edu/~tixu/

Re: [GENERAL] sub query reference error or user error

2012-11-06 Thread David Johnston

On Nov 6, 2012, at 12:36, sivakumar krishnamurthy 
sivakumar.mailingl...@gmail.com wrote:

 Hi All,
   For the below test case shouldn't the  update statement throw error because 
 the sub query projects a column which is not existing in the table. For ex in 
 inner select nos1 column exists in t1 and not t2.
 Is this a bug or an OP error?
 
 template1=# SELECT version();
 version   
   
 
  PostgreSQL 9.1.6 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real 
 (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit
 (1 row)
 template1=# \d t1
   Table public.t1
  Column |  Type   | Modifiers 
 +-+---
  nos1   | integer | 
 
 template1=# \d t2
   Table public.t2
  Column |  Type   | Modifiers 
 +-+---
  nos2   | integer | 
 
 template1=# UPDATE t1 set nos1=2 where nos1 in (select nos1 from t2);
 UPDATE 0
 template1=# 
 
 Thanks,
 Sivakumar.K
 

In a correlated sub-query any column from the parent or sub-query can appear.  
In this case anything in t1or t2.  In this case the effect is a constant true 
evaluation since for each row in t1 the value of nos1 is constant.  The use of 
t2 simply causes the constant to be repeated once for each row in t2.  As a 
safety feature you should prefix your columns in sub-queries with the proper 
table name.  t2.nos1 would then throw the desired error.

Dave J.

-- 
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] Unexpectedly high disk space usage

2012-11-06 Thread Lists


Jeff, thanks for the feedback!

On 11/05/2012 08:51 PM, Jeff Janes wrote:

My first guesses would be things that are not directly under the
databases control, such as:

1) your server logs are accumulating and you aren't doing anything about them


I'm guessing that this is not the case:

[root@delta data]# du -shc *  | grep -i log
47M pg_clog
15M pg_log
641Mpg_xlog


2) you are taking backup snapshots to somewhere in that directory and
not cleaning them up


Our backup snapshots (taken with pg_dump) are taken on a different 
server over the network. Dumps are made several times during each day. 
Could this be part of the problem if (somehow) they didn't complete? And 
if so, would there be some cleanup I'd have to do other than restarting PG?



3) your archive_command is failing (which you should see reports of in
the server logs) and so you are accumulating xlog files.


As I understand things, the result above under 1) demonstrates that 
this, also, is not the cause.




--
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] Unexpectedly high disk space usage

2012-11-06 Thread Lists

I followed your example, the result is at the bottom. Based on this it 
would seem that there are 3-4 databases that seem to be the culprit. How 
could I get more depth/detail on what specifically is the problem?


-Ben

On 11/05/2012 07:10 PM, Scott Marlowe wrote:
What does du -sh have to say about it? Use unix tools to examine your 
file system and see where the usage is going. For instance, I can do 
this: cd /var/lib/postgresql/8.4/main/ du -s *|sort -n 0 server.crt 0 
server.key 4 pg_tblspc 4 pg_twophase 4 PG_VERSION 4 postmaster.opts 4 
postmaster.pid 12 pg_clog 12 pg_stat_tmp 12 pg_subtrans 28 
pg_multixact 460 global 16392 pg_xlog 16396 base which tells me that 
I'm using about 16MB for each pg_xlog and base. I can then do cd into 
base and look around: cd base du -s *|sort -n 5416 1 5416 11563 5560 
11564 Which shows me using about 5MB each for three different dbs. And 
so on. On an off guess, did you go from a SQL_ASCII encoding to UTF8? 
That might increase disk space usage a bit. 


[root@delta ~]# cd /var/lib/pgsql/9.1/data/
[root@delta data]# du -s * | sort -n
4   pg_ident.conf
4   pg_serial
4   pg_tblspc
4   PG_VERSION
4   postmaster.opts
4   postmaster.pid
8   pg_hba.conf
12  pg_notify
12  pg_twophase
20  postgresql.300
20  postgresql.conf
20  postgresql.conf.20120903
20  postgresql.conf.300
76  pg_subtrans
104 pg_multixact
15044   pg_log
18184   global
25216   pg_stat_tmp
47916   pg_clog
671916 pg_xlog
164753204   base

[root@delta data]# cd base
[root@delta base]# du -s * | sort -n
4   pgsql_tmp
612412772
638812780
64241
72424   331506
72700   160676
72896   391655
73200   52389
73216   523672
74104   619675
74956   295646
76768   307580
77896   547597
80824   571547
87368   475799
90940   631604
113876  124651
123548  148525
130096  367533
149792  439726
173648  355578
175404  679545
190732  559580
225780  511706
326468  667547
352736  655477
398736  535644
469408  136582
483716  499753
513124  270926
575612  715601
590408  487780
04  463779
713208  643540
714896  583515
803216  343438
806952  427663
855156  739506
872200  197221
975692  64371
987692  775594
1005268 595488
1024812 691482
1042212 727552
1047464 379566
1260044 76601
1276756 16384
1345072 403667
1474468 209158
1477808 172604
1536168 221124
1637652 258798
1811504 88598
1963740 245588
2076748 703467
2193536 415671
2430908 801322
2552640 319552
2785212 28315
3454880 112612
3755548 451666
3929420 100666
4651876 40451
5714940 751514
6257740 233293
7313900 184735
9334796 763606
10940780283609
20837264788338
45285640607471


--
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] Unexpectedly high disk space usage

2012-11-06 Thread Jeff Janes

On Tue, Nov 6, 2012 at 10:49 AM, Lists li...@benjamindsmith.com wrote:
 I followed your example, the result is at the bottom. Based on this it would
 seem that there are 3-4 databases that seem to be the culprit. How could I
 get more depth/detail on what specifically is the problem?

If you have installed the contrib modules (oid2name specifically), you
can use that to get the name of the bloated database:

oid2name | fgrep 607471

If the name of the database doesn't give you any insight, then look
for large files in the directory base/607471 that whose names all
start with the same digits and use oid2name to get the names of the
relations for those files.

oid2name -d name of database  -o base name of large files

Cheers,

Jeff


-- 
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] Unexpectedly high disk space usage

2012-11-06 Thread Tom Lane

Jeff Janes jeff.ja...@gmail.com writes:
 On Tue, Nov 6, 2012 at 10:49 AM, Lists li...@benjamindsmith.com wrote:
 I followed your example, the result is at the bottom. Based on this it would
 seem that there are 3-4 databases that seem to be the culprit. How could I
 get more depth/detail on what specifically is the problem?

 If you have installed the contrib modules (oid2name specifically), you
 can use that to get the name of the bloated database:
 oid2name | fgrep 607471

Or, if you didn't install contrib, try

select datname from pg_database where oid = 607471

 If the name of the database doesn't give you any insight, then look
 for large files in the directory base/607471 that whose names all
 start with the same digits and use oid2name to get the names of the
 relations for those files.

 oid2name -d name of database  -o base name of large files

For this you can try

select relname from pg_class where relfilenode = whatever

Or let the database do the work:

select relname, pg_relation_size(oid) from pg_class order by 2 desc;

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] Memory issue on FreeBSD

2012-11-06 Thread Vick Khera

On Mon, Nov 5, 2012 at 10:11 AM, Frank Broniewski b...@metrico.lu wrote:

 and this is after a few hours of running:

 Mem: 91M Active, 17G Inact, 3983M Wired, 1526M Cache, 3283M Buf, 155M Free
 Swap: 4096M Total, 828K Used, 4095M Free


For comparison, here is the output of a 32GB FreeBSD 9.0/amd64 server, with
Postgres 9.0.7 running since June 10, and is heavily pounded on 24x7.  The
data + indexes are about 240GB on disk.  This server only runs postgres
aside from the basic system processes.

Mem: 231M Active, 21G Inact, 3777M Wired, 1009M Cache, 3285M Buf, 191M Free
Swap: 4096M Total, 272K Used, 4096M Free

I agree with the conclusion that the shared memory segments are confusing
the output of top.  There are no memory leaks, and FreeBSD doesn't lose
any memory.

There are some scripts floating around that read values from sysctl
vm.stats.vm and format them nicely to tell you how much memory is used up
and free.  Try the one referenced here:
http://www.cyberciti.biz/faq/freebsd-command-to-get-ram-information/

[GENERAL] SPI function varchar difference between 9.0 and 9.2

2012-11-06 Thread Achilleas Mantzios

(pls, include me in the reply, i am resending this from here since the one sent 
from achill(at)matrix(dot)gatewaynet(dot)com didn't make it for some reason)



Hello, we are using and still maintaining a heavily modified version of 
DBMirror in our infrastructure, 
involving one master and 80 (and growing) remote slaves, connected via UUCP 
over (unreliable) satelite comms.
We modified DBmirror to account for as we call it :
Foreign Key dependency oriented, row grained, conditional, Asynchronous, Lazy 
replication.
 
Its simplicity and ease of extending were the major factors for adopting 
DBmirror early (circa 2004) instead of another replication 
solution.
 
Here is the description of the specific table i am trying to insert from inside 
the trigger function: (those who have worked with DBMirror should be familiar)
 
dynacom=# \d dbmirror_pendingdata
Table public.dbmirror_pendingdata
Column |   Type| Modifiers 
+---+---
seqid  | integer   | not null
iskey  | boolean   | not null
data   | character varying | 
Indexes:
dbmirror_pendingdata_pkey PRIMARY KEY, btree (seqid, iskey)
Foreign-key constraints:
$1 FOREIGN KEY (seqid) REFERENCES dbmirror_pending(seqid) ON UPDATE CASCADE 
ON DELETE CASCADE
 
 
 
Now the problem : up to 9.0 this code used to work :
 
Oid planArgTypes[1] = {NAMEOID};
char   *insQuery = INSERT INTO dbmirror_pendingdata (SeqId,IsKey,Data) 
VALUES(currval('dbmirror_pending_seqid_seq'),'f',$1);
void   *pplan;
Datum   planData[1];
char   *cpKeyData;
int iRetValue;
 
pplan = SPI_prepare(insQuery, 1, planArgTypes);
if (pplan == NULL)
{
elog(NOTICE, Could not prepare INSERT plan);
return -1;
}
 
cpKeyData = packageData();
// ^^ this is normal NULL terminated C char *, no varlena header
planData[0] = PointerGetDatum(cpKeyData);
iRetValue = SPI_execp(pplan, planData, NULL, 1);
if (cpKeyData != 0)
pfree(cpKeyData);
 
if (iRetValue != SPI_OK_INSERT)
{
elog(NOTICE, Error inserting row in pendingDelete);
return -1;
}
return 0;
 
The above worked fine till 9.0 (9.0 included) (although i am sure we should 
have updated the code to match some post 8.3 changes, ok we are guilty of this).
Trying to update a row of a table (in this example named items) participating 
in the DBMirror replication e.g. 
dynacom=# update items set comment = 1 where id=1637984;
is supposed to result into a row looking like the following :
dynacom=# select data from dbmirror_pendingdata where seqid = 28073232;
data 
--
id='1637984' vslwhid='579' serialno= rh= lastinspdate= 
classused='0' classaa= classsurvey= classsurveydate= classduedate= 
classpostponed= classcomment= defid='325010' machtypecount='1' 
totalrh='0' comment= attachments= lastrepdate='2011-06-27' pmsstate= 
 
I am writing the above to give you an idea of how the DBmirror encoding looks 
like on this specific table (name items).
 
Trying to run this In 9.2.1 the above code stores only varchars up to 64 bytes: 
 
dynacom=# select data from dbmirror_pendingdata where seqid in (select seqid 
FROM dbmirror_pending WHERE slaveid=533) AND NOT iskey;
data 

id='1377285' vslwhid='533' serialno= rh= lastinspdate=`|\x01\x08
 
We see that is data content is truncated after the 64th byte.
 
NAMEOID indeed has 64-byte length, so i changed this to :
 
Oid planArgTypes[1] = {VARCHAROID};
 
The result was a substantial delay (6 secs) in performing the update, + a major 
corruption of the inserted row :
 
dynacom=# \timing 
Timing is on.
dynacom=# 
dynacom=# update items set comment = 1 where id=1377285;
UPDATE 1
Time: 6272,113 ms
dynacom=# \timing 
Timing is off.
dynacom=# 
dynacom=# select data from dbmirror_pendingdata where seqid in (select seqid 
FROM dbmirror_pending WHERE slaveid=533) AND NOT iskey;
ERROR:  compressed data is corrupt
dynacom=# 
dynacom=# 
 
That got me thinking that maybe this particular function storeData (which is 
the basic function writing out replication data) needed major overhaul.
I read a bit about the varlena functions and ended up into something like this :
 
Oid planArgTypes[1] = {VARCHAROID};
char   *insQuery = INSERT INTO dbmirror_pendingdata (SeqId,IsKey,Data) 
VALUES(currval('dbmirror_pending_seqid_seq'),'f',$1);
SPIPlanPtr pplan;
Datum   planData[1];
char   *cpKeyData;
char   *cpKeyData_tmp;
int iRetValue;
 
pplan = SPI_prepare(insQuery, 1, planArgTypes);
if (pplan == NULL)
{
elog(NOTICE, Could not prepare INSERT plan);
return -1;
}
cpKeyData = packageData();
// ^^ this is

Re: [GENERAL] fuzzystrmatch module buggy? observations

2012-11-06 Thread Bruce Momjian

On Tue, Oct 30, 2012 at 02:29:09PM +0100, r d wrote:
 The fuzzystrmatch module (http://www.postgresql.org/docs/9.2/static/
 fuzzystrmatch.html) is currently, as of 9.2.1, documented with the caution At
 present, the soundex, metaphone, dmetaphone, and dmetaphone_alt functions do
 not work well with multibyte encodings (such as UTF-8). 
 
 While the venerable algorithms contained in the module seem to generally work
 for Latin strings from European languages which all have accented/diacritic
 characters such as äöüñáéíóúàèìòù, for languages with non-Latin characters 
 such
 as Kyrillic, Hebrew, Arabic, Chinese, these venerable algorithms return NULL
 (empty) or plain weirdness. 
 
 Some examples:
 
 dmetaphone ('Новости') = 'NN'
 soundex ('Новости') = NULL
 
 dmetaphone ('לפחות') = NULL
 soundex ('לפחות') = NULL
 
 soundex ('相关搜索') = NULL
 dmetaphone ('相关搜索') = NULL
 
 metaphone() crashes with SQL state: 42883 for all these strings (it tells me I
 should cast the 'unknown' input).
 
 The string 'äöüñáéíóúàèìòù' causes metaphone(), dmetaphone(), dmetaphone_alt,
 soundex() to fail.
 
 Only levenshtein() appears to function correctly with all above inputs, even
 when I let it compare Hebrew against Chinese strings.
 
 Summarizing my experience:
 * for english (ASCII equivalent), the module works, 
 * for the rest of the Latin charsets (equivalent to ISO 8859-x) the module
 works unreliably,
 * for non-latin chars (UTF8 with 2-4 bytes per char) the module does not work
 
 Note: My DB and the OS are set up for UTF-8.
 
 This would appear to be less a problem of Postgresql and the fuzzystrmach
 module itself but because there
 appear to exist no replacement algorithms adequate for a multilingual world -
 at least that is my impression 
 after looking at the IPA and http://www.lt-world.org websites and branching 
 out
 from there.

This is a very good summary.  I was not aware of all these behaviors.

 Given all this I have no idea of this is a bug at all or the state-of-the-art
 around this topic is inadequate.

I have no idea either.

 Questions (to the developers):
 - Is there anything in work or planned for the fuzzystrmatch module?
 - Does anybody know about adequate replacements or upgrades of the soundex,
 metaphone etc. algorithms from academia?

I have not heard of anyone working in this area.  What usually happens
is some expert in the field shows up and submits a patch to improve it.

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

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


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

[GENERAL] Comparing txid_current() to xmin

2012-11-06 Thread Mike Lewis

I am trying to make a trigger that updates a row once and only once per
transaction (even if this trigger gets fired multiple times).  The general
idea is that for a user we have a version number.  When we modify the
user's data, the version number is incremented then set on the object.  We
only need to increment the version number once.

I am thinking about doing something like:

update user
set version=version+1
where txid_current() != xmin and user_id = 352395;


So I guess my questions are:

How dirty is this?
Will I run into issues?
Is there a better way of doing this?

Thanks,
Mike

--
Michael Lewis
lolrus.org
mikelikes...@gmail.com

Re: [GENERAL] Comparing txid_current() to xmin

2012-11-06 Thread Sergey Konoplev

On Tue, Nov 6, 2012 at 2:55 PM, Mike Lewis mikelikes...@gmail.com wrote:
 I am trying to make a trigger that updates a row once and only once per
 transaction (even if this trigger gets fired multiple times).  The general
 idea is that for a user we have a version number.  When we modify the user's
 data, the version number is incremented then set on the object.  We only
 need to increment the version number once.

 I am thinking about doing something like:

 update user
 set version=version+1
 where txid_current() != xmin and user_id = 352395;


 So I guess my questions are:

 How dirty is this?
 Will I run into issues?
 Is there a better way of doing this?

AFAIU it will work without issues. However I would use an additional
modified column that is set by trigger every time the row is updated
(and inserted) to the current time stamp and use it instead of
txid_current()/xmin. The only my reason is that it can give me more
control than txid based solution, for example if I need to set the
modified column from outside, say to sync it with some another
database shard's data.

--
a database and software architect
http://www.linkedin.com/in/grayhemp

Jabber: gray...@gmail.com Skype: gray-hemp Phone: +14158679984


-- 
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] Tuning / performance questions

2012-11-06 Thread Craig Ringer

Thanks for including your configuration and version; it makes things
much easier.

Reply follows inline.

On 11/06/2012 09:04 PM, Bryan Montgomery wrote:
 I'm wondering what general ways there are to monitor and improve
 performance? We look at pgadmin's server status but that only sees the
 function being run. Additionally, is there a good way to 'explain' a
 function? Or do you have to execute the function steps individually
 and explain those?

See the auto_explain contrib module. It can explain statements within
functions, as well as the functions themselves.

http://www.postgresql.org/docs/current/static/auto-explain.html
http://www.postgresql.org/docs/devel/static/auto-explain.html

 The server typically has up to 500 connections with a max of 750
 connections.

Get a connection pooler. Urgently. See
http://wiki.postgresql.org/wiki/PgBouncer . It is extremely unlikely
that your server is running efficiently with that many concurrent
connections actively working. Reducing it to (say) 100 and using
transaction-level connection pooling may boost performance significantly.

 work_mem = 512MB# min 64kB

That's really dangerous with your connection count. If many connections
actually use that, you'll run out of RAM in a hurry and enter nasty
paging storm. If possible, reduce it, then raise it selectively in
transactions where you know a high work_mem is needed.

 fsync = off # turns forced synchronization
 on or off

So you don't value your data and don't mind if you lose all of it,
permanently and unrecoverably, if your server loses power or the host OS
hard crashes?

It's much safer to use `synchronous_commit = off` and a commit_delay. If
that isn't enough, get fast-flushing storage like a good raid controller
with a battery backed cache you can put in write-back mode, or some high
quality SSDs with power-protected write caches.

 full_page_writes = off  # recover from partial page writes

As above: I hope your data isn't important to you.

--
Craig Ringer

Re: [GENERAL] Comparing txid_current() to xmin

2012-11-06 Thread Alvaro Herrera

Mike Lewis escribió:
 I am trying to make a trigger that updates a row once and only once per
 transaction (even if this trigger gets fired multiple times).  The general
 idea is that for a user we have a version number.  When we modify the
 user's data, the version number is incremented then set on the object.  We
 only need to increment the version number once.
 
 I am thinking about doing something like:
 
 update user
 set version=version+1
 where txid_current() != xmin and user_id = 352395;

Uh, txid_current returns a 64 bit value, whereas xmin only stores the 32
least significant bits.  They would certainly differ after a xid
wraparound.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, 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] Comparing txid_current() to xmin

2012-11-06 Thread Mike Lewis


 Uh, txid_current returns a 64 bit value, whereas xmin only stores the 32
 least significant bits.  They would certainly differ after a xid
 wraparound.

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


Ah yes...  That would be a problem.  What about using just the lower 32
bits of the txid_current?

Re: [GENERAL] Tuning / performance questions

2012-11-06 Thread Bryan Montgomery

Thanks for the insight. I know why some of the settings were set, but there
are others that have been highlighted that now don't make sense. I'll
strongly recommend adjusting those now.

On Tue, Nov 6, 2012 at 8:54 PM, Craig Ringer ring...@ringerc.id.au wrote:

Thanks for including your configuration and version; it makes things
much easier.

Reply follows inline.

On 11/06/2012 09:04 PM, Bryan Montgomery wrote:

I'm wondering what general ways there are to monitor and improve
performance? We look at pgadmin's server status but that only sees the
function being run. Additionally, is there a good way to 'explain' a
function? Or do you have to execute the function steps individually and
explain those?

See the auto_explain contrib module. It can explain statements within
functions, as well as the functions themselves.

http://www.postgresql.org/docs/current/static/auto-explain.htmlhttp://www.postgresql.org/docs/devel/static/auto-explain.html

The server typically has up to 500 connections with a max of 750
connections.

Get a connection pooler. Urgently. See
http://wiki.postgresql.org/wiki/PgBouncer . It is extremely unlikely that
your server is running efficiently with that many concurrent connections
actively working. Reducing it to (say) 100 and using transaction-level
connection pooling may boost performance significantly.

work_mem = 512MB# min 64kB

That's really dangerous with your connection count. If many connections
actually use that, you'll run out of RAM in a hurry and enter nasty paging
storm. If possible, reduce it, then raise it selectively in transactions
where you know a high work_mem is needed.

fsync = off # turns forced synchronization
on or off

So you don't value your data and don't mind if you lose all of it,
permanently and unrecoverably, if your server loses power or the host OS
hard crashes?

It's much safer to use `synchronous_commit = off` and a commit_delay. If
that isn't enough, get fast-flushing storage like a good raid controller
with a battery backed cache you can put in write-back mode, or some high
quality SSDs with power-protected write caches.

full_page_writes = off # recover from partial page
writes

As above: I hope your data isn't important to you.

--
Craig Ringer

[GENERAL] creating a function returning FALSE on NULL input ?

2012-11-06 Thread Yvon Thoraval

I'd like to create a function :
returning true if the length of the text arg is greater than 0 ;
false otherwise ;

and also returning false when arg is NULL, then i wrote :

CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select
character_length($1)  0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL
INPUT;

where i get the error :

ERROR:  syntax error at or near FALSE
LINE 1: ...r_length($1)  0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON N...

with RETURNS NULL on NULL INPUT, that's OK.

-- 
Yvon

Re: [GENERAL] creating a function returning FALSE on NULL input ?

2012-11-06 Thread David Johnston

On Nov 7, 2012, at 0:33, Yvon Thoraval yvon.thora...@gmail.com wrote:

 I'd like to create a function :
 returning true if the length of the text arg is greater than 0 ;
 false otherwise ;
 
 and also returning false when arg is NULL, then i wrote :
 
 CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select 
 character_length($1)  0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL INPUT;
 
 where i get the error :
 
 ERROR:  syntax error at or near FALSE
 LINE 1: ...r_length($1)  0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON N...
 
 with RETURNS NULL on NULL INPUT, that's OK.
 
 -- 
 Yvon
 

You have to allow for null input and deal with it in the function body.  
Usually via a CASE structure.

David J.

Re: [GENERAL] Tuning / performance questions

2012-11-06 Thread Craig Ringer

On 11/07/2012 01:29 PM, Bryan Montgomery wrote:
 Thanks for the insight. I know why some of the settings were set, but
 there are others that have been highlighted that now don't make sense.
 I'll strongly recommend adjusting those now.
Good. If you've been running with fsync=off all this time I hope you've
also been keeping good backups.

BTW, I should've sent you a link to
http://wiki.postgresql.org/wiki/Number_Of_Database_Connections in my
prior email, where I recommended connection pooling.

--
Craig Ringer

Re: [GENERAL] creating a function returning FALSE on NULL input ?

2012-11-06 Thread Yvon Thoraval

Fine thanks,I've found that way :
recettes= DROP FUNCTION has_infos(text);
DROP FUNCTION


recettes= CREATE FUNCTION has_infos(text DEFAULT '') RETURNS boolean AS
$BODY$
recettes$ BEGIN
recettes$ IF character_length($1)  0 THEN
recettes$   RETURN TRUE;
recettes$ ELSE
recettes$   RETURN FALSE;
recettes$ END IF ;
recettes$ END
recettes$ $BODY$
recettes- LANGUAGE 'plpgsql' ;
CREATE FUNCTION

I've verified, it works on NULL input...



2012/11/7 David Johnston pol...@yahoo.com

 On Nov 7, 2012, at 0:33, Yvon Thoraval yvon.thora...@gmail.com wrote:

 I'd like to create a function :
 returning true if the length of the text arg is greater than 0 ;
 false otherwise ;

 and also returning false when arg is NULL, then i wrote :

 CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select
 character_length($1)  0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL
 INPUT;

 where i get the error :

 ERROR:  syntax error at or near FALSE
 LINE 1: ...r_length($1)  0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON N...

 with RETURNS NULL on NULL INPUT, that's OK.

 --
 Yvon


 You have to allow for null input and deal with it in the function body.
  Usually via a CASE structure.

 David J.




-- 
Yvon

Re: [GENERAL] creating a function returning FALSE on NULL input ?

2012-11-06 Thread Tom Lane

Yvon Thoraval yvon.thora...@gmail.com writes:
 CREATE FUNCTION has_infos(text) RETURNS boolean AS 'select
 character_length($1)  0;' LANGUAGE SQL IMMUTABLE RETURNS FALSE ON NULL
 INPUT;

FWIW, RETURNS NULL ON NULL INPUT is a formulaic phrase specified
in the SQL standard.  It's not meant to be something you can plug an
arbitrary value into --- it has to be written exactly that way.
The traditional Postgres term is just STRICT, which means precisely
the same thing.

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] The bug reporting form are unavailable