CreateTable should be idempotent

[acim]

CreateTable should be idempotent

[ucirello]

you just mark in the changelog that this is breaking change and eventually bump major version.

Great idea actually!

[ucirello]

Hi @acim - thanks for opening this issue and the PR.

At first, I decided to add an non-idempotent CreateTable method because I wanted a clear signal that the table was already there. CreateTable will indeed error if a table with the same name is present, which I believe to be more beneficial for two problems that hit me along the way.

1. If the structure of the table changes, you won't get a silent failure.

2. If the table name you chose collides with the use of the table by another application or library, you won't get a silent failure.

It is quite tempting to believe that the typical case of pglock is somewhere in a standalone world. My experience so far has shown me that to not be the case, pglock usually lives inside a larger application and sharing some database context with it.

Therefore, if anything, I'd like to see the CreateTable go away instead of being made smarter.

With all that said, you are not the first one who proposes this idea, which is starting to make me believe it is about time to think of a v2 for pglock. You could refer to further reasoning in: #28

[acim]

I considered your reasoning before reporting this issue, but this is kind of choosing between two bad solutions. If you drop CreateTable then everyone has to create the table somehow else, either manually or by adding migration. Even in that case the problem of changing the table schema would remain.

The only automatic way would be to read the information_schema and find out if the table with correct schema is there or not.

Or maybe to add version field in the table and keep it there forever (in each schema change). Then you could find out if the schema version is fine or not and eventually panic (yes, panic is very bad in libraries) or something. I see that this is quite difficult topic even though it looks easy.

[ucirello]

If you drop CreateTable then everyone has to create the table somehow else, either manually or by adding migration.

And I think that's fine. It is for the consumer of this package to decide how the database should be operated, not the library itself. I added a piece of sugar that is being misused quite frequently. For some applications, hot DB changes might be fine, but for others it might not. CreateTable offers a tool that is hard to wield correctly.

Even in that case the problem of changing the table schema would remain.

You are right. The problem remains but it would not be pglock's responsibility any longer.

The only automatic way would be to read the information_schema and find out if the table with correct schema is there or not.

And perhaps the more fundamental question is why it needs to be automatic in the first place.

I see that this is quite difficult topic even though it looks easy.

I agree. And because of the Go 1 compatibility promise, I can't do a lot except trying to not make the situation any worse or much different than what it currently is.

[acim]

You are right. The problem remains but it would not be pglock's responsibility any longer.

Well, in case you have to change the schema any time in the future, you just mark in the changelog that this is breaking change and eventually bump major version. I mean it would be nice to inform users about this so that they can't miss it. :D