Re: [HACKERS] length coerce for bpchar is broken since 7.0

2000-10-16 Thread Tom Lane

Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> Before going further, may I ask you a question. Why in exprTypmod() is
> bpchar() treated differently from other data types such as varchar?

It's just hardwired knowledge about that particular datatype.  In the
light of your comments, it seems clear that the code here is wrong
for the MULTIBYTE case: instead of plain VARSIZE(), it should be
returning the number of multibyte characters + 4 (or whatever
atttypmod is defined to mean for MULTIBYTE bpchar).  I think I wrote
this code to start with, so you can blame me for the fact that it
neglects the MULTIBYTE case :-(

regards, tom lane



Re: [HACKERS] length coerce for bpchar is broken since 7.0

2000-10-16 Thread Tatsuo Ishii

> > Simply clipping multibyte strings by atttypmode might produce
> > incorrect multibyte strings. Consider a case inserting 3 multibyte
> > letters (each consisting of 2 bytes) into a char(5) column.
> 
> It seems to me that this means that atttypmod or exprTypmod() isn't
> correctly defined for MULTIBYTE char(n) values.  We should define
> typmod in such a way that they agree iff the string is correctly
> clipped.  This might be easier said than done, perhaps, but I don't
> like the idea of having to apply length-coercion functions all the
> time because we can't figure out whether they're needed or not.

Before going further, may I ask you a question. Why in exprTypmod() is
bpchar() treated differently from other data types such as varchar?

switch (con->consttype)
{
case BPCHAROID:
if (!con->constisnull)
return 
VARSIZE(DatumGetPointer(con->constvalue));
break;
default:
break;
}

--
Tatsuo Ishii



Re: [HACKERS] length coerce for bpchar is broken since 7.0

2000-10-16 Thread Tom Lane

Tatsuo Ishii <[EMAIL PROTECTED]> writes:
 If VARSIZE returned from exprTypmod() and atttypmod passed to
 coerce_type_typmod() is equal, the function node to call bpchar()
 would not be added.
>> 
>> Um, what's wrong with that?  Seems to me that parse_coerce is doing
>> exactly what it's supposed to, ie, adding only length coercions
>> that are needed.

> Simply clipping multibyte strings by atttypmode might produce
> incorrect multibyte strings. Consider a case inserting 3 multibyte
> letters (each consisting of 2 bytes) into a char(5) column.

It seems to me that this means that atttypmod or exprTypmod() isn't
correctly defined for MULTIBYTE char(n) values.  We should define
typmod in such a way that they agree iff the string is correctly
clipped.  This might be easier said than done, perhaps, but I don't
like the idea of having to apply length-coercion functions all the
time because we can't figure out whether they're needed or not.

regards, tom lane



Re: [HACKERS] length coerce for bpchar is broken since 7.0

2000-10-16 Thread Tatsuo Ishii

> > If VARSIZE returned from exprTypmod() and atttypmod passed to
> > coerce_type_typmod() is equal, the function node to call bpchar()
> > would not be added.
> 
> Um, what's wrong with that?  Seems to me that parse_coerce is doing
> exactly what it's supposed to, ie, adding only length coercions
> that are needed.

Simply clipping multibyte strings by atttypmode might produce
incorrect multibyte strings. Consider a case inserting 3 multibyte
letters (each consisting of 2 bytes) into a char(5) column.

Or this kind of consideration should be in bpcharin() as I said in the
earilier mail?
--
Tatsuo Ishii



Re: [HACKERS] pgsql is 75 times faster with my new index scan

2000-10-16 Thread Bruce Momjian

> > TODO:
> > - add HeapTupleHeaderData into each IndexTupleData
> > - change code to reflect above
> > - when deleting-updating heap then also update tuples'
> >   HeapTupleHeaderData in indices
> 
> I doubt everyone would like trading query speed for insert/update 
> speed plus index size

If he is scanning through the entire index, he could do a sequential
scan of the table, grab all the tid transaction status values, and use
those when viewing the index.  No need to store/update the transaction
status in the index that way.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] "initdb -t" destroys all databases

2000-10-16 Thread Bruce Momjian

Peter, comments?


> Richard Poole <[EMAIL PROTECTED]> writes:
> > It seems that initdb starts a single-user backend but gives it the "-x"
> > option, which makes it call BootStrapXLOG, which fails because it
> > expects to be called only on absolutely first-time system startup (?).
> > initdb sees the failure and removes everything under the data directory,
> > which is the wrong behaviour here.
> 
> Sounds like a bug to me too.  Peter E. has been hacking initdb to be
> more robust; Peter, have you fixed this already in current sources?
> 
>   regards, tom lane
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



[HACKERS] Re: [COMMITTERS] pgsql/src/backend/utils/adt (formatting.c oracle_compat.c)

2000-10-16 Thread Bruce Momjian

> 
> This effectively one line patch should fix the fact that
> foreign key definitions in create table were erroring if
> a primary key was defined.  I was using the columns 
> list to get the columns of the table for comparison, but
> it got reused as a temporary list inside the primary key
> stuff.
> 
> Stephan Szabo
> 
> 

I think this was the fix Stephan was talking about.   I grabbed all the
patches a few weeks ago.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] The lightbulb just went on...

2000-10-16 Thread Tom Lane

The Hermit Hacker <[EMAIL PROTECTED]> writes:
>> I will get this patch made up tonight for REL7_0; if Alfred doesn't
>> see more failures after running it for a few days, then let's move
>> forward on a 7.0.3 release.

> that works for me ... I'm in Montreal for the weekend, so if we can get it
> out before Thursday, great, else we'll do it on Monday, 'k? 

I think he was seeing MTBF of several days anyway, so we won't have any
confidence that the problem is gone before next week.

regards, tom lane



Re: [HACKERS] psql's \d functions broken for views in current sources

2000-10-16 Thread Bruce Momjian

Was this addressed?


> Hiroshi Inoue <[EMAIL PROTECTED]> writes:
> > Tom Lane wrote:
> >> I assume this breakage is from the recent RELKIND_VIEW change;
> >> probably psql didn't get updated to know about the new relkind.
> 
> > Probably psql uses pg_views though I don't remember correctly.
> > It seemd that pg_views(initdb) should be changed first.
> 
> No, pg_views still works --- although it could be made far more
> efficient (don't need the WHERE EXISTS(...) test anymore, just look
> at relkind).  So I don't think that explains why psql is misbehaving.
> 
> You are right that we ought to change the definition of pg_views,
> anyway.
> 
>   regards, tom lane
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] length coerce for bpchar is broken since 7.0

2000-10-16 Thread Tom Lane

Tatsuo Ishii <[EMAIL PROTECTED]> writes:
> If VARSIZE returned from exprTypmod() and atttypmod passed to
> coerce_type_typmod() is equal, the function node to call bpchar()
> would not be added.

Um, what's wrong with that?  Seems to me that parse_coerce is doing
exactly what it's supposed to, ie, adding only length coercions
that are needed.

regards, tom lane



[HACKERS] length coerce for bpchar is broken since 7.0

2000-10-16 Thread Tatsuo Ishii

It seems the length coerce for bpchar is broken since 7.0.
In 6.5 when a string is inserted, bpchar() is called to properly clip
the string. However in 7.0 (and probably current) bpchar() is not
called anymore. 

coerce_type_typmod() calls exprTypmod(). exprTypmod() returns VARSIZE
of the bpchar data only if the data type is bpchar (if the data type
is varchar, exprTypmod just returns -1 and the parser add a function
node to call varchar(). so there is no problem for varchar). If
VARSIZE returned from exprTypmod() and atttypmod passed to
coerce_type_typmod() is equal, the function node to call bpchar()
would not be added.

I'm not sure if this was an intended efect of the change. Anyway we
have to do the length coerce for bpchar somewhere and I'm thinking now
is doing in bpcharin(). This would also solve the problem in copy in a
mail I have posted.

Comments?
--
Tatsuo Ishii



Re: [HACKERS] fkey + primary key does not work in current

2000-10-16 Thread Bruce Momjian

That's strange.  I didn't see it.  Can you send it over.  The archives
don't seem to be working again.

> 
> I believe that I sent a patch on Sep 17 for this to -patches although
> I don't know if anyone saw it (it's in the archives, so I know it
> went through).
> 
> Stephan Szabo
> [EMAIL PROTECTED]
> 
> On Mon, 16 Oct 2000, Bruce Momjian wrote:
> 
> > Has this been resolved?
> >
> > > On Fri, 15 Sep 2000, Tatsuo Ishii wrote:
> > > 
> > > > It seems that foreign key does not work in current, if specified with
> > > > primary key definition. Take a look at following example(works in
> > > > 7.0.2.):
> > > > 
> > > > test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, 
>PRIMARY KEY(ptest1, ptest2, ptest3) );
> > > > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' 
>for table 'pktable'
> > > > CREATE
> > > > test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, 
>primary key (ftest1,ftest2,ftest3,ftest4),  CONSTRAINT constrname3 FOREIGN 
>KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE);
> > > > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' 
>for table 'fktable'
> > > > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> > > > ERROR:  columns referenced in foreign key constraint not found.
> > > 
> > > Hmm, that's very strange.  I wonder which columns it think didn't exist.
> > > It shouldn't be checking the pktable in that case, which would imply
> > > it doesn't believe the existance of ftest1,ftest2,ftest3.  Probably
> > > a stupid mistake on my part.  As soon as I clear off space to compile
> > > current, I'll look.
> 
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] fkey + primary key does not work in current

2000-10-16 Thread Stephan Szabo


I believe that I sent a patch on Sep 17 for this to -patches although
I don't know if anyone saw it (it's in the archives, so I know it
went through).

Stephan Szabo
[EMAIL PROTECTED]

On Mon, 16 Oct 2000, Bruce Momjian wrote:

> Has this been resolved?
>
> > On Fri, 15 Sep 2000, Tatsuo Ishii wrote:
> > 
> > > It seems that foreign key does not work in current, if specified with
> > > primary key definition. Take a look at following example(works in
> > > 7.0.2.):
> > > 
> > > test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, 
>PRIMARY KEY(ptest1, ptest2, ptest3) );
> > > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for 
>table 'pktable'
> > > CREATE
> > > test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, 
>primary key (ftest1,ftest2,ftest3,ftest4),  CONSTRAINT constrname3 FOREIGN 
>KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE);
> > > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' for 
>table 'fktable'
> > > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> > > ERROR:  columns referenced in foreign key constraint not found.
> > 
> > Hmm, that's very strange.  I wonder which columns it think didn't exist.
> > It shouldn't be checking the pktable in that case, which would imply
> > it doesn't believe the existance of ftest1,ftest2,ftest3.  Probably
> > a stupid mistake on my part.  As soon as I clear off space to compile
> > current, I'll look.




Re: [HACKERS] The lightbulb just went on...

2000-10-16 Thread The Hermit Hacker

On Mon, 16 Oct 2000, Tom Lane wrote:

