public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Vijaykumar Jain <[email protected]>
Cc: pgsql-general <[email protected]>
Subject: Re: how to know if the sql will run a seq scan
Date: Tue, 15 Oct 2024 14:29:39 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAM+6J96rMZJnEGvoQyuzmEw6oH6gg79A5Ahr4yKF_3RVYVM8mA@mail.gmail.com>
References: <CAM+6J96kmqmHJPWOU-Q9607BO9DiXCHAkHyuZtdF2Hc_6qVFtg@mail.gmail.com>
<[email protected]>
<CAM+6J96rMZJnEGvoQyuzmEw6oH6gg79A5Ahr4yKF_3RVYVM8mA@mail.gmail.com>
On 10/15/24 13:50, Vijaykumar Jain wrote:
> Sorry top posting, coz Gmail app on phone.
>
> Yeah, my point was for example we have a large table and we are
> attaching a table as a partition. Now it will scan the whole table to
> validate the constraint and that will create all sorts of problems.
Now you have changed the problem description.
To get a proper answer you will need to provide a more detailed
description of what you are doing with the following information:
1) Postgres version.
2) Definition of 'large'.
3) The command/process being used to create the partition.
4) The actual constraint definition.
5) The table definition.
> I understand the benefit of not valid constraint and then validating
> constraint to reduce blocking.
> But yeah monitoring locks for the statement should give me good enough
> hint of what will happen.
>
> Thanks for your reply. It helps.
>
>
>
> On Wed, Oct 16, 2024, 1:54 AM Adrian Klaver <[email protected]
> <mailto:[email protected]>> wrote:
>
> On 10/15/24 12:50, Vijaykumar Jain wrote:
> >
> > Hi,
> >
> > tl;dr
> > I am trying to learn what sql can result in a full seq scan.
> >
> > Basically there is a lot of info on the internet of what ddl
> change may
> > take an access exclusive lock while running a seq scan and hold
> for long.
> > And for some cases we can make use of
> > "not valid" constraint and then run a validate constraint as work
> > arounds to avoid long exclusive locks etc.
> > but how do we check the same. i mean for dmls there is a explain/
> > auto_explain.
> >
> > but for DDLs, how do we check the same.
> > i tried to isolate my setup and use pg_stat_user_tables and
> monitor the
> > same, which helped, but it is not useful as it does not link me
> to what
> > process/command invoked the seq scan.
> >
> > am i clear in my question ?
> >
> > if yes,
> > how do i log an alter table that may or may not do a seq scan,
> that may
> > or may not rewrite the table file on disk etc.
> > its a useless question, i am just playing with it for building
> > knowledge, no requirement as such.
>
> Look at the docs:
>
> https://www.postgresql.org/docs/current/sql-altertable.html
> <https://www.postgresql.org/docs/current/sql-altertable.html;
>
> "Scanning a large table to verify a new foreign key or check constraint
> can take a long time, and other updates to the table are locked out
> until the ALTER TABLE ADD CONSTRAINT command is committed. The main
> purpose of the NOT VALID constraint option is to reduce the impact of
> adding a constraint on concurrent updates. With NOT VALID, the ADD
> CONSTRAINT command does not scan the table and can be committed
> immediately. After that, a VALIDATE CONSTRAINT command can be issued to
> verify that existing rows satisfy the constraint. The validation step
> does not need to lock out concurrent updates, since it knows that other
> transactions will be enforcing the constraint for rows that they insert
> or update; only pre-existing rows need to be checked. Hence, validation
> acquires only a SHARE UPDATE EXCLUSIVE lock on the table being altered.
> (If the constraint is a foreign key then a ROW SHARE lock is also
> required on the table referenced by the constraint.) In addition to
> improving concurrency, it can be useful to use NOT VALID and VALIDATE
> CONSTRAINT in cases where the table is known to contain pre-existing
> violations. Once the constraint is in place, no new violations can be
> inserted, and the existing problems can be corrected at leisure until
> VALIDATE CONSTRAINT finally succeeds."
>
>
> > --
> > Thanks,
> > Vijay
> >
> > Open to work
> > Resume - Vijaykumar Jain <https://github.com/cabecada
> <https://github.com/cabecada>;
>
> --
> Adrian Klaver
> [email protected] <mailto:[email protected]>
>
--
Adrian Klaver
[email protected]
view thread (4+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: how to know if the sql will run a seq scan
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox