Re: 8.0 Open Items

P O S T G R E S Q L

8 . 0 O P E N I T E M S

Current version at ftp://momjian.postgresql.org/pub/postgresql/open_items.

Changes
-------
* Win32
o add binary version stamps?
o fix signal-safe socket handler for SSL
o fix query cancel in psql (?)
o report correct errno codes from native Windows system calls
o shorten timezone for %t log_line_prefix
o start pg_autovacuum easily
o fix users who's timezones are not recognized
o allow installed locales rather than hardcoded one
o update encoding list to include win1250
o synchonize supported encodings and docs
o use dynamic buffer for token buffer in win32 admin check
o disable readline-required psql options
o fix shared memory on Win2k terminal server
* fix oid2name for tablespaces
* allow libpq to check parameterized data types
* make pgxs install by default
* add xid to log_line_prefix for PITR
* cleanup FRONTEND use in /port, malloc, elog
* fix recovery of DROP TABLESPACE after checkpoint
* fix ambiguity for objects using default tablespaces
* fix case where template db already uses target tablespace
* determine proper crash recovery/logging for pg_subtrans
* remove to_char(interval) if we initdb or mention removal
* have plpython reject pseudotype arguments because it crashes
* add i386 solaris spinlock code

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On August 20, 2004 01:28 pm, Bruce Momjian wrote:
> P O S T G R E S Q L
>
> 8 . 0 O P E N I T E M S
> * determine proper crash recovery/logging for pg_subtrans
> * remove to_char(interval) if we initdb or mention removal

I vote just to mention it's removal at this time, much the say way we
deprecated money I know that we make use of to_char(interval) which we have
added code to handel those edgecases where it was returning improper results.
This will allow us (PGDG or our clients) more time to develop a full and
proper solution the real problem.

> * have plpython reject pseudotype arguments because it crashes
> * add i386 solaris spinlock code

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com

Agreed. Done.

---------------------------------------------------------------------------

Darcy Buskermolen wrote:
> On August 20, 2004 01:28 pm, Bruce Momjian wrote:
> > P O S T G R E S Q L
> >
> > 8 . 0 O P E N I T E M S
> > * determine proper crash recovery/logging for pg_subtrans
> > * remove to_char(interval) if we initdb or mention removal
>
> I vote just to mention it's removal at this time, much the say way we
> deprecated money I know that we make use of to_char(interval) which we have
> added code to handel those edgecases where it was returning improper results.
> This will allow us (PGDG or our clients) more time to develop a full and
> proper solution the real problem.
>
>
> > * have plpython reject pseudotype arguments because it crashes
> > * add i386 solaris spinlock code
>
> --
> Darcy Buskermolen
> Wavefire Technologies Corp.
> ph: 250.717.0200
> fx: 250.763.1759
> http://www.wavefire.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Darcy Buskermolen wrote:
>>> * remove to_char(interval) if we initdb or mention removal
>>
>> I vote just to mention it's removal at this time,

> Agreed. Done.

While I don't care that much one way or the other --- what is the
difference between this and the prior state? Karel already said
in the 7.4 docs that to_char(interval) would be removed in the next
release. Why would the people who ignored the warning last time
believe it this time round?

I think that 8.0 is a more appropriate release number in which to be
taking backwards-compatibility hits than 8.1. So if we're gonna do
it at all, I would vote for doing it now.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Darcy Buskermolen wrote:
> >>> * remove to_char(interval) if we initdb or mention removal
> >>
> >> I vote just to mention it's removal at this time,
>
> > Agreed. Done.
>
> While I don't care that much one way or the other --- what is the
> difference between this and the prior state? Karel already said
> in the 7.4 docs that to_char(interval) would be removed in the next
> release. Why would the people who ignored the warning last time
> believe it this time round?
>
> I think that 8.0 is a more appropriate release number in which to be
> taking backwards-compatibility hits than 8.1. So if we're gonna do
> it at all, I would vote for doing it now.

