Re: [ADMIN] duplicate templates, wrong users
> Here's your problem: user1 has the same userid as pgsql. this > used to happen in 7.0, I think. Is that what you're running? I'm sure it's what I used to be running. I am running this now: postgresql-7.1.2_2 according to FreeBSD ports. > As to fixing it - have you created a lot of db objects (tables, views, > functions, etc.) as user1 that need to be kept, or is this a test db? There is data in a variety of tables belonging to user1 in the db1 database that CANNOT be lost. I have tried using pg_dump and pg_dumpall with little to no success: yanek:~> pg_dump db1 > dump getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than one tuple returned by a subselect used as an expression.'. > I _think_ you can get away with connecting to the db as a superuser, > and updating the pg_shadow table so that user1 has a unique > id. This means that everything user1 used to own will be owned by > pgsql. Now you can drop and recreate the db that user1 is supposed to > own. Anyone can give confirmation on this procedure? I really cannot afford to lose this data, though I have done an offline tar -zcvf and will again before I do any dropping. So... just update the 'usesysid' in pg_shadow (what about pg_user?), drop the database (which is now only owned by pgsql?), and re-create that DB as user1? Thanks for the info so far. -Yanek. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] adding primary keys
how come you can not alter a table and add a primary key after the table has been created? (or at least i can't seem to) mydb=> alter table actions add primary key (action); ERROR: ALTER TABLE / ADD CONSTRAINT is not implemented for that constraint type. any suggestions? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[ADMIN] Vacuum Fails
Here's the error message: --- pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. connection to server was lost vacuumdb: vacuum failed Does any one know what is causing this problem? What is the best solution? Version postgresql-7.0.3-8 Andy G. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] postgres on WinNT and IIS
At 10:43 PM 12/2/2001 , [EMAIL PROTECTED] wrote: >Can anyone advise me on running postgres and php on a WinNT box with IIS? I believe there are Windows versions of both PostgreSQL and PHP. > I found cygwin, does it work and is that the only solution? Cygwin =does= work. Quite well, too. -crl -- Chad R. Larson (CRL22)[EMAIL PROTECTED] Eldorado Computing, Inc. 602-604-3100 5353 North 16th Street, Suite 400 Phoenix, Arizona 85016-3228 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] psql option for writing schema
On 30 Nov 2001 10:52:26 -0800, Tony Reina <[EMAIL PROTECTED]> wrote: > I seem to remember a psql option that dumped out the table schema. I > can't seem to find it in the man for psql. Could someone point out the > option to me? > In psql \? is your friend. Probably what you want is \d tableName ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] Making user names NOT case sensitive
Hello. Is there a way to configure postgres so that the connection doesn't check the username as case sensitive. It's kind of anoying for window users. Ligia ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [ADMIN] setting up ODBC
[EMAIL PROTECTED] (Jodi Kanter) writes: > This is a multi-part message in MIME format. > > --=_NextPart_000_00D1_01C17CBA.DEDC5EF0 > Content-Type: text/plain; charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > I am trying to connect to postgres via ODBC so that I can use Visio. I have= > installed psqlODBC on my windows 2000 workstation and am trying to get to = > our postgres database located on a Linux machine. > Do I need to enable ODBC on the Linux side? or just configure the driver on= > my workstation? > I have read something about postgres automatically rejecting IP connections. > Can anyone advise?=20 > Thanks=20 > Jodi=20 > ___ > Jodi L Kanter > BioInformatics Database Administrator > University of Virginia > (804) 924-2846 > [EMAIL PROTECTED] > > --=_NextPart_000_00D1_01C17CBA.DEDC5EF0 > Content-Type: text/html; charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > > > > > > > > I am trying to connect to postgres via ODB= > C so that=20 > I can use Visio. I have installed psqlODBC on my windows 2000 workstation a= > nd am=20 > trying to get to our postgres database located on a Linux machine. DIV> > Do I need to enable ODBC on the Linux side= > ? or just=20 > configure the driver on my workstation? > I have read something about postgres autom= > atically=20 > rejecting IP connections. > Can anyone advise? > Thanks > Jodi > > > style=3D"FONT-FAMILY: Arial; FONT-SIZE: 9pt; mso-bidi-font-size: 12.0pt; ms= > o-fareast-font-family: 'MS Mincho'">___Jodi= > =20 > L KanterBioInformatics Database AdministratorUniversity of=20 > Virginia(804) 924-2846 href=3D"mailto:[EMAIL PROTECTED]";>[EMAIL PROTECTED] style=3D"mso-special-character: line-break"> style=3D"mso-special-character: line-break"> =3D=20 > "urn:schemas-microsoft-com:office:office"=20 > /> > > --=_NextPart_000_00D1_01C17CBA.DEDC5EF0-- Jodi, you may not have been aware of this to date, but newsgroups much appreciate receiving messages in plain text format. Regards, Lucien -- If you receive this by error, please delete it and inform the sender. http://www.consult-meyers.com recommends e-mail encryption using pgp. To Big Brother Echelon from "spook": security jihad plutonium CIA Albanian Rule Psix supercomputer ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[ADMIN] Time zone setting
Hi! I am running postgreSQL 7.1.3 on a HP-UX 10.20 box. I have a problem telling postgres which time zone to use. I want to use WETDST, but no matter what I do, postgres is off with 5 hours. I have tried to set the time zone in the script that starts postgres TZ=WETDST export TZ I also came across the PGTZ variable in one of the docs, and tried that one, but to no help. By using the SET TIME ZONE command, I can manage to set the correct time zone, but that is too much of a hazzle to use for each session. Probably I have overlooked something, but what? Btw, I have compiled it without locale support. Is that the problem? As far as I could see, locale support has mostly to do with character encoding. -- Morten Sickel Norwegian Radiation Protection Authority ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[ADMIN] duplicate templates, wrong users
Well, a while ago I set up postgresql and have been using it for a small database ever since. I'm now looking at local authentication and permissions and a number of questions have come up. When I run select * from pg_database, I get this: datname | datdba | encoding | datistemplate | datallowconn | datlastsysoid | datpath --++--+---+--+-- -+- db1 | 72 |0 | f | t| 18539 | template1| 72 |0 | t | t| 18539 | template0| 72 |0 | t | f| 18539 | db2 | 73 |0 | f | t| 18539 | db3 | 75 |0 | f | t| 18539 | (I've replaced the names of the databases with db1, db2, and db3). here's pg_user: usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd | passwd | valuntil +--+-+--+--+---+ --+-- pgsql | 72 | t | t| t| t | | os_user| 71 | t | f| t| t | | user3 | 75 | f | f| f| f | | user1 | 72 | f | f| f| f | | user2 | 73 | f | f| f| f | | where user1 should correspond to db1 etc. When I list my databases, it looks like this: List of databases Database | Owner --+ db2 | user2 db1 | user1 db1 | pgsql template0| pgsql template0| user1 template1| user1 template1| pgsql db3 | user3 What's with the duplicate databases? Why does user1 own TEMPLATES? Clearly I have screwed something up. What's the proper way to fix it? As I see it, I should be able to create a user, create a database with that user... change that user to not be able to create databases, and then lock down the databases so other users cannot get in. This is possible, yes? I cannot find good documentation on this anywhere. Any help would be GREATLY appreciated. -Yanek. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[ADMIN] ODBC queries die; "Unexpected protocol character from backend"
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello: I have a PostgreSQL 7.1.3 system which is accessed from an Apache CGI handler via ODBC (driven by libiodbc-3.0.5). It has worked very well, but on two occasions queries just stop working. The Apache error log shows: Unknown error; Unexpected protocol character from backend (send_query), SQLSTATE=S1000 Unexpected protocol character from backend (send_query), SQLSTATE=S1000 and not much else. If I stop and restart the Apache handler, the problem is resolved -- until the next time. I see nothing in the PostgreSQL logs on the database server which would describe a possible cause. I'm not sure if it is related (it probably is), but the PostgreSQL logs contain many lines of the form: pq_flush: send() failed: Broken pipe Prior to these lines were occasional lines: pq_recvbuf: unexpected EOF on client connection but I would assume the user just hit stop on their client browser rather than waiting for the query to finish. Any advice would be appreciated. - -< codingstyle.com: What's your style? > Steve Frampton <[EMAIL PROTECTED]> http://www.codingstyle.com GNU Privacy Guard ID: 99F18E3A (see http://www.gnupg.org/ for details) GNU-PG Fingerprint: 244D FA2F 5EAB 6F52 41B4 8FB4 D4F4 0738 99F1 8E3A -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.4 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8Cwer1PQHOJnxjjoRAq/hAJ0XmYVk9GgYuAlNJimRMEDmlFi3SgCfSLhq SESw7ymO6zoY/muv70buu/A= =KGtZ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] adding primary keys
On 3 Dec 2001, jason wrote: > how come you can not alter a table and add a primary key after the > table has been created? Because it wasn't implemented for that constraint type ;) (I think it'll work in at least some cases for 7.2)? > any suggestions? Check the archives for 7.1 and earlier workarounds, it's come up fairly recently. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] duplicate templates, wrong users
On Wed, 5 Dec 2001, Yanek Korff wrote: > here's pg_user: > usename | usesysid | usecreatedb | usetrace | usesuper | usecatupd | > passwd | valuntil > +--+-+--+--+---+ > --+-- > pgsql | 72 | t | t| t| t | > | > os_user| 71 | t | f| t| t | > | > user3 | 75 | f | f| f| f | > | > user1 | 72 | f | f| f| f | > | > user2 | 73 | f | f| f| f | > | > > where user1 should correspond to db1 etc. > > When I list my databases, it looks like this: >List of databases > Database | Owner > --+ > db2 | user2 > db1 | user1 > db1 | pgsql > template0| pgsql > template0| user1 > template1| user1 > template1| pgsql > db3 | user3 > > > What's with the duplicate databases? Why does user1 own TEMPLATES? Clearly > I have screwed something up. What's the proper way to fix it? As I see it, > I should be able to create a user, create a database with that user... > change that user to not be able to create databases, and then lock down the > databases so other users cannot get in. This is possible, yes? I cannot > find good documentation on this anywhere. Any help would be GREATLY > appreciated. You have two users with the same usesysid and it happens to be pgsql and user1 that conflict. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [ADMIN] duplicate templates, wrong users
On Thu, Dec 06, 2001 at 01:32:56PM -0500, Yanek Korff wrote: > > Here's your problem: user1 has the same userid as pgsql. this > > used to happen in 7.0, I think. Is that what you're running? > I'm sure it's what I used to be running. I am running this now: > postgresql-7.1.2_2 > according to FreeBSD ports. > > > As to fixing it - have you created a lot of db objects (tables, views, > > functions, etc.) as user1 that need to be kept, or is this a test db? > There is data in a variety of tables belonging to user1 in the db1 database > that CANNOT be lost. I have tried using pg_dump and pg_dumpall with little > to no success: > yanek:~> pg_dump db1 > dump > getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than > one tuple returned by a subselect used as an expression.'. > > > I _think_ you can get away with connecting to the db as a superuser, > > and updating the pg_shadow table so that user1 has a unique > > id. This means that everything user1 used to own will be owned by > > pgsql. Now you can drop and recreate the db that user1 is supposed to > > own. > Anyone can give confirmation on this procedure? I really cannot afford to > lose this data, though I have done an offline tar -zcvf and will again > before I do any dropping. So... just update the 'usesysid' in pg_shadow > (what about pg_user?), drop the database (which is now only owned by > pgsql?), and re-create that DB as user1? No, it'll remove the duplicate id: there's really only one of each database, it just looks like two when you use psql -l because psql does a join to the pg_user table to get the owner name. Here: reedstrm@wallace:~$ psql -E -l * QUERY * SELECT pg_database.datname as "Database", pg_user.usename as "Owner", pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database, pg_user WHERE pg_database.datdba = pg_user.usesysid UNION SELECT pg_database.datname as "Database", NULL as "Owner", pg_encoding_to_char(pg_database.encoding) as "Encoding"FROM pg_database WHERE pg_database.datdba NOT IN (SELECT usesysid FROM pg_user) ORDER BY "Database" * List of databases Database | Owner | Encoding +--+--- bioinfo| reedstrm | SQL_ASCII ifs_test | reedstrm | SQL_ASCII nsf| reedstrm | SQL_ASCII reedstrm | reedstrm | SQL_ASCII repository | reedstrm | SQL_ASCII template0 | postgres | SQL_ASCII template1 | postgres | SQL_ASCII test | reedstrm | SQL_ASCII (8 rows) So, the first part there will return two rows for each database owned by '72' (in your case), one for each user with id = 72. You can verify this by doing a select * from pg_database; You'll see one of each database. By breaking that duplication, you should then be able to pg_dump the database. You'll then have to go through the schema by hand, and find everything that's supposed to be owned by user1 (which will dump as being owned by pgsql) and fix it. There's no automatic way to do this, since your system beleives that 'user1' and 'pgsql' are both '72' and that's how the items are actually tracked: by usesysid. I just tested this by artificially creating a user with duplicate usesysid (I had to UPDATE pg_shadow to do it : the CREATE USER command is guarded against this) and got exactly the symptoms you report: reedstrm@wallace:~$ pg_dump ifs_test >ifs_test.sql reedstrm@wallace:~$ psql template1 -c "update pg_shadow set usesysid=32 where usename='user1';" UPDATE 1 reedstrm@wallace:~$ pg_dump ifs_test > ifs_test.sql getDatabase(): SELECT failed. Explanation from backend: 'ERROR: More than one tuple returned by a subselect used as an expression. '. reedstrm@wallace:~$ psql template1 -c "update pg_shadow set usesysid=33 where usename='user1';" UPDATE 1 reedstrm@wallace:~$ pg_dump ifs_test > ifs_test.sql reedstrm@wallace:~$ Ross -- Ross Reedstrom, Ph.D. [EMAIL PROTECTED] Executive Director phone: 713-348-6166 Gulf Coast Consortium for Bioinformatics fax: 713-348-6182 Rice University MS-39 Houston, TX 77005 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
