Re: [HACKERS] Multi CPU Queries - Feedback and/or suggestions wanted!
Jonah H. Harris wrote: fadvise is a kludge. I don't think it's a kludge at all. posix_fadvise() is a pretty nice and clean interface to hint the kernel what pages you're going to access in the near future. I can't immediately come up with a cleaner interface to do that. Compared to async I/O, it's helluva lot simpler to add a few posix_fadvise() calls to an application, than switch to a completely different paradigm. And while posix_fadvise() is just a hint, allowing the OS to prioritize accordingly, all async I/O requests look the same. While it will help, it still makes us completely reliant on the OS. That's not a bad thing in my opinion. The OS knows the I/O hardware, disk layout, utilization, and so forth, and is in a much better position to do I/O scheduling than a user process. The only advantage a user process has is that it knows better what pages it's going to need, and posix_fadvise() is a good interface to let the user process tell the kernel that. IIRC, we currently have support for rings in the buffer pool, which we could read directly into. The rings won't help you a bit. It's just a different way to choose victim buffers. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Multi CPU Queries - Feedback and/or suggestions wanted!
Jonah H. Harris wrote: On Thu, Oct 23, 2008 at 8:44 PM, Bruce Momjian [EMAIL PROTECTED] wrote: True, it is a kludge but if it gives us 95% of the benfit with 10% of the code, it is a win. I'd say, optimistically, maybe 30-45% the benefit over a proper multi-block read using O_DIRECT. Let's try to focus. We're not talking about using O_DIRECT, we're talking about using asynchronous I/O or posix_fadvise(). And without more details on what you mean by benefit, under what circumstances, any numbers like that is just unmeasurable handwaving. In terms of getting the RAID array busy, in Greg's tests posix_fadvise() on Linux worked just as well as async I/O works on Solaris. So it doesn't seem like there's any inherent performance advantage in the async I/O interface over posix_fadvise() + read(), There is differences between different OS implementations of the interfaces. But we're developing software for the future, and for a wide range of platforms, and I'm sure operating systems will develop as well. The decision should not be made on what is the fastest interface on a given operating system in 2008. Async I/O might have a small potential edge on CPU usage, because less system calls are needed. However, let me remind you all that we're talking about how to utilize RAID array to do physical, random, I/O as fast as possible. IOW, the bottleneck is I/O, by definition. The CPU efficiency of the kernel interface to initiate the I/O is insignificant, until we reach a large enough random read throughput to saturate the CPU, and even then there's probably more significant CPU savings to be made elsewhere. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- 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] Any reason to have heap_(de)formtuple?
Kris Jurka napsal(a): The problem with trying to deprecate it is that the vast majority of the backend is still using the old interfaces, so people looking for inspiration for their external modules will likely end up using the old interface. Like Alvaro I started this conversion a while ago, got bored, and forgot about it. If people do want this conversion done while keeping the old interface around, I can track down that patch, update it and finish it up for the next CommitFest. Yes, Please. thanks -- Zdenek Kotala Sun Microsystems Prague, Czech Republic http://sun.com/postgresql -- 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] SE-PostgreSQL wiki updates, but ...
KaiGai Kohei wrote: Simon Riggs wrote: On Tue, 2008-10-21 at 18:48 +0900, KaiGai Kohei wrote: I started to rework the SE-PostgreSQL documentation to catch up the latest implementation, because the existing PDF documents are a bit legacy to be updated. In addition, I moved them to wiki site for easier future updates. http://code.google.com/p/sepgsql/wiki/TheSepgsqlDocument I've forwarded this on as promised, with request for comments. I'm now reworking it at: http://wiki.postgresql.org/wiki/SEPostgreSQL Now, I completed the chapter 1, 2 and 6, and working for the chapter 3. If you can comment anything in this state, please feel free to tell me. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- 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] Multi CPU Queries - Feedback and/or suggestions wanted!
On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: While we could build an abstract prefetch interface and simply use fadvise for it now (rather than OS-specific code), I don't see an easy win in any case. When building an abstract interface, always use at least two implementations (I guess that would be fadvise on linux and AIO on solaris in this case). You are much more likely to get the interface right this way. -- Hannu -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgres-R pacth
Hi All, I would like to ask a help to Postgres-R... because have tried to use it with any version from CVS and normal Postgres source code but could not do patch it. What kind of form or method must be used to patch that it can work correctly? Thanks. Imre
[HACKERS] tribble.postgresql.org maintenance/hardware replacement
there will be planned maintenance today for tribble.postgresql.org starting 15:30 CEST today (24.10.2008) affecting the following services: cvs.postgresql.org wwwmaster.postgresql.org www.pgadmin.org doxygen.postgresql.org wiki.postgresql.org tribble had some unexpected downtimes lately so I'm going to do a full hardware replacement in that time. Stefan -- 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] Multi CPU Queries - Feedback and/or suggestions wanted!
On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing [EMAIL PROTECTED] wrote: On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: While we could build an abstract prefetch interface and simply use fadvise for it now (rather than OS-specific code), I don't see an easy win in any case. When building an abstract interface, always use at least two implementations (I guess that would be fadvise on linux and AIO on solaris in this case). You are much more likely to get the interface right this way. I agree, I just wasn't sure as to whether Greg's patch supported both methods. -- Jonah H. Harris, Senior DBA myYearbook.com -- 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] Multi CPU Queries - Feedback and/or suggestions wanted!
Jonah H. Harris wrote: On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing [EMAIL PROTECTED] wrote: On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: While we could build an abstract prefetch interface and simply use fadvise for it now (rather than OS-specific code), I don't see an easy win in any case. When building an abstract interface, always use at least two implementations (I guess that would be fadvise on linux and AIO on solaris in this case). You are much more likely to get the interface right this way. I agree, I just wasn't sure as to whether Greg's patch supported both methods. It does not, and probably will not for the near future; we can only hope Solaris suports posix_fadvise() at some point. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] [pgsql-www] tribble.postgresql.org maintenance/hardware replacement
Stefan Kaltenbrunner wrote: there will be planned maintenance today for tribble.postgresql.org starting 15:30 CEST today (24.10.2008) affecting the following services: cvs.postgresql.org wwwmaster.postgresql.org www.pgadmin.org doxygen.postgresql.org wiki.postgresql.org tribble had some unexpected downtimes lately so I'm going to do a full hardware replacement in that time. hardware replaced and all services are up and running again. Stefan -- 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] Multi CPU Queries - Feedback and/or suggestions wanted!
I thought about how to support both and ran into probblems that would make the resulting solutions quite complex. In the libaio view of the world you initiate io and either get a callback or call another syscall to test if it's complete. Either approach has problems for postgres. If the process that initiated io is in the middle of a long query it might takr a long time ot even never get back to complete the io. The callbacks use threads... And polling for completion has the problem that another process could be waiting on the io and can't issue a read as long as the first process has the buffer locked and io in progress. I think aio makes a lot more sense if you're using threads so you can start a thread to wait for the io to complete. Actually I think it might be doable with a lot of work but I'm worried that it would be a lot of extra complexity even when you're not using it. The current patch doesn't change anything when you're not using it and actually is quite simple. greg On 24 Oct 2008, at 03:18 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Jonah H. Harris wrote: On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing [EMAIL PROTECTED] wrote: On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: While we could build an abstract prefetch interface and simply use fadvise for it now (rather than OS-specific code), I don't see an easy win in any case. When building an abstract interface, always use at least two implementations (I guess that would be fadvise on linux and AIO on solaris in this case). You are much more likely to get the interface right this way. I agree, I just wasn't sure as to whether Greg's patch supported both methods. It does not, and probably will not for the near future; we can only hope Solaris suports posix_fadvise() at some point. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Multi CPU Queries - Feedback and/or suggestions wanted!
Also keep in mind that solaris is open source these days. If someone wants it they could always go ahead and add the feature ... greg On 24 Oct 2008, at 03:18 PM, Bruce Momjian [EMAIL PROTECTED] wrote: Jonah H. Harris wrote: On Fri, Oct 24, 2008 at 7:59 AM, Hannu Krosing [EMAIL PROTECTED] wrote: On Fri, 2008-10-24 at 00:52 -0400, Jonah H. Harris wrote: While we could build an abstract prefetch interface and simply use fadvise for it now (rather than OS-specific code), I don't see an easy win in any case. When building an abstract interface, always use at least two implementations (I guess that would be fadvise on linux and AIO on solaris in this case). You are much more likely to get the interface right this way. I agree, I just wasn't sure as to whether Greg's patch supported both methods. It does not, and probably will not for the near future; we can only hope Solaris suports posix_fadvise() at some point. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Multi CPU Queries - Feedback and/or suggestions wanted!
* Greg Stark [EMAIL PROTECTED] [081024 10:48]: I thought about how to support both and ran into probblems that would make the resulting solutions quite complex. In the libaio view of the world you initiate io and either get a callback or call another syscall to test if it's complete. Either approach has problems for postgres. If the process that initiated io is in the middle of a long query it might takr a long time ot even never get back to complete the io. The callbacks use threads... And polling for completion has the problem that another process could be waiting on the io and can't issue a read as long as the first process has the buffer locked and io in progress. I think aio makes a lot more sense if you're using threads so you can start a thread to wait for the io to complete. Actually I think it might be doable with a lot of work but I'm worried that it would be a lot of extra complexity even when you're not using it. The current patch doesn't change anything when you're not using it and actually is quite simple. In the Solaris async IO, are you bound by direct IO? Does the OS page-cache still get primed by async reads? If so, how about starting async IO into a throwaway local buffer; treat async IO in the same way as fadvise, a pre-load the OS page cache so the real read is quick. Sure, I understand it's not the perfect model, but it I don't see PostgreSQL being refactored enough to have a pure async model happening any time in the near future... -- Aidan Van Dyk Create like a god, [EMAIL PROTECTED] command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] Making pg_standby compression-friendly
In the absence of further feedback from 'yall (and in the presence of some positive results from internal QA), I'm adding the posted patch as-is to the 2008-11 CommitFest queue. That said, any such additional feedback would be gratefully appreciated. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SPI cursor functions
The docs for FETCH indicate you can fetch from the current row using FORWARD 0, but SPI_scroll_cursor_fetch(someportal, FETCH_FORWARD, 0); fails for me. Is there a way to emulate FETCH FORWARD 0 with SPI? Also, I noticed that SPI_scroll_cursor_move(someportal, FETCH_BACKWARD, 1); sets SPI_processed to 0, not 1. Is that the intended behavior? How does one check for a failed backwards move? Thanks. THK -- Timothy H. Keitt University of Texas at Austin http://www.keittlab.org/ Contact info and schedule at http://www.keittlab.org/~tkeitt/ Publications http://www.keittlab.org/biblio -- 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] WIP : change tablespace for a database
Tom Lane a écrit : Guillaume Lelarge [EMAIL PROTECTED] writes: To get the list of relations to move, the user needs to be connected to the database. Why? If what you are doing is changing the database's default tablespace (which IMHO is what such a command ought to do) That's exactly what I'm trying to do. then all you have to do is bulk-copy the per-DB subdirectory from the old default tablespace to the new one. There's no reason to think about it at the individual-relation level, and there won't be any change to the contents of any catalog in the DB either (only its pg_database row will change). So, I should be doing something like this: * check various stuff (like permission and the fact that no-one is connected on the target database) * lock the database * read the default tablespace dir (AllocateDir, ReadDir) * move each file in it to the target tablespace (copydir, rmtree) * change the default tablespace in pg_database My current patch works well with this simple script: [EMAIL PROTECTED] psql postgres psql (8.4devel) Type help for help. postgres=# create database db1; CREATE DATABASE postgres=# \c db1 psql (8.4devel) You are now connected to database db1. db1=# create tablespace ts1 db1-# location '/home/guillaume/postgresql_tblspc'; CREATE TABLESPACE db1=# create table t1(id int4); CREATE TABLE db1=# insert into t1 values (1); INSERT 0 1 db1=# \c postgres psql (8.4devel) You are now connected to database postgres. postgres=# alter database db1 tablespace ts1; NOTICE: alter tablespace db1 set tablespace ts1! NOTICE: move base/16384 to pg_tblspc/16385/16384 NOTICE: remove base/16384 ALTER DATABASE postgres=# \c db1 psql (8.4devel) You are now connected to database db1. db1=# \d List of relations Schema | Name | Type | Owner +--+---+--- public | t1 | table | guillaume (1 row) db1=# select datname, dattablespace from pg_database db1-# where datname='db1'; datname | dattablespace -+--- db1 | 16385 (1 row) db1=# select relname, relfilenode, reltablespace from pg_class db1-# where relname='t1'; relname | relfilenode | reltablespace -+-+--- t1 | 16386 | 0 (1 row) So, it seems to work. I say seems because there's no XLOG record that says I moved all relations from one tablespace to another. Am I right in thinking I need to insert a new XLOG record? should I create a new one? Thanks. -- Guillaume. http://www.postgresqlfr.org http://dalibo.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Handling NULL records in plpgsql
Was anything ever done with http://archives.postgresql.org/pgsql- hackers/2008-09/msg01758.php ? I have a different issue I'm seeing, but I think it's related, and ISTM it's a bug (on 8.2.10): SELECT INTO v_prev * FROM installments_static WHERE id = (SELECT id FROM installments_static i WHERE i.loan_id = NEW.loan_id AND i.installment_number NEW.installment_number ORDER BY installment_number DESC LIMIT 1 ) ; ... RAISE DEBUG $$Previous installment: id = %, due_date = % Current installment: id = %, number = %, loan_id = %, installment_date = %, due_date = % Next installment: id = %, installment_date = % v_prev IS NOT NULL = %, v_prev IS NULL = % v_next IS NOT NULL = %, v_next IS NULL = %$$ , v_prev.id, v_prev.due_date , NEW.id, NEW.installment_number, NEW.loan_id, NEW.installment_date, NEW.due_date , v_next.id, v_next.installment_date , v_prev IS NOT NULL, v_prev IS NULL , v_next IS NOT NULL, v_next IS NULL ; psql:sql/installments_static.sql:XX: DEBUG: Previous installment: id = 5, due_date = -XX-XX Current installment: id = 8, number = 2, loan_id = 3, installment_date = -XX-XX, due_date = -XX-XX Next installment: id = NULL, installment_date = NULL v_prev IS NOT NULL = f, v_prev IS NULL = f -- v_prev is actually set! v_next IS NOT NULL = f, v_next IS NULL = t -- v_next is unset, ie: NULL If I change v_* IS NOT NULL to NOT v_* IS NULL everything's ok: psql:sql/installments_static.sql:XX: DEBUG: Previous installment: id = 5, due_date = -XX-XX Current installment: id = 8, number = 2, loan_id = 3, installment_date = -XX-XX, due_date = -XX-XX Next installment: id = NULL, installment_date = NULL NOT v_prev IS NULL = t, v_prev IS NULL = f NOT v_next IS NULL = f, v_next IS NULL = t -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] WIP : change tablespace for a database
Guillaume Lelarge [EMAIL PROTECTED] writes: So, it seems to work. I say seems because there's no XLOG record that says I moved all relations from one tablespace to another. Am I right in thinking I need to insert a new XLOG record? should I create a new one? You certainly need to do *something* about that. But are you sure there aren't any existing record types that will work? Look at CREATE/DROP DATABASE. regards, tom lane -- 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] Handling NULL records in plpgsql
Decibel! [EMAIL PROTECTED] writes: Was anything ever done with http://archives.postgresql.org/pgsql-hackers/2008-09/msg01758.php ? No, we got stalled on what the behavior really ought to be: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01772.php I have a different issue I'm seeing, but I think it's related, and ISTM it's a bug (on 8.2.10): It's entirely possible for a row variable to be in a state where neither IS NULL nor IS NOT NULL is true. RTFM (under Comparison Operators) or see the SQL spec. regards, tom lane -- 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] SPI cursor functions
Tim Keitt [EMAIL PROTECTED] writes: The docs for FETCH indicate you can fetch from the current row using FORWARD 0, but SPI_scroll_cursor_fetch(someportal, FETCH_FORWARD, 0); fails for me. Is there a way to emulate FETCH FORWARD 0 with SPI? Fails how? AFAICS that calls the same code that the SQL-level FETCH command would. You do realize that FORWARD 0 means to re-fetch the current row, ie the most recently fetched row? There isn't any current row in the initial state of a cursor. Also, I noticed that SPI_scroll_cursor_move(someportal, FETCH_BACKWARD, 1); sets SPI_processed to 0, not 1. Is that the intended behavior? Well, it is if you don't have any row to move backward to ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] ERROR: cache lookup failed for function 0
[Re-sending to hackers, since the original message to pgsql-bugs has
not been approved for delivery in the last four days…apologies for any
duplicates.]
Howdy,
I ran into this error on 8.2 a while ago, and just figured out what
was causing it. Here's a quick example on 8.2:
BEGIN;
-- Compare name[]s more or less like 8.3 does.
CREATE OR REPLACE FUNCTION namearray_text(name[])
RETURNS TEXT AS 'SELECT textin(array_out($1));'
LANGUAGE sql IMMUTABLE STRICT;
CREATE CAST (name[] AS text) WITH FUNCTION namearray_text(name[]) AS
IMPLICIT;
CREATE OR REPLACE FUNCTION namearray_eq( name[], name[] )
RETURNS bool
AS 'SELECT $1::text = $2::text;'
LANGUAGE sql IMMUTABLE STRICT;
CREATE OPERATOR = (
LEFTARG= name[],
RIGHTARG = name[],
NEGATOR= ,
PROCEDURE = namearray_eq
);
SELECT '{foo}'::name[] '{bar}'::name[];
ROLLBACK;
If you comment out the NEGATOR line, the error is changed to the more
useful
ERROR: operator is not unique: name[] name[]
I'm assuming that, if you did this for 8.3 (which has name[]
comparison operators in core, so it'd have to be an operator with some
other type), you'd get the same useless error.
Ideally, in the situation where a NEGATOR (or commutator, too?) is
specified but has not actually been defined, you'd get an error such as:
ERROR: operator not defined: name[] name[]
Thanks,
David
--
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] Handling NULL records in plpgsql
On Oct 24, 2008, at 7:19 PM, Tom Lane wrote: Decibel! [EMAIL PROTECTED] writes: Was anything ever done with http://archives.postgresql.org/pgsql- hackers/2008-09/msg01758.php ? No, we got stalled on what the behavior really ought to be: http://archives.postgresql.org/pgsql-hackers/2008-09/msg01772.php I have a different issue I'm seeing, but I think it's related, and ISTM it's a bug (on 8.2.10): It's entirely possible for a row variable to be in a state where neither IS NULL nor IS NOT NULL is true. RTFM (under Comparison Operators) or see the SQL spec. Ahh, I finally saw the bit that talks about it. I really think we should have a way of telling if a array/row/record variable is actually set to something, and I'm pretty sure that should be unrelated to whether all the elements in it happen to be null. And the IS NOT NULL case seems exceptionally broken. I think it's extremely confusing to have it behave differently than NOT blah IS NULL. This puts us in an ugly position. Do we break with spec? Or should we come up with a different construct (IS [NOT] DEFINED?)? I'm disinclined to just leave it as-is, because I think it's pretty common for people to want to see if a variable is set or not. I'm inclined towards DEFINED, as ugly as it is, so that we're not breaking the spec. To answer the questions in that thread, I would say that a record containing all nulls is still a distinct record. It was set to something, it just happens that that something contained all nulls. That's definitely not the same as it being set to nothing. Consider: CREATE TABLE moo(a int, b int, c int); CREATE TABLE cow(LIKE moo); INSERT INTO moo SELECT NULL, NULL, NULL FROM generate_series(1,10) i; SELECT count(*) FROM moo; SELECT count(*) FROM cow; SELECT INTO rowvar_a * FROM moo LIMIT 1; SELECT INTO rowvar_b * FROM cow LIMIT 1; I would argue that rowvar_b IS NOT NULL should be false and rowvar_a IS NOT NULL should be true. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] Handling NULL records in plpgsql
Decibel! [EMAIL PROTECTED] writes: ... I would argue that rowvar_b IS NOT NULL should be false and rowvar_a IS NOT NULL should be true. While I don't necessarily disagree with you about what is sane, what is required by the SQL spec seems pretty clear here. regards, tom lane -- 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] Regression in IN( field, field, field ) performance
On Oct 23, 2008, at 11:16 AM, Tom Lane wrote: Gregory Stark [EMAIL PROTECTED] writes: Tom Lane [EMAIL PROTECTED] writes: Works fine for me, eg I think he's looking for something like: 5 IN (col1,col2,col3) resulting in a bitmap or of three index scans of three different indexes on col1, col2, and col3. Ah, I see. It would be easy to make transformAExprIn() generate an OR tree instead of = ANY(ARRAY[]), if we could figure out the conditions where an OR tree is superior. I'm not sure it's easy to tell though. Is it sufficient to do this when there are Vars on the right side and none on the left? There's 6 cases here, in a 2x3 array. In one dimension, the LHS can be either a Var or a fixed value. In the other dimension, the three possibilities are 1: everything on the RHS is a fixed value, 2: some fixed, some not, 3: everything on the RHS is a variable: 1 2 3 -- Right Hand Side --- A: LHS fixed All fixed Mixture All var. B: LHS var. All fixed Mixture All var. For A2 and A3, an OR is probably best. There's no way I can think of to optimize A3 with an array, and with A2 you could get lucky and hit something like 1 = 1. Hopefully the planner would check all the fixed cases first. For A1, an array might be best; it depends on if it's cheaper to build a huge OR clause and evaluate, or to iterate through the array, and that could depend on the number of terms. B1 might actually be similar to A1... was testing done to see if ORs were faster for a small number of elements? For B3, the only use-case I can think of is comparing fields within a record, and I can't see that resulting in a really large number of terms (which would presumabbly favor an array). But if you turned it into ORs, the planner could decide that it's better to use an index on some/all of the terms on the RHS. That could end up being far faster than using an array. An example would be field_in_small_table IN ( field_a_in_large_table, field_b_in_large_table, field_c_in_large_table ). One final note: A2 and B2 could be treated as a combination. Treat all the RHS fixed values as you would A1/B1, treat all the RHS variables as you would A3/B3, and OR the results. Ideally, the planner would understand the costs associated with how many terms are involved and would act accordingly. But I don't know that we can make it accurate enough to do that. I think that the A3 and B3 cases should always be OR'd. Treating as an array just ties the planner's hands too much. Presumably A1/B1 should be done with arrays, otherwise we wouldn't have moved away from ORs to begin with. That leaves the mixed RHS case. If it's cheap to just split things into two piles (fixed RHS vs variable RHS) then that's probably the way to go. Ideally, each condition would then be estimated separately, and the executor would favor executing the cheaper one first. -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
Re: [HACKERS] minimal update
On Oct 22, 2008, at 1:43 PM, Andrew Dunstan wrote: + if (!CALLED_AS_TRIGGER(fcinfo)) + elog(ERROR, min_update_trigger: not called by trigger manager); The error I get in 8.2 when calling a trigger function directly is: ERROR: trigger functions may only be called as triggers To stay consistent, I think the remaining errors should s/: not/ may only be/, ie: min_update_trigger may only be called on update + /* and that it's called on update */ + if (! TRIGGER_FIRED_BY_UPDATE(trigdata-tg_event)) + elog(ERROR, min_update_trigger: not called on update); + + /* and that it's called before update */ + if (! TRIGGER_FIRED_BEFORE(trigdata-tg_event)) + elog(ERROR, min_update_trigger: not called before update); + + /* and that it's called for each row */ + if (! TRIGGER_FIRED_FOR_ROW(trigdata-tg_event)) + elog(ERROR, min_update_trigger: not called for each row); -- Decibel!, aka Jim C. Nasby, Database Architect [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 smime.p7s Description: S/MIME cryptographic signature
