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

[Scott Marlowe]

On Thu, Jan 29, 2009 at 12:41 PM, rhubbell  wrote:
> On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
> Jeff Frost  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.

So, maybe you could tell us what "didn't work" means in a more
expanded manner, along with things like error messages?  md5 works a
charm for me, and it has since it came out, so I'm wondering what's so
different in your setup that it doesn't.

-- 
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?

[Craig Ringer]

Martin Gainty wrote:

PROCEDUREs *which compile into Procedure Cache* and have IN/OUT (Mode) 
parameters..


Do you mean that as a feature request?

If you intended to say something along the lines of: "I'd like stored 
procedures, invoked using 'CALL procname(params)' syntax, as distinct 
from SQL or PL/PgSQL functions" ... then I do agree that'd be handy.


Their absence could easily become a peeve, especially if you use DB 
interfaces that expect to invoke stored procedures this way. Some seem 
to want you to jump through some hoops (switch to "native query" mode, 
declare functions to the interface, etc) to call server-side functions, 
but provide a convenient interface to CALL stored procedures.


--
Craig Ringer

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

Re: [GENERAL] Pet Peeves?

[Martin Gainty]

PROCEDUREs *which compile into Procedure Cache* and have IN/OUT (Mode) 
parameters..

Martin Gainty 
__ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business 
of Sender. This transmission is of a confidential nature and Sender does not 
endorse distribution to any party other than intended recipient. Sender does 
not necessarily endorse content contained within this transmission. 




> Date: Fri, 30 Jan 2009 12:37:11 +0900
> From: cr...@postnewspapers.com.au
> To: st...@enterprisedb.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Pet Peeves?
> 
> 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 few things that used to really bug me have gone away between 8.1 and
> 8.3. The big one is that there are no longer issues with temp tables in
> PL/PgSQL functions or any of the other problems related to the lack of
> automatic plan invalidation for functions.
> 
> I could go on forever about the things I LIKE about Pg.
> 
> 
> There are a few niggles I have noticed, though:
> 
> - VACUUM FULL is rather slow and often leaves indexes badly bloated.
> This is a usability issue for new admins, too, who won't know they're
> usually better off using CLUSTER. That in its self suggests something's
> not right, IMO.
> 
> - There's no REINDEX CONCURRENTLY.
> 
> - There are no built-in ways for admins to easily discover, be alerted
> to, or manually check for index and table bloat. We NEED a
> pg_catalog.pg_bloat view IMO, as well as NOTICE level warnings from
> VACUUM when very bloated indexes and tables are discovered. This is a
> mainly a usability issue for new admins.
> 
> - Bytea's literal format is wasteful and is painful to work with.
> Supporting something reasonably compact and commonly understood by most
> tools and libraries (like, say, base64) would be really nice. It'd also
> be useful for backup/restore.
> 
> - The problems involved in restoring/upgrading a database to a newer
> major version when extensions like PostGIS are in use. Argh.
> 
> - Table partitioning is effective, but somewhat clumsy, and would really
> benefit from some automatic management tools.
> 
> - No column-level triggers and, thus, no way to attach a trigger to a
> domain type.
> 
> - The need for a dump and restore for major version upgrades. I
> understand why, but ...
> 
> --
> Craig Ringer
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

_
Windows Live™ Hotmail®:…more than just e-mail. 
http://windowslive.com/explore?ocid=TXT_TAGLM_WL_t2_hm_justgotbetter_explore_012009

Re: [GENERAL] Pet Peeves?

[Craig Ringer]

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 few things that used to really bug me have gone away between 8.1 and
8.3. The big one is that there are no longer issues with temp tables in
PL/PgSQL functions or any of the other problems related to the lack of
automatic plan invalidation for functions.

I could go on forever about the things I LIKE about Pg.


There are a few niggles I have noticed, though:

- VACUUM FULL is rather slow and often leaves indexes badly bloated.
This is a usability issue for new admins, too, who won't know they're
usually better off using CLUSTER. That in its self suggests something's
not right, IMO.

- There's no REINDEX CONCURRENTLY.

