date:20130909


From: MauMau maumau...@gmail.com

OK, I'll take this approach.  That is:



I did as Tom san suggested.  Please review the attached patch.  I chose as 
common errnos by selecting those which are used in PosttgreSQL source code 
out of the error numbers defined in POSIX 2013.


As I said, lack of %m string has been making troubleshooting difficult, so I 
wish this to be backported at least 9.2.


Regards
MauMau


errno_str.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] Strange hanging bug in a simple milter

2013-09-09 Thread Heikki Linnakangas


On 09.09.2013 09:34, Vesa-Matti J Kari wrote:

Basically all that the authmilter now does is to connect to PostgreSQL in
authmilt_connect() and close the connection in authmilt_close(). Based on
the authmilter debug logging it seems to me that when the hanging occurs,
the authmilter never completes PQsetdbLogin().

Based on the document

   http://www.postgresql.org/docs/9.1/interactive/libpq-threading.html

I am sure libpq should be thread safe and on startup
the authmilter verifies that the libpq is indeed thread safe.


If you think you could set up a test enviroment using:

- Sendmail (or maybe Postfix)
- authmilter
- PostgreSQL

here is an authmilter-simplied.tar.gz package for you:

   http://www.helsinki.fi/~vmkari/authmilter-simplified.tar.gz

The README file contains a rough instructions outline on how to setup
things in order to reproduce the strange hanging bug.


Please note that when running two test message sender scripts in parallel,
the bug does not occur immediately, but only after between 1 to 5 minutes
of processing. Sometimes it may take even longer.


I managed to set that up and got it running. But it works fine for me, 
does not hang.


I'd suggest poking around with gdb, to see where it hangs. Also, run 
select * from pg_stat_activity from a psql session to see what's 
happening inside the database. log_connections=on and 
log_disconnections=on would also be a good idea.


PS. You'll need to escape the strings in the queries, to avoid SQL 
injection.


- Heikki


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

[HACKERS] Is this a correct recommendation for Solaris 10 kernel settings?


Hello, Josh, all,

Could you explain why the following paragraph is necessary?  I'm afraid this 
is not correct and we need to remove it, because:

1. One PostgreSQL instance only needs one shmid.
2. The calculation of the number of semids is shown before, so we don't say 
4096.
3. PostgreSQL does not use IPC message queues. max-msg-ids is the number of 
message queue ids, isn't it?


http://www.postgresql.org/docs/current/static/kernel-resources.html#SYSVIPC

[Excerpt]
Other recommended kernel setting changes for database servers which will 
have a large number of connections are:

project.max-shm-ids=(priv,32768,deny)
project.max-sem-ids=(priv,4096,deny)
project.max-msg-ids=(priv,4096,deny)


I'm asking you because I found your proposal of the above article here:

http://www.postgresql.org/message-id/4be9c10a.4040...@agliodbs.com


Regards
MauMau



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

Re: [HACKERS] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

On 9/6/13 10:37 AM, Tom Lane wrote:
 BTW: personally, I would say that what you're looking at is a glibc bug.
 I always thought the contract of gettext was to return the ASCII version
 if it fails to produce a translated version.  That might not be what the
 end user really wants to see, but surely returning something like ???
 is completely useless to anybody.

The question marks come from iconv.  Take a look at what this prints:

iconv po/ja.po -f utf-8 -t us-ascii//translit

If you use GNU libiconv, this will print a bunch of question marks.
Other implementations will probably not understand //translit and just
fail the conversion.

I think the use of //translit by gettext is poor judgement, because my
experiments show that the quality of the results is poor and not useful
for a user interface.

My suggestion in this matter is to disable gettext processing when
LC_CTYPE is set to C.  We could log a warning when LC_MESSAGES is set to
something and LC_CTYPE is set to C.  Or just do the warning and keep
logging.  Something like that.



-- 
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] Strange hanging bug in a simple milter

2013-09-09 Thread Vesa-Matti J Kari


Hello,

On Mon, 9 Sep 2013, Heikki Linnakangas wrote:

 I managed to set that up and got it running.

Many thanks for taking the time.

 But it works fine for me, does not hang.

Okay. Have you tried increasing the iterations for the smtp sender
scripts? And could you please specify what is your test environment like
(i.e. OS and the related library versions)?

 I'd suggest poking around with gdb, to see where it hangs.

I have actually done that, but it only show the main listener thread from
the libmilter library:

(gdb) bt
#0  0x7fe64bdd0313 in poll () from /lib/x86_64-linux-gnu/libc.so.6
#1  0x7fe64c4f7b46 in mi_listener () from /usr/lib/libmilter.so.1.0.1
#2  0x7fe64c4f8707 in smfi_main () from /usr/lib/libmilter.so.1.0.1
#3  0x00402c8f in main (argc=15, argv=0x7fffa6560e68) at
authmilter.c:699

Hmmm. The man page mentioned no threads, but Google was helpful and
suggested info threads so here goes:

(I hope alpine will not wrap these long lines)

(gdb) info threads
  Id   Target Id Frame
  9Thread 0x7fe64700c700 (LWP 14362) authmilter 0x7fe64c0b69f7 in 
do_sigwait () from /lib/x86_64-linux-gnu/libpthread.so.0
  8Thread 0x7fe64680b700 (LWP 14363) authmilter 0x7fe64bdd0313 in 
poll () from /lib/x86_64-linux-gnu/libc.so.6
  7Thread 0x7fe645809700 (LWP 14365) authmilter 0x7fe64c0b589c in 
__lll_lock_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
  6Thread 0x7fe645008700 (LWP 22404) authmilter 0x7fe64c0b589c in 
__lll_lock_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
  5Thread 0x7fe64600a700 (LWP 27263) authmilter 0x7fe64c0b589c in 
__lll_lock_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
  4Thread 0x7fe644807700 (LWP 27264) authmilter 0x7fe64c0b589c in 
__lll_lock_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
  3Thread 0x7fe62700 (LWP 27283) authmilter 0x7fe64c0b589c in 
__lll_lock_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
  2Thread 0x7fe62f7fe700 (LWP 27284) authmilter 0x7fe64c0b589c in 
__lll_lock_wait () from /lib/x86_64-linux-gnu/libpthread.so.0
* 1Thread 0x7fe64c8fd740 (LWP 14361) authmilter 0x7fe64bdd0313 in 
poll () from /lib/x86_64-linux-gnu/libc.so.6

It looks like a deadlock situation of some kind...

(gdb) thread 2
[Switching to thread 2 (Thread 0x7fe62f7fe700 (LWP 27284))]
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
(gdb) bt
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#1  0x7fe64c0b1065 in _L_lock_858 () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#2  0x7fe64c0b0eba in pthread_mutex_lock () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#3  0x7fe64c2df200 in ?? () from /usr/lib/libpq.so.5
#4  0x7fe64b78a5f5 in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#5  0x7fe64b77a915 in RSA_new_method () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#6  0x7fe64b77d64d in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#7  0x7fe64b7b9bf2 in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#8  0x7fe64b7bc6d1 in ASN1_item_ex_d2i () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#9  0x7fe64b7bd0c4 in ASN1_item_d2i () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#10 0x7fe64b77ea2f in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#11 0x7fe64b7b461a in X509_PUBKEY_get () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#12 0x7fe64b7d119a in X509_get_pubkey_parameters () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#13 0x7fe64b7d1398 in X509_verify_cert () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#14 0x7fe64bac52f8 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#15 0x7fe64baa2ef3 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#16 0x7fe64baa7222 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#17 0x7fe64c2dffcb in ?? () from /usr/lib/libpq.so.5
#18 0x7fe64c2d0c5e in PQconnectPoll () from /usr/lib/libpq.so.5
#19 0x7fe64c2d1e3e in ?? () from /usr/lib/libpq.so.5
#20 0x7fe64c2d26f8 in PQsetdbLogin () from /usr/lib/libpq.so.5
#21 0x00401ba5 in authmilt_connect (ctx=0xe81b60, 
hostname=0x7fe628c0 localhost, hostaddr=0x7fe62f7fdce0) at 
authmilter.c:212
#22 0x7fe64c4f69dc in ?? () from /usr/lib/libmilter.so.1.0.1
#23 0x7fe64c4f5f5f in mi_engine () from /usr/lib/libmilter.so.1.0.1
#24 0x7fe64c4fada6 in ?? () from /usr/lib/libmilter.so.1.0.1
#25 0x7fe64c0aee9a in start_thread () from
/lib/x86_64-linux-gnu/libpthread.so.0
#26 0x7fe64bddbccd in clone () from /lib/x86_64-linux-gnu/libc.so.6
#27 0x in ?? ()

