9

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.

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.

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:

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.

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?

What doesn't make sense is this program working bug free on my Windows XP system while throwing this error constantly on 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.

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.

Here is a short log of the program connecting and disconnecting from the server on Win 7:
// connecting

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
Improve this question
11
  • 1
    Are you absolutely, 100% certain it's really connecting to the correct database? This is certainly a local configuration issue of some sort; Pg really doesn't care what the platform is. Commented Aug 27, 2012 at 0:56
  • Its defiantly connecting to the right database, there is only one database on the whole machine and there are no connection errors, both environments are identical appart from the windows version (SQL scripts were used to set up both). However, if you can think of a reason it wouldn't be connecting to the right database I'd be glad to try and fix it. Commented Aug 27, 2012 at 0:58
  • If it were a libpq dll compatibility issue you'd likely be seeing crashes or truly wacky behaviour, not getting valid error messages from the database. If it were Npgsql, I'd expect to see exceptions that didn't have a server-side SQLSTATE in them. Commented Aug 27, 2012 at 0:59
  • Sure it's not connecting to a db on another machine? Commented Aug 27, 2012 at 0:59
  • Try adding some code to your program that runs the SQL statement: SELECT relname FROM pg_class WHERE relkind = 'r' AND relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public'); . Print the results. Do you see the table you expect? Also try SELECT current_database(); . The schema info functions may be useful: postgresql.org/docs/9.1/static/functions-info.html . It can also be handy to turn server-side query logging on with log_statement='all' in postgresql.conf and reload/restart Pg, then examine the server logs. Commented Aug 27, 2012 at 1:03

4 Answers 4

Reset to default
6

The strange and erratic behaviour seen here, and discussion in the comments, suggests that the system catalogs (in the pg_catalog schema) may've been modified directly - perhaps an attempt to REVOKE some permissions.

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 public in pg_catalog sounds like it helped, but who knows what else has been done.

If this were my DB I'd take a pg_dump of each database and a pg_dumpall --globals-only and 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.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

Figured it out with the help of 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

which, instead of throwing a relation not found error, threw an access is denied error.

After changing the user I was logging in as to a superuser and ticking all the check boxes under "role privileges", the relation not found error didn't occur any more.

Improve this answer

3 Comments

Regular app users really shouldn't be superusers, and certainly shouldn't need to be. This is very weird. Furthermore, why permission denied when npgsql was reporting does not exist is odd. There's more to this than we've seen so far. I'm wondering if somebody's been mucking with the catalog directly? Personally I'd drop the whole cluster and re-initdb it.
@CraigRinger I'm the only one who's got access to this machine, but following your advice I de-superusered the account and using the "Grant Wizard" gave all permissions to public for all objects. This allows my user to access everything, even without superuser. I'm beginning to think all this is just due to in-experiance unfortunatly, I'll have to do quite a bit of research on this.
Yeah, maybe if someone REVOKEd a bunch of permissions in pg_catalog you could see behaviour like this. Drop your cluster and re-initdb if you can easily restore it from a template (say, like a dump taken from the working XP machine). Who knows what's broken.
1

Make sure Table exists , if using some migration to create table then confirm it is being created before you do any operation on the table! Good luck

Improve this answer

1 Comment

This question was answered a long time ago and this answer doesn't add anything, were you trying to answer a different question?
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.