Re: [GENERAL] Installing plpython on 8.4
On Friday 03 July 2009 06:09:37 Scott Bailey wrote: > I'm having trouble installing plpython in 8.4. I tried under Windows > (one click installer from EDB) and under Ubuntu (linux binary). In both > cases I was told: > could not load library 8.4/lib/postgresql/plpython.(so|dll) > > Both systems have python 2.5 installed. And plpython was working in 8.3 > (and I believe 8.4 B1) on both. Any ideas what I'm doing wrong? I tried with the 8.4.0 Debian package and it worked. Could you start by describing exactly what you entered and the exact output and error message that came back? -- 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] Problem search on text arrays, using the overlaps (&&) operator
John Cheng schrieb: --- For some reason, I am seeing a big difference in our real database. I don't want to just rewrite all of our queries yet. I'm guessing the data makes a big difference. What would be a good way to examine the data to figure out what's the best way to write our queries? Is there any features in PostgreSQL that can help me improve the performance? Any advice would be greatly appreciated! Hi, did you think about using the fulltext search integrated up from version 8.3. I never used your approach and don't know if the fulltextsearch is suitable for your case ... just a hint. http://www.postgresql.org/docs/8.4/interactive/textsearch.html Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Howto get the contents of mystopwords/dictionary/synonym/thesaurus ?
Hi, Is it possible to get an overview/the contents of the stopwords list, dictionary, synonyms or thesaurus using an SQL query, e.g. "SELECT * from stopwords"? Is it possible to add or remove entries from the dictionaries using SQL? TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Group by on %like%
Hi, I would like to run a query and group several rows based on a phone number. However, the same phone number might have a prefix on occasion, example : name | phone_number -- james | 123456 james | 00441234556 james | 555666 sarah | 567890 sarah | 567890 as you can see, the first 2 James seems to belong together. running select name, phone_number from relation group by name, phone_number would not reflect this. I don't think there is a way to run something similar to this : select name, phone_number from relation group by name, %phone_number% // or similar However, I believe there is a way, so I would like to here it from you :) Functions, sums .. please let me know.. Thank you in advance / Jennifer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Group by on %like%
What is the output you are trying to achieve? > However, the same phone number might have a prefix on occasion, example : > > name | phone_number > -- > james | 123456 > james | 00441234556 > james | 555666 > sarah | 567890 > sarah | 567890 > > as you can see, the first 2 James seems to belong together. > > running > > select name, phone_number from relation group by name, phone_number > > would not reflect this. > > I don't think there is a way to run something similar to this : > > select name, phone_number from relation group by name, %phone_number% > // or similar > > However, I believe there is a way, so I would like to here it from you :) > > Functions, sums .. please let me know.. > > Thank you in advance / Jennifer > > -- > 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] Vacation reply
Could someone look into this? Since I do not believe we should condone this. This is what I got when I sent a message to the list. At least, I do not like these personally. If I was wrong with this, then I apologise up front. If I need to send these kinds of remarks elsewhere, please provide me with the correct information Regards, Serge Fonville On Fri, Jul 3, 2009 at 11:52 AM, wrote: > Dear Friends: > > We are a large wholesaler who mainly sell electrical products > such as laptop,TV,digital camera, mobile, Digital Video, Mp4, GPS, and so > on. And our official web is fcxqrz.com We offer you the products with the > best quality and price .All the items on our website are brand new in sealed > factory box and offered warranty by the original manufactures . > > > > Email: fcxqr...@188.com > > MSN : fcx...@hotmail.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] Group by on %like%
On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey wrote: > Hi, > > I would like to run a query and group several rows based on a phone number. > > However, the same phone number might have a prefix on occasion, example : > > name | phone_number > -- > james | 123456 > james | 00441234556 > james | 555666 > sarah | 567890 > sarah | 567890 > > as you can see, the first 2 James seems to belong together. > > running > > select name, phone_number from relation group by name, phone_number > > would not reflect this. > > I don't think there is a way to run something similar to this : > > select name, phone_number from relation group by name, %phone_number% > // or similar > > However, I believe there is a way, so I would like to here it from you :) > > Functions, sums .. please let me know.. > > Thank you in advance / Jennifer You could run a sub-select first to get your results and then group on that, such as: SELECT name, pn FROM (SELECT name, substring(phone_number from length(phone_number)-7) AS pn FROM relation WHERE phone_number LIKE '%1234%') AS r GROUP BY name,pn The substring bit is the part you will have to work out in order to make sure you get the correct rows returning you are looking for. This is just an example :) Regards, GF
Re: [GENERAL] Group by on %like%
On Fri, Jul 3, 2009 at 8:32 PM, Guy Flaherty wrote: > > > On Fri, Jul 3, 2009 at 7:44 PM, Jennifer Trey wrote: > >> Hi, >> >> I would like to run a query and group several rows based on a phone >> number. >> >> However, the same phone number might have a prefix on occasion, example : >> >> name | phone_number >> -- >> james | 123456 >> james | 00441234556 >> james | 555666 >> sarah | 567890 >> sarah | 567890 >> >> as you can see, the first 2 James seems to belong together. >> >> running >> >> select name, phone_number from relation group by name, phone_number >> >> would not reflect this. >> >> I don't think there is a way to run something similar to this : >> >> select name, phone_number from relation group by name, %phone_number% >> // or similar >> >> However, I believe there is a way, so I would like to here it from you :) >> >> Functions, sums .. please let me know.. >> >> Thank you in advance / Jennifer > > > You could run a sub-select first to get your results and then group on > that, such as: > > SELECT name, pn > FROM > (SELECT name, substring(phone_number from length(phone_number)-7) AS pn > FROM relation > WHERE phone_number LIKE '%1234%') AS r > GROUP BY name,pn > Blah, having said that, you are probably looking for something more like this: SELECT "name", substring(phone_number from length(phone_number)-7) AS pn FROM relation GROUP BY name,2 GF
Re: [GENERAL] Group by on %like%
Hi, Le 3 juil. 09 à 11:44, Jennifer Trey a écrit : I would like to run a query and group several rows based on a phone number. However, the same phone number might have a prefix on occasion, example : name | phone_number -- james | 123456 james | 00441234556 as you can see, the first 2 James seems to belong together. What I would do is provide a normalize_phone_number(phone_number text), such as it returns the same phone number when given a number with or without international prefix. Then you SELECT name, normalize_phone_number(phone_numer) FROM relation GROUP BY 1, 2; Now you're left with deciding if you prefer to normalize with the prefix or with it stripped, and to invent an automated way to detect international prefixes. The so called prefix project might help you do this if you have a table of known prefixes to strip (or recognize): http://prefix.projects.postgresql.org/ http://prefix.projects.postgresql.org/prefix-1.0~rc1.tar.gz CREATE OR REPLACE FUNCTION normalize_phone_number(text) RETURNS text LANGUAGE PLpgSQL STABLE AS $f$ DECLARE v_prefix text; BEGIN SELECT prefix INTO v_prefix FROM international_prefixes WHERE prefix @> $1; IF FOUND THEN -- we strip the prefix to normalize the phone number RETURN substring($1 from length(v_prefix)); ELSE RETURN $1; END IF; END; $f$; Note: I typed the function definition directly into the Mail composer, bugs are yours :) Regards, -- dim -- 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] Delete triggers order in delete cascade (pg 8.3.7).
On 2009-07-02, Michaël Lemaire wrote: > Richard Huxton wrote: > >> Michaël Lemaire wrote: >> > Hi all. >> > >> > I've come across a problem with delete cascade. >> > >> > I have three tables A, B and C. >> > Table B has a foreign key on A with delete cascade. >> > Table C has a foreign key on B with delete cascade. >> > So, we have this reference chain: C->B->A >> > All three tables have an 'on delete' trigger. >> > >> > My problem is, when I delete a row from A, the delete triggers are >> > fired in the order A then B then C, which is the opposite of what I >> > expected (the row from B should be deleted before the A one, or the >> > reference constraint would break). >> >> The "on delete cascade" are (sort of) implemented with system >> triggers. So deleting a row from A triggers a delete on B where >> fkey=X and so on. >> >> > This happens with 'after' and 'before' triggers. >> > I really need the order to be C then B then A. >> >> Why? What are you trying to do? > > The delete triggers add 'command' rows in another table to notify > another server of data changes (kind of a replication system but with > data convertion). > > This other server's database doesn't have delete cascades (I can't > change this for compatibility with other scripts). So delete commands > must be issued in an order that don't break foreign keys. they come out backwards, live with it. when you select from the command table do order by timestamp_column ascending sequence_column descending and they'll magically come out in the "right" order. -- 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] Problem search on text arrays, using the overlaps (&&) operator
Hi Andreas, I'm afraid fulltext search won't fit our app here. Our application tags each record with "source flags", which is a text[] of strings that describes where the record came from. These flags are already passed into the application when we store the records. So we can simply store them as text[]. Contrast to this, doing a fulltext search would be storing these flags as one single string, then using the to_tsvector() to have PostgreSQL parse it out again. The fulltext search approach doesn't seem to make sense for us. I'm also suspcious that the same type of problem would affect queries on tsvector columns, but I have not tested myself. - Original Message - From: "Andreas Wenk" To: "John Cheng" , "PG-General Mailing List" Sent: Friday, July 3, 2009 2:12:46 AM GMT -08:00 US/Canada Pacific Subject: Re: [GENERAL] Problem search on text arrays, using the overlaps (&&) operator John Cheng schrieb: > --- > For some reason, I am seeing a big difference in our real database. I > don't want to just rewrite all of our queries yet. I'm guessing the > data makes a big difference. What would be a good way to examine the > data to figure out what's the best way to write our queries? Is there > any features in PostgreSQL that can help me improve the performance? > > Any advice would be greatly appreciated! Hi, did you think about using the fulltext search integrated up from version 8.3. I never used your approach and don't know if the fulltextsearch is suitable for your case ... just a hint. http://www.postgresql.org/docs/8.4/interactive/textsearch.html Cheers Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unsubscribe-pattern-allmatching LISTNAME ALL
unsubscribe-pattern-allmatching LISTNAME ALL Ing. Raúl Rojas Galván Administrador de Sistemas
[GENERAL] How to use RETURN TABLE in Postgres 8.4
I'written something like this:
CREATE TABLE "bug_table" (
"id" BIGINT NOT NULL,
test VARCHAR,
CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;
INSERT INTO bug_table (id,test) VALUES (1,'test');
select * from bug_table;
CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (id INT8,
test VARCHAR)
AS $$
BEGIN
-- @todo hide password
RETURN QUERY
SELECT id ,test
FROM bug_table
;
END;
$$
LANGUAGE plpgsql STRICT SECURITY DEFINER;
SELECT * FROM buggy_procedure();
---
it returns 1 but empty row. What is wrong with this?
Regards
Michal Szymanski
http://blog.szymanskich.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] REINDEX "is not a btree"
Hello! I got into a situation I don't know how the get out .. First, I could not access to my biggest database in postgre anymore because it suddenly gave the error (after long time working with no problems) ERROR: could not open relation 1663/392281/530087: No such file or directory After trying with several backups with no success, I did a vacuum and I tried to REINDEX the database (in the standalone back-end). Unfortunately the process was interrupted, and when I tried to start postgres again I got the error: 'SQL select * from pg_database order by datname failed : index "pg_authid_rolname_index" is not a btree" I connected as a standalone mode again to REINDEX the database: pg_ctl stop -D /data/pgsql/data /usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes REINDEX database dbpedia_infoboxes The REINDEX was successful this time but I was still having the "is not a btree" problem, so I tried again with: pg_ctl stop -D /data/pgsql/data /usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes REINDEX SYSTEM dbpedia_infoboxes The process finish, but I was still having the "is not a btree" problem. And even more, now not only the same problem "is not a btree" is still there, but also I can not connect in the standalone mode anymore: bash-3.2$ /usr/bin/postgres -D /data/pgsql/data dbpedia_infoboxes FATAL: index "pg_database_datname_index" is not a btree (I tried with other databases as well and the same) I don't know much about postgre, I have no clue what else I can do. Please, please any help is very very much appreciated I have lots of databases and months of work in postgre (also lots of backups for the data in /data) but I don't know how to make postgres to work again. (it is working in unix red hat). Millions of thanks in advance, solving this problem is crucial for me. Vanessa
Re: [GENERAL] How to use RETURN TABLE in Postgres 8.4
Michal Szymanski writes: > CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (id INT8, > test VARCHAR) > AS $$ > BEGIN > -- @todo hide password > RETURN QUERY > SELECT id ,test > FROM bug_table > ; > END; > $$ > LANGUAGE plpgsql STRICT SECURITY DEFINER; Don't use column names in your functions that are the same as variable or parameter names of the function. This is working basically as if you'd written "SELECT null,null", because the output parameters are still null when the RETURN QUERY is executed. 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] How to use RETURN TABLE in Postgres 8.4
2009/7/3 Tom Lane : > Michal Szymanski writes: >> CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (id INT8, >> test VARCHAR) >> AS $$ >> BEGIN >> -- @todo hide password >> RETURN QUERY >> SELECT id ,test >> FROM bug_table >> ; >> END; >> $$ >> LANGUAGE plpgsql STRICT SECURITY DEFINER; > > Don't use column names in your functions that are the same as variable > or parameter names of the function. This is working basically as if > you'd written "SELECT null,null", because the output parameters are > still null when the RETURN QUERY is executed. > use qualified names instead RETURN QUERY SELECT b.id, b.test FROM bug_table b; regards Pavel Stehule > 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 > -- 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] Group by on %like%
Hello,
Le 3/07/09 12:53, Dimitri Fontaine a écrit :
Hi,
Le 3 juil. 09 à 11:44, Jennifer Trey a écrit :
I would like to run a query and group several rows based on a phone
number.
However, the same phone number might have a prefix on occasion, example :
name | phone_number
--
james | 123456
james | 00441234556
as you can see, the first 2 James seems to belong together.
What I would do is provide a normalize_phone_number(phone_number text),
such as it returns the same phone number when given a number with or
without international prefix.
Then you
SELECT name, normalize_phone_number(phone_numer)
FROM relation
GROUP BY 1, 2;
[...]
The solution suggested by Dimitri Fontaine and based on a customized
function for normalizing phone numbers seems to be a clean one. All the
power is contained in the normalize_phone_number() implementation.
The following query may be an alternative solution that does not require
any tier function except the classic aggregative ones (COUNT(), SUM()):
SELECT P3.name, P3.phone_number
FROM (
SELECT P1.name, P1.phone_number, (
CASE WHEN CHAR_LENGTH(P1.phone_number) >=
CHAR_LENGTH(P2.phone_number)
THEN 1
ELSE 0
END
) AS gec
FROM (
SELECT P01.name, P01.phone_number
FROM pnd AS P01
GROUP BY P01.name, P01.phone_number
) AS P1 INNER JOIN (
SELECT P02.name, P02.phone_number
FROM pnd AS P02
GROUP BY P02.name, P02.phone_number
) AS P2
ON P1.name = P2.name
AND (
CASE WHEN CHAR_LENGTH(P1.phone_number) >=
CHAR_LENGTH(P2.phone_number)
THEN P1.phone_number LIKE ('%'||P2.phone_number)
ELSE P2.phone_number LIKE ('%'||P1.phone_number)
END
)
) AS P3
GROUP BY P3.name, P3.phone_number
HAVING COUNT(*) = SUM(P3.gec)
"pnd" is assumed to be the main table including "name" and
"phone_number" columns. "pnd" is directly used as a table source in
subqueries aliased P1 and P2 and only for those subqueries.
Assuming the starting values in the table "pnd" as following:
name | phone_number
--
james | 123456
james | 0044123456
james | 555666
sarah | 567890
sarah | 567890
(notice that the phone_number of the 2nd row has been adjusted for
similarity to be effective between row 1 and row 2)
The resulting rows from the overall query will be:
name | phone_number
--
james | 0044123456
james | 555666
sarah | 567890
The choice has been made here to keep the longuest phone_number for each
set of similar phone_numbers. The shortest could also be kept if desired.
The overall query implies a few subqueries. Subquery aliased P3 is a
join between P1 and P2, both corresponding to the same subquery. The
difference is in expressing the join conditions: i) on the commun column
"name"; and ii) on the likelihood between phone numbers according to the
length of these latter. Function CHAR_LENGTH() is used instead of
LENGTH() because the first renders the real number of characters whereas
the second gives the number of bytes used to encode the argument.
Table P3 is composed of couples (X, Y) of "name" and "phone_number".
Each couple is associated to the number "gec" resulting from the
counting of phone_numbers Z similar to Y and with CHAR_LENGTH(Y) greater
or equal to CHAR_LENGTH(Z).
Eventually only the rows of P3 for which the sum of "gec" is equal to
the number of rows of P3 where the value of "phone_number" is the same
are kept.
Hoping this alternative solution will help a little (validated with
PostgreSQL 8.3.1).
Regards.
P-S: I think this question might also have interested the PgSQL-SQL
mailing list and posted there.
--
nha / Lyon / France.
--
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 use RETURN TABLE in Postgres 8.4
Actually, since pgsql does not rely on the names but rather the position of the columns returned to fill the returned table, it would be better to use something like CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (rv_id INT8, rv_test VARCHAR) AS $$ BEGIN -- @todo hide password RETURN QUERY SELECT id as t_id, test as t_test FROM bug_table ; END; Unless you code that calls this function has the column names coded with in it, you can also access the data returned using an index, or position, to get the values in the returned recordset. lv_id = rs.column(1) *if not a zero based language*. > Date: Fri, 3 Jul 2009 17:49:42 +0200 > Subject: Re: [GENERAL] How to use RETURN TABLE in Postgres 8.4 > From: pavel.steh...@gmail.com > To: t...@sss.pgh.pa.us > CC: dy...@poczta.onet.pl; pgsql-general@postgresql.org > > 2009/7/3 Tom Lane : > > Michal Szymanski writes: > >> CREATE OR REPLACE FUNCTION buggy_procedure() RETURNS TABLE (id INT8, > >> test VARCHAR) > >> AS $$ > >> BEGIN > >> -- @todo hide password > >> RETURN QUERY > >> SELECT id ,test > >> FROM bug_table > >> ; > >> END; > >> $$ > >> LANGUAGE plpgsql STRICT SECURITY DEFINER; > > > > Don't use column names in your functions that are the same as variable > > or parameter names of the function. This is working basically as if > > you'd written "SELECT null,null", because the output parameters are > > still null when the RETURN QUERY is executed. > > > > use qualified names instead > > RETURN QUERY > SELECT b.id, b.test >FROM bug_table b; > > regards > Pavel Stehule > > > >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 > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Store derived data or use view?
I have a table which stores the absolute longitude of a planetary position, eg: MERCURY --- 157.65 SATURN - 247.65 When 2 planets are a certain distance apart there is an 'aspect', eg. 90 degrees is a "square" aspect I wish to record these aspects for different user profiles and eventually do searches for users who have the same aspect(s). Would it be better, in terms of search speed/efficiency, to calculate and store the aspect data, eg. Mercury/Saturn square, or should I just store the longitude data and create a view with the calculated aspects? I anticipate a large dataset of users so search speed/efficiency is very important. gvim -- 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] Store derived data or use view?
If it's static (i.e. the planets don't move too much, hah), calculate and store. No sense in re-calculating it each and every time. Matthew Hartman Programmer/Analyst Information Management, ICP Kingston General Hospital (613) 549- x4294 > -Original Message- > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general- > ow...@postgresql.org] On Behalf Of gvimrc > Sent: Friday, July 03, 2009 1:08 PM > To: pgsql > Subject: [GENERAL] Store derived data or use view? > > I have a table which stores the absolute longitude of a planetary > position, eg: > > MERCURY > --- > 157.65 > > SATURN > - > 247.65 > > When 2 planets are a certain distance apart there is an 'aspect', eg. 90 > degrees is a "square" aspect > > I wish to record these aspects for different user profiles and eventually > do searches for users who have the same aspect(s). Would it be better, in > terms of search speed/efficiency, to calculate and store the aspect data, > eg. Mercury/Saturn square, or should I just store the longitude data and > create a view with the calculated aspects? I anticipate a large dataset of > users so search speed/efficiency is very important. > > gvim > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general.now. -- 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] Q: data modeling with inheritance
On Thu, Jul 02, 2009 at 01:54:04PM -0700, Reece Hart wrote: > This is a question about data modeling with inheritance and a way to > circumvent the limitation that primary keys are not inherited. I'm missing what you're doing here that foreign keys don't cover. Could you send along your DDL? Just generally, I've only found table inheritance useful for partitioning. "Polymorphic" foreign key constraints can be handled other ways such as the one sketched out below. http://archives.postgresql.org/sfpug/2005-04/msg00022.php Cheers, David. > > I'm considering a project to model genomic variants and their associated > phenotypes. (Phenotype is a description of the observable trait, such as > disease or hair color.) There are many types of variation, many types of > phenotypes, and many types of association. By "type", I mean that they > have distinct structure (column names and inter-row dependencies). The > abstract relations might look like this: > > variant associationphenotype > --- ---- > variant_id - variant_id+--- phenotype_id > genome_idphenotype_id -+short_descr > strand origin_id (i.e., who) long_descr > start_coord ts (timestamp) > stop_coord > > > There are several types of variants, such as insertions, deletions, > inversions, copy-number variants, single nucleotide polymorphisms, > translocations, and unknowable future genomic shenanigans. > > Phenotypes might come from ontologies or controlled vocabularies that > need a graph structure, others domains might be free text. Each is > probably best-served by a subclass table. > > Associations might be quantitative or qualitative, and would come from > multiple origins. > > The problem that arises is the combinatorial nature of the schema design > coupled with the lack of inherited primary keys. In the current state > of PG, one must (I think) make joining tables (association subclasses) > for every combination of referenced foreign keys (variant and phenotype > subclasses). > > So, how would you model this data? Do I ditch inheritance? > > Thanks, > Reece > > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Howto get the contents of mystopwords/dictionary/synonym/thesaurus ?
Joost Kraaijeveld wrote: Hi, Hi, Is it possible to get an overview/the contents of the stopwords list, dictionary, synonyms or thesaurus using an SQL query, e.g. "SELECT * from stopwords"? if I understand correctly, you want to see the content of the dictionarys. No - you can't see the entries with SQL because the data is not stored in the database but in files. Usually you can find the files in /usr/share/postgresql/8.x/tsearch_data/ or /usr/local/share/postgresql/8.x/tsearch_data/ Is it possible to add or remove entries from the dictionaries using SQL? see above ... TIA Cheers Andy -- 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] Store derived data or use view?
i just asked NASA the same question I'll post the answer back to the list.. Martin Gainty We can lick gravity, but sometimes the paperwork is overwhelming. - Wehrner Von Braun __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Fri, 3 Jul 2009 18:08:17 +0100 > From: gvi...@googlemail.com > To: pgsql-general@postgresql.org > Subject: [GENERAL] Store derived data or use view? > > I have a table which stores the absolute longitude of a planetary position, > eg: > > MERCURY > --- > 157.65 > > SATURN > - > 247.65 > > When 2 planets are a certain distance apart there is an 'aspect', eg. 90 > degrees is a "square" aspect > > I wish to record these aspects for different user profiles and eventually do > searches for users who have the same aspect(s). Would it be better, in terms > of search speed/efficiency, to calculate and store the aspect data, eg. > Mercury/Saturn square, or should I just store the longitude data and create a > view with the calculated aspects? I anticipate a large dataset of users so > search speed/efficiency is very important. > > gvim > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Insert movie times and more without leaving Hotmail®. http://windowslive.com/Tutorial/Hotmail/QuickAdd?ocid=TXT_TAGLM_WL_HM_Tutorial_QuickAdd_062009
Re: [GENERAL] PG_DUMP/RESTORE Would like an explanation of these (non-critical) errors
James B. Byrne wrote: Hi, pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 4; 2615 2200 SCHEMA public postgres pg_restore: [archiver (db)] could not execute query: ERROR: cannot drop schema public because other objects depend on it HINT: Use DROP ... CASCADE to drop the dependent objects too. Command was: DROP SCHEMA public; pg_restore: [archiver (db)] could not execute query: ERROR: schema "public" already exists Command was: CREATE SCHEMA public; WARNING: errors ignored on restore: 2 The pg_dump command is: pg_dump --create --format=c --user=postgres --verbose hll_redmine | gzip > /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz && rsync -avz --bwlimit=35 --delete-after --exclude="database.yml" --exclude="*.log" --exclude="*cache" --exclude="*ruby_sess*" /var/data/pas-redmine inet03.mississauga.harte-lyne.ca:/var/data 1> /dev/null --create is not working here because you select a custom format for your dump. --create is only working with plain SQL dumps. The pg_restore command, which generates the error, is: gunzip < /var/data/pas-redmine/db/dump/hll_redmine.pgsql.gz | pg_restore --clean --user=postgres --dbname=hll_redmine ; vacuumdb --user=postgres --full --analyze hll_redmine 1> /dev/null with the --clean parameter you delete existing objects in hll_redmine but there are dependant objects. A common way to avoid this is to drop the whole database first, create a new one and then restore the dump into it. Means use --create instead of --clean. Dropping the database can cause problems because you have to cut all client connections before being able to cut it. So maybe this approach is not working for you. Another idea is not to use any of these parameters and dump only the data. pg_restore -a Cheers Andy -- 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] Store derived data or use view?
In addition to the amount of data that will be captured, you will need take in to consideration how often each user will be accessing this data as well as the number of users. For example, if you have 10 users running the query once an hour every hour of the day, you might get away with calculating the aspect each time the data data is requested. But if you have 100 users wanting the data every 15 minutes, you may find it faster to perform the calculations on the insert of the planetary data. Personally, and since you indicated that there will a large amount of data, I would do the calculations on insert. I am not associated with NASA. From: mgai...@hotmail.com To: gvi...@googlemail.com; pgsql-general@postgresql.org Subject: Re: [GENERAL] Store derived data or use view? Date: Fri, 3 Jul 2009 15:46:50 -0400 i just asked NASA the same question I'll post the answer back to the list.. Martin Gainty We can lick gravity, but sometimes the paperwork is overwhelming. - Wehrner Von Braun __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Fri, 3 Jul 2009 18:08:17 +0100 > From: gvi...@googlemail.com > To: pgsql-general@postgresql.org > Subject: [GENERAL] Store derived data or use view? > > I have a table which stores the absolute longitude of a planetary position, > eg: > > MERCURY > --- > 157.65 > > SATURN > - > 247.65 > > When 2 planets are a certain distance apart there is an 'aspect', eg. 90 > degrees is a "square" aspect > > I wish to record these aspects for different user profiles and eventually do > searches for users who have the same aspect(s). Would it be better, in terms > of search speed/efficiency, to calculate and store the aspect data, eg. > Mercury/Saturn square, or should I just store the longitude data and create a > view with the calculated aspects? I anticipate a large dataset of users so > search speed/efficiency is very important. > > gvim > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general Insert movie times and more without leaving Hotmail®. See how.
Re: [GENERAL] Installing plpython on 8.4
Peter Eisentraut wrote: On Friday 03 July 2009 06:09:37 Scott Bailey wrote: I'm having trouble installing plpython in 8.4. I tried under Windows (one click installer from EDB) and under Ubuntu (linux binary). In both cases I was told: could not load library 8.4/lib/postgresql/plpython.(so|dll) Both systems have python 2.5 installed. And plpython was working in 8.3 (and I believe 8.4 B1) on both. Any ideas what I'm doing wrong? I tried with the 8.4.0 Debian package and it worked. Could you start by describing exactly what you entered and the exact output and error message that came back? On ubuntu from command line: > createlang -h localhost -p 5433 plpythonu template1 createlang: language installation failed: ERROR: could not load library "/opt/postgres/8.4/lib/postgresql/plpython.so": libpython2.3.so.1.0: cannot open shared object file: No such file or directory And in sql: CREATE PROCEDURAL LANGUAGE 'plpythonu' HANDLER plpython_call_handler; ERROR: could not load library "/opt/postgres/8.4/lib/postgresql/plpython.so": libpython2.3.so.1.0: cannot open shared object file: No such file or directory I won't have access to the windows machine until Monday, but the error message was the same except 'so' was 'dll' It looks like it wants Python 2.3 from the error message, I hope that is not the case. -- 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] Q: data modeling with inheritance
On Thu, 2009-07-02 at 19:19 -0700, Nathan Boley wrote: > Is an association, for example, an experiment that establishes a > dependent relationship? So could there be multiple associations > between variant and phenotype? Exactly. You might have one group say that allele X "causes" some trait, whereas another group might report a more precise increase in odds ratio (for example) for the same genotype/phenotype. > Is your concern that the number of joins will grow exponentially in > the number of variants and phenotypes? Not the number of joins, but the number of association subclasses. If I have Nv variant subclasses and Np phenotype subclasses, I'd need Nv * Np association subclasses. Multiply that by the number of association subclasses. > So all variants would be stored in the variants table, all phenotypes are in > the phenotypes table, and you join through association. Thanks. I had considered that too and that's probably what I'll end up using. -Reece
Re: [GENERAL] Q: data modeling with inheritance
On Fri, 2009-07-03 at 11:29 -0700, David Fetter wrote: > I'm missing what you're doing here that foreign keys don't cover. > Could you send along your DDL? No DDL yet... I'm just in the thinking stages. FKs technically would do it, but would become unwieldy. The intention was to have subclasses of each of the variant, association, and phenotype tables. That leads to the polymorphic key problem. > Just generally, I've only found table inheritance useful for > partitioning. "Polymorphic" foreign key constraints can be handled > other ways such as the one sketched out below. That answers the question -- I do want polymorphic foreign keys. Dang. Thanks, Reece
Re: [GENERAL] Q: data modeling with inheritance
On Fri, Jul 03, 2009 at 05:37:20PM -0700, Reece Hart wrote: > On Fri, 2009-07-03 at 11:29 -0700, David Fetter wrote: > > > I'm missing what you're doing here that foreign keys don't cover. > > Could you send along your DDL? > > No DDL yet... I'm just in the thinking stages. FKs technically would > do it, but would become unwieldy. The intention was to have > subclasses of each of the variant, association, and phenotype > tables. That leads to the polymorphic key problem. How many (order of magnitude) are we talking about here? > > Just generally, I've only found table inheritance useful for > > partitioning. "Polymorphic" foreign key constraints can be > > handled other ways such as the one sketched out below. > > That answers the question -- I do want polymorphic foreign keys. > Dang. It solved some real-world problems I had at the time, mostly from the game space. My biology is a little rusty :/ Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Pls help
Hi, I really have a serious problem with this. I hope you could give me some insights on how to possibly solve this problem. I have installed a new copy of postgres in my PC using Windows XP OS and I have copy the data folder in my previous installation from the other PC, my problem is that I cannot access the records that I have copied from the other PC. An error message pops up saying "Cache lookup failed for relation 16410". What does this mean? Please help. I will really appreciate if you could give me some ideas with this. Thanks in advance and God bless.Pls Roseller Romanos STI-Pagadian Gillamac Building, Pagadian City Office Nos: (062) 2144-785 Home Nos: (062) 2144-695 Mobile Nos: 09205302636
Re: [GENERAL] Pls help
2009/7/4 Roseller A. Romanos : > Hi, > > I really have a serious problem with this. I hope you could give me some > insights on how to possibly solve this problem. I have installed a new copy > of postgres in my PC using Windows XP OS and I have copy the data folder in > my previous installation from the other PC, my problem is that I cannot > access the records that I have copied from the other PC. you cannot copy postgres data files from one computer to second. PostgreSQL is not MySQL. Use pg_dump instead or copy statement. 1. on PC1 pg_dump -t yourtable yourdatabase > yourtable.sql 2. copy yourtable.sql file from PC1 to PC2 3. on PC2 psql yourdatabase < yourtable.sql > > An error message pops up saying "Cache lookup failed for relation 16410". > What does this mean? > your data file is not compatible with data dictionary. http://www.postgresql.org/docs/8.3/static/backup.html regards Pavel Stehule > Please help. I will really appreciate if you could give me some ideas with > this. > Thanks in advance and God bless.Pls > > > Roseller Romanos > STI-Pagadian > Gillamac Building, Pagadian City > Office Nos: (062) 2144-785 > Home Nos: (062) 2144-695 > Mobile Nos: 09205302636 > -- 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] Pls help
On Fri, Jul 3, 2009 at 10:40 PM, Pavel Stehule wrote: > 2009/7/4 Roseller A. Romanos : >> Hi, >> >> I really have a serious problem with this. I hope you could give me some >> insights on how to possibly solve this problem. I have installed a new copy >> of postgres in my PC using Windows XP OS and I have copy the data folder in >> my previous installation from the other PC, my problem is that I cannot >> access the records that I have copied from the other PC. > > you cannot copy postgres data files from one computer to second. > PostgreSQL is not MySQL. Use pg_dump instead or copy statement. Well, strictly speaking, if they're from the same architecture, with the same compile time options and the same major version of pgsql* then you can. But you need to shut down the source and destination servers while doing it. * Note that in pgsql the first TWO numbers denote a major version, so 8.1 and 8.2 are NOT the same major version. -- 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] Pls help
2009/7/4 Scott Marlowe : > On Fri, Jul 3, 2009 at 10:40 PM, Pavel Stehule wrote: >> 2009/7/4 Roseller A. Romanos : >>> Hi, >>> >>> I really have a serious problem with this. I hope you could give me some >>> insights on how to possibly solve this problem. I have installed a new copy >>> of postgres in my PC using Windows XP OS and I have copy the data folder in >>> my previous installation from the other PC, my problem is that I cannot >>> access the records that I have copied from the other PC. >> >> you cannot copy postgres data files from one computer to second. >> PostgreSQL is not MySQL. Use pg_dump instead or copy statement. > > Well, strictly speaking, if they're from the same architecture, with > the same compile time options and the same major version of pgsql* > then you can. But you need to shut down the source and destination > servers while doing it. and all your DDL operation are same and executed in same order too > > * Note that in pgsql the first TWO numbers denote a major version, so > 8.1 and 8.2 are NOT the same major version. > -- 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] Pls help
On Sat, Jul 4, 2009 at 12:12 AM, Pavel Stehule wrote: > 2009/7/4 Scott Marlowe : >> On Fri, Jul 3, 2009 at 10:40 PM, Pavel Stehule >> wrote: >>> 2009/7/4 Roseller A. Romanos : Hi, I really have a serious problem with this. I hope you could give me some insights on how to possibly solve this problem. I have installed a new copy of postgres in my PC using Windows XP OS and I have copy the data folder in my previous installation from the other PC, my problem is that I cannot access the records that I have copied from the other PC. >>> >>> you cannot copy postgres data files from one computer to second. >>> PostgreSQL is not MySQL. Use pg_dump instead or copy statement. >> >> Well, strictly speaking, if they're from the same architecture, with >> the same compile time options and the same major version of pgsql* >> then you can. But you need to shut down the source and destination >> servers while doing it. > > and all your DDL operation are same and executed in same order too I'm not sure what you mean. If you shut down both db instances, and copy the files over, there's no DDL involved really. -- 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] Pls help
2009/7/4 Scott Marlowe : > On Sat, Jul 4, 2009 at 12:12 AM, Pavel Stehule wrote: >> 2009/7/4 Scott Marlowe : >>> On Fri, Jul 3, 2009 at 10:40 PM, Pavel Stehule >>> wrote: 2009/7/4 Roseller A. Romanos : > Hi, > > I really have a serious problem with this. I hope you could give me some > insights on how to possibly solve this problem. I have installed a new > copy > of postgres in my PC using Windows XP OS and I have copy the data folder > in > my previous installation from the other PC, my problem is that I cannot > access the records that I have copied from the other PC. you cannot copy postgres data files from one computer to second. PostgreSQL is not MySQL. Use pg_dump instead or copy statement. >>> >>> Well, strictly speaking, if they're from the same architecture, with >>> the same compile time options and the same major version of pgsql* >>> then you can. But you need to shut down the source and destination >>> servers while doing it. >> >> and all your DDL operation are same and executed in same order too > > I'm not sure what you mean. If you shut down both db instances, and > copy the files over, there's no DDL involved really. > When you copy all files, then ok. But when you copy only one data file, then you have a problem. Problem should be different oid of pg_class table Pavel -- 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] Pls help
On Sat, Jul 4, 2009 at 12:22 AM, Pavel Stehule wrote: > 2009/7/4 Scott Marlowe : >> On Sat, Jul 4, 2009 at 12:12 AM, Pavel Stehule >> wrote: >>> 2009/7/4 Scott Marlowe : On Fri, Jul 3, 2009 at 10:40 PM, Pavel Stehule wrote: > 2009/7/4 Roseller A. Romanos : >> Hi, >> >> I really have a serious problem with this. I hope you could give me some >> insights on how to possibly solve this problem. I have installed a new >> copy >> of postgres in my PC using Windows XP OS and I have copy the data folder >> in >> my previous installation from the other PC, my problem is that I cannot >> access the records that I have copied from the other PC. > > you cannot copy postgres data files from one computer to second. > PostgreSQL is not MySQL. Use pg_dump instead or copy statement. Well, strictly speaking, if they're from the same architecture, with the same compile time options and the same major version of pgsql* then you can. But you need to shut down the source and destination servers while doing it. >>> >>> and all your DDL operation are same and executed in same order too >> >> I'm not sure what you mean. If you shut down both db instances, and >> copy the files over, there's no DDL involved really. >> > > When you copy all files, then ok. But when you copy only one data > file, then you have a problem. > > Problem should be different oid of pg_class table Definitely, all files, and blow away the destination first. The OP had said he copied the data directory, which should be more than enough, but if they left old files in place I'm not so sure. -- 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] Pls help
On Fri, Jul 3, 2009 at 10:02 PM, Roseller A. Romanos wrote: > Hi, > > I really have a serious problem with this. I hope you could give me some > insights on how to possibly solve this problem. I have installed a new copy > of postgres in my PC using Windows XP OS and I have copy the data folder in > my previous installation from the other PC, my problem is that I cannot > access the records that I have copied from the other PC. > > An error message pops up saying "Cache lookup failed for relation 16410". > What does this mean? > > Please help. I will really appreciate if you could give me some ideas with > this. Note that this could be a permissions problem. All the files need to belong to whatever user postgres runs as. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