(gdb) thread 3
[Switching to thread 3 (Thread 0x7fe62700 (LWP 27283))]
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
(gdb) bt
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#1  0x7fe64c0b1065 in _L_lock_858 () from

Re: [HACKERS] ENABLE/DISABLE CONSTRAINT NAME

On 9/3/13 3:13 AM, wangs...@highgo.com.cn wrote:
 Drop/build and disable/enable constraint has no fundamental difference,
 and could achieve the same purpose.What I do also more convenient for
 the user.
 Recording the disabled constraints is easier than recoding all the
 constrains.

Note that other schema objects can depend on the existence of
constraints.  For example, the validity of a view might depend on the
existence of a primary key constraint.  What would you do with the view
if the primary key constraint is temporarily disabled?

 What's more, a lot of people ever asked about turing off constraint and
 The sql2008 support this.So I think it's necessary in some ways.

I don't see this in the SQL standard.  There is [NOT] ENFORCED, but
that's something different.  Implementing that instead might actually
address the above concern.



-- 
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 fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

Peter Eisentraut pete...@gmx.net writes:
 My suggestion in this matter is to disable gettext processing when
 LC_CTYPE is set to C.  We could log a warning when LC_MESSAGES is set to
 something and LC_CTYPE is set to C.  Or just do the warning and keep
 logging.  Something like that.

Meh.  Seems that would only prevent one specific instance of the general
problem that strerror can fail to translate its result.  Other locale
combinations might create the same kind of failure.

More generally, though, is strerror actually using gettext at all, or
some homegrown implementation?  As I said upthread, I would expect that
gettext(foo) returns the given ASCII string foo if it fails to create
a translated version.  This is evidently not what's happening in strerror.

It's way past time to look into the glibc sources and see what it's
actually doing...

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 fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

On 9/9/13 10:25 AM, Tom Lane wrote:
 Peter Eisentraut pete...@gmx.net writes:
 My suggestion in this matter is to disable gettext processing when
 LC_CTYPE is set to C.  We could log a warning when LC_MESSAGES is set to
 something and LC_CTYPE is set to C.  Or just do the warning and keep
 logging.  Something like that.
 
 Meh.  Seems that would only prevent one specific instance of the general
 problem that strerror can fail to translate its result.  Other locale
 combinations might create the same kind of failure.

True.  There isn't much we can do, really.  If your LC_MESSAGES and
LC_CTYPE don't get along, you get what you asked for.  This isn't
specific to PostgreSQL:

$ LC_CTYPE=C LC_MESSAGES=ja_JP.utf8 ls --foo
ls: ???`--foo'??
 `ls --help' .

 More generally, though, is strerror actually using gettext at all, or
 some homegrown implementation?  As I said upthread, I would expect that
 gettext(foo) returns the given ASCII string foo if it fails to create
 a translated version.  This is evidently not what's happening in strerror.

That is correct.  It returns the original string if it cannot find a
translation or the character conversion of the translation fails.  But
the character conversion to US-ASCII//TRANSLIT does not fail.  It just
produces an undesirable result.  If you patch the gettext source to
remove the //TRANSLIT, you will get the result you want.




-- 
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] Hstore: Query speedups with Gin index

2013-09-09 Thread Blake Smith

Thanks for getting back to me about this change Oleg. I took your advice
and reworked the patch by adding a new hstore gin opclass
(gin_hstore_combined_ops) and leaving the functionality of the default
hstore gin opclass the same. This should prevent the on-disk compatibility
issues from the first patch, and allow users to select the different
indexing method when they build the index. The hstore regression suite is
passing for me locally with the --enable-cassert configure flag. Please let
me know what you think and if there is any other work that would need to be
done (style cleanups, updating documentation, etc) to get this merged.

Thanks!

Blake






On Fri, Sep 6, 2013 at 1:47 PM, Oleg Bartunov obartu...@gmail.com wrote:

 Blake,

 I think it's better to implement this patch as a separate opclass, so
 users will have option to choose indexing.

 Oleg


 On Tue, Sep 3, 2013 at 6:24 PM, Blake Smith blakesmi...@gmail.com wrote:

 Thanks for the feedback everyone. I've attached the patch that we are now
 running in production to service our hstore include queries. We rebuilt the
 index to account for the on-disk incompatibility. I've submitted the patch
 to commitfest here:
 https://commitfest.postgresql.org/action/patch_view?id=1203

 Michael: I don't have a formal benchmark, but several of our worst
 queries went from 10-20 seconds per query down to 50-400 ms. These are
 numbers we've seen when testing real production queries against our
 production dataset with real world access patterns.
 Oleg: Thanks for your thoughts on this change. As for the spgist / gin
 work you're doing, is there anything you need help with or are you still in
 the research phase? I'd love to help get something more robust merged into
 mainline if you think there's collaborative work to be done (even if it's
 only user testing).

 Thanks,

 Blake




 On Wed, Aug 28, 2013 at 12:40 PM, Andres Freund 
 and...@2ndquadrant.comwrote:

 On 2013-08-28 13:31:22 -0400, Bruce Momjian wrote:
  On Sun, Aug 25, 2013 at 10:11:50PM -0400, Tom Lane wrote:
   Michael Paquier michael.paqu...@gmail.com writes:
On Thu, Aug 22, 2013 at 11:55 PM, Blake Smith 
 blakesmi...@gmail.com wrote:
The combined entry is used to support contains (@) queries,
 and the key
only item is used to support key contains (?) queries. This
 change seems
to help especially with hstore keys that have high cardinalities.
 Downsides
of this change is that it requires an index rebuild, and the
 index will be
larger in size.
  
Index rebuild would be a problem only for minor releases,
  
   That's completely false; people have expected major releases to be
   on-disk-compatible for several years now.  While there probably will
 be
   future releases in which we are willing to break storage
 compatibility,
   a contrib module doesn't get to dictate that.
  
   What might be a practical solution, especially if this isn't always a
   win (which seems likely given the index-bloat risk), is to make
 hstore
   offer two different GIN index opclasses, one that works the
 traditional
   way and one that works this way.
  
   Another thing that needs to be taken into account here is Oleg and
   Teodor's in-progress work on extending hstore:
   https://www.pgcon.org/2013/schedule/events/518.en.html
   I'm not sure if this patch would conflict with that at all, but it
   needs to be considered.
 
  We can disallow in-place upgrades for clusters that use certain contrib
  modules --- we have done that in the past.

 But that really cannot be acceptable for hstore. The probably most
 widely used extension there is.

 Greetings,

 Andres Freund

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




 --
 Blake Smith
 http://blakesmith.me
 @blakesmith


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





-- 
Blake Smith
http://blakesmith.me
@blakesmith


0001-Add-gin_hstore_combined_ops-hstore-indexing-opclass.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] Strange hanging bug in a simple milter

2013-09-09 Thread Stephen Frost

Vesa-Matti, Heikki,

* Heikki Linnakangas (hlinnakan...@vmware.com) wrote:
 On 09.09.2013 15:36, Vesa-Matti J Kari wrote:
 If I interpret this correctly, threads #2 and #3 are waiting for the same
 lock but they make no progress.
 
 A-ha, the deadlock happens while doing SSL stuff. I didn't have SSL
 enabled in my test server. As soon as I turned it on, it hung.
 
 Attached is a small stand-alone test program to reproduce it. You
 can pass a libpq connection string as argument to it.

Interesting...  Which version of libpq were you working against?  I see
that Vesa-Matti had the problem happen w/ 9.1.9, which should have been
before the changes that I made to add locking around our usage of
SSL_context, as otherwise we would end up in situations where we'd dump
core, but he also had it with 9.3rc1, which should have included it.  I
had tested the patch w/ a pretty good amount of concurrent threads fired
off from a little python script and didn't run into any deadlocks
there..

Vesa-Matti, was this working previously, and are you sure you were
testing with 9.3rc1's libpq?  Heikki, which are you testing against and
perhaps you might try before and after?  I'll be able to look into it
more in a few hours also.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Strange hanging bug in a simple milter

2013-09-09 Thread Heikki Linnakangas


On 09.09.2013 15:36, Vesa-Matti J Kari wrote:

It looks like a deadlock situation of some kind...

