Re: [BUGS] BUG #6583: for loop cursor
Hi Tom, Well I thought I was running version "8.4.*" but it turned out to be "8.2.14" when I did a show server_version in pgAdminIII. So, is this a known issue? Well I won't possibly be able to upgrade the version as its bundled with the DB. :-( Cordially, Biju George UST Global | Trivandrum From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: Thu 4/12/2012 8:34 PM To: Biju George (UST, IND) Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] BUG #6583: for loop cursor I wrote: > Works for me in 8.4.11 (after correcting record_var.id -> record_var.pid > at line 6). Maybe you need to update to some more recent version than > 8.4.0. Actually, I do get the described behavior when trying this in 8.3, which does not have FOR-over-cursor. So maybe the more appropriate question is whether you're really running 8.4.anything. regards, tom lane
Re: [BUGS] BUG #6585: Can't compile from source code
But 9.0.7 compiles fine. 8.4.11 too. On Thu, Apr 12, 2012 at 03:55:01PM -0400, Tom Lane wrote: > m...@akamit.com writes: > > Operating system: AIX 7.1.0 > > While compiling from source the following error stops the compilation > > process > > > ld: 0711-317 ERROR: Undefined symbol: .mbstowcs_l > > Hm. Our configure script assumes that if wcstombs_l() is available then > mbstowcs_l() is too. Is that not the case on AIX? It would be pretty > bizarre if not. > > regards, tom lane -- С Уважением, Андрей. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6585: Can't compile from source code
Andrey Mitroshin writes: > But 9.0.7 compiles fine. 8.4.11 too. That's fairly irrelevant, since the code that wants to use wcstombs_l() and mbstowcs_l() is new in 9.1 (it's part of COLLATE-clause support). regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6572: The example of SPI_execute is bogus
On Thu, Apr 5, 2012 at 2:39 AM, Hitoshi Harada wrote:
> On Wed, Apr 4, 2012 at 8:00 AM, Tom Lane wrote:
>> umi.tan...@gmail.com writes:
>>> http://www.postgresql.org/docs/9.1/static/spi-spi-execute.html
>>
>>> ===
>>> SPI_execute("INSERT INTO foo SELECT * FROM bar", false, 5);
>>> will allow at most 5 rows to be inserted into the table.
>>> ===
>>
>>> This seems not true unless I'm missing something.
>>
>> Hmm ... that did work as described, until we broke it :-(. This is an
>> oversight in the 9.0 changes that added separate ModifyTuple nodes to
>> plan trees. ModifyTuple doesn't return after each updated row, unless
>> there's a RETURNING clause; which means that the current_tuple_count
>> check logic in ExecutePlan() no longer stops execution as intended.
>>
>> Given the lack of complaints since 9.0, maybe we should not fix this
>> but just redefine the new behavior as being correct? But it seems
>> mighty inconsistent that the tuple limit would apply if you have
>> RETURNING but not when you don't. In any case, the ramifications
>> are wider than one example in the SPI docs.
>>
>> Thoughts?
>
> To be honest, I was surprised when I found tcount parameter is said to
> be applied to even INSERT. I believe people think that parameter is
> to limit memory consumption when returning tuples thus it'd be applied
> for only SELECT or DML with RETURNING. So I'm +1 for non-fix but
> redefine the behavior. Who wants to limit the number of rows
> processed inside the backend, from SPI?
Yeah. I think it would be a good idea for UPDATE and DELETE to expose
a LIMIT option, but I can't really see the virtue in making that
functionality available only through SPI.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #6587: Limit on a query is mis-documented
The following bug has been logged on the website: Bug reference: 6587 Logged by: Mike Boldi Email address: mbo...@prospectiv.com PostgreSQL version: 9.0.3 Operating system: Linux/Redhat Description: in the manual it says If a limit count is given, no more than that many rows will be returned (but possibly less, if the query itself yields less rows) BUT !! limit caps the largest internal spool. not the rows returned. I am doing a large query so I limit the query to 550 but I get 88 rows returned. I increase this to 900 and get 136 rows returned .. explain Plan ... Aggregate (cost=1689951.65..1689951.66 rows=1 width=0) -> Hash Join (cost=644.62..1687656.17 rows=918194 width=0) Hash Cond: (a.email_a = o.email) -> Subquery Scan on a (cost=0.00..1654854.18 rows=9149 width=64) Filter: ((a.memberid_a > a.memberid_b) AND (a.email_a = a.email_b) AND (length(a.email_a) > 0)) -> Limit (cost=0.00..1325501.10 rows=16467654 width=690) -> Nested Loop (cost=0.00..1325501.10 rows=16467654 width=690) -> Seq Scan on lgen_fraud_members12282 (cost=0.00..1597.14 rows=5381 width=345) Filter: ((regcomplete <> (-8)) AND (dateid >= 5938)) -> Index Scan using lgah11_12282 on lgen_fraud_members12282 (cost=0.00..123.62 rows=3060 width=345) Index Cond: ((staging.lgen_fraud_members12282.dateid >= staging.lgen_fraud_members12282.dateid) AND (staging.lgen_fraud_members12282.memberid > staging.lgen_fraud_members12282.memberid)) -> Hash (cost=393.72..393.72 rows=20072 width=32) -> Seq Scan on lgen_fraud_email o (cost=0.00..393.72 rows=20072 width=32) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6587: Limit on a query is mis-documented
On Fri, Apr 13, 2012 at 8:40 PM, wrote: > I am doing a large query so I limit the query to 550 but I get 88 rows > returned. I increase this to 900 and get 136 rows returned .. > explain Plan ... Please send the query with and without the LIMIT. It might also help to send more such as the schema and possibly even the data if that's possible. But at least send the queries so we can see what you're doing. The plan does not look correct for what you've described. -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6587: Limit on a query is mis-documented
On 04/13/12 12:40 PM, mbo...@prospectiv.com wrote: Aggregate (cost=1689951.65..1689951.66 rows=1 width=0) -> Hash Join (cost=644.62..1687656.17 rows=918194 width=0) Hash Cond: (a.email_a = o.email) -> Subquery Scan on a (cost=0.00..1654854.18 rows=9149 width=64) Filter: ((a.memberid_a> a.memberid_b) AND (a.email_a = a.email_b) AND (length(a.email_a)> 0)) -> Limit (cost=0.00..1325501.10 rows=16467654 width=690) -> Nested Loop (cost=0.00..1325501.10 rows=16467654 width=690) -> Seq Scan on lgen_fraud_members12282 (cost=0.00..1597.14 rows=5381 width=345) Filter: ((regcomplete<> (-8)) AND (dateid >= 5938)) -> Index Scan using lgah11_12282 on lgen_fraud_members12282 (cost=0.00..123.62 rows=3060 width=345) Index Cond: ((staging.lgen_fraud_members12282.dateid>= staging.lgen_fraud_members12282.dateid) AND (staging.lgen_fraud_members12282.memberid> staging.lgen_fraud_members12282.memberid)) -> Hash (cost=393.72..393.72 rows=20072 width=32) -> Seq Scan on lgen_fraud_email o (cost=0.00..393.72 rows=20072 width=32) without seeing the query, I can only suggest that it appears the LIMIT is inside of a nested query or something, as it looks like it was applied prior to additional filtering and joining. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6572: The example of SPI_execute is bogus
> > Yeah. I think it would be a good idea for UPDATE and DELETE to expose > a LIMIT option, but I can't really see the virtue in making that > functionality available only through SPI. > I don't agree - LIMIT after UPDATE or DELETE has no sense. Clean solution should be based on using updateable CTE. Regards Pavel -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #6572: The example of SPI_execute is bogus
On Fri, Apr 13, 2012 at 10:43 PM, Pavel Stehule wrote: >> Yeah. I think it would be a good idea for UPDATE and DELETE to expose >> a LIMIT option, but I can't really see the virtue in making that >> functionality available only through SPI. > > I don't agree - LIMIT after UPDATE or DELETE has no sense. Clean > solution should be based on using updateable CTE. It has a lot of sense. Without it, it's very difficult to do logical replication on a table with no primary key. (Whether or not people should create such tables in the first place is, of course, beside the point.) -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