> The Hermit Hacker <[EMAIL PROTECTED]> writes:
> > Something to force a v7.0.3 ... ?
> 
> Yes.  We had plenty to force a 7.0.3 already, actually, but I was
> holding off recommending a release in hopes of finding Alfred's
> problem.

I thought so, about having plenty, but when I asked before SF, it sort of
fell on deaf ears, so figured you weren't ready yet :)

> I will get this patch made up tonight for REL7_0; if Alfred doesn't
> see more failures after running it for a few days, then let's move
> forward on a 7.0.3 release.

that works for me ... I'm in Montreal for the weekend, so if we can get it
out before Thursday, great, else we'll do it on Monday, 'k? 





Re: [HACKERS] Patch for TNS services

2000-10-16 Thread Bruce Momjian

Patch applied.  Can you send me the SGML diff?  I will merge them in.


> I've now prepared a polished and clean patch vs. 7.0.2. Who's gonna integrate 
> this patch in the CVS? I've no CVS access.
> 
> The docs are another problem. I've installed jade and most other SGML stuff 
> here, but "make user.html" fails with errors like :
> 
> jade:user.sgml:5:55:W: cannot generate system identifier for public text 
> "-//OASIS//DTD Dojade:user.sgml:41:0:E: reference to entity "BOOK" for which 
> no system identifier could be
> jade:user.sgml:5:0: entity was defined here
> jade:user.sgml:41:0:E: DTD did not contain element declaration for document 
> type name 
> 
> The patch is included as attachement (159 lines).
> 
> 
> The patch is included
> 
> Am Tue, 12 Sep 2000 schrieben Sie:
> > Sounds like people want it.  Can you polish it off, add SGML docs and
> > send it over?
> >
> > > -BEGIN PGP SIGNED MESSAGE-
> > >
> > > Last week I created a patch for the Postgres client side libraries to
> > > allow something like a (not so mighty) form of Oracle TNS, but nobody
> > > showed any interest. Currently, the patch is not perfect yet, but works
> > > fine for us. I want to avoid improving the patch if there is no interest
> > > in it, so if you think it might be a worthy improvement please drop me a
> > > line.
> > >
> > > It works like this:
> > > The patch allows to supply another parameter to the Postgres connect
> > > string, called "service". So, instead of having a connect string (e.g. in
> > > PHP) like "dbname=foo host=bar port=5433 user=foouser password=barpass"
> > > the string would be
> > > "service=stupid_name_here"
> > > or more often
> > > "service=stupid_name_here user=foouser password=barpass"
> > >
> > > There's a config file /etc/pg_service.conf, having an entry like:
> > > [stupid_name_here]
> > > dbname=foo
> > > host=bar
> > > port=5433
> > > 
> > >
> > > The advantage is you can go from one database host, database, port or
> > > whatever without having to touch the scripts or applications. We're
> > > currently in the process of migrating all of our PHP and Python scripts
> > > to another from localhost, port 5433 to another machine, port 5432 and
> > > it's not something I ever want to do again, I'd to change around 100
> > > files and I'm still not sure if I've missed one.
> > >
> > > The patch is client-side only, around 100 lines, needs no changes to the
> > > backend and is compatible with all applications supplying a connection
> > > string (not using PQsetdblogin)
> > >
> > > - --
> > > Why is it always Segmentation's fault?
> > > -BEGIN PGP SIGNATURE-
> > > Version: 2.6.3i
> > > Charset: noconv
> > >
> > > iQCVAwUBOa1MsQotfkegMgnVAQEIsAP+Na72pNdT+RoQcjuX5cn1TKkPlNAh9BV5
> > > kCNP+Zui6WfZSiA8RYPuruXF0QyEMPZZD6AI9Wqr5sQ75kVSb65uOt9rLrdS0bxA
> > > WTClNjlLKG3Rk1IGSFBm+C0p8lcA3AYTohHLhHB3q+WeLTneI5lJfwpo2AWyinQt
> > > 0k/1r6EwpUk=
> > > =+skX
> > > -END PGP SIGNATURE-
> >
> > [ Attachment, skipping... ]
> 
[ Attachment, skipping... ]


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



RE: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread Philip Warner

At 15:07 16/10/00 -0700, Mikheev, Vadim wrote:
>
>So, pg_dump should be preserved asis.
>

Just to clarify; I have no intention of doing anything nasty to pg_dump.
All I plan to do is rename the pg_restore to one of
pg_load/pg_import/pg_undump/pmud_gp, to make way for a WAL based restore
utility, although as Bruce suggests, this may be premature.




Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



Re: [HACKERS] The lightbulb just went on...

2000-10-16 Thread Tom Lane

The Hermit Hacker <[EMAIL PROTECTED]> writes:
> Something to force a v7.0.3 ... ?

Yes.  We had plenty to force a 7.0.3 already, actually, but I was
holding off recommending a release in hopes of finding Alfred's
problem.

I will get this patch made up tonight for REL7_0; if Alfred doesn't
see more failures after running it for a few days, then let's move
forward on a 7.0.3 release.

regards, tom lane



Re: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread Philip Warner

At 00:12 17/10/00 +0200, Peter Eisentraut wrote:
>
>Btw., it will still be possible to restore, er, reload, with psql, right?
>

Correct.



Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



Re: [HACKERS] Re: New relkind for views

2000-10-16 Thread Tom Lane

Mark Hollomon <[EMAIL PROTECTED]> writes:
 I say let them drop it with either one. 
>> 
>> I kinda like the 'drop index with drop index', 'drop table with drop
>> table' and 'drop view with drop view' groupings ... at least you are
>> pretty sure you haven't 'oopsed' in the process :)

> So the vote is now tied. Any other opinions

I vote for the fascist approach (command must agree with actual type
of object).  Seems safest.  Please make sure the error message is
helpful though, like "Use DROP SEQUENCE to drop a sequence".

regards, tom lane



Re: [HACKERS] The lightbulb just went on...

2000-10-16 Thread The Hermit Hacker


Something to force a v7.0.3 ... ?

On Mon, 16 Oct 2000, Tom Lane wrote:

> ... with a blinding flash ...
> 
> The VACUUM funnies I was complaining about before may or may not be real
> bugs, but they are not what's biting Alfred.  None of them can lead to
> the observed crashes AFAICT.
> 
> What's biting Alfred is the code that moves a tuple update chain, lines
> 1541 ff in REL7_0_PATCHES.  This sets up a pointer to a source tuple in
> "tuple".  Then it gets the destination page it plans to move the tuple
> to, and applies vc_vacpage to that page if it hasn't been done already.
> But when we're moving a tuple chain, *it is possible for the destination
> page to be the same as the source page*.  Since vc_vacpage applies
> PageRepairFragmentation, all the live tuples on the page may get moved.
> Afterwards, tuple.t_data is out of date and pointing at some random
> chunk of some other tuple.  The subsequent copy of the tuple copies
> garbage, which explains Alfred's several crashes in constructing index
> entries for the copied tuple (all of which bombed out from the
> index-build calls at lines 1634 ff, ie, for tuples being moved as part
> of a chain).  Once in a while, the obsolete pointer will be pointing at
> the real header of a different tuple --- perhaps even the place where we
> are about to put the copy.  This improbable case explains the one
> observed Assert crash in which a copied tuple's HEAP_MOVED_IN bit
> mysteriously got turned off.  Reason: it was cleared through the
> old-tuple pointer just after being set via the new-tuple one.
> 
> Proof that this is happening can be seen in the core dumps for Alfred's
> index-construction-crash cases: tuple.t_data does not point at the same
> place that the tuple.ip_posid'th page line item points at.  This could
> only happen if the page was reshuffled since the tuple pointer was set
> up.  The explanation for the Assert crash is a bit of a leap of faith,
> but I feel confident that it's right.
> 
> The solution is to do everything we're going to do with the source
> tuple, especially copying it and updating its state, *before* we apply
> vc_vacpage to the destination page.  Then we don't care if the source
> gets moved during vc_vacpage.
> 
> I will prepare a patch along this line and send it to Alfred for
> testing.
> 
>   regards, tom lane
> 
> 

Marc G. Fournier   ICQ#7615664   IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: [EMAIL PROTECTED]   secondary: scrappy@{freebsd|postgresql}.org 




Re: [HACKERS] Re: New relkind for views

2000-10-16 Thread Mark Hollomon

On Mon, Oct 16, 2000 at 08:41:43PM -0300, The Hermit Hacker wrote:
> On Mon, 16 Oct 2000, Bruce Momjian wrote:
> 
> > > "Hollomon, Mark" wrote:
> > > > 
> > > > Do we still want to be able to inherit from views?
> > > 
> > > Also:
> > > 
> > > Currently a view may be dropped with either 'DROP VIEW'
> > > or 'DROP TABLE'. Should this be changed?
> > 
> > I say let them drop it with either one. 
> 
> I kinda like the 'drop index with drop index', 'drop table with drop
> table' and 'drop view with drop view' groupings ... at least you are
> pretty sure you haven't 'oopsed' in the process :)
> 
> 

So the vote is now tied. Any other opinions

-- 
Mark Hollomon
[EMAIL PROTECTED]



Re: [HACKERS] fkey + primary key does not work in current

2000-10-16 Thread Bruce Momjian

Has this been resolved?


> 
> On Fri, 15 Sep 2000, Tatsuo Ishii wrote:
> 
> > It seems that foreign key does not work in current, if specified with
> > primary key definition. Take a look at following example(works in
> > 7.0.2.):
> > 
> > test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, 
>PRIMARY KEY(ptest1, ptest2, ptest3) );
> > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for 
>table 'pktable'
> > CREATE
> > test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, 
>primary key (ftest1,ftest2,ftest3,ftest4),  CONSTRAINT constrname3 FOREIGN 
>KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE);
> > NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' for 
>table 'fktable'
> > NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
> > ERROR:  columns referenced in foreign key constraint not found.
> 
> Hmm, that's very strange.  I wonder which columns it think didn't exist.
> It shouldn't be checking the pktable in that case, which would imply
> it doesn't believe the existance of ftest1,ftest2,ftest3.  Probably
> a stupid mistake on my part.  As soon as I clear off space to compile
> current, I'll look.
> 
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



RE: [HACKERS] Possible performance improvement: buffer replacemen t policy

2000-10-16 Thread Mikheev, Vadim

Thanks.

Vadim

> -Original Message-
> From: Tom Lane [mailto:[EMAIL PROTECTED]]
> Sent: Monday, October 16, 2000 5:40 PM
> To: Mikheev, Vadim
> Cc: 'Bruce Momjian'; [EMAIL PROTECTED]
> Subject: Re: [HACKERS] Possible performance improvement: buffer
> replacemen t policy 
> 
> 
> "Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
> >> Sounds like a perfect idea.  Good luck.  :-)
> 
> > Hmm, how much time will be required?
> > I integrate WAL right now and have to do significant changes
> > in bufmgr...
> 
> Don't worry about it, I am not planning to commit that code anytime
> soon.  (I have other stuff I want to fix in bufmgr, but I can wait
> for you to finish WAL first.)
> 
>   regards, tom lane
> 



