Repository: sqlfluff/sqlfluff
Issue category: Bug Report

Postgres `SELECT ... LIMIT 1 FOR UPDATE` unparsable
### Search before asking

- [X] I searched the [issues](https://github.com/sqlfluff/sqlfluff/issues) and found no similar issues.


### What Happened

The Postgres docs (https://www.postgresql.org/docs/current/sql-select.html) seem to suggest that the row-level locking syntax `FOR UPDATE` follows after `LIMIT` clauses. However, SQLFluff fails to lint the query `SELECT foo FROM bar LIMIT 1 FOR UPDATE`. This query syntax is acceptable to Postgres, as is the opposite order `SELECT foo FROM bar FOR UPDATE LIMIT 1`. SQLFluff only accepts this latter structure, though.

### Expected Behaviour

Linting successful with no parsing errors.

### Observed Behaviour

```
== [stdin] FAIL                                                                                                                                                
L:   1 | P:  29 |  PRS | Line 1, Position 29: Found unparsable section: 'FOR
                       | UPDATE'
WARNING: Parsing errors found and dialect is set to 'postgres'. Have you configured your dialect correctly?
All Finished 📜 🎉!
```

### How to reproduce

```bash
echo 'SELECT foo FROM bar LIMIT 1 FOR UPDATE' | sqlfluff lint --dialect=postgres -
```

### Dialect

postgres

### Version

sqlfluff, version 3.2.5
Python 3.11.10


### Configuration

N/A

### Are you willing to work on and submit a PR to address the issue?

- [ ] Yes I am willing to submit a PR!

### Code of Conduct

- [X] I agree to follow this project's [Code of Conduct](https://github.com/sqlfluff/sqlfluff/blob/main/CODE_OF_CONDUCT.md)



Functions to edit:
src/sqlfluff/dialects/dialect_postgres.py:SelectStatementSegment
Functions added: src/sqlfluff/dialects/dialect_postgres.py:FetchClauseSegment

Patch:
diff --git a/src/sqlfluff/dialects/dialect_postgres.py b/src/sqlfluff/dialects/dialect_postgres.py
index a5e2334034e..105f1a74066 100644
--- a/src/sqlfluff/dialects/dialect_postgres.py
+++ b/src/sqlfluff/dialects/dialect_postgres.py
@@ -1670,6 +1670,26 @@ class ForClauseSegment(BaseSegment):
     )
 
 
+class FetchClauseSegment(ansi.FetchClauseSegment):
+    """A `FETCH` clause like in `SELECT."""
+
+    type = "fetch_clause"
+    match_grammar: Matchable = Sequence(
+        "FETCH",
+        OneOf(
+            "FIRST",
+            "NEXT",
+        ),
+        OneOf(
+            Ref("NumericLiteralSegment"),
+            Ref("ExpressionSegment", exclude=Ref.keyword("ROW")),
+            optional=True,
+        ),
+        OneOf("ROW", "ROWS"),
+        OneOf("ONLY", Sequence("WITH", "TIES")),
+    )
+
+
 class UnorderedSelectStatementSegment(ansi.UnorderedSelectStatementSegment):
     """Overrides ANSI Statement, to allow for SELECT INTO statements."""
 
@@ -1688,19 +1708,20 @@ class UnorderedSelectStatementSegment(ansi.UnorderedSelectStatementSegment):
 
 
 class SelectStatementSegment(ansi.SelectStatementSegment):
-    """Overrides ANSI as the parse grammar copy needs to be reapplied."""
+    """Overrides ANSI as the parse grammar copy needs to be reapplied.
+
+    As per https://www.postgresql.org/docs/current/sql-select.html
+    """
 
     # Inherit most of the parse grammar from the unordered version.
     match_grammar: Matchable = UnorderedSelectStatementSegment.match_grammar.copy(
         insert=[
+            Ref("NamedWindowSegment", optional=True),
             Ref("OrderByClauseSegment", optional=True),
             Ref("LimitClauseSegment", optional=True),
-            Ref("NamedWindowSegment", optional=True),
-        ]
-    ).copy(
-        insert=[Ref("ForClauseSegment", optional=True)],
-        before=Ref("LimitClauseSegment", optional=True),
-        # Overwrite the terminators, because we want to remove some.
+            Ref("FetchClauseSegment", optional=True),
+            Ref("ForClauseSegment", optional=True),
+        ],
         replace_terminators=True,
         terminators=[
             Ref("SetOperatorSegment"),

Test patch: diff --git a/test/fixtures/dialects/postgres/select.sql b/test/fixtures/dialects/postgres/select.sql
index 6c874ea279a..392d157b423 100644
--- a/test/fixtures/dialects/postgres/select.sql
+++ b/test/fixtures/dialects/postgres/select.sql
@@ -78,8 +78,9 @@ SELECT col1, col2
 FROM mytable1
 JOIN mytable2 ON col1 = col2
 ORDER BY sync_time ASC
+LIMIT 1
 FOR SHARE OF mytable1, mytable2 SKIP LOCKED
-LIMIT 1;
+;
 
 Select * from foo TABLESAMPLE SYSTEM (10);
 
@@ -94,3 +95,14 @@ SELECT 1 /* hi hi /* foo */ ho ho */ AS bar;
 
 -- escape double quotes
 SELECT """t".col1 FROM tbl1 AS """t";
+
+SELECT
+    film_id,
+    title
+FROM
+    film
+ORDER BY
+    title
+FETCH FIRST 10 ROW ONLY;
+
+SELECT foo FROM bar LIMIT 1 FOR UPDATE;
diff --git a/test/fixtures/dialects/postgres/select.yml b/test/fixtures/dialects/postgres/select.yml
index d10eb262746..1acd591ee16 100644
--- a/test/fixtures/dialects/postgres/select.yml
+++ b/test/fixtures/dialects/postgres/select.yml
@@ -3,7 +3,7 @@
 # computed by SQLFluff when running the tests. Please run
 # `python test/generate_parse_fixture_yml.py`  to generate them after adding or
 # altering SQL files.
-_hash: 67e1713643ad1d884f3672640f2d755a6731920489c151d97e18eb148e71cf48
+_hash: 5345b0bcb3f5ac09c7500b419c2a23032d3c1385fa4db9212dcbd44134ad1e33
 file:
 - statement:
     select_statement:
@@ -785,6 +785,9 @@ file:
       - column_reference:
           naked_identifier: sync_time
       - keyword: ASC
+      limit_clause:
+        keyword: LIMIT
+        numeric_literal: '1'
       for_clause:
       - keyword: FOR
       - keyword: SHARE
@@ -796,9 +799,6 @@ file:
           naked_identifier: mytable2
       - keyword: SKIP
       - keyword: LOCKED
-      limit_clause:
-        keyword: LIMIT
-        numeric_literal: '1'
 - statement_terminator: ;
 - statement:
     select_statement:
@@ -942,3 +942,54 @@ file:
               keyword: AS
               quoted_identifier: '"""t"'
 - statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+      - keyword: SELECT
+      - select_clause_element:
+          column_reference:
+            naked_identifier: film_id
+      - comma: ','
+      - select_clause_element:
+          column_reference:
+            naked_identifier: title
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: film
+      orderby_clause:
+      - keyword: ORDER
+      - keyword: BY
+      - column_reference:
+          naked_identifier: title
+      fetch_clause:
+      - keyword: FETCH
+      - keyword: FIRST
+      - numeric_literal: '10'
+      - keyword: ROW
+      - keyword: ONLY
+- statement_terminator: ;
+- statement:
+    select_statement:
+      select_clause:
+        keyword: SELECT
+        select_clause_element:
+          column_reference:
+            naked_identifier: foo
+      from_clause:
+        keyword: FROM
+        from_expression:
+          from_expression_element:
+            table_expression:
+              table_reference:
+                naked_identifier: bar
+      limit_clause:
+        keyword: LIMIT
+        numeric_literal: '1'
+      for_clause:
+      - keyword: FOR
+      - keyword: UPDATE
+- statement_terminator: ;