Tuple concurrently updated postgresql

«tuple concurrently updated» with Postgres #3508

Comments

dl-lim commented Jun 29, 2021 •

Describe the bug

Error while creating a view in postgres

Occassionally also throws this:
tuple concurrently updated

Steps To Reproduce

Have 2 sql files under the same model — they work when only one exists. But when two exist in the same folder (model), the error above throws.

The two sql files refer to different tables within the same schema.

Expected behavior

2 views should be created without errors

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt —version :

The operating system you’re using:
Ubuntu 20.0.4

The output of python —version :
Python 3.8.5

The text was updated successfully, but these errors were encountered:

jtcohen6 commented Jun 29, 2021

Thanks for opening @alderson59. I don’t quite understand what you mean by:

Have 2 sql files under the same model

Could you share the code for the two models in your project? Obviously scrubbed of any sensitive details.

dl-lim commented Jun 29, 2021 •

It’s a large table, so I try to show some of the different types of columns queried and selected.

Important to note that these queries are all working on their own — tested via the CLI, adminer, as well as via dbt (when standalone — meaning I removed one of the sql files from the directory when doing dbt run )

view1.sql

view2.sql

Have 2 sql files under the same model

Apologies, should have elaborated further. I also probably misunderstood what was meant by a «model» when I posted that. I now understand each model is its own .sql file

What I meant was, 2 sql files under the same folder/dir.

Directory structure:

dbt_project.yml:

Ran with dbt run —models tag:some_tag — didn’t seem to be any problems here.

jtcohen6 commented Jul 2, 2021

@alderson59 Thanks for the details, it sounds like you’ve been able to run these models successfully. Are you still seeing the reported error when you dbt run ?

dl-lim commented Jul 2, 2021 •

yep, nothing has changed since I last posted this. Are you able to reproduce the issue?

I suspect the two models are trying to run concurrently and postgres doesn’t like that? I did set threads: 10 in profiles.yml if that helps.

Postgres is postgres:13.0, default unmodified official docker image for additional context.

sdabhi23 commented Jul 3, 2021 •

I am also facing the same issue

Describe the bug

I have 2 models which have some source tables in common. Both are materialised as views. When running dbt on 2 threads, the creation of the 2 views happens in parallel and one of the views throws this error. When I run these models one-by-one using the model selection syntax, they are running perfectly!

It might be possible that this error is not related to having common set of source tables

Steps To Reproduce

Create 2 models whose materialization is set to view with some source tables in common. Set threads > 1 in profiles.yml . Do dbt run

It might be possible that this error is not related to having common set of source tables

Expected behavior

The models should get created without any error

Screenshots and log output

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt —version :

The operating system you’re using:
Ubuntu 18.04

The output of python —version :
Python 3.6.9

Additional context

Add any other context about the problem here.

dl-lim commented Jul 4, 2021

It might be possible that this error is not related to having common set of source tables

To chime in on this, I am in fact using two separate tables, but both from the same schema in the same db.

When running dbt on 2 threads, the creation of the 2 views happens in parallel and one of the views throws this error. When I run these models one-by-one using the model selection syntax, they are running perfectly!

Spot on! When running 2 at once, one of the views get created, while the other doesn’t due to the lock.

DAG flow-wise, the 2 models should be running concurrently side-by-side, which probably means dbt/postgres has trouble reading two queries at once.

Can we bump this to a higher priority? Thanks.

dl-lim commented Jul 4, 2021

Ok, I hacked this a little, and it’s a temporary fix as I think there may be some other underlying issues here.

So, knowing that two independent models can’t run concurrently from the same db, I made the second one as a downstream of the first, and to do that, we need to use the <> function without actually parsing the SQL — so, I commented the line via SQL as such — <> . Now it works, but it runs downstream of the first.

sdabhi23 commented Jul 4, 2021

In this case you lose the parallelism of running on multiple threads ☹️ A simpler solution would be to run the views one-by-one if you con’t have many views or switching to single threaded execution.

I would like to add that this only happens when 2 views are running in parallel. Not when a table and a view are running in parallel.