Re: [HACKERS] Possible performance improvement: buffer replacemen t policy

2000-10-16 Thread Tom Lane

"Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
>> Sounds like a perfect idea.  Good luck.  :-)

> Hmm, how much time will be required?
> I integrate WAL right now and have to do significant changes
> in bufmgr...

Don't worry about it, I am not planning to commit that code anytime
soon.  (I have other stuff I want to fix in bufmgr, but I can wait
for you to finish WAL first.)

regards, tom lane



[HACKERS] The lightbulb just went on...

2000-10-16 Thread Tom Lane

... with a blinding flash ...

The VACUUM funnies I was complaining about before may or may not be real
bugs, but they are not what's biting Alfred.  None of them can lead to
the observed crashes AFAICT.

What's biting Alfred is the code that moves a tuple update chain, lines
1541 ff in REL7_0_PATCHES.  This sets up a pointer to a source tuple in
"tuple".  Then it gets the destination page it plans to move the tuple
to, and applies vc_vacpage to that page if it hasn't been done already.
But when we're moving a tuple chain, *it is possible for the destination
page to be the same as the source page*.  Since vc_vacpage applies
PageRepairFragmentation, all the live tuples on the page may get moved.
Afterwards, tuple.t_data is out of date and pointing at some random
chunk of some other tuple.  The subsequent copy of the tuple copies
garbage, which explains Alfred's several crashes in constructing index
entries for the copied tuple (all of which bombed out from the
index-build calls at lines 1634 ff, ie, for tuples being moved as part
of a chain).  Once in a while, the obsolete pointer will be pointing at
the real header of a different tuple --- perhaps even the place where we
are about to put the copy.  This improbable case explains the one
observed Assert crash in which a copied tuple's HEAP_MOVED_IN bit
mysteriously got turned off.  Reason: it was cleared through the
old-tuple pointer just after being set via the new-tuple one.

Proof that this is happening can be seen in the core dumps for Alfred's
index-construction-crash cases: tuple.t_data does not point at the same
place that the tuple.ip_posid'th page line item points at.  This could
only happen if the page was reshuffled since the tuple pointer was set
up.  The explanation for the Assert crash is a bit of a leap of faith,
but I feel confident that it's right.

The solution is to do everything we're going to do with the source
tuple, especially copying it and updating its state, *before* we apply
vc_vacpage to the destination page.  Then we don't care if the source
gets moved during vc_vacpage.

I will prepare a patch along this line and send it to Alfred for
testing.

regards, tom lane



RE: [HACKERS] Possible performance improvement: buffer replacement policy

2000-10-16 Thread Mikheev, Vadim

> > It looks like it wouldn't take too much work to replace 
> > shared buffers on the basis of LRU-2 instead of LRU, so
> > I'm thinking about trying it.
> > 
> > Has anyone looked into this area?  Is there a better method to try?
> 
> Sounds like a perfect idea.  Good luck.  :-)

Hmm, how much time will be required?
I integrate WAL right now and have to do significant changes
in bufmgr...

Vadim





[HACKERS] Re: getting local domain to get attached through sendmail ...

2000-10-16 Thread Per Hedeland

I wonder how many "you're not allowed to post to this list" bounces I'll
get from answering this... Please, when you send messages to
sendmail.org, send them *only* there, not to various mailing lists as
well.

The Hermit Hacker <[EMAIL PROTECTED]> wrote:
>   I'm trying to get the committers mailing list to work, and the
>"break" is in sendmail, as far as I can tell.  Basically, its taking
>'locally posted messages' and not adding a domain to the back of it, so
>that majordomo sees them as:
>
>--== Error when connecting: Invalid address: "Marc G. Fournier" 
>You did not include a hostname as part of the address.
> at /usr/local/majordomo/bin/mj_queuerun line 470
>
>I've tried adding 'FEATURE(`always_add_domain')' to my m4 config file, but
>that doesn't appear to be helping, but figure it probably somethign really
>obvious :(

Did you put it *before* the MAILER lines, as described in cf/README?

--Per Hedeland



Re: [HACKERS] Possible performance improvement: buffer replacement policy

2000-10-16 Thread Bruce Momjian

> (If you subscribe to the ACM digital library, you can get a PDF of this
> from there.)  This article argues that standard LRU buffer management is
> inherently not great for database caches, and that it's much better to
> replace pages on the basis of time since the K'th most recent reference,
> not just time since the most recent one.  K=2 is enough to get most of
> the benefit.  The big win is that you are measuring an actual page
> interreference time (between the last two references) and not just
> dealing with a lower-bound guess on the interreference time.  Frequently
> used pages are thus much more likely to stay in cache.
> 
> It looks like it wouldn't take too much work to replace shared buffers
> on the basis of LRU-2 instead of LRU, so I'm thinking about trying it.
> 
> Has anyone looked into this area?  Is there a better method to try?

Sounds like a perfect idea.  Good luck.  :-)

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] Re: New relkind for views]

2000-10-16 Thread Bruce Momjian

> On Mon, 16 Oct 2000, Bruce Momjian wrote:
> 
> > > "Hollomon, Mark" wrote:
> > > > 
> > > > Do we still want to be able to inherit from views?
> > > 
> > > Also:
> > > 
> > > Currently a view may be dropped with either 'DROP VIEW'
> > > or 'DROP TABLE'. Should this be changed?
> > 
> > I say let them drop it with either one. 
> 
> I kinda like the 'drop index with drop index', 'drop table with drop
> table' and 'drop view with drop view' groupings ... at least you are
> pretty sure you haven't 'oopsed' in the process :)

Good point.  Oops is bad.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] Re: New relkind for views

2000-10-16 Thread The Hermit Hacker

On Mon, 16 Oct 2000, Bruce Momjian wrote:

> > "Hollomon, Mark" wrote:
> > > 
> > > Do we still want to be able to inherit from views?
> > 
> > Also:
> > 
> > Currently a view may be dropped with either 'DROP VIEW'
> > or 'DROP TABLE'. Should this be changed?
> 
> I say let them drop it with either one. 

I kinda like the 'drop index with drop index', 'drop table with drop
table' and 'drop view with drop view' groupings ... at least you are
pretty sure you haven't 'oopsed' in the process :)





[HACKERS] Re: [GENERAL] PL/Perl compilation error

2000-10-16 Thread Bruce Momjian

I can not apply this.  Seems it has changed in the current tree.  Here
is the current plperl.c file.  

> Bruce Momjian wrote:
> 
> > Can you send me a patch?
> >
> > > Hi,
> > >
> > > I have take a look to the source code concerning PL/Perl, it seems that 2 
>variables
> > > have a bad call : errgv and na.
> > >
> > > If you replace them by their normal call (in 5.6.0) PL_errgv and PL_na you will 
>get
> > > success to compile the lib plperl.so.
> > >
> 
> This patch (simple diff) applies to postgresql-7.0.2.
> See attachment...
> 
> Regards
> 
> Gilles DAROLD
> 
> 
> 

> 328c328
> < if (SvTRUE(GvSV(PL_errgv)))
> ---
> > if (SvTRUE(GvSV(errgv)))
> 334c334
> < elog(ERROR, "creation of function failed : %s", SvPV(GvSV(PL_errgv), 
>PL_na));
> ---
> > elog(ERROR, "creation of function failed : %s", SvPV(GvSV(errgv), na));
> 444c444
> < if (SvTRUE(GvSV(PL_errgv)))
> ---
> > if (SvTRUE(GvSV(errgv)))
> 450c450
> < elog(ERROR, "plperl : error from function : %s", SvPV(GvSV(PL_errgv), 
>PL_na));
> ---
> > elog(ERROR, "plperl : error from function : %s", SvPV(GvSV(errgv), 
>na));
> 654c654
> < (SvPV(perlret, PL_na),
> ---
> > (SvPV(perlret, na),
> 2192c2192
> < output = perl_eval_pv(SvPV(output, PL_na), TRUE);
> ---
> > output = perl_eval_pv(SvPV(output, na), TRUE);


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026


/**
 * plperl.c - perl as a procedural language for PostgreSQL
 *
 * IDENTIFICATION
 *
 *This software is copyrighted by Mark Hollomon
 *   but is shameless cribbed from pltcl.c by Jan Weick.
 *
 *The author hereby grants permission  to  use,  copy,  modify,
 *distribute,  and  license this software and its documentation
 *for any purpose, provided that existing copyright notices are
 *retained  in  all  copies  and  that  this notice is included
 *verbatim in any distributions. No written agreement, license,
 *or  royalty  fee  is required for any of the authorized uses.
 *Modifications to this software may be  copyrighted  by  their
 *author  and  need  not  follow  the licensing terms described
 *here, provided that the new terms are  clearly  indicated  on
 *the first page of each file where they apply.
 *
 *IN NO EVENT SHALL THE AUTHOR OR DISTRIBUTORS BE LIABLE TO ANY
 *PARTY  FOR  DIRECT,   INDIRECT,   SPECIAL,   INCIDENTAL,   OR
 *CONSEQUENTIAL   DAMAGES  ARISING  OUT  OF  THE  USE  OF  THIS
 *SOFTWARE, ITS DOCUMENTATION, OR ANY DERIVATIVES THEREOF, EVEN
 *IF  THE  AUTHOR  HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH
 *DAMAGE.
 *
 *THE  AUTHOR  AND  DISTRIBUTORS  SPECIFICALLY   DISCLAIM   ANY
 *WARRANTIES,  INCLUDING,  BUT  NOT  LIMITED  TO,  THE  IMPLIED
 *WARRANTIES  OF  MERCHANTABILITY,  FITNESS  FOR  A  PARTICULAR
 *PURPOSE,  AND NON-INFRINGEMENT.  THIS SOFTWARE IS PROVIDED ON
 *AN "AS IS" BASIS, AND THE AUTHOR  AND  DISTRIBUTORS  HAVE  NO
 *OBLIGATION   TO   PROVIDE   MAINTENANCE,   SUPPORT,  UPDATES,
 *ENHANCEMENTS, OR MODIFICATIONS.
 *
 * IDENTIFICATION
 *$Header: /home/projects/pgsql/cvsroot/pgsql/src/pl/plperl/plperl.c,v 1.13 
2000/09/12 04:28:30 momjian Exp $
 *
 **/


/* system stuff */
#include 
#include 
#include 
#include 
#include 
#include 
#include 

/* postgreSQL stuff */
#include "executor/spi.h"
#include "commands/trigger.h"
#include "utils/elog.h"
#include "fmgr.h"
#include "access/heapam.h"

#include "tcop/tcopprot.h"
#include "utils/syscache.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_type.h"

/* perl stuff */
/*
 * Evil Code Alert
 *
 * both posgreSQL and perl try to do 'the right thing'
 * and provide union semun if the platform doesn't define
 * it in a system header.
 * psql uses HAVE_UNION_SEMUN
 * perl uses HAS_UNION_SEMUN
 * together, they cause compile errors.
 * If we need it, the psql headers above will provide it.
 * So we tell perl that we have it.
 */
#ifndef HAS_UNION_SEMUN
#define HAS_UNION_SEMUN
#endif
#include "EXTERN.h"
#include "perl.h"


/**
 * The information we cache about loaded procedures
 **/
typedef struct plperl_proc_desc
{
char   *proname;
FmgrInforesult_in_func;
Oid result_in_elem;
int result_in_len;
int narg

Re: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread The Hermit Hacker

On Tue, 17 Oct 2000, Peter Eisentraut wrote:

> Philip Warner writes:
> 
> > >I like the pg_{import,export} names myself ... *nod*
> > 
> > Sounds fine also; but we have compatibility issues in that we still need
> > pg_dump. Maybe just a symbolic link to pg_export.
> 
> I'm not so fond of changing a long-established program name for the sake
> of ethymological correctness or consistency with other products (yeah,
> right).  I got plenty of suggestions if you want to start that.  I say
> stick to pg_dump[all], and name the inverse pg_undump, pg_load, or
> pmud_gp.

pmud_gp? *raised eyebrow*





Re: [HACKERS] Indexing for geographical objects

2000-10-16 Thread Bruce Momjian

We certainly would like to have them.  Can you send a patch that applies
against our current CVS snapshot.

[ Charset ISO-8859-1 unsupported, converting... ]
> Hi,
> 
> I'm developping a geographical object type, very close to the geographic
> type of PG. For the moment it is set up as external functions...
> 
> I would like to add indexing capabilities, and I have seen that indexing for
> PG geographical objects is on the TODO list for 7.1. 
> 
> I would like to get in touch with the person maintaining this part of the
> code, and see if I could transfer some of these algorithms to my code...
> 
> At the end, these new geo objects could be incorporated in PG, but that up
> to the PG dev team...
> 
> Cheers..
> 
> Franck Martin
> Database Development Officer
> SOPAC South Pacific Applied Geoscience Commission
> Fiji
> E-mail: [EMAIL PROTECTED]  
> Web site: http://www.sopac.org/  
> 
> This e-mail is intended for its recipients only. Do not forward this
> e-mail without approval. The views expressed in this e-mail may not be
> neccessarily the views of SOPAC.
> 
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] minor fixes for regress

2000-10-16 Thread Bruce Momjian

Applied and updated.



> I wasn't too sure where to mail this.
> 
> I have noticed that there are some identical files in
> postgresql-7.0.2/src/test/regress/expected/
> 
> > diff float8-cygwin.out float8-small-is-zero.out #I recommend deleting
> float8-cygwin.out
> > diff geometry-cygwin-precision.out geometry-solaris-precision.out #I
> recommend deleting geometry-cygwin-precision.out
> 
> below is the diff of postgresql-7.0.2/src/test/regress/resultmap
> that has the above files deleted plus the addition of an alpha regression
> test built with alphaev56-dec-osf4.0e/2.95.2/ . The alpha geometry
> regression file is attached
> 
> 11c11
> < float8/i.86-pc-cygwin*=float8-cygwin
> ---
> > float8/i.86-pc-cygwin*=float8-small-is-zero
> 18c18
> < geometry/i.86-pc-cygwin*=geometry-cygwin-precision
> ---
> > geometry/i.86-pc-cygwin*=geometry-solaris-precision
> 21a22
> > geometry/alpha.*-dec-osf=geometry-alpha-precision
> 
> 
> 
> Ricardo Muggli
> Systems Manager
> Information and Technology Services
> Minnesota State University, Mankato
Content-Description: 

[ Attachment, skipping... ]


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



RE: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread Mikheev, Vadim

> >I like the pg_{import,export} names myself ... *nod*
> >
> 
> Sounds fine also; but we have compatibility issues in that we 
> still need pg_dump. Maybe just a symbolic link to pg_export.

Yes, we still need in pg_dump, because of pg_dump is thing
quite different from WAL based backup/restore. pg_dump
is utility to export data in system independant format
using standard SQL commands (with COPY extension) and WAL
based backup system is to export *physical* data files
(and logs). So, pg_dump should be preserved asis.

Vadim



Re: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread Bruce Momjian

> Philip Warner writes:
> 
> > >I like the pg_{import,export} names myself ... *nod*
> > 
> > Sounds fine also; but we have compatibility issues in that we still need
> > pg_dump. Maybe just a symbolic link to pg_export.
> 
> I'm not so fond of changing a long-established program name for the sake
> of ethymological correctness or consistency with other products (yeah,

Agreed.



-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread Peter Eisentraut

Philip Warner writes:

> >I like the pg_{import,export} names myself ... *nod*
> 
> Sounds fine also; but we have compatibility issues in that we still need
> pg_dump. Maybe just a symbolic link to pg_export.

I'm not so fond of changing a long-established program name for the sake
of ethymological correctness or consistency with other products (yeah,
right).  I got plenty of suggestions if you want to start that.  I say
stick to pg_dump[all], and name the inverse pg_undump, pg_load, or
pmud_gp.

Btw., it will still be possible to restore, er, reload, with psql, right?

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/




Re: AW: [HACKERS] new relkind for view

2000-10-16 Thread Bruce Momjian

TODO updated.

> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Added to TODO>
> >> 
> >> I think this is for new todo items:
> >> create insert, update and delete rules for simple one table views
> >> change elog for complex view ins|upd|del to "cannot {ins|upd|del}
> >> [into|from] complex view without an on {ins|upd|del} rule"
> >> add the functionality for "with check option" clause of create view
> 
> The second of these three items is done already (in the rewriter,
> not the executor):
> 
> regression=# create view vv1 as select * from int4_tbl;
> CREATE
> regression=# insert into vv1 values (33);
> ERROR:  Cannot insert into a view without an appropriate rule
> 
>   regards, tom lane
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: AW: [HACKERS] new relkind for view

2000-10-16 Thread Tom Lane

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Added to TODO>
>> 
>> I think this is for new todo items:
>> create insert, update and delete rules for simple one table views
>> change elog for complex view ins|upd|del to "cannot {ins|upd|del}
>> [into|from] complex view without an on {ins|upd|del} rule"
>> add the functionality for "with check option" clause of create view

The second of these three items is done already (in the rewriter,
not the executor):

regression=# create view vv1 as select * from int4_tbl;
CREATE
regression=# insert into vv1 values (33);
ERROR:  Cannot insert into a view without an appropriate rule

regards, tom lane



Re: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread Philip Warner

At 10:08 16/10/00 -0300, The Hermit Hacker wrote:
>
>I like the pg_{import,export} names myself ... *nod*
>

Sounds fine also; but we have compatibility issues in that we still need
pg_dump. Maybe just a symbolic link to pg_export.



Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
 |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/



[HACKERS] Re: [BUGS] unique/references not honored when inheriting tables

2000-10-16 Thread Stephan Szabo


AFAIK, yes.  Inheriting unique constraints would be a 
special case of inheriting indexes theoretically, since I'd
assume that inheriting a unique column should mean unique
through entire inheritance tree.  References is similar, 
we'd need to do something to inherit the triggers, and
referencing to an inheritance tree needs the unique 
constraint on that entire tree.

I'm not sure what it would take to do a trigger over an
inheritance tree. I guess the other option is to create
appropriate triggers when you inherit and having alter
table add/drop constraint do the same thing).

On Mon, 16 Oct 2000, Bruce Momjian wrote:

> Is this still true in 7.1?
> 
> 
> > Helge Bahmann ([EMAIL PROTECTED]) reports a bug with a severity of 4
> > The lower the number the more severe it is.
> > 
> > Short Description
> > unique/references not honored when inheriting tables
> > 
> > Long Description
> > If a table inherits fields carrying the "references" or "unique" constraint, they 
>are not honoured but silently dropped. It is necessary to manually create the 
>triggers/indices.
> > 
> > It would be nice if it were possible to create an index across a table and all 
>sub-tables.
> > 
> > 
> > Sample Code
> > CREATE TABLE foo(id int unique)
> > CREATE TABLE bar() INHERITS (foo)
> > INSERT INTO bar VALUES(1)
> > INSERT INTO bar VALUES(1)
> > 
> > 
> > No file was uploaded with this report
> > 
> > 
> 
> 
> -- 
>   Bruce Momjian|  http://candle.pha.pa.us
>   [EMAIL PROTECTED]   |  (610) 853-3000
>   +  If your life is a hard drive, |  830 Blythe Avenue
>   +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026
> 





Re: AW: [HACKERS] new relkind for view

2000-10-16 Thread Bruce Momjian

Added to TODO>

[ Charset ISO-8859-1 unsupported, converting... ]
> 
> > > 2. The executor complains if a DELETE or
> > >   INSERT references a view.
> 
> I think this is for new todo items:
>   create insert, update and delete rules for simple one table views
>   change elog for complex view ins|upd|del to "cannot {ins|upd|del}
> [into|from] complex view without an on {ins|upd|del} rule"
>   add the functionality for "with check option" clause of create view
> 
> Andreas
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [PATCHES] Re: [HACKERS] when does CREATE VIEW not create a view?

2000-10-16 Thread Ross J. Reedstrom

On Mon, Oct 16, 2000 at 03:31:08PM -0500, Ross J. Reedstrom wrote:
> On Mon, Oct 16, 2000 at 12:22:23PM -0400, Bruce Momjian wrote:
> > OK, the bad news is that this does not apply to the current development
> > tree.  Ross, can you make a more corrent one?  Sorry.
> 
> I think it won't apply because it's already in there. There were also
> subsequent fixes to how pg_dump deals with views by Phil.

Err, I mean fixes by Philip to how pg_dump deals with views. AFAIK,
there's no special cases in the code for views created by Philip. ;->

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.



Re: [HACKERS] when does CREATE VIEW not create a view?

2000-10-16 Thread Ross J. Reedstrom

On Mon, Oct 16, 2000 at 12:22:23PM -0400, Bruce Momjian wrote:
> OK, the bad news is that this does not apply to the current development
> tree.  Ross, can you make a more corrent one?  Sorry.

I think it won't apply because it's already in there. There were also
subsequent fixes to how pg_dump deals with views by Phil.

Ross
-- 
Open source code is like a natural resource, it's the result of providing
food and sunshine to programmers, and then staying out of their way.
[...] [It] is not going away because it has utility for both the developers 
and users independent of economic motivations.  Jim Flynn, Sunnyvale, Calif.



Re: [HACKERS] TODO list updates

2000-10-16 Thread Bruce Momjian

Thanks.  TODO updated.

> 
> * Disallow LOCK on view
>   change to
> * -Disallow LOCK on view (Mark H)
>   well, at least when my patch is applied :)
> 
> 
> * Allow SQL function indexes
>   This seems to work in the CVS code, or I have misunderstood:
>   CREATE TABLE t ( a int);
>   CREATE FUNCTION mod5(int) RETURNS int AS 'select $1 % 5' LANGUAGE 'sql';
>   CREATE INDEX sql_index ON t ( mod5(a) );
> 
> 
> * Add ALTER TABLE command to change table ownership
>   Dibs on this.
> 
> 
> -- 
> Mark Hollomon
> [EMAIL PROTECTED]
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] Re: ?????: ?????: WAL and indexes (Re: [HACKERS] WAL status& todo)

2000-10-16 Thread Bruce Momjian

> "Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
> > And how could I use such records on recovery
> > being unable to know what data columns represent
> > keys, what functions should be used for ordering?
> 
> Um, that's not built into the index either, is it?  OK, you win ...
> 
> I'm still nervous about how we're going to test the WAL code adequately
> for the lesser-used index types.  Any ideas out there?

Wait for bug reports?  :-)

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



[HACKERS] Ответ: [HACKERS] Otvet: WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Mikheev, Vadim

>> One of the purposes of WAL is immediate removing tuples 
>> inserted by aborted xactions. I want make VACUUM
>> *optional* in future - space must be available for
>> reusing without VACUUM. And this is first, very small,
>> step in this direction.
>
>Why would vacuum become optional?  Would WAL offer an option to
>not reclaim free space?  We're hoping that vacuum becomes unneeded

Reclaiming free space is issue of storage manager, as
I said here many times. WAL is just Write A-head Log
(first write to log then to data files, to have ability
to recover using log data) and for matter of space it can
only help to delete tuples inserted by aborted transaction.

>when postgresql is run with some flag indicating that we're
>uninterested in time travel.

Time travel is gone ~ 3 years ago and vacuum was needed all
these years and will be needed to reclaim space in 7.1

>How much longer do you estimate until you can make it work that way?

Hopefully in 7.2

Vadim




[HACKERS] Re: Ответ: Ответ: WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Tom Lane

"Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
> And how could I use such records on recovery
> being unable to know what data columns represent
> keys, what functions should be used for ordering?

Um, that's not built into the index either, is it?  OK, you win ...

I'm still nervous about how we're going to test the WAL code adequately
for the lesser-used index types.  Any ideas out there?

regards, tom lane



[HACKERS] Ответ: Ответ: WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Mikheev, Vadim

>>> I don't understand why WAL needs to log internal operations of any of
>>> the index types.  Seems to me that you could treat indexes as black
>>> boxes that are updated as side effects of WAL log items for heap tuples:
>>> when adding a heap tuple as a result of a WAL item, you just call the
>>> usual index insert routines, and when deleting a heap tuple as a result
>>
>> On recovery backend *can't* use any usual routines:
>> system catalogs are not available.
>
>OK, good point, but that just means you can't use the catalogs to
>discover what indexes exist for a given table.  You could still create
>log entries that look like "insert indextuple X into index Y" without
>any further detail.

And how could I use such records on recovery
being unable to know what data columns represent
keys, what functions should be used for ordering?

>>> the index is corrupt and rebuild it from scratch, using Hiroshi's
>>> index-rebuild code.
>>
>> How fast is rebuilding of index for table with 10^7 records?
>
>It's not fast, of course.  But the point is that you should seldom
>have to do it.

With WAL system writes lazy and as result
probability to see "begin update" confirmation
without "done update" will be high, very high
(only log records go to disk on commit, data blocks
will be forced to disk on checkpoints - each 3-5
minutes - only).

>> I agree to consider rtree/hash/gist as experimental
>> index access methods BUT we have to have at least
>> *one* reliable index AM with short down time/
>> fast recovery.
>
>With all due respect, I wonder just how "reliable" btree WAL undo/redo
>will prove to be ... let alone the other index types.  I worry that
>this approach is putting too much emphasis on making it fast, and not
>enough on making it right.

This approach (logging all index changes) is *standard*
WAL approach and is reliable (but implementation may be
not of course -:)). This is what I've seen in books,
I didn't invent anything new and special here.
Tom, can you implement (or spend a some time for design)
hash redo/undo with "black box approach" so we could
see how good is it? I still miss are you going to use
begin/done update or "insert tuple X into index Y"
records.

Vadim




Re: [HACKERS] Yet another LIKE-indexing scheme

2000-10-16 Thread Tom Lane

> Can you give me a TODO item?

* Fix LIKE indexing optimization for non-ASCII locales

regards, tom lane



Re: [HACKERS] Isn't non-TEST_AND_SET code long dead?

2000-10-16 Thread Bruce Momjian

> Mike Mascari <[EMAIL PROTECTED]> writes:
> > On a somewhat related note, what about the NO_SECURITY defines
> > strewn throughout the backend? Does anyone run the server with
> > NO_SECURITY defined? And if so, what benefit is that over just
> > running with everything owned by the same user?
> 
> I suppose the idea was to avoid expending *any* cycles on security
> checks if you didn't need them in your particular situation.  But
> offhand I've never heard of anyone actually using the feature.  I'm
> dubious whether the amount of time saved would be worth the trouble.

NO_SECURITY define removed.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] Yet another LIKE-indexing scheme

2000-10-16 Thread Bruce Momjian

Can you give me a TODO item?


> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Any status on this?
> 
> Still broken, no known fix short of disabling LIKE optimization in
> non-ASCII locales ...
> 
>   regards, tom lane
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



RE: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread merlin

> Both options are in Oracle now, as proudly documented in their
> freely accessible on-line documentation.  It is very possible
> they didn't implement it until version 8, i.e. until a couple of years
> ago.

FYI:  ALTER TABLE DROP COLUMN was added as of 8 / 8i according to our
Oracle DBA.

- merlin




[HACKERS] Re: [BUGS] unique/references not honored when inheriting tables

2000-10-16 Thread Bruce Momjian

Is this still true in 7.1?


> Helge Bahmann ([EMAIL PROTECTED]) reports a bug with a severity of 4
> The lower the number the more severe it is.
> 
> Short Description
> unique/references not honored when inheriting tables
> 
> Long Description
> If a table inherits fields carrying the "references" or "unique" constraint, they 
>are not honoured but silently dropped. It is necessary to manually create the 
>triggers/indices.
> 
> It would be nice if it were possible to create an index across a table and all 
>sub-tables.
> 
> 
> Sample Code
> CREATE TABLE foo(id int unique)
> CREATE TABLE bar() INHERITS (foo)
> INSERT INTO bar VALUES(1)
> INSERT INTO bar VALUES(1)
> 
> 
> No file was uploaded with this report
> 
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] Yet another LIKE-indexing scheme

2000-10-16 Thread Tom Lane

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Any status on this?

Still broken, no known fix short of disabling LIKE optimization in
non-ASCII locales ...

regards, tom lane



Re: [HACKERS] Re: Otvet: WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Alfred Perlstein

* Tom Lane <[EMAIL PROTECTED]> [001016 09:47] wrote:
> "Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
> >> I don't understand why WAL needs to log internal operations of any of
> >> the index types.  Seems to me that you could treat indexes as black
> >> boxes that are updated as side effects of WAL log items for heap tuples:
> >> when adding a heap tuple as a result of a WAL item, you just call the
> >> usual index insert routines, and when deleting a heap tuple as a result
> 
> > On recovery backend *can't* use any usual routines:
> > system catalogs are not available.
> 
> OK, good point, but that just means you can't use the catalogs to
> discover what indexes exist for a given table.  You could still create
> log entries that look like "insert indextuple X into index Y" without
> any further detail.

One thing you guys may wish to consider is selectively fsyncing on
system catelogs and marking them dirty when opened for write:

postgres:  i need to write to a critical table...
opens table, marks dirty
completes operation and marks undirty and fsync

-or-

postgres:  i need to write to a critical table...
opens table, marks dirty
crash, burn, smoke (whatever)

Now you may still have the system tables broken, however the chances
of that may be siginifigantly reduced depending on how often writes
must be done to them.

It's a hack, but depending on the amount of writes done to critical
tables it may reduce the window for these inconvient situations 
signifigantly.

-- 
-Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
"I have the heart of a child; I keep it in a jar on my desk."



Re: [HACKERS] Yet another LIKE-indexing scheme

2000-10-16 Thread Bruce Momjian

Any status on this?


> Erich Stamberger <[EMAIL PROTECTED]> writes:
> >> Our existing code fails because it generates WHERE name >= 'Czec' AND
> >> name < 'Czed'; it will therefore not find names beginning 'Czech'
> >> because those are in another part of the index, between 'Czeh' and
> >> 'Czei'.  But WHERE name >= 'Cze' AND name < 'Czf' would work.
> 
> > The Problem is: What tells us, that it is 'f' which sorts
> > after 'e' in that locale?
> 
> We keep trying until we find a character that *does* sort after 'e'.
> I did say I was assuming that people had read the previous discussion
> and knew what the existing approach was ;-)
> 
> However I've since thought of a different counterexample: if the LIKE
> pattern is 'Czech%' and we strip off the 'h', we lose since we'll be
> looking between 'Czec' and 'Czed' but the desired strings are in the
> index between 'Czeh' and 'Czei'.  Back to the drawing board...
> 
>   regards, tom lane
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



[HACKERS] Re: Ответ: [HACKERS] Possible performance improvement: buffer replacement policy

2000-10-16 Thread Tom Lane

"Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
> Excuse me but what is LRU-2?

Like LRU, but using time to second most recent reference, instead of
most recent reference, to sort the buffers for recycling.  This gives
a more robust statistic about how often the page is actually being
touched.  (Or that's the theory anyway.)

> I know that in Oracle unused buffers are not in
> simple LRU list: Oracle tries to postpone writes
> as long as possible -:)

Manage dirty buffers separately from clean ones, you mean?  Hm, we could
do that.  With WAL it might even make sense, though before we tended to
flush dirty buffers so fast it would hardly matter.

regards, tom lane



Re: [HACKERS] Full text indexing (Question/request)

2000-10-16 Thread Bruce Momjian

See contrib/fulltextindex.

[ Charset ISO-8859-1 unsupported, converting... ]
> I didn't see any mention of it on the TODO so I thought I'd ask if anyone
> had thought about full test indexing for 7.1 (I'm guessing not)..
> 
> If not, I'd like to suggest it be put on the TODO -- if nothing else so
> someone could pick it up in the far future if they wanted to.. It doesn't
> seem like too many are worried about it so the request is pretty selfish,
> though I'm sure it would help many people especially after 7.1 and TOAST
> make text fields unlimited in size.
> 
> Thanks!
> 
> -Mitch
> 
> 
> 


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



[HACKERS] Re: Ответ: WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Tom Lane

"Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
>> I don't understand why WAL needs to log internal operations of any of
>> the index types.  Seems to me that you could treat indexes as black
>> boxes that are updated as side effects of WAL log items for heap tuples:
>> when adding a heap tuple as a result of a WAL item, you just call the
>> usual index insert routines, and when deleting a heap tuple as a result

> On recovery backend *can't* use any usual routines:
> system catalogs are not available.

OK, good point, but that just means you can't use the catalogs to
discover what indexes exist for a given table.  You could still create
log entries that look like "insert indextuple X into index Y" without
any further detail.

>> the index is corrupt and rebuild it from scratch, using Hiroshi's
>> index-rebuild code.

> How fast is rebuilding of index for table with 10^7 records?

It's not fast, of course.  But the point is that you should seldom
have to do it.

> I agree to consider rtree/hash/gist as experimental
> index access methods BUT we have to have at least
> *one* reliable index AM with short down time/
> fast recovery.

With all due respect, I wonder just how "reliable" btree WAL undo/redo
will prove to be ... let alone the other index types.  I worry that
this approach is putting too much emphasis on making it fast, and not
enough on making it right.

regards, tom lane



Re: [HACKERS] INHERITS doesn't offer enough functionality

2000-10-16 Thread Alfred Perlstein

* Bruce Momjian <[EMAIL PROTECTED]> [001016 08:55] wrote:
> > Alfred Perlstein wrote:
> > > 
> > > I noticed that INHERITS doesn't propogate indecies, It'd be nice
> > > if there was an toption to do so.
> > 
> > Yep it would. Are you volunteering?
> > 
> 
> Added to TODO:
> 
>   * Allow inherited tables to inherit index

Thank you, it's not a big problem that this doesn't happen, but it'd
be nice to see it as an option when creating a table via inheritance.

What about RULEs?  I wouldn't really have a use for that but others
might.

-- 
-Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
"I have the heart of a child; I keep it in a jar on my desk."



Re: [HACKERS] Otvet: WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Alfred Perlstein

* Mikheev, Vadim <[EMAIL PROTECTED]> [001016 09:33] wrote:
> >I don't understand why WAL needs to log internal operations of any of
> >the index types.  Seems to me that you could treat indexes as black
> >boxes that are updated as side effects of WAL log items for heap tuples:
> >when adding a heap tuple as a result of a WAL item, you just call the
> >usual index insert routines, and when deleting a heap tuple as a result
> 
> On recovery backend *can't* use any usual routines:
> system catalogs are not available.
> 
> >of undoing a WAL item, you mark the tuple invalid but don't physically
> >remove it till VACUUM (thus no need to worry about its index entries).
> 
> One of the purposes of WAL is immediate removing tuples 
> inserted by aborted xactions. I want make VACUUM
> *optional* in future - space must be available for
> reusing without VACUUM. And this is first, very small,
> step in this direction.

Why would vacuum become optional?  Would WAL offer an option to
not reclaim free space?  We're hoping that vacuum becomes unneeded
when postgresql is run with some flag indicating that we're
uninterested in time travel.

How much longer do you estimate until you can make it work that way?

thanks,
-Alfred



[HACKERS] Ответ: [HACKERS] New file naming

2000-10-16 Thread Mikheev, Vadim

Great & Thanks! And then we'll have to log all files
to be removed as part of xaction commit record.

Vadim

> - éÓÈÏÄÎÏÅ ÓÏÏÂÝÅÎÉÅ -
> ïÔ:   Tom Lane [SMTP:[EMAIL PROTECTED]]
> ïÔÐÒÁ×ÌÅÎÏ:   16 ieoya?y 2000 a. 8:59
> ëÏÍÕ: Mikheev, Vadim
> ëÏÐÉÑ:[EMAIL PROTECTED]
> ôÅÍÁ: Re: [HACKERS] New file naming 
> 
> "Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
> > ALTER TABLE RENAME is rollback-able now. I think that
> > it's very easy to make DROP TABLE rollback-able too
> > (file should be removed after xaction committed) but
> > I have no time to deal with this currently.
> 
> I will make sure that works before release.
> 
>   regards, tom lane



[HACKERS] Full text indexing (Question/request)

2000-10-16 Thread Mitch Vincent

I didn't see any mention of it on the TODO so I thought I'd ask if anyone
had thought about full test indexing for 7.1 (I'm guessing not)..

If not, I'd like to suggest it be put on the TODO -- if nothing else so
someone could pick it up in the far future if they wanted to.. It doesn't
seem like too many are worried about it so the request is pretty selfish,
though I'm sure it would help many people especially after 7.1 and TOAST
make text fields unlimited in size.

Thanks!

-Mitch





[HACKERS] Ответ: [HACKERS] Possible performance improvement: buffer replacement policy

2000-10-16 Thread Mikheev, Vadim

Excuse me but what is LRU-2?
I know that in Oracle unused buffers are not in
simple LRU list: Oracle tries to postpone writes
as long as possible -:)

Vadim

> - éÓÈÏÄÎÏÅ ÓÏÏÂÝÅÎÉÅ -
> ïÔ:   Tom Lane [SMTP:[EMAIL PROTECTED]]
> ïÔÐÒÁ×ÌÅÎÏ:   16 ieoya?y 2000 a. 8:50
> ëÏÍÕ: Bruce Momjian
> ëÏÐÉÑ:[EMAIL PROTECTED]
> ôÅÍÁ: Re: [HACKERS] Possible performance improvement: buffer
> replacement policy 
> 
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >> It looks like it wouldn't take too much work to replace shared buffers
> >> on the basis of LRU-2 instead of LRU, so I'm thinking about trying it.
> >> 
> >> Has anyone looked into this area?  Is there a better method to try?
> 
> > Sounds like a perfect idea.  Good luck.  :-)
> 
> Actually, the idea went down in flames :-(, but I neglected to report
> back to pghackers about it.  I did do some code to manage buffers as
> LRU-2.  I didn't have any good performance test cases to try it with,
> but Richard Brosnahan was kind enough to re-run the TPC tests previously
> published by Great Bridge with that code in place.  Wasn't any faster,
> in fact possibly a little slower, likely due to the extra CPU time spent
> on buffer freelist management.  It's possible that other scenarios might
> show a better result, but right now I feel pretty discouraged about the
> LRU-2 idea and am not pursuing it.
> 
>   regards, tom lane



[HACKERS] Ответ: WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Mikheev, Vadim

>I don't understand why WAL needs to log internal operations of any of
>the index types.  Seems to me that you could treat indexes as black
>boxes that are updated as side effects of WAL log items for heap tuples:
>when adding a heap tuple as a result of a WAL item, you just call the
>usual index insert routines, and when deleting a heap tuple as a result

On recovery backend *can't* use any usual routines:
system catalogs are not available.

>of undoing a WAL item, you mark the tuple invalid but don't physically
>remove it till VACUUM (thus no need to worry about its index entries).

One of the purposes of WAL is immediate removing tuples 
inserted by aborted xactions. I want make VACUUM
*optional* in future - space must be available for
reusing without VACUUM. And this is first, very small,
step in this direction.

>This doesn't address the issue of recovering from an incomplete index
>update (such as a partially-completed btree page split), but I think
>the most reliable way to do that is to add WAL records on the order of
>"update beginning for index X" and "update done for index X".  If you
>see the begin and not the done record when replaying a log, you assume

You will still have to log changes for *each* page
updated on behalf of index operation! The fact that
you've seen begin/end records in log doesn't mean
that all intermediate changes to index pages are
written to index file unless you've logged all these
changes and see all of them in index on recovery.

>the index is corrupt and rebuild it from scratch, using Hiroshi's
>index-rebuild code.

How fast is rebuilding of index for table with
10^7 records?
I agree to consider rtree/hash/gist as experimental
index access methods BUT we have to have at least
*one* reliable index AM with short down time/
fast recovery.

>For that matter I am far from convinced that the currently committed
>code for btree WAL logging is correct --- where does it cope with
>cleaning up after an unfinished page split?  I don't see it.

What do you mean? If you say about updating parent
page ("my bits moved ..." etc) then as I've mentioned
previously we can handle uninserted parent item in
run time (though it's not implemented yet -:)).
WAL allows to restore both left and right siblings
and this is the most critical split issue.
(BTW, note that having all btitems on leaf level
at place we could do REINDEX very fast).

>Since we have very poor testing capabilities for the non-mainstream
>index types (remember how I broke rtree completely during 6.5 devel,
>and no one noticed till quite late in beta?) I will have absolutely
>zero confidence in WAL support for these index types if it's implemented
>this way.  I think we should go with a black-box approach that's the
>same for all index types and is implemented completely outside the
>index-access-method-specific code.

I agreed with this approach for all indices except
btree (above + "hey, something is already done for
them" -:)). But remember that to know is index
consistent or not we have to log *all* changes made
in index file anyway... so seems we have to be
very close to be AM specific -:)

Vadim




Re: [HACKERS] Re: New relkind for views

2000-10-16 Thread Bruce Momjian

> "Hollomon, Mark" wrote:
> > 
> > Do we still want to be able to inherit from views?
> 
> Also:
> 
> Currently a view may be dropped with either 'DROP VIEW'
> or 'DROP TABLE'. Should this be changed?

I say let them drop it with either one. 

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] selective fsync?

2000-10-16 Thread Tom Lane

WAL should supersede all these concerns about fsync.  At the very least,
it changes the tradeoffs enough that there's no point in designing
performance improvements based on the old code...

regards, tom lane



Re: [HACKERS] when does CREATE VIEW not create a view?

2000-10-16 Thread Bruce Momjian

OK, the bad news is that this does not apply to the current development
tree.  Ross, can you make a more corrent one?  Sorry.


> On Tue, Aug 29, 2000 at 10:12:38AM +0900, [EMAIL PROTECTED] wrote:
> > > > Oh, the patch strikes me since it is not "multibyte aware."
> 
> O.K. - 
> Here's the multibyte aware version of my patch to fix the truncation
> of the rulename autogenerated during a CREATE VIEW. I've modified all
> the places in the backend that want to construct the rulename to use
> the MakeRetrieveViewRuleName(), where I put the #ifdef MULTIBYTE, so
> that's the only place that knows how to construct a view rulename. Except
> pg_dump, where I replicated the code, since it's a standalone binary.
> 
> The only effect the enduser will see is that views with names len(name)
> > NAMEDATALEN-4 will fail to be created, if the derived rulename clases
> with an existing rule: i.e. the user is trying to create two views with
> long names whose first difference is past NAMEDATALEN-4 (but before
> NAMEDATALEN: that'll error out after the viewname truncation.) In no
> case will the user get left with a table without a view rule, as the
> current code does.
> 
> >
> > Please do so. If you need any help, please let me know.
> > --
> > Tatsuo Ishii
> 
> I haven't tested the MULTIBYTE part. Could you give it a quick once over?
> 
> Ross
> -- 
> Ross J. Reedstrom, Ph.D., <[EMAIL PROTECTED]> 
> NSBRI Research Scientist/Programmer
> Computer and Information Technology Institute
> Rice University, 6100 S. Main St.,  Houston, TX 77005

[ Attachment, skipping... ]


-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: AW: [HACKERS] My new job

2000-10-16 Thread Tom Lane

Zeugswetter Andreas SB <[EMAIL PROTECTED]> writes:
> One thing that comes to my mind is, that you (core members) working full
> time on PG will produce so much work, that we "hobby PgSQL'ers" will
> have a hard job in keeping up to date.

When core was first talking to the Great Bridge folks, one of our big
concerns was that a group of commercial developers contributing to the
project would be able to control the direction of the project by sheer
manpower, ie, core wouldn't have time to review their submissions in
any detail.

That risk is still with us, though it looks a little different now that
we ourselves are the commercial developers in question ;-).

I agree with what a couple of people have already remarked: transparency
of decision making is going to be a critical issue in the future.  Each
of us full-timers will have to be very careful to keep pghackers
informed about what we're doing or thinking of doing.  It won't benefit
the project if a few core developers get to work full-time, but everyone
else drops out because they feel they can't keep up or are not able to
make a meaningful contribution.

regards, tom lane



Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Adam Haberlach

On Mon, Oct 16, 2000 at 06:51:10PM +1100, Chris wrote:
> KuroiNeko wrote:
> 
> > 1 create table alpha( id int4, payload text );
> 
> 
> >  Not  a   big  deal,   right?  
> 
> Yes a big deal. You just lost all your oids.

Been there.  Done that.  Learned to heed the warnings about using
oids in any kind of persistant manner.

-- 
Adam Haberlach|ASCII   /~\
[EMAIL PROTECTED]   |   Ribbon   \ /  Against
http://www.newsnipple.com | CampaignX   HTML
'88 EX500 |/ \  E-mail



Re: [HACKERS] New file naming

2000-10-16 Thread Tom Lane

"Mikheev, Vadim" <[EMAIL PROTECTED]> writes:
> ALTER TABLE RENAME is rollback-able now. I think that
> it's very easy to make DROP TABLE rollback-able too
> (file should be removed after xaction committed) but
> I have no time to deal with this currently.

I will make sure that works before release.

regards, tom lane



[HACKERS] selective fsync?

2000-10-16 Thread Alfred Perlstein

I'm wondering how useful it would be if one could enable selective
fsync.  That would mean that although the database was running
async mode, the system tables and doing things like create index
would cause an fsync to enforce ordering in case of a crash.

This would prevent more serious problems like stray files in the
database while still allowing normal data to be managed quickly.

-- 
-Alfred Perlstein - [[EMAIL PROTECTED]|[EMAIL PROTECTED]]
"I have the heart of a child; I keep it in a jar on my desk."



Re: [HACKERS] INHERITS doesn't offer enough functionality

2000-10-16 Thread Bruce Momjian

> Alfred Perlstein wrote:
> > 
> > I noticed that INHERITS doesn't propogate indecies, It'd be nice
> > if there was an toption to do so.
> 
> Yep it would. Are you volunteering?
> 

Added to TODO:

* Allow inherited tables to inherit index

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: [HACKERS] Possible performance improvement: buffer replacement policy

2000-10-16 Thread Tom Lane

Bruce Momjian <[EMAIL PROTECTED]> writes:
>> It looks like it wouldn't take too much work to replace shared buffers
>> on the basis of LRU-2 instead of LRU, so I'm thinking about trying it.
>> 
>> Has anyone looked into this area?  Is there a better method to try?

> Sounds like a perfect idea.  Good luck.  :-)

Actually, the idea went down in flames :-(, but I neglected to report
back to pghackers about it.  I did do some code to manage buffers as
LRU-2.  I didn't have any good performance test cases to try it with,
but Richard Brosnahan was kind enough to re-run the TPC tests previously
published by Great Bridge with that code in place.  Wasn't any faster,
in fact possibly a little slower, likely due to the extra CPU time spent
on buffer freelist management.  It's possible that other scenarios might
show a better result, but right now I feel pretty discouraged about the
LRU-2 idea and am not pursuing it.

regards, tom lane



Re: [HACKERS] snapshots ...

2000-10-16 Thread Peter Eisentraut

The Hermit Hacker writes:

> ... should be working again.  I hard coded the path so that it finds
> bison, which appears to be what was killing it ...

That sounds suspiciously like the respective cron user not having
/usr/local/bin is its path.

-- 
Peter Eisentraut  [EMAIL PROTECTED]   http://yi.org/peter-e/




Re: Precedence of '|' operator (was Re: [HACKERS] [patch,rfc] binary operators on integers)

2000-10-16 Thread Tom Lane

Thomas Lockhart <[EMAIL PROTECTED]> writes:
> I'd like to see closer adherence to the "usual" operator precedence. But
> I really *hate* having to explicitly call out each rule in the a_expr,
> b_expr, and/or c_expr productions. Any way around this?

It's not easy in yacc/bison, I don't believe.  Precedence of an operator
is converted to precedence of associated productions, so there's no way
to make it work without an explicit production for each operator token
that needs a particular precedence.

In any case, the only way to make things really significantly better
would be if the precedence of an operator could be specified in its
pg_operator entry.  That would be way cool, but (a) yacc can't do it,
(b) there's a fundamental circularity in the idea: you can't identify
an operator's pg_operator entry until you know its input data types,
which means you have to have already decided which subexpressions are
its inputs, and (c) the grammar phase of parsing cannot look at database
entries anyway because of transaction-abort issues.

Because of point (b) there is no chance of driving precedence lookup
from pg_operator anyway.  You can only drive precedence lookup from
the operator *name*, not the input datatypes.  This being so, I don't
see any huge advantage to having the precedence be specified in a
database table as opposed to hard-coding it in the grammar files.

One thing that might reduce the rule bloat a little bit is to have
just one symbolic token (like the existing Op) for each operator
precedence level, thus only one production per precedence level in
a_expr and friends.  Then the lexer would have to have a table to
look up operator names to see which symbolic token to return them
as.  Still don't get to go to the database, but at least setting a
particular operator name's precedence is a one-liner affair instead
of a matter of multiple rules.

regards, tom lane



Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread KuroiNeko

> > Not a big deal, right?
>
> Yes a big deal. You just lost all your oids.

 After I hit the  wall with oids for the first time, I  don't refer to them
anymore :) But yes, you're perfectly right, this is one more reason to have
DDL completely `automated,' ie no manual substitutions.
 And here  the fact that  drop column is  rarely needed is  a double-bladed
sword.  With things  that you  don't  do often,  you're at  risk to  forget
something essential and hose your data.


--

 Well I tried to be meek
  And I have tried to be mild
 But I spat like a woman
  And I sulked like a child
 I have lived behind the walls
  That have made me alone
 Striven for peace
  Which I never have known

 Dire Straits, Brothers In Arms, The Man's Too Strong (Knopfler)




[HACKERS] New file naming

2000-10-16 Thread Mikheev, Vadim

is implemented. Regression tests are passed.
make distclean + initdb are required.

Now all file/dir names are numeric. OIDs are used
for databases *and* relations on creation, but
relation file names may be changed later if required
(separate from oid pg_class.relfilenode field is used).

ALTER TABLE RENAME is rollback-able now. I think that
it's very easy to make DROP TABLE rollback-able too
(file should be removed after xaction committed) but
I have no time to deal with this currently.

There is still one issue - database locations. They don't
work at the moment. I'm going to use soft link for them.
Thomas, is it ok?

Vadim




AW: [HACKERS] My new job

2000-10-16 Thread Zeugswetter Andreas SB

> > >Bottom line is we're not sure what to do now.  Opinions from the 
> > >floor, anyone?

One thing that comes to my mind is, that you (core members) working full
time on PG will produce so much work, that we "hobby PgSQL'ers" will
have a hard job in keeping up to date.

Thus you will have to be nice to us, becoming more and more ignorant.
You will have to understand seemingly dumb, uninformed, outdated  ... questions 
and suggestions :-)

