[HACKERS] DatumGetInetP buggy
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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/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
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
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
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
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
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
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
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
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
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
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
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
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)
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
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
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/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
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
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
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
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 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
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
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
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 ?
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
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
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
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
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
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
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
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
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
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
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
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
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
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
* 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
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
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
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
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
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
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
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
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 ?
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
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] pg_comments (was: Allow \dd to show constraint comments)
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
* 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 ?
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
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 ?
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
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
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)
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
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
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
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
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 ?
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
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
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
