[HACKERS] NOTICE: CREATE TABLE will create implicit triggers for foreign-key checks

[Peter Eisentraut]

Regarding the NOTICE

CREATE TABLE will create implicit triggers for foreign-key checks

Does anyone care?

The other "helpful" notices about sequences for serial columns and indexes
for unique constraints have some merit, because they inform the user
objects that the user might be interested in are going to be created as a
side effect.  But there's nothing that a user can reasonably do with an
implicit trigger for a foreign-key check.  ISTM that this notice is about
as interesting as the information that CREATE VIEW will create an implicit
rule.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] Thoughts on maintaining 7.3

[Marc G. Fournier]

On Tue, 30 Sep 2003, Joshua D. Drake wrote:

> Hello,
>
>   With the recent stint of pg_upgrade statements and the impending
> release of 7.4 what do people think about having a dedicated maintenance
> team for 7.3? 7.3 is a pretty solid release and I think people will be
> hard pressed to upgrade to 7.4.  Of course a lot of people will, but I
> have customer that are just now upgrading to 7.3 because of legacy
> application and migratory issues.
>
>Anyway I was considering a similar situation to how Linux works where
> their is a maintainer for each release... Heck even Linux 2.0 still
> released until recently.
>
>   Of course the theory being that we backport "some" features and fix
> any bugs that we find?
>
>What are people's thoughts on this?

The key issue here is that those creating the patches need to spend the
time to create appropriate ones for v7.3, and not many seem willing ...
Tom generally does alot of work on back-patching where appropriate, but
those patches are generally either very critical, or benign to changes
since v7.3 ...

The main detractor from us doing this up to this point has been, I
believe, testing to make sure any back patches don't break *any* of the
various OS ports, testing that generally only gets done while in a Beta
freeze ...

Not saying that if someone submit'd patches to v7.3, they wouldn't get
applied ... only that, to date, the work/effort has been greater then the
overall benefit, and nobody has step'd up to the plate to do it ...

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Wednesday beta postponed till Thursday

[Marc G. Fournier]

On Wed, 1 Oct 2003, Christopher Kings-Lynne wrote:

> Just use FreeBSD 5 - background fsck.

Trust me, I'm soo looking forward to 5.x to be rated 'stable enough
for a production server' .. I spent a good portion of yesterday aft
chatting on the -current mailng list about how slow fsck was :(

I run 5.x on the computers here in the office, and have had the occasional
"freak reboot", so she's not quite there yet ... hopefully in the spring I
can take some time to start migrating over to it ...

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Wednesday beta postponed till Thursday

[Marc G. Fournier]

On Wed, 1 Oct 2003, Tom Lane wrote:

> Christopher Kings-Lynne <[EMAIL PROTECTED]> writes:
> > Just use FreeBSD 5 - background fsck.
>
> Apparently Marc doesn't think FreeBSD 5 is stable enough to use yet.

Trust me, if I felt confident enough with it, we'd already be moved ...
after Xmas, hopefully be able to start with it ... *cross fingers*
They've done some major re-writes of the SMP code to improve performance
that I'd love to get running ...

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Thoughts on maintaining 7.3

[Robert Treat]

On Wed, 2003-10-01 at 08:36, Marc G. Fournier wrote:
> 
> 
> On Tue, 30 Sep 2003, Joshua D. Drake wrote:
> 
> > Hello,
> >
> >   With the recent stint of pg_upgrade statements and the impending
> > release of 7.4 what do people think about having a dedicated maintenance
> > team for 7.3? 7.3 is a pretty solid release and I think people will be
> > hard pressed to upgrade to 7.4.  Of course a lot of people will, but I
> > have customer that are just now upgrading to 7.3 because of legacy
> > application and migratory issues.
> >
> >Anyway I was considering a similar situation to how Linux works where
> > their is a maintainer for each release... Heck even Linux 2.0 still
> > released until recently.
> >
> >   Of course the theory being that we backport "some" features and fix
> > any bugs that we find?
> >
> >What are people's thoughts on this?
> 
> The key issue here is that those creating the patches need to spend the
> time to create appropriate ones for v7.3, and not many seem willing ...
> Tom generally does alot of work on back-patching where appropriate, but
> those patches are generally either very critical, or benign to changes
> since v7.3 ...
> 
> The main detractor from us doing this up to this point has been, I
> believe, testing to make sure any back patches don't break *any* of the
> various OS ports, testing that generally only gets done while in a Beta
> freeze ...
> 
> Not saying that if someone submit'd patches to v7.3, they wouldn't get
> applied ... only that, to date, the work/effort has been greater then the
> overall benefit, and nobody has step'd up to the plate to do it ...

Maybe I've mis-read Joshua's intentions, but I got the impression that
this 7.3 maintainer would follow the patches list and backport patches
whenever possible. This way folks coding for 7.4/7.5 can stay focused on
that, but folks who can't upgrade to 7.4 for whatever reason can still
get some features / improvements. 

Several linux distros already do this for many packages, and personally
I've always been surprised that, given postgresql's major release
upgrade issues, that no commercial company has stepped in to offer this
in the past. I think what Joshua is wondering is how much cooperation
would he get from the community if he was willing to donate these
efforts back into project.

While your concerns about testing are valid, there are already issues
with that for minor releases, as evidenced by our need to do the quick
7.3.4 after trouble in 7.3.3. Not to mention how little testing is
happening to the code that's been back patched into 7.3 since 7.3.4...
Hmm... maybe thats actually an argument against having more changes get
put in, OTOH if Joshua can address the testing issues maybe there would
be an overall improvement.  

I personally think it's a good idea for *someone* to do this, but I'll
leave it to core to decide if they want to put the projects stamp of
approval on it for any official community release.

Robert Treat 
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Fix for PL/Tcl

[Jan Wieck]

Bruce Momjian wrote:
Jan Wieck wrote:
Just committed a small fix for PL/Tcl.

I don't find it on the TODO, but you might want to add it to the release 
notes.

 * Fixed PL/Tcl's spi_prepare to accept full qualified type names in
   the parameter type list.
Oops, properly added to release notes, removed from TODO.
Should this be backpatched into 7.3 as well?

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Thoughts on maintaining 7.3

[scott.marlowe]

On Tue, 30 Sep 2003, Joshua D. Drake wrote:

> Hello,
> 
>   With the recent stint of pg_upgrade statements and the impending 
> release of 7.4 what
> do people think about having a dedicated maintenance team for 7.3? 7.3 
> is a pretty
> solid release and I think people will be hard pressed to upgrade to 7.4. 
> Of course
> a lot of people will, but I have customer that are just now upgrading to 
> 7.3 because
> of legacy application and migratory issues.
> 
>Anyway I was considering a similar situation to how Linux works where 
> their is a
> maintainer for each release... Heck even Linux 2.0 still released until 
> recently.
> 
>   Of course the theory being that we backport "some" features and fix 
> any bugs that
> we find?
> 
>What are people's thoughts on this?

It seems to me the upgrade from 7.2 to 7.4 is easier than an upgrade to 
7.3, since at least 7.4's pg_dumpall can connect to a 7.2 database and 
suck in everything, whereas in 7.3 I had to dump with 7.2's dumpall and 
then tweak the file by hand a fair bit to get it to go into 7.3.

With 7.4 I'm finding upgrading to be easier.  I'll likely upgrade out 
production servers to 7.4.0 when it comes out and wind up skipping 7.3 
altogether.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] NOTICE: CREATE TABLE will create implicit triggers for foreign-key

[Gaetano Mendola]

Peter Eisentraut wrote:
> Regarding the NOTICE
>
> CREATE TABLE will create implicit triggers for foreign-key checks
>
> Does anyone care?
I don't care but is a way for a beginner to understand that behind
a foreign key there is a TRIGGER that have not a 0 cost.
> The other "helpful" notices about sequences for serial columns and 
indexes
> for unique constraints have some merit, because they inform the user
> objects that the user might be interested in are going to be created as a
> side effect.  But there's nothing that a user can reasonably do with an
> implicit trigger for a foreign-key check.  ISTM that this notice is about
> as interesting as the information that CREATE VIEW will create an 
implicit
> rule.

I think that this is a particular implementation of Postgres translate a
View on a Rule, am I wrong ?
BTW shall be nice have this kind of "NOTICE", that may be is better call
"INTERNALS", and switchable off.
Regards
Gaetano Mendola
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Wednesday beta postponed till Thursday

[scott.marlowe]

On Tue, 30 Sep 2003, Tom Lane wrote:

> It seems some junior electrician in Panama pulled the wrong circuit
> breaker ... and then the mail.postgresql.org server spent an
> unreasonable number of hours fsck'ing.  (Why is Marc a FreeBSD fan
> anyway?  Don't ask me, I work for Red Hat.)  Anyhow, due to the loss
> of project communications for today, it seems best to put off tomorrow's
> intended 7.4beta4 release for a day.  We'll plan Thursday instead.

Hey Jim!?  What does this big red switch do?
@#$% NO CARRIER


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Thoughts on maintaining 7.3

[Marc G. Fournier]

On Wed, 1 Oct 2003, Robert Treat wrote:

> Maybe I've mis-read Joshua's intentions, but I got the impression that
> this 7.3 maintainer would follow the patches list and backport patches
> whenever possible. This way folks coding for 7.4/7.5 can stay focused on
> that, but folks who can't upgrade to 7.4 for whatever reason can still
> get some features / improvements.

