Re: [GENERAL] very long update gin index troubles back?

[Ivan Sergio Borgonovo]

On Wed, 28 Jan 2009 23:38:47 +0300 (MSK)
Oleg Bartunov o...@sai.msu.su wrote:

 On Wed, 28 Jan 2009, Ivan Sergio Borgonovo wrote:

  On Tue, 27 Jan 2009 20:45:53 +0300
  Teodor Sigaev teo...@sigaev.ru wrote:

  No matter if I drop the trigger that update agg content and the
  fact that I'm just updating d, postgresql will update the
  index?
  Yes, due to MVCC. Update of row could produce new version
  (tuple) and new version should be index as old one.

  Does that mean that it could be a good choice to place the
  tsvector in another table?

 this is a trade-off - additional join

gin index creation/update seems to put a lot of pressure on the box.

While the total amount of time saved for every search may exceed the
time saved avoiding to reindex when not strictly necessary, when the
box is reindexing a large set of records it is on its knees.

Consider that, excluding the update that cause the reindex, the
table could be considered readonly and during tests it was
actually readonly while in production I may expect no more than 10
clients waiting to write on the table during an index update.
Still I'm wondering how the index updated when I eg. do something
like:

create table1 (
 x int,
 y int,
 fti tsvector
);

update table1 set x=10 where y=11; and more than one record is
involved.
Will the whole table be locked till the whole statement end or the
table will be locked just when every single row is updated till that
row is reindexed?

Is there any way to control reindexing priority (a sort of nice)?

CONCURRENTLY doesn't look as what I'm looking for.

A solution that could be just fired and forgotten could be OK.
Once you define an index CONCURRENTLY I can't understand how to
check for invalidity and when.

The problem of scheduling the update of the tsvector is that a
search may return a wrong result till the tsvector is updated.
It would be nice if when an index entry is stale postgresql could
look at real data... but well that's not a field I can speculate
on.

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] getting no days problem

[sanjeev kumar]

Hi all,

I have written procedure as follows:
-
--procedure begin
Create Procedure sp_getNoOfDays(
 ip_startDateIN date,
 ip_endDate  IN date,
 op_noofdays OUT bigint
)
is
v_DOBMonthnumber(20);
v_currMonth   number(20);
v_absMonthsDiff   number(20);
cnt_noOfDays  bigint;
Begin
dbms_output.put_line('1 '||ip_startDate);
dbms_output.put_line('2 '||ip_endDate);
 v_DOBMonth:=to_char(ip_startDate,'MM');
dbms_output.put_line('3 ');
  v_currMonth:=to_char(ip_endDate,'MM');
dbms_output.put_line('4 ');
 v_absMonthsDiff:=abs(to_number(v_currMonth-v_DOBMonth));
dbms_output.put_line('5 '||v_absMonthsDiff);

if v_absMonthsDiff1
then
dbms_output.put_line('6 ');
select extract(day from ip_endDate)-extract(day from ip_startDate)
into   cnt_noOfDays
from   dual;
dbms_output.put_line('7 ');
else
select ip_endDate-ip_startDate
into cnt_noOfDays
from dual;
--cnt_noOfDays:=0;
end if;

op_noofdays:=cnt_noOfDays;
dbms_output.put_line('8 ');
Exception
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error Message : '||SQLERRM||'Error Code :
'||SQLCODE);

End;
--procedure end
-
--Here I am executing

declare
opn bigint;
begin
sp_getNoOfDays('02-Feb-2009','29-Jan-2009',opn);
dbms_output.put_line('No of Days is ='||opn);
end;
--
--I am not getting required results but getting error as follows:

INFO:  1 02-FEB-09 00:00:00
INFO:  2 29-JAN-09 00:00:00
INFO:  3
INFO:  4
INFO:  5 1
INFO:  Error Message : EDB-22P02: invalid input syntax for integer: @ 4
days agoError Code : 22P02
INFO:
-
Please any one can help me

-- 
Thanks  Regards,
-Sanjeev (MIT)

Re: [GENERAL] getting no days problem

[Thom Brown]

2009/1/29 sanjeev kumar vannalaspg...@gmail.com

 Hi all,

 I have written procedure as follows:
 -
 --procedure begin
 Create Procedure sp_getNoOfDays(
  ip_startDateIN date,
  ip_endDate  IN date,
  op_noofdays OUT bigint
 )
 i


Create Procedure?  Are you sure this isn't for Oracle?  Shouldn't this be a
function?

Thom

Re: [GENERAL] Slow first query despite LIMIT and OFFSET clause

[Gregory Stark]

Phoenix Kiula phoenix.ki...@gmail.com writes:

  Index Cond: ((user_id)::text = 'superman'::text)
  Filter: (status = 'Y'::bpchar)

 Of course for unregistered users we use user_known = 0, so they are
 excluded from this index. Is this not a useful partial index? I think
 in this SQL, the user_id is always superman and the user_known
 always 1 which is why the guesstimate from the planner may be off?

Well the histograms are for each column separately, so the planner will take
the selectivity estimates for user_id='superman' and status = 'Y' and multiply
them.

If the status of 'superman' records are very different from the status
records as a whole then this will give poor results.

If that's not the case then raising the statistics target for those two
columns might help. And of course if the table hasn't been analyzed recently
then analyzing it more often is always good.

There isn't really a good solution for cross-column stats. You could perhaps
create a functional index (could still be partial too) on an expression like

  CASE WHEN status = 'Y' THEN superman

Which will make Postgres build stats for the result of that expression
specifically. Then if you use that expression exactly as-is in the query the
planner should those statistics. I think. I haven't tried this... Tell us how
it goes :)

I wonder if we should look at building partial histograms for the columns in
partial indexes effectively equivalent to this... hm...

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Encoding problem using pg_dumpall

[Moshe Ben-Shoham]

Hi,

 

I have a database with encoding UTF-8 installed on Windows, and I try to
dump it using pg_dumpall, on the machine on which the database is
installed. I get the following error message:

 

C:\Program Files\PostgreSQL\8.3\binpg_dumpall -U admint 
c:\temp\dbdump.sql

pg_dump: SQL command failed

pg_dump: Error message from server: ERROR:  character 0xd595 of encoding
UTF8 has no equivalent in WIN1252

pg_dump: The command was: COPY public.cms_history_properties
(structure_id, propertydef_id, property_mapping_id,
property_mapping_type, property_value, publish_tag) TO stdout;

pg_dumpall: pg_dump failed on database opencms, exiting

 

it probably means that some UTF-8 characters cannot be represented in
WIN1252 which is the encoding of the output file. 

 

Is there any solution to this problem?

 

Thanks,

Moshe Ben Shoham.





The information contained in this message is proprietary to the sender, 
protected from disclosure, and may be privileged. The information is intended 
to be conveyed only to the designated recipient(s) of the message. If the 
reader of this message is not the intended recipient, you are hereby notified 
that any dissemination, use, distribution or copying of this communication is 
strictly prohibited and may be unlawful. If you have received this 
communication in error, please notify us immediately by replying to the message 
and deleting it from your computer. Thank you.








This footnote confirms that this email message has been scanned by
PineApp Mail-SeCure for the presence of malicious code, vandals  computer 
viruses.

[GENERAL] updateable view: message

[Harald Armin Massa]

Hello,

I am using updateable views to have time-based tables (some status is
valid for some time and has to be preserved after updates, inserts and
deletes)

I created DO INSTEAD rules for update, insert and delete, example:


CREATE OR REPLACE RULE formularfeld_update AS
ON UPDATE TO formularfeld

DO INSTEAD ( UPDATE otformularfeld SET validbis = now(),
letztespeicherung = now()
  WHERE otformularfeld.id_formfeld = old.id_formfeld AND
otformularfeld.validbis = '-12-31 00:00:00'::timestamp without
time zone AND otformularfeld.quarant = get_quarant();

 INSERT INTO otformularfeld (id_formfeld, id_formular, id_bf,
sortierung, ebene, gruppe, letztespeicherung, einblenden, ausblenden,
drucknr, untergruppe, validvon, validbis, id_user, quarant)
  VALUES (new.id_formfeld, new.id_formular, new.id_bf, new.sortierung,
new.ebene, new.gruppe, now(), new.einblenden, new.ausblenden,
new.drucknr, new.untergruppe, now(), 'infinity'::timestamp without
time zone, get_user(), get_quarant());
);

(that is: write this line is no longer valid, and from now on this
line is valid instead)


Everything works fine. Just the feedbacks are ... disturbing:
update formularfeld set sortierung=1442 where id_formfeld=13798

leads to:

Query returned successfully: 0 rows affected, 234 ms execution time.

which is correct in one sence:
 - within the updateable view there were 0 rows affected - all stuff
is done in otformularfeld; the table behind the view

on the other hand: the instead rule touched two rows in
otformularfeld. And this is reflected nowhere.

So my question: can I have influence on that query result message, and
if yes, how? My only option so far would be to have some function
called within the instead rule that raises NOTICE ... which seems
rather hackisch.


Harald
-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
EuroPython 2009 will take place in Birmingham - Stay tuned!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Text search segmentation fault

[Tommy Gildseth]

While trying to create a new dictionary for use with PostgreSQL text 
search, I get a segfault. My Postgres version is 8.3.5


The dictionary I use is the Norwegian Bokmål  Nynorsk (Norway) pack 
for OOo 2.x downloaded from 
http://wiki.services.openoffice.org/wiki/Dictionaries#Norwegian_.28Norway.29


I've also uploaded the dictionary and affix-file used to this location:
http://folk.uio.no/tommygi/postgres/nb_no.dict
http://folk.uio.no/tommygi/postgres/nb_no.affix

These are unpacked from the zip-file I got from the above location, and 
converted to UTF-8 with the following commands:

  - iconv -f latin1 -t utf-8 nb_NO.dic  nb_no.dict
  - iconv -f latin1 -t utf-8 nb_NO.aff  nb_no.affix

The command I use is this:
CREATE TEXT SEARCH DICTIONARY no_ispell (
TEMPLATE = ispell,
DictFile = nb_no,
AffFile =  nb_no,
StopWords = norwegian
);


I've uploaded a strace of the process at 
http://folk.uio.no/tommygi/postgres/strace.txt
This is captured with strace -ppid. Since this is about the extent of 
my knowledge of strace, please let me know if there's any other options 
I should specify.


Relevant parts of the postgres log file is included below.

