Re: [GENERAL] Inheritance efficiency
2010/4/30 David Fetter da...@fetter.org: On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote: No info about this point (partial indexes)? Is also this geared with linear algorithms ? Should I move to an enterprise grade version of PostgreSQL? The enterprise grade version of PostgreSQL is the community version. Proprietary forks exist, but they don't fix this kind of problem. :) Hmmm ... I think this is the kind of problems that keeps PostgreSQL away from the enterprise grade world. The ability to cope with thousands of DB objects like (child-)tables, indexes, functions and so on with O(1) or at least O(log(n)) complexity is among the key points. For example, the Linux kernel made the big jump with server hardware thanks also to the O(1) schedulers. In this specific case, if you think about inheritance for partitioning and you stick with the example idea of one partition per month, then the current solution is more than OK. In the real world, that is not really the general case, especially in the enterprise grade world, where maybe you partition with both a time stamp and another column, like product code ranges and prefixes ... Is there any planning about this improvement? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: FW: [GENERAL] Java Memory Issue while Loading Postgres library
Hi Did you eventually figure out what was wrong? Was it just that you were trying to load a full result set and running out of memory with an OutOfMemoryError? Or was the jvm truly crashing rather than just throwing OutOfMemoryError? -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inheritance efficiency
2010/4/30 Vincenzo Romano vincenzo.rom...@notorand.it: 2010/4/30 David Fetter da...@fetter.org: On Thu, Apr 29, 2010 at 11:29:36AM +0200, Vincenzo Romano wrote: No info about this point (partial indexes)? Is also this geared with linear algorithms ? Should I move to an enterprise grade version of PostgreSQL? The enterprise grade version of PostgreSQL is the community version. Proprietary forks exist, but they don't fix this kind of problem. :) Hmmm ... I think this is the kind of problems that keeps PostgreSQL away from the enterprise grade world. The ability to cope with thousands of DB objects like (child-)tables, indexes, functions and so on with O(1) or at least O(log(n)) complexity is among the key points. For example, the Linux kernel made the big jump with server hardware thanks also to the O(1) schedulers. In this specific case, if you think about inheritance for partitioning and you stick with the example idea of one partition per month, then the current solution is more than OK. In the real world, that is not really the general case, especially in the enterprise grade world, where maybe you partition with both a time stamp and another column, like product code ranges and prefixes ... Is there any planning about this improvement? Could it be possible to just make some changes (adding indexes) to the information schema to gain this enterprise gradeness? -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: FW: [GENERAL] Java Memory Issue while Loading Postgres library
-Original Message- From: Craig Ringer [mailto:cr...@postnewspapers.com.au] Sent: Friday, April 30, 2010 12:01 PM To: Arya, Ashish Cc: pgsql-general@postgresql.org; Bhattacharya, A Subject: Re: FW: [GENERAL] Java Memory Issue while Loading Postgres library Hi Did you eventually figure out what was wrong? Was it just that you were trying to load a full result set and running out of memory with an OutOfMemoryError? Or was the jvm truly crashing rather than just throwing OutOfMemoryError? -- Craig Ringer --- Thanks Craig for your inputs. However, we identified the problem as OutOfMemoryError and it was thrown from Java because of unnecessary Raise info message from our program. Thus we have set the client_min_messages and log_min_messages to 'error' level and switched off the messages from our program has solved the problem. Please if anyone face the same issue refer to the below link. http://archives.postgresql.org/pgsql-jdbc/2009-01/msg00068.php Many thanks AB -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Writing SRF
On Thu, Apr 29, 2010 at 8:03 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jorge Arevalo jorgearev...@gis4free.org writes: Many thanks! That was one of my errors. Another one was this: char szDataPointer[10]; sprintf(szDataPointer, %p, a_pointer); These lines caused a memory error. That looks all right in itself (unless you're on a 64-bit machine, in which case you need a bigger array to hold %p output). However the array would only live as long as the function it's in. What were you doing with the data afterwards, returning it maybe? regards, tom lane Thanks for the tip. And about the data pointed by this address, is copied in a safe place (I hope...) before using it to construct the data that will be returned. Just now, it's working, but I'll be careful. Many thanks again! Best regards, Jorge -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Writing SRF
On Thu, Apr 29, 2010 at 8:08 PM, Martin Gainty mgai...@hotmail.com wrote: it has been years since i've mucked in the C++ swamp but that means your (near) heap is ok but you're stack is hosed.. probably specific to compiler (version) and Operating System(version) and environment settings..ping back if you are still experiencing those problems with those configuration settings Ok, now it's working. In GNU/Linux with gcc 4.4.1, and I hope in Windows XP too (it will be tested). Many thanks! Saludos Cordiales desde EEUU! Martin Gainty ¡Saludos desde España también! Jorge __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: jorgearev...@gis4free.org Date: Thu, 29 Apr 2010 19:45:41 +0200 Subject: Re: [GENERAL] Writing SRF To: t...@sss.pgh.pa.us CC: pgsql-general@postgresql.org On Thu, Apr 29, 2010 at 3:56 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jorge Arevalo jorgearev...@gis4free.org writes: Yes. For example, the function expects 2 arguments, and it's called with 2 arguments: 1 composite type (following this format https://svn.osgeo.org/postgis/spike/wktraster/doc/RFC1-SerializedFormat) and one integer. But PG_NARGS() returns a really big value (16297) when I first check the number of arguments at the beginning of the function. Has sense? Given only that data point, I would guess that you forgot to mark the function as being called with V1 protocol (PG_FUNCTION_INFO_V1). regards, tom lane Many thanks! That was one of my errors. Another one was this: char szDataPointer[10]; sprintf(szDataPointer, %p, a_pointer); These lines caused a memory error. I changed them for: char * pszDataPointer; pszDataPointer = (char *)allocator(10 * sizeof(char)); sprintf(pszDataPointer, %p, a_pointer); Meaning allocator a memory allocator in a valid memory context for PostgreSQL. And seems to work :-). Is the static memory dangerous in a PostgreSQL memory context? Thanks again! Jorge -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. Get busy. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Select with string that has a lone hyphen yields nothing
Hi The hyphen which written in 'Olympus E-PL1' is different from the one which written in 'Camera - Black'. em-dash http://www.fileformat.info/info/unicode/char/2014/index.htm en-dash http://www.fileformat.info/info/unicode/char/2013/index.htm figure-dash http://www.fileformat.info/info/unicode/char/2012/index.htm I have no idea to fix using PostgreSQL's function,because they don't equal. I think you have to change the data or change the behavior of your application . Thank you. I have a product names table like this: datab=# select product_id, name from table.product_synonyms where name ilike '%%olympus e-pl1%%'; product_id | name +--- 8736 | Olympus E-PL1 8736 | Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera – Black (Body Only) (Call for pre-order. Available on: 2010-04-09) 8736 | Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera – Blue (Body Only) 8736 | Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera w/ ED 14-42mm f3.5-5.6 (champagne/gold) (4 rows) Any select statement prior to the hyphen yields a result, but from the hyphen on yields nothing: datab=# select product_id, name from table.product_synonyms where name ilike '%%Olympus E-PL1 Interchangeable Lens Type Live View Digital Camera - Blue %%'; product_id | name +-- (0 rows) Any ideas how to fix this? -- Kenichiro Tanaka K.K.Ashisuto http://www.ashisuto.co.jp/english/index.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required)
Hi chaps, I've just upgraded a server from 8.3 to 8.4, and when trying to use the parallel restore options I get the following error: pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required) The dump I'm trying to restore is purely a data dump, and the schema is separate (due to the way our setup works). These are the options I'm using for the dump and the restore: pg_dump -Fc dbname -U postgres -h localhost -a --disable-triggers pg_restore -U postgres --disable-triggers -j 4 -c -d dbname can anyone tell me what I'm doing wrong, or why my files are not supported by parallel restore? Thanks Glyn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Cumulative count (running total) window fn
On 29 Apr 2010, at 19:21, Oliver Kohll - Mailing Lists wrote: The two plans (note I've been rewriting the field names for readability until now but haven't here): explain analyze SELECT year, sum(c) over (order by year) FROM ( SELECT extract(year from a56b7a8d6de03f67b) AS year, count(a10e4ab8863c199f1) AS c FROM a2e9a7e9e257153de GROUP BY extract(year from a56b7a8d6de03f67b) ) as subq; Oh my, how can you work with such column and table names? You and any colleagues you may have will probably appreciate having a few views over those tables that translate that gibberish to human readable stuff. You could go further and make those views updatable (by means of a few rules), but then you run the risk that colleagues start to hug you... Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4bdaabce10411378620886! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Java Memory Issue while Loading Postgres library
a.bhattacha...@sungard.com wrote: We have a java exe making a call to a postgres function. This postgres function internally makes a call to a dll (which is written using Postgres extended C). Now the issue is that, when we make a call to this dll, it consumes a lot of memory and this memory is getting consumed from the heap space of the original java process causing an out of memory exception in Java. Is this a known issue. Do we have a way to keep these processes disjoint and not eat up heap space of the original process? It seems like your Java code doesn't release the resources (for instance, not closing result sets, or something similar). If you could post relevant parts of Java code that would be helpful. Also post the versions of Postgres and Java you're using. Regards, Ognjen -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Indexing queries with bit masks
I want a column in my Users table that will keep track of which types of notifications the user wants to subscribe to. There's probably about 10 different types, so I don't want to have 10 boolean columns because this seems kinda hacky and makes adding new types more work. So I'm thinking about using a 32bit integer type and storing the data as a bitmask. When a certain event happens, let's say event 4, I need to query for which users to notify. So I'll be doing something like: SELECT UserId FROM Users WHERE Subscriptions 8; (I haven't checked this syntax but I'm assuming that's how you do it).. My question is say there's a million rows in the Users table. If I have an index on Subscriptions, will this index be used in the above query? Is there another good way to make this query super fast, or is my approach totally dumb? I haven't implemented this yet so I'm open to new clever ideas. Thanks!! Mike
[GENERAL] Nuevo sobre PGday Latinoamericano 2011...
En función de las propias sugerencias realizadas por los interesados en el tema, ya está actualizado y disponible para todos los usuarios en la información relacionada con el PGday Latinoamericano 2011 la oferta de la cadena de turismo Cubanacan , para dar cobertura a todos los colegas que desde el exterior quieran participar en nuestro evento. La dirección para acceder es la siguiente: http://postgresql.uci.cu/news/19 Saludos, Ing.Yunior Mesa Reyes Postgre-SQL Empresarial. DATEC Universidad de las Ciencias Informáticas.Ciudad de la Habana. Cuba. «Se tu el cambio que quieres ver en el mundo...El éxito es el fracaso superado por la perseverancia»
Re: [GENERAL] How many threads/cores Postgres can utilise?
Greg Smith wrote: Piotr Kublicki wrote: We're thinking about installing Postgres on a virtual machine (RedHat 5 64-bits), however not sure how many CPUs can be wisely assigned, without wasting of resources. The database will use as many cores as you have available, so long as you have multiple simultaneous queries to keep each of them busy--no single query will use more than one core. In practice, on a VM install you may discover you're limited by either I/O rate or VM overhead long before you reach the scalability limits of the database though. I thought we had an FAQ item on this topic, but it seems it was removed or was never there. :-( I have added one: http://wiki.postgresql.org/wiki/FAQ#How_does_PostgreSQL_use_CPU_resources.3F I also added information to the FAQ about focusing on I/O and memory issues before CPU: http://wiki.postgresql.org/wiki/FAQ#What_computer_hardware_should_I_use.3F -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inheritance efficiency
On Fri, Apr 30, 2010 at 08:44:26AM +0200, Vincenzo Romano wrote: Should I move to an enterprise grade version of PostgreSQL? The enterprise grade version of PostgreSQL is the community version. Proprietary forks exist, but they don't fix this kind of problem. :) Hmmm ... I think this is the kind of problems that keeps PostgreSQL away from the enterprise grade world. The ability to cope with thousands of DB objects like (child-)tables, indexes, functions and so on with O(1) or at least O(log(n)) complexity is among the key points. For example, the Linux kernel made the big jump with server hardware thanks also to the O(1) schedulers. In this specific case, if you think about inheritance for partitioning and you stick with the example idea of one partition per month, then the current solution is more than OK. In the real world, that is not really the general case, especially in the enterprise grade world, where maybe you partition with both a time stamp and another column, like product code ranges and prefixes ... Is there any planning about this improvement? Could it be possible to just make some changes (adding indexes) to the information schema to gain this enterprise gradeness? Your assertion that PostgreSQL is not enterprise grade is simply false. For years, it has been and continues to be used as the basis of extremely large mission-critical systems. That said, if you wish to make changes, or propose that some be made, please feel free to do so after 9.0 comes out. In the mean time, please test 9.0beta1 along with any ensuing betas and release candidates, and report back the results of the aforementioned testing. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Problem Changing search_path in pgTAP tests
Hi. I am running into a problem when trying to run pgTAP tests. Basically, there are two functions with the same name in different schemas, and I'm trying to get different versions at different times by modifying the search_path. However, there seems to be a case where postgres is caching the functions, causing it to find the wrong function after the search path has been changed. Here is a more detailed description - There are two pgTAP tests, A and B. They both run the same function in the public schema, C. C runs a function D. There are two versions of D (D1 and D2), in two different schemas (S1 and S2). Test A sets the search path to include S1, but not S2, so when C runs, it should run D1, not D2. This works correctly. Test B sets the search path to include S2, but not S1, so when C runs, it should run D2, not D1. In this case, D1 is still what gets run. A print statement confirms that D1 is being run, and that, in the function D1, the search path includes S2, but not S1. It will work correctly if A and B just attempt to run D directly. This problem only happens when A and B run C, which in turn runs D. This problem also won't occur if some function runs D, changes the search path, and runs D again. I have a small database that will reproduce this issue. It contains 7 functions (and the pgTAP functions) - some are there to reproduce the issue, others are there to show control cases where the problem doesn't happen. I won't post it here in case attachements don't come through correctly, but if anyone is interested please let me know and I will be happy to email it directly to you. We are running on Postgres 8.4.1 on Mac. Also, for anyone not familiar with pgTAP, here is the website - http://pgtap.projects.postgresql.org/ - Scott Sturdivant -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Nuevo sobre PGday Latinoamericano 2011...
2010/4/30 Ing. Yunior Mesa Reyes ymre...@uci.cu: En función de las propias sugerencias realizadas por los interesados en el tema, ya está actualizado y disponible para todos los usuarios en la información relacionada con el PGday Latinoamericano 2011 la oferta de la cadena de turismo Cubanacan, para dar cobertura a todos los colegas que desde el exterior quieran participar en nuestro evento. La dirección para acceder es la siguiente: http://postgresql.uci.cu/news/19 Saludos, Ing.Yunior Mesa Reyes Postgre-SQL Empresarial. DATEC Universidad de las Ciencias Informáticas.Ciudad de la Habana. Cuba. «Se tu el cambio que quieres ver en el mundo...El éxito es el fracaso superado por la perseverancia» pgsql-general@postgresql.org, pgsql-annou...@postgresql.org are english language lists. So Spanish is not really the maximum here ... -- Vincenzo Romano NotOrAnd Information Technologies NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] timestamp convert to date
Hi List ! I'm running PostgreSQL 8.3.10 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Ubuntu 4.3.3-5ubuntu4) 4.3.3 (Ubuntu 9.04) I want to use -infinity,infinity as my date interval maximum endpoints in an application I'm writing . Is it possible to use date ? I did a test but it looks like date doesn't support infinity as a value. However this looks like it doesn't give an error either: select 'infinity'::timestamp::date; it just gives me a blank row in pgadmin3 query window. So is infinity supposed to work with date or should this conversion give an error ? It would be really nice with the possibility to use infinity with the date type. Regards //Dan
Re: [GENERAL] pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required)
Glyn Astill glynast...@yahoo.co.uk writes: I've just upgraded a server from 8.3 to 8.4, and when trying to use the parallel restore options I get the following error: pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required) This is the second or third report we've gotten of that, but nobody's been able to offer a reproducible test case. Can you? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timestamp convert to date
Dan S strd...@gmail.com writes: I did a test but it looks like date doesn't support infinity as a value. Try 8.4 or later. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing queries with bit masks
Mike Christensen m...@kitchenpc.com writes: When a certain event happens, let's say event 4, I need to query for which users to notify. So I'll be doing something like: SELECT UserId FROM Users WHERE Subscriptions 8; My question is say there's a million rows in the Users table. If I have an index on Subscriptions, will this index be used in the above query? No. At least not with a standard btree index. I'm not exactly sure that an index would be helpful at all --- it seems like the selectivity of this condition won't be very good anyway, will it? The more popular notifications will be subscribed to by a large fraction of the user base. Maybe it'd be useful to index unpopular notifications, but how often will you be searching for those? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing queries with bit masks
On Fri, Apr 30, 2010 at 10:08 AM, Tom Lane t...@sss.pgh.pa.us wrote: Mike Christensen m...@kitchenpc.com writes: When a certain event happens, let's say event 4, I need to query for which users to notify. So I'll be doing something like: SELECT UserId FROM Users WHERE Subscriptions 8; My question is say there's a million rows in the Users table. If I have an index on Subscriptions, will this index be used in the above query? No. At least not with a standard btree index. I'm not exactly sure that an index would be helpful at all --- it seems like the selectivity of this condition won't be very good anyway, will it? The more popular notifications will be subscribed to by a large fraction of the user base. Maybe it'd be useful to index unpopular notifications, but how often will you be searching for those? We've got some similar columns (though nothing with any major number of rows), so this is interesting... If all subscriptions are roughly equal in popularity then any single select should give ~ 10% of the data. That would seem to be selective enough that you'd really want an index? If so, any answers to the OP's main question; what would be the most efficient way to handle this type of thing? -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Function to Table reference
Hi All - Is there a way to find which functions are being used by table. Ex :- If there are functions fnc_a, fnc_b, fnc_c and table A is used in fnc_a and fnc_c, How can we find that ? can you please help? regards
[GENERAL] savepoints with the same name
I was thinking thinking about the issue asked here, about an error in a query causing the whole transaction to abort, http://stackoverflow.com/questions/2741919/can-i-ask-postgresql-to-ignore-errors-within-a-transaction/2745677 which has already bothered so many postgresql users and has been discussed before (it's certainly not a bug, I know). I wonder if the suggestion I (leonbloy) gave, of adding a SAVEPOINT after each insert (when doing interactive work) is reasonable. In particular, after reading this message http://archives.postgresql.org/pgsql-general/2009-07/msg00636.php If you savepoint every single insert, you'll wind up begin much much slower... Does that also apply when I issue a SAVEPOINT with the same name? Does the new savepoint release the previous and create a new one, or does it move the previous? Or it's just that a new one is created and the name shadows the previous, so that there are still N active savepoints in the transaction, taking up memory/resources ? Hernán J. González http://hjg.com.ar/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inheritance efficiency
Vincenzo Romano wrote: In this specific case, if you think about inheritance for partitioning and you stick with the example idea of one partition per month, then the current solution is more than OK. In the real world, that is not really the general case, especially in the enterprise grade world, where maybe you partition with both a time stamp and another column, like product code ranges and prefixes ... Is there any planning about this improvement? Of course. People is always looking to make improvements in many areas. There are very few things that people consider to be more than OK. The partitioning features are among those being more examined for possibly improvements. This does *not* mean that PostgreSQL doesn't serve mission critical systems already, on enterprises large and small, some of them on very large systems. What you see in these lists (people describing partition by month schemes) are not necessarily the most complex setups out there. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function to Table reference
(anonymous) wrote: Is there a way to find which functions are being used by table. Ex :- If there are functions fnc_a, fnc_b, fnc_c and table A is used in fnc_a and fnc_c, How can we find that ? can you please help? Basically, you can't. Functions are more or less black boxes to PostgreSQL. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function to Table reference
got it.. Thank you On Fri, Apr 30, 2010 at 12:17 PM, Tim Landscheidt t...@tim-landscheidt.dewrote: (anonymous) wrote: Is there a way to find which functions are being used by table. Ex :- If there are functions fnc_a, fnc_b, fnc_c and table A is used in fnc_a and fnc_c, How can we find that ? can you please help? Basically, you can't. Functions are more or less black boxes to PostgreSQL. Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing queries with bit masks
Peter Hunsberger peter.hunsber...@gmail.com writes: If all subscriptions are roughly equal in popularity then any single select should give ~ 10% of the data. That would seem to be selective enough that you'd really want an index? My personal rule of thumb is that 10% is around the threshold where indexes stop being very helpful. At that selectivity, you're going to be having to read every page of the table anyway, and it's not clear that the extra I/O to read the index is going to get repaid in CPU savings. (Now if the table+index are fully cached in RAM, the threshold's probably a bit higher, but there still is not reason to think that an index is going to make for a huge improvement.) If so, any answers to the OP's main question; what would be the most efficient way to handle this type of thing? Well, btree's right out for indexing bit selections. In principle you could maybe do something with a GIN index, but I don't think we ship any prefab GIN opclasses for this. [ thinks for a bit ] The best idea that comes to mind offhand is to not use an integer, but a boolean array, such that the queries look like select ... where subscriptions[4]; This already gives you one big advantage, which is that you're not hard-wiring an assumption about how many notification types there can ever be. What I would then do is build a separate partial index for each subscription column, ie, create index ... where subscriptions[1]; create index ... where subscriptions[2]; .. etc .. Now this only works as long as the queries are referencing explicit constant subscription numbers, else the planner won't be able to match the WHERE clause to any of the partial indexes. But if that is a reasonable restriction for your app then it seems like it should work. The main disadvantage of this is that you need N indexes, which could get a bit expensive if the table is updated heavily. But you don't need to bother maintaining indexes corresponding to subscriptions that are too popular to be worth indexing, so some of that could be bought back by careful index selection. Another point is that the partial indexes could be created on some other column(s) and thereby serve double duty. This depends on the details of your typical queries though. Is the subscriptions[] clause usually used by itself, or together with additional WHERE conditions? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] information_schema.parameters
why specific_name column on that view contains also OID ? This makes two databases that are identical, have different values there. Is there any specific reason for that ? -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Function to Table reference
Tim Landscheidt t...@tim-landscheidt.de writes: (anonymous) wrote: Is there a way to find which functions are being used by table. Ex :- If there are functions fnc_a, fnc_b, fnc_c and table A is used in fnc_a and fnc_c, How can we find that ? can you please help? Basically, you can't. Functions are more or less black boxes to PostgreSQL. You could possibly grep all the functions' source code for references to the particular table you care about, eg select ... from pg_proc where prosrc ~ 'mytable' but bear in mind that this could miss dynamically-constructed queries. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] savepoints with the same name
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 I was thinking thinking about the issue asked here, about an error in a query causing the whole transaction to abort, ... I wonder if the suggestion I (leonbloy) gave, of adding a SAVEPOINT after each insert (when doing interactive work) is reasonable. Not only reasonable, but already implemented inside of psql. Just type: \set ON_ERROR_ROLLBACK on inside your psql session, and it will automatically create a savepoint before each command, and thus allow your transaction to continue even if you encounter errors. If you savepoint every single insert, you'll wind up begin much much slower... Does that also apply when I issue a SAVEPOINT with the same name? Does the new savepoint release the previous and create a new one, or does it move the previous? Or it's just that a new one is created and the name shadows the previous, so that there are still N active savepoints in the transaction, taking up memory/resources ? Yes, savepoints will slow things down, but probably not as much as you are fearing. Savepoints will nest (or shadow). The ON_ERROR_ROLLBACK feature creates the savepoint before the query, then either does a ROLLBACK TO or a RELEASE depending on the success of the query, so there is no build up of savepoints. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201004301245 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkvbCVIACgkQvJuQZxSWSsi4nwCdH8xwQ3RpVlD65I239hs/eAbW V3oAniaEv2VWFkrrhqDU9HDlCRMv1ROx =i0P6 -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required)
--- On Fri, 30/4/10, Tom Lane t...@sss.pgh.pa.us wrote: Glyn Astill glynast...@yahoo.co.uk writes: I've just upgraded a server from 8.3 to 8.4, and when trying to use the parallel restore options I get the following error: pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required) This is the second or third report we've gotten of that, but nobody's been able to offer a reproducible test case. Can you? Hi Tom, The schema is fairly large, but I will try. One thing I forgot to mention is that in the restore script I drop the indexes off my tables between restoring the schema and the data. I've always done this to speed up the restore, but is there any chance this could be causing the issue? I guess what would help is some insight into what the error message means. It appers to orginate in _PrintTocData in pg_backup_custom.c, but I don't really understand what's happening here at all, a wild guess is it's trying to seek to a particular toc entry in the file? or process the file sequentially? http://doxygen.postgresql.org/pg__backup__custom_8c.html#6024b8108422e69062072df29f48506f Glyn -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inheritance efficiency
2010/4/30 Alvaro Herrera alvhe...@commandprompt.com: Vincenzo Romano wrote: In this specific case, if you think about inheritance for partitioning and you stick with the example idea of one partition per month, then the current solution is more than OK. In the real world, that is not really the general case, especially in the enterprise grade world, where maybe you partition with both a time stamp and another column, like product code ranges and prefixes ... Is there any planning about this improvement? Of course. People is always looking to make improvements in many areas. There are very few things that people consider to be more than OK. The partitioning features are among those being more examined for possibly improvements. This does *not* mean that PostgreSQL doesn't serve mission critical systems already, on enterprises large and small, some of them on very large systems. What you see in these lists (people describing partition by month schemes) are not necessarily the most complex setups out there. Hi. I've nerver meant to say that PG is not mission critical! I argued that O(n) stuff will keep it away from enterprise grade applications. I've been told earlier that It is fine for dozens of child tables, but not thousands; it does need improvement. This is not enterprise grade. And the same could go for (a large number of) partial indexes. Any idea here? Infact I have in mind also a different approach to partitioning which could be useful (under certain constraints, of course). Instead of partitioning the table itself, you can partition the indexes. The data can still be in a single table (for the sake of some FKs for example). Just the indexes get partitioned· But, of course, a lot depends on whether the selection of the right indexes (among thousands) is effective or not. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.rom...@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it NON QVIETIS MARIBVS NAVTA PERITVS -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required)
Glyn Astill wrote: One thing I forgot to mention is that in the restore script I drop the indexes off my tables between restoring the schema and the data. I've always done this to speed up the restore, but is there any chance this could be causing the issue? Uh. Why are you doing that? pg_restore is supposed to restore the schema, then data, finally indexes and other stuff. Are you using separate schema/data dumps? If so, don't do that -- it's known to be slower. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required)
--- On Fri, 30/4/10, Alvaro Herrera alvhe...@commandprompt.com wrote: Uh. Why are you doing that? pg_restore is supposed to restore the schema, then data, finally indexes and other stuff. Are you using separate schema/data dumps? If so, don't do that -- it's known to be slower. Yes, I'm restoring the schema first, then the data. The reason being that the data can come from different slony 1.2 slaves, but the schema always comes from the origin server due to modifications slony makes to schemas on the slaves. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_restore: [custom archiver] dumping a specific TOC data block out of order is not supported without ID on this input stream (fseek required)
Glyn Astill glynast...@yahoo.co.uk writes: The schema is fairly large, but I will try. My guess is that you can reproduce it with not a lot of data, if you can isolate the trigger condition. One thing I forgot to mention is that in the restore script I drop the indexes off my tables between restoring the schema and the data. I've always done this to speed up the restore, but is there any chance this could be causing the issue? Possibly. I think there must be *something* unusual triggering the problem, and maybe that is it or part of it. I guess what would help is some insight into what the error message means. It's hard to tell. The likely theories are (1) we're doing things in an order that requires seeking backwards in the file, and for some reason pg_restore thinks it can't do that; (2) there's a bug causing the code to search for a item number that isn't actually in the file. One of the previous reports actually turned out to be pilot error: the initial dump had failed after emitting a partially complete file, and so the error from pg_restore was essentially an instance of (2). But with three or so reports I'm thinking there's something else going on. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] temp tables
Do temp tables need to be explicitly dropped, or do the go away when the process that created them leaves? -- Until later, Geoffrey I predict future happiness for America if they can prevent the government from wasting the labors of the people under the pretense of taking care of them. - Thomas Jefferson -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing queries with bit masks
On Fri, Apr 30, 2010 at 11:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Hunsberger peter.hunsber...@gmail.com writes: If all subscriptions are roughly equal in popularity then any single select should give ~ 10% of the data. That would seem to be selective enough that you'd really want an index? My personal rule of thumb is that 10% is around the threshold where indexes stop being very helpful. At that selectivity, you're going to be having to read every page of the table anyway, and it's not clear that the extra I/O to read the index is going to get repaid in CPU savings. (Now if the table+index are fully cached in RAM, the threshold's probably a bit higher, but there still is not reason to think that an index is going to make for a huge improvement.) If so, any answers to the OP's main question; what would be the most efficient way to handle this type of thing? Ok, that makes sense, which immediately makes me wonder if partitions might make sense for this use case? In particular if there really are only 10 different types? [...] The best idea that comes to mind offhand is to not use an integer, but a boolean array, such that the queries look like select ... where subscriptions[4]; Interesting idea. That might be worth testing for some of my use cases -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] temp tables
In response to Geoffrey : Do temp tables need to be explicitly dropped, or do the go away when the process that created them leaves? The latter one. But explicitely delete them isn't an error. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] timestamp convert to date
Thank you very much for the quick answer ! I'm considering installing the upcoming 9.0 beta instead of 8.4. Will it be available as an installable ubuntu 9.04 package ? I have not tried to install a beta release before so I'm a bit worried about crashing my 8.3.10 install which works now. Is there a description or manual page on how to install a beta in paralell with my 8.3.10 installation ? regards //Dan 2010/4/30 Tom Lane t...@sss.pgh.pa.us Dan S strd...@gmail.com writes: I did a test but it looks like date doesn't support infinity as a value. Try 8.4 or later. regards, tom lane
Re: [GENERAL] Inheritance efficiency
Vincenzo Romano wrote: This is not enterprise grade. Enterprise grade is nothing but a buzzword. Oh, it's also a moving target. We've been not enterprise grade for years, always one feature behind (and strangely, the one lacking feature is always the one of interest to the complainant). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Native DB replication for PG
I believe v9 will have native DB master/slave DB replication (correct if wrong). If so, what's the best guess on when will v9 be released? Thanks!
Re: [GENERAL] Native DB replication for PG
On Fri, Apr 30, 2010 at 2:17 PM, Gauthier, Dave dave.gauth...@intel.com wrote: I believe v9 will have native DB master/slave DB replication (correct if wrong). If so, what’s the best guess on when will v9 be released? well, depends on how you define replication, but yes. my _guess_ on release is late summer. the key event to watch for is entering beta then you can figure 1-2 months. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Native DB replication for PG
On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave dave.gauth...@intel.com wrote: I believe v9 will have native DB master/slave DB replication (correct if wrong). If so, what’s the best guess on when will v9 be released? If I had to plan server deployments for the next year (and I do) I'd be sticking with pg 8.3 and a proven replication engine. Next summer I'll be seriously considering 9.0 and hot PITR slaves. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Native DB replication for PG
On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave dave.gauth...@intel.com wrote: I believe v9 will have native DB master/slave DB replication (correct if wrong). If so, what's the best guess on when will v9 be released? If I had to plan server deployments for the next year (and I do) I'd be sticking with pg 8.3 and a proven replication engine. Next summer Surely you mean 8.4? :-) Ray. I googled postgres 8.4 feathres and found no mention of DB replication support at... http://www.postgresql.org/about/press/features84.html -Original Message- From: Raymond O'Donnell [mailto:r...@iol.ie] Sent: Friday, April 30, 2010 4:39 PM To: Scott Marlowe Cc: Gauthier, Dave; pgsql-general@postgresql.org Subject: Re: [GENERAL] Native DB replication for PG On 30/04/2010 21:30, Scott Marlowe wrote: On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave dave.gauth...@intel.com wrote: I believe v9 will have native DB master/slave DB replication (correct if wrong). If so, what's the best guess on when will v9 be released? If I had to plan server deployments for the next year (and I do) I'd be sticking with pg 8.3 and a proven replication engine. Next summer Surely you mean 8.4? :-) Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Native DB replication for PG
On Fri, 2010-04-30 at 13:42 -0700, Gauthier, Dave wrote: On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave dave.gauth...@intel.com wrote: I believe v9 will have native DB master/slave DB replication (correct if wrong). If so, what's the best guess on when will v9 be released? If I had to plan server deployments for the next year (and I do) I'd be sticking with pg 8.3 and a proven replication engine. Next summer Surely you mean 8.4? :-) No, I would buy the 8.3 argument as well. Depending on your conservative level. 8.4 is fine and all but 8.3 is about as rock solid as it gets. It is a great baseline at this point. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inheritance efficiency
Alvaro Herrera wrote: Vincenzo Romano wrote: This is not enterprise grade. Enterprise grade is nothing but a buzzword. Oh, it's also a moving target. We've been not enterprise grade for years, always one feature behind (and strangely, the one lacking feature is always the one of interest to the complainant). We do have this enhancement coming in Postgres 9.0: Add an index on pg_inherits.inhparent, and use it to avoid seqscans in find_inheritance_children(). This is a complete no-op in databases without any inheritance. In databases where there are just a few entries in pg_inherits, it could conceivably be a small loss. However, in databases with many inheritance parents, it can be a big win. However, I don't think this going to help a lot for partitioning because the cost is mostly checking the CHECK constraints, not finding the table's children. Like all Postgres missing features, we just need someone with time to volunteer to research and fix it. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Native DB replication for PG
On Fri, Apr 30, 2010 at 2:38 PM, Raymond O'Donnell r...@iol.ie wrote: On 30/04/2010 21:30, Scott Marlowe wrote: On Fri, Apr 30, 2010 at 12:17 PM, Gauthier, Dave dave.gauth...@intel.com wrote: I believe v9 will have native DB master/slave DB replication (correct if wrong). If so, what’s the best guess on when will v9 be released? If I had to plan server deployments for the next year (and I do) I'd be sticking with pg 8.3 and a proven replication engine. Next summer Surely you mean 8.4? :-) Nope. 8.3 and 8.4 have similar performance, and we don't really need any of the newer features from 8.4 right yet. Also 8.4.1 was crashing on us under heavy load and I haven't had time to investigate issue yet. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Indexing queries with bit masks
Ok I've been blatantly lying, err, purposely simplifying the problem for the sake of the original email :) I've read over the responses, and am actually now considering just not using any index at all. Here's why: First, this actually isn't the only thing on the WHERE clause. It will only query for users who are friends with you so it can notify them of your activities. That's done via a weird JOIN on a table that holds all the friend relationships. So in reality, it will only load maybe a hundred rows, or maybe a thousand every once in a while if you're way popular. If I'm not mistaken, it should use the index to narrow it down to the list of friends, and then use a sequential scan to weed out the ones who subscribe to that type of notification. Second, the only thing /ever/ that will do this query is the queue service whose job it is to process notifications (which are files dropped on the file system) and email people all day long. This service handles one job at a time, and could potentially run on its own machine with its own read-only copy of the database. Thus, even if it was a fairly slow query, it's not gonna bring down the rest of the site. Regarding the idea of putting an index on each bit, I thought about this earlier as well as just kinda cringed. The users table gets updated quite a bit (last logon, session id, any time they change their profile info, etc).. Too many indexes is bad. I could just put the data in another table of course, which lead me to another idea. Have a table called Subscriptions and have each row hold a user id and a notification type. I could index both, and join on (Subscriptions.UserId = Users.UserId AND Subscriptions.Type = 8). This would be pretty dang fast, however updates are kinda a royal pain. When the user changes which types of subscriptions they want (via a list of checkboxes), I'd have to figure out which rows to delete and which new ones to insert. However, I think I have an idea in mind for a PgSQL function you pass in the bitmask to and then it translates it to conditional deletes and inserts. A third idea I'm tossing around is just not worry about it. Put the bitmask in the DB, but not filter on it. Every friend would be loaded into the dataset, but the queue processor would just skip rows if they didn't subscribe to that event. In other words, move the problem down to the business layer. The drawback is potentially large number of rows are loaded, serialized, etc into memory that will just be ignored. But of course the DB is probably a read-only copy and it's not even close to the bottle neck of the email queue under heavy load, so it's probably a non-issue. If mailing is slow, I just add more queue services.. I'm exploring all these ideas. I predict using the bitwise AND on the where clause isn't gonna be the worst design ever, and it's sure easier to implement than a table of subscriptions. What do you guys think? Mike On Fri, Apr 30, 2010 at 9:29 AM, Tom Lane t...@sss.pgh.pa.us wrote: Peter Hunsberger peter.hunsber...@gmail.com writes: If all subscriptions are roughly equal in popularity then any single select should give ~ 10% of the data. That would seem to be selective enough that you'd really want an index? My personal rule of thumb is that 10% is around the threshold where indexes stop being very helpful. At that selectivity, you're going to be having to read every page of the table anyway, and it's not clear that the extra I/O to read the index is going to get repaid in CPU savings. (Now if the table+index are fully cached in RAM, the threshold's probably a bit higher, but there still is not reason to think that an index is going to make for a huge improvement.) If so, any answers to the OP's main question; what would be the most efficient way to handle this type of thing? Well, btree's right out for indexing bit selections. In principle you could maybe do something with a GIN index, but I don't think we ship any prefab GIN opclasses for this. [ thinks for a bit ] The best idea that comes to mind offhand is to not use an integer, but a boolean array, such that the queries look like select ... where subscriptions[4]; This already gives you one big advantage, which is that you're not hard-wiring an assumption about how many notification types there can ever be. What I would then do is build a separate partial index for each subscription column, ie, create index ... where subscriptions[1]; create index ... where subscriptions[2]; .. etc .. Now this only works as long as the queries are referencing explicit constant subscription numbers, else the planner won't be able to match the WHERE clause to any of the partial indexes. But if that is a reasonable restriction for your app then it seems like it should work. The main disadvantage of this is that you need N indexes, which could get a bit expensive if the table is updated heavily. But
Re: [GENERAL] Inheritance efficiency
On Fri, Apr 30, 2010 at 00:19, Vincenzo Romano vincenzo.rom...@notorand.it wrote: For example, the Linux kernel made the big jump with server hardware thanks also to the O(1) schedulers. flamebait Uhh linux has not had a O(1) scheduler since 2.6.23, its supposedly O(log n) now. =) /flamebait -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Native DB replication for PG
Joshua D. Drake wrote: On Fri, 2010-04-30 at 13:42 -0700, Gauthier, Dave wrote: If I had to plan server deployments for the next year (and I do) I'd be sticking with pg 8.3 and a proven replication engine. Next summer Surely you mean 8.4? :-) No, I would buy the 8.3 argument as well. Depending on your conservative level. 8.4 is fine and all but 8.3 is about as rock solid as it gets. Unless you don't vacuum enough on a bigger database, run out of FSM pages, and the whole vacuum strategy goes to hell afterwards. I would say that running into that issue is *probable* for an 8.3 install of any significant size, whereas the odds of running into a regression in 8.4 relative to 8.3 is pretty low. The whole the older version is always more reliable mantra doesn't make sense when you've got a major known issue in the older release that just goes away by using the newer one, and I feel that's the case with 8.4 vs. 8.3. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Inheritance efficiency
Vincenzo Romano wrote: I argued that O(n) stuff will keep it away from enterprise grade applications. I've been told earlier that It is fine for dozens of child tables, but not thousands; it does need improvement. This is not enterprise grade Enterprise grade doesn't mean anything. Partitioning designs that require thousands of child tables to work right are fundamentally misdesigned anyway, so there is no reason for any of the contributors to the project to work on improving support for them. There are far too many obvious improvements that could be made to PostgreSQL, ones that will benefit vastly more people, to divert resources toward something you shouldn't be dong anyway like that. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
