[HACKERS] Buffering GiST leaf pages too
While looking at Alexander's GiST fastbuild patch, which adds buffers to internal nodes to avoid random I/O during index build, it occurred to me that inserting the tuples to the leaf pages one at a time is quite inefficient too, even if the leaf pages are in cache. There's still the overhead of locking and WAL-logging each insertion separately. I think we could get a nice further speedup if we attach a small buffer (one block or so) to every leaf page we're currently writing tuples to, and update the leaf page in bulk. Conveniently, the code to insert multiple tuples to a page already exists in GiST code (because inserting a tuple sometimes splits the page into more than two parts, so you need to insert multiple downlinks to the parent), so this requires no changes to the low-level routines and WAL-logging. Let's finish off the main fastbuild patch first, but I wanted to get the idea out 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] OperationalError: FATAL: lock AccessShareLock on object 0/1260/0 is already
On Sun, Aug 14, 2011 at 12:16:39AM -0400, Robert Haas wrote:
> On Fri, Aug 12, 2011 at 7:19 PM, daveg wrote:
> > This seems to be bug month for my client. Now there are seeing periods
> > where all new connections fail immediately with the error:
> >
> > FATAL: lock AccessShareLock on object 0/1260/0 is already held
...
> > What can I do to help track this down?
>
> I've seen that error (though not that exact fact pattern) caused by
> bad RAM. It's unclear to me what else could cause it.
>
> In terms of debugging, it seems like it might be sensible to start by
> injecting some debugging code that dumps out the contents of the LOCK
> and LOCALLOCK structures at the point the error occurs.
I've made up the attached patch to print this, please suggest any additions.
I'll deploy this on a couple of the production hosts that have had the
issue this evening, but there is no telling when or if it will strike next.
-dg
--
David Gould da...@sonic.net 510 536 1443510 282 0869
If simplicity worked, the world would be overrun with insects.
*** postgresql-9.0.4/src/backend/storage/lmgr/lock.c.orig 2011-08-22
13:46:29.386428943 -0700
--- postgresql-9.0.4/src/backend/storage/lmgr/lock.c2011-08-23
00:12:43.456422966 -0700
***
*** 469,474
--- 469,534
return LockAcquireExtended(locktag, lockmode, sessionLock, dontWait,
true);
}
+ int DG_FORCE_TRAP = 0;
+
+ inline static void
+ DG_LOCK_PRINT(const char *where, const LOCK *lock, LOCKMODE type)
+ {
+ elog(LOG,
+"%s: lock(%p) id(%u,%u,%u,%u,%u,%u) grantMask(%x) "
+"req(%d,%d,%d,%d,%d,%d,%d)=%d "
+"grant(%d,%d,%d,%d,%d,%d,%d)=%d wait(%d) type(%s)",
+where, lock,
+lock->tag.locktag_field1, lock->tag.locktag_field2,
+lock->tag.locktag_field3, lock->tag.locktag_field4,
+lock->tag.locktag_type, lock->tag.locktag_lockmethodid,
+lock->grantMask,
+lock->requested[1], lock->requested[2], lock->requested[3],
+lock->requested[4], lock->requested[5], lock->requested[6],
+lock->requested[7], lock->nRequested,
+lock->granted[1], lock->granted[2], lock->granted[3],
+lock->granted[4], lock->granted[5], lock->granted[6],
+lock->granted[7], lock->nGranted,
+lock->waitProcs.size,
+LockMethods[LOCK_LOCKMETHOD(*lock)]->lockModeNames[type]);
+ }
+
+
+ inline static void
+ DG_PROCLOCK_PRINT(const char *where, const PROCLOCK *proclockP)
+ {
+ elog(LOG,
+"%s: proclock(%p) lock(%p) method(%u) proc(%p) "
+ "hold(%x) release(%x) "
+ "links(p,n): lock=(%p,%p), proc=(%p,%p)",
+where, proclockP, proclockP->tag.myLock,
+PROCLOCK_LOCKMETHOD(*(proclockP)),
+proclockP->tag.myProc,
+(int) proclockP->holdMask, (int) proclockP->releaseMask,
+proclockP->lockLink.prev, proclockP->lockLink.next,
+proclockP->procLink.prev, proclockP->procLink.next
+ );
+ }
+
+ inline static void
+ DG_LOCALLOCK_PRINT(const char *where, const LOCALLOCK *localP)
+ {
+ elog(LOG,
+"%s: locallock(%p) id(%u,%u,%u,%u,%u,%u mode %x) "
+"lock(%p), proclock(%p) "
+"hashcode %x nlocks %ld numLockOwners %d maxLockOwners %d ",
+where, localP,
+localP->tag.lock.locktag_field1,
localP->tag.lock.locktag_field2,
+localP->tag.lock.locktag_field3,
localP->tag.lock.locktag_field4,
+localP->tag.lock.locktag_type,
localP->tag.lock.locktag_lockmethodid,
+localP->tag.mode,
+localP->lock, localP->proclock,
+localP->hashcode, localP->nLocks,
+localP->numLockOwners, localP->maxLockOwners
+/* localP->lockOwners[0].owner, localP->lockOwners.nlocks "%p
%d" */
+ );
+ }
+
/*
* LockAcquireExtended - allows us to specify additional options
*
***
*** 500,505
--- 560,568
LWLockIdpartitionLock;
int status;
boollog_lock = false;
+ int DG_found_local = -1;
+ int DG_found_lock = -1;
+ int DG_found_proc = -1;
if (lockmethodid <= 0 || lockmethodid >= lengthof(LockMethods))
elog(ERROR, "unrecognized lock method: %d", lockmethodid);
***
*** 540,546
locallock = (LOCALLOCK *) hash_search(LockMethodLocalHash,
(void *) &localtag,
HASH_ENTER, &found);
!
/*
* if it's a new locallock objec
Re: [HACKERS] VIP: plpgsql - early embedded sql plan preparation
On Tue, Aug 23, 2011 at 6:30 AM, Pavel Stehule wrote: > Hello > > I am returning back to discus two months ago about usage of > plpgsql_lint. I integrated this module to plpgsql's core. This feature > is controlled via plpgsql GUC variable prepare_plans and via plpgsql > option. It works with all plpgsql's regress tests without tests where > dynamic sql or refcursors are used. Early plan preparation is disabled > default so it should not to break current applications. > > This feature can live as contrib module too, but integration has one > advantage - there is still a possibility to use other plpgsql > extensions - mainly plpgsql debugger. > > I didn't work on documentation yet, so I there is small example: > > CREATE TYPE tp AS (a int, b int); > > CREATE OR REPLACE FUNCTION test() > RETURNS int AS $$ > DECLARE v tp; > BEGIN > v := (10,20); > IF false THEN > RAISE NOTICE '%', v.z; > END IF; > RETURN v.a; > END; > $$ LANGUAGE plpgsql; > > postgres=# SELECT test(); > test > -- > 10 > (1 row) > > with enabled early planning it found a bug in not executed code > > CREATE OR REPLACE FUNCTION public.test() > RETURNS integer > LANGUAGE plpgsql > AS $function$ > #prepare_plans on_start > DECLARE v tp; > BEGIN > v := (10,20); > IF false THEN > RAISE NOTICE '%', v.z; > END IF; > RETURN v.a; > END; > $function$ > > postgres=# select test(); > ERROR: record "v" has no field "z" > LINE 1: SELECT v.z > ^ > QUERY: SELECT v.z > CONTEXT: PL/pgSQL function "test" line 7 at RAISE > > you can set GUC > > postgres=# set plpgsql.prepare_plans to on_start; > SET > > and you can overwrite this global setting with directive > #prepare_plans on_demand > > CREATE OR REPLACE FUNCTION public.test() > RETURNS integer > LANGUAGE plpgsql > AS $function$ > #prepare_plans on_demand > DECLARE v tp; > BEGIN > v := (10,20); > IF false THEN > RAISE NOTICE '%', v.z; > END IF; > RETURN v.a; > END; > $function$ > > Regards > > Pavel Stehule Is it possible to raise warnings at function creation time? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] VIP: plpgsql - early embedded sql plan preparation
> > Is it possible to raise warnings at function creation time? > when this code will be in core, then it is possible without one significant exceptions - triggers :( I am not able to specify a target table - and real type for NEW and OLD records. Regards Pavel > merlin > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PushActiveSnapshot(GetTransactionSnapshot())
On Mon, Aug 22, 2011 at 7:07 PM, Alvaro Herrera wrote: > Excerpts from Simon Riggs's message of dom ago 21 16:23:39 -0300 2011: >> In common cases of snapshot use we run GetSnapshotData() into a >> statically allocated snapshot, then immediately copy the static struct >> into a dynamically allocated copy. >> >> The static allocation was designed to remove the overhead of dynamic >> allocation, but then we do it anyway. >> >> The snapmgr code does this explicitly, but the reason isn't >> documented, it just says we must do this. > > IIRC the active snapshot is scribbled onto by some operations, which is > why the copy is mandatory. Maybe there's some way to optimize things so > that the copy is done only when necessary. IIRC the copying of the > ActiveSnapshot was only introduced because some subtle bugs were > detected in the code without copy. When I introduced the mandatory > copy, I don't remember thinking about the statically allocated struct. "Some operations", "subtle bugs". Do you have any further information on those? -- 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] PushActiveSnapshot(GetTransactionSnapshot())
Excerpts from Simon Riggs's message of mar ago 23 10:56:17 -0300 2011: > On Mon, Aug 22, 2011 at 7:07 PM, Alvaro Herrera > wrote: > > Excerpts from Simon Riggs's message of dom ago 21 16:23:39 -0300 2011: > >> In common cases of snapshot use we run GetSnapshotData() into a > >> statically allocated snapshot, then immediately copy the static struct > >> into a dynamically allocated copy. > >> > >> The static allocation was designed to remove the overhead of dynamic > >> allocation, but then we do it anyway. > >> > >> The snapmgr code does this explicitly, but the reason isn't > >> documented, it just says we must do this. > > > > IIRC the active snapshot is scribbled onto by some operations, which is > > why the copy is mandatory. Maybe there's some way to optimize things so > > that the copy is done only when necessary. IIRC the copying of the > > ActiveSnapshot was only introduced because some subtle bugs were > > detected in the code without copy. When I introduced the mandatory > > copy, I don't remember thinking about the statically allocated struct. > > "Some operations", "subtle bugs". > > Do you have any further information on those? See commits 07cefdfb7a1c1a7ae96783c9723102250a4c3bad and caa4cfa3697472a6673eb817eb34681684cba14f -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Deferred Snapshots
Back in 2009, I proposed the idea of Lazy Snapshots. The idea was to put off generating a snapshot until we need one. I have further ideas on this now. In general, this was a bad idea because... On Tue, 2009-08-18 at 10:18 -0400, Tom Lane wrote: > Simon, this concept is completely broken, as far as I can tell. > Consider this example: > > * You scan some row R1 with an ancient XMIN and no XMAX. > You decide it's visible. > * Transaction T2 deletes R1, inserts R2, and commits. > * You scan R2. Since it has a recent XMIN, you now take > a snapshot. The snapshot says T2 committed. So you > consider R2 is visible. This is inconsistent. In the > worst case R2 might be an update of R1. > > Even if the idea gave self-consistent results, I don't agree that it's > okay to not know when the statement's snapshot will be taken. Timing > of snapshots relative to other actions is critical for many reasons. which still I believe to be correct. However, that does not exclude a number of cases where the idea is still meaningful. 1. When we access all-visible data blocks. 2. When we perform a SELECT that accesses data using a unique index and we only examine a single row and that row older than our RecentXmin. 3. When we perform an UPDATE that accesses data using a unique index and we only examine a single row. In this case, we identify the row version and then re-evaluate it if it has been updated after our snapshot. It seems cheaper to just leave the snapshot open and find the latest committed version of the row and then update that, skipping all the PlanQual stuff. Those areas are very common *and* there is a strong correspondence with the workloads where cost of snapshots is a limiting factor. Does anybody see a reason to not investigate that further? Thanks, -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cheaper snapshots redux
On Mon, Aug 22, 2011 at 10:25 PM, Robert Haas wrote: > I've been giving this quite a bit more thought, and have decided to > abandon the scheme described above, at least for now. I liked your goal of O(1) snapshots and think you should go for that. I didn't realise you were still working on this, and had some thoughts at the weekend which I recorded just now. Different tack entirely. > Heikki has made the suggestion a few times (and a few other people > have since made somewhat similar suggestions in different words) of > keeping an-up-to-date snapshot in shared memory such that transactions > that need a snapshot can simply copy it. I've since noted that in Hot > Standby mode, that's more or less what the KnownAssignedXids stuff > already does. I objected that, first, the overhead of updating the > snapshot for every commit would be too great, and second, it didn't > seem to do a whole lot to reduce the size of the critical section, and > therefore probably wouldn't improve performance that much. But I'm > coming around to the view that these might be solvable problems rather > than reasons to give up on the idea altogether. Sounds easy enough to just link up KnownAssignedXids and see... -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Getting rid of pg_pltemplate
One of my goals for the extensions feature has been that we should be able to get rid of the pg_pltemplate system catalog, moving all the information therein into languages' extension definition files. This would allow third-party procedural languages to be installed as easily as built-in ones. We failed to get this done in 9.1, mostly because we couldn't work out what to do about tmpldbacreate (the feature to allow non-superuser database owners to create "safe" languages). Here's a proposal for that. We'll add a new boolean parameter to extension control files, called say "dba_create" (ideas for better names welcome). If it's missing or set to false, there's no change in behavior. When it's true, then (a) you must be superuser or owner of the current database to create the extension; (b) the commands within the extension's script will be run as though by a superuser, even if you aren't one. My initial thought about how to actually implement (b) was to hot-wire superuser() so that it would return true while executing such a script. However, the end result of that would be that the extension's contained objects would be owned by the non-superuser DBA, and I'm not sure that's a good idea. I seem to recall some discussions about how the SQL owner of, say, a C-language function could use ALTER FUNCTION on it in ways that could open security holes. So it might be a better idea to execute the script as though we'd temporarily done SET ROLE to the bootstrap superuser, so that the objects end up owned by that user. (Of course, we'd only need to do that when the calling user isn't already a superuser, else he might as well own the objects.) Presumably, a dba_create extension could also be dropped by a non-superuser DBA. We could either inspect the extension control file again when deciding whether to allow DROP EXTENSION, or copy the flag into a new column in pg_extension so that the installed extension doesn't rely on having the control file still around. Probably the latter is a better idea. The above mechanism could be applied to any sort of extension, not just procedural language ones, and would be useful for ones involving C-language functions (which is most). But it's not my purpose at the moment to open a debate about whether any of our existing contrib modules ought to get marked as dba_create. For the moment I'm just considering the procedural languages. (In essence, if a database administrator allows a dba_create extension to be installed in his extensions directory, he's certifying that he trusts that extension enough to allow it to be installed by non-superusers. This is not just a matter of whether the extension itself is safe, but whether the installation script could conceivably be subverted by running it in a malicious SQL environment. I'd just as soon start out with assuming that only for the PL extensions, which need do nothing except a couple of CREATE FUNCTION commands and then CREATE LANGUAGE.) Once we have the above mechanism, we'd redefine CREATE LANGUAGE thusly: 1. CREATE LANGUAGE with no parameters becomes just a deprecated synonym for CREATE EXTENSION, ie, we turn it into a request to create the extension of the same name. This is mostly to allow loading of old dump files. 2. CREATE LANGUAGE with parameters is expected to be used in extension definition files. It becomes a superuser-only command (with the dba_create override of course), and must specify all properties of the language. The existing weird rules about overriding the specified parameters with data from pg_pltemplate go away. 3. Likewise, we get rid of the weird rules in pg_dump about when to provide parameters for CREATE LANGUAGE. If we're dumping a language definition at all, dump it with full parameters. Having done that, we'd mark all the standard "trusted" PLs as dba_create, expand the existing definition scripts for the PL extensions so that they fully specify the languages and their support functions (transferring all that knowledge from the current contents of pg_pltemplate), and then remove pg_pltemplate. Now, the reason we invented pg_pltemplate in the first place was to solve problems with updating procedural language definitions from one release to the next. Essentially, if we do this, we're making a bet that the extensions feature provides a more complete, better-thought-out update mechanism than pg_pltemplate itself. I think that's probably right, especially when thinking about it from the standpoint of a non-core PL; but it's worth pointing out that we are taking some risk of having to do more work than before. For example, if we wanted to add another type of support function to PLs in the future, this approach would mean having to add an ALTER LANGUAGE command for the PLs' update scripts to use to add that function to an existing PL. Otherwise we could not support binary-upgrade scenarios. Comments? regards, tom lane -- Sent via pgsql-hackers mailing
Re: [HACKERS] Deferred Snapshots
Simon Riggs writes: > Back in 2009, I proposed the idea of Lazy Snapshots. > The idea was to put off generating a snapshot until we need one. It was a broken idea then, and it has not become less so with the passage of time. > However, that does not exclude a number of cases where the idea is > still meaningful. > 1. When we access all-visible data blocks. How's that help? The block can still become not-all-visible immediately after you look; and even if it stays all-visible throughout the query, that doesn't help the problem of inconsistency with other rows whose status did change recently. The fundamental hole in all these ideas is that they destroy the guarantee of consistent treatment of row visibility, which is what a snapshot is *for*. And as I said last time, it's not acceptable to not know when the snapshot will be taken. 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] cheaper snapshots redux
Robert Haas writes: > With respect to the first problem, what I'm imagining is that we not > do a complete rewrite of the snapshot in shared memory on every > commit. Instead, when a transaction ends, we'll decide whether to (a) > write a new snapshot or (b) just record the XIDs that ended. If we do > (b), then any backend that wants a snapshot will need to copy from > shared memory both the most recently written snapshot and the XIDs > that have subsequently ended. From there, it can figure out which > XIDs are still running. Of course, if the list of recently-ended XIDs > gets too long, then taking a snapshot will start to get expensive, so > we'll need to periodically do (a) instead. There are other ways that > this could be done as well; for example, the KnownAssignedXids stuff > just flags XIDs that should be ignored and then periodically compacts > away the ignored entries. I'm a bit concerned that this approach is trying to optimize the heavy contention situation at the cost of actually making things worse anytime that you're not bottlenecked by contention for access to this shared data structure. In particular, given the above design, then every reader of the data structure has to duplicate the work of eliminating subsequently-ended XIDs from the latest stored snapshot. Maybe that's relatively cheap, but if you do it N times it's not going to be so cheap anymore. In fact, it looks to me like that cost would scale about as O(N^2) in the number of transactions you allow to elapse before storing a new snapshot, so you're not going to be able to let very many go by before you do that. I don't say this can't be made to work, but I don't want to blow off performance for single-threaded applications in pursuit of scalability that will only benefit people running massively parallel applications on big iron. 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] FATAL: lock AccessShareLock on object 0/1260/0 is already held
On Mon, Aug 22, 2011 at 3:31 AM, daveg wrote: > So far I've got: > > - affects system tables > - happens very soon after process startup > - in 8.4.7 and 9.0.4 > - not likely to be hardware or OS related > - happens in clusters for period of a few second to many minutes > > I'll work on printing the LOCK and LOCALLOCK when it happens, but it's > hard to get downtime to pick up new builds. Any other ideas on getting to > the bottom of this? I've been thinking this one over, and doing a little testing. I'm still stumped, but I have a few thoughts. What that error message is really saying is that the LOCALLOCK bookkeeping doesn't match the PROCLOCK bookkeeping; it doesn't tell us which one is to blame. My first thought was that there might be some situation where LockAcquireExtended() gets an interrupt between the time it does the LOCALLOCK lookup and the time it acquires the partition lock. If the interrupt handler were to acquire (but not releases) a lock in the meantime, then we'd get confused. However, I can't see how that's possible. I inserted some debugging code to fail an assertion if CHECK_FOR_INTERRUPTS() gets invoked in between those two points or if ImmediateInterruptOK is set on entering the function, and the system still passes regression tests. My second thought is that perhaps a process is occasionally managing to exit without fully cleaning up the associated PROCLOCK entry. At first glance, it appears that this would explain the observed symptoms. A new backend gets the PGPROC belonging to the guy who didn't clean up after himself, hits the error, and disconnects, sticking himself right back on to the head of the SHM_QUEUE where the next connection will inherit the same PGPROC and hit the same problem. But it's not clear to me what could cause the system to get into this state in the first place, or how it would eventually right itself. It might be worth kludging up your system to add a test to InitProcess() to verify that all of the myProcLocks SHM_QUEUEs are either NULL or empty, along the lines of the attached patch (which assumes that assertions are enabled; otherwise, put in an elog() of some sort). Actually, I wonder if we shouldn't move all the SHMQueueInit() calls for myProcLocks to InitProcGlobal() rather than doing it over again every time someone calls InitProcess(). Besides being a waste of cycles, it's probably less robust this way. If there somehow are leftovers in one of those queues, the next successful call to LockReleaseAll() ought to clean up the mess, but of course there's no chance of that working if we've nuked the queue pointers. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company initprocess-assert.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] cheaper snapshots redux
On Tue, Aug 23, 2011 at 12:13 PM, Tom Lane wrote: > I'm a bit concerned that this approach is trying to optimize the heavy > contention situation at the cost of actually making things worse anytime > that you're not bottlenecked by contention for access to this shared > data structure. In particular, given the above design, then every > reader of the data structure has to duplicate the work of eliminating > subsequently-ended XIDs from the latest stored snapshot. Maybe that's > relatively cheap, but if you do it N times it's not going to be so cheap > anymore. In fact, it looks to me like that cost would scale about as > O(N^2) in the number of transactions you allow to elapse before storing > a new snapshot, so you're not going to be able to let very many go by > before you do that. That's certainly a fair concern, and it might even be worse than O(n^2). On the other hand, the current approach involves scanning the entire ProcArray for every snapshot, even if nothing has changed and 90% of the backends are sitting around playing tiddlywinks, so I don't think I'm giving up something for nothing except perhaps in the case where there is only one active backend in the entire system. On the other hand, you could be entirely correct that the current implementation wins in the uncontended case. Without testing it, I just don't know... -- 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] cheaper snapshots redux
Robert Haas writes: > I think the real trick is figuring out a design that can improve > concurrency. I'm far from familiar with the detailed concepts here, but allow me to comment. I have two open questions: - is it possible to use a distributed algorithm to produce XIDs, something like Vector Clocks? Then each backend is able to create a snapshot (well, and XID) on its own, and any backend is still able to compare its snapshot to any other snapshot (well, XID) - is it possible to cache the production of the next snapshots so that generating an XID only means getting the next in a pre-computed vector? My guess by reading the emails here is that we need to add some information at snapshot generation time, it's not just about getting a 32 bit sequence number. I'm not sure I'm being that helpful here, but sometime stating the obviously impossible ideas allows to think about some new design, so I figured I would still try :) Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] text search: restricting the number of parsed words in headline generation
Given a document and a query, the goal of headline generation is to
produce text excerpts in which the query appears. Currently the headline
generation in postgres follows the following steps:
1. Tokenize the documents and obtain the lexemes
2. Decide on lexemes that should be the part of the headline
3. Generate the headline
So the time taken by the headline generation is directly dependent on
the size of the document. The longer the document, the more time taken
to tokenize and more lexemes to operate on.
Most of the time is taken during the tokenization phase and for very big
documents, the headline generation is very expensive.
Here is a simple patch that limits the number of words during the
tokenization phase and puts an upper-bound on the headline generation.
The headline function takes a parameter MaxParsedWords. If this
parameter is negative or not supplied, then the entire document is
tokenized and operated on (the current behavior). However, if the
supplied MaxParsedWords is a positive number, then the tokenization
stops after MaxParsedWords is obtained. The remaining headline
generation happens on the tokens obtained till that point.
The current patch can be applied to 9.1rc1. It lacks changes to the
documentation and test cases. I will add them if you folks agree on the
functionality.
-Sushant.
diff -ru postgresql-9.1rc1/src/backend/tsearch/ts_parse.c postgresql-9.1rc1-dev/src/backend/tsearch/ts_parse.c
--- postgresql-9.1rc1/src/backend/tsearch/ts_parse.c 2011-08-19 02:53:13.0 +0530
+++ postgresql-9.1rc1-dev/src/backend/tsearch/ts_parse.c 2011-08-23 21:27:10.0 +0530
@@ -525,10 +525,11 @@
}
void
-hlparsetext(Oid cfgId, HeadlineParsedText *prs, TSQuery query, char *buf, int buflen)
+hlparsetext(Oid cfgId, HeadlineParsedText *prs, TSQuery query, char *buf, int buflen, int max_parsed_words)
{
int type,
-lenlemm;
+lenlemm,
+numparsed = 0;
char *lemm = NULL;
LexizeData ldata;
TSLexeme *norms;
@@ -580,8 +581,8 @@
else
addHLParsedLex(prs, query, lexs, NULL);
} while (norms);
-
- } while (type > 0);
+ numparsed += 1;
+ } while (type > 0 && (max_parsed_words < 0 || numparsed < max_parsed_words));
FunctionCall1(&(prsobj->prsend), PointerGetDatum(prsdata));
}
--- postgresql-9.1rc1/src/backend/tsearch/wparser.c 2011-08-19 02:53:13.0 +0530
+++ postgresql-9.1rc1-dev/src/backend/tsearch/wparser.c 2011-08-23 21:30:12.0 +0530
@@ -304,6 +304,8 @@
text *out;
TSConfigCacheEntry *cfg;
TSParserCacheEntry *prsobj;
+ ListCell *l;
+int max_parsed_words = -1;
cfg = lookup_ts_config_cache(PG_GETARG_OID(0));
prsobj = lookup_ts_parser_cache(cfg->prsId);
@@ -317,13 +319,21 @@
prs.lenwords = 32;
prs.words = (HeadlineWordEntry *) palloc(sizeof(HeadlineWordEntry) * prs.lenwords);
- hlparsetext(cfg->cfgId, &prs, query, VARDATA(in), VARSIZE(in) - VARHDRSZ);
if (opt)
prsoptions = deserialize_deflist(PointerGetDatum(opt));
else
prsoptions = NIL;
+ foreach(l, prsoptions)
+ {
+ DefElem*defel = (DefElem *) lfirst(l);
+ char *val = defGetString(defel);
+ if (pg_strcasecmp(defel->defname, "MaxParsedWords") == 0)
+ max_parsed_words = pg_atoi(val, sizeof(int32), 0);
+}
+
+ hlparsetext(cfg->cfgId, &prs, query, VARDATA(in), VARSIZE(in) - VARHDRSZ, max_parsed_words);
FunctionCall3(&(prsobj->prsheadline),
PointerGetDatum(&prs),
PointerGetDatum(prsoptions),
diff -ru postgresql-9.1rc1/src/include/tsearch/ts_utils.h postgresql-9.1rc1-dev/src/include/tsearch/ts_utils.h
--- postgresql-9.1rc1/src/include/tsearch/ts_utils.h 2011-08-19 02:53:13.0 +0530
+++ postgresql-9.1rc1-dev/src/include/tsearch/ts_utils.h 2011-08-23 21:04:14.0 +0530
@@ -98,7 +98,7 @@
*/
extern void hlparsetext(Oid cfgId, HeadlineParsedText *prs, TSQuery query,
- char *buf, int4 buflen);
+ char *buf, int4 buflen, int max_parsed_words);
extern text *generateHeadline(HeadlineParsedText *prs);
/*
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Why doesn't psql use the information schema to get ACL description ?
Recently a bug was reported on the JDBC list regarding table privileges. It turns out that we have not been parsing the ACL tables correctly. This is partially due to having to track the catalogs through each version. So this breaks down into two questions 1) is it possible to get all of the ACL data from the information_schema ? 2) If so why doesn't psql use these tables ? Ultimately my ulterior motive is to make sure the information schema is actually useful and ensure that it maintains that status. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting rid of pg_pltemplate
On Aug 23, 2011, at 8:31 AM, Tom Lane wrote: > One of my goals for the extensions feature has been that we should be able > to get rid of the pg_pltemplate system catalog, moving all the information > therein into languages' extension definition files. This would allow > third-party procedural languages to be installed as easily as built-in > ones. We failed to get this done in 9.1, mostly because we couldn't work > out what to do about tmpldbacreate (the feature to allow non-superuser > database owners to create "safe" languages). Here's a proposal for that. Awesome. > We'll add a new boolean parameter to extension control files, called say > "dba_create" (ideas for better names welcome). as_superuser? security_superuser? install_as_superuser? Note that we already have a "superuser" flag, so we'll want to carefully document the relationship. > Presumably, a dba_create extension could also be dropped by a > non-superuser DBA. We could either inspect the extension control file > again when deciding whether to allow DROP EXTENSION, or copy the flag into > a new column in pg_extension so that the installed extension doesn't rely > on having the control file still around. Probably the latter is a better > idea. I would think so, yes. > The above mechanism could be applied to any sort of extension, not just > procedural language ones, and would be useful for ones involving > C-language functions (which is most). But it's not my purpose at the > moment to open a debate about whether any of our existing contrib modules > ought to get marked as dba_create. For the moment I'm just considering > the procedural languages. That can be discussed after there's a feature to discuss. :-) > (In essence, if a database administrator allows a dba_create extension > to be installed in his extensions directory, he's certifying that he > trusts that extension enough to allow it to be installed by > non-superusers. This is not just a matter of whether the extension > itself is safe, but whether the installation script could conceivably be > subverted by running it in a malicious SQL environment. I'd just as > soon start out with assuming that only for the PL extensions, which need > do nothing except a couple of CREATE FUNCTION commands and then CREATE > LANGUAGE.) I think this makes sense. But as you note, it does open things up, so we probably ought to have a way to alert the DBA that the extension she just downloaded and installed from PGXN has this flag set. This needs to be an informed decision. > Having done that, we'd mark all the standard "trusted" PLs as dba_create, > expand the existing definition scripts for the PL extensions so that they > fully specify the languages and their support functions (transferring all > that knowledge from the current contents of pg_pltemplate), and then > remove pg_pltemplate. What about untrusted languages? > Now, the reason we invented pg_pltemplate in the first place was to > solve problems with updating procedural language definitions from one > release to the next. Essentially, if we do this, we're making a bet > that the extensions feature provides a more complete, better-thought-out > update mechanism than pg_pltemplate itself. I think that's probably > right, especially when thinking about it from the standpoint of a > non-core PL; but it's worth pointing out that we are taking some risk of > having to do more work than before. For example, if we wanted to add > another type of support function to PLs in the future, this approach > would mean having to add an ALTER LANGUAGE command for the PLs' update > scripts to use to add that function to an existing PL. Otherwise we > could not support binary-upgrade scenarios. Who came up with this upgrade script design, anyway? ;-P Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cheaper snapshots redux
Robert Haas writes: > That's certainly a fair concern, and it might even be worse than > O(n^2). On the other hand, the current approach involves scanning the > entire ProcArray for every snapshot, even if nothing has changed and > 90% of the backends are sitting around playing tiddlywinks, so I don't > think I'm giving up something for nothing except perhaps in the case > where there is only one active backend in the entire system. On the > other hand, you could be entirely correct that the current > implementation wins in the uncontended case. Without testing it, I > just don't know... Sure. Like I said, I don't know that this can't be made to work. I'm just pointing out that we have to keep an eye on the single-backend case as well as the many-backends case. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Range Types
Attached is the latest version of the Range Types patch. I will get it into better shape before the commitfest, but wanted to put up a draft in case anyone had comments on the TODO items. Changes: * Uses BTree opclass rather than compare function. * Collation specified at type definition time. * Various fixes. TODO: * Should the catalog hold the opclass or the opfamily? This doesn't affect much, but I wasn't sure which to actually store in the catalog. * Use Robert Haas' suggestion for auto-generating constructors with the same name as the range type, e.g. "int8range(1,10,'[]')", where the third argument defaults to '[)'. This allows better type inference for constructors, especially when there are multiple range types over the same base type (and collation is a common case of this). I believe this was the best idea after significant discussion: http://archives.postgresql.org/pgsql-hackers/2011-06/msg02046.php http://archives.postgresql.org/pgsql-hackers/2011-07/msg00210.php * Send/recv functions * cleanup * documentation updates Regards, Jeff Davis rangetypes-20110822.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
[HACKERS] skip WAL on COPY patch
The attached patch adds an option to the COPY command to skip writing
WAL when the following conditions are all met:
1) The table is empty (zero size on disk)
2) The copy command can obtain an access exclusive lock on the table
with out blocking.
3) The WAL isn't needed for replication
For example
COPY a FROM '/tmp/a.txt' (SKIP_WAL);
A non-default option to the copy command is required because the copy
will block out any concurrent access to the table which would be
undesirable in some cases and is different from the current behaviour.
This can safely be done because if the transaction does not commit the
empty version of the data files are still available. The COPY command
already skips WAL if the table was created in the current transaction.
There was a discussion on something similar before[1] but I didn't see
any discussion of having it only obtain the lock if it can do so without
waiting (nor could I find in the archives what happened to that patch).
I'm not attached to the SKIP_WAL vs LOCK as the option
1- see http://archives.postgresql.org/pgsql-patches/2005-12/msg00206.php
Steve
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml
index a73b022..3a0e521 100644
*** a/doc/src/sgml/ref/copy.sgml
--- b/doc/src/sgml/ref/copy.sgml
*** COPY { ta
*** 42,47
--- 42,48
FORCE_QUOTE { ( column [, ...] ) | * }
FORCE_NOT_NULL ( column [, ...] ) |
ENCODING 'encoding_name'
+ SKIP_WAL
*** COPY { ta
*** 293,298
--- 294,312
for more details.
+
+
+ SKIP_WAL
+
+
+ Specifies that the writing of WAL should be skipped if possible.
+ WAL can be skipped if the table being copied into is empty and
+ if an exclusive lock can be obtained without waiting. If this
+ option is specified and WAL is skipped then the transaction will
+ hold an exclusive lock on the table being copied until the transaction
+ commits.
+
+
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 528a3a1..bd81a4b 100644
*** a/src/backend/commands/copy.c
--- b/src/backend/commands/copy.c
***
*** 29,34
--- 29,35
#include "commands/defrem.h"
#include "commands/trigger.h"
#include "executor/executor.h"
+ #include "commands/tablecmds.h"
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
#include "mb/pg_wchar.h"
***
*** 37,42
--- 38,44
#include "parser/parse_relation.h"
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
+ #include "storage/lmgr.h"
#include "tcop/tcopprot.h"
#include "utils/acl.h"
#include "utils/builtins.h"
*** typedef struct CopyStateData
*** 120,125
--- 122,128
bool *force_quote_flags; /* per-column CSV FQ flags */
List *force_notnull; /* list of column names */
bool *force_notnull_flags; /* per-column CSV FNN flags */
+ bool skip_wal;/* skip WAL if able */
/* these are just for error messages, see CopyFromErrorCallback */
const char *cur_relname; /* table name for error messages */
*** ProcessCopyOptions(CopyState cstate,
*** 965,970
--- 968,978
errmsg("argument to option \"%s\" must be a valid encoding name",
defel->defname)));
}
+ else if (strcmp(defel->defname,"skip_wal") == 0)
+ {
+
+ cstate->skip_wal=true;
+ }
else
ereport(ERROR,
(errcode(ERRCODE_SYNTAX_ERROR),
*** CopyFrom(CopyState cstate)
*** 1910,1915
--- 1918,1957
if (!XLogIsNeeded())
hi_options |= HEAP_INSERT_SKIP_WAL;
}
+
+ /*
+ * if SKIP_WAL was requested we try to avoid writing
+ * WAL if the table is 0 bytes on disk (empty) and
+ * that we can obtain an exclusive lock on it without blocking.
+ *
+ */
+ if(cstate->skip_wal && !XLogIsNeeded() &&
+ ConditionalLockRelationOid(cstate->rel->rd_id,AccessExclusiveLock))
+ {
+
+ Datum size = DirectFunctionCall2(pg_relation_size,
+ ObjectIdGetDatum(cstate->rel->rd_id),
+ PointerGetDatum(cstring_to_text("main")));
+ if ( DatumGetInt64(size)==0)
+ {
+ /**
+ * The relation is empty + unused.
+ * truncate it so that if this transaction
+ * rollsback then the changes to the relation files
+ * will dissapear (the current relation files will
+ * remain untouched)
+ */
+ truncate_relation(cstate->rel);
+ hi_options |= HEAP_INSERT_SKIP_FSM;
+ hi_options |= HEAP_INSERT_SKIP_WAL;
+ }
+ else
+ {
+ UnlockRelation(cstate->rel,AccessExclusiveLock);
+ }
+
+ }
+
/*
* We need a ResultRelInfo so we can use the regular executor's
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 4509cda..ff5bf8d 100644
*** a/src/backend/commands/tablecmds.c
--- b/src/backend/commands/tablecmds.c
*** ExecuteTruncate(TruncateStm
Re: [HACKERS] SSI 2PC coverage
Heikki Linnakangas wrote: > Committed. I removed the second expected output file, and marked > the prepared-transactions tests in the schedule as "ignore" > instead. That way if max_prepared_transactions=0, you get a notice > that the test case failed, but pg_regress still returns 0 as exit > status. Thanks! Sorry I didn't get to it. Things got really busy here. > I did change the lexer slightly, to trim whitespace from the > beginning and end of SQL blocks. This cuts the size of expected > output a bit, and makes it look nicer anyway. OK. You missed the alternative outputs for a couple files. These are needed to get successful tests with default_transaction_isolation = 'serializable' or 'repeatable read'. Patch attached. -Kevin *** a/src/test/isolation/expected/fk-deadlock2_1.out --- b/src/test/isolation/expected/fk-deadlock2_1.out *** *** 1,110 Parsed test spec with 2 sessions starting permutation: s1u1 s1u2 s1c s2u1 s2u2 s2c ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1c: COMMIT; ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2c: COMMIT; starting permutation: s1u1 s1u2 s2u1 s1c s2u2 s2c ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1c: COMMIT; step s2u1: <... completed> ERROR: could not serialize access due to concurrent update ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ERROR: current transaction is aborted, commands ignored until end of transaction block ! step s2c: COMMIT; starting permutation: s1u1 s2u1 s1u2 s2u2 s1c s2c ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; step s1u2: <... completed> ERROR: deadlock detected ! step s1c: COMMIT; ! step s2c: COMMIT; starting permutation: s1u1 s2u1 s1u2 s2u2 s2c s1c ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; step s1u2: <... completed> ERROR: deadlock detected ! step s2c: COMMIT; ! step s1c: COMMIT; starting permutation: s1u1 s2u1 s2u2 s1u2 s1c s2c ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ERROR: deadlock detected step s2u2: <... completed> ! step s1c: COMMIT; ! step s2c: COMMIT; starting permutation: s1u1 s2u1 s2u2 s1u2 s2c s1c ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ERROR: deadlock detected step s2u2: <... completed> ! step s2c: COMMIT; ! step s1c: COMMIT; starting permutation: s2u1 s1u1 s1u2 s2u2 s1c s2c ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; step s1u2: <... completed> ERROR: deadlock detected ! step s1c: COMMIT; ! step s2c: COMMIT; starting permutation: s2u1 s1u1 s1u2 s2u2 s2c s1c ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; step s1u2: <... completed> ERROR: deadlock detected ! step s2c: COMMIT; ! step s1c: COMMIT; starting permutation: s2u1 s1u1 s2u2 s1u2 s1c s2c ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ERROR: deadlock detected step s2u2: <... completed> ! step s1c: COMMIT; ! step s2c: COMMIT; starting permutation: s2u1 s1u1 s2u2 s1u2 s2c s1c ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ERROR: deadlock detected step s2u2: <... completed> ! step s2c: COMMIT; ! step s1c: COMMIT; starting permutation: s2u1 s2u2 s1u1 s2c s1u2 s1c ! step s2u1: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s2u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ! step s1u1: UPDATE A SET Col1 = 1 WHERE AID = 1; ! step s2c: COMMIT; step s1u1: <... completed> ! step s1u2: UPDATE B SET Col2 = 1 WHERE BID = 2; ERROR: could not serialize access due
Re: [HACKERS] Getting rid of pg_pltemplate
"David E. Wheeler" writes: > On Aug 23, 2011, at 8:31 AM, Tom Lane wrote: >> Having done that, we'd mark all the standard "trusted" PLs as dba_create, >> expand the existing definition scripts for the PL extensions so that they >> fully specify the languages and their support functions (transferring all >> that knowledge from the current contents of pg_pltemplate), and then >> remove pg_pltemplate. > What about untrusted languages? Untrusted languages would still need to be installed by superusers. At least, that's the policy enforced by the current contents of pg_pltemplate, and I wasn't planning to question it. 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] Why doesn't psql use the information schema to get ACL description ?
On tis, 2011-08-23 at 12:48 -0400, Dave Cramer wrote: > Recently a bug was reported on the JDBC list regarding table > privileges. It turns out that we have not been parsing the ACL tables > correctly. This is partially due to having to track the catalogs > through each version. So this breaks down into two questions > > 1) is it possible to get all of the ACL data from the information_schema ? > 2) If so why doesn't psql use these tables ? > > Ultimately my ulterior motive is to make sure the information schema > is actually useful and ensure that it maintains that status. The information schema only exposes privileges defined in the SQL standard on objects defined in the SQL standard. So psql or any tool that wants to provide information on PostgreSQL-specific features can't use that. But perhaps the JDBC interfaces only expose certain standard information anyway, so it could be useful. Depends on the details, though. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] text search: restricting the number of parsed words in headline generation
Sushant Sinha writes: > Given a document and a query, the goal of headline generation is to > produce text excerpts in which the query appears. ... right ... > Here is a simple patch that limits the number of words during the > tokenization phase and puts an upper-bound on the headline generation. Doesn't this force the headline to be taken from the first N words of the document, independent of where the match was? That seems rather unworkable, or at least unhelpful. 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] skip WAL on COPY patch
Steve Singer writes: > The attached patch adds an option to the COPY command to skip writing > WAL when the following conditions are all met: > 1) The table is empty (zero size on disk) > 2) The copy command can obtain an access exclusive lock on the table > with out blocking. > 3) The WAL isn't needed for replication Exposing this as a user-visible option seems a seriously bad idea. We'd have to support that forever. ISTM it ought to be possible to avoid the exclusive lock ... maybe not with this particular implementation, but somehow. 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] Getting rid of pg_pltemplate
Tom Lane writes: > We'll add a new boolean parameter to extension control files, called say > "dba_create" (ideas for better names welcome). If it's missing or set > to false, there's no change in behavior. When it's true, then > > (a) you must be superuser or owner of the current database to create the > extension; > > (b) the commands within the extension's script will be run as though by a > superuser, even if you aren't one. That's called sudo on linux. I propose that we stick to such a name. Do we want a more general SUDO facility in PostgreSQL? It would be, I guess, about the same thing as SET ROLE postgres; and you would have to be granted the rights to “upgrade” to being that role. I can see how relaxing the role membership would help in the case of extensions though, with the behavior you're describing. Do you see other places where giving sudoer to a database owner even if he's not in the postgres (or another superuser) role would be useful? 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] text search: restricting the number of parsed words in headline generation
Excerpts from Tom Lane's message of mar ago 23 15:59:18 -0300 2011: > Sushant Sinha writes: > > Given a document and a query, the goal of headline generation is to > > produce text excerpts in which the query appears. > > ... right ... > > > Here is a simple patch that limits the number of words during the > > tokenization phase and puts an upper-bound on the headline generation. > > Doesn't this force the headline to be taken from the first N words of > the document, independent of where the match was? That seems rather > unworkable, or at least unhelpful. Yeah ... Doesn't a search result include the position on which the tokens were found within the document? Wouldn't it make more sense to improve the system somehow so that it can restrict searching for headlines in the general area where the tokens were found? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Getting rid of pg_pltemplate
Dimitri Fontaine writes: > Tom Lane writes: >> We'll add a new boolean parameter to extension control files, called say >> "dba_create" (ideas for better names welcome). If it's missing or set >> to false, there's no change in behavior. When it's true, then >> >> (a) you must be superuser or owner of the current database to create the >> extension; >> >> (b) the commands within the extension's script will be run as though by a >> superuser, even if you aren't one. > That's called sudo on linux. I propose that we stick to such a name. I'm not impressed with that name: it will mean nothing to Windows users, nor for that matter to many non-sysadmin types on Unix. > Do we want a more general SUDO facility in PostgreSQL? It would be, I > guess, about the same thing as SET ROLE postgres; Yeah, I think SET ROLE already covers that territory. The point of the current proposal is to grant a very limited subset of superuser privileges --- specifically, the right to install specific extensions --- to database owners. Maybe it'd make sense to eliminate the tie to database ownership and instead consider that you're allowed to do this if you're a member of some predefined role, which then would typically be GRANTed to database owners or other semi-trustworthy people. But we don't currently have any predefined group roles like that, so it'd be a rather large departure from past practice. 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] Getting rid of pg_pltemplate
On Tue, Aug 23, 2011 at 3:09 PM, Dimitri Fontaine wrote: > Tom Lane writes: >> We'll add a new boolean parameter to extension control files, called say >> "dba_create" (ideas for better names welcome). If it's missing or set >> to false, there's no change in behavior. When it's true, then >> >> (a) you must be superuser or owner of the current database to create the >> extension; >> >> (b) the commands within the extension's script will be run as though by a >> superuser, even if you aren't one. > > That's called sudo on linux. I propose that we stick to such a name. Actually, this is somewhat more like UNIX setuid (2). When I first started using SECURITY DEFINER functions, I thought of it as being "like sudo." But it's really "like setuid". -- When confronted by a difficult problem, solve it by reducing it to the question, "How would the Lone Ranger handle this?" -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_dump --exclude-table-data
Attached is an undocumented patch that allows a user to have pg_dump
exclude data but not DDL for a table. One use case for this is a very
large table that changes infrequently, and for which dumping data
frequently would be wasteful and unnecessary. This is especially useful
in conjunction with another patch (see next email) to do post-data items
only or omit post-data items in pg_restore.
For those who are (like my clients :-) ) anxious to get their hands on
this immediately, a backport patch is also attached which applies to 9.0
sources, and applies with offsets to 8.4 sources.
cheers
andrew
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 8721e65..aad6b00 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -104,6 +104,8 @@ static SimpleStringList table_include_patterns = {NULL,
NULL};
static SimpleOidList table_include_oids = {NULL, NULL};
static SimpleStringList table_exclude_patterns = {NULL, NULL};
static SimpleOidList table_exclude_oids = {NULL, NULL};
+static SimpleStringList tabledata_exclude_patterns = {NULL, NULL};
+static SimpleOidList tabledata_exclude_oids = {NULL, NULL};
/* default, if no "inclusion" switches appear, is to dump everything */
static bool include_everything = true;
@@ -261,6 +263,7 @@ main(int argc, char **argv)
{"blobs", no_argument, NULL, 'b'},
{"clean", no_argument, NULL, 'c'},
{"create", no_argument, NULL, 'C'},
+ {"exclude-table-data", required_argument, NULL, 'D'},
{"file", required_argument, NULL, 'f'},
{"format", required_argument, NULL, 'F'},
{"host", required_argument, NULL, 'h'},
@@ -334,7 +337,7 @@ main(int argc, char **argv)
}
}
- while ((c = getopt_long(argc, argv,
"abcCE:f:F:h:in:N:oOp:RsS:t:T:U:vwWxX:Z:",
+ while ((c = getopt_long(argc, argv,
"abcCD:E:f:F:h:in:N:oOp:RsS:t:T:U:vwWxX:Z:",
long_options,
&optindex)) != -1)
{
switch (c)
@@ -355,6 +358,10 @@ main(int argc, char **argv)
outputCreateDB = 1;
break;
+ case 'D': /* exclude table(s)
data */
+
simple_string_list_append(&tabledata_exclude_patterns, optarg);
+ break;
+
case 'E': /* Dump encoding */
dumpencoding = optarg;
break;
@@ -689,6 +696,10 @@ main(int argc, char **argv)
}
expand_table_name_patterns(&table_exclude_patterns,
&table_exclude_oids);
+
+ expand_table_name_patterns(&tabledata_exclude_patterns,
+
&tabledata_exclude_oids);
+
/* non-matching exclusion patterns aren't an error */
/*
@@ -813,6 +824,8 @@ help(const char *progname)
printf(_(" -b, --blobs include large objects in
dump\n"));
printf(_(" -c, --clean clean (drop) database objects
before recreating\n"));
printf(_(" -C, --createinclude commands to create
database in dump\n"));
+ printf(_(" -D, --exclude-table-data=TABLE\n"
+" do NOT dump data for
the named table(s)\n"));
printf(_(" -E, --encoding=ENCODING dump the data in encoding
ENCODING\n"));
printf(_(" -n, --schema=SCHEMA dump the named schema(s)
only\n"));
printf(_(" -N, --exclude-schema=SCHEMA do NOT dump the named
schema(s)\n"));
@@ -1012,6 +1025,15 @@ selectDumpableTable(TableInfo *tbinfo)
simple_oid_list_member(&table_exclude_oids,
tbinfo->dobj.catId.oid))
tbinfo->dobj.dump = false;
+
+ /* If table is to be dumped, check that the data is not excluded */
+ if (tbinfo->dobj.dump && !
+ simple_oid_list_member(&tabledata_exclude_oids,
+
tbinfo->dobj.catId.oid))
+ tbinfo->dobj.dumpdata = true;
+ else
+ tbinfo->dobj.dumpdata = false;
+
}
/*
@@ -1391,6 +1413,10 @@ dumpTableData(Archive *fout, TableDataInfo *tdinfo)
DataDumperPtr dumpFn;
char *copyStmt;
+ /* don't do anything if the data isn't wanted */
+ if (!tbinfo->dobj.dumpdata)
+ return;
+
if (!dump_inserts)
{
/* Dump/restore using COPY */
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 1dc7157..b4f5716 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -127,6 +127,7 @@ typedef st
Re: [HACKERS] skip WAL on COPY patch
On Tue, Aug 23, 2011 at 3:05 PM, Tom Lane wrote: > Steve Singer writes: >> The attached patch adds an option to the COPY command to skip writing >> WAL when the following conditions are all met: > >> 1) The table is empty (zero size on disk) >> 2) The copy command can obtain an access exclusive lock on the table >> with out blocking. >> 3) The WAL isn't needed for replication > > Exposing this as a user-visible option seems a seriously bad idea. > We'd have to support that forever. ISTM it ought to be possible to > avoid the exclusive lock ... maybe not with this particular > implementation, but somehow. Also, if it only works when the table is zero size on disk, you might as well just let people truncate their already-empty tables when they want this optimization. What I think would be really interesting is a way to make this work when the table *isn't* empty. In other words, have a COPY option that (1) takes an exclusive lock on the table, (2) writes the data being inserted into new pages beyond the old EOF, and (3) arranges for crash recovery or transaction abort to truncate the table back to its previous length. Then you could do fast bulk loads even into a table that's already populated, so long as you don't mind that the table will be excusive-locked and freespace within existing heap pages won't be reused. -- 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] Getting rid of pg_pltemplate
Tom Lane writes: >> That's called sudo on linux. I propose that we stick to such a name. > > I'm not impressed with that name: it will mean nothing to Windows users, > nor for that matter to many non-sysadmin types on Unix. Fair enough. >> Do we want a more general SUDO facility in PostgreSQL? It would be, I >> guess, about the same thing as SET ROLE postgres; > > Yeah, I think SET ROLE already covers that territory. Yes, except for the need of CREATE EXTENSION where you would want to delegate to power to database owner without granting them superuser at will. So I agree with your statement that we need something more than what we already have here, even if under the hood it will probably end up using existing mechanisms. > The point of the current proposal is to grant a very limited subset of > superuser privileges --- specifically, the right to install specific > extensions --- to database owners. Maybe it'd make sense to eliminate > the tie to database ownership and instead consider that you're allowed > to do this if you're a member of some predefined role, which then would > typically be GRANTed to database owners or other semi-trustworthy people. > But we don't currently have any predefined group roles like that, so > it'd be a rather large departure from past practice. I think we have something like that, in fact, with the replication privilege. We could have an "extension" privilege that defaults to being granted to database owners, and that would behave like SET ROLE superuser; when issuing extension related features. 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] Getting rid of pg_pltemplate
Christopher Browne writes: > Actually, this is somewhat more like UNIX setuid (2). > > When I first started using SECURITY DEFINER functions, I thought of it > as being "like sudo." But it's really "like setuid". I see SECURITY DEFINER functions definitely as setuid for PostgreSQL, but I was thinking about this CREATE EXTENSION thing more like sudo. In the former case, you manage the rights on the object (script, function), in the latter case you manage the rights on the command issued. Well I guess it's a very thin line here. But maybe the parameter could be called security_definer, knowing that the control files are a superuser privilege thing (so the definer needs to be a superuser granted the postgres system user). Maybe run_script_with_superuser is more explicit for the situation though. 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] skip WAL on COPY patch
Robert Haas writes: > What I think would be really interesting is a way to make this work > when the table *isn't* empty. In other words, have a COPY option that > (1) takes an exclusive lock on the table, (2) writes the data being > inserted into new pages beyond the old EOF, and (3) arranges for crash > recovery or transaction abort to truncate the table back to its > previous length. Then you could do fast bulk loads even into a table > that's already populated, so long as you don't mind that the table > will be excusive-locked and freespace within existing heap pages won't > be reused. What are you going to do with the table's indexes? 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] skip WAL on COPY patch
Excerpts from Robert Haas's message of mar ago 23 17:08:50 -0300 2011: > What I think would be really interesting is a way to make this work > when the table *isn't* empty. In other words, have a COPY option that > (1) takes an exclusive lock on the table, (2) writes the data being > inserted into new pages beyond the old EOF, and (3) arranges for crash > recovery or transaction abort to truncate the table back to its > previous length. Then you could do fast bulk loads even into a table > that's already populated, so long as you don't mind that the table > will be excusive-locked and freespace within existing heap pages won't > be reused. It seems to me this would be relatively simple if we allowed segments that are not a full GB in length. That way, COPY could write into a whole segment and "attach" it to the table at commit time (say, by renaming). -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_restore --no-post-data and --post-data-only
Attached is an undocumented patch that allows pg_restore to omit
post-data items or omit all but post-data items. This has been discussed
before, and Simon sent in a patch back on 2008, which has bitrotted
some. I'm not sure why it was dropped at the time, but I think it's time
to do this. This patch relies on some infrastructure that was added
since Simon's patch, so it works a bit differently (and more simply).
So with this patch, the following three sequences should be equivalent:
pg_restore --no-post-data
pg_restore --post-data-only
pg_restore -s --no-post-data
pg_restore -a
pg_restore --post-data-only
pg_restore
This is useful and worth doing on its own, and will also add to the
usefulness of the pg_dump --exclude-table-data patch in my previous email.
As with that patch, a version that applies to version 9.0 and 8.4
sources is also attached, for the very eager.
cheers
andrew
diff --git a/src/bin/pg_dump/pg_backup.h b/src/bin/pg_dump/pg_backup.h
index 5a73779..8bd45c1 100644
--- a/src/bin/pg_dump/pg_backup.h
+++ b/src/bin/pg_dump/pg_backup.h
@@ -106,6 +106,8 @@ typedef struct _restoreOptions
char *superuser; /* Username to use as superuser */
char *use_role; /* Issue SET ROLE to this */
int dataOnly;
+ int postDataOnly; /* skip all but post-data section */
+ int noPostData; /* skip post-data section */
int dropSchema;
char *filename;
int schemaOnly;
diff --git a/src/bin/pg_dump/pg_backup_archiver.c
b/src/bin/pg_dump/pg_backup_archiver.c
index 26ee9d9..d113435 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -2086,6 +2086,7 @@ ReadToc(ArchiveHandle *AH)
int depIdx;
int depSize;
TocEntry *te;
+ boolin_post_data = false;
AH->tocCount = ReadInt(AH);
AH->maxDumpId = 0;
@@ -2151,6 +2152,12 @@ ReadToc(ArchiveHandle *AH)
te->section = SECTION_PRE_DATA;
}
+ /* will stay true even for SECTION_NONE items */
+ if (te->section == SECTION_POST_DATA)
+ in_post_data = true;
+
+ te->inPostData = in_post_data;
+
te->defn = ReadStr(AH);
te->dropStmt = ReadStr(AH);
@@ -2306,6 +2313,12 @@ _tocEntryRequired(TocEntry *te, RestoreOptions *ropt,
bool include_acls)
return 0;
}
+ /* skip (all but) post data section as required */
+ if (ropt->noPostData && te->inPostData)
+ return 0;
+ if (ropt->postDataOnly && ! te->inPostData)
+ return 0;
+
if (ropt->selTypes)
{
if (strcmp(te->desc, "TABLE") == 0 ||
diff --git a/src/bin/pg_dump/pg_backup_archiver.h
b/src/bin/pg_dump/pg_backup_archiver.h
index a3a87dc..8557481 100644
--- a/src/bin/pg_dump/pg_backup_archiver.h
+++ b/src/bin/pg_dump/pg_backup_archiver.h
@@ -289,6 +289,9 @@ typedef struct _tocEntry
void *dataDumperArg; /* Arg for above routine */
void *formatData; /* TOC Entry data specific to file
format */
+ /* in post data? not quite the same as section, might be SECTION_NONE */
+ boolinPostData;
+
/* working state (needed only for parallel restore) */
struct _tocEntry *par_prev; /* list links for pending/ready items; */
struct _tocEntry *par_next; /* these are NULL if not in either list */
diff --git a/src/bin/pg_dump/pg_restore.c b/src/bin/pg_dump/pg_restore.c
index dbdf7ac..e205d6e 100644
--- a/src/bin/pg_dump/pg_restore.c
+++ b/src/bin/pg_dump/pg_restore.c
@@ -76,6 +76,8 @@ main(int argc, char **argv)
static int no_data_for_failed_tables = 0;
static int outputNoTablespaces = 0;
static int use_setsessauth = 0;
+ static int post_data_only = 0;
+ static int no_post_data = 0;
struct option cmdopts[] = {
{"clean", 0, NULL, 'c'},
@@ -116,7 +118,9 @@ main(int argc, char **argv)
{"no-tablespaces", no_argument, &outputNoTablespaces, 1},
{"role", required_argument, NULL, 2},
{"use-set-session-authorization", no_argument,
&use_setsessauth, 1},
-
+ {"post-data-only", no_argument, &post_data_only, 1},
+ {"no-post-data", no_argument, &no_post_data, 1},
+
{NULL, 0, NULL, 0}
};
@@ -337,6 +341,8 @@ main(int argc, char **argv)
opts->noDataForFailedTables = no_data_for_failed_tables;
opts->noTablespace = outputNoTablespaces;
opts->use_setsessauth = use_setsessauth;
+ opts->postDataOnly = post_data_only;
+ opts->noPostData = no_post_data;
if (opts->format
Re: [HACKERS] skip WAL on COPY patch
On 11-08-23 04:17 PM, Tom Lane wrote: Robert Haas writes: What I think would be really interesting is a way to make this work when the table *isn't* empty. In other words, have a COPY option that (1) takes an exclusive lock on the table, (2) writes the data being inserted into new pages beyond the old EOF, and (3) arranges for crash recovery or transaction abort to truncate the table back to its previous length. Then you could do fast bulk loads even into a table that's already populated, so long as you don't mind that the table will be excusive-locked and freespace within existing heap pages won't be reused. What are you going to do with the table's indexes? regards, tom lane What about not updating the indexes during the copy operation then to an automatic rebuild of the indexes after the copy (but during the same transaction). If your only adding a few rows to a large table this wouldn't be what you want, but if your only adding a few rows then a small amount of WAL isn't a big concern either. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] skip WAL on COPY patch
On Tue, Aug 23, 2011 at 4:17 PM, Tom Lane wrote: > Robert Haas writes: >> What I think would be really interesting is a way to make this work >> when the table *isn't* empty. In other words, have a COPY option that >> (1) takes an exclusive lock on the table, (2) writes the data being >> inserted into new pages beyond the old EOF, and (3) arranges for crash >> recovery or transaction abort to truncate the table back to its >> previous length. Then you could do fast bulk loads even into a table >> that's already populated, so long as you don't mind that the table >> will be excusive-locked and freespace within existing heap pages won't >> be reused. > > What are you going to do with the table's indexes? Oh, hmm. That's awkward. I suppose you could come up with some solution that involved saving preimages of each already-existing index page that was modified until commit. If you crash before commit, you truncate away all the added pages and roll back to the preimages of any modified pages. That's pretty complex, though, and I'm not sure that it would be enough of a win to justify the effort. It also sounds suspiciously like a poor-man's implementation of a rollback segment; and if we ever decide we want to have an option for rollback segments, we probably want more than a poor man's version. -- 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] skip WAL on COPY patch
Excerpts from Robert Haas's message of mar ago 23 17:43:13 -0300 2011: > On Tue, Aug 23, 2011 at 4:17 PM, Tom Lane wrote: > > Robert Haas writes: > >> What I think would be really interesting is a way to make this work > >> when the table *isn't* empty. In other words, have a COPY option that > >> (1) takes an exclusive lock on the table, (2) writes the data being > >> inserted into new pages beyond the old EOF, and (3) arranges for crash > >> recovery or transaction abort to truncate the table back to its > >> previous length. Then you could do fast bulk loads even into a table > >> that's already populated, so long as you don't mind that the table > >> will be excusive-locked and freespace within existing heap pages won't > >> be reused. > > > > What are you going to do with the table's indexes? > > Oh, hmm. That's awkward. If you see what I proposed, it's simple: you can scan the new segment(s) and index the tuples found there (maybe in bulk which would be even faster). -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Short document fix
In the CREATE DOMAIN reference page of the current HEAD, it says
---
CREATE DOMAIN us_postal_code AS TEXT
CHECK(
VALUE ~ '^\\d{5}$'
OR VALUE ~ '^\\d{5}-\\d{4}$'
);
---
but I believe it should conform the standard string style now that the
default is standard_conforming_strings = on. I didn't grep if there
other pages like this.
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
Re: [HACKERS] 9.1rc1: TRAP: FailedAssertion("!(item_width > 0)", File: "costsize.c", Line: 3274)
"Erik Rijkers" writes:
> The below SQL causes:
> TRAP: FailedAssertion("!(item_width > 0)", File: "costsize.c", Line: 3274)
Thanks, I've applied a patch for this.
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] skip WAL on COPY patch
On Tue, 2011-08-23 at 15:05 -0400, Tom Lane wrote: > Steve Singer writes: > > The attached patch adds an option to the COPY command to skip writing > > WAL when the following conditions are all met: > > > 1) The table is empty (zero size on disk) > > 2) The copy command can obtain an access exclusive lock on the table > > with out blocking. > > 3) The WAL isn't needed for replication > > Exposing this as a user-visible option seems a seriously bad idea. In that particular way, I agree. But it might be useful if there were a more general declarative option like "BULKLOAD". We might then use that information for a number of optimizations that make sense for large loads. 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] Another extensions bug
Whilst testing the schema-creation-permissions scenario that Kaigai-san recently pointed out, I happened to do this: regression=# create schema c; CREATE SCHEMA regression=# create extension cube with schema c; CREATE EXTENSION regression=# drop schema c; DROP SCHEMA ... er, what? I was expecting ERROR: cannot drop schema c because other objects depend on it DETAIL: extension cube depends on schema c HINT: Use DROP ... CASCADE to drop the dependent objects too. Inspection of pg_depend proved that indeed there was only a NORMAL dependency of the extension on the schema, so this should not have happened. After a good bit of careful tracing, I came to the conclusion that the bug is here in findDependentObjects, when it comes across an INTERNAL or EXTENSION dependency in its first loop: /* * Okay, recurse to the other object instead of proceeding. We * treat this exactly as if the original reference had linked * to that object instead of this one; hence, pass through the * same flags and stack. */ findDependentObjects(&otherObject, flags, stack, targetObjects, pendingObjects, depRel); What happens in the cube case is that there's an INTERNAL dependency from type cube[] to type cube, while both of these types also have EXTENSION dependencies on the cube extension. So, when we trace the INTERNAL dependency from type cube to type cube[], findDependentObjects is entered with target object cube[] and flags = DEPFLAG_INTERNAL. In its first loop, it discovers that cube[] has an EXTENSION dependency on the cube extension, so the above code recurses to the extension object with flags = DEPFLAG_INTERNAL. And that causes the extension to get marked as being internally dependent on some other object-to-be-deleted, which then licenses the dependency code to delete it silently and without requiring CASCADE. Ooops. I think the above-quoted code is just fundamentally wrong. It was never possible to reach that code with flags = DEPFLAG_INTERNAL before, because an object can have only one INTERNAL dependency and it's the one that would have been caught by the preceding test to see if we were recursing from the other end of that dependency. Now that we've added the possibility of EXTENSION dependencies, we could get here for an EXTENSION dependency after recursing from an INTERNAL dependency (as in this case), or for an INTERNAL dependency after recursing from an EXTENSION dependency. In neither of those cases does it make sense to propagate the previous flags state. It is conceivable that we should allow AUTO dependencies to propagate here, but I'm unconvinced; I don't think the case can ever arise at present, and it certainly couldn't have arisen before (there is no case where the system will label an object with both AUTO and INTERNAL dependencies). If there is a reason to do an AUTO drop of the referenced object, it should show up in some other dependency of that object. So I'm thinking this recursive call should just pass DEPFLAG_NORMAL in all cases: findDependentObjects(&otherObject, DEPFLAG_NORMAL, stack, targetObjects, pendingObjects, depRel); I've not tested this heavily, but it fixes the example above and it passes the regression tests. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] REGRESS_OPTS default
I got burnt tonight by the fact that the earthdistance contrib module runs its regression test in database "regression", not "contrib_regression" as I was expecting and as every other contrib module does. The reason turns out to be that earthdistance/Makefile does this: REGRESS_OPTS = --extra-install=contrib/cube while pgxs.mk does this: # Calling makefile can set REGRESS_OPTS, but this is the default: ifndef REGRESS_OPTS REGRESS_OPTS = --dbname=$(CONTRIB_TESTDB) endif so of course the dbname provided here doesn't take effect and we fall back to pg_regress' default. There are at least two ways we could fix this: change earthdistance/Makefile to do this: REGRESS_OPTS = --extra-install=contrib/cube --dbname=$(CONTRIB_TESTDB) or change pgxs.mk to do this: REGRESS_OPTS += --dbname=$(CONTRIB_TESTDB) I'm leaning towards the latter as being less prone to mistakes of omission. If there's some reason for a pgxs-using makefile to override the target DB name, it can set CONTRIB_TESTDB instead of messing with REGRESS_OPTS. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] text search: restricting the number of parsed words in headline generation
> > Here is a simple patch that limits the number of words during the > > tokenization phase and puts an upper-bound on the headline generation. > > Doesn't this force the headline to be taken from the first N words of > the document, independent of where the match was? That seems rather > unworkable, or at least unhelpful. > > regards, tom lane In headline generation function, we don't have any index or knowledge of where the match is. We discover the matches by first tokenizing and then comparing the matches with the query tokens. So it is hard to do anything better than first N words. One option could be that we start looking for "good match" while tokenizing and then stop if we have found good match. Currently the algorithms that decide a good match operates independently of the tokenization and there are two of them. So integrating them would not be easy. The patch is very helpful if you believe in the common case assumption that most of the time a good match is at the top of the document. Typically a search application generates headline for the top matches of a query i.e., those in which the query terms appears frequently. So there should be atleast one or two good text excerpt matches at the top of the document. -Sushant. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Question: CREATE EXTENSION and create schema permission?
Kohei KaiGai writes: > The attached patch adds permission check at the scenario that I > explained bellow. Instead of using this patch, I changed the code to call CreateSchemaCommand itself. The test that was still missing was the one to restrict the schema name to not start with "pg_". It seemed to me that if we were treating this as a basically nonprivileged schema creation operation, that rule ought to be enforced too, as well as any other restrictions that we might someday add to CREATE SCHEMA execution. 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] text search: restricting the number of parsed words in headline generation
Sushant Sinha writes: >> Doesn't this force the headline to be taken from the first N words of >> the document, independent of where the match was? That seems rather >> unworkable, or at least unhelpful. > In headline generation function, we don't have any index or knowledge of > where the match is. We discover the matches by first tokenizing and then > comparing the matches with the query tokens. So it is hard to do > anything better than first N words. After looking at the code in wparser_def.c a bit more, I wonder whether this patch is doing what you think it is. Did you do any profiling to confirm that tokenization is where the cost is? Because it looks to me like the match searching in hlCover() is at least O(N^2) in the number of tokens in the document, which means it's probably the dominant cost for any long document. I suspect that your patch helps not so much because it saves tokenization costs as because it bounds the amount of effort spent in hlCover(). I haven't tried to do anything about this, but I wonder whether it wouldn't be possible to eliminate the quadratic blowup by saving more state across the repeated calls to hlCover(). At the very least, it shouldn't be necessary to find the last query-token occurrence in the document from scratch on each and every call. Actually, this code seems probably flat-out wrong: won't every successful call of hlCover() on a given document return exactly the same q value (end position), namely the last token occurrence in the document? How is that helpful? 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] text search: restricting the number of parsed words in headline generation
> > Actually, this code seems probably flat-out wrong: won't every > successful call of hlCover() on a given document return exactly the same > q value (end position), namely the last token occurrence in the > document? How is that helpful? > >regards, tom lane > There is a line that saves the computation state from the previous call and search only starts from there: int pos = *p;
Re: [HACKERS] Another extensions bug
I wrote: > ... So I'm thinking this recursive call should > just pass DEPFLAG_NORMAL in all cases: On further reflection, it seems more in keeping with the coding elsewhere in this module to treat this as a distinct dependency type, instead of confusing it with a NORMAL dependency. There's no actual functional difference at the moment, but more info is better than less. Hence, proposed patch attached (which also improves some of the related comments). regards, tom lane diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index c459c1e221383b47a6d4b21178b6b4d9e9b5f471..0e6165118556ca47b494ff2674f83016548a3fc8 100644 *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *** typedef struct *** 98,103 --- 98,104 #define DEPFLAG_AUTO 0x0004 /* reached via auto dependency */ #define DEPFLAG_INTERNAL 0x0008 /* reached via internal dependency */ #define DEPFLAG_EXTENSION 0x0010 /* reached via extension dependency */ + #define DEPFLAG_REVERSE 0x0020 /* reverse internal/extension link */ /* expansible list of ObjectAddresses */ *** findDependentObjects(const ObjectAddress *** 513,524 /* * The target object might be internally dependent on some other object ! * (its "owner"). If so, and if we aren't recursing from the owning ! * object, we have to transform this deletion request into a deletion ! * request of the owning object. (We'll eventually recurse back to this ! * object, but the owning object has to be visited first so it will be ! * deleted after.) The way to find out about this is to scan the ! * pg_depend entries that show what this object depends on. */ ScanKeyInit(&key[0], Anum_pg_depend_classid, --- 514,526 /* * The target object might be internally dependent on some other object ! * (its "owner"), or be a member of an extension (again considered its ! * owner). If so, and if we aren't recursing from the owning object, we ! * have to transform this deletion request into a deletion request of the ! * owning object. (We'll eventually recurse back to this object, but the ! * owning object has to be visited first so it will be deleted after.) ! * The way to find out about this is to scan the pg_depend entries that ! * show what this object depends on. */ ScanKeyInit(&key[0], Anum_pg_depend_classid, *** findDependentObjects(const ObjectAddress *** 567,573 * 1. At the outermost recursion level, disallow the DROP. (We * just ereport here, rather than proceeding, since no other * dependencies are likely to be interesting.) However, if ! * the other object is listed in pendingObjects, just release * the caller's lock and return; we'll eventually complete the * DROP when we reach that entry in the pending list. */ --- 569,575 * 1. At the outermost recursion level, disallow the DROP. (We * just ereport here, rather than proceeding, since no other * dependencies are likely to be interesting.) However, if ! * the owning object is listed in pendingObjects, just release * the caller's lock and return; we'll eventually complete the * DROP when we reach that entry in the pending list. */ *** findDependentObjects(const ObjectAddress *** 607,625 /* * 3. When recursing from anyplace else, transform this ! * deletion request into a delete of the other object. * * First, release caller's lock on this object and get ! * deletion lock on the other object. (We must release * caller's lock to avoid deadlock against a concurrent ! * deletion of the other object.) */ ReleaseDeletionLock(object); AcquireDeletionLock(&otherObject); /* ! * The other object might have been deleted while we waited to ! * lock it; if so, neither it nor the current object are * interesting anymore. We test this by checking the * pg_depend entry (see notes below). */ --- 609,627 /* * 3. When recursing from anyplace else, transform this ! * deletion request into a delete of the owning object. * * First, release caller's lock on this object and get ! * deletion lock on the owning object. (We must release * caller's lock to avoid deadlock against a concurrent ! * deletion of the owning object.) */ ReleaseDeletionLock(object); AcquireDeletionLock(&otherObject); /* ! * The owning object might have been deleted while we waited ! * to lock it; if so, neither it nor the current object are * interesting anymore. We test this by checking the * pg_depend entry (see notes below). */ *** findDependentObjects(const ObjectAddress *** 631,643 } /* ! * Okay, recur
