Re: [HACKERS] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Wed, 2010-07-07 at 22:26 -0400, Robert Haas wrote: Rereading the thread, I'm a bit confused by why we're proposing to use a SHARE lock; it seems to me that a self-conflicting lock type would simplify things. There's a bunch of discussion on the thread about how to handle pg_class updates atomically, but doesn't using a self-conflicting lock type eliminate that problem? The use of the SHARE lock had nothing to do with the pg_class update requirement, so that suggestion won't help there. It strikes me that for the following operations, which don't affect queries at all, we could use a SHARE UPDATE EXCLUSIVE, which is likely superior to SHARE for this purpose because it wouldn't lock out concurrent DML write operations: Yes, we can also use SHARE UPDATE EXCLUSIVE for some of them. The use of SHARE lock was specifically targeted at ADD FOREIGN KEY, to allow multiple concurrent FKs. Not much use for production however, so SUE looks better to me. Not sure I agree with the don't affect queries at all bit I'll take my previous patch through to completion now, I'm funded to do that work now. Sept commitfest though. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] keepalive in libpq using
On Wed, Jul 7, 2010 at 10:04 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub pa...@microolap.com wrote: While I'm very excited about enabling keepalives in libpq, I want to know how can I use this functionality in my application? Let's imagine that I connect to a server with keepalives option, other options (keepalives_idle, keepalives_interval, keepalives_count) are used either. Then network goes down. So, how will I know that connection is dead? Any callback function? Or should I check PQstatus periodically? I'm not sure, exactly. I think what'll happen is that if you're trying to read data from the remote server, the connection will eventually break instead of hanging forever, but I'm not exactly sure what that'll look like at the libpq level. I'm not sure what effect it'll have on an idle connection. When network goes down while receiving result from server, you will get PGRES_FATAL_ERROR from PQresultStatus(). Also you can get the error message could not receive data from server: Connection timed out via PQerrorMessage(). Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] patch (for 9.1) string functions
Pavel Stehule pavel.steh...@gmail.com wrote:
updated version, concat function doesn't use separator
BTW, didn't you forget stringfunc.sql.in for contrib/stringfunc ?
So, I have not check stringfunc module yet.
I reviewed your patch, and format() in the core is almost ok. It's very cool!
On the other hand, contrib/stringfunc tries to implement safe-sprintf. It's
very complex, and I have questions about multi-byte character handling in it.
* How to print NULL value.
format() function prints NULL as NULL, but RAISE statement in PL/pgSQL
does as NULL. Do we need the same result for them?
postgres=# SELECT format('% vs %', 'NULL', NULL);
format
--
NULL vs NULL
(1 row)
postgres=# DO $$ BEGIN RAISE NOTICE '% vs %', 'NULL', NULL; END; $$;
NOTICE: NULL vs NULL
DO
* Error messages: too few/many parameters
For the same reason, too few/many parameters specified for format()
might be better for the messages.
For RAISE in PL/pgSQL:
ERROR: too few parameters specified for RAISE
ERROR: too many parameters specified for RAISE
* Why do you need convert multi-byte characters to wide char?
Length specifier in stringfunc_sprintf() means character length.
But is pg_encoding_mbcliplen() enough for the purpose?
* Character-length vs. disp-length in length specifier for sprintf()
For example, '%10s' for sprintf() means 10 characters in the code.
But there might be usages to format text values for display. In such
case, display length might be better for the length specifier.
How about having both s and S?
%10s -- 10 characters
%10S -- 10 disp length; we could use pg_dsplen() for the purpse.
Regards,
---
Takahiro Itagaki
NTT Open Source Software Center
--
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 for 9.1: WAL streaming from WAL buffers
On Thu, Jul 8, 2010 at 7:55 AM, Robert Haas robertmh...@gmail.com wrote: What was the final decision on behavior if fsync=off? I'm not sure we made any decision, per se, but if you use fsync=off in combination with SR and experience an unexpected crash-and-reboot on the master, you will be sad. True. But, without SR, an unexpected crash-and-reboot in the master would make you sad ;) So I'm not sure whether we really need to take action for the case of SR + fsync=off. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] keepalive in libpq using
Hello, Fujii. You wrote: FM On Wed, Jul 7, 2010 at 10:04 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Jul 6, 2010 at 1:08 PM, Pavel Golub pa...@microolap.com wrote: While I'm very excited about enabling keepalives in libpq, I want to know how can I use this functionality in my application? Let's imagine that I connect to a server with keepalives option, other options (keepalives_idle, keepalives_interval, keepalives_count) are used either. Then network goes down. So, how will I know that connection is dead? Any callback function? Or should I check PQstatus periodically? I'm not sure, exactly. I think what'll happen is that if you're trying to read data from the remote server, the connection will eventually break instead of hanging forever, but I'm not exactly sure what that'll look like at the libpq level. I'm not sure what effect it'll have on an idle connection. FM When network goes down while receiving result from server, you will FM get PGRES_FATAL_ERROR from PQresultStatus(). Also you can get the FM error message could not receive data from server: Connection timed out FM via PQerrorMessage(). Sounds good for me. My customer proposed such a scenario: I have opened connection to database server (ver 8.4.3) through the SSH tunnel. This tunnel is created by external program PUTTY. My PC running Application is connected to the ETH switch and server is connected to another port of the switch. So, when I disconnect server from the switch, my PC is still online (I mean ETH port have the link). So, my local side of the SSH tunnel is still alive, but remote side is down... So no connection to server is possible at this moment. But in this scenario, when I do something like this: PQExec(...); Application stay locked on this command. Looks like client is still waiting answer from the server in the case the TCP connection is still alive, even if Server is not accessible. The question is: Can this situation be solved with keealives? FM Regards, -- With best wishes, Pavel mailto:pa...@gf.microolap.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] keepalive in libpq using
On Thu, Jul 8, 2010 at 4:57 PM, Pavel Golub pa...@microolap.com wrote: Sounds good for me. My customer proposed such a scenario: I have opened connection to database server (ver 8.4.3) through the SSH tunnel. This tunnel is created by external program PUTTY. My PC running Application is connected to the ETH switch and server is connected to another port of the switch. So, when I disconnect server from the switch, my PC is still online (I mean ETH port have the link). So, my local side of the SSH tunnel is still alive, but remote side is down... So no connection to server is possible at this moment. But in this scenario, when I do something like this: PQExec(...); Application stay locked on this command. Looks like client is still waiting answer from the server in the case the TCP connection is still alive, even if Server is not accessible. The question is: Can this situation be solved with keealives? AFAIK, keepalive works only if there is no un-Acked data pending. If network goes down before sending query to server (i.e., calling PQexec), the ACK for the query doesn't arrive, so you cannot detect the disconnection via keepalive while waiting for the result of the query. OTOH, if network goes down after sending query, keepalive would make you detect the disconnection. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] keepalive in libpq using
On Thu, Jul 8, 2010 at 10:23, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Jul 8, 2010 at 4:57 PM, Pavel Golub pa...@microolap.com wrote: Sounds good for me. My customer proposed such a scenario: I have opened connection to database server (ver 8.4.3) through the SSH tunnel. This tunnel is created by external program PUTTY. My PC running Application is connected to the ETH switch and server is connected to another port of the switch. So, when I disconnect server from the switch, my PC is still online (I mean ETH port have the link). So, my local side of the SSH tunnel is still alive, but remote side is down... So no connection to server is possible at this moment. But in this scenario, when I do something like this: PQExec(...); Application stay locked on this command. Looks like client is still waiting answer from the server in the case the TCP connection is still alive, even if Server is not accessible. The question is: Can this situation be solved with keealives? AFAIK, keepalive works only if there is no un-Acked data pending. If network goes down before sending query to server (i.e., calling PQexec), the ACK for the query doesn't arrive, so you cannot detect the disconnection via keepalive while waiting for the result of the query. OTOH, if network goes down after sending query, keepalive would make you detect the disconnection. In this scenario, wouldn't it work if the *ssh connection* had keepalives enabled though? Then that one should tear down, which in turn would send a clear signal to libpq the connection is gone. -- 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] patch: preload dictionary new version
Hello
2010/7/8 Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp:
Pavel Stehule pavel.steh...@gmail.com wrote:
this version has enhanced AllocSet allocator - it can use a mmap API.
I review your patch and will report some comments. However, I don't have
test cases for the patch because there is no large dictionaries in the
default postgres installation. I'd like to ask you to supply test data
for the patch.
you can use a Czech dictionary - please, download it from
http://www.pgsql.cz/data/czech.tar.gz
CREATE TEXT SEARCH DICTIONARY cspell
(template=ispell, dictfile = czech, afffile=czech, stopwords=czech);
CREATE TEXT SEARCH CONFIGURATION cs (copy=english);
ALTER TEXT SEARCH CONFIGURATION cs
ALTER MAPPING FOR word, asciiword WITH cspell, simple;
postgres=# select * from ts_debug('cs','Příliš žluťoučký kůň se napil
žluté vody');
alias |description| token | dictionaries |
dictionary | lexemes
---+---+---+-++-
word | Word, all letters | Příliš| {cspell,simple} | cspell
| {příliš}
blank | Space symbols | | {} ||
word | Word, all letters | žluťoučký | {cspell,simple} | cspell
| {žluťoučký}
blank | Space symbols | | {} ||
word | Word, all letters | kůň | {cspell,simple} | cspell
| {kůň}
blank | Space symbols | | {} ||
asciiword | Word, all ASCII | se| {cspell,simple} | cspell | {}
blank | Space symbols | | {} ||
asciiword | Word, all ASCII | napil | {cspell,simple} | cspell
| {napít}
blank | Space symbols | | {} ||
word | Word, all letters | žluté | {cspell,simple} | cspell
| {žlutý}
blank | Space symbols | | {} ||
asciiword | Word, all ASCII | vody | {cspell,simple} | cspell
| {voda}
This patch allocates memory with non-file-based mmap() to preload text search
dictionary files at the server start. Note that dist files are not mmap'ed
directly in the patch; mmap() is used for reallocatable shared memory.
The dictinary loader is also modified a bit to use simple_alloc() instead
of palloc() for long-lived cache. It can reduce calls of AllocSetAlloc(),
that have some overheads to support pfree(). Since the cache is never
released, simple_alloc() seems to have better performance than palloc().
Note that the optimization will also work for non-preloaded dicts.
it produce little bit better spead, but mainly it significant memory
reduction - palloc allocation is expensive, because add 4 bytes (8
bytes) to any allocations. And it is problem for thousands smalls
blocks like TSearch ispell dictionary uses. On 64 bit the overhead is
horrible
=== Questions ===
- How do backends share the dict cache? You might expect postmaster's
catalog is inherited to backends with fork(), but we don't use fork()
on Windows.
I though about some variants
a) using a shared memory - but it needs more shared memory
reservation, maybe some GUC - but this variant was refused in
discussion.
b) using a mmap on Unix and CreateFileMapping API on windows - but it
is little bit problem for me. I am not have a develop tools for ms
windows. And I don't understand to MS Win platform :(
Magnus, can you do some tip?
Without MSWindows we don't need to solve a shared memory and can use
only fork. If we can think about MSWin too, then we have to calculate
only with some shared memory based solution. But it has more
possibilities - shared dictionary can be loaded in runtime too.
- Why are SQL functions dpreloaddict_init() and dpreloaddict_lexize()
defined but not used?
it is used, if I remember well. It uses ispell dictionary API. The
using is simlyfied - you can parametrize preload dictionary - and then
you use a preloaded dictionary - not some specific dictionary. This
has one advantage and one disadvantage + very simple configuration, +
there are not necessary some shared dictionary manager, - only one
preload dictionary can be used.
=== Design ===
- You added 3 custom parameters (dict_preload.dictfile/afffile/stopwords),
but I think text search configuration names is better than file names.
However, it requires system catalog access but we cannot access any
catalog at the moment of preloading. If config-name-based setting is
difficult, we need to write docs about where we can get the dict names
to be preloaded instead. (from \dFd+ ?)
yes - it is true argument - there are not possible access to these
data in preloaded time. I would to support preloading - (and possible
support sharing session loaded dictionaries), because it ensure a
constant time for TSearch queries everytime. Yes, some documentation,
some enhancing of dictionary list info can be solution.
Re: [HACKERS] keepalive in libpq using
On Thu, Jul 8, 2010 at 5:27 PM, Magnus Hagander mag...@hagander.net wrote: In this scenario, wouldn't it work if the *ssh connection* had keepalives enabled though? Then that one should tear down, which in turn would send a clear signal to libpq the connection is gone. Sorry, I'm not sure that. Can the ssh connection detect the disconnection via keepalive, in that case? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- 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] patch: preload dictionary new version
Hello
I found a page http://www.genesys-e.org/jwalter//mix4win.htm where is
section Emulation of mmap/munmap. Can be a solution?
Regards
Pavel Stehule
2010/7/8 Pavel Stehule pavel.steh...@gmail.com:
Hello
2010/7/8 Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp:
Pavel Stehule pavel.steh...@gmail.com wrote:
this version has enhanced AllocSet allocator - it can use a mmap API.
I review your patch and will report some comments. However, I don't have
test cases for the patch because there is no large dictionaries in the
default postgres installation. I'd like to ask you to supply test data
for the patch.
you can use a Czech dictionary - please, download it from
http://www.pgsql.cz/data/czech.tar.gz
CREATE TEXT SEARCH DICTIONARY cspell
(template=ispell, dictfile = czech, afffile=czech, stopwords=czech);
CREATE TEXT SEARCH CONFIGURATION cs (copy=english);
ALTER TEXT SEARCH CONFIGURATION cs
ALTER MAPPING FOR word, asciiword WITH cspell, simple;
postgres=# select * from ts_debug('cs','Příliš žluťoučký kůň se napil
žluté vody');
alias | description | token | dictionaries |
dictionary | lexemes
---+---+---+-++-
word | Word, all letters | Příliš | {cspell,simple} | cspell
| {příliš}
blank | Space symbols | | {} | |
word | Word, all letters | žluťoučký | {cspell,simple} | cspell
| {žluťoučký}
blank | Space symbols | | {} | |
word | Word, all letters | kůň | {cspell,simple} | cspell
| {kůň}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | se | {cspell,simple} | cspell | {}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | napil | {cspell,simple} | cspell
| {napít}
blank | Space symbols | | {} | |
word | Word, all letters | žluté | {cspell,simple} | cspell
| {žlutý}
blank | Space symbols | | {} | |
asciiword | Word, all ASCII | vody | {cspell,simple} | cspell
| {voda}
This patch allocates memory with non-file-based mmap() to preload text search
dictionary files at the server start. Note that dist files are not mmap'ed
directly in the patch; mmap() is used for reallocatable shared memory.
The dictinary loader is also modified a bit to use simple_alloc() instead
of palloc() for long-lived cache. It can reduce calls of AllocSetAlloc(),
that have some overheads to support pfree(). Since the cache is never
released, simple_alloc() seems to have better performance than palloc().
Note that the optimization will also work for non-preloaded dicts.
it produce little bit better spead, but mainly it significant memory
reduction - palloc allocation is expensive, because add 4 bytes (8
bytes) to any allocations. And it is problem for thousands smalls
blocks like TSearch ispell dictionary uses. On 64 bit the overhead is
horrible
=== Questions ===
- How do backends share the dict cache? You might expect postmaster's
catalog is inherited to backends with fork(), but we don't use fork()
on Windows.
I though about some variants
a) using a shared memory - but it needs more shared memory
reservation, maybe some GUC - but this variant was refused in
discussion.
b) using a mmap on Unix and CreateFileMapping API on windows - but it
is little bit problem for me. I am not have a develop tools for ms
windows. And I don't understand to MS Win platform :(
Magnus, can you do some tip?
Without MSWindows we don't need to solve a shared memory and can use
only fork. If we can think about MSWin too, then we have to calculate
only with some shared memory based solution. But it has more
possibilities - shared dictionary can be loaded in runtime too.
- Why are SQL functions dpreloaddict_init() and dpreloaddict_lexize()
defined but not used?
it is used, if I remember well. It uses ispell dictionary API. The
using is simlyfied - you can parametrize preload dictionary - and then
you use a preloaded dictionary - not some specific dictionary. This
has one advantage and one disadvantage + very simple configuration, +
there are not necessary some shared dictionary manager, - only one
preload dictionary can be used.
=== Design ===
- You added 3 custom parameters (dict_preload.dictfile/afffile/stopwords),
but I think text search configuration names is better than file names.
However, it requires system catalog access but we cannot access any
catalog at the moment of preloading. If config-name-based setting is
difficult, we need to write docs about where we can get the dict names
to be preloaded instead. (from \dFd+ ?)
yes - it is true argument - there are not possible access to these
data in
Re: [HACKERS] patch: preload dictionary new version
On Wed, Jul 7, 2010 at 10:50 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: This patch allocates memory with non-file-based mmap() to preload text search dictionary files at the server start. Note that dist files are not mmap'ed directly in the patch; mmap() is used for reallocatable shared memory. I thought someone (Tom?) had proposed idea previously of writing a dictionary precompiler that would produce a file which could then be mmap()'d into the backend. Has any thought been given to that approach? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Thu, Jul 8, 2010 at 2:16 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-07-07 at 22:26 -0400, Robert Haas wrote: Rereading the thread, I'm a bit confused by why we're proposing to use a SHARE lock; it seems to me that a self-conflicting lock type would simplify things. There's a bunch of discussion on the thread about how to handle pg_class updates atomically, but doesn't using a self-conflicting lock type eliminate that problem? The use of the SHARE lock had nothing to do with the pg_class update requirement, so that suggestion won't help there. Forgive me if I press on this just a bit further, but ISTM that an atomic pg_class update functionality isn't intrinsically required, because if it were the current code would need it. So what is changing in this patch that makes it necessary when it isn't now? ISTM it must be that the lock is weaker. What am I missing? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Keepalives win32
On Wed, Jul 7, 2010 at 15:32, Robert Haas robertmh...@gmail.com wrote: On Wed, Jul 7, 2010 at 9:20 AM, Magnus Hagander mag...@hagander.net wrote: On Wed, Jun 30, 2010 at 17:46, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: I also think we may want to suggest that for most environments, people may want to change these settings to something more aggressive, like a 30 to 120 second initial delay, with a 10 or 20 second retry interval. The RFC defaults seem approximately right for a TCP connection to a colony on the surface of the moon, where besides the round trip latency of 2.5 seconds they might have to pay by the byte. Well, the RFCs were definitely written at a time when bandwidth was a lot more expensive than it is today. In other words, it is *so* conservative that I have trouble seeing it ever causing a problem compared to not having keepalive enabled, but it will eventually clean things up. Yes. This is a large part of the reason why I think it's okay for us to turn libpq keepalive on by default in 9.0 --- the default parameters for it are so conservative as to be unlikely to cause trouble. If Windows isn't using RFC-equivalent default parameters, that seems like a good reason to disregard the system settings and force use of the RFC values as defaults. Here's an updated version of the patch, which includes server side functionality. I took out the code that tried tobe smart. It'll now set them to 2 hours/1 second by default. I looked quickly at the RFC and didn't find the exact values there, so those values are the documented out-of-the-box defaults on Windows. I can easily change them to RFC values if someone can find them for me :) It's also merged with roberts macos patch, since they were conflicting. Doc changes not included, but I'll get those in before commit. Comments? Looks generally OK, though my knowledge of Windows is pretty limited. We'd better get this committed PDQ if it's going into beta3, else there won't be a full buildfarm cycle before we wrap. Committed. -- 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] GSoC - code of implementation of materialized views
2010/6/29 Pavel Baroš baro...@seznam.cz: Yeah, it is my fault, I did not mentioned that this patch is not final. It is only small part of whole implementation. I wanted to show just this, because I think that is the part that should not change much. And to show I did something, I am not ignoring GSoC. Now I can fully focus on the program. Most of the problems you mentioned (except pg_dump) I have implemented and I will post it to HACKERS soon. Until now I've not had much time, because I just finished my BSc. studies yesterday. Any update on this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] patch: preload dictionary new version
2010/7/8 Robert Haas robertmh...@gmail.com: On Wed, Jul 7, 2010 at 10:50 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: This patch allocates memory with non-file-based mmap() to preload text search dictionary files at the server start. Note that dist files are not mmap'ed directly in the patch; mmap() is used for reallocatable shared memory. I thought someone (Tom?) had proposed idea previously of writing a dictionary precompiler that would produce a file which could then be mmap()'d into the backend. Has any thought been given to that approach? The precompiler can save only some time related to parsing. But it isn't main issue. Without simple allocation the data from dictionary takes about 55 MB, with simple allocation about 10 MB. If you have a 100 max_session, then these data can be 100 x repeated in memory - about 1G (for Czech dictionary). I think so memory can be used better. Minimally you have to read these 10MB from disc - maybe from file cache - but it takes some time too - but it will be significantly better than now. Regards Pavel Stehule -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] patch: preload dictionary new version
On Thu, Jul 8, 2010 at 7:03 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/7/8 Robert Haas robertmh...@gmail.com: On Wed, Jul 7, 2010 at 10:50 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: This patch allocates memory with non-file-based mmap() to preload text search dictionary files at the server start. Note that dist files are not mmap'ed directly in the patch; mmap() is used for reallocatable shared memory. I thought someone (Tom?) had proposed idea previously of writing a dictionary precompiler that would produce a file which could then be mmap()'d into the backend. Has any thought been given to that approach? The precompiler can save only some time related to parsing. But it isn't main issue. Without simple allocation the data from dictionary takes about 55 MB, with simple allocation about 10 MB. If you have a 100 max_session, then these data can be 100 x repeated in memory - about 1G (for Czech dictionary). I think so memory can be used better. A precompiler can give you all the same memory management benefits. Minimally you have to read these 10MB from disc - maybe from file cache - but it takes some time too - but it will be significantly better than now. If you use mmap(), you don't need to anything of the sort. And the EXEC_BACKEND case doesn't require as many gymnastics, either. And the variable can be PGC_SIGHUP or even PGC_USERSET instead of PGC_POSTMASTER. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] patch: preload dictionary new version
2010/7/8 Robert Haas robertmh...@gmail.com: On Thu, Jul 8, 2010 at 7:03 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/7/8 Robert Haas robertmh...@gmail.com: On Wed, Jul 7, 2010 at 10:50 PM, Takahiro Itagaki itagaki.takah...@oss.ntt.co.jp wrote: This patch allocates memory with non-file-based mmap() to preload text search dictionary files at the server start. Note that dist files are not mmap'ed directly in the patch; mmap() is used for reallocatable shared memory. I thought someone (Tom?) had proposed idea previously of writing a dictionary precompiler that would produce a file which could then be mmap()'d into the backend. Has any thought been given to that approach? The precompiler can save only some time related to parsing. But it isn't main issue. Without simple allocation the data from dictionary takes about 55 MB, with simple allocation about 10 MB. If you have a 100 max_session, then these data can be 100 x repeated in memory - about 1G (for Czech dictionary). I think so memory can be used better. A precompiler can give you all the same memory management benefits. Minimally you have to read these 10MB from disc - maybe from file cache - but it takes some time too - but it will be significantly better than now. If you use mmap(), you don't need to anything of the sort. And the EXEC_BACKEND case doesn't require as many gymnastics, either. And the variable can be PGC_SIGHUP or even PGC_USERSET instead of PGC_POSTMASTER. I use mmap(). And with mmap the precompiler are not necessary. Dictionary is loaded only one time - in original ispell format. I think, it is much more simple for administration - just copy ispell files. There are not some possible problems with binary incompatibility, you don't need to solve serialisation, deserialiasation, ...you don't need to copy TSearch ispell parser code to client application - probably we would to support not compiled ispell dictionaries still. Using a precompiler means a new questions for upgrade! The real problem is using a some API on MS Windows, where mmap doesn't exist. I think we can divide this problem to three parts a) simple allocator - it can be used not only for TSearch dictionaries. b) sharing a data - it is important for large dictionaries c) preloading - it decrease load time of first TSearch query Regards Pavel Stehule -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] leaky views, yet again
I think we pretty much have conceptual agreement on the shape of the solution to this problem: when a view is created with CREATE SECURITY VIEW, restrict functions and operators that might disclose tuple data from being pushed down into view (unless, perhaps, the user invoking the view has sufficient privileges to execute the underlying query anyhow, e.g. superuser or view owner). What we have not resolved is exactly how we're going to decide which functions and operators might do such a dastardly thing. I think the viable options are as follows: 1. Adopt Heikki's proposal of treating indexable operators as non-leaky. http://archives.postgresql.org/pgsql-hackers/2010-06/msg00291.php Or, perhaps, and even more restrictively, treat only hashable/mergeable operators as non-leaky. 2. Add an explicit flag to pg_proc, which can only be set by superusers (and which is cleared if a non-superuser modifies it in any way), allowing a function to be tagged as non-leaky. I believe that it would be reasonable to set this flag for all of our built-in indexable operators (though I'd read the code before doing it), but it would remove the need for the assumption that third-party operators are equally sane. CREATE OR REPLACE FUNCTION doesnt_leak() RETURNS integer AS $$SELECT 42$$ IMMUTABLE SEAWORTHY; -- doesn't leak This problem is not going away, so we should try to decide on something. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [v9.1] Add security hook on initialization of instance
2010/6/16 KaiGai Kohei kai...@ak.jp.nec.com: OK, fair enough. Please wait for a few days. I'll introduce the proof-of-concept module until this week. I think we have decided not to pursue this, at least for now. If that is the case, the CommitFest entry should be updated to Returned with Feedback. https://commitfest.postgresql.org/action/patch_view?id=323 FWIW, I am still of the opinion that we shouldn't have a hook here anyway, because there is no reason to complain about lack of a security context until the user performs an action which requires them to have a security context. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] GSoC - code of implementation of materialized views
On Thu, Jul 8, 2010 at 9:09 AM, Pavel baro...@seznam.cz wrote: Any update on this? Sure, sorry for delay, I updated code on http://github.com/pbaros/postgres just a few minutes ago. Today I'll post patch here on HACKERS with my comments. It's a little hard for me to understand what's going on via the git repo, but it looks like you've introduced a bunch of spurious whitespace changes in OpenIntoRel. Don't let it delay you from posting the patch, but do please clean them up as soon as you get a chance. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] GSoC - code of implementation of materialized views
On Thu, Jul 8, 2010 at 9:22 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, Jul 8, 2010 at 9:09 AM, Pavel baro...@seznam.cz wrote: Any update on this? Sure, sorry for delay, I updated code on http://github.com/pbaros/postgres just a few minutes ago. Today I'll post patch here on HACKERS with my comments. It's a little hard for me to understand what's going on via the git repo, but it looks like you've introduced a bunch of spurious whitespace changes in OpenIntoRel. Don't let it delay you from posting the patch, but do please clean them up as soon as you get a chance. Never mind... I see what you did. It's fine. /me blushes -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [RRR] Reviewfest 2010-06 Plans and Call for Reviewers
Robert Haas robertmh...@gmail.com wrote: Who is going to manage the actual CF? I'm willing to do so if people want that. I promise to be more aggressive about pursuing this once the release is out; I've seen some of the same people who are doing reviews contributing to the release process, so I haven't wanted to push on the RF tasks. I'm also perfectly willing to step aside for anyone else. ;-) -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] [v9.1] Add security hook on initialization of instance
Robert, * Robert Haas (robertmh...@gmail.com) wrote: 2010/6/16 KaiGai Kohei kai...@ak.jp.nec.com: OK, fair enough. Please wait for a few days. I'll introduce the proof-of-concept module until this week. I think we have decided not to pursue this, at least for now. If that is the case, the CommitFest entry should be updated to Returned with Feedback. I think RwF is fine (since I think we're still waiting on another patch anyway) for this commitfest. I don't want to shut the door entirely on this for 9.1, but a new/updated patch could be done in a later commitfest. FWIW, I am still of the opinion that we shouldn't have a hook here anyway, because there is no reason to complain about lack of a security context until the user performs an action which requires them to have a security context. I don't agree with this, in general. It may be a difficult problem to solve though. From my perspective the above is similar to saying we don't need a pg_hba.conf or that we should open a database before checking the user's credentials. I'd like to give a security module the ability to be involved in the initial connection authorization, but we run into an issue there if that module then needs access to the catalog. Perhaps it doesn't, but it seems like it would, to use to make a decision. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [RRR] Reviewfest 2010-06 Plans and Call for Reviewers
On Thu, Jul 8, 2010 at 9:32 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Robert Haas robertmh...@gmail.com wrote: Who is going to manage the actual CF? I'm willing to do so if people want that. I promise to be more aggressive about pursuing this once the release is out; I've seen some of the same people who are doing reviews contributing to the release process, so I haven't wanted to push on the RF tasks. I'm also perfectly willing to step aside for anyone else. ;-) Personally, if you can step up to the plate on this one, I think that would be great. I am going to be out of town for a week starting two days from now, and I would rather not have to try to manage a CF without regular Internet connectivity and while I'm supposed to be on vacation. And, if I do spend some time working on this, either while I'm on vacation or afterwards, it would be nice if I could focus on reviewing and committing. If someone else wants to volunteer, of course, that's great too... In terms of being aggressive about pursuing this, I think it's important that between July 15th and August 14th we try to give everyone who has submitted a patch by July 14th some feedback on their work, which means we need more people reviewing than have so far. I don't think the release will be out before the CF is over, but I'm hoping that we can get enough people involved to walk and chew gum at the same time. Last year, the first CommitFest involved returning with feedback a lot of patches that had horribly bitrotted or had major design issues - and the second CommitFest involved committing many of those same patches. So giving people feedback now is really important to avoid having things pile up at the end of the release cycle. The good/bad news is that we don't have any major uncommitted patches floating around unmerged at this pont, as we did last cycle with HS and SR. KNNGIST is in a similar situation to where HS and SR were at this time last year, but it's not as big of a patch, and it's not as high-profile (although I think anyone who uses PostGIS, and not a few others, are drooling...). Sync rep is going to be a big patch, or, hopefully, a series of patches, but it's more of a question of getting it designed and written than getting it merged at this point. I have a bad feeling we're going to be trying to land that one at the very last minute. :-( -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] patch: preload dictionary new version
Pavel Stehule pavel.steh...@gmail.com writes: 2010/7/8 Robert Haas robertmh...@gmail.com: A precompiler can give you all the same memory management benefits. I use mmap(). And with mmap the precompiler are not necessary. Dictionary is loaded only one time - in original ispell format. I think, it is much more simple for administration - just copy ispell files. There are not some possible problems with binary incompatibility, you don't need to solve serialisation, deserialiasation, ...you don't need to copy TSearch ispell parser code to client application - probably we would to support not compiled ispell dictionaries still. Using a precompiler means a new questions for upgrade! You're inventing a bunch of straw men to attack. There's no reason that a precompiler approach would have to put any new requirements on the user. For example, the dictionary-load code could automatically execute the precompile step if it observed that the precompiled copy of the dictionary was missing or had an older file timestamp than the source. I like the idea of a precompiler step mainly because it still gives you most of the benefits of the patch on platforms without mmap. (Instead of mmap'ing, just open and read() the precompiled file.) In particular, you would still have a creditable improvement for Windows users without writing any Windows-specific code. I think we can divide this problem to three parts a) simple allocator - it can be used not only for TSearch dictionaries. I think that's a waste of time, frankly. There aren't enough potential use cases. b) sharing a data - it is important for large dictionaries Useful but not really essential. c) preloading - it decrease load time of first TSearch query This is the part that is the make-or-break benefit of the patch. You need a solution that cuts load time even when mmap isn't available. 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] [v9.1] Add security hook on initialization of instance
Stephen Frost sfr...@snowman.net writes: * Robert Haas (robertmh...@gmail.com) wrote: FWIW, I am still of the opinion that we shouldn't have a hook here anyway, because there is no reason to complain about lack of a security context until the user performs an action which requires them to have a security context. I don't agree with this, in general. It may be a difficult problem to solve though. From my perspective the above is similar to saying we don't need a pg_hba.conf or that we should open a database before checking the user's credentials. I'd like to give a security module the ability to be involved in the initial connection authorization, but we run into an issue there if that module then needs access to the catalog. Maybe so, but the proposed hook placement doesn't actually allow a plugin module to be involved in the authorization --- we've already decided the authorization is OK. All it can do there is some additional initialization, which could equally well be done on first use (if any) of the additional information. There might be some value in letting a plugin actually have some control over the authentication process, but I'm not sure offhand what a reasonable hook design would be. 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] [v9.1] Add security hook on initialization of instance
On Thu, Jul 8, 2010 at 9:37 AM, Stephen Frost sfr...@snowman.net wrote: FWIW, I am still of the opinion that we shouldn't have a hook here anyway, because there is no reason to complain about lack of a security context until the user performs an action which requires them to have a security context. I don't agree with this, in general. It may be a difficult problem to solve though. From my perspective the above is similar to saying we don't need a pg_hba.conf or that we should open a database before checking the user's credentials. I'd like to give a security module the ability to be involved in the initial connection authorization, but we run into an issue there if that module then needs access to the catalog. Perhaps it doesn't, but it seems like it would, to use to make a decision. Well, perhaps I'll revise my opinion here a bit. If we're actually going to do something with the user's security context at connection time, like validate that they have rights to connect to the database they've selected, then it would make sense to have a hook somewhere in the authentication process. I think we have to assume that whatever actions a pluggable security provider might take at authentication time are going to be based on information from outside the database. It would be nice to have an infrastructure that would support making an access control decision based on data from within the database, but as of today any catalogs consulted during authentication must be (a) shared and (b) nailed, and there's certainly no provision for third-party modules to add shared or nailed system tables (or even, ordinary system tables). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [v9.1] Add security hook on initialization of instance
* Tom Lane (t...@sss.pgh.pa.us) wrote: Maybe so, but the proposed hook placement doesn't actually allow a plugin module to be involved in the authorization --- we've already decided the authorization is OK. All it can do there is some additional initialization, which could equally well be done on first use (if any) of the additional information. Right, I agree that the existing patch isn't what should be done here. There might be some value in letting a plugin actually have some control over the authentication process, but I'm not sure offhand what a reasonable hook design would be. Definitely needs more thought, but that's the direction that I think makes more sense. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] [v9.1] Add security hook on initialization of instance
* Robert Haas (robertmh...@gmail.com) wrote: I think we have to assume that whatever actions a pluggable security provider might take at authentication time are going to be based on information from outside the database. I feel that would be perfect for 9.1 and supporting access to the general catalog is something that, if we figure out a sane way to do it, we could always add later (if there's demand, etc). For those bits of the catalog which *do* meet the requirements you mention, I hope it'll be possible for the security module to access them? Does make me wonder if we might consider adding a field to those to support a label rather than trying to figure out a way for a third party to provide a shared/nailed relation. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [v9.1] Add security hook on initialization of instance
On Thu, Jul 8, 2010 at 10:48 AM, Stephen Frost sfr...@snowman.net wrote: * Robert Haas (robertmh...@gmail.com) wrote: I think we have to assume that whatever actions a pluggable security provider might take at authentication time are going to be based on information from outside the database. I feel that would be perfect for 9.1 and supporting access to the general catalog is something that, if we figure out a sane way to do it, we could always add later (if there's demand, etc). For those bits of the catalog which *do* meet the requirements you mention, I hope it'll be possible for the security module to access them? Does make me wonder if we might consider adding a field to those to support a label rather than trying to figure out a way for a third party to provide a shared/nailed relation. I'm not sure what the best thing to do about this is. I think it might be a good idea to start with some discussion of what problems people are trying to solve (hopefully N 1?) and then try to figure out what a good solution might look like. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [v9.1] Add security hook on initialization of instance
* Robert Haas (robertmh...@gmail.com) wrote: I'm not sure what the best thing to do about this is. I think it might be a good idea to start with some discussion of what problems people are trying to solve (hopefully N 1?) and then try to figure out what a good solution might look like. Guess my first thought was that you'd have a database-level label that would be used by SELinux to validate a connection. A second thought is labels for roles. KaiGai, can you provide your thoughts on this discussion/approach/problems? I realize it's come a bit far-afield from your original proposal. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [RRR] Reviewfest 2010-06 Plans and Call for Reviewers
Robert Haas robertmh...@gmail.com wrote: Last year, the first CommitFest involved returning with feedback a lot of patches that had horribly bitrotted That's an excellent point. If there is anyone out there who could do an initial run to kick back patches with bitrot in the next week or so, that would be a tremendous help. or had major design issues That's a much harder problem. The set of people who can review for that is rather smaller than the set who can see if a patch applies without error. So giving people feedback now is really important to avoid having things pile up at the end of the release cycle. Yeah, with only four review cycles, big items must get some review early to avoid holding up the release at the other end. The good/bad news is that we don't have any major uncommitted patches floating around unmerged at this pont, as we did last cycle with HS and SR. I'm not sure I understand what you mean by unmerged -- are you excluding patches where they are in a git repository which is merging in HEAD on a regular basis? -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] [COMMITTERS] pgsql: Add support for TCP keepalives on Windows, both for backend and
Magnus Hagander mag...@hagander.net writes: Seems pretty simple - mingw doesn't have support for this. We have two ways to deal with that I think: 1) Disable it on mingw. 2) Include it in our custom headers. For #2, what we need to include is the define of SIO_KEEPALIVE_VALS as well as the definition of struct tcp_keepalive. We've done #2 before at least once, which worked well until mingw suddenly caught up and added it a while later. It's not like this is a new definition in windows, but we need to be ready for them to eventually do that. Yeah. I'm satisfied with doing #1 and waiting for them to fix it. I guess there is: 3) write an autoconf test and provide them only when mingw doesn't have it. if we're going with #3, I'll respectfully have to ask somebod yelse to write the autoconf test, that's beyond me I think :-) An easy approximation would be to make the code #ifdef SIO_KEEPALIVE_VALS. That would fail if the mingw guys decide to provide the #define without adding the struct at the same time, but that seems moderately unlikely. 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
[HACKERS] Re: [COMMITTERS] pgsql: Add support for TCP keepalives on Windows, both for backend and
On Thu, Jul 8, 2010 at 17:11, Magnus Hagander mag...@hagander.net wrote: On Thu, Jul 8, 2010 at 17:04, Tom Lane t...@sss.pgh.pa.us wrote: m...@postgresql.org (Magnus Hagander) writes: Log Message: --- Add support for TCP keepalives on Windows, both for backend and the new libpq support. Buildfarm member narwhal doesn't like this patch. You have about six or eight hours to fix or revert it before beta3 wraps. Gah. Seems mingw is out of date with reality again. I'll go look for a vm with it on and see if I can find how to do it there. (and yes, I even asked Dave to do a special run with that bf member for me, and then forgot to check the result. sorry!) Seems pretty simple - mingw doesn't have support for this. We have two ways to deal with that I think: 1) Disable it on mingw. 2) Include it in our custom headers. For #2, what we need to include is the define of SIO_KEEPALIVE_VALS as well as the definition of struct tcp_keepalive. We've done #2 before at least once, which worked well until mingw suddenly caught up and added it a while later. It's not like this is a new definition in windows, but we need to be ready for them to eventually do that. I guess there is: 3) write an autoconf test and provide them only when mingw doesn't have it. if we're going with #3, I'll respectfully have to ask somebod yelse to write the autoconf test, that's beyond me I think :-) Opinions on which way to go? -- 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
[HACKERS] Re: [COMMITTERS] pgsql: Add support for TCP keepalives on Windows, both for backend and
On Thu, Jul 8, 2010 at 17:39, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: Seems pretty simple - mingw doesn't have support for this. We have two ways to deal with that I think: 1) Disable it on mingw. 2) Include it in our custom headers. For #2, what we need to include is the define of SIO_KEEPALIVE_VALS as well as the definition of struct tcp_keepalive. We've done #2 before at least once, which worked well until mingw suddenly caught up and added it a while later. It's not like this is a new definition in windows, but we need to be ready for them to eventually do that. Yeah. I'm satisfied with doing #1 and waiting for them to fix it. I guess there is: 3) write an autoconf test and provide them only when mingw doesn't have it. if we're going with #3, I'll respectfully have to ask somebod yelse to write the autoconf test, that's beyond me I think :-) An easy approximation would be to make the code #ifdef SIO_KEEPALIVE_VALS. That would fail if the mingw guys decide to provide the #define without adding the struct at the same time, but that seems moderately unlikely. Seems reasonable. I'll go do something along that line and verify that it actually works :-) That laves the questions of docs - right now the docs just say it works on windows. I guess we need to add some kind of disclaimer around that, but the fact is that for 99+% of our windows users it will work - since they use the binaries, and the binaries are built with the full api - so we shouldn't make it *too* prominent.. -- 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] [COMMITTERS] pgsql: Add support for TCP keepalives on Windows, both for backend and
Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Seems pretty simple - mingw doesn't have support for this. We have two ways to deal with that I think: 1) Disable it on mingw. 2) Include it in our custom headers. For #2, what we need to include is the define of SIO_KEEPALIVE_VALS as well as the definition of struct tcp_keepalive. We've done #2 before at least once, which worked well until mingw suddenly caught up and added it a while later. It's not like this is a new definition in windows, but we need to be ready for them to eventually do that. Yeah. I'm satisfied with doing #1 and waiting for them to fix it. I guess there is: 3) write an autoconf test and provide them only when mingw doesn't have it. if we're going with #3, I'll respectfully have to ask somebod yelse to write the autoconf test, that's beyond me I think :-) An easy approximation would be to make the code #ifdef SIO_KEEPALIVE_VALS. That would fail if the mingw guys decide to provide the #define without adding the struct at the same time, but that seems moderately unlikely. +1 for this course of action. 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
[HACKERS] Out of date comment in xlogutils.c
I noticed the following out-of-date bits in a comment in xlogutils.c: * LockBuffer(buffer, BUFFER_LOCK_EXCLUSIVE), for reading from the main * fork. * - * (Getting the lock is not really necessary, since we expect that this is - * only used during single-process XLOG replay, but some subroutines such - * as MarkBufferDirty will complain if we don't. And hopefully we'll get - * hot standby support in the future, where there will be backends running - * read-only queries during XLOG replay.) - * * The returned buffer is exclusively-locked. * * For historical reasons, instead of a ReadBufferMode argument, this only -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] Out of date comment in xlogutils.c
Joshua Tolley eggyk...@gmail.com writes: I noticed the following out-of-date bits in a comment in xlogutils.c: Removing the comment altogether doesn't seem appropriate. I changed it to * (Getting the buffer lock is not really necessary during single-process * crash recovery, but some subroutines such as MarkBufferDirty will complain * if we don't have the lock. In hot standby mode it's definitely necessary.) 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] [COMMITTERS] pgsql: Add support for TCP keepalives on Windows, both for backend and
On Thu, Jul 8, 2010 at 17:45, Andrew Dunstan and...@dunslane.net wrote: Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Seems pretty simple - mingw doesn't have support for this. We have two ways to deal with that I think: 1) Disable it on mingw. 2) Include it in our custom headers. For #2, what we need to include is the define of SIO_KEEPALIVE_VALS as well as the definition of struct tcp_keepalive. We've done #2 before at least once, which worked well until mingw suddenly caught up and added it a while later. It's not like this is a new definition in windows, but we need to be ready for them to eventually do that. Yeah. I'm satisfied with doing #1 and waiting for them to fix it. I guess there is: 3) write an autoconf test and provide them only when mingw doesn't have it. if we're going with #3, I'll respectfully have to ask somebod yelse to write the autoconf test, that's beyond me I think :-) An easy approximation would be to make the code #ifdef SIO_KEEPALIVE_VALS. That would fail if the mingw guys decide to provide the #define without adding the struct at the same time, but that seems moderately unlikely. +1 for this course of action. Here's what I came up with and will apply as soon as my msvc build completes. (the mingw one works with this) -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ mingwkeepalives.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Add support for TCP keepalives on Windows, both for backend and
On Thu, Jul 8, 2010 at 18:06, Magnus Hagander mag...@hagander.net wrote: On Thu, Jul 8, 2010 at 17:45, Andrew Dunstan and...@dunslane.net wrote: Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: Seems pretty simple - mingw doesn't have support for this. We have two ways to deal with that I think: 1) Disable it on mingw. 2) Include it in our custom headers. For #2, what we need to include is the define of SIO_KEEPALIVE_VALS as well as the definition of struct tcp_keepalive. We've done #2 before at least once, which worked well until mingw suddenly caught up and added it a while later. It's not like this is a new definition in windows, but we need to be ready for them to eventually do that. Yeah. I'm satisfied with doing #1 and waiting for them to fix it. I guess there is: 3) write an autoconf test and provide them only when mingw doesn't have it. if we're going with #3, I'll respectfully have to ask somebod yelse to write the autoconf test, that's beyond me I think :-) An easy approximation would be to make the code #ifdef SIO_KEEPALIVE_VALS. That would fail if the mingw guys decide to provide the #define without adding the struct at the same time, but that seems moderately unlikely. +1 for this course of action. Here's what I came up with and will apply as soon as my msvc build completes. (the mingw one works with this) ... and applied. -- 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] [RRR] Reviewfest 2010-06 Plans and Call for Reviewers
On Thu, Jul 8, 2010 at 11:36 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: or had major design issues That's a much harder problem. The set of people who can review for that is rather smaller than the set who can see if a patch applies without error. Well, true. But reporting whether the patch applies without error is about the most minimal review possible, so that's not saying much. Many of the patches that need review are relatively small ones, so we need people to check whether they work, see if they have docs and regression tests, try to break them, and also - importantly - offer an opinion on whether the proposed feature is actually useful. Of course others may disagree, but you can't reach consensus if nobody's willing to offer a starting point for discussion. Reviewers who can actually analyze the code even a little bit can find many more issues. Does it fit the style of the surrounding code? Are there unnecessary or unrelated changes in the patch? And for extra credit, does it have bugs? What I do frequently is find a comparable - something similar in the existing code - and compare them side-by-side. For each change, I ask myself whether it's there because the new functionality is different from the old, or whether it's arbitrary. I agree that the larger features need a different kind of analysis, and if those get left for more experienced reviewers I think that's fine. Hopefully, some of those people will volunteer to help out; I noticed that Itagaki Takahiro has already started reviewing, and Bernd Helmle has signed up to review one of my patches. But there is no shortage of things that can be looked at by people who are doing this first the first time, especially if they have even a passing ability to read C code. The good/bad news is that we don't have any major uncommitted patches floating around unmerged at this pont, as we did last cycle with HS and SR. I'm not sure I understand what you mean by unmerged -- are you excluding patches where they are in a git repository which is merging in HEAD on a regular basis? By unmerged I meant simply uncommitted. I know we have a number of fairly big patches, but I don't think they're as complex as HS and SR, and they are not leftovers that were too big to swallow during the 9.0 cycle. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] GSoC - code of implementation of materialized views
Dne 8.7.2010 12:33, Robert Haas napsal(a): 2010/6/29 Pavel Barošbaro...@seznam.cz: Yeah, it is my fault, I did not mentioned that this patch is not final. It is only small part of whole implementation. I wanted to show just this, because I think that is the part that should not change much. And to show I did something, I am not ignoring GSoC. Now I can fully focus on the program. Most of the problems you mentioned (except pg_dump) I have implemented and I will post it to HACKERS soon. Until now I've not had much time, because I just finished my BSc. studies yesterday. Any update on this? Sure, sorry for delay, I updated code on http://github.com/pbaros/postgres just a few minutes ago. Today I'll post patch here on HACKERS with my comments. Pavel Baros
Re: [HACKERS] [RRR] Reviewfest 2010-06 Plans and Call for Reviewers
Robert Haas robertmh...@gmail.com wrote: Kevin Grittner kevin.gritt...@wicourts.gov wrote: or had major design issues That's a much harder problem. The set of people who can review for that is rather smaller than the set who can see if a patch applies without error. Well, true. But reporting whether the patch applies without error is about the most minimal review possible I didn't mean to imply that only the two extremes of review (does the patch apply? and does this patch have a major overall design flaw?) were the only things to try to address now; I was just responding to your observation that these comprised a lot of the activity of the first CommitFest last year, and that the latter is harder to address without a review by a senior developer. I suspect that one person could check for bitrot in all pending patches with a one or two FTE day's effort, and if that's done within the next few days, it might allow time for fixes before the start of the CF free up more of the first week of the CF to more substantive review. Nice comments later in the email, though; I hope you won't mind if you find excerpts popping up in the code review Wiki pages. ;-) -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] [RRR] Reviewfest 2010-06 Plans and Call for Reviewers
On Thu, Jul 8, 2010 at 12:44 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Nice comments later in the email, though; I hope you won't mind if you find excerpts popping up in the code review Wiki pages. ;-) Not at all. Go for it... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Admission Control
Simon, Mark, Actually only 1 lock check per query, but certainly extra processing and data structures to maintain the pool information... so, yes certainly much more suitable for DW (AFAIK we never attempted to measure the additional overhead for non DW workload). I recall testing it when the patch was submitted for 8.2., and the overhead was substantial in the worst case ... like 30% for an in-memory one-liner workload. I've been going over the greenplum docs and it looks like the attempt to ration work_mem was dropped. At this point, Greenplum 3.3 only rations by # of concurrent queries and total cost. I know that work_mem rationing was in the original plans; what made that unworkable? My argument in general is that in the general case ... where you can't count on a majority of long-running queries ... any kind of admission control or resource management is a hard problem (if it weren't, Oracle would have had it before 11). I think that we'll need to tackle it, but I don't expect the first patches we make to be even remotely usable. It's definitely not an SOC project. I should write more about this. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] inner join removal
So I sat down to work on some code to do inner join removal, and quickly got stuck. As a first step, I wanted to write some code that would identify inner joins that could be treated as LEFT JOINs if we so chose. Consider: SELECT 1 FROM foo, bar WHERE foo.x = bar.x; If it so happens that there is a left join constraint from either foo (x) or bar (x) referencing the other one, then the referenced column has a unique index on it; if, further, the referencing column is not null, then every row in the referencing table will have exactly one partner in the referenced table. Thus, the join can be implemented as a LEFT JOIN without changing the results. In this case, that also means the join can be removed, but it can be a win anyway. Consider: SELECT * FROM foo LEFT JOIN (bar JOIN baz ON bar.y = baz.y) ON foo.x = bar.x; If foo is itty bitty and bar and baz are enormous, it would be nice to start by joining foo to bar and then joining the result to baz, but that's not legal. However, if bar (y) references baz (y) and bar.y is not null, then the inner join is equivalent to a left join and it's OK to commute them. Anyway, I didn't get near as far as actually trying to implement this fancy footwork because I got stuck on a much simpler problem. I thought that it would make sense to iterate through all the relations in the query and look at the join clauses for each one. So in the above query, for example, I was expecting that when I looked at the RelOptInfo for baz, its joinlist would contain bar.y = baz.y. It doesn't, because bar.y = baz.y gets eaten by the equivalence class machinery. Essentially, what I want to do is ask, for each relation, whether there is exactly one other relation that gets joined to it, and then if that proves to be the case, get a list of all the join clauses and examine them. But I can't figure out how to do that. Help? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] inner join removal
Robert Haas robertmh...@gmail.com writes: Consider: SELECT * FROM foo LEFT JOIN (bar JOIN baz ON bar.y = baz.y) ON foo.x = bar.x; If foo is itty bitty and bar and baz are enormous, it would be nice to start by joining foo to bar and then joining the result to baz, but that's not legal. However, if bar (y) references baz (y) and bar.y is not null, then the inner join is equivalent to a left join and it's OK to commute them. I think you're going at this in the wrong place. It'd likely work better to identify this situation while building the SpecialJoinInfo structs describing the join order constraints, and mark the constraints appropriately. In fact, I'm not convinced that convert the inner join to a left join is even the right way to think about the problem, because if you fail to get a win from it then you have likely made things worse not better, by adding a join order constraint that wasn't there before. I think it might work out better if you ask what additional conditions are needed in order to prove that this inner join can commute with this left join, and then work at being able to prove that. (It's entirely likely that the planner isn't currently gathering the right information for solving that problem.) 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] inner join removal
Robert Haas robertmh...@gmail.com writes: On Thu, Jul 8, 2010 at 2:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think it might work out better if you ask what additional conditions are needed in order to prove that this inner join can commute with this left join, and then work at being able to prove that. (It's entirely likely that the planner isn't currently gathering the right information for solving that problem.) We have to avoid putting much of anything into the critical path where we're trying out different join orders - we want to figure it out earlier and, if possible, by examining each relation just once. Right, but look for example at the logic involved with the current outer join transformation identity #3, which can't be applied unless the join predicate is strict for the left-hand side. We avoid doing the dogwork to test that more than once. I'm hoping that something similar will work for this problem. But it's way premature to say much about that without a mathematical characterization of the extra conditions needed to make the joins commutable. 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] inner join removal
I think it might work out better if you ask what additional conditions are needed in order to prove that this inner join can commute with this left join, and then work at being able to prove that. (It's entirely likely that the planner isn't currently gathering the right information for solving that problem.) I also see this falling into a whole class of issues where the planner could utilize the existance of FKs to plan queries better. That might be a better first step. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.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] inner join removal
On Thu, Jul 8, 2010 at 3:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Thu, Jul 8, 2010 at 2:48 PM, Tom Lane t...@sss.pgh.pa.us wrote: I think it might work out better if you ask what additional conditions are needed in order to prove that this inner join can commute with this left join, and then work at being able to prove that. (It's entirely likely that the planner isn't currently gathering the right information for solving that problem.) We have to avoid putting much of anything into the critical path where we're trying out different join orders - we want to figure it out earlier and, if possible, by examining each relation just once. Right, but look for example at the logic involved with the current outer join transformation identity #3, which can't be applied unless the join predicate is strict for the left-hand side. We avoid doing the dogwork to test that more than once. admits ignorance Uh... where exactly is that logic? I've been looking for it for 2 years and haven't found it yet. I'm hoping that something similar will work for this problem. But it's way premature to say much about that without a mathematical characterization of the extra conditions needed to make the joins commutable. I wrote a previous email on this topic which might be a start in that direction. http://archives.postgresql.org/pgsql-hackers/2009-10/msg01012.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] inner join removal
Robert Haas robertmh...@gmail.com writes: On Thu, Jul 8, 2010 at 3:05 PM, Tom Lane t...@sss.pgh.pa.us wrote: Right, but look for example at the logic involved with the current outer join transformation identity #3, which can't be applied unless the join predicate is strict for the left-hand side. We avoid doing the dogwork to test that more than once. Uh... where exactly is that logic? I've been looking for it for 2 years and haven't found it yet. Look at the code that deals with SpecialJoinInfo.lhs_strict. There isn't very much of it (which is precisely my point ...) 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] LLVM / clang
On fre, 2010-06-25 at 15:49 -0400, Peter Eisentraut wrote: For the record, here is a patch that would address these issues. At the moment, I'm waiting to get my hands on the new version 2.7 of clang to see if some of these issues have gone away. Considering that clang already helped us find one bug in the code, I think it's worth trying to make this work. So, clang 2.7 didn't fix it. Do we want to proceed with my patch or leave clang unsupported? -- 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] LLVM / clang
Peter Eisentraut pete...@gmx.net writes: So, clang 2.7 didn't fix it. Do we want to proceed with my patch or leave clang unsupported? Given that the patch breaks plperl, I'd vote no ... but in any case right now is not the time to be applying it. Maybe it would be useful to put it in HEAD after we branch 9.0. 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Thu, 2010-07-08 at 06:08 -0400, Robert Haas wrote: On Thu, Jul 8, 2010 at 2:16 AM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2010-07-07 at 22:26 -0400, Robert Haas wrote: Rereading the thread, I'm a bit confused by why we're proposing to use a SHARE lock; it seems to me that a self-conflicting lock type would simplify things. There's a bunch of discussion on the thread about how to handle pg_class updates atomically, but doesn't using a self-conflicting lock type eliminate that problem? The use of the SHARE lock had nothing to do with the pg_class update requirement, so that suggestion won't help there. Forgive me if I press on this just a bit further, but ISTM that an atomic pg_class update functionality isn't intrinsically required, because if it were the current code would need it. So what is changing in this patch that makes it necessary when it isn't now? ISTM it must be that the lock is weaker. What am I missing? Not sure I follow that logic. Discussion on the requirement is in the archives. I don't wish to question that aspect myself. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and 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] [v9.1] Add security hook on initialization of instance
(2010/07/08 23:58), Stephen Frost wrote: * Robert Haas (robertmh...@gmail.com) wrote: I'm not sure what the best thing to do about this is. I think it might be a good idea to start with some discussion of what problems people are trying to solve (hopefully N 1?) and then try to figure out what a good solution might look like. Guess my first thought was that you'd have a database-level label that would be used by SELinux to validate a connection. A second thought is labels for roles. KaiGai, can you provide your thoughts on this discussion/approach/problems? I realize it's come a bit far-afield from your original proposal. Let's sort out the point at issues from perspective of the models. Please remind the authentication (in broad-sense) is consist of a few steps. 1) Identification In this step, system tries to obtain the identifier of client. Typically, it is a username provided using connection string in pgsql. 2) Authentication (in narrow-sense) In this step, system tries to verify whether the given identifier is correct, or not. Typically, it checks the password corresponding to the user. 3) Authorization In this step, system tries to assign a set of privileges on the new session. Typically, it is user identifier itself, because DAC makes access control decision based on the combination of user identifier and access control list of the objects. But all the security modules also intend to utilize the user identifier for its own access control decision. E.g, SELinux uses a security label of the client process. If we would have something like Oracle Label Security, client's label may be associated with a certain database role that is already authorized. The purpose of my patch was to provide an external security provider a chance to (3) authorize the new session based on or not-based on the (1) identification and (2) authentication. If we try to allow security providers to get control all of the 1-3, it might be hard to find the best place to put the hook right now. But, at least, SELinux does not have a plan to interpose identification and authentication. All I want to do here is to authorize the client just after authentication process. I'd like to suggest that we focus on the (3) authorization process for functionality of the patch. It may be also worth to control identification and authentication using security provider, but we don't have actual guest module right now. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.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] Admission Control
On 09/07/10 05:10, Josh Berkus wrote: Simon, Mark, Actually only 1 lock check per query, but certainly extra processing and data structures to maintain the pool information... so, yes certainly much more suitable for DW (AFAIK we never attempted to measure the additional overhead for non DW workload). I recall testing it when the patch was submitted for 8.2., and the overhead was substantial in the worst case ... like 30% for an in-memory one-liner workload. Interesting - quite high! However I recall you tested the initial committed version, later additions dramatically reduced the overhead (what is in the Bizgres repo *now* is the latest). I've been going over the greenplum docs and it looks like the attempt to ration work_mem was dropped. At this point, Greenplum 3.3 only rations by # of concurrent queries and total cost. I know that work_mem rationing was in the original plans; what made that unworkable? That certainly was my understanding too. I left Greenplum about the time this was being discussed, and I think the other staff member involved with the design left soon afterwards as well, which might have been a factor! My argument in general is that in the general case ... where you can't count on a majority of long-running queries ... any kind of admission control or resource management is a hard problem (if it weren't, Oracle would have had it before 11). I think that we'll need to tackle it, but I don't expect the first patches we make to be even remotely usable. It's definitely not an SOC project. I should write more about this. +1 Cheers Mark -- 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] Admission Control
On 09/07/10 12:58, Mark Kirkwood wrote: On 09/07/10 05:10, Josh Berkus wrote: Simon, Mark, Actually only 1 lock check per query, but certainly extra processing and data structures to maintain the pool information... so, yes certainly much more suitable for DW (AFAIK we never attempted to measure the additional overhead for non DW workload). I recall testing it when the patch was submitted for 8.2., and the overhead was substantial in the worst case ... like 30% for an in-memory one-liner workload. Interesting - quite high! However I recall you tested the initial committed version, later additions dramatically reduced the overhead (what is in the Bizgres repo *now* is the latest). Purely out of interest, since the old repo is still there, I had a quick look at measuring the overhead, using 8.4's pgbench to run two custom scripts: one consisting of a single 'SELECT 1', the other having 100 'SELECT 1' - the latter being probably the worst case scenario. Running 1,2,4,8 clients and 1000-1 tramsactions gives an overhead in the 5-8% range [1] (i.e transactions/s decrease by this amount with the scheduler turned on [2]). While a lot better than 30% (!) it is certainly higher than we'd like. Cheers Mark [1] I got the same range for pgbench select-only using its usual workload [2] As compared to Bizgres(8.2.4) and also standard Postgres 8.2.12. -- 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] Admission Control
On Thu, Jul 8, 2010 at 10:21 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: Purely out of interest, since the old repo is still there, I had a quick look at measuring the overhead, using 8.4's pgbench to run two custom scripts: one consisting of a single 'SELECT 1', the other having 100 'SELECT 1' - the latter being probably the worst case scenario. Running 1,2,4,8 clients and 1000-1 tramsactions gives an overhead in the 5-8% range [1] (i.e transactions/s decrease by this amount with the scheduler turned on [2]). While a lot better than 30% (!) it is certainly higher than we'd like. Isn't the point here to INCREASE throughput? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] patch (for 9.1) string functions
2010/7/8 Pavel Stehule pavel.steh...@gmail.com: sorry, attached fixed patch Make installcheck for contrib/stringfunc is broken. Please run regression test with --enable-cassert build. test stringfunc ... TRAP: FailedAssertion(!(!lc_ctype_is_c()), File: mbutils.c, Line: 715) LOG: server process (PID 15121) was terminated by signal 6: Aborted This patch contains several functions. - format(fmt text, VARIADIC args any) - sprintf(fmt text, VARIADIC args any) - concat(VARIADIC args any) - concat_ws(separator text, VARIADIC args any) - concat_json(VARIADIC args any) - concat_sql(VARIADIC args any) - rvrs(str text) - left(str text, n int) - right(str text, n int) The first one is in the core, and others are in contrib/stringfunc. But I think almost all of them should be in the core, because users want to write portable SQLs. Contrib modules are not always available. Note that concat() is supported by Oracle, MySQL, and DB2. Also left() and right() are supported by MySQL, DB2, and SQL Server. Functions that depend on GUC settings should be marked as VOLATILE instead of IMMUTABLE. I think format(), sprintf(), and all of concat()s should be volatile because at least timestamp depends on datestyle parameter. concat_ws() and rvrs() should be renamed to non-abbreviated forms. How about concat_with_sep() and reverse() ? I think we should avoid concat_json() at the moment because there is another development project for JSON support. The result type will be JOIN type rather than text then. I'm not sure usefulness of concat_sql(). Why don't you just quote all values with quotes and separate them with comma? format() function prints NULL as NULL, but RAISE statement in PL/pgSQL does as NULL. I prefer just NULL. maybe some GUC variable stringfunc.null_string = 'NULL' in future?? We have some choices for NULL representation. For example, empty string, NULL, NULL, or (null) . What will be our choice? Each of them looks equally reasonable for me. GUC idea is also good because we need to mark format() as VOLATILE anyway. We have nothing to lose. --- Takahiro Itagaki -- 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] Admission Control
On 09/07/10 14:26, Robert Haas wrote: On Thu, Jul 8, 2010 at 10:21 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: Purely out of interest, since the old repo is still there, I had a quick look at measuring the overhead, using 8.4's pgbench to run two custom scripts: one consisting of a single 'SELECT 1', the other having 100 'SELECT 1' - the latter being probably the worst case scenario. Running 1,2,4,8 clients and 1000-1 tramsactions gives an overhead in the 5-8% range [1] (i.e transactions/s decrease by this amount with the scheduler turned on [2]). While a lot better than 30% (!) it is certainly higher than we'd like. Isn't the point here to INCREASE throughput? LOL - yes it is! Josh wanted to know what the overhead was for the queue machinery itself, so I'm running a test to show that (i.e so I have a queue with the thresholds set higher than the test will load them). In the situation where (say) 11 concurrent queries of a certain type make your system become unusable, but 10 are fine, then constraining it to have a max of 10 will tend to improve throughput. By how much is hard to say, for instance preventing the Linux OOM killer shutting postgres down would be infinite I guess :-) Cheers Mark -- 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] get_whatever_oid, part 2
Sorry for the late responding. It looks good to me that the point (1) and (4) are fixed. The (2) and (3) are depending on individual subjective sense, so it will be no problem, if we decide not to clean them up at the same time. Elsewhere, I noticed one other stuff related to naming convention. The new internal APIs are basically named as: get_(object class)_oid(args...); At the part 1 patch, the object class was entirely matched with name of the system catalog. E.g, get_namespace_oid(), get_langeage_oid(). ^ | | +-- pg_namespace +-- pg_language But some of APIs in the part 2 have different object class name from their corresponding system catalog. How about the following renamings? - get_tsparser_oid() - get_ts_parser_oid() - get_tsdictionary_oid() - get_ts_dict_oid() - get_tstemplate_oid() - get_ts_template_oid() - get_tsconfiguration_oid() - get_ts_config_oid() - get_rule_oid() - get_rewrite_oid() - get_rule_oid_without_relid() - get_rewrite_oid_without_relid() But, it is also depending on my subjective sense. Even if you disagree with the proposition, I'll mark it 'ready for committer' on the next. Thanks, (2010/07/06 22:23), Robert Haas wrote: 2010/7/5 KaiGai Koheikai...@ak.jp.nec.com: I checked this patch. Then, I was surprised at we have such so many code duplications. I believe this patch can effectively eliminate these code duplications and it is worthful to apply. However, here are some points to be fixed/revised. 1. [BUG] DropCast() does not handle missing_ok correctly. You removed the 'return' on the path when get_cast_oid() with missing_ok = true returned InvalidOid. It seems to me a bug to be fixed. Good catch. Fixed. 2. we can reduce more code duplications using get_opfamily_oid() and get_opclass_oid(). I could find translations from a qualified name to schema/object names at GetIndexOpClass(), RenameOpFamily() and AlterOpFamilyOwner(). The new APIs enable to eliminate code duplications here. GetIndexOpClass() needs input type of the operator class, in addition to its OID, but it can be obtained using get_opclass_input_type(). RenameOpFamily() and AlterOpFamilyOwner() need a copy of the operator family tuple, in addition to its OID, but it can be obtained using GetSysCacheCopy1(OPFAMILYOID). I don't believe this is a good idea. We don't want to do extra syscache lookups just so that we can make the code look nicer. 3. Are OpClassCacheLookup() and OpFamilyCacheLookup() still needed? The OpFamilyCacheLookup() is only called from RemoveOpFamily() except for the get_opfamily_oid(), because it needs namespace OID in addition to the OID of operator family. If we have get_opfamily_namespace() in lsyscache.c, we can merge get_opfamily_oid() and OpFamilyCacheLookup() completely? The OpClassCacheLookup() is only called from RemoveOpClass(), RenameOpClass() and AlterOpClassOwner(), because RemoveOpClass() needs namespace OID in addition to the OID of operator class, and rest of them want to copy the HeapTuple to update it. If we have get_opclass_namespace() in lsyscache.c, RemoveOpClass() can use get_opclass_oid() instead of OpClassCacheLookup(). And, we can use a pair of get_opclass_oid() and GetSysCacheCopy1() instead of OpClassCacheLookup() and heap_copytuple() in the RenameOpClass() and AlterOpClassOwner(). Same thing here. I left that stuff alone on purpose. 4. Name of the arguments incorrect. It seems to me 'missing_ok', instead of 'failOK'. :D Yeah, that's an oversight on my part. Fixed. -- KaiGai Kohei kai...@ak.jp.nec.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] server authentication over Unix-domain sockets
(2010/07/03 1:39), Andrew Dunstan wrote: Peter Eisentraut wrote: Is there any possibilities that both WIN32 and HAVE_UNIX_SOCKETS are set concurrently? If possible, the libpq may try to call undefined function, then build will be failed. Win32 never has HAVE_UNIX_SOCKET. Cygwin might though, I recall some old discussion about that - can't remember the outcome though, and I can't find it right now. Cygwin doesn't define WIN32 anyway. Indeed, and we've been very careful to keep it that way. Sorry for the late responding. I marked this patch 'ready for committer'. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.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] leaky views, yet again
(2010/07/08 22:08), Robert Haas wrote: I think we pretty much have conceptual agreement on the shape of the solution to this problem: when a view is created with CREATE SECURITY VIEW, restrict functions and operators that might disclose tuple data from being pushed down into view (unless, perhaps, the user invoking the view has sufficient privileges to execute the underlying query anyhow, e.g. superuser or view owner). What we have not resolved is exactly how we're going to decide which functions and operators might do such a dastardly thing. I think the viable options are as follows: 1. Adopt Heikki's proposal of treating indexable operators as non-leaky. http://archives.postgresql.org/pgsql-hackers/2010-06/msg00291.php Or, perhaps, and even more restrictively, treat only hashable/mergeable operators as non-leaky. 2. Add an explicit flag to pg_proc, which can only be set by superusers (and which is cleared if a non-superuser modifies it in any way), allowing a function to be tagged as non-leaky. I believe that it would be reasonable to set this flag for all of our built-in indexable operators (though I'd read the code before doing it), but it would remove the need for the assumption that third-party operators are equally sane. CREATE OR REPLACE FUNCTION doesnt_leak() RETURNS integer AS $$SELECT 42$$ IMMUTABLE SEAWORTHY; -- doesn't leak This problem is not going away, so we should try to decide on something. I'd like to vote the second option, because this approach will be also applied on another aspect of leaky views. When leaky and non-leaky functions are chained within a WHERE clause, it will be ordered by the cost of functions. So, we have possibility that leaky functions are executed earlier than non-leaky functions. It will not be an easy work to mark built-in functions as either leaky or non-leaky, but it seems to me the most straight forward solution. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.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] Admission Control
On Thu, Jul 8, 2010 at 11:00 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 09/07/10 14:26, Robert Haas wrote: On Thu, Jul 8, 2010 at 10:21 PM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: Purely out of interest, since the old repo is still there, I had a quick look at measuring the overhead, using 8.4's pgbench to run two custom scripts: one consisting of a single 'SELECT 1', the other having 100 'SELECT 1' - the latter being probably the worst case scenario. Running 1,2,4,8 clients and 1000-1 tramsactions gives an overhead in the 5-8% range [1] (i.e transactions/s decrease by this amount with the scheduler turned on [2]). While a lot better than 30% (!) it is certainly higher than we'd like. Isn't the point here to INCREASE throughput? LOL - yes it is! Josh wanted to know what the overhead was for the queue machinery itself, so I'm running a test to show that (i.e so I have a queue with the thresholds set higher than the test will load them). In the situation where (say) 11 concurrent queries of a certain type make your system become unusable, but 10 are fine, then constraining it to have a max of 10 will tend to improve throughput. By how much is hard to say, for instance preventing the Linux OOM killer shutting postgres down would be infinite I guess :-) Hmm. Well, those numbers seem awfully high, for what you're doing, then. An admission control mechanism that's just letting everything in shouldn't knock 5% off performance (let alone 30%). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Branch created, let the experiment begin ...
As decided at this years hackers conference, we are branching REL9_0_STABLE *before* the release, instead of after. The hope is that we won't be taking away resources from finishing the release, but still allow ppl to continue to work on projects that are for 9.1. The branch is now created. -- 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] Admission Control
On 09/07/10 15:57, Robert Haas wrote: Hmm. Well, those numbers seem awfully high, for what you're doing, then. An admission control mechanism that's just letting everything in shouldn't knock 5% off performance (let alone 30%). Yeah it does, on the other hand both Josh and I were trying to elicit the worst case overhead. Cheers Mark -- 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] Branch created, let the experiment begin ...
On Thu, Jul 8, 2010 at 11:58 PM, Marc G. Fournier scra...@postgresql.org wrote: As decided at this years hackers conference, we are branching REL9_0_STABLE *before* the release, instead of after. The hope is that we won't be taking away resources from finishing the release, but still allow ppl to continue to work on projects that are for 9.1. The branch is now created. Before we begin this experiment, we need to stamp HEAD as 9.1devel. As of this writing, it is still stamped as 9.0beta3. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Branch created, let the experiment begin ...
Robert Haas robertmh...@gmail.com writes: Before we begin this experiment, we need to stamp HEAD as 9.1devel. Done. 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] [COMMITTERS] pgsql: Stamp HEAD as 9.1devel.
On Fri, Jul 9, 2010 at 12:10 AM, Tom Lane t...@postgresql.org wrote: Log Message: --- Stamp HEAD as 9.1devel. (And there was much rejoicing.) /me opens a beer. How long should I wait before I start breaking things? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Admission Control
On Fri, Jul 9, 2010 at 12:03 AM, Mark Kirkwood mark.kirkw...@catalyst.net.nz wrote: On 09/07/10 15:57, Robert Haas wrote: Hmm. Well, those numbers seem awfully high, for what you're doing, then. An admission control mechanism that's just letting everything in shouldn't knock 5% off performance (let alone 30%). Yeah it does, on the other hand both Josh and I were trying to elicit the worst case overhead. Even so... -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [COMMITTERS] pgsql: Stamp HEAD as 9.1devel.
On Jul 8, 2010, at 9:20 PM, Robert Haas wrote: /me opens a beer. How long should I wait before I start breaking things? I should think that you would need to drink at least 5-6 beers before you started stumbling around and breaking shit. 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] [COMMITTERS] pgsql: Stamp HEAD as 9.1devel.
Robert Haas robertmh...@gmail.com writes: Stamp HEAD as 9.1devel. (And there was much rejoicing.) /me opens a beer. How long should I wait before I start breaking things? Did you have any particular breakage in mind? I'm hesitant to have a whole lot of variance between REL9_0_STABLE and HEAD just yet, since we'll surely be doing a lot of double-patching for awhile. Localized patches, no problem, but this might not be the best time to s/foo/bar/g or something like that. 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] [COMMITTERS] pgsql: Stamp HEAD as 9.1devel.
On Fri, Jul 9, 2010 at 12:31 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Stamp HEAD as 9.1devel. (And there was much rejoicing.) /me opens a beer. How long should I wait before I start breaking things? Did you have any particular breakage in mind? I'm hesitant to have a whole lot of variance between REL9_0_STABLE and HEAD just yet, since we'll surely be doing a lot of double-patching for awhile. Localized patches, no problem, but this might not be the best time to s/foo/bar/g or something like that. Well, you can see for yourself what I've submitted for the next CF. As those things get reviewed, I'd like to get them committed; many of them need follow-on patches in which I don't want to invest too much effort until the initial patches go in. And then, too, other people have also submitted patches which I will be working on also. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] [COMMITTERS] pgsql: Stamp HEAD as 9.1devel.
Robert Haas robertmh...@gmail.com writes: How long should I wait before I start breaking things? Did you have any particular breakage in mind? Well, you can see for yourself what I've submitted for the next CF. You might want to hold off on the get_whatever_oid patches for a bit, but the other stuff I see there looks pretty localized. No objection to pressing forward with CF work otherwise. 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] Bug? Concurrent COMMENT ON and DROP object
(2010/07/07 11:31), Robert Haas wrote: On Tue, Jul 6, 2010 at 10:18 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haasrobertmh...@gmail.com writes: Obviously not. We don't need to acquire an AccessExclusiveLock to comment on an object - just something that will CONFLICT WITH an AccessExclusiveLock. So, use the same locking rules, perhaps, but take a much weaker lock, like AccessShareLock. Well, it probably needs to be a self-conflicting lock type, so that two COMMENTs on the same object can't run concurrently. But I agree AccessExclusiveLock is too strong: that implies locking out read-only examination of the object, which we don't want. Hmm... so, maybe ShareUpdateExclusiveLock? That looks to be the weakest thing that is self-conflicting. The others are ShareRowExclusiveLock, ExclusiveLock, and AccessExclusiveLock. Is it necessary to confirm existence of the database object being commented on after we got acquired the lock, isn't it? Since the logic of AcquireDeletionLock() requires us to provide argument as object-id form, but we have to translate the object name into object-id outside of the critical section, so the object being commented might be already dropped and committed before we got acquired the lock. Thanks, -- KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
