Re: Regarding feature #6841

On Tue, 23 Apr 2024 at 13:50, Dave Page <dpage(at)pgadmin(dot)org> wrote:

>
>
> On Tue, 23 Apr 2024 at 12:03, Thom Brown <thom(at)linux(dot)com> 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