Re: [GENERAL] Simple schema diff script in Perl
sqlt-diff --to MySQL Schema1=MySQL Schema2=MySQL SchemaUpdate.mysql.sql i can't guess where is the database name or user to use, if it work with dumps i need to give the dump files and the database type... My version says: | Currently (v0.0900), only MySQL is supported by this code. I don't know if there is a newer version. I can see that such a tool could be useful. -- Florian Weimerfwei...@bfk.de BFK edv-consulting GmbH http://www.bfk.de/ Kriegsstraße 100 tel: +49-721-96201-1 D-76133 Karlsruhe fax: +49-721-96201-99 -- 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] libssl issue ?
Thanks for the input Tom. Compiling openssl with the shared option did the trick. On Thu, Sep 16, 2010 at 3:49 PM, Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@postnewspapers.com.au writes: On 16/09/2010 4:35 PM, Peter Roethlisberger wrote: /usr/local/openssl/lib64/libssl.a: could not read symbols: Bad value Bad build of OpenSSL? What does: Well, the real question here is why it's not picking up a shared-library version of libssl instead. By and large you don't want libpq linking to a static version of openssl, so even if this configuration worked it wouldn't be a good thing to do. I'd get rid of the /usr/local version of openssl and use the distro-provided one instead. regards, tom lane -- ___ Peter Roethlisberger Suldhaltenstrasse 4 3703 Aeschi Switzerland 46°39'14 N 7°41'47 E Mobile: +41 (0)79 785 79 35 Fix: +41 (0)33 654 04 39 Skype:PeterRoethlisberger peter.roethlisber...@gmail.com
[GENERAL] What's wrong with this query?
Here's the query:
SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
R.PrepTime, R.CookTime, R.OwnerId, U.Alias, ts_rank_cd(R.TextSearch,
query) as Rank
FROM Recipes R, plainto_tsquery('veggie burgers') query
INNER JOIN Users U ON U.UserId = R.OwnerId
WHERE (R.TextSearch @@ query)
AND NOT EXISTS (select 1 from RecipeIngredients inner join
IngredientBlacklist using (IngredientId) where RecipeId = r.RecipeId
and IngredientBlacklist.UserId =
'affaa328-5b53-430e-991a-22674ede6faf' limit 1)
ORDER BY Rank DESC LIMIT 100;
Here's the error:
ERROR: invalid reference to FROM-clause entry for table r
LINE 3: INNER JOIN Users U ON U.UserId = R.OwnerId
^
HINT: There is an entry for table r, but it cannot be referenced
from this part of the query.
** Error **
ERROR: invalid reference to FROM-clause entry for table r
SQL state: 42P01
Hint: There is an entry for table r, but it cannot be referenced
from this part of the query.
Character: 239
This seems to happen when I add the fulltext functions. Thanks!
Mike
--
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] Transposing rows and columns
On 09/16/2010 05:26 PM, Aram Fingal wrote: On Sep 16, 2010, at 4:37 PM, John R Pierce wrote: On 09/16/10 10:44 AM, Aram Fingal wrote: I have thought about that but later on, when we do the full sized experiments, there will be too many rows for Excel to handle. if you insist on this transposing, won't that mean you'll end up with more columns than SQL can/should handle? No. The organization in Excel is much more efficient of the total number of cells used but not much good for querying. When I transpose it for use in the database (or pivot it in Excel), it actually multiplies the number of rows. So, if the version with separate columns for each subject has X rows and Y columns, you get X * Y rows in the database version. For example, If there are 100 subjects, and 1000 drug/dose combinations. Then the Excel version has 102 columns (drug, dose and a column for each subject) and 1000 rows. The database (or pivoted) version would have 4 columns (subject, drug, dose and response) and 100,000 rows. Excel maxes out at 65,535 rows and PostgreSQL has no limit. I think excel 2007 can handle more than 65,535 rows. The subjects, by the way, are not people, they are cancer cell tissue cultures in 384-well plates, handled by robots. That's how we can do so many drug/dose combinations. We'll do even more in the future. -Aram -- Stephen Clark NetWolves Sr. Software Engineer III Phone: 813-579-3200 Fax: 813-882-0209 Email: steve.cl...@netwolves.com www.netwolves.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] where does postgres keep the query result until it is returned?
Hi, Where does postgres keep the query result until it is returned? In the shared_buffers? Or in extra memory that was not previously allocated, or something else? What if the query result becomes very large, so that it won't fit into memory? cheers, WBL -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw
Re: [GENERAL] where does postgres keep the query result until it is returned?
On Fri, Sep 17, 2010 at 03:00:36PM +0200, Willy-Bas Loos wrote: Where does postgres keep the query result until it is returned? In the shared_buffers? Or in extra memory that was not previously allocated, or something else? Postgres, the server software, will spill large results (and any intermediate working sets) to disk automatically as needed. I believe any memory allocated for this task will be up to work_mem in size. The client side however isn't as intelligent, libpq will, by default, try and read all records into memory and will crash if the results are too large. There are various ways of dealing with this, but haven't tried myself. -- Sam http://samason.me.uk/ -- 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] where does postgres keep the query result until it is returned?
On Fri, Sep 17, 2010 at 02:14:57PM +0100, Sam Mason wrote: Postgres, the server software, will spill large results (and any intermediate working sets) to disk automatically as needed. I believe any memory allocated for this task will be up to work_mem in size. That wasn't very clear was it; when I said this task I meant the task of accumulating results before things spill. -- Sam http://samason.me.uk/ -- 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's wrong with this query?
Mike Christensen m...@kitchenpc.com writes:
Here's the query:
SELECT R.RecipeId, R.Title, R.Description, R.ImageUrl, R.Rating,
R.PrepTime, R.CookTime, R.OwnerId, U.Alias, ts_rank_cd(R.TextSearch,
query) as Rank
FROM Recipes R, plainto_tsquery('veggie burgers') query
INNER JOIN Users U ON U.UserId = R.OwnerId
WHERE (R.TextSearch @@ query)
AND NOT EXISTS (select 1 from RecipeIngredients inner join
IngredientBlacklist using (IngredientId) where RecipeId = r.RecipeId
and IngredientBlacklist.UserId =
'affaa328-5b53-430e-991a-22674ede6faf' limit 1)
ORDER BY Rank DESC LIMIT 100;
Here's the error:
ERROR: invalid reference to FROM-clause entry for table r
LINE 3: INNER JOIN Users U ON U.UserId = R.OwnerId
You've been bitten by the mysql-ish idea that comma and JOIN are
interchangeable. They are not. In the SQL standard, and in every
implementation of it other than mysql, JOIN binds tighter than comma
--- so that INNER JOIN's condition can only refer to query and u,
not r. Try it like this (or any of several other ways):
FROM Recipes R INNER JOIN Users U ON U.UserId = R.OwnerId,
plainto_tsquery('veggie burgers') query
WHERE ...
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] where does postgres keep the query result until it is returned?
Sam Mason s...@samason.me.uk writes: On Fri, Sep 17, 2010 at 02:14:57PM +0100, Sam Mason wrote: Postgres, the server software, will spill large results (and any intermediate working sets) to disk automatically as needed. I believe any memory allocated for this task will be up to work_mem in size. That wasn't very clear was it; when I said this task I meant the task of accumulating results before things spill. If the question was about the final query result sent to the client: we don't spill that to disk, nor hold it anywhere. The backend sends it to the client on-the-fly as each row is generated. It's the client's responsibility to cope if the result set is huge. (As you noted, libpq is none too bright about that.) 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] where does postgres keep the query result until it is returned?
If the question was about the final query result sent to the client: yes we don't spill that to disk, nor hold it anywhere. The backend sends it to the client on-the-fly as each row is generated. thanks, i didn't know that. I asked because i have a function that produces a result in xml. that is one row, one value even, but it grows pretty large. how is that handled? -- Patriotism is the conviction that your country is superior to all others because you were born in it. -- George Bernard Shaw -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELF LEFT OUTER JOIN = SELF JOIN including NULL values
Hello everybody out there using PostgreSQL, After having read the official documentation and having done extensive web search, I'm wondering how to perform something like a SELF LEFT OUTER JOIN in PostgreSQL, i.e. a SELF JOIN on a table containing NULL values in one of the columns to join. Thanks in advance for your help, Julia Heute erleben, was morgen Trend wird - das kann man auf der IFA in Berlin. Oder auf arcor.de: Wir stellen Ihnen die wichtigsten News, Trends und Gadgets der IFA vor. Natürlich mit dabei: das brandneue IPTV-Angebot von Vodafone! Alles rund um die Internationale Funkausstellung in Berlin finden Sie hier: http://www.arcor.de/rd/footer.ifa2010 -- 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] SELF LEFT OUTER JOIN = SELF JOIN including NULL values
On 17/09/2010 17:16, julia.jacob...@arcor.de wrote: Hello everybody out there using PostgreSQL, After having read the official documentation and having done extensive web search, I'm wondering how to perform something like a SELF LEFT OUTER JOIN in PostgreSQL, i.e. a SELF JOIN on a table containing NULL values in one of the columns to join. Something like this? - select from my_table a left join my_table b on (a.my_column = b.my_column) ... Would this do it? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] SELF LEFT OUTER JOIN = SELF JOIN including NULL values
On Fri, Sep 17, 2010 at 06:16:44PM +0200, julia.jacob...@arcor.de wrote: Hello everybody out there using PostgreSQL, After having read the official documentation and having done extensive web search, I'm wondering how to perform something like a SELF LEFT OUTER JOIN in PostgreSQL, i.e. a SELF JOIN on a table containing NULL values in one of the columns to join. I guess you're looking for the IS NOT DISTINCT FROM operator. I.e. SELECT * FROM foo a INNER JOIN foo b ON (a.x = b.x AND a.y IS NOT DISTINCT FROM b.y); If you want a truth table: WITH x(v) AS (VALUES (1),(2),(NULL)) SELECT l.v, r.v, l.v = r.v AS equality, l.v IS NOT DISTINCT FROM r.v AS isnotdistinctfrom FROM x l, x r; -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PLPGSQL function to search function source for a list of terms
Here is a plpsql function I put together to search db functions in
schemas other than pg_catalog and information_schema. Not the greatest
of coding, but it might help someone else trying to solve the same
issue I was having: to search all public functions for a list of
terms. Sample usage is below the code, as are some sample results.
Any changes/improvements/critcisms appreciated. (or even a better version!)
[works in pg version 8.4, not in 8.2 -- not sure about 8.3]
create or replace function search_public_functions(p_search_strings
TEXT, p_case_insensitive BOOLEAN, OUT function_name TEXT, OUT
matching_terms TEXT) RETURNS SETOF RECORD AS
$body$
declare
x RECORD;
qry TEXT;
v_match BOOLEAN := 'false';
v_matches TEXT;
v_search_stringsTEXT := p_search_strings;
v_case_insensitive BOOLEAN := p_case_insensitive;
v_funcdef TEXT;
begin
/* v_search_strings is a list, pipe-separated, exactly what we
want to search against.
NOTE: works on postgresql v8.4
example:
select function_name,matching_terms from
search_public_functions('crosstab|intersect|except|ctid',true);
*/
if (v_case_insensitive IS NOT FALSE) then
v_case_insensitive := TRUE;
end if;
qry := 'SELECT n.nspname||''.''||p.proname||''
(''||pg_catalog.pg_get_function_arguments(p.oid)||'')''::TEXT as
funcname,
(select pg_catalog.pg_get_functiondef(p.oid))
as funcdef,
p.oid as funcoid
FROM pg_catalog.pg_proc p
LEFT JOIN pg_catalog.pg_namespace n ON n.oid =
p.pronamespace
WHERE pg_catalog.pg_function_is_visible(p.oid)
AND n.nspname ''pg_catalog''
AND n.nspname ''information_schema''
AND NOT p.proisagg
ORDER BY 1';
if (p_case_insensitive IS TRUE) then
v_search_strings := LOWER(v_search_strings);
end if;
for x in execute qry loop
v_match := 'false';
function_name := null;
v_funcdef := null;
select into v_match x.funcdef ~* v_search_strings;
if ( v_match IS TRUE ) then
v_matches := null;
v_funcdef := x.funcdef;
if (p_case_insensitive IS TRUE) then
v_funcdef := LOWER(v_funcdef);
end if;
select array_to_string(array_agg(val),',')
into v_matches from (select distinct
array_to_string(regexp_matches(v_funcdef, v_search_strings ,'g'),',')
as val) as y2;
function_name := x.funcname;
matching_terms := v_matches;
RETURN NEXT;
end if;
end loop;
end;
$body$ language plpgsql SECURITY DEFINER;
select function_name,matching_terms from
search_public_functions('crosstab|intersect|except|ctid',true);
function_name | matching_terms
--+
public.array_intersect (anyarray, anyarray) | intersect
public.cant_delete_error () | except
public.crosstab2 (text) | crosstab
public.crosstab3 (text) | crosstab
public.crosstab4 (text) | crosstab
public.crosstab (text) | crosstab
public.crosstab (text, integer) | crosstab
public.crosstab (text, text) | crosstab
public.find_bad_block (p_tablename text)| ctid,except
--
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] PLPGSQL function to search function source for a list of terms
On 17/09/2010 17:37, bricklen wrote: Here is a plpsql function I put together to search db functions in schemas other than pg_catalog and information_schema. Not the greatest of coding, but it might help someone else trying to solve the same issue I was having: to search all public functions for a list of terms. Sample usage is below the code, as are some sample results. That could be pretty useful - why don't you put it on the wiki? Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Transposing rows and columns
On Sep 17, 2010, at 9:00 AM, Steve Clark wrote: I think excel 2007 can handle more than 65,535 rows. You may be right. I'm actually using NeoOffice (Mac enhanced version of OpenOffice) and that can handle something like 1,048,000 rows.I wouldn't be surprised if newer versions of Excel can do the same. The real issue is querying. -Aram -- 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] PLPGSQL function to search function source for a list of terms
On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnell r...@iol.ie wrote: That could be pretty useful - why don't you put it on the wiki? Ray. I was going to put an entry at http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I couldn't find the edit option. Maybe I'm blind? I just noticed how poorly formatted that cut 'n paste job turned out too. Ugh. -- 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] PLPGSQL function to search function source for a list of terms
On 17/09/2010 18:12, bricklen wrote: On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnellr...@iol.ie wrote: That could be pretty useful - why don't you put it on the wiki? Ray. I was going to put an entry at http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I couldn't find the edit option. Maybe I'm blind? I just noticed how poorly formatted that cut 'n paste job turned out too. Ugh. You need to be logged in to edit the wiki - if you haven't yet got a community account, you can get one on the pg.org site: http://www.postgresql.org/community/signup Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] PLPGSQL function to search function source for a list of terms
On Fri, Sep 17, 2010 at 10:16 AM, Raymond O'Donnell r...@iol.ie wrote: On 17/09/2010 18:12, bricklen wrote: On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnellr...@iol.ie wrote: That could be pretty useful - why don't you put it on the wiki? Ray. I was going to put an entry at http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I couldn't find the edit option. Maybe I'm blind? I just noticed how poorly formatted that cut 'n paste job turned out too. Ugh. You need to be logged in to edit the wiki - if you haven't yet got a community account, you can get one on the pg.org site: http://www.postgresql.org/community/signup Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie Aha! Thanks, I'll do that. -- 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] missing chunk number 497 for toast value 504723663
On Thursday 16 September 2010, Tom Lane elucidated thus: Utsav Turray utsav.tur...@newgen.co.in writes: I am using postgres 7.3.2 on RHEL 4.0. Egad. Secondly what are probable reasons behind corruption and what can we do to prevent this error. Update. Whatever reasons you might have for running 7.3.2 are bad ones. Disclaimer: I agree with Tom; running 7.3.2 is a bad idea. That said: like he said, he can't. He's running RHEL 4.0. Presumably he is on a support contract, so moving to non-system software means he no longer has vendor support and upgrades for the packages installed on his system. Pg 7.3.x is what came with RHEL 4. Considering RHEL 3 will EOL (finally) at the end of October, RH is going to be supporting Pg 7.3 for quite a while. Hopefully they'll back port security fixes. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com - Fairbanks, AK PGP Key: http://pgp.mit.edu/ ID 0x73B13B6A -- 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] missing chunk number 497 for toast value 504723663
On Fri, Sep 17, 2010 at 11:42 AM, Joshua J. Kugler jos...@eeinternet.com wrote: On Thursday 16 September 2010, Tom Lane elucidated thus: Utsav Turray utsav.tur...@newgen.co.in writes: I am using postgres 7.3.2 on RHEL 4.0. Egad. Secondly what are probable reasons behind corruption and what can we do to prevent this error. Update. Whatever reasons you might have for running 7.3.2 are bad ones. Disclaimer: I agree with Tom; running 7.3.2 is a bad idea. That said: like he said, he can't. He's running RHEL 4.0. Presumably he is on a support contract, so moving to non-system software means he no longer has vendor support and upgrades for the packages installed on his system. Pg 7.3.x is what came with RHEL 4. Considering RHEL 3 will EOL (finally) at the end of October, RH is going to be supporting Pg 7.3 for quite a while. Hopefully they'll back port security fixes. And 7.3.2 is the last update available for RHEL3? When I look at centos 3 repos, they have 7.3.21 in them. -- To understand recursion, one must first understand recursion. -- 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] PLPGSQL function to search function source for a list of terms
On Fri, Sep 17, 2010 at 10:17 AM, bricklen brick...@gmail.com wrote: On Fri, Sep 17, 2010 at 10:16 AM, Raymond O'Donnell r...@iol.ie wrote: On 17/09/2010 18:12, bricklen wrote: On Fri, Sep 17, 2010 at 9:57 AM, Raymond O'Donnellr...@iol.ie wrote: That could be pretty useful - why don't you put it on the wiki? Ray. I was going to put an entry at http://wiki.postgresql.org/wiki/Category:Library_Snippets, but I couldn't find the edit option. Maybe I'm blind? I just noticed how poorly formatted that cut 'n paste job turned out too. Ugh. You need to be logged in to edit the wiki - if you haven't yet got a community account, you can get one on the pg.org site: http://www.postgresql.org/community/signup Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie Aha! Thanks, I'll do that. It has now been added to http://wiki.postgresql.org/wiki/Category:Library_Snippets -- 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] where does postgres keep the query result until it is returned?
On Fri, Sep 17, 2010 at 04:51:46PM +0200, Willy-Bas Loos wrote: i have a function that produces a result in xml. that is one row, one value even, but it grows pretty large. how is that handled? Rows are sent back in the entireity, so the PG instance would need enough memory to work with that row. When you're running a 32bit version of PG, values whose size is beyond ~100MB are a bit touch and go whether it will work. -- Sam http://samason.me.uk/ -- 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 generate XML output from a Store Procedure
Hello List I hope all of you are ok I would like to know if exist a manner to obtain a output on xml format from a store procedure It may lead me to improve my current develop practices when I have to send a Xml ouput which I want to transform by XSLT scripts. I'll be waiting for your commments.
[GENERAL] Alter Table Command Rearranges Rows
I noticed that my database was in order based on my primary key column called 'id' which when from 1 (first) to 6 (last). Today I had to edit table data which wasn't anything crazy: team=#ALTER users SET name = 'David' WHERE id = '1'; UPDATE 1 Now when I do a 'SELECT * FROM users' command in PostgreSQL, my row that I altered column data in has been dropped all the way to the bottom. This is extremely messy and annoying for me and I was wondering if this is normal behavior for PostgreSQL? I could understand that if remove the row and then re-added it, I would expect it to add a new row to the bottom of the table. Thanks for any assistance or clarification. -Carlos -- 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] Alter Table Command Rearranges Rows
On Fri, Sep 17, 2010 at 4:12 PM, Carlos Mennens carlos.menn...@gmail.com wrote: Thanks for any assistance or clarification. Rows in SQL are unordered. If you want an ordering, specify one on your SELECT. -- 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] missing chunk number 497 for toast value 504723663
Joshua J. Kugler jos...@eeinternet.com writes: On Thursday 16 September 2010, Tom Lane elucidated thus: Update. Whatever reasons you might have for running 7.3.2 are bad ones. Disclaimer: I agree with Tom; running 7.3.2 is a bad idea. That said: like he said, he can't. He's running RHEL 4.0. Presumably he is on a support contract, so moving to non-system software means he no longer has vendor support and upgrades for the packages installed on his system. Pg 7.3.x is what came with RHEL 4. No, it wasn't. Red Hat shipped 7.4.x on RHEL-4, and the current package there is 7.4.29. Red Hat did ship 7.3.x on RHEL-3, and the current package there is 7.3.21 + several back-ported patches. 7.3.2 hasn't been current on any Red Hat distro since 2003. I know because I do the work. If he is depending on a third party vendor that can't be bothered to update past 7.3.2, he needs to find a less incompetent vendor. Pronto, before he loses more data to their incompetence. 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] Alter Table Command Rearranges Rows
On Sep 17, 2010, at 16:12 , Carlos Mennens wrote: I noticed that my database was in order based on my primary key column called 'id' which when from 1 (first) to 6 (last). Today I had to edit table data which wasn't anything crazy: team=#ALTER users SET name = 'David' WHERE id = '1'; UPDATE 1 This isn't valid syntax: I believe you issued UPDATE users Postgres (nor any other SQL RDBMS) does not guarantee row order unless you specify it with an ORDER BY clause. Michael Glaesemann grzm seespotcode 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] missing chunk number 497 for toast value 504723663
On Friday 17 September 2010, Tom Lane elucidated thus: Joshua J. Kugler jos...@eeinternet.com writes: On Thursday 16 September 2010, Tom Lane elucidated thus: Update. Whatever reasons you might have for running 7.3.2 are bad ones. Disclaimer: I agree with Tom; running 7.3.2 is a bad idea. That said: like he said, he can't. He's running RHEL 4.0. Presumably he is on a support contract, so moving to non-system software means he no longer has vendor support and upgrades for the packages installed on his system. Pg 7.3.x is what came with RHEL 4. No, it wasn't. Red Hat shipped 7.4.x on RHEL-4, and the current package there is 7.4.29. Red Hat did ship 7.3.x on RHEL-3, and the current package there is 7.3.21 + several back-ported patches. 7.3.2 hasn't been current on any Red Hat distro since 2003. I know because I do the work. If he is depending on a third party vendor that can't be bothered to update past 7.3.2, he needs to find a less incompetent vendor. Pronto, before he loses more data to their incompetence. I apologize. I went to look at packages, and must have seen the '7' and it didn't click that it was 7.4. I must have scanned too fast. So yes, that is *VERY* weird that he is running RHEL 4, but only Pg 7.3. j -- Joshua Kugler Part-Time System Admin/Programmer http://www.eeinternet.com - Fairbanks, AK PGP Key: http://pgp.mit.edu/ ID 0x73B13B6A -- 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] Alter Table Command Rearranges Rows
On Fri, Sep 17, 2010 at 1:32 PM, Michael Glaesemann g...@seespotcode.net wrote: Postgres (nor any other SQL RDBMS) does not guarantee row order unless you specify it with an ORDER BY clause. This is true, but some database will maintain a tables clustering. MS-Access comes to mind. I don't know if MySQL does this also. In PostgreSQL you can issue a periodic cluster command on the primary key. But as mentioned it is a bad practice to rely on the physical ordering of the table. -- Regards, Richard Broersma Jr. Visit the Los Angeles PostgreSQL Users Group (LAPUG) http://pugs.postgresql.org/lapug -- 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 generate XML output from a Store Procedure
On Fri, Sep 17, 2010 at 1:04 PM, Edwin Plauchu pianodae...@gmail.com wrote: Hello List I hope all of you are ok I would like to know if exist a manner to obtain a output on xml format from a store procedure It may lead me to improve my current develop practices when I have to send a Xml ouput which I want to transform by XSLT scripts. I'll be waiting for your commments. Maybe some useful info in here: http://www.postgresql.org/docs/8.4/interactive/functions-xml.html -- 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] Alter Table Command Rearranges Rows
On Fri, Sep 17, 2010 at 4:32 PM, Michael Glaesemann g...@seespotcode.net wrote: This isn't valid syntax: I believe you issued UPDATE users Woops. I did use the UPDATE and not ALTER command. On Fri, Sep 17, 2010 at 4:39 PM, Richard Broersma richard.broer...@gmail.com wrote: On Fri, Sep 17, 2010 at 1:32 PM, Michael Glaesemann g...@seespotcode.net wrote: Postgres (nor any other SQL RDBMS) does not guarantee row order unless you specify it with an ORDER BY clause. This is true, but some database will maintain a tables clustering. MS-Access comes to mind. I don't know if MySQL does this also. MySQL does but I am fine with just running the ORDER BY command when I use SELECT. -Carlos -- 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 magic for identifieing double adresses
On Sep 15, 2010, at 10:40 PM, Andreas wrote: I need to clean up a lot of contact data because of a merge of customer lists that used to be kept separate. I allready know that there are double entries within the lists and they do overlap, too. Relevant fields could be name, street, zip, city, phone Is there a way to do something like this with postgresql ? I fear this will need still a lot of manual sorting and searching even when potential peers get automatically identified. I recently started working with the pg_trgm contrib module for matching songs based on titles and writers. This is especially difficult because the writer credits end up in one big field with every possible variation on order and naming conventions. So far I have been pleased with the results. For example, the algorithm correctly matched these two song titles: FONTAINE DI ROMA AKA FOUNTAINS OF ROME FOUNTAINS OF ROME A/K/A FONTANE DI ROMA Trigrams can be indexed, so it is relatively fast to find an initial set of candidates. There is a nice introductory article here: http://www.postgresonline.com/journal/categories/59-pgtrgm 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
[GENERAL] Referring to function parameter in function
I appear to be having a problem with a function I've created, and no
doubt it'll be something obvious I'm doing wrong. Here's my function:
CREATE OR REPLACE FUNCTION get_lsfr(
bitlength INT,
taps INT[],
from_value INT
) RETURNS INT AS $$
DECLARE
last_tap_value BIT;
tap INT;
new_value INT;
BEGIN
IF (SELECT MAX(x) FROM unnest(taps) AS x) bitlength THEN
RAISE EXCEPTION 'LSFR tap exceeds range of value.';
END IF;
FOR tap IN SELECT value FROM unnest(taps) AS x(value) ORDER BY value
DESC LOOP
IF last_tap_value IS NOT NULL THEN
last_tap_value := last_tap_value #
GET_BIT(from_value::bit(bitlength), tap.value-1);
ELSE
last_tap_value := GET_BIT(from_value::bit(bitlength),
tap.value-1);
CONTINUE;
END IF;
END LOOP;
new_value := (last_tap_value || SUBSTRING(from_value::BIT(bitlength),
1, bitlength - 1))::BIT(bitlength)::INT;
RETURN new_value;
END;
$$ LANGUAGE plpgsql;
And here's it's usage and result:
select get_lsfr(4,'{3,4}'::int[],6);
ERROR: invalid input syntax for integer: bitlength
LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
^
QUERY: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
CONTEXT: PL/pgSQL function get_lsfr line 14 at assignment
If the function is difficult to read, please look at this paste:
http://pgsql.privatepaste.com/fd5b83166c
I want to use the parameter called bitlength as the length of a bit
when casting a value.
So, in this case, it would be GET_BIT(6::bit(4), 4-1)
What am I missing?
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
--
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] Referring to function parameter in function
Thom Brown t...@linux.com writes: ERROR: invalid input syntax for integer: bitlength LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1) ^ I want to use the parameter called bitlength as the length of a bit when casting a value. Hm, you can't ... that's not a valid place for a parameter. You'd have to EXECUTE a built-up string. 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] Referring to function parameter in function
On 18 September 2010 00:14, Tom Lane t...@sss.pgh.pa.us wrote:
Thom Brown t...@linux.com writes:
ERROR: invalid input syntax for integer: bitlength
LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
^
I want to use the parameter called bitlength as the length of a bit
when casting a value.
Hm, you can't ... that's not a valid place for a parameter. You'd have
to EXECUTE a built-up string.
Ah, thanks Tom. Although it's now treating the actual query text as a
value by the look of it:
CREATE OR REPLACE FUNCTION get_lsfr(
bitlength INT,
taps INT[],
from_value INT
) RETURNS INT AS $$
DECLARE
last_tap_value BIT;
tap RECORD;
new_value INT;
BEGIN
IF (SELECT MAX(x) FROM unnest(taps) AS x) bitlength THEN
RAISE EXCEPTION 'LSFR tap exceeds range of value.';
END IF;
FOR tap IN SELECT tap_values FROM unnest(taps) AS x(tap_values) ORDER
BY tap_values DESC LOOP
IF last_tap_value IS NOT NULL THEN
EXECUTE 'SELECT ' || last_tap_value || ' # GET_BIT(' ||
from_value
|| '::bit(' || bitlength || '), ' || tap.tap_values || '-1)' INTO
last_tap_value;
ELSE
EXECUTE 'SELECT GET_BIT(' || from_value || '::bit(' ||
bitlength ||
'), ' || tap.tap_values || '-1)' INTO last_tap_value;
CONTINUE;
END IF;
END LOOP;
new_value := (last_tap_value || SUBSTRING(from_value::BIT(bitlength),
1, bitlength - 1))::BIT(bitlength)::INT;
RETURN new_value;
END;
$$ LANGUAGE plpgsql;
=# select get_lsfr(4,'{3,4}'::int[],6);
ERROR: S is not a valid binary digit
LINE 1: SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' || from_...
^
QUERY: SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' ||
from_value || '::bit(' || bitlength || '), ' || tap.tap_values ||
'-1)'
CONTEXT: PL/pgSQL function get_lsfr line 12 at EXECUTE statement
http://pgsql.privatepaste.com/5441ff7cc0
I'm thinking maybe I haven't used the correct syntax.
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
--
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] Referring to function parameter in function
On 18 September 2010 00:52, Thom Brown t...@linux.com wrote:
On 18 September 2010 00:14, Tom Lane t...@sss.pgh.pa.us wrote:
Thom Brown t...@linux.com writes:
ERROR: invalid input syntax for integer: bitlength
LINE 1: SELECT GET_BIT(from_value::bit(bitlength), tap.value-1)
^
I want to use the parameter called bitlength as the length of a bit
when casting a value.
Hm, you can't ... that's not a valid place for a parameter. You'd have
to EXECUTE a built-up string.
Ah, thanks Tom. Although it's now treating the actual query text as a
value by the look of it:
CREATE OR REPLACE FUNCTION get_lsfr(
bitlength INT,
taps INT[],
from_value INT
) RETURNS INT AS $$
DECLARE
last_tap_value BIT;
tap RECORD;
new_value INT;
BEGIN
IF (SELECT MAX(x) FROM unnest(taps) AS x) bitlength THEN
RAISE EXCEPTION 'LSFR tap exceeds range of value.';
END IF;
FOR tap IN SELECT tap_values FROM unnest(taps) AS x(tap_values) ORDER
BY tap_values DESC LOOP
IF last_tap_value IS NOT NULL THEN
EXECUTE 'SELECT ' || last_tap_value || ' # GET_BIT('
|| from_value
|| '::bit(' || bitlength || '), ' || tap.tap_values || '-1)' INTO
last_tap_value;
ELSE
EXECUTE 'SELECT GET_BIT(' || from_value || '::bit(' ||
bitlength ||
'), ' || tap.tap_values || '-1)' INTO last_tap_value;
CONTINUE;
END IF;
END LOOP;
new_value := (last_tap_value || SUBSTRING(from_value::BIT(bitlength),
1, bitlength - 1))::BIT(bitlength)::INT;
RETURN new_value;
END;
$$ LANGUAGE plpgsql;
=# select get_lsfr(4,'{3,4}'::int[],6);
ERROR: S is not a valid binary digit
LINE 1: SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' || from_...
^
QUERY: SELECT 'SELECT ' || last_tap_value || ' # GET_BIT(' ||
from_value || '::bit(' || bitlength || '), ' || tap.tap_values ||
'-1)'
CONTEXT: PL/pgSQL function get_lsfr line 12 at EXECUTE statement
http://pgsql.privatepaste.com/5441ff7cc0
I'm thinking maybe I haven't used the correct syntax.
--
I've solved it. These constructs take a bit of getting used to. I
just needed to convert the parameter being injected after the SELECT
to text as the bit value couldn't be inserted natively. My function
works perfectly now. Thanks for the help :)
--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unintuitive subquery record wrapping
I'm puzzled if this is by design or just overlooked... create table t(a int, b varchar); insert into t values(1,'x'); For basic query: select t from t result is of type t. If I query: select sq from (select t from t) sq; result is of type record. I need to query like this: select (sq).t from (select t from t) sq; or like this: select t from (select t from t) t; to get result of type t. Wrapping t to record if another alias is used seems a bit unintuitive to me? Is this by design and if yes, why? Regards, Rikard -- 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] unintuitive subquery record wrapping
Rikard Pavelic rikard.pave...@zg.htnet.hr writes: For basic query: select t from t result is of type t. yeah ... If I query: select sq from (select t from t) sq; result is of type record. yeah ... it's a record containing a single field of type t. 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
