postgresql - define tables that reference other tables not yet defined

[Carlos]

Hi,

I need to be able to define tables that reference other tables not yet defined (because of the order in which define_table is called).

Something like the following (simplified for this discussion):

    db.define_table(x'table1', Field('xtest'), Field('xref2', 'reference table2'))

    db.define_table('xtable2', Field('xtest'))

If I execute the above with sqlite, there's no error.

But if I execute it with postgresql (with latest web2py), then I get the following error:

   <class 'psycopg2.ProgrammingError'>(relation "xtable2" does not exist)

Would defining all tables in a single db transaction (somehow) solve this problem?.

Or would I have to implement a trick like defining all tables empty (with no fields) first and then re-defining all tables with all correct fields afterwards?.

Or what would you recommend to solve this issue?.

Thanks,

   Carlos

[villas]

I guess you meant:  reference xtable2

Well the easy way is to make sure your tables are created in the right order,  but with circular references that isn't possible.

I thought that Massimo had addressed this by turning off the checking for other tables,  but maybe it isn't working...

https://groups.google.com/d/msg/web2py/G3HABb--lGs/A3zGiU3db9AJ

[Carlos]

Hi villas,

Yes, I meant 'reference xtable2', I just forgot to update the pasted code.

Massimo (or others), should this be supported by web2py for postgresql?.

Thanks,

   Carlos

[Carlos]

Hi Massimo (and others),

Can you please let me know if this is supported by web2py (therefore considered a bug)?.

Otherwise, do you have any recommendations to solve it?.

I also posted a message here:

   https://groups.google.com/d/msg/web2py/G3HABb--lGs/9P9TIC_hIV4J

Thanks,

   Carlos

[Mariano Reingart]

On Mon, Jun 13, 2011 at 3:43 PM, Carlos <carlos...@gmail.com> wrote:
> Hi Massimo (and others),
> Can you please let me know if this is supported by web2py (therefore
> considered a bug)?.
> Otherwise, do you have any recommendations to solve it?.

You can define your tables manually via SQL, and disable web2py migrations.

I don't think you can redefine a table in web2py, and it would be
"counter-productive" anyway.

Circular references may work in SQLite as it does little for
consistency and data integrity, but postgresql is strict, you should
first create all the tables, insert initial data, then add foreing key
constraints.
I think this is not yet supported automatically in web2py, but you can
find workarounds easily.

Best regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

[Carlos]

Hi Mariano,

Thanks for your input.

I would like a generic and automated web2py solution for circular references that works with all supported dbs (not only postgresql).

I thought this was already supported in web2py, which is why I was asking also to Massimo (pending his confirmation / correction)?.

Can you please give a quick example of the easy workarounds you mention to do via web2py (preferably generic and automated)?.

Thanks again,

   Carlos

[Mariano Reingart]

IRRC Workaround are:
* redesign the database to avoid circular references
* don't use circular references at all, they aren't really required
by web2py DAL, you can define tables without "references" feild types,
just normal ones, and use validators (you can add foreign key
constraints later by SQL)
* use circular references but no automatic migrations (migrate=False)

Anyway, there is no easy way in PostgreSQL (and possibly other "real"
databases too) to create circular references the way web2py does,
you'll need to execute SQL to properly create tables, populate them
and create foreing key constraints.
You can use db.executesql for this, and look for a standard postgresql
backup to see the required commands and correct steps.

Best regards,

Mariano Reingart
http://www.sistemasagiles.com.ar
http://reingart.blogspot.com

[pbreit]

Mariano is probably right that you want to avoid circular references if possible. But the example you provided doesn't look like a circular reference to me.

What you might want to do is define the tables first without the reference, have Web2py create them and then add the reference after the tables are created and have Web2py migrate the alterations.

[Carlos]

Thanks for all you input.