(gdb) thread 2
[Switching to thread 2 (Thread 0x7fe62f7fe700 (LWP 27284))]
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
(gdb) bt
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#1  0x7fe64c0b1065 in _L_lock_858 () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#2  0x7fe64c0b0eba in pthread_mutex_lock () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#3  0x7fe64c2df200 in ?? () from /usr/lib/libpq.so.5
#4  0x7fe64b78a5f5 in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#5  0x7fe64b77a915 in RSA_new_method () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#6  0x7fe64b77d64d in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#7  0x7fe64b7b9bf2 in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#8  0x7fe64b7bc6d1 in ASN1_item_ex_d2i () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#9  0x7fe64b7bd0c4 in ASN1_item_d2i () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#10 0x7fe64b77ea2f in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#11 0x7fe64b7b461a in X509_PUBKEY_get () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#12 0x7fe64b7d119a in X509_get_pubkey_parameters () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#13 0x7fe64b7d1398 in X509_verify_cert () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#14 0x7fe64bac52f8 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#15 0x7fe64baa2ef3 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#16 0x7fe64baa7222 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#17 0x7fe64c2dffcb in ?? () from /usr/lib/libpq.so.5
#18 0x7fe64c2d0c5e in PQconnectPoll () from /usr/lib/libpq.so.5
#19 0x7fe64c2d1e3e in ?? () from /usr/lib/libpq.so.5
#20 0x7fe64c2d26f8 in PQsetdbLogin () from /usr/lib/libpq.so.5
#21 0x00401ba5 in authmilt_connect (ctx=0xe81b60, hostname=0x7fe628c0 
localhost, hostaddr=0x7fe62f7fdce0) at authmilter.c:212
#22 0x7fe64c4f69dc in ?? () from /usr/lib/libmilter.so.1.0.1
#23 0x7fe64c4f5f5f in mi_engine () from /usr/lib/libmilter.so.1.0.1
#24 0x7fe64c4fada6 in ?? () from /usr/lib/libmilter.so.1.0.1
#25 0x7fe64c0aee9a in start_thread () from
/lib/x86_64-linux-gnu/libpthread.so.0
#26 0x7fe64bddbccd in clone () from /lib/x86_64-linux-gnu/libc.so.6
#27 0x in ?? ()

(gdb) thread 3
[Switching to thread 3 (Thread 0x7fe62700 (LWP 27283))]
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
(gdb) bt
#0  0x7fe64c0b589c in __lll_lock_wait () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#1  0x7fe64c0b1065 in _L_lock_858 () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#2  0x7fe64c0b0eba in pthread_mutex_lock () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#3  0x7fe64c2df200 in ?? () from /usr/lib/libpq.so.5
#4  0x7fe64b78a5f5 in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#5  0x7fe64b77a915 in RSA_new_method () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#6  0x7fe64b77d64d in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#7  0x7fe64b7b9bf2 in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#8  0x7fe64b7bc6d1 in ASN1_item_ex_d2i () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#9  0x7fe64b7bd0c4 in ASN1_item_d2i () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#10 0x7fe64b77ea2f in ?? () from /lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#11 0x7fe64b7b461a in X509_PUBKEY_get () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#12 0x7fe64b7d119a in X509_get_pubkey_parameters () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#13 0x7fe64b7d1398 in X509_verify_cert () from 
/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#14 0x7fe64bac52f8 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#15 0x7fe64baa2ef3 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#16 0x7fe64baa7222 in ?? () from /lib/x86_64-linux-gnu/libssl.so.1.0.0
#17 0x7fe64c2dffcb in ?? () from /usr/lib/libpq.so.5
#18 0x7fe64c2d0c5e in PQconnectPoll () from /usr/lib/libpq.so.5
#19 0x7fe64c2d1e3e in ?? () from /usr/lib/libpq.so.5
#20 0x7fe64c2d26f8 in PQsetdbLogin () from /usr/lib/libpq.so.5
#21 0x00401ba5 in authmilt_connect (ctx=0xe818e0, hostname=0x7fe6280008c0 
localhost, hostaddr=0x7fe62fffece0) at authmilter.c:212
#22 0x7fe64c4f69dc in ?? () from /usr/lib/libmilter.so.1.0.1
#23 0x7fe64c4f5f5f in mi_engine () from /usr/lib/libmilter.so.1.0.1
#24 0x7fe64c4fada6 in ?? () from /usr/lib/libmilter.so.1.0.1
#25 0x7fe64c0aee9a in start_thread () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#26 0x7fe64bddbccd in clone () from /lib/x86_64-linux-gnu/libc.so.6
#27 0x in ?? ()

If I interpret this correctly, threads #2 and #3 are waiting for the same
lock but they make no progress.


A-ha, the deadlock happens while doing

Re: [HACKERS] improve Chinese locale performance

On Mon, Sep 9, 2013 at 5:22 AM, Quan Zongliang quanzongli...@gmail.com wrote:
 Understood.

 I just try to speed up text compare, not redesign locale.

 Do you have a plan to do this?

Not any time soon, anyway.

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


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

Re: [HACKERS] Strange hanging bug in a simple milter

2013-09-09 Thread Heikki Linnakangas


On 09.09.2013 18:20, Stephen Frost wrote:

Vesa-Matti, Heikki,

* Heikki Linnakangas (hlinnakan...@vmware.com) wrote:

On 09.09.2013 15:36, Vesa-Matti J Kari wrote:

If I interpret this correctly, threads #2 and #3 are waiting for the same
lock but they make no progress.


A-ha, the deadlock happens while doing SSL stuff. I didn't have SSL
enabled in my test server. As soon as I turned it on, it hung.

Attached is a small stand-alone test program to reproduce it. You
can pass a libpq connection string as argument to it.


Interesting...  Which version of libpq were you working against?  I see
that Vesa-Matti had the problem happen w/ 9.1.9, which should have been
before the changes that I made to add locking around our usage of
SSL_context, as otherwise we would end up in situations where we'd dump
core, but he also had it with 9.3rc1, which should have included it.  I
had tested the patch w/ a pretty good amount of concurrent threads fired
off from a little python script and didn't run into any deadlocks
there..



Vesa-Matti, was this working previously, and are you sure you were
testing with 9.3rc1's libpq?  Heikki, which are you testing against and
perhaps you might try before and after?  I'll be able to look into it
more in a few hours also.


Thanks! I tested with git master.

I added printf()s into the pq_lockingcallback function, and got a trace 
where both threads got stuck waiting for lock 10 in the pq_lockarray. It 
looks like someone is failing to release it. The backtrace for both 
threads look like this:


#0  __lll_lock_wait () at 
../nptl/sysdeps/unix/sysv/linux/x86_64/lowlevellock.S:135
#1  0x7fad49826f3c in _L_lock_974 () from 
/lib/x86_64-linux-gnu/libpthread.so.0
#2  0x7fad49826d8b in __GI___pthread_mutex_lock 
(mutex=0x7fad3800a260) at pthread_mutex_lock.c:64
#3  0x7fad49a53f08 in pq_lockingcallback (line=175, 
file=0x7fad48fa8bb3 x_pubkey.c, n=10, mode=optimized out) at 
fe-secure.c:872
#4  pq_lockingcallback (mode=optimized out, n=10, file=0x7fad48fa8bb3 
x_pubkey.c, line=175) at fe-secure.c:868
#5  0x7fad48f396ab in X509_PUBKEY_get () from 
/usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#6  0x7fad48f56292 in X509_get_pubkey_parameters () from 
/usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#7  0x7fad48f5649c in X509_verify_cert () from 
/usr/lib/x86_64-linux-gnu/libcrypto.so.1.0.0
#8  0x7fad4924f14a in ?? () from 
/usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
#9  0x7fad4922ce1c in ?? () from 
/usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
#10 0x7fad492310d2 in ?? () from 
/usr/lib/x86_64-linux-gnu/libssl.so.1.0.0
#11 0x7fad49a54c67 in open_client_SSL (conn=0x7fad380397d0) at 
fe-secure.c:1463

#12 pqsecure_open_client (conn=conn@entry=0x7fad380397d0) at fe-secure.c:306
#13 0x7fad49a44fb6 in PQconnectPoll (conn=conn@entry=0x7fad380397d0) 
at fe-connect.c:2123
#14 0x7fad49a4618e in connectDBComplete 
(conn=conn@entry=0x7fad380397d0) at fe-connect.c:1521
#15 0x7fad49a46b47 in PQconnectdb (conninfo=optimized out) at 
fe-connect.c:516

#16 0x004007b6 in test_connect (threadid=2) at threaded-connect.c:25
#17 0x0040086e in run_thread (arg=0x600e04 two) at 
threaded-connect.c:55
#18 0x7fad49824e0e in start_thread (arg=0x7fad48203700) at 
pthread_create.c:311
#19 0x7fad4955993d in clone () at 
../sysdeps/unix/sysv/linux/x86_64/clone.S:113


Sometimes the lockup happens differently, with one thread hung up in 
SSL_init() and another waiting for the first one on ssl_config_mutex, 
for example.


