Re: [HACKERS] INSERT...ON DUPLICATE KEY LOCK FOR UPDATE
On Sat, Nov 23, 2013 at 11:52 PM, Peter Geoghegan p...@heroku.com wrote: pg@hamster:~/pgbench-tools/tests$ pgbench -f upsert.sql -n -c 50 -T 20 I can get it to deadlock (and especially to throw unique constraint violations) like crazy. I'm sorry, this test-case is an earlier one that is actually entirely invalid for the purpose stated (though my concerns stated above remain - I just didn't think the multi-unique-index case had been exercised enough, and so did this at the last minute). Please omit it from your consideration. I think I have been working too late... -- 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
[HACKERS] review - pg_stat_statements
Hello all I did check of pg_stat_statements_ext_text.v2.2013_11_16.patch, that introduce a external storage for query text I got a compilation warning: bash-4.1$ make all gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -I. -I. -I../../src/include -D_GNU_SOURCE -I/usr/include/libxml2 -c -o pg_stat_statements.o pg_stat_statements.c pg_stat_statements.c: In function ‘query_text_retrieve’: pg_stat_statements.c:1477:3: warning: format ‘%lu’ expects type ‘long unsigned int’, but argument 4 has type ‘off_t’ pg_stat_statements.c: In function ‘garbage_collect_query_texts’: pg_stat_statements.c:1796:2: warning: format ‘%ld’ expects type ‘long int’, but argument 3 has type ‘off_t’ pg_stat_statements.c:1796:2: warning: format ‘%ld’ expects type ‘long int’, but argument 4 has type ‘off_t’ gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -fpic -shared -o pg_stat_statements.so pg_stat_statements.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/usr/local/pgsql/lib',--enable-new-dtags my environment: bash-4.1$ uname -a Linux nemesis 2.6.35.14-106.fc14.i686 #1 SMP Wed Nov 23 13:57:33 UTC 2011 i686 i686 i386 GNU/Linux bash-4.1$ gcc --version gcc (GCC) 4.5.1 20100924 (Red Hat 4.5.1-4) Next, usual queries, and my replies: * This patch works as was expected and proposed * The code is well commented and respects PostgreSQL coding standards * I didn't find any problems when I tested it * I tried do some basic benchmark, and I didn't see any negative on performance related to implemented feature * We would to this patch - a idea of proposal is right - a shared memory can be used better than storage of possibly extra long queries Peter does some warning about performance in feature proposal http://www.postgresql.org/message-id/cam3swzryynfwxi3r3edakwboytaf1_pwgjxtayddnsbjafd...@mail.gmail.com. The risks are not negligible mainly on environments with slow IO and high varied load. I have no idea, how to eliminate this risks with possibilities that we have on extensions (maybe divide global stat file to smaller per database - it should not be solution for some configuration too). A enough solution (for this release) should better explanation in documentation. For future releases, we should to think about significant refactoring - moving to core and using asynchronous stats (and stats per database) or using specialized background worker Peter mentioned a race conditions under high load. It is fixed? summary: * compilation warning * undocumented new risks of waiting to locks when new query is identified and processed Regards Pavel
Re: [HACKERS] review - pg_stat_statements
On Sun, Nov 24, 2013 at 1:18 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I got a compilation warning: I'll look into it. * I tried do some basic benchmark, and I didn't see any negative on performance related to implemented feature You're never going to see any performance impact with something like a regular pgbench workload. That's because you'll only ever write query texts out when a shared lock is held. With only extreme exceptions (a garbage collection), exceptions will never be exercised by what you're doing, you will only block whole new entries from being added - existing stat counters are just protected by a shared lock + spinlock. You might consider rigging pg_stat_statements to create a new hash value randomly (consisting of a random integer for a queryid hash) maybe 1% - 10% of the time. That would simulate the cache being filled quickly, I guess, where that shared lock will conflict with the exclusive lock, potentially showing where what I've done can hurt. I recommend in the interest of fairness not letting it get so far as to put the cache under continual pressure. Now, this isn't that important a case, because having a larger hash table makes exclusive locking/new entries less necessary, and this work enables people to have larger hash tables. But it does matter to some degree. * We would to this patch - a idea of proposal is right - a shared memory can be used better than storage of possibly extra long queries Right. Plus the amount of storage used is pretty modest, even compared to previous shared memory use. Each entry doesn't need to have track_activity_query_size bytes of storage, only what it needs (though garbage can accrue, which is a concern). Peter does some warning about performance in feature proposal http://www.postgresql.org/message-id/cam3swzryynfwxi3r3edakwboytaf1_pwgjxtayddnsbjafd...@mail.gmail.com I'm mostly talking about the cost of the shared lock for *reading* here, when pg_stat_statements() is called. If that was happening at the same time as I/O for reading the query texts from file, that could be a concern. Not enough of a concern to worry about humans doing this, I think, but maybe for scripts. Maybe the trick would be to copy the stats into shared memory, and only then read texts from disk (with the shared lock released). We'd have to be concerned about a garbage collection occurring, so we'd need to re-acquire a shared lock again (plus a spinlock) to check that didn't happen (which is generally very unlikely). Only when we know it didn't happen could we display costs to the user, and that might mean keeping all the query texts in memory, and that might matter in extreme situations. The reason I haven't done all this already is because it's far from clear that it's worth the trouble. Peter mentioned a race conditions under high load. It is fixed? Yes, the version you mentioned had the fix. -- 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
[HACKERS] Connect from background worker thread to database
Hello everyone. I am new to writing extensions to PostgreSQL. And I am not familiar with the C language (My professional languages: Ruby, Golang, Java). But still want to write an extension for PostgreSQL. After articles of Michael Paquier about background workers in PostgreSQL, I realized what I can create what I always want - web interface for this database. This interface will provide some info about database (like in couchbase: http://docs.couchbase.com/couchbase-manual-2.0/images/web-console-cluster-overview.png ) and can be used as REST API. Right now this server should work as JSON API and I will build on top of this API web interface on some JS framework. My initiative right now is working as web server just with some hello string. But I don't understand how should I connect to database from threads in web server. All source you can find here: https://github.com/le0pard/pg_web This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. Thanks in advance, Alexey
Re: [HACKERS] Connect from background worker thread to database
Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Report exit code from external recovery commands properly
On Wed, 2013-11-13 at 19:14 -0800, Peter Geoghegan wrote: I think this is a very good idea, but you should go a bit further: document the special relationship restore_command has to special return codes. How about this? diff --git a/doc/src/sgml/backup.sgml b/doc/src/sgml/backup.sgml index 1712974..995933c 100644 --- a/doc/src/sgml/backup.sgml +++ b/doc/src/sgml/backup.sgml @@ -1084,9 +1084,17 @@ titleRecovering Using a Continuous Archive Backup/title para It is important that the command return nonzero exit status on failure. -The command emphasiswill/ be called requesting files that are not present -in the archive; it must return nonzero when so asked. This is not an -error condition. Not all of the requested files will be WAL segment +The command emphasiswill/ be called requesting files that are not +present in the archive; it must return nonzero when so asked. This is not +an error condition. An exception is that if the command was terminated by +a signal (other than systemitemSIGTERM/systemitem, which is used as +part of a database server shutdown) or an error by the shell (such as +command not found), then recovery will abort and the server will not start +up. + /para + + para +Not all of the requested files will be WAL segment files; you should also expect requests for files with a suffix of literal.backup/ or literal.history/. Also be aware that the base name of the literal%p/ path will be different from diff --git a/doc/src/sgml/recovery-config.sgml b/doc/src/sgml/recovery-config.sgml index c0c543e..9d80256 100644 --- a/doc/src/sgml/recovery-config.sgml +++ b/doc/src/sgml/recovery-config.sgml @@ -70,6 +70,10 @@ titleArchive Recovery Settings/title restore_command = 'cp /mnt/server/archivedir/%f %p' restore_command = 'copy C:\\server\\archivedir\\%f %p' # Windows /programlisting +An exception is that if the command was terminated by a signal (other +than systemitemSIGTERM/systemitem, which is used as part of a +database server shutdown) or an error by the shell (such as command +not found), then recovery will abort and the server will not start up. /para /listitem /varlistentry @@ -106,8 +110,10 @@ titleArchive Recovery Settings/title command. /para para -If the command returns a non-zero exit status then a WARNING log -message will be written. +If the command returns a nonzero exit status then a warning log +message will be written. An exception is that if the command was +terminated by a signal or an error by the shell (such as command not +found), a fatal error will be raised. /para /listitem /varlistentry @@ -127,10 +133,11 @@ titleArchive Recovery Settings/title last valid restart point, like in xref linkend=archive-cleanup-command. /para para -If the command returns a non-zero exit status then a WARNING log +If the command returns a nonzero exit status then a warning log message will be written and the database will proceed to start up anyway. An exception is that if the command was terminated by a -signal, the database will not proceed with startup. +signal or an error by the shell (such as command not found), the +database will not proceed with startup. /para /listitem /varlistentry -- 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[2]: [HACKERS] Connect from background worker thread to database
If I remove comment from BackgroundWorkerInitializeConnection https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L179 and comment InitPostgres https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L98 , I have the same errors in log: 2013-11-24 13:35:24 UTC ERROR: stack depth limit exceeded 2013-11-24 13:35:24 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 13:35:24 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; 2013-11-24 13:35:25 UTC LOG: worker process: pg_web (PID 1957) exited with exit code 1 2013-11-24 13:35:26 UTC LOG: starting background worker process pg_web 2013-11-24 13:35:26 UTC INFO: Start web server on port 8080 If I change InitPostgres to BackgroundWorkerInitializeConnection and leave BackgroundWorkerInitializeConnection in bgw_main, I will have this error: 2013-11-24 13:39:58 UTC ERROR: invalid processing mode in background worker 2013-11-24 13:39:58 UTC LOG: worker process: pg_web (PID 2719) exited with exit code 1 2013-11-24 13:39:59 UTC LOG: starting background worker process pg_web Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de: Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund
Re: [HACKERS] pre-commit triggers
On 19 November 2013 16:46, Robert Haas robertmh...@gmail.com wrote: On Tue, Nov 19, 2013 at 12:45 AM, Noah Misch n...@leadboat.com wrote: On Fri, Nov 15, 2013 at 01:01:48PM -0500, Andrew Dunstan wrote: The triggers don't fire if there is no real XID, so only actual data changes should cause the trigger to fire. What's the advantage of this provision? Without it, an individual trigger could make the same check and drop out quickly. A trigger not wanting it can't so easily work around its presence, though. Heretofore, skipping XID assignment has been an implementation detail that improves performance without otherwise calling user attention to itself. This provision would make the decision to acquire an XID (where optional) affect application behavior. Yeah, I agree that that's an ugly wart. If we want a pre-commit trigger that's only called for transactions that write data, we at least need to name it appropriately. It looks to me that this idea is horribly physical and seems likely to be badly misused. I don't see any way to use these that won't be quite ugly. There is no trigger descriptor, so no way of writing a constraint sensibly, since you'll need to make a constraint check for every commit by every user, not just ones that touch the data you care about. And security goes straight out the window, so these can't be used in normal application development. Plus we can already do this with RegisterXactCallback() as Alvaro points out - so if its a hack we're after, then we already have it, no patch required. So this patch doesn't give us anything genuinely useful for application developers, nor does it give us the thing that Josh is looking for.. The main reason is to enforce arbitrary assertions which need enforcement at the end of a transaction and not before. I like the goal, but this is not the solution. Josh also points out... This is the sort of thing the SQL committee covered in ASSERTIONS, but of course they never specified any method for implementation. I think we should be thinking harder about how to implement ASSERTIONs, possibly calling them ASSERTION TRIGGERs not pre-commit write event triggers. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SQL assertions prototype
On 15 November 2013 03:30, Peter Eisentraut pete...@gmx.net wrote: A constraint trigger performs the actual checking. Good, that is consistent with other constraints. This is not a performance feature. It's for things like, this table should have at most 10 rows, or all the values in this table must be bigger than all the values in that other table. It's a bit esoteric, but it comes up again and again. While I accept it may never perform well, it needs to perform reasonably well. The key use cases for this are * enforcing one and only one relationships * enforcing quantified relationships like we do in XML: minoccurs and maxoccurs * enforcing only one sub-type across multiple sub-type tables etc So we'd need to get access to the changed rows, rather than re-executing a huge SQL command that re-checks every row of the table. That last point will make it unusable for sensible amounts of data. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] preserving forensic information when we freeze
On 21 November 2013 16:51, Andres Freund and...@2ndquadrant.com wrote: Definitely separate yes. And I agree, it's partially moot if Heikki's patch gets in, but I am not sure it will make it into 9.4. There seems to be quite some work left. I'd prefer to do all 3 patches in one release. Don't mind which one. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Completing PL support for Event Triggers
Peter Eisentraut pete...@gmx.net writes: I have committed the PL/Tcl part. I'll work on the PL/Perl part next. Thanks! I believe we're still waiting on something from you for PL/Python. Yes I still need to figure that one out. -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Traffic jams in fn_extra
On 19 November 2013 23:08, Paul Ramsey pram...@cleverelephant.ca wrote: On the solution, I wasn't suggesting another void* slot, but rather a slot that holds a hash table, so that an arbitrary number of things can be stuffed in. Overkill, really, since in 99.9% of times only one thing would be in there, and in the other 0.1% of times two things. In our own GenericCacheCollection, we just statically allocate 16 slots. Why do you need to do this dance with fn_extra? It's possible to allocate a hash table in a Transaction-lifetime memory context on first call into a function then cache things there. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review - pg_stat_statements
2013/11/24 Peter Geoghegan p...@heroku.com On Sun, Nov 24, 2013 at 1:18 AM, Pavel Stehule pavel.steh...@gmail.com wrote: I got a compilation warning: I'll look into it. * I tried do some basic benchmark, and I didn't see any negative on performance related to implemented feature You're never going to see any performance impact with something like a regular pgbench workload. That's because you'll only ever write query texts out when a shared lock is held. With only extreme exceptions (a garbage collection), exceptions will never be exercised by what you're doing, you will only block whole new entries from being added - existing stat counters are just protected by a shared lock + spinlock. You might consider rigging pg_stat_statements to create a new hash value randomly (consisting of a random integer for a queryid hash) maybe 1% - 10% of the time. That would simulate the cache being filled quickly, I guess, where that shared lock will conflict with the exclusive lock, potentially showing where what I've done can hurt. I recommend in the interest of fairness not letting it get so far as to put the cache under continual pressure. Now, this isn't that important a case, because having a larger hash table makes exclusive locking/new entries less necessary, and this work enables people to have larger hash tables. But it does matter to some degree. how is a size of hash table related to exclusive locks in pgss_store? I don't afraid about performance of pg_stat_statements(). I afraid a performance of creating new entry and appending to file. I didn't expected some slowdown - a benchmark was only verification against some hidden cost. Is not difficult to write synthetic benchmark that will find some differences, but a result of this benchmark will be useless probably due minimal relation to reality. * We would to this patch - a idea of proposal is right - a shared memory can be used better than storage of possibly extra long queries Right. Plus the amount of storage used is pretty modest, even compared to previous shared memory use. Each entry doesn't need to have track_activity_query_size bytes of storage, only what it needs (though garbage can accrue, which is a concern). Peter does some warning about performance in feature proposal http://www.postgresql.org/message-id/cam3swzryynfwxi3r3edakwboytaf1_pwgjxtayddnsbjafd...@mail.gmail.com I'm mostly talking about the cost of the shared lock for *reading* here, when pg_stat_statements() is called. If that was happening at the same time as I/O for reading the query texts from file, that could be a concern. Not enough of a concern to worry about humans doing this, I think, but maybe for scripts. Maybe the trick would be to copy the stats into shared memory, and only then read texts from disk (with the shared lock released). We'd have to be concerned about a garbage collection occurring, so we'd need to re-acquire a shared lock again (plus a spinlock) to check that didn't happen (which is generally very unlikely). Only when we know it didn't happen could we display costs to the user, and that might mean keeping all the query texts in memory, and that might matter in extreme situations. The reason I haven't done all this already is because it's far from clear that it's worth the trouble. I don't expect so pg_stat_statements is on application critical path, so I prefer mostly simple design Peter mentioned a race conditions under high load. It is fixed? Yes, the version you mentioned had the fix. ok Regards Pavel -- Peter Geoghegan
Re: [HACKERS] PL/Python: domain over array support
On Sat, Nov 23, 2013 at 11:09:53AM -0200, Rodolfo Campero wrote: 2013/11/22 Marko Kreen mark...@gmail.com One more thing - please update Python 3 regtests too. The attached patch (version 3) includes the expected results for Python 3 (file plpython_types_3.out). Thanks. Looks good now. Tagging as ready for committer. -- marko -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Server is not getting started with log level as debug5 on master after commit 3147ac
Amit Kapila amit.kapil...@gmail.com writes: I think that still this kind of problems can be there at other places in code. I checked few places and suspecting secure_read() can also have similar problem: case SSL_ERROR_SSL: ereport(COMMERROR, (errcode(ERRCODE_PROTOCOL_VIOLATION), errmsg(SSL error: %s, SSLerrmessage(; /* fall through */ Note that what it falls through to is errno = ECONNRESET, so the caller will see a well-defined value of errno after this. Even without the ereport call, I'd think that was necessary because SSL_get_error isn't documented to return a meaningful value of errno except in the SSL_ERROR_SYSCALL case. In general it is responsibility of caller to take care of errno handling, but I am not sure it is taken care well at all places in code and the chances of such problems were less earlier because there was less chance that ereport would reset errno, but now it will definitely do so. [ shrug... ] To my mind, this is a *good* thing, because now we will more easily find and fix any callers that are broken. They were broken anyway, it's just that the circumstances might've been difficult to reproduce. As an example consider the possibility that ereport might previously have stomped on errno only with unusual log_line_prefix settings. 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] autovacuum_work_mem
On 19 October 2013 19:22, Peter Geoghegan p...@heroku.com wrote: I won't repeat the rationale for the patch here. I can't see the problem that this patch is trying to solve. I'm having trouble understanding when I would use this. VACUUM uses 6 bytes per dead tuple. And autovacuum regularly removes dead tuples, limiting their numbers. In what circumstances will the memory usage from multiple concurrent VACUUMs become a problem? In those circumstances, reducing autovacuum_work_mem will cause more passes through indexes, dirtying more pages and elongating the problem workload. I agree that multiple concurrent VACUUMs could be a problem but this doesn't solve that, it just makes things worse. Freezing doesn't require any memory at all, so wraparound vacuums won't be controlled by this parameter. Can we re-state what problem actually is here and discuss how to solve it. (The reference [2] didn't provide a detailed explanation of the problem, only the reason why we want a separate parameter). -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de: Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund Fixed by using PQconnectdb from libpq-fe.h. Thanks. -- Alexey Vasiliev
Re: [HACKERS] Sequence Access Method WIP
On 18 November 2013 07:36, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 14.11.2013 22:10, Simon Riggs wrote: Includes test extension which allows sequences without gaps - gapless. I realize this is just for demonstration purposes, but it's worth noting that it doesn't actually guarantee that when you use the sequence to populate a column in the table, the column would not have gaps. Sequences are not transactional, so rollbacks will still produce gaps. The documentation is misleading on that point. Without a strong guarantee, it's a pretty useless extension. True. If I fix that problem, I should change the name to lockup sequences, since only one transaction at a time could use the nextval. Should I change the documentation, or just bin the idea? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sequence Access Method WIP
On 18 November 2013 07:06, Heikki Linnakangas hlinnakan...@vmware.com wrote: On 18.11.2013 13:48, Simon Riggs wrote: On 18 November 2013 07:50, Heikki Linnakangas hlinnakan...@vmware.com wrote: It doesn't go far enough, it's still too *low*-level. The sequence AM implementation shouldn't need to have direct access to the buffer page at all. I don't think the sequence AM should be in control of 'cached'. The caching is done outside the AM. And log_cnt probably should be passed to the _alloc function directly as an argument, ie. the server code asks the AM to allocate N new values in one call. I can't see what the rationale of your arguments is. All index Ams write WAL and control buffer locking etc.. Index AM's are completely responsible for the on-disk structure, while with the proposed API, both the AM and the backend are intimately aware of the on-disk representation. Such a shared responsibility is not a good thing in an API. I would also be fine with going 100% to the index AM direction, and remove all knowledge of the on-disk layout from the backend code and move it into the AMs. Then you could actually implement the discussed store all sequences in a single file change by writing a new sequence AM for it. I think the way to resolve this is to do both of these things, i.e. a two level API 1. Implement SeqAM API at the most generic level. Add a nextval() call as well as alloc() 2. Also implement the proposed changes to alloc() So the SeqAM would implement either nextval() or alloc() but not both global sequences as envisaged for BDR would use a special alloc() call. I don't think that is too much work, but I want to do this just once... Thoughts on exact next steps for implementation please? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Server is not getting started with log level as debug5 on master after commit 3147ac
I wrote: Amit Kapila amit.kapil...@gmail.com writes: In general it is responsibility of caller to take care of errno handling, but I am not sure it is taken care well at all places in code and the chances of such problems were less earlier because there was less chance that ereport would reset errno, but now it will definitely do so. [ shrug... ] To my mind, this is a *good* thing, because now we will more easily find and fix any callers that are broken. They were broken anyway, it's just that the circumstances might've been difficult to reproduce. As an example consider the possibility that ereport might previously have stomped on errno only with unusual log_line_prefix settings. ... and having said that, there *are* bugs here, which have got nothing to do with ereport(). client_read_ended() for instance is not being careful to preserve errno :-( 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] PL/Python: domain over array support
Thank you very much Marko. 2013/11/24 Marko Kreen mark...@gmail.com On Sat, Nov 23, 2013 at 11:09:53AM -0200, Rodolfo Campero wrote: 2013/11/22 Marko Kreen mark...@gmail.com One more thing - please update Python 3 regtests too. The attached patch (version 3) includes the expected results for Python 3 (file plpython_types_3.out). Thanks. Looks good now. Tagging as ready for committer. -- marko -- Rodolfo Campero Anachronics S.R.L. Tel: (54 11) 4899 2088 rodolfo.camp...@anachronics.com http://www.anachronics.com
Re: [HACKERS] [PATCH] SQL assertions prototype
On Sun, Nov 24, 2013 at 11:03 PM, Simon Riggs si...@2ndquadrant.com wrote: So we'd need to get access to the changed rows, rather than re-executing a huge SQL command that re-checks every row of the table. That last point will make it unusable for sensible amounts of data. That sounds very similar to handling incremental maintenance of materialized views, which Kevin is working on. Let's assume that the huge SQL command that re-checks every row of the table is actually a materialized view. In that case, the CREATE ASSERTION trigger would merely need to scan the matview and raise an error if any rows were present. That should be a very quick operation. No need to invent some sort of get access to the changed rows mechanism especially for CREATE ASSERTION. Kind regards, Andrew Tipton -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add CREATE support to event triggers
On Thu, Nov 21, 2013 at 2:36 AM, Christopher Browne cbbro...@gmail.com wrote: b) What's the best design of the SRF output? This patch proposes two columns, object identity and create statement. Is there use for anything else? Class/object OIDs perhaps, schema OIDs for objects types that have it? I don't see any immediate need to that info, but perhaps someone does. Probably an object type is needed as well, to know if it's a table or a domain or a sequence or whatever. I suspect that what will be needed to make it all usable is some sort of structured form. That is in keeping with Robert Haas' discomfort with the normalized form. My minor gripe is that you haven't normalized enough (e.g. - it should be CREATE TABLE foo.foo2 (acolumn foo.bar), capturing the normalization of data types that are referenced). But Robert's quite right that users may want more than just to capture that literally; they may want to modify it, for instance, by shifting to another schema. And it will be no fun at all if you have to construct an SQL parser in order to change it. It's certainly much easier to transform a structured representation into a valid SQL command string than it is to do the inverse. You may be interested in an extension that I'm working on for a client, which provides relation_create, relation_alter, and relation_drop event triggers for 9.3: https://bitbucket.org/malloclabs/pg_schema_triggers I decided to create a composite type for each event, which can be accessed from within the event trigger by calling a special function. For example, the relation_alter event supplies the relation Oid and the old and new pg_class rows. It's easy to then examine the old vs. new rows and determine what has changed. Kind regards, Andrew Tipton -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] session_preload_libraries not in sample config?
session_preload_libraries is not in the sample config file. Is that just an oversight? Regards, Jeff Davis -- Sent 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: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist
2013/11/21 Peter Eisentraut pete...@gmx.net On 11/21/13, 2:35 AM, Pavel Stehule wrote: I am feeling, so almost all people prefer DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ]; Can we live with it? Fine with me. I think it helps if you consider IF EXISTS an attribute of the command, not an attribute of the command parameters. Now we should be aware that this sort of sets a precedent for ALTER TABLE IF EXISTS ... DROP ANYTHING ... and similar composite commands. If might be worth checking other SQL databases. We stole the IF EXISTS from somewhere, I believe. I did some searching: So DROP TRIGGER IF EXISTS is supported by SQL anywhere, MySQL Doesn't support: MS SQL server (conditional drops is by T-SQL IF EXISTS() statement), Oracle, DB2, But significant difference between PostgreSQL and other databases is requirement to specify table in DROP statement. So in SQL anywhere or in MySQL DROP TRIGGER IF EXISTS is fully fault tolerant, there are not possibility to specify table. Note: DROP TRIGGER ON tablename is PostgreSQL feature - no other databases (without PostgreSQL forks) uses this syntax - so we don't need thinking what is in (or what will be) in ANSI standard (or what other databases does). In this moment syntax of DROP TRIGGER is non standard. So if we can adopt design (idea) in SQL anywhere or MySQL, then DROP TRIGGER IF EXISTS should be enough. In our implementation there are two conditions, but we should not to check if target table exists (from statement purpose). So now, +1 for using DROP TRIGGER IF EXISTS name ON tablename without requirement for tablename Regards Pavel
Re: [HACKERS] Traffic jams in fn_extra
Hi Simon, We do the dance because it’s how we always have and don’t know any other way, any better way. :) The usual explanation. Is there any place you can point to that demonstrates your technique? Thanks! P -- Paul Ramsey http://cleverelephant.ca/ http://postgis.net/ On Sunday, November 24, 2013 at 8:21 AM, Simon Riggs wrote: On 19 November 2013 23:08, Paul Ramsey pram...@cleverelephant.ca (mailto:pram...@cleverelephant.ca) wrote: On the solution, I wasn't suggesting another void* slot, but rather a slot that holds a hash table, so that an arbitrary number of things can be stuffed in. Overkill, really, since in 99.9% of times only one thing would be in there, and in the other 0.1% of times two things. In our own GenericCacheCollection, we just statically allocate 16 slots. Why do you need to do this dance with fn_extra? It's possible to allocate a hash table in a Transaction-lifetime memory context on first call into a function then cache things there. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] review: create if not exists
Hello, this patch introduces IF NOT EXISTS clause to CREATE (AGGREGATE | CAST |COLATION |DOMAIN |ROLE |SEQUENCE |SEARCH CONFIGURATION |SEARCH DICTIONARY | SEARCH PARSE | SEARCH TEMPLATE | TYPE). Implemented feature was discussed more times in spring and summer, and this patch was ready for commit in summer. Fabrízio reflected all reviewers comments. I primary checked a consistency of related patch. * all new functionality (syntax) is well documented * there are no any compilation warning related to implemented feature * all new functionality is tested in regress tests * new code fully respects PostgreSQL coding standards * new code is almost mechanical - and enough documented * new functionality has no impact on performance * new functionality doesn't need a changes in pg_dump This patch is ready for commit Regards Pavel Stehule
Re: [HACKERS] review: create if not exists
sorry + * all regress tests passed without error 2013/11/24 Pavel Stehule pavel.steh...@gmail.com Hello, this patch introduces IF NOT EXISTS clause to CREATE (AGGREGATE | CAST |COLATION |DOMAIN |ROLE |SEQUENCE |SEARCH CONFIGURATION |SEARCH DICTIONARY | SEARCH PARSE | SEARCH TEMPLATE | TYPE). Implemented feature was discussed more times in spring and summer, and this patch was ready for commit in summer. Fabrízio reflected all reviewers comments. I primary checked a consistency of related patch. * all new functionality (syntax) is well documented * there are no any compilation warning related to implemented feature * all new functionality is tested in regress tests * new code fully respects PostgreSQL coding standards * new code is almost mechanical - and enough documented * new functionality has no impact on performance * new functionality doesn't need a changes in pg_dump This patch is ready for commit Regards Pavel Stehule
Re: [HACKERS] Traffic jams in fn_extra
On 24 November 2013 16:02, Paul Ramsey pram...@cleverelephant.ca wrote: We do the dance because it’s how we always have and don’t know any other way, any better way. :) The usual explanation. Is there any place you can point to that demonstrates your technique? src/backend/utils/mmgr/README You can create memory contexts as children of other contexts, so for example you might create PostGIS Cache Context as a sub-context of TopTransactionContext. So it can be created dynamically as needed and will automatically go away at end of xact. Or you could use CurTransactionContext if you want to do things at subtransaction level. This is all used very heavily within Postgres itself, including the various caches in different parts of the code. Obviously, if you start cacheing too much then people will claim that PostGIS is leaking memory, so it depends how far you go. But then you might alleviate that with a postgis.session_cache parameter to acknowledge and allow control. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Errors on missing pg_subtrans/ files with 9.3
coredumper worked like a charm. Useful tool, that is... although as a bit of advice, I'd try not to run it on Postgres if your various memory settings are tweaked towards production use -- the core dump that was captured on my server weighed in at 16 GB. Anyways, I've attached both the log entries that caused the error as well as a backtrace using `bt full`. Cheers, hope it helps. Logfile: Nov 23 14:38:32 dev postgres[23810]: [4-1] user=dev,db=dev ERROR: could not access status of transaction 13514992 Nov 23 14:38:32 dev postgres[23810]: [4-2] user=dev,db=dev DETAIL: Could not open file pg_subtrans/00CE: Success. Nov 23 14:38:32 dev postgres[23810]: [4-3] user=dev,db=dev CONTEXT: SQL statement SELECT 1 FROM ONLY dev.collection_batches x WHERE id OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x Nov 23 14:38:32 dev postgres[23810]: [4-4] user=dev,db=dev STATEMENT: update provider_listings set deactivated_at=$1, effective_provider_id=$2, first_collection_date=$3, last_modified=$4, latest_collection_batch_id=$5, latest_collection_date=$6, latest_notfound_collection_batch_id=$7, latest_notfound_date=$8, latest_raw_listing_id=$9, latest_raw_listing_hash=$10, latitude=$11, lock_version=$12, longitude=$13, process_state=$14, provider_id=$15, source_listing_key=$16, source_provider_key=$17, status=$18 where id=$19 and lock_version=$20 Backtrace: [jay@dev local]% gdb /usr/pgsql-9.3/bin/postgres core.23810.2013-11-23.14:35:28.EST GNU gdb (GDB) CentOS (7.0.1-45.el5.centos) Copyright (C) 2009 Free Software Foundation, Inc. License GPLv3+: GNU GPL version 3 or later http://gnu.org/licenses/gpl.html This is free software: you are free to change and redistribute it. There is NO WARRANTY, to the extent permitted by law. Type show copying and show warranty for details. This GDB was configured as x86_64-redhat-linux-gnu. For bug reporting instructions, please see: http://www.gnu.org/software/gdb/bugs/... Reading symbols from /usr/pgsql-9.3/bin/postgres...Reading symbols from /usr/lib/debug/usr/pgsql-9.3/bin/postgres.debug...done. done. [New Thread 23810] warning: .dynamic section for /usr/lib64/libxslt.so.1 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /usr/lib64/libxml2.so.2 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libpam.so.0 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libssl.so.6 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libcrypto.so.6 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /usr/lib64/libgssapi_krb5.so.2 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libcrypt.so.1 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libdl.so.2 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libm.so.6 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /usr/lib64/libldap-2.3.so.0 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libc.so.6 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /usr/lib64/libkrb5.so.3 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libcom_err.so.2 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libz.so.1 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libaudit.so.0 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /usr/lib64/libk5crypto.so.3 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /usr/lib64/libkrb5support.so.0 is not at the expected address warning: difference appears to be caused by prelink, adjusting expectations warning: .dynamic section for /lib64/libkeyutils.so.1 is not at the expected address warning: difference appears to be caused by prelink,
Re: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
On Mon, Nov 25, 2013 at 2:10 AM, Alexey Vasiliev leopard...@inbox.ru wrote: Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de: Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund Fixed by using PQconnectdb from libpq-fe.h. Thanks. You should not need an extra PQconnectdb to connect to a database using a background worker for your case as far as I understood. By using that you are requiring a connection to database without using the internal infrastructure in place, meaning that your bgworker is not connected to the database server from the inside but from the outside, like a normal client would do. Aren't to trying to have a background worker connected to a database when it is initialized with BgWorkerStart_PostmasterStart? Bgworkers using this start-up mode are not eligible to initialize database connections. Please use either BgWorkerStart_ConsistentState or BgWorkerStart_RecoveryFinished and BackgroundWorkerInitializeConnection will allow a connection to database without any extra work. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Traffic jams in fn_extra
Simon Riggs si...@2ndquadrant.com writes: On 24 November 2013 16:02, Paul Ramsey pram...@cleverelephant.ca wrote: We do the dance because its how we always have and dont know any other way, any better way. :) The usual explanation. Is there any place you can point to that demonstrates your technique? src/backend/utils/mmgr/README You can create memory contexts as children of other contexts, so for example you might create PostGIS Cache Context as a sub-context of TopTransactionContext. So it can be created dynamically as needed and will automatically go away at end of xact. The real question of course is whether transaction-level caching is appropriate for what they're storing. If they want only statement-level caching then using fn_extra is often the right thing. Also note that having the cache go away is the easy part. The hard part is knowing whether you've created it yet in the current transaction, and finding it if you have. The usual method is to keep a static variable pointing to it, and plugging into the transaction cleanup callback mechanism with a routine that'll reset the pointer to NULL at transaction end. For examples, look for callers of RegisterXactCallback(). 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] Building on S390
On Sat, 2013-11-23 at 11:49 -0500, Tom Lane wrote: My point is, they compile the *backend* as position-independent code. The backend is not a shared library. Maybe it is in Postgres-XC? But at least this makes their build process significantly different, so it's doubtful that this is a PG-proper issue. Note that that's not an unreasonable decision in itself, if it's done pursuant to some distro policy that daemons should run with ASLR enabled. Right. False alarm. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Use MAP_HUGETLB where supported (v3)
At 2013-11-21 22:14:35 +0100, and...@2ndquadrant.com wrote: I'd certainly want a setting that errors out if it cannot get the memory using hugetables. OK, then the current try/on/off settings are fine. I'm better today, so I'll read the patch Heikki posted and see what more needs to be done there. -- Abhijit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Show lossy heap block info in EXPLAIN ANALYZE for bitmap heap scan
On 1 November 2013 16:32, Etsuro Fujita fujita.ets...@lab.ntt.co.jp wrote: From: Fujii Masao [mailto:masao.fu...@gmail.com] This is what I'm looking for! This feature is really useful for tuning work_mem when using full text search with pg_trgm. I'm not sure if it's good idea to show the number of the fetches because it seems difficult to tune work_mem from that number. How can we calculate how much to increase work_mem to avoid lossy bitmap from the number of the fetches in EXPLAIN output? We can calculate that from the following equation in tbm_create(): nbuckets = maxbytes / (MAXALIGN(sizeof(HASHELEMENT)) + MAXALIGN(sizeof(PagetableEntry)) + sizeof(Pointer) + sizeof(Pointer)), where maxbytes is the size of memory used for the hashtable in a TIDBitmap, designated by work_mem, and nbuckets is the estimated number of hashtable entries we can have within maxbytes. From this, the size of work_mem within which we can have every hashtable entry as an exact bitmap is calculated as follows: work_mem = (the number of exact pages + the number of lossy pages) * (MAXALIGN(sizeof(HASHELEMENT)) + MAXALIGN(sizeof(PagetableEntry)) + sizeof(Pointer) + sizeof(Pointer)) / (1024 * 1024). I am yet to give more thought on the above formula (particularly exact_pages + lossy_pages), but I was also wondering if the user would indeed be able to figure out the above way to estimate the memory, or the explain itself should show the estimated memory required for the bitmap. For hash joins we do show the memory taken by the hash table in show_hash_info(). We can show the memory requirement in addition to the number of exact/lossy pages. I'll show you an example. The following is the result for work_mem = 1MB: postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.02; QUERY PLAN -- -- Bitmap Heap Scan on demo (cost=2716.54..92075.46 rows=105766 width=34) (actual time=24.907..1119.961 rows=100047 loops=1) Recheck Cond: ((col2 = 0.01::double precision) AND (col2 = 0.02::double precision)) Rows Removed by Index Recheck: 5484114 Heap Blocks: exact=11975 lossy=46388 - Bitmap Index Scan on demo_idx (cost=0.00..2690.09 rows=105766 width=0) (actual time=22.821..22.821 rows=100047 loops=1) Index Cond: ((col2 = 0.01::double precision) AND (col2 = 0.02::double precision)) Total runtime: 1129.334 ms (7 rows) So, by setting work_mem to work_mem = (11975 + 46388) * (MAXALIGN(sizeof(HASHELEMENT)) + MAXALIGN(sizeof(PagetableEntry)) + sizeof(Pointer) + sizeof(Pointer)) / (1024 * 1024), which is about 5MB, we have the following (Note that no lossy heap pages!): postgres=# EXPLAIN ANALYZE SELECT * FROM demo WHERE col2 between 0.01 and 0.02; QUERY PLAN Bitmap Heap Scan on demo (cost=2716.54..92075.46 rows=105766 width=34) (actual time=42.981..120.252 rows=1 00047 loops=1) Recheck Cond: ((col2 = 0.01::double precision) AND (col2 = 0.02::double precision)) Heap Blocks: exact=58363 - Bitmap Index Scan on demo_idx (cost=0.00..2690.09 rows=105766 width=0) (actual time=26.023..26.023 r ows=100047 loops=1) Index Cond: ((col2 = 0.01::double precision) AND (col2 = 0.02::double precision)) Total runtime: 129.304 ms (6 rows) BTW, as the EXPLAIN ANALYZE output, the number of exact/lossy heap pages would be fine with me. Anyway, could you add the patch into next CF? Done. Thanks, Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO: Split out pg_resetxlog output into pre- and post-sections
On Sat, Nov 9, 2013, Amit Kapila wrote
On Fri, Nov 8, 2013 at 10:37 AM, Rajeev rastogi
rajeev.rast...@huawei.commailto:rajeev.rast...@huawei.com wrote:
On Fri, 08 November 2013 09:47
On Tue, Nov 5, 2013 at 3:20 PM, Rajeev rastogi
rajeev.rast...@huawei.commailto:rajeev.rast...@huawei.com wrote:
On execution of pg_resetxlog using the option -n
Please provide your opinion or expectation out of this patch.
Your approach in patch seems to be inline with Todo item. On a
quick glance, I observed few things which can make your patch better:
1. The purpose was to print pg_control values in one section and
any other reset values in different section, so in that
regard, should we display below in new section, as here
newXlogSegNo is not directly from pg_control.
PrintControlValues()
{
..
XLogFileName(fname, ControlFile.checkPointCopy.ThisTimeLineID,
newXlogSegNo);
printf(_(First log segment after reset:%s\n),
fname);
}
Yes we can print newXlogSegNo.
I think then your documentation also need updates.
I have added to documentation.
One more thing, I think as per this patch few parameters will be
displayed twice once in pg_control values .. section and once in
Values to be used after reset:, so by doing this I guess you want to
make it easier for user to refer both pg_control's original/guessed
value and new value after reset. Here I wonder if someone wants to
refer to original values, can't he directly use pg_controldata? Anyone
else have thoughts about how can we display values which can make
current situation better for user.
Aim of this patch is to:
1. Without this patch, if I give some parameter using -l switch and also
provide -n switch
Then it will display this values as TimeLineID of latest checkpoint, which
is not
Really the truth.
1. So we can print both actual values and values to be used after reset in
different section
So that is extra clear.
Usage of pg_controldata may not be preferable in this case because:
1. User will have to use two separate executable, which can be actually
achieved by only pg_resetxlog.
2. pg_controldata prints many other additional parameters, in which user may
not be interested.
I have attached the updated patch.
Please let me know if it is OK or anyone else have any other idea.
Note: Replied this mail on 11th Nov also but for some reason didn't appear in
community mail chain.
Thanks and Regards,
Kumar Rajeev Rastogi
pg_resetxlogsectionV2.patch
Description: pg_resetxlogsectionV2.patch
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Server is not getting started with log level as debug5 on master after commit 3147ac
On Sun, Nov 24, 2013 at 10:22 PM, Tom Lane t...@sss.pgh.pa.us wrote: Amit Kapila amit.kapil...@gmail.com writes: I think that still this kind of problems can be there at other places in code. I checked few places and suspecting secure_read() can also have similar problem: case SSL_ERROR_SSL: ereport(COMMERROR, (errcode(ERRCODE_PROTOCOL_VIOLATION), errmsg(SSL error: %s, SSLerrmessage(; /* fall through */ Note that what it falls through to is errno = ECONNRESET, so the caller will see a well-defined value of errno after this. Even without the ereport call, I'd think that was necessary because SSL_get_error isn't documented to return a meaningful value of errno except in the SSL_ERROR_SYSCALL case. Yes, that is right. Idea is that there can be more occurrences which we might need to handle and the same seems to be taken care in your latest commit. 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] COPY table FROM STDIN doesn't show count tag
On 22 November 2013 16:14, Rajeev rastogi rajeev.rast...@huawei.com wrote: On 21 November 2013, Amit Khandekar amit.khande...@enterprisedb.com wrote: Ok. we will then first fix the \COPY TO issue where it does not revert back the overriden psql output file handle. Once this is solved, fix for both COPY FROM and COPY TO, like how it is done in the patch earlier ( copydefectV2.patch). I analyzed the solution to fix \COPY TO issue but unfortunately I observed that *do_copy* is already resetting the value of *cur_cmd_source and queryFout* but before that itself result status is printed. So we’ll have to reset the value before result status is being displayed. So as other alternative solutions, I have two approaches: 1. We can store current file destination *queryFout *in some local variable and pass the same to *SendQuery* function as a parameter. Same can be used to reset the value of queryFout after return from ProcessResult From all other callers of SendQuery , we can pass NULL value for this new parameter. 2. We can add new structure member variable FILE *prevQueryFout in structure “struct _*psqlSettings”, *which hold the value of queryFout before being changed in do_copy. And then same can be used to reset value in SendQuery or ProcessResult. I think approach #2 is fine. Rather than prevQueryFout, I suggest defining a separate FILE * handle for COPY. I don't see any other client-side command that uses its own file pointer for reading and writing, like how COPY does. And this handle has nothing to do with pset stdin and stdout. So we can have this special _psqlSettings-copystream specifically for COPY. Both handleCopyIn() and handleCopyOut() will be passed pset.copystream. In do_copy(), instead of overriding pset.queryFout, we can set pset.copystream to copystream, or to stdin/stdout if copystream is NULL. Please let me know which approach is OK or if any other approach suggested. Based on feedback I shall prepare the new patch and share the same. Thanks and Regards, Kumar Rajeev Rastogi
[HACKERS] Re[2]: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
Понедельник, 25 ноября 2013, 8:31 +09:00 от Michael Paquier michael.paqu...@gmail.com: On Mon, Nov 25, 2013 at 2:10 AM, Alexey Vasiliev leopard...@inbox.ru wrote: Воскресенье, 24 ноября 2013, 14:06 +01:00 от Andres Freund and...@anarazel.de : Hi, On 2013-11-24 16:27:06 +0400, Олексій Васильєв wrote: This is part where I try to connect to database: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L92-L132 , but SPI functions give error in log (it is commented): 2013-11-24 02:57:43 UTC ERROR: stack depth limit exceeded 2013-11-24 02:57:43 UTC HINT: Increase the configuration parameter max_stack_depth (currently 2048kB), after ensuring the platform's stack depth limit is adequate. 2013-11-24 02:57:43 UTC CONTEXT: SQL statement SELECT COUNT(*) FROM pg_class; Because I doing something in wrong way. I will appreciate for any help: where I doing wrong, link to the article how to do it, just a tip, pull request - anything. Google search and PostgreSQL sources reading so far helped me to this point. At the very least you're calling InitPostgres() instead of BackgroundWorkerInitializeConnection() which you have commented out. And the latter should only be called once in every worker. Greetings, Andres Freund Fixed by using PQconnectdb from libpq-fe.h. Thanks. You should not need an extra PQconnectdb to connect to a database using a background worker for your case as far as I understood. By using that you are requiring a connection to database without using the internal infrastructure in place, meaning that your bgworker is not connected to the database server from the inside but from the outside, like a normal client would do. Aren't to trying to have a background worker connected to a database when it is initialized with BgWorkerStart_PostmasterStart? Bgworkers using this start-up mode are not eligible to initialize database connections. Please use either BgWorkerStart_ConsistentState or BgWorkerStart_RecoveryFinished and BackgroundWorkerInitializeConnection will allow a connection to database without any extra work. -- Michael Thanks, I just try this. This work if I working with database in loop inside bgw_main function. But if I create threads inside bgw_main and try to connect to database - I have errors stack depth limit exceeded . The code: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to database https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 - http_event_handler function execute in threads, because it handle http requests. And this code not work. BTW, I need connect to database depend from request url, so execute BackgroundWorkerInitializeConnection at the beginning not enough. Thanks again. -- Alexey Vasiliev
Re: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
On Mon, Nov 25, 2013 at 1:02 PM, Alexey Vasiliev leopard...@inbox.ru wrote: Thanks, I just try this. This work if I working with database in loop inside bgw_main function. But if I create threads inside bgw_main and try to connect to database - I have errors stack depth limit exceeded . The code: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to database https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 - http_event_handler function execute in threads, because it handle http requests. And this code not work. BTW, I need connect to database depend from request url, so execute BackgroundWorkerInitializeConnection at the beginning not enough. There is a design problem with your application when trying to create threads with mg_start in order to grab events. Note that I am not familiar with mongoose, but with bgworkers you cannot simply create new threads that would be able to connect to server concurrently. A model that would be more suited with bgworkers would be something like: - Handle event messages by for example opening a port or monitoring the events on a single bgworker launched by bgw_main, with for example a message queue model (possible with 9.3). Connection to database would be done with a single connection, managed within the loop of bgw_main. - Create a new bgworker dynamically each time a new event comes in (possible only with 9.4~). The top work would be done by a central bgworker initialized with server, which would treat events and kick new slave workers when necessary. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UNION ALL on partitioned tables won't use indices.
On Sat, Nov 23, 2013 at 01:35:32PM -0500, Tom Lane wrote: Noah Misch n...@leadboat.com writes: I'm unclear on the key ideas behind distinguishing em_is_child members from ordinary EC members. src/backend/optimizer/README says These members are *not* full-fledged members of the EquivalenceClass and do not affect the class's overall properties at all. Is that an optimization to avoid futile planner work, or is it necessary for correctness? If the latter, what sort of query might give wrong answers if an EquivalenceMember were incorrectly added as em_is_child = false? See commit dd4134ea, which added that text. IIRC, the key point is that among real EC members, there will never be duplicates: a Var x.y for instance cannot appear in two different ECs, because we'd have merged the two ECs into one instead. However, this property does *not* hold for child EC members. The problem is that two completely unrelated UNION ALL child subselects might have, say, constant 1 in their tlists. This should not lead to concluding that we can merge ECs that mention their UNION ALL parent variables. Thanks for the pointer; I found things clearer after reviewing the ECs from the test case queries from commits dd4134ea and 57664ed2. I've not looked at these patches yet, but if they're touching equivclass.c at all, I'd guess that it's wrong. Only PATCH-1 touched equivclass.c. My gut feeling after two minutes' thought is that the best fix is for expand_inherited_rtentry to notice when the parent table is already an appendrel member, and enlarge that appendrel instead of making a new one. (This depends on the assumption that pull_up_simple_union_all always happens first, but that seems OK.) I'm not sure if that concept exactly corresponds to either PATCH-3 or PATCH-4, but that's the way I'd think about it. PATCH-4 does approximately that. I agree that's the right general direction. However, adding a qual to one of the inheritance queries once again defeated MergeAppend with the patches for approaches (2) and (3). That's an independent limitation, see is_safe_append_member: * Also, the child can't have any WHERE quals because there's no place to * put them in an appendrel. (This is a bit annoying...) It'd be nice to fix that, but it's not going to be easy, and it should be a separate patch IMO. It's pretty much unrelated to the question at hand here. After further study, I agree. It would still be good to understand why that test case crashed PATCH-1, then ensure that the other patches don't have a similar lurking bug. An alternative to extending our ability to pull up UNION ALL subqueries, having perhaps broader applicability, would be to push down the possibly-useful sort order to subqueries we can't pull up. We'd sometimes have two levels of MergeAppend, but that could still handily beat the explicit-sort plan. In any case, it is indeed a separate topic. For the sake of the archives, you can get such plans today by manually adding the ORDER BY to the relevant UNION ALL branch: EXPLAIN (ANALYZE) (SELECT oid, * FROM pg_proc WHERE protransform = 0 ORDER BY oid) UNION ALL SELECT oid, * FROM pg_proc ORDER BY 1 LIMIT 5; QUERY PLAN Limit (cost=0.57..1.31 rows=5 width=544) (actual time=0.102..0.155 rows=5 loops=1) - Merge Append (cost=0.57..748.25 rows=5084 width=544) (actual time=0.095..0.130 rows=5 loops=1) Sort Key: pg_proc_1.oid - Index Scan using pg_proc_oid_index on pg_proc pg_proc_1 (cost=0.28..332.83 rows=2538 width=544) (actual time=0.058..0.069 rows=3 loops=1) Filter: ((protransform)::oid = 0::oid) - Index Scan using pg_proc_oid_index on pg_proc (cost=0.28..326.47 rows=2546 width=544) (actual time=0.029..0.036 rows=3 loops=1) -- 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] [PoC] pgstattuple2: block sampling to reduce physical read
On 16/09/13 16:20, Satoshi Nagayasu wrote: Thanks for checking. Fixed to eliminate SnapshotNow. Looking forward to get a new patch, incorporating the comments, that are already given in the following mails: 1. Jaime Casanova: The name pgstattuple2, doesn't convince me... maybe you can use pgstattuple() if you use a second argument (percentage of the sample) to overload the function. (http://www.postgresql.org/message-id/5265ad16.3090...@catalyst.net.nz) The comment related to having an argument, to mention the sampling number, is also given by Greg Smith: There should be an input parameter to the function for how much sampling to do (http://www.postgresql.org/message-id/51ee62d4.7020...@2ndquadrant.com) 2. Yourself: I think it could be improved by sorting sample block numbers before physical block reads in order to eliminate random access on the disk. (http://www.postgresql.org/message-id/525779c5.2020...@uptime.jp) for which, Mark Kirkwood , has given a rough patch. Regards, Firoz EV
Re: [HACKERS] Heavily modified big table bloat even in auto vacuum is running
On Fri, Nov 22, 2013 at 12:12 PM, Haribabu kommi haribabu.ko...@huawei.com wrote: On 19 November 2013 10:33 Amit Kapila wrote: If I understood correctly, then your patch's main intention is to correct the estimate of dead tuples, so that it can lead to Vacuum cleaning the table/index which otherwise is not happening as per configuration value (autovacuum_vacuum_threshold) in some of the cases, also it is not reducing the complete bloat (Unpatched - 1532MB ~Patched - 1474MB), as the main reason of bloat is extra space in index which can be reclaimed by reindex operation. So if above is correct then this patch has 3 advantages: a. Extra Vacuum on table/index due to better estimation of dead tuples. b. Space reclaim due to this extra vacuum c. may be some performance advantage as it will avoid the delay in cleaning dead tuples I think better way to test the patch is to see how much benefit is there due to above (a and b points) advantages. Different values of autovacuum_vacuum_threshold can be used to test. The performance effect of the patch is not much visible as I think the analyze on the table estimates the number of dead tuples of the table with some estimation. Yes, that seems to be the reason why you are not seeing any performance benefit, but still I think this is useful optimization to do, as analyze updates both the livetuples and dead tuples and similarly vacuum should also update both the counts. Do you see any reason why Vacuum should only update live tuples and not deadtuples? Because of this reason not much performance improvement is not visible as the missed dead tuple calculation in vacuum is covered by the analyze. Yeah, so might be we can check once by configuring analyze_threshold/scalefactor in a way that analyze doesn't get trigger during your test. 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
[HACKERS] Re[2]: [HACKERS] Re[2]: [HACKERS] Connect from background worker thread to database
Понедельник, 25 ноября 2013, 13:31 +09:00 от Michael Paquier michael.paqu...@gmail.com: On Mon, Nov 25, 2013 at 1:02 PM, Alexey Vasiliev leopard...@inbox.ru wrote: Thanks, I just try this. This work if I working with database in loop inside bgw_main function. But if I create threads inside bgw_main and try to connect to database - I have errors stack depth limit exceeded . The code: https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L195 - connect to database https://github.com/le0pard/pg_web/blob/master/src/pg_web.c#L100-L131 - http_event_handler function execute in threads, because it handle http requests. And this code not work. BTW, I need connect to database depend from request url, so execute BackgroundWorkerInitializeConnection at the beginning not enough. There is a design problem with your application when trying to create threads with mg_start in order to grab events. Note that I am not familiar with mongoose, but with bgworkers you cannot simply create new threads that would be able to connect to server concurrently. A model that would be more suited with bgworkers would be something like: - Handle event messages by for example opening a port or monitoring the events on a single bgworker launched by bgw_main, with for example a message queue model (possible with 9.3). Connection to database would be done with a single connection, managed within the loop of bgw_main. - Create a new bgworker dynamically each time a new event comes in (possible only with 9.4~). The top work would be done by a central bgworker initialized with server, which would treat events and kick new slave workers when necessary. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers Thanks. I will look how to do this in the best way by your suggestions. -- Alexey Vasiliev