[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG:  server 
process (PID 12002) was terminated by signal 11: Segmentation fault
[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG: 
terminating any other active server processes
[2009-01-29 13:55:11.725 CET] [pgtest02] [:] [] [26466] [0] LOG:  all 
server processes terminated; reinitializing
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: 
database system was interrupted; last known up at 2009-01-29 13:42:05 CET
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: 
database system was not properly shut down; automatic recovery in progress
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG:  record 
with zero length at 0/4A2F9A0
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG:  redo 
is not required
[2009-01-29 13:55:11.986 CET] [pgtest02] [fotoportal:postgres] 
[192.168.6.137(49650)] [12019] [0] FATAL:  the database system is in 
recovery mode
[2009-01-29 13:55:11.988 CET] [pgtest02] [fotoportal:postgres] 
[192.168.6.137(49655)] [12020] [0] FATAL:  the database system is in 
recovery mode
[2009-01-29 13:55:12.010 CET] [pgtest02] [:] [] [12023] [0] LOG: 
autovacuum launcher started
[2009-01-29 13:55:12.011 CET] [pgtest02] [:] [] [26466] [0] LOG: 
database system is ready to accept connections



--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Pet Peeves?

[Gregory Stark]

I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
but I would be interested to hear if people have any complaints from personal
experience. What would be most interesting is if you can explain an example of
when the problem caused real inconvenience to you, since sometimes it's hard
to see from a theoretical description where the real harm lies.

So, what do people say? Is Postgres perfect in your world or does it do some
things which rub you the wrong way?

Feel free to respond on-list or if you prefer in personal emails. I do intend
to use the ideas you give in my presentation so mark anything you wouldn't be
happy to see in a slide at a conference some day.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Oleg Bartunov]

Tommy,

I tried your example and didn't find any problem.
My postgresql version is 8.3.3 and I didn't use stopwords, since I don't
have them.

arxiv=# select version();
version

 PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.3
(1 row)

arxiv=# select ts_lexize('no_ispell', 'overbuljongterningpakkmesterassistent');
  ts_lexize
--
 {over,buljong,terning,pakk,mester,assistent}
(1 row)

Time: 922.364 ms


Oleg

On Thu, 29 Jan 2009, Tommy Gildseth wrote:

While trying to create a new dictionary for use with PostgreSQL text search, 
I get a segfault. My Postgres version is 8.3.5


The dictionary I use is the Norwegian Bokm?l  Nynorsk (Norway) pack for OOo 
2.x downloaded from 
http://wiki.services.openoffice.org/wiki/Dictionaries#Norwegian_.28Norway.29


I've also uploaded the dictionary and affix-file used to this location:
http://folk.uio.no/tommygi/postgres/nb_no.dict
http://folk.uio.no/tommygi/postgres/nb_no.affix

These are unpacked from the zip-file I got from the above location, and 
converted to UTF-8 with the following commands:

 - iconv -f latin1 -t utf-8 nb_NO.dic  nb_no.dict
 - iconv -f latin1 -t utf-8 nb_NO.aff  nb_no.affix

The command I use is this:
CREATE TEXT SEARCH DICTIONARY no_ispell (
   TEMPLATE = ispell,
   DictFile = nb_no,
   AffFile =  nb_no,
   StopWords = norwegian
);


I've uploaded a strace of the process at 
http://folk.uio.no/tommygi/postgres/strace.txt
This is captured with strace -ppid. Since this is about the extent of my 
knowledge of strace, please let me know if there's any other options I should 
specify.


Relevant parts of the postgres log file is included below.

[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG:  server 
process (PID 12002) was terminated by signal 11: Segmentation fault
[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG: terminating 
any other active server processes
[2009-01-29 13:55:11.725 CET] [pgtest02] [:] [] [26466] [0] LOG:  all server 
processes terminated; reinitializing
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: database 
system was interrupted; last known up at 2009-01-29 13:42:05 CET
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: database 
system was not properly shut down; automatic recovery in progress
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG:  record with 
zero length at 0/4A2F9A0
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG:  redo is not 
required
[2009-01-29 13:55:11.986 CET] [pgtest02] [fotoportal:postgres] 
[192.168.6.137(49650)] [12019] [0] FATAL:  the database system is in recovery 
mode
[2009-01-29 13:55:11.988 CET] [pgtest02] [fotoportal:postgres] 
[192.168.6.137(49655)] [12020] [0] FATAL:  the database system is in recovery 
mode
[2009-01-29 13:55:12.010 CET] [pgtest02] [:] [] [12023] [0] LOG: autovacuum 
launcher started
[2009-01-29 13:55:12.011 CET] [pgtest02] [:] [] [26466] [0] LOG: database 
system is ready to accept connections






Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Teodor Sigaev]

Could you provide a backtrace? Do you use unchanged norwegian.stop file?
I'm not able to reproduce the bug - postgres just works.

Tommy Gildseth wrote:
While trying to create a new dictionary for use with PostgreSQL text 
search, I get a segfault. My Postgres version is 8.3.5



--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tommy Gildseth]

It works on one of my servers:
SELECT version();
 version
-
 PostgreSQL 8.3.4 on i686-pc-linux-gnu, compiled by GCC cc (GCC) 3.2.3 
20030502 (Red Hat Linux 3.2.3-59)


The one it fails on is running:
SELECT version();
version

 PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC cc (GCC) 
3.2.3 20030502 (Red Hat Linux 3.2.3-59)


Oleg Bartunov wrote:

Tommy,

I tried your example and didn't find any problem.
My postgresql version is 8.3.3 and I didn't use stopwords, since I don't
have them.

arxiv=# select version();
version

 PostgreSQL 8.3.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.2.3
(1 row)

arxiv=# select ts_lexize('no_ispell', 
'overbuljongterningpakkmesterassistent');

  ts_lexize
--
 {over,buljong,terning,pakk,mester,assistent}
(1 row)

Time: 922.364 ms


Oleg

On Thu, 29 Jan 2009, Tommy Gildseth wrote:

While trying to create a new dictionary for use with PostgreSQL text 
search, I get a segfault. My Postgres version is 8.3.5


The dictionary I use is the Norwegian Bokm?l  Nynorsk (Norway) pack 
for OOo 2.x downloaded from 
http://wiki.services.openoffice.org/wiki/Dictionaries#Norwegian_.28Norway.29 



I've also uploaded the dictionary and affix-file used to this location:
http://folk.uio.no/tommygi/postgres/nb_no.dict
http://folk.uio.no/tommygi/postgres/nb_no.affix

These are unpacked from the zip-file I got from the above location, 
and converted to UTF-8 with the following commands:

 - iconv -f latin1 -t utf-8 nb_NO.dic  nb_no.dict
 - iconv -f latin1 -t utf-8 nb_NO.aff  nb_no.affix

The command I use is this:
CREATE TEXT SEARCH DICTIONARY no_ispell (
   TEMPLATE = ispell,
   DictFile = nb_no,
   AffFile =  nb_no,
   StopWords = norwegian
);


I've uploaded a strace of the process at 
http://folk.uio.no/tommygi/postgres/strace.txt
This is captured with strace -ppid. Since this is about the extent 
of my knowledge of strace, please let me know if there's any other 
options I should specify.


Relevant parts of the postgres log file is included below.

[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG:  
server process (PID 12002) was terminated by signal 11: Segmentation 
fault
[2009-01-29 13:55:11.721 CET] [pgtest02] [:] [] [26466] [0] LOG: 
terminating any other active server processes
[2009-01-29 13:55:11.725 CET] [pgtest02] [:] [] [26466] [0] LOG:  all 
server processes terminated; reinitializing
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: 
database system was interrupted; last known up at 2009-01-29 13:42:05 CET
[2009-01-29 13:55:11.931 CET] [pgtest02] [:] [] [12018] [0] LOG: 
database system was not properly shut down; automatic recovery in 
progress
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG:  
record with zero length at 0/4A2F9A0
[2009-01-29 13:55:11.934 CET] [pgtest02] [:] [] [12018] [0] LOG:  redo 
is not required
[2009-01-29 13:55:11.986 CET] [pgtest02] [fotoportal:postgres] 
[192.168.6.137(49650)] [12019] [0] FATAL:  the database system is in 
recovery mode
[2009-01-29 13:55:11.988 CET] [pgtest02] [fotoportal:postgres] 
[192.168.6.137(49655)] [12020] [0] FATAL:  the database system is in 
recovery mode
[2009-01-29 13:55:12.010 CET] [pgtest02] [:] [] [12023] [0] LOG: 
autovacuum launcher started
[2009-01-29 13:55:12.011 CET] [pgtest02] [:] [] [26466] [0] LOG: 
database system is ready to accept connections






Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83



--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Richard Huxton]

Gregory Stark wrote:
 I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
 FOSDEM 2009 this year.

Hmm - three niggles things leap to mind.

1. Case-folding on column-names.
Quoting is a PITA sometimes when you're transferring from a different
DBMS. Be nice to have a true_case_insensitive=on flag.

2. Non-deferred unique checks
Merging two sets of data and renumbering pkeys is more fiddly than it
needs to be. Doesn't happen often, but it's one of the few things we
don't do properly.

3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say -00-00.


You could throw in non-updateable views, exact-match tsearch queries,
per-statement triggers not having the old/new rowsets but that's more
things PG doesn't do rather than things it does.

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tommy Gildseth]

Yes, originaly I used a customized norwegian.stop-file, but I changed 
that to the one that comes with PostgreSQL, and got the same error.


How do I make a backtrace?

Teodor Sigaev wrote:

Could you provide a backtrace? Do you use unchanged norwegian.stop file?
I'm not able to reproduce the bug - postgres just works.

Tommy Gildseth wrote:
While trying to create a new dictionary for use with PostgreSQL text 
search, I get a segfault. My Postgres version is 8.3.5






--
Tommy Gildseth

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tommy Gildseth]

I tried without specifying a StopWords-list as well, but same thing happens.


Teodor Sigaev wrote:

Could you provide a backtrace? Do you use unchanged norwegian.stop file?
I'm not able to reproduce the bug - postgres just works.

Tommy Gildseth wrote:
While trying to create a new dictionary for use with PostgreSQL text 
search, I get a segfault. My Postgres version is 8.3.5






--
Tommy Gildseth

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Text search name and name synonims dictionary

[Igor Katson]

I have a column, containing the name of the user and there is a need to 
organize an indexed search on this column. As far as I understand, I 
need to use the full-text search capabilities of postgres.


I would like to attach a dictionary, containing many possible names, and 
the short names like:


William Will Bill Billy
James Jim Jimmy

etc., which will give me a possibility to perform indexed search on any 
of these names and it will give me all the matches on variations of the 
name, regardless of which of them was chosen as a search argument.


1. Is it possible to do that with postgres' full-text search capabilities?
2. Does anyone know, if there is a Russian dictionary of these names?
3. What is the name of such kind of dictionary, to know, what to google for?

Thanks in advance, and regards
Igor Katson.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Teodor Sigaev]

How do I make a backtrace?


- if you have coredump, just execute gdb /PATH1/postgres gdb /PATH2/core and 
type bt. Linux doesn't make core by default, so you allow to do it by ulimit -c 
unlimited for postgresql user
- connect to db, and attach gdb to backend process: gdb /PATH1/postgres 
BACKEND_PID and type run in gdb, next, execute CREATE DICTIONARY and type bt in gdb




