Re: [HACKERS] Hot Standby, max_connections and max_prepared_transactions
On Fri, 2009-09-04 at 01:25 -0400, Tom Lane wrote: > Simon Riggs writes: > > On Thu, 2009-09-03 at 22:22 +0300, Heikki Linnakangas wrote: > >> Simon Riggs wrote: > >>> I propose we just accept that both max_connections and > >>> max_prepared_transactions need to be set correctly for recovery to work. > >>> This will make the state transitions more robust and it will avoid > >>> spurious and hard to test error messages. > >>> Any objections to me removing this slice of code from the patch? > > >> Umm, what slice of code? I don't recall any code trying to make it work. > > > Well, its there. > > Just to be clear: you're proposing requiring that these be set the > same on master and slave? Yes, or more precisely: Slave value >= Master value > I don't have a problem with that, but > I do suggest that we must provide a mechanism to check it --- I don't > want DBAs to be faced with obscure failures when (not if) they > mess it up. Perhaps include the values in checkpoint WAL records? Good plan. We can generate an immediate message at startup. -- Simon Riggs www.2ndQuadrant.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] Hot Standby, max_connections and max_prepared_transactions
Simon Riggs writes: > On Thu, 2009-09-03 at 22:22 +0300, Heikki Linnakangas wrote: >> Simon Riggs wrote: >>> I propose we just accept that both max_connections and >>> max_prepared_transactions need to be set correctly for recovery to work. >>> This will make the state transitions more robust and it will avoid >>> spurious and hard to test error messages. >>> Any objections to me removing this slice of code from the patch? >> Umm, what slice of code? I don't recall any code trying to make it work. > Well, its there. Just to be clear: you're proposing requiring that these be set the same on master and slave? I don't have a problem with that, but I do suggest that we must provide a mechanism to check it --- I don't want DBAs to be faced with obscure failures when (not if) they mess it up. Perhaps include the values in checkpoint WAL records? 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] Implementation of GROUPING SETS (T431: Extended grouping capabilities)
2009/9/3 Pavel Stehule : > 2009/9/3 Joshua Tolley : >> On Thu, Sep 03, 2009 at 01:19:25AM +0400, Олег Царев wrote: >>> After week-lengthed investigation, now i 'm sure - my level of >>> qualification not enough for implementation task "GROUPING SETS". >>> I require documentation about the executor and the planner, i can't >>> understand scheme of work by source code. >>> Many code, many cases, but very little information "what is it" and >>> "how thos work". May be i stupid. >> >> I doubt you're stupid; a stupid person wouldn't know what GROUPING SETS >> meant, >> wouldn't bother finding out, and certainly wouldn't bother trying to >> implement >> it. It's very helpful that you've let us know you're not working on it. That >> way Pavel, if he finds he has time and interest, or someone else, can work on >> it without fear of conflicting with what you're doing. Thanks for your work; >> please don't get discouraged! > > There some ways, how implement GROUPING SETS. Currently I don't would > to continue on this topic without some sponsoring. It got too my time > - and I am able to implement it only as some special variant of CTE. > Second way is total grouping planner refactoring - what is out of me. There supposed to be another way to implement, that is a "hybrid" way of CTE approach and hash tables approach. As you noticed, in group mode the planner may be untouchable which CTE helps to avoid and in hash mode you've almost done it. I wouldn't like to agree to CTE based approach totally, but the hybrid might be reasonable. Regards, > > regard > Pavel > >> >> -- >> Joshua Tolley / eggyknap >> End Point Corporation >> http://www.endpoint.com >> >> -BEGIN PGP SIGNATURE- >> Version: GnuPG v1.4.9 (GNU/Linux) >> >> iEYEARECAAYFAkqfM2QACgkQRiRfCGf1UMOHvgCgpzV9cvjhCWhzcmvRDbXjdBQ1 >> 4RYAn2E+ZLRLdho+c+ZFleslPrbyxsZN >> =66vh >> -END PGP SIGNATURE- >> >> > -- Hitoshi Harada -- 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] community decision-making & 8.5
2009/9/4 Joshua D. Drake : > On Thu, 2009-09-03 at 12:00 -0400, Robert Haas wrote: >> And /me pokes Brendan Jurd. :-) >> > > Hah! I almost listed him. /me adds a poke to Brendan Jurd. > /me stirs from sleep to announce "huh? whaddyawant?" Seriously though, I have been keeping an eye on this thread, and I think it's heading in an interesting and positive direction. Still I'm a little unclear on what would be expected of, and indeed what I could contribute to, the "release team" effort. So far in the thread I've seen mention of deciding/announcing/enforcing project deadlines, managing commit fests, beta testing and beta-to-release items. Is that about the right idea? Cheers, BJ -- 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] [PATCH] Largeobject access controls
Robert Haas wrote: > 2009/9/3 KaiGai Kohei : >> KaiGai Kohei wrote: >>> Alvaro Herrera wrote: Tom Lane wrote: > KaiGai Kohei writes: >> BTW, currently, the default ACL of largeobject allows anything for owner >> and nothing for world. Do you have any comment for the default behavior? > Mph. I think the backlash will be too great. You have to leave the > default behavior the same as it is now, ie, world access. BTW as a default it is pretty bad. Should we have a GUC var to set the default LO permissions? >>> It seems to me a reasonable idea in direction. >>> However, it might be better to add a GUC variable to turn on/off LO >>> permission feature, not only default permissions. >>> It allows us to control whether the privilege mechanism should perform >>> in backward compatible, or not. >> Now we have two options: >> >> 1. A GUC variable to set the default largeobject permissions. >> >> SET largeobject_default_acl = [ ro | rw | none ] >>- ro : read-only >>- rw : read-writable >>- none : nothing >> >> It can control the default acl which is applied when NULL is set on >> the pg_largeobject_meta.lomacl. However, lo_unlink() checks ownership >> on the largeobject, so it is not enough compatible with v8.4.x or prior. >> >> 2. A GUC veriable to turn on/off largeobject permissions. >> >> SET largeobject_compat_dac = [ on | off ] >> >> When the variable is turned on, largeobject dac permission check is >> not applied as the v8.4.x or prior version did. So, the variable is >> named "compat" which means compatible behavior. >> It also does not check ownership on lo_unlink(). >> >> My preference is the second approach. >> >> What's your opinion? > > I prefer the first. There's little harm in letting users set the > default permissions for themselves, but a GUC that controls > system-wide behavior will have to be something only superusers can > money with, and that seems like it will reduce usability. I don't intend to allow session users to set up their default acl. Both operation should be always system-wide. If a normal user can change the default acl, it is also equivalent he can grant all permissions to public on all the largeobject with its acl being NULL. Note that PostgreSQL does not set up a certain ACLs on its creation time, so NULL is assigned. The default ACL means an alternarive set of permissions, when it is NULL. > Why couldn't lo_unlink() just check write privilege? Because it is inconsistent behavior. PostgreSQL checks its ownership on dropping a certain database objects, such as tabls, procedures and so on. It seems to me quite strange, if only largeobject checks writer permission to drop itself. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- 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] [PATCH] Largeobject access controls
2009/9/3 KaiGai Kohei : > KaiGai Kohei wrote: >> Alvaro Herrera wrote: >>> Tom Lane wrote: KaiGai Kohei writes: > BTW, currently, the default ACL of largeobject allows anything for owner > and nothing for world. Do you have any comment for the default behavior? Mph. I think the backlash will be too great. You have to leave the default behavior the same as it is now, ie, world access. >>> BTW as a default it is pretty bad. Should we have a GUC var to set the >>> default LO permissions? >> >> It seems to me a reasonable idea in direction. >> However, it might be better to add a GUC variable to turn on/off LO >> permission feature, not only default permissions. >> It allows us to control whether the privilege mechanism should perform >> in backward compatible, or not. > > Now we have two options: > > 1. A GUC variable to set the default largeobject permissions. > > SET largeobject_default_acl = [ ro | rw | none ] > - ro : read-only > - rw : read-writable > - none : nothing > > It can control the default acl which is applied when NULL is set on > the pg_largeobject_meta.lomacl. However, lo_unlink() checks ownership > on the largeobject, so it is not enough compatible with v8.4.x or prior. > > 2. A GUC veriable to turn on/off largeobject permissions. > > SET largeobject_compat_dac = [ on | off ] > > When the variable is turned on, largeobject dac permission check is > not applied as the v8.4.x or prior version did. So, the variable is > named "compat" which means compatible behavior. > It also does not check ownership on lo_unlink(). > > My preference is the second approach. > > What's your opinion? I prefer the first. There's little harm in letting users set the default permissions for themselves, but a GUC that controls system-wide behavior will have to be something only superusers can money with, and that seems like it will reduce usability. Why couldn't lo_unlink() just check write privilege? ...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] [PATCH] Largeobject access controls
KaiGai Kohei wrote: > Alvaro Herrera wrote: >> Tom Lane wrote: >>> KaiGai Kohei writes: BTW, currently, the default ACL of largeobject allows anything for owner and nothing for world. Do you have any comment for the default behavior? >>> Mph. I think the backlash will be too great. You have to leave the >>> default behavior the same as it is now, ie, world access. >> BTW as a default it is pretty bad. Should we have a GUC var to set the >> default LO permissions? > > It seems to me a reasonable idea in direction. > However, it might be better to add a GUC variable to turn on/off LO > permission feature, not only default permissions. > It allows us to control whether the privilege mechanism should perform > in backward compatible, or not. Now we have two options: 1. A GUC variable to set the default largeobject permissions. SET largeobject_default_acl = [ ro | rw | none ] - ro : read-only - rw : read-writable - none : nothing It can control the default acl which is applied when NULL is set on the pg_largeobject_meta.lomacl. However, lo_unlink() checks ownership on the largeobject, so it is not enough compatible with v8.4.x or prior. 2. A GUC veriable to turn on/off largeobject permissions. SET largeobject_compat_dac = [ on | off ] When the variable is turned on, largeobject dac permission check is not applied as the v8.4.x or prior version did. So, the variable is named "compat" which means compatible behavior. It also does not check ownership on lo_unlink(). My preference is the second approach. What's your opinion? Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- 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] Triggers on columns
Peter Eisentraut wrote: > The SQL standard specifies that a trigger is fired if the column is > mentioned in the UPDATE statement, independent of whether the value is > actually changed through the update. Hmmm, what does the SQL standard say about modification of NEW values? Should we fire column triggers when their columns are mentioned in the UPDATE statement, but modified by another triggers? I believe we should fire them, but it is inconsistent because we will make different decisions whether NEW values are modified or not. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Elementary dependency look-up
Attached is a patch to add a couple basic dependency look-up capability
functions. They're based off the pg_get_serial_sequence function, and
are kind of the inverse of that function in some respects.
The patch adds two new functions to the backend, pg_get_owner_object and
pg_get_owner_column. These look up the requested object in the
pg_depend table, looking for an 'a' type dependency to another relation,
and resolve either the relation or column names to text.
postgres=# SELECT pg_get_owner_object('tbl_id_seq') AS obj,
postgres-# pg_get_owner_column('tbl_id_seq') AS col,
postgres-# pg_get_serial_sequence(
postgres(# pg_get_owner_object('tbl_id_seq'),
postgres(# pg_get_owner_column('tbl_id_seq')
postgres(# ) AS full_circle;
obj | col |full_circle
+-+---
public.tbl | id | public.tbl_id_seq
(1 row)
I tried not to be too myopic in the design, but apart from sequence
ownership I can't really think of any other uses for this. 'p'in and
'i'nternal relationships wouldn't make much sense, and 'n'ormal ones are
generally exposed in other ways. Anyone have any input there on how
this could be expanded?
Anyway, as an immediate practical example the patch modifies psql's
describe-verbose on sequences to show the ownership information...
postgres=# \d+ tbl_id_seq
(...)
Owner: public.tbl.id
- Josh Williams
Index: doc/src/sgml/func.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/func.sgml,v
retrieving revision 1.487
diff -c -r1.487 func.sgml
*** doc/src/sgml/func.sgml 16 Aug 2009 19:55:21 - 1.487
--- doc/src/sgml/func.sgml 2 Sep 2009 23:11:15 -
***
*** 12264,12269
--- 12264,12277
+ pg_get_owner_object
+
+
+
+ pg_get_owner_column
+
+
+
pg_tablespace_databases
***
*** 12365,12370
--- 12373,12388
uses
+pg_get_owner_object(relation_oid)
+text
+get name of the relation that owns the specified object, such as a sequence
+
+
+pg_get_owner_column(relation_oid)
+text
+get column name associated with the specified object in its owning relation
+
+
pg_get_triggerdef(trigger_oid)
text
get CREATE [ CONSTRAINT ] TRIGGER command for trigger
***
*** 12478,12483
--- 12496,12513
+pg_get_owner_object returns the name of the relation
+that owns the specified relation object, or NULL if the object isn't owned
+by a relation. The input parameter can be passed as an OID or possibly a
+double-quoted identifier. This can be treated in some respects as the
+inverse of pg_get_serial_sequence, where the association
+can be modified or removed with ALTER SEQUENCE OWNED BY.
+pg_get_owner_column returns the name of the column
+associated with an owned object, such as the name of a sequence's
+original serial column.
+
+
+
pg_get_userbyid extracts a role's name given
its OID.
Index: src/backend/utils/adt/ruleutils.c
===
RCS file: /projects/cvsroot/pgsql/src/backend/utils/adt/ruleutils.c,v
retrieving revision 1.306
diff -c -r1.306 ruleutils.c
*** src/backend/utils/adt/ruleutils.c 1 Aug 2009 19:59:41 - 1.306
--- src/backend/utils/adt/ruleutils.c 2 Sep 2009 23:11:19 -
***
*** 1446,1451
--- 1446,1601
/*
+ * pg_get_owner_object
+ * Returns the name of the object that owns the specified object
+ * by looking up an "auto" dependency relationship.
+ * Useful for finding a sequence's parent table.
+ * See pg_get_owner_column for the originating serial column.
+ */
+ Datum
+ pg_get_owner_object(PG_FUNCTION_ARGS)
+ {
+ Oid relId = PG_GETARG_OID(0);
+ Oid ownerId = InvalidOid;
+ RelationdepRel;
+ ScanKeyData key[3];
+ SysScanDesc depScan;
+ HeapTuple tup;
+
+ /* Find the requested object in the dependency table... */
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_classid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_objid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relId));
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_objsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(0));
+
+ depScan = systable_beginscan(depRel, DependDependerIndexId, true, SnapshotNow, 3, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(depScan)))
+ {
+ Form_pg_depend depRecord = (Form_pg_depend) GETSTRUCT(tup);
+
+ /* ... and look for the relation it depends on. */
+ if (depRecord->refclassid == RelationRela
Re: [HACKERS] community decision-making & 8.5
* Joshua D. Drake (j...@commandprompt.com) wrote: > She would definitely be a good option if she has time. I know that I > would be interested and I would like to see at least one long time > -hacker on board. I don't presume to be a long time -hacker, but I'm interested in what I can do to help with this. I like the general approach. I'm also up for being involved in 'managing' beta testers. Not sure if that role would be appropriate for this group, though it seems like it'd be a natural fit as it's directly related to the "is the release ready" question.. Just my 2c. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] remove flatfiles.c
On Thu, Sep 03, 2009 at 07:57:25PM -0400, Andrew Dunstan wrote: > daveg wrote: > >On Tue, Sep 01, 2009 at 07:42:56PM -0400, Tom Lane wrote: > >>I'm having a hard time believing that VACUUM FULL really has any > >>interesting use-case anymore. > > > >I have a client who uses temp tables heavily, hundreds of thousands of > >creates > >and drops per day. They also have long running queries. The only thing that > >keeps catalog bloat somewhat in check is vacuum full on bloated catalogs > >a few times a day. Without that pg_class, pg_attribute etc quickly balloon > >to thousands of pages. > > That's a rate of more than one create and drop per second. How does your > client handle the fact that VACUUM FULL will exclusively lock those > catalog tables? Without knowing more, it looks like a bit of a design issue. I'd say it is several per second. They wait for the catalog locks sometimes. This is not an interactive application so that is somewhat acceptable. It also occasionally causes deadlocks which is less agreeable. There are various reasons for the heavy use of temps, mainly having to do with loading external feeds or reusing intermediate query results in a series of queries. It would be great if there was a way to have temp tables that did not get cataloged, eg local cache only. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] remove flatfiles.c
daveg wrote: On Tue, Sep 01, 2009 at 07:42:56PM -0400, Tom Lane wrote: Greg Stark writes: On Wed, Sep 2, 2009 at 12:01 AM, Alvaro Herrera wrote: The use cases where VACUUM FULL wins currently are where storing two copies of the table and its indexes concurrently just isn't practical. Yeah, but then do you really need to use VACUUM FULL? If that's really a problem then there ain't that many dead tuples around. That's what I want to believe. But picture if you have, say a 1-terabyte table which is 50% dead tuples and you don't have a spare 1-terabytes to rewrite the whole table. But trying to VACUUM FULL that table is going to be horridly painful too, and you'll still have bloated indexes afterwards. You might as well just live with the 50% waste, especially since if you did a full-table update once you'll probably do it again sometime. I'm having a hard time believing that VACUUM FULL really has any interesting use-case anymore. I have a client who uses temp tables heavily, hundreds of thousands of creates and drops per day. They also have long running queries. The only thing that keeps catalog bloat somewhat in check is vacuum full on bloated catalogs a few times a day. Without that pg_class, pg_attribute etc quickly balloon to thousands of pages. That's a rate of more than one create and drop per second. How does your client handle the fact that VACUUM FULL will exclusively lock those catalog tables? Without knowing more, it looks like a bit of a design issue. 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] remove flatfiles.c
On Tue, Sep 01, 2009 at 07:42:56PM -0400, Tom Lane wrote: > Greg Stark writes: > > On Wed, Sep 2, 2009 at 12:01 AM, Alvaro > > Herrera wrote: > >>> The use cases where VACUUM FULL wins currently are where storing two > >>> copies of the table and its indexes concurrently just isn't practical. > >> > >> Yeah, but then do you really need to use VACUUM FULL? If that's really > >> a problem then there ain't that many dead tuples around. > > > That's what I want to believe. But picture if you have, say a > > 1-terabyte table which is 50% dead tuples and you don't have a spare > > 1-terabytes to rewrite the whole table. > > But trying to VACUUM FULL that table is going to be horridly painful > too, and you'll still have bloated indexes afterwards. You might as > well just live with the 50% waste, especially since if you did a > full-table update once you'll probably do it again sometime. > > I'm having a hard time believing that VACUUM FULL really has any > interesting use-case anymore. I have a client who uses temp tables heavily, hundreds of thousands of creates and drops per day. They also have long running queries. The only thing that keeps catalog bloat somewhat in check is vacuum full on bloated catalogs a few times a day. Without that pg_class, pg_attribute etc quickly balloon to thousands of pages. -dg -- David Gould da...@sonic.net 510 536 1443510 282 0869 If simplicity worked, the world would be overrun with insects. -- 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] Hot Standby, max_connections and max_prepared_transactions
On Thu, 2009-09-03 at 22:22 +0300, Heikki Linnakangas wrote: > Simon Riggs wrote: > > I propose we just accept that both max_connections and > > max_prepared_transactions need to be set correctly for recovery to work. > > This will make the state transitions more robust and it will avoid > > spurious and hard to test error messages. > > > > Any objections to me removing this slice of code from the patch? > > Umm, what slice of code? I don't recall any code trying to make it work. Well, its there. Perhaps the full functionality has been clipped in recent changes, but there are still unwanted ramifications in the design that I think would be best to remove. No loss of functionality, just HS won't activate unless max_connections is set >= value on primary. Since max_prepared_transactions already has this same problem in the current code I see no reason to fuss. We can always put in more flexible code later. State change code in StartupXLog(), Snapshots disabled code in GetSnapshotData(), ProcArray->allowStandbySnapshots etc.. -- Simon Riggs www.2ndQuadrant.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] Commitfest Code Sprint with PUGs
>>> How about this: 6:00 Tuesday evening (Pacific), the three of us (and >>> anybody else) agree to be around a table with laptops on, cellphones >>> ready, listening at an IRC channel. Could you assign us good patches >>> before then (like 10)? And then we commit, commit, commit. > > Sounds good, already have at least someone else from #pdxpug interested too. > :) > Just need to pick a location. Anybody have a quiet house? I can probably finagle a room here at PSU, though I would love it if someone would volunteer a house. >>> I think we should think of this as a dry run where we iron out the >>> details, and then maybe the following saturday do it again on a larger >>> scale? > > Practice run was my expectation as well. Making both dates might be > problematic for some people (:ahem: me) but we should have enough > attendee overlap to deal with that. OK, let's play followups by ear, but consider Tues Sept 15 as the date? -- 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] Hot Standby, max_connections and max_prepared_transactions
Simon Riggs wrote: > I propose we just accept that both max_connections and > max_prepared_transactions need to be set correctly for recovery to work. > This will make the state transitions more robust and it will avoid > spurious and hard to test error messages. > > Any objections to me removing this slice of code from the patch? Umm, what slice of code? I don't recall any code trying to make it work. -- 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] Commitfest Code Sprint with PUGs
On Thu, Sep 3, 2009 at 10:58 AM, Robert Haas wrote: > On Thu, Sep 3, 2009 at 1:54 PM, Webb Sprague wrote: >> Hi Josh et al, >> >> I believe we are all still interested (Selena? Gabrielle?) Heck yes! >> How about this: 6:00 Tuesday evening (Pacific), the three of us (and >> anybody else) agree to be around a table with laptops on, cellphones >> ready, listening at an IRC channel. Could you assign us good patches >> before then (like 10)? And then we commit, commit, commit. Sounds good, already have at least someone else from #pdxpug interested too. :) Just need to pick a location. Anybody have a quiet house? >> I think we should think of this as a dry run where we iron out the >> details, and then maybe the following saturday do it again on a larger >> scale? Practice run was my expectation as well. Making both dates might be problematic for some people (:ahem: me) but we should have enough attendee overlap to deal with that. gabrielle -- 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] community decision-making & 8.5
On Thu, Sep 3, 2009 at 11:27 AM, Tom Lane wrote: > Josh Berkus writes: >> Selena, Robert, Brendan, Kevin, >> One of the ideas behind the Alpha releases was to give someone other >> than the core team some practice doing releases. > > Uh, what's the point of that? The existing core team has that process > perfectly well in hand. What I thought this discussion was about was > putting more effort into long-range project planning/management. Ok! I was volunteering for work, not suggesting a process was broken. There's plenty to do around commitfest, so I'll stick to taking tasks there! -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] Triggers on columns
Peter Eisentraut writes: > On tor, 2009-09-03 at 11:19 -0400, Robert Haas wrote: >> Sure, but I don't think it makes a lot of sense to spend a lot of time >> implementing the standard behavior unless someone can provide a >> plausible use case. > One use case is porting Oracle applications. I see a lot of that used > there. The original proposer might had have other ideas. That's only a good argument if we are prepared to implement exactly Oracle's semantics for the feature ... which, frankly, I have no reason whatever to assume are exactly like the standard'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] community decision-making & 8.5
On Thu, Sep 3, 2009 at 2:27 PM, Tom Lane wrote: > Josh Berkus writes: >> Selena, Robert, Brendan, Kevin, >> One of the ideas behind the Alpha releases was to give someone other >> than the core team some practice doing releases. > > Uh, what's the point of that? The existing core team has that process > perfectly well in hand. What I thought this discussion was about was > putting more effort into long-range project planning/management. Exactly. I don't see much value in investing a lot of time and effort in something that's already working well. If someone has a complaint about the way the process for packaging and bundling releases is working, then let's hear it (on a separate thread!), but all of the discussions that we've had on this thread are about making sure that we set time lines and expectations early, stick to them, and resolve issues that come up in a timely fashion. Those are basically all process issues, and I don't see much value in throwing one technical task into the mix. ...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] community decision-making & 8.5
Josh Berkus writes: > Selena, Robert, Brendan, Kevin, > One of the ideas behind the Alpha releases was to give someone other > than the core team some practice doing releases. Uh, what's the point of that? The existing core team has that process perfectly well in hand. What I thought this discussion was about was putting more effort into long-range project planning/management. 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] Commitfest Code Sprint with PUGs
On Thu, Sep 3, 2009 at 11:02 AM, Webb Sprague wrote: >> The CommitFest is scheduled to start 9/15, so doing this on 9/8 might >> be a bit too soon. I wouldn't object to doing it a few days before >> the start of the CommitFest to flush out any patches with obvious >> problems, but I think a week ahead of time is too much. > > Yeah, I meant Tues 9/15. > >> Also, I don't think anything is going to committed as a result of this >> - the goal is to post reviewers to pgsql-hackers. > > Sounds good. +1 -- http://chesnok.com/daily - me http://endpoint.com - work -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Hot Standby, max_connections and max_prepared_transactions
We discussed earlier that HS should continue to work even if max_connections was set differently on the primary and the standby. This now gives a situation where snapshots can be allowed, then disallowed for a while, then allowed again. Complication is that this will cause some connections to fail since we take a snapshot in postinit.c. (That is the part I just noticed in my self-review). Some queries will also fail. Sometimes, not all the time. This makes both behaviour and coding more complicated and my feeling is that if we are aiming for simplicity in all areas we should remove this. Currently max_prepared_transactions needs to be set correctly in recovery also, so this complex coding doesn't actually remove the need to set some parameters correctly. Not many people change them from the default in the first place, so I don't think its a big deal. And most people use the same postgresql.conf on the standby anyway. I propose we just accept that both max_connections and max_prepared_transactions need to be set correctly for recovery to work. This will make the state transitions more robust and it will avoid spurious and hard to test error messages. Any objections to me removing this slice of code from the patch? -- Simon Riggs www.2ndQuadrant.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] Triggers on columns
On Thu, Sep 3, 2009 at 2:16 PM, Peter Eisentraut wrote: > On tor, 2009-09-03 at 11:19 -0400, Robert Haas wrote: >> Sure, but I don't think it makes a lot of sense to spend a lot of time >> implementing the standard behavior unless someone can provide a >> plausible use case. > > One use case is porting Oracle applications. I see a lot of that used > there. The original proposer might had have other ideas. Perhaps so, but his second post to the thread suggests that he didn't have the interpretation you're proposing in mind. ...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] Triggers on columns
On tor, 2009-09-03 at 11:19 -0400, Robert Haas wrote: > Sure, but I don't think it makes a lot of sense to spend a lot of time > implementing the standard behavior unless someone can provide a > plausible use case. One use case is porting Oracle applications. I see a lot of that used there. The original proposer might had have other ideas. -- 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] Commitfest Code Sprint with PUGs
> The CommitFest is scheduled to start 9/15, so doing this on 9/8 might > be a bit too soon. I wouldn't object to doing it a few days before > the start of the CommitFest to flush out any patches with obvious > problems, but I think a week ahead of time is too much. Yeah, I meant Tues 9/15. > Also, I don't think anything is going to committed as a result of this > - the goal is to post reviewers to pgsql-hackers. Sounds good. > > ...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] Commitfest Code Sprint with PUGs
On Thu, Sep 3, 2009 at 1:54 PM, Webb Sprague wrote: > Hi Josh et al, > > I believe we are all still interested (Selena? Gabrielle?) > > How about this: 6:00 Tuesday evening (Pacific), the three of us (and > anybody else) agree to be around a table with laptops on, cellphones > ready, listening at an IRC channel. Could you assign us good patches > before then (like 10)? And then we commit, commit, commit. > > I think we should think of this as a dry run where we iron out the > details, and then maybe the following saturday do it again on a larger > scale? The CommitFest is scheduled to start 9/15, so doing this on 9/8 might be a bit too soon. I wouldn't object to doing it a few days before the start of the CommitFest to flush out any patches with obvious problems, but I think a week ahead of time is too much. Also, I don't think anything is going to committed as a result of this - the goal is to post reviewers to pgsql-hackers. ...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] Commitfest Code Sprint with PUGs
Hi Josh et al, I believe we are all still interested (Selena? Gabrielle?) How about this: 6:00 Tuesday evening (Pacific), the three of us (and anybody else) agree to be around a table with laptops on, cellphones ready, listening at an IRC channel. Could you assign us good patches before then (like 10)? And then we commit, commit, commit. I think we should think of this as a dry run where we iron out the details, and then maybe the following saturday do it again on a larger scale? -W On Thu, Sep 3, 2009 at 10:23 AM, Josh Berkus wrote: > Webb, Selena, Gabrielle, > > September 15th is coming up soon. Will PDXPUG be interested in doing a > CommitFest Sprint? When can you do it? > > Let me know, because we'll want to have the sprinters claim patches early. > > -- > 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] gcc versus division-by-zero traps
On Thu, Sep 03, 2009 at 01:26:52PM -0400, Tom Lane wrote: > David Fetter writes: > > On Thu, Sep 03, 2009 at 10:24:17AM -0400, Tom Lane wrote: > >> While s390x is still not quite mainstream, at least I can get > >> access to one ;-). > > > Do you also have access to z/OS with Unix System Services? > > No, Red Hat's machines run RHEL ;-) I'm given to understand it's possible to run both on the same hardware. Cheers, David -- David Fetter 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] community decision-making & 8.5
Josh Berkus wrote: > So I think it would make sense for you guys to do Alpha2. I'm not really clear on what that means. I'm assuming that part of the goal is for us to become more intimately familiar with the details of putting together a release, documenting the process, and suggesting possible process improvements. But I think I need a little more of a broad outline, at least, of what would fall to this team. Are we talking about taking things from the end of the CF up to the point of publishing the alpha release files? We would try to cover, to the extent we can, the jobs normally done in a major release by Tom, Bruce, and others? (Partly to free them to do other work during the alpha release preparation?) Or am I totally off base in what you're talking about? -Kevin -- 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] community decision-making & 8.5
On Thu, Sep 3, 2009 at 10:21 AM, Josh Berkus wrote: > Selena, Robert, Brendan, Kevin, > > One of the ideas behind the Alpha releases was to give someone other > than the core team some practice doing releases. > > So I think it would make sense for you guys to do Alpha2. Agreed, Peter? I'm up for it! :) Like Robert as expressed on other management roles, I am not interested in doing it forever. I am certainly up for being part of a rotating team, and documenting/automating things as much as possible. -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] gcc versus division-by-zero traps
David Fetter writes: > On Thu, Sep 03, 2009 at 10:24:17AM -0400, Tom Lane wrote: >> While s390x is still not quite mainstream, at least I can get >> access to one ;-). > Do you also have access to z/OS with Unix System Services? No, Red Hat's machines run RHEL ;-) >> What I am thinking is that in the three >> functions known to exhibit the bug (int24div, int28div, int48div) >> we should do something like this: > How big would this change be? How would people know to use that > construct everywhere it's appropriate? I'm talking about patching exactly those three functions. We don't have any reports of trouble elsewhere. The long-term fix is in the compiler anyway, this is just a workaround for currently-known issues. Part of my motivation for this is to get rid of an existing hack in the Red Hat RPMs: # use -O1 on sparc64 and alpha %ifarch sparc64 alpha CFLAGS=`echo $CFLAGS| sed -e "s|-O2|-O1|g" ` %endif I believe that that is only there to prevent exactly this problem. Anyway I'd like to try removing it after patching as proposed, and then we'll find out if there are other trouble spots ... 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] community decision-making & 8.5
On Thu, Sep 3, 2009 at 1:21 PM, Josh Berkus wrote: > Selena, Robert, Brendan, Kevin, > > One of the ideas behind the Alpha releases was to give someone other > than the core team some practice doing releases. > > So I think it would make sense for you guys to do Alpha2. Agreed, Peter? I have no interest in that. ...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] community decision-making & 8.5
On Thu, 2009-09-03 at 10:21 -0700, Josh Berkus wrote: > Selena, Robert, Brendan, Kevin, > > One of the ideas behind the Alpha releases was to give someone other > than the core team some practice doing releases. > > So I think it would make sense for you guys to do Alpha2. Agreed, Peter? I think we need at least one long term contributor or core member to participate in this. Peter would you be up for a Liaison to Core and active release person? Joshua D. Drake > > -- > Josh Berkus > PostgreSQL Experts Inc. > www.pgexperts.com > -- 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] Commitfest Code Sprint with PUGs
Webb, Selena, Gabrielle, September 15th is coming up soon. Will PDXPUG be interested in doing a CommitFest Sprint? When can you do it? Let me know, because we'll want to have the sprinters claim patches early. -- 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] community decision-making & 8.5
Selena, Robert, Brendan, Kevin, One of the ideas behind the Alpha releases was to give someone other than the core team some practice doing releases. So I think it would make sense for you guys to do Alpha2. Agreed, Peter? -- 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] Triggers on columns
iog...@free.fr escribió: > A simple use case would be to update a timestamp column with > CURRENT_TIMESTAMP as instance. No, because you want to update the timestamp in all cases, whatever columns the update actually updates. -- 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] gcc versus division-by-zero traps
On Thu, Sep 03, 2009 at 10:24:17AM -0400, Tom Lane wrote:
> We have seen several previous reports of regression test failures
> due to division by zero causing SIGFPE, even though the code should
> never reach the division command:
>
> http://archives.postgresql.org/pgsql-bugs/2006-11/msg00180.php
> http://archives.postgresql.org/pgsql-bugs/2007-11/msg00032.php
> http://archives.postgresql.org/pgsql-bugs/2008-05/msg00148.php
> http://archives.postgresql.org/pgsql-general/2009-05/msg00774.php
>
> It's always been on non-mainstream architectures so it was hard to
> investigate. But I have finally been able to reproduce this:
> https://bugzilla.redhat.com/show_bug.cgi?id=520916
>
> While s390x is still not quite mainstream, at least I can get
> access to one ;-).
Do you also have access to z/OS with Unix System Services? IBM's
compiler, c89, is amazingly strict, and should help us flush out bugs. :)
> What turns out to be the case is that
> "simple" test cases like
> if (y == 0)
> single_function_call(...);
> z = x / y;
> do not show the problem; you need something pretty complex in the
> if-command. Like, say, an ereport() construct. So that's why the gcc
> boys haven't already had visits from mobs of villagers about this.
>
> I hope that the bug will get fixed in due course, but even if they
> respond pretty quickly it will be years before the problem disappears
> from every copy of gcc in the field. So I'm thinking that it would
> behoove us to install a workaround, now that we've characterized the
> problem sufficiently. What I am thinking is that in the three
> functions known to exhibit the bug (int24div, int28div, int48div)
> we should do something like this:
>
>
> if (arg2 == 0)
> + {
> ereport(ERROR,
> (errcode(ERRCODE_DIVISION_BY_ZERO),
>errmsg("division by zero")));
> + /* ensure compiler realizes we don't reach the division */
> + PG_RETURN_NULL();
> + }
> /* No overflow is possible */
> PG_RETURN_INT64((int64) arg1 / arg2);
>
> Thoughts?
How big would this change be? How would people know to use that
construct everywhere it's appropriate?
Cheers,
David.
--
David Fetter 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] Triggers on columns
On Thu, 3 Sep 2009, Robert Haas wrote: On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentraut wrote: On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: On Sep 3, 2009, at 7:44 AM, Peter Eisentraut wrote: The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. That is thorougly bizarre, IMO. Well, if you find that bizarre, consider the existing behavior: Why should an ON UPDATE row trigger fire when none of the values of the row's columns actually change? I think if you read TRIGGER ON UPDATE as TRIGER ON UPDATE OF then it makes some sense. Not to me. I use triggers to maintain database invariants, such as: CREATE TABLE foo (id serial, name varchar, number_of_bars integer not null default 0, primary key (id)); CREATE TABLE bar (id serial, foo_id integer not null references foo (id)); By setting up INSERT, UPDATE, and DELETE triggers on bar, I can maintain the invariant that number_of_bars for each foo is in fact the number of bars where foo_id is the id of that foo. However, in order to suppress unnecessary updates to the foo table, I have to have the update trigger check whether OLD.foo_id = NEW.foo_id before it does anything. If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... By the way, I completely agree that it would be useful to have a way to suppress triggers from firing when no columns were actually modified. But I also wouldn't argue that should be the only available behavior. Sometimes it's useful to schedule a no-op update explicitly for the purpose of firing triggers. A simple use case would be to update a timestamp column with CURRENT_TIMESTAMP as instance. ...Robert -- Guillaume (ioguix) de Rorthais -- 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] community decision-making & 8.5
On Thu, Sep 3, 2009 at 9:00 AM, Robert Haas wrote: > Yeah, I'm game, though I'm hoping not to become the guy who spends all > his time doing release planning, because I like writing code, too. > Hopefully Selena won't mind my mentioning that she sent me a private > email expressing some interest in this area, too. Not at all! My schedule is largely open this fall and winter, and I have a patch or two that I aught to finish soonish as well. -selena -- http://chesnok.com/daily - me http://endpoint.com - work -- 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] community decision-making & 8.5
"Joshua D. Drake" wrote: > /me pokes Robert Haas and Kevin Grittner I'm honored to be suggested for such a role. I'm happy to do what I can, but am reluctant to put myself too squarely in any critical path, as I have responsibility for dealing with some family health issues which make unpredictable demands on my free time and energy. If things can be arranged so that what I *can* do contributes, but my absence at times does no harm, I'm happy to help. Robert Haas is obviously an outstanding suggestion. -Kevin -- 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] community decision-making & 8.5
On Thu, 2009-09-03 at 12:00 -0400, Robert Haas wrote: > > O.k. so the second part of this, is I feel it should contain a majority > > of people who are not already being slammed into the ground by community > > work. E.g; let's get some fresh blood. It is certainly important to have > > a couple of long standing contributors involved but we have some people > > that have cropped up recently (relatively) within our community that > > could probably be overworked a bit more ;) > > > > Joshua D. Drake > > > > /me pokes Robert Haas and Kevin Grittner > > Yeah, I'm game, though I'm hoping not to become the guy who spends all > his time doing release planning, because I like writing code, too. Of course. > Hopefully Selena won't mind my mentioning that she sent me a private > email expressing some interest in this area, too. > She would definitely be a good option if she has time. I know that I would be interested and I would like to see at least one long time -hacker on board. > And /me pokes Brendan Jurd. :-) > Hah! I almost listed him. /me adds a poke to Brendan Jurd. Sincerely, 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] community decision-making & 8.5
On Thu, Sep 3, 2009 at 11:55 AM, Joshua D. Drake wrote: > On Thu, 2009-09-03 at 11:41 -0400, Robert Haas wrote: >> On Thu, Sep 3, 2009 at 11:38 AM, Joshua D. Drake >> wrote: >> > On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: >> >> On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: >> >> > Isn't "core" supposed to be the release manager? >> >> >> >> The core team has historically been the release *maker* and has some >> >> done management of the final phases of that process. But I think the >> >> sentiment is growing that we need more management throughout the entire >> >> release cycle. >> > >> > O.k. so a "release" team. Cool. I am assuming the team would be more >> > directed toward upcoming major release versus minor releases to past >> > revisions. We already pretty much have that under control between -core >> > and -packagers. Yes? >> >> +1. > > O.k. so the second part of this, is I feel it should contain a majority > of people who are not already being slammed into the ground by community > work. E.g; let's get some fresh blood. It is certainly important to have > a couple of long standing contributors involved but we have some people > that have cropped up recently (relatively) within our community that > could probably be overworked a bit more ;) > > Joshua D. Drake > > /me pokes Robert Haas and Kevin Grittner Yeah, I'm game, though I'm hoping not to become the guy who spends all his time doing release planning, because I like writing code, too. Hopefully Selena won't mind my mentioning that she sent me a private email expressing some interest in this area, too. And /me pokes Brendan Jurd. :-) ...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] community decision-making & 8.5
On Thu, 2009-09-03 at 11:41 -0400, Robert Haas wrote: > On Thu, Sep 3, 2009 at 11:38 AM, Joshua D. Drake > wrote: > > On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: > >> On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: > >> > Isn't "core" supposed to be the release manager? > >> > >> The core team has historically been the release *maker* and has some > >> done management of the final phases of that process. But I think the > >> sentiment is growing that we need more management throughout the entire > >> release cycle. > > > > O.k. so a "release" team. Cool. I am assuming the team would be more > > directed toward upcoming major release versus minor releases to past > > revisions. We already pretty much have that under control between -core > > and -packagers. Yes? > > +1. O.k. so the second part of this, is I feel it should contain a majority of people who are not already being slammed into the ground by community work. E.g; let's get some fresh blood. It is certainly important to have a couple of long standing contributors involved but we have some people that have cropped up recently (relatively) within our community that could probably be overworked a bit more ;) Joshua D. Drake /me pokes Robert Haas and Kevin Grittner > > ...Robert > -- 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] community decision-making & 8.5
On Thu, Sep 3, 2009 at 11:38 AM, Joshua D. Drake wrote: > On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: >> On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: >> > Isn't "core" supposed to be the release manager? >> >> The core team has historically been the release *maker* and has some >> done management of the final phases of that process. But I think the >> sentiment is growing that we need more management throughout the entire >> release cycle. > > O.k. so a "release" team. Cool. I am assuming the team would be more > directed toward upcoming major release versus minor releases to past > revisions. We already pretty much have that under control between -core > and -packagers. Yes? +1. ...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] community decision-making & 8.5
On Thu, 2009-09-03 at 07:44 +0300, Peter Eisentraut wrote: > On ons, 2009-09-02 at 12:52 -0700, Joshua D. Drake wrote: > > Isn't "core" supposed to be the release manager? > > The core team has historically been the release *maker* and has some > done management of the final phases of that process. But I think the > sentiment is growing that we need more management throughout the entire > release cycle. O.k. so a "release" team. Cool. I am assuming the team would be more directed toward upcoming major release versus minor releases to past revisions. We already pretty much have that under control between -core and -packagers. Yes? 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] Triggers on columns
On Thu, Sep 3, 2009 at 10:37 AM, Peter Eisentraut wrote: > On Thu, 2009-09-03 at 10:24 -0400, Robert Haas wrote: >> If TRIGGER ON UPDATE OF foo_id means whether the value actually >> changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id >> means whether the column was present in the update list, then it >> doesn't. Perhaps there are some use cases where we can be certain >> that we only care about whether the value was in the update list, and >> not whether it was changed, but off the top of my head it seems like >> 0% of mine would fall into that category. > > Yeah, probably. I didn't make this up; I'm just reading the > standard. ;-) > > But of course you can already do what you do, so you don't lose anything > if it turns out that this proposed feature ends up working the other > way. Sure, but I don't think it makes a lot of sense to spend a lot of time implementing the standard behavior unless someone can provide a plausible use case. If that means we have to give our non-standard feature an incompatible syntax or whatever so as not to create confusion with the "standard" behavior, then let's do that, because it sounds WAY more useful. ...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] Triggers on columns
Robert Haas wrote: On Wed, Sep 2, 2009 at 9:52 PM, Itagaki Takahiro wrote: Here is a patch to implement "Support triggers on columns" in our ToDo list. The syntax is: CREATE TRIGGER name BEFORE UPDATE OF col1, col12, ... ON tbl FOR EACH ROW EXECUTE PROCEDURE func(); I consulted the previous work following: Column-level triggers (From: Greg Sabino Mullane, Date: 2005-07-04) http://archives.postgresql.org/pgsql-patches/2005-07/msg00107.php and completed some under-construction parts. It's still arguable that we should add dependencies from column triggers to referenced columns. In the present patch, dropeed columns are just ignored and always considered as not-modified. Please grep with "TODO: (TRIGGER)" to check the issue. Comments welcome. Wow, so I wouldn't have to do this any more? IF (TG_OP = 'UPDATE') THEN IF (OLD.foo IS NOT DISTINCT FROM NEW.foo AND OLD.bar IS NOT DISTINCT FROM NEW.bar AND OLD.baz IS NOT DISTINCT FROM NEW.baz) THEN RETURN NULL; END IF; END IF; Apart from any possible gain in efficiency, the sheer savings in typing sound quite awesome. You could make it nicer with something like: row(new.foo,new.bar,new.baz) is distinct from row(old.foo,old.bar,old.baz) couldn't you? I'm actually having trouble thinking of a case where I'd find this feature very useful. 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] Triggers on columns
Hi, Robert Haas writes: > By the way, I completely agree that it would be useful to have a way > to suppress triggers from firing when no columns were actually > modified. http://www.postgresql.org/docs/8.4/static/functions-trigger.html Currently PostgreSQL provides one built in trigger function, suppress_redundant_updates_trigger, which will prevent any update that does not actually change the data in the row from taking place, in contrast to the normal behaviour which always performs the update regardless of whether or not the data has changed. (This normal behaviour makes updates run faster, since no checking is required, and is also useful in certain cases.) ... The suppress_redundant_updates_trigger function can be added to a table like this: CREATE TRIGGER z_min_update BEFORE UPDATE ON tablename FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger(); 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] Triggers on columns
Robert Haas wrote: > It also seems to me logically inconsistent that we would expose this > information via the CREATE TRIGGER interface but not to the trigger > function itself. From within the function, you can compare NEW and > OLD, but you get no visibility into which columns were actually > updated. And apparently now from within CREATE TRIGGER we'll have > just the opposite. Blech... Sybase provides an "if update(columnname)" syntax to allow such tests. Perhaps PostgreSQL could do something similar? > Sometimes it's useful to schedule a no-op update explicitly for the > purpose of firing triggers. Yes. It's a less frequent need, but it does exist. The thing is, if you only fire triggers if something was actually changed to a new value, you can't get to that. If you fire on all updates you can test whether there were actual changes. Of course, ideally, both would be convenient. -Kevin -- 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] Triggers on columns
On Thu, 2009-09-03 at 10:24 -0400, Robert Haas wrote: > If TRIGGER ON UPDATE OF foo_id means whether the value actually > changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id > means whether the column was present in the update list, then it > doesn't. Perhaps there are some use cases where we can be certain > that we only care about whether the value was in the update list, and > not whether it was changed, but off the top of my head it seems like > 0% of mine would fall into that category. Yeah, probably. I didn't make this up; I'm just reading the standard. ;-) But of course you can already do what you do, so you don't lose anything if it turns out that this proposed feature ends up working the other way. > It also seems to me logically inconsistent that we would expose this > information via the CREATE TRIGGER interface but not to the trigger > function itself. From within the function, you can compare NEW and > OLD, but you get no visibility into which columns were actually > updated. And apparently now from within CREATE TRIGGER we'll have > just the opposite. Blech... Well, it might make sense to make this information available within the trigger function through new variables. -- 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] Concurrent execution of pg_relation_size and DROP TABLE
Itagaki Takahiro writes: > Is it reasonable to replace relation_open() calls to try_relation_open() ? I don't think so; that's just papering over one form of the problem, and who's to say that an error is not desirable when a wrong OID is given? In general there's always a risk of concurrency problems. 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] Triggers on columns
On Thu, Sep 3, 2009 at 9:51 AM, Peter Eisentraut wrote: > On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: >> On Sep 3, 2009, at 7:44 AM, Peter Eisentraut wrote: >> > The SQL standard specifies that a trigger is fired if the column is >> > mentioned in the UPDATE statement, independent of whether the value is >> > actually changed through the update. >> >> That is thorougly bizarre, IMO. > > Well, if you find that bizarre, consider the existing behavior: Why > should an ON UPDATE row trigger fire when none of the values of the > row's columns actually change? I think if you read > > TRIGGER ON UPDATE > > as > > TRIGER ON UPDATE OF > > then it makes some sense. Not to me. I use triggers to maintain database invariants, such as: CREATE TABLE foo (id serial, name varchar, number_of_bars integer not null default 0, primary key (id)); CREATE TABLE bar (id serial, foo_id integer not null references foo (id)); By setting up INSERT, UPDATE, and DELETE triggers on bar, I can maintain the invariant that number_of_bars for each foo is in fact the number of bars where foo_id is the id of that foo. However, in order to suppress unnecessary updates to the foo table, I have to have the update trigger check whether OLD.foo_id = NEW.foo_id before it does anything. If TRIGGER ON UPDATE OF foo_id means whether the value actually changed, then I can skip the check. If TRIGGER ON UPDATE OF foo_id means whether the column was present in the update list, then it doesn't. Perhaps there are some use cases where we can be certain that we only care about whether the value was in the update list, and not whether it was changed, but off the top of my head it seems like 0% of mine would fall into that category. It also seems to me logically inconsistent that we would expose this information via the CREATE TRIGGER interface but not to the trigger function itself. From within the function, you can compare NEW and OLD, but you get no visibility into which columns were actually updated. And apparently now from within CREATE TRIGGER we'll have just the opposite. Blech... By the way, I completely agree that it would be useful to have a way to suppress triggers from firing when no columns were actually modified. But I also wouldn't argue that should be the only available behavior. Sometimes it's useful to schedule a no-op update explicitly for the purpose of firing triggers. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gcc versus division-by-zero traps
We have seen several previous reports of regression test failures
due to division by zero causing SIGFPE, even though the code
should never reach the division command:
http://archives.postgresql.org/pgsql-bugs/2006-11/msg00180.php
http://archives.postgresql.org/pgsql-bugs/2007-11/msg00032.php
http://archives.postgresql.org/pgsql-bugs/2008-05/msg00148.php
http://archives.postgresql.org/pgsql-general/2009-05/msg00774.php
It's always been on non-mainstream architectures so it was hard
to investigate. But I have finally been able to reproduce this:
https://bugzilla.redhat.com/show_bug.cgi?id=520916
While s390x is still not quite mainstream, at least I can get
access to one ;-). What turns out to be the case is that
"simple" test cases like
if (y == 0)
single_function_call(...);
z = x / y;
do not show the problem; you need something pretty complex in the
if-command. Like, say, an ereport() construct. So that's why the gcc
boys haven't already had visits from mobs of villagers about this.
I hope that the bug will get fixed in due course, but even if they
respond pretty quickly it will be years before the problem disappears
from every copy of gcc in the field. So I'm thinking that it would
behoove us to install a workaround, now that we've characterized the
problem sufficiently. What I am thinking is that in the three
functions known to exhibit the bug (int24div, int28div, int48div)
we should do something like this:
if (arg2 == 0)
+ {
ereport(ERROR,
(errcode(ERRCODE_DIVISION_BY_ZERO),
errmsg("division by zero")));
+ /* ensure compiler realizes we don't reach the division */
+ PG_RETURN_NULL();
+ }
/* No overflow is possible */
PG_RETURN_INT64((int64) arg1 / arg2);
Thoughts?
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] Feature request: DEFAULT as input value of function argument
Hello defaults are supported in 8.4 regards Pavel Stehule 2009/9/3 Sergey Konoplev : >> IMHO convenient solution is to make possible to specify something like >> COLUMN_DEFAULT as input value of function. >> >> I wonder if it's possible. >> > > So, what do you think of it? > > -- > Regards, > Sergey Konoplev > -- > PostgreSQL articles in english & russian > http://gray-hemp.blogspot.com/search/label/postgresql/ > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- 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] Triggers on columns
On Thu, 2009-09-03 at 07:57 -0400, Robert Haas wrote: > On Sep 3, 2009, at 7:44 AM, Peter Eisentraut wrote: > > The SQL standard specifies that a trigger is fired if the column is > > mentioned in the UPDATE statement, independent of whether the value is > > actually changed through the update. > > That is thorougly bizarre, IMO. Well, if you find that bizarre, consider the existing behavior: Why should an ON UPDATE row trigger fire when none of the values of the row's columns actually change? I think if you read TRIGGER ON UPDATE as TRIGER ON UPDATE OF then it makes some sense. -- 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] SELECT ... FOR UPDATE [WAIT integer | NOWAIT] for 8.5
Boszormenyi Zoltan írta:
> Alvaro Herrera írta:
>
>> Boszormenyi Zoltan wrote:
>>
>>
>>
>>> The vague consensus for syntax options was that the GUC
>>> 'lock_timeout' and WAIT [N] extension (wherever NOWAIT
>>> is allowed) both should be implemented.
>>>
>>> Behaviour would be that N seconds timeout should be
>>> applied to every lock that the statement would take.
>>>
>>>
>> In http://archives.postgresql.org/message-id/291.1242053...@sss.pgh.pa.us
>> Tom argues that lock_timeout should be sufficient. I'm not sure what
>> does WAIT [N] buy
>>
>
> Okay, we implemented only the lock_timeout GUC.
> Patch attached, hopefully in an acceptable form.
> Documentation included in the patch, lock_timeout
> works the same way as statement_timeout, takes
> value in milliseconds and 0 disables the timeout.
>
> Best regards,
> Zoltán Böszörményi
>
New patch attached. It's only regenerated for current CVS
so it should apply cleanly.
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
diff -dcrpN pgsql.ooscur/doc/src/sgml/config.sgml pgsql.locktimeout/doc/src/sgml/config.sgml
*** pgsql.ooscur/doc/src/sgml/config.sgml 2009-08-26 10:19:48.0 +0200
--- pgsql.locktimeout/doc/src/sgml/config.sgml 2009-09-03 15:41:34.0 +0200
*** COPY postgres_log FROM '/full/path/to/lo
*** 4028,4033
--- 4028,4056
+
+ lock_timeout (integer)
+
+lock_timeout configuration parameter
+
+
+
+ Abort any statement that tries to lock any rows or tables and the lock
+ has to wait more than the specified number of milliseconds, starting
+ from the time the command arrives at the server from the client.
+ If log_min_error_statement is set to ERROR or
+ lower, the statement that timed out will also be logged.
+ A value of zero (the default) turns off the limitation.
+
+
+
+ Setting lock_timeout in
+ postgresql.conf is not recommended because it
+ affects all sessions.
+
+
+
+
vacuum_freeze_table_age (integer)
diff -dcrpN pgsql.ooscur/doc/src/sgml/ref/lock.sgml pgsql.locktimeout/doc/src/sgml/ref/lock.sgml
*** pgsql.ooscur/doc/src/sgml/ref/lock.sgml 2009-01-16 11:44:56.0 +0100
--- pgsql.locktimeout/doc/src/sgml/ref/lock.sgml 2009-09-03 15:41:34.0 +0200
*** where loc
*** 39,46
NOWAIT is specified, LOCK
TABLE does not wait to acquire the desired lock: if it
cannot be acquired immediately, the command is aborted and an
!error is emitted. Once obtained, the lock is held for the
!remainder of the current transaction. (There is no UNLOCK
TABLE command; locks are always released at transaction
end.)
--- 39,49
NOWAIT is specified, LOCK
TABLE does not wait to acquire the desired lock: if it
cannot be acquired immediately, the command is aborted and an
!error is emitted. If lock_timeout is set to a value
!higher than 0, and the lock cannot be acquired under the specified
!timeout value in milliseconds, the command is aborted and an error
!is emitted. Once obtained, the lock is held for the remainder of
!the current transaction. (There is no UNLOCK
TABLE command; locks are always released at transaction
end.)
diff -dcrpN pgsql.ooscur/doc/src/sgml/ref/select.sgml pgsql.locktimeout/doc/src/sgml/ref/select.sgml
*** pgsql.ooscur/doc/src/sgml/ref/select.sgml 2009-08-31 12:55:43.0 +0200
--- pgsql.locktimeout/doc/src/sgml/ref/select.sgml 2009-09-03 15:41:34.0 +0200
*** FOR SHARE [ OF = 0)
+ {
+ int i;
+
+ for (i = 0; i < nmembers; i++)
+ {
+ TransactionId member = members[i];
+
+ debug_elog4(DEBUG2, "ConditionalMultiXactIdWait: trying %d (%u)",
+ i, member);
+ if (!TransactionIdIsCurrentTransactionId(member))
+ {
+ result = TimedXactLockTableWait(member);
+ if (!result)
+ break;
+ }
+ }
+
+ pfree(members);
+ }
+
+ return result;
+ }
+
+ /*
* CreateMultiXactId
* Make a new MultiXactId
*
diff -dcrpN pgsql.ooscur/src/backend/commands/lockcmds.c pgsql.locktimeout/src/backend/commands/lockcmds.c
*** pgsql.ooscur/src/backend/commands/lockcmds.c 2009-06-13 18:24:48.0 +0200
--- pgsql.locktimeout/src/backend/commands/lockcmds.c 2009-09-03 15:41:34.0 +0200
*** LockTableRecurse(Oid reloid, RangeVar *r
*** 65,70
--- 65,71
{
Relation rel;
AclResult aclresult;
+ bool result;
/*
* Acquire the lock. We must do this first to pro
[HACKERS] ECPG patchset
Hi,
we have updated our patchset to current 8.5 CVS.
The actual patches will be in emails coming as
answers to this one. As two patches were already included,
the remaining patches are as follows:
1. dynamic cursorname
2. sqlda support
3. describe support
4. proper out-of-scope declare/open/fetch for cursors in compat mode
Changes (the numbers indicate the patch it was made in):
1. ECPG auto-generated grammar couldn't deal with
grammar objects having both an "addon" and a "rule"
extension, emitting an extra '{' between the "addon"
and the original rule code blocks. Fix was needed for parse.pl
and now the grammar looks nicer, my previous question was
solved by it.
2. No more ecpg_compare_sqlda_with_PGresult() function, it triggered
most of the time anyway, it's cheaper to unconditionally free() and
malloc().
No more realloc(), it ruined the internal pointers inside the sqlda
structure.
A small leak fix for FETCH when a decimal or numeric was used:
> diff -durpN pgsql.dyncursor/src/interfaces/ecpg/ecpglib/data.c
> pgsql.sqlda/src/interfaces/ecpg/ecpglib/data.c
> --- pgsql.dyncursor/src/interfaces/ecpg/ecpglib/data.c 2009-08-08
> 17:19:45.0 +0200
> +++ pgsql.sqlda/src/interfaces/ecpg/ecpglib/data.c 2009-09-03
> 12:56:36.0 +0200
> @@ -554,7 +554,7 @@ ecpg_get_data(const PGresult *results, i
> else
>
> PGTYPESnumeric_to_decimal(nres, (decimal *) (var + offset * act_tuple));
>
> - free(nres);
> + PGTYPESnumeric_free(nres);
> break;
>
> case ECPGt_interval:
Now sqlda usage is valgrind-clean.
We added the possibility to extend SQLDA for multi-row FETCH,
using the ->desc_next pointer, but not implemented. Multi-row
FETCH is not available in Informix.
3. Only updated to current CVS.
4. Fixed uninitialized pointers that were discovered by valgrind and
were causing problems on HP-UX. (Small malloc()s seem to be
zeroed out by Fedora 9's glibc, but not on HP-UX.)
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] suggestion to improve planer
Hi. I hope, that this is right mailing list. SELECT date, value FROM t_event WHERE t_event.id in (SELECT id FROM t_event WHERE date < '2009-08-25' ORDER BY date DESC LIMIT 1) ORDER BY date; cost 6.4 SELECT date, value FROM t_event WHERE t_event.id = (SELECT id FROM t_event WHERE date < '2009-08-25' ORDER BY date DESC LIMIT 1) ORDER BY date; cost 6.36..6.37 Why that two query dont have equal cost? If it is not problem, try add some planer code to recognize that sublesect HAVE TO return just one row (limit 1) and in plan could be used filter/index scan instead of hash aggregate. I have also some complex query examples where cost difference is more visible. Have a nice day. -- Odborník na všetko je zlý odborník. Ja sa snažím byť výnimkou potvrdzujúcou pravidlo. -- 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] Triggers on columns
On Sep 3, 2009, at 7:44 AM, Peter Eisentraut wrote: On Thu, 2009-09-03 at 16:25 +0900, Itagaki Takahiro wrote: I'd like to check conditions by comparing actual values but not a target of UPDATE statement because I think almost user expects the former behavior. Unmodified UPDATE-targets are common case if we use a framework that generates SQL statements internally. The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. That is thorougly bizarre, IMO. ...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] Triggers on columns
On Thu, 2009-09-03 at 16:25 +0900, Itagaki Takahiro wrote: > I'd like to check conditions by comparing actual values but not > a target of UPDATE statement because I think almost user expects > the former behavior. Unmodified UPDATE-targets are common case > if we use a framework that generates SQL statements internally. The SQL standard specifies that a trigger is fired if the column is mentioned in the UPDATE statement, independent of whether the value is actually changed through the update. -- 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] Fwd: Copy out wording
On Thu, Sep 3, 2009 at 13:31, Andrew Dunstan wrote: > > > Magnus Hagander wrote: >> >> Oh, hang on, "the NULL string" refers to the copy parameter? Not a >> part of the data? I read it as "a string being NULL". Maybe something >> like "the value of the NULL string parameter" to be overly clear for >> people like me? :-) >> > > We could change: > > A NULL is output as the NULL string and is not quoted, while a data value > matching the NULL string is quoted. > > > to > > A NULL is output as the NULL parameter and is not quoted, while a non-NULL > data value whose text representation > matches the NULL parameter is quoted. > > > or something similar. Would that be better? Yes, much better IMO. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Fwd: Copy out wording
Magnus Hagander wrote: Oh, hang on, "the NULL string" refers to the copy parameter? Not a part of the data? I read it as "a string being NULL". Maybe something like "the value of the NULL string parameter" to be overly clear for people like me? :-) We could change: A NULL is output as the NULL string and is not quoted, while a data value matching the NULL string is quoted. to A NULL is output as the NULL parameter and is not quoted, while a non-NULL data value whose text representation matches the NULL parameter is quoted. or something similar. Would that be better? 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] Fwd: Copy out wording
On Thu, Sep 3, 2009 at 13:19, Andrew Dunstan wrote:
>
>
> Magnus Hagander wrote:
>>
>> Our documentation for COPY
>> (http://www.postgresql.org/docs/8.4/static/sql-copy.html) has the
>> following to say:
>> "
>> The CSV format has no standard way to distinguish a NULL value from
>> an empty string. PostgreSQL's COPY handles this by quoting. A NULL is
>> output as the NULL string and is not quoted, while a data value
>> matching the NULL string is quoted. Therefore, using the default
>> settings, a NULL is written as an unquoted empty string, while an
>> empty string is written with double quotes (""). Reading values
>> follows similar rules. You can use FORCE NOT NULL to prevent NULL
>> input comparisons for specific columns.
>> "
>>
>> Shouldn't that be:
>> "A NULL is output as the NULL string and is not quoted, while a data
>> value matching the empty string is quoted"?
>>
>> If not, then what really is the difference between a NULL and a NULL
>> string?
>>
>>
>
>
> No, it shouldn't. Let's say NULL is represented as "foo". Then a null
> between delimiters will be written as
>
> delimiter foo delimiter
>
> while the string "foo" will be
>
> delimiter quotechar foo quotechar delimiter
>
> and an empty non-null string will be
>
> delimiter delimiter
>
> unless you have FORCE QUOTE on for it, in which case it will be
>
> delimiter quotechar quotechar delimiter
>
>
> We had quite a bit of debate on the shape of CSV output at the time it was
> done (during 8.0), and that's what we came up with. It has the useful
> property that we can round-trip the data, i.e. we can read back the data we
> output without losing information about nulls, no matter what the NULL
> string is, something we have always been resistant to changing.
>
> If you think we could explain it better, by all means have a go at it. But
> your proposed change isn't accurate. Here is an illustration of the above:
Oh, hang on, "the NULL string" refers to the copy parameter? Not a
part of the data? I read it as "a string being NULL". Maybe something
like "the value of the NULL string parameter" to be overly clear for
people like me? :-)
(FWIW, I totally agree with the feature, I was just confused by the docs)
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.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] Fwd: Copy out wording
Magnus Hagander wrote:
Our documentation for COPY
(http://www.postgresql.org/docs/8.4/static/sql-copy.html) has the
following to say:
"
The CSV format has no standard way to distinguish a NULL value from
an empty string. PostgreSQL's COPY handles this by quoting. A NULL is
output as the NULL string and is not quoted, while a data value
matching the NULL string is quoted. Therefore, using the default
settings, a NULL is written as an unquoted empty string, while an
empty string is written with double quotes (""). Reading values
follows similar rules. You can use FORCE NOT NULL to prevent NULL
input comparisons for specific columns.
"
Shouldn't that be:
"A NULL is output as the NULL string and is not quoted, while a data
value matching the empty string is quoted"?
If not, then what really is the difference between a NULL and a NULL string?
No, it shouldn't. Let's say NULL is represented as "foo". Then a null
between delimiters will be written as
delimiter foo delimiter
while the string "foo" will be
delimiter quotechar foo quotechar delimiter
and an empty non-null string will be
delimiter delimiter
unless you have FORCE QUOTE on for it, in which case it will be
delimiter quotechar quotechar delimiter
We had quite a bit of debate on the shape of CSV output at the time it
was done (during 8.0), and that's what we came up with. It has the
useful property that we can round-trip the data, i.e. we can read back
the data we output without losing information about nulls, no matter
what the NULL string is, something we have always been resistant to
changing.
If you think we could explain it better, by all means have a go at it.
But your proposed change isn't accurate. Here is an illustration of the
above:
andrew=# copy (values (1, 'foo', 2),(3,null,4),(5,'',6 ) ) to
stdout null 'foo' csv header;
column1,column2,column3
1,"foo",2
3,foo,4
5,,6
andrew=# copy (values (1, 'foo', 2),(3,null,4),(5,'',6 ) ) to
stdout null 'foo' csv header force quote column2;
column1,column2,column3
1,"foo",2
3,foo,4
5,"",6
HTH
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] Feature request: DEFAULT as input value of function argument
> IMHO convenient solution is to make possible to specify something like > COLUMN_DEFAULT as input value of function. > > I wonder if it's possible. > So, what do you think of it? -- Regards, Sergey Konoplev -- PostgreSQL articles in english & russian http://gray-hemp.blogspot.com/search/label/postgresql/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Concurrent execution of pg_relation_size and DROP TABLE
I received a trouble-report that a query using pg_relation_size() ends with an error, "could not open relation". It came from DROP TABLE was executed concurrently; relation_open() used in pg_relation_size() raised an error. Is it reasonable to replace relation_open() calls to try_relation_open() ? If failed, pg_relation_size() will just return NULLs. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Fwd: Copy out wording
Crap, I just realized I sent to pgadmin hackers by mystake. Meh.
Our documentation for COPY
(http://www.postgresql.org/docs/8.4/static/sql-copy.html) has the
following to say:
"
The CSV format has no standard way to distinguish a NULL value from
an empty string. PostgreSQL's COPY handles this by quoting. A NULL is
output as the NULL string and is not quoted, while a data value
matching the NULL string is quoted. Therefore, using the default
settings, a NULL is written as an unquoted empty string, while an
empty string is written with double quotes (""). Reading values
follows similar rules. You can use FORCE NOT NULL to prevent NULL
input comparisons for specific columns.
"
Shouldn't that be:
"A NULL is output as the NULL string and is not quoted, while a data
value matching the empty string is quoted"?
If not, then what really is the difference between a NULL and a NULL string?
--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.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] Triggers on columns
KaiGai Kohei wrote: > Itagaki-san, isn't it more suitable to check rte->modifiedCols > than heap_tuple_attr_equals()? Although, this information is > not delivered to executor... I'd like to check conditions by comparing actual values but not a target of UPDATE statement because I think almost user expects the former behavior. Unmodified UPDATE-targets are common case if we use a framework that generates SQL statements internally. Anyway, we need to compare the actual values if we want to treat NEW value modifed by another trigger correctly. Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Triggers on columns
Tom Lane wrote: > exactly how, and when, are you determining whether a column has > been "modified"? I can't count the number of times somebody > has proposed simplistic and incorrect solutions to that. > Usually they forget about BEFORE triggers changing the row. There are some approaches: 1. Just check conditions in alphabetical order. Ignore subsequent modifications after the conditions are examined. 2. Recheck conditions if NEW values are modified, but triggers that have been fired already are not executed twice. 3. Column triggers are called after non-conditional UPDATE triggers and column triggers cannot modify NEW values. I like approach 2. because it is the most user-friendly. There is a possibility that another trigger changes NEW values to "unmodified" state after some conditional triggers are executed, but it could be admissible. The approach 3. seems to be the most strict, but hard to use because of the restriction. Just for reference: - Oracle Database: They support multiple triggers and UPDATE OF and WHEN clause and can modify NEW values in trigger bodies. So they must have same problems discussing here -- but I cannot find how they work around it... http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/create_trigger.htm#i2064026 - MySQL: They can modify NEW values, but no problem because they don't support UPDATE OF, WHEN clause, nor multiple triggers for each event. http://dev.mysql.com/doc/refman/5.4/en/create-trigger.html Regards, --- ITAGAKI Takahiro NTT Open Source Software Center -- 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] Triggers on columns
Tom Lane wrote:
> Itagaki Takahiro writes:
>> Sure, and I found there might be difference between "UPDATE" and
>> "UPDATE OF {all-columns}" triggers. UPDATE trigger is always fired
>> when a row is updated even if none of the columns are actually
>> modified, but UPDATE OF {all-columns} trigger is fired only when
>> at least one of the columns is modified.
>
> I'm betraying the fact that I haven't read the patch, but ...
> exactly how, and when, are you determining whether a column has
> been "modified"? I can't count the number of times somebody
> has proposed simplistic and incorrect solutions to that.
> Usually they forget about BEFORE triggers changing the row.
It uses heap_tuple_attr_equals() to check whether a certain
column is modified, or not.
Itagaki-san, isn't it more suitable to check rte->modifiedCols
than heap_tuple_attr_equals()? Although, this information is
not delivered to executor...
What is the correct behavior when UPDATE statement set a new
value but it was identical to the original value?
In this case, heap_tuple_attr_equals() cannot detect the column
is used as a target of the UPDATE.
Thanks,
--
OSS Platform Development Division, NEC
KaiGai Kohei
--
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] combined indexes with Gist - planner issues?
Hi, Hans-Juergen Schoenig -- PostgreSQL wrote: > we did some experiments with doing such a table. > the problem is if you want to allow arbitrary combinations of words > which can be modeled perfectly with FTI. > you would instantly end up with a self join with 5 relations or so - > which is again bad. > > there are too many common words to consider doing with partly with gist > and partly with a btree. How about an inverted index, either via GIN or with a custom table, such that you have the cheapest price per existing word. (That's pretty close to how full text searching itself works). Either reduce the number of words with tsearch2's stemming algorithms. Or go for trigrams right away. Split a word or search query in all its trigrams, then look up the (cheapest) price(s) per trigram and return the n least expensive ones. I've done somethings pretty similar for a customer, using the custom table approach, as integration of GIN just started back then. Even now, you need to consider the downside of that index lacking visibility information and having to recreate the index from time to time. OTOH a custom table needs a lot more manual twiddling with triggers and bulk "index" rebuilding. I guess I'd still go for a custom table today, as it's simply more flexible. Something like: CREATE TABLE cheapest_product_by_word ( trgm TEXT, cheapest_products bigint[] ); However, all of this is assuming that data (i.e. prices, products) change very rarely and it's beneficial to calculate such an intermediate lookup-table in advance. Not sure how much that's the case for you. Regards Markus Wanner -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
