The table structure looks (roughly) like this:
* Table "Base": (id, created, deleted, origin, ...) ~3m rows
* Table "A": (id as FK on "Base", ...) ~400k rows
* Table "B": (id, ref_a as FK on "A", type, ...) ~2m rows
Swapping the PK of "A" happens as following, the FK is dropped during
Sorry, i forgot to add the following:
Explain / Analyze for the last "update type on B" call, normally there the
table has million of rows but i removed most of them since otherwise it would
not finish sometime soon:
('Update on B (cost=0.00..71.50 rows=1000 width=244) (actual
time=18.015..18.0
On 2022-Mar-28, Peter J. Holzer wrote:
> On 2022-03-27 14:06:25 -0400, Tom Lane wrote:
> > We follow that spec, so depending on what DIN 91379 *actually* says,
> > we might have additional reasons not to be in compliance. I don't
> > read German unfortunately.
>
> It defines minimal character s
Hi,
>I was able to narrow this down to something in SQLAlchemy behavior.
Fine :)
Something about the way TextClause changes the raw SQL string causes the
behavior I’m seeing, although we didn’t notice it at the time of the changeover.
I don’t know what exactly it’s doing yet, but when I switc
Per Kaminsky writes:
> # ('Trigger for constraint B_firmid_fkey: time=25.592 calls=1000',)
> # ('Trigger for constraint B_type_fkey: time=14.899 calls=1000',)
> # ('Trigger for constraint B_userid_fkey: time=16.146 calls=1000',)
> # ('Trigger for constraint B_version_fkey: time=11.285 calls=1000',
On 3/28/22 00:22, Per Kaminsky wrote:
Sorry, i forgot to add the following:
Explain / Analyze for the last "update type on B" call, normally there
the table has million of rows but i removed most of them since otherwise
it would not finish sometime soon:
('Update on B (cost=0.00..71.50 r
The tables have Index to each other on each foreign key. The index itself was
not touched though, and a remove/recreate did not help. Could it be possible,
that when the PK and FK values are replaced the Index is not (immediately)
updated and thus cannot be used?
The temporary table is not show
On 3/28/22 08:47, Per Kaminsky wrote:
The tables have Index to each other on each foreign key. The index
itself was not touched though, and a remove/recreate did not help. Could
it be possible, that when the PK and FK values are replaced the Index is
not (immediately) updated and thus cannot be
Do you mean a simple "ANALYZE VERBOSE A"? Or something different?
Following the thought that maybe the index got stale, i just tried to add a
"REINDEX TABLE B". This did not help as well, which might be the case, if an
index (re)build is always deferred until the end of the transaction (which i
On 3/28/22 09:39, Per Kaminsky wrote:
Do you mean a simple "ANALYZE VERBOSE A"? Or something different?
Following the thought that maybe the index got stale, i just tried to
add a "REINDEX TABLE B". This did not help as well, which might be the
case, if an index (re)build is always deferred unt
On 3/27/22 23:53, Per Kaminsky wrote:
The table structure looks (roughly) like this:
* Table "Base": (id, created, deleted, origin, ...) ~3m rows
* Table "A": (id as FK on "Base", ...) ~400k rows
* Table "B": (id, ref_a as FK on "A", type, ...) ~2m rows
Swapping the PK of "A" happens as f
> On Mar 28, 2022, at 5:42 AM, Philippe Doussot
> wrote:
>
> >Something about the way TextClause changes the raw SQL string causes the
> >behavior I’m seeing, although we didn’t notice it at the time of the
> >changeover.
> >I don’t know what exactly it’s doing yet, but when I switch back t
The usage of "on update cascade" certainly sounds plausible. Since the update
does only happen rarely if there is a special need to completely change the
backend structure it is not added to the FK at the moment.
Just ran the whole thing again with the "ANALYZE A; ANALYZE B" but with no gain
unf
On 3/28/22 10:49 AM, Per Kaminsky wrote:
The usage of "on update cascade" certainly sounds plausible. Since the
update does only happen rarely if there is a special need to completely
change the backend structure it is not added to the FK at the moment.
Just ran the whole thing again with the "A
Hi All,
We use JSONB /NoSQL functionality of PostgreSQL.
One of the column "doc" in our table "Table1" is of type JSONB.
Now the rows in this column "doc" can have different values with different
schemas.
For e.g values of doc per row will be something like below
ROW1 = {"id":"1", "name":"abc
15 matches
Mail list logo
