[HACKERS] DatumGetInetP buggy

2011-11-08 Thread Boszormenyi Zoltan
Hi,

I wanted to do some transformation on an inet value from
an SPI-using function. The inet Datum passed from SPI_getbinval()
to the values array in heap_form_tuple() obviously gives good data
to the frontend. When I use DatumGetInetP() on the Datum,
the structure passed to me is corrupt:

zozo=# select * from inet_test() as (id integer, i1 inet, i2 inet);
NOTICE:  i1 family=CORRUPTED
NOTICE:  i1 family=CORRUPTED
NOTICE:  i1 family=CORRUPTED
 id | i1  |  i2  
+-+---
  1 | 192.168.0.1 | 192.168.0.101
  2 | 192.168.0.2 | 192.168.0.102
  3 | 192.168.0.3 | 192.168.0.103
(3 rows)

I looked at utils/inet.h and DatumGetInetP() uses PG_DETOAST_DATUM_PACKED().
fmgr.h warns about PG_DETOAST_DATUM_PACKED() that it may give you
an unaligned pointer. Indeed, using PG_DETOAST_DATUM() instead of the
_PACKED variant on the Datum give me a well formed inet structure:

zozo=# select * from inet_test() as (id integer, i1 inet, i2 inet);
NOTICE:  i1 family=AF_INET
NOTICE:  i1 netmask=32 bits
NOTICE:  i1 address=192.168.0.1
NOTICE:  i1 family=AF_INET
NOTICE:  i1 netmask=32 bits
NOTICE:  i1 address=192.168.0.2
NOTICE:  i1 family=AF_INET
NOTICE:  i1 netmask=32 bits
NOTICE:  i1 address=192.168.0.3
 id | i1  |  i2  
+-+---
  1 | 192.168.0.1 | 192.168.0.101
  2 | 192.168.0.2 | 192.168.0.102
  3 | 192.168.0.3 | 192.168.0.103
(3 rows)

System is Fedora 16/x86_64, PostgreSQL 9.1.1 as provided by the OS.
The same error occurs on PostgreSQL 9.0.4 on another system
which is also Linux/x86_64

Example code is attached, the tables used by the code are:

create table t1 (id serial primary key, i1 inet);
create table t2 (id serial primary key, id2 integer references t1(id), i2 inet);
insert into t1 (i1) values ('192.168.0.1');
insert into t1 (i1) values ('192.168.0.2');
insert into t1 (i1) values ('192.168.0.3');
insert into t2 (id2, i2) values (1, '192.168.0.101');
insert into t2 (id2, i2) values (2, '192.168.0.102');
insert into t2 (id2, i2) values (3, '192.168.0.103');

Best regards,
Zoltán Böszörményi

-- 
--
Zoltán Böszörményi
Cybertec Schönig  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/



inet-test.tgz
Description: application/compressed-tar

-- 
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] DatumGetInetP buggy

2011-11-08 Thread Heikki Linnakangas

On 08.11.2011 11:22, Boszormenyi Zoltan wrote:

Hi,

I wanted to do some transformation on an inet value from
an SPI-using function. The inet Datum passed from SPI_getbinval()
to the values array in heap_form_tuple() obviously gives good data
to the frontend. When I use DatumGetInetP() on the Datum,
the structure passed to me is corrupt:

zozo=# select * from inet_test() as (id integer, i1 inet, i2 inet);
NOTICE:  i1 family=CORRUPTED
NOTICE:  i1 family=CORRUPTED
NOTICE:  i1 family=CORRUPTED
  id | i1  |  i2
+-+---
   1 | 192.168.0.1 | 192.168.0.101
   2 | 192.168.0.2 | 192.168.0.102
   3 | 192.168.0.3 | 192.168.0.103
(3 rows)

I looked at utils/inet.h and DatumGetInetP() uses PG_DETOAST_DATUM_PACKED().
fmgr.h warns about PG_DETOAST_DATUM_PACKED() that it may give you
an unaligned pointer. Indeed, using PG_DETOAST_DATUM() instead of the
_PACKED variant on the Datum give me a well formed inet structure:


Hmm, it seems to be intentional, but I agree it's very much contrary to 
the usual convention that DatumGetXXXP() returns a detoasted and 
depacked datum. I think we should change it. I propose the attached 
patch. It changes DatumGetInetP() to do PG_DETOAST_DATUM(), and adds new 
DatumGetInetPP() macro to return the packed version. I also moved the 
access macros like ip_family() from network.c to inet.h, so that they're 
available for whoever wants to look at the fields without having to depack.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com
diff --git a/src/backend/utils/adt/network.c b/src/backend/utils/adt/network.c
index 9aca1cc..a276d04 100644
--- a/src/backend/utils/adt/network.c
+++ b/src/backend/utils/adt/network.c
@@ -29,37 +29,6 @@ static int	ip_addrsize(inet *inetptr);
 static inet *internal_inetpl(inet *ip, int64 addend);
 
 /*
- *	Access macros.	We use VARDATA_ANY so that we can process short-header
- *	varlena values without detoasting them.  This requires a trick:
- *	VARDATA_ANY assumes the varlena header is already filled in, which is
- *	not the case when constructing a new value (until SET_INET_VARSIZE is
- *	called, which we typically can't do till the end).  Therefore, we
- *	always initialize the newly-allocated value to zeroes (using palloc0).
- *	A zero length word will look like the not-1-byte case to VARDATA_ANY,
- *	and so we correctly construct an uncompressed value.
- *
- *	Note that ip_maxbits() and SET_INET_VARSIZE() require
- *	the family field to be set correctly.
- */
-
-#define ip_family(inetptr) \
-	(((inet_struct *) VARDATA_ANY(inetptr))-family)
-
-#define ip_bits(inetptr) \
-	(((inet_struct *) VARDATA_ANY(inetptr))-bits)
-
-#define ip_addr(inetptr) \
-	(((inet_struct *) VARDATA_ANY(inetptr))-ipaddr)
-
-#define ip_maxbits(inetptr) \
-	(ip_family(inetptr) == PGSQL_AF_INET ? 32 : 128)
-
-#define SET_INET_VARSIZE(dst) \
-	SET_VARSIZE(dst, VARHDRSZ + offsetof(inet_struct, ipaddr) + \
-ip_addrsize(dst))
-
-
-/*
  * Return the number of bytes of address storage needed for this data type.
  */
 static int
@@ -907,7 +876,7 @@ convert_network_to_scalar(Datum value, Oid typid)
 		case INETOID:
 		case CIDROID:
 			{
-inet	   *ip = DatumGetInetP(value);
+inet	   *ip = DatumGetInetPP(value);
 int			len;
 double		res;
 int			i;
diff --git a/src/include/utils/inet.h b/src/include/utils/inet.h
index 9626a2d..7cb7337 100644
--- a/src/include/utils/inet.h
+++ b/src/include/utils/inet.h
@@ -53,6 +53,36 @@ typedef struct
 	inet_struct inet_data;
 } inet;
 
+/*
+ *	Access macros.	We use VARDATA_ANY so that we can process short-header
+ *	varlena values without detoasting them.  This requires a trick:
+ *	VARDATA_ANY assumes the varlena header is already filled in, which is
+ *	not the case when constructing a new value (until SET_INET_VARSIZE is
+ *	called, which we typically can't do till the end).  Therefore, we
+ *	always initialize the newly-allocated value to zeroes (using palloc0).
+ *	A zero length word will look like the not-1-byte case to VARDATA_ANY,
+ *	and so we correctly construct an uncompressed value.
+ *
+ *	Note that ip_maxbits() and SET_INET_VARSIZE() require
+ *	the family field to be set correctly.
+ */
+
+#define ip_family(inetptr) \
+	(((inet_struct *) VARDATA_ANY(inetptr))-family)
+
+#define ip_bits(inetptr) \
+	(((inet_struct *) VARDATA_ANY(inetptr))-bits)
+
+#define ip_addr(inetptr) \
+	(((inet_struct *) VARDATA_ANY(inetptr))-ipaddr)
+
+#define ip_maxbits(inetptr) \
+	(ip_family(inetptr) == PGSQL_AF_INET ? 32 : 128)
+
+#define SET_INET_VARSIZE(dst) \
+	SET_VARSIZE(dst, VARHDRSZ + offsetof(inet_struct, ipaddr) + \
+ip_addrsize(dst))
+
 
 /*
  *	This is the internal storage format for MAC addresses:
@@ -70,9 +100,11 @@ typedef struct macaddr
 /*
  * fmgr interface macros
  */
-#define DatumGetInetP(X)	((inet *) PG_DETOAST_DATUM_PACKED(X))
+#define DatumGetInetP(X)	((inet *) PG_DETOAST_DATUM(X))
+#define DatumGetInetPP(X)	((inet *) PG_DETOAST_DATUM_PACKED(X))
 #define 

Re: [HACKERS] heap vacuum cleanup locks

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 2:26 AM, Simon Riggs si...@2ndquadrant.com wrote:
 On Tue, Nov 8, 2011 at 2:54 AM, Robert Haas robertmh...@gmail.com wrote:
 It would still be nice to fix the case where we need to freeze a tuple
 that is on a page someone else has pinned, but I don't have any good
 ideas for how to do that.

 I think we need to avoid long pin hold times generally.

In the case of a suspended sequential scan, which is the case where
this has most recently bitten me on a production system, it actually
seems rather unnecessary to hold the pin for a long period of time.
If we release the buffer pin, then someone could vacuum the buffer.  I
haven't looked in detail at the issues, but in theory that doesn't
seem like a huge problem: just remember which TIDs you've already
looked at and, when you re-acquire the buffer, pick up where you left
off.  Any tuples that have been vacuumed away meanwhile weren't going
to be visible to your scan anyway.

But there's an efficiency argument against doing it that way.  First,
if we release the pin then we'll have to reacquire the buffer, which
means taking and releasing a BufMappingLock, the buffer header
spinlock, and the buffer content lock.  Second, instead of returning a
pointer to the data in the page, we'll have to copy the data out of
the buffer before releasing the pin.

The situation is similar (perhaps even simpler) for index-only scans.
We could easily release the heap buffer pin after returning a tuple,
but it will make things much slower if the next heap fetch hits the
same page.

I wonder if we could arrange for a vacuum that's waiting for a cleanup
lock to signal the backends that could possibly be holding a
conflicting pin.  Sort of like what the startup process does during
Hot Standby, except that instead of killing the people holding the
pins, we'd send them a signal that says if at all possible, could you
please release those buffer pins right away?, and then the backends
would try to comply.  Actually making that work though seems a bit
tricky, though, and getting it wrong would mean very, very rare,
nearly unreproducible bugs.

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


[HACKERS] Disable OpenSSL compression

2011-11-08 Thread Albe Laurenz
I ran into a performance problem described in this thread:

http://archives.postgresql.org/pgsql-performance/2011-10/msg00249.php
continued here:
http://archives.postgresql.org/pgsql-performance/2011-11/msg00045.php

OpenSSL compresses data by default, and that causes a
performance penalty of 100% and more, at least when
SELECTing larger bytea objects.
The backend process becomes CPU bound.

From OpenSSL version 1.0.0. on, compression can be
disabled.  The attached patch does that, and with that
patch I see dramatic performance improvements:

Unpatched:

samples %   image name symbol name
675483.7861 libz.so.1.2.3  /lib64/libz.so.1.2.3
618  7.6665 libcrypto.so.1.0.0 /usr/lib64/libcrypto.so.1.0.0
534  6.6245 postgres   hex_encode
95   1.1785 libc-2.12.so   memcpy

Patched:

samples %   image name symbol name
751 50.1670 libcrypto.so.1.0.0 /usr/lib64/libcrypto.so.1.0.0
594 39.6794 postgres   hex_encode
83   5.5444 libc-2.12.so   memcpy

(the test case is selecting one 27 MB bytea in text
mode over a localhost connection)

Are there any objections to this?

