Is altering a table from its own trigger possible? If not, how come?
For context, I am trying to attach a partition to a table using a trigger on that same table. Here is the error:
ERROR: cannot ALTER TABLE "tablename" because it is being used by active queries in this session
Any help would be greatly appreciated.
The error message answers both of your questions. The rule is that structure is constant while queries are running. The various lock types handle this and cause queries in different sessions to queue up. If you try and break things within the same session you end up with something like the error message shown instead as queuing won't help.
Whether there is a workable way to accomplish your goal I do not know.