Re: [SQL] array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3
I wrote: > While we could probably revert just enough of the changes to > enforce_generic_type_consistency to allow this case again, I wonder > just how safe that'd really be. It would amount to expecting that > functions that take anyarray but don't take or return anyelement to > not only work on any array type, but to be always prepared for the > input element type to change on-the-fly (since that's exactly what > would happen when scanning pg_statistic). Quite a lot of the built-in > anyarray functions are prepared to do that, but I'm not sure they all > are. I went and looked, and found that none of the thirty or so built-in functions that accept ANYARRAY are coded to make unsafe assumptions about the input array type remaining the same across calls. So at least as of CVS HEAD, it seems safe to relax this back to the way it was pre-8.3. I'm still worried about the possibility of extension functions or future core functions failing to follow this coding rule; but as long as people are lazy and copy-and-paste from the existing models, it should be okay. regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] optimizing a query
Hi, I have an 'event' table and an 'event_date' table pointing to it containing (potentially) several event dates (start and (optionnaly) end) for the event in the past, present and future. I'm trying to build a query to select the most "relevant" date: 'current' or 'next' or 'last' (by order of priority). Actually I already have a view of event+"most relevant"event_date: CREATE VIEW event_story_review AS SELECT d.* FROM event_list_story_review d WHERE (d.id_date = (SELECT d2.id_date FROM event_date d2 WHERE (d2.id_event = d.id_event) ORDER BY d2.end_date is not null desc, (d2.end_date >= d.today) DESC, d2.start_date LIMIT 1)); This works but I am bothered by the subquery which has a slight performance impact on all queries using this view (there are many in my app). Is there a better way of doing it? maybe without a subquery? Thanks, -- http://www.critikart.net -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3
Is there any known workaround to get this the elements of the histogram_bounds anyarray in 8.3.5. If not, when might I expect a fix? Just trying to plan our testing/release schedule of rolling out to 8.3 around this problem. Thanks, Corey Tom Lane wrote: I wrote: While we could probably revert just enough of the changes to enforce_generic_type_consistency to allow this case again, I wonder just how safe that'd really be. It would amount to expecting that functions that take anyarray but don't take or return anyelement to not only work on any array type, but to be always prepared for the input element type to change on-the-fly (since that's exactly what would happen when scanning pg_statistic). Quite a lot of the built-in anyarray functions are prepared to do that, but I'm not sure they all are. I went and looked, and found that none of the thirty or so built-in functions that accept ANYARRAY are coded to make unsafe assumptions about the input array type remaining the same across calls. So at least as of CVS HEAD, it seems safe to relax this back to the way it was pre-8.3. I'm still worried about the possibility of extension functions or future core functions failing to follow this coding rule; but as long as people are lazy and copy-and-paste from the existing models, it should be okay. regards, tom lane
Re: [HACKERS] Re: [SQL] array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3
Corey Horton writes: > Is there any known workaround to get this the elements of the > histogram_bounds anyarray in 8.3.5. It appears that you could explicitly cast to text and thence to text[]: select array_to_string(histogram_bounds::text::text[], '-') from ... but this might be too ugly for your taste. > If not, when might I expect a fix? 8.3.6, or apply http://archives.postgresql.org/pgsql-committers/2008-12/msg00109.php regards, tom lane -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [HACKERS] Re: [SQL] array_to_string(anyarray, text) that was working in 8.1 is not working in 8.3
Fantastic - I'll just if/else the query based on db version. Thanks! Corey Tom Lane wrote: Corey Horton writes: Is there any known workaround to get this the elements of the histogram_bounds anyarray in 8.3.5. It appears that you could explicitly cast to text and thence to text[]: select array_to_string(histogram_bounds::text::text[], '-') from ... but this might be too ugly for your taste. If not, when might I expect a fix? 8.3.6, or apply http://archives.postgresql.org/pgsql-committers/2008-12/msg00109.php regards, tom lane
Re: [SQL] optimizing a query
On Sun, Dec 14, 2008 at 02:51:24PM -0800, Michal Szymanski wrote: > On 14 Gru, 20:22, [email protected] (Louis-David > Mitterrand) wrote: > > > > I have an 'event' table and an 'event_date' table pointing to it > > containing (potentially) several event dates (start and (optionnaly) > > end) for the event in the past, present and future. > > > > I'm trying to build a query to select the most "relevant" date: > > 'current' or 'next' or 'last' (by order of priority). > > > > Actually I already have a view of event+"most relevant"event_date: > > > > CREATE VIEW event_story_review AS > > SELECT d.* FROM event_list_story_review d > > WHERE (d.id_date = (SELECT d2.id_date FROM event_date d2 > > WHERE > > (d2.id_event = d.id_event) > > ORDER BY d2.end_date is not null desc, > > (d2.end_date >= d.today) DESC, > > d2.start_date LIMIT 1)); > > > > This works but I am bothered by the subquery which has a slight > > performance impact on all queries using this view (there are many in my > > app). > > > > Is there a better way of doing it? maybe without a subquery? > > The question is how do you plan to use your view ? Do you select all > rows from viev or you select only few tow from view using additional > filters? Usually you can rewrite subquery to JOINsbut without > information how do you plan use view it is hard to say is it bettter > solution. > It is important how many row do you plan in each table. I usually select all rows from the view with additional filters. If you have an example of rewriting the query with a join (instead of subquery) would you care sending it? So that I could run some tests. Thanks, -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