It is possible that this could cause a performance
regression for people who SELECT lots of compressible
data over really slow network connections, but is that
a realistic scenario?

If there are concerns about that, maybe a GUC variable like
ssl_compression (defaulting to off) would be a solution.

Yours,
Laurenz Albe


ssl.patch
Description: ssl.patch

-- 
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] proposal: psql concise mode

2011-11-08 Thread Alvaro Herrera

Excerpts from Robert Haas's message of mar nov 08 01:25:31 -0300 2011:

 But I can't help feeling that as we continue to add more features,
 we've eventually going to end up with our backs to the wall.  Not sure
 what to do about that, but...

What I've imagined for a long time is psql being able to display each
row in more than one line; for example something like

\df

   Listado de funciones
 Esquema |  Nombre   | Tipo de dato de salida | Tipo
 Tipos de datos de argumentos   

-+---++--
 public  | bt_metap  | record | normal
 relname text, OUT magic integer, OUT version integer, OUT root integer, OUT 
level integer, OUT fastroot integer, OUT fastlevel integer  
-+---++--
 public  | bt_page_items | SETOF record   | normal
 relname text, blkno integer, OUT itemoffset smallint, OUT ctid tid, OUT 
itemlen smallint, OUT nulls boolean, OUT vars boolean, OUT data text
-+---++--

So it's not as horizontally-wasteful as expanded, but it's much more
readable than aligned.

-- 
Á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] synchronous commit vs. hint bits

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 1:59 AM, Simon Riggs si...@2ndquadrant.com wrote:
 Please continue to expect that, I just haven't finished it yet...

OK.

So here's the deal: this is an effective, mostly automatic solution to
the performance problem noted in the original post.  For example, at
32 clients, the original test case gives about 7800-8300 tps with
wal_writer_delay=200ms, and about 10100 tps with
wal_writer_delay=20ms.  With wal_writer_delay=200ms but the patch
applied, median of three five minute pgbench runs is 9952 tps; all
three runs are under 1 tps.  So it's not quite as good as
adjusting wal_writer_delay downward, but it gives you roughly 90% of
the benefit automatically, without needing to adjust any settings.
That seems very worthwhile.

At 1 client, 8 clients, and 80 clients, the results were even better.
The patched code with wal_writer_delay=200ms slightly outperformed the
unpatched code with wal_writer_delay=20ms (and outperformed the
unpatched code with wal_writer_delay=200ms even more).  It's possible
that some of that is random variation, but maybe not all of it - e.g.
at 1 client:

unpatched, wal_writer_delay = 200ms: 602, 604, 607 tps
unpatched, wal_writer_delay = 20ms: 614, 616, 616 tps
patched, wal_writer_delay = 200ms: 633, 634, 636 tps

The fact that those numbers aren't bouncing around much suggests that
it might be a real effect.

I have also reviewed the code and it seems OK.

So +1 from me for applying this.

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Magnus Hagander
On Tue, Nov 8, 2011 at 14:59, Albe Laurenz laurenz.a...@wien.gv.at wrote:
 I ran into a performance problem described in this thread:

 http://archives.postgresql.org/pgsql-performance/2011-10/msg00249.php
 continued here:
 http://archives.postgresql.org/pgsql-performance/2011-11/msg00045.php

 OpenSSL compresses data by default, and that causes a
 performance penalty of 100% and more, at least when
 SELECTing larger bytea objects.
 The backend process becomes CPU bound.

 From OpenSSL version 1.0.0. on, compression can be
 disabled.  The attached patch does that, and with that
 patch I see dramatic performance improvements:

 Unpatched:

 samples %       image name         symbol name
 6754    83.7861 libz.so.1.2.3      /lib64/libz.so.1.2.3
 618      7.6665 libcrypto.so.1.0.0 /usr/lib64/libcrypto.so.1.0.0
 534      6.6245 postgres           hex_encode
 95       1.1785 libc-2.12.so       memcpy

 Patched:

 samples %       image name         symbol name
 751     50.1670 libcrypto.so.1.0.0 /usr/lib64/libcrypto.so.1.0.0
 594     39.6794 postgres           hex_encode
 83       5.5444 libc-2.12.so       memcpy

 (the test case is selecting one 27 MB bytea in text
 mode over a localhost connection)

 Are there any objections to this?

This should probably  be made an option.

And doesn't it, at least in a lot of cases, make more sense to control
this from the client side? It might typically be good to use
comopression if you are connecting over a slow link such as mobile or
satellite. And typically the client knows that, not the server. So
either client, or pg_hba driven, perhaps?


 It is possible that this could cause a performance
 regression for people who SELECT lots of compressible
 data over really slow network connections, but is that
 a realistic scenario?

Turning it off unconditionally can certainly create such a regression.
I don't think it's at all unrealstic.


-- 
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.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] Disable OpenSSL compression

2011-11-08 Thread Martin Pihlak
On 11/08/2011 03:59 PM, Albe Laurenz wrote:
 If there are concerns about that, maybe a GUC variable like
 ssl_compression (defaulting to off) would be a solution.
 

I'd vote for a libpq connect option instead. Something like
sslcompress=yes|no accompanied by PGSSLCOMPRESS environment
variable. And defaulting to yes, as not to break any
backward compatibilty. For instance we expect SSL to provide
compression, wouldn't even use it without it.

Regards,
Martin

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Marko Kreen
On Tue, Nov 8, 2011 at 3:59 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote:
 It is possible that this could cause a performance
 regression for people who SELECT lots of compressible
 data over really slow network connections, but is that
 a realistic scenario?

Yes, it's a realistic scenario.  Please make it a option.

Also, high-security links may prefer compression.

-- 
marko

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Tom Lane
Marko Kreen mark...@gmail.com writes:
 On Tue, Nov 8, 2011 at 3:59 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote:
 It is possible that this could cause a performance
 regression for people who SELECT lots of compressible
 data over really slow network connections, but is that
 a realistic scenario?

 Yes, it's a realistic scenario.  Please make it a option.

I distinctly recall us getting bashed a few years ago because there
wasn't any convenient way to turn SSL compression *on*.  Now that SSL
finally does the sane thing by default, you want to turn it off?

The fact of the matter is that in most situations where you want SSL,
ie links across insecure WANs, compression is a win.  Testing a local
connection, as you seem to have done, is just about 100% irrelevant to
performance in the real world.

There might be some argument for providing a client option to disable
compression, but it should not be forced, and it shouldn't even be the
default.  But before adding YA connection option, I'd want to see some
evidence that it's useful over non-local connections.

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] ProcArrayLock contention

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 2:24 AM, YAMAMOTO Takashi y...@mwd.biglobe.ne.jp wrote:
 latestCompletedXid got backward due to concurrent updates
 and it fooled TransactionIdIsInProgress?

Ah ha!  I bet that's it.

I think this could be avoided by a more sophisticated locking scheme.
Instead of waking up all the people trying to do
ProcArrayEndTransaction() and letting them all run simultaneously,
wake up one of them.  That one guy goes and clears all the XID fields
and updates latestCompletedXid, and then wakes up all the others (who
now don't even need to reacquire the spinlock to release the lock,
because they never really held it in the first place, but yet the work
they needed done is done).

The trick is to make something like that work within the confines of
the LWLock mechanism.  It strikes me that we have a number of places
in the system where it would be useful to leverage the queuing and
error handling facilities that the lwlock mechanism provides, but have
different rules for handling lock conflicts - either different lock
modes, or request combining, or whatever.  lwlock.c is an awfully big
chunk of code to cut-and-paste if you need an lwlock with three modes,
or some primitive that has behavior similar to an lwlock overall but
with some differences in detail.  I wonder if there's a way that we
could usefully refactor things to make that sort of thing easier.

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Dave Page
On Tue, Nov 8, 2011 at 2:34 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 The fact of the matter is that in most situations where you want SSL,
 ie links across insecure WANs, compression is a win.  Testing a local
 connection, as you seem to have done, is just about 100% irrelevant to
 performance in the real world.

I would disagree with that. Deployments in the cloud may have fast,
but untrustworthy network connections.

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

EnterpriseDB UK: 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] Disable OpenSSL compression

2011-11-08 Thread Andrew Dunstan



On 11/08/2011 09:34 AM, Tom Lane wrote:

Marko Kreenmark...@gmail.com  writes:

On Tue, Nov 8, 2011 at 3:59 PM, Albe Laurenzlaurenz.a...@wien.gv.at  wrote:

It is possible that this could cause a performance
regression for people who SELECT lots of compressible
data over really slow network connections, but is that
a realistic scenario?

Yes, it's a realistic scenario.  Please make it a option.

I distinctly recall us getting bashed a few years ago because there
wasn't any convenient way to turn SSL compression *on*.  Now that SSL
finally does the sane thing by default, you want to turn it off?

The fact of the matter is that in most situations where you want SSL,
ie links across insecure WANs, compression is a win.  Testing a local
connection, as you seem to have done, is just about 100% irrelevant to
performance in the real world.

There might be some argument for providing a client option to disable
compression, but it should not be forced, and it shouldn't even be the
default.  But before adding YA connection option, I'd want to see some
evidence that it's useful over non-local connections.



I can certainly conceive of situations where one wants SSL on a high 
speed/bandwidth network. I don't think we should assume that all or even 
most real world SSL use will be across slow networks.


Here's another data point: 
http://journal.paul.querna.org/articles/2011/04/05/openssl-memory-use/



cheers

andrew

--
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] Disable OpenSSL compression

2011-11-08 Thread Tom Lane
Andrew Dunstan and...@dunslane.net writes:
 On 11/08/2011 09:34 AM, Tom Lane wrote:
 There might be some argument for providing a client option to disable
 compression, but it should not be forced, and it shouldn't even be the
 default.  But before adding YA connection option, I'd want to see some
 evidence that it's useful over non-local connections.

 I can certainly conceive of situations where one wants SSL on a high 
 speed/bandwidth network. I don't think we should assume that all or even 
 most real world SSL use will be across slow networks.

Even for that use-case, I don't believe that testing on a local loopback
connection should be considered representative.

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] Disable OpenSSL compression

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 9:58 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Andrew Dunstan and...@dunslane.net writes:
 On 11/08/2011 09:34 AM, Tom Lane wrote:
 There might be some argument for providing a client option to disable
 compression, but it should not be forced, and it shouldn't even be the
 default.  But before adding YA connection option, I'd want to see some
 evidence that it's useful over non-local connections.

 I can certainly conceive of situations where one wants SSL on a high
 speed/bandwidth network. I don't think we should assume that all or even
 most real world SSL use will be across slow networks.

 Even for that use-case, I don't believe that testing on a local loopback
 connection should be considered representative.

Probably not, but I think we ought to provide the option to disable
compression for those who want to do that.  I also agree with you that
we should leave the default as-is.

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Marko Kreen
On Tue, Nov 8, 2011 at 4:34 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Marko Kreen mark...@gmail.com writes:
 On Tue, Nov 8, 2011 at 3:59 PM, Albe Laurenz laurenz.a...@wien.gv.at wrote:
 It is possible that this could cause a performance
 regression for people who SELECT lots of compressible
 data over really slow network connections, but is that
 a realistic scenario?

 Yes, it's a realistic scenario.  Please make it a option.

 I distinctly recall us getting bashed a few years ago because there
 wasn't any convenient way to turn SSL compression *on*.  Now that SSL
 finally does the sane thing by default, you want to turn it off?

 The fact of the matter is that in most situations where you want SSL,
 ie links across insecure WANs, compression is a win.  Testing a local
 connection, as you seem to have done, is just about 100% irrelevant to
 performance in the real world.

 There might be some argument for providing a client option to disable
 compression, but it should not be forced, and it shouldn't even be the
 default.  But before adding YA connection option, I'd want to see some
 evidence that it's useful over non-local connections.

+1 for keeping current default.