Teodor Sigaev wrote:

Could you provide a backtrace? Do you use unchanged norwegian.stop file?
I'm not able to reproduce the bug - postgres just works.

Tommy Gildseth wrote:
While trying to create a new dictionary for use with PostgreSQL text 
search, I get a segfault. My Postgres version is 8.3.5








--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Grzegorz Jaśkiewicz]

to get coredump:
sudo su - postgres
ulimit -c unlimited
pg_ctl restart


Also, Teodor - mind the fact, that his machine is 64, and you've
tested it on 32bits.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search name and name synonims dictionary

[Oleg Bartunov]

On Thu, 29 Jan 2009, Igor Katson wrote:

I have a column, containing the name of the user and there is a need to 
organize an indexed search on this column. As far as I understand, I need to 
use the full-text search capabilities of postgres.


I would like to attach a dictionary, containing many possible names, and the 
short names like:


William Will Bill Billy
James Jim Jimmy

etc., which will give me a possibility to perform indexed search on any of 
these names and it will give me all the matches on variations of the name, 
regardless of which of them was chosen as a search argument.


1. Is it possible to do that with postgres' full-text search capabilities?


yes, look on contrib/dict_xsyn


2. Does anyone know, if there is a Russian dictionary of these names?


there are printed dictionaries, not sure about electronic versions.



3. What is the name of such kind of dictionary, to know, what to google for?


http://yandex.ru/yandsearch?text=словарь+личных+имен



Thanks in advance, and regards
Igor Katson.




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tommy Gildseth]

Teodor Sigaev wrote:

How do I make a backtrace?


- if you have coredump, just execute gdb /PATH1/postgres gdb /PATH2/core 
and type bt. Linux doesn't make core by default, so you allow to do it 
by ulimit -c unlimited for postgresql user
- connect to db, and attach gdb to backend process: gdb /PATH1/postgres 
BACKEND_PID and type run in gdb, next, execute CREATE DICTIONARY and 
type bt in gdb


I am completely unfamiliar with gdb, but hopefully, this is what you are 
after?



(gdb) continue
Continuing.

Program received signal SIGSEGV, Segmentation fault.
[Switching to Thread 182898870848 (LWP 606)]
0x005a6f65 in makeCompoundFlags ()
(gdb) bt
#0  0x005a6f65 in makeCompoundFlags ()
#1  0x005a712a in mkSPNode ()
#2  0x005a709b in mkSPNode ()
#3  0x005a709b in mkSPNode ()
#4  0x005a709b in mkSPNode ()
#5  0x005a71a2 in mkSPNode ()
#6  0x005a734d in NISortDictionary ()
#7  0x005a4dae in dispell_init ()
#8  0x00638823 in OidFunctionCall1 ()
#9  0x004f4800 in verify_dictoptions ()
#10 0x004f48da in DefineTSDictionary ()
#11 0x0059c4fe in PortalRunUtility ()
#12 0x0059c5dc in PortalRunMulti ()
#13 0x0059bf28 in PortalRun ()
#14 0x005977d2 in exec_simple_query ()
#15 0x0059a874 in PostgresMain ()
#16 0x00572821 in BackendRun ()
#17 0x00572112 in BackendStartup ()
#18 0x0057034c in ServerLoop ()
#19 0x0056f938 in PostmasterMain ()
#20 0x00529f5b in main ()


--
Tommy Gildseth

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Grzegorz Jaśkiewicz]

if it's
static uint32
makeCompoundFlags(IspellDict *Conf, int affix)
{
uint32  flag = 0;
char   *str = Conf-AffixData[affix];

while (str  *str)
{
flag |= Conf-flagval[(unsigned int) *str];
str++;
}

return (flag  FF_DICTFLAGMASK);
}


Than I have quite few notes about that function:
- affix is not checked on entry, and should be unsigned,
- for sake of safety uint32_t should be used instead of unsigned int,
in the cast
- there should be some safety limit for lenght of str,

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Encoding problem using pg_dumpall

[Tom Lane]

Moshe Ben-Shoham mos...@nexperience.com writes:
 C:\Program Files\PostgreSQL\8.3\binpg_dumpall -U admint 
 c:\temp\dbdump.sql
 pg_dump: SQL command failed
 pg_dump: Error message from server: ERROR:  character 0xd595 of encoding
 UTF8 has no equivalent in WIN1252

Apparently you have WIN1252 set as the default client encoding, probably
via an environment variable or locale setting.  Either get rid of it,
or override it by including -E UTF8 in the pg_dump command.

(Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
which seems like an oversight.  So you need to fix your locale,
or else use pg_dump directly.)

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Changing owner of pg_toast tables

[Mark Styles]

Hi,

I'm trying to drop a role that is no longer being used. However the role
has 4 dependencies which are all pg_toast tables. How can I change the
owner of those pg_toast tables so I can drop the role?

-- 
Mark 
http://www.lambic.co.uk



signature.asc
Description: Digital signature

Re: [GENERAL] Text search name and name synonims dictionary

[Igor Katson]

Oleg Bartunov wrote:

On Thu, 29 Jan 2009, Igor Katson wrote:

I have a column, containing the name of the user and there is a need 
to organize an indexed search on this column. As far as I understand, 
I need to use the full-text search capabilities of postgres.


I would like to attach a dictionary, containing many possible names, 
and the short names like:


William Will Bill Billy
James Jim Jimmy

etc., which will give me a possibility to perform indexed search on 
any of these names and it will give me all the matches on variations 
of the name, regardless of which of them was chosen as a search 
argument.


1. Is it possible to do that with postgres' full-text search 
capabilities?


yes, look on contrib/dict_xsyn


2. Does anyone know, if there is a Russian dictionary of these names?


there are printed dictionaries, not sure about electronic versions.


3. What is the name of such kind of dictionary, to know, what to 
google for?


http://yandex.ru/yandsearch?text=словарь+личных+имен



Thanks in advance, and regards
Igor Katson.




Regards,
Oleg
_


Thanks for the help, Oleg!

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Teodor Sigaev]

I reproduced the bug with a help of Grzegorz's point for 64-bit box. So, patch 
is attached and I'm going to commit it


--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/
*** src/backend/tsearch/spell.c.orig2009-01-29 18:18:03.0 +0300
--- src/backend/tsearch/spell.c 2009-01-29 18:20:09.0 +0300
***
*** 521,527 
(errcode(ERRCODE_CONFIG_FILE_ERROR),
 errmsg(multibyte flag character is not 
allowed)));
  
!   Conf-flagval[(unsigned int) *s] = (unsigned char) val;
Conf-usecompound = true;
  }
  
--- 521,527 
(errcode(ERRCODE_CONFIG_FILE_ERROR),
 errmsg(multibyte flag character is not 
allowed)));
  
!   Conf-flagval[*(unsigned char*) s] = (unsigned char) val;
Conf-usecompound = true;
  }
  
***
*** 654,660 
ptr = repl + (ptr - prepl) + 1;
while (*ptr)
{
!   aflg |= Conf-flagval[(unsigned int) 
*ptr];
ptr++;
}
}
--- 654,660 
ptr = repl + (ptr - prepl) + 1;
while (*ptr)
{
!   aflg |= Conf-flagval[*(unsigned char*) 
ptr];
ptr++;
}
}
***
*** 735,741 
  
if (*s  pg_mblen(s) == 1)
{
!   Conf-flagval[(unsigned int) *s] = 
FF_COMPOUNDFLAG;
Conf-usecompound = true;
}
oldformat = true;
--- 735,741 
  
if (*s  pg_mblen(s) == 1)
{
!   Conf-flagval[*(unsigned char*) s] = 
FF_COMPOUNDFLAG;
Conf-usecompound = true;
}
oldformat = true;
***
*** 791,797 

(errcode(ERRCODE_CONFIG_FILE_ERROR),
 errmsg(multibyte flag 
character is not allowed)));
  
!   flag = (unsigned char) *s;
goto nextline;
}
if (STRNCMP(recoded, COMPOUNDFLAG) == 0 || STRNCMP(recoded, 
COMPOUNDMIN) == 0 ||
--- 791,797 

(errcode(ERRCODE_CONFIG_FILE_ERROR),
 errmsg(multibyte flag 
character is not allowed)));
  
!   flag = *(unsigned char*) s;
goto nextline;
}
if (STRNCMP(recoded, COMPOUNDFLAG) == 0 || STRNCMP(recoded, 
COMPOUNDMIN) == 0 ||
***
*** 851,857 
  
while (str  *str)
{
!   flag |= Conf-flagval[(unsigned int) *str];
str++;
}
  
--- 851,857 
  
while (str  *str)
{
!   flag |= Conf-flagval[*(unsigned char*) str];
str++;
}
  

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Teodor Sigaev]

Than I have quite few notes about that function:
- affix is not checked on entry, and should be unsigned,


Could be Assert( affix=0  affix  Conf-nAffixData )


- for sake of safety uint32_t should be used instead of unsigned int,
in the cast

see patch

- there should be some safety limit for lenght of str,

It's a C-string

--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 3:26 PM, Teodor Sigaev teo...@sigaev.ru wrote:
 I reproduced the bug with a help of Grzegorz's point for 64-bit box. So,
 patch is attached and I'm going to commit it

:)

To be honest, looking through that file, I am quite worried about few
points. I don't know too much about insights of ispell, but I see few
suspicious things in mkSPNode too.
I generally don't want to get involve in reviewing code for stuff I
don't know, But if Teodor (and Oleg) don't mind, I can raise my
points, and see if anything useful comes out of it.

Also, about that patch - it doesn't seem to apply cleanly to 8.4,
perhaps that file has changed too much (I based my 'review' above on
8.4's code)


-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 3:32 PM, Teodor Sigaev teo...@sigaev.ru wrote:


 Than I have quite few notes about that function:
 - affix is not checked on entry, and should be unsigned,

 Could be Assert( affix=0  affix  Conf-nAffixData )

wouldn't that crash pg backend too ?

The structure that this file parses, does it come straight from ispell
file, or is it being already parsed (and checked for errors) ?


-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tom Lane]

Teodor Sigaev teo...@sigaev.ru writes:
 I reproduced the bug with a help of Grzegorz's point for 64-bit box.

Hmm, seems it's not so much a 64 bit error as a signed vs unsigned
char issue?  Does this affect the old contrib/tsearch2 code?

Please try to make the commits in the next eight hours, as we have
release wraps scheduled for tonight.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Richard Broersma]

On Thu, Jan 29, 2009 at 5:16 AM, Gregory Stark st...@enterprisedb.com wrote:

 So, what do people say? Is Postgres perfect in your world or does it do some
 things which rub you the wrong way?

