Re: [HACKERS] problem/bug in drop tablespace?

2012-05-09 Thread Albe Laurenz
Michael Nolan wrote:
 While researching a problem reported on the -general list by a user
who lost a disk containing his
 index tablespace, I ran into something, but  I'm not sure is a serious
bug or just an inconsistency in
 how \d shows tables.
 
 Here are the steps I took.
 
 1.  Create a new database 'MYDB' and connect to it.
 2.  Create a new tablespace 'MYTBLSP'
 3.  Create a table 'MYTABLE' and populate it.
 4.  Create an index 'MYIND' on that table, with the index in the new
tablespace, MYTBLSP.
 
 Now, exit psql and delete the files in the tablespace directory
created in step 2, simulating the
 problem the user had.
 
 Trying to execute an SQL command on the table MYTABLE will, as
expected, generate an error.
 
 Now, drop tablespace MYTBLSP.  This will produce an error, but it will
delete the tablespace according
 to \db.
 
 Recreate tablespace MYTBLSP.
 
 Regenerate the index on MYTABLE.  Queries will work on this table
again, as expected.
 
 Now, here's the problem I ran into:
 
 The index will be rebuilt in tablespace MYTBLSP, but \d on table
MYTABLE will not show the index as
 being in that tablespace.

I cannot reproduce this on 9.1.3:

test=# CREATE TABLESPACE mytbsp LOCATION '/home/laurenz/x';
CREATE TABLESPACE

test=# CREATE TABLE mytable(id integer PRIMARY KEY USING INDEX
TABLESPACE mytbsp, val text);
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index
mytable_pkey for table mytable
CREATE TABLE

test=# INSERT INTO mytable VALUES (1, 'eins'), (2, 'zwei');
INSERT 0 2

test=# \d mytable
   Table laurenz.mytable
 Column |  Type   | Modifiers
+-+---
 id | integer | not null
 val| text|
Indexes:
mytable_pkey PRIMARY KEY, btree (id), tablespace mytbsp

