Thread: Can't connect on Windows

Hi,

I have an Access DB, that's been running OK in Access, and as part of a
web app based on JDBC. When I convert it to postreSQL, all table
creation, data insertion, and primary key creation statements complete
just fine.
However, the first foreign key attempt fails:

Creating Foreign Key: CompanyContactInfo
SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT
"CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES "Company" (id) ON
DELETE CASCADE ON UPDATE CASCADE
SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT
"CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES  () ON DELETE NO
ACTION ON UPDATE NO ACTION
SQL (DBName): ROLLBACK

I'm pretty sure that Access doesn't have a foreign key to null table and
null column and would appreciate any suggestions as to why this 2nd
statement might be coming up. Might there be a bug either somewhere?

P.S. I ran this with 3 different modes, including FULL DEBUG. The
statements above are the most descriptive (Errors and SQL Queries).

P.P.S. This is run on XP Pro, and from MS site I'm seeing that MSAD 2.7
for XP, which I have, already has the Jet Engine for ODBC. I could
install their service pack for that, but it appears to be unnecessary
(and possibly undesirable).

P.P.P.S. There is a couple of dozens of foreign keys, and using the logs
I could just add the relevant statements and make the migration
half-manual. Needless to say, it would be a lot better if that wasn't
necessary.

P.P.P.P.S. I once looked up SQL statements corresponding to tables or
queries in an older version of Access, but I can't find a way to do the
same in the current one (not for table definitions anyway). That might
be of a little help. Although the best I can think if is that \r\n is
processed incorrectly by ODBC bridge or pgAdmin, and that results in
null table with null column.

Thanks in advance,
r.

Actually exactly 1 out of every 10 foreign keys couldn't be re-created.
So that means almost everything is automatic. And that is already good
enough for production. Nevertheless, I'd appreciate any feedback on the
problem below.

Thanks,
r.

-----Original Message-----
From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Reshat Sabiq
Sent: Sunday, December 22, 2002 9:00 AM
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Access-postgreSQL migration (extra invalid
foreign key added)

Hi,

I have an Access DB, that's been running OK in Access, and as part of a
web app based on JDBC. When I convert it to postreSQL, all table
creation, data insertion, and primary key creation statements complete
just fine.
However, the first foreign key attempt fails:

Creating Foreign Key: CompanyContactInfo
SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT
"CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES "Company" (id) ON
DELETE CASCADE ON UPDATE CASCADE
SQL (DBName): ALTER TABLE "ContactInfo" ADD CONSTRAINT
"CompanyContactInfo_fk" FOREIGN KEY(id) REFERENCES  () ON DELETE NO
ACTION ON UPDATE NO ACTION
SQL (DBName): ROLLBACK

I'm pretty sure that Access doesn't have a foreign key to null table and
null column and would appreciate any suggestions as to why this 2nd
statement might be coming up. Might there be a bug either somewhere?

P.S. I ran this with 3 different modes, including FULL DEBUG. The
statements above are the most descriptive (Errors and SQL Queries).

P.P.S. This is run on XP Pro, and from MS site I'm seeing that MSAD 2.7
for XP, which I have, already has the Jet Engine for ODBC. I could
install their service pack for that, but it appears to be unnecessary
(and possibly undesirable).

P.P.P.S. There is a couple of dozens of foreign keys, and using the logs
I could just add the relevant statements and make the migration
half-manual. Needless to say, it would be a lot better if that wasn't
necessary.

P.P.P.P.S. I once looked up SQL statements corresponding to tables or
queries in an older version of Access, but I can't find a way to do the
same in the current one (not for table definitions anyway). That might
be of a little help. Although the best I can think if is that \r\n is
processed incorrectly by ODBC bridge or pgAdmin, and that results in
null table with null column.

Thanks in advance,
r.



---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

What is the significance of public schema (for migration and otherwise).
Is it optional to select a schema? I believe it is for security
constraints. I'm just not used to working with SQL statements like:
public."TableName"
What schema should I have for a DB that will be on the web mostly for
queries? In the short term, I might be connecting to it remotely, but in
the long term I should have add/update/delete interface on the web
(running as me via JDBC). Should I keep it public, none at all, or make
a custom one?