I would like to see the SQL92 feature for allowing sub-queries in
CHECK constraints, instead of marking this feature as intentionally
omitted.
-- 
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changing owner of pg_toast tables

[Tom Lane]

Mark Styles postg...@lambic.co.uk writes:
 I'm trying to drop a role that is no longer being used. However the role
 has 4 dependencies which are all pg_toast tables. How can I change the
 owner of those pg_toast tables so I can drop the role?

I guess the interesting question to me is what happened to the tables
those toast tables are/were attached to?  They should have the same
owners as their parent tables.  What PG version is this exactly?

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Gregory Stark]

Teodor Sigaev teo...@sigaev.ru writes:

 I reproduced the bug with a help of Grzegorz's point for 64-bit box. So, patch
 is attached and I'm going to commit it
...

 ! Conf-flagval[(unsigned int) *s] = (unsigned char) val;
...
 ! Conf-flagval[*(unsigned char*) s] = (unsigned char) val;

Maybe I'm missing something but I don't understand how this fixes the problem.
s is a char* so type punning it to an unsigned char * before dereferencing
it is really the same as casting it to unsigned char directly and casting it
to unsigned int really ought to have done the same thing anyways.

All of the changes are of this type so I can't see how your patch could have
fixed the problem.

And in general casting the pointer before dereferencing it is a whole lot
scarier code which should raise eyebrows a lot faster than just a simple cast
to unsigned char like you had it originally.

What really boggles me is why you don't just use unsigned chars everywhere and
remove all of these casts. or would that just move the casts to strcmp and
company?

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Teodor Sigaev]

Tom Lane wrote:

Teodor Sigaev teo...@sigaev.ru writes:

I reproduced the bug with a help of Grzegorz's point for 64-bit box.


Hmm, seems it's not so much a 64 bit error as a signed vs unsigned
char issue?  


Yes, but I don't understand why it worked in 32-bit box.


Does this affect the old contrib/tsearch2 code?


Will check.


Please try to make the commits in the next eight hours, as we have
release wraps scheduled for tonight.


Minor versions or beta of 8.4? if last, I'd like to commit btree_gin and 
fast_update_gin.  For both patches all pointed issues was resolved and Jeff, 
seems, haven't objections.


--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changing owner of pg_toast tables

[Mark Styles]

On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote:
 Mark Styles postg...@lambic.co.uk writes:
  I'm trying to drop a role that is no longer being used. However the role
  has 4 dependencies which are all pg_toast tables. How can I change the
  owner of those pg_toast tables so I can drop the role?
 
 I guess the interesting question to me is what happened to the tables
 those toast tables are/were attached to? They should have the same
 owners as their parent tables.  

They did have the same owner, I changed the owner to postgres so I could
drop the role, but the corresponding pg_toast tables did not change.

 What PG version is this exactly?

8.1.11

-- 
Mark 
http://www.lambic.co.uk



signature.asc
Description: Digital signature

Re: [GENERAL] Text search segmentation fault

[Tom Lane]

Gregory Stark st...@enterprisedb.com writes:
 Maybe I'm missing something but I don't understand how this fixes the problem.
 s is a char* so type punning it to an unsigned char * before dereferencing
 it is really the same as casting it to unsigned char directly

No, it isn't.  If char is signed then you'll get quite different results
from a high-bit-set byte value, because sign extension will happen
before the value is reinterpreted as unsigned.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Devrim GÜNDÜZ]

On Thu, 2009-01-29 at 19:00 +0300, Teodor Sigaev wrote:
  Please try to make the commits in the next eight hours, as we have
  release wraps scheduled for tonight.
 
 Minor versions or beta of 8.4? 

Minor versions.

-- 
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
   http://www.gunduz.org


signature.asc
Description: This is a digitally signed message part

Re: [GENERAL] Text search segmentation fault

[Teodor Sigaev]

To be honest, looking through that file, I am quite worried about few
points. I don't know too much about insights of ispell, but I see few
suspicious things in mkSPNode too.
I generally don't want to get involve in reviewing code for stuff I
don't know, But if Teodor (and Oleg) don't mind, I can raise my
points, and see if anything useful comes out of it.

If you see bug/mistake/suspicious point, please, don't be quiet



Also, about that patch - it doesn't seem to apply cleanly to 8.4,
perhaps that file has changed too much (I based my 'review' above on
8.4's code)

will tweak
--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Gregory Stark]

Gregory Stark st...@enterprisedb.com writes:

 Teodor Sigaev teo...@sigaev.ru writes:

 I reproduced the bug with a help of Grzegorz's point for 64-bit box. So, 
 patch
 is attached and I'm going to commit it
 ...

 !Conf-flagval[(unsigned int) *s] = (unsigned char) val;
 ...
 !Conf-flagval[*(unsigned char*) s] = (unsigned char) val;

 Maybe I'm missing something but I don't understand how this fixes the problem.

Ah, I understand how this fixes the problem. You were casting to unsigned
*int* not unsigned char so it was sign extending first and then overflowing.
So char255 was coming out as MAX_INT instead of 255.

#include stdio.h

main()
{
  volatile signed char a = -1;
  printf(ud=%ud\n, (unsigned int)a);
}

$ ./a.out
ud=4294967295d


If you just make these all casts to (unsigned char) it should work just as
well as the pointer type punning -- and be a whole lot less scary.

 What really boggles me is why you don't just use unsigned chars everywhere and
 remove all of these casts. or would that just move the casts to strcmp and
 company?

It still seems to me if you put a few unsigned in variable declarations you
could remove piles upon piles of casts and make all of the code more readable.


-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 4:06 PM, Gregory Stark st...@enterprisedb.com wrote:
 Gregory Stark st...@enterprisedb.com writes:

 Ah, I understand how this fixes the problem. You were casting to unsigned
 *int* not unsigned char so it was sign extending first and then overflowing.
:)

 It still seems to me if you put a few unsigned in variable declarations you
 could remove piles upon piles of casts and make all of the code more readable.

which is one of the main problems I see with that code, overall.


-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tom Lane]

Teodor Sigaev teo...@sigaev.ru writes:
 Tom Lane wrote:
 Please try to make the commits in the next eight hours, as we have
 release wraps scheduled for tonight.

 Minor versions or beta of 8.4?

This is just back-branch update releases.  8.4 beta is still a good
ways off :-(

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Terry Fielder]

I have 2, closely related:

1) if I have multiple pids running queries, say all selects, I have no 
idea which pid is running which query


and that ties to:
2) If I try to kill 1 postgres pid (e.g. to abort a bad query), the 
whole backend shuts down and rolls back.

Can we get a way to look at and then kill a specific bad query?

Maybe this is not missing functionality, it can do it and I just don't 
know how.  Anybody want to wisen me up?  :)


Terry

Terry Fielder
te...@greatgulfhomes.com
Associate Director Software Development and Deployment
Great Gulf Homes / Ashton Woods Homes
Fax: (416) 441-9085



Gregory Stark wrote:

I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
but I would be interested to hear if people have any complaints from personal
experience. What would be most interesting is if you can explain an example of
when the problem caused real inconvenience to you, since sometimes it's hard
to see from a theoretical description where the real harm lies.

So, what do people say? Is Postgres perfect in your world or does it do some
things which rub you the wrong way?

Feel free to respond on-list or if you prefer in personal emails. I do intend
to use the ideas you give in my presentation so mark anything you wouldn't be
happy to see in a slide at a conference some day.

  


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Bill Moran]

In response to Terry Fielder te...@ashtonwoodshomes.com:
 
 1) if I have multiple pids running queries, say all selects, I have no 
 idea which pid is running which query

SELECT * FROM pg_stat_activity;

If the current_query column doesn't have the query in it, then you need
to tweak your postgres.conf settings:
http://www.postgresql.org/docs/8.3/static/monitoring-stats.html

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Gregory Stark]

Tom Lane t...@sss.pgh.pa.us writes:

 Gregory Stark st...@enterprisedb.com writes:
 Maybe I'm missing something but I don't understand how this fixes the 
 problem.
 s is a char* so type punning it to an unsigned char * before dereferencing
 it is really the same as casting it to unsigned char directly

 No, it isn't.  If char is signed then you'll get quite different results
 from a high-bit-set byte value, because sign extension will happen
 before the value is reinterpreted as unsigned.

What I wrote is correct. There's no sign extension if you're casting from
signed char to unsigned char since there's no extension.

I really think he should just change all the unsigned int into unsigned
char and not do the type punning with pointer casts. That's just evil.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's Slony Replication support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] chinese parser for text search !

[Daniel Chiaramello]

Hello Oleg and others.

I also found that reference, but failed to find the corresponding 
Chinese dictionary it mentions.

And when I tried to compile nlpbamboo, it fails.

Has one of you tried (and succeeded) to use Tsearch for Chinese?

Thanks for your attention,
Daniel

Oleg Bartunov a écrit :

Hi there,

there is a chinese parser for tsearch2 available from 
http://code.google.com/p/nlpbamboo/wiki/TSearch2 under BSD license.


It'd be nice to have it for chinese text search configuration we are 
currently

completely missing, as well as for japanese language ( any guess ?)

Unfortunately, we unable to verify this parser, so I'm asking about help.
We need usual extensive testing (memory leaking, portability, etc),
regression test and patch for documentation.

Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83




--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Full backup - pg_dumpall sufficient?

[Gerhard Wiesinger]

Hello!

I recently read some Mail on the mailinglist where some parts of 
PostgreSQL were not dumped with pg_dumpall and additionally some pg_dump 
was necessary (it was something like internals, catalog, etc.)


Any ideas what additionally has to be dumped to pg_dumpall for a full 
backup?


Thnx.

Ciao,
Gerhard

--
http://www.wiesinger.com/


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tom Lane]

Gregory Stark st...@enterprisedb.com writes:
 I really think he should just change all the unsigned int into unsigned
 char and not do the type punning with pointer casts. That's just evil.

Oh, I see.  That would work too, but I don't really see that it's a huge
improvement.

What *would* be an improvement IMHO is to declare the pointer as
unsigned char * in the first place ;-).  However, that might result
in having to introduce a lot of casts elsewhere ...

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full backup - pg_dumpall sufficient?