The problem, I think (and please note that I'm not against it, just
playing major devil's advocate here) is that there have always been some
major fundamental coding changes between releases that there are very few
patches that are "back-patchable" without having to do some heavy
re-writes ...

> Several linux distros already do this for many packages, and personally
> I've always been surprised that, given postgresql's major release
> upgrade issues, that no commercial company has stepped in to offer this
> in the past. I think what Joshua is wondering is how much cooperation
> would he get from the community if he was willing to donate these
> efforts back into project.

Using Linux/FreeBS/Insert OS Here as an example is like comparing apples
to oranges ... take FreeBSD as an example, since I know it ... 5.x has had
some *major* re-writes to the kernel done to it, getting rid of 'the Giant
Lock' that SMP in 4.x uses ... those changes are not back-patchable, since
then you'd have 5.x ... there are alot of changes to the 5.x kernel that
rely on those changes, and are therefore not *easily* back-patchable ...

Now, userland software is a totally different case, since they are rarely
"tied" to the kernel itself ...

Think of PostgreSQL as the kernel, not as the distro ... how many changes
from one kernel release ae easily patched into an older one, without
having to take alot of other baggage back with it ... ?

> I personally think it's a good idea for *someone* to do this, but I'll
> leave it to core to decide if they want to put the projects stamp of
> approval on it for any official community release.

I don't believe anyone would work against this, nor could I imagine that
anyone would think it was "a bad idea", I'm just curious as to how
possible it is to do ...


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[HACKERS] Patch for allowing multiple -t options for pg_dump

[Andreas Joseph Krogh]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

This si my first look at the pg-code, so it may not comply with the 
coding-standards. I haven't coded in C for a while either, so if someone 
finds a better way to implement this, go ahead, but this patch works for me 
with 7.4beta3.

http://home.officenet.no/~andreak/pg_dump.c.diff

comments are welcome.

If it's ok, I'll remove my debuging statements and provide a cleaner patch.

- -- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Managing Director, Senior Software Developer
OfficeNet AS

- - Writing software is more fun than working.

gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQE/et34UopImDh2gfQRAqtBAKCcrJ3mlN5G6Jp3h3kz2G8zMlc2xgCeLmOv
844vwdmhcU4/VLjugW+ISF8=
=/hYF
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] NOTICE: CREATE TABLE will create implicit triggers for foreign-key checks

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> Regarding the NOTICE
> CREATE TABLE will create implicit triggers for foreign-key checks
> Does anyone care?

I was thinking just the other day that it seemed to be useless clutter.

> The other "helpful" notices about sequences for serial columns and indexes
> for unique constraints have some merit, because they inform the user
> objects that the user might be interested in are going to be created as a
> side effect.

More to the point, they give the names of the automatically-created
objects.  If the FK trigger creation notice included the names of the
triggers then it might have nonzero value.  But I'm not sure that people
need to care anymore about the names of the triggers, since there's no
longer much reason to manipulate those triggers.  Nowadays you'd
manipulate the FK constraint itself, instead.

I'm in favor of dropping it...

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Thoughts on maintaining 7.3

[Tom Lane]

"Marc G. Fournier" <[EMAIL PROTECTED]> writes:
> On Tue, 30 Sep 2003, Joshua D. Drake wrote:
>> Of course the theory being that we backport "some" features and fix
>> any bugs that we find?

> Not saying that if someone submit'd patches to v7.3, they wouldn't get
> applied ... only that, to date, the work/effort has been greater then the
> overall benefit, and nobody has step'd up to the plate to do it ...

The idea of backporting features scares me; I really doubt that you can
get enough beta-testing on a back branch to be confident that you
haven't broken anything with a feature addition.  In any case you'd be
quite limited in what you could do without forcing an initdb.

Another issue is that people expect dot-releases to be absolutely rock
solid.  If you start introducing new features then you considerably
increase the risk of introducing new bugs.  (I'm still embarrassed about
7.3.3's failure-to-start bug...)

Our past practice has been to back-port only bug fixes, and only
critical or low-risk ones at that.  I think this could be done in a more
thorough fashion, and it could be continued longer than we've done in
the past, but you shouldn't set the scope of the maintenance effort any
wider than that.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Thoughts on maintaining 7.3

[Neil Conway]

On Wed, 2003-10-01 at 09:14, Robert Treat wrote:
> Maybe I've mis-read Joshua's intentions, but I got the impression that
> this 7.3 maintainer would follow the patches list and backport patches
> whenever possible. This way folks coding for 7.4/7.5 can stay focused on
> that, but folks who can't upgrade to 7.4 for whatever reason can still
> get some features / improvements.

I don't think there's a need for a formalized "7.3 maintainer" -- if
individuals would like to see particular fixes backported to 7.3, they
can read pgsql-patches and post backported patches themselves. If
someone wants to go ahead and do that, I wouldn't complain. (Similarly,
if there is enough demand for a commercial company to do something
similar for their customers, that might also be a good idea).

However, I think it's a bad idea to backport any features into older
releases. The reason 7.3.x is really stable is precisely that it has had
a lot of testing and bugfixing work done, but no new features.
Furthermore, adding more features to 7.3.x reduces the incentive to
upgrade to 7.4, worsening the support problem: the more people using old
releases, the more demand there will be for backported features, leading
to more people using 7.3, leading to more demand for ...

(FWIW, I think that any energy we might spend on a 7.3 maintainer would
be better directed at improving the upgrade story...)

-Neil



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] NOTICE: CREATE TABLE will create implicit triggers

[Stephan Szabo]

On Wed, 1 Oct 2003, Peter Eisentraut wrote:

> Regarding the NOTICE
>
> CREATE TABLE will create implicit triggers for foreign-key checks
>
> Does anyone care?

Probably not anymore. It doesn't give names (as Tom noticed), but at least
it gave a starting point to look for them back when you still had to
interact with them to do things like drop the constraint.  I don't think
any of that really applies any longer though.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] more i18n/l10n issues

[Tom Lane]

Peter Eisentraut <[EMAIL PROTECTED]> writes:
> [ some questions about --help-config ]

I got this reply from Fernando Nasser of Red Hat.  I suggested he should
post it for himself, but since he hasn't yet...

regards, tom lane

> --- Forwarded Message
> 
> Date:Tue, 30 Sep 2003 11:24:50 +0200 (CEST)
> From:Peter Eisentraut <[EMAIL PROTECTED]>
(...)
> 
> OK, but does this tool actually need all of the following features:
> 
> -G -- do not group by category
> both -m and -M -- machine-readable output with and without header
> human-readable output
> 
 > Were some of these just added for "completeness"?  With what rationale?
 >


-m and -M

The utility we designed and possibly any other that wants to process the 
output will find it easier to process a more regularly formed output 
instead of one that was embellished for human viewing.  The reason for 
having one with and one without headings is that in some circumstances 
the utility designer may want to use the headers to make her/his table 
headers and others may prefer to have pre-defined table headers.  The 
guy who wrote our window used -M while I would have liked him to use -m 
(but -M was faster to do and he was on a hurry).

-G

The -G (using the Unix convention of negating things with capital 
letters) is what we use.  This option is probably what will be used for 
generating the postgresql.conf default file automatically.
As we were not adding a facility for our use but for other tool 
developers as well and we thought that some may want to process it in 
different ways we made it an option.  We don't object making it a side 
effect of using -m or -M.



> Also, --help-config 'foo' outputs all parameters matching 'foo' somewhere
> in the string, not only 'foo'.  I think that is a misdesign.
> 

It works like locate (or slocate). I believe some other Unix utilities 
do the same.  Unix commands are mostly 'misdesigned' I admit.

I had a '-re' which accepted a regular expression. With that re-added 
one could make the --help-config 'foo' works like Peter wants.  But I 
would put that for a vote: I wouldn't be surprised if Peter were in a 
minority on this issue.


>>postgresql.conf.  The "long" descriptions were what the GUI tool wants.
> 

Tom, actually the tool (as I believe from previous experience with 
command line help facilities) needs both.

The short description shows up in summaries where you have a list of 
options and not much space.  The long description shows up in specific 
help commands, as tooltips etc., whenever the user wants and you can 
provide more information.

> 
> Most parameters don't have long descriptions, so that doesn't seem right.
> 

When the meaning is obvious there is no need to re-state it.  There may 
be cases where a better description should be written and it is only 
empty because nobody yet knows exactly what it does, or could not come 
up with a proper description (whenever a description was available in 
the documentation the field was filled).


> Also, in many cases where there is a long description, it was copied out
> of the documentation, with the short description being the first sentence
> and the long description being the rest.  The result is that in some cases
> the long description doesn't make sense in isolation.  I would like that
> to be clarified.
> 

This is a GNU trick to avoid repeating the same text (from the short 
description) in the long description.  I believe it is from Richard 
Stallman's time.  It is used in several GNU tools, for instance the GNU 
GDB debugger (which uses just one field and makes the first sentence or 
line the short description). The idea is that the long description is 
formed by the concatenation of the two.

-- 
Fernando Nasser
Red Hat Canada Ltd. E-Mail:  [EMAIL PROTECTED]
2323 Yonge Street, Suite #300
Toronto, Ontario   M4P 2C9

--- End of Forwarded Message


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] Index/Function organized table layout

[Alvaro Herrera]

On Tue, Sep 30, 2003 at 11:31:26PM -0700, James Rogers wrote:

> The problem: My working set is typically several million rows (and growing)
> at any one time, which has a tendency to thrash the buffers mercilessly.
> Records are inserted in an order that does not reflect typical retrieval
> such that a typical query has to hit many, many blocks to collect all the
> rows in a given range query.  CLUSTER isn't cutting it (more below).
> Definitely sub-optimal.

I have a situation that is very similar to yours, and my problems are
very similar to yours.  I hope some of your ideas are implementable in
some way, because I think they would solve some of my problems too.

> 1.) B-tree organized tables.  The primary key index also contains the
> entire row.

I think this is called a clustered index on some other database systems.
Basically you want to replace the content of the btree item with the
whole tuple, instead of the pointer to the heap element which contains
the tuple.  One thing to keep in mind is that index tuples are limited
to BLCKSZ/3 IIRC, so this limits the size of tuples that can be put in
such a "table".  TOASTing may help alleviate this problem.

As for other indexes, I'm not sure why you say this precludes the use of
other indexes.  The only thing they have to do is keep pointers to index
elements, instead of heap elements.  Doesn't sound impossible to me.

Another thing to keep in mind:  L&Y requires unique keys.  In the
current btree implementation this is worked around using the
pointers-to-heap (ctid?) to differentiate items that have the same key.
If you use a clustered index you won't have this pointer; maybe it will
be required that this index is marked UNIQUE.  This may not be a too
onerous restriction, given that the index is a primary key after all.


I don't have anything to say about 2).