dl-lim commented Jul 7, 2021

Bump on this issue, please 🙂

The hack I’m using above is causing me to have to write a bunch of dependencies in each model in my web of views, sadly. Lots of unnecessary debugging to ensure that it works as it should have, whether with or without concurrency.

jtcohen6 commented Jul 7, 2021

@alderson59 @sdabhi23 I’m looking into this issue, but I haven’t been able to reproduce it myself. I’d like to gather some more information, if possible:

• Could you each share the version of Postgres you’re using?
• Could you each share (from logs/dbt.log ) the exact SQL that dbt runs right before Postgres returns deadlock detected / tuple concurrently updated ? My guess is that this happens when it tries to either rename or drop the existing version of the view.

sdabhi23 commented Jul 8, 2021

Postgres version: 11.10 logs/dbt.log : I have post hooks running which grant permissions on all objects to various roles. Those are the last SQL statements running on either of the threads. Looks like I should have done permissions on the object being created instead of all objects

jtcohen6 commented Jul 9, 2021

Aha! This sounds promising, and closer to a known quantity. We recommend that post-hook grants be specific to the model just created, and grants on all objects be executed from on-run-end hooks instead.

What happens if you change the post-hook grant to be specific to << this >> ?

dl-lim commented Jul 12, 2021

@jtcohen6 thanks for looking into this!

Just like @sdabhi23 , I also have post hooks running grant perms. I set this in dbt_project.yml to the tune of:
GRANT SELECT ON ALL TABLES IN SCHEMA public,schema1,schema2 TO user; since the view models reference data from different schemas. This needs to be rebuilt on each run with our use case.

I’m not sure if << this >> nor << this.schema >> would do any good here.

jtcohen6 commented Jul 12, 2021

It seems we’ve found the culprit: concurrent grants on the same objects.

@alderson59 It sounds like your approach may want to be:

• Once at the start of a run, grant select on all source schemas to user
• After each model runs, grant select on just that model to user

In practice, that could look like:

I’m going to close this issue—I don’t believe we need to make any code changes in dbt.

Источник

postgresql Error tuple concurrently updated #821

Comments

mrnonz commented Aug 1, 2016

On my server use sameersbn/gitlab:latest and sameersbn/postgresql:9.4-24

When I try to server gitlab-server I got this message

gitlab_1 | Initializing logdir.
gitlab_1 | Initializing datadir.
gitlab_1 | Updating CA certificates.
postgresql_1 | Initializing certdir.
postgresql_1 | Initializing logdir.
postgresql_1 | Initializing rundir.
postgresql_1 | Setting resolv.conf ACLs.
postgresql_1 | Creating database user: gitlab
postgresql_1 | Creating database: gitlabhq_production.
postgresql_1 | ‣ Loading pg_trgm extension.
postgresql_1 | ‣ Granting access to gitlab user.
postgresql_1 | ERROR: tuple concurrently updated
gitlab_1 | Installing configuration templates.
gitlab_1 | Configuring gitlab.
gitlabdocker_postgresql_1 exited with code 1
postgresql_1 | Initializing datadir.
postgresql_1 | Initializing certdir.
postgresql_1 | Initializing logdir.
postgresql_1 | Initializing rundir.
postgresql_1 | Setting resolv.conf ACLs.
postgresql_1 | Creating database user: gitlab
postgresql_1 | Creating database: gitlabhq_production.
postgresql_1 | ‣ Loading pg_trgm extension.
postgresql_1 | ‣ Granting access to gitlab user.
postgresql_1 | ERROR: tuple concurrently updated
postgresql_1 | Initializing datadir.
postgresql_1 | Initializing certdir.
postgresql_1 | Initializing logdir.
postgresql_1 | Initializing rundir.
postgresql_1 | Setting resolv.conf ACLs.
postgresql_1 | Creating database user: gitlab
postgresql_1 | Creating database: gitlabhq_production.
postgresql_1 | ‣ Loading pg_trgm extension.
postgresql_1 | ‣ Granting access to gitlab user.
postgresql_1 | ERROR: tuple concurrently updated

The text was updated successfully, but these errors were encountered:

solidnerd commented Aug 1, 2016

Hey,
I tested it with your image configuration and I got no problems. Did you try a fresh install or an upgrade. I would recommend you to use a version number of the GitLab image so it’s easier to find the problem and you know which version you running.

lwolf commented Nov 25, 2016

got the same issue today, after trying to update postgresql container from 9.5-1 to 9.5-3.
Now I can’t start postgres at all.

damoon commented Apr 3, 2017

same problem here. can anyone remember how to fix it?

heiderich commented May 23, 2017

I am also experiencing this problem. I should mention that just before the docker host system ran out of free disk space. I made sure that disk space is available and restarted the docker host. Since the restart I am experiencing this problem.

Recently I upgraded from gitlab 9.0.0 to 9.1.4. I am now on postgresql 9.6-2.

boxofrox commented Jun 7, 2017 •

I ran into this myself after my server ran into a batch of problems: thermal limit shutdowns and rampant every-minute gitlab backups that led to zero free disk space.

Here’s roughly the commands I ran that eventually resolved the issue for me.

Hope you have the same luck I did.

BirgerK commented Nov 27, 2017

@boxofrox is saving me some sleep this night!

Highlighting something in his answer: It is important to spin up another postgres-Container manually, so you can run sudo -u $ -g $ $/postgres -D $ -d 5 in it. Then you’re able to spin up again your production-container!

luismadrigal commented Sep 14, 2018 •

@boxofrox thanks for posting this, it is already looking promising.
But can you please explain what these mean

Thanks, in advance!

Edit: Oh it looks like that is the command to run

boxofrox commented Sep 14, 2018 •

Oh it looks like that is the command to run

@luismadrigal yes, those are all options and variables for manually running postgres inside of its docker container.

But can you please explain what these mean

These PG_* variables are defined in the postgres Dockerfile [1], so once you’re at a shell inside the postgres container, they’re available and ready to use. Type env | grep -i pg_ to print the environment variables for review.

$ specifies both the process UID/GID the database service runs as.
$ holds a path to the directory where the postgres binary is located.
$ holds a path to where postgres stores database files.

And the -d 5 is a postgres option that sets the debugging level to 5 [2]. My intent here was to dump more information related to the tuple concurrently updated error into the server log file, but instead I found that the server ran without any problems, then I just methodically backed out to the composer utility, testing whether each tool between me and postgres might be causing the startup error (i.e. entrypoint.sh, docker compose).

stale bot commented May 7, 2020

This issue has been automatically marked as stale because it has not had any activity for the last 60 days. It will be closed if no further activity occurs during the next 7 days. Thank you for your contributions.

Источник

ERROR: tuple concurrently updated

Subject: ERROR: tuple concurrently updated Date: 2007-03-28 10:03:08 Message-ID: 528853D3C5ED2C4AA8990B504BA7FB850106DD94@sol.transas.com Views: Raw Message | Whole Thread | Download mbox | Resend email Thread: Lists: pgsql-bugs

Testing of the concurrent access to database objects leaded to the following error:

ERROR: tuple concurrently updated

SQL state: XX000

According to the «PostgreSQL Error Codes» table in the documentation, «XX000» is the PostgreSQL internal error code.

How to reproduce such an error:

1. Create a table in some database and view:

create table t1 ( id int );

create temp view v1 as select * from t1;

2. Concurrent access to table t1 is performed by 2 clients (further C1 and C2) particularly in this order:

C1: begin; drop view v1;

C2: drop table t1;

And finally, transaction of the client C2 is terminated with this «expected» error.

Responses

• Re: ERROR: tuple concurrently updated at 2007-03-28 18:02:19 from Tom Lane

Browse pgsql-bugs by date

From	Date	Subject
Next Message	Tom Lane	2007-03-28 18:02:19	Re: ERROR: tuple concurrently updated
Previous Message	Magnus Hagander	2007-03-28 08:33:02	Re: «Permission denied» failures occurring on Windows

• 
• 

Copyright © 1996-2022 The PostgreSQL Global Development Group

Источник