[CT-2733] [Regression] SQLFluff dbt templater fails with TypeError: cannot pickle 'ContextVar' object

[katieclaiborne]

Is this a new bug in dbt-core?

• I believe this is a new bug in dbt-core
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

When running with dbt-core v1.5.2, the sqlfluff-lint pre-commit hook fails with the type error below.

TypeError: cannot pickle 'ContextVar' object

Expected Behavior

I expected the hook to run successfully.

Steps To Reproduce

1. Initialize a dbt project in the root directory of a GitHub repository.
2. Create a .pre-commit-config.yaml file with the configuration below.

repos:
- repo: https://github.com/sqlfluff/sqlfluff
  rev: 2.1.0
  hooks:
    - id: sqlfluff-lint
      additional_dependencies: [
        'dbt-bigquery>=1.5,<1.6',
        'dbt-core==1.5.2',
        'sqlfluff-templater-dbt==2.1.0'
      ]

3. Create a .sqlfluff file with the configuration below.

[sqlfluff]
dialect = bigquery
templater = dbt
exclude_rules = layout.spacing, structure.using
max_line_length = 110

[sqlfluff:templater:dbt]
project_dir = .
profiles_dir = ~/.dbt

[sqlfluff:indentation]
# See https://docs.sqlfluff.com/en/stable/layout.html#configuring-indent-locations
tab_space_size = 2

[sqlfluff:rules:capitalisation.keywords]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.identifiers]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.functions]
extended_capitalisation_policy = lower

[sqlfluff:rules:capitalisation.literals]
capitalisation_policy = lower

[sqlfluff:rules:capitalisation.types]
extended_capitalisation_policy = lower

4. Create a .sqlfluffignore file with the configuration below.

# dbt output files
target/
dbt_packages/

# other
analysis/
macros/

5. Install pre-commit package manager

pip install pre-commit

6. Install git hook scripts

pre-commit install

8. Run the SQLFluff lint pre-commit hook over a dbt model.

pre-commit run sqlfluff-lint --files <path/to/dbt_model.sql>

Relevant log output

[INFO] Initializing environment for https://github.com/sqlfluff/sqlfluff:dbt-bigquery>=1.5,<1.6,dbt-core==1.5.2,sqlfluff-templater-dbt==2.1.0.
[INFO] Installing environment for https://github.com/sqlfluff/sqlfluff.
[INFO] Once installed this environment will be reused.
[INFO] This may take a few minutes...
sqlfluff-lint............................................................Failed
- hook id: sqlfluff-lint
- exit code: 1

=== [dbt templater] Sorting Nodes...
17:39:17  Registered adapter: bigquery=1.5.3
=== [dbt templater] Compiling dbt project...
=== [dbt templater] Project Compiled.
Traceback (most recent call last):
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/bin/sqlfluff", line 8, in <module>
    sys.exit(cli())
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/click/core.py", line 1130, in __call__
    return self.main(*args, **kwargs)
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/click/core.py", line 1055, in main
    rv = self.invoke(ctx)
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/click/core.py", line 1657, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/click/core.py", line 1404, in invoke
    return ctx.invoke(self.callback, **ctx.params)
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/click/core.py", line 760, in invoke
    return __callback(*args, **kwargs)
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/sqlfluff/cli/commands.py", line 605, in lint
    result = lnt.lint_paths(
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/sqlfluff/core/linter/linter.py", line 1206, in lint_paths
    for i, linted_file in enumerate(runner.run(expanded_paths, fix), start=1):
  File "/Users/katie.claiborne/.cache/pre-commit/repo3vyhin5m/py_env-python3.9/lib/python3.9/site-packages/sqlfluff/core/linter/runner.py", line 142, in run
    for lint_result in self._map(
  File "/Users/katie.claiborne/.pyenv/versions/3.9.10/lib/python3.9/multiprocessing/pool.py", line 870, in next
    raise value
  File "/Users/katie.claiborne/.pyenv/versions/3.9.10/lib/python3.9/multiprocessing/pool.py", line 537, in _handle_tasks
    put(task)
  File "/Users/katie.claiborne/.pyenv/versions/3.9.10/lib/python3.9/multiprocessing/connection.py", line 211, in send
    self._send_bytes(_ForkingPickler.dumps(obj))
  File "/Users/katie.claiborne/.pyenv/versions/3.9.10/lib/python3.9/multiprocessing/reduction.py", line 51, in dumps
    cls(buf, protocol).dump(obj)
TypeError: cannot pickle 'ContextVar' object

Environment

- OS: macOS Ventura 13.4
- Python: Python 3.9.10
- dbt: dbt-core==1.5.2, dbt-bigquery==1.5.3

Which database adapter are you using with dbt?

bigquery

Additional Context

@MarcBook has opened a similar issue in the SQLFluff repo: sqlfluff/sqlfluff#4930

I agree with his assessment that ca73a2a may have caused this bug.

[dbeatty10]

Thank you for a fantastic write-up @katieclaiborne 🤩

I haven't gone through the steps to reproduce this yet, but we'll give this a look and get back to you. We will specifically take a look at any downstream effects of ca73a2a.

[katieclaiborne]

Thank you, Doug!

[dbeatty10]

@gshank is there a value that root should take as a default here in the case cv_project_root is not set here?

[greg-finley]

Thanks so much for your attention to this issue! I will mention that it's blocking the next SQLFLuff release (which contains an unrelated security fix): sqlfluff/sqlfluff#4931

[dbeatty10]

Thanks for calling out the effect that it is having on your next release @greg-finley.

We don't have a firm timeline to fix and release this. In the meantime, would it be an option for sqlfluff to disallow dbt-core==1.5.2 within setup.cfg (or wherever you specify dependencies)?

[gshank]

If we don't have a project and hence a project_root, I presume we'd have to fallback to cwd.

[gshank]

This actually doesn't have anything to do with the project_root. It's that multiprocessing needs to pickle the code and can't pickle ContextVars.

[gshank]

It's been a very busy week... Could someone verify if the changes in #7949 fixes this issue? We don't use multiprocessing in our code...

[amardatar]

Hey @gshank yeah I tried testing this and it seems to do the job. We use redshift and I haven't tried testing this way before but I think it would work - basically running cloned versions of dbt-core and dbt-redshift, with dbt-core checked out to ct-2733-multiprocessing_contextvars and dbt-redshift checked out to main, and dbt-redshift repointed to install from the local checked out version of dbt-core (mostly just explaining this in case my methodology is bad).

Screenshot with some output and SQLFluff did succeed with that.

[dbeatty10]

@gshank I'm testing the changes in #7949 right now.

[dbeatty10]

@gshank Similar to the positive report by @amardatar, this worked for me too ✅

Details

First, using a branch from dbt-core 1.5.latest, I made sure to reproduce the error reported in sqlfluff/sqlfluff#4930:

LookupError: <ContextVar name='project_root' at 0xffffaf887010>

and also the error message reported in #7937:

TypeError: cannot pickle 'ContextVar' object

Then I cherry-picked your commits into #7970 (resolving one small merge conflict along the way). With your changes, both of the following worked without error:

sqlfluff lint models/my_model.sql
pre-commit run sqlfluff-lint --files models/my_model.sql