- There are no built-in ways for admins to easily discover, be alerted
to, or manually check for index and table bloat. We NEED a
pg_catalog.pg_bloat view IMO, as well as NOTICE level warnings from
VACUUM when very bloated indexes and tables are discovered. This is a
mainly a usability issue for new admins.

- Bytea's literal format is wasteful and is painful to work with.
Supporting something reasonably compact and commonly understood by most
tools and libraries (like, say, base64) would be really nice. It'd also
be useful for backup/restore.

- The problems involved in restoring/upgrading a database to a newer
major version when extensions like PostGIS are in use. Argh.

- Table partitioning is effective, but somewhat clumsy, and would really
benefit from some automatic management tools.

- No column-level triggers and, thus, no way to attach a trigger to a
domain type.

- The need for a dump and restore for major version upgrades. I
understand why, but ...

--
Craig Ringer

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

Re: [GENERAL] Full backup - pg_dumpall sufficient?

[Tom Lane]

Jeff Frost  writes:
>> I guess I could pg_dumpall -s | grep "ALTER DATABASE" to grab that stuff.

> That seems silly.  Is this the best way to find this data:

> SELECT name, setting FROM pg_settings where source = 'database' ORDER BY
> name;   

No, you'd miss anything overridden locally in your session.

I'd think about getting it out of pg_database.datconfig, instead.
Or really the easiest way is to tweak the logic in pg_dumpall about
what to dump when ...

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?

[Jeff Frost]

Jeff Frost wrote:
> On Thu, 29 Jan 2009, Tom Lane wrote:
>
>> Jeff Frost  writes:
>>> 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?
>>
>> Regular pg_dumpall will catch that.
>>
>> There's been some previous discussion about redrawing the dividing lines
>> so that this doesn't fall between the cracks when you try to use
>> --globals plus per-database pg_dump, but AFAIR nothing's been done about
>> it yet.  It's a bit tricky since it's not entirely clear who's
>> responsible for creating the individual databases when you restore in
>> that scenario.
>
> I guess I could pg_dumpall -s | grep "ALTER DATABASE" to grab that stuff.
>
That seems silly.  Is this the best way to find this data:

SELECT name, setting FROM pg_settings where source = 'database' ORDER BY
name;   

?

-- 
Jeff Frost, Owner   
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?

[Adrian Klaver]

On Thursday 29 January 2009 9:19:15 am rhubbell wrote:
> 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 realize the authentication is done top to bottom? The first line that 
matches wins :) So if you have a matching "trust" line before your "md5" line 
then it will not use md5.

For a better explanation:

http://www.postgresql.org/docs/8.2/interactive/auth-pg-hba-conf.html

"Each record specifies a connection type, a client IP address range (if 
relevant 
for the connection type), a database name, a user name, and the authentication 
method to be used for connections matching these parameters. The first record 
with a matching connection type, client address, requested database, and user 
name is used to perform authentication. There is no "fall-through" or "backup": 
if one record is chosen and the authentication fails, subsequent records are 
not considered. If no record matches, access is denied."


-- 
Adrian Klaver
akla...@comcast.net

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

Re: [GENERAL] Full backup - pg_dumpall sufficient?

[Jeff Frost]

On Thu, 29 Jan 2009, Tom Lane wrote:


Jeff Frost  writes:

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?


Regular pg_dumpall will catch that.

There's been some previous discussion about redrawing the dividing lines
so that this doesn't fall between the cracks when you try to use
--globals plus per-database pg_dump, but AFAIR nothing's been done about
it yet.  It's a bit tricky since it's not entirely clear who's
responsible for creating the individual databases when you restore in
that scenario.


I guess I could pg_dumpall -s | grep "ALTER DATABASE" to grab that stuff.

--
Jeff Frost, Owner   
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] Full backup - pg_dumpall sufficient?

[Tom Lane]

Jeff Frost  writes:
> 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? 

Regular pg_dumpall will catch that.

There's been some previous discussion about redrawing the dividing lines
so that this doesn't fall between the cracks when you try to use
--globals plus per-database pg_dump, but AFAIR nothing's been done about
it yet.  It's a bit tricky since it's not entirely clear who's
responsible for creating the individual databases when you restore in
that scenario.

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?

[Erik Jones]

On Jan 29, 2009, at 9:43 AM, David Fetter wrote:


