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 1rzd7s-005Se0-Tl for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 Apr 2024 13:57:33 +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 1rzd7r-00FLfL-JH for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 Apr 2024 13:57:31 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rzd7r-00FLfD-At for pgadmin-hackers@lists.postgresql.org; Wed, 24 Apr 2024 13:57:31 +0000 Received: from mail-lf1-x136.google.com ([2a00:1450:4864:20::136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rzd7o-002iL7-Fd for pgadmin-hackers@postgresql.org; Wed, 24 Apr 2024 13:57:30 +0000 Received: by mail-lf1-x136.google.com with SMTP id 2adb3069b0e04-516d6c1e238so8721482e87.2 for ; Wed, 24 Apr 2024 06:57:27 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=pgadmin.org; s=google; t=1713967046; x=1714571846; 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=xildd5Odfq72uNn29IK2Q/5uxjhZRqseaBtrmB/kasU=; b=d14JN/6KoLDGcknMjKMauHuB2OlpYdAstUx253JZoXsi4CNTX+WpcCLRuM6SVA/Vx8 Po3p0wAebJgcmtdzeqKfbBAMlQ9i53xvH9qjocJvvMC2Rjy3W2dWw/6r1zW02TNqcvFy Mc2eAjiyzVJDJZ4WNJVPvWiIf3JdHeaJkBG2YXFcNtaLOlXjqH0k6yA47yfqW/fVyzan D8gRdvfG4qn/gB2DgmOFx5eo9ifD1wXgmWOjAzcMYXbIb7Zdds792pr5ACyiY5cDxml/ fa0s8qa8Mzabm9oWobzPsHlapdHvJqY+BTSdSoGNoNG7r/re6ExmOPN9okt19yi9bSOm Jd5g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713967046; x=1714571846; 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=xildd5Odfq72uNn29IK2Q/5uxjhZRqseaBtrmB/kasU=; b=YWujLONHMnuAaWkfvYVoDcLuq/YXXEHAKE+zT2zsm31UKqRuBN4riM2WxVT248GpQC BD2U6xKdsfiFKTNXrfiY1S+LTxm3FAfZpm2C9y+banx7dSzmuonCIAUlaBlJjw2wuXbZ ku55f/xTYN8yihb4izuibciWYVTyvWFhL5Ac97MKkMCAbhQr2TZsJegzWboyqXtuiPRa YKd958MO6DEbRmuWvVF+5X18t8dmd621ZG+CcUxvTjJJZ3p1mwFyrCm1jyfbC2OKWMWR TUdBCyZVZrUpp42rFNKqw8rvGo67PtVymbu0sBWas46XpN8QWkrsjNyioSwUAYk3l9GD h2ug== X-Forwarded-Encrypted: i=1; AJvYcCVnRoTdJtRX6Ce+iGs+T7DFU5jxoxs/C+I0/XLJMv6ZDWK4YGkDL1ldPfkLt10w8aZyKCxdWtBoG6dgvioQPIUC4LTdTrOOPT71/8atKxI= X-Gm-Message-State: AOJu0Yzz7soKq9+3BijGIwEaln2fjA9uQk6gl4kcFf2Nl924vqQByZqu IFudZmEeoLNplBFb98cVr2txAkdYmgPjbuuetFpCAT4IrEuJcvKZ+ltX2skcCQqS8AkYsFndHQZ 0N5Q7GJ0m5LULR8qtjWb8mpTUIcTL4gGAzGtp X-Google-Smtp-Source: AGHT+IGsYTLH4xmtT9u+0OUE/N6HXAnEav32iw9Gsfl0GsMfEXsxKzV/u/aE0zNk0rOwdvvT2IPElEjnv/QJ5W1jXi0= X-Received: by 2002:a05:6512:3eb:b0:518:9cbf:cd6b with SMTP id n11-20020a05651203eb00b005189cbfcd6bmr1780297lfq.13.1713967045869; Wed, 24 Apr 2024 06:57:25 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Dave Page Date: Wed, 24 Apr 2024 14:57:14 +0100 Message-ID: Subject: Re: Regarding feature #6841 To: Thom Brown Cc: Aditya Toshniwal , Anil Sahoo , pgadmin-hackers Content-Type: multipart/alternative; boundary="000000000000ed1f310616d80cbd" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000ed1f310616d80cbd Content-Type: text/plain; charset="UTF-8" On Wed, 24 Apr 2024 at 14:43, Thom Brown wrote: > On Tue, 23 Apr 2024 at 13:50, Dave Page wrote: > >> >> >> On Tue, 23 Apr 2024 at 12:03, Thom Brown wrote: >> >>> >>>> You've been able to do the "Select and run" thing for years. If you >>>> select text in the editor and hit the execute button, only the selected >>>> text is sent to the server. If nothing is selected, the entire string is >>>> sent. This feature will complement that for convenience, but for safety >>>> will have a separate button/shortcut. >>>> >>> >>> Oh, I clearly don't use PgAdmin enough to know this already. >>> >> >> Boo! >> >> >>> >>> I still find the proposal somewhat unintuitive, but the foot-gun >>> safeguards that have been suggested sound like any pedal injuries will >>> solely be the fault of the user. >>> >>> I would want to see it tested in a diverse range of scenarios though, >>> which will require some imagination given what users will no doubt try to >>> use it on. >>> >> >> Yes, I have made that very clear to the team. Suggestions for test >> scenarios are welcome of course - a good way to experiment might be to see >> how the current version of pgAdmin (which uses the new CodeMirror code) >> manages to mess up syntax highlighting of anything weird. >> > > I guess here's a few to try out: > Very helpful - thanks Thom! > > -- Put the cursor on every relation name, and every SELECT, DELETE and > INSERT > WITH deleted_rows AS ( > DELETE FROM mytable WHERE id IN ( > -- Does this run on its own? > SELECT id FROM mytable > ) > RETURNING id, content > ), > move_rows AS ( > INSERT INTO newtable > -- Does this SELECT run on its own, or does it backtrack to the INSERT? > SELECT id, content > FROM deleted_rows > ), > combined_result AS( > SELECT tableoid::regclass, id, content > FROM mytable > UNION ALL > -- Does this SELECT get run on its own? > SELECT tableoid::regclass, id, content > FROM newtable > ) > -- Does this SELECT get run on its own? > SELECT id, content > INTO backuptable > FROM combined_result; > > > SELECT id, content > FROM ( > /* > We are just performing: > SELECT id, content > FROM newtable; > ... at 2 levels > Does that commented query above highlight? > > Does each level of the query and nested queries run correctly? > */ > SELECT id, content, 'dummy1' > FROM ( > SELECT id, content, 'dummmy1', 'dummy2' > FROM newtable > ) > ); > > > DO LANGUAGE plpgsql $SELECT$ > DECLARE > myrec RECORD; > -- Does either SELECT in the cursor try to run when under PgAdmin's > cursor? > -- Is there any backtracking when selecting the 2nd one? > mycur CURSOR FOR SELECT 1 FROM (SELECT (VALUES (1))); > BEGIN > SELECT INTO STRICT myrec FROM ( > -- Does selecting the following SELECT correctly run without going > -- into the SELECT INTO? > SELECT > -- Can you run the query that appears in the value? > $$SELECT * FROM mytable$$ AS query, > > -- What happens when you select either of these SELECTs? > 'SELECT' AS "SELECT", > > -- And what happens on each one of these 4 DELETEs > $DELETE$DELETE$DELETE$ AS "DELETE" > ); > END > $SELECT$; > > None of this renders incorrectly in PgAdmin though. > > Thom > -- Dave Page pgAdmin: https://www.pgadmin.org PostgreSQL: https://www.postgresql.org EDB: https://www.enterprisedb.com --000000000000ed1f310616d80cbd Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Wed, 24 Apr 2024 at 14:43, Thom Br= own <thom@linux.com> wrote:
=
On Tue, 23 Apr 202= 4 at 13:50, Dave Page <dpage@pgadmin.org> wrote:


On Tue, 23 Apr 2024 at 1= 2:03, Thom Brown <th= om@linux.com> wrote:

You've been able to do the &= quot;Select and run" thing for years. If you select text in the editor= and hit the execute button, only the selected text is sent to the server. = If nothing is selected, the entire string is sent. This feature will comple= ment that for convenience, but for safety will have a separate button/short= cut.

Oh, I clearly don't use PgAdmin enough to know this already= .

Boo!
=C2=A0

I still find the proposal somewhat unintuitive, but the foot-gun safegu= ards that have been suggested sound like any pedal injuries will solely be = the fault of the user.

I= would want to see it tested in a diverse range of scenarios though, which = will require some imagination given what users will no doubt try to use it = on.

Yes, I have made that very = clear to the team. Suggestions for test scenarios are welcome of course - a= good way to experiment might be to see how the current version of pgAdmin = (which uses the new CodeMirror code) manages to mess up syntax highlighting= of anything weird.

I guess here's a few to try out:
Very helpful - thanks Thom!
=C2=A0

-- Put the cursor on every relatio= n name, and every SELECT, DELETE and INSERT
WITH deleted_rows AS (
= =C2=A0 DELETE FROM mytable WHERE id IN (
=C2=A0 =C2=A0 -- Does this run = on its own?
=C2=A0 =C2=A0 SELECT id FROM mytable
=C2=A0 )
=C2=A0 R= ETURNING id, content
),
move_rows AS (
=C2=A0 INSERT INTO newtable=
=C2=A0 -- Does this SELECT run on its own, or does it backtrack to the = INSERT?
=C2=A0 SELECT id, content
=C2=A0 FROM deleted_rows
),
c= ombined_result AS(
=C2=A0 SELECT tableoid::regclass, id, content
=C2= =A0 FROM mytable
=C2=A0 UNION ALL
=C2=A0 -- Does this SELECT get run = on its own?
=C2=A0 SELECT tableoid::regclass, id, content
=C2=A0 FROM= newtable
)
-- Does this SELECT get run on its own?
SELECT id, con= tent
INTO backuptable
FROM combined_result;


SELECT id, con= tent
FROM (
=C2=A0 /*
=C2=A0 =C2=A0 We are just performing:
=C2= =A0 =C2=A0 SELECT id, content
=C2=A0 =C2=A0 FROM newtable;
=C2=A0 =C2= =A0 ... at 2 levels
=C2=A0 =C2=A0 Does that commented query above highli= ght?

=C2=A0 =C2=A0 Does each level of the query and nested queries r= un correctly?
=C2=A0 */
=C2=A0 SELECT id, content, 'dummy1'=C2=A0 FROM (
=C2=A0 =C2=A0 =C2=A0SELECT id, content, 'dummmy1'= ;, 'dummy2'
=C2=A0 =C2=A0 =C2=A0FROM newtable
=C2=A0 )
);<= br>

DO LANGUAGE plpgsql $SELECT$
DECLARE
=C2=A0 myrec RECORD;<= br>=C2=A0 -- Does either SELECT in the cursor try to run when under PgAdmin= 's cursor?
=C2=A0 -- Is there any backtracking when selecting the 2n= d one?
=C2=A0 mycur CURSOR FOR SELECT 1 FROM (SELECT (VALUES (1)));
B= EGIN
=C2=A0 SELECT INTO STRICT myrec FROM (
=C2=A0 =C2=A0 -- Does sel= ecting the following SELECT correctly run without going
=C2=A0 =C2=A0 --= into the SELECT INTO?
=C2=A0 =C2=A0 SELECT
=C2=A0-- Can you run th= e query that appears in the value?
=C2=A0 =C2=A0 =C2=A0 $$SELECT * FROM = mytable$$ AS query,
=C2=A0
=C2=A0-- What happens when you select = either of these SELECTs?
=C2=A0 =C2=A0 =C2=A0 'SELECT' AS "= SELECT",
=C2=A0
=C2=A0-- And what happens on each one of the= se 4 DELETEs
=C2=A0 =C2=A0 =C2=A0 $DELETE$DELETE$DELETE$ AS "DELETE= "
=C2=A0 );
END
$SELECT$;

None of th= is renders incorrectly in PgAdmin though.

Thom


--
Dave Page
--000000000000ed1f310616d80cbd--