Re: [PATCHES] CREATE INDEX ... ONLINE
Greg Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: Updated patch. Fixed a few minor things, added documentation and regression tests. Unfortunately I can't test the regression tests because I get a segmentation fault earlier in the same file due to a GIN index build. So I haven't updated the expected output. What's the status of this patch --- have you done anything more with it since the 13-Jul submission? I haven't done anything. I was mostly waiting until I got some feedback about the code quality. I figured if there were parts that had to be reworked or fixed up I would prefer to do that first before worrying about perfecting details which is all that I believe remain. Just remembered one open question I had. I'm not clear what to do with the index statistics. It may be that the current code is basically the right thing -- it leaves the statistics as they are after phase 1, ie after the regular index build before we go through looking for newly added tuples. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] CREATE INDEX ... ONLINE
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: Updated patch. Fixed a few minor things, added documentation and regression tests. Unfortunately I can't test the regression tests because I get a segmentation fault earlier in the same file due to a GIN index build. So I haven't updated the expected output. What's the status of this patch --- have you done anything more with it since the 13-Jul submission? I haven't done anything. I was mostly waiting until I got some feedback about the code quality. I figured if there were parts that had to be reworked or fixed up I would prefer to do that first before worrying about perfecting details which is all that I believe remain. The only items that remain that I'm aware of are: a) Rename the command and make psql recognize it b) print a message when the wait finishes if we printed one when it started and possibly look into making an option to print the messages even if it doesn't have to wait at all -- currently it only prints the message if it sleeps at least once. I think the patch includes a more than adequate quantity of documentation though I haven't reread it since I wrote it and possibly it may need editing. Also, I don't recall writing any regression tests. But regression tests are kind of a joke here. It would be reasonable to toss one in just to ensure that we don't break non-transactional commands in psql or the grammar, but really no regression test based on a single psql connection is going to be able to test any of the potential bugs we hashed through at the code sprint to get this to work. It won't even be able to reach a big chunk of the code. -- Gregory Stark EnterpriseDB 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: [HACKERS] [PATCHES] Resurrecting per-page cleaner for btree
Jim Nasby [EMAIL PROTECTED] writes: Even if we stopped right there it would still be a huge win in many (most?) cases. How often do the indexes on a table comprise even 50% of the table's size? I would say they're usually roughly comparable actually. It depends on how wide your table is of course but the wider your table rows the more indexes you're likely to have on the table too. Even in the 50% case, you've gone from 1.5X to .6X Sure, and a 3x speedup is nothing to sneeze at, that would be a great improvement to vacuum. But it's still just a linear speedup and doesn't address the algorithmic problem. The fundamental problem is we have a process that's O(m) where m is the total space taken by a table and its indexes. The actual amount of space it has to reclaim is n. Other than nm there's basically no relationship between these figures. As long as that's the case vacuum may as well be O(n^2) or O(n!). We frequently assume -- and often it's a valid assumption -- that these figures are roughly proportional. Hence all the talk about databases reaching a steady-state where the amount of dead space is constantly being reclaimed at more or less the same speed it's being generated. But there are also plenty of use cases where a complete vacuum pass takes thousands of times longer than the i/o it took to generate those dead tuples. Currently Postgres just isn't that great a tool for those use cases. Unfortunately while I'm convinced of the problem I'm equally unconvinced of the solution. I tried to solve online index builds using retail index lookups in a very similar way to what's being discussed here. And ran into the same problems. I eventually decided that while it could be made to work that way it would be far too much code, far too unsafe, and far too invasive in the index access methods to be the right approach. Our existing method works with minimal help from the index access methods which allows for an enormous degree of freedom in the index design.. To be able to support retail vacuum you would have to force index method implementors to keep information in a way that allowed them to look up a particular value/tid efficiently which would limit the kinds of indexes you could support drastically. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] reply to ...
Marc G. Fournier [EMAIL PROTECTED] writes: 'k, isn't the Reply-To header part of an RFC somewhere? Or is it really an optional thing for an MUA to follow? The relevant RFC would be 2822. If mailers have started ignoring reply-to it would be *because* of lists that set it. In the presence of such lists a mailer what's a mailer supposed to do when you ask it to send a personal response to the author? How can it figure out whether the list has done something wacky with the reply-to header or if it's set as intended to the original author's desired contact point? -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] CREATE TABLE LIKE x INCLUDING CONSTRAINTS
Fixed previous patch by calling change_varattnos_of_a_node() to fix up constraint expressions in case attribute positions don't line up. change_varattnos_of_a_node is in tablecmds.c for inherited tables so this means making it extern. I have a feeling it probably ought to move to some file of functions for manipulating Nodes but I couldn't really find an appropriate place. At first I was going to put it in ruleutils.c but then it seems the other functions in that file go in builtins.h which would be a strange place for this I think. So in the end I left the functions in tablecmds.[ch] at least until someone more familiar with the source tree suggests another pair of files for them to be defined in. like.patch4 Description: Binary data -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] CREATE TABLE LIKE x INCLUDING CONSTRAINTS
Alvaro Herrera [EMAIL PROTECTED] writes: Hum, how are you handling the case where I specify CREATE TABLE LIKE x INCLUDING CONSTRAINTS EXCLUDING CONSTRAINTS ? I have the last one taking priority. I could make it an error but don't see much point in doing so. It seems to be making something an error for no particular gain. EXCLUDING CONSTRAINTS and EXCLUDING DEFAULTS are both kind of stupid since they're the defaults. There's not much need for either except that EXCLUDING DEFAULTS is in the standard and it would be weird not to follow the pattern. And generally I feel like explaining corner cases like this -- when there's no useful application of it -- to just clutter up documentation. The closest analog is command-line options where often script writers want a way to provide flags and then let a variable override those flags. But people rarely do that kind of thing with SQL scripts. I suppose it does happen though. Maybe it would be helpful to know how it will work. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[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] 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] ADD/DROP INHERITS
Tom Lane [EMAIL PROTECTED] writes: If you're happy with the code looking directly at pg_constraint then I see no reason to change it. I just mentioned the relcache because I thought you were concerned about the performance of a pg_constraint search. I'm not concerned with the performance hit of doing a linear scan on pg_constraint or pg_attribute. I am slightly concerned about repeatedly calling SearchSysCacheExistsAttName But using relcache would mean a O(n^2) search across the attributes which might be even worse. I'm unclear how efficient the SysCache lookup function is. If it's a hash table lookup it might be slower but more scalable than an O(n^2) match against the relcache anyways. And I'm slightly concerned with the O(n^2) constraint matching. If someone has 100+ constraints it may be somewhat disappointing to have the operation have a noticeable delay. 1,000 constraints means a million calls to strcmp. Realistically though 1,000 check constraints would be pretty unlikely. 100 constraints might be on the edge of reasonableness and 10,000 calls to strcmp is probably also at the edge of reasonableness too. -- 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
[PATCHES] ADD/DROPS INHERIT (actually INHERIT / NO INHERIT)
I cleaned up the code and added some more documentation.
I think I've addressed all the concerns raised so far. Please tell me if I've
missed anything.
There were a few tangentially related issues that have come up that I think
are TODOs. I'm likely to tackle one or two of these next so I'm interested in
hearing feedback on them as well.
. Constraints currently do not know anything about inheritance. Tom suggested
adding a coninhcount and conislocal like attributes have to track their
inheritance status.
. Foreign key constraints currently do not get copied to new children (and
therefore my code doesn't verify them). I don't think it would be hard to
add them and treat them like CHECK constraints.
. No constraints at all are copied to tables defined with LIKE. That makes it
hard to use LIKE to define new partitions. The standard defines LIKE and
specifically says it does not copy constraints. But the standard already has
an option called INCLUDING DEFAULTS; we could always define a non-standard
extension LIKE table INCLUDING CONSTRAINTS that gives the user the option to
request a copy including constraints.
. Personally, I think the whole attislocal thing is bunk. The decision about
whether to drop a column from children tables or not is something that
should be up to the user and trying to DWIM based on whether there was ever
a local definition or the column was acquired purely through inheritance is
hardly ever going to match up with user expectations.
. And of course there's the whole unique and primary key constraint issue. I
think to get any traction at all on this you have a prerequisite of a real
partitioned table implementation where the system knows what the partition
key is so it can recognize when it's a leading part of an index key.
Index: doc/src/sgml/ddl.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ddl.sgml,v
retrieving revision 1.57
diff -c -p -c -r1.57 ddl.sgml
*** doc/src/sgml/ddl.sgml 30 Apr 2006 21:15:32 - 1.57
--- doc/src/sgml/ddl.sgml 13 Jun 2006 22:39:25 -
*** VALUES ('New York', NULL, NULL, 'NY');
*** 2061,2087
/para
para
!Table inheritance can currently only be defined using the xref
!linkend=sql-createtable endterm=sql-createtable-title
!statement. The related statement commandCREATE TABLE AS/command does
!not allow inheritance to be specified. There
!is no way to add an inheritance link to make an existing table into
!a child table. Similarly, there is no way to remove an inheritance
!link from a child table once it has been defined, other than by dropping
!the table completely. A parent table cannot be dropped
!while any of its children remain. If you wish to remove a table and
!all of its descendants, one easy way is to drop the parent table with
!the literalCASCADE/literal option.
/para
para
xref linkend=sql-altertable endterm=sql-altertable-title will
!propagate any changes in column data definitions and check
!constraints down the inheritance hierarchy. Again, dropping
!columns or constraints on parent tables is only possible when using
!the literalCASCADE/literal option. commandALTER
!TABLE/command follows the same rules for duplicate column merging
!and rejection that apply during commandCREATE TABLE/command.
/para
sect2 id=ddl-inherit-caveats
--- 2061,2108
/para
para
!Table inheritance can be defined using the xref linkend=sql-createtable
!endterm=sql-createtable-title statement using the
!commandINHERITS/command keyword. However the related statement
!commandCREATE TABLE AS/command does not allow inheritance to be
!specified.
! /para
!
! para
!Alternatively a table which is already defined in a compatible way can have
!a new parent added with xref linkend=sql-altertable
!endterm=sql-altertable-title using the commandINHERIT/command
!subform. To do this the new child table must already include columns with
!the same name and type as the columns of the parent. It must also include
!check constraints with the same name and check expression as those of the
!parent. Similarly an inheritance link can be removed from a child using the
!commandALTER TABLE/command using the commandNO INHERIT/command
!subform.
!
! para
!One convenient way to create a compatible table to be a new child is using
!the commandLIKE/command option of commandCREATE TABLE/command. This
!creates a table with the same columns with the same type (however note the
!caveat below regarding constraints). Alternatively a compatible table can
!be created by first creating a new child using commandCREATE
!TABLE/command then removing the inheritance link with commandALTER
!TABLE/command. /para
!
! para
!A parent table cannot be dropped while any
!of
Re: [PATCHES] ADD/DROPS inherits
Simon Riggs [EMAIL PROTECTED] writes: On Mon, 2006-06-12 at 17:39 -0400, Greg Stark wrote: Points I'm uncertain about: . I throw an elog() error if there's a null conbin for a CHECK constraint. Is it possible for a valid CHECK constraint structure to have a null conbin? ruleutils shows: elog(ERROR, null conbin for constraint %u I'm unclear what you mean by this. This doesn't look like what I would expect the error to look like if it was triggered. And the %u makes it appear as if the name of the constraint it %u which is passing strange too. How do I reproduce this? I added some basic (very basic) regression tests Should we fail if columns in the wrong order from the parent? I thought that was one of the restrictions you discovered? I don't think we can complain about wrongly ordered columns. Tom pointed out something as simple as adding more columns to the parent can create a non-standard ordering since it adds them after the local columns. And in any case verifying the order in complicated cases involving multiple parents and locally defined columns would be nigh impossible anyways. Can we test for ALTER TABLE child NO INHERIT parent1 INHERIT parent2 That was on Hannu's wish list. Is INHERIT allowed or disallowed with other ALTER TABLE options? If it is allowed, can we test for something that will fail and something that would pass, e.g. ALTER TABLE DROP column1 INHERITS parent -- where the parent passes on column1. Both those two cases both work, so you just want more regression tests? No problem. Note that operations aren't done in a strictly left-to-right order. For instance ADD/DROP columns are done before INHERIT/NO INHERIT. And ALTER TABLE child NO INHERIT parent 1, INHERIT parent2 will treat attislocal subtly different from the reverse. When I read those tests, it makes me think this should be INHERITS and NOT INHERITS (not great English, but then neither is NO INHERIT). ISTM it might become confusing between INHERITS and INHERIT. Waddyathink? None of these syntaxes are particularly more or less appealing than any other to me. I'm still trying to think of something better. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [PATCHES] ADD/DROP INHERITS
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: So should I set up a nested scan, essentially implementing a nested loop? or should I gather together all the children in a list? I'd use the predigested form of the constraints attached to the Relation tupledescs, cf. RelationBuildTupleDesc, equalTupleDescs. It might be worth refactoring equalTupleDescs so you could share code --- ISTM what you're trying to implement is something like a subsetTupleDesc. Unless I'm missing something that predigested form only has the conbin field. It doesn't have the name of the constraint nor the other fields like deferrable and deferred by default. It also doesn't have foreign key constraints which I'm ignoring now but suggesting that we will want to be copying to children and checking for in new children in the future. And subsetTupleDesc seems to be checking that the attributes are in the same specific order, not that they match by name. That seems like a very different kind of quality/subset nature than needed here. -- greg ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[PATCHES] ADD/DROPS inherits
I couldn't figure out how to make use of the predigested constraints in the relcache, so I continued on the tack I was on. I just stuf the entire HeapTuple in a list and decompiled the constraint source only if I find a constraint with a matching name. Points I'm uncertain about: . I throw an elog() error if there's a null conbin for a CHECK constraint. Is it possible for a valid CHECK constraint structure to have a null conbin? . Memory management. Do I need the heap_copytuple or is that unnecessary? Would it be ok to simply store the actual HeapTuples as the scan proceeds? . Locking -- all of it :) I added some basic (very basic) regression tests and documentation. Did I find the right places? Is that enough or should I add more? Index: doc/src/sgml/ref/alter_table.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/ref/alter_table.sgml,v retrieving revision 1.84 diff -u -p -c -r1.84 alter_table.sgml cvs diff: conflicting specifications of output style *** doc/src/sgml/ref/alter_table.sgml 12 Feb 2006 19:11:00 - 1.84 --- doc/src/sgml/ref/alter_table.sgml 12 Jun 2006 21:30:54 - *** where replaceable class=PARAMETERact *** 46,51 --- 46,53 CLUSTER ON replaceable class=PARAMETERindex_name/replaceable SET WITHOUT CLUSTER SET WITHOUT OIDS + INHERIT replaceable class=PARAMETERparent_table/replaceable + NO INHERIT replaceable class=PARAMETERparent_table/replaceable OWNER TO replaceable class=PARAMETERnew_owner/replaceable SET TABLESPACE replaceable class=PARAMETERnew_tablespace/replaceable /synopsis *** where replaceable class=PARAMETERact *** 250,255 --- 252,303 /varlistentry varlistentry + termliteralINHERIT replaceable class=PARAMETERparent_table/replaceable/literal/term + listitem + para + + This form adds a new parent table to the table. This won't add new + columns to the child table, instead all columns of the parent table must + already exist in the child table. They must have matching data types, + and if they have literalNOT NULL/literal constraints in the parent + then they must also have literalNOT NULL/literal constraints in the + child. + + /para + para + + There must also be matching table constraints for all + literalCHECK/literal table constraints of the parent. Currently + literalUNIQUE/literal, literalPRIMARY KEY/literal, and + literalFOREIGN KEY/literal constraints are ignored however this may + change in the future. + + /para + para + + The easiest way to create a suitable table is to create a table using + literalINHERITS/literal and then remove it via literalNO + INHERIT/literal. Alternatively create a table using + literalLIKE/literal however note that literalLIKE/literal does + not create the necessary constraints. + + /para + + /listitem +/varlistentry + +varlistentry + termliteralNO INHERIT replaceable class=PARAMETERparent_table/replaceable/literal/term + listitem + para +This form removes a parent table from the list of parents of the table. +Queries against the parent table will no longer include records drawn +from the target table. + /para + /listitem +/varlistentry + +varlistentry termliteralOWNER/literal/term listitem para Index: src/backend/commands/tablecmds.c === RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v retrieving revision 1.184 diff -u -p -c -r1.184 tablecmds.c cvs diff: conflicting specifications of output style *** src/backend/commands/tablecmds.c10 May 2006 23:18:39 - 1.184 --- src/backend/commands/tablecmds.c12 Jun 2006 21:30:54 - *** typedef struct NewColumnValue *** 159,166 --- 159,168 static void truncate_check_rel(Relation rel); static List *MergeAttributes(List *schema, List *supers, bool istemp, List **supOids, List **supconstr, int *supOidCount); + static void MergeAttributesIntoExisting(Relation rel, Relation relation); static bool change_varattnos_of_a_node(Node *node, const AttrNumber *newattno); static void StoreCatalogInheritance(Oid relationId, List *supers); + static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, int16 seqNumber, Relation catalogRelation); static intfindAttrByName(const char *attributeName, List *schema); static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass); static bool needs_toast_table(Relation rel); *** static void ATPrepSetTableSpace(AlteredT *** 246,251 --- 248,255 static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace); static void
Re: [PATCHES] ADD/DROP INHERITS
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: I also haven't checked the constraint name. To do so it would make sense to use a small hash table. No, it'd make sense to use strcmp(). It's unlikely that there will be enough constraints attached to any one table to justify use of any but the simplest algorithm. AFAICS you should just iterate through the child constraints looking for matches ... and I'd suggest checking the name first, as that will save a whole lot more work in reverse-compiling than any amount of tenseness in the matching code. So should I set up a nested scan, essentially implementing a nested loop? or should I gather together all the children in a list? My inclination is to avoid the repeated scans and gather them together in a list of cons cells of the two strings. Or can I stuff the whole tuple in the list elements? I'm unclear on the memory management of tuples in the midst of a scan; would I have to copy them? Are the scans less expensive than I imagine and there's no point in storing the results? And are there any other fields of pg_constraint that I should be checking for matches in? Do we care if a parent table has a non-deferrable constraint and the child has a deferrable one, or if the parent's is deferred by default and the child isn't? I'm ignoring unique, primary key, and foreign key constraints on the theory that these things don't really work on inherited tables yet anyways. Yeah, the consistent thing to do with these is nothing, until something is done about the generic problem. It seems to me that foreign key constraints ought to be copied even now though. Also, it seems to me that LIKE ought to copy constraints or at least have an option to. Otherwise it's not really suitable for creating partitions which would be sad since it seems perfect for that task. -- greg ---(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] ADD/DROP INHERITS
Tom Lane [EMAIL PROTECTED] writes: I don't believe those attributes mean anything for check constraints ATM, but you may as well compare them anyway. If we ever do implement them then it'd be reasonable to expect parent and child to have identical settings. I'm not sure. Does it have to have identical behaviour as long as it guarantees the same level of data integrity? Deferred constraints will still guarantee that the promises of the parent table are met. But in that case I guess I really have to store the whole tuple. I'll look into the stuff you suggested I look at to do that. Also, it seems to me that LIKE ought to copy constraints or at least have an option to. What does the spec say about that? It says: NOTE 245 \u2014 column constraints, except for NOT NULL, are not included in CDi; column constraint definitions are effectively transformed to table constraint definitions and are thereby also excluded. We could still do an INCLUDING CONSTRAINTS option or something like that? It seems it would make it much more convenient for creating partitions. Then we could document that CREATE TABLE child (LIKE parent INCLUDING CONSTRAINTS) is guaranteed to create a suitable child table for your parent table. -- greg ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[PATCHES] ADD/DROP INHERITS
This is where I am with the ADD/DROP INHERITS patch now.
1) The syntax is:
ALTER TABLE child INHERIT parent
ALTER TABLE child NO INHERIT parent
no new reserved words, no conflicts, no complicated grammar productions in
gram.y to confuse people in the future.
2) Dependencies are being added and dropped by trawling directly through
pg_depend rather than having dependencies on pg_depend lines.
3) Constraints are being compared by reverse-compiling the definition and
comparing the result with strcmp.
I also haven't checked the constraint name. To do so it would make sense to
use a small hash table. I see something called dynahash in the source tree.
Is it meant for these kind of quick small tasks?
I'm ignoring unique, primary key, and foreign key constraints on the theory
that these things don't really work on inherited tables yet anyways. Also
NONE of these are copied when you create a new inherited table so it would
mean that you wouldn't be able to re-add a freshly automatically generated
child after removing it.
Actually a foreign key constraint *from* a child table work fine. But like
I said it currently isn't being copied when you create a child table. We
could consider fixing that and adding a check here for matching foreign key
constraints at the same time.
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.184
diff -u -p -c -r1.184 tablecmds.c
cvs diff: conflicting specifications of output style
*** src/backend/commands/tablecmds.c10 May 2006 23:18:39 - 1.184
--- src/backend/commands/tablecmds.c9 Jun 2006 21:28:13 -
*** typedef struct NewColumnValue
*** 159,166
--- 159,168
static void truncate_check_rel(Relation rel);
static List *MergeAttributes(List *schema, List *supers, bool istemp,
List **supOids, List **supconstr, int
*supOidCount);
+ static void MergeAttributesIntoExisting(Relation rel, Relation relation);
static bool change_varattnos_of_a_node(Node *node, const AttrNumber
*newattno);
static void StoreCatalogInheritance(Oid relationId, List *supers);
+ static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, int16
seqNumber, Relation catalogRelation);
static intfindAttrByName(const char *attributeName, List *schema);
static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass);
static bool needs_toast_table(Relation rel);
*** static void ATPrepSetTableSpace(AlteredT
*** 246,251
--- 248,255
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
bool enable, bool
skip_system);
+ static void ATExecAddInherits(Relation rel, RangeVar *parent);
+ static void ATExecDropInherits(Relation rel, RangeVar *parent);
static void copy_relation_data(Relation rel, SMgrRelation dst);
static void update_ri_trigger_args(Oid relid,
const char *oldname,
*** static void
*** 1156,1165
StoreCatalogInheritance(Oid relationId, List *supers)
{
Relationrelation;
- TupleDesc desc;
int16 seqNumber;
ListCell *entry;
- HeapTuple tuple;
/*
* sanity checks
--- 1160,1167
*** StoreCatalogInheritance(Oid relationId,
*** 1179,1194
* anymore, there's no need to look for indirect ancestors.)
*/
relation = heap_open(InheritsRelationId, RowExclusiveLock);
- desc = RelationGetDescr(relation);
seqNumber = 1;
foreach(entry, supers)
{
! Oid parentOid = lfirst_oid(entry);
Datum datum[Natts_pg_inherits];
charnullarr[Natts_pg_inherits];
ObjectAddress childobject,
parentobject;
datum[0] = ObjectIdGetDatum(relationId);/*
inhrel */
datum[1] = ObjectIdGetDatum(parentOid); /* inhparent */
--- 1181,1206
* anymore, there's no need to look for indirect ancestors.)
*/
relation = heap_open(InheritsRelationId, RowExclusiveLock);
seqNumber = 1;
foreach(entry, supers)
{
! StoreCatalogInheritance1(relationId, lfirst_oid(entry),
seqNumber, relation);
! seqNumber += 1;
! }
!
! heap_close(relation, RowExclusiveLock);
! }
!
! static void
! StoreCatalogInheritance1(Oid relationId, Oid parentOid, int16 seqNumber,
Relation relation)
! {
Datum datum[Natts_pg_inherits];
char
[PATCHES] ALTER TABLE ADD/DROP INHERITS
As described on -hackers this is my work so far adding ADD/DROP INHERITS. It
implements the controversial ALTER TABLE table ADD/DROP INHERITS parent
syntax that requires making INHERITS a reserved keyword. I haven't seen a
clear consensus yet on what the best syntax to use here would be.
Also, it doesn't handle default column values yet.
Other than that I think it's complete. There are a number of things I'm not
completely certain I'm on the right track with though so it can certainly use
some more eyeballs on it.
Index: src/backend/commands/tablecmds.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/commands/tablecmds.c,v
retrieving revision 1.184
diff -u -p -c -r1.184 tablecmds.c
cvs diff: conflicting specifications of output style
*** src/backend/commands/tablecmds.c10 May 2006 23:18:39 - 1.184
--- src/backend/commands/tablecmds.c7 Jun 2006 18:09:56 -
*** typedef struct NewColumnValue
*** 159,166
--- 159,168
static void truncate_check_rel(Relation rel);
static List *MergeAttributes(List *schema, List *supers, bool istemp,
List **supOids, List **supconstr, int
*supOidCount);
+ static void MergeAttributesIntoExisting(Relation rel, Relation relation);
static bool change_varattnos_of_a_node(Node *node, const AttrNumber
*newattno);
static void StoreCatalogInheritance(Oid relationId, List *supers);
+ static void StoreCatalogInheritance1(Oid relationId, Oid parentOid, int16
seqNumber, Relation catalogRelation);
static intfindAttrByName(const char *attributeName, List *schema);
static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass);
static bool needs_toast_table(Relation rel);
*** static void ATPrepSetTableSpace(AlteredT
*** 246,251
--- 248,255
static void ATExecSetTableSpace(Oid tableOid, Oid newTableSpace);
static void ATExecEnableDisableTrigger(Relation rel, char *trigname,
bool enable, bool
skip_system);
+ static void ATExecAddInherits(Relation rel, RangeVar *parent);
+ static void ATExecDropInherits(Relation rel, RangeVar *parent);
static void copy_relation_data(Relation rel, SMgrRelation dst);
static void update_ri_trigger_args(Oid relid,
const char *oldname,
*** static void
*** 1156,1165
StoreCatalogInheritance(Oid relationId, List *supers)
{
Relationrelation;
- TupleDesc desc;
int16 seqNumber;
ListCell *entry;
- HeapTuple tuple;
/*
* sanity checks
--- 1160,1167
*** StoreCatalogInheritance(Oid relationId,
*** 1179,1194
* anymore, there's no need to look for indirect ancestors.)
*/
relation = heap_open(InheritsRelationId, RowExclusiveLock);
- desc = RelationGetDescr(relation);
seqNumber = 1;
foreach(entry, supers)
{
! Oid parentOid = lfirst_oid(entry);
Datum datum[Natts_pg_inherits];
charnullarr[Natts_pg_inherits];
ObjectAddress childobject,
parentobject;
datum[0] = ObjectIdGetDatum(relationId);/*
inhrel */
datum[1] = ObjectIdGetDatum(parentOid); /* inhparent */
--- 1181,1206
* anymore, there's no need to look for indirect ancestors.)
*/
relation = heap_open(InheritsRelationId, RowExclusiveLock);
seqNumber = 1;
foreach(entry, supers)
{
! StoreCatalogInheritance1(relationId, lfirst_oid(entry),
seqNumber, relation);
! seqNumber += 1;
! }
!
! heap_close(relation, RowExclusiveLock);
! }
!
! static void
! StoreCatalogInheritance1(Oid relationId, Oid parentOid, int16 seqNumber,
Relation relation)
! {
Datum datum[Natts_pg_inherits];
charnullarr[Natts_pg_inherits];
ObjectAddress childobject,
parentobject;
+ HeapTuple tuple;
+ TupleDesc desc = RelationGetDescr(relation);
datum[0] = ObjectIdGetDatum(relationId);/*
inhrel */
datum[1] = ObjectIdGetDatum(parentOid); /* inhparent */
*** StoreCatalogInheritance(Oid relationId,
*** 1222,1234
* Mark the parent as having subclasses.
*/
setRelhassubclassInRelation(parentOid, true);
- seqNumber += 1;
- }
- heap_close(relation, RowExclusiveLock);
}
/*
* Look for an existing schema entry with the given name.
*
--- 1234,1246
* Mark the parent as having
Re: [PATCHES] ALTER TABLE ADD/DROP INHERITS
Tom Lane [EMAIL PROTECTED] writes: Andrew Dunstan [EMAIL PROTECTED] writes: We should try very hard to avoid adding new reserved words, IMNSHO. *Especially* ones not sanctioned by the SQL spec. Reserving a word that is not listed as reserved in the standard is really a spec violation, because it means that spec-conforming SQL commands may fail. I expected that but I haven't seen any agreement on a replacement syntax. The only other one proposed was: ALTER TABLE foo ALTER INHERITS ADD|DROP bar and it received no comments. Should I just assume that's the direction to go if nobody objects? -- 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
[PATCHES] Doc bug
I'm sure nobody was really confused by this little copy/paste oops in the comments but just for the hell of it. cd /r3/usr_local/src/pgsql/pgsql/src/backend/utils/adt/ diff -c /r3/usr_local/src/pgsql/pgsql/src/backend/utils/adt/name.c.\~1.58.\~ /r3/usr_local/src/pgsql/pgsql/src/backend/utils/adt/name.c *** /r3/usr_local/src/pgsql/pgsql/src/backend/utils/adt/name.c.~1.58.~ Fri May 26 15:51:28 2006 --- /r3/usr_local/src/pgsql/pgsql/src/backend/utils/adt/name.c Tue May 30 00:28:36 2006 *** *** 122,129 * *namelt - returns 1 iff a b *namele - returns 1 iff a = b ! *namegt - returns 1 iff a b ! *namege - returns 1 iff a = b * */ Datum --- 122,129 * *namelt - returns 1 iff a b *namele - returns 1 iff a = b ! *namegt - returns 1 iff a b ! *namege - returns 1 iff a = b * */ Datum -- greg ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [PATCHES] [HACKERS] Add switches for DELIMITER and NULL in pg_dump COPY
David Fetter [EMAIL PROTECTED] writes: Not everybody's editor/mailer/whatever does this right, and it makes things fragile. Another way to do this is to change the delimter to a printable character like '|', but that raises hackles, too. Frankly if you're passing you data through an editor/mailer/whatever you don't trust then your setup is already fragile. At least if you're using tabs then you find out about these problems. Tiptoeing around the untrustworthy process just means that it'll fail randomly (and unpredictably) when other characters appear in the data that the software doesn't handle. There are certainly cases where you'll need to do this to interface with other (amateurish) software. But pg_dump isn't for that at all. Even COPY isn't a general purpose data formatter. To interface with other software not using a standard format you're going to have to pass the data through Perl or something like that anyways. -- greg ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PATCHES] [HACKERS] Adding a --quiet option to initdb
Bruce Momjian pgman@candle.pha.pa.us writes: Devrim GUNDUZ wrote: Hi, On Wed, 2006-01-25 at 11:28 -0500, Tom Lane wrote: Devrim GUNDUZ [EMAIL PROTECTED] writes: Attached is a patch which adds --quiet and --q option to initdb. Why is this a good idea? I was playing with 8.2 RPM init script and thought that instead of directing the output to /dev/null, it would be better to use a command line option for that. Also, we are designing a new installer project and --quiet might help us. OK, as long as you understand that the patch should not be applied. It might be valuable from only one person is not enough. I always wondered why the Redhat init scripts thought it was a clever idea to redirect the output to /dev/null. It seems like a pessimal user interface choice. Every time I have to work with a Redhat machine where Postgres isn't starting up the first thing I have to do is edit the init script so I can what the problem is. -- greg ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] External Sort timing debug statements
Tom Lane [EMAIL PROTECTED] writes: Applied with revisions: I made it use the VacRUsage code so that we could see both CPU and elapsed time, and moved the report points around a bit. The output with trace_sort enabled looks like this: NOTICE: begin tuple sort: nkeys = 1, workMem = 1024, randomAccess = t NOTICE: switching to external sort: CPU 0.05s/0.10u sec elapsed 0.15 sec NOTICE: finished writing run 1: CPU 0.14s/0.83u sec elapsed 0.99 sec NOTICE: finished writing run 2: CPU 0.25s/1.67u sec elapsed 1.94 sec NOTICE: finished writing run 3: CPU 0.37s/2.51u sec elapsed 2.90 sec NOTICE: finished writing run 4: CPU 0.48s/3.36u sec elapsed 3.86 sec I'm not actually involved in this so maybe I'm completely off base here. But wouldn't you want to know how many tuples are being sorted and how many data are being written in these runs in order to be able to actually make sense of these timing measurements? Otherwise you would never know whether the results people will send you are really comparable. And even if you're testing things in a well controlled environments you'll never know if you missed something and you're comparing two incomparable results. -- greg ---(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: [HACKERS] [PATCHES] Dbsize backend integration
Bruce Momjian pgman@candle.pha.pa.us writes: I don't think so. I think trait and property suggests an aspect of the object, so saying trait/property size is saying I am talking about an aspect of the object, while for a heap, its size is really its size, it isn't an aspect of its size. I haven't been following this discussion but, uh, does the fact that I have absolutely no clue what pg_trait_size() or pg_property_size() would be measuring count for anything? My best guess here is that it's for measuring the space taken up by a column which doesn't make a lot of sense. I think you need to think about unambiguous words that help the user understand what the function does; words that the user might guess if they were looking for a function to do that, whatever that is. Not words that are sufficiently vague as to include whatever it's actually doing but offer no clue what that is. There are an infinite number of such words to pick and no way for the user to figure out what he or she is looking for. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PATCHES] [PERFORM] 7.4 vs 7.3 ( hash join issue )
Greg Stark [EMAIL PROTECTED] writes:
Dennis Bjorklund [EMAIL PROTECTED] writes:
On 22 Sep 2004, Greg Stark wrote:
Actually this looks like it's arguably a bug to me. Why does the hash
join execute the sequential scan at all? Shouldn't it also like the
merge join recognize that the other hashed relation is empty and skip
the sequential scan entirely?
I'm not sure you can classify that as a bug. It's just that he in one of
the plans started with the empty scan and bacause of that didn't need
the other, but with the hash join it started with the table that had 16
rows and then got to the empty one.
No, postgres didn't do things in reverse order. It hashed the empty table and
then went ahead and checked every record of the non-empty table against the
empty hash table.
Alright, attached is a simple patch that changes this. I don't really know
enough of the overall code to be sure this is safe. But from what I see of the
hash join code it never returns any rows unless there's a match except for
outer joins. So I think it should be safe.
test=# create table a (a integer);
CREATE TABLE
test=# create table b (a integer);
CREATE TABLE
test=# set enable_mergejoin = off;
SET
test=# explain analyze select * from a natural join b;
QUERY PLAN
---
Hash Join (cost=22.50..345.00 rows=5000 width=4) (actual time=0.022..0.022 rows=0
loops=1)
Hash Cond: (outer.a = inner.a)
- Seq Scan on a (cost=0.00..20.00 rows=1000 width=4) (never executed)
- Hash (cost=20.00..20.00 rows=1000 width=4) (actual time=0.005..0.005 rows=0
loops=1)
- Seq Scan on b (cost=0.00..20.00 rows=1000 width=4) (actual
time=0.002..0.002 rows=0 loops=1)
Total runtime: 0.089 ms
(6 rows)
By comparison, note the sequential scan doesn't show never executed on 7.4.3
(sorry, I didn't think to run the query against 8.0 before I compiled the
patched version):
QUERY PLAN
---
Hash Join (cost=22.50..345.00 rows=5000 width=4) (actual time=0.881..0.881 rows=0
loops=1)
Hash Cond: (outer.a = inner.a)
- Seq Scan on a (cost=0.00..20.00 rows=1000 width=4) (actual time=0.001..0.001
rows=0 loops=1)
- Hash (cost=20.00..20.00 rows=1000 width=4) (actual time=0.008..0.008 rows=0
loops=1)
- Seq Scan on b (cost=0.00..20.00 rows=1000 width=4) (actual
time=0.004..0.004 rows=0 loops=1)
Total runtime: 1.105 ms
(6 rows)
Index: backend/executor/nodeHash.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/nodeHash.c,v
retrieving revision 1.86
diff -c -r1.86 nodeHash.c
*** backend/executor/nodeHash.c 29 Aug 2004 04:12:31 - 1.86
--- backend/executor/nodeHash.c 22 Sep 2004 17:51:53 -
***
*** 232,237
--- 232,238
hashtable-buckets = NULL;
hashtable-nbatch = nbatch;
hashtable-curbatch = 0;
+ hashtable-ntup = 0;
hashtable-innerBatchFile = NULL;
hashtable-outerBatchFile = NULL;
hashtable-innerBatchSize = NULL;
***
*** 493,498
--- 494,501
heapTuple-t_len);
hashTuple-next = hashtable-buckets[bucketno];
hashtable-buckets[bucketno] = hashTuple;
+
+ hashtable-ntup ++;
}
else
{
Index: backend/executor/nodeHashjoin.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/nodeHashjoin.c,v
retrieving revision 1.64
diff -c -r1.64 nodeHashjoin.c
*** backend/executor/nodeHashjoin.c 29 Aug 2004 05:06:42 - 1.64
--- backend/executor/nodeHashjoin.c 22 Sep 2004 17:51:54 -
***
*** 127,132
--- 127,140
hashNode-hashtable = hashtable;
(void) ExecProcNode((PlanState *) hashNode);
+ /* An empty hash table can't return any matches */
+ if (hashtable-nbatch == 0
+ hashtable-ntup == 0
+ node-js.jointype != JOIN_LEFT)
+ {
+ return NULL;
+ }
+
/*
* Open temp files for outer batches, if needed. Note that file
* buffers are palloc'd in regular executor context.
Index: include/executor/hashjoin.h
===
RCS file: /projects/cvsroot/pgsql-server/src/include/executor/hashjoin.h,v
retrieving revision 1.32
diff -c -r1.32 hashjoin.h
*** include/executor
Re: [PATCHES] [PERFORM] 7.4 vs 7.3 ( hash join issue )
Tom Lane [EMAIL PROTECTED] writes:
Yeah, I was just looking at doing that.
Well I imagine it takes you as long to read my patch as it would for you to
write it. But anyways it's still useful to me as exercises.
It would also be interesting to prefetch one row from the outer table and fall
out immediately (without building the hash table) if the outer table is
empty. This seems to require some contortion of the code though :-(
Why is it any more complicated than just moving the hash build down lower?
There's one small special case needed in ExecHashJoinOuterGetTuple but it's
pretty non-intrusive.
It seems to work for me but I can't test multiple batches easily. I think I've
convinced myself that they would work fine but...
test=# explain analyze select * from a natural join b;
QUERY PLAN
-
Hash Join (cost=22.50..345.00 rows=5000 width=4) (actual time=0.005..0.005 rows=0
loops=1)
Hash Cond: (outer.a = inner.a)
- Seq Scan on a (cost=0.00..20.00 rows=1000 width=4) (actual time=0.002..0.002
rows=0 loops=1)
- Hash (cost=20.00..20.00 rows=1000 width=4) (never executed)
- Seq Scan on b (cost=0.00..20.00 rows=1000 width=4) (never executed)
Total runtime: 0.070 ms
(6 rows)
Index: backend/executor/nodeHash.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/nodeHash.c,v
retrieving revision 1.86
diff -c -r1.86 nodeHash.c
*** backend/executor/nodeHash.c 29 Aug 2004 04:12:31 - 1.86
--- backend/executor/nodeHash.c 22 Sep 2004 18:37:40 -
***
*** 232,237
--- 232,238
hashtable-buckets = NULL;
hashtable-nbatch = nbatch;
hashtable-curbatch = 0;
+ hashtable-ntup = 0;
hashtable-innerBatchFile = NULL;
hashtable-outerBatchFile = NULL;
hashtable-innerBatchSize = NULL;
***
*** 493,498
--- 494,501
heapTuple-t_len);
hashTuple-next = hashtable-buckets[bucketno];
hashtable-buckets[bucketno] = hashTuple;
+
+ hashtable-ntup ++;
}
else
{
Index: backend/executor/nodeHashjoin.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/executor/nodeHashjoin.c,v
retrieving revision 1.64
diff -c -r1.64 nodeHashjoin.c
*** backend/executor/nodeHashjoin.c 29 Aug 2004 05:06:42 - 1.64
--- backend/executor/nodeHashjoin.c 22 Sep 2004 18:37:41 -
***
*** 109,143
ResetExprContext(econtext);
/*
-* if this is the first call, build the hash table for inner relation
-*/
- if (!node-hj_hashdone)
- {
- /*
-* create the hash table
-*/
- Assert(hashtable == NULL);
- hashtable = ExecHashTableCreate((Hash *) hashNode-ps.plan,
-
node-hj_HashOperators);
- node-hj_HashTable = hashtable;
-
- /*
-* execute the Hash node, to build the hash table
-*/
- hashNode-hashtable = hashtable;
- (void) ExecProcNode((PlanState *) hashNode);
-
- /*
-* Open temp files for outer batches, if needed. Note that file
-* buffers are palloc'd in regular executor context.
-*/
- for (i = 0; i hashtable-nbatch; i++)
- hashtable-outerBatchFile[i] = BufFileCreateTemp(false);
-
- node-hj_hashdone = true;
- }
-
- /*
* Now get an outer tuple and probe into the hash table for matches
*/
outerTupleSlot = node-js.ps.ps_OuterTupleSlot;
--- 109,114
***
*** 163,171
--- 134,180
node-hj_MatchedOuter = false;
/*
+* if this is the first call, build the hash table for inner
relation
+*/
+ if (!node-hj_hashdone)
+ {
+ /*
+* create the hash table
+*/
+ Assert(hashtable == NULL);
+ hashtable = ExecHashTableCreate((Hash *)
hashNode-ps.plan,
+
node-hj_HashOperators);
+ node-hj_HashTable = hashtable;
+
+ /*
+* execute the Hash node, to build the hash table
+
Re: [PATCHES] [SQL] COUNT(*) to find records which have a certain number of dependencies ?
T E Schmitz [EMAIL PROTECTED] writes:
There's a German saying Go and find a parking-meter, i.e. suggesting to pop a
coin in the parking-meter and talk to it as nobody else wants to listen. ;-)
Yes well I anticipated such a response. So I tried my hand at it myself.
Well I finally found a problem tractable enough for me to get all the way from
start to end in a single sitting. Here's a simple solution to my complaint.
This patch allows subqueries without aliases. This is SQL-non-spec-compliant
syntax that Oracle supports and many users expect to work. It's also just
damned convenient, especially for simple ad-hoc queries.
There was a comment saying an alias name would have to be constructed so I
took that approach. It seems like it would have been cleaner to just ensure
that the code doesn't fail when no alias is present. But I have no idea how
much work would be involved in that, so I just took advice from the anonymous
author of the comment.
Incidentally, It seems weird to me that the counter doesn't reset for every
query. Perhaps I should change that?
Index: src/backend/parser/gram.y
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/gram.y,v
retrieving revision 2.475
diff -u -p -c -r2.475 gram.y
cvs diff: conflicting specifications of output style
*** src/backend/parser/gram.y 29 Aug 2004 04:12:35 - 2.475
--- src/backend/parser/gram.y 20 Sep 2004 21:34:13 -
*** table_ref: relation_expr
*** 5158,5177
{
/*
* The SQL spec does not permit a subselect
!* (derived_table) without an alias clause,
!* so we don't either. This avoids the problem
!* of needing to invent a unique refname for
it.
!* That could be surmounted if there's
sufficient
!* popular demand, but for now let's just
implement
!* the spec and see if anyone complains.
!* However, it does seem like a good idea to
emit
!* an error message that's better than syntax
error.
*/
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
!errmsg(subquery in FROM must
have an alias),
!errhint(For example, FROM
(SELECT ...) [AS] foo.)));
! $$ = NULL;
}
| select_with_parens alias_clause
{
--- 5158,5172
{
/*
* The SQL spec does not permit a subselect
!* (derived_table) without an alias clause,
We surmount
!* this because of popular demand by gining up
a fake name
!* in transformRangeSubselect
*/
!
! RangeSubselect *n = makeNode(RangeSubselect);
! n-subquery = $1;
! n-alias = NULL;
! $$ = (Node *) n;
}
| select_with_parens alias_clause
{
Index: src/backend/parser/parse_clause.c
===
RCS file: /projects/cvsroot/pgsql-server/src/backend/parser/parse_clause.c,v
retrieving revision 1.136
diff -u -p -c -r1.136 parse_clause.c
cvs diff: conflicting specifications of output style
*** src/backend/parser/parse_clause.c 29 Aug 2004 05:06:44 - 1.136
--- src/backend/parser/parse_clause.c 20 Sep 2004 21:34:14 -
*** transformRangeSubselect(ParseState *psta
*** 418,426
* an unlabeled subselect.
*/
if (r-alias == NULL)
! ereport(ERROR,
! (errcode(ERRCODE_SYNTAX_ERROR),
!errmsg(subquery in FROM must have an alias)));
/*
* Analyze and transform the subquery.
--- 418,434
* an unlabeled subselect.
*/
if (r-alias == NULL)
! {
! static int subquery_counter = 1;
! static char buf[30];
!
! sprintf(buf, *SUBQUERY*%d*, subquery_counter++);
!
! r-alias
Re: [PATCHES] Autoconf test for incompatible version of flex
Tom Lane [EMAIL PROTECTED] writes: Greg Stark [EMAIL PROTECTED] writes: This patch adds an autoconf test to check for the new incompatible version of flex. It seems unlikely that we should cause configure to reject all future versions of flex... .31 may be broken but I think we should assume that they'l fix it. I inquired about this problem elsewhere, it's not a bug, it's an API change. Until postgres ports to the new API it won't work with newer versions of Flex. From the Debian maintainer of the flex package (http://bugs.debian.org/199776): The signature of all functions has changed. flex has new command line options, and option parsing has changed (now also supports POSIX conventions optionally). Handles POSIXLY_CORRECT environment variable. Various i18n translations are included in the distribution. flex now works with recent bison versions This is not a single short lived change. I understand that this requires all packages using lex to massage their lexers to conform to the new behaviour of flex; but the gains in reduced complexity of the scanner and reentrancy and standards compliance are well worth it. -- greg ---(end of broadcast)--- TIP 8: explain analyze is your friend