-- 
Alvaro Herrera ()
"No deja de ser humillante para una persona de ingenio saber
que no hay tonto que no le pueda enseñar algo." (Jean B. Say)

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] Index/Function organized table layout

[Tom Lane]

James Rogers <[EMAIL PROTECTED]> writes:
> Now, I've actually hacked commercial MVCC engines back in the day, and am
> comfortable playing around in database internals.  I have an "itch to
> scratch" for improving the scalability of Really Large Tables by explicitly
> allowing control of table layouts as an optional property of the tables.  I
> would like some feedback as to whether this is practical given the current
> architecture; it appears that it is, but I'm not intimately familiar with it

I think you'd need to do some basic architectural work first.  Right now
we have a clean API for index access methods, but there is no comparable
abstraction layer for heaps (tables).  It'd probably be necessary to
create such a layer in order to allow different heap organizations to be
supported.  Another point of confusion is that heaps and indexes are
rigidly distinct.  Perhaps some heaps could be considered to be indexes
as well, in order to support your idea of b-tree-organized tables.
Doing that would undoubtedly break a few places though.

> Both of these things really are attempts to address the same basic problem,
> which is optimizing the number of buffers a given query uses by making the
> tables layout reflect typical queries.

Hm, are you sure that smarter buffer management wouldn't serve the
purpose?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Thoughts on maintaining 7.3

[Robert Treat]

On Wed, 2003-10-01 at 10:49, Neil Conway wrote:
> On Wed, 2003-10-01 at 09:14, Robert Treat wrote:
> > Maybe I've mis-read Joshua's intentions, but I got the impression that
> > this 7.3 maintainer would follow the patches list and backport patches
> > whenever possible. This way folks coding for 7.4/7.5 can stay focused on
> > that, but folks who can't upgrade to 7.4 for whatever reason can still
> > get some features / improvements.
> 
> I don't think there's a need for a formalized "7.3 maintainer" -- if
> individuals would like to see particular fixes backported to 7.3, they
> can read pgsql-patches and post backported patches themselves. If
> someone wants to go ahead and do that, I wouldn't complain. (Similarly,
> if there is enough demand for a commercial company to do something
> similar for their customers, that might also be a good idea).

ok

> 
> However, I think it's a bad idea to backport any features into older
> releases. The reason 7.3.x is really stable is precisely that it has had
> a lot of testing and bugfixing work done, but no new features.

eh.. i could see some things, like tsearch2 or pg_autovacuum, which
afaik are almost if not completely compatible with 7.3, which will not
get back ported. Also fixes in some of the extra tools like psql could
be very doable, I know I had a custom psql for 7.2 that back patched the
\timing option and some of the pager fixes. now, weather that could be
done with stuff closer to core, i don't know...

btw personally i'm fine with these things not being packpatched, though
if someone came out with a 7.3 pg_autovacuum rpm, or a 7.3 psql rpm, i'm
sure a lot of people would use it. 

> Furthermore, adding more features to 7.3.x reduces the incentive to
> upgrade to 7.4, worsening the support problem: the more people using old
> releases, the more demand there will be for backported features, leading
> to more people using 7.3, leading to more demand for ...
> 
> (FWIW, I think that any energy we might spend on a 7.3 maintainer would
> be better directed at improving the upgrade story...)
> 

mmm. in place upgrade  

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Thoughts on maintaining 7.3

[Andrew Sullivan]

On Tue, Sep 30, 2003 at 09:37:26AM -0700, Joshua D. Drake wrote:
> 
>  Of course the theory being that we backport "some" features and fix 
> any bugs that
> we find?

I would argue _very strongly_ against backporting features.

The backporting of features into the Linux kernel is an extremely
good analogy in this case.  Someone gets the clever idea that this or
that feature from 2.1/2.3/2.5 is desperately needed in 2.0/2.2/2.4
and merrily goes about adding all sorts of new cruft to the so-called
stable release.  As a result, we have plenty of examples of massive
filesystem corruption, modules that used to work and just plain don't
any more, sudden surprise hardware incompatibilites, &c.  All too
frequently releases in the "stable" series are one right atop the
other.  What's worse, all these additional features are bound up with
the important remote-root-type patches that make it into later
releases of the kernel.  As a result, it's a lot of work to compile a
known-safe and known-clean kernel for use on one's own machines. 

Patching an older release to fix critical, data-mangling bugs is one
thing.  But if people want the latest nifty feature backported to an
old release, let 'em pay the developer to do it in their private
source tree, and not force on the rest of us the job of sorting out
what crucial patches we need to apply to our old, pristine source of
PostgreSQL 7.3.4.  If you're really going to trust your database
software, you do not allow new features to be added after having
carefully teated all your applications against the system.

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

> eh.. i could see some things, like tsearch2 or pg_autovacuum, which
> afaik are almost if not completely compatible with 7.3, which will not
> get back ported. Also fixes in some of the extra tools like psql could
> be very doable, I know I had a custom psql for 7.2 that back patched the
> \timing option and some of the pager fixes. now, weather that could be
> done with stuff closer to core, i don't know...

Sure but businesses don't like to upgrade unless they have too. If we 
really want to attract more business to using PostgreSQL then they need
to feel like they don't have to upgrade every 12 months. Upgrading is 
expensive and it rarely goes as smoothly as a dump/restore.

> > Furthermore, adding more features to 7.3.x reduces the incentive to
> > upgrade to 7.4, worsening the support problem: the more people using old
> > releases, the more demand there will be for backported features, leading
> > to more people using 7.3, leading to more demand for ...

I am considering a time limited type thing. Not open ended. Something like 
18 or 24 months (max) from release of the new version. You can't expect
business to consider that timeframe during the development of the new 
release. They want to see the new release in action for a period of time.
They also want time to play with the new release without sacrificing 
support for the previous release.

> mmm. in place upgrade  

In reality in place upgrade will never work. Sure we can build a script 
that will deal with PostgreSQL itself, but not user defined data types, 
operators, functions etc... Those are all things that need stable time to 
migrate and test.

Sincerely,

Joshua Drake


> 
> Robert Treat
> 

-- 
Co-Founder
Command Prompt, Inc.
The wheel's spinning but the hamster's dead


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

> I would argue _very strongly_ against backporting features.

For massive features sure but an example of a feature that works
very well and easily with 7.3 is the preloading of libs.

Sincerely,

Joshua Drake

-- 
Co-Founder
Command Prompt, Inc.
The wheel's spinning but the hamster's dead


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] Thoughts on maintaining 7.3

[Robert Treat]

On Wed, 2003-10-01 at 09:41, Marc G. Fournier wrote:
> On Wed, 1 Oct 2003, Robert Treat wrote:
> 
> > Several linux distros already do this for many packages, and personally
> > I've always been surprised that, given postgresql's major release
> > upgrade issues, that no commercial company has stepped in to offer this
> > in the past. I think what Joshua is wondering is how much cooperation
> > would he get from the community if he was willing to donate these
> > efforts back into project.
> 
> Using Linux/FreeBS/Insert OS Here as an example is like comparing apples
> to oranges ... take FreeBSD as an example, since I know it ... 5.x has had
> some *major* re-writes to the kernel done to it, getting rid of 'the Giant
> Lock' that SMP in 4.x uses ... those changes are not back-patchable, since
> then you'd have 5.x ... there are alot of changes to the 5.x kernel that
> rely on those changes, and are therefore not *easily* back-patchable ...
> 
> Now, userland software is a totally different case, since they are rarely
> "tied" to the kernel itself ...
> 

you missed my point. some distro's (red hat, suse, mandrake, etc..)
backpatch into their distributed packages separate of the packages
original source tree. this is great for folks who may want/need a new
change, but can't upgrade to latest source for some reason. 

> Think of PostgreSQL as the kernel, not as the distro ... how many changes
> from one kernel release ae easily patched into an older one, without
> having to take alot of other baggage back with it ... ?

I wasn't thinking of PostgreSQL as a distro, but actually I think that
view is somewhat valid, since there are enough add ons to core that one
could modify without having to make huge changes. 

As Tom pointed out, with the restriction of not being able to initdb,
you're probably pretty limited on what you can push back, but I think
there's still enough there that folks might want to look at it. (The
recent bugs in pltcl handling dropped columns come to mind, though maybe
Tom backpatched those? Cant recall)


Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Index/Function organized table layout

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> As for other indexes, I'm not sure why you say this precludes the use of
> other indexes.  The only thing they have to do is keep pointers to index
> elements, instead of heap elements.  Doesn't sound impossible to me.

