Re: [GENERAL] Using the wrong index (very suboptimal), why?
Tom, > You need to > look into what the estimated vs actual rowcounts are for just the > two-column condition (ie, where nid = something AND iid1 = something). > Getting those estimates to match reality is the key to getting a sane > plan choice here. Many thanks - I agree in principle it isn't automatically the worst decision, however for this data set it unfortunately is :( Improving the accuracy of the cost estimates is exactly what I'm trying to achieve, so far I've tried - increasing the statistics target to the maximum setting with SET STATISTICS 1000 on columns rid, nid and iid1 of answers, then re-vacuuming - adjusting random_page_cost downwards even more to try to make it prefer index IO - increasing cpu_tuple_cost up to 1.0 (from 0.01) to try to discourage it from processing data rows - decreating cpu_index_tuple_cost to 0.0005 (from 0.005) to encourage it to spend more time in index rows. None of the above had any effect :( Interestingly, I didn't revacuum between changing the cost figues in the config file (I did reload). The cost figures from EXPLAIN did change, but using the queries below the 'wrong' index always comes up just over 1/3rd more expensive. Here's a query that matches actual data in the table; it sees the cost of the 2-column index as much less, but it takes an order of magnitude longer to run (using nid & iid1 means filtering through around 22,000 rows). The three queries; first is the one that's used in practise; against all 3 columns. The next 2 queries use the two different combinations of just 2 columns: emystery=# explain analyse select * FROM answers WHERE rid=668332334 AND nid=253869913 AND iid1=535292129; QUERY PLAN -- Index Scan using index_answers_nidiid1 on answers (cost=0.00..114.14 rows=1 width=60) (actual time=0.168..790.262 rows=1 loops=1) Index Cond: ((nid = 253869913) AND (iid1 = 535292129)) Filter: (rid = 668332334) Total runtime: 790.305 ms (4 rows) emystery=# explain analyse select * FROM answers WHERE rid=668332334 AND nid=253869913; QUERY PLAN Index Scan using index_answers_ridnidiid1 on answers (cost=0.00..152.49 rows=1 width=60) (actual time=0.120..0.141 rows=18 loops=1) Index Cond: ((rid = 668332334) AND (nid = 253869913)) Total runtime: 0.207 ms (3 rows) emystery=# explain analyse select * FROM ecos_answers WHERE nid=253869913 AND iid1=535292129; QUERY PLAN --- Index Scan using index_ecos_answers_nidiid1 on ecos_answers (cost=0.00..114.14 rows=1 width=60) (actual time=0.206..2398.645 rows=21891 loops=1) Index Cond: ((nid = 253869913) AND (iid1 = 535292129)) Total runtime: 2424.769 ms (3 rows) What setting should I be tweaking to make the two column index more expensive? I thought I would need to either... - inform the database that there are likely to be a lot of rows that need filtering (SET STATISTICS [maximum]???) or - tell the database that filtering rows is expensive (cpu_tuple_cost++, random_page_cost-- ???) ...but both of which I've already triedbut it would seem that from the EXPLAIN output that the first one is key; it shouldn't think (for the last example( that it's only going to get 1 row back!) but aside from increasing SET STATISTICS what other setting is there to inform it of this? The only thing I can think that's remaining is to rebuild/replace the preferred index, that should make it smaller and *if* the index cost estimates are partly based on # disk pages that should reduce the perceived cost of using it. Is this the case? Even if it does that still doesn't fix the underlying problem. Any help appreciated, Many thanks Shane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using the wrong index (very suboptimal), why?
"Shane Wright" writes: > Tom, >> You need to >> look into what the estimated vs actual rowcounts are for just the >> two-column condition (ie, where nid = something AND iid1 = something). > Improving the accuracy of the cost estimates is exactly what I'm trying > to achieve, so far I've tried > - increasing the statistics target to the maximum setting with SET > STATISTICS 1000 on columns rid, nid and iid1 of answers, then > re-vacuuming I hope you meant re-analyzing. > emystery=# explain analyse select * FROM ecos_answers WHERE > nid=253869913 AND iid1=535292129; > QUERY PLAN > > > --- > Index Scan using index_ecos_answers_nidiid1 on ecos_answers > (cost=0.00..114.14 rows=1 width=60) (actual time=0.206..2398.645 > rows=21891 loops=1) >Index Cond: ((nid = 253869913) AND (iid1 = 535292129)) > Total runtime: 2424.769 ms > (3 rows) Well, here's the problem all right: 1 row estimated vs 21891 actual. Is there something odd about the joint distribution of these two columns? regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using the wrong index (very suboptimal), why?
Hi Tom, >> - increasing the statistics target to the maximum setting with SET >> STATISTICS 1000 on columns rid, nid and iid1 of answers, then >> re-vacuuming. > I hope you meant re-analyzing. Hehe absolutely yes; I always VACUUM VERBOSE ANALYSE :) >> Index Scan using index_answers_nidiid1 on answers >> (cost=0.00..114.14 rows=1 width=60) (actual time=0.206..2398.645 >> rows=21891 loops=1) >>Index Cond: ((nid = 253869913) AND (iid1 = 535292129)) >> Total runtime: 2424.769 ms >Well, here's the problem all right: 1 row estimated vs 21891 actual. >Is there something odd about the joint distribution of these two >columns? Good :) - I've been chasing the same thing, albeit not with any luck yet :( Shouldn't be anything odd about the data I wouldn't have thought... There are ~670 million rows. No nulls in nid, ~6% of iid1 are null (they will always be null or not null for a given nid. I.e. rows with a given nid value will either all be null or all be not null). nids are randomly selected, there are only ~27000 distinct values, all between 1 and 9. iid1s are also random in the same range, ~5 distinct values. All the rows for a given value of nid will have one of a small set of possible iid1 values; usually 5-15 distinct values. The frequency at which each nid may occurs is quite uneven; some will be tens of times, others will be a couple of hundred thousand. Same applies to corresponding iid1 values. (the table stores answers to questions; nid is the question ID, iid1 is the answer ID [for questions where the user picks from a pre-defined list] - iid1 is NULL for textual answers. iid1 values are grouped into sets of options the user can pick from, defined elsewhere. These sets can be shared across questions/nids but not often) Does the above sound strange? There aren't any strange errors from the database, autovacuum is enabled (but not vacuuming the table often enough). This problem was triggered this time when I manually vacuumed the table - which cleared a lot of dead rows (again, I always VACUUM VERBOSE ANALYSE). A separate installation with a similar data set (actually the same but older; seems the most comparable) estimated 10 rows returned for the same query. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Using the wrong index (very suboptimal), why?
Tom, >> Index Scan using index_answers_nidiid1 on answers >> (cost=0.00..114.14 rows=1 width=60) (actual time=0.206..2398.645 >> rows=21891 loops=1) >>Index Cond: ((nid = 253869913) AND (iid1 = 535292129)) >> Total runtime: 2424.769 ms >Well, here's the problem all right: 1 row estimated vs 21891 actual. >Is there something odd about the joint distribution of these two >columns? I should also clarify, there will almost always be many many rows that match any nid/iid1 pair (for any nid or iid1 value that exists in this table anyway). The chance of there actually being only one result is very small. Most of the time this will be hundreds/thousands - but will also be tens of thousands on a regular basis. A hundred thousand or more periodically. Many thanks Shane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy/delete issue
Adrian Klaver wrote: > On Sunday 21 December 2008 1:49:18 am Herouth Maoz wrote: > >> Adrian Klaver wrote: >> >>> >>> >>> Are you sure the problem is not in "$datefield" = "*" . That the script >>> that formats the data file is not correctly adding "*" to the right file. >>> Seems almost like sometimes the second CMD is being run against the table >>> that the first CMD should be run on. In other words it is not doing a >>> complete delete , but a date based one, and you then import duplicate >>> records. >>> >> Thanks for your reply. The file containing the tables list is static - >> it doesn't change from one run to the next (unless I edit it personally). >> >> Herouth >> > > Well something is not static :) You mentioned this happens only with one > table. Have you tried running your procedure against that table only? Well, every time this happens, I re-run the procedure, with all the lines in the data files up to the given table deleted. And it works. Then I restore the original data file. And the next day it works. It only happens once in a while. > Just > because a DELETE did not error does not mean it succeeded in the way you > wanted. You might want to throw a count() in the mix to see if you are really > clearing out the table the way you want to. I wonder if there is a way to use the result of "count()" in \echo... > Also is the actual data file static from one run to the next? If you mean the data file that contains the list of tables, then yes. If you mean the data in the table itself, then no, the data changes - new records are added and old ones are updated. > Would also help to see the schema for the > table involved and maybe a sample of the data, if that is possible. > > A sample of the data would be a bit tricky, as this is customers' private information. But the table schema is: CREATE TABLE web1010.users ( user_id CHAR(32)PRIMARY KEY NOT NULL, whitelabel_id NUMERIC(21) NOT NULL, usernameVARCHAR(30) NOT NULL, passwordCHAR(32)NOT NULL, perms VARCHAR(255)NOT NULL, first_name VARCHAR(40) NULL, last_name VARCHAR(40) NULL, total_pointsINTEGER DEFAULT 0 NOT NULL, date_createdTIMESTAMP NOT NULL, date_birth TIMESTAMP NULL, gender INTEGER NULL, city_id NUMERIC(21) NULL, is_active SMALLINTNOT NULL, email VARCHAR(255)NULL, subscriptin_id NUMERIC(21) NULL, subscriptin_num_of_msg INTEGER NULL, subscriptin_date_start TIMESTAMP NULL, subscriptin_sent_datetime TIMESTAMP NULL, subscriptin_credit_left INTEGER NULL, subscriptin_status INTEGER NULL, subscriptin_sent_reference NUMERIC(21) NULL, first_time_subscribed VARCHAR(10) NULL, sms_credit INTEGER NULL, reg_pid NUMERIC(21) NULL, spam_fl SMALLINTNULL, constraint PK_USERS unique (whitelabel_id,username) ) ; I suppose this doesn't happen with other tables in the process, because most other tables don't have two unique constraints in them - most only have the primary key. But still, if everything is deleted from the table, this should not be an issue... I might take Dennis Brakhane's advice and replace the DELETE command with TRUNCATE, as I see no harm in doing so. Nevertheless, DELETE should either work or fail saying "could not delete because...". Otherwise PostgreSQL is not a very reliable... Thanks, Herouth
[GENERAL] Donwload 8.4
Hi ! There isn't another way to download 8.4devel that isn't cvs and the last rpm binary? I mean, i need source code, but in my work i can't access with cvs because proxy policies. Sorry for my bad english, -- Emanuel Calvo Franco Syscope Postgresql Consultant ArPUG / AOSUG Member -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Donwload 8.4
ftp://ftp.postgresql.org/pub/snapshot/dev/ I think that's the right place. On Tue, Dec 23, 2008 at 9:09 AM, Emanuel Calvo Franco wrote: > Hi ! > > There isn't another way to download 8.4devel that isn't > cvs and the last rpm binary? > > I mean, i need source code, but in my work i can't > access with cvs because proxy policies. > > Sorry for my bad english, > > -- > Emanuel Calvo Franco > Syscope Postgresql Consultant > ArPUG / AOSUG Member > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- When fascism comes to America, it will be draped in a flag and carrying a cross - Sinclair Lewis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Copy/delete issue
On Tuesday 23 December 2008 6:43:56 am Herouth Maoz wrote: > > Well, every time this happens, I re-run the procedure, with all the > lines in the data files up to the given table deleted. And it works. > Then I restore the original data file. And the next day it works. It > only happens once in a while. See next comment. > > > Also is the actual data file static from one run to the next? > > If you mean the data file that contains the list of tables, then yes. If > you mean the data in the table itself, then no, the data changes - new > records are added and old ones are updated. I should have been more specific. You mentioned you repeat the procedure 5 minutes or so after a failure. Is there a change in the actual data between those runs? > > > Would also help to see the schema for the > > table involved and maybe a sample of the data, if that is possible. > > A sample of the data would be a bit tricky, as this is customers' > private information. But the table schema is: > > CREATE TABLE web1010.users > ( > user_id CHAR(32)PRIMARY KEY NOT NULL, > whitelabel_id NUMERIC(21) NOT NULL, > usernameVARCHAR(30) NOT NULL, > passwordCHAR(32)NOT NULL, > perms VARCHAR(255)NOT NULL, > first_name VARCHAR(40) NULL, > last_name VARCHAR(40) NULL, > total_pointsINTEGER DEFAULT 0 NOT NULL, > date_createdTIMESTAMP NOT NULL, > date_birth TIMESTAMP NULL, > gender INTEGER NULL, > city_id NUMERIC(21) NULL, > is_active SMALLINTNOT NULL, > email VARCHAR(255)NULL, > subscriptin_id NUMERIC(21) NULL, > subscriptin_num_of_msg INTEGER NULL, > subscriptin_date_start TIMESTAMP NULL, > subscriptin_sent_datetime TIMESTAMP NULL, > subscriptin_credit_left INTEGER NULL, > subscriptin_status INTEGER NULL, > subscriptin_sent_reference NUMERIC(21) NULL, > first_time_subscribed VARCHAR(10) NULL, > sms_credit INTEGER NULL, > reg_pid NUMERIC(21) NULL, > spam_fl SMALLINTNULL, > constraint PK_USERS unique (whitelabel_id,username) > ) > ; > > I suppose this doesn't happen with other tables in the process, because > most other tables don't have two unique constraints in them - most only > have the primary key. But still, if everything is deleted from the > table, this should not be an issue... In the original post you said the constraint violation was on the PK. Is that the case or is it on PK_USERS? > > I might take Dennis Brakhane's advice and replace the DELETE command > with TRUNCATE, as I see no harm in doing so. Nevertheless, DELETE should > either work or fail saying "could not delete because...". Otherwise > PostgreSQL is not a very reliable... Worth trying. However it does not answer the question of what is going on. While it is possible that there is a DELETE bug, I still believe it is a case of DELETE working in way you are not expecting. > > > Thanks, > > Herouth -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Question about pattern matching
TABLENAME id Candidate pattern - 1 0089258068520 2 008925806852 3 00892580685 4 0089258068 5 008925806 6 00892580 7 0089258 8 008925 9 00892 10 0089 11 008 12 00 13 0 PATTERN --- 008925700 QUESTION Pls let me know which is the best way to match the following pattern against this table and Receive id = 8 that is the longest matching pattern from left to right. BR, Sakellarios Dimitris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
On Tue, Dec 23, 2008 at 9:05 AM, wrote: > TABLENAME > > id Candidate pattern > - > 1 0089258068520 > 2 008925806852 > 3 00892580685 > 4 0089258068 > 5 008925806 > 6 00892580 > 7 0089258 > 8 008925 > 9 00892 > 10 0089 > 11 008 > 12 00 > 13 0 > > > PATTERN > --- > 008925700 > > QUESTION > > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. Without getting out the pgsql manual to write the code, I'd basically append enough 0s to each candidate to pad them out to the same length, all ending in one or more 0s, except the longest, which wouldn't need padding. Then I'd see which one matched. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
I should say I'd pad them to match length with the input value. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
dimitris.sakellar...@telesuite.gr wrote: > TABLENAME > > idCandidate pattern > - > 10089258068520 > 2008925806852 > 300892580685 > 40089258068 > 5008925806 > 600892580 > 70089258 > 8008925 > 900892 > 100089 > 11008 > 1200 > 130 > > > PATTERN > --- > 008925700 > > QUESTION > > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. The simplest way would be to select the id of the record with max(length(pattern)), using a WHERE clause to filter for only matching patterns by comparing the substring of both pattern and teststr of length min(length(pattern),length(teststr)) for equality. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
Hi, Dimitris I think simple SELECT TABLENAME.id FROM TABLENAME WHERE prm_patern ilike TABLENAME.candidate_pattern||'%' ORDER BY |char_length(|TABLENAME.candidate_pattern) desc LIMIT 1 should do the trick. (Provided TABLENAME is not very large of course) Julius Tuskenis dimitris.sakellar...@telesuite.gr rašė: TABLENAME idCandidate pattern - 10089258068520 2008925806852 300892580685 40089258068 5008925806 600892580 70089258 8008925 900892 100089 11008 1200 130 PATTERN --- 008925700 QUESTION Pls let me know which is the best way to match the following pattern against this table and Receive id = 8 that is the longest matching pattern from left to right. BR, Sakellarios Dimitris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
SELECT ... from . WHERE X LIKE pattern || '%' ORDER BY length(pattern) DESC LIMIT 1; ??? basic prefix matching for telcos :P -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
thanks alot for your help. Dimitris Quoting Grzegorz Jaśkiewicz : SELECT ... from . WHERE X LIKE pattern || '%' ORDER BY length(pattern) DESC LIMIT 1; ??? basic prefix matching for telcos :P -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
Jef thanks alot for your help. I appreciate that! It worked fine. Dimitris Quoting "Hoover, Jeffrey" : cameradb_dev=# select id, Candidate_pattern from all_patterns where :pattern like Candidate_pattern||'%' and candidate_pattern between substring(:pattern from 1 for 1) and :pattern order by length(Candidate_pattern) desc limit 1; id | candidate_pattern +--- 8 | 008925 (1 row) note 1: bind (or substitute) your value for :pattern note 2: "and candidate_pattern between..." only helps if candidate_pattern is indexed, if there aren't many rows it is not necessary -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of dimitris.sakellar...@telesuite.gr Sent: Tuesday, December 23, 2008 11:05 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Question about pattern matching TABLENAME id Candidate pattern - 1 0089258068520 2 008925806852 3 00892580685 4 0089258068 5 008925806 6 00892580 7 0089258 8 008925 9 00892 10 0089 11 008 12 00 13 0 PATTERN --- 008925700 QUESTION Pls let me know which is the best way to match the following pattern against this table and Receive id = 8 that is the longest matching pattern from left to right. BR, Sakellarios Dimitris. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
On Tue, Dec 23, 2008 at 06:05:08PM +0200, dimitris.sakellar...@telesuite.gr wrote: > QUESTION > > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. check this: http://www.depesz.com/index.php/2008/03/04/searching-for-longest-prefix/ read also the comments additionally, check this url: http://pgfoundry.org/projects/prefix Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Question about pattern matching
Thanks depesz, It was really helpful Dimitris. -Original Message- From: dep...@depesz.com [mailto:dep...@depesz.com] Sent: Tuesday, December 23, 2008 9:18 PM To: dimitris.sakellar...@telesuite.gr Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Question about pattern matching On Tue, Dec 23, 2008 at 06:05:08PM +0200, dimitris.sakellar...@telesuite.gr wrote: > QUESTION > > Pls let me know which is the best way to match the > following pattern against this table and Receive id = 8 > that is the longest matching pattern from left to right. check this: http://www.depesz.com/index.php/2008/03/04/searching-for-longest-prefix/ read also the comments additionally, check this url: http://pgfoundry.org/projects/prefix Best regards, depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 __ NOD32 3693 (20081215) Information __ This message was checked by NOD32 antivirus system. http://www.eset.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] need some help with pl-pgsql
have a function written in pgsql it runs just fine, except its doing something really stupid. The function runs just fine till this select statement Select (wo_wipvalue/wo_qtyord) into _TotalCost from wo where wo_id = pWoid ; This goes to the work order header table to gets the current value divided by current qty thats been completed so far, then sticks the value into _TotalCost variable based on the parameter passed into variable pWoid. problem is it returns NULL which is impossible as i can manually run the select statement and get the $1.589445 I have no idea how to fix this problem going forward??? and the database is 8.3.5 running Ubuntu 8.0 lts -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] encoding of PostgreSQL messages
Hi all ! How can I programmatically detect which encoding a PostgreSQL server I am trying to connect to sends back messages -- before I connect (so client_encoding and the pg_settings table are flat out). Thanks, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] encoding of PostgreSQL messages
Karsten Hilbert wrote: > Hi all ! > > How can I programmatically detect which encoding a > PostgreSQL server I am trying to connect to sends back > messages -- before I connect (so client_encoding and > the pg_settings table are flat out). Hmm, isn't client_encoding reported in the startup packet sent by the server, after auth? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] encoding of PostgreSQL messages
On Tue, Dec 23, 2008 at 06:45:17PM -0300, Alvaro Herrera wrote: > > How can I programmatically detect which encoding a > > PostgreSQL server I am trying to connect to sends back > > messages -- before I connect (so client_encoding and > > the pg_settings table are flat out). > > Hmm, isn't client_encoding reported in the startup packet sent by the > server, after auth? That would not quite be enough -- I am talking about messages reported *during* auth, say FATAL: password authentication failed for user "postgres" or fe_sendauth: no password supplied both of which, in other locales, may contain non-ASCII characters. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need some help with pl-pgsql
On Tuesday 23 December 2008 1:04:49 pm justin wrote: > have a function written in pgsql it runs just fine, except its doing > something really stupid. > > The function runs just fine till this select statement > > Select (wo_wipvalue/wo_qtyord) > into _TotalCost > from wo > where wo_id = pWoid ; > > This goes to the work order header table to gets the current value > divided by current qty thats been completed so far, then sticks the > value into _TotalCost variable based on the parameter passed into > variable pWoid. > > problem is it returns NULL which is impossible as i can manually run > the select statement and get the $1.589445 > > I have no idea how to fix this problem going forward??? > > > and the database is 8.3.5 running Ubuntu 8.0 lts Would help to see the whole function. Also make sure you did not name one of the variables the same as a column name, this will confuse plpgsql. Are you using the same value for wo_id in the function as in the manual select statement? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need some help with pl-pgsql
Adrian Klaver wrote:
On Tuesday 23 December 2008 1:04:49 pm justin wrote:
have a function written in pgsql it runs just fine, except its doing
something really stupid.
The function runs just fine till this select statement
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
This goes to the work order header table to gets the current value
divided by current qty thats been completed so far, then sticks the
value into _TotalCost variable based on the parameter passed into
variable pWoid.
problem is it returns NULL which is impossible as i can manually run
the select statement and get the $1.589445
I have no idea how to fix this problem going forward???
and the database is 8.3.5 running Ubuntu 8.0 lts
Would help to see the whole function. Also make sure you did not name one of
the variables the same as a column name, this will confuse plpgsql. Are you
using the same value for wo_id in the function as in the manual select
statement?
First the funciton has been running for months and never has had a
problem. No changes to the database scheme. Second use variable naming
scheme completely different from column names. _ always is the first
character in variables. p is always the first character in passed
parameters.
Take a look at the screen shot and be in aw as i am
postproduction(pwoid integer, pqty numeric, pbackflush boolean,
pbackflushoperations boolean, pitemlocseries integer, psuuser text,
prnuser text, pdate date)
RETURNS integer AS
$BODY$ DECLARE
_woNumber TEXT;
_itemlocSeries INTEGER;
_parentQty NUMERIC;
_qty NUMERIC;
_TotalCost numeric;
BEGIN
IF (pQty <= 0) THEN
RETURN 0;
END IF;
IF ( ( SELECT wo_status
FROM wo
WHERE (wo_id=pWoid) ) NOT IN ('R','E','I') ) THEN
RETURN -1;
END IF;
--If this is item type Job then we are using the wrong function
SELECT item_type INTO _check
FROM wo, itemsite, item
WHERE ((wo_id=pWoid)
AND (wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (item_type = 'J'));
IF (FOUND) THEN
RAISE EXCEPTION 'Work orders for job items are posted when
quantities are shipped on the associated sales order';
END IF;
SELECT formatWoNumber(pWoid) INTO _woNumber;
SELECT roundQty(item_fractional, pQty) INTO _parentQty
FROM wo,
itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Create the material receipt transaction
IF (pItemlocSeries = 0) THEN
SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
ELSE
_itemlocSeries = pItemlocSeries;
END IF;
--Lets get Wips Current total cost
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
--Moves WIP into Inventory.
SELECT postInvTrans( itemsite_id,
'RM',
_parentQty,
'W/O',
'WO',
_woNumber,
'',
'Receive Inventory from Manufacturing',
costcat_asset_accnt_id,
costcat_wip_accnt_id,
_itemlocSeries,
true,
_TotalCost,
pDate::timestamp ) INTO _invhistid
FROM wo,
itemsite,
costcat
WHERE ( (wo_itemsite_id=itemsite_id)
AND (itemsite_costcat_id=costcat_id)
AND (wo_id=pWoid) );
-- Increase this W/O's received qty decrease its WIP value
UPDATE wo SET
wo_qtyrcv = (wo_qtyrcv + _parentQty),
wo_wipvalue = (wo_wipvalue - (_TotalCost*pQty))
FROM itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Make sure the W/O is at issue status
UPDATE wo SET
wo_status='I'
WHERE (wo_id=pWoid);
RETURN _itemlocSeries;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION postproduction(integer, numeric, boolean, boolean,
integer, text, text, date) OWNER TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO public;
Re: [GENERAL] need some help with pl-pgsql
- "justin" wrote:
> Adrian Klaver wrote:
>
> Would help to see the
> whole function. Also make sure you did not name one of
> the variables the same as a column name, this will confuse plpgsql.
> Are you
> using the same value for wo_id in the function as in the manual select
> statement?
>
> First the funciton has been running for months and never has had a
> problem. No changes to the database scheme. Second use variable naming
> scheme completely different from column names. _ always is the first
> character in variables. p is always the first character in passed
> parameters.
>
> Take a look at the screen shot and be in aw as i am
>
>
>
> postproduction(pwoid integer, pqty numeric, pbackflush boolean,
> pbackflushoperations boolean, pitemlocseries integer, psuuser text,
> prnuser text, pdate date)
> RETURNS integer AS
> $BODY$ DECLARE
> _woNumber TEXT;
> _itemlocSeries INTEGER;
>
> _parentQty NUMERIC;
> _qty NUMERIC;
> _TotalCost numeric;
>
> BEGIN
>
> IF (pQty <= 0) THEN
> RETURN 0;
> END IF;
>
> IF ( ( SELECT wo_status
> FROM wo
> WHERE (wo_id=pWoid) ) NOT IN ('R','E','I') ) THEN
> RETURN -1;
> END IF;
>
> --If this is item type Job then we are using the wrong function
> SELECT item_type INTO _check
> FROM wo, itemsite, item
> WHERE ((wo_id=pWoid)
> AND (wo_itemsite_id=itemsite_id)
> AND (itemsite_item_id=item_id)
> AND (item_type = 'J'));
>
> IF (FOUND) THEN
> RAISE EXCEPTION 'Work orders for job items are posted when quantities
> are shipped on the associated sales order';
> END IF;
>
> SELECT formatWoNumber(pWoid) INTO _woNumber;
>
> SELECT roundQty(item_fractional, pQty) INTO _parentQty
> FROM wo,
> itemsite,
> item
> WHERE ((wo_itemsite_id=itemsite_id)
> AND (itemsite_item_id=item_id)
> AND (wo_id=pWoid));
>
> -- Create the material receipt transaction
> IF (pItemlocSeries = 0) THEN
> SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
> ELSE
> _itemlocSeries = pItemlocSeries;
> END IF;
>
>
>
> --Lets get Wips Current total cost
> Select (wo_wipvalue/wo_qtyord)
> into _TotalCost
> from wo
> where wo_id = pWoid ;
> --Moves WIP into Inventory.
> SELECT postInvTrans( itemsite_id,
> 'RM',
> _parentQty,
> 'W/O',
> 'WO',
> _woNumber,
> '',
> 'Receive Inventory from Manufacturing',
> costcat_asset_accnt_id,
> costcat_wip_accnt_id,
> _itemlocSeries,
> true,
> _TotalCost,
> pDate::timestamp ) INTO _invhistid
> FROM wo,
> itemsite,
> costcat
> WHERE ( (wo_itemsite_id=itemsite_id)
> AND (itemsite_costcat_id=costcat_id)
> AND (wo_id=pWoid) );
>
> -- Increase this W/O's received qty decrease its WIP value
> UPDATE wo SET
> wo_qtyrcv = (wo_qtyrcv + _parentQty),
> wo_wipvalue = (wo_wipvalue - (_TotalCost*pQty))
> FROM itemsite,
> item
> WHERE ((wo_itemsite_id=itemsite_id)
> AND (itemsite_item_id=item_id)
> AND (wo_id=pWoid));
>
> -- Make sure the W/O is at issue status
> UPDATE wo SET
> wo_status='I'
> WHERE (wo_id=pWoid);
>
>
>
> RETURN _itemlocSeries;
>
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
> COST 100;
> ALTER FUNCTION postproduction(integer, numeric, boolean, boolean,
> integer, text, text, date) OWNER TO justin;
> GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
> boolean, integer, text, text, date) TO justin;
> GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
> boolean, integer, text, text, date) TO public;
Hmmm. A couple of suggestions while I ponder this more. For clarity sake decide
on a case scheme. I start getting twitchy when I see pwoid and pWoid refer to
the same thing. This depends on case folding being consistent, not something I
like to count on. Second you might want to put in a few RAISE NOTICE statements
to see what values are being pulled from the db for wo_wipvalue and wo_qtyord
and what is being supplied to the WHERE clause for pWoid.
Adrian Klaver
akla...@comcast.net
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need some help with pl-pgsql
On Tuesday 23 December 2008 3:36:46 pm Adrian Klaver wrote: > > Hmmm. A couple of suggestions while I ponder this more. For clarity sake > decide on a case scheme. I start getting twitchy when I see pwoid and pWoid > refer to the same thing. This depends on case folding being consistent, not > something I like to count on. Second you might want to put in a few RAISE > NOTICE statements to see what values are being pulled from the db for > wo_wipvalue and wo_qtyord and what is being supplied to the WHERE clause > for pWoid. > > Adrian Klaver > akla...@comcast.net SELECT postInvTrans( itemsite_id, 'RM', _parentQty, 'W/O', 'WO', _woNumber, '', 'Receive Inventory from Manufacturing', costcat_asset_accnt_id, costcat_wip_accnt_id, _itemlocSeries, true, _TotalCost, pDate::timestamp ) INTO _invhistid Potential issue. I don't see where _invhistid is declared. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] encoding of PostgreSQL messages
On Dec 23, 2008, at 4:24 PM, Karsten Hilbert wrote: How can I programmatically detect which encoding a PostgreSQL server I am trying to connect to sends back messages -- before I connect (so client_encoding and the pg_settings table are flat out). I don't think there is a way because you can't get any information without authorizing first. It is also unclear to me how things work in the other direction. When authenticating, what if the user or database name have non-ascii characters. Are they interpreted in the encoding of the server since the client has not established an encoding? John DeSoi, Ph.D. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need some help with pl-pgsql
Adrian Klaver wrote: On Tuesday 23 December 2008 3:36:46 pm Adrian Klaver wrote: Hmmm. A couple of suggestions while I ponder this more. For clarity sake decide on a case scheme. I start getting twitchy when I see pwoid and pWoid refer to the same thing. This depends on case folding being consistent, not something I like to count on. Second you might want to put in a few RAISE NOTICE statements to see what values are being pulled from the db for wo_wipvalue and wo_qtyord and what is being supplied to the WHERE clause for pWoid. Adrian Klaver akla...@comcast.net SELECT postInvTrans( itemsite_id, 'RM', _parentQty, 'W/O', 'WO', _woNumber, '', 'Receive Inventory from Manufacturing', costcat_asset_accnt_id, costcat_wip_accnt_id, _itemlocSeries, true, _TotalCost, pDate::timestamp ) INTO _invhistid Potential issue. I don't see where _invhistid is declared. its declared. i noticed this function has more variables declared than it ever used so i removed them when posting to the list. I did not want anymore confusion so I removed one to many sorry. This function was completely rewritten several months ago along with a allot of other functions so there still allot of clean up to do making notes and removing stuff that does not need to be in the function. I just can't figure out why in this function it can't find the record. This malfunctioning function is called by ForceCloseWo() which goes through the work order completing any outstanding operations, and issuing material so it has to find the record first or it will fail out right. Once all material and operations are marked completed it then calls PostProduction() which moves the items out of WIP tables put the items into Inventory and does all the accounting stuff. So i really can't figure out what it is doing??? Now On the mixed case. i come from the Xbase languages specifically Foxpro which is case insensitive and a typeless language so i have a few really bad habits. I try to get every thing to match but sometimes screw up.
[GENERAL] Merry Xmas and Happy Holidays
Hello Peeps, I'd like to take this opportunity to wish you nice helpful bunch my warm greetings. Hope you have a fantastic time and looking forward to learn more in the new future. Novice tgurl ;-) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] need some help with pl-pgsql
Adrian Klaver wrote:
- "justin" wrote:
Adrian Klaver wrote:
Would help to see the
whole function. Also make sure you did not name one of
the variables the same as a column name, this will confuse plpgsql.
Are you
using the same value for wo_id in the function as in the manual select
statement?
First the funciton has been running for months and never has had a
problem. No changes to the database scheme. Second use variable naming
scheme completely different from column names. _ always is the first
character in variables. p is always the first character in passed
parameters.
Take a look at the screen shot and be in aw as i am
postproduction(pwoid integer, pqty numeric, pbackflush boolean,
pbackflushoperations boolean, pitemlocseries integer, psuuser text,
prnuser text, pdate date)
RETURNS integer AS
$BODY$ DECLARE
_woNumber TEXT;
_itemlocSeries INTEGER;
_parentQty NUMERIC;
_qty NUMERIC;
_TotalCost numeric;
BEGIN
IF (pQty <= 0) THEN
RETURN 0;
END IF;
IF ( ( SELECT wo_status
FROM wo
WHERE (wo_id=pWoid) ) NOT IN ('R','E','I') ) THEN
RETURN -1;
END IF;
--If this is item type Job then we are using the wrong function
SELECT item_type INTO _check
FROM wo, itemsite, item
WHERE ((wo_id=pWoid)
AND (wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (item_type = 'J'));
IF (FOUND) THEN
RAISE EXCEPTION 'Work orders for job items are posted when quantities
are shipped on the associated sales order';
END IF;
SELECT formatWoNumber(pWoid) INTO _woNumber;
SELECT roundQty(item_fractional, pQty) INTO _parentQty
FROM wo,
itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Create the material receipt transaction
IF (pItemlocSeries = 0) THEN
SELECT NEXTVAL('itemloc_series_seq') INTO _itemlocSeries;
ELSE
_itemlocSeries = pItemlocSeries;
END IF;
--Lets get Wips Current total cost
Select (wo_wipvalue/wo_qtyord)
into _TotalCost
from wo
where wo_id = pWoid ;
--Moves WIP into Inventory.
SELECT postInvTrans( itemsite_id,
'RM',
_parentQty,
'W/O',
'WO',
_woNumber,
'',
'Receive Inventory from Manufacturing',
costcat_asset_accnt_id,
costcat_wip_accnt_id,
_itemlocSeries,
true,
_TotalCost,
pDate::timestamp ) INTO _invhistid
FROM wo,
itemsite,
costcat
WHERE ( (wo_itemsite_id=itemsite_id)
AND (itemsite_costcat_id=costcat_id)
AND (wo_id=pWoid) );
-- Increase this W/O's received qty decrease its WIP value
UPDATE wo SET
wo_qtyrcv = (wo_qtyrcv + _parentQty),
wo_wipvalue = (wo_wipvalue - (_TotalCost*pQty))
FROM itemsite,
item
WHERE ((wo_itemsite_id=itemsite_id)
AND (itemsite_item_id=item_id)
AND (wo_id=pWoid));
-- Make sure the W/O is at issue status
UPDATE wo SET
wo_status='I'
WHERE (wo_id=pWoid);
RETURN _itemlocSeries;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION postproduction(integer, numeric, boolean, boolean,
integer, text, text, date) OWNER TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO justin;
GRANT EXECUTE ON FUNCTION postproduction(integer, numeric, boolean,
boolean, integer, text, text, date) TO public;
Hmmm. A couple of suggestions while I ponder this more. For clarity sake decide
on a case scheme. I start getting twitchy when I see pwoid and pWoid refer to
the same thing. This depends on case folding being consistent, not something I
like to count on. Second you might want to put in a few RAISE NOTICE statements
to see what values are being pulled from the db for wo_wipvalue and wo_qtyord
and what is being supplied to the WHERE clause for pWoid.
Adrian Klaver
akla...@comcast.ne
I have taken your idea and made sure all the variables all appear the
same and add raise notice for each portion of the command that is failing.
-
NOTICE: _wipvalue:
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement
NOTICE: _wipqty: 1.
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement
NOTICE: _wipvalue/_wipqty=
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement
NOTICE: pwoid: 6916
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement
NOTICE: TotalCost:
CONTEXT: SQL statement "SELECT postProduction( $1 , $2 , FALSE,
false, 0, Current_User, Current_User, $3 )"
PL/pgSQL function "forceclosewo" line 66 at SQL statement
So it appears that something is causing _wipvalue to get set to NULL
somewhere else in the code. Now when runnin
[GENERAL] How to Import Excel Into PostgreSQL database
Dear All, I using PosgreSql8.1.I want to import Excel into PostgreSQL database. Is there any way to import ? . Please anyone can guide me. I am waiting for your great response. Thanks Venkat.
Re: [GENERAL] How to Import Excel Into PostgreSQL database
Venkat Rao Tammineni escribió: Dear All, I using PosgreSql8.1.I want to import Excel into PostgreSQL database. Is there any way to import ? . Please anyone can guide me. I am waiting for your great response. Thanks Venkat. Try with csv format and think it's going a nothing problem. See http://darkavngr.blogspot.com/2007/06/importar-datos-externos-nuestra-base-de.html for example. -- Regards, Julio Cesar Sánchez González. -- Ahora me he convertido en la muerte, destructora de mundos. Soy la Muerte que se lleva todo, la fuente de las cosas que vendran. www.sistemasyconectividad.com.mxhttp://darkavngr.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to Import Excel Into PostgreSQL database
Hi, Thanks a lot. Regards Venkat. -Original Message- From: Julio Cesar Sánchez González [mailto:know...@sistemasyconectividad.com.mx] Sent: Wednesday, December 24, 2008 1:14 PM To: Venkat Rao Tammineni Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] How to Import Excel Into PostgreSQL database Venkat Rao Tammineni escribió: > > Dear All, > > > > I using PosgreSql8.1.I want to import Excel into PostgreSQL > database. Is there any way to import ? . > > > > Please anyone can guide me. > > > > I am waiting for your great response. > > > > Thanks > > Venkat. > > > Try with csv format and think it's going a nothing problem. See http://darkavngr.blogspot.com/2007/06/importar-datos-externos-nuestra-base-d e.html for example. -- Regards, Julio Cesar Sánchez González. -- Ahora me he convertido en la muerte, destructora de mundos. Soy la Muerte que se lleva todo, la fuente de las cosas que vendran. www.sistemasyconectividad.com.mxhttp://darkavngr.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
