Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0oVb-00DK4N-0I for pgsql-general@arkaria.postgresql.org; Tue, 15 Oct 2024 20:51:11 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1t0oVZ-00BKOK-Bd for pgsql-general@arkaria.postgresql.org; Tue, 15 Oct 2024 20:51:09 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1t0oVY-00BKLf-SV for pgsql-general@lists.postgresql.org; Tue, 15 Oct 2024 20:51:09 +0000 Received: from mail-ej1-x643.google.com ([2a00:1450:4864:20::643]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1t0oVT-0013of-Lt for pgsql-general@postgresql.org; Tue, 15 Oct 2024 20:51:07 +0000 Received: by mail-ej1-x643.google.com with SMTP id a640c23a62f3a-a99ebb390a5so44353966b.1 for ; Tue, 15 Oct 2024 13:51:03 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1729025462; x=1729630262; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=EaejsPdv6rWNR7xP+HeDptLvmsQU8ax6m1fKSls75WM=; b=cL6gFkTZMhm8zvhkjXLp80aLDe6lgB/97hI+7uHC37C3M6VZcPg7UtWF4vVQSWX3n0 wNSI2j5TNJA+VIJTVAr8MiRQiS1vM0TZ77vdEGJJNzAbC59up2+vua4R9JVTyBSVumm5 CgrstcPiIpECiR9s+sBmsNDPdblPiIH1ZkNnuclO6Z5qjYDlyI4uldRcgN0XE3QBquiT K+Jx26ZqFItHXcKF9IABfNCSABm3lOfXp/fDBaIb4MjXxLA/5o5MMsFwwpaX9YY/kOIp zA6dpMlWHU1RnpO6zHPqfHtx/+VbsL3D0Zo3FaLQmL0RiJtwBXIDiaq/+yKTlrN5k1gT taVA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1729025462; x=1729630262; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=EaejsPdv6rWNR7xP+HeDptLvmsQU8ax6m1fKSls75WM=; b=jzTtIQvD0uxI2LJeCH/aGf8mRlU5pVJ+0NNKuJjvuEi0p8k4cz4wvOYqxiVKiE8qzM XNWKA6/JLnSI1soANUBg63TM3HJ9BboiwdQf91asKNbi1qeCernMIBoaWlzzsrNd7H+U IRFyatkLTXtbjYPqcJOO5+/YhkpoqJVSIl4AmkS9pCaHqDNnMtDEap1qK1nh+ZNwAIHV 78/QslFpgHRqdSlpsLO9WjpWh2JMAcSBUV+mW31zpcDzPsDG6kwUsAMJgZXLw2lh9Th9 U4hzFZj2CRIyuIZwHJFpCnA/0j6S6dfoWpPPcRqOgHSytbWUhzvnGemUXpmUYj0Wicld fwmQ== X-Gm-Message-State: AOJu0Yy9b9h0L+N80q907HoRe2xrMqXh4Mz7d8BVdkVPEBgI2uJGjS7b ePFOU+jBFTBh94MeRv5YQTTyUY2payJ/UPmdkRJ0YVRGNBbifGVfPkfn5TSeNWyy2/CeKOAYC5C 4iU7KGF3G1RKGCf1yqTlIcDN5qTNtaq1I X-Google-Smtp-Source: AGHT+IGaz0jWpQfL2JX0yAnGdFDABa0cN7WzyZEqKyekQZWSXCanDDpxsOn1g5DkKt+VL15EDD1fBG61ReXy79haW2A= X-Received: by 2002:a17:906:7951:b0:a9a:597:8cc9 with SMTP id a640c23a62f3a-a9a05978d8fmr900146766b.12.1729025461812; Tue, 15 Oct 2024 13:51:01 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Vijaykumar Jain Date: Wed, 16 Oct 2024 02:20:51 +0530 Message-ID: Subject: Re: how to know if the sql will run a seq scan To: Adrian Klaver Cc: pgsql-general Content-Type: multipart/alternative; boundary="00000000000075860a06248a1ca7" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000075860a06248a1ca7 Content-Type: text/plain; charset="UTF-8" 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. 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 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 > > "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 > > -- > Adrian Klaver > adrian.klaver@aklaver.com > > --00000000000075860a06248a1ca7 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable

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 v= alidate the constraint and that will create all sorts of problems.
I understand the benefit of not valid constraint and then validating constr= aint 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> 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 ma= y
> take an access exclusive lock while running=C2=A0a seq scan and hold f= or long.
>=C2=A0 =C2=A0And 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 th= e
> same, which helped, but it is not useful as it does not link me to wha= t
> 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 ma= y
> 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/doc= s/current/sql-altertable.html

"Scanning a large table to verify a new foreign key or check constrain= t
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<= /a>>

--
Adrian Klaver
adrian.klaver@aklaver.com

--00000000000075860a06248a1ca7--