Why not use 'create table if not exist' ?

[soyking]

The before-insert trigger in Postgres is something like:

IF NOT EXISTS(
    SELECT 1 FROM information_schema.tables WHERE table_name=tablename)
THEN
    BEGIN
        EXECUTE 'CREATE TABLE ' || tablename || ' (
            CHECK (' || checks || '),
            LIKE "{{parent_table}}" INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
        ) INHERITS ("{{parent_table}}");';
    EXCEPTION WHEN duplicate_table THEN
        -- pass
    END;
END IF;

And it will check whether table's existense by SELETC 1, then create table if select failed.I tried to replace with CREATE TABLE IF NOT EXIST

BEGIN
    EXECUTE 'CREATE TABLE IF NOT EXISTS ' || tablename || ' (
        CHECK (' || checks || '),
        LIKE "{{parent_table}}" INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES
    ) INHERITS ("{{parent_table}}");';
EXCEPTION WHEN duplicate_table THEN
    -- pass
END;

In my case, it will speed up insert action about 25% (I really care about the decrease of insert action's speed). But I doubt that it is too special, so I open this issue to discuss about it. Thanks!

[maxtepkeev]

Hi, this was implemented long time ago, so I can't answer for sure as supported software evolved during that time, but you should definitely check #1 and #26 as they are connected to everything you did here

Let me know if you still have questions after reading these 2.

[soyking]

@maxtepkeev

Thanks for your response! Sorry for ignorance of #1

I could reproduce relation already exists error if I only use CREATE TABLE IF NOT EXISTS. But this situation could be avoided since catching the duplicate_table exception when CREATE TABLE IF NOT EXISTS as I shown above, and it still faster than before.

Also, in my django program there is no insert action for the partition table, so I choose to RETURN NULL :P

Thanks again!

[maxtepkeev]

I could reproduce relation already exists error if I only use CREATE TABLE IF NOT EXISTS. But this situation could be avoided since catching the duplicate_table exception when CREATE TABLE IF NOT EXISTS as I shown above, and it still faster than before.

Yes, that makes total sense, you can add a PR for that if you have time, I will gladly accept that change. I can't really say right now from the top of my head why we use SELECT 1 instead of using CREATE TABLE IF NOT EXISTS.

[maxtepkeev]

Regarding RETURN NULL, as described in #26 we need to add some kind of param to control that behaviour, I left a detailed description on what should be done to make sure that we didn't brake existing functionality, but that is a lot of work, so again if you have time and desire, PRs are always appreciated :)

[soyking]

I've tried to add return_null option for architect.install function on fork branch

But there are some limitations:

• it only works on PostgreSQL
• for orms like django pony sqlalchemy and sqlobject, you should maintain the model's primary id as I did in tests.
• it seems that peewee must fetch the primary key when create a row, but I am not sure and I make an issue.

[maxtepkeev]

Cool, great job. Let's wait for the answer regarding peewee a bit.