github.com/cockroachdb/cockroach@v20.2.0-alpha.1+incompatible/pkg/sql/pgwire/testdata/pgtest/portals (about)

     1  # Verify that a completed portal can't be re-executed.
     2  
     3  send
     4  Parse {"Query": "SELECT 1"}
     5  Bind
     6  Execute
     7  Sync
     8  ----
     9  
    10  until
    11  ReadyForQuery
    12  ----
    13  {"Type":"ParseComplete"}
    14  {"Type":"BindComplete"}
    15  {"Type":"DataRow","Values":[{"text":"1"}]}
    16  {"Type":"CommandComplete","CommandTag":"SELECT 1"}
    17  {"Type":"ReadyForQuery","TxStatus":"I"}
    18  
    19  send
    20  Execute
    21  Sync
    22  ----
    23  
    24  until
    25  ErrorResponse
    26  ReadyForQuery
    27  ----
    28  {"Type":"ErrorResponse","Code":"34000"}
    29  {"Type":"ReadyForQuery","TxStatus":"I"}
    30  
    31  # Verify that closing a bound portal prevents execution.
    32  
    33  # 80 = ASCII 'P'
    34  send
    35  Parse {"Name": "s", "Query": "SELECT 1"}
    36  Bind {"DestinationPortal": "p", "PreparedStatement": "s"}
    37  Close {"ObjectType": 80, "Name": "p"}
    38  Execute {"Portal": "p"}
    39  Sync
    40  ----
    41  
    42  until
    43  ErrorResponse
    44  ReadyForQuery
    45  ----
    46  {"Type":"ParseComplete"}
    47  {"Type":"BindComplete"}
    48  {"Type":"CloseComplete"}
    49  {"Type":"ErrorResponse","Code":"34000"}
    50  {"Type":"ReadyForQuery","TxStatus":"I"}
    51  
    52  # The spec says that closing a prepared statement also closes its portals,
    53  # but that doesn't seem to be the case. Below I would expect that Bind,
    54  # Close, Execute causes the execute to return an error, but it instead
    55  # returns the portal result. This happens in both Postgres and Cockroach.
    56  
    57  # 83 = ASCII 'S'
    58  # After closing, re-parse with the same name to make sure the execute
    59  # happens on the old statement.
    60  send
    61  Bind {"DestinationPortal": "p", "PreparedStatement": "s"}
    62  Close {"ObjectType": 83, "Name": "s"}
    63  Parse {"Name": "s", "Query": "SELECT 2"}
    64  Execute {"Portal": "p"}
    65  Sync
    66  ----
    67  
    68  until
    69  ReadyForQuery
    70  ----
    71  {"Type":"BindComplete"}
    72  {"Type":"CloseComplete"}
    73  {"Type":"ParseComplete"}
    74  {"Type":"DataRow","Values":[{"text":"1"}]}
    75  {"Type":"CommandComplete","CommandTag":"SELECT 1"}
    76  {"Type":"ReadyForQuery","TxStatus":"I"}
    77  
    78  # Portal still isn't destroyed within a transaction either, in PG or CR.
    79  
    80  send
    81  Query {"String": "BEGIN"}
    82  ----
    83  
    84  until
    85  ReadyForQuery
    86  ----
    87  {"Type":"CommandComplete","CommandTag":"BEGIN"}
    88  {"Type":"ReadyForQuery","TxStatus":"T"}
    89  
    90  send
    91  Bind {"DestinationPortal": "p", "PreparedStatement": "s"}
    92  Close {"ObjectType": 83, "Name": "s"}
    93  Parse {"Name": "s", "Query": "SELECT 3"}
    94  Execute {"Portal": "p"}
    95  Sync
    96  ----
    97  
    98  until
    99  ReadyForQuery
   100  ----
   101  {"Type":"BindComplete"}
   102  {"Type":"CloseComplete"}
   103  {"Type":"ParseComplete"}
   104  {"Type":"DataRow","Values":[{"text":"2"}]}
   105  {"Type":"CommandComplete","CommandTag":"SELECT 1"}
   106  {"Type":"ReadyForQuery","TxStatus":"T"}
   107  
   108  send
   109  Query {"String": "COMMIT"}
   110  ----
   111  
   112  until
   113  ReadyForQuery
   114  ----
   115  {"Type":"CommandComplete","CommandTag":"COMMIT"}
   116  {"Type":"ReadyForQuery","TxStatus":"I"}
   117  
   118  # Execute a portal with limited rows inside a transaction.
   119  
   120  send
   121  Query {"String": "BEGIN"}
   122  Parse {"Query": "SELECT * FROM generate_series(1, 2)"}
   123  Bind
   124  Execute {"MaxRows": 1}
   125  Sync
   126  ----
   127  
   128  until
   129  ReadyForQuery
   130  ReadyForQuery
   131  ----
   132  {"Type":"CommandComplete","CommandTag":"BEGIN"}
   133  {"Type":"ReadyForQuery","TxStatus":"T"}
   134  {"Type":"ParseComplete"}
   135  {"Type":"BindComplete"}
   136  {"Type":"DataRow","Values":[{"text":"1"}]}
   137  {"Type":"PortalSuspended"}
   138  {"Type":"ReadyForQuery","TxStatus":"T"}
   139  
   140  # This is the second of 2 rows, but we don't expect a command complete
   141  # yet.
   142  
   143  send
   144  Execute {"MaxRows": 1}
   145  Sync
   146  ----
   147  
   148  until
   149  ReadyForQuery
   150  ----
   151  {"Type":"DataRow","Values":[{"text":"2"}]}
   152  {"Type":"PortalSuspended"}
   153  {"Type":"ReadyForQuery","TxStatus":"T"}
   154  
   155  # There were only 2 rows, so this third execute should return a command
   156  # complete.
   157  
   158  send
   159  Execute {"MaxRows": 1}
   160  Sync
   161  ----
   162  
   163  until
   164  ReadyForQuery
   165  ----
   166  {"Type":"CommandComplete","CommandTag":"SELECT 0"}
   167  {"Type":"ReadyForQuery","TxStatus":"T"}
   168  
   169  send
   170  Query {"String": "COMMIT"}
   171  ----
   172  
   173  until
   174  ReadyForQuery
   175  ----
   176  {"Type":"CommandComplete","CommandTag":"COMMIT"}
   177  {"Type":"ReadyForQuery","TxStatus":"I"}
   178  
   179  send
   180  Query {"String": "SELECT 'here'"}
   181  ----
   182  
   183  until ignore=RowDescription
   184  ReadyForQuery
   185  ----
   186  {"Type":"DataRow","Values":[{"text":"here"}]}
   187  {"Type":"CommandComplete","CommandTag":"SELECT 1"}
   188  {"Type":"ReadyForQuery","TxStatus":"I"}
   189  
   190  # Execute a portal first with a row limit and then without.
   191  
   192  send
   193  Query {"String": "BEGIN"}
   194  Parse {"Query": "SELECT * FROM generate_series(1, 4)"}
   195  Bind
   196  Execute {"MaxRows": 1}
   197  Sync
   198  ----
   199  
   200  until
   201  ReadyForQuery
   202  ReadyForQuery
   203  ----
   204  {"Type":"CommandComplete","CommandTag":"BEGIN"}
   205  {"Type":"ReadyForQuery","TxStatus":"T"}
   206  {"Type":"ParseComplete"}
   207  {"Type":"BindComplete"}
   208  {"Type":"DataRow","Values":[{"text":"1"}]}
   209  {"Type":"PortalSuspended"}
   210  {"Type":"ReadyForQuery","TxStatus":"T"}
   211  
   212  send
   213  Execute
   214  Sync
   215  ----
   216  
   217  until
   218  ReadyForQuery
   219  ----
   220  {"Type":"DataRow","Values":[{"text":"2"}]}
   221  {"Type":"DataRow","Values":[{"text":"3"}]}
   222  {"Type":"DataRow","Values":[{"text":"4"}]}
   223  {"Type":"CommandComplete","CommandTag":"SELECT 3"}
   224  {"Type":"ReadyForQuery","TxStatus":"T"}
   225  
   226  send
   227  Query {"String": "COMMIT"}
   228  ----
   229  
   230  until
   231  ReadyForQuery
   232  ----
   233  {"Type":"CommandComplete","CommandTag":"COMMIT"}
   234  {"Type":"ReadyForQuery","TxStatus":"I"}
   235  
   236  send
   237  Query {"String": "SELECT 'here'"}
   238  ----
   239  
   240  until ignore=RowDescription
   241  ReadyForQuery
   242  ----
   243  {"Type":"DataRow","Values":[{"text":"here"}]}
   244  {"Type":"CommandComplete","CommandTag":"SELECT 1"}
   245  {"Type":"ReadyForQuery","TxStatus":"I"}
   246  
   247  # Execute a portal with a result limit. This is outside of a transaction
   248  # so we expect an error. This differs slightly from the postgres behavior,
   249  # which will do the first execute, auto close the portal, and then fail
   250  # on the second.
   251  
   252  send
   253  Parse {"Query": "SELECT * FROM generate_series(1, 2)"}
   254  Bind
   255  Execute {"MaxRows": 1}
   256  Sync
   257  ----
   258  
   259  until
   260  ReadyForQuery
   261  ----
   262  {"Type":"ParseComplete"}
   263  {"Type":"BindComplete"}
   264  {"Type":"DataRow","Values":[{"text":"1"}]}
   265  {"Type":"PortalSuspended"}
   266  {"Type":"ReadyForQuery","TxStatus":"I"}
   267  
   268  # Try the second execute, which we expect to fail because implicit
   269  # transactions auto close portals after the first suspension.
   270  
   271  send
   272  Execute
   273  Sync
   274  ----
   275  
   276  until
   277  ErrorResponse
   278  ReadyForQuery
   279  ----
   280  {"Type":"ErrorResponse","Code":"34000"}
   281  {"Type":"ReadyForQuery","TxStatus":"I"}
   282  
   283  send
   284  Query {"String": "SELECT 'here'"}
   285  ----
   286  
   287  until ignore=RowDescription
   288  ReadyForQuery
   289  ----
   290  {"Type":"DataRow","Values":[{"text":"here"}]}
   291  {"Type":"CommandComplete","CommandTag":"SELECT 1"}
   292  {"Type":"ReadyForQuery","TxStatus":"I"}
   293  
   294  # Execute a portal partially and close it.
   295  
   296  send
   297  Query {"String": "BEGIN"}
   298  Parse {"Query": "SELECT * FROM generate_series(1, 2)"}
   299  Bind
   300  Execute {"MaxRows": 1}
   301  Sync
   302  ----
   303  
   304  until
   305  ReadyForQuery
   306  ReadyForQuery
   307  ----
   308  {"Type":"CommandComplete","CommandTag":"BEGIN"}
   309  {"Type":"ReadyForQuery","TxStatus":"T"}
   310  {"Type":"ParseComplete"}
   311  {"Type":"BindComplete"}
   312  {"Type":"DataRow","Values":[{"text":"1"}]}
   313  {"Type":"PortalSuspended"}
   314  {"Type":"ReadyForQuery","TxStatus":"T"}
   315  
   316  # Close the empty portal then try to execute it. 80 = 'P'
   317  send
   318  Close {"ObjectType": 80}
   319  Execute
   320  Sync
   321  ----
   322  
   323  until
   324  ErrorResponse
   325  ReadyForQuery
   326  ----
   327  {"Type":"CloseComplete"}
   328  {"Type":"ErrorResponse","Code":"34000"}
   329  {"Type":"ReadyForQuery","TxStatus":"E"}
   330  
   331  send
   332  Query {"String": "ROLLBACK"}
   333  Query {"String": "SELECT 'here'"}
   334  ----
   335  
   336  until ignore=RowDescription
   337  ReadyForQuery
   338  ReadyForQuery
   339  ----
   340  {"Type":"CommandComplete","CommandTag":"ROLLBACK"}
   341  {"Type":"ReadyForQuery","TxStatus":"I"}
   342  {"Type":"DataRow","Values":[{"text":"here"}]}
   343  {"Type":"CommandComplete","CommandTag":"SELECT 1"}
   344  {"Type":"ReadyForQuery","TxStatus":"I"}