Re: [GENERAL] Data cube in PostgreSQL
Gowrishankar L wrote: Hi All, I need to make certain changes to cube.c file which comes with cube contrib ( http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/cube/). I am not sure how to compile it so that I can use those changes in postgresql. Could you help me? Well, the full procedure would be: 1. Download source package of postgresql, untar somewhere, cd to top directory 2. ./configure ; make; make install 3. cd contrib/cube; make; make install 4. start/restart the server 5. Run any .sql scripts you need to in your target database (see README.cube) If you're making changes you might well be restarting PG frequently, so you might want to script that. Does that help? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Raid Chunk Sizes for DSS type DB
On 30.10.2007 03:11, Joshua D. Drake wrote: Ow Mun Heng <[EMAIL PROTECTED]> wrote: It's not an optimal setup but since I only have 3x500G drives to play with, I can't build a Raid10 Uhhh RAID 1 is your best bet. You get fault tolerance (mirrored) plus you get a hot spare (3 drives). This is not true with Linux MD RAID. It might sound scary to most people, but you _can_ have a RAID 10 with only 3 drives. http://en.wikipedia.org/wiki/Non-standard_RAID_levels#Linux_MD_RAID_10 Another thing you want to do is to check if the MD device you created supports barriers. I know MD RAID 1 does, MD RAID 5 does not, I don't know about MD RAID 10. If it does not, make sure you have an UPS. -- Regards, Hannes Dorbath ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Selecting K random rows - efficiently!
2007/10/26, Patrick TJ McPhee <[EMAIL PROTECTED]>: > In article <[EMAIL PROTECTED]>, cluster <[EMAIL PROTECTED]> wrote: > % > How important is true randomness? > % > % The goal is an even distribution but currently I have not seen any way > % to produce any kind of random sampling efficiently. Notice the word > > How about generating the ctid randomly? You can get the number of pages > from pg_class and estimate the number of rows either using the number > of tuples in pg_class or just based on what you know about the data. > Then just generate two series of random numbers, one from 0 to the number > of pages and the other from 1 to the number of rows per page, and keep > picking rows until you have enough numbers. Assuming there aren't too > many dead tuples and your estimates are good, this should retrieve n rows > with roughly n look-ups. > > If your estimates are low, there will be tuples which can never be selected, > and so far as I know, there's no way to construct a random ctid in a stock > postgres database, but apart from that it seems like a good plan. If > efficiency is important, you could create a C function which returns a > series of random tids and join on that. > -- > SELECT id, ... FROM data WHERE id = ANY(ARRAY( SELECT (random()*max_id)::int FROM generate_series(1,20))) LIMIT 1; -- max_id is external constant Pavel Stehule ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Selecting K random rows - efficiently!
> 2007/10/26, Patrick TJ McPhee <[EMAIL PROTECTED]>: >> In article <[EMAIL PROTECTED]>, cluster <[EMAIL PROTECTED]> >> wrote: >> % > How important is true randomness? >> % >> % The goal is an even distribution but currently I have not seen any way >> % to produce any kind of random sampling efficiently. Notice the word >> >> How about generating the ctid randomly? You can get the number of pages >> from pg_class and estimate the number of rows either using the number >> of tuples in pg_class or just based on what you know about the data. >> Then just generate two series of random numbers, one from 0 to the >> number >> of pages and the other from 1 to the number of rows per page, and keep >> picking rows until you have enough numbers. Assuming there aren't too >> many dead tuples and your estimates are good, this should retrieve n >> rows >> with roughly n look-ups. >> >> If your estimates are low, there will be tuples which can never be >> selected, >> and so far as I know, there's no way to construct a random ctid in a >> stock >> postgres database, but apart from that it seems like a good plan. If >> efficiency is important, you could create a C function which returns a >> series of random tids and join on that. >> -- >> > > SELECT id, ... >FROM data > WHERE id = ANY(ARRAY( >SELECT (random()*max_id)::int > FROM generate_series(1,20))) > LIMIT 1; > > -- max_id is external constant > > Pavel Stehule That selects records where the id is one of twenty random numbers between zero and the maximum id. That's not truely random, nor is it completely safe if there are lots of gaps in the values of id. For example, if the lowest id is 5 and the highest is 50040, this will be very likely to generate lots of numbers below 5 and find no records at all. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Checking empty array
Dear all,
What is the correct way to check whether an array is empty?
I have an array which I initialize with '{}' and then do som array_append
under some circonstances. I would like to know whether the array is empty at
the end of the function. I have not found the answer in the help page nor on
the web. I use the keyword isnull but that does not seem to work in all the
cases.
I would appreciate if someone could put this piece of information into the
Postgres documentation.
Here is what I do, in pseudo-code:
myarray varchar[];
myarray := '{}';
FOR ___ LOOP
IF (___) THEN
myarray := array_append(myarray, somestuff);
END IF;
END LOOP;
-- Is my array empty now?
IF (myarray isnull) THEN
__
END IF;
Regards,
Alexis Beuraud
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Checking empty array
Alexis Beuraud wrote:
Dear all,
What is the correct way to check whether an array is empty?
-- Is my array empty now?
IF (myarray isnull) THEN
An empty array isn't null (unknown), it's empty.
Try enquiring about it's size:
SELECT array_dims('{}'::integer[]) is null;
?column?
--
t
You could use array_lower/upper instead too.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Selecting K random rows - efficiently!
2007/10/30, vincent <[EMAIL PROTECTED]>: > > 2007/10/26, Patrick TJ McPhee <[EMAIL PROTECTED]>: > >> In article <[EMAIL PROTECTED]>, cluster <[EMAIL PROTECTED]> > >> wrote: > >> % > How important is true randomness? > >> % > >> % The goal is an even distribution but currently I have not seen any way > >> % to produce any kind of random sampling efficiently. Notice the word > >> > >> How about generating the ctid randomly? You can get the number of pages > >> from pg_class and estimate the number of rows either using the number > >> of tuples in pg_class or just based on what you know about the data. > >> Then just generate two series of random numbers, one from 0 to the > >> number > >> of pages and the other from 1 to the number of rows per page, and keep > >> picking rows until you have enough numbers. Assuming there aren't too > >> many dead tuples and your estimates are good, this should retrieve n > >> rows > >> with roughly n look-ups. > >> > >> If your estimates are low, there will be tuples which can never be > >> selected, > >> and so far as I know, there's no way to construct a random ctid in a > >> stock > >> postgres database, but apart from that it seems like a good plan. If > >> efficiency is important, you could create a C function which returns a > >> series of random tids and join on that. > >> -- > >> > > > > SELECT id, ... > >FROM data > > WHERE id = ANY(ARRAY( > >SELECT (random()*max_id)::int > > FROM generate_series(1,20))) > > LIMIT 1; > > > > -- max_id is external constant > > > > Pavel Stehule > > That selects records where the id is one of twenty random numbers between > zero and the maximum id. That's not truely random, nor is it completely > safe if there are lots of gaps in the values of id. For example, if the > lowest id is 5 and the highest is 50040, this will be very likely to > generate lots of numbers below 5 and find no records at all. > > there is only one safe way ORDER BY random() LIMIT 1; if you know so your id has not uniform distribution, you have to mo: SELECT random()*(max_id - min_id) + min_id This solution is far to ideal, but it is fast and for some purposes enough (web shops, etc) Pavel ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Checking empty array
2007/10/30, Alexis Beuraud <[EMAIL PROTECTED]>:
> Dear all,
>
> What is the correct way to check whether an array is empty?
> I have an array which I initialize with '{}' and then do som array_append
> under some circonstances. I would like to know whether the array is empty at
> the end of the function. I have not found the answer in the help page nor on
> the web. I use the keyword isnull but that does not seem to work in all the
> cases.
> I would appreciate if someone could put this piece of information into the
> Postgres documentation.
>
> Here is what I do, in pseudo-code:
>
> myarray varchar[];
> myarray := '{}';
>
> FOR ___ LOOP
> IF (___) THEN
> myarray := array_append(myarray, somestuff);
> END IF;
> END LOOP;
>
> -- Is my array empty now?
> IF (myarray isnull) THEN
> __
> END IF;
>
IF (array_upper(myarray,1) is null) THEN
END IF;
Regards
Pavel Stehule
> Regards,
> Alexis Beuraud
>
>
> ---(end of broadcast)---
> TIP 1: if posting/reading through Usenet, please send an appropriate
>subscribe-nomail command to [EMAIL PROTECTED] so that your
>message can get through to the mailing list cleanly
>
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Fragments in tsearch2 headline
On 28/10/2007, Oleg Bartunov <[EMAIL PROTECTED]> wrote: > On Sat, 27 Oct 2007, Tom Lane wrote: > > > "Catalin Marinas" <[EMAIL PROTECTED]> writes: > >> Is there an easy way to generate a headline from separate fragments > >> containing the search words and maybe separated by "..."? > > > > Hmm, the documentation for ts_headline claims it does this already: [...] > > However, a quick look at the code suggests this is a lie --- I see no > > evidence whatever that there's any smarts for putting in ellipses. > > Probably documentation is not correct here. 'ellipsis-separated' should be > treated as a general wording. Default highlighting is .. as it > stated below in docs. It seems that I'll have to implement the headline outside the query (Python, in my case). I would use to_tsvector and to_tsquery to generate the lexemes and the work position, add them to a hash table and use the position of the matching lexemes to generate the headline. I could also highlight the full text and generate the headline I want based on it but if I limit the number of excerpts, it gets complicated to avoid the same lexeme being shown in all excerpts. Is a lexeme always a substring of the corresponding token (so that I can use simple regexp)? Any other ideas? Thanks. -- Catalin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Which index can i use ?
Abandoned wrote: Hi.. I want to do index in postgresql & python. My table: id(int) | id2(int) | w(int) | d(int) My query: select id, w where id=x and id2=y (sometimes and d=z) I have too many insert and select operation on this table. And which index type can i use ? Btree, Rtree, Gist or Hash ? Also I want to unique (id, id2).. OK, well a UNIQUE constraint automatically gives you an index, so (id,id2) are already indexed. If you add your own indexes, just use btree (or don't specify anything and let PostgreSQL choose btree for you). The others are all for specialised uses. Now this is my index. is it give me good performance ? CREATE UNIQUE INDEX ind1 ON test USING btree (id, id2) CREATE INDEX ind2 ON test USING btree (id) CREATE INDEX ind3 ON test USING btree (id2) CREATE INDEX ind4 ON test USING btree (w) CREATE INDEX ind5 ON test USING btree (d) This is a lot of indexes. Every index will slow down inserts and updates (the index will need to be updated). So - start with the unique index and see how that works for you. If you find problems with some queries look at using EXPLAIN ANALYZE to see the plan your slow query is using, then we can try again. Oh, and make sure autovacuum is running to keep the planner informed of changes in your database. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Fragments in tsearch2 headline
Catalin,
what is your need ? What's wrong with this ?
postgres=# select ts_headline('1 2 3 4 5 3 4 abc abc 2 3 xyz','2'::tsquery,
'StartSel=...,StopSel=...')
;
ts_headline
---
1 ...2... 3 4 5 3 4 abc abc ...2... 3 xyz
Oleg
On Tue, 30 Oct 2007, Catalin Marinas wrote:
On 28/10/2007, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
On Sat, 27 Oct 2007, Tom Lane wrote:
"Catalin Marinas" <[EMAIL PROTECTED]> writes:
Is there an easy way to generate a headline from separate fragments
containing the search words and maybe separated by "..."?
Hmm, the documentation for ts_headline claims it does this already:
[...]
However, a quick look at the code suggests this is a lie --- I see no
evidence whatever that there's any smarts for putting in ellipses.
Probably documentation is not correct here. 'ellipsis-separated' should be
treated as a general wording. Default highlighting is .. as it
stated below in docs.
It seems that I'll have to implement the headline outside the query
(Python, in my case). I would use to_tsvector and to_tsquery to
generate the lexemes and the work position, add them to a hash table
and use the position of the matching lexemes to generate the headline.
I could also highlight the full text and generate the headline I want
based on it but if I limit the number of excerpts, it gets complicated
to avoid the same lexeme being shown in all excerpts. Is a lexeme
always a substring of the corresponding token (so that I can use
simple regexp)?
Any other ideas?
Thanks.
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
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Fragments in tsearch2 headline
Oleg Bartunov wrote:
Catalin,
what is your need ? What's wrong with this ?
postgres=# select ts_headline('1 2 3 4 5 3 4 abc abc 2 3
xyz','2'::tsquery, 'StartSel=...,StopSel=...')
;
ts_headline
---
1 ...2... 3 4 5 3 4 abc abc ...2... 3 xyz
I think he want's something like: "1 2 3 ... abc 2 3 ..."
A few characters of context around each match and then ... between. Kind
of like grep -C.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] print command in sproc
2007/10/30, Jorge Godoy <[EMAIL PROTECTED]>: > Em Tuesday 23 October 2007 07:17:53 Goboxe escreveu: > > Hi, > > > > What is the equivalent MSSQL 'print' command in pg sproc? > > What does the MSSQL 'print' command "prints"? It prints what you tell it to print :) Goboxe: The key question is, what you need 'print' for? For PL/PgSQL debugging, use RAISE NOTICE - or write your own logging functions If you are using psql, and just want to emit some static text - use '\echo' HTH. -- Filip Rembiałkowski ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Problems with PostgreSQL DBI-Link / DBD-ODBC
Hi David! Thanks for the fast reply. So you mean that I might already have created a connection but am still trying to create the same one again? >>Check whether the existing one works :) So how can I test it? What is the correct way of accessing this data source in a select statement? Concerning the documents, I wouldn't say that they are unclear about make_accessor_functions(). The problem is, that in the dbi-link documents (quote: "Do the following, with the appropriate parameters. "Appropriate parameters" come from the perldoc of the appropriate DBD...") and in the dbi-odbc documents I can't find any information about the correct use of the parameters for the ODBC-connection. That's why I tried to create the connection so many times. Regards, Pit David Fetter wrote: On Mon, Oct 29, 2007 at 03:44:05PM +0100, Pit M. wrote: I have the following problem with PostgreSQL and hope you guys can help me out in this matter: I try to get a remote connection to any database (e.g. MS-SQL or MS Access) using DBI-Link and DBD:ODBC. My problem is, that everything seems fine so far, however I don't know how to use the parameters for "make_accessor_functions()". I searched the net for it, but can only find samples for Perl-Scripting, but I need a sample for using it directly in SQL. This is what I've come up so far. Hope you can point me to the right direction. SELECT make_accessor_functions( 'dbi:ODBC:Northwind::dbi_link.data_source, 'sa'::text, NULL::text, '--- AutoCommit: 1 RaiseError: 1 '::dbi_link.yaml, NULL::dbi_link.yaml, NULL::text, NULL, ::text, 'Northwind'::text ); I have a ODBC connection named "Northwind". The error message I'm always receiving (for the MS-SQL sample) is: ERROR: error from Perl function: error from Perl function: duplicate key violates unique constraint "dbi_connection_data_source_key" at line 94. at line 35. SQL state: XX000 It looks like you're trying to connect the same data_source and user_name twice. Check whether the existing one works :) Are the documents unclear on the idea that you only run make_accessor_functions() once per remote (data_source,user_name) pair per local database? Cheers, David. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Data cube in PostgreSQL
I see a misunderstanding here. cube contrib is for handling geometrical data. EFEU package is OLAP - related, that's entirely different topic. http://www.informit.com/articles/article.aspx?p=24684 2007/10/1, Dimitri Fontaine <[EMAIL PROTECTED]>: > Hi, > > Le Wednesday 26 September 2007 20:58:38 Gowrishankar, vous avez écrit: > > Is there any other way of extending postgresql to include cubes? > > Something like the cube contrib? > http://developer.postgresql.org/cvsweb.cgi/pgsql/contrib/cube/ > > Contribs are part of PostgreSQL sources and maintained as such, but not > included into the 'core' project. They are certainly distributed along with > PostgreSQL in your packaging system of choice. > > Regards, > -- > dim > > ---(end of broadcast)--- > TIP 5: don't forget to increase your free space map settings > -- Filip Rembiałkowski ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Checking empty array
Alexis Beuraud wrote: > What is the correct way to check whether an array is empty? > > Here is what I do, in pseudo-code: > > -- Is my array empty now? > IF (myarray isnull) THEN Try this: IF array_lower(myarray, 1) IS NULL THEN ... END IF Yours, Laurenz Albe ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Collation sequence and use of operatings system's locale
Am Montag, 29. Oktober 2007 schrieb James Gates: > Notice that the results don't match the operating system's (either byte > code or dictionary) sort order for the same locale, or even the C or > POSIX locales. Note that none of the sort orders you showed match an actual Swedish dictionary sort, so something on your system is probably broken. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fragments in tsearch2 headline
On 30/10/2007, Richard Huxton <[EMAIL PROTECTED]> wrote:
> Oleg Bartunov wrote:
> > Catalin,
> >
> > what is your need ? What's wrong with this ?
> >
> > postgres=# select ts_headline('1 2 3 4 5 3 4 abc abc 2 3
> > xyz','2'::tsquery, 'StartSel=...,StopSel=...')
> > ;
> > ts_headline
> > ---
> > 1 ...2... 3 4 5 3 4 abc abc ...2... 3 xyz
>
> I think he want's something like: "1 2 3 ... abc 2 3 ..."
>
> A few characters of context around each match and then ... between. Kind
> of like grep -C.
That's pretty much correct (with the difference that I'd like context
of words rather than lines as in "grep" and StartSel=,
StopSel=).
Since the text I want a headline for might be pretty long (tens of
lines), I'd like to only show the excerpts around the matching words.
Similar to the above example:
select ts_headline('1 2 3 4 5 3 4 abc x y z 2 3', '2 & abc'::tsquery);
should give:
'1 2 3 4 ... 3 4 abc x y'
Currently, if you limit the maximum words so that 'abc' is too far, it
only highlights the first match.
Many of the search engines (including google) show the headline this
way. I think Lucene can do this as well but I've never used it to be
sure.
--
Catalin
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] function and passing the table name to be used with SQL stmnt
kamiseq <[EMAIL PROTECTED]> writes: > select into _id lastval(); > and is that safe operation, can I rely on lastval() that it will > return value of id that was inserted before? You want to use currval() with the sequence name. > can it happened that other user will insert something between my > insert and select? currval() specifically written to be safe in this case. -Doug ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Partitioning: how to exclude unrelated partitions?
Sean Z. wrote:
Hi,
I partitioned a table "events" into 31 tables, based on "day" of
event_time.
I did 3 steps to setup partition, after creating partition tables:
1. Add the constraint to the 31 partition tables like:
ALTER TABLE events_day_1
ADD CONSTRAINT events_day_1_event_time_check CHECK
(date_part('day'::text, event_time) = 1::double precision);
[snip]
Do I miss anything?
Best,
Sean
I believe you can only partition on literal values. You'll probably
need to include a derived 'day' column in your table that you can
populate in the rule. Your query will then need to include the literal
day value in the where clause, rather than the event_time.
Check out the caveats section for partitioning here (bottom of page, 5.9.5):
http://www.postgresql.org/docs/8.2/interactive/ddl-partitioning.html
It's not terribly obvious at first reading, as the focus is more on
querying than designing the table. Maybe that would be worth expanding
on a little in the docs?
Regards,
Paul
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Base Backups from PITR Standby
On 29-Oct-07, at 11:06 PM, Tom Lane wrote: Brian Wipf <[EMAIL PROTECTED]> writes: The process I use that leads to the warnings is simple: I use pg_controldata to determine the current checkpoint WAL location of the standby server. I ensure I have this WAL file and all newer WALs. I backup all files under the standby's database cluster directory, including all tablespaces, which are soft linked. I restore the database cluster directory to its new location and create soft links in pg_tblspc to point to the proper tablespace directories just backed up. I create the pg_xlog directory containing an archive_status directory. This seems not exactly per spec. Why are you not using pg_start_backup and pg_stop_backup around the copying of the database files? I'm trying to take a base backup from the standby server in archive recovery mode. I don't believe it's possible to connect to it to issue pg_start_backup/pg_stop_backup. http://www.postgresql.org/docs/current/interactive/warm-standby.html#BACKUP-INCREMENTAL-UPDATED ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problems with PostgreSQL DBI-Link / DBD-ODBC
On Tue, Oct 30, 2007 at 01:12:41PM +0100, Pit M. wrote: > Hi David! > > Thanks for the fast reply. So you mean that I might already have > created a connection but am still trying to create the same one > again? Yes. > >>Check whether the existing one works :) > So how can I test it? Fire up psql and do: SELECT data_source_id, local_schema, data_source FROM dbi_link.dbi_conection; This gives you schemas you have created successfully. You can then check what's in schema foo by doing: \dv foo.* > What is the correct way of accessing this data source in a select > statement? > > Concerning the documents, I wouldn't say that they are unclear about > make_accessor_functions(). The problem is, that in the dbi-link > documents (quote: "Do the following, with the appropriate > parameters. "Appropriate parameters" come from the perldoc of the > appropriate DBD...") and in the dbi-odbc documents I can't find any > information about the correct use of the parameters for the > ODBC-connection. That's why I tried to create the connection so many > times. Ah, I see. I don't have an example of dbd-odbc just yet, but you should be able to write a very short perl program which connects to your data source. http://www.easysoft.com/developer/languages/perl/dbd_odbc_tutorial_part_1.html Once you've verified that that's working, you can set up a new connection. The parameters you pass into $dbh coincide, not coincidentally, with the first inputs to make_accessor_functions :) Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL and AutoCad
If your holy grail is the ability of using infomation to drive drawings I have to ask if you have any idea what that could lead too? - Design productivity would increase by factors of hundreds - perhaps thousands. - Information would be infinitly adaptable. - Structure that information properly and knowedge will result. - We would begin to realize the full potential of computing power. Is that what you were saying?? Bob - Original Message - From: "Richard Broersma Jr" <[EMAIL PROTECTED]> To: ; "Andy" <[EMAIL PROTECTED]> Sent: Monday, October 29, 2007 9:13 PM Subject: Re: [GENERAL] PostgreSQL and AutoCad --- On Thu, 10/25/07, Andy <[EMAIL PROTECTED]> wrote: >> Is there any way of converting text from an AutoCad (.dwg ot .dxf) file into >> a PostgreSQL Database?? Do you want AutoCad to edit the drawings right out of the database? How would you want to put them in/get them out, of the database? I think the more traditional problem is to extract information embedded (within blocks) in a drawing to produce a bill of material. As long as the text is stored in a block it is a trivial task. On the other hand, if the text is free floating in the drawing, finding it is a little more difficult but still possible using lisp or vba. Auto cad has prebuilt tools to extract/link data from blocks to any ODBC compliant database. Of course, the holy grail would be to eliminate auto cad altogether and then render drawings from the data stored in the database. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Fragments in tsearch2 headline
On Tue, 30 Oct 2007, Catalin Marinas wrote:
On 30/10/2007, Richard Huxton <[EMAIL PROTECTED]> wrote:
Oleg Bartunov wrote:
Catalin,
what is your need ? What's wrong with this ?
postgres=# select ts_headline('1 2 3 4 5 3 4 abc abc 2 3
xyz','2'::tsquery, 'StartSel=...,StopSel=...')
;
ts_headline
---
1 ...2... 3 4 5 3 4 abc abc ...2... 3 xyz
I think he want's something like: "1 2 3 ... abc 2 3 ..."
A few characters of context around each match and then ... between. Kind
of like grep -C.
That's pretty much correct (with the difference that I'd like context
of words rather than lines as in "grep" and StartSel=,
StopSel=).
Since the text I want a headline for might be pretty long (tens of
lines), I'd like to only show the excerpts around the matching words.
Similar to the above example:
select ts_headline('1 2 3 4 5 3 4 abc x y z 2 3', '2 & abc'::tsquery);
should give:
'1 2 3 4 ... 3 4 abc x y'
Currently, if you limit the maximum words so that 'abc' is too far, it
only highlights the first match.
ok, then you have to formalize many things - how long should be excerpts,
how much excerpts to show, etc. In tsearch2 we have get_covers() function,
which produces all excerpts like:
=# select get_covers(to_tsvector('1 2 3 4 5 3 4 abc x y z 2 3'),
'2&3'::tsquery);
get_covers
1 {1 2 3 }1 4 5 {2 3 4 abc x y z {3 2 }2 3 }3
(1 row)
Once you formalize your requirements, you can look on it and adapt to your
needs (and share with people). I think it could be nice contrib module.
Many of the search engines (including google) show the headline this
way. I think Lucene can do this as well but I've never used it to be
sure.
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
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] pgsql.broken.csc
Hello, one of our clients is using pgsql 8.1, and recently the site crashed due to lack of hard disk space. Upon inspection a file was found : pgsql.broken.csc (size: > 51 GB) I cannot find any reference to this file on pgsql site or on google. What is this file? Is it related to PGSQL? What caused it to grow to this size? Any help will be appreciated. Thanks Environment: OS: Redhat Linux 4 DB: pgsql 8.1 AppSrvr: Tomat 5.5.9 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] pgsql.broken.csc
M Rather <[EMAIL PROTECTED]> writes: > one of our clients is using pgsql 8.1, and recently the site crashed due > to lack of hard disk space. Upon inspection a file was found : > pgsql.broken.csc (size: > 51 GB) > I cannot find any reference to this file on pgsql site or on google. > What is this file? Is it related to PGSQL? What caused it to grow to > this size? PG would never make a file named that. Did you try looking into the file to see what it contains? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] pgsql.broken.csc
Tom Lane wrote: M Rather <[EMAIL PROTECTED]> writes: one of our clients is using pgsql 8.1, and recently the site crashed due to lack of hard disk space. Upon inspection a file was found : pgsql.broken.csc (size: > 51 GB) I cannot find any reference to this file on pgsql site or on google. What is this file? Is it related to PGSQL? What caused it to grow to this size? PG would never make a file named that. Did you try looking into the file to see what it contains? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings I should have mentioned this in my original email. Unfortunately the file was deleted by the hosting service (our client had OK'd it) to bring the site backup immediately. I may have to see if some other process (valid or malicious) may have created the file. Thanks and Regards -MRather
[GENERAL] Table has type character varying, but query expects character varying
I have the following table in a win32 8.2.2 database: (dumped from pgadmin) CREATE TABLE email_directory ( email_directory_id serial NOT NULL, mailbox_id integer NOT NULL, path character varying(255) NOT NULL, marked_for_deletion boolean NOT NULL DEFAULT false, CONSTRAINT email_directory_pkey PRIMARY KEY (email_directory_id), CONSTRAINT email_directory_mailbox_id_fkey FOREIGN KEY (mailbox_id) REFERENCES mailbox (mailbox_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CASCADE ) WITHOUT OIDS; ALTER TABLE email_directory OWNER TO postgres; GRANT ALL ON TABLE email_directory TO postgres; CREATE UNIQUE INDEX email_directory_mailbox_id_path_idx ON email_directory USING btree (mailbox_id, lower(path::text)); I have the following row in the table: 56, 4,'/Demo', f When I try the following query I get the error below. update email_directory set path='/Something else' where email_directory_id=56 ERROR: attribute 3 has wrong type SQL state: XX000 Detail: Table has type character varying, but query expects character varying. What has gone wrong? Howard www.selestial.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Fragments in tsearch2 headline
This is a nice idea and seems easy to implement. I will try to write
it down and send a patch to the mailing list.
I was also working to add support for phrase search. Currently to
check for phrase you have to match the entire document. It will be
better if a filter like are_words_consecutive(tsvector *t, tsquery *q)
can be added to reduce the number of matching documents before we
actually do the phrase search. Do you think this will improve the
performance of phrase search? If so I will like to write this
function and send a patch.
-Sushant.
On 10/30/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote:
> On Tue, 30 Oct 2007, Catalin Marinas wrote:
>
> > On 30/10/2007, Richard Huxton <[EMAIL PROTECTED]> wrote:
> >> Oleg Bartunov wrote:
> >>> Catalin,
> >>>
> >>> what is your need ? What's wrong with this ?
> >>>
> >>> postgres=# select ts_headline('1 2 3 4 5 3 4 abc abc 2 3
> >>> xyz','2'::tsquery, 'StartSel=...,StopSel=...')
> >>> ;
> >>> ts_headline
> >>> ---
> >>> 1 ...2... 3 4 5 3 4 abc abc ...2... 3 xyz
> >>
> >> I think he want's something like: "1 2 3 ... abc 2 3 ..."
> >>
> >> A few characters of context around each match and then ... between. Kind
> >> of like grep -C.
> >
> > That's pretty much correct (with the difference that I'd like context
> > of words rather than lines as in "grep" and StartSel=,
> > StopSel=).
> >
> > Since the text I want a headline for might be pretty long (tens of
> > lines), I'd like to only show the excerpts around the matching words.
> > Similar to the above example:
> >
> > select ts_headline('1 2 3 4 5 3 4 abc x y z 2 3', '2 & abc'::tsquery);
> >
> > should give:
> >
> > '1 2 3 4 ... 3 4 abc x y'
> >
> > Currently, if you limit the maximum words so that 'abc' is too far, it
> > only highlights the first match.
>
> ok, then you have to formalize many things - how long should be excerpts,
> how much excerpts to show, etc. In tsearch2 we have get_covers() function,
> which produces all excerpts like:
>
> =# select get_covers(to_tsvector('1 2 3 4 5 3 4 abc x y z 2 3'),
> '2&3'::tsquery);
> get_covers
>
> 1 {1 2 3 }1 4 5 {2 3 4 abc x y z {3 2 }2 3 }3
> (1 row)
>
> Once you formalize your requirements, you can look on it and adapt to your
> needs (and share with people). I think it could be nice contrib module.
>
>
> >
> > Many of the search engines (including google) show the headline this
> > way. I think Lucene can do this as well but I've never used it to be
> > sure.
> >
> >
>
> 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
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Table has type character varying, but query expects character varying
Howard Cole <[EMAIL PROTECTED]> writes: > I have the following table in a win32 8.2.2 database: (dumped from pgadmin) > ... > What has gone wrong? You're using a broken release :-( http://www.postgresql.org/docs/8.2/static/release-8-2-3.html regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PG windows service issues
Hi All, I'm new to this list. I've set up postgreSQL on windows about 4 times since the first 8.x series came out. The first time it was 8.0 beta3 IIRC. I believe I've installed another in the 8.0 series, then a 8.1 then just a couple of weeks ago 8.2.4. Every time I run into the same problem. I get the thing to work, find out I have to hack a couple of conf files to allow the port to work and then when I close my machine, trying to restart the service fails. (I know a server isn't meant to be shutdown like a desktop machine, but I'm trying out PG on my desktop ...) The only way I found to get rid of that problem is to reinitialize the password of the account that runs the service *every time I reboot my machine*. Then I can start the service. This is not only cumbersome, but it is outright dangerous. I wonder why this happens. I've installed a lot of software from the linux world as services (e.g.Zope) and I never encountered such problems. How can I get rid of those issues ? Sorry for the slightly whining tone of this email (I do like postgreSQL!), but I figured those types of issues had some time to settle since the 8.x series came out and they didn't. TIA, from a user who wants to bring folks to postgreSQL on windows, Yves Moisan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Fragments in tsearch2 headline
> On 10/30/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: >> ... In tsearch2 we have get_covers() function, >> which produces all excerpts like: I had not realized till just now that the 8.3 core version of tsearch omitted any material feature of contrib/tsearch2. Why was get_covers() left out? regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] active connections
Hi, I would like to know how many active connections exist. Is necessary show the number ip of client. Please, somebody knows? Thanks! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] active connections
am Tue, dem 30.10.2007, um 14:51:33 -0300 mailte João Paulo Zavanela folgendes: > Hi, > > I would like to know how many active connections exist. > Is necessary show the number ip of client. ask pg_stat_activity (select * from pg_stat_activity;) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Updated .vim file
Does anyone have a .vim file that takes dollar quoting into account? I've tried the one mentioned at http://archives.postgresql.org/pgsql-general/2006-04/msg01266.php , but it doesn't appear to understand dollar quotes. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 pgpGSrqzbdYLx.pgp Description: PGP signature
Re: [GENERAL] Base Backups from PITR Standby
On Tue, 2007-10-30 at 08:25 -0600, Brian Wipf wrote: > I'm trying to take a base backup from the standby server in archive > recovery mode. I don't believe it's possible to connect to it to issue > pg_start_backup/pg_stop_backup. > > http://www.postgresql.org/docs/current/interactive/warm-standby.html#BACKUP-INCREMENTAL-UPDATED It's safest to shutdown the standby first, take a backup then crank it up again. It's possible to do it online in the way you suggest, but only when running with either full_page_writes = on or when making the backup with rsync, or another method that uses atomic 8Kb reads. The backup is only valid if you rollforward past the next restartpoint after the backup completes, which is not technically the right place but definitely far enough. So its not exactly for the feint-hearted, at the moment. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Fragments in tsearch2 headline
On Tue, 30 Oct 2007, Tom Lane wrote: On 10/30/07, Oleg Bartunov <[EMAIL PROTECTED]> wrote: ... In tsearch2 we have get_covers() function, which produces all excerpts like: I had not realized till just now that the 8.3 core version of tsearch omitted any material feature of contrib/tsearch2. Why was get_covers() left out? That time we considered it as developers function useful only for debugging. 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] PG windows service issues
Yves Moisan wrote: Hi All, I'm new to this list. I've set up postgreSQL on windows about 4 times since the first 8.x series came out. The first time it was 8.0 beta3 IIRC. I believe I've installed another in the 8.0 series, then a 8.1 then just a couple of weeks ago 8.2.4. Every time I run into the same problem. I get the thing to work, find out I have to hack a couple of conf files to allow the port to work and then when I close my machine, trying to restart the service fails. (I know a server isn't meant to be shutdown like a desktop machine, but I'm trying out PG on my desktop ...) The only way I found to get rid of that problem is to reinitialize the password of the account that runs the service *every time I reboot my machine*. Then I can start the service. This is not only cumbersome, but it is outright dangerous. I wonder why this happens. I've installed a lot of software from the linux world as services (e.g.Zope) and I never encountered such problems. How can I get rid of those issues ? Sorry for the slightly whining tone of this email (I do like postgreSQL!), but I figured those types of issues had some time to settle since the 8.x series came out and they didn't. TIA, from a user who wants to bring folks to postgreSQL on windows, Yves Moisan ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq The only similar issues I have seen was where the user was created for the DB with the user must change password at next login option and this caused some issues? Also if I removed and reinstalled PostgreSQL in some of the older 8.0.X?? versions it complained as it tried to create the postgres user account when it already existed. I am installing PostgreSQL 8.0.x 8.1.x and 8.2.x on Windows 2000 Pro and Server, Windows XP and Windows 2003 Server without issue. Are you deinstalling and reinstalling on the same machine over and over? Does the postgres windows user still exist after the deinstall? Oisin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] PostgreSQL and AutoCad
As I have a vested interest in storing AutoCad stuff in PostgreSQL, I searched for something like this a while ago and I ran across this.. I haven't really had a chance to play with it yet http://sourceforge.net/projects/dxf2postgis/ I'm personally interested in the idea of versioning for a drawing. Instead of storing the entire drawing for each version, one could theoretically just store the vector additions/changes/deletions that happen from one revision to the next. -- Ilan On Oct 30, 2007, at 11:34 AM, Bob Pawley wrote: If your holy grail is the ability of using infomation to drive drawings I have to ask if you have any idea what that could lead too? - Design productivity would increase by factors of hundreds - perhaps thousands. - Information would be infinitly adaptable. - Structure that information properly and knowedge will result. - We would begin to realize the full potential of computing power. Is that what you were saying?? Bob - Original Message - From: "Richard Broersma Jr" <[EMAIL PROTECTED]> To: ; "Andy" <[EMAIL PROTECTED]> Sent: Monday, October 29, 2007 9:13 PM Subject: Re: [GENERAL] PostgreSQL and AutoCad --- On Thu, 10/25/07, Andy <[EMAIL PROTECTED]> wrote: >> Is there any way of converting text from an AutoCad (.dwg ot .dxf) file into >> a PostgreSQL Database?? Do you want AutoCad to edit the drawings right out of the database? How would you want to put them in/get them out, of the database? I think the more traditional problem is to extract information embedded (within blocks) in a drawing to produce a bill of material. As long as the text is stored in a block it is a trivial task. On the other hand, if the text is free floating in the drawing, finding it is a little more difficult but still possible using lisp or vba. Auto cad has prebuilt tools to extract/link data from blocks to any ODBC compliant database. Of course, the holy grail would be to eliminate auto cad altogether and then render drawings from the data stored in the database. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Ilan Volow "Implicit code is inherently evil, and here's the reason why:"
Re: [GENERAL] Base Backups from PITR Standby
On 30-Oct-07, at 2:42 PM, Simon Riggs wrote: It's safest to shutdown the standby first, take a backup then crank it up again. It's possible to do it online in the way you suggest, but only when running with either full_page_writes = on or when making the backup with rsync, or another method that uses atomic 8Kb reads I'm using rsync with the -a flag (equivalent to -rlptgoD) I can give rsync a block size with the -B flag. According to the rsync man page, "[the blocksize] is normally selected based on the size of each file being updated". Maybe this is all I need to set. I'll give that a try. . The backup is only valid if you rollforward past the next restartpoint after the backup completes I can ensure this is done. , which is not technically the right place but definitely far enough. So its not exactly for the feint-hearted, at the moment. Thanks for the input Simon, that helps a lot. If I can't get the online backup of the warm standby to work properly, I'll simply keep two warm standby's replaying WALs on the backup server. I'll have one installation continually replaying WALs for failover and a second which can be shut down periodically for taking base backups. Brian Wipf <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] PostgreSQL and AutoCad
Ilan Volow wrote on 30.10.2007 23:01: I'm personally interested in the idea of versioning for a drawing. Instead of storing the entire drawing for each version, one could theoretically just store the vector additions/changes/deletions that happen from one revision to the next. Which could also be done using a versioning system like Subversion which will also only store the difference between two files (especially if they are text files) Thomas ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SSL Connectivity on Solaris 10 x86
I am using md5. OK. thanks for the clue... Now, for the root certificate anyone? :) regards, dotyet On 10/30/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > "Dot Yet" <[EMAIL PROTECTED]> writes: > > I am seeing this "Connection reset by peer" message in the > > postmaster.logfile, but the connection between the client and server > > does get established > > and works fine too... > > What pg_hba.conf setup are you using? I'm thinking that this might be > expected behavior if it's password-based, because psql drops the > connection before prompting the user for a password when the server > tells it a password is needed. > > regards, tom lane >
Re: [GENERAL] PostgreSQL and AutoCad
Thanks Ilan this looks promising. Bob - Original Message - From: Ilan Volow To: pgsql-general@postgresql.org Sent: Tuesday, October 30, 2007 3:01 PM Subject: Re: [GENERAL] PostgreSQL and AutoCad As I have a vested interest in storing AutoCad stuff in PostgreSQL, I searched for something like this a while ago and I ran across this.. I haven't really had a chance to play with it yet http://sourceforge.net/projects/dxf2postgis/ I'm personally interested in the idea of versioning for a drawing. Instead of storing the entire drawing for each version, one could theoretically just store the vector additions/changes/deletions that happen from one revision to the next. -- Ilan On Oct 30, 2007, at 11:34 AM, Bob Pawley wrote: If your holy grail is the ability of using infomation to drive drawings I have to ask if you have any idea what that could lead too? - Design productivity would increase by factors of hundreds - perhaps thousands. - Information would be infinitly adaptable. - Structure that information properly and knowedge will result. - We would begin to realize the full potential of computing power. Is that what you were saying?? Bob - Original Message - From: "Richard Broersma Jr" <[EMAIL PROTECTED]> To: ; "Andy" <[EMAIL PROTECTED]> Sent: Monday, October 29, 2007 9:13 PM Subject: Re: [GENERAL] PostgreSQL and AutoCad --- On Thu, 10/25/07, Andy <[EMAIL PROTECTED]> wrote: >> Is there any way of converting text from an AutoCad (.dwg ot .dxf) file into >> a PostgreSQL Database?? Do you want AutoCad to edit the drawings right out of the database? How would you want to put them in/get them out, of the database? I think the more traditional problem is to extract information embedded (within blocks) in a drawing to produce a bill of material. As long as the text is stored in a block it is a trivial task. On the other hand, if the text is free floating in the drawing, finding it is a little more difficult but still possible using lisp or vba. Auto cad has prebuilt tools to extract/link data from blocks to any ODBC compliant database. Of course, the holy grail would be to eliminate auto cad altogether and then render drawings from the data stored in the database. :-) Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings Ilan Volow "Implicit code is inherently evil, and here's the reason why:"
[GENERAL] generating UPDATE SET ... WHERE PK = ...
Hi, I would like to generate with a request a SQL order like UPDATE tab1 SET col_a = ?, col_b = ? ... WHERE pk = ? for each table of a given schema (in fact what I want is slightly more complicated but if I can write the above, I will be able to get the rest). Will anyone be kind enough to share a piece of code doing this (from pgAdmin may be) ? Cheers, [EMAIL PROTECTED] The Computing Froggy _ Ne gardez plus qu'une seule adresse mail ! Copiez vos mails vers Yahoo! Mail
Re: [GENERAL] Delete/Update with order by
On Thu, Oct 25, 2007 at 09:22:02AM -0300, Evandro Andersen wrote: > In Oracle you can use this: > > DELETE FROM A WHERE A1 = 10 ORDER BY A2 > > There is something in the Postgresql ? Yes. DELETE...USING :) http://www.postgresql.org/docs/current/static/sql-delete.html Cheers, David. -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: [EMAIL PROTECTED] Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL]
Hi all, I was trying to create function in postgres, but it returns error mentioning the language is NOT defined. The function is as following: CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$ DECLARE d_word ALIAS FOR $1; d_phrase ALIAS FOR $2; BEGIN IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0; RETURN 1; END; $$ LANGUAGE plpgsql; ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. I was wonderring why it is not included by default? Or have I missed out something in the configuration! Also, how to do a better text search? I have come across the bad performance of LIKE statement. All helps are appreciated. Thanks. _ Manage multiple email accounts with Windows Live Mail! http://www.get.live.com/wl/all ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL]
carter ck wrote: > Hi all, > > I was trying to create function in postgres, but it returns error mentioning > the language is NOT defined. > > The function is as following: > > CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$ > DECLARE > d_word ALIAS FOR $1; > d_phrase ALIAS FOR $2; > BEGIN > IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0; > > RETURN 1; > > END; > > $$ LANGUAGE plpgsql; > > ERROR: language "plpgsql" does not exist > HINT: Use CREATE LANGUAGE to load the language into the database. > > I was wonderring why it is not included by default? Or have I missed out > something in the configuration! > > According to the documentation, you have to explicitly create the language in order to register the language with the database: CREATE LANGUAGE plpgsql; Assuming everything else is set up properly, this will allow you to use the plpgsql language. Ron ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL]
On Wed, Oct 31, 2007 at 11:07:36AM +0800, carter ck wrote: > Hi all, > > I was trying to create function in postgres, but it returns error mentioning > the language is NOT defined. > ERROR: language "plpgsql" does not exist > HINT: Use CREATE LANGUAGE to load the language into the database. > > I was wonderring why it is not included by default? Or have I missed out > something in the configuration! It's included by default, just not enabled by default. Try "create language plpgsql" as administrator. > Also, how to do a better text search? I have come across the bad performance > of LIKE statement. See tsearch2. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL]
You can also create the language in template1 and then you'll have it in any other database you'll create (from template1). Il Wednesday 31 October 2007 08:21:08 Martijn van Oosterhout ha scritto: > On Wed, Oct 31, 2007 at 11:07:36AM +0800, carter ck wrote: > > Hi all, > > > > I was trying to create function in postgres, but it returns error > > mentioning the language is NOT defined. > > > > > ERROR: language "plpgsql" does not exist > > HINT: Use CREATE LANGUAGE to load the language into the database. > > > > I was wonderring why it is not included by default? Or have I missed out > > something in the configuration! > > It's included by default, just not enabled by default. Try "create > language plpgsql" as administrator. > > > Also, how to do a better text search? I have come across the bad > > performance of LIKE statement. > > See tsearch2. > > Have a nice day, -- Reg me Please ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL]
Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 >>> On Wed, Oct 31, 2007 at 4:07 AM, in message <[EMAIL PROTECTED]>, carter ck <[EMAIL PROTECTED]> wrote: > Hi all, > > I was trying to create function in postgres, but it returns error mentioning > the language is NOT defined. > > The function is as following: > > CREATE OR REPLACE FUNCTION test_word_count(TEXT, TEXT) RETURNS INTEGER AS $$ > DECLARE > d_word ALIAS FOR $1; > d_phrase ALIAS FOR $2; > BEGIN > IF d_word IS NULL OR d_phrase IS NULL THEN RETURN 0; > > RETURN 1; > > END; > > $$ LANGUAGE plpgsql; > > ERROR: language "plpgsql" does not exist > HINT: Use CREATE LANGUAGE to load the language into the database. > > I was wonderring why it is not included by default? Or have I missed out > something in the configuration! > > Also, how to do a better text search? I have come across the bad performance > of LIKE statement. Have you tried TSearch2 to do a text search? ---(end of broadcast)--- TIP 6: explain analyze is your friend
