PostgreSQL table inheritance with "postgresql_inherits" does not take "add_is_dependent_on" into account

[Adrian]

Hello,

I have the following problem - I recently upgraded to the 1.0+ branch from 0.9 and now the PostgreSQL table inheritance does not work properly any longer because the tables that inherit from the master table are sometimes created before (random) the actual table they inherit from, throwing (psycopg2.ProgrammingError) relation "<table>" does not exist errors. With the 0.9+ branch a simple add_is_dependent_on was working to solve this but it does not seem to be taken into account anymore. Is there something that changed from 0.9 to 1.0 that needs to be done to get it to work? metadata.sorted_tables returns the proper table order (master table first, dependencies later) though but tables are not created in that order by metadata.create_all().

Thanks,

Adrian

[Mike Bayer]

On 11/23/2015 12:43 PM, Adrian wrote:
> Hello,
>
> I have the following problem - I recently upgraded to the 1.0+ branch
> from 0.9 and now the PostgreSQL table inheritance does not work properly
> any longer because the tables that inherit from the master table are
> sometimes created before (random) the actual table they inherit from,
> throwing (psycopg2.ProgrammingError) relation "<table>" does not exist
> errors. With the 0.9+ branch a simple add_is_dependent_on was working to
> solve this but it does not seem to be taken into account anymore.

this is not the case, that API is still taken into account. I can
remove the code that does so and the test which exercises this feature
then fails, so it is also tested.

Can you please provide a full reproducing test case? It needs to be
succinct, single file, and runnable by me, thanks.

Is
> there something that changed from 0.9 to 1.0 that needs to be done to
> get it to work? metadata.sorted_tables returns the proper table order
> (master table first, dependencies later) though but tables are not
> created in that order by metadata.create_all().
>
> Thanks,
>
> Adrian
>

> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send
> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[Adrian Schreyer]

I actually I just found the problem; the tables are in fact created in the right order - the problem is that the DDL contains INHERITS ( "parent" ). It gives the same error if I try to run the code in a GUI with the inherited table name quoted, without (the quoting) though it works. 

You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Adrian]

I attached a script that reproduces the problem. It actually only happens if the metadata contains a schema, then the tablename in the INHERITS() clause get quoted, which causes the problem.

> an email to sqlalchemy+unsubscribe@googlegroups.com
> <mailto:sqlalchemy+unsub...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlalchemy@googlegroups.com>.

> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe.

To unsubscribe from this group and all its topics, send an email to sqlalchemy+unsubscribe@googlegroups.com.

[Mike Bayer]

On 11/23/2015 03:15 PM, Adrian wrote:
> I attached a script that reproduces the problem. It actually only
> happens if the metadata contains a schema, then the tablename in the
> INHERITS() clause get quoted, which causes the problem.
>

postgresql_inherits was only added in 1.0. How can this have "worked"
in 0.9?

> > an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> > <mailto:sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> > To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>
> > <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> > Visit this group at http://groups.google.com/group/sqlalchemy

> <http://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to a topic
> in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe

> <https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe>.

> To unsubscribe from this group and all its topics, send an email

> to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

> Visit this group at http://groups.google.com/group/sqlalchemy

> <http://groups.google.com/group/sqlalchemy>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

[Adrian Schreyer]

That's true now that you are saying it, I actually implemented it myself before using a simple @compiles with CreateTable. 

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.

[Mike Bayer]

On 11/23/2015 03:15 PM, Adrian wrote:

> I attached a script that reproduces the problem. It actually only
> happens if the metadata contains a schema, then the tablename in the
> INHERITS() clause get quoted, which causes the problem.

anyway, there's no direct "postgresql_inherits_schema" feature as of
yet, so yo can work around that you have to artificially place the
schema name inside of the table name using direct quote control:

from sqlalchemy.sql.elements import quoted_name

child = Table(
"child", metadata,
Column("bar", String(4), nullable=False),
postgresql_inherits=quoted_name(parent.fullname, quote=False),
prefixes=["UNLOGGED"])

> > an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>
> > <mailto:sqlalchemy+...@googlegroups.com

> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>>.

> > To post to this group, send email to

> sqlal...@googlegroups.com <mailto:sqlal...@googlegroups.com>
> > <mailto:sqlal...@googlegroups.com
> <mailto:sqlal...@googlegroups.com>>.

> > Visit this group at http://groups.google.com/group/sqlalchemy

> <http://groups.google.com/group/sqlalchemy>.

> > For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to a topic
> in the Google Groups "sqlalchemy" group.
> To unsubscribe from this topic, visit
> https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe

> <https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe>.

> To unsubscribe from this group and all its topics, send an email

> to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy%2Bunsu...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

> Visit this group at http://groups.google.com/group/sqlalchemy

> <http://groups.google.com/group/sqlalchemy>.

> For more options, visit https://groups.google.com/d/optout

> <https://groups.google.com/d/optout>.

>
> --
> You received this message because you are subscribed to the Google
> Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send

> an email to sqlalchemy+...@googlegroups.com
> <mailto:sqlalchemy+...@googlegroups.com>.

> To post to this group, send email to sqlal...@googlegroups.com

> <mailto:sqlal...@googlegroups.com>.

[Adrian Schreyer]

That works and solves it, thanks!

To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/k4Lhj7i5sBM/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.