Re: [HACKERS] Small Bug in GetConflictingVirtualXIDs
On Mon, 2009-12-21 at 04:02 +0100, Andres Freund wrote: The logic behind this seems fine except in the case of dropping a database. There you very well might have a open connection without an open snapshot. Yes, you're right, thanks for the report. I re-arranged the logic there recently to reduce the number of backends that would conflict, so it looks like I broke that case when I did that. Should easily be solvable through an extra parameter for GetConflictingVirtualXIDs. Want me to prepare a patch? Much appreciated, thanks. -- Simon Riggs www.2ndQuadrant.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] alpha3 release schedule?
Simon Riggs wrote: On Sat, 2009-12-19 at 20:59 +0200, Heikki Linnakangas wrote: I put them on the TODO list at https://wiki.postgresql.org/wiki/Hot_Standby_TODO, under the must-fix category. I notice you also re-arranged other items on there, specifically the notion that starting from a shutdown checkpoint is somehow important. I didn't rearrange anything. I added that item because it was missing. Yes, it is important in my opinion. -- 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] An example of bugs for Hot Standby
Following question may be redundant. Just a confirmation. Deadlock example is catstrophic while it's rather a rare event. On the other hand, LockBufferForCleanup() can cause another problem. * One idle pin-holder backend can freeze startup process(). This problem is not catstrophic, but it seems a similar problem which StandbyAcquireAccessExclusiveLock() tries to avoid. ...Is this the problem you call general problem above ? Here is a typical scenario in which startup process freezes until the end of a certain transaction. 1. Consider a table A, which has pages with HOT chain tuples old enough to be vacuumed. 2. Xact 1 in the standby node declares a cursor for table A, fetches the page which contains the HOT chain, and becomes idle for some reason. 3. Xact 2 in the active node reads the table A and calls heap_page_prune() for HOT pruning, which create XLOG_HEAP2_CLEAN record. 4. Startup process tries to redo XLOG_HEAP2_CLEAN record, calls LockBufferForCleanup() and freezes until the Xact 1 ends. Note that with HOT pruning, we do not need VACUUM command, and most tables, which has long history of updation, can be table A. -- Hiroyuki YAMADA Kokolink Corporation yam...@kokolink.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] alpha3 release schedule?
The problem you mention here has been documented and very accessible for months and not a single person mentioned it up to now. What's more, the equivalent problem happens in the latest production version of Postgres - users can delay VACUUM endlessly in just the same way, yet I've not seen this raised as an issue in many years of using Postgres. Similarly, there are some ways that Postgres can deadlock that it need not, yet those negative behaviours are accepted and nobody is rushing to fix them, nor demanding that they should be. Few things are theoretically perfect on their first release. Sorry for annoying you, at the very first. Well, this is certainly a well-known problem, but the cursor example (or deadlock example) reveals that the problem is more severe than it was considered before, I guess. Following comments in backup.sgml(which are now replaced by the deadlock example) Waits for buffer cleanup locks do not currently result in query cancellation. Long waits are uncommon, though can happen in some cases with long running nested loop joins. ...refered only to the example where startup process should wait until the end of one query. And long waits are assumed to be uncommon. The cursor example shows, however, the waits can be as long as one transaction, and occur in usual use case. FYI, I wrote a typical freeze scenario in the mail posted in the original deadlock example thread. Then the startup process may have to wait until the end of transaction, and we can not expect when the pin-holder transaction ends. Also, you mentioned the VACCUM case of the production version, but following two problems have different impacts. * One VACUUM process freezes until the end of a certain transaction. * Startup process(and whole recovery work) freezes until the end of a certain transaction. The startup process is the last process to freeze. So I guess this problem may become must-fix. Anyway, the patch are committed and alpha 3 are to be released. Do you think this problem is must-fix for the final release ? regards, -- Hiroyuki YAMADA Kokolink Corporation yam...@kokolink.net -- 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: Proposal: Pre ordered aggregates, default ORDER BY clause for aggregates - median support
On Mon, Dec 21, 2009 at 5:48 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Information about count of rows are not detected in planner time. This have to by available in any executor's sort node. So this value will be available every time - index scan is problem. Interesting is Greg's info about O(N) algorithms. Then we can implement median as classic aggregate. ... On second hand - any internal implementation of median will be faster, then currently used techniques. Some further information now that I'm on a real keyboard. The O(n) algorithm for median of which I'm aware is Quickselect. It's based on Quicksort which makes it unsuitable for a disk-based algorithm since it would have to read every block many times. Perhaps there's some way to adapt it or there might be some other O(n) algorithm which has better i/o patterns. But in cases where the tupleset/tuplesort is still in memory it would be easy to add support for pulling out the nth element and use that in a magic median() function. It wouldn't be a classic aggregate, at least as I understand it where you also need something like O(1) space to store the state, because you definitely need access to the whole tupleset to do the quickselect. If we don't find a way to optimize this for on-disk tuplestores though then I wonder whether it's worth it. It would only help in the narrow case that you had a large enough set to see the difference between doing quickselect and quicksort but small enough to fit in workmem. I suppose that case is widening over time though as memory sizes get bigger and bigger. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Minimum perl version supported
On Sun, Dec 20, 2009 at 10:55:55PM -0500, Robert Haas wrote: On Sun, Dec 20, 2009 at 10:23 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andres Freund and...@anarazel.de writes: On Monday 21 December 2009 02:23:39 Robert Haas wrote: A more important point is whether we really need to make this dependent on Perl 5.6 or later. I dont see a platform without perl 5.6 where a new enough flex/bison is available... That argument only holds water if you are willing to follow the same rules as we use for flex/bison, ie, they are not needed to build from a source tarball. Otherwise this *is* moving the goalposts on required tool support. I believe that we have long had agreement on making the relevant files distprep targets, so this will not be an issue. Anyway, the whole thing is a silly argument anyway: we can certainly make this compatible back even as far as Perl 5.0 if need be for very little extra work. FYI Perl 5.6.0 was released in March 2000. 5.6.2 in November 2003. The general perception in the perl community is that 5.8.x is the oldest perl that's widely used and that gets regular automated testing: http://stats.cpantesters.org/pmatrix-wide.html There's very little use of 5.6.x (typically 5.6.2). There's almost no use of earlier versions. What is worth a little bit of effort to establish is exactly what version of Perl we're already depending on, so that we can document that for the benefit of future tool writers. There's no reason why this particular thing needs to be compatible further back than what is already required otherwise. I'd like to know the earliest version of perl that's supported for PL/Perl, if that's ever been determined (I've not seen it in the docs). plperl requires Safe v2.09, released in Oct 2003 and included in 5.8.1. That version, and later versions, have only been tested back to perl 5.6. I'd recommend Perl 5.6(.2) as a minimum for threads/multiplicity. Perl 5.5 had complelely different, and unsafe, code for threads and no concept of multiplicity. I'd recommend Perl 5.8(.1) as a minimum for UTF-8 databases. Perl 5.7 had a complelely different, and flawed, concept of Unicode operation. Tim. p.s. I'll test PL/Perl with perl 5.6.2 (assuming I can still build it on my system) as part of testing the PL/Perl patches I'm working on. -- 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] Initial refactoring of plperl.c [PATCH]
I've submitted this patch to the open CommitFest
https://commitfest.postgresql.org/action/patch_view?id=245
Tim.
On Wed, Nov 25, 2009 at 03:36:25PM +, Tim Bunce wrote:
Following on from my earlier draft plperl.c refactoring patch, here's a
new version that's complete (from my perspective at least).
I've started work on the enhancements to plperl I outlined on pg-general
(in the Wishlist of PL/Perl Enhancements for 8.5 thread).
I have a working implementation of those changes, plus some performance
enhancements, that I'm now re-working into a clean set of tested and
polished patches.
This patch is a first step that doesn't add any extra functionality.
It refactors the internals to make adding the extra functionality
easier (and more clearly visible).
Changes in this patch:
- Changed MULTIPLICITY check from runtime to compiletime.
No loads the large Config module.
- Changed plperl_init_interp() to return new interp
and not alter the global interp_state
- Moved plperl_safe_init() call into check_interp().
- Removed plperl_safe_init_done state variable
as interp_state now covers that role.
- Changed plperl_create_sub() to take a plperl_proc_desc argument.
- Simplified return value handling in plperl_create_sub.
- Added a test for the effect of the utf8fix function.
- Changed perl.com link in the docs to perl.org and tweaked
wording to clarify that require, not use, is what's blocked.
- Moved perl code in large multi-line C string literal macros
out to plc_*.pl files.
- Added a test2macro.pl utility to convert the plc_*.pl files to
macros in a perlchunks.h file which is #included
I'd appreciate any feedback on the patch.
Tim.
diff --git a/doc/src/sgml/plperl.sgml b/doc/src/sgml/plperl.sgml
index 49631f2..4c26561 100644
*** a/doc/src/sgml/plperl.sgml
--- b/doc/src/sgml/plperl.sgml
***
*** 14,20
para
PL/Perl is a loadable procedural language that enables you to write
productnamePostgreSQL/productname functions in the
!ulink url=http://www.perl.com;Perl programming language/ulink.
/para
para
--- 14,20
para
PL/Perl is a loadable procedural language that enables you to write
productnamePostgreSQL/productname functions in the
!ulink url=http://www.perl.org;Perl programming language/ulink.
/para
para
*** SELECT * FROM perl_set();
*** 298,304
use strict;
/programlisting
in the function body. But this only works in applicationPL/PerlU/
!functions, since literaluse/ is not a trusted operation. In
applicationPL/Perl/ functions you can instead do:
programlisting
BEGIN { strict-import(); }
--- 298,305
use strict;
/programlisting
in the function body. But this only works in applicationPL/PerlU/
!functions, since the literaluse/ triggers a literalrequire/
!which is not a trusted operation. In
applicationPL/Perl/ functions you can instead do:
programlisting
BEGIN { strict-import(); }
diff --git a/src/pl/plperl/GNUmakefile b/src/pl/plperl/GNUmakefile
index a3c3495..8989b14 100644
*** a/src/pl/plperl/GNUmakefile
--- b/src/pl/plperl/GNUmakefile
*** PSQLDIR = $(bindir)
*** 45,50
--- 45,55
include $(top_srcdir)/src/Makefile.shlib
+ plperl.o: perlchunks.h
+
+ perlchunks.h: plc_*.pl
+ $(PERL) text2macro.pl --strip='^(\#.*|\s*)$$' plc_*.pl perlchunks.htmp
+ mv perlchunks.htmp perlchunks.h
all: all-lib
*** submake:
*** 65,71
$(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X)
clean distclean maintainer-clean: clean-lib
! rm -f SPI.c $(OBJS)
rm -rf results
rm -f regression.diffs regression.out
--- 70,76
$(MAKE) -C $(top_builddir)/src/test/regress pg_regress$(X)
clean distclean maintainer-clean: clean-lib
! rm -f SPI.c $(OBJS) perlchunks.htmp perlchunks.h
rm -rf results
rm -f regression.diffs regression.out
diff --git a/src/pl/plperl/expected/plperl.out
b/src/pl/plperl/expected/plperl.out
index c8a8fdb..e9f5324 100644
*** a/src/pl/plperl/expected/plperl.out
--- b/src/pl/plperl/expected/plperl.out
*** $$ LANGUAGE plperl;
*** 555,557
--- 555,564
SELECT perl_spi_prepared_bad(4.35) as double precision;
ERROR: type does_not_exist does not exist at line 2.
CONTEXT: PL/Perl function perl_spi_prepared_bad
+ --
+ -- Test compilation of unicode regex
+ --
+ CREATE OR REPLACE FUNCTION perl_unicode_regex(text) RETURNS INTEGER AS $$
+ # see http://rt.perl.org/rt3/Ticket/Display.html?id=47576
+ return ($_[0] =~ /\x{263A}|happy/i) ? 1 : 0; # unicode smiley
+ $$ LANGUAGE plperl;
diff --git a/src/pl/plperl/plc_perlboot.pl b/src/pl/plperl/plc_perlboot.pl
index ...f4739df .
*** a/src/pl/plperl/plc_perlboot.pl
--- b/src/pl/plperl/plc_perlboot.pl
***
*** 0
Re: [HACKERS] New VACUUM FULL
On Mon, 2009-12-07 at 16:55 +0900, Itagaki Takahiro wrote: Tom Lane t...@sss.pgh.pa.us wrote: You should take those out again; if I am the committer I certainly will. Such a test will guarantee complete instability of every other regression test, and it's not worth it. I read the original comment was saying to add regression tests for database-wide vacuums. But I'll reduce the range of vacuum if they are not acceptable. The new patch contains only table-based vacuum for local tables and some of system tables to test non-INPLACE vacuum are not used for system tables. VACUUM FULL pg_am; VACUUM FULL pg_class; VACUUM FULL pg_database; Thanks for adding those additional tests. I notice that during copy_heap_data() we make no attempt to skip pages that are all visible according to the visibilitymap. It seems like it would be a substantial win to copy whole blocks if all the pre-conditions are met (I see what they are). I'm surprised to see that neither CLUSTER nor VACUUM FULL made use of this previously. I think we either need to implement that or document that vacuum will not skip all-visible pages when running VACUUM FULL. Also, I notice that if we perform new VACUUM FULL on a table it will fully re-write the table and rebuild indexes, even if it hasn't found a single row to remove. Old VACUUM FULL was substantially faster than this on tables that had nothing to remove. We aren't asking users to recode anything, so many people will be performing VACUUM FULL; as usual every night or weekend. If they do that it will result in substantially longer run times in many databases, all while holding AccessExclusiveLocks. Please can you arrange for the cluster operation to skip rebuilding indexes if the table is not reduced in size? Part of the reason why these happen is that the code hasn't been refactored much at all from its original use for cluster. There are almost zero comments to explain the additional use of this code for VACUUM, or at least to explain it still all works even when there is no index. e.g. check_index_is_clusterable() ought not to be an important routine when there is no index being clustered. I'm seeing that the code all works but that this patch isn't yet a sufficiently permanent change to the code for me to commit, though it could be soon. -- Simon Riggs www.2ndQuadrant.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] alpha3 release schedule?
On Mon, 2009-12-21 at 18:42 +0900, Hiroyuki Yamada wrote: Do you think this problem is must-fix for the final release ? We should be clear that this is a behaviour I told you about, not a shock discovery by yourself. There is no permanent freeze, just a wait, from which the Startup process wakes up at the appropriate time. There is no crash or hang as is usually implied by the word freeze. It remains to be seen whether this is a priority for usability enhancement in this release. There are other issues as well and it is doubtful that every user will be fully happy with the functionality in this release. I will work on things in the order in which I understand them to be important for the majority, given my time and budget constraints and the resolvability of the issues. When you report bugs, I say thanks. When you start agitating about already-documented restrictions and I see which other software you promote, I think you may have other motives. Regrettably that reduces the weight I give your claims, in relation to other potential users. If you genuinely care about this topic then I hope and expect that you would start thinking about improvements, or even writing some. I am already in touch with many potential users and will be engaging more widely to understand users's reactions from the Alpha release. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Proposal: Pre ordered aggregates, default ORDER BY clause for aggregates - median support
2009/12/21 Greg Stark gsst...@mit.edu: On Mon, Dec 21, 2009 at 5:48 AM, Pavel Stehule pavel.steh...@gmail.com wrote: Information about count of rows are not detected in planner time. This have to by available in any executor's sort node. So this value will be available every time - index scan is problem. Interesting is Greg's info about O(N) algorithms. Then we can implement median as classic aggregate. ... On second hand - any internal implementation of median will be faster, then currently used techniques. Some further information now that I'm on a real keyboard. The O(n) algorithm for median of which I'm aware is Quickselect. It's based on Quicksort which makes it unsuitable for a disk-based algorithm since it would have to read every block many times. Perhaps there's some way to adapt it or there might be some other O(n) algorithm which has better i/o patterns. But in cases where the tupleset/tuplesort is still in memory it would be easy to add support for pulling out the nth element and use that in a magic median() function. It wouldn't be a classic aggregate, at least as I understand it where you also need something like O(1) space to store the state, because you definitely need access to the whole tupleset to do the quickselect. I can check speed diferences on orafce's median implementation. But orafce isn't correct - it ignores work_mem limit - so it usable only for some external modules or for testing. I'll try simple median implementation based on aggregate API. Then I'll compare speed. If we don't find a way to optimize this for on-disk tuplestores though then I wonder whether it's worth it. It would only help in the narrow case that you had a large enough set to see the difference between doing quickselect and quicksort but small enough to fit in workmem. I suppose that case is widening over time though as memory sizes get bigger and bigger. Thank you I have to do same test and get more info about quickselect Pavel -- greg -- 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] using separate parameters in psql query execution
On Mon, Dec 21, 2009 at 1:03 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
2009/12/21 Robert Haas robertmh...@gmail.com:
On Mon, Nov 16, 2009 at 5:01 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hello
now - complete patch
ToDo:
* enhance a documentation (any volunteer?)
* check name for backslash command
I read through this patch tonight and I don't understand what the
point of this change is. That's something that should probably be
discussed and also incorporated into the documentation.
Do you ask about ToDo points?
I used pexec as switch. Probably better name is
parametrized-execution, send-parameters-separately or
parametrized-queries
general goal of this patch is removing issues with variables quoting
- using psql variables should be more robust and more secure.
My point is that I don't think someone new to psql (or even
experienced in psql, such as myself) has a hope of reading the
documentation for this option and understanding why they might or
might not want to use it. Even your description here, removing
issues with variable quoting is pretty vague. Maybe you need to
provide some examples of the pros and cons of using this option.
I checked second design based on enhanced syntax -
http://www.postgres.cz/index.php/Enhanced-psql#Variables_quoting . It
working too, but it needs one exec more.
Hmm, the :[foo] and :{foo} syntax looks sort of cool. But I don't
understand why it would need any more server calls.
...Robert
--
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] Minimum perl version supported
On Mon, Dec 21, 2009 at 5:45 AM, Tim Bunce tim.bu...@pobox.com wrote: FYI Perl 5.6.0 was released in March 2000. 5.6.2 in November 2003. Gosh, I feel old. I started on Perl 4.036. What is worth a little bit of effort to establish is exactly what version of Perl we're already depending on, so that we can document that for the benefit of future tool writers. There's no reason why this particular thing needs to be compatible further back than what is already required otherwise. I'd like to know the earliest version of perl that's supported for PL/Perl, if that's ever been determined (I've not seen it in the docs). plperl requires Safe v2.09, released in Oct 2003 and included in 5.8.1. That version, and later versions, have only been tested back to perl 5.6. I'd recommend Perl 5.6(.2) as a minimum for threads/multiplicity. Perl 5.5 had complelely different, and unsafe, code for threads and no concept of multiplicity. I'd recommend Perl 5.8(.1) as a minimum for UTF-8 databases. Perl 5.7 had a complelely different, and flawed, concept of Unicode operation. Tim. p.s. I'll test PL/Perl with perl 5.6.2 (assuming I can still build it on my system) as part of testing the PL/Perl patches I'm working on. It's possible that we might support building the release in general with an older version of Perl than what we support for PL/perl, but it sounds like we may have already burned this bridge if the MSVC stuff expects something newer. Maybe we should just document that 5.6 is the minimum supported version to build from source and be done with it (we should clarify what MSVC requires). If someone is running a 1990s version of Perl in 2010 when 8.5 comes out, they can always make distprep on another machine. There can't be many people who will want a brand-new PostgreSQL and an ancient Perl, I think. ...Robert -- 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] using separate parameters in psql query execution
2009/12/21 Robert Haas robertmh...@gmail.com:
On Mon, Dec 21, 2009 at 1:03 AM, Pavel Stehule pavel.steh...@gmail.com
wrote:
2009/12/21 Robert Haas robertmh...@gmail.com:
On Mon, Nov 16, 2009 at 5:01 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:
Hello
now - complete patch
ToDo:
* enhance a documentation (any volunteer?)
* check name for backslash command
I read through this patch tonight and I don't understand what the
point of this change is. That's something that should probably be
discussed and also incorporated into the documentation.
Do you ask about ToDo points?
I used pexec as switch. Probably better name is
parametrized-execution, send-parameters-separately or
parametrized-queries
general goal of this patch is removing issues with variables quoting
- using psql variables should be more robust and more secure.
My point is that I don't think someone new to psql (or even
experienced in psql, such as myself) has a hope of reading the
documentation for this option and understanding why they might or
might not want to use it. Even your description here, removing
issues with variable quoting is pretty vague. Maybe you need to
provide some examples of the pros and cons of using this option.
ok
the problem:
postgres=#
postgres=# \set name 'Pavel Stehule'
postgres=# select :name;
ERROR: column pavel does not exist
LINE 1: select Pavel Stehule;
^
The content of variable name is simple. So I am able to explicit quting.
postgres=# \set name '\'Pavel Stehule\''
postgres=# select :name;
?column?
---
Pavel Stehule
(1 row)
But when content of variable goes from outside - I have a problem. I
can get error, or (In worst case), I can be SQL injected.
postgres=# \set name 'usename from pg_user'
postgres=# select :name;
usename
--
postgres
pavel
(2 rows)
with using parametrized queris these problems are out, because queries
and parameters are separated.
I checked second design based on enhanced syntax -
http://www.postgres.cz/index.php/Enhanced-psql#Variables_quoting . It
working too, but it needs one exec more.
Hmm, the :[foo] and :{foo} syntax looks sort of cool. But I don't
understand why it would need any more server calls.
Actually I don't use local implementation of quoting on client side.
Quoting is processed on server side.
So SELECT :{foo} is processed
like
1. take value foo to var;
2. call exec_query_params(SELECT quote_literal($1), var)
3 use result as content of foo
if we copy quote_literal and quote_ident to client, then this call
should be removed.
Pavel
...Robert
--
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] Minimum perl version supported
2009/12/21 Robert Haas robertmh...@gmail.com: On Mon, Dec 21, 2009 at 5:45 AM, Tim Bunce tim.bu...@pobox.com wrote: FYI Perl 5.6.0 was released in March 2000. 5.6.2 in November 2003. Gosh, I feel old. I started on Perl 4.036. What is worth a little bit of effort to establish is exactly what version of Perl we're already depending on, so that we can document that for the benefit of future tool writers. There's no reason why this particular thing needs to be compatible further back than what is already required otherwise. I'd like to know the earliest version of perl that's supported for PL/Perl, if that's ever been determined (I've not seen it in the docs). plperl requires Safe v2.09, released in Oct 2003 and included in 5.8.1. That version, and later versions, have only been tested back to perl 5.6. I'd recommend Perl 5.6(.2) as a minimum for threads/multiplicity. Perl 5.5 had complelely different, and unsafe, code for threads and no concept of multiplicity. I'd recommend Perl 5.8(.1) as a minimum for UTF-8 databases. Perl 5.7 had a complelely different, and flawed, concept of Unicode operation. Tim. p.s. I'll test PL/Perl with perl 5.6.2 (assuming I can still build it on my system) as part of testing the PL/Perl patches I'm working on. It's possible that we might support building the release in general with an older version of Perl than what we support for PL/perl, but it sounds like we may have already burned this bridge if the MSVC stuff expects something newer. Maybe we should just document that 5.6 is The MSVC stuff is only tested on 5.8. the minimum supported version to build from source and be done with it (we should clarify what MSVC requires). If someone is running a 1990s You mean somehting like http://www.postgresql.org/docs/8.4/static/install-win32-full.html#AEN23266 -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Streaming replication and non-blocking I/O
On Fri, Dec 18, 2009 at 11:42 AM, Fujii Masao masao.fu...@gmail.com wrote: Okey. Design clarification again; 0. Begin by connecting to the master using PQconnectdb() with new conninfo option specifying the request of replication. The startup packet with the request is sent to the master, then the backend switches to the walsender mode. The walsender goes into the main loop and wait for the request from the walreceiver. snip 4. Start replication Slave - Master: Query message, with query string START REPLICATION: , where is the RecPtr of the starting point. Master - Slave: CopyOutResponse followed by a continuous stream of CopyData messages with WAL contents. Done. Currently there is no new libpq function for replication. The walreceiver uses only existing functions like PQconnectdb, PQexec, PQgetCopyData, etc. git://git.postgresql.org/git/users/fujii/postgres.git branch: replication 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] New VACUUM FULL
Simon Riggs wrote: I notice that during copy_heap_data() we make no attempt to skip pages that are all visible according to the visibilitymap. It seems like it would be a substantial win to copy whole blocks if all the pre-conditions are met (I see what they are). I'm surprised to see that neither CLUSTER nor VACUUM FULL made use of this previously. I think we either need to implement that or document that vacuum will not skip all-visible pages when running VACUUM FULL. Unfortunately the visibility map isn't completely crash-safe at the moment (see comments in visibilitymap.c for details). So it's not safe to use it for such purposes. I was planning to address that in 8.5 but it seems I won't have the time. -- 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] fdw validation function vs zero catalog id
Tom Lane wrote: The validator function must take two arguments: one of type text[], which will contain the array of options as stored in the system catalogs, and one of type oid, which will be the OID of the system catalog containing the options, or zero if the context is not known. Hmm, dunno how I missed that. But anyway ISTM the current code conforms to that specification just fine. I think what you're really lobbying It certainly looks like a bug to me -- while performing CREATE or ALTER on a SQL/MED object, the catalog must surely be known, and one would expect that the validator function is passed the actual catalog id. Otherwise there would be no point for the validator function to accept the catalog id at all. for is that we remove the or zero escape hatch and insist that the backend code do whatever it has to do to supply a correct OID. This patch shows that that's not too hard right now, but are there going to be future situations where it's harder? What was the motivation for including the escape hatch in the first place? The validator is run for the generic options specified to CREATE/ALTER FDW, SERVER and USER MAPPING (+ possible future SQL/MED objects). In this case the catalog is always known. Also we can assume that the catalog is known, if a user runs the validator directly. So yes, AFAICS there is no case for the or zero. 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
[HACKERS] Table size does not include toast size
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello I wonder why the function pg_relation_size(text) does not take into account the space used by toast data in a table when returning the space used by the table. As an administrator I would expect pg_total_relation_size() to return data+toast+indexes and pg_relation_size() to return data+toast. Is this a deliberate decision? Could we change this behavior in the future? We are using a 8.3 database. Thanks in advance. regards, - -- Rafael Martinez, r.m.guerr...@usit.uio.no Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFLL3m+BhuKQurGihQRAgBdAKCV5ZIBJyDOzGWh/En4sTvWSW67ZwCfYoYx iUYIMJCbk6li2BhYcR7JB5M= =l2YF -END 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
[HACKERS] Re: Proposal: Pre ordered aggregates, default ORDER BY clause for aggregates - median support
On Mon, Dec 21, 2009 at 11:43 AM, Pavel Stehule pavel.steh...@gmail.com wrote: If we don't find a way to optimize this for on-disk tuplestores though then I wonder whether it's worth it. It would only help in the narrow case that you had a large enough set to see the difference between doing quickselect and quicksort but small enough to fit in workmem. I suppose that case is widening over time though as memory sizes get bigger and bigger. Thank you I have to do same test and get more info about quickselect So my first thought of how to do median efficiently on large on-disk data structures is to do a first pass, attempting to find as narrow a target partition as possible that the median definitely falls in. Then do a second pass counting how many tuples fall before and after the target partition and accumulating all the tuples in the range and perform quickselect to find the right tuple in the target partition. I do wonder To find a target partition you could use quickselect itself to find a set of medians but I think it will boil down to a kind of huffman-like tree encoding. You want to accumulate values as degenerate singleton partitions until they don't fit in work_mem. When you exhaust work_mem you collapse two adjacent partitions into a single partition covering the whole range with a count of 2. You keep going collapsing the two lowest count ranges each time (perhaps preferring to collapse ranges far from the current median?). When you're done you can easily determine which range contains the median. You then have to scan the whole original input again skipping any tuple that falls outside that partition. If it still doesn't fit in work_mem you have to repeat the algorithm. This looks like a lot of code for a narrow use case though. And it would do a minimum of two passes through the input which is going to mean it'll perform similarly to our regular tape sort until the inputs get very large and tapesort needs to do multiple passes. At that point it's possible this algorithm might need multiple passes as well, though hopefully of smaller and smaller sets. I thought I would put it down in an email in case anyone's interested in experimenting though. Also, I assume I've just reinvented some standard algorithm I should remember from school, though I can't tell which offhand. It looks kind of like mergesort except since we're just doing selection we don't need to actually do the merge step, just keep track of which values we would have merged. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New VACUUM FULL
On Mon, Dec 21, 2009 at 12:56 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: Simon Riggs wrote: I notice that during copy_heap_data() we make no attempt to skip pages that are all visible according to the visibilitymap. It seems like it would be a substantial win to copy whole blocks if all the pre-conditions are met (I see what they are). I'm surprised to see that neither CLUSTER nor VACUUM FULL made use of this previously. I think we either need to implement that or document that vacuum will not skip all-visible pages when running VACUUM FULL. Unfortunately the visibility map isn't completely crash-safe at the moment (see comments in visibilitymap.c for details). So it's not safe to use it for such purposes. I was planning to address that in 8.5 but it seems I won't have the time. Well since we're going to have to read in the page to do the copy we could just use the page header flag PD_ALL_VISIBLE instead. But sequential scans already use that bit and I'm assuming but haven't checked that these access paths do use the same underlying access path as sequential scans. In which case it won't really save much since the main advantage would be skipping the visibility checks. Saving the actual work to copy tuples retail instead of the whole block wholesale seems unlikely to buy much and would result in us not compacting space on the page and storing accurate free space map values which I think people would expect from both of these commands. If I'm wrong and these commands are not using a sequential scan under the hood or the fact that they're using SNAPSHOT_ANY defeats that optimization then perhaps there is something there. On the third hand presumably all the hint bits will be set if the page bit is set so perhaps there's nothing there even so. -- greg -- 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] alpha3 bundled -- please verify
On sön, 2009-12-20 at 22:02 +0200, Marko Tiikkaja wrote: On 2009-12-20 18:20 +0200, Tom Lane wrote: James William Pyeli...@jwp.name writes: But it doesn't seem to want to stop configure'ing on my fbsd8/amd64 box: Usually that means timestamp skew, ie file timestamps are later than your system clock. I've hit this problem before and could not figure out what was wrong. Is this documented somewhere? Well, this is more of a general problem. Anything using make will misbehave if the clock is wrong, and anything using autotools even more so. -- 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] Minimum perl version supported
On Dec 21, 2009, at 7:46 AM, Magnus Hagander mag...@hagander.net wrote: 2009/12/21 Robert Haas robertmh...@gmail.com: On Mon, Dec 21, 2009 at 5:45 AM, Tim Bunce tim.bu...@pobox.com wrote: FYI Perl 5.6.0 was released in March 2000. 5.6.2 in November 2003. Gosh, I feel old. I started on Perl 4.036. What is worth a little bit of effort to establish is exactly what version of Perl we're already depending on, so that we can document that for the benefit of future tool writers. There's no reason why this particular thing needs to be compatible further back than what is already required otherwise. I'd like to know the earliest version of perl that's supported for PL/Perl, if that's ever been determined (I've not seen it in the docs). plperl requires Safe v2.09, released in Oct 2003 and included in 5.8.1. That version, and later versions, have only been tested back to perl 5.6. I'd recommend Perl 5.6(.2) as a minimum for threads/multiplicity. Perl 5.5 had complelely different, and unsafe, code for threads and no concept of multiplicity. I'd recommend Perl 5.8(.1) as a minimum for UTF-8 databases. Perl 5.7 had a complelely different, and flawed, concept of Unicode operation. Tim. p.s. I'll test PL/Perl with perl 5.6.2 (assuming I can still build it on my system) as part of testing the PL/Perl patches I'm working on. It's possible that we might support building the release in general with an older version of Perl than what we support for PL/perl, but it sounds like we may have already burned this bridge if the MSVC stuff expects something newer. Maybe we should just document that 5.6 is The MSVC stuff is only tested on 5.8. the minimum supported version to build from source and be done with it (we should clarify what MSVC requires). If someone is running a 1990s You mean somehting like http://www.postgresql.org/docs/8.4/static/install-win32-full.html#AEN23266 Yeah, very much like that. :-) ...Robert -- 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] Table size does not include toast size
Rafael Martinez r.m.guerr...@usit.uio.no writes: I wonder why the function pg_relation_size(text) does not take into account the space used by toast data in a table when returning the space used by the table. It's not supposed to. Use pg_total_relation_size if you want a number that includes index and toast space. 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] Minimum perl version supported
Magnus Hagander mag...@hagander.net writes: 2009/12/21 Robert Haas robertmh...@gmail.com: On Mon, Dec 21, 2009 at 5:45 AM, Tim Bunce tim.bu...@pobox.com wrote: plperl requires Safe v2.09, released in Oct 2003 and included in 5.8.1. That version, and later versions, have only been tested back to perl 5.6. I'd recommend Perl 5.8(.1) as a minimum for UTF-8 databases. Perl 5.7 had a complelely different, and flawed, concept of Unicode operation. The MSVC stuff is only tested on 5.8. Given the above three things it seems like we could define 5.8.1 as the minimum required version. 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] Minimum perl version supported
On Mon, Dec 21, 2009 at 10:09:58AM -0500, Tom Lane wrote: Magnus Hagander mag...@hagander.net writes: 2009/12/21 Robert Haas robertmh...@gmail.com: On Mon, Dec 21, 2009 at 5:45 AM, Tim Bunce tim.bu...@pobox.com wrote: plperl requires Safe v2.09, released in Oct 2003 and included in 5.8.1. That version, and later versions, have only been tested back to perl 5.6. I'd recommend Perl 5.8(.1) as a minimum for UTF-8 databases. Perl 5.7 had a complelely different, and flawed, concept of Unicode operation. The MSVC stuff is only tested on 5.8. Given the above three things it seems like we could define 5.8.1 as the minimum required version. I'd be delighted with that. Tim. -- 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] An example of bugs for Hot Standby
Hiroyuki Yamada yam...@kokolink.net wrote: 4. Startup process tries to redo XLOG_HEAP2_CLEAN record, calls LockBufferForCleanup() and freezes until the Xact 1 ends. I think they word you're searching for is blocks. Blocking to protect integrity doesn't sound like a bug to me; perhaps an opportunity for enhancement. -Kevin -- 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] Table size does not include toast size
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: Rafael Martinez r.m.guerr...@usit.uio.no writes: I wonder why the function pg_relation_size(text) does not take into account the space used by toast data in a table when returning the space used by the table. It's not supposed to. Use pg_total_relation_size if you want a number that includes index and toast space. I am probably missing the point here, why is it not supposed to show the size of the table(data) *without* indexes? My question was because I can not understand the use and usefulness of pg_relation_size() (as it works today) in a table that use toast. - From an administrator point of view, there are two numbers that are interesting, the total size of a table (indexes included) and the size of the table without taking into account the space used by its indexes. At least, if there is a logic in this behavior, it should be documented in 9.23. System Administration Functions. The documentation only says Disk space used by the table or index with It is not the first time confused users have asked me why pg_relation_size() does not show the space used by the table without indexes. Many do not know what 'toast' is, and most probably they do not need to know about this either. regards, - -- Rafael Martinez, r.m.guerr...@usit.uio.no Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFLL5NCBhuKQurGihQRAmtZAJ99wJPKbS1u2RUGxO4G++X7nbqt2gCeJubn b+328nrEICsXPS7kgD4bq68= =bBO8 -END 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] Table size does not include toast size
--On 21. Dezember 2009 10:01:37 -0500 Tom Lane t...@sss.pgh.pa.us wrote: It's not supposed to. Use pg_total_relation_size if you want a number that includes index and toast space. I've created a C-Function a while ago that extracts the TOAST size for a given relation. This gave me the opportunity to do a pg_relation_size(oid) + pg_relation_toast_size(oid) for a given table oid to calculate on disk data size required by a table. Maybe we should include such a function in core? -- Thanks Bernd -- 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] Small Bug in GetConflictingVirtualXIDs
Andres Freund wrote: The logic behind this seems fine except in the case of dropping a database. There you very well might have a open connection without an open snapshot. Perhaps the simplest fix is to ensure that drop database gets a snapshot? -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] Table size does not include toast size
Rafael Martinez r.m.guerr...@usit.uio.no writes: I am probably missing the point here, why is it not supposed to show the size of the table(data) *without* indexes? Because pg_relation_size is defined at the physical level of showing one relation, where relation means a pg_class entry. If you want agglomerations of multiple relations, you can use pg_total_relation_size, or build your own total if you have some other usage in mind. The one you propose seems fairly arbitrary --- for example, if it includes the toast relation, why not the toast relation's index too? It's not like either one is optional from the user's standpoint. 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] Table size does not include toast size
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Bernd Helmle wrote: --On 21. Dezember 2009 10:01:37 -0500 Tom Lane t...@sss.pgh.pa.us wrote: It's not supposed to. Use pg_total_relation_size if you want a number that includes index and toast space. I've created a C-Function a while ago that extracts the TOAST size for a given relation. This gave me the opportunity to do a pg_relation_size(oid) + pg_relation_toast_size(oid) for a given table oid to calculate on disk data size required by a table. Maybe we should include such a function in core? It is a possibility. But I really think that pg_relation_size() not reporting the total size of the table (without indexes) is useless. toast is an internal way of organizing/saving data for tuples larger than the page size used by PostgreSQL. It is a mechanism transparent to the user and therefore pg_relation_size() should not differentiate between data saved via toast or not. The size of the table without the indexes should be reported regardless the technique used to save the data on the disk. regards, - -- Rafael Martinez, r.m.guerr...@usit.uio.no Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFLL5ZHBhuKQurGihQRAoR8AJ97RoST3VHGCmcIOhkdRbJIWb3mnwCeN7Mm 7Oja4kmyrQfM6/RxyUE4K2A= =kxO9 -END 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] Small Bug in GetConflictingVirtualXIDs
Alvaro Herrera alvhe...@commandprompt.com writes: Andres Freund wrote: The logic behind this seems fine except in the case of dropping a database. There you very well might have a open connection without an open snapshot. Perhaps the simplest fix is to ensure that drop database gets a snapshot? I confess to not having followed the thread closely, but why is DROP DATABASE special in this regard? Wouldn't we soon find ourselves needing every utility command to take a snapshot? 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] Small Bug in GetConflictingVirtualXIDs
On Mon, 2009-12-21 at 10:38 -0500, Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Andres Freund wrote: The logic behind this seems fine except in the case of dropping a database. There you very well might have a open connection without an open snapshot. Perhaps the simplest fix is to ensure that drop database gets a snapshot? I confess to not having followed the thread closely, but why is DROP DATABASE special in this regard? Wouldn't we soon find ourselves needing every utility command to take a snapshot? Andres has worded this a little imprecisely, causing a confusion. In cases regarding HS we need to be clear whether the interacting sessions are on the master or on the standby to understand the reasons for poor interactions. What he means is that you can be connected to the standby without an open snapshot (from the standby) at the point we replay a drop database command that had been run on the master. That case currently causes the bug, created by my recent change to GetConflictingVirtualXids(). Giving the drop database a snapshot is not the answer. I expect Andres to be able to fix this with a simple patch that would not effect the case of normal running. -- Simon Riggs www.2ndQuadrant.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] Table size does not include toast size
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: Rafael Martinez r.m.guerr...@usit.uio.no writes: I am probably missing the point here, why is it not supposed to show the size of the table(data) *without* indexes? Because pg_relation_size is defined at the physical level of showing one relation, where relation means a pg_class entry. If you want agglomerations of multiple relations, you can use pg_total_relation_size, Ok, thanks for the clarification :-) The 'problem' is that as a developer with advanced knowledge of the postgres internals, you see a table as a group of relations (toast, indexes, toast relation's index, etc) A 'normal' user only sees a table and its indexes and this user misinterpret the use of the function pg_relation_size() when it reads in the documentation pg_relation_size(): Disk space used by the table or index ... regards, - -- Rafael Martinez, r.m.guerr...@usit.uio.no Center for Information Technology Services University of Oslo, Norway PGP Public Key: http://folk.uio.no/rafael/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.7 (GNU/Linux) iD8DBQFLL5lwBhuKQurGihQRApt1AJ4wQS9+WSiUSAB6sSV6i/z0y0gZhwCfWq1Y BnnbddNedMMGCUGJ+X4eMMY= =yUsa -END 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] Minimum perl version supported
On Dec 21, 2009, at 7:18 AM, Tim Bunce wrote: Given the above three things it seems like we could define 5.8.1 as the minimum required version. I'd be delighted with that. +1 BTW Tim, have you tested with 5.11 yet? Best, 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] Small Bug in GetConflictingVirtualXIDs
On Monday 21 December 2009 16:38:07 Tom Lane wrote: Alvaro Herrera alvhe...@commandprompt.com writes: Andres Freund wrote: The logic behind this seems fine except in the case of dropping a database. There you very well might have a open connection without an open snapshot. Perhaps the simplest fix is to ensure that drop database gets a snapshot? I confess to not having followed the thread closely, but why is DROP DATABASE special in this regard? Wouldn't we soon find ourselves needing every utility command to take a snapshot? Because most other entities are locked when you access them. So on the standby the AccessExlusive (generated on the master) will conflict with whatever lock you currently have on that entity (on the slave). There are no locks for an idle session though. Andres -- 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] Table size does not include toast size
Bernd Helmle wrote: I've created a C-Function a while ago that extracts the TOAST size for a given relation. This gave me the opportunity to do a pg_relation_size(oid) + pg_relation_toast_size(oid) for a given table oid to calculate on disk data size required by a table. Maybe we should include such a function in core? Writing such a thing is already on my to-do list; it's absolutely a missing piece of the puzzle here. If you've got such a patch, by all means submit that. I just ran into my first heavily TOASTy database recently and the way I'm computing sizes on the relations there is too complicated for my tastes, so it's completely unreasonable to expect regular users to do that. To answer Rafael's concerns directly: you're right that this is confusing. pg_relation_size is always going to do what it does right now just because of how that fits into the design of the database. However, the documentation should be updated to warn against the issue with TOAST here. And it should be easier to get the total you're like to see here: main relation + toasted parts, since that's what most DBAs want in this area. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] Table size does not include toast size
Greg Smith g...@2ndquadrant.com writes: To answer Rafael's concerns directly: you're right that this is confusing. pg_relation_size is always going to do what it does right now just because of how that fits into the design of the database. However, the documentation should be updated to warn against the issue with TOAST here. And it should be easier to get the total you're like to see here: main relation + toasted parts, since that's what most DBAs want in this area. Perhaps invent pg_table_size() = base table + toast table + toast index and pg_indexes_size() = all other indexes for table giving us the property pg_table_size + pg_indexes_size = pg_total_relation_size I think the 8.4 documentation already makes it apparent that pg_relation_size is a pretty low-level number. If we invent other functions with obvious names, that should be 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] Table size does not include toast size
Tom Lane wrote: Perhaps invent pg_table_size() = base table + toast table + toast index and pg_indexes_size() = all other indexes for table giving us the property pg_table_size + pg_indexes_size = pg_total_relation_size Right; that's exactly the way I'm computing things now, I just have to crawl way too much catalog data to do it. I also agree that if we provide pg_table_size, the issue of pg_relation_size doesn't do what I want goes away without needing to even change the existing documentation--people don't come to that section looking for relation, they're looking for table. Bernd, there's a basic spec if you have time to work on this. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] Streaming replication and non-blocking I/O
Fujii Masao wrote: On Fri, Dec 18, 2009 at 11:42 AM, Fujii Masao masao.fu...@gmail.com wrote: Okey. Design clarification again; 0. Begin by connecting to the master using PQconnectdb() with new conninfo option specifying the request of replication. The startup packet with the request is sent to the master, then the backend switches to the walsender mode. The walsender goes into the main loop and wait for the request from the walreceiver. snip 4. Start replication Slave - Master: Query message, with query string START REPLICATION: , where is the RecPtr of the starting point. Master - Slave: CopyOutResponse followed by a continuous stream of CopyData messages with WAL contents. Done. Currently there is no new libpq function for replication. The walreceiver uses only existing functions like PQconnectdb, PQexec, PQgetCopyData, etc. Ok thanks, sounds good, I'll take a look. -- 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] Streaming replication and non-blocking I/O
Fujii Masao wrote: On Tue, Dec 15, 2009 at 4:11 AM, Tom Lane t...@sss.pgh.pa.us wrote: Hm. Perhaps it should be a loadable plugin and not hard-linked into the backend? Compare dblink. You mean that such plugin is supplied in shared_preload_libraries, a new process is forked and the shared-memory related to walreceiver is created by using shmem_startup_hook? Since this approach would solve the problem discussed previously, ISTM this makes sense. http://archives.postgresql.org/pgsql-hackers/2009-11/msg00031.php Some additional code might be required to control the termination of walreceiver. I'm not sure which problem in that thread you're referring to, but I can see two options: 1. Use dlopen()/dlsym() in walreceiver to use libpq. A bit awkward, though we could write a bunch of macros to hide that and make the libpq calls look normal. 2. Move walreceiver altogether into a loadable module, which is linked as usual to libpq. Like e.g contrib/dblink. Thoughts? Both seem reasonable to me. I tested the 2nd option (see 'replication' branch in my git repository), splitting walreceiver.c into two: the functions that run in the walreceiver process, and the functions that are called from other processes to control walreceiver. That's a quite nice separation, though of course we could do that with the 1st approach as well. PS. I just merged with CVS HEAD. Streaming replication is pretty awesome with Hot Standby! -- 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] Minimum perl version supported
On Mon, Dec 21, 2009 at 08:22:54AM -0800, David E. Wheeler wrote: On Dec 21, 2009, at 7:18 AM, Tim Bunce wrote: Given the above three things it seems like we could define 5.8.1 as the minimum required version. I'd be delighted with that. +1 BTW Tim, have you tested with 5.11 yet? Not recently. I'm putting the finishing touches on my updated feature patch now. I hope to post it in the next few days. I'll test with 5.8.1 and 5.11 before I do. Tim. -- 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] Table size does not include toast size
On Mon, Dec 21, 2009 at 5:02 PM, Greg Smith g...@2ndquadrant.com wrote: Tom Lane wrote: Perhaps invent pg_table_size() = base table + toast table + toast index and pg_indexes_size() = all other indexes for table giving us the property pg_table_size + pg_indexes_size = pg_total_relation_size Right; that's exactly the way I'm computing things now, I just have to crawl way too much catalog data to do it. I also agree that if we provide pg_table_size, the issue of pg_relation_size doesn't do what I want goes away without needing to even change the existing documentation--people don't come to that section looking for relation, they're looking for table. Bernd, there's a basic spec if you have time to work on this. What about, the visibility maps and free space maps? -- greg -- 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] Table size does not include toast size
Greg Stark gsst...@mit.edu writes: On Mon, Dec 21, 2009 at 5:02 PM, Greg Smith g...@2ndquadrant.com wrote: Right; that's exactly the way I'm computing things now, I just have to crawl way too much catalog data to do it. I also agree that if we provide pg_table_size, the issue of pg_relation_size doesn't do what I want goes away without needing to even change the existing documentation--people don't come to that section looking for relation, they're looking for table. Bernd, there's a basic spec if you have time to work on this. What about, the visibility maps and free space maps? Those would be included for each relation, I should think. The objective here is not to break things down even more finely than pg_relation_size does, but to aggregate into terms that are meaningful to the user --- which is to say, the table and its indexes. Anything you can't get rid of by dropping indexes/constraints is part of the table at this level of detail. 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] Streaming replication and non-blocking I/O
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Fujii Masao wrote: I'm not sure which problem in that thread you're referring to, but I can see two options: 1. Use dlopen()/dlsym() in walreceiver to use libpq. A bit awkward, though we could write a bunch of macros to hide that and make the libpq calls look normal. 2. Move walreceiver altogether into a loadable module, which is linked as usual to libpq. Like e.g contrib/dblink. Thoughts? Both seem reasonable to me. From a packager's standpoint the second is much saner. If you want to use dlopen() then you will have to know the exact name of the .so file (e.g. libpq.so.5.3) and possibly its location too. Or you will have to persuade packagers that they should ship bare libpq.so symlinks, which is contrary to packaging standards on most Linux distros. (walreceiver.so wouldn't be subject to those standards, but libpq is because it's a regular library that can also be hard-linked by applications.) 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] Segfault from PL/Perl Returning vstring
At least I think it's a segfault. This function returns a vstring: CREATE OR REPLACE FUNCTION wtf( ) RETURNS text LANGUAGE plperl IMMUTABLE STRICT AS $X$ return $^V; $X$; Here's what happens when I call it: try=# select wtf(); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! So I think that it doesn't know what to do with vstrings. They should probably never be returned (they're mostly deprecated), but if they are, they should be cast to text, I think. Best, 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] Segfault from PL/Perl Returning vstring
David E. Wheeler wrote: At least I think it's a segfault. This function returns a vstring: CREATE OR REPLACE FUNCTION wtf( ) RETURNS text LANGUAGE plperl IMMUTABLE STRICT AS $X$ return $^V; $X$; Here's what happens when I call it: try=# select wtf(); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! So I think that it doesn't know what to do with vstrings. They should probably never be returned (they're mostly deprecated), but if they are, they should be cast to text, I think. It's not doing that for me. The plperl code has no way at all of knowing that the bytes you are returning come from $^V. If you really want the version back, do what the perl docs tell you and sprintf the value: andrew=# CREATE OR REPLACE FUNCTION wtf( ) RETURNS text LANGUAGE plperl IMMUTABLE STRICT AS $X$ return sprintf(%vd,$^V); $X$; CREATE FUNCTION andrew=# select wtf(); wtf --- 5.8.8 BTW, this should arguably not be an immutable function. You could replace the perl library, so it's not solely dependent on the input for the result. 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] Segfault from PL/Perl Returning vstring
On Dec 21, 2009, at 11:46 AM, Andrew Dunstan wrote: It's not doing that for me. Odd. The plperl code has no way at all of knowing that the bytes you are returning come from $^V. If you really want the version back, do what the perl docs tell you and sprintf the value: It works fine if I return `$^V`. BTW, this should arguably not be an immutable function. You could replace the perl library, so it's not solely dependent on the input for the result. Yeah, that's leftover from trying to troubleshoot another problem. More on that in a bit. Best, 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] Segfault from PL/Perl Returning vstring
On mån, 2009-12-21 at 14:46 -0500, Andrew Dunstan wrote: BTW, this should arguably not be an immutable function. You could replace the perl library, so it's not solely dependent on the input for the result. By this logic, no function could be immutable, because you could replace the C library or parts of the kernel. -- 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] Segfault from PL/Perl Returning vstring
Peter Eisentraut wrote: On mån, 2009-12-21 at 14:46 -0500, Andrew Dunstan wrote: BTW, this should arguably not be an immutable function. You could replace the perl library, so it's not solely dependent on the input for the result. By this logic, no function could be immutable, because you could replace the C library or parts of the kernel. *shrug* Maybe, OTOH upgrading perl is not such an unusual operation, and doing so *will* affect this value. It's a bit hard to see why one would want the perl version to be immutable. It doesn't look like it would be much use in an index I think people are generally safer not marking functions immutable unless they actually need them to be. 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] Possible patch for better index name choosing
On mån, 2009-12-21 at 00:03 -0500, Tom Lane wrote: Well, we could tamp down the risks considerably if we undid my point (1), namely to still consider only the first index column when generating a name. I think putting all the column names into the index names instead of only the first is a significant improvement that should be kept. If we can't do it properly in some cases, we should punt in some obvious way, not pretend to do the correct thing but actually omit some bits. -- 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] Segfault from PL/Perl Returning vstring
On Mon, Dec 21, 2009 at 02:46:17PM -0500, Andrew Dunstan wrote: David E. Wheeler wrote: At least I think it's a segfault. This function returns a vstring: CREATE OR REPLACE FUNCTION wtf( ) RETURNS text LANGUAGE plperl IMMUTABLE STRICT AS $X$ return $^V; $X$; Here's what happens when I call it: try=# select wtf(); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. ! So I think that it doesn't know what to do with vstrings. They should probably never be returned (they're mostly deprecated), but if they are, they should be cast to text, I think. It's not doing that for me. You're using 5.8.8. In 5.10.0 $^V was changed to be an object. I'm working in that area. I'll look into it. Tim. -- 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] Segfault from PL/Perl Returning vstring
On Dec 21, 2009, at 2:13 PM, Tim Bunce wrote:
You're using 5.8.8. In 5.10.0 $^V was changed to be an object.
I'm working in that area. I'll look into it.
While you're at it, I have a new problem:
CREATE OR REPLACE FUNCTION wtf(
expression text
) RETURNS text LANGUAGE plperl AS $$
return {$_[0]};
$$;
try=# select wtf('foo');
wtf
-
{
(1 row)
Note how I don't even get the closing }. This does not happen with PL/PerlU.
The denizens of #postgresql think that there's some sort of issue with Safe and
Encode or Encode::Alias (the latter `require`s Encode in two if its methods).
Can you replicate it? It's driving me absolutely batshit. I'm using Perl
5.10.2, Safe 2.20, and Encode 2.39.
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] Possible patch for better index name choosing
Peter Eisentraut pete...@gmx.net writes: On mån, 2009-12-21 at 00:03 -0500, Tom Lane wrote: Well, we could tamp down the risks considerably if we undid my point (1), namely to still consider only the first index column when generating a name. I think putting all the column names into the index names instead of only the first is a significant improvement that should be kept. Yeah, I think so too. It's well worth any risk of application incompatibility --- we make much bigger changes in every major release without blinking. 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] Segfault from PL/Perl Returning vstring
On Dec 21, 2009, at 2:34 PM, David E. Wheeler wrote:
On Dec 21, 2009, at 2:13 PM, Tim Bunce wrote:
You're using 5.8.8. In 5.10.0 $^V was changed to be an object.
I'm working in that area. I'll look into it.
While you're at it, I have a new problem:
CREATE OR REPLACE FUNCTION wtf(
expression text
) RETURNS text LANGUAGE plperl AS $$
return {$_[0]};
$$;
try=# select wtf('foo');
wtf
-
{
(1 row)
Note how I don't even get the closing }. This does not happen with
PL/PerlU. The denizens of #postgresql think that there's some sort of issue
with Safe and Encode or Encode::Alias (the latter `require`s Encode in two if
its methods). Can you replicate it? It's driving me absolutely batshit. I'm
using Perl 5.10.2, Safe 2.20, and Encode 2.39.
Also, my encoding is en_US.UTF-8. I think it matter that it's UTF-8, to judge
by the
if (GetDatabaseEncoding() == PG_UTF8)
Block in plperl.c.
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
[HACKERS] Small change of the HS document
Hi, I found there is no primary tag for the HS parameters in config.sgml. Attached patch adds that tag. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** *** 1753,1758 archive_command = 'copy %p C:\\server\\archivedir\\%f' # Windows --- 1753,1761 varlistentry id=recovery-connections xreflabel=recovery_connections termvarnamerecovery_connections/varname (typeboolean/type)/term + indexterm +primaryvarnamerecovery_connections/ configuration parameter/primary + /indexterm listitem para Parameter has two roles. During recovery, specifies whether or not *** *** 1771,1776 archive_command = 'copy %p C:\\server\\archivedir\\%f' # Windows --- 1774,1782 varlistentry id=max-standby-delay xreflabel=max_standby_delay termvarnamemax_standby_delay/varname (typestring/type)/term + indexterm +primaryvarnamemax_standby_delay/ configuration parameter/primary + /indexterm listitem para When server acts as a standby, this parameter specifies a wait policy -- 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] Small Bug in GetConflictingVirtualXIDs
On Monday 21 December 2009 16:48:52 Simon Riggs wrote: Giving the drop database a snapshot is not the answer. I expect Andres to be able to fix this with a simple patch that would not effect the case of normal running. Actually its less simply than I had thought at first - I don't think the code ever handled that correctly. I might be wrong there, my knowledge of the involved code is a bit sparse... The whole conflict resolution builds on the concept of waiting for an VXid, but an idle backend does not have a valid vxid. Thats correct, right? Sure, the code should be modifyable to handle that code mostly transparently (simply ignoring a invalid localTransactionId when the database id is valid), but ... I am inclined to just unconditionally kill the users of the database. Its not like that would be an issue in production. I cant see a case where its important to run a session to its end on a database which was dropped on the master. Opinions on that? Andres -- 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] Streaming replication and non-blocking I/O
On Tue, Dec 22, 2009 at 2:31 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: 2. Move walreceiver altogether into a loadable module, which is linked as usual to libpq. Like e.g contrib/dblink. Thoughts? Both seem reasonable to me. I tested the 2nd option (see 'replication' branch in my git repository), splitting walreceiver.c into two: the functions that run in the walreceiver process, and the functions that are called from other processes to control walreceiver. That's a quite nice separation, though of course we could do that with the 1st approach as well. Though I seem not to understand what a loadable module means, I wonder how the walreceiver module is loaded. AFAIK, we need to manually install the dblink functions by executing dblink.sql before using them. Likewise, if we choose the 2nd option, we must manually install the walreceiver module before starting replication? Or we automatically install that by executing system_view.sql, like pg_start_backup? I'd like to reduce the number of installation operations as much as possible. Is my concern besides the point? PS. I just merged with CVS HEAD. Streaming replication is pretty awesome with Hot Standby! 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] LIKE INCLUDING COMMENTS code is a flight of fancy
Tom Lane t...@sss.pgh.pa.us wrote: I suggest that we might want to just rip out the support for copying comments on indexes. Or maybe even the whole copy-comments aspect of it. We have two related ToDo items below. They are a bit inconsintent, but they mean we should forbid COMMENT on columns of an index, or must have full-support of the feature. Which direction should we go? As for me, forbidding comments on index columns seems to be a saner way because index can have arbitrary key names in some cases. - Forbid COMMENT on columns of an index Postgres currently allows comments to be placed on the columns of an index, but pg_dump doesn't handle them and the column names themselves are implementation-dependent. http://archives.postgresql.org/message-id/27676.1237906...@sss.pgh.pa.us - pg_dump / pg_restore: Add dumping of comments on index columns and composite type columns http://archives.postgresql.org/pgsql-hackers/2009-03/msg00931.php (XXX: Comments on composite type columns can work now?) Regards, --- Takahiro Itagaki 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] Streaming replication and non-blocking I/O
Fujii Masao masao.fu...@gmail.com writes: Though I seem not to understand what a loadable module means, I wonder how the walreceiver module is loaded. Put it in shared_preload_libraries, perhaps. 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] Segfault from PL/Perl Returning vstring
David E. Wheeler wrote:
On Dec 21, 2009, at 2:34 PM, David E. Wheeler wrote:
On Dec 21, 2009, at 2:13 PM, Tim Bunce wrote:
You're using 5.8.8. In 5.10.0 $^V was changed to be an object.
I'm working in that area. I'll look into it.
While you're at it, I have a new problem:
CREATE OR REPLACE FUNCTION wtf(
expression text
) RETURNS text LANGUAGE plperl AS $$
return {$_[0]};
$$;
try=# select wtf('foo');
wtf
-
{
(1 row)
Note how I don't even get the closing }. This does not happen with PL/PerlU.
The denizens of #postgresql think that there's some sort of issue with Safe and Encode or
Encode::Alias (the latter `require`s Encode in two if its methods). Can you replicate it?
It's driving me absolutely batshit. I'm using Perl 5.10.2, Safe 2.20, and Encode 2.39.
Also, my encoding is en_US.UTF-8. I think it matter that it's UTF-8, to judge
by the
if (GetDatabaseEncoding() == PG_UTF8)
Block in plperl.c.
I cannot reproduce this. I tested with perl 5.10.1 which is the latest
reported stable release at http://www.cpan.org/src/README.html, on an
8.4.2 UTF8 database, and with the same Safe and Encode module versions
as above.
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] Backup history file should be replicated in Streaming Replication?
On Sat, Dec 19, 2009 at 1:03 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I don't think it's worthwhile to modify pg_stop_backup() like that. We should address the general problem. At the moment, you're fine if you also configure WAL archiving and log file shipping, but it would be nice to have some simpler mechanism to avoid the problem. For example, a GUC in master to retain all log files (including backup history files) for X days. Or some way for to register the standby with the master so that the master knows it's out there, and still needs the logs, even when it's not connected. I propose the new GUC replication_reserved_segments (better name?) which determines the maximum number of WAL files held for the standby. Design: * Only the WAL files which are replication_reserved_segments segments older than the current write segment can be recycled. IOW, we can think that the standby which falls replication_reserved_segments segments behind is always connected to the primary, and the WAL files needed for the active standby are not recycled. * Disjoin the standby which falls more than replication_reserved_segment segments behind, in order to avoid the disk full failure, i.e., the primary server's PANIC error. This would be only possible in asynchronous replication case. Thought? 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] Largeobject Access Controls (r2460)
(2009/12/21 9:39), KaiGai Kohei wrote:
(2009/12/19 12:05), Robert Haas wrote:
On Fri, Dec 18, 2009 at 9:48 PM, Tom Lanet...@sss.pgh.pa.us wrote:
Robert Haasrobertmh...@gmail.com writes:
Oh. This is more complicated than it appeared on the surface. It
seems that the string BLOB COMMENTS actually gets inserted into
custom dumps somewhere, so I'm not sure whether we can just change it.
Was this issue discussed at some point before this was committed?
Changing it would seem to require inserting some backward
compatibility code here. Another option would be to add a separate
section for BLOB METADATA, and leave BLOB COMMENTS alone. Can
anyone comment on what the Right Thing To Do is here?
The BLOB COMMENTS label is, or was, correct for what it contained.
If this patch has usurped it to contain other things
It has.
I would argue
that that is seriously wrong. pg_dump already has a clear notion
of how to handle ACLs for objects. ACLs for blobs ought to be
made to fit into that structure, not dumped in some random place
because that saved a few lines of code.
OK. Hopefully KaiGai or Takahiro can suggest a fix.
The patch has grown larger than I expected before, because the way
to handle large objects are far from any other object classes.
Here are three points:
1) The new BLOB ACLS section was added.
It is a single purpose section to describe GRANT/REVOKE statements
on large objects, and BLOB COMMENTS section was reverted to put
only descriptions.
Because we need to assume a case when the database holds massive
number of large objects, it is not reasonable to store them using
dumpACL(). It chains an ACL entry with the list of TOC entries,
then, these are dumped. It means pg_dump may have to register massive
number of large objects in the local memory space.
Currently, we also store GRANT/REVOKE statements in BLOB COMMENTS
section, but confusable. Even if pg_restore is launched with
--no-privileges options, it cannot ignore GRANT/REVOKE statements
on large objects. This fix enables to distinguish ACLs on large
objects from other properties, and to handle them correctly.
2) The BLOBS section was separated for each database users.
Currently, the BLOBS section does not have information about owner
of the large objects to be restored. So, we tried to alter its
ownership in the BLOB COMMENTS section, but incorrect.
The --use-set-session-authorization option requires to restore
ownership of objects without ALTER ... OWNER TO statements.
So, we need to set up correct database username on the section
properties.
This patch renamed the hasBlobs() by getBlobs(), and changed its
purpose. It registers DO_BLOBS, DO_BLOB_COMMENTS and DO_BLOB_ACLS
for each large objects owners, if necessary.
For example, if here are five users owning large objects, getBlobs()
shall register five TOC entries for each users, and dumpBlobs(),
dumpBlobComments() and dumpBlobAcls() shall be also invoked five
times with the username.
3) _LoadBlobs()
For regular database object classes, _printTocEntry() can inject
ALTER xxx OWNER TO ... statement on the restored object based on
the ownership described in the section header.
However, we cannot use this infrastructure for large objects as-is,
because one BLOBS section can restore multiple large objects.
_LoadBlobs() is a routine to restore large objects within a certain
section. This patch modifies this routine to inject ALTER LARGE
OBJECT loid OWNER TO user statement for each large objects
based on the ownership of the section.
(if --use-set-session-authorization is not given.)
$ diffstat pgsql-fix-pg_dump-blob-privs.patch
pg_backup_archiver.c |4
pg_backup_custom.c | 11 !
pg_backup_files.c|9 !
pg_backup_tar.c |9 !
pg_dump.c| 312 +++!!
pg_dump.h|9 !
pg_dump_sort.c |8 !
7 files changed, 68 insertions(+), 25 deletions(-), 269 modifications(!)
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei kai...@ak.jp.nec.com
*** base/src/bin/pg_dump/pg_dump.h (revision 2512)
--- base/src/bin/pg_dump/pg_dump.h (working copy)
***
*** 116,122
DO_FOREIGN_SERVER,
DO_DEFAULT_ACL,
DO_BLOBS,
! DO_BLOB_COMMENTS
} DumpableObjectType;
typedef struct _dumpableObject
--- 116,123
DO_FOREIGN_SERVER,
DO_DEFAULT_ACL,
DO_BLOBS,
! DO_BLOB_COMMENTS,
! DO_BLOB_ACLS,
} DumpableObjectType;
typedef struct _dumpableObject
***
*** 442,447
--- 443,454
char *defaclacl;
} DefaultACLInfo;
+ typedef struct _blobsInfo
+ {
+ DumpableObject dobj;
+ char *rolname;
+ } BlobsInfo;
+
/* global decls */
extern bool force_quotes; /* double-quotes for identifiers flag */
extern bool g_verbose; /* verbose flag */
*** base/src/bin/pg_dump/pg_backup_tar.c (revision 2512)
--- base/src/bin/pg_dump/pg_backup_tar.c (working copy)
***
*** 104,110
static const char
Re: [HACKERS] Streaming replication and non-blocking I/O
Fujii Masao wrote: On Tue, Dec 22, 2009 at 2:31 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: 2. Move walreceiver altogether into a loadable module, which is linked as usual to libpq. Like e.g contrib/dblink. Thoughts? Both seem reasonable to me. I tested the 2nd option (see 'replication' branch in my git repository), splitting walreceiver.c into two: the functions that run in the walreceiver process, and the functions that are called from other processes to control walreceiver. That's a quite nice separation, though of course we could do that with the 1st approach as well. Though I seem not to understand what a loadable module means, I wonder how the walreceiver module is loaded. AFAIK, we need to manually install the dblink functions by executing dblink.sql before using them. Likewise, if we choose the 2nd option, we must manually install the walreceiver module before starting replication? I think we can just use load_external_function() to load the library and call WalReceiverMain from AuxiliaryProcessMain(). Ie. hard-code the library name. Walreceiver is quite tightly coupled with the rest of the backend anyway, so I don't think we need to come up with a pluggable API at the moment. That's the way I did it yesterday, see 'replication' branch in my git repository, but it looks like I fumbled the commit so that some of the changes were committed as part of the merge commit with origin/master (=CVS HEAD). Sorry about that. shared_preload_libraries seems like a bad place because the library doesn't need to be loaded in all backends. Just the walreceiver process. -- 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] [PATCH] remove redundant ownership checks
(2009/12/21 12:53), Robert Haas wrote: On Thu, Dec 17, 2009 at 7:19 PM, Tom Lanet...@sss.pgh.pa.us wrote: KaiGai Koheikai...@ak.jp.nec.com writes: [ patch to remove EnableDisableRule's permissions check ] I don't particularly like this patch, mainly because I disagree with randomly removing permissions checks without any sort of plan about where they ought to go. So where ought they to go? If we're going to start moving these checks around we need a very well-defined notion of where permissions checks should be made, so that everyone knows what to expect. I have not seen any plan for that. This seems to me to get right the heart of the matter. When I submitted my machine-readable explain patch, you critiqued it for implementing half of an abstraction layer, and it seems to me that our current permissions-checking logic has precisely the same issue. We consistently write code that starts by checking permissions and then moves right along to implementing the action. Those two things need to be severed. I see two ways to do this. Given a function that (A) does some prep work, (B) checks permissions, and (C) performs the action, we could either: 1. Make the existing function do (A) and (B) and then call another function to do (C), or 2. Make the existing function do (A), call another function to do (B), and then do (C) itself. I'm not sure which will work better, but I think making a decision about which way to do it and how to name the functions would be a big step towards having a coherent plan for this project. We have mixed policy in the current implementation. The point is what database object shall be handled in this function. If we consider a rewrite rule as a database object, not a property of the relation, it seems to me a correct manner to apply permission checks in the EnableDisableRule(), because it handles a given rewrite rule. If we consider a rewrite rule as a property of a relation, not an independent database object, it seems to me we should apply permission checks in ATPrepCmd() which handles a relation, rather than EnableDisableRule(). My patch stands on the later perspective, because pg_rewrite entries don't have its own ownership and access privileges, and it is always owned by a certain relation. Thanks, A related issue is where parse analysis should be performed. We're not completely consistent about this right now. Most of it seems to be done by code in the parser directory, but there are several exceptions, including DefineRule(). ...Robert -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.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] Streaming replication and non-blocking I/O
On Tue, Dec 22, 2009 at 3:30 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I think we can just use load_external_function() to load the library and call WalReceiverMain from AuxiliaryProcessMain(). Ie. hard-code the library name. Walreceiver is quite tightly coupled with the rest of the backend anyway, so I don't think we need to come up with a pluggable API at the moment. That's the way I did it yesterday, see 'replication' branch in my git repository, but it looks like I fumbled the commit so that some of the changes were committed as part of the merge commit with origin/master (=CVS HEAD). Sorry about that. Umm.., I still cannot find the place where the walreceiver module is loaded by using load_external_function() in your 'replication' branch. Also the compilation of that branch fails. Is the 'pushed' branch the latest? Sorry if I'm missing something. 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
