Re: [HACKERS] GSoC 2011 - Mentors? Projects?
Le 26/03/2011 02:43, Tomas Vondra a écrit : Dne 26.3.2011 02:05, Joshua Berkus napsal(a): Tomas, I spoke to a teacher from a local university last week, mainly as we were looking for a place where a local PUG could meet regularly. I realized this could be a good opportunity to head-hunt some students to participate in this GSoC. Are we still interested in new students? Yes, please! We have had students from Charles University several times before, and would be glad to have more. The wiki page has links to the information about the program. Talk to Zdenek if you have more questions. I know Zdenek was mentoring some students in the previous years, but he's been a bit hard to reach recently. And the deadline is near. I've read some info about the program on a wiki, but I'm not sure what should the students do. Let's say they will read the list of project ideas on the wiki, and they'll choose one or two of them. What should they do next? Should they write to the pgsql-students mailing list? They could write to the pgsql-students list. There are already some threads about items to work on. I guess most of the students won't have much experience with PostgreSQL, and most of the ideas is described just very briefly, so they'll need help with the proposal. Sure. Two lists AFAICT, pgsql_students and pgsql-hackers. -- Guillaume http://www.postgresql.fr http://dalibo.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] Can I check if somebody is superuser in stored procedure?
Hello 2011/3/28 David Fetter da...@fetter.org: On Sun, Mar 27, 2011 at 03:21:18PM +0200, Pavel Stehule wrote: Hello Is there some simple possibility to check a rights from stored procedure? Well, there's the catalog lookup method: SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname=$1 AND rolsuper) Is that what you had in mind? I found this too, but it isn't what I searched - I searched a some exported function based on internal cache. For my purpose is this solution enough. Regards Pavel Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ExecEvalVar does not have appropriate ExprState?
Hi,
I see the ExecInitExpr says something like this:
case T_Var:
state = (ExprState *) makeNode(ExprState);
state-evalfunc = ExecEvalVar;
---
But the ExecEvalVar function definition says:
Var*variable = (Var *) exprstate-expr;
TupleTableSlot *slot;
AttrNumber attnum;
if (isDone)
*isDone = ExprSingleResult;
/* Get the input slot and attribute number we want */
switch (variable-varno)
{
case INNER: /* get the tuple from
the inner node */
-
Since ExprState - expr in its final form would finally contain only:
NodeTag type;
I think that the pointer being cast in the ExecEvalVar is actually
already a form of Var which is passed around as Expr for sake of
function call.
So, was the node in the Expr tree for the corresponding ExprState node
of the ExprState tree actually a 'Var'? I think without this being the
reality, the function ExecEvalVar would crash! In my belief, the similar
fact would stand for a few other nodes, notably Const, Param,
CoerceToDomain and CaseTest.
Also, I have tried, but failed to fidn the exact place where the Expr
tree is created. Just pointing me to the file / function which does this
would be of great help.
Kindly correct me if I am wrong.
Regards,
Vaibhav
--
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] ExecEvalVar does not have appropriate ExprState?
On 28.03.2011 10:02, Vaibhav Kaushal wrote:
Hi,
I see the ExecInitExpr says something like this:
case T_Var:
state = (ExprState *) makeNode(ExprState);
state-evalfunc = ExecEvalVar;
---
But the ExecEvalVar function definition says:
Var*variable = (Var *) exprstate-expr;
TupleTableSlot *slot;
AttrNumber attnum;
if (isDone)
*isDone = ExprSingleResult;
/* Get the input slot and attribute number we want */
switch (variable-varno)
{
case INNER: /* get the tuple from
the inner node */
-
Since ExprState - expr in its final form would finally contain only:
NodeTag type;
I think that the pointer being cast in the ExecEvalVar is actually
already a form of Var which is passed around as Expr for sake of
function call.
Right, exprstate-expr is a Var in ExecEvalVar.
So, was the node in the Expr tree for the corresponding ExprState node
of the ExprState tree actually a 'Var'?
Yes.
Also, I have tried, but failed to fidn the exact place where the Expr
tree is created. Just pointing me to the file / function which does this
would be of great help.
A raw expression tree is created in the grammar,
src/backend/parser/gram.y. It is then transformed in parse analysis
phase to the form the planner accepts, in transformExpr(). The planner
can do some further transformations, like replacing immutable function
calls with Consts.
--
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] ExecEvalVar does not have appropriate ExprState?
Thanks for the confirmation. I am happy to have understood some basics of
PG.
Also, I think the similar stands for the Const, Param,
CoerceToDomain and CaseTest nodes, right? They too cast the pointer to
another type.
Thanks for the help Heikki. (I could better call you HL, if you dont mind :P
)
Regards,
Vaibhav
On Mon, Mar 28, 2011 at 1:05 PM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
On 28.03.2011 10:02, Vaibhav Kaushal wrote:
Hi,
I see the ExecInitExpr says something like this:
case T_Var:
state = (ExprState *) makeNode(ExprState);
state-evalfunc = ExecEvalVar;
---
But the ExecEvalVar function definition says:
Var*variable = (Var *) exprstate-expr;
TupleTableSlot *slot;
AttrNumber attnum;
if (isDone)
*isDone = ExprSingleResult;
/* Get the input slot and attribute number we want */
switch (variable-varno)
{
case INNER: /* get the tuple
from the inner node */
-
Since ExprState - expr in its final form would finally contain only:
NodeTag type;
I think that the pointer being cast in the ExecEvalVar is actually
already a form of Var which is passed around as Expr for sake of
function call.
Right, exprstate-expr is a Var in ExecEvalVar.
So, was the node in the Expr tree for the corresponding ExprState node
of the ExprState tree actually a 'Var'?
Yes.
Also, I have tried, but failed to fidn the exact place where the Expr
tree is created. Just pointing me to the file / function which does this
would be of great help.
A raw expression tree is created in the grammar, src/backend/parser/gram.y.
It is then transformed in parse analysis phase to the form the planner
accepts, in transformExpr(). The planner can do some further
transformations, like replacing immutable function calls with Consts.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] ExecEvalVar does not have appropriate ExprState?
On 28.03.2011 10:44, Vaibhav Kaushal wrote: Also, I think the similar stands for the Const, Param, CoerceToDomain and CaseTest nodes, right? They too cast the pointer to another type. Yep. -- 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] SSI bug?
YAMAMOTO Takashi wrote: this psql session was the only activity to the server at this point. [no residual SIReadLock] Right, that's because we were using HASH_ENTER instead of HASH_ENTER_NULL. I've posted a patch which should correct that. sure, with your patch it seems that they turned into different ones. PG_DIAG_SEVERITY: ERROR PG_DIAG_SQLSTATE: 53200 PG_DIAG_MESSAGE_PRIMARY: out of shared memory PG_DIAG_MESSAGE_HINT: You might need to increase max_pred_locks_per_transaction. PG_DIAG_SOURCE_FILE: predicate.c PG_DIAG_SOURCE_LINE: 2049 PG_DIAG_SOURCE_FUNCTION: CreatePredicateLock Even with the above information it may be far from clear where allocations are going past their maximum, since one HTAB could grab more than its share and starve another which is staying below its maximum. I'll take a look at the possibility of adding a warning or some such when an HTAB expands past its maximum size. I see from your later post that you are running with this patch. Has that reported anything yet? i got nothing except the following one. (in the server log) WARNING: hash table ShmemIndex has more entries than expected DETAIL: The maximum was set to 32 on creation. That doesn't seem likely to be causing the problem, but maybe the allocations for that should be bumped a bit. These results suggest that there is some sort of a leak in the cleanup of the PredicateLockTargetHash HTAB entries. Will look into it. Thanks again. -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] Additional options for Sync Replication
On Sun, Mar 27, 2011 at 11:27 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Mar 27, 2011 at 5:45 PM, Simon Riggs si...@2ndquadrant.com wrote: I was hoping to fine tune/tweak Sync Rep after feedback during beta, but my understanding of current consensus is that that will be too late to make user visible changes. So I'm proposing this change now, before Beta, rather than during Beta. This is completely inappropriate. The deadline for new feature patches has long since passed. It was January 15th. The discussion you are referring to had to do with fixing the behavior of features we already have, not adding new ones. You skipped the bit that said this code was part of the original patch submission, so this code met your deadline. That was stated clearly in the next paragraph of my email. It's also a minor change and one that others had agreed we want. You have no basis on which to prevent this. -- 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] Additional options for Sync Replication
On Sun, Mar 27, 2011 at 11:55 PM, Greg Stark gsst...@mit.edu wrote: Also, for what it's worth I prefer thinking of synchronous_commit/synchronous_replication as one big multi-way variable: synchronous_commit = memory | disk | replica-memory | replica-disk | replica-visible That's close enough to my thinking for me to say yes to. I'd prefer to call it commit_durability though. -- 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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 10:52 AM, Simon Riggs si...@2ndquadrant.com wrote: You have no basis on which to prevent this. It's also already on the Open Items list, put there by you. Why is this even a discussion point? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix plpgsql to release SPI plans when a function or DO block is
On 28/03/11 04:31, Tom Lane wrote: David Fetter da...@fetter.org writes: On Sun, Mar 27, 2011 at 04:51:13PM +, Tom Lane wrote: Fix plpgsql to release SPI plans when a function or DO block is freed. Do the other PLs we ship need similar fixes? Offhand I think the other PLs leave management of prepared plans to the user. If there are any places where they cache plans behind the scenes, maybe a similar fix would be appropriate. FWIW I executed do $$ plpy.execute(select 1 from pg_class) $$ language plpythonu; 10k times in a session and the backend grew a lot in memory and never released it. I can't offhand see where the memory went, I'll try to investigate in the evening. Cheers, Jan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Replication server timeout patch
On 24.03.2011 15:24, Fujii Masao wrote: On Wed, Mar 23, 2011 at 7:33 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: I don't much like the API for this. Walsender shouldn't need to know about the details of the FE/BE protocol, pq_putbytes_if_available() seems too low level to be useful. I think a better API would be to have a non-blocking version of pq_putmessage(). We can make the output buffer in pqcomm.c resizeable, so that when the message doesn't fit in the output buffer in pq_putmessage(), the buffer is enlarged instead of trying to flush it. Attached is a patch using that approach. This is a much smaller patch, and easier to understand. Agreed. Thanks for improving the patch. pq_flush_if_writable() calls internal_flush() without using PG_TRY block. This seems unsafe because for example pgwin32_waitforsinglesocket() called by secure_write() can throw ERROR. Perhaps it's time to give up on the assumption that the socket is in blocking mode except within those two functions. Attached patch adds the pq_set_nonblocking() function from your patch, and adds calls to it before all secure_read/write operations to put the socket in the right mode. There's only a few of those operations. Should we use COMMERROR instead of ERROR if we fail to put the socket in the right mode? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index e0ebee6..3192ef7 100644 --- a/doc/src/sgml/config.sgml +++ b/doc/src/sgml/config.sgml @@ -2019,6 +2019,28 @@ SET ENABLE_SEQSCAN TO OFF; /para /listitem /varlistentry + + varlistentry id=guc-replication-timeout xreflabel=replication_timeout + termvarnamereplication_timeout/varname (typeinteger/type)/term + indexterm + primaryvarnamereplication_timeout/ configuration parameter/primary + /indexterm + listitem + para +Specifies the maximum time, in milliseconds, to wait for the reply +from the standby before terminating replication. This is useful for +the primary server to detect the standby crash or network outage. +A value of zero turns this off. This parameter can only be set in +the filenamepostgresql.conf/ file or on the server command line. +The default value is 60 seconds. + /para + para +To make the timeout work properly, xref linkend=guc-wal-receiver-status-interval +must be enabled on the standby, and its value must be less than the +value of varnamereplication_timeout/. + /para + /listitem + /varlistentry /variablelist /sect2 @@ -2216,6 +2238,11 @@ SET ENABLE_SEQSCAN TO OFF; the filenamepostgresql.conf/ file or on the server command line. The default value is 10 seconds. /para + para + When xref linkend=guc-replication-timeout is enabled on the primary, + varnamewal_receiver_status_interval/ must be enabled, and its value + must be less than the value of varnamereplication_timeout/. + /para /listitem /varlistentry diff --git a/src/backend/libpq/pqcomm.c b/src/backend/libpq/pqcomm.c index 3c7b05b..db313a8 100644 --- a/src/backend/libpq/pqcomm.c +++ b/src/backend/libpq/pqcomm.c @@ -55,10 +55,12 @@ * pq_peekbyte - peek at next byte from connection * pq_putbytes - send bytes to connection (not flushed until pq_flush) * pq_flush - flush pending output + * pq_flush_if_writable - flush pending output if writable without blocking * pq_getbyte_if_available - get a byte if available without blocking * * message-level I/O (and old-style-COPY-OUT cruft): * pq_putmessage - send a normal message (suppressed in COPY OUT mode) + * pq_putmessage_noblock - buffer a normal message without blocking (suppressed in COPY OUT mode) * pq_startcopyout - inform libpq that a COPY OUT transfer is beginning * pq_endcopyout - end a COPY OUT transfer * @@ -92,6 +94,7 @@ #include miscadmin.h #include storage/ipc.h #include utils/guc.h +#include utils/memutils.h /* * Configuration options @@ -105,15 +108,21 @@ static char sock_path[MAXPGPATH]; /* - * Buffers for low-level I/O + * Buffers for low-level I/O. + * + * The receive buffer is fixed size. Send buffer is usually 8k, but can be + * enlarged by pq_putmessage_noblock() if the message doesn't fit otherwise. */ -#define PQ_BUFFER_SIZE 8192 +#define PQ_SEND_BUFFER_SIZE 8192 +#define PQ_RECV_BUFFER_SIZE 8192 -static char PqSendBuffer[PQ_BUFFER_SIZE]; +static char *PqSendBuffer; +static int PqSendBufferSize; /* Size send buffer */ static int PqSendPointer; /* Next index to store a byte in PqSendBuffer */ +static int PqSendStart; /* Next index to send a byte in PqSendBuffer */ -static char PqRecvBuffer[PQ_BUFFER_SIZE]; +static char PqRecvBuffer[PQ_RECV_BUFFER_SIZE]; static int PqRecvPointer; /* Next index to read a byte from PqRecvBuffer */ static
Re: [HACKERS] Needs Suggestion
SUBHAM ROY subham@gmail.com writes: I want to know how can we measure the execution time of a query in Postgres (Explain analyze will not do). Also is there any tools available in Linux for measuring the performance of queries of databases such as Oracle 11g, Postgres, etc. Any suggestions will be very helpful. Try pgbench (in contribs) and then Tsung, that could help you run a test suite and get time reports. See also pgbench-tools. http://www.westnet.com/~gsmith/content/postgresql/pgbench-tools.htm Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional options for Sync Replication
On 28.03.2011 13:14, Simon Riggs wrote: On Mon, Mar 28, 2011 at 10:52 AM, Simon Riggssi...@2ndquadrant.com wrote: You have no basis on which to prevent this. It's also already on the Open Items list, put there by you. Why is this even a discussion point? FWIW, I agree this is an additional feature that we shouldn't be messing with at this point in the release cycle. The 'apply' mode would be quite interesting, it would make it easier to build load-balancing clusters. But the patch isn't up to the task on that yet - the 'apply' status report is only sent after wal_receiver_status_interval fills up, so you get long delays. PS. you're missing some break's in the switch-statement in SyncRepWaitForLSN(), effectively making the patch do nothing. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Comments on system tables and columns
Hi, I notice that none of the system tables or columns thereof bear any comments. Is this intentional, or an oversight? I would have thought comments would be useful since the column names aren't exactly always self-explanatory. Thanks -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] psql \dt and table size
--On 26. März 2011 21:59:18 -0400 Robert Haas robertmh...@gmail.com wrote: But I think we can just call pg_table_size() regardless in 9.0+; I believe it'll return the same results as pg_relation_size() on non-tables. Anyone see a problem with that? Hmm yeah, seems i was thinking too complicated...here is a cleaned up version of this idea. -- Thanks Bernd psql_tablesize.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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 6:14 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Mar 28, 2011 at 10:52 AM, Simon Riggs si...@2ndquadrant.com wrote: You have no basis on which to prevent this. It's also already on the Open Items list, put there by you. Huh? There is an open item about whether we should merge synchronous_commit and synchronous_replication into a single GUC, which might be a better interface since it would typically give the user one less thing to have to fiddle with, but there is certainly no open item for adding additional sync rep modes. Merging those two GUCs is a reasonable thing to consider even at this late date, because once we cut beta - and certainly once we cut final - we'll be stuck supporting whatever interface we release for 5+ years. There is no similar risk for this patch - the only risk of not committing this feature now is that we won't have this feature in 9.1. But if we accept that as valid justification for committing it, then everything is fair game, and that is not going to lead to a timely release. Why is this even a discussion point? Adding more new code is likely to add more new bugs, and we're trying not to do that right now, so we can make a release. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 12:48 AM, Fujii Masao masao.fu...@gmail.com wrote: If you want to take hot backup from the standby, you need to do the procedure explained in http://wiki.postgresql.org/wiki/Incrementally_Updated_Backups It'd be nice to improve this in 9.2. Relying on users to get this just right seems both inconvenient and error-prone. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 01:48:13PM +0900, Fujii Masao wrote: In 9.0, recovery doesn't read a backup history file. That FATAL error happens if recovery ends before it reads the WAL record which was generated by pg_stop_backup(). IOW, recovery gets the recovery ending location from WAL record not backup history file. Since you didn't run pg_stop_backup() and there is no WAL record containing the recovery ending location, you got that error. If you want to take hot backup from the standby, you need to do the procedure explained in http://wiki.postgresql.org/wiki/Incrementally_Updated_Backups Is it intentional and/or does it serve some greater good? I mean - ability to make backups on slave without ever bothering master was pretty interesting. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 12:05 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 28.03.2011 13:14, Simon Riggs wrote: On Mon, Mar 28, 2011 at 10:52 AM, Simon Riggssi...@2ndquadrant.com wrote: You have no basis on which to prevent this. It's also already on the Open Items list, put there by you. Why is this even a discussion point? FWIW, I agree this is an additional feature that we shouldn't be messing with at this point in the release cycle. There is an open item about what the UI is for sync commit/sync rep, which is the subject of this patch. The 'apply' mode would be quite interesting, it would make it easier to build load-balancing clusters. But the patch isn't up to the task on that yet - the 'apply' status report is only sent after wal_receiver_status_interval fills up, so you get long delays. Yes it's up to the task, you misread it. It will continue sending replies while apply flush and then it will fall back to the behaviour you mention. PS. you're missing some break's in the switch-statement in SyncRepWaitForLSN(), effectively making the patch do nothing. OK, thanks. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 10:59 AM, Simon Riggs si...@2ndquadrant.com wrote: On Sun, Mar 27, 2011 at 11:55 PM, Greg Stark gsst...@mit.edu wrote: Also, for what it's worth I prefer thinking of synchronous_commit/synchronous_replication as one big multi-way variable: synchronous_commit = memory | disk | replica-memory | replica-disk | replica-visible That's close enough to my thinking for me to say yes to. Patch to implement this new UI attached, exactly as you suggest above. Words can be changed easily without changing the music. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training Services syncrep_ui.v2.cpatch 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] Additional options for Sync Replication
On 28.03.2011 15:34, Simon Riggs wrote: On Mon, Mar 28, 2011 at 12:05 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 28.03.2011 13:14, Simon Riggs wrote: On Mon, Mar 28, 2011 at 10:52 AM, Simon Riggssi...@2ndquadrant.com wrote: You have no basis on which to prevent this. It's also already on the Open Items list, put there by you. Why is this even a discussion point? FWIW, I agree this is an additional feature that we shouldn't be messing with at this point in the release cycle. There is an open item about what the UI is for sync commit/sync rep, which is the subject of this patch. plus new functionality. For the UI part, you just need to change GUCs. The 'apply' mode would be quite interesting, it would make it easier to build load-balancing clusters. But the patch isn't up to the task on that yet - the 'apply' status report is only sent after wal_receiver_status_interval fills up, so you get long delays. Yes it's up to the task, you misread it. It will continue sending replies while apply flush and then it will fall back to the behaviour you mention. There's nothing to wake up the walreceiver after applying a commit record. Oh, you're relying on the periodic wakeups specified by NAPTIME_PER_CYCLE (100ms). That's still on average a 50ms delay to every commit. We should try to eliminate these polling loops, not make them more important. In fact, we should raise NAPTIME_PER_CYCLE to, say, 1000ms, to reduce spurious wakeups on an idle system. Am I reading the patch correctly that if the standby hasn't applied all WAL yet, you send a reply message at every wakeup, whether or not any progress has been made since last time? So if you have a long-running-transaction in the standby, for example, conflicting with WAL recovery, the standby will keep sending a status report to the master every 100ms. -- 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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 1:14 PM, Robert Haas robertmh...@gmail.com wrote: but there is certainly no open item for adding additional sync rep modes. In your opinion. We will have to live with the UI for a long time, yes. I'm trying to get it right, whether that includes adding an obvious/easy omission or renaming things to make better sense. Your other changes make this sensible, and feedback I received after a recent presentation tells me that people will expect it to work with the two additional options. I would prefer further feedback before solidifying this design, but if we must solidify it now, then I prefer to do that with all 5 options. As originally submitted for this commit fest. -- 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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 8:54 AM, Simon Riggs si...@2ndquadrant.com wrote: On Mon, Mar 28, 2011 at 1:14 PM, Robert Haas robertmh...@gmail.com wrote: but there is certainly no open item for adding additional sync rep modes. In your opinion. Well, as you just pointed out yourself a few minutes ago, I did write the open item in question... I fancy I knew what I meant. I would prefer further feedback before solidifying this design, but if we must solidify it now, then I prefer to do that with all 5 options. As originally submitted for this commit fest. Even if it were true that these options were in the patch submitted for this CommitFest, that wouldn't be a reason to commit them now, because the CommitFest is over and has been for several weeks. But it turns out they weren't. http://archives.postgresql.org/message-id/1295127631.3282.100.camel@ebony +/* + * Queuing code is written to allow later extension to multiple + * queues. Currently, we use just one queue (==FSYNC). + * + * XXX We later expect to have RECV, FSYNC and APPLY modes. + */ -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional options for Sync Replication
On 28.03.2011 15:54, Simon Riggs wrote: On Mon, Mar 28, 2011 at 1:14 PM, Robert Haasrobertmh...@gmail.com wrote: but there is certainly no open item for adding additional sync rep modes. In your opinion. Huh? First you say that Robert added an open item about this to the list, he says that the open item wasn't about additional sync rep modes, and you say in your opinion. We will have to live with the UI for a long time, yes. I'm trying to get it right, whether that includes adding an obvious/easy omission or renaming things to make better sense. Your other changes make this sensible, and feedback I received after a recent presentation tells me that people will expect it to work with the two additional options. I would prefer further feedback before solidifying this design, but if we must solidify it now, then I prefer to do that with all 5 options. As originally submitted for this commit fest. It's too late to be doing this. The patch isn't ready to be committed, and there's high potential to introduce new bugs or usability issues. And regarding the UI, I'm not totally convinced that a four-state GUC set in the master is the way go. It would feel at least as logical to control this in the standby. I don't really want to get into that discussion, though. My point is that if we wanted to still sneak this in, then we would have to have those discussions. -1. Let's fix the existing issues, and release. -- 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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 1:51 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: The 'apply' mode would be quite interesting, it would make it easier to build load-balancing clusters. But the patch isn't up to the task on that yet - the 'apply' status report is only sent after wal_receiver_status_interval fills up, so you get long delays. Yes it's up to the task, you misread it. It will continue sending replies while apply flush and then it will fall back to the behaviour you mention. There's nothing to wake up the walreceiver after applying a commit record. Oh, you're relying on the periodic wakeups specified by NAPTIME_PER_CYCLE (100ms). That's still on average a 50ms delay to every commit. Rubbish. Every commit, no way. How could you think that? That delay affects only the last commit of a sequence of commits after which the server goes quiet. And that only applies to people that have specifically chosen to wait for the apply, which as you say means they may have fairly long waits anyway. We should try to eliminate these polling loops, not make them more important. In fact, we should raise NAPTIME_PER_CYCLE to, say, 1000ms, to reduce spurious wakeups on an idle system. I'm OK with changing the polling loops. Is there a big problem there? I think it would take you about an hour to rewrite that, if you wanted to do so. But its not a necessary step to do that, especially when we're discussing whether Latches are actually as portable as we think. That sounds like more risk for a slight gain in performance. Am I reading the patch correctly that if the standby hasn't applied all WAL yet, you send a reply message at every wakeup, whether or not any progress has been made since last time? So if you have a long-running-transaction in the standby, for example, conflicting with WAL recovery, the standby will keep sending a status report to the master every 100ms. Sure. First, is that a problem? Why? The WalReceiver isn't busy doing anything else at that point, by definition. The WalSender isn't doing anything then either, by definition. Both are used to higher send rates. Second, if that really is a problem, sounds like a simple if test added to reply code. Third, the conflict issues are much reduced as well in this release. For this exact purpose. So I don't see any blockers in what you say. -- 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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 2:05 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It would feel at least as logical to control this in the standby. Now you are being ridiculous. You've spoken strongly against this at every single step of this journey. We're well passed the stage of putting anything in that could do that, as well you know. -- 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] alpha5
On Sat, Mar 26, 2011 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: Per previous discussion, I'm going to wrap alpha5 Monday morning Eastern time, barring objections. It seems that the 'make distcheck' build is broken. Apparently we don't have any automated testing of this? Anyone know what to fix here? openjade:tempfile_HISTORY.sgml:674:55:X: reference to non-existent ID GUC-DEADLOCK-TIMEOUT openjade:tempfile_HISTORY.sgml:677:23:X: reference to non-existent ID GUC-LOG-MIN-DURATION-STATEMENT openjade:tempfile_HISTORY.sgml:678:23:X: reference to non-existent ID GUC-LOG-AUTOVACUUM-MIN-DURATION openjade:tempfile_HISTORY.sgml:1116:23:X: reference to non-existent ID SQL-ALTERTABLE openjade:tempfile_HISTORY.sgml:676:23:X: reference to non-existent ID GUC-MAX-STANDBY-STREAMING-DELAY openjade:tempfile_HISTORY.sgml:675:23:X: reference to non-existent ID GUC-MAX-STANDBY-ARCHIVE-DELAY -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alpha5
Robert Haas robertmh...@gmail.com writes: On Sat, Mar 26, 2011 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: Per previous discussion, I'm going to wrap alpha5 Monday morning Eastern time, barring objections. It seems that the 'make distcheck' build is broken. Apparently we don't have any automated testing of this? Anyone know what to fix here? Bruce keeps trying to put cross-references where they mustn't go ... 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] alpha5
Excerpts from Tom Lane's message of lun mar 28 10:26:59 -0300 2011: Robert Haas robertmh...@gmail.com writes: On Sat, Mar 26, 2011 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: Per previous discussion, I'm going to wrap alpha5 Monday morning Eastern time, barring objections. It seems that the 'make distcheck' build is broken. Apparently we don't have any automated testing of this? Anyone know what to fix here? Bruce keeps trying to put cross-references where they mustn't go ... The long explanation is that history.sgml is used to generate HISTORY in plain text, and for that it must be built standalone. So all references to nodes external to that file are verboten. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alpha5
On Mon, Mar 28, 2011 at 9:26 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Mar 26, 2011 at 9:41 PM, Robert Haas robertmh...@gmail.com wrote: Per previous discussion, I'm going to wrap alpha5 Monday morning Eastern time, barring objections. It seems that the 'make distcheck' build is broken. Apparently we don't have any automated testing of this? Anyone know what to fix here? Bruce keeps trying to put cross-references where they mustn't go ... Actually those are all my fault. Sorry, I'm still learning the ropes. I didn't realize xref couldn't be used in the release notes; it looks like Bruce used link rather than xref for the things I used xref for. This is the sort of thing for which make maintainer-check would be very useful. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alpha5
I wrote: Robert Haas robertmh...@gmail.com writes: It seems that the 'make distcheck' build is broken. Apparently we don't have any automated testing of this? Anyone know what to fix here? Bruce keeps trying to put cross-references where they mustn't go ... Quick hack applied. I think there's a better way, but I don't remember it at this time of day. 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] Set hint bits upon eviction from BufMgr
On Fri, Mar 25, 2011 at 3:18 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 25, 2011 at 3:32 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 25.03.2011 16:52, Merlin Moncure wrote: Without this bit, the only way to set hint bits going during bufmgr eviction is to do a visibility check on every tuple, which would probably be prohibitively expensive. I don't think the naive approach of scanning all tuples would be too bad, actually. The hint bits only need to be set once, and it'd be bgwriter shouldering the overhead. I was thinking the same thing. The only thing I'm worried about is whether it'd make the bgwriter less responsive; we already have some issues in that department. I'd like to experiment on this and see what comes out. If the bgwriter was to be granted the ability to inspect buffers and set hints, it needs to be able to peek in and inspect the buffer itself which it currently doesn't do FWICT. I was thinking about setting a flag in the buffer (BM_HEAP) that gets set by the loader which flags the buffer for later inspection. Is there a simpler way to do this? It may turn out to be a dud, but I'd still like to play with the all visible bit and see how that interacts with data loading, both with and without special bgwriter logic (i'm going to kludge in a crude mechanism to try to prefer non all visible pages). The reason why I like it is the optimization is narrow and the risk of downside is low, although it's up a notch on the complexity level. If you do end up retooling the bgwriter to set hint bits broadly, there are some tricks you can do to reduce the number of useless clog checks you do (that is, you fault through to an in progress transaction). They involve changing the way the scan works, maybe even organizing buffers into multiple priority pools, so it's complicated and has to be done very carefully. I think you guys are correct: the logic belongs in the bgwriter. Generally speaking, it looks like the best route to minimizing hint bit pain is to if at all possible write them out set so they don't have to be rewritten later (Stephen's approach to leverage in transaction table creation is another way of attempting to do that). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] alpha5
On Mon, Mar 28, 2011 at 9:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: I wrote: Robert Haas robertmh...@gmail.com writes: It seems that the 'make distcheck' build is broken. Apparently we don't have any automated testing of this? Anyone know what to fix here? Bruce keeps trying to put cross-references where they mustn't go ... Quick hack applied. I think there's a better way, but I don't remember it at this time of day. Slightly better hack applied. Tarballs now at: http://developer.postgresql.org/~rhaas/ Please sanity check and let me know if it looks OK to push these out for real. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Set hint bits upon eviction from BufMgr
On Mon, Mar 28, 2011 at 9:48 AM, Merlin Moncure mmonc...@gmail.com wrote: I'd like to experiment on this and see what comes out. Great! If the bgwriter was to be granted the ability to inspect buffers and set hints, it needs to be able to peek in and inspect the buffer itself which it currently doesn't do FWICT. That matches my understanding. I was thinking about setting a flag in the buffer (BM_HEAP) that gets set by the loader which flags the buffer for later inspection. Is there a simpler way to do this? Hmm. That's slightly crufty, but it might be OK. At least, I don't have a better idea. I think you guys are correct: the logic belongs in the bgwriter. Generally speaking, it looks like the best route to minimizing hint bit pain is to if at all possible write them out set so they don't have to be rewritten later (Stephen's approach to leverage in transaction table creation is another way of attempting to do that). Yeah. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional options for Sync Replication
On 28.03.2011 16:11, Simon Riggs wrote: On Mon, Mar 28, 2011 at 2:05 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It would feel at least as logical to control this in the standby. Now you are being ridiculous. You've spoken strongly against this at every single step of this journey. I was thinking specifically about whether flush vs. write (vs. apply, maybe) here. It would make sense to set that in the standby. You might even want to set it differently on different standbys. What I was strongly against is the action at a distance, where setting a GUC in a standby suddenly makes the master to wait for acks from that server. That's dangerous, but I don't see such danger in setting the level of synchronicity in the standby, once you've decided that it's a synchronous 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] Set hint bits upon eviction from BufMgr
Robert Haas robertmh...@gmail.com writes: On Mon, Mar 28, 2011 at 9:48 AM, Merlin Moncure mmonc...@gmail.com wrote: I was thinking about setting a flag in the buffer (BM_HEAP) that gets set by the loader which flags the buffer for later inspection. Is there a simpler way to do this? Hmm. That's slightly crufty, but it might be OK. At least, I don't have a better idea. The major problem with all of this is that the bgwriter has no idea which buffers contain heap pages. And I'm not convinced it's a good idea to try to let it know that. If we get to the point where bgwriter is trying to do catalog accesses, we are in for a world of pain. (Can you say modularity violation? How about deadlock?) 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] Triggers on system catalog
Hi, Is there a way to create triggers on system catalog tables like pg_class, pg_attribute etc...? Thanks, Shridhar
[HACKERS] Triggers on system catalog
Hi, Is there a way to create triggers on system catalog tables like pg_class, pg_attribute etc...? Thanks, Shridhar -- View this message in context: http://postgresql.1045698.n5.nabble.com/Triggers-on-system-catalog-tp4267669p4267669.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
Re: [HACKERS] Set hint bits upon eviction from BufMgr
Tom Lane t...@sss.pgh.pa.us wrote: The major problem with all of this is that the bgwriter has no idea which buffers contain heap pages. And I'm not convinced it's a good idea to try to let it know that. If we get to the point where bgwriter is trying to do catalog accesses, we are in for a world of pain. (Can you say modularity violation? How about deadlock?) How about having a BackgroundPrepareForWriteFunction variable associated with each page the bgwriter might see, which would be a pointer to a function to call (if the variable is not NULL) before writing? The bgwriter would still have no idea what kind of page it was or what the function did -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] alpha5
On 03/28/2011 09:25 AM, Robert Haas wrote:
On Sat, Mar 26, 2011 at 9:41 PM, Robert Haasrobertmh...@gmail.com wrote:
Per previous discussion, I'm going to wrap alpha5 Monday morning
Eastern time, barring objections.
It seems that the 'make distcheck' build is broken. Apparently we
don't have any automated testing of this? Anyone know what to fix
here?
There is a bunch of things we don't test in a way that shows up
obviously, if at all. I'm thinking of making a buildfarm enhancement
that will allow performance of optional steps with some extra timing and
branch params. Obvious candidates include building the docs, looking for
typedefs, and benchmark runs as per Greg's post yesterday. The extra
config would look something like:
optional_steps = {
find_typedefs = { branches = ['HEAD'], min_hours_since = 23 },
benchmark = {dow = [0,2], min_hour = 3, max_hour = 6 },
build_docs = {min_hours_since = 72},
},
Adding a docs build to the buildfarm would at least show up errors like
the one you found pretty quickly. It's going to have to be optional,
though, as by no means does everyone have the required toolset installed.
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] Triggers on system catalog
On Mon, Mar 28, 2011 at 10:19 AM, Shridhar Polas shridharpo...@gmail.comwrote: Hi, Is there a way to create triggers on system catalog tables like pg_class, pg_attribute etc...? No, Postgres does not support triggers on system catalogs; we do not have DDL triggers either, if that's what you were trying to achieve. Regards, -- Gurjeet Singh EnterpriseDB Corporation The Enterprise PostgreSQL Company
Re: [HACKERS] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 10:11 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 28.03.2011 16:11, Simon Riggs wrote: On Mon, Mar 28, 2011 at 2:05 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It would feel at least as logical to control this in the standby. Now you are being ridiculous. You've spoken strongly against this at every single step of this journey. I was thinking specifically about whether flush vs. write (vs. apply, maybe) here. It would make sense to set that in the standby. You might even want to set it differently on different standbys. What I was strongly against is the action at a distance, where setting a GUC in a standby suddenly makes the master to wait for acks from that server. That's dangerous, but I don't see such danger in setting the level of synchronicity in the standby, once you've decided that it's a synchronous standby. It might not be dangerous, but the standby currently sends write, flush, and apply positions back separately, so the master must decide which of those to pay attention to, unless we rework the whole design. I actually think the current design is quite nice and am in no hurry to rejigger that particular part of it. However, I do think that we may need or want to rejigger the timing of replies for performance. It might be, for example, that when waiting for the write, the master should somehow indicate to the standby the earliest write-LSN that could release waiters, so that a standby can send back a reply the instant it hits that LSN, without waiting to finish reading everything that's buffered up as it does now. For apply, I agree with your feeling that the startup process needs to wake walreceiver via a latch when the apply position advances, but here again it might be beneficial to send the first interesting LSN from master to standby to cut down unnecessary wakeups. We also need to consider the issue raised elsewhere - that a naive implementation of this could allow the commit to become visible on the standby before it becomes visible on the master. That would be expensive to prevent, because you'd need an additional set of master-standby interlocks, but I think at least one person was arguing that it was necessary for correctness - my memory of the details is fuzzy at the moment. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Set hint bits upon eviction from BufMgr
On Mon, Mar 28, 2011 at 10:19 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Mar 28, 2011 at 9:48 AM, Merlin Moncure mmonc...@gmail.com wrote: I was thinking about setting a flag in the buffer (BM_HEAP) that gets set by the loader which flags the buffer for later inspection. Is there a simpler way to do this? Hmm. That's slightly crufty, but it might be OK. At least, I don't have a better idea. The major problem with all of this is that the bgwriter has no idea which buffers contain heap pages. And I'm not convinced it's a good idea to try to let it know that. If we get to the point where bgwriter is trying to do catalog accesses, we are in for a world of pain. (Can you say modularity violation? How about deadlock?) Well, that's why Merlin was suggesting having the backends that read the buffers in flag the heap pages as BM_HEAP. Then the background writer can just examine that bit. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comments on system tables and columns
Em 28-03-2011 08:14, Thom Brown escreveu: I notice that none of the system tables or columns thereof bear any comments. Is this intentional, or an oversight? I would have thought comments would be useful since the column names aren't exactly always self-explanatory. It could be useful in some cases. IIRC the comments are not there to avoid bloating the catalog. One month ago or so I saw a commit to comment operator support functions. Maybe it is worth comment system catalog too [1]. [1] http://eulerto.blogspot.com/2010/11/comment-on-catalog-tables.html -- Euler Taveira de Oliveira http://www.timbira.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] Recursive containment of composite types
Bug #5950 proposes the following test case: create table t (); alter table t add childs t; alter table t add id serial not null primary key; Most of the back branches dump core because CheckAttributeType() goes into infinite recursion. That doesn't happen in HEAD, but so far as I can see that's just because of some chance rearrangement of the order of operations in ALTER TABLE. I wouldn't be at all surprised if there are related cases where HEAD fails too. I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? 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] Set hint bits upon eviction from BufMgr
On Mon, Mar 28, 2011 at 9:29 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: The major problem with all of this is that the bgwriter has no idea which buffers contain heap pages. And I'm not convinced it's a good idea to try to let it know that. If we get to the point where bgwriter is trying to do catalog accesses, we are in for a world of pain. (Can you say modularity violation? How about deadlock?) How about having a BackgroundPrepareForWriteFunction variable associated with each page the bgwriter might see, which would be a pointer to a function to call (if the variable is not NULL) before writing? The bgwriter would still have no idea what kind of page it was or what the function did Well, that is much cleaner from abstraction point of view but you lose the ability to adjust scan priority before flushing out the page...I'm assuming by the time this function is called, you've already made the decision to write it out. (maybe priority is necessary and maybe it isn't, but I don't like losing the ability to tune at that level). You could though put a priority inspection facility behind a similar abstraction fence (BackgroundGetWritePriority) though. Maybe that's more trouble than it's worth though. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Recursive containment of composite types
On Mon, Mar 28, 2011 at 10:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Bug #5950 proposes the following test case: create table t (); alter table t add childs t; alter table t add id serial not null primary key; Most of the back branches dump core because CheckAttributeType() goes into infinite recursion. That doesn't happen in HEAD, but so far as I can see that's just because of some chance rearrangement of the order of operations in ALTER TABLE. I wouldn't be at all surprised if there are related cases where HEAD fails too. I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? Well, essentially what you'd be doing is making a linked list data type. t contains an id, and perhaps also another t. You can travel down through arbitrarily many objects of type t, each of which has an id value, and eventually you'll hit one where t.childs is NULL. So it's semantically sensible, I believe, but it seems perfectly sensible to just prohibit it. If someone wants to do the work to make it work in some future release, we can consider it, but I think there are other aspects of the type system that are more worthy of our attention than this one is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Recursive containment of composite types
On Mon, Mar 28, 2011 at 9:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Bug #5950 proposes the following test case: create table t (); alter table t add childs t; alter table t add id serial not null primary key; Most of the back branches dump core because CheckAttributeType() goes into infinite recursion. That doesn't happen in HEAD, but so far as I can see that's just because of some chance rearrangement of the order of operations in ALTER TABLE. I wouldn't be at all surprised if there are related cases where HEAD fails too. I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? Well, maybe. In fact, probably. That's like asking in C if it's sane to have a structure to contain a pointer back to itself, which of course it is. That said, if it doesn't work properly, it should probably be disabled until it does. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 3:11 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 28.03.2011 16:11, Simon Riggs wrote: On Mon, Mar 28, 2011 at 2:05 PM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: It would feel at least as logical to control this in the standby. Now you are being ridiculous. You've spoken strongly against this at every single step of this journey. I was thinking specifically about whether flush vs. write (vs. apply, maybe) here. It would make sense to set that in the standby. You might even want to set it differently on different standbys. What I was strongly against is the action at a distance, where setting a GUC in a standby suddenly makes the master to wait for acks from that server. That's dangerous, but I don't see such danger in setting the level of synchronicity in the standby, once you've decided that it's a synchronous standby. The action at a distance thought still applies, since you would wait more or less time depending upon how this parameter was set on the standby. I can't see how this situation differs. Your own argument still applies. I would point out that I argued against you, but was persuaded towards your approach. The code won't easily allow what you suggest. There were multiple approaches to implementation, but there aren't anymore. So you can't say the UI is unclear; its very clear how we implement things from here - the way this patch implements it - on the master. This is a simple patch, containing functionality already discussed and agreed and for which code was submitted in this CF. There's no reason to block this, only for us to decide the final naming of parameter/s and options. -- 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] Additional options for Sync Replication
Robert Haas robertmh...@gmail.com wrote: We also need to consider the issue raised elsewhere - that a naive implementation of this could allow the commit to become visible on the standby before it becomes visible on the master. That would be expensive to prevent, because you'd need an additional set of master-standby interlocks, but I think at least one person was arguing that it was necessary for correctness - my memory of the details is fuzzy at the moment. I remember expressing concern about that; I don't know if anyone else did. After some discussion, I was persuaded that the use cases where it would matter are narrow enough that documentation of the issue should be enough. -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] Recursive containment of composite types
On Mon, Mar 28, 2011 at 9:54 AM, Merlin Moncure mmonc...@gmail.com wrote: On Mon, Mar 28, 2011 at 9:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: Bug #5950 proposes the following test case: create table t (); alter table t add childs t; alter table t add id serial not null primary key; Most of the back branches dump core because CheckAttributeType() goes into infinite recursion. That doesn't happen in HEAD, but so far as I can see that's just because of some chance rearrangement of the order of operations in ALTER TABLE. I wouldn't be at all surprised if there are related cases where HEAD fails too. I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? Well, maybe. In fact, probably. That's like asking in C if it's sane to have a structure to contain a pointer back to itself, which of course it is. That said, if it doesn't work properly, it should probably be disabled until it does. hm, you can work around lack of above at present using two vs one types: postgres=# create table b (); postgres=# create table c (); postgres=# alter table b add c c; postgres=# alter table c add b b; postgres=# alter table c add i int; postgres=# alter table b add j int; postgres=# select row(row(null, 1), 1)::b; row ((,1),1) This isn't great but might cover some of the cases where you need such a thing (and I tested this on 8.1). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Comments on system tables and columns
Excerpts from Thom Brown's message of lun mar 28 08:14:07 -0300 2011: Hi, I notice that none of the system tables or columns thereof bear any comments. Is this intentional, or an oversight? I would have thought comments would be useful since the column names aren't exactly always self-explanatory. Bruce has been working on changes to have catalog objects (tables, views and columns) contain comments, but he deferred it to 9.2 because it involved nontrivial pieces of infrastructure (mainly to avoid duplication with the SGML catalog documentation). -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Recursive containment of composite types
On Mon, Mar 28, 2011 at 10:47 AM, Tom Lanet...@sss.pgh.pa.us wrote: Bug #5950 proposes the following test case: create table t (); alter table t add childs t; alter table t add id serial not null primary key; Most of the back branches dump core because CheckAttributeType() goes into infinite recursion. That doesn't happen in HEAD, but so far as I can see that's just because of some chance rearrangement of the order of operations in ALTER TABLE. I wouldn't be at all surprised if there are related cases where HEAD fails too. I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? Well, essentially what you'd be doing is making a linked list data type. t contains an id, and perhaps also another t. You can travel down through arbitrarily many objects of type t, each of which has an id value, and eventually you'll hit one where t.childs is NULL. So it's semantically sensible, I believe, but it seems perfectly sensible to just prohibit it. This makes me think of HL7 datatypes R1 http://www.hl7.org/v3ballot2011jan/html/welcome/environment/index.html and http://www.hl7.org/v3ballot2011jan/html/infrastructure/datatypes/datatypes.html where a Concept Descriptor type has as attribute a set of concept descriptor types. Regarding the bug: if recursion causes errors, cycles could be dangerous as well. -- Yeb Havinga http://www.mgrid.net/ Mastering Medical 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] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 01:48:13PM +0900, Fujii Masao wrote: In 9.0, recovery doesn't read a backup history file. That FATAL error happens if recovery ends before it reads the WAL record which was generated by pg_stop_backup(). IOW, recovery gets the recovery ending location from WAL record not backup history file. Since you didn't run pg_stop_backup() and there is no WAL record containing the recovery ending location, you got that error. If you want to take hot backup from the standby, you need to do the procedure explained in http://wiki.postgresql.org/wiki/Incrementally_Updated_Backups one more question. how come that I can use this backup to make standalone pg, and it starts without any problem, but when I start it as sr slave, let it run for some time, and then promote to standalone, it breaks? Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Recursive containment of composite types
Merlin Moncure mmonc...@gmail.com writes: On Mon, Mar 28, 2011 at 9:47 AM, Tom Lane t...@sss.pgh.pa.us wrote: I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? Well, maybe. In fact, probably. That's like asking in C if it's sane to have a structure to contain a pointer back to itself, which of course it is. That said, if it doesn't work properly, it should probably be disabled until it does. hm, you can work around lack of above at present using two vs one types: postgres=# create table b (); postgres=# create table c (); postgres=# alter table b add c c; postgres=# alter table c add b b; Well, that'd have to be disallowed too under what I have in mind. Indirect recursion is no safer than direct. If you try alter table b add k serial; at this point, you'll get the same crash or failure as for the direct recursion case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 10:58 AM, Simon Riggs si...@2ndquadrant.com wrote: This is a simple patch, containing functionality already discussed and agreed and for which code was submitted in this CF. These statements are simply not accurate. It isn't a simple patch, the details of how the write and apply modes should work haven't been fully discussed, and there is no version of your patch submitted for the last CommitFest which contains any of this functionality. Moreover, that CommitFest is OVER, so your repeated references to it as this CF rather than the last CF do not match my view of how the world works. There's no reason to block this, only for us to decide the final naming of parameter/s and options. At the end of the day, we make these decisions by consensus, and three people have said quite clearly that they believe it is too late to apply something like this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
On Fri, Mar 25, 2011 at 1:32 PM, Alexander Korotkov aekorot...@gmail.com wrote: I would like to ask you about currency of the work above. I propose to develop functionality of GIN and GiST q-gram indexes with following features: 1) Handle edit distance (e.g. levenshtein distance) and LIKE/ILIKE queries(using GIN partial match if no full q-grams can be extracted from wildcard) 2) Support of various q 3) Support of positional q-grams in GIN (for more effective edit distance filtering) 4) Various signature size in GiST As you can see, there are some significant differences from pg_trgm. Do you see this functionality useful? If you think this functionality useful, where do you like to see it: separate project, contrib module, core (of course, in the case when code have sufficient quality)? I have stong confidence level about implementability of this project in few month. That's why I could propose this as an GSoC project. I'm afraid I don't know this code well enough to give you any meaningful feedback, but I hope someone will. All - note that Alexander has contributed a number of patches in this area previously that have been committed, so it'd be great if we can do our part to help him continue contributing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Fix plpgsql to release SPI plans when a function or DO block is
* Jan Urbański (wulc...@wulczer.org) wrote: On 28/03/11 04:31, Tom Lane wrote: Do the other PLs we ship need similar fixes? Offhand I think the other PLs leave management of prepared plans to the user. If there are any places where they cache plans behind the scenes, maybe a similar fix would be appropriate. FWIW I executed do $$ plpy.execute(select 1 from pg_class) $$ language plpythonu; 10k times in a session and the backend grew a lot in memory and never released it. I can't offhand see where the memory went, I'll try to investigate in the evening. I'm about 90% sure that they all have this problem.. I havn't had a chance to look at how Tom fixed pl/pgsql (I didn't think it'd be easy to do w/o coming up with a way to explicitly tell the PL to release something) so perhaps I'm mistaken, but they all share very similar code.. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
Robert Haas robertmh...@gmail.com writes: On Fri, Mar 25, 2011 at 1:32 PM, Alexander Korotkov aekorot...@gmail.com wrote: I would like to ask you about currency of the work above. I propose to develop functionality of GIN and GiST q-gram indexes with following features: I'm afraid I don't know this code well enough to give you any meaningful feedback, but I hope someone will. Really Oleg and Teodor are the only people well-qualified to comment on such stuff. (It sounds reasonable to me, but I wouldn't know if there are problems in the idea.) They may be too busy right at the moment. 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] Recursive containment of composite types
On 03/28/2011 11:14 AM, Tom Lane wrote: Merlin Moncuremmonc...@gmail.com writes: On Mon, Mar 28, 2011 at 9:47 AM, Tom Lanet...@sss.pgh.pa.us wrote: I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? Well, maybe. In fact, probably. That's like asking in C if it's sane to have a structure to contain a pointer back to itself, which of course it is. That said, if it doesn't work properly, it should probably be disabled until it does. hm, you can work around lack of above at present using two vs one types: postgres=# create table b (); postgres=# create table c (); postgres=# alter table b add c c; postgres=# alter table c add b b; Well, that'd have to be disallowed too under what I have in mind. Indirect recursion is no safer than direct. If you try alter table b add k serial; at this point, you'll get the same crash or failure as for the direct recursion case. I think we should forbid it for now. If someone comes up with a) a good way to make it works and b) a good use case, we can look at it then. I expect the PostgreSQL type system to be a good deal more constrained than a general in-memory programming language type system. If lack of working type recursion were a serious problem surely we'd have seen more squawks about this by now. 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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 3:42 PM, Robert Haas robertmh...@gmail.com wrote: It might not be dangerous, but the standby currently sends write, flush, and apply positions back separately, so the master must decide which of those to pay attention to, unless we rework the whole design. I actually think the current design is quite nice and am in no hurry to rejigger that particular part of it. In particular what I like about the current design is that there's no reason you shouldn't be able to change the commit durability setting per-transacion. You might want to have logging records be asynchronous, regular operations be synchronous on the master, and opeations involving money block until the slave has received them or synced them or even applied them. Or you might want to mark just the transactions affecting the data that your read-only queries which are load-balanced on slaves as blocking until the slave has applied them so people don't see inconsistent old data making it look like the transaction failed. -- 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] Recursive containment of composite types
Andrew Dunstan and...@dunslane.net writes: On 03/28/2011 11:14 AM, Tom Lane wrote: I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? I think we should forbid it for now. If someone comes up with a) a good way to make it works and b) a good use case, we can look at it then. I expect the PostgreSQL type system to be a good deal more constrained than a general in-memory programming language type system. If lack of working type recursion were a serious problem surely we'd have seen more squawks about this by now. The immediate issue in CheckAttributeType() could be fixed by tracking which types it was processing and not recursing into an already-open type. Which, not at all coincidentally, is 90% the same code it'll need to have to throw error. The issue for really making it work is how do we know if there are any other places that need a recursion defense? I'm pretty sure that find_composite_type_dependencies would, and I don't know where else there might be a hidden assumption that column references don't loop. So I think that it's mostly about testing rather than anything else. If I were fairly confident that I knew where all the risk spots were, I'd just fix them rather than trying to forbid the construction. 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] Lock problem with autovacuum truncating heap
On 3/27/2011 9:51 PM, Robert Haas wrote: On Sun, Mar 27, 2011 at 9:41 PM, Jan Wieckjanwi...@yahoo.com wrote: On 3/27/2011 6:21 PM, Robert Haas wrote: On Sun, Mar 27, 2011 at 3:25 PM, Jan Wieckjanwi...@yahoo.comwrote: Since we are talking about stable releases, I think just releasing and reacquiring the exclusive lock is enough. We can then try to further improve things for future releases. That seems unsafe - things can change under you while you don't hold the lock... The only change relevant in this case would be some concurrent client extending the relation while we don't hold the lock. A call to RelationGetNumberOfBlocks() after reacquiring the lock will tell. Safety reestablished. I thought that the risk was that someone might write tuples into the blocks that we're thinking of truncating. Currently the risk is that while vacuum is doing its main work, someone can either extend the relation or reuse space inside one of the empty blocks (that are about to be truncated away). That is why the function lazy_truncate_heap() does the following: 1) acquire exclusive lock 2) check via RelationGetNumberOfBlocks() if it has been extended before locking - abort if so 3) check via count_nondeletable_pages() what the highest block in the to be truncated range is, that contains a (newly created) tuple 4) truncate the relation 5) release the lock The function count_nondeletable_pages() is the one doing the block wise reverse scan. It does check for interrupts and that is the place, where the deadlock code will boot vacuum. What I am proposing is to put all those 5 steps into a loop that tries to bite off smaller bits from the end of the table, instead of trying to swallow the whole dead space at once. count_nondeletable_pages() is a static function and only called from lazy_truncate_heap(), so fiddling with the scan direction inside of it would be totally safe from a functional side effect point of view. Doing so or not depends on whether reversing its scan direction does have a performance benefit or not. I agree with Tom that at some chunk size, the effect might be negative. That is because currently it scans backwards and returns at the first block containing a tuple. To scan forward, it has to scan all the blocks, remembering the last that contained a tuple. I don't like a 1,000 ms hiccup in my system, regardless of how many transaction hoops you make it go through. I can't argue with that. I assumed we have a consensus that both, locking a system for 10+ minutes as well as having a 1,000ms hiccup every 2 minutes, are problems we need to fix. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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] Lock problem with autovacuum truncating heap
On 3/27/2011 10:43 PM, Tom Lane wrote: In particular, I thought the direction Jan was headed was to release and reacquire the lock between truncating off limited-size chunks of the file. If we do that, we probably *don't* want or need to allow autovac to be booted off the lock more quickly. That is correct. 3) Scanning backwards 8MB at a time scanning each 8MB forwards instead of just going back by block backwards. Maybe. I'd want to see some experimental evidence justifying the choice of chunk size; I'm pretty sure this will become counterproductive once the chunk size is too large. Me too, which is why that part of my proposal is highly questionable and requires a lot of evidence to be even remotely considered for back releases. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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] Additional options for Sync Replication
On Mon, Mar 28, 2011 at 4:15 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Mar 28, 2011 at 10:58 AM, Simon Riggs si...@2ndquadrant.com wrote: This is a simple patch, containing functionality already discussed and agreed and for which code was submitted in this CF. These statements are simply not accurate. Then you are mistaken on every single point. It isn't a simple patch, Yes, it is. Most of the patch is docs and GUC changes. The current patch was specifically designed by me to allow this to be added. One queue is replaced easily by 3 queues, which essentially touches only 2 places in the current code, sleep and wakeup. And it touches them in very simple ways. Variable to Array. Easy. the details of how the write and apply modes should work haven't been fully discussed, The original patch had all 3 modes side-by-side, all working identically. There has never been any objection or discussion about that and its been part of the design for months. What different approach is there? and there is no version of your patch submitted for the last CommitFest which contains any of this functionality. v9, submitted on 15 Jan contains this functionality. Moreover, that CommitFest is OVER, so your repeated references to it as this CF rather than the last CF do not match my view of how the world works. We are still applying patches, for various reasons. I must have missed your objections to Tom's proposals to fix un-neat things about collations, or his additions to the extensions features. Go say what you've said here to him as well. There's no reason to block this, only for us to decide the final naming of parameter/s and options. At the end of the day, we make these decisions by consensus, and three people have said quite clearly that they believe it is too late to apply something like this. Something like this. Well given that all of the facts on which you've based your decision are wrong, I expect you to revise your decision. There is nothing about this patch that makes it possible or sensible to exclude it. You wish to continue to discuss Network timeouts. Why are they in and this out. Both were submitted as part of my original patch -- 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] Another swing at JSON
On Mon, Mar 28, 2011 at 1:21 PM, Joseph Adams joeyadams3.14...@gmail.com wrote: Attached is a patch that adds a 'json' contrib module. Although we may want a built-in JSON data type in the near future, making it a module (for the time being) has a couple advantages: Is this something you'd hope to get committed at some point, or do you plan to maintain it as an independent project? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Another swing at JSON
On Mon, Mar 28, 2011 at 1:48 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Mar 28, 2011 at 1:21 PM, Joseph Adams joeyadams3.14...@gmail.com wrote: Attached is a patch that adds a 'json' contrib module. Although we may want a built-in JSON data type in the near future, making it a module (for the time being) has a couple advantages: Is this something you'd hope to get committed at some point, or do you plan to maintain it as an independent project? I'm hoping to get it committed at some point, perhaps as a module soon, and a built-in later. Plenty of people are still waiting for JSON data type support, for example: http://stackoverflow.com/questions/4995945/optimize-escape-json-in-postgresql-9-0 -- 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] Another swing at JSON
On Mon, Mar 28, 2011 at 2:03 PM, Joseph Adams joeyadams3.14...@gmail.com wrote: On Mon, Mar 28, 2011 at 1:48 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Mar 28, 2011 at 1:21 PM, Joseph Adams joeyadams3.14...@gmail.com wrote: Attached is a patch that adds a 'json' contrib module. Although we may want a built-in JSON data type in the near future, making it a module (for the time being) has a couple advantages: Is this something you'd hope to get committed at some point, or do you plan to maintain it as an independent project? I'm hoping to get it committed at some point, perhaps as a module soon, and a built-in later. Plenty of people are still waiting for JSON data type support, for example: http://stackoverflow.com/questions/4995945/optimize-escape-json-in-postgresql-9-0 Well, one thing you'll need to do is rework it for the new 9.1 extensions interface. Once you're reasonably happy with it, I think it'd be good to add this to the next CommitFest: https://commitfest.postgresql.org/action/commitfest_view/open I'd like to review it more, but it's more than I can tackle at the moment. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
On Mon, Mar 28, 2011 at 7:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I'm afraid I don't know this code well enough to give you any meaningful feedback, but I hope someone will. Really Oleg and Teodor are the only people well-qualified to comment on such stuff. (It sounds reasonable to me, but I wouldn't know if there are problems in the idea.) They may be too busy right at the moment. Thank you for reply. I'm going to ask Oleg and Teodor for their feedback. With best regards, Alexander Korotkov.
Re: [HACKERS] SSI bug?
hi, (6) Does the application continue to run relatively sanely, or does it fall over at this point? my application just exits on the error. if i re-run the application without rebooting postgres, it seems that i will get the error sooner than the first run. (but it might be just a matter of luck) If your application hits this again, could you check pg_stat_activity and pg_locks and see if any SIReadLock entries are lingering after the owning transaction and all overlapping transactions are completed? If anything is lingering between runs of your application, it *should* show up in one or the other of these. this is 71ac48fd9cebd3d2a873635a04df64096c981f73 with your two patches. this psql session was the only activity to the server at this point. hoge=# select * from pg_stat_activity; -[ RECORD 1 ]+ datid| 16384 datname | hoge procpid | 7336 usesysid | 10 usename | takashi application_name | psql client_addr | client_hostname | client_port | -1 backend_start| 2011-03-26 12:28:21.882226+09 xact_start | 2011-03-28 11:55:19.300027+09 query_start | 2011-03-28 11:55:19.300027+09 waiting | f current_query| select * from pg_stat_activity; hoge=# select count(*) from pg_locks where mode='SIReadLock'; -[ RECORD 1 ] count | 7057 hoge=# select locktype,count(*) from pg_locks group by locktype; -[ RECORD 1 ] locktype | virtualxid count| 1 -[ RECORD 2 ] locktype | relation count| 1 -[ RECORD 3 ] locktype | tuple count| 7061 hoge=# (7) The message hint would help pin it down, or a stack trace at the point of the error would help more. Is it possible to get either? Looking over the code, it appears that the only places that SSI could generate that error, it would cancel that transaction with the hint You might need to increase max_pred_locks_per_transaction. and otherwise allow normal processing. no message hints. these errors are not generated by SSI code, at least directly. Right, that's because we were using HASH_ENTER instead of HASH_ENTER_NULL. I've posted a patch which should correct that. sure, with your patch it seems that they turned into different ones. PG_DIAG_SEVERITY: WARNING PG_DIAG_SQLSTATE: 53200 PG_DIAG_MESSAGE_PRIMARY: out of shared memory PG_DIAG_SOURCE_FILE: shmem.c PG_DIAG_SOURCE_LINE: 190 PG_DIAG_SOURCE_FUNCTION: ShmemAlloc PG_DIAG_SEVERITY: ERROR PG_DIAG_SQLSTATE: 53200 PG_DIAG_MESSAGE_PRIMARY: out of shared memory PG_DIAG_MESSAGE_HINT: You might need to increase max_pred_locks_per_transaction. PG_DIAG_SOURCE_FILE: predicate.c PG_DIAG_SOURCE_LINE: 2049 PG_DIAG_SOURCE_FUNCTION: CreatePredicateLock Even with the above information it may be far from clear where allocations are going past their maximum, since one HTAB could grab more than its share and starve another which is staying below its maximum. I'll take a look at the possibility of adding a warning or some such when an HTAB expands past its maximum size. I see from your later post that you are running with this patch. Has that reported anything yet? i got nothing except the following one. (in the server log) WARNING: hash table ShmemIndex has more entries than expected DETAIL: The maximum was set to 32 on creation. YAMAMOTO Takashi Thanks, -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] Sync Rep v19
On Thu, Mar 24, 2011 at 11:53 AM, Fujii Masao masao.fu...@gmail.com wrote: On Thu, Mar 24, 2011 at 8:34 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, Mar 24, 2011 at 11:17 AM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Mar 23, 2011 at 5:53 PM, Fujii Masao masao.fu...@gmail.com wrote: Do you still want to work up a patch for this? If so, I can review. Sure. Will do. The attached patch allows standby servers to connect during smart shutdown in order to wake up backends waiting for sync rep. I think that is possibly OK, but the big problem is the lack of a clear set of comments about how the states are supposed to interact that allow these changes to be validated. That state isn't down to you, but I think we need that clear so this change is more obviously correct than it is now. src/backend/replication/README needs to be updated to make that clear? Not sure where we'd put them. -- 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] Additional options for Sync Replication
On Sun, Mar 27, 2011 at 10:45 PM, Simon Riggssi...@2ndquadrant.com wrote: I was hoping to fine tune/tweak Sync Rep after feedback during beta, but my understanding of current consensus is that that will be too late to make user visible changes. So I'm proposing this change now, before Beta, rather than during Beta. For what it's worth I think this is a simplification. We have: 1) Development when new features are added 2) Feature freeze - when those features are tweaked and fixed based on our own testing but no new features added 3) Beta - when features are tweaked and fixed in response to user suggestions but no new features added How to say this short: when testing the latest syncrep patches I've wasted time looking where the recv|fsync|apply api was, to find out it was gone. *shrug* didn't need it for my use case. But for others it might well be frustration to find out that what's currently called syncrep cannot be configured in a way it's suitable, and that they might have wasted considerable time while finding that out. The dba interface for recv|fsync|apply seems to be pretty stable, so supporting that for years should be without risk. How it works under the hood - the beta period seems like *the* opportunity to attrach mayor testing from all people waiting to get their hands on syncrep. An aspect of a good product is that it doesn't waste users time, like a good piece of code needs little comment. Alarm bells should go off when somebody is about to write a large piece of documentation writing what a feature doesn't support. It would be better to just support it (recv|fsync|apply), or no syncrep at all. Syncrep is incomplete without it. -- Yeb Havinga http://www.mgrid.net/ Mastering Medical 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] [COMMITTERS] pgsql: Remove more SGML tabs.
On tor, 2011-03-24 at 16:05 -0400, Peter Eisentraut wrote: Anyway, here is a small patch that changes the duplicate_oids script to return a nonzero exit status in case of a problem, and then creates a global maintainer-check target that checks that and the SGML syntax and the NLS thing. Other things could be added in the future. Documentation should be added. Committed, but didn't find a good place to document it. -- 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] alpha5
On mån, 2011-03-28 at 09:35 -0400, Robert Haas wrote: Actually those are all my fault. Sorry, I'm still learning the ropes. I didn't realize xref couldn't be used in the release notes; it looks like Bruce used link rather than xref for the things I used xref for. This is the sort of thing for which make maintainer-check would be very useful. And/or we could add the creation of these files to make doc or make world or something. -- 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] Recursive containment of composite types
On mån, 2011-03-28 at 09:54 -0500, Merlin Moncure wrote: Well, maybe. In fact, probably. That's like asking in C if it's sane to have a structure to contain a pointer back to itself, which of course it is. But this is not a pointer, it's containment. SQL has pointers, too (reference types). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [COMMITTERS] pgsql: Remove more SGML tabs.
On Mon, Mar 28, 2011 at 10:56:39PM +0300, Peter Eisentraut wrote: On tor, 2011-03-24 at 16:05 -0400, Peter Eisentraut wrote: Anyway, here is a small patch that changes the duplicate_oids script to return a nonzero exit status in case of a problem, and then creates a global maintainer-check target that checks that and the SGML syntax and the NLS thing. Other things could be added in the future. Documentation should be added. Committed, but didn't find a good place to document it. Is there a section for any of the maintainer- stuff in make? If not, should there be? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote: You can't be guaranteed that they won't standardize something incompatible no matter what we do. We could choose to do it as you've proposed and they could then standardize some weird syntax - the = is a fairly relevant example of exactly that. The matter of how to resolve SQL parameter names is already standardized. See clause on identifier chain. -- 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] Open issues for collations
On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * RI triggers should insert COLLATE clauses in generated queries to satisfy SQL2008 9.13 SR 4a, which says that RI comparisons use the referenced column's collation. Right now you may get either table's collation depending on which query type is involved. I think an obvious failure may not be possible so long as equality means the same thing in all collations, but it's definitely possible that the planner might decide it can't use the referenced column's unique index, which would suck for performance. (Note: this rule seems to prove that the committee assumes equality can mean different things in different collations, else they'd not have felt the need to specify.) Right, but we don't support that yet, so I don't consider that that has to be addressed right now. Rather it could go on a list of things to fix when supporting collations which redefine equality. The index mismatch issue is also not urgent. It's not a regression and it's more like don't-do-that-then or do-it-differently-then. * It'd sure be nice if we had some nontrivial test cases that work in encodings besides UTF8. I'm still bothered that the committed patch failed to cover single-byte-encoding cases in upper/lower/initcap. Well, how do we want to maintain these test cases without doing too much duplication? It would be easy to run a small sed script over collate.linux.utf8.sql to create, say, a latin1 version out of it. Since it's Linux only, it might be valid to do it that way without having to make it super-portable in C. * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Fine with me. * Is it worth adding a cares-about-collation flag to pg_proc? Probably too late to be worrying about such refinements for 9.1. Probably. It would open up a bunch of new cases to change and fine-tune. -- 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] Libpq PGRES_COPY_BOTH - version compatibility
On sön, 2011-03-27 at 00:20 -0400, Tom Lane wrote: but we haven't bumped the protocol version number since 7.4, and so I have no faith that clients will behave sensibly So we will never change the minor protocol version, because we've never done it and don't know whether it works? Perhaps the answer then is to do it more often? -- 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] Problem with streaming replication, backups, and recovery (9.0.x)
hubert depesz lubaczewski dep...@depesz.com wrote: how come that I can use this backup to make standalone pg, and it starts without any problem, but when I start it as sr slave, let it run for some time, and then promote to standalone, it breaks? We need more detail to make much of a guess about that. -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] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 04:24:23PM -0500, Kevin Grittner wrote: hubert depesz lubaczewski dep...@depesz.com wrote: how come that I can use this backup to make standalone pg, and it starts without any problem, but when I start it as sr slave, let it run for some time, and then promote to standalone, it breaks? We need more detail to make much of a guess about that. what details can I provide? I can provide scripts that I use to test it, and also access to test machine that I was testing it on. if you'd need something else - just tell me what, i'll do my best to provide. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Problem with streaming replication, backups, and recovery (9.0.x)
hubert depesz lubaczewski dep...@depesz.com wrote: On Mon, Mar 28, 2011 at 04:24:23PM -0500, Kevin Grittner wrote: hubert depesz lubaczewski dep...@depesz.com wrote: how come that I can use this backup to make standalone pg, and it starts without any problem, but when I start it as sr slave, let it run for some time, and then promote to standalone, it breaks? We need more detail to make much of a guess about that. what details can I provide? I can provide scripts that I use to test it, and also access to test machine that I was testing it on. For starters, what do you mean by it breaks? What, exactly happens? What is in the logs? What version of PostgreSQL? Are you using pg_standby or custom scripts? -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] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 04:53:37PM -0500, Kevin Grittner wrote: hubert depesz lubaczewski dep...@depesz.com wrote: On Mon, Mar 28, 2011 at 04:24:23PM -0500, Kevin Grittner wrote: hubert depesz lubaczewski dep...@depesz.com wrote: how come that I can use this backup to make standalone pg, and it starts without any problem, but when I start it as sr slave, let it run for some time, and then promote to standalone, it breaks? We need more detail to make much of a guess about that. what details can I provide? I can provide scripts that I use to test it, and also access to test machine that I was testing it on. For starters, what do you mean by it breaks? What, exactly happens? What is in the logs? What version of PostgreSQL? Are you using pg_standby or custom scripts? hmm ... i thought that all details are in the first mail in thread. I can probably repost it, but it seems to me that it includes all of the information - which scripts, how it fails, in what cases, and what exactly i'm doing. have you seen this mail - http://archives.postgresql.org/pgsql-hackers/2011-03/msg01490.php ? Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Problem with streaming replication, backups, and recovery (9.0.x)
hubert depesz lubaczewski dep...@depesz.com wrote: have you seen this mail - http://archives.postgresql.org/pgsql-hackers/2011-03/msg01490.php Ah, OK. I have a theory. Can you try it in what would be the failure case, but run an explicit a CHECKPOINT on the master, wait for pg_controldata to show that checkpoint on the slave, and (as soon as you see that) try to trigger the slave to come up in production? -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] Problem with streaming replication, backups, and recovery (9.0.x)
hubert depesz lubaczewski dep...@depesz.com wrote: have you seen this mail - http://archives.postgresql.org/pgsql-hackers/2011-03/msg01490.php One more thing: Am I correct in understanding that you are trying to do a PITR-style backup without using pg_start_backup() and pg_stop_backup()? If so, why? -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] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 05:29:22PM -0500, Kevin Grittner wrote: hubert depesz lubaczewski dep...@depesz.com wrote: have you seen this mail - http://archives.postgresql.org/pgsql-hackers/2011-03/msg01490.php Ah, OK. I have a theory. Can you try it in what would be the failure case, but run an explicit a CHECKPOINT on the master, wait for pg_controldata to show that checkpoint on the slave, and (as soon as you see that) try to trigger the slave to come up in production? yes. will check, but it will happen in ~ 10 hours. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 05:43:15PM -0500, Kevin Grittner wrote: hubert depesz lubaczewski dep...@depesz.com wrote: have you seen this mail - http://archives.postgresql.org/pgsql-hackers/2011-03/msg01490.php One more thing: Am I correct in understanding that you are trying to do a PITR-style backup without using pg_start_backup() and pg_stop_backup()? If so, why? because this is backup on slave, and the point was to make the backup work without *any* bothering master. so far it worked fine. and generally even with 9.0 it still works, and backup *can* be used to setup new pg instance. but it cannot be used to make sr slave, which we could later on promote. Best regards, depesz -- The best thing about modern society is how easy it is to avoid contact with it. http://depesz.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] Libpq PGRES_COPY_BOTH - version compatibility
Peter Eisentraut pete...@gmx.net writes: On sön, 2011-03-27 at 00:20 -0400, Tom Lane wrote: but we haven't bumped the protocol version number since 7.4, and so I have no faith that clients will behave sensibly So we will never change the minor protocol version, because we've never done it and don't know whether it works? My feeling is we should leave it for a time when we have a protocol change to make that's actually of interest to clients (and, therefore, some benefit to them in return for any possible breakage). The case for doing it to benefit only walsender/walreceiver seems vanishingly thin to me, because in practice those are going to be quite useless if you don't have the same PG version installed at both ends anyway. Now if we had a track record showing that we could tweak the protocol version without causing problems, it'd be fine with me to do it for this usage. But we don't, and this particular case doesn't seem like the place to start. 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] Another swing at JSON
On 3/28/11 10:21 AM, Joseph Adams wrote: Currently, there are no functions for converting to/from PostgreSQL values or getting/setting sub-values (e.g. JSONPath). However, I did adapt the json_stringify function written by Itagaki Takahiro in his patch ( http://archives.postgresql.org/pgsql-hackers/2010-09/msg01200.php ). Would it be possible for you to add a TODO list for JSON support to the wiki? We have some potential GSOC students who are interested in working on JSON support. -- 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] Libpq PGRES_COPY_BOTH - version compatibility
I wrote: Now if we had a track record showing that we could tweak the protocol version without causing problems, it'd be fine with me to do it for this usage. But we don't, and this particular case doesn't seem like the place to start. And, btw, a moment's study of the protocol version checking code in postmaster.c shows that bumping the minor version number to 3.1 *would* break things: a client requesting 3.1 from a current postmaster would get a failure. Maybe we oughta change that logic --- it's not clear to me that there's any meaningful difference between major and minor numbers given the current postmaster behavior. 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] Open issues for collations
Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * It'd sure be nice if we had some nontrivial test cases that work in encodings besides UTF8. I'm still bothered that the committed patch failed to cover single-byte-encoding cases in upper/lower/initcap. Well, how do we want to maintain these test cases without doing too much duplication? It would be easy to run a small sed script over collate.linux.utf8.sql to create, say, a latin1 version out of it. I tried. The upper/lower test cases require Turkish characters that aren't in Latin1. I'm not sure if we can readily produce test cases that cover both sorting changes and case-folding changes in just one single-byte encoding --- anybody? One thing I noticed but didn't push to committing is that the test case has a largely-unnecessary assumption about how the local system's locale names spell utf8. We could eliminate that by having it use the trimmed locale names created by initdb. I would've made more of a push for that if it resulted in a test case that passed on OS X, but it turns out that once you get past the locale name spelling, you find out that Macs still can't sort UTF8 strings correctly :-( 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] Open issues for collations
Excerpts from Tom Lane's message of lun mar 28 21:02:40 -0300 2011: Peter Eisentraut pete...@gmx.net writes: On lör, 2011-03-26 at 00:36 -0400, Tom Lane wrote: * It'd sure be nice if we had some nontrivial test cases that work in encodings besides UTF8. I'm still bothered that the committed patch failed to cover single-byte-encoding cases in upper/lower/initcap. Well, how do we want to maintain these test cases without doing too much duplication? It would be easy to run a small sed script over collate.linux.utf8.sql to create, say, a latin1 version out of it. I tried. The upper/lower test cases require Turkish characters that aren't in Latin1. I'm not sure if we can readily produce test cases that cover both sorting changes and case-folding changes in just one single-byte encoding --- anybody? ISO-8859-9? -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problem with streaming replication, backups, and recovery (9.0.x)
On Mon, Mar 28, 2011 at 9:19 PM, hubert depesz lubaczewski dep...@depesz.com wrote: On Mon, Mar 28, 2011 at 01:48:13PM +0900, Fujii Masao wrote: In 9.0, recovery doesn't read a backup history file. That FATAL error happens if recovery ends before it reads the WAL record which was generated by pg_stop_backup(). IOW, recovery gets the recovery ending location from WAL record not backup history file. Since you didn't run pg_stop_backup() and there is no WAL record containing the recovery ending location, you got that error. If you want to take hot backup from the standby, you need to do the procedure explained in http://wiki.postgresql.org/wiki/Incrementally_Updated_Backups Is it intentional and/or does it serve some greater good? Yes, it's intentional. In streaming replication, at first the master must stream a backup history file to the standby in order to let it know the recovery ending position. But streaming replication doesn't have ability to send a text file, so we changed the code so that the recovery ending position was also written as WAL record which can be streamed. IIRC another reason is that it's more reliable to write down the important information like the recovery ending position to WAL record than a backup history file. I mean - ability to make backups on slave without ever bothering master was pretty interesting. Me, too. We would need to implement that in 9.2. BTW, in my system, I use another trick to take a base backup from the standby: (All of these operations are expected to be performed on the standby) (1) Run CHECKPOINT (2) Copy pg_control to temporary area (3) Take a base backup of $PGDATA (4) Copy back pg_control from temporary area to the backup taken in (2). (5) Calculate the recovery ending position from current pg_control in $PGDATA by using pg_controldata When recovery starts from that backup, it doesn't automatically check whether it has reached the ending position or not. So the user needs to check that manually. Yeah, this trick is very fragile and complicated. I'd like to improve the way in 9.2. 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] Problem with streaming replication, backups, and recovery (9.0.x)
On Tue, Mar 29, 2011 at 12:11 AM, hubert depesz lubaczewski dep...@depesz.com wrote: On Mon, Mar 28, 2011 at 01:48:13PM +0900, Fujii Masao wrote: In 9.0, recovery doesn't read a backup history file. That FATAL error happens if recovery ends before it reads the WAL record which was generated by pg_stop_backup(). IOW, recovery gets the recovery ending location from WAL record not backup history file. Since you didn't run pg_stop_backup() and there is no WAL record containing the recovery ending location, you got that error. If you want to take hot backup from the standby, you need to do the procedure explained in http://wiki.postgresql.org/wiki/Incrementally_Updated_Backups one more question. how come that I can use this backup to make standalone pg, and it starts without any problem, but when I start it as sr slave, let it run for some time, and then promote to standalone, it breaks? Did you use recovery.conf to start standalone PostgreSQL? If not, recovery doesn't check whether it reaches the recovery ending position or not. So I guess no problem didn't happen. 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] Open issues for collations
Em 28-03-2011 22:27, Alvaro Herrera escreveu: Excerpts from Tom Lane's message of lun mar 28 21:02:40 -0300 2011: I tried. The upper/lower test cases require Turkish characters that aren't in Latin1. I'm not sure if we can readily produce test cases that cover both sorting changes and case-folding changes in just one single-byte encoding --- anybody? ISO-8859-9? I'm afraid we have to map lang to single byte character set. Not all languages prefer ISO-8859. -- Euler Taveira de Oliveira http://www.timbira.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] deadlock_timeout at PGC_SIGHUP?
A few years ago, this list had a brief conversation on $SUBJECT: http://archives.postgresql.org/message-id/1215443493.4051.600.ca...@ebony.site What is notable/surprising about the behavior when two backends have different values for deadlock_timeout? After sleeping to acquire a lock, each backend will scan for deadlocks every time its own deadlock_timeout elapses. Some might be surprised that a larger-deadlock_timeout backend can still be the one to give up; consider this timeline: Backend TimeCommand A N/A SET deadlock_timeout = 1000 B N/A SET deadlock_timeout = 100 A 0 LOCK t B 50 LOCK u A 100 LOCK u B 1050LOCK t (Backend A gets an ERROR at time 1100) More generally, one cannot choose deadlock_timeout values for two sessions such that a specific session will _always_ get the ERROR. However, one can drive the probability rather high. Compare to our current lack of control. Is some other behavior that only arises when backends have different deadlock_timeout settings more surprising than that one? If we could relax deadlock_timeout to a GucContext below PGC_SIGHUP, it would probably need to stop at PGC_SUSET for now. Otherwise, an unprivileged user could increase deadlock_timeout to hide his lock waits from log_lock_waits. One could remove that limitation by introducing a separate log_lock_waits timeout, but that patch would be significantly meatier. Some might also object to PGC_USERSET on the basis that a user could unfairly preserve his transaction by setting a high deadlock_timeout. However, that user could accomplish a similar denial of service by idly holding locks or trying deadlock-prone lock acquisitions in subtransactions. nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
