Re: Transaction locks on first insert into partitioned table partition

>
>
> The only DDL that could affect what partition the row is meant to go
> into is DROP TABLE or DETACH PARTITION on the partition that's being
> INSERTed into. In that case, we'll take an AccessExclusiveLock on the
> partition itself which would prevent the concurrent INSERT.
>
> To me it sounds very much like Martin is trying to use partitioning in
> a way that's not supported. He didn't mention it, by my guess is he's
> trying to use a BEFORE INSERT trigger to create a partition that does
> not exist. That's a very bad idea and he'll be much better off
> creating the partitions before they're required using some job that
> runs periodically.  I think this is likely the case since otherwise,
> he'd not have a problem with starting a new transaction to perform the
> dummy insert to populate the relcache.
>

If only specific DDL changes invalidate the relcache entry then why not
update the cache when -they- happen. I don't understand what is going on
behind the scenes so there may be a good explanation.

I am creating missing partition tables ad-hoc roughly once every day as my
table naming pattern would suggest, though not as a before insert trigger.
I am inserting about 1000 records every second 24/7, so there will be no
"good" time to do the DDL update anyway.

I do not understand how this would not be how partitioned tables where
intended to be used, nor why you cannot see the problem of having the first
insert statement take a lock on the master table "without my knowledge".

I cannot ensure that a potential dummy insert statement happens before a
real insert statement, since having the relcache entry invalidated requires
my transaction doing the DDL update to be committed, leaving it all to be a
race condition with my normal insert operations.

I'm going to do a workaround to this if not bug then IMO bad design.
Probably something along the lines of a more managed table maintenance
step. I am fortunate that I can do that. Not everyone will be able to.

I now understand why the lock is taking, which was what I was asking. I
thank you for your comments and suggestions.

Kind regards, Martin

>