However, btree feels free to move index entries around while inserting
other entries.  I'm not sure that you could usefully use "ctid" as an
identifier for tuples in a btree-organized heap.  This will break more
things than just other indexes :-( ... UPDATE chaining for one example.

> Another thing to keep in mind:  L&Y requires unique keys.  In the
> current btree implementation this is worked around using the
> pointers-to-heap (ctid?) to differentiate items that have the same key.
> If you use a clustered index you won't have this pointer; maybe it will
> be required that this index is marked UNIQUE.

IIRC this assumption is really only used when re-finding the parent
downlink after a page split, and so we were able to get rid of it by
looking for the downlink by child block number, without using the key at
all.  So that part doesn't bother me.  The mutability of index ctids
seems like a much worse problem.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[HACKERS]

[Yanhong.Li-1]

unsubscribe


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Index/Function organized table layout

[Alvaro Herrera]

On Wed, Oct 01, 2003 at 11:37:38AM -0400, Tom Lane wrote:
> James Rogers <[EMAIL PROTECTED]> writes:

> > Both of these things really are attempts to address the same basic problem,
> > which is optimizing the number of buffers a given query uses by making the
> > tables layout reflect typical queries.
> 
> Hm, are you sure that smarter buffer management wouldn't serve the
> purpose?

It doesn't help when there a lot of access locality in searching.  In my
case I want to select some thousands of records that were inserted very
apart from each other, but are logically very near.  Having this
pseudoheap that is ordered by definition helps very much with the
selection; the current heap requires me to bring to buffers lots of
uninteresting tuples, whichever buffer management algorithm is used,
because they are in the same page as interesting tuples.

-- 
Alvaro Herrera ()
"Linux transformó mi computadora, de una `máquina para hacer cosas',
en un aparato realmente entretenido, sobre el cual cada día aprendo
algo nuevo" (Jaime Salinas)

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Thoughts on maintaining 7.3

[Andrew Sullivan]

On Wed, Oct 01, 2003 at 08:49:51AM -0700, Joshua D. Drake wrote:
> > I would argue _very strongly_ against backporting features.
> 
> For massive features sure but an example of a feature that works
> very well and easily with 7.3 is the preloading of libs.

Then let people patch the stable releases themselves, or pay
companies to produce such mini-branches (and thereby pay the cost of
the necessary testing, &c.).

How does one know in advance which set of "working well and easily"
features can be back ported and be sure not to break on some release
of IRIX, Solaris, AIX, or SCO?  Those are not platforms that get the
kind of kicking that Linux and FreeBSD do, but people are still
relying on the dot releases not to break anything on those platforms. 
I think that Postgres has a tradition that, when a release is stable,
it's _stable, man_ -- a tradition that other software (commercial or not)
should emulate.  I'd hate to see that go overboard in an attempt to
add features to the main releases.

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Afilias CanadaToronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Thoughts on maintaining 7.3

[Neil Conway]

On Wed, 2003-10-01 at 11:48, Joshua D. Drake wrote:
> Sure but businesses don't like to upgrade unless they have too.

Granted, but maintaining old releases doesn't come at zero cost. It may
benefit some users, but the relevant question is whether that benefit is
worth the cost. The time someone spends backpatching changes into old
releases (and thoroughly testing those changes, and fixing the
regressions those changes cause) is presumably time that would otherwise
be spent improving the latest release of PostgreSQL.

So when the bugfix is important, has been well-tested, and is unlikely
to cause regressions, backpatching the change to previous stable
releases is a good idea. When this isn't the case (and even more so if
it's a feature and not a bugfix), I don't think it justifies the cost
(and the risk of destabilization) for most users.

In summary, I think the status quo is basically okay. Perhaps we should
backpatch a few more things, but we're basically in the right ballpark.

> In reality in place upgrade will never work.

Perhaps not, but the upgrade story can certainly be made more palatable.
I think that's the actual problem here -- rather than skating around it
by making it less necessary to do the upgrade in the first place, I
think our time is better spent making upgrades as painless as possible.
Just IMHO, of course (especially since I'm not particularly interested
in doing the work on the upgrade process myself).

-Neil



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[HACKERS] buffer manager

[monu_indian]

Hi
I want to change the buffer policy & page structure of pgsql from which file I should 
start?Is any document which describe the code of buffer manager og pgsql?

Get Your Private, Free E-mail from Indiatimes at http://email.indiatimes.com

 Buy The Best In BOOKS at http://www.bestsellers.indiatimes.com

Bid for for Air Tickets @ Re.1 on Air Sahara Flights. Just log on to 
http://airsahara.indiatimes.com and Bid Now!


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

With 7.4 I'm finding upgrading to be easier.  I'll likely upgrade out 
production servers to 7.4.0 when it comes out and wind up skipping 7.3 
altogether.
 

Sure but I talking about people who are running 7.3 and are happy with 
it. The reality is that for probably 95% of the people
out there , there is no reason for 7.4. When you have existing system 
that works... why upgrade? That is one of the benefits
of Open Source stuff, we no longer get force into un-needed upgrade cycles.

We use PostgreSQL for everything, and I don't have any inclination to 
upgrade to 7.4 except that it is 7.4. I only have two
customers that will see any real benefit from going to 7.4. The rest are 
going to stay on 7.3 because they don't want:

A. The downtime
B. Unknown or unexpected problems
C. A brand new database
D. Migration costs
When you deal with the systems I do, the cost to a customer to migrate 
to 7.4 would be in the minimum of 10,000-20,000 dollars.
They start to ask why were upgrading with those numbers.

That is not to say that 7.4 is not worth it from a technical sense but 
for my customers, "If it ain't broke, don't fix it" is a mantra and
the reality is that 7.3 is not broke in their minds. There is 
limitations pg_dump/pg_restore has some issues, having to reindex the 
database
(which 7.4 doesn't fix), vacuum (which 7.4 doesn't fix) but my customers 
accept them as that.

Your mileage may vary but I can only talk from my experience.

Sincerely,

Joshua D. Drake





--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
   (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

Maybe I've mis-read Joshua's intentions, but I got the impression that
this 7.3 maintainer would follow the patches list and backport patches
whenever possible. This way folks coding for 7.4/7.5 can stay focused on
that, but folks who can't upgrade to 7.4 for whatever reason can still
get some features / improvements. 
 

And bug fixes but yes that is accurate.

Sincerely,

Joshua Drake

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

I don't believe anyone would work against this, nor could I imagine that
anyone would think it was "a bad idea", I'm just curious as to how
possible it is to do ...
 

For most things probably not that possible. For things like:

Simple feature enhancements (preloading of libs)
Fixing pl/Language bugs (and making sure they still work on 7.3)
Buffer overflow fixes
Security problems (the fact that alter user/createuser with encrypted 
password ' will go into a .psqlhistory file is horrendous)
pg_dump/pg_restore enhancements

Would entirely be possible.

Sincerely,

Joshua rake




---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
 

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com
The most reliable support for the most reliable Open Source database.


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] ADD FOREIGN KEY

[Manfred Koizar]

On Tue, 30 Sep 2003 08:00:07 -0400, Christopher Browne
<[EMAIL PROTECTED]> wrote:
>I would be pretty "game" for a near-single-user-mode approach that
>would turn off some of the usual functionality that we knew we didn't
>need because the data source was an already-committed-and-FK-checked
>set of data.

Single user mode is a good idea, IMHO.  But it should only make sure
that there is not more than one user connected to the database (or to
the postmaster).  Everything else should depend on special GUC
variables that are only settable in single user mode:

db=> SET disable-fk-verification = true;
ERROR: "disable-fk-verification" can only be set in single user mode
db=> SET SINGLE USER MODE ON;
ERROR: permission denied
HINT: Must be superuser or owner of database "db".
db=> \c - dbo
You are now connected as new user "dbo".
db=> SET SINGLE USER MODE ON;
ERROR: cannot enter single user mode
HINT: You are not the only user connected to database "db".
-- after other users have logged out ...
db=> SET SINGLE USER MODE ON;
SET
db=> SET disable-fk-verification = true;
SET

Single user mode would also help in several cases where now a
standalone backend is required ...

Servus
 Manfred

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[HACKERS] query plan different for "SELECT ..." and "DECLARE CURSOR ..."?

[David Blasby]

I've been noticing query planning to be different for a cursor-based 
select and normal select.

For example, my query looks like this:

=# SELECT 

The query takes about 1/4 of a second.

But, for:

=# BEGIN;
=# DECLARE mycursor BINARY CURSOR FOR SELECT ;
=# FETCH ALL IN mycursor;
The same [SQL] query is being used, but this takes about 6 seconds (20* 
longer).

Using explain, I see that the planner chose a different plan.

Why does this sort of thing happen?  How do I stop it?

I've included the query plans below if you think a specific example is 
important - but i'm more looking for a generic answer.  Sorry for the 
complexity.

NOTE: these are are PostGIS queries (&& is GIST indexed).  The reason a 
binary cursor is being used is because I use the WKB (well known binary) 
geometry representation as the transit 'language'.

thanks for your help,
dave
"SELECT plan"

QUERY PLAN 

-
 Hash Join  (cost=1993.11..2462.50 rows=9 width=40) (actual 
time=77.69..169.96 rows=67 loops=1)
   Hash Cond: ("outer".lha_id = "inner".lha_id)
   ->  Subquery Scan b  (cost=1983.00..2443.08 rows=1839 width=16) 
(actual time=60.48..127.20 rows=67 loops=1)
 ->  Aggregate  (cost=1983.00..2443.08 rows=1839 width=16) 
(actual time=60.47..127.08 rows=67 loops=1)
   ->  Group  (cost=1983.00..2351.14 rows=18387 width=16) 
(actual time=59.68..115.57 rows=18387 loops=1)
 ->  Merge Join  (cost=1983.00..2305.17 rows=18387 
width=16) (actual time=59.67..93.81 rows=18387 loops=1)
   Merge Cond: ("outer".lha_id = 
"inner".lha_from_id)
   ->  Sort  (cost=8.77..8.99 rows=89 width=8) 
(actual time=0.44..0.48 rows=82 loops=1)
 Sort Key: p.lha_id
 ->  Seq Scan on lha_pop p 
(cost=0.00..5.89 rows=89 width=8) (actual time=0.03..0.15 rows=89 loops=1)
   ->  Sort  (cost=1974.23..2020.19 rows=18387 
width=8) (actual time=59.19..64.80 rows=18387 loops=1)
 Sort Key: s.lha_from_id
 ->  Seq Scan on msp_trip_summary s 
(cost=0.00..671.84 rows=18387 width=8) (actual time=1.70..31.31 
rows=18387 loops=1)
   Filter: (distance > 200)
   ->  Hash  (cost=10.11..10.11 rows=1 width=36) (actual 
time=15.71..15.71 rows=0 loops=1)
 ->  Seq Scan on lha_albers a  (cost=0.00..10.11 rows=1 
width=36) (actual time=1.06..15.54 rows=89 loops=1)
   Filter: (the_geom && 'SRID=-1;BOX3D(25 25 
0,190 190 0)'::geometry)
 Total runtime: 173.97 msec
(18 rows)

and the "DECLARE" plan:
 QUERY PLAN 


 Nested Loop  (cost=1983.00..2476.17 rows=9 width=40)
   Join Filter: ("outer".lha_id = "inner".lha_id)
   ->  Seq Scan on lha_albers a  (cost=0.00..10.11 rows=1 width=36)
 Filter: (the_geom && 'SRID=-1;BOX3D(25 25 0,190 
190 0)'::geometry)
   ->  Subquery Scan b  (cost=1983.00..2443.08 rows=1839 width=16)
 ->  Aggregate  (cost=1983.00..2443.08 rows=1839 width=16)
   ->  Group  (cost=1983.00..2351.14 rows=18387 width=16)
 ->  Merge Join  (cost=1983.00..2305.17 rows=18387 
width=16)
   Merge Cond: ("outer".lha_id = 
"inner".lha_from_id)
   ->  Sort  (cost=8.77..8.99 rows=89 width=8)
 Sort Key: p.lha_id
 ->  Seq Scan on lha_pop p 
(cost=0.00..5.89 rows=89 width=8)
   ->  Sort  (cost=1974.23..2020.19 rows=18387 
width=8)
 Sort Key: s.lha_from_id
 ->  Seq Scan on msp_trip_summary s 
(cost=0.00..671.84 rows=18387 width=8)
   Filter: (distance > 200)
 Total runtime: 0.41 msec
(17 rows)



tap=# explain DECLARE mycursor BINARY CURSOR FOR SELECT 
asbinary(force_collection(force_2d(the_geom)),'NDR'),LHA_ID::text from (
tap(# Select a.the_geom, a.lha_id, trips from 
lha_albers a,
tap(# (SELECT (s.lha_from_id) as lha_id, 
(sum(s.count)::float / max(p.population)::float * 100) as trips
tap(# from lha_pop p, msp_trip_summary s
tap(# where  p.lha_id = s.lha_from_id 
AND s.distance > 200 Group by s.lha_from_id) b
tap(# where a.lha_id = b.lha_id
tap(# ) AS TBL WHERE the_geom && setSRID('BOX3D(25 
25,190 190)'::BOX3D, -1 )
tap-# ;





---(end of broadcast)---
TIP 3: if posting/re

Re: [HACKERS] Thoughts on maintaining 7.3

[Andrew Dunstan]

Joshua D. Drake wrote:

For most things probably not that possible. For things like:

Simple feature enhancements (preloading of libs) 


How long is a piece of string? When does something stop being simple?

Fixing pl/Language bugs (and making sure they still work on 7.3)
Buffer overflow fixes 


Everyone seems to agree that bugs should be fixed.

Security problems (the fact that alter user/createuser with encrypted 
password ' will go into a .psqlhistory file is horrendous) 


you can avoid this in the create case by using createuser -P instead of 
psql. Or by using psql -c (although that might put stuff in your shell 
history ;-)
Maybe there's a good case for an alteruser counterpart to createuser.

pg_dump/pg_restore enhancements

Which ones? If it is things known to be broken being fixed that comes 
under the bug fix category.

cheers

andrew

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] Lost mails

[Darko Prenosil]

Two mails with updated translations for /src/backend/po/hr.po are lost.
First time I send clear po file, second tar.gz - no result.
Is something blocking mails with attachment ? I didn't receive notification
that mail is blocked or something like that.
Can I try to send it to some other address ?

Regards !


---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Thoughts on maintaining 7.3

[scott.marlowe]

On Wed, 1 Oct 2003, Joshua D. Drake wrote:

> 
> >With 7.4 I'm finding upgrading to be easier.  I'll likely upgrade out 
> >production servers to 7.4.0 when it comes out and wind up skipping 7.3 
> >altogether.
> >  
> >
> 
> Sure but I talking about people who are running 7.3 and are happy with 
> it. The reality is that for probably 95% of the people
> out there , there is no reason for 7.4. When you have existing system 
> that works... why upgrade? That is one of the benefits
> of Open Source stuff, we no longer get force into un-needed upgrade cycles.

Agreed, we've been on 7.2 for a while now because it just works.  
The regex substring introduced in 7.3 was a pretty cool feature, for 
instance, that makes life easy.

> When you deal with the systems I do, the cost to a customer to migrate 
> to 7.4 would be in the minimum of 10,000-20,000 dollars.
> They start to ask why were upgrading with those numbers.

then maybe they would be willing to donate some small amount each ($500 or 
so) to pay for backporting issues.  Since mostly what I'd want on an older 
version would be bug / security fixes, that $500 should go a long way 
towards backporting.

> That is not to say that 7.4 is not worth it from a technical sense but 
> for my customers, "If it ain't broke, don't fix it" is a mantra and
> the reality is that 7.3 is not broke in their minds. There is 
> limitations pg_dump/pg_restore has some issues, having to reindex the 
> database
> (which 7.4 doesn't fix), vacuum (which 7.4 doesn't fix) but my customers 
> accept them as that.

I was under the imporession that 7.4 removed the need to reindex caused by 
monotonically increasing index keys, no?

> Your mileage may vary but I can only talk from my experience.

Yeah, I would rather have had more back porting to 7.2 because there were 
tons of little improvements form 7.2 to 7.3 I could have used while 
waiting for 7.4's improved pg_dumpall to come along.

Cheers:-)


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] ADD FOREIGN KEY

[Christopher Browne]

[EMAIL PROTECTED] (Manfred Koizar) writes:
> On Tue, 30 Sep 2003 08:00:07 -0400, Christopher Browne
> <[EMAIL PROTECTED]> wrote:
>>I would be pretty "game" for a near-single-user-mode approach that
>>would turn off some of the usual functionality that we knew we didn't
>>need because the data source was an already-committed-and-FK-checked
>>set of data.
>
> Single user mode is a good idea, IMHO.  But it should only make sure
> that there is not more than one user connected to the database (or
> to the postmaster).

Well, there already exists an honest-to-goodness single-user mode,
where you start a postmaster directly.  

This is the way that you need to connect to PG in order to be able to
regenerate indexes for any "nailed" system tables.

If I could be certain that a "pg_fast_recovery" program could run
several times faster than the existing approach of "psql <
recoveryfile.sql", then it might well be worthwhile to have something
invoked something like the following:

% zcat /backups/latest_backup.gz | postmaster -D $PGDATA -F -N 0 
--fast-recovery-off-ACID --log /tmp/recovery.log mydb

-N 0 means that there won't even be as many as one user connected to
the database.

I would, given an ideal world, prefer to be able to have a connection
or two live during this to let me monitor the DB and even get an early
peek at the data.  But if I could save a few hours of recovery time,
it might be livable to lose that.
-- 
select 'cbbrowne' || '@' || 'libertyrms.info';

Christopher Browne
(416) 646 3304 x124 (land)

---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

then maybe they would be willing to donate some small amount each ($500 or 
so) to pay for backporting issues.  Since mostly what I'd want on an older 
version would be bug / security fixes, that $500 should go a long way 
towards backporting.
 

Sure.

I was under the imporession that 7.4 removed the need to reindex caused by 
monotonically increasing index keys, no?
 

Someone else brought that up. Maybe I am misunderstanding something but 
it was my understanding that 7.4 fixes alot of
the issues but one of the issues (index bloat) although improved is not 
entirely fixed and thus we would still need reindex?
Tom am I on crack?


Yeah, I would rather have had more back porting to 7.2 because there were 
tons of little improvements form 7.2 to 7.3 I could have used while 
waiting for 7.4's improved pg_dumpall to come along.
 

Well there ya go :)

Sincerely,

Joshua Drake




Cheers:-)
 

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org


---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] Thoughts on maintaining 7.3

[Alvaro Herrera]

On Wed, Oct 01, 2003 at 11:53:12AM -0700, Joshua D. Drake wrote:
> 
> >Eh?  In 7.4 you should not need to reindex.
>
> I thought tom was saying that the index bloat was "better" in 7.4 but it 
> was not gone... thus we would still need reindex yes?

The problem has been "corrected enough" for there to be no need to
reindex, AFAIK.

I think what Tom is concerned about is that this hasn't been tested
enough with big datasets.  Also there a little loss of index pages but
it's much less (orders of magnitude, I think) than what was before.
This is because the index won't shrink "vertically".

-- 
Alvaro Herrera ()
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Thoughts on maintaining 7.3

[Robert Treat]

On Wed, 2003-10-01 at 15:31, Joshua D. Drake wrote:
> 
> >then maybe they would be willing to donate some small amount each ($500 or 
> >so) to pay for backporting issues.  Since mostly what I'd want on an older 
> >version would be bug / security fixes, that $500 should go a long way 
> >towards backporting.
> >  
> >
> Sure.
> 

and the question as i thought was being discussed (or should be
discussed) was what is the level of interest in having this work kept in
the community cvs tree vs. someone else's quasi-forked branch... 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] 7.4 status

[Patrick Welche]

On Mon, Sep 29, 2003 at 11:50:23PM +0200, Peter Eisentraut wrote:
> Tom Lane writes:
> 
> > At the very least we need to set a strings freeze soon, so the
> > translators can catch up.  Peter, are you getting close to done with the
> > message revisions you've been making?
> 
> Yes, I think we're ready for a string freeze.  Alvaro, do you have
> anything you still want to submit in that area?

Does this count a string change? :)

Patrick
? psql
Index: help.c
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/psql/help.c,v
retrieving revision 1.80
diff -u -r1.80 help.c
--- help.c  14 Sep 2003 22:37:13 -  1.80
+++ help.c  1 Oct 2003 19:58:15 -
@@ -234,8 +234,8 @@
ON(pset.popt.topt.format == PRINT_HTML));
fprintf(output, _("  \\pset NAME [VALUE]\n"
  " set table output option\n"
- " (NAME := 
{format|border|expanded|fieldsep|null|recordsep|\n"
- " tuples_only|title|tableattr|pager})\n"));
+ " (NAME := 
{format|border|expanded|fieldsep|footer|null|\n"
+ " 
recordsep|tuples_only|title|tableattr|pager})\n"));
fprintf(output, _("  \\t show only rows (currently %s)\n"),
ON(pset.popt.topt.tuples_only));
fprintf(output, _("  \\T [STRING]set HTML  tag attributes, or unset 
if none\n"));

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[HACKERS] FreeSpaceMap hashtable out of memory

[Maksim Likharev]

Hi,
Using PG under Cygwin we having following error message during INSERT
INTO
"FreeSpaceMap hashtable out of memory".

What does that mean?
And if for a moment step out of knowledge 'PG under Cygwin', what in
general 
this message is about and more important how to fix it?

Thank you.

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

and the question as i thought was being discussed (or should be
discussed) was what is the level of interest in having this work kept in
the community cvs tree vs. someone else's quasi-forked branch... 
 

It is my thinking that regardless of commercial backing that the 
PostgreSQL project as a whole would gain better validity
within the commercial world if we maintained releases longer.

It is really irrelevant whether somebody pays me or you 500.00 buck to 
make a patch and submit it to the tree. What is
relevant IMHO is that the community is backing a release for longer than 
12-18 months.

Yes a commercial company could just pick it up and say ... hey we will 
support it for x (Mammoth 7.3.4 is supported until 2005 for example)
but I was more looking at this from an overall community perspective.

Sincerely,

Joshua D. Drake




Robert Treat
 

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[HACKERS] initdb

[Andrew Dunstan]

The following code is in initdb.sh:

exit_nicely(){
   stty echo > /dev/null 2>&1
   echo 1>&2
   echo "$CMDNAME: failed" 1>&2
   if [ "$noclean" != yes ]; then
   if [ "$made_new_pgdata" = yes ]; then
   echo "$CMDNAME: removing data directory \"$PGDATA\"" 1>&2
   rm -rf "$PGDATA" || echo "$CMDNAME: failed" 1>&2
   fi
   else
   echo "$CMDNAME: data directory \"$PGDATA\" not removed at user's 
request" 1>&2
   fi
   exit 1
}

So if the data directory previously existed and was empty, we don't 
clean it out on error, even if we didn't use the noclean flag. Is this 
intended behaviour or a bug? (If a bug it's trivially easy to fix.)

cheers

andrew

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Lost mails

[scott.marlowe]

On Wed, 1 Oct 2003, Darko Prenosil wrote:

> Two mails with updated translations for /src/backend/po/hr.po are lost.
> First time I send clear po file, second tar.gz - no result.
> Is something blocking mails with attachment ? I didn't receive notification
> that mail is blocked or something like that.
> Can I try to send it to some other address ?

Just send it again, yesterday a junior eletrical tech in Panama turned off 
the mail server for the lists for a little while.


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] initdb

[Peter Eisentraut]

Andrew Dunstan writes:

> So if the data directory previously existed and was empty, we don't
> clean it out on error, even if we didn't use the noclean flag. Is this
> intended behaviour or a bug? (If a bug it's trivially easy to fix.)

If the data directory already existed, we don't want to delete it.  We
should possibly delete the content, though.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Tue, 30 Sep 2003, Tom Lane wrote:
>> I think I can implement it and it will act as stated in my proposal.
>> Whether people like the proposed behavior is the big question in my
>> mind.

> I think it's more reasonable than the current behavior or any of
> the others we've hit along the way, and we have to pretty much choose
> now if we want to change it for 7.4.

I've committed the attached patch.  One thing I wanted to double-check
with you is that the SELECT FOR UPDATES done in the noaction cases are
being correctly handled.  I think it is correct to do them with the
current snapshot rather than the start-of-transaction snap; do you
agree?  Also, I did not propagate the crosscheck support into
heap_mark4update, meaning that these SELECT FOR UPDATEs won't complain
if they find a row that was inserted later than the start of the
serializable transaction.  I'm not totally sure if they should or not;
what do you think?

regards, tom lane

*** src/backend/access/heap/heapam.c.orig   Thu Sep 25 10:22:54 2003
--- src/backend/access/heap/heapam.cWed Oct  1 16:02:27 2003
***
*** 1207,1220 
   * NB: do not call this directly unless you are prepared to deal with
   * concurrent-update conditions.  Use simple_heap_delete instead.
   *
   * Normal, successful return value is HeapTupleMayBeUpdated, which
   * actually means we did delete it.  Failure return codes are
   * HeapTupleSelfUpdated, HeapTupleUpdated, or HeapTupleBeingUpdated
!  * (the last only possible if wait == false).
   */
  int
  heap_delete(Relation relation, ItemPointer tid,
!   ItemPointer ctid, CommandId cid, bool wait)
  {
ItemId  lp;
HeapTupleData tp;
--- 1207,1229 
   * NB: do not call this directly unless you are prepared to deal with
   * concurrent-update conditions.  Use simple_heap_delete instead.
   *
+  *relation - table to be modified
+  *tid - TID of tuple to be deleted
+  *ctid - output parameter, used only for failure case (see below)
+  *cid - delete command ID to use in verifying tuple visibility
+  *crosscheck - if not SnapshotAny, also check tuple against this
+  *wait - true if should wait for any conflicting update to commit/abort
+  *
   * Normal, successful return value is HeapTupleMayBeUpdated, which
   * actually means we did delete it.  Failure return codes are
   * HeapTupleSelfUpdated, HeapTupleUpdated, or HeapTupleBeingUpdated
!  * (the last only possible if wait == false).  On a failure return,
!  * *ctid is set to the ctid link of the target tuple (possibly a later
!  * version of the row).
   */
  int
  heap_delete(Relation relation, ItemPointer tid,
!   ItemPointer ctid, CommandId cid, Snapshot crosscheck, bool 
wait)
  {
ItemId  lp;
HeapTupleData tp;
***
*** 1240,1246 
tp.t_tableOid = relation->rd_id;
  
  l1:
!   result = HeapTupleSatisfiesUpdate(&tp, cid);
  
if (result == HeapTupleInvisible)
{
--- 1249,1255 
tp.t_tableOid = relation->rd_id;
  
  l1:
!   result = HeapTupleSatisfiesUpdate(tp.t_data, cid);
  
if (result == HeapTupleInvisible)
{
***
*** 1278,1283 
--- 1287,1300 
else
result = HeapTupleUpdated;
}
+ 
+   if (crosscheck != SnapshotAny && result == HeapTupleMayBeUpdated)
+   {
+   /* Perform additional check for serializable RI updates */
+   if (!HeapTupleSatisfiesSnapshot(tp.t_data, crosscheck))
+   result = HeapTupleUpdated;
+   }
+ 
if (result != HeapTupleMayBeUpdated)
{
Assert(result == HeapTupleSelfUpdated ||
***
*** 1378,1384 
  
result = heap_delete(relation, tid,
 &ctid,
!GetCurrentCommandId(),
 true /* wait for commit */);
switch (result)
{
--- 1395,1401 
  
result = heap_delete(relation, tid,
 &ctid,
!GetCurrentCommandId(), SnapshotAny,
 true /* wait for commit */);
switch (result)
{
***
*** 1407,1420 
   * NB: do not call this directly unless you are prepared to deal with
   * concurrent-update conditions.  Use simple_heap_update instead.
   *
   * Normal, successful return value is HeapTupleMayBeUpdated, which
   * actually means we *did* update it.  Failure return codes are
   * HeapTupleSelfUpdated, HeapTupleUpdated, or HeapTupleBeingUpdated
!  * (the last only possible if wait == false).
   */
  int
  heap_update(Relation relation, ItemPointer otid, HeapTuple newtup,
!

Re: [HACKERS] FreeSpaceMap hashtable out of memory

[Tom Lane]

"Maksim Likharev" <[EMAIL PROTECTED]> writes:
> Using PG under Cygwin we having following error message during INSERT
> INTO
> "FreeSpaceMap hashtable out of memory".

Hm, that's not supposed to happen.  Can you create a reproducible
example?

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] Lost mails

[Tom Lane]

"Darko Prenosil" <[EMAIL PROTECTED]> writes:
> Two mails with updated translations for /src/backend/po/hr.po are lost.
> First time I send clear po file, second tar.gz - no result.
> Is something blocking mails with attachment ?

How big were they --- over 40K?  If so, they're probably being held for
moderator approval.

> I didn't receive notification
> that mail is blocked or something like that.

You're supposed to; I know I always get a notification when I run into
one of the moderator filters.  But I've gotten the impression it doesn't
work for everybody.  You could talk to Marc about it if you want to try
to help debug the issue.

> Can I try to send it to some other address ?

pgsql-patches has a higher limit than the other lists, I think.  But
don't resend until you've confirmed that Marc doesn't have it in his
moderator queue.

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] Thoughts on maintaining 7.3

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> ... having to reindex the database (which 7.4 doesn't fix),

It's supposed to fix it.  What are you expecting not to be fixed?

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Stephan Szabo]

On Wed, 1 Oct 2003, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Tue, 30 Sep 2003, Tom Lane wrote:
> >> I think I can implement it and it will act as stated in my proposal.
> >> Whether people like the proposed behavior is the big question in my
> >> mind.
>
> > I think it's more reasonable than the current behavior or any of
> > the others we've hit along the way, and we have to pretty much choose
> > now if we want to change it for 7.4.
>
> I've committed the attached patch.  One thing I wanted to double-check
> with you is that the SELECT FOR UPDATES done in the noaction cases are
> being correctly handled.  I think it is correct to do them with the
> current snapshot rather than the start-of-transaction snap; do you
> agree?  Also, I did not propagate the crosscheck support into

I think the ones in the main functions need to be current snapshot.  I
think the one in ri_Check_Pk_Match doesn't need to be. That's there to see
if this same transaction has inserted a new row with the old value of the
updated/deleted pk row and the serializable snapshot should be fine.
Any conflicting attempts from another transaction should be waiting on our
completion due to the unique index I think.

> heap_mark4update, meaning that these SELECT FOR UPDATEs won't complain
> if they find a row that was inserted later than the start of the
> serializable transaction.  I'm not totally sure if they should or not;
> what do you think?

Well, I think that not doing so would only change the error from a
serialization error to a matching row exists error.  It might be a bit
surprising if you've just done a select yourself and seen that there were
no matching rows, but I'm not sure that it's a big deal as long as it
errors as appropriate.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Thoughts on maintaining 7.3

[Joshua D. Drake]

Hello,

  When I was reading hackers about the fixes you had made, it stated 
that the index bloat problems should be better. I took
that as meaning that although it won't be required nearly as often, we 
still may need to reindex occassionaly. It was later
pointed out to me that this may not be the case, to wit I responded: 
Tom, am I on crack?

Sincerely,

Joshua Drake

Tom Lane wrote:

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
 

... having to reindex the database (which 7.4 doesn't fix),
   

It's supposed to fix it.  What are you expecting not to be fixed?

			regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
 

--
Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC
Postgresql support, programming shared hosting and dedicated hosting.
+1-503-222-2783 - [EMAIL PROTECTED] - http://www.commandprompt.com
Editor-N-Chief - PostgreSQl.Org - http://www.postgresql.org


---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

[HACKERS] NOTICE vs WARNING resolution

[Peter Eisentraut]

Following up to the discussion a few weeks ago and in accordance with the
criteria developed there about how a message should be classified NOTICE
or WARNING, I have identified the following cases that ought to be
reclassified:

change WARNING to NOTICE:

table "%s" has no indexes  [during REINDEX]

change NOTICE to WARNING:

changing return type of function %s from "opaque" to xxx
INTERVAL(%d) precision reduced to maximum allowed, %d

I also thought that

copyObject() failed to produce an equal parse tree

should be made an elog() because it's an internal error.


Objections?

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] Lost mails

[Marc G. Fournier]

On Wed, 1 Oct 2003, Tom Lane wrote:

> "Darko Prenosil" <[EMAIL PROTECTED]> writes:
> > Two mails with updated translations for /src/backend/po/hr.po are lost.
> > First time I send clear po file, second tar.gz - no result.
> > Is something blocking mails with attachment ?
>
> How big were they --- over 40K?  If so, they're probably being held for
> moderator approval.

Just checked the queue, and nothing from 'Darko' or 'finteh' in the queue
to be approved ... is there another address it would have come from?

---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] Thursday still good to go for beta4 ... ?

[Marc G. Fournier]

As the subject says, anybody sitting on anything that they wish to get
into beta4?  'translation stuff' not withstanding, of course ...

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Tom Lane]

Stephan Szabo <[EMAIL PROTECTED]> writes:
> On Wed, 1 Oct 2003, Tom Lane wrote:
>> I've committed the attached patch.  One thing I wanted to double-check
>> with you is that the SELECT FOR UPDATES done in the noaction cases are
>> being correctly handled.

> I think the ones in the main functions need to be current snapshot.  I
> think the one in ri_Check_Pk_Match doesn't need to be. That's there to see
> if this same transaction has inserted a new row with the old value of the
> updated/deleted pk row and the serializable snapshot should be fine.
> Any conflicting attempts from another transaction should be waiting on our
> completion due to the unique index I think.

An exact match would be waiting on our commit, but I was confused about
whether ri_Check_Pk_Match could look for partial matches or not.  If it
can, then I'd think it might encounter rows inserted by concurrent
transactions.  OTOH maybe in serializable mode we should pretend those
aren't there.  The thing that really confused me was that it's doing a
SELECT FOR UPDATE --- does it really need to do any row locking, if it's
looking only for rows that our own xact inserted?

>> heap_mark4update, meaning that these SELECT FOR UPDATEs won't complain
>> if they find a row that was inserted later than the start of the
>> serializable transaction.  I'm not totally sure if they should or not;
>> what do you think?

> Well, I think that not doing so would only change the error from a
> serialization error to a matching row exists error.

That was my thought also, and that the row-exists error would be more
informative of the two, but I'm worried that I've overlooked something.

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] Thoughts on maintaining 7.3

[Tom Lane]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
>When I was reading hackers about the fixes you had made, it stated 
> that the index bloat problems should be better. I took
> that as meaning that although it won't be required nearly as often, we 
> still may need to reindex occassionaly.

The critical word there is "may".  The index compression code covers
some cases and not others.  Depending on your usage pattern you might or
might not ever need to reindex.  I *think* that most people won't need
to reindex any more, but I'm waiting on field reports from 7.4 to find
out for sure.

In any case, people who aren't upgrading from 7.3 because they think
7.4 won't help them are making a self-fulfilling negative prophecy.

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] ADD FOREIGN KEY

[Tom Lane]

Manfred Koizar <[EMAIL PROTECTED]> writes:
> db=> SET disable-fk-verification = true;
> ERROR: "disable-fk-verification" can only be set in single user mode

I don't really see the point of such a restriction.  Restricting the
system to a single user has nothing to do with making
disable-fk-verification more safe.  It would simply be an artificial
restriction making the feature harder to use.

Also, not very long ago we were speculating about the possible value of
parallel restore processes --- while I recall being unimpressed with the
likely gains, I wouldn't want to put a permanent kibosh on the idea by
adopting a philosophy that restores are supposed to be done in
single-user mode.

regards, tom lane

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] FreeSpaceMap hashtable out of memory

[Maksim Likharev]

It is problematic to produce small enough subset, due to large DB and 
randomness of the situation.
But here is what we see in server log file, see below:

It seems like 
WARNING:  ShmemAlloc: out of memory
ERROR:FreeSpaceMap hashtable out of memory

goes together, does it related to the size of Shared Memory or could
increase of 
Shared Memory solve the thing or that just a coincidence?

Another thing ( from the code) it seems that
hash_search trying to inset new entry into DynHash and cannot allocate
memory
in DynHash Context, so another question is DynHash Context upper
limited, another word
cannot grow more than 8 * 1024 * 1024 byte?

Any suggestions are welcome, 
Thank you.


--- LOG

LOG:  all server processes terminated; reinitializing shared memory and 
semaphores
IpcMemoryCreate: shmget(key=5432001, size=4669440, 03600) failed: Not
enough 
core

This error usually means that PostgreSQL's request for a shared
memory segment exceeded available memory or swap space.
To reduce the request size (currently 4669440 bytes), reduce
PostgreSQL's shared_buffers parameter (currently 256) and/or
its max_connections parameter (currently 128).

The PostgreSQL Administrator's Guide contains more information about
shared memory configuration.


Error 2:
WARNING:  ShmemAlloc: out of memory
ERROR:  FreeSpaceMap hashtable out of memory



-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]
Sent: Wednesday, October 01, 2003 2:51 PM
To: Maksim Likharev
Cc: [EMAIL PROTECTED]
Subject: Re: [HACKERS] FreeSpaceMap hashtable out of memory 


"Maksim Likharev" <[EMAIL PROTECTED]> writes:
> Using PG under Cygwin we having following error message during INSERT
> INTO
> "FreeSpaceMap hashtable out of memory".

Hm, that's not supposed to happen.  Can you create a reproducible
example?

regards, tom lane

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] query plan different for "SELECT ..." and "DECLARE CURSOR ..."?

[Tom Lane]

David Blasby <[EMAIL PROTECTED]> writes:
> I've been noticing query planning to be different for a cursor-based 
> select and normal select.

IIRC, in a DECLARE context the planner puts more weight on the startup
cost than the total cost, on the theory that you might not be planning
to fetch the whole result, and even if you are you may prefer to overlap
some frontend and backend processing by fetching the results
incrementally rather than all at once.

There was some talk of introducing a control variable to affect this
weighting, but it's not there yet.

In any case, I'd think the real issue here is that the planner thinks
these two plans are nearly the same cost, when in reality there's an
order-of-magnitude difference.  As far as I can see the problem is with
the estimation of this scan result:

>   ->  Seq Scan on lha_albers a  (cost=0.00..10.11 rows=1 width=36) (actual 
> time=1.06..15.54 rows=89 loops=1)
> Filter: (the_geom && 'SRID=-1;BOX3D(25 250,190 190 
> 0)'::geometry)

The factor-of-89 error in row count here translates directly to a
factor-of-89 underestimation of the cost of the nestloop plan.

You may need to bite the bullet and try to devise some real selectivity
estimation techniques for your geometric operators.  The stuff in
src/backend/utils/adt/geo_selfuncs.c at the moment is all just stubs :-(

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] Thoughts on maintaining 7.3

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> I think what Tom is concerned about is that this hasn't been tested
> enough with big datasets.  Also there a little loss of index pages but
> it's much less (orders of magnitude, I think) than what was before.
> This is because the index won't shrink "vertically".

The fact that we won't remove levels shouldn't be meaningful at all ---
I mean, if the index was once big enough to require a dozen btree
levels, and you delete everything, are you going to be upset that it
drops to 13 pages rather than 2?  I doubt it.

The reason I'm waffling about whether the problem is completely fixed or
not is that the existing code will only remove-and-recycle completely
empty btree pages.  As long as you have one key left on a page it will
stay there.  So you could end up with ridiculously low percentage-filled
situations.  This could be fixed by collapsing together adjacent
more-than-half-empty pages, but we ran into a lot of problems trying to
do that in a concurrent fashion.  So I'm waiting to find out if real
usage patterns have a significant issue with this or not.

For example, if you have a timestamp index and you routinely clean out
all entries older than N-days-ago, you won't have a problem in 7.4.
If your pattern is to delete nine out of every ten entries (maybe you
drop minute-by-minute entries and keep only hourly entries after awhile)
then you might find the index loading getting unpleasantly low.  We'll
have to see whether it's a problem in practice.  I'm willing to revisit
the page-merging problem if it's proven to be a real practical problem,
but it looked hard enough that I think it's more profitable to spend the
development effort elsewhere until it's proven necessary.

regards, tom lane

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [HACKERS] invalid tid errors in latest 7.3.4 stable.

[Stephan Szabo]

On Wed, 1 Oct 2003, Tom Lane wrote:

> Stephan Szabo <[EMAIL PROTECTED]> writes:
> > On Wed, 1 Oct 2003, Tom Lane wrote:
> >> I've committed the attached patch.  One thing I wanted to double-check
> >> with you is that the SELECT FOR UPDATES done in the noaction cases are
> >> being correctly handled.
>
> > I think the ones in the main functions need to be current snapshot.  I
> > think the one in ri_Check_Pk_Match doesn't need to be. That's there to see
> > if this same transaction has inserted a new row with the old value of the
> > updated/deleted pk row and the serializable snapshot should be fine.
> > Any conflicting attempts from another transaction should be waiting on our
> > completion due to the unique index I think.
>
> An exact match would be waiting on our commit, but I was confused about
> whether ri_Check_Pk_Match could look for partial matches or not.  If it

Not currently, it only checks for exact matches on fully non-NULL keys.
The only case where partial matches are needed are match partial, but the
rules are actually more complicated (because the which part needs to be
partially matched is dependant on the actual matched rows).
I guess it doesn't hurt for it to be using the current + cross check
excepting a small amount of time presumably since it shouldn't change the
results.

> can, then I'd think it might encounter rows inserted by concurrent
> transactions.  OTOH maybe in serializable mode we should pretend those
> aren't there.  The thing that really confused me was that it's doing a
> SELECT FOR UPDATE --- does it really need to do any row locking, if it's
> looking only for rows that our own xact inserted?

Actually, it probably doesn't. I just wasn't bright enough to think of it
to change it from the other queries at the time.

> >> heap_mark4update, meaning that these SELECT FOR UPDATEs won't complain
> >> if they find a row that was inserted later than the start of the
> >> serializable transaction.  I'm not totally sure if they should or not;
> >> what do you think?
>
> > Well, I think that not doing so would only change the error from a
> > serialization error to a matching row exists error.
>
> That was my thought also, and that the row-exists error would be more
> informative of the two, but I'm worried that I've overlooked something.

I don't see it either, but I'm also a little worried in general.  Do we
have any sort of easy multi-session regression test like tools already
built for sort of lock step testing of things like this?  I think I'm
going to try to run as many cases as I can come up with this week on cvs
tip.

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] Thoughts on maintaining 7.3

[Tom Lane]

Robert Treat <[EMAIL PROTECTED]> writes:
> and the question as i thought was being discussed (or should be
> discussed) was what is the level of interest in having this work kept in
> the community cvs tree vs. someone else's quasi-forked branch... 

I see no reason that the maintenance shouldn't be done in the community
CVS archive.  The problem is where to find the people who want to do it.
Of course we have to trust those people enough to give them write access
to the community archive, but if they can't be trusted with that, one
wonders who's going to trust their work product either.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [HACKERS] FreeSpaceMap hashtable out of memory

[Tom Lane]

"Maksim Likharev" <[EMAIL PROTECTED]> writes:
> It seems like 
> WARNING:  ShmemAlloc: out of memory
> ERROR:FreeSpaceMap hashtable out of memory
> goes together, does it related to the size of Shared Memory

Yeah, the FSM hashtable is in shared memory, so your problem is that
you're running out of shared memory.  This is not necessarily the fault
of the FSM as such though; it could be that some other shared data
structure is growing bigger than it was expected to.

Thinking about it, I'm fairly certain that the FSM can't grow larger
than the bounds you set for it, and so the problem is presumably
elsewhere.  The most likely bet is that the lock table is getting larger
than expected.  There is a control knob for the estimated size of the
lock table (max_locks_per_transaction), so if that's where the problem
is, it's easy to fix.  You should try to find out if that's the issue
though.  When this happens, are there a very large number of entries in
the pg_locks view?

regards, tom lane

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

[HACKERS] PREPARE/EXECUTE across backends?

[Jingren Zhou]

Hi,

From the document, it seems that PREPARE/EXECUTE works only in the same 
session. I am wondering whether postgres can prepare a query (save the plan) 
for difference backends.

I am working on a project which requires executing "psql -c 'query'" in 
command line multiple times. Since the performance is critical, it would be 
nice to prepare the same query first to avoid being parsed/optimized each 
time. But psql opens a new backend each time, it looks like that 
PREPARE/EXECUTE doesn't work. Is there any workaround?

Thanks

_
Instant message with integrated webcam using MSN Messenger 6.0. Try it now 
FREE!  http://msnmessenger-download.com

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] PREPARE/EXECUTE across backends?

[Neil Conway]

On Wed, 2003-10-01 at 20:25, Jingren Zhou wrote:
> From the document, it seems that PREPARE/EXECUTE works only in the same 
> session. I am wondering whether postgres can prepare a query (save the plan) 
> for difference backends.

The decision to store prepared statements per-backend, rather than in
shared memory, was made deliberately. In fact, an early version of the
PREPARE/EXECUTE patch (written by Karel Zak) stored prepared statements
in shared memory. But I decided to remove this, because:

  - it is more complex

  - since shared memory must be allocated statically on postmaster
startup, it would make prepared statements more fragile: at some point
we would run out of room in shm, and need to either remove prepared
statements, or swap them out to disk

  - it would encourage poor application design, since it wouldn't be
trivial to tell whether a given prepared query has already been prepared
by a different backend, and what name it is using

  - the performance gains are not that dramatic: preparing a statement
once per active backend is not that expensive. In most of the cases
where prepared statements are useful, since the # of backends is usually
far smaller than the # of times you're executing a given prepared
statement

That's all the reasons I can think of off the top of my head for doing
things the way we do. However, I'm open to being convinced: if you think
we should store prepared statements in shm, feel free to make a case for
it.

-Neil



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] NOTICE: CREATE TABLE will create implicit triggers

[Christopher Kings-Lynne]

