pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
column size too large, is this a bug?
22+ messages / 8 participants
[nested] [flat]

* column size too large, is this a bug?
@ 2004-03-25 17:58 Qing Zhao <qzhao@quotefx.net>
  2004-03-25 18:20 ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 2 replies; 22+ messages in thread

From: Qing Zhao @ 2004-03-25 17:58 UTC (permalink / raw)
  To: pgsql-performance

  I have a query which get's data from a single table.
When I try to get data from for an RFQ which has around 5000 rows, it 
is breaking off at 18th row.
If i reduce some columns , then it returns all the rows and not so slow.
  I have tried with different sets of column and there is no pattern 
based on columns.

  But one thing is sure one size of the rows grows more than some bytes, 
the records do not get returned. Now the following query returns me all 
5001 rows to me pretty fast


   select
   _level_ as l,
    nextval('seq_pk_bom_detail') as bom_detail,
    prior nextval('seq_pk_bom_detail') as parent_subassembly,
   parent_part_number,
    customer_part_number,
   /* mfr_name,
    mfr_part,
    description,*/
    commodity,
    needs_date,
    target_price,
    comments,
    case qty_per
       when null then 0.00001
       when 0 then 0.00001
       else qty_per
    end,
    qty_multiplier1,
    qty_multiplier2,
    qty_multiplier3,
    qty_multiplier4,
    qty_multiplier5
  from bom_detail_work_clean
  where (0=0)
   and bom_header=20252
   and file_number = 1
   start with customer_part_number = 'Top Assembly 1'
   connect by parent_part_number = prior customer_part_number;


But if I uncomment the description then it returns me only 18 rows.

   select
   _level_ as l,
    nextval('seq_pk_bom_detail') as bom_detail,
    prior nextval('seq_pk_bom_detail') as parent_subassembly,
   parent_part_number,
    customer_part_number,
   /* mfr_name,
    mfr_part,*/
    description,
    commodity,
    needs_date,
    target_price,
    comments,
    case qty_per
       when null then 0.00001
       when 0 then 0.00001
       else qty_per
    end,
    qty_multiplier1,
    qty_multiplier2,
    qty_multiplier3,
    qty_multiplier4,
    qty_multiplier5
  from bom_detail_work_clean
  where (0=0)
   and bom_header=20252
   and file_number = 1
   start with customer_part_number = 'Top Assembly 1'
   connect by parent_part_number = prior customer_part_number;

Now these 18 rows are level 2 records  in heirarchical query. I have a 
feeling the server has some memory paging mechanism
and if it can not handle beyond certain byets, it just returns what it 
has.
  During your investigation of optimization of postgreSQL did you come 
across any setting that might help us ?

Thanks!

Qing

PS: I just reload the file while reducing the content in the 
description column.
The file got uploaded. So looks like the problem is size of the record 
being inserted.

^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
@ 2004-03-25 18:20 ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-25 22:20   ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  1 sibling, 1 reply; 22+ messages in thread

From: Tom Lane @ 2004-03-25 18:20 UTC (permalink / raw)
  To: Qing Zhao <qzhao@quotefx.net>; +Cc: pgsql-performance

Qing Zhao <qzhao@quotefx.net> writes:
>   I have a query which get's data from a single table.
> When I try to get data from for an RFQ which has around 5000 rows, it 
> is breaking off at 18th row.
> If i reduce some columns , then it returns all the rows and not so slow.

