Re: [BUGS] BUG #7793: tsearch_data thesaurus size limit

[David Boutin]

Ok thanks for your reply Tom.

We have made on our side some update on this file
 src/backend/tsearch/dict_thesaurus.c
Then we recompile PG 9.2.2 with this patch and now the thesaurus works fine
with more than 64k entries and queries runtime is always low as expected.

Here is our update of the file:

 typedef struct LexemeInfo
 {
- *uint16* idsubst; /* entry's number in DictThesaurus->subst */
+ *uint32* idsubst; /* entry's number in DictThesaurus->subst */
  uint16 posinsubst; /* pos info in entry */

...

 static void
-newLexeme(DictThesaurus *d, char *b, char *e, *uint16* idsubst, uint16
posinsubst)
+newLexeme(DictThesaurus *d, char *b, char *e, *uint32* idsubst, uint16
posinsubst)
 {
  TheLexeme  *ptr;

...

 static void
-addWrd(DictThesaurus *d, char *b, char *e, *uint16* idsubst, uint16 nwrd,
uint16 posinsubst, bool useasis)
+addWrd(DictThesaurus *d, char *b, char *e, *uint32* idsubst, uint16 nwrd,
uint16 posinsubst, bool useasis)
 {

...

 thesaurusRead(char *filename, DictThesaurus *d) {
  tsearch_readline_state trst;
- *uint16* idsubst = 0;
+ *uint32* idsubst = 0;
  bool useasis = false;

...

 static bool
-matchIdSubst(LexemeInfo *stored, *uint16* idsubst)
+matchIdSubst(LexemeInfo *stored, *uint32* idsubst)
 {
  bool res = true;


Kind regards.
David



On Mon, Jan 7, 2013 at 1:41 AM, Tom Lane  wrote:

> dav...@gmail.com writes:
> > [ thesaurus dictionary fails for more than 64K entries ]
>
> I see a whole bunch of uses of "uint16" in
> src/backend/tsearch/dict_thesaurus.c.  It's not immediately clear which
> of these would need to be widened to support more entries, or what the
> storage cost of doing that would be.  We probably should at least put in
> a range check so that you get a clean failure instead of a crash though.
>
> regards, tom lane
>

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Dave Cramer]

Good points to both. Thank you both for reviewing.


Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Fri, Jan 11, 2013 at 12:36 PM, Stefan Reiser  wrote:

> Kris Jurka schrieb:
>
>
>> On Fri, 11 Jan 2013, Dave Cramer wrote:
>>
>>  Ok, I've pushed this fix into master
>>>
>>>  You've made any failure to parse the affected row count return
>> SUCCESS_NO_INFO.  Shouldn't you change the integer parsing to a long
>> parsing and only modify the response if the value is > INT_MAX while still
>> throwing an exception if we get something that is truly undecipherable?
>>
>> Kris Jurka
>>
>>
>>  Dave,
> I'm completely unfamiliar with the driver's code, so I better won't take
> part in the further discussion -- just one thing: Now "insert_oid" won't be
> assigned correctly when the assignment of update_count fails:
>
> [QueryExecutorImpl.java]
> try
> {
> update_count = Integer.parseInt(status.**substring(1 +
> status.lastIndexOf(' ')));
> if (status.startsWith("INSERT"))
> insert_oid = Long.parseLong(status.**substring(1 +
> status.indexOf(' '),
> status.lastIndexOf(' ')));
> }
> catch (NumberFormatException nfe)
> {
> update_count=Statement.**SUCCESS_NO_INFO;
> }
>
> better be something like this: ?
>
> try
> {
> update_count = Integer.parseInt(status.**substring(1 +
> status.lastIndexOf(' ')));
> }
> catch (NumberFormatException nfe)
> {
> update_count=Statement.**SUCCESS_NO_INFO;
> }
> try {
> if (status.startsWith("INSERT"))
> insert_oid = Long.parseLong(status.**substring(1 +
> status.indexOf(' '),
> status.lastIndexOf(' ')));
> } catch ( ...
>  // don't know what expected behaviour should be ...
> }
>
> regards
> Stefan Reiser
>

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Stefan Reiser]

Kris Jurka schrieb:


On Fri, 11 Jan 2013, Dave Cramer wrote:


Ok, I've pushed this fix into master


You've made any failure to parse the affected row count return
SUCCESS_NO_INFO.  Shouldn't you change the integer parsing to a long
parsing and only modify the response if the value is > INT_MAX while still
throwing an exception if we get something that is truly undecipherable?

Kris Jurka



Dave,
I'm completely unfamiliar with the driver's code, so I better won't take 
part in the further discussion -- just one thing: Now "insert_oid" won't 
be assigned correctly when the assignment of update_count fails:


[QueryExecutorImpl.java]
try
{
update_count = Integer.parseInt(status.substring(1 + 
status.lastIndexOf(' ')));

if (status.startsWith("INSERT"))
insert_oid = Long.parseLong(status.substring(1 + 
status.indexOf(' '),

status.lastIndexOf(' ')));
}
catch (NumberFormatException nfe)
{
update_count=Statement.SUCCESS_NO_INFO;
}

better be something like this: ?

try
{
update_count = Integer.parseInt(status.substring(1 + 
status.lastIndexOf(' ')));

}
catch (NumberFormatException nfe)
{
update_count=Statement.SUCCESS_NO_INFO;
}
try {
if (status.startsWith("INSERT"))
insert_oid = Long.parseLong(status.substring(1 + 
status.indexOf(' '),

status.lastIndexOf(' ')));
} catch ( ...
 // don't know what expected behaviour should be ...
}

regards
Stefan Reiser


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Kris Jurka]

On Fri, 11 Jan 2013, Dave Cramer wrote:

> Ok, I've pushed this fix into master
> 

You've made any failure to parse the affected row count return 
SUCCESS_NO_INFO.  Shouldn't you change the integer parsing to a long 
parsing and only modify the response if the value is > INT_MAX while still 
throwing an exception if we get something that is truly undecipherable?

Kris Jurka


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Kris Jurka]

On Fri, 11 Jan 2013, Stefan Reiser wrote:

> What about returning Statement.SUCCESS_NO_INFO as it says in
> http://docs.oracle.com/javase/6/docs/api/java/sql/BatchUpdateException.html#getUpdateCounts%28%29
> and
> http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29
> 
> It seems better to report no number at all rather than a number (INT_MAX) that
> is known to be wrong.

What about Statement.executeUpdate?  It has provision for returing a 
batch execution response code.

Kris Jurka


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Dave Cramer]

Ok, I've pushed this fix into master

On Fri, Jan 11, 2013 at 10:38 AM, Dave Cramer  wrote:

> SUCCESS_NO_INFO




Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Dave Cramer]

Yes, that seems like a much better approach. I'm guessing SUCCESS_NO_INFO
is < 0 and an int. I can't wait for the error reports (arguments)

Dave

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Fri, Jan 11, 2013 at 10:32 AM, Stefan Reiser  wrote:

> One thought:
>
> What about returning Statement.SUCCESS_NO_INFO as it says in
> http://docs.oracle.com/javase/**6/docs/api/java/sql/**
> BatchUpdateException.html#**getUpdateCounts%28%29
> and
> http://docs.oracle.com/javase/**6/docs/api/java/sql/Statement.**
> html#executeBatch%28%29
>
> ?
>
> It seems better to report no number at all rather than a number (INT_MAX)
> that is known to be wrong.
>
>
>
> Dave Cramer schrieb:
>
>> Ok, this is much more difficult than I thought.
>>
>> Turns out that there are at least two interfaces that expect an int not a
>> long.
>>
>> BatchUpdateException
>> executeBatch
>>
>> I'm thinking the only option here is to report INT_MAX as opposed to
>> failing.
>>
>> Thoughts ?
>>
>> Dave
>>
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>>
>> On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane > t...@sss.pgh.pa.us>> wrote:
>>
>>
>> Dave Cramer mailto:p...@fastcrypt.com>> writes:
>> > So an unsigned long won't fit inside a java long either, but
>> hopefully it
>> > will never be necessary. That would be a huge number of changes.
>>
>> I think we'll all be safely dead by the time anybody manages to
>> process
>> 2^63 rows in one PG command ;-).  If you can widen the value from
>> int to
>> long on the Java side, that should be sufficient.
>>
>> regards, tom lane
>>
>>
>>
>

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Stefan Reiser]