But I trust, your real [business] heart belongs to PostgreSQL, and if there comes 
the time of strong disagreement with Great Bridge, it will be easy for you to find 
a new job.

Congratulations
Andreas



WAL and indexes (Re: [HACKERS] WAL status & todo)

2000-10-16 Thread Tom Lane

"Vadim Mikheev" <[EMAIL PROTECTED]> writes:
> 3. There are no redo/undo for HASH, RTREE & GIST yet. This would be *really
> really
> great* if someone could implement it using BTREE' redo/undo code as
> prototype.
> These are the most complex parts of this todo.

I don't understand why WAL needs to log internal operations of any of
the index types.  Seems to me that you could treat indexes as black
boxes that are updated as side effects of WAL log items for heap tuples:
when adding a heap tuple as a result of a WAL item, you just call the
usual index insert routines, and when deleting a heap tuple as a result
of undoing a WAL item, you mark the tuple invalid but don't physically
remove it till VACUUM (thus no need to worry about its index entries).

This doesn't address the issue of recovering from an incomplete index
update (such as a partially-completed btree page split), but I think
the most reliable way to do that is to add WAL records on the order of
"update beginning for index X" and "update done for index X".  If you
see the begin and not the done record when replaying a log, you assume
the index is corrupt and rebuild it from scratch, using Hiroshi's
index-rebuild code.

