Re: [SQL] delete and select with IN clause issues
Jeff Frost <[EMAIL PROTECTED]> writes: > Well, I spoke to soon on the it all works front. So, it's been > reindexed and appears to be working properly now. I guess I'll keep > an eye on it for a while. I didn't get your query suggestion in time, > so hopefully I grabbed the right binary file..though it did seem to > disappear after the reindex, so I think it's likely the correct one. > Definitely got the correct second one. Well, I can't find anything wrong :-(. There are some differences in the list of contained keys, but they're all up near the end of the range, which is consistent with the assumption that the table is live and had some changes between your two dumps of the index. In particular, there's no difference in the entries for the troublesome key value: 3863562924080 25 3863562924080 26 3863562924080 27 So I dunno what to make of it. If it happens again, we need to look more closely. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Determining correct table order for insert or drop statements to satisfy foreign keys
On 11/2/06, Peter Hanson <[EMAIL PROTECTED]> wrote: Hi Aaron,I'm using PHP5 as the programming language.Unfortunately it looks like PHP has very limited support for meta data: http://us2.php.net/manual/en/function.pg-meta-data.php Check this out:http://www.postgresql.org/docs/8.0/interactive/functions-info.html http://archives.postgresql.org/pgsql-admin/2006-08/msg00147.phpSee if that points you in the right direction. On Thursday 02 November 2006 16:58, Aaron Bono wrote:> On 11/2/06, Peter Hanson <[EMAIL PROTECTED]> wrote:> > Hello,> >> > First, sorry if this has already been covered, though I didn't find > > anything> > similar in the archives. Also sorry if this is the wrong list for this> > type> > of question, though it seemed to be the most relevant list for this> > question. > >> > On to the question:> >> > I'm writing a simple import script that reads in an excel file that a> > customer> > fills out. Each worksheet represents a different table and the tables' > > fields> > are columns in each worksheet.> >> > The tables represented in the file are variable, I have no idea what> > table combinations will be in the excel file when it's sent to the > > script.> >> > What I need is a way for the script to determine what order it should> > process> > the worksheets in the excel file.> >> > For example, you have this table structure (each table would be a > > worksheet):> >> > PERSON (1) <--> (many) ORDER (1) <--> (many) ORDER_ITEM> >> > Obviously, the people would have to be inserted first, then the orders, > > and> > finally the items, to keep the foreign keys between the tables happy.> >> > Is there a mechanism in PostgreSQL that would tell me this order? Or is> > it> > something that would have to be assembled from analyzing the > > schema? Anybody> > needed to perform an operation like this before? Any help/direction would> > be> > greatly appreciated.>> What programming language are you using? Many languages provide mechanisms > to ask the database for metadata about tables including primary keys,> indexes, data types and foreign keys. That is one option for you.-- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] The empty list?
On 11/3/06, Jesper Krogh <[EMAIL PROTECTED]> wrote: ... but missing SQL for the empty list basically means thatwe cant handle the empty set encoded in the empty array withoutexplicitly introducing code for this size of array. From aprogramatically viewpoint, this seems quite odd. Somebody, somewhere has to programitacally handle the empty array. You are just suggesting that the database do it, not you.Also, if you were selecting anything from an empty list and your program knows it is from an empty list, why even bother asking the database. The additional checks in your code can help reduce database calls and increase performance. -- == Aaron Bono Aranya Software Technologies, Inc. http://www.aranya.com http://codeelixir.com==
Re: [SQL] delete and select with IN clause issues
On Fri, 3 Nov 2006, Tom Lane wrote: Jeff Frost <[EMAIL PROTECTED]> writes: Well, I spoke to soon on the it all works front. So, it's been reindexed and appears to be working properly now. I guess I'll keep an eye on it for a while. I didn't get your query suggestion in time, so hopefully I grabbed the right binary file..though it did seem to disappear after the reindex, so I think it's likely the correct one. Definitely got the correct second one. Well, I can't find anything wrong :-(. There are some differences in the list of contained keys, but they're all up near the end of the range, which is consistent with the assumption that the table is live and had some changes between your two dumps of the index. In particular, there's no difference in the entries for the troublesome key value: 3863562924080 25 3863562924080 26 3863562924080 27 So I dunno what to make of it. If it happens again, we need to look more closely. Tom, I know we shouldn't have to REINDEX in the 8.1.x days. Do you have any idea what might have allowed this to happen? A while back this particular server was unable to send e-mail and so we weren't getting the vacuum verbose output. As a consequence the FSM settings were too low. That has been remedied, but I'm wondering if it's possible that the FSM settings being too low would allow the INDEX to somehow get damaged? -- Jeff Frost, Owner <[EMAIL PROTECTED]> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[SQL] Index + View Question
Hi,If I've got several tables, each with their own indexes. I also have a view that aggregates several of these tables. My question is, as novice-like as it may sound, do I need to create new indexes on the view or do the indexes from the orig. tables apply somehow when I select data from the view? Cheers,Dylan
Re: [SQL] Index + View Question
On Fri, Nov 03, 2006 at 11:17:30AM -0800, Dylan Lorimer wrote: > If I've got several tables, each with their own indexes. I also have a view > that aggregates several of these tables. My question is, as novice-like as > it may sound, do I need to create new indexes on the view or do the indexes > from the orig. tables apply somehow when I select data from the view? You _can't_ create an index on the view. Depending on what version of PostgreSQL you are using, it will be more or less intelligent about using the indexes in your views. But keep in mind that there are some cases where the indexes on several tables simply won't work -- if the condition is such that all the tables need to be compared to one another, for instant, no index will help you, because you'll end up pulling everything into memory before anything else can happen. A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Index + View Question
When you select from views, you are infact selecting from the underlying tables. Indexes created on those tables are used ultimately. --Imad www.EnterpriseDB.com On 11/4/06, Dylan Lorimer <[EMAIL PROTECTED]> wrote: Hi, If I've got several tables, each with their own indexes. I also have a view that aggregates several of these tables. My question is, as novice-like as it may sound, do I need to create new indexes on the view or do the indexes from the orig. tables apply somehow when I select data from the view? Cheers, Dylan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[SQL] Long SQL Update Question
So I kicked off a SQL transaction that has been running for 30+ minutes. Is there any way to monitor it to know if it has possibly hanged or not? To be fair, it is an update on 4+ million rows, so I expect it to run for a bit. But any stats or metrics I can get would be helpful. Cheers, dylan
[SQL] Duplicate Fields + Views Questions
Hi,I've got 2 views - each with identical column names. I want to create an aggregate view of both tables as a new view. But when I try to do this I understandably get errors on duplicate fields.As an example: CREATE VIEW gnis_view AS SELECT * FROM gnis_international_view, gnis_usa_view;ERROR: column "the_geom" duplicatedERROR: column "the_geom" duplicatedAny ideas on how I can achieve what I am seeking? Cheers,dylan
Re: [SQL] Duplicate Fields + Views Questions
> I've got 2 views - each with identical column names. I want to create an > aggregate view of both tables as a new view. But when I try to do this I > understandably get errors on duplicate fields. > > As an example: > > CREATE VIEW gnis_view AS SELECT * FROM gnis_international_view, > gnis_usa_view; > ERROR: column "the_geom" duplicated > ERROR: column "the_geom" duplicated > > Any ideas on how I can achieve what I am seeking Yes but you will have to specify each column name from each table and create aliases for the duplicated columns. Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] Duplicate Fields + Views Questions
Thanks,Got it working with a UNION.Cheers,dylanOn 11/3/06, Richard Broersma Jr <[EMAIL PROTECTED] > wrote:> I've got 2 views - each with identical column names. I want to create an > aggregate view of both tables as a new view. But when I try to do this I> understandably get errors on duplicate fields.>> As an example:>> CREATE VIEW gnis_view AS SELECT * FROM gnis_international_view, > gnis_usa_view;> ERROR: column "the_geom" duplicated> ERROR: column "the_geom" duplicated>> Any ideas on how I can achieve what I am seekingYes but you will have to specify each column name from each table and create aliases for the duplicated columns.Regards,Richard Broersma Jr.-- E. Dylan Lorimer | Google Earth Enterprise650.253.2459 (O) | 650.862.6810 (M) | 650.644.0182 (F) Earth Enterprise Support: [EMAIL PROTECTED] | 866.4GO.OGLE ext. 4