What client-side software are you using?  This is surely a limitation on
the client side, because there is no such problem in the server.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 18:20 ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-25 22:20   ` Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:28     ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 22+ messages in thread

From: Qing Zhao @ 2004-03-25 22:20 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance

Tom,

Thanks for your help!
It's not through one client. I am using JDBC. But the same things 
happen when I use client like psql.

Qing
On Mar 25, 2004, at 10:20 AM, Tom Lane wrote:

> Qing Zhao <qzhao@quotefx.net> writes:
>>   I have a query which get's data from a single table.
>> When I try to get data from for an RFQ which has around 5000 rows, it
>> is breaking off at 18th row.
>> If i reduce some columns , then it returns all the rows and not so 
>> slow.
>
> What client-side software are you using?  This is surely a limitation 
> on
> the client side, because there is no such problem in the server.
>
> 			regards, tom lane
>




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 18:20 ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-25 22:20   ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
@ 2004-03-25 22:28     ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 22+ messages in thread

From: Tom Lane @ 2004-03-25 22:28 UTC (permalink / raw)
  To: Qing Zhao <qzhao@quotefx.net>; +Cc: pgsql-performance

Qing Zhao <qzhao@quotefx.net> writes:
> It's not through one client. I am using JDBC. But the same things 
> happen when I use client like psql.

That's really hard to believe.  Can you provide a reproducible test
case?

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
@ 2004-03-25 22:57 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-25 23:11   ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 2 replies; 22+ messages in thread

From: Stephan Szabo @ 2004-03-25 22:57 UTC (permalink / raw)
  To: Qing Zhao <qzhao@quotefx.net>; +Cc: pgsql-performance

On Thu, 25 Mar 2004, Qing Zhao wrote:

>    select
>    _level_ as l,
>     nextval('seq_pk_bom_detail') as bom_detail,
>     prior nextval('seq_pk_bom_detail') as parent_subassembly,
>    parent_part_number,
>     customer_part_number,
>    /* mfr_name,
>     mfr_part,
>     description,*/
>     commodity,
>     needs_date,
>     target_price,
>     comments,
>     case qty_per
>        when null then 0.00001
>        when 0 then 0.00001
>        else qty_per
>     end,
>     qty_multiplier1,
>     qty_multiplier2,
>     qty_multiplier3,
>     qty_multiplier4,
>     qty_multiplier5
>   from bom_detail_work_clean
>   where (0=0)
>    and bom_header=20252
>    and file_number = 1
>    start with customer_part_number = 'Top Assembly 1'
>    connect by parent_part_number = prior customer_part_number;

What version are you running, and did you apply any patches (for example
one to support the start with/connect by syntax used above?)




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-03-25 23:11   ` Qing Zhao <qzhao@quotefx.net>
  1 sibling, 0 replies; 22+ messages in thread

From: Qing Zhao @ 2004-03-25 23:11 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Partha Roy <proy@orbitwerks.com>; pgsql-performance

It is 7.3.4 on MAC OS X (darwin). The patch we applied is 
hier-Pg7.3-0.5, which allows
to perform hierarchical queries on PgSQL using Oracle's syntax.

Thanks!

Qing

On Mar 25, 2004, at 2:57 PM, Stephan Szabo wrote:

> On Thu, 25 Mar 2004, Qing Zhao wrote:
>
>>    select
>>    _level_ as l,
>>     nextval('seq_pk_bom_detail') as bom_detail,
>>     prior nextval('seq_pk_bom_detail') as parent_subassembly,
>>    parent_part_number,
>>     customer_part_number,
>>    /* mfr_name,
>>     mfr_part,
>>     description,*/
>>     commodity,
>>     needs_date,
>>     target_price,
>>     comments,
>>     case qty_per
>>        when null then 0.00001
>>        when 0 then 0.00001
>>        else qty_per
>>     end,
>>     qty_multiplier1,
>>     qty_multiplier2,
>>     qty_multiplier3,
>>     qty_multiplier4,
>>     qty_multiplier5
>>   from bom_detail_work_clean
>>   where (0=0)
>>    and bom_header=20252
>>    and file_number = 1
>>    start with customer_part_number = 'Top Assembly 1'
>>    connect by parent_part_number = prior customer_part_number;
>
> What version are you running, and did you apply any patches (for 
> example
> one to support the start with/connect by syntax used above?)
>
>

^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2004-03-26 00:39   ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 22+ messages in thread

From: Tom Lane @ 2004-03-26 00:39 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Qing Zhao <qzhao@quotefx.net>; pgsql-performance

Stephan Szabo <sszabo@megazone.bigpanda.com> writes:
> On Thu, 25 Mar 2004, Qing Zhao wrote:
>> start with customer_part_number = 'Top Assembly 1'
>> connect by parent_part_number = prior customer_part_number;

> What version are you running, and did you apply any patches (for example
> one to support the start with/connect by syntax used above?)

Oh, good eye ... it's that infamous CONNECT BY patch again, without doubt.

