Re: [GENERAL] Trigger isn't fired
Hi Stephan, it was the problem, but not realy understand the reason. The documentetion sais that perform doesn't porvide the return value and I thought I do not need that. But I read absently, and didn't realized that the query to perform must be a SELECT. Thank you for opening my eyes. :) Now the trigger does its task. Thank you very much. Best regards, -- Csaba Együd > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Stephan Szabo > Sent: 2004. június 26. 22:32 > To: Együd Csaba > Cc: [EMAIL PROTECTED] (E-mail) > Subject: Re: [GENERAL] Trigger isn't fired > > > On Sat, 26 Jun 2004, [iso-8859-2] Együd Csaba wrote: > > > Hi, > > I created a trigger function and a trigger with that > function on a table. It > > is an "after insert or update or delete" trigger, in which > I want to modify > > a second table regardless of the current modifications. > > > > Everything seems to be good on modifying the table, no > error messages. After > > the modifications the second table isn't modified at all. > Should anybody > > suggest me what I'm doing wrong? Does a trigger functon > send error messages > > if it fails? > > > > My statements: > > -- > > CREATE OR REPLACE FUNCTION trg_upd_prod_instock() RETURNS > trigger AS ' > > BEGIN > > perform ''update t_products set > > > getup_instock=get_stock_getup(''||quote_literal(NEW.productid) > ||'',1) where > > id=''||quote_literal(NEW.productid); > > I don't think this is going to do what you want. Are you > sure you didn't > want EXECUTE rather than PERFORM? > > ---(end of > broadcast)--- > TIP 8: explain analyze is your friend > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.707 / Virus Database: 463 - Release Date: 2004. 06. 15. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.707 / Virus Database: 463 - Release Date: 2004. 06. 15. ---(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: [GENERAL] Trigger isn't fired
In article <[EMAIL PROTECTED]>, Együd Csaba <[EMAIL PROTECTED]> writes: > Hi Stephan, > it was the problem, but not realy understand the reason. The documentetion > sais that perform doesn't porvide the return value and I thought I do not > need that. But I read absently, and didn't realized that the query to > perform must be a SELECT. > Thank you for opening my eyes. :) Now the trigger does its task. Does it really? You're using NEW.productid, which should be undefined for a DELETE trigger. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Trigger isn't fired
Yes, yes you are right. :) I had already modified that (using TG_OP variable) before I posted the letter just didn't mentioned. By the way the trigger does its task. Rally. :) Thanks, -- Csaba > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of Harald Fuchs > Sent: 2004. június 27. 12:12 > To: [EMAIL PROTECTED] > Subject: Re: [GENERAL] Trigger isn't fired > > > In article <[EMAIL PROTECTED]>, > Együd Csaba <[EMAIL PROTECTED]> writes: > > > Hi Stephan, > > it was the problem, but not realy understand the reason. > The documentetion > > sais that perform doesn't porvide the return value and I > thought I do not > > need that. But I read absently, and didn't realized that > the query to > > perform must be a SELECT. > > > Thank you for opening my eyes. :) Now the trigger does its task. > > Does it really? You're using NEW.productid, which should be undefined > for a DELETE trigger. > > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Date localization
hello, Excuze me, If I want to add localization support in my language Except the messages in postgres, what parts, must be done in Glibc? and what parts must be done in postgres? LC_COLLATE,LC_CTYPE,LC_MONETARY,LC_NUMERIC,LC_TIME I found that the collation order must be done in glibc. What about Formatting of dates and times? Is it write that all of these must be done in glibc and therefore it will be abvailable in postgres? Thanks. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pgsql on debian
On Sun, 2004-06-27 at 00:36, Jay wrote: > Hi group, > I am trying to get pgsql working on my debian system. I installed pgsql > using apt-get. but then i cant connect to it . i am using the following > command > psql -U jay -d jaydb > > earlier i used create su postgres 'createdb root' jaydb. Sounds as if you created a database called 'root'. > but now i cannot access the db. > i get the follwing msgs > > psql: FATAL: database "jaydb" does not exist Use "psql -l" to list existing databases. -- Oliver Elphick [EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA "Therefore being justified by faith, we have peace with God through our Lord Jesus Christ." Romans 5:1 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] pgsql on debian
hi oliver i tried the following command but nothing seems to work psql -U root -d root psql: FATAL: IDENT authentication failed for user "root" any suggestions thanks jay Oliver Elphick wrote: On Sun, 2004-06-27 at 00:36, Jay wrote: Hi group, I am trying to get pgsql working on my debian system. I installed pgsql using apt-get. but then i cant connect to it . i am using the following command psql -U jay -d jaydb earlier i used create su postgres 'createdb root' jaydb. Sounds as if you created a database called 'root'. but now i cannot access the db. i get the follwing msgs psql: FATAL: database "jaydb" does not exist Use "psql -l" to list existing databases. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Performance problem on RH7.1
Hi, yes it is vacuumed regulary once a day. And vacuum full is done once a week. The reasons of the slow seq scan are those two stored procedures in the field list (get_stock and get_stock_getup). These take 13-20 ms every time thay executed. Multiplying with the nr of rows we get 11-18 sec. It is strange that the laptop substantially faster then the server. The get_stock* functions are executed 2-3 times faster. This is a reason, but I think it isn't enough. There must be something more there. Next time I'll try to run a fsck on the data partition. May be it will show something wrong. Thank you all. Best regards, -- Csaba Együd > -Original Message- > From: Alvaro Herrera [mailto:[EMAIL PROTECTED] > Sent: 2004. június 27. 3:38 > To: Együd Csaba > Cc: [EMAIL PROTECTED] (E-mail) > Subject: Re: [GENERAL] Performance problem on RH7.1 > > > On Sat, Jun 26, 2004 at 12:16:17PM +0200, Együd Csaba wrote: > > > I've a problem with the perfprmance of the production environment. > > I've two db servers. One on my laptop computer (2Ghz, 1GB, > WinXP, Cygwin, > > Postgres 7.3.4) and one on a production server (2GHz, 1GB, > Ultra SCSI, > > RH7.1, Postgres 7.3.2). > > > > I run the same dump and the same query on both of the computers. The > > difference is substantial. > > The query takes 5 times longer on the production server > then on the laptop. > > Are both databases properly vacuumed? Did you try a VACUUM FULL? I'm > wondering why it takes a lot of time seqscanning the > t_products table in > the production server compared to the laptop. > > -- > Alvaro Herrera () > "Hoy es el primer día del resto de mi vida" > > --- > Incoming mail is certified Virus Free. > Checked by AVG anti-virus system (http://www.grisoft.com). > Version: 6.0.707 / Virus Database: 463 - Release Date: 2004. 06. 15. > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.712 / Virus Database: 468 - Release Date: 2004. 06. 27. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] ERROR: tables can have at most 1600 columns
I found this error in /var/log/messages yesterday after a cron job wouldn't complete: STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate; ERROR: tables can have at most 1600 columns STATEMENT: ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER; ERROR: tables can have at most 1600 columns ...etc... The columns didn't exist at the time I tried to drop them, and \dt showed that the table only contained the normal dozen columns. Because I use the COPY command I remove columns to match the input from an ftp'ed file, add the columns, continue processing, etc. After dropping and then re-creating the table the error didn't recur and processing continued. My question is this: every night the database is vacuumed-full-analyze: wouldn't that prevent this condition from happening? Or is there some other regular maintenance I should be running? I'll be away for the next week but I look forward to reading any insight on this when I return. Thanks Ron -- running Postgresql 7.4.2 on debian stable ---(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: [GENERAL] pg_dump out of shared memory
[EMAIL PROTECTED] (Thomas F. O'Connell) wrote in message news: > postgresql.conf just has the default of 1000 shared_buffers. The > database itself has thousands of tables, some of which have rows > numbering in the millions. Am I correct in thinking that, despite the > hint, it's more likely that I need to up the shared_buffers? So the answer here, verified by Tom Lane and my own remedy to the problem, is "no". Now I'm curious: why does pg_dump require that max_connections * max_shared_locks_per_transaction be greater than the number of objects in the database? Or if that's not the right assumption about how pg_dump is working, how does pg_dump obtain its locks, and why is the error that it runs out of shared memory? Is there a portion of shared memory that's set aside for locks? What is the shared lock table? -tfo ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] [ADMIN] Is this a "Stupid Question" ?
Thanks Grega your explanation will go a far way, Thanks again -- Adam -- Grega Bremec wrote: ...and on Sun, Jun 20, 2004 at 07:50:53PM -0700, Adam Smith used the keyboard: I have posted this and similar questions repeatedly and can't even raise a single response. I am being led to believe that this then 'Must be a stupid question' although people say that there is no stupid question. Is that another for political correctness I am attempting an install of 7.4.3 on FreeBSD O/S 4.9, apparently remnants of 7.3.x are scattered around on the disk from (a) previous ports installation, causing mutex_lock/unlock, libpq.so and other installation problems. I want to reconfigure and reinstall. How do I know what, & where all these fragments are located or how do I uninstall all of them or at least those that should be removed. Hi Adam, I don't think it's a stupid question, but it may be, given the way ports are being maintained, difficult to answer. Figuring out where and what was being installed in 7.3.x would've been rather easy if you had installed the official source distribution of PostgreSQL - all it would've taken was a download from the archives and a reinstall from some wrapper script that took notice of where and what was being installed. It may have even been just as simple as running "configure" with original settings, followed by "make uninstall". Since you're using FBSD ports collection though, a lot of the responsibility for what goes where and how the various other aspects of the source distribution are being organized, has been delegated implicitly by you to the FBSD ports maintainer of the PostgreSQL package. I suggest you investigate where the ports graveyard of the postgres package is located in order to find out what happened to the version of PostgreSQL your system still seems to be holding parts of, and try downloading and reinstalling (or maybe even "uninstall"ing) it; if nothing else, at least it should enable you to look into how it was being organized across the disks by providing you with all the Makefiles that were used to install it. Bottom line is - any (or all) of the above may be next to impossible to do if the person that installed 7.3.x on that machine used some non-standard setup options and failed to document it somewhere. In that case, all that's left for you to do is to try to get a complete listing of the files that are being installed in 7.3.x (perhaps by installing with a DESTDIR, or using a different --prefix) and make heavy use of find(1), locate(1) and other UNIX system commands for locating files on a system, in an effort to chase down and remove all the fragments of that previous build. At least writing scripts to automate this for you isn't all that difficult to do. Hope this helped, ---(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
[GENERAL] Is this a "Stupid Question" ?
I have posted this and similar questions repeatedly and can't even raise a single response. I am being led to believe that this then 'Must be a stupid question' although people say that there is no stupid question. Is that another for political correctness I am attempting an install of 7.4.3 on FreeBSD O/S 4.9, apparently remnants of 7.3.x are scattered around on the disk from (a) previous ports installation, causing mutex_lock/unlock, libpq.so and other installation problems. I want to reconfigure and reinstall. How do I know what, & where all these fragments are located or how do I uninstall all of them or at least those that should be removed. Thanks -- Adam -- PS I should probably use the name Eve instead! ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] ERROR: tables can have at most 1600 columns
On Sun, Jun 27, 2004 at 11:11:32AM -0700, Ron St-Pierre wrote: > I found this error in /var/log/messages yesterday after a cron job > wouldn't complete: > STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate; > ERROR: tables can have at most 1600 columns > STATEMENT: ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER; > ERROR: tables can have at most 1600 columns > ...etc... > > The columns didn't exist at the time I tried to drop them, and \dt > showed that the table only contained the normal dozen columns. Have you done the DROP COLUMN/ADD COLUMN cycle to this table more than, say, 1500 times? Because a dropped column is actually only hidden from the user, but it's still present to the system and it will still affect the 1600 limit. Dropping the table and restoring from a backup would bring it to sanity, as you found out. And it will probably also get you back some performance (though it may be so small a gain that you may not notice). > My question is this: every night the database is vacuumed-full-analyze: > wouldn't that prevent this condition from happening? Nope. -- Alvaro Herrera () "The only difference is that Saddam would kill you on private, where the Americans will kill you in public" (Mohammad Saleh, 39, a building contractor) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Is this a "Stupid Question" ?
hmmm, not sure that this is the best way. under linux runing "./configure --help" will show you the default instlation paths. go to each one of them and delete. On future instelation you can consider using --prefix=/usr/local/priv_dir_pg_what_ever_ver dont forget to update PATH and ld.conf cheers -- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 Fax: 972-4-6990098 http://www.canaan.net.il -- - Original Message - From: "Adam Smith" <[EMAIL PROTECTED]> To: <"[EMAIL PROTECTED]"@postgresql.org> Sent: Monday, June 21, 2004 5:50 AM Subject: [GENERAL] Is this a "Stupid Question" ? > I have posted this and similar questions repeatedly and can't even raise > a single response. I am being led to believe that this then 'Must be a > stupid question' although people say that there is no stupid question. > Is that another for political correctness > > I am attempting an install of 7.4.3 on FreeBSD O/S 4.9, apparently > remnants of 7.3.x are scattered around on the disk from (a) previous > ports installation, causing mutex_lock/unlock, libpq.so and other > installation problems. I want to reconfigure and reinstall. How do I > know what, & where all these fragments are located or how do I uninstall > all of them or at least those that should be removed. > > Thanks > > -- Adam -- > PS I should probably use the name Eve instead! > > > ---(end of broadcast)--- > TIP 9: the planner will ignore your desire to choose an index scan if your > joining column's datatypes do not match > ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] pgsql on debian
1)from root su postgres 2)createuser the username 3)exit 4)su the user 5)createdb psql the database Jay wrote: hi oliver i tried the following command but nothing seems to work psql -U root -d root psql: FATAL: IDENT authentication failed for user "root" any suggestions thanks jay Oliver Elphick wrote: On Sun, 2004-06-27 at 00:36, Jay wrote: Hi group, I am trying to get pgsql working on my debian system. I installed pgsql using apt-get. but then i cant connect to it . i am using the following command psql -U jay -d jaydb earlier i used create su postgres 'createdb root' jaydb. Sounds as if you created a database called 'root'. but now i cannot access the db. i get the follwing msgs psql: FATAL: database "jaydb" does not exist Use "psql -l" to list existing databases. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] pgsql on debian
Jay <[EMAIL PROTECTED]> writes: > i tried the following command but nothing seems to work > psql -U root -d root > psql: FATAL: IDENT authentication failed for user "root" You basically cannot use the -U switch when using IDENT authorization; that auth mode requires that your presented Postgres username be the same as the Unix name you're logged in under. (If you don't like this then change to a different auth mode; see pg_hba.conf.) In any case, root is unlikely to be the name of the postgres superuser. When you probably want to do is "su" to become postgres and then psql without using -U. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Performance problem on RH7.1
=?iso-8859-1?Q?Egy=FCd_Csaba?= <[EMAIL PROTECTED]> writes: > It is strange that the laptop substantially faster then the server. The > get_stock* functions are executed 2-3 times faster. So what do those stored procedures do exactly? What it smells like to me is a bad plan for a query executed in one of the stored procedures, but it's hard to theorize with no data. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] indexing lat lon
I have the following situation: I'm developing an reverse geocoder, so input is lat/lon coordinate and output is the nearest textual location. I have a database of +- 2 000 000 records that contains lat/lon and full name of the location. These are the steps I take now: 1 'draw' a box around the lat/lon position we have as input 2 search the DB for all the points in this box 3 measure the distance to each point in the box This works fine, but step 2 takes too lang for obvious reasons... select * from table where lat between box_point1_lat and box_point2_lat and lon between box_point1_lon and box_point2_lon; This would require a seq scan for but indexing the table doesn't help much either (factor 2) ... Is there a good way of indexing the lat/lon column so lookup is faster? I have looked at PostGis but didn't think it would give a good solution in this context (correct me if I'm wrong) _ Plannen om een nieuwe computer te kopen? Kijk eens hier.. http://www.msn.be/shopping/dell_bis/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] pg_dump out of shared memory
[EMAIL PROTECTED] (Thomas F. O'Connell) writes: > Now I'm curious: why does pg_dump require that > max_connections * max_shared_locks_per_transaction be greater than the > number of objects in the database? Not objects, just tables. pg_dump takes AccessShareLock (the weakest kind of lock) on each table it intends to dump. This is basically just to prevent someone from dropping the table underneath it. (It would actually have to take that lock anyway as a byproduct of reading the table contents, but we grab the locks ASAP during pg_dump startup to reduce the risks of problems from concurrent drops.) On a database with thousands of tables, this could easily require more locks than the default lock table size can hold. Most normal apps don't need more than a few tables locked within any one transaction, which is why the table size is calculated as a multiple of max_connections. There's a great deal of slop involved, because we pad the shared memory size by 100K or so which is room for quite a few more lock entries than the nominal table size ... but eventually you'll run out of room. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] ERROR: tables can have at most 1600 columns
Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Sun, Jun 27, 2004 at 11:11:32AM -0700, Ron St-Pierre wrote: >> STATEMENT: ALTER TABLE victoria.eodData DROP COLUMN tickDate; >> ERROR: tables can have at most 1600 columns >> STATEMENT: ALTER TABLE victoria.eodData ADD COLUMN tickerID INTEGER; >> ERROR: tables can have at most 1600 columns > Have you done the DROP COLUMN/ADD COLUMN cycle to this table more than, > say, 1500 times? Because a dropped column is actually only hidden from > the user, but it's still present to the system and it will still affect > the 1600 limit. That is a good theory, but it doesn't quite explain why Ron's getting the error from DROP COLUMN --- AFAICS, the places that would issue such an error won't get called in that path. I tried to reproduce this and could not: after 1600 cycles of adding and dropping a column, I did indeed start to get "tables can have at most 1600 columns" from ADD, but DROP continued to behave normally. Ron, are you sure these errors were coming from the DROPs and not only the ADDs? Can you exhibit a test case? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] indexing lat lon
"Jonathan Raemdonck" <[EMAIL PROTECTED]> writes: > These are the steps I take now: > 1 'draw' a box around the lat/lon position we have as input > 2 search the DB for all the points in this box > 3 measure the distance to each point in the box Right. All you need is an index amenable to step 2. > Is there a good way of indexing the lat/lon column so lookup is faster? Yeah, you can store the lat/long as a point, build an rtree index on it, and use box-overlap as the indexable operator. I seem to recall that you actually have to store the lat/long as a box of zero size to get this to work with the present set of rtree-indexable operators. If you look in the archives you will find some previous similar cases. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[GENERAL] postgresql perl connectivity
hi people i am trying to connect to ppstgresql on a debian system using perl . i am getting the following error .can any one please suggest what has to be done install_driver(pg) failed: Can't locate DBD/pg.pm in @INC (@INC contains: /etc/perl /usr/local/lib/perl/5.8.3 /usr/local/share/perl/5.8.3 /usr/lib/perl5 /usr/share/perl5 /usr/lib/perl/5.8 /usr/share/perl/5.8 /usr/local/lib/site_perl .) at (eval 1) line 3. Perhaps the DBD::pg perl module hasn't been fully installed, or perhaps the capitalisation of 'pg' isn't right. Available drivers: ExampleP, Pg, Proxy, Sponge, mysql. at psqldb.pl line 10 thanks jay ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] postgresql perl connectivity
On Sun, 2004-06-27 at 21:05, Jay wrote: > hi people > i am trying to connect to ppstgresql on a debian system using perl . i > am getting the following error .can any one please suggest what has to > be done > > > install_driver(pg) failed: Can't locate DBD/pg.pm in @INC (@INC > contains: /etc/perl /usr/local/lib/perl/5.8.3 > /usr/local/share/perl/5.8.3 /usr/lib/perl5 /usr/share/perl5 > /usr/lib/perl/5.8 /usr/share/perl/5.8 /usr/local/lib/site_perl .) at > (eval 1) line 3. > Perhaps the DBD::pg perl module hasn't been fully installed, > or perhaps the capitalisation of 'pg' isn't right. > Available drivers: ExampleP, Pg, Proxy, Sponge, mysql. > at psqldb.pl line 10 > > > thanks > jay I have not done much perl programming but it looks like you need to install the CPAN DBD package. -- Scot L. Harris <[EMAIL PROTECTED]> ---(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