One thought:

What about returning Statement.SUCCESS_NO_INFO as it says in
http://docs.oracle.com/javase/6/docs/api/java/sql/BatchUpdateException.html#getUpdateCounts%28%29
and
http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29

?

It seems better to report no number at all rather than a number 
(INT_MAX) that is known to be wrong.




Dave Cramer schrieb:

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int 
not a long.


BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to 
failing.


Thoughts ?

Dave


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane > wrote:


Dave Cramer mailto:p...@fastcrypt.com>> writes:
> So an unsigned long won't fit inside a java long either, but
hopefully it
> will never be necessary. That would be a huge number of changes.

I think we'll all be safely dead by the time anybody manages to
process
2^63 rows in one PG command ;-).  If you can widen the value from
int to
long on the Java side, that should be sufficient.

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 #7803: Replication Problem(no master is there)

[Simon Riggs]

On 11 January 2013 12:18, Tomonari Katsumata
 wrote:

> I'm not sure but what about adding the parameter("cascade_mode") on
> recovery.conf ?
> The parameter represents a will to connect to standby server when starting
> as standby.
> If the parameter is set to on, connect to a server forcely like PostgreSQL
> 9.2,
> and if the parameter is set to off, connect to the another standby server is
> refused like PostgreSQL 9.1.

We added a REPLICATION privelge onto user accounts to control access.

Perhaps we should add a CASCADE privilege as well, so that we can
control whether we can connect to a master and/or a standby.

Syntax would be

ALTER USER foo
[MASTER | CASCADE] REPLICATION

REPLICATION allows both master and cascaded replication (same as now)
MASTER REPLICATION allows master only
CASCADE REPLICATION allows cascaded replication only
NOREPLICATION allows neither option

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [JDBC] [BUGS] BUG #7766: Running a DML statement that affects more than 4 billion rows results in an exception

[Dave Cramer]

Ok, this is much more difficult than I thought.

Turns out that there are at least two interfaces that expect an int not a
long.

BatchUpdateException
executeBatch

I'm thinking the only option here is to report INT_MAX as opposed to
failing.

Thoughts ?

Dave


Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca


On Fri, Dec 21, 2012 at 3:17 PM, Tom Lane  wrote:

> Dave Cramer  writes:
> > So an unsigned long won't fit inside a java long either, but hopefully it
> > will never be necessary. That would be a huge number of changes.
>
> I think we'll all be safely dead by the time anybody manages to process
> 2^63 rows in one PG command ;-).  If you can widen the value from int to
> long on the Java side, that should be sufficient.
>
> regards, tom lane
>

Re: [BUGS] BUG #7803: Replication Problem(no master is there)

[Tomonari Katsumata]

Hi,


hi, I'm playing with Synchronous Replication on PostgreSQL 9.2.2.
And I saw a strange behavior.


Unless you left out something, the configuration you described 
actually sets up asynchronous replication.




Thank you for the comment.
I was thinking to promote one of them and set
synchronous_standby_names = 'sby' and reload configure file.
Before that, I noticed the issue.



=
[issues]
two standbys are connected on each other, but
no master is there.
...
=

I did not see the situation like above on PostgreSQL 9.1.7.

Is this intended change?


In 9.1, this scenario was impossible because you could not connect a 
standby to another standby. In 9.2, that's allowed. It's a new feature 
called "cascading replication", see 
http://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION.


With that feature, it's indeed possible to form a cycle of standby 
servers connected to each other. There was just a long discussion on 
pgsql-hackers on whether we should try to detect that scenario [1], 
but the consensus seems to be that we should not. It would be 
difficult to implement such detection, and sometimes it's useful to 
have such a cycle, as a transient state at a failover, for example.