I think we should add "Have you applied any patches to your copy of
Postgres?" to the standard bug report form ...

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-26 01:21     ` Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 22+ messages in thread

From: Josh Berkus @ 2004-03-26 01:21 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Qing Zhao <qzhao@quotefx.net>; pgsql-performance

Tom,

> Oh, good eye ... it's that infamous CONNECT BY patch again, without doubt.

Hey, who does this patch?   What's wrong wiith it?

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
@ 2004-03-26 02:04       ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 03:00         ` Re: column size too large, is this a bug? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2004-03-26 17:29         ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  0 siblings, 2 replies; 22+ messages in thread

From: Tom Lane @ 2004-03-26 02:04 UTC (permalink / raw)
  To: josh@agliodbs.com; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Qing Zhao <qzhao@quotefx.net>; pgsql-performance

Josh Berkus <josh@agliodbs.com> writes:
>> Oh, good eye ... it's that infamous CONNECT BY patch again, without doubt.

> Hey, who does this patch?   What's wrong wiith it?

I'm just venting my annoyance at people expecting us to support
hacked-up versions, especially without telling us they're hacked-up.
This is the third or fourth trouble report I can recall that was
eventually traced to that patch (after considerable effort).

Anyway, my guess for the immediate problem is incorrect installation of
the patch, viz not doing a complete "make clean" and rebuild after
patching.  The patch changes the Query struct which is referenced in
many more files than are actually modified by the patch, and so if you
didn't build with --enable-depend then a simple "make" will leave you
with a patchwork of files that have different ideas about the field
offsets in Query.  I'm a bit surprised it doesn't just dump core...

(That's not directly the fault of the patch, though, except to the
extent that it can be blamed for coming without adequate installation
instructions.  What is directly the fault of the patch is that it
doesn't force an initdb by changing catversion.  The prior trouble
reports had to do with views not working because their stored rules were
incompatible with the patched backend.  We should not have had to deal
with that, and neither should those users.)

Theory B, of course, is that this is an actual bug in the patch and not
just incorrect installation.  I'm not interested enough to investigate
though.  

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-26 03:00         ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2004-03-26 03:11           ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 22+ messages in thread

From: Christopher Kings-Lynne @ 2004-03-26 03:00 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Stephan Szabo <sszabo@megazone.bigpanda.com>; Qing Zhao <qzhao@quotefx.net>; pgsql-performance

> Theory B, of course, is that this is an actual bug in the patch and not
> just incorrect installation.  I'm not interested enough to investigate
> though.  

Is there still someone around who's working on getting a similar patch 
into 7.5?  Seems there huge user demand for such a thing...

(And no, I'm not volunteering, it's well beyond my abilities...)

Chris




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 03:00         ` Re: column size too large, is this a bug? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
@ 2004-03-26 03:11           ` Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 03:38             ` Re: column size too large, is this a bug? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 1 reply; 22+ messages in thread

From: Tom Lane @ 2004-03-26 03:11 UTC (permalink / raw)
  To: Christopher Kings-Lynne <chriskl@familyhealth.com.au>; +Cc: josh@agliodbs.com, Stephan Szabo <sszabo@megazone.bigpanda.com>; Qing Zhao <qzhao@quotefx.net>; pgsql-performance

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:
> Is there still someone around who's working on getting a similar patch 
> into 7.5?  Seems there huge user demand for such a thing...

Andrew Overholt did some preliminary work toward implementing the
SQL99-spec WITH functionality (which subsumes what CONNECT BY does,
and a few other things too).  But he's left Red Hat and gone back
to school.  One of the many things on my todo list is to pick up that
patch and get it finished.

IIRC Andrew had finished the parser work and we had a paper design for
the executor support.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 03:00         ` Re: column size too large, is this a bug? Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  2004-03-26 03:11           ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-26 03:38             ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
  0 siblings, 0 replies; 22+ messages in thread

From: Christopher Kings-Lynne @ 2004-03-26 03:38 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, Stephan Szabo <sszabo@megazone.bigpanda.com>; Qing Zhao <qzhao@quotefx.net>; pgsql-performance

