[SQL] Select Instead on a table
Hello, [I hope this is no FAQ, but I did neither find anything about it in the PostgreSQL FAQ, nor get any hit on RULE or INSTEAD on the mailing list archive search¹...] Is it possible to create a ON SELECT DO INSTEAD rule on a table? All of my tries to do so failed by either complaining that the table is not empty, or converting the empty table into a view, thus making insert/update/delete impossible. What we need is basically a table "outer" where one column is computed via a subselect over another table "inner", something like SELECT id, some, more, rows, ( SELECT aggregate(inner.innerrow) AS collect WHERE inner.id=outer.id) as INNER FROM outer; (In reality, the inner query is a little more complicated because of some weird ordering and limit stuff, but this is the basic idea) Of yourse, we could (and currently do) do this via a View, but this has two disadvantages: - We have an extra relation floating around (outer and outer_view) - We need to create and maintain INSERT/UPDATE/DELETE rules for outer. We cannot change the application code that issues the queries, this is the reason for dealing with rules and views in this case. Thanks, Markus Schaber Footnotes: ¹ The latter was rather surprising to me, may be a defect in the mailing list archive search? -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 8: explain analyze is your friend
[SQL] curious delay on view/where
Hi folks. I have the following view: CREATE VIEW "stock_available" as SELECT * FROM stock_details WHERE available = true AND visible = true AND location not in (SELECT descr FROM ignored); Stock_details is itself a view pulling in a number of tables. Everything works fine until I try to pull in only the details for a specific branch, using the following. select * from stock_available where branch = 'Leeds'; or select * from stock_available where branch = 'Doncaster'; At this point, the query takes 11 seconds. Any other quiery, including select * from stock_available where branch != 'Doncaster' and select * from stock_available where branch != 'Leeds' which only return the equivelent of the top two (we only have Leeds and Doncaster) are les than 1 second. Anyone got any ideas of the cause, or thoughts on how I can trace the problem? -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] extra info - curious delay on view/where
On Thursday 28 October 2004 11:16 am, Gary Stainburn wrote: > Hi folks. > > I have the following view: > > CREATE VIEW "stock_available" as > SELECT * FROM stock_details > WHERE available = true AND visible = true AND > location not in (SELECT descr FROM ignored); > > Stock_details is itself a view pulling in a number of tables. > Everything works fine until I try to pull in only the details for a > specific branch, using the following. > > select * from stock_available where branch = 'Leeds'; > or > select * from stock_available where branch = 'Doncaster'; > > At this point, the query takes 11 seconds. Any other quiery, > including [snip] Once thing I forgot to mention. If I run the above on the base view stock_details, it returns in < 1 second too. -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Select Instead on a table
Markus Schaber <[EMAIL PROTECTED]> writes: > Is it possible to create a ON SELECT DO INSTEAD rule on a table? Not unless you want it to become a view. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] curious delay on view/where
Gary Stainburn <[EMAIL PROTECTED]> writes: > Anyone got any ideas of the cause, or thoughts on how I can trace the > problem? EXPLAIN ANALYZE results for the fast and slow cases would be interesting. Also, have you ANALYZEd the underlying tables lately? And what PG version is this? regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] extra info - curious delay on view/where
Hi Folks, It´s just for curiosity I ask: Could an INDEX speed up that SELECT? Thanks in advance, and sorry for my newbies questions. André (Brazilian User) > On Thursday 28 October 2004 11:16 am, Gary Stainburn wrote: > > Hi folks. > > > > I have the following view: > > > > CREATE VIEW "stock_available" as > > SELECT * FROM stock_details > > WHERE available = true AND visible = true AND > > location not in (SELECT descr FROM ignored); > > > > Stock_details is itself a view pulling in a number of tables. > > Everything works fine until I try to pull in only the details for a > > specific branch, using the following. > > > > select * from stock_available where branch = 'Leeds'; > > or > > select * from stock_available where branch = 'Doncaster'; > > > > At this point, the query takes 11 seconds. Any other quiery, > > including > [snip] > > Once thing I forgot to mention. If I run the above on the base view > stock_details, it returns in < 1 second too. > -- > Gary Stainburn > > This email does not contain private or confidential material as it > may be snooped on by interested government parties for unknown > and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > __ Acabe com aquelas janelinhas que pulam na sua tela. AntiPop-up UOL - É grátis! http://antipopup.uol.com.br/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [SQL] curious delay on view/where
On Thursday 28 October 2004 3:25 pm, Tom Lane wrote: > Gary Stainburn <[EMAIL PROTECTED]> writes: > > Anyone got any ideas of the cause, or thoughts on how I can trace > > the problem? > > EXPLAIN ANALYZE results for the fast and slow cases would be > interesting. Also, have you ANALYZEd the underlying tables lately? > And what PG version is this? > > regards, tom lane Hi Tom. I've the analyze but don't understand what it's telling me. I've made it available at http://www.stainburn.com/analyze.txt Gary -- Gary Stainburn This email does not contain private or confidential material as it may be snooped on by interested government parties for unknown and undisclosed purposes - Regulation of Investigatory Powers Act, 2000 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Select Instead on a table
Hi, Tom, On Thu, 28 Oct 2004 10:22:47 -0400 Tom Lane <[EMAIL PROTECTED]> wrote: > > Is it possible to create a ON SELECT DO INSTEAD rule on a table? > > Not unless you want it to become a view. That's what I suspected, after scanning the docs. Thanks, Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
