[HACKERS] Proposal - Support for 'OR REPLACE' in 'CREATE TRIGGER' command
Hi, This is a proposal to implement support for 'OR REPLACE' in 'CREATE TRIGGER' command as an optional clause. The aim of this proposal is to present a way to support replace trigger functionality. Adding the optional clause 'OR REPLACE' in CREATE TRIGGER syntax gives users the option of redefining the trigger in single command. And such support will be helpful for customers when they are migrating from other RDBMs, which already has this support in-built. As PostgreSQL supports 'OR REPLACE' in various commands such as CREATE VIEW, CREATE FUNCTION, CREATE LANGUAGE, CREATE RULE, implementing this support to 'CREATE TRIGGER' command also will be useful for different migration/schema upgrade scripts. [Brief Design] Brief design of 'OR REPLACE' support in 'CREATE TRIGGER' implementation is presented below: - Updating parser rules to support 'OR REPLACE' as an optional clause in the syntax of 'CREATE TRIGGER' command. - Support to update existing trigger's system table(pg_trigger) record with new definition. - Support to update existing trigger's dependencies(which the trigger depends on)based on new trigger definition. - Following the existing system behaviour of other commands that supports 'OR REPLACE' functionality, any dependent object should not be dropped while replacing the trigger. Only identified dependent object is the constraint object, created for constraint trigger. When the constraint trigger is replaced by regular trigger, to avoid having constraint object still tagged to regular trigger, a new condition will be enforced to restrict replacing constraint trigger with regular trigger and vice-versa. Any attempt to replace between regular and constraint triggers displays new error messages. I can start working on a patch based on the above. Thanks & Regards, Vaishnavi
Re: [HACKERS] [PERFORM] encouraging index-only scans
On Mon, Sep 9, 2013 at 9:33 PM, Jeff Janes wrote: > On Sun, Sep 8, 2013 at 8:49 PM, Amit Kapila wrote: >> On Mon, Sep 9, 2013 at 2:35 AM, Jeff Janes 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. Yes, that is right, but how about freezing of tuples, delaying that also might not be good. Also it might not be good for all kind of scenarios that always foreground operations take care of cleaning up dead rows leaving very less chance for Vacuum (only when it has to scan all pages aka anti-wraparound vacuum) to cleanup dead rows. If we are sure that Vacuum skipping pages in a database where there are less non-HOT updates and deletes (or mostly inserts and Hot-updates) is not having any significant impact, then it can be quite useful for IOS. With Regards, Amit Kapila. 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] psql: small patch to correct filename formatting error in '\s FILE' output
2013/9/10 Bruce Momjian : > On Tue, Jan 22, 2013 at 07:30:59PM -0500, Tom Lane wrote: >> Ian Lawrence Barwick 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? IIRC the patch was never applied, the reversion candidate is the existing commit 0725065b. (Sorry for not following up earlier, this one dropped off my radar). Regards Ian Barwick -- Sent 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?
Maciek Sakrejda writes: > 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 > 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. No, a quick look at report_fork_failure_to_client shows it still always sends V2 protocol. We fixed some of the lesser issues discussed in that thread, but I don't think we ever agreed how to deal with this one. I've been thinking of late that it might be time to retire libpq's support for V2 protocol (other than in the specific context of the first error message received while trying to make a connection). If we did that, we'd remove the code path that thinks it should downgrade to V2 protocol, and thus fix this problem by removing code not adding more. However, that doesn't sound like a back-patchable solution, and also it remains unclear whether non-libpq clients such as JDBC have an issue with this. 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: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
On Tue, Sep 10, 2013 at 05:42:06AM +0900, MauMau wrote:
> From: "Tom Lane"
>> Noah Misch 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] 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
Re: [HACKERS] Re: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
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] 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 http://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
On Mon, Sep 9, 2013 at 6:05 PM, Peter Eisentraut 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] 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 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 http://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
On Mon, 2013-09-09 at 14:51 -0700, Peter Geoghegan wrote: > On Mon, Sep 9, 2013 at 2:43 PM, Peter Eisentraut 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] 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] 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
>
>
> 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 http://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] lcr v5 - introduction of InvalidCommandId
On 2013-09-09 18:43:51 -0400, Tom Lane wrote: > Robert Haas writes: > > On Thu, Sep 5, 2013 at 3:23 PM, Andres Freund > > 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 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
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
[HACKERS] Protocol forced to V2 in low-memory conditions?
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] lcr v5 - introduction of InvalidCommandId
Andres Freund 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
Re: [HACKERS] lcr v5 - introduction of InvalidCommandId
Robert Haas writes: > On Thu, Sep 5, 2013 at 3:23 PM, Andres Freund 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] 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] New statistics for WAL buffer dirty writes
On Mon, Sep 9, 2013 at 2:43 PM, Peter Eisentraut 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] 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] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
From: "Peter Eisentraut"
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] Next CFM?
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 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
[HACKERS] memory usage of pg_upgrade
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] [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
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
From: "Peter Eisentraut" 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
From: "Tom Lane"
Noah Misch 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] file_fdw target file ownership
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] file_fdw target file ownership
Andres Freund 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] lcr v5 - introduction of InvalidCommandId
On Thu, Sep 5, 2013 at 3:23 PM, Andres Freund 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
On 2013-09-09 21:41:00 +0200, Daniel Vérité wrote: >Tom Lane writes: > > > Andres Freund 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] file_fdw target file ownership
Tom Lane writes: > Andres Freund 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] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
Noah Misch 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] 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 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 http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
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] Custom Plan node
* 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
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 http://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
Peter Eisentraut 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] strange IS NULL behaviour
On Sat, Sep 7, 2013 at 10:59 AM, Bruce Momjian 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] Custom Plan node
Robert Haas 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] [rfc] overhauling pgstat.stat
On 9 Září 2013, 18:50, Robert Haas wrote: > On Sat, Sep 7, 2013 at 6:57 PM, Tomas Vondra 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
On Fri, Sep 6, 2013 at 7:03 PM, Tom Lane wrote: > Robert Haas 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] Strange hanging bug in a simple milter
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] [rfc] overhauling pgstat.stat
On Sat, Sep 7, 2013 at 6:57 PM, Tomas Vondra 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] [PERFORM] encouraging index-only scans
On Sun, Sep 8, 2013 at 8:49 PM, Amit Kapila wrote: > On Mon, Sep 9, 2013 at 2:35 AM, Jeff Janes 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 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] overflow checks optimized away
On Sat, Sep 7, 2013 at 6:55 PM, Greg Stark 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 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] Strange hanging bug in a simple milter
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] Strange hanging bug in a simple milter
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=) at
fe-secure.c:872
#4 pq_lockingcallback (mode=, 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=) at
fe-connect.c:516
#16 0x004007b6 in test_connect (threadid=2) at threaded-connect.c:25
#17 0x0040086e in run_thread (arg=0x600e04 ) 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] improve Chinese locale performance
On Mon, Sep 9, 2013 at 5:22 AM, Quan Zongliang 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
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 doi
Re: [HACKERS] Strange hanging bug in a simple milter
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] Hstore: Query speedups with Gin index
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 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 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 >> wrote: >> >>> 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 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] [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 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] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
Peter Eisentraut 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] 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] Strange hanging bug in a simple milter
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 /lib/
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
[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] Strange hanging bug in a simple milter
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
Re: [HACKERS] [bug fix] strerror() returns ??? in a UTF-8/C database with LC_MESSAGES=non-ASCII
From: "MauMau" 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] improve Chinese locale performance
On 09/06/2013 01:02 AM, Robert Haas wrote: On Wed, Sep 4, 2013 at 11:02 PM, Quan Zongliang wrote: I think of a new idea. Add a compare method column to pg_collation. Every collation has its own compare function or null. When function varstr_cmp is called, if specified collation has compare function, call it instead of strcoll(). I think we're going to need to have two kinds of collations: OS-derived collations (which get all of their smarts from the OS), and PG-internal collations (which use PG-aware code for everything). Which I suspect is a bit more involved than what you're imagining, but mixing and matching doesn't seem likely to end well. However, what you're proposing might serve as a useful demonstration of how much performance there is to be gained here. Understood. I just try to speed up text compare, not redesign locale. Do you have a plan to do this? Thank you. Quan Zongliang -- Sent 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 8:19), Tomas Vondra wrote: On 8.9.2013 23:04, Jeff Janes wrote: On Tue, Sep 3, 2013 at 10:09 PM, Satoshi Nagayasu wrote: Hi, (2013/09/04 13:07), Alvaro Herrera wrote: Satoshi Nagayasu wrote: As you may know, this file could be handreds of MB in size, because pgstat.stat holds all access statistics in each database, and it needs to read/write an entire pgstat.stat frequently. As a result, pgstat.stat often generates massive I/O operation, particularly when having a large number of tables in the database. We already changed it: commit 187492b6c2e8cafc5b39063ca3b67846e8155d24 Author: Alvaro Herrera Date: Mon Feb 18 17:56:08 2013 -0300 Split pgstat file in smaller pieces Thanks for the comments. I forgot to mention that. Yes, we have already split single pgstat.stat file into several pieces. However, we still need to read/write large amount of statistics data when we have a large number of tables in single database or multiple databases being accessed. Right? Do you have a test case for measuring this? I vaguely remember from when I was testing the split patch, that I thought that after that improvement the load that was left was so low that there was little point in optimizing it further. This is actually a pretty good point. Creating a synthetic test case is quite simple - just create 1.000.000 tables in a single database, but I'm wondering if it's actually realistic. Do we have a real-world example where the current "one stat file per db" is not enough? I have several assumptions for that. - Single shared database contains thousands of customers. - Each customer has hundreds of tables and indexes. - Customers are separated by schemas (namespaces) in single database. - Application server uses connection pooling for performance reason. - Workload (locality in the table access) can not be predicted. Looks reasonable? The reason why I worked on the split patch is that our application is slightly crazy and creates a lot of tables (+ indexes) on the fly, and as we have up to a thousand databases on each host, we often ended up with a huge stat file. Splitting the stat file improved that considerably, although that's partially because we have the stats on a tmpfs, so I/O is not a problem, and the CPU overhead is negligible thanks to splitting the stats per database. I agree that splitting a single large database into several pieces, like thousands of tiny databases, could be an option in some cases. However, what I intend here is eliminating those limitations on database design. In fact, when considering connection pooling, splitting a database is not a good idea, because AFAIK, many connection poolers manage connections per database. So, I'd like to support 100k tables in single database. Any comments? Regards, But AFAIK there are operating systems where creating a filesystem in RAM is not that simple - e.g. Windows. In such cases even a moderate number of objects may be a significant issue I/O-wise. But then again, I can't really think of reasonable a system creating that many objects in a single database (except for e.g. a shared database using schemas instead of databases). Tomas -- Satoshi Nagayasu Uptime Technologies, LLC. http://www.uptime.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