But I can imagine scenarios where having option to turn compression
off could be useful:
- when minimal latency is required
- when normal latency is required, but data is big
- when serving big non-compressible blobs - zlib can be very slow
- when serving lots of connections and want
  to minimize unnecessary cpu and memory load

Depending on how zlib is used by openssl, some of
them may not happen in practice.

-- 
marko

-- 
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] DatumGetInetP buggy

2011-11-08 Thread Tom Lane
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes:
 Hmm, it seems to be intentional, but I agree it's very much contrary to 
 the usual convention that DatumGetXXXP() returns a detoasted and 
 depacked datum. I think we should change it. I propose the attached 
 patch. It changes DatumGetInetP() to do PG_DETOAST_DATUM(), and adds new 
 DatumGetInetPP() macro to return the packed version. I also moved the 
 access macros like ip_family() from network.c to inet.h, so that they're 
 available for whoever wants to look at the fields without having to depack.

No objection to making the DatumGet macro names conform to common
convention, but I'm not thrilled with moving those special-purpose
accessor macros into wider circulation.  It's not necessary and the
macros don't work unless used in a particular way per the comment,
so I don't think they can be considered general purpose.

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] heap vacuum cleanup locks

2011-11-08 Thread Simon Riggs
On Tue, Nov 8, 2011 at 1:50 PM, Robert Haas robertmh...@gmail.com wrote:

 But there's an efficiency argument against doing it that way.  First,
 if we release the pin then we'll have to reacquire the buffer, which
 means taking and releasing a BufMappingLock, the buffer header
 spinlock, and the buffer content lock.  Second, instead of returning a
 pointer to the data in the page, we'll have to copy the data out of
 the buffer before releasing the pin.

The only way I can see this working is to optimise this in the
planner, so that when we have a nested loop within a loop, we avoid
having the row on the outer loop pinned while we perform the inner
loop.

-- 
 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] proposal: psql concise mode

2011-11-08 Thread Dickson S. Guedes
2011/11/8 Alvaro Herrera alvhe...@commandprompt.com:
 What I've imagined for a long time is psql being able to display each
 row in more than one line; for example something like

 \df

                       Listado de funciones
  Esquema |      Nombre       | Tipo de dato de salida | Tipo
  Tipos de datos de argumentos
 -+---++--
  public  | bt_metap          | record                 | normal
  relname text, OUT magic integer, OUT version integer, OUT root integer, OUT 
 level integer, OUT fastroot integer, OUT fastlevel integer
 -+---++--
  public  | bt_page_items     | SETOF record           | normal
  relname text, blkno integer, OUT itemoffset smallint, OUT ctid tid, OUT 
 itemlen smallint, OUT nulls boolean, OUT vars boolean, OUT data text
 -+---++--

Isn't that what pagers like 'less' and 'more' do already? May be we
could provide a pager more specific to psql output as a contrib or
extension.

[]s
-- 
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

-- 
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] heap vacuum cleanup locks

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 10:08 AM, Simon Riggs si...@2ndquadrant.com wrote:
 On Tue, Nov 8, 2011 at 1:50 PM, Robert Haas robertmh...@gmail.com wrote:
 But there's an efficiency argument against doing it that way.  First,
 if we release the pin then we'll have to reacquire the buffer, which
 means taking and releasing a BufMappingLock, the buffer header
 spinlock, and the buffer content lock.  Second, instead of returning a
 pointer to the data in the page, we'll have to copy the data out of
 the buffer before releasing the pin.

 The only way I can see this working is to optimise this in the
 planner, so that when we have a nested loop within a loop, we avoid
 having the row on the outer loop pinned while we perform the inner
 loop.

Hmm.  I've actually never run into a problem that involved that
particular situation.

In any case, I think the issues are basically the same: keeping the
pin improves performance; dropping it helps VACUUM.  Both are
important.

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Albe Laurenz
Tom Lane wrote:
 I distinctly recall us getting bashed a few years ago because there
 wasn't any convenient way to turn SSL compression *on*.  Now that SSL
 finally does the sane thing by default, you want to turn it off?
 
 The fact of the matter is that in most situations where you want SSL,
 ie links across insecure WANs, compression is a win.  Testing a local
 connection, as you seem to have done, is just about 100% irrelevant to
 performance in the real world.

Maybe that's paranoia, but we use SSL via the company's LAN to keep
potentially sensitive data from crossing the network unencrypted.

 There might be some argument for providing a client option to disable
 compression, but it should not be forced, and it shouldn't even be the
 default.  But before adding YA connection option, I'd want to see some
 evidence that it's useful over non-local connections.

I will try to provide test results via remote connection; I thought
that localhost was a good enough simulation for a situation where
you are not network bound.

I agree with you that a client option would make more sense.
The big problem I personally have with that is that it only works
if you use libpq. When using the JDBC driver or Npgsql, a client
option wouldn't help me at all.

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] heap vacuum cleanup locks

2011-11-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 On Tue, Nov 8, 2011 at 2:26 AM, Simon Riggs si...@2ndquadrant.com wrote:
 I think we need to avoid long pin hold times generally.

 In the case of a suspended sequential scan, which is the case where
 this has most recently bitten me on a production system, it actually
 seems rather unnecessary to hold the pin for a long period of time.
 If we release the buffer pin, then someone could vacuum the buffer.

This seems unlikely to be a productive line of thought.  The only way
you could release buffer pin is if you first copied all the tuples you
need out of the page, and that seems like an unacceptable performance
hit.  We should not be penalizing foreground query operations for the
benefit of background maintenance like VACUUM.  (The fact that we do
an equivalent thing in btree index scans isn't an argument for doing
it here, because the tradeoffs are very different.  In the index case,
the amount of data to be copied is a great deal less; the length of
time the lock would have to be held is often a great deal more; and
releasing the lock quickly gives a performance benefit for other
foreground operations, not only background maintenance.)

It strikes me that the only case where vacuum now has to wait is where
it needs to freeze an old XID.  Couldn't it do that without insisting on
exclusive access?  We only need exclusive access if we're going to move
data around, but we could have a code path in vacuum that just replaces
old XIDs with FrozenXID without moving/deleting anything.

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] proposal: psql concise mode

2011-11-08 Thread Alvaro Herrera

Excerpts from Dickson S. Guedes's message of mar nov 08 12:11:21 -0300 2011:

 Isn't that what pagers like 'less' and 'more' do already? May be we
 could provide a pager more specific to psql output as a contrib or
 extension.

Well, now that you mention it, all pagers I know are line-based.  If you
want to figure out how to make a pager that's record-based instead, be
my guest.  It sounds very useful for this sort of use case.

Nice to see you in PGBR by the way.

-- 
Á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] heap vacuum cleanup locks

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 10:26 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 It strikes me that the only case where vacuum now has to wait is where
 it needs to freeze an old XID.  Couldn't it do that without insisting on
 exclusive access?  We only need exclusive access if we're going to move
 data around, but we could have a code path in vacuum that just replaces
 old XIDs with FrozenXID without moving/deleting anything.

Interesting idea.  I think in general we insist that you must have a
buffer content lock to inspect the tuple visibility info, in which
case that would be safe.  But I'm not sure we do that absolutely
everywhere.  For instance, just last night I noticed this:

/*
 * If xmin isn't what we're expecting, the
slot must have been
 * recycled and reused for an unrelated tuple.
 This implies that
 * the latest version of the row was deleted,
so we need do
 * nothing.  (Should be safe to examine xmin
without getting
 * buffer's content lock, since xmin never
changes in an existing
 * tuple.)
 */
if
(!TransactionIdEquals(HeapTupleHeaderGetXmin(tuple.t_data),

  priorXmax))
{
ReleaseBuffer(buffer);
return NULL;
}

Maybe we can convince ourselves that that case is OK, or fixable; I'm
not sure whether there are any others.

-- 
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] heap vacuum cleanup locks

2011-11-08 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes:
 Interesting idea.  I think in general we insist that you must have a
 buffer content lock to inspect the tuple visibility info, in which
 case that would be safe.  But I'm not sure we do that absolutely
 everywhere.  For instance, just last night I noticed this:

 /*
  * If xmin isn't what we're expecting, the
 slot must have been
  * recycled and reused for an unrelated tuple.
  This implies that
  * the latest version of the row was deleted,
 so we need do
  * nothing.  (Should be safe to examine xmin
 without getting
  * buffer's content lock, since xmin never
 changes in an existing
  * tuple.)
  */
 if

Hmm ... I think that code is OK but the comment needs work.  Here we are
necessarily looking for a pretty recent value of xmin (it has to be
later than GlobalXmin), so there's no need to worry that it might get
changed to FrozenXID.

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] Disable OpenSSL compression

2011-11-08 Thread Albe Laurenz
Tom Lane wrote:
 There might be some argument for providing a client option to disable
 compression, but it should not be forced, and it shouldn't even be the
 default.  But before adding YA connection option, I'd want to see some
 evidence that it's useful over non-local connections.

Here are numbers from a test via LAN.
The client machine has OpenSSL 0.9.8e, the server OpenSSL 1.0.0.

The client command run was

echo 'select ...' | time psql host=... -o /dev/null

and \timing was turned on in .psqlrc

In addition to the oprofile data I collected three times:
- the duration as shown in the server log
- the duration as shown by \timing
- the duration of the psql command as measured by time

Without patch:

duration: 5730.996 ms (log), 5975.093 ms (\timing), 22.87 s (time)

samples  %image name   symbol name
4428 80.2029  libz.so.1.2.3/lib64/libz.so.1.2.3
559  10.1250  postgres hex_encode
361   6.5387  libcrypto.so.1.0.0   /usr/lib64/libcrypto.so.1.0.0
831.5034  libc-2.12.so memcpy

With patch:

duration: 3001.009 ms (log), 3243.690 ms (\timing), 20.27 s (time)

samples  %image name   symbol name
1072 58.0401  libcrypto.so.1.0.0   /usr/lib64/libcrypto.so.1.0.0
587  31.7813  postgres hex_encode
105   5.6849  libc-2.12.so memcpy


I think this makes a good case for disabling compression.

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] Disable OpenSSL compression

2011-11-08 Thread k...@rice.edu
On Tue, Nov 08, 2011 at 04:59:02PM +0100, Albe Laurenz wrote:
 Tom Lane wrote:
  There might be some argument for providing a client option to disable
  compression, but it should not be forced, and it shouldn't even be the
  default.  But before adding YA connection option, I'd want to see some
  evidence that it's useful over non-local connections.
 
 Here are numbers from a test via LAN.
 The client machine has OpenSSL 0.9.8e, the server OpenSSL 1.0.0.
 
 The client command run was
 
 echo 'select ...' | time psql host=... -o /dev/null
 
 and \timing was turned on in .psqlrc
 
 In addition to the oprofile data I collected three times:
 - the duration as shown in the server log
 - the duration as shown by \timing
 - the duration of the psql command as measured by time
 
 Without patch:
 
 duration: 5730.996 ms (log), 5975.093 ms (\timing), 22.87 s (time)
 
 samples  %image name   symbol name
 4428 80.2029  libz.so.1.2.3/lib64/libz.so.1.2.3
 559  10.1250  postgres hex_encode
 361   6.5387  libcrypto.so.1.0.0   /usr/lib64/libcrypto.so.1.0.0
 831.5034  libc-2.12.so memcpy
 
 With patch:
 
 duration: 3001.009 ms (log), 3243.690 ms (\timing), 20.27 s (time)
 
 samples  %image name   symbol name
 1072 58.0401  libcrypto.so.1.0.0   /usr/lib64/libcrypto.so.1.0.0
 587  31.7813  postgres hex_encode
 105   5.6849  libc-2.12.so memcpy
 
 
 I think this makes a good case for disabling compression.
 
 Yours,
 Laurenz Albe

Certainly a good case for providing the option to disable compression.

Regards,
Ken

-- 
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] Disable OpenSSL compression

2011-11-08 Thread k...@rice.edu
On Tue, Nov 08, 2011 at 04:19:02PM +0100, Albe Laurenz wrote:
 Tom Lane wrote:
  I distinctly recall us getting bashed a few years ago because there
  wasn't any convenient way to turn SSL compression *on*.  Now that SSL
  finally does the sane thing by default, you want to turn it off?
  
  The fact of the matter is that in most situations where you want SSL,
  ie links across insecure WANs, compression is a win.  Testing a local
  connection, as you seem to have done, is just about 100% irrelevant to
  performance in the real world.
 
 Maybe that's paranoia, but we use SSL via the company's LAN to keep
 potentially sensitive data from crossing the network unencrypted.
 
  There might be some argument for providing a client option to disable
  compression, but it should not be forced, and it shouldn't even be the
  default.  But before adding YA connection option, I'd want to see some
  evidence that it's useful over non-local connections.
 
 I will try to provide test results via remote connection; I thought
 that localhost was a good enough simulation for a situation where
 you are not network bound.
 
 I agree with you that a client option would make more sense.
 The big problem I personally have with that is that it only works
 if you use libpq. When using the JDBC driver or Npgsql, a client
 option wouldn't help me at all.
 
 Yours,
 Laurenz Albe
 

I think that JDBC and Npgsql should also support disabling compression.

Regards,
Ken

-- 
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] heap vacuum cleanup locks

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 10:54 AM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 Interesting idea.  I think in general we insist that you must have a
 buffer content lock to inspect the tuple visibility info, in which
 case that would be safe.  But I'm not sure we do that absolutely
 everywhere.  For instance, just last night I noticed this:

                         /*
                          * If xmin isn't what we're expecting, the
 slot must have been
                          * recycled and reused for an unrelated tuple.
  This implies that
                          * the latest version of the row was deleted,
 so we need do
                          * nothing.  (Should be safe to examine xmin
 without getting
                          * buffer's content lock, since xmin never
 changes in an existing
                          * tuple.)
                          */
                         if

 Hmm ... I think that code is OK but the comment needs work.  Here we are
 necessarily looking for a pretty recent value of xmin (it has to be
 later than GlobalXmin), so there's no need to worry that it might get
 changed to FrozenXID.

OK.  Here's another possible concern: what happens if the page we're
freezing contains a dead tuple?  It looks to me like
heap_freeze_tuple() is written so as not to require a cleanup lock -
indeed, the comments claim it's called when holding only a share lock
on the buffer, which doesn't appear to match what lazy_scan_heap() is
actually doing.  But it does seem to assume that any tuples that still
exist are all-visible, which only works if vacuum has already pruned
the page.

-- 
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] lexemes in prefix search going through dictionary modifications

2011-11-08 Thread Sushant Sinha
I think there is a need to provide prefix search to bypass
dictionaries.If you folks think that there is some credibility to such a
need then I can think about implementing it. How about an operator like
:# that does this? The :* will continue to mean the same as
currently.

-Sushant.

On Tue, 2011-10-25 at 23:45 +0530, Sushant Sinha wrote:
 On Tue, 2011-10-25 at 19:27 +0200, Florian Pflug wrote:
 
  Assume, for example, that the postgres mailing list archive search used
  tsearch (which I think it does, but I'm not sure). It'd then probably make
  sense to add postgres to the list of stopwords, because it's bound to 
  appear in nearly every mail. But wouldn't you want searched which include
  'postgres*' to turn up empty? Quite certainly not.
 
 That improves recall for postgres:* query and certainly doesn't help
 other queries like post:*. But more importantly it affects precision
 for all queries like a:*, an:*, and:*, s:*, 't:*', the:*, etc
 (When that is the only search it also affects recall as no row matches
 an empty tsquery). Since stopwords are smaller, it means prefix search
 for a few characters is meaningless. And I would argue that is when the
 prefix search is more important -- only when you know a few characters.
 
 
 -Sushant



-- 
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] Disable OpenSSL compression

2011-11-08 Thread Christopher Browne
On Tue, Nov 8, 2011 at 11:06 AM, k...@rice.edu k...@rice.edu wrote:
 I think that JDBC and Npgsql should also support disabling compression.

That's the *real* problem here...

You're quite right that if we allow controlling this on the libpq
side, it is surely desirable to allow controlling this via JDBC,
Npgsql, and other mechanisms people may have around.  (There are
native protocol implementations for Common Lisp and Go, for instance.
They may not be particularly important, )

Unfortunately, each protocol implementation is independent, which
really is the nature of the beast, which includes:
a) The code of the implementation,
b) Release of the implementation,
c) Packaging of releases into software distributions.

With that series of complications, I wonder if maybe the right place
to control this is pg_hba.conf.  That has the merit of centralizing
control in such a way that it would apply commonly to
libpq/JDBC/Npgsql/..., though with the demerit that the control does
not take place on the client side, which is desirable.

I wonder how many SSL parameters there are which would be worth trying
to have available.  I expect we'd benefit from looking at all the
relevant ones at once, so as to not have the problem of hacking one
more into place and perhaps doing it a bit differently each time.
-- 
When confronted by a difficult problem, solve it by reducing it to the
question, How would the Lone Ranger handle 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] [v9.2] Object access hooks with arguments support (v1)

2011-11-08 Thread Robert Haas
On Mon, Nov 7, 2011 at 12:20 PM, Kohei KaiGai kai...@kaigai.gr.jp wrote:
 If sepgsql would apply permission checks db_procedure:{install} on the
 OAT_POST_CREATE hook based on the funcion-oid within new entry of
 system catalog, we can relocate OAT_PREP_CREATE hook more conceptually
 right place, such as just after the pg_namespace_aclcheck() of DefineType().
 On the other hand, we may need to answer why these information are NOT
 delivered on the OAT_PREP_CREATE hook without knowledge of sepgsql
 internal.

I'm having a hard time understanding this.

Can you strip this patch down so it just applies to a single object
type (tables, maybe, or functions, or whatever you like) and then
submit the corresponding sepgsql changes with it?  Just as a demo
patch, so I can understand where you're trying to go with this.

-- 
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] plpython extension control files installation

2011-11-08 Thread Robert Haas
On Sat, Nov 5, 2011 at 10:42 AM, Peter Eisentraut pete...@gmx.net wrote:
 We only build the language plpython2u or plpython3u, not both, in any
 build, but we always install the extension control files for all
 variants.  Is there a reason for this, or just an oversight?

Sounds like an oversight.

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Jeroen Vermeulen

On 2011-11-08 22:59, Albe Laurenz wrote:


In addition to the oprofile data I collected three times:
- the duration as shown in the server log
- the duration as shown by \timing
- the duration of the psql command as measured by time


[...]


I think this makes a good case for disabling compression.


It's a few data points, but is it enough to make a good case?  As I 
understand it, compression can save time not only on transport but also 
on the amount of data that needs to go through encryption -- probably 
depending on choice of cypher, hardware support, machine word width, 
compilation details etc.  Would it make sense to run a wider experiment, 
e.g. in the buld farm?


Another reason why I believe compression is often used with encryption 
is to maximize information content per byte of data: harder to guess, 
harder to crack.  Would that matter?



Jeroen

--
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] proposal: psql concise mode

2011-11-08 Thread Dickson S. Guedes
2011/11/8 Alvaro Herrera alvhe...@commandprompt.com:
 Excerpts from Dickson S. Guedes's message of mar nov 08 12:11:21 -0300 2011:
 Isn't that what pagers like 'less' and 'more' do already? May be we
 could provide a pager more specific to psql output as a contrib or
 extension.

 Well, now that you mention it, all pagers I know are line-based.  If you
 want to figure out how to make a pager that's record-based instead, be
 my guest.  It sounds very useful for this sort of use case.


Yes, it could be a good homework for me to do later, then I'll wait
for more opinions about the original Josh's proposal to avoid
duplicate work.


 Nice to see you in PGBR by the way.


Nice to see you too! :)


Best regards.
-- 
Dickson S. Guedes
mail/xmpp: gue...@guedesoft.net - skype: guediz
http://guedesoft.net - http://www.postgresql.org.br

-- 
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] Disable OpenSSL compression

2011-11-08 Thread Tom Lane
Jeroen Vermeulen j...@xs4all.nl writes:
 Another reason why I believe compression is often used with encryption 
 is to maximize information content per byte of data: harder to guess, 
 harder to crack.  Would that matter?

Yes, it would.  There's a reason why the OpenSSL default is what it is.

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] DatumGetInetP buggy

2011-11-08 Thread Heikki Linnakangas

On 08.11.2011 17:06, Tom Lane wrote:

Heikki Linnakangasheikki.linnakan...@enterprisedb.com  writes:

Hmm, it seems to be intentional, but I agree it's very much contrary to
the usual convention that DatumGetXXXP() returns a detoasted and
depacked datum. I think we should change it. I propose the attached
patch. It changes DatumGetInetP() to do PG_DETOAST_DATUM(), and adds new
DatumGetInetPP() macro to return the packed version. I also moved the
access macros like ip_family() from network.c to inet.h, so that they're
available for whoever wants to look at the fields without having to depack.


No objection to making the DatumGet macro names conform to common
convention, but I'm not thrilled with moving those special-purpose
accessor macros into wider circulation.  It's not necessary and the
macros don't work unless used in a particular way per the comment,
so I don't think they can be considered general purpose.


Ok.

What do people think of backpatching this? I'm inclined to backpatch, on 
the grounds that the macro that detoasts and depacks should always work 
(ie. after this patch), even if the depacking isn't necessary and 
introduces an extra palloc+copy, but the reverse is not true.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] ERROR: MergeAppend child's targetlist doesn't match MergeAppend

2011-11-08 Thread Teodor Sigaev

Hi!

I ran into a problem with PG 9.1 and bug is observed even in master. After 
simplifying a query (original was 9Kb long!) it's possible to reproduce it easily:


CREATE TABLE wow (t1 text, t2 text);
CREATE INDEX idx ON wow (t1,t2);

SET enable_seqscan=off;
SET enable_bitmapscan=off;

EXPLAIN
SELECT
t1, t2
FROM (
SELECT t1, t2 FROM wow
UNION ALL
SELECT 'a', 'a' FROM wow
) t
ORDER BY t1, t2;

if second 'a' constant is changed to something else then it works fine.

The root of problem is that tlist_member() (called in 
create_merge_append_plan()) for second constant returns TargetEntry for first 
constant because they are equal. And the same problem is observed if second 
select is replaced by  SELECT t1, t1 FROM wow.


It's seems to me that check in create_merge_append_plan() is too restrictive:
if (memcmp(sortColIdx, node-sortColIdx,
   numsortkeys * sizeof(AttrNumber)) != 0)
elog(ERROR, MergeAppend child's targetlist doesn't match 
MergeAppend);


Although I think, that more accurate check will repeat work done in 
prepare_sort_from_pathkeys().


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

--
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] Measuring relation free space

2011-11-08 Thread Greg Smith

On 11/06/2011 11:55 PM, Mark Kirkwood wrote:
I am guessing (at this point very much guessing) that pg_freespace map 
may return its data faster, as pageinspect is gonna have to grovel 
through all the pages for itself (whereas pg_freespacemap relies on 
using info from the ... free space map fork).


I started with pageinspect because I wasn't sure if other methods would 
be as accurate.  For example, I wasn't sure until just before submission 
that only the free space and size of the relation are needed to get a 
useful measure here; at one point I was considering some other things 
too.  I've ruled them out as unnecessary as far as I can tell, but I 
can't claim my tests are exhaustive.  Some review confirmation that this 
measure is useful for other people is one thing I was hoping for 
feedback on, as one thing to consider in addition to the actual 
implementation.


If this measurement is the only one needed, than as I said at the start 
of the thread here it might easily be implemented to run just against 
the free space map instead.  I'm thinking of what's been sent so far as 
a UI with matching output it should produce.  If it's possible to get 
the same numbers faster, exactly how to implement the function under the 
hood is easy enough to change.  Jaime already has a new version in 
development that adds a ring buffer for example.


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