The reason I think this is a better way is that I don't believe any of
us (unless maybe Vadim) understand rtree, hash, or especially GIST
indexes well enough to implement a correct WAL logging scheme for them.
Certainly just "use the btree code as a prototype" will not yield a
crash-robust WAL method for the other index types, because they will
have different requirements about what combinations of changes have to
happen together to get from one consistent state to the next.

For that matter I am far from convinced that the currently committed
code for btree WAL logging is correct --- where does it cope with
cleaning up after an unfinished page split?  I don't see it.

Since we have very poor testing capabilities for the non-mainstream
index types (remember how I broke rtree completely during 6.5 devel,
and no one noticed till quite late in beta?) I will have absolutely
zero confidence in WAL support for these index types if it's implemented
this way.  I think we should go with a black-box approach that's the
same for all index types and is implemented completely outside the
index-access-method-specific code.

regards, tom lane



[HACKERS] 7.1 and ecpg

2000-10-16 Thread Michael Meskes

What exactly do we do with 7.1 on Nov 1st? Freeze or release? I'm absolutely
sure I won't finish ecpg until Nov 1st. Yes, I know I had similar problems
with 7.0, but real life tends to take away too much time.

Michael
-- 
Michael Meskes
[EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!



AW: AW: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Zeugswetter Andreas SB


> I think we do, because it solves more than just the ALTER DROP COLUMN
> problem: it cleans up other sore spots too.  Like ALTER TABLE ADD COLUMN
> in a table with child tables.

Yes, could also implement "add column xx int before someothercolumn"
to add a column in the middle.

Andreas



Re: Precedence of '|' operator (was Re: [HACKERS] [patch,rfc] binary operators on integers)

2000-10-16 Thread Thomas Lockhart

> Well, that's a good point --- it isn't going to get any less painful to
> fix it later.  Do we want to just remove the special treatment of '|'
> and let it become one with the undifferentiated mass of Op, or do we
> want to try to set up reasonable precedence for all the bitwise
> operators (and if so, what should that be)?  The second choice has a
> greater chance of breaking existing apps because it's changing more
> operators ...
> Thomas, any opinions here?

I'd like to see closer adherence to the "usual" operator precedence. But
I really *hate* having to explicitly call out each rule in the a_expr,
b_expr, and/or c_expr productions. Any way around this?

  - Thomas



RE: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Don Baccus

At 10:56 PM 10/15/00 +0900, Hiroshi Inoue wrote:

>When I used Oracle,I saw neither option of DROP COLUMN
>feature. It seems to tell us that the implementation isn't 
>that easy. It may not be a bad choise to give up DROP
>COLUMN feature forever.

Both options are in Oracle now, as proudly documented in their
freely accessible on-line documentation.  It is very possible
they didn't implement it until version 8, i.e. until a couple of years
ago.



- Don Baccus, Portland OR <[EMAIL PROTECTED]>
  Nature photos, on-line guides, Pacific Northwest
  Rare Bird Alert Service and other goodies at
  http://donb.photo.net.



[HACKERS] Re: pgsql-committers list definitely wedged

2000-10-16 Thread Marc G. Fournier

On Mon, 16 Oct 2000, Tom Lane wrote:

> > but I see at ftp://ftp.postgresql.org/pub/dev/ that the nightly
> > snapshot hasn't updated since Monday either.
> 
> As of this morning, it looks like the tar.gz files all got updated
> at 4AM EDT last night, but to judge by timestamps, the associated
> .md5 files didn't.  What the heck?  What script is being run by the
> cron job, anyway?  ~pgsql/bin/mk-snapshot does not look like it could
> behave this way.

00 04 * * *   /home/projects/pgsql/bin/mk-snapshot

And, actually, looking at the script again, it does look like it can
behave this way ... if you run a script from cron, its PATH gets set to a
'secure path', which is very limited.  I set it to a specific PATH so that
/usr/local was included, but didn't realize that md5 was in /sbin ... just
added that to the path for tonights run and am running it manually now so
that its all in sync ...





Re: [HACKERS] bytea type

2000-10-16 Thread Bruce Momjian

[ Charset ISO-8859-1 unsupported, converting... ]
> I found bytea doing a \dT in psql, but I do not find any documentation on
> it.
> 
> Could I have some source code implementation of bytea with examples ?

Yes, it is like text, but you can put in binary data as 'a\\000b' puts
a, null, b.

-- 
  Bruce Momjian|  http://candle.pha.pa.us
  [EMAIL PROTECTED]   |  (610) 853-3000
  +  If your life is a hard drive, |  830 Blythe Avenue
  +  Christ can be your backup.|  Drexel Hill, Pennsylvania 19026



Re: AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread The Hermit Hacker


I like the pg_{import,export} names myself ... *nod*


On Mon, 16 Oct 2000, Zeugswetter Andreas SB wrote:

> 
> > > > As a result do people have any objection to changing pg_restore to
> > > > pg_undump? Or pg_load?
> 
> Also possible would be a name like Oracle
> pg_exp and pg_imp for export and import.
> (or pg_export and pg_import)
> 
> Load and unload is often more tied to data only (no dml).
> 
> I agree that the current name pg_restore for its current functionality
> is not good and misleading in the light of WAL backup. 
> 
> Andreas
> 
> 

Marc G. Fournier   ICQ#7615664   IRC Nick: Scrappy
Systems Administrator @ hub.org 
primary: [EMAIL PROTECTED]   secondary: scrappy@{freebsd|postgresql}.org 




AW: [HACKERS] analyze.c

2000-10-16 Thread Zeugswetter Andreas SB


> > I've been reading something about implementation of histograms, and,
> > AFAIK, in practice histograms is just a cool name for no more than:
> >1. top ten with frequency for each
> >2. the same for top ten worse
> >3. average for the rest

Consider, that we only need that info for choice of index, and if an average value was 
too
frequent for this index to be efficient you can safely drop the index, it would be 
useless.
Thus it seems to me that keeping stats on the most infrequent values (point 2) is 
useless.
For me these would also be the most volatile, thus the stats would only be
accurate for a short period of time.

I think what we need is as follows:
1. our current histograms 
2. a list of exceptions for exceptional values that are very frequent
 
Exceptional are those values that would skew the distribution too much.

Very infrequent values should not be used for min|max values of histogram buckets,
but that is imho all that needs to be done for infrequent values.

Andreas



[HACKERS] bytea type

2000-10-16 Thread Franck Martin

I found bytea doing a \dT in psql, but I do not find any documentation on
it.

Could I have some source code implementation of bytea with examples ?

Franck Martin
Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: [EMAIL PROTECTED]  
Web site: http://www.sopac.org/  

This e-mail is intended for its recipients only. Do not forward this
e-mail without approval. The views expressed in this e-mail may not be
neccessarily the views of SOPAC.




AW: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Zeugswetter Andreas SB


> This style of "DROP COLUMN" would change the attribute
> numbers whose positons are after the dropped column.
> Unfortunately we have no mechanism to invalidate/remove
> objects(or prepared plans) which uses such attribute numbers.
> And I've seen no proposal/discussion to solve this problem
> for DROP COLUMN feature. We wound't be able to prevent
> PostgreSQL from doing the wrong thing silently. 

That issue got me confused now (There was a previous mail where 
you suggested using logical colid most of the time). Why not use the 
physical colid in prepared objects/plans. Since those can't currently change
it seems such plans would not be invalidated.

Andreas



Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Hannu Krosing

Chris wrote:
> 
> Hiroshi Inoue wrote:
> 
> > When I used Oracle,I saw neither option of DROP
> > COLUMN feature. It seems to tell us that the
> > implementation isn't
> > that easy. It may not be a bad choise to give up DROP
> > COLUMN feature forever.
> 
> Because it's not easy we shouldn't do it? I don't think so. The perfect
> solution is lazy updating of tuples but it requires versioning of
> meta-data and that requires a bit of work.

I would prefer the logical/physical numbering + typed tuples
(or is it the same thing ;)

It would give us the additional benefit of being able to move to SQL3-wise 
correct CREATE TABLE UNDER syntax with most constraints (primary/foreign key, 
unique, ...) carried on automatically if we store the (single-)inheritance 
hierarchy in one file.

Others (NOT NULL, CHECK, ...) will need additional check for tuple type.

This does not solve the problem for multiple inheritance, but then we could 
cludge most of it by inheriting all from a single root.

I suspect it would still be easier than doing it the other way (by
constructing 
UNIONs each time, checking several indexes for uniquenass (or creating a new 
index type for indexing several separate relations))

-
Hannu



AW: [HACKERS] Backup, restore & pg_dump

2000-10-16 Thread Zeugswetter Andreas SB


> > > As a result do people have any objection to changing pg_restore to
> > > pg_undump? Or pg_load?

Also possible would be a name like Oracle
pg_exp and pg_imp for export and import.
(or pg_export and pg_import)

Load and unload is often more tied to data only (no dml).

I agree that the current name pg_restore for its current functionality
is not good and misleading in the light of WAL backup. 

Andreas



Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Chris

Hiroshi Inoue wrote:

> We could easily break the consistency of DB due to 
> careless implementations.

I'm sure no-one around here would do careless implementations. :-)



Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Hiroshi Inoue


Chris wrote:

> Hiroshi Inoue wrote:
>
> > When I used Oracle,I saw neither option of DROP
> > COLUMN feature. It seems to tell us that the
> > implementation isn't
> > that easy. It may not be a bad choise to give up DROP
> > COLUMN feature forever.
>
> Because it's not easy we shouldn't do it? I don't think so. The perfect
> solution is lazy updating of tuples but it requires versioning of
> meta-data and that requires a bit of work.
>

We could easily break the consistency of DB due to careless
implementations. Is "DROP COLUMN" valuable to walk on a
tightrope ?  I would agree if "ADD COLUMN" needs to walk
on a tightrope.

Regards.

Hiroshi Inoue


Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Chris

Hiroshi Inoue wrote:

> Certainly it would need 2x.
> However is ADD COLUMN DEFAULT really needed ?
> I would do as follows.
> 
> ADD COLUMN (without default)
> UPDATE .. SET new_column = new default
> ALTER TABLE ALTER COLUMN SET DEFAULT

Well in current postgres that would use 2x. With WAL I presume that
would use a lot of log space and probably a lot more processing. But if
you can do the above you might as well support the right syntax.



Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Hiroshi Inoue


Chris wrote:

> Hiroshi Inoue wrote:
>
> > When I used Oracle,I saw neither option of DROP
> > COLUMN feature. It seems to tell us that the
> > implementation isn't
> > that easy. It may not be a bad choise to give up DROP
> > COLUMN feature forever.
>
> Because it's not easy we shouldn't do it? I don't think so. The perfect
> solution is lazy updating of tuples but it requires versioning of
> meta-data and that requires a bit of work.
>
> > However I would strongly object to 2x
> > ADD COLUMN if such implementations are proposed.
>
> Not even 2x for ADD COLUMN DEFAULT ?

Certainly it would need 2x.
However is ADD COLUMN DEFAULT really needed ?
I would do as follows.

ADD COLUMN (without default)
UPDATE .. SET new_column = new default
ALTER TABLE ALTER COLUMN SET DEFAULT

Regards.
Hiroshi Inoue


Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Chris

Hiroshi Inoue wrote:

> When I used Oracle,I saw neither option of DROP 
> COLUMN feature. It seems to tell us that the 
> implementation isn't
> that easy. It may not be a bad choise to give up DROP
> COLUMN feature forever.

Because it's not easy we shouldn't do it? I don't think so. The perfect
solution is lazy updating of tuples but it requires versioning of
meta-data and that requires a bit of work.

> However I would strongly object to 2x
> ADD COLUMN if such implementations are proposed. 

Not even 2x for ADD COLUMN DEFAULT ?



Re: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Chris

KuroiNeko wrote:

> 1 create table alpha( id int4, payload text );


>  Not  a   big  deal,   right?  

Yes a big deal. You just lost all your oids.



Re: AW: AW: [HACKERS] ALTER TABLE DROP COLUMN

2000-10-16 Thread Hiroshi Inoue


Tom Lane wrote:

> Bruce Momjian <[EMAIL PROTECTED]> writes:
> >> This said, I think Hiroshi's patch seems a perfect starting point, no ?
>
> > Having phantom columns adds additional complexity to the system overall.
> > We have to decide we really want it before making things more complex
> > than they already are.
>
> I think we do, because it solves more than just the ALTER DROP COLUMN
> problem: it cleans up other sore spots too.  Like ALTER TABLE ADD COLUMN
> in a table with child tables.
>
> Of course, it depends on just how ugly and intrusive the code changes
> are to make physical and logical columns distinct.  I'd like to think
> that some fairly limited changes in and around heap_getattr would do
> most of the trick.  If we need something as messy as the first-cut
> DROP_COLUMN_HACK then I'll look for another way...
>

Hmm,the implementation using physical and logical attribute numbers
would be much more complicated than first-cut DROP_COLUMN_HACK.
There's no simpler way than first-cut DROP_COLUMN_HACK.
I see no progress in 2x DROP COLUMN implementation.

How about giving up DROP COLUMN forever ?

Regards.

Hiroshi Inoue


  1   2   >