[Raymond O'Donnell]

On 29/01/2009 16:31, Gerhard Wiesinger wrote:

 I recently read some Mail on the mailinglist where some parts of
 PostgreSQL were not dumped with pg_dumpall and additionally some pg_dump
 was necessary (it was something like internals, catalog, etc.)

It's the other way around - pg_dump dumps just the specified database,
but not cluster-wide stuff like login roles; you need to do a pg_dumpall
to get those as well.

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[David Fetter]

On Thu, Jan 29, 2009 at 01:16:17PM +, Gregory Stark wrote:
 
 I'm putting together a talk on PostgreSQL Pet Peeves for
 discussion at FOSDEM 2009 this year.  I have a pretty good idea what
 some them are of course, but I would be interested to hear if people
 have any complaints from personal experience.  What would be most
 interesting is if you can explain an example of when the problem
 caused real inconvenience to you, since sometimes it's hard to see
 from a theoretical description where the real harm lies.
 
 So, what do people say?  Is Postgres perfect in your world or does
 it do some things which rub you the wrong way?
 
 Feel free to respond on-list or if you prefer in personal emails.  I
 do intend to use the ideas you give in my presentation so mark
 anything you wouldn't be happy to see in a slide at a conference
 some day.

* No built-in ways to get the information psql gets.  See what psql
  is doing isn't an option when somebody doesn't have psql on hand.

* No deferrable UNIQUE constraints.

* No man pages for the internals.

* Letter options in psql, pg_dump[all], pg_restore aren't consistent
  and can easily steer you very wrong.  I'm looking at you, -d.

* CTEs not yet integrated into the adjacency lists in pg_catalog, etc.

The following aren't problems with the PostgreSQL core engine itself,
but they're nearby, so they catch ire:

* Neither pgAdmin nor phpPgAdmin includes any facilities for
  extracting ERDs.

* Neither of them let you set up Slony (or any other replication
  system) to start with.

-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full backup - pg_dumpall sufficient?

[Tom Lane]

Gerhard Wiesinger li...@wiesinger.com writes:
 Any ideas what additionally has to be dumped to pg_dumpall for a full 
 backup?

The configuration files (postgresql.conf, pg_hba.conf, pg_ident.conf),
plus any SSL server keys/certs you might be using --- basically, all
the static text files in the toplevel $PGDATA directory.  Those things
are not accessible to a client so pg_dump can't dump them.

Some people put these files in a different directory where they'll be
caught by their regular filesystem backup procedures for the server.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full backup - pg_dumpall sufficient?

[Gerhard Wiesinger]

Hello Ray,

Yes, that's clear. But there was even some stuff which isn't dumped with 
pg_dumpall (as far as I read).


So it was like to run 2 statements like:
1.) Run pg_dumpall
2.) Run pg_dump additionally ...

Ciao,
Gerhard

--
http://www.wiesinger.com/


On Thu, 29 Jan 2009, Raymond O'Donnell wrote:


On 29/01/2009 16:31, Gerhard Wiesinger wrote:


I recently read some Mail on the mailinglist where some parts of
PostgreSQL were not dumped with pg_dumpall and additionally some pg_dump
was necessary (it was something like internals, catalog, etc.)


It's the other way around - pg_dump dumps just the specified database,
but not cluster-wide stuff like login roles; you need to do a pg_dumpall
to get those as well.

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Jason Long]

Richard Huxton wrote:

Gregory Stark wrote:
  

I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
FOSDEM 2009 this year.



Hmm - three niggles things leap to mind.

1. Case-folding on column-names.
Quoting is a PITA sometimes when you're transferring from a different
DBMS. Be nice to have a true_case_insensitive=on flag.
  

I was just wishing for this the other day.

2. Non-deferred unique checks
Merging two sets of data and renumbering pkeys is more fiddly than it
needs to be. Doesn't happen often, but it's one of the few things we
don't do properly.

3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say -00-00.


You could throw in non-updateable views, exact-match tsearch queries,
per-statement triggers not having the old/new rowsets but that's more
things PG doesn't do rather than things it does.

  

Re: [GENERAL] Text search segmentation fault

[Tom Lane]

Teodor Sigaev teo...@sigaev.ru writes:
 Tom Lane wrote:
 Hmm, seems it's not so much a 64 bit error as a signed vs unsigned
 char issue?  

 Yes, but I don't understand why it worked in 32-bit box.

You were casting to unsigned int.  So the offset added to the base
pointer for, say, 255 in the char would be equivalent to -1 on a 32-bit
box, or 0x on 64-bit.  The latter would likely provoke SIGSEGV
due to indexing out of the allocated process workspace, the former just
in scribbling on the byte adjacent to where it should have.  Still
broken, but not a segfault.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ssl to more than one server

[Ivan Sergio Borgonovo]

I succeded to connect to one postgresql server with ssl.
Now it's the time of the second... but postgresql clients (pgsql)
just look at ~/.postgresql/postgresql.(key|crt)
So I can't put in ~/.postgresql/ another [].crt coming from another
server.

What should I do to keep stuff separate?

thanks

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Dave Page]

On Thu, Jan 29, 2009 at 4:57 PM, David Fetter da...@fetter.org wrote:

 * Neither of them let you set up Slony (or any other replication
  system) to start with.

pgAdmin does (well, barring installation and setting up slon.conf):
http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html

-- 
Dave Page
EnterpriseDB UK:   http://www.enterprisedb.com

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Gregory Stark]

David Fetter da...@fetter.org writes:

 * No built-in ways to get the information psql gets.  See what psql
   is doing isn't an option when somebody doesn't have psql on hand.

Uhm, what information are you referring to here?

 * No man pages for the internals.

Is it just that not all of the manual is actually exported into man pages? Or
is there stuff you would like to see in the manual that isn't there?

 * CTEs not yet integrated into the adjacency lists in pg_catalog, etc.

I'm not sure what you're referring to here either.

 Remember to vote!

This may not be so timely any more, though I suppose there's always someone
somewhere holding elections :)

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Teodor Sigaev]

char issue?  Does this affect the old contrib/tsearch2 code?


Checked - No, that was improvement for 8.3 :).

--
Teodor Sigaev   E-mail: teo...@sigaev.ru
   WWW: http://www.sigaev.ru/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[rhubbell]

I'm a new user to PostgreSQL so mine's fresh from doing an install recently.


In /etc/postgresql/8.3/main/pg_hba.conf

# METHOD can be trust, reject, md5, crypt, password, gss, sspi,
# krb5, ident, pam or ldap.  Note that password sends passwords
# in clear text; md5 is preferred since it sends encrypted passwords.


So I chose md5 but it will not work, seems like a basic thing. So I am
forced to use trust.  These are the kinds of things that wear down
busy people trying use the software. Maybe this is a documentation
enhancement or bug.

You say:
I have a pretty good idea what some them are

What's your list so far?


On Thu, 29 Jan 2009 13:16:17 +
Gregory Stark st...@enterprisedb.com wrote:

 
 I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
 FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
 but I would be interested to hear if people have any complaints from personal
 experience. What would be most interesting is if you can explain an example of
 when the problem caused real inconvenience to you, since sometimes it's hard
 to see from a theoretical description where the real harm lies.
 
 So, what do people say? Is Postgres perfect in your world or does it do some
 things which rub you the wrong way?
 
 Feel free to respond on-list or if you prefer in personal emails. I do intend
 to use the ideas you give in my presentation so mark anything you wouldn't be
 happy to see in a slide at a conference some day.
 
 -- 
   Gregory Stark
   EnterpriseDB  http://www.enterprisedb.com
   Ask me about EnterpriseDB's Slony Replication support!
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Gregory Stark]

Jason Long mailing.l...@supernovasoftware.com writes:

 Richard Huxton wrote:

 1. Case-folding on column-names.
 Quoting is a PITA sometimes when you're transferring from a different
 DBMS. Be nice to have a true_case_insensitive=on flag.
   
 I was just wishing for this the other day.

I'm kind of wondering what behaviour you two are looking for and what
different DBMS you're referring to.

I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[David Fetter]

On Thu, Jan 29, 2009 at 05:18:17PM +, Dave Page wrote:
 On Thu, Jan 29, 2009 at 4:57 PM, David Fetter da...@fetter.org wrote:
 
  * Neither of them let you set up Slony (or any other replication
  system) to start with.
^
 pgAdmin does (well, barring installation and setting up slon.conf):
 http://pgsnake.blogspot.com/2007/09/setting-up-slony-i-with-pgadmin.html

It's exactly that setup that's the peeve.  I don't think that this is
an easy problem to fix, as the general one needs filesystem access to
more than one machine, even machines without PostgreSQL installed, for
the case where the slons are on separate boxes.

It's just a peeve. :)

On the other hand, lack of a good set of startup tools has pretty much
tanked Slony adoption :(

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Alvaro Herrera]

Gregory Stark wrote:
 
 I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
 FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
 but I would be interested to hear if people have any complaints from personal
 experience. What would be most interesting is if you can explain an example of
 when the problem caused real inconvenience to you, since sometimes it's hard
 to see from a theoretical description where the real harm lies.
 
 So, what do people say? Is Postgres perfect in your world or does it do some
 things which rub you the wrong way?

No foreign keys in inheritance trees

No true clustered indexes

Lazy vacuum is not able to release free pages in the middle of a table

No concurrent reindex

Cross-column stats problems

No integrated job agent ;-)

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J
La tristeza es un muro entre dos jardines (Khalil Gibran)

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Recovery mode

[Sebastian Tennant]

Hi there,

Just noticed this in my webapp logs:

 ERROR: FATAL:  the database system is in recovery mode

Only one instance, so I'm not too concerned, but why, how often, how
long for, etc.

Am I negelecting to do some important database maintenace?

Could it be related to the backup cron performs hourly:

 pg_dump --format=custom --file=file db

Seb
-- 
Emacs' AlsaPlayer - Music Without Jolts
Lightweight, full-featured and mindful of your idyllic happiness.
http://home.gna.org/eap


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changing owner of pg_toast tables

[Tom Lane]

Mark Styles postg...@lambic.co.uk writes:
 On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote:
 I guess the interesting question to me is what happened to the tables
 those toast tables are/were attached to? They should have the same
 owners as their parent tables.  

 They did have the same owner, I changed the owner to postgres so I could
 drop the role, but the corresponding pg_toast tables did not change.

Well, that's just weird.  Can you reproduce such a behavior?  In my
tests 8.1 definitely does change the toast table's owner along with the
parent.  One can imagine that step failing, but if so the whole
ALTER OWNER transaction should roll back.

As for getting out of your immediate problem, I think what you'd need to
do is manually adjust the pg_class.relowner fields for those toast
tables, and then get rid of the pg_shdepend entries that claim they
depend on the old role.  (You don't need to put back new entries
claiming they depend on postgres.)  But I'd sure like to find out what
happened.  We've heard a few reports before of toast tables not getting
dropped when their parents were, and I wonder if this is related.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Encoding problem using pg_dumpall

[Magnus Hagander]

Tom Lane wrote:
 Moshe Ben-Shoham mos...@nexperience.com writes:
 C:\Program Files\PostgreSQL\8.3\binpg_dumpall -U admint 
 c:\temp\dbdump.sql
 pg_dump: SQL command failed
 pg_dump: Error message from server: ERROR:  character 0xd595 of encoding
 UTF8 has no equivalent in WIN1252
 
 Apparently you have WIN1252 set as the default client encoding, probably
 via an environment variable or locale setting.  Either get rid of it,
 or override it by including -E UTF8 in the pg_dump command.
 
 (Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
 which seems like an oversight.  So you need to fix your locale,
 or else use pg_dump directly.)