I don't see any mention of it being removed in the 7.4 release notes.
I see it in the SGML docs:

Warning: <literal><function>to_char</function>(<type>interval</type>, <type>text</type>)</literal>
is deprecated and should not be used in newly-written code. It will be removed in the next version.

I suppose that is enough warning. Is it fair to remove things during
beta though, especially since it wasn't mentioned in the 7.4 release
notes but just the docs? Of course, if it is buggy, that might be a
reason to remove it anyway.

Let's keep it in the release notes and if we remove it via initdb we can
just update the release notes to say it is now removed in 8.0.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> I see it in the SGML docs:

> Warning: <literal><function>to_char</function>(<type>interval</type>, <type>text</type>)</literal>
> is deprecated and should not be used in newly-written code. It will be removed in the next version.

> I suppose that is enough warning. Is it fair to remove things during
> beta though, especially since it wasn't mentioned in the 7.4 release
> notes but just the docs? Of course, if it is buggy, that might be a
> reason to remove it anyway.

The reason that that warning is in the 7.4 docs is that we already knew
it was buggy before 7.4 release. (In fact I'm pretty sure this most
recent bug report is a duplicate.)

> Let's keep it in the release notes and if we remove it via initdb we can
> just update the release notes to say it is now removed in 8.0.

Okay, I don't want to force an initdb just for this either. But if we
do one for other reasons, it's toast.

regards, tom lane

Tom Lane wrote:
> Okay, I don't want to force an initdb just for this either. But if we
> do one for other reasons, it's toast.

I don't see why an initdb is required: if we want to remove it, we can
replace the function's implementation with elog(ERROR, "this function
has been removed"), or the like. The difference between doing that much
and actually removing the function's catalog entry is pretty negligible
from the user's POV. The next time we bump the catalog version (either
during beta or during the 8.1 cycle), we can remove the catalog entry
for the function.

That said, I don't see the need to get rid of the function in time for
8.0, and it would be nice to have a more public notice of deprecation
(the release notes) to give users fair warning before we remove it.

-Neil

P.S. I hope everyone had a good summer!

Neil Conway <neilc(at)samurai(dot)com> writes:
> Tom Lane wrote:
>> Okay, I don't want to force an initdb just for this either. But if we
>> do one for other reasons, it's toast.

> I don't see why an initdb is required: if we want to remove it, we can
> replace the function's implementation with elog(ERROR, "this function
> has been removed"), or the like. The difference between doing that much
> and actually removing the function's catalog entry is pretty negligible
> from the user's POV.

No, not at all. A nonfunctional catalog entry gets in the way of the
user replacing the function, should he wish to do that.

regards, tom lane

Tom Lane wrote:
> Neil Conway <neilc(at)samurai(dot)com> writes:
> > Tom Lane wrote:
> >> Okay, I don't want to force an initdb just for this either. But if we
> >> do one for other reasons, it's toast.
>
> > I don't see why an initdb is required: if we want to remove it, we can
> > replace the function's implementation with elog(ERROR, "this function
> > has been removed"), or the like. The difference between doing that much
> > and actually removing the function's catalog entry is pretty negligible
> > from the user's POV.
>
> No, not at all. A nonfunctional catalog entry gets in the way of the
> user replacing the function, should he wish to do that.

Yea, but I would call the odds of that "pretty negligible".

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Sat, 2004-08-21 at 11:26, Bruce Momjian wrote:
> Tom Lane wrote:
<snip>
> > No, not at all. A nonfunctional catalog entry gets in the way of the
> > user replacing the function, should he wish to do that.
>
> Yea, but I would call the odds of that "pretty negligible".

What if they're trying to restore backwards compatibility by adding the
function?

Regards,
Jeff

On Fri, Aug 20, 2004 at 11:44:28PM -0400, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Darcy Buskermolen wrote:
> >>> * remove to_char(interval) if we initdb or mention removal
> >>
> >> I vote just to mention it's removal at this time,
>
> > Agreed. Done.
>
> While I don't care that much one way or the other --- what is the
> difference between this and the prior state? Karel already said
> in the 7.4 docs that to_char(interval) would be removed in the next
> release. Why would the people who ignored the warning last time
> believe it this time round?
>
> I think that 8.0 is a more appropriate release number in which to be
> taking backwards-compatibility hits than 8.1. So if we're gonna do
> it at all, I would vote for doing it now.

I agree with Tom. The function to_char(interval) is useless, bad,
unsupported and without future (if you want to know why you can found
answer in lists archive). I think 8.0 is really better time for some
cleanups and back compatibility changes than some other release.

BTW, I'm going to start fulltime job for RH next week. Note RH
constitute new team of developers in Czech Republic. Maybe I will have
again more time for PostgreSQL (or maybe not if they assign me to some
other project -- but PostgreSQL is my wish :-) So to_char() familly
will again better maintained.

Karel

--
Karel Zak <zakkr(at)zf(dot)jcu(dot)cz>
http://home.zf.jcu.cz/~zakkr/

On August 20, 2004 09:01 pm, Bruce Momjian wrote:
> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > Darcy Buskermolen wrote:
> > >>> * remove to_char(interval) if we initdb or mention removal
> > >>
> > >> I vote just to mention it's removal at this time,
> > >
> > > Agreed. Done.
> >

> I don't see any mention of it being removed in the 7.4 release notes.
> I see it in the SGML docs:
>
> Warning: <literal><function>to_char</function>(<type>interval</type>,
> <type>text</type>)</literal> is deprecated and should not be used in
> newly-written code. It will be removed in the next version.

Hmm I had never seen this entry before, most likely because it's the same
size/face as the body, Maybe in the future we can have warnings of this sort
made a bit more prominate or such? Though I must admit I do not read the
manual from cover to cover with each release. My reading tends to stem from
the release notes to give me an overview of what's new, and what's coming
down the pipe. So based on what in the docs, and that this is an major
release, removing to_char(interval) would be acceptable.

>
> I suppose that is enough warning. Is it fair to remove things during
> beta though, especially since it wasn't mentioned in the 7.4 release
> notes but just the docs? Of course, if it is buggy, that might be a
> reason to remove it anyway.
>
> Let's keep it in the release notes and if we remove it via initdb we can
> just update the release notes to say it is now removed in 8.0.

--
Darcy Buskermolen
Wavefire Technologies Corp.
ph: 250.717.0200
fx: 250.763.1759
http://www.wavefire.com

One of the possible barriers to adoption of PITR is the volume of the logs
themselves. Maybe this isn't a problem for now, maybe it is.

Re-thinking the whole purpose of the additional full page images appended to
the xlog records, I now understand and agree with Tom's comment in the docs
that we don't need to include those additional full page images for PITR -
they only exist to correctly recover the database in the event of a crash.
The reason for this is that the base backup provides a full set of blocks on
which to recover - there is no danger that the backup contains bad blocks,
as would be the case for crash recovery. It's taken an age for me to
understand that bit, since the actual crash recovery model seems different
from other systems (I should have spotted this earlier).

As a result, I have thought that there may be a way to remove those pages
from the xlog files immediately before being copied away to archive, without
effecting crash recovery logic AT ALL. The archiver process could read the
xlog files and re-write them exactly as read to another file, but without
the full page images - writing exactly the current xlog record format. This
would mean that the archived xlog files would then become variable length.
Apart from that, not much other code need change. The recovery logic
wouldn't need to change at all - the xlog files would just simply never have
full page images to re-apply. The archive logic would need enhancing to do
the read/re-write, but much of that same code needs to be written/adapted
anyway for the offline xlog file reader. The archive code itself would
simply copy to an intermediate file, say ARCHIVEFILE, just like we do on
recovery - so the use of %p would still work as before and require
redirecting only, no other changes.

