R: [GENERAL] totally different plan when using partitions
Anyone??? This looks like a bug to me... or is there an explanation? --- Mer 12/8/09, Scara Maccai m_li...@yahoo.it ha scritto: Da: Scara Maccai m_li...@yahoo.it Oggetto: [GENERAL] totally different plan when using partitions A: pgsql-general pgsql-general@postgresql.org Data: Mercoledì 12 agosto 2009, 13:05 query using partitions explicitly (1): explain analyze select nome1, thv3tralacc, dltbfpgpdch FROM cell_bsc_60_0610 as cell_bsc left outer join teststscell73_0610_1 as data on data.ne_id=cell_bsc.nome1 left outer join teststscell13_0610_1 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time where data.time =cell_bsc.starttime and data.time =cell_bsc.endtime and data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and data1.time =cell_bsc.starttime and data1.time =cell_bsc.endtime and data1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and cell_bsc.nome2=2 explain analyze: http://explain-analyze.info/query_plans/3805-query-plan-2509 same query, but using postgresql's partition pruning (2): explain analyze select nome1, thv3tralacc, dltbfpgpdch FROM cell_bsc_60_0610 as cell_bsc left outer join teststscell73 as data on data.ne_id=cell_bsc.nome1 left outer join teststscell13 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time where data.time =cell_bsc.starttime and data.time =cell_bsc.endtime and data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and data1.time =cell_bsc.starttime and data1.time =cell_bsc.endtime and data1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and cell_bsc.nome2=2 explain analyze: http://explain-analyze.info/query_plans/3807-query-plan-2511 The second version is A LOT slower (10x). But the 2 queries should be identical... why the two totally different plans??? As you can see in query 1 I just put the used table, in query 2 postgres uses exactly the table I put in 1 (plus the empty tables that are the father of the other tables); so I don't understand why the 2 plans... -- 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] totally different plan when using partitions
Scara Maccai wrote: same query, but using postgresql's partition pruning (2): explain analyze select nome1, thv3tralacc, dltbfpgpdch FROM cell_bsc_60_0610 as cell_bsc left outer join teststscell73 as data on data.ne_id=cell_bsc.nome1 left outer join teststscell13 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time where data.time =cell_bsc.starttime and data.time =cell_bsc.endtime and data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and data1.time =cell_bsc.starttime and data1.time =cell_bsc.endtime and data1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and cell_bsc.nome2=2 explain analyze: http://explain-analyze.info/query_plans/3807-query-plan-2511 The second version is A LOT slower (10x). But the 2 queries should be identical... why the two totally different plans??? Well, the first version was expecting about 400 rows, the second 15000, so it's not surprising that they have different plans. I'm not sure whether the planner is smart enough to cope with the multiple tests on time vs the partitioning and realise it can use your index on the partition. I'm assuming the partition is defined as being between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' - strikes me as an odd interval, and you don't say anywhere. You _might_ have a better plan by moving the partitioned tests into subqueries: FROM cell_bsc_60_0610 as cell_bsc left outer join ( SELECT ne_id FROM teststscell73 WHEREtime between '2006-10-01 00:00:00' and '2006-10-06 00:00:00') as data on data.ne_id=cell_bsc.nome1 -- Richard Huxton Archonet Ltd -- 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] Help interpreting pg_stat_bgwriter output
On Wed, 12 Aug 2009, sam mulube wrote: is my interpreting of buffers_clean = 0 correct? Yes. If so, why would the bgwriter not be writing out any buffers? The purpose of the cleaner is to prepare buffers that we expect will be needed for allocations in the near future. Let's do a little math on your system to guess why that's not happening. checkpoints_timed = 333 checkpoints_req = 0 You're never triggering checkpoints from activity. This suggests that your system is having a regular checkpoint every 5 minutes, and therefore the time your server has been up is about 1665 minutes. bgwriter_delay = 200ms With the background writer running 5 times per second, the data you've sampled involved it running 1665 * 60 * 5 = 499500 times. During none of those runs did it actually write anything; why? buffers_alloc = 19163 During those runs, 19163 buffers were allocated. This means that during the average background writer delay nap, 19163 / 499500 = 0.04 buffers were allocated. That's very little demand for buffers that need to be cleaned on average, and the evidence here suggests the system is finding plenty of cleaned up and ready to go buffers from the background checkpoint process. It doesn't need to do any work on top of what the checkpoint buffer cleanup is doing. buffers_backend = 740 This number represents the behavior the background writer is trying to prevent--backends having to clean their own buffers up. Your result here suggests that on average, during any 5 minute period there are 740 / 333 = 2.2 buffers being written that we might have had the background writer take care of instead. Again, that's so little activity that the averages the background writer estimates with aren't even detecting anything worth doing. In short, your system isn't nearly active enough for the background writer to find itself with useful work to do, and one of the design goals for it was to keep it from spinning around doing nothing in that situation. If your system load goes up, I expect you'll discover cleaning starts happening too. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] totally different plan when using partitions
Thank you for your reply. This makes partitions unusable for me... hope someone explains why this happens... this still looks like a bug to me... BTW the problem arises when adding the second left outer join: when using only 1 partitioned table (that is, only 1 left outer join) the 2 plans are identical. My answers follow. Well, the first version was expecting about 400 rows, the second 15000, so it's not surprising that they have different plans. Well, they're using exactly the same tables; I don't understand why one expects 400 rows and the other 15000 I'm not sure whether the planner is smart enough to cope with the multiple tests on time vs the partitioning and realise it can use your index on the partition. Sorry, didn't understand that... I'm assuming the partition is defined as being between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' - strikes me as an odd interval, and you don't say anywhere. Data is partitioned on a 4 tables per month basis You _might_ have a better plan by moving the partitioned tests into subqueries: FROM cell_bsc_60_0610 as cell_bsc left outer join ( SELECT ne_id FROM teststscell73 WHEREtime between '2006-10-01 00:00:00' and '2006-10-06 00:00:00') as data on data.ne_id=cell_bsc.nome1 Tried that, no changes. -- 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] totally different plan when using partitions
Scara Maccai wrote: Thank you for your reply. This makes partitions unusable for me... hope someone explains why this happens... this still looks like a bug to me... BTW the problem arises when adding the second left outer join: when using only 1 partitioned table (that is, only 1 left outer join) the 2 plans are identical. My answers follow. Well, the first version was expecting about 400 rows, the second 15000, so it's not surprising that they have different plans. Well, they're using exactly the same tables; I don't understand why one expects 400 rows and the other 15000 Well let's see. It's difficult to be certain without the table definitions and sizes, but... Both sequentially scan cell_bsc_60_0610 because it will return 300 rows. Seems sensible, and it gets the row estimate right. The fast one then uses the index teststscell73_0610_1_pkey to probe for matches and then again via teststscell13_0610_1_pkey. Two nested loops only make sense where you have a small number of rows matching, otherwise it can get expensive going back and fore to the index and table all the time. In this case, we end up matching more rows than we expected (rows=60 loops=285 gives us ~17100 rows to check against the second index) but not enough to slow us down. That first plan estimated a cost of 33391 for its 408 rows and the second 70402 for 15982 rows. That's double the estimated cost, but it takes four times longer to complete. If the first query had estimated the number of rows correctly it would have *looked* more expensive than the second. So - with your particular setup PostgreSQL thinks it takes longer to do index searches than it really does (or perhaps it thinks sorting is quicker, or both). You might want to adjust your cost estimates (see ch 18.6 of the manuals). Start by checking effective_cache_size. Then maybe reduce random_page_cost. Small steps though - these settings will affect all your other queries too. Also if you have the time, try issuing set enable_mergejoin=off and re-issue query #2. That will force it to choose another plan. Oh - if the partitions hold historical (unchanging) data it might be worth CLUSTERing them on the pkey index too. I'm not sure whether the planner is smart enough to cope with the multiple tests on time vs the partitioning and realise it can use your index on the partition. Sorry, didn't understand that... The partitioning relies on proving that only partition P needs to be considered. The more complicated the situation the harder it is to do that. In this case, I'm not sure that's whats happening though - it seems to narrow the partition down well enough. I'm assuming the partition is defined as being between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' - strikes me as an odd interval, and you don't say anywhere. Data is partitioned on a 4 tables per month basis But that's from the start of the 1st to the start of the 6th - five full days rather than 7+. It also *includes* the start of the 6th (= not ) which suggests overlap on the partitions. What does the definition of your partition say *exactly*? -- Richard Huxton Archonet Ltd -- 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] Looping through string constants
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote: CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS $BODY$ SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ LANGUAGE 'sql' IMMUTABLE STRICT I'd recommend taking off the STRICT from this. It will, counter intuitively, slow things down when you're not expecting it. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] multiple paramters in aggregate function
According to the documentation, you can pass multiple parameters into an aggregate function, but it only stores one value. What I am trying to do is sum a quantity field, but it also has units that need to be converted. My function should take 2 values, the quantity and the unit, determine which unit to use, the one in state or the passed in one and to convert either the quantity in state or the quantity passed in and add it to the other quantity. In other words: 4 meter 400 mm 100 cm I want to sum it all, my function decides to use meter (based on the requirements) and should return 4.00104 (or something like that) and then I have a second aggregate function which just chooses which unit to use, so in my query I use 2 aggregate functions, one gives me the sum of converted quantity and the other gives me which unit it is in. Currently, the only way I can think of doing this is by keeping an array in state. Is there a better way? Thank you Sim -- 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] totally different plan when using partitions + request
I'm still looking into it, but it seems the difference in the 2 plans is due to the fact that when using partitions, the planner adds the time it would take to index-scan the empty root table. But that table will never contain any data... Is there any chance to have the partitioning mechanism know that a table will always contain no data, because only inheriting table will contain data? Having the planner line: - Index Scan using teststscell13_pkey on teststscell13 data1 (cost=0.0..3.9 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=285) doesn't make any sense: that table will never have any data. I'd like to have a way to tell that to Postgresql... Something like: CREATE TABLE tabroot (...) WITH (NODATA) So that it will stop scanning the empty table every single loop... And every time you try to insert directly into tabroot you get an error... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
R: [GENERAL] multiple paramters in aggregate function
Is there a better way? I think you could use a User Data Type. Then pass that as parameter to your aggregate function. That is: you would pass (4, 'meter') (400, 'mm') (100, 'cm') to your aggregate function. Each one is a user datatype: CREATE TYPE mytype AS ( v double precision, t varchar(10) ); See http://www.postgresql.org/docs/8.4/static/rowtypes.html This is the example based on a custom data type of complex numbers: http://www.postgresql.org/docs/8.4/static/xaggr.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] difficulty running pg on XP as appl.
I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4. Running postgres.exe gives the error: Execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on how to properly start the server. Now, at the postgres wiki it says: http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Why_do_I_need_a_non-administrator_account_to_run_PostgreSQL_under.3F Why do I need a non-administrator account to run PostgreSQL under? When a hacker gains entry to a computer using a software bug in a package, she gains the permissions of the user account under which the service is run. Whilst we do not know of any such bugs in PostgreSQL, we enforce the use of a non-administrative service account to minimise the possible damage that a hacker could do should they find and utilise a bug in PostgreSQL to hack the system. This has long been common practice in the Unix world, and is starting to become standard practice in the Windows world as well as Microsoft and other vendors work to improve the security of their systems. Note, that with the release of PostgreSQL 8.2, it is possible to run under a administrative account. PostgreSQL 8.2 and above are able to irrevocably give up administrative rights at startup thus ensuring the rest of the system remains secure in the extremely unlikely event that PostgreSQL becomes compromised. So, I'm running a recent enough version of pg, now how do I get it to drop Administrator privs so it will run, rather than give me an error? Is there some special command line -c option or something (I would have thought this would be outomatic, but evidently not)? TIA
[GENERAL] 8.4.0 bug - failure to enforce a foreign key constraint
Hello, I have reported this yesterday via WWW as bug 4979, but I can't see it in the -bugs archive. Has it been lost or are the bug reports being moderated...? Anyway. Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL 5.3. radek=# \d kandydaci Table public.kandydaci Column | Type | Modifiers ---+--+--- id_rekordu| bigint | not null id_osoby | integer | not null id_rodzaju_adresu | smallint | score | double precision | not null Indexes: kandydaci_pkey PRIMARY KEY, btree (id_rekordu, id_osoby) Check constraints: c_kandydaci_score CHECK (score = 0::double precision AND score = 1::double precision) Foreign-key constraints: kandydaci_fk_id_rekordu FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE radek=# select count(*), sum((r.id is null)::int) as orphans from kandydaci k left join rekordy r on r.id=k.id_rekordu; count | orphans ---+- 1472 | 152 (1 row) The orphans count should be 0, obviously. This table is only inserted into, never updated. These rows should have been deleted by the CASCADE constraint: table rekordy references table tasks (also with ON DELETE CASCADE), and some tasks were deleted. Judging from the IDs, this has happened multiple times (at least twice). It's a test database with very low load. Some complex SELECT queries, bulk inserts, 99.9% non-conflicting transactions (users work on their own parts of the data, as defined by the task_id). No weird stuff has been done to this cluster. Loaded modules: plpgsql, plperl, dblink, fuzzystrmatch, hstore-new. Any ideas? I have not been able to reproduce it, unfortunately. -- Radosław Zieliński ra...@pld-linux.org pgp0BUnxKVQ9K.pgp Description: PGP signature
Re: [GENERAL] difficulty running pg on XP as appl.
PG Subscriber wrote: I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4. Running postgres.exe gives the error: Execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on how to properly start the server. Now, at the postgres wiki it says: http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Why_do_I_need_a_non-administrator_account_to_run_PostgreSQL_under.3F Why do I need a non-administrator account to run PostgreSQL under? When a hacker gains entry to a computer using a software bug in a package, she gains the permissions of the user account under which the service is run. Whilst we do not know of any such bugs in PostgreSQL, we enforce the use of a non-administrative service account to minimise the possible damage that a hacker could do should they find and utilise a bug in PostgreSQL to hack the system. This has long been common practice in the Unix world, and is starting to become standard practice in the Windows world as well as Microsoft and other vendors work to improve the security of their systems. Note, that with the release of PostgreSQL 8.2, it is possible to run under a administrative account. PostgreSQL 8.2 and above are able to irrevocably give up administrative rights at startup thus ensuring the rest of the system remains secure in the extremely unlikely event that PostgreSQL becomes compromised. So, I'm running a recent enough version of pg, now how do I get it to drop Administrator privs so it will run, rather than give me an error? Is there some special command line -c option or something (I would have thought this would be outomatic, but evidently not)? TIA Try using pg_ctl to start the server. Regards Garry begin:vcard fn:Garry Saddington n:Saddington;Garry org:ScholarPack Ltd. adr:;;Histon House;Hogsthorpe;Lincolnshire;PE24 5QA;England email;internet:ga...@scholarpack.com title:Lead Developer/C.E.O tel;work:01754 871243 tel;cell:07817730615 x-mozilla-html:FALSE url:www.scholarpack.com version:2.1 end:vcard -- 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] multiple paramters in aggregate function
On 13 Aug 2009, at 12:51, Sim Zacks wrote: What I am trying to do is sum a quantity field, but it also has units that need to be converted. 4 meter 400 mm 100 cm I want to sum it all, my function decides to use meter (based on the requirements) and should return 4.00104 (or something like that) and then I have a second aggregate function which just chooses which unit to use, so in my query I use 2 aggregate functions, one gives me the sum of converted quantity and the other gives me which unit it is in. Is there a better way? It's probably easiest to decide on an internal unit to use in your aggregate and only convert it to the desired unit once you're done summing them. I'd probably convert all measurements to mm in the function and summarise those. The final unit conversion can be taken out of the aggregate that way too, so I'd also have separate functions for converting units to and from other units - those functions will likely come in handy anyway. Your query would then be something like: SELECT convert_unit(sum_mm(field), 'mm', 'meter') FROM table; In general, don't put multiple operations in one function but split them into separate functions. You're much more flexible that way. Alban Hertroys -- Screwing up is the correct approach to attaching something to the ceiling. !DSPAM:737,4a83fca210137297812668! -- 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] difficulty running pg on XP as appl.
On Thu, Aug 13, 2009 at 13:21, PG Subscribermypg...@gmail.com wrote: I'm on Windows XP SP 2, trying to run PostgreSQL 8.3.4. Running postgres.exe gives the error: Execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on how to properly start the server. Now, at the postgres wiki it says: http://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Why_do_I_need_a_non-administrator_account_to_run_PostgreSQL_under.3F Why do I need a non-administrator account to run PostgreSQL under? When a hacker gains entry to a computer using a software bug in a package, she gains the permissions of the user account under which the service is run. Whilst we do not know of any such bugs in PostgreSQL, we enforce the use of a non-administrative service account to minimise the possible damage that a hacker could do should they find and utilise a bug in PostgreSQL to hack the system. This has long been common practice in the Unix world, and is starting to become standard practice in the Windows world as well as Microsoft and other vendors work to improve the security of their systems. Note, that with the release of PostgreSQL 8.2, it is possible to run under a administrative account. PostgreSQL 8.2 and above are able to irrevocably give up administrative rights at startup thus ensuring the rest of the system remains secure in the extremely unlikely event that PostgreSQL becomes compromised. So, I'm running a recent enough version of pg, now how do I get it to drop Administrator privs so it will run, rather than give me an error? Is there some special command line -c option or something (I would have thought this would be outomatic, but evidently not)? The privilege dropping functionality lives in pg_ctl, so it will only work if you start the server through pg_ctl (or as a service). -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] 8.4.0 bug - failure to enforce a foreign key constraint
On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinskira...@pld-linux.org wrote: Hello, I have reported this yesterday via WWW as bug 4979, but I can't see it in the -bugs archive. Has it been lost or are the bug reports being moderated...? Anyway. Pg 8.4.0 from yum.postgresql.org running on 64bit RHEL 5.3. radek=# \d kandydaci Table public.kandydaci Column | Type | Modifiers ---+--+--- id_rekordu | bigint | not null id_osoby | integer | not null id_rodzaju_adresu | smallint | score | double precision | not null Indexes: kandydaci_pkey PRIMARY KEY, btree (id_rekordu, id_osoby) Check constraints: c_kandydaci_score CHECK (score = 0::double precision AND score = 1::double precision) Foreign-key constraints: kandydaci_fk_id_rekordu FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE radek=# select count(*), sum((r.id is null)::int) as orphans from kandydaci k left join rekordy r on r.id=k.id_rekordu; count | orphans ---+- 1472 | 152 (1 row) since you do LEFT JOIN, indeed you can get r.id to be null. -- 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] multiple paramters in aggregate function
It's probably easiest to decide on an internal unit to use in your aggregate and only convert it to the desired unit once you're done summing them. I'd probably convert all measurements to mm in the function and summarise those. That could work in some cases, however in our case it would not produce desirable results. If the user put in meters, he is expecting to see meters. My problem comes in only when the user put in values in multiple unit types, which does not happen very often. It is generally a mistake, but we would prefer to let them make the mistake and then see an irrational result and correcting it, rather then telling them they probably made a mistake. I think Scara's solution makes the most sense. It is slightly cleaner then using an array and comes up with the same result. Sim -- 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] multiple paramters in aggregate function
That could work in some cases, however in our case it would not produce desirable results. Well I don't think you got Alban's suggestion right... What he was trying to say was: - use a regular (not aggregated) function to convert all measures to mm - use the normal SUM() to sum those value - use another regular function to convert from mm to whatever select mm_to_m(sum(convert_to_mm(measure))) from a Which is easier than my solution. -- 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] totally different plan when using partitions
Scara Maccai wrote: explain analyze select nome1, thv3tralacc, dltbfpgpdch FROM cell_bsc_60_0610 as cell_bsc left outer join teststscell73_0610_1 as data on data.ne_id=cell_bsc.nome1 left outer join teststscell13_0610_1 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time where data.time =cell_bsc.starttime and data.time =cell_bsc.endtime and data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and data1.time =cell_bsc.starttime and data1.time =cell_bsc.endtime anddata1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and cell_bsc.nome2=2 explain analyze: http://explain-analyze.info/query_plans/3805-query-plan-2509 same query, but using postgresql's partition pruning (2): explain analyze select nome1, thv3tralacc, dltbfpgpdch FROM cell_bsc_60_0610 as cell_bsc left outer join teststscell73 as data on data.ne_id=cell_bsc.nome1 left outer join teststscell13 as data1 on data1.ne_id=cell_bsc.nome1 and data1.time=data.time where data.time =cell_bsc.starttime and data.time =cell_bsc.endtime and data.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and data1.time =cell_bsc.starttime and data1.time =cell_bsc.endtime anddata1.time between '2006-10-01 00:00:00' and '2006-10-06 00:00:00' and cell_bsc.nome2=2 Huh, clearly not the same query (you're using the partition directly in the first query) ... Doing two changes at once is not helping your case. -- 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] multiple paramters in aggregate function
Sim Zacks wrote: According to the documentation, you can pass multiple parameters into an aggregate function, but it only stores one value. What I am trying to do is sum a quantity field, but it also has units that need to be converted. Have you seen Martijn van Oosterhout's tagged types? http://svana.org/kleptog/pgsql/taggedtypes.html -- 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] totally different plan when using partitions
Huh, clearly not the same query (you're using the partition directly in the first query) ... Doing two changes at once is not helping your case. Sorry, I don't understand... of course I used the partition directly in the first query... it's the difference between the two... what I don't like is that since the tables used are in fact the same, the plan shouldn't be that different. My conclusion is that the planner thinks there could be some data in the root partition, even if that will always be empty. What I would like is a way to tell Postgres hey, don't even look at the root table. That's just a placeholder for the partitions. It will never contain any data when I create the tables. Otherwise the planner might get fooled by an empty table index scan in a loop (which is what happens here), thinking that that will take time. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL for Firefox Bookmarks?
People, It would be nice to be able to use PostgreSQL as the storage for Firefox Bookmarks - anyone know if this would be possible? how it could be done? Thanks, Phil. -- Philip Rhoades GPO Box 3411 Sydney NSW 2001 Australia E-mail: p...@pricom.com.au -- 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] totally different plan when using partitions + request
Scara Maccai wrote: I'm still looking into it, but it seems the difference in the 2 plans is due to the fact that when using partitions, the planner adds the time it would take to index-scan the empty root table. But that table will never contain any data... Is there any chance to have the partitioning mechanism know that a table will always contain no data, because only inheriting table will contain data? Having the planner line: - Index Scan using teststscell13_pkey on teststscell13 data1 (cost=0.0..3.9 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=285) doesn't make any sense: that table will never have any data. I'd like to have a way to tell that to Postgresql... It's one index probe and takes virtually no time at all. That's not your problem. -- Richard Huxton Archonet Ltd -- 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] totally different plan when using partitions + request
- Index Scan using teststscell13_pkey on teststscell13 data1 (cost=0.0..3.9 rows=1 width=16) (actual time=0.006..0.006 rows=0 loops=285) doesn't make any sense: that table will never have any data. I'd like to have a way to tell that to Postgresql... It's one index probe and takes virtually no time at all. That's not your problem. Put that in a 6 nested loop and it won't be virtually no time at all I'm afraid... to the planner that 3.9 cost almost the same as an index scan on a populated table... Hence the planner uses a different plan. Otherwise I don't see why the 2 plans should be different... -- 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] PostgreSQL for Firefox Bookmarks?
Philip Rhoades wrote: People, It would be nice to be able to use PostgreSQL as the storage for Firefox Bookmarks - anyone know if this would be possible? how it could be done? Not sure about doing it with PostgreSQL but there is the Firefox Weave Service which might do what I think you're after. Probably over-kill though. http://labs.mozilla.com/projects/weave/ \\||/ Rod -- Thanks, Phil. -- 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] PostgreSQL for Firefox Bookmarks?
On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote: It would be nice to be able to use PostgreSQL as the storage for Firefox Bookmarks - anyone know if this would be possible? how it could be done? Uh, I think it would consume more system resources. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org signature.asc Description: This is a digitally signed message part
Re: [GENERAL] totally different plan when using partitions
Scara Maccai wrote: Huh, clearly not the same query (you're using the partition directly in the first query) ... Doing two changes at once is not helping your case. Sorry, I don't understand... of course I used the partition directly in the first query... it's the difference between the two... what I don't like is that since the tables used are in fact the same, the plan shouldn't be that different. I misread your original email to say that you were changing the parameter. What version are you using? Also, please post the table definitions (preferably in pg_dump -s format) My conclusion is that the planner thinks there could be some data in the root partition, even if that will always be empty. What I would like is a way to tell Postgres hey, don't even look at the root table. That's just a placeholder for the partitions. It will never contain any data when I create the tables. Otherwise the planner might get fooled by an empty table index scan in a loop (which is what happens here), thinking that that will take time. I'm not sure I agree with your assessment of the problem. -- 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
[GENERAL] array syntax and geometric type syntax
I am trying to make sense of geometric literal syntax in and out of array
syntax. I cannot figure out a general rule: sometimes single quotes work,
sometimes double quotes work, and inside and outside of array literals the
rules are different an seemingly inconsistent.
Examples of all the weird cases are below. If someone could explain what the
SQL parser is really looking for, and what the best or most correct way is,
I would be grateful.
Thanks,
Dan
Examples:
db=# create temporary table x (p point);
CREATE TABLE
Can't use bare point notation:
db=# insert into x values ( (1,2) );
ERROR: column p is of type point but expression is of type record
HINT: You will need to rewrite or cast the expression.
Can use single-quoted points:
db=# insert into x values ( '(1,2)' );
INSERT 0 1
Can't use double-quoted points:
db=# insert into x values ( (1,2) );
ERROR: column (1,2) does not exist
LINE 1: insert into x values ( (1,2) );
Function notation works, as expected:
db=# insert into x values (point(1,2));
INSERT 0 1
Casting works, as expected:
db=# insert into x values ( '(1,2)'::point );
INSERT 0 1
Values print without quotes:
db=# select * from x;
p
---
(1,2)
(1,2)
(1,2)
(3 rows)
OK, now try an array of points:
db=# create temporary table y (pa point[]);
CREATE TABLE
ARRAY[] with single quoted value doesn't work:
db=# insert into y values (array[ '(1,2)' ]);
ERROR: column pa is of type point[] but expression is of type text[]
HINT: You will need to rewrite or cast the expression.
ARRAY[] with double quoted value doesn't work:
db=# insert into y values (array [ (1,2) ]);
ERROR: column (1,2) does not exist
LINE 1: insert into y values (array [ (1,2) ]);
^
Array[] with casting a quoted string works:
db=# insert into y values (array [ '(1,2)'::point ]);
INSERT 0 1
ARRAY[] with point() works:
db=# insert into y values (array [ point(1,2) ]);
INSERT 0 1
{} notation with unquoted value inside doesn't work:
db=# insert into y values ('{ (1,2) }');
ERROR: invalid input syntax for type point: (1
{} notation with double quotes inside works!!:
db=# insert into y values ('{ (1,2) }');
INSERT 0 1
{} with cast doesn't work:
db=# insert into y values ( '{ ''(2,3)''::point }');
ERROR: invalid input syntax for type point: '(2
{} with point() doesn't work:
db=# insert into y values ( '{ point(2,3) }');
ERROR: invalid input syntax for type point: point(2
Values print with {} and double-quote notation inside:
db=# select * from y;
pa
---
{(1,2)}
{(1,2)}
{(1,2)}
(3 rows)
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Can I get Field informations from system tables?
Hi!
I must get informations about the tables.
For example:
Tnnn:
1. [Field Name, Type, Size, NotNull]
2. [Field Name, Type, Size, NotNull]
...
The test table is this:
CREATE TABLE testfields
(
fbigint bigint NOT NULL,
fbool boolean,
fchar character(100),
fcharv character varying(100),
fdate date,
fdouble double precision,
fint integer,
fnum numeric,
fmemo text,
ftimez time with time zone,
ftime time without time zone,
ftimestampz timestamp with time zone,
ftimestamp timestamp without time zone,
fserial serial NOT NULL,
CONSTRAINT testfields_pkey PRIMARY KEY (fserial)
)
When I see Pygresql, I got only these informations:
Command line: C:\Python25\python.exe c:\PGDB_T~1.PY
Working directory: c:\
Timeout: 0 ms
[{'FSERIAL': 1, 'FMEMO': 'fdsf sdf dsfds sdd sfsdfsdfsd dsfsd sdfsd ssdsd
sdsd', 'FCHAR':
'alma
', 'FBIGINT': 1L, 'FNUM': Decimal(454.3234), 'FTIMESTAMPZ': '1999-01-01
10:10:10+01', 'FINT': 43545, 'FTIMEZ': '10:10:10+02', 'FDOUBLE': 4.5656656,
'FTIME': '10:10:10', 'FCHARV': 'alma', 'FDATE': '1999-03-25', 'FTIMESTAMP':
'1999-01-01 10:10:10', 'FBOOL': True}]
('fbigint', 'int8', None, 8, None, None, None)
('fbool', 'bool', None, 1, None, None, None)
('fchar', 'bpchar', None, -1, None, None, None)
('fcharv', 'varchar', None, -1, None, None, None)
('fdate', 'date', None, 4, None, None, None)
('fdouble', 'float8', None, 8, None, None, None)
('fint', 'int4', None, 4, None, None, None)
('fnum', 'numeric', None, -1, None, None, None)
('fmemo', 'text', None, -1, None, None, None)
('ftimez', 'timetz', None, 12, None, None, None)
('ftime', 'time', None, 8, None, None, None)
('ftimestampz', 'timestamptz', None, 8, None, None, None)
('ftimestamp', 'timestamp', None, 8, None, None, None)
('fserial', 'int4', None, 4, None, None, None)
Process Python Interpeter terminated, ExitCode:
The main problem that I don't see the size of the char/varchar fields, and I
don't determine that int field is serial or not?
Thanks for the help:
dd
Re: [GENERAL] comparing NEW and OLD (any good this way?)
On Wed, Aug 12, 2009 at 10:57:54PM +0200, Daniel Verite wrote: It seems to me that there is something special with rows: in tables, the values of columns may be null or not, but at the level of the row, there is no information that would say: this row itself as an object is null. Hum, there seem to be lots of different things happening here--lets try and untangle them a bit. I would say that the following returns a null value of type row (actually a pair of integers): SELECT b FROM (SELECT 1) a LEFT JOIN (SELECT 1,2) b(b1,b2) ON FALSE; It currently gets serialized as '\N' in the output of psql for me, but I'd have no problem if it appeared as '(,)'. Both of these seem like valid representations of a null row to me. In other discussions about similar issues I've said that the expression: ROW(NULL,NULL) IS DISTINCT FROM NULL should evaluate to FALSE. I still think this is correct and generally useful behavior. Anyway, let's try to assign null to a row variable (with 8.4.0): CREATE TABLE our_table(i int); CREATE FUNCTION test() returns void as $$ declare r our_table; begin r:=null; end; $$ LANGUAGE plpgsql; SELECT test() yields: ERROR:cannot assign non-composite value to a row variable CONTEXT: PL/pgSQL function test line 4 at assignment This just looks like PG missing a feature. plpgsql has much less user and developer time spent on it, so I'd expect to find more strangeness in darker corners like this. As a follow-up to the comparison between rows and arrays, note that if we'd make r an int[], there would be no error. OK, maybe people just do this more often and hence there's been a reason to make it work. However, I agree that if we consider that a row is a composite type, then there is a problem because we sure can insert NULL into a column that is of a composite type. So the row cannot be null line of reasoning holds only so far as you don't stuff rows into columns :) When you say columns, do you mean the value associated with a particular attribute in a particular row of a particular table? Surely this is a normal value and just because it happens to be stored in a table it shouldn't be any different from any other value anywhere else in PG. -- Sam http://samason.me.uk/ -- 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] totally different plan when using partitions
What version are you using? Also, please post the table definitions (preferably in pg_dump -s format) Table definition at the end of the msg. Postgresql 8.4beta1 I'm not sure I agree with your assessment of the problem. This is why I think that's the problem: This is an explain of the query using set enable_mergejoin=off; set enable_hashjoin=off http://explain-analyze.info/query_plans/3817-query-plan-2525 As you can see, the 2 root partition roots (teststscell73 and teststscell13) take teststscell73: 3.90 * 30120 loops = 117468 cost teststscell13: 3.89 * 15964 loops = 62099 cost total: 179567 cost out of 377398 total cost of the query... basically the 2 empty tables index access take 1/2 of the query planned time... while they should take 0, since they're empty!!! Since I can't tell postgresql they're empty, it assumes they have to be accessed... As I said, when using partitioning, I would like the option of flagging some tables (what I call the root tables) as Always empty, so that the planner wouldn't care of them... CREATE TABLE cell_bsc_60_0610 ( id integer NOT NULL, nome1 integer, nome2 integer, starttime timestamp without time zone, endtime timestamp without time zone ); CREATE TABLE teststscell13 ( time timestamp without time zone NOT NULL, ne_id integer NOT NULL, void0 integer, void1 integer, void2 integer, id1 integer, [] mutil33 integer, mutil12 integer ); CREATE TABLE teststscell13_0610_1 (CONSTRAINT teststscell13_0610_1_time_check CHECK (((time = '2006-10-01 00:00:00'::timestamp without time zone) AND (time '2006-10-09 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell13); CREATE TABLE teststscell13_0610_2 (CONSTRAINT teststscell13_0610_2_time_check CHECK (((time = '2006-10-09 00:00:00'::timestamp without time zone) AND (time '2006-10-16 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell13); CREATE TABLE teststscell13_0610_3 (CONSTRAINT teststscell13_0610_3_time_check CHECK (((time = '2006-10-16 00:00:00'::timestamp without time zone) AND (time '2006-10-24 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell13); CREATE TABLE teststscell13_0610_4 (CONSTRAINT teststscell13_0610_4_time_check CHECK (((time = '2006-10-24 00:00:00'::timestamp without time zone) AND (time '2006-11-01 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell13); CREATE TABLE teststscell73 ( time timestamp without time zone NOT NULL, ne_id integer NOT NULL, mutil22 integer, traffdlgprsscan integer, dlbpdch integer, dlgpdch integer, dlepdch integer, dltbfpbpdch integer, [...] void504 integer, void505 integer, void506 integer, void507 integer, void508 integer, void509 integer, void510 integer, void511 integer ); CREATE TABLE teststscell73_0610_1 (CONSTRAINT teststscell73_0610_1_time_check CHECK (((time = '2006-10-01 00:00:00'::timestamp without time zone) AND (time '2006-10-09 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell73); CREATE TABLE teststscell73_0610_2 (CONSTRAINT teststscell73_0610_2_time_check CHECK (((time = '2006-10-09 00:00:00'::timestamp without time zone) AND (time '2006-10-16 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell73); CREATE TABLE teststscell73_0610_3 (CONSTRAINT teststscell73_0610_3_time_check CHECK (((time = '2006-10-16 00:00:00'::timestamp without time zone) AND (time '2006-10-24 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell73); CREATE TABLE teststscell73_0610_4 (CONSTRAINT teststscell73_0610_4_time_check CHECK (((time = '2006-10-24 00:00:00'::timestamp without time zone) AND (time '2006-11-01 00:00:00'::timestamp without time zone))) ) INHERITS (teststscell73); ALTER TABLE ONLY teststscell13_0610_1 ADD CONSTRAINT teststscell13_0610_1_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell13_0610_2 ADD CONSTRAINT teststscell13_0610_2_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell13_0610_3 ADD CONSTRAINT teststscell13_0610_3_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell13_0610_4 ADD CONSTRAINT teststscell13_0610_4_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell13 ADD CONSTRAINT teststscell13_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell73_0610_1 ADD CONSTRAINT teststscell73_0610_1_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell73_0610_2 ADD CONSTRAINT teststscell73_0610_2_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell73_0610_3 ADD CONSTRAINT teststscell73_0610_3_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell73_0610_4 ADD CONSTRAINT teststscell73_0610_4_pkey PRIMARY KEY (ne_id, time); ALTER TABLE ONLY teststscell73 ADD CONSTRAINT teststscell73_pkey PRIMARY KEY (ne_id, time); CREATE INDEX cell_bsc_60_idx ON cell_bsc_60_0610 USING btree (nome2,
Re: [GENERAL] Looping through string constants
On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote: CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS $BODY$ SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ LANGUAGE 'sql' IMMUTABLE STRICT I'd recommend taking off the STRICT from this. It will, counter intuitively, slow things down when you're not expecting it. Woah! Really? I use strict a lot when it doesn't make sense to process a function with a null param. Can you give me more details or point me to more reading on this issue? Thanks. -- 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] array syntax and geometric type syntax
On Thu, Aug 13, 2009 at 11:02:37AM -0400, Dan Halbert wrote:
I am trying to make sense of geometric literal syntax in and out of
array syntax. I cannot figure out a general rule: sometimes single
quotes work, sometimes double quotes work, and inside and outside of
array literals the rules are different an seemingly inconsistent.
I'm sure it doesn't look like it, but literal syntax is the same
everywhere. What's confusing is that there's no visual difference
between a text literal any any other type--hence the error messages you
get back from PG are a bit confusing.
Firstly, column references are always at the top-level and are always
in double quotes. The other double quotes you were using were inside
a literal and hence subject to rules specific to that datatype's literal
input code.
The nicer syntax to distinguish things is to use:
TYPENAME 'literal'
in code. For example:
SELECT INT '1', FLOAT8 '1.1', NUMERIC '1.1';
Points are fun, because although the following look similar and have the
same result:
SELECT POINT '(1,2)', POINT (1,2);
They're actually doing very different things underneath. The first is
a straight point literal, the second is calling the point function and
giving its two parameters. I.e. it's short for:
SELECT POINT (FLOAT '1', FLOAT '2');
Array and Record literals are more complicated because they have to deal
with nesting of things and hence can get quite baroque. Lets go with
arrays first, because that's what your question was about. The basic
literal format is:
'{elem1,elem2,elem_n}'
So inside the single quotes, used to indicate a literal, there are
double quotes that separate each element. However all these double
quotes are just a waste of space most of the time, so unless there
are any strange characters (i.e. commas, braces, double quotes or
backslashes if I remember right) it doesn't bother with the double
quotes. For example, if we evaluate the above:
SELECT '{elem1,elem2,elem_n}'::TEXT[];
(sadly the normal literal syntax doesn't work for arrays) we get back:
{elem1,elem2,n}
So, PG has read in the literal, turned it into a real value internally,
and then converted it back to a literal to show you the result. These
two conversions have resulted in you getting something different back,
but they both represent the same thing as far as PG is concerned.
--
Sam http://samason.me.uk/
--
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] array syntax and geometric type syntax
I am trying to make sense of geometric literal syntax in and out of array syntax. I cannot figure out a general rule: sometimes single quotes work, sometimes double quotes work, and inside and outside of array literals the rules are different an seemingly inconsistent. Examples of all the weird cases are below. If someone could explain what the SQL parser is really looking for, and what the best or most correct way is, I would be grateful. I'm not sure you have a question here that you didn't answer yourself. Postgres is stricter than most dbms's about typing. It doesn't like to guess about what you probably meant. It's both a blessing and a curse. But you get used to it. You've already gone through what works and what doesn't. Just do what works. :) But to answer the question about why the quotes when it is a point array: Postgres is correctly interpreting and storing your point arrays. But arrays are output as comma separated lists and since your points have embedded commas, it quotes them. Scott -- 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] Looping through string constants
On Thu, Aug 13, 2009 at 08:30:07AM -0700, Scott Bailey wrote: On Wed, Aug 12, 2009 at 08:45:58PM -0700, Scott Bailey wrote: CREATE OR REPLACE FUNCTION unnest(anyarray) RETURNS SETOF anyelement AS $BODY$ SELECT $1[i] FROM generate_series(array_lower($1,1), array_upper($1,1)) i; $BODY$ LANGUAGE 'sql' IMMUTABLE STRICT I'd recommend taking off the STRICT from this. It will, counter intuitively, slow things down when you're not expecting it. Woah! Really? I use strict a lot when it doesn't make sense to process a function with a null param. Can you give me more details or point me to more reading on this issue? Thanks. There have been a few to-and-fros between me and other people about this. It's basically awkward interaction with the optimizer not being able to expand this out because it may change behavior. Try: http://archives.postgresql.org/pgsql-general/2009-06/msg00233.php IMMUTABLE is good though, don't go removing that yet! -- Sam http://samason.me.uk/ -- 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] totally different plan when using partitions
Scara Maccai wrote: set enable_mergejoin=off; set enable_hashjoin=off http://explain-analyze.info/query_plans/3817-query-plan-2525 Ah, good - that's useful. As you can see, the 2 root partition roots (teststscell73 and teststscell13) take teststscell73: 3.90 * 30120 loops = 117468 cost teststscell13: 3.89 * 15964 loops = 62099 cost total: 179567 cost out of 377398 total cost of the query... Your original slow query was only estimated at a cost of 7 - it's still going to be preferred even if you do get these to zero. Once the cost estimates bear more of a relation to run-times things might improve. -- Richard Huxton Archonet Ltd -- 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] comparing NEW and OLD (any good this way?)
This just looks like PG missing a feature. plpgsql has much less user and developer time spent on it, so I'd expect to find more strangeness in darker corners like this. this rule should be simply removed. It's not problem. The people long time believe so row cannot be null ever. I don't know if this is from Oracle or somewhere. SQL/PSM allows it. This semantic is little bit difficult. There is rule so any object is NULL when all fields is NULL too. I thing, so it's true. There is object, that has zero information. When You thinking about it, you have to forgot any your knowledges from languages that's knows pointers. Maybe some people has problem, because they put in equality NULL from SQL and NULL pointer. regards Pavel Stehule -- 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] array syntax and geometric type syntax
From Sam Mason s...@samason.me.uk:
The nicer syntax to distinguish things is to use:
TYPENAME 'literal'
Thanks! That is very helpful. I saw that syntax in one example I found on the
web, and incorrectly thought it was an alternate way of writing the function
call.
The point of all this was to figure out a uniform syntax I can use for doing
some type adapters to convert back and forth between Python objects and PG
objects (through psycopg2 or some other Python-PG interface). Perhaps I should
have mentioned that initially. I now see that the '{...}' notation does not do
any evaluation of what's inside, e.g.
SELECT '{1,2,1+2}'::INT[];
doesn't work, but
SELECT ARRAY[1,2,1+2]::INT[];
works fine.
Dan
--
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] PostgreSQL for Firefox Bookmarks?
Devrim, On 2009-08-14 00:55, Devrim GÜNDÜZ wrote: On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote: It would be nice to be able to use PostgreSQL as the storage for Firefox Bookmarks - anyone know if this would be possible? how it could be done? Uh, I think it would consume more system resources. Why do you say that? I am running PostgreSQL for other things anyway an occasional call from FF should not be a big deal? Thanks, Phil. -- Philip Rhoades GPO Box 3411 Sydney NSW 2001 Australia E-mail: p...@pricom.com.au -- 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] totally different plan when using partitions
Richard Huxton d...@archonet.com writes: Scara Maccai wrote: http://explain-analyze.info/query_plans/3817-query-plan-2525 Ah, good - that's useful. Hmmm ... why is the inner Nested Loop estimated to produce 30120 rows, when the cartesian product of its inputs would only be 285 * 14 = 3990 rows? What PG version is this, and can you extract a self-contained test case with an equally silly estimate? 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] array syntax and geometric type syntax
On Thu, Aug 13, 2009 at 12:31:29PM -0400, Dan Halbert wrote: Perhaps I should have mentioned that initially. In retrospect everything is easy! SELECT ARRAY[1,2,1+2]::INT[]; works fine. I'd not put a cast into that one. I can't see any performance reason why it's bad, I think it's mainly because it may mask other problems later on. It's not going to affect much fundamental either way though. -- Sam http://samason.me.uk/ -- 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] PostgreSQL for Firefox Bookmarks?
On Fri, 2009-08-14 at 02:36 +1000, Philip Rhoades wrote: Devrim, On 2009-08-14 00:55, Devrim GÜNDÜZ wrote: On Fri, 2009-08-14 at 00:20 +1000, Philip Rhoades wrote: It would be nice to be able to use PostgreSQL as the storage for Firefox Bookmarks - anyone know if this would be possible? how it could be done? Uh, I think it would consume more system resources. Why do you say that? I am running PostgreSQL for other things anyway an occasional call from FF should not be a big deal? If you are using PostgreSQL for your general usage database then you likely wouldn't notice much difference. However, as I recall FF just uses SQLite, so if you want an interface to that it would be easy enough. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] PostgreSQL for Firefox Bookmarks?
On Fri, 14 Aug 2009, Philip Rhoades wrote: It would be nice to be able to use PostgreSQL as the storage for Firefox Bookmarks - anyone know if this would be possible? how it could be done? Firefox uses SQLite to hold its bookmarks. It's certainly possible to hack the code to use an alternate database instead; for example there's a description of a port to use CouchDB at http://www.kryogenix.org/days/2009/07/06/firefox-bookmarks-in-couchdb PostgreSQL is not an inappropriate choice here though, as it's not designed for embedded use. It's just not easy to have a captive PostgreSQL postmaster process for a job like this that's managed by another app, and the project at large is not interested in making that easier. See http://wiki.postgresql.org/wiki/Todo#Features_We_Do_Not_Want for more details. The small advantages you'd get using PostgreSQL instead of SQLite (better scalability with multiple clients, better handling of very large files, less possiblity of data corruption) are pretty minor relative to how much work it would take to make the code compatible, and the number of people who could take advantage of it usefully (those who are already managing a database server on the same system as the browser) is tiny. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Can I get Field informations from system tables?
On Thu, Aug 13, 2009 at 05:20:22PM +0200, Durumdara wrote: I must get informations about the tables. For example: Tnnn: 1. [Field Name, Type, Size, NotNull] 2. [Field Name, Type, Size, NotNull] I'd recommend either using the standard defined information_schema[1] or playing around with running psql with -E. -- Sam http://samason.me.uk/ [1] http://www.postgresql.org/docs/current/static/information-schema.html -- 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] 8.4.0 bug - failure to enforce a foreign key constraint
Grzegorz Jaśkiewicz gryz...@gmail.com [2009-08-13 14:23]: On Thu, Aug 13, 2009 at 12:36 PM, Radoslaw Zielinskira...@pld-linux.org wrote: [...] kandydaci_fk_id_rekordu FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE [...] since you do LEFT JOIN, indeed you can get r.id to be null. There is a foreign key on this field, and it's the only one used in the JOIN condition. LEFT was only used to demonstrate the issue in a single query. -- Radosław Zieliński ra...@pld-linux.org pgpoKF88MTwR8.pgp Description: PGP signature
[GENERAL] Index utilization
I am working on cleaning up a bloated database. I have been reindexing etc. There appear to be a good number of never to almost never used indexes. I am looking in pg_stat_user_indexes which yields some questions. Assuming that the reset stats on server is not turned on how old are stats? Are there any good open source tools to monitor how often indexes are used? I was basically thinking of writing end of day numbers for the stats in a table so I can plot index usage over a few months to see what is getting used. Does anybody see a problem with that? Thanks in advance -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Selecting rows by content of array type field
I have table like this:
CREATE TABLE messages {
recepients varchar[],
};
Want to select by content co array type field recepients:
SELECT * FROM messages where 'john' ANY (recepients);
If i want to create index on recepients field, is it enough to
CREATE INDEX messages_recepients_index ON messages (recepients);
or is there other way how to index this? Expressions?
Thanks
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Encoding question when dumping/restoring databases for upgrade
Hello, I am sitting on version 7.4.x and am going to upgrade to version 8.3.x. From all I can read I should have no problem with actual format of the pgdump file (for actual dumping and restoring purposes) but I am having problems with encoding (which I was fairly sure I would). I have searched the web for solutions and one solution given (in one thread where Tom Lane answered) was to set the correct encoding in the version 8.3.x database. However, the default encoding in the version 8.3.x instance is currently UTF8 and I am happy with that (in fact, I would even want it to be UNICODE). The encoding for most of the databases in the version 7.4.x was LATIN1. Is there any way I can ignore the LATIN1 encoding and force the database to accept the UTF8 encoding of the new version 8.3.x instance? I get the below message when I try the psql -f file database command. psql:aranzo20090812:30: ERROR: encoding LATIN1 does not match server's locale en_US.UTF-8 DETAIL: The server's LC_CTYPE setting requires encoding UTF8. Any help would be appreciated. Archie -- 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] Selecting rows by content of array type field
On Thu, Aug 13, 2009 at 04:02:14PM +0300, mito wrote:
I have table like this:
CREATE TABLE messages {
recepients varchar[],
};
Want to select by content co array type field recepients:
SELECT * FROM messages where 'john' ANY (recepients);
not sure if it went missing in the email, but you want an equals in
there, i.e:
SELECT * FROM messages WHERE 'john' = ANY(recepients);
If i want to create index on recepients field, is it enough to
CREATE INDEX messages_recepients_index ON messages (recepients);
or is there other way how to index this? Expressions?
I think you want a GIN index; have a look at:
http://www.postgresql.org/docs/current/static/indexes.html
--
Sam http://samason.me.uk/
--
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] Encoding question when dumping/restoring databases for upgrade
a...@archie.netg.se writes: I am sitting on version 7.4.x and am going to upgrade to version 8.3.x. From all I can read I should have no problem with actual format of the pgdump file (for actual dumping and restoring purposes) but I am having problems with encoding (which I was fairly sure I would). I have searched the web for solutions and one solution given (in one thread where Tom Lane answered) was to set the correct encoding in the version 8.3.x database. However, the default encoding in the version 8.3.x instance is currently UTF8 and I am happy with that (in fact, I would even want it to be UNICODE). The encoding for most of the databases in the version 7.4.x was LATIN1. Is there any way I can ignore the LATIN1 encoding and force the database to accept the UTF8 encoding of the new version 8.3.x instance? Sure, you can load a latin1 dump into a utf8 database. However a pg_dumpall script will try to recreate the databases with their original encodings. You can either edit the script to adjust the ENCODING options for the databases, or use pg_dump to dump the databases one at a time and then load them into hand-created databases on the receiving end. 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] Simulate count result are distinct between 8.3 and 8.4
Why don't you make it simple and just use row_number() from 8.4... It can be simplified as: select row_number() over(), i, p from prueba limit 5; I know the use of WF. What surprised me, is the difference between both versions in the same query. I'm trying to understand why happens and not look for another way :) -- Emanuel Calvo Franco Database consultant at: www.siu.edu.ar www.emanuelcalvofranco.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] PostgreSQL for Firefox Bookmarks?
Philip Rhoades wrote: People, It would be nice to be able to use PostgreSQL as the storage for Firefox Bookmarks - anyone know if this would be possible? how it could be done? Most likely it could be done, if you wanted to. You will need to know how to write a firefox plugin, and how to programmatically connect to a postgresql server and perform an insert/update/select, then integrate both pieces of knowledge. sqlite manager may answer some of that for you. http://code.google.com/p/sqlite-manager/ Then there is always the question of should you? What do you wish to accomplish? Could a smaller db like sqlite be a better option? -- Shane Ambler pgSQL (at) Sheeky (dot) Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] max_allowed_packet equivalent in Postgres?
Hey folks, I'm installing OTRS/ITSM (and yes, sending the same question to their list) and it gives me this warning. I cannot find an equivalent config parameter in Postgres. Make sure your database accepts packages over 5 MB in size. A MySQL database for example accepts packages up to 1 MB by default. In this case, the value for max_allowed_packet must be increased. The recommended maximum size accepted is 20 MB. -- “Don't eat anything you've ever seen advertised on TV” - Michael Pollan, author of In Defense of Food -- 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] max_allowed_packet equivalent in Postgres?
Alan McKay wrote: Hey folks, I'm installing OTRS/ITSM (and yes, sending the same question to their list) and it gives me this warning. I cannot find an equivalent config parameter in Postgres. Make sure your database accepts packages over 5 MB in size. A MySQL database for example accepts packages up to 1 MB by default. In this case, the value for max_allowed_packet must be increased. The recommended maximum size accepted is 20 MB. Postgres has no known limit. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] max_allowed_packet equivalent in Postgres?
On Thu, 13 Aug 2009, Alan McKay wrote: Make sure your database accepts packages over 5 MB in size. A MySQL database for example accepts packages up to 1 MB by default. In this case, the value for max_allowed_packet must be increased. packages-packet for this to make sense; basically they're saying that the program sends wide rows back and forth to the client, and as described in http://dev.mysql.com/doc/refman/5.1/en/packet-too-large.html there's a low default there on that database. It's possible to run into this general class of issue with PostgreSQL; see ttp://archives.postgresql.org/pgsql-bugs/2006-07/msg00051.php for one example. But that is caused by a problem in the client side application, not the server. There is no server-side buffer size here as you'll find in MySQL. If your client app is coded correctly to handle large packets of data, it should work up to the size limits documented at http://www.postgresql.org/about/ , so you probably having nothing to worry about here. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] max_allowed_packet equivalent in Postgres?
On Thu, Aug 13, 2009 at 03:31:39PM -0400, Greg Smith wrote: If your client app is coded correctly to handle large packets of data, it should work up to the size limits documented at http://www.postgresql.org/about/ , so you probably having nothing to worry about here. Is it worth having a note about having enough memory floating around for those limits to actually be hit in practice? There would be no way of creating a row 1.6TB in size in one go, it would be ~800 UPDATE statements to get it up to that size as far as I can see. -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] plperl function
Hi. I'm trying to write a plperl function that returns a list of ids
that I want to use in a subquery.
The function call would look like:
select * from mlist( 168.4, 55.2, 0.1);
and would return a list of integers. I've written this function,
and it returns the right list of integers, but when I use it as a
subquery, the query hangs (if I use a return type of setof integer)
or gives an error message (if I use a return type of integer[]).
I want to use select * from mlist( 168.4, 55.2, 0.1) in something like
select id from ctable where cmid in ( select * from mlist( 168.4,
55.2, 0.1 ) );
or
select id from ctable where cmid = ANY ( select * from mlist( 168.4,
55.2, 0.1 ) );
cmid is an integer.
-
If I do
explain select id from ctable where cmid in ( 102185, 102186,102187 );
(*where I've hard-coded the integers*), I get
QUERY
PLAN
---
Bitmap Heap Scan on ctable (cost=2293.67..271604.69 rows=77653 width=8)
Recheck Cond: (cmid = ANY ('{102185,102186,102187}'::integer[]))
- Bitmap Index Scan on ctable_cmid_index (cost=0.00..2274.26
rows=77653 width=0)
Index Cond: (cmid = ANY ('{102185,102186,102187}'::integer[]))
(4 rows)
First I tried using the return type setof integer, but when I execute
select id from ctable where cmid in ( select * from mlist( 168.4,
55.2, 0.1 ) );
the query just seems to hang (minutes go by) and eventually I hit Ctrl-c.
The response time for
select id from ctable where cmid in ( 102185, 102186,102187 );
(*where I've hard-coded the integers*),is very fast ( 1s).
The explain above gave me the idea to try a return type of integer[], but
then I get the error message,
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
I also tried a return type of text and tried to cast it to integer[]
like in the
explain, but got a syntax error.
-
What return type should I be using? Is there anything wrong with using
a plperl function to generate a list of integers to use in a subquery?
I'd appreciate any suggestions, help with syntax, sample plperl
functions, etc.
Thanks,
Janet
--
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] comparing NEW and OLD (any good this way?)
Sam Mason wrote: Hum, there seem to be lots of different things happening here--lets try and untangle them a bit. I would say that the following returns a null value of type row (actually a pair of integers): SELECT b FROM (SELECT 1) a LEFT JOIN (SELECT 1,2) b(b1,b2) ON FALSE; It currently gets serialized as '\N' in the output of psql for me, but I'd have no problem if it appeared as '(,)'. Both of these seem like valid representations of a null row to me. If we query that result with libpq functions, it appears that the result is a row that contains a field named b of type record (oid=2249), and that field is null. So if we consider that this field is a row, then yeah it's a null row. In other discussions about similar issues I've said that the expression: ROW(NULL,NULL) IS DISTINCT FROM NULL should evaluate to FALSE. I still think this is correct and generally useful behavior. I see no reason to disagree with this. Besides, the fact that ROW(NULL,NULL) IS DISTINCT FROM NULL evaluates to true while ROW(NULL,NULL) IS NULL also evaluates to true looks quite puzzling to me. However, I agree that if we consider that a row is a composite type, then there is a problem because we sure can insert NULL into a column that is of a composite type. So the row cannot be null line of reasoning holds only so far as you don't stuff rows into columns :) When you say columns, do you mean the value associated with a particular attribute in a particular row of a particular table? That's what I meant, yes. Best regards, -- Daniel PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org -- 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] comparing NEW and OLD (any good this way?)
On Thu, Aug 13, 2009 at 11:44 PM, Daniel Veritedan...@manitou-mail.org wrote: In other discussions about similar issues I've said that the expression: ROW(NULL,NULL) IS DISTINCT FROM NULL should evaluate to FALSE. I still think this is correct and generally useful behavior. I see no reason to disagree with this. Besides, the fact that ROW(NULL,NULL) IS DISTINCT FROM NULL evaluates to true while ROW(NULL,NULL) IS NULL also evaluates to true looks quite puzzling to me. Why is this thread still going on? What does the spec say we should be doing and are we violating it in any of these cases? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] plperl function
ERROR: operator does not exist: integer = integer[]
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
Sounds like you are trying to return directly the query.
You must do a loop with that query inside (cursor) and
use next clause (to return one by one the values)
OR
return the query directly using return query (i don't remember
right now the plperl function to do that)
CREATE OR REPLACE FUNCTION perl_func()
RETURNS SETOF INTEGER AS $$
my $rv = spi_exec_query('select id from ctable where cmid in (
select i from mlist( 168.4, 55.2, 0.1 ) );');
my $status = $rv-{status};
my $nrows = $rv-{processed};
foreach my $rn (0..$nrows -1) {
return_next($row-{i});
}
return undef;
$$ LANGUAGE plperl;
SELECT * FROM perl_func();
I didn't test it, if you have problems, i'll try to help again :)
The error is telling you that could not return an array into
integer.
--
Emanuel Calvo Franco
Database consultant at:
www.siu.edu.ar
www.emanuelcalvofranco.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] max_allowed_packet equivalent in Postgres?
On Thu, Aug 13, 2009 at 11:20 PM, Sam Masons...@samason.me.uk wrote: On Thu, Aug 13, 2009 at 03:31:39PM -0400, Greg Smith wrote: If your client app is coded correctly to handle large packets of data, it should work up to the size limits documented at http://www.postgresql.org/about/ , so you probably having nothing to worry about here. Is it worth having a note about having enough memory floating around for those limits to actually be hit in practice? There would be no way of creating a row 1.6TB in size in one go, it would be ~800 UPDATE statements to get it up to that size as far as I can see. That wouldn't work actually. If you did something like UPDATE tab set a = a || a the first thing Postgres does when it executes the concatenation operator is retrieve the original a and decompress it (twice in this case). Then it constructs the result entirely in memory before toasting. At the very least one copy of a and one copy of the compressed a have to fit in memory. To work with objects which don't fit comfortably in memory you really have to use the lo interface. Toast lets you get away with it only for special cases like substr() or length() but not in general. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] max_allowed_packet equivalent in Postgres?
On Fri, Aug 14, 2009 at 12:03:37AM +0100, Greg Stark wrote: On Thu, Aug 13, 2009 at 11:20 PM, Sam Masons...@samason.me.uk wrote: Is it worth having a note about having enough memory floating around for those limits to actually be hit in practice? There would be no way of creating a row 1.6TB in size in one go, it would be ~800 UPDATE statements to get it up to that size as far as I can see. That wouldn't work actually. If you did something like UPDATE tab set a = a || a the first thing Postgres does when it executes the concatenation operator is retrieve the original a and decompress it (twice in this case). Then it constructs the result entirely in memory before toasting. At the very least one copy of a and one copy of the compressed a have to fit in memory. Yup, that would indeed break---I was thinking of a single update per column. The ~800 comes from the fact that I think you may just about be able to squeeze two 1GB literals into memory at a time and hence update two of your 1600 columns with each update. To work with objects which don't fit comfortably in memory you really have to use the lo interface. Toast lets you get away with it only for special cases like substr() or length() but not in general. Yup, the lo interface is of course much better for this sort of thing. -- Sam http://samason.me.uk/ -- 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] comparing NEW and OLD (any good this way?)
On Thu, Aug 13, 2009 at 11:53:49PM +0100, Greg Stark wrote: On Thu, Aug 13, 2009 at 11:44 PM, Daniel Veritedan...@manitou-mail.org wrote: In other discussions about similar issues I've said that the expression: ROW(NULL,NULL) IS DISTINCT FROM NULL should evaluate to FALSE. I still think this is correct and generally useful behavior. I see no reason to disagree with this. Besides, the fact that ROW(NULL,NULL) IS DISTINCT FROM NULL evaluates to true while ROW(NULL,NULL) IS NULL also evaluates to true looks quite puzzling to me. Why is this thread still going on? Because I'm a stickler for details and people keep replying! What does the spec say we should be doing and are we violating it in any of these cases? Whenever I've looked through I've not found anything definite either way. I think my interests here are more pedagogical that anything else, but PG's behavior is somewhat inconsistent and it could be nice to figure out what the best way of fixing these inconsistencies are. -- Sam http://samason.me.uk/ -- 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] max_allowed_packet equivalent in Postgres?
On Fri, Aug 14, 2009 at 12:33 AM, Sam Masons...@samason.me.uk wrote: On Fri, Aug 14, 2009 at 12:03:37AM +0100, Greg Stark wrote: On Thu, Aug 13, 2009 at 11:20 PM, Sam Masons...@samason.me.uk wrote: There would be no way of creating a row 1.6TB in size in one go I was thinking of a single update per column. Oh, my bad, you did indeed say row and I assumed column. Yes, you could create a single row of 1.6TB by doing repeated updates setting one column at a time to a 1G datum. (You would have to be using 32k blocks though) -- greg http://mit.edu/~gsstark/resume.pdf -- 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] mail alert
-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Christophe Pettus On Aug 11, 2009, at 12:19 AM, Jan Verheyden wrote: I was looking in what way it's possible to alert via mail when some conditions are true in a database. An external script that connects to the database, checks for the condition, and sends the email if it exists is probably your best solution. You might want to take a look at check_postgres: http://bucardo.org/check_postgres/ I would look forward to having such a feature in Postgres actually. Right now, I'm using cron to do those checks. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Video from the August 11, 2009 SFPUG meeting available
Greetings, The video of the August 11, 2009 SFPUG talk, featuring David Fetter's presentation on windowing and common table expressions, is now up: http://thebuild.com/blog/2009/08/13/sfpug-windowing-and-common-table-expressions/ -- -- Christophe Pettus x...@thebuild.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] 8.4.0 bug - failure to enforce a foreign key constraint
Radoslaw Zielinski wrote: radek=# \d kandydaci Table public.kandydaci Column | Type | Modifiers ---+--+--- id_rekordu| bigint | not null id_osoby | integer | not null id_rodzaju_adresu | smallint | score | double precision | not null [...] Foreign-key constraints: kandydaci_fk_id_rekordu FOREIGN KEY (id_rekordu) REFERENCES rekordy(id) ON DELETE CASCADE radek=# select count(*), sum((r.id is null)::int) as orphans from kandydaci k left join rekordy r on r.id=k.id_rekordu; count | orphans ---+- 1472 | 152 (1 row) The orphans count should be 0, obviously. Just to make sure that there is really an inconsistency: Could you pg_dump both tables and try to load them into another database? If that works without errors, we must have missed something obvious. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
