Re: [HACKERS] Initial review of xslt with no limits patch
2010/8/6 David Fetter :
> On Fri, Aug 06, 2010 at 05:57:37AM +0200, Pavel Stehule wrote:
>> 2010/8/6 Andrew Dunstan :
>> > On 08/05/2010 06:56 PM, Mike Fowler wrote:
>> >> SELECT
>> >> xslt_process('cim30400'::text,
>> >> $$http://www.w3.org/1999/XSL/Transform";
>> >> version="1.0">
>> >>
>> >>
>> > [snip]
>> >>
>> >> $$::text, 'n1=v1,n2=v2,n3=v3,n4=v4,n5=v5'::text)
>> >
>> > I haven't been paying attention to this, so sorry if this has been
>> > discussed
>> > before, but it just caught my eye. Why are we passing these params as a
>> > comma-separated list rather than as an array or as a variadic list of
>> > params? This looks rather ugly. What if you want to have a param that
>> > includes a comma?
>>
>> There is probably problem in pairs - label = value. Can be nice, if we
>> can use a variadic functions for this, but I am afraid, ...
>>
>> using a variadic function isn't too much nice now
>>
>> some xslt_process(xmlsrc, 'n1=v1','n2=v2','n3=v3'
>
> This sounds like the perfect case for pulling hstore into core code. :)
I afraid so integration of hstore can break and block work on real
hash support. I would to have hash tables in core, but with usual
features and usual syntax - like Perl or PHP
Regards
Pavel
>
> Cheers,
> David.
> --
> David Fetter http://fetter.org/
> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
> Skype: davidfetter XMPP: david.fet...@gmail.com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>
> 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] Initial review of xslt with no limits patch
On Fri, Aug 06, 2010 at 05:57:37AM +0200, Pavel Stehule wrote:
> 2010/8/6 Andrew Dunstan :
> > On 08/05/2010 06:56 PM, Mike Fowler wrote:
> >> SELECT
> >> xslt_process('cim30400'::text,
> >> $$http://www.w3.org/1999/XSL/Transform";
> >> version="1.0">
> >>
> >>
> > [snip]
> >>
> >> $$::text, 'n1=v1,n2=v2,n3=v3,n4=v4,n5=v5'::text)
> >
> > I haven't been paying attention to this, so sorry if this has been discussed
> > before, but it just caught my eye. Why are we passing these params as a
> > comma-separated list rather than as an array or as a variadic list of
> > params? This looks rather ugly. What if you want to have a param that
> > includes a comma?
>
> There is probably problem in pairs - label = value. Can be nice, if we
> can use a variadic functions for this, but I am afraid, ...
>
> using a variadic function isn't too much nice now
>
> some xslt_process(xmlsrc, 'n1=v1','n2=v2','n3=v3'
This sounds like the perfect case for pulling hstore into core code. :)
Cheers,
David.
--
David Fetter http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
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: Review: Re: [PATCH] Re: [HACKERS] Adding xpath_exists function
On tis, 2010-07-27 at 16:33 -0700, David Fetter wrote: > * Do we already have it? > > Not really. There are kludges to accomplish these things, but > they're available mostly in the sense that a general-purpose > language allows you to write code to do anything a Turing machine > can do. I think this has been obsoleted by the xmlexists patch. -- 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] Initial review of xslt with no limits patch
2010/8/6 Andrew Dunstan :
>
>
> On 08/05/2010 06:56 PM, Mike Fowler wrote:
>>
>> SELECT
>> xslt_process('cim30400'::text,
>> $$http://www.w3.org/1999/XSL/Transform";
>> version="1.0">
>>
>>
> [snip]
>>
>> $$::text, 'n1=v1,n2=v2,n3=v3,n4=v4,n5=v5'::text)
>>
>>
>
> I haven't been paying attention to this, so sorry if this has been discussed
> before, but it just caught my eye. Why are we passing these params as a
> comma-separated list rather than as an array or as a variadic list of
> params? This looks rather ugly. What if you want to have a param that
> includes a comma?
>
There is probably problem in pairs - label = value. Can be nice, if we
can use a variadic functions for this, but I am afraid, ...
using a variadic function isn't too much nice now
some xslt_process(xmlsrc, 'n1=v1','n2=v2','n3=v3'
The same is true for array. Pg hasn't hash available from SQL level
I am thinking about new kind of functions - with only positionals
arguments. And internal parameter can be a array of used labels.
Regards
Pavel Stehule
> 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
[HACKERS] pgsql-hack...@news.hub.org 81% OFF on Pfizer!
http://groups.yahoo.com/group/pedranmarcksld/message
vielmehr an das Vorhandensein eines
{688 Voraussetzung fur die Befreiung verlorener Gebiete}
wenn auch noch so kleinen Restes dieses Volkes und Staates, der, im Besitz der
notigen Freiheit, nicht
nur der Trager der geistigen Gemeinschaft des gesamten Volkstums, sondern auch
der Vorbereiter des
militarischen Freiheitskampfes zu sein vermag.
Wenn ein Volk von hundert Millionen Menschen, um die staatliche Geschlossenheit
zu wahren,
gemeinsam das Joch der Sklaverei erduldet, so ist dies schlimmer, als wenn ein
solcher Staat und ein
solches Volk zertrum
--
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] Initial review of xslt with no limits patch
On 08/05/2010 06:56 PM, Mike Fowler wrote:
SELECT
xslt_process('cim30400'::text,
$$http://www.w3.org/1999/XSL/Transform";
version="1.0">
[snip]
$$::text, 'n1=v1,n2=v2,n3=v3,n4=v4,n5=v5'::text)
I haven't been paying attention to this, so sorry if this has been
discussed before, but it just caught my eye. Why are we passing these
params as a comma-separated list rather than as an array or as a
variadic list of params? This looks rather ugly. What if you want to
have a param that includes a comma?
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] Initial review of xslt with no limits patch
2010/8/6 Mike Fowler :
> Hi Pavel,
>
> On 02/08/10 09:21, Pavel Stehule wrote:
>>
>> Hello
>>
>> 2010/8/2 Mike Fowler:
>>>
>>> Hi Pavel,
>>>
>>> Currently your patch isn't applying to head, from the looks of things a
>>> function signature has changed. Can you update your patch please?
>>>
>>
>> yes - see attachment
>>
>
> Thanks, the new patch applies cleanly. However I've been attempting to run
> the parameterised XSLT this evening but to no avail. Reverting your code
> I've discovered that it does not work in the old version either.
>
> Given the complete lack of documentation (not your fault) it's always
> possible that I'm doing something wrong. Given the query below, you should
> get the XML that follows, and indeed in oXygen (a standalone XML tool) you
> do:
>
> SELECT
> xslt_process('cim30400'::text,
> $$http://www.w3.org/1999/XSL/Transform";
> version="1.0">
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> $$::text, 'n1=v1,n2=v2,n3=v3,n4=v4,n5=v5'::text)
>
>
> v1
> v2
> v3
> v4
> v5
>
>
> Sadly I get the following in both versions:
>
>
>
>
>
>
>
>
>
>
> Unless you can see anything I'm doing wrong I suggest we mark this patch
> either 'Returned with feedback' or 'Rejected'. Since contrib/xml2 is
> deprecated, perhaps a better way forward is to pull XSLT handling into core
> and fix both the apparent inability to handle parameters as well as the
> limited number of parameters.
there is some wrong, but I am not able to sey what now. But this patch
is very simply. I'll fix it today.
Pavel
>
> Regards,
>
> --
> Mike Fowler
> Registered Linux user: 379787
>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [PATCH] Re: [HACKERS] Adding xpath_exists function
Excerpts from Mike Fowler's message of mar jun 29 06:37:28 -0400 2010: > After seeing some other posts in the last couple of days, I realised I > hadn't documented the function in the SGML. I have now done so, and > added a couple of tests with XML literals. Please find the patch > attached. Now time to go correct the xmlexists patch too... Hmm, is 0 a valid node number in a xmlNodeSet? If it is, searching for the zeroth node would make the code call PG_RETURN_BOOL(0), no? -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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
[HACKERS] LockDatabaseObject vs. LockSharedObject
It seems suspicious to me that LockSharedObject() calls AcceptInvalidationMessges() and LockDatabaseObject() does not. Since the only caller of LockSharedObject() at present is AcquireDeletionLock(), I'm not sure there's an observable bug here at the moment, but then again, I'm also not sure there isn't. The call in LockSharedObject() was added here: http://archives.postgresql.org/pgsql-committers/2006-05/msg00026.php -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] MERGE Specification
Dear All, I have seen a lively discussion about the DO NOTING action in MERGE command. And, I think most people want it. So it will be added to my next patch. Before the implementation, I still have some questions to confirm: 1. If we have a DO NOTHING action specified, it should be the last WHEN clause. It must be of the NOT MATCHED cases, and it CANNOT have any additional action qualifications. Am I correct? 2. If no DO NOTHING specified, we will imply a INSERT DEFAULT VALUES action as the end of MERGE. My question is, is this action taken only for the NOT MATCHED tuples? If this is the case, then what about the MATCHED tuples that match not previous actions? Ignore them? That means we are in fact going to add two implicit WHEN clause: a) WHEN NOT MATCHED INSERT default values; b) WHEN MATCHED THEN DO NOTHING. OR, is the INSERT DEFAULT VALUES applied to ALL tuples not matter they are MATCHED or not? Besides, (I mean no offense, but) can this method really avoid losing row? So far as I know, the DEFAULT values for table attributes are defined when the table is created and no variables are allowed in the default value expressions. That means, they are usually constants or simple serial numbers. Image that we have a MERGE command that has thousands of NOT MATCHED tuples going to the implicit action. Then, the target table will inserted with thousands of rows with DEAULT VALUES. These row will have similar (if not exactly the same) simple content, which contains NO information from the source table of MERGE. Is this really what we want? If it is not, then what is the use of the INSERT DEFAULT VALUES action? Regards
Re: [HACKERS] Concurrent MERGE
>> I've never had the deadlock detector successfully deal with the above. >> Let alone a 4-way. > Hm. I have seen 5way deadlocks getting resolved just recently. I can > find the relevant if you find it interesting, but I doubt it is. Ah, I didn't know that it was even *supposed* to resolve larger-than-2-way deadlocks, so I didn't attempt to look for more granular information. Next time I need to resolve one of these, I'll get analysis information about exactly which kinds of locks are being held where. I've seen it happen at multiple sites running 8.3 and 8.4, so whatever code is supposed to resolve circular deadlocks doesn't work all the time. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] Concurrent MERGE
Josh Berkus writes: >> Hm? Please explain what you're talking about. > Transaction A locks 1 and wants a lock on 2 > Transaction B locks 2 and wants a lock on 3 > Transaction C locks 3 and wants a lock on 1 > I've never had the deadlock detector successfully deal with the above. > Let alone a 4-way. >> Not sure I believe this either; one deadlock kills one transaction. >> If you lose multiple transactions I think you had multiple deadlocks. > Deadlock termination kills *all* of the transactions involved in the > deadlock; what else could it do? This is as opposed to serialization > failures, in which usually only one of the transactions involved fails. I'm not sure whose deadlock detector you're talking about, but it's not Postgres'. 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] Concurrent MERGE
On Thu, Aug 05, 2010 at 03:49:05PM -0700, Josh Berkus wrote: > > > Hm? Please explain what you're talking about. > > Transaction A locks 1 and wants a lock on 2 > Transaction B locks 2 and wants a lock on 3 > Transaction C locks 3 and wants a lock on 1 > > I've never had the deadlock detector successfully deal with the above. > Let alone a 4-way. Hm. I have seen 5way deadlocks getting resolved just recently. I can find the relevant if you find it interesting, but I doubt it is. > > Not sure I believe this either; one deadlock kills one transaction. > > If you lose multiple transactions I think you had multiple deadlocks. > > Deadlock termination kills *all* of the transactions involved in the > deadlock; what else could it do? This is as opposed to serialization > failures, in which usually only one of the transactions involved fails. Uhm: postgres=# CREATE TABLE a(); CREATE TABLE postgres=# CREATE TABLE b(); CREATE TABLE a: postgres=# BEGIN;LOCK a; b: postgres=# BEGIN;LOCK b; BEGIN;LOCK a; a: postgres=# lock b; b: ERROR: deadlock detected DETAIL: Process 12016 waits for AccessExclusiveLock on relation 24585 of database 11564; blocked by process 12011. Process 12011 waits for AccessExclusiveLock on relation 24588 of database 11564; blocked by process 12016. HINT: See server log for query details Afaik it worked like that for years. Andres -- 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 MERGE
On 06/08/10 10:49, Josh Berkus wrote: Hm? Please explain what you're talking about. Transaction A locks 1 and wants a lock on 2 Transaction B locks 2 and wants a lock on 3 Transaction C locks 3 and wants a lock on 1 I've never had the deadlock detector successfully deal with the above. Let alone a 4-way. Hmm - seems to work ok for me (8.3.11 with pgbench schema updating branches table by bid): UPDATE branches SET filler='filled' WHERE bid=:x I get transaction A succeeds, B is rolled back by the deadlock detector, C left waiting for A to commit or rollback. What do you find? Cheers Mark -- 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] including backend ID in relpath of temp rels - updated patch
Robert Haas writes: > [ BackendRelFileNode patch ] One thing that I find rather distressing about this is the 25% bloat in sizeof(SharedInvalidationMessage). Couldn't we avoid that? Is it really necessary to *ever* send an SI message for a backend-local rel? I agree that one needs to send relcache inval sometimes for temp rels, but I don't see why each backend couldn't interpret that as a flush on its own local version. 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] Initial review of xslt with no limits patch
Hi Pavel,
On 02/08/10 09:21, Pavel Stehule wrote:
Hello
2010/8/2 Mike Fowler:
Hi Pavel,
Currently your patch isn't applying to head, from the looks of things a
function signature has changed. Can you update your patch please?
yes - see attachment
Thanks, the new patch applies cleanly. However I've been attempting to
run the parameterised XSLT this evening but to no avail. Reverting your
code I've discovered that it does not work in the old version either.
Given the complete lack of documentation (not your fault) it's always
possible that I'm doing something wrong. Given the query below, you
should get the XML that follows, and indeed in oXygen (a standalone XML
tool) you do:
SELECT
xslt_process('cim30400'::text,
$$http://www.w3.org/1999/XSL/Transform";
version="1.0">
$$::text, 'n1=v1,n2=v2,n3=v3,n4=v4,n5=v5'::text)
v1
v2
v3
v4
v5
Sadly I get the following in both versions:
Unless you can see anything I'm doing wrong I suggest we mark this patch
either 'Returned with feedback' or 'Rejected'. Since contrib/xml2 is
deprecated, perhaps a better way forward is to pull XSLT handling into
core and fix both the apparent inability to handle parameters as well as
the limited number of parameters.
Regards,
--
Mike Fowler
Registered Linux user: 379787
--
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 MERGE
> Hm? Please explain what you're talking about. Transaction A locks 1 and wants a lock on 2 Transaction B locks 2 and wants a lock on 3 Transaction C locks 3 and wants a lock on 1 I've never had the deadlock detector successfully deal with the above. Let alone a 4-way. > Not sure I believe this either; one deadlock kills one transaction. > If you lose multiple transactions I think you had multiple deadlocks. Deadlock termination kills *all* of the transactions involved in the deadlock; what else could it do? This is as opposed to serialization failures, in which usually only one of the transactions involved fails. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] Concurrent MERGE
Josh Berkus writes: > Yes; it's a major project. Our detector works pretty well for deadlocks > which are 2-process locks or even several processes all locking against > the same first process. However, triangular and quadralateral deadlocks > (which I've seen more than once) it completely cannot handle, Hm? Please explain what you're talking about. > and some > types of activity which can cause deadlocks (like autovacuum or DDL > activity) also seem to be outside its purview. There's some known issues with deadlocks involving LWLocks as well as regular locks, which I agree aren't fixable without some significant rework. But I don't believe there's anything fundamentally wrong with the deadlock detector --- the real problem there is stretching LWLocks beyond their design intention, namely to be used only for situations where deadlock is impossible. > Also, even where the deadlock detector does its job, it's still the most > expensive type of serialization failure: Well, that's certainly true --- you don't want deadlock recovery to be part of any high-performance path. > 3. deadlocks can, and do, result in cancelling several transactions > instead of just one; there is no "winner" which is allowed to complete. Not sure I believe this either; one deadlock kills one transaction. If you lose multiple transactions I think you had multiple deadlocks. 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] Concurrent MERGE
On 8/5/10 1:59 PM, Kevin Grittner wrote: > Oh, and if deadlocks are that broken, it's a bit scary that we have > let that go. Is it the problem that technically intractable? Yes; it's a major project. Our detector works pretty well for deadlocks which are 2-process locks or even several processes all locking against the same first process. However, triangular and quadralateral deadlocks (which I've seen more than once) it completely cannot handle, and some types of activity which can cause deadlocks (like autovacuum or DDL activity) also seem to be outside its purview. The latter is probably fixable if I can create some good test cases. However, the "circular" deadlock problem has an n! issue with detecting it. Also, even where the deadlock detector does its job, it's still the most expensive type of serialization failure: 1. the detector will wait at least 1 second to check, so we're usually looking at a couple seconds to resolve the deadlock; 2. since deadlocks don't happen in testing, most applicaiton error handling isn't set up for them; 3. deadlocks can, and do, result in cancelling several transactions instead of just one; there is no "winner" which is allowed to complete. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] PL/pgSQL EXECUTE '..' USING with unknown
Andrew Dunstan writes: > On 08/05/2010 05:11 PM, Tom Lane wrote: >> This example doesn't seem terribly compelling. Why would you bother >> using USING with constants? > In a more complex example you might use $1 in more than one place in the > query. Well, that's better than no justification, but it's still pretty weak. A bigger problem is that doing anything like this will require reversing the logical path of causation in EXECUTE USING. Right now, we evaluate the USING expressions first, and then their types feed forward into parsing the EXECUTE string. What Heikki is suggesting requires reversing that, at least to some extent. I'm not convinced it's possible without breaking other cases that are more important. 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] Patch to show individual statement latencies in pgbench output
On Aug4, 2010, at 13:58 , Florian Pflug wrote: > On Aug3, 2010, at 21:16 , Greg Smith wrote: >>> That was a leftover of the trimming and comment skipping logic, which my >>> patch moves to process_command. >> >> I think there's still a trimming error here--line 195 of the new patch is >> now removing the declaration of "i" just before it sets it to zero? > Hm, I think it's just the diff thats miss-leading there. It correctly marks > the "int i" line as "removed" with a "-", but for some reason marks the "i = > 0" line (and its successors) with a "!", although they're removed too, and > not modified. > >> On the coding standard side, I noticed all your for loops are missing a >> space between the for and the (; that should get fixed. > Fixed Crap. I've messed up to the effect that the for-loop formatting fix wasn't actually in the patch. Attached is an updated version (v4). Sorry for the noise. best regards, Florian Pflug pgbench_statementlatency_v4.patch Description: Binary data -- 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] PL/pgSQL EXECUTE '..' USING with unknown
On 08/05/2010 05:11 PM, Tom Lane wrote: Heikki Linnakangas writes: There's a little problem with EXECUTE USING when the parameters are of type unknown (going back to 8.4 where EXECUTE USING was introduced): do $$ BEGIN EXECUTE 'SELECT to_date($1, $2)' USING '17-DEC-80', 'DD-MON-YY'; END; $$; ERROR: failed to find conversion function from unknown to text This example doesn't seem terribly compelling. Why would you bother using USING with constants? In a more complex example you might use $1 in more than one place in the query. 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] PL/pgSQL EXECUTE '..' USING with unknown
Heikki Linnakangas writes: > There's a little problem with EXECUTE USING when the parameters are of > type unknown (going back to 8.4 where EXECUTE USING was introduced): > do $$ > BEGIN >EXECUTE 'SELECT to_date($1, $2)' USING '17-DEC-80', 'DD-MON-YY'; > END; > $$; > ERROR: failed to find conversion function from unknown to text This example doesn't seem terribly compelling. Why would you bother using USING with constants? 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Peter Eisentraut writes: > On tor, 2010-08-05 at 14:38 -0400, Tom Lane wrote: >> Huh? The functionality proposed for removal is only that of omitting >> an explicit delimiter argument for string_agg(). Since the default >> value (an empty string) doesn't seem to be the right thing all that >> often anyway, I'm not following why you think this is a significant >> downgrade. > I just think it's useful to have the one-argument version. I understand > the functionality is available in other ways. Well, other things being equal I'd have preferred to keep the one-argument version too. But this thread has made it even clearer than before that we will get continuing bug reports if we leave the behavior alone. I don't think the ability to leave off the delimiter value is worth the amount of confusion it'll cause. 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] Concurrent MERGE
Josh Berkus wrote: > Overall, you're missing the point: there are workarounds for all > of these things now. However, they are *workarounds*, which means > that they are awkward, expensive, and/or hard to administrate; > having predicate locks would make things much easier. Well, if some form of the SSI patch goes in most of your use cases can be solved just by making the transactions serializable and letting the chips fall where they may. That's the whole point of it. I'll say it again: with true serializable transactions, if you can show that your transaction will do the right thing if there are no concurrent transactions, it will do the right thing in any mix of serializable transactions or be rolled back with a serialization failure. Full stop. No need to explicitly lock anything (with or without NOWAIT), no need to SELECT FOR UPDATE/SHARE, no need to "reserve" anything -- I consider all of those to be awkward workarounds. You just systematically retry transactions which fail with SQLSTATE '40001'. If your software isn't set up so that this can be done once, in one place, you need to rethink your design. I'm not at all clear how any form of predicate locking can help with the "blackouts" example. Perhaps if you explained how you see that working I might get it. Oh, and if deadlocks are that broken, it's a bit scary that we have let that go. Is it the problem that technically intractable? -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] PL/pgSQL EXECUTE '..' USING with unknown
Hello
2010/8/5 Heikki Linnakangas :
> There's a little problem with EXECUTE USING when the parameters are of type
> unknown (going back to 8.4 where EXECUTE USING was introduced):
>
> do $$
> BEGIN
> EXECUTE 'SELECT to_date($1, $2)' USING '17-DEC-80', 'DD-MON-YY';
> END;
> $$;
> ERROR: failed to find conversion function from unknown to text
> CONTEXT: SQL statement "SELECT to_date($1, $2)"
> PL/pgSQL function "inline_code_block" line 2 at EXECUTE statement
>
> The corresponding case works fine when used with PREPARE/EXECUTE:
>
> postgres=# PREPARE foostmt AS SELECT to_date($1, $2);
> PREPARE
> postgres=# EXECUTE foostmt ('17-DEC-80', 'DD-MON-YY');
> to_date
>
> 1980-12-17
> (1 row)
>
> With PREPARE/EXECUTE, the query is analyzed with parse_analyze_varparams()
> which allows unknown param types to be deduced from the context. Seems we
> should use that for EXECUTE USING as well, but there's no SPI interface for
> that.
>
> Thoughts? Should we add an SPI_prepare_varparams() function and use that?
>
+1 - There are similar problems with recordsets
> --
> 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
>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] PL/pgSQL EXECUTE '..' USING with unknown
There's a little problem with EXECUTE USING when the parameters are of
type unknown (going back to 8.4 where EXECUTE USING was introduced):
do $$
BEGIN
EXECUTE 'SELECT to_date($1, $2)' USING '17-DEC-80', 'DD-MON-YY';
END;
$$;
ERROR: failed to find conversion function from unknown to text
CONTEXT: SQL statement "SELECT to_date($1, $2)"
PL/pgSQL function "inline_code_block" line 2 at EXECUTE statement
The corresponding case works fine when used with PREPARE/EXECUTE:
postgres=# PREPARE foostmt AS SELECT to_date($1, $2);
PREPARE
postgres=# EXECUTE foostmt ('17-DEC-80', 'DD-MON-YY');
to_date
1980-12-17
(1 row)
With PREPARE/EXECUTE, the query is analyzed with
parse_analyze_varparams() which allows unknown param types to be deduced
from the context. Seems we should use that for EXECUTE USING as well,
but there's no SPI interface for that.
Thoughts? Should we add an SPI_prepare_varparams() function and use that?
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Concurrent MERGE
Kevin, Overall, you're missing the point: there are workarounds for all of these things now. However, they are *workarounds*, which means that they are awkward, expensive, and/or hard to administrate; having predicate locks would make things much easier. > I don't see how that can be resolved without killing something, do > you? You would just have to replace the current deadlock with some > other form of serialization failure. (And no, I will never give up > the position that a deadlock *is* one of many forms of serialization > failure.) If you're in lock nowait mode, you could get back a "can't lock" error message immediately rather than waiting for the procedure to time out. There's certainly going to be an error regardless; it's a question of how expensive it is for the application and the database server. Deadlocks are *very* expensive, especially since our deadlock detector doesn't always figure them out successfully (which means the deadlock has to be resolved by the DBA). So any other type of serialization failure or error is better than deadlocking. > I must be missing something. Please explain how this would work > *without* serialization failures. As far as I can see, you can > replace deadlocks with some other form, but I don't see the point. See above. >> (4) Blackouts: records of type "x" aren't supposed to be created >> during period "y to y1" or while procedure "z" is running. >> Predicate locking can be used to prevent this more easily than >> adding and removing a trigger. > > I would have thought that advisory locks covered this. In what way > do they fall short for this use case? Currently, I do use advisory locks for this case. However, they require a fair amount of administrative design and monitoring overhead. > H Assuming fine enough granularity (like from an index for > which a range could be locked to detect the conflict) adding a > listener to the SIRead lock handling would be good for this. Well, > as long as the transactions were serializable. Yeah, it's that last caveat which makes SIRead locks not as flexible as the theoretical predicate lock. Of course, any eventual actual implemenation of predicate locks might be equally inflexible. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On tor, 2010-08-05 at 14:38 -0400, Tom Lane wrote: > Huh? The functionality proposed for removal is only that of omitting > an explicit delimiter argument for string_agg(). Since the default > value (an empty string) doesn't seem to be the right thing all that > often anyway, I'm not following why you think this is a significant > downgrade. I just think it's useful to have the one-argument version. I understand the functionality is available in other ways. -- 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 MERGE
Josh Berkus wrote: > Anyway, here's some of the uses I'm thinking of: > > (1) Pre-insert lock: you know that you're going to insert a record > with PK="X" later in a long-running SP, so you want to lock out > other inserts of PK="X" at the beginning of the procedure. Well, if we added a listener, you could SELECT the desired key, and be notified of a conflicting insert, but that's not really what you're looking for. It does seem to me that you could solve this one by inserting the tuple and then updating it at the end, but I suppose you're looking to avoid the resulting dead tuple. Perhaps a listener could be fed to a "cancel the conflicting query" routine? In any event, the only resolution to such a conflict is to kill something, right? And right now, a write/write conflict would occur which would resolve it, you just want to be able to "reserve" the slot up front, so your transaction isn't canceled after doing a bunch of work, right? > (2) FK Locking: you plan to modify or delete a parent FK record in > this transaction, so you want to prevent any updates or inserts on > its related child records. (in my experience, FK-releated > sharelocks are the #1 cause of deadlocking). I don't see how that can be resolved without killing something, do you? You would just have to replace the current deadlock with some other form of serialization failure. (And no, I will never give up the position that a deadlock *is* one of many forms of serialization failure.) > (3) No-duplicate queueing: you want to create a queue table which > doesn't accept duplicate events, but you don't want it to be a > source of deadlocks. This is a variant of (1), but a common case. I must be missing something. Please explain how this would work *without* serialization failures. As far as I can see, you can replace deadlocks with some other form, but I don't see the point. Basically, I think we should change the deadlock SQLSTATE to '40001' and any code which needs to deal with such things treats that SQLSTATE as meaning "that wasn't a good time to try that transaction, try again in a bit." Or, if you just want it to do nothing if the row already exists, perhaps the new MERGE code would work? > (4) Blackouts: records of type "x" aren't supposed to be created > during period "y to y1" or while procedure "z" is running. > Predicate locking can be used to prevent this more easily than > adding and removing a trigger. I would have thought that advisory locks covered this. In what way do they fall short for this use case? > (5) Debugging: (variant of 4) records of type "x" keep getting > inserted in the table, and you don't know where they're coming > from. You can predicate lock to force an error and debug it. H Assuming fine enough granularity (like from an index for which a range could be locked to detect the conflict) adding a listener to the SIRead lock handling would be good for this. Well, as long as the transactions were serializable. -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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Josh Berkus writes: > On 8/5/10 12:18 PM, Robert Haas wrote: >> Could we arrange to emit this error message only when there is an >> aggregate with the same name but different arguments? > Personally, I don't see this as really necessary. Just mentioning ORDER > BY in the hint will be enough to give people the right place to look. I suppose Robert is more concerned about the possibility that we emit the ORDER BY hint when that isn't really the source of the problem. But after all, the reason it's a hint and not the primary error message is that it's not certain to be helpful. 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] Concurrent MERGE
On 8/5/10 12:33 PM, Kevin Grittner wrote: > I don't know whether this is the right time to discuss those 9 > different uses, but just so everyone knows, the SIRead locks needed > for the SSI implementation in the current serializable patch have > some characteristics which may be exactly what you want (if you want > cache invalidation or some such) or may render them totally useless > from some purposes. Yeah, I haven't wrapped my head around your stuff enough yet. I would say that having such locks available only for serializable transactions limits some of the uses I'm thinking of. Anyway, here's some of the uses I'm thinking of: (1) Pre-insert lock: you know that you're going to insert a record with PK="X" later in a long-running SP, so you want to lock out other inserts of PK="X" at the beginning of the procedure. (2) FK Locking: you plan to modify or delete a parent FK record in this transaction, so you want to prevent any updates or inserts on its related child records. (in my experience, FK-releated sharelocks are the #1 cause of deadlocking). (3) No-duplicate queueing: you want to create a queue table which doesn't accept duplicate events, but you don't want it to be a source of deadlocks. This is a variant of (1), but a common case. (4) Blackouts: records of type "x" aren't supposed to be created during period "y to y1" or while procedure "z" is running. Predicate locking can be used to prevent this more easily than adding and removing a trigger. (5) Debugging: (variant of 4) records of type "x" keep getting inserted in the table, and you don't know where they're coming from. You can predicate lock to force an error and debug it. ... that's off the top of my head. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] Concurrent MERGE
Josh Berkus wrote: > Well, we *still* want predicate locking regardless of what MERGE > supports. It's useful in about 9 different ways. I don't know whether this is the right time to discuss those 9 different uses, but just so everyone knows, the SIRead locks needed for the SSI implementation in the current serializable patch have some characteristics which may be exactly what you want (if you want cache invalidation or some such) or may render them totally useless from some purposes. (1) They don't block anything. Ever. Conflicts with writes are detected, and right now that is used to mark rw-conflicts between serializable transactions. I assume we may want to add listeners who can be signaled on such conflicts, too; but that isn't there now. (2) They are only acquired by serializable transactions. (3) They can survive the transaction which acquired them, and even the termination of the process which ran the transaction. Right now they go away when the last serializable transaction which overlapped the acquiring serializable transaction completes. If we add listeners, I assume we'd want to keep them as long as a listener was registered, probably with some timeout feature. Just so everyone knows what is and isn't there right now. -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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 8/5/10 12:18 PM, Robert Haas wrote: > Could we arrange to emit this error message only when there is an > aggregate with the same name but different arguments? Personally, I don't see this as really necessary. Just mentioning ORDER BY in the hint will be enough to give people the right place to look. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Robert Haas writes: > On Thu, Aug 5, 2010 at 3:16 PM, Tom Lane wrote: >> Next question: exactly how should the variant HINT be phrased? >> I'm inclined to drop the bit about explicit casts and make it read >> something like >> >> HINT: No aggregate function matches the given name and argument >> types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all >> regular arguments of the aggregate. > Could we arrange to emit this error message only when there is an > aggregate with the same name but different arguments? That'd move it into the category of needing significant restructuring, I'm afraid. At the moment it's not apparent that it's worth it. We're already able to limit the use of the variant hint to a pretty darn narrow set of cases, and it is only a hint after all. 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 12:16 PM, Tom Lane wrote: > HINT: No aggregate function matches the given name and argument > types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all > regular arguments of the aggregate. +1 David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 3:16 PM, Tom Lane wrote: > Josh Berkus writes: >>> Well, maybe we need to expend some more sweat on the error message then. >>> But this patch was still a prerequisite thing, because without it there >>> is no error that we can complain about. > >> Yes, I'd say an addition to the HINT is in order *assuming* at that >> stage we can tell if the user passed an ORDER BY or not. > > I was just looking at this, and realized I was mistaken earlier: the > error is issued in ParseFuncOrColumn, which already is passed the > agg_order list, so actually it's completely trivial to tell whether > a variant error message is appropriate. I suggest that we key it off > there being not just an ORDER BY, but an ORDER BY with more than one > element; if there's only one then this cannot be the source of > confusion. > > Next question: exactly how should the variant HINT be phrased? > I'm inclined to drop the bit about explicit casts and make it read > something like > > HINT: No aggregate function matches the given name and argument > types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all > regular arguments of the aggregate. Could we arrange to emit this error message only when there is an aggregate with the same name but different arguments? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Josh Berkus writes: >> Well, maybe we need to expend some more sweat on the error message then. >> But this patch was still a prerequisite thing, because without it there >> is no error that we can complain about. > Yes, I'd say an addition to the HINT is in order *assuming* at that > stage we can tell if the user passed an ORDER BY or not. I was just looking at this, and realized I was mistaken earlier: the error is issued in ParseFuncOrColumn, which already is passed the agg_order list, so actually it's completely trivial to tell whether a variant error message is appropriate. I suggest that we key it off there being not just an ORDER BY, but an ORDER BY with more than one element; if there's only one then this cannot be the source of confusion. Next question: exactly how should the variant HINT be phrased? I'm inclined to drop the bit about explicit casts and make it read something like HINT: No aggregate function matches the given name and argument types. Perhaps you misplaced ORDER BY; ORDER BY must appear after all regular arguments of the aggregate. 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
> Well, maybe we need to expend some more sweat on the error message then. > But this patch was still a prerequisite thing, because without it there > is no error that we can complain about. Yes, I'd say an addition to the HINT is in order *assuming* at that stage we can tell if the user passed an ORDER BY or not. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] remove upsert example from docs
Merlin Moncure writes: > On Thu, Aug 5, 2010 at 2:09 PM, Tom Lane wrote: >> I was not persuaded that there's a real bug in practice. IMO, his >> problem was a broken trigger not broken upsert logic. Even if we >> conclude this is unsafe, simply removing the example is of no help to >> anyone. > Well, the error handler is assuming that the unique_volation is coming > from the insert made within the loop. This is obviously not a safe > assumption in an infinite loop context. Well, that's a fair point. Perhaps we should just add a note that if there are any triggers that do additional inserts/updates, the exception catcher had better check which table the unique_violation is being reported for. >> A more useful response would be to supply a correct example. > Agree: I'd go further I would argue to supply both the 'safe' and > 'high concurrency (with caveat)' way. I'm not saying the example is > necessarily bad, just that it's maybe not a good thing to be pointing > as a learning example without qualifications. Then you get a lesson > both on upsert methods and defensive error handling (barring > objection, I'll provide that). Have at it. 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:45 AM, Tom Lane wrote: >> I'm confused: that looks like the two-argument form to me. Have I missed >> something? > > Yeah, the whole point of the thread: that's not a call of a two-argument > aggregate. It's a call of a one-argument aggregate, using a two-column > sort key to order the aggregate input rows. OH!. Wow, weird. I never noticed that. >> It confuses the shit out of me. It says "string_agg(text)" doesn't exist >> when that clearly is not the name of the function you've called. > > Well, maybe we need to expend some more sweat on the error message then. > But this patch was still a prerequisite thing, because without it there > is no error that we can complain about. Yeah, understood. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:42 AM, Thom Brown wrote: >>> LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; >>> ^ >> >> I'm confused: that looks like the two-argument form to me. Have I missed >> something? >> >>> HINT: No function matches the given name and argument types. You might >>> need to add explicit type casts. >>> >>> It's not perfect (I don't think it's practical to get the HINT to >>> read "Put the ORDER BY at the end" ;-)) but at least it should >>> get people pointed in the right direction when they do this. >> >> It confuses the shit out of me. It says "string_agg(text)" doesn't exist >> when that clearly is not the name of the function you've called. >> > > What function name do you believe was called? The message says: string_agg(f1 order by f1, ',') That looks like string_agg(text, text) or string_agg(anyelement, text). Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
"David E. Wheeler" writes: > On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: >> Applied to HEAD and 9.0. The mistaken case will now yield this: >> regression=# select string_agg(f1 order by f1, ',') from text_tbl; >> ERROR: function string_agg(text) does not exist > I'm confused: that looks like the two-argument form to me. Have I missed > something? Yeah, the whole point of the thread: that's not a call of a two-argument aggregate. It's a call of a one-argument aggregate, using a two-column sort key to order the aggregate input rows. > It confuses the shit out of me. It says "string_agg(text)" doesn't exist when > that clearly is not the name of the function you've called. Well, maybe we need to expend some more sweat on the error message then. But this patch was still a prerequisite thing, because without it there is no error that we can complain about. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] remove upsert example from docs
On Thu, Aug 5, 2010 at 2:09 PM, Tom Lane wrote: > Merlin Moncure writes: >> Attached is a patch to remove the upsert example from the pl/pgsql >> documentation. It has a serious bug (see: >> http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial >> to fix. IMNSHO, our code examples should encourage good practices and >> style. > > I was not persuaded that there's a real bug in practice. IMO, his > problem was a broken trigger not broken upsert logic. Even if we > conclude this is unsafe, simply removing the example is of no help to > anyone. Well, the error handler is assuming that the unique_volation is coming from the insert made within the loop. This is obviously not a safe assumption in an infinite loop context. It should be double checking where the error was being thrown from -- but the only way I can think of to do that is to check sqlerrm. Or you arguing that if you're doing this, all dependent triggers must not throw unique violations up the exception chain? Looping N times and punting is meh: since you have to now check in the app, why have this mechanism at all? > A more useful response would be to supply a correct example. Agree: I'd go further I would argue to supply both the 'safe' and 'high concurrency (with caveat)' way. I'm not saying the example is necessarily bad, just that it's maybe not a good thing to be pointing as a learning example without qualifications. Then you get a lesson both on upsert methods and defensive error handling (barring objection, I'll provide that). merlin -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Thu, Aug 5, 2010 at 12:25, Tom Lane wrote: > regression=# select string_agg(f1 order by f1, ',') from text_tbl; > ERROR: function string_agg(text) does not exist > LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; > ^ > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > > It's not perfect (I don't think it's practical to get the HINT to > read "Put the ORDER BY at the end" ;-)) but at least it should > get people pointed in the right direction when they do this. Not to mention I think most of the confusion came from using the 1 argument version first (with an order by) and then jumping straight to the 2 arg version. -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On 5 August 2010 19:39, David E. Wheeler wrote: > On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: > >> Applied to HEAD and 9.0. The mistaken case will now yield this: >> >> regression=# select string_agg(f1 order by f1, ',') from text_tbl; >> ERROR: function string_agg(text) does not exist >> LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; >> ^ > > I'm confused: that looks like the two-argument form to me. Have I missed > something? > >> HINT: No function matches the given name and argument types. You might need >> to add explicit type casts. >> >> It's not perfect (I don't think it's practical to get the HINT to >> read "Put the ORDER BY at the end" ;-)) but at least it should >> get people pointed in the right direction when they do this. > > It confuses the shit out of me. It says "string_agg(text)" doesn't exist when > that clearly is not the name of the function you've called. > What function name do you believe was called? -- Thom Brown Registered Linux user: #516935 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CommitFest 2010-07 week three progress report
"Kevin Grittner" wrote: New numbers on where we are with this CommitFest, at the end of the third week: 72 patches were submitted 3 patches were withdrawn (deleted) by their authors 12 patches were moved to CommitFest 2010-09 -- 57 patches in CommitFest 2010-07 -- 3 committed to 9.0 -- 54 patches for 9.1 -- 1 rejected 17 returned with feedback 21 committed for 9.1 -- 39 disposed -- 15 pending 9 ready for committer -- 6 will still need reviewer attention 1 waiting on author to respond to review -- 5 patches need review now and have a reviewer assigned Of the four patches moved to the next CF, one was because we couldn't find a reviewer for ECPG code at this time, one was because both Florian and I would like to work up some additional tests for the "serializable lock consistency" patch before sending it to a committer, and two were because Itagaki changed jobs and didn't have time during this CF to finish reviews already well underway. With only ten days to go, in order to leave time for committers to do their thing, we need to be wrapping up the remaining patches. I think we look pretty good. Of the remaining six patches, two are Work in Progress, so are not expected to go to a committer; three involve a committer, so I figure they can decide when and if it's time to return or move them, which just leaves one which is down to tweaking docs. The "WIP patch for serializable transactions with predicate locking" patch has yet to have a review posted, although there have been off-list discussions. The reviewer had to put it aside for about a week due to job pressures, but is reported back on it. (The suspense is killing me.) Last week: > 72 patches were submitted > 3 patches were withdrawn (deleted) by their authors > 8 patches were moved to CommitFest 2010-09 > -- > 61 patches in CommitFest 2010-07 > -- > 3 committed to 9.0 > -- > 58 patches for 9.1 > -- > 1 rejected > 13 returned with feedback > 12 committed for 9.1 > -- > 26 disposed > -- > 32 pending > 10 ready for committer > -- > 22 will still need reviewer attention > 7 waiting on author to respond to review > -- > 15 need review before further action > 2 "Needs Review" patches don't have a reviewer assigned > -- > 13 patches need review and have a reviewer assigned -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On Aug 5, 2010, at 11:25 AM, Tom Lane wrote: > Applied to HEAD and 9.0. The mistaken case will now yield this: > > regression=# select string_agg(f1 order by f1, ',') from text_tbl; > ERROR: function string_agg(text) does not exist > LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; > ^ I'm confused: that looks like the two-argument form to me. Have I missed something? > HINT: No function matches the given name and argument types. You might need > to add explicit type casts. > > It's not perfect (I don't think it's practical to get the HINT to > read "Put the ORDER BY at the end" ;-)) but at least it should > get people pointed in the right direction when they do this. It confuses the shit out of me. It says "string_agg(text)" doesn't exist when that clearly is not the name of the function you've called. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
Peter Eisentraut writes: > I vote against this patch. There are plenty of other places that SQL is > confusing, and this move seems excessive to me, and I find the > functionality that is proposed for removal quite useful. Huh? The functionality proposed for removal is only that of omitting an explicit delimiter argument for string_agg(). Since the default value (an empty string) doesn't seem to be the right thing all that often anyway, I'm not following why you think this is a significant downgrade. 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
On ons, 2010-08-04 at 18:19 -0400, Tom Lane wrote: > > This policy also implies that we are never going to allow default > arguments for aggregates, or at least never have any built-in ones > that use such a feature. > > By my count the following people had offered an opinion on making > this change: > for: tgl, josh, badalex, mmoncure > against: rhaas, thom > Anybody else want to vote, or change their vote after seeing the > patch? I vote against this patch. There are plenty of other places that SQL is confusing, and this move seems excessive to me, and I find the functionality that is proposed for removal quite useful. -- 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] Drop one-argument string_agg? (was Re: [BUGS] string_agg delimiter having no effect with order by)
I wrote: > Well, I forgot that an aggregate involves more than one catalog row ;-). > So it's a bit bigger patch than that, but still pretty small and safe. > See attached. Applied to HEAD and 9.0. The mistaken case will now yield this: regression=# select string_agg(f1 order by f1, ',') from text_tbl; ERROR: function string_agg(text) does not exist LINE 1: select string_agg(f1 order by f1, ',') from text_tbl; ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. It's not perfect (I don't think it's practical to get the HINT to read "Put the ORDER BY at the end" ;-)) but at least it should get people pointed in the right direction when they do this. 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] remove upsert example from docs
On 08/05/2010 02:09 PM, Tom Lane wrote: Merlin Moncure writes: Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. I was not persuaded that there's a real bug in practice. IMO, his problem was a broken trigger not broken upsert logic. Even if we conclude this is unsafe, simply removing the example is of no help to anyone. A more useful response would be to supply a correct example. Yeah, that's how it struck me just now. Maybe we should document that the inserts had better not fire a trigger that could cause an uncaught uniqueness violation exception. You could also possibly usefully prevent infinite looping in such cases by using a limited loop rather an unlimited loop. 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] Two different methods of sneaking non-immutable data into an index
mmonc...@gmail.com (Merlin Moncure) writes: > On Thu, Aug 5, 2010 at 12:59 PM, Chris Browne wrote: >> mmonc...@gmail.com (Merlin Moncure) writes: >>> On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas wrote: On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure wrote: > *) also, isn't it possible to change text cast influencing GUCs 'n' > times per statement considering any query can call a function and any > function can say, change datestyle? Shouldn't the related functions > be marked 'volatile', not stable? This is just evil. It seems to me that we might want to instead prevent functions from changing things for their callers, or postponing any such changes until the end of the statement, or, uh, something. We can't afford to put ourselves in a situation of having to make everything volatile; at least, not if "performance" is anywhere in our top 50 goals. >>> >>> yeah -- perhaps you shouldn't be allowed set things like datestyle in >>> functions then. I realize this is a corner (of the universe) case, >>> but I can't recall any other case of volatility being relaxed on >>> performance grounds... :-). Maybe a documentation warning would >>> suffice? >> >> That would cause grief for Slony-I, methinks, and probably other things >> that behave somewhat similar. >> >> The "logtrigger()" function coerces datestyle to ISO, so that when dates >> get stored, they are stored in a canonical form, irrespective of an >> individual connection's decisions on datestyle, so we don't have to >> include datestyle information as part of the replicated data. > > hm -- interesting -- couldn't that cause exactly the sort of situation > though where stability of statement is violated? It shouldn't... The data gets stored physically, on disk, in a canonical form. Why should it be "unstable" to capture data in a canonical form, when that's what gets stored on disk? -- (format nil "~...@~s" "cbbrowne" "gmail.com") The statistics on sanity are that one out of every four Americans is suffering from some form of mental illness. Think of your three best friends. If they're okay, then it's you. -- Rita Mae Brown -- 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 upsert example from docs
Merlin Moncure writes: > Attached is a patch to remove the upsert example from the pl/pgsql > documentation. It has a serious bug (see: > http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial > to fix. IMNSHO, our code examples should encourage good practices and > style. I was not persuaded that there's a real bug in practice. IMO, his problem was a broken trigger not broken upsert logic. Even if we conclude this is unsafe, simply removing the example is of no help to anyone. A more useful response would be to supply a correct example. 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] remove upsert example from docs
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > Attached is a patch to remove the upsert example from the pl/pgsql > documentation. It has a serious bug (see: > http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial > to fix. IMNSHO, our code examples should encourage good practices and > style. No, removing is a bad idea, as it's referenced from here to the North Pole and back. Better would simply be a warning about the non uniqueness of the unique constraint message. > The 'correct' way to do race free upsert is to take a table lock first > -- you don't have to loop or open a subtransaction. A high > concurrency version is nice but is more of a special case solution (it > looks like concurrent MERGE might render the issue moot anyways). I think anything doing table locks should be the "special case solution" as production systems generally avoid full table locks like the plague. The existing solution works fine as long as we explain that caveat (which is a little bit of a corner case, else we'd have heard more complaints before now). - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201008051402 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkxa/XgACgkQvJuQZxSWSsjTbACfcjrsBVXCOGUb6foARfNIztSo AswAn0bNttP8XOs/2tw6jFsSa0cZkq7e =HUcq -END PGP SIGNATURE- -- 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] Performance Enhancement/Fix for Array Utility Functions
I started taking a look at the internals of the detoast functions and I came to the conclusion that I didn't have sufficient understanding of what was going on to make the correct changes, nor sufficient time to gain that understanding. Sorry for not getting back sooner. There are a lot of different cases for the detoast stuff, and I think I would need a full understanding of toast functionality. (for example, I didn't even know there was lzma compression in postgres until one of the replies to this thread) Thanks, Mike -- Michael Lewis lolrus.org mikelikes...@gmail.com On Thu, Aug 5, 2010 at 10:52 AM, Kevin Grittner wrote: > Robert Haas wrote: > > On Wed, Jul 28, 2010 at 1:20 AM, Mike Lewis > > wrote: > >>> > >>> > 1. As-is, it's a significant *pessimization* for small arrays, > >>> > because the heap_tuple_untoast_attr_slice code does a > >>> > palloc/copy even when one is not needed because the data is > >>> > already not toasted. I think there needs to be a code path > >>> > that avoids that. > >>> > >>> This seems like it shouldn't be too hard to fix, and I think it > >>> should be fixed. > >> > >> Do you have any suggestions where to start? I do agree that this > >> should be fixed as well. I don't have too much time to dedicate > >> to this project. I can try to put in some time this weekend > >> though if it isn't looking too bad. > > > > Perhaps you could check VARATT_IS_EXTENDED. If that's true, then > > slice it, but if it's false, then just use the original datum. > > You might want to wrap that up in a function rather than cramming > > it all in the macro definition, though. > > As Mike hasn't been able to find the time to get to this yet, I'm > marking this as "Returned with Feedback". Hopefully the issues can > be addressed in the next five weeks and we can pick it up again in > the next CommitFest. > > -Kevin >
Re: [HACKERS] remove upsert example from docs
Merlin Moncure wrote: > Attached is a patch to remove the upsert example from the pl/pgsql > documentation. It has a serious bug (see: > http://www.spinics.net/lists/pgsql/msg112560.html) which is > nontrivial to fix. IMNSHO, our code examples should encourage > good practices and style. > > The 'correct' way to do race free upsert is to take a table lock > first -- you don't have to loop or open a subtransaction. A high > concurrency version is nice but is more of a special case solution > (it looks like concurrent MERGE might render the issue moot > anyways). Of course, this can be done safely without a table lock if either or both of the concurrency patches (one by Florian, one by Dan and myself) get committed, so maybe we should wait to see whether either of them makes it before adjusting the docs on this point -- at least for 9.1. Taking a broken example out of 9.0 and back branches might make sense -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] Two different methods of sneaking non-immutable data into an index
On Thu, Aug 5, 2010 at 12:59 PM, Chris Browne wrote: > mmonc...@gmail.com (Merlin Moncure) writes: >> On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas wrote: >>> On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure wrote: *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? >>> >>> This is just evil. It seems to me that we might want to instead >>> prevent functions from changing things for their callers, or >>> postponing any such changes until the end of the statement, or, uh, >>> something. We can't afford to put ourselves in a situation of having >>> to make everything volatile; at least, not if "performance" is >>> anywhere in our top 50 goals. >> >> yeah -- perhaps you shouldn't be allowed set things like datestyle in >> functions then. I realize this is a corner (of the universe) case, >> but I can't recall any other case of volatility being relaxed on >> performance grounds... :-). Maybe a documentation warning would >> suffice? > > That would cause grief for Slony-I, methinks, and probably other things > that behave somewhat similar. > > The "logtrigger()" function coerces datestyle to ISO, so that when dates > get stored, they are stored in a canonical form, irrespective of an > individual connection's decisions on datestyle, so we don't have to > include datestyle information as part of the replicated data. I think functions should be allowed to change GUCs internally, but maybe not for the context from which they were called. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] pg_stat_user_functions' notion of user
On 8/5/10 6:58 AM, Peter Eisentraut wrote: > pg_stat_user_functions has an inconsistent notion of what "user" is. > Whereas the other pg_stat_user_* views filter out non-user objects by > schema, pg_stat_user_functions checks for language "internal", which > does not successfully exclude builtin functions of language SQL. Is > there a reason for this inconsistency? Undoubtedly because function data collection already filters on function language, per the GUC setting. Not that that is a *good* reason, but I can see how we arrived a the current functionality. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] Performance Enhancement/Fix for Array Utility Functions
Robert Haas wrote: > On Wed, Jul 28, 2010 at 1:20 AM, Mike Lewis > wrote: >>> >>> > 1. As-is, it's a significant *pessimization* for small arrays, >>> > because the heap_tuple_untoast_attr_slice code does a >>> > palloc/copy even when one is not needed because the data is >>> > already not toasted. I think there needs to be a code path >>> > that avoids that. >>> >>> This seems like it shouldn't be too hard to fix, and I think it >>> should be fixed. >> >> Do you have any suggestions where to start? I do agree that this >> should be fixed as well. I don't have too much time to dedicate >> to this project. I can try to put in some time this weekend >> though if it isn't looking too bad. > > Perhaps you could check VARATT_IS_EXTENDED. If that's true, then > slice it, but if it's false, then just use the original datum. > You might want to wrap that up in a function rather than cramming > it all in the macro definition, though. As Mike hasn't been able to find the time to get to this yet, I'm marking this as "Returned with Feedback". Hopefully the issues can be addressed in the next five weeks and we can pick it up again in the next CommitFest. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] remove upsert example from docs
Attached is a patch to remove the upsert example from the pl/pgsql documentation. It has a serious bug (see: http://www.spinics.net/lists/pgsql/msg112560.html) which is nontrivial to fix. IMNSHO, our code examples should encourage good practices and style. The 'correct' way to do race free upsert is to take a table lock first -- you don't have to loop or open a subtransaction. A high concurrency version is nice but is more of a special case solution (it looks like concurrent MERGE might render the issue moot anyways). merlin Index: doc/src/sgml/plpgsql.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/plpgsql.sgml,v retrieving revision 1.156 diff -c -6 -r1.156 plpgsql.sgml *** doc/src/sgml/plpgsql.sgml 29 Jul 2010 19:34:40 - 1.156 --- doc/src/sgml/plpgsql.sgml 5 Aug 2010 17:34:54 - *** *** 2332,2382 linkend="errcodes-table"> for a list of possible error codes). The SQLERRM variable contains the error message associated with the exception. These variables are undefined outside exception handlers. - - Exceptions with UPDATE/INSERT - - - This example uses exception handling to perform either - UPDATE or INSERT, as appropriate: - - - CREATE TABLE db (a INT PRIMARY KEY, b TEXT); - - CREATE FUNCTION merge_db(key INT, data TEXT) RETURNS VOID AS - $$ - BEGIN - LOOP - -- first try to update the key - UPDATE db SET b = data WHERE a = key; - IF found THEN - RETURN; - END IF; - -- not there, so try to insert the key - -- if someone else inserts the same key concurrently, - -- we could get a unique-key failure - BEGIN - INSERT INTO db(a,b) VALUES (key, data); - RETURN; - EXCEPTION WHEN unique_violation THEN - -- do nothing, and loop to try the UPDATE again - END; - END LOOP; - END; - $$ - LANGUAGE plpgsql; - - SELECT merge_db(1, 'david'); - SELECT merge_db(1, 'dennis'); - - - - Cursors --- 2332,2343 -- 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 MERGE
I wrote: > So... No, it's not directly a problem on the server itself. I just had a thought -- the MERGE code isn't doing anything fancy with snapshots, is it? I haven't been tracking that discussion too closely or read the patch. My previous comments assume that the *snapshot* is stable for the duration of a MERGE command, at least if the transaction isolation level is serializable. -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] Concurrent MERGE
> At 2010 Dev Mtg, we put me down to work on making merge work > concurrently. That was garbled slightly and had me down as working on > predicate locking which is the general solution to the problem. Well, we *still* want predicate locking regardless of what MERGE supports. It's useful in about 9 different ways. -- -- Josh Berkus PostgreSQL Experts Inc. http://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] Concurrent MERGE
Chris Browne wrote: > robertmh...@gmail.com (Robert Haas) writes: >> I suspect Kevin's patch will solve it if using a sufficiently >> high transaction isolation level, but something else might be >> needed otherwise. However, I confess to ignorance as to the >> underlying issues? Why is MERGE worse in this regard than, say, >> UPDATE? > > It's worse than UPDATE because > - It could be an INSERT, if the data's new, but > - If the data's there, it becomes an UPDATE, but > - If some concurrent update has just DELETEd the data that's >there, it becomes an INSERT again, but > - Oops, that DELETE rolled bac, so it's an UPDATE again... > > Recurse as needed to make it more undecidable as to whether it's > really an INSERT or an UPDATE :-). Not to get too far into the serializable issues, but the server won't do any such recursion with the serializable patch. Each serializable transaction would have its own snapshot where the row was there or it wasn't, and would act accordingly. If they took conflicting actions on the same row, one of them might be rolled back with a serialization failure. The client is likely to want to retry the operation based on the SQLSTATE indicating serialization failure, which (as the patch stands now) could result in some head-banging if the client doesn't introduce some delay first. I have an optimization in mind (described on the Wiki page) which could help with that, but its impact on overall performance is uncertain, so I don't want to mess with that until we have more benchmarks in place for realistic loads which might use serializable isolation. So... No, it's not directly a problem on the server itself. Yes, a client can make it a problem by resubmitting failed queries "too quickly". But, we might be able to fix that with additional work. -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] Two different methods of sneaking non-immutable data into an index
Chris Browne writes: > mmonc...@gmail.com (Merlin Moncure) writes: >> yeah -- perhaps you shouldn't be allowed set things like datestyle in >> functions then. > That would cause grief for Slony-I, methinks, and probably other things > that behave somewhat similar. Yeah, it's not really practical (or useful IMO) to try to lock this down completely. 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] Two different methods of sneaking non-immutable data into an index
On Thu, Aug 5, 2010 at 12:59 PM, Chris Browne wrote: > mmonc...@gmail.com (Merlin Moncure) writes: >> On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas wrote: >>> On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure wrote: *) also, isn't it possible to change text cast influencing GUCs 'n' times per statement considering any query can call a function and any function can say, change datestyle? Shouldn't the related functions be marked 'volatile', not stable? >>> >>> This is just evil. It seems to me that we might want to instead >>> prevent functions from changing things for their callers, or >>> postponing any such changes until the end of the statement, or, uh, >>> something. We can't afford to put ourselves in a situation of having >>> to make everything volatile; at least, not if "performance" is >>> anywhere in our top 50 goals. >> >> yeah -- perhaps you shouldn't be allowed set things like datestyle in >> functions then. I realize this is a corner (of the universe) case, >> but I can't recall any other case of volatility being relaxed on >> performance grounds... :-). Maybe a documentation warning would >> suffice? > > That would cause grief for Slony-I, methinks, and probably other things > that behave somewhat similar. > > The "logtrigger()" function coerces datestyle to ISO, so that when dates > get stored, they are stored in a canonical form, irrespective of an > individual connection's decisions on datestyle, so we don't have to > include datestyle information as part of the replicated data. hm -- interesting -- couldn't that cause exactly the sort of situation though where stability of statement is violated? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pgsql-hack...@news.hub.org 37% OFF on Pfizer!
http://groups.yahoo.com/group/syrilalwinl/message n Bayern 646 Ludwig III. von Bayern: Gesuch Hitlers an L. 179 Lueger, Dr. Karl, BegrunderderChristlich-sozialen Partei (s. diese): L. und die Christlich-soziale Partei 58. Burgermeister von Wien 74, 107, 108, 133 Madchenerziehung im volkischen Staat 454. Vgl. Erziehung Madchenhandel und Judentum 63 Marx, Karl, Begrunder des Marxismus 234, 420, 532. Staatslehre 434 Marxismus: Verkennen 184. Kern 351. Kulturzerstorer 69. Von der westlichen Demokratie gefordert 85. M. und Demokratie 412. M. und Judentum 350 f., 352, 498. Staatsauffassung 420. V -- 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] Two different methods of sneaking non-immutable data into an index
mmonc...@gmail.com (Merlin Moncure) writes:
> On Wed, Aug 4, 2010 at 9:31 PM, Robert Haas wrote:
>> On Wed, Aug 4, 2010 at 6:43 PM, Merlin Moncure wrote:
>>> *) also, isn't it possible to change text cast influencing GUCs 'n'
>>> times per statement considering any query can call a function and any
>>> function can say, change datestyle? Shouldn't the related functions
>>> be marked 'volatile', not stable?
>>
>> This is just evil. It seems to me that we might want to instead
>> prevent functions from changing things for their callers, or
>> postponing any such changes until the end of the statement, or, uh,
>> something. We can't afford to put ourselves in a situation of having
>> to make everything volatile; at least, not if "performance" is
>> anywhere in our top 50 goals.
>
> yeah -- perhaps you shouldn't be allowed set things like datestyle in
> functions then. I realize this is a corner (of the universe) case,
> but I can't recall any other case of volatility being relaxed on
> performance grounds... :-). Maybe a documentation warning would
> suffice?
That would cause grief for Slony-I, methinks, and probably other things
that behave somewhat similar.
The "logtrigger()" function coerces datestyle to ISO, so that when dates
get stored, they are stored in a canonical form, irrespective of an
individual connection's decisions on datestyle, so we don't have to
include datestyle information as part of the replicated data.
--
output = reverse("moc.liamg" "@" "enworbbc")
http://linuxfinances.info/info/postgresql.html
Chaotic Evil means never having to say you're sorry.
--
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 MERGE
robertmh...@gmail.com (Robert Haas) writes: > On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs wrote: >> Looks like MERGE is progressing well. >> >> At 2010 Dev Mtg, we put me down to work on making merge work >> concurrently. That was garbled slightly and had me down as working on >> predicate locking which is the general solution to the problem. >> >> Do we still need me to work on concurrent MERGE, or is that included in >> the current MERGE patch (can't see it), or is that covered elsewhere >> (for example Kevin Grittner's recent work)? >> >> Still happy to do work as proposed, just checking still required. > > I suspect Kevin's patch will solve it if using a sufficiently high > transaction isolation level, but something else might be needed > otherwise. However, I confess to ignorance as to the underlying > issues? Why is MERGE worse in this regard than, say, UPDATE? It's worse than UPDATE because - It could be an INSERT, if the data's new, but - If the data's there, it becomes an UPDATE, but - If some concurrent update has just DELETEd the data that's there, it becomes an INSERT again, but - Oops, that DELETE rolled bac, so it's an UPDATE again... Recurse as needed to make it more undecidable as to whether it's really an INSERT or an UPDATE :-). -- Rules of the Evil Overlord #208. "Members of my Legion of Terror will attend seminars on Sensitivity Training. It's good public relations for them to be kind and courteous to the general population when not actively engaged in sowing chaos and destruction." -- 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 MERGE
Heikki Linnakangas wrote: >> However, I confess to ignorance as to the underlying >> issues? Why is MERGE worse in this regard than, say, UPDATE? > > MERGE can be used to implement "upsert", where a row is updated if > it exists and inserted if it doesn't. I don't think Kevin's patch > will suffice for that. You don't usually want a serialization > failure error when you run two upserts at the same time, you want > both of them to succeed, one doing an insert and the other one > doing an update. The patch Dan and I are working on won't block anything that snapshot isolation doesn't already block, so if the behavior you want is that one is held up until the other is done with something, it's not going to help. What it would do is detect whether two concurrent upserts are behaving in a way that is consistent with some serial execution of the two upserts; it would do nothing if there was a consistent interpretation, but roll one back if each appeared to come before the other in some respect. All of that, of course, with the usual caveats that it would have *no* impact unless both were run at the SERIALIZABLE isolation level, there could be false positives, and the MERGE code might possibly need to add a few calls to the functions added in the serializable patch. I hope that clarified rather than muddied the waters -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] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why?
Let's be clear. If you change the postgres code and then things break I think you're pretty much on your own. We can accept some responsibility for helping you if you're running our code, but not if you're running our code which you have subsequently mangled. If you break things you get to fix them. cheers andrew On 08/05/2010 10:20 AM, Richard wrote: Oh sorry, I missed something. I turned off the XLOG archive in code after pg_start_backup so the pg_xlog directory contains all the xlog files. And for performance purpose, I change the checkpoint type in pg_start_backup to CHECKPOINT_IMMEDIATE, does it matter? The PG log I mentioned above is the running error log not the XLOG. -- Richard 2010-08-05 - 发件人：Robert Haas 发送日期：2010-08-05 22:07:45 收件人：Richard 抄送：Andrew Dunstan; pgsql-hackers 主题：Re: [HACKERS] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why? On Thu, Aug 5, 2010 at 9:50 AM, Richard wrote: I reboot PG because I found PG recovery end point if far away from the actual end point of the XLOG on the backup directory, so I want to test if the original DB is OK. Unfortunately, I got the same PG log on the original DB. I don't unstand what you said, I missing what? The transaction logs archived during the backup? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Boszormenyi Zoltan writes: > Alvaro Herrera Ãrta: >> Since we're still in the beta phase, it makes sense to apply the fix >> right now so that it appears in 9.0. No point in waiting for 9.0.1. > It boils down to the fact that Michael doesn't have too much time > and no one else knows ECPG in depth. So... Yeah. I think what Michael is saying is he doesn't have time to review the patch now and doesn't want to hold up 9.0 until he does. We can delay 9.0 for him, or apply the patch unreviewed, or allow 9.0 to go out with this as a known bug. I don't much care for #2, given the size of the patch. 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] Concurrent MERGE
On 05/08/10 18:57, Robert Haas wrote: On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs wrote: Looks like MERGE is progressing well. At 2010 Dev Mtg, we put me down to work on making merge work concurrently. That was garbled slightly and had me down as working on predicate locking which is the general solution to the problem. Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or is that covered elsewhere (for example Kevin Grittner's recent work)? Still happy to do work as proposed, just checking still required. I suspect Kevin's patch will solve it if using a sufficiently high transaction isolation level, but something else might be needed otherwise. With truly serializable isolation I think you'll get a serialization failure error. However, I confess to ignorance as to the underlying issues? Why is MERGE worse in this regard than, say, UPDATE? MERGE can be used to implement "upsert", where a row is updated if it exists and inserted if it doesn't. I don't think Kevin's patch will suffice for that. You don't usually want a serialization failure error when you run two upserts at the same time, you want both of them to succeed, one doing an insert and the other one doing an update. -- 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] Needs Suggestion
sub...@cse.iitb.ac.in writes: > I need suggestion about how region based memory management is done in > postgres. Have you read src/backend/utils/mmgr/README ? It's old but still reasonably accurate. 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] Concurrent MERGE
On 05/08/10 18:43, Simon Riggs wrote: Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or ... It's not in the current MERGE patch. -- 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] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Alvaro Herrera írta: > Excerpts from Michael Meskes's message of jue ago 05 05:39:46 -0400 2010: > >> Sorry I thought Zoltan's explanation was clear enough. All prior ECPG >> versions were fine because dynamic cursor names were only added in 9.0. >> Apparently only this one place was missed. So this is a bug in the new >> feature, however not such a major one that it warrants the complete removal >> IMO. I'd prefer to fix this in 9.0.1. >> > > Since we're still in the beta phase, it makes sense to apply the fix > right now so that it appears in 9.0. No point in waiting for 9.0.1. > It boils down to the fact that Michael doesn't have too much time and no one else knows ECPG in depth. So... -- 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 MERGE
On Thu, Aug 5, 2010 at 11:43 AM, Simon Riggs wrote: > Looks like MERGE is progressing well. > > At 2010 Dev Mtg, we put me down to work on making merge work > concurrently. That was garbled slightly and had me down as working on > predicate locking which is the general solution to the problem. > > Do we still need me to work on concurrent MERGE, or is that included in > the current MERGE patch (can't see it), or is that covered elsewhere > (for example Kevin Grittner's recent work)? > > Still happy to do work as proposed, just checking still required. I suspect Kevin's patch will solve it if using a sufficiently high transaction isolation level, but something else might be needed otherwise. However, I confess to ignorance as to the underlying issues? Why is MERGE worse in this regard than, say, UPDATE? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Excerpts from Michael Meskes's message of jue ago 05 05:39:46 -0400 2010: > Sorry I thought Zoltan's explanation was clear enough. All prior ECPG > versions were fine because dynamic cursor names were only added in 9.0. > Apparently only this one place was missed. So this is a bug in the new > feature, however not such a major one that it warrants the complete removal > IMO. I'd prefer to fix this in 9.0.1. Since we're still in the beta phase, it makes sense to apply the fix right now so that it appears in 9.0. No point in waiting for 9.0.1. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. 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
[HACKERS] Re: Re: [HACKERS] Online backup cause boot failure, anyone know why?
Sorry, wrong word, it should be job. You mean the wrong type of checkpoint causes XLOG file recovery fail? I was confused, the XLOG files seem corrupted, is it also caused by the checkpoint type? If so , why it can do this? -- Richard 2010-08-05 - 发件人：Nicolas Barbier 发送日期：2010-08-05 23:43:22 收件人：Richard 抄送：Heikki Linnakangas; Tom Lane; pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? 2010/8/5 Richard : > All jods are done by client code, not manually. What is a jod? > I still did't not understand what you said. > What break what? The fact that you replaced CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE is the cause of your problem. You "broke" the correctness of the system by doing so. Nicolas -- 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] MERGE Specification
On Thu, Aug 5, 2010 at 7:25 AM, Simon Riggs wrote: > Also had these fragments as well, if they're still useful. Probably just > useful as pointers as to what else to change to include the docs. > > > The tests and docs were written from SQL standard, so any deviations > would need to be flagged. The idea of writing the tests first was that > they provide an objective test of whether the implementation works > according to spec. > > I'd quite like a commentary on anything that needs changing. Not saying > I will necessarily object to differences, but knowing the differences > sounds important for us. I think this is a wonderful feature. A couple of thoughts: *) Would however very much like to see RETURNING support if it's not there. Our other DML statements support it, and this one should too. wCTE if/when we get it will make the lack of it especially glaring. (OTOH, no issue if there is no rule support...they should be deprecated) *) The decision to stay on the standard and not do a 'race free' version was IMO a good one. I am starting to come around to the point of view that the *only* safe way to guarantee race free merge with the current locking model is to take an appropriate table lock. BTW, our pl/pgsql upsert example we've been encouraging people to use has a horrible bug (see: http://postgresql.1045698.n5.nabble.com/Danger-of-idiomatic-plpgsql-loop-for-merging-data-td2257700.html). If we want to rework the locking model to support anticipatory locks then fine (but that has nothing to do with MERGE specifically). merlin -- 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 MERGE
Looks like MERGE is progressing well. At 2010 Dev Mtg, we put me down to work on making merge work concurrently. That was garbled slightly and had me down as working on predicate locking which is the general solution to the problem. Do we still need me to work on concurrent MERGE, or is that included in the current MERGE patch (can't see it), or is that covered elsewhere (for example Kevin Grittner's recent work)? Still happy to do work as proposed, just checking still required. Thanks, -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- 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] Online backup cause boot failure, anyone know why?
2010/8/5 Richard : > All jods are done by client code, not manually. What is a jod? > I still did't not understand what you said. > What break what? The fact that you replaced CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE is the cause of your problem. You "broke" the correctness of the system by doing so. Nicolas -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [HACKERS] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Sorry I thought Zoltan's explanation was clear enough. All prior ECPG versions were fine because dynamic cursor names were only added in 9.0. Apparently only this one place was missed. So this is a bug in the new feature, however not such a major one that it warrants the complete removal IMO. I'd prefer to fix this in 9.0.1. Hope this cleans it up a bit. Michael "Kevin Grittner" schrieb: >Michael Meskes wrote: > >> I'd consider this a bug. > >Could you explain why? The assertions that people consider it a bug >without explanation of *why* is confusing for me. > >It sounds more like a feature of the ECPG interface that people >would really like, and which has been technically possible since >PostgreSQL 8.3, but for which nobody submitted a patch until this >week. There was some hint that a 9.0 ECPG patch added new features >which might make people expect this feature to have also been added. >If this patch isn't necessarily correct, and would be dangerous to >apply at this point, should the other patch be reverted as something >which shouldn't go out without this feature? > >-Kevin -- Sent from my Android phone with K-9 Mail. Please excuse my brevity. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Needs Suggestion
I need suggestion about how region based memory management is done in postgres. I know the concept of region based memory management and also know about the functions like memorycontextswitch(). But I am not understanding how Postgres uses hierarchical, region-based memory management. That is I am not getting the inner idea or meaning of the code. Currently, my project topic is "Parallelizing the spatial join" using POSIX threads, so I have to understand the inner details and meanings of the code. I am using "ddd" to step through its code, from there I am getting the flow of the code but not understanding the semantics of its data-structures and its region based memory management. Kindly, if anybody can give some Ideas or Suggestions. Thank You, Subham Roy, CSE, IIT Bombay. -- 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] MERGE Specification
On Thu, Aug 5, 2010 at 11:35 AM, Simon Riggs wrote: > * It appears we would be in violation of the standard on > 14.12 General Rule 6 a) i) 2) B), p.890 > (Oh, I wish I was joking, there really is such a paragraph number) Just shoot me. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Online backup cause boot failure, anyone know why?
All jods are done by client code, not manually. I still did't not understand what you said. What break what? Thandks! -- Richard 2010-08-05 - 发件人：Heikki Linnakangas 发送日期：2010-08-05 23:21:54 收件人：Richard 抄送：Tom Lane; pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? On 05/08/10 17:56, Richard wrote: > I am sorry, my English is poor. > I was confused by what you said. > What do you mean by saying "that'd break it"! Replacing CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE broke it. Don't do that. If you want to change the behavior of pg_start_backup() to perform the checkpoint immediately, change "CHECKPOINT_WAIT" to "CHECKPOINT_WAIT | CHECKPOINT_IMMEDIATE". The usual work-around though is not to hack the source code, but perform a manual CHECKPOINT just before calling pg_start_backuo(). That makes the checkpoint performed by pg_start_backup() finish quickly. -- 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] MERGE Specification
On Thu, 2010-08-05 at 18:17 +0300, Heikki Linnakangas wrote: > On 05/08/10 17:22, Simon Riggs wrote: > > On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: > > > >> In the contrary, Simon's instruction says that the DEFAULT action for > >> the tuple caught by no actions is > >> WHEN NOT MATCHED THEN INSERT DEFAULT VALUES > >> > >> From the user's point of view, these two kinds of MERGE command may > >> have not much differences. But, as the coder, I prefer current > >> setting, because we can save the implementation for a new type > >> of MERGE actions (DO NOTHING is a special merge action type). And, > >> thus, no checks and special process for it. (For example, we need to > >> make sure that DO NOTHING is the last WHEN clause, and it has no > >> additional qual. And we have to generate a INSERT DEFAULT VALUES > >> action for the MERGE command if we don't find the DO NOTHING action) > >> > >> Well, if people want the DO NOTHING action, I will add it in the > >> system. > > > > This is only important when using AND, so its not > > important for the common UPSERT case of unconditional UPDATE/INSERT. > > Assuming the default action if no other action matches is to do nothing, > then an explicit DO NOTHING is just a convenience. You can have the same > effect by having an "AND NOT " to all the actions following > the DO NOTHING action. I admit it's quite handy, but let's avoid > PostgreSQL extensions at this point. err... * DELETE is an extension to the standard, though supported by Oracle, DB2 and SQLServer and damn useful * INSERT DEFAULT VALUES is an extension to the standard, though matches options on the normal INSERT clause * rule support is an extension to the standard * It appears we would be in violation of the standard on 14.12 General Rule 6 a) i) 2) B), p.890 (Oh, I wish I was joking, there really is such a paragraph number) which specifies that the join between source and target table must not return multiple rows or must return "cardinality violation". That's pretty difficult thing to check and not very useful if it does do that. anyway, that list isn't an argument in favour of change. The argument in favour of a fail-safe default is that it is a safe coding practice that the PostgreSQL project already uses itself. The only way to write a safe MERGE SQL statement is with an extension to the standard... Principle of minimal extension would mean we only need to support RAISE ERROR, to allow people to specify they actively want statement to fail if the list of WHEN clauses does not produce a match. > > Personally, I would prefer the default action to be RAISE ERROR or > > similar. Otherwise its just too easy to get complex logic wrong and lose > > a few rows without noticing. If that was the case then you would > > definitely need DO NOTHING when you explicitly wanted to lose a few > > rows. > > > > You may think that's a bit strong, but consider that PostgreSQL uses > > default => ERROR in vast majority of switch() statements. I think its a > > safe coding practice and the annoyance of having run-time errors is much > > better than losing rows. > > > > The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not > > part of the standard AFAICS. > > What does the standard say about this? We should follow the standard, I > don't see enough reason to deviate here. I checked the standard before commenting previously and have done so again here. I can't see anything that refers to this (in SQL:2008), either way. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- 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] GROUPING SETS revisited
I found other issue :( postgres=# select name, place from cars group by grouping sets(name, place,()); name | place ---+ bmw | skoda | opel | | germany | czech rep. skoda | czech rep. skoda | germany bmw | czech rep. bmw | germany opel | czech rep. opel | germany (11 rows) postgres=# explain select name, place from cars group by grouping sets(name, place,()); QUERY PLAN -- Append (cost=36.98..88.55 rows=1230 width=54) CTE GroupingSets -> Seq Scan on cars (cost=0.00..18.30 rows=830 width=68) -> HashAggregate (cost=18.68..20.68 rows=200 width=32) -> CTE Scan on "GroupingSets" (cost=0.00..16.60 rows=830 width=32) -> HashAggregate (cost=18.68..20.68 rows=200 width=32) -> CTE Scan on "GroupingSets" (cost=0.00..16.60 rows=830 width=32) -> CTE Scan on "GroupingSets" (cost=0.00..16.60 rows=830 width=64) (8 rows) the combination of nonagregates and empty sets do a problems - because we can't ensure agg mode without aggregates or group by. But it is only minor issue 2010/8/5 Joshua Tolley : > On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote: >> So Joshua, can you look on code? > > Sure... thanks :) > > -- > Joshua Tolley / eggyknap > End Point Corporation > http://www.endpoint.com > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkxa1NsACgkQRiRfCGf1UMPwzQCgjz52P86Yx4ac4aRkKwjn8OHK > 6/EAoJ/CjXEyPaLpx39SI5bKQPz+AwBR > =Mi2J > -END PGP SIGNATURE- > > -- 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] Online backup cause boot failure, anyone know why?
On 05/08/10 17:56, Richard wrote: > I am sorry, my English is poor. > I was confused by what you said. > What do you mean by saying "that'd break it"! Replacing CHECKPOINT_WAIT with CHECKPOINT_IMMEDIATE broke it. Don't do that. If you want to change the behavior of pg_start_backup() to perform the checkpoint immediately, change "CHECKPOINT_WAIT" to "CHECKPOINT_WAIT | CHECKPOINT_IMMEDIATE". The usual work-around though is not to hack the source code, but perform a manual CHECKPOINT just before calling pg_start_backuo(). That makes the checkpoint performed by pg_start_backup() finish quickly. -- 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] MERGE Specification
On 05/08/10 17:22, Simon Riggs wrote: On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: In the contrary, Simon's instruction says that the DEFAULT action for the tuple caught by no actions is WHEN NOT MATCHED THEN INSERT DEFAULT VALUES From the user's point of view, these two kinds of MERGE command may have not much differences. But, as the coder, I prefer current setting, because we can save the implementation for a new type of MERGE actions (DO NOTHING is a special merge action type). And, thus, no checks and special process for it. (For example, we need to make sure that DO NOTHING is the last WHEN clause, and it has no additional qual. And we have to generate a INSERT DEFAULT VALUES action for the MERGE command if we don't find the DO NOTHING action) Well, if people want the DO NOTHING action, I will add it in the system. This is only important when using AND, so its not important for the common UPSERT case of unconditional UPDATE/INSERT. Assuming the default action if no other action matches is to do nothing, then an explicit DO NOTHING is just a convenience. You can have the same effect by having an "AND NOT " to all the actions following the DO NOTHING action. I admit it's quite handy, but let's avoid PostgreSQL extensions at this point. Personally, I would prefer the default action to be RAISE ERROR or similar. Otherwise its just too easy to get complex logic wrong and lose a few rows without noticing. If that was the case then you would definitely need DO NOTHING when you explicitly wanted to lose a few rows. You may think that's a bit strong, but consider that PostgreSQL uses default => ERROR in vast majority of switch() statements. I think its a safe coding practice and the annoyance of having run-time errors is much better than losing rows. The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not part of the standard AFAICS. What does the standard say about this? We should follow the standard, I don't see enough reason to deviate here. -- 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] GROUPING SETS revisited
On Thu, Aug 05, 2010 at 04:46:51PM +0200, Pavel Stehule wrote: > So Joshua, can you look on code? Sure... thanks :) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com signature.asc Description: Digital signature
Re: [HACKERS] Online backup cause boot failure, anyone know why?
I am sorry, my English is poor. I was confused by what you said. What do you mean by saying "that'd break it"! -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:44:50 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? "Richard" writes: > For perfromance purpose , I change the pg_start_backup checkpoint type from > CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much "8.3.7" as "randomly-hacked-up 8.3.7". Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. 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 -- 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] Online backup cause boot failure, anyone know why?
I am sorry, my English is poor. I was confused by what you said. What do you mean by saying "that'd break it"! -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:44:50 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? "Richard" writes: > For perfromance purpose , I change the pg_start_backup checkpoint type from > CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much "8.3.7" as "randomly-hacked-up 8.3.7". Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause bootfailure,anyone know why?
Thanks for your patience. I use XLogCtl->Insert.forcePageWrites for XLOG recycling flag. So after pg_start_backup, no more XLOG files will be recycled. And as I said above, I make a CHECKPOINT_IMMEDIATE checkpoint in pg_start_backup, instead CHECKPOINT_WAIT. That all I did to code. I wonder whether the XLOG is corrupted, because the first error is "unexpected pageaddr %X/%X in log file %u, segment %u, offset %u" .The error page addr contains a LSN 8K before it should do and I compare the two pages , they are almost the same except the last several bytes. So it should not be missing some XLOG, can be the XLOG file or buffer was corrupted. -- Richard 2010-08-05 - 发件人：Robert Haas 发送日期：2010-08-05 22:38:37 收件人：Richard 抄送：Andrew Dunstan; pgsql-hackers 主题：Re: Re: [HACKERS] Re: Re: [HACKERS] Online backup cause bootfailure,anyone know why? On Thu, Aug 5, 2010 at 10:20 AM, Richard wrote: > Oh sorry, I missed something. I turned off the XLOG archive in code after > pg_start_backup so the pg_xlog directory contains all the xlog files. > And for performance purpose, I change the checkpoint type in pg_start_backup > to CHECKPOINT_IMMEDIATE, does it matter? > The PG log I mentioned above is the running error log not the XLOG. Well, it's pretty clear that you're missing some WAL; otherwise, you wouldn't be getting an error that says "WAL ends before end time of backup dump". It's hard to speculate as to whether that's a configuration problem or a result of your custom modifications to the source code, since you haven't provided many details about either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Online backup cause boot failure, anyone know why?
"Richard" writes: > For perfromance purpose , I change the pg_start_backup checkpoint type from > CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? Oh, so this isn't so much "8.3.7" as "randomly-hacked-up 8.3.7". Yes, that'd break it, I believe. CHECKPOINT_IMMEDIATE doesn't imply waiting. 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] ECPG dynamic cursor fix for UPDATE/DELETE ... WHERE CURRENT OF :curname
Kevin Grittner írta: > Michael Meskes wrote: > >> All prior ECPG versions were fine because dynamic cursor names >> were only added in 9.0. Apparently only this one place was >> missed. So this is a bug in the new feature, however not such a >> major one that it warrants the complete removal IMO. I'd prefer to >> fix this in 9.0.1. >> As we are so late in the beta phase, we can live with that, hopefully you will find time by then to review the patch which is actually not that complex, only a bit large. The part of ECPGdo() that deals with auto-preparing statements is moved closer to calling ecpg_execute(), after the varargs are converted to stmt->inlist and ->outlist. >> Hope this cleans it up a bit. >> > > Thanks. I think I get it now. > > To restate from another angle, to confirm my understanding: UPDATE > WHERE CURRENT OF is working for cursors with the name hard-coded in > the embedded statement, which is the only way cursor names were > allowed to be specified prior to 9.0; 9.0 implements dynamic cursor > names, allowing you to use a variable for the cursor name; but this > one use of a cursor name isn't allowing a variable yet. Do I have > it right? (If so, I now see why it would be considered a bug.) > Yes, you understand it correctly. Best regards, Zoltán Böszörményi -- 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] Re: Re: [HACKERS] Online backup cause boot failure,anyone know why?
On Thu, Aug 5, 2010 at 10:20 AM, Richard wrote: > Oh sorry, I missed something. I turned off the XLOG archive in code after > pg_start_backup so the pg_xlog directory contains all the xlog files. > And for performance purpose, I change the checkpoint type in pg_start_backup > to CHECKPOINT_IMMEDIATE, does it matter? > The PG log I mentioned above is the running error log not the XLOG. Well, it's pretty clear that you're missing some WAL; otherwise, you wouldn't be getting an error that says "WAL ends before end time of backup dump". It's hard to speculate as to whether that's a configuration problem or a result of your custom modifications to the source code, since you haven't provided many details about either. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] MERGE Specification
On Thu, 2010-08-05 at 21:55 +0800, Boxuan Zhai wrote: > In the contrary, Simon's instruction says that the DEFAULT action for > the tuple caught by no actions is > WHEN NOT MATCHED THEN INSERT DEFAULT VALUES > > From the user's point of view, these two kinds of MERGE command may > have not much differences. But, as the coder, I prefer current > setting, because we can save the implementation for a new type > of MERGE actions (DO NOTHING is a special merge action type). And, > thus, no checks and special process for it. (For example, we need to > make sure that DO NOTHING is the last WHEN clause, and it has no > additional qual. And we have to generate a INSERT DEFAULT VALUES > action for the MERGE command if we don't find the DO NOTHING action) > > Well, if people want the DO NOTHING action, I will add it in the > system. This is only important when using AND , so its not important for the common UPSERT case of unconditional UPDATE/INSERT. Personally, I would prefer the default action to be RAISE ERROR or similar. Otherwise its just too easy to get complex logic wrong and lose a few rows without noticing. If that was the case then you would definitely need DO NOTHING when you explicitly wanted to lose a few rows. You may think that's a bit strong, but consider that PostgreSQL uses default => ERROR in vast majority of switch() statements. I think its a safe coding practice and the annoyance of having run-time errors is much better than losing rows. The INSERT DEFAULT VALUES was behaviour taken from another DBMS, its not part of the standard AFAICS. > Now, I have changed the RULE strategy of MERGE to the better logic. > And I am working on triggers for MERGE, which is also mentioned in the > instruction file. I will build a new patch with no long comment and > blank line around functions, and possibly contain the regress test > file and this sgml instructions in it. > > I wish we can reach a agreement on the DO NOTHING thing before my next > submission, so I can make necessary modification on my code for > it. (the new patch may be finished in one or two days, I think) > > Thanks! > > PS: I have an embarrassing question: how to view the sgml instructions > of postgres in web page form, rather than read the source code of > them? If you edit the files, as shown in the patches here, then you just need to drop into the doc/sgml/src directory and type "make". The SGML will then be compiled into HTML and you can view the resulting file directly in your web browser. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Training and Services -- 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] Online backup cause boot failure, anyone know why?
Thanks for replying. But I could't find relation between the RequestXLogSwitch function and the error I met. For perfromance purpose , I change the pg_start_backup checkpoint type from CHECKPOINT_WAIT to CHECKPOINT_IMMEDIATE, does it matter? -- Richard 2010-08-05 - 发件人：Tom Lane 发送日期：2010-08-05 22:04:30 收件人：Richard 抄送：pgsql-hackers 主题：Re: [HACKERS] Online backup cause boot failure, anyone know why? "Richard" writes: > PS : I am using PG 8.3.7 I believe there's a related bug fix in 8.3.8. BTW, -hackers is not the place for this type of question. 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] MERGE Specification
On Thu, Aug 05, 2010 at 09:55:29PM +0800, Boxuan Zhai wrote: > On Thu, Aug 5, 2010 at 7:25 PM, Simon Riggs wrote: > > > On Thu, 2010-08-05 at 12:29 +0300, Heikki Linnakangas wrote: > > > On 05/08/10 10:46, Simon Riggs wrote: > > > > On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote: > > > >> The following two files specify the behaviour of the MERGE statement > > and > > > >> how it will work in the world of PostgreSQL. > > > > > > > >> The HTML file was generated from SGML source, though the latter is not > > > >> included here for clarity. > > > > > > > > Enclose merge.sgml docs for forthcoming MERGE command, as originally > > > > written. > > > > > > Oh, cool, I wasn't aware you had written that already. Boxuan, please > > > include this in your patch, after reviewing and removing/editing > > > anything that doesn't apply to your patch. > > > Thanks a lot for the instruction file of MERGE command. I have read through > it carefully. It is really a great work. I have to admit that I am not > familiar with the sgml language, and I cannot write the instruction by > myself. It's really not super complicated. It's quite like (and ancestral to) HTML. > All features of MERGE demonstrated in this file are consistent with my > implementation, EXCEPT the DO NOTHING option. In current edition, we don't > have the DO NOTHING action type. That is, during the execution of MERGE > commands, if one tuple is not caught by any of the merge actions, it will be > ignored. In another word, DO NOTING (although cannot be specified explicitly > by user) is the DEFAULT action for tuples. > > In the contrary, Simon's instruction says that the DEFAULT action for the > tuple caught by no actions is > WHEN NOT MATCHED THEN INSERT DEFAULT VALUES I believe that the SQL standard specifies this behavior, and I don't think we have a compelling reason to do something different from what the SQL standard specifies. > Well, if people want the DO NOTHING action, I will add it in the system. That'd be great :) > Now, I have changed the RULE strategy of MERGE to the better logic. And I > am working on triggers for MERGE, which is also mentioned in the instruction > file. I will build a new patch with no long comment and blank line around > functions, and possibly contain the regress test file and this sgml > instructions in it. > > I wish we can reach a agreement on the DO NOTHING thing before my next > submission, so I can make necessary modification on my code for it. (the new > patch may be finished in one or two days, I think) > > Thanks! > > PS: I have an embarrassing question: how to view the sgml instructions of > postgres in web page form, rather than read the source code of them? After you've built postgresql, do this: cd doc/src/sgml make Then you can point a web browser at the doc/src/sgml/html/index.html (and similar) http://www.postgresql.org/docs/current/static/docguide.html has information about the tools you will need for the above to work. Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics 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
