带有 Npgsql 的 Postgresql “关系 <tablename> 不存在”。仅在 Win 7 上发生
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12135177/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgresql with Npgsql "relation <tablename> does not exist." Only occurring on Win 7
提问by Serdalis
I'm running into a very strange bug when using Postgresql using the Npgsql (2.0.11 and 2.0.11.94) DLL in .NET 3.5.
在 .NET 3.5 中使用 Npgsql（2.0.11 和 2.0.11.94）DLL 使用 Postgresql 时，我遇到了一个非常奇怪的错误。
I've created a program that will run these two queries (these are copied directly from the programs output):
我创建了一个程序来运行这两个查询（这些是直接从程序输出中复制的）：
INSERT INTO "db_events" VALUES ('2','1','2','1', to_timestamp('2012/08/27 10:22:43', 'YYYY/MM/DD HH24:MI:SS'),'2012', '8', '27', '10', '22', '43', '35' );
INSERT INTO "db_events_counts" VALUES ('1','2', '0', '1', '0', '1' );
This program will run perfectly fine on Windows XP x86 with both postgres 8.4.12 and 9.0.9, and will enter the data into the tables as it should.
该程序将在带有 postgres 8.4.12 和 9.0.9 的 Windows XP x86 上完美运行，并将按原样将数据输入表中。
However, when running the exact same program on Windows 7 with a database that has been set up in an identical way to the Windows XP data base, I run into the error:
但是，当在 Windows 7 上运行完全相同的程序时，使用与 Windows XP 数据库相同的方式设置的数据库，我遇到了错误：
ERROR: 42P01: relation "db_events" does not exist
I have read that this error is because postgres is forcing table names to lowercase, which is fine since they are already. Or that a table created with quotes has to be referenced with quotes, which is also fine since I am using quotes.
我读过这个错误是因为 postgres 强制表名小写，这很好，因为它们已经是小写了。或者用引号创建的表必须用引号引用，这也很好，因为我使用了引号。
In the windows 7 database, if i copy and paste these two queries into pgadmin, they work fine, no errors, this leads me to believe it's something to do with the DLL?
在 windows 7 数据库中，如果我将这两个查询复制并粘贴到 pgadmin 中，它们可以正常工作，没有错误，这让我相信这与 DLL 有关系吗？
What doesn't make sense is this program working bug free on my Windows XP system while throwing this error constantly on Windows 7.
没有意义的是这个程序在我的 Windows XP 系统上运行没有错误，同时在 Windows 7 上不断抛出这个错误。
I also try a simple delete statement:
我还尝试了一个简单的删除语句：
DELETE FROM "db_events"; DELETE FROM "db_events_counts";
But that also ends in the same error.
但这也以同样的错误结束。
Is there anything I am missing? Does Npgsql need to be compiled in the same windows environment as it's run in? or is there some subtle difference between windows 7 and windows XP with postgres that I am not getting.
有什么我想念的吗？Npgsql 是否需要在与运行相同的 Windows 环境中编译？或者我没有得到带有 postgres 的 windows 7 和 windows XP 之间有一些细微的区别。
Any help or information on the topic would be greatly appreciated.
任何有关该主题的帮助或信息将不胜感激。
Due to questions about the connection, here is what i have tried:
由于有关连接的问题，这是我尝试过的：
Server=localhost;Port=5433;User Id=databaseuser;Password=databaseuser_123;Database=db123;
Server=127.0.0.1;Port=5433;User Id=databaseuser;Password=databaseuser_123;Database=db123;
Server=10.223.132.123;Port=5433;User Id=databaseuser;Password=databaseuser_123;Database=db123;
The last being the local machines IP address.
最后一个是本地机器的 IP 地址。
Here is a short log of the program connecting and disconnecting from the server on Win 7:
// connecting
这是程序在 Win 7 上与服务器连接和断开连接的简短日志：
// 连接
2012-08-27 11:26:00 EST ERROR: relation "db_events" does not exist at character 13
2012-08-27 11:26:00 EST STATEMENT: DELETE FROM "db_events"; DELETE FROM "db_events_counts";
2012-08-27 12:52:29 EST ERROR: relation "db_events" does not exist at character 13
2012-08-27 12:52:29 EST STATEMENT: INSERT INTO "db_events" VALUES ('114','1','2','1', to_timestamp('2012/08/27 12:52:29', 'YYYY/MM/DD HH24:MI:SS'),'2012', '8', '27', '12', '52', '29', '35' );
// disconnecting
// 断开连接
2012-08-27 11:26:07 EST LOG: could not receive data from client: No connection could be made because the target machine actively refused it.
2012-08-27 11:26:07 EST LOG: unexpected EOF on client connection
采纳答案by Craig Ringer
The strange and erratic behaviour seen here, and discussion in the comments, suggests that the system catalogs (in the pg_catalogschema) may've been modified directly - perhaps an attempt to REVOKEsome permissions.
此处看到的奇怪和不稳定的行为以及评论中的讨论表明系统目录（在pg_catalog架构中）可能已被直接修改 - 可能是对REVOKE某些权限的尝试。
That's not a good idea. The system catalogs should really only be modified by experts. That's one of the reasons only superuser accounts can modify them directly, and one of the many reasons you should not use superuser accounts in day-to-day operation.
这不是一个好主意。系统目录真的只能由专家修改。这是只有超级用户帐户可以直接修改它们的原因之一，也是您不应在日常操作中使用超级用户帐户的众多原因之一。
Unless you know exactly what was done and can undo it, I'd recommend reverting to a working copy of the database like the one on your known-good XP machine. GRANTing access to publicin pg_catalogsounds like it helped, but who knows what else has been done.
除非您确切地知道做了什么并且可以撤消它，否则我建议您恢复到数据库的工作副本，例如您已知良好的 XP 机器上的副本。GRANTing 访问publicinpg_catalog听起来很有帮助，但谁知道还做了什么。
If this were my DB I'd take a pg_dumpof each database and a pg_dumpall --globals-onlyand restore it to a spare DB to make sure it looked complete. I'd then stop Pg and re-initdb. That's a bit of a pain on Windows, though, so you might well be fine with just backing up the damaged database, DROPping it, re-creating it and restoring the data back into it.
如果这是我的数据库，我会取pg_dump每个数据库的一个pg_dumpall --globals-only和一个并将其恢复到备用数据库以确保它看起来完整。然后我会停止 Pg 并重新 initdb。不过，这在 Windows 上有点麻烦，因此您可能只需备份损坏的数据库、DROPping 它、重新创建它并将数据恢复到其中即可。
回答by Serdalis
Figured it out with the help of CraigRinger.
在 CraigRinger 的帮助下解决了这个问题。
Even though the user I was logging in as was the owner of the database, he did not have permission to look into anything under the public schema.
即使我登录的用户是数据库的所有者，他也无权查看公共架构下的任何内容。
This was discovered using:select * from public.db_events
这是使用以下方法发现的：select * from public.db_events
which, instead of throwing a relation not founderror, threw an access is deniederror.
这不是抛出relation not found错误，而是抛出access is denied错误。
After changing the user I was logging in as to a superuserand ticking all the check boxes under "role privileges", the relation not founderror didn't occur any more.
更改我以 a 登录的用户superuser并勾选“角色权限”下的所有复选框后，relation not found错误不再发生。
回答by Abdullah Abualkher
PostgreSQL folds all identifiers to lower-case. This is PostgreSQL behavior, and has nothing to do with Npgsql - the latter simply passes along your SQL as you wrote it. You can switch to all-lowercase table names, in which case you no longer need the quotes.
PostgreSQL 将所有标识符折叠为小写。这是 PostgreSQL 的行为，与 Npgsql 无关 - 后者只是在您编写 SQL 时传递您的 SQL。您可以切换到全小写的表名，在这种情况下，您不再需要引号。