Re: [HACKERS] DatumGetInetP buggy

2011-11-08 Thread Boszormenyi Zoltan
2011-11-08 18:53 keltezéssel, Heikki Linnakangas írta:
 On 08.11.2011 17:06, Tom Lane wrote:
 Heikki Linnakangasheikki.linnakan...@enterprisedb.com  writes:
 Hmm, it seems to be intentional, but I agree it's very much contrary to
 the usual convention that DatumGetXXXP() returns a detoasted and
 depacked datum. I think we should change it. I propose the attached
 patch. It changes DatumGetInetP() to do PG_DETOAST_DATUM(), and adds new
 DatumGetInetPP() macro to return the packed version. I also moved the
 access macros like ip_family() from network.c to inet.h, so that they're
 available for whoever wants to look at the fields without having to depack.

 No objection to making the DatumGet macro names conform to common
 convention, but I'm not thrilled with moving those special-purpose
 accessor macros into wider circulation.  It's not necessary and the
 macros don't work unless used in a particular way per the comment,
 so I don't think they can be considered general purpose.

 Ok.

 What do people think of backpatching this? I'm inclined to backpatch, on the 
 grounds
 that the macro that detoasts and depacks should always work (ie. after this 
 patch), even
 if the depacking isn't necessary and introduces an extra palloc+copy, but the 
 reverse is
 not true.

On the grounds that 9.0.x also shows the problem with
the stock macro, backporting would be nice. I don't know
which older trees may show the problem, I only tested with
9.0 and 9.1.

-- 
--
Zoltán Böszörményi
Cybertec Schönig  Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de
 http://www.postgresql.at/


-- 
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] Disable OpenSSL compression

2011-11-08 Thread Stephen Frost
Tom,

* Tom Lane (t...@sss.pgh.pa.us) wrote:
 The fact of the matter is that in most situations where you want SSL,
 ie links across insecure WANs, compression is a win.  Testing a local
 connection, as you seem to have done, is just about 100% irrelevant to
 performance in the real world.

I'm mystified by the idea that SSL shouldn't be used on local networks.
If the only things talking to the database are other servers on
physically secure networks, perhaps, but when you've got databases
exposed (even through firewalls) to client networks (which are in the
same building), and any data that's even remotely sensetive, you should
be using SSL or IPSEC.  The chances of eaves-dropping on a typiacal WAN
physical/dedicated link (not over the Internet..) are actually much less
than some disgruntled employee spoofing the local switches to monitor
someone else's traffic.  For starters, you're going to need some pretty
specialized gear to eavesdrop on a T1 or similar link and once it's past
the last mile and into the fibre network...  Well, there's some folks
who can manage that, but it's not very many.

 There might be some argument for providing a client option to disable
 compression, but it should not be forced, and it shouldn't even be the
 default.  But before adding YA connection option, I'd want to see some
 evidence that it's useful over non-local connections.

I agree that it should be an option and that it should be on by default.
It's going to typically be a win.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] ERROR: MergeAppend child's targetlist doesn't match MergeAppend

2011-11-08 Thread Tom Lane
Teodor Sigaev teo...@sigaev.ru writes:
 SELECT
  t1, t2
 FROM (
  SELECT t1, t2 FROM wow
  UNION ALL
  SELECT 'a', 'a' FROM wow
 ) t
 ORDER BY t1, t2;

Hmm, interesting.

 It's seems to me that check in create_merge_append_plan() is too restrictive:
  if (memcmp(sortColIdx, node-sortColIdx,
 numsortkeys * sizeof(AttrNumber)) != 0)
  elog(ERROR, MergeAppend child's targetlist doesn't match 
 MergeAppend);

No, it isn't.  That code is fine; the problem is that
add_child_rel_equivalences is generating an invalid state of the
EquivalenceClass structures by adding equal items to two different
EquivalenceClasses.  We need to rethink what that routine is doing.
It's definitely wrong for it to add constant items; here, that would
imply injecting t1 = 'a' and t2 = 'a' conditions, which is not correct.

 And the same problem is observed if second 
 select is replaced by  SELECT t1, t1 FROM wow.

And this one is a bit nasty too, since it would still add equal items
to two different ECs, leading to the conclusion that they should be
merged, ie t1 = t2, which is likewise wrong.

Not immediately sure what to do about this.  The quick-and-dirty fix
would be to only apply add_child_rel_equivalences to simple inheritance
child relations, for which the added items must be Vars and must be
different (which is what that code is expecting).  Seems like a bit of a
shame to lobotomize planning for UNION cases, though.  Maybe we need a
more complicated representation of child EquivalenceClass members.

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] unite recovery.conf and postgresql.conf

2011-11-08 Thread Josh Berkus

 configuration data somewhere else, but we really need to be able to tell
 the difference between starting PITR, continuing PITR after a
 mid-recovery crash, and finished PITR, up and running normally.
 A GUC is not a good way to do that.

Does a GUC make sense to you for how to handle standby/master for
replication?

-- 
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] 9.1.2 ?

2011-11-08 Thread Greg Jaskiewicz
Given the amount of fixes that went into the branch, and importance of them - 
when can we expect 9.1.2 to be released officially ?
9.1.1 was stamped on 22nd of September. 


-- 
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] DatumGetInetP buggy

2011-11-08 Thread Heikki Linnakangas

On 08.11.2011 20:46, Boszormenyi Zoltan wrote:

2011-11-08 18:53 keltezéssel, Heikki Linnakangas írta:

What do people think of backpatching this? I'm inclined to backpatch, on the 
grounds
that the macro that detoasts and depacks should always work (ie. after this 
patch), even
if the depacking isn't necessary and introduces an extra palloc+copy, but the 
reverse is
not true.


On the grounds that 9.0.x also shows the problem with
the stock macro, backporting would be nice. I don't know
which older trees may show the problem, I only tested with
9.0 and 9.1.


Packed varlenas were introduced in 8.3 - this hasn't changed since then. 
Committed and backpatched all the way to 8.3.


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.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] Releasing an alpha for CF2

2011-11-08 Thread Josh Berkus
Hackers,

I'd really like us to release an alpha version based on the finished
CF2.  There have been a number of major features added to PostgreSQL
prior to this CF -- including Haas's performance improvements and range
types -- which we really want at least some users to test early.

Would it be possible to do an alpha release?

-- 
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] pl/python custom datatype parsers

2011-11-08 Thread Andrew Dunstan



On 03/01/2011 11:50 AM, Peter Eisentraut wrote:

On fre, 2011-02-11 at 16:49 +0100, Jan Urbański wrote:

I believe it's (b). But as we don't have time for that discussion that
late in the release cycle, I think we need to consider it identical to (c).

As I previously mentioned, I think that there should be an SQL-level way
to tie together languages and types.  I previously mentioned the
SQL-standard command CREATE TRANSFORM as a possibility.  I've had this
on my PL/Python TOTHINK list for a while.  Thankfully you removed all
the items ahead of this one, so I'll think of something to do in 9.2.

Of course we'll be able to use the actual transform code that you
already wrote.



Peter,

Did you make any progress on this?

cheers

andrew

--
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] Measuring relation free space

2011-11-08 Thread Jaime Casanova
On Sun, Nov 6, 2011 at 5:38 AM, Magnus Hagander mag...@hagander.net wrote:

 Looks pretty useful.


thanks for the review, attached is a new version of it

 One quick stylistic comment - we don't generally use * 1.0 to turn
 an int into a double - just use a cast.


ok


 Hooking into a ring buffer seems like an almost requirement before you
 can run this on a larger production system, wouldn't it? I don't know
 how  hard that is code-wise, but it certainly seems worthwhile.


seems it wasn't too difficult... i just have to indicate the right
buffer access strategy so it's using a ring buffer now

-- 
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
diff --git a/contrib/pageinspect/btreefuncs.c b/contrib/pageinspect/btreefuncs.c
new file mode 100644
index dbb2158..8be21ed
*** a/contrib/pageinspect/btreefuncs.c
--- b/contrib/pageinspect/btreefuncs.c
***
*** 34,39 
--- 34,40 
  #include utils/builtins.h
  #include utils/rel.h
  
+ #include btreefuncs.h
  
  extern Datum bt_metap(PG_FUNCTION_ARGS);
  extern Datum bt_page_items(PG_FUNCTION_ARGS);
*** GetBTPageStatistics(BlockNumber blkno, B
*** 155,160 
--- 156,204 
  		stat-avg_item_size = 0;
  }
  
+ /*
+  * GetBTRelationFreeSpace
+  *
+  * Get the free space for a btree index.
+  * This is a helper function for relation_free_space()
+  *
+  */
+ float4
+ GetBTRelationFreeSpace(Relation rel)
+ {
+ 	BTPageStat stat;
+ 
+ 	Buffer		buffer;
+ 	BlockNumber blkno;
+ 	BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ 	Size 		free_space = 0;
+ 	double		free_percent = 0;
+ 
+ BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+ 	
+ 	/* Skip page 0 because it is a metapage */
+ 	for (blkno = 1; blkno  totalBlcksInRelation; blkno++)
+ 	{
+ 		buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ 		/* 
+ 		 * get the statistics of the indexes and use that info
+ 		 * to determine free space on the page
+ 		 */
+ 		GetBTPageStatistics(blkno, buffer, stat);
+ 		if (stat.type == 'd')
+ 			free_space += stat.page_size;
+ 		else
+ 			free_space += stat.free_size;		
+ 
+ 		ReleaseBuffer(buffer);
+ 	}
+ 
+ 	if (totalBlcksInRelation  1)
+ 		free_percent = ((float4) free_space) / ((totalBlcksInRelation - 1) * BLCKSZ);
+ 	return free_percent;
+ }
+ 
+ 
  /* ---
   * bt_page()
   *
diff --git a/contrib/pageinspect/btreefuncs.h b/contrib/pageinspect/btreefuncs.h
new file mode 100644
index ...549f878
*** a/contrib/pageinspect/btreefuncs.h
--- b/contrib/pageinspect/btreefuncs.h
***
*** 0 
--- 1,5 
+ /*
+  * contrib/pageinspect/btreefuncs.h
+  */
+ 
+ float4 GetBTRelationFreeSpace(Relation);
diff --git a/contrib/pageinspect/heapfuncs.c b/contrib/pageinspect/heapfuncs.c
new file mode 100644
index fa50655..e7436fb
*** a/contrib/pageinspect/heapfuncs.c
--- b/contrib/pageinspect/heapfuncs.c
***
*** 28,33 
--- 28,36 
  #include funcapi.h
  #include utils/builtins.h
  #include miscadmin.h
+ #include storage/bufmgr.h
+ 
+ #include heapfuncs.h
  
  Datum		heap_page_items(PG_FUNCTION_ARGS);
  
*** bits_to_text(bits8 *bits, int len)
*** 55,60 
--- 58,96 
  }
  
  
+ /*
+  * GetHeapRelationFreeSpace()
+  *
+  * Get the free space for a heap relation.
+  * This is a helper function for relation_free_space()
+  */
+ float4
+ GetHeapRelationFreeSpace(Relation rel)
+ {
+ 	Buffer  buffer;
+ 	Page		page;
+ 	BlockNumber blkno;
+ 	BlockNumber totalBlcksInRelation = RelationGetNumberOfBlocks(rel);
+ 	Sizefree_space = 0;
+ 	double		free_percent = 0;
+ 
+ 	BufferAccessStrategy bstrategy = GetAccessStrategy(BAS_BULKREAD);
+ 
+ 	for (blkno = 0; blkno  totalBlcksInRelation; blkno++)
+ 	{
+ 		buffer = ReadBufferExtended(rel, MAIN_FORKNUM, blkno, RBM_NORMAL, bstrategy);
+ 		page   = BufferGetPage(buffer);
+ 		free_space += PageGetHeapFreeSpace(page);
+ 
+ 		ReleaseBuffer(buffer);
+ 	}
+ 
+ 	if (totalBlcksInRelation  0)
+ 		free_percent = ((float4) free_space) / (totalBlcksInRelation * BLCKSZ);
+ 	return free_percent;
+ }
+ 
+ 
  /*
   * heap_page_items
   *
diff --git a/contrib/pageinspect/heapfuncs.h b/contrib/pageinspect/heapfuncs.h
new file mode 100644
index ...17b7cb3
*** a/contrib/pageinspect/heapfuncs.h
--- b/contrib/pageinspect/heapfuncs.h
***
*** 0 
--- 1,5 
+ /*
+  * contrib/pageinspect/heapfuncs.h
+  */
+ 
+ float4 GetHeapRelationFreeSpace(Relation);
diff --git a/contrib/pageinspect/pageinspect--1.0.sql b/contrib/pageinspect/pageinspect--1.0.sql
new file mode 100644
index 5613956..4502a13
*** a/contrib/pageinspect/pageinspect--1.0.sql
--- b/contrib/pageinspect/pageinspect--1.0.sql
*** CREATE FUNCTION fsm_page_contents(IN pag
*** 105,107 
--- 105,115 
  RETURNS text
  AS 'MODULE_PATHNAME', 

[HACKERS] Materialized views

2011-11-08 Thread Kevin Grittner
This is the time of year when the Wisconsin Courts formalize their
annual plan for where people will be spending the bulk of their time
in the coming year.  Two years ago at this time, managers decided
that serializable transactions were a big enough issue to justify
assigning about half of my 2011 time to working on PostgreSQL
enhancements for that.  This year our big database issue is
materialized views.
 
As we strive to create our next generation of software we find
ourselves wanting to provide dashboard type windows with graphs of
statistics which are insanely expensive to calculate on the fly. 
We've been creating ad hoc materialized views to deal with the
performance issues, but that is labor intensive.  I'm considering
submitting a proposal to management that I be assigned to work on
a declarative implementation in PostgreSQL to allow speedier
application development of software needing materialized views.
 
I'm posting to make sure that nobody else is already in the midst of
working on this, and to check regarding something on the Wiki page
for this topic:
 
http://wiki.postgresql.org/wiki/Materialized_Views
 
That page describes three components: creating MVs, updating MVs, and
having the planner automatically detect when an MV matches some
portion of a regular query and using the MV instead of the specified
tables in such cases.  I have high confidence that if time is
approved I could do the first two for the 9.3, but that last one
seems insanely complicated and not necessarily a good idea.  (That's
particularly true with some of the lazier strategies for maintaining
the data in the materialized view.)  I don't think we want to use
that 3rd component in our shop, anyway.  So the question is, would a
patch which does the first two without the third be accepted by the
community?
 
I'm not at the point of proposing specifics yet; the first phase
would be a close review of prior threads and work on the topic
(including the GSoC work).  Then I would discuss implementation
details here before coding.
 
The hope on our end, of course, is that the time spent on
implementing this would be more than compensated by application
programmer time savings as we work on our next generation of
application software, which seems like a pretty safe bet to me.
 
-Kevin

-- 
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] Hot Backup with rsync fails at pg_clog if under load

2011-11-08 Thread Greg Smith
I was curious if anyone running into these problems has gotten a chance 
to test the 3 fixes committed here.  It sounded like Linas even had a 
repeatable test case?


For easier reference the commits are:

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=2f55c535e1f026929cf20855b3790d3632062d42

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ff8451aa14c8513e429cbef09ddc72e79da366a5

http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=656bba95af3950b26e9e97d86d29787d89e2b423

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


--
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] Releasing an alpha for CF2

2011-11-08 Thread Alexander Korotkov
On Wed, Nov 9, 2011 at 12:56 AM, Josh Berkus j...@agliodbs.com wrote:

 I'd really like us to release an alpha version based on the finished
 CF2.  There have been a number of major features added to PostgreSQL
 prior to this CF -- including Haas's performance improvements and range
 types -- which we really want at least some users to test early.


People trying range types are likely to try indexes on them. GiST on range
types implementation in head have some limitations:
1) Full index scan for column @ const case.
2) Absence of selectivity estimation functions for , @, @ cause no GiST
index usage until turning enable_seqscan to off.

IMHO, it would be nice to release alpha version with range types without
these limitations. I'm going to finish my patch for GiST on range types in
couple of days. Concerning selectivity estimation, possible solution is to
create some constant estimations with lower selectivity, which make planner
use corresponding index by default (like we've for geometric datatypes).

--
With best regards,
Alexander Korotkov.


Re: [HACKERS] Materialized views

2011-11-08 Thread Thom Brown
On 8 November 2011 21:23, Kevin Grittner kevin.gritt...@wicourts.gov wrote:
 This is the time of year when the Wisconsin Courts formalize their
 annual plan for where people will be spending the bulk of their time
 in the coming year.  Two years ago at this time, managers decided
 that serializable transactions were a big enough issue to justify
 assigning about half of my 2011 time to working on PostgreSQL
 enhancements for that.  This year our big database issue is
 materialized views.

 As we strive to create our next generation of software we find
 ourselves wanting to provide dashboard type windows with graphs of
 statistics which are insanely expensive to calculate on the fly.
 We've been creating ad hoc materialized views to deal with the
 performance issues, but that is labor intensive.  I'm considering
 submitting a proposal to management that I be assigned to work on
 a declarative implementation in PostgreSQL to allow speedier
 application development of software needing materialized views.

 I'm posting to make sure that nobody else is already in the midst of
 working on this, and to check regarding something on the Wiki page
 for this topic:

 http://wiki.postgresql.org/wiki/Materialized_Views

 That page describes three components: creating MVs, updating MVs, and
 having the planner automatically detect when an MV matches some
 portion of a regular query and using the MV instead of the specified
 tables in such cases.  I have high confidence that if time is
 approved I could do the first two for the 9.3, but that last one
 seems insanely complicated and not necessarily a good idea.  (That's
 particularly true with some of the lazier strategies for maintaining
 the data in the materialized view.)  I don't think we want to use
 that 3rd component in our shop, anyway.  So the question is, would a
 patch which does the first two without the third be accepted by the
 community?

 I'm not at the point of proposing specifics yet; the first phase
 would be a close review of prior threads and work on the topic
 (including the GSoC work).  Then I would discuss implementation
 details here before coding.

 The hope on our end, of course, is that the time spent on
 implementing this would be more than compensated by application
 programmer time savings as we work on our next generation of
 application software, which seems like a pretty safe bet to me.

+1

I was pleased to see the subject of this thread.  I definitely think
it's worth it, especially if you're able to make it also work for
foreign tables (saving expense of seeking external data so can also
act as a local cache, but that's me getting carried away).  And I
agree regarding the planner detection.  If that ever were desired, it
certainly would't need implementing in the first phase.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: 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


[HACKERS] Misleading CREATE TABLE error

2011-11-08 Thread Thom Brown
Hi,

I found the following error message misleading:

test=# create table cows2 (LIKE cows);
ERROR:  inherited relation cows is not a table
STATEMENT:  create table cows2 (LIKE cows);

I'm not trying to inherit a relation, I'm trying to base a table on
it.  As it happens, cows is a foreign table, which *is* a table,
just not a regular table.  It might be useful to add support to clone
foreign tables into regular tables, the use-case being that you may
wish to import all the data locally into a table of the same
structure.  But the gripe here is the suggestion that the relation
would have been inherited, which would actually be achieved using
INHERITS.

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: 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] Measuring relation free space

2011-11-08 Thread Robert Treat
On Tue, Nov 8, 2011 at 1:07 PM, Greg Smith g...@2ndquadrant.com wrote:
 On 11/06/2011 11:55 PM, Mark Kirkwood wrote:

 I am guessing (at this point very much guessing) that pg_freespace map may
 return its data faster, as pageinspect is gonna have to grovel through all
 the pages for itself (whereas pg_freespacemap relies on using info from the
 ... free space map fork).

 I started with pageinspect because I wasn't sure if other methods would be
 as accurate.  For example, I wasn't sure until just before submission that
 only the free space and size of the relation are needed to get a useful
 measure here; at one point I was considering some other things too.  I've
 ruled them out as unnecessary as far as I can tell, but I can't claim my
 tests are exhaustive.  Some review confirmation that this measure is useful
 for other people is one thing I was hoping for feedback on, as one thing to
 consider in addition to the actual implementation.

 If this measurement is the only one needed, than as I said at the start of
 the thread here it might easily be implemented to run just against the free
 space map instead.  I'm thinking of what's been sent so far as a UI with
 matching output it should produce.  If it's possible to get the same numbers
 faster, exactly how to implement the function under the hood is easy enough
 to change.  Jaime already has a new version in development that adds a ring
 buffer for example.

It's already easy to get good enough numbers based on user space
tools with very little overhead, so I think it's more important that
the server side tool be accurate rather than fast. Of course, if we
can get both, that's a bonus, but I'd rather not go that route at the
expense of accuracy. Just my .02.

Robert Treat
conjecture: xzilla.net
consulting: omniti.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] Releasing an alpha for CF2

2011-11-08 Thread Josh Berkus
Alexander,

 People trying range types are likely to try indexes on them. GiST on range
 types implementation in head have some limitations:

... it's an alpha.  There will always be one more patch.

-- 
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] Materialized views

2011-11-08 Thread Josh Berkus
On 11/8/11 1:23 PM, Kevin Grittner wrote:
 So the question is, would a
 patch which does the first two without the third be accepted by the
 community?

AFAIC, yes.

For that matter, Part 3 is useful without parts 1 and 2, if someone
wanted to work on that.  I recall some academic work on automated
materialized view matching back in the 7.2 days.

And I can help test whatever you come up with ... I do a lot of matviews.

-- 
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] Materialized views

2011-11-08 Thread Greg Jaskiewicz

On 8 Nov 2011, at 21:23, Kevin Grittner wrote:

 This is the time of year when the Wisconsin Courts formalize their
 annual plan for where people will be spending the bulk of their time
 in the coming year.  Two years ago at this time, managers decided
 that serializable transactions were a big enough issue to justify
 assigning about half of my 2011 time to working on PostgreSQL
 enhancements for that.  This year our big database issue is
 materialized views.


+1 for that too. Too many hacked together MVs here, and more coming. 


-- 
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] Materialized views

2011-11-08 Thread Stephen Frost
* Kevin Grittner (kevin.gritt...@wicourts.gov) wrote:
 So the question is, would a
 patch which does the first two without the third be accepted by the
 community?

I'm about 99% sure the answer to that is 'yes'.  Are you thinking of
having a background scheduler which handles the updating of
schedule-driven (instead of trigger-driven) MVs..?  Not to try to
feature-creep this on you, but you might consider how a new backend
process which handles scheduled tasks could be generalized to go beyond
handling just MV updates.. :)

Thanks!

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] Materialized views

2011-11-08 Thread Kevin Grittner
Stephen Frost sfr...@snowman.net wrote:
 
 Are you thinking of having a background scheduler which handles
 the updating of schedule-driven (instead of trigger-driven) MVs..?
 
I'm trying not to get to far into discussing design in advance of
reviewing previous work and any papers on the topic.  That said, it
seems clear that the above is required but not sufficient.
 
 Not to try to feature-creep this on you, but you might consider
 how a new backend process which handles scheduled tasks could be
 generalized to go beyond handling just MV updates.. :)
 
I'll keep that in mind.  :-)
 
-Kevin

-- 
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] Materialized views

2011-11-08 Thread David E. Wheeler
On Nov 8, 2011, at 2:54 PM, Stephen Frost wrote:

 So the question is, would a
 patch which does the first two without the third be accepted by the
 community?

+1 Definitely.

 I'm about 99% sure the answer to that is 'yes'.  Are you thinking of
 having a background scheduler which handles the updating of
 schedule-driven (instead of trigger-driven) MVs..?  Not to try to
 feature-creep this on you, but you might consider how a new backend
 process which handles scheduled tasks could be generalized to go beyond
 handling just MV updates.. :)

