[ADMIN] Dropping Foreign Key without recreating table
I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I didn't specify a constraint name for the foreign key. \d shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to alter table mytable drop constraint RI_ConstraintTrigger_17195 restrict I'm getting the error constraint does not exist. What am I doing wrong here? Or is there another simple solution to switch foreign keys to be deferrable? Regards Egon ---(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] Dropping Foreign Key without recreating table
since name of constrauint is in mixed cased u must double quote it in command. below will work. psql alter table mytable drop constraint RI_ConstraintTrigger_17195 restrict ; I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I didn't specify a constraint name for the foreign key. \d shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to alter table mytable drop constraint RI_ConstraintTrigger_17195 restrict I'm getting the error constraint does not exist. What am I doing wrong here? Or is there another simple solution to switch foreign keys to be deferrable? Regards Egon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) - Get your free web based email at trade-india.com. India's Leading B2B eMarketplace.! http://www.trade-india.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [ADMIN] Dropping Foreign Key without recreating table
Oops disregard my prev reply, RI_ConstraintTrigger_17195 is a trigger not contraint so u must in 7.2.1 do DROP TRIGGER RI_ConstraintTrigger_17195 on mytable ; in 7.3 foreign key constraints on tables have name. so you need not drop underlying triggers like in 721 but can use command to drop the contraint which u are attempting now like alter table mytable drop constraint NAME OF THE FKEY CONSTRAINT restrict ; cheers mallah. I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I didn't specify a constraint name for the foreign key. \d shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to alter table mytable drop constraint RI_ConstraintTrigger_17195 restrict I'm getting the error constraint does not exist. What am I doing wrong here? Or is there another simple solution to switch foreign keys to be deferrable? Regards Egon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) - Get your free web based email at trade-india.com. India's Leading B2B eMarketplace.! http://www.trade-india.com/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] Dropping Foreign Key without recreating table
Thanks Mallah, I didn't realize the name of the trigger is in mixed case. However, I had to drop another 2 triggers on the referenced table. So it looks for me, a foreign key uses 3 triggers at all. Looking into pg_trigger, I found them all. Thanks Egon [EMAIL PROTECTED] wrote: Oops disregard my prev reply, RI_ConstraintTrigger_17195 is a trigger not contraint so u must in 7.2.1 do DROP TRIGGER RI_ConstraintTrigger_17195 on mytable ; in 7.3 foreign key constraints on tables have name. so you need not drop underlying triggers like in 721 but can use command to drop the contraint which u are attempting now like alter table mytable drop constraint NAME OF THE FKEY CONSTRAINT restrict ; cheers mallah. I wanted to change a foreign key to be deferrable (db version 7.2.1). During table creation I didn't specify a constraint name for the foreign key. \d shows a trigger RI_ConstraintTrigger_17195, however when I'm trying to alter table mytable drop constraint RI_ConstraintTrigger_17195 restrict I'm getting the error constraint does not exist. What am I doing wrong here? Or is there another simple solution to switch foreign keys to be deferrable? Regards Egon ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) - Get your free web based email at trade-india.com. India's Leading B2B eMarketplace.! http://www.trade-india.com/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Problems invoking psql, was: Re: [ADMIN] Troubles at Startup
Mr. Elphick: OK. Here is what I got. hesco@biko:~$ ls -al /usr/bin/psql lrwxrwxrwx1 root root 10 Oct 10 16:24 /usr/bin/psql - pg_wrapper hesco@biko:~$ ls -al /usr/bin/pg_w* -rwxr-xr-x1 root root 6584 Sep 11 04:30 /usr/bin/pg_wrapper hesco@biko:~$ ls -al /usr/lib/postgresql/bin/psql -rwxr-xr-x1 root root 6584 Nov 21 20:57 /usr/lib/postgresql/bin/psql So it appears that instead of copying the symbolic link, it copied instead pg_wrapper, renaming it in the new directory as psql. hesco@biko:~$ /usr/lib/postgresql/bin/psql -U hesco template1 hangs, and spins the hard drive interminably. So I open a second shell and do: hesco@biko:~$ su postgres Password: postgres@biko:/home/hesco$ /usr/lib/postgresql/bin/psql No database specified postgres@biko:/home/hesco$ /usr/lib/postgresql/bin/psql template1 and that invocation also hangs, and spins the hard drive interminably. then running top in a third shell shows me: 512 hesco 10 0 320 320 268 S 2.1 0.5 0:03 psql 1978 postgres 10 0 320 320 268 S 2.1 0.5 0:00 psql when I Control-C each of those first two shells, the hard drive stops spinning. Then I do the following: postgres@biko:/home/hesco$ su Password: biko:/home/hesco# apt-get remove postgresql-client Reading Package Lists... Done Building Dependency Tree... Done The following packages will be REMOVED: postgresql postgresql-client 0 packages upgraded, 0 newly installed, 2 to remove and 0 not upgraded. Need to get 0B of archives. After unpacking 4530kB will be freed. Do you want to continue? [Y/n] y dpkg: conflicting diversions involving `/Package: mysql-common' or `/Status: ins tall ok installed' E: Sub-process /usr/bin/dpkg returned an error code (2) biko:/home/hesco# apt-get install postgresql-client Reading Package Lists... Done Building Dependency Tree... Done Sorry, postgresql-client is already the newest version. 0 packages upgraded, 0 newly installed, 0 to remove and 0 not upgraded. biko:/home/hesco# I do not know what to make of these error messages: dpkg: conflicting diversions involving `/Package: mysql-common' or `/Status: ins tall ok installed' E: Sub-process /usr/bin/dpkg returned an error code (2) -- Hugh Esco At 06:37 PM 11/23/02 +, you wrote: On Sat, 2002-11-23 at 15:13, Hugh Esco wrote: Problem solved! When using pg_ctl to make the postmaster see a pg_hba.conf file that had been edited while the database server was running, I got a message that it looked for, but did not find, the psql script in the /usr/lib/postgresql/bin directory. So I copied this file from /usr/bin (where the apt-get install postgresql had installed it) to /usr/lib/postgresql/bin. And while I am still having trouble invoking a psql command line prompt, I am now getting postmaster starting up automatically at boot up, exactly as /etc/rc#.d says it should. I'm not surprised you're having trouble getting a command line, since you appear to have overwritten the real psql in /usr/lib/postgresql/bin/psql with the symbolic link /usr/bin/psql, which is a link to /usr/bin/pg_wrapper, which tries to call /usr/lib/postgresql/bin/psql. I also don't see how this can make any difference to how postmaster starts up, since postmaster does not use psql at all. Is there some appropriate place I should report this problem to the folks who build and maintain the Debian package for postgresql, so this matter can be handled in future distributions? Debian bug reports in general go to bugs.debian.org. The proper way to report them is to use reportbug (install the reportbug package). Debian PostgreSQL bug reports will come to me, so you needn't bother in this particular case. You never did answer the question whether you could run /usr/lib/postgresql/bin/psql directly. psql is part of the postgresql-client package, not postgresql. It seems to me you should reinstall postgresql-client, and then we can take another look at your situation. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C The LORD is nigh unto all them that call upon him, to all that call upon him in truth. Psalms 145:18 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: Problems invoking psql, was: Re: [ADMIN] Troubles at Startup
On Sun, 2002-11-24 at 16:18, Hugh Esco wrote: So it appears that instead of copying the symbolic link, it copied instead pg_wrapper, renaming it in the new directory as psql. hesco@biko:~$ /usr/lib/postgresql/bin/psql -U hesco template1 hangs, and spins the hard drive interminably. So I open a second shell and do: hesco@biko:~$ su postgres Password: postgres@biko:/home/hesco$ /usr/lib/postgresql/bin/psql No database specified postgres@biko:/home/hesco$ /usr/lib/postgresql/bin/psql template1 and that invocation also hangs, and spins the hard drive interminably. Presumably it will try to call itself recursively. Since it does an exec, each successive process will replace the one before. ... biko:/home/hesco# apt-get remove postgresql-client Reading Package Lists... Done Building Dependency Tree... Done The following packages will be REMOVED: postgresql postgresql-client 0 packages upgraded, 0 newly installed, 2 to remove and 0 not upgraded. Need to get 0B of archives. After unpacking 4530kB will be freed. Do you want to continue? [Y/n] y dpkg: conflicting diversions involving `/Package: mysql-common' or `/Status: ins tall ok installed' E: Sub-process /usr/bin/dpkg returned an error code (2) I don't know what that's about; you probably have some package installed that depends on either mysql or postgresql in some way, but I don't know of any file diversions involving postgresql packages. What have you got in /var/lib/dpkg/diversions? biko:/home/hesco# apt-get install postgresql-client Reading Package Lists... Done Building Dependency Tree... Done Sorry, postgresql-client is already the newest version. 0 packages upgraded, 0 newly installed, 0 to remove and 0 not upgraded. You need to use the --reinstall option if the package is up to date: apt-get --reinstall install postgresql-client Then check the contents of /usr/lib/postgresql/bin. You should have the following real executable files, which are installed by postgresql-client: /usr/lib/postgresql/bin/readpgenv /usr/lib/postgresql/bin/psql /usr/lib/postgresql/bin/createuser /usr/lib/postgresql/bin/createdb /usr/lib/postgresql/bin/dropuser /usr/lib/postgresql/bin/dropdb /usr/lib/postgresql/bin/pg_dump /usr/lib/postgresql/bin/pg_restore You should be able to do the following as user postgres: $ /usr/bin/psql -d template1 or $ /usr/lib/postgresql/bin/psql -d template1 and get a connection in both cases. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Jesus saith unto him, I am the way, the truth, and the life; no man cometh unto the Father, but by me. John 14:6 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Weird processes
At 10:47 14.11.2002 -0500, A.M. wrote the following message: What you're looking for is SQLRelay. one can not use sql relay as fake postgresql server? Thanks in advance. Tomaz ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] H/W RAID 5 on slower disks versus no raid on faster HDDs
Mallah, I agree with Chris. The fastest is to have an in memory database. Raid 0 (striping) will speed up both reading and writing since you have more available disk I/O bandwidth. SCSI320 in theory is twice as fast as SCSI160. But the bottleneck will be the throughput of the individual disks. 15,000 rpm of course will be faster than 10,000 rpm. More interesting then the rpm numbers itself are seek time and transfer rate. In a production environment I would always favor some kind of error protection. Either RAID 5 or RAID 1 (mirroring). A hardware RAID controller is faster than software RAID. For pure speed raw devices would be faster then file systems. However, PostgeSQL currently does not support them. Given your 2 choices b) should be faster. a) 3*18 GB 10,000 RPM Ultra160 Dual Channel SCSI controller + H/W Raid 5 b) 2*36 GB 15,000 RPM Ultra320 Dual Channel SCSI and no RAID Option b) can be improved by using striping. Save your money on the SCSI Ultra320 since the disk cant transfer enough data anyway and buy more memory instead. And finally good application design has a greater impact on performance than a little faster hardware. Regards, Nikolaus Dilger On Thu, 21 Nov 2002, Chris Ruprecht wrote: raid 0 (striping) spreads the load over multiple spindels, the same way raid 5 does. but raid 5 always needs to calculate parity and write that to it's parity drive. RPM isn't that critical, a lot depends on the machine, the processor and the memory (and the spped with which the processor can get to the memory). I have recently tested a lot of systems with some database benchmarks we wrote here at work. We're not running Postgres here at work, sorry, these benchmarks are of no use to Postgres ... We we found is that a lot depends on motherboard design, not so much on drive speed. We got to stages where we allocated 1.8 GB of RAM to shared memory for the database server process, resulting in the entire database being sucked into memory. When doing reads, 100% of the data is coming out the that menory, and drive speed becomes irrelevant. From tests I did with Postgres on my boxes at home, I can say: The more shared memory you can throw at the server process, the better. Under MacOS X I wasn't able to allocate more than 3 MB, Under Linux, I can allocate anything I want to, so I usually start up the server with 256 MB. The difference? A process which takes 4 minutes under Linux, takes 6 hours under MacOS - same hardware, same drives, different memory settings. Best regards, Chris On Thursday 21 November 2002 12:02, you wrote: Thanks Chris, does raid0 enhances both read/write both? does rpms not matter that much? regds mallah. On Thursday 21 November 2002 22:27, you wrote: RAID 5 gives you pretty bad performance, a slowdown of about 50%. For pure performance, I'd use the 3 18 GB drives with RAID 0. If you need fault tolerance, you could use RAID 0+1 or 1+0 but you'd need an even number of drives for that, of which half would become 'usable space'. Best regards, Chris On Thursday 21 November 2002 11:45, you wrote: Hi folks, I have two options: 3*18 GB 10,000 RPM Ultra160 Dual Channel SCSI controller + H/W Raid 5 and 2*36 GB 15,000 RPM Ultra320 Dual Channel SCSI and no RAID Does anyone opinions *performance wise* the pros and cons of above two options. please take in consideration in latter case its higher RPM and better SCSI interface. Regds Mallah. -- Network Grunt and Bit Pusher extraordinaire ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? a href=http://mail.dilger.cc/jump/http://www.postgresql.org/users-lounge/docs/faq.html;http://www.postgresql.org/users-lounge/docs/faq.html/a ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: Problems invoking psql, was: Re: [ADMIN] Troubles at
Oliver Elphick asked: What have you got in /var/lib/dpkg/diversions? Here is my preliminary answer, plus a question of my own: biko:/var/lib/dpkg# ls -al | grep diversions -rw-r--r--1 root root 361825 Nov 21 18:48 diversions -rw-r--r--1 root root 361931 Nov 21 18:48 diversions-old biko:/var/lib/dpkg# wc diversions 8841 43017 361825 diversions biko:/var/lib/dpkg# cat diversions | grep Package: packages.txt biko:/var/lib/dpkg# ls -al | grep packages.txt -rw-r--r--1 root root 8147 Nov 23 19:44 packages.txt biko:/var/lib/dpkg# wc packages.txt 430 8608147 packages.txt biko:/var/lib/dpkg# Do you really want to see the whole list? Or is there perhaps something in particular you are looking for? Here is the dialogue for my reinstall attempt. Man, these error messages are starting to look familiar. biko:/var/lib/dpkg# apt-get --reinstall install postgresql-client Reading Package Lists... Done Building Dependency Tree... Done 0 packages upgraded, 0 newly installed, 1 reinstalled, 0 to remove and 0 not up graded. Need to get 0B/280kB of archives. After unpacking 0B will be used. Do you want to continue? [Y/n] y dpkg: conflicting diversions involving `/Package: mysql-common' or `/Status: ins tall ok installed' E: Sub-process /usr/bin/dpkg returned an error code (2) biko:/var/lib/dpkg# With more interminable hard disk spinning as my only response to an attempt to once again invoke psql. -- Hugh Esco At 10:41 PM 11/24/02 +, you wrote: On Sun, 2002-11-24 at 16:18, Hugh Esco wrote: So it appears that instead of copying the symbolic link, it copied instead pg_wrapper, renaming it in the new directory as psql. hesco@biko:~$ /usr/lib/postgresql/bin/psql -U hesco template1 hangs, and spins the hard drive interminably. So I open a second shell and do: hesco@biko:~$ su postgres Password: postgres@biko:/home/hesco$ /usr/lib/postgresql/bin/psql No database specified postgres@biko:/home/hesco$ /usr/lib/postgresql/bin/psql template1 and that invocation also hangs, and spins the hard drive interminably. Presumably it will try to call itself recursively. Since it does an exec, each successive process will replace the one before. ... biko:/home/hesco# apt-get remove postgresql-client Reading Package Lists... Done Building Dependency Tree... Done The following packages will be REMOVED: postgresql postgresql-client 0 packages upgraded, 0 newly installed, 2 to remove and 0 not upgraded. Need to get 0B of archives. After unpacking 4530kB will be freed. Do you want to continue? [Y/n] y dpkg: conflicting diversions involving `/Package: mysql-common' or `/Status: ins tall ok installed' E: Sub-process /usr/bin/dpkg returned an error code (2) I don't know what that's about; you probably have some package installed that depends on either mysql or postgresql in some way, but I don't know of any file diversions involving postgresql packages. What have you got in /var/lib/dpkg/diversions? biko:/home/hesco# apt-get install postgresql-client Reading Package Lists... Done Building Dependency Tree... Done Sorry, postgresql-client is already the newest version. 0 packages upgraded, 0 newly installed, 0 to remove and 0 not upgraded. You need to use the --reinstall option if the package is up to date: apt-get --reinstall install postgresql-client Then check the contents of /usr/lib/postgresql/bin. You should have the following real executable files, which are installed by postgresql-client: /usr/lib/postgresql/bin/readpgenv /usr/lib/postgresql/bin/psql /usr/lib/postgresql/bin/createuser /usr/lib/postgresql/bin/createdb /usr/lib/postgresql/bin/dropuser /usr/lib/postgresql/bin/dropdb /usr/lib/postgresql/bin/pg_dump /usr/lib/postgresql/bin/pg_restore You should be able to do the following as user postgres: $ /usr/bin/psql -d template1 or $ /usr/lib/postgresql/bin/psql -d template1 and get a connection in both cases. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight, UK http://www.lfix.co.uk/oliver GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C Jesus saith unto him, I am the way, the truth, and the life; no man cometh unto the Father, but by me. John 14:6 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
