Re: [HACKERS] lock_timeout GUC patch
Tom Lane írta: > Robert Haas writes: > >> 2010/1/20 Boszormenyi Zoltan : >> >>> Attached with the proposed modification to lift the portability concerns. >>> > > >> I think that it is a very bad idea to implement this feature in a way >> that is not 100% portable. >> > > Agreed, this is not acceptable. If there were no possible way to > implement the feature portably, we *might* consider doing it like this. > But I think more likely it'd get rejected anyway. When there is a > clear path to a portable solution, it's definitely not going to fly > to submit a nonportable one. > > regards, tom lane > OK, I will implement it using setitimer(). It may not reach 8.5 though, when will this last Commitfest end? Thanks, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Largeobject Access Controls (r2460)
KaiGai Kohei wrote: > This patch renamed the hasBlobs() by getBlobs(), and changed its > purpose. It registers DO_BLOBS, DO_BLOB_COMMENTS and DO_BLOB_ACLS > for each large objects owners, if necessary. This patch adds DumpableObjectType DO_BLOB_ACLS and struct BlobsInfo. We use three BlobsInfo objects for DO_BLOBS, DO_BLOB_COMMENTS, and DO_BLOB_ACLS _for each distinct owners_ of large objects. So, even if we have many large objects in the database, we just keep at most (3 * num-of-roles) BlobsInfo in memory. For older versions of server, we assume that blobs are owned by only one user with an empty name. We have no BlobsInfo if no large objects. I'm not sure whether we need to make groups for each owner of large objects. If I remember right, the primary issue was separating routines for dump BLOB ACLS from routines for BLOB COMMENTS, right? Why did you make the change? Another concern is their confusable identifier names -- getBlobs() returns BlobsInfo for each owners. Could we rename them something like getBlobOwners() and BlobOwnerInfo? Also, DumpableObject.name is not used in BlobsInfo. We could reuse DumpableObject.name instead of the "rolname" field in BlobsInfo. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] is any reason why we cannot cast from record (row) to typed row?
Hello I looked on question on http://stackoverflow.com/questions/2104811/execute-using-statement-in-pl-pgsql-doesnt-work-with-record-type I was surprised so isn't possible cast from record to target type - is there reason for this? DECLARE r RECORD; BEGIN EXECUTE 'SELECT * FROM xx' INTO r; r::xx isn't possible r::text::xx is possible Regards Pavel Stehule -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HS/SR and smart shutdown
Fujii Masao wrote: > On Thu, Jan 21, 2010 at 10:44 AM, Josh Berkus wrote: >>> If it's "standby", it's a previously-existing behavior that a "smart" >>> shutdown doesn't work immediately during recovery. After a recovery >>> has been completed, it would work. Of course, I agree that such a >>> behavior should be documented. >> Well, as long as streaming rep is running, you can't do a smart shutdown >> ... smart shutdown seems to treat the walreciever as a client >> connection. > > Even if SR is not running, as long as the startup process is running, > we can't do a smart shutdown. It's not peculiar to SR. Right, that's the way a standby server (= one still in recovery) has always behaved. It has made sense in the past: it's not in the spirit of smart shutdown to kill the WAL replay immediately. "smart" means wait for recovery to finish, then shutdown. It's a good question if that still makes sense with Hot Standby. Perhaps we should redefine smart shutdown in standby mode to shut down as soon as all read-only connections have died. >> At the very least, this should be in the documentation. > > Agreed. Something like "smart shutdown is not allowed during recovery" > should be in the following section. > http://developer.postgresql.org/pgdocs/postgres/server-shutdown.html It's allowed, it just doesn't do what you might expect. In the master, smart shutdown shuts down as soon as all regular backends are gone. It doesn't wait for the standby connections to die. In fact they're not killed until after the shutdown checkpoint is written, so that it gets sent to the standbys too. I think we're good there. -- 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] attoptions
On Wed, Jan 20, 2010 at 19:51, Robert Haas wrote: > On Tue, Jan 19, 2010 at 10:51 AM, Alex Hunsaker wrote: >> But yes, lets keep it simple for now. > > OK. Updated patch attached. Changes: > > - Incorporate your previous review patch. > - Omit attacl and attoptions from hardcoded relation descriptor > initializers so the whole thing still builds. Seems to me a comment about the above might be nice. Something like /* Things after here are should always be default null */ in pg_attribute.h ? Other than the below it looks good to me. *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *** *** 2426,2437 ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, case AT_SetOptions: /* ALTER COLUMN SET ( options ) */ case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */ ATSimplePermissionsRelationOrIndex(rel); ! ATSimpleRecursion(wqueue, rel, cmd, recurse); pass = AT_PASS_COL_ATTRS; break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ ATSimplePermissions(rel, false); ! /* This command never recurses */ /* No command-specific prep needed */ pass = AT_PASS_COL_ATTRS; break; --- 2426,2437 case AT_SetOptions: /* ALTER COLUMN SET ( options ) */ case AT_ResetOptions: /* ALTER COLUMN RESET ( options ) */ ATSimplePermissionsRelationOrIndex(rel); ! /* This command never recurses */ pass = AT_PASS_COL_ATTRS; break; case AT_SetStorage: /* ALTER COLUMN SET STORAGE */ ATSimplePermissions(rel, false); ! ATSimpleRecursion(wqueue, rel, cmd, recurse); /* No command-specific prep needed */ pass = AT_PASS_COL_ATTRS; break; -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Red-black tree for GIN
On Mon, Jan 11, 2010 at 1:18 PM, Robert Haas wrote: > 2010/1/11 Teodor Sigaev : >> knngist uses that implementation of rb-tree. One more candidate is a ts_stat >> which now uses unbalanced binary tree. > > Ah, OK. That's great if we can reuse that code in 2 or 3 places. Some preliminary thoughts on this patch: 1. I think rb_free_recursive is missing a pfree(). 2. We already have a definition of NIL in the PG source base. I think this one needs to be named something else. RBNIL, maybe. 3. This code could really use some more comments, and maybe some of the variable names could be better chosen, too. It's far from obvious what is going on here. I studied rbtrees in college and I still remember more or less how they work, but, boy, this is hard to follow. The names of the iterator states are truly horrible, at least IMO. 4. It would be nice if you could do a better job conforming to project indentation style. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication and archiving
On Thu, Jan 21, 2010 at 10:48 AM, Josh Berkus wrote: > Presumably, however, if the slave falls sufficiently behind and there > are no archive logs, then the slave would not be able to resynch with > the master, no? In that case, we would need to make a fresh backup of the primary, and start the standby from that data. 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] HS/SR and smart shutdown
On Thu, Jan 21, 2010 at 10:44 AM, Josh Berkus wrote: > >> If it's "standby", it's a previously-existing behavior that a "smart" >> shutdown doesn't work immediately during recovery. After a recovery >> has been completed, it would work. Of course, I agree that such a >> behavior should be documented. > > Well, as long as streaming rep is running, you can't do a smart shutdown > ... smart shutdown seems to treat the walreciever as a client > connection. Even if SR is not running, as long as the startup process is running, we can't do a smart shutdown. It's not peculiar to SR. > At the very least, this should be in the documentation. Agreed. Something like "smart shutdown is not allowed during recovery" should be in the following section. http://developer.postgresql.org/pgdocs/postgres/server-shutdown.html 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] WARNING: pgstat wait timeout
On Wed, Jan 20, 2010 at 9:32 PM, Jaime Casanova wrote: > On Wed, Jan 20, 2010 at 6:20 PM, Sergey E. Koposov wrote: >> Hello hackers, >> >> I've recently hit the message "WARNING: pgstat wait timeout" with PG 8.4.2. > > i see the same yesterday when initdb a freshly compiled 8.5dev + > lock_timeout patch > i thought maybe it was related to that patch and was thinking in > recompile without the patch but hadn't time, obviously i was wrong > ah! i forgot to say that it was on win32 + mingw, to confirme that patch works fin in that os -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] plpython3 perf
On Jan 14, 2010, at 2:03 PM, Joshua D. Drake wrote: > What I would (as a non hacker) would look for is: > > (1) Generalized benchmarks between plpython(core) and plpython3u > > I know a lot of these are subjective, but it is still good to see if > there are any curves or points that bring the performance of either to > light. k, it was pretty much as expected. However, the surprise for me was that @pytypes didn't perform as terribly as I expected it to. I imagine it's impact may become more noticeable with more parameters, but, nonetheless, I was surprised. I didn't do any SRF tests, but the installations are still setup, so if anyone really wants to see that, it shouldn't take long to do. Apologies ahead of time for the lack pretty graphs. =) I used two different builds/installations of PG to test as the PL names conflict. Both were compiled with the following CFLAGS(pg_config output): -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv Both PLs were built against the same build of Python(recent svn update of release31-maint): Python 3.1.1+ (release31-maint:77585M, Jan 17 2010, 10:29:13) [GCC 4.2.1 (Apple Inc. build 5646) (dot 1)] on darwin I ran each of the test files a few times against the target installation, but I only attached one of each to this message. (Primarily, multiple runs to filter out any spurious spikes.) The source SQL and output files are attached. rawtest.sql.out is the output for raw data objects(native typing). pytypestest.sql.out is the output of the @pytypes test(native typing with conversion overhead). plpythontest.sql.out is the output for core's plpython(conversion). A few samples from the output files are included inline below. Each volatile function is called 100,000 times from a COUNT() aggregate, and the duration is measured using psql's \timing. Most of the functions simply return the first parameter given to it. The functions are ran inside a transaction because plpython3 does some cache clears(linecache) and GC at the end of transactions. The parameter type, if any, is indicated by the label: noparams: raw: 125ms pytypes: 372ms (base overhead, it would appear) plpython: 309ms oneint2: raw: 140ms pytypes: 684ms plpython: 750ms oneint8: raw: 145ms pytypes: 676ms plpython: 718ms text_large: raw: 271ms pytypes: 2766ms plpython: 2310ms composite: raw: 235ms pytypes: 795ms (N/A, no conversion done, but takes a bit of a hit anyways) plpython: 1654ms plpythontest.sql.out Description: Binary data pytypestest.sql.out Description: Binary data rawtest.sql.out Description: Binary data pytypestest.sql Description: Binary data rawtest.sql Description: Binary data plpythontest.sql Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WARNING: pgstat wait timeout
On Wed, Jan 20, 2010 at 6:20 PM, Sergey E. Koposov wrote: > Hello hackers, > > I've recently hit the message "WARNING: pgstat wait timeout" with PG 8.4.2. i see the same yesterday when initdb a freshly compiled 8.5dev + lock_timeout patch i thought maybe it was related to that patch and was thinking in recompile without the patch but hadn't time, obviously i was wrong -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Tue, 2010-01-19 at 19:24 -0500, Tom Lane wrote: > (I'm still > wondering if we couldn't do without the lock altogether though.) Here's the problem as I see it: If we insert the notifications into the queue before actually recording the commit, there's a window in between where another backend could perform the expected sequence as you wrote: 1. LISTEN foo; (and commit the listen) 2. examine current database state 3. assume that we'll get a NOTIFY for any change that commits subsequently to what we saw in step 2 and miss the NOTIFYs, and not see the updated database state. But I don't think that the NOTIFYs will actually be missed. Once put into the queue, the notification will only be removed from the queue after all backends have read it. But no backend will advance past it as long as the notification is from an uncommitted transaction. By the time the notifying transaction is committed, the listening transaction will also be committed, and therefore subscribed to the queue. The newly-listening backend will be awakened properly as well, because that's done after the notifying transaction commits, and therefore will wake up any listening transactions that committed earlier. However, there's still a problem inserting into the queue when no backends are listening. Perhaps that can be solved right before we wake up the listening backends after the notifying transaction commits: if there are no listening backends, clear the queue. We still might get spurious notifications if they were committed before the LISTEN transaction was committed. And we also might get spurios notifications if the UNLISTEN doesn't take effect quite quickly enough. Those are both acceptable. If the above scheme is too complex, we can always use a heavyweight lock. However, there's no pg_listener so it's not obvious what LOCKTAG to use. We can just pick something arbitrary, like the Oid of the new pg_listening() function, I suppose. Is there any precedent for that? Thoughts? Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Fix auto-prepare #2
Boszormenyi Zoltan wrote: > I only wanted to call ECPGprepare() in case it wasn't already prepared. > ECPGprepare() also checks for the statement being already prepared > with ecpg_find_prepared_statement() but in case it exists it > DEALLOCATEs the statement and PREPAREs again so there's > would be no saving for auto-prepare calling it unconditionally and > we are doing a little extra work by calling ecpg_find_prepared_statement() > twice. We need a common function shared by ECPGprepare() and > ecpg_auto_prepare() to not do extra work in the auto-prepare case. > > The attached patch implements this and also your leak fixes > plus includes your change for the autoprep.pgc regression test. Good. I think the patch is ready to commit. A comment for committer (Michael?) : I was cofused by the AddStmtToCache's 2nd argument "char *stmtID" because it doesn't have a const. Should it be "const char *" ? If the argument has a const, callers assume that they can pass a not-strdup'ed string as the argument. Regards, --- Takahiro Itagaki NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HS/SR and smart shutdown
Tom Lane wrote: Robert Haas writes: On Wed, Jan 20, 2010 at 8:44 PM, Josh Berkus wrote: Well, as long as streaming rep is running, you can't do a smart shutdown ... smart shutdown seems to treat the walreciever as a client connection. At the very least, this should be in the documentation. How hard is it to fix? I think the first question is do we *want* to fix it, or is it appropriate behavior? If the master shuts down, will the slaves try to fail over to become masters? When the master restarts, will the slaves automatically reconnect? If these questions have the wrong answers, shutting down the master isn't something to be done lightly, and automatically disconnecting slaves would be a real bad idea. Right - surely people who have been using pg_standby etc have discovered this behaviour, so documenting it is fine I would think. regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HS/SR and smart shutdown
On Wed, Jan 20, 2010 at 8:56 PM, Tom Lane wrote: > Robert Haas writes: >> On Wed, Jan 20, 2010 at 8:44 PM, Josh Berkus wrote: >>> Well, as long as streaming rep is running, you can't do a smart shutdown >>> ... smart shutdown seems to treat the walreciever as a client >>> connection. At the very least, this should be in the documentation. > >> How hard is it to fix? > > I think the first question is do we *want* to fix it, or is it > appropriate behavior? > > If the master shuts down, will the slaves try to fail over to become > masters? When the master restarts, will the slaves automatically > reconnect? If these questions have the wrong answers, shutting down the > master isn't something to be done lightly, and automatically > disconnecting slaves would be a real bad idea. I thought the scenario in question was that someone wanted to manually shut down the slave. Am I misunderstanding? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HS/SR and smart shutdown
Robert Haas writes: > On Wed, Jan 20, 2010 at 8:44 PM, Josh Berkus wrote: >> Well, as long as streaming rep is running, you can't do a smart shutdown >> ... smart shutdown seems to treat the walreciever as a client >> connection. At the very least, this should be in the documentation. > How hard is it to fix? I think the first question is do we *want* to fix it, or is it appropriate behavior? If the master shuts down, will the slaves try to fail over to become masters? When the master restarts, will the slaves automatically reconnect? If these questions have the wrong answers, shutting down the master isn't something to be done lightly, and automatically disconnecting slaves would be a real bad idea. 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] HS/SR and smart shutdown
On Wed, Jan 20, 2010 at 8:44 PM, Josh Berkus wrote: >> If it's "standby", it's a previously-existing behavior that a "smart" >> shutdown doesn't work immediately during recovery. After a recovery >> has been completed, it would work. Of course, I agree that such a >> behavior should be documented. > > Well, as long as streaming rep is running, you can't do a smart shutdown > ... smart shutdown seems to treat the walreciever as a client > connection. At the very least, this should be in the documentation. How hard is it to fix? ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication and archiving
> Huh? *Archived* segments aren't supposed to get deleted, at least not > by any automatic Postgres action. It would be up to the DBA how long > he wants to keep them around. OK. The docs indicated that the segments needed to be kept around in case the slave fell behind. If that's not the case (as it appears not to be) then they can just be deleted by cron job, or the archive_command on the master can be changed. Presumably, however, if the slave falls sufficiently behind and there are no archive logs, then the slave would not be able to resynch with the master, no? --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HS/SR and smart shutdown
> If it's "standby", it's a previously-existing behavior that a "smart" > shutdown doesn't work immediately during recovery. After a recovery > has been completed, it would work. Of course, I agree that such a > behavior should be documented. Well, as long as streaming rep is running, you can't do a smart shutdown ... smart shutdown seems to treat the walreciever as a client connection. At the very least, this should be in the documentation. --Josh Berkus -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] HS/SR and smart shutdown
On Thu, Jan 21, 2010 at 8:04 AM, Josh Berkus wrote: > I've been working on my demo, and I'm discovering that due to the > connection from the walsender and walreceiver, "smart" shutdown from > pg_ctl doesn't work if replication is active. > > This seems worth fixing; if we don't fix it, we should at least document it. > > Comments? Thanks for the report. Which servers (primary or standby) did you try a "smart" shutdown on? If it's "primary", could you show me the reproducible test set? At least in my box, a "smart" shutdown on the primary works fine. If it's "standby", it's a previously-existing behavior that a "smart" shutdown doesn't work immediately during recovery. After a recovery has been completed, it would work. Of course, I agree that such a behavior should be documented. 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] Streaming Replication and archiving
Mark Kirkwood wrote: The likely typical use case for streaming replication makes a good case and automated safe way of pruning these guys Sorry, stupid typo: should read '...makes a good case for an automated safe way of pruning these' -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication and archiving
Tom Lane wrote: Mark Kirkwood writes: Josh Berkus wrote: Sure, but if the archived WAL segments are NOT needed, how are they supposed to get deleted? It doesn't take long to run out of disk space if they're not being rotated. From what I am seeing at the moment (8.5 devel from 2 days ago), the archived segments are not deleted at all (I have several hundred now after a number of pgbench runs over the last day or so). Huh? *Archived* segments aren't supposed to get deleted, at least not by any automatic Postgres action. It would be up to the DBA how long he wants to keep them around. Exactly - there was a comment in the 'retry from archive' thread that suggested otherwise. The likely typical use case for streaming replication makes a good case and automated safe way of pruning these guys - I've seen a few cases where overly aggressive cleanup has broken log shipping setups (usually 8.2, before the restart option was available). regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication, retrying from archive
On Wed, 2010-01-20 at 21:26 +0200, Heikki Linnakangas wrote: > So there's just two states: > > 1. Recovering from archive > 2. Streaming > > We start from 1, and switch state at error. > > This gives nice behavior from a user point of view. Standby tries to > make progress using either the archive or streaming, whichever becomes > available first. Sounds good. Easier to drive if we have two gears. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] WARNING: pgstat wait timeout
Hello hackers, I've recently hit the message "WARNING: pgstat wait timeout" with PG 8.4.2. I saw some reports about that message in the -bugs mailing list http://archives.postgresql.org/pgsql-bugs/2009-12/msg00175.php http://archives.postgresql.org/pgsql-bugs/2009-07/msg00081.php where the backtrace from the statisctic collector was requested. Although I don't have any other bad sympthoms in the system, I still obtained a backtrace from the statistics collector process. Since I'm not 100% sure that the message is really a bug, feel free to ignore. But if needed I have PG still running, so I can check something else if needed. Here is the (rather innocent IMHO) backtrace of the statistic collector process: (gdb) bt #0 0x7f31ddfc4b1f in poll () from /lib/libc.so.6 #1 0x005bf7da in PgstatCollectorMain (argc=, # argv=) at pgstat.c:2718 #2 0x005c0131 in pgstat_start () at pgstat.c:631 #3 0x005c474d in reaper (postgres_signal_arg=out>) #at postmaster.c:2322 #4 #5 0x7f31ddfc6c83 in select () from /lib/libc.so.6 #6 0x005c20fc in ServerLoop () at postmaster.c:1347 #7 0x005c34a7 in PostmasterMain (argc=3, argv=0x144fd20) at #postmaster.c:1040 #8 0x0056cdc8 in main (argc=3, argv=0x144fd20) at main.c:188 (gdb) quit The program is running. Quit anyway (and detach it)? (y or n) y Detaching from program: /opt/pgsql/bin/postgres, process 24677 - Bt full: (gdb) bt full #0 0x7f31ddfc4b1f in poll () from /lib/libc.so.6 No symbol table info available. #1 0x005bf7da in PgstatCollectorMain (argc=, argv=) at pgstat.c:2718 len = 64 msg = {msg_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, msg_dummy = {m_hdr = { m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}}, msg_inquiry = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, inquiry_time = 0}, msg_tabstat = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, m_databaseid = 0, m_nentries = 0, m_xact_commit = 0, m_xact_rollback = 0, m_entry = {{t_id = 0, t_counts = { t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 138, t_tuples_inserted = 138, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 2672, t_counts = {t_numscans = 1, t_tuples_returned = 1, t_tuples_fetched = 1, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 1259, t_counts = {t_numscans = 4, t_tuples_returned = 553, t_tuples_fetched = 0, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 24, t_blocks_hit = 24}}, {t_id = 2615, t_counts = {t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 0, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 1, t_blocks_hit = 1}}, {t_id = 2685, t_counts = {t_numscans = 1, t_tuples_returned = 1, t_tuples_fetched = 1, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 1172815, t_counts = {t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 0, t_tuples_inserted = 50, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 50, t_new_dead_tuples = 0, t_blocks_fetched = 23077, t_blocks_hit = 15381}}, {t_id = 1172684, t_counts = {t_numscans = 0, t_tuples_returned = 0, msg = {msg_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, msg_dummy = {m_hdr = { m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}}, msg_inquiry = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, inquiry_time = 0}, msg_tabstat = {m_hdr = {m_type = PGSTAT_MTYPE_BGWRITER, m_size = 64}, m_databaseid = 0, m_nentries = 0, m_xact_commit = 0, m_xact_rollback = 0, m_entry = {{t_id = 0, t_counts = { t_numscans = 0, t_tuples_returned = 0, t_tuples_fetched = 138, t_tuples_inserted = 138, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 2672, t_counts = {t_numscans = 1, t_tuples_returned = 1, t_tuples_fetched = 1, t_tuples_inserted = 0, t_tuples_updated = 0, t_tuples_deleted = 0, t_tuples_hot_updated = 0, t_new_live_tuples = 0, t_new_dead_tuples = 0, t_blocks_fetched = 2, t_blocks_hit = 2}}, {t_id = 1259, t_count
Re: [HACKERS] Streaming Replication and archiving
Mark Kirkwood writes: > Josh Berkus wrote: >> Sure, but if the archived WAL segments are NOT needed, how are they >> supposed to get deleted? It doesn't take long to run out of disk space >> if they're not being rotated. > From what I am seeing at the moment (8.5 devel from 2 days ago), the > archived segments are not deleted at all (I have several hundred now > after a number of pgbench runs over the last day or so). Huh? *Archived* segments aren't supposed to get deleted, at least not by any automatic Postgres action. It would be up to the DBA how long he wants to keep them around. 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] HS/SR and smart shutdown
I've been working on my demo, and I'm discovering that due to the connection from the walsender and walreceiver, "smart" shutdown from pg_ctl doesn't work if replication is active. This seems worth fixing; if we don't fix it, we should at least document it. Comments? --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Jeff Davis writes: > On Wed, 2010-01-20 at 15:54 -0500, Tom Lane wrote: >> Yes. That is the case with the existing implementation as well, no? >> We don't consider sending notifies until transaction end, so anything >> that commits during the xact in which you UNLISTEN will get dropped. > Only if the transaction containing UNLISTEN commits. Are you saying it > would also be OK to drop NOTIFYs if a backend's UNLISTEN transaction > aborts? No, I would say not, but that wasn't being proposed was it? The decisions about what to do are only made at/after commit. > Thinking out loud: If we're taking this approach, I wonder if it might > be a good idea to PreventTransactionChain for LISTEN and UNLISTEN? That shouldn't be necessary IMO. There's never been such a restriction before. 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] Listen / Notify - what to do when the queue is full
On Wed, Jan 20, 2010 at 11:08 PM, Jeff Davis wrote: >> Yes. That is the case with the existing implementation as well, no? >> We don't consider sending notifies until transaction end, so anything >> that commits during the xact in which you UNLISTEN will get dropped. > > Only if the transaction containing UNLISTEN commits. Are you saying it > would also be OK to drop NOTIFYs if a backend's UNLISTEN transaction > aborts? If the backend's UNLISTEN transaction aborts, then it has never executed UNLISTEN... So it will continue to get notifications (if it has executed a LISTEN before). Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Serializable implementation milestone: table SIREAD locks without correct lifespan
Attached is a patch for the next milestone on the Serializable wiki page: changing the table-level predicate locks to SIREAD locks without worrying about lifespan. (Implementing correct lifespan is next.) The result of not worrying about it is that they aren't cleaned up at all, even when the transaction ends and the connection is closed. In a way, that's not all bad, because neither of those events *should* remove these locks; so there's nothing to *undo* for the next step. As before, this is "for the record" and not a request for commit or official review. If anyone looks at it out of interest in this effort, any feedback is welcome. Applies cleanly to head and passes regression tests. ;-) -Kevin *** a/src/backend/catalog/index.c --- b/src/backend/catalog/index.c *** *** 2132,2138 IndexCheckExclusion(Relation heapRelation, * * After completing validate_index(), we wait until all transactions that * were alive at the time of the reference snapshot are gone; this is ! * necessary to be sure there are none left with a serializable snapshot * older than the reference (and hence possibly able to see tuples we did * not index).Then we mark the index "indisvalid" and commit. Subsequent * transactions will be able to use it for queries. --- 2132,2138 * * After completing validate_index(), we wait until all transactions that * were alive at the time of the reference snapshot are gone; this is ! * necessary to be sure there are none left with a transaction-based snapshot * older than the reference (and hence possibly able to see tuples we did * not index).Then we mark the index "indisvalid" and commit. Subsequent * transactions will be able to use it for queries. *** a/src/backend/commands/trigger.c --- b/src/backend/commands/trigger.c *** *** 2360,2366 ltrmark:; case HeapTupleUpdated: ReleaseBuffer(buffer); ! if (IsXactIsoLevelSerializable) ereport(ERROR, (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), errmsg("could not serialize access due to concurrent update"))); --- 2360,2366 case HeapTupleUpdated: ReleaseBuffer(buffer); ! if (IsXactIsoLevelXactSnapshotBased) ereport(ERROR, (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), errmsg("could not serialize access due to concurrent update"))); *** a/src/backend/executor/execMain.c --- b/src/backend/executor/execMain.c *** *** 1538,1544 EvalPlanQualFetch(EState *estate, Relation relation, int lockmode, case HeapTupleUpdated: ReleaseBuffer(buffer); ! if (IsXactIsoLevelSerializable) ereport(ERROR, (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), errmsg("could not serialize access due to concurrent update"))); --- 1538,1544 case HeapTupleUpdated: ReleaseBuffer(buffer); ! if (IsXactIsoLevelXactSnapshotBased) ereport(ERROR, (errcode(ERRCODE_T_R_SERIALIZATION_FAILURE), errmsg("could not serialize access due to concurrent update"))); *** a/src/backend/executor/nodeBitmapHeapscan.c --- b/src/backend/executor/nodeBitmapHeapscan.c *** *** 42,47 --- 42,48 #include "executor/nodeBitmapHeapscan.h" #include "pgstat.h" #include "storage/bufmgr.h" + #include "storage/predicate.h" #include "utils/memutils.h" #include "utils/snapmgr.h" #include "utils/tqual.h" *** *** 114,119 BitmapHeapNext(BitmapHeapScanState *node) --- 115,123 #endif /* USE_PREFETCH */ } + /* TODO SSI: Lock at tuple level subject to granularity promotion. */ + PredicateLockRelation(node->ss.ss_currentRelation); + for (;;) { Pagedp; *** a/src/backend/executor/nodeIndexscan.c --- b/src/backend/executor/nodeIndexscan.c *** *** 30,35 --- 30,36 #include "executor/execdebug.h" #include "executor/nodeIndexscan.h" #include "optimizer/clauses.h" + #include "storage/predicate.h" #include "utils/arr
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Wed, 2010-01-20 at 15:54 -0500, Tom Lane wrote: > Joachim Wieland writes: > > Okay, what about unprocessed notifications in the queue and a backend > > executing UNLISTEN: can we assume that it is not interested in > > notifications anymore once it executes UNLISTEN and discard all of > > them even though there might be notifications that have been sent (and > > committed) before the UNLISTEN committed? > > Yes. That is the case with the existing implementation as well, no? > We don't consider sending notifies until transaction end, so anything > that commits during the xact in which you UNLISTEN will get dropped. Only if the transaction containing UNLISTEN commits. Are you saying it would also be OK to drop NOTIFYs if a backend's UNLISTEN transaction aborts? > Again, a little bit of sloppiness here doesn't seem important. Issuing > UNLISTEN implies the client is not interested anymore. Thinking out loud: If we're taking this approach, I wonder if it might be a good idea to PreventTransactionChain for LISTEN and UNLISTEN? It might simplify things for users because they wouldn't be expecting transaction-like behavior, except for the NOTIFYs themselves. Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Custom GUCs still a bit broken
It seems like Custom GUCs are still in need of some work, as shown in my recent email. In particular, they are not transaction safe - if a transaction attempts to do DefineCustomFooVariable() and that transaction aborts, the placeholder setting that it used is already gone by the time it tries to roll back GUC settings. I think this code at the end of define_custom_variable() /* * Free up as much as we conveniently can of the placeholder structure * (this neglects any stack items...) */ set_string_field(pHolder, pHolder->variable, NULL); set_string_field(pHolder, &pHolder->reset_val, NULL); free(pHolder); needs to be removed and instead we need to save pHolder in a list along with the GUC level, to be processed later by AtEOXact_GUC(), which would do the right thing according to whether or not it had a commit or an abort. I want to get this fixed before we consider custom settings for plperl that have possible security implications. Thoughts? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MonetDB test says that PostgreSQL often has errors or missing results
Mark Wong wrote: What the TPC provides isn't really a usable kit. It could be entertaining to see how their kit works. The one for TPC-H seems to work for a lot of people; the best of the intros I found for how to make it go was http://bhairav.serc.iisc.ernet.in/doc/Installation/tpch.htm , there are others. I'd guess MonetDB followed a procedure just like that, discovered some queries didn't work right, and just called it a day and published rather than investigate. -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [NOVICE] Python verison for build in config.pl (Win32)
On Jan 20, 2010, at 12:27 PM, Magnus Hagander wrote: > Well, it needs the version to match it to the DLL name. For python > 2.6, it needs python26.dll. But yes, there should probably be some way > to ask python itself about that - that would be the non-naive method. > But as long as python is installed per default, we got it for free, > which is why it has "worked so far". [on tom's question] IIRC, the reason you can't query Python in the same way that configure/python.m4 does is because the generated Makefile that supports distutils.sysconfig does not exist in standard win32 builds. That is, AFAIK, there is no way to request the exact path of the dll/lib file in win32. However, I'm not particularly familiar with Python on win32, so that may not be the case. Given the absence of a more precise method, I'd recommend considering something along the lines of: Allow the user specify (config.pl?) the Python executable to build against and default to the python.exe in %PATH%. (this may already be the case, idk) Query Python for the version information and installation prefix. python -c 'import sys; print(str(sys.version_info[0]) + str(sys.version_info[1]))' python -c 'import sys; print(sys.prefix)' Assume that the prefix has a normal layout, and construct the lib path from the extracted version and prefix. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication and archiving
Josh Berkus wrote: Thanks Dimitri, I'd missed that thread. Ok, slave will need a suitable restore_comand in addition to primary_conninfo in recovery.conf, and then extended communication failures (or shutting down the slave for a while!) will not break the streaming setup (FWIW I tried this just now). Sure, but if the archived WAL segments are NOT needed, how are they supposed to get deleted? It doesn't take long to run out of disk space if they're not being rotated. +1 From what I am seeing at the moment (8.5 devel from 2 days ago), the archived segments are not deleted at all (I have several hundred now after a number of pgbench runs over the last day or so). regards Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per-user pg_service.conf
committed On fre, 2010-01-15 at 13:37 +0100, Christoph Berg wrote: > There's not much I have to add, maybe the documentation could add a > pointer to what keywords are recognized: > > | The file uses an "INI file" format where the section name is the > | service name and the parameters are connection parameters. > > ... (see Section 30.1 for a list). I added that. > > Independently for what this patch changes, error reporting could be > more detailed, currently "syntax error in service file \"%s\", line > %d" is reported for "no = in line" and "keyword X is unknown". The > latter case deserves a different message, maybe like "keyword \"%s\" > is invalid in service file \"%s\", line %d". That was a bit outside of the mandate of the patch, but if someone wants to send in something for that, I'm sure it would be considered. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
Joachim Wieland writes: > Okay, what about unprocessed notifications in the queue and a backend > executing UNLISTEN: can we assume that it is not interested in > notifications anymore once it executes UNLISTEN and discard all of > them even though there might be notifications that have been sent (and > committed) before the UNLISTEN committed? Yes. That is the case with the existing implementation as well, no? We don't consider sending notifies until transaction end, so anything that commits during the xact in which you UNLISTEN will get dropped. Again, a little bit of sloppiness here doesn't seem important. Issuing UNLISTEN implies the client is not interested anymore. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming Replication and archiving
> Thanks Dimitri, I'd missed that thread. Ok, slave will need a suitable > restore_comand in addition to primary_conninfo in recovery.conf, and > then extended communication failures (or shutting down the slave for a > while!) will not break the streaming setup (FWIW I tried this just now). Sure, but if the archived WAL segments are NOT needed, how are they supposed to get deleted? It doesn't take long to run out of disk space if they're not being rotated. Masao-san, can you comment on this? --Josh -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Listen / Notify - what to do when the queue is full
On Wed, Jan 20, 2010 at 5:14 PM, Tom Lane wrote: > In that case I think you've way overcomplicated matters. Just deliver > the notification. We don't really care if the listener gets additional > notifications; the only really bad case would be if it failed to get an > event that was generated after it committed a LISTEN. Okay, what about unprocessed notifications in the queue and a backend executing UNLISTEN: can we assume that it is not interested in notifications anymore once it executes UNLISTEN and discard all of them even though there might be notifications that have been sent (and committed) before the UNLISTEN committed? Joachim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
David Fetter writes: > On Wed, Jan 20, 2010 at 09:22:49PM +0200, Heikki Linnakangas wrote: >>> My point is that we should replace such polling loops with something >>> non-polling, using wait/signal or semaphores or something. > Is this a TODO yet? It hardly seems concrete enough for a TODO item. 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: Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
On Wed, Jan 20, 2010 at 09:22:49PM +0200, Heikki Linnakangas wrote: > Tom Lane wrote: > > Heikki Linnakangas writes: > >> My point is that we should replace such polling loops with something > >> non-polling, using wait/signal or semaphores or something. That gets > >> quite a bit more complex. You'd probably still have the loop, but > >> instead of pg_usleep() you'd call some new primitive function that waits > >> until the shared variable changes. > > > > Maybe someday --- it's certainly not something we need to mess with for > > 8.5. As Simon comments, getting it to work nicely in the face of corner > > cases (like processes dying unexpectedly) could be a lot of work. > > Agreed, polling is good enough for 8.5. Is this a TODO yet? Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [NOVICE] Python verison for build in config.pl (Win32)
On Wed, Jan 20, 2010 at 20:24, Tom Lane wrote: > Magnus Hagander writes: >> Or we'd welcome a patch for a smarter way to detect the version ;) > > This particular code doesn't look like it really needs to know the > *version*. What it wants is the full pathname of the python.lib file > that goes with the python executable. Isn't there a way to ask Python > itself for that? Well, it needs the version to match it to the DLL name. For python 2.6, it needs python26.dll. But yes, there should probably be some way to ask python itself about that - that would be the non-naive method. But as long as python is installed per default, we got it for free, which is why it has "worked so far". -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Streaming replication, retrying from archive
Dimitri Fontaine wrote: > Heikki Linnakangas writes: >> 1. Initial archive recovery. Standby fetches WAL files from archive >> using restore_command. When a file is not found in archive, we start >> walreceiver and switch to state 2 >> >> 2. Retrying to restore from archive. When the connection to primary is >> established and replication is started, we switch to state 3 > > When do the master know about this new slave being there? I'd say not > until 3 is ok, and then, the actual details between 1 and 2 look > strange, partly because it's more about processes than states. Right. The master doesn't need to know about the slave. > I'd propose to have 1 and 2 started in parallel from the beginning, and > as Simon proposes, being able to get back to 1. at any time: > > 0. start from a base backup, determine the first WAL / LSN we need to >start streaming, call it SR_LSN. That means asking the master its >current xlog location. What if the master can't be contacted? > The LSN we're at now, after replaying the base >backup and maybe the initial recovery from local WAL files, let's >call it BASE_LSN. > > 1. Get the missing WAL to get from BASE_LSN to SR_LSN from the archive, >with restore_command, apply them as we receive them, and start >2. possibly in parallel > > 2. Streaming replication: we connect to the primary and walreceiver gets >the WALs from the connection. It either stores them if current >standby's position < SR_LSN or apply them directly if we were already >streaming. > >Local storage would be either standby's archiving or a specific >temporary location. I guess it's more or less what you want to do >with retrying from the master's archives, but I'm not sure your line >of though makes it simpler. Seems complicated... > > The details about when a slave is in sync will get more important as > soon as we have synchronous streaming. Yeah, a lot of that logic and states is completely unnecessary until we have a synchronous mode. Even then, it seems complex. Here's what I've been hacking: First of all, walreceiver no longer tries to retry the connection on error, and postmaster no longer tries to relaunch it if it dies. So when Walreceiver is launched, it tries to connect once, and if successful, streams until an error occurs or it's killed. When startup process needs more WAL to continue replay, the logic is in pseudocode: while () { if() { wait for WAL to arrive, or for walreceiver to die. } else { Run restore_command If (restore_command succeeded) break; else { Sleep 5 seconds Start walreceiver } } } So there's just two states: 1. Recovering from archive 2. Streaming We start from 1, and switch state at error. This gives nice behavior from a user point of view. Standby tries to make progress using either the archive or streaming, whichever becomes available first. Attached is a WIP patch implementing that, also available in the 'replication-xlogrefactor' branch in my git repository. It includes the Read/FetchRecord refactoring I mentioned earlier; that's a pre-requisite for this. The code implementing the above retry logic in XLogReadPage(), in xlog.c. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 690dbb6..6cb6bf0 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -144,16 +144,6 @@ HotStandbyState standbyState = STANDBY_DISABLED; static XLogRecPtr LastRec; /* - * Are we doing recovery from XLOG stream? If so, we recover without using - * offline XLOG archives even though InArchiveRecovery==true. This flag is - * used only in standby mode. - */ -static bool InStreamingRecovery = false; - -/* The current log page is partially-filled, and so needs to be read again? */ -static bool needReread = false; - -/* * Local copy of SharedRecoveryInProgress variable. True actually means "not * known, need to check the shared state". */ @@ -457,12 +447,16 @@ static uint32 openLogOff = 0; * These variables are used similarly to the ones above, but for reading * the XLOG. Note, however, that readOff generally represents the offset * of the page just read, not the seek position of the FD itself, which - * will be just past that page. + * will be just past that page. readLen indicates how much of the current + * page has been read into readBuf. */ static int readFile = -1; static uint32 readId = 0; static uint32 readSeg = 0; static uint32 readOff = 0; +static uint32 readLen = 0; +/* Is the currently open segment being streamed from primary? */ +static bool readStreamed = false; /* Buffer for currently read page (XLOG_BLCKSZ bytes) */ static char *readBuf = NULL; @@ -474,7 +468,6 @@ static uint32 readRecordBufSize = 0; /* State information for XLOG reading */ static XLogRecPtr ReadRecPtr; /* start of las
Re: [HACKERS] [NOVICE] Python verison for build in config.pl (Win32)
Magnus Hagander writes: > Or we'd welcome a patch for a smarter way to detect the version ;) This particular code doesn't look like it really needs to know the *version*. What it wants is the full pathname of the python.lib file that goes with the python executable. Isn't there a way to ask Python itself for 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: Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
Tom Lane wrote: > Heikki Linnakangas writes: >> My point is that we should replace such polling loops with something >> non-polling, using wait/signal or semaphores or something. That gets >> quite a bit more complex. You'd probably still have the loop, but >> instead of pg_usleep() you'd call some new primitive function that waits >> until the shared variable changes. > > Maybe someday --- it's certainly not something we need to mess with for > 8.5. As Simon comments, getting it to work nicely in the face of corner > cases (like processes dying unexpectedly) could be a lot of work. Agreed, polling is good enough for 8.5. -- 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] [NOVICE] Python verison for build in config.pl (Win32)
On Wed, Jan 20, 2010 at 18:59, Tom Lane wrote: > Matt writes: >> Attempting to build 8.5 alpha on Windows XP (MSVC 2005) with Python support. >> Path to local interpreter added to config.pl (C:\Python), but message is >> presented: > >> "Could not determine python version from path at build.pl line 38" > >> Do the build scripts attempt to determine the Python version from the path >> name? Since my machine has a generic path name, is there a way to specify >> the interpreter version? > > Hm, I see this in Mkvcbuild.pm: > > $solution->{options}->{python} =~ /\\Python(\d{2})/i > || croak "Could not determine python version from path"; > $plpython->AddLibrary($solution->{options}->{python} . > "\\Libs\\python$1.lib"); > > Apparently you need to hack that to deduce the appropriate library > pathname. What exactly is your python path name, and is it a standard > installation pattern at all? From the OP, it's c:\python. And yes, the python version detection is very naive, in that it expects the default installation paths which are c:\python25 for example. So you'll need to move your python installation to the default location. Or we'd welcome a patch for a smarter way to detect the version ;) -- 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: Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
Heikki Linnakangas writes: > My point is that we should replace such polling loops with something > non-polling, using wait/signal or semaphores or something. That gets > quite a bit more complex. You'd probably still have the loop, but > instead of pg_usleep() you'd call some new primitive function that waits > until the shared variable changes. Maybe someday --- it's certainly not something we need to mess with for 8.5. As Simon comments, getting it to work nicely in the face of corner cases (like processes dying unexpectedly) could be a lot of 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: Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
Tom Lane wrote: > Heikki Linnakangas writes: >> Streaming Replication introduces a few places with a polling pattern >> like this (in pseudocode): > >> while() >> { >> /* Check if variable in shared has advanced beoynd X */ >> SpinLockAcquire() >> localvar = sharedvar; >> SpinLockRelease() >> if (localvar > X) >> break; > >> /* Not yet. Sleep >> pg_usleep(100); >> } > > I trust there's a CHECK_FOR_INTERRUPTS in there ... > >> It would be nice to have a new synchronization primitive for that. > > Maybe. The lock, the variable, the comparison operation, and the sleep > time all seem rather specific to each application. Not sure that it'd > really buy much to try to turn it into a generic subroutine. My point is that we should replace such polling loops with something non-polling, using wait/signal or semaphores or something. That gets quite a bit more complex. You'd probably still have the loop, but instead of pg_usleep() you'd call some new primitive function that waits until the shared variable changes. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wed, 2010-01-20 at 17:40 +0100, Andres Freund wrote: > > > or similar things with LWLockAcquire in a signal handler > > > > [ grows visibly pale ] *Please* tell me we are not trying to take > > locks in a signal handler. What happens if it interrupts code that > > is already holding that lock? > Yes the patch does that at two places. I think it would be more sensible to discuss specific code and issues, rather than have general discussions about various horrors. You've already pointed out that I need to prevent multiple sigalrm interrupts using boolean flags; I've already said that I would do that. The use of locks themselves are clearly not a problem, since the existing sigalrm handler takes LWlocks for deadlock detection. The problem is just about being called multiple times. The code in HoldingBufferPinThatDelaysRecovery() also needs protection against being interrupted multiple times, but we should note that a second signal of that type is not going to arrive from anywhere inside the server and requires an explicit user action. The locking isn't strictly necessary since the value is only read when the only process that ever writes that value is sleeping on a semaphore. The single integer value can always be read atomically anyway. So I will remove the locking in XXXStartupBufferPinWaitBufId(), add in the booleans and we're done. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
On Wed, 2010-01-20 at 20:00 +0200, Heikki Linnakangas wrote: > Hot standby also has a polling loop where it waits for a > transaction a transaction to die, though I'm not sure if that can be > fit into the same model I prefer that in the context of HS because the Startup process is waiting for things to die. Given that their death may not be handled sweetly, I would not wish to rely on that to wake Startup. In the other two cases you mention all processes are working together normally and we aren't expecting the other processes to die. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
Heikki Linnakangas writes: > Streaming Replication introduces a few places with a polling pattern > like this (in pseudocode): > while() > { > /* Check if variable in shared has advanced beoynd X */ > SpinLockAcquire() > localvar = sharedvar; > SpinLockRelease() > if (localvar > X) > break; > /* Not yet. Sleep > pg_usleep(100); > } I trust there's a CHECK_FOR_INTERRUPTS in there ... > It would be nice to have a new synchronization primitive for that. Maybe. The lock, the variable, the comparison operation, and the sleep time all seem rather specific to each application. Not sure that it'd really buy much to try to turn it into a generic subroutine. 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
Synchronization primitives (Was: Re: [HACKERS] An example of bugs for Hot Standby)
Andres Freund wrote: > On Wednesday 20 January 2010 17:59:36 Tom Lane wrote: >> Andres Freund writes: >>> I realize its way too late in the cycle for that, but why dont we start >>> using some library for easy cross platform atomic ops? >> (1) there probably isn't one that does exactly what we want, works >> everywhere, and has the right license; >> (2) what actual gain would we get? We've already done the work. > That there might be some other instructions were interested in? > Like really atomic increment? This reminds me of something I've been pondering for some time: Streaming Replication introduces a few places with a polling pattern like this (in pseudocode): while() { /* Check if variable in shared has advanced beoynd X */ SpinLockAcquire() localvar = sharedvar; SpinLockRelease() if (localvar > X) break; /* Not yet. Sleep pg_usleep(100); } For example, startup process polls like that to wait for walreceiver to write & flush new WAL to be replayed. And in master, walsender polls like that for new WAL to be generated, so that it can be sent to standby. Hot standby also has a polling loop where it waits for a transaction a transaction to die, though I'm not sure if that can be fit into the same model. That's OK for asynchronous replication, but unacceptable for synchronous mode. It would be nice to have a new synchronization primitive for that. -- 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] MonetDB test says that PostgreSQL often has errors or missing results
On Tue, Jan 19, 2010 at 10:04 PM, Greg Smith wrote: > Josh Berkus wrote: >> >> Actually, the report which MonetDB has published I believe is illegal. >> If they're not running it through the TPC, they can't claim it's a >> "TPCH" result. >> > > I just resisted getting into that but now you've set me off again. > Presumably they're using the public TPC-H data and query generator > distributed by the TPC, and there's certainly plenty of other unofficial > reports of results using that floating around. Where I think they really > crossed the line here is using that kit to produce unaudited results, and > then publishing results that included comparisons against a competitor, > which is clearly not what the TPC intends you to do here. What the TPC provides isn't really a usable kit. It could be entertaining to see how their kit works. Regards, Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [NOVICE] Python verison for build in config.pl (Win32)
Matt writes: > Attempting to build 8.5 alpha on Windows XP (MSVC 2005) with Python support. > Path to local interpreter added to config.pl (C:\Python), but message is > presented: > "Could not determine python version from path at build.pl line 38" > Do the build scripts attempt to determine the Python version from the path > name? Since my machine has a generic path name, is there a way to specify > the interpreter version? Hm, I see this in Mkvcbuild.pm: $solution->{options}->{python} =~ /\\Python(\d{2})/i || croak "Could not determine python version from path"; $plpython->AddLibrary($solution->{options}->{python} . "\\Libs\\python$1.lib"); Apparently you need to hack that to deduce the appropriate library pathname. What exactly is your python path name, and is it a standard installation pattern at all? 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] About "Our CLUSTER implementation is pessimal" patch
> I read the thread "Our CLUSTER implementation is pessimal" > http://archives.postgresql.org/pgsql-hackers/2008-08/msg01371.php . > > I would like to try/integrate that patch as we use CLUSTER a lot on our > system. > > I was going to try to add the proper cost_index/cost_sort calls to decide > which > "path" should be executed, as in: > > http://archives.postgresql.org/pgsql-hackers/2008-09/msg00517.php I think I got something up and running to check if a table scan + sort is supposed to be faster than an index scan for a certain CLUSTER operation. The way I did it is (I guess...) wrong: I created the elements needed by get_relation_info, create_seqscan_path, create_index_path, cost_sort. It has been, obviously, a trial and error approach: I added the member values as soon as one function call crashed... and I bet I didn't get all the corner cases. Is there any better way of doing it? Leonardo (this is called in copy_heap_data to decide which path to choose:) static bool use_index_scan(Oid tableOid, Oid indexOid) { RelOptInfo *rel; PlannerInfo *root; Query *query; PlannerGlobal *glob; Path *seqAndSortPath; IndexPath *indexPath; RangeTblEntry *rte; rel = makeNode(RelOptInfo); rel->reloptkind = RELOPT_BASEREL; rel->relid = 1; rel->rtekind = RTE_RELATION; /* needed by get_relation_info */ glob = makeNode(PlannerGlobal); /* needed by get_relation_info: */ query = makeNode(Query); query->resultRelation = 0; root = makeNode(PlannerInfo); root->parse = query; root->glob = glob; get_relation_info(root, tableOid, false, rel); seqAndSortPath = create_seqscan_path(NULL, rel); rel->rows = rel->tuples; rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = tableOid; root->simple_rel_array_size = 2; root->simple_rte_array = (RangeTblEntry **) palloc0(root->simple_rel_array_size * sizeof(RangeTblEntry *)); root->simple_rte_array[1] = rte; root->total_table_pages = rel->pages; indexPath = create_index_path(root, (IndexOptInfo*)(list_head(rel->indexlist)->data.ptr_value), NULL, NULL, ForwardScanDirection, NULL); cost_sort(seqAndSortPath, root, NULL, seqAndSortPath->total_cost, rel->tuples, rel->width, -1); return indexPath->path.total_cost < seqAndSortPath->total_cost; } -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
Hi Tom, Hi Simon, On Wednesday 20 January 2010 17:59:36 Tom Lane wrote: > Andres Freund writes: > > I realize its way too late in the cycle for that, but why dont we start > > using some library for easy cross platform atomic ops? > > (1) there probably isn't one that does exactly what we want, works > everywhere, and has the right license; > (2) what actual gain would we get? We've already done the work. That there might be some other instructions were interested in? Like really atomic increment? > >> [ grows visibly pale ] *Please* tell me we are not trying to take > >> locks in a signal handler. What happens if it interrupts code that > >> is already holding that lock? > > > > Yes the patch does that at two places. > > That's a must-fix. Its code intended to fix a existing problem not already comitted code. But otherwise I definitely agree. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
Tom Lane writes: > The proposed patch to just provide a helpful message > is only a dozen or two lines, which is about the right amount of effort > to expend in this direction IMHO. For the record, agreed on the commands for which we have no obvious equivalent :) Regards, -- Dimitri Fontaine PostgreSQL DBA, Architecte -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
Andres Freund writes: > I realize its way too late in the cycle for that, but why dont we start using > some library for easy cross platform atomic ops? (1) there probably isn't one that does exactly what we want, works everywhere, and has the right license; (2) what actual gain would we get? We've already done the work. >> [ grows visibly pale ] *Please* tell me we are not trying to take >> locks in a signal handler. What happens if it interrupts code that >> is already holding that lock? > Yes the patch does that at two places. That's a must-fix. 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] MySQL-ism help patch for psql
Dimitri Fontaine writes: > I'll give my vote to Peter's idea that show tables; should better act as > if you typed \d. We have previously considered and rejected this type of approach, for example in the pgsql-bugs discussion I referenced upthread. > I don't see what the gain is to refuse being nice to MySQL newcomers > when someone actually does the work. Nobody has actually done such work, nor offered to. If it did show up it would be a large and ugly patch that would have a good chance of being rejected. The proposed patch to just provide a helpful message is only a dozen or two lines, which is about the right amount of effort to expend in this direction IMHO. 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] An example of bugs for Hot Standby
On Wednesday 20 January 2010 17:30:04 Tom Lane wrote: > Andres Freund writes: > > On Wednesday 20 January 2010 06:30:28 Tom Lane wrote: > >> Er ... what? I believe there are live platforms with sig_atomic_t = > >> char. If we're assuming more that's a must-fix. > > > > The reason I have asked is that the code is doing things like: > > [ grabbing a spinlock to read a single integer ] > > Yes, I think we probably actually need that. The problem is not so > much whether the read is an atomic operation as whether you can rely > on getting an up-to-date value. On multiprocessors with weak memory > ordering you need some type of "sync" instruction to be sure you will > see a value that was recently written by another processor. Currently, > we embed such instructions in the spinlock acquire/release code. > There's been some discussion of exposing memory sync independently > of lock acquisition; perhaps that would be enough here, but I haven't > looked at the surrounding logic enough to say. I think it should be enough. I realize its way too late in the cycle for that, but why dont we start using some library for easy cross platform atomic ops? I think libatomic or such should support the required platforms. > My complaint at the top was responding to the idea that someone might > be supposing the specific type sig_atomic_t was at least as wide as > int. That's a different matter altogether. We do assume in some places > that we can read or write the specific type TransactionId indivisibly, > but we don't try to declare it as sig_atomic_t. So we already assume that? Fine. (yes, the sig_atomic_t was a sidetrack - I had memorized it wrongly as "the biggest value that can be read/written atomically which is *clearly* wrong) > > or similar things with LWLockAcquire in a signal handler > > [ grows visibly pale ] *Please* tell me we are not trying to take > locks in a signal handler. What happens if it interrupts code that > is already holding that lock? Yes the patch does that at two places. Thats what I was complaining about and what triggered my sig_atomic_t question because of the above explained misunderstanding. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
Andres Freund writes: > On Wednesday 20 January 2010 06:30:28 Tom Lane wrote: >> Er ... what? I believe there are live platforms with sig_atomic_t = char. >> If we're assuming more that's a must-fix. > The reason I have asked is that the code is doing things like: > [ grabbing a spinlock to read a single integer ] Yes, I think we probably actually need that. The problem is not so much whether the read is an atomic operation as whether you can rely on getting an up-to-date value. On multiprocessors with weak memory ordering you need some type of "sync" instruction to be sure you will see a value that was recently written by another processor. Currently, we embed such instructions in the spinlock acquire/release code. There's been some discussion of exposing memory sync independently of lock acquisition; perhaps that would be enough here, but I haven't looked at the surrounding logic enough to say. My complaint at the top was responding to the idea that someone might be supposing the specific type sig_atomic_t was at least as wide as int. That's a different matter altogether. We do assume in some places that we can read or write the specific type TransactionId indivisibly, but we don't try to declare it as sig_atomic_t. > or similar things with LWLockAcquire in a signal handler [ grows visibly pale ] *Please* tell me we are not trying to take locks in a signal handler. What happens if it interrupts code that is already holding that lock? 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] Git out of sync vs. CVS
Heikki Linnakangas writes: > Magnus Hagander wrote: >> Actually, such a correction patch would be nice and short. Attached >> for reference. Thoughts? > That seems better than rewinding the history all the way back to August. +1 ... I'm just an interested observer not a user of the git repository, but this approach seems far less work for everyone concerned. 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] Listen / Notify - what to do when the queue is full
Joachim Wieland writes: > On Wed, Jan 20, 2010 at 1:05 AM, Tom Lane wrote: >> I guess Joachim is trying to provide a similar guarantee for the new >> implementation, but I'm not clear on why it would require locking. > It is rather about a listening backend seeing a notification in the > global queue without knowing if it should deliver the notification to > its frontend or not. The backend needs to know if its own LISTEN > committed before or after the NOTIFY committed that it sees in the > queue. In that case I think you've way overcomplicated matters. Just deliver the notification. We don't really care if the listener gets additional notifications; the only really bad case would be if it failed to get an event that was generated after it committed a LISTEN. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lock_timeout GUC patch
Robert Haas writes: > 2010/1/20 Boszormenyi Zoltan : >> Attached with the proposed modification to lift the portability concerns. > I think that it is a very bad idea to implement this feature in a way > that is not 100% portable. Agreed, this is not acceptable. If there were no possible way to implement the feature portably, we *might* consider doing it like this. But I think more likely it'd get rejected anyway. When there is a clear path to a portable solution, it's definitely not going to fly to submit a nonportable 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] lock_timeout GUC patch
2010/1/20 Boszormenyi Zoltan : > Attached with the proposed modification to lift the portability concerns. > Fixed the missing check for get_rel_name() and one typo ("transation") > Introduced checks for semtimedop() and sem_timedwait() in configure.in > and USE_LOCK_TIMEOUT in port.h depending on > HAVE_DECL_SEMTIMEDOP || HAVE_DECL_SEM_TIMEDWAIT || WIN32 > Introduced assign_lock_timeout() GUC validator function that allows > setting the value only from the wired-in-default (0) or from SET statements. > > Comments? I think that it is a very bad idea to implement this feature in a way that is not 100% portable. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bloom filters bloom filters bloom filters
> Then your union operation is to just bitwise or the two bloomfilters. Keep in mind that when performing this sort of union between two comparably-sized sets, your false-positive rate will increase by about an order of magnitude. You need to size your bloom filters accordingly, or perform the union differently. Intersections, however, behave well. There is a similar problem, among others, with expanding smaller filters to match larger ones. David Hudson
Re: [HACKERS] MySQL-ism help patch for psql
I would personally emulate \d and take the chance for showing a funny warning, something like: "hey, it's not MySql!" or similar. I am sure we will Finder something appropriate. :) Inviato da iPhone Il giorno 20/gen/2010, alle ore 16.30, "Kevin Grittner" > ha scritto: Dimitri Fontaine wrote: I'll give my vote to Peter's idea that show tables; should better act as if you typed \d. I guess we don't need a "tables" GUC. Show all wouldn't include it? Would we require a semicolon? Do we support \d-style globs? Still seems kinda messy. +1 for help to show the PostgreSQL command as a guess for what they want to do. -1 for MySQL emulation. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
Dimitri Fontaine wrote: > I'll give my vote to Peter's idea that show tables; should better > act as if you typed \d. I guess we don't need a "tables" GUC. Show all wouldn't include it? Would we require a semicolon? Do we support \d-style globs? Still seems kinda messy. +1 for help to show the PostgreSQL command as a guess for what they want to do. -1 for MySQL emulation. -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] MySQL-ism help patch for psql
Dimitri Fontaine wrote: > Robert Haas writes: > > If what the user wanted was to be using MySQL, he is out of luck > > anyway. > > That's not what we're talking about. We're talking about having a nice > client tool for those people having to do both MySQL and PostgreSQL > support, or new to PostgreSQL and comming from MySQL. > > I'll give my vote to Peter's idea that show tables; should better act as > if you typed \d. > > I don't see what the gain is to refuse being nice to MySQL newcomers > when someone actually does the work. If the USE keyword is one we want > to keep free for our own usage, let just skip that compat option. I think the problem is that many other MySQL commands will not work or be supported, and if you give the person the desired output _and_ a suggestion to use \d, the suggests is easily overlooked. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
Robert Haas writes: > If what the user wanted was to be using MySQL, he is out of luck > anyway. That's not what we're talking about. We're talking about having a nice client tool for those people having to do both MySQL and PostgreSQL support, or new to PostgreSQL and comming from MySQL. I'll give my vote to Peter's idea that show tables; should better act as if you typed \d. I don't see what the gain is to refuse being nice to MySQL newcomers when someone actually does the work. If the USE keyword is one we want to keep free for our own usage, let just skip that compat option. > I'm actually no big advocate of the \d commands. They're basically > magical queries that you can't easily see or edit We already have the psql \set ECHO_HIDDEN command to easily see the query, then it's a copy/paste away. I'd propose to have this setting also make it so that the query it runs is placed in the buffer for next \e command, which is not the case in 8.4. Regards, -- dim -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Git out of sync vs. CVS
Robert Haas wrote: > On Wed, Jan 20, 2010 at 4:27 AM, Heikki Linnakangas > wrote: >> Magnus Hagander wrote: >>> Actually, such a correction patch would be nice and short. Attached >>> for reference. Thoughts? >> That seems better than rewinding the history all the way back to August. > > It seems pretty horrible to me. That means we'll have a range of > times 5 months long for which the git repository doesn't match CVS. > > Admittedly, I understand that this is going to be extremely painful > for anyone who (like Heikki) has to manage a substantial private > branch. I won't object to rewinding, it should be fairly painless to rebase. > I haven't been in a hurry to see us move to git because the git mirror > is, for most purposes, just as good. But if the git mirror is going > to start sucking, then I'm in a hurry. The way I used to work before > I learned git seems laughable now, and I do NOT want to go back. My feelings exactly. I'm not in a hurry to switch because the mirror is good enough for me. But if *I* have to spend time fixing the mirror every few weeks, I'm not happy. Magnus has been kind enough to handle the last mirror troubles, but I believe hë́ shares the feeling. -- 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] lock_timeout GUC patch
Hi, I wrote: > Okay, after reading google it seems you're right that OS X lacks > sem_timedwait(). Jaime Casanova írta: > If that's the case then others timeouts should be failing on os x, no? > But i have never hear that > among others, I found this reference on the missing sem_timedwait() function: http://bugs.freepascal.org/view.php?id=13148 Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
Robert Haas wrote: I'm actually no big advocate of the \d commands. They're basically magical queries that you can't easily see or edit - I've more than once wished for a WHERE clause (\df WHERE "Result data type" = 'internal' or what have you. You *can* easily see them, at least. Run "psql -E" or inside psql do "\set ECHO_HIDDEN" cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lock_timeout GUC patch
If that's the case then others timeouts should be failing on os x, no? But i have never hear that 2010/1/20, Boszormenyi Zoltan : > Boszormenyi Zoltan írta: >> Tom Lane írta: >> >>> Greg Stark writes: >>> >>> we already have statement timeout it seems the natural easy to implement this is with more hairy logic to calculate the timeout until the next of the three timeouts should fire and set sigalarm. I sympathize with whoever tries to work that through though, the logic is hairy enough with just the two variables...but at least we know that sigalarm works or at least it had better... >>> Yeah, that code is ugly as sin already. Maybe there is a way to >>> refactor it so it can scale better? I can't help thinking of Polya's >>> inventor's paradox ("the more general problem may be easier to solve"). >>> >>> If we want to do it without any new system-call dependencies I think >>> that's probably the only way. I'm not necessarily against new >>> dependencies, if they're portable --- but it seems these aren't. >>> >>> >> >> Okay, after reading google it seems you're right that OS X lacks >> sem_timedwait(). How about adding a configure check for semtimedop() >> and sem_timedwait() and if they don't exist set a compile time flag >> (HAVE_XXX) and in this case PGSemaphoreTimedLock() would >> behave the same as PGSemaphoreLock() and have an assign_*() >> function that tells the user that the timeout functionality is missing? >> We have precedent for the missing functionality with e.g. >> effective_io_concurrency and ereport() is also allowed in such >> functions, see assign_transaction_read_only(). >> > > Attached with the proposed modification to lift the portability concerns. > Fixed the missing check for get_rel_name() and one typo ("transation") > Introduced checks for semtimedop() and sem_timedwait() in configure.in > and USE_LOCK_TIMEOUT in port.h depending on > HAVE_DECL_SEMTIMEDOP || HAVE_DECL_SEM_TIMEDWAIT || WIN32 > Introduced assign_lock_timeout() GUC validator function that allows > setting the value only from the wired-in-default (0) or from SET statements. > > Comments? > > Best regards, > Zoltán Böszörményi > > -- > Bible has answers for everything. Proof: > "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more > than these cometh of evil." (Matthew 5:37) - basics of digital technology. > "May your kingdom come" - superficial description of plate tectonics > > -- > Zoltán Böszörményi > Cybertec Schönig & Schönig GmbH > http://www.postgresql.at/ > > -- Enviado desde mi dispositivo móvil Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
On Wed, Jan 20, 2010 at 9:26 AM, Peter Eisentraut wrote: > On ons, 2010-01-20 at 09:05 -0500, Bruce Momjian wrote: >> I disagree. No one has complained that we are being a "smartass" by >> reporting this for "help" in psql: >> >> You are using psql, the command-line interface to PostgreSQL. >> Type: \copyright for distribution terms >> \h for help with SQL commands >> \? for help with psql commands >> \g or terminate with semicolon to execute query >> \q to quit >> >> while to be really helpful we would display \?. After extensive >> discussion we chose against that because we wanted to steer people to >> the proper commands, rather than have them consider 'help' as a valid >> command. The same is true for the MySQL commands --- we just want to >> point people to the proper commands. > > That's not the same thing. The user typed "help" and you help him. If > the user types "show tables", you show him the tables. If the user > typed "show tables" and you send him a help message, that is not what > the user wanted. If what the user wanted was to be using MySQL, he is out of luck anyway. I'm actually no big advocate of the \d commands. They're basically magical queries that you can't easily see or edit - I've more than once wished for a WHERE clause (\df WHERE "Result data type" = 'internal' or what have you. But I don't have a practical solution for dealing with that problem, and I think trying to emulate MySQL is probably not a good idea... what if we wanted to make "USE" actually mean something some day? If it just prints out a helpful error message, that could still be possible (and we lose the helpful error message), but if people are expecting it to work, we're hosed. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Git out of sync vs. CVS
On Wed, Jan 20, 2010 at 4:27 AM, Heikki Linnakangas wrote: > Magnus Hagander wrote: >> On Wed, Jan 20, 2010 at 09:52, Magnus Hagander wrote: >>> On Tue, Jan 19, 2010 at 16:59, Robert Haas wrote: On Tue, Jan 19, 2010 at 10:44 AM, Magnus Hagander wrote: > On Mon, Jan 18, 2010 at 01:53, Kevin Grittner > wrote: >> Magnus Hagander wrote: >> >> the Git repository is missing parts of two non-recent commits. >>> We've seen this happen before. >> That seems like kind of a blasé attitude toward something upon which >> some people rely. > For the record, I am one of those people. I use it for *all* my > postgresql development. And this is a serious pain. FWIW, I am in favor of rewinding and making everyone rebase, but I think we should do it ASAP. >>> Ok, I started looking at this. >>> >>> First, it's not at all clear to me what Peter means wiht his comments. >>> But it happens to be that one of the commits he's referring to is all >>> the way back in August. So we'd have to rewind it all that way. Do we >>> really want to do that, or do we want to do a manual commit on the >>> repository bringing it back in sync instead? (either by knowing what's >>> wrong with those commits, or do a complete diff of cvs head vs git >>> head) >> >> Actually, such a correction patch would be nice and short. Attached >> for reference. Thoughts? > > That seems better than rewinding the history all the way back to August. It seems pretty horrible to me. That means we'll have a range of times 5 months long for which the git repository doesn't match CVS. Admittedly, I understand that this is going to be extremely painful for anyone who (like Heikki) has to manage a substantial private branch. I haven't been in a hurry to see us move to git because the git mirror is, for most purposes, just as good. But if the git mirror is going to start sucking, then I'm in a hurry. The way I used to work before I learned git seems laughable now, and I do NOT want to go back. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
On Wed, Jan 20, 2010 at 9:05 AM, Bruce Momjian wrote: > Peter Eisentraut wrote: >> On tis, 2010-01-19 at 16:00 -0600, David Christensen wrote: >> > Currently, a session will look like the following: >> > >> > machack:machack:5485=# show tables; >> > See: >> > \d >> > or \? for general help with psql commands >> > machack:machack:5485=# >> >> I think if you make "show tables" and the others actually execute \d and >> then possibly print a notice about what the "better" command would have >> been, you actually *help* people do their work instead of appearing to >> be a smartass -- "See, we took the time to research what you want to do, >> and here is why it's wrong." >> >> Moreover, the backslash is really hard to type on some keyboards, so I'd >> expect significant uptake for people to use the SHOW variants as their >> primary method. > > I disagree. No one has complained that we are being a "smartass" by > reporting this for "help" in psql: > > You are using psql, the command-line interface to PostgreSQL. > Type: \copyright for distribution terms > \h for help with SQL commands > \? for help with psql commands > \g or terminate with semicolon to execute query > \q to quit > > while to be really helpful we would display \?. After extensive > discussion we chose against that because we wanted to steer people to > the proper commands, rather than have them consider 'help' as a valid > command. The same is true for the MySQL commands --- we just want to > point people to the proper commands. +1. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
On ons, 2010-01-20 at 09:05 -0500, Bruce Momjian wrote: > I disagree. No one has complained that we are being a "smartass" by > reporting this for "help" in psql: > > You are using psql, the command-line interface to PostgreSQL. > Type: \copyright for distribution terms >\h for help with SQL commands >\? for help with psql commands >\g or terminate with semicolon to execute query >\q to quit > > while to be really helpful we would display \?. After extensive > discussion we chose against that because we wanted to steer people to > the proper commands, rather than have them consider 'help' as a valid > command. The same is true for the MySQL commands --- we just want to > point people to the proper commands. That's not the same thing. The user typed "help" and you help him. If the user types "show tables", you show him the tables. If the user typed "show tables" and you send him a help message, that is not what the user wanted. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Small locking bugs in hs
On Wed, 2010-01-20 at 14:13 +0100, Andres Freund wrote: > I do understand it correctly that in CancelVirtualTransaction > LW_SHARED is > taken only so that another transaction can finish during that time? We're canceling one specific vxid, so no need to block other snapshots from being taken. Read only queries don't take ProcArrayLock when they complete and the Startup process is the only process to record xact completion during recovery. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
Peter Eisentraut wrote: > On tis, 2010-01-19 at 16:00 -0600, David Christensen wrote: > > Currently, a session will look like the following: > > > >machack:machack:5485=# show tables; > >See: > > \d > > or \? for general help with psql commands > >machack:machack:5485=# > > I think if you make "show tables" and the others actually execute \d and > then possibly print a notice about what the "better" command would have > been, you actually *help* people do their work instead of appearing to > be a smartass -- "See, we took the time to research what you want to do, > and here is why it's wrong." > > Moreover, the backslash is really hard to type on some keyboards, so I'd > expect significant uptake for people to use the SHOW variants as their > primary method. I disagree. No one has complained that we are being a "smartass" by reporting this for "help" in psql: You are using psql, the command-line interface to PostgreSQL. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit while to be really helpful we would display \?. After extensive discussion we chose against that because we wanted to steer people to the proper commands, rather than have them consider 'help' as a valid command. The same is true for the MySQL commands --- we just want to point people to the proper commands. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Streaming Replication and archiving
I've been having a look at this, one master + one replica and also one master + 2 replicas. I gotta say this is a nice piece of functionality (particularly the multiple replicas). I've been using the wiki page (http://wiki.postgresql.org/wiki/Streaming_Replication) as a guide, and I notice that it recommends the master (and replicas) have a non-trivial archive_command even after the backup step is completed. ISTM that after the backup the master's archive_command can be set to '' or '/bin/true' as the walsender does not make any use of the WAL archive (AFAICS anyway). Clearly it might be desirable to have the archived segments around for other reasons - but equally it might be desirable *not* to have to have to (e.g disk space), or am I overlooking something? Cheers Mark -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
On tis, 2010-01-19 at 16:00 -0600, David Christensen wrote: > Currently, a session will look like the following: > >machack:machack:5485=# show tables; >See: > \d > or \? for general help with psql commands >machack:machack:5485=# I think if you make "show tables" and the others actually execute \d and then possibly print a notice about what the "better" command would have been, you actually *help* people do their work instead of appearing to be a smartass -- "See, we took the time to research what you want to do, and here is why it's wrong." Moreover, the backslash is really hard to type on some keyboards, so I'd expect significant uptake for people to use the SHOW variants as their primary method. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] MySQL-ism help patch for psql
On tis, 2010-01-19 at 11:43 -0800, Jeff Davis wrote: > I'll make an analogy to: > > $ git difff > git: 'difff' is not a git-command. See 'git --help'. > > Did you mean this? > diff This is presumably spelling-based, which might be an interesting feature (although probably useless for psql's single-letter commands). Maybe this analogy is more interesting, for a user that recently used cvs: $ git update git: 'update' is not a git-command. See 'git --help'. Did you mean this? update-ref --> Probably not. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Small locking bugs in hs
On Wednesday 20 January 2010 12:59:40 Simon Riggs wrote: > On Wed, 2010-01-20 at 04:47 +0100, Andres Freund wrote: > > On Saturday 16 January 2010 12:32:35 Simon Riggs wrote: > > > No. As mentioned upthread, this is not a bug. > > > > Could you also mention in a little bit more detail why not? > > When a cleanup record arrives without a latestRemovedXid we are forced > to assume that the xid could be as late as latestCompletedXid. > Regrettably we aren't certain which of the xids are still there since it > is possible that earlier xids in KnownAssignedXids are actually FATAL > errors that did not write abort records. So we need to conflict with all > current snapshots whose xmin is less than latestCompletedXid to be safe. > This can cause false positives in our assessment of which vxids > conflict. > By using exclusive lock we prevent new snapshots from being taken while > we work out which snapshots to conflict with. This protects those new > snapshots from also being included in our conflict list. > > After the lock is released, we allow snapshots again. It is possible > that we arrive at a snapshot that is identical to one that we just > decided we should conflict with. This a case of false positives, not an > actual problem. > > There are two cases: (1) if we were correct in using latestCompletedXid > then that means that all xids in the snapshot lower than that are FATAL > errors, so not xids that ever commit. We can make no visibility errors > if we allow such xids into the snapshot. (2) if we erred on the side of > caution and in fact the latestRemovedXid should have been earlier than > latestCompletedXid then we conflicted with a snapshot needlessly. Taking > another identical snapshot is OK, because the earlier conflicted > snapshot was a false positive. > > In either case, a snapshot taken after conflict assessment will still be > valid and non-conflicting even if an identical snapshot that existed > before conflict assessment was assessed as conflicting. > > If we allowed concurrent snapshots while we were deciding who to > conflict with we would need to include all concurrent snapshotters in > the conflict list as well. We'd have difficulty in working out exactly > who that was, so it is happier for all concerned if we take an exclusive > lock. > > It also means that users waiting for a snapshot is a good thing, since > it is more likely that they will live longer after having waited. So its > not a bug for us to use exclusive lock and is actually desirable. > > We could reduce false positives by having the master calculate the exact > xmin each time it issues an XLOG_BTREE_DELETE record. That would > introduce more contention since that happens during btree split > operations, so might be counter productive. Wow. Thanks for the extensive explanation! I do understand it correctly that in CancelVirtualTransaction LW_SHARED is taken only so that another transaction can finish during that time? Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch: Allow substring/replace() to get/set bit values
> All issues addressed, with one tiny nit-pick -- the get_bit and > set_bit methods are not part of the SQL standard. Damn! I completely forgot to mention that I had no idea if what I wrote in the docs made any sense... Well thank you for your thorough review. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch: Allow substring/replace() to get/set bit values
Leonardo F wrote: > New version of the patch, let me know if I can fix/change something > else. All issues addressed, with one tiny nit-pick -- the get_bit and set_bit methods are not part of the SQL standard. I took the liberty of removing "SQL-standard" from the documentation of these functions so that I can mark this "Ready for Committer". Thanks for the patch! -Kevin getsetbit.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lock_timeout GUC patch
Boszormenyi Zoltan írta: > Tom Lane írta: > >> Greg Stark writes: >> >> >>> we already have statement timeout it seems the natural easy to implement >>> this is with more hairy logic to calculate the timeout until the next of the >>> three timeouts should fire and set sigalarm. I sympathize with whoever tries >>> to work that through though, the logic is hairy enough with just the two >>> variables...but at least we know that sigalarm works or at least it had >>> better... >>> >>> >> Yeah, that code is ugly as sin already. Maybe there is a way to >> refactor it so it can scale better? I can't help thinking of Polya's >> inventor's paradox ("the more general problem may be easier to solve"). >> >> If we want to do it without any new system-call dependencies I think >> that's probably the only way. I'm not necessarily against new >> dependencies, if they're portable --- but it seems these aren't. >> >> > > Okay, after reading google it seems you're right that OS X lacks > sem_timedwait(). How about adding a configure check for semtimedop() > and sem_timedwait() and if they don't exist set a compile time flag > (HAVE_XXX) and in this case PGSemaphoreTimedLock() would > behave the same as PGSemaphoreLock() and have an assign_*() > function that tells the user that the timeout functionality is missing? > We have precedent for the missing functionality with e.g. > effective_io_concurrency and ereport() is also allowed in such > functions, see assign_transaction_read_only(). > Attached with the proposed modification to lift the portability concerns. Fixed the missing check for get_rel_name() and one typo ("transation") Introduced checks for semtimedop() and sem_timedwait() in configure.in and USE_LOCK_TIMEOUT in port.h depending on HAVE_DECL_SEMTIMEDOP || HAVE_DECL_SEM_TIMEDWAIT || WIN32 Introduced assign_lock_timeout() GUC validator function that allows setting the value only from the wired-in-default (0) or from SET statements. Comments? Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ diff -dcrpN --exclude=configure pgsql.orig/configure.in pgsql.1/configure.in *** pgsql.orig/configure.in 2010-01-17 20:44:10.0 +0100 --- pgsql.1/configure.in 2010-01-20 12:13:20.0 +0100 *** if test "$PORTNAME" != "win32"; then *** 1674,1686 --- 1674,1692 if test x"$USE_NAMED_POSIX_SEMAPHORES" = x"1" ; then AC_DEFINE(USE_NAMED_POSIX_SEMAPHORES, 1, [Define to select named POSIX semaphores.]) SEMA_IMPLEMENTATION="src/backend/port/posix_sema.c" + AC_CHECK_FUNC(sem_timedwait) + AC_CHECK_DECLS(sem_timedwait, [], [], [#include ]) else if test x"$USE_UNNAMED_POSIX_SEMAPHORES" = x"1" ; then AC_DEFINE(USE_UNNAMED_POSIX_SEMAPHORES, 1, [Define to select unnamed POSIX semaphores.]) SEMA_IMPLEMENTATION="src/backend/port/posix_sema.c" + AC_CHECK_FUNC(sem_timedwait) + AC_CHECK_DECLS(sem_timedwait, [], [], [#include ]) else AC_DEFINE(USE_SYSV_SEMAPHORES, 1, [Define to select SysV-style semaphores.]) SEMA_IMPLEMENTATION="src/backend/port/sysv_sema.c" + AC_CHECK_FUNC(semtimedop) + AC_CHECK_DECLS(semtimedop, [], [], [#include ]) fi fi else diff -dcrpN --exclude=configure pgsql.orig/doc/src/sgml/config.sgml pgsql.1/doc/src/sgml/config.sgml *** pgsql.orig/doc/src/sgml/config.sgml 2010-01-15 11:02:47.0 +0100 --- pgsql.1/doc/src/sgml/config.sgml 2010-01-20 11:37:23.0 +0100 *** COPY postgres_log FROM '/full/path/to/lo *** 4236,4241 --- 4236,4265 + + lock_timeout (integer) + +lock_timeout configuration parameter + + + + Abort any statement that tries to acquire a heavy-weight lock (e.g. rows, + pages, tables, indices or other objects) and the lock has to wait more + than the specified number of milliseconds, starting from the time the + command arrives at the server from the client. + If log_min_error_statement is set to ERROR or lower, + the statement that timed out will also be logged. A value of zero + (the default) turns off the limitation. + + + + Setting lock_timeout in + postgresql.conf is not recommended because it + affects all sessions. + + + + vacuum_freeze_table_age (integer) diff -dcrpN --exclude=configure pgsql.orig/doc/src/sgml/ref/lock.sgml pgsql.1/doc/src/sgml/ref/lock.sgml *** pgsql.orig/doc/src/sgml/ref/lock.sgml 2009-09-18 08:26:40.0
Re: [HACKERS] Patch rev 2: MySQL-ism help patch for psql
On Tue, Jan 19, 2010 at 5:01 PM, David Christensen wrote: > > On Jan 19, 2010, at 4:23 PM, Robert Haas wrote: > >> On Tue, Jan 19, 2010 at 5:14 PM, David E. Wheeler >> wrote: >>> >>> Why would they want more? It's not MySQL, and they know that. If we give >>> them some very minor helpful hints for the most common things they try to >>> do, it would be a huge benefit to them. I know I've badly wanted the >>> opposite when I've had to use MySQL, but I don't expect MySQL to implement >>> \c for me. >> >> +1. I think this is a well-thought out proposal. I like Tom's >> suggestion upthread for how to handle \c. > > I've attached a second revision of this patch incorporating the various > feedback I've received. > >> Although the deadline for patches for 8.5 has supposedly already >> passed > > Yeah, I realized this after I scratched my itch, and had just thought I > would send to the list any way for after the CF; you can commit or bump as > needed. Patch enclosed as a context-diff attachment this time. > > Regards, > > David > -- > David Christensen > End Point Corporation > da...@endpoint.com Although I have a snowballs chance in hell to convert my coworkers to using pg I think that this patch would make such an outcome more likely. Please consider what a MySQL dba does when he gets a call at 3AM that a server (p3.any43.db69.I_have_no_clue_what_this_stupid_f'ing_server_is.wtf.pg ) is at max-connections. I think that some helpful hints for non-pg dba's that are using pg in some capacity are a very good idea. -- Rob Wultsch wult...@gmail.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] Small locking bugs in hs
On Wed, 2010-01-20 at 04:47 +0100, Andres Freund wrote: > On Saturday 16 January 2010 12:32:35 Simon Riggs wrote: > > > > No. As mentioned upthread, this is not a bug. > Could you also mention in a little bit more detail why not? When a cleanup record arrives without a latestRemovedXid we are forced to assume that the xid could be as late as latestCompletedXid. Regrettably we aren't certain which of the xids are still there since it is possible that earlier xids in KnownAssignedXids are actually FATAL errors that did not write abort records. So we need to conflict with all current snapshots whose xmin is less than latestCompletedXid to be safe. This can cause false positives in our assessment of which vxids conflict. By using exclusive lock we prevent new snapshots from being taken while we work out which snapshots to conflict with. This protects those new snapshots from also being included in our conflict list. After the lock is released, we allow snapshots again. It is possible that we arrive at a snapshot that is identical to one that we just decided we should conflict with. This a case of false positives, not an actual problem. There are two cases: (1) if we were correct in using latestCompletedXid then that means that all xids in the snapshot lower than that are FATAL errors, so not xids that ever commit. We can make no visibility errors if we allow such xids into the snapshot. (2) if we erred on the side of caution and in fact the latestRemovedXid should have been earlier than latestCompletedXid then we conflicted with a snapshot needlessly. Taking another identical snapshot is OK, because the earlier conflicted snapshot was a false positive. In either case, a snapshot taken after conflict assessment will still be valid and non-conflicting even if an identical snapshot that existed before conflict assessment was assessed as conflicting. If we allowed concurrent snapshots while we were deciding who to conflict with we would need to include all concurrent snapshotters in the conflict list as well. We'd have difficulty in working out exactly who that was, so it is happier for all concerned if we take an exclusive lock. It also means that users waiting for a snapshot is a good thing, since it is more likely that they will live longer after having waited. So its not a bug for us to use exclusive lock and is actually desirable. We could reduce false positives by having the master calculate the exact xmin each time it issues an XLOG_BTREE_DELETE record. That would introduce more contention since that happens during btree split operations, so might be counter productive. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wednesday 20 January 2010 11:33:05 Simon Riggs wrote: > On Wed, 2010-01-20 at 11:04 +0100, Andres Freund wrote: > > On Wednesday 20 January 2010 10:52:24 Simon Riggs wrote: > > > On Wed, 2010-01-20 at 10:45 +0100, Andres Freund wrote: > > > > LWLockAcquire > > > > > > I'm using spinlocks, not lwlocks. > > > > CancelDBBackends which is used in SendRecoveryConflictWithBufferPin which > > in turn used by CheckStandbyTimeout triggered by SIGALRM acquires the > > lwlock. > > Those are used in similar ways to deadlock detection. But only if ImmediateInterruptOK && InterruptHoldoffCount == 0 && CritSectionCount == 0 - which is not the case with HoldingBufferPinThatDelaysRecovery. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wed, 2010-01-20 at 11:04 +0100, Andres Freund wrote: > On Wednesday 20 January 2010 10:52:24 Simon Riggs wrote: > > On Wed, 2010-01-20 at 10:45 +0100, Andres Freund wrote: > > > LWLockAcquire > > > > I'm using spinlocks, not lwlocks. > CancelDBBackends which is used in SendRecoveryConflictWithBufferPin which in > turn used by CheckStandbyTimeout triggered by SIGALRM acquires the lwlock. Those are used in similar ways to deadlock detection. > Now that case is a bit less dangerous because you would have to interrupt > yourself to trigger a deadlock there because the code sleeps soon after > setting up the handler. > If ever two SIGALRM occur consecutive there is a problem. I'll protect against subsequent calls. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: Patch: Allow substring/replace() to get/set bit values
New version of the patch, let me know if I can fix/change something else. Leonardo getsetbit.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] lock_timeout GUC patch
Tom Lane írta: > Greg Stark writes: > >> we already have statement timeout it seems the natural easy to implement >> this is with more hairy logic to calculate the timeout until the next of the >> three timeouts should fire and set sigalarm. I sympathize with whoever tries >> to work that through though, the logic is hairy enough with just the two >> variables...but at least we know that sigalarm works or at least it had >> better... >> > > Yeah, that code is ugly as sin already. Maybe there is a way to > refactor it so it can scale better? I can't help thinking of Polya's > inventor's paradox ("the more general problem may be easier to solve"). > > If we want to do it without any new system-call dependencies I think > that's probably the only way. I'm not necessarily against new > dependencies, if they're portable --- but it seems these aren't. > Okay, after reading google it seems you're right that OS X lacks sem_timedwait(). How about adding a configure check for semtimedop() and sem_timedwait() and if they don't exist set a compile time flag (HAVE_XXX) and in this case PGSemaphoreTimedLock() would behave the same as PGSemaphoreLock() and have an assign_*() function that tells the user that the timeout functionality is missing? We have precedent for the missing functionality with e.g. effective_io_concurrency and ereport() is also allowed in such functions, see assign_transaction_read_only(). Best regards, Zoltán Böszörményi -- Bible has answers for everything. Proof: "But let your communication be, Yea, yea; Nay, nay: for whatsoever is more than these cometh of evil." (Matthew 5:37) - basics of digital technology. "May your kingdom come" - superficial description of plate tectonics -- Zoltán Böszörményi Cybertec Schönig & Schönig GmbH http://www.postgresql.at/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] review: More frame options in window functions
2010/1/19 Hitoshi Harada : > 2010/1/19 Hitoshi Harada : >> Yeah, that's my point, too. The planner has to distinguish "four" from >> sort pathkeys and to teach the executor the simple information which >> column should be used to determine frame. I was bit wrong because some >> of current executor code isn't like it, like using ordNumCols == 0 to >> know whether partition equals to frame, though > > And here's another version to fix this problem (I hope). Now the > planner distinguish sort column from actual significant pathkeys. I > tested it on both of 32bit and 64bit Linux. > I tested it, and reported problems are fixed Thank you Pavel > Regards, > > > -- > Hitoshi Harada > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wednesday 20 January 2010 10:52:24 Simon Riggs wrote: > On Wed, 2010-01-20 at 10:45 +0100, Andres Freund wrote: > > LWLockAcquire > > I'm using spinlocks, not lwlocks. CancelDBBackends which is used in SendRecoveryConflictWithBufferPin which in turn used by CheckStandbyTimeout triggered by SIGALRM acquires the lwlock. Now that case is a bit less dangerous because you would have to interrupt yourself to trigger a deadlock there because the code sleeps soon after setting up the handler. If ever two SIGALRM occur consecutive there is a problem. Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bug ? different behaviour between 8.3 and 8.4 won IS NULL with sub arrays of nulls
On Tue, 19 Jan 2010, Tom Lane wrote: iog...@free.fr writes: I found a difference of behaviour between 8.3 and 8.4 on IS NULL with multi-level arrays with NULL values. 8.3's behavior is just a bug --- Ok, should I report through the -bugs ml for tracking purpose ? or is it useless cause it's on -hackers andsomeone will jump on this bug to "fix and forget it" ? try comparing the results when the values are variables that happen to be null, rather than simple constant nulls. 8.4 is consistent with that case, 8.3 isn't. Right, it behaves consistently with variables. Here is another test case where 8.3 is inconsistent with *himself* this time: < postgres=# SELECT substring(version(),12,5); substring --- 8.3.9 (1 ligne) postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL FROM (SELECT 1) t; ?column? -- t (1 ligne) postgres=# SELECT ROW(ROW(NULL),NULL) IS NULL; ?column? -- f (1 ligne) > regards, tom lane -- Jehan-Guillaume (ioguix) de Rorthais DBA http://www.dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wed, 2010-01-20 at 10:45 +0100, Andres Freund wrote: > LWLockAcquire I'm using spinlocks, not lwlocks. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wednesday 20 January 2010 10:40:10 Simon Riggs wrote: > On Wed, 2010-01-20 at 06:14 +0100, Andres Freund wrote: > > > Full resolution patch attached for Startup process waits on buffer > > > pins. > > > > > > Startup process sets SIGALRM when waiting on a buffer pin. If woken by > > > alarm we send SIGUSR1 to all backends requesting that they check to see > > > if they are blocking Startup process. If so, they throw ERROR/FATAL as > > > for other conflict resolutions. Deadlock stop gap removed. > > > max_standby_delay = -1 option removed to prevent deadlock. > > > > Wouldnt it be more foolproof to also loop around sending the FATAL? Not > > that its likely but... > > More foolproof and much less accurate. The Startup process doesn't know > who is holding the buffer pin that blocks it, so it could not target a > FATAL. > > > From HoldingBufferPinThatDelaysRecovery youre calling > > GetStartupBufferPinWaitBufId - that sounds a bit dangerous because that > > one is acquiring a spinlock which can also get taken at other places. > > Its not the most likely scenario, but it would certainly be annoying to > > debug. > Spinlock. It isn't held for long in any situation. What problem do you > foresee? If any backend is signalled while currently holding the ProcStructLock there is a basically unrecoverable deadlock - its not likely but possible. > > Is there any supported platform with sizeof(sig_atomic_t) <4 - I would > > doubt so? If not the locking in GetStartupBufferPinWaitBufId and > > SetStartupBufferPinWaitBufId shouldnt be needed? > I prefer spinlocking. Well, its deadlock land taking the same lock inside and outside of a signal handler... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wednesday 20 January 2010 06:30:28 Tom Lane wrote: > Andres Freund writes: > > Is there any supported platform with sizeof(sig_atomic_t) <4 - I would > > doubt so? > > Er ... what? I believe there are live platforms with sig_atomic_t = char. > If we're assuming more that's a must-fix. The reason I have asked is that the code is doing things like: /* * Used by backends when they receive a request to check for buffer pin waits. */ int GetStartupBufferPinWaitBufId(void) { int bufid; /* use volatile pointer to prevent code rearrangement */ volatile PROC_HDR *procglobal = ProcGlobal; SpinLockAcquire(ProcStructLock); bufid = procglobal->startupBufferPinWaitBufId; SpinLockRelease(ProcStructLock); return bufid; } or similar things with LWLockAcquire in a signal handler which strikes me as a not that good idea. As at least on x86 reading an integer is atomic the above spinlock is pointless. My cross arch experience is barely existing, so... Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wed, 2010-01-20 at 06:14 +0100, Andres Freund wrote: > > > > Full resolution patch attached for Startup process waits on buffer pins. > > > > Startup process sets SIGALRM when waiting on a buffer pin. If woken by > > alarm we send SIGUSR1 to all backends requesting that they check to see > > if they are blocking Startup process. If so, they throw ERROR/FATAL as > > for other conflict resolutions. Deadlock stop gap removed. > > max_standby_delay = -1 option removed to prevent deadlock. > Wouldnt it be more foolproof to also loop around sending the FATAL? Not that > its likely but... More foolproof and much less accurate. The Startup process doesn't know who is holding the buffer pin that blocks it, so it could not target a FATAL. > From HoldingBufferPinThatDelaysRecovery youre calling > GetStartupBufferPinWaitBufId - that sounds a bit dangerous because that one > is > acquiring a spinlock which can also get taken at other places. Its not the > most likely scenario, but it would certainly be annoying to debug. Spinlock. It isn't held for long in any situation. What problem do you foresee? > Is there any supported platform with sizeof(sig_atomic_t) <4 - I would doubt > so? If not the locking in GetStartupBufferPinWaitBufId and > SetStartupBufferPinWaitBufId shouldnt be needed? I prefer spinlocking. > Same issue issue (and more likely to trigger) exists with CheckStandbyTimeout- > >SendRecoveryConflictWithBufferPin->CancelDBBackends I don't see an issue. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] An example of bugs for Hot Standby
On Wednesday 20 January 2010 06:30:28 Tom Lane wrote: > Andres Freund writes: > > Is there any supported platform with sizeof(sig_atomic_t) <4 - I would > > doubt so? > > Er ... what? I believe there are live platforms with sig_atomic_t = char. > If we're assuming more that's a must-fix. So were assuming genereally that a integer cannot be read/written atomatically? Or was that only relating to the actualy signal.h typedef? Andres -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers