Feedback-ID: i76984098:Fastmail
Message-ID: <7448286f-6868-4f77-b457-5a1a943d8576@aklaver.com>
Date: Tue, 15 Oct 2024 14:29:39 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: how to know if the sql will run a seq scan
To: Vijaykumar Jain <vijaykumarjain.github@gmail.com>
Cc: pgsql-general <pgsql-general@postgresql.org>
References: <CAM+6J96kmqmHJPWOU-Q9607BO9DiXCHAkHyuZtdF2Hc_6qVFtg@mail.gmail.com>
 <ed5e1318-b571-44ab-8a48-750159410097@aklaver.com>
 <CAM+6J96rMZJnEGvoQyuzmEw6oH6gg79A5Ahr4yKF_3RVYVM8mA@mail.gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <CAM+6J96rMZJnEGvoQyuzmEw6oH6gg79A5Ahr4yKF_3RVYVM8mA@mail.gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7448286f-6868-4f77-b457-5a1a943d8576%40aklaver.com>
Precedence: bulk

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 <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com