Re: Regarding feature #6841

On Wed, 24 Apr 2024 at 14:43, Thom Brown <thom(at)linux(dot)com> wrote:

> 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:
>

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