On Thu, Jan 29, 2009 at 05:18:19PM +, Gregory Stark wrote:

David Fetter  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.


I'd say a good example close of this is the ability to generate full  
create statements for database objects via an SQL command.  I.e.  
shelling out to pg_dump is not always a fun option.


Erik Jones, Database Administrator
Engine Yard
Support, Scalability, Reliability
866.518.9273 x 260
Location: US/Pacific
IRC: mage2k






--
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, Ron Mayer wrote:


 - Surely someone wrote a good count(*)-replacement-trigger before.
   Now where can I find one?  Searching for "count" on pgfoundry
   doesn't help me.   Searching varlena genralbits find shows me
   a simple one, but IIRC is lacking when it comes to concurrency.


There were two Varlena postings on this and one other good article.  I got 
sick of not being to find them every time I wanted to and added links to 
them all at http://wiki.postgresql.org/wiki/Slow_Counting


--
* 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

[GENERAL] Call volume query

[Mike Diehl]

Hi all.

I've encountered an SQL problem that I think is beyond my skills...

I've got a table full of records relating to events (phone calls, in
this case) and I need to find the largest number of events (calls)
occurring at the same time.

The table had a start timestamp and a duration field which contains the
length of the call in seconds.

I need to find out how many concurrent calls I supported, at peek
volume.

Can this be done in SQL?  Or do I need to write a perl script?

Thank you,
Mike.


-- 
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] How relate pg_class to pg_constraint

[Gregory Stark]

Bill Todd  writes:

> I need to join pg_class and pg_constraint to get information about constraints
> on a table. It appears that pg_constraint.conrelid is the foreign key but I do
> not see a relid column in pg_class. What column(s) define the relationship
> between these tables? Thanks.

There's a system column called "oid" on all the system tables which is the
primary key. It doesn't show up unless you explicitly list it in the target
list of the select.

So you need a join like WHERE pg_class.oid = conrelid

If all you need is the name to display for users then there's a convenience
type called regclass which you can use by doing "SELECT conrelid::regclass
from pg_constraint". There are similar regtype and a few others like it too.

-- 
  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] How relate pg_class to pg_constraint

[Bill Todd]

Bill Todd wrote:
I need to join pg_class and pg_constraint to get information about 
constraints on a table. It appears that pg_constraint.conrelid is the 
foreign key but I do not see a relid column in pg_class. What 
column(s) define the relationship between these tables? Thanks.


Bill

Is the relationship pg_constraint.conrelid = pg_class.oid? I assume the 
column lists for the system tables do not include the oid column because 
everyone (but us newbies) knows that every system table has an oid 
column.


Bill

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

[GENERAL] How relate pg_class to pg_constraint

[Bill Todd]

I need to join pg_class and pg_constraint to get information about 
constraints on a table. It appears that pg_constraint.conrelid is the 
foreign key but I do not see a relid column in pg_class. What column(s) 
define the relationship between these tables? Thanks.


Bill

--
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?

[Karsten Hilbert]

On Thu, Jan 29, 2009 at 09:51:42AM -0800, Joshua D. Drake wrote:

> If we are listing pet peeves :)
> 
> Up to 8.4, postgresql didn't accurately represent timestamps because

Ah, speaking of timestamps:

GNUmed could nicely use a timestamp with time zone which
preserves the time zone that was used for insertion/update.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] ssl to more than one server

[Ivan Sergio Borgonovo]

On Thu, 29 Jan 2009 12:53:20 -0500
Tom Lane  wrote:

> Ivan Sergio Borgonovo  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.

I did a cat new.(crt|key) >> postgresql.(crt|key) on the client.
The old "server" still work. The new one still doesn't.

I took notes on how I did the first time and I think they were
enough detailed to repeat the process but I've to admit I really
didn't understand what I did the first time, so I'm not absolutely
sure if I really did it right.

Somehow I haven't been able to find an howto that really explain how
to do it and grasp enough to be confident to bend it enough to a bit
broader context.

I just know that eg. auto-signing a certificate for apache is much
easier and doesn't involve moving files across client and server.

I think a clearer guide from some pg/ssl guru will be certainly very
welcome by all the users.