> Andrew Overholt did some preliminary work toward implementing the
> SQL99-spec WITH functionality (which subsumes what CONNECT BY does,
> and a few other things too).  But he's left Red Hat and gone back
> to school.  One of the many things on my todo list is to pick up that
> patch and get it finished.

Out of interest, what is your 7.5 todo list?

Chris




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-03-26 17:29         ` Qing Zhao <qzhao@quotefx.net>
  2004-03-26 18:01           ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 18:15           ` Re: column size too large, is this a bug? Andrew Rawnsley <ronz@ravensfield.com>
  1 sibling, 2 replies; 22+ messages in thread

From: Qing Zhao @ 2004-03-26 17:29 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: josh@agliodbs.com, pgsql-performance@postgresql.org, Stephan Szabo <sszabo@megazone.bigpanda.com>

Thanks a lot!  We were migrating to Postgres from Oracle and
every now and then, we ran into something that we do not
understand completely and  it is a learning process for us.

Your responses have made it much clear for us. BTW, do you
think that it's better for us just to rewrite everything so we don't
need to use the patch at all? Why do others still use it?

Thanks!

Qing
On Mar 25, 2004, at 6:04 PM, Tom Lane wrote:

> Josh Berkus <josh@agliodbs.com> writes:
>>> Oh, good eye ... it's that infamous CONNECT BY patch again, without 
>>> doubt.
>
>> Hey, who does this patch?   What's wrong wiith it?
>
> I'm just venting my annoyance at people expecting us to support
> hacked-up versions, especially without telling us they're hacked-up.
> This is the third or fourth trouble report I can recall that was
> eventually traced to that patch (after considerable effort).
>
> Anyway, my guess for the immediate problem is incorrect installation of
> the patch, viz not doing a complete "make clean" and rebuild after
> patching.  The patch changes the Query struct which is referenced in
> many more files than are actually modified by the patch, and so if you
> didn't build with --enable-depend then a simple "make" will leave you
> with a patchwork of files that have different ideas about the field
> offsets in Query.  I'm a bit surprised it doesn't just dump core...
>
> (That's not directly the fault of the patch, though, except to the
> extent that it can be blamed for coming without adequate installation
> instructions.  What is directly the fault of the patch is that it
> doesn't force an initdb by changing catversion.  The prior trouble
> reports had to do with views not working because their stored rules 
> were
> incompatible with the patched backend.  We should not have had to deal
> with that, and neither should those users.)
>
> Theory B, of course, is that this is an actual bug in the patch and not
> just incorrect installation.  I'm not interested enough to investigate
> though.
>
> 			regards, tom lane
>




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 17:29         ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
@ 2004-03-26 18:01           ` Josh Berkus <josh@agliodbs.com>
  1 sibling, 0 replies; 22+ messages in thread

From: Josh Berkus @ 2004-03-26 18:01 UTC (permalink / raw)
  To: Qing Zhao <qzhao@quotefx.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance@postgresql.org, Stephan Szabo <sszabo@megazone.bigpanda.com>

Quig,

> Your responses have made it much clear for us. BTW, do you
> think that it's better for us just to rewrite everything so we don't
> need to use the patch at all? Why do others still use it?

Others use it because of the same reason you do.   

If you want to use the patch for seemless porting, I suggest that you contact 
Evgen directly.  He's not very active on the main project mailing lists, so 
you'll need to e-mail him personally.   You may also need to sponsor him for 
bug fixes, since he is apparently an independent developer.  I don't really 
know him.

As an alternative, you may want to take a look at the IS_CONNECTED_BY patch 
in /contrib/tablefunc in the PostgreSQL source.   As this was developed by 
Joe Conway, who is a very active major contributor in the community, it is 
more likely to be bug-free.    However, it will force you to change your 
query syntax somewhat.

Of course, there are other query tree structures you could use if you're 
willing to modify your database design.   But you may not want to go that 
far.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 17:29         ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
@ 2004-03-26 18:15           ` Andrew Rawnsley <ronz@ravensfield.com>
  2004-03-28 19:25             ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  1 sibling, 1 reply; 22+ messages in thread

From: Andrew Rawnsley @ 2004-03-26 18:15 UTC (permalink / raw)
  To: Qing Zhao <qzhao@quotefx.net>; +Cc: josh@agliodbs.com, Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-performance