A good next step might be to create a standalone program that doesn't 
use libpq at all, but just calls X509_verify_cert() concurrently in two 
threads. Or open plain SSL connections. If the deadlock can be 
reproduced with that, then we could just report the bug to the OpenSSL 
and hope that they can figure it out.


- Heikki
diff --git a/src/interfaces/libpq/fe-secure.c b/src/interfaces/libpq/fe-secure.c
index 3bd0113..3e70306 100644
--- a/src/interfaces/libpq/fe-secure.c
+++ b/src/interfaces/libpq/fe-secure.c
@@ -871,11 +871,15 @@ pq_lockingcallback(int mode, int n, const char *file, int line)
 	{
 		if (pthread_mutex_lock(pq_lockarray[n]))
 			PGTHREAD_ERROR(failed to lock mutex);
+		printf(%ld locking callback:   lock %d %s:%d\n, pthread_self(), n, file, line);
+		fflush(stdout);
 	}
 	else
 	{
 		if (pthread_mutex_unlock(pq_lockarray[n]))
 			PGTHREAD_ERROR(failed to unlock mutex);
+		printf(%ld locking callback: unlock %d %s:%d\n, pthread_self(), n, file, line);
+		fflush(stdout);
 	}
 }
 #endif   /* ENABLE_THREAD_SAFETY */


lock-trace.gz
Description: GNU Zip compressed 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] Strange hanging bug in a simple milter

2013-09-09 Thread Alvaro Herrera

Heikki Linnakangas wrote:

 I'll dig into that, but right now it seems like an OpenSSL or
 libcrypto bug to me. Or something in the way we use them, although I
 can't see anything obviously wrong in the libpq code at a quick
 glance.

Can you please try with ssl_renegotiation_limit=0?

[ looks ]  Uh, actually you don't even send data in those connections in
your test program, do you?  Maybe there's a problem with the mutex stuff
committed recently by Stephen.

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


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

Re: [HACKERS] [RFC] overflow checks optimized away

On Sat, Sep 7, 2013 at 6:55 PM, Greg Stark st...@mit.edu wrote:
 Should these patches be applied?

 I have a copy of the program and was going to take care of this.

When?

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


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

Re: [HACKERS] dynamic shared memory

On Fri, Sep 6, 2013 at 3:40 PM, Jim Nasby j...@nasby.net wrote:
 The specific scenario I'm worried about is something like a PANIC in the
 middle of the snprintf call in dsm_write_state_file(). That would leave that
 file in a completely unknown state so who knows what would then happen on
 restart. ISTM that writing a temp file and then doing a filesystem mv would
 eliminate that issue.

Doing an atomic rename would eliminate the possibility of seeing a
partially written file, but a partially written file is mostly
harmless: we'll interpret whatever bytes we see as as integer and try
to use that as a DSM key.  Then we'll just see that no such shared
memory key exists (probably) or that we don't own it (probably) or
that it doesn't look like a valid control segment (probably) and
ignore it.

If someone does a kill -9 the postmaster in the middle of write()
creating a partially written file, and the partially written file
happens to identify another shared memory segment owned by the same
user ID with the correct magic number and header contents to be
interpreted as a control segment, then we will indeed erroneously blow
away that purported control segment and all other segments to which it
points.  I suppose we can stick in a rename() there just to completely
rule out that scenario, but it's pretty bloody unlikely anyway.

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


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

Re: [HACKERS] [PERFORM] encouraging index-only scans

2013-09-09 Thread Jeff Janes

On Sun, Sep 8, 2013 at 8:49 PM, Amit Kapila amit.kapil...@gmail.com wrote:
 On Mon, Sep 9, 2013 at 2:35 AM, Jeff Janes jeff.ja...@gmail.com wrote:
 I thought it was well known, but maybe I was overly optimistic.  I've
 considered IOS to be mostly useful for data mining work on read-mostly
 tables, which you would probably vacuum manually after a bulk load.

 For transactional tables, I think that trying to keep the vm set-bit
 density high enough would be a losing battle.  If we redefined the
 nature of the vm so that doing a HOT update would not clear the
 visibility bit, perhaps that would change the outcome of this battle.

 Wouldn't it make the Vacuum bit in-efficient in the sense that it will
 skip some of the pages in which there are only
 HOT updates for cleaning dead rows.

Maybe.  But anyone is competent to clean up dead rows from HOT
updates, it is not exclusively vacuum that can do it, like it is for
non-HOT tuples.  So I think any inefficiency would be very small.

Cheers,

Jeff


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

Re: [HACKERS] only linestyle is NULL as default

On Sat, Sep 7, 2013 at 3:57 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
 when I checked psql and pset without any arguments patch, I found so only
 popt-topt.line_style is initialized to NULL as default. All other popt
 variables are not null.

 Can we fixed?

I suggest that you reply to the correct thread instead of starting a
new one.  The patch author is much more likely to notice it that way.

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


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

Re: [HACKERS] [rfc] overhauling pgstat.stat

On Sat, Sep 7, 2013 at 6:57 PM, Tomas Vondra t...@fuzzy.cz wrote:
 Don't forget the stats are written only by the postmaster, all the
 regular backends only read it (and eventually send updates back).

The postmaster, or the stats collector?

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


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

Re: [HACKERS] Strange hanging bug in a simple milter

2013-09-09 Thread Stephen Frost

Alvaro,

* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote:
 Heikki Linnakangas wrote:
  I'll dig into that, but right now it seems like an OpenSSL or
  libcrypto bug to me. Or something in the way we use them, although I
  can't see anything obviously wrong in the libpq code at a quick
  glance.
 
 Can you please try with ssl_renegotiation_limit=0?
 
 [ looks ]  Uh, actually you don't even send data in those connections in
 your test program, do you?  Maybe there's a problem with the mutex stuff
 committed recently by Stephen.

I was wondering about that also, but it was apparently an issue even
before that change (it was reported against 9.1.9).  Also, Heikki's
analysis appears to show cases where two threads end up waiting on the
same entry in the lockarray, which I don't think my changes would have
impacted at all.

In any case, I hope to find time this afternoon/evening to try this
against libpq from before and after, just to be sure and rule out that
patch.  Assuming that pans out, I tend to agree w/ Heikki that we should
test this outside of libpq entirely and see if we can reproduce it.
Even if we're able to do that, we may need to consider ways to fix it
ourselves (perhaps be holding heavier locks or something), as we have no
idea how long it'll take an OpenSSL fix to happen..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [HACKERS] Custom Plan node

On Fri, Sep 6, 2013 at 7:03 PM, Tom Lane t...@sss.pgh.pa.us wrote:
 Robert Haas robertmh...@gmail.com writes:
 I find this a somewhat depressing response.  Didn't we discuss this
 exact design at the developer meeting in Ottawa?  I thought it sounded
 reasonable to you then, or at least I don't remember you panning it.

 What I recall saying is that I didn't see how the planner side of it would
 work ... and I still don't see that.  I'd be okay with committing
 executor-side fixes only if we had a vision of where we'd go on the
 planner side; but this patch doesn't offer any path forward there.

 This is not unlike the FDW stuff, where getting a reasonable set of
 planner APIs in place was by far the hardest part (and isn't really done
 even yet, since you still can't do remote joins or remote aggregation in
 any reasonable fashion).  But you can do simple stuff reasonably simply,
 without reimplementing all of the planner along the way --- and I think
 we should look for some equivalent level of usefulness from this before
 we commit it.

I do think there are problems with this as written.  The example
consumer of the hook seems to contain a complete list of plan nodes,
which is an oxymoron in the face of a facility to add custom plan
nodes.

But, I guess I'm not yet convinced that one-for-one substitution of
nodes is impossible even with something about this simple.  If someone
can do a post-pass over the plan tree and replace a SeqScan node with
an AwesomeSeqScan node or a Sort node with a RadixSort node, would
that constitute a sufficient POC to justify this infrastructure?
Obviously, what you'd really want is to be able to inject those nodes
(with proper costing) at the time they'd otherwise be generated, since
it could affect whether or not a path involving a substituted node
survives in the first place, but I'm not sure it's reasonable to
expect the planner infrastructure for such changes in the same path as
the executor hooks.

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


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

Re: [HACKERS] [rfc] overhauling pgstat.stat

2013-09-09 Thread Tomas Vondra

On 9 Září 2013, 18:50, Robert Haas wrote:
 On Sat, Sep 7, 2013 at 6:57 PM, Tomas Vondra t...@fuzzy.cz wrote:
 Don't forget the stats are written only by the postmaster, all the
 regular backends only read it (and eventually send updates back).

 The postmaster, or the stats collector?



Stats collector, of course. I meant to point out that the write activity
comes from a single dedicated process, which may not be that obvious, and
I somehow managed to name the incorrect one.

Tomas