The server is not complaining... actually it is the client that is
not able to reply with a proper key.

-- 
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?

[Steve Atkins]

On Jan 29, 2009, at 12:25 PM, Gregory Stark wrote:


Steve Atkins  writes:

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.


FWIW our CPAN equivalent is pgfoundry. I don't think we quite have the
critical mass yet that Perl has to really make it a big success  
though.


Kinda. It's much more like a freshmeat/sourceforge equivalent than a
CPAN equivalent. There's no standard package format, no dependency
or version handling, no possibility of automated installation.

It also has a mixture of postgresql modules, client-side code that can
access postgresql and even occasional projects that have nothing much
to do with postgresql.

Making modules more, uh, modular, so they can be installed and  
uninstalled
smoothly and preferably without special access privileges is a  
recognized

issue though.



Yup, that's one prerequisite for the rest of it, really. pgTap, which  
we have

now, was probably another.

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?

[Karsten Hilbert]

On Thu, Jan 29, 2009 at 02:22:28PM -0300, Alvaro Herrera 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
+1

Or, in other words, no indices across table (am I wrong ?).

GNUmed uses table inheritance quite extensively and could
save an additional key column per child table.

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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

Re: [GENERAL] Pet Peeves?

[Ron Mayer]

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,


* The capitalization that makes everyone (customers, execs, etc) I introduce
  it to parse the name as Postgre-SQL.


* Last I checked, postgres keeps my laptop's hard drive spinning all the time
  by doing some I/O even when "totally idle".  For that matter, waking
  up the CPUs a few times a second too.

* pgfoundry - which was once described on the mailinglists as
  the "kiss of death" for projects.   Yes, I understand why it is
  what it is; but it's still a pet-peeve.   I can't even put my finger
  on what's annoying about it; but it is.

* Upgrades involving databases with extra modules like postgis.
  $ sh utils/postgis_restore.pl lwpostgis.sql newdb olddb.dump -E=UNICODE

* Finding extensions I might want.  For example:
  - Surely someone wrote a good count(*)-replacement-trigger before.
Now where can I find one?  Searching for "count" on pgfoundry
doesn't help me.   Searching varlena genralbits find shows me
a simple one, but IIRC is lacking when it comes to concurrency.
Googling just gets me lots of "read the archives" postings in
the archives.


-- 
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?

[hubert depesz lubaczewski]

1. pg_dump -d - do i need to explain?
2. psql is not compatible with different (older) version of pg, in terms
   of working \x commands
3. lack of optimizer hints
4. lack of covering indexes
5. lack of jobs (like cron, not like something to do to be paid :)

depesz

-- 
Linkedin: http://www.linkedin.com/in/depesz  /  blog: http://www.depesz.com/
jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

-- 
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 Atkins  writes:

> 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.

FWIW our CPAN equivalent is pgfoundry. I don't think we quite have the
critical mass yet that Perl has to really make it a big success though.

Making modules more, uh, modular, so they can be installed and uninstalled
smoothly and preferably without special access privileges is a recognized
issue though.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

-- 
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 8:10 PM, rhubbell  wrote:
>
> Another "Pet Peeve":
>
> Where oh where is pg_config? Oh where oh where can it be?

$PGDIR/bin ?

-- 
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] Text search segmentation fault

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 6:53 PM, Tommy Gildseth
 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 Q&A, 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

[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] 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  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: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

[Alan Hodgson]

On Thursday 29 January 2009, rhubbell  wrote:
> On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
>
> Jeff Frost  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: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

[Bill Moran]

In response to rhubbell :

> On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
> Jeff Frost  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: [GENERAL] Changing owner of pg_toast tables

[Mark Styles]

On Thu, Jan 29, 2009 at 02:11:37PM -0500, Tom Lane wrote:
> Mark Styles  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: [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: md5 doesn't work (Was Re: [GENERAL] Pet Peeves?)

[rhubbell]

On Thu, 29 Jan 2009 11:34:00 -0800 (PST)
Jeff Frost  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  wrote:
> >
> >> In response to 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".
> >>
> >> 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 
> 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

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  wrote:


In response to 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".


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   
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?

[Alan Hodgson]

On Thursday 29 January 2009, 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?

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

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  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?

[rhubbell]

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

> In response to 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".
> 
> 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?

[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] Changing owner of pg_toast tables

[Tom Lane]

Mark Styles  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] 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  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

[Mark Styles]

On Thu, Jan 29, 2009 at 12:29:07PM -0500, Tom Lane wrote:
> Mark Styles  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] Pet Peeves?

[Alan Hodgson]

On Thursday 29 January 2009, Terry Fielder  
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] 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?

[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] 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?

[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?

[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?

[Steve Crawford]

Gregory Stark wrote:

Steve Crawford  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?

[Bill Moran]

In response to 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".

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] Full backup - pg_dumpall sufficient?

[Jeff Frost]

Tom Lane wrote:
> Gerhard Wiesinger  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   
Frost Consulting, LLC   http://www.frostconsultingllc.com/
Phone: 916-647-6411 FAX: 916-405-4032

Re: [GENERAL] Pet Peeves?

[Gregory Stark]

Steve Crawford  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] ssl to more than one server

[Tom Lane]

Ivan Sergio Borgonovo  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?

[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] Full backup - pg_dumpall sufficient?

[Tom Lane]

Gerhard Wiesinger  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] Encoding problem using pg_dumpall

[Magnus Hagander]

Tom Lane wrote:
> Magnus Hagander  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] 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] Pet Peeves?

[David Fetter]

On Thu, Jan 29, 2009 at 05:18:19PM +, Gregory Stark wrote:
> David Fetter  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
 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  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?

[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?

[Richard Huxton]

Gregory Stark wrote:
> Jason Long  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] Encoding problem using pg_dumpall

[Tom Lane]

Magnus Hagander  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] 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] 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] Pet Peeves?

[Jason Long]

Gregory Stark wrote:

Jason Long  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] Encoding problem using pg_dumpall

[Magnus Hagander]

Tom Lane wrote:
> "Moshe Ben-Shoham"  writes:
>> C:\Program Files\PostgreSQL\8.3\bin>pg_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] Changing owner of pg_toast tables

[Tom Lane]

Mark Styles  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

[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= 

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] 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

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  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  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?

[Gregory Stark]

Jason Long  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?

[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  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] 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?

[Gregory Stark]

David Fetter  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] Pet Peeves?

[Dave Page]

On Thu, Jan 29, 2009 at 4:57 PM, David Fetter  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

[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] Text search segmentation fault

[Tom Lane]

Teodor Sigaev  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

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] 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] Full backup - pg_dumpall sufficient?

[Tom Lane]

Gerhard Wiesinger  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] 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  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?

[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] Text search segmentation fault

[Tom Lane]

Gregory Stark  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

[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] 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

Re: [GENERAL] Text search segmentation fault

[Gregory Stark]

Tom Lane  writes:

> Gregory Stark  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] Pet Peeves?

[Bill Moran]

In response to Terry Fielder :
> 
> 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] 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] Text search segmentation fault

[Tom Lane]

Teodor Sigaev  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] Text search segmentation fault

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 4:06 PM, Gregory Stark  wrote:
> Gregory Stark  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

[Gregory Stark]

Gregory Stark  writes:

> Teodor Sigaev  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 char<255> was coming out as MAX_INT instead of 255.

#include 

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

[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

[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

[Tom Lane]

Gregory Stark  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] Changing owner of pg_toast tables

[Mark Styles]

On Thu, Jan 29, 2009 at 10:46:08AM -0500, Tom Lane wrote:
> Mark Styles  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

[Teodor Sigaev]

Tom Lane wrote:

Teodor Sigaev  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] Text search segmentation fault

[Gregory Stark]

Teodor Sigaev  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] Changing owner of pg_toast tables

[Tom Lane]

Mark Styles  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] Pet Peeves?

[Richard Broersma]

On Thu, Jan 29, 2009 at 5:16 AM, 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?

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] Text search segmentation fault

[Tom Lane]

Teodor Sigaev  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] Text search segmentation fault

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 3:32 PM, Teodor Sigaev  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

[Grzegorz Jaśkiewicz]

On Thu, Jan 29, 2009 at 3:26 PM, 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

:)

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

[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

[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