I used to use the connect-by patch, but have since rewritten everything 
to use a nested set model. I was
having problems that, while not immediately traceable back to the 
patch, showed up when I started
using it and went away when I stopped (strange locking behavior, 
crashing with vacuum full, problems after
dropping columns) . Plus the annoyance of maintaining a non-stock build 
across numerous installations
exceeded its benefits. Relying on it for a business critical situation 
became too much of a risk.



On Mar 26, 2004, at 12:29 PM, Qing Zhao wrote:

> Thanks a lot!  We were migrating to Postgres from Oracle and
> every now and then, we ran into something that we do not
> understand completely and  it is a learning process for us.
>
> Your responses have made it much clear for us. BTW, do you
> think that it's better for us just to rewrite everything so we don't
> need to use the patch at all? Why do others still use it?
>
> Thanks!
>
> Qing
> On Mar 25, 2004, at 6:04 PM, Tom Lane wrote:
>
>> Josh Berkus <josh@agliodbs.com> writes:
>>>> Oh, good eye ... it's that infamous CONNECT BY patch again, without 
>>>> doubt.
>>
>>> Hey, who does this patch?   What's wrong wiith it?
>>
>> I'm just venting my annoyance at people expecting us to support
>> hacked-up versions, especially without telling us they're hacked-up.
>> This is the third or fourth trouble report I can recall that was
>> eventually traced to that patch (after considerable effort).
>>
>> Anyway, my guess for the immediate problem is incorrect installation 
>> of
>> the patch, viz not doing a complete "make clean" and rebuild after
>> patching.  The patch changes the Query struct which is referenced in
>> many more files than are actually modified by the patch, and so if you
>> didn't build with --enable-depend then a simple "make" will leave you
>> with a patchwork of files that have different ideas about the field
>> offsets in Query.  I'm a bit surprised it doesn't just dump core...
>>
>> (That's not directly the fault of the patch, though, except to the
>> extent that it can be blamed for coming without adequate installation
>> instructions.  What is directly the fault of the patch is that it
>> doesn't force an initdb by changing catversion.  The prior trouble
>> reports had to do with views not working because their stored rules 
>> were
>> incompatible with the patched backend.  We should not have had to deal
>> with that, and neither should those users.)
>>
>> Theory B, of course, is that this is an actual bug in the patch and 
>> not
>> just incorrect installation.  I'm not interested enough to investigate
>> though.
>>
>> 			regards, tom lane
>>
>
>
> ---------------------------(end of 
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 17:29         ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-26 18:15           ` Re: column size too large, is this a bug? Andrew Rawnsley <ronz@ravensfield.com>
@ 2004-03-28 19:25             ` Josh Berkus <josh@agliodbs.com>
  2004-03-28 19:49               ` Re: column size too large, is this a bug? Andrew Rawnsley <ronz@ravensfield.com>
  2004-03-30 14:20               ` Re: column size too large, is this a bug? Robert Treat <xzilla@users.sourceforge.net>
  0 siblings, 2 replies; 22+ messages in thread

From: Josh Berkus @ 2004-03-28 19:25 UTC (permalink / raw)
  To: Andrew Rawnsley <ronz@ravensfield.com>; Qing Zhao <qzhao@quotefx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-performance

Andrew,

> I used to use the connect-by patch, but have since rewritten everything
> to use a nested set model. 

Cool!   You're probably the only person I know other than me using nested sets 
in a production environment.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 17:29         ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-26 18:15           ` Re: column size too large, is this a bug? Andrew Rawnsley <ronz@ravensfield.com>
  2004-03-28 19:25             ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
@ 2004-03-28 19:49               ` Andrew Rawnsley <ronz@ravensfield.com>
  1 sibling, 0 replies; 22+ messages in thread

From: Andrew Rawnsley @ 2004-03-28 19:49 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance


Well, I don't know if I would use it in an insert-heavy environment (at 
least the way I implemented it), but for select-heavy
stuff I don't know why you would want to use anything else. Hard to 
beat the performance of a simple BETWEEN.

On Mar 28, 2004, at 2:25 PM, Josh Berkus wrote:

> Andrew,
>
>> I used to use the connect-by patch, but have since rewritten 
>> everything
>> to use a nested set model.
>
> Cool!   You're probably the only person I know other than me using 
> nested sets
> in a production environment.
>
> -- 
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-25 22:57 ` Re: column size too large, is this a bug? Stephan Szabo <sszabo@megazone.bigpanda.com>
  2004-03-26 00:39   ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 01:21     ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  2004-03-26 02:04       ` Re: column size too large, is this a bug? Tom Lane <tgl@sss.pgh.pa.us>
  2004-03-26 17:29         ` Re: column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
  2004-03-26 18:15           ` Re: column size too large, is this a bug? Andrew Rawnsley <ronz@ravensfield.com>
  2004-03-28 19:25             ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
@ 2004-03-30 14:20               ` Robert Treat <xzilla@users.sourceforge.net>
  1 sibling, 0 replies; 22+ messages in thread

From: Robert Treat @ 2004-03-30 14:20 UTC (permalink / raw)
  To: Josh Berkus <josh@agliodbs.com>; Andrew Rawnsley <ronz@ravensfield.com>; Qing Zhao <qzhao@quotefx.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; pgsql-performance

On Sunday 28 March 2004 14:25, Josh Berkus wrote:
> Andrew,
>
> > I used to use the connect-by patch, but have since rewritten everything
> > to use a nested set model.
>
> Cool!   You're probably the only person I know other than me using nested
> sets in a production environment.

You cut me deep there Josh, real deep.  :-)

If you search the pgsql-sql archives you'll find some helpful threads on using 
nested sets in PostgreSQL, one in particular I  was involved with was a 
generic "move_tree" function that enabled moving a node from one branch to 
another. 

Robert Treat
-- 
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL



^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
@ 2004-03-29 17:05 Jaime Casanova <el_vigia_ec@hotmail.com>
  2004-03-29 17:25 ` Re: column size too large, is this a bug? Andrew Rawnsley <ronz@ravensfield.com>
  0 siblings, 1 reply; 22+ messages in thread

From: Jaime Casanova @ 2004-03-29 17:05 UTC (permalink / raw)
  To: pgsql-performance


>Andrew,

> > I used to use the connect-by patch, but have since rewritten everything
> > to use a nested set model.

>Cool!   You're probably the only person I know other than me using nested 
>sets
>in a production environment.


can you explain me what is a nested set?

_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*. 
http://join.msn.com/?page=features/featuredemail




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-29 17:05 Re: column size too large, is this a bug? Jaime Casanova <el_vigia_ec@hotmail.com>
@ 2004-03-29 17:25 ` Andrew Rawnsley <ronz@ravensfield.com>
  2004-03-29 19:12   ` Re: column size too large, is this a bug? Josh Berkus <josh@agliodbs.com>
  0 siblings, 1 reply; 22+ messages in thread

From: Andrew Rawnsley @ 2004-03-29 17:25 UTC (permalink / raw)
  To: Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-performance


Its a way of representing a tree with right-left pointers in each  
record (basically re-inventing a hierarchical database
in  a relational model...). A good description is in Joe Celko's SQL  
For Smarties book. Selection is very fast because
any node's children have node ID's between the right and left nodes of  
said node, so there's no mucking about
with connect by and what not. There's a synopsis linked at the PG  
Cookbook pages (http://www.brasileiro.net/postgres/cookbook),
but the cookbook seems to off-line (I think I'll offer to mirror it -  
this happens frequently). There's another description at
http://www.intelligententerprise.com/001020/celko.jhtml? 
_requestid=65750.

Insertion takes a fair amount of work, as you generally have to  
re-arrange the node IDs when you add a record.

On Mar 29, 2004, at 12:05 PM, Jaime Casanova wrote:

>
>> Andrew,
>
>> > I used to use the connect-by patch, but have since rewritten  
>> everything
>> > to use a nested set model.
>
>> Cool!   You're probably the only person I know other than me using  
>> nested sets
>> in a production environment.
>
>
> can you explain me what is a nested set?
>
> _________________________________________________________________
> Add photos to your messages with MSN 8. Get 2 months FREE*.  
> http://join.msn.com/?page=features/featuredemail
>
>
> ---------------------------(end of  
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
--------------------

Andrew Rawnsley
President
The Ravensfield Digital Resource Group, Ltd.
(740) 587-0114
www.ravensfield.com




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
  2004-03-29 17:05 Re: column size too large, is this a bug? Jaime Casanova <el_vigia_ec@hotmail.com>
  2004-03-29 17:25 ` Re: column size too large, is this a bug? Andrew Rawnsley <ronz@ravensfield.com>
@ 2004-03-29 19:12   ` Josh Berkus <josh@agliodbs.com>
  0 siblings, 0 replies; 22+ messages in thread

From: Josh Berkus @ 2004-03-29 19:12 UTC (permalink / raw)
  To: Andrew Rawnsley <ronz@ravensfield.com>; Jaime Casanova <el_vigia_ec@hotmail.com>; +Cc: pgsql-performance

Andrew,

> Its a way of representing a tree with right-left pointers in each  
> record (basically re-inventing a hierarchical database
> in  a relational model...). A good description is in Joe Celko's SQL  
> For Smarties book. Selection is very fast because
> any node's children have node ID's between the right and left nodes of  
> said node, so there's no mucking about
> with connect by and what not. There's a synopsis linked at the PG  
> Cookbook pages (http://www.brasileiro.net/postgres/cookbook),
> but the cookbook seems to off-line (I think I'll offer to mirror it -  
> this happens frequently). There's another description at
> http://www.intelligententerprise.com/001020/celko.jhtml? 
> _requestid=65750.

I have a full implementation of this.  I was going to do it as a magazine 
article, so I've been holding it off line.   However, publication seems to be 
indefinitely delayed, so I'll probably post it on TechDocs as soon as I have 
time.

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco




^ permalink  raw  reply  [nested|flat] 22+ messages in thread

* Re: column size too large, is this a bug?
@ 2004-03-29 17:29 Jaime Casanova <el_vigia_ec@hotmail.com>
  0 siblings, 0 replies; 22+ messages in thread

From: Jaime Casanova @ 2004-03-29 17:29 UTC (permalink / raw)
  To: pgsql-performance

thanx a lot

_________________________________________________________________
STOP MORE SPAM with the new MSN 8 and get 2 months FREE* 
http://join.msn.com/?page=features/junkmail




^ permalink  raw  reply  [nested|flat] 22+ messages in thread


end of thread, other threads:[~2004-03-30 14:20 UTC | newest]

Thread overview: 22+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-25 17:58 column size too large, is this a bug? Qing Zhao <qzhao@quotefx.net>
2004-03-25 18:20 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-25 22:20   ` Qing Zhao <qzhao@quotefx.net>
2004-03-25 22:28     ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-25 22:57 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2004-03-25 23:11   ` Qing Zhao <qzhao@quotefx.net>
2004-03-26 00:39   ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-26 01:21     ` Josh Berkus <josh@agliodbs.com>
2004-03-26 02:04       ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-26 03:00         ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-03-26 03:11           ` Tom Lane <tgl@sss.pgh.pa.us>
2004-03-26 03:38             ` Christopher Kings-Lynne <chriskl@familyhealth.com.au>
2004-03-26 17:29         ` Qing Zhao <qzhao@quotefx.net>
2004-03-26 18:01           ` Josh Berkus <josh@agliodbs.com>
2004-03-26 18:15           ` Andrew Rawnsley <ronz@ravensfield.com>
2004-03-28 19:25             ` Josh Berkus <josh@agliodbs.com>
2004-03-28 19:49               ` Andrew Rawnsley <ronz@ravensfield.com>
2004-03-30 14:20               ` Robert Treat <xzilla@users.sourceforge.net>
2004-03-29 17:05 Re: column size too large, is this a bug? Jaime Casanova <el_vigia_ec@hotmail.com>
2004-03-29 17:25 ` Andrew Rawnsley <ronz@ravensfield.com>
2004-03-29 19:12   ` Josh Berkus <josh@agliodbs.com>
2004-03-29 17:29 Re: column size too large, is this a bug? Jaime Casanova <el_vigia_ec@hotmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox