From: | Martin Lund Askøe <martinlundaskoe(at)gmail(dot)com> |
---|---|
To: | David Rowley <david(dot)rowley(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-novice(at)lists(dot)postgresql(dot)org |
Subject: | Re: Transaction locks on first insert into partitioned table partition |
Date: | 2019-06-09 22:45:23 |
Message-ID: | CAOvc5vjKpzxPDJL8CB+5-HGbZxOgYBABNfDKPcMC2Lv=ACXacw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
>
>
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Srikar Vankadaru | 2019-06-10 10:12:41 | Need help pg backup & recovery |
Previous Message | David Rowley | 2019-06-09 00:28:10 | Re: Transaction locks on first insert into partitioned table partition |