-- 
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] Custom Plan node

Robert Haas robertmh...@gmail.com writes:
 But, I guess I'm not yet convinced that one-for-one substitution of
 nodes is impossible even with something about this simple.  If someone
 can do a post-pass over the plan tree and replace a SeqScan node with
 an AwesomeSeqScan node or a Sort node with a RadixSort node, would
 that constitute a sufficient POC to justify this infrastructure?

No, for exactly the reason you mention: such a change wouldn't have been
accounted for in the planner's other choices, and thus this isn't anything
more than a kluge.

In these specific examples you'd have to ask whether it wouldn't make more
sense to be modifying or hooking the executor's code for the existing plan
node types, anyway.  The main reason I can see for not attacking it like
that would be if you wanted the planner to do something different ---
which the above approach forecloses.

Let me be clear that I'm not against the concept of custom plan nodes.
But it was obvious from the beginning that making the executor deal with
them would be much easier than making the planner deal with them.  I don't
think we should commit a bunch of executor-side infrastructure in the
absence of any (ahem) plan for doing something realistic on the planner
side.  Either that infrastructure will go unused, or we'll be facing a
continual stream of demands for doubtless-half-baked planner changes
so that people can do something with it.

I'd be willing to put in the infrastructure as soon as it's clear that we
have a way forward, but not if it's never going to be more than a kluge.

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] strange IS NULL behaviour

On Sat, Sep 7, 2013 at 10:59 AM, Bruce Momjian br...@momjian.us wrote:
 Why don't you add the proposal to the commitfest?

 This issue is so much larger than the patch's validity that I don't see
 how that would work.

I hate to be rude here, but I think you're being ridiculous.  We have
a well-established procedure for getting patches reviewed around here,
and while it is not perfect, it mostly works.  If you try that
procedure and it doesn't work, then I think you have a right to
complain.  But to object, on the one hand, that people aren't going to
look at the patch, and then to refuse to add it to the tracking tool
that the project uses to ensure that patches get looked at, seems
patently unfair.

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


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

Re: [HACKERS] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

Peter Eisentraut pete...@gmx.net writes:
 On 9/9/13 10:25 AM, Tom Lane wrote:
 Meh.  Seems that would only prevent one specific instance of the general
 problem that strerror can fail to translate its result.  Other locale
 combinations might create the same kind of failure.

 True.  There isn't much we can do, really.  If your LC_MESSAGES and
 LC_CTYPE don't get along, you get what you asked for.  This isn't
 specific to PostgreSQL:

So should we just say this is pilot error?  It may be, but if we can work
around it with a reasonably small amount of effort/risk, I think it's
appropriate to do that.  The proposal to reject a strerror result that
starts with '?' sounds plausible to me.

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] [PERFORM] encouraging index-only scans

On Sun, Sep  8, 2013 at 12:47:35AM +0200, Andres Freund wrote:
 Hi,
 
 On 2013-09-07 12:50:59 -0400, Bruce Momjian wrote:
  That seems very complicated.  I think it would be enough to record the
  current xid at the time of the vacuum, and when testing for later
  vacuums, if that saved xid is earlier than the RecentGlobalXmin, and
  there have been no inserts/updates/deletes, we know that all of
  the pages can now be marked as allvisible.
 
 But that would constantly trigger vacuums, or am I missing something? Or
 what are you suggesting this xid to be used for?

OK, let me give some specifices.  Let's suppose we run a vacuum, and at
the time the current xid counter is 200.  If we later have autovacuum
check if it should vacuum, and there have been no dead rows generated
(no update/delete/abort), if the current RecentGlobalXmin is 200, then
we know that all the transactions that prevented all-visible marking the
last time we ran vacuum has completed.  That leaves us with just
inserts that could prevent all-visible.

If there have been no inserts, we can assume that we can vacuum just the
non-all-visible pages, and even if there are only 10, it just means we
have to read 10 8k blocks, not the entire table, because the all-visible
is set for all the rest of the pages.

Now, if there have been inserts, there are a few cases.  If the inserts
happened in pages that were previously marked all-visible, then we now
have pages that lost all-visible, and we probably don't want to vacuum
those.  Of course, we will not have recorded which pages changed, but
any decrease in the all-visible table count perhaps should have us
avoiding vacuum just to set the visibility map.  We should probably
update our stored vm bit-set count and current xid value so we can check
again later to see if things have sabilized.

If the vm-set bit count is the same as the last time autovacuum checked
the table, then the inserts happened either in the vm-bit cleared pages,
or in new data pages.  If the table size is the same, the inserts
happened in existing pages, so we probably don't want to vacuum.  If the
table size has increased, some inserts went into new pages, so we might
want to vacuum, but I am unclear how many new pages should force a
vacuum.

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

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


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

Re: [HACKERS] Custom Plan node

2013-09-09 Thread Stephen Frost

* Robert Haas (robertmh...@gmail.com) wrote:
 But, I guess I'm not yet convinced that one-for-one substitution of
 nodes is impossible even with something about this simple.  

Couldn't that be done with hooks in those specific plan nodes, or
similar..?  Of course, as Tom points out, that wouldn't address how the
costing is done and it could end up being wrong if the implementation of
the node is completely different.

All that said, I've already been wishing for a way to change how Append
works to allow for parallel execution through FDWs; eg: you have a bunch
of foreign tables (say, 32) to independent PG clusters on indepentdent
pieces of hardware which can all execute a given request in parallel.
With a UNION ALL view created over top of those tables, it'd be great if
we fired off all the queries at once and then went through collecting
the responses, instead of going through them serially..

The same approach could actually be said for Appends which go across
tablespaces, if you consider that independent tablespaces mean
independent and parallelizable I/O access.  Of course, all of this would
need to deal sanely with ORDER BY and LIMIT cases.

Thanks,

Stephen


signature.asc
Description: Digital signature

[HACKERS] Re: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

2013-09-09 Thread Noah Misch

On Mon, Sep 09, 2013 at 08:29:58AM -0400, Peter Eisentraut wrote:
 On 9/6/13 10:37 AM, Tom Lane wrote:
  BTW: personally, I would say that what you're looking at is a glibc bug.
  I always thought the contract of gettext was to return the ASCII version
  if it fails to produce a translated version.  That might not be what the
  end user really wants to see, but surely returning something like ???
  is completely useless to anybody.
 
 The question marks come from iconv.  Take a look at what this prints:
 
 iconv po/ja.po -f utf-8 -t us-ascii//translit
 
 If you use GNU libiconv, this will print a bunch of question marks.

Actually, GNU libiconv's iconv() decides that //translit is unimplementable
for some of the characters in that file, and it fails the conversion.  GNU
libc's iconv(), on the other hand, emits the question marks.

 I think the use of //translit by gettext is poor judgement, because my
 experiments show that the quality of the results is poor and not useful
 for a user interface.

It depends on the quality of the //translit implementation.  GNU libiconv's
seems pretty good.  It gives up for Japanese or Russian characters, so you get
the English messages.  For Polish, GNU libiconv transliterates like this:

msgstr nie można usunąć pliku lub katalogu \%s\: %s\n
msgstr nie mozna usuna'c pliku lub katalogu \%s\: %s\n

That's fair, considering what it has to work with.  Ideally, (a) GNU libc
should import the smarter transliteration code from GNU libiconv, and (b) GNU
gettext should check for weak //translit implementations and not use
//translit under such circumstances.

 My suggestion in this matter is to disable gettext processing when
 LC_CTYPE is set to C.  We could log a warning when LC_MESSAGES is set to
 something and LC_CTYPE is set to C.  Or just do the warning and keep
 logging.  Something like that.

In an ENCODING=UTF8, LC_CTYPE=C database, no transliteration should need to
happen, and no transliteration does happen for the PG messages.  I think
MauMau's original bind_textdomain_codeset() proposal was on the right track.
We would need to do that for every relevant 3rd-party message domain, though.
Ick.  This suggests to me that gettext really needs an API for overriding the
default codeset pertaining to message domains not subjected to
bind_textdomain_codeset().  In the meantime, adding bind_textdomain_codeset()
calls for known localized dependencies seems like a fine coping mechanism.

If we can reasonably detect when gettext is supplying useless ? messages,
that's good, too.

Thanks,
nm

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com


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

Re: [HACKERS] strange IS NULL behaviour

On Mon, Sep  9, 2013 at 12:37:25PM -0400, Robert Haas wrote:
 On Sat, Sep 7, 2013 at 10:59 AM, Bruce Momjian br...@momjian.us wrote:
  Why don't you add the proposal to the commitfest?
 
  This issue is so much larger than the patch's validity that I don't see
  how that would work.
 
 I hate to be rude here, but I think you're being ridiculous.  We have
 a well-established procedure for getting patches reviewed around here,
 and while it is not perfect, it mostly works.  If you try that
 procedure and it doesn't work, then I think you have a right to
 complain.  But to object, on the one hand, that people aren't going to
 look at the patch, and then to refuse to add it to the tracking tool
 that the project uses to ensure that patches get looked at, seems
 patently unfair.