$ rm -rf /home/laurenz/x/PG_9.1_201105231/*

test=# SELECT * FROM mytable;
ERROR:  could not open file
pg_tblspc/46752/PG_9.1_201105231/16420/46759: No such file or
directory

Ok, that's expected.

test=# DROP TABLESPACE mytbsp;
DROP TABLESPACE

No error.

test=# CREATE TABLESPACE mytbsp LOCATION '/home/laurenz/x';
CREATE TABLESPACE

test=# REINDEX INDEX mytable_pkey;
ERROR:  could not create directory
pg_tblspc/46752/PG_9.1_201105231/16420: No such file or directory

Sure, the tablespace OID has changed.

test=# ALTER TABLE mytable DROP CONSTRAINT mytable_pkey;
ALTER TABLE

test=# ALTER TABLE mytable ADD PRIMARY KEY (id) USING INDEX TABLESPACE
mytbsp;
NOTICE:  ALTER TABLE / ADD PRIMARY KEY will create implicit index
mytable_pkey for table mytable
ALTER TABLE

test=# \d mytable
   Table laurenz.mytable
 Column |  Type   | Modifiers
+-+---
 id | integer | not null
 val| text|
Indexes:
mytable_pkey PRIMARY KEY, btree (id), tablespace mytbsp


Looks ok.

Yours,
Laurenz Albe

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Simon Riggs
On 9 May 2012 03:52, Bruce Momjian br...@momjian.us wrote:
 On Tue, May 08, 2012 at 10:29:31PM -0400, Aidan Van Dyk wrote:
 On Tue, May 8, 2012 at 10:09 PM, Bruce Momjian br...@momjian.us wrote:

  And then, I could envision (if it continues down this road):
    off
    local
    remote_accept
    remote_write
    remote_sync
    remote_apply (implies visible to new connections on the standby)
 
  Not saying all off these are necessarily worth it, but they are all
  the various stages of WAL processing on the remote...
 
  The _big_ problem with write is that we might need that someday to
  indicate some other kind of write, e.g. write to kernel, fsync to disk.

 Well, yes, but in the sequence of:
    remote_accept
    remote_write
    remote_sync

 it is much more clear...

 With a single remote_write, you can't tell just by itself it that is
 intended to  be it's a write *to* the remote, or it's a write *by*
 the remote.  But when combined with other terms, only one makes sense
 in all cases.

 Yep.  In fact, remote_write I thought meant a remote write, while it
 currently means a write to the remote.  I like remote_accept.

The naming is not arbitrary. -1 to changing it as suggested.

It is as Aidan says, a state between receive and fsync, normally
referred to as write.

Plus the word remote denotes it is on the standby, not the local master.

So both words have specific meaning, and IMHO clear meaning.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Bruce Momjian
On Wed, May 09, 2012 at 08:52:40AM +0100, Simon Riggs wrote:
  Well, yes, but in the sequence of:
     remote_accept
     remote_write
     remote_sync
 
  it is much more clear...
 
  With a single remote_write, you can't tell just by itself it that is
  intended to  be it's a write *to* the remote, or it's a write *by*
  the remote.  But when combined with other terms, only one makes sense
  in all cases.
 
  Yep.  In fact, remote_write I thought meant a remote write, while it
  currently means a write to the remote.  I like remote_accept.
 
 The naming is not arbitrary. -1 to changing it as suggested.
 
 It is as Aidan says, a state between receive and fsync, normally
 referred to as write.

Let me point out that our documentation says nothing about it being
written to the kernel --- it just says has received the commit record
of the transaction to memory.

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

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

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


[HACKERS] Can pg_trgm handle non-alphanumeric characters?

2012-05-09 Thread MauMau

Hello,

This question may be appropriate for pgsql-general, but let me ask here 
because the only relevant discussion seems to have been done on 
pgsql-hackers:


http://archives.postgresql.org/pgsql-hackers/2011-09/msg00169.php

Can pg_trgm support non-alphanumeric characters by undefining KEEPONLYALNUM 
in contrib/pg_trgm/trgm.h? If no, what kind of problems would happen?


Regards
MauMau



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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Robert Haas
On Wed, May 9, 2012 at 7:29 AM, Bruce Momjian br...@momjian.us wrote:
 Let me point out that our documentation says nothing about it being
 written to the kernel --- it just says has received the commit record
 of the transaction to memory.

Maybe remote_receive would be better.  If we're actually writing it
back to the kernel before acknowledging the commit, that seems like an
implementation defect more than anything else, since it does not -
AFAICS - provide any additional, useful guarantee.

Another thing I've been wondering is whether, perhaps, we ought to
keep synchronous_commit tri-valued: on/local/off, and have a separate
GUC for synchronous_replication_mode.  It's a bit arbitrary that on
happens to mean remote fsync rather than remote write/receive.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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


Re: [HACKERS] age(xid) on hot standby

2012-05-09 Thread Simon Riggs
On 9 May 2012 00:55, Simon Riggs si...@2ndquadrant.com wrote:
 On 8 May 2012 20:01, Peter Eisentraut pete...@gmx.net wrote:
 On ons, 2012-01-18 at 14:55 -0500, Tom Lane wrote:
 BTW, it strikes me that maybe the coding should work about like this:

       if (!TransactionIdIsValid(age_reference_xid))
       {
               age_reference_xid = GetTopTransactionIdIfAny();
               if (!TransactionIdIsValid(age_reference_xid))
                       age_reference_xid = ReadNewTransactionId();
       }
       ... use age_reference_xid to compute result ...

 and of course code somewhere to reset age_reference_xid at end of xact.

 How about this patch?

 I think we should fix this, but not with this exact patch.

 We should just use MyPgXact-xid
 rather than add more to the transaction path

 I'll simplify the patch and commit.

Committed, but forgot to give appropriate credit. Sorry about that.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Simon Riggs
On 9 May 2012 13:48, Robert Haas robertmh...@gmail.com wrote:
 On Wed, May 9, 2012 at 7:29 AM, Bruce Momjian br...@momjian.us wrote:
 Let me point out that our documentation says nothing about it being
 written to the kernel --- it just says has received the commit record
 of the transaction to memory.

 Maybe remote_receive would be better.  If we're actually writing it
 back to the kernel before acknowledging the commit, that seems like an
 implementation defect more than anything else, since it does not -
 AFAICS - provide any additional, useful guarantee.

It does provide an additional guarantee, but I accept you personally
may not find that useful.

If the docs don't describe it well enough, then we can change the docs.


 Another thing I've been wondering is whether, perhaps, we ought to
 keep synchronous_commit tri-valued: on/local/off, and have a separate
 GUC for synchronous_replication_mode.  It's a bit arbitrary that on
 happens to mean remote fsync rather than remote write/receive.

You mean the way it originally was? I would agree.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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


[HACKERS] pgstat wait timeout just got a lot more common on Windows

2012-05-09 Thread Tom Lane
Last night I changed the stats collector process to use
WaitLatchOrSocket instead of a periodic forced wakeup to see whether
the postmaster has died.  This morning I observe that several Windows
buildfarm members are showing regression test failures caused by
unexpected pgstat wait timeout warnings.  Everybody else is fine.

This suggests that there is something broken in the Windows
implementation of WaitLatchOrSocket.  I wonder whether it also
tells us something we did not know about the underlying cause of
those messages.  Not sure what though.  Ideas?  Can anyone who
knows Windows take another look at WaitLatchOrSocket?

regards, tom lane

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Bruce Momjian
fOn Wed, May 09, 2012 at 03:02:23PM +0100, Simon Riggs wrote:
 On 9 May 2012 13:48, Robert Haas robertmh...@gmail.com wrote:
  On Wed, May 9, 2012 at 7:29 AM, Bruce Momjian br...@momjian.us wrote:
  Let me point out that our documentation says nothing about it being
  written to the kernel --- it just says has received the commit record
  of the transaction to memory.
 
  Maybe remote_receive would be better.  If we're actually writing it
  back to the kernel before acknowledging the commit, that seems like an
  implementation defect more than anything else, since it does not -
  AFAICS - provide any additional, useful guarantee.
 
 It does provide an additional guarantee, but I accept you personally
 may not find that useful.

The guarantee is that if Postgres crashes, we don't lose any data, but
not if the OS crashes (right?) because that isn't clear now.

 If the docs don't describe it well enough, then we can change the docs.

Well, we should make that clear in the docs then.

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

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

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


Re: [HACKERS] age(xid) on hot standby

2012-05-09 Thread Tom Lane
Simon Riggs si...@2ndquadrant.com writes:
 We should just use MyPgXact-xid
 rather than add more to the transaction path
 
 I'll simplify the patch and commit.

 Committed, but forgot to give appropriate credit. Sorry about that.

This patch didn't fix things, it broke things.  The former guarantee
that age's reference point would hold still throughout a transaction
just disappeared.

What I read your previous suggestion to be was that age() would keep
local state and use inspection of the current VXID to tell if its
cache was stale.  That would keep the fix localized (which I agree
is a good idea) without losing the stability guarantee.

regards, tom lane

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



Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Robert Haas
On Wed, May 9, 2012 at 10:34 AM, Bruce Momjian br...@momjian.us wrote:
 It does provide an additional guarantee, but I accept you personally
 may not find that useful.

 The guarantee is that if Postgres crashes, we don't lose any data, but
 not if the OS crashes (right?) because that isn't clear now.

True, point taken.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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


Re: [HACKERS] age(xid) on hot standby

2012-05-09 Thread Simon Riggs
On 9 May 2012 15:34, Tom Lane t...@sss.pgh.pa.us wrote:
 Simon Riggs si...@2ndquadrant.com writes:
 We should just use MyPgXact-xid
 rather than add more to the transaction path

 I'll simplify the patch and commit.

 Committed, but forgot to give appropriate credit. Sorry about that.

 This patch didn't fix things, it broke things.  The former guarantee
 that age's reference point would hold still throughout a transaction
 just disappeared.

 What I read your previous suggestion to be was that age() would keep
 local state and use inspection of the current VXID to tell if its
 cache was stale.  That would keep the fix localized (which I agree
 is a good idea) without losing the stability guarantee.

Gotcha. Will fix.

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training  Services

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Robert Haas
On Wed, May 9, 2012 at 10:02 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Another thing I've been wondering is whether, perhaps, we ought to
 keep synchronous_commit tri-valued: on/local/off, and have a separate
 GUC for synchronous_replication_mode.  It's a bit arbitrary that on
 happens to mean remote fsync rather than remote write/receive.

 You mean the way it originally was? I would agree.

No.  The original design for sync rep had synchronous_commit with only
TWO values, on and off.  I think the design we eventually settled on,
with three values, was better, and I'm in favor of keeping it.
However, there might be some virtue in separating the knob that
controls whether we do sync rep from the knob that controls which kind
of sync rep we do.  I'm not sure.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Fujii Masao
On Thu, May 10, 2012 at 1:42 AM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, May 9, 2012 at 10:02 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Another thing I've been wondering is whether, perhaps, we ought to
 keep synchronous_commit tri-valued: on/local/off, and have a separate
 GUC for synchronous_replication_mode.  It's a bit arbitrary that on
 happens to mean remote fsync rather than remote write/receive.

 You mean the way it originally was? I would agree.

 No.  The original design for sync rep had synchronous_commit with only
 TWO values, on and off.  I think the design we eventually settled on,
 with three values, was better, and I'm in favor of keeping it.
 However, there might be some virtue in separating the knob that
 controls whether we do sync rep from the knob that controls which kind
 of sync rep we do.  I'm not sure.

At least there is one problem in separating the knob: a user can set
synchronous_commit to OFF and synchronous_replication_mode to ON,
which makes the transaction wait long for replication. In this setting,
because of asynchronous commit, WAL records cannot be written to the
local disk immediately, and which means that WAL records cannot be
sent immediately, so replication wait time would get long. AFAIR this is
the reason why we merged two parameters into one before.

Regards,

-- 
Fujii Masao

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


Re: [HACKERS] Can pg_trgm handle non-alphanumeric characters?

2012-05-09 Thread Fujii Masao
On Wed, May 9, 2012 at 9:10 PM, MauMau maumau...@gmail.com wrote:
 Hello,

 This question may be appropriate for pgsql-general, but let me ask here
 because the only relevant discussion seems to have been done on
 pgsql-hackers:

 http://archives.postgresql.org/pgsql-hackers/2011-09/msg00169.php

 Can pg_trgm support non-alphanumeric characters by undefining KEEPONLYALNUM
 in contrib/pg_trgm/trgm.h?

Yes unless I'm missing something.

Regards,

-- 
Fujii Masao

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


[HACKERS] libpq URL syntax vs SQLAlchemy

2012-05-09 Thread Peter Eisentraut
I have been reviewing how our new libpq URL syntax compares against
existing implementations of URL syntaxes in other drivers or
higher-level access libraries.  In the case of SQLAlchemy, there is an
incompatibility regarding how Unix-domain sockets are specified.

First, here is the documentation on that:
http://docs.sqlalchemy.org/en/latest/dialects/postgresql.html

The recommended way to access a server over a Unix-domain socket is to
leave off the host, as in:

postgresql://user:password@/dbname

In libpq, this is parsed as host='/dbname', no database.

To specify a socket path in SQLAlchemy, you use:

postgresql://user:password@/dbname?host=/var/lib/postgresql

This also works in libpq (bizarrely, perhaps, considering the previous
case).

This libpq behavior is a problem for several reasons:

- It's incompatible with a popular existing implementation.

- It violates RFC 3986, which doesn't allow slashes in the
  authority (host, port, user, password) part.

- As a consequence of this, URLs like this will be parsed differently
  (or will fail to be parsed) by existing URL parsing libraries (tried
  Perl URI and Python urllib, for instance).

- Moreover, if these libraries can't parse the URL, it might mean those
  drivers can't adopt that URL syntax.

- It's internally inconsistent, as shown above.

- In most places in PostgreSQL clients, no host means Unix-domain
  socket, but not here.

- It favors the case of non-default Unix-domain socket plus default
  database over default Unix-domain socket plus non-default database.

- It's not obvious how to get to the default Unix-domain socket at all.
  postgresql:///dbname doesn't work, but postgresql:///dbname?host=
  does.

I think this whole approach of using unescaped slashes in the host
part of the URL is going to cause lots of problems like this.  We should
consider one or more of:

- Requiring percent escapes

- Requiring specifying the socket path as a parameter, like in the
  above example

- Requiring some delimiters like for IPv6 addresses (which had the
  same problem of reusing a reserved character) (probably a bad idea,
  since we can't make existing URL parsing libraries understand
  this)



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


Re: [HACKERS] problem/bug in drop tablespace?

2012-05-09 Thread Michael Nolan
On 5/9/12, Albe Laurenz laurenz.a...@wien.gv.at wrote:

 I cannot reproduce this on 9.1.3:

Odd, I've tried it another two times, with similar results to my initial post.

Here's what I get starting with the point where I deleted the files in
the tablespace:

mytest=# select * from mytable;
select * from mytable;
ERROR:  could not open file
pg_tblspc/289477766/PG_9.1_201105231/289477763/289477785: No such
file or directory
mytest=# \d mytable
 Table public.mytable
Column  Type   Modifiers
-- --- -
id integer not null
valtext
Indexes:
mytable_pkey PRIMARY KEY, btree (id), tablespace mytblspc

mytest=# drop tablespace mytblspc;
drop tablespace mytblspc;
WARNING:  could not open directory
pg_tblspc/289477766/PG_9.1_201105231: No such file or directory
DROP TABLESPACE
Time: 16.460 ms
mytest=# \d mytable
 Table public.mytable
Column  Type   Modifiers
-- --- -
id integer not null
valtext
Indexes:
mytable_pkey PRIMARY KEY, btree (id)

mytest=# create tablespace mytblspc location '/home/postgres/mytb';
create tablespace mytblspc location '/home/postgres/mytb';
CREATE TABLESPACE
Time: 42.396 ms
mytest=# \d mytable
 Table public.mytable
Column  Type   Modifiers
-- --- -
id integer not null
valtext
Indexes:
mytable_pkey PRIMARY KEY, btree (id)

mytest=# reindex table mytable;
reindex table mytable;
REINDEX
Time: 112.981 ms

mytest=# \d mytable
 Table public.mytable
Column  Type   Modifiers
-- --- -
id integer not null
valtext
Indexes:
mytable_pkey PRIMARY KEY, btree (id)

Here's what's in the mytb directory now:

[postgres@romaine PG_9.1_201105231]$ ls -lR
:
total 4
drwx--. 2 postgres postgres 4096 May  9 13:22 289477763

./289477763:
total 16
-rw---. 1 postgres postgres 16384 May  9 13:22 289477790


It appears that the index has been rebuilt in the mytblspc tablespace,
though \d mytable does not show that.

I get the same results whether I rebuild the specific index as you did
or reindex the table, as I did.

I'm running on 9.1.3 built from the source code, not a distribution.
--
Mike Nolan

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


Re: [HACKERS] [COMMITTERS] pgsql: Split contrib documentation into extensions and programs

2012-05-09 Thread Alvaro Herrera

Excerpts from Peter Eisentraut's message of mié may 09 13:54:53 -0400 2012:
 Split contrib documentation into extensions and programs
 
 Create separate appendixes for contrib extensions and other server
 plugins on the one hand, and utility programs on the other.  Recast
 the documentation of the latter as refentries, so that man pages are
 generated.

I noticed that the pgupgrade manpage doesn't have the titles of each
step in Usage.  Not sure this is a serious problem, but it's different
from the HTML at any rate.

-- 
Álvaro Herrera alvhe...@commandprompt.com
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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


Re: [HACKERS] problem/bug in drop tablespace?

2012-05-09 Thread Michael Nolan
I see one potential difference between your results and mine.

When I rebuild the tablespace, I wind up with the same filename/OID as
before, I'm not sure you do.
--
Mike Nolan

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


[HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway
I'm working on an upgrade of PostgreSQL embedded in a product from
version 8.1.x to 9.1.x. One particular PL/pgSQL function is giving us an
issue as there seems to be a rather severe regression in memory usage --
a query that finishes in 8.1 causes an out of memory exception on 9.1.

Using the same data on the same machine I see memory use stay steady at
a reasonably low value on the 8.1 installation but steadily climb on 9.1
(I watched it go over 2 GB and canceled the query -- the production
machines are 32 bit)

The attached standalone script seems to reproduce the effect. On 8.1
memory usage remains steady and low, on 9.1 I watched it climb past 1.1
GB and canceled the query.

I suspect the append node to be the culprit because if I skip the UNION
ALL, i.e. if I use one generate_series with 20 million rows instead of
10 with 2 million each, then I do not see the memory leak. The real
function is actually selecting over many inherited tables (i.e. a
partitioned table).

Thoughts?

Thanks,

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting,  24x7 Support
CREATE OR REPLACE FUNCTION selectDetailed ()
RETURNS setof record AS $_$
DECLARE
result_rec  record;
sql text;
BEGIN
sql := $$
   --EXPLAIN
   SELECT * FROM (
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,200) as t(id)) ss
   UNION ALL
   SELECT now(),
  ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
  'a'::text, ''::text,
  'cc'::text,
  'ddd'::text,
  'eee'::text,
  

Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Josh Berkus

 The naming is not arbitrary. -1 to changing it as suggested.
 
 It is as Aidan says, a state between receive and fsync, normally
 referred to as write.
 
 Plus the word remote denotes it is on the standby, not the local master.
 
 So both words have specific meaning, and IMHO clear meaning.

Clear to a postgres hacker, maybe.  Not at *all* clear to our general users.

The natural assumption is that remote write means that it's written to
disk on the remote. Which is not what it means.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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


[HACKERS] bgwriter idle-mode behavior (was Re: Latch for the WAL writer)

2012-05-09 Thread Tom Lane
I wrote:
 I believe the bgwriter code needs to be fixed similarly to the way
 I changed the walwriter patch, namely that there needs to be a separate
 flag (not the latch's isSet state) advertising whether the bgwriter is
 currently in hibernation mode.

After further study of the bgwriter hibernation patch (commit
6d90eaaa89a007e0d365f49d6436f35d2392cfeb), I think that my worries about
race conditions in the use of the bgwriter's latch are really the least
of its problems.  BgBufferSync embodies a rather carefully tuned
feedback control loop, and I think these changes broke it.  In the first
place, that feedback loop is built on the assumption that BgBufferSync
is executed at fixed intervals, which isn't true anymore; and in the
second place, the loop is not driven so much by the rate of buffers
being dirtied as it is by the rate of buffers being allocated.  To be
concrete, if there is a constant but slow rate of buffer consumption,
the bgwriter will eventually lap the strategy scan and then stay there,
resulting in BgBufferSync returning true every time even though actually
the system is doing things.  This results in bgwriter.c deciding it's in
hibernation mode, whereupon we have a scenario where backends will be
signaling it all the time.  The way BgWriterNap is coded, that means
BgBufferSync is not executed at a fixed BgWriterDelay interval, but at
variable intervals from BgWriterDelay up to BGWRITER_HIBERNATE_MS, which
pretty much destroys the predictability of the feedback loop.

My proposal for fixing this is that

(1) BgBufferSync should return true (OK to hibernate) only if it's
lapped the strategy scan *and* recent_alloc is zero, meaning no new
buffers were allocated anywhere since last time.

(2) We should remove the bgwriter wakening calls from MarkBufferDirty
and SetBufferCommitInfoNeedsSave, and instead place one in buffer
allocation.

(3) The bottom-of-loop logic in bgwriter should be along the lines of

rc = WaitLatch(..., BgWriterDelay);
if (rc == WL_TIMEOUT  can_hibernate)
{
set global flag to tell backends to kick bgwriter
if they allocate a buffer;
WaitLatch(..., BGWRITER_HIBERNATE_MS);
clear global flag;
}

In comparison to the existing code, this method guarantees immediate
response to any signal (latch-setting event), and it ensures that
if we extend the normal sleep time for the bgwriter, the extra sleep
covers only an interval in which no new buffer allocations happened.
That provision seems to me to justify pretending that that interval
simply didn't exist for the purposes of the feedback control loop,
which allows us to not have to rethink how that loop works.

Comments?

regards, tom lane

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Bruce Momjian
On Wed, May 09, 2012 at 02:23:30PM -0700, Josh Berkus wrote:
 
  The naming is not arbitrary. -1 to changing it as suggested.
  
  It is as Aidan says, a state between receive and fsync, normally
  referred to as write.
  
  Plus the word remote denotes it is on the standby, not the local master.
  
  So both words have specific meaning, and IMHO clear meaning.
 
 Clear to a postgres hacker, maybe.  Not at *all* clear to our general users.
 
 The natural assumption is that remote write means that it's written to
 disk on the remote. Which is not what it means.

Right, and if we are wrapping beta tomorrow, it would be good for us to
decide soon.  We can always change it after beta, but sooner is better.

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

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

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Bruce Momjian
On Wed, May 09, 2012 at 05:37:09PM -0400, Bruce Momjian wrote:
 On Wed, May 09, 2012 at 02:23:30PM -0700, Josh Berkus wrote:
  
   The naming is not arbitrary. -1 to changing it as suggested.
   
   It is as Aidan says, a state between receive and fsync, normally
   referred to as write.
   
   Plus the word remote denotes it is on the standby, not the local master.
   
   So both words have specific meaning, and IMHO clear meaning.
  
  Clear to a postgres hacker, maybe.  Not at *all* clear to our general users.
  
  The natural assumption is that remote write means that it's written to
  disk on the remote. Which is not what it means.
 
 Right, and if we are wrapping beta tomorrow, it would be good for us to
 decide soon.  We can always change it after beta, but sooner is better.

And I will take the blame for brining it up so near beta --- I only
realized when writing the release notes (which should be announced in a
few hours).

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

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

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


Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Tom Lane
Joe Conway m...@joeconway.com writes:
 I'm working on an upgrade of PostgreSQL embedded in a product from
 version 8.1.x to 9.1.x. One particular PL/pgSQL function is giving us an
 issue as there seems to be a rather severe regression in memory usage --
 a query that finishes in 8.1 causes an out of memory exception on 9.1.

I see no memory leak at all in this example, either in HEAD or 9.1
branch tip.  Perhaps whatever you're seeing is an already-fixed bug?

Another likely theory is that you've changed settings from the 8.1
installation.  I would expect this example to eat about 10 times
work_mem (due to one tuplestore for each generate_series invocation),
and that's more or less what I see happening here.  A large work_mem
could look like a leak, but it isn't.

If you need further help in debugging, try launching the postmaster
under a fairly restrictive memory ulimit, so that the backend will get a
malloc failure before it starts to swap too badly.  The memory map it
will then print on stderr should point to where the memory is going.

regards, tom lane

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


Re: [HACKERS] Can pg_trgm handle non-alphanumeric characters?

2012-05-09 Thread MauMau

From: Fujii Masao masao.fu...@gmail.com

On Wed, May 9, 2012 at 9:10 PM, MauMau maumau...@gmail.com wrote:

This question may be appropriate for pgsql-general, but let me ask here
because the only relevant discussion seems to have been done on
pgsql-hackers:

http://archives.postgresql.org/pgsql-hackers/2011-09/msg00169.php

Can pg_trgm support non-alphanumeric characters by undefining 
KEEPONLYALNUM

in contrib/pg_trgm/trgm.h?


Yes unless I'm missing something.


Then, does it make sense to remove #define KEEPONLYALNUM in 9.1.4? Would 
it cause any problems? If no, I wish that, because it eliminates the need to 
do the removal every time the users applies minor releases.



Regards
MauMau



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


Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway
On 05/09/2012 03:08 PM, Tom Lane wrote:
 I see no memory leak at all in this example, either in HEAD or 9.1
 branch tip.  Perhaps whatever you're seeing is an already-fixed bug?
 
 Another likely theory is that you've changed settings from the 8.1
 installation.  I would expect this example to eat about 10 times
 work_mem (due to one tuplestore for each generate_series invocation),
 and that's more or less what I see happening here.  A large work_mem
 could look like a leak, but it isn't.

Good call -- of course that just means my contrived example fails to
duplicate the real issue :-(
In the real example, even with work_mem = 1 MB I see the same behavior
on 9.1.

 If you need further help in debugging, try launching the postmaster
 under a fairly restrictive memory ulimit, so that the backend will get a
 malloc failure before it starts to swap too badly.  The memory map it
 will then print on stderr should point to where the memory is going.

Thanks -- will try that.

Joe


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting,  24x7 Support

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


Re: [HACKERS] Can pg_trgm handle non-alphanumeric characters?

2012-05-09 Thread Euler Taveira
On 09-05-2012 19:17, MauMau wrote:
 Then, does it make sense to remove #define KEEPONLYALNUM in 9.1.4? Would it
 cause any problems? If no, I wish that, because it eliminates the need to do
 the removal every time the users applies minor releases.
 
If you do so, you'll break minor versions. IMHO the default is the desirable
behavior for almost all use cases (you are the first one that complain about
it). Maybe in the future, we should be able to flip this flag without
rebuilding binaries.


-- 
   Euler Taveira de Oliveira - Timbira   http://www.timbira.com.br/
   PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Fujii Masao
On Thu, May 10, 2012 at 6:23 AM, Josh Berkus j...@agliodbs.com wrote:

 The naming is not arbitrary. -1 to changing it as suggested.

 It is as Aidan says, a state between receive and fsync, normally
 referred to as write.

 Plus the word remote denotes it is on the standby, not the local master.

 So both words have specific meaning, and IMHO clear meaning.

 Clear to a postgres hacker, maybe.  Not at *all* clear to our general users.

If so, we should also rename the column write_location in pg_stat_replication?
I named remote_write (originally write) after that column. And, in
remote_write,
internally the master waits for replication until the wait LSN has
reached write_location.

Regards,

-- 
Fujii Masao

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


Re: [HACKERS] Can pg_trgm handle non-alphanumeric characters?

2012-05-09 Thread Fujii Masao
On Thu, May 10, 2012 at 8:18 AM, Euler Taveira eu...@timbira.com wrote:
 On 09-05-2012 19:17, MauMau wrote:
 Then, does it make sense to remove #define KEEPONLYALNUM in 9.1.4? Would it
 cause any problems? If no, I wish that, because it eliminates the need to do
 the removal every time the users applies minor releases.

 If you do so, you'll break minor versions.

Right. And removing KEEPONLYALNUM is a feature change rather than bug fix,
so that should be proposed during major version development cycle.

 IMHO the default is the desirable
 behavior for almost all use cases (you are the first one that complain about
 it).

Really? I was thinking non-English users (including me) basicaly would not be
satisfied with the default because they cannot use pg_trgm for N-gram full text
search of non-English text. Though I agree some users would prefer the default.

 Maybe in the future, we should be able to flip this flag without
 rebuilding binaries.

Agreed.

Regards,

-- 
Fujii Masao

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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Josh Berkus

 If so, we should also rename the column write_location in 
 pg_stat_replication?

Now that you bring it up, probably.  Although not necessarily for 9.2.

 I named remote_write (originally write) after that column. And, in
 remote_write,
 internally the master waits for replication until the wait LSN has
 reached write_location.

Yeah, I get what it means.  But I'm not the person I'm worried about
being confused.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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


Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway
On 05/09/2012 03:36 PM, Joe Conway wrote:
 Good call -- of course that just means my contrived example fails to
 duplicate the real issue :-(
 In the real example, even with work_mem = 1 MB I see the same behavior
 on 9.1.

OK, new script. This more faithfully represents the real life scenario,
and reproduces the issue on HEAD with out-of-the-box config settings,
versus 8.1 which completes the query having never exceeded a very modest
memory usage:

---
On pg 8.1 with out of the box config:
VIRT  RES  SHR
199m  11m 3032
---
On pg head with out of the box config:
VIRT  RES  SHR
1671m 1.5g  16m
---

I have not tried your ulimit suggestion yet but will do that next.

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting,  24x7 Support
CREATE OR REPLACE FUNCTION create_parts ()
RETURNS text AS $_$
DECLARE
i int;
sql   text;
BEGIN
   sql := 'CREATE TABLE foo (
   ts timestamp with time zone,
   d1 bigint, d2 bigint, d3 bigint,
   d4 bigint, d5 bigint, d6 bigint,
   s1 text, s2 text, s3 text,
   s4 text, s5 text, s6 text)';
EXECUTE sql;
FOR i IN 1..100 LOOP
  sql := 'CREATE TABLE foo_' || i || ' () INHERITS (foo)';
  EXECUTE sql;
  sql := $$
   INSERT INTO foo_$$ || i || $$ SELECT now(),
   ss.id, ss.id, ss.id, ss.id, ss.id, ss.id, 
   'a'::text, ''::text,
   'cc'::text,
   'ddd'::text,
   'eee'::text,
   'fff'::text
   FROM (SELECT id::bigint FROM generate_series(1,20) as t(id)) ss
 $$;
  EXECUTE sql;
END LOOP;
RETURN 'OK';
END;
$_$ LANGUAGE plpgsql VOLATILE;

SELECT create_parts ();

CREATE OR REPLACE FUNCTION selectDetailed ()
RETURNS setof foo AS $_$
DECLARE
result_rec  record;
sql text;
BEGIN
sql := 'SELECT * FROM foo';
FOR result_rec IN EXECUTE sql LOOP
RETURN NEXT result_rec;
END LOOP;
RETURN;
END;
$_$ LANGUAGE plpgsql STABLE;

select count(*) from selectDetailed();




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


Re: [HACKERS] synchronous_commit and remote_write

2012-05-09 Thread Jeff Janes
On Wed, May 9, 2012 at 8:43 AM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, May 9, 2012 at 10:34 AM, Bruce Momjian br...@momjian.us wrote:
 It does provide an additional guarantee, but I accept you personally
 may not find that useful.

 The guarantee is that if Postgres crashes, we don't lose any data, but
 not if the OS crashes (right?) because that isn't clear now.

 True, point taken.

Back when synchronous_commit only had 2 values, I thought it should
have had 3.  The guarantee of losing transactions only on an OS crash,
and not on a Postgres server crash, seems quite valuable (especially
if you are playing around with custom extensions that might crash
Postgres upon custom bugs).  And the costs seem minimal.  If the
kernel is so constipated that even simple writes are blocking, it
seems you are hosed regardless of where those writes are occurring.

Cheers,

Jeff

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


Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway
On 05/09/2012 05:06 PM, Joe Conway wrote:
 OK, new script. This more faithfully represents the real life scenario,
 and reproduces the issue on HEAD with out-of-the-box config settings,
 versus 8.1 which completes the query having never exceeded a very modest
 memory usage:
 
 ---
 On pg 8.1 with out of the box config:
 VIRT  RES  SHR
 199m  11m 3032
 ---
 On pg head with out of the box config:
 VIRT  RES  SHR
 1671m 1.5g  16m
 ---

The attached one-liner seems to plug up the majority (although not quite
all) of the leakage.

do_convert_tuple() is allocating a new tuple for every row in the loop
and exec_stmt_return_next() is leaking it.

The query now finishes successfully. On pg head with attached patch and
out of the box config:
VIRT  RES  SHR
196m  35m  31m

This look sane/correct?

Joe

-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting,  24x7 Support
diff --git a/src/pl/plpgsql/src/pl_exec.c b/src/pl/plpgsql/src/pl_exec.c
index de1aece..24346c2 100644
*** a/src/pl/plpgsql/src/pl_exec.c
--- b/src/pl/plpgsql/src/pl_exec.c
*** exec_stmt_return_next(PLpgSQL_execstate 
*** 2469,2474 
--- 2469,2475 
  	{
  		tuple = do_convert_tuple(tuple, tupmap);
  		free_conversion_map(tupmap);
+ 		free_tuple = true;
  	}
  }
  break;

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


Re: [HACKERS] unite recovery.conf and postgresql.conf

2012-05-09 Thread Josh Berkus
All,

I'll point out that this patch got sandbagged to death, and never made
it into 9.2.  So, for 9.2 replication is just as hard to configure and
manage as it was in 9.1.  Are we going to fix it in 9.3, or not?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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


[HACKERS] Draft release notes complete

2012-05-09 Thread Bruce Momjian
I have completed my draft of the 9.2 release notes, and committed it to
git.  I am waiting for our development docs to build, but after 40
minutes, I am still waiting:


http://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=guaibasaurusdt=lateststg=make-doc

(Why is there no time zone shown in the date/time at the top?)   I think
it will eventually show up here:

http://www.postgresql.org/docs/devel/static/release-9-2.html

My private build is now online:

http://momjian.us/tmp/pgsql/release-9-2.html

I tested creation of the HISTORY file so Tom shouldn't need to fix
missing markup tomorrow.  :-)

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

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

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


Re: [HACKERS] unite recovery.conf and postgresql.conf

2012-05-09 Thread Bruce Momjian
On Wed, May 09, 2012 at 08:07:52PM -0700, Josh Berkus wrote:
 All,
 
 I'll point out that this patch got sandbagged to death, and never made
 it into 9.2.  So, for 9.2 replication is just as hard to configure and
 manage as it was in 9.1.  Are we going to fix it in 9.3, or not?

Greg Smith was going to allow for files in configuration directories,
and that was somehow going to make it easier to manage the configuration
files and remove recovery.conf.  I don't think Greg did it;  I didn't
see it in the release notes I just wrote.

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

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

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


Re: [HACKERS] unite recovery.conf and postgresql.conf

2012-05-09 Thread Greg Smith

On 05/09/2012 11:15 PM, Bruce Momjian wrote:

On Wed, May 09, 2012 at 08:07:52PM -0700, Josh Berkus wrote:

All,

I'll point out that this patch got sandbagged to death, and never made
it into 9.2.  So, for 9.2 replication is just as hard to configure and
manage as it was in 9.1.  Are we going to fix it in 9.3, or not?


Greg Smith was going to allow for files in configuration directories,
and that was somehow going to make it easier to manage the configuration
files and remove recovery.conf.  I don't think Greg did it;  I didn't
see it in the release notes I just wrote.


That was actually submitted back in November, and rightly kicked back as 
needing more work.  I would have updated it and resubmitted if that was 
the only blocker.  But by the time January rolled around, it was already 
obvious that the last CommitFest was going into overtime.  Didn't seem 
like a great time to add a disruptive change like this one into the mix.


I expect to revisit config directories before the first 9.3 CF, it will 
help multiple things I'd like to see happen.  Then we can circle back to 
the main unification job with a fairly clear path forward from there.


--
Greg Smith   2ndQuadrant USg...@2ndquadrant.com   Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

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


Re: [HACKERS] Draft release notes complete

2012-05-09 Thread Bruce Momjian
On Wed, May 09, 2012 at 11:11:02PM -0400, Bruce Momjian wrote:
 (Why is there no time zone shown in the date/time at the top?)   I think
 it will eventually show up here:
 
   http://www.postgresql.org/docs/devel/static/release-9-2.html

The docs finally built 90 minutes after my commit, and the URL above is
now working.  (Does it always take this long to update?)

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

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

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


Re: [HACKERS] unite recovery.conf and postgresql.conf

2012-05-09 Thread Josh Berkus

 I expect to revisit config directories before the first 9.3 CF, it will
 help multiple things I'd like to see happen.  Then we can circle back to
 the main unification job with a fairly clear path forward from there.

Yeah, let's discuss this next week.  Easier configuration is one
demand I'm hearing from developers in general, and I don't think that's
nearly as hard a feature as, say, parallel query.  We can do it.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

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


Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Tom Lane
Joe Conway m...@joeconway.com writes:
 The attached one-liner seems to plug up the majority (although not quite
 all) of the leakage.

Looks sane to me.  Are you planning to look for the remaining leakage?

regards, tom lane

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


Re: [HACKERS] Draft release notes complete

2012-05-09 Thread Stefan Kaltenbrunner
On 05/10/2012 06:33 AM, Bruce Momjian wrote:
 On Wed, May 09, 2012 at 11:11:02PM -0400, Bruce Momjian wrote:
 (Why is there no time zone shown in the date/time at the top?)   I think
 it will eventually show up here:

  http://www.postgresql.org/docs/devel/static/release-9-2.html
 
 The docs finally built 90 minutes after my commit, and the URL above is
 now working.  (Does it always take this long to update?)

the developer docs builds are a byproduct of the snaptshot generation
concept on borka.postgresql.org. For each snapshot we are doing a
complete buildfarm-run to verify the basic integrity of the current code
and only if that one succeeds we will generate a snapshot-tarball AND
upload the docs to the main website.
This whole process is not triggered by a commit but simply running on a
fixed every 4 hours cycle.


Stefan

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


Re: [HACKERS] Draft release notes complete

2012-05-09 Thread Bruce Momjian
On Thu, May 10, 2012 at 07:02:43AM +0200, Stefan Kaltenbrunner wrote:
 On 05/10/2012 06:33 AM, Bruce Momjian wrote:
  On Wed, May 09, 2012 at 11:11:02PM -0400, Bruce Momjian wrote:
  (Why is there no time zone shown in the date/time at the top?)   I think
  it will eventually show up here:
 
 http://www.postgresql.org/docs/devel/static/release-9-2.html
  
  The docs finally built 90 minutes after my commit, and the URL above is
  now working.  (Does it always take this long to update?)
 
 the developer docs builds are a byproduct of the snaptshot generation
 concept on borka.postgresql.org. For each snapshot we are doing a
 complete buildfarm-run to verify the basic integrity of the current code
 and only if that one succeeds we will generate a snapshot-tarball AND
 upload the docs to the main website.
 This whole process is not triggered by a commit but simply running on a
 fixed every 4 hours cycle.

OK, good to know.  Sometimes I need a quick build that I can show
people, so I will just use my personal URL for those cases.

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

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

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


[HACKERS] psql: server version check for \dO

2012-05-09 Thread Josh Kupershmidt
Hi all,

I think psql's \dO command is missing the server version check which
similar commands such as \dx use. Right now \dO errors out with:

test=# \dO
ERROR:  relation pg_catalog.pg_collation does not exist

when talking to servers older than 9.1, which don't have pg_collation.
Simple patch for listCollations() attached.

Josh
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
new file mode 100644
index 8dfb570..2cfacd3
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*** listCollations(const char *pattern, bool
*** 3061,3066 
--- 3061,3073 
  	printQueryOpt myopt = pset.popt;
  	static const bool translate_columns[] = {false, false, false, false, false};
  
+ 	if (pset.sversion  90100)
+ 	{
+ 		fprintf(stderr, _(The server (version %d.%d) does not support collations.\n),
+ pset.sversion / 1, (pset.sversion / 100) % 100);
+ 		return true;
+ 	}
+ 
  	initPQExpBuffer(buf);
  
  	printfPQExpBuffer(buf,

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


Re: [HACKERS] memory leak regression 9.1 versus 8.1

2012-05-09 Thread Joe Conway
On 05/09/2012 10:01 PM, Tom Lane wrote:
 Joe Conway m...@joeconway.com writes:
 The attached one-liner seems to plug up the majority (although not quite
 all) of the leakage.
 
 Looks sane to me.  Are you planning to look for the remaining leakage?

Actually, now I'm not so sure there really are any other leaks. The last
test I ran, on 9.1 with the original data and plpgsql function, grew to:

  VIRT  RES  SHR
  540m 327m 267m

but then stabilized there through the end of the query, which
successfully returned:

  count
--
 28847766
(1 row)

This was with:

report_log=# show shared_buffers;
 shared_buffers

 256MB
(1 row)

report_log=# show work_mem;
 work_mem
--
 16MB
(1 row)

So I think those memory usage numbers look reasonable.

The bug appears to go back through 8.4 -- kind of surprising no one has
complained before.

Joe


-- 
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting,  24x7 Support

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


Re: [HACKERS] Draft release notes complete

2012-05-09 Thread Erik Rijkers
On Thu, May 10, 2012 06:33, Bruce Momjian wrote:
 On Wed, May 09, 2012 at 11:11:02PM -0400, Bruce Momjian wrote:

  http://www.postgresql.org/docs/devel/static/release-9-2.html


To E.1.2.5. Monitoring should be added:

Rename pg_stat_activity.current_query to query (Magnus Hagander)



And perhaps (same paragraph):

The previous query values are preserved, allowing for enhanced analysis.

would be clearer as:

The last query values are preserved, allowing for enhanced analysis.




Erik Rijkers






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


Re: [HACKERS] Draft release notes complete

2012-05-09 Thread Tom Lane
Bruce Momjian br...@momjian.us writes:
 The docs finally built 90 minutes after my commit, and the URL above is
 now working.  (Does it always take this long to update?)

I believe the new implementation of that stuff is that the devel docs
are built whenever the buildfarm member guaibasaurus runs for HEAD,
which it seems to do on an hourly schedule.  This is definitely not as
fast-responding as Peter's former custom script, but I'm not sure if
it's worth thinking of another way.

regards, tom lane

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


Re: [HACKERS] Draft release notes complete

2012-05-09 Thread Robert Haas
On Wed, May 9, 2012 at 11:11 PM, Bruce Momjian br...@momjian.us wrote:
 I have completed my draft of the 9.2 release notes, and committed it to
 git.

Extra parens:
Remove the spclocation field from pg_tablespace (Magnus Hagander, Tom Lane))
Reduce overhead of creating virtual transaction id locks ((Robert
Haas, Jeff Davis)

The antecedent of these is unclear:
Allow backends to detect postmaster death via a pipe read failure,
rather than polling (Peter Geoghegan, Heikki Linnakangas)
These are internally called latches.

Missing comma:
Cancel queries if clients get disconnected (Florian Pflug Greg Jaskiewicz)

You mean effect:
Such casts have no affect.

I think all three of these are the same thing:
Avoid table and index rebuilds when NUMERIC, VARBIT, and temporal
columns are changed in compatible ways (Noah Misch)
Reduce need to rebuild indexes for various ALTER TABLE operations
(Noah Misch) DUPLICATE?
Avoid index rebuilds for no-rewrite ALTER TABLE / ALTER TYPE (Noah Misch)

This feature wasn't committed at all:
Parallel pg_dump (Robert Haas, Joachim Wieland) DETAILS?

Yes, this is still true:
This is currently unused. STILL TRUE?

As a general comment, I think that your new policy of crediting the
reviewer on every feature except when that reviewer is also a
committer has produced a horrific mess.  Just to pick one of many
examples, consider this item:

Add a security_barrier option for views (KaiGai Kohei, Noah Misch)

Here is what the commit message says:

Patch by KaiGai Kohei; original problem report by Heikki Linnakangas
(in October 2009!).  Review (in earlier versions) by Noah Misch and
others.  Design advice by Tom Lane and myself.  Further review and
cleanup by me.

So there are four people mentioned in this commit message, and you've
picked out two of them to credit, not on the basis of who did the most
work, but rather on the basis of which ones happen to not be
committers.  The result is that, as I read through these release
notes, one gets what I believe to be a very misleading notion of who
developed which features.  I don't object to not being credited on
this one, but I don't think it makes sense to credit Noah and NOT
credit me.  As you have it, people who did little more than say yep,
looks fine to me are credited almost equally with the people who
wrote the code, while a committer who heavily revised the patch may
not be mentioned at all, or sometimes (seemingly at random) they are.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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