Re: [HACKERS] A note about hash-based catcache invalidations
On 17.08.2011 00:17, Tom Lane wrote: I'm looking into the idea I mentioned earlier: All is not entirely lost, however: there's still some possible performance benefit to be gained here, if we go to the scheme of identifying victim catcache entries by hashvalue only. Currently, each heap_update in a cached catalog has to issue two sinval messages (per cache!): one against the old TID and one against the new TID. We'd be able to reduce that to one message in the common case where the hashvalue remains the same because the cache key columns didn't change. Removing the tuple ID from sinval messages turns out to have slightly wider impact than I thought at first, because the current coding passes those to callback functions registered with CacheRegisterSyscacheCallback, and there are a lot of 'em. However, only one of them seems to be doing anything with the tuplePtr argument, namely PlanCacheFuncCallback. We can make it work with the hash value instead, which will be about as good as what we're doing now. Any objections to that plan? A callback might be using the tuple ID in a way that fails if VACUUM FULL moves the tuple, so I think we *have* to change it. (as you did already) -- Heikki Linnakangas 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] synchronized snapshots
On tis, 2011-08-16 at 20:35 -0400, Tom Lane wrote: In fact, now that I think about it, setting the transaction snapshot from a utility statement would be functionally useful because then you could take locks beforehand. Another issue is that in some client interfaces, BEGIN and COMMIT are hidden behind API calls, which cannot easily be changed or equipped with new parameters. So in order to have this functionality available through those interfaces, we'd need a separately callable command. -- 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] WIP: Fast GiST index build
On Tue, Aug 16, 2011 at 11:15 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 16.08.2011 22:10, Heikki Linnakangas wrote: Here's an version of the patch with a bunch of minor changes: And here it really is, this time with an attachment... Thanks a lot. I'm going to start rerunning the tests now. -- With best regards, Alexander Korotkov.
Re: [HACKERS] A note about hash-based catcache invalidations
On Tue, Aug 16, 2011 at 10:17 PM, Tom Lane t...@sss.pgh.pa.us wrote: Any objections to that plan? None at all, but some questions. This overhaul of the cache mechanism has been extensive, so you're now very well placed to answer related questions. As you know, I've been trying to reduce the lock strength of some DDL operations. When that was last discussed there were two options. The first was to re-write SnapshotNow, which in my opinion is necessary but solves only part of the problem. I proposed explicit locking around catalog access, which would affect the cache path/code. I don't like that, but I don't see another way. From where you are now, do have any insight about how to tackle the locking problem? Thanks. -- 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] Backup's from standby
Thanks for your reply., @ Robert., What issue we may face if you take a backups(includes data dir + wal files) at standby without LVM snapshot? -Senthil -- View this message in context: http://postgresql.1045698.n5.nabble.com/Backup-s-from-standby-tp4688344p4706899.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.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] Enforcing that all WAL has been replayed after restoring from backup
On Wed, Aug 17, 2011 at 5:49 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Hmm, this behaves slightly differently, if you first try to start the restored server without recovery.conf, stop recovery, and restart it after adding recovery.conf. But I guess that's not a big deal, the check is simply skipped in that case, which is what always happens without this patch anyway. Oh, I forgot to consider that case. Yeah, I agree with you. Committed this to 9.1, Thanks a lot! but kept master as it was. So, in master, we should change pg_controldata.c and pg_resetxlog.c for new pg_control field backupEndRequired? (sorry for the delay, I wanted to fix the bogus comment as soon as I saw it, but needed some time to ponder the rest of the patch) NM. Thanks! Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Enforcing that all WAL has been replayed after restoring from backup
On 17.08.2011 12:26, Fujii Masao wrote: So, in master, we should change pg_controldata.c and pg_resetxlog.c for new pg_control field backupEndRequired? Ah, good catch! Fixed. -- Heikki Linnakangas 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] Caching Python modules
hello … i have just fallen over a nasty problem (maybe missing feature) with PL/Pythonu … consider: -- add a document to the corpus CREATE OR REPLACE FUNCTION textprocess.add_to_corpus(lang text, t text) RETURNS float4 AS $$ from SecondCorpus import SecondCorpus from SecondDocument import SecondDocument i am doing some intense text mining here. the problem is: is it possible to cache those imported modules from function to function call. GD works nicely for variables but can this actually be done with imported modules as well? the import takes around 95% of the total time so it is definitely something which should go away somehow. i have checked the docs but i am not more clever now. many thanks, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria 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
Re: [HACKERS] Caching Python modules
On 17/08/11 14:09, PostgreSQL - Hans-Jürgen Schönig wrote: CREATE OR REPLACE FUNCTION textprocess.add_to_corpus(lang text, t text) RETURNS float4 AS $$ from SecondCorpus import SecondCorpus from SecondDocument import SecondDocument i am doing some intense text mining here. the problem is: is it possible to cache those imported modules from function to function call. GD works nicely for variables but can this actually be done with imported modules as well? the import takes around 95% of the total time so it is definitely something which should go away somehow. i have checked the docs but i am not more clever now. After a module is imported in a backend, it stays in the interpreter's sys.modules dictionary and importing it again will not cause the module Python code to be executed. As long as you are using the same backend you should be able to call add_to_corpus repeatedly and the import statements should take a long time only the first time you call them. This simple test demonstrates it: $ cat /tmp/slow.py import time time.sleep(5) $ PYTHONPATH=/tmp/ bin/postgres -p 5433 -D data/ LOG: database system was shut down at 2011-08-17 14:16:18 CEST LOG: database system is ready to accept connections $ bin/psql -p 5433 postgres Timing is on. psql (9.2devel) Type help for help. postgres=# select slow(); slow -- (1 row) Time: 5032.835 ms postgres=# select slow(); slow -- (1 row) Time: 1.051 ms Cheers, Jan -- 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] Caching Python modules
On 17/08/11 14:19, Jan Urbański wrote: On 17/08/11 14:09, PostgreSQL - Hans-Jürgen Schönig wrote: CREATE OR REPLACE FUNCTION textprocess.add_to_corpus(lang text, t text) RETURNS float4 AS $$ from SecondCorpus import SecondCorpus from SecondDocument import SecondDocument i am doing some intense text mining here. the problem is: is it possible to cache those imported modules from function to function call. GD works nicely for variables but can this actually be done with imported modules as well? the import takes around 95% of the total time so it is definitely something which should go away somehow. i have checked the docs but i am not more clever now. After a module is imported in a backend, it stays in the interpreter's sys.modules dictionary and importing it again will not cause the module Python code to be executed. As long as you are using the same backend you should be able to call add_to_corpus repeatedly and the import statements should take a long time only the first time you call them. This simple test demonstrates it: [example missing the slow() function code] Oops, forgot to show the CREATE statement of the test function: postgres=# create or replace function slow() returns void language plpythonu as $$ import slow $$; Jan -- 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] Backup's from standby
On Wed, Aug 17, 2011 at 1:39 AM, senthilnathan senthilnatha...@gmail.com wrote: Thanks for your reply., @ Robert., What issue we may face if you take a backups(includes data dir + wal files) at standby without LVM snapshot? The backup might be corrupted in arbitrary ways. -- 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] Online base backup from the hot-standby
On Wed, Aug 17, 2011 at 6:19 AM, Fujii Masao masao.fu...@gmail.com wrote: 2011/8/17 Jun Ishiduka ishizuka@po.ntts.co.jp: I see in xlog.h XLR_BKP_REMOVABLE, the comment above it says that this flag is used to indicate that the archiver can compress the full page blocks to non-full page blocks. I am not familiar with where in the code this actually happens but will this cause issues if the first standby is processing WAL files from the archive? I confirmed the flag in xlog.c, so I seemed to only insert it in XLogInsert(). I consider whether it is available. That flag is not available to check whether full-page writing was skipped or not. Because it's in full-page data, not non-full-page one. The straightforward approach to address the problem you raised is to log the change of full_page_writes on the master. Since such a WAL record is also replicated to the standby, the standby can know whether full_page_writes is enabled or not in the master, from the WAL record. If it's disabled, pg_start_backup() in the standby should emit an error and refuse standby-only backup. If the WAL record indicating that full_page_writes was disabled on the master arrives during standby-only backup, the standby should cancel the backup. Seems like something we could add to XLOG_PARAMETER_CHANGE fairly easily. -- 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] Caching Python modules
On Aug 17, 2011, at 2:19 PM, Jan Urbański wrote: On 17/08/11 14:09, PostgreSQL - Hans-Jürgen Schönig wrote: CREATE OR REPLACE FUNCTION textprocess.add_to_corpus(lang text, t text) RETURNS float4 AS $$ from SecondCorpus import SecondCorpus from SecondDocument import SecondDocument i am doing some intense text mining here. the problem is: is it possible to cache those imported modules from function to function call. GD works nicely for variables but can this actually be done with imported modules as well? the import takes around 95% of the total time so it is definitely something which should go away somehow. i have checked the docs but i am not more clever now. After a module is imported in a backend, it stays in the interpreter's sys.modules dictionary and importing it again will not cause the module Python code to be executed. As long as you are using the same backend you should be able to call add_to_corpus repeatedly and the import statements should take a long time only the first time you call them. This simple test demonstrates it: $ cat /tmp/slow.py import time time.sleep(5) $ PYTHONPATH=/tmp/ bin/postgres -p 5433 -D data/ LOG: database system was shut down at 2011-08-17 14:16:18 CEST LOG: database system is ready to accept connections $ bin/psql -p 5433 postgres Timing is on. psql (9.2devel) Type help for help. postgres=# select slow(); slow -- (1 row) Time: 5032.835 ms postgres=# select slow(); slow -- (1 row) Time: 1.051 ms Cheers, Jan hello jan … the code is actually like this … the first function is called once per backend. it compiles some fairly fat in memory stuff … this takes around 2 secs or so … but this is fine and not an issue. -- setup the environment CREATE OR REPLACE FUNCTION textprocess.setup_sentiment(pypath text, lang text) RETURNS void AS $$ import sys sys.path.append(pypath) sys.path.append(pypath + /external) from SecondCorpus import SecondCorpus import const GD['path_to_classes'] = pypath GD['corpus'] = SecondCorpus(lang) GD['lang'] = lang return; $$ LANGUAGE 'plpythonu' STABLE; this is called more frequently ... -- add a document to the corpus CREATE OR REPLACE FUNCTION textprocess.add_to_corpus(lang text, t text) RETURNS float4 AS $$ from SecondCorpus import SecondCorpus from SecondDocument import SecondDocument doc1 = SecondDocument(GD['corpus'].senti_provider, lang, t) doc1.create_sentences() GD['corpus'].add_document(doc1) GD['corpus'].process() return doc1.total_score $$ LANGUAGE 'plpythonu' STABLE; the point here actually is: if i use the classes in a normal python command line program this routine does not look like an issue creating the document object and doing the magic in there is not a problem actually … on the SQL side this is already fairly heavy for some reason ... funcid | schemaname |funcname | calls | total_time | self_time | ?column? +-+-+---++---+-- 235287 | textprocess | setup_sentiment |54 | 100166 |100166 | 1854 235288 | textprocess | add_to_corpus | 996 | 438909 |438909 | 440 looks like some afternoon with some more low level tools :(. many thanks, hans -- Cybertec Schönig Schönig GmbH Gröhrmühlgasse 26 A-2700 Wiener Neustadt, Austria 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] rc1 or beta4?
The current plan (or, the last one I recall) is to push another 9.1 release tomorrow, for Monday release. Are we going with beta4 or rc1? -- 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] rc1 or beta4?
On Wed, 2011-08-17 at 14:00 +0100, Dave Page wrote: Are we going with beta4 or rc1? RC1: http://archives.postgresql.org/message-id/19869.1312298...@sss.pgh.pa.us Regards, -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] rc1 or beta4?
2011/8/17 Devrim GÜNDÜZ dev...@gunduz.org: On Wed, 2011-08-17 at 14:00 +0100, Dave Page wrote: Are we going with beta4 or rc1? RC1: http://archives.postgresql.org/message-id/19869.1312298...@sss.pgh.pa.us In Tom's final email to the -core thread he mentions I see now that he did say RC1. I thought we were voting on the date though (not that I have a problem with it being RC1). -- 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] Online base backup from the hot-standby
On Wed, Aug 17, 2011 at 9:40 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 17, 2011 at 6:19 AM, Fujii Masao masao.fu...@gmail.com wrote: The straightforward approach to address the problem you raised is to log the change of full_page_writes on the master. Since such a WAL record is also replicated to the standby, the standby can know whether full_page_writes is enabled or not in the master, from the WAL record. If it's disabled, pg_start_backup() in the standby should emit an error and refuse standby-only backup. If the WAL record indicating that full_page_writes was disabled on the master arrives during standby-only backup, the standby should cancel the backup. Seems like something we could add to XLOG_PARAMETER_CHANGE fairly easily. I'm afraid it's not so easy. Because since fpw can be changed by SIGHUP, it's not easy to ensure that logging the change of fpw must happen ahead of the actual behavior change by that. Probably we need to make the backend which detects the change of fpw first log that. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rc1 or beta4?
Dave Page dp...@pgadmin.org writes: Are we going with beta4 or rc1? In Tom's final email to the -core thread he mentions I see now that he did say RC1. I thought we were voting on the date though (not that I have a problem with it being RC1). Well, if this one's not ready to be an RC then I think we can forget about pushing out a final during September ... 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] plpython crash
On 17/08/11 11:40, Jan Urbański wrote: On 16/08/11 19:12, Jan Urbański wrote: On 16/08/11 19:07, Jean-Baptiste Quenot wrote: [plpython is buggy] I'll have a patch ready soon. Here are two patches that fix two separate bugs that you found simultaneously. Because they're actually separate issues, it turned out fixing them was a bit more tricky than I expected (fixing one was unmasking the other one etc). Thanks for the report! Jan From 3c0bf7519cad735160d9d222d6f86f84987b38b5 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Urba=C5=84ski?= wulc...@wulczer.org Date: Wed, 17 Aug 2011 16:07:54 +0200 Subject: [PATCH 2/2] Guard against return type changing in PL/Python functions. Functions cache their I/O routines and in case their return type is composite, a change of the underlying type can cause the cache to become invalid. PL/Python was already checking for composite type changes for input arguments, now the check is extended to cover the return type as well. Per bug report from Jean-Baptiste Quenot. --- src/pl/plpython/expected/plpython_record.out | 21 ++ src/pl/plpython/plpython.c | 93 ++--- src/pl/plpython/sql/plpython_record.sql | 15 3 files changed, 103 insertions(+), 26 deletions(-) diff --git a/src/pl/plpython/expected/plpython_record.out b/src/pl/plpython/expected/plpython_record.out index 7c60089..0bcc46c 100644 --- a/src/pl/plpython/expected/plpython_record.out +++ b/src/pl/plpython/expected/plpython_record.out @@ -308,6 +308,27 @@ SELECT * FROM test_inout_params('test_in'); test_in_inout (1 row) +-- try changing the return types and call functions again +ALTER TABLE table_record DROP COLUMN first; +ALTER TABLE table_record DROP COLUMN second; +ALTER TABLE table_record ADD COLUMN first text; +ALTER TABLE table_record ADD COLUMN second int4; +SELECT * FROM test_table_record_as('obj', 'one', 1, false); + first | second +---+ + one | 1 +(1 row) + +ALTER TYPE type_record DROP ATTRIBUTE first; +ALTER TYPE type_record DROP ATTRIBUTE second; +ALTER TYPE type_record ADD ATTRIBUTE first text; +ALTER TYPE type_record ADD ATTRIBUTE second int4; +SELECT * FROM test_type_record_as('obj', 'one', 1, false); + first | second +---+ + one | 1 +(1 row) + -- errors cases CREATE FUNCTION test_type_record_error1() RETURNS type_record AS $$ return { 'first': 'first' } diff --git a/src/pl/plpython/plpython.c b/src/pl/plpython/plpython.c index 90d3c47..a254ffa 100644 --- a/src/pl/plpython/plpython.c +++ b/src/pl/plpython/plpython.c @@ -1489,6 +1489,42 @@ PLy_function_delete_args(PLyProcedure *proc) PyDict_DelItemString(proc-globals, proc-argnames[i]); } +static bool +PLy_procedure_argument_valid(PLyTypeInfo *arg) +{ + Oid relid; + HeapTuple relTup; + bool valid; + + /* Only check input arguments that are composite */ + if (arg-is_rowtype != 1) { + return true; + } + + /* An uninitialised typ_relid means that we got called on an output + * argument of a function returning a unnamed record type */ + if (!OidIsValid(arg-typ_relid)) { + return true; + } + + Assert(TransactionIdIsValid(arg-typrel_xmin)); + Assert(ItemPointerIsValid(arg-typrel_tid)); + + /* Get the pg_class tuple for the argument type */ + relid = arg-typ_relid; + relTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(relTup)) + elog(ERROR, cache lookup failed for relation %u, relid); + + /* If it has changed, the function is not valid */ + valid = (arg-typrel_xmin == HeapTupleHeaderGetXmin(relTup-t_data) + ItemPointerEquals(arg-typrel_tid, relTup-t_self)); + + ReleaseSysCache(relTup); + + return valid; +} + /* * Decide whether a cached PLyProcedure struct is still valid */ @@ -1509,33 +1545,16 @@ PLy_procedure_valid(PLyProcedure *proc, HeapTuple procTup) /* If there are composite input arguments, they might have changed */ for (i = 0; i proc-nargs; i++) { - Oid relid; - HeapTuple relTup; - /* Short-circuit on first changed argument */ if (!valid) break; - /* Only check input arguments that are composite */ - if (proc-args[i].is_rowtype != 1) - continue; - - Assert(OidIsValid(proc-args[i].typ_relid)); - Assert(TransactionIdIsValid(proc-args[i].typrel_xmin)); - Assert(ItemPointerIsValid(proc-args[i].typrel_tid)); - - /* Get the pg_class tuple for the argument type */ - relid = proc-args[i].typ_relid; - relTup = SearchSysCache1(RELOID, ObjectIdGetDatum(relid)); - if (!HeapTupleIsValid(relTup)) - elog(ERROR, cache lookup failed for relation %u, relid); - - /* If it has changed, the function is not valid */ - if (!(proc-args[i].typrel_xmin == HeapTupleHeaderGetXmin(relTup-t_data) - ItemPointerEquals(proc-args[i].typrel_tid, relTup-t_self))) - valid = false; + valid = PLy_procedure_argument_valid(proc-args[i]); + } - ReleaseSysCache(relTup); + /* if the output argument is composite, it might have changed */ + if (valid) { + valid =
Re: [HACKERS] rc1 or beta4?
Hello, Dave. You wrote: DP The current plan (or, the last one I recall) is to push another 9.1 DP release tomorrow, for Monday release. Are we going with beta4 or rc1? +1 for RC1 DP -- DP Dave Page DP Blog: http://pgsnake.blogspot.com DP Twitter: @pgsnake DP EnterpriseDB UK: http://www.enterprisedb.com DP The Enterprise PostgreSQL Company -- With best wishes, Pavel mailto:pa...@gf.microolap.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A note about hash-based catcache invalidations
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: A callback might be using the tuple ID in a way that fails if VACUUM FULL moves the tuple, so I think we *have* to change it. (as you did already) Yeah, I thought about that too. As things stand in 9.0 and 9.1, there's at least a theoretical possibility of this: 1. Process A prepares a plan that includes an inline'd copy of a SQL function. It labels the plan with the function's pg_proc TID. 2. Process B executes VACUUM FULL pg_proc, moving the SQL function's tuple to a different TID. 3. Process C modifies the SQL function via CREATE OR REPLACE FUNCTION, and sends out an inval against the new TID. 4. Process A doesn't invalidate its cached plan because it thinks the TID is for some other function; so it continues to use the obsolete version of the function. The only way I can see to fix that is to back-patch the last set of changes I committed yesterday. I think that's entirely unworkable for 9.0, because of the risk of breaking third-party code that registers syscache callbacks. Even in 9.1 it seems a bit late to be changing that API, so I'm thinking we should leave it alone. The odds of anyone actually getting burnt in the field by the above scenario seem lower than the odds of causing problems with a late API change. 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] Online base backup from the hot-standby
On Wed, Aug 17, 2011 at 9:53 AM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Aug 17, 2011 at 9:40 PM, Robert Haas robertmh...@gmail.com wrote: On Wed, Aug 17, 2011 at 6:19 AM, Fujii Masao masao.fu...@gmail.com wrote: The straightforward approach to address the problem you raised is to log the change of full_page_writes on the master. Since such a WAL record is also replicated to the standby, the standby can know whether full_page_writes is enabled or not in the master, from the WAL record. If it's disabled, pg_start_backup() in the standby should emit an error and refuse standby-only backup. If the WAL record indicating that full_page_writes was disabled on the master arrives during standby-only backup, the standby should cancel the backup. Seems like something we could add to XLOG_PARAMETER_CHANGE fairly easily. I'm afraid it's not so easy. Because since fpw can be changed by SIGHUP, it's not easy to ensure that logging the change of fpw must happen ahead of the actual behavior change by that. Probably we need to make the backend which detects the change of fpw first log that. Ugh, you're right. But then you might have problems if the state changes again before all backends have picked up the previous change. What I've thought about before is making one backend (say, bgwriter) store its latest value in shared memory, protected by some lock that would already be held at the time the value is needed. Everyone else uses the shared memory copy instead of relying on their local value. -- 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] rc1 or beta4?
On 17/08/11 15:00, Dave Page wrote: The current plan (or, the last one I recall) is to push another 9.1 release tomorrow, for Monday release. Are we going with beta4 or rc1? Sorry to butt in, but it would probably be good to include fixes for the two segfault plpython bugs[1] before wrapping up the release. Cheers, Jan [1] http://archives.postgresql.org/message-id/4e4bcd52.90...@wulczer.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rc1 or beta4?
On 17 August 2011 16:47, Jan Urbański wulc...@wulczer.org wrote: On 17/08/11 15:00, Dave Page wrote: The current plan (or, the last one I recall) is to push another 9.1 release tomorrow, for Monday release. Are we going with beta4 or rc1? Sorry to butt in, but it would probably be good to include fixes for the two segfault plpython bugs[1] before wrapping up the release. It's not listed as a beta-blocker yet. I take it that it should? -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] rc1 or beta4?
On Wed, Aug 17, 2011 at 4:47 PM, Jan Urbański wulc...@wulczer.org wrote: On 17/08/11 15:00, Dave Page wrote: The current plan (or, the last one I recall) is to push another 9.1 release tomorrow, for Monday release. Are we going with beta4 or rc1? Sorry to butt in, but it would probably be good to include fixes for the two segfault plpython bugs[1] before wrapping up the release. And Ashesh's fix for building against ActiveState Python 3.2: http://archives.postgresql.org/pgsql-hackers/2011-08/msg00836.php. Care to review that one? :-) -- 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] rc1 or beta4?
On 17/08/11 17:50, Thom Brown wrote: On 17 August 2011 16:47, Jan Urbański wulc...@wulczer.org wrote: On 17/08/11 15:00, Dave Page wrote: The current plan (or, the last one I recall) is to push another 9.1 release tomorrow, for Monday release. Are we going with beta4 or rc1? Sorry to butt in, but it would probably be good to include fixes for the two segfault plpython bugs[1] before wrapping up the release. It's not listed as a beta-blocker yet. I take it that it should? Oh, in the wiki? I don't know, it is a segfault-causing bug, but all I wanted was to draw some attention in case the people wrapping the release missed that thread. Jan -- 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/Perl Returned Array
On 08/12/2011 09:17 PM, Alex Hunsaker wrote: [empty arrays returned are not handled correctly] Anyway, the attached patch fixes it for me. That is when we don't have an array state, just return an empty array. (Also adds some additional comments) Applied, thanks. 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] rc1 or beta4?
On 17 August 2011 16:56, Jan Urbański wulc...@wulczer.org wrote: On 17/08/11 17:50, Thom Brown wrote: On 17 August 2011 16:47, Jan Urbański wulc...@wulczer.org wrote: On 17/08/11 15:00, Dave Page wrote: The current plan (or, the last one I recall) is to push another 9.1 release tomorrow, for Monday release. Are we going with beta4 or rc1? Sorry to butt in, but it would probably be good to include fixes for the two segfault plpython bugs[1] before wrapping up the release. It's not listed as a beta-blocker yet. I take it that it should? Oh, in the wiki? I don't know, it is a segfault-causing bug, but all I wanted was to draw some attention in case the people wrapping the release missed that thread. It was my understanding that the only things which can prevent a new beta or release candidate are listed on the wiki ( http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items). There's only one item on the list now, and I think even that has probably been fixed. If it's not on there, I guess it hasn't yet been considered to be something which can block a release. Since it's not even listed as a non-blocker either, I don't think it's been reviewed in this context. -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
[HACKERS] Re: [COMMITTERS] pgsql: In pg_upgrade, avoid dumping orphaned temporary tables. This ma
On Wed, Aug 17, 2011 at 05:12:49PM +0100, Thom Brown wrote: On 17 August 2011 17:02, David Fetter da...@fetter.org wrote: On Wed, Aug 17, 2011 at 04:40:26PM +0100, Thom Brown wrote: On 17 August 2011 06:13, David Fetter da...@fetter.org wrote: On Tue, Aug 16, 2011 at 10:17:21PM -0400, Bruce Momjian wrote: David Fetter wrote: Any way to put David Byrne's name in here? He reported the bug and sent a proposed fix. Do we report the reporter's name in commit messages? I can do that if others are doing it. I don't think I can put it in now. He didn't just report the bug. He sent patches to fix it, even if you didn't base yours off his, so here's yours truly agitating to get him credit :) Can this now be removed as a beta-blocker? I'm missing some context. Is there a URL associated with this blockage? Sure, it's generally tracked on the wiki: http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items OK, that's probably moving to resolved issues. By the way, the wiki is now tracking bugs. We have a bug tracker, and it's that. At some point in the very, very near future, we will have to make some painful decisions about whether we continue with /ad hoc/ bug tracking such as, It's on the wiki, somewhere, if everybody remembers to put it there vs. more formal systems, the details of which are TBB*. Cheers, David. * To Be Bikeshedded -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] rc1 or beta4?
On Wed, Aug 17, 2011 at 5:16 PM, Thom Brown t...@linux.com wrote: On 17 August 2011 16:56, Jan Urbański wulc...@wulczer.org wrote: On 17/08/11 17:50, Thom Brown wrote: On 17 August 2011 16:47, Jan Urbański wulc...@wulczer.org wrote: On 17/08/11 15:00, Dave Page wrote: The current plan (or, the last one I recall) is to push another 9.1 release tomorrow, for Monday release. Are we going with beta4 or rc1? Sorry to butt in, but it would probably be good to include fixes for the two segfault plpython bugs[1] before wrapping up the release. It's not listed as a beta-blocker yet. I take it that it should? Oh, in the wiki? I don't know, it is a segfault-causing bug, but all I wanted was to draw some attention in case the people wrapping the release missed that thread. It was my understanding that the only things which can prevent a new beta or release candidate are listed on the wiki (http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items). There's only one item on the list now, and I think even that has probably been fixed. If it's not on there, I guess it hasn't yet been considered to be something which can block a release. Since it's not even listed as a non-blocker either, I don't think it's been reviewed in this context. That doesn't mean other things can't or shouldn't be fixed - just that they won't necessarily cause adjustment of the schedule to accomodate them. -- 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] rc1 or beta4?
Thom Brown t...@linux.com writes: On 17 August 2011 16:56, Jan UrbaÅski wulc...@wulczer.org wrote: On 17/08/11 17:50, Thom Brown wrote: It's not listed as a beta-blocker yet. I take it that it should? Oh, in the wiki? I don't know, it is a segfault-causing bug, but all I wanted was to draw some attention in case the people wrapping the release missed that thread. It was my understanding that the only things which can prevent a new beta or release candidate are listed on the wiki ( http://wiki.postgresql.org/wiki/PostgreSQL_9.1_Open_Items). There's only one item on the list now, and I think even that has probably been fixed. If it's not on there, I guess it hasn't yet been considered to be something which can block a release. Since it's not even listed as a non-blocker either, I don't think it's been reviewed in this context. I think you're imagining a lot more structure than actually exists in this project ;-). Anybody can edit that page, and there's no necessary consequence of something being written there. It's just notes to help us keep track of issues, not something graven on stone tablets. The pg_upgrade thing is listed as a beta blocker because I put it there --- but that's just my opinion. If it had proven hard to fix we might have concluded that we wouldn't let it block a beta release. If the plpython thing is a new crash that didn't exist before 9.1, my feeling is that it's a blocker. 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] PATCH: Compiling PostgreSQL using ActiveState Python 3.2
On ons, 2011-08-17 at 18:28 +0530, Ashesh Vashi wrote: I am trying to build PostgreSQL 9.1beta3 using the ActiveState Python 3.2. It did not compile successfully. Note that building against Python 3.2 works at least on Debian, so this is not a universal problem. It appears to have to do with the stable ABI thing they introduced in Python 3.2, so it will be mainly relevant to platforms targeted by that. When I tried to figure out the exact reason for the failure, I found that: 1. 'python_configdir' variable is hardcoded, instead it should use the configuration 'LIBPL'. That looks reasonable. My Debian installation works around this by a symlink, but that's perhaps a hack they put in for this reason. 2. 'plpython' is trying get linked using '-lpython${*python_version*}', but it should be '-lpython${*python_ldversion*}'. That, on the other hand, will be a problem. get_config_vars('LDVERSION') isn't defined before Python 3.2, so this will break all previous versions. I find it a bit curious that this is necessary, because the previous coding works for me: $ python3.2 -c import distutils.sysconfig,string; print(' '.join(filter(None,distutils.sysconfig.get_config_vars('LDLIBRARY' libpython3.2mu.so $ python3.2 -c import distutils.sysconfig,string; print(' '.join(filter(None,distutils.sysconfig.get_config_vars('LDVERSION' 3.2mu So it is not in fact true that we are linking against '-lpython ${*python_version*}'. -- 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] A note about hash-based catcache invalidations
BTW, while we're thinking about this ... The plpython patch Jan just submitted reminds me that several of the PLs detect whether they have obsolete cached data by noting whether the tuple's xmin *and* TID are the same as previously seen. Unlike depending on TID alone, I think this is probably safe. It can obviously give a false positive (thinks tuple changed when it didn't) after a catalog VACUUM FULL; but an error in that direction is safe. What would be problematic is a false negative (failure to notice a real change), and I think the inclusion of the xmin in the test protects us against that. An example scenario is: 1. We cache the data, saving xmin X1 and TID T1. 2. VACUUM FULL moves the tuple to TID T2. 3. Somebody else updates the tuple, by chance moving it right back to T1. But they will assign a new xmin X2, so we will know it changed. Can anyone think of a situation this does not cover? 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] PATCH: Compiling PostgreSQL using ActiveState Python 3.2
Peter Eisentraut pete...@gmx.net writes: On ons, 2011-08-17 at 18:28 +0530, Ashesh Vashi wrote: When I tried to figure out the exact reason for the failure, I found that: 1. 'python_configdir' variable is hardcoded, instead it should use the configuration 'LIBPL'. That looks reasonable. My Debian installation works around this by a symlink, but that's perhaps a hack they put in for this reason. FWIW, all three python installations I have handy (2.7 on Fedora 14, 2.7 on OS X Lion, 2.5 on HPUX) produce the same result from either of python -c from distutils.sysconfig import get_python_lib as f; import os; print(os.path.join(f(plat_specific=1,standard_lib=1),'config')) python -c import distutils.sysconfig,string; print(' '.join(filter(None,distutils.sysconfig.get_config_vars('LIBPL' It's not immediately apparent to me why we should think that get_python_lib is less trustworthy than LIBPL; but if someone can make that case, I don't have any objection to this part of the patch. 2. 'plpython' is trying get linked using '-lpython${*python_version*}', but it should be '-lpython${*python_ldversion*}'. That, on the other hand, will be a problem. get_config_vars('LDVERSION') isn't defined before Python 3.2, so this will break all previous versions. Yes. In particular, this appears to be doing the wrong thing on my Lion installation: it changes python_libspec = -L/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/config -lpython2.7 to just python_libspec = -L/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/config -lpython and there is no libpython.dylib in that directory. The build accidentally fails to fail because there is a libpython.dylib in /usr/lib and it happens to be symlinked to the right version of python, but I hardly think we want to trust that. I'm also wondering why a patch that's supposed to enable building against python 3.2 should need to touch the old way code path. If 3.2 isn't using the new way, what exactly does? 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] A note about hash-based catcache invalidations
On Wed, Aug 17, 2011 at 1:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: BTW, while we're thinking about this ... The plpython patch Jan just submitted reminds me that several of the PLs detect whether they have obsolete cached data by noting whether the tuple's xmin *and* TID are the same as previously seen. Unlike depending on TID alone, I think this is probably safe. It can obviously give a false positive (thinks tuple changed when it didn't) after a catalog VACUUM FULL; but an error in that direction is safe. What would be problematic is a false negative (failure to notice a real change), and I think the inclusion of the xmin in the test protects us against that. An example scenario is: 1. We cache the data, saving xmin X1 and TID T1. 2. VACUUM FULL moves the tuple to TID T2. 3. Somebody else updates the tuple, by chance moving it right back to T1. But they will assign a new xmin X2, so we will know it changed. Can anyone think of a situation this does not cover? What about this: 1. We cache the data, saving xmin X1 and TID T1. 2. VACUUM FULL moves the tuple to TID T2 but stores some other tuple in TID T1. 3. If the tuple that is now at TID T1 happens to have xmin = X1, we're in trouble. -- 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] rc1 or beta4?
On 08/17/2011 09:42 AM, Tom Lane wrote: I think you're imagining a lot more structure than actually exists in this project ;-). Anybody can edit that page, and there's no necessary consequence of something being written there. It's just notes to help us keep track of issues, not something graven on stone tablets. The pg_upgrade thing is listed as a beta blocker because I put it there --- but that's just my opinion. If it had proven hard to fix we might have concluded that we wouldn't let it block a beta release. If the plpython thing is a new crash that didn't exist before 9.1, my feeling is that it's a blocker. regards, tom lane I can confirm that the bug in pg_upgrade has been fixed with Bruce's patch in commit 2411fbdac448045a23eebf4f0dbfd5790ebad720 Thanks Dave Byrne -- 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] plpython crash
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: On 16/08/11 19:07, Jean-Baptiste Quenot wrote: [plpython is buggy] Here are two patches that fix two separate bugs that you found simultaneously. Because they're actually separate issues, it turned out fixing them was a bit more tricky than I expected (fixing one was unmasking the other one etc). These look generally sane although I have some minor stylistic gripes. Will clean them up and apply in a few hours (I have to leave for an appointment shortly). 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] non-ipv6 vs hostnames
On tis, 2011-08-16 at 16:17 +0200, Magnus Hagander wrote: Well, I got this on a win64 build. It's *supposed* to have ipv6. I wonder if it breaks on windows just because there is no ipv6 address on the machine... It would mean that getaddrinfo() of ::1 failed. That seems weird. A system admin can set registry keys to disable IPv6, either partially (allowing ::1), or totally (all IPv6 addresses fail). If the system has IPv6 enabled, it's not possible for there to be no ipv6 address. There is always the link-local address of each LAN adapter. -- 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/Perl Returned Array
On Aug 17, 2011, at 9:06 AM, Andrew Dunstan wrote: [empty arrays returned are not handled correctly] Anyway, the attached patch fixes it for me. That is when we don't have an array state, just return an empty array. (Also adds some additional comments) Applied, thanks. Awesome, thanks! 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] Re: [COMMITTERS] pgsql: In pg_upgrade, avoid dumping orphaned temporary tables. This ma
Excerpts from David Fetter's message of mié ago 17 12:31:14 -0400 2011: By the way, the wiki is now tracking bugs. We have a bug tracker, and it's that. Yeah, but only during each release's last mile. Also consider that any bug found to exist on older releases is not normally added to the list of blockers. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 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] non-ipv6 vs hostnames
On ons, 2011-08-17 at 13:12 -0400, charles.mcdev...@emc.com wrote: On tis, 2011-08-16 at 16:17 +0200, Magnus Hagander wrote: Well, I got this on a win64 build. It's *supposed* to have ipv6. I wonder if it breaks on windows just because there is no ipv6 address on the machine... It would mean that getaddrinfo() of ::1 failed. That seems weird. A system admin can set registry keys to disable IPv6, either partially (allowing ::1), or totally (all IPv6 addresses fail). If the system has IPv6 enabled, it's not possible for there to be no ipv6 address. There is always the link-local address of each LAN adapter. The problem here is that the system cannot *parse* the address ::1. This should not have anything to do with which addresses exist or could exist. -- 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: Compiling PostgreSQL using ActiveState Python 3.2
On ons, 2011-08-17 at 13:20 -0400, Tom Lane wrote: FWIW, all three python installations I have handy (2.7 on Fedora 14, 2.7 on OS X Lion, 2.5 on HPUX) produce the same result from either of python -c from distutils.sysconfig import get_python_lib as f; import os; print(os.path.join(f(plat_specific=1,standard_lib=1),'config')) python -c import distutils.sysconfig,string; print(' '.join(filter(None,distutils.sysconfig.get_config_vars('LIBPL' It's not immediately apparent to me why we should think that get_python_lib is less trustworthy than LIBPL; but if someone can make that case, I don't have any objection to this part of the patch. The issue, at least for me, is that the file isn't necessarily called 'config' anymore. I have /usr/lib/python3.2/config-3.2mu because of some shared object ABI tagging system they introduced. (/usr/lib/python3.2/config is a symlink to that, as a transition measure, I guess.) LIBPL exists at least as far back as Python 2.2, so its use should be safe. 2. 'plpython' is trying get linked using '-lpython${*python_version*}', but it should be '-lpython${*python_ldversion*}'. That, on the other hand, will be a problem. get_config_vars('LDVERSION') isn't defined before Python 3.2, so this will break all previous versions. Yes. In particular, this appears to be doing the wrong thing on my Lion installation: it changes python_libspec = -L/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/config -lpython2.7 to just python_libspec = -L/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/config -lpython and there is no libpython.dylib in that directory. The build accidentally fails to fail because there is a libpython.dylib in /usr/lib and it happens to be symlinked to the right version of python, but I hardly think we want to trust that. Yes, because get_config_vars('LDVERSION') doesn't exist in that version. In theory, it would return '2.7', so everything would fit back together, but LDVERSION doesn't exist before 3.2. I'm also wondering why a patch that's supposed to enable building against python 3.2 should need to touch the old way code path. If 3.2 isn't using the new way, what exactly does? Analogously to the point above, the result on my system should be -L something -lpython3.2mu And that's what I get. The claim is that on the ActiveState installation, this doesn't work out, but we need to see some details here, I guess. -- 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: Compiling PostgreSQL using ActiveState Python 3.2
Peter Eisentraut pete...@gmx.net writes: On ons, 2011-08-17 at 13:20 -0400, Tom Lane wrote: It's not immediately apparent to me why we should think that get_python_lib is less trustworthy than LIBPL; but if someone can make that case, I don't have any objection to this part of the patch. The issue, at least for me, is that the file isn't necessarily called 'config' anymore. I have /usr/lib/python3.2/config-3.2mu Ah, I see. LIBPL exists at least as far back as Python 2.2, so its use should be safe. Yeah, that part of the patch seems sane then. Yes, because get_config_vars('LDVERSION') doesn't exist in that version. In theory, it would return '2.7', so everything would fit back together, but LDVERSION doesn't exist before 3.2. Could we have the code use 'LDVERSION' if it gets a nonempty result, and otherwise fall back to the current scheme? But I guess first we need some details as to why the current scheme isn't sufficient. 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] A note about hash-based catcache invalidations
Robert Haas robertmh...@gmail.com writes: On Wed, Aug 17, 2011 at 1:10 PM, Tom Lane t...@sss.pgh.pa.us wrote: The plpython patch Jan just submitted reminds me that several of the PLs detect whether they have obsolete cached data by noting whether the tuple's xmin *and* TID are the same as previously seen. Can anyone think of a situation this does not cover? What about this: 1. We cache the data, saving xmin X1 and TID T1. 2. VACUUM FULL moves the tuple to TID T2 but stores some other tuple in TID T1. 3. If the tuple that is now at TID T1 happens to have xmin = X1, we're in trouble. No, because remember that we're also effectively demanding a match on OID (because we fetch the tuple by OID to begin with) and that the tuple be live (else we won't fetch it at all). There should not be another live tuple with the same OID that vacuum could move to T1 --- if there is, we've got worse problems than a broken caching check. 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] rc1 or beta4?
Dave Byrne dby...@mdb.com writes: I can confirm that the bug in pg_upgrade has been fixed with Bruce's patch in commit 2411fbdac448045a23eebf4f0dbfd5790ebad720 Thanks, I marked it resolved on the wiki page. 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/Perl Returned Array
On Wed, Aug 17, 2011 at 10:06, Andrew Dunstan and...@dunslane.net wrote: On 08/12/2011 09:17 PM, Alex Hunsaker wrote: [empty arrays returned are not handled correctly] Anyway, the attached patch fixes it for me. That is when we don't have an array state, just return an empty array. (Also adds some additional comments) Applied, thanks. Thanks for picking this up. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FATAL: ReleaseSavepoint: unexpected state STARTED
Hello I tried reporting the following bug via web form, it somerhow got lost (it is not in pgsql-bugs archives, it was #6157 I believe). Anyway, here it is: psql -c 'release q; prepare q(int) as select 1' FATAL: ReleaseSavepoint: unexpected state STARTED server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. connection to server was lost The message is from 8.4.2, but the bug is in 9.0.4 too . Greetings Marcin Mańk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Displaying accumulated autovacuum cost
Attached is a patch that tracks and displays the accumulated cost when autovacuum is running. Code by Noah Misch and myself. I hope this idea will bring a formal process to vacuum tuning, which is currently too hard to do. I was about to add without... to that, but I then realized it needs no disclaimer; it's just too hard, period. Vacuum issues are enemy #1 at all the terabyte scale customer sites I've been fighting with lately. The patch updates the command string just before the workers sleep to show how much work they've done so far. And at the end, it adds a few new lines to the information written to the logs, when the autovacuum is notable enough to be logged at all. The overhead it adds is at most a few integer operations per buffer processed and a slower title string update once per sleep. It's trivial compared to both the vacuum itself, and to the instrumentation's value to sites with vacuum issues. To demonstrate the patch in action, here's a test case using a 6.4GB pgbench_accounts table: $ createdb pgbench $ pgbench -i -s 500 pgbench $ psql -d pgbench -c select pg_relation_size('pgbench_accounts'); pg_relation_size -- 6714761216 $ psql -d pgbench -c select relname,relpages from pg_class where relname='pgbench_accounts'; relname | relpages --+-- pgbench_accounts | 819673 $psql -d pgbench -c delete from pgbench_accounts where aid2000 You can see the new information in the command string with ps and grep: $ while [ 1 ] ; do (ps -eaf | grep [a]utovacuum worker sleep 60) ; done gsmith2687 17718 0 15:44 ?00:00:00 postgres: autovacuum worker process h=19 m=14196 d=14185 ... gsmith2687 17718 0 15:44 ?00:00:09 postgres: autovacuum worker process h=182701 m=301515 d=321345 ... gsmith2687 17718 1 15:44 ?00:00:23 postgres: autovacuum worker process h=740359 m=679987 d=617559 ... That's accumulated hit/miss/dirty counts, the raw numbers. When the autovacuum is finished, those totals appear as a new line in the log entry: LOG: automatic vacuum of table pgbench.public.pgbench_accounts: index scans: 1 pages: 0 removed, 819673 remain tuples: 1999 removed, 3022 remain buffer usage: 809537 hits, 749340 misses, 686660 dirtied system usage: CPU 5.70s/19.73u sec elapsed 2211.60 sec To check if this makes sense, we need the relevant parameters at the time, which were the defaults (I only tweaked some basic config bits here, including shared_buffers=400MB so a bit more was cached): vacuum_cost_page_hit = 1 # 0-1 credits vacuum_cost_page_miss = 10 # 0-1 credits vacuum_cost_page_dirty = 20# 0-1 credits vacuum_cost_limit = 200# 1-1 credits autovacuum_vacuum_cost_delay = 20ms Every 20ms equals 50 times/second. That means the cost accumulation should be 200 * 50 = 1 / second, or 600K/minute. That's how fast the cost should be increasing here. Given a runtime of 2211.60 seconds, that's a total estimated cost of 2209.15 * 1 = 22,091,500. Now we check that against the totals printed at the end of the vacuum: 1 * 809537 hits=809,537 10 * 749340 misses=7,493,400 20 * 686607 dirtied=13,732,140 And that gives a directly computed total of 22,035,077. Close enough to show this is working as expected. And how I computed all that should give you an idea how you might use these numbers to extract other useful statistics, if you'd like to tune the balance of various cost_page_* parameters as one example. I have no idea how anyone could ever set those relative to one another without this data, it would take epic guessing skills. What else can do you do with this data? -Figure out if the VACUUM is still making progress when it appears stuck -Estimate how long it will take to finish, based on current progress and whatever total cost was logged last time VACUUM ran against this relation. -Compute approximate hit rate on the read side. OS caching issues and the ring buffer are obviously a problem with that, this isn't too valuable. -Can see the cost split when multiple vacuums are running. This problem is why sites can't just use total time to vacuum as a useful proxy to estimate how long one will take to run. -Easy to track the read/write ratio -Directly measure the write rate That last one is I think the part people are most perplexed by right now, and this makes it trivial. How do you turn all these cost figures into real-world read/write rates? It's been hard to do. Now, you can take a bunch of samples of the data at 1 minute intervals, like my little ps | grep example above does. The delta in the dirty= column is how much was written per minute, in units of 8K (usually) buffers. Multiply that by 8192/(60*1024*1024), and you get MB/s out of there. I collected that data for a cleanup run of the pgbench_accounts damage done above, CSV file with all the
Re: [HACKERS] plpython crash
=?UTF-8?B?SmFuIFVyYmHFhHNraQ==?= wulc...@wulczer.org writes: Here are two patches that fix two separate bugs that you found simultaneously. Because they're actually separate issues, it turned out fixing them was a bit more tricky than I expected (fixing one was unmasking the other one etc). Applied with one non-cosmetic change: I got rid of the test on TransactionIdIsValid(arg-typrel_xmin) in PLy_input_tuple_funcs, as well as where you'd copied that logic in PLy_output_tuple_funcs. AFAICS skipping the update on the xmin/tid, if we're coming through there a second time, would be simply wrong. 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] FATAL: ReleaseSavepoint: unexpected state STARTED
=?UTF-8?B?TWFyY2luIE1hxYRr?= marcin.m...@gmail.com writes: psql -c 'release q; prepare q(int) as select 1' FATAL: ReleaseSavepoint: unexpected state STARTED Can't get terribly excited about that, seeing that the statement is surely going to draw an error and abort processing the rest of the command string in any case ... 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] rc1 or beta4?
On Wed, Aug 17, 2011 at 5:33 PM, Dave Page dp...@pgadmin.org wrote: That doesn't mean other things can't or shouldn't be fixed - just that they won't necessarily cause adjustment of the schedule to accomodate them. +1 -- 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] Displaying accumulated autovacuum cost
Em 17-08-2011 18:04, Greg Smith escreveu: Attached is a patch that tracks and displays the accumulated cost when autovacuum is running. Code by Noah Misch and myself. I hope this idea will bring a formal process to vacuum tuning, which is currently too hard to do. I was about to add without... to that, but I then realized it needs no disclaimer; it's just too hard, period. Vacuum issues are enemy #1 at all the terabyte scale customer sites I've been fighting with lately. Interesting patch. I drafted a similar idea but didn't have a chance to publish it. It is a complement to the idea about autovacuum tuning [1]. Hope I will have time to post something for the next CF. And, of course, I will review this patch. The patch updates the command string just before the workers sleep to show how much work they've done so far. And at the end, it adds a few new lines to the information written to the logs, when the autovacuum is notable enough to be logged at all. The overhead it adds is at most a few integer operations per buffer processed and a slower title string update once per sleep. It's trivial compared to both the vacuum itself, and to the instrumentation's value to sites with vacuum issues. I don't like exposing this information only on title processes. It would be difficult for client apps (for example, PGAdmin) to track this kind of information and it is restricted to local access. I'm not objecting to display this information in process title; I'm just saying that that information should be exposed in functions (say pg_stat_get_vacuum_[hit|miss|dirty]) too. I'm not sure about adding this information to incremental counters but that would be useful to trace a vacuum work pattern. [1] http://archives.postgresql.org/pgsql-hackers/2011-06/msg00678.php -- Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/ PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento -- 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] Displaying accumulated autovacuum cost
On 08/17/2011 07:42 PM, Euler Taveira de Oliveira wrote: I don't like exposing this information only on title processes. It would be difficult for client apps (for example, PGAdmin) to track this kind of information and it is restricted to local access. I'm not objecting to display this information in process title; I'm just saying that that information should be exposed in functions (say pg_stat_get_vacuum_[hit|miss|dirty]) too. I tend to build the simplest possible thing that is useful enough to work. The data is getting stored and shown now, where it wasn't before. If it's possible to expose that in additional ways later too, great. The big step up for this information is to go from unobtainable to obtainable. I'd prefer not to add a quest for easily obtainable to the requirements until that big jump is made, for fear it will cause nothing to get delivered. -- Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us -- 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] Online base backup from the hot-standby
On Thu, Aug 18, 2011 at 12:09 AM, Robert Haas robertmh...@gmail.com wrote: Ugh, you're right. But then you might have problems if the state changes again before all backends have picked up the previous change. Right. What I've thought about before is making one backend (say, bgwriter) store its latest value in shared memory, protected by some lock that would already be held at the time the value is needed. Everyone else uses the shared memory copy instead of relying on their local value. Sounds reasonable. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Online base backup from the hot-standby
2011/8/5 Jun Ishiduka ishizuka@po.ntts.co.jp: * Procedure 1. Call pg_start_backup('x') on the standby. 2. Take a backup of the data dir. 3. Call pg_stop_backup() on the standby. 4. Copy the control file on the standby to the backup. 5. Check whether the control file is status during hot standby with pg_controldata. - If the standby promote between 3. and 4., the backup can not recovery. - pg_control is that Minimum recovery ending location is equals 0/0. - backup-end record is not written. What if we do #4 before #3? The backup gets corrupted? My guess is that the backup is still valid even if we copy pg_control before executing pg_stop_backup(). Which would not require #5 because if the standby promotion happens before pg_stop_backup(), pg_stop_backup() can detect that status change and cancel the backup. #5 looks fragile. If we can get rid of it, the procedure becomes more robust, I think. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Allowing same cursor name in nested levels
On Tue, Aug 16, 2011 at 7:25 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jeevan Chalke jeevan.cha...@enterprisedb.com writes: 1. I have two functions func1 and func2. 2. func1 calls func2 3. Both has cursor with same name, say mycursor 4. Somehow I forgot closing it 5. executing func1 throws an error 'cursor mycursor already in use' Is this expected behavior??? Yes ... or at least, it's always been like that. I just mingled around the code and later appended a cursor count to the cursor name to allow same cursor name in nested levels. That would break code that expects the cursor name to be what it said it should be. It is documented that you can refer to cursors by name across multiple functions. Hmm... got it. Thanks for the clarification. regards, tom lane -- Jeevan B Chalke Senior Software Engineer, RD EnterpriseDB Corporation The Enterprise PostgreSQL Company Phone: +91 20 30589500 Website: www.enterprisedb.com EnterpriseDB Blog: http://blogs.enterprisedb.com/ Follow us on Twitter: http://www.twitter.com/enterprisedb This e-mail message (and any attachment) is intended for the use of the individual or entity to whom it is addressed. This message contains information from EnterpriseDB Corporation that may be privileged, confidential, or exempt from disclosure under applicable law. If you are not the intended recipient or authorized to receive this for the intended recipient, any use, dissemination, distribution, retention, archiving, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender immediately by reply e-mail and delete this message.
Re: [HACKERS] Online base backup from the hot-standby
* Procedure 1. Call pg_start_backup('x') on the standby. 2. Take a backup of the data dir. 3. Call pg_stop_backup() on the standby. 4. Copy the control file on the standby to the backup. 5. Check whether the control file is status during hot standby with pg_controldata. ? - If the standby promote between 3. and 4., the backup can not recovery. ? ? ?- pg_control is that Minimum recovery ending location is equals 0/0. ? ? ?- backup-end record is not written. What if we do #4 before #3? The backup gets corrupted? My guess is that the backup is still valid even if we copy pg_control before executing pg_stop_backup(). Which would not require #5 because if the standby promotion happens before pg_stop_backup(), pg_stop_backup() can detect that status change and cancel the backup. #5 looks fragile. If we can get rid of it, the procedure becomes more robust, I think. Sure, you're right. Jun Ishizuka NTT Software Corporation TEL:045-317-7018 E-Mail: ishizuka@po.ntts.co.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers