Re: [PATCHES] return can contains any row or record functions
Tom has some objectives to state PL/pgSQL not explicitly into patch if I remeber well. If I get comments, I'll correct patch promptly. Pavel OK, but it seemed the patch needed more work before it could be applied. I added a TODO item for it: o Allow PL/RETURN to return row or record functions http://archives.postgresql.org/pgsql-patches/2005-11/msg00045.php --- Pavel Stehule wrote: Not from my side Regards Pavel Stehule From: Bruce Momjian pgman@candle.pha.pa.us To: Pavel Stehule [EMAIL PROTECTED] CC: [EMAIL PROTECTED], pgsql-patches@postgresql.org Subject: Re: [PATCHES] return can contains any row or record functions Date: Wed, 14 Jun 2006 18:48:00 -0400 (EDT) Has any more work happened on this patch? --- Pavel Stehule wrote: - we can't use estate-rsi for the RETURN case, at least as presently implemented, because that is not always filled-out -- the example I gave before shows how because we don't check for a compatible TupleDesc when estate-rsi is NULL, we end up returning a value that is incompatible with the function's declared return type I expected so when rsi is NULL, then I haven't any info about desired record type, and then I have to skip checking for a compatibility (and I can it to do, because this is clean error, then is not necessery conversion, and next step do exception and error if returned type is wrong). I found different problem. It has maybe relation with Tom Lane's changes about returning one field records. And maybe wee need test for conversion everytime create or replace function a() returns record as $$ return (1); $$ language plpgsql; is syntax clean, it's row expression, but select a() ends with wrong result type supplied in RETURN. With change return (1,2), all works fine. return row(1) works well too. - therefore, we need to use some other way to get the TupleDesc of the function's declared return type. Offhand I think we can use estate-fn_rettype (plus the funcapi stuff for handling RECORDOID), but I haven't had a chance to try it yet. testing estate-fn_rettype is safer, but what is efectivity? Is necessery caching TupleDesc in retturn statement? I don't know? I don't study mechanism when is rsi valid or not. But I found some samples in source, when rsi was used for same purpose. Pavel _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org _ Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci. http://messenger.msn.cz/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + _ Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PATCHES] CREATE TABLE LIKE INCLUDING CONSTRAINTS
I just managed to crash the server so I guess this isn't finished, but I'm posting it in the post early post often spirit. Aside from error checks it also needs docs and tests of course. This patch implements an option to copy check constraints when using LIKE. Because the standard specifically excludes constraints (except NOT NULL) from being copied it defaults to off and has to be explicitly requested by the user using the nonstandard extension INCLUDING CONSTRAINTS. This is especially useful in combination with ALTER TABLE INHERIT since without it there's no convenient way to create eligible tables for adding to the inheritance tree. The user would have to manually reenter every check constraint. Question: . Is nodeToString() the right thing here? Currently only check constraints are added because only check constraints are handled by inheritance. I intend to add foreign key constraints when I add them to the rest of inheritance which will necessitate a scan of pg_constraint instead of using the relcache :( postgres=# create table z (i integer check (i=0)); CREATE TABLE postgres=# create table zz (like z including constraints); CREATE TABLE postgres=# \d zz Table public.zz Column | Type | Modifiers +-+--- i | integer | Check constraints: z_i_check CHECK (i = 0) like.patch2 Description: Binary data -- greg ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] CREATE TABLE LIKE INCLUDING CONSTRAINTS
Fixed the bug, added docs and tests. like.patch3 Description: Binary data The previous message explaining the patch: Greg Stark [EMAIL PROTECTED] writes: This patch implements an option to copy check constraints when using LIKE. Because the standard specifically excludes constraints (except NOT NULL) from being copied it defaults to off and has to be explicitly requested by the user using the nonstandard extension INCLUDING CONSTRAINTS. This is especially useful in combination with ALTER TABLE INHERIT since without it there's no convenient way to create eligible tables for adding to the inheritance tree. The user would have to manually reenter every check constraint. Question: . Is nodeToString() the right thing here? Currently only check constraints are added because only check constraints are handled by inheritance. I intend to add foreign key constraints when I add them to the rest of inheritance which will necessitate a scan of pg_constraint instead of using the relcache :( postgres=# create table z (i integer check (i=0)); CREATE TABLE postgres=# create table zz (like z including constraints); CREATE TABLE postgres=# \d zz Table public.zz Column | Type | Modifiers +-+--- i | integer | Check constraints: z_i_check CHECK (i = 0) -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] patch fixing the old RETURN NEXT bug
Added to TODO:
o Fix problems with RETURN NEXT on tables with
dropped/added columns after function creation
http://archives.postgresql.org/pgsql-patches/2006-02/msg00165$
---
Sergey E. Koposov wrote:
Hello All,
I'm proposing the fix of this bug:
http://archives.postgresql.org/pgsql-hackers/2005-02/msg00498.php
The exact SQL code exposing the error:
--
create table usno (ra real, dec real, bmag real, rmag real,ipix int8);
CREATE OR REPLACE FUNCTION xxx(refcursor) RETURNS refcursor AS '
DECLARE query varchar;
BEGIN
query = ''SELECT * FROM usno'';
OPEN $1 FOR EXECUTE query;
RETURN $1;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION yyy() RETURNS SETOF usno AS '
DECLARE rec record;
DECLARE cur refcursor;
BEGIN
cur=xxx(''curs_name'');
LOOP
FETCH cur into rec;
EXIT WHEN NOT FOUND;
RETURN NEXT rec;
END LOOP;
RETURN;
END;
' LANGUAGE plpgsql;
insert into usno values(1,2,3,4);
select * from yyy();
alter table usno add column errbox box;
select * from yyy();
alter table usno drop column errbox;
select * from yyy();
---
The problem with that is in fact in pl_exec.c in function
compatible_tupdesc(), which do not check for the deleted attributes.
The patch is attached.
Regards,
Sergey
*
Sergey E. Koposov
Max Planck Institute for Astronomy
Web: http://lnfm1.sai.msu.ru/~math
E-mail: [EMAIL PROTECTED]
Content-Description:
[ Attachment, skipping... ]
---(end of broadcast)---
TIP 6: explain analyze is your friend
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [PATCHES] plpython tracebacks
URL added to TODO list. --- Neil Conway wrote: P. Scott DeVos wrote: I'm on it. Actually, don't worry about it -- I've made the corrections I had in mind myself. Attached is a revised patch. On looking closer, I didn't really like the way the patch accumulated the lines of the traceback: AFAICS _PyString_Join() is not an official Python C API function (it's not documented, at any rate), and besides it is cleaner and more efficient to build up the traceback string in a StringInfo rather than using Python lists and strings. The attached patch isn't quite finished: No Traceback when there is no traceback information doesn't seem like the best message, I need to update the regression tests and some comments, etc. But I plan to apply something similar in substance to the attached patch to HEAD in the next day or two, barring objections. -Neil ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] CREATE TABLE LIKE INCLUDING CONSTRAINTS
Greg Stark [EMAIL PROTECTED] writes: This patch implements an option to copy check constraints when using LIKE. Ah, found a problem. I need to do a change_varattnos_of_a_node() call here. Should this function maybe be promoted to some other file like ruleutils.c? -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Automatic free space map filling
Added to TODO list with URL. --- ITAGAKI Takahiro wrote: Zeugswetter Andreas DCP SD [EMAIL PROTECTED] wrote: Ok, we cannot reuse a dead tuple. Maybe we can reuse the space of a dead tuple by reducing the tuple to it's header info. Attached patch realizes the concept of his idea. The dead tuples will be reduced to their headers are done by bgwriter. This patch is incomplete, so please discuss in the thread on HACKERS. --- ITAGAKI Takahiro NTT Cyber Space Laboratories [ Attachment, skipping... ] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] TupleDesc refcounting
I wrote: I'm finally getting back to looking at the problem of reference-counting cached TupleDescs as was discussed in January. I had objected to the last patch Neil posted: http://archives.postgresql.org/pgsql-patches/2006-01/msg00243.php on the grounds that it seemed too complicated. On looking at it closely, I realize that a lot of the complexity comes from my insistence that the ResourceOwner mechanism ought to warn about any tupdesc references that haven't been explicitly released before transaction end. After much thrashing I concluded that my original instinct was right and we really do want to insist on cleanup of tupdesc references during normal processing. The approach I tried to take effectively meant leaking tupdesc references until end of query, which is really bad news for SQL-language functions --- a function that grabs a tupdesc reference during plan startup would then accumulate a tupdesc reference during every invocation, leading to indefinite bloat in the ResourceOwner over a long query. So I ended up applying something pretty close to Neil's patch. I did modify it to not bother reference-counting tupdescs that aren't actually in any cache. I also fixed up TupleTableSlots to do reference-count processing if ExecSetSlotDescriptor is handed a reference-counted tupdesc (my assertion yesterday that this never happens was wrong), but not to bother cleaning up non-ref-counted descriptors. All in all, a lot of thrashing for only marginal improvement :-( regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] table/index fillfactor control, try 2
On Fri, 2006-06-16 at 13:33 +0900, ITAGAKI Takahiro wrote: This is a revised fillfactor patch. It uses WITH syntax and we can add new AM specific parameters easily. Cool. I'll look at that in more detail. So we have a new element of the RelationData struct: void*rd_amopts; Which each AM defines and interprets. The internal structure is stored in the pg_class.relamopaque column as bytea. I guess it is not the best and there is room for discussion. I examined the following ideas, but they had complexities and difficulties. 1. Add AM specific system tables (pg_heap, pg_btree, etc.) that may inherit pg_class. But it will impact the current source code terribly. Hmmm, yep, not a good idea. 2. Store the structures in AM's meta page. But heaps don't have meta pages. But perhaps they should? That sounds very similar to the idea of non-transactional pg_class data. It would make a lot of sense if heaps had meta pages too, and that the data within them was cached on the relcache just as index meta page data will be for 8.2 3. Store them into an array of text column that newly added to pg_class. But we hove to re-parse the array every time relations are loaded. Not sure if thats a big overhead? Is it a big array? I hope not. We should be aiming for as few parameters as possible for an index/heap, otherwise we'll never be able to determine their correct settings. 4. Add new system table, pg_class_option (relid, option name, value). But it has same problem as 3 and needs additional heap scannings. No thanks. Therefore, I choose the as-is binary format to store the internal structures. Any comments or better ideas? Well, its either metapages or array-on-pg_class for me. The metagpages thought would require some consolidation from various other proposals, so we'll need to wait for wider discussion on that. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] drop if exists remnainder (reprise)
Andrew Dunstan wrote: Here is an updated patch for the remaining cases of DROP objtype IF EXISTS ... as recently discussed on -hackers. The cases are: language, tablespace, trigger, rule, opclass, function, aggregate. operator, and cast. Regression tests and docs still to come. I wasn't quite sure how to format the message in the case of aggregate - the change in calls there seems to have made it somewhat harder, so some advice would be appreciated. I have committed this to avoid further bitrot. I will get docs and regression tests done shortly. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [BUGS] [Win32] Problem with rename()
Really? If there was a patch, I missed it. My recollection is that there was general agreement about this particular problem (see, for example, http://archives.postgresql.org/pgsql-bugs/2006-04/msg00189.php ), but things kind of trailed off after that without a resolution. As far as the complete list of Win32 problems which affected us: - The stats collector crashing should indeed be fixed in 8.1.4 - Missing stats caused by Windows PID recycling is fixed in 8.2 - Various semaphore problems are probably all fixed with the new Win32 semaphore implementation in 8.2 - The stuck log rename problem mentioned above is still an issue - The permission denied on fsync (or something like that) problem is still an issue. Unfortunately, IIRC, we could never really nail down the underlying problem. None of these problems affect us any more: the production servers now run Linux. Great to have options! (and we were moving that direction anyway) Pete Bruce Momjian pgman@candle.pha.pa.us 16.06.2006 22:05 I am assuming this problem and the other rash of Win32 problems reported in March are now all fixed in 8.1.4. If not, please let me know. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] [BUGS] [Win32] Problem with rename()
Peter Brant wrote: Really? If there was a patch, I missed it. My recollection is that there was general agreement about this particular problem (see, for example, http://archives.postgresql.org/pgsql-bugs/2006-04/msg00189.php ), but things kind of trailed off after that without a resolution. Yea. Where you using WAL archiving? We will have a fix in 8.1.5 to prevent multiple archivers from starting. Perhaps that was a cause. As far as the complete list of Win32 problems which affected us: - The stats collector crashing should indeed be fixed in 8.1.4 - Missing stats caused by Windows PID recycling is fixed in 8.2 - Various semaphore problems are probably all fixed with the new Win32 semaphore implementation in 8.2 - The stuck log rename problem mentioned above is still an issue Yep. What has me baffled is why no one else is seeing the problem. We had a rash of reports, and now all is quiet. - The permission denied on fsync (or something like that) problem is still an issue. Unfortunately, IIRC, we could never really nail down the underlying problem. Yes, I just reread that thread. I also am confused where to go from here. None of these problems affect us any more: the production servers now run Linux. Great to have options! (and we were moving that direction anyway) Were you the only one use Win32 in heavy usage? You were on Win2003. Were there some bugs in the OS that got fixed later. Yea, stumped. Guess we will have to wait for more reports. I don't even see how to document this as a TODO. -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [PATCHES] [GENERAL] Omitting tablespace creation from pg_dumpall...
Tom Lane wrote:
Bruce Momjian pgman@candle.pha.pa.us writes:
Should pg_dumpall be using the SET default_tablespace = foo method as
well?
That would mean changing the semantics of CREATE DATABASE; currently it
copies the default tablespace from the template database, rather than
looking at default_tablespace. I'm unsure if that's a good idea or not.
None of the other properties of a database are handled that way.
Interesting distinction.
I have added a documentation and code comment patch for this.
--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDBhttp://www.enterprisedb.com
+ If your life is a hard drive, Christ can be your backup. +
Index: doc/src/sgml/ref/pg_dumpall.sgml
===
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/pg_dumpall.sgml,v
retrieving revision 1.55
diff -c -c -r1.55 pg_dumpall.sgml
*** doc/src/sgml/ref/pg_dumpall.sgml 1 Nov 2005 21:09:50 - 1.55
--- doc/src/sgml/ref/pg_dumpall.sgml 16 Jun 2006 21:56:58 -
***
*** 377,382
--- 377,389
databases.
/para
+ para
+applicationpg_dumpall/application requires all needed
+tablespace directories to exist before the restore or
+database creation will fail for databases in non-default
+locations.
+ /para
+
/refsect1
Index: src/bin/pg_dump/pg_dumpall.c
===
RCS file: /cvsroot/pgsql/src/bin/pg_dump/pg_dumpall.c,v
retrieving revision 1.79
diff -c -c -r1.79 pg_dumpall.c
*** src/bin/pg_dump/pg_dumpall.c 7 Jun 2006 22:24:45 - 1.79
--- src/bin/pg_dump/pg_dumpall.c 16 Jun 2006 21:57:09 -
***
*** 888,894
appendPQExpBuffer(buf, ENCODING = );
appendStringLiteralConn(buf, dbencoding, conn);
! /* Output tablespace if it isn't default */
if (strcmp(dbtablespace, pg_default) != 0)
appendPQExpBuffer(buf, TABLESPACE = %s,
fmtId(dbtablespace));
--- 888,902
appendPQExpBuffer(buf, ENCODING = );
appendStringLiteralConn(buf, dbencoding, conn);
! /*
! * Output tablespace if it isn't the default. For default, it
! * uses the default from the template database. If tablespace
! * is specified and tablespace creation failed earlier,
! * (e.g. no such directory), the database creation will fail
! * too. One solution would be to use 'SET default_tablespace'
! * like we do in pg_dump for setting non-default database
! * locations.
! */
if (strcmp(dbtablespace, pg_default) != 0)
appendPQExpBuffer(buf, TABLESPACE = %s,
fmtId(dbtablespace));
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] plpython improvements
Added to TODO: o Allow PL/python to composite types and result sets once buggy assert-enabled versions of python can be detected http://archives.postgresql.org/pgsql-patches/2006-04/msg00087$ --- Hannu Krosing wrote: ?hel kenal p?eval, N, 2006-05-04 kell 18:21, kirjutas Sven Suursoho: Hi, Sun, 30 Apr 2006 19:14:28 +0300, Tom Lane [EMAIL PROTECTED]: Sven Suursoho [EMAIL PROTECTED] writes: Unfortunately, there is still one problem when using unpatched python, caused by too aggressive assert. http://mail.python.org/pipermail/python-checkins/2005-August/046571.html. I don't think we are going to be able to accept a patch that causes the server to crash when using any but a bleeding-edge copy of Python. Actually not bleeding-edge, but just version 2.4.x as distributed in Fedora Core (and possibly in RHAS), which have assert() enabled in python.so. The assert there is buggy (bug http://sourceforge.net/tracker/index.php?func=detailaid=1257960group_id=5470atid=105470) Did complete rewrite for SETOF functions: now accepts any python object for which iter(object) returns iterable object. In this way we don't have to deal with specific containers but can use unified python iterator API. It means that plpython is future-proof -- whenever python introduces new container, stored procedures already can use those without recompiling language handler. Also integrated with regression tests and updated existing tests to use named parameters. When using python interpreter with asserts enabled, generators still crash. But I don't think that we should drop this feature because of that. Reasons: 1) this is someone else's bug, we are using documented API correctly 2) it doesn't concern majority of users because probably there is no asserts in production packages (tested with gentoo, ubuntu, suse). This is true even for older python versions that are not patched. From reading the bug, it seems that older versions of python also don't have this bug, only 2.4. And after all, we can document using sets, lists, tuples, iterators etc and explicitly state that returning generator is undefined. I think that a less confusing way of saying it would be : Generators crash if python version used is 2.4.x and it is compiled with asserts. Currently only known linux distributions to distibute such python.so files are Fedora and possibly other RedHat distributions, while Gentoo, Ubuntu and Suse are OK. If you need to use generators on such a platform, compile your own python from source and make sure that configure uses your version. I think the patch should be commited so we can collect data about where else the buggy version of python exists. And if some buildfarm machines start crashing, python should be fixed there. Hannu -- Bruce Momjian http://candle.pha.pa.us EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] table/index fillfactor control, try 2
Simon Riggs [EMAIL PROTECTED] writes: On Fri, 2006-06-16 at 13:33 +0900, ITAGAKI Takahiro wrote: 2. Store the structures in AM's meta page. But heaps don't have meta pages. But perhaps they should? That sounds very similar to the idea of non-transactional pg_class data. The disadvantage of putting this stuff into metapages is that then you need some entirely new protocol for letting clients get at it (and pg_dump, for one, needs to). I agree with putting it in a catalog. An opaque bytea won't do though. What I'd suggest is something real close to the format used for GUC parameters in ALTER DATABASE SET and ALTER USER SET, ie, pairs of keyword/value strings. This way pg_dump doesn't need very much smarts about what the values are that it's dumping. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