The problem is that I don't believe this patch is commit-ready ---
someone needs to research the IS NULL tests in all areas of our code to
see if they match this patch, and I can't do that.  Is that something a
reviewer is going to be willing to do?  I don't think I have ever seen a
commit-fest item that still required serious research outside the patch
area before committing.  I could ask just for feedback, but I have
already received enough feedback to know I can't get the patch to a
ready-enough state.

I think requiring commit-fest reviewers come to the same conclusion is
just making extra work for them.  Still, if you want me to add it to the
next commit-fest, please let me know.

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

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


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

Re: [HACKERS] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

Noah Misch n...@leadboat.com writes:
 ... I think
 MauMau's original bind_textdomain_codeset() proposal was on the right track.

It might well be.  My objection was to the proposal for back-patching it
when we have little idea of the possible side-effects.  I would be fine
with handling that as a 9.4-only patch (preferably with the usual review
process).

 We would need to do that for every relevant 3rd-party message domain, though.
 Ick.

Yeah, and another question is whether 3rd-party code might not do its own
bind_textdomain_codeset() call with what it thinks is the right setting,
thereby overriding our attempted fix.

Still, libc is certainly the source of the vast majority of
potentially-translated messages that we might be passing through to users,
so fixing it would be a step forward.

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] file_fdw target file ownership

2013-09-09 Thread Daniel Vérité

   Tom Lane writes:

 Andres Freund and...@2ndquadrant.com writes:

  One would be to use open(O_NOFOLLOW)?
 
 That would only stop symlink attacks, not hardlink variants;
 and it'd probably stop some legitimate use-cases too.

The creation of the hardlink is denied by the OS based on the
attacker not having sufficient permissions to the target file.
In principle the mentioned loophole is limited to a symlink, which
is not restricted at create time.

Thinking a bit more about the scenario of the malicious writer,
I think the secure way to proceed for the superuser would be to
set up two directories, one with write permissions to the
producer of data, the other without.

The superuser would have to move the file from the writable
dir to the non-writable dir, before creating the foreign table. The
file itself should remain writable by the uploader if it's live data.
The data-producer has to be aware that updates happen
at a different path than uploads.

The problem is that it's really not intuitive. I can imagine unaware
admins implementing the insecure process without a second thought.

By contrast, if symlinks were followed only optionally, it would
be safer as a default choice and the installations that need symlinks could
still use something like:

CREATE FOREIGN TABLE (...) SERVER name 
  OPTIONS (symlink_allowed 'on', filename '/path/to/file', ...)

The mere existence of the option is a  hint that there are consequences
to consider.

Best regards,
-- 
 Daniel Vérité
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org 


-- 
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] file_fdw target file ownership

2013-09-09 Thread Andres Freund

On 2013-09-09 21:41:00 +0200, Daniel Vérité wrote:
Tom Lane writes:
 
  Andres Freund and...@2ndquadrant.com writes:
 
   One would be to use open(O_NOFOLLOW)?
  
  That would only stop symlink attacks, not hardlink variants;
  and it'd probably stop some legitimate use-cases too.
 
 The creation of the hardlink is denied by the OS based on the
 attacker not having sufficient permissions to the target file.
 In principle the mentioned loophole is limited to a symlink, which
 is not restricted at create time.

It only requires search privileges, doesn't it?

andres@alap2:~$ ln /etc/shadow /tmp/frak
andres@alap2:~$ cat /tmp/frak
cat: /tmp/frak: Permission denied
andres@alap2:~$ ls -l /tmp/frak
-rw-r- 2 root shadow 1652 Jun  4 22:05 /tmp/frak

There are patches around preventing that kind of thing, but they aren't
too widespread yet.

Greetings,

Andres Freund

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


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

Re: [HACKERS] lcr v5 - introduction of InvalidCommandId

On Thu, Sep 5, 2013 at 3:23 PM, Andres Freund and...@2ndquadrant.com wrote:
  Oh.  I hadn't looked at the patch, but I had (mis)read what Robert said
  to think that you were proposing introducing InvalidCommandId = 0x
  while leaving FirstCommandId alone.  That would make more sense to me as
  (1) it doesn't change the interpretation of anything that's (likely to be)
  on disk; (2) it allows the check for overflow in CommandCounterIncrement
  to not involve recovering from an *actual* overflow.  With the horsing
  around we've been seeing from the gcc boys lately

 Ok, I can do it that way. LCR obviously shouldn't care.

 It doesn't care to the point that the patch already does exactly what
 you propose. It's just my memory that remembered things differently.

 So, a very slightly updated patch attached.

Committed.

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


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

Re: [HACKERS] file_fdw target file ownership

Andres Freund and...@2ndquadrant.com writes:
 On 2013-09-09 21:41:00 +0200, Daniel Vérité wrote:
 Tom Lane writes:
 That would only stop symlink attacks, not hardlink variants;

 The creation of the hardlink is denied by the OS based on the
 attacker not having sufficient permissions to the target file.

 It only requires search privileges, doesn't it?

Yeah, it would be a mistake to assume that the OS will prevent a hardlink
operation based on file ownership.  Even if some OSes behave that way,
it's far from universal.

It's true that an attacker has to be able to name the target file to do a
hardlink, so if he lacks search privileges on a parent directory then he
can't hardlink (but he can still mount a symlink attack, if he knows what
the path name would be).  So preventing the symlink variant would help for
the particular case of a PG server key stored inside $PGDATA.  But it
doesn't seem to me that that's enough coverage to call it a solution.
People sometimes keep server keys elsewhere, for instance.

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] file_fdw target file ownership

2013-09-09 Thread Daniel Vérité

 Andres Freund writes

 andres@alap2:~$ ls -l /tmp/frak
 -rw-r- 2 root shadow 1652 Jun  4 22:05 /tmp/frak

Ah, indeed.
It fails for me though (Ubuntu 12.04, linux 3.2.0, ext4):

$ ln /etc/shadow /tmp/frak
ln: failed to create hard link `/tmp/frak' = `/etc/shadow': Operation not
permitted

but I can see it succeed on older linux.

Still $PGDATA with its rwx-- permissions is insulated from
any such hard-linking from outsiders, whereas a soft link can point
anywhere.

Best regards,
-- 
 Daniel Vérité
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org 


-- 
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 fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII


From: Tom Lane t...@sss.pgh.pa.us

Noah Misch n...@leadboat.com writes:

... I think
MauMau's original bind_textdomain_codeset() proposal was on the right 
track.


It might well be.  My objection was to the proposal for back-patching it
when we have little idea of the possible side-effects.  I would be fine
with handling that as a 9.4-only patch (preferably with the usual review
process).



Still, libc is certainly the source of the vast majority of
potentially-translated messages that we might be passing through to users,
so fixing it would be a step forward.



We are using 9.1/9.2 and 9.2 is probably dominant, so I would be relieved 
with either of the following choices:


1. Take the approach that doesn't use bind_textdomain_codeset(libc) (i.e. 
the second version of errno_str.patch) for 9.4 and older releases.


2. Use bind_textdomain_codeset(libc) (i.e. take strerror_codeset.patch) 
for 9.4, and take the non-bind_textdomain_codeset approach for older 
releases.



Regards
MauMau



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

Re: [HACKERS] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII


From: Peter Eisentraut pete...@gmx.net

Does anyone know why the PostgreSQL-supplied part of the error message
does not get messed up?


That is because bind_textdomain_codeset() is called for postgres.mo in 
src/backend/utils/mb/mbutils.c, specifying the database encoding as the 
second argument.  This is done at session start.


Regards
MauMau



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

Re: [HACKERS] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

On 9/6/13 9:40 AM, MauMau wrote:
 $ psql -d postgres -c SELECT * FROM a
 ... This outputs, in Japanese, a message meaning could not open file
 base/xxx/yyy: ???.
 
 The problem is that strerror() returns ???, which hides the cause of
 the trouble.
 
 The cause is that gettext() called by strerror() tries to convert UTF-8
 messages obtained from libc.mo to ASCII.  This is because postgres calls
 setlocale(LC_CTYPE, C) when it connects to the database.

Does anyone know why the PostgreSQL-supplied part of the error message
does not get messed up?


-- 
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 fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

