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.

Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:

Reply via email to