Sorry, I had not read the discussion and have reported it again.
And I understand it's hard to detect cyclic situation.



So the bottom line is that this is an intended change, and the admin 
will just have to avoid doing that.


OK.
This situation was made by playing, so it's not big problem for now.



This makes me wonder if there should be a GUC to forbid cascading 
replication, though. If you don't want to do cascading replication 
(which is quite rare, I'd say), you could just disable it to avoid a 
situation like this.


I'm not sure but what about adding the parameter("cascade_mode") on 
recovery.conf ?
The parameter represents a will to connect to standby server when 
starting as standby.
If the parameter is set to on, connect to a server forcely like 
PostgreSQL 9.2,

and if the parameter is set to off, connect to the another standby server is
refused like PostgreSQL 9.1.




[1] http://archives.postgresql.org/pgsql-hackers/2012-12/msg01134.php

- Heikki




regards,

NTT Software Corporation
 Tomonari Katsumata




--
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 #7803: Replication Problem(no master is there)

[Heikki Linnakangas]

On 11.01.2013 11:19, Simon Riggs wrote:

On 11 January 2013 08:40, Heikki Linnakangas  wrote:


This makes me wonder if there should be a GUC to forbid cascading
replication, though. If you don't want to do cascading replication (which is
quite rare, I'd say), you could just disable it to avoid a situation like
this.


Connection from a standby is disabled by default. Don't enable it...


It's controlled by hot_standby=on/off. You might well want to enable hot 
standby, to run queries on a standby, but disable cascading replication.


You can also forbid replication connections using pg_hba.conf, but then 
you need to modify it to allow connections again after failover, when 
the standby becomes master. That's doable, but inconvenient.


(just thinking out loud here..) If we were to have such a switch, it'd 
be nice to still allow base backups from the standby.


- Heikki


--
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 #7803: Replication Problem(no master is there)

[Simon Riggs]

On 11 January 2013 08:40, Heikki Linnakangas  wrote:

> This makes me wonder if there should be a GUC to forbid cascading
> replication, though. If you don't want to do cascading replication (which is
> quite rare, I'd say), you could just disable it to avoid a situation like
> this.

Connection from a standby is disabled by default. Don't enable it...

-- 
 Simon Riggs   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


-- 
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 #7803: Replication Problem(no master is there)

[Heikki Linnakangas]

On 11.01.2013 06:09, katsumata.tomon...@po.ntts.co.jp wrote:

The following bug has been logged on the website:

Bug reference:  7803
Logged by:  Tomonari Katsumata
Email address:  katsumata.tomon...@po.ntts.co.jp
PostgreSQL version: 9.2.2
Operating system:   RHEL 5.3 x86_64
Description:

hi, I'm playing with Synchronous Replication on PostgreSQL 9.2.2.
And I saw a strange behavior.


Unless you left out something, the configuration you described actually 
sets up asynchronous replication.



=
[issues]
two standbys are connected on each other, but
no master is there.
...
=

I did not see the situation like above on PostgreSQL 9.1.7.

Is this intended change?


In 9.1, this scenario was impossible because you could not connect a 
standby to another standby. In 9.2, that's allowed. It's a new feature 
called "cascading replication", see 
http://www.postgresql.org/docs/9.2/static/warm-standby.html#CASCADING-REPLICATION.


With that feature, it's indeed possible to form a cycle of standby 
servers connected to each other. There was just a long discussion on 
pgsql-hackers on whether we should try to detect that scenario [1], but 
the consensus seems to be that we should not. It would be difficult to 
implement such detection, and sometimes it's useful to have such a 
cycle, as a transient state at a failover, for example.


So the bottom line is that this is an intended change, and the admin 
will just have to avoid doing that.


This makes me wonder if there should be a GUC to forbid cascading 
replication, though. If you don't want to do cascading replication 
(which is quite rare, I'd say), you could just disable it to avoid a 
situation like this.


[1] http://archives.postgresql.org/pgsql-hackers/2012-12/msg01134.php

- Heikki


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs