On 2017-05-18 21:48, Rob Brucks wrote:
> Hello Everyone,
>
> I am unable to figure out how the trigger was able to successfully
> create the table, but then fail creating the index. I would have
> expected one thread to "win" and create both the table and index, but
> other threads would fail when creating the table… but NOT when
> creating the index.
First, I agree whole heartedly with the other's suggestions to "not do
this".
Create a cronjob of whatever that prepares the required tables before
you need them, empty tables are cheap.
Second: IF EXISTS only tells you that an object exists and is ready for
use.
So what happens when a process is in the middle of creating that object?
Does IF EXISTS tell you it exists or not?
What you need (accepting that this whole trigger based approach is
probably not the best option)
is a proper locking mechanism. A "thundering herd" protection. The first
time the trigger is triggered
it should set a lock (n advisory lock for example) that subsequent calls
to the same trigger
can lok at to see if the table they need is being created at that time,
so they will skip the create commands
and *WAIT* for the first process to complete before using the table.
That *WaIT* is important, and also something you probably don't want,
especially if you have a busy database.