[GENERAL] What may cause - Connection rejected: FATAL: Ident authentication failed for user?
Good morning, My daily data population cronjob(around 1 hour) terminated at the middle and raised the following error this morning: Connection rejected: FATAL: Ident authentication failed for user "schema_owner_name". Could anyone tell me what might cause the problem please? Thanks a lot! -- Lu Ying -- 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] What may cause - Connection rejected: FATAL: Ident authentication failed for user?
On Wed, Apr 22, 2009 at 2:36 PM, Emi Lu wrote: > Good morning, > > My daily data population cronjob(around 1 hour) terminated at the middle and > raised the following error this morning: > > Connection rejected: FATAL: Ident authentication failed for user > "schema_owner_name". > > Could anyone tell me what might cause the problem please? password isn't specified for user, and indent rule doesn't apply to his origin (host it is logging from) . Check hba conf -- 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] What may cause - Connection rejected: FATAL: Ident authentication failed for user?
Check your pg_hba.conf file. What does it look like? The message suggests that your job is trying to connect to the database as the user "schema_owner_name" (or whatever the real user name is), but is actually running as a different unix user. Also, did anyone change the unix user running this particular data population job? John L. Cheng - Original Message > From: Emi Lu > To: pgsql-general@postgresql.org > Sent: Wednesday, April 22, 2009 6:36:44 AM > Subject: [GENERAL] What may cause - Connection rejected: FATAL: Ident > authentication failed for user? > > Good morning, > > My daily data population cronjob(around 1 hour) terminated at the middle and > raised the following error this morning: > > Connection rejected: FATAL: Ident authentication failed for user > "schema_owner_name". > > Could anyone tell me what might cause the problem please? > > > Thanks a lot! > -- Lu Ying > > -- 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] Yet another "drop table vs delete" question
Alvaro Herrera wrote: Try TRUNCATE. That leaves the less garbage behind and takes the less time. A follow up question, as far as I understand it, delete removes entries in the fsm, so vacuum has to clean it all up when performing a delete, is this approximately correct? what happens with truncate? does it remove everything so that vacuum has almost no work to do or is it approximately as much work either way? regards thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Reg: Logging log_min_duration_statement
I set log_min_duration_statement to 1 and restart the postgres. But when i check the tail to log i am getting all queries. please tell is it bug ? i have log_statement = all . is there any relation between this and min_duaration? Arvind S * "Many of lifes failure are people who did not realize how close they were to success when they gave up." -Thomas Edison*
Re: [GENERAL] Reg: Logging log_min_duration_statement
On Wed, Apr 22, 2009 at 9:16 AM, S Arvind wrote: > I set log_min_duration_statement to 1 and restart the postgres. But > when > i check the tail to log i am getting all queries. please tell is it bug ? > i have log_statement = all . is there any relation between this and > min_duaration? I think we covered this in IRC, but you should set log_statement=none and leave log_min_duration_statement as is. --Scott
Re: [GENERAL] Reg: Logging log_min_duration_statement
In response to S Arvind : > I set log_min_duration_statement to 1 and restart the postgres. But when > i check the tail to log i am getting all queries. please tell is it bug ? > i have log_statement = all . is there any relation between this and > min_duaration? log_statement = all loggs _all_ statements, but log_min_duration_statement loggs only statements with a duraton time more than the parameter (ms). Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.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] Help request to improve function performance
Dear members of the list, I have a function which returns a custom type, that has only two fields, each of them being varchar arrays. The reason that I have written this function is that I have a table basically with the following structure (with simplified column names) name_col1 name_col2 sessionId value1 value3 id1 value2 value2 id1 value4 value4 id1 value7 value4 id2 value2 value2 id2 value4 value4 id2 value1 value5 id3 So mutliple rows are bound together with sessionIds, and I need to get back all rows with a query, grouped by sessionID. However, group by sql statement does not solve my problem, since I get back a lot of rows, which I have to group into objects again in my application. What I need is a way to return all rows having the same sessionId as a single row. Of course this is not possible with this table, so I've created a custom type, which has array type columns. The following function gets all rows that belongs to a patient, and for each session id, it inserts rows with that session id into array fields of the custom type. The problem is, it is very slow! Getting back all the rows with a select takes 360 ms, while getting back the results of this function takes 50 seconds! Is there any way I can make the following function faster, or any other methods you can recommend to do what I'm trying to do? I am trying to avoid hundreds of calls to db, or grouping query results in my middleware application. Here comes the function, and your help will be much appreciated. Best Regards Seref CREATE or REPLACE FUNCTION getNodeContainers( context_Id varchar) RETURNS setof NodesContainer AS $$ DECLARE archetype_data_row app.archetype_data%ROWTYPE; archetype_data_row_main app.archetype_data%ROWTYPE; nodescontainervar NodesContainer%ROWTYPE; session_Id varchar; indexVar integer := 0; BEGIN CREATE TEMP TABLE all_rows_of_patient AS select * from app.archetype_data WHERE app.archetype_data.context_id = context_Id; FOR session_Id IN SELECT distinct(all_rows_of_patient.session_id) from all_rows_of_patient LOOP -- do the following for each session_ID indexVar := 0; FOR archetype_data_row IN --select rows that belong to this session ID SELECT * from all_rows_of_patient WHERE all_rows_of_patient.session_id = session_Id and all_rows_of_patient.context_id = context_Id LOOP nodescontainervar.name[indexVar] := archetype_data_row.name; nodescontainervar.context_Id[indexVar] := archetype_data_row.context_Id; indexVar := indexVar + 1; END LOOP; return NEXT nodescontainervar; END LOOP; drop table all_rows_of_patient; return; END; $$ LANGUAGE 'plpgsql'; -- View this message in context: http://www.nabble.com/Help-request-to-improve-function-performance-tp23175540p23175540.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Dynamic SQL in Function
If I have built a dynamic sql statement in a function, how do i return it as a ref cursor? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] From 8.1 to 8.3
Our company wants to move from 8,1 to 8.3 latest. In irc they told me to check realse notes for issues while upgrading. But there are lots of release notesss. Can anyone tell some most noticable change or place-of-error while upgrading? Arvind S * "Many of lifes failure are people who did not realize how close they were to success when they gave up." -Thomas Edison*
Re: [GENERAL] From 8.1 to 8.3
S Arvind escribió: > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to > check realse notes for issues while upgrading. But there are lots of release > notesss. Can anyone tell some most noticable change or place-of-error while > upgrading? If you're too lazy to read them, we're too lazy to summarise them for you ... (Luckily for everybody, Bruce and Tom were NOT lazy enough to write them in the first place.) The meat of what you need to know is in the 8.2.0 and 8.3.0 notes, the "incompatibilities" section anyhow. -- 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] From 8.1 to 8.3
On Wed, 2009-04-22 at 22:12 +0530, S Arvind wrote: > Our company wants to move from 8,1 to 8.3 latest. In irc they told me > to check realse notes for issues while upgrading. But there are lots > of release notesss. Can anyone tell some most noticable change or > place-of-error while upgrading? one I had to solve was the need for explicit casting in SQL queries that used numeric comparison of REAL with TEXT... yes... this used to be possible on 8.1 and is no longer on 8.3 so if your applications have such queries maybe you will bumo into some problems I used stuff like this: cast(instantin as numeric) cheers Joao > > Arvind S > > > "Many of lifes failure are people who did not realize how close they > were to success when they gave up." > -Thomas Edison -- 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] From 8.1 to 8.3
On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote: > S Arvind escribió: > > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to > > check realse notes for issues while upgrading. But there are lots of release > > notesss. Can anyone tell some most noticable change or place-of-error while > > upgrading? > > If you're too lazy to read them, we're too lazy to summarise them for > you ... > And to actually be helpful, the number one issue people see to run into is this one: Non-character data types are no longer automatically cast to TEXT (Peter, Tom) Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text, for most (though not all) built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types. For example, these expressions formerly worked: substr(current_date, 1, 4) 23 LIKE '2%' but will now draw "function does not exist" and "operator does not exist" errors respectively. Use an explicit cast instead: substr(current_date::text, 1, 4) 23::text LIKE '2%' (Of course, you can use the more verbose CAST() syntax too.) The reason for the change is that these automatic casts too often caused surprising behavior. An example is that in previous releases, this expression was accepted but did not do what was expected: current_date < 2017-11-17 This is actually comparing a date to an integer, which should be (and now is) rejected — but in the presence of automatic casts both sides were cast to text and a textual comparison was done, because the text < text operator was able to match the expression when no other < operator could. Types char(n) and varchar(n) still cast to text automatically. Also, automatic casting to text still works for inputs to the concatenation (||) operator, so long as least one input is a character-string type. However Alvaro is right. You should read the entire incompatibilities section, and of course test. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Error installing Postgres
When trying to upgrade Postgres I got this message: The existing data directory (Date/time settings: floating -point numbers) is not compatible with this server (Date/Time setting: 64-bit integers) I saw a few posts about this but I'm still not sure how to fix it. I think one of them said I need to completely remove Postgres and do a dump and restore. Is that the only way? Christine -- 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] Error installing Postgres
On 22/04/2009 16:57, Christine Penner wrote: > The existing data directory (Date/time settings: floating -point > numbers) is not compatible with this server (Date/Time setting: 64-bit > integers) > > I saw a few posts about this but I'm still not sure how to fix it. I > think one of them said I need to completely remove Postgres and do a > dump and restore. Is that the only way? It sounds as if you're trying to get a later-version server to use an earlier-version data directory - this won't work. Between major versions of PostgreSQL (8.2 -> 8.3 for example) you *have* to do a dump/restore - it says this in the docs and (I think) in the release notes. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Error installing Postgres
On Wed, 2009-04-22 at 18:09 +0100, Raymond O'Donnell wrote: > On 22/04/2009 16:57, Christine Penner wrote: > > > The existing data directory (Date/time settings: floating -point > > numbers) is not compatible with this server (Date/Time setting: 64-bit > > integers) > > > > I saw a few posts about this but I'm still not sure how to fix it. I > > think one of them said I need to completely remove Postgres and do a > > dump and restore. Is that the only way? > > It sounds as if you're trying to get a later-version server to use an > earlier-version data directory - this won't work. Actually the error is about whether or not the server was compiled with integer datetime support. There may also be an upgrade error here as well if she is trying to use 8.4Beta because 8.4 finally fixes the long standing mistake of not using integer datetime support by default. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Error installing Postgres
I am upgrading from 8.3.4 to 8.3.7. That's why I'm confused. The notes said I wouldn't have to do that. 8.3.4 was the original install. Christine At 10:09 AM 22/04/2009, you wrote: On 22/04/2009 16:57, Christine Penner wrote: > The existing data directory (Date/time settings: floating -point > numbers) is not compatible with this server (Date/Time setting: 64-bit > integers) > > I saw a few posts about this but I'm still not sure how to fix it. I > think one of them said I need to completely remove Postgres and do a > dump and restore. Is that the only way? It sounds as if you're trying to get a later-version server to use an earlier-version data directory - this won't work. Between major versions of PostgreSQL (8.2 -> 8.3 for example) you *have* to do a dump/restore - it says this in the docs and (I think) in the release notes. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Error installing Postgres
On 22/04/2009 18:12, Joshua D. Drake wrote: > On Wed, 2009-04-22 at 18:09 +0100, Raymond O'Donnell wrote: >> It sounds as if you're trying to get a later-version server to use an >> earlier-version data directory - this won't work. > > Actually the error is about whether or not the server was compiled with > integer datetime support. There may also be an upgrade error here as > well if she is trying to use 8.4Beta because 8.4 finally fixes the long > standing mistake of not using integer datetime support by default. Ah - ok. I stand corrected. :-) Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] What may cause - Connection rejected: FATAL: Ident authentication failed for user?
On Wed, Apr 22, 2009 at 7:36 AM, Emi Lu wrote: > Good morning, > > My daily data population cronjob(around 1 hour) terminated at the middle and > raised the following error this morning: > > Connection rejected: FATAL: Ident authentication failed for user > "schema_owner_name". > > Could anyone tell me what might cause the problem please? This does not seem logical. ident authentication doesn't just suddenly get turned on by accident. Are you sure these two things are related? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Testing ... please reply
Could someone reply to this email? I am testing my subscription; joined over 2 months ago, but never get any response to questions Thanks! Atul -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Joshua D. Drake Sent: Wednesday, April 22, 2009 12:56 PM To: Alvaro Herrera Cc: S Arvind; pgsql-general@postgresql.org Subject: Re: [GENERAL] From 8.1 to 8.3 On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote: > S Arvind escribió: > > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to > > check realse notes for issues while upgrading. But there are lots of release > > notesss. Can anyone tell some most noticable change or place-of-error while > > upgrading? > > If you're too lazy to read them, we're too lazy to summarise them for > you ... > And to actually be helpful, the number one issue people see to run into is this one: Non-character data types are no longer automatically cast to TEXT (Peter, Tom) Previously, if a non-character value was supplied to an operator or function that requires text input, it was automatically cast to text, for most (though not all) built-in data types. This no longer happens: an explicit cast to text is now required for all non-character-string types. For example, these expressions formerly worked: substr(current_date, 1, 4) 23 LIKE '2%' but will now draw "function does not exist" and "operator does not exist" errors respectively. Use an explicit cast instead: substr(current_date::text, 1, 4) 23::text LIKE '2%' (Of course, you can use the more verbose CAST() syntax too.) The reason for the change is that these automatic casts too often caused surprising behavior. An example is that in previous releases, this expression was accepted but did not do what was expected: current_date < 2017-11-17 This is actually comparing a date to an integer, which should be (and now is) rejected — but in the presence of automatic casts both sides were cast to text and a textual comparison was done, because the text < text operator was able to match the expression when no other < operator could. Types char(n) and varchar(n) still cast to text automatically. Also, automatic casting to text still works for inputs to the concatenation (||) operator, so long as least one input is a character-string type. However Alvaro is right. You should read the entire incompatibilities section, and of course test. Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.287 / Virus Database: 270.12.1/2070 - Release Date: 04/22/09 08:49:00 -- 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] trouble with to_char('L')
On Wed, Apr 22, 2009 at 2:13 AM, Alvaro Herrera wrote: > Ouch ... I thought that was the way that Windows designated UTF8 > locales, but maybe I am wrong. Ok, now I found out that Windows doesn't support locales with encoding using more than two bytes per character and initdb falls back to 1252. http://msdn.microsoft.com/en-us/library/x99tb11d.aspx I guess I'll have to manage with win1252 encoded dbs for the moment. Thanks for the answers! Mikko -- 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] Testing ... please reply
On 22/04/2009 18:21, Atul Chojar wrote: > Could someone reply to this email? I am testing my subscription; > joined over 2 months ago, but never get any response to questions Receiving you loud and clear! Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Error installing Postgres
Windows XP pro. I went to the postgres web site, downloads, clicked on the binary package and selected windows. It gave me a file called Postgresql-8.3.7-1-windows.exe When I run that I hit next for the postgres directory, then after hitting next for the data directory I get the error. I kept all defaults for directories because I want to keep my existing data etc. Christine At 10:24 AM 22/04/2009, you wrote: On Wed, 2009-04-22 at 10:13 -0700, Christine Penner wrote: > I am upgrading from 8.3.4 to 8.3.7. That's why I'm confused. The > notes said I wouldn't have to do that. 8.3.4 was the original install. What OS are you running? How did you go about upgrading? Debian/Ubuntu will use --integer-datetimes by default. If the machine was once compiled from source you could see the problem. RedHat/Cent/Fedora has long used the incorrect default of floating based timestamps. If you downloaded the wrong package from pgsqlrpms then you could run into the error (pgsqlrpms has both --integer-datetimes and floating based packages (for compatibility). Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Error installing Postgres
On Wed, 2009-04-22 at 10:31 -0700, Christine Penner wrote: > Windows XP pro. I went to the postgres web site, downloads, clicked > on the binary package and selected windows. It gave me a file called > Postgresql-8.3.7-1-windows.exe > > When I run that I hit next for the postgres directory, then after > hitting next for the data directory I get the error. I kept all > defaults for directories because I want to keep my existing data etc. Huh, I wonder when the windows package changed its defaults. O.k. so what I would do is this: 1. Uninstall 8.3.7 2. Reinstall 8.3.4 3. Take a backup 4. Uninstall 8.3.4 5. Reinstall 8.3.7 in a new location 6. Restore backup 7. Test/Verify 7a. If o.k. remove 8.3.4 location 7b. If not o.k. check back Sincerely, Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Error installing Postgres
On Wed, 2009-04-22 at 10:13 -0700, Christine Penner wrote: > I am upgrading from 8.3.4 to 8.3.7. That's why I'm confused. The > notes said I wouldn't have to do that. 8.3.4 was the original install. What OS are you running? How did you go about upgrading? Debian/Ubuntu will use --integer-datetimes by default. If the machine was once compiled from source you could see the problem. RedHat/Cent/Fedora has long used the incorrect default of floating based timestamps. If you downloaded the wrong package from pgsqlrpms then you could run into the error (pgsqlrpms has both --integer-datetimes and floating based packages (for compatibility). Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- 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] Testing ... please reply
Coming loud and clear ! joao On Wed, 2009-04-22 at 13:21 -0400, Atul Chojar wrote: > Could someone reply to this email? I am testing my subscription; joined over > 2 months ago, but never get any response to questions > > Thanks! > Atul > > > > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Joshua D. Drake > Sent: Wednesday, April 22, 2009 12:56 PM > To: Alvaro Herrera > Cc: S Arvind; pgsql-general@postgresql.org > Subject: Re: [GENERAL] From 8.1 to 8.3 > > On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote: > > S Arvind escribió: > > > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to > > > check realse notes for issues while upgrading. But there are lots of > > > release > > > notesss. Can anyone tell some most noticable change or place-of-error > > > while > > > upgrading? > > > > If you're too lazy to read them, we're too lazy to summarise them for > > you ... > > > > And to actually be helpful, the number one issue people see to run into > is this one: > > Non-character data types are no longer automatically cast to > TEXT (Peter, Tom) > > Previously, if a non-character value was supplied to an operator > or function that requires text input, it was automatically cast > to text, for most (though not all) built-in data types. This no > longer happens: an explicit cast to text is now required for all > non-character-string types. For example, these expressions > formerly worked: > > substr(current_date, 1, 4) > 23 LIKE '2%' > > but will now draw "function does not exist" and "operator does > not exist" errors respectively. Use an explicit cast instead: > > substr(current_date::text, 1, 4) > 23::text LIKE '2%' > > (Of course, you can use the more verbose CAST() syntax too.) The > reason for the change is that these automatic casts too often > caused surprising behavior. An example is that in previous > releases, this expression was accepted but did not do what was > expected: > > current_date < 2017-11-17 > > This is actually comparing a date to an integer, which should be > (and now is) rejected — but in the presence of automatic casts > both sides were cast to text and a textual comparison was done, > because the text < text operator was able to match the > expression when no other < operator could. > > Types char(n) and varchar(n) still cast to text automatically. > Also, automatic casting to text still works for inputs to the > concatenation (||) operator, so long as least one input is a > character-string type. > > However Alvaro is right. You should read the entire incompatibilities > section, and of course test. > > Sincerely, > > Joshua D. Drake > > -- > PostgreSQL - XMPP: jdr...@jabber.postgresql.org >Consulting, Development, Support, Training >503-667-4564 - http://www.commandprompt.com/ >The PostgreSQL Company, serving since 1997 > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 8.5.287 / Virus Database: 270.12.1/2070 - Release Date: 04/22/09 > 08:49:00 > > -- 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] trouble with to_char('L')
Mikko escribió: > On Wed, Apr 22, 2009 at 2:13 AM, Alvaro Herrera > wrote: > > Ouch ... I thought that was the way that Windows designated UTF8 > > locales, but maybe I am wrong. > > Ok, now I found out that Windows doesn't support locales with encoding > using more than two bytes per character and initdb falls back to 1252. > > http://msdn.microsoft.com/en-us/library/x99tb11d.aspx Hmm. Does this imply that we shouldn't allow UTF8 database on Windows at all? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] Error installing Postgres
Joshua D. Drake wrote on 22.04.2009 19:34: Huh, I wonder when the windows package changed its defaults. Could it be that Christine initially installed the pginstaller version, and now downloaded the EnterpriseDB installer? And EnterpriseDB compiles with a different default setting? As far as I recall the .exe is the EnterpriseDB One-Click-Installer. The pginstaller always comes as a zip file Christine: what kind of installer did you use for the initial installation? Thomas -- 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] Error installing Postgres
On 22/04/2009 19:00, Thomas Kellerer wrote: > Could it be that Christine initially installed the pginstaller version, > and now downloaded the EnterpriseDB installer? And EnterpriseDB compiles > with a different default setting? > > As far as I recall the .exe is the EnterpriseDB One-Click-Installer. The > pginstaller always comes as a zip file That's probably a fair guess - from memory, the pginstaller uses a different naming convention too. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Error installing Postgres
It was a zip file with an msi installer in it. I tried to find a similar one for the update but all I could find was the one click installer. Christine At 11:00 AM 22/04/2009, you wrote: Joshua D. Drake wrote on 22.04.2009 19:34: Huh, I wonder when the windows package changed its defaults. Could it be that Christine initially installed the pginstaller version, and now downloaded the EnterpriseDB installer? And EnterpriseDB compiles with a different default setting? As far as I recall the .exe is the EnterpriseDB One-Click-Installer. The pginstaller always comes as a zip file Christine: what kind of installer did you use for the initial installation? Thomas -- 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] Error installing Postgres
Christine Penner writes: > Windows XP pro. I went to the postgres web site, downloads, clicked > on the binary package and selected windows. It gave me a file called > Postgresql-8.3.7-1-windows.exe There are different people distributing Postgres-for-Windows with different build options. You need to make sure you get upgrade packages from the same place you got the original install, else you will possibly hit this type of problem. 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] Error installing Postgres
"Joshua D. Drake" writes: > RedHat/Cent/Fedora has long used the incorrect default of floating based > timestamps. Josh, you're being extremely unhelpful by presenting the problem in this narrow minded "correct" vs "incorrect" way. I'm going to go on the record now that if Bruce gets pg_migrator working for 8.4 (which he seems to think might still happen) it's entirely possible that the Red Hat packages won't *ever* switch away from FP timestamps; or at least not till the next upgrade cycle that pg_migrator doesn't work for. It won't be worth the pain of forcing a dump/reload just to change the representation of timestamps. 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] Error installing Postgres
On 22/04/2009 19:05, Christine Penner wrote: > It was a zip file with an msi installer in it. I tried to find a similar > one for the update but all I could find was the one click installer. The initial installation sounds like it was the pgInstaller, so. It would be worth trying again with the same installer: http://www.postgresql.org/download/windows The pgInstaller is listed underneath the one-click installer. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] thanks for the "testing" replies ; now my first question - Logs say update done but not actually done or committed into database
Thanks to everyone who replied to the test email!
Now for my real question:-
We are facing a strange problem in our 8.2.7 database.
There is a bash shell script that does:-
sql=”select distinct to_char(date_of_issue, ‘MM’) from
yan.int_prod_s_master order by 1;”
MM=`/usr/local/pgsql/bin/psql -U postgres -h payday -d sandbox -t -c “$sql”`
for x in $MM
do
$scriptdir/USCS_production_updates.sh $x
>>$logdir/USCS_production_updates.log 2>&1
done
The $scriptdir/USCS_production_updates.sh script does updates like:-
MM=$1
database=”us_audit”
db_user=”postgres”
db_host=”nutrageous”
psql_cmd=”/usr/local/pgsql/bin/psql -U ${db_user} -h ${db_host} -d ${database}
-e “;
sql=”
update int_prod_manual_price_${MM} mp
set …
from int_prod_s_master_${MM} sm
where …
and not exists ( select 1 from int_prod_stop_${MM} where …)
and …;
“;
$psql_cmd -c “$SQL”
When these scripts run, the USCS_production_updates.log shows the correct
update statement, with values of MM substituted in the table names, and
message like “UPDATE 1025” from postgres indicating 1025 rows got updated.
However, none of these updates actually get applied in the database. Auto
commit is on in the database, but it seems the updates do not get committed.
The system logs also show no errors.
Any ideas why above update is not working?
Thanks!
Atul
-Original Message-
From: pgsql-general-ow...@postgresql.org
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Joao Ferreira
Sent: Wednesday, April 22, 2009 1:34 PM
To: Atul Chojar
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Testing ... please reply
Coming loud and clear !
joao
On Wed, 2009-04-22 at 13:21 -0400, Atul Chojar wrote:
> Could someone reply to this email? I am testing my subscription; joined over
> 2 months ago, but never get any response to questions
>
> Thanks!
> Atul
>
>
>
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Joshua D. Drake
> Sent: Wednesday, April 22, 2009 12:56 PM
> To: Alvaro Herrera
> Cc: S Arvind; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] From 8.1 to 8.3
>
> On Wed, 2009-04-22 at 12:49 -0400, Alvaro Herrera wrote:
> > S Arvind escribió:
> > > Our company wants to move from 8,1 to 8.3 latest. In irc they told me to
> > > check realse notes for issues while upgrading. But there are lots of
> > > release
> > > notesss. Can anyone tell some most noticable change or place-of-error
> > > while
> > > upgrading?
> >
> > If you're too lazy to read them, we're too lazy to summarise them for
> > you ...
> >
>
> And to actually be helpful, the number one issue people see to run into
> is this one:
>
> Non-character data types are no longer automatically cast to
> TEXT (Peter, Tom)
>
> Previously, if a non-character value was supplied to an operator
> or function that requires text input, it was automatically cast
> to text, for most (though not all) built-in data types. This no
> longer happens: an explicit cast to text is now required for all
> non-character-string types. For example, these expressions
> formerly worked:
>
> substr(current_date, 1, 4)
> 23 LIKE '2%'
>
> but will now draw "function does not exist" and "operator does
> not exist" errors respectively. Use an explicit cast instead:
>
> substr(current_date::text, 1, 4)
> 23::text LIKE '2%'
>
> (Of course, you can use the more verbose CAST() syntax too.) The
> reason for the change is that these automatic casts too often
> caused surprising behavior. An example is that in previous
> releases, this expression was accepted but did not do what was
> expected:
>
> current_date < 2017-11-17
>
> This is actually comparing a date to an integer, which should be
> (and now is) rejected — but in the presence of automatic casts
> both sides were cast to text and a textual comparison was done,
> because the text < text operator was able to match the
> expression when no other < operator could.
>
> Types char(n) and varchar(n) still cast to text automatically.
> Also, automatic casting to text still works for inputs to the
> concatenation (||) operator, so long as least one input is a
> character-string type.
>
> However Alvaro is right. You should read the entire incompatibilities
> section, and of course test.
>
> Sincerely,
>
> Joshua D. Drake
>
> --
> PostgreSQL - XMPP: jdr...@jabber.postgresql.org
>Consulting, Development, Support, Training
>503-667-4564 - http://www.commandprompt.com/
>The PostgreSQL Company, serving since 1997
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes
Re: [GENERAL] Error installing Postgres
The link below takes me to the same place I got this installer I'm having problems with. I am pretty sure I got the original installer from the Postgres web site just like I just did. Christine At 11:14 AM 22/04/2009, you wrote: On 22/04/2009 19:05, Christine Penner wrote: > It was a zip file with an msi installer in it. I tried to find a similar > one for the update but all I could find was the one click installer. The initial installation sounds like it was the pgInstaller, so. It would be worth trying again with the same installer: http://www.postgresql.org/download/windows The pgInstaller is listed underneath the one-click installer. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Error installing Postgres
Tom Lane wrote: > "Joshua D. Drake" writes: > > RedHat/Cent/Fedora has long used the incorrect default of floating based > > timestamps. > > Josh, you're being extremely unhelpful by presenting the problem in > this narrow minded "correct" vs "incorrect" way. > > I'm going to go on the record now that if Bruce gets pg_migrator > working for 8.4 (which he seems to think might still happen) it's Yes, I can confirm I think pg_migrator will work for 8.3->8.4 upgrades; I start testing this week. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Error installing Postgres
On 22/04/2009 19:21, Christine Penner wrote: > The link below takes me to the same place I got this installer I'm > having problems with. I am pretty sure I got the original installer from Well, there are two installers listed there (at least, that's what I see): * One-click installer, on EnterpriseDB's site - postgresql-8.3.7-1-windows.exe * pgInstaller, at http://www.postgresql.org/ftp/binary/v8.3.7/win32/ - postgresql-8.3.7-1.zip. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Error installing Postgres
Looks like that's the one I got last time. They must have had that one list on the main download page when I did the original install. I wouldn't have looked in the ftp section. Also when I looked in the ftp section earlier today, I skipped the binary stuff because the one click installer I got was called a binary package. I think that will work for me. Thanks Christine At 11:27 AM 22/04/2009, you wrote: On 22/04/2009 19:21, Christine Penner wrote: > The link below takes me to the same place I got this installer I'm > having problems with. I am pretty sure I got the original installer from Well, there are two installers listed there (at least, that's what I see): * One-click installer, on EnterpriseDB's site - postgresql-8.3.7-1-windows.exe * pgInstaller, at http://www.postgresql.org/ftp/binary/v8.3.7/win32/ - postgresql-8.3.7-1.zip. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] trouble with to_char('L')
Alvaro Herrera writes: > Does this imply that we shouldn't allow UTF8 database on Windows at all? That would be pretty unfortunate :-( I think what this suggests is that there probably needs to be some encoding conversion logic near the places we examine localeconv() output. 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] Error installing Postgres
Raymond O'Donnell wrote on 22.04.2009 20:14: On 22/04/2009 19:05, Christine Penner wrote: It was a zip file with an msi installer in it. I tried to find a similar one for the update but all I could find was the one click installer. The initial installation sounds like it was the pgInstaller, so. It would be worth trying again with the same installer: http://www.postgresql.org/download/windows The pgInstaller is listed underneath the one-click installer. I still wonder why the one-click installer is so much more prominent than the pginstaller. All people who downloaded PG because I recommended it, downloaded the one-click installer because that "was the one on the download page" Thomas -- 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] Dynamic SQL in Function
On Wed, Apr 22, 2009 at 12:29 PM, wrote:
> If I have built a dynamic sql statement in a function, how do i return it
> as a ref cursor?
CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS
$$
BEGIN
OPEN _ref FOR execute 'SELECT * from foo';
RETURN _ref;
END;
$$ LANGUAGE plpgsql;
BEGIN;
SELECT reffunc('funccursor');
FETCH ALL IN funccursor;
COMMIT;
--
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] Error installing Postgres
Bruce Momjian wrote on 22.04.2009 20:26: Yes, I can confirm I think pg_migrator will work for 8.3->8.4 upgrades; I start testing this week. This is pretty good news, cool. Will there be Windows binaries for the pg_migrator once that 8.4 ships? Thomas -- 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] Dynamic SQL in Function
n Wed, Apr 22, 2009 at 2:54 PM, Merlin Moncure wrote: > On Wed, Apr 22, 2009 at 12:29 PM, wrote: >> If I have built a dynamic sql statement in a function, how do i return it >> as a ref cursor? > > CREATE FUNCTION reffunc(_ref) RETURNS refcursor AS oops CREATE FUNCTION reffunc(_ref refcursor) RETURNS refcursor AS merlin -- 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] Error installing Postgres
On Wed, Apr 22, 2009 at 7:54 PM, Thomas Kellerer wrote: > I still wonder why the one-click installer is so much more prominent than > the pginstaller. Because it was designed to remove many of the complexities of the MSI installer that most users don't need and many don't understand if they're new to PostgreSQL. And it's not 'much more prominent', it's just listed above. There's even a paragraph at the top of the page explaining the differences between the two and the target audience for each. The trick is to read the text, or if you must blindly click on the first link you'll get the most simple package :-). -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] Error installing Postgres
Thomas Kellerer wrote: > Bruce Momjian wrote on 22.04.2009 20:26: > > Yes, I can confirm I think pg_migrator will work for 8.3->8.4 upgrades; > > I start testing this week. > > > This is pretty good news, cool. > > Will there be Windows binaries for the pg_migrator once that 8.4 ships? Uh, no idea on that one but I think we will know in the next few weeks. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] thanks for the "testing" replies ; now my first question - Logs say update done but not actually done or committed into database
2009/4/22 Atul Chojar
>
> We are facing a strange problem in our 8.2.7 database.
>
> There is a bash shell script that does:-
>
> sql=”select distinct to_char(date_of_issue, ‘MM’) from
> yan.int_prod_s_master order by 1;”
> MM=`/usr/local/pgsql/bin/psql -U postgres -h payday -d sandbox -t -c
> “$sql”`
> for x in $MM
> do
> $scriptdir/USCS_production_updates.sh $x
> >>$logdir/USCS_production_updates.log 2>&1
> done
>
> The $scriptdir/USCS_production_updates.sh script does updates like:-
>
> MM=$1
> database=”us_audit”
> db_user=”postgres”
> db_host=”nutrageous”
> psql_cmd=”/usr/local/pgsql/bin/psql -U ${db_user} -h ${db_host} -d
> ${database} -e “;
> sql=”
> update int_prod_manual_price_${MM} mp
> set …
> from int_prod_s_master_${MM} sm
> where …
> and not exists ( select 1 from int_prod_stop_${MM} where …)
> and …;
> “;
> $psql_cmd -c “$SQL”
>
> When these scripts run, the USCS_production_updates.log shows the correct
> update statement, with values of MM substituted in the table names, and
> message like “UPDATE 1025” from postgres indicating 1025 rows got updated.
>
> However, none of these updates actually get applied in the database. Auto
> commit is on in the database, but it seems the updates do not get committed.
>
> The system logs also show no errors.
>
> Any ideas why above update is not working?
>
>
hi,
1. if you run the very same UPDATE manually, does it work?
2. do you have any triggers on these tables? it's possible that they prevent
updates from happening.
3. if you have table with same name but in different schema, and specific
search_path, this could be a reason too.
HTH
PS. you don't have to post such questions to list owner :)
--
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/
Re: [GENERAL] Help request to improve function performance
2009/4/22 sarikan > > Dear members of the list, > I have a function which returns a custom type, that has only two fields, > each of them being varchar arrays. > The reason that I have written this function is that I have a table > basically with the following structure (with simplified column names) > > name_col1 name_col2 sessionId > value1 value3 id1 > value2 value2 id1 > value4 value4 id1 > value7 value4 id2 > value2 value2 id2 > value4 value4 id2 > value1 value5 id3 > Why not post your REAL schema? It would make life easier, both for you and for people trying to help. > > So mutliple rows are bound together with sessionIds, and I need to get back > all rows with a query, grouped by sessionID. However, group by sql > statement > does not solve my problem, since I get back a lot of rows, which I have to > group into objects again in my application. What I need is a way to return > all rows having the same sessionId as a single row. Of course this is not > possible with this table, so I've created a custom type, which has array > type columns. The following function gets all rows that belongs to a > patient, and for each session id, it inserts rows with that session id into > array fields of the custom type. > The problem is, it is very slow! Getting back all the rows with a select > takes 360 ms, while getting back the results of this function takes 50 > seconds! Is there any way I can make the following function faster, or any > other methods you can recommend to do what I'm trying to do? I am trying > to > avoid hundreds of calls to db, or grouping query results in my middleware > application. Here comes the function, and your help will be much > appreciated. > > Best Regards > Seref > > (below code edited to be more readable; logic unchanged) > > CREATE or REPLACE FUNCTION getNodeContainers( context_Id varchar) RETURNS > setof NodesContainer AS > $$ > DECLARE > archetype_data_row app.archetype_data; > archetype_data_row_main app.archetype_data; > nodescontainervar NodesContainer; > session_Id varchar; > indexVar integer := 0; > BEGIN >CREATE TEMP TABLE all_rows_of_patient AS select * from > app.archetype_data >WHERE context_id = context_Id; >FOR session_Id IN > SELECT distinct session_id from all_rows_of_patient >LOOP -- do the following for each session_ID >indexVar := 0; >FOR archetype_data_row IN --select rows that belong to this session > ID >SELECT * from all_rows_of_patient >WHERE session_id = session_Id and context_id = context_Id >LOOP >nodescontainervar.name[indexVar] := archetype_data_row.name > ; >nodescontainervar.context_Id[indexVar] := > archetype_data_row.context_Id; >indexVar := indexVar + 1; >END LOOP; >return NEXT nodescontainervar; >END LOOP; >drop table all_rows_of_patient; >return; > END; > $$ LANGUAGE 'plpgsql'; > > please read above code - thats what postgres actually executes. column names have precedence before variable names in name resolution. conditions like WHERE context_id = context_Id; WHERE session_id = session_Id and context_id = context_Id are obviously no-op conditions, not what you really want. I hope now it's clear now why this function has long execution time :) some other remarks: 1) you use temp tables inside a function, which is rather bad (search archives for explanation). try to avoid it. 2) usage of indexvar is not needed - there are array operators and functions 3) if you get rid of temp table, this function could be marked as STABLE,which will prevent penalty in some strange situations 4) mark your function as STRICT, which will save some CPU cycles when someone calls it on null input HTH. -- Filip Rembiałkowski JID,mailto:filip.rembialkow...@gmail.com http://filip.rembialkowski.net/
Re: [GENERAL] trouble with to_char('L')
Tom Lane wrote:
> Alvaro Herrera writes:
>> Does this imply that we shouldn't allow UTF8 database on Windows at all?
>
> That would be pretty unfortunate :-(
>
> I think what this suggests is that there probably needs to be some
> encoding conversion logic near the places we examine localeconv()
> output.
Attached is a patch to the current CVS.
It uses a similar way like LC_TIME stuff does.
regards,
Hiroshi Inoue
Index: pg_locale.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/pg_locale.c,v
retrieving revision 1.49
diff -c -c -r1.49 pg_locale.c
*** pg_locale.c 1 Apr 2009 09:17:32 - 1.49
--- pg_locale.c 22 Apr 2009 21:08:33 -
***
*** 386,391
--- 386,449
free(s->positive_sign);
}
+ #ifdefWIN32
+ #define MAX_BYTES_PER_CHARACTER 4
+ static char *dbstr_win32(bool matchenc, const char *str)
+ {
+ int encoding = GetDatabaseEncoding();
+ boolis_ascii = true;
+ size_t len, ilen, wclen, dstlen;
+ wchar_t *wbuf;
+ char*dst, *ibuf;
+
+ if (matchenc)
+ return strdup(str);
+ /* Is the str an ascii string ? */
+ for (ibuf = str; *ibuf; ibuf++)
+ {
+ if (!isascii(*ibuf))
+ {
+ is_ascii = false;
+ break;
+ }
+ }
+ /* Simply returns the strdup()ed ascii string */
+ if (is_ascii)
+ return strdup(str);
+
+ ilen = strlen(str) + 1;
+ wclen = ilen * sizeof(wchar_t);
+ wbuf = (wchar_t *) palloc(wclen);
+ len = mbstowcs(wbuf, str, ilen);
+ if (len == -1)
+ elog(ERROR,
+ "could not convert string to Wide characters:error
%lu", GetLastError());
+
+ dstlen = len * MAX_BYTES_PER_CHARACTER + 1;
+ dst = malloc(dstlen);
+
+ len = WideCharToMultiByte(CP_UTF8, 0, wbuf, len, dst, dstlen, NULL,
NULL);
+ pfree(wbuf);
+ if (len == 0)
+ elog(ERROR,
+ "could not convert string to UTF-8:error %lu",
GetLastError());
+
+ dst[len] = '\0';
+ if (encoding != PG_UTF8)
+ {
+ char *convstr = pg_do_encoding_conversion(dst, len, PG_UTF8,
encoding);
+ if (dst != convstr)
+ {
+ strlcpy(dst, convstr, dstlen);
+ pfree(convstr);
+ }
+ }
+
+ return dst;
+ }
+
+ #define strdup(str) dbstr_win32(is_encoding_match, str)
+ #endif /* WIN32 */
/*
* Return the POSIX lconv struct (contains number/money formatting
***
*** 398,403
--- 456,466
struct lconv *extlconv;
char *save_lc_monetary;
char *save_lc_numeric;
+ #ifdefWIN32
+ char *save_lc_ctype = NULL;
+ boollc_ctype_change = false, is_encoding_match;
+ #endif /* WIN32 */
+
/* Did we do it already? */
if (CurrentLocaleConvValid)
***
*** 413,418
--- 476,492
if (save_lc_numeric)
save_lc_numeric = pstrdup(save_lc_numeric);
+ #ifdefWIN32
+ save_lc_ctype = setlocale(LC_CTYPE, NULL);
+ if (save_lc_ctype && stricmp(locale_monetary, save_lc_ctype) != 0)
+ {
+ lc_ctype_change = true;
+ save_lc_ctype = pstrdup(save_lc_ctype);
+ setlocale(LC_CTYPE, locale_monetary);
+ }
+ is_encoding_match = (pg_get_encoding_from_locale(locale_monetary) ==
GetDatabaseEncoding());
+ #endif
+
setlocale(LC_MONETARY, locale_monetary);
setlocale(LC_NUMERIC, locale_numeric);
***
*** 437,442
--- 511,524
CurrentLocaleConv.n_sign_posn = extlconv->n_sign_posn;
/* Try to restore internal settings */
+ #ifdefWIN32
+ #undefstrdup
+ if (lc_ctype_change)
+ {
+ setlocale(LC_CTYPE, save_lc_ctype);
+ pfree(save_lc_ctype);
+ }
+ #endif /* WIN32 */
if (save_lc_monetary)
{
setlocale(LC_MONETARY, save_lc_monetary);
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] how to search for relation by name?
when i try to copy database (into another database), i get "relation does not exist" errors for 'super objects' like sequences. (that is fine since i am using pg_dump, not pg_dumpall) but there is one relation i can't find to recreate in the new database. how can i search database for relation by name (some catalog table)? -- 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 search for relation by name?
Here is the sql
SELECT c.oid,
n.nspname,
c.relname
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relname LIKE ('%dt%')
AND pg_catalog.pg_table_is_visible(c.oid)
replace dt with your sequence name
pg_catalog has the information.
Thanks
Deepak
On Wed, Apr 22, 2009 at 2:36 PM, zach cruise wrote:
> when i try to copy database (into another database), i get "relation
> does not exist" errors for 'super objects' like sequences. (that is
> fine since i am using pg_dump, not pg_dumpall) but there is one
> relation i can't find to recreate in the new database.
>
> how can i search database for relation by name (some catalog table)?
>
> --
> 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] Yet another "drop table vs delete" question
On Apr 22, 2009, at 8:04 AM, Thomas Finneid wrote: Alvaro Herrera wrote: Try TRUNCATE. That leaves the less garbage behind and takes the less time. A follow up question, as far as I understand it, delete removes entries in the fsm, so vacuum has to clean it all up when performing a delete, is this approximately correct? what happens with truncate? does it remove everything so that vacuum has almost no work to do or is it approximately as much work either way? No, DELETE doesn't do anything with the FSM. It simply marks the tuple in the page as deleted (not visible) to transactions with transaction ids larger than the DELETE transaction's commit id (assuming it commits). So, once all transactions with transaction ids lower than that DELETE transaction's commit id it can be considered dead since nothing can see it anymore. VACUUM looks for those dead tuples and adds them to the FSM. INSERTs and UPDATEs (and COPYs) then look to the free space map for a dead tuple first when space is needed for a new tuple before allocating space in hopes of avoiding that space allocation by reusing the dead tuple's space. You're pretty much on with regards to TRUNCATE as it deletes the pages that are allocated to the table (I'm not sure if the TRUNCATE handles clearing out the FSM entries for that table or if VACUUM does when the table is next vacuum'd). Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- 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] Help request to improve function performance
Hi Filip,
First of all: thanks a lot for your kind response. Here is the create script
for my schema:
CREATE TABLE "app"."archetype_data" (
"id" BIGINT NOT NULL,
"context_id" VARCHAR(1000),
"archetype_name" VARCHAR(1000),
"archetype_path" VARCHAR(1000),
"name" VARCHAR(1000),
"value_string" VARCHAR(1000),
"value_int" BIGINT,
"value_double" DOUBLE PRECISION,
"session_id" VARCHAR(1000),
"instance_index" INTEGER,
CONSTRAINT "archetype_data_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
Now, regarding your feedback, here are the points, hoping that you can give
me feedback, and bring me up to speed in the topic, for I've been in the
upper layers of the software world for so long :) Please forgive me for
further questions:
Are you telling me that due to name resolution process, my use of variable
and column names for context_id and session_id are causing problems? I'll
change variable names into names which would be obviously different from
column names.
I used the temp table to speed up the following selects, since the actual
table has more than 9 million rows. after creating the temp table, I am
selecting from 50K rows. Am I wrong about the performance gain here? What
would you suggest instead?
Temp tables forced me to use execute, after hitting a known problem, also
expressed in the faq, is this what you're talking about?
I will be investing serious time into postgresql from now on, and I hope you
can give me couple of useful hints, to ease my way forward :) Looking at the
schema, can you think of a better way to send this result set to a java
based app?
Many thanks again
Kind regards
Seref
2009/4/22 Filip Rembiałkowski
>
>
> 2009/4/22 sarikan
>
>>
>> Dear members of the list,
>> I have a function which returns a custom type, that has only two fields,
>> each of them being varchar arrays.
>> The reason that I have written this function is that I have a table
>> basically with the following structure (with simplified column names)
>>
>> name_col1 name_col2 sessionId
>> value1 value3 id1
>> value2 value2 id1
>> value4 value4 id1
>> value7 value4 id2
>> value2 value2 id2
>> value4 value4 id2
>> value1 value5 id3
>>
>
> Why not post your REAL schema? It would make life easier, both for you and
> for people trying to help.
>
>
>
>>
>> So mutliple rows are bound together with sessionIds, and I need to get
>> back
>> all rows with a query, grouped by sessionID. However, group by sql
>> statement
>> does not solve my problem, since I get back a lot of rows, which I have to
>> group into objects again in my application. What I need is a way to return
>> all rows having the same sessionId as a single row. Of course this is not
>> possible with this table, so I've created a custom type, which has array
>> type columns. The following function gets all rows that belongs to a
>> patient, and for each session id, it inserts rows with that session id
>> into
>> array fields of the custom type.
>> The problem is, it is very slow! Getting back all the rows with a select
>> takes 360 ms, while getting back the results of this function takes 50
>> seconds! Is there any way I can make the following function faster, or any
>> other methods you can recommend to do what I'm trying to do? I am trying
>> to
>> avoid hundreds of calls to db, or grouping query results in my middleware
>> application. Here comes the function, and your help will be much
>> appreciated.
>>
>> Best Regards
>> Seref
>>
>>
> (below code edited to be more readable; logic unchanged)
>
>
>>
>> CREATE or REPLACE FUNCTION getNodeContainers( context_Id varchar) RETURNS
>> setof NodesContainer AS
>> $$
>> DECLARE
>> archetype_data_row app.archetype_data;
>> archetype_data_row_main app.archetype_data;
>> nodescontainervar NodesContainer;
>> session_Id varchar;
>> indexVar integer := 0;
>> BEGIN
>>CREATE TEMP TABLE all_rows_of_patient AS select * from
>> app.archetype_data
>>WHERE context_id = context_Id;
>>FOR session_Id IN
>> SELECT distinct session_id from all_rows_of_patient
>>LOOP -- do the following for each session_ID
>>indexVar := 0;
>>FOR archetype_data_row IN --select rows that belong to this session
>> ID
>>SELECT * from all_rows_of_patient
>>WHERE session_id = session_Id and context_id = context_Id
>>LOOP
>>nodescontainervar.name[indexVar] :=
>> archetype_data_row.name;
>>nodescontainervar.context_Id[indexVar] :=
>> archetype_data_row.context_Id;
>>indexVar := indexVar + 1;
>>END LOOP;
>>return NEXT nodescontainervar;
>>END LOOP;
>>drop table all_rows_of_patient;
>>return;
>> END;
>> $$ LANGUAGE 'plpgsql';
>>
>>
> please read above code - thats what postgres actually executes. column
> names have precedence before variable names in name resolution.
> conditions like
> WHERE context_id = context_Id;
>
[GENERAL] how to revoke multiple users permission from multiple tables at the same time?
how to revoke multiple users permission from multiple tables at the same time? Or in simple is there a way to revoke multiple users grant access from multiple tables under a schema.? I use Revoke below command to execute on each table one by one. revoke SELECT/ALL on testtable from user1; Thanks for taking your time to read this. thanks Deepak
Re: [GENERAL] Help request to improve function performance
you keep everything in varchars, and yet you request improvements in performance. you are a funny guy, ... -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] how to revoke multiple users permission from multiple tables at the same time?
On Wed, Apr 22, 2009 at 4:19 PM, DM wrote:
> how to revoke multiple users permission from multiple tables at the same
> time?
> Or in simple is there a way to revoke multiple users grant access from
> multiple tables under a schema.?
Best way is to NOT grant multiple users permissions, but to grant a
role the permissions and grant that role to users. That way you only
have to revoke persmissions from the role to revoke it from all the
users.
> I use Revoke below command to execute on each table one by one.
> revoke SELECT/ALL on testtable from user1;
Note that you can build a set of revoke commands by using selects and
concatenations if you need them. Something like this (use psql -E to
see the queries \ commands invoke in psql)
SELECT 'revoke all from somename on '||n.nspname||'.'|| c.relname ||';'
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','S','')
AND n.nspname <> 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1;
?column?
--
revoke all from somename on public.colony;
revoke all from somename on public.delegate;
revoke all from somename on public.jt1;
revoke all from somename on public.jt2;
revoke all from somename on public.mytable;
revoke all from somename on public.test1;
revoke all from somename on public.test2;
revoke all from somename on public.tmp;
(8 rows)
--
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] Help request to improve function performance
Hi there, I have a set of dynamically composed objects represented in Java, with string values for various attributes, which have variable length. In case you have suggestions for a better type for this case, it would be my pleasure to hear about them. 2009/4/22 Grzegorz Jaśkiewicz > you keep everything in varchars, and yet you request improvements in > performance. > you are a funny guy, ... >
[GENERAL] Re: [ADMIN] how to revoke multiple users permission from multiple tables at the same time?
On Wed, Apr 22, 2009 at 5:01 PM, Scott Marlowe wrote:
> On Wed, Apr 22, 2009 at 4:19 PM, DM wrote:
>> how to revoke multiple users permission from multiple tables at the same
>> time?
>> Or in simple is there a way to revoke multiple users grant access from
>> multiple tables under a schema.?
>
> Best way is to NOT grant multiple users permissions, but to grant a
> role the permissions and grant that role to users. That way you only
> have to revoke persmissions from the role to revoke it from all the
> users.
>
>> I use Revoke below command to execute on each table one by one.
>> revoke SELECT/ALL on testtable from user1;
>
> Note that you can build a set of revoke commands by using selects and
> concatenations if you need them. Something like this (use psql -E to
> see the queries \ commands invoke in psql)
>
> SELECT 'revoke all from somename on '||n.nspname||'.'|| c.relname ||';'
> FROM pg_catalog.pg_class c
> JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
> LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> WHERE c.relkind IN ('r','v','S','')
> AND n.nspname <> 'pg_catalog'
> AND n.nspname !~ '^pg_toast'
> AND pg_catalog.pg_table_is_visible(c.oid)
> ORDER BY 1;
>
> ?column?
> --
> revoke all from somename on public.colony;
> revoke all from somename on public.delegate;
> revoke all from somename on public.jt1;
> revoke all from somename on public.jt2;
> revoke all from somename on public.mytable;
> revoke all from somename on public.test1;
> revoke all from somename on public.test2;
> revoke all from somename on public.tmp;
> (8 rows)
In my hurry I got the order wrong, you want the revoke to look like this:
revoke all on tablename from somename;
I leave it to you to rebuild the query to get what ya need.
--
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] Help request to improve function performance
2009/4/22 Seref Arikan :
> Hi Filip,
> First of all: thanks a lot for your kind response. Here is the create script
> for my schema:
>
> CREATE TABLE "app"."archetype_data" (
> "id" BIGINT NOT NULL,
> "context_id" VARCHAR(1000),
> "archetype_name" VARCHAR(1000),
> "archetype_path" VARCHAR(1000),
> "name" VARCHAR(1000),
> "value_string" VARCHAR(1000),
> "value_int" BIGINT,
> "value_double" DOUBLE PRECISION,
> "session_id" VARCHAR(1000),
> "instance_index" INTEGER,
> CONSTRAINT "archetype_data_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
If I'm not mistaken, you're doing Entity Attribute Value model type
storage. I.e. a database in a database. Makes for easy coding, and
danged near impossible to troubleshoot your data.
It's a religious issue but I come down on the side that good data
modelling is hard for a reason, because it pays you back so much in
the end.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: [ADMIN] how to revoke multiple users permission from multiple tables at the same time?
Thanks Scott.
Good answer, I was consolidating the schemas here, there were too many users
were granted permission to tables.I wanted to consolidate/optimize to bring
it to one role and granting this role to the user (same way as you
mentioned).
Thanks for the solution.
Thanks
Deepak
On Wed, Apr 22, 2009 at 4:02 PM, Scott Marlowe wrote:
> On Wed, Apr 22, 2009 at 5:01 PM, Scott Marlowe
> wrote:
> > On Wed, Apr 22, 2009 at 4:19 PM, DM wrote:
> >> how to revoke multiple users permission from multiple tables at the same
> >> time?
> >> Or in simple is there a way to revoke multiple users grant access from
> >> multiple tables under a schema.?
> >
> > Best way is to NOT grant multiple users permissions, but to grant a
> > role the permissions and grant that role to users. That way you only
> > have to revoke persmissions from the role to revoke it from all the
> > users.
> >
> >> I use Revoke below command to execute on each table one by one.
> >> revoke SELECT/ALL on testtable from user1;
> >
> > Note that you can build a set of revoke commands by using selects and
> > concatenations if you need them. Something like this (use psql -E to
> > see the queries \ commands invoke in psql)
> >
> > SELECT 'revoke all from somename on '||n.nspname||'.'|| c.relname ||';'
> > FROM pg_catalog.pg_class c
> > JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
> > LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
> > WHERE c.relkind IN ('r','v','S','')
> > AND n.nspname <> 'pg_catalog'
> > AND n.nspname !~ '^pg_toast'
> > AND pg_catalog.pg_table_is_visible(c.oid)
> > ORDER BY 1;
> >
> > ?column?
> > --
> > revoke all from somename on public.colony;
> > revoke all from somename on public.delegate;
> > revoke all from somename on public.jt1;
> > revoke all from somename on public.jt2;
> > revoke all from somename on public.mytable;
> > revoke all from somename on public.test1;
> > revoke all from somename on public.test2;
> > revoke all from somename on public.tmp;
> > (8 rows)
>
> In my hurry I got the order wrong, you want the revoke to look like this:
>
> revoke all on tablename from somename;
>
> I leave it to you to rebuild the query to get what ya need.
>
Re: [GENERAL] Problem with pgpool-II tool
The error message says all. Pgpool process needs to create /var/run/pgpool/pgpool.pid when starting up. So you should give pgpool write access right to the directory(/var/run/pgpool/), and of courese along with access right to reach the directory. -- Tatsuo Ishii SRA OSS, Inc. Japan > Hello, > > I have encountered a problem while configuring pgpool-II,I > encountered a problem while executing the following command. > > pgpool -n & > [1] 2796 > [achand...@localhost ~]$ pool_config: could not open configuration file > (pgpool.conf) > pool_config: using default values... > pid file found but it seems bogus. Trying to start pgpool anyway... > 2009-04-19 12:39:24 ERROR: pid 2796: could not open pid file as > /var/run/pgpool/pgpool.pid. reason: Permission denied > > Following were the default paths in pgpool.conf file > > # Logging directory > logdir = '/tmp' > > # pid file name > pid_file_name = '/var/run/pgpool/pgpool.pid' > > since there is no pgpool/pgpool.pid file in run folder i created pgpool > directory and pgpool.pid file in that directory and gave a value 2678 in > pgpool.pid file then i got the above message.Can you guys please help me in > this? > > Thanks, > Aravind. > > > -- 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] Help request to improve function performance
Seref Arikan wrote: I have a set of dynamically composed objects represented in Java, with string values for various attributes, which have variable length. In case you have suggestions for a better type for this case, it would be my pleasure to hear about them. cut out about 3 layers of abstraction and get down to what you REALLY need to get done. dynamically composed piles of text valued attributes will NEVER be efficient, no matter what you do. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
