Re: [GENERAL] Need suggestion on how best to update 3 million rows
Ow Mun Heng wrote: > I found 2 new ways to do this. > > option 1 > --- > > create table foo as select unique_id, rtrim(number) as number from foo; > alter table add primary key... > create index... > drop org_table > alter table rename... > All this is ~10min This only works if you don't have foreign key constraints involving that table. Otherwise you just lost your data integrity (although I expect an error to be thrown). > option 2 > > This I saw in the mysql archives (in my laptop).. when I say this I > went.. WTF? This is possible?? Dang IT! > > update a set number=replace(number,'ABC ', 'ABC') where reptest like '% > ABC%'; Ehrm... yes, nothing special about it. Basic SQL really ;) But shouldn't you be using trim() or rtrim() instead?: update table set number = trim(number) you could probably speed that up by only querying the records that need trimming, for example: create index tmp_idx on table(number) where number != trim(number); analyze table; update table set number = trim(number) where number != trim(number); -- 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 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need suggestion on how best to update 3 million rows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 01:13, Ow Mun Heng wrote: > I have a table in PG, Pulled from SQL Server using Perl DBI (w/o using > chopblanks) and have ended up with a column where the "space" is being > interpreted as a value. > > eg: > > "ABC " when it should be "ABC" > > this is being defined as varchar(4) > > I've already pull the relevent columns with > > create foo as select unique_id, rtrim(number) from org_column > > I've tried to do the update using > > update org_column set number = foo.number where foo.unique_id = > org_column=unique_id. Number? Where does "number" come from? Unless you've got weird field names, that doesn't sound like a very good name for a VARCHAR(4) column. > The update is taking a few hours and still hasn't ended. > > I've killed it already and rolled back the changes. > > what's the easiest way to update these fields? Is it only *some* tuples that have the "extra space" problem? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG38NAS9HxQb37XmcRAlZhAKCMtXSMzvbZ04M3YAdlAhjN4p7rSQCfZTDp Goyd+/FIFdwoc7IA87Mr3xM= =hJfr -END PGP SIGNATURE- ---(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] Need suggestion on how best to update 3 million rows
On Thu, 2007-09-06 at 04:07 -0500, Ron Johnson wrote: > On 09/06/07 01:13, Ow Mun Heng wrote: > > update org_column set number = foo.number where foo.unique_id = > > org_column=unique_id. > > Number? Where does "number" come from? Unless you've got weird > field names, that doesn't sound like a very good name for a > VARCHAR(4) column. "number" is just a fictitious column name. I use sane column names of course :-) > > > The update is taking a few hours and still hasn't ended. > > > Is it only *some* tuples that have the "extra space" problem? Actually, it's all of it ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Need suggestion on how best to update 3 million rows
On Thu, 2007-09-06 at 11:08 +0200, Alban Hertroys wrote: > Ow Mun Heng wrote: > > I found 2 new ways to do this. > > > > option 1 > > --- > > > > create table foo as select unique_id, rtrim(number) as number from foo; > > alter table add primary key... > > create index... > > drop org_table > > alter table rename... > > All this is ~10min > > This only works if you don't have foreign key constraints involving that > table. Otherwise you just lost your data integrity (although I expect an > error to be thrown). Got it.. Don't use FK's so.. I'm safe (for now) > > > option 2 > > > > This I saw in the mysql archives (in my laptop).. when I say this I > > went.. WTF? This is possible?? Dang IT! > > > > update a set number=replace(number,'ABC ', 'ABC') where reptest like '% > > ABC%'; > > Ehrm... yes, nothing special about it. Basic SQL really ;) Hmm.. I feel the salt.. > But shouldn't you be using trim() or rtrim() instead?: > > update table set number = trim(number) Hmm.. didn't think of that. Next time I guess. (in all honestly, I didn't know you can update it on the same process/column/table. I was dumping it to a separate table and updating it.. Now I know.. > > you could probably speed that up by only querying the records that need > trimming, for example: > > create index tmp_idx on table(number) where number != trim(number); > analyze table; > update table set number = trim(number) where number != trim(number); all fields in that column is affected. I have " " (5 spaces) instead of nulls Thanks for the pointers.. ---(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] Need suggestion on how best to update 3 million rows
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 04:20, Ow Mun Heng wrote: > On Thu, 2007-09-06 at 04:07 -0500, Ron Johnson wrote: > >> On 09/06/07 01:13, Ow Mun Heng wrote: > >>> update org_column set number = foo.number where foo.unique_id = >>> org_column=unique_id. >> Number? Where does "number" come from? Unless you've got weird >> field names, that doesn't sound like a very good name for a >> VARCHAR(4) column. > > "number" is just a fictitious column name. I use sane column names of > course :-) OK. Maybe there's an English language "issue", or maybe I'm just excessively picky, but using "number" in this context is confusing. In any event, it didn't stop the Earth from spinning or the sun from shining, so it can't be that bad of a problem... >>> The update is taking a few hours and still hasn't ended. >>> > >> Is it only *some* tuples that have the "extra space" problem? > > Actually, it's all of it Then I agree with Alban: update table set number = trim(number); or, if you need the leading spaces: update table set number = rtrim(number) Then: update table set number = NULL where number = ''; - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG38y7S9HxQb37XmcRAgC8AKDue6TRz4oLcmavV5u6dw0yOiQC4gCfVmgt pCuDuyjOKh7LM/dfACkw3lc= =KCw6 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need suggestion on how best to update 3 million rows
On Thu, 2007-09-06 at 04:47 -0500, Ron Johnson wrote: > Maybe there's an English language "issue", or maybe I'm just > excessively picky, but using "number" in this context is confusing. My Bad.. hehe.. > Then I agree with Alban: > update table set number = trim(number); > or, if you need the leading spaces: > update table set number = rtrim(number) > > Then: > update table set number = NULL where number = ''; Dag it.. I wish I didn't live on the other end of the TimeZone and gotten this answer before I went the "hard way" oh well.. lesson learned and that's the positive spin on it right? Thanks a bunch guys.. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] UTF8 frustrations
[EMAIL PROTECTED] wrote:
>
> Here is the latest issue, to verify that the pg_dump works, I'm going
> to do dump and restore on the same host/cluster.
>
> Source:
> DB_source:
> Red Hat Enterprise Linux AS release 4 (Nahant Update 4)
> psql 8.2.4
> Destination:
> same machine different db name
>
> echo $LANG
> en_US.UTF-8
>
> SET client_encoding = 'UTF8';
>
> Command used:
> pg_dump -Fc srcdb > db.dump
> pg_restore -d devdb db.dump
>
> Results, same error. Now I'm really concerned.
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
> pg_restore: [archiver (db)] Error from TOC entry 1625; 0 16680 TABLE
DATA logs watersj
> pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence
for encoding "UTF8": 0xdf69
> HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding
> CONTEXT: COPY logs, line 69238382
> WARNING: errors ignored on restore: 1
I can recreate this behaviour with 8.2.4 (UTF-8).
psql> CREATE TABLE test (id serial PRIMARY KEY, val text);
psql> INSERT INTO test (val) VALUES (E'\xdf\x69');
psql> \q
$ pg_dump -F c -f x.dmp -t test testdb
$ pg_restore -c -d testdb x.dmp
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1840; 0 45883 TABLE
DATA test laurenz
pg_restore: [archiver (db)] COPY failed: ERROR: invalid byte sequence
for encoding "UTF8": 0xdf69
HINT: This error can also happen if the byte sequence does not match
the encoding expected by the server, which is controlled by
"client_encoding".
CONTEXT: COPY test, line 1
WARNING: errors ignored on restore: 1
The problem is that in (at least) one record in your table
watersj.logs, there is a corrupt string.
Unfortunately (as demonstrated above) it is possible to enter
corrupt data into a PostgreSQL database, this is what must have
happened in your case.
I suggest that you identify and correct this string in the original
database, then everything should work fine.
You can extract the offending row from the dump, that should
help to identify it. 69238382 rows is a little unwieldy, but
tools like awk can help:
pg_restore db.dump | awk '/^COPY logs /,/^\\\.$/ { if (lineno==69238382)
print $0; ++lineno }'
I think there is the desire to fix problems like this in 8.3,
but I don't think that's done yet.
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need suggestion on how best to update 3 million rows
Ow Mun Heng wrote: On Thu, 2007-09-06 at 04:47 -0500, Ron Johnson wrote: Maybe there's an English language "issue", or maybe I'm just excessively picky, but using "number" in this context is confusing. My Bad.. hehe.. Then I agree with Alban: update table set number = trim(number); or, if you need the leading spaces: update table set number = rtrim(number) Then: update table set number = NULL where number = ''; Dag it.. I wish I didn't live on the other end of the TimeZone and gotten this answer before I went the "hard way" oh well.. lesson learned and that's the positive spin on it right? Thanks a bunch guys.. Two other tips for bulk-updates like this: 1. Do as many columns in one go as you can 2. Only update rows that need updating When you've finished, a CLUSTER/VACUUM FULL can be useful too. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Rankinf of Results?
Hi there, I would like to achieve some kind of rating of the results of a query. As it searches in different fields of the (metadata) database, matching keywords of the field of the "data variable names" are more important than matching keywords in the "description" field... I have no idea how to achieve that. Could anyone point me into the right direction? Thanks for any advice! Stef ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need suggestion on how best to update 3 million rows
On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote: > create index tmp_idx on table(number) where number != trim(number); > analyze table; > update table set number = trim(number) where number != trim(number); dont use !=. use <>. != does something different, and in fact it is not a real operator - it's just 2 operators bundled together. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] SQL query with IFs (?) to "Eliminate" NULL Values
Stefan Schwarzer skrev: > Hi there, > > I want to calculate per Capita values on-the-fly, taking for example the > "Total GDP" data set and divide it by "Total Population". Now, each of > these data sets have a couple of "0" or "-" values (the latter being > the indicator for : "no data available"). > > Until now I have it working like this: > > SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS y_2004, > countries_view.name AS name > FROM pop_total, countries_view > LEFT JOIN tpes_total ON tpes_total.id = countries_view.id > WHERE pop_total.y_2004<> '0' AND pop_total.y_2004<> '-' AND > tpes_total.y_2004 <> '-' AND countries_view.id = pop_total.id > ORDER BY name ASC > > But then it eliminates the countries having these "0" or "-" values. > > In principal I still would like to have them in my final $result, and > then via PHP display them in grey (or with "x" or something like that). > > So, I guess I'd need some kind of IF statement to do the calculation > only with "valuable" numbers and pass the others as they are. > > But I have no idea how this would work. Another idea -using a left join with additional criteria. I agree with the suggestion to use real NULLS to signify mising data - but you still have to work around the issue with population=0 though SELECT DISTINCT (tpes_total.y_2004 / pop_total.y_2004) AS y_2004, countries_view.name AS name FROM countries_view LEFT JOIN pop_total ON countries_view.id = pop_total.id AND pop_total.y_2004<> '0' AND pop_total.y_2004<> '-' LEFT JOIN tpes_total ON tpes_total.id = countries_view.id AND tpes_total.y_2004 <> '-' AND countries_view.id = pop_total.id ORDER BY name ASC (As a question of style, I would suggest never to mix ANSI-joins with commaseparated tables lists. Use ANSI-joins. They are good for you.) Nis ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Querying database for table pk - better way?
On 9/5/07, Josh Trutwin <[EMAIL PROTECTED]> wrote: > On Wed, 5 Sep 2007 19:08:33 -0400 > "Merlin Moncure" <[EMAIL PROTECTED]> wrote: > > > On 9/5/07, Josh Trutwin <[EMAIL PROTECTED]> wrote: > > > I have a php application that needs to query the PK of a table - > > > I'm currently using this from the information_schema views: > > > > try this: > > CREATE OR REPLACE VIEW PKEYS AS > > SELECT nspname as schema, c2.oid as tableoid, c2.relname as > > table, substring(pg_catalog.pg_get_indexdef(i.indexrelid, 0, true) > > from E'\\((.*)\\)') > > FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, > > pg_catalog.pg_index i, pg_namespace n > > WHERE c.oid = i.indrelid AND i.indexrelid = c2.oid AND > > c.relkind = 'r' AND i.indisprimary AND c.relnamespace = n.oid > > ORDER BY i.indisprimary DESC, i.indisunique DESC, > > c2.relname; > > Beautiful, thanks - I had to change one thing - c2.relname is the > constraint name - c.relname is the table name. right, my mistake... > Couple questions: > > 1.) The ORDER BY - I assume this can be reduced to "ORDER BY > c.relname"? actually, 'order by' in views, unless in subquery, is bad form and should be removed. I lifted a lot of this query by the way from psql...using the psql -E invocation to get the sql it makes when doing \d :-). > 3.) I changed the WHERE clause to use INNER JOIN's - is it just your > personal preference not to use INNER JOINs or does it actually have > an impact on the planner? I prefer to separate them so I can > visually keep the join conditions separate from the extra stuff in the Actually, I generally use joins (although I type just JOIN, not INNER JOIN) and prefer JOIN USING to JOIN ON where it can be used. For simple queries with no left or right joins or certain special cases I might use the where clause (it rarely makes a difference in the planner AFAIK). merlin ---(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] Need suggestion on how best to update 3 million rows
hubert depesz lubaczewski skrev: > On Thu, Sep 06, 2007 at 11:08:02AM +0200, Alban Hertroys wrote: >> create index tmp_idx on table(number) where number != trim(number); >> analyze table; >> update table set number = trim(number) where number != trim(number); > > dont use !=. use <>. != does something different, and in fact it is > not a real operator - it's just 2 operators bundled together. Rubbish. From the documentation: "The != operator is converted to <> in the parser stage. It is not possible to implement != and <> operators that do different things." Nis ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Alias "all fields"?
Hi there, I guess I am demanding too much But it would be cool to have some kind of alias for "all fields". What I mean is this here: Instead of this: SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND y2005 NOT NULL I would like to have this: SELECT * FROM gdp WHERE all-fields NOT NULL This is, because my tables have different - and a different number of fields. In principal, I actually just want to have the number of fields which are NOT NULL... Thanks for any advice. Stef ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Alias "all fields"?
Hmm >SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL > AND y2005 NOT NULL It sounds like a bad table design, because i think you need an field "f_year" and "value_of_f_year" then there would be entries like f_year;value_of_f_year 1970 'NULL' 1970 dfgsd 1971 'NULL' 1971 where f_year IS NOT NULL and value_of_f_year IS NOT NULL Greetings, -Franz -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Stefan Schwarzer Gesendet: Donnerstag, 6. September 2007 13:43 An: pgsql-general@postgresql.org Betreff: [SPAM] [GENERAL] Alias "all fields"? Hi there, I guess I am demanding too much But it would be cool to have some kind of alias for "all fields". What I mean is this here: Instead of this: SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND y2005 NOT NULL I would like to have this: SELECT * FROM gdp WHERE all-fields NOT NULL This is, because my tables have different - and a different number of fields. In principal, I actually just want to have the number of fields which are NOT NULL... Thanks for any advice. Stef ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Alias "all fields"?
SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND y2005 NOT NULL It sounds like a bad table design, because i think you need an field "f_year" and "value_of_f_year" then there would be entries like f_year;value_of_f_year 1970 'NULL' 1970 dfgsd 1971 'NULL' 1971 where f_year IS NOT NULL and value_of_f_year IS NOT NULL My table design is - due to some import/update reasons - surely not the best one, but pretty simple: idy1970y1971y1972 .. 1 23 25 28 2 NULLNULL 5 3 NULL 94 102 What do you think? Greetings, -Franz -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Im Auftrag von Stefan Schwarzer Gesendet: Donnerstag, 6. September 2007 13:43 An: pgsql-general@postgresql.org Betreff: [SPAM] [GENERAL] Alias "all fields"? Hi there, I guess I am demanding too much But it would be cool to have some kind of alias for "all fields". What I mean is this here: Instead of this: SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND y2005 NOT NULL I would like to have this: SELECT * FROM gdp WHERE all-fields NOT NULL This is, because my tables have different - and a different number of fields. In principal, I actually just want to have the number of fields which are NOT NULL... Thanks for any advice. Stef ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(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] Alias "all fields"?
Stefan Schwarzer wrote: SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND y2005 NOT NULL I would like to have this: SELECT * FROM gdp WHERE all-fields NOT NULL Well you can get closer: SELECT * FROM gdp WHERE (y1970+y1971+...+y2005) IS NOT NULL; This makes use of the fact that X+NULL = NULL -- 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] Need suggestion on how best to update 3 million rows
On Thu, Sep 06, 2007 at 01:39:51PM +0200, Nis Jørgensen wrote: > Rubbish. From the documentation: hmm .. i'm sorry - i was *sure* about it because we were bitten by something like this lately - apparently it was similiar but not the same. sorry again for misinformation. depesz -- quicksil1er: "postgres is excellent, but like any DB it requires a highly paid DBA. here's my CV!" :) http://www.depesz.com/ - blog dla ciebie (i moje CV) ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Alias "all fields"?
Stefan Schwarzer schrieb: Hi there, I guess I am demanding too much But it would be cool to have some kind of alias for "all fields". What I mean is this here: Instead of this: SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND y2005 NOT NULL I would like to have this: SELECT * FROM gdp WHERE all-fields NOT NULL This is, because my tables have different - and a different number of fields. In principal, I actually just want to have the number of fields which are NOT NULL... Well, this seems more a matter of correct normalization. If you organize your tables to have a column for the year, you would just not have "empty" years to select. Problem solved. Last not least you can easily count the existing years and whatnot... Regards Tino ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Alias "all fields"?
>My table design is - due to some import/update reasons - surely not >the best one, but pretty simple: > >idy1970y1971y1972 .. >1 23 25 28 >2 NULLNULL 5 >3 NULL 94 102 > >What do you think? Normally i use perl with DBD/DBI to import data into databases and it is quite easy to modify raw data with perl and import them. I would prefer another table design (your import will then not be so simple, but your "selects" will get "normally" faster and easier). Table: id_Table;id_row;t_year;t_year_value 1;1;y1970,23 2;1;y1971;25 ... 20;3;y1970;NULL 21;3;y1971;94 You will need only id_row if you need all tuples in the same line of your original line. I yould do it so, if you have more then 3 or 4 columns of y . Greetings, -Franz ---(end of broadcast)--- TIP 6: explain analyze is your friend
ANY (was: Re: [GENERAL] Alias "all fields"?)
Richard Huxton wrote: > Well you can get closer: > > SELECT * FROM gdp WHERE (y1970+y1971+...+y2005) IS NOT NULL; > > This makes use of the fact that X+NULL = NULL I was going to suggest SELECT * FROM gdp WHERE NULL NOT IN (y1970, y1971, y1972); But that doesn't work. So I tried using ANY with IS NOT NULL, as according to the documentation "IN is equivalent to = ANY" ( http://www.postgresql.org/docs/8.2/static/functions-subquery.html#AEN13967). So maybe: SELECT * FROM gdp WHERE NULL IS NOT ANY(y1970, y1971, y1972); I get nothing but syntax errors... I remember trying to use ANY in the past and never got it to work... So, how do you use ANY with a fixed set of values (the way IN can)? And can this be used to solve the OP's problem without using tricks like summing NULL values? -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Version 8.2.4 ecpg - function not found
We are upgrading from Version 7.4.8 to 8.2.4. In 7.4, there were functions called ECPGis_informix_null ECPGset_informix_null In 8.2.4, I do not see these functions. Instead, I see functions ECPGis_noind_null ECPGset_noind_null Are they functionally the same? Also, the 8.2.4 doc (Section 31.9.1) describes the functions risnull and rsetnull. These are the names of the original Informix functions. Are they available for use through ecpg? As always, TIA. Paul Tilles ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Alias "all fields"?
Stefan Schwarzer schrieb: SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL AND y2005 NOT NULL It sounds like a bad table design, because i think you need an field "f_year" and "value_of_f_year" then there would be entries like f_year;value_of_f_year 1970 'NULL' 1970 dfgsd 1971 'NULL' 1971 where f_year IS NOT NULL and value_of_f_year IS NOT NULL My table design is - due to some import/update reasons - surely not the best one, but pretty simple: idy1970y1971y1972 .. 1 23 25 28 2 NULLNULL 5 3 NULL 94 102 What do you think? Make the table: id | year | value ---+--+-- 1 | 1970 |23 1 | 1971 |25 1 | 1972 |28 ... 2 | 1972 | 5 3 | 1971 |94 3 | 1972 | 102 primary key: (id,year) value not null and be ready. the import/update reasons are pretty easily solved that way too. Regards Tino ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Controlling locale and impact on LIKE statements
Martin Langhoff escribió: > On 9/5/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > > Martin Langhoff escribió: > > > > > As I have a Pg install where the locale is already en_US.UTF-8, and > > > the database already exists, is there a DB-scoped way of controlling > > > the locale? > > > > Not really. > > Ah well. But I do have to wonder why... if each database can have its > own encoding, that is likely to be matched with a locale. Isn't that > the main usage scenario? In fact, with unicode encodings, it's likely > that all your DBs are utf-8 encoded, but each may have its own locale. The problem is twofold: 1. index ordering is dependent on locale, and 2. there are some indexes over text columns on shared tables, that is, tables to are in all databases (pg_database, pg_authid, etc). So you cannot really change the locale without making those indexes invalid. It has been said in the past that it is possible to work around this, which would allow us to change locale per database, but it hasn't gotten done yet. > And yet, right now it's all affected by the locale the cluster was > init'd under. In my case, old Pg installations have been upgraded a > few times from a Debian Sarge (C locale). Newer DB servers based on > ubuntu are getting utf-8-ish locales. And all this variation is > impacting something that should be per DB... > > Is this too crazy to ask? ;-) Well, you are not the only one to have asked this, so it's probably not crazy. It just hasn't gotten any hacker motivated enough yet, though. > > You are right and Eloy is wrong on that discussion. There is not > > anything the DB can do to use the regular index if the locale is not C > > for LIKE queries. There are good reasons for this. There's not much > > option beyond creating the pattern_ops index. > > Are the reasons *really* good? ;-) Well, I can't remember them ATM :-) But this was given deep consideration and the pattern_ops were the best solution to be found. -- Alvaro Herrera http://www.amazon.com/gp/registry/5ZYLFMCVHXC "Industry suffers from the managerial dogma that for the sake of stability and continuity, the company should be independent of the competence of individual employees." (E. Dijkstra) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Need suggestion on how best to update 3 million rows
At 06:32 PM 9/6/2007, Richard Huxton wrote: Two other tips for bulk-updates like this: 1. Do as many columns in one go as you can 2. Only update rows that need updating When you've finished, a CLUSTER/VACUUM FULL can be useful too. How about: make sure you have enough free space because the table will effectively double in size? Assuming it hasn't already been updated a few times without vacuuming :). That's still true right? It is safe to assume that postgresql will still handle the out of disk space scenario gracefully - no data corruption - the transaction fails and that's it? Regards, Link. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] foreign key violation error with partitioned table
hello, i have a partitioned table t_kayit with 6 partitions and kayit_id is primary key on this table. My other t_vto_sonuclari table use that kayit_id as foreign key. I'm trying to insert values which contains kayit_id to t_vto_sonuclari and i'm sure those kayit_ids are in t_kayit table too but when i'm inserting , i had error. ERROR: insert or update on table "t_vto_sonuclari" violates foreign key constraint "fk_t_kayit_kayit_id" DETAIL: Key(kayit_id)=(54168) is not present in table t_kayit I created a new test table which is totally same as t_kayit but non partitioned . I create new foreign key on that t_vto_sonuclari which refers to the new test table. In that case i didn't had that error.I could insert. what am i supposed to do for partitioned table?
[GENERAL] Wrong dynamic link ../../../src/interfaces/libpq/libpq.sl.3
Hi I was not getting this message befor, But now when I compile postgresql 7.4.2 on a HPUX PA m/c there is a shared dynamic library ../../../src/interfaces/libpq/libpq.sl.3. This linking was not there before. Due to this when i run the psql binary i get the below message can someone please help. shmlgarlica# ./psql /usr/lib/dld.sl: Can't open shared library: ../../../src/interfaces/libpq/libpq.sl.3 /usr/lib/dld.sl: No such file or directory Abort(coredump) shmlgarlica# chatr psql psql: shared executable shared library dynamic path search: SHLIB_PATH disabled second embedded path disabled first Not Defined shared library list: dynamic ../../../src/interfaces/libpq/libpq.sl.3 dynamic /usr/lib/libc.2 dynamic /usr/lib/libssl.sl.0 dynamic /usr/lib/libcrypto.sl.0 dynamic /usr/lib/libgen.2 dynamic /usr/lib/libdld.2 dynamic /usr/lib/libnsl.1 dynamic /usr/lib/libm.2 dynamic /usr/lib/libkrb5.sl dynamic /usr/lib/libk5crypto.sl dynamic /usr/lib/libcom_err.sl shared library binding: deferred global hash table disabled plabel caching disabled global hash array size:1103 global hash array nbuckets:3 shared vtable support disabled explicit unloading enabled static branch prediction disabled executable from stack: D (default) kernel assisted branch prediction enabled lazy swap allocation disabled text segment locking disabled data segment locking disabled third quadrant private data space disabled fourth quadrant private data space disabled third quadrant global data space disabled data page size: D (default) instruction page size: D (default) nulptr references enabled shared library private mapping disabled shared library text merging disabled shmlgarlica# ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] ANY
Alban Hertroys wrote: SELECT * FROM gdp WHERE NULL IS NOT ANY(y1970, y1971, y1972); I get nothing but syntax errors... I remember trying to use ANY in the past and never got it to work... So, how do you use ANY with a fixed set of values (the way IN can)? And can this be used to solve the OP's problem without using tricks like summing NULL values? AFAIK there are two variants of ANY() 1. sets 2. arrays So you should be able to do: ... WHERE x = ANY( ARRAY[a, b, c] ) Of course, where x is NULL, I don't think that'll help you (x = NULL returns NULL). Oh, and you couldn't have nulls in arrays before 8.2 iirc. -- Richard Huxton Archonet Ltd ---(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] foreign key violation error with partitioned table
hello, i have a partitioned table t_kayit with 6 partitions and kayit_id is primary key on this table. My other t_vto_sonuclari table use that kayit_id as foreign key. I'm trying to insert values which contains kayit_id to t_vto_sonuclari and i'm sure those kayit_ids are in t_kayit table but when i'm inserting , i had error. ERROR: insert or update on table "t_vto_sonuclari" violates foreign key constraint "fk_t_kayit_kayit_id" DETAIL: Key(kayit_id)=(54168) is not present in table t_kayit I created a new test table which is as same as t_kayit but non partitioned . I create new foreign key on that t_vto_sonuclari which refers to the new test table. In that case i didn't had that error.I could insert. what am i supposed to do for partitioned table?
Re: [GENERAL] foreign key violation error with partitioned table
The postgresql partitions is done using inheritance . So basically your master table is empty and the child tables(partitions) contains all the records...right. You can check if your master table contains any records by using this query SELECT * FROM ONLY This will return zero if your master table is empty. So when you reference your master table from other tables, it will always give FK violation since your master table is empty and the recs are in child tables only. Shar joe gunce orman <[EMAIL PROTECTED]> wrote: hello, i have a partitioned table t_kayit with 6 partitions and kayit_id is primary key on this table. My other t_vto_sonuclari table use that kayit_id as foreign key. I'm trying to insert values which contains kayit_id to t_vto_sonuclari and i'm sure those kayit_ids are in t_kayit table too but when i'm inserting , i had error. ERROR: insert or update on table "t_vto_sonuclari" violates foreign key constraint "fk_t_kayit_kayit_id" DETAIL: Key(kayit_id)=(54168) is not present in table t_kayit I created a new test table which is totally same as t_kayit but non partitioned . I create new foreign key on that t_vto_sonuclari which refers to the new test table. In that case i didn't had that error.I could insert. what am i supposed to do for partitioned table? - Be a better Heartthrob. Get better relationship answers from someone who knows. Yahoo! Answers - Check it out.
Re: [GENERAL] PostgreSQL with Kerberos and Active Directory
Not really - it's always worked that way for me :-( Have you managed to make any other kerberised applications work on this machine? There are sample programs in the kerberos package - try those to see if the problem is in postgresql or int he kerberos libs/setup. //Magnus On Sun, Sep 02, 2007 at 12:05:54PM +0300, Idan Miller wrote: > Hi Magnus, > > I tried changing the SPN to uppercase POSTGRES, but still the same error > occurs. > Any other ideas? (this didn't work both locally and remotely). > > Idan. > > > On 8/31/07, Magnus Hagander <[EMAIL PROTECTED]> wrote: > > > > Ok. I'd try locally from the machine first, so you know the krb > > configurations are absolutely identical all the way. Just change your > > pg_hba so it uses krb5 (and don't forget to use -h - krb5 only works over > > TCP/IP sockets) > > > > THat said, I think your problem is in that you use "postgres" as your SPN. > > It has to be uppercase POSTGRES to work with Active Directory. > > > > //Magnus > > > > > > On Thu, Aug 30, 2007 at 03:34:18PM +0300, Idan Miller wrote: > > > We tried to connect from a different gentoo machine. > > > both client and server are running version 8.2.4 of postgresql. > > > right now, we are trying to connect from gentoo, but we want to connect > > from > > > windows as well > > > > > > Idan > > > > > > > > > On 8/30/07, Magnus Hagander <[EMAIL PROTECTED]> wrote: > > > > > > > > On Thu, Aug 30, 2007 at 02:07:13PM +0300, Idan Miller wrote: > > > > > Hi everyone, > > > > > > > > > > I'm trying to configure PostgreSQL version 8.2.4 with Kerberos and > > > > Active > > > > > Directory. > > > > > The AD is run on a windows 2003 server, and the postgre on gentoo. > > > > > The gentoo computer name is postgre and it's added to the windows > > 2003 > > > > > server AD domain. > > > > > > > > > > I did the following: > > > > > - I compiled postgre with kerberos support and installed it on the > > > > gentoo > > > > > machine. > > > > > - I created a keytab for the user postgres/postgre on the windows > > 2003 > > > > > server machine and copied it to the gentoo machine. > > > > > - I configured the postgresql.conf to point to the keytab. > > > > > - I configured pg_hba.conf to authenticate remote users by kerberos. > > > > > - I followed additional configurations from the howto in the mailing > > > > list > > > > > archives. > > > > > > > > > > Now, when trying to log in with an AD user to postgre I get: > > > > > psq: krb5_sendauth: Bad application version was sent (via sendauth) > > > > > > > > > > Any help will be appreciated. > > > > > > > > Are you sure you have postgresql 8.2 on both ends of the connection? > > Are > > > > yuor clients on windos or unix? > > > > > > > > //Magnus > > > > > > ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Alias "all fields"?
[EMAIL PROTECTED] schrieb: Make the table: id | year | value ---+--+-- 1 | 1970 |23 1 | 1971 |25 1 | 1972 |28 ... 2 | 1972 | 5 3 | 1971 |94 3 | 1972 | 102 primary key: (id,year) value not null and be ready. the import/update reasons are pretty easily solved that way too. then your primary key will consists of two fields. I prefer the primary keys with one field only. Maybe but this would not help you with this situation here :-) I think this fittes best but I'm open to alternative approaches. The wide-table of the original design has definitively much more problems. And if id is independent from year (not really month or so) it is usefull imho to have a compound primary key. Alternatively you can skip the primary key part and just define: id not null, year not null, value not null unique (id,year) Regards Tino ---(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] Wrong dynamic link ../../../src/interfaces/libpq/libpq.sl.3
"Rajaram J" <[EMAIL PROTECTED]> writes: > shmlgarlica# chatr psql > psql: > shared executable > shared library dynamic path search: > SHLIB_PATH disabled second > embedded path disabled first Not Defined > shared library list: > dynamic ../../../src/interfaces/libpq/libpq.sl.3 > dynamic /usr/lib/libc.2 > dynamic /usr/lib/libssl.sl.0 > dynamic /usr/lib/libcrypto.sl.0 This is normal; the question is what have you done with the embedded path? An out-of-the-box build should generate something like this: $ chatr ~postgres/version74/bin/psql /home/postgres/version74/bin/psql: shared executable shared library dynamic path search: SHLIB_PATH disabled second embedded path enabled first /home/postgres/version74/lib internal name: psql shared library list: dynamic ../../../src/interfaces/libpq/libpq.sl.3 dynamic /usr/lib/libc.1 dynamic /usr/local/lib/libz.sl dynamic /usr/lib/libdld.1 shared library binding: deferred BTW, if you are building 7.4.2 from source today, you are nuts. Current release in that branch is 7.4.17. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] ANY
Richard Huxton wrote: > Alban Hertroys wrote: >> >> SELECT * FROM gdp WHERE NULL IS NOT ANY(y1970, y1971, y1972); > AFAIK there are two variants of ANY() > 1. sets > 2. arrays > > So you should be able to do: > ... WHERE x = ANY( ARRAY[a, b, c] ) But then the documentation isn't entirely correct. It suggests that it works similar to IN, but it doesn't. > Of course, where x is NULL, I don't think that'll help you (x = NULL > returns NULL). Oh, and you couldn't have nulls in arrays before 8.2 iirc. Well, as ANY allows the use of an operator, I figured you could use IS NULL with it. -- 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] Alias "all fields"?
Stefan Schwarzer <[EMAIL PROTECTED]> writes: > Instead of this: > SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL > AND y2005 NOT NULL > I would like to have this: >SELECT * FROM gdp WHERE all-fields NOT NULL This idea seems rather pointless for any operation other than null-testing, since nothing else would apply uniformly to all data types. For null-testing you can use row null tests: regression=# select * from int8_tbl i; q1|q2 --+--- 123 | 456 123 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 22 | | (7 rows) regression=# select * from int8_tbl i where row(i.*) is not null; q1|q2 --+--- 123 | 456 123 | 4567890123456789 4567890123456789 | 123 4567890123456789 | 4567890123456789 4567890123456789 | -4567890123456789 (5 rows) regression=# Note: this only works the way you want in 8.2 and up; earlier versions thought that "row is not null" meant that *any* field is not null, rather than *all*. regards, tom lane ---(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] tsearch2 anomoly?
I'm having trouble understanding to_tsvector. (PostreSQL 8.1.9 contrib)
In this first case converting 'gallery2-httpd-conf' makes sense to me
and is exactly what I want. It looks like the entire string is
indexed plus the substrings broken by '-' are indexed.
ossdb=# select to_tsvector('gallery2-httpd-conf');
to_tsvector
-
'conf':4 'httpd':3 'gallery2':2 'gallery2-httpd-conf':1
However, I'd expect the same to happen in the httpd example - but it
does not appear to.
ossdb=# select to_tsvector('httpd-2.2.3-5.src.rpm');
to_tsvector
---
'httpd-2.2.3-5.src.rpm':1
Why don't I get: 'httpd', 'src', 'rpm', 'httpd-2.2.3-5.src.rpm' ?
Is this a bug or design?
Thank you!
Bob
---(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] Connection pooling
Hello. I'm using Apache + PHP + Postgres for my project. I've tried the two poolers people usually recommend here - pgbouncer and pgpool. I have a problem with pgbouncer - under the load the query execution becomes ~10 times slower than it should be - basically to test it, I connect with psql (establishing connection becomes somewhat slow under load) and use \timing to measure execution time. The basic query of "select * from aaa where id = 1" runs 10 times slower than it should. If I connect with psql directly to postgres, the execution time is acceptable. Does anyone know what is the problem? The pgpool (I tried 3.1, 3.4 and pgpool-II 1.2) works fine but has the following problem - after some time it just "hangs", and if I try to connect to it with psql it just hangs indefinitely. After restart it works fine again. I turned off persistent connections in php so it's not that. Does anybody have the same problem? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] a provocative question?
I am getting in the habit of storing much of my day-to-day information in postgres, rather than "flat" files. I have not had any problems of data corruption or loss, but others have warned me against abandoning files. I like the benefits of enforced data types, powerful searching, data integrity, etc. But I worry a bit about the "safety" of my data, residing in a big scary database, instead of a simple friendly folder-based files system. I ran across this quote on Wikipedia at http://en.wikipedia.org/wiki/Eudora_%28e-mail_client%29 "Text files are also much safer than databases, in that should disk corruption occur, most of the mail is likely to be unaffected, and any that is damaged can usually be recovered." How naive (optimistic?) is it to think that "the database" can replace "the filesystem"? TJ O'Donnell http://www.gnova.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Do AGGREGATES consistently use sort order?
I have the following query:
select array_accum(name) from (select name from placenames where
desig='crater' order by name desc) a;
with array_accum defined as:
CREATE AGGREGATE array_accum (
BASETYPE = anyelement,
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
Can I count on this aggregate to take each new item in sorted order
when it adds it to the state vector? So that if I have the following:
oregon_2007_08_20=# select * from (select name from placenames where
desig='crater' order by name desc) a;
name
Yapoah Crater
West Crater
Twin Craters
Timber Crater
Red Crater
Newberry Crater
Nash Crater
Mount Mazama
Millican Crater
Little Nash Crater
Le Conte Crater
Jordan Craters
Diamond Craters
Coffeepot Crater
Cayuse Crater
Black Crater
Big Hole
Belknap Crater
(18 rows)
I can always count on (note the order name):
\a
oregon_2007_08_20=# select array_accum(name) from (select name from
placenames where desig='crater' order by name desc) a;
array_accum
{"Yapoah Crater","West Crater","Twin Craters","Timber Crater","Red
Crater","Newberry Crater","Nash Crater","Mount Mazama","Millican
Crater","Little Nash Crater","Le Conte Crater","Jordan
Craters","Diamond Craters","Coffeepot Crater","Cayuse Crater","Black
Crater","Big Hole","Belknap Crater"}
(1 row)
I am interested in stitching a line out of points in postgis, but the
order/aggregate thing is a general question.
Thx
W
---(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] tsearch2 anomoly?
This is how default parser works. See output from
select * from ts_debug('gallery2-httpd-conf');
and
select * from ts_debug('httpd-2.2.3-5.src.rpm');
All token type:
select * from token_type();
On Thu, 6 Sep 2007, RC Gobeille wrote:
I'm having trouble understanding to_tsvector. (PostreSQL 8.1.9 contrib)
In this first case converting 'gallery2-httpd-conf' makes sense to me and is
exactly what I want. It looks like the entire string is indexed plus the
substrings broken by '-' are indexed.
ossdb=# select to_tsvector('gallery2-httpd-conf');
to_tsvector
-
'conf':4 'httpd':3 'gallery2':2 'gallery2-httpd-conf':1
However, I'd expect the same to happen in the httpd example - but it does not
appear to.
ossdb=# select to_tsvector('httpd-2.2.3-5.src.rpm');
to_tsvector
---
'httpd-2.2.3-5.src.rpm':1
Why don't I get: 'httpd', 'src', 'rpm', 'httpd-2.2.3-5.src.rpm' ?
Is this a bug or design?
Thank you!
Bob
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 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] Alias "all fields"?
as everyone has pointed out it does not seem like the best table design and querying for these fields as normal course of business does not seem that great, but if you wanted to audit tables like these once in a while you could easily do it using your favorite scripting language or SQL itself. here's a simple psql example to get you started: test=> \t Tuples only is off. test=> \a Output format is aligned. test=> \t Showing only tuples. test=> \a Output format is unaligned. test=> \o /tmp/null-test.sql test=> select 'select ''' || upper(table_name) || '''|| '' not null rows count: '' || count(*) from '||table_name||' where ' || array_to_string(array(select column_name::text from information_schema.columns c where c.table_name = t.table_name),' is not null and ') || ' is not null;' from information_schema.tables t where table_schema = 'test' and table_name like 'emp%'; test=> \o test=> \i /tmp/null-test.sql EMPLOYEE not null rows count: 89 EMPLOYEE_ROLE not null rows count: 11 EMPLOYEE_ROLE_PRIVILEGE not null rows count: 266 EMPLOYEE_PRIVILEGE not null rows count: 53 EMPLOYEE_PRIVILEGE_GROUP not null rows count: 9 > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of > Stefan Schwarzer > Sent: Thursday, September 06, 2007 4:43 AM > To: pgsql-general@postgresql.org > Subject: [GENERAL] Alias "all fields"? > > Hi there, > > I guess I am demanding too much But it would be cool to > have some > kind of alias for "all fields". > > What I mean is this here: > > Instead of this: > > SELECT * FROM gdp WHERE y1970 NOT NULL AND y1971 NOT NULL > AND y2005 NOT NULL > > I would like to have this: > >SELECT * FROM gdp WHERE all-fields NOT NULL > > This is, because my tables have different - and a different > number of > fields. > > In principal, I actually just want to have the number of > fields which > are NOT NULL... > > Thanks for any advice. > > Stef > > > ---(end of > broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org/ > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Alias "all fields"?
> Make the table: > id | year | value > ---+--+-- > 1 | 1970 |23 > 1 | 1971 |25 > 1 | 1972 |28 > ... > 2 | 1972 | 5 > 3 | 1971 |94 > 3 | 1972 | 102 > primary key: (id,year) > value not null > and be ready. >the import/update reasons are pretty easily solved >that way too. then your primary key will consists of two fields. I prefer the primary keys with one field only. -Franz ---(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] Do AGGREGATES consistently use sort order?
"Webb Sprague" <[EMAIL PROTECTED]> writes:
> I can always count on (note the order name):
>
> \a
> oregon_2007_08_20=# select array_accum(name) from (select name from
> placenames where desig='crater' order by name desc) a;
> array_accum
> {"Yapoah Crater","West Crater","Twin Craters","Timber Crater","Red
> Crater","Newberry Crater","Nash Crater","Mount Mazama","Millican
> Crater","Little Nash Crater","Le Conte Crater","Jordan
> Craters","Diamond Craters","Coffeepot Crater","Cayuse Crater","Black
> Crater","Big Hole","Belknap Crater"}
> (1 row)
>
> I am interested in stitching a line out of points in postgis, but the
> order/aggregate thing is a general question.
Yes.
You can even do this with GROUP BY as long as the leading columns of the ORDER
BY inside the subquery exactly matches the GROUP BY columns.
In theory we can't promise anything about future versions of Postgres but
there are lots of people doing this already so if ever this was lost there
would probably be some new explicit way to achieve the same thing.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] a provocative question?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 10:43, TJ O'Donnell wrote: > I am getting in the habit of storing much of my day-to-day > information in postgres, rather than "flat" files. > I have not had any problems of data corruption or loss, > but others have warned me against abandoning files. > I like the benefits of enforced data types, powerful searching, > data integrity, etc. > But I worry a bit about the "safety" of my data, residing > in a big scary database, instead of a simple friendly > folder-based files system. > > I ran across this quote on Wikipedia at > http://en.wikipedia.org/wiki/Eudora_%28e-mail_client%29 > > "Text files are also much safer than databases, in that should disk > corruption occur, most of the mail is likely to be unaffected, and any > that is damaged can usually be recovered." > > How naive (optimistic?) is it to think that "the database" can > replace "the filesystem"? Text file are *simple*. When fsck repairs the disk and creates a bunch of recovery files, just fire up $EDITOR (or cat, for that matter) and piece your text files back together. You may lose a block of data, but the rest is there, easy to read. Database files are *complex*. Pointers and half-vacuumed freespace and binary fields and indexes and WALs, yadda yadda yadda. And, by design, it's all got to be internally consistent. Any little corruption and *poof*, you've lost a table. A strategically placed corruption and you've lost your database. But... that's why database vendors create backup/restore commands. You *do* back up your database(s), right?? - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG4D2nS9HxQb37XmcRAg73AKCD321T0u7lux0K2NBhkpQ4kwBjOwCfWh3D WDuns1HAZboUPlraTzbE0oo= =NuLE -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] work hour calculations
correction: > The result I'm expecting for the above to be > >notification_time| finished_time | actual > ++- > 2007-07-06 15:50:00+10 | 2007-07-09 07:10:00+10 | 01:20:00 > 2007-07-07 12:30:00+10 | 2007-07-09 07:20:00+10 | 00:20:00 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to 'register' functions, so they can be called (plpythonu)
> I believe the python embedder mangles the function names when it loads > them into PG, so you can't call them directly. do you think it possible to use the internal system catalogs to lookup the 'mangled' names? ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] fillfactor Question
Hi, fillfactor affects 'update' statements or also has affects for 'insert' and 'delete'? Thanks, Sia ---(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] Reporting services for PostgreSQL
> "Render" as in "run the report program on the host"? Yes. Many reports shows only summary data in reports. If such report is created in server, it runs fast. If such report is created in client, it need to retrieve a lot of data and is very slow. Andrus. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] how to find the number of rows inserted into the master table?
Hi, I have a master table 'Master' with 3 partition tables 'child1', 'child2',' child3' which inherits the master table 'Master'. I have check constraints in the child tables to insert the appropriate values and also there are functions and triggers defined to do this. My question is, if I insert a row into master, the row correctly gets inserted into the child tables. But the output always shows '0 rows inserted' since the rows are not actually inserted in to the master. Is there a way to show the number of rows inserted into the master tables (though in reality the rows get inserted into the partitions and not the master) Thanks in advance shar jo - Yahoo! oneSearch: Finally, mobile search that gives answers, not web links.
Re: [GENERAL] Controlling locale and impact on LIKE statements
On 9/5/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Martin Langhoff escribió: > > > As I have a Pg install where the locale is already en_US.UTF-8, and > > the database already exists, is there a DB-scoped way of controlling > > the locale? > > Not really. Ah well. But I do have to wonder why... if each database can have its own encoding, that is likely to be matched with a locale. Isn't that the main usage scenario? In fact, with unicode encodings, it's likely that all your DBs are utf-8 encoded, but each may have its own locale. And yet, right now it's all affected by the locale the cluster was init'd under. In my case, old Pg installations have been upgraded a few times from a Debian Sarge (C locale). Newer DB servers based on ubuntu are getting utf-8-ish locales. And all this variation is impacting something that should be per DB... Is this too crazy to ask? ;-) > You are right and Eloy is wrong on that discussion. There is not > anything the DB can do to use the regular index if the locale is not C > for LIKE queries. There are good reasons for this. There's not much > option beyond creating the pattern_ops index. Are the reasons *really* good? ;-) I can see that LIKE 'foo%' is implemented as a combined greater-than/less-than clause, which is collation dependent. But why can't I say "for this query, assume C collation, even if you've been init'd under a utf-8 locale"? That'd save us a whole lot of trouble... cheers, martin ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Reporting services for PostgreSQL
Thank you. How server-side reporting works ? Will it use some C stored proceure in server ? In which format rendered report is sent back ? I need to call it in C# Where to find example calling OpenRpt in MONO / .NET ? Is OpenRpt now in LGPL, I havent found any announcment about licence change ? Is there any roadmap for future, OpenRpt is not updated almost a year ? Andrus. "Ned Lilly" <[EMAIL PROTECTED]> kirjutas sõnumis news:[EMAIL PROTECTED] > Try OpenRPT - server side rendering engine, and client-side GUI designer. > > http://sourceforge.net/projects/openrpt > > Cheers, > Ned > > > On 9/1/2007 7:12 AM Andrus wrote: >> I'm looking for a report generator which renders reports in server and >> sends rendering result >> to client. >> >> any idea ? >> >> Andrus. >> >> >> >> ---(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 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] Do AGGREGATES consistently use sort order?
order/aggregate thing is a general question. > > Yes. > > You can even do this with GROUP BY as long as the leading columns of the ORDER > BY inside the subquery exactly matches the GROUP BY columns. > > In theory we can't promise anything about future versions of Postgres but > there are lots of people doing this already so if ever this was lost there > would probably be some new explicit way to achieve the same thing. Does anyone have any spec links, oracle behavior, or whatever? For now I will trust Postgres to continue behaving sanely, but I am curious. Thx to Gregory for the quick reply > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Is the ole db provider broken?
Hi newsgroup. I am trying to access postgresql with the ole db driver, but it just doesn't seem to work. OUTOFMEMORY messages etc. (I am trying to convert a MSSQL DB to Postgres with the integration services from MSSQL) Is this a known problem? ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Alias "all fields"?
[EMAIL PROTECTED] wrote: > > > Make the table: > > > id | year | value > > ---+--+-- > > 1 | 1970 |23 > > 1 | 1971 |25 > > 1 | 1972 |28 > > ... > > 2 | 1972 | 5 > > 3 | 1971 |94 > > 3 | 1972 | 102 > > > primary key: (id,year) > > value not null > > and be ready. > >the import/update reasons are pretty easily solved > >that way too. > > then your primary key will consists of two fields. > I prefer the primary keys with one field only. Is there any reason for that preference? -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need suggestion on how best to update 3 million rows
Lincoln Yeoh wrote: > At 06:32 PM 9/6/2007, Richard Huxton wrote: > >> Two other tips for bulk-updates like this: >> 1. Do as many columns in one go as you can >> 2. Only update rows that need updating >> >> When you've finished, a CLUSTER/VACUUM FULL can be useful too. > > How about: make sure you have enough free space because the table will > effectively double in size? Assuming it hasn't already been updated a few > times without vacuuming :). It is true for CLUSTER, but not for VACUUM FULL. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] ANY
Alban Hertroys <[EMAIL PROTECTED]> writes: > Richard Huxton wrote: >> AFAIK there are two variants of ANY() >> 1. sets >> 2. arrays >> >> So you should be able to do: >> ... WHERE x = ANY( ARRAY[a, b, c] ) > But then the documentation isn't entirely correct. It suggests that it > works similar to IN, but it doesn't. The subquery variants (section 9.19) do work the same. There is nothing on the other page (9.20) making such a comparison. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgresql 7.3 on Red Hat Enterprise 5 (Problem with SEMMNI, SEMMNS)
Thank you for comments, did I mention the system is a beast? # cat /proc/sys/kernel/shmmax 68719476736 It can not be the resource limit, it has to be something else. I assume this version of postgres is incompatible with RedHat ES 5. Changing to a newer version of postgres is not an option for now. It would take too much time to rewrite the application to work with the changes introduced in the lastest realeses. I have tried PG 7.3.17 version an it works fine, I just can not use it. -- Darek Czarkowski ---(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] a provocative question?
"TJ O'Donnell" <[EMAIL PROTECTED]> writes: > I ran across this quote on Wikipedia at > http://en.wikipedia.org/wiki/Eudora_%28e-mail_client%29 > "Text files are also much safer than databases, in that should disk > corruption occur, most of the mail is likely to be unaffected, and any > that is damaged can usually be recovered." This is mostly FUD. You can get data out of a damaged database, too. (I'd also point out that modern filesystems are nearly as complicated as databases --- try getting your "simple" text files back if the filesystem metadata is fried.) In the end there is no substitute for a good backup policy... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Need suggestion on how best to update 3 million rows
Alvaro Herrera wrote: > Lincoln Yeoh wrote: > > At 06:32 PM 9/6/2007, Richard Huxton wrote: > > > >> Two other tips for bulk-updates like this: > >> 1. Do as many columns in one go as you can > >> 2. Only update rows that need updating > >> > >> When you've finished, a CLUSTER/VACUUM FULL can be useful too. > > > > How about: make sure you have enough free space because the table will > > effectively double in size? Assuming it hasn't already been updated a few > > times without vacuuming :). > > It is true for CLUSTER, but not for VACUUM FULL. Doh, sorry, you were referring to double the space during the UPDATE. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] a provocative question?
Tom Lane wrote: "TJ O'Donnell" <[EMAIL PROTECTED]> writes: I ran across this quote on Wikipedia at http://en.wikipedia.org/wiki/Eudora_%28e-mail_client%29 "Text files are also much safer than databases, in that should disk corruption occur, most of the mail is likely to be unaffected, and any that is damaged can usually be recovered." Should probably insert as well the standard disclaimer about Wikipedia. Great source of info, but that particular sentence has not been corrected yet by the forces-that-dictate-everything-ends-up-correct-sooner-or-later to point out the design trade-offs between simple systems like files (or paper for that matter) vs more complex but safer systems such as databases. And no, I wont write it :) This is mostly FUD. You can get data out of a damaged database, too. (I'd also point out that modern filesystems are nearly as complicated as databases --- try getting your "simple" text files back if the filesystem metadata is fried.) In the end there is no substitute for a good backup policy... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster -- Kenneth Downs Secure Data Software, Inc. www.secdat.comwww.andromeda-project.org 631-689-7200 Fax: 631-689-0527 cell: 631-379-0010
Re: [GENERAL] Postgresql 7.3 on Red Hat Enterprise 5 (Problem with SEMMNI, SEMMNS)
Darek Czarkowski <[EMAIL PROTECTED]> writes: > It can not be the resource limit, it has to be something else. I assume thi= > s version of postgres is incompatible with RedHat ES 5. Changing to a newer= > version of postgres is not an option for now. It would take too much time = > to rewrite the application to work with the changes introduced in the laste= > st realeses. I have tried PG 7.3.17 version an it works fine, I just can no= > t use it. What changes would those be? If your app works on 7.3.4 it should work with 7.3.17. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] an other provokative question??
Relational database pioneer says technology is obsolete http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9034619 kindlt explain how?? sincerely siva ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] an other provokative question??
> -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-general- > [EMAIL PROTECTED] On Behalf Of [EMAIL PROTECTED] > Sent: Thursday, September 06, 2007 12:33 PM > To: pgsql-general@postgresql.org > Subject: [GENERAL] an other provokative question?? > > Relational database pioneer says technology is obsolete > http://www.computerworld.com/action/article.do?command=viewArticleBasic&; ar > ticleId=9034619 > > kindlt explain how?? This bit is a hint: "Column-oriented databases -- such as the one built by Stonebraker's latest start-up, Andover, Mass.-based Vertica Systems Inc. -- store data vertically in table columns rather than in successive rows." Mr. Stonebraker's company sells column oriented databases. So of course the other methods must be "obsolete". It actually is a good idea for some operations. Database warehouses seem to benefit from that storage scheme. All of the database systems that I know of that use this column-oriented scheme are in-memory database systems. I don't know if Mr. Stonebraker's is also. There is at least one open source database that uses columns to store the data: http://monetdb.cwi.nl/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] a provocative question?
[EMAIL PROTECTED] ("TJ O'Donnell") writes:
> I am getting in the habit of storing much of my day-to-day
> information in postgres, rather than "flat" files.
> I have not had any problems of data corruption or loss,
> but others have warned me against abandoning files.
> I like the benefits of enforced data types, powerful searching,
> data integrity, etc.
> But I worry a bit about the "safety" of my data, residing
> in a big scary database, instead of a simple friendly
> folder-based files system.
>
> I ran across this quote on Wikipedia at
> http://en.wikipedia.org/wiki/Eudora_%28e-mail_client%29
>
> "Text files are also much safer than databases, in that should disk
> corruption occur, most of the mail is likely to be unaffected, and any
> that is damaged can usually be recovered."
>
> How naive (optimistic?) is it to think that "the database" can
> replace "the filesystem"?
There is certainly some legitimacy to the claim; the demerits of
things like the Windows Registry as compared to "plain text
configuration" have been pretty clear.
If the "monstrous fragile binary data structure" gets stomped on, by
any means, then you can lose data in pretty massive and invisible
ways. It's most pointedly true if the data representation conflates
data and indexes in some attempt to "simplify" things by having Just
One File. In such a case, if *any* block gets corrupted, that has the
potential to irretrievably destroy the database.
However, the argument may also be taken too far.
-> A PostgreSQL database does NOT assemble data into "one monstrous
fragile binary data structure."
Each table consists of data files that are separate from index
files. Blowing up an index file *doesn't* blow up the data.
-> You are taking regular backups, right???
If you are, that's a considerable mitigation of risks. I don't
believe it's typical to set up off-site backups of one's Windows
Registry, in contrast...
-> In the case of PostgreSQL, mail stored in tuples is likely to get
TOASTed, which changes the shape of things further; the files get
smaller (due to compression), which changes the "target profile"
for this data.
-> In the contrary direction, storing the data as a set of files, each
of which requires storing metadata in binary filesystem data
structures provides an (invisible-to-the-user) interface to
what is, no more or less, than a "monstrous fragile binary data
structure."
That is, after all, what a filesystem is, if you strip out the
visible APIs that turn it into open()/close()/mkdir() calls.
If the wrong directory block gets "crunched," then /etc could get
munched just like the Windows Registry could.
Much of the work going into filesystem efforts, the last dozen years,
is *exceeding* similar to the work going into managing storage in
DBMSes. People working in both areas borrow from each other.
The natural result is that they live in fairly transparent homes in
relation to one another. Someone who "casts stones" of the sort in
your quote is making the fallacious assumption that since the fact
that a filesystem is a database of file information is kept fairly
much invisible, that a filesystem is somehow fundamentally less
vulnerable to the same kinds of corruptions.
Reality is that they are vulnerable in similar ways.
The one thing I could point to, in Eudora, as a *further* visible
merit that DOES retain validity is that there is not terribly much
metadata entrusted to the filesystem. Much the same is true for the
Rand MH "Mail Handler", where each message is a file with very little
filesystem-based metadata.
If you should have a filesystem failure, and discover you have a
zillion no-longer-named in lost+found, and decline to recover from a
backup, it should nonetheless be possible to re-process them through
any mail filters, and rebuild a mail filesystem that will appear
roughly similar to what it was like before.
That actually implies that there is *more* "conservatism of format"
than first meets the eye; in effect, the data is left in raw form,
replete with redundancies that can, in order to retain the ability to
perform this recovery process, *never* be taken out.
There is, in effect, more than meets the eye here...
--
(format nil "[EMAIL PROTECTED]" "cbbrowne" "acm.org")
http://linuxfinances.info/info/advocacy.html
"Lumping configuration data, security data, kernel tuning parameters,
etc. into one monstrous fragile binary data structure is really dumb."
- David F. Skoll
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Do AGGREGATES consistently use sort order?
[EMAIL PROTECTED] (Gregory Stark) writes:
> "Webb Sprague" <[EMAIL PROTECTED]> writes:
>
>> I can always count on (note the order name):
>>
>> \a
>> oregon_2007_08_20=# select array_accum(name) from (select name from
>> placenames where desig='crater' order by name desc) a;
>> array_accum
>> {"Yapoah Crater","West Crater","Twin Craters","Timber Crater","Red
>> Crater","Newberry Crater","Nash Crater","Mount Mazama","Millican
>> Crater","Little Nash Crater","Le Conte Crater","Jordan
>> Craters","Diamond Craters","Coffeepot Crater","Cayuse Crater","Black
>> Crater","Big Hole","Belknap Crater"}
>> (1 row)
>>
>> I am interested in stitching a line out of points in postgis, but
>> the order/aggregate thing is a general question.
>
> Yes.
>
> You can even do this with GROUP BY as long as the leading columns of
> the ORDER BY inside the subquery exactly matches the GROUP BY
> columns.
>
> In theory we can't promise anything about future versions of
> Postgres but there are lots of people doing this already so if ever
> this was lost there would probably be some new explicit way to
> achieve the same thing.
Is there not some risk that the query planner might choose to do
hash-based accumulation could discard the subquery's ordering?
Under the visible circumstances, it's unlikely, but isn't it possible
for the aggregation to pick hashing and make a hash of this?
--
output = reverse("gro.mca" "@" "enworbbc")
http://linuxfinances.info/info/spiritual.html
If anyone ever markets a really well-documented Unix that doesn't
require babysitting by a phalanx of provincial Unix clones, there'll
be a lot of unemployable, twinky-braindamaged misfits out deservedly
pounding the pavements.
---(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] an other provokative question??
Dann Corbit wrote: > All of the database systems > that I know of that use this column-oriented scheme are in-memory > database systems. I don't know if Mr. Stonebraker's is also. KDB+ (http://kx.com/) is column-oriented and has both on-disk and in-memory capabilities http://kx.com/faq/#6 . It's around since 1998 and both column and row oriented databases are still around so I think it'd be more fair to say both are mature technologies. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] a provocative question?
There's also a point in regard to how modifications are made to your data store. In general, things working with text files don't go to much effort to maintain durability like a real database would. The most direct way of editing a text file is to make all the changes in memory, then write the whole thing out. Some editors make backup files, or use a create-delete-rename cycle, but they won't necessarily force the data to disk -- if it's entirely in cache you could end up losing the contents of the file anyway. In the general case on the systems I work with, corruption is a relatively low concern due to the automatic error detection and correction my disks perform, and the consistency guarantees of modern filesystems. Interruptions (e.g. crashes or power failures) are much more likely, and in that regard the typical modification process of text files is more of a risk than working with a database. I've also had times where faulty RAM corrupted gigabytes of data on disk due to cache churn alone. It will always depend on your situation. In both cases, you definitely want backups just for the guarantees neither approach can make. [way off topic] In regard to the Windows Registry in particular... > There is certainly some legitimacy to the claim; the demerits of > things like the Windows Registry as compared to "plain text > configuration" have been pretty clear. > -> You are taking regular backups, right??? > >If you are, that's a considerable mitigation of risks. I don't >believe it's typical to set up off-site backups of one's Windows >Registry, in contrast... Sometimes I think most people get their defining impressions of the Windows Registry from experience with the Windows 9x line. I'll definitely agree that it was simply awful there, and there's much to complain about still, but... The Windows Registry in NT is an actual database, with a WAL, structured and split into several files, replication of some portions in certain network arrangements, redundant backup of key parts in a local system, and any external storage or off-site backup system for Windows worth its salt does, indeed, back it up. It's been that way for about a decade. ---(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] Reporting services for PostgreSQL
Hi Andrus, There are some pretty good PDF docs that would be a good starting point for all of your questions - see http://www.xtuple.org/?q=node/2177. (They're also in the downloads area of the Sourceforge site, but a little hard to find). Speaking of the downloads, if you check there (http://sourceforge.net/project/showfiles.php?group_id=132959&package_id=146756), you'll see that version 2.2 was released on July 27, 2007 - so I'm not sure what you mean about not being updated in almost a year. Yes, as of version 2.2, the project is now licensed under LGPL. We heard from a number of people that they weren't comfortable contributing to a dual-licensed product. If you're interested in commercial support, xTuple has a couple of options at www.openrpt.com. As for the roadmap for the future, I'd encourage you to visit our community site at www.openrpt.org (aka xTuple.org). Cheers, Ned On 9/5/2007 1:21 PM Andrus wrote: Thank you. How server-side reporting works ? Will it use some C stored proceure in server ? In which format rendered report is sent back ? I need to call it in C# Where to find example calling OpenRpt in MONO / .NET ? Is OpenRpt now in LGPL, I havent found any announcment about licence change ? Is there any roadmap for future, OpenRpt is not updated almost a year ? Andrus. "Ned Lilly" <[EMAIL PROTECTED]> kirjutas sõnumis news:[EMAIL PROTECTED] Try OpenRPT - server side rendering engine, and client-side GUI designer. http://sourceforge.net/projects/openrpt Cheers, Ned On 9/1/2007 7:12 AM Andrus wrote: I'm looking for a report generator which renders reports in server and sends rendering result to client. any idea ? Andrus. ---(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 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq ---(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] log_statement and PREPARE
The docs (8.1) say the following about log_statement: -- snip -- ... mod logs all ddl statements, plus INSERT, UPDATE, DELETE, TRUNCATE, and COPY FROM. PREPARE and EXPLAIN ANALYZE statements are also logged if their contained command is of an appropriate type. -- snip -- Can someone please expain the meaning of, "if their contained command is of an appropriate type"? I take it to mean that the prepared statement will be logged if the it contains an INSERT (if 'mod' was chosen, of course). I ask because i've set log_statement to 'mod' but am not seeing any of my prepared statements in the log. INSERT, UPDATE, and friends i do see. FWIW, the app is PHP using MDB2. But checking its source doesn't give me any reason to believe the issue lies there. Shouldn't i see these PREPAREs? brian ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Column as arrays.. more efficient than columns?
On Sep 6, 2007, at 19:37 , Ow Mun Heng wrote: Nobody has any comments on this?? Don't do it. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] tsearch2 anomoly?
Thanks and I didn't know about ts_debug, so thanks for that also.
For the record, I see how to use my own processing function (e.g.
dropatsymbol) to get what I need:
http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro
.html
However, can you explain the logic behind the parsing difference if I just
add a ".s" to a string:
ossdb=# select ts_debug('gallery2-httpd-2.1-conf.');
ts_debug
---
(default,hword,"Hyphenated word",gallery2-httpd-2,{simple},"'2' 'httpd'
'gallery2' 'gallery2-httpd-2'")
(default,part_hword,"Part of hyphenated word",gallery2,{simple},'gallery2')
(default,lpart_hword,"Latin part of hyphenated
word",httpd,{en_stem},'httpd')
(default,float,"Decimal notation",2.1,{simple},'2.1')
(default,lpart_hword,"Latin part of hyphenated word",conf,{en_stem},'conf')
(5 rows)
ossdb=# select ts_debug('gallery2-httpd-2.1-conf.s');
ts_debug
-
(default,host,Host,gallery2-httpd-2.1-conf.s,{simple},'gallery2-httpd-2.1-c
onf.s')
(1 row)
Thanks again,
Bob
On 9/6/07 11:19 AM, "Oleg Bartunov" <[EMAIL PROTECTED]> wrote:
> This is how default parser works. See output from
> select * from ts_debug('gallery2-httpd-conf');
> and
> select * from ts_debug('httpd-2.2.3-5.src.rpm');
>
> All token type:
>
> select * from token_type();
>
>
> On Thu, 6 Sep 2007, RC Gobeille wrote:
>
>> I'm having trouble understanding to_tsvector. (PostreSQL 8.1.9 contrib)
>>
>> In this first case converting 'gallery2-httpd-conf' makes sense to me and is
>> exactly what I want. It looks like the entire string is indexed plus the
>> substrings broken by '-' are indexed.
>>
>>
>> ossdb=# select to_tsvector('gallery2-httpd-conf');
>> to_tsvector
>> -
>> 'conf':4 'httpd':3 'gallery2':2 'gallery2-httpd-conf':1
>>
>>
>> However, I'd expect the same to happen in the httpd example - but it does not
>> appear to.
>>
>> ossdb=# select to_tsvector('httpd-2.2.3-5.src.rpm');
>> to_tsvector
>> ---
>> 'httpd-2.2.3-5.src.rpm':1
>>
>> Why don't I get: 'httpd', 'src', 'rpm', 'httpd-2.2.3-5.src.rpm' ?
>>
>> Is this a bug or design?
>>
>>
>> Thank you!
>> Bob
>
> 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
Re: [GENERAL] log_statement and PREPARE
brian <[EMAIL PROTECTED]> writes: > The docs (8.1) say the following about log_statement: > -- snip -- > ... mod logs all ddl statements, plus INSERT, UPDATE, DELETE, TRUNCATE, > and COPY FROM. PREPARE and EXPLAIN ANALYZE statements are also logged if > their contained command is of an appropriate type. > -- snip -- > Can someone please expain the meaning of, "if their contained command is > of an appropriate type"? I take it to mean that the prepared statement > will be logged if the it contains an INSERT (if 'mod' was chosen, of > course). I think you mis-parsed it. The sentence about PREPARE et al is an independent sentence applying to all the possible values of log_statement. That is, these commands will be logged if the contained command is one that would have been logged, at the current log level. > I ask because i've set log_statement to 'mod' but am not seeing any of > my prepared statements in the log. INSERT, UPDATE, and friends i do see. Ah. Probably you are confusing PREPARE-the-SQL-command, which is what this is speaking of, with the protocol-level prepared-statement functionality. 8.1 is pretty bad about logging extended-query-protocol operations. If you can update to 8.2 you'll be happier. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Postgresql 7.3 on Red Hat Enterprise 5 (Problem with SEMMNI, SEMMNS)
"Scott Marlowe" <[EMAIL PROTECTED]> writes: > On 9/6/07, Tom Lane <[EMAIL PROTECTED]> wrote: >> What changes would those be? If your app works on 7.3.4 it should work >> with 7.3.17. > Actually, from what he wrote, I take it that 7.3.17 works fine, but > some insane policy where he works demands he use 7.3.4 Nah, if they were that troglodytic they'd hardly let him use an OS as newfangled as RHEL-5 (even RHEL-4 shipped with PG 7.4.x). I read him to say that PG 7.4 and up contain changes that break his app, which could be a fair complaint. But if it doesn't work on 7.3.latest then there's something pretty wrong with it. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Column as arrays.. more efficient than columns?
On 9/6/07, Ow Mun Heng <[EMAIL PROTECTED]> wrote: > Table is like > > create table foo ( > number int, > subset int, > value int > ) > > select * from foo; > number | subset | value > 111 > 122 > 1310 > 143 > > current query is like > > select number, > avg(case when subset = 1 then value else null end) as v1, > avg(case when subset = 2 then value else null end) as v2, > avg(case when subset = 3 then value else null end) as v3, > avg(case when subset = 4 then value else null end) as v4 > from foo > group by number arrays are interesting and have some useful problems. however, we must first discuss the problems...first and foremost if you need to read any particular item off the array you must read the entire array from disk and you must right all items back to disk for writes. also, they cause some problems with constraints and other issues that come up with de-normalization tactics. however, If a particular data is expressed actually as an array of items (the polygon type comes to mind), then why not? let'l that said, let's look at a better way to express this query. what jumps out at me right away is: select number, subset, avg(value) from foo group by subset; does this give you the answer that you need? If not we can proceed and look at why arrays may or may not be appropriate (i suspect I am not seeing the whole picture here). merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Column as arrays.. more efficient than columns?
On Thu, 2007-09-06 at 20:20 -0500, Michael Glaesemann wrote: > On Sep 6, 2007, at 19:58 , Ow Mun Heng wrote: > > > Don't denormalise the table? > > Yes. Don't denormalize the tables. I would believe performance would be better it being denormalised. (in this case) > > > don't put them into arrays? > > Yes. Don't use arrays. Caveat: if the data is *naturally* an array > and you will not be doing any relational operations on individual > elements of the arrays, then it makes sense to use arrays. Treat > arrays as you would any other opaque type. Data is naturally an array, and will be used as an array in any case. Since there will not be queries where users will select any one of the values in that array, but the whole array itself. data willbe used in this form code | v1 | v2 | v3 | v4 A 12 10 23 B 10 12 15 22 C 11 24 18 46 D 21 22 20 41 which will be imported into statistical software/excel for further manipulation. I i give them in the denormalised form, it'll take them an addition 30min or so to make them back into the form above. and it'll make the queries more efficient too. index on Code, select * from foo where code = 'B'; By denormalising, I will also get the benefit of reducing the # of rows by a factor of 20.. (20 rows = 1 code) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Column as arrays.. more efficient than columns?
On Sep 6, 2007, at 19:58 , Ow Mun Heng wrote: Don't denormalise the table? Yes. Don't denormalize the tables. don't put them into arrays? Yes. Don't use arrays. Caveat: if the data is *naturally* an array and you will not be doing any relational operations on individual elements of the arrays, then it makes sense to use arrays. Treat arrays as you would any other opaque type. Michael Glaesemann grzm seespotcode net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Column as arrays.. more efficient than columns?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Michael Glaesemann wrote: > > On Sep 6, 2007, at 19:37 , Ow Mun Heng wrote: > >> Nobody has any comments on this?? > > Don't do it. HAHAHAHAHAHAHA Joshua D. Drake > > Michael Glaesemann > grzm seespotcode net > > > > ---(end of broadcast)--- > TIP 2: Don't 'kill -9' the postmaster > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG4KS5ATb/zqfZUUQRAg9wAJ96nzIP18MGtMlRZltoyN0XQb3iogCfSuPd lX7G0aGGq6NbyrHOzW2N1lk= =YaVL -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Call for Speakers PostgreSQL Conference Fall 2007
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, The PostgreSQL Conference Fall 2007 is shaping up nicely. We are now seeking more speakers. Here is the current lineup: 8:00 - 9:00 - Coffee / Social / Wake up / Go back to hotel for socks 9:00 - 9:30 - JoshB - Welcome to 8.3 10:00 - 11:00 - David Wheeler Web 2.0 (Rails) applications with PostgreSQL 11:00 - 12:00 - Need Speaker 12:00 - 13:00 - Lunch 13:00 - 13:30 - Mark Wong - PostgreSQL Performance 13:30 - 14:00 - Joshua Drake - PL/Proxy and Horizontal Scaling 14:00 - 15:00 - Web Sprague - PostGIS (geographic database) 15:00 - 16:00 - David Fetter - Babel of procedural languages 16:00 - 16:30 - Need Speaker 16:30 - 17:00 - Need Speaker 17:00 - 17:30 - Josh Berkus - Stupid Solaris tricks 17:30 - 18:00 - Get to party/dinner 18:00 -- Till they kick us out If you are interested in filling a speaking slot please visit and subscribe (we will be needing speakers for other events as well): http://www.postgresqlconference.org/mailman/listinfo/speakers If you are planning on attending, please join: http://www.postgresqlconference.org/mailman/listinfo/attendees Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG4KUKATb/zqfZUUQRAnWyAJ0SrKcty7OYrr2l1Bl+oUD5hrbO8QCeOjnL ux8FEpvYdC1zysId3ZJ7ToA= =gYsW -END PGP SIGNATURE- ---(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] log_statement and PREPARE
Tom Lane wrote: brian <[EMAIL PROTECTED]> writes: That was understood. What i meant is that the only time i see anything *related to* the prepared statement i think should be there is when the EXECUTE fails for some reason because the context of the error is logged. That particular EXECUTE was preceeded by a PREPARE that was not logged. It was my understanding that the PREPARE would be logged. [ squint... ] It got logged when I tried it. But I was testing 8.1 branch tip (or close to it -- post-8.1.9 for sure). I think you said you were on 8.1.2? There could well have been some related bug fixes in that branch, but I'm too lazy to check the release notes right now. In any case, i'll upgrade soonest. I keep reading here about all the 8.2 goodness. If you can update to 8.2 without too much pain, I'd sure recommend that. But if you run into compatibility problems, it seems that 8.1.9 might perhaps help too. I'm with 8.1.4 now. No big deal, though; this was all because i was trying to debug something and wanted to see the PREPARE come in for a bit. But i've already resolved the initial bug in my app. I was just wondering if i was reading the docs correctly. I'll upgrade. b ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] log_statement and PREPARE
brian <[EMAIL PROTECTED]> writes: > That was understood. What i meant is that the only time i see anything > *related to* the prepared statement i think should be there is when the > EXECUTE fails for some reason because the context of the error is > logged. That particular EXECUTE was preceeded by a PREPARE that was not > logged. It was my understanding that the PREPARE would be logged. [ squint... ] It got logged when I tried it. But I was testing 8.1 branch tip (or close to it -- post-8.1.9 for sure). I think you said you were on 8.1.2? There could well have been some related bug fixes in that branch, but I'm too lazy to check the release notes right now. > In any case, i'll upgrade soonest. I keep reading here about all the 8.2 > goodness. If you can update to 8.2 without too much pain, I'd sure recommend that. But if you run into compatibility problems, it seems that 8.1.9 might perhaps help too. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] log_statement and PREPARE
Tom Lane wrote: brian <[EMAIL PROTECTED]> writes: The only hint of a prepared statement being logged is when there's an error. eg: <2007-09-05 17:35:22 EDT>ERROR: duplicate key violates unique constraint "auth_member_id_key" <2007-09-05 17:35:22 EDT>STATEMENT: EXECUTE mdb2_statement_pgsqla0e8d35156e904f9581ac790eb917b98 (A_HASH_HERE, 5271) That looks like an EXECUTE to me. If you want EXECUTEs to be logged conditionally based on what they are executing, you need 8.2. That was understood. What i meant is that the only time i see anything *related to* the prepared statement i think should be there is when the EXECUTE fails for some reason because the context of the error is logged. That particular EXECUTE was preceeded by a PREPARE that was not logged. It was my understanding that the PREPARE would be logged. In any case, i'll upgrade soonest. I keep reading here about all the 8.2 goodness. brian ---(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] array_to_records function
Here I'm posting a function to convert array to records. any other suggestions are welcome create or replace function array_to_records(int[]) RETURNS SETOF record AS $$ DECLARE ret_rec record; a int; b int; BEGIN b = length(array_dims($1)); a = substr(array_dims($1),4, (b-4) ); FOR i IN 1.. a LOOP FOR ret_rec IN select i, $1[i] LOOP RETURN NEXT ret_rec; END LOOP; END LOOP; RETURN; END; $$ LANGUAGE 'plpgsql';
Re: [GENERAL] log_statement and PREPARE
brian <[EMAIL PROTECTED]> writes: > The only hint of a prepared statement being logged is when there's an > error. eg: > <2007-09-05 17:35:22 EDT>ERROR: duplicate key violates unique > constraint "auth_member_id_key" > <2007-09-05 17:35:22 EDT>STATEMENT: EXECUTE > mdb2_statement_pgsqla0e8d35156e904f9581ac790eb917b98 (A_HASH_HERE, 5271) That looks like an EXECUTE to me. If you want EXECUTEs to be logged conditionally based on what they are executing, you need 8.2. regards, tom lane ---(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] Compiling Pl/Perl on Mac OSX
Logan Bowers <[EMAIL PROTECTED]> writes: > Has anyone had any luck compiling the Pl/Perl language on Mac OSX > (10.4)? I get the following error: Worksforme ... which Postgres version are you using exactly? > gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith - > Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict- > aliasing -bundle -multiply_defined suppress plperl.o spi_internal.o > SPI.o -L/opt/local/lib -L/usr/local/lib -L/opt/local/lib/perl5/5.8.8/ > darwin-2level/CORE -L../../../src/port -L/System/Library/Perl/5.8.6/ > darwin-thread-multi-2level/CORE/ /opt/local/lib/perl5/5.8.8/ > darwin-2level/auto/DynaLoader/DynaLoader.a -lperl -ldl -lm -lc - > bundle_loader ../../../src/backend/postgres -o libplperl.0.0.so Actually, I'm wondering about the references to /opt/local/lib/perl5/ there; I don't see those in my build --- here's my command for linking plperl: gcc -no-cpp-precomp -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -g -bundle -multiply_defined suppress plperl.o spi_internal.o SPI.o -L/usr/local/lib -L/System/Library/Perl/5.8.6/darwin-thread-multi-2level/CORE -L../../../src/port /System/Library/Perl/5.8.6/darwin-thread-multi-2level/auto/DynaLoader/DynaLoader.a -lperl -ldl -lm -lc -bundle_loader ../../../src/backend/postgres -o libplperl.0.0.so I'm a bit suspicious that you have a non-shared-library build of Perl under /opt, and you've done something that makes Postgres find that instead of the Apple-supplied version. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] log_statement and PREPARE
Tom Lane wrote: brian <[EMAIL PROTECTED]> writes: But that should mean that my prepared statement that contains an INSERT should be logged, yes? (8.1 issues notwithstanding) I ask because i've set log_statement to 'mod' but am not seeing any of my prepared statements in the log. INSERT, UPDATE, and friends i do see. Hm, works for me: regression=# create temp table fooey(f1 int); CREATE TABLE regression=# prepare foo(int) as insert into fooey values($1); PREPARE regression=# execute foo(42); INSERT 0 1 regression=# show log_statement; log_statement --- mod (1 row) in log: LOG: statement: create temp table fooey(f1 int); LOG: statement: prepare foo(int) as insert into fooey values($1); The same sequence in 8.2 logs: LOG: statement: create temp table fooey(f1 int); LOG: statement: prepare foo(int) as insert into fooey values($1); LOG: statement: execute foo(42); DETAIL: prepare: prepare foo(int) as insert into fooey values($1); OK, maybe i *am* confused about PREPARE. The PEAR MDB2 source is doing: $query = 'PREPARE '.$statement_name.$types_string.' AS '.$query; which becomes something like: PREPARE mdb2_statement_pgsqla0e8d35156e904f9581ac790eb917b98 AS ... So i think i see your point. The "mdb2_statement_pgsql ... " string identifier is what is then passed along with my data to Pg when executed. So, quite different than "EXECUTE foo(42)". And this sort of thing is not logged? Even in 8.2? On the chance that there really is something amiss, i'll continue ... I know that the change to postgresql.conf has been recognised because i've been seeing the regular queries logged since changing log_statement. The only hint of a prepared statement being logged is when there's an error. eg: <2007-09-05 17:35:22 EDT>ERROR: duplicate key violates unique constraint "auth_member_id_key" <2007-09-05 17:35:22 EDT>STATEMENT: EXECUTE mdb2_statement_pgsqla0e8d35156e904f9581ac790eb917b98 (A_HASH_HERE, 5271) Otherwise, nothing but INSERT, UPDATE, etc. I'm using 8.1.4, btw. I'll take this over to the PEAR list. If what you were looking for was that the *execute* gets logged dependent on what the prepared statement was, then you need 8.2. All that stuff got rewritten pretty heavily for 8.2 ... Yeah, i'll upgrade and check that out. Thanks again. brian ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] a provocative question?
[EMAIL PROTECTED] ("Trevor Talbot") writes:
> There's also a point in regard to how modifications are made to your
> data store. In general, things working with text files don't go to
> much effort to maintain durability like a real database would. The
> most direct way of editing a text file is to make all the changes in
> memory, then write the whole thing out. Some editors make backup
> files, or use a create-delete-rename cycle, but they won't
> necessarily force the data to disk -- if it's entirely in cache you
> could end up losing the contents of the file anyway.
In the case of Eudora, if its filesystem access protocol involves
writing a new text file, and completing that before unlinking the old
version, then the risk of "utter destruction" remains fairly low
specifically because of the nature of access protocol.
> In the general case on the systems I work with, corruption is a
> relatively low concern due to the automatic error detection and
> correction my disks perform, and the consistency guarantees of
> modern filesystems. Interruptions (e.g. crashes or power failures)
> are much more likely, and in that regard the typical modification
> process of text files is more of a risk than working with a
> database.
Error rates are not so low that it's safe to be cavalier about this.
> I've also had times where faulty RAM corrupted gigabytes of data on
> disk due to cache churn alone.
Yeah, and there is the factor that as disk capacities grow, the
chances of there being errors grow (more bytes, more opportunities)
and along with that, the number of opportunities for broken checksums
to match by accident also grow. (Ergo "don't be cavalier" unless you
can be pretty sure that your checksums are getting more careful...)
> It will always depend on your situation. In both cases, you
> definitely want backups just for the guarantees neither approach can
> make.
Certainly.
> [way off topic]
> In regard to the Windows Registry in particular...
>
>> There is certainly some legitimacy to the claim; the demerits of
>> things like the Windows Registry as compared to "plain text
>> configuration" have been pretty clear.
>
>> -> You are taking regular backups, right???
>>
>>If you are, that's a considerable mitigation of risks. I don't
>>believe it's typical to set up off-site backups of one's Windows
>>Registry, in contrast...
>
> Sometimes I think most people get their defining impressions of the
> Windows Registry from experience with the Windows 9x line. I'll
> definitely agree that it was simply awful there, and there's much to
> complain about still, but...
>
> The Windows Registry in NT is an actual database, with a WAL,
> structured and split into several files, replication of some portions
> in certain network arrangements, redundant backup of key parts in a
> local system, and any external storage or off-site backup system for
> Windows worth its salt does, indeed, back it up.
>
> It's been that way for about a decade.
I guess I deserve that :-).
There is a further risk, that is not directly mitigated by backups,
namely that if you don't have some lowest common denominator that's
easy to recover from, you may not have a place to recover that data.
In the old days, Unix filesystems were sufficiently buggy corruptible
that it was worthwhile to have an /sbin partition, all statically
linked, generally read-only, and therefore seldom corrupted, to have
as a base for recovering the rest of the system.
Using files in /etc, for config, and /sbin for enough tools to recover
with, provided a basis for recovery.
In contrast, there is definitely risk to stowing all config in a DBMS
such that you may have the recursive problem that you can't get the
parts of the system up to help you recover it without having the DBMS
running, but since it's corrupted, you don't have the config needed to
get the system started, and so we recurse...
--
let name="cbbrowne" and tld="linuxdatabases.info" in name ^ "@" ^ tld;;
http://www3.sympatico.ca/cbbrowne/linuxdistributions.html
As of next Monday, TRIX will be flushed in favor of VISI-CALC.
Please update your programs.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Call for Speakers PostgreSQL Conference Fall 2007
On Sep 6, 2007, at 21:10 , Joshua D. Drake wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, The PostgreSQL Conference Fall 2007 is shaping up nicely. We are now seeking more speakers. Here is the current lineup: What's the difference between the conference groups at http:// www.postgresqlconference.org/ and http://www.pgcon.org/2008/? I am subscribed to general and hackers and this is the first time I've seen this particular conference mentioned. Could the announcements be made on general as well? Do I need to subscribe to advocacy too? Cheers, M ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Column as arrays.. more efficient than columns?
On Thu, 2007-09-06 at 20:19 -0700, Joe Conway wrote: > Ow Mun Heng wrote: > > => select code, round(avg(case when subset = '0' then value else null > > end),0) as v0, > > round(avg(case when subset = '1' then value else null end),0) as v1, > > round(avg(case when subset = '2' then value else null end),0) as v2, > > round(avg(case when subset = '3' then value else null end),0) as v3, > > round(avg(case when subset = '4' then value else null end),0) as v4 > > from foo > > group by code; > > code | v0 | v1 | v2 | v3 | v4 > > --+++++ > > A| 98 | 20 | 98 | 98 | 98 > > An alternative way to get the output below, would be to feed your > aggregate query above to the crosstab() function in contrib/tablefunc. I just looked at it and seems like the ... row_name and value must be of type text ... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] an other provokative question??
Erik Jones <[EMAIL PROTECTED]> writes: > I'm curious, given that Postgres wasn't even an SQL-centric database > when the original project ended, how much of the current Postgres > code base still contains code from the original project before the > incorporation of SQl rename to PostgreSQL? You can still find a lot of code in the current CVS that has obvious ancestry in Postgres v4r2. I think there might not be too many lines that have never been changed at all, but nobody who could read C would have any problem detecting the relationship. Elein might have more to say on the point... I'm just a newbie. 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
Re: [GENERAL] dblink vs dbi-link (and errors compiling)
On Fri, 2007-09-07 at 00:17 -0500, Erik Jones wrote: > On Sep 6, 2007, at 10:54 PM, Ow Mun Heng wrote: > > > In either of the above, I would like to know which one is able to help > > me to like connect to a remote DB, use the table there and join to a > > local table in PG so that I don't have to use DBI to pull and insert > > those data into the local PG database. > > Neither. To the best of my knowledge, there isn't anything that will > allow you to to do that. Select the data you need from the remote > source into a temp table and join against that if you don't want to > keep the data locally after you've used it. Ah.. Too bad.. There goes my "easy peasy life" out the window. But in any case, good to know.. dbi-link would be what I would want to try out. > > > > BTW, dblink doesn't compile. (8.2.4) > > > > dblink.c:56:28: error: utils/fmgroids.h: No such file or directory > > dblink.c: In function 'get_pkey_attnames': > In the src/contrib/dblink/ directory of the source tree you built > postgres from just do > > make > make install Did that.. ended up with above error. the tablefunc compile went through though. BTW, this is gentoo and though I compiled it from source, the compiled tarballs are deleted. I did a configure in the PG source main dir and then proceeded to do a make in the contrib/dblink directory with above errors. Anyhow.. dbi-link is what I'll play with. Thanks.. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Column as arrays.. more efficient than columns?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 20:53, Merlin Moncure wrote: [snip] > > arrays are interesting and have some useful problems. however, we > must first discuss the problems...first and foremost if you need to > read any particular item off the array you must read the entire array > from disk and you must right all items back to disk for writes. Reads and writes are done at the page level, so I'm not sure this is valid. >also, > they cause some problems with constraints and other issues that come > up with de-normalization tactics. ACK. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG4N+/S9HxQb37XmcRApl6AJ43p087jXwHs2LHGlr+JoIUVs8s7QCgmRWY BjV99QNGxKQnel3vQ4RuBMA= =IeDI -END PGP SIGNATURE- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] dblink vs dbi-link (and errors compiling)
On Sep 6, 2007, at 10:54 PM, Ow Mun Heng wrote: I'm confused as to the difference between dblink and dbi-link. dblink is included in the contrib directory and dbi-link is available from pgfoundry. dblink seems like it creates a view of a remote DB and is static, which means that it needs to be refreshed each time. Yes. dblink allows you to run queries from one postgres database against another postgres database. dbi-link seems like it uses perl's dbi to connect to a remote db. dbi-link allows you to run queries from one postgres database against any database that can be accessed via perl's dbi library. In either of the above, I would like to know which one is able to help me to like connect to a remote DB, use the table there and join to a local table in PG so that I don't have to use DBI to pull and insert those data into the local PG database. Neither. To the best of my knowledge, there isn't anything that will allow you to to do that. Select the data you need from the remote source into a temp table and join against that if you don't want to keep the data locally after you've used it. BTW, dblink doesn't compile. (8.2.4) gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing - fpic -I../../src/interfaces/libpq -I. -I../../src/include - D_GNU_SOURCE -c -o dblink.o dblink.c dblink.c:56:28: error: utils/fmgroids.h: No such file or directory dblink.c: In function 'get_pkey_attnames': dblink.c:1684: error: 'F_OIDEQ' undeclared (first use in this function) dblink.c:1684: error: (Each undeclared identifier is reported only once dblink.c:1684: error: for each function it appears in.) make: *** [dblink.o] Error 1 In the src/contrib/dblink/ directory of the source tree you built postgres from just do make make install Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] an other provokative question??
On Fri, 7 Sep 2007, Ron Johnson wrote: Definitely a niche product. Stonebraker's commentary was unfortunately spun by the ComputerWorld columnist. I hope people followed the link to his actual blog entry at http://www.databasecolumn.com/2007/09/one-size-fits-all.html where his arguement is that the idea of one database approach always being right just isn't true anyway. With that mindset, every technology is a niche product of sorts; just the size of the niche varies. Given past history of this project and its relation to Stonebraker, I was tempted earlier today to suggest that the Postgres vs. PostgreSQL renaming argument be dropped in favor of renaming the database "Horizontica". -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] dblink vs dbi-link (and errors compiling)
I'm confused as to the difference between dblink and dbi-link. dblink is included in the contrib directory and dbi-link is available from pgfoundry. dblink seems like it creates a view of a remote DB and is static, which means that it needs to be refreshed each time. dbi-link seems like it uses perl's dbi to connect to a remote db. In either of the above, I would like to know which one is able to help me to like connect to a remote DB, use the table there and join to a local table in PG so that I don't have to use DBI to pull and insert those data into the local PG database. BTW, dblink doesn't compile. (8.2.4) gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fpic -I../../src/interfaces/libpq -I. -I../../src/include -D_GNU_SOURCE -c -o dblink.o dblink.c dblink.c:56:28: error: utils/fmgroids.h: No such file or directory dblink.c: In function 'get_pkey_attnames': dblink.c:1684: error: 'F_OIDEQ' undeclared (first use in this function) dblink.c:1684: error: (Each undeclared identifier is reported only once dblink.c:1684: error: for each function it appears in.) make: *** [dblink.o] Error 1 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Column as arrays.. more efficient than columns?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 21:26, Ow Mun Heng wrote: > On Thu, 2007-09-06 at 20:57 -0500, Michael Glaesemann wrote: >> On Sep 6, 2007, at 20:46 , Ow Mun Heng wrote: > >>> I would believe performance would be better it being denormalised. (in >>> this case) >> I assume you've arrived at the conclusion because you have >> (a) shown >> that the performance with a normalized schema does not meet your >> needs; >> (b) benchmarked the normalized schema under production >> conditions; >> (c) benchmarked the denormalized schema under production >> conditions; and >> (d) shown that performance is improved in the >> denormalized case to arrive at that conclusion. I'm interested to see >> the results of your comparisons. > >> Regardless, it sounds like you've already made up your mind. Why ask >> for comments? > > You've assumed wrong. I've not arrived at any conclusion but merely > exploring my options on which way would be the best to thread. I'm > asking the list because I'm new in PG and after reading all those > articles on highscalability etc.. majority of them are all using some > kind of denormalised tables. Correlation != causation. There *might* be a causal relationship between high scalability and table denormalization, but I seriously doubt it. > Right now, there's 8 million rows of data in this one table, and growing > at a rapid rate of ~2 million/week. I can significantly reduce this > number down to 200K (i think by denormalising it) and shrink the table > size. Even presuming you only insert data SIX hours per day, that's only 13.3 inserts per second. Not very impressive. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG4N81S9HxQb37XmcRArnRAJ9T2vOWe+RTWK99zYKCXIVfzisY5ACg3s8H NAeykgSGT2jeiXUa8P8oRAQ= =GBcW -END PGP SIGNATURE- ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] an other provokative question??
On Sep 6, 2007, at 10:54 PM, Tom Lane wrote: "Dann Corbit" <[EMAIL PROTECTED]> writes: Relational database pioneer says technology is obsolete http://www.computerworld.com/action/article.do? command=3DviewArticleBasic&articleId=3D9034619 This bit is a hint: "Column-oriented databases -- such as the one built by Stonebraker's latest start-up, Andover, Mass.-based Vertica Systems Inc. -- store data vertically in table columns rather than in successive rows." Mr. Stonebraker's company sells column oriented databases. So of course the other methods must be "obsolete". I don't see anything in there where Stonebraker says that relational DBs are obsolete. What he suggests is that column-oriented storage might beat row-oriented storage for a lot of modern applications. He might be right (I'm sure not going to bet against the guy who started Postgres) but this has not got anything to do with the concept of a relational database. It's an implementation detail --- maybe a pretty fundamental one, but in principle you could build a DB either way and no user could see a semantic difference. I'm curious, given that Postgres wasn't even an SQL-centric database when the original project ended, how much of the current Postgres code base still contains code from the original project before the incorporation of SQl rename to PostgreSQL? Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.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
[GENERAL] Security Advances in Postgresql over other RDBMS
Hi, The way postgres has the concept of host base authentication, is this a step forward over other RDBMS like sql server and oracle? I was wondering, what are some novel security features in postgres as compared to other RDBMS. Thanks, Jas
Re: [GENERAL] an other provokative question??
"Dann Corbit" <[EMAIL PROTECTED]> writes: >> Relational database pioneer says technology is obsolete >> http://www.computerworld.com/action/article.do?command=3DviewArticleBasic&articleId=3D9034619 > This bit is a hint: > "Column-oriented databases -- such as the one built by Stonebraker's > latest start-up, Andover, Mass.-based Vertica Systems Inc. -- store data > vertically in table columns rather than in successive rows." > Mr. Stonebraker's company sells column oriented databases. So of course > the other methods must be "obsolete". I don't see anything in there where Stonebraker says that relational DBs are obsolete. What he suggests is that column-oriented storage might beat row-oriented storage for a lot of modern applications. He might be right (I'm sure not going to bet against the guy who started Postgres) but this has not got anything to do with the concept of a relational database. It's an implementation detail --- maybe a pretty fundamental one, but in principle you could build a DB either way and no user could see a semantic difference. Count on a reporter to overstate the argument ... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Call for Speakers PostgreSQL Conference Fall 2007
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 A.M. wrote: > > On Sep 6, 2007, at 21:10 , Joshua D. Drake wrote: > >> -BEGIN PGP SIGNED MESSAGE- >> Hash: SHA1 >> >> Hello, >> >> The PostgreSQL Conference Fall 2007 is shaping up nicely. We are now >> seeking more speakers. Here is the current lineup: > > What's the difference between the conference groups at > http://www.postgresqlconference.org/ and http://www.pgcon.org/2008/? The "PostgreSQL Conference" series of conferences are regional community conferences designed to help strengthen our user base and fund raise for the PostgreSQL community. All registrations and sponsorships go directly to the community via SPI, a 501c3 non profit. PgCon (The PostgreSQL Conference) is a commercial conference in Canada that takes place in May. It is run by a community member by the name of Dan Langille and it tracks along BSDCon. The Fall conference is in Portland Oregon where we are a very short (and reasonable cheap flight) from Seattle, Denver, Phoenix, and The Bay. The Winter conference is attached to SCALE and will run in conjunction with their conference. It is in Southern California. The two summer conferences are OSCON, which will have a repeat (but possibly two days) of this years OSCON PostgreSQL conference. The second of the summer would be along side LWE in August of 08. Then we wrap to Fall 2008 in Portland again. Is is our intent to also have at least one geographically strategic conference on the east coast, and we are attempting to work with the European communities as well. Sincerely, Joshua D. Drake > > I am subscribed to general and hackers and this is the first time I've > seen this particular conference mentioned. Could the announcements be > made on general as well? Do I need to subscribe to advocacy too? > > Cheers, > M > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG4M0KATb/zqfZUUQRAuLeAKCN+zvLbGJOOhvAE+YSBg+OPvGq3QCeJ+Qo Bpk+kVWxIPuQlrFoUamckIc= =f7/I -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] an other provokative question??
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 09/06/07 22:54, Tom Lane wrote: > "Dann Corbit" <[EMAIL PROTECTED]> writes: >>> Relational database pioneer says technology is obsolete >>> http://www.computerworld.com/action/article.do?command=3DviewArticleBasic&articleId=3D9034619 > >> This bit is a hint: >> "Column-oriented databases -- such as the one built by Stonebraker's >> latest start-up, Andover, Mass.-based Vertica Systems Inc. -- store data >> vertically in table columns rather than in successive rows." > >> Mr. Stonebraker's company sells column oriented databases. So of course >> the other methods must be "obsolete". > > I don't see anything in there where Stonebraker says that relational DBs > are obsolete. What he suggests is that column-oriented storage might Does "column-oriented storage" mean that all of the COLUMN_A values for all 200 million rows are stored together on adjacent pages? If so, then doing aggregates (the bread and butter of DW) *would* seem to be faster. But b-tree leaf that points to "a record" would need num_cols pointers instead of one pointer. Very messy. And large. Definitely a niche product. > beat row-oriented storage for a lot of modern applications. He might be > right (I'm sure not going to bet against the guy who started Postgres) > but this has not got anything to do with the concept of a relational > database. It's an implementation detail --- maybe a pretty fundamental > one, but in principle you could build a DB either way and no user could > see a semantic difference. - -- Ron Johnson, Jr. Jefferson LA USA Give a man a fish, and he eats for a day. Hit him with a fish, and he goes away for good! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFG4OF4S9HxQb37XmcRAtQeAKCGqjOcdmT6ccrbMy/JDOURjYItSACfVu7/ AEdP1gbDPK/MNwCVlCb1IAg= =PD28 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