On 9/9/13 4:42 PM, MauMau wrote:
 We are using 9.1/9.2 and 9.2 is probably dominant, so I would be
 relieved with either of the following choices:
 
 1. Take the approach that doesn't use bind_textdomain_codeset(libc)
 (i.e. the second version of errno_str.patch) for 9.4 and older releases.
 
 2. Use bind_textdomain_codeset(libc) (i.e. take
 strerror_codeset.patch) for 9.4, and take the
 non-bind_textdomain_codeset approach for older releases.

I think we are not going to backpatch any of this.  There is a clear
workaround: fix your locale settings.



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

[HACKERS] memory usage of pg_upgrade

2013-09-09 Thread Jeff Janes

pg_upgrade reserves 5 times MAXPGPATH, or 5120 characters, for the
tablespace name of every object (table, toast table, index) in the
database being upgraded.  This adds up pretty quickly when there is a
very large number of objects.  It could be changed to char* to a
separately allocated name that takes only as much space it needs.  But
maybe it would be better to point into os_info.old_tablespaces or
something like that, as surely there are not going to be one
independent file space per object.


typedef struct
{
 ...
chartablespace[MAXPGPATH];
} RelInfo;

The struct FileNameMap has 4 more .

Since there seems to be some interest in improving the scalability of
pg_upgrade, this is one of the things to consider fixing.  What is the
best way to do it?

Cheers,

Jeff


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

Re: [HACKERS] Next CFM?

2013-09-09 Thread David Fetter

On Mon, Sep 02, 2013 at 12:13:56PM -0700, David Fetter wrote:
 On Mon, Sep 02, 2013 at 12:00:02PM -0500, Josh Berkus wrote:
  Hackers,
  
  We need a Commit Fest manager for the September CF.  I'm not going
  to do it; this month is a heavy travel month for me (3 conferences
  and a wedding).
  
  For help, here's the Commitfest Checklist Mike and I assembled:
  
  https://wiki.postgresql.org/wiki/CommitFest_Checklist
  
  Mind you, Peter E. seems to be getting patches organized ... are you
  CFM for this one, Peter?
 
 If Peter won't, I will.

Peter, are you taking this one?

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate


-- 
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 fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII


From: Peter Eisentraut pete...@gmx.net

On 9/9/13 4:42 PM, MauMau wrote:
1. Take the approach that doesn't use bind_textdomain_codeset(libc)

(i.e. the second version of errno_str.patch) for 9.4 and older releases.

2. Use bind_textdomain_codeset(libc) (i.e. take
strerror_codeset.patch) for 9.4, and take the
non-bind_textdomain_codeset approach for older releases.


I think we are not going to backpatch any of this.  There is a clear
workaround: fix your locale settings.


No, it's a hard workaround to take:

1. Recreate the database with LC_CTYPE = ja_JP.UTF-8.  This changes various 
behaviors such as ORDER BY, index scan, and the performance of LIKE clause. 
This is almost impossible.


2. Change lc_messages in postgresql.conf to 'C'.  This is OK for me as I can 
read/write English to some extent (though poor).  But English is difficult 
for some (or many?) Japanese.


So I hesitate to ask the users to do so.

Regards
MauMau





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

Re: [HACKERS] New statistics for WAL buffer dirty writes

On 9/6/13 11:32 PM, Satoshi Nagayasu wrote:
 The revised patch for wal buffer statistics is attached.
 A test script is also attached. Please take a look.

You have duplicate OIDs.  Run the script duplicate_oids to find them.



-- 
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] New statistics for WAL buffer dirty writes

2013-09-09 Thread Peter Geoghegan

On Mon, Sep 9, 2013 at 2:43 PM, Peter Eisentraut pete...@gmx.net wrote:
 You have duplicate OIDs.  Run the script duplicate_oids to find them.

Are you considering picking up the script that Andrew wrote to
automate that as part of the build? I wonder why that didn't end up
going anywhere.


-- 
Peter Geoghegan


-- 
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] Re: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

On 9/9/13 2:57 PM, Noah Misch wrote:
 Actually, GNU libiconv's iconv() decides that //translit is unimplementable
 for some of the characters in that file, and it fails the conversion.  GNU
 libc's iconv(), on the other hand, emits the question marks.

That can't be right, because the examples I produced earlier (which
produced question marks) were produced on OS X with GNU libiconv.



-- 
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] lcr v5 - introduction of InvalidCommandId

Robert Haas robertmh...@gmail.com writes:
 On Thu, Sep 5, 2013 at 3:23 PM, Andres Freund and...@2ndquadrant.com wrote:
 So, a very slightly updated patch attached.

 Committed.

Hmm ... shouldn't this patch adjust the error messages in
CommandCounterIncrement?  We just took away one possible command.
It's pretty nitpicky, especially since many utility commands do
more than one CommandCounterIncrement, but still ...

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] lcr v5 - introduction of InvalidCommandId

Andres Freund and...@2ndquadrant.com writes:
 On 2013-09-09 18:43:51 -0400, Tom Lane wrote:
 Hmm ... shouldn't this patch adjust the error messages in
 CommandCounterIncrement?

 Hm. You're talking about cannot have more than 2^32-2 commands in a
 transaction? If so, the patch and the commit seem to have adjusted that?

Oh!  That's what I get for going on memory instead of re-reading the
commit.  Sorry, never mind the noise.

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] Protocol forced to V2 in low-memory conditions?

2013-09-09 Thread Maciek Sakrejda

One of our customers seems to be running into exactly the issue
hypothesized about by Tom here:

http://www.postgresql.org/message-id/8040.1314403...@sss.pgh.pa.us

That is, the server is in low-memory conditions, and the client
occasionally issues an error saying it can't complete a certain action due
to the protocol version:

PG::UnableToSend: function requires at least protocol version 3.0: SELECT
...

The server is 9.2.4, and the client is the Ruby pg gem using a 9.2.4 libpq.

Was the possibility of an inadvertent protocol downgrade addressed as part
of that patch? I read through the thread, but it wasn't entirely clear.

Thanks,
Maciek

Re: [HACKERS] memory usage of pg_upgrade

2013-09-09 Thread Andrew Dunstan



On 09/09/2013 06:20 PM, Jeff Janes wrote:

pg_upgrade reserves 5 times MAXPGPATH, or 5120 characters, for the
tablespace name of every object (table, toast table, index) in the
database being upgraded.  This adds up pretty quickly when there is a
very large number of objects.  It could be changed to char* to a
separately allocated name that takes only as much space it needs.  But
maybe it would be better to point into os_info.old_tablespaces or
something like that, as surely there are not going to be one
independent file space per object.


typedef struct
{
  ...
 chartablespace[MAXPGPATH];
} RelInfo;

The struct FileNameMap has 4 more .

Since there seems to be some interest in improving the scalability of
pg_upgrade, this is one of the things to consider fixing.  What is the
best way to do it?



Send in a patch :-)

We recently ripped out some uses of statically sized strings in the 
parallel code and replaced them with pointers to palloc'ed strings. So 
there is good precedent for this. See 
https://github.com/postgres/postgres/commit/910d3a458c15c1b4cc518ba480be2f712f42f179


In the case of tablespaces, I should have thought you could keep a hash 
table of the names and just store an entry id in the table structure. 
But that's just my speculation without actually looking at the code, so 
don't take my word for it :-)


cheers

andrew



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

Re: [HACKERS] lcr v5 - introduction of InvalidCommandId

2013-09-09 Thread Andres Freund

On 2013-09-09 18:43:51 -0400, Tom Lane wrote:
 Robert Haas robertmh...@gmail.com writes:
  On Thu, Sep 5, 2013 at 3:23 PM, Andres Freund and...@2ndquadrant.com 
  wrote:
  So, a very slightly updated patch attached.
 
  Committed.
 
 Hmm ... shouldn't this patch adjust the error messages in
 CommandCounterIncrement?  We just took away one possible command.
 It's pretty nitpicky, especially since many utility commands do
 more than one CommandCounterIncrement, but still ...

Hm. You're talking about cannot have more than 2^32-2 commands in a
transaction? If so, the patch and the commit seem to have adjusted that?

Greetings,

Andres Freund

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


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

Re: [HACKERS] memory usage of pg_upgrade