Anyway, the effect of that would be to allow compression of ARCHIVED xlog
files, without effecting crash recovery logic AT ALL - so the full page
images would still exist within locally held xlog files. If we ever mix the
two on recovery, it all still works AFAICS. So, the problem of how we stop
saving away full page images goes away - we still Save them, but we don't
Archive them.

I raise this now because I'm thinking that this functionality really ought
to be in the main production 8.0 release. Not sure if anybody will
agree....but that's what I'm thinking now, based upon what seems like a
simple design to put it there. My rationale is that it will be simpler to
support one file format than two, if we introduce the change at a later
time.

...I know, I write it, then we decide.....OK.....

Best Regards, Simon Riggs

> Bruce Momjian wrote
>
> P O S T G R E S Q L
>
> 8 . 0 O P E N I T E M S
>

a few comments...

> * add xid to log_line_prefix for PITR

This at first sounded like a wonderfully simple solution, though on
consideration, I'm not sure I'd recommend its use or ask for help in writing
it from the developer community.

First, it is an optional feature, and so won't help in the slightest when
you forgot to turn it on.

Second, as you mentioned when originally discussed, just because 123 has
committed, doesn't mean 122 has. So when the log line prefix shows 117 xid
for one log entry, then 123 for the next one...we really know nothing at all
about what happened between one log entry and the next and we would be
taking a stupid risk to say "I'll aim for 120 then".

Tom and I had previously discussed an offline xlog file inspection tool,
which almost got named...pg_xlogspy IIRC. The idea would be that you scan
the file to find out the contents, thereby allowing more informed decisions
about how to select your recovery target values. It's not written yet, I
know, but we (i.e. I) probably have time to make it work.

> * fix recovery of DROP TABLESPACE after checkpoint

Not something I have time, or sufficient immediate knowledge of to look into
this. Personally, I would just re-issue the statement...

Perhaps we're referring to the CREATE/DROP DATABASE not being logged?
Slightly different, its true.
Again, I would have to say, not enough time to learn how to make that
work... though it looks straightforward enough - need to re-create the
directory and make the correct catalog entries.

While reading/writing documentation, I'd been thinking some more about:
XLog File compression on archive
(opening a separate thread to discuss)

Best Regards, Simon Riggs

On Mon, Aug 23, 2004 at 10:03:26PM +0100, Simon Riggs wrote:

Simon,

> I raise this now because I'm thinking that this functionality really ought
> to be in the main production 8.0 release. Not sure if anybody will
> agree....but that's what I'm thinking now, based upon what seems like a
> simple design to put it there. My rationale is that it will be simpler to
> support one file format than two, if we introduce the change at a later
> time.

Why do you talk about another file format? AFAIU it's the same format,
only the archived files will lack the page images. Maybe I'm missing
something?

Anyway I think we are way past feature freeze, and XLog "compression"
could made it into 8.1 without loss of functionality. At that point we
will say "now PITR uses less space on the archiver" and that's it.

(Maybe we could even think about LZ-compressing the remaining WAL
entries on the archived logs?)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La felicidad no es mañana. La felicidad es ahora"

Alvaro Herrera wrote:
> On Mon, Aug 23, 2004 at 10:03:26PM +0100, Simon Riggs wrote:
>
> Simon,
>
> > I raise this now because I'm thinking that this functionality really ought
> > to be in the main production 8.0 release. Not sure if anybody will
> > agree....but that's what I'm thinking now, based upon what seems like a
> > simple design to put it there. My rationale is that it will be simpler to
> > support one file format than two, if we introduce the change at a later
> > time.
>
> Why do you talk about another file format? AFAIU it's the same format,
> only the archived files will lack the page images. Maybe I'm missing
> something?
>
> Anyway I think we are way past feature freeze, and XLog "compression"
> could made it into 8.1 without loss of functionality. At that point we
> will say "now PITR uses less space on the archiver" and that's it.
>
> (Maybe we could even think about LZ-compressing the remaining WAL
> entries on the archived logs?)