Peter Eisentraut wrote:

Regarding the NOTICE

CREATE TABLE will create implicit triggers for foreign-key checks

Does anyone care?
I don't.

The other "helpful" notices about sequences for serial columns and indexes
for unique constraints have some merit, because they inform the user
objects that the user might be interested in are going to be created as a
side effect.  But there's nothing that a user can reasonably do with an
implicit trigger for a foreign-key check.  ISTM that this notice is about
as interesting as the information that CREATE VIEW will create an implicit
rule.
I agree.

Chris



---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

Re: [HACKERS] PREPARE/EXECUTE across backends?

[Kris Jurka]

On Wed, 1 Oct 2003, Jingren Zhou wrote:

> Hi,
>
> >From the document, it seems that PREPARE/EXECUTE works only in the same
> session. I am wondering whether postgres can prepare a query (save the plan)
> for difference backends.
>
> I am working on a project which requires executing "psql -c 'query'" in
> command line multiple times. Since the performance is critical, it would be
> nice to prepare the same query first to avoid being parsed/optimized each
> time. But psql opens a new backend each time, it looks like that
> PREPARE/EXECUTE doesn't work. Is there any workaround?

Your real overhead here isn't from having to prepare the query each time,
it's from having to start psql and open a new connection each time.
Perhaps you need to rethink your design and go with something that will
maintain a persistent connection.