IIRC, you can't set the windows console to be UTF8. Thus, your option is
to switch to using pg_dump and use -E UTF8 on that one.

//Magnus

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Jason Long]

Gregory Stark wrote:

Jason Long mailing.l...@supernovasoftware.com writes:

  

Richard Huxton wrote:



1. Case-folding on column-names.
Quoting is a PITA sometimes when you're transferring from a different
DBMS. Be nice to have a true_case_insensitive=on flag.
  
  

I was just wishing for this the other day.



I'm kind of wondering what behaviour you two are looking for and what
different DBMS you're referring to.
  
Ah, I misread.  I was wishing for the a way to make table and column 
names case sensitive without having to add quotes everywhere.

I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.

  

Re: [GENERAL] Pet Peeves?

[Steve Crawford]

3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say -00-00.



Oh dear $DEITY, no. Part of the ethos of PostgreSQL is that it requires 
you to enter valid data. I don't see how auto-replacing one invalid date 
with a standardized different invalid date is a benefit.


In a data-cleaning environment, I could see some limit benefit of some 
sort of explicit override on the copy command, say:

\copy foo (a, b, c invalid=null, d invalid='') from foo.txt...

At least you could get the good data into a table to analyze it. But 
overall, the data-cleaning role seems to me to belong more in the ETL arena.


-Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] New 8.4 hot standby feature

[Gabi Julien]

On Thursday 29 January 2009 02:43:18 you wrote:
 On Tue, 2009-01-27 at 12:53 -0500, Gabi Julien wrote:
  I have merged the last hot standby patch (v9g) to 8.4 devel and I am
  pleased with the experience. This is promising stuff.

 Thanks,

  Perhaps it is a bit too soon to
  ask questions here but here it is:

 Thanks very much for the bug report.

  1. Speed of recovery
 
  With a archive_timeout of 60 seconds, it can take about 4 minutes before
  I see the reflected changes in the replica. This is normal since, in
  addition to the WAL log shipping, it takes more time to do the recovery
  itself. Still, is there any way besides the archive_timeout config option
  to speed up the recovery of WAL logs on the hot standby?

 There was a reported bug whose apparent symptoms were delay of WAL
 files. The bug was not in fact anything to do with that at all, it was
 just delayed *visibility*. So I doubt very much that you have a
 performance problem.

 The bug fix patch is attached, verified to solve the problem.

Thanks. Please discard all my previous comments. This was the true source of 
the issue that I was experiencing.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Encoding problem using pg_dumpall

[Tom Lane]

Magnus Hagander mag...@hagander.net writes:
 Tom Lane wrote:
 (Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
 which seems like an oversight.  So you need to fix your locale,
 or else use pg_dump directly.)

 IIRC, you can't set the windows console to be UTF8.

Ugh.  That seems to raise the priority of having a -E switch quite
a lot.  I'm surprised no one has complained of this before.

I think it should be possible to work around it by setting
PGCLIENTENCODING in the environment, but I dunno how to do that
on Windows.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Richard Huxton]

Gregory Stark wrote:
 Jason Long mailing.l...@supernovasoftware.com writes:
 
 Richard Huxton wrote:

 1. Case-folding on column-names.
 Quoting is a PITA sometimes when you're transferring from a different
 DBMS. Be nice to have a true_case_insensitive=on flag.
   
 I was just wishing for this the other day.
 
 I'm kind of wondering what behaviour you two are looking for and what
 different DBMS you're referring to.
 
 I'm assuming it's not the ANSI fold-to-uppercase behaviour you're looking for.

It's anywhere you can get tables created quoted/unquoted and not that in
the app. Or even on the command-line. It's a PITA to go round
remembering to quote every column because the table was created
preserving case.

Not the end of the world, but you did ask for peeves.

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Guy Rouillier]

Gregory Stark wrote:

I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
but I would be interested to hear if people have any complaints from personal
experience. What would be most interesting is if you can explain an example of
when the problem caused real inconvenience to you, since sometimes it's hard
to see from a theoretical description where the real harm lies.

So, what do people say? Is Postgres perfect in your world or does it do some
things which rub you the wrong way?

Feel free to respond on-list or if you prefer in personal emails. I do intend
to use the ideas you give in my presentation so mark anything you wouldn't be
happy to see in a slide at a conference some day.



Back in March 2005, I started an email thread titled Debugging 
deadlocks.  Most of the experienced PGers participated in that thread. 
 The basic issue at that time was that inserting a row into a table 
with a foreign key placed an exclusive row-level lock (SELECT FOR 
UPDATE) on the reference table (the table to which the foreign key 
refers).  If you happen to do inserts on two different tables, each with 
a foreign key to the same reference table, deadlocks are pretty easy to 
create.  This is especially true if the reference table has low 
cardinality, which is often the case.


I don't know if this situation has been improved since that time.

--
Guy Rouillier

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[David Fetter]

On Thu, Jan 29, 2009 at 05:18:19PM +, Gregory Stark wrote:
 David Fetter da...@fetter.org writes:
 
  * No built-in ways to get the information psql gets.  See what
  psql is doing isn't an option when somebody doesn't have psql on
  hand.
 
 Uhm, what information are you referring to here?

All the stuff that generates \d output is available only to psql.
When somebody wants to make another client, or even expose some of
that functionality, they pretty much have to roll it from scratch.

  * No man pages for the internals.
 
 Is it just that not all of the manual is actually exported into man
 pages?  Or is there stuff you would like to see in the manual that
 isn't there?

The configuration files (postgresql.conf and pg_hba.conf, at least)
and the stuff in libpq and SPI would be a great start.

  * CTEs not yet integrated into the adjacency lists in pg_catalog,
  etc.
 
 I'm not sure what you're referring to here either.

The DAG structures in pg_depend leap to mind.  There's no view that
shows the actual dependencies, except in the sense of, Here's the
edges.  Figure it out for yourself.

  Remember to vote!
 
 This may not be so timely any more, though I suppose there's always
 someone somewhere holding elections :)

It's always time to vote on *something* :)

Oh, and one more associated project peeve:

* PostGIS doesn't ship with core PostgreSQL.

I've come up with a strategy for fixing it.  Port JTS
http://sourceforge.net/projects/jts-topo-suite/ from Java to C and
BSDL the result, is a pretty gigantic task, and it's just the start,
but I'm pretty sure it's the right strategy for fixing the peeve.

Cheers,
David.
-- 
David Fetter da...@fetter.org http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Richard Huxton]

David Fetter wrote:
 * Letter options in psql, pg_dump[all], pg_restore aren't consistent
   and can easily steer you very wrong.  I'm looking at you, -d.

Ah, good one - I keep doing that too. For the record -d is usually
database-name, but for pg_dump it's dump with inserts. Which is a
zillion time slower than COPY for restoring.

-- 
  Richard Huxton
  Archonet Ltd

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Encoding problem using pg_dumpall

[Magnus Hagander]

Tom Lane wrote:
 Magnus Hagander mag...@hagander.net writes:
 Tom Lane wrote:
 (Hmm, actually it looks like pg_dumpall hasn't got a -E switch,
 which seems like an oversight.  So you need to fix your locale,
 or else use pg_dump directly.)
 
 IIRC, you can't set the windows console to be UTF8.
 
 Ugh.  That seems to raise the priority of having a -E switch quite
 a lot.  I'm surprised no one has complained of this before.

Most people use pg_dump. I know I recommend everbody to use pg_dump to
dump the database, because you can use -Fc. Then just use pg_dumpall to
dump the globals, and they normally don't have any non-ascii in them.


 I think it should be possible to work around it by setting
 PGCLIENTENCODING in the environment, but I dunno how to do that
 on Windows.

That's a simple
set PGCLIENTENCODING=UTF8

//Magnus


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full backup - pg_dumpall sufficient?

[Tom Lane]

Gerhard Wiesinger li...@wiesinger.com writes:
 Hello Ray,
 Yes, that's clear. But there was even some stuff which isn't dumped with 
 pg_dumpall (as far as I read).

Perhaps you were reading some extremely obsolete information?
It used to be that pg_dumpall couldn't dump large objects,
but that was a long time back.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Joshua D. Drake]

On Thu, 2009-01-29 at 17:43 +, Richard Huxton wrote:
 David Fetter wrote:
  * Letter options in psql, pg_dump[all], pg_restore aren't consistent
and can easily steer you very wrong.  I'm looking at you, -d.
 
 Ah, good one - I keep doing that too. For the record -d is usually
 database-name, but for pg_dump it's dump with inserts. Which is a
 zillion time slower than COPY for restoring.

If we are listing pet peeves :)

Up to 8.4, postgresql didn't accurately represent timestamps because
they are stored as float by default

The fact that there is:

pg_dump
pg_dumpall
pg_restore

At all...

It should be pg_backup and that is it, with a nice -R flag for restore.

The idea that it is proper to pipe a backup through psql to restore.

Our date handling as a whole (extract,date_part) is wonky. There have
been more than one blog post on this.

Our lack of partitioning :)

Joshua D. Drake


-- 
PostgreSQL - XMPP: jdr...@jabber.postgresql.org
   Consulting, Development, Support, Training
   503-667-4564 - http://www.commandprompt.com/
   The PostgreSQL Company, serving since 1997


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] ssl to more than one server

[Tom Lane]

Ivan Sergio Borgonovo m...@webthatworks.it writes:
 I succeded to connect to one postgresql server with ssl.
 Now it's the time of the second... but postgresql clients (pgsql)
 just look at ~/.postgresql/postgresql.(key|crt)
 So I can't put in ~/.postgresql/ another [].crt coming from another
 server.

Not an ssl expert, but I think you just concatenate all the keys you
need into the one text file.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Gregory Stark]

Steve Crawford scrawf...@pinpointresearch.com writes:

 3. Date handling
 Sometimes I've got data with invalid dates and it would be great if it
 could replace all the bad ones with, say -00-00.
 

 Oh dear $DEITY, no. 

I think it would be best if we limited ourselves right now to discussing the
problems themselves and not debating the pros and cons of possible solutions.

I want to encourage people to post their peeves even if they know perfectly
well the reasons why things are the way they are.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's 24x7 Postgres support!

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Full backup - pg_dumpall sufficient?

[Jeff Frost]

Tom Lane wrote:
 Gerhard Wiesinger li...@wiesinger.com writes:
   
 Hello Ray,
 Yes, that's clear. But there was even some stuff which isn't dumped with 
 pg_dumpall (as far as I read).
 

 Perhaps you were reading some extremely obsolete information?
 It used to be that pg_dumpall couldn't dump large objects,
 but that was a long time back.

   