Agreed. I added a mention of it in the TODO list:

* Eliminate need to write full pages to WAL before page modification

Currently, to protect against partial disk page writes, we write the
full page images to WAL before they are modified so we can correct any
partial page writes during recovery. These pages can also be
eliminated from point-in-time archive files.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Simon Riggs wrote:

> As a result, I have thought that there may be a way to remove those pages
> from the xlog files immediately before being copied away to archive, without
> effecting crash recovery logic AT ALL. The archiver process could read the
> xlog files and re-write them exactly as read to another file, but without
> the full page images - writing exactly the current xlog record format. This
> would mean that the archived xlog files would then become variable length.
> Apart from that, not much other code need change. The recovery logic
> wouldn't need to change at all - the xlog files would just simply never have
> full page images to re-apply. The archive logic would need enhancing to do
> the read/re-write, but much of that same code needs to be written/adapted
> anyway for the offline xlog file reader. The archive code itself would
> simply copy to an intermediate file, say ARCHIVEFILE, just like we do on
> recovery - so the use of %p would still work as before and require
> redirecting only, no other changes.

So this means that the way to have an "hot spare" postgres that play
the logs while are arriving is not applicable anymore ?

See Tom advice: http://archives.postgresql.org/pgsql-hackers/2004-08/msg00704.php
and my success: http://archives.postgresql.org/pgsql-hackers/2004-08/msg00852.php

Am I right ?

Regards
Gaetano Mendola

On Tue, Aug 24, 2004 at 12:05:33AM +0200, Gaetano Mendola wrote:
> Simon Riggs wrote:
>
> >As a result, I have thought that there may be a way to remove those pages
> >from the xlog files immediately before being copied away to archive,
> >without effecting crash recovery logic AT ALL.
>
> So this means that the way to have an "hot spare" postgres that play
> the logs while are arriving is not applicable anymore ?

No, it doesn't. Your scheme can still work; it will just have less work
to do.

Basically the point here is skipping the restoration of whole pages. In
a non-crashed scenario there's no need to restore them, because it is
only used if the system happenned to crash while there was a write in
progress. Supposedly this cannot happen on your hot-spare system.

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"La persona que no quería pecar / estaba obligada a sentarse
en duras y empinadas sillas / desprovistas, por cierto
de blandos atenuantes" (Patricio Vogel)

"Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> As a result, I have thought that there may be a way to remove those pages
> from the xlog files immediately before being copied away to archive, without
> effecting crash recovery logic AT ALL.

This isn't all that easy. The main problem I can see is that you have
to maintain (or be able to reconstruct) the absolute WAL offset of each
WAL record, because that number shows up in page LSNs in the data files.
There's also the question of keeping track of page boundaries in the WAL
files. And random access to a WAL segment would go by the wayside ---
I think you'd have to scan forward from the file start to locate the
checkpoint record you intend to replay from. The reader code would need
some nontrivial alterations to deal with all this, and you would
*definitely* need to know whether you were reading a compressed or
uncompressed WAL file.

I have zero interest in touching this problem for 8.0 ...

regards, tom lane

PS: but something you *could* do in 8.0 is replace "cp" by "gzip" to
archive compressed files that way.

> Bruce Momjian...
> Alvaro Herrera wrote:
> > Anyway I think we are way past feature freeze, and XLog "compression"
> > could made it into 8.1 without loss of functionality. At that point we
> > will say "now PITR uses less space on the archiver" and that's it.
> >
> > (Maybe we could even think about LZ-compressing the remaining WAL
> > entries on the archived logs?)
>
> Agreed. I added a mention of it in the TODO list:
>

> Tom Lane...
> I have zero interest in touching this problem for 8.0 ...

OK, thats unanimously out for 8.0.

Yes, you can compress stuff with a tool - though its even easier if the data
isn't there in the first place.

Thanks for the quick replies - saved me some time.

Best Regards, Simon Riggs