Kris Jurka



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] PREPARE/EXECUTE across backends?

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> writes:
> The decision to store prepared statements per-backend, rather than in
> shared memory, was made deliberately. In fact, an early version of the
> PREPARE/EXECUTE patch (written by Karel Zak) stored prepared statements
> in shared memory. But I decided to remove this, because:
> [ several good reasons ]

Another issue is that we currently don't have a mechanism for flushing
query plans when they become obsolete (eg, an index is added or
removed).  Locally-cached plans are relatively easy to refresh: just
start a fresh session.  A shared plan cache would retain bogus plans
forever, short of a postmaster restart.

Obviously we need a mechanism for detecting and handling cached-plan
invalidations, and I hope someone will get around to that soon.
But we *cannot* consider a shared plan cache until that mechanism
exists.

If I recall correctly, Karel's original shared plan cache also triggered
a lot of concern about contention for the shared data structure ...
I'm not convinced that it would be a big bottleneck, but there's
definitely an issue to think about there ...

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] PREPARE/EXECUTE across backends?

[Neil Conway]

On Wed, 2003-10-01 at 22:43, Tom Lane wrote:
> Another issue is that we currently don't have a mechanism for flushing
> query plans when they become obsolete (eg, an index is added or
> removed).  Locally-cached plans are relatively easy to refresh: just
> start a fresh session.  A shared plan cache would retain bogus plans
> forever, short of a postmaster restart.