+1 That sure would be nice. Might be some useful stuff in pgAgent to pull into 
this (license permitting).

Best,

David


-- 
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] Materialized views

2011-11-08 Thread Josh Berkus

 I'm about 99% sure the answer to that is 'yes'.  Are you thinking of
 having a background scheduler which handles the updating of
 schedule-driven (instead of trigger-driven) MVs..?  Not to try to
 feature-creep this on you, but you might consider how a new backend
 process which handles scheduled tasks could be generalized to go beyond
 handling just MV updates.. :)

No feature-creeping; the two features described will be hard enough.
Besides, we have pg_agent for that.

-- 
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] ERROR: MergeAppend child's targetlist doesn't match MergeAppend

2011-11-08 Thread Tom Lane
I wrote:
 Not immediately sure what to do about this.  The quick-and-dirty fix
 would be to only apply add_child_rel_equivalences to simple inheritance
 child relations, for which the added items must be Vars and must be
 different (which is what that code is expecting).  Seems like a bit of a
 shame to lobotomize planning for UNION cases, though.  Maybe we need a
 more complicated representation of child EquivalenceClass members.

After some thought and experimentation, the best fix seems to be to
eliminate the ambiguity by wrapping subquery outputs in PlaceHolderVars
whenever there is a risk of confusion.  The attached crude patch fixes
both test cases.  It needs work (more comments and a regression test
case would be good), but barring objection I'll push forward with doing
it this way.

regards, tom lane

diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c
index aeaae8c8d87637b62e02e54b0c50509f98cd0138..5a9605d764ce268466c95fb73f219657a5781c25 100644
*** a/src/backend/optimizer/prep/prepjointree.c
--- b/src/backend/optimizer/prep/prepjointree.c
*** pull_up_simple_subquery(PlannerInfo *roo
*** 784,805 
  	parse-havingQual = pullup_replace_vars(parse-havingQual, rvcontext);
  
  	/*
! 	 * Replace references in the translated_vars lists of appendrels. When
! 	 * pulling up an appendrel member, we do not need PHVs in the list of the
! 	 * parent appendrel --- there isn't any outer join between. Elsewhere, use
! 	 * PHVs for safety.  (This analysis could be made tighter but it seems
! 	 * unlikely to be worth much trouble.)
  	 */
  	foreach(lc, root-append_rel_list)
  	{
  		AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
- 		bool		save_need_phvs = rvcontext.need_phvs;
  
- 		if (appinfo == containing_appendrel)
- 			rvcontext.need_phvs = false;
  		appinfo-translated_vars = (List *)
  			pullup_replace_vars((Node *) appinfo-translated_vars, rvcontext);
- 		rvcontext.need_phvs = save_need_phvs;
  	}
  
  	/*
--- 784,799 
  	parse-havingQual = pullup_replace_vars(parse-havingQual, rvcontext);
  
  	/*
! 	 * Replace references in the translated_vars lists of appendrels, too.
! 	 * We must preserve the original AppendRelInfo structs, so we have to do
! 	 * it this way.
  	 */
  	foreach(lc, root-append_rel_list)
  	{
  		AppendRelInfo *appinfo = (AppendRelInfo *) lfirst(lc);
  
  		appinfo-translated_vars = (List *)
  			pullup_replace_vars((Node *) appinfo-translated_vars, rvcontext);
  	}
  
  	/*
*** pullup_replace_vars_callback(Var *var,
*** 1407,1420 
  			if (newnode  IsA(newnode, Var) 
  ((Var *) newnode)-varlevelsup == 0)
  			{
! /* Simple Vars always escape being wrapped */
! wrap = false;
  			}
  			else if (newnode  IsA(newnode, PlaceHolderVar) 
  	 ((PlaceHolderVar *) newnode)-phlevelsup == 0)
  			{
  /* No need to wrap a PlaceHolderVar with another one, either */
! wrap = false;
  			}
  			else if (rcon-wrap_non_vars)
  			{
--- 1401,1416 
  			if (newnode  IsA(newnode, Var) 
  ((Var *) newnode)-varlevelsup == 0)
  			{
! /* Simple Vars always escape being wrapped, unless dup */
! wrap = (rcon-wrap_non_vars 
! 		tlist_member(newnode, rcon-targetlist) != tle);
  			}
  			else if (newnode  IsA(newnode, PlaceHolderVar) 
  	 ((PlaceHolderVar *) newnode)-phlevelsup == 0)
  			{
  /* No need to wrap a PlaceHolderVar with another one, either */
! wrap = (rcon-wrap_non_vars 
! 		tlist_member(newnode, rcon-targetlist) != tle);
  			}
  			else if (rcon-wrap_non_vars)
  			{
diff --git a/src/backend/optimizer/util/placeholder.c b/src/backend/optimizer/util/placeholder.c
index 8b65245b5107595da7e15d09d009446ca0d701c7..a5d35f0783cc5b53094ed4c5790cdb48aad7069e 100644
*** a/src/backend/optimizer/util/placeholder.c
--- b/src/backend/optimizer/util/placeholder.c
*** find_placeholders_in_jointree(PlannerInf
*** 120,125 
--- 120,128 
  		Assert(root-parse-jointree != NULL 
  			   IsA(root-parse-jointree, FromExpr));
  		(void) find_placeholders_recurse(root, (Node *) root-parse-jointree);
+ 
+ 		/* Also set up PlaceHolderInfos for PHVs in append_rel_list */
+ 		mark_placeholders_in_expr(root, (Node *) root-append_rel_list, NULL);
  	}
  }
  

-- 
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] Materialized views

2011-11-08 Thread David E. Wheeler
On Nov 8, 2011, at 3:25 PM, Josh Berkus wrote:

 I'm about 99% sure the answer to that is 'yes'.  Are you thinking of
 having a background scheduler which handles the updating of
 schedule-driven (instead of trigger-driven) MVs..?  Not to try to
 feature-creep this on you, but you might consider how a new backend
 process which handles scheduled tasks could be generalized to go beyond
 handling just MV updates.. :)
 
 No feature-creeping; the two features described will be hard enough.
 Besides, we have pg_agent for that.

Well, there has to be some way to refresh an MV at regular intervals, no?

Best,

David


-- 
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] Measuring relation free space

2011-11-08 Thread Greg Smith

On 11/08/2011 05:07 PM, Robert Treat wrote:

It's already easy to get good enough numbers based on user space
tools with very little overhead, so I think it's more important that
the server side tool be accurate rather than fast.


What user space method do you consider good enough here?  I haven't 
found any approximation that I was really happy with; wouldn't have 
bothered with this otherwise.


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


--
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] Materialized views

2011-11-08 Thread David E. Wheeler
On Nov 8, 2011, at 4:23 PM, Josh Berkus wrote:

 Well, there has to be some way to refresh an MV at regular intervals, no?
 
 For periodic update matviews, yes.  However, Kevin needs only produce
 the command/function call for updating a generic periodic matview.  He
 doesn't need to provide a scheduling utility.  For simple cases, cron works.

Oh, I see. I was assuming it would be automatic-ish, with the declaration of 
the matview including update interval information. Could certainly come later, 
though.

Best,

David


-- 
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] Materialized views

2011-11-08 Thread Josh Berkus

 No feature-creeping; the two features described will be hard enough.
 Besides, we have pg_agent for that.
 
 Well, there has to be some way to refresh an MV at regular intervals, no?

For periodic update matviews, yes.  However, Kevin needs only produce
the command/function call for updating a generic periodic matview.  He
doesn't need to provide a scheduling utility.  For simple cases, cron works.

-- 
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] 9.1.2 ?

2011-11-08 Thread Tom Lane
Greg Jaskiewicz g...@pointblue.com.pl writes:
 Given the amount of fixes that went into the branch, and importance of them - 
 when can we expect 9.1.2 to be released officially ?
 9.1.1 was stamped on 22nd of September. 

That's barely more than six weeks ago.  Usually, in the absence of any
seriously nasty bugs, Postgres update releases are three months or more
apart; more often than that puts undue load on our downstream packagers.
I don't recall that we've fixed anything since September that seemed to
warrant an immediate release.

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] lexemes in prefix search going through dictionary modifications

2011-11-08 Thread Tom Lane
Sushant Sinha sushant...@gmail.com writes:
 I think there is a need to provide prefix search to bypass
 dictionaries.If you folks think that there is some credibility to such a
 need then I can think about implementing it. How about an operator like
 :# that does this? The :* will continue to mean the same as
 currently.

I don't think that just turning off dictionaries for prefix searches is
going to do much of anything useful, because the lexemes in the index
are still going to have gone through normalization.  Somehow we need to
identify which lexemes could match the prefix after accounting for the
fact that they've been through normalization.

An example: if the original word is transferring, the lexeme (in the
english config) is just transfer.  If you search for transferring:*
and suppress dictionaries, you'll fail to get a match, which is simply
wrong.  It's not a step forward to suppress some failure cases while
adding new ones.

Another point is that whatever we do about this really ought to be
inside the engine, not exposed in a form that makes users do their
queries differently.

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] Materialized views

2011-11-08 Thread Andrew Dunstan



On 11/08/2011 06:25 PM, Josh Berkus wrote:

I'm about 99% sure the answer to that is 'yes'.  Are you thinking of
having a background scheduler which handles the updating of
schedule-driven (instead of trigger-driven) MVs..?  Not to try to
feature-creep this on you, but you might consider how a new backend
process which handles scheduled tasks could be generalized to go beyond
handling just MV updates.. :)

No feature-creeping; the two features described will be hard enough.
Besides, we have pg_agent for that.



Yeah. I'd be plenty happy with greedy MVs plus lazy MVs that need a 
manual refresh. These are both patterns I use a lot (the buildfarm 
dashboard is in effect a greedy MV although it's not updated by a trigger).


cheers

andrew



--
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] pg_comments (was: Allow \dd to show constraint comments)

2011-11-08 Thread Thom Brown
On 17 October 2011 03:04, Robert Haas robertmh...@gmail.com wrote:
 On Fri, Oct 14, 2011 at 11:12 AM, Robert Haas robertmh...@gmail.com wrote:
 On Wed, Oct 12, 2011 at 10:20 PM, Josh Kupershmidt schmi...@gmail.com 
 wrote:
 On the third hand, Josh's previous batch of changes to clean up
 psql's behavior in this area are clearly a huge improvement: you can
 now display the comment for nearly anything by running the appropriate
 \dfoo command for whatever the object type is.  So ... is this still
 a good idea, or should we just forget about it?

 I think this question is a part of a broader concern, namely do we
 want to create and support system views for easier access to
 information which is already available in different ways through psql
 commands, or by manually digging around in the catalogs? I believe
 there are at least several examples of existing views we maintain
 which are very similar to pg_comments: pg_seclabel seems quite
 similar, for instance.

 That's one's a direct analogue, but I don't want to overbroaden the
 issue.  I guess it just seems to me that if no one's going to champion
 adding this, maybe we shouldn't.

 Hearing no cries of oh, yes, please, I'm marking this Returned with
 Feedback for now.  We can always revisit it if we hear that more
 people want it.

Why is this marked as done on with Wiki's todo list?

-- 
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

EnterpriseDB UK: 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] Materialized views

2011-11-08 Thread Stephen Frost
* Josh Berkus (j...@agliodbs.com) wrote:
  Well, there has to be some way to refresh an MV at regular intervals, no?
 
 For periodic update matviews, yes.  However, Kevin needs only produce
 the command/function call for updating a generic periodic matview.  He
 doesn't need to provide a scheduling utility.  For simple cases, cron works.

Perhaps that would be an acceptable initial version, but I'd be pretty
disappointed and I certainly don't think we should stop there.  

I'm quite aware of cron and as aware of the difficulties that many DBAs
are going to have getting cronjobs implemented.  There's permission
issues (we see this already with the requests to provide an in-PG DBA
interface for pg_hba.conf..), locking issues (writing decent scripts
that don't destroy the box if they take a bit too long, like 5m on a
5m scheduled job), authentication issues (we don't really want these
running as superuser if we can avoid it..), and probably other things
I'm not thinking of.