Tom one thing I noticed recently is that pg_dumpall --globals doesn't
seem to pick up when you alter the GUCs at the database level and
neither does pg_dump.  How should you dump to grab that per-database
stuff? 

For example on 8.3.5:

discord:~ $ psql jefftest
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

jefftest=# show default_statistics_target ;
 default_statistics_target
---
 10
(1 row)

Time: 0.139 ms

jefftest=# ALTER DATABASE jefftest SET default_statistics_target = 100;
ALTER DATABASE
Time: 46.758 ms

jefftest=# \q
discord:~ $ psql jefftest
Welcome to psql 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

jefftest=# show default_statistics_target ;
 default_statistics_target
---
 100
(1 row)

Time: 0.318 ms
jefftest=# \q
discord:~ $ pg_dumpall --globals|grep default_statistics_target
discord:~ $ pg_dump jefftest | grep default_statistics_target
discord:~ $

-- 
Jeff Frost, Owner   j...@frostconsultingllc.com
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032

Re: [GENERAL] Pet Peeves?

[Bill Moran]

In response to rhubbell rhubb...@ihubbell.com:
 
 I'm a new user to PostgreSQL so mine's fresh from doing an install recently.
 
 In /etc/postgresql/8.3/main/pg_hba.conf
 
 # METHOD can be trust, reject, md5, crypt, password, gss, sspi,
 # krb5, ident, pam or ldap.  Note that password sends passwords
 # in clear text; md5 is preferred since it sends encrypted passwords.
 
 
 So I chose md5 but it will not work, seems like a basic thing. So I am
 forced to use trust.

How on earth does failure of md5 to work force you to use trust?

How about crypt or password (password is pretty darn simple to set up).

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Steve Crawford]

Gregory Stark wrote:

Steve Crawford scrawf...@pinpointresearch.com writes:

  

3. Date handling
Sometimes I've got data with invalid dates and it would be great if it
could replace all the bad ones with, say -00-00.


Oh dear $DEITY, no. 



I think it would be best if we limited ourselves right now to discussing the
problems themselves and not debating the pros and cons of possible solutions.

I want to encourage people to post their peeves even if they know perfectly
well the reasons why things are the way they are.

  


Fair enough.

My big pg_dump peeve: the inability to dump a function-definition with 
pg_dump.


Cheers,
Steve

Re: [GENERAL] Pet Peeves?

[Steve Atkins]

On Jan 29, 2009, at 5:16 AM, Gregory Stark wrote:



I'm putting together a talk on PostgreSQL Pet Peeves for  
discussion at
FOSDEM 2009 this year. I have a pretty good idea what some them are  
of course,
but I would be interested to hear if people have any complaints from  
personal
experience. What would be most interesting is if you can explain an  
example of
when the problem caused real inconvenience to you, since sometimes  
it's hard

to see from a theoretical description where the real harm lies.

So, what do people say? Is Postgres perfect in your world or does it  
do some

things which rub you the wrong way?

Feel free to respond on-list or if you prefer in personal emails. I  
do intend
to use the ideas you give in my presentation so mark anything you  
wouldn't be

happy to see in a slide at a conference some day.


1. Version upgrades require a dump and restore, which is painfully slow.

2. No distinction between parse errors and execution errors in psql. I  
want mistakes that can be detected at parse time (typos, invalid  
syntax, non-existent columns) to throw an message, and not affect the  
backend state. I want other errors to abort the transaction.  
ON_ERROR_ROLLBACK is better than nothing, but I'd like to distinguish  
the two cases.


3. Returning free space to the OS when there've been large changes to  
a large table. VACUUM FULL is too slow to be useful, CLUSTER requires  
an index, and can often be too slow. I tend to resort to CREATE TABLE  
AS or pg_dump, and manually handle the constraints, which is ugly. I'd  
be happy to have a vacuum equivalent that ran for days, shuffling  
empty pages to the end of the table, as long as it didn't interrupt  
service.


4. UPSERT. It's a really useful primitive, and we don't have it.

5. Backslash characters in strings. It's tricky to put a string in the  
database with a backslash in it, in a way that'll support different  
versions of the backend.


6. Where's my CPAN equivalent? Postgresql is extensible, but it's hard  
to find the extension you need, and often harder than it should be to  
install.


7. Table partitioning. It's nice, but rather limited.

8. pg_dump -d / -D. It's just a footgun.

9. psql vs pg_restore for restoring dumps. It's confusing to explain  
to people.


10. Backups in general. There are much better approaches than pg_dump,  
but explaining them to users is too complex.


Cheers,
  Steve


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Greg Smith]

On Thu, 29 Jan 2009, rhubbell wrote:


So I chose md5 but it will not work, seems like a basic thing. So I am
forced to use trust.  These are the kinds of things that wear down
busy people trying use the software. Maybe this is a documentation
enhancement or bug.


I wrote up a first draft of something aimed at this particular area at 
http://wiki.postgresql.org/wiki/Client_Authentication


I'm still not completely happy with how I describe what you need to switch 
to md5, but at least it points to all the right places people usually 
miss.  If you just went through this recently or still have open issues, 
I'd be curious to get your feedback about that piece.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[johnf]

On Thursday 29 January 2009 05:16:17 am Gregory Stark wrote:
 I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
 FOSDEM 2009 this year. I have a pretty good idea what some them are of
 course, but I would be interested to hear if people have any complaints
 from personal experience. What would be most interesting is if you can
 explain an example of when the problem caused real inconvenience to you,
 since sometimes it's hard to see from a theoretical description where the
 real harm lies.

 So, what do people say? Is Postgres perfect in your world or does it do
 some things which rub you the wrong way?

 Feel free to respond on-list or if you prefer in personal emails. I do
 intend to use the ideas you give in my presentation so mark anything you
 wouldn't be happy to see in a slide at a conference some day.

 --
   Gregory Stark
   EnterpriseDB  http://www.enterprisedb.com
   Ask me about EnterpriseDB's Slony Replication support!

I'm not sure if it's just my version (8.1) but I can't access two different 
database in the same statement as in

insert into public.db1.table1 (select * from public.db2.table2)

-- 
John Fabiani

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Jeff Davis]

On Thu, 2009-01-29 at 13:16 +, Gregory Stark wrote:
 So, what do people say? Is Postgres perfect in your world or does it do some
 things which rub you the wrong way?

The one that has always bothered me is that there's no way to explicitly
set the value that is returned by PQcmdTuples(), i.e. the number of
affected rows.

This makes it very difficult to make truly transparent updatable views
in a complex case, e.g., if you're updating a remote table or something.

Regards,
Jeff Davis


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Tommy Gildseth]

Teodor Sigaev wrote:
I reproduced the bug with a help of Grzegorz's point for 64-bit box. So, 
patch is attached and I'm going to commit it





Thanks a lot. Exceptional response time :D
Less than 2.5 hours from problem reported, till a patch was made. Don't 
think there's many projects or commercial products that can compete with 
that ;-)


--
Tommy Gildseth

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Alan Hodgson]

On Thursday 29 January 2009, Terry Fielder te...@ashtonwoodshomes.com 
wrote:
 and that ties to:
 2) If I try to kill 1 postgres pid (e.g. to abort a bad query), the
 whole backend shuts down and rolls back.
 Can we get a way to look at and then kill a specific bad query?

select pg_cancel_backend(pid). Or kill pid from the shell, it only kills off 
one backend on 8.x anyway.


-- 
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changing owner of pg_toast tables

[Mark Styles]

On Thu, Jan 29, 2009 at 12:29:07PM -0500, Tom Lane wrote:
 Mark Styles postg...@lambic.co.uk writes:
  On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote:
  I guess the interesting question to me is what happened to the tables
  those toast tables are/were attached to? They should have the same
  owners as their parent tables.  
 
  They did have the same owner, I changed the owner to postgres so I could
  drop the role, but the corresponding pg_toast tables did not change.
 
 Well, that's just weird.  Can you reproduce such a behavior?  In my
 tests 8.1 definitely does change the toast table's owner along with the
 parent.  One can imagine that step failing, but if so the whole
 ALTER OWNER transaction should roll back.

Actually, pgadmin3 may have given me an error on that operation (which I
ignored, it did what I wanted, I thought), I believe it was something
like 'OID not found'.

I have to do something similar for another role so I'll pay more
attention then.

 As for getting out of your immediate problem, I think what you'd need to
 do is manually adjust the pg_class.relowner fields for those toast
 tables, and then get rid of the pg_shdepend entries that claim they
 depend on the old role.  (You don't need to put back new entries
 claiming they depend on postgres.)  But I'd sure like to find out what
 happened.  We've heard a few reports before of toast tables not getting
 dropped when their parents were, and I wonder if this is related.

Thanks, I managed to clear out the offending dependencies. relowner was
actually set correctly, but the pg_shdepend records were wrong.

-- 
Mark 
http://www.lambic.co.uk



signature.asc
Description: Digital signature

Re: [GENERAL] Changing owner of pg_toast tables

[Alvaro Herrera]

Mark Styles wrote:
 On Thu, Jan 29, 2009 at 12:29:07PM -0500, Tom Lane wrote:
  Mark Styles postg...@lambic.co.uk writes:
   On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote:
   I guess the interesting question to me is what happened to the tables
   those toast tables are/were attached to? They should have the same
   owners as their parent tables.  
  
   They did have the same owner, I changed the owner to postgres so I could
   drop the role, but the corresponding pg_toast tables did not change.
  
  Well, that's just weird.  Can you reproduce such a behavior?  In my
  tests 8.1 definitely does change the toast table's owner along with the
  parent.  One can imagine that step failing, but if so the whole
  ALTER OWNER transaction should roll back.
 
 Actually, pgadmin3 may have given me an error on that operation (which I
 ignored, it did what I wanted, I thought), I believe it was something
 like 'OID not found'.

I agree with Tom -- this is pretty weird.  If it failed, it should have
failed all the way and rollback the other changes.

On other news, I noticed the other day while playing with reloptions
that if you run an ALTER TABLE command that rewrites the table, the
toast table seems to be misnamed, i.e. the same bug we fixed on CLUSTER
not long ago.  I very much doubt this explains your problem, but still ...

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changing owner of pg_toast tables

[Tom Lane]

Mark Styles postg...@lambic.co.uk writes:
 Thanks, I managed to clear out the offending dependencies. relowner was
 actually set correctly, but the pg_shdepend records were wrong.

Hmm ... what actually was in the pg_shdepend entries?

Given the way the code works, this could be explained by a corrupt index
for pg_shdepend (which could cause it to fail to find the entries it
should've deleted).  But I don't see how that would lead to an OID not
found type of message.  Also, if you did get an error, that should've
rolled back the whole thing.  So I'm still baffled.

regards, tom lane

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Greg Smith]

