Re: [HACKERS] Creating indexes in the background
Since I've mentioned my use case, I might as well mention another issue I stumbled across, which is that concurrent index creation cannot happen from within trigger functions. I'm able to non-concurrently create indexes from within trigger functions. Why is there this disparity? Because concurrent index creation needs to open and commit transactions underneath, which regular index creation does not. Functions cannot run across transaction boundaries. Would having background index creation allow for this? For example, launching a background concurrent index creation could record just the actuation of the index creation in the trigger's transaction, and postgresql would actually populate the index once the trigger's transaction is committed. --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] Creating indexes in the background
Thanks. Is this something viable as a feature request? On Oct 28, 2012 7:48 AM, David Johnston pol...@yahoo.com wrote: On Oct 27, 2012, at 19:20, David Lee davidomu...@gmail.com wrote: Hey folks, It seems like right now when you want to create an index concurrently, the index creation will get canceled if you cancel the statement (i.e. you must keep your statement open). Is there a way to launch an index creation in the background so that the statement doesn't need to be kept open? --David No there is not. David J.
Re: [HACKERS] Creating indexes in the background
Thanks for all the responses. I forgot to ask in my initial post: If not already available, is background indexing a viable feature request? (This was why I sent to pgsql-hackers). My use case is a multi-tenant CMS where indexes can be created by a web front-end. Since web requests should return quickly, it would be optimal to fire off an index creation and return immediately. Since this is not available natively in postgresql, I have to have a separate worker process that handles long-running connections, and manage communication between the worker process and the web server process. Since index creation--especially concurrent index creation on tables with lots of writes--takes a long time, it would be great to be able to trigger index creation in the background, without having to dedicate a process+connection that keeps a statement open. Since I've mentioned my use case, I might as well mention another issue I stumbled across, which is that concurrent index creation cannot happen from within trigger functions. I'm able to non-concurrently create indexes from within trigger functions. Why is there this disparity? If you can't tell, my ideal goal is to be able to launch background concurrent index creation via triggers. --David On Oct 28, 2012, at 2:35 PM, Kevin Grittner kgri...@mail.com wrote: David Lee wrote: It seems like right now when you want to create an index concurrently, the index creation will get canceled if you cancel the statement (i.e. you must keep your statement open). Is there a way to launch an index creation in the background so that the statement doesn't need to be kept open? You are asking on the wrong list; this list is for discussions related to developing the PostgreSQL product itself. To ask questions about how to *use* PostgreSQL, please use one of the other lists. This sort of question belongs on the pgsql-general or pgsql-admin list. As long as I'm sending an email -- I usually run such queries on the server, either through a crontab job (if it's a recurring need), an at job (if it is a one-time off-hours need), or a background job. The specifics will depend on your operating system, which is something you should generally include in a question. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Creating indexes in the background
Hey folks, It seems like right now when you want to create an index concurrently, the index creation will get canceled if you cancel the statement (i.e. you must keep your statement open). Is there a way to launch an index creation in the background so that the statement doesn't need to be kept open? --David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Foreign key constraint on sub-column of composite-type column
Hi, I was trying to create foreign key constraints on a sub-column of a composite-type column, but couldn't find a way to do it. After asking around on IRC, it seems like this isn't supported in PostgreSQL. I wanted to do something like: create type profile as (account_id integer); create table users (profile profile); alter table users add constraint account_fk foreign key ((profile).account_id) references accounts; Would this be a viable feature request? --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] libxml incompatibility
On 6 mar, 22:44, and...@dunslane.net (Andrew Dunstan) wrote: Holger Hoffstaette wrote: On Fri, 06 Mar 2009 14:32:25 -0600, Kenneth Marshall wrote: On Fri, Mar 06, 2009 at 02:58:30PM -0500, Andrew Dunstan wrote: Yes, I discovered this a few weeks ago. [...] Maybe someone can trace the libxml calls ... not sure how exactly ... given Alvaro's example, it doesn't seem likely to me that this is due to a call to xmlCleanupParser(), but maybe the perl code invokes by simply doing use XML::LibXML; calls that for some perverse reason. I'm able to duplicate this on Postgres 8.4 (Debian Etch, XML::LibXML from CPAN). Here's the backtrace from the crash: #0 0x082f3cf1 in MemoryContextAlloc () #1 0x082c3f8a in xml_palloc () #2 0xb7dfa548 in xmlInitCharEncodingHandlers () from /usr/lib/ libxml2.so.2 #3 0xb7e0195e in xmlInitParser () from /usr/lib/libxml2.so.2 #4 0xb7dff2ef in xmlCheckVersion () from /usr/lib/libxml2.so.2 #5 0xb573af2e in boot_XML__LibXML () from /usr/local/lib/perl/5.8.8/auto/XML/LibXML/LibXML.so #6 0xb587981b in Perl_pp_entersub () from /usr/lib/libperl.so.5.8 #7 0xb5877f19 in Perl_runops_standard () from /usr/lib/libperl.so.5.8 #8 0xb5819b6e in Perl_magicname () from /usr/lib/libperl.so.5.8 #9 0xb581a844 in Perl_call_sv () from /usr/lib/libperl.so.5.8 ... Is it supposed to be OK to call xmlCheckVersion() more than once? -- DLL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UUIDs using e2fs library on Linux in 8.4
On 7 feb, 23:44, da...@fetter.org (David Fetter) wrote: On Sat, Feb 07, 2009 at 10:44:48PM -0500, David Lee Lambert wrote: In the same spirit as the FreeBSD-native UUID generator that was discussed here a couple months ago, I was able to link Postgres 8.4 against the UUID generator embedded in the Linux ext2fs toolchain. There's a license conflict issue with that patch, namely that you've used the GPL, which means we can't ship it. Is that license from something else, or would you consider re-licensing it under BSDL-compatible terms? I originally based the code on something else (the pguuid project on GBorg; the original author's Hotmail address seems dead). However, I just checked, and no line of code what I posted matches his code, except some #include lines and function declarations. I am willing to apply the BSD license to it if I actually can claim the copyright. -- DLL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] UUIDs using e2fs library on Linux in 8.4
In the same spirit as the FreeBSD-native UUID generator that was discussed here a couple months ago, I was able to link Postgres 8.4 against the UUID generator embedded in the Linux ext2fs toolchain. My code is posted at http://www.lmert.com/download/pguuid-for-8.4.tar.gz Unlike on FreeBSD, there isn't a link-conflict issue that requires this; it's just Another Way To Do It. -- David Lee Lambert ... Software Developer, member IEEE, ACM Cell phone: +1 586-873-8813 GPG key at http://www.lmert.com/keyring.txt IM: davidleelambert (Yahoo!) or lambe...@cse.msu.edu (MSN) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] uuids on freebsd
On Dec 17, 2:30 pm, Andrew Gierth and...@tao11.riddles.org.uk wrote: Has anyone ever managed to get uuid generation working on FreeBSD? [...] ([...] The only solution I could come up with was to knock off a quick uuid-freebsd module that uses the base system uuid functions rather than the ossp ones. I could put this on pgfoundry if there isn't likely to be a real fix in the near future.) +1 for putting it on pgFoundry. I managed to hack the old contrib/uuid so it would use the functions from Linux e2fs uuid on 8.0/8.1/8.2. I might have time soon to see if I can get that to work on 8.3. -- DLL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Optimizing DISTINCT with LIMIT
On Thursday 04 December 2008 15:09, Gregory Stark wrote: tmp [EMAIL PROTECTED] writes: Also, it is my impression that many people use LIMIT to minimize the evaluation time of sub queries from which the outer query only needs a small subset of the sub query output. I've seen lots of queries which only pull a subset of the results too -- but it's always a specific subset. So that means using ORDER BY or a WHERE clause to control it. I use ORDER BY random() LIMIT :some_small_number frequently to get a feel for data. That always builds the unrandomized relation and then sorts it. I guess an alternate path for single-table queries would be to randomly choose a block number and then a tuple number; but that would be biased toward long rows (of which fewer can appear in a block). -- David Lee Lambert ... Software Developer Cell phone: +1 586-873-8813 ; alt. email [EMAIL PROTECTED] or [EMAIL PROTECTED] GPG key at http://www.lmert.com/keyring.txt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Friday 05 December 2008 03:55, Peter Eisentraut wrote: Kurt Harriman wrote: Sometimes people would like to call C++ code in the PostgreSQL backend environment... for example, in user-defined functions, triggers, access methods. And there is sometimes a need for C++ code to call back into PostgreSQL's C functions, such as the SPI interface. Have you considered writing a procedural language plugin for C++? PostgreSQL supports a lot of extension languages, and none of them require the amount of backend changes that you outline here, because the PL plugin serves as glue. I think this patch is great, although I haven't had time to test it yet. The only real backend change is the exception-handling clause; and the fact that the backend will also be linked against the C++ runtime library. Everything else is routine stuff that an experienced C++ developer would end up catching while trying to get his build-system for a new project running; but it could certainly scare away someone with less experience. Better to deal with this way ahead of time and test it on a few platforms. -- David Lee Lambert ... Software Developer Cell phone: +1 586-873-8813 ; alt. email [EMAIL PROTECTED] or [EMAIL PROTECTED] GPG key at http://www.lmert.com/keyring.txt pgp3zsixMgumQ.pgp Description: PGP signature
Re: [HACKERS] Mostly Harmless: Welcoming our C++ friends
On Friday 05 December 2008 10:45, Kurt Harriman wrote: Tom Lane wrote: Kurt Harriman [EMAIL PROTECTED] writes: [ make the backend C++-compilable ] I tested applying this patch to CVS HEAD today and compiling with --enable-cplusplus with gcc 4.2: $ ldd postmaster ... libstdc++.so.6 = /usr/lib/libstdc++.so.6 (0xb7bf9000) ... Then I ran pgbench and played with a table with a UUID column. Performance was great. (I first mistakenly applied it against a not-up-to-date source-tree --- something from mid-September --- and that ended up not compiling.) I still have not tried this with my own C++ code, but it seems to have less impact on the build process than some might have feared. -- David Lee Lambert ... Software Developer Cell phone: +1 586-873-8813 ; alt. email [EMAIL PROTECTED] or [EMAIL PROTECTED] GPG key at http://www.lmert.com/keyring.txt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
