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 1rzcu9-005R3T-R1 for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 Apr 2024 13:43:21 +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 1rzcu5-00FH5W-Oh for pgadmin-hackers@arkaria.postgresql.org; Wed, 24 Apr 2024 13:43:17 +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 1rzcu5-00FH5O-DE for pgadmin-hackers@lists.postgresql.org; Wed, 24 Apr 2024 13:43:17 +0000 Received: from mail-wr1-f44.google.com ([209.85.221.44]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1rzcu2-004QHZ-Mg for pgadmin-hackers@postgresql.org; Wed, 24 Apr 2024 13:43:16 +0000 Received: by mail-wr1-f44.google.com with SMTP id ffacd0b85a97d-3499f1bed15so567968f8f.1 for ; Wed, 24 Apr 2024 06:43:14 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1713966193; x=1714570993; 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=o4vQmNy5V+6jCAoE+5FVu1l1e06KXhOvK4wZy+qToFc=; b=o6GgDiNOKxeni0qhij/VZsJCWNkMssVXrJRns6rcs7NCiKCOnxE74KJpaqPPs9wWyx YcLtVsIGO7UTBswYlpoI3Lr162P4p6coUlBauDqAhSEtd2Eg6bCxwmRPdY3nnB5pjmPc ktiRf5MP7S5eziCvKak+aiGP8eBR0fDa23Zgf47LP35ATSHPKHqOwSSEbrdRYSeCsZlz XqogyWdf0J8R+DGiyn0U5TDdbDyHqDH4krtMtyjcTvbopkiWiom/hl37b9DQ9hoqj7qf +0ePLJQeilUrWcCMJl0sDI3EzlgC1ImrOWj7hqeB0U8JQG+Y908HW9xTRk13OdkuZcwF BsrA== X-Forwarded-Encrypted: i=1; AJvYcCWYisuKN29JPuv/A2vMvNg/miqsfnzBlYDjxK6OVo5Ib4oGGIug6tLYWNq0aFztFdKw+c3mguyR6Cqai6OlNA3u30Isr55cOUCeZMUTYbg= X-Gm-Message-State: AOJu0YyuX3gwLqcVGvZ+fWGBytPUMCRyEeRbeczcGpXcbk58OY7rSkT+ rnn/qTTSlzbscoE1uJ3/vjjc0XeBxO3dB7EwuhwQQAqZFc16o6iQ61Ye1vWpe/rgHzx+zzyezIh V3faB97YhQI3upVPe79+5QfGuIpI= X-Google-Smtp-Source: AGHT+IGJCC9dxJRQxeMchGtK7LApWojQVZDU/0/qhxZF8LFw0d6JqFZNiBqz4XyGaL7RW2ByVZo7SUI5QW8SB14oC9Q= X-Received: by 2002:adf:f646:0:b0:34b:7380:65f9 with SMTP id x6-20020adff646000000b0034b738065f9mr1989251wrp.29.1713966192693; Wed, 24 Apr 2024 06:43:12 -0700 (PDT) MIME-Version: 1.0 References: In-Reply-To: From: Thom Brown Date: Wed, 24 Apr 2024 14:42:45 +0100 Message-ID: Subject: Re: Regarding feature #6841 To: Dave Page Cc: Aditya Toshniwal , Anil Sahoo , pgadmin-hackers Content-Type: multipart/alternative; boundary="0000000000001299fb0616d7da6c" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --0000000000001299fb0616d7da6c Content-Type: text/plain; charset="UTF-8" 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: -- 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 --0000000000001299fb0616d7da6c Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, 23 Apr 2024 at 13:50, Dave Page &= lt;dpage@pgadmin.org> wrote:


On Tue, 23 Apr 2024 at 12:03, Th= om Brown <thom@linux= .com> wrote:

You've been able to do the "Select and r= un" thing for years. If you select text in the editor and hit the exec= ute button, only the selected text is sent to the server. If nothing is sel= ected, the entire string is sent. This feature will complement that for con= venience, but for safety will have a separate button/shortcut.
<= /div>

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

Boo!
=C2=A0

I still find the proposal somewhat unintuitive, but t= he foot-gun safeguards that have been suggested sound like any pedal injuri= es will solely be the fault of the user.

<= div dir=3D"auto">I would want to see it tested in a diverse range of scenar= ios though, which will require some imagination given what users will no do= ubt try to use it on.

Yes, I ha= ve made that very clear to the team. Suggestions for test scenarios are wel= come of course - a good way to experiment might be to see how the current v= ersion of pgAdmin (which uses the new CodeMirror code) manages to mess up s= yntax highlighting of anything weird.

I guess here's a few to try out:

=
-- Put the cursor on every relation name, and every SELECT, DELE= TE 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 SEL= ECT id FROM mytable
=C2=A0 )
=C2=A0 RETURNING id, content
),
mo= ve_rows AS (
=C2=A0 INSERT INTO newtable
=C2=A0 -- Does this SELECT r= un on its own, or does it backtrack to the INSERT?
=C2=A0 SELECT id, con= tent
=C2=A0 FROM deleted_rows
),
combined_result AS(
=C2=A0 SEL= ECT 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 tabl= eoid::regclass, id, content
=C2=A0 FROM newtable
)
-- Does this SE= LECT get run on its own?
SELECT id, content
INTO backuptable
FROM = combined_result;


SELECT id, content
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 highlight?

=C2=A0 =C2=A0 Does= each level of the query and nested queries run 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 )
);


DO LANGUAGE plpgsq= l $SELECT$
DECLARE
=C2=A0 myrec RECORD;
=C2=A0 -- Does either SELE= CT in the cursor try to run when under PgAdmin's cursor?
=C2=A0 -- I= s there any backtracking when selecting the 2nd one?
=C2=A0 mycur CURSOR= FOR SELECT 1 FROM (SELECT (VALUES (1)));
BEGIN
=C2=A0 SELECT INTO ST= RICT myrec FROM (
=C2=A0 =C2=A0 -- Does selecting the following SELECT c= orrectly run without going
=C2=A0 =C2=A0 -- into the SELECT INTO?
=C2= =A0 =C2=A0 SELECT
=C2=A0-- Can you run the query that appears in the v= alue?
=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 these 4 DELETEs
=C2=A0 =C2= =A0 =C2=A0 $DELETE$DELETE$DELETE$ AS "DELETE"
=C2=A0 );
END=
$SELECT$;

None of this renders incorrectly in = PgAdmin though.

Thom
--0000000000001299fb0616d7da6c--