Re: [HACKERS] pg_get_indexdef() doesn't quote string reloptions
If this communication is in fact intended to be protected by some legal privilege, or to remain company confidential, you have definitely sent it to the wrong place. Sadly I don't control my company's email server. They however don't control my gmail account. I'll switch to that. eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?
On Nov 1, 2011, at 6:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: I can think of a number of places where you can write * where I'm pretty sure we *don't* want this. It should be restricted to top-level entries in SELECT targetlists, IMO. Yes. That is the exact conclusion I've come to. However, why is select table.* foo from table allowed? What does that even mean? eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?
On Oct 30, 2011, at 12:53 AM, Joshua D. Drake wrote: If it is quite regular I would actually argue two things: 1. Use a view 2. You haven't normalized correctly I am not trying to be a pedantic zealot or anything but those would be my arguments against. You know how general database work goes. For every situation where you can make a view or every situation where you should normalize, there's at least one corresponding situation where you can't. All database systems, Postgres included, give us plenty of ways to do things wrong, many of which are much worse than this little idea. I guess I'd like for everyone to evaluate the idea on the value it could provide to Postgres and its users, rather than imposing philosophical/religious beliefs about correct database schema design. I'm regularly tasked with debugging queries, analyzing, exporting, and otherwise transforming data into something a customer wants. I'd use something like SELECT * EXCLUDING (…) on a *daily* basis. I'm sick and tired of stuff like: psql -tA db -c \d table | cut -f1 -d\| | grep -v col | tr \\n , just to exclude one column from a list of maybe 100. And if multiple tables are involved in the query, it just gets that much more complicated. I'd rather do: SELECT * EXCLUDING (x.col) FROM ( giant application-generated query ) x; Then, once I verify it's all good: COPY ( SELECT * EXCLUDING (x.col) FROM ( giant application-generated query ) x ) TO '/tmp/foo.out' WITH CSV; Anyways, looks like it might be an uphill battle to get the idea accepted (let alone any code I write!), but I ain't givin' up just yet. eric PROPRIETARY AND COMPANY CONFIDENTIAL COMMUNICATIONS The information contained in this communication is intended only for the use of the addressee. Any other use is strictly prohibited. Please notify the sender if you have received this message in error. This communication is protected by applicable legal privileges and is company confidential. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Thoughts on SELECT * EXCLUDING (...) FROM ...?
The exact choice of keyword matters a lot less than whether this can be done with out shift/reduce or reduce/reduce conflicts. Which is the problem right now. See my other email. I'll post a diff tomorrow. Maybe if enough folks think is a feature worth having we can find a solution. My gram.y-fu is at its limits right now. eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.5 vs. 9.0, Postgres vs. PostgreSQL
On Jan 21, 2010, at 12:35 PM, David E. Wheeler wrote: And where do you think baby powder comes from? Sheesh. You won the thread! eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 2:47 PM, Tom Lane wrote: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Sorry if this is obvious to everyone else, but *when* will the error throw? During CREATE FUNCTION or during runtime? I'm secretly hoping that it'll throw during CREATE FUNCTION. I'd rather have my entire schema creation transaction abort so I can fix the problems up-front, rather than at random while the application is running. eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 3:46 PM, Tom Lane wrote: Sorry if this is obvious to everyone else, but *when* will the error throw? Whenever we do semantic analysis of the particular query or expression. That's what I figured. During CREATE FUNCTION or during runtime? I'm secretly hoping that it'll throw during CREATE FUNCTION. Be careful what you ask for, you might get it ;-) snip really good reasons Yeah, and we've got at least one function that does the CREATE TEMP TABLE foo (...) pattern. So I understand. We want to our schema to keep pace with whatever the default settings are for stuff like this, so it'd be great if we could find and resolve the issues sooner rather than later. We implemented better coding practices later on in the project to help us disambiguate between variables and columns, but there's still a bunch of legacy stuff that's going to be broken. eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Client application name
On Oct 13, 2009, at 11:02 AM, Dave Page wrote: A useful feature found in other DBMSs such as MS SQL Server that has been requested on these lists a few times, is the ability for a client application to report its name to the server. I've been following this thread closely and haven't seen mention of including the setting as part of the process name, so a 'ps' (on Unix) would display it. Thoughts? eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Client application name
On Oct 14, 2009, at 12:39 PM, Dave Page wrote: Isn't that cluttered enough already? I find the ps output uninformative. Having it display something that gets generated from my application would start to make it useful. Maybe what I really want is a totally different feature: log_line_prefix, but for process name. eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] When is a record NULL?
On Jul 26, 2009, at 6:46 PM, David E. Wheeler wrote: Is there some way to get using_while() to properly return all the records? I'm just a random lurker, but FOUND seems to work just fine (I suppose it's PG-specific?). http://www.postgresql.org/docs/8.1/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-DIAGNOSTICS BEGIN OPEN stuff; FETCH stuff INTO rec; WHILE FOUND LOOP RETURN NEXT rec; FETCH stuff INTO rec; END LOOP; END; HTH, eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1 regression?
On Apr 22, 2009, at 10:47 PM, Tom Lane wrote: I think this is due to a change that was made in 8.2: Cool. Thanks for the followup! eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4b1 regression?
On Apr 20, 2009, at 2:27 PM, Eric B. Ridge wrote: Some investigation showed that the use of non-IMMUTABLE PL/PGSQL functions as view columns, when these views are joined with other views, cause the query to be planned poorly. I'm sorry. I should have said VOLATILE functions. Which is the default if nothing is specified (and that's true for 8.1 and 8.4) eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4b1 regression?
I loaded a copy of a production database into PG 8.4b1 and immediately saw that all of our queries were significantly slower compared to v8.1. Some investigation showed that the use of non-IMMUTABLE PL/PGSQL functions as view columns, when these views are joined with other views, cause the query to be planned poorly. Attached are the two different plans. Literally, the only difference is changing the definition of the custom PL/PGSQL to be IMMUTABLE. I spent some time coming up with a reproduce-able schema, but it's almost 500k gzipped. Is that too big to attach to -hackers? The function in the test schema is simply: CREATE FUNCTION make_it_slow(id bigint) RETURNS text LANGUAGE plpgsql AS $$begin return 'non-immutable functions make it slow'; end;$$; In our case, the suspect functions *can* be declared IMMUTABLE, and we should have done that in the first place, but I thought it was worth mentioning that v8.1 did a much better job planning in this particular case. If my test schema will be beneficial, please let me know. Thanks! eric foo=# explain analyze select count(*) from c where tab_id = 2; QUERY PLAN Aggregate (cost=2014181.55..2014181.56 rows=1 width=0) (actual time=524.034..524.035 rows=1 loops=1) - Hash Join (cost=2316.92..2014180.53 rows=407 width=0) (actual time=436.223..524.026 rows=1 loops=1) Hash Cond: (item.id = folder.id) - Hash Join (cost=2311.61..2013055.97 rows=81398 width=1313) (actual time=51.783..508.441 rows=81398 loops=1) Hash Cond: (perms.owner_id = user.id) - Hash Join (cost=2310.45..5678.39 rows=81398 width=1281) (actual time=50.485..204.430 rows=81398 loops=1) Hash Cond: (perms.item_id = item.id) - Seq Scan on perms (cost=0.00..1332.98 rows=81398 width=17) (actual time=0.041..37.544 rows=81398 loops=1) Filter: (NOT deleted) - Hash (cost=1292.98..1292.98 rows=81398 width=1272) (actual time=50.389..50.389 rows=81398 loops=1) - Seq Scan on item (cost=0.00..1292.98 rows=81398 width=1272) (actual time=0.038..22.298 rows=81398 loops=1) - Hash (cost=1.07..1.07 rows=7 width=40) (actual time=0.017..0.017 rows=7 loops=1) - Seq Scan on user (cost=0.00..1.07 rows=7 width=40) (actual time=0.013..0.014 rows=7 loops=1) SubPlan 1 - Aggregate (cost=24.39..24.40 rows=1 width=0) (never executed) - Seq Scan on comments (cost=0.00..24.38 rows=3 width=0) (never executed) Filter: (read AND (item_id = $0)) - Hash (cost=5.29..5.29 rows=1 width=16) (actual time=0.157..0.157 rows=1 loops=1) - Nested Loop (cost=0.00..5.29 rows=1 width=16) (actual time=0.152..0.154 rows=1 loops=1) - Seq Scan on collection (cost=0.00..1.01 rows=1 width=8) (actual time=0.023..0.024 rows=1 loops=1) Filter: (tab_id = 2) - Index Scan using folder_pkey on folder (cost=0.00..4.27 rows=1 width=8) (actual time=0.125..0.126 rows=1 loops=1) Index Cond: (folder.id = collection.id) Total runtime: 525.447 ms (24 rows) foo=# explain analyze select count(*) from c where tab_id = 2; QUERY PLAN - Aggregate (cost=14.15..14.16 rows=1 width=0) (actual time=0.070..0.071 rows=1 loops=1) - Nested Loop (cost=0.00..14.14 rows=1 width=0) (actual time=0.048..0.053 rows=1 loops=1) - Nested Loop (cost=0.00..9.86 rows=1 width=24) (actual time=0.042..0.045 rows=1 loops=1) - Nested Loop (cost=0.00..9.58 rows=1 width=32) (actual time=0.038..0.040 rows=1 loops=1) - Nested Loop (cost=0.00..5.30 rows=1 width=24) (actual time=0.030..0.032 rows=1 loops=1) - Seq Scan on collection (cost=0.00..1.01 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1) Filter: (tab_id = 2) - Index Scan using perms_pkey on perms (cost=0.00..4.27 rows=1 width=16) (actual time=0.016..0.016 rows=1 loops=1) Index Cond: (perms.item_id = collection.id) Filter: (NOT perms.deleted) - Index Scan using item_pkey on item
[HACKERS] Kudos on v8.4dev
I just wanted to give you guys a virtual pat on the back for PG v8.4dev. I've been out of the computer world for a little over a year and we're still using v8.1 here where I work, so I'm a little behind the changes timeline since v8.1, but v8.4 is looking very nice. I'm working on migrating a 200G database (with large full text records) from a 3rd-party database system in 8.4dev as a proof of concept for using PG for everything, and so far things are great. Just the improvement in error message detail (esp. syntax errors WRT malformed data) is huge. I can't wait to get into all the other fancy stuff 8.4 has to offer. Hopefully it'll be released around the same time I've sold the PHB's on my POC. I'm going to be using 8.4dev for the next month or more, so if there's anything y'all would like feedback on, especially regarding multi- gigabyte databases w/ full text, I'll be happy to provide it. Keep up the great work! eric -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SRF's + SPI
With pg v7.4.7 I've written an SRF that uses SPI to return the results
of a query. It's one of those functions that works perfectly for me in
development but randomly crashes in production. Thus far I've been
unable to reproduce the crash. The problem is surely in my code. And
before I dig into debugging the hard stuff I would love a sanity check
to verify that I'm using SPI_connect()/SPI_finish() correctly within
the context of the SRF.
The backtrace from a core dump:
(gdb) bt
#0 0x0820ee29 in pfree ()
#1 0x08204a18 in end_MultiFuncCall ()
#2 0x4cee0f99 in my_src (fcinfo=0xbfffccd0) at foo.c:93
#3 0x0811058b in ExecMakeTableFunctionResult ()
#4 0x08750c78 in ?? ()
#5 0x4000 in ?? ()
#6 0xbfffccac in ?? ()
#7 0xbfffcd88 in ?? ()
And the code at foo.c:93
SRF_RETURN_DONE(funcctx);
I'm wondering if I'm using SPI incorrectly. In the past I've had
issues with incorrectly using SPI (esp with recursion), and I'm not
entirely sure how it should work with SRF's. Like I said, everything
usually works without problems, but from time to time it crashes.
Below is the basic outline of my code.
Any input will be greatly appreciated.
thanks!
eric
--
Datum my_srf (PG_FUNCTION_ARGS) {
FuncCallContext *funcctx;
if (SRF_IS_FIRSTCALL()) {
MemoryContext oldcontext;
char *query;
if(PG_ARGISNULL(0))
SRF_RETURN_DONE(NULL); /* nothing to expand when
arg[0] is null */
funcctx = SRF_FIRSTCALL_INIT();
oldcontext =
MemoryContextSwitchTo(funcctx-multi_call_memory_ctx);
query = function call to build a query string ;
SPI_connect();
if (SPI_exec(query, 0) != SPI_OK_SELECT)
elog(ERROR, unable to execute fulltext query);
funcctx-slot = TupleDescGetSlot(SPI_tuptable-tupdesc);
funcctx-attinmeta =
TupleDescGetAttInMetadata(SPI_tuptable-tupdesc);
funcctx-user_fctx = SPI_tuptable;
funcctx-call_cntr = 0;
funcctx-max_calls = SPI_processed;
MemoryContextSwitchTo(oldcontext);
}
funcctx = SRF_PERCALL_SETUP();
if (funcctx-call_cntr funcctx-max_calls) {
SPITupleTable *tuptable = (SPITupleTable *) funcctx-user_fctx;
HeapTuple tuple;
Datum result;
tuple = tuptable-vals[funcctx-call_cntr];
result = TupleGetDatum(funcctx-slot, tuple);
SRF_RETURN_NEXT(funcctx, result);
} else {
SPI_finish();
line_93:
SRF_RETURN_DONE(funcctx); /** XXX: CRASH HERE **/
}
PG_RETURN_NULL();
}
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] unnest
On Nov 5, 2004, at 7:09 AM, John Hansen wrote:
Attached, array - rows iterator.
select * from unnest(array[1,2,3,4,5]);
This is really handy! But there is a problem...
The switch statement could probably be done in a different way, but
there doesn't seem to be any good examples of how to return anyitem. If
anyone have a better way, please let me know.
Why do you need the switch statement at all? array-elements is already
an array of Datums. Won't simply returning
array-elements[array-i]
work?
The problem is:
test=# select * from unnest('{1,2,3,4,5}'::int8[]);
unnest
--
25314880
25314888
25314896
25314904
25314912
(5 rows)
Whereas simply returning the current Datum in array-elements returns
the correct result:
if (array-i array-num_elements)
SRF_RETURN_NEXT(funcctx,array-elements[array-i++]);
else
SRF_RETURN_DONE(funcctx);
test=# select * from unnest('{1,2,3,4,5}'::int8[]);
unnest
1
2
3
4
5
(5 rows)
Also works for the few other datatypes I checked.
Am I missing something obvious?
eric
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] User Defined Functions/AM's inherently slow?
On Jan 18, 2004, at 7:28 PM, Tom Lane wrote: Theory B would be that there's some huge overhead in calling non-built-in functions on your platform. I've done some profiling and convinced myself that indeed there's pretty steep overhead involved in fmgr_info() for a C-language function. Much of it isn't platform-dependent either --- as best I can tell, the lion's share of the time is being eaten in expand_dynamic_library_name(). In scenarios where a function is called many times per query, we cache the results of fmgr_info() ... but we do not do so for operations like ambeginscan that are done just once per query. Wow, thanks for spending the time on this. What about for gettuple? Do calls to it take advantage of the cache? If not, this likely explains some of my custom am's performance troubles. Every other function language uses shortcuts or caching to reduce the cost of fmgr_info() lookup; external C language is the only one that hasn't been optimized in this way. I shall see what I can do about that. ISTM we can have a hash table that maps function OID to function address using the same sorts of techniques that plpgsql et al use. If there's anything I can do to help, let me know. I'll be happy to test any patches you might come up with too. eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [GENERAL] Can't Build 7.3.4 on OS X
On Sep 27, 2003, at 3:43 PM, Tom Lane wrote: Eric Ridge [EMAIL PROTECTED] writes: I don't think the OS X 10.3 betas are readily available (I've payed to be in Apple's developer program), so if you don't have access to 10.3 but have some idea as to what would cause this problem with tas, I'll do whatever I can to help test. I have verified that CVS tip builds okay on 10.3 beta. I would recommend dropping the CVS-tip versions of s_lock.h and s_lock.c into the 7.3 source tree if you need to get 7.3 working on 10.3. Using s_lock.c and .h from at least 7.4 works too. Were you ever able to figure out why 7.3.4 wouldn't build? eric ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [GENERAL] division by zero
On Saturday, March 8, 2003, at 11:54 PM, Justin Clift wrote: Tom Lane wrote: snip 2. Consider this Apple's problem and file a bug report. Is there a good place to report errors to Apple for this kind of thing? The best place I can find is: http://developer.apple.com/bugreporter/index.html Unfortunately, there doesn't seem to be a way to query existing reports... If there is, I can't find it. Also, I can't help but wonder why Apple/DarwinTeam handle integer division by zero this way. There must be a reason, which makes me think that [considering] this Apple's problem might not work out for postgres in the end. eric ---(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] psql and readline
I've been following this thread, and I thought this might be a good place and time to throw in a few additional feature requests. What about expanding the history capabilities of psql's history command (\s) to include something more bash/tcsh-like? For example: !insert -- execute the last command that began with insert !23 -- execute item #23 in my history The above would require \s to output history index numbers. Might also be cool if it would (optionally) truncate each line at the console width, so it would be a little easier to read. And what about some kind of switch to tell psql to combine multi-line commands into 1. So if I type a big select on 5 lines, after executing it, it appears as 1 entry in my history. Say something like \ss for history Single and \sv for history Verbatim (or whatever). Thanks for your time. eric ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
