Re: [HACKERS] Predicate locking
On Tue, May 03, 2011 at 01:36:36PM +0900, Vlad Arkhipov wrote: > Then I commited the both and the second one raised an exception: > ERROR: could not serialize access due to read/write dependencies among > transactions > SQL state: 40001 > > However the second transaction does not access the records that the > first one does. If I had predicate locks I could avoid this situation by > locking the records with the specified id. Yes, you're right -- the current implementation of SSI only locks indexes at the granularity of index pages. So although those transactions don't actually access the same records, they're detected as a conflict because they're on the same index page. Of course, on a larger table this might be less likely to happen. Getting this down to index-key and index-gap lock granularity is on the todo list. Our focus in the initial SSI development has been to get something that's functionally correct and stable before optimizing it. I'm hoping to get some time to work on index-key locking for 9.2, as I expect it will make a significant performance difference. Dan -- Dan R. K. Ports MIT CSAILhttp://drkp.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] DLL export with mingw-w64: currently a no-op
Hi PostgreSQL Hackers, When compiling PG with mingw-w64 the PGDLLEXPORT macro is blank. Here is a patch that "fixes it for me." If this is correct, I'd appreciate it will be applied to 9.0.x as well as HEAD. -- Johann Oskarssonhttp://www.2ndquadrant.com/|[] PostgreSQL Development, 24x7 Support, Training and Services --+-- | Blog: http://my.opera.com/myrkraverk/blog/ pg_dllexport.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extreme bloating of intarray GiST indexes
Josh Berkus writes: > Tom, Alexander, > So we are using gist_intbig_ops, so that's not the issue. > Using pgstattuple might be a bit of a challenge. The client doesn't > have it installed, and I can't pull it from Yum without also upgrading > PostgreSQL, since Yum doesn't stock old versions AFAIK. And updating Postgres to latest minor release is a bad thing why? I can't believe you're not holding your client's feet to the fire about running an old version, quite independently of the fact that they need that contrib module. But having said that, what you say makes no sense at all. They have intarray installed, so they have postgresql-contrib. I know of no Yum-accessible distributions in which intarray and pgstattuple wouldn't be delivered in the same RPM. 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] Predicate locking
30.04.2011 22:18, Kevin Grittner wrote: Vlad Arkhipov wrote: 29.04.2011 21:18, Kevin Grittner wrote: Vlad Arkhipov wrote: But even if it would work it would not help me anyways. Because my constraint is much more complex and depends on other tables, I cannot express it in terms of exclusion constraints. Are you aware of the changes to the SERIALIZABLE transaction isolation level in the upcoming 9.1 release? http://wiki.postgresql.org/wiki/Serializable http://wiki.postgresql.org/wiki/SSI If you can wait for that, it might be just what you're looking for. I would not like to make the whole transaction serializable because of performance and concurrency reasons. I'm curious -- what do you expect the performance and concurrency impact to be? You do realize that unlike SELECT FOR UPDATE, SERIALIZABLE in PostgreSQL 9.1 will not cause any blocking beyond what is there in READ COMMITTED, right? Does 9.1beta contain the new SERIALIZABLE isolation level? If so, I can show you some concurrency issues. First I created a table: create table t (id bigint, value bigint); insert into t values (1, 1); insert into t values (2, 1); create index t_idx on t(id); Then I started two transactions. 1. begin transaction; set transaction isolation level serializable; select * from t where id = 2; // and do some logic depending on this result insert into t (id, value) values (-2, 1); 2. begin transaction; set transaction isolation level serializable; select * from t where id = 3; // and do some logic depending on this result insert into t (id, value) values (-3, 0); Then I commited the both and the second one raised an exception: ERROR: could not serialize access due to read/write dependencies among transactions SQL state: 40001 However the second transaction does not access the records that the first one does. If I had predicate locks I could avoid this situation by locking the records with the specified id. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] A small step towards more organized beta testing
> For filter purpose, could have the form a 32bits/64bits choice? That would go into the "platform details" field. Adding new fields in Googledocs is problematic, so I'd rather not add one at this point, and spend my time on replacing it with a Django app instead. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extreme bloating of intarray GiST indexes
Tom, Alexander, So we are using gist_intbig_ops, so that's not the issue. Using pgstattuple might be a bit of a challenge. The client doesn't have it installed, and I can't pull it from Yum without also upgrading PostgreSQL, since Yum doesn't stock old versions AFAIK. Maybe we should consider making diagnostic utilities like this standard with PostgreSQL? -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Select For Update and Left Outer Join
On May 1, 2011, at 12:27 PM, Patrick Earl wrote: > In ORMs like NHibernate, there are a few strategies for mapping > inheritance to SQL. One of these is "Joined Subclass," which allows > for the elimination of duplicate data and clean separation of class > contents. > > With a class hierarchy such as this: > > Pet > Dog : Pet > Cat : Pet > > The query to get all the pets is as follows: > > select * from Pet > left join Dog on Dog.Id = Pet.Id > left join Cat on Cat.Id = Pet.Id Since FOR UPDATE seems to be a dead end here... Is that construct something that NHibernate natively understands? If so, could you use Postgres table inheritance instead of joins? -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] FDW table hints
On Mon, May 02, 2011 at 09:27:08PM +0200, Magnus Hagander wrote: > postgres=# DROP TABLE FOO; > ERROR: "foo" is not a table > HINT: Use DROP FOREIGN TABLE to remove a foreign table. > postgres=# CREATE INDEX baz ON foo(bar); > ERROR: "foo" is not a table > > To some, that would be confusing - foo kind of is a table, just a > different kind. Should we have some HINT on that one as well? Until we can actually create indexes on foreign tables, yes ;) Cheers, David (Local indexes? Foreign indexes? Both?) -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HTML tags :/
On Mon, May 02, 2011 at 04:56:42PM -0300, Alvaro Herrera wrote:
> Excerpts from David Fetter's message of lun may 02 14:30:15 -0300 2011:
> > On Mon, May 02, 2011 at 12:15:09PM -0300, Alvaro Herrera wrote:
>
> > > I think it'd be good to have id attrs in all the sect2 sections of that
> > > chapter.
> >
> > By "that chapter," do you mean everything in func.sgml, or just the
> > stuff in the ?
>
> Well, I mean the chapter:
>
>
>
> There aren't that many sect2's missing the id (about one third of them
> are in functions-conditional). The ones in functions-subquery could be
> problematic though.
Please find attached a patch adding IDs to the appropriate (I think)
spots.
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 633f215..657835c 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -8717,7 +8717,7 @@ CREATE TYPE rainbow AS ENUM ('red', 'orange', 'yellow',
'green', 'blue', 'purple
with configure --with-libxml.
-
+
Producing XML Content
@@ -9093,7 +9093,7 @@ SELECT xmlagg(x) FROM (SELECT * FROM test ORDER BY y
DESC) AS tab;
-
+
XML Predicates
@@ -9854,7 +9854,7 @@ SELECT setval('foo', 42, false);Next
nextval wi
-
+
CASE
@@ -9966,7 +9966,7 @@ SELECT ... WHERE CASE WHEN x <> 0 THEN y/x > 1.5
ELSE false END;
-
+
COALESCE
@@ -10005,7 +10005,7 @@ SELECT COALESCE(description, short_description,
'(none)') ...
-
+
NULLIF
@@ -10034,7 +10034,7 @@ SELECT NULLIF(value, '(none)') ...
-
+
GREATEST and LEAST
@@ -11492,7 +11492,7 @@ SELECT xmlagg(x) FROM (SELECT x FROM test ORDER BY y
DESC) AS tab;
Boolean (true/false) results.
-
+
EXISTS
@@ -11542,7 +11542,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 =
tab1.col2);
-
+
IN
@@ -11598,7 +11598,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 =
tab1.col2);
-
+
NOT IN
@@ -11654,7 +11654,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 =
tab1.col2);
-
+
ANY/SOME
@@ -11719,7 +11719,7 @@ WHERE EXISTS (SELECT 1 FROM tab2 WHERE col2 =
tab1.col2);
-
+
ALL
--
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] Bad COMPACT_ALLOC_CHUNK size in tsearch/spell.c?
Merlin Moncure writes: > On Mon, May 2, 2011 at 11:09 AM, Tom Lane wrote: >> I did a bit of testing of this and committed it with minor adjustments. > Thanks for the attribution -- I hardly deserved it. One question > though: ALLOC_CHUNK_FRACTION was put to four with the language 'We > allow chunks to be at most 1/4 of maxBlockSize'. > further down we have: > "+* too. For the typical case of maxBlockSize a power of 2, the chunk > size > +* limit will be at most 1/8th maxBlockSize, so that given a stream of > +* requests that are all the maximum chunk size we will waste at most > +* 1/8th of the allocated space." > Is this because the divide by 2 right shift halves the amount of > wasted space, so that the maximum waste is in fact half again the > fraction? No, it's the overhead. The patch as you submitted it was forcing allocChunkSize down to 512, because after subtracting off the per-malloc-block overhead and the per-palloc-chunk overhead, it came to the (correct) conclusion that 1024 didn't quite fit 8 times into 8192. I thought that was probably excessive, so I backed off the fraction. 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] HTML tags :/
Excerpts from David Fetter's message of lun may 02 14:30:15 -0300 2011: > On Mon, May 02, 2011 at 12:15:09PM -0300, Alvaro Herrera wrote: > > I think it'd be good to have id attrs in all the sect2 sections of that > > chapter. > > By "that chapter," do you mean everything in func.sgml, or just the > stuff in the ? Well, I mean the chapter: There aren't that many sect2's missing the id (about one third of them are in functions-conditional). The ones in functions-subquery could be problematic though. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad COMPACT_ALLOC_CHUNK size in tsearch/spell.c?
On Mon, May 2, 2011 at 11:09 AM, Tom Lane wrote: > Merlin Moncure writes: >> On Tue, Apr 26, 2011 at 3:19 PM, Merlin Moncure wrote: >>> On Tue, Apr 26, 2011 at 1:48 PM, Tom Lane wrote: After chewing on that thought for a bit, it seems like an easy fix is to modify AllocSetContextCreate (around line 390 in HEAD's aset.c) so that allocChunkLimit is not just constrained to be less than maxBlockSize, but significantly less than maxBlockSize --- say an eighth or so. > >>> well, +1 on any solution that doesn't push having to make assumptions >>> about the allocator from the outside. your fix seems to nail it >>> without having to tinker around with the api which is nice. (plus you >>> could just remove the comment). >>> >>> Some perfunctory probing didn't turn up any other cases like this. > >> patch attached -- I did no testing beyond make check though. I >> suppose changes to the allocator are not to be take lightly and this >> should really be tested in some allocation heavy scenarios. > > I did a bit of testing of this and committed it with minor adjustments. Thanks for the attribution -- I hardly deserved it. One question though: ALLOC_CHUNK_FRACTION was put to four with the language 'We allow chunks to be at most 1/4 of maxBlockSize'. further down we have: "+* too. For the typical case of maxBlockSize a power of 2, the chunk size +* limit will be at most 1/8th maxBlockSize, so that given a stream of +* requests that are all the maximum chunk size we will waste at most +* 1/8th of the allocated space." Is this because the divide by 2 right shift halves the amount of wasted space, so that the maximum waste is in fact half again the fraction? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FDW table hints
postgres=# DROP TABLE FOO; ERROR: "foo" is not a table HINT: Use DROP FOREIGN TABLE to remove a foreign table. postgres=# CREATE INDEX baz ON foo(bar); ERROR: "foo" is not a table To some, that would be confusing - foo kind of is a table, just a different kind. Should we have some HINT on that one as well? -- 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] A small step towards more organized beta testing
2011/5/2 Josh Berkus : > Hackers, > > I've replaced test-report-by-email with a GoogleDocs application for Beta1. > > The form for submitting test reports is here: > > https://spreadsheets.google.com/viewform?hl=en&formkey=dEh3WEwzOFhKWWw4dHdRS2VQTExRdVE6MQ&ifq [... cut ...] It's very good Josh. For filter purpose, could have the form a 32bits/64bits choice? -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br -- 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 clang report
Peter Eisentraut writes: > On ons, 2011-02-09 at 20:30 +0200, Peter Eisentraut wrote: >> Regression tests (world): >> >> --- src/test/regress/expected/float8.out >> +++ src/test/regress/results/float8.out >> @@ -384,7 +384,15 @@ >> SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; >> ERROR: value out of range: overflow >> SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; >> -ERROR: value out of range: overflow >> + bad | ?column? >> +-+-- >> + |0 >> + | NaN >> + | NaN >> + | NaN >> + | NaN >> +(5 rows) >> + >> SELECT 0 ^ 0 + 0 ^ 1 + 0 ^ 0.0 + 0 ^ 0.5; >> ?column? >> -- > So issue here is actually that clang has an option >-fmath-errno >Indicate that math functions should be treated as updating errno. Really? It looks to me like the issue is that pow() is returning NaN instead of Inf for an out-of-range result. That's a bug: the correct result is *not* ill-defined, it's simply too large to represent. If that has anything to do with errno, it's an implementation artifact that's unrelated to the claimed meaning of the switch. But I would also note that the Single Unix Spec is unequivocal about this case: If the correct value would cause overflow, +-HUGE_VAL is returned, and errno is set to [ERANGE]. That's "IS set", not "may be set" as in some other cases. So this behavior should not depend on any such compiler switch anyway, unless the intent of the switch is "ignore the standard and do whatever we feel like". 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] A small step towards more organized beta testing
Hackers, I've replaced test-report-by-email with a GoogleDocs application for Beta1. The form for submitting test reports is here: https://spreadsheets.google.com/viewform?hl=en&formkey=dEh3WEwzOFhKWWw4dHdRS2VQTExRdVE6MQ&ifq The accumulated test reports are here: https://spreadsheets.google.com/spreadsheet/pub?hl=en&hl=en&key=0AoeuP3g2YZsFdEh3WEwzOFhKWWw4dHdRS2VQTExRdVE&single=true&gid=0&output=html Instructions are here: http://wiki.postgresql.org/wiki/HowToBetaTest#Reporting_Tests Obviously, this is a temporary solution. I'm working on a Django app to replace it. But for now, it lets us take test reports, and lets hackers view them. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] new clang report
On ons, 2011-02-09 at 20:30 +0200, Peter Eisentraut wrote: > Regression tests (world): > > --- src/test/regress/expected/float8.out > +++ src/test/regress/results/float8.out > @@ -384,7 +384,15 @@ > SELECT '' AS bad, f.f1 * '1e200' from FLOAT8_TBL f; > ERROR: value out of range: overflow > SELECT '' AS bad, f.f1 ^ '1e200' from FLOAT8_TBL f; > -ERROR: value out of range: overflow > + bad | ?column? > +-+-- > + |0 > + | NaN > + | NaN > + | NaN > + | NaN > +(5 rows) > + > SELECT 0 ^ 0 + 0 ^ 1 + 0 ^ 0.0 + 0 ^ 0.5; > ?column? > -- So issue here is actually that clang has an option -fmath-errno Indicate that math functions should be treated as updating errno. If you pass this option, then the regression tests pass. If not, you get the above difference. So the question is, do we a) legislate that -fmath-errno is required, or b) fix dpow() to handle this case somehow (how?), or c) provide an alternative expected file? -- 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] HTML tags :/
On Mon, May 02, 2011 at 12:15:09PM -0300, Alvaro Herrera wrote: > Excerpts from David Fetter's message of lun may 02 10:58:37 -0300 2011: > > On Mon, Apr 18, 2011 at 06:44:03PM -0300, Alvaro Herrera wrote: > > > > The sect2 they are in would need an id attribute for there to be a > > > stable #-style link. > > > > Please find attached a patch to fix this. > > > > I believe there are other places in the docs where an id attribute > > would be handy. Will check those :) > > I think it'd be good to have id attrs in all the sect2 sections of that > chapter. By "that chapter," do you mean everything in func.sgml, or just the stuff in the ? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: PostgreSQL 9.1 beta1 disponível para testes.
Sorry, wrong list.. apologizes. Em 2 de maio de 2011 13:56, Dickson S. Guedes escreveu: > Olá povo, tudo bem? > > Que tal ajudarmos a testar esta versão candidata? > > Visite [1] e [2] para mais informações > > [1] http://www.postgresql.org/developer/beta > [2] http://wiki.postgresql.org/wiki/HowToBetaTest -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PostgreSQL 9.1 beta1 disponível para testes.
Olá povo, tudo bem? Que tal ajudarmos a testar esta versão candidata? Visite [1] e [2] para mais informações [1] http://www.postgresql.org/developer/beta [2] http://wiki.postgresql.org/wiki/HowToBetaTest -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br -- 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] Proposed patch: Smooth replication during VACUUM FULL
Il 02/05/11 18:20, Simon Riggs ha scritto:
I'm sure Gabriele can add those things as well - that also looks like
another 1 line change.
Yes, today we have performed some tests with that patch as well
(attached is version 2). The version 2 of the patch (which includes the
change Tom suggested on Saturday), smooths the process even more.
You can look at the attached graph for now - even though we are
currently relaunching a test with all 3 different versions from scratch
(unpatched, patch v1 and patch v2), with larger data in order to confirm
this behaviour.
I'm just observing that the patch as-is appears effective and I feel
it is important.
Exactly. One thing also important to note as well is that with the
vacuum delay being honoured, "vacuum full" operations in a SyncRep
scenario take less time as well - as the load is more distributed over time.
You can easily spot in the graphs the point where VACUUM FULL
terminates, then it is just a matter of flushing the WAL delay for
replication.
Anyway, I hope I can give you more detailed information tomorrow. Thanks.
Cheers,
Gabriele
--
Gabriele Bartolini - 2ndQuadrant Italia
PostgreSQL Training, Services and Support
gabriele.bartol...@2ndquadrant.it | www.2ndQuadrant.it
<>diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index bcc7d1e..fa3d22f 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -894,7 +894,8 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid
OIDOldIndex,
Buffer buf;
boolisdead;
- CHECK_FOR_INTERRUPTS();
+ /* Launches vacuum delay */
+ vacuum_delay_point();
if (indexScan != NULL)
{
@@ -1012,7 +1013,7 @@ copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid
OIDOldIndex,
HeapTuple tuple;
boolshouldfree;
- CHECK_FOR_INTERRUPTS();
+ vacuum_delay_point();
tuple = tuplesort_getheaptuple(tuplesort, true,
&shouldfree);
if (tuple == NULL)
--
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] Proposed patch: Smooth replication during VACUUM FULL
On Mon, May 2, 2011 at 5:20 PM, Simon Riggs wrote: >> Yeah, it would help during the initial scan >> of the old rel, but not during the sort or reindex steps. > > As Greg points out, the sort is not really of concern (for now). Though I was surprised the reindex isn't an equally big problem. It might matter a lot what the shape of the schema is. If you have lots of indexes the index wal might be larger than the table rebuild. -- 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] Copy/paste from psql - was: Changing the continuation-line prompt in psql?
Excerpts from Alastair Turner's message of sáb abr 30 05:10:40 -0300 2011: > Extending the history command (\s) sounds more promising > \s- for a reverse ordered history > \s[n] for the last n or n-from-last-th (\s1 different from \p in that > it shows the last completed query not the one in progress) > > and most importantly showing full history through a less-style > interface like large result sets rather than in the flow of psql I agree that \s needs a bit of a whack, regardless of anything done to the prompts. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed patch: Smooth replication during VACUUM FULL
On Mon, May 2, 2011 at 3:37 PM, Tom Lane wrote: > Simon Riggs writes: >> I can't see the objection to replacing something inadvertently removed >> in 9.0, especially since it is a 1 line patch and is accompanied by >> copious technical evidence. > > I am not sure which part of "this isn't a substitute for what happened > before 9.0" you fail to understand. > > As for "copious technical evidence", I saw no evidence provided > whatsoever that this patch really did anything much to fix the > reported problem. Just so we're looking at the same data, graph attached. > Yeah, it would help during the initial scan > of the old rel, but not during the sort or reindex steps. As Greg points out, the sort is not really of concern (for now). > (And as for the thoroughness of the technical analysis, the patch > doesn't even catch the second CHECK_FOR_INTERRUPTS in copy_heap_data; > which would at least provide some relief for the sort part of the > problem, though only in the last pass of sorting.) I'm sure Gabriele can add those things as well - that also looks like another 1 line change. I'm just observing that the patch as-is appears effective and I feel it is important. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services <> -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad COMPACT_ALLOC_CHUNK size in tsearch/spell.c?
Merlin Moncure writes: > On Tue, Apr 26, 2011 at 3:19 PM, Merlin Moncure wrote: >> On Tue, Apr 26, 2011 at 1:48 PM, Tom Lane wrote: >>> After chewing on that thought for a bit, it seems like an easy fix is to >>> modify AllocSetContextCreate (around line 390 in HEAD's aset.c) so that >>> allocChunkLimit is not just constrained to be less than maxBlockSize, >>> but significantly less than maxBlockSize --- say an eighth or so. >> well, +1 on any solution that doesn't push having to make assumptions >> about the allocator from the outside. your fix seems to nail it >> without having to tinker around with the api which is nice. (plus you >> could just remove the comment). >> >> Some perfunctory probing didn't turn up any other cases like this. > patch attached -- I did no testing beyond make check though. I > suppose changes to the allocator are not to be take lightly and this > should really be tested in some allocation heavy scenarios. I did a bit of testing of this and committed it with minor adjustments. 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] Proposed patch: Smooth replication during VACUUM FULL
On Mon, May 2, 2011 at 3:37 PM, Tom Lane wrote: > As for "copious technical evidence", I saw no evidence provided > whatsoever that this patch really did anything much to fix the > reported problem. Yeah, it would help during the initial scan > of the old rel, but not during the sort or reindex steps. > Well if Simon's right that it's a question of generating an overwhelming amount of wal rather than saturating the local i/o then the sort isn't relevant. I'm not sure of what the scale of wal from the reindex operation is compared to the table rebuild. Of course you would have same problem doing a COPY load or even just doing a sequential scan of a recently loaded table. Or is there something about table rebuilds that is particularly nasty? -- 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] HTML tags :/
Excerpts from David Fetter's message of lun may 02 10:58:37 -0300 2011: > On Mon, Apr 18, 2011 at 06:44:03PM -0300, Alvaro Herrera wrote: > > The sect2 they are in would need an id attribute for there to be a > > stable #-style link. > > Please find attached a patch to fix this. > > I believe there are other places in the docs where an id attribute > would be handy. Will check those :) I think it'd be good to have id attrs in all the sect2 sections of that chapter. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposed patch: Smooth replication during VACUUM FULL
Excerpts from Bernd Helmle's message of sáb abr 30 19:40:00 -0300 2011: > > > --On 30. April 2011 20:19:36 +0200 Gabriele Bartolini > wrote: > > > I have noticed that during VACUUM FULL on reasonably big tables, replication > > lag climbs. In order to smooth down the replication lag, I propose the > > attached patch which enables vacuum delay for VACUUM FULL. > > Hmm, but this will move one problem into another. You need to hold exclusive > locks longer than necessary and given that we discourage the regular use of > VACUUM FULL i cannot see a real benefit of it... With the 8.4 code you had the possibility of doing so, if you so wished. It wasn't enabled by default. (Say you want to vacuum a very large table that is not critical to operation; so you can lock it for a long time without trouble, but you can't have this vacuum operation cause delays in the rest of the system due to excessive I/O.) The argument seems sane to me. I didn't look into the details of the patch though. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] (Better) support for cross compiled external modules
Hi all, Is it possible to add support for cross compiled PGXS modules to the build system? That is, when PG is cross compiled, a -pg_config is also built for use with external modules? I'm not adverse to submit a patch for this myself, but would like a pointer in the general direction for it. -- Johann Oskarssonhttp://www.2ndquadrant.com/|[] PostgreSQL Development, 24x7 Support, Training and Services --+-- | Blog: http://my.opera.com/myrkraverk/blog/ -- 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] Proposed patch: Smooth replication during VACUUM FULL
Simon Riggs writes: > I can't see the objection to replacing something inadvertently removed > in 9.0, especially since it is a 1 line patch and is accompanied by > copious technical evidence. I am not sure which part of "this isn't a substitute for what happened before 9.0" you fail to understand. As for "copious technical evidence", I saw no evidence provided whatsoever that this patch really did anything much to fix the reported problem. Yeah, it would help during the initial scan of the old rel, but not during the sort or reindex steps. (And as for the thoroughness of the technical analysis, the patch doesn't even catch the second CHECK_FOR_INTERRUPTS in copy_heap_data; which would at least provide some relief for the sort part of the problem, though only in the last pass of sorting.) 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] HTML tags :/
On Mon, Apr 18, 2011 at 06:44:03PM -0300, Alvaro Herrera wrote: > Excerpts from David Fetter's message of lun abr 18 18:34:11 -0300 2011: > > Folks, > > > > While readjusting pg_docbot's URLs for LEAST and GREATEST, I came > > across an infelicity. They'd been tagged as > > "http://www.postgresql.org/docs/current/static/functions-conditional.html#AEN12680"; > > and I re-tagged them as > > "http://www.postgresql.org/docs/current/static/functions-conditional.html#AEN15582"; > > > > I didn't see a more descriptive tag. Am I missing something > > important? > > The sect2 they are in would need an id attribute for there to be a > stable #-style link. Please find attached a patch to fix this. I believe there are other places in the docs where an id attribute would be handy. Will check those :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 633f215..14ac073 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -10034,7 +10034,7 @@ SELECT NULLIF(value, '(none)') ... - + GREATEST and LEAST -- 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] SYSTEM_IDENTIFY fields was:(Re: [COMMITTERS] pgsql: Include more status information in walsender results)
On Sat, Apr 30, 2011 at 03:13, Jaime Casanova wrote: > On Thu, Feb 3, 2011 at 7:56 AM, Magnus Hagander wrote: >> Include more status information in walsender results >> >> Add the current xlog insert location to the response of >> IDENTIFY_SYSTEM > > why was this third field added to SYSTEM_IDENTIFY? can't find any > place where it's used... > not even on BaseBackup() before the call to SYSTEM_IDENTIFY was removed I believe this was discussed before the patch was committed, but here is the short version: It is required for the streaming client. It didn't make it into 9.1, but given that it's a very useful tool outside it, I think we should still keep the functionality in the server. Prior to this, that client required two separate logins, once to get the current xlog location and then another one to do the streaming. With this, the information is available over the streaming protocol alone. -- 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] Proposed patch: Smooth replication during VACUUM FULL
On Mon, May 2, 2011 at 7:44 AM, Greg Stark wrote: > On Sun, May 1, 2011 at 9:31 PM, Simon Riggs wrote: >> I don't think the performance of replication is at issue. This is >> about resource control. >> > > The unspoken question here is why would replication be affected by i/o > load anyways? It's reading data file buffers that have only recently > been written and should be in cache. I wonder if this system has > chosen O_DIRECT or something like that for writing out wal? It's not, that is a misunderstanding in the thread. It appears that the sheer volume of WAL being generated slows down replication. I would guess it's the same effect as noticing a slow down on web traffic when somebody is watching streaming video. The requested solution is the same as the network case: rate limit the task using too much resource, if the user requests that. I can't see the objection to replacing something inadvertently removed in 9.0, especially since it is a 1 line patch and is accompanied by copious technical evidence. Sure, we can do an even better job in a later release. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] clog_redo causing very long recovery time
I'm working with a client that uses Postgres on what amounts to an
appliance.
The database is therefore subject to occasional torture such as, in this
particular case, running out of disk space while performing a million
plus queries (of mixed varieties, many using plpgsql with exception
handling -- more on that later), and eventually being power-cycled. Upon
restart, clog_redo was called approx 885000 times (CLOG_ZEROPAGE) during
recovery, which took almost 2 hours on their hardware. I should note
that this is on Postgres 8.3.x.
After studying the source, I can only see one possible way that this
could have occurred:
In varsup.c:GetNewTracsactionId(), ExtendCLOG() needs to succeed on a
freshly zeroed clog page, and ExtendSUBTRANS() has to fail. Both of
these calls can lead to a page being pushed out of shared buffers and to
disk, so given a lack of disk space, sufficient clog buffers, but lack
of subtrans buffers, this could happen. At that point the transaction id
does not get advanced, so clog zeros the same page, extendSUBTRANS()
fails again, rinse and repeat.
I believe in the case above, subtrans buffers were exhausted due to the
extensive use of plpgsql with exception handling.
I can simulate this failure with the attached debug-clog patch which
makes use of two pre-existing debug GUCs to selectively interject an
ERROR in between calls to ExtendCLOG() and ExtendSUBTRANS(). If you want
to test this yourself, apply this patch and use the function in
test_clog.sql to generate a million or so transactions. After the first
32K or before (based on when clog gets its first opportunity to zero a
new page) you should start seeing messages about injected ERRORs. Let a
few hundred thousand ERRORs go by, then kill postgres. Recovery will
take ages, because clog_redo is calling fsync hundreds of thousands of
times in order to zero the same page over and over.
The attached fix-clogredo diff is my proposal for a fix for this.
Thoughts/alternatives, etc appreciated.
Thanks,
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
diff --git a/src/backend/access/transam/clog.c
b/src/backend/access/transam/clog.c
index 52224b1..317bc2e 100644
--- a/src/backend/access/transam/clog.c
+++ b/src/backend/access/transam/clog.c
@@ -36,6 +36,7 @@
#include "access/slru.h"
#include "access/transam.h"
#include "postmaster/bgwriter.h"
+#include "utils/guc.h"
/*
* Defines for CLOG page sizes. A page is the same BLCKSZ as is used
@@ -355,6 +356,9 @@ ExtendCLOG(TransactionId newestXact)
/* Zero the page and make an XLOG entry about it */
ZeroCLOGPage(pageno, true);
+ /* steal this variable for test -- means we've been here */
+ Debug_print_rewritten = true;
+
LWLockRelease(CLogControlLock);
}
diff --git a/src/backend/access/transam/varsup.c
b/src/backend/access/transam/varsup.c
index 8838d42..e55a67b 100644
--- a/src/backend/access/transam/varsup.c
+++ b/src/backend/access/transam/varsup.c
@@ -21,6 +21,7 @@
#include "storage/pmsignal.h"
#include "storage/proc.h"
#include "utils/builtins.h"
+#include "utils/guc.h"
/* Number of OIDs to prefetch (preallocate) per XLOG write */
@@ -107,6 +108,11 @@ GetNewTransactionId(bool isSubXact)
* Extend pg_subtrans too.
*/
ExtendCLOG(xid);
+ if (Debug_print_rewritten && Debug_pretty_print)
+ {
+ Debug_print_rewritten = false;
+ elog(ERROR,"injected ERROR");
+ }
ExtendSUBTRANS(xid);
/*
diff -cNr postgresql-8.3.13.orig/src/backend/access/transam/clog.c
postgresql-8.3.13/src/backend/access/transam/clog.c
*** postgresql-8.3.13.orig/src/backend/access/transam/clog.cTue Dec 14
03:51:20 2010
--- postgresql-8.3.13/src/backend/access/transam/clog.c Thu Apr 28 12:04:52 2011
***
*** 74,79
--- 75,81
#define ClogCtl (&ClogCtlData)
+ static int last_pageno = -1;
static intZeroCLOGPage(int pageno, bool writeXlog);
static bool CLOGPagePrecedes(int page1, int page2);
***
*** 471,476
--- 476,488
memcpy(&pageno, XLogRecGetData(record), sizeof(int));
+ /* avoid repeatedly zeroing the same page */
+ if (InRecovery && pageno == last_pageno)
+ return;
+
+ /* save state */
+ last_pageno = pageno;
+
LWLockAcquire(CLogControlLock, LW_EXCLUSIVE);
slotno = ZeroCLOGPage(pageno, false);
create language plpgsql;
\i /path/to/share/contrib/dblink.sql
CREATE OR REPLACE FUNCTION test_clog(howmany int) RETURNS int AS $_$
DECLARE
i int;
arr text[];
dbname text;
BEGIN
dbname := current_database();
arr := dblink_get_connections();
IF arr IS NOT NULL THEN
PERFORM dblink_disconnect('conn');
END IF;
EXECUTE $$SELECT dblink_connect('conn','dbname=$$ || dbname || $$')$$;
PERFORM db
Re: [HACKERS] Proposed patch: Smooth replication during VACUUM FULL
On Sun, May 1, 2011 at 9:31 PM, Simon Riggs wrote: > I don't think the performance of replication is at issue. This is > about resource control. > The unspoken question here is why would replication be affected by i/o load anyways? It's reading data file buffers that have only recently been written and should be in cache. I wonder if this system has chosen O_DIRECT or something like that for writing out wal? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
