[HACKERS] XLogFlush
Maybe this is one of those things that is obvious when someone points
it out to you, but right now I am not seeing it. If you look at the
last eight lines of this snippet from XLogFlush, you see that if we
obtain WriteRqstPtr under the WALInsertLock, then we both write and
flush up to the highest write request. But if we obtain it under the
info_lck, then we write up to the highest write request but flush only
up to our own records flush request. Why the disparate treatment?
The effect of this seems to be that when WALInsertLock is busy, group
commits are suppressed.
if (LWLockConditionalAcquire(WALInsertLock, LW_EXCLUSIVE))
{
XLogCtlInsert *Insert = XLogCtl-Insert;
uint32 freespace = INSERT_FREESPACE(Insert);
if (freespace SizeOfXLogRecord) /*
buffer is full */
WriteRqstPtr = XLogCtl-xlblocks[Insert-curridx];
else
{
WriteRqstPtr = XLogCtl-xlblocks[Insert-curridx];
WriteRqstPtr.xrecoff -= freespace;
}
LWLockRelease(WALInsertLock);
WriteRqst.Write = WriteRqstPtr;
WriteRqst.Flush = WriteRqstPtr;
}
else
{
WriteRqst.Write = WriteRqstPtr;
WriteRqst.Flush = record;
}
Cheers,
Jeff
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Geometric Elimination
Trying to solve this problem by using a process of elimination. All
works fine until the comment below is removed.
ALTER OPERATOR FAMILY box_ops USING GiST ADD
OPERATOR 1(box,point),
OPERATOR 2(box,point),
OPERATOR 3(box,point),
OPERATOR 4(box,point),
OPERATOR 5(box,point),
-- OPERATOR 7 @ (box,point),
OPERATOR 9 | (box,point),
OPERATOR 10 | (box,point),
OPERATOR 11 | (box,point);
Ah! So operator @ is wrong.
DROP OPERATOR IF EXISTS @(box,point);
CREATE OPERATOR @ (
LEFTARG= box,
RIGHTARG = point,
PROCEDURE = contains,
COMMUTATOR = @,
RESTRICT = contsel,
JOIN = contjoinsel
);
No, all seems fine here. Maybe the definition of the function is incorrect
CREATE OR REPLACE FUNCTION contains(box,point) RETURNS boolean
LANGUAGE C IMMUTABLE STRICT
AS 'contains.so', 'box_point_contains';
The C function? No it seems OK as well. What am I missing? It must be
completely obvious. Someone is laughing out there. Put me out of my
misery please!
/*
* Box contains point. box @ point.
*/
Datum box_point_contains(PG_FUNCTION_ARGS)
{
BOX *box = PG_GETARG_BOX_P(0);
Point *point = PG_GETARG_POINT_P(1);
intisin = point-x = box-low.x
point-x = box-high.x
point-y = box-low.y
point-y = box-high.y;
PG_RETURN_BOOL(isin);
}
--
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] Geometric Elimination
On Fri, Aug 21, 2009 at 08:28:05PM +1000, Paul Matthews wrote: Trying to solve this problem by using a process of elimination. All works fine until the comment below is removed. I haven't completely understood what you're trying to do, but I have a few points. - I don't see any definition of an operator class, just the family, which doesn't seem to make any sense to me. - Does it work if you replace the use of the operator with the equivalent function call (contains)? - Check for differences in the explain output, that should reveal any implicit casts that may be getting in your way. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] WIP: generalized index constraints
Hi, Le 21 août 09 à 06:04, Jeff Davis a écrit : There is not much of a problem with backwards compatibility. LIKE is shorthand (not stored in catalogs), so it doesn't affect pg_dump/restore. And hopefully there aren't a lot of apps out there creating tables dynamically using the LIKE syntax. I for one use this a lot, every time I'm doing partitioning. What I do is a plpgsql function creating partitions for a given period (create_parts(date, date) and default interval with create_parts(date)), and the function will EXECUTE something like this: CREATE TABLE schema.partition_MM ( LIKE schema.parent INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING CONSTRAINTS, CHECK ( partition check expression ) ) INHERITS( schema.parent ); The reason to do this is that inherits won't care at all about the indexes, defaults and constraints. The drawback to doing it this way is the cheer number of NOTICEs you get back at inherits time when PG is so verbose about finding that child already has all the parents columns. From 8.3 onwards it's possible to trick the system though: CREATE FUNCTION ... () RETURNS ... LANGUAGE plpgsql SET client_min_messages TO warning AS $$ $$; Regards, -- dim -- 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] Geometric Elimination
Martijn van Oosterhout wrote: I haven't completely understood what you're trying to do Putting in place the missing 'box op point' and 'point op box' operators. The problematic queries are at the bottom of the email. - I don't see any definition of an operator class, just the family, which doesn't seem to make any sense to me. I am working on the assumption that the Box and Point class already have operator classes. Otherwise how would all the existing Box and Point operators work? From my limited understanding of the source code it's in postgres.bki round about line 2208 and 2211. (It there a better interface to this information?). - Does it work if you replace the use of the operator with the equivalent function call (contains)? Good idea. Yes. It does work. As such, we can assume that the C function and the CREATE FUNCTION are OK. - Check for differences in the explain output, that should reveal any implicit casts that may be getting in your way. The EXPLAIN does not show any explicit casts occurring. -- This works -- 17 seconds SELECT W.geocode, F.state, F.code, F.name FROM work as W, features as F, boundary as TB WHERE TB.feature_id = F.feature_id AND TB.boundout IS TRUE AND TB.boundbox @ box(W.geocode,W.geocode) AND contains(TB.boundary,W.geocode) AND (TB.feature_id) NOT IN ( SELECT feature_id FROM boundary as FB WHERE FB.feature_id = TB.feature_id AND FB.boundout IS FALSE AND FB.boundbox @ box(W.geocode,W.geocode) AND contains(FB.boundary,W.geocode) ) ORDER BY W.geocode[0], W.geocode[1]; -- This works -- 39 seconds SELECT W.geocode, F.state, F.code, F.name FROM work as W, features as F, boundary as TB WHERE TB.feature_id = F.feature_id AND TB.boundout IS TRUE AND contains(TB.boundbox,W.geocode) AND contains(TB.boundary,W.geocode) AND (TB.feature_id) NOT IN ( SELECT feature_id FROM boundary as FB WHERE FB.feature_id = TB.feature_id AND FB.boundout IS FALSE AND contains(FB.boundbox,W.geocode) AND contains(FB.boundary,W.geocode) ) ORDER BY W.geocode[0], W.geocode[1]; -- This fails. -- Returns empty set SELECT W.geocode, F.state, F.code, F.name FROM work as W, features as F, boundary as TB WHERE TB.feature_id = F.feature_id AND TB.boundout IS TRUE AND TB.boundbox @ W.geocode AND contains(TB.boundary,W.geocode) AND (TB.feature_id) NOT IN ( SELECT feature_id FROM boundary as FB WHERE FB.feature_id = TB.feature_id AND FB.boundout IS FALSE AND FB.boundbox @ W.geocode AND contains(FB.boundary,W.geocode) ) ORDER BY W.geocode[0], W.geocode[1]; -- 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] Index-only quals
On Fri, Aug 21, 2009 at 12:43 PM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: I added a new column 'amregurgitate' to pg_am, to mark which indexams can return index tuples. Very picturesque but uh, perhaps the more mundane amcanrettuples would be clearer? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Index-only quals
Greg Stark wrote:
On Fri, Aug 21, 2009 at 12:43 PM, Heikki
Linnakangasheikki.linnakan...@enterprisedb.com wrote:
Here is an updated version of my patch to return data from b-tree
indexes, and use it to satisfy quals.
+ if (!found_clause useful_pathkeys == NIL
!useful_predicate)
+ ipath-scantype = ST_INDEXSCAN;
+ else
+ {
+ ipath-scantype = 0;
+ if (index-amhasgettuple)
+ ipath-scantype |= ST_INDEXSCAN;
+ if (index-amhasgetbitmap)
+ ipath-scantype |= ST_BITMAPSCAN;
+ }
+
Does this section need to check amhasgettuple for the index-only scan
case as well? It looks like right now if an indexam has amregurgitate
set but not amhasgettuple then weird things could happen.
We check earlier in the function before we construct indexonlyQuals that
the index has amhasgettuple. Hmm, can you find an easier-to-understand
way to write that?
--
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] Index-only quals
Greg Stark wrote: It looks like right now if an indexam has amregurgitate set but not amhasgettuple then weird things could happen. The combination (amregurgitate !amhasgettuple) makes no sense, BTW. If an indexam has no gettuple function, there's no way it can return data from the index. -- 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] Index-only quals
On Fri, Aug 21, 2009 at 12:43 PM, Heikki
Linnakangasheikki.linnakan...@enterprisedb.com wrote:
Here is an updated version of my patch to return data from b-tree
indexes, and use it to satisfy quals.
+ if (!found_clause useful_pathkeys == NIL
!useful_predicate)
+ ipath-scantype = ST_INDEXSCAN;
+ else
+ {
+ ipath-scantype = 0;
+ if (index-amhasgettuple)
+ ipath-scantype |= ST_INDEXSCAN;
+ if (index-amhasgetbitmap)
+ ipath-scantype |= ST_BITMAPSCAN;
+ }
+
Does this section need to check amhasgettuple for the index-only scan
case as well? It looks like right now if an indexam has amregurgitate
set but not amhasgettuple then weird things could happen.
--
greg
http://mit.edu/~gsstark/resume.pdf
--
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: generalized index constraints
Brendan Jurd escribió: I would be fine with a NOTICE in the former case, so something like this would be cool # CREATE TABLE foo (LIKE bar INCLUDING INDEXES); NOTICE: INCLUDING INDEXES will also include any constraints on those indexes. HINT: Specify EXCLUDING CONSTRAINTS to omit them. To my mind the severity is similar to such notices as NOTICE: CREATE TABLE / UNIQUE will create implicit index i.e., this is probably what you wanted us to do, but just in case you weren't expecting this side-effect, we're letting you know about it. NOTICEs is what we do with index creation on primary key, unique indexes, and sequences on serial columns, and I think they are seen as just noise by everyone except novices. Do we want to add more? Maybe they should be INFO, so that they are shown to the client but not sent to the server log. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SIGUSR1 pingpong between master na autovacum launcher causes crash
I found following core file of PG 8.4.0 on my system (Solaris Nevada b119): fe8ae42d _dowrite (85bf6e8, 3a, 8035e3c, 80350e8) + 8d fe8ae743 _ndoprnt (85bf6e8, 8035ec8, 8035e3c, 0) + 2ba fe8b322d vsnprintf (85bfaf0, 3ff, 85bf6e8, 8035ec8, 0, 0) + 65 082194ea appendStringInfoVA (8035e9c, 85bf6e8, 8035ec8) + 4a 083ca5d3 errmsg (849c340, 0) + 103 0829272d StartAutoVacWorker (fe97f000, 32, 85b82b0, 8035ef4, 82a1496, c) + 3d 082a1901 StartAutovacuumWorker (c, 8035f08, fe8ed28f, 10, 0, 8035fbc) + 71 082a1496 sigusr1_handler (10, 0, 8035fbc) + 186 fe8ed28f __sighndlr (10, 0, 8035fbc, 82a1310) + f fe8e031f call_user_handler (10) + 2af fe8e054f sigacthandler (10, 0, 8035fbc) + df --- called from signal handler with signal 16 (SIGUSR1) --- fe8f37f6 __systemcall (3, fec32b88, 0, fe8e0b46) + 6 fe8e0c71 thr_sigsetmask (3, 85abd50, 0, fe8e0d18) + 139 fe8e0d3f sigprocmask (3, 85abd50, 0) + 31 082a14a4 sigusr1_handler (10, 0, 8036340) + 194 fe8ed28f __sighndlr (10, 0, 8036340, 82a1310) + f fe8e031f call_user_handler (10) + 2af fe8e054f sigacthandler (10, 0, 8036340) + df ... 80x same sighandler stack --- called from signal handler with signal 16 (SIGUSR1) --- fe8f37f6 __systemcall (3, fec32b88, 0, fe8e0b46) + 6 fe8e0c71 thr_sigsetmask (3, 85abd50, 0, fe8e0d18) + 139 fe8e0d3f sigprocmask (3, 85abd50, 0) + 31 082a14a4 sigusr1_handler (10, 0, 80478fc) + 194 fe8ed28f __sighndlr (10, 0, 80478fc, 82a1310) + f fe8e031f call_user_handler (10) + 2af fe8e054f sigacthandler (10, 0, 80478fc) + df --- called from signal handler with signal 16 (SIGUSR1) --- fe8f1867 __pollsys (8047b50, 2, 8047c04, 0) + 7 fe89ce61 pselect (6, 8047c44, 0, 0, 8047c04, 0) + 199 fe89d236 select (6, 8047c44, 0, 0, 8047c38, 0) + 78 0829dc20 ServerLoop (feffb804, bd26003b, 41b21fcb, 85c1de0, 1, 0) + c0 0829d5d0 PostmasterMain (3, 85b72c8) + dd0 08227abf main (3, 85b72c8, 8047df0, 8047d9c) + 22f 080b893d _start (3, 8047e80, 8047ea5, 8047ea8, 0, 8047ec2) + 7d The problem what I see here is that StartAutovacuumWorker() fails and send SIGUSR1 to the postmaster, but it send it too quickly and signal handler is still active. When signal mask is unblocked in sigusr1_handler() than signal handler is run again... The reason why StartAutovacuumWorker() is interesting. Log says: LOG: could not fork autovacuum worker process: Not enough space It is strange and I don't understand it. May be too many nested signal handlers call could cause it. Strange also is that 100ms is not enough to protect this situation, but I think that sleep could interrupted by signal. My suggestion is to set for example gotUSR1=true in sigusr1_handler() and in the server loop check if we got a USR1 signal. It avoids any problems with signal handler which is not currently POSIX compliant anyway. any other ideas? Zdenek -- 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: generalized index constraints
Alvaro Herrera alvhe...@commandprompt.com writes: NOTICEs is what we do with index creation on primary key, unique indexes, and sequences on serial columns, and I think they are seen as just noise by everyone except novices. Do we want to add more? Maybe they should be INFO, so that they are shown to the client but not sent to the server log. There was some discussion awhile back of creating a new NOVICE message level. INFO strikes me as a completely bad idea here, because it would actually make it harder for non-novices to suppress the messages. 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] Geometric Elimination
Paul Matthews p...@netspace.net.au writes: The C function? No it seems OK as well. What am I missing? Did you teach the opclass's consistent() function about these new operators? 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] Geometric Elimination
On Fri, Aug 21, 2009 at 09:42:57PM +1000, Paul Matthews wrote: Martijn van Oosterhout wrote: I haven't completely understood what you're trying to do Putting in place the missing 'box op point' and 'point op box' operators. The problematic queries are at the bottom of the email. If that's all you're doing, why all the fussing with indexes. - I don't see any definition of an operator class, just the family, which doesn't seem to make any sense to me. I am working on the assumption that the Box and Point class already have operator classes. Otherwise how would all the existing Box and Point operators work? From my limited understanding of the source code it's in postgres.bki round about line 2208 and 2211. (It there a better interface to this information?). The box type has an operator class, for boxes. There is no operator class for points, it could clearly be done, it just hasn't. Operator classes are *only* needed for index support. If you don't want index support, don't set them up. For catalogs I usually get go the the anoncvs interface for the raw data, for example: http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_opclass.h?rev=1.85 http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/include/catalog/pg_amop.h?rev=1.90 If you want index support, you need to go the whole way, and setup an appropriate operator class for (box,point). - Check for differences in the explain output, that should reveal any implicit casts that may be getting in your way. The EXPLAIN does not show any explicit casts occurring. It would be helpful if you pasted the actual EXPLAIN output. The last two really should be the same, so what's the difference in explain output? Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] XLogFlush
Jeff Janes jeff.ja...@gmail.com writes: Maybe this is one of those things that is obvious when someone points it out to you, but right now I am not seeing it. If you look at the last eight lines of this snippet from XLogFlush, you see that if we obtain WriteRqstPtr under the WALInsertLock, then we both write and flush up to the highest write request. But if we obtain it under the info_lck, then we write up to the highest write request but flush only up to our own records flush request. Why the disparate treatment? I think the point of the check within the info_lck section is that the global Write pointer must not be allowed to go backward. It's likely unnecessary though, since there is probably a defense against that in XLogWrite (or if not there should be). The other bit of the reasoning that doesn't seem well commented is that if we can't find out what the global status is (because of failure to acquire the insert lock), we should just do the work we know we need, not guess at some greater requirement. 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] GRANT ON ALL IN schema
Alvaro Herrera alvhe...@commandprompt.com writes: Petr Jelinek wrote: The implementation as I see it would create function in pg_temp namespace, call it and then drop it. Any other implementation would imho mean rewriting procedure language api. That's really ugly. It'll cause catalog bloat with every execution. I think it would be acceptable to have a new column in pg_language that pointed to an anonymous block execute function. Languages that do not define this function cannot use this new feature. +1. The other way would also (presumably) mean invoking the language's validate procedure, which might well be redundant and in any case would probably not have exactly the error-reporting behavior one would want. I think it's better if the language knows it's dealing with an anonymous block. You could even imagine the language relaxing its rules a bit, for instance not requiring an outer BEGIN/END in plpgsql. 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] Geometric Elimination
Martijn van Oosterhout klep...@svana.org writes: If you want index support, you need to go the whole way, and setup an appropriate operator class for (box,point). No, I think he's doing the right thing by adding these cross-type operators loose in the opfamily. An operator class is the subset of an opfamily that's *essential* to the functioning of an index on a particular datatype, and cross-type ops almost by definition are not that. I suspect the missing piece is to add cases to the relevant consistent() function(s). 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] UPDATE ... SET (a, b, c) = (expr)
Alvaro Herrera alvhe...@commandprompt.com writes: So is this the right way to approach the problem, or am I missing some simpler way? See discussions of NikhilS's patch last year. I think the conclusion we had arrived at was that we should refactor the representation of SubLink so that a single-row subselect could be responsible for producing the values of more than one Param (located in more than one place in the query's expression trees). Otherwise you get into having to redefine the representation of targetlists, which will break more code than is pleasant to think about. 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] Feedback about Drupal SQL debugging
Dear friends, I have been using PostgreSQL since 6.3 releases and I am a real fan. Of course, I never use nor trust MySQL to deliver data. Now I use Drupal 6.3 with PostgreSQL 8.4. I loose a lot of time correcting Drupal SQL. You may be interested in my developer feedback. I gathered some real examples here: Guidelines for writing MySQL and PostgreSQL compliant SQL = http://drupal.org/node/14 This page gathers most frequent problems that Drupal users and developers encounter when using PostgreSQL. I would be delighted to have your feedback. Could some issues reasonably be fixed for a better Drupal support? Kind regards, Jean-Michel signature.asc Description: Ceci est une partie de message numériquement signée
Re: [HACKERS] Feedback about Drupal SQL debugging
This page gathers most frequent problems that Drupal users and developers encounter when using PostgreSQL. I would be delighted to have your feedback. Could some issues reasonably be fixed for a better Drupal support? Well I doubt we would do anything to copy MySQL. However Drupal has already made strides to solve this in 7.x. The abstraction layer is much smarter. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] Feedback about Drupal SQL debugging
On Fri, 21 Aug 2009 18:22:41 +0200 Jean-Michel Pouré j...@poure.com wrote: I gathered some real examples here: Guidelines for writing MySQL and PostgreSQL compliant SQL = http://drupal.org/node/14 This page gathers most frequent problems that Drupal users and developers encounter when using PostgreSQL. I would be delighted to have your feedback. It looks to me like you could just reference SQL99 rather than mentioning PostgreSQL other than as an example of a standards compliant database engine. How would those constructs work in MS-SQL or Oracle? -- D'Arcy J.M. Cain da...@druid.net | Democracy is three wolves http://www.druid.net/darcy/| and a sheep voting on +1 416 425 1212 (DoD#0082)(eNTP) | what's for dinner. -- 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] Feedback about Drupal SQL debugging
Jean-Michel Pour? wrote: -- Start of PGP signed section. Dear friends, I have been using PostgreSQL since 6.3 releases and I am a real fan. Of course, I never use nor trust MySQL to deliver data. Now I use Drupal 6.3 with PostgreSQL 8.4. I loose a lot of time correcting Drupal SQL. You may be interested in my developer feedback. I gathered some real examples here: Guidelines for writing MySQL and PostgreSQL compliant SQL = http://drupal.org/node/14 This page gathers most frequent problems that Drupal users and developers encounter when using PostgreSQL. I would be delighted to have your feedback. Could some issues reasonably be fixed for a better Drupal support? I doubt we are going to change Postgres to improve Drupal support --- it would be better to fix Drupal. However, I have a few suggestions: For this item, http://drupal.org/node/80, use || for concatentation. I can't believe MySQL doesn't support multi-column indexes, http://drupal.org/node/58. For this item, I think you want DELETE FROM history USING ..., http://drupal.org/node/62. The SQL standard doesn't support multiple deletes, so odds are we will not either, http://drupal.org/node/555648. I show multi-value INSERT was added in PG 8.2, not 8.4, * Add support for multiple-row VALUES clauses, per SQL standard (Joe, Tom), http://drupal.org/node/68. I am confused because I thought Drupal worked with Postgres, but looking at your list, it seems it doesn't. -- Bruce Momjian br...@momjian.ushttp://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] Feedback about Drupal SQL debugging
On Fri, 2009-08-21 at 12:50 -0400, Bruce Momjian wrote: I show multi-value INSERT was added in PG 8.2, not 8.4, * Add support for multiple-row VALUES clauses, per SQL standard (Joe, Tom), http://drupal.org/node/68. I am confused because I thought Drupal worked with Postgres, but looking at your list, it seems it doesn't. Drupal itself works perfectly on Postgres. The problem is module authors that are MySQL developers who never test their module on Postgres. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- 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] Feedback about Drupal SQL debugging
Joshua D. Drake wrote: This page gathers most frequent problems that Drupal users and developers encounter when using PostgreSQL. I would be delighted to have your feedback. Could some issues reasonably be fixed for a better Drupal support? Well I doubt we would do anything to copy MySQL. However Drupal has already made strides to solve this in 7.x. The abstraction layer is much smarter. Joshua, Since you haven't shown us what page this refers to, I at least am totally in the dark about what is being discussed. cheers andrew -- 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] Feedback about Drupal SQL debugging
2009/8/21 Andrew Dunstan and...@dunslane.net: Since you haven't shown us what page this refers to, I at least am totally in the dark about what is being discussed. It was in the original post http://drupal.org/node/14 -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Feedback about Drupal SQL debugging
Greg Stark wrote: 2009/8/21 Andrew Dunstan and...@dunslane.net: Since you haven't shown us what page this refers to, I at least am totally in the dark about what is being discussed. It was in the original post http://drupal.org/node/14 Darn. Our mail system sucks badly. For some insane reason some postgresbut not all emails to me get badly delayed. cheers andrew -- 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] Index-only quals
On Fri, Aug 21, 2009 at 7:27 AM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: Greg Stark wrote: It looks like right now if an indexam has amregurgitate set but not amhasgettuple then weird things could happen. The combination (amregurgitate !amhasgettuple) makes no sense, BTW. If an indexam has no gettuple function, there's no way it can return data from the index. to have two columns that can conflict is not error prone? why not make amhasgettuple an enum? -- Atentamente, Jaime Casanova Soporte y capacitación de PostgreSQL Asesoría y desarrollo de sistemas Guayaquil - Ecuador Cel. +59387171157 -- 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] Feedback about Drupal SQL debugging
2009/8/21 Jean-Michel Pouré j...@poure.com: Dear friends, I have been using PostgreSQL since 6.3 releases and I am a real fan. Of course, I never use nor trust MySQL to deliver data. Now I use Drupal 6.3 with PostgreSQL 8.4. I loose a lot of time correcting Drupal SQL. You may be interested in my developer feedback. I gathered some real examples here: Guidelines for writing MySQL and PostgreSQL compliant SQL = http://drupal.org/node/14 This page gathers most frequent problems that Drupal users and developers encounter when using PostgreSQL. I would be delighted to have your feedback. Could some issues reasonably be fixed for a better Drupal support? A lot of these issues seem to have easy workarounds, so I'm not sure what the big deal is. If you don't write standards-compliant SQL, you shouldn't be surprised when you find out that it's not portable. Most of those constructions wouldn't work on Microsoft SQL server either, and I bet at least some of them would fail under Oracle as well. For the int/varchar casting issue, you might try putting single quotes around the values. I would expect that to work in both databases, though I don't use MySQL. ...Robert -- 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-pass planner
On Aug 20, 2009, at 11:18 PM, Josh Berkus wrote: I don't think it's a bad idea, I just think you have to set your expectations pretty low. If the estimates are bad there isn't really any plan that will be guaranteed to run quickly. Well, the way to do this is via a risk-confidence system. That is, each operation has a level of risk assigned to it; that is, the cost multiplier if the estimates are wrong. And each estimate has a level of confidence attached. Then you can divide the risk by the confidence, and if it exceeds a certain level, you pick another plan which has a lower risk/confidence level. However, the amount of extra calculations required for even a simple query are kind of frightning. Would it? Risk seems like it would just be something along the lines of the high-end of our estimate. I don't think confidence should be that hard either. IE: hard-coded guesses have a low confidence. Something pulled right out of most_common_vals has a high confidence. Something estimated via a bucket is in-between, and perhaps adjusted by the number of tuples. -- Decibel!, aka Jim C. Nasby, Database Architect deci...@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 -- 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] XLogFlush
On 21 August 2009 at 10:18 Jeff Janes jeff.ja...@gmail.com wrote: The effect of this seems to be that when WALInsertLock is busy, group commits are suppressed. Agreed, but its not a place to look at just yet since this is changing as part of sync rep patch. We do need to change this to make group commit work better. Glad to see someone willing to get involved. Best Regards, Simon Riggs
Re: [HACKERS] Lazy Snapshots
On 19 August 2009 at 00:09 Josh Berkus j...@agliodbs.com wrote: When we discussed Hot Standby at pgCon 2008, we discussed the possibility of stopping the replications stream whenever a VACUUM came through until any queries currently running on the slave completed. Did that approach turn out to be completely unworkable? Why? It will be an option, in two forms and not the part I was removing. Neither of those do all the things I want to be able to do, so I had been searching for another way to do replay and uninterrupted queries. Best Regards, Simon Riggs
Re: [HACKERS] Lazy Snapshots
On 18 August 2009 at 16:18 Tom Lane t...@sss.pgh.pa.us wrote: Simon, this concept is completely broken, as far as I can tell. Thanks for the precise example. Yes, I agree it is not going to work when there could be more than one row version in the relation. Which doesn't leave useful wriggle room, so not pursuing this further. What a shame. As discussed on -perform, it does seem as if we might have retrospectively calculated snapshots if we had the right data structure. But that's a different set of thoughts for another day. Best Regards, Simon Riggs
[HACKERS] still a wip, plpython3
Thought another message might be appropriate as I've made some progress: finished up PEP302 interfaces on PyPgFunction objects(provides linecache support for tracebacks), IST support, DB error handling(pg error - pyexc - pg error), and, recently, some annoying type interface improvements.. Some of those features in action: IST tests[1], error tests[2]. Also, I started on a WIP wiki page covering my progress and a small amount of documentation for anyone curious enough to play with it[3]. [some notes; feel free to swing the cluebat =] In order to implement IST support and error handling, the PL has to keep track of the number of open ISTs, and whether or not the code executed by the PL caused a database error. Tracking the error state is needed so that some (most) Postgres interfaces will not be invoked inside a failed transaction, and so that the PL will not exit without indicating an error state(elog/ ereport), and so that the PL will not try to commit a failed subtransaction on IST __exit__. Tracking the number of open ISTs is needed so that the PL can properly clean up after any misused Postgres.Transaction objects. When this situation is presented to the PL, all the excess ISTs that remained open will be aborted and an error will be thrown. At this point, the major items remaining are: SPI interfaces, polymorphic functions, array composite interfaces(the objects can be created and passed around, but the item/element access methods are completely untested, ATM), whatever random bugs popup/minor cleanupS, and, of course, documentation. Still lots to do. [1] http://github.com/jwp/postgresql-plpython3/blob/67c75e7e80c853d12cd279ecd86fd0a409d4007d/src/pl/plpython3/expected/plpython3_xact.out [2] http://github.com/jwp/postgresql-plpython3/blob/67c75e7e80c853d12cd279ecd86fd0a409d4007d/src/pl/plpython3/expected/plpython3_error.out [3] http://wiki.postgresql.org/wiki/WIP:plpython3 -- 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] Feedback about Drupal SQL debugging
Jean-Michel, Thank you for doing this! I've registered for the Drupal site so that I can fix and/or expand some of your items. People who know Drupal better than me should add to them. If you want to discuss Drupal PostgreSQL again, please post on the pgsql-advocacy list or the pgsql-php mailing lists. pgsql-hackers isn't the best place to get people to help you. BTW, why don't we have a multi-argument version of CONCAT()? In 8.4, it would be possible ... I should add it to mysqlcompat library. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] SIGUSR1 pingpong between master na autovacum launcher causes crash
Zdenek Kotala wrote: The problem what I see here is that StartAutovacuumWorker() fails and send SIGUSR1 to the postmaster, but it send it too quickly and signal handler is still active. When signal mask is unblocked in sigusr1_handler() than signal handler is run again... The reason why StartAutovacuumWorker() is interesting. Log says: LOG: could not fork autovacuum worker process: Not enough space Does this mean that the machine is out of swap space? It is strange and I don't understand it. May be too many nested signal handlers call could cause it. Strange also is that 100ms is not enough to protect this situation, but I think that sleep could interrupted by signal. My suggestion is to set for example gotUSR1=true in sigusr1_handler() and in the server loop check if we got a USR1 signal. It avoids any problems with signal handler which is not currently POSIX compliant anyway. What 100ms? The pg_usleep call you see in ServerLoop is only there during shutdown; normally it would be the select() call that would be blocking the process. If sigusr1_handler needs rewriting, don't all the other sighandler as well? Note that the process is supposed to be running with signals blocked all the time except during those sleep/select calls, which is what (according to comments) let the sighandlers do nontrivial tasks. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] SIGUSR1 pingpong between master na autovacum launcher causes crash
Alvaro Herrera píše v pá 21. 08. 2009 v 15:40 -0400: Zdenek Kotala wrote: The problem what I see here is that StartAutovacuumWorker() fails and send SIGUSR1 to the postmaster, but it send it too quickly and signal handler is still active. When signal mask is unblocked in sigusr1_handler() than signal handler is run again... The reason why StartAutovacuumWorker() is interesting. Log says: LOG: could not fork autovacuum worker process: Not enough space Does this mean that the machine is out of swap space? It is ENOMEM error. But it is strange. Machine has 4GB RAM and it was freshly installed PG84 without any data and with default configuration. It was not under load. I did not find any clue what happend with memory on this system. The question is if out of memory was a source or result of the pinpong. It is strange and I don't understand it. May be too many nested signal handlers call could cause it. Strange also is that 100ms is not enough to protect this situation, but I think that sleep could interrupted by signal. My suggestion is to set for example gotUSR1=true in sigusr1_handler() and in the server loop check if we got a USR1 signal. It avoids any problems with signal handler which is not currently POSIX compliant anyway. What 100ms? The pg_usleep call you see in ServerLoop is only there during shutdown; normally it would be the select() call that would be blocking the process. I mean AutoVacLauncherMain() http://doxygen.postgresql.org/autovacuum_8c.html#19ef1013e6110a4536ed92a454aba8c9 line 656 If sigusr1_handler needs rewriting, don't all the other sighandler as well? Note that the process is supposed to be running with signals blocked all the time except during those sleep/select calls, which is what (according to comments) let the sighandlers do nontrivial tasks. Comments says that it is OK. POSIX says that is not OK and my instinct say to trust the POSIX standard. Especially I do not see any reason why we need do this in signal handler. avl_sigterm_handler and so on are good example how it should be implemented in postmaster as well. The core shows that it is not good idea to have complicated signal handler. Zdenek -- 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] SIGUSR1 pingpong between master na autovacum launcher causes crash
Alvaro Herrera alvhe...@commandprompt.com writes: If sigusr1_handler needs rewriting, don't all the other sighandler as well? It does not, and neither do they. I'm not sure what happened here but it wasn't the fault of the postmaster's organization of signal handlers. It does seem that we ought to change things so that there's a bit more delay before trying to re-launch a failed autovac worker, though. Whatever caused this was effectively turning the autovac logic into a fork-bomb engine. I'm not thinking of just postponing the relaunch into the main loop, but ensuring at least a few hundred msec delay before we try again. 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] Feedback about Drupal SQL debugging
Josh Berkus j...@agliodbs.com writes: BTW, why don't we have a multi-argument version of CONCAT()? Why wouldn't people use the SQL-standard || operator instead? 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] SIGUSR1 pingpong between master na autovacum launcher causes crash
Tom Lane wrote:
Alvaro Herrera alvhe...@commandprompt.com writes:
If sigusr1_handler needs rewriting, don't all the other sighandler as
well?
It does not, and neither do they. I'm not sure what happened here but
it wasn't the fault of the postmaster's organization of signal handlers.
It does seem that we ought to change things so that there's a bit more
delay before trying to re-launch a failed autovac worker, though.
Whatever caused this was effectively turning the autovac logic into
a fork-bomb engine. I'm not thinking of just postponing the relaunch
into the main loop, but ensuring at least a few hundred msec delay before
we try again.
Would it be enough to move the kill() syscall into ServerLoop in
postmaster.c instead of letting it be called in the signal handler, per
the attached patch? This way the signal is not delayed, but we exit the
signal handler before doing it.
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Index: src/backend/postmaster/postmaster.c
===
RCS file: /home/alvherre/Code/cvs/pgsql/src/backend/postmaster/postmaster.c,v
retrieving revision 1.587
diff -c -p -r1.587 postmaster.c
*** src/backend/postmaster/postmaster.c 7 Aug 2009 05:58:55 - 1.587
--- src/backend/postmaster/postmaster.c 21 Aug 2009 21:21:05 -
*** bool redirection_done = false; /* stder
*** 290,295
--- 290,297
/* received START_AUTOVAC_LAUNCHER signal */
static volatile sig_atomic_t start_autovac_launcher = false;
+ /* the launcher needs to be signalled to communicate some condition */
+ static volatile bool avlauncher_needs_signal = false;
/*
* State for assigning random salts and cancel keys.
*** ServerLoop(void)
*** 1391,1396
--- 1393,1406
if (PgStatPID == 0 pmState == PM_RUN)
PgStatPID = pgstat_start();
+ /* If we need to signal the autovacuum launcher, do so now */
+ if (avlauncher_needs_signal)
+ {
+ avlauncher_needs_signal = false;
+ if (AutoVacPID != 0)
+ kill(AutoVacPID, SIGUSR1);
+ }
+
/*
* Touch the socket and lock file every 58 minutes, to ensure that
* they are not removed by overzealous /tmp-cleaning tasks. We assume
*** StartAutovacuumWorker(void)
*** 4354,4365
/*
* Report the failure to the launcher, if it's running. (If it's not, we
* might not even be connected to shared memory, so don't try to call
! * AutoVacWorkerFailed.)
*/
if (AutoVacPID != 0)
{
AutoVacWorkerFailed();
! kill(AutoVacPID, SIGUSR1);
}
}
--- 4364,4379
/*
* Report the failure to the launcher, if it's running. (If it's not, we
* might not even be connected to shared memory, so don't try to call
! * AutoVacWorkerFailed.) Note that we also need to signal it so that it
! * responds to the condition, but we don't do that here, instead waiting
! * for ServerLoop to do it. This way we avoid a ping-pong signalling in
! * quick succession between the autovac launcher and postmaster in case
! * things get ugly.
*/
if (AutoVacPID != 0)
{
AutoVacWorkerFailed();
! avlauncher_needs_signal = true;
}
}
--
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] Feedback about Drupal SQL debugging
I've registered for the Drupal site so that I can fix and/or expand some of your items. Thanks. I corrected the index on dual fields page. If you want to discuss Drupal PostgreSQL again, please post on the pgsql-advocacy list or the pgsql-php mailing lists. pgsql-hackers isn't the best place to get people to help you. I would prefer no, please. This post is made to understand what needs to be done at PostgreSQL level for better Drupal supports. As written previously, Drupal developers write MySQL code. Some of this code is not portable, okay. BTW, why don't we have a multi-argument version of CONCAT()? In 8.4, it would be possible ... I should add it to mysqlcompat library. yes. In PostgreSQL core ... PostgreSQL requires all non-aggregated fields to be present in the GROUP BY clause (I fixed 10 such issues in Drupal code). http://drupal.org/node/30 Why can't PostgreSQL add the required field automatically? Could this be added to PostgreSQL to-do-list? Kind regards, Jean-Michel signature.asc Description: Ceci est une partie de message numériquement signée
Re: [HACKERS] SIGUSR1 pingpong between master na autovacum launcher causes crash
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: It does seem that we ought to change things so that there's a bit more delay before trying to re-launch a failed autovac worker, though. Whatever caused this was effectively turning the autovac logic into a fork-bomb engine. I'm not thinking of just postponing the relaunch into the main loop, but ensuring at least a few hundred msec delay before we try again. Would it be enough to move the kill() syscall into ServerLoop in postmaster.c instead of letting it be called in the signal handler, per the attached patch? This way the signal is not delayed, but we exit the signal handler before doing it. I'd still like to have some fork-rate-limiting behavior in there somewhere. However, it might make sense for the avlauncher to do that rather than the postmaster. Does that idea seem more implementable? (If the launcher implements a minimum delay between requests then it really doesn't matter whether we apply this patch or not, and I'd be inclined to leave the postmaster alone rather than add yet more state.) 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] SIGUSR1 pingpong between master na autovacum launcher causes crash
Tom Lane wrote:
I'd still like to have some fork-rate-limiting behavior in there
somewhere. However, it might make sense for the avlauncher to do that
rather than the postmaster. Does that idea seem more implementable?
Well, there's already rate limiting in the launcher:
if (AutoVacuumShmem-av_signal[AutoVacForkFailed])
{
/*
* If the postmaster failed to start a new
worker, we sleep
* for a little while and resend the signal.
The new worker's
* state is still in memory, so this is
sufficient. After
* that, we restart the main loop.
*
* XXX should we put a limit to the number of
times we retry?
* I don't think it makes much sense, because a
future start
* of a worker will continue to fail in the
same way.
*/
AutoVacuumShmem-av_signal[AutoVacForkFailed] =
false;
pg_usleep(10L); /* 100ms */
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_WORKER);
continue;
}
Does it just need a longer delay?
--
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
--
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] GRANT ON ALL IN schema
Tom Lane napsal(a): That's really ugly. It'll cause catalog bloat with every execution. I think it would be acceptable to have a new column in pg_language that pointed to an anonymous block execute function. Languages that do not define this function cannot use this new feature. +1. The other way would also (presumably) mean invoking the language's validate procedure, which might well be redundant and in any case would probably not have exactly the error-reporting behavior one would want. I think it's better if the language knows it's dealing with an anonymous block. You could even imagine the language relaxing its rules a bit, for instance not requiring an outer BEGIN/END in plpgsql. Alright I can do it this way. However there is one question about implementing it in plpgsql. Currently, the compiler reads info directly from heap tuple, so I either have to write separate compiler for inline functions or change the existing one to accept the required info as parameters and fabricate some of it when compiling inline function. I am unsure which one is the preferred way. -- Regards Petr Jelinek (PJMODOS)
Re: [HACKERS] SIGUSR1 pingpong between master na autovacum launcher causes crash
Alvaro Herrera alvhe...@commandprompt.com writes: Tom Lane wrote: I'd still like to have some fork-rate-limiting behavior in there somewhere. However, it might make sense for the avlauncher to do that rather than the postmaster. Does that idea seem more implementable? Well, there's already rate limiting in the launcher: [ scratches head... ] You know, as I was writing that email the concept seemed a bit familiar. But if that's in there, how the heck did the launcher manage to bounce back to the postmaster before the latter got out of its signal handler? Have you tested this actually works as intended? Could Zdenek have tested a version that lacks it? Does it just need a longer delay? Maybe, but I think we need to understand exactly what happened first. 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] GRANT ON ALL IN schema
Petr Jelinek pjmo...@pjmodos.net writes: However there is one question about implementing it in plpgsql. Currently, the compiler reads info directly from heap tuple, so I either have to write separate compiler for inline functions or change the existing one to accept the required info as parameters and fabricate some of it when compiling inline function. I am unsure which one is the preferred way. Sounds like we have to refactor that code a bit. Or maybe it should just be a separate code path. The current plpgsql compiler is also pretty intertwined with stuffing all the information about the function into a persistent memory context, which is something we most definitely *don't* want for an anonymous code block. So it's going to take a bit of work there. I think pulling the heap tuple apart might be the least of your worries. 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] Feedback about Drupal SQL debugging
On Fri, Aug 21, 2009 at 04:19:43PM -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: BTW, why don't we have a multi-argument version of CONCAT()? Why wouldn't people use the SQL-standard || operator instead? Because by default, MySQL uses that as, get this, logical OR. Cheers, David (grateful he's with a project that doesn't just gratuitously go around breaking stuff) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Feedback about Drupal SQL debugging
David Fetter da...@fetter.org writes: On Fri, Aug 21, 2009 at 04:19:43PM -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: BTW, why don't we have a multi-argument version of CONCAT()? Why wouldn't people use the SQL-standard || operator instead? Because by default, MySQL uses that as, get this, logical OR. Egad. Well, I think that's something for the mysqlcompat project not core ... 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] Feedback about Drupal SQL debugging
On 8/21/09 3:17 PM, Tom Lane wrote: David Fetter da...@fetter.org writes: On Fri, Aug 21, 2009 at 04:19:43PM -0400, Tom Lane wrote: Josh Berkus j...@agliodbs.com writes: BTW, why don't we have a multi-argument version of CONCAT()? Why wouldn't people use the SQL-standard || operator instead? Because by default, MySQL uses that as, get this, logical OR. Egad. Well, I think that's something for the mysqlcompat project not core ... Yeah, I'll write one. I'll also blog it as an example of the new variable argument functions. -- Josh Berkus PostgreSQL Experts Inc. www.pgexperts.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] GRANT ON ALL IN schema
Tom Lane napsal(a): Petr Jelinek pjmo...@pjmodos.net writes: However there is one question about implementing it in plpgsql. Currently, the compiler reads info directly from heap tuple, so I either have to write separate compiler for inline functions or change the existing one to accept the required info as parameters and fabricate some of it when compiling inline function. I am unsure which one is the preferred way. Sounds like we have to refactor that code a bit. Or maybe it should just be a separate code path. The current plpgsql compiler is also pretty intertwined with stuffing all the information about the function into a persistent memory context, which is something we most definitely *don't* want for an anonymous code block. So it's going to take a bit of work there. I think pulling the heap tuple apart might be the least of your worries. The question is still valid, though it's better put in your words - do we want to refactor the existing compiler or write a separate one ? About putting the information about the function into a persistent memory context - I was planning on bypassing it and it can be easily bypassed with both implementations, since plpgsql_compile won't be called even if we do the refactoring. When I talked about modifying current compiler I was talking about do_compile only (that's why I talked about the heap tuple). It's true that we don't need most of the PLpgSQL_function struct for anonymous code block and there might be other advantages in using separate compiler and exec functions for this. -- Regards Petr Jelinek (PJMODOS)
Re: [HACKERS] Feedback about Drupal SQL debugging
Jean-Michel Pouré wrote: BTW, why don't we have a multi-argument version of CONCAT()? In 8.4, it would be possible ... I should add it to mysqlcompat library. yes. In PostgreSQL core ... No. That is exactly where it shouldn't go. And frankly, Drupal developers should stop writing non-portable code. Isn't there a Mysql mode that is supposed to conform to the standard, at least more than their default mode? PostgreSQL requires all non-aggregated fields to be present in the GROUP BY clause (I fixed 10 such issues in Drupal code). http://drupal.org/node/30 Why can't PostgreSQL add the required field automatically? Could this be added to PostgreSQL to-do-list? Isn't that contrary to the standard? cheers andrew -- 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] Feedback about Drupal SQL debugging
2009/8/21 Jean-Michel Pouré j...@poure.com: PostgreSQL requires all non-aggregated fields to be present in the GROUP BY clause (I fixed 10 such issues in Drupal code). http://drupal.org/node/30 Why can't PostgreSQL add the required field automatically? Could this be added to PostgreSQL to-do-list? This is a more complex (and more interesting) topic than what your blog discusses. Firstly understand what MySQL is *actually* doing: select a,b,c from tab group by a Only sorts and groups by a as instructed. The b columns and c columns are not included in the grouping. So if you have data like: a,b,c 1,1,1 1,2,2 2,1,1 2,2,2 You'll get two groups because there are only two values of a. One group will have a=1 and one group will have a=2. Which value you get for b and c will be completely arbitrary and unpredictable. If Postgres added b,c to the GROUP BY it would produce four groups, because there four different values of a,b,c. You *can* get something similar to MySQL's behaviour using DISTINCT ON: select distinct on (a) a,b,c from a ORDER BY a,b,c But Postgres insists you have an ORDER BY which has to agree with the DISTINCT ON columns and provide some extra column(s) to determine which values of b,c are chosen. If Postgres changed on this front it would be to support the SQL Standard concept of functional dependency. In cases where some columns are guaranteed to be unique you can leave them out of the GROUP BY but still use them in the select list. This isn't MySQL's behaviour of just allowing you to leave them out and hope that it doesn't matter which row's values are used. The database has to actually determine that it really doesn't matter. Typically that would be because you've grouped by a set of columns which form the key of a unique constraint, in which case every other column from that table would also necessarily be the same since they would all come from the same row of that table. -- greg http://mit.edu/~gsstark/resume.pdf -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] EXPLAIN VERBOSE vs resjunk output columns
Currently, explain.c goes out of its way to not print resjunk output columns (those not meant to be seen by the user, such as hidden sort key columns) in EXPLAIN VERBOSE targetlists. I made 8.4 act that way for reasons I don't recall at the moment, but I've found several times now that it's misleading. Would anybody complain about including those columns? An example of what I'm talking about: regression=# explain verbose select unique1 from tenk1 order by tenthous; QUERY PLAN Sort (cost=1122.39..1147.39 rows=1 width=8) Output: unique1 Sort Key: tenk1.tenthous - Seq Scan on public.tenk1 (cost=0.00..458.00 rows=1 width=8) Output: unique1 (5 rows) The seqscan is actually emitting both unique1 and tenthous, as it obviously must or the sort node would have nothing to work with. You can see that from the fact that the estimated row width is 8 bytes (two integers) ... but tenthous is nowhere to be seen in the Output: line. 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] revised hstore patch
Robert Haas wrote: On Wed, Jul 22, 2009 at 2:17 PM, Andrew Gierthand...@tao11.riddles.org.uk wrote: Unless I hear any objections I will proceed accordingly... At this point it's been 12 days since this was written and no updated patch has been posted, so I think it's well past time to move this to Returned with Feedback. Accordingly I'm going to make that change. Hopefully, an updated patch will be ready in time for the September CommitFest. Curious if this patch is likely for 8.5 and/or if there's a newer patch available. I've come across an application that it seems well suited for, and would be happy to test whichever version of the patch would be most useful for me to test against. -- 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] Feedback about Drupal SQL debugging
* Greg Stark (gsst...@mit.edu) wrote: select distinct on (a) a,b,c from a ORDER BY a,b,c But Postgres insists you have an ORDER BY which has to agree with the DISTINCT ON columns and provide some extra column(s) to determine which values of b,c are chosen. Not quite technically correct. You have to have an ORDER BY which includes the columns inside the DISTINCT ON, but not any more than that. At that point, the values you get for the other columns are arbitrary. PG does *allow* you to provide other columns in the ORDER BY, so you can specify which values from those other columns should be used. I'm not advocating that we force another column to be used, nor do I think you are, but I have to admit that I don't think I've ever used it w/o other columns in the ORDER BY. If Postgres changed on this front it would be to support the SQL Standard concept of functional dependency. In cases where some columns are guaranteed to be unique you can leave them out of the GROUP BY but still use them in the select list. This isn't MySQL's behaviour of just allowing you to leave them out and hope that it doesn't matter which row's values are used. The database has to actually determine that it really doesn't matter. Typically that would be because you've grouped by a set of columns which form the key of a unique constraint, in which case every other column from that table would also necessarily be the same since they would all come from the same row of that table. Hrmm. That sounds kinda neat, but you'd still have to specify one of the columns in the GROUP BY, I presume? Or could you just say 'GROUP BY' without any columns, and have it GROUP BY the key of the table you're using? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] EXPLAIN VERBOSE vs resjunk output columns
* Tom Lane (t...@sss.pgh.pa.us) wrote: Currently, explain.c goes out of its way to not print resjunk output columns (those not meant to be seen by the user, such as hidden sort key columns) in EXPLAIN VERBOSE targetlists. I made 8.4 act that way for reasons I don't recall at the moment, but I've found several times now that it's misleading. Would anybody complain about including those columns? +1 to include them. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] EXPLAIN VERBOSE vs resjunk output columns
On Sat, Aug 22, 2009 at 1:37 AM, Tom Lanet...@sss.pgh.pa.us wrote: You can see that from the fact that the estimated row width is 8 bytes (two integers) ... but tenthous is nowhere to be seen in the Output: line. +1 for self-consistent output. If we're including their size in width then they should be in the output list. If there was some way to mark the junk columns so users could tell which columns are being used higher up and which aren't -- which might be helpful if there are ambiguous names -- then that might be good. Nothing comes to mind offhand that wouldn't just be a lot of clutter though. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Feedback about Drupal SQL debugging
2009/8/22 Stephen Frost sfr...@snowman.net: Hrmm. That sounds kinda neat, but you'd still have to specify one of the columns in the GROUP BY, I presume? Or could you just say 'GROUP BY' without any columns, and have it GROUP BY the key of the table you're using? You would have to specify the key. I think typically you would have something like: SELECT a.*, sum(b.col) FROM a,b GROUP BY a.pk Since you have the primary key of a in your group by column you're allowed to use any columns from a in your select list even if they're not listed in the group by clause. The database knows that it can use those values from any output row of the group since they'll all come from the same orginal row of a. Or possibly it could use some plan that doesn't involve multiplying that data in the first place. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Feedback about Drupal SQL debugging
* Greg Stark (gsst...@mit.edu) wrote: You would have to specify the key. I think typically you would have something like: SELECT a.*, sum(b.col) FROM a,b GROUP BY a.pk Ahhh, ok, this makes more sense. This is SQL standard? Do we have a TODO for it? The database knows that it can use those values from any output row of the group since they'll all come from the same orginal row of a. Or possibly it could use some plan that doesn't involve multiplying that data in the first place. Right. It strikes me as a relativly small amount of work to get the initial just add the columns to the group by logic implemented. I'd start from exactly where that ERROR comes from, to minimize any performance hit from having to go figure out if the columns in the GROUP BY comprise a key. Doing something different in the planner based on that could come later, if necessary. I havn't looked at any code yet, but those who are familiar with these areas- any gotchas you can think of off-hand to make this more difficult than I'm hoping it is? Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] revised hstore patch
Ron Mayer wrote: Robert Haas wrote: On Wed, Jul 22, 2009 at 2:17 PM, Andrew Gierthand...@tao11.riddles.org.uk wrote: Unless I hear any objections I will proceed accordingly... At this point it's been 12 days since this was written and no updated patch has been posted, so I think it's well past time to move this to Returned with Feedback. Accordingly I'm going to make that change. Hopefully, an updated patch will be ready in time for the September CommitFest. Curious if this patch is likely for 8.5 and/or if there's a newer patch available. I've come across an application that it seems well suited for, and would be happy to test whichever version of the patch would be most useful for me to test against. Yea, I was wondering about this too. I do think we should just change the format and pg_migrator will detect the change and prevent migration for those cases. -- Bruce Momjian br...@momjian.ushttp://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] Feedback about Drupal SQL debugging
Stephen Frost sfr...@snowman.net writes: Right. It strikes me as a relativly small amount of work to get the initial just add the columns to the group by logic implemented. Well, no, you *aren't* adding the columns to the GROUP BY. You're just not throwing the error. You really don't want to add redundant columns to GROUP BY because it makes more work for the planner and executor (unless the planner can figure out they're redundant, which in itself takes work). This is a bit trickier than it looks because it makes the validity of a query dependent on the existence of an appropriate uniqueness constraint; thus for example DROP CONSTRAINT might invalidate a stored rule or view. See prior discussions. 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] Feedback about Drupal SQL debugging
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Right. It strikes me as a relativly small amount of work to get the initial just add the columns to the group by logic implemented. Well, no, you *aren't* adding the columns to the GROUP BY. You're just not throwing the error. You really don't want to add redundant columns to GROUP BY because it makes more work for the planner and executor (unless the planner can figure out they're redundant, which in itself takes work). Hmm, right. Possibly also add some bit of info to pass to something down the line, if necessary. This is a bit trickier than it looks because it makes the validity of a query dependent on the existence of an appropriate uniqueness constraint; thus for example DROP CONSTRAINT might invalidate a stored rule or view. See prior discussions. Ah, yes. Couldn't the dependency system be used to handle this though? If you try to drop that constraint it'll complain unless you use cascade which would drop the view? I'll try and find older discussions. Sadly, I don't see it on the TODO. Perhaps I can add it. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Feedback about Drupal SQL debugging
2009/8/22 Stephen Frost sfr...@snowman.net: * Greg Stark (gsst...@mit.edu) wrote: You would have to specify the key. I think typically you would have something like: SELECT a.*, sum(b.col) FROM a,b GROUP BY a.pk Ahhh, ok, this makes more sense. This is SQL standard? Incidentally it makes even more sense that MySQL would do what they do when you remember that they didn't have subqueries until recently. So MySQL programmers had all become accustomed to the circumlocutions like: SELECT a.* FROM a left join b USING (a.b_id = b.id) WHERE b.id IS NULL GROUP BY a.id to express the much simpler select * from a where b_id in (select id from b) So not many uses of it in MySQL actually *would* be valid if we implemented the shortcut. But MySQL doesn't enforce that so it serves that purpose as well as what we get out of DISTINCT ON. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Feedback about Drupal SQL debugging
* Greg Stark (gsst...@mit.edu) wrote: So not many uses of it in MySQL actually *would* be valid if we implemented the shortcut. But MySQL doesn't enforce that so it serves that purpose as well as what we get out of DISTINCT ON. That's probably a good thing- if they're valid then we'd probably return something different which would be a suprise. I'm not really looking at this from the help MySQL apps point of view.. It just strikes me as something nice to have. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Feedback about Drupal SQL debugging
2009/8/22 Stephen Frost sfr...@snowman.net: This is a bit trickier than it looks because it makes the validity of a query dependent on the existence of an appropriate uniqueness constraint; thus for example DROP CONSTRAINT might invalidate a stored rule or view. See prior discussions. Ah, yes. Couldn't the dependency system be used to handle this though? If you try to drop that constraint it'll complain unless you use cascade which would drop the view? I'll try and find older discussions. Sadly, I don't see it on the TODO. Perhaps I can add it. All this wasn't possible before 8.3 so there are a whole slew of optimizations that have been kind of waiting in the wings until we got that infrastructure. The first step is probably to do the opposite of what we're talking about here: cases where people *have* added extra columns to the GROUP BY key so they can use those columns in their select list. We can remove those columns from the sort or hash comparison key if there's a column (or columns) which is a unique constraint key for the same source. Similarly we can remove columns from an ORDER BY if the order key has earlier columns which are already a unique key for the same source. That would be fairly simple and it would provide a good test case for the dependency tracking stuff and plan invalidation triggered by constraint ddl. It wouldn't be a massive performance change but it would help some cases where the sort node is comparing a lot of redundant keys. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Feedback about Drupal SQL debugging
Greg Stark gsst...@mit.edu writes: The first step is probably to do the opposite of what we're talking about here: cases where people *have* added extra columns to the GROUP BY key so they can use those columns in their select list. We can remove those columns from the sort or hash comparison key if there's a column (or columns) which is a unique constraint key for the same source. Similarly we can remove columns from an ORDER BY if the order key has earlier columns which are already a unique key for the same source. This is something we could only do at plan time --- if we do it at parse time we risk making a robust query into one that will break when somebody drops a constraint. So it's not really the inverse of the other case. 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] Feedback about Drupal SQL debugging
On Friday 21 August 2009 04:01:36 pm Andrew Dunstan wrote: Jean-Michel Pouré wrote: BTW, why don't we have a multi-argument version of CONCAT()? In 8.4, it would be possible ... I should add it to mysqlcompat library. yes. In PostgreSQL core ... No. That is exactly where it shouldn't go. And frankly, Drupal developers should stop writing non-portable code. Isn't there a Mysql mode that is supposed to conform to the standard, at least more than their default mode? This is all solved with Drupal 7. We really shouldn't be burning time on this. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc : 503-667-4564 - http://www.commandprompt.com/ Since 1997, Consulting, Development, Support, Training -- 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] Feedback about Drupal SQL debugging
Greg Stark wrote: If Postgres changed on this front it would be to support the SQL Standard concept of functional dependency. In cases where some columns are guaranteed to be unique you can leave them out of the GROUP BY but still use them in the select list. This isn't MySQL's behaviour of just allowing you to leave them out and hope that it doesn't matter which row's values are used. The database has to actually determine that it really doesn't matter. Typically that would be because you've grouped by a set of columns which form the key of a unique constraint, in which case every other column from that table would also necessarily be the same since they would all come from the same row of that table. That would make much more sense. You can also get the effect of picking an arbitrary row now by use max(column) or min(column) in place of the straight column. cheers andrew -- 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] Feedback about Drupal SQL debugging
Andrew Dunstan and...@dunslane.net writes: Jean-Michel Pouré wrote: Why can't PostgreSQL add the required field automatically? Could this be added to PostgreSQL to-do-list? Isn't that contrary to the standard? As of SQL99 it's supposed to be legal if you're grouping by a primary key (or some other cases where the other columns can be proved functionally dependent on the grouping columns, but that's the most useful one). We haven't got round to implementing that, but I'm not sure that it would make the Drupal code work anyway. Are they actually writing to spec here, or just doing whatever mysql will let them? BTW, I was under the impression there already *was* a TODO entry about improving our standards compliance in this area. I can't find it in the list right now, though. 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] Lazy Snapshots
Hi, I have given some thought in this direction. I am just providing my idea. a) Have a structure similar to commit log, which should also store, transaction id at which the transaction got committed. Every transaction, after committing should update the transaction id at which the commit has happened b) All the transactions- when it starts should have two figures - xmin and also a transaction id after which nothing has got committed c) So whenever it sees the records, which are not inside the window, it can make decision by itself. i) The ones below xmin and committed are visible ii) the ones after xmax and committed are not visible iii) When its something in between, then check the time at which the commit happened in the structure and make the decision. Ideally, it would be better to change the structure of commit log itself. But maintaining an another structure also would help. Thanks, Gokul.
