aboutsummaryrefslogtreecommitdiff
path: root/contrib/tablefunc/tablefunc.c
Commit message (Collapse)AuthorAge
...
* Fix dblink and tablefunc to not return with the wrong CurrentMemoryContext.Tom Lane2008-11-30
| | | | Per buildfarm results.
* Be more tense about not creating tuplestores with randomAccess = true unlessTom Lane2008-10-29
| | | | | | | | backwards scan could actually happen. In particular, pass a flag to materialize-mode SRFs that tells them whether they need to require random access. In passing, also suppress unneeded backward-scan overhead for a Portal's holdStore tuplestore. Per my proposal about reducing I/O costs for tuplestores.
* Extend ExecMakeFunctionResult() to support set-returning functions that returnTom Lane2008-10-28
| | | | | | | | | via a tuplestore instead of value-per-call. Refactor a few things to reduce ensuing code duplication with nodeFunctionscan.c. This represents the reasonably noncontroversial part of my proposed patch to switch SQL functions over to returning tuplestores. For the moment, SQL functions still do things the old way. However, this change enables PL SRFs to be called in targetlists (observe changes in plperl regression results).
* Add $PostgreSQL$ markers to a lot of files that were missing them.Andrew Dunstan2008-05-17
| | | | | | | | | | This particular batch was just for *.c and *.h file. The changes were made with the following 2 commands: find . \( \( -name 'libstemmer' -o -name 'expected' -o -name 'ppport.h' \) -prune \) -o \( -name '*.[ch]' \) \( -exec grep -q '\$PostgreSQL' {} \; -o -print \) | while read file ; do head -n 1 < $file | grep -q '^/\*' && echo $file; done | xargs -l sed -i -e '1s/^\// /' -e '1i/*\n * $PostgreSQL:$ \n *' find . \( \( -name 'libstemmer' -o -name 'expected' -o -name 'ppport.h' \) -prune \) -o \( -name '*.[ch]' \) \( -exec grep -q '\$PostgreSQL' {} \; -o -print \) | xargs -l sed -i -e '1i/*\n * $PostgreSQL:$ \n */'
* Simplify and standardize conversions between TEXT datums and ordinary CTom Lane2008-03-25
| | | | | | | | | | | | | | | | | | | | strings. This patch introduces four support functions cstring_to_text, cstring_to_text_with_len, text_to_cstring, and text_to_cstring_buffer, and two macros CStringGetTextDatum and TextDatumGetCString. A number of existing macros that provided variants on these themes were removed. Most of the places that need to make such conversions now require just one function or macro call, in place of the multiple notational layers that used to be needed. There are no longer any direct calls of textout or textin, and we got most of the places that were using handmade conversions via memcpy (there may be a few still lurking, though). This commit doesn't make any serious effort to eliminate transient memory leaks caused by detoasting toasted text objects before they reach text_to_cstring. We changed PG_GETARG_TEXT_P to PG_GETARG_TEXT_PP in a few places where it was easy, but much more could be done. Brendan Jurd and Tom Lane
* Update copyrights in source tree to 2008.Bruce Momjian2008-01-01
|
* Clean up a couple of problems in crosstab_hash's use of a hash table.Tom Lane2007-12-07
| | | | | | | | | | | | The original coding leaked memory (at least 8K per crosstab_hash call) because it allowed the hash table to be allocated as a child of TopMemoryContext and then never freed it. Fix that by putting the hash table under per_query_ctx, instead. Also get rid of use of a static variable to point to the hash table. Aside from being ugly, that would actively do the wrong thing in the case of re-entrant calls to crosstab_hash, which are at least theoretically possible since it was expecting the static variable to stay valid across a SPI_execute call.
* pgindent run for 8.3.Bruce Momjian2007-11-15
|
* Have crosstab variants treat NULL rowid as a category in its own right,Joe Conway2007-11-10
| | | | | per suggestion from Tom Lane. This fixes crash-bug reported by Stefan Schwarzer.
* Add resetStringInfo(), which clears the content of a StringInfo, andNeil Conway2007-03-03
| | | | | | fixup various places in the tree that were clearing a StringInfo by hand. Making this function a part of the API simplifies client code slightly, and avoids needlessly peeking inside the StringInfo interface.
* Update CVS HEAD for 2007 copyright. Back branches are typically notBruce Momjian2007-01-05
| | | | back-stamped for this.
* pgindent run for 8.2.Bruce Momjian2006-10-04
|
* Create infrastructure for 'MinimalTuple' representation of in-memoryTom Lane2006-06-27
| | | | | | | | tuples with less header overhead than a regular HeapTuple, per my recent proposal. Teach TupleTableSlot code how to deal with these. As proof of concept, change tuplestore.c to store MinimalTuples instead of HeapTuples. Future patches will expand the concept to other places where it is useful.
* Magic blocks don't do us any good unless we use 'em ... so install oneTom Lane2006-05-30
| | | | in every shared library.
* Update copyright for 2006. Update scripts.Bruce Momjian2006-03-05
|
* Attached is a patch that replaces a bunch of places where StringInfosNeil Conway2006-03-01
| | | | | | | | | | | | | are unnecessarily allocated on the heap rather than the stack. If the StringInfo doesn't outlive the stack frame in which it is created, there is no need to allocate it on the heap via makeStringInfo() -- stack allocation is faster. While it's not a big deal unless the code is in a critical path, I don't see a reason not to save a few cycles -- using stack allocation is not less readable. I also cleaned up a bit of code along the way: moved variable declarations into a more tightly-enclosing scope where possible, fixed some pointless copying of strings in dblink, etc.
* This patch makes the error message strings throughout the backendNeil Conway2006-03-01
| | | | | | | | more compliant with the error message style guide. In particular, errdetail should begin with a capital letter and end with a period, whereas errmsg should not. I also fixed a few related issues in passing, such as fixing the repeated misspelling of "lexeme" in contrib/tsearch2 (per Tom's suggestion).
* Re-run pgindent, fixing a problem where comment lines after a blankBruce Momjian2005-11-22
| | | | | | | | | comment line where output as too long, and update typedefs for /lib directory. Also fix case where identifiers were used as variable names in the backend, but as typedefs in ecpg (favor the backend for indenting). Backpatch to 8.1.X.
* Standard pgindent run for 8.1.Bruce Momjian2005-10-15
|
* Fix inadequate error checking: you can't assume that fcinfo->resultinfoTom Lane2005-07-09
| | | | is a ReturnSetInfo unless you've tested it with IsA.
* Document get_call_result_type() and friends; mark TypeGetTupleDesc()Tom Lane2005-05-30
| | | | | | and RelationNameGetTupleDesc() as deprecated; remove uses of the latter in the contrib library. Along the way, clean up crosstab() code and documentation a little.
* Modify hash_search() API to prevent future occurrences of the errorTom Lane2005-05-29
| | | | | | | | | | | | | spotted by Qingqing Zhou. The HASH_ENTER action now automatically fails with elog(ERROR) on out-of-memory --- which incidentally lets us eliminate duplicate error checks in quite a bunch of places. If you really need the old return-NULL-on-out-of-memory behavior, you can ask for HASH_ENTER_NULL. But there is now an Assert in that path checking that you aren't hoping to get that behavior in a palloc-based hash table. Along the way, remove the old HASH_FIND_SAVE/HASH_REMOVE_SAVED actions, which were not being used anywhere anymore, and were surely too ugly and unsafe to want to see revived again.
* Update copyrights that were missed.Bruce Momjian2005-01-01
|
* Adjust comments previously moved to column 1 by pgident.Bruce Momjian2004-10-07
|
* Redesign query-snapshot timing so that volatile functions in READ COMMITTEDTom Lane2004-09-13
| | | | | | | | | | | | | mode see a fresh snapshot for each command in the function, rather than using the latest interactive command's snapshot. Also, suppress fresh snapshots as well as CommandCounterIncrement inside STABLE and IMMUTABLE functions, instead using the snapshot taken for the most closely nested regular query. (This behavior is only sane for read-only functions, so the patch also enforces that such functions contain only SELECT commands.) As per my proposal of 6-Sep-2004; I note that I floated essentially the same proposal on 19-Jun-2002, but that discussion tailed off without any action. Since 8.0 seems like the right place to be taking possibly nontrivial backwards compatibility hits, let's get it done now.
* Pgindent run for 8.0.Bruce Momjian2004-08-29
|
* Update copyright to 2004.Bruce Momjian2004-08-29
|
* Hashed crosstab was dying with an SPI_finish error when the source SQLJoe Conway2004-08-11
| | | | | produced no rows. Now it returns 0 rows instead. Adjusted regression test for this case.
* Replace TupleTableSlot convention for whole-row variables and functionTom Lane2004-04-01
| | | | | | | | results with tuples as ordinary varlena Datums. This commit does not in itself do much for us, except eliminate the horrid memory leak associated with evaluation of whole-row variables. However, it lays the groundwork for allowing composite types as table columns, and perhaps some other useful features as well. Per my proposal of a few days ago.
* Apply quote_literal to the start_with argument of connectby. Fixes problemJoe Conway2004-02-24
| | | | reported by David Garamond when working with bytea parent and child keys.
* Rename SortMem and VacuumMem to work_mem and maintenance_work_mem.Tom Lane2004-02-03
| | | | | | | Make btree index creation and initial validation of foreign-key constraints use maintenance_work_mem rather than work_mem as their memory limit. Add some code to guc.c to allow these variables to be referenced by their old names in SHOW and SET commands, for backwards compatibility.
* Attached is a patch for contrib/tablefunc. It fixes two issues raised byTom Lane2003-10-02
| | | | | | | Lars Boegild Thomsen (full email below) and also corrects the regression expected output for a recent backend message adjustment. Please apply. Joe Conway
* With Joe Conway's concurrence, remove srandom() call from normal_rand().Tom Lane2003-09-13
| | | | | | This was the last piece of code that took it upon itself to reset the random number sequence --- now we only have srandom() in postmaster start, backend start, and explicit setseed() operations.
* Fix some copyright notices that weren't updated. Improve copyright toolTom Lane2003-08-04
| | | | so it won't miss 'em again.
* pgindent run.Bruce Momjian2003-08-04
|
* > Am Son, 2003-06-22 um 02.09 schrieb Joe Conway:Bruce Momjian2003-07-27
| | | | | | | | | | | | | | | | | | | >>Sounds like all that's needed for your case. But to be complete, in >>addition to changing tablefunc.c we'd have to: >>1) come up with a new function call signature that makes sense and does >>not cause backward compatibility problems for other people >>2) make needed changes to tablefunc.sql.in >>3) adjust the README.tablefunc appropriately >>4) adjust the regression test for new functionality >>5) be sure we don't break any of the old cases >> >>If you want to submit a complete patch, it would be gratefully accepted >>-- for review at least ;-) > > Here's the patch, at least for steps 1-3 Nabil Sayegh Joe Conway
* Error message editing in contrib (mostly by Joe Conway --- thanks Joe!)Tom Lane2003-07-24
|
* 'third' -> 'fourth' where needed, per Joe Conway.Tom Lane2003-06-25
|
* Replace cryptic 'Unknown kind of return type' messages with somethingTom Lane2003-06-15
| | | | hopefully a little more useful.
* Check calling context for connectby_text(), per Joe Conway.Tom Lane2003-05-16
|
* Fix various recent build and regression-test problems in contrib/.Tom Lane2003-05-14
| | | | Includes fixes from Joe Conway.
* This patch implements holdable cursors, following the proposalBruce Momjian2003-03-27
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | (materialization into a tuple store) discussed on pgsql-hackers earlier. I've updated the documentation and the regression tests. Notes on the implementation: - I needed to change the tuple store API slightly -- it assumes that it won't be used to hold data across transaction boundaries, so the temp files that it uses for on-disk storage are automatically reclaimed at end-of-transaction. I added a flag to tuplestore_begin_heap() to control this behavior. Is changing the tuple store API in this fashion OK? - in order to store executor results in a tuple store, I added a new CommandDest. This works well for the most part, with one exception: the current DestFunction API doesn't provide enough information to allow the Executor to store results into an arbitrary tuple store (where the particular tuple store to use is chosen by the call site of ExecutorRun). To workaround this, I've temporarily hacked up a solution that works, but is not ideal: since the receiveTuple DestFunction is passed the portal name, we can use that to lookup the Portal data structure for the cursor and then use that to get at the tuple store the Portal is using. This unnecessarily ties the Portal code with the tupleReceiver code, but it works... The proper fix for this is probably to change the DestFunction API -- Tom suggested passing the full QueryDesc to the receiveTuple function. In that case, callers of ExecutorRun could "subclass" QueryDesc to add any additional fields that their particular CommandDest needed to get access to. This approach would work, but I'd like to think about it for a little bit longer before deciding which route to go. In the mean time, the code works fine, so I don't think a fix is urgent. - (semi-related) I added a NO SCROLL keyword to DECLARE CURSOR, and adjusted the behavior of SCROLL in accordance with the discussion on -hackers. - (unrelated) Cleaned up some SGML markup in sql.sgml, copy.sgml Neil Conway
* Attached is an update to contrib/tablefunc. It implements a new hashedBruce Momjian2003-03-20
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | version of crosstab. This fixes a major deficiency in real-world use of the original version. Easiest to undestand with an illustration: Data: ------------------------------------------------------------------- select * from cth; id | rowid | rowdt | attribute | val ----+-------+---------------------+----------------+--------------- 1 | test1 | 2003-03-01 00:00:00 | temperature | 42 2 | test1 | 2003-03-01 00:00:00 | test_result | PASS 3 | test1 | 2003-03-01 00:00:00 | volts | 2.6987 4 | test2 | 2003-03-02 00:00:00 | temperature | 53 5 | test2 | 2003-03-02 00:00:00 | test_result | FAIL 6 | test2 | 2003-03-02 00:00:00 | test_startdate | 01 March 2003 7 | test2 | 2003-03-02 00:00:00 | volts | 3.1234 (7 rows) Original crosstab: ------------------------------------------------------------------- SELECT * FROM crosstab( 'SELECT rowid, attribute, val FROM cth ORDER BY 1,2',4) AS c(rowid text, temperature text, test_result text, test_startdate text, volts text); rowid | temperature | test_result | test_startdate | volts -------+-------------+-------------+----------------+-------- test1 | 42 | PASS | 2.6987 | test2 | 53 | FAIL | 01 March 2003 | 3.1234 (2 rows) Hashed crosstab: ------------------------------------------------------------------- SELECT * FROM crosstab( 'SELECT rowid, attribute, val FROM cth ORDER BY 1', 'SELECT DISTINCT attribute FROM cth ORDER BY 1') AS c(rowid text, temperature int4, test_result text, test_startdate timestamp, volts float8); rowid | temperature | test_result | test_startdate | volts -------+-------------+-------------+---------------------+-------- test1 | 42 | PASS | | 2.6987 test2 | 53 | FAIL | 2003-03-01 00:00:00 | 3.1234 (2 rows) Notice that the original crosstab slides data over to the left in the result tuple when it encounters missing data. In order to work around this you have to be make your source sql do all sorts of contortions (cartesian join of distinct rowid with distinct attribute; left join that back to the real source data). The new version avoids this by building a hash table using a second distinct attribute query. The new version also allows for "extra" columns (see the README) and allows the result columns to be coerced into differing datatypes if they are suitable (as shown above). In testing a "real-world" data set (69 distinct rowid's, 27 distinct categories/attributes, multiple missing data points) I saw about a 5-fold improvement in execution time (from about 2200 ms old, to 440 ms new). I left the original version intact because: 1) BC, 2) it is probably slightly faster if you know that you have no missing attributes. README and regression test adjustments included. If there are no objections, please apply. Joe Conway
* This patch fixes a bunch of spelling mistakes in comments throughout theTom Lane2003-03-10
| | | | | | PostgreSQL source code. Neil Conway
* Revise tuplestore and nodeMaterial so that we don't have to read theTom Lane2003-03-09
| | | | | | | | | | entire contents of the subplan into the tuplestore before we can return any tuples. Instead, the tuplestore holds what we've already read, and we fetch additional rows from the subplan as needed. Random access to the previously-read rows works with the tuplestore, and doesn't affect the state of the partially-read subplan. This is a step towards fixing the problems with cursors over complex queries --- we don't want to stick in Materialize nodes if they'll prevent quick startup for a cursor.
* Remove inappropriate double-quoting in connectby() code; adjustTom Lane2002-11-23
| | | | regression test to avoid using VALUE as a name. From Joe Conway.
* > The previous patch fixed an infinite recursion bug inBruce Momjian2002-10-03
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > contrib/tablefunc/tablefunc.c:connectby. But, other unmanageable error > seems to occur even if a table has commonplace tree data(see below). > > I would think the patch, ancestor check, should be > > if (strstr(branch_delim || branchstr->data || branch_delim, > branch_delim || current_key || branch_delim)) > > This is my image, not a real code. However, if branchstr->data includes > branch_delim, my image will not be perfect. Good point. Thank you Masaru for the suggested fix. Attached is a patch to fix the bug found by Masaru. His example now produces: regression=# SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '11', 0, '-') AS t(keyid int, parent_keyid int, level int, branch text); keyid | parent_keyid | level | branch -------+--------------+-------+---------- 11 | | 0 | 11 10 | 11 | 1 | 11-10 111 | 11 | 1 | 11-111 1 | 111 | 2 | 11-111-1 (4 rows) While making the patch I also realized that the "no show branch" form of the function was not going to work very well for recursion detection. Therefore there is now a default branch delimiter ('~') that is used internally, for that case, to enable recursion detection to work. If you need a different delimiter for your specific data, you will have to use the "show branch" form of the function. Joe Conway
* Fix portability bug in get_normal_pair (RAND_MAX != MAX_RANDOM_VALUE).Tom Lane2002-09-14
| | | | Also try to improve readability and performance.
* > Now I'm testing connectby() in the /contrib/tablefunc in 7.3b1, which wouldBruce Momjian2002-09-12
| | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | > be a useful function for many users. However, I found the fact that > if connectby_tree has the following data, connectby() tries to search the end > of roots without knowing that the relations are infinite(-5-9-10-11-9-10-11-) . > I hope connectby() supports a check routine to find infinite relations. > > > CREATE TABLE connectby_tree(keyid int, parent_keyid int); > INSERT INTO connectby_tree VALUES(1,NULL); > INSERT INTO connectby_tree VALUES(2,1); > INSERT INTO connectby_tree VALUES(3,1); > INSERT INTO connectby_tree VALUES(4,2); > INSERT INTO connectby_tree VALUES(5,2); > INSERT INTO connectby_tree VALUES(6,4); > INSERT INTO connectby_tree VALUES(7,3); > INSERT INTO connectby_tree VALUES(8,6); > INSERT INTO connectby_tree VALUES(9,5); > > INSERT INTO connectby_tree VALUES(10,9); > INSERT INTO connectby_tree VALUES(11,10); > INSERT INTO connectby_tree VALUES(9,11); <-- infinite > The attached patch fixes the infinite recursion bug in contrib/tablefunc/tablefunc.c:connectby found by Masaru Sugawara. test=# SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 4, '~') AS t(keyid int, parent_keyid int, level int, branch text); keyid | parent_keyid | level | branch -------+--------------+-------+------------- 2 | | 0 | 2 4 | 2 | 1 | 2~4 6 | 4 | 2 | 2~4~6 8 | 6 | 3 | 2~4~6~8 5 | 2 | 1 | 2~5 9 | 5 | 2 | 2~5~9 10 | 9 | 3 | 2~5~9~10 11 | 10 | 4 | 2~5~9~10~11 (8 rows) test=# SELECT * FROM connectby('connectby_tree', 'keyid', 'parent_keyid', '2', 5, '~') AS t(keyid int, parent_keyid int, level int, branch text); ERROR: infinite recursion detected I implemented it by checking the branch string for repeated keys (whether or not the branch is returned). The performance hit was pretty minimal -- about 1% for a moderately complex test case (220000 record table, 9 level tree with 3800 members). Joe Conway
* Be careful to include postgres.h *before* any system headers, to ensureTom Lane2002-09-05
| | | | | | that the right flavors of largefile-related definitions are seen. Most of these changes are probably unnecessary, but better safe than sorry.
generated by cgit v1.2.3 (git 2.25.1) at 2025-08-05 04:25:08 +0000