Re: [GENERAL] tsearch strategy for incremental search
Pierre,
you, probably, can use custom configuration, which uses pg_3chars
dictionary.
Oleg
On Mon, 30 Jun 2008, Pierre Thibaudeau wrote:
2008/6/30 Oleg Bartunov [EMAIL PROTECTED]:
tsearch will have prefix search support in 8.4.
Thanks Oleg! That's fantastic news!
In the meantime, carrying on with my earlier idea, here's the little
function I came up with for extracting the 3-char-lexeme tsvector, in
case anyone's interested:
CREATE OR REPLACE FUNCTION public.to_three_char_tsvector(str text)
RETURNS tsvector AS
$BODY$declare
somerow record;
shortened_text text := '';
BEGIN
FOR somerow IN SELECT regexp_split_to_table(trim(both '\'' from
strip(to_tsvector('simple',str))::text),'\'') AS item LOOP
shortened_text := shortened_text || ' ' ||
COALESCE(substring(somerow.item for 3), '');
END LOOP;
RETURN strip(to_tsvector(shortened_text));
END;$BODY$
LANGUAGE 'plpgsql' IMMUTABLE;
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
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-using cluster
Hello, One of my hd failed recently, so I has to reinstall my system, but my data where on other hd that did not fail. So I want to use that data , I tried initd -D /storage/pgCluster but I get a directory not empty message, of course I want to use that cluster. How could I use that data? Thanks.
Re: [GENERAL] re-using cluster
Fernando Dominguez wrote: Hello, One of my hd failed recently, so I has to reinstall my system, but my data where on other hd that did not fail. So I want to use that data , I tried initd -D /storage/pgCluster but I get a directory not empty message, of course I want to use that cluster. How could I use that data? First, take a copy of the cluster before doing anything else. Then: if your new OS has the same version of PostgreSQL installed, and is on the same architecture (ie both old and new are 32 bit) then you should be able to simply start the postmaster with the old cluster using pg_ctl then dump the database. You don't need the same point release of PostgreSQL - for example, 8.2.7 can read a cluster created by 8.2.1, but 8.3.1 cannot read it. If you now have a newer version of PostgreSQL you will need to compile an older version to read your cluster, dump it, and then load it into the new version of PostgreSQL. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Postgresql 8.3 Installer issue
Hi, I am trying to build a new installer application. I am in the process of upgrading postgresql 8.1 to 8.3.3 but i am having a issue which i can't seemed to resolve. Error output from rpmbuilder + su -c - user'$RPM_BUILD_ROOT/usr/local/app/pgsql/bin/postmaster -D $RPM_BUILD_ROOT/usr/local/app/pgsql/data -S ' /var/tmp/app-root/usr/local/app/pgsql/bin/postmaster: option requires an argument -- S Try postmaster --help for more information. error: Bad exit status from /var/tmp/rpm-tmp.12297 (%install) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] out of balance result on select from suspected index corruption
Hello, PG: 8.2.7 (then upgraded to 8.2.9 to try and resolve with same result) Linux 2.6.25 Our selects which have run normally for a very long time suddenly started: - consuming all memory. - crashing (oom) if the select was run directly. - producing out of balance results in one of the column results if run from within a function. In an effort to resolve, I upgraded to 8.2.9, which yielded the same results. VACUUM [full analyze] on the db or suspect table resulted in same as above. Eventually, I tried reindexing the table, which also failed as above. The workaround was to manually drop each index from the offending table, then re-create them. The weird out of balance message in one of the column results (ie, instead of 'bob' as a result for that column, it would simply be out of balance). Sorry I can't provide more detail since this happened yesterday as we're preparing for the month-end billing run (and getting things back online was critical) :-( Regards Henry -- 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] freebsd + postgresql 8.3.3 = 100% of cpu usage on stats collector?
On Sun, Jun 29, 2008 at 05:13:59PM -0400, Bill Moran wrote: Use the kdump utility to convert the ktrace.out file to something usable. Something like kdump ktrace.txt will probably get you what you want, assuming your ktrace file is ktrace.out. ok. did it. in about 7 seconds, i got 250mb of ktrace.out. ktrace.txt is 176 megabytes. content of this is pretty simple: 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) should i make the ktrace.txt available, or should i do something else to help you ( i mean, the pg-hackers community) diagnose the issue? Best regards, depesz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] allowed variable names in functions?
Hello. I suspect that in a plpgsql function DECLARE c2 REAL; cadiv REAL; works but c2 REAL; c2div REAL; doesn't. Is this true, and if so, what are the rules for the names in the function? I use 8.1. -- 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] allowed variable names in functions?
Hello it works in my 8.1 postgres=# CREATE LANGUAGE plpgsql; CREATE LANGUAGE postgres=# create or replace function foo(a int) returns void as $$declare c2 real; c2div real; begin c2div := 10.9; end; $$ language plpgsql; CREATE FUNCTION postgres=# select foo(10); foo - (1 row) Regards Pavel Stehule 2008/6/30 A B [EMAIL PROTECTED]: Hello. I suspect that in a plpgsql function DECLARE c2 REAL; cadiv REAL; works but c2 REAL; c2div REAL; doesn't. Is this true, and if so, what are the rules for the names in the function? I use 8.1. -- 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] allowed variable names in functions?
Then my assumption was wrong. Here is the entire function and it fails with the names c2,c2div,c3,c3div, but if names are changed, it works! (by works I mean I get the hello lines printed) There is nothing wrong with the select statement either, that works fine if I run it stand-alone, or with the names of c2,c2div,c3,c3div changed. CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$ DECLARE c2 REAL; c2div REAL; c3 REAL; c3div REAL; weights RECORD; tmp RECORD; retval RECORD; t RECORD; BEGIN RAISE NOTICE 'starting...'; FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP RAISE NOTICE 'hello %',tmp.id; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; 2008/6/30 Pavel Stehule [EMAIL PROTECTED]: Hello it works in my 8.1 postgres=# CREATE LANGUAGE plpgsql; CREATE LANGUAGE postgres=# create or replace function foo(a int) returns void as $$declare c2 real; c2div real; begin c2div := 10.9; end; $$ language plpgsql; CREATE FUNCTION postgres=# select foo(10); foo - (1 row) Regards Pavel Stehule 2008/6/30 A B [EMAIL PROTECTED]: Hello. I suspect that in a plpgsql function DECLARE c2 REAL; cadiv REAL; works but c2 REAL; c2div REAL; doesn't. Is this true, and if so, what are the rules for the names in the function? I use 8.1. -- 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] allowed variable names in functions?
By changing c2,c3,etc. to something else xc2,xc3, etc. it worked! So was the problem that I refered to the same names in the SELECT statement? 2008/6/30 A B [EMAIL PROTECTED]: Then my assumption was wrong. Here is the entire function and it fails with the names c2,c2div,c3,c3div, but if names are changed, it works! (by works I mean I get the hello lines printed) There is nothing wrong with the select statement either, that works fine if I run it stand-alone, or with the names of c2,c2div,c3,c3div changed. CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$ DECLARE c2 REAL; c2div REAL; c3 REAL; c3div REAL; BEGIN FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP -- 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] allowed variable names in functions?
am Mon, dem 30.06.2008, um 12:38:40 +0200 mailte A B folgendes: Then my assumption was wrong. Here is the entire function and it fails with the names c2,c2div,c3,c3div, but if names are changed, it works! (by works I mean I get the hello lines printed) There is nothing wrong with the select statement either, that works fine if I run it stand-alone, or with the names of c2,c2div,c3,c3div changed. CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$ DECLARE c2 REAL; c2div REAL; c3 REAL; c3div REAL; weights RECORD; tmp RECORD; retval RECORD; t RECORD; BEGIN RAISE NOTICE 'starting...'; FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP RAISE NOTICE 'hello %',tmp.id; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; Don't use the same names for plpgsql-variables and for column names. 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
Re: [GENERAL] allowed variable names in functions?
I see one big problem. You have colision between column and variable names! When you has SQL inside function use prefix for variables or use qualified names. DECLARE a varchar; BEGIN FOR a IN SELECT a FROM ... -- is bug you have to do DELARE _a varchar; BEGIN FOR _a IN SELECT t.a FROM tab t ... Regards Pavel Stehule 2008/6/30 A B [EMAIL PROTECTED]: Then my assumption was wrong. Here is the entire function and it fails with the names c2,c2div,c3,c3div, but if names are changed, it works! (by works I mean I get the hello lines printed) There is nothing wrong with the select statement either, that works fine if I run it stand-alone, or with the names of c2,c2div,c3,c3div changed. CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$ DECLARE c2 REAL; c2div REAL; c3 REAL; c3div REAL; weights RECORD; tmp RECORD; retval RECORD; t RECORD; BEGIN RAISE NOTICE 'starting...'; FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP RAISE NOTICE 'hello %',tmp.id; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; 2008/6/30 Pavel Stehule [EMAIL PROTECTED]: Hello it works in my 8.1 postgres=# CREATE LANGUAGE plpgsql; CREATE LANGUAGE postgres=# create or replace function foo(a int) returns void as $$declare c2 real; c2div real; begin c2div := 10.9; end; $$ language plpgsql; CREATE FUNCTION postgres=# select foo(10); foo - (1 row) Regards Pavel Stehule 2008/6/30 A B [EMAIL PROTECTED]: Hello. I suspect that in a plpgsql function DECLARE c2 REAL; cadiv REAL; works but c2 REAL; c2div REAL; doesn't. Is this true, and if so, what are the rules for the names in the function? I use 8.1. -- 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] server log files
Hi Experts, I'm using PostgreSQL 8.3.3 and have a question about log files that PostgreSQL generates. There are two ways to specify the log file path 1)pg_ctl start -l filename http://www.postgresql.org/docs/8.3/interactive/app-pg-ctl.html 2)server configuration (postgresql.conf) http://www.postgresql.org/docs/8.3/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHERE I'm now considering the following settings in postgresql.conf. log_destinatio = stderr logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d.log' log_rotation_age = 1d log_truncate_on_rotation = off I just wonder whether I still should continue using 'pg_ctl start -l filename' to keep useful information about PostgreSQL server status. I tried enabling both of two log files but few messages were written to the former one. It seems only messages generated before those parameters are recognized and ones generated by pg_ctl itself are written. For instance, I can see some messages by issueing 'pg_ctl start' while PostgreSQL is already up. If all messages while PostgreSQL server is running are wrintten to the file specified by log_directory and log_filename, I'd like to consider to start up PostgreSQL as follows: $pg_ctl start /dev/null Any comments or suggestions would be greatly appreciated. Thanks in advance, ebi -- 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] allowed variable names in functions?
On Mon, Jun 30, 2008 at 12:44:31PM +0200, A B wrote: By changing c2,c3,etc. to something else xc2,xc3, etc. it worked! So was the problem that I refered to the same names in the SELECT statement? Yes. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] allowed variable names in functions?
Thank you both! :-) -- 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] allowed variable names in functions?
But there not any problem with returning values with code like this DECLARE retval RECORD; retval.c2 := RETRUN NEXT retval; if c2 is a field on some table? 2008/6/30 Karsten Hilbert [EMAIL PROTECTED]: On Mon, Jun 30, 2008 at 12:44:31PM +0200, A B wrote: By changing c2,c3,etc. to something else xc2,xc3, etc. it worked! So was the problem that I refered to the same names in the SELECT statement? Yes. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- 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] allowed variable names in functions?
But there not any problem with returning values with code like this DECLARE retval RECORD; retval.c2 := RETRUN NEXT retval; if c2 is a field on some table? Oh, that seems also to be problematic. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] The tuple structure of a not-yet-assigned record is indeterminate.
In my function I have (kept the important part) CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$ DECLARE retval RECORD; BEGIN some loop retval.jd := tmp.id; retval.d2 := _c2; retval.d3 := _c3; RETURN NEXT retval; end loop return; END; and I get ERROR: record retval is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function foo line 33 at assignment It seems that the line with retval.jd := tmp.id; is where it stops. Any suggestion on how to fix this error? -- 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] The tuple structure of a not-yet-assigned record is indeterminate.
am Mon, dem 30.06.2008, um 13:33:55 +0200 mailte A B folgendes: In my function I have (kept the important part) CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$ DECLARE retval RECORD; BEGIN some loop retval.jd := tmp.id; retval.d2 := _c2; retval.d3 := _c3; RETURN NEXT retval; end loop return; END; and I get ERROR: record retval is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. CONTEXT: PL/pgSQL function foo line 33 at assignment It seems that the line with retval.jd := tmp.id; is where it stops. Any suggestion on how to fix this error? Sure, declare your result like my example: test=# create or replace function ab() returns setof record as $$declare r record; begin select into r 1,2;return next r;end;$$language plpgsql; CREATE FUNCTION test=*# select * from ab() as (i int, j int); i | j ---+--- 1 | 2 (1 row) Regards, 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
Re: [GENERAL] The tuple structure of a not-yet-assigned record is indeterminate.
Sure, declare your result like my example: test=# create or replace function ab() returns setof record as $$declare r record; begin select into r 1,2;return next r;end;$$language plpgsql; Unfortunatly I have not the luxury of creating the record with a single SELECT command. Isn't there a way around this problem? I really do not want to try to write it as a single SELECT command. There must be a way of telling it what structure the record will have? -- 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] Postgresql 8.3 Installer issue
Jamie Deppeler wrote: I am trying to build a new installer application. I am in the process of upgrading postgresql 8.1 to 8.3.3 but i am having a issue which i can't seemed to resolve. Error output from rpmbuilder + su -c - user'$RPM_BUILD_ROOT/usr/local/app/pgsql/bin/postmaster -D $RPM_BUILD_ROOT/usr/local/app/pgsql/data -S ' /var/tmp/app-root/usr/local/app/pgsql/bin/postmaster: option requires an argument -- S Try postmaster --help for more information. error: Bad exit status from /var/tmp/rpm-tmp.12297 (%install) From version 8.2 on, 'postmaster' is a symbolic link to 'postgres'. The '-S' flag has a different meaning there. If you want silent mode, either give postmaster (or postgres) the option '--silent_mode=on' on the command line or add 'silent_mode=on' to postgresql.conf. Yours, Laurenz Albe -- 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] The tuple structure of a not-yet-assigned record is indeterminate.
am Mon, dem 30.06.2008, um 13:57:22 +0200 mailte A B folgendes: Sure, declare your result like my example: test=# create or replace function ab() returns setof record as $$declare r record; begin select into r 1,2;return next r;end;$$language plpgsql; Unfortunatly I have not the luxury of creating the record with a single SELECT command. You have only quotet (and read?) my function, but not the select. Again: test=*# select * from ab() as (i int, j int); i | j ---+--- 1 | 2 (1 row) Look at the part after the function-call... Regards, 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] php + postgresql website ?
i can name a lot of website written in mysql and php. currently, i am planning to use postgresql database for my next project. i know there is a lot of testimonial about postgresql is better than mysql. can anyone please give me an example of website using postgresql database? -- 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] php + postgresql website ?
http://board.crewcial.org currently in flux as we're finishing up a dev version, but we've been running postgres since 7.4, currently on 8.1.. with around 7 million rows of data totaling a few GB. traffic is around 300k hits a day. nothing crazy, but it works really well. - ian On Mon, Jun 30, 2008 at 8:06 AM, paragasu [EMAIL PROTECTED] wrote: i can name a lot of website written in mysql and php. currently, i am planning to use postgresql database for my next project. i know there is a lot of testimonial about postgresql is better than mysql. can anyone please give me an example of website using postgresql database? -- 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] The tuple structure of a not-yet-assigned record is indeterminate.
I did read the select line also, and select * from foo() as (a integer, b integer, c integer); gives me unfortunatly the error ERROR: record retval is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. So you are telling me this is an error that is caused by the way I call the function and not the definition of the function it self? You have only quotet (and read?) my function, but not the select. Again: test=*# select * from ab() as (i int, j int); i | j ---+--- 1 | 2 (1 row) I Look at the part after the function-call... Regards, 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 -- 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] php + postgresql website ?
Le lundi 30 juin 2008, paragasu a écrit : i can name a lot of website written in mysql and php. currently, i am planning to use postgresql database for my next project. i know there is a lot of testimonial about postgresql is better than mysql. can anyone please give me an example of website using postgresql database? This blog entry could be of interest: http://people.planetpostgresql.org/xzilla/index.php?/archives/324-The-Big-Guys.html HTH, -- dim signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] The tuple structure of a not-yet-assigned record is indeterminate.
am Mon, dem 30.06.2008, um 14:25:30 +0200 mailte A B folgendes: I did read the select line also, and select * from foo() as (a integer, b integer, c integer); gives me unfortunatly the error ERROR: record retval is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. Okay, right, i see. You can rewrite your function using IN/OUT-parameters. For instance, your function: CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$ DECLARE retval RECORD; BEGIN some loop retval.jd := tmp.id; retval.d2 := _c2; retval.d3 := _c3; RETURN NEXT retval; end loop return; END; rewrite to: CREATE OR REPLACE FUNCTION foo(OUT jd int, OUT d2 int, OUT d3 int) RETURNS SETOF RECORD AS $$ BEGIN some loop jd := tmp.id; d2 := _c2; d3 := _c3; RETURN NEXT; end loop; return; END; *untested*. Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) NGnuPG-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
Re: [GENERAL] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
On Fri, Jun 27, 2008 at 08:22:35PM +, Ragnar wrote: let us assume your resultset has a a unique column pk, and is ordered on column o: next page select * from foo where (o,pk)(o,?) order by o limit 10; (where the ? is the last pk value in previous select) this method will be able to make use of an index on (o,pk) Hum, I think I must be missing something. I'm not sure why you're comparing 'o' to itself and you're not putting any ordering constraint on the primary key. I think the query should look closer to: SELECT * FROM foo WHERE (o,pk)($1,$2) ORDER BY o,pk LIMIT 10; Or am I going mad? I'm expecting a table structure somewhat like: CREATE TABLE foo ( pkTEXT PRIMARY KEY, value TEXT, o INT NOT NULL ); CREATE INDEX foo_ord_idx ON foo (o,pk); Sam -- 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] libpq block allocated before my malloc handler inits?
All I am doing at the moment is spawning a pool of threads each with their own database connection. I am using PQconnectdb to create the database connection when the offending block is deallocated (this is largely to answer the other email I got with regards to this).I just tried using sslmode=disable in the connection string, to no avail. It still crashes with an invalid free. Perhaps I should try to compile the database with no SSL in it at all? Would that make any difference? Thanks for the input, Rob Stoddard Russell Smith wrote: rob wrote: I am trying to build a small program with libpq as the interface to a Postgre database. I am using the most current version. My program uses malloc and free hooks to manage memory without having to request memory from the system all the time. I expected that the init function (__malloc_initialize_hook) would run before anything else, but after opening a number of connections to the database with PQconnectdb, my program blows up because of a free which refers to a block of memory that wasn't allocated using my malloc function. My program runs without a hitch if I comment out the PQconnectdb function calls. I've experienced an openSSL, libpq + other library using SSL bug recently. Do you get the same crash is you explicitly disable SSL in the connection string? sslmode=disable. Thanks Russell -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query
Hello , I am a beginner for postgresql. I want to activate a constraint for some time and after that I want to deactivate it. Or I want to know whether a particular constraint exists and whether its activated or not. Waiting for yr reply
Re: [GENERAL] allowed variable names in functions?
A B wrote: Here is the entire function and it fails with the names c2,c2div,c3,c3div, but if names are changed, it works! (by works I mean I get the hello lines printed) There is nothing wrong with the select statement either, that works fine if I run it stand-alone, or with the names of c2,c2div,c3,c3div changed. CREATE OR REPLACE FUNCTION foo(pid_ INTEGER) RETURNS void AS $$ DECLARE c2 REAL; c2div REAL; c3 REAL; c3div REAL; weights RECORD; tmp RECORD; retval RECORD; t RECORD; BEGIN RAISE NOTICE 'starting...'; FOR tmp IN SELECT id,c2,c3 FROM Master WHERE pid=pid_ AND c3 !=0 LOOP RAISE NOTICE 'hello %',tmp.id; END LOOP; RETURN; END; $$ LANGUAGE plpgsql; That is because c2 and c3 in the SELECT statement are replaced with the variables before the SQL statement is executed. See the documentation: http://www.postgresql.org/docs/current/static/plpgsql-implementation.html#PLPGSQL-VAR-SUBST You should qualify the column names: SELECT master.id, master.c2, master.c3 FROM master WHERE ... AND master.c3 0 Yours, Laurenz Albe -- 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] php + postgresql website ?
Skype uses PostgreSQL as its backend. http://highscalability.com/skype-plans-postgresql-scale-1-billion-users C.S.Chandrasekkar On Mon, Jun 30, 2008 at 8:06 AM, paragasu [EMAIL PROTECTED] wrote: i can name a lot of website written in mysql and php. currently, i am planning to use postgresql database for my next project. i know there is a lot of testimonial about postgresql is better than mysql. can anyone please give me an example of website using postgresql database? -- 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] The tuple structure of a not-yet-assigned record is indeterminate.
I can try your suggestion, but I'd rather want to know why it doesn't work with the record, when you try to build it your self. It worked fine when you selected into the record. But speaking of that, If I try like you did: SELECT INTO retval some expression build from the variables tmp.id, _c3, _c2 that constitutes a row RETURN NEXT retval; wouldn't that work? I think I need some help with the syntax for the expression above... I will also try to create a datatype of my own and see if that works as a last way out. 2008/6/30 A. Kretschmer [EMAIL PROTECTED]: am Mon, dem 30.06.2008, um 14:25:30 +0200 mailte A B folgendes: I did read the select line also, and select * from foo() as (a integer, b integer, c integer); gives me unfortunatly the error ERROR: record retval is not assigned yet DETAIL: The tuple structure of a not-yet-assigned record is indeterminate. Okay, right, i see. You can rewrite your function using IN/OUT-parameters. For instance, your function: CREATE OR REPLACE FUNCTION foo() RETURNS SETOF RECORD AS $$ DECLARE retval RECORD; BEGIN some loop retval.jd := tmp.id; retval.d2 := _c2; retval.d3 := _c3; RETURN NEXT retval; end loop return; END; rewrite to: CREATE OR REPLACE FUNCTION foo(OUT jd int, OUT d2 int, OUT d3 int) RETURNS SETOF RECORD AS $$ BEGIN some loop jd := tmp.id; d2 := _c2; d3 := _c3; RETURN NEXT; end loop; return; END; *untested*. Hope that helps, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) NGnuPG-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 -- 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] out of balance result on select from suspected index corruption
Henry - Zen Search SA [EMAIL PROTECTED] writes: Our selects which have run normally for a very long time suddenly started: - consuming all memory. - crashing (oom) if the select was run directly. - producing out of balance results in one of the column results if run from within a function. The string out of balance appears nowhere in the PG 8.2.x sources. So I suppose it must have come from some add-on code, or perhaps got inserted on the client side. What data type is that column, and what non-core code is involved? 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] freebsd + postgresql 8.3.3 = 100% of cpu usage on stats collector?
hubert depesz lubaczewski [EMAIL PROTECTED] writes: 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call 58241 postgres CALL poll(0x7fffd4e0,0x1,0x7d0) 58241 postgres RET poll -1 errno 4 Interrupted system call Hmm. The wait-for-input loop in the stats collector assumes that if it gets EINTR, it should just retry the poll() call immediately. Which AFAIK is correct. I think you're looking at a kernel or libc bug here --- somehow the EINTR failure is recurring continuously. It's probably time to ask about this on a FreeBSD-specific list. 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] server log files
EBIHARA, Yuichiro [EMAIL PROTECTED] writes: I'm now considering the following settings in postgresql.conf. log_destinatio = stderr logging_collector = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d.log' log_rotation_age = 1d log_truncate_on_rotation = off I just wonder whether I still should continue using 'pg_ctl start -l filename' to keep useful information about PostgreSQL server status. You probably still want -l just to capture any error occuring during server startup. But once the log collector has started to run, nothing more should get written to that file. If all messages while PostgreSQL server is running are wrintten to the file specified by log_directory and log_filename, I'd like to consider to start up PostgreSQL as follows: $pg_ctl start /dev/null If the postmaster fails to start, you won't know why. 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] Query
see trigger and estatus select tgname,tgenabled from pg_catalog.pg_trigger --- On Mon, 6/30/08, kartik [EMAIL PROTECTED] wrote: From: kartik [EMAIL PROTECTED] Subject: [GENERAL] Query To: pgsql-general@postgresql.org Date: Monday, June 30, 2008, 9:55 AM Hello , I am a beginner for postgresql. I want to activate a constraint for some time and after that I want to deactivate it. Or I want to know whether a particular constraint exists and whether its activated or not. Waiting for yr reply
Re: [GENERAL] The tuple structure of a not-yet-assigned record is indeterminate.
As a final note, it worked fine with a custom data type! :-) No problem returning values (yet) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] multi-word expression full-text searching
Hello all:
I'm testing all full-text searching possibilities of PostgreSQL and...
Is it possible to search for a multi-word expression?
I can search one or more words:
SELECT id FROM document WHERE to_tsvector('english',text) @@
to_tsquery('english','despite');
SELECT id FROM document WHERE to_tsvector('english',text) @@
to_tsquery('english','despite subject');
But it seems not be possible to do a query like:
SELECT id FROM document WHERE to_tsvector('english',text) @@
to_tsquery('english','despite this');
to search the documents in which occur the expression despite this.
This last query gives the following error:
ERROR: syntax error in tsquery: despite this
Is it really impossible to searh a multi-word expression?
Thanks in advance,
Mario Barcala
--
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] Advice Wanted on Selecting Multi-row Data Requests in 10-Row Blocks
On Mon, Jun 30, 2008 at 8:51 AM, Sam Mason [EMAIL PROTECTED] wrote: select * from foo where (o,pk)(o,?) order by o limit 10; Hum, I think I must be missing something. I'm not sure why you're comparing 'o' to itself and you're not putting any ordering constraint on the primary key. I think the query should look closer to: SELECT * FROM foo WHERE (o,pk)($1,$2) ORDER BY o,pk LIMIT 10; Or am I going mad? yes, you are correct. you need to supply at least one value for each ordered field. I think this is what the OP was tring to say. usually it's much simpler than this: select * from foo where pk $1 order by pk limit 1; This will pull up table in pk order which is usually fine. Any ordering will do as long as the combination of fields being ordered are unique. Adding pk as the second criteria is only needed if you want to order by a non duplicate field. If 'o' is a candidate key this is not required. btw, the use of OFFSET for this type of problem is actually fairly terrible...it's almost never a good idea. 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] multi-word expression full-text searching
SELECT id FROM document WHERE to_tsvector('english',text) @@
plainto_tsquery('english','despite this');
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
--
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] tsearch strategy for incremental search
2008/6/30 Oleg Bartunov [EMAIL PROTECTED]: you, probably, can use custom configuration, which uses pg_3chars dictionary. Thanks Oleg. That sounds like a cleverer (and more natural) idea than mine. I am intrigued: is pg_3chars something that's part of the current distribution of postgresql (I did a quick search and found nothing), or are you implying that it would be worth writing a tsearch configuration along the lines of my earlier ideas? -- 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] tsearch strategy for incremental search
On Mon, 30 Jun 2008, Pierre Thibaudeau wrote: 2008/6/30 Oleg Bartunov [EMAIL PROTECTED]: you, probably, can use custom configuration, which uses pg_3chars dictionary. Thanks Oleg. That sounds like a cleverer (and more natural) idea than mine. I am intrigued: is pg_3chars something that's part of the current distribution of postgresql (I did a quick search and found nothing), or are you implying that it would be worth writing a tsearch configuration along the lines of my earlier ideas? it's just a suggestion name :) btw, if you're really lazy you can use dict_regex (http://vo.astronet.ru/arxiv/dict_regex.html) dictionary. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] out of balance result on select from suspected index corruption [RESOLVED]
On Mon, June 30, 2008 4:51 pm, Tom Lane wrote: The string out of balance appears nowhere in the PG 8.2.x sources. So I suppose it must have come from some add-on code, or perhaps got inserted on the client side. What data type is that column, and what non-core code is involved? I have no idea where this comes from - I've grepped globally for that silly string and can't find it. The installation is pretty standard - pg+plsql. Must be something deep which I've forgotten about. The problem was this: a silly SQL error (misuse of OR and missing parentheses) resulted in a massive result set which resulted in OOM - if the select is run manually (no funny out of balance strings). If it's run in a function (which uses a FOR loop, which uses implicit cursors), then the out of balance string is returned for the TEXT column almost immediately. Weird. Anyway, I'm just relieved the solution was fixing my stupid SQL. Regards Henry -- 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] php + postgresql website ?
i am very surprise to see skype use postgresql database at their database. it really make me confident about postgresql. the only problem with postgresql is that not many $5/month hosting out there preinstalled with postgresql. i only have access to postgresql database after i buy a vps hosting somewhere =) thank.. thus website really help me a lot with my decision.. On 6/30/08, Chandra ASGI Tech [EMAIL PROTECTED] wrote: Skype uses PostgreSQL as its backend. http://highscalability.com/skype-plans-postgresql-scale-1-billion-users C.S.Chandrasekkar On Mon, Jun 30, 2008 at 8:06 AM, paragasu [EMAIL PROTECTED] wrote: i can name a lot of website written in mysql and php. currently, i am planning to use postgresql database for my next project. i know there is a lot of testimonial about postgresql is better than mysql. can anyone please give me an example of website using postgresql database? -- 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] out of balance result on select from suspected index corruption [RESOLVED]
Henry - Zen Search SA [EMAIL PROTECTED] writes: The problem was this: a silly SQL error (misuse of OR and missing parentheses) resulted in a massive result set which resulted in OOM - if the select is run manually (no funny out of balance strings). If it's run in a function (which uses a FOR loop, which uses implicit cursors), then the out of balance string is returned for the TEXT column almost immediately. Is it possible the out of balance is actually an expected result from the query --- ie, it's in a table somewhere? In the first case you'd not see it because of the OOM failure, but with a cursor you'd be able to process (at least some of) the query 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] php + postgresql website ?
On Tue, 1 Jul 2008, paragasu wrote: the only problem with postgresql is that not many $5/month hosting out there preinstalled with postgresql. There is a list of hosting providers at http://www.postgresql.org/support/professional_hosting I clicked on the first North American one there, A2 Hosting, and found plans starting at $7/month. hub.org has them starting at $8, there's a lot of places to poke through there. You might not get all the way down to $5 but there are options close to that. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] php + postgresql website ?
On Mon, Jun 30, 2008 at 1:38 PM, paragasu [EMAIL PROTECTED] wrote: i am very surprise to see skype use postgresql database at their database. it really make me confident about postgresql. the only problem with postgresql is that not many $5/month hosting out there preinstalled with postgresql. Be sure and look here: http://www.postgresql.org/support/professional_hosting for companies that host postgresql. There are a few in there that are in the $10 a month range. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Max array size
Hi, Does anyone know the maximum number of elements allowed in a one-dimensional array? It looks like my script may add at least a couple million, not sure if it will hit a limit... -- 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] Max array size
Postgres User [EMAIL PROTECTED] writes: Does anyone know the maximum number of elements allowed in a one-dimensional array? It looks like my script may add at least a couple million, not sure if it will hit a limit... You'd hit the 1GB field size limit before you hit anything else, at least in terms of hard limits. But the *practical* limit is likely to be a whole lot less, especially if you are talking about a variable-width datatype. Indexing into such a large array would perform poorly. I'd counsel redesigning your data representation. You'll be a lot happier with the performance of a table with a couple million rows than with an array with a couple million entries. 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] server log files
Tom, Thanks a lot! 2008/7/1 Tom Lane [EMAIL PROTECTED]: $pg_ctl start /dev/null If the postmaster fails to start, you won't know why. That's enough reason for me to continue using 'pg_ctl start -l'. ebi -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