The biggest peeve I still have to fight is attached to the old why aren't 
there any optimizer hints? tree.  PostgreSQL forces you to understand a 
non-trivial amount of how the query optimizer works before you can get it 
to do the right thing once you get beyond a small database, and nobody 
likes doing the why isn't it using the index?! dance.


When you turn enable_seqscan off and it proceeds to do a seqscan anyway 
when there's seemingly a perfect index right there, it's pretty 
frustrating.  I spent the better part of a weekend last year fighting a 
battle with a single select * from t where type='x' and ts='date1' and 
ts='date2, running against a giant table with an index on (type,ts) that 
just stopped using that index for mysterious reasons.  You do not want to 
seqscan a billion rows.


The main thing I'd like to see here is more visibility into rejected 
plans, so that at least people could see those costs.  If I could have 
seen whether the cost estimate on the index-based plan was going up or 
down as I tweaked parameters/updated stats, at least then I could quantify 
my progress toward returning to where the right plan was preferred one. 
The flip side is that as it is right now, it's also hard to answer the 
question how close am I to having this plan fail? until it already has. 
I know there's been some academic work in this area as part of classes on 
database internals, I'd like to see some of that turn into a production 
feature.


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[rhubbell]

Umm, because md5 doesn't work and trust does work.
On Thu, 29 Jan 2009 13:16:19 -0500
Bill Moran wmo...@potentialtech.com wrote:

 In response to rhubbell rhubb...@ihubbell.com:
  
  I'm a new user to PostgreSQL so mine's fresh from doing an install recently.
  
  In /etc/postgresql/8.3/main/pg_hba.conf
  
  # METHOD can be trust, reject, md5, crypt, password, gss, 
  sspi,
  # krb5, ident, pam or ldap.  Note that password sends passwords
  # in clear text; md5 is preferred since it sends encrypted passwords.
  
  
  So I chose md5 but it will not work, seems like a basic thing. So I am
  forced to use trust.
 
 How on earth does failure of md5 to work force you to use trust?
 
 How about crypt or password (password is pretty darn simple to set up).
 
 -- 
 Bill Moran
 http://www.potentialtech.com
 http://people.collaborativefusion.com/~wmoran/
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[rhubbell]

Ok will have a look and get back to you, thanks.

On Thu, 29 Jan 2009 13:39:08 -0500 (EST)
Greg Smith gsm...@gregsmith.com wrote:

 On Thu, 29 Jan 2009, rhubbell wrote:
 
  So I chose md5 but it will not work, seems like a basic thing. So I am
  forced to use trust.  These are the kinds of things that wear down
  busy people trying use the software. Maybe this is a documentation
  enhancement or bug.
 
 I wrote up a first draft of something aimed at this particular area at 
 http://wiki.postgresql.org/wiki/Client_Authentication
 
 I'm still not completely happy with how I describe what you need to switch 
 to md5, but at least it points to all the right places people usually 
 miss.  If you just went through this recently or still have open issues, 
 I'd be curious to get your feedback about that piece.
 
 --
 * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Alan Hodgson]

On Thursday 29 January 2009, Gregory Stark st...@enterprisedb.com wrote:
 I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
 FOSDEM 2009 this year. I have a pretty good idea what some them are of
 course, but I would be interested to hear if people have any complaints
 from personal experience. What would be most interesting is if you can
 explain an example of when the problem caused real inconvenience to you,
 since sometimes it's hard to see from a theoretical description where the
 real harm lies.

 So, what do people say? Is Postgres perfect in your world or does it do
 some things which rub you the wrong way?

Lack of in-place upgrades for major version changes.

I have others, but honestly this is the biggest that comes up over and over 
again.


-- 
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

[Jeff Frost]

On Thu, 29 Jan 2009, rhubbell wrote:


Umm, because md5 doesn't work and trust does work.


Generally this is because you haven't yet set a password for the postgres 
user.  You have to set a password for at least the postgres user via ALTER 
ROLE while you've still got it set to trust or ident before changing to md5.



On Thu, 29 Jan 2009 13:16:19 -0500
Bill Moran wmo...@potentialtech.com wrote:


In response to rhubbell rhubb...@ihubbell.com:


I'm a new user to PostgreSQL so mine's fresh from doing an install recently.

In /etc/postgresql/8.3/main/pg_hba.conf

# METHOD can be trust, reject, md5, crypt, password, gss, sspi,
# krb5, ident, pam or ldap.  Note that password sends passwords
# in clear text; md5 is preferred since it sends encrypted passwords.


So I chose md5 but it will not work, seems like a basic thing. So I am
forced to use trust.


How on earth does failure of md5 to work force you to use trust?

How about crypt or password (password is pretty darn simple to set up).



--
Jeff Frost, Owner   j...@frostconsultingllc.com
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

[rhubbell]

On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
Jeff Frost j...@frostconsultingllc.com wrote:

 On Thu, 29 Jan 2009, rhubbell wrote:
 
  Umm, because md5 doesn't work and trust does work.
 
 Generally this is because you haven't yet set a password for the postgres 
 user.  You have to set a password for at least the postgres user via ALTER 
 ROLE while you've still got it set to trust or ident before changing to md5.

Yeah, yeah, did all that, didn't work. Sorry, still a Pet Peeve. (^;
While you mention it, another Pet Peeve was the use of ident. Yikes.

 
  On Thu, 29 Jan 2009 13:16:19 -0500
  Bill Moran wmo...@potentialtech.com wrote:
 
  In response to rhubbell rhubb...@ihubbell.com:
 
  I'm a new user to PostgreSQL so mine's fresh from doing an install 
  recently.
 
  In /etc/postgresql/8.3/main/pg_hba.conf
 
  # METHOD can be trust, reject, md5, crypt, password, gss, 
  sspi,
  # krb5, ident, pam or ldap.  Note that password sends passwords
  # in clear text; md5 is preferred since it sends encrypted passwords.
 
 
  So I chose md5 but it will not work, seems like a basic thing. So I am
  forced to use trust.
 
  How on earth does failure of md5 to work force you to use trust?
 
  How about crypt or password (password is pretty darn simple to set up).
 
 
 -- 
 Jeff Frost, Owner j...@frostconsultingllc.com
 Frost Consulting, LLC http://www.frostconsultingllc.com/
 Phone: 916-647-6411   FAX: 916-405-4032

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[Andrew Sullivan]

On Thu, Jan 29, 2009 at 09:51:42AM -0800, Joshua D. Drake wrote:
 It should be pg_backup and that is it, with a nice -R flag for restore.

I suppose you think that ssh_add -D is an intuitive interface too?  ;-)

A

-- 
Andrew Sullivan
a...@crankycanuck.ca

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Changing owner of pg_toast tables

[Mark Styles]

On Thu, Jan 29, 2009 at 02:11:37PM -0500, Tom Lane wrote:
 Mark Styles postg...@lambic.co.uk writes:
  Thanks, I managed to clear out the offending dependencies. relowner was
  actually set correctly, but the pg_shdepend records were wrong.
 
 Hmm ... what actually was in the pg_shdepend entries?

I guess I should've noted that down eh? From memory, the classid was 
the oid of the pg_toast object, the refobjid was the oid of the role,
the deptype was 'o', I don't recall what the other values were.

I'll keep my eye out for more problems as I work through tidying this
database.

-- 
Mark 
http://www.lambic.co.uk



signature.asc
Description: Digital signature

Re: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

[Bill Moran]

In response to rhubbell rhubb...@ihubbell.com:

 On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
 Jeff Frost j...@frostconsultingllc.com wrote:
 
  On Thu, 29 Jan 2009, rhubbell wrote:
  
   Umm, because md5 doesn't work and trust does work.
  
  Generally this is because you haven't yet set a password for the postgres 
  user.  You have to set a password for at least the postgres user via ALTER 
  ROLE while you've still got it set to trust or ident before changing to md5.
 
 Yeah, yeah, did all that, didn't work. Sorry, still a Pet Peeve. (^;
 While you mention it, another Pet Peeve was the use of ident. Yikes.

_My_ point was that a broken md5 (which is still under debate) doesn't
force you to use trust.  There are other auth options like crypt and
password.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

[Alan Hodgson]

On Thursday 29 January 2009, rhubbell rhubb...@ihubbell.com wrote:
 On Thu, 29 Jan 2009 11:34:00 -0800 (PST)

 Jeff Frost j...@frostconsultingllc.com wrote:
  On Thu, 29 Jan 2009, rhubbell wrote:
   Umm, because md5 doesn't work and trust does work.
 
  Generally this is because you haven't yet set a password for the
  postgres user.  You have to set a password for at least the postgres
  user via ALTER ROLE while you've still got it set to trust or ident
  before changing to md5.

 Yeah, yeah, did all that, didn't work. Sorry, still a Pet Peeve. (^;
 While you mention it, another Pet Peeve was the use of ident. Yikes.


Well, it works for everyone else. So clearly you missed something.

-- 
Current Peeve: The mindset that the Internet is some sort of school for
novice sysadmins and that everyone -not- doing stupid dangerous things
should act like patient teachers with the ones who are. -- Bill Cole, NANAE 

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Pet Peeves?

[rhubbell]

Another Pet Peeve:

Where oh where is pg_config? Oh where oh where can it be?


On Thu, 29 Jan 2009 13:16:17 +
Gregory Stark st...@enterprisedb.com wrote:

 
 I'm putting together a talk on PostgreSQL Pet Peeves for discussion at
 FOSDEM 2009 this year. I have a pretty good idea what some them are of course,
 but I would be interested to hear if people have any complaints from personal
 experience. What would be most interesting is if you can explain an example of
 when the problem caused real inconvenience to you, since sometimes it's hard
 to see from a theoretical description where the real harm lies.
 
 So, what do people say? Is Postgres perfect in your world or does it do some
 things which rub you the wrong way?
 
 Feel free to respond on-list or if you prefer in personal emails. I do intend
 to use the ideas you give in my presentation so mark anything you wouldn't be
 happy to see in a slide at a conference some day.
 
 -- 
   Gregory Stark
   EnterpriseDB  http://www.enterprisedb.com
   Ask me about EnterpriseDB's Slony Replication support!
 
 -- 
 Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-general

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] need contact in Nepal

[Oleg Bartunov]

Hi there,

I plan to visit Nepal in april (Annapurna trek), so if there is 
an interest I can give a talk about PostgreSQL and discuss

some aspects of full-text search and nepal language.
I heard that PostgreSQL is used in Nepal.

Please, contact me offlist.

Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Text search segmentation fault

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 6:53 PM, Tommy Gildseth
tommy.gilds...@usit.uio.no wrote:
 Thanks a lot. Exceptional response time :D
 Less than 2.5 hours from problem reported, till a patch was made. Don't
 think there's many projects or commercial products that can compete with
 that ;-)

Oh, wait , it still has to go through 14 days QA, doesn't it ?
:D

-- 
GJ

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general