Re: [HACKERS] Tru64/Alpha problems
Tom Lane wrote: Andrew Dunstan [EMAIL PROTECTED] writes: I'd like to know some settings that we can use that will get Tru64 cleanly through the buildfarm set. If noone offers any, I propose that we revert the getaddrinfo() test in configure and use our own on Tru64 until they do. I have not had any response to this. Is there any objection to my reverting the configure changes for the head and 8.1 branches? Presumably, whoever was complaining beforehand will come back ... but I don't remember who that was. regards, tom lane i think the issue you are referring to comes from a Solaris report. some patch levels of solaris have seriously broken getaddrinfo(). in this case pg_hba.conf cannot be read anymore. we got a similar report some time ago. we did a simple configure tweak to make sure that the onboard function is used. it seems to happen only on some strange patchlevel (god knows which ones). best regards, hans -- Cybertec Geschwinde Schönig GmbH Schöngrabern 134; A-2020 Hollabrunn Tel: +43/1/205 10 35 / 340 www.postgresql.at, www.cybertec.at ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Support Parallel Query Execution in Executor
Josh Berkus josh@agliodbs.com writes: Basically reading a large table off disk does this: read some table while not processing process in cpu while not reading read some more table while not processing process some more in cpu while not reading etc. resulting in an I/O througput graph that looks like: * * * * ** ** * ** ** ** * * * * Interesting ... The really annoying part about this, for me personally, is that the peaks are significantly faster than comparable commercial DBMSes ... but our average is far less. So even on a single seq scan, parallel query execution would make a significant difference in performance, possibly as much as +75% on seq scans of large tables. ... but I'm failing to follow where it says that parallel processing will fix that. All I can foresee in that direction is extra data transfer costs, bought at the price of portability and locking headaches. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] How to implement oracle like rownum(function or seudocolumn) ?
I need a rownum column, like Oracle. I have searched the mailing lists and I don't see a satisfactory solution, so I was wondering write a UDF to implement it, the requirements are:1. Need to preserve state between calls to the function (the rowcount). Maybe using a MemoryContext with a lifespan equal to the executing query. 2. It should be called every time a new row is produced. 3. And more important, need to be called in the right place when called from subquerys:Imagine one to man relationship between table A and tabla B. Then next querySELECT s.id, s.rownum FROM (SELECT a.id, rownum() AS rownum FROM a) s, b WHERE a.id = b.idIn this case, the rownum function should behave as if it was an aggregated function, where the subquery is evaluated first and the results joined with table b. I think that a UDF could be evaluated last by the planner (when producing the final resultset) so it won't give the intended result.I looking for a general solution, Any ideas? .Thanks. Atte.Juan Manuel Díaz Lara New Yahoo! Messenger with Voice. Call regular phones from your PC and save big.
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn) ?
Juan Manuel Diaz Lara [EMAIL PROTECTED] writes: I need a rownum column, like Oracle. I have searched the mailing lists and I don't see a satisfactory solution, so I was wondering write a UDF to implement it, the requirements are: Try keeping a counter in fcinfo-flinfo-fn_extra. 3. And more important, need to be called in the right place when called from subquerys: Don't expect miracles in this department. The planner will evaluate the function where it sees fit... regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Support Parallel Query Execution in Executor
On 4/8/06, Tom Lane [EMAIL PROTECTED] wrote: ... but I'm failing to follow where it says that parallel processing will fix that. All I can foresee in that direction is extra data transfer costs, bought at the price of portability and locking headaches. I don't think it's any less portable than the system is now; It's just enabling multiple slave processes to participate in scans and processing (parallel query, parallel index builds, parallel sorts, ...) Likewise, the additional I/O cost isn't that much of an issue because systems which really take advantage of this type of parallel processing have large bandwidth I/O arrays anyway. I didn't even want to mention that EVERY other database I know of (Oracle, DB2, Sybase, SQL Server, Ingres, Bizgres MPP, MaxDB) supports this, but it's a pretty obvious win for many environments. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn) ?
On Sat, Apr 08, 2006 at 12:46:06PM -0400, Tom Lane wrote:
Juan Manuel Diaz Lara [EMAIL PROTECTED] writes:
I need a rownum column, like Oracle. I have searched the mailing lists
and I don't see a satisfactory solution, so I was wondering write a
UDF to implement it, the requirements are:
Try keeping a counter in fcinfo-flinfo-fn_extra.
Is this close to being correct?
Datum
rownum(PG_FUNCTION_ARGS)
{
int64 *row_counter;
if (fcinfo-flinfo-fn_extra == NULL) {
row_counter = (int64 *)MemoryContextAlloc(fcinfo-flinfo-fn_mcxt,
sizeof(int64));
*row_counter = 0;
fcinfo-flinfo-fn_extra = row_counter;
}
row_counter = fcinfo-flinfo-fn_extra;
PG_RETURN_INT64(++(*row_counter));
}
3. And more important, need to be called in the right place when
called from subquerys:
Don't expect miracles in this department. The planner will evaluate the
function where it sees fit...
Would OFFSET 0 be the workaround in this case?
SELECT rownum(), *
FROM (SELECT rownum() AS f_rownum, id AS f_id FROM foo) AS f,
(SELECT rownum() AS b_rownum, id AS b_id FROM bar) AS b;
rownum | f_rownum | f_id | b_rownum | b_id
+--+---+--+---
1 |1 | foo-1 |1 | bar-1
2 |2 | foo-1 |2 | bar-2
3 |3 | foo-2 |3 | bar-1
4 |4 | foo-2 |4 | bar-2
5 |5 | foo-3 |5 | bar-1
6 |6 | foo-3 |6 | bar-2
(6 rows)
SELECT rownum(), *
FROM (SELECT rownum() AS f_rownum, id AS f_id FROM foo OFFSET 0) AS f,
(SELECT rownum() AS b_rownum, id AS b_id FROM bar OFFSET 0) AS b;
rownum | f_rownum | f_id | b_rownum | b_id
+--+---+--+---
1 |1 | foo-1 |1 | bar-1
2 |1 | foo-1 |2 | bar-2
3 |2 | foo-2 |1 | bar-1
4 |2 | foo-2 |2 | bar-2
5 |3 | foo-3 |1 | bar-1
6 |3 | foo-3 |2 | bar-2
(6 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] Support Parallel Query Execution in Executor
Hi, On Sat, 2006-04-08 at 13:16 -0400, Jonah H. Harris wrote: On 4/8/06, Tom Lane [EMAIL PROTECTED] wrote: ... but I'm failing to follow where it says that parallel processing will fix that. All I can foresee in that direction is extra data transfer costs, bought at the price of portability and locking headaches. I don't think it's any less portable than the system is now; ACK. As long as processes, signals and shared memory are used this could be as portable as PostgreSQL is now. It's just enabling multiple slave processes to participate in scans and processing (parallel query, parallel index builds, parallel sorts, ...) Likewise, the additional I/O cost isn't that much of an issue because systems which really take advantage of this type of parallel processing have large bandwidth I/O arrays anyway. Ehm.. which additional I/O cost? Or do you count inter-process communication to I/O? I'd like to help teaching PostgreSQL the art of parallel query execution. I have rawly implemented an internal message passing system on shared memory basis. This allows all pg-processes to send messages to each other identified by PID. Uppon receiving of a message a process gets a SIGUSR2 (AFAIC remember now). On the positive side are: - portable as I'm using only shmem and signals - fast (not much copying around of the data in memory) One downside I see with my approach is: - the shared memory buffer is limited in size, so it can simply be 'full' and no more messages can be sent before another process reads its messages and frees the memory for other messages. In case you're interested I'll compile a patch for review. Regards Markus ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Support Parallel Query Execution in Executor
On 4/8/06, Markus Schiltknecht [EMAIL PROTECTED] wrote: ACK. As long as processes, signals and shared memory are used this could be as portable as PostgreSQL is now. This is certainly the case. Ehm.. which additional I/O cost? Or do you count inter-process communication to I/O? Inter-process will add a minimal amount, but if it's done correctly, you'll still end up ahead. I'd like to help teaching PostgreSQL the art of parallel query execution. Cool, we're not at the communication-level yet, but your help would be appreciated. In case you're interested I'll compile a patch for review. Surely! -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
Someone correct me if I'm wrong, but I was allways under the impression
that Oracle's ROWNUM is a thing attached to a row in the final result
set, whatever (possibly random) order that happens to have. Now a) this
is something that IMHO belongs into the client or stored procedure code,
b) if I am right, the code below will break as soon as an ORDER BY is
added to the query and most importantly c) if a) cannot do the job, it
indicates that the database schema or business process definition lacks
some key/referential definition and is in need of a fix.
My humble guess is that c) is also the reason why the ANSI didn't find a
ROWNUM desirable.
Jan
On 4/8/2006 1:26 PM, Michael Fuhr wrote:
On Sat, Apr 08, 2006 at 12:46:06PM -0400, Tom Lane wrote:
Juan Manuel Diaz Lara [EMAIL PROTECTED] writes:
I need a rownum column, like Oracle. I have searched the mailing lists
and I don't see a satisfactory solution, so I was wondering write a
UDF to implement it, the requirements are:
Try keeping a counter in fcinfo-flinfo-fn_extra.
Is this close to being correct?
Datum
rownum(PG_FUNCTION_ARGS)
{
int64 *row_counter;
if (fcinfo-flinfo-fn_extra == NULL) {
row_counter = (int64 *)MemoryContextAlloc(fcinfo-flinfo-fn_mcxt,
sizeof(int64));
*row_counter = 0;
fcinfo-flinfo-fn_extra = row_counter;
}
row_counter = fcinfo-flinfo-fn_extra;
PG_RETURN_INT64(++(*row_counter));
}
3. And more important, need to be called in the right place when
called from subquerys:
Don't expect miracles in this department. The planner will evaluate the
function where it sees fit...
Would OFFSET 0 be the workaround in this case?
SELECT rownum(), *
FROM (SELECT rownum() AS f_rownum, id AS f_id FROM foo) AS f,
(SELECT rownum() AS b_rownum, id AS b_id FROM bar) AS b;
rownum | f_rownum | f_id | b_rownum | b_id
+--+---+--+---
1 |1 | foo-1 |1 | bar-1
2 |2 | foo-1 |2 | bar-2
3 |3 | foo-2 |3 | bar-1
4 |4 | foo-2 |4 | bar-2
5 |5 | foo-3 |5 | bar-1
6 |6 | foo-3 |6 | bar-2
(6 rows)
SELECT rownum(), *
FROM (SELECT rownum() AS f_rownum, id AS f_id FROM foo OFFSET 0) AS f,
(SELECT rownum() AS b_rownum, id AS b_id FROM bar OFFSET 0) AS b;
rownum | f_rownum | f_id | b_rownum | b_id
+--+---+--+---
1 |1 | foo-1 |1 | bar-1
2 |1 | foo-1 |2 | bar-2
3 |2 | foo-2 |1 | bar-1
4 |2 | foo-2 |2 | bar-2
5 |3 | foo-3 |1 | bar-1
6 |3 | foo-3 |2 | bar-2
(6 rows)
--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
On 4/8/06, Jan Wieck [EMAIL PROTECTED] wrote: Someone correct me if I'm wrong, but I was allways under the impression that Oracle's ROWNUM is a thing attached to a row in the final result set, whatever (possibly random) order that happens to have. Now a) this is something that IMHO belongs into the client or stored procedure code, b) if I am right, the code below will break as soon as an ORDER BY is added to the query and most importantly c) if a) cannot do the job, it indicates that the database schema or business process definition lacks some key/referential definition and is in need of a fix. Yes, a rownum is assigned at fetch time. An example is the following PostgreSQL query: SELECT id FROM sometable ORDER BY id LIMIT 5; In Oracle-land is written as: SELECT id FROM (SELECT id FROM sometable ORDER BY id) WHERE rownum = 5; My humble guess is that c) is also the reason why the ANSI didn't find a ROWNUM desirable. I believe this is a good assumption. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] ERROR: record type has not been registered on CVS head
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: While trying to help somebody on IRC with slow queries against information_schema i stumbled across the following EXPLAIN buglet (much reduced from the original one and does not make a lot of sense therefore): foo=# explain SELECT * FROM information_schema.constraint_column_usage JOIN information_schema.key_column_usage ON key_column_usage.constraint_name = constraint_column_usage.constraint_name; ERROR: record type has not been registered I've applied a patch for this in HEAD, but I'm a bit hesitant to back-patch it without more testing. Since the consequence of the bug is only inability to get EXPLAIN output, the most prudent course might be to leave it unfixed in 8.1. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
Jan Wieck [EMAIL PROTECTED] writes: My humble guess is that c) is also the reason why the ANSI didn't find a ROWNUM desirable. I've never understood what the conceptual model is for Oracle's rownum. Where along the SQL operational pipeline (FROM / WHERE / GROUP BY / aggregate / compute output columns / ORDER BY) is it supposed to be computed? To be useful for the often-requested purpose of nicely labeling output with line numbers, it'd have to be assigned post-ORDER-BY, but then it doesn't make any sense at all to use it in WHERE, nor in sub-selects. A function implemented as per Michael's example would not give the results that I think people would expect for SELECT rownum(), * FROM foo ORDER BY whatever; unless the planner chances to do the ordering with an indexscan. If it does it with a sort step then the rownums will be computed before sorting :-( regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
On 4/8/06, Tom Lane [EMAIL PROTECTED] wrote: A function implemented as per Michael's example would not give the results that I think people would expect for SELECT rownum(), * FROM foo ORDER BY whatever; Yep, the query would have to be rewritten similar to Oracle's: SELECT rownum(), * FROM (SELECT * FROM foo ORDER BY whatever); IIRC, processing-wise, rownum and order-by processing is handled as follows: SELECT id, whatever FROM foo WHERE rownum = 10 ORDER BY id; is the same as PostgreSQL's SELECT id, whatever FROM (SELECT id, whatever FROM foo LIMIT 10) ORDER BY id; -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Summer of Code -- mentors needed as well
Folks, As well as projects and students, we also will need mentors for Summer of Code projects. I'm not sure exactly what time commitment being a mentor involves, but I imagine that it's at least a couple dozen hours over the summer. Volunteers, please contact me. Note that we can have corporate mentors as well, I think. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] FOUND not set by EXECUTE?
I had expected the FOUND PL/pgSQL variable to be set by an UPDATE statement executed by an EXECUTE statement, but it doesn't appear to work: try=# drop table try; DROP TABLE try=# CREATE TABLE try ( try(# id integer try(# ); CREATE TABLE try=# INSERT INTO try VALUES (1); INSERT 0 1 try=# CREATE OR REPLACE FUNCTION try_me () RETURNS VOID AS $$ try$# DECLARE try$# rcount integer; try$# BEGIN try$#EXECUTE 'UPDATE try SET ID = 12'; try$#RAISE NOTICE 'Found: %', FOUND; try$#GET DIAGNOSTICS rcount = ROW_COUNT; try$#RAISE NOTICE 'Row Count: %', rcount; try$# END; try$# $$ LANGUAGE plpgsql SECURITY DEFINER; CREATE FUNCTION try=# SELECT try_me(); NOTICE: Found: f NOTICE: Row Count: 1 try_me (1 row) Note that FOUND is false, but the ROW_COUNT fetched by GET DIAGNOSTICS is set to 1. So shouldn't FOUND be true? Or does it just not work with EXECUTE and need to be documented as such? Or am I just missing something obvious? Thanks, David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Summer of Code -- mentors needed as well
On Sat, 2006-04-08 at 12:54 -0700, Josh Berkus wrote: As well as projects and students, we also will need mentors for Summer of Code projects. I'm not sure exactly what time commitment being a mentor involves, but I imagine that it's at least a couple dozen hours over the summer. Volunteers, please contact me. Note that we can have corporate mentors as well, I think. I think I will be able to do this, given some input into the choice of project and some reasonable match in timezone. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] FOUND not set by EXECUTE?
David Wheeler [EMAIL PROTECTED] writes: Note that FOUND is false, but the ROW_COUNT fetched by GET DIAGNOSTICS is set to 1. So shouldn't FOUND be true? Or does it just not work with EXECUTE and need to be documented as such? Or am I just missing something obvious? It *is* documented: the manual lists the statements that affect FOUND, and EXECUTE is not among them. Whether it should be is another question, but that's a definition disagreement (a/k/a enhancement proposal) not a bug. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Support Parallel Query Execution in Executor
Jonah H. Harris [EMAIL PROTECTED] writes: On 4/8/06, Markus Schiltknecht [EMAIL PROTECTED] wrote: Ehm.. which additional I/O cost? Or do you count inter-process communication to I/O? Inter-process will add a minimal amount, but if it's done correctly, you'll still end up ahead. This is exactly the bit of optimism I was questioning. We've already been sweating blood trying to reduce multiprocessor contention on data structures in which collisions ought to be avoidable (ie, buffer arrays where you hope not everyone is hitting the same buffer at once). I think passing large volumes of data between different processes is going to incur quite a lot of locking overhead, pipeline stalls for cache line transfers, etc, etc, because heavy contention for the transfer buffer is simply not going to be avoidable. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
On Sat, Apr 08, 2006 at 02:02:53PM -0400, Jan Wieck wrote: Someone correct me if I'm wrong, but I was allways under the impression that Oracle's ROWNUM is a thing attached to a row in the final result set, whatever (possibly random) order that happens to have. Now a) this is something that IMHO belongs into the client or stored procedure code, b) if I am right, the code below will break as soon as an ORDER BY is added to the query and most importantly c) if a) cannot do the job, it indicates that the database schema or business process definition lacks some key/referential definition and is in need of a fix. My humble guess is that c) is also the reason why the ANSI didn't find a ROWNUM desirable. Sadly, ANSI did just that. http://troels.arvin.dk/db/rdbms/#select-limit http://troels.arvin.dk/db/rdbms/#select-top-n http://troels.arvin.dk/db/rdbms/#select-limit-offset Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Support Parallel Query Execution in Executor
On 4/8/06, Tom Lane [EMAIL PROTECTED] wrote: passing large volumes of data between different processes is going to incur quite a lot of locking overhead, pipeline stalls for cache line transfers, etc, etc, because heavy contention for the transfer buffer is simply not going to be avoidable. I don't think anyone believes there isn't going to be contention somewhere. Anyone that's developed a parallel system acknowledges there's a price to pay with parallelism (in complexity) which requires good algorithms, strategy, and design. I may certainly be misunderstanding you, but it seems like you want to avoid parallelism altogether. Aside from PostgreSQL, the only systems I know of which don't support parallelism in query execution are MySQL and Firebird, but I don't see competing at the low end as a compelling argument. Similarly, I don't think every major vendor put money into developing something that isn't useful. I do agree that there are many times you won't want to have parallel query. The reasons for employing parallelism is very dependent on the environment and application it's used in, so it's certainly going to be up to the user to decide whether they want to use it or not. We're currently playing with a proof-of-concept to find issues before proposing a design. This doesn't affect the community in any way. Rather than negativity, I'd really like to see your suggestions on avoiding contention as much as possible; your ideas may certainly get us past several obstacles more quickly. -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] How to implement oracle like rownum(function or seudocolumn)
On 4/8/06, David Fetter [EMAIL PROTECTED] wrote: Sadly, ANSI did just that. Thanks for pointing that out... I'd been using LIMIT/OFFSET for so long, I totally forgot about the standard :( -- Jonah H. Harris, Database Internals Architect EnterpriseDB Corporation 732.331.1324 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Support Parallel Query Execution in Executor
On 4/8/06, Tom Lane [EMAIL PROTECTED] wrote: This is exactly the bit of optimism I was questioning. We've already been sweating blood trying to reduce multiprocessor contention on data structures in which collisions ought to be avoidable (ie, buffer arrays where you hope not everyone is hitting the same buffer at once). I think passing large volumes of data between different processes is going to incur quite a lot of locking overhead, pipeline stalls for cache line transfers, etc, etc, because heavy contention for the transfer buffer is simply not going to be avoidable. We should consider true parallel execution and overlapping execution with I/O as distinct cases. For example, one case made in this thread involved bursty performance with seqscans presumably because the I/O was stalling while processing was being performed. In general this can be avoided without parallel execution through the use of non-blocking I/O and making an effort to keep the request pipeline full. There are other cases where it is useful to perform parallel I/O without parallel processing.. for example: a query that will perform an index lookup per row can benefit from running some number of those lookups in parallel in order to hide the lookup latency and give the OS and disk elevators a chance to make the random accesses a little more orderly. This can be accomplished without true parallel processing. (Perhaps PG does this already?) Parallel execution to get access to more CPU and memory bandwidth is a fine thing, and worth the costs in many cases... but it shouldn't be used as an easy way to get parallel IO without careful consideration. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] FOUND not set by EXECUTE?
On Apr 8, 2006, at 14:38, Tom Lane wrote: It *is* documented: the manual lists the statements that affect FOUND, and EXECUTE is not among them. Whether it should be is another question, but that's a definition disagreement (a/k/a enhancement proposal) not a bug. I think that: a. It should be (it'd be very useful, without a doubt). b. Until it is, the docs should explicitly mention that EXECUTE does not affect found. No, EXECUTE is not in the list, and UPDATE, INSERT, and DELETE are, and although I'm using them in an EXECUTE statement rather than directly in the PL/pgSQL, it still seemed rather confusing, because they're still UPDATE, INSERT, and DELETE. So yes, it's a definition disagreement, but I think that things could be clearer. Thanks, David ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Support Parallel Query Execution in Executor
Greg, On 4/8/06 5:43 PM, Gregory Maxwell [EMAIL PROTECTED] wrote: For example, one case made in this thread involved bursty performance with seqscans presumably because the I/O was stalling while processing was being performed. In general this can be avoided without parallel execution through the use of non-blocking I/O and making an effort to keep the request pipeline full. I agree - there is a real continuing need for overlapped scan and execution, though I'm not sure quite how to get it efficiently using inter-process communication. The AIO interface is nicely proven at this point, but to Tom's point, it's non-portable. What we see now is that the executor is CPU bound at about 300MB/s scan rate using modern Opteron CPUs. Using some kind of overlap strategy would help this a lot - using programmable readahead to stage I/O requests asynchronously from the scan node would be better than OS hints like fadvise(), but the tuning would be tricky IMO. - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Fat binaries for OS X (was Re: [GENERAL] Postgres Library natively available for Mac OSX Intel?)
Philipp Ott [EMAIL PROTECTED] writes: Currently 8.1.3 compiles and runs just fine on OSX 10.4.6 + XCode 2.2.1, but generates binaries just for the current host architecture. Now when I add -arch i386 -arch ppc to CFLAGS and LDFLAGS for configure, then it compiles everything just fine, however at linking stage I get various problems for missing architecture files. I looked into this and found that the problem is our habit of using ld -r to aggregate multiple .o files into a single SUBSYS.o file that's still relocatable. The Darwin version of ld is pretty brain-dead when it comes to fat files containing code for more than one architecture --- the man page says UNIVERSAL FILE SUPPORT The link editor accepts ``universal'' (multiple-architecture) input files, but always creates a ``thin'' (single-architecture), standard Mach-O output file. The architecture is specified using the -arch arch_type option. If this option is not used, ld(1) attempts to deter- mine the output architecture by examining the first object file encoun- tered on the command line. If it is a ``thin'' file, its architecture determines that of the output file. If the first input file is a ``universal'' file, the ``best'' architecture for the host is used. (See the explanation of the -arch option, below.) The compiler driver cc(1) handles creating universal executables by calling ld(1) multiple times and using lipo(1) to create a ``univer- sal'' file from the results of the ld(1) executions. So what you're seeing is that one of the arches has been dropped from the SUBSYS.o files. Unfortunately, there doesn't seem to be any way to use cc/gcc to emulate ld -r, in the sense of just combining multiple fat .o files into one fat .o file. At least I couldn't see one after perusing the man page for a bit. I also found out that lipo(1) is not by itself smart enough to do this. So it looks like you'd have to write a small shell script to do what the above snippet describes cc as doing. Not out of the question by any means, but still a PITA. Any Apple experts around who know a better answer? Is Apple likely to improve this situation in the near future? BTW, our configure script is not real flexible about adjusting the command used to produce the SUBSYS.o files ... if you want anything except $(LD) -r -o SUBSYS.o *.o, you have to edit Makefile.global after configuring. But without having a solution that actually works for multi-arch Darwin, I'm not seeing the point of improving that yet. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Get explain output of postgresql in Tables
Jim C. Nasby wrote: On Fri, Mar 24, 2006 at 07:54:09AM +0900, Satoshi Nagayasu wrote: Jim C. Nasby wrote: Structure for the human-consumable output or for something that would be machine-parsed? ISTM it would be best to keep the current output as-is, and provide some other means for producing machine-friendly output, presumably in a table format. How about (well-formed) XML format? Anyone menthioned in the past threads? I guess XML is good for the explain structure. Unless you want to actually analyze the output in something like plpgsql, but I can certainly see uses for both. Perhaps getting one implimented will make it easier to implement the other. TODO has: * Allow EXPLAIN output to be more easily processed by scripts -- Bruce Momjian http://candle.pha.pa.us + If your life is a hard drive, Christ can be your backup. + ---(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] How to implement oracle like rownum(function or seudocolumn)
I need a rownum column, like Oracle. I have searched the mailing lists and I don't see a satisfactory solution, so I was wondering write a UDF to implement it, the requirements are: +1 I would _love_ to see rownums in PostgreSQL :) Chris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Support Parallel Query Execution in Executor
Gregory Maxwell wrote: We should consider true parallel execution and overlapping execution with I/O as distinct cases. For example, one case made in this thread involved bursty performance with seqscans presumably because the I/O was stalling while processing was being performed. In general this can be avoided without parallel execution through the use of non-blocking I/O and making an effort to keep the request pipeline full. There are other cases where it is useful to perform parallel I/O without parallel processing.. for example: a query that will perform an index lookup per row can benefit from running some number of those lookups in parallel in order to hide the lookup latency and give the OS and disk elevators a chance to make the random accesses a little more orderly. This can be accomplished without true parallel processing. (Perhaps PG does this already?) I have done some testing more along these lines with an old fork of postgres code (2001). In my tests, I used a thread to delegate out the actual heap scan of the SeqScan. The job of the slave thread the was to fault in buffer pages and determine the time validity of the tuples. ItemPointers are passed back to the master thread via a common memory area guarded by mutex locking. The master thread is then responsible for converting the ItemPointers to HeapTuples and finishing the execution run. I added a little hack to the buffer code to force pages read into the buffer to stay at the back of the free buffer list until the master thread has had a chance to use it. These are the parameters of my test table. Pages 9459: ; Tup 961187: Live 673029, Dead 288158 Average tuple size is 70 bytes create table test (rand int, varchar(256) message) So far I've done a couple of runs with a single query on a 2 processor machine with the following results via dtrace. select * from test; CPU IDFUNCTION:NAME 1 46218ExecEndSeqScan:return Inline scan time 81729 0 46216 ExecEndDelegatedSeqScan:return Delegated scan time 59903 0 46218ExecEndSeqScan:return Inline scan time 95708 0 46216 ExecEndDelegatedSeqScan:return Delegated scan time 58255 0 46218ExecEndSeqScan:return Inline scan time 79028 0 46216 ExecEndDelegatedSeqScan:return Delegated scan time 50500 average 34% decrease in total time using the delegated scan. A very crude, simple test but I think it shows some promise. I know I used threads but you could probably just as easily use a slave process and pass ItemPointers via pipes or shared memory. Thanks, Myron Scott ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Support Parallel Query Execution in Executor
Myron, First, this sounds really good! On 4/8/06 9:54 PM, Myron Scott [EMAIL PROTECTED] wrote: I added a little hack to the buffer code to force pages read into the buffer to stay at the back of the free buffer list until the master thread has had a chance to use it. This is the part I'm curious about - is this using the shared_buffers region in a circular buffer fashion to store pre-fetched pages? One thing I've wondered about is: how much memory is required to get efficient overlap? Did you find that you had to tune the amount of buffer memory to get the performance to work out? - Luke ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
