Re: AW: AW: [HACKERS] ALTER TABLE DROP COLUMN
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
Re: AW: [HACKERS] ALTER TABLE DROP COLUMN
KuroiNeko wrote: 1 create table alpha( id int4, payload text ); snip Not a big deal, right? Yes a big deal. You just lost all your oids.
Re: AW: [HACKERS] ALTER TABLE DROP COLUMN
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
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
AW: [HACKERS] Backup, restore pg_dump
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
AW: AW: [HACKERS] ALTER TABLE DROP COLUMN
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
AW: [HACKERS] analyze.c
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] 7.1 and ecpg
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: [HACKERS] My new job
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
Re: [HACKERS] Otvet: WAL and indexes (Re: [HACKERS] WAL status todo)
* 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] Re: : WAL and indexes (Re: [HACKERS] WAL status todo)
"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] Full text indexing (Question/request)
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
Re: [HACKERS] Re: Otvet: WAL and indexes (Re: [HACKERS] WAL status todo)
* 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."
[HACKERS] Re: [BUGS] unique/references not honored when inheriting tables
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
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: [HACKERS] Isn't non-TEST_AND_SET code long dead?
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
Can you give me a TODO item? * Fix LIKE indexing optimization for non-ASCII locales regards, tom lane
[HACKERS] Re: : : WAL and indexes (Re: [HACKERS] WAL status todo)
"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] : [HACKERS] Otvet: WAL and indexes (Re: [HACKERS] WAL status todo)
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
Re: [HACKERS] Re: ?????: ?????: WAL and indexes (Re: [HACKERS] WAL status todo)
"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
Re: [HACKERS] TODO list updates
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] when does CREATE VIEW not create a view?
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: [PATCHES] Re: [HACKERS] when does CREATE VIEW not create a view?
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: AW: [HACKERS] Backup, restore pg_dump
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 |/
Re: AW: [HACKERS] new relkind for view
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] new relkind for view
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] Backup, restore pg_dump
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] Backup, restore pg_dump
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
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: [HACKERS] minor fixes for regress
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: [HACKERS] Indexing for geographical objects
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] mailto:[EMAIL PROTECTED] Web site: http://www.sopac.org/ 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: AW: [HACKERS] Backup, restore pg_dump
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*
[HACKERS] Re: [GENERAL] PL/Perl compilation error
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 stdio.h
#include stdlib.h
#include stdarg.h
#include unistd.h
#include fcntl.h
#include string.h
#include setjmp.h
/* 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 nargs;
FmgrInfo
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] Re: New relkind for views
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] The lightbulb just went on...
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
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: AW: [HACKERS] Backup, restore pg_dump
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] The lightbulb just went on...
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: [HACKERS] Patch for TNS services
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: [HACKERS] The lightbulb just went on...
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] fkey + primary key does not work in current
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] fkey + primary key does not work in current
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] length coerce for bpchar is broken since 7.0
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
Re: [HACKERS] psql's \d functions broken for views in current sources
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] The lightbulb just went on...
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
[HACKERS] Re: [COMMITTERS] pgsql/src/backend/utils/adt (formatting.c oracle_compat.c)
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] initdb -t destroys all databases
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
Re: [HACKERS] pgsql is 75 times faster with my new index scan
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] length coerce for bpchar is broken since 7.0
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] length coerce for bpchar is broken since 7.0
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
