search

go-gitea / gitea

Git with a cup of tea! Painless self-hosted all-in-one software development service, including Git hosting, code review, team collaboration, package registry and CI/CD
https://gitea.com
MIT License
45.08k stars 5.49k forks source link

Postgres schema support #12505

Closed osbeker closed 4 years ago

osbeker commented 4 years ago

Description

I'm using Postgres (11) and I want to use a schema (-> I don't want my data in a public schema in my cluster and I may want to have dev and test and prod instances) I created a schema "prod" and configured it. After generating the schema, which seems successful, I soon get a "500" in the UI. I look into the log and see this: ... 2020/08/17 06:59:17 ...m.io/xorm/core/db.go:154:QueryContext() [I] [SQL] SELECT user_id, count(*) AS count FROM notification WHERE user_id IN (SELECT user_id FROM notification WHERE updated_unix >= $1 AND updated_unix < $2) AND status = $3 GROUP BY user_id [1597661945 1597661955 1] - 260.505µs 2020/08/17 06:59:17 ...ource/manager_run.go:41:Run() [E] Unable to get UIDcounts: pq: relation "notification" does not exist ...

Looking at the sql that was generated, I have to agree - there is not table "notification": that is because there only is a prod.notification.

I assume the problem is you do not qualify the table names properly for schema support?

Screenshots

lunny commented 4 years ago

I think we have support schema, you should set it in 

[database]
SCHEMA=
osbeker commented 4 years ago

hi, thanx for the reply. I did - my schema is named "prod"! ... [database] SCHEMA = prod ...

...and it created the schema in that schema - the relations are indeed in that schema (your app does the creation). ... but when attempting to do anything in the web-ui, I get a "500" page... Looking at the log I see the generated sql and the what appears the response from the dbms... example for "notification" table. If you look at the sql and the error message you will see the relation name is not prefixed with the schema qualifier! It will look for it in the "public" schema! But it is not there, it is in "prod", as it should be. -> Your sql generation is faulty, you need to prefix the relation name with the schema name! Is: "select from notification where ..." Expected" "select from prod.notification where ..."

I would look into it, but I don't do Go (yet).

I don't know if you generate it yourself or use a mapping tool. If it is a mapping tool, you may have to configure it to include the schema qualifier! I assume you use a mapping tool and a separate schema-generation (possibly tool)? The schema generation seems to check out - there query there after do not... "notification" is merely one example, none of the sql in my log are prefixed. My guess is if you use a tool, the tool would be smart enough to do that -> you may have forgotten to tell the tool that the schema lives in a schema.

Cheers, --C.

From: Lunny Xiao notifications@github.com Sent: Monday, August 17, 2020 8:16 AM To: go-gitea/gitea gitea@noreply.github.com Cc: osbeker osbeker@hotmail.com; Author author@noreply.github.com Subject: Re: [go-gitea/gitea] Postgres schema support (#12505)

I think we have support schema, you should set it in

[database] SCHEMA=

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHubhttps://github.com/go-gitea/gitea/issues/12505#issuecomment-674846556, or unsubscribehttps://github.com/notifications/unsubscribe-auth/ABRR4Z7W2RSYWPQSGMNQR2LSBENRBANCNFSM4QBQ662Q.

zeripath commented 4 years ago

I wonder if we simply need to do "SET search_path = ?", setting.Database.Schema in models/models.go:140:func getEngine()

zeripath commented 4 years ago

Actually that would probably suffice. We could prepend the search_path for maximum portability though:

db.Exec(`SELECT set_config(
    'search_path',
    ? || ',' || current_setting('search_path'),
    false
) WHERE current_setting('search_path') !~ '(^|,)'|| ? || '(,|$)';`, settting.Database.Schema, settting.Database.Schema)

In the end I've dropped the WHERE because we should just always prepend - we always want to be looking in our schema first.

dmayle commented 4 years ago

I've tried installing gitea 1.13.0-rc2 (from Docker Hub) and I'm still seeing problems with this. For the moment, the logs still show issues with certain queries:

2020/11/13 16:02:03 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] SELECT count(*) FROM "gitea"."repository" WHERE (owner_id=$1 OR (owner_id<>$2 AND ("repository".id IN (SELECT "access".repo_id FROM access WHERE "access".user_id=$3) OR "repository".id IN (SELECT "team_repo".repo_id FROM team_repo INNER JOIN team_user ON "team_user".team_id = "team_repo".team_id WHERE "team_user".uid=$4) OR ("repository".is_private=$5 AND "repository".owner_id IN (SELECT "org_user".org_id FROM org_user INNER JOIN "user" ON "user".id = "org_user".org_id WHERE "org_user".uid=$6 AND "user".type=$7 AND "user".visibility=$8)))) OR (is_private=$9 AND owner_id IN (SELECT "user".id FROM "user" WHERE "user".visibility=$10)) OR (is_private=$11 AND owner_id IN (SELECT "user".id FROM "user" WHERE "user".visibility=$12))) [2 2 2 2 false 2 1 private false public false limited] - 4.640044ms
2020/11/13 16:02:03 routers/home.go:155:RenderRepoSearch() [E] SearchRepository: Count: pq: relation "access" does not exist

and also:

2020/11/13 16:02:09 ...m.io/xorm/core/db.go:286:afterProcess() [I] [SQL] SELECT user_id, count(*) AS count FROM notification WHERE user_id IN (SELECT user_id FROM notification WHERE updated_unix >= $1 AND updated_unix < $2) AND status = $3 GROUP BY user_id [1605283317 1605283327 1] - 2.815717ms
2020/11/13 16:02:09 ...ource/manager_run.go:41:Run() [E] Unable to get UIDcounts: pq: relation "notification" does not exist
zeripath commented 4 years ago

Since #12634 your search path should be being set in such a way that this does not matter.

It would be useful to ensure that all of these tables are in the gitea schema and that you have not set something in such a way that setting set_config is not helpful.

It would also be helpful to tell us the version of postgres you are using and to check if SELECT set_config('search_path', ? || ',' || current_setting('search_path'), false) works on that version of postgres.

dmayle commented 4 years ago

@zeripath I was also having problems because my memcached was set to use authentication, which gitea doesn't support. After updating this, the DBAs reinitialized the gitea database and realized they made a mistake in their config. We create schemas and users with a search path predefined. Once we did that, everything started working, even with 1.12.5