Thanks,

Stephen


signature.asc
Description: Digital signature


Re: [HACKERS] 9.1.2 ?

2011-11-08 Thread Greg Smith

On 11/08/2011 07:34 PM, Tom Lane wrote:

I don't recall that we've fixed anything since September that seemed to
warrant an immediate release.
   


The backup+pg_clog failure issues fixed last week have been a nasty 
problem hitting people for a while.  Backup corruption is obviously 
serious.  Only reason I think it wasn't a higher priority issue is that 
it didn't happen every time, and the people impacted were eventually 
able to work around it.  Concern about that problem is why I popped off 
a message earlier today, about whether the fixes committed have been 
confirmed outside of Simon's own testing.


I was curious how 9.0 fared last year for comparison, here's that data:

Version Date  Days  Weeks
9.0.009/20/10
9.0.110/04/10142.0
9.0.212/16/107310.4
9.0.301/31/11466.6
9.0.404/18/117711.0
9.0.509/26/11161   23.0

So the average for the first three point releases was around 6 weeks apart.

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


--
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] Materialized views

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 8:31 PM, Stephen Frost sfr...@snowman.net wrote:
 Perhaps that would be an acceptable initial version, but I'd be pretty
 disappointed and I certainly don't think we should stop there.

I agree that a built-in job scheduler would be pretty awesome, but I
think it's a completely separate project from materialized views.
Each of the two is a major project all by itself; making one into a
dependency of the other is a recipe for failure.

In view of Kevin's success with SSI, I'm very pleased to see him
picking this out as his next target.  If and when it gets done, this
will be a great, great feature.  And we shouldn't be afraid to start
small and move incrementally toward what we really want.

-- 
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] 9.1.2 ?

2011-11-08 Thread Tom Lane
Greg Smith g...@2ndquadrant.com writes:
 I was curious how 9.0 fared last year for comparison, here's that data:

 Version Date  Days  Weeks
 9.0.009/20/10
 9.0.110/04/10142.0
 9.0.212/16/107310.4
 9.0.301/31/11466.6
 9.0.404/18/117711.0
 9.0.509/26/11161   23.0

 So the average for the first three point releases was around 6 weeks apart.

The 9.0.1 and 9.0.3 releases were both forced by security issues,
so I think that's an unusually low average.

Having said that, if enough people think that those backup issues are
critical-data-loss problems, I won't stand in the way of making a
release now.  But like you, I'm not exactly convinced we're done with
those issues.

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] Releasing an alpha for CF2

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 3:56 PM, Josh Berkus j...@agliodbs.com wrote:
 I'd really like us to release an alpha version based on the finished
 CF2.  There have been a number of major features added to PostgreSQL
 prior to this CF -- including Haas's performance improvements and range
 types -- which we really want at least some users to test early.

 Would it be possible to do an alpha release?

Well, we discussed this not long ago, and the conclusion was that we
could do an alpha release if and when someone was willing to write
release notes.  I suggested that it would make sense to do our first
alpha after CF3, and that's still pretty much how I feel about it.  If
we're lucky, maybe we can twist Bruce's arm into doing it.
Regardless, I don't think there's much time in trying to squeeze it
into the week remaining before CF3, though; anything we write now will
be obsolete almost as soon as it's written.

An interesting question is this: What fraction of what is ultimately
going to end up in this release is done now, or what fraction will be
done by the end of CF3?  Last time around, all the major features kind
of fell on our head in January.  But some of those - like SSI and sync
rep - were things that we knew to be coming long in advance.  I know
various people are working on patches to do various things, but the
only outstanding thing that sticks out in my mind as an outstanding
big feature patch is Alvaro's work on foreign key locks.  I'm sure
there must be others...  but I'm not sure how many.

-- 
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] proposal: psql concise mode

2011-11-08 Thread Josh Kupershmidt
On Mon, Nov 7, 2011 at 11:25 PM, Robert Haas robertmh...@gmail.com wrote:
 But I can't help feeling that as we continue to add more features,
 we've eventually going to end up with our backs to the wall.  Not sure
 what to do about that, but...

Seriously, parts of psql are starting to become a real mess.

[tangentially related rant]

I cringe whenever I have to go digging around in describe.c.
describeOneTableDetails() is what, 1100+ lines?! Doubtless, some
refactoring of that function would help. But the
backwards-compatibility burden isn't helping the situation. The first
conditional block based on pset.sversion in that function contains:

...
else if (pset.sversion = 8)
{
[some query against pg_catalog.pg_class]
}
else
{
printfPQExpBuffer(buf,
  SELECT relchecks, relkind, relhasindex, relhasrules, 
  reltriggers  0, relhasoids, 
  '', ''\n
  FROM pg_catalog.pg_class WHERE oid = '%s';,
  oid);
}

We're essentially pretending that we support all server versions with
this code, instead of erroring out on some definite old version and
admitting sorry, can't do it. The latter query would really break on
a 7.1 [*] or earlier server (thanks to relhasoids). Other pieces of
the same function would fail on 7.2 or earlier, e.g. due to querying
pg_depend or pg_namespace. Other code will fail on 7.3 or earlier,
e.g. due to querying pg_user.

I think we should draw a line somewhere about just how far back psql
must support, and don't worry about having crufty maybe it works but
who knows exactly how far back code for further support than that. I
think 8.0 would be a very generous backwards compatibility target.

Josh

--
[*] I based these claims about how far back the code would actually
work based on perusing old catalog doc pages, like:
http://www.postgresql.org/docs/7.3/static/catalogs.html
It's possible some of the old doc pages are incorrect, but I think my
point still stands.

-- 
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] pg_comments (was: Allow \dd to show constraint comments)

2011-11-08 Thread Robert Haas
On Tue, Nov 8, 2011 at 8:12 PM, Thom Brown t...@linux.com wrote:
 Why is this marked as done on with Wiki's todo list?

I assume you're referring to this item:

--
\dd is missing comments for several types of objects.  Comments are
not handled at all for some object types, and are handled by both \dd
and the individual backslash command for others. Consider a system
view like pg_comments to manage this mess.
--

What we did is modify psql so that all the backslash commands display
comments for their corresponding object types, at least when the +
modifier is used.  We then made \dd display comments for all object
types that lack their own backslash commands.  That seems like it
pretty much covers the todo item.

I initially thought that pg_comments could be used *by psql*, which
seemed to me a good argument in favor of its general utility.  When we
didn't end up doing it that way, the view got less appealing to me.
I'm still willing to add it if enough people say they want it, but so
far I think we've gotten about three votes between +0.25 and +0.50 and
a -1 from Tom.  Which is not really enough for me to feel like pulling
the trigger.

-- 
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] proposal: psql concise mode

2011-11-08 Thread Tom Lane
Josh Kupershmidt schmi...@gmail.com writes:
 We're essentially pretending that we support all server versions with
 this code, instead of erroring out on some definite old version and
 admitting sorry, can't do it. ...
 I think we should draw a line somewhere about just how far back psql
 must support,

Says right at the top of the file:

 * Support for the various \d (describe) commands.  Note that the current
 * expectation is that all functions in this file will succeed when working
 * with servers of versions 7.4 and up.  It's okay to omit irrelevant
 * information for an old server, but not to fail outright.

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] Materialized views

2011-11-08 Thread Greg Smith

On 11/08/2011 04:23 PM, Kevin Grittner wrote:

http://wiki.postgresql.org/wiki/Materialized_Views
That page describes three components: creating MVs, updating MVs, and
having the planner automatically detect when an MV matches some
portion of a regular query and using the MV instead of the specified
tables in such cases.


So far that page is just my initial notes on this subject based on some 
research I did, don't read too much into it.  Ignoring MV substitution 
is certainly the right plan for an initial development target.  An 
implementation that didn't update automatically at all is also still a 
useful step.  It's very easy to pick too big of a chunk of this project 
to chew on at once.


When I wrote that, it wasn't completely clear to me yet that doing the 
updates would involve so many of the same tricky bits that stalled 
progress on the MERGE command.  Nowadays I think of working out the 
concurrency issues in both this and MERGE, in the wake of true 
serialization, as another step in this implementation plan.  It's not 
strictly necessary, but there's some shared issues that might be tackled 
easier in the MERGE context instead.


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


--
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] Releasing an alpha for CF2

2011-11-08 Thread Greg Smith

On 11/08/2011 09:31 PM, Robert Haas wrote:

I know various people are working on patches to do various things, but the
only outstanding thing that sticks out in my mind as an outstanding
big feature patch is Alvaro's work on foreign key locks.  I'm sure
there must be others...  but I'm not sure how many.
   


Along with that, there's a lot of potential for ripple from the Command 
Triggers patch Dimitri just submitted today.  That's already 
approaching 3000 lines of code and he's still going on it.  I've been 
sniffing around recently looking for troublesome patches, under the 
assumption I'll be running the CF next week, and those two are the ones 
I've been most concerned about.


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


--
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] type privileges and default privileges

2011-11-08 Thread Peter Eisentraut
On mån, 2011-11-07 at 12:21 -0500, Robert Haas wrote:
  As I'm plotting to write code for this, I wonder about how to handle
  default privileges.  For compatibility and convenience, we would
 still
  want to have types with public privileges by default.  Should we
  continue to hardcode this, as we have done in the past with
 functions,
  for example, or should we use the new default privileges facility to
  register the public default privileges in the template database?
 
 I think it would make sense to follow the model of default privileges,
 but I'm a bit confused by the rest of this, because pg_default_acl is
 normally empty - you only make an entry there when a schema has
 different defaults than the, uh, default defaults.  So you shouldn't
 need to register anything, I wouldn't think.

Let me put this differently.  Should we either continue to hardcode the
default privileges in the acldefault() function, or should we instead
initialize the system catalogs with an entry in pg_default_acl as though
ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
executed?



-- 
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] 9.1.2 ?

2011-11-08 Thread Magnus Hagander
On Nov 9, 2011 3:25 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 Greg Smith g...@2ndquadrant.com writes:
  I was curious how 9.0 fared last year for comparison, here's that data:

  Version Date  Days  Weeks
  9.0.009/20/10
  9.0.110/04/10142.0
  9.0.212/16/107310.4
  9.0.301/31/11466.6
  9.0.404/18/117711.0
  9.0.509/26/11161   23.0

  So the average for the first three point releases was around 6 weeks
apart.

 The 9.0.1 and 9.0.3 releases were both forced by security issues,
 so I think that's an unusually low average.

 Having said that, if enough people think that those backup issues are
 critical-data-loss problems, I won't stand in the way of making a
 release now.  But like you, I'm not exactly convinced we're done with
 those issues.


I definitely think they are important enough to trigger a release. But as
you say, I think we need confirmation that they actually fix the problem...

/Magnus


Re: [HACKERS] type privileges and default privileges

2011-11-08 Thread Tom Lane
Peter Eisentraut pete...@gmx.net writes:
 Let me put this differently.  Should we either continue to hardcode the
 default privileges in the acldefault() function, or should we instead
 initialize the system catalogs with an entry in pg_default_acl as though
 ALTER DEFAULT PRIVILEGES GRANT USAGE ON TYPES TO PUBLIC; had been
 executed?

If you're proposing to replace acldefault() with a catalog lookup,
I vote no.  I think that's a performance hit with little redeeming
social value.

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] ERROR: MergeAppend child's targetlist doesn't match MergeAppend

2011-11-08 Thread Teodor Sigaev

After some thought and experimentation, the best fix seems to be to
eliminate the ambiguity by wrapping subquery outputs in PlaceHolderVars
whenever there is a risk of confusion.  The attached crude patch fixes
both test cases.  It needs work (more comments and a regression test
case would be good), but barring objection I'll push forward with doing
it this way.


Thank you, your patch fixes original query too.

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

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