Re: [PATCHES] hash index improving v3
On Mon, Sep 22, 2008 at 11:25 PM, Alex Hunsaker <[EMAIL PROTECTED]> wrote: > On Sun, Sep 14, 2008 at 8:16 PM, Tom Lane <[EMAIL PROTECTED]> wrote: >> BTW, one thing I noticed was that the hash index build time for the >> "wide column" case got a lot worse after applying the patch (from 56 to >> 237 sec). The reason for this turned out to be that with the smaller >> predicted index size, the code decided not to use the pre-sorting method >> that was recently added. Reducing effective_cache_size to less than the >> index size brought the time back down, to about 54 sec. So it would >> seem that effective_cache_size is too large a cutoff value. I'm >> considering changing hashbuild to switch over at shared_buffers instead >> of effective_cache_size --- any thoughts about that? > > Switching to shared_buffers gets my vote, on my test table with > 50,000,000 rows it takes about 8 minutes to create an index using the > default effective_cache_size. With effective_cache_size set to 6GB > (machine has 8GB) its still going an hour later. Agreed. I think using shared_buffers as a cutoff is a much better idea as well. -- Jonah H. Harris, Senior DBA myYearbook.com -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] hash index improving v3
On Wed, Sep 3, 2008 at 10:06 PM, Simon Riggs <[EMAIL PROTECTED]> wrote: >> It seems hash index is a little better on index creation and >> selection. >> But maybe it's in the range of noise, I'm not sure. >> I'd like to try it with a bigger dataset (e.g. table with 10GB) but >> there is not enough space in my computer. >> Anyone interest can make a test on a bigger data set. I tried it earlier on a 500M row table and found a few bugs. In particular, it doesn't seem like recheck is happening and the performance/sizing is a bit *interesting*. I'll post stats tomorrow when I'm in the office. -- Jonah H. Harris, Senior DBA myYearbook.com -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] actualized SQL/PSM patch
On Tue, Apr 1, 2008 at 5:55 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > The fundamental problem I've got with this patch is that it adds 400K > of new code (and that's just the code, not counting documentation or > regression tests) that we'll have to maintain, to obtain a feature that > so far as I've heard there is precisely zero demand for. We have a customer that wants to use it as part of a MySQL-to-Postgres migration. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837 | http://www.enterprisedb.com/ -- Sent via pgsql-patches mailing list (pgsql-patches@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-patches
Re: [PATCHES] [HACKERS] Including Snapshot Info with Indexes
On Jan 28, 2008 8:21 AM, Gokulakannan Somasundaram <[EMAIL PROTECTED]> wrote: > I am not seeing my mail getting listed in the archives. So i am just > resending it, in case the above one has got missed. It was sent. Archive processing is delayed. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Proposed patch: synchronized_scanning GUC variable
On Jan 27, 2008 3:07 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Per today's -hackers discussion, add a GUC variable to allow clients to > disable the new synchronized-scanning behavior, and make pg_dump disable > sync scans so that it will reliably preserve row ordering. This is a > pretty trivial patch, but seeing how late we are in the 8.3 release > cycle, I thought I'd better post it for comment anyway. +1 -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(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] [HACKERS] Including Snapshot Info with Indexes
Doh! Can you please send another patch with gram.y as well. Mine is missing all of the thick index stuff. On Jan 23, 2008 2:45 PM, Gokulakannan Somasundaram <[EMAIL PROTECTED]> wrote: > Thanks for reviewing the patch. Please go ahead and make the changes and > re-submit the patch. I will take care, that i won't repeat the stated > mistakes again. > > The Missing of Thick Keyword - i don't know how it got removed. > > > > Thanks, > Gokul > > > > On Jan 23, 2008 10:49 PM, Jonah H. Harris <[EMAIL PROTECTED]> wrote: > > On Jan 23, 2008 11:28 AM, Gokulakannan Somasundaram <[EMAIL PROTECTED]> > wrote: > > > Fixed a type 'o' > > > > I'm playing with this now against 8.3 HEAD. Looks like there's a > > couple things which are problematic: > > > > - DefineIndex was updated only in bootparse.c, not in bootparse.y > > - The patch contains changes to pg_config.h > > - THICK isn't defined in gram.y (as a token or under > > unreserved_keywords), so compilation of keywords.c fails. > > > > In the future, please make changes to the proper pre-built files so > > that someone doesn't have to configure it, then patch it. I have them > > fixed and will submit the patch back here if you'd like. Or, you can > > fix it. It's up to you :) > > > > > > > > > > -- > > Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 > > EnterpriseDB Corporation| fax: 732.331.1301 > > 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] > > Edison, NJ 08837| http://www.enterprisedb.com/ > > > > -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Including Snapshot Info with Indexes
On Jan 23, 2008 11:28 AM, Gokulakannan Somasundaram <[EMAIL PROTECTED]> wrote: > Fixed a type 'o' I'm playing with this now against 8.3 HEAD. Looks like there's a couple things which are problematic: - DefineIndex was updated only in bootparse.c, not in bootparse.y - The patch contains changes to pg_config.h - THICK isn't defined in gram.y (as a token or under unreserved_keywords), so compilation of keywords.c fails. In the future, please make changes to the proper pre-built files so that someone doesn't have to configure it, then patch it. I have them fixed and will submit the patch back here if you'd like. Or, you can fix it. It's up to you :) -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Revised xml memory allocation patch
On Jan 15, 2008 5:04 AM, Neil Conway <[EMAIL PROTECTED]> wrote: > > Any thoughts whether to apply or not? > > Seems like a much more sane approach to me -- +1. Agreed, sounds and looks better to me. +1 -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] [DOCS] Partition: use triggers instead of rules
On Nov 28, 2007 3:53 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > Entirely removing the example of how to do it with rules doesn't > seem like a good idea. Agreed. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation| fax: 732.331.1301 499 Thornall Street, 2nd Floor | [EMAIL PROTECTED] Edison, NJ 08837| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
On 10/26/06, Gregory Stark <[EMAIL PROTECTED]> wrote: I think what you want is to add a new method entry in pg_type to allow a type to declare a method to tell you whether a change is work-free or not. Then any type, even user-defined types, can allow some changes to be work-free and some not without exposing any implementation details outside the type. Seems like too much work for a fairly simple use-case. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(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] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
On 10/26/06, Tom Lane <[EMAIL PROTECTED]> wrote: This makes some really quite unacceptable assumptions about the meaning and encoding of typmod ... True, so VARCHAR seems like the only one? That's the only one I've really encountered in the field on a fairly regular basis. I'm also wondering what's the point of comparing attbyval etc when you've already checked it's the same type OID. True. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] Eliminating phase 3 requirement for varlen increases via ALTER COLUMN
The attached patch handles the simple case where a user wants to
increase the user-defined storage size of a variable length object,
such as VARCHAR or NUMERIC, without having to rebuild the table.
It does so by verifying that no transform was defined and testing
whether the user simply requested an increase to the size of a
variable length column. If so, it skips type coercion and no longer
performs ATRewriteTables; instead, it allows ATExecAlterColumnType to
update the catalog to reflect said increase to the attribute's typmod
(as it currently does) and skips phase 3. The former way to perform
this was by manually updating the catalog.
The current case of ALTERing a column to it's current data type
specification (often used to forcibly reclaim dropped column space)
has been maintained.
Use case:
CREATE TABLE test_tbl (
test_idNUMERIC(10) NOT NULL,
test_name VARCHAR(32) NOT NULL,
PRIMARY KEY (test_id));
ALTER TABLE test_tbl ALTER COLUMN test_name TYPE VARCHAR(64);
I can't find any case where this doesn't work... but perhaps you guys
know of some.
--
Jonah H. Harris, Software Architect | phone: 732.331.1300
EnterpriseDB Corporation| fax: 732.331.1301
33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED]
Iselin, New Jersey 08830| http://www.enterprisedb.com/
diff -cr pgsql/src/backend/commands/tablecmds.c pgsql-typmodvlincfix/src/backend/commands/tablecmds.c
*** pgsql/src/backend/commands/tablecmds.c 2006-10-13 17:43:18.0 -0400
--- pgsql-typmodvlincfix/src/backend/commands/tablecmds.c 2006-10-26 12:19:30.0 -0400
***
*** 4880,4885
--- 4880,4888
Node *transform;
NewColumnValue *newval;
ParseState *pstate = make_parsestate(NULL);
+ bool isSimpleTypemodIncrease = false;
+ HeapTuple typeTuple;
+ Form_pg_type tform;
/* lookup the attribute so we can check inheritance status */
tuple = SearchSysCacheAttName(RelationGetRelid(rel), colName);
***
*** 4906,4912
colName)));
/* Look up the target type */
! targettype = typenameTypeId(NULL, typename);
/* make sure datatype is legal for a column */
CheckAttributeType(colName, targettype);
--- 4909,4917
colName)));
/* Look up the target type */
! typeTuple = typenameType(NULL, typename);
! tform = (Form_pg_type) GETSTRUCT(typeTuple);
! targettype = HeapTupleGetOid(typeTuple);
/* make sure datatype is legal for a column */
CheckAttributeType(colName, targettype);
***
*** 4951,4982
}
else
{
! transform = (Node *) makeVar(1, attnum,
! attTup->atttypid, attTup->atttypmod,
! 0);
}
- transform = coerce_to_target_type(pstate,
- transform, exprType(transform),
- targettype, typename->typmod,
- COERCION_ASSIGNMENT,
- COERCE_IMPLICIT_CAST);
- if (transform == NULL)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("column \"%s\" cannot be cast to type \"%s\"",
- colName, TypeNameToString(typename;
-
/*
! * Add a work queue item to make ATRewriteTable update the column
! * contents.
! */
! newval = (NewColumnValue *) palloc0(sizeof(NewColumnValue));
! newval->attnum = attnum;
! newval->expr = (Expr *) transform;
! tab->newvals = lappend(tab->newvals, newval);
ReleaseSysCache(tuple);
/*
--- 4956,5012
}
else
{
! /*
! * As no transform was defined, we want to test whether the user
! * simply requested an increase to the size of a variable length
! * column. If so, we don't need to perform ATRewriteTables and will
! * instead, just update the catalog to reflect said increase
! * to the attribute's typmod.
! */
! if (attTup->atttypid == targettype
! && attTup->atttypmod < typename->typmod
! && attTup->attndims == list_length(typename->arrayBounds)
! && (attTup->attlen == tform->typlen && tform->typlen == -1)
! && attTup->attbyval == tform->typbyval
! && attTup->attalign == tform->typalign
! && attTup->attstorage == tform->typstorage)
!
! isSimpleTypemodIncrease = true;
! else
! transform = (Node *) makeVar(1, attnum,
! attTup->atttypid, attTup->atttypmod,
! 0);
}
/*
! * If this is just a simple typmod increase, there's no need to
! * coerce types.
! */
! if (!isSimpleTypemodIncrease)
! {
! transform = coerce_to_target_type(pstate,
! transform, exprType(transform),
! targettype, typename->typmod,
! COERCION_ASSIGNMENT,
! COERCE_IMPLICIT_CAST);
! if (transform == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("column \"%s\" can
Re: [PATCHES] Proposed patch for rules with RETURNING
On 9/1/06, Tom Lane <[EMAIL PROTECTED]> wrote: Attached is a draft patch that makes rules support RETURNING ... What format is that patch in? Either something's wrong or Gmail just went really wacko. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Adding fulldisjunctions to the contrib
Y'know I was gonna check up on that because my recollection was that it was a 2/2 split as well, though I thought that was of people who made their view clear rather than just -core (whose opinion in this case is no more important than any of the other long time contributors imho). Don't suppose you noted the views of the other 6? As counted, regarding inclusion in /contrib the thread sits at 5 for, 4 against, and 1 seems to lean toward making it a contrib. Just in case my counting is wrong, this is what I've marked: Tzahi Fadida - For Bruce Momjian - Against AgentM - Possibly For Tom Lane - Against Jonah Harris - For David Fetter - For Josh Drake - Against Andrew Dunstan - Against Josh Berkus - For Dave Page - For -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Adding fulldisjunctions to the contrib
On 8/27/06, Joshua D. Drake <[EMAIL PROTECTED]> wrote: 1. Is not quite complete Only because it wasn't merged into the core. Which, like I said, would be difficult to get consensus on design, grammar, and implementation when it's a brand new and non-standard feature only a few people understand. I honestly don't think a project like that would've ever gotten off the ground in -hackers. Being a contrib module makes it a bit more flexible and gives people the chance to try it out; that way we'll see if it's worth merging into the core. Think of it as a Phase I of Full DIsjunctions... Phase II is a bit of a redesign and merge into 8.3. 2. Does not follow postgresql style guidelines This statement was not made. I believe those two points were made by Tom but I can't find his response so if I am on crack -- I apologize in advance. One of the points, taken a little out of context, was made by Tom. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] Adding fulldisjunctions to the contrib
On 8/26/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote: So, yes, it is used, and by far more that just hard core hackers. OK. Kewl. I just hadn't run into many people (except hackers) that knew about it. Thanks for sharing that. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(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] Adding fulldisjunctions to the contrib
On 8/26/06, Joshua D. Drake <[EMAIL PROTECTED]> wrote: Your attitude has been lacking about this whole thing, as has a lot of other people. PgFoundry is the official sub project site for PostgreSQL. That may be the case. However, all I've seen+heard is conjecture that pgfoundry is a good thing; where's the proof? Show me and other fellow "whiners" that a lot of people use pgfoundry and I'll gladly shut up about it. It is not a graveyard, projects on PgFoundry should receive full advocacy and promotion about their abilities and their linkage PostgreSQL. See previous email to Andrew regarding projects that don't work with the latest versions of PostgreSQL. I think I've even seen a pgfoundry project last updated for 7.x; that's certainly the case for gborg. If we spent half as much time promoting and helping the various sub project succeed as we doing whining on this list, we would be far more dominant in the industry then we are. So, subprojects [pgfoundry] is the source of all industry dominance? I wish I would've known that before :) Sorry, I was itchin' to say it. I am sick of all the moaning that goes on, So am I... in general. When full disjunctons is ready, I am sure it will be considered for core. It currently is not and pgFoundry is the perfect place for until until then. As it's not a common feature, I don't think many of the hackers know what it is or what it does. Certainly, very few have spoken on this thread. It's odd, only 10 people have commented on this thread; 4 of which are core members, 2 in favor and 2 against. Yet, we're having an argument on why this wasn't included. Unless this is the new math, 2 vs. 2 seems like a tie to me. We can still promote and announce we have a full disjunctions implementation, just as we can advertise we have full text indexing. Wherever it ends up, I look forward to seeing the promotion and announcements. Tzahi has put a lot of work into it over the past few months. I'm done on this topic but would gladly appreciate public or private proof regarding pgfoundry's popularity. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] Adding fulldisjunctions to the contrib
On 8/26/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote: this is inaccurate, irresponsible and insulting to those of us who spend time maintaining pgfoundry. Andrew, I'm sorry if it sounded that way... it wasn't meant as such. It is not a graveyard. Plenty of stuff outside the core gets included in packaged distributions - just see for example what goes into the Windows distro, or the packages that CP distributes. I'm not saying that *everything* on pgfoundry is junk... but I can start naming dead projects if you'd like. It's like SourceForge before SourceForge jumped the shark... now 90% of SourceForge is either projects dead-and-gone or which hadn't even started. It's almost not even worth the time to search SF.net anymore. I believe that's the direction pgfoundry is headed. Not because of poor management or administration... just that when you have a large number of projects, the majority of which are dead or not even worth viewing, it takes the credibility of the site down as a whole. Look at gborg... there was some good stuff there and there still is; if you already know about it. Both gborg and pgfoundry have projects on there won't even work with a current version of PostgreSQL. Outside of all us hackers... how many people actually use pgfoundry? Does anyone have the stats? Has anyone polled users? How many of the users are newbies and how many are already familiar with PostgreSQL? If we don't have these basic answers, continuing to praise pgfoundry as the home for all-things-PostgreSQL is pointless. The implication of your statement is that anything not accepted into the core is automatically somehow considered unworthy. Not at all. I'm referring to this case in particular. Please refer to Tom's recent remarks about playing on extensibility as one of our strengths. I never said it wasn't... extensibility is, IMHO, our *core* strength. However, I don't think that's a good reason for pushing everything to pgfoundry. My impression (please correct me if I'm wrong) is that proper full disjunction support would include grammar support, in which case contrib is not where it should belong anyway. If that's so, then the next step would be for somebody to pick up the work that Tzahi has done and take it the rest of the way. That would be a worth goal for 8.3. You are correct, a *full* implementation would most likely include integration into the core; grammar and all. However, being as it's an entirely new feature in any database system ever seen, I don't think it should be required. It's kind of funny though; it's difficult enough to convince -hackers to adopt a feature that every other database system in the world has, yet we're going to make it even more difficult for an innovative feature. I can only imagine trying to get a consensus on the grammar and implementation of a totally nonstandard feature that only a few people really understand. As I see it, the full disjunction code will likely end up being a low profile project on pgfoundry because Tzahi won't have time to continue maintaining it and not many of us have enough insight into it to do so ourselves. As such, I don't think it's going to get enough attention and enough of a user following to make it worth the time of one of the core developers to pick it up. Of course, I may always be wrong. Perhaps pgfoundry is more popular than I've seen in past experience. Maybe one of the core developers does want to pick up full disjunctions for 8.3. Guess we'll just have to wait and see... -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Adding fulldisjunctions to the contrib
On 8/25/06, Bruce Momjian <[EMAIL PROTECTED]> wrote: Sorry, we did not get enough feedback to include this in 8.2. Please add it to pgfoundry and let's see how it goes. Yep... it's too bad. A new feature no other database has now goes to it's final resting place on pgfoundry. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Updated INSERT/UPDATE RETURNING
On 8/5/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Jonah H. Harris" <[EMAIL PROTECTED]> writes: > Here's the updated patch with DELETE RETURNING removed. This isn't > really an issue because no one wanted DELETE RETURNING to begin with. I don't have the time to add DELETE RETURNING back in. My initial patch for this included it, so anyone can feel free to pick it up and add it back. If no one wants to do this, I'd just say to add INS/UPD version to 8.2 and I'll work on the DELETE version for 8.3. As far as the current patch, I'm actually fine with someone reviewing it as-is; I just wanted another look over it to see if I missed something... but one of you may see that much quicker than I (as I'm too close too it). -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PATCHES] Maintaining cluster order on insert
On 8/9/06, Tom Lane <[EMAIL PROTECTED]> wrote: UPDATE tries to place the new tuple on the same page it's already on. I think he meant for INSERT. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] Updated INSERT/UPDATE RETURNING
On 8/5/06, Tom Lane <[EMAIL PROTECTED]> wrote: Huh? Why'd you remove it? I can't imagine it makes things significantly simpler to omit that case, and even if you can't think of uses for it, I can (taking jobs from a to-do queue for instance). It can be added back. Dequeing is a good use-case idea though :) BTW, it occurs to me to wonder whether we've picked a good choice of syntax. I don't remember where the suggestion to use "RETURNING" came from (did we borrow it from another DBMS?). Oracle. DB2 uses something similar to SELECT * FROM (UPDATE tbl SET ... ); But AFAICS this syntax will require the introducing keyword to be a fully reserved word, and since RETURNING is not listed as a reserved word in the SQL spec, reserving it is arguably a spec violation. True. The simplest alternative that comes to mind is to use RETURNS instead I don't have a strong feeling either way, but now is the time to decide. I don't care either way, RETURNS is fine I guess. OK, but we need a final version soon. Sure thing. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[PATCHES] Updated INSERT/UPDATE RETURNING
Here's the updated patch with DELETE RETURNING removed. This isn't really an issue because no one wanted DELETE RETURNING to begin with. It is important to note that this patch is not yet ready to be committed. I still need to go through and run some more tests on it but wanted to put it in the queue again and let ya know I've been given time to make sure it gets in. This patch includes: - Code changes to core - Code changes to PL/pgSQL - Preliminary Documentation Updates (need to add to PL/pgSQL docs) - Preliminary Regression Tests (need to add PL/pgSQL regressions) There were a couple suggestions for sorta-kewl features like being able to use INSERT/UPDATE RETURNING in a FOR loop, etc. I may be able to get those in if people really want it but I looked into it after Neil mentioned it and IIRC, there are quite a few changes required to support it. Suggestions requested. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ pg82-iuret-073106.patch Description: Binary data ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] New variable server_version_num
On 7/30/06, David Fetter <[EMAIL PROTECTED]> wrote: Failure to parse means the transaction bails out, which is just what I want in my case, as it disallows people attempting to run the programs--they're for DBI-Link--on too early a version of PostgreSQL. As there are some subtleties to the implementation, I need something that quickly returns boolean or fails entirely when it detects same. From an application development standpoint, it would be nice to have a strictly numeric version returning function for checking server compatibility. -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] table/index fillfactor control
On 6/6/06, Jonah H. Harris <[EMAIL PROTECTED]> wrote: On 6/6/06, ITAGAKI Takahiro <[EMAIL PROTECTED]> wrote: > This is a patch for table/index fillfactor control discussed in > http://archives.postgresql.org/pgsql-hackers/2006-06/msg00175.php There's 4 shift/reduce conflicts which I believe are caused by having used WITH... did you plan to fix this? BTW, I think this is nice functionality and definitely second Simon & Tom's ideas. Thanks for picking it up again :) -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(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] table/index fillfactor control
On 6/6/06, ITAGAKI Takahiro <[EMAIL PROTECTED]> wrote: This is a patch for table/index fillfactor control discussed in http://archives.postgresql.org/pgsql-hackers/2006-06/msg00175.php There's 4 shift/reduce conflicts which I believe are caused by having used WITH... did you plan to fix this? -- Jonah H. Harris, Software Architect | phone: 732.331.1300 EnterpriseDB Corporation| fax: 732.331.1301 33 Wood Ave S, 2nd Floor| [EMAIL PROTECTED] Iselin, New Jersey 08830| http://www.enterprisedb.com/ ---(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] INS/UPD/DEL RETURNING for 8.2
On 3/2/06, Tom Lane <[EMAIL PROTECTED]> wrote: > For example, should it be possible to write: > FOR x in DELETE FROM t1 WHERE ... RETURNING t1.x LOOP Seems like you'd want to get there eventually, if not in the first cut. I'd like to get this into the first release of RETURNING for 8.2. I wonder if we should rejigger the representation of Query so that a FOO-RETURNING command actually *is* a SELECT in some sense, so that there's no need for special cases. I want to get rid of all the special case code and move in this direction, that way we can make better use of code that already exists and is well-tested. I'm a bit fuzzy about how this would work exactly --- you still need to keep track of two targetlists it seems --- but it's worth thinking about. I've had a bee in my bonnet for literally years about the fact that INSERT/SELECT really needs two levels of targetlist, as does UNION. Maybe if we thought a little bit larger we could clean up all of that messiness at one stroke. Have you had any ideas on how to best accomplish this? -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PATCHES] WIP: splitting BLCKSZ
On 4/3/06, Mark Wong <[EMAIL PROTECTED]> wrote: > Once I get a test system going again in the lab I'll start > posting some data. I'm planning a combination of > block sizes (BLCKSZ and XLOG_BLCKSZ) and number > of WAL buffers. Cool. I'm looking forward to the results. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(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 SYNONYM ...
On 3/14/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote: Maybe one of the proponents could put together a comparison matrix ofhow this is done in each of the databases previously mentioned. Hans, I don't have time to do this, would you like to take a stab at it? -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/13/06, Peter Eisentraut <[EMAIL PROTECTED]> wrote: Does any SQL-like database system other than Oracle have this feature? I know that SQL Server, DB2, SAP DB/MAX DB, and Mimer have it. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: You responded with a statement about synonyms having cost in response to amessage of mine in response to a message from [EMAIL PROTECTED] whichsaid "the costs will only be added if the "real table" is not found. therefore there is no impact on "normal" users." Your idea and that of theperson who submitted this patch thus seem to be slightly different. Yes, this is something that Hans and I would discuss about the patch. I'd rather the feature follows the model youdescribed (although I may have greater concerns of the cost) because it seems consistent with other lookups. This is correct, for a synonym to be added, the cost for 1 synonym would be the same as adding 1 more table/function/view/etc depending on the catalog you're searching; I don't think it's too costly. I'm going to start a new thread to propose this clearly. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: What feature though? Part of the definition of a feature like synonym hasto nail down things like how it interacts with search pathI've said how it interacts with the search path (with Oracle) several times and illustrated it in the last one, let's not go through this again. The message I was responding to was talking about the patch and seeming to say that there wasn't a cost for non-users because the search was done iff acandidate object wasn't found. IMHO, this is a different feature than asynonym feature for which each search path entry is checked so that synonyms in earlier path entries shadow later concrete objects. Weprobably don't want both features even if we want either, but they'rereally different features.Like I said in the email before this, there is a way to limit the cost of synonyms for ONLY if a real object does not exist in the search path. However, this would be odd behavior regarding namespace searching IMHO. I think the only *good* implementation is to follow the search path as it is now and include synonyms in it... this would mean a cost for any query whether or not a synonym were used or not. The real question is how to lessen the cost if we decide to implement the functionality. I would say that that's a really bad choice, and Joe should have hissynonyms somewhere other than public so as not to pollute other people's default search path with his particular needs that may not be the same assomeone else's. What does Jane do now when she needs the opposite set andwhy is Joe's choice more relevant than Jane's? Joe and Jane could create synonyms locally in their own schemas, so this isn't an issue at all. The demonstration example was representative of many ERP systems where a synonym is publicly shared by all modules and you wouldn't have the "Jane" issue. I'm nearly done fighting this... synonyms are useful functionality that many people in this discussion have not used. I've explained how it works in Oracle and the reasoning behind it. If we want to limit users to search_path for the sake of not being Oracle, fine. I have patches to work on and this seems to be going nowhere. I'm open to helping anyone implement similar functionality and/or discussion, but this thread has too many sub-discussions to be useful. For functionality descriptions, see the Oracle docs. I'm not averse to straying from Oracle's way of doing it if it makes sense. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: Yes, however there are two slightly separate discussions going on and Ithink you're taking them as a single discussion. I agree that there are two discussions happening in this thread, but I don't think anyone has agreed at all that this patch as it is would be acceptable for various reasons. There are a couple things that Hans and I will discuss about the patch assuming we decide this is a feature that would be nice for PostgreSQL. If your search path is A,B and there is a B.EMPLOYEE table and an A.EMPLOYEE synonym to HR.EMPLOYEE, which table does select * from EMPLOYEEread? The one first in your search path. You could not, for example, create a SYNONYM called EMPLOYEE in the HR schema as it would conflict with the EMPLOYEE table. Synonyms act like the objects they represent in term of namespace searches. ASSUME: CREATE USER joe; CREATE SCHEMA AUTHORIZATION joe; Joe's search_path is $user,public CREATE SCHEMA hr; CREATE TABLE hr.employee (emp_id, ...) CREATE TABLE hr.payroll (emp_id, ...) CREATE TABLE hr.commissions; For Joe to see this, they either have to add HR to their search_path or fully qualify it. Let's assume they use current PostgreSQL behavior: SET search_path TO ..., HR Now they can SELECT * FROM EMPLOYEE where EMPLOYEE is HR.EMPLOYEE Now assume: CREATE SCHEMA crm; CREATE TABLE crm.employee; CREATE TABLE crm.customer; CREATE TABLE crm.commissions; Now, joe needs to query customer and employee without qualification... HR.EMPLOYEE is the common table that, with the exception of the CRM module, the application refers to simply as EMPLOYEE. Now what does Joe do: SET search_path TO ..., HR, CRM; OK, they still have the tables named correctly but they have to manually make sure they order search_path. Now, you tell me (without qualification) how Joe can access the CRM commissions table? They can't. With synonyms, the search path for Joe would remain $user, public and one could easily do CREATE SYNONYM public.employee FOR hr.employee; CREATE SYNONYM public.commissions FOR crm.commissions; As Joe: SELECT * FROM EMPLOYEE becomes SELECT * FROM HR.EMPLOYEE As Joe: SELECT * FROM COMMISSIONS becomes SELECT * FROM CRM.COMMISSIONS I guess synonym searching could be done iff no object were found in the current search. I don't know why I thought it would be just as costly (perhaps too much Sam Adams). The worst-case scenario would be an additional search only if an object weren't found in a catalog search, basically this would be the cost of using synonyms and wouldn't affect performance for everyone else. Oracle does have a small cost only when using synonyms as well. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
GRR, not enough coffee yet today :(
Jonah now enjoys some SumatraOn 3/8/06, Jonah H. Harris <[EMAIL PROTECTED]
> wrote:I agree wholeheartedly.
regarding "we should take a fresh look at the problem rather than just blindly copying a construct from Oracle".
SELECT dblink('host=somehost dbname=remotedb ...', 'SELECT
emp_id,first_name,last_name,middle_name,birth_dt,ssn,... FROM EMPLOYEE
WHERE last_name = ''Blow''') AS t1(emp_id BIGINT, first_name VARCHAR,
last_name VARCHAR, middle_name VARCHAR, birth_dt DATE, ssn NUMERIC, ...)
should be SELECT * FROM ...
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/8/06, Neil Conway <[EMAIL PROTECTED]> wrote:
I'm inclined to agree. The points raised about the difficulties ofmanaging large numbers of schemas are legitimate, but I don't see thatsynonyms are a very effective solution. If we're going to make it lesspainful to work on applications with many tens of schemas, that's a
worthwhile project, but I think we should take a fresh look at theproblem rather than just blindly copying a construct from Oracle.
I agree wholeheartedly.
As for the idea that because we're not supporting database links (which
synonyms are used for a lot) we shouldn't support synonyms, now that we
have 2PC, it's now possible to reimplement the dblink contrib module
into something which supported nicer database linking. Don't get
me wrong, it certainly would be a task, but it's doable and I know a
number of people who use the dblink contrib module on a daily basis for
data copying and remote querying.
I don't know anyone that really likes typing:
SELECT dblink('host=somehost dbname=remotedb ...', 'SELECT
emp_id,first_name,last_name,middle_name,birth_dt,ssn,... FROM EMPLOYEE
WHERE last_name = ''Blow''') AS t1(emp_id BIGINT, first_name VARCHAR,
last_name VARCHAR, middle_name VARCHAR, birth_dt DATE, ssn NUMERIC, ...)
instead of:
SELECT * FROM [EMAIL PROTECTED] WHERE last_name = 'Blow';
If that's not bad enough, just try to do a lot of dynamic work using
database links using the contrib module... it's not easy or efficient
having to create types, functions, views, and rules to do dynamic work.
This is another discussion in and of itself, but I don't think
supporting nicer database links is a discussion that's too far off
either. I'm glad we have the contrib module, but there's a lot of
nicer things we could do there as well. Not because it's an
Oracle thing, but because it's great functionality to have.
-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/8/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: Doesn't that pretty much go against the (I thought) outstanding behavioralquestion of whether the synonyms are scoped and obey search path? If theydo, I don't see how the above rule can hold since finding the "real table" is insufficient to know if there's an earlier synonym. There is a cost for synonyms no matter how you look at it. Assume your user has it's own schema, that there is a synonym in public for EMPLOYEE which pointed to HR.EMPLOYEE, and your search path is $user,public. If you do a SELECT * FROM EMPLOYEE, the search order is still the same as it is in PostgreSQL now, there's no EMPLOYEE table in the $user schema, so when it gets to searching public, it finds the synonym. The only alternative in this scenario is to create the EMPLOYEE table in public (which is pretty stupid in most cases), or to set the search path to $user,public,hr. Again, this doesn't cover the "same-named tables in multiple schemas" argument, but it does illustrate that PostgreSQL's namespace scoping remains the same. The question is whether we want to offer the functionality and what the least intrusive way to handle it is. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: To be blunt, those people aren't going to be moving to Postgres anyhow.If the notion of fixing this issue daunts them, they are not going to bewilling to deal with the other incompatibilities between Oracle and PG. IMHO, we're not discussing incompatibilities. We're discussing functionality which PostgreSQL does not offer. The real question is should PostgreSQL offer similar functionality? And we are *not* buying into the notion of becoming a bug-compatibleOracle clone. I don't think I said anything about that. Synonyms are not a bug, they are a feature which offers functionality that PostgreSQL (in some cases) cannot. I'm not saying we should clone synonyms just because Oracle has them; instead, I think we could find a way to offer similar functionality regardless of who/where the idea came from. Like it or not, people use the proprietary functionality bigger vendors offer; that's partly why the big vendors exist. When people move towards an open source database they generally look at PostgreSQL first as we are known for being the, "most advanced open source database" but in the same breath we way things like "we're not going to copy Oracle/SQL Server/Sybase/DB2 features." You're right, we all agree that we shouldn't just add something because [insert database vendor name here] has it, but we should at least have an open mind and look at it from a functionality/migration perspective before dismissing it. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/7/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Well, if you don't want to have a monstrous search path with 130+schemas, then you'll have a monstrous amount of synonyms. Given thatschemas are a way to separate the object namespace, it seems moresensible to me to propagate the user of reasonable search paths than the use of hundreds (thousands?) of synonyms. Like I said, sometimes the user doesn't have a choice. Sure, it's easy to tell someone that has a 300-line PHP application to fix their code, but I've worked with people who have hundreds of thousands of lines of code and they don't just say, "gee, let's just search-and-replace everything!"; that's a testing nightmare. Also, there's *usually* not thousands of synonyms, usually tens or hundreds. Again, they are mainly used to easily reference objects which exist in other schemas or where there are duplicate object names across schemas. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/7/06, Alvaro Herrera <[EMAIL PROTECTED]> wrote: Tom Lane wrote:> (Actually, I don't think the case for table synonyms has been made> adequately either; "Oracle has it" is *not* enough reason to take on> another feature that we'll have to maintain forever, especially given > that we're being told that one of the major use-cases for synonyms> isn't going to be supported. AFAICS this patch does nothing you> couldn't do much better with a quick search-and-replace over your > application code. In short, I remain unsold.)What I don't really understand is what part of this cannot be achievedby changing the search_path. The only case I can think of is when youhave tables A and B in schemas R and S, but you want to use R.A and S.B.So there's no way to change search_path for this. But is this reallythe intended use case? Not totally intended, but (unfortunately) used nonetheless. I wonder whether synonyms were introduced in Oracle because of that ideaof theirs that each user has its own schema, and can access that schema only; so to use a table in another schema you need to create a synonym.We don't have that limitation so we don't need that usage either. No, one could do fully qualified naming in Oracle; synonyms do have other purposes outside of this single one listed. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: (Actually, I don't think the case for table synonyms has been madeadequately either; "Oracle has it" is *not* enough reason to take onanother feature that we'll have to maintain forever, especially given that we're being told that one of the major use-cases for synonymsisn't going to be supported. AFAICS this patch does nothing youcouldn't do much better with a quick search-and-replace over yourapplication code. In short, I remain unsold.) I agree with this to some extent. The main use case, aside from database link objects, is really for generally large applications such as a large ERP system. Most ERP systems have a general or foundation-like schema where common objects lie and each module is separated using schemas. As an example, you would have HR, AP, AR, GL, FA, COMMON, ... schemas which encapsulate the functionality of their respective modules whether it be procedures, functions, views, tables, etc. For each module to be able to access, for example, the HR.EMPLOYEE table, they generally refer to just EMPLOYEE which is a synonym to HR.EMPLOYEE. Now, one may argue that it's incorrect/bad application-design to not use fully qualified names, however, there are cases (especially in VERY large database applications) where you do not want to use fully qualified naming. In PostgreSQL, the alternative to synonyms is to have a monstrous search path $user, public, HR, AP, AR, GL, FA, COMMON... Not that we have Oracle Applications running on PostgreSQL, but 11i has something like 130+? schemas which would be pretty nasty and semi-unprofessional as a search_path rather than as something defined similar to synonyms. Another consideration is poor application design which uses the same named table in one schema which acts differently than the same named table in another schema... synonyms resolve this issue which could be problematic if not impossible to solve using search_path alone. Without the database link case, the functional reason for not using search_path is surely reduced but it is in no way wholly eliminated either. Some users don't have the ability to choose how vendors/developers write their software and they can't easily just convert an entire application to use search_path where they once had synonyms (especially if the application is fairly sizable). -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/7/06, Stephan Szabo <[EMAIL PROTECTED]> wrote: I'd personally be more interested in what the impact is on people notusing synonyms. How free is any search for synonyms if you aren't usingthe feature? Unless synonym enablement were a configurable parameter (which wouldn't really make sense), the cost would be the same whether they're used or not during searching.-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] CREATE SYNONYM ...
On 3/7/06, Hans-Jürgen Schönig <[EMAIL PROTECTED]> wrote: we should definitely work together.what is the status of your patch?maybe we can discuss this off list? The last time I worked on it was on 8.0 (I think), but it wouldn't take much to get it up to speed on 8.2. It's actually very similar to yours so it would probably be just as easy to start off with your patch. I'm open to whatever but I'm really busy so I can only devote some time to it if it's likely to be accepted. -Jonah
Re: [PATCHES] CREATE SYNONYM ...
On 3/7/06, Tom Lane <[EMAIL PROTECTED]> wrote: I'm inclined to reject this patch on the grounds that it doesn't dowhat Oracle does and does not look like it could be extended to do whatOracle does. My understanding is that what Oracle people mostly usesynonyms for is to provide cross-database access --- and this can't do that. I'm not in favor of providing syntax compatibility if we don'thave functional compatibility; I think that isn't doing anyone anyfavors. And if the behavior does get used, then we'd have a backwards compatibility problem if anyone ever wants to do it right. People in Oracle use synonyms for two reasons... either as a synonym to an object over a database link or to an object in another schema. I have an almost completed patch similar to this one that does act as Oracle does (albeit limited for database links because we don't support them as Oracle does such as [EMAIL PROTECTED]). I'm also quite dubious that this would work properly, because it hooksinto table and function lookup in only one place respectively. It's hard to believe that only one of the many lookups for tables andfunctions needs to be changed. I did pretty much the same thing for candidate lookups and haven't found a problem yet, but that's not to say there isn't one. The semantics of namespace search seem wrong; I would think that a synonym in schema A should mask a table in schema B if A precedes Bon the search path, but this doesn't work that way. This is correct, A should always precede B in namespace lookups. I'm also not very happy about adding an additional catalog search tofunction and table lookup, which are already quite expensive enough. (The last two objections might both be addressed by forgetting thenotion of a separate catalog and instead making synonyms be alternative kinds of entries in pg_class and pg_proc. However, that does nothing tohelp with the cross-database problem, and might indeed hinder it.) Don't know how to really get around the additional lookup without extending pg_class and pg_proc. Even so, this would still add overhead to catalog searches. Just for the record, this is lacking pg_dump support as well asdocumentation. True. I'd be glad to submit my patch and/or cleanup this one if its something the community would be willing to accept.-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] INS/UPD/DEL RETURNING for 8.2
On 3/2/06, Jonah H. Harris <[EMAIL PROTECTED]> wrote: On 3/2/06, Tom Lane <[EMAIL PROTECTED] > wrote: "Jonah H. Harris" <[EMAIL PROTECTED]> writes:> INSERT/UPDATE/DELETE seem to work fine in normal operation but there is an > error with DELETE RETURNING when used through PL/pgSQL. Probably other places too. I don't see any provision here for ensuringthat the variables used in the RETURNING list are actually computed bythe plan. This would be masked in the INSERT and non-join UPDATE cases by the fact that the plan has to compute all columns of the target tableanyway ... but in a DELETE it'd be an issue.I think set-returning functions in the RETURNING list might give yousome fits too ... Yeah, I got to looking into the special tuple handling code in execUtils for retrieving the old (deleted) tuple and there's something definitely getting lost along the way in some cases. I've been stewing on this and haven't yet come up with anything. Have you any thoughts on how we can accomplish this better? -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] INS/UPD/DEL RETURNING for 8.2
On 3/2/06, Tom Lane <[EMAIL PROTECTED]> wrote: "Jonah H. Harris" <[EMAIL PROTECTED]> writes:> INSERT/UPDATE/DELETE seem to work fine in normal operation but there is an> error with DELETE RETURNING when used through PL/pgSQL. Probably other places too. I don't see any provision here for ensuringthat the variables used in the RETURNING list are actually computed bythe plan. This would be masked in the INSERT and non-join UPDATE cases by the fact that the plan has to compute all columns of the target tableanyway ... but in a DELETE it'd be an issue.I think set-returning functions in the RETURNING list might give yousome fits too ... Yeah, I got to looking into the special tuple handling code in execUtils for retrieving the old (deleted) tuple and there's something definitely getting lost along the way in some cases. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
Re: [PATCHES] INS/UPD/DEL RETURNING for 8.2
On 3/2/06, Tom Lane <[EMAIL PROTECTED]> wrote: This might tie into something that was bothering me about Jonah'sfirst-cut patch, which was that he was introducing special cases intoplaces where it didn't seem real appropriate (like printtup.c). Iwonder if we should rejigger the representation of Query so that a FOO-RETURNING command actually *is* a SELECT in some sense, so thatthere's no need for special cases.I was thinking along the same lines. This is Omar's patch updated to 8.2 but as I get to looking through it, there are a couple things that could be cleaned up. I paced around a bit today trying to theorize how this could be done without a lot of changes and retaining the speed increase gained by not performing two separate operations. I'm a bit fuzzy about how this would work exactly --- you still need tokeep track of two targetlists it seems --- but it's worth thinking about. I've had a bee in my bonnet for literally years about the factthat INSERT/SELECT really needs two levels of targetlist, as does UNION.Maybe if we thought a little bit larger we could clean up all of that messiness at one stroke.I'm definitely open to looking into it. Any suggestions are always welcome. -- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation 732.331.1324
[PATCHES] INS/UPD/DEL RETURNING for 8.2
Patch for core and PL/pgSQL to support the
INSERT/UPDATE/DELETE RETURNING syntax in 8.2
INSERT/UPDATE/DELETE seem to work fine in normal operation but there is
an error with DELETE RETURNING when used through PL/pgSQL.
Here's an example PL/pgSQL test:
CREATE SEQUENCE test_id_seq START 1 INCREMENT 1;
CREATE TABLE test_tbl (
test_id BIGINT NOT NULL
DEFAULT nextval('test_id_seq'),
test_name VARCHAR(64) NOT NULL,
PRIMARY KEY (test_id));
CREATE OR REPLACE FUNCTION test_func (test_nm VARCHAR)
RETURNS VOID AS $$
DECLARE
current_rec RECORD;
BEGIN
-- Test INSERT RETURNING
INSERT INTO test_tbl (test_name) VALUES (test_nm)
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test UPDATE RETURNING
UPDATE test_tbl SET test_name = 'Uncle Bob'
WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
-- Test DELETE RETURNING
DELETE FROM test_tbl WHERE test_id = current_rec.test_id
RETURNING * INTO current_rec;
-- This DOES NOT WORK
RAISE NOTICE 'test_id is %', current_rec.test_id;
RAISE NOTICE 'test_name is %', current_rec.test_name;
RETURN;
END;
$$ LANGUAGE plpgsql;-- Jonah H. Harris, Database Internals ArchitectEnterpriseDB Corporation732.331.1324
diff -cr pgsql/src/backend/access/common/printtup.c pgsql-iudret/src/backend/access/common/printtup.c
*** pgsql/src/backend/access/common/printtup.c 2005-11-03 12:11:30.0 -0500
--- pgsql-iudret/src/backend/access/common/printtup.c 2006-03-02 12:07:43.0 -0500
***
*** 19,24
--- 19,25
#include "access/printtup.h"
#include "libpq/libpq.h"
#include "libpq/pqformat.h"
+ #include "executor/executor.h"
#include "tcop/pquery.h"
#include "utils/lsyscache.h"
#include "utils/portal.h"
***
*** 112,117
--- 113,120
{
DR_printtup *myState = (DR_printtup *) self;
Portal portal = myState->portal;
+ List *returning = ((Query *) linitial(portal->parseTrees))->returning;
+ bool withReturning = (returning != NIL);
if (PG_PROTOCOL_MAJOR(FrontendProtocol) < 3)
{
***
*** 136,142
SendRowDescriptionMessage(typeinfo,
FetchPortalTargetList(portal),
portal->formats);
!
/*
* We could set up the derived attr info at this time, but we postpone it
* until the first call of printtup, for 2 reasons:
--- 139,149
SendRowDescriptionMessage(typeinfo,
FetchPortalTargetList(portal),
portal->formats);
! else if (withReturning)
! SendRowDescriptionMessage(ExecTypeFromTL(returning, false),
! returning,
! portal->formats);
!
/*
* We could set up the derived attr info at this time, but we postpone it
* until the first call of printtup, for 2 reasons:
***
*** 305,311
/*
* send the attributes of this tuple
*/
! for (i = 0; i < natts; ++i)
{
PrinttupAttrInfo *thisState = myState->myinfo + i;
Datum origattr = slot->tts_values[i],
--- 312,318
/*
* send the attributes of this tuple
*/
! for (i = 0; i < natts; i++)
{
PrinttupAttrInfo *thisState = myState->myinfo + i;
Datum origattr = slot->tts_values[i],
diff -cr pgsql/src/backend/executor/execMain.c pgsql-iudret/src/backend/executor/execMain.c
*** pgsql/src/backend/executor/execMain.c 2006-02-27 23:10:27.0 -0500
--- pgsql-iudret/src/backend/executor/execMain.c 2006-03-02 12:07:43.0 -0500
***
*** 77,88
static void ExecSelect(TupleTableSlot *slot,
DestReceiver *dest,
EState *estate);
! static void ExecInsert(TupleTableSlot *slot, ItemPointer tupleid,
! EState *estate);
! static void ExecDelete(TupleTableSlot *slot, ItemPointer tupleid,
! EState *estate);
! static void ExecUpdate(TupleTableSlot *slot, ItemPointer tupleid,
! EState *estate);
static TupleTableSlot *EvalPlanQualNext(EState *estate);
static void EndEvalPlanQual(EState *estate);
static void ExecCheckRTEPerms(RangeTblEntry *rte);
--- 77,88
static void ExecSelect(TupleTableSlot *slot,
DestReceiver *dest,
EState *estate);
! static void ExecInsert(TupleTableSlot *slot, DestReceiver *dest,
!ItemPointer tupleid, EState *estate);
! static void ExecDelete(TupleTableSlot *slot, DestReceiver *dest,
! ItemPointer tupleid, EState *estate);
! static void ExecUpdate(TupleTableSlot *slot, DestReceiver *dest,
! ItemPointer tupleid, EState *estate);
static TupleTableSlot