Well, keep in mind we already have DEALLOCATE for removing prepared
statements, which would continue to be available if we switched to
storing prepared statements in shared memory. However, using DEALLOCATE
to get around invalid cached plans is obviously not a good solution.

> Obviously we need a mechanism for detecting and handling cached-plan
> invalidations, and I hope someone will get around to that soon.

Agreed.

> But we *cannot* consider a shared plan cache until that mechanism
> exists.

Given the presence of DEALLOCATE, I think this overstates the case
somewhat: longer-lived prepared statements that are stored in shared
memory makes handling invalidated plans more of an issue, of course.

-Neil



---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] initdb

[Andrew Dunstan]

Peter Eisentraut wrote:

Andrew Dunstan writes:

 

So if the data directory previously existed and was empty, we don't
clean it out on error, even if we didn't use the noclean flag. Is this
intended behaviour or a bug? (If a bug it's trivially easy to fix.)
   

If the data directory already existed, we don't want to delete it.  We
should possibly delete the content, though.
 

yes, that's what I meant.

Here's a patch which should work, I think:

Index: src/bin/initdb/initdb.sh
===
RCS file: /projects/cvsroot/pgsql-server/src/bin/initdb/initdb.sh,v
retrieving revision 1.203
diff -c -w -r1.203 initdb.sh
*** src/bin/initdb/initdb.sh27 Sep 2003 16:27:57 -  1.203
--- src/bin/initdb/initdb.sh1 Oct 2003 21:06:07 -
***
*** 44,49 
--- 44,53 
 if [ "$made_new_pgdata" = yes ]; then
 echo "$CMDNAME: removing data directory \"$PGDATA\"" 1>&2
 rm -rf "$PGDATA" || echo "$CMDNAME: failed" 1>&2
+ else
+ echo "$CMDNAME: removing contents of data directory 
\"$PGDATA\"" 1>&2
+ rm -rf "$PGDATA"/* || echo "$CMDNAME: failed" 1>&2
 fi
 else
 echo "$CMDNAME: data directory \"$PGDATA\" not removed at 
user's request" 1>&2



---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] 7.4 status

[Peter Eisentraut]

Alvaro Herrera writes:

> The only things left that I can see are
>
> #: commands/tablecmds.c:4093
> msgid "tables \"%s\" already has a TOAST table"
> "tables" -> "table"

Fixed.

> #: commands/user.c:651 commands/user.c:1357
> msgid "sysid %d is already assigned"
> "sysid" -> "user ID"?  Not sure, maybe it can be a group ID.

Changed to "user ID" and "group ID".  Sounds better to me too.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [HACKERS] 7.4 status

[Peter Eisentraut]

Patrick Welche writes:

> Does this count a string change? :)

Yes, but it's also a documentation bug fix.

Anyway, you got it in before the freeze. :)

-- 
Peter Eisentraut   [EMAIL PROTECTED]


---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])