Re: [GENERAL] Tsearch2 cache lookup problem
Tom Lane wrote:
Richard Huxton dev@archonet.com writes:
Matroska80 wrote:
Hi i have a problem using tsearch2 with postgresql. Executing:
SELECT to_tsquery('default','kj');
return a
cache lookup failed for function 141542.
Try the following:
SELECT oid,* FROM pg_proc WHERE proname='to_tsquery';
That should show whether there is a function with that OID.
My recollection is that older versions of tsearch2 use a configuration
table that stores function OIDs. If you dump and restore that table
verbatim then you'll get failures like this because the new installation
has different OIDs for those functions. See the tsearch2 documentation
for workarounds, but I think you're supposed to install tsearch2 before
you restore the old dump.
Yes, you:
1. Create the db
2. Install tsearch2
3. do a pg_restore -l/-L and remove the tsearch2 elements when restoring.
However, Matroska80 did say that no dump/restore had occurred. That is
right, isn't it?
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] questions?
xia_pw wrote: Hi,guys.Who knows the structure of the pgsql source codes.What I means is that the content of the source codes and its function. The source code is downloadable from the website at http://www.postgresql.org/ and there are details at http://www.postgresql.org/developer/ You may find searching the archives of the hackers list useful too. http://archives.postgresql.org/pgsql-hackers/ HTH -- 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
[GENERAL] (sem assunto)
subscribe end ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] double-quoted field names in pgadmin
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of levi godinez Sent: 09 March 2006 07:39 To: pgsql-general@postgresql.org Subject: [GENERAL] double-quoted field names in pgadmin Hi, How to turn off the double-quoted field names in pgadmin? Don't use object names that need quoting - for example, stcik to lower case letters, numbers and underscores. Regards, Dave ---(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] database/schema level triggers?
Thanks for the info, everyone.Tomislav
Re: [GENERAL] Fixing up a corrupted toast table
On 3/9/06, Tom Lane [EMAIL PROTECTED] wrote: Steve Atkins [EMAIL PROTECTED] writes: make it happy by inserting a dummy row into the toast table (chunk ID as specified in the error, chunk sequence 0, any old data value). Any attempt to touch the toast table gives me: ERROR: cannot change TOAST relation pg_toast_17410 Ugh. Maybe we should allow superusers to do that? Or is it too much of a foot-gun? +1 on allowing superusers to do that. I recently needed it. Especially cool would be if i could simply insert untoasted values there, so i can put some fake values there and detect them later. (As I cannot query 'what table row has toast_oid') -- marko ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Perspective: PostgreSQL usage boon after release of 8.2
On 3/7/06, Ken Johanson [EMAIL PROTECTED] wrote: Just a quick thought, and an possibly over-optimistic forecast: I'm am SOOO looking forward to hearing the beta announcement for 8.2... hopefully that version's still on target for the standard string escape option.. This *one* roadblock will be gone. I have to agree. 8.2 is shaping up to be one of those odd releases is light on the attention grabbing 'enterprise features' but packs lots of quiet usability improvements that benefit everybody. 7.4 was another release like that... Merlin ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Benchmark-Comparison PostGreSQL vs. SQL Server
Background: 1. I read people were using PostGreSQL with TeraBytes of data sometimes, or thousands of users. These are things that could easily break SQL Server. - So I thought PostGreSQL might be similar fast to SQL Server. 2. I did some tests: Windows XP SP2 Several GIGs free harddisk, ~400 MB free RAM Java 1.5 / JDBC PostGreSQL 8.0 beta (through Windows Installer), default configuration, default driver SQL Server 2000 SP3a, default configuration, SQL Server like mysql, is very 'noob friendly'...it doesn't require a lot of configuration to be quick is forgiving of lousy access strategies to the database (these are good things btw). PostgreSQL 8.0b was still using old style win32 fsync which maps to flushfilebuffers()...this is an an ultra conservative way of flushing ot disk so this is not apples to apples comparison. That said, SQL Server will be very hard to beat with write performance on windows because it is very tuned to the win32 api and has very efficient disk access. In the mean time for a fairer test you have to turn fsync off in postgresql.conf and you definately want to be running pg 8.1 which has tons of improvements for win32 and a more efficient syncer...although in windows for production database I would highly suggest targeting a caching raid controller. I am right now in the development planning phase of a PostgreSQL databse which has to scale well into the terabyte range. I can tell you without hesitation that the database can handle it and deserves the reputation of a workhorse that it has. Administrating pg is a bit harder to get into but well help you understand what a database is really all about and how to make your data access elegant and efficient. Merlin ---(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] view creation question
I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a CREATE VIEW myview AS statement?
Re: [GENERAL] view creation question
On Thu, Mar 09, 2006 at 10:55:12AM -0500, Larry White wrote: I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a CREATE VIEW myview AS statement? Use UNION or UNION ALL. http://www.postgresql.org/docs/8.1/interactive/queries-union.html -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] view creation question
On Mar 10, 2006, at 0:55 , Larry White wrote: Is there some way to wrap these two independent queries in a CREATE VIEW myview AS statement? CREATE VIEW myview AS Select name, description, date from Tasks UNION Select name, description, date from Issues; Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] view creation question
Larry White wrote: I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a CREATE VIEW myview AS statement? SELECT * FROM Tasks UNION ALL SELECT * FROM Issues; HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] view creation question
In addition to the other replies (regarding UNION), are you aware that you can use inheritance in postgres to factor out common fields ? Then you could use the parent table to access the common denominator of the children. I'm not sure though if this fits your needs, just worth mentioning. See also: http://www.postgresql.org/docs/8.1/static/tutorial-inheritance.html Cheers, Csaba. On Thu, 2006-03-09 at 16:55, Larry White wrote: I need a read only view that concatenates data from two tables. Basically both tables would need a simple query like Select name, description, date from Tasks; Select name, description, date from Issues; Is there some way to wrap these two independent queries in a CREATE VIEW myview AS statement? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] ExtenDB
Hi All, Does anyone here have any experience with ExtenDB? We were looking into this as a solution for a database server cluster that our company will be putting online this fall and would appreciate any feedback anyone can give concerning this, or any of the other open source cluster solutions. Sincerely, Gary Koskenmaki
[GENERAL] multi-column aggregates
Hello All, Is there any way in postgres to have an aggregate that uses input from two columns without using composite types? The example we are working on is a first or last aggregate which requires a data value and a date column. The result would be to find the latest date within the group and return the associated data value. Since there may be multiple date columns each with dependent columns, we can't use a sort by date and a simpler first or last aggregate. Thanks, -Chris -- Chris Kratz ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] multi-column aggregates
Chris Kratz [EMAIL PROTECTED] writes: Is there any way in postgres to have an aggregate that uses input from two columns without using composite types? No. 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] NULL TIMESTAM problem
Hi! I'm new in Postgres. I nedd to fill a database table x from a file With the COPY command an the delimiter '*'. This table has a timestamp null column (I declared like: ' birthday timestamp NULL' ). But when I try to insert NULL values(specified in the file), postgres throw an error. I don't know how can I specify this NULL value wkthout an '\N' character. Thanks Enrique. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] multi-column aggregates
Thanks Tom, Well for anyone else who may be interested in doing something similar, here is what we did. It does require typecasting going into the functions, composite types and using the dot notation to get the value back out of the composite object returned. But it works. This is what we wanted... select last(cur_date, some_column) from some_table We got this close... select (last((cur_date, some_column)::last_int_agg)).value as last_int from... which I think will be useable for what we need. If anyone has ideas to simplify this, I would appreciate it. Example and generation script attached at end. I do have to say that the flexibility in postgres for creating our own data types and aggregate functions is wonderfull. Kudos again to everyone who has but so much time and energy into postgres. --- -Chris On Thursday 09 March 2006 01:08 pm, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: Is there any way in postgres to have an aggregate that uses input from two columns without using composite types? No. regards, tom lane - simple example test data:: - test=# select id, grouping, cur_date::date, cur_date2::date, integer_column from test_agg_last; id | grouping | cur_date | cur_date2 | integer_column +--+++ 1 |1 | 2006-01-05 | 2006-01-03 | 8 2 |1 | 2006-01-01 | 2006-01-05 | 78 3 |2 | 2006-01-03 | 2006-01-01 | 32 (3 rows) test=# select test-#grouping, test-#(last((cur_date, integer_column)::last_int_agg)).value as last_int, test-#(first((cur_date, integer_column)::last_int_agg)).value as first_int, test-#(last((cur_date2, integer_column)::last_int_agg)).value as last_int2, test-#(first((cur_date2, integer_column)::last_int_agg)).value as first_int2 test-# from test_agg_last test-# group by grouping test-# order by grouping test-# ; grouping | last_int | first_int | last_int2 | first_int2 --+--+---+---+ 1 |8 |78 |78 | 8 2 | 32 |32 |32 | 32 (2 rows) - First and Last aggregates using an arbitrary date column - -- aggregate types create type last_int_agg as (cur_date timestamp, value int); create type last_txt_agg as (cur_date timestamp, value text); create type last_rel_agg as (cur_date timestamp, value double precision); create type last_num_agg as (cur_date timestamp, value numeric(12,2)); create type last_dte_agg as (cur_date timestamp, value date); create type last_tme_agg as (cur_date timestamp, value time); create type last_bln_agg as (cur_date timestamp, value boolean); create type last_ntv_agg as (cur_date timestamp, value interval); -- generic last accumulator function CREATE OR REPLACE function last_accum(anyelement, anyelement) returns anyelement AS $$ BEGIN IF $1.cur_date IS NULL and $2.cur_date IS NOT NULL THEN RETURN $2; ELSEIF $1.cur_date IS NOT NULL and $2.cur_date IS NULL THEN RETURN $1; ELSEIF $2.cur_date$1.cur_date THEN RETURN $2; ELSE RETURN $1; END IF; END; $$ LANGUAGE plpgsql; -- generic first accumulator function CREATE OR REPLACE function first_accum(anyelement, anyelement) returns anyelement AS $$ BEGIN IF $1.cur_date IS NULL and $2.cur_date IS NOT NULL THEN RETURN $2; ELSEIF $1.cur_date IS NOT NULL and $2.cur_date IS NULL THEN RETURN $1; ELSEIF $2.cur_date$1.cur_date THEN RETURN $2; ELSE RETURN $1; END IF; END; $$ LANGUAGE plpgsql; -- last aggregate CREATE AGGREGATE last ( sfunc = last_accum, basetype = anyelement, stype = anyelement ); -- first aggregate CREATE AGGREGATE first ( sfunc = first_accum, basetype = anyelement, stype = anyelement ); -- test data create table test_agg_last( id serial primary key, grouping integer, cur_date timestamp, cur_date2 timestamp, integer_column integer, real_column double precision, currency_column numeric(12,2), text_column text, date_column date, time_column time without time zone, interval_column interval, boolean_column boolean); insert into test_agg_last(grouping, cur_date, cur_date2, integer_column, real_column, currency_column, text_column, date_column, time_column, interval_column, boolean_column) values(1, '1/5/06', '1/3/06', 8, 38.7, '12.00', 'Four score', '12/3/78', '3:32pm', '1 day', true); insert into test_agg_last(grouping, cur_date, cur_date2, integer_column,
Re: [GENERAL] multi-column aggregates
Chris Kratz wrote: Well for anyone else who may be interested in doing something similar, here is what we did. It does require typecasting going into the functions, composite types and using the dot notation to get the value back out of the composite object returned. But it works. This is what we wanted... select last(cur_date, some_column) from some_table We got this close... select (last((cur_date, some_column)::last_int_agg)).value as last_int from... have you looked at new row-wise comparison feature (i might be misunderstanding your problem)? select some_column from some_table where (cur_date, some_column) '01/01/06', ) order by cur_date desc, some_column desc limit 1; this will give you the highest value of some_column on the abitrarily chosen date 01/01/06 (assuming all values of some_column are less than ). Merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] NULL TIMESTAM problem
On Thu, Mar 09, 2006 at 12:49:14PM -0600, Enrique Sánchez wrote: I nedd to fill a database table x from a file With the COPY command an the delimiter '*'. This table has a timestamp null column (I declared like: ' birthday timestamp NULL' ). But when I try to insert NULL values(specified in the file), postgres throw an error. What's the error? How are the NULL values specified in the file? I don't know how can I specify this NULL value wkthout an '\N' character. See the COPY documentation -- COPY has a NULL option that allows you to specify what string should be interpreted as NULL. Recent versions also have a CSV option that interprets empty strings as NULL. http://www.postgresql.org/docs/8.1/interactive/sql-copy.html (Use the documentation for whatever version you're running.) -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Data corruption zero a file - help!!
On Thu, Mar 09, 2006 at 03:57:46PM +1100, Noel Faux wrote: Given that this seems problem has occurred a number of times for a number I've written a small step by step procedure to address this issue. Is there any other comments you which to add. I was thinking that this should be added to the FAQ / troubleshooting in the docs. How to repair corrupted data due to ERROR: invalid page header in block X of relation Y: In pgsql-novice Christopher Goodfellow mentioned the zero_damaged_pages option that I had forgotten about. If you don't care about examining the bad pages then you could set this option in a session and execute a statement that hits every page in the file (Tom Lane mentioned VACUUM and SELECT COUNT(*)). Here's the example I posted in reply: test=# select count(*) from foo; ERROR: invalid page header in block 10 of relation foo test=# set zero_damaged_pages to on; SET test=# select count(*) from foo; WARNING: invalid page header in block 10 of relation foo; zeroing out page WARNING: invalid page header in block 20 of relation foo; zeroing out page WARNING: invalid page header in block 30 of relation foo; zeroing out page count --- 9445 (1 row) test=# set zero_damaged_pages to off; SET -- Michael Fuhr ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] JDBC
I am attempting to connect Postgresql (Windows XP, Version 1.2.1 March 28, 2005 (in the PG Admin 3 about ) to Java Studio Creator 2. Of the four selections presented can someone point to the driver I should use for PG Version 1.2.1??? Bob
Re: [GENERAL] NULL TIMESTAM problem
OK. the error was that: postgress didn't recognize which was the NULLstring, because the format file was: value1*value2**value4, where the delimiter is: * . I learnd the default NULL string is '\N', but as you told me, you can specify this in the command definition. Thanks Michael. From: Michael Fuhr [EMAIL PROTECTED] To: Enrique Sánchez [EMAIL PROTECTED] CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] NULL TIMESTAM problem Date: Thu, 9 Mar 2006 12:26:10 -0700 On Thu, Mar 09, 2006 at 12:49:14PM -0600, Enrique Sánchez wrote: I nedd to fill a database table x from a file With the COPY command an the delimiter '*'. This table has a timestamp null column (I declared like: ' birthday timestamp NULL' ). But when I try to insert NULL values(specified in the file), postgres throw an error. What's the error? How are the NULL values specified in the file? I don't know how can I specify this NULL value wkthout an '\N' character. See the COPY documentation -- COPY has a NULL option that allows you to specify what string should be interpreted as NULL. Recent versions also have a CSV option that interprets empty strings as NULL. http://www.postgresql.org/docs/8.1/interactive/sql-copy.html (Use the documentation for whatever version you're running.) -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] JDBC
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I'm assuming that your real postgres version is 8.1 and that what you're reporting is just 8.1.2-1 release for windows. Thebest place to get JDBC drivers for postgres is http://jdbc.postgresql.org I would recommend downloading the JDBC3 driver for 8.1. Point your web browser to: http://jdbc.postgresql.org/download.html Direct link: http://jdbc.postgresql.org/download/postgresql-8.1-405.jdbc3.jar - -Daniel -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2 (GNU/Linux) iD8DBQFEEIjN9SJ2nhowvKERAjSbAJ4+vEHmZVKc8hfQy263Th5HjsMFaACeLnQ+ mKBUnxCjqviMp7jZ36iGCfg= =bhGA -END PGP SIGNATURE- On 3/9/06, Bob Pawley [EMAIL PROTECTED] wrote: I am attempting to connect Postgresql (Windows XP, Version 1.2.1 March 28, 2005 (in the PG Admin 3 about ) to Java Studio Creator 2. Of the four selections presented can someone point to the driver I should use for PG Version 1.2.1??? Bob
Re: [GENERAL] JDBC
I'll try it. Thanks for your help. Bob - Original Message - From: Daniel Blaisdell To: Bob Pawley Cc: Postgresql Sent: Thursday, March 09, 2006 11:58 AM Subject: Re: [GENERAL] JDBC -BEGIN PGP SIGNED MESSAGE-Hash: SHA1I'm assuming that your real postgres version is 8.1 and that what you're reporting is just 8.1.2-1 release for windows. Thebest place to get JDBC drivers for postgres is http://jdbc.postgresql.orgI would recommend downloading the JDBC3 driver for 8.1.Point your web browser to: http://jdbc.postgresql.org/download.htmlDirect link: http://jdbc.postgresql.org/download/postgresql-8.1-405.jdbc3.jar- -Daniel-BEGIN PGP SIGNATURE-Version: GnuPG v1.4.2 (GNU/Linux)iD8DBQFEEIjN9SJ2nhowvKERAjSbAJ4+vEHmZVKc8hfQy263Th5HjsMFaACeLnQ+mKBUnxCjqviMp7jZ36iGCfg==bhGA-END PGP SIGNATURE- On 3/9/06, Bob Pawley [EMAIL PROTECTED] wrote: I am attempting to connect Postgresql (Windows XP, Version 1.2.1 March 28, 2005 (in the PG Admin 3 about ) to Java Studio Creator 2. Of the four selections presented can someone point to the driver I should use for PG Version 1.2.1??? Bob
Re: [GENERAL] view creation question
Richard Huxton wrote: Larry White wrote: SELECT * FROM Tasks UNION ALL SELECT * FROM Issues; In case you care about where a record originated from: SELECT *, 'Tasks' AS source FROM Tasks UNION ALL SELECT *, 'Issues' AS source FROM Issues; Regards, -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] multi-column aggregates
I may not fully understand your situation but SELECT distinct grouping, (SELECT integer_column FROM test.test_agg_last WHERE grouping=t1.grouping ORDER BY cur_date DESC LIMIT 1) AS last_int, (SELECT integer_column FROM test.test_agg_last WHERE grouping=t1.grouping ORDER BY cur_date ASC LIMIT 1) AS first_int, (SELECT integer_column FROM test.test_agg_last WHERE grouping=t1.grouping ORDER BY cur_date2 DESC LIMIT 1) AS last_int2, (SELECT integer_column FROM test.test_agg_last WHERE grouping=t1.grouping ORDER BY cur_date2 ASC LIMIT 1) AS first_int2 FROM test.test_agg_last t1 produced the same result as at least your first example: 1,8,78,78,8 2,32,32,32,32 I think it does what you are trying to do and can be expanded to your other examples. Maybe not as cool as using composite aggregates, though. -- BMT Chris Kratz wrote: Thanks Tom, Well for anyone else who may be interested in doing something similar, here is what we did. It does require typecasting going into the functions, composite types and using the dot notation to get the value back out of the composite object returned. But it works. This is what we wanted... select last(cur_date, some_column) from some_table We got this close... select (last((cur_date, some_column)::last_int_agg)).value as last_int from... which I think will be useable for what we need. If anyone has ideas to simplify this, I would appreciate it. Example and generation script attached at end. I do have to say that the flexibility in postgres for creating our own data types and aggregate functions is wonderfull. Kudos again to everyone who has but so much time and energy into postgres. --- -Chris On Thursday 09 March 2006 01:08 pm, Tom Lane wrote: Chris Kratz [EMAIL PROTECTED] writes: Is there any way in postgres to have an aggregate that uses input from two columns without using composite types? No. regards, tom lane - simple example test data:: - test=# select id, grouping, cur_date::date, cur_date2::date, integer_column from test_agg_last; id | grouping | cur_date | cur_date2 | integer_column +--+++ 1 |1 | 2006-01-05 | 2006-01-03 | 8 2 |1 | 2006-01-01 | 2006-01-05 | 78 3 |2 | 2006-01-03 | 2006-01-01 | 32 (3 rows) test=# select test-#grouping, test-#(last((cur_date, integer_column)::last_int_agg)).value as last_int, test-#(first((cur_date, integer_column)::last_int_agg)).value as first_int, test-#(last((cur_date2, integer_column)::last_int_agg)).value as last_int2, test-#(first((cur_date2, integer_column)::last_int_agg)).value as first_int2 test-# from test_agg_last test-# group by grouping test-# order by grouping test-# ; grouping | last_int | first_int | last_int2 | first_int2 --+--+---+---+ 1 |8 |78 |78 | 8 2 | 32 |32 |32 | 32 (2 rows) - First and Last aggregates using an arbitrary date column - -- aggregate types create type last_int_agg as (cur_date timestamp, value int); create type last_txt_agg as (cur_date timestamp, value text); create type last_rel_agg as (cur_date timestamp, value double precision); create type last_num_agg as (cur_date timestamp, value numeric(12,2)); create type last_dte_agg as (cur_date timestamp, value date); create type last_tme_agg as (cur_date timestamp, value time); create type last_bln_agg as (cur_date timestamp, value boolean); create type last_ntv_agg as (cur_date timestamp, value interval); -- generic last accumulator function CREATE OR REPLACE function last_accum(anyelement, anyelement) returns anyelement AS $$ BEGIN IF $1.cur_date IS NULL and $2.cur_date IS NOT NULL THEN RETURN $2; ELSEIF $1.cur_date IS NOT NULL and $2.cur_date IS NULL THEN RETURN $1; ELSEIF $2.cur_date$1.cur_date THEN RETURN $2; ELSE RETURN $1; END IF; END; $$ LANGUAGE plpgsql; -- generic first accumulator function CREATE OR REPLACE function first_accum(anyelement, anyelement) returns anyelement AS $$ BEGIN IF $1.cur_date IS NULL and $2.cur_date IS NOT NULL THEN RETURN $2; ELSEIF $1.cur_date IS NOT NULL and $2.cur_date IS NULL THEN RETURN $1; ELSEIF $2.cur_date$1.cur_date THEN RETURN $2; ELSE RETURN $1; END IF; END; $$ LANGUAGE plpgsql; -- last aggregate CREATE AGGREGATE last ( sfunc = last_accum, basetype = anyelement, stype = anyelement ); -- first
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
In article [EMAIL PROTECTED], Roman Neuhauser [EMAIL PROTECTED] wrote: I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. I stopped getting any mail from postgresql.org last fall. Repeated attempts to subscribe have all been met with silence. Something appears to have changed. ---(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] multi-column aggregates
On Thu, Mar 09, 2006 at 12:56:21 -0500, Chris Kratz [EMAIL PROTECTED] wrote: Hello All, Is there any way in postgres to have an aggregate that uses input from two columns without using composite types? The example we are working on is a first or last aggregate which requires a data value and a date column. The result would be to find the latest date within the group and return the associated data value. Since there may be multiple date columns each with dependent columns, we can't use a sort by date and a simpler first or last aggregate. It sounds like you could use the (postgres specific) DISTINCT ON construct to do what you are looking for. Something like: SELECT DISTINCT ON (groupcol) datacol ORDER BY groupcol DESC, datecol DESC; If there are multiple versions of this being done at the same time, you can do them separately and then join then on the group key (groupcol in the example). ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
On Thu, 9 Mar 2006, Rick Ellis wrote: In article [EMAIL PROTECTED], Roman Neuhauser [EMAIL PROTECTED] wrote: I've been waiting five months for the majordomo moderators to approve my subscription requests to several @postgresql.org mailing lists. I stopped getting any mail from postgresql.org last fall. Repeated attempts to subscribe have all been met with silence. Something appears to have changed. So how did you get this message? :( Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] PL/pgSQL question
Hi All!
I'm trying to get working the below PL/pgSQL function without sucess.
The function is correctly created, but when I tested it i got:
# SELECT grantAccess('sara', 'sarapass');
ERROR: set-valued function called in context that cannot accept a set
CONTEXT: PL/pgSQL function grantaccess line 10 at return next
veillewm=#
What I'm missing?
Thank in advance
Younes
- CODE BEGIN --
CREATE FUNCTION grantAccess(text,text) RETURNS SETOF users AS '
DECLARE
userlogin ALIAS FOR $1;
userpasswd ALIAS FOR $2;
row users%ROWTYPE;
BEGIN
FOR row IN SELECT user_id FROM users WHERE user_login = userlogin
AND user_passwd = userpasswd AND user_account = TRUE LOOP
RETURN NEXT row;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';
- CODE END --
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
In article [EMAIL PROTECTED], Marc G. Fournier [EMAIL PROTECTED] wrote: So how did you get this message? :( Via Usenet. -- http://yosemitephotos.net/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Data corruption zero a file - help!!
To clarify, when set on, every time it hits this error, postgres will rezero that block? Michael Fuhr wrote: On Thu, Mar 09, 2006 at 03:57:46PM +1100, Noel Faux wrote: Given that this seems problem has occurred a number of times for a number I've written a small step by step procedure to address this issue. Is there any other comments you which to add. I was thinking that this should be added to the FAQ / troubleshooting in the docs. How to repair corrupted data due to "ERROR: invalid page header in block X of relation "Y": In pgsql-novice Christopher Goodfellow mentioned the zero_damaged_pages option that I had forgotten about. If you don't care about examining the bad pages then you could set this option in a session and execute a statement that hits every page in the file (Tom Lane mentioned VACUUM and SELECT COUNT(*)). Here's the example I posted in reply: test=# select count(*) from foo; ERROR: invalid page header in block 10 of relation "foo" test=# set zero_damaged_pages to on; SET test=# select count(*) from foo; WARNING: invalid page header in block 10 of relation "foo"; zeroing out page WARNING: invalid page header in block 20 of relation "foo"; zeroing out page WARNING: invalid page header in block 30 of relation "foo"; zeroing out page count --- 9445 (1 row) test=# set zero_damaged_pages to off; SET begin:vcard fn:Noel Faux n:Faux;Noel org:Monash University;Biochemistry and Molecular Biology adr:;;;Clayton;Vic;3800;Australia email;internet:[EMAIL PROTECTED] tel;work:+61 03 9905 1418 url:http://vbc.med.monash.edu.au/~fauxn version:2.1 end:vcard ---(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] Out of memory error on pg_restore
Tom was exactly right. I was trying to restore the dump file into an already created table structure that did have three foreign key constraints. I removed the primary key constraint to speed up the load, but was not aware of the memory usage of the foreign keys. I dropped the table and ran the pg_restore again, and it is running currently and behaving nicely at constant 13Mb of memory. It will probably take some time, but I think it is working fine. Thanks for the help. ---(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] Out of memory error on pg_restore
Yes, I was indeed out of memory. That is the problem: the postgres.exe process corresponding to the pg_restore continuously consumes more and more memory until it runs out and fails with the mentioned error. Since I already have 4Gb of RAM, throwing more hardware at it is not a feasible solution, so I was wondering what else can I do to complete this process without running out of memory. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] Storage Estimates
Is there any documentation or literature on storage estimation for PostgreSQL 8.1 on Windows? If I am given raw data file structure and frequency, I want to be able to calculate the amount of storage necessary to store one day, one month, six months, one year, or two years worth of data. So I am looking for some information on how data is stored in the database, similar to numerous formulas you can use to calculate storage requirements for Oracle (based on the size of a row of data, index type and size, etc.) Thanks. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] PostgresSQL 8.13 builds perfect on new Mac OS X 10.4 for Intel Chips
Hi, this is to inform you that I just downloaded and build PostgresSQL 8.1.3 on Mac OS X 10.4 Intel without any problems. No surprise for you perhaps, but a big surprise for me. Since make and compilers on Mac OS X are always GNU, I expected to be able to build PostgresSQL with some effort. But the build was successfull right at the first try. Great ! Karsten ---(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] posgresql server connection
Hello I have the postgresql installed on Fedora Core 4. I have set tcpip_socket=true in posgresql.conf file and specified the host ip adress in pg_hba.conf file. When i start the postmaster i have this error: FATAL: unrecognized configuration parameter tcpip_socket can you help me??!! thanks and regards _ MSN Hotmail : créez votre adresse e-mail gratuite à vie ! http://www.msn.fr/newhotmail/Default.asp?Ath=f ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Tsearch2 cache lookup problem
I made a restore from a database without tsearch2 installed, installed tsearch2 executing tsearch2.sql and than used it successfully. One day after this i obtained that error as if a database corruption occurred...however noone has touched the database... Thanks for the answers John slave -- View this message in context: http://www.nabble.com/Tsearch2-cache-lookup-problem-t1246642.html#a3322653 Sent from the PostgreSQL - general forum at Nabble.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] PL/pgSQL question
Ycrux [EMAIL PROTECTED] writes:
# SELECT grantAccess('sara', 'sarapass');
ERROR: set-valued function called in context that cannot accept a set
You need to do SELECT * FROM grantAccess(...). This is a plpgsql
implementation restriction that we'll probably try to fix someday,
although there's also a school of thought that says that set-returning
functions in the SELECT targetlist are a bad idea and should be phased
out.
regards, tom lane
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] majordomo unmaintained, postmaster emails ignored?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I stopped getting any mail from postgresql.org last fall. Repeated attempts to subscribe have all been met with silence. Something appears to have changed. You are still subscribed, but your address was set to nomail. I've flipped it back to normal; please let me know if you don't start seeing messages again. - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200603091925 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEEMeXvJuQZxSWSsgRAvvJAKDixnPrcmQ1cy1goOylRjjeRa0xrwCeIw+6 FAoXeUNflgbZpXwGr51RPSY= =Zc+Y -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] posgresql server connection
Mongi LASSOUED [EMAIL PROTECTED] writes: I have the postgresql installed on Fedora Core 4. I have set tcpip_socket=true in posgresql.conf file and specified the host ip adress in pg_hba.conf file. When i start the postmaster i have this error: FATAL: unrecognized configuration parameter tcpip_socket As of PG 8.0, tcpip_socket has been replaced with a more general parameter, listen_addresses. Please see the documentation for the version of Postgres you are using. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] posgresql server connection
On Thursday 09 March 2006 11:31, Mongi LASSOUED wrote: Hello I have the postgresql installed on Fedora Core 4. I have set tcpip_socket=true in posgresql.conf file and specified the host ip adress in pg_hba.conf file. When i start the postmaster i have this error: FATAL: unrecognized configuration parameter tcpip_socket can you help me??!! thanks and regards FC4 comes with pgsql 8.0. AFAIK tcpip_socket is 7.x. On 8.x you set listen_addresses to the interface you want to listen on or '*' for all. jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Data corruption zero a file - help!!
Noel Faux [EMAIL PROTECTED] writes: To clarify, when set on, every time it hits this error, postgres will rezero that block? It'll only re zero if the page gets dropped from shared memory without there having been any occasion to write it out. Otherwise, the first write will clobber the bad data on disk and that's the end of it. My suggestion to use either VACUUM or SELECT COUNT(*) failed to take that behavior into account --- VACUUM *will* rewrite the page, but a SELECT scan won't dirty the page. So you might consider a SELECT to see how bad the situation is (how many bad pages) and then a VACUUM if you want them cleaned up. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Run PostgreSQL with Administrator account in Windows
Hello, How do I run PostgreSQL with Administrator account in Windows? I know this is a security issue but I just want to test it in my Administrator account. Thanks, Michael Louie Loria LoRz Technology Solutions http://www.lorztech.com signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Run PostgreSQL with Administrator account in Windows
Michael Louie Loria wrote: Hello, How do I run PostgreSQL with Administrator account in Windows? I know this is a security issue but I just want to test it in my Administrator account. You can't. It is a security issue and we don't allow it. Joshua D. Drake Thanks, Michael Louie Loria LoRz Technology Solutions http://www.lorztech.com -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: PLphp, PLperl - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Run PostgreSQL with Administrator account in Windows
Joshua D. Drake wrote: You can't. It is a security issue and we don't allow it. Joshua D. Drake Thanks Joshua. that was a quick response. Michael Louie Loria LoRz Technology Solutions http://www.lorztech.com signature.asc Description: OpenPGP digital signature
Re: [GENERAL] Run PostgreSQL with Administrator account in Windows
Joshua D. Drake [EMAIL PROTECTED] writes: Michael Louie Loria wrote: How do I run PostgreSQL with Administrator account in Windows? You can't. It is a security issue and we don't allow it. FYI, there is code in CVS HEAD to allow starting the postmaster from an admin account via pg_ctl: pg_ctl will give up admin privileges before launching the postmaster, thereby closing the security hole. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] select where in and order
Tony Smith wrote on Thursday, March 09, 2006 6:33 PM To: pgsql-general@postgresql.org Subject: [GENERAL] select where in and order I have two tables action and group: action id, name group: action_id rank I what to select from action table by order by the rank in the group table. If I use select * from action where id in (select action_id from group order by rank) Try something like: select a.*, g.action_id FROM action a INNER JOIN group g ON a.id = g.action_id ORDER BY g.action_id ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] select where in and order
Tony Smith wrote: I have two tables action and group: action id, name group: action_id rank I what to select from action table by order by the rank in the group table. If I use select * from action where id in (select action_id from group order by rank) Why not select * from action a, group g where a.id=g.action_id order by rank desc; ? -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] select where in and order
On Thu, 9 Mar 2006, Tony Smith wrote: I have two tables action and group: action id, name group: action_id rank I what to select from action table by order by the rank in the group table. If I use select * from action where id in (select action_id from group order by rank) The action may not be ordered by rank. How can I do it? Well, I think any answer is going to depend on a few pieces of information about the layout and desired behavior. Is group.action_id unique? If so, probably converting it into a join is easiest, I think that'd be something like: select action.* from action, group where action.id=group.action_id order by rank If not, which rank do you want to use from group for a matching id? You could probably then do something with group by and an aggregate. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] questions?
Hi,I haveread the source codes of pgsql these days,and I want to know which part of the source codes deal with the function of executing the sql(select,alter,and so on),and which function deal with the query operation. Thank!
Re: [GENERAL] questions?
xia_pw wrote: Hi,I have read the source codes of pgsql these days,and I want to know which part of the source codes deal with the function of executing the sql(select,alter,and so on),and which function deal with the query operation. Thank! Why? If you want to add functionality you'll need to discuss it first on the -hackers list and they will be able to point you in the right direction. -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Run PostgreSQL with Administrator account in Windows
Tom Lane wrote: Joshua D. Drake [EMAIL PROTECTED] writes: Michael Louie Loria wrote: How do I run PostgreSQL with Administrator account in Windows? You can't. It is a security issue and we don't allow it. FYI, there is code in CVS HEAD to allow starting the postmaster from an admin account via pg_ctl: pg_ctl will give up admin privileges before launching the postmaster, thereby closing the security hole. regards, tom lane I was thinking of that but I'm new to PostgreSQL so I still don't know how to compile it. Currently checking http://www.postgresql.org/docs/faqs.FAQ_MINGW.html and subscribing to pgsql-hackers. Thanks Michael Louie Loria LoRz Technology Solutions http://www.lorztech.com signature.asc Description: OpenPGP digital signature
