Re: [HACKERS] 7.4 Wishlist
On 30 Nov 2002, Neil Conway wrote: On Sat, 2002-11-30 at 12:47, Stephan Szabo wrote: check constraints with subselects. Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. I can't see any justification for doing it as insert/update on main table only since by my reading of the spec the constraint is logically checked at the end of each statement (or transaction) even if we would normally not do so in practice when we know the constraint shouldn't be violated. Of course this was in the general set of, if I had months and months and nothing else to do (like work) then I'd want to look at it because I think it'd be useful. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] tsearch thoughts
On Sat, 30 Nov 2002, Christopher Kings-Lynne wrote: Is there any reason why the tseach indexes couldn't be modified to just work on TEXT fields and not TXTIDX fields. Is there really a reason to have the TXTIDX type? I mean, when the index is created over the text column, instead of just indexing the text as-is, index the txt2txtidx'd version...? That would vastly reduce the complexity of tsearch, and would make the indexed text invisible, as it is in most other fti implementations...? Chris, This is sort of we had thought about full text searching in postgres and what should happens with maturity of tsearch. We began from contrib/module just to get some experience and still need to do some research on underlying algorithms. Also, remember current GiST is not concurrent and we plan to work on this issue. We're very busy and need somebody to help us with interface (dictionaries, parser, postgresql internal interface). I tried to simulate this myself, although ideally it would be invisible to the user: test=# create table test (a text); CREATE test=# CREATE INDEX my_idx ON test USING gist(txt2txtidx(a)); ERROR: DefineIndex: index function must be marked iscachable So the index isn't iscachable - why's that? I don't remember the reason, but you may try to define it as 'iscachable' in tsearch.sql. Say it was marked iscachable, then I'd be able to query like this: SELECT * FROM test WHERE txt2txtidx(test) ## 'apple'; This would mean that the index on-disk file would be large, but the table file would stay small. It would also vastly reduce the size of pg_dumps... Could we move towards something like: CREATE FULLTEXT INDEX my_idx ON test (a); Or something? Chris ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html Regards, Oleg _ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
Matthew T. O'Connor [EMAIL PROTECTED] writes: pg_dump, our upgrade process is painful enough having to do a dump, reload. I think we should be able to guarantee (or at least let much closer to it) that the process works in all cases. I would already be happy if pg_dump backed up my databases correctly, so that I dont have to reorder SQL statements manually in the dump before psql can execute it. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://CERT.Uni-Stuttgart.DE/people/fw/ RUS-CERT fax +49-711-685-5898 ---(end of broadcast)--- TIP 3: 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] 7.4 To Do
On Sunday 01 December 2002 05:03, Tom Lane wrote: Paul Ramsey [EMAIL PROTECTED] writes: Oracle has finally surpassed PostgreSQL in some elements of object-relational technology. Among the things you can do are: - Address components of objects using dot-notation. (select employee.salary from employees) Cool. How do they resolve the conflict against schema notation (ie, is employee a table reference or a schema name here)? There are simple name resolution rules regards Haris Peco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 7.4 Wishlist
Stephan Szabo [EMAIL PROTECTED] writes: On 30 Nov 2002, Neil Conway wrote: Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. Note that you can get the stupid semantics (run the subselect only when the constrained table changes) today: just hide the subselect in a user-defined function that's called from the constraint expression. Or put the whole check in a trigger instead of using a constraint. I don't think we should bother with direct support of subselects in constraints unless we can come up with an implementation that is significantly better than what you can accomplish with these workarounds. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Segmentation fault while COPY in 7.3
Nicolai Tufar [EMAIL PROTECTED] writes:
From: Tom Lane [EMAIL PROTECTED]
Ohhh ...
Nicolai, are you running with a client encoding different from server
encoding?
Got it!
Okay, the problem is a double free of memory in COPY OUT. I've applied
the following patch for 7.3.1. (A better fix would be for COPY OUT to
run in a local memory context that it could reset every so often, like
once per row, and then we could remove these risky retail pfree's entirely.
I plan to do that instead in development sources.)
regards, tom lane
*** src/backend/commands/copy.c.origFri Oct 18 20:25:36 2002
--- src/backend/commands/copy.c Sun Dec 1 12:28:24 2002
***
*** 1470,1478
char *string;
charc;
chardelimc = delim[0];
-
boolsame_encoding;
! char *string_start;
int mblen;
int i;
--- 1470,1477
char *string;
charc;
chardelimc = delim[0];
boolsame_encoding;
! char *string_start = NULL;
int mblen;
int i;
***
*** 1481,1492
{
string = (char *) pg_server_to_client((unsigned char *) server_string,
strlen(server_string));
! string_start = string;
}
else
{
string = server_string;
- string_start = NULL;
}
for (; (c = *string) != '\0'; string += mblen)
--- 1480,1491
{
string = (char *) pg_server_to_client((unsigned char *) server_string,
strlen(server_string));
! if (string != server_string)
! string_start = string;
}
else
{
string = server_string;
}
for (; (c = *string) != '\0'; string += mblen)
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] 7.4 Wishlist
On Sun, 1 Dec 2002, Tom Lane wrote: Stephan Szabo [EMAIL PROTECTED] writes: On 30 Nov 2002, Neil Conway wrote: Have we decided how this would even work? Last I heard, Tom still had some major reservations about the practicality of implementing these -- for example, would you re-evaluate all constraints that SELECT from a table when the table changes? You'd have to either do it in all cases or come up with something that was smart enough to limit the cases to some extent based on the expression. I doubt that it'd perform terribly well, especially at first. Note that you can get the stupid semantics (run the subselect only when the constrained table changes) today: just hide the subselect in a user-defined function that's called from the constraint expression. Or put the whole check in a trigger instead of using a constraint. I don't think we should bother with direct support of subselects in constraints unless we can come up with an implementation that is significantly better than what you can accomplish with these workarounds. Well, the problem is that user defined triggers trying to do the real semantics for update/insert on the other tables of the constraint seem to me like they'll have the same issues as foreign keys do currently, either you'll be forced to write something too strong and deadlock alot, or you'll write something too weak and end up with constraint violations with concurrent transactions unless you basically write a very low level C function to do it for you. I guess this, since in general, the non-action foreign keys really are just check constraints with a subselect effectively. ---(end of broadcast)--- TIP 3: 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] ExecMakeTableFunctionResult vs. pre-evaluated functions
Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: A grotty answer is to not apply constant-expression folding to table function RTE entries. A better answer would be to make ExecMakeTableFunctionResult more flexible, but I'm not quite sure what it should do if presented a non-function-call expression tree. Any thoughts? If presented with a non-function-call expression tree, can we always evaluate it to produce a scalar constant (if it isn't already)? If so, why not do that, create a one row, one column tuplestore, and exit? It's really no different than a function call that does the same, is it? Yeah, that's probably a reasonable approach to take. It would fail if we had an expression that returned a non-scalar value (viz. a set), but the constant-folder won't try to fold or inline anything that returns a set, so you shouldn't see any problem in practice. We do need to do something about this, since even without the inlining code there's a problem: the only reason the regression example works in 7.3 is that the constant-simplifier doesn't fire. Which it would, if the function were marked as immutable, as would be reasonable to do. regression=# select version(); version - PostgreSQL 7.3 on hppa-hp-hpux10.20, compiled by GCC 2.95.3 (1 row) regression=# CREATE FUNCTION getfoo(int) RETURNS int AS 'SELECT $1;' regression-# LANGUAGE SQL immutable; CREATE FUNCTION regression=# SELECT * FROM getfoo(1) AS t1; ERROR: ExecMakeTableFunctionResult: expression is not a function call regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
I was thinking more along the lines of ALTER TABLE a.b RENAME TO x.y I don't see anything in the SQL spec about this; anyone know what precedent is in Oracle or other DBMSes? Good question. I can't find anything in the Oracle docs indicating it is even possible. We should probably just go with your suggestion. Anything else beyond the relnamespace and pg_depend entries that need to be dealt with? What about sequences for serial columns? What about views or types that depend on the table? Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
Tom Lane wrote: If the parser treated PUBLIC as an actual keyword, you'd not be having this problem, because keywords are case-folded on an ASCII-only basis (which is consistent with the SQL99 spec, amazingly enough). We put in the above hack after someone complained that PUBLIC didn't use to be a reserved word ... but considering that SQL92 clearly lists it as a reserved word, there's not a lot of ground for that complaint to stand on. I'd prefer shifting PUBLIC back to the true-keyword category over any of the other workarounds you've suggested ... PUBLIC doesn't seem like a very common column name --- seems safe to make it reserved. We made 'value' reserved in 7.3, and that was a much more common one. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: 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] ExecMakeTableFunctionResult vs. pre-evaluated functions
Joe Conway [EMAIL PROTECTED] writes: If presented with a non-function-call expression tree, can we always evaluate it to produce a scalar constant (if it isn't already)? If so, why not do that, create a one row, one column tuplestore, and exit? It's really no different than a function call that does the same, is it? Yeah, that's probably a reasonable approach to take. It would fail if we had an expression that returned a non-scalar value (viz. a set), but the constant-folder won't try to fold or inline anything that returns a set, so you shouldn't see any problem in practice. Actually, it turns out to be easy to make ExecMakeTableFunctionResult cope with expressions returning sets as well. It's the same as the ValuePerCall protocol we defined for table functions (no surprise, since that protocol was deliberately modeled on the existing implementation of set-returning expressions). So it's done. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Why not add PostGIS to the core?
Why are the features provided by PostGIS not added to the core of PostgreSQL? Hans ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: PUBLIC doesn't seem like a very common column name --- seems safe to make it reserved. We made 'value' reserved in 7.3, and that was a much more common one. I'm still quite unhappy about 'value', and would like to look into making it unreserved again. This business does show that there are some pitfalls in that, though :-( regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
Bruce Momjian [EMAIL PROTECTED] writes: We made 'value' reserved in 7.3, and that was a much more common one. BTW, you mean current not 7.3. That patch has still got some serious problems anyway: 7.3: regression=# select value; ERROR: Attribute value not found HEAD: regression=# select value; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: We made 'value' reserved in 7.3, and that was a much more common one. BTW, you mean current not 7.3. That patch has still got some serious problems anyway: Yes, I realized later it was current. I was fixing the dbdpg regression tests, and git bitten by that, and forgot I was using current and not 7.3. 7.3: regression=# select value; ERROR: Attribute value not found HEAD: regression=# select value; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Yow! -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] ALTER TABLE schema SCHEMA TO new_schema?
Christopher Kings-Lynne wrote: possible. We should probably just go with your suggestion. Anything else beyond the relnamespace and pg_depend entries that need to be dealt with? What about sequences for serial columns? What about views or types that depend on the table? Yeah, good point. I think properly dealing with the pg_depends issues will catch anything of that nature, but what to do with them? Probably should move dependent type, constraint, index entries to the same new namespace. We might want to move related sequences, but I'm not sure we'd want to do that silently, since the sequence could be in use for other tables as well. And we should probably restrict the change if there are dependent functions or views. Does this capture the issues? Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
On Sunday, December 1, 2002, at 10:49 AM, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: PUBLIC doesn't seem like a very common column name --- seems safe to make it reserved. We made 'value' reserved in 7.3, and that was a much more common one. I'm still quite unhappy about 'value', and would like to look into making it unreserved again. This business does show that there are some pitfalls in that, though :-( Actually, I don't think it's reserved in 7.3, only in the 7.4 development sources. Otherwise, Bricolage would fail hard, and it doesn't. So there's some time to play with this issue, I think. David -- David Wheeler AIM: dwTheory [EMAIL PROTECTED] ICQ: 15726394 http://david.wheeler.net/ Yahoo!: dew7e Jabber: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
regression=# select value; ERROR: Attribute value not found HEAD: regression=# select value; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Yow! I believe these are fixed in the patch I sent in last week. -- Rod Taylor [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Hard-coded PUBLIC in pg_dump
regression=# select value; ERROR: Attribute value not found HEAD: regression=# select value; server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. Yow! I believe these are fixed in the patch I sent in last week. -- Rod Taylor [EMAIL PROTECTED] signature.asc Description: This is a digitally signed message part
Re: [HACKERS] tsearch thoughts
This is sort of we had thought about full text searching in postgres and what should happens with maturity of tsearch. We began from contrib/module just to get some experience and still need to do some research on underlying algorithms. Also, remember current GiST is not concurrent and we plan to work on this issue. We're very busy and need somebody to help us with interface (dictionaries, parser, postgresql internal interface). Hi Oleg, I'm busy too :) Is there for instance a specific thing that need work? Chris ---(end of broadcast)--- TIP 3: 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] Why not add PostGIS to the core?
Hans-Jürgen Schönig wrote: Why are the features provided by PostGIS not added to the core of PostgreSQL? Because they are GPL licensed. Some also thought it would be better as a separate project that could release independently. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Read-only plan trees
Han Holl's recent complaint about memory leaks in SQL-language functions has started me thinking again about making plan trees read-only to the executor. This would make it a lot easier to manage memory cleanly in the SQL function executor, and would eliminate a lot of plan tree copying that currently goes on in plpgsql, prepared queries, etc. Basically, instead of having plan tree nodes point to associated executor state nodes, we should turn that around: executor state should point to plan nodes. Executor startup should build a state-node tree that exactly parallels the plan tree, and *all* data that is changed by the executor should live in that tree. We can build this tree in a memory context that is made to have query lifetime. At executor shutdown, rather than individually pfree'ing lots of stuff (and having memory leaks wherever we forget), we can just delete the query memory context. This is a nontrivial task, and so I plan to tackle it in several stages. Step 1: restructure plannodes.h and execnodes.h so that there is an executor state tree with entries for each plan node. This tree will be built recursively during ExecInitNode() --- you pass it a plan tree, and it returns a state tree that links to the plan tree nodes. ExecutorRun then needs only a pointer to the state tree. Step 2: similarly restructure trees for expressions (quals and targetlists). Currently we do not explicitly build a state tree for expressions --- the objects that ought to be in this tree are the fcache entries that are attached to OP_EXPR and FUNC_EXPR nodes in an expression plan tree. The fcache objects really need to be in the executor's context however, and the cleanest way to make that happen seems to be to build a state tree paralleling the expression plan tree. But this is slightly inefficient, since there would be many nodes in the expression state trees that aren't doing anything very useful, ie, all the ones that correspond to nodes other than OP and FUNC in the plan tree. An alternative approach would be to make it work somewhat like Params do now: in each OP and FUNC node, put an integer index field to replace the current fcache pointer. The planner would be responsible for assigning sequential index values to every OP and FUNC in a plan, and storing the total number of 'em in the plan's top node. Then at runtime, the executor would allocate an array of that many fcache structs which it'd store in the EState for the plan. Execution of an individual op or func would index into the EState to find the fcache. Either of these approaches would mean that we couldn't easily just execute a scalar expression tree, which is something that we do in quite a few places (constraint checking for instance). There would need to be some advance setup done. With the Param-style approach, the advance setup would not be read-only on the expression plan tree ... which seems like a bad idea, so I'm leaning towards building the more expensive data structure. Step 3: only after all the above spadework is done could we actually set up a query-lifetime memory context and build the executor's state in it. Comments? regards, tom lane ---(end of broadcast)--- TIP 3: 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] Read-only plan trees
Tom Lane wrote: Either of these approaches would mean that we couldn't easily just execute a scalar expression tree, which is something that we do in quite a few places (constraint checking for instance). There would need to be some advance setup done. With the Param-style approach, the advance setup would not be read-only on the expression plan tree ... which seems like a bad idea, so I'm leaning towards building the more expensive data structure. Even though the former is a bit more expensive, it sounds like it is still a net win due to reduced/eliminated need for making plan tree copies, right? It sounds like it is also simpler and easier to maintain. Step 3: only after all the above spadework is done could we actually set up a query-lifetime memory context and build the executor's state in it. Comments? Sounds like a great plan. Let me know if there's anything I can do to help. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Does anyone know what embedded transactions are?
Justin Clift wrote: Hi guys, Was just looking at the project page for Compiere, an Open Source ERP+CRM solution that is usually in the top 10 most popular SourceForge projects. They were attempting to port Compiere from Oracle to PostgreSQL, but have stopped (apparently) because PostgreSQL doesn't support embedded transations. http://www.compiere.org/technology/independence.html Does anyone know what they're talking about? I assume it is: BEGIN; ... BEGIN; ... COMMIT; COMMIT; That thing I am trying to do for 7.4. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Does anyone know what embedded transactions are?
Reading through their web page, they seem to have decided to try and fund moving their transactional issues into the Java container instead of getting nested transactions into PostgreSQL. It sounds retrograde and risky, but I suppose if they carry it off, they will attain true database independance. Joe Conway wrote: Has anyone from Compiere ever contacted this list to discuss their issues? It is an unbelievable shame that the most active open source ERP can't use an open source database. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Read-only plan trees
Joe Conway [EMAIL PROTECTED] writes: Tom Lane wrote: Either of these approaches would mean that we couldn't easily just execute a scalar expression tree, which is something that we do in quite a few places (constraint checking for instance). There would need to be some advance setup done. With the Param-style approach, the advance setup would not be read-only on the expression plan tree ... which seems like a bad idea, so I'm leaning towards building the more expensive data structure. Even though the former is a bit more expensive, it sounds like it is still a net win due to reduced/eliminated need for making plan tree copies, right? I think it will be a net win compared to our current code, because we can save copying whole plan trees in a number of places. But I was wondering if further improvement is possible. Another reason not to go with the fcache-array approach is that it does not help with storing executor-state data for anything except op/func nodes. I am not sure offhand whether we need any for other expression node types, but it's sure a reasonably likely future possibility. Sounds like a great plan. Let me know if there's anything I can do to help. Right at the moment I'm struggling a bit with terminology. We've got basically four categories of node types to deal with in this scheme: Plan steps Expressions (Scan, Sort, etc) (Var, Op, Func, etc) Planner output Plan Expr? Executor state CommonState ??? The existing Plan-category nodes are all derived from nodetype Plan, so that seems reasonably well set. The existing executor state nodes for Plan nodes are all derived from CommonState, but that seems like a name that conveys hardly anything. The existing expression-category nodes do *not* have any common substructure, and don't seem to need any. I'm not thrilled about using Expr as a generic term for them, but am not sure what else to write. (I'm also finding it confusing whether plan node means any node in a tree output by the planner --- which would then include expression nodes --- or just nodes that correspond to major steps in the query pipeline --- which is the present usage.) And what about a generic term for execution state nodes for expression nodes? Any ideas about naming are welcome. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] GiST a second class citizen?
The PostGIS project has been making use of GiST for about a year now and (thanks to the excellent work of Oleg and Teodor) have found it to be a most excellent indexing system. We are about to apply for some government RD funding, and one of our potential subprojects is creating GiST bindings for all the standard SQL PostgreSQL types. Why? Well, because our spatial indexes are GiST, providing bindings for the standard types allows us to do multi-key indexes which combine spatial and non-spatial data. That would be pretty unique in the DMBS world as it stands right now. So far, GiST is integrated into the main tree, but all the bindings seem to be kept outside, in contrib (ltree, btree, rtree). If there were a complete set of GiST b-tree bindings available for the builtin types, where would/should they reside? Would I be completely out-to-lunch if I suggested that the GiST bindings might even replace the standard ones? The ability to multikey indexes of wierd-and-crazy-types with normal-boring-types seems like a halmark of a Real Live Object-Relational DBMS. Thoughts? Paul ---(end of broadcast)--- TIP 3: 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] GiST a second class citizen?
Paul Ramsey [EMAIL PROTECTED] writes: So far, GiST is integrated into the main tree, but all the bindings seem to be kept outside, in contrib (ltree, btree, rtree). This is a historical artifact. As GiST comes more up-to-speed, the operator classes for it should become mainstream. Would I be completely out-to-lunch if I suggested that the GiST bindings might even replace the standard ones? Yes. There is no replacement involved here, because opclasses for different index types are quite independent. (Perhaps what you are really suggesting is that GiST should become the default index type instead of btree --- to which I can only reply that it's got a *long* way to go before that would be considered for an instant...) regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Read-only plan trees
Tom Lane wrote: Right at the moment I'm struggling a bit with terminology. We've got basically four categories of node types to deal with in this scheme: Plan steps Expressions (Scan, Sort, etc) (Var, Op, Func, etc) Planner output Plan Expr? Executor state CommonState ??? The existing Plan-category nodes are all derived from nodetype Plan, so that seems reasonably well set. The existing executor state nodes for Plan nodes are all derived from CommonState, but that seems like a name that conveys hardly anything. The existing expression-category nodes do *not* have any common substructure, and don't seem to need any. I'm not thrilled about using Expr as a generic term for them, but am not sure what else to write. (I'm also finding it confusing whether plan node means any node in a tree output by the planner --- which would then include expression nodes --- or just nodes that correspond to major steps in the query pipeline --- which is the present usage.) And what about a generic term for execution state nodes for expression nodes? Any ideas about naming are welcome. Maybe: Plan steps Expressions - Planner output Plan Expr Executor state PlanState ExprState I think Plan node should only refer to nodes literally derived from nodetype Plan. Similarly with PlanState nodes. Joe ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Read-only plan trees
Joe Conway [EMAIL PROTECTED] writes: Any ideas about naming are welcome. Maybe: Plan steps Expressions - Planner outputPlan Expr Executor statePlanState ExprState I think Plan node should only refer to nodes literally derived from nodetype Plan. Similarly with PlanState nodes. That part works for me. The other part isn't quite right since most expression-class nodes don't inherit from Expr, and their state nodes certainly don't need an fcache. But come to think of it, we don't need an fcache for AND/OR/NOT nodes, and SUBPLAN has different needs altogether. I wonder if it's time to split the Expr node class into three or so classes: op/func, boolean, and subplan. If we did that, we could use the Expr struct name for the superclass of all expression-type nodes (since it'd contain only NodeTag, it'd be a purely decorative superclass) and then ExprState works as the name of the associated superclass of expression-state nodes (only slightly less decorative, it'd contain NodeTag and the Expr * link to the associated expression node). The existing FunctionCache struct would then become part of the ExprState subclass that's associated with the op/func Expr subclass. This seems like it works... regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] toast table growing indefinitely? Known problems?
Is there a known problem with TOAST tables growing indefinitely in 7.2? We have a database that has large text chunks inserted deleted regularly, and despite regular vacuums, the toast table continues to grow. We can not do a VACUUM FULL since it's a 24x7 system, but we do VACUUM frequently. Based on other threads we have tried using REINDEX on the base table, but we have confirmed that it is not the toast index table that is the main consumer, and it does not seem to help. On a separate issue, we have seen toast indexes growing indefinitely on 7.3 despite VACUUM FULL. Attempting a reindex results in: # reindex table tt; WARNING: table tt wasn't reindexed REINDEX Any help and/or pointers would be appreciated. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] 7.4 Wishlist
David Wheeler wrote: My understanding is that the nul character is legal in a byte sequence, but if it's not properly escaped, it'll be parsed as the end of the statement. Unfortunately, I think that it's a very tough problem to solve. No question wrt '\0' bytes -- they would have to be escaped when casting from bytea to text. The harder issue is that there are apparently many other multiple byte sequences that, while valid in an ASCII encoding, are not valid in one or more multibyte encodings. See this thread: http://archives.postgresql.org/pgsql-hackers/2002-04/msg00236.php This is why currently all non printable characters are escaped (which I think is all bytes 127). Text on the other hand is already known to be valid for a particular encoding, so it doesn't need escaping. I'm not sure what happens when the backend encoding and client encoding don't match -- I'd guess there is some probability of invalid byte sequences in that case too. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Does anyone know what embedded transactions are?
Bruce Momjian wrote: Justin Clift wrote: Hi guys, Was just looking at the project page for Compiere, an Open Source ERP+CRM solution that is usually in the top 10 most popular SourceForge projects. They were attempting to port Compiere from Oracle to PostgreSQL, but have stopped (apparently) because PostgreSQL doesn't support embedded transations. Has anyone from Compiere ever contacted this list to discuss their issues? It is an unbelievable shame that the most active open source ERP can't use an open source database. Joe ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Does anyone know what embedded transactions are?
Joe Conway wrote: snip Has anyone from Compiere ever contacted this list to discuss their issues? It is an unbelievable shame that the most active open source ERP can't use an open source database. I think so, but not with zeal. :-/ Regards and best wishes, Justin Clift Joe -- My grandfather once told me that there are two kinds of people: those who work and those who take the credit. He told me to try to be in the first group; there was less competition there. - Indira Gandhi ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] toast table growing indefinitely? Known problems?
Philip Warner [EMAIL PROTECTED] writes: Is there a known problem with TOAST tables growing indefinitely in 7.2? No. The index on the toast table may well bloat, because it's tracking a moving range of toast-item OIDs. But the table itself should be okay so long as it's vacuumed regularly. If you're seeing bloat while using plain (not full) vacuums, my guess would be that you need to enlarge the FSM parameters in postgresql.conf. On a separate issue, we have seen toast indexes growing indefinitely on 7.3 despite VACUUM FULL. Attempting a reindex results in: # reindex table tt; WARNING: table tt wasn't reindexed REINDEX AFAIK that will only reindex tt's own indexes. To reindex the toast table would require naming same, with something like: regression=# reindex table pg_toast.pg_toast_1675403; REINDEX or you could specify the index: regression=# reindex index pg_toast.pg_toast_1675403_index; REINDEX The number that appears in these names is the OID of the table owning the toast table (ie, tt's oid). regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] toast table growing indefinitely? Known
At 12:37 AM 2/12/2002 -0500, Tom Lane wrote: If you're seeing bloat while using plain (not full) vacuums, my guess would be that you need to enlarge the FSM parameters in postgresql.conf. Good call; they are still default (1 pages) and we are seeing 26000 pages per day being updated: NOTICE: Removed 102226 tuples in 26002 pages. CPU 2.02s/1.87u sec elapsed 69.85 sec. NOTICE: Pages 201819: Changed 26128, Empty 0; Tup 315227: Vac 102226, Keep 0, UnUsed 393793. Total CPU 9.57s/3.07u sec elapsed 189.32 sec. I've increased the value to 4 since we have much busier days, and I'll se what happens. Is there any way (other than VACUUM FULL) to recover the current lost space? Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 03 5330 3172 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] toast table growing indefinitely? Known problems?
Philip Warner [EMAIL PROTECTED] writes: Is there any way (other than VACUUM FULL) to recover the current lost space? Probably not. Plain VACUUM will reclaim any empty pages it happens to see at the end of the table, but without a VACUUM FULL you won't get any proactive effort to make the end-pages empty. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