Thank you,
r.

On Sat, Dec 21, 2002 at 08:51:08PM -0500, Reshat Sabiq wrote:
> I installed cygwin-based postgreSQL. I have not been able yet to start
> it as a service (net start postmaster), apparently because of
> permissions problems. I'm waiting on some suggestions, hopefully, about
> that.

Have you followed all the steps in the Cygwin README file for
Postgres, exactly?  That file is located at
/usr/doc/Cygwin/postgresql-*.README.

Note, in particular, that those steps have you run initdb a second
time to create the empty database owned by the 'postgres' user, a
necessary step for running postgresql as a service associated with
that postgres user.

--
Fred Yankowski      fred@ontosys.com           tel: +1.630.879.1312
OntoSys, Inc        PGP keyID: 7B449345        fax: +1.630.879.1370
www.ontosys.com     38W242 Deerpath Rd, Batavia, IL 60510-9461, USA

-----Original Message-----
From: Reshat Sabiq [mailto:sabiq@purdue.edu]
Sent: 22 December 2002 01:51
To: pgadmin-support@postgresql.org
Subject: [pgadmin-support] Can't connect on Windows

I installed cygwin-based postgreSQL. I have not been able yet to start it as a service (net start postmaster), apparently because of permissions problems. I’m waiting on some suggestions, hopefully, about that.

 

I can connect to the DB w/o starting a service from the cygwin command line, however, in that case I only have 1 user (myself), no postgres user is there yet. This way, I cannot connect to the DB from pgAdmin (localhost), it says “Could not connect to the server”, “Could not connect to remote socket”. 

 

The 'postgres' user is whoever ran initdb. 

 

Do I get it right that to connect on localhost with pgAdmin, I have to have postmaster running as a service? I think adding postgres user and running w/o net start postmaster will not make any difference. Is that right?

 

Yes, that's right - you don't need to run as a service. In fact, on my dev laptop I prefer it that way as I can start/stop PG in a bash windows when I like so I'm not wasting cycles when I'm doing other things.

Regards, Dave.

Note, in particular, that those steps have you run initdb a second
time to create the empty database owned by the 'postgres' user, a
necessary step for running postgresql as a service associated with
that postgres user.

Yes, I have shortcut to the readme, cause I've been using it so much
recently. However, I think there are 2 different procedures, 1 with 4
steps (simplified), and another 1 with 7 steps to install as a service.
I believe the initdb statement in the 2nd procedure is unrelated to the
one in the first.

In short, when I was trying to initdb after switching to postgres OS
user (instead su), I ended up with a semget ... Function not implemented
error. Eventually, I got it to complete installation and can now connect
to it from pgAdmin, but not as a service, just running as myself.

I am now inclined to believe that due to absence of su, the best way to
run it as a service is to make the service owned by the user installing
postgreSQL, rather than a separate postgres user (bottom of page):
http://www.pgsql.info/pg_win.html
(I don't speak German, but the screenshots are understandable).
That was on my mind, but I got it to work as myself and was too excited
to back off w/o a confirmation of this idea.

Sincerely,
r.

-----Original Message-----
From: pgadmin-support-owner@postgresql.org
[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Dave Page
Sent: Sunday, December 22, 2002 2:25 PM
To: Reshat Sabiq; pgadmin-support@postgresql.org
Subject: Re: [pgadmin-support] Can't connect on Windows

------ 
The 'postgres' user is whoever ran initdb. 
------

From the readme I thought I should make a new user account called
postgres (the way it’s usually done in Linux/UNIX). If that is correct,
then I still don’t know how to initdb under “postgres” account, because
of semget … Function not implemented error I mentioned earlier (after
switching to postgres user instead su). As I mentioned in previous
message, however, this page recommends making the user installing
postgres to be the owner of the service (bottom):
http://www.pgsql.info/pg_win.html
And if that is the preferred way of doing it on Windows, then I would
mention that in the readme. Or maybe everybody gets it and I'm not... :)
I think that will be my next try to get it running as a service.

Thank you,
r.