Re: Regarding feature #6841

On Wed, 24 Apr 2024 at 14:43, Thom Brown <thom@linux.com> wrote:

On Tue, 23 Apr 2024 at 13:50, Dave Page <dpage@pgadmin.org> wrote:

On Tue, 23 Apr 2024 at 12:03, Thom Brown <thom@linux.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