Re: [HACKERS] Perl 5.18 breaks pl/perl regression tests?
Tom Lane wrote:
It looks quite a bit like somebody's fixed a line-counting bug inside
Perl, which may mean that we'll have to maintain two expected-output
files or else remove these particular test cases. Which would be
annoying.
Maybe we can set a $SIG{__WARN__} routine instead, which would re-print
the warning appending a \n, to supress the line count.
--
Á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] Perl 5.18 breaks pl/perl regression tests?
On 2013-06-03 06:55, Michael Paquier wrote: Just by having a look at the release notes of Perl, there are still nothing describing changes between 1.6.3 and 1.8.0: http://perldoc.perl.org/index-history.html That page is not updated, it seems. In this list https://metacpan.org/module/RJBS/perl-5.18.0/pod/perldelta.pod is mentioned Line numbers at the end of a string eval are no longer off by one. [perl #114658] -- 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] erroneous restore into pg_catalog schema
On 14.05.2013 15:35, Stephen Frost wrote: * Andres Freund (and...@2ndquadrant.com) wrote: I don't disagree, but how is that relevant for fixing the issue at hand? We still need to fix restores that currently target the wrong schema in a backward compatible manner? On this, I agree w/ Tom that we should put that check back into place- it's really too late to do anything else. In the interest of getting the release out, I've reverted commit a475c603. We'll probably want to do something more elegant in the future, but this will do for now. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Time for beta2 ?
We've almost cleared the open items list, and I'm not aware of any other unfixed issues in 9.3beta1. Could we make a beta2 release soon? There have been a bunch of recovery-related fixes since beta1, it would be nice to get those fixes in the hands of testers. - 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] Vacuum, Freeze and Analyze: the big picture
On Mon, Jun 3, 2013 at 1:20 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Jun 2, 2013 at 2:44 PM, Jeff Janes jeff.ja...@gmail.com wrote: Do we know why anti-wraparound uses so many resources in the first place? The default settings seem to be quite conservative to me, even for a system that has only a single 5400 rpm hdd (and even more so for any real production system that would be used for a many-GB database). I wonder if there is something simple but currently unknown going on which is causing it to damage performance out of all proportion to the resources it ought to be using. I can't rule that out. Personally, I've always attributed it to the fact that it's (a) long and (b) I/O-intensive. But it's not impossible there could also be bugs lurking. It could be related to the OS. I have no evidence for or against, but it's possible that OS write-out routines defeat the careful cost based throttling that PostgreSQL does by periodically dumping a large portion of dirty pages into the write queue at once. That does nasty things to query latencies as evidenced by the work on checkpoint spreading. Regards, Ants Aasma -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt Web: http://www.postgresql-support.de -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Implicit rule created for materialized views
Hi In playing with materialized views, I noticed that they still seem to have an _RETURN rule implicitly created like a regular view. This doesn't seem right to me - is there a reason? viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres psql (9.3beta1) Type help for help. postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class; SELECT 298 postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class = 'ruletest'::regclass; pg_get_ruledef - CREATE RULE _RETURN AS + ON SELECT TO ruletest DO INSTEAD SELECT pg_class.relname, + pg_class.relnamespace, + pg_class.reltype, + pg_class.reloftype,+ pg_class.relowner, + pg_class.relam,+ pg_class.relfilenode, + pg_class.reltablespace,+ pg_class.relpages, + pg_class.reltuples,+ pg_class.relallvisible,+ pg_class.reltoastrelid,+ pg_class.reltoastidxid,+ pg_class.relhasindex, + pg_class.relisshared, + pg_class.relpersistence, + pg_class.relkind, + pg_class.relnatts, + pg_class.relchecks,+ pg_class.relhasoids, + pg_class.relhaspkey, + pg_class.relhasrules, + pg_class.relhastriggers, + pg_class.relhassubclass, + pg_class.relispopulated, + pg_class.relfrozenxid, + pg_class.relminmxid, + pg_class.relacl, + pg_class.reloptions+ FROM pg_class; (1 row) -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture
On 06/02/2013 05:56 AM, Robert Haas wrote: On Sat, Jun 1, 2013 at 1:49 PM, Simon Riggs si...@2ndquadrant.com wrote: On 30 May 2013 19:48, Josh Berkus j...@agliodbs.com wrote: There's currently some great ideas bouncing around about eliminating the overhead associated with FREEZE. However, I wanted to take a step back and take a look at the big picture for VACUUM, FREEZE and ANALYZE. That is a very commendable approach. We should do that more often. The big, big picture is this: 90% of our users need to think about VACUUM/ANALYZE at least 10% of the time and 10% of our users need to think about it almost 90% of the time. When you say stuff like that, you should add speculating from my personal experience. People might get the impression you'd measured this somehow and it could confuse the issue if you try to assemble a high level viewpoint and then add in factoids that are just opinions. We should strive to measure such things. That's considerably better than was the case 5 years ago, when vacuum management was a daily or weekly responsibility for nearly 100% of our users, but it's still not good enough. Our target should be that only those with really unusual setups should have to *ever* think about vacuum and analyze. I think that's where we already are given that 1000s of users have quite small databases. The problem increases with scale. Larger databases have bigger problems and make it easier to notice things are happening. I think you should mention that the evidence for these issues is anecdotal and take careful notes of the backgrounds in which they occurred. Saying things occur in all cases wouldn't be accurate or helpful to their resolution. We should be seeking to contrast this against other databases to see if we are better or worse than other systems. For example, recording the moans of someone who is currently managing a 1 TB database, but yet hasn't ever managed anything else that big is less valuable than a balanced, experienced viewpoint (if such exists). Anyway, I support this approach, just wanted to make sure we do it in sufficient detail to be useful. I agree with all that. I don't have any data either, but I agree that AFAICT it seems to mostly be a problem for large (terabyte-scale) databases, or ones that are dreadfully short of I/O bandwidth. AWS, I'm looking at you. It would be interesting to make a list of what other issues people have seen using PostgreSQL on very large data sets. Complaints I've heard include: 1. Inexplicable failure of the planner to use indexes on very large tables, preferring an obviously-stupid sequential scan. This might be fixed by the latest index-size fudge factor work. I've seen cases on Stack Overflow and elsewhere in which disk merge sorts perform vastly better than in-memory quicksort, so the user benefited from greatly *lowering* work_mem. (b) users making ridiculous settings changes to avoid the problems caused by anti-wraparound vacuums kicking in at inconvenient times and eating up too many resources. Some recent experiences I've had have also bought home to me that vacuum problems are often of the user's own making. My database is slow - This autovacuum thing is using up lots of I/O and CPU, I'll increase this delay setting here - My database is slower - Maybe I didn't solve the autovacuum thing, I'll just turn it off - My database is barely working - I'll whack in some manual VACUUM cron jobs during low load maintenance hours and hope that keeps the worst of the problem away, that's what random forum posts on the Internet say to do. - oh my, why did my DB just do an emergency shutdown? Vacuum being more able to operate in a feedback loop driven by bloat statistics might be quite valuable, but I'm also wondering if there's any remotely feasible way to more usefully alert users when they're having table bloat issues and vacuum isn't coping. Particularly for cases where autovacuum is working but being impaired by locking. -- Craig Ringer 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] Implicit rule created for materialized views
On Mon, Jun 3, 2013 at 1:06 PM, Kevin Grittner kevin.gritt...@enterprisedb.com wrote: Yes, that is currently used for REFRESH, and will be used to drive the incremental maintenance when that is added. Without it, CREATE MATERIALIZED VIEW wouldn't be different from CREATE TABLE AS. OK. A materialized view is pretty much like a view, but with the results materialized. Yeah, I get that, but what is confusing is that this now seems to be a special kind of relation where there is an ON SELECT DO INSTEAD rule which isn't actually executed on SELECTs from the view but at some arbitrary time in the future. On Mon, Jun 3, 2013 at 6:58 AM, Dave Page dp...@pgadmin.org wrote: Hi In playing with materialized views, I noticed that they still seem to have an _RETURN rule implicitly created like a regular view. This doesn't seem right to me - is there a reason? viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres psql (9.3beta1) Type help for help. postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class; SELECT 298 postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class = 'ruletest'::regclass; pg_get_ruledef - CREATE RULE _RETURN AS + ON SELECT TO ruletest DO INSTEAD SELECT pg_class.relname, + pg_class.relnamespace, + pg_class.reltype, + pg_class.reloftype,+ pg_class.relowner, + pg_class.relam,+ pg_class.relfilenode, + pg_class.reltablespace,+ pg_class.relpages, + pg_class.reltuples,+ pg_class.relallvisible,+ pg_class.reltoastrelid,+ pg_class.reltoastidxid,+ pg_class.relhasindex, + pg_class.relisshared, + pg_class.relpersistence, + pg_class.relkind, + pg_class.relnatts, + pg_class.relchecks,+ pg_class.relhasoids, + pg_class.relhaspkey, + pg_class.relhasrules, + pg_class.relhastriggers, + pg_class.relhassubclass, + pg_class.relispopulated, + pg_class.relfrozenxid, + pg_class.relminmxid, + pg_class.relacl, + pg_class.reloptions+ FROM pg_class; (1 row) -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Minor spelling fixes
* Thom Brown (t...@linux.com) wrote: Oh, if you're in the mood for typo-correction... Done, thanks. I wouldn't be against material improvements to the comments in some of the code that's being fixed for typos either, by the way.. Starting with actually *having* some, in some places. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Running pgindent
* Bruce Momjian (br...@momjian.us) wrote: On Fri, May 31, 2013 at 03:51:30PM -0400, Andrew Dunstan wrote: I spent quite a lot of time trying to make the tool behave the same as the old script. Yes, and I believe we tested running the Perl version to make sure it was the same, so the changes we are seeing are just normal (unfortunate) adjustments by pgindent. Just to wrap this up- I wanted to say thanks to both you (Bruce) and to Andrew for making pgindent work and the documentation / instructions easy to follow. In the past, pgindent has always seemed to be a black art, where it was difficult to get consistent results across architectures due to different typedef lists, etc. Now, at least for me, it 'just worked'. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] GRANT role_name TO role_name ON database_name
* Clark C. Evans (c...@clarkevans.com) wrote: Yes, if we had per-database roles, it would work. However, I don't think it's necessary. We've already got role permissions specific to a database; so we're most of the way there. PG has two sets of catalogs, per-databases ones and 'shared' ones. There are role permissions in both (pg_database being one of the more obvious 'shared' cases). The main piece missing is a way for me to assign a role to a user, but only for a specific database. Let me rephrase this, using a different syntax... I'm pretty sure that I understand what you're getting at here, but I think the direction we'd really like to go in is to have per-database roles. There are a lot of additional advantages that would provide along with covering your use-case. Inventing new syntax and having to add new catalog tables without actually getting the per-DB role system that has long been asked for seems like the wrong approach to me. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture
Craig Ringer cr...@2ndquadrant.com wrote: On 06/02/2013 05:56 AM, Robert Haas wrote: I agree with all that. I don't have any data either, but I agree that AFAICT it seems to mostly be a problem for large (terabyte-scale) databases, or ones that are dreadfully short of I/O bandwidth. AWS, I'm looking at you. I've seen cases on Stack Overflow and elsewhere in which disk merge sorts perform vastly better than in-memory quicksort, so the user benefited from greatly *lowering* work_mem. I have seen this a few times, to. It would be interesting to characterize the conditions under which this is the case. (b) users making ridiculous settings changes to avoid the problems caused by anti-wraparound vacuums kicking in at inconvenient times and eating up too many resources. Where I hit a nightmare scenario with an anti-wraparound autovacuum, personally, was after an upgrade using pg_dump piped to psql. At a high OLTP transaction load time (obviously the most likely time for it to kick in, because it is triggered by xid consumption), it started to READ AND REWRITE every heap page of every table. This overwhelmed the battery-backed write cache, causing a series of freezes for a few minutes at a time, raising a very large number of end-user complaints. This is when I started insisting on a VACUUM FREEZE ANALYZE after any bulk load before it was considered complete and the database brought online for production use. Some recent experiences I've had have also bought home to me that vacuum problems are often of the user's own making. My database is slow - This autovacuum thing is using up lots of I/O and CPU, I'll increase this delay setting here - My database is slower - Maybe I didn't solve the autovacuum thing, I'll just turn it off - My database is barely working - I'll whack in some manual VACUUM cron jobs during low load maintenance hours and hope that keeps the worst of the problem away, that's what random forum posts on the Internet say to do. - oh my, why did my DB just do an emergency shutdown? Yeah, I've seen exactly that sequence, and some variations on it quite often. In fact, when I was first using PostgreSQL I got as far as Maybe I didn't solve the autovacuum thing but instead of I'll just turn it off my next step was I wonder what would happen if I tried making it *more* aggressive so that it didn't have so much work to do each time it fired? Of course, that vastly improved things. I have found it surprisingly difficult to convince other people to try that, though. I have seen people so convinced that vacuum (and particularly autovacuum) are *so* evil that they turn off autovacuum and monitor the freeze status of their tables and databases so that they can run VACUUM just in time to prevent the emergency shutdown. Obviously, this isn't great for their performance. :-( -- Kevin Grittner 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] Vacuum, Freeze and Analyze: the big picture
On 2013-06-02 11:44:04 -0700, Jeff Janes wrote: Do we know why anti-wraparound uses so many resources in the first place? The default settings seem to be quite conservative to me, even for a system that has only a single 5400 rpm hdd (and even more so for any real production system that would be used for a many-GB database). I guess the point is that nobody can actually run a bigger OLTP database successfully with the default settings. Usually that will end up with a) huge amounts of bloat in the tables autovac doesn't scan first b) forced shutdowns because autovac doesn't freeze quickly enough. The default suggestion that frequently seems to be made is just to disable autovac cost limitations because of 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] Implicit rule created for materialized views
Dave Page dp...@pgadmin.org writes: On Mon, Jun 3, 2013 at 1:06 PM, Kevin Grittner kevin.gritt...@enterprisedb.com wrote: A materialized view is pretty much like a view, but with the results materialized. Yeah, I get that, but what is confusing is that this now seems to be a special kind of relation where there is an ON SELECT DO INSTEAD rule which isn't actually executed on SELECTs from the view but at some arbitrary time in the future. There is that. I wondered before if it would be worth the trouble to invent a distinct pg_rewrite.ev_type value for these things, ie the rule would be something like ON REFRESH DO INSTEAD On balance that seems like it would force a lot of additional code changes for questionable benefit, though. 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] erroneous restore into pg_catalog schema
Heikki Linnakangas hlinnakan...@vmware.com writes: In the interest of getting the release out, I've reverted commit a475c603. We'll probably want to do something more elegant in the future, but this will do for now. That may be the best short-term answer, but I see no such revert in the repo ... 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] erroneous restore into pg_catalog schema
On 03.06.2013 17:18, Tom Lane wrote: Heikki Linnakangashlinnakan...@vmware.com writes: In the interest of getting the release out, I've reverted commit a475c603. We'll probably want to do something more elegant in the future, but this will do for now. That may be the best short-term answer, but I see no such revert in the repo ... Oh, forgot to push. It's there now. - 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] Perl 5.18 breaks pl/perl regression tests?
Kaare Rasmussen ka...@jasonic.dk writes: That page is not updated, it seems. In this list https://metacpan.org/module/RJBS/perl-5.18.0/pod/perldelta.pod is mentioned Line numbers at the end of a string eval are no longer off by one. [perl #114658] Hah. That leads to http://perl5.git.perl.org/perl.git/commitdiff/451f421 in which it's said What happens is that eval tacks \n; on to the end of the string if it does not already end with a semicolon. So we could likely hide the cross-version difference in behavior by adjusting these two test cases to include a semicolon in the eval'd string. 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] UTF-8 encoding problem w/ libpq
I try to create database columns with umlauts, using the UTF8 client encoding. However, the server seems to mess up the column names. In particular, it seems to perform a lowercase operation on each byte of the UTF-8 multi-byte sequence. Here is my code: const wchar_t *strName = Lid_äß; wstring strCreate = wstring(Lcreate table test_umlaut() + strName + L integer primary key); PGconn *pConn = PQsetdbLogin(, , NULL, NULL, dev503, postgres, **); if (!pConn) FAIL; if (PQsetClientEncoding(pConn, UTF-8)) FAIL; PGresult *pResult = PQexec(pConn, drop table test_umlaut); if (pResult) PQclear(pResult); pResult = PQexec(pConn, ToUtf8(strCreate.c_str()).c_str()); if (pResult) PQclear(pResult); pResult = PQexec(pConn, select * from test_umlaut); if (!pResult) FAIL; if (PQresultStatus(pResult)!=PGRES_TUPLES_OK) FAIL; if (PQnfields(pResult)!=1) FAIL; const char *fName = PQfname(pResult,0); ShowW(Name: , strName); ShowA(in UTF8: , ToUtf8(strName).c_str()); ShowA(from DB: , fName); ShowW(in UTF16: , ToWide(fName).c_str()); PQclear(pResult); PQreset(pConn); (ShowA/W call OutputDebugStringA/W, and ToUtf8/ToWide use WideCharToMultiByte/MultiByteToWideChar with CP_UTF8.) And this is the output generated: Name: id_äß in UTF8: id_äß from DB: id_ã¤ãÿ in UTF16: id_??? It seems like the backend thinks the name is in ANSI encoding, not in UTF-8. If I change the strCreate query and add double quotes around the column name, then the problem disappears. But the original name is already in lowercase, so I think it should also work without quoting the column name. Am I missing some setup in either the database or in the use of libpq? I’m using PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit The database uses: ENCODING = 'UTF8' LC_COLLATE = 'English_United Kingdom.1252' LC_CTYPE = 'English_United Kingdom.1252' Thanks for any help, Martin
Re: [HACKERS] UTF-8 encoding problem w/ libpq
On Mon, Jun 03, 2013 at 03:40:14PM +0100, Martin Schäfer wrote: I try to create database columns with umlauts, using the UTF8 client encoding. However, the server seems to mess up the column names. In particular, it seems to perform a lowercase operation on each byte of the UTF-8 multi-byte sequence. Here is my code: const wchar_t *strName = Lid_äß; wstring strCreate = wstring(Lcreate table test_umlaut() + strName + L integer primary key); PGconn *pConn = PQsetdbLogin(, , NULL, NULL, dev503, postgres, **); if (!pConn) FAIL; if (PQsetClientEncoding(pConn, UTF-8)) FAIL; PGresult *pResult = PQexec(pConn, drop table test_umlaut); if (pResult) PQclear(pResult); pResult = PQexec(pConn, ToUtf8(strCreate.c_str()).c_str()); if (pResult) PQclear(pResult); pResult = PQexec(pConn, select * from test_umlaut); if (!pResult) FAIL; if (PQresultStatus(pResult)!=PGRES_TUPLES_OK) FAIL; if (PQnfields(pResult)!=1) FAIL; const char *fName = PQfname(pResult,0); ShowW(Name: , strName); ShowA(in UTF8: , ToUtf8(strName).c_str()); ShowA(from DB: , fName); ShowW(in UTF16: , ToWide(fName).c_str()); PQclear(pResult); PQreset(pConn); (ShowA/W call OutputDebugStringA/W, and ToUtf8/ToWide use WideCharToMultiByte/MultiByteToWideChar with CP_UTF8.) And this is the output generated: Name: id_äß in UTF8: id_äß from DB: id_ã¤ãÿ in UTF16: id_??? It seems like the backend thinks the name is in ANSI encoding, not in UTF-8. If I change the strCreate query and add double quotes around the column name, then the problem disappears. But the original name is already in lowercase, so I think it should also work without quoting the column name. Am I missing some setup in either the database or in the use of libpq? I’m using PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit The database uses: ENCODING = 'UTF8' LC_COLLATE = 'English_United Kingdom.1252' LC_CTYPE = 'English_United Kingdom.1252' Thanks for any help, Martin Hi Martin, If you do not want the lowercase behavior, you must put double-quotes around the column name per the documentation: http://www.postgresql.org/docs/9.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS section 4.1.1. Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PostgreSQL Process memory architecture
Hi Stephen, I have some basic question - How do I add this flags CATCACHE_STATS and CATCACHE_FORCE_RELEASE when building postgresql? I added it to src/Makefile.global in this line: CPPFLAGS = -D_GNU_SOURCE -DCATCACHE_STATS -DCATCACHE_FORCE_RELEASE And changed log level to debug2, but it doesn't log the catcache statistcs Lior -Original Message- From: Stephen Frost [mailto:sfr...@snowman.net] Sent: Monday, May 27, 2013 16:44 To: Ben Zeev, Lior Cc: Atri Sharma; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture * Ben Zeev, Lior (lior.ben-z...@hp.com) wrote: Each query is running in a separate transaction. Interesting. You might also compile with CATCACHE_STATS (and not CATCACHE_FORCE_RELEASE, or perhaps with and without) and then check out your logs after the process ends (you might need to increase the logging level to DEBUG2 if you don't see anything initially). Why does portioning is done better rather than using partial index? There's a couple of reasons, but for one thing, you can do parallel loading of data into partitioned tables (particularly if you refer to the individual partitions directly rather than going through the top-level table with a trigger or similar). Trying to parallel load into one table with 500 indexes would be pretty painful, I expect. Thanks, Stephen -- 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] Implicit rule created for materialized views
Dave Page dp...@pgadmin.org wrote: Kevin Grittner kevin.gritt...@enterprisedb.com wrote: Dave Page dp...@pgadmin.org wrote: In playing with materialized views, I noticed that they still seem to have an _RETURN rule implicitly created like a regular view. A materialized view is pretty much like a view, but with the results materialized. Yeah, I get that, but what is confusing is that this now seems to be a special kind of relation where there is an ON SELECT DO INSTEAD rule which isn't actually executed on SELECTs from the view but at some arbitrary time in the future. Perhaps this way of looking at it will allow it to make sense: It generates values which will be returned by SELECT -- it just does that in advance and caches them on disk for quicker return when queried. As a practical matter, a materialized view needs to store exactly the same information about its query, in the same form, as a regular view. To add a new table to store this in a different place, with references and such maintained in the same way, would have multiplied the size of the patch with a lot of copy/pasted code. I'm pretty sure the result would have been something which was harder to review and maintain. -- Kevin Grittner 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] UTF-8 encoding problem w/ libpq
-Original Message- From: k...@rice.edu [mailto:k...@rice.edu] Sent: 03 June 2013 16:48 To: Martin Schäfer Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] UTF-8 encoding problem w/ libpq On Mon, Jun 03, 2013 at 03:40:14PM +0100, Martin Schäfer wrote: I try to create database columns with umlauts, using the UTF8 client encoding. However, the server seems to mess up the column names. In particular, it seems to perform a lowercase operation on each byte of the UTF-8 multi-byte sequence. Here is my code: const wchar_t *strName = Lid_äß; wstring strCreate = wstring(Lcreate table test_umlaut() + strName + L integer primary key); PGconn *pConn = PQsetdbLogin(, , NULL, NULL, dev503, postgres, **); if (!pConn) FAIL; if (PQsetClientEncoding(pConn, UTF-8)) FAIL; PGresult *pResult = PQexec(pConn, drop table test_umlaut); if (pResult) PQclear(pResult); pResult = PQexec(pConn, ToUtf8(strCreate.c_str()).c_str()); if (pResult) PQclear(pResult); pResult = PQexec(pConn, select * from test_umlaut); if (!pResult) FAIL; if (PQresultStatus(pResult)!=PGRES_TUPLES_OK) FAIL; if (PQnfields(pResult)!=1) FAIL; const char *fName = PQfname(pResult,0); ShowW(Name: , strName); ShowA(in UTF8: , ToUtf8(strName).c_str()); ShowA(from DB: , fName); ShowW(in UTF16: , ToWide(fName).c_str()); PQclear(pResult); PQreset(pConn); (ShowA/W call OutputDebugStringA/W, and ToUtf8/ToWide use WideCharToMultiByte/MultiByteToWideChar with CP_UTF8.) And this is the output generated: Name: id_äß in UTF8: id_äß from DB: id_ã¤ãÿ in UTF16: id_??? It seems like the backend thinks the name is in ANSI encoding, not in UTF-8. If I change the strCreate query and add double quotes around the column name, then the problem disappears. But the original name is already in lowercase, so I think it should also work without quoting the column name. Am I missing some setup in either the database or in the use of libpq? I’m using PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit The database uses: ENCODING = 'UTF8' LC_COLLATE = 'English_United Kingdom.1252' LC_CTYPE = 'English_United Kingdom.1252' Thanks for any help, Martin Hi Martin, If you do not want the lowercase behavior, you must put double-quotes around the column name per the documentation: http://www.postgresql.org/docs/9.2/interactive/sql-syntax- lexical.html#SQL-SYNTAX-IDENTIFIERS section 4.1.1. Regards, Ken The original name 'id_äß' is already in lowercase. The backend should leave it unchanged IMO. Regards, Martin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Running pgindent
Stephen Frost escribió: Just to wrap this up- I wanted to say thanks to both you (Bruce) and to Andrew for making pgindent work and the documentation / instructions easy to follow. In the past, pgindent has always seemed to be a black art, where it was difficult to get consistent results across architectures due to different typedef lists, etc. Now, at least for me, it 'just worked'. Yes, agreed. I tend to add a pgindent step to my patch submissions (even though my editor does a pretty good job), and this has been made possible by this work. Thanks guys. -- Á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] UTF-8 encoding problem w/ libpq
On Mon, Jun 03, 2013 at 04:09:29PM +0100, Martin Schäfer wrote: If I change the strCreate query and add double quotes around the column name, then the problem disappears. But the original name is already in lowercase, so I think it should also work without quoting the column name. Am I missing some setup in either the database or in the use of libpq? I’m using PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit The database uses: ENCODING = 'UTF8' LC_COLLATE = 'English_United Kingdom.1252' LC_CTYPE = 'English_United Kingdom.1252' Thanks for any help, Martin Hi Martin, If you do not want the lowercase behavior, you must put double-quotes around the column name per the documentation: http://www.postgresql.org/docs/9.2/interactive/sql-syntax- lexical.html#SQL-SYNTAX-IDENTIFIERS section 4.1.1. Regards, Ken The original name 'id_äß' is already in lowercase. The backend should leave it unchanged IMO. Regards, Martin Only in utf-8 which needs to be double-quoted for a column name as you have seen, otherwise the value will be lowercased per byte. Regards, Ken -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Implicit rule created for materialized views
On Mon, Jun 3, 2013 at 3:59 PM, Kevin Grittner kgri...@ymail.com wrote: Dave Page dp...@pgadmin.org wrote: Kevin Grittner kevin.gritt...@enterprisedb.com wrote: Dave Page dp...@pgadmin.org wrote: In playing with materialized views, I noticed that they still seem to have an _RETURN rule implicitly created like a regular view. A materialized view is pretty much like a view, but with the results materialized. Yeah, I get that, but what is confusing is that this now seems to be a special kind of relation where there is an ON SELECT DO INSTEAD rule which isn't actually executed on SELECTs from the view but at some arbitrary time in the future. Perhaps this way of looking at it will allow it to make sense: It generates values which will be returned by SELECT -- it just does that in advance and caches them on disk for quicker return when queried. That perspective certainly makes it clearer. As a practical matter, a materialized view needs to store exactly the same information about its query, in the same form, as a regular view. To add a new table to store this in a different place, with references and such maintained in the same way, would have multiplied the size of the patch with a lot of copy/pasted code. I'm pretty sure the result would have been something which was harder to review and maintain. Yeah, I have no desire for that to be done. I'm just trying to understand what looked like some weirdness in the way it all worked. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Perl 5.18 breaks pl/perl regression tests?
On Jun 3, 2013, at 7:31 AM, Tom Lane t...@sss.pgh.pa.us wrote: Hah. That leads to http://perl5.git.perl.org/perl.git/commitdiff/451f421 in which it's said What happens is that eval tacks \n; on to the end of the string if it does not already end with a semicolon. So we could likely hide the cross-version difference in behavior by adjusting these two test cases to include a semicolon in the eval'd string. And a comment, since that is, shall we say, rather obscure. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF-8 encoding problem w/ libpq
On 03.06.2013 18:27, k...@rice.edu wrote: On Mon, Jun 03, 2013 at 04:09:29PM +0100, Martin Schäfer wrote: If I change the strCreate query and add double quotes around the column name, then the problem disappears. But the original name is already in lowercase, so I think it should also work without quoting the column name. Am I missing some setup in either the database or in the use of libpq? I’m using PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit The database uses: ENCODING = 'UTF8' LC_COLLATE = 'English_United Kingdom.1252' LC_CTYPE = 'English_United Kingdom.1252' Thanks for any help, Martin Hi Martin, If you do not want the lowercase behavior, you must put double-quotes around the column name per the documentation: http://www.postgresql.org/docs/9.2/interactive/sql-syntax- lexical.html#SQL-SYNTAX-IDENTIFIERS section 4.1.1. Regards, Ken The original name 'id_äß' is already in lowercase. The backend should leave it unchanged IMO. Only in utf-8 which needs to be double-quoted for a column name as you have seen, otherwise the value will be lowercased per byte. He *is* using UTF-8. Or trying to, anyway :-). The downcasing in the backend is supposed to leave bytes with the high-bit set alone, ie. in UTF-8 encoding, it's supposed to leave ä and ß alone. I suspect that the conversion to UTF-8, before the string is sent to the server, is not being done correctly. I'm not sure what's wrong there, but I'd suggest printing the actual byte sequence sent to the server, to check if it's in fact valid UTF-8. ie. replace the PQexec() line with something like: const char *s = ToUtf8(strCreate.c_str()).c_str(); int i; for (i=0; s[i]; i++) printf(%02x, (unsigned char) s[i]); printf(\n); pResult = PQexec(pConn, s); That should contain the UTF-8 byte sequence for äß, c3a4c39f - 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] Re: [HACKERS] high io BUT huge amount of free memory
On Mon, Jun 3, 2013 at 11:08 AM, Миша Тюрин tmih...@bk.ru wrote: Hi all hackers again! Since i had got this topic there many test was done by our team and many papers was seen. And then I noticed that os_page_replacement_algorithm with CLOCK and others features might * interfere / overlap * with/on postgres_shared_buffers. I also think there are positive correlation between the write load and the pressure on file cache in case with large shared buffers. I assumed if i would set smaller size of buffers that cache could work more effective because files pages has more probability to be placed in the right place in memory. After all we set shared buffers down to 16GB ( instead of 64GB ) and we got new pictures. Now we have alive raid! 16GB shared buffers = and we won 80 GB of server memory! It is good result. But upto 70GB of memory are still unused // instead of 150. In future I think we can set shared buffers more close to zero or to 100% of all available memory. Many thanks Oleg Bartunov and Fedor Sigaev for their tests and some interesting assumptions. hm, in that case, wouldn't adding 48gb of physical memory have approximately the same effect? or is something else going on? merlin -- 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] Vacuum, Freeze and Analyze: the big picture
I agree with all that. I don't have any data either, but I agree that AFAICT it seems to mostly be a problem for large (terabyte-scale) databases, or ones that are dreadfully short of I/O bandwidth. AWS, I'm looking at you. Well, at this point, numerically I'd bet that more than 50% of our users are on AWS, some other cloud, or some kind of iSCSI storage ... some place where IO sucks. It's How Things Are Done Now. Speaking for my own clientele, people run into issues, or think they have issues, with autovacuum at databases as small as 100GB, as long as they have sufficient write throughput. One really pathological case I had to troubleshoot was a database which was only 200MB in size! (this database contained counts of things, and was updated 10,000 times per second). Anyway, my goal with that wiki page -- which is on the wiki so that others can add to it -- is to get all of the common chronic issues on the table so that we don't inadvertently make one problem worse while making another one better. Some of the solutions to FREEZE being bandied around seemed likely to do that. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture
Jeff, Do we know why anti-wraparound uses so many resources in the first place? The default settings seem to be quite conservative to me, even for a system that has only a single 5400 rpm hdd (and even more so for any real production system that would be used for a many-GB database). I wonder if there is something simple but currently unknown going on which is causing it to damage performance out of all proportion to the resources it ought to be using. Does anti-wraparound vacuum (AWAV) write synchronously? If so, there's a potential whole world of hurt there. Otherwise, the effect you're seeing is just blowing out various caches: the CPU cache, storage cache, and filesystem cache. While we can (and do) prevent vacuum from blowing out shared_buffers, we can't do much about the others. Also, locking while it does its work. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF-8 encoding problem w/ libpq
On 06/03/2013 12:22 PM, Heikki Linnakangas wrote: On 03.06.2013 18:27, k...@rice.edu wrote: On Mon, Jun 03, 2013 at 04:09:29PM +0100, Martin Schäfer wrote: If I change the strCreate query and add double quotes around the column name, then the problem disappears. But the original name is already in lowercase, so I think it should also work without quoting the column name. Am I missing some setup in either the database or in the use of libpq? I’m using PostgreSQL 9.2.1, compiled by Visual C++ build 1600, 64-bit The database uses: ENCODING = 'UTF8' LC_COLLATE = 'English_United Kingdom.1252' LC_CTYPE = 'English_United Kingdom.1252' Thanks for any help, Martin Hi Martin, If you do not want the lowercase behavior, you must put double-quotes around the column name per the documentation: http://www.postgresql.org/docs/9.2/interactive/sql-syntax- lexical.html#SQL-SYNTAX-IDENTIFIERS section 4.1.1. Regards, Ken The original name 'id_äß' is already in lowercase. The backend should leave it unchanged IMO. Only in utf-8 which needs to be double-quoted for a column name as you have seen, otherwise the value will be lowercased per byte. He *is* using UTF-8. Or trying to, anyway :-). The downcasing in the backend is supposed to leave bytes with the high-bit set alone, ie. in UTF-8 encoding, it's supposed to leave ä and ß alone. I suspect that the conversion to UTF-8, before the string is sent to the server, is not being done correctly. I'm not sure what's wrong there, but I'd suggest printing the actual byte sequence sent to the server, to check if it's in fact valid UTF-8. ie. replace the PQexec() line with something like: const char *s = ToUtf8(strCreate.c_str()).c_str(); int i; for (i=0; s[i]; i++) printf(%02x, (unsigned char) s[i]); printf(\n); pResult = PQexec(pConn, s); That should contain the UTF-8 byte sequence for äß, c3a4c39f Umm, no, the backend code doesn't do it right. Some time ago I suggested a fix for this - see http://www.postgresql.org/message-id/50acf7fa.7070...@dunslane.net. Tom thought there might be other places that need fixing, and I haven't had time to look for them. But maybe we should just fix this one for now at least. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture
On Mon, Jun 3, 2013 at 1:28 PM, Josh Berkus j...@agliodbs.com wrote: Does anti-wraparound vacuum (AWAV) write synchronously? If so, there's a potential whole world of hurt there. Not any moreso than anything else ... although it probably does a very high percentage of FPIs, which might lead to lots of checkpointing. Also, locking while it does its work. Eh? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re[2]: [HACKERS] Re: [HACKERS] high io BUT huge amount of free memory
hm, in that case, wouldn't adding 48gb of physical memory have approximately the same effect? or is something else going on? imho, adding 48gb would have no effects. server already has 376GB memory and still has a lot of unused GB. let me repeat, we added 80GB for files cache by decreasing buffers from 64GB to 16GB. there are was 150GB of unused, and now unused part is only 70GB. some of links i read about eviction http://linux-mm.org/PageReplacementDesign http://linux-mm.org/PageReplacementRequirements mikhail -- 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] Vacuum, Freeze and Analyze: the big picture
Also, locking while it does its work. Eh? Even if we're doing lazy vacuum, we have to lock a few pages at a time of each table. This does result in response time delays on the current workload, which can be quite bad if it's a highly contended table already. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Vacuum, Freeze and Analyze: the big picture
On 2013-06-03 11:00:38 -0700, Josh Berkus wrote: Also, locking while it does its work. Eh? Even if we're doing lazy vacuum, we have to lock a few pages at a time of each table. This does result in response time delays on the current workload, which can be quite bad if it's a highly contended table already. We don't really lock more pages at a time than normal DML does. 1 heap page at a time, possibly several index pages at once. There's something related which can cause problems which is that we require cleanup locks on the page to be able to repair fragmentation which makes *vacuum* wait for all clients to release their page pins. 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] UTF-8 encoding problem w/ libpq
Heikki Linnakangas hlinnakan...@vmware.com writes: He *is* using UTF-8. Or trying to, anyway :-). The downcasing in the backend is supposed to leave bytes with the high-bit set alone, ie. in UTF-8 encoding, it's supposed to leave ä and à alone. Well, actually, downcase_truncate_identifier() is doing this: unsigned char ch = (unsigned char) ident[i]; if (ch = 'A' ch = 'Z') ch += 'a' - 'A'; else if (IS_HIGHBIT_SET(ch) isupper(ch)) ch = tolower(ch); There's basically no way that that second case can give pleasant results in a multibyte encoding, other than by not doing anything. I suspect that Windows' libc has fewer defenses than other implementations and performs some transformation that we don't get elsewhere. This may also explain the gripe yesterday in -general about funny results in OS X. We talked about this before and went off into the weeds about whether it was sensible to try to use towlower() and whether that wouldn't create undesirably platform-sensitive results. I wonder though if we couldn't just fix this code to not do anything to high-bit-set bytes in multibyte encodings. 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] Implicit rule created for materialized views
Yes, that is currently used for REFRESH, and will be used to drive the incremental maintenance when that is added. Without it, CREATE MATERIALIZED VIEW wouldn't be different from CREATE TABLE AS. A materialized view is pretty much like a view, but with the results materialized. -Kevin On Mon, Jun 3, 2013 at 6:58 AM, Dave Page dp...@pgadmin.org wrote: Hi In playing with materialized views, I noticed that they still seem to have an _RETURN rule implicitly created like a regular view. This doesn't seem right to me - is there a reason? viper:~ dpage$ /usr/local/pgsql-9.3/bin/psql -p 5433 -U postgres psql (9.3beta1) Type help for help. postgres=# CREATE MATERIALIZED VIEW ruletest AS SELECT * FROM pg_class; SELECT 298 postgres=# SELECT pg_get_ruledef(oid) FROM pg_rewrite WHERE ev_class = 'ruletest'::regclass; pg_get_ruledef - CREATE RULE _RETURN AS + ON SELECT TO ruletest DO INSTEAD SELECT pg_class.relname, + pg_class.relnamespace, + pg_class.reltype, + pg_class.reloftype,+ pg_class.relowner, + pg_class.relam,+ pg_class.relfilenode, + pg_class.reltablespace,+ pg_class.relpages, + pg_class.reltuples,+ pg_class.relallvisible,+ pg_class.reltoastrelid,+ pg_class.reltoastidxid,+ pg_class.relhasindex, + pg_class.relisshared, + pg_class.relpersistence, + pg_class.relkind, + pg_class.relnatts, + pg_class.relchecks,+ pg_class.relhasoids, + pg_class.relhaspkey, + pg_class.relhasrules, + pg_class.relhastriggers, + pg_class.relhassubclass, + pg_class.relispopulated, + pg_class.relfrozenxid, + pg_class.relminmxid, + pg_class.relacl, + pg_class.reloptions+ FROM pg_class; (1 row) -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Optimising Foreign Key checks
On 6/2/13 4:45 AM, Simon Riggs wrote: Will this add too much cost where it doesn't help? I don't know what to predict there. There's the obvious case of trivial transactions with no more than one referential integrity check per FK, but there's also the case of a transaction with many FK checks all searching different keys. If the hash hit rate (key duplication rate) is low, the hash can consume considerably more memory than the trigger queue without preventing many RI queries. What sort of heuristic could we use to avoid pessimizing such cases? I've struggled with that for a while now. Probably all we can say is that there might be one, and if there is not, then manual decoration of the transaction will be the way to go. Just an idea... each backend could keep a store that indicates what FKs this would help with. For example, any time we hit a transaction that exercises the same FK more than once, we stick the OID of the FK constraint (or maybe of the two tables) into a hash that's in that backend's top memory context. (Or if we want to be real fancy, shared mem). -- Jim C. Nasby, Data Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- 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] UTF-8 encoding problem w/ libpq
On 06/03/2013 02:28 PM, Tom Lane wrote: . I wonder though if we couldn't just fix this code to not do anything to high-bit-set bytes in multibyte encodings. That's exactly what I suggested back in November. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF-8 encoding problem w/ libpq
On 03.06.2013 21:28, Tom Lane wrote: Heikki Linnakangashlinnakan...@vmware.com writes: He *is* using UTF-8. Or trying to, anyway :-). The downcasing in the backend is supposed to leave bytes with the high-bit set alone, ie. in UTF-8 encoding, it's supposed to leave ä and ß alone. Well, actually, downcase_truncate_identifier() is doing this: unsigned char ch = (unsigned char) ident[i]; if (ch= 'A' ch= 'Z') ch += 'a' - 'A'; else if (IS_HIGHBIT_SET(ch) isupper(ch)) ch = tolower(ch); There's basically no way that that second case can give pleasant results in a multibyte encoding, other than by not doing anything. Hmph, I see. I suspect that Windows' libc has fewer defenses than other implementations and performs some transformation that we don't get elsewhere. This may also explain the gripe yesterday in -general about funny results in OS X. Can't really blame Windows on that. On Windows, we don't require that the encoding and LC_CTYPE's charset match. The OP used UTF-8 encoding in the server, but LC_CTYPE=English_United Kingdom.1252, ie. LC_CTYPE implies WIN1252 encoding. We allow that and it generally works on Windows because in varstr_cmp, we use MultiByteToWideChar() followed by wcscoll_l(), which doesn't care about the charset implied by LC_CTYPE. But for isupper(), it matters. We talked about this before and went off into the weeds about whether it was sensible to try to use towlower() and whether that wouldn't create undesirably platform-sensitive results. I wonder though if we couldn't just fix this code to not do anything to high-bit-set bytes in multibyte encodings. Yeah, we should do that. It makes no sense to call isupper or tolower on bytes belonging to multi-byte characters. - 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] Vacuum, Freeze and Analyze: the big picture
On 06/03/2013 11:12 AM, Andres Freund wrote: On 2013-06-03 11:00:38 -0700, Josh Berkus wrote: Also, locking while it does its work. Eh? Even if we're doing lazy vacuum, we have to lock a few pages at a time of each table. This does result in response time delays on the current workload, which can be quite bad if it's a highly contended table already. We don't really lock more pages at a time than normal DML does. 1 heap page at a time, possibly several index pages at once. Really? I though vacuum held onto its locks until it reached vacuum_cost. If it doesn't, then maybe we should adjust the default for vacuum_cost_limit upwards. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimising Foreign Key checks
On 3 June 2013 19:41, Jim Nasby j...@nasby.net wrote: On 6/2/13 4:45 AM, Simon Riggs wrote: Will this add too much cost where it doesn't help? I don't know what to predict there. There's the obvious case of trivial transactions with no more than one referential integrity check per FK, but there's also the case of a transaction with many FK checks all searching different keys. If the hash hit rate (key duplication rate) is low, the hash can consume considerably more memory than the trigger queue without preventing many RI queries. What sort of heuristic could we use to avoid pessimizing such cases? I've struggled with that for a while now. Probably all we can say is that there might be one, and if there is not, then manual decoration of the transaction will be the way to go. Just an idea... each backend could keep a store that indicates what FKs this would help with. For example, any time we hit a transaction that exercises the same FK more than once, we stick the OID of the FK constraint (or maybe of the two tables) into a hash that's in that backend's top memory context. (Or if we want to be real fancy, shared mem). Yes, that principle would work. We could just store that on the relcache entry for a table. It requires a little bookkeeping to implement that heuristic. I'm sure other ways exist as well. -- 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] Question about storage subsystem of PotgreSQL
Hi I want to find some architectural information about the storage subsystem of PostgreSQL especially the modulus which are responsible to loading data from disk to the Shared Buffer. I cannot find any structured and useful information on web. Would you please help me to find such information. Should I read the source code? Which part of code is responsible to reading data from the disk? Regards Javadi -- 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] Vacuum, Freeze and Analyze: the big picture
On Mon, Jun 03, 2013 at 11:27:57AM +0300, Ants Aasma wrote: I can't rule that out. Personally, I've always attributed it to the fact that it's (a) long and (b) I/O-intensive. But it's not impossible there could also be bugs lurking. It could be related to the OS. I have no evidence for or against, but it's possible that OS write-out routines defeat the careful cost based throttling that PostgreSQL does by periodically dumping a large portion of dirty pages into the write queue at once. That does nasty things to query latencies as evidenced by the work on checkpoint spreading. In other contexts I've run into issues relating to large continuous writes stalling. The issue is basically that the Linux kernel allows (by default) writes to pile up until they reach 5% of physical memory before deciding that the sucker who wrote the last block becomes responsible for writing the whole lot out. At full speed of course. Depending on the amount of memory and the I/O speed of your disks this could take a while, and interfere with other processes. This leads to extremely bursty I/O behaviour. The solution, as usual, is to make it more aggressive, so the kernel background writer triggers at 1% memory. I'm not saying that's the problem here, but it is an example of a situation where the write queue can become very large very quickly. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Question about storage subsystem of PotgreSQL
On Sat, Jun 1, 2013 at 02:11:13PM +0430, javadi wrote: Hi I want to find some architectural information about the storage subsystem of PostgreSQL especially the modulus which are responsible to loading data from disk to the Shared Buffer. I cannot find any structured and useful information on web. Would you please help me to find such information. Should I read the source code? Which part of code is responsible to reading data from the disk? You should read the developer's FAQ: http://wiki.postgresql.org/wiki/Developer_FAQ -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question about storage subsystem of PotgreSQL
javadi seyyedahmad.jav...@gmail.com wrote: I want to find some architectural information about the storage subsystem of PostgreSQL especially the modulus which are responsible to loading data from disk to the Shared Buffer. I cannot find any structured and useful information on web. Would you please help me to find such information. Should I read the source code? Which part of code is responsible to reading data from the disk? You might find the README for the storage manager helpful: http://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/storage/smgr/README -- Kevin Grittner 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] Vacuum, Freeze and Analyze: the big picture
On Mon, Jun 3, 2013 at 2:56 PM, Josh Berkus j...@agliodbs.com wrote: Really? I though vacuum held onto its locks until it reached vacuum_cost. If it doesn't, then maybe we should adjust the default for vacuum_cost_limit upwards. That would be completely insane. Or in other words, no, it doesn't do anything like that. :-) -- 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] Vacuum, Freeze and Analyze: the big picture
On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote: I've seen cases on Stack Overflow and elsewhere in which disk merge sorts perform vastly better than in-memory quicksort, so the user benefited from greatly *lowering* work_mem. I've heard of that happening on Oracle, when the external sort is capable of taking advantage of I/O parallelism, but I have a pretty hard time believing that it could happen with Postgres under any circumstances. Maybe if someone was extraordinarily unlucky and happened to hit quicksort's O(n ^ 2) worst case it could happen, but we take various measures that make that very unlikely. It might also have something to do with our check for pre-sorted input [1], but I'm still skeptical. [1] http://www.postgresql.org/message-id/caeylb_xn4-6f1ofsf2qduf24ddcvhbqidt7jppdl_rit1zb...@mail.gmail.com -- 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] Vacuum, Freeze and Analyze: the big picture
On Mon, Jun 3, 2013 at 3:48 PM, Martijn van Oosterhout klep...@svana.org wrote: On Mon, Jun 03, 2013 at 11:27:57AM +0300, Ants Aasma wrote: I can't rule that out. Personally, I've always attributed it to the fact that it's (a) long and (b) I/O-intensive. But it's not impossible there could also be bugs lurking. It could be related to the OS. I have no evidence for or against, but it's possible that OS write-out routines defeat the careful cost based throttling that PostgreSQL does by periodically dumping a large portion of dirty pages into the write queue at once. That does nasty things to query latencies as evidenced by the work on checkpoint spreading. In other contexts I've run into issues relating to large continuous writes stalling. The issue is basically that the Linux kernel allows (by default) writes to pile up until they reach 5% of physical memory before deciding that the sucker who wrote the last block becomes responsible for writing the whole lot out. At full speed of course. Depending on the amount of memory and the I/O speed of your disks this could take a while, and interfere with other processes. This leads to extremely bursty I/O behaviour. The solution, as usual, is to make it more aggressive, so the kernel background writer triggers at 1% memory. I'm not saying that's the problem here, but it is an example of a situation where the write queue can become very large very quickly. Yeah. IMHO, the Linux kernel's behavior around the write queue is flagrantly insane. The threshold for background writing really seems like it ought to be zero. I can see why it makes sense to postpone writing back dirty data if we're otherwise starved for I/O. But it seems like the kernel is disposed to cache large amounts of dirty data for an unbounded period of time even if the I/O system is completely idle, and it's difficult to imagine what class of user would find that behavior desirable. -- 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] Vacuum, Freeze and Analyze: the big picture
Robert Haas robertmh...@gmail.com wrote: Martijn van Oosterhout klep...@svana.org wrote: On Mon, Jun 03, 2013 at 11:27:57AM +0300, Ants Aasma wrote: It could be related to the OS. I have no evidence for or against, but it's possible that OS write-out routines defeat the careful cost based throttling that PostgreSQL does by periodically dumping a large portion of dirty pages into the write queue at once. That does nasty things to query latencies as evidenced by the work on checkpoint spreading. In other contexts I've run into issues relating to large continuous writes stalling. The issue is basically that the Linux kernel allows (by default) writes to pile up until they reach 5% of physical memory before deciding that the sucker who wrote the last block becomes responsible for writing the whole lot out. At full speed of course. Depending on the amount of memory and the I/O speed of your disks this could take a while, and interfere with other processes. This leads to extremely bursty I/O behaviour. The solution, as usual, is to make it more aggressive, so the kernel background writer triggers at 1% memory. I'm not saying that's the problem here, but it is an example of a situation where the write queue can become very large very quickly. Yeah. IMHO, the Linux kernel's behavior around the write queue is flagrantly insane. The threshold for background writing really seems like it ought to be zero. I can see why it makes sense to postpone writing back dirty data if we're otherwise starved for I/O. I imagine the reason the OS guys would give for holding up on disk writes for as long as possible would sound an awful lot like the reason PostgreSQL developers give for doing it. Keep in mind that the OS doesn't know whether there might or might not be another layer of caching (on a battery-backed RAID controller or SSD). It's trying to minimize disk writes by waiting, to improve throughput by collapsing duplicate writes and allowing the writes to be performed in a more efficient order based on physical layout. But it seems like the kernel is disposed to cache large amounts of dirty data for an unbounded period of time even if the I/O system is completely idle, It's not unbounded time. Last I heard, the default was 30 seconds. and it's difficult to imagine what class of user would find that behavior desirable. Well, certainly not a user of a database that keeps dirty pages lingering for five minutes by default, and often increases that to minimize full page writes. IMO, our defaults for bgwriter are far too passive. -- Kevin Grittner 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] Vacuum, Freeze and Analyze: the big picture
On Mon, Jun 3, 2013 at 3:16 PM, Kevin Grittner kgri...@ymail.com wrote: But it seems like the kernel is disposed to cache large amounts of dirty data for an unbounded period of time even if the I/O system is completely idle, It's not unbounded time. Last I heard, the default was 30 seconds. I'm pretty sure it is unbounded. The VM documentation is a bit vague on what dirty_expire_centisecs actually means, which is I presume where this number comes from. It says: This tunable is used to define when dirty data is old enough to be eligible for writeout by the pdflush daemons. It is expressed in 100'ths of a second. Data which has been dirty in-memory for longer than this interval will be written out next time a pdflush daemon wakes up. So I think the a pdflush daemon won't necessarily wake up until dirty_background_bytes or dirty_background_ratio have been exceeded, regardless of this threshold. Am I mistaken? https://www.kernel.org/doc/Documentation/sysctl/vm.txt -- 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] Vacuum, Freeze and Analyze: the big picture
On 06/04/2013 05:27 AM, Peter Geoghegan wrote: On Mon, Jun 3, 2013 at 5:03 AM, Craig Ringer cr...@2ndquadrant.com wrote: I've seen cases on Stack Overflow and elsewhere in which disk merge sorts perform vastly better than in-memory quicksort, so the user benefited from greatly *lowering* work_mem. I've heard of that happening on Oracle, when the external sort is capable of taking advantage of I/O parallelism, but I have a pretty hard time believing that it could happen with Postgres under any circumstances. IIRC it's usually occurred with very expensive comparison operations. I'll see if I can find one of the SO cases. -- Craig Ringer 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] Vacuum, Freeze and Analyze: the big picture
On 2013-06-03 16:41:32 -0700, Peter Geoghegan wrote: On Mon, Jun 3, 2013 at 3:16 PM, Kevin Grittner kgri...@ymail.com wrote: But it seems like the kernel is disposed to cache large amounts of dirty data for an unbounded period of time even if the I/O system is completely idle, It's not unbounded time. Last I heard, the default was 30 seconds. I'm pretty sure it is unbounded. The VM documentation is a bit vague on what dirty_expire_centisecs actually means, which is I presume where this number comes from. It says: This tunable is used to define when dirty data is old enough to be eligible for writeout by the pdflush daemons. It is expressed in 100'ths of a second. Data which has been dirty in-memory for longer than this interval will be written out next time a pdflush daemon wakes up. So I think the a pdflush daemon won't necessarily wake up until dirty_background_bytes or dirty_background_ratio have been exceeded, regardless of this threshold. Am I mistaken? Without having it checked again, afair it should wakeup every dirty_writeback_centisecs which is something like 5seconds. All that has pretty significantly changed - and imo improved! - in the last year or so of kernel development. Unfortunately it will take a while till we commonly see those kernels being used :( 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] Vacuum, Freeze and Analyze: the big picture
All that has pretty significantly changed - and imo improved! - in the last year or so of kernel development. Unfortunately it will take a while till we commonly see those kernels being used :( ... after being completely broken for 3.2 through 3.5. We're actually using 3.9 in production on some machines, because we couldn't take the IO disaster that is 3.4. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] local_preload_libraries logspam
On Mon, May 13, 2013 at 3:22 PM, Peter Geoghegan p...@heroku.com wrote: Attached patch renders all loaded library... messages DEBUG1, regardless of whether local_preload_libraries or shared_preload_libraries is involved, and regardless of EXEC_BACKEND. Can someone take a look at this, please? I'd rather like to see this issue fixed. -- 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] Optimising Foreign Key checks
On Sun, Jun 02, 2013 at 10:45:21AM +0100, Simon Riggs wrote: For clarity the 4 problems are 1. SQL execution overhead 2. Memory usage 3. Memory scrolling 4. Locking overhead, specifically FPWs and WAL records from FK checks probably in that order or thereabouts. The above is why I went for a technique that avoided SQL execution entirely, as well as conserving memory by de-duplicating the values in a hash table as we go, which avoids all three problems. The fourth was solved by the more extreme approach to locking. That nicely frames the benefits of your proposals. Makes sense. I think it might be worth considering joining the after trigger queue directly to the referenced table(s), something like this... CREATE OR REPLACE FUNCTION after_trigger_queue() RETURNS SETOF tid AS $$ ... $$ LANGUAGE SQL; EXPLAIN SELECT 1 FROM ONLY order WHERE orderid IN (SELECT orderid FROM ONLY order_line WHERE ctid IN (SELECT after_trigger_queue FROM after_trigger_queue() )) FOR KEY SHARE; Agreed. But we could optimise that even further if we had a BlockScan, which would be a block-oriented version of the tid scan where we simply provide a bitmap of blocks needing to be scanned, just like the output of an BitmapIndexScan. The reason for mentioning that here is that parallel query will eventually need the ability to do a scan of a subset of blocks, as does tablesample. So I can see 3 callers of such a Scan type. Interesting. I was going to say that could lock more keys than needed, but perhaps you would afterward filter by xmin/cmin. -- 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
[HACKERS] create a git symbolic-ref for REL9_3_STABLE
I suppose we'll be branching off 9.3 in a few weeks. That event always creates a service gap in the build farm and similar services, and a race in the NLS service to get everything adjusted to the new branch. It seems to me we could already now create a git symbolic-ref named REL9_3_STABLE that points to master, get all those services updated for the new name, and when the actual branching is supposed to happen, we just remove the symbolic-ref and make a real branch, and everything else should already be in place. I have never actually used symbolic-ref, but after playing with it a little bit, it seems it should work fine for this purpose. Comments? -- 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] create a git symbolic-ref for REL9_3_STABLE
On 06/03/2013 09:30 PM, Peter Eisentraut wrote: I suppose we'll be branching off 9.3 in a few weeks. That event always creates a service gap in the build farm and similar services, and a race in the NLS service to get everything adjusted to the new branch. The buildfarm has had a mechanism for handling this for a little while now. See http://wiki.postgresql.org/wiki/PostgreSQL_Buildfarm_Howto#Using_run_branches.pl cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump with postgis extension dumps rules separately
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 06/02/2013 03:10 PM, Dimitri Fontaine wrote: Tom Lane t...@sss.pgh.pa.us writes: Actually, I believe the answer is just that getSchemaData() is doing things in the wrong order: Indeed Tom, as usual, seems to have the best correct answer :-) New patch attached works as expected and requires nothing special for existing databases with installed extensions such as PostGIS with RULEs. Each time I have to look at the pg_dump parts I discover new things. I've been misleading Joe in telling him I though the problem must have been in extension dependency tracking for rules, without taking the necessary time to have a real look at the code. Sorry about that. BTW, I'm inclined to think it's also wrong that the getEventTriggers() call was just added at the end; those things are certainly not table subsidiary objects. I don't know if we allow event triggers to be extension members, but if we did, that call would have to occur before getExtensionMembership(). Event triggers sure should be allowed as extension members. That leaves me wondering if there's another possible code arrangement in that function so that it's easier to maintain. Maybe something with a couple of structs and a function that knows how to use them to fill in the variables, but I can see we have some inter-dependencies and some getSomething functions have quite a specialized API. I moved getEventTriggers() as well. I was surprised by a couple of things looking at this code. First, getRules() is written differently than other table subsidiary objects' get functions. Secondly, I would have expected getExtensionMembership() to be recursive -- instead it looks to only go one level deep. Perhaps the longer term fix would be to identify extension dependencies recursively, and then the reliance on strict ordering here could be relaxed. But I don't think we want to make that change in 9.3 at this point. Objections to this version? Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iQIcBAEBAgAGBQJRrVLjAAoJEDfy90M199hleA4P/iuGvghfqAo9dogK5e75e7gx AgL27/WvagrI4mhQp0RQUD3LJx52/XbGqNUMJiGBSuLgGcdwTCyC4yzLAXMEWKD6 x1l1u9mtSAhokk2KjwxdKEzFIzIQ/fYNan5FtXOlgiyq+A7v2hGsFC7ChPgnU6eW H4nnI94lm3gW7GqxFS3NNjJ0pTDKwAUCYqfoiIjA58WXSUMZoVc5F+DCsS4YjDVG wve9zf3HVhPxVi/BNCQfRF1grpZfNJFWjSRo1IclCUCqcQWr4BWyXkNuDmSft67S 4UqIkvZyPM+jCoPrJIbqo363CHYICHJ1jfeeYIn+8FnWOtm+fJoXncZbDaKQm9la iqeHv6qiQzRAq7ui59Nwgo+gSK8IKQYdXilu4wq4LgF0RSb9NTiWldSs+H2FmGLs k1VNcpGdKlKzp7gOtEAMjd+HWgbYV7Worv7nQY7MJSG81Vnx+LMfiRwSb8Tvrzox RJcd2zTX3P2ohaczUjRNT6dC9tWT84r+fbelMIOk1ZL2RJixYyzTft7YSrfaz08N iYL8ho9JPGgO6AX90cpc879HVwkJR3Ffxdu/FPH1AsgtcVO4XUBJlRex1oYWWF+y FA3Hr+yUhPtyf3Ad/PUGxPpY6ZFTkg1w5prRpDIDKXLnTtWChKrgBsiKow3K5IFA cQ2OpvmBNiTBkNkbYBKE =cAP6 -END PGP SIGNATURE- diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c index ae52ac1..81c4a95 100644 --- a/src/bin/pg_dump/common.c +++ b/src/bin/pg_dump/common.c @@ -203,8 +203,8 @@ getSchemaData(Archive *fout, int *numTablesPtr) inhinfo = getInherits(fout, numInherits); if (g_verbose) - write_msg(NULL, reading rewrite rules\n); - getRules(fout, numRules); + write_msg(NULL, reading event triggers\n); + getEventTriggers(fout, numEventTriggers); /* * Identify extension member objects and mark them as not to be dumped. @@ -215,6 +215,10 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, finding extension members\n); getExtensionMembership(fout, extinfo, numExtensions); + if (g_verbose) + write_msg(NULL, reading rewrite rules\n); + getRules(fout, numRules); + /* Link tables to parents, mark parents of target tables interesting */ if (g_verbose) write_msg(NULL, finding inheritance relationships\n); @@ -240,10 +244,6 @@ getSchemaData(Archive *fout, int *numTablesPtr) write_msg(NULL, reading triggers\n); getTriggers(fout, tblinfo, numTables); - if (g_verbose) - write_msg(NULL, reading event triggers\n); - getEventTriggers(fout, numEventTriggers); - *numTablesPtr = numTables; return tblinfo; } fix-extension-ruledeps.03.diff.sig Description: PGP signature -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump with postgis extension dumps rules separately
Joe Conway m...@joeconway.com writes: I was surprised by a couple of things looking at this code. First, getRules() is written differently than other table subsidiary objects' get functions. Secondly, I would have expected getExtensionMembership() to be recursive -- instead it looks to only go one level deep. Perhaps the longer term fix would be to identify extension dependencies recursively, and then the reliance on strict ordering here could be relaxed. But I don't think we want to make that change in 9.3 at this point. I'm not sure that's appropriate: extension membership is not a recursive concept AFAICS. In any case, as you say, it's something for more analysis later. Objections to this version? I'd have put the getRules call where getEventTriggers is now, or at least adjacent to getTriggers in one direction or the other. I'm not sure there is anything functionally wrong with what you have here; but IMO rules and table-level triggers are pretty much the same kind of critters so far as pg_dump is concerned, to wit, they're table properties not free-standing objects (which is exactly the point of this change). So it seems to me to make sense to process them together. BTW, don't forget that the getRules move needs to be back-patched. 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] MVCC catalog access
On Tue, Jun 4, 2013 at 3:57 AM, Robert Haas robertmh...@gmail.com wrote: On Thu, May 30, 2013 at 1:39 AM, Michael Paquier michael.paqu...@gmail.com wrote: +1. Here's a more serious patch for MVCC catalog access. This one involves more data copying than the last one, I think, because the previous version did not register the snapshots it took, which I think is not safe. So this needs to be re-tested for performance, which I have so far made no attempt to do. It strikes me as rather unfortunate that the snapshot interface is designed in such a way as to require so much data copying. It seems we always take a snapshot by copying from PGXACT/PGPROC into CurrentSnapshotData or SecondarySnapshotData, and then copying data a second time from there to someplace more permanent. It would be nice to avoid that, at least in common cases. And here are more results comparing master branch with and without this patch... 1) DDL CREATE/DROP test: 1-1) master: 250 connections: Create: 24846.060, Drop: 30391.713 Create: 23771.394, Drop: 29769.396 500 connections: Create: 24339.449, Drop: 30084.741 Create: 24152.176, Drop: 30643.471 1000 connections: Create: 26007.960, Drop: 31019.918 Create: 25937.592, Drop: 30600.341 2000 connections: Create: 26900.324, Drop: 30741.989 Create: 26910.660, Drop: 31577.247 1-2) mvcc catalogs: 250 connections: Create: 25371.342, Drop: 31458.952 Create: 25685.094, Drop: 31492.377 500 connections: Create: 28557.882, Drop: 33673.266 Create: 27901.910, Drop: 33223.006 1000 connections: Create: 31910.130, Drop: 36770.062 Create: 32210.093, Drop: 36754.888 2000 connections: Create: 40374.754, Drop: 43442.528 Create: 39763.691, Drop: 43234.243 2) backend startup 2-1) master branch: 250 connections: real0m8.993s user0m0.128s sys 0m0.380s 500 connections: real0m9.004s user0m0.212s sys 0m0.340s 1000 connections: real0m9.072s user0m0.272s sys 0m0.332s 2000 connections: real0m9.257s user0m0.204s sys 0m0.392s 2-2) MVCC catalogs: 250 connections: real0m9.067s user0m0.108s sys 0m0.396s 500 connections: real0m9.034s user0m0.112s sys 0m0.376s 1000 connections: real0m9.303s user0m0.176s sys 0m0.328s 2000 connections real0m9.916s user0m0.160s sys 0m0.428s Except for the case of backend startup test for 500 connections that looks to have some noise, performance degradation reaches 6% for 2000 connections, and less than 1% for 250 connections. This is better than last time. For the CREATE/DROP case, performance drop reaches 40% for 2000 connections (32% during last tests). I also noticed a lower performance drop for 250 connections now (3~4%) compared to the 1st time (9%). I compiled the main results on tables here: http://michael.otacoo.com/postgresql-2/postgres-9-4-devel-mvcc-catalog-access-take-2-2/ The results of last time are also available here: http://michael.otacoo.com/postgresql-2/postgres-9-4-devel-mvcc-catalog-access-2/ Regards, -- Michael
Re: [HACKERS] PostgreSQL Process memory architecture
No matter how I try to redesign the schema the indexes consume large amount of memory, About 8KB per index. Is there a way to invalidated this cache? Is there a way to limit the amount of memory and use some kind of LRU/LFU algorithm to clean old cache? -Original Message- From: Atri Sharma [mailto:atri.j...@gmail.com] Sent: Monday, May 27, 2013 17:24 To: Stephen Frost Cc: Ben Zeev, Lior; Pg Hackers Subject: Re: [HACKERS] PostgreSQL Process memory architecture We may still be able to do better than what we're doing today, but I'm still suspicious that you're going to run into other issues with having 500 indexes on a table anyway. +1. I am suspicious that the large number of indexes is the problem here,even if the problem is not with book keeping associated with those indexes. Regards, Atri -- Regards, Atri l'apprenant -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
