Re: [HACKERS] Re: PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum
On 03.03.2011 09:12, daveg wrote: Question: what would be the consequence of simply patching out the setting of this flag? Assuming that the incorrect PD_ALL_VISIBLE flag is the only problem (big assumption perhaps) then simply never setting it would at least avoid the possibility of returning wrong answers, presumably at some performance cost. We possibly could live with that until we get a handle on the real cause and fix. Yes. With that assumption. If you really want to do that, I would suggest the attached patch instead. This just disables the optimization in seqscans to trust it, so an incorrectly set flag won't affect correctness of query results, but the flag is still set as usual and you still get the warnings so that we can continue to debug the issue. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/src/backend/access/heap/heapam.c b/src/backend/access/heap/heapam.c index 7dcc601..d53aede 100644 --- a/src/backend/access/heap/heapam.c +++ b/src/backend/access/heap/heapam.c @@ -255,6 +255,11 @@ heapgetpage(HeapScanDesc scan, BlockNumber page) * transaction in the standby. */ all_visible = PageIsAllVisible(dp) !snapshot-takenDuringRecovery; + /* + * XXX: there seems to be something wrong with the way the flag is set, + * so don't trust it. Remove this when the cause is found. + */ + all_visible = false; for (lineoff = FirstOffsetNumber, lpp = PageGetItemId(dp, lineoff); lineoff = lines; -- Sent 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 v17
On Thu, 2011-03-03 at 02:14 -0500, Tom Lane wrote:
Fujii Masao masao.fu...@gmail.com writes:
On Thu, Mar 3, 2011 at 12:11 AM, Heikki Linnakangas
heikki.linnakan...@enterprisedb.com wrote:
To achieve the effect Fujii is looking for, we would have to silently drop
the connection. That would correctly leave the client not knowing whether
the transaction committed or not.
Yeah, this seems to make more sense.
It was pointed out that sending an ERROR would not do because it would
likely lead to client code assuming the transaction failed, which might
or might not be the case. But maybe we could send a WARNING and then
close the connection? That would give humans a clue what had happened,
but not do anything to the state of automated clients.
So when we perform a Fast Shutdown we want to do something fairly
similar to quickdie()?
Please review the attached patch.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
*** a/src/backend/replication/syncrep.c
--- b/src/backend/replication/syncrep.c
***
*** 63,71
/* User-settable parameters for sync rep */
bool sync_rep_mode = false; /* Only set in user backends */
! int sync_rep_timeout = 120; /* Only set in user backends */
char *SyncRepStandbyNames;
#define IsOnSyncRepQueue() (MyProc-lwWaiting)
--- 63,72
/* User-settable parameters for sync rep */
bool sync_rep_mode = false; /* Only set in user backends */
! int sync_rep_timeout = 120; /* Only set in user backends */
char *SyncRepStandbyNames;
+ bool WaitingForSyncRep = false; /* Global state for some exit methods */
#define IsOnSyncRepQueue() (MyProc-lwWaiting)
***
*** 202,207 SyncRepWaitOnQueue(XLogRecPtr XactCommitLSN)
--- 203,209
MyProc-waitLSN = XactCommitLSN;
SyncRepAddToQueue();
LWLockRelease(SyncRepLock);
+ WaitingForSyncRep = true;
/*
* Alter ps display to show waiting for sync rep.
***
*** 241,246 SyncRepWaitOnQueue(XLogRecPtr XactCommitLSN)
--- 243,249
{
SyncRepRemoveFromQueue();
LWLockRelease(SyncRepLock);
+ WaitingForSyncRep = false;
/*
* Reset our waitLSN.
***
*** 248,254 SyncRepWaitOnQueue(XLogRecPtr XactCommitLSN)
MyProc-waitLSN.xlogid = 0;
MyProc-waitLSN.xrecoff = 0;
-
if (new_status)
{
/* Reset ps display */
--- 251,256
*** a/src/backend/tcop/postgres.c
--- b/src/backend/tcop/postgres.c
***
*** 2902,2907 ProcessInterrupts(void)
--- 2902,2935
ereport(FATAL,
(errcode(ERRCODE_ADMIN_SHUTDOWN),
errmsg(terminating autovacuum process due to administrator command)));
+ else if (WaitingForSyncRep)
+ {
+ /*
+ * This must NOT be a FATAL message. We want the state of the
+ * transaction being aborted to be indeterminate to ensure that
+ * the transaction completion guarantee is never broken.
+ */
+ ereport(WARNING,
+ (errcode(ERRCODE_ADMIN_SHUTDOWN),
+ errmsg(terminating connection because fast shutdown is requested),
+ errdetail(This connection requested synchronous replication at commit
+ yet confirmation of replication has not been received.
+ The transaction has committed locally and might be committed
+ on recently disconnected standby servers also.)));
+
+ /*
+ * We DO NOT want to run proc_exit() callbacks -- we're here because
+ * we are shutting down and don't want any code to stall or
+ * prevent that.
+ */
+ on_exit_reset();
+
+ /*
+ * Note we do exit(0) not exit(0). This is to avoid forcing
+ * postmaster into a system reset cycle.
+ */
+ exit(0);
+ }
else if (RecoveryConflictPending RecoveryConflictRetryable)
{
pgstat_report_recovery_conflict(RecoveryConflictReason);
*** a/src/include/miscadmin.h
--- b/src/include/miscadmin.h
***
*** 78,83 extern PGDLLIMPORT volatile uint32 CritSectionCount;
--- 78,86
/* in tcop/postgres.c */
extern void ProcessInterrupts(void);
+ /* in replication/syncrep.c */
+ extern bool WaitingForSyncRep;
+
#ifndef WIN32
#define CHECK_FOR_INTERRUPTS() \
--
Sent 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 v17
Robert Haas robertmh...@gmail.com writes: I don't understand how synchronous replication with allow_standalone_primary=on gives you ANY extra nines. AFAICS, the only point of having synchronous replication is that you wait to acknowledge the commit to the client until the commit record has been replicated. Doing that only when the standby happens to be connected doesn't seem like it helps much. Because you're still thinking in terms of data availability, rather than in terms of service availability. With the later in mind, what you want is to be able to continue servicing from the standby should the primary crash, and you want a good guarantee about the standby's data. Of course in such a situation you will have some monitoring to ensure that the standby remains in sync, and you want to know that at failover time. But a standby failure, when you want service availability, should never bring the service down. It's what happens, though, if you've been setting up *data* availability, because there there's no choice. 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] Quick Extensions Question
Tom Lane t...@sss.pgh.pa.us writes: Probably in future the standard PLs will be packaged as extensions, and then it will work. The main reason that it won't happen for 9.1 is that right now we require superuser privilege to install an extension, which would be a regression compared to the privilege requirements for installing standard PLs in existing releases. And relaxing that requirement is a research project :-( Meanwhile, is it possible to have CREATE LANGUAGE internally register an extension? It's a kludge but I somehow though I'd mention it. 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
[HACKERS] aggregate version of first_value function?
We have window function version of first_value(), but aggregate version looks useful to write queries something like: =# CREATE TABLE obj (id integer, pos point); =# SELECT X.id, first_value(Y.id ORDER BY X.pos - Y.pos) AS neighbor FROM obj X, obj Y GROUP BY X.id; Is it reasonable? Or, do we have alternative ways for the same purpose? -- Itagaki Takahiro -- Sent 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: PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum
On Thu, Mar 03, 2011 at 10:16:29AM +0200, Heikki Linnakangas wrote: On 03.03.2011 09:12, daveg wrote: Question: what would be the consequence of simply patching out the setting of this flag? Assuming that the incorrect PD_ALL_VISIBLE flag is the only problem (big assumption perhaps) then simply never setting it would at least avoid the possibility of returning wrong answers, presumably at some performance cost. We possibly could live with that until we get a handle on the real cause and fix. Yes. With that assumption. If you really want to do that, I would suggest the attached patch instead. This just disables the optimization in seqscans to trust it, so an incorrectly set flag won't affect correctness of query results, but the flag is still set as usual and you still get the warnings so that we can continue to debug the issue. Thanks. I'll be applying this tomorrow and will send you some page images to look at assuming it still does it. I had a look at how this gets set and cleared and did not see anything obvious so I'm pretty mystified. Also, we are seeing thousands of these daily for at least a month on 4 large hosts and no-one has noticed any other issues, which suprises me. Very strange. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] knngist - 0.8
Thanks, Tom ! Oleg On Wed, 2 Mar 2011, Tom Lane wrote: Teodor Sigaev teo...@sigaev.ru writes: [ builtin_knngist_contrib_btree_gist-0.12 patch ] Applied with some corrections --- mostly, that the upgrade script was all wet. I added some documentation too. regards, tom lane Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_depend dependency and concurrent DDL issues in PG 8.3.x
Hi, Am referring to the following conversation: http://archives.postgresql.org/pgsql-bugs/2007-12/msg00190.php To summarize, in 8.3.x due to improper locking and concurrency issues in the DROP OBJECT codepath, for example if one tries to drop an index while dropping the table from another session, we end up with orphaned index objects. There are similar issues related to orphaned triggers, tuple concurrent update errors etc. in that thread. s1= CREATE TABLE x(i integer); s2= BEGIN; s2= CREATE UNIQUE INDEX x_pkey ON x(i); s1= DROP TABLE x; (Session hangs) s2= COMMIT I see that all these issues have been fixed and committed by Tom via git commitid: 281a724d on 6th June, 2008. Was wondering why this fix is not in these supported branches like 8.3.13 for example. Kinda confused.. Regards, Nikhils -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Porting PostgreSQL to DragonFly BSD
On Wednesday 2. of March 2011 20:18:08 Peter Eisentraut wrote: On ons, 2011-03-02 at 09:10 +0100, Rumko wrote: What about this patch ( http://www.rumko.net/0001-DragonFly-BSD-support-linked-nbsd.patch )? instead of linking to freebsd, it's linked to netbsd and It still compiles due to the two templates being similar enough. Looks good. Committed. Thank you. -- Regards, Rumko signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Perl 5.12 complains about ecpg parser-hacking scripts
On Wed, Mar 02, 2011 at 01:33:35PM -0600, Andy Colson wrote: I thought Kris was going to work on this, but saw no progress, and I was bored the other day, so I started working on it. Here is a parse.pl, with some major refactoring. I named it with a 2 so I could run it beside the original and diff em: Thanks for all the work. I am sure there are new bugs. I have not run it on anything but 9.0.1. Are there other .y files you might feed it? (something other than backend/parser/gram.y?) I ran it against several versions and it always gave the right output. So i decided to just commit it to the archive so we can see if it breaks anything. The old script is still in there so in case of a major problem that I cannot foresee we can simply change the Makefile back to using parse.pl. Michael -- Michael Meskes Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) Michael at BorussiaFan dot De, Meskes at (Debian|Postgresql) dot Org Jabber: michael.meskes at googlemail dot com VfL Borussia! Força Barça! Go SF 49ers! Use Debian GNU/Linux, PostgreSQL -- Sent 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 v17
On Wed, Mar 2, 2011 at 5:10 PM, Yeb Havinga yebhavi...@gmail.com wrote: On 2011-03-02 21:26, Kevin Grittner wrote: I think including synchronous is OK as long as it's properly qualified. Off-hand thoughts in no particular order: semi-synchronous conditionally synchronous synchronous with automatic failover to standalone It would be good to name the concept equal to how other DBMSses call it, if they have a similar concept - don't know if Mysql's semisynchronous replication is the same, but after a quick read it sounds like it does. Here's the link: http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html I think this is mostly about how many slaves have to ack the commit. It's not entirely clear to me what happens if a slave is set up but not connected 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] pg_depend dependency and concurrent DDL issues in PG 8.3.x
On Thu, Mar 3, 2011 at 6:09 AM, Nikhil Sontakke nikhil.sonta...@enterprisedb.com wrote: I see that all these issues have been fixed and committed by Tom via git commitid: 281a724d on 6th June, 2008. Was wondering why this fix is not in these supported branches like 8.3.13 for example. Kinda confused.. We don't usually back-patch such large changes. -- 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] pg_depend dependency and concurrent DDL issues in PG 8.3.x
I see that all these issues have been fixed and committed by Tom via git commitid: 281a724d on 6th June, 2008. Was wondering why this fix is not in these supported branches like 8.3.13 for example. Kinda confused.. We don't usually back-patch such large changes. Oh ok. Thanks. Regards, Nikhils -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WAL segments pile up during standalone mode
Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. -- Á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] WAL segments pile up during standalone mode
On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. I don't think it would violate the POLA for a standalone backend to checkpoint periodically, but I have to admit I can count the number of times I've run a standalone backend on one hand. Does this come up much? -- 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] Quick Extensions Question
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: Probably in future the standard PLs will be packaged as extensions, and then it will work. The main reason that it won't happen for 9.1 is that right now we require superuser privilege to install an extension, which would be a regression compared to the privilege requirements for installing standard PLs in existing releases. And relaxing that requirement is a research project :-( Meanwhile, is it possible to have CREATE LANGUAGE internally register an extension? It's a kludge but I somehow though I'd mention it. Hmmm ... it definitely is a kluge, but ... You'd need to work out how the CREATE OR REPLACE and DROP cases would work. 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] aggregate version of first_value function?
Itagaki Takahiro itagaki.takah...@gmail.com writes: We have window function version of first_value(), but aggregate version looks useful to write queries something like: =# CREATE TABLE obj (id integer, pos point); =# SELECT X.id, first_value(Y.id ORDER BY X.pos - Y.pos) AS neighbor FROM obj X, obj Y GROUP BY X.id; Is it reasonable? Or, do we have alternative ways for the same purpose? I don't see any good reason to encourage people to write that in a nonstandard way when there's a prefectly good standard way, ie, use the window-function version. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Open unmatch source file when step into parse_analyze() in Eclipse?
Hi, I'm debug Postgresql with Eclipse under Redhat and I met a problem. when I step in then function parse_analyze(), Eclipse opened file src/backend/commands/analyze.c. But actually, function parse_analyze() should match the file src/backend/parser/analyze.c. So I found the execute order didn't match the source code. How can I make the Eclipse open the right file? Thank you for answering PS: I have set compile optimization level to 0 with CFLAGS='-O0' and it worked well in other source code. -- Best Wishes! hom -- Sent 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 3, 2011 at 7:53 PM, Simon Riggs si...@2ndquadrant.com wrote:
Latest version of Sync Rep, which includes substantial internal changes
and simplifications from previous version. (25-30 changes).
Includes all outstanding technical comments, typos and docs. I will
continue to work on self review and test myself, though actively
encourage others to test and report issues.
Thanks for the patch!
* synchronous_standby_names = * matches all standby names
Using '*' as the default seems to lead the performance degradation by
being connected from unexpected synchronous standby.
* pg_stat_replication now shows standby priority - this is an ordinal
number so 1 means 1st, 2 means 2nd etc, though 0 means not a sync
standby.
monitoring.sgml should be updated.
Though I've not read whole of the patch yet, here is the current comment:
Using MyProc-lwWaiting and lwWaitLink for backends to wait for replication
looks fragile. Since they are used also by lwlock, the value of them can be
changed unexpectedly. Instead, how about defining dedicated variables for
replication?
+ else if (WaitingForSyncRep)
+ {
+ /*
+* This must NOT be a FATAL message. We want the state
of the
+* transaction being aborted to be indeterminate to
ensure that
+* the transaction completion guarantee is never broken.
+*/
The backend can reach this code path after returning the commit to the client.
Instead, how about doing this in EndCommand, to close the connection before
returning the commit?
+ LWLockAcquire(SyncRepLock, LW_EXCLUSIVE);
+ sync_priority = walsnd-sync_standby_priority;
+ LWLockRelease(SyncRepLock);
LW_SHARE can be used here, instead.
+ /*
+* Wait no longer if we have already reached our LSN
+*/
+ if (XLByteLE(XactCommitLSN, queue-lsn))
+ {
+ /* No need to wait */
+ LWLockRelease(SyncRepLock);
+ return;
+ }
It might take long to acquire SyncRepLock, so how about comparing
our LSN with WalSnd-flush before here?
replication_timeout_client depends on GetCurrentTransactionStopTimestamp().
In COMMIT case, it's OK. But In PREPARE TRANSACTION, COMMIT PREPARED
and ROLLBACK PREPARED cases, it seems problematic because they don't call
SetCurrentTransactionStopTimestamp().
In SyncRepWaitOnQueue, the backend can theoretically call WaitLatch() again
after the wake-up from the latch. In this case, the timeout should
be calculated
again. Otherwise, it would take unexpectedly very long to cause the timeout.
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] Re: PD_ALL_VISIBLE flag was incorrectly set happend during repeatable vacuum
On Thu, Mar 3, 2011 at 2:16 AM, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: On 03.03.2011 09:12, daveg wrote: Question: what would be the consequence of simply patching out the setting of this flag? Assuming that the incorrect PD_ALL_VISIBLE flag is the only problem (big assumption perhaps) then simply never setting it would at least avoid the possibility of returning wrong answers, presumably at some performance cost. We possibly could live with that until we get a handle on the real cause and fix. Yes. With that assumption. If you really want to do that, I would suggest the attached patch instead. This just disables the optimization in seqscans to trust it, so an incorrectly set flag won't affect correctness of query results, but the flag is still set as usual and you still get the warnings so that we can continue to debug the issue. This. The mis-set flag can is likely a bug/concurrency issue etc, but could also be a symptom of more sinister data corruption. I did various vacuum experiments all day yesterday on my windows workstation and was not able to produce any mis-flags. I trust iscsi more than nfs, but maybe there is a connection here that is hardware based. hm. do you think it would be helpful to know what is causing the all_visible flag to get flipped? If so, the attached patch shows which case is throwing it... merlin visible_debug.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
[HACKERS] Mark deprecated operators as such in their comments?
I finally got around to completing the function-comments cleanup proposed here: http://archives.postgresql.org/pgsql-docs/2010-10/msg00041.php There are now a heck of a lot of boilerplate comments like DESCR(implementation of + operator); in pg_proc.h (about 700 of 'em to be exact). My original plan had involved getting initdb to generate those comments automatically instead of having to maintain them manually, but I desisted from that after noticing that there are various cases where we have multiple operators linking to the same pg_proc entry, so initdb wouldn't know which one to pick. But thinking about it this morning, I realize that all those cases are ones where we've replaced an old spelling of an operator name with a better choice, and really the old entry is deprecated but we still have it for compatibility reasons. So we could teach initdb how to build the desired comments if there were some easy way for it to recognize the deprecated operators. The obvious way to do that is to put something like deprecated, use @ instead in the comment for the deprecated version. This seems like a good idea from a user's standpoint too, considering that the entire motivation for this effort was to ensure that \df (and by extension \do) output will tell you to avoid non-preferred ways of spelling a function/operator call. (BTW, the operators in question are @, ~, and @@@ uses that are now preferred to be spelled @, @, and @@ respectively.) So, two questions: 1. Do people like the idea of marking obsolete operator names this way? If so, exactly how to mark them? We could try to add (deprecated, ...) at the end of the existing description, or just replace the description completely. In some of these cases the existing description is pretty long, making the latter attractive. 2. Given that we do #1, is it really a good idea to generate the boilerplate comments automatically? The argument I can see against it is that right now there's a pretty simple coding rule every pg_proc.h entry should have a comment. This is less confusing than every pg_proc.h entry should have a comment, except those that are linked to pg_operator entries and aren't meant to be used directly. I'm not sure that argument outweighs writing the boilerplate comment is a PITA, but I'm not sure it doesn't either. Comments? 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] WAL segments pile up during standalone mode
Excerpts from Robert Haas's message of jue mar 03 11:18:38 -0300 2011: On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. I don't think it would violate the POLA for a standalone backend to checkpoint periodically, but I have to admit I can count the number of times I've run a standalone backend on one hand. Does this come up much? I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint during vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? -- Á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] Quick Extensions Question
Tom Lane t...@sss.pgh.pa.us writes: Meanwhile, is it possible to have CREATE LANGUAGE internally register an extension? It's a kludge but I somehow though I'd mention it. Hmmm ... it definitely is a kluge, but ... You'd need to work out how the CREATE OR REPLACE and DROP cases would work. Maybe the fever ain't gone far enough, but I'd just do nothing in the first case and internally cascade to the extension in the second case. In fact internally the drop case would be redirected on the extension and the dependencies would get rid of the PL, right? -- 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] Quick Extensions Question
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: You'd need to work out how the CREATE OR REPLACE and DROP cases would work. Maybe the fever ain't gone far enough, but I'd just do nothing in the first case and internally cascade to the extension in the second case. In fact internally the drop case would be redirected on the extension and the dependencies would get rid of the PL, right? Not sure it's that easy. I think DROP LANGUAGE can't assume that the language it's been told to drop is extension-ified. (Even if we do this for all the core ones, there are a dozen non-core ones that might not all get with the program right away.) How do we make this work in a way that covers both cases, but doesn't turn DROP LANGUAGE into a security hole that lets non-superusers drop random extensions? It may all work pretty easily, but I'm still caffeine-deprived so I'm not sure ... 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] WAL segments pile up during standalone mode
Alvaro Herrera alvhe...@commandprompt.com writes: I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint during vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? I really don't care for the idea of standalone mode doing *anything* the user didn't explicitly tell it to. In its role as a disaster recovery tool, that's just a recipe for shooting yourself in the foot. Perhaps this problem would be adequately addressed by documentation, ie suggest that when vacuuming very large tables in standalone mode, you should issue CHECKPOINT after each one. 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] WAL segments pile up during standalone mode
On Thu, Mar 3, 2011 at 10:16 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Robert Haas's message of jue mar 03 11:18:38 -0300 2011: On Thu, Mar 3, 2011 at 9:15 AM, Alvaro Herrera alvhe...@commandprompt.com wrote: Excerpts from Fujii Masao's message of mié mar 02 22:44:45 -0300 2011: On Thu, Mar 3, 2011 at 3:22 AM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: I noticed that in standalone mode, WAL segments don't seem to be recycled. This could get problematic if you're forced to vacuum large tables in that mode and space for WAL is short. Checkpoint is required to recycle old WAL segments. Can checkpoint be executed in standalone mode? even during VACUUM FULL? Hmm, I guess it would violate POLA that the standalone server would decide to run checkpoint in the middle of vacuum. I imagine that in some cases the only option would be to process the tables manually, with the ALTER TABLE/SET TYPE trick or similar (VACUUM FULL in 9.0+). So I can see that there is no good fix for this problem, yet it is a very inconvenient situation to be in. I don't think it would violate the POLA for a standalone backend to checkpoint periodically, but I have to admit I can count the number of times I've run a standalone backend on one hand. Does this come up much? I admit I have no idea why these guys seem to run into wraparound problems so much. On the other hand, I'm not sure that it would work to try to checkpoint during vacuum, because the backend is in a transaction. Maybe it would work to force a checkpoint after each command, and between tables in a multi-table vacuum (which is presumably a common thing to do in a standalone backend) or something like that? I doubt it's necessary to force a checkpoint after each command - I assume that if you want one, you can just execute the CHECKPOINT command explicitly. The multi-table VACUUM case could be handled similarly - VACUUM each table, then checkpoint, and so on. It'd probably be more worthwhile to pursue the approach of allowing the system to be brought up in multi-user mode, but allow only super-users to log in and don't allow them to do anything except VACUUM until some semblance of sanity is achieved. -- 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] Mark deprecated operators as such in their comments?
On Thu, Mar 3, 2011 at 10:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: I finally got around to completing the function-comments cleanup proposed here: http://archives.postgresql.org/pgsql-docs/2010-10/msg00041.php There are now a heck of a lot of boilerplate comments like DESCR(implementation of + operator); in pg_proc.h (about 700 of 'em to be exact). My original plan had involved getting initdb to generate those comments automatically instead of having to maintain them manually, but I desisted from that after noticing that there are various cases where we have multiple operators linking to the same pg_proc entry, so initdb wouldn't know which one to pick. But thinking about it this morning, I realize that all those cases are ones where we've replaced an old spelling of an operator name with a better choice, and really the old entry is deprecated but we still have it for compatibility reasons. So we could teach initdb how to build the desired comments if there were some easy way for it to recognize the deprecated operators. The obvious way to do that is to put something like deprecated, use @ instead in the comment for the deprecated version. This seems like a good idea from a user's standpoint too, considering that the entire motivation for this effort was to ensure that \df (and by extension \do) output will tell you to avoid non-preferred ways of spelling a function/operator call. (BTW, the operators in question are @, ~, and @@@ uses that are now preferred to be spelled @, @, and @@ respectively.) So, two questions: 1. Do people like the idea of marking obsolete operator names this way? If so, exactly how to mark them? We could try to add (deprecated, ...) at the end of the existing description, or just replace the description completely. In some of these cases the existing description is pretty long, making the latter attractive. Deprecated, use blah instead? 2. Given that we do #1, is it really a good idea to generate the boilerplate comments automatically? The argument I can see against it is that right now there's a pretty simple coding rule every pg_proc.h entry should have a comment. This is less confusing than every pg_proc.h entry should have a comment, except those that are linked to pg_operator entries and aren't meant to be used directly. I'm not sure that argument outweighs writing the boilerplate comment is a PITA, but I'm not sure it doesn't either. I think the chances that future patches will follow the more complex coding rule are near zero, absent some type of automated enforcement mechanism. -- 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] Mark deprecated operators as such in their comments?
Robert Haas robertmh...@gmail.com writes: On Thu, Mar 3, 2011 at 10:13 AM, Tom Lane t...@sss.pgh.pa.us wrote: 2. Given that we do #1, is it really a good idea to generate the boilerplate comments automatically? The argument I can see against it is that right now there's a pretty simple coding rule every pg_proc.h entry should have a comment. This is less confusing than every pg_proc.h entry should have a comment, except those that are linked to pg_operator entries and aren't meant to be used directly. I'm not sure that argument outweighs writing the boilerplate comment is a PITA, but I'm not sure it doesn't either. I think the chances that future patches will follow the more complex coding rule are near zero, absent some type of automated enforcement mechanism. Well, there is an enforcement mechanism: the regression tests will now complain if any pg_proc.h entry lacks a comment. What they can't do very well is enforce that the comment is sanely chosen. In particular the likely failure mechanism is that someone submits a custom comment for a function that would be better off being labeled as implementation of XXX operator. But AFAICS such a mistake is about equally likely with either approach, maybe even a tad more so if submitters are forced to comment every function instead of having an automatic default. 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] sepgsql contrib module
On Thu, Mar 3, 2011 at 5:38 AM, Kohei Kaigai kohei.kai...@eu.nec.com wrote: BTW, it seems to me the base version of selinux-policy-* package in Ubuntu is forked from an older snapshot (20091117), so it does not have enough rules to run SE-PostgreSQL. Right now, Fedora 13/14 is the easiest way. Yeah. I think that pretty much sucks, because really we would like this to work wherever SE-Linux works. But I suppose in time it will fix itself. -- 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] Quick Extensions Question
On Thu, Mar 3, 2011 at 10:31 AM, Tom Lane t...@sss.pgh.pa.us wrote: It may all work pretty easily, but I'm still caffeine-deprived so I'm not sure ... I think that it's not a good idea to devote too much energy to this problem right now, anyway. We have crammed a gigantic pile of code into the source tree in the last month, and there are bound to be bugs even in what we already have. We need to get that code out into the field in the form of alpha and beta releases and start getting it tested; and even apart from bugs, we need to get some field experience with it, so that we learn which things work well enough in practice and which things are really problems. Then we can come back to the table and engineer better solutions for 9.2. It is also important that we get to the point of being able to start accepting 9.2 patches on other topics as quickly as possible. Unless we do something radically different than what we have done in previous releases, we are now about to enter a ~4 month period during which there will be no CommitFests and very little 9.2 work underway, at least publicly. Even with the improvements we have made in getting CommitFests started and finished on time (the present case being, fortunately, an exception, and yeah I know it could have been a lot worse, but it could also have been better), the long quiet period that is required to get a release out the door is still problematic for many of our developers. -- 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] Quick Extensions Question
[ slightly more awake now ... ] I wrote: Not sure it's that easy. I think DROP LANGUAGE can't assume that the language it's been told to drop is extension-ified. (Even if we do this for all the core ones, there are a dozen non-core ones that might not all get with the program right away.) No, wait, that's nonsense. With what you're talking about, there would never be a non-extension-ified language, because CREATE LANGUAGE would force it to be attached to an extension. So maybe that problem isn't so hard after all. However, what *is* looking a bit hard is dump/restore behavior. By default, pg_dump would proceed to dump an installed language as a CREATE EXTENSION command, and that would fail on restore, especially if you were trying to restore as non superuser. The behavior of --binary-upgrade would be differently unpleasant: it would make the extension, then try to CREATE LANGUAGE, and that would fail because the extension name already exists. No doubt we could kluge those behaviors too, but it's starting to look pretty messy. On the whole I'd rather spend time trying to fix the real problem, which is allowing CREATE EXTENSION to non-superusers. The general case of that looks quite hard to me, but maybe we could get something that works for the single case of an extension containing just a language. 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] Mark deprecated operators as such in their comments?
On Thu, Mar 3, 2011 at 3:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: 1. Do people like the idea of marking obsolete operator names this way? If so, exactly how to mark them? We could try to add (deprecated, ...) at the end of the existing description, or just replace the description completely. In some of these cases the existing description is pretty long, making the latter attractive. Marking deprecated legacy names is nice. I hate as a programmer being confronted with multiple identical-sounding options and not knowing which one I should be using. 2. Given that we do #1, is it really a good idea to generate the boilerplate comments automatically? The argument I can see against it is that right now there's a pretty simple coding rule every pg_proc.h entry should have a comment. This is less confusing than every pg_proc.h entry should have a comment, except those that are linked to pg_operator entries and aren't meant to be used directly. I'm not sure that argument outweighs writing the boilerplate comment is a PITA, but I'm not sure it doesn't either. A way out might be to have a token in the DESCR like - or $opr or something which indicates substitute the default description here. But I'm not sure it's worth bothering. Filling in the description field is hardly the most annoying part of adding pg_proc entries for operators. If we could move most or all of the entries to an SQL file so that we didn't have to deal with commutator and negator oids and all that, that would save a lot of pain. -- 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] sepgsql contrib module
Sorry so much! I thought I replied to the question already, but not yet. $ find /usr/share/selinux -name '*ake*' /usr/share/selinux/default/include/Makefile /usr/share/selinux/ubuntu/include/Makefile /usr/share/selinux/mls/include/Makefile Not sure which of these would be the right one to use. The 4th level entry shall be replaced by policy type. So, if ubuntu policy type is available on the system, the Makefile we shall use is /usr/share/selinux/ubuntu/include/Makefile . ^^ We can confirm the current available policy type from /etc/selinux/config or using sestatus command. [kaigai@vmlinux tmp]$ sestatus SELinux status: enabled SELinuxfs mount:/selinux Current mode: enforcing Mode from config file: enforcing Policy version: 24 Policy from config file:targeted It is the policy type. In this case, the current available policy type is targeted. BTW, it seems to me the base version of selinux-policy-* package in Ubuntu is forked from an older snapshot (20091117), so it does not have enough rules to run SE-PostgreSQL. Right now, Fedora 13/14 is the easiest way. Thanks, -- NEC Europe Ltd, Global Competence Center KaiGai Kohei kohei.kai...@eu.nec.com -Original Message- From: Robert Haas [mailto:robertmh...@gmail.com] Sent: 17. Februar 2011 11:42 To: Kohei Kaigai Cc: Tom Lane; Andrew Dunstan; Stephen Frost; KaiGai Kohei; PgHacker Subject: Re: [HACKERS] sepgsql contrib module On Thu, Feb 17, 2011 at 3:56 AM, Kohei Kaigai kohei.kai...@eu.nec.com wrote: The attached patch removes rules to build a policy package for regression test and modifies documentation part to introduce steps to run the test. Committed. Incidentally, on my Ubuntu system: $ find /usr/share/selinux -name '*ake*' /usr/share/selinux/default/include/Makefile /usr/share/selinux/ubuntu/include/Makefile /usr/share/selinux/mls/include/Makefile Not sure which of these would be the right one to use. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company Click https://www.mailcontrol.com/sr/1JPOTPNZc+vTndxI!oX7UnkyRQ0MRq91W9aRlCO 56S1wi0rtpLI1rpvj957f8eUOrAhhBS0z5yrieLvRJKIvyA== to report this email as spam. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
Robert Haas robertmh...@gmail.com writes: I think that it's not a good idea to devote too much energy to this problem right now, anyway. [ we need to get to beta ASAP, instead ] I hear you, but once we get to beta, or even the last alpha, it's going to be very hard to make changes that would interfere with people doing upgrades or dump/restores. If we don't do something about the language- as-extension situation right now, the window will be closed until 9.2. Most of the other things that are on our plates for beta are internal changes that would be unlikely to break the upgrade path for beta testers. You may well be right that there's no way to fix this with an amount of effort that would be appropriate to spend now, but I don't want to just walk away from the problem without considering whether there is a way that we can fix it with a day or so's additional effort. 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 unmatch source file when step into parse_analyze() in Eclipse?
On 03.03.2011 16:46, hom wrote: Hi, I'm debug Postgresql with Eclipse under Redhat and I met a problem. when I step in then function parse_analyze(), Eclipse opened file src/backend/commands/analyze.c. But actually, function parse_analyze() should match the file src/backend/parser/analyze.c. So I found the execute order didn't match the source code. How can I make the Eclipse open the right file? Hmm, seems like a bug in Eclipse debugger. I found this: https://bugs.eclipse.org/bugs/show_bug.cgi?id=35960 In the next-to-last comment, Mikhail Khodjaiants suggests checking the Search for duplicate source files option in the launch configuration. -- 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] Quick Extensions Question
Tom Lane t...@sss.pgh.pa.us writes: Not sure it's that easy. I think DROP LANGUAGE can't assume that the language it's been told to drop is extension-ified. (Even if we do this If CREATE LANGUAGE creates an extension of the same name, then DROP LANGUAGE can assume that there's an extension of the same name, right? for all the core ones, there are a dozen non-core ones that might not all get with the program right away.) How do we make this work in a way that covers both cases, but doesn't turn DROP LANGUAGE into a security hole that lets non-superusers drop random extensions? We could check that the extension named the same as the language only contains one object of class pg_language. It may all work pretty easily, but I'm still caffeine-deprived so I'm not sure ... It does not look like a big deal to me either. If you don't have the time too, I could propose a patch. 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] Quick Extensions Question
On Thu, Mar 3, 2011 at 11:19 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I think that it's not a good idea to devote too much energy to this problem right now, anyway. [ we need to get to beta ASAP, instead ] I hear you, but once we get to beta, or even the last alpha, it's going to be very hard to make changes that would interfere with people doing upgrades or dump/restores. If we don't do something about the language- as-extension situation right now, the window will be closed until 9.2. So what? AFAIK the extension patch hasn't broken anything here that used to work. People can still install languages the way they always have. What we're talking about here is a way of installing languages that is arguably nicer than what they are doing now. The window for feature enhancements is already closed until 9.2, unless you want to go back and start working through every patch we marked Returned with Feedback during this last CommitFest. -- 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] Quick Extensions Question
On 03.03.2011 18:30, Robert Haas wrote: On Thu, Mar 3, 2011 at 11:19 AM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haasrobertmh...@gmail.com writes: I think that it's not a good idea to devote too much energy to this problem right now, anyway. [ we need to get to beta ASAP, instead ] I hear you, but once we get to beta, or even the last alpha, it's going to be very hard to make changes that would interfere with people doing upgrades or dump/restores. If we don't do something about the language- as-extension situation right now, the window will be closed until 9.2. So what? AFAIK the extension patch hasn't broken anything here that used to work. People can still install languages the way they always have. What we're talking about here is a way of installing languages that is arguably nicer than what they are doing now. IMHO the main advantage of having languages as extensions is that you could define a dependency on a language. We've been talking about PLs, but what about the other thing David asked: could we have extension entries for compile-time options like SSL or libxml, so that you could define a dependency on them? -- 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] Quick Extensions Question
On Thu, Mar 3, 2011 at 11:39 AM, Aidan Van Dyk ai...@highrise.ca wrote: No, what is being talked about isn't intended as a way of installing languages that is ... nicer. What is being talked about is allowing an Extension that is being installed know that it's going to blow up because it's required language (plpgsql, for instance) isn't installed. Maybe it's a problem with extensions that isn't easily solvable, but that means extension authors are going to have a readme in their extension with the followign text: EXTENSION mystuff requires that pl/pgsql be installed in the database. There is no way for the extension to check this before it is installed, so make sure it's installed, or be prepared to cope with errors during the installation. And make sure you don't try and drop pl/pgsql language when the extension is installed either. Maybe that's enough for 9.1. Well, in 9.0, what'll happen is you'll type psql -f somefile.sql and it'll spit out a stream of error messages if things aren't in the prerequisite state. Unless you happen to have taken the precaution of wrapping the whole thing in a transaction, you may end up with the extension half-installed and some fun manual cleanup to do. I'm not going to argue that this is perfect, but it is already better than it was. The extensions patch didn't change much between mid-Decemeber when we wrapped up CF#3 and early February when Tom picked it up. If he wanted to have a chance to do lots more refinement after the initial commit, he had the means within his grasp: he could have picked it up in December. It is no more fair for Tom to hold up this release to work on extensions than it is for Simon to hold it up to work on sync rep. Jeff Davis would have loved to get range types in, Alvaro would have liked to do finish his work on foreign key locks, and there are other examples as well. We can't expect anyone to be willing to step aside graciously when time has expired unless everyone is willing to do it. By the way, I don't question Tom's decision to leave this patch until February. He worked on other things. Who am I to say that they were any less valuable than this? But you can't have your cake and eat it too. There's a part of me that wonders whether we'd get the same number of features per release if we shortened the release cycle by one CommitFest. Most large patches get done in 2 or 3 CommitFests, so it would still be entirely reasonable to get a major feature done in one release. Right now we seem to have two classes of people: the ones who start working at the beginning of the cycle, and are done by CF#2/#3, and the ones who start at the end of the cycle, and hold things up at the end. Neither group would get any less done on a shorter cycle. Contrariwise I bet if we went to 5 CommitFests we'd see only a slight increase in patches - they'd just be spread out over more calendar time. It's already the case that the middle two CommitFests are smaller than the first and last. -- 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] Quick Extensions Question
On Thu, Mar 3, 2011 at 4:30 PM, Robert Haas robertmh...@gmail.com wrote: So what? AFAIK the extension patch hasn't broken anything here that used to work. People can still install languages the way they always have. What we're talking about here is a way of installing languages that is arguably nicer than what they are doing now. The window for feature enhancements is already closed until 9.2, unless you want to go back and start working through every patch we marked Returned with Feedback during this last CommitFest. No, what is being talked about isn't intended as a way of installing languages that is ... nicer. What is being talked about is allowing an Extension that is being installed know that it's going to blow up because it's required language (plpgsql, for instance) isn't installed. Maybe it's a problem with extensions that isn't easily solvable, but that means extension authors are going to have a readme in their extension with the followign text: EXTENSION mystuff requires that pl/pgsql be installed in the database. There is no way for the extension to check this before it is installed, so make sure it's installed, or be prepared to cope with errors during the installation. And make sure you don't try and drop pl/pgsql language when the extension is installed either. Maybe that's enough for 9.1. a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Testing extension upgrade scripts
da...@kineticode.com (David E. Wheeler) writes: You should blog this. He just did, using the SMTP protocol... -- select 'cbbrowne' || '@' || 'acm.org'; http://linuxdatabases.info/info/postgresql.html Where do you want to Tell Microsoft To Go Today? -- Sent 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 Fri, 2011-03-04 at 00:02 +0900, Fujii Masao wrote:
* synchronous_standby_names = * matches all standby names
Using '*' as the default seems to lead the performance degradation by
being connected from unexpected synchronous standby.
You can configure it however you wish. It seemed better to have an out
of the box setting that was useful.
* pg_stat_replication now shows standby priority - this is an ordinal
number so 1 means 1st, 2 means 2nd etc, though 0 means not a sync
standby.
monitoring.sgml should be updated.
Didn't think it needed to be, but I've added a few lines to explain.
Though I've not read whole of the patch yet, here is the current comment:
Using MyProc-lwWaiting and lwWaitLink for backends to wait for replication
looks fragile. Since they are used also by lwlock, the value of them can be
changed unexpectedly. Instead, how about defining dedicated variables for
replication?
Yes, I think the queue stuff needs a rewrite now.
+ else if (WaitingForSyncRep)
+ {
+ /*
+ * This must NOT be a FATAL message. We want the state
of the
+ * transaction being aborted to be indeterminate to
ensure that
+ * the transaction completion guarantee is never broken.
+ */
The backend can reach this code path after returning the commit to the client.
Instead, how about doing this in EndCommand, to close the connection before
returning the commit?
OK, will look.
+ LWLockAcquire(SyncRepLock, LW_EXCLUSIVE);
+ sync_priority = walsnd-sync_standby_priority;
+ LWLockRelease(SyncRepLock);
LW_SHARE can be used here, instead.
Seemed easier to keep it simple and have all lockers use LW_EXCLUSIVE.
But I've changed it for you.
+ /*
+ * Wait no longer if we have already reached our LSN
+ */
+ if (XLByteLE(XactCommitLSN, queue-lsn))
+ {
+ /* No need to wait */
+ LWLockRelease(SyncRepLock);
+ return;
+ }
It might take long to acquire SyncRepLock, so how about comparing
our LSN with WalSnd-flush before here?
If we're not the sync standby and we need to takeover the role of sync
standby we may need to issue a wakeup even though our standby reached
that LSN some time before. So we need to check each time.
replication_timeout_client depends on GetCurrentTransactionStopTimestamp().
In COMMIT case, it's OK. But In PREPARE TRANSACTION, COMMIT PREPARED
and ROLLBACK PREPARED cases, it seems problematic because they don't call
SetCurrentTransactionStopTimestamp().
Shame on them!
Seems reasonable that they should call
SetCurrentTransactionStopTimestamp().
I don't want to make a special case there for prepared transactions.
In SyncRepWaitOnQueue, the backend can theoretically call WaitLatch() again
after the wake-up from the latch. In this case, the timeout should
be calculated
again. Otherwise, it would take unexpectedly very long to cause the timeout.
That was originally modelled on on the way the statement_timeout timer
works. If it gets nudged and wakes up too early it puts itself back to
sleep to wakeup at the same time again.
I've renamed the variables to make that clearer and edited slightly.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and 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] Mark deprecated operators as such in their comments?
I wrote: Robert Haas robertmh...@gmail.com writes: Deprecated, use blah instead? Everybody seems happy with that part of the proposal, so I'll make it happen. I think the chances that future patches will follow the more complex coding rule are near zero, absent some type of automated enforcement mechanism. Well, there is an enforcement mechanism: the regression tests will now complain if any pg_proc.h entry lacks a comment. What they can't do very well is enforce that the comment is sanely chosen. In particular the likely failure mechanism is that someone submits a custom comment for a function that would be better off being labeled as implementation of XXX operator. But AFAICS such a mistake is about equally likely with either approach, maybe even a tad more so if submitters are forced to comment every function instead of having an automatic default. After further reflection I think that it should be marginally less error-prone to provide the default comment mechanism. So unless someone feels more strongly against it than they've indicated so far, I'll go ahead and do that. 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] Quick Extensions Question
Tom Lane t...@sss.pgh.pa.us writes: On the whole I'd rather spend time trying to fix the real problem, which is allowing CREATE EXTENSION to non-superusers. The general case of that looks quite hard to me, but maybe we could get something that works for the single case of an extension containing just a language. Then, what about a control file property to cover that? pl_language = plpgsql Then when running the script any object attached to the extension that is not a 'pg_catalog.pg_language'::regclass is an ERROR. And only when the pl_language property is used then the superuser-only check is bypassed. 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] Sync Rep v19
Simon Riggs si...@2ndquadrant.com writes: On Fri, 2011-03-04 at 00:02 +0900, Fujii Masao wrote: * synchronous_standby_names = * matches all standby names Using '*' as the default seems to lead the performance degradation by being connected from unexpected synchronous standby. You can configure it however you wish. It seemed better to have an out of the box setting that was useful. Well the HBA still needs some opening before anyone can claim to be a standby. I guess the default line would be commented out and no standby would be accepted as synchronous by default, assuming this GUC is sighup. 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] Quick Extensions Question
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: We've been talking about PLs, but what about the other thing David asked: could we have extension entries for compile-time options like SSL or libxml, so that you could define a dependency on them? Then those should be marked System and only get displayed with \dxS, or this will completely bloat the extension listings. Also if we get there, what about listing all the SQL Standard Features (optional only maybe) that are provided by the server? 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] Quick Extensions Question
On Mar 3, 2011, at 9:47 AM, Dimitri Fontaine wrote: Then, what about a control file property to cover that? pl_language = plpgsql Then when running the script any object attached to the extension that is not a 'pg_catalog.pg_language'::regclass is an ERROR. And only when the pl_language property is used then the superuser-only check is bypassed. More simply, I think there are two kinds of dependencies: * Other extensions * Core features Notwithstanding that PLs might be extensions, now or in the future, the necessity to require other stuff from core, like libxml support or SSL, together with your example, leads me to think that we ought to think about having two ways of specifying dependencies: requires and core_requires. The latter might look something like: core_requires = plpgsql libxml The downside of course is that then there would need to be a second infrastructure for tracking core dependencies, and it would need to be kept up-to-date. But I think something like this will be essential -- even if it only supports core PLs for now. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
On Mar 3, 2011, at 9:55 AM, Dimitri Fontaine wrote: Then those should be marked System and only get displayed with \dxS, or this will completely bloat the extension listings. Also if we get there, what about listing all the SQL Standard Features (optional only maybe) that are provided by the server? I think server version is sufficient for this. If we go with my previous proposal, I might add something like this to the explanation extension I released on PGXN last week: core_requires = 9.0, plpgsql, libxml Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
David E. Wheeler da...@kineticode.com writes: core_requires = 9.0, plpgsql, libxml As soon as you get there you need an or operator to be able to say 9.0 | 9.1, or maybe some comparison operators to say = 9.0. Remember that about all extensions we have are source-compatible with many different releases of PostgreSQL. So having the PostgreSQL server itself as an extension so that you can require it is 9.2 material at best in my opinion. 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] Quick Extensions Question
On Mar 3, 2011, at 10:12 AM, Dimitri Fontaine wrote: David E. Wheeler da...@kineticode.com writes: core_requires = 9.0, plpgsql, libxml As soon as you get there you need an or operator to be able to say 9.0 | 9.1, or maybe some comparison operators to say = 9.0. Remember that about all extensions we have are source-compatible with many different releases of PostgreSQL. We'd just start by saying a number means =. So having the PostgreSQL server itself as an extension so that you can require it is 9.2 material at best in my opinion. That sounds silly. It's clearly not an extension. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep v19
On Thu, 2011-03-03 at 18:51 +0100, Dimitri Fontaine wrote: Simon Riggs si...@2ndquadrant.com writes: On Fri, 2011-03-04 at 00:02 +0900, Fujii Masao wrote: * synchronous_standby_names = * matches all standby names Using '*' as the default seems to lead the performance degradation by being connected from unexpected synchronous standby. You can configure it however you wish. It seemed better to have an out of the box setting that was useful. Well the HBA still needs some opening before anyone can claim to be a standby. I guess the default line would be commented out and no standby would be accepted as synchronous by default, assuming this GUC is sighup. The patch sets * as the default, so all standbys are synchronous by default. Would you prefer it if it was blank, meaning no standbys are synchronous, by default? -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and 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] Quick Extensions Question
On Thu, Mar 3, 2011 at 12:56 PM, David E. Wheeler da...@kineticode.com wrote: More simply, I think there are two kinds of dependencies: * Other extensions * Core features Notwithstanding that PLs might be extensions, now or in the future, the necessity to require other stuff from core, like libxml support or SSL, together with your example, leads me to think that we ought to think about having two ways of specifying dependencies: requires and core_requires. The latter might look something like: core_requires = plpgsql libxml Not a bad thought, but you might also need to require at least a certain version of libxml. RPMs have a whole grammar for dependencies of this sort, and it is both complicated and very useful. You can say things like: Requires: package Requires: package = minversion Requires: package = maxversion Requires: package = exactversion The usefulness of the first two should be obvious, but the third and fourth are needed as well. For example, the kernel header version must exactly match the kernel version. I don't know 100% for certain that we're going to need those cases here as well, but I wouldn't bet against it. An RPM can also provide a certain capability: Provides: WonderfulGoodStuff And some other RPM can then depend on that capability. I think it's important that we don't get too confident that we've solved this problem in its full generality. I very much doubt that that's the case. -- 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] Sync Rep v19
On Thu, Mar 3, 2011 at 1:14 PM, Simon Riggs si...@2ndquadrant.com wrote: On Thu, 2011-03-03 at 18:51 +0100, Dimitri Fontaine wrote: Simon Riggs si...@2ndquadrant.com writes: On Fri, 2011-03-04 at 00:02 +0900, Fujii Masao wrote: * synchronous_standby_names = * matches all standby names Using '*' as the default seems to lead the performance degradation by being connected from unexpected synchronous standby. You can configure it however you wish. It seemed better to have an out of the box setting that was useful. Well the HBA still needs some opening before anyone can claim to be a standby. I guess the default line would be commented out and no standby would be accepted as synchronous by default, assuming this GUC is sighup. The patch sets * as the default, so all standbys are synchronous by default. Would you prefer it if it was blank, meaning no standbys are synchronous, by default? I think * is a reasonable default. -- 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] Quick Extensions Question
On Mar 3, 2011, at 10:22 AM, Robert Haas wrote: Requires: package Requires: package = minversion Requires: package = maxversion Requires: package = exactversion The usefulness of the first two should be obvious, but the third and fourth are needed as well. In the long term, perhaps. But for right now, just = would address 90% of the problem. That's all CPAN modules have, and while it's occasionally annoying, it's *very* occasionally. I think it's important that we don't get too confident that we've solved this problem in its full generality. I very much doubt that that's the case. Who said anything about full generality? I'm interested in a 90% (or even 99%) solution. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
On Thu, Mar 3, 2011 at 1:30 PM, David E. Wheeler da...@kineticode.com wrote: On Mar 3, 2011, at 10:22 AM, Robert Haas wrote: Requires: package Requires: package = minversion Requires: package = maxversion Requires: package = exactversion The usefulness of the first two should be obvious, but the third and fourth are needed as well. In the long term, perhaps. But for right now, just = would address 90% of the problem. That's all CPAN modules have, and while it's occasionally annoying, it's *very* occasionally. I think it's important that we don't get too confident that we've solved this problem in its full generality. I very much doubt that that's the case. Who said anything about full generality? I'm interested in a 90% (or even 99%) solution. It's pretty important that we don't design ourselves into a corner here. -- 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] Quick Extensions Question
On Mar 3, 2011, at 10:32 AM, Robert Haas wrote: Who said anything about full generality? I'm interested in a 90% (or even 99%) solution. It's pretty important that we don't design ourselves into a corner her Which is why my suggestion is pretty much free from any design. Just a list of dependencies, with only a server version number. No other syntax at all. It can be added later. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Time zone database
Hackers, Today on the ietf-applications list, I saw this: http://www.ietf.org/mail-archive/web/apps-discuss/current/msg02301.html If there are particular issues with respect to the time zone database stuff that you all have struggled with and want highlighted, feel free to send them to me and I'll try to figure out whether they're relevant to this Internet Draft and the new plans. I will be at the IETF meeting in Prague, though I won't plan to attend the session in question unless someone tells me I ought. If none of this is relevant to Postgres, sorry for the noise. I just saw it in passing and remember some of the annoyances that happened in the past. Also, if you want me to see what you have to say, send your mail directly to me or cc: me. I can't really keep up with the volume on this list, and I'm likely to miss it if it's only here. Best, A -- Andrew Sullivan a...@crankycanuck.ca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
David E. Wheeler da...@kineticode.com wrote: On Mar 3, 2011, at 10:32 AM, Robert Haas wrote: It's pretty important that we don't design ourselves into a corner her Which is why my suggestion is pretty much free from any design Now you're scaring me. I read that as the proposed design is free from the influence of any design effort. That's precisely how you can find yourself standing in a corner with wet paint on the floor all around you. At a minimum you would need to specify the format of the dependency list and either some header and or terminator or some specification of something which *can't* be in the list. It would be ironic if our extensions configuration wasn't extensible. -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] Quick Extensions Question
On Mar 3, 2011, at 10:49 AM, Kevin Grittner wrote: Which is why my suggestion is pretty much free from any design Now you're scaring me. I read that as the proposed design is free from the influence of any design effort. No. Just as simple as possible. That's precisely how you can find yourself standing in a corner with wet paint on the floor all around you. But if the paint were made with chocolate, would you care? At a minimum you would need to specify the format of the dependency list and either some header and or terminator or some specification of something which *can't* be in the list. It would be ironic if our extensions configuration wasn't extensible. I'm talking about a hard-coded list of things that can go on the list, core items, simply separated by commas (or however the postgresql.conf format supports a list of items). No external dependencies (like the particular version of libxml2) or any version numbers at all, aside from the server itself. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
David E. Wheeler da...@kineticode.com writes: On Mar 3, 2011, at 10:32 AM, Robert Haas wrote: Who said anything about full generality? I'm interested in a 90% (or even 99%) solution. It's pretty important that we don't design ourselves into a corner her Which is why my suggestion is pretty much free from any design. Just a list of dependencies, with only a server version number. No other syntax at all. It can be added later. I basically agree with Robert that requires = 9.1 is entirely useless. There's next to no scenario where an extension author wouldn't really be wanting to write requires = 9.1 instead. And to do that, we have to solve the whole version-number-comparison problem that we worked so hard to dodge before. So this all looks to me like something that needs considerably more thought than we can devote to it for 9.1. 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] Quick Extensions Question
On Mar 3, 2011, at 10:54 AM, Tom Lane wrote: Which is why my suggestion is pretty much free from any design. Just a list of dependencies, with only a server version number. No other syntax at all. It can be added later. I basically agree with Robert that requires = 9.1 is entirely useless. There's next to no scenario where an extension author wouldn't really be wanting to write requires = 9.1 instead. And to do that, we have to solve the whole version-number-comparison problem that we worked so hard to dodge before. So this all looks to me like something that needs considerably more thought than we can devote to it for 9.1. I'm saying that core_requires = 9.1.0, libxml, plpgsql Means = 9.1.0. That = is an assignment operator, not comparison. And this is the *only* version number I'd specify, the core version number, of which the core has perfect control of how things are compared (pg_version()). Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
David E. Wheeler da...@kineticode.com writes: On Mar 3, 2011, at 10:54 AM, Tom Lane wrote: I basically agree with Robert that requires = 9.1 is entirely useless. I'm saying that core_requires = 9.1.0, libxml, plpgsql Means = 9.1.0. That's not a design, that's just a very arbitrary kluge. And it doesn't solve anything at all that we need to solve today, because you can already assume that you're running on = 9.1 just by the fact that you're writing an extension. Having a solution for this in time for 9.2 will be plenty soon enough. BTW, I don't see any good reason to distinguish core requires from non-core. If anything, the spirit of an extension proposal should be trying to reduce the distinction between core stuff and not-core stuff, since part of the point of extensions is that features might migrate across that boundary. 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] Quick Extensions Question
On Mar 3, 2011, at 11:09 AM, Tom Lane wrote: That's not a design, that's just a very arbitrary kluge. And it doesn't solve anything at all that we need to solve today, because you can already assume that you're running on = 9.1 just by the fact that you're writing an extension. Having a solution for this in time for 9.2 will be plenty soon enough. Fair enough. BTW, I don't see any good reason to distinguish core requires from non-core. If anything, the spirit of an extension proposal should be trying to reduce the distinction between core stuff and not-core stuff, since part of the point of extensions is that features might migrate across that boundary. Okay. My only concern on that front, with regards to a future design, is how things will be reserved. I suppose that could be got 'round by preserving things starting with, say, pg- or pg: as core features. So if I released an extension called xslt, it wouldn't conflict with the core xslt extension. Or else core extensions would just have their names implicitly reserved. FWIW, extension names are required to be unique on PGXN. So no two people can have an extension named foo. I'd like to get a list of core extensions reserved in the code soon so that no one tries to uploaded plperl, for example. What might such a list look like? Just PLs plus ./configure options (pam, ldap, bonjour, etc.) plus postgresql itself, of course? Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep v19
On Fri, 2011-03-04 at 00:02 +0900, Fujii Masao wrote:
+ else if (WaitingForSyncRep)
+ {
+ /*
+* This must NOT be a FATAL message. We want
the state of the
+* transaction being aborted to be
indeterminate to ensure that
+* the transaction completion guarantee is
never broken.
+*/
The backend can reach this code path after returning the commit to the
client.
Instead, how about doing this in EndCommand, to close the connection
before
returning the commit?
I don't really understand this comment.
You can't get there after returning the COMMIT message. Once we have
finished waiting we set WaitingForSyncRep = false, before we return to
RecordTransactionCommit() and continue from there.
Anyway, this is code in the interrupt handler and only gets executed
when we receive SIGTERM for a fast shutdown.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and 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] Time zone database
On 03.03.2011 20:45, Andrew Sullivan wrote: Today on the ietf-applications list, I saw this: http://www.ietf.org/mail-archive/web/apps-discuss/current/msg02301.html If there are particular issues with respect to the time zone database stuff that you all have struggled with and want highlighted, feel free to send them to me and I'll try to figure out whether they're relevant to this Internet Draft and the new plans. I will be at the IETF meeting in Prague, though I won't plan to attend the session in question unless someone tells me I ought. If none of this is relevant to Postgres, sorry for the noise. I just saw it in passing and remember some of the annoyances that happened in the past. Yes, we use David Olson's tz database. According to this proposal, David Olson is retiring, and they propose that IETF takes over maintainership of the tz database. The idea seems to be that the work to keep the database and tz code current would continue like before, just in the hands of different people, so I don't think this affects us in any way. -- 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] Quick Extensions Question
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: On the whole I'd rather spend time trying to fix the real problem, which is allowing CREATE EXTENSION to non-superusers. The general case of that looks quite hard to me, but maybe we could get something that works for the single case of an extension containing just a language. Then, what about a control file property to cover that? pl_language = plpgsql Then when running the script any object attached to the extension that is not a 'pg_catalog.pg_language'::regclass is an ERROR. And only when the pl_language property is used then the superuser-only check is bypassed. Well and of course as soon as one language is registered, new ones are an ERROR too. -- 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] Time zone database
On Thu, Mar 03, 2011 at 09:27:58PM +0200, Heikki Linnakangas wrote: Yes, we use David Olson's tz database. According to this proposal, David Olson is retiring, and they propose that IETF takes over maintainership of the tz database. Yeah, I guess I ought to have summarized. That is indeed the plan. The idea seems to be that the work to keep the database and tz code current would continue like before, just in the hands of different people, so I don't think this affects us in any way. There is the possibility that the IETF will be somehow less quick to cope with changes. (The IETF is not a speedy way to get anything done.) I think that's the biggest reservation I've heard expressed. Anyway, as long as nobody's worried, I can stand mute :) Thanks for the reply. A -- Andrew Sullivan a...@crankycanuck.ca -- Sent 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 2011-03-03 11:53, Simon Riggs wrote: Latest version of Sync Rep, which includes substantial internal changes and simplifications from previous version. (25-30 changes). Includes all outstanding technical comments, typos and docs. I will continue to work on self review and test myself, though actively encourage others to test and report issues. Interesting changes * docs updated * names listed in synchronous_standby_names are now in priority order * synchronous_standby_names = * matches all standby names * pg_stat_replication now shows standby priority - this is an ordinal number so 1 means 1st, 2 means 2nd etc, though 0 means not a sync standby. Some initial remarks: 1) this works nice: application_name not in synchronous_standby_names - sync_priority = 0 (OK) change synchronous_standby_names to default *, reload conf - sync_priority = 1 (OK) message in log file LOG: 0: standby walreceiver is now the synchronous standby with priority 1 2) priorities I have to get used to mapping the integers to synchronous replication meaning. 0 - asynchronous 1 - the synchronous standby that is waited for 2 and higher - potential syncs Could it be hidden from the user? I liked asynchronous / synchronous / potential synchronous then the log message could be LOG: 0: standby walreceiver is now the synchronous standby 3) walreceiver is the default application name - could there be problems when a second standby with that name connects (ofcourse the same question holds for two the same nondefault application_names)? regards Yeb Havinga -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
Dimitri Fontaine dimi...@2ndquadrant.fr writes: Tom Lane t...@sss.pgh.pa.us writes: On the whole I'd rather spend time trying to fix the real problem, which is allowing CREATE EXTENSION to non-superusers. The general case of that looks quite hard to me, but maybe we could get something that works for the single case of an extension containing just a language. Then, what about a control file property to cover that? pl_language = plpgsql That doesn't fix the permissions problem, it's just a special-case wart for PLs. However, it does strike me that there is one simple case we could support without a great deal of sweat. Namely, what if we allow non-superusers to create an extension if all the commands in the script are ones they could execute anyway? In particular, an extension containing only CREATE LANGUAGE would work for exactly those users who could execute CREATE LANGUAGE under the existing dispensations. This might also make it less painful to use extensions that consist purely of SQL (no underlying C functions). This special case avoids two of the nastier problems that have been bugging me with respect to the general case: 1. We don't have to worry about somehow kluging the permissions checks for commands executed within the script, as we would have to do to let a non-superuser create an extension that includes C functions for instance. 2. We aren't opening a Pandora's box of security vulnerabilities, as would certainly happen if extension scripts that effectively have superuser privs were to be executed in an environment under the control of a malicious non-superuser. Offhand I don't see any security risks in this type of feature. We'd be letting non-superusers examine the extension directory, but we have enough safeguards in place already to be sure they can't see the rest of the filesystem via the extension commands. In the simplest form we could implement this by just removing the superuser() check in CREATE EXTENSION. But then people who tried to load a superuser-only extension would get a permissions failure on some random command within the extension, which might be thought less than user-friendly. Also it might be good to have a more explicit marking of superuser-only extensions. So I'm thinking it might be best to invent a control file property along the lines of superuser = false -- default is true which would presently do nothing except control whether to make a superuser() permissions check before running the script. (In future it might do more, but only after a lot of careful thought.) We'd also have to fix ALTER EXTENSION and DROP EXTENSION to check for extension ownership instead of superuserness, but that would be simple enough, since I already insisted on an extowner column ;-) This looks like it would be at most a few hours' work to change, and it would enable creation of extensions for the built-in languages that can be loaded with the same permissions as before. It would not do anything towards allowing non-superusers to load languages that aren't listed in pg_pltemplate, but it doesn't make things any worse for non-core languages either: they can make extensions that are superuser-loadable, which is the same permissions situation they are in now. Comments? 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] Sync Rep v19
On Thu, 2011-03-03 at 22:27 +0100, Yeb Havinga wrote: On 2011-03-03 11:53, Simon Riggs wrote: Latest version of Sync Rep, which includes substantial internal changes and simplifications from previous version. (25-30 changes). Includes all outstanding technical comments, typos and docs. I will continue to work on self review and test myself, though actively encourage others to test and report issues. Interesting changes * docs updated * names listed in synchronous_standby_names are now in priority order * synchronous_standby_names = * matches all standby names * pg_stat_replication now shows standby priority - this is an ordinal number so 1 means 1st, 2 means 2nd etc, though 0 means not a sync standby. Some initial remarks: 1) this works nice: application_name not in synchronous_standby_names - sync_priority = 0 (OK) change synchronous_standby_names to default *, reload conf - sync_priority = 1 (OK) message in log file LOG: 0: standby walreceiver is now the synchronous standby with priority 1 2) priorities I have to get used to mapping the integers to synchronous replication meaning. 0 - asynchronous 1 - the synchronous standby that is waited for 2 and higher - potential syncs Could it be hidden from the user? I liked asynchronous / synchronous / potential synchronous Yes, that sounds good. I will leave it as it is now to gain other comments since this need not delay commit. then the log message could be LOG: 0: standby walreceiver is now the synchronous standby The priority is mentioned in the LOG message, so you can understand what happens when multiple standbys connect. e.g. if you have synchronous_standby_names = 'a, b, c' and then the standbys connect in the order b, c, a then you will see log messages LOG: standby b is now the synchronous standby with priority 2 LOG: standby a is now the synchronous standby with priority 1 It's designed so no matter which order standbys arrive in it is the highest priority standby that makes it to the front in the end. 3) walreceiver is the default application name - could there be problems when a second standby with that name connects (ofcourse the same question holds for two the same nondefault application_names)? That's documented: in that case which standby is sync is indeterminate. -- Simon Riggs http://www.2ndQuadrant.com/books/ PostgreSQL Development, 24x7 Support, Training and 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] Quick Extensions Question
On Mar 3, 2011, at 1:31 PM, Tom Lane wrote: However, it does strike me that there is one simple case we could support without a great deal of sweat. Namely, what if we allow non-superusers to create an extension if all the commands in the script are ones they could execute anyway? In particular, an extension containing only CREATE LANGUAGE would work for exactly those users who could execute CREATE LANGUAGE under the existing dispensations. This might also make it less painful to use extensions that consist purely of SQL (no underlying C functions). Now see here? THAT's what I'm talking about! This looks like it would be at most a few hours' work to change, and it would enable creation of extensions for the built-in languages that can be loaded with the same permissions as before. Would that time include having extension records for the core PLs, created when you CREATE LANGUAGE and removed when you DROP LANGUAGE? It would not do anything towards allowing non-superusers to load languages that aren't listed in pg_pltemplate, but it doesn't make things any worse for non-core languages either: they can make extensions that are superuser-loadable, which is the same permissions situation they are in now. Comments? I assume that non-core PLs must be installed by a superuser? And if so, then they could be distributed as extensions with superuser = true? I think this is awesome. Love it, especially for SQL-only extensions (of which I expect there will be many in the coming years). Of course, this doesn't address how to make compile-time options pre-requisites, but I think that's a somewhat less important issue, frankly. I can modify the explanation extension install script to throw an exception of xpath isn't installed, for example. Kind of a PITA, but do-able. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
Tom Lane t...@sss.pgh.pa.us wrote: what if we allow non-superusers to create an extension if all the commands in the script are ones they could execute anyway? +1 The supporting detail all made sense to me. To put it another way, why would you want to *bar* someone from executing a set of statements they have authority to execute, just because someone gathered them together in an extension? -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
Simon Riggs si...@2ndquadrant.com writes: Anyway, this is code in the interrupt handler and only gets executed when we receive SIGTERM for a fast shutdown. I trust it's not getting *directly* executed from the interrupt handler, at least not without ImmediateInterruptOK. 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] Quick Extensions Question
David E. Wheeler da...@kineticode.com writes: On Mar 3, 2011, at 1:31 PM, Tom Lane wrote: This looks like it would be at most a few hours' work to change, and it would enable creation of extensions for the built-in languages that can be loaded with the same permissions as before. Would that time include having extension records for the core PLs, created when you CREATE LANGUAGE and removed when you DROP LANGUAGE? Extensions yes, but not managed with those commands. You'd have to switch over to saying CREATE/DROP EXTENSION plpgsql, etc. The LANGUAGE commands themselves would now only occur within those extension scripts. BTW, a fine point I didn't mention in the previous summary is that if CREATE LANGUAGE within an extension script creates language support functions pursuant to what it finds in pg_pltemplate, those functions should be marked as belonging to the extension, so that they'd get dropped during DROP EXTENSION. I'm not sure whether the path of control is such that that'd happen today, but if it doesn't we'd need to tweak things. I assume that non-core PLs must be installed by a superuser? Right. That's true already, because only a superuser can do the unvetted 'CREATE FUNCTION ... LANGUAGE c' commands that are necessary for installing a PL not known to pg_pltemplate. I would like to get rid of pg_pltemplate eventually (in favor of trusting commands coming from an extension script), but we're not there yet. And if so, then they could be distributed as extensions with superuser = true? Right. 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] file signature for files that make up postgres database
Hi I am trying to recover a postgres database from a ext3 partition that the filesystem has become corrupt and lost files. Can anybody tell me what are the file signatures for the files that comprise a postgres database? Anybody know of any tools that can detect and recover postgres files from an EXT3 partition? I see that photorec is supposed to be able to recognize mysql database files. Thanks, Lee -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
On Mar 3, 2011, at 2:16 PM, Tom Lane wrote: Extensions yes, but not managed with those commands. You'd have to switch over to saying CREATE/DROP EXTENSION plpgsql, etc. The LANGUAGE commands themselves would now only occur within those extension scripts. Ah, I see. So if someone installed a PL with CREATE LANGUAGE and my extension requires that, PL, the requirement will not appear to be fulfilled. Kind of a bummer. Maybe add a note to CREATE LANGUAGE suggesting the use of CREATE EXTENSION, instead? Or perhaps createlang could be made to create the extension instead? That would probably mostly solve the problem. BTW, a fine point I didn't mention in the previous summary is that if CREATE LANGUAGE within an extension script creates language support functions pursuant to what it finds in pg_pltemplate, those functions should be marked as belonging to the extension, so that they'd get dropped during DROP EXTENSION. I'm not sure whether the path of control is such that that'd happen today, but if it doesn't we'd need to tweak things. Makes sense. Right. That's true already, because only a superuser can do the unvetted 'CREATE FUNCTION ... LANGUAGE c' commands that are necessary for installing a PL not known to pg_pltemplate. I would like to get rid of pg_pltemplate eventually (in favor of trusting commands coming from an extension script), but we're not there yet. That sounds like a decent plan. I can see we're getting a number of To-Dos for 9.2 out of the extension work. Someone got the enumerated somewhere? And if so, then they could be distributed as extensions with superuser = true? Right. Great, thanks! David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] file signature for files that make up postgres database
Lee Duynslager lee.duynsla...@gmail.com writes: Hi I am trying to recover a postgres database from a ext3 partition that the filesystem has become corrupt and lost files. Can anybody tell me what are the file signatures for the files that comprise a postgres database? Look at PageHeaderIsValid in bufpage.c. Those tests are pretty weak if only applied once, but if you got matches on the first ten or twenty 8K pages of a file, you could probably conclude it was a PG file with high confidence. 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] Quick Extensions Question
David E. Wheeler da...@kineticode.com writes: On Mar 3, 2011, at 2:16 PM, Tom Lane wrote: Extensions yes, but not managed with those commands. You'd have to switch over to saying CREATE/DROP EXTENSION plpgsql, etc. The LANGUAGE commands themselves would now only occur within those extension scripts. Ah, I see. So if someone installed a PL with CREATE LANGUAGE and my extension requires that, PL, the requirement will not appear to be fulfilled. Kind of a bummer. Maybe add a note to CREATE LANGUAGE suggesting the use of CREATE EXTENSION, instead? Well, the recovery path at that point would involve CREATE EXTENSION plsomething FROM unpackaged. This doesn't seem to me to be any worse than the messiness around upgrading contrib modules into extensions. We would have to document it of course. But this is exactly analogous to the case where you write an extension that requires citext, and then somebody complains because his 9.0-upgraded citext installation doesn't satisfy the requires. 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] Quick Extensions Question
On Thu, Mar 3, 2011 at 4:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Comments? My only real concern about this is that someone might get confused about whether they are supposed to issue CREATE EXTENSION or CREATE LANGUAGE. -- 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] ALTER TABLE deadlock with concurrent INSERT
On Mar 2, 2011, at 2:54 PM, Joe Conway wrote:
On 03/02/2011 12:41 PM, Tom Lane wrote:
Looks like the process trying to do the ALTER has already got some
lower-level lock on the table. It evidently hasn't got
AccessExclusiveLock, but nonetheless has something strong enough to
block an INSERT, such as ShareLock.
Hmmm, is it possible that the following might do that, whereas a simple
ALTER TABLE would not?
Impossible to tell without seeing what's in the script... ie: if the script was
BEGIN;
-- Do something to that table that blocks inserts
SELECT change_column_type(...);
COMMIT;
You'd get a deadlock.
The script also has several race conditions:
- Someone could drop the table after you query pg_class
- Someone could alter/drop the column after you query pg_attribute
My suggestion would be to try to grab an exclusive lock on the table as the
first line in the function (and then don't do anything cute in the declare
section, such as use tablename::regprocedure).
Speaking of which, I would recommend using the regprocedure and regtype casts
instead of querying the catalog directly; that way you have working schema
support and you're immune from future catalog changes. Unfortunately you'll
still have to do things the hard way to find the column (unless we added
regcolumn post 8.3), but you might want to use information_schema, or at least
see what it's doing there. The query *technically* should include WHERE attnum
0 (maybe =) AND NOT attisdropped, though it's probably not a big deal that
it isn't since ALTER TABLE will save your bacon there (though, I'd include a
comment to that effect to protect anyone who decides to blindly cut and paste
that query somewhere else where it does matter...).
8---
BEGIN;
CREATE OR REPLACE FUNCTION change_column_type
(
tablename text,
columnname text,
newtype text
) RETURNS text AS $$
DECLARE
newtypeid oid;
tableoidoid;
curtypeid oid;
BEGIN
SELECT INTO newtypeid oid FROM pg_type WHERE oid =
newtype::regtype::oid;
SELECT INTO tableoid oid FROM pg_class WHERE relname = tablename;
IF NOT FOUND THEN
RETURN 'TABLE NOT FOUND';
END IF;
SELECT INTO curtypeid atttypid FROM pg_attribute WHERE
attrelid = tableoid AND attname::text = columnname;
IF NOT FOUND THEN
RETURN 'COLUMN NOT FOUND';
END IF;
IF curtypeid != newtypeid THEN
EXECUTE 'ALTER TABLE ' || tablename || ' ALTER COLUMN ' ||
columnname || ' SET DATA TYPE ' || newtype;
RETURN 'CHANGE SUCCESSFUL';
ELSE
RETURN 'CHANGE SKIPPED';
END IF;
EXCEPTION
WHEN undefined_object THEN
RETURN 'INVALID TARGET TYPE';
END;
$$ LANGUAGE plpgsql;
SELECT change_column_type('attribute_summary',
'sequence_number',
'numeric');
COMMIT;
8---
This text is in a file being run from a shell script with something like:
psql dbname script.sql
The concurrent INSERTs are being done by the main application code
(running on Tomcat).
Joe
--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, 24x7 Support
--
Jim C. Nasby, Database Architect j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Snapshot synchronization, again...
On Mar 1, 2011, at 10:54 PM, Tom Lane wrote: Jim Nasby j...@nasby.net writes: Dumb question: Is this something that could be solved by having the postmaster track this information in it's local memory and make it available via a variable-sized IPC mechanism, such as a port or socket? That would eliminate the need to clean things up after a crash; I'm not sure if there would be other benefits. Involving the postmaster in this is entirely *not* reasonable. The postmaster cannot do anything IPC-wise that the stats collector couldn't do, and every additional function we load onto the postmaster is another potential source of unrecoverable database-wide failures. The PM is reliable only because it doesn't do much. Makes sense. Doesn't have to be the postmaster; it could be some other process. Anyway, I just wanted to throw the idea out as food for thought. I don't know if it'd be better or worse than temp files... -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Quick Extensions Question
Robert Haas robertmh...@gmail.com writes: On Thu, Mar 3, 2011 at 4:31 PM, Tom Lane t...@sss.pgh.pa.us wrote: Comments? My only real concern about this is that someone might get confused about whether they are supposed to issue CREATE EXTENSION or CREATE LANGUAGE. It wouldn't really matter, up till the point when they tried to load an extension that listed the language extension as a requires. And then they could fix it with CREATE EXTENSION ... FROM unpackaged. It's no worse than the situation with contrib modules that haven't been upgraded to extensions. Also, to the extent that we can make all that you forgot to upgrade it to an extension pain happen in 9.1, I think that's better than spreading it over multiple releases. Which is what will happen if we don't extension-ify languages till later. 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] ALTER TABLE deadlock with concurrent INSERT
On 03/03/2011 03:49 PM, Jim Nasby wrote: On Mar 2, 2011, at 2:54 PM, Joe Conway wrote: On 03/02/2011 12:41 PM, Tom Lane wrote: Looks like the process trying to do the ALTER has already got some lower-level lock on the table. It evidently hasn't got AccessExclusiveLock, but nonetheless has something strong enough to block an INSERT, such as ShareLock. Hmmm, is it possible that the following might do that, whereas a simple ALTER TABLE would not? Impossible to tell without seeing what's in the script... ie: if the script was BEGIN; -- Do something to that table that blocks inserts SELECT change_column_type(...); COMMIT; You'd get a deadlock. The script was exactly the one posted, i.e. BEGIN; CREATE FUNCTION change_column_type(...); SELECT change_column_type(...); COMMIT; That's all there is to it. And the function itself has no specific reference to the table being altered. That's why I'm left scratching my head ;-) Joe -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, 24x7 Support signature.asc Description: OpenPGP digital signature
Re: [HACKERS] ALTER TABLE deadlock with concurrent INSERT
On Mar 3, 2011, at 6:26 PM, Joe Conway wrote: On 03/03/2011 03:49 PM, Jim Nasby wrote: On Mar 2, 2011, at 2:54 PM, Joe Conway wrote: On 03/02/2011 12:41 PM, Tom Lane wrote: Looks like the process trying to do the ALTER has already got some lower-level lock on the table. It evidently hasn't got AccessExclusiveLock, but nonetheless has something strong enough to block an INSERT, such as ShareLock. Hmmm, is it possible that the following might do that, whereas a simple ALTER TABLE would not? Impossible to tell without seeing what's in the script... ie: if the script was BEGIN; -- Do something to that table that blocks inserts SELECT change_column_type(...); COMMIT; You'd get a deadlock. The script was exactly the one posted, i.e. BEGIN; CREATE FUNCTION change_column_type(...); SELECT change_column_type(...); COMMIT; That's all there is to it. And the function itself has no specific reference to the table being altered. That's why I'm left scratching my head ;-) I suggest grabbing a snapshot of pg_locks for the connection that's creating the function, and then do the same for the insert and see what could potentially conflict... -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] why is max standby delay only 35 minutes?
I have a server where I wanted to do some reporting on a standby, and wanted to set the max standby delay to 1 hour. upon doing that, i get this in the logs: 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG: received SIGHUP, reloading configuration files 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG: 360 is outside the valid range for parameter max_standby_archive_delay (-1 .. 2147483) The error is clear enough, but is there some reason that the parameter is coded this way? istm people are much more likely to want to be able to set the precision in hours than in microseconds. OTOH, maybe it's a bug? The default resolution is in milliseconds, and you can't set it to anything less than that (afaict). I asked on irc and the consensus seemed to be that the internal representation is off, are we missing something? Robert Treat play: xzilla.net work: omniti.com hiring: l42.org/Lg -- Sent 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 Fri, Mar 4, 2011 at 7:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: Anyway, this is code in the interrupt handler and only gets executed when we receive SIGTERM for a fast shutdown. I trust it's not getting *directly* executed from the interrupt handler, at least not without ImmediateInterruptOK. Yes, the backend waits for replication while cancel/die interrupt is being blocked, i.e., InterruptHoldoffCount 0. So SIGTERM doesn't lead the waiting backend to there directly. The backend reaches there after returning the result. 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] Sync Rep v19
On Fri, Mar 4, 2011 at 1:27 PM, Fujii Masao masao.fu...@gmail.com wrote: On Fri, Mar 4, 2011 at 7:01 AM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: Anyway, this is code in the interrupt handler and only gets executed when we receive SIGTERM for a fast shutdown. I trust it's not getting *directly* executed from the interrupt handler, at least not without ImmediateInterruptOK. Yes, the backend waits for replication while cancel/die interrupt is being blocked, i.e., InterruptHoldoffCount 0. So SIGTERM doesn't lead the waiting backend to there directly. The backend reaches there after returning the result. BTW, this is true in COMMIT and PREPARE cases, and false in COMMIT PREPARED and ROLLBACK PREPARED cases. In the latter cases, HOLD_INTERRUPT() is not called before waiting for replication. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Sync Rep v19
On Fri, 2011-03-04 at 13:35 +0900, Fujii Masao wrote:
On Fri, Mar 4, 2011 at 1:27 PM, Fujii Masao masao.fu...@gmail.com wrote:
On Fri, Mar 4, 2011 at 7:01 AM, Tom Lane t...@sss.pgh.pa.us wrote:
Simon Riggs si...@2ndquadrant.com writes:
Anyway, this is code in the interrupt handler and only gets executed
when we receive SIGTERM for a fast shutdown.
I trust it's not getting *directly* executed from the interrupt handler,
at least not without ImmediateInterruptOK.
Yes, the backend waits for replication while cancel/die interrupt is
being blocked, i.e., InterruptHoldoffCount 0. So SIGTERM doesn't
lead the waiting backend to there directly. The backend reaches there
after returning the result.
BTW, this is true in COMMIT and PREPARE cases,
CommitTransaction() calls HOLD_INTERRUPT() and then RESUME_INTERRUPTS(),
which was reasonable before we started waiting for syncrep. The
interrupt does occur *before* we send the message back, but doesn't work
effectively at interrupting the wait in the way you would like.
If we RESUME_INTERRUPTS() prior to waiting and then HOLD again that
would allow all signals not just SIGTERM. We would need to selectively
reject everything except SIGTERM messages.
Ideas?
Alter ProcessInterrupts() to accept an interrupt if ProcDiePending
WaitingForSyncRep and InterruptHoldoffCount 0. That looks a little
scary, but looks like it will work.
and false in
COMMIT PREPARED and ROLLBACK PREPARED cases. In the
latter cases, HOLD_INTERRUPT() is not called before waiting for
replication.
--
Simon Riggs http://www.2ndQuadrant.com/books/
PostgreSQL Development, 24x7 Support, Training and Services
diff --git a/src/backend/tcop/postgres.c b/src/backend/tcop/postgres.c
index 3063e0b..5d86deb 100644
--- a/src/backend/tcop/postgres.c
+++ b/src/backend/tcop/postgres.c
@@ -2843,8 +2843,17 @@ RecoveryConflictInterrupt(ProcSignalReason reason)
void
ProcessInterrupts(void)
{
- /* OK to accept interrupt now? */
- if (InterruptHoldoffCount != 0 || CritSectionCount != 0)
+ /*
+ * OK to accept interrupt now?
+ *
+ * Normally this is very straightforward. We don't accept interrupts
+ * between HOLD_INTERRUPTS() and RESUME_INTERRUPTS().
+ *
+ * For SyncRep, we want to accept SIGTERM signals while other interrupts
+ * are held, so we have a special case solely when WaitingForSyncRep.
+ */
+ if ((InterruptHoldoffCount != 0 || CritSectionCount != 0)
+ !(WaitingForSyncRep ProcDiePending))
return;
InterruptPending = false;
if (ProcDiePending)
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why is max standby delay only 35 minutes?
On Fri, Mar 4, 2011 at 04:00, Robert Treat r...@xzilla.net wrote: I have a server where I wanted to do some reporting on a standby, and wanted to set the max standby delay to 1 hour. upon doing that, i get this in the logs: 2011-03-03 21:20:08 EST () [2656]: [2-1] user=,db=LOG: received SIGHUP, reloading configuration files 2011-03-03 21:20:08 EST () [2656]: [3-1] user=,db=LOG: 360 is outside the valid range for parameter max_standby_archive_delay (-1 .. 2147483) The error is clear enough, but is there some reason that the parameter is coded this way? istm people are much more likely to want to be able to set the precision in hours than in microseconds. OTOH, maybe it's a bug? The default resolution is in milliseconds, and you can't set it to anything less than that (afaict). I asked on irc and the consensus seemed to be that the internal representation is off, are we missing something? See this thread here: http://archives.postgresql.org/pgsql-hackers/2010-12/msg01517.php Summary: should be fixed, but it needs to be verified that it works across all possible codepaths. It's not an issue with just max_standby_delay. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] ALTER TABLE deadlock with concurrent INSERT
On Wed, Mar 02, 2011 at 12:25:16PM -0800, Joe Conway wrote:
I'm working with a client on an application upgrade script which
executes a function to conditionally do an:
ALTER TABLE foo ALTER COLUMN bar SET DATA TYPE baz
If this is run while the application is concurrently doing inserts into
foo, we are occasionally seeing deadlocks. Aside from the fact that they
are better off not altering the table amid concurrent inserts, I'm
trying to understand why this is even able to happen. I expect one to
block the other, not a deadlock.
This is 8.4.1 (I know, I know, I have advised strongly that they upgrade
to 8.4.latest).
We have not been able to repeat this forcibly. Here is what the log shows:
--
2011-02-25 14:38:07 PST [31686]: [1-1] ERROR: deadlock detected
2011-02-25 14:38:07 PST [31686]: [2-1] DETAIL: Process 31686 waits for
AccessExclusiveLock on relation 16896 of database 16386; blocked by
process 31634.
Process 31634 waits for RowExclusiveLock on relation 16902 of
database 16386; blocked by process 31686.
Process 31686: SELECT change_column_type('attribute_summary',
'sequence_number', 'numeric');
Process 31634: insert into attribute_summary (attribute_value,
sequence_number, attribute_id) values ($1, $2, $3)
2011-02-25 14:38:07 PST [31686]: [3-1] HINT: See server log for query
details.
2011-02-25 14:38:07 PST [31686]: [4-1] CONTEXT: SQL statement ALTER
TABLE attribute_summary ALTER COLUMN sequence_number SET DATA TYPE numeric
PL/pgSQL function change_column_type line 18 at EXECUTE statement
2011-02-25 14:38:07 PST [31686]: [5-1] STATEMENT: SELECT
change_column_type('attribute_summary', 'sequence_number', 'numeric');
--
Does relation 16902 (attribute_summary) have a foreign key constraint over the
sequence_number column, in either direction, with relation 16896? That would
explain it:
session 1: ALTER TABLE attribute_summary ... sleeps after relation_openrv in
transformAlterTableStmt
session 2: SELECT 1 FROM rel16896 LIMIT 0;
session 2: SELECT 1 FROM attribute_summary LIMIT 0; blocks
session 1: wakes up; continues ALTER TABLE: deadlock upon locking rel16896
Off the cuff, I think you could make sure this never deadlocks with a PL/pgSQL
recipe like this:
LOOP
BEGIN
LOCK TABLE rel16896;
LOCK TABLE attribute_summary NOWAIT;
EXIT;
EXCEPTION WHEN lock_not_available THEN
END;
END LOOP;
Granted, the cure may be worse than the disease.
nm
--
Sent 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 Fri, Mar 4, 2011 at 12:02 AM, Fujii Masao masao.fu...@gmail.com wrote:
Though I've not read whole of the patch yet, here is the current comment:
Here are another comments:
+#replication_timeout_client = 120 # 0 means wait forever
Typo: s/replication_timeout_client/sync_replication_timeout
+ else if (timeout 0
+
TimestampDifferenceExceeds(GetCurrentTransactionStopTimestamp(),
+
wait_start, timeout))
If SetCurrentTransactionStopTimestamp() is called before (i.e., COMMIT case),
the return value of GetCurrentTransactionStopTimestamp() is the same as
wait_start. So, in this case, the timeout never expires.
+ strcpy(new_status + len, waiting for sync
rep);
+ set_ps_display(new_status, false);
How about changing the message to something like waiting for %X/%X
(%X/%X indicates the LSN which the backend is waiting for)?
Please initialize MyProc-procWaitLink to NULL in InitProcess() as well as
do MyProc-lwWaitLink.
+ /*
+* We're a potential sync standby. Release waiters if we are the
+* highest priority standby. We do this even if the standby is not yet
+* caught up, in case this is a restart situation and
+* there are backends waiting for us. That allows backends to exit the
+* wait state even if new backends cannot yet enter the wait state.
+*/
I don't think that it's good idea to switch the high priority standby which has
not caught up, to the sync one, especially when there is already another
sync standby. Because that degrades replication from sync to async for
a while, even though there is sync standby which has caught up.
+ if (walsnd-pid != 0
+ walsnd-sync_standby_priority 0
+ (priority == 0 ||
+priority walsnd-sync_standby_priority))
+ {
+priority = walsnd-sync_standby_priority;
+syncWalSnd = walsnd;
+ }
According to the code, the last named standby has highest priority. But the
document says the opposite.
ISTM the waiting backends can be sent the wake-up signal by the
walsender multiple times since the walsender doesn't remove any
entry from the queue. Isn't this unsafe? waste of the cycle?
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
How should the primary behave when the sync standby goes away? Re: [HACKERS] Sync Rep v17
On Wed, Mar 2, 2011 at 11:30 PM, Fujii Masao masao.fu...@gmail.com wrote: On Wed, Mar 2, 2011 at 8:22 PM, Simon Riggs si...@2ndquadrant.com wrote: The WALSender deliberately does *not* wake waiting users if the standby disconnects. Doing so would break the whole reason for having sync rep in the first place. What we do is allow a potential standby to takeover the role of sync standby, if one is available. Or the failing standby can reconnect and then release waiters. If there is potential standby when synchronous standby has gone, I agree that it's not good idea to release the waiting backends soon. In this case, those backends should wait for next synchronous standby. On the other hand, if there is no potential standby, I think that the waiting backends should not wait for the timeout and should wake up as soon as synchronous standby has gone. Otherwise, those backends suspend for a long time (i.e., until the timeout expires), which would decrease the high-availability, I'm afraid. Keeping those backends waiting for the failed standby to reconnect is an idea. But this looks like the behavior for allow_standalone_primary = off. If allow_standalone_primary = on, it looks more natural to make the primary work alone without waiting the timeout. Also I think that the waiting backends should be released as soon as the last synchronous standby switches to asynchronous mode. Since there is no standby which is planning to reconnect, obviously they no longer need to wait. 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
