pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
abusing an aggregate funct
7+ messages / 3 participants
[nested] [flat]

* abusing an aggregate funct
@ 1998-11-14 12:00 Thomas Good <tomg@admin.nrnet.org>
  1998-11-14 15:01 ` Re: [SQL] abusing an aggregate funct Marc Howard Zuckman <marc@fallon.classyad.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Thomas Good @ 1998-11-14 12:00 UTC (permalink / raw)
  To: pgsql-sql

Hi all...

I ported a foxpro db to postgres - the foxpro strategy (if this is
not a non-sequitor) was to put everything in one big table...
with no unique constraints on any attr/no p_key.

I had perl iterate thru the dump file prepending an int value to each
tuple - which is now my p_key.  Works fine.

Due to the sheer girth of the table the following query, called from
a data entry script,  takes a bit of time to finish:

SELECT max(rec_num) FROM crtrd1;

I use this to setup `rec_num + 1' for my query number (p_key)...any
way I can speed this up a bit?  I've run vacuum analyze but the
table size is doing me in...

Stuck_In_Staten_Island,
Tom

(thanks ;-)

    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----     
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056




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

* Re: [SQL] abusing an aggregate funct
  1998-11-14 12:00 abusing an aggregate funct Thomas Good <tomg@admin.nrnet.org>
@ 1998-11-14 15:01 ` Marc Howard Zuckman <marc@fallon.classyad.com>
  1998-11-15 14:04   ` Re: [SQL] abusing an aggregate funct Postgres DBA <postgres@nest.bistbn.com>
  1998-11-15 15:08   ` Re: [SQL] abusing an aggregate funct Thomas Good <tomg@admin.nrnet.org>
  0 siblings, 2 replies; 7+ messages in thread

From: Marc Howard Zuckman @ 1998-11-14 15:01 UTC (permalink / raw)
  To: Thomas Good <tomg@admin.nrnet.org>; +Cc: pgsql-sql

On Sat, 14 Nov 1998, Thomas Good wrote:

> Due to the sheer girth of the table the following query, called from
> a data entry script,  takes a bit of time to finish:
> 
> SELECT max(rec_num) FROM crtrd1;
> 
> I use this to setup `rec_num + 1' for my query number (p_key)...any
> way I can speed this up a bit?  I've run vacuum analyze but the
> table size is doing me in...
> 
Use a sequence to generate rec_num.  See man create_sequence.

You can then use curval('sequence_name') to get the last
sequence number generated, or if you are performing an insert:
  insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata);

You could also create the table using nextval as the default
value of p_key.  You may wish to create a unique_index on
p_key, but nextval is guaranteed not to generate duplicates
unless you allow it to rollover.





Marc Zuckman
marc@fallon.classyad.com

_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_     Visit The Home and Condo MarketPlace		      _
_          http://www.ClassyAd.com			      _
_							      _
_  FREE basic property listings/advertisements and searches.  _
_							      _
_  Try our premium, yet inexpensive services for a real	      _
_   selling or buying edge!				      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_




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

* Re: [SQL] abusing an aggregate funct
  1998-11-14 12:00 abusing an aggregate funct Thomas Good <tomg@admin.nrnet.org>
  1998-11-14 15:01 ` Re: [SQL] abusing an aggregate funct Marc Howard Zuckman <marc@fallon.classyad.com>
@ 1998-11-15 14:04   ` Postgres DBA <postgres@nest.bistbn.com>
  1 sibling, 0 replies; 7+ messages in thread

From: Postgres DBA @ 1998-11-15 14:04 UTC (permalink / raw)
  To: Marc Howard Zuckman <marc@fallon.classyad.com>; +Cc: Thomas Good <tomg@admin.nrnet.org>; pgsql-sql



On Sat, 14 Nov 1998, Marc Howard Zuckman wrote:

> On Sat, 14 Nov 1998, Thomas Good wrote:
> 
> > Due to the sheer girth of the table the following query, called from
> > a data entry script,  takes a bit of time to finish:
> > 
> > SELECT max(rec_num) FROM crtrd1;
> > 
> > I use this to setup `rec_num + 1' for my query number (p_key)...any
> > way I can speed this up a bit?  I've run vacuum analyze but the
> > table size is doing me in...
> > 
> Use a sequence to generate rec_num.  See man create_sequence.
> 
> You can then use curval('sequence_name') to get the last
> sequence number generated, or if you are performing an insert:
>   insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata);
> 
> You could also create the table using nextval as the default
> value of p_key.  You may wish to create a unique_index on
> p_key, but nextval is guaranteed not to generate duplicates
> unless you allow it to rollover.
> 
> 
Unfortunately, solution using sequences is not so good unless you don't 
suppose to use access to that table from some simultaneously running
sessions. The problem is that every sessions accessing this additional
sequence  with nextval()  will get its own pool of values for the
sequence, so early or later you'll get some gaps in records numbering
because of at least on of two reasons:
	a) usually the length of such a pool is set to limit that is
bigger then 1 and  obviously, it's impossible to check the number of
inserts (and thus the number of nestval`s) during every sessions. 
	b) and in case of pool with 1-length some of your inserts may
fail, but nextavl would be called and next time you try insert you'll call
nextval again and will miss one or more values of sequence.


Also, you can't issue curval until you haven't call nextval at least once
during currunt session -- this is also source of gaps in values from
sequence assigned to records in the table. So, I think this method can
produce only approximation of real ammount of records:-(


Aleksey.




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

* Re: [SQL] abusing an aggregate funct
  1998-11-14 12:00 abusing an aggregate funct Thomas Good <tomg@admin.nrnet.org>
  1998-11-14 15:01 ` Re: [SQL] abusing an aggregate funct Marc Howard Zuckman <marc@fallon.classyad.com>
@ 1998-11-15 15:08   ` Thomas Good <tomg@admin.nrnet.org>
  1 sibling, 0 replies; 7+ messages in thread

From: Thomas Good @ 1998-11-15 15:08 UTC (permalink / raw)
  To: Marc Howard Zuckman <marc@fallon.classyad.com>; +Cc: pgsql-sql

On Sat, 14 Nov 1998, Marc Howard Zuckman wrote:

> Use a sequence to generate rec_num.  See man create_sequence.

Got it.  
Thanks Marc!

> You can then use curval('sequence_name') to get the last
> sequence number generated, or if you are performing an insert:
>   insert into crtrd1 (p_key,otherdata) values (nextval('sequence_name',moredata);
> 
> You could also create the table using nextval as the default
> value of p_key.  You may wish to create a unique_index on
> p_key, but nextval is guaranteed not to generate duplicates
> unless you allow it to rollover.
> 
> 
> 
> 
> 
> Marc Zuckman
> marc@fallon.classyad.com
> 
> _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
> _     Visit The Home and Condo MarketPlace		      _
> _          http://www.ClassyAd.com			      _
> _							      _
> _  FREE basic property listings/advertisements and searches.  _
> _							      _
> _  Try our premium, yet inexpensive services for a real	      _
> _   selling or buying edge!				      _
> _\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
> 


    ---------- Sisters of Charity Medical Center ----------
                   Department of Psychiatry
                            ----     
    Thomas Good                          <tomg@q8.nrnet.org>
    Coordinator, North Richmond C.M.H.C. Information Systems
    75 Vanderbilt Ave, Quarters 8        Phone: 718-354-5528
    Staten Island, NY   10304            Fax:   718-354-5056




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

* Re: [SQL] abusing an aggregate funct
@ 1998-11-15 14:46 Postgres DBA <postgres@nest.bistbn.com>
  1998-11-15 16:04 ` Re: [SQL] abusing an aggregate funct Marc Howard Zuckman <marc@fallon.classyad.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Postgres DBA @ 1998-11-15 14:46 UTC (permalink / raw)
  To: M Simms <grim@argh.demon.co.uk>; +Cc: pgsql-sql



On Sun, 15 Nov 1998, M Simms wrote:

> > Unfortunately, solution using sequences is not so good unless you don't 
> > suppose to use access to that table from some simultaneously running
> > sessions. The problem is that every sessions accessing this additional
> > sequence  with nextval()  will get its own pool of values for the
> > sequence, so early or later you'll get some gaps in records numbering
> > because of at least on of two reasons:
> 
> >From the manpage of create sequence
> 
> 
>        Low-level locking is used to enable multiple  simultaneous
>        calls to a generator.
> 
> Doesnt this mean it will work for this task?
> 
> 

Sure, you can access one sequence from a couple of sessions at once but
it the only thing is guaranteed is UNIQUENESS of values you get from
nestval() across one sequence. Although nobody can promise you that there
will no gaps... There are lots of reasons, I mentioned just some of them
(see my previous posting in this thread)

Aleksey






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

* Re: [SQL] abusing an aggregate funct
  1998-11-15 14:46 Re: [SQL] abusing an aggregate funct Postgres DBA <postgres@nest.bistbn.com>
@ 1998-11-15 16:04 ` Marc Howard Zuckman <marc@fallon.classyad.com>
  1998-11-16 08:31   ` Re: [SQL] abusing an aggregate funct Postgres DBA <postgres@nest.bistbn.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Marc Howard Zuckman @ 1998-11-15 16:04 UTC (permalink / raw)
  To: Postgres DBA <postgres@nest.bistbn.com>; +Cc: M Simms <grim@argh.demon.co.uk>; pgsql-sql

On Sun, 15 Nov 1998, Postgres DBA wrote:

> 
> 
> On Sun, 15 Nov 1998, M Simms wrote:
> 
> > > Unfortunately, solution using sequences is not so good unless you don't 
> > > suppose to use access to that table from some simultaneously running
> > > sessions. The problem is that every sessions accessing this additional
> > > sequence  with nextval()  will get its own pool of values for the
> > > sequence, so early or later you'll get some gaps in records numbering
> > > because of at least on of two reasons:
> > 
> > >From the manpage of create sequence
> > 
> > 
> >        Low-level locking is used to enable multiple  simultaneous
> >        calls to a generator.
> > 
> > Doesnt this mean it will work for this task?
> > 
> > 
> 
> Sure, you can access one sequence from a couple of sessions at once but
> it the only thing is guaranteed is UNIQUENESS of values you get from
> nestval() across one sequence. Although nobody can promise you that there
> will no gaps... There are lots of reasons, I mentioned just some of them
> (see my previous posting in this thread)
> 
> Aleksey
> 
> 
> 
> 

My understanding is that if you do not enable the cache feature
of the sequence, there will be no gaps.  Of course, disabling the
cache slows the transaction.

Marc Zuckman
marc@fallon.classyad.com

_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_
_     Visit The Home and Condo MarketPlace		      _
_          http://www.ClassyAd.com			      _
_							      _
_  FREE basic property listings/advertisements and searches.  _
_							      _
_  Try our premium, yet inexpensive services for a real	      _
_   selling or buying edge!				      _
_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_




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

* Re: [SQL] abusing an aggregate funct
  1998-11-15 14:46 Re: [SQL] abusing an aggregate funct Postgres DBA <postgres@nest.bistbn.com>
  1998-11-15 16:04 ` Re: [SQL] abusing an aggregate funct Marc Howard Zuckman <marc@fallon.classyad.com>
@ 1998-11-16 08:31   ` Postgres DBA <postgres@nest.bistbn.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Postgres DBA @ 1998-11-16 08:31 UTC (permalink / raw)
  To: Marc Howard Zuckman <marc@fallon.classyad.com>; +Cc: M Simms <grim@argh.demon.co.uk>; pgsql-sql



On Sun, 15 Nov 1998, Marc Howard Zuckman wrote:

> On Sun, 15 Nov 1998, Postgres DBA wrote:
> 
> > 
> > 
> > On Sun, 15 Nov 1998, M Simms wrote:
> > 
> > > > Unfortunately, solution using sequences is not so good unless you don't 
> > > > suppose to use access to that table from some simultaneously running
> > > > sessions. The problem is that every sessions accessing this additional
> > > > sequence  with nextval()  will get its own pool of values for the
> > > > sequence, so early or later you'll get some gaps in records numbering
> > > > because of at least on of two reasons:
> > > 
> > > >From the manpage of create sequence
> > > 
> > > 
> > >        Low-level locking is used to enable multiple  simultaneous
> > >        calls to a generator.
> > > 
> > > Doesnt this mean it will work for this task?
> > > 
> > > 
> > 
> > Sure, you can access one sequence from a couple of sessions at once but
> > it the only thing is guaranteed is UNIQUENESS of values you get from
> > nestval() across one sequence. Although nobody can promise you that there
> > will no gaps... There are lots of reasons, I mentioned just some of them
> > (see my previous posting in this thread)
> > 
> > Aleksey
> > 
> > 
> > 
> > 
> 
> My understanding is that if you do not enable the cache feature
> of the sequence, there will be no gaps.  Of course, disabling the
> cache slows the transaction.

You are extremly right:-) Nevertheless, don't forget of another problem:
you CAN'T call curval() before nextval(). So, every time you want to check
the maximum row number in the table you should increase sequence counter
using nextval() -- here is real source of gaps, and I don't know if it is
possible to overcome it :_( The only idea I have for now is to check 
last_value field of the sequence instead of calling curval()...

But again, what to do with nextval() calls during rollbacked transaction??

Aleksey.




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


end of thread, other threads:[~1998-11-16 08:31 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1998-11-14 12:00 abusing an aggregate funct Thomas Good <tomg@admin.nrnet.org>
1998-11-14 15:01 ` Marc Howard Zuckman <marc@fallon.classyad.com>
1998-11-15 14:04   ` Postgres DBA <postgres@nest.bistbn.com>
1998-11-15 15:08   ` Thomas Good <tomg@admin.nrnet.org>
1998-11-15 14:46 Re: [SQL] abusing an aggregate funct Postgres DBA <postgres@nest.bistbn.com>
1998-11-15 16:04 ` Marc Howard Zuckman <marc@fallon.classyad.com>
1998-11-16 08:31   ` Postgres DBA <postgres@nest.bistbn.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