Re: [SQL] failed to build any 5-way joins
"Alessandro Depase" <[EMAIL PROTECTED]> writes: > select * > from info where parent_infoid is null=20 > and fieldtypeid in (select fieldtypeid from users_auth_groups uag1, field_a= > uth fa where userid =3D 8 and fa.groupid =3D uag1.groupid)=20 > and main_infoid in (select ic.infoid from info_category ic, category_auth c= > a, users_auth_groups uag2=20 > where ic.categoryid =3D ca.categoryid and uag2.userid =3D 8 and uag2.grou= > pid =3D ca.groupid and read_write =3D 'W')=20 Hmm. It's right, there's no way to construct a sub-plan that joins just that number of relations, because of the constraint that the IN sub-selects have to be fully formed before we can do IN processing. A brute force solution is to just remove the error cross-check in src/backend/optimizer/path/joinrels.c: if (result_rels == NIL) elog(ERROR, "failed to build any %d-way joins", level); I'll probably install some less-drastic fix for 7.4.1, but if you need a solution right now, that will get you going. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] failed to build any 5-way joins
Thanks Tom, but I think I can wait, being possible to use the exists clause and not having, at the moment a big database. When there will be the less-drastic fix you refer to, I will use it and change my query. Thanks again. Bye Alessandro Depase - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Alessandro Depase" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Wednesday, December 17, 2003 4:32 PM Subject: Re: [SQL] failed to build any 5-way joins > "Alessandro Depase" <[EMAIL PROTECTED]> writes: > > select * > > from info where parent_infoid is null=20 > > and fieldtypeid in (select fieldtypeid from users_auth_groups uag1, field_a= > > uth fa where userid =3D 8 and fa.groupid =3D uag1.groupid)=20 > > and main_infoid in (select ic.infoid from info_category ic, category_auth c= > > a, users_auth_groups uag2=20 > > where ic.categoryid =3D ca.categoryid and uag2.userid =3D 8 and uag2.grou= > > pid =3D ca.groupid and read_write =3D 'W')=20 > > Hmm. It's right, there's no way to construct a sub-plan that joins just > that number of relations, because of the constraint that the IN > sub-selects have to be fully formed before we can do IN processing. > > A brute force solution is to just remove the error cross-check in > src/backend/optimizer/path/joinrels.c: > > if (result_rels == NIL) > elog(ERROR, "failed to build any %d-way joins", level); > > I'll probably install some less-drastic fix for 7.4.1, but if you need a > solution right now, that will get you going. > > regards, tom lane > > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[SQL] migration between databases and versions
I have been Red Hat linux 7.3 on my prod server. I have been running postgres 7.2 on it. I would like to upgrade it to the newest version of postgres. 1) Is there a version for redhat 7.3. 2) How stable is postgres 7.4 ? I have another critical isssue. My database has now changed.In our new release of the product we have added few more columns to some of the tables in the database. But the production does not have these fields. All the fields in the tables in production are also in developement version. But there are some extra fields in developement database that are not in production. There is a lot of data already in the production database. Now i need to add move the data from the old production database to new one which has certain extra fields. Please let me know if it is possible to do this migration, if yes how? using pg_dump? Eg: Old database with data has a Table named 'person' with fields A,B New Database to be put under prodcution also has an Table named 'person' with field A, B,C If i do a pg_dump from old to new does it fill the A,B and leave C alone or will it give an error given that C is not a null field. Please help me --Mohan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] migration between databases and versions
Hi Mohan, I'm running 7.4 on a reasonably old version of turbo linux, on and even older creakier machine and it seems fine so far. I suspect I may be having some problems due to the fact that the development environment is 3 linux servers running under virtual pc on one windows 2000 box. :) I'm facing a similar data migration issue to you, but possibly more complex from what I read. So far my data migration has been from the old production system to the new development system in order to test the procedures and generate test data. My approach has been to dump tables individually, then edit the dump file and change the tables name, and delete things like constraints and index definitions. I then load the old table as person_old (to use your example) and then create a bulk insert statement to copy data from the old table format to the new one. My system is a major redevelopment, so I wouldn't expect pg_dump to handle it all for me automatically. My understanding is that you will have problems loading data into a table that does not have exactly the same definition as the one used to create the dump. For example, if the 2 tables have the same columns but they were added in a different order. It may be that using different dump options could get around this though - such as generating the dump as insert statements. If you come up wth any interesting information as you progress with this, I'd be happy to hear about it. Regards Iain - Original Message - From: <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, December 18, 2003 3:58 AM Subject: [SQL] migration between databases and versions > I have been Red Hat linux 7.3 on my prod server. I have been running > postgres 7.2 on it. > I would like to upgrade it to the newest version of postgres. > 1) Is there a version for redhat 7.3. > 2) How stable is postgres 7.4 ? > I have another critical isssue. > My database has now changed.In our new release of the product we have > added few more columns to some of the tables in the database. But the > production does not have these fields. All the fields in the tables in > production are also in developement version. But there are some extra > fields in developement database that are not in production. There is a lot > of data already in the production database. > Now i need to add move the data from the old production database to new > one which has certain extra fields. Please let me know if it is possible > to do this migration, if yes how? using pg_dump? > > Eg: Old database with data >has a Table named 'person' with fields A,B > New Database to be put under prodcution >also has an Table named 'person' with field A, B,C > > If i do a pg_dump from old to new does it fill the A,B and leave C alone > or will it give an error given that C is not a null field. > > Please help me > > --Mohan > > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
