Postgres schema support #12505
Comments
|
I think we have support schema, you should set it in [database]
SCHEMA= |
|
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 <[email protected]>
Sent: Monday, August 17, 2020 8:16 AM
To: go-gitea/gitea <[email protected]>
Cc: osbeker <[email protected]>; Author <[email protected]>
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 GitHub<#12505 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/ABRR4Z7W2RSYWPQSGMNQR2LSBENRBANCNFSM4QBQ662Q>.
|
|
I wonder if we simply need to do |
|
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. |
Rather than rely on the user running the gitea server and db setting the schema search_path correctly - if gitea is run with a schema we should simply set the search_path to have that schema first in the path. Fix go-gitea#12505 Signed-off-by: Andrew Thornton <[email protected]>
Rather than rely on the user running the gitea server and db setting the schema search_path correctly - if gitea is run with a schema we should simply set the search_path to have that schema first in the path. Fix #12505 Signed-off-by: Andrew Thornton <[email protected]>
|
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: and also: |
|
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 |
|
@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 |
[x]):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
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
The text was updated successfully, but these errors were encountered: