Re: [HACKERS] replace plugins directory with GUC
On 10.10.2012 03:45, Peter Eisentraut wrote: About that plugins directory ($libdir/plugins) ... I don't think we ever really got that to work sensibly. I don't remember the original design discussion, but I have seen a number of explanations offered over the years. It's not clear who decides what to put in there (plugin author, packager, DBA?), how to put it there (move it, copy it, symlink it? -- no support in pgxs), and based on what criteria. Yeah, it would be good to clarify that. It was originally added for the pldebugger module (http://archives.postgresql.org/pgsql-hackers/2006-07/msg00803.php), but I couldn't find any discussion on the decision to create a new 'plugins' directory. It was never quite enough for pldebugger, as you still needed to add it to shared_preload_libraries for it to work, at least if you wanted to get the full functionality. The installation procedure in the README clearly instructs to add it to shared_preload_libraries, it doesn't say anything about local_preload_libraries. I recently refactored pldebugger to not install in the plugins directory anymore, it now just drops it in $libdir. It would seem to be much more in the spirit of things to simply list the allowed plugins in a GUC variable, like some_clever_name_here = $libdir/this, $libdir/that but there is probably a reason why this wasn't done that way in the first place. I think the idea was that plugins directory would be easier for users/admins. I agree that a GUC like above feels more natural. Now that we support include-directories in postgresql.conf, you could put a "mylib.conf" file in the include directory that contains the above line, if you want to enable/disable a module just by moving things around in the filesystem (after configuring an include directory in postgresql.conf). But actually, you can't, because there's no way to append to a setting, you can only override. That's an obvious missing feature in the include mechanism. Even ignoring the plugins directory, it would be nice to be able to append libraries to shared_preload_libraries. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On Tue, Oct 09, 2012 at 09:10:13PM -0400, Robert Haas wrote: > On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus wrote: > > The second is for making deployment scripts idempotent. For example, > > say you have script A which creates table "josh", and script B which > > needs table "josh" to be empty, if present. Since the two scripts are > > tied to different database features, and you don't know which one will > > be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can > > solve that problem with DO, but why make users go to the extra effort? > > Hmm. That's an interesting point. I think we're currently in > somewhat of a limbo zone about where we ought to have IF EXISTS and IF > NOT EXISTS options, and where we should not. Really, I'd like to > figure out what policy we want to have, and then go make everything > work that way. I don't exactly know what the policy should be, but if > we don't have one then we're going to have to argue about every patch > individually, which is already getting to be more than tedious. Agreed. I, too, struggle to envision the concrete use case for TRUNCATE IF EXISTS, but adding IF [NOT] EXISTS to some marginal candidate commands would not hurt as part of a broad plan. > At > the one extreme, you have Tom, who probably would not have added any > of these given his druthers; at the other extreme, there are probably > some people who would say we ought to have this for every command in > the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR > CREATE for good measure?). I'm not sure what the right thing to do > is... but we should probably come up with some consensus position we > can all live with, and then go make this uniform[1]. For what it's worth, I'm in that camp of disfavoring all IF [NOT] EXISTS syntax. I worked on a project that fed idempotent SQL scripts through psql to migrate schema changes; I used such syntax then and appreciated the keystrokes saved. But the syntax is a bandage for raw psql input remaining a hostile environment for implementing the full range of schema changes. Switch to submitting your SQL from a richer programming environment, and these additions to core syntax cease to add much. nm -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
Robert Haas writes: > On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus wrote: >> The second is for making deployment scripts idempotent. For example, >> say you have script A which creates table "josh", and script B which >> needs table "josh" to be empty, if present. Since the two scripts are >> tied to different database features, and you don't know which one will >> be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can >> solve that problem with DO, but why make users go to the extra effort? > Hmm. That's an interesting point. I'm still not buying this as a realistic use-case. The only way TRUNCATE IF EXISTS helps script B is if B isn't going to do *anything* with table "josh" except truncate it. I will grant that there might be a case or two out there where that's just the ticket, but I think they're probably few and far between; not enough to justify bespoke syntax. As Robert already pointed out, a quick DO handles the problem well enough if you only need it once in a blue moon. I also note the lack of response to my point about IF EXISTS being squishy to the point of outright dangerous in the multi-table case. I might hold still and not complain if we didn't have the multi-table syntax. But with it, this looks a lot less like a well-considered feature and a lot more like something that was implemented because it could be done in two lines, as long as you aren't too picky about what the semantics are. TBH, I think most all of our ventures in IF(NOT)EXISTS have suffered from that disease, but that doesn't mean I'm not going to complain when we adopt the same cowboy approach to command semantics for ever thinner justifications. 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] Truncate if exists
On Tue, Oct 9, 2012 at 4:18 PM, Josh Berkus wrote: > The second is for making deployment scripts idempotent. For example, > say you have script A which creates table "josh", and script B which > needs table "josh" to be empty, if present. Since the two scripts are > tied to different database features, and you don't know which one will > be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can > solve that problem with DO, but why make users go to the extra effort? Hmm. That's an interesting point. I think we're currently in somewhat of a limbo zone about where we ought to have IF EXISTS and IF NOT EXISTS options, and where we should not. Really, I'd like to figure out what policy we want to have, and then go make everything work that way. I don't exactly know what the policy should be, but if we don't have one then we're going to have to argue about every patch individually, which is already getting to be more than tedious. At the one extreme, you have Tom, who probably would not have added any of these given his druthers; at the other extreme, there are probably some people who would say we ought to have this for every command in the book, right down to INSERT IF EXISTS (and, hey, why not INSERT OR CREATE for good measure?). I'm not sure what the right thing to do is... but we should probably come up with some consensus position we can all live with, and then go make this uniform[1]. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company [1] And yes, I will volunteer to do some or all of the required implementation work, if that's helpful. Or else somebody else can do it. That's good, too. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] pgxs problem...
I wrote: > So if I've not lost track, the scorecard is: > 1. We need to install mkldexport.sh when on AIX, so that pgxs builds can > use it. > 2. Makefile.aix has the wrong idea about where to find postgres.imp when > in pgxs mode. > 3. pljava needs -lm and isn't explicitly asking for it. > I will see about fixing the first two, but the third is on pljava to > fix. I've committed a patch for the first two things: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=bd0ef304f8a306522983f3b4b06274fdc45beed8 ... but not having an AIX machine, I can't actually test it. Would you verify it works? 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] replace plugins directory with GUC
About that plugins directory ($libdir/plugins) ... I don't think we ever really got that to work sensibly. I don't remember the original design discussion, but I have seen a number of explanations offered over the years. It's not clear who decides what to put in there (plugin author, packager, DBA?), how to put it there (move it, copy it, symlink it? -- no support in pgxs), and based on what criteria. It would seem to be much more in the spirit of things to simply list the allowed plugins in a GUC variable, like some_clever_name_here = $libdir/this, $libdir/that but there is probably a reason why this wasn't done that way in the first place. Anyway, the current setup stinks. Can we come up with something better? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bugs in CREATE/DROP INDEX CONCURRENTLY
On Tue, Oct 9, 2012 at 6:11 PM, Simon Riggs wrote: > On 6 October 2012 00:56, Tom Lane wrote: > > 1. These operations think they can use ordinary heap_update operations > > to change pg_index entries when they don't have exclusive lock on the > > parent table. The lack of ex-lock means that another backend could be > > currently loading up its list of index OIDs for the table --- and since > > it scans pg_index with SnapshotNow to do that, the heap_update could > > result in the other backend failing to see this index *at all*. That's > > okay if it causes the other backend to not use the index for scanning... > > but not okay if it causes the other backend to fail to make index > > entries it is supposed to make. > > > > I think this could possibly be fixed by using nontransactional > > update-in-place when we're trying to change indisvalid and/or > > indisready, but I've not really thought through the details. > > Only solution there is to fix SnapshotNow, as we discussed last > Christmas. I'll dig out my patch for that, which IIRC I was nervous of > committing at last minute into 9.2. > Hi Simon, Do you have an URL to this patch? -- Michael Paquier http://michael.otacoo.com
Re: [HACKERS] [GENERAL] pgxs problem...
John R Pierce writes: > Further, it appears the link command pljava is using for the AIX case is > given in its makefile as... > $(COMPILER) $(LDFLAGS_NO_L) $(LDFLAGS_SL) -o $(plugin) $< > -Wl,-bE:$(NAME)$(EXPSUFF) $(SHLIB_LINK) > I can't find anywhere LDFLAGS_NO_L is defined. however, SHLIB_LINK is > defined to concatenate PLJAVA_LDFLAGS, so I set that to -lm and POOF, > its built. scary! Well, the reason I mentioned contrib/cube is that it's known to need libm. I see in its makefile SHLIB_LINK += $(filter -lm, $(LIBS)) so apparently that's the de rigueur way to add libm when you need it. I'd suggest pestering the pljava people to do likewise. They might be getting away without this on more-forgiving platforms, but that doesn't make it good practice to omit. So if I've not lost track, the scorecard is: 1. We need to install mkldexport.sh when on AIX, so that pgxs builds can use it. 2. Makefile.aix has the wrong idea about where to find postgres.imp when in pgxs mode. 3. pljava needs -lm and isn't explicitly asking for it. I will see about fixing the first two, but the third is on pljava to fix. (These aren't new bugs BTW --- it looks to me like this has been wrong since the pgxs code was created, in 8.1. I guess we don't have many AIX users :-() 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] Truncate if exists
On Tue, Oct 9, 2012 at 2:04 PM, Simon Riggs wrote: > On 9 October 2012 21:35, Peter Eisentraut wrote: >> On 10/9/12 5:09 AM, Simon Riggs wrote: >>> Anyone want to check for any other missing IF EXISTS capability in other >>> DDL? >> >> TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is >> stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? > > I'm not involved in the planning or justification for this patch, and > have no opinion. > > I discussed applying it because it was an uncontentious patch. It > clearly is not I also read Simon's approach as not a push for inclusion, but defaulting to commit for smaller patches that basically look mechanically legitimate with no objections to streamline communication. Since pgsql-hackers has a good record objecting to patches that require objection in a timely manner, I think that's reasonable. The cost of revert would not be that high, either. Clearly those conditions were not met, but I don't think it's justified to jump on Simon for this approach on a patch like this. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On 9 October 2012 21:35, Peter Eisentraut wrote: > On 10/9/12 5:09 AM, Simon Riggs wrote: >> Anyone want to check for any other missing IF EXISTS capability in other DDL? > > TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is > stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? I'm not involved in the planning or justification for this patch, and have no opinion. I discussed applying it because it was an uncontentious patch. It clearly is not -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On 10/10/12 09:35, Peter Eisentraut wrote: On 10/9/12 5:09 AM, Simon Riggs wrote: Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? INSERT IF NOT EXISTS ? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On 10/9/12 5:09 AM, Simon Riggs wrote: > Anyone want to check for any other missing IF EXISTS capability in other DDL? TRUNCATE is not really DDL. If we allow TRUNCATE IF EXISTS, what is stopping someone from requesting DELETE IF EXISTS or INSERT IF EXISTS next? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visual Studio 2012 RC
Noah Misch wrote: I apologize for the slow drip of problem reports; I just happen to be trying additional configurations with your patch as a foundation. No problem! I'm totally aware of the fact that testing the different platforms/configurations is pretty time consuming. Actually I didn't expect so many configuration dependent problems. Otherwise I'd have done more extensive testing myself. Anyways I'll be pretty busy until this weekend so I'll probably have a look at all the problems/suggestions at once by then. Regards, Brar -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
Robert, > I've been a big proponent of adding "IF EXISTS" support to CREATE > TABLE and ALTER TABLE but I'm having a hard time getting excited about > this one. I can't imagine that many people would use it, and those > who do can implement it in about 10 lines of PL/pgsql. The existence > of DO blocks and the fact that PL/pgsql is now installed by default > have made it much more convenient to solve these kinds of problems > using those tools rather than needing dedicated syntax. That does not > mean that the most frequently used cases shouldn't have dedicated > syntax anyway, for convenience, but I'm doubtful that this falls into > that category. On the other hand, it's useful to consistently have "IF EXISTS" syntax for the majority of utility commands. It's confusing to users that they can do "DROP TABLE IF EXISTS" but not "TRUNCATE IF EXISTS", even if the latter is less useful than the former. So that's one reason to support this. The second is for making deployment scripts idempotent. For example, say you have script A which creates table "josh", and script B which needs table "josh" to be empty, if present. Since the two scripts are tied to different database features, and you don't know which one will be deployed first, it's useful to have TRUNCATE IF EXISTS. Yes, you can solve that problem with DO, but why make users go to the extra effort? Is it *as* useful as other IF EXISTS? No. Is it replaceable with a DO $$ statement? Yes. Is that a reason to block a fairly trivial patch which makes things 0.1% easier for users? No. Not if the patch itself is broken, that's another story. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On 9 October 2012 15:06, Tom Lane wrote: > Simon Riggs writes: >> On 9 October 2012 09:33, Sébastien Lardière wrote: >>> With the help of Cédric, here's a patch changing the TRUNCATE TABLE >>> command, adding the IF EXISTS option to allow the presence in the list >>> of tables of a missing or invisible table. > >> Will apply in 48 hours barring objections. > > I object: this doesn't deserve to be fast-tracked like that with no > thought about whether the semantics are actually useful or sensible. I wasn't fast tracking it, just looking to apply small uncontentious patches quickly. Your objection is enough to stall until next commitfest for further discussion. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Statistics and selectivity estimation for ranges
On Mon, Oct 1, 2012 at 3:22 AM, Jeff Davis wrote: > On Tue, 2012-09-04 at 17:27 +0400, Alexander Korotkov wrote: > > Addon patch is attached. Actually, I don't get your intention of > > introducing STATISTIC_KIND_RANGE_EMPTY_FRAC stakind. Did you plan to > > leave it as empty frac in distinct stakind or replace this stakind > > with STATISTIC_KIND_LENGTH_HISTOGRAM? In the attached > > patch STATISTIC_KIND_RANGE_EMPTY_FRAC is replaced > > with STATISTIC_KIND_LENGTH_HISTOGRAM. > > Review comments: > > 1. In compute_range_stats, you need to guard against the case where > there is no subdiff function. Perhaps default to 1.0 or something? > Let it be 1.0 without subdiff function. However, there is not so much use of this method of estimation without subdiff. But, probably it's better than nothing. 2. I think it would be helpful to add comments regarding what happens > when lengths are identical, right now it's a little confusing. For > instance, the comment: "Generate a length histogram slot entry if there > are at least two length values" doesn't seem right, because the > condition still matches even if there is only one distinct value. > I've rephrased comment. Not it's implicitly says that collected values are not necessary distinct. > 3. It looks like get_distance also needs to guard against a missing > subdiff. > Same to compute_range_stats. Let default value be 1.0. > 4. There are 3 binary search functions, which seems a little excessive: > * rbound_bsearch: greatest i such that hist[i] < v; or -1 > * rbound_bsearch_equal: greatest i such that: > hist[i] <= v and (i=0 or hist[i-1] != hist[i]); or -1 > * length_hist_bsearch: least i such that hist[i] >= v; > or length of hist > (let me know if I misunderstood the definitions) > At a minimum, we need more consistent and informative names. Also, the > definition of rbound_bsearch_equal is a little confusing because it's > looking for the highest index among distinct values, but the lowest > index among identical values. Do you see a way to refactor these to be a > little easier to understand? > Actually, goal of rbound_bsearch_equal is to find histogram bin to start interpolation from. I've renamed it to rbound_bsearch_bin and added corresponding comment. -- With best regards, Alexander Korotkov. range_stat-0.8.patch.gz Description: GNU Zip compressed 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] Truncate if exists
On Tue, Oct 9, 2012 at 12:28 PM, Sébastien Lardière wrote: >> For starters, the use-case hasn't been explained to my satisfaction. >> In what situation is it actually helpful to TRUNCATE a table that's >> not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS >> to keep from failing later in the script? If so, why not just do that >> first? > > it could be useful to not rollback transactions : > > - if a table is not yet or no more visible, because of search_path > modification I don't think I understand the case you are describing here. > - if a table was dropped, for any reason But in this case surely you could use DROP IF EXISTS. I've been a big proponent of adding "IF EXISTS" support to CREATE TABLE and ALTER TABLE but I'm having a hard time getting excited about this one. I can't imagine that many people would use it, and those who do can implement it in about 10 lines of PL/pgsql. The existence of DO blocks and the fact that PL/pgsql is now installed by default have made it much more convenient to solve these kinds of problems using those tools rather than needing dedicated syntax. That does not mean that the most frequently used cases shouldn't have dedicated syntax anyway, for convenience, but I'm doubtful that this falls into that category. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Detecting libpq connections improperly shared via fork()
On Thu, Oct 04, 2012 at 12:14:02AM +0200, Andres Freund wrote: > On Thursday, October 04, 2012 12:08:18 AM Daniel Farina wrote: > > It would be fantastic for libpq to somehow monitor use of a connection > > from multiple PIDs that share a parent and deliver an error indicating > > what is wrong. Unfortunately detecting that would require either a > > file or some kind of shared memory map, AFAIK, and I don't know how > > keen anyone is on accepting that patch. So, may I ask: how keen is > > anyone on accepting such a patch, and under what conditions of > > mechanism? > Hm. An easier version of this could just be storing the pid of the process > that did the PQconnectdb* in the PGconn struct. You can then check that > PGconn->pid == getpid() at relatively few places and error out on a mismatch. > That should be doable with only minor overhead. On system's that support it, pthread_atfork() might help. Though being like a signal handler you don't have access to the PGconn structure, so you can't flag anything easily. Maybe just to cache getpid()? In any case, adding a check to PQexec and friends would probably be sufficient, since that's what every program is going to start with. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > He who writes carelessly confesses thereby at the very outset that he does > not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Switching timeline over streaming replication
On 06.10.2012 15:58, Amit Kapila wrote: One more test seems to be failed. Apart from this, other tests are passed. 2. a. Master M-1 b. Standby S-1 follows M-1 c. insert 10 records on M-1. verify all records are visible on M-1,S-1 d. Stop S-1 e. insert 2 records on M-1. f. Stop M-1 g. Start S-1 h. Promote S-1 i. Make M-1 recovery.conf such that it should connect to S-1 j. Start M-1. Below error comes on M-1 which is expected as M-1 has more data. LOG: database system was shut down at 2012-10-05 16:45:39 IST LOG: entering standby mode LOG: consistent recovery state reached at 0/176A070 LOG: record with zero length at 0/176A070 LOG: database system is ready to accept read only connections LOG: streaming replication successfully connected to primary LOG: fetching timeline history file for timeline 2 from primary server LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions LOG: new timeline 2 forked off current database system timeline 1 before current recovery point 0/176A070 LOG: re-handshaking at position 0/100 on tli 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: walreceiver ended streaming and awaits new instructions LOG: new timeline 2 forked off current database system timeline 1 before current recovery point 0/176A070 k. Stop M-1. Start M-1. It is able to successfully connect to S-1 which is a problem. l. check in S-1. Records inserted in step-e are not present. m. Now insert records in S-1. M-1 doesn't recieve any records. On M-1 server following log is getting printed. LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 LOG: out-of-sequence timeline ID 1 (after 2) in log segment 00020001, offset 0 Hmm, seems we need to keep track of which timeline we've used to recover before. Before restart, the master correctly notices that timeline 2 forked off earlier in its history, so it cannot recover to that timeline. But after restart the master begins recovery from the previous checkpoint, and because timeline 2 forked off timeline 1 after the checkpoint, it concludes that it can follow that timeline. It doesn't realize that it had some already recovered/flushed some WAL in timeline 1 after the fork-point. Attached is a new version of the patch. I committed the refactoring of XLogPageRead() already, as that was a readability improvement even without this patch. All the reported issues should be fixed now, although I will continue testing this tomorrow. I added various checks that that the correct timeline is followed during recovery. minRecoveryPoint is now accompanied by a timeline ID, so that when we restart recovery, we check that we recover back to minRecoveryPoint along the same timeline as last time. Also, it now checks at beginning of recovery that the checkpoint record comes from the correct timeline. That fixes the problem that you reported above. I also adjusted the error messages on timeline history problems to be more clear. - Heikki streaming-tli-switch-4.patch.gz Description: GNU Zip compressed 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] Truncate if exists
On 10/09/2012 04:06 PM, Tom Lane wrote: > Simon Riggs writes: >> On 9 October 2012 09:33, Sébastien Lardière wrote: >>> With the help of Cédric, here's a patch changing the TRUNCATE TABLE >>> command, adding the IF EXISTS option to allow the presence in the list >>> of tables of a missing or invisible table. >> Will apply in 48 hours barring objections. > I object: this doesn't deserve to be fast-tracked like that with no > thought about whether the semantics are actually useful or sensible. > > For starters, the use-case hasn't been explained to my satisfaction. > In what situation is it actually helpful to TRUNCATE a table that's > not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS > to keep from failing later in the script? If so, why not just do that > first? it could be useful to not rollback transactions : - if a table is not yet or no more visible, because of search_path modification - if a table was dropped, for any reason > Second, to my mind the point of a multi-table TRUNCATE is to ensure that > all the referenced tables get reset to empty *together*. With something > like this, you'd have no such guarantee. Consider a timeline like this: > > Session 1 Session 2 > > TRUNCATE IF EXISTS a, b, c; > ... finds c doesn't exist ... > ... working on a and b ... > CREATE TABLE c ( ... ); > INSERT INTO c ...; > ... commits ... > > Now we have a, b, and c, but c isn't empty, violating the expectations > of session 1. So even if there's a use-case for IF EXISTS on a single > table, I think it's very very dubious to allow it in multi-table > commands. Well, I have to say that if I'm the guy who create the table c, I don't want to see the table empty after my insert, don't you think ? I understand your point about the multi-table TRUNCATE, but my point is to commit transaction, whatever the visibility or presence of a given table. In a perfect world, we could review all our processes, and change them to guarantee commit, then we don't need IF EXISTS ; But i'm not in this case, and maybe some others neither, are you ? -- Sébastien Lardière PostgreSQL DBA Team Manager Hi-Media -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TODO item: teach pg_dump about sparsely-stored large objects
On Tue, Oct 9, 2012 at 3:40 AM, Tom Lane wrote: > Admittedly, this is no different than what happens when you try to back > up a sparsely-stored Unix file, at least with simpler backup tools. > But it seems to me we should try a bit harder. Fwiw both GNU tar and GNU cp support creating sparse files. They do it by just detecting blocks of NULs and skipping over them. pg_restore could do that today without any API changes. That said, an API to save pg_dump the time and space of reading the fake zeros out of the database dosen't sonud like a bad thing. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add FET to Default and Europe.txt
On Mon, Oct 8, 2012 at 4:26 PM, Tom Lane wrote: > for instance IST (the Indians and the Israelis both use this, but not to > mean the same thing). And Ireland btw. So I'm not sure if this goes anywhere but could we get away with picking the timezone with the matching abbreviation closest to the system timezone? -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] DEALLOCATE IF EXISTS
On Tue, Oct 9, 2012 at 4:09 PM, Tom Lane wrote: > =?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= writes: > > Indeed, brackets was not correct, it's better now (I think), and correct > > some comments. > > Still wrong ... at the very least you missed copyfuncs/equalfuncs. > In general, when adding a field to a struct, it's good practice to > grep for all uses of that struct. > I don't see Sébastien's message, but I made the same mistake in my patch. Another one is attached with copyfuncs and equalfuncs. I did a grep for DeallocateStmt and I don't believe I have missed anything else. Also, I'm changing the subject so as not to hijack this thread any further. deallocate_if_exists.2.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] Truncate if exists
=?ISO-8859-1?Q?S=E9bastien_Lardi=E8re?= writes: > Indeed, brackets was not correct, it's better now (I think), and correct > some comments. Still wrong ... at the very least you missed copyfuncs/equalfuncs. In general, when adding a field to a struct, it's good practice to grep for all uses of that struct. 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] Behavior for crash recovery when it detects a corrupt WAL record
On 09.10.2012 16:42, Amit Kapila wrote: I have observed that currently during recovery, while it applies the WAL records even if it detects that there is a corrupt record by crc validation, it proceeds. Basically ReadRecord(), returns NULL in such cases which makes the behavior same as it has reached end of WAL. After that server get started and user can perform operations normally. Yeah. We rely on the CRC to detect end of WAL during recovery. If the system crashes while the WAL is being flushed to disk, it's normal that there's a corrupt (ie. partially written) record at the end of the WAL. This is a common technique used by pretty much every system with a transaction log / journal. The other option would be to perform two fsyncs for every commit; one to flush the WAL to disk, and another to update some global pointer to point to the end of valid WAL (e.g in pg_control). - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
Simon Riggs writes: > On 9 October 2012 09:33, Sébastien Lardière wrote: >> With the help of Cédric, here's a patch changing the TRUNCATE TABLE >> command, adding the IF EXISTS option to allow the presence in the list >> of tables of a missing or invisible table. > Will apply in 48 hours barring objections. I object: this doesn't deserve to be fast-tracked like that with no thought about whether the semantics are actually useful or sensible. For starters, the use-case hasn't been explained to my satisfaction. In what situation is it actually helpful to TRUNCATE a table that's not there yet? Aren't you going to have to do a CREATE IF NOT EXISTS to keep from failing later in the script? If so, why not just do that first? Second, to my mind the point of a multi-table TRUNCATE is to ensure that all the referenced tables get reset to empty *together*. With something like this, you'd have no such guarantee. Consider a timeline like this: Session 1 Session 2 TRUNCATE IF EXISTS a, b, c; ... finds c doesn't exist ... ... working on a and b ... CREATE TABLE c ( ... ); INSERT INTO c ...; ... commits ... Now we have a, b, and c, but c isn't empty, violating the expectations of session 1. So even if there's a use-case for IF EXISTS on a single table, I think it's very very dubious to allow it in multi-table commands. 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] Behavior for crash recovery when it detects a corrupt WAL record
I have observed that currently during recovery, while it applies the WAL records even if it detects that there is a corrupt record by crc validation, it proceeds. Basically ReadRecord(), returns NULL in such cases which makes the behavior same as it has reached end of WAL. After that server get started and user can perform operations normally. However ITSM that this is a problem as user might loose some committed data. Is there any particular reason for this behavior? With Regards, Amit Kapila.
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Tuesday, October 09, 2012 6:00 PM Robert Haas wrote: > On Mon, Oct 8, 2012 at 10:42 AM, Amit Kapila > wrote: > > How about following: > > 1. replication_client_timeout -- shouldn't it be client as new > configuration > > is for wal receiver > > 2. replication_standby_timeout > > ISTM that the client and the standby are the same thing. Yeah same, but may be one (replication_standby_timeout) can be more easily understandable by user. > > If we introduce a new parameter for wal receiver, wouldn't > > replication_timeout be confusing for user? > > Maybe. > I actually don't think that I understand what problem we're > trying to solve here. If the connection between the master and the > standby is lost, shouldn't the standby realize that it's no longer > receiving keepalives from the master and terminate the connection? For wal receiver keepalives are also like one kind of message, so the behavior is such that when it checks that it doesn't receive any message, it tries to send reply/feedback message to master after an interval of wal_receiver_status_interval. So after every wal_receiver_status_interval, wal receiver sends a reply, but still the socket send doesn't fail. It fails only after many send calls as internally might be in send(), until the sockets internal buffer is full, it keeps accumulating even if other side recv has not received the data. So that's the reason we decided to introduce a timeout parameter in wal receiver similar to what we have currently in walsender. > I > thought I had tested this at some point and it was working, so either > it's subsequently gotten broken again or the scenario you're talking > about is different in some way that I don't currently understand. Standby takes quite longer around 15 minutes to detect whereas master is able to detect quite sooner in 2-3 mins and master also mainly detects due to timeout functionality in wal sender. With Regards, Amit Kapila. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad Data back Door
I wrote: > Tom Lane wrote: Now, having said that, I think it has to be the reponsibility of the FDW to apply any required check ... which makes this a bug report against oracle_fdw, not the core system. > As the author I agree that this is a bug in oracle_fdw. Ok, fixed. David, could you try the latest version from CVS to see if it works for you? Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Placement of permissions checks for large object operations
On Mon, Oct 8, 2012 at 6:52 PM, Tom Lane wrote: > While looking at the 64-bit large object patch, I happened to notice > that the LO permissions checks that were added a release or two back > seem to be done exceedingly inefficiently. To wit, they're done in > lo_read() and lo_write(), which means that if the user say reads an 8MB > large object with an 8K buffer size, we'll repeat the same permissions > check 1000 times. > > This is particularly bizarre on the read side, where the code has gone > to probably-unreasonable lengths to make dead certain that it will get > the same answer each time. But even on the write side, it's not > apparent to me that it's useful or sensible to allow a few writes and > then stop allowing them if some other transaction commits an ACL change > meanwhile. (There would be a race condition there anyway, since our > transaction might examine the ACL just before somebody else changes it.) > > I thought about proposing that the permissions checks be done in lo_open > instead. However, that would result in semantic changes --- for > instance, it's legal and sometimes useful to supply INV_WRITE and not > actually write, if you want up-to-date read results. So we can't really > enforce permissions at open time based on the supplied flags. > > However, it wouldn't be hard to make the code apply the checks at most > once per lo_open, by keeping flags in the LargeObjectDesc entries > showing whether we've already checked read or write privilege on each > descriptor. So the check would be made only during the first lo_read > or lo_write call on a given descriptor. Seems like a good idea. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #7534: walreceiver takes long time to detect n/w breakdown
On Mon, Oct 8, 2012 at 10:42 AM, Amit Kapila wrote: > How about following: > 1. replication_client_timeout -- shouldn't it be client as new configuration > is for wal receiver > 2. replication_standby_timeout ISTM that the client and the standby are the same thing. > If we introduce a new parameter for wal receiver, wouldn't > replication_timeout be confusing for user? Maybe. I actually don't think that I understand what problem we're trying to solve here. If the connection between the master and the standby is lost, shouldn't the standby realize that it's no longer receiving keepalives from the master and terminate the connection? I thought I had tested this at some point and it was working, so either it's subsequently gotten broken again or the scenario you're talking about is different in some way that I don't currently understand. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PQntuples and PQgetvalue problem.
On Fri, Oct 5, 2012 at 9:15 AM, zafer yagmuroglu wrote: > In my C++ program, The sample code you have provided doesn't look like valid C++ to me. I wouldn't expect that to compile, let alone work. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On Tue, Oct 9, 2012 at 11:51 AM, Vik Reykja wrote: > On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs wrote: > >> Anyone want to check for any other missing IF EXISTS capability in other >> DDL? >> > > Yes, DEALLOCATE. > Patch attached. deallocate_if_exists.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] Move postgresql_fdw_validator into dblink
Hanada-san, It is fair enough for me. So, I'd like to hand over this patch for committers. Thanks, 2012/10/9 Shigeru HANADA : > (2012/10/09 0:30), Kohei KaiGai wrote: >> >> The attached patch is a revised one according to my previous >> suggestion. It re-defines "PQconninfoOption *options" as static >> variable with NULL initial value, then, PQconndefaults() shall >> be invoked at once. The default options never changed during >> duration of the backend process, so here is no reason why we >> allocate and free this object for each validator invocation. > > > Sorry for delayed response. It seems reasonable, so I just fixed obsolete > comment and indent. Please see attached v3 patch which was rebased against > latest head of master. > > >> If it is also OK for you, I'd like to take over this patch to comitter. >> This patch is prerequisite of postgresql_fdw, so I hope this patch >> getting merged soon. > > > Please go ahead. :-) > > Regards, > -- > Shigeru HANADA > -- KaiGai Kohei -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Detecting libpq connections improperly shared via fork()
On Tue, Oct 9, 2012 at 2:51 AM, Andres Freund wrote: > On Thursday, October 04, 2012 03:23:54 AM Tom Lane wrote: >> Daniel Farina writes: >> > On Wed, Oct 3, 2012 at 3:14 PM, Andres Freund > wrote: >> >> Hm. An easier version of this could just be storing the pid of the >> >> process that did the PQconnectdb* in the PGconn struct. You can then >> >> check that PGconn->pid == getpid() at relatively few places and error >> >> out on a mismatch. That should be doable with only minor overhead. >> > >> > I suppose this might needlessly eliminate someone who forks and hands >> > off the PGconn struct to exactly one child, but it's hard to argue >> > with its simplicity and portability of mechanism. >> >> Yeah, the same thing had occurred to me, but I'm not sure that getpid() >> is really cheap enough to claim that the overhead is negligible. > I guess its going to be os/libc dependant. In glibc systems getpid() should be > basically just be a function call (no syscall). To protect users who fork but then thoroughly forget about the connection in either the parent or child process, the original sketch I had in mind (which did not use getpid) would be to increment-and-check a monotonic number of some kind when protocol traffic is initiated (effectively "tell" on the socket). If that shared state is incremented in an unexpected way, then it is known that another process somewhere has mucked with the protocol state, and it's time to deliver a lucid error. That means both a shared (such as an anonymous mmap) and a not-shared (process-local as per most things when forking, or in the case of threads thread-local) state would be required. Both halves have thorny portability problems AFAIK, so I was somewhat hesitant to bring it up. However, I would like to re-iterate that this is a very common problem, so it may be worth pausing to think about solving it. Whenever someone writes in saying "what's up with these strange SSL errors", generally the first question in response is "are you using unicorn?" (for Ruby, 'gunicorn' for Python). The answer is almost invariably yes. The remainder have renegotiation issues. -- fdr -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Detecting libpq connections improperly shared via fork()
On Thursday, October 04, 2012 03:23:54 AM Tom Lane wrote: > Daniel Farina writes: > > On Wed, Oct 3, 2012 at 3:14 PM, Andres Freund wrote: > >> Hm. An easier version of this could just be storing the pid of the > >> process that did the PQconnectdb* in the PGconn struct. You can then > >> check that PGconn->pid == getpid() at relatively few places and error > >> out on a mismatch. That should be doable with only minor overhead. > > > > I suppose this might needlessly eliminate someone who forks and hands > > off the PGconn struct to exactly one child, but it's hard to argue > > with its simplicity and portability of mechanism. > > Yeah, the same thing had occurred to me, but I'm not sure that getpid() > is really cheap enough to claim that the overhead is negligible. I guess its going to be os/libc dependant. In glibc systems getpid() should be basically just be a function call (no syscall). > A bigger problem with this is that it only fixes the issue for cases in > which somebody makes new threads of control with fork(). I believe that > issues involving multiple threads trying to use the same PGconn are at > least as widespread. I'm not terribly excited about removing > functionality and adding overhead to protect against just one variant of > the problem. True. But people seem to be more wary of problems in the case of threads... We could play similar things with pthread_self() or gettid() but I am not sure how portable even the former is... Greetings, Andres -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On Tue, Oct 9, 2012 at 11:09 AM, Simon Riggs wrote: > Anyone want to check for any other missing IF EXISTS capability in other > DDL? > Yes, DEALLOCATE.
Re: [HACKERS] Truncate if exists
On 10/09/2012 11:09 AM, Simon Riggs wrote:
> On 9 October 2012 09:33, Sébastien Lardière wrote:
>
>> With the help of Cédric, here's a patch changing the TRUNCATE TABLE
>> command, adding the IF EXISTS option to allow the presence in the list
>> of tables of a missing or invisible table.
>>
>> This meets the needs of scripts that should be run in different stages,
>> and do not always have the same visibility on the tables, as well as
>> DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we
>> prefer to ignore the absence of the table.
>>
>> It is a small patch which changes very little code, but that could be
>> quite useful.
> Agreed.
>
> Patch looks fine, but please observe the coding standards wrt nested brackets.
>
> Will apply in 48 hours barring objections.
>
> Anyone want to check for any other missing IF EXISTS capability in other DDL?
>
Indeed, brackets was not correct, it's better now (I think), and correct
some comments.
Thanks,
--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media
diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml
index 7b9c2f3..8af9f0b 100644
--- a/doc/src/sgml/ref/truncate.sgml
+++ b/doc/src/sgml/ref/truncate.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
-TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
+TRUNCATE [ TABLE ] [ IF EXISTS ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
@@ -44,6 +44,16 @@ TRUNCATE [ TABLE ] [ ONLY ] name [
+IF EXISTS
+
+
+ Do not throw an error if a table does not exist. A notice is issued
+ in this case.
+
+
+
+
+
name
@@ -222,7 +232,8 @@ TRUNCATE othertable CASCADE;
also appear in that standard, but have slightly different though related
meanings. Some of the concurrency behavior of this command is left
implementation-defined by the standard, so the above notes should be
- considered and compared with other implementations if necessary.
+ considered and compared with other implementations if necessary. The
+ IF EXISTS option is a PostgreSQL extension.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 359d478..cb08bac 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -948,6 +948,9 @@ ExecuteTruncate(TruncateStmt *stmt)
ResultRelInfo *resultRelInfo;
SubTransactionId mySubid;
ListCell *cell;
+ ListCell *prev;
+
+ prev = NULL;
/*
* Open, exclusive-lock, and check all the explicitly-specified relations
@@ -959,7 +962,23 @@ ExecuteTruncate(TruncateStmt *stmt)
bool recurse = interpretInhOption(rv->inhOpt);
Oid myrelid;
- rel = heap_openrv(rv, AccessExclusiveLock);
+ rel = heap_openrv_extended(rv, AccessExclusiveLock, stmt->missing_ok);
+ /* if oid is not valid
+ * NOTICE with table name
+ * then invalid oid in the list
+ */
+ if(rel == NULL)
+ {
+ ereport(NOTICE,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("relation %s does not exist", rv->relname )
+ )
+ );
+ list_delete_cell(stmt->relations, cell, prev) ;
+ continue;
+ }
+ prev = cell;
+
myrelid = RelationGetRelid(rel);
/* don't throw error for "TRUNCATE foo, foo" */
if (list_member_oid(relids, myrelid))
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e4ff76e..f6a03f3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5065,6 +5065,16 @@ TruncateStmt:
n->relations = $3;
n->restart_seqs = $4;
n->behavior = $5;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+}
+| TRUNCATE opt_table IF_P EXISTS relation_expr_list opt_restart_seqs opt_drop_behavior
+{
+ TruncateStmt *n = makeNode(TruncateStmt);
+ n->relations = $5;
+ n->restart_seqs = $6;
+ n->behavior = $7;
+ n->missing_ok = true;
$$ = (Node *)n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 09b15e7..814b129 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1954,6 +1954,7 @@ typedef struct TruncateStmt
List *relations; /* relations (RangeVars) to be truncated */
bool restart_seqs; /* restart owned sequences? */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if object is missing? */
} TruncateStmt;
/* --
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 5c5277e..839104d 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -420,3 +420,13 @@ SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
ERROR: relation "truncate_a_id1" does not exist
LINE 1: SELECT nextval('truncate_a_id1');
^
+-- test IF EXISTS
+CREATE TABLE truncate_a(id int);
+INSERT INTO tru
Re: [HACKERS] Bugs in CREATE/DROP INDEX CONCURRENTLY
On 6 October 2012 00:56, Tom Lane wrote: > 1. These operations think they can use ordinary heap_update operations > to change pg_index entries when they don't have exclusive lock on the > parent table. The lack of ex-lock means that another backend could be > currently loading up its list of index OIDs for the table --- and since > it scans pg_index with SnapshotNow to do that, the heap_update could > result in the other backend failing to see this index *at all*. That's > okay if it causes the other backend to not use the index for scanning... > but not okay if it causes the other backend to fail to make index > entries it is supposed to make. > > I think this could possibly be fixed by using nontransactional > update-in-place when we're trying to change indisvalid and/or > indisready, but I've not really thought through the details. Only solution there is to fix SnapshotNow, as we discussed last Christmas. I'll dig out my patch for that, which IIRC I was nervous of committing at last minute into 9.2. > 2. DROP INDEX CONCURRENTLY doesn't bother to do > TransferPredicateLocksToHeapRelation until long after it's invalidated > the index. Surely that's no good? Is it even possible to do that > correctly, when we don't have a lock that will prevent new predicate > locks from being taken out meanwhile? No idea there. Input appreciated. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Truncate if exists
On 9 October 2012 09:33, Sébastien Lardière wrote: > With the help of Cédric, here's a patch changing the TRUNCATE TABLE > command, adding the IF EXISTS option to allow the presence in the list > of tables of a missing or invisible table. > > This meets the needs of scripts that should be run in different stages, > and do not always have the same visibility on the tables, as well as > DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we > prefer to ignore the absence of the table. > > It is a small patch which changes very little code, but that could be > quite useful. Agreed. Patch looks fine, but please observe the coding standards wrt nested brackets. Will apply in 48 hours barring objections. Anyone want to check for any other missing IF EXISTS capability in other DDL? -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Move postgresql_fdw_validator into dblink
(2012/10/09 0:30), Kohei KaiGai wrote: The attached patch is a revised one according to my previous suggestion. It re-defines "PQconninfoOption *options" as static variable with NULL initial value, then, PQconndefaults() shall be invoked at once. The default options never changed during duration of the backend process, so here is no reason why we allocate and free this object for each validator invocation. Sorry for delayed response. It seems reasonable, so I just fixed obsolete comment and indent. Please see attached v3 patch which was rebased against latest head of master. If it is also OK for you, I'd like to take over this patch to comitter. This patch is prerequisite of postgresql_fdw, so I hope this patch getting merged soon. Please go ahead. :-) Regards, -- Shigeru HANADA diff --git a/contrib/dblink/Makefile b/contrib/dblink/Makefile index ac63748..a27db88 100644 --- a/contrib/dblink/Makefile +++ b/contrib/dblink/Makefile @@ -7,7 +7,7 @@ SHLIB_LINK = $(libpq) SHLIB_PREREQS = submake-libpq EXTENSION = dblink -DATA = dblink--1.0.sql dblink--unpackaged--1.0.sql +DATA = dblink--1.1.sql dblink--1.0--1.1.sql dblink--unpackaged--1.0.sql REGRESS = dblink diff --git a/contrib/dblink/dblink--1.0--1.1.sql b/contrib/dblink/dblink--1.0--1.1.sql new file mode 100644 index 000..f224d3d --- /dev/null +++ b/contrib/dblink/dblink--1.0--1.1.sql @@ -0,0 +1,14 @@ +/* contrib/dblink/dblink--1.0--1.1.sql */ + +-- complain if script is sourced in psql, rather than via CREATE EXTENSION +\echo Use "ALTER EXTENSION dblink UPDATE TO '1.1'" to load this file. \quit + +CREATE FUNCTION dblink_fdw_validator( +options text[], +catalog oid +) +RETURNS void +AS 'MODULE_PATHNAME', 'dblink_fdw_validator' +LANGUAGE C IMMUTABLE; + +CREATE FOREIGN DATA WRAPPER dblink_fdw VALIDATOR dblink_fdw_validator; diff --git a/contrib/dblink/dblink--1.0.sql b/contrib/dblink/dblink--1.0.sql deleted file mode 100644 index 1fec9e3..000 --- a/contrib/dblink/dblink--1.0.sql +++ /dev/null @@ -1,223 +0,0 @@ -/* contrib/dblink/dblink--1.0.sql */ - --- complain if script is sourced in psql, rather than via CREATE EXTENSION -\echo Use "CREATE EXTENSION dblink" to load this file. \quit - --- dblink_connect now restricts non-superusers to password --- authenticated connections -CREATE FUNCTION dblink_connect (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_connect (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT; - --- dblink_connect_u allows non-superusers to use --- non-password authenticated connections, but initially --- privileges are revoked from public -CREATE FUNCTION dblink_connect_u (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT SECURITY DEFINER; - -CREATE FUNCTION dblink_connect_u (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_connect' -LANGUAGE C STRICT SECURITY DEFINER; - -REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public; -REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public; - -CREATE FUNCTION dblink_disconnect () -RETURNS text -AS 'MODULE_PATHNAME','dblink_disconnect' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_disconnect (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_disconnect' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_open (text, text, text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_open' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, int) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, int, boolean) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, text, int) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_fetch (text, text, int, boolean) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_fetch' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text, text) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink_close (text, text, boolean) -RETURNS text -AS 'MODULE_PATHNAME','dblink_close' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink (text, text) -RETURNS setof record -AS 'MODULE_PATHNAME','dblink_record' -LANGUAGE C STRICT; - -CREATE FUNCTION dblink (text, text, boolean) -RETURNS setof record -AS 'MODULE_P
[HACKERS] Truncate if exists
Hi,
With the help of Cédric, here's a patch changing the TRUNCATE TABLE
command, adding the IF EXISTS option to allow the presence in the list
of tables of a missing or invisible table.
This meets the needs of scripts that should be run in different stages,
and do not always have the same visibility on the tables, as well as
DROP TABLE. Rather than rollback the entire TRUNCATE or transaction, we
prefer to ignore the absence of the table.
It is a small patch which changes very little code, but that could be
quite useful.
Regards,
--
Sébastien Lardière
PostgreSQL DBA Team Manager
Hi-Media
diff --git a/doc/src/sgml/ref/truncate.sgml b/doc/src/sgml/ref/truncate.sgml
index 7b9c2f3..8af9f0b 100644
--- a/doc/src/sgml/ref/truncate.sgml
+++ b/doc/src/sgml/ref/truncate.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
-TRUNCATE [ TABLE ] [ ONLY ] name [ * ] [, ... ]
+TRUNCATE [ TABLE ] [ IF EXISTS ] [ ONLY ] name [ * ] [, ... ]
[ RESTART IDENTITY | CONTINUE IDENTITY ] [ CASCADE | RESTRICT ]
@@ -44,6 +44,16 @@ TRUNCATE [ TABLE ] [ ONLY ] name [
+IF EXISTS
+
+
+ Do not throw an error if a table does not exist. A notice is issued
+ in this case.
+
+
+
+
+
name
@@ -222,7 +232,8 @@ TRUNCATE othertable CASCADE;
also appear in that standard, but have slightly different though related
meanings. Some of the concurrency behavior of this command is left
implementation-defined by the standard, so the above notes should be
- considered and compared with other implementations if necessary.
+ considered and compared with other implementations if necessary. The
+ IF EXISTS option is a PostgreSQL extension.
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 359d478..f48dbf8 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -948,6 +948,9 @@ ExecuteTruncate(TruncateStmt *stmt)
ResultRelInfo *resultRelInfo;
SubTransactionId mySubid;
ListCell *cell;
+ ListCell *prev;
+
+ prev = NULL;
/*
* Open, exclusive-lock, and check all the explicitly-specified relations
@@ -959,7 +962,22 @@ ExecuteTruncate(TruncateStmt *stmt)
bool recurse = interpretInhOption(rv->inhOpt);
Oid myrelid;
- rel = heap_openrv(rv, AccessExclusiveLock);
+ rel = heap_openrv_extended(rv, AccessExclusiveLock, stmt->missing_ok);
+ /* TODO : if oid is not valid
+ * NOTICE with table name
+ * invalid oid in the list
+ */
+ if(rel == NULL) {
+ ereport(NOTICE,
+ (errcode(ERRCODE_UNDEFINED_TABLE),
+ errmsg("relation %s does not exist", rv->relname )
+ )
+ );
+ list_delete_cell(stmt->relations, cell, prev) ;
+ continue;
+ }
+ prev = cell;
+
myrelid = RelationGetRelid(rel);
/* don't throw error for "TRUNCATE foo, foo" */
if (list_member_oid(relids, myrelid))
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index e4ff76e..f6a03f3 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -5065,6 +5065,16 @@ TruncateStmt:
n->relations = $3;
n->restart_seqs = $4;
n->behavior = $5;
+ n->missing_ok = false;
+ $$ = (Node *)n;
+}
+| TRUNCATE opt_table IF_P EXISTS relation_expr_list opt_restart_seqs opt_drop_behavior
+{
+ TruncateStmt *n = makeNode(TruncateStmt);
+ n->relations = $5;
+ n->restart_seqs = $6;
+ n->behavior = $7;
+ n->missing_ok = true;
$$ = (Node *)n;
}
;
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 09b15e7..814b129 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1954,6 +1954,7 @@ typedef struct TruncateStmt
List *relations; /* relations (RangeVars) to be truncated */
bool restart_seqs; /* restart owned sequences? */
DropBehavior behavior; /* RESTRICT or CASCADE behavior */
+ bool missing_ok; /* skip error if object is missing? */
} TruncateStmt;
/* --
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 5c5277e..839104d 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -420,3 +420,13 @@ SELECT nextval('truncate_a_id1'); -- fail, seq should have been dropped
ERROR: relation "truncate_a_id1" does not exist
LINE 1: SELECT nextval('truncate_a_id1');
^
+-- test IF EXISTS
+CREATE TABLE truncate_a(id int);
+INSERT INTO truncate_a VALUES ( 1 );
+TRUNCATE TABLE IF EXISTS truncate_a ;
+INSERT INTO truncate_a VALUES ( 1 );
+TRUNCATE TABLE IF EXISTS truncate_a, truncate_b ;
+NOTICE: relation truncate_b does not exist
+DROP TABLE truncate_a ;
+TRUNCATE TABLE IF EXISTS truncate_a ;
+NOTICE: relation truncate_a does not exist
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index a3d6f53..ed39360 100644
--- a/src/test/regress/s
Re: [HACKERS] Deparsing DDL command strings
Jim Nasby writes: > I definitely want to be able to parse DDL commands to be able to > either enforce things or to drive other parts of the system based on > what's changing. Without the ability to capture (and parse) DDL > commands I'm stuck creating wrapper functions around anything I want > to capture and then trying to ensure that everyone uses the wrappers > and not the raw DDL commands. Are you mainly working on some Auditing system? > Event triggers that just spit out raw SQL give me the first part of > this, but not the second part: I'm still stuck trying to parse raw SQL > on my own. Having normalized SQL to parse should make that a bit > easier, but ideally I'd like to be able to pull specific elements out > of a command. I'd want to be able to do things like: The current design for event triggers is to spit out several things: - command tag is already commited - object id, can be null - schema name, can be null - object name - operationeither ALTER, CREATE or DROP, … - object type TABLE, VIEW, FUNCTION, … - normalized command string After some more thinking, it appears that in several case you want to have all those information filled in and you don't want to care if that means your trigger needs to run at ddl_command_start or ddl_command_end. The proposal I want to make here is to introduce a generic event (or an event alias) named ddl_command_trace that the system provides at the right spot where you have the information. That's useful when you don't intend to divert the execution of the DDL and need to know all about it. For a DROP operation, ddl_command_trace would be ddl_command_start, and for a CREATE operation, that would be ddl_command_end, so that the target object (still|already) exists when the trigger is fired. > IF command is ALTER TABLE THEN That's called TG_TAG, see http://www.postgresql.org/docs/devel/static/plpgsql-trigger.html#PLPGSQL-EVENT-TRIGGER > FOR EACH subcommand > IF subcommand IS DROP COLUMN THEN > do something that needs to know what column is being dropped > ELSE IF subcommand IS ADD COLUMN THEN > do something that needs to know the definition of the column being added We decided not to publish any notion of subcommand at this stage. > I don't think every bit of that has to be dealt with by the event > trigger code itself. For example, if you're adding a column to a table > and the entries have already been made in the catalog, you could query > to get the details of the column definition if you were given an OID > into pg_attributes. It's easy enough to provide the OID of the newly created main command target object, it's harder to provide in a generic way all the OID of the objects you might be interested into, because each command has its own set of such. DROP can target multiple objects, they all are the main target. ALTER target only a single object, but can link to dependent objects. CREATE an operator class or a cast and you're talking about a bunch of operators and functions to tie together. It's not that easy. > Having said all that, an event system that spits back the raw SQL > would certainly be better than nothing. But realize that people would > still need to do parsing on it (ie: replication solutions will need to > know what table just got ALTER'd). You would have most of what you're asking. I think that looking into the normalized string to get the information you need when you already know you're looking at an ALTER TABLE statement and you already have the object references (schema, name, oid) is going to make things easier. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] sortsupport for text
Peter Geoghegan writes: > On 8 October 2012 21:35, Robert Haas wrote: Gentlemen, you can't fight here. This is the War Room. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Bad Data back Door
David E. Wheeler wrote: >> As the author I agree that this is a bug in oracle_fdw. > Thanks. Should I file a report somewhere? That's not necessary. Thanks for reporting the problem. It may be a few days until I get around to fix that. >> Oracle does not care much about correct encoding. > Yeah, same here. I've been looking into write a function to try > to fix poorly-encoded data, though, but haven't got far, > because CONVERT() does not indicate failure. If you have > any insight on this, I'd appreciate your thoughts on this > Stack Overflow question: > > http://stackoverflow.com/q/12717363/79202 The only thing I can think of is a stored procedure in Java. You could use java.nio.charset.CharsetEncoder and java.nio.charset.CharsetDecoder which should throw exceptions if they encounter illegal bytes. Yours, Laurenz Albe -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
