-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi Alessandra,
We have set up your database on our local Postgres server and have run a test against it using MartBuilder 0.5 and creating a dataset around the feature table, without making any changes to the schema or making any modifications to the auto-generated dataset. We too got disk-full errors. They relate to one single statement generated by MartBuilder: create table sgdlite_mart.TEMP__266 as select a.*,b.dbxref_id as dbxref_id,b.is_analysis as is_analysis,b.is_obsolete as is_obsolete,b.md5checksum as md5checksum,b.name as name,b.organism_id as organism_id,b.residues as residues,b.seqlen as seqlen,b.timeaccessioned as timeaccessioned,b.timelastmodified as timelastmodified,b.type_id as type_id,b.uniquename as uniquename from sgdlite_mart.TEMP__265 as a left join public.feature as b on a.srcfeature_id=b.feature_id; It seems that Postgres is for some reason unable to perform this join efficiently. I noticed that just by removing the 'b.name' column from the select clause, the statement runs in seconds, but if you include it, then it takes hours and eventually fills up the disk. This is backed up by doing an explain query on the original query and the one without b.name - they give entirely different execution plans. This sounds like a bug in Postgres. I think you'd be better off reporting it to the Postgres mailing lists and asking them to help you. They'll need copies of your schema, the data in your schema, and the SQL that MartBuilder generates. Sorry I couldn't be of more help. cheers, Richard Arek Kasprzyk wrote: > > On 4 Oct 2007, at 12:20, Alessandra Bilardi wrote: > >>> >> >> Hi Arek, >> Thank you for yuor replay. >> I tested other dataset tables and I obtain sql script running without >> errors. Now, I will select best tables that they content our queries and >> create ad hoc sql script by MBuilder. >> >>> out of space. This sounds more like a bug to me. Would you be able to >>> give us your database dump so we could have >>> a look and recreate the problem here? >> >> If you have to follow this linux comands you have to obtain "no space on >> device" error: >> >> $ curl -O http://sgdlite.princeton.edu/download/sgdlite/sgdlite.sql.gz >> $ createdb sgdlite >> $ (gunzip -c sgdlite.sql.gz | psql -d sgdlite -f - ) >& log.load >> $ bin/martbuilder.sh >> schema is chado about sgdlite database >> dataset is composed "feature" table and its group tables >> save sql script as sgdlite_mart.sql >> $ psql -d sgdlite < sgdlite_mart.sql 2> sgdlite_mart.err >> >> My free disk space was 20Gb. >> >> Regards, >> >> Alessandra Bilardi > > thanks for the info Alessandra > we'll have a look and get back to you on this > > a. > > > >> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >> >> CRIBI, University of Padova, Italy | >> http://www.linkedin.com/in/bilardi >> Via Ugo Bassi, 58/B - 35121 Padova | http://www.alessandrabilardi.com/ >> Tel 0498276165 -- Fax 0498276159 | [EMAIL PROTECTED] >> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >> >> > > > ------------------------------------------------------------------------------- > > Arek Kasprzyk > EMBL-European Bioinformatics Institute. > Wellcome Trust Genome Campus, Hinxton, > Cambridge CB10 1SD, UK. > Tel: +44-(0)1223-494606 > Fax: +44-(0)1223-494468 > ------------------------------------------------------------------------------- > > > > -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2.2 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHBkmV4C5LeMEKA/QRAr25AJ9qRFz03fWkz0010+TrseeLuEWL9ACfaBqf bD2rw4bQEkeTAiKwTxiqovo= =zIUs -----END PGP SIGNATURE-----