On Mon, Sep  9, 2013 at 06:39:39PM -0400, Andrew Dunstan wrote:
 
 On 09/09/2013 06:20 PM, Jeff Janes wrote:
 pg_upgrade reserves 5 times MAXPGPATH, or 5120 characters, for the
 tablespace name of every object (table, toast table, index) in the
 database being upgraded.  This adds up pretty quickly when there is a
 very large number of objects.  It could be changed to char* to a
 separately allocated name that takes only as much space it needs.  But
 maybe it would be better to point into os_info.old_tablespaces or
 something like that, as surely there are not going to be one
 independent file space per object.
 
 
 typedef struct
 {
   ...
  chartablespace[MAXPGPATH];
 } RelInfo;
 
 The struct FileNameMap has 4 more .
 
 Since there seems to be some interest in improving the scalability of
 pg_upgrade, this is one of the things to consider fixing.  What is the
 best way to do it?
 
 
 Send in a patch :-)
 
 We recently ripped out some uses of statically sized strings in the
 parallel code and replaced them with pointers to palloc'ed strings.
 So there is good precedent for this. See 
 https://github.com/postgres/postgres/commit/910d3a458c15c1b4cc518ba480be2f712f42f179
 
 In the case of tablespaces, I should have thought you could keep a
 hash table of the names and just store an entry id in the table
 structure. But that's just my speculation without actually looking
 at the code, so don't take my word for it :-)

Yes, please feel free to improve the code.  I improved pg_upgrade CPU
usage for a lerge number of objects, but never thought to look at memory
usage.  It would be a big win to just palloc/pfree the memory, rather
than allocate tones of memory.  If you don't get to it, I will in a few
weeks.

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

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


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

Re: [HACKERS] ECPG FETCH readahead

You need to update the dblink regression tests.




-- 
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] New statistics for WAL buffer dirty writes

On Mon, 2013-09-09 at 14:51 -0700, Peter Geoghegan wrote:
 On Mon, Sep 9, 2013 at 2:43 PM, Peter Eisentraut pete...@gmx.net wrote:
  You have duplicate OIDs.  Run the script duplicate_oids to find them.
 
 Are you considering picking up the script that Andrew wrote to
 automate that as part of the build? I wonder why that didn't end up
 going anywhere.

It is automated.  Andrew's rewrite is still worth considering, and I had
planned to do that, but it doesn't provide any functionality we don't
already have.




-- 
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] psql: small patch to correct filename formatting error in '\s FILE' output

On Tue, Jan 22, 2013 at 07:30:59PM -0500, Tom Lane wrote:
Ian Lawrence Barwick barw...@gmail.com writes:
Related email from the archives on this subject:
http://www.postgresql.org/message-id/37ed240d0611200645l5b70c8ddw5fb735e0d35a7...@mail.gmail.com

I agree with the opinion stated there that \cd with no argument really
ought to do what cd with no argument usually does on the platform.
So if we're going to fix \cd to print the resulting current directory,
wouldn't it work to just set dir to . rather than / for Windows?

Does commit 0725065b just need to be reverted, or is an additional
patch required to remove the prefixed working directory from \s output?

Offhand it looked like reverting the commit would be enough, but I
didn't look hard to see if there had been any subsequent related
changes. [ pokes around... ] Well, at least there are still no other
uses of pset.dirname.

I still see that weird behavior in git head:

pgdevel=# \s history.txt
Wrote history to file ./history.txt.
pgdevel=# \s /tmp/history.txt
Wrote history to file .//tmp/history.txt.
pgdevel=# \cd /tmp
pgdevel=# \s /tmp/history.txt
Wrote history to file /tmp//tmp/history.txt.

Should I revert the suggested patch?

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

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

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

Re: [HACKERS] New statistics for WAL buffer dirty writes

2013-09-09 Thread Peter Geoghegan

On Mon, Sep 9, 2013 at 6:05 PM, Peter Eisentraut pete...@gmx.net wrote:
 It is automated.

Oh, yeah. I see that the maintainer-check target does that. I should
probably get into the habit of using targets other than
check/installcheck, as you recently demonstrated.

-- 
Peter Geoghegan


-- 
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: Set effective_cache_size to greater of .conf value, shared_buffers

On Thu, Sep  5, 2013 at 09:02:27PM -0700, Josh Berkus wrote:
 On 09/05/2013 03:30 PM, Merlin Moncure wrote:
 
  Standard advice we've given in the past is 25% shared buffers, 75%
  effective_cache_size.  Which would make EFS *3X* shared_buffers, not 4X.
   Maybe we're changing the conventional calculation, but I thought I'd
  point that out.
  
  This was debated upthread.
 
 Actually, no, it wasn't.  Tom threw out a suggestion that we use 4X for
 historical reasons.  That's all, there was no discussion.
 
 So, my point stands: our historical advice has been to set EFS to 75% of
 RAM.  Maybe we're changing that advice, but if so, let's change it.
 Otherwise 3X makes more sense.

So, what do we want the effective_cache_size default to be?  3x or 4x?
We clearly state:

If you have a dedicated database server with 1GB or more of RAM,
a reasonable starting value for shared_buffers is 25% of the
memory in your system.  There are some workloads where even

If we make the default 4x, that means that people using the above
suggestion would be setting their effective_cache_size to 100% of RAM? 
If we go with 4x, which I believe was the majority opinion, what shall
we answer to someone who asks about this contradiction?

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

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


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

Re: [HACKERS] Re: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

2013-09-09 Thread Noah Misch

On Mon, Sep 09, 2013 at 05:49:38PM -0400, Peter Eisentraut wrote:
 On 9/9/13 2:57 PM, Noah Misch wrote:
  Actually, GNU libiconv's iconv() decides that //translit is unimplementable
  for some of the characters in that file, and it fails the conversion.  GNU
  libc's iconv(), on the other hand, emits the question marks.
 
 That can't be right, because the examples I produced earlier (which
 produced question marks) were produced on OS X with GNU libiconv.

Hmm.  I get the good behavior (decline to transliterate Japanese) with these
iconv --version strings:

iconv (GNU libiconv 1.11) [/usr/bin/iconv on Mac OS X 10.7]
iconv (GNU libiconv 1.14) [recently-updated fink]
iconv (GNU libiconv 1.14) [recently-updated Cygwin]

I also saw that on OpenBSD and NetBSD, though I'm not in an immediate position
to check the libiconv versions there.  I get the bad behavior (question
marks) on these:

iconv (GNU libc) 2.12 [Centos 6.4]
iconv (GNU libc) 2.3.4 [CentOS 4.4]
iconv (Ubuntu EGLIBC 2.15-0ubuntu10.4) 2.15 [Ubuntu 12.04]
iconv (GNU libc) 2.5 [Ubuntu 7.04]

That sure looked like GNU libc vs. GNU libiconv, but I guess I'm missing some
other factor.  What is your GNU libiconv version that emits question marks?

Thanks,
nm

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com


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

Re: [HACKERS] Re: Privileges for INFORMATION_SCHEMA.SCHEMATA (was Re: [DOCS] Small clarification in 34.41. schemata)

On Sat, 2013-09-07 at 14:01 -0400, Bruce Momjian wrote:
   pg_has_role(n.nspowner, 'USAGE')
   OR has_schema_privilege(n.oid, 'CREATE, USAGE')
   
   As things stand, a non-superuser won't see public, pg_catalog,
   nor even information_schema itself in this view, which seems a
   tad silly.
  
  I agree it would make sense to change this.
 
 Is this the patch you want applied?  The docs are fine? 

I have committed it with a documentation update.



-- 
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: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII

2013-09-09 Thread Noah Misch

On Tue, Sep 10, 2013 at 05:42:06AM +0900, MauMau wrote:
 From: Tom Lane t...@sss.pgh.pa.us
 Noah Misch n...@leadboat.com writes:
 ... I think
 MauMau's original bind_textdomain_codeset() proposal was on the right 
 track.

 It might well be.  My objection was to the proposal for back-patching it
 when we have little idea of the possible side-effects.

Agreed.

 We are using 9.1/9.2 and 9.2 is probably dominant, so I would be relieved 
 with either of the following choices:

 1. Take the approach that doesn't use bind_textdomain_codeset(libc) 
 (i.e. the second version of errno_str.patch) for 9.4 and older releases.

 2. Use bind_textdomain_codeset(libc) (i.e. take strerror_codeset.patch) 
 for 9.4, and take the non-bind_textdomain_codeset approach for older  
 releases.

I like (2), at least at a high level.  The concept of errno_str.patch is safe
enough to back-patch.  One can verify that it only changes behavior when
strerror() returns NULL, an empty string, or something that begins with '?'.
I can't see resenting the change when that has happened.

Note that you can work around the problem today by linking PostgreSQL with a
better iconv() implementation.

Question-mark-damaged messages are not limited to strerror().  A combination
like lc_messages=ja_JP, encoding=LATIN1, lc_ctype=en_US will produce question
marks for PG and libc messages even with the bind_textdomain_codeset(libc)
change.  Is it worth doing anything about that?  That one looks self-inflicted
in comparison to the lc_messages=ja_JP, encoding=UTF8, lc_ctype=C case.

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com


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

Re: [HACKERS] Protocol forced to V2 in low-memory conditions?