Re: [GENERAL] Cognitive dissonance

[Greg Smith]

John Gage wrote:
Posters are correctly referred to the documentation as frequently as 
possible.  In fact, very frequently.  The frequency might decrease if 
the documentation were in plain text.  It is easier to search a single 
plain text file than any other source, except perhaps the database 
itself.


In reality searches are being done on the web, which combines the HTML 
version of the official documentation with blog posts, presentation 
materials, the wiki, and similar other resources.  This is why I don't 
actually care about a text version of the docs; I've just gotten used to 
using Google to search the PostgreSQL documentation.  The occasional 
time when I know I just want to search the manual instead, I can search 
the PDF version.  Neither of those are great solutions, but they're good 
enough that it's not worth fighting to build a text version over as I 
see it.  I'd use it if it were around, but there's little motivation for 
most of us to work on it.


Postgres is getting pushed off the map at the low end by MySQL, now 
owned by Oracle.


The dynamics are much more complicated than that.  Big MySQL sites are 
switching to NoSQL; medium sized MySQL sites are switching to PostgreSQL 
to get rid of scaling and reliability issues (I personally have been 
seeing a lot of this from Rails installs lately); small to medium size 
Oracle shops are switching to PostgreSQL to lower licensing costs.


The idea that plain-text documentation for the database would be a 
significant driver in any of these trends would be greatly exaggerating 
the significance of a technical detail important to a pretty small 
number of people.  On my personal list of "things that could be improved 
in the documentation", good plain text format is there, but there's a 
whole lot of things above it.


--
Greg Smith  2ndQuadrant US  Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com   www.2ndQuadrant.us


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

Re: [GENERAL] Cognitive dissonance

[John Gage]

1) On a list that howls with complaints when posts are in html, it is  
surprising that there is resistance to the idea of documentation in  
plain text.


2) Posters are correctly referred to the documentation as frequently  
as possible.  In fact, very frequently.  The frequency might decrease  
if the documentation were in plain text.  It is easier to search a  
single plain text file than any other source, except perhaps the  
database itself.


3) Postgres is getting pushed off the map at the low end by MySQL, now  
owned by Oracle.If Postgres ceased to exist, Ellison would be  
thrilled.  I chose A2 Hosting (with whom I am very happy) for my  
website because they support Postgres.  I'm writing cgi scripts in  
perl.  I had to install the postgres driver for dbi.  It was not pre- 
installed.  There are about four buttons for MySQL on the cPanel and  
two farther over on the right for Postgres.


An anecdote.  I discovered the tsvector functionality a while back.  I  
have used it to create indices for my text files and several other  
tasks.  I recently was re-looking at my files and saw  
"tsvector::text".  I had forgotten that the double colon is one way to  
cast a type.  Double colon is not in the html index of the  
documentation.  I found it by searching my plain text version of the  
pdf file.  In my opinion, the html documentation is useful for reading  
it like a novel or referencing it in these lists.



On Jun 8, 2010, at 9:56 PM, Josh Kupershmidt wrote:


Not that I see a whole lot of utility in this endeavor



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

Re: [GENERAL] Some insight on the proper SQL would be appreciated

[Tim Landscheidt]

Andy Colson  wrote:

>> thanks very much Andy. Very elegant.

>> I do need to presere the users that have<5 entries though, so I think I can
>> modify your function to do that as well.

> Oh, duh!  because nothing is less than 1900-01-01...  my
> date math sucks.  It should probably return '2100-01-01' or
> something.

If you're using stored functions, you could (and should be-
cause the whole table is probably read anyhow) also code a
function that reads all entries, resets a counter at the
start and whenever the user changes, increments it on every
row and returns all rows where the counter is less than
five.

Tim


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

Re: [GENERAL] Queues Problem

[Vick Khera]

On Tue, Jun 8, 2010 at 2:01 PM, uaca man  wrote:
>> You will have to write your code to be more event
>>driven, and make the web server just generate requests and view the
>>results where they are stored.
>
> What do you mean? That is what I think I am trying to do. No?

You have work you need done.  You want to send that work to some
worker (a cron job or never-ending loop in your description) to do the
work and get back an answer either directly or posted to some place
(the database).  Seems to me like gearman and or AMQP would be ideal
solutions, and you can then have as many workers as you want and not
worry about contention of querying for the next job in the table which
inherently requires some form of locking.

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

Re: [GENERAL] What does PGDG mean, in the Postgres YUM repositories?

[Massa, Harald Armin]

>Hi.  What does "PGDG" mean, as in PGDG84, the PostgreSQL >repository
containing PostgreSQL 8.4.x, etc?

Postgresql Global Development Group ?

Harald

-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
no fx, no carrier pigeon
-
Using PostgreSQL is mostly about sleeping well at night.

[GENERAL] What does PGDG mean, in the Postgres YUM repositories?

[Aleksey Tsalolikhin]

Hi.  What does "PGDG" mean, as in PGDG84, the PostgreSQL repository
containing PostgreSQL 8.4.x, etc?

Thanks,
Aleksey

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

Re: [GENERAL] Postgres 8.4 segfaults on CentOS 5.5 (using EnterpriseDB installers)

[Aleksey Tsalolikhin]

Dear Steve,

  Thanks for your suggestions!

  I ended up switching from the Enterprise DB installer to the
Postgres PGDG84 YUM repository, since (a) it provides PostgreSQL 8.4.4
AND Slony 1.x (unlike Enterprise DB installer which only has
Slony 2.0.2 which has a potential data loss problem), and now
I can keep my entire configuration in RPM packages.

Thanks!
Aleksey

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

Re: [GENERAL] Queues Problem

[uaca man]

2010/6/8 Peter Hunsberger 

> On Tue, Jun 8, 2010 at 1:26 PM, uaca man  wrote:
> >> 2) Think of the front end as changing states as the user interacts
> >> with it, then figure out what queries need to be made to correspond to
> >> the changes in state.  For example, it is unlikely the user needs the
> >> amount of "gold" updated every 5 seconds.  Rather, they need to know
> >> how much they have on hand when they go to use it.  At that point, you
> >> query for the old balance, find the last updated time, how many
> >> buildings have been completed since then and for how long and figure
> >> out what the new gold balance is.  Update the new balance at that
> >> point (with a timestamp), and the front end goes on it's merry way...
> >
> > That is exactly what we are doing for the most part and was our first bet
> > with the buildings, however since building can affect pretty much
> anything,
> > anywhere on the game changing states as the user interacts is getting
> beyond
> > comprehension of a human mind(al least for my mind) and that was when I
> had
> > the super idea, lest put the queue on the crontab!
>
> Then each thing the building interacts with has it's own unique set of
> states.  The only ones you need worry about are the ones a _user_ is
> actually interacting with at any given point.
>
> >
> > Looks like we are going to cut off a few options of the game.
> > ps: do i top post or bottom post here?
> >
>
> I guess that there is no easy solution, will try the first bet and
> calculate the past every user request.
>


> --
> Peter Hunsberger
>

[GENERAL] full-text search configuration question

[Andrew Geery]

I have several tables with text columns that contain information that
I would like to be able to search using a FTS index.  For each text
column, there is also a column that is a foreign key into a language
table for the language used in the text.  My idea is to add a column
to the language table to reference a FTS configuration for a given
language and to write a function to wrap a call to to_tsvector (after
looking up the given configuration name from the language) as per the
documentation 
[http://www.postgresql.org/docs/8.4/interactive/textsearch-tables.html].
 However, functions used in indexes must be immutable and while the
FTS configuration probably wouldn't change very often for a given
language, I suppose it is possible.

My question is: am I better off putting a column in each table with
which I want to use FTS to store the FTS config name or can I get away
with pushing the FTS configuration name into the language table?

Thanks
Andrew

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

Re: [GENERAL] Postgres 8.4 segfaults on CentOS 5.5 (using EnterpriseDB installers)

[Steve Crawford]

On 06/04/2010 06:28 PM, Aleksey Tsalolikhin wrote:

Hi.

  ...
Now we have our first CentOS 5.5 server (64-bit) and I installed
PostgreSQL 8.4.4 using the EnterpriseDB installer, and it is unable to
start the database instance.  If I try to start it manually, I get a
Segmentation Fault.  I tried the 8.4.2 installer, but the installed
binaries segfault too.  Have you seen this?  Any suggestions to
resolve it?

...

Any suggestions, please?

   
Since it doesn't look like anyone has jumped in with ideas I'll take 
some shots in the dark. I haven't seen this issue come up before and 
CentOS lags RHEL and is supposed to be basically binary-identical so I 
suspect it is related something about your particular install.


Is it possible that you (perhaps as a dependency) have some parts of 
PostgreSQL installed by CentOS and are experiencing conflicts.


I would both verify that you really have disabled SELinux and also try 
removing all traces of all installed copy/copies of PG and installing 
the (now version 8.4) PostgreSQL installation from the CentOS 5.5 
distribution and see if that works then go from there.


Cheers,
Steve

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

Re: [GENERAL] Cognitive dissonance

[Josh Kupershmidt]

On Tue, Jun 8, 2010 at 5:04 AM, John Gage  wrote:
> I do suggest that a plain text file of the entire documentation be made part
> of the documentation armamentarium.

Not that I see a whole lot of utility in this endeavor, but it's
possible to do a decent PDF to plain text conversion. I tried some of
the online tools that do this and found 
to do the best job with the Postgres manual. I didn't bother trying
any client-side applications which do the same job.

Attached is a short snippet of a text export of the PDF manual.

Josh

 Preface

   This book is the official documentation of PostgreSQL.  It has been 
written by the PostgreSQL  develop-
   ers and other volunteers in parallel to the development of the 
PostgreSQL  software. It describes all the
   functionality that the current version of PostgreSQL  officially 
supports.
   To make  the large amount of information about PostgreSQL 
manageable, this book has been organized
   in several parts. Each  part is targeted at a different class of 
users, or at users in different stages of their
   PostgreSQL  experience:

   ·  Part I is an informal introduction for new users.
   ·  Part II documents the SQL  query language environment, including 
data types and functions, as well as
  user-level performance tuning. Every PostgreSQL user should read 
this.
   ·  Part III describes the installation and administration of the 
server. Everyone who  runs a PostgreSQL
  server, be it for private use or for others, should read this 
part.
   ·  Part IV describes the programming  interfaces for PostgreSQL  
client programs.
   ·  Part V contains information for advanced users about the 
extensibility capabilities of the server. Topics
  include user-defined data types and functions.
   ·  Part VI contains reference information about SQL  commands,  
client and server programs. This part
  supports the other parts with structured information sorted by 
command   or program.
   ·  Part VII contains assorted information that might be of use to 
PostgreSQL  developers.




 1.  What  is   PostgreSQL?

   PostgreSQL  is an object-relational database management  system 
(ORDBMS)based on  POSTGRES,
   Version 4.21, developed at the University of California at Berkeley 
Computer  Science Department. POST-
   GRES   pioneered many  concepts that only became available in some  
commercial database systems much
   later.
   PostgreSQL  is an open-source descendant of this original Berkeley 
code. It supports a large part of the
   SQL  standard and offers many modern  features:

   ·  complex queries
   ·  foreign keys
   ·  triggers
   ·  views
   ·  transactional integrity
   ·  multiversion concurrency control
   Also, PostgreSQL can be extended by the user in many  ways, for 
example by adding new

   ·  data types

 1. http://s2k-ftp.CS.Berkeley.EDU:8000/postgres/postgres.html






 xlix



 Preface

   ·  functions
   ·  operators
   ·  aggregate functions
   ·  index methods
   ·  procedural languages


   And  because of the liberal license, PostgreSQL  can be used, 
modified, and distributed by anyone free of
   charge for any purpose, be it private, commercial, or academic.



 2.  A   Brief History   of   PostgreSQL

   The object-relational database management  system now known  as 
PostgreSQL  is derived from the POST-
   GRES   package written at the University of California at Berkeley. 
With over two decades of development
   behind it, PostgreSQL  is now the most advanced open-source database 
available anywhere.


   2.1.   The   Berkeley   POSTGRES   Project
   The POSTGRESproject, led by Professor Michael Stonebraker, was 
sponsored by the Defense Advanced
   Research Projects Agency (DARPA),  the Army Research Office (ARO),  
the National Science Foundation
   (NSF), and ESL,  Inc. The implementation of POSTGRESbegan in 
1986. The initial concepts for the
   system were presented in The design of POSTGRES, and the 
definition of the initial data model appeared
   in The POSTGRESdata model . The design of the rule system at 
that time was described in The design
   of the POSTGRESrules system. The rationale and architecture of 
the storage manager  we

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Jeff Amiel]

On 6/8/10 2:03 PM, "Alvaro Herrera"  wrote:

> 
> I've seen this problem (and others) in a high-load environment.  Not
> Slony related though.
> 
> I wrote a small tool to check btree index files for consistency problems
> such as this one, by parsing pg_filedump output.  I've seen strange
> things such as index pointers pointing to pages that shouldn't have been
> pointed to; mismatching sibling pointers; and others.
> 
> Do you have a copy of the broken index file?

Alas, no.  But I have another 5 days to reproduce the problem before
changing hardware/versions (which will probably bring a whole new set of
'opportunities' to deal with).  If it happens again, I will snag the index
file for analysis.

Any way you can share your tool source so I can proactively look for issues
in my indexes?



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

Re: [GENERAL] Some insight on the proper SQL would be appreciated

[Andy Colson]

On 6/8/2010 2:08 PM, Aaron Burnett wrote:


thanks very much Andy. Very elegant.

I do need to presere the users that have<5 entries though, so I think I can
modify your function to do that as well.

Thanks again.




Oh, duh!  because nothing is less than 1900-01-01...  my date math 
sucks.  It should probably return '2100-01-01' or something.


-Andy

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

Re: [GENERAL] Some insight on the proper SQL would be appreciated

[Andy Colson]

On 6/8/2010 11:29 AM, Aaron Burnett wrote:


Greetings,

I hope this is the proper list for this, but I am a loss on how to achieve
one particular set of results.

I have a table which is a list of users who entered a contest. They can
enter as many times as they want, but only 5 will count. So some users have
one entry, some have as many as 15.

How could I distill this down further to give me a list that shows each
entry per user up to five entries per user? In other words, I need a
separate line item for each entry from each user up to the maximum of 5 rows
per user.

Table looks like this:
username   | firstname |  lastname   |  signedup
--+---+-+---
-
   ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-03-13
   ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-05-07
   ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-06-06
   ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch   | 2010-03-12
   ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25
   ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09
   ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay   | Maher   | 2010-04-20
   fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn   | Woodul  | 2010-04-05
   fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03
   feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03
   feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05
   fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer  | Lavigne | 2010-02-09
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-20
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-27
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-03
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-10
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-17
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-25
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-01
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-08
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-16
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-22
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-30
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-06-06
   fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-12
   fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-15

But in John Smith's case where he has more than 5 entries, I would like
query results to limit him to just 5 entries to look like this:

username   | firstname |  lastname   |  signedup
--+---+-+---
-
   ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-03-13
   ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-05-07
   ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-06-06
   ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch   | 2010-03-12
   ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25
   ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09
   ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay   | Maher   | 2010-04-20
   fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn   | Woodul  | 2010-04-05
   fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03
   feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03
   feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05
   fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer  | Lavigne | 2010-02-09
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-20
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-27
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-03
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-10
   fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-17
   fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-12
   fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-15

The username is unique for each user.

pg version 8.25 on RHEL

Any help in this would be greatly appreciated.

Thank you.




Ok, here we go.  Add this function:

CREATE OR REPLACE FUNCTION fifth(uid text)
   RETURNS timestamp without time zone
   LANGUAGE plpgsql
   IMMUTABLE
AS $function$
declare
  result timestamp;
begin
  select signedup into result from users where u

Re: [GENERAL] Queues Problem

[Peter Hunsberger]

On Tue, Jun 8, 2010 at 1:26 PM, uaca man  wrote:
>> 2) Think of the front end as changing states as the user interacts
>> with it, then figure out what queries need to be made to correspond to
>> the changes in state.  For example, it is unlikely the user needs the
>> amount of "gold" updated every 5 seconds.  Rather, they need to know
>> how much they have on hand when they go to use it.  At that point, you
>> query for the old balance, find the last updated time, how many
>> buildings have been completed since then and for how long and figure
>> out what the new gold balance is.  Update the new balance at that
>> point (with a timestamp), and the front end goes on it's merry way...
>
> That is exactly what we are doing for the most part and was our first bet
> with the buildings, however since building can affect pretty much anything,
> anywhere on the game changing states as the user interacts is getting beyond
> comprehension of a human mind(al least for my mind) and that was when I had
> the super idea, lest put the queue on the crontab!

Then each thing the building interacts with has it's own unique set of
states.  The only ones you need worry about are the ones a _user_ is
actually interacting with at any given point.

>
> Looks like we are going to cut off a few options of the game.
> ps: do i top post or bottom post here?
>

Bottom post.
-- 
Peter Hunsberger

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

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Alvaro Herrera]

Excerpts from Jeff Amiel's message of mar jun 08 09:26:25 -0400 2010:
> Not looking for help...just putting some data out there.
> 
> 2 previous crashes caused by corrupt slony indexes
> 
> http://archives.postgresql.org/pgsql-general/2010-02/msg00022.php
> 
> http://archives.postgresql.org/pgsql-general/2009-12/msg01172.php
> 
> New one yesterday.
> 
> Jun  7 15:05:01 db-1 postgres[9334]: [ID 748848 local0.crit] [3989781-1] 
> 2010-06-07 15:05:01.087 CDT9334PANIC:  right sibling 169 of block 168 is 
> not next child of 249 in index "sl_seqlog_idx"

I've seen this problem (and others) in a high-load environment.  Not
Slony related though.

I wrote a small tool to check btree index files for consistency problems
such as this one, by parsing pg_filedump output.  I've seen strange
things such as index pointers pointing to pages that shouldn't have been
pointed to; mismatching sibling pointers; and others.

Do you have a copy of the broken index file?

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Alvaro Herrera]

Excerpts from Jeff Amiel's message of mar jun 08 14:19:02 -0400 2010:

> " It seems preferable to configure autovacuum to avoid vacuum
> Slony-I-managed configuration tables. "
> 
> 
> HmmmI don't do this.
> Surely this is not relative to my corrupt indexes2 attempted vacuums on
> same indexes?

Pretty unlikely.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] Queues Problem

[Andy Colson]

On 6/8/2010 1:26 PM, uaca man wrote:

 > 2) Think of the front end as changing states as the user interacts
 > with it, then figure out what queries need to be made to correspond to
 > the changes in state.  For example, it is unlikely the user needs the
 > amount of "gold" updated every 5 seconds.  Rather, they need to know
 > how much they have on hand when they go to use it.  At that point, you
 > query for the old balance, find the last updated time, how many
 > buildings have been completed since then and for how long and figure
 > out what the new gold balance is.  Update the new balance at that
 > point (with a timestamp), and the front end goes on it's merry way...

That is exactly what we are doing for the most part and was our first
bet with the buildings, however since building can affect pretty much
anything, anywhere on the game changing states as the user interacts is
getting beyond comprehension of a human mind(al least for my mind) and
that was when I had the super idea, lest put the queue on the crontab!

Looks like we are going to cut off a few options of the game.

ps: do i top post or bottom post here?



2010/6/8 Peter Hunsberger mailto:peter.hunsber...@gmail.com>>

On Tue, Jun 8, 2010 at 1:00 PM, uaca man mailto:uaca...@gmail.com>> wrote:
 > This would work except for one thing, the building may affect another
 > buildings, Consider this:
 >
 > the user starts one construction that will finish in 10 minutes
and the
 > building will give a bonus of +5 gold each seconds for the user.
This has to
 > be available in the seconds that the build is done and not one
hour late
 > because the user will lose the bonus for one hour.
 >

Sounds like you need to take a big step back and figure out what
overall systems architecture makes sense for a real time gaming
platform  Couple of suggestions:

1) If you need real time events you do them where you need them: on
the front end, where the user interaction is happening.  Do not
attempt to code gaming rules and logic into database triggers and back
end relationships (though you might store gaming rules etc. in a
table).

2) Think of the front end as changing states as the user interacts
with it, then figure out what queries need to be made to correspond to
the changes in state.  For example, it is unlikely the user needs the
amount of "gold" updated every 5 seconds.  Rather, they need to know
how much they have on hand when they go to use it.  At that point, you
query for the old balance, find the last updated time, how many
buildings have been completed since then and for how long and figure
out what the new gold balance is.  Update the new balance at that
point (with a timestamp), and the front end goes on it's merry way...

--
Peter Hunsberger




bottom.

This is a much harder question than I'd first thought.

If you go with the program that runs every second, I see one problem: 
what if the update takes more than a second to do?


I have an image of a spreadsheet in my mind, where there is a huge 
interdependence on the cells.  Have no idea what I'm talking about here, 
but how about some kind of state machine?  Then you can roll the state 
forward (or backward).  If the formula had some kind of time element 
then the "cells" could be recalculated for any given point in time.


ahh, well, in any case, I really dont know.  Sounds like an interesting 
problem though.


-Andy

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

Re: [GENERAL] Some insight on the proper SQL would be appreciated

[Andy Colson]

On 6/8/2010 11:29 AM, Aaron Burnett wrote:


Greetings,

I hope this is the proper list for this, but I am a loss on how to achieve
one particular set of results.

I have a table which is a list of users who entered a contest. They can
enter as many times as they want, but only 5 will count. So some users have
one entry, some have as many as 15.

How could I distill this down further to give me a list that shows each
entry per user up to five entries per user? In other words, I need a
separate line item for each entry from each user up to the maximum of 5 rows
per user.

Table looks like this:
   username   | firstname |  lastname   |  signedup
--+---+-+---
-
  ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-03-13
  ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-05-07
  ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-06-06
  ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch   | 2010-03-12
  ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25
  ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09
  ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay   | Maher   | 2010-04-20
  fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn   | Woodul  | 2010-04-05
  fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03
  feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03
  feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05
  fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer  | Lavigne | 2010-02-09
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-20
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-27
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-03
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-10
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-17
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-25
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-01
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-08
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-16
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-22
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-30
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-06-06
  fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-12
  fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-15

But in John Smith's case where he has more than 5 entries, I would like
query results to limit him to just 5 entries to look like this:

   username   | firstname |  lastname   |  signedup
--+---+-+---
-
  ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-03-13
  ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-05-07
  ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-06-06
  ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch   | 2010-03-12
  ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25
  ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09
  ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay   | Maher   | 2010-04-20
  fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn   | Woodul  | 2010-04-05
  fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03
  feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03
  feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05
  fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer  | Lavigne | 2010-02-09
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-20
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-27
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-03
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-10
  fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-17
  fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-12
  fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-15

The username is unique for each user.

pg version 8.25 on RHEL

Any help in this would be greatly appreciated.

Thank you.




Ok, here we go.  Add this function:

CREATE OR REPLACE FUNCTION fifth(uid text)
 RETURNS timestamp without time zone
 LANGUAGE plpgsql
 IMMUTABLE
AS $function$
declare
result timestamp;
begin
select signedup into result from users where usename = uid 
order by signedup limit 1 offset 4;

  

Re: [GENERAL] Cognitive dissonance

[John R Pierce]

Justin Graf wrote:

There are linux chm readers
  

...


Note that even Microsoft deprecated CHM back in 2003 after it was 
realized it was full of potential security exploits that couldn't 
readily be abated.




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

Re: [GENERAL] Queues Problem

[uaca man]

> 2) Think of the front end as changing states as the user interacts
> with it, then figure out what queries need to be made to correspond to
> the changes in state.  For example, it is unlikely the user needs the
> amount of "gold" updated every 5 seconds.  Rather, they need to know
> how much they have on hand when they go to use it.  At that point, you
> query for the old balance, find the last updated time, how many
> buildings have been completed since then and for how long and figure
> out what the new gold balance is.  Update the new balance at that
> point (with a timestamp), and the front end goes on it's merry way...

That is exactly what we are doing for the most part and was our first bet
with the buildings, however since building can affect pretty much anything,
anywhere on the game changing states as the user interacts is getting beyond
comprehension of a human mind(al least for my mind) and that was when I had
the super idea, lest put the queue on the crontab!

Looks like we are going to cut off a few options of the game.

ps: do i top post or bottom post here?


2010/6/8 Peter Hunsberger 

> On Tue, Jun 8, 2010 at 1:00 PM, uaca man  wrote:
> > This would work except for one thing, the building may affect another
> > buildings, Consider this:
> >
> > the user starts one construction that will finish in 10 minutes and the
> > building will give a bonus of +5 gold each seconds for the user. This has
> to
> > be available in the seconds that the build is done and not one hour late
> > because the user will lose the bonus for one hour.
> >
>
> Sounds like you need to take a big step back and figure out what
> overall systems architecture makes sense for a real time gaming
> platform  Couple of suggestions:
>
> 1) If you need real time events you do them where you need them: on
> the front end, where the user interaction is happening.  Do not
> attempt to code gaming rules and logic into database triggers and back
> end relationships (though you might store gaming rules etc. in a
> table).
>
> 2) Think of the front end as changing states as the user interacts
> with it, then figure out what queries need to be made to correspond to
> the changes in state.  For example, it is unlikely the user needs the
> amount of "gold" updated every 5 seconds.  Rather, they need to know
> how much they have on hand when they go to use it.  At that point, you
> query for the old balance, find the last updated time, how many
> buildings have been completed since then and for how long and figure
> out what the new gold balance is.  Update the new balance at that
> point (with a timestamp), and the front end goes on it's merry way...
>
> --
> Peter Hunsberger
>

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Jeff Amiel]

On 6/8/10 1:15 PM, "Jaime Casanova"  wrote:

> On Tue, Jun 8, 2010 at 12:49 PM, Jeff Amiel  wrote:
>> 
>> Does Slony manage it's own vacuuming separate from postgres' autovacuum?
>> 
> 
> Yes it does: http://www.slony.info/documentation/maintenance.html


" It seems preferable to configure autovacuum to avoid vacuum
Slony-I-managed configuration tables. "


HmmmI don't do this.
Surely this is not relative to my corrupt indexes2 attempted vacuums on
same indexes?
 


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

Re: [GENERAL] Queues Problem

[Peter Hunsberger]

On Tue, Jun 8, 2010 at 1:00 PM, uaca man  wrote:
> This would work except for one thing, the building may affect another
> buildings, Consider this:
>
> the user starts one construction that will finish in 10 minutes and the
> building will give a bonus of +5 gold each seconds for the user. This has to
> be available in the seconds that the build is done and not one hour late
> because the user will lose the bonus for one hour.
>

Sounds like you need to take a big step back and figure out what
overall systems architecture makes sense for a real time gaming
platform  Couple of suggestions:

1) If you need real time events you do them where you need them: on
the front end, where the user interaction is happening.  Do not
attempt to code gaming rules and logic into database triggers and back
end relationships (though you might store gaming rules etc. in a
table).

2) Think of the front end as changing states as the user interacts
with it, then figure out what queries need to be made to correspond to
the changes in state.  For example, it is unlikely the user needs the
amount of "gold" updated every 5 seconds.  Rather, they need to know
how much they have on hand when they go to use it.  At that point, you
query for the old balance, find the last updated time, how many
buildings have been completed since then and for how long and figure
out what the new gold balance is.  Update the new balance at that
point (with a timestamp), and the front end goes on it's merry way...

-- 
Peter Hunsberger

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

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Jaime Casanova]

On Tue, Jun 8, 2010 at 12:49 PM, Jeff Amiel  wrote:
>
> Does Slony manage it's own vacuuming separate from postgres' autovacuum?
>

Yes it does: http://www.slony.info/documentation/maintenance.html

-- 
Jaime Casanova www.2ndQuadrant.com
Soporte y capacitación de PostgreSQL

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

Re: [GENERAL] Query process is very slow

[Szymon Guz]

2010/6/8 Kalai R 

> Sir,
>
> I am using the following psql query with union and subqueries in my
> project.
>
>
> select a.areaname,f.fmno,fm.mno,f.aliasno as falias,f.salutation as
> fsal,f.headname, fm.aliasno as fmalias,fm.salutation as fmsal,fm.membername,
> extract (year from age(now(), to_date(fm.dob,'-MM-dd'))) as age,
> fr1.year,sum(fr2.amount) as amt  from fmember fm left outer join fmreceipt2
> fr2 on fm.mno=fr2.mno, fmreceipt1 fr1,prayerarea a,family f where fr2.mno in
> (select fm2.mno from fmreceipt1 fm1,fmreceipt2 fm2 where fm1.refno =
> fm2.refno and fr1.edate>='01-06-2010' and fr1.edate<='08-06-2010' group by
> fm2.mno,fm1.year having (sum(fm2.amount)>=0 and sum(fm2.amount)<=1000))and 
> a.areano=f.areano and fm.fmno=f.fmno and fm.dob<>'' and
> fr2.refno=fr1.refno group by
> a.areaname,f.fmno,fm.mno,f.aliasno,f.salutation,f.headname,fm.aliasno,
> fm.salutation,fm.membername,fm.dob,fr1.year,fr1.edate having extract  (year
> from age(now(), to_date(fm.dob,'-MM-dd')))>0 and fr1.edate>='01-06-2010'
> and fr1.edate<='08-06-2010' union select
> a.areaname,f.fmno,fm.mno,f.aliasno as falias,f.salutation as fsal,
> f.headname,fm.aliasno as fmalias,fm.salutation as fmsal,fm.membername,
> extract (year from age(now(), to_date(fm.dob,'-MM-dd'))) as age,0,0 from
> fmember fm,family f,prayerarea a where fm.mno not in (select fm2.mno from
> fmreceipt2 fm2) and f.fmno=fm.fmno and a.areano=f.areano and fm.dob<>''
> group by a.areaname,f.fmno,fm.mno,f.aliasno,f.salutation,f.headname,
> fm.aliasno,fm.salutation,fm.membername,fm.dob having extract  (year from
> age(now(), to_date(fm.dob,'-MM-dd')))>0 order by 1,2,3,11
>
>
> When I run this query it takes 1 minute to give result but in the receipt1
> table contains  4000 records only.
> I want to know what is the problem? Is there any mistake in the query?
>
> Kindly clarify.
>
> Thanks
> Gloier
>
>

Sorry, the query in this form is totally unreadable. First of all run
explain analyze and then paste the output here or use
http://explain.depesz.com/ and give us the link to the much
more readable version.

regards
Szymon Guz

[GENERAL] Query process is very slow

[Kalai R]

Sir,

I am using the following psql query with union and subqueries in my project.



select a.areaname,f.fmno,fm.mno,f.aliasno as falias,f.salutation as
fsal,f.headname, fm.aliasno as fmalias,fm.salutation as fmsal,fm.membername,
extract (year from age(now(), to_date(fm.dob,'-MM-dd'))) as age,
fr1.year,sum(fr2.amount) as amt  from fmember fm left outer join fmreceipt2
fr2 on fm.mno=fr2.mno, fmreceipt1 fr1,prayerarea a,family f where fr2.mno in
(select fm2.mno from fmreceipt1 fm1,fmreceipt2 fm2 where fm1.refno =
fm2.refno and fr1.edate>='01-06-2010' and fr1.edate<='08-06-2010' group by
fm2.mno,fm1.year having (sum(fm2.amount)>=0 and sum(fm2.amount)<=1000)) and
a.areano=f.areano and fm.fmno=f.fmno and fm.dob<>'' and fr2.refno=fr1.refno
group by
a.areaname,f.fmno,fm.mno,f.aliasno,f.salutation,f.headname,fm.aliasno,
fm.salutation,fm.membername,fm.dob,fr1.year,fr1.edate having extract  (year
from age(now(), to_date(fm.dob,'-MM-dd')))>0 and fr1.edate>='01-06-2010'
and fr1.edate<='08-06-2010' union select a.areaname,f.fmno,fm.mno,f.aliasno
as falias,f.salutation as fsal, f.headname,fm.aliasno as
fmalias,fm.salutation as fmsal,fm.membername, extract (year from age(now(),
to_date(fm.dob,'-MM-dd'))) as age,0,0 from fmember fm,family
f,prayerarea a where fm.mno not in (select fm2.mno from fmreceipt2 fm2) and
f.fmno=fm.fmno and a.areano=f.areano and fm.dob<>'' group by
a.areaname,f.fmno,fm.mno,f.aliasno,f.salutation,f.headname,
fm.aliasno,fm.salutation,fm.membername,fm.dob having extract  (year from
age(now(), to_date(fm.dob,'-MM-dd')))>0 order by 1,2,3,11


When I run this query it takes 1 minute to give result but in the receipt1
table contains  4000 records only.
I want to know what is the problem? Is there any mistake in the query?

Kindly clarify.

Thanks
Gloier

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Jeff Amiel]

On 6/8/10 12:56 PM, "Tom Lane"  wrote:

> Jeff Amiel  writes:
>> On a side note, I am 100% sure that autovacuum was disabled when I brought
>> the database back up after the core dump(s).  However, minutes after
>> restarting, some of my larger tables started getting vacuumed by pgsql user.
>> Any way that a vacuum would kick off for a particular table (or series of
>> tables) even when autovacuum was off in the postgresql.conf?
> 
> Anti-transaction-wraparound vacuums, perhaps?

I would hope not. :)
This is postgres 8.2.X.  Autovacuum has been enabled forever (seemingly with
no errors)
Anything I can look for ? (I searched the logs for references to "must be
vacuumed within" but found nothing)

SELECT datname, age(datfrozenxid) FROM pg_database;

postgres178649703

prod204588751

template1178653277

template0178653131



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

Re: [GENERAL] Queues Problem

[uaca man]

>You should investigate a proper queueing or job scheduling solution,
>such as RabbitMQ or Qpid or gearman.  They are designed for this type
>of requirement.

All of those(RabbitMQ , Qpid and gearman) are messages queue and are used to
exchange message between different process, system, applications or whatever
you like, much like IBM MQSeries Plataform.

I don’t see how this is related to problem. Did is miss something?

> You will have to write your code to be more event
>driven, and make the web server just generate requests and view the
>results where they are stored.

What do you mean? That is what I think I am trying to do. No?


2010/6/8 Vick Khera 

> On Tue, Jun 8, 2010 at 12:53 PM, uaca man  wrote:
> > Lets say for a 20 thousand users server, it may have at most 20 thousand
> > constructions started at the same time.
> >
> > To accomplish such behavior so far I could come up with two options:
> >
> > 1.   Make a never ending function that will look at the
>   BuildingQueue
> > table every second and finish the construction.
> >
> > 2.   Every time the user start a construction add a cron job for that
> > construction to run 1 seconds after the construction is finished and call
> a
> > function the will finish.
>
> You should investigate a proper queueing or job scheduling solution,
> such as RabbitMQ or Qpid or gearman.  They are designed for this type
> of requirement.  You will have to write your code to be more event
> driven, and make the web server just generate requests and view the
> results where they are stored.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Queues Problem

[uaca man]

This would work except for one thing, the building may affect another
buildings, Consider this:

the user starts one construction that will finish in 10 minutes and the
building will give a bonus of +5 gold each seconds for the user. This has to
be available in the seconds that the build is done and not one hour late
because the user will lose the bonus for one hour.


2010/6/8 Andy Colson 

> On 6/8/2010 11:53 AM, uaca man wrote:
>
>> Hello my fellow postgreSQL gurus. I´m a user of postgresSQL of quite
>> some time now, but most of my experience is consuming database, and for
>> the current project we are without a proper DBA and they have to bear
>> with me and so I must seek advice.
>>
>> I have a list of building and a queue and the user can start the
>> construction of one or more building that will take some time to build.
>> The problems lies with the fact this is a web browser game and the user
>> can log in, star the construction and log off, to further aggravate the
>> problem another user may take a look at the buildings, to add a little
>> bit more, when a building is done it may have effect on the user
>> population, gold and whatever the “imagination team” can come up with.
>>
>> Bottom line is: the construction has to be “concluded” with second’s
>> precision.
>>
>> Lets say for a 20 thousand users server, it may have at most 20 thousand
>> constructions started at the same time.
>>
>> To accomplish such behavior so far I could come up with two options:
>>
>> 1. Make a never ending function that will look at the BuildingQueue
>> table every second and finish the construction.
>>
>> 2. Every time the user start a construction add a cron job for that
>> construction to run 1 seconds after the construction is finished and
>> call a function the will finish.
>>
>> For some reason I can not believe that a never ending function is a good
>> idea and I don’t think cron jobs are meant to have 20 thousand jobs.
>>
>> Anyone care to share?
>>
>> Tables:
>>
>> Create table "Building"
>>
>> (
>>
>> "idBuilding" Serial NOT NULL,
>>
>> "description" Varchar(200),
>>
>> "time" Integer,
>>
>> primary key ("idBuilding")
>>
>> ) Without Oids;
>>
>> Create table "BuildingQueue"
>>
>> (
>>
>> "idBuilding" Integer NOT NULL,
>>
>> "start" Timestamp,
>>
>> "end" Timestamp,
>>
>> primary key ("idBuilding")
>>
>> ) Without Oids;
>>
>> Alter table "BuildingQueue" add foreign key ("idBuilding") references
>> "Building" ("idBuilding") on update restrict on delete restrict;
>>
>>
>
> How about you figure out when "it should be finished", and if now() is
> after "should be" then mark the building as completed with a finish time of
> "should be".
>
> Then you can run it whenever (every hour, on login, etc), and it'll catch
> up, and mark buildings as complete with the appropriate time.
>
> -Andy
>

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Tom Lane]

Jeff Amiel  writes:
> On a side note, I am 100% sure that autovacuum was disabled when I brought
> the database back up after the core dump(s).  However, minutes after
> restarting, some of my larger tables started getting vacuumed by pgsql user.
> Any way that a vacuum would kick off for a particular table (or series of
> tables) even when autovacuum was off in the postgresql.conf?

Anti-transaction-wraparound vacuums, perhaps?

regards, tom lane

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

Re: [GENERAL] Some insight on the proper SQL would be appreciated

[Andy Colson]

On 6/8/2010 11:29 AM, Aaron Burnett wrote:


Greetings,

I hope this is the proper list for this, but I am a loss on how to achieve
one particular set of results.

I have a table which is a list of users who entered a contest. They can
enter as many times as they want, but only 5 will count. So some users have
one entry, some have as many as 15.




is it possible to add a new column:  "isValid integer"

(or something like it, to signify which one's can and cannot be selected)

I was thinking of doing it in two steps, an update to set isValid, then 
a select with just "isValid = 1".  Not sure how hard the update would be 
though.


Oh, wait, I'll bet a window function would be helpful... but you are on 
8.2 so I dont think you have them.


I can think of a stored proc that might work, that ok?

Oh, another thought.. if we order it by username, signedup, and then did 
something like:


where ... signedup > (select the 5th signedup from users ... )

not 100% how to do the subselect though.  I can see min(signedup) or 
max(signedup), but not sure how to get the 5th.


Not real helpful, sorry, just some random thoughts

-Andy

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

Re: [GENERAL] Does npgsql have a bunch of bugs with DB enums?

[Francisco Figueiredo Jr.]

Thank you very much, Mike!

We'll have a look at that.


On Tue, Jun 8, 2010 at 00:03, Mike Christensen  wrote:
> Ok I did more investigation on this and traced the issue down to a
> singe npgsql bug.  Enums actually work fine, as long as you're using
> an IDataReader to get at the data.  Once you attempt to load the
> reader into a DataSet, it blows up.  I'll log this bug..
>
> Mike
>
> On Mon, Jun 7, 2010 at 4:43 AM, Mike Christensen  wrote:
>> This is probably not the right forum for this question, but maybe
>> someone can help me out or redirect me.
>>
>> I'm running into a lot of problems with npgsql and enum types.  There
>> seems to be very little support or testing in this area.  The issue
>> right now is I have a lot of SQL functions that have OUT parameters of
>> an enum type.  Such as:
>>
>> CREATE TYPE UnitTypeEnum AS ENUM ('Unit', 'Volume', 'Weight');
>>
>>
>> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
>> OUT UnitType UnitTypeEnum)
>>  RETURNS SETOF record AS
>> ...
>>
>> When I call that function using the npgsql driver, I get all sorts of
>> errors.  The data table simple has no ProviderType (it's blank), even
>> though the DataType is System.String.  If I try to read the data
>> reader, I get exceptions.  The solution I found is to cast the enum to
>> a text, such as:
>>
>> CREATE OR REPLACE FUNCTION readformsforingredient(IN _ingredient text,
>> OUT UnitType text)
>> ...
>> select UnitType::text from foo;
>>
>> However, this becomes a pain if I'm not using OUT parameters and just
>> returning a set of a row type.  Plus, having to cast ENUMs is kind of
>> a hack.
>>
>> Are the people working on npgsql aware of this problem, and can we
>> expect to get real enum support in future versions?  It seems they
>> should just magically cast to either a string or an int and I
>> shouldn't have to worry about that.  Thanks!!
>>
>> Mike
>>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://fxjr.blogspot.com
http://twitter.com/franciscojunior

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

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Jeff Amiel]

On 6/8/10 11:23 AM, "Tom Lane"  wrote:

> In your original report you mentioned that the next autovacuum attempt
> on the same table succeeded without incident.  Has that been true each
> time?  I wonder whether this is some transient state, rather than actual
> corruption that you need to REINDEX to recover from.
> 
I didn't waste time during this event and reindexed as quick as I could.

I will note, however, that these indexes (specifically the one that
generated the error) were in use (successfully) AFTER the event BEFORE the
reindex by the slony triggers (by virtue of inserts into the log tables and
such) even though I stopped autovacuum and slon daemon.  Not reads,
obviously...just inserts/updates.

If nobody else has seen/is seeing this, I will chalk this whole scenario up
to oddball SAN issues (we've logged many a write/read error over the year(s)
causing corruption on these heavily manipulated indexes.  I'll be glad to
move to my attached storage as quickly as possible.

On a side note, I am 100% sure that autovacuum was disabled when I brought
the database back up after the core dump(s).  However, minutes after
restarting, some of my larger tables started getting vacuumed by pgsql user.
Any way that a vacuum would kick off for a particular table (or series of
tables) even when autovacuum was off in the postgresql.conf?  My only manual
vacuum process is kicked off late at night, so this was not it.

Alsobefore I had a chance to disable the slon daemon I also noticed
other slony tables being vacuum analyzed (even though autovacuum was off)
Does Slony manage it's own vacuuming separate from postgres' autovacuum?







 



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

[GENERAL] Queues Problem

[uaca man]

Hello my fellow postgreSQL gurus. I´m a user of   postgresSQL of quite some
time now, but most of my experience is consuming database, and for the
current project we are without a proper DBA and they have to bear with me
and so I must seek advice.

I have a list of building and a queue and the user can start the
construction of one or more building that will take some time to build. The
problems lies with the fact this is a web browser game and the user can log
in, star the construction and log off, to further aggravate the problem
another user may take a look at the buildings, to add a little bit more,
when a building is done it may have effect on the user population, gold and
whatever the “imagination team” can come up with.

Bottom line is: the construction has to be “concluded” with second’s
precision.

Lets say for a 20 thousand users server, it may have at most 20 thousand
constructions started at the same time.

To accomplish such behavior so far I could come up with two options:

1.   Make a never ending function that will look at the   BuildingQueue
table every second and finish the construction.

2.   Every time the user start a construction add a cron job for that
construction to run 1 seconds after the construction is finished and call a
function the will finish.



For some reason I can not believe that a never ending function is a good
idea and I don’t think cron jobs are meant to have 20 thousand jobs.

Anyone care to share?

Tables:

Create table "Building"

(

"idBuilding" Serial NOT NULL,

"description" Varchar(200),

"time" Integer,

 primary key ("idBuilding")

) Without Oids;

Create table "BuildingQueue"

(

"idBuilding" Integer NOT NULL,

"start" Timestamp,

"end" Timestamp,

 primary key ("idBuilding")

) Without Oids;



Alter table "BuildingQueue" add  foreign key ("idBuilding") references
"Building" ("idBuilding") on update restrict on delete restrict;

Re: [GENERAL] Queues Problem

[Andy Colson]

On 6/8/2010 11:53 AM, uaca man wrote:

Hello my fellow postgreSQL gurus. I´m a user of postgresSQL of quite
some time now, but most of my experience is consuming database, and for
the current project we are without a proper DBA and they have to bear
with me and so I must seek advice.

I have a list of building and a queue and the user can start the
construction of one or more building that will take some time to build.
The problems lies with the fact this is a web browser game and the user
can log in, star the construction and log off, to further aggravate the
problem another user may take a look at the buildings, to add a little
bit more, when a building is done it may have effect on the user
population, gold and whatever the “imagination team” can come up with.

Bottom line is: the construction has to be “concluded” with second’s
precision.

Lets say for a 20 thousand users server, it may have at most 20 thousand
constructions started at the same time.

To accomplish such behavior so far I could come up with two options:

1. Make a never ending function that will look at the BuildingQueue
table every second and finish the construction.

2. Every time the user start a construction add a cron job for that
construction to run 1 seconds after the construction is finished and
call a function the will finish.

For some reason I can not believe that a never ending function is a good
idea and I don’t think cron jobs are meant to have 20 thousand jobs.

Anyone care to share?

Tables:

Create table "Building"

(

"idBuilding" Serial NOT NULL,

"description" Varchar(200),

"time" Integer,

primary key ("idBuilding")

) Without Oids;

Create table "BuildingQueue"

(

"idBuilding" Integer NOT NULL,

"start" Timestamp,

"end" Timestamp,

primary key ("idBuilding")

) Without Oids;

Alter table "BuildingQueue" add foreign key ("idBuilding") references
"Building" ("idBuilding") on update restrict on delete restrict;




How about you figure out when "it should be finished", and if now() is 
after "should be" then mark the building as completed with a finish time 
of "should be".


Then you can run it whenever (every hour, on login, etc), and it'll 
catch up, and mark buildings as complete with the appropriate time.


-Andy

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

Re: [GENERAL] Cognitive dissonance

[Justin Graf]

***SNIP***
> 2) Its also available in chm  windows help file format.  Which i find 
> allot
> more useful
> http://www.postgresql.org/docs/manuals/
> you could print chm to a text file.
>
> --I'll have to boot over to XP, ugh.  Will do.
There are linux chm readers

http://www.linux.com/news/software/applications/8209-chm-viewers-for-linux

and one for firefox
https://addons.mozilla.org/en-US/firefox/addon/3235/


All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Queues Problem

[Vick Khera]

On Tue, Jun 8, 2010 at 12:53 PM, uaca man  wrote:
> Lets say for a 20 thousand users server, it may have at most 20 thousand
> constructions started at the same time.
>
> To accomplish such behavior so far I could come up with two options:
>
> 1.   Make a never ending function that will look at the   BuildingQueue
> table every second and finish the construction.
>
> 2.   Every time the user start a construction add a cron job for that
> construction to run 1 seconds after the construction is finished and call a
> function the will finish.

You should investigate a proper queueing or job scheduling solution,
such as RabbitMQ or Qpid or gearman.  They are designed for this type
of requirement.  You will have to write your code to be more event
driven, and make the web server just generate requests and view the
results where they are stored.

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

Re: [GENERAL] Cognitive dissonance

[Chris Browne]

jus...@magwerks.com (Justin Graf) writes:
> Its also available in chm  windows help file format.  Which i find allot 
> more useful
> http://www.postgresql.org/docs/manuals/
> you could print chm to a text file.
>
> also it not hard to dump a PDF document into a text file.

I wish I could find a converter that would generate one of the common
eBook formats (epub, mobi).

There do exist CHM readers on mobile platforms such as Android, but
they're much clumsier to work with than the rather more heavily used
eBook readers.

I have poked around for conversions; nothing particularly suitable has
emerged :-(.
-- 
select 'cbbrowne' || '@' || 'cbbrowne.com';
http://cbbrowne.com/info/internet.html
"MS  apparently now  has a  team dedicated  to tracking  problems with
Linux  and publicizing them.   I guess  eventually they'll  figure out
this back fires... ;)" -- William Burrow 

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

[GENERAL] Queues Problem

[uaca man]

Hello my fellow postgreSQL gurus. I´m a user of   postgresSQL of quite some
time now, but most of my experience is consuming database, and for the
current project we are without a proper DBA and they have to bear with me
and so I must seek advice.

I have a list of building and a queue and the user can start the
construction of one or more building that will take some time to build. The
problems lies with the fact this is a web browser game and the user can log
in, star the construction and log off, to further aggravate the problem
another user may take a look at the buildings, to add a little bit more,
when a building is done it may have effect on the user population, gold and
whatever the “imagination team” can come up with.

Bottom line is: the construction has to be “concluded” with second’s
precision.

Lets say for a 20 thousand users server, it may have at most 20 thousand
constructions started at the same time.

To accomplish such behavior so far I could come up with two options:

1.   Make a never ending function that will look at the   BuildingQueue
table every second and finish the construction.

2.   Every time the user start a construction add a cron job for that
construction to run 1 seconds after the construction is finished and call a
function the will finish.



For some reason I can not believe that a never ending function is a good
idea and I don’t think cron jobs are meant to have 20 thousand jobs.

Anyone care to share?

Tables:

Create table "Building"

(

"idBuilding" Serial NOT NULL,

"description" Varchar(200),

"time" Integer,

 primary key ("idBuilding")

) Without Oids;

Create table "BuildingQueue"

(

"idBuilding" Integer NOT NULL,

"start" Timestamp,

"end" Timestamp,

 primary key ("idBuilding")

) Without Oids;



Alter table "BuildingQueue" add  foreign key ("idBuilding") references
"Building" ("idBuilding") on update restrict on delete restrict;

Re: [GENERAL] Cognitive dissonance

[John Gage]

Thank you all for your suggestions.  Thank you very much.

John


1) I suppose the next thing you'll be suggesting is that, because
Postgres is a database, the documentation should be stored as some
form of searchable table within the database itself?



--Well, that is exactly what I have done with the MeSH subject  
headings.  And it works like a charm.



2) Its also available in chm  windows help file format.  Which i find  
allot

more useful
http://www.postgresql.org/docs/manuals/
you could print chm to a text file.

--I'll have to boot over to XP, ugh.  Will do.


3)  also it not hard to dump a PDF document into a text file.

--I would print out what the dump looks like, but this is a family  
program



4) Would a \h+ that gave you the text from the web-page be useful..?   
That,

plus the various man pages, would cover an awful lot of what's in SGML..

From :
"The DocBook SGML source for the manuals is available as part of the  
PostgreSQL source download available in the FTP area."



-I'm headed there.  It's just that given the incredibly good  
documentation and the fact that it's available in just about every  
format except a text file, I was sort of hoping for a policy change on  
the part of the powers that be.






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

[GENERAL] Some insight on the proper SQL would be appreciated

[Aaron Burnett]

Greetings,

I hope this is the proper list for this, but I am a loss on how to achieve
one particular set of results.

I have a table which is a list of users who entered a contest. They can
enter as many times as they want, but only 5 will count. So some users have
one entry, some have as many as 15.

How could I distill this down further to give me a list that shows each
entry per user up to five entries per user? In other words, I need a
separate line item for each entry from each user up to the maximum of 5 rows
per user.

Table looks like this:
  username   | firstname |  lastname   |  signedup
--+---+-+---
-
 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-03-13
 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-05-07
 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-06-06
 ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch   | 2010-03-12
 ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25
 ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09
 ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay   | Maher   | 2010-04-20
 fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn   | Woodul  | 2010-04-05
 fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03
 feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03
 feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05
 fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer  | Lavigne | 2010-02-09
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-20
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-27
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-03
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-10
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-17
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-25
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-01
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-08
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-16
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-22
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-05-30
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-06-06
 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-12
 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-15

But in John Smith's case where he has more than 5 entries, I would like
query results to limit him to just 5 entries to look like this:

  username   | firstname |  lastname   |  signedup
--+---+-+---
-
 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-03-13
 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-05-07
 ffc4bd0a-ccff-4203-a03c-4bd6b8c23a03 | Jean  | Mathews | 2010-06-06
 ff8720d4-f808-4ee6-90e6-649872fdfa55 | Janis | Bunch   | 2010-03-12
 ff48d89b-6afe-4ba0-8539-9683bec62c89 | Sandy | Yashnyk | 2010-04-25
 ff48d2a4-0637-4e46-9461-3dd0dc0d4d83 | Kendra| Elliott | 2010-05-09
 ff3a2a14-2575-44d5-b40b-3780f4d2506a | Kay   | Maher   | 2010-04-20
 fef7625b-d1e1-4c1a-bc82-d35a4a6db16d | Carolyn   | Woodul  | 2010-04-05
 fef21bbb-07a0-4c84-8708-4dc41b8b770b | Laurie| Montijo | 2010-04-03
 feee6473-af4d-4e70-b20c-a74ba08c000f | Geneva| Anderson| 2010-04-03
 feb690fc-0afb-4e87-b0d1-bdb2c4603fd1 | Judith| Astroff | 2010-06-05
 fea0f9a6-e89f-4dbd-b3fd-83efed27221f | Jennifer  | Lavigne | 2010-02-09
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-20
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-03-27
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-03
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-10
 fea0334e-4ae1-4599-b24d-9a5d8ce4fd37 | John  | Smith   | 2010-04-17
 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-12
 fe825a6d-6820-4282-b8e9-2e3cb9f660e8 | Susan | Stowe   | 2010-03-15

The username is unique for each user.

pg version 8.25 on RHEL

Any help in this would be greatly appreciated.

Thank you.


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

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Tom Lane]

Jeff Amiel  writes:
> New one yesterday.

> Jun  7 15:05:01 db-1 postgres[9334]: [ID 748848 local0.crit] [3989781-1] 
> 2010-06-07 15:05:01.087 CDT9334PANIC:  right sibling 169 of block 168 is 
> not next child of 249 in index "sl_seqlog_idx"

In your original report you mentioned that the next autovacuum attempt
on the same table succeeded without incident.  Has that been true each
time?  I wonder whether this is some transient state, rather than actual
corruption that you need to REINDEX to recover from.

regards, tom lane

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

Re: [GENERAL] BUG #5492: Sequence corruption

[p . buongiovanni]

Hi Greg,

first of all thank you for your answer below.

The first problem of the query performing slowly has been solved with a 
FULL VACUUMING of the database.
Now the problem of the sequence remains.

This e-mail is sent to pgsql-general@postgresql.org e-mail address too, as 
per your suggestion.

WIth reference to the "bad" sequence, if I use pgAdmin III and go to 
subject_isid_seq object of the business schema I see the following:

-- Sequence: business.subject_isid_seq

-- DROP SEQUENCE business.subject_isid_seq;

CREATE SEQUENCE business.subject_isid_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 47948992
  CACHE 10;
ALTER TABLE business.subject_isid_seq OWNER TO netdw_owner;

My problem is that the START value is higher than the MAX value (100) used 
in the table. I tried to change the START value with SETVAL function many 
times without success.
With the above situation if I now try to change the value esecuting the 
following code:

SELECT SETVAL('business.subject_isid_seq', MAX(iSId)) FROM 
business.Subject;

I obtain the return value 100, as expected.
If I now use pgAdmin III to look at the sequence data I see the following 
situation:

CREATE SEQUENCE business.subject_isid_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 9223372036854775807
  START 1810
  CACHE 10;
ALTER TABLE business.subject_isid_seq OWNER TO netdw_owner;

Every time I try to change the value to 100 I see the value is different. 
When I execute the SETVAL function I am connected as netdw_owner user, 
that's the business schema owner. So I believe it's not a problem of 
object ownership.
I have other sequences in my database but only this one has this problem.

Could anybody help me in finding a solution?
Thank you very much in advance.

Piergiorgio Buongiovanni







Greg Stark  
07/06/2010 23.33

Per
Piergiorgio Buongiovanni 
CC
pgsql-b...@postgresql.org
Oggetto
Re: BUG #5492: Query performs slowly and sequence corrupted






On Mon, Jun 7, 2010 at 5:36 PM, Piergiorgio Buongiovanni
 wrote:
> I reused the previous command to re-set the sequence value to the right 
one,
> but I see that the START value is now 59100. I reused the previous 
command
> another time and the START value is now 30440.
>
> I think this is a bug. I have a lot of problems with this sequence.

Sequences wouldn't directly affect retrieval times. But one way you
could get both of these symptoms is by having an application which
inserts many rows but aborts and rolls back the inserts without
committing. Perhaps a large copy which is interrupted. That would fill
the table with garbage dead records which could slow down retrieval
depending on the access method and also increase the sequence value.

You'll need to provide a lot more data before people would be able to
help you. I suggest you post the results of "explain analyze select
..." as well as some details of what previous operations you've done
on this database to pgsql-gene...@postgresql.org. pgsql-bugs is not
really appropriate unless you're reporting a specific problem with
postgresql in general, not an operational problem with your database.


-- 
greg




   
-- Disclaimer --
 
This message contains information which may be confidential. Unless you are the 
addressee (or authorized to receive for the addressee), 
you may not use, copy or disclose to anyone the message or any information 
contained in the message. If you have received the message
in error, please contact the sender by e-mail and delete the message. Many  
thanks.
 
Il presente messaggio contiene informazioni di carattere riservato. Qualora non 
foste il destinatario (o autorizzato dallo stesso al ricevimento)
non usate, copiate o rivelate il presente messaggio o le informazioni 
contenute. Se avete ricevuto il messaggio per errore, Vi preghiamo di
cancellarlo e avvisare il mittente tramite e-mail. Grazie.

Re: [GENERAL] Restore roles with same oid

[Jeff Amiel]

On 6/8/10 10:47 AM, "Stephen Frost"  wrote:
 
> Then you've been through this before..  Perhaps you should go check out
> what you did then.  Back before 8.1, we didn't use OIDs for
> users/groups. :)  Changing to OIDs was part of the work that I did to
> add role support.


Hmmm...this code has been in place since March 2005. using the usesysid
from pg_shadow.  8.1 wsn't released until november of 2005.

Doesn't matterI still have some work to do.







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

Re: [GENERAL] Restore roles with same oid

[Stephen Frost]

* Jeff Amiel (jam...@istreamimaging.com) wrote:
> On 6/8/10 10:39 AM, "Stephen Frost"  wrote:
> > I'm afriad you're not going to have a choice..  I would recommend
> > creating a mapping from the old IDs to the new ones as part of this
> > upgrade, to keep the historical information.  Guess it's not nice to
> > point this out- but you really shouldn't have ever used OIDs for
> > something external to PG (or even internally, really). :)
> 
> To be honest...this was done early in our postgresql careers (back in the
> 7.X days).  We knew so little.   :)

Then you've been through this before..  Perhaps you should go check out
what you did then.  Back before 8.1, we didn't use OIDs for
users/groups. :)  Changing to OIDs was part of the work that I did to
add role support.

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Restore roles with same oid

[Jeff Amiel]

On 6/8/10 10:39 AM, "Stephen Frost"  wrote:

> I'm afriad you're not going to have a choice..  I would recommend
> creating a mapping from the old IDs to the new ones as part of this
> upgrade, to keep the historical information.  Guess it's not nice to
> point this out- but you really shouldn't have ever used OIDs for
> something external to PG (or even internally, really). :)

To be honest...this was done early in our postgresql careers (back in the
7.X days).  We knew so little.   :)

 


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

Re: [GENERAL] Restore roles with same oid

[Stephen Frost]

* Jeff Amiel (jam...@istreamimaging.com) wrote:
> On 6/8/10 10:30 AM, "Thom Brown"  wrote:
> > Can't you switch to using role names?  I don't think oids are intended
> > to be used by anything other than PostgreSQL.
> 
> :( If only I couldmassive audit tables contain these IDs with years of
> data 
> 
> We have a plan to change to sequence values stored in one of our tables (one
> for each of our postgresql users) but don't want to pull the trigger on that
> plan as part of this upgrade if we can help it.

I'm afriad you're not going to have a choice..  I would recommend
creating a mapping from the old IDs to the new ones as part of this
upgrade, to keep the historical information.  Guess it's not nice to
point this out- but you really shouldn't have ever used OIDs for
something external to PG (or even internally, really). :)

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Restore roles with same oid

[Jeff Amiel]

On 6/8/10 10:30 AM, "Thom Brown"  wrote:

> Can't you switch to using role names?  I don't think oids are intended
> to be used by anything other than PostgreSQL.

:( If only I couldmassive audit tables contain these IDs with years of
data 

We have a plan to change to sequence values stored in one of our tables (one
for each of our postgresql users) but don't want to pull the trigger on that
plan as part of this upgrade if we can help it.



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

Re: [GENERAL] Restore roles with same oid

[Thom Brown]

On 8 June 2010 15:59, Jeff Amiel  wrote:
> We currently use the 'usesysid' column from pg_shadow (which is really
> pg_authid.oid I assume) for a trigger-based auditing mechanism.
>
> We are about to do a dump from an 8.2 database into 8.4 and would like to
> preserve the usesysid/oid when restoring.
>
> No matter what options I throw ad pg_dumpall, it gives me 'create role'
> commands...and obviously no preservation of the oids.
>
> Any suggestions on how I can manage this?
>

Can't you switch to using role names?  I don't think oids are intended
to be used by anything other than PostgreSQL.

Thom

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

[GENERAL] Restore roles with same oid

[Jeff Amiel]

We currently use the 'usesysid' column from pg_shadow (which is really
pg_authid.oid I assume) for a trigger-based auditing mechanism.

We are about to do a dump from an 8.2 database into 8.4 and would like to
preserve the usesysid/oid when restoring.

No matter what options I throw ad pg_dumpall, it gives me 'create role'
commands...and obviously no preservation of the oids.

Any suggestions on how I can manage this?


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

Re: [GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Vick Khera]

On Tue, Jun 8, 2010 at 9:26 AM, Jeff Amiel  wrote:
> That being said, the fact that each time this has happened, it has been a 
> slony index that has been corrupt, I find it 'odd'.  While I can't imagine a 
> bug in slony corrupting postgres indexes...and I can't imagine a bug in 
> postgres corrupting only slony indexes, I don't really know what to think.  
> Just putting this out there in case anyone has similar issues or can use this 
> data in some meaningful way.
>

Slony is just a client to postgres, nothing more, nothing less.  Any
index corruption is going to be the fault of Postgres server or
hardware.

We don't know what version of postgres you're running, so nobody can
say anything for sure.

I will say that we observed an index corruption (on a zero-row
"status" table, so it grows and changes a lot during the course of a
day) about a week ago.  It was not a slony table.  A quick reindex on
that table and we were off and running.  We noticed it because slony
was trying to insert into the table and getting a read error.  This
has happened to us maybe one other time in the 10+ years this
application has been running.

I'm on Pg 8.3.7.

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

Re: [GENERAL] Cognitive dissonance

[Stephen Frost]

* John Gage (jsmg...@numericable.fr) wrote:
> But either I am a visitor from the Crab Nebula, or there is someone else 
> out there who would like to have a text file of the entire  
> documentation.

Soo..  there are quite a few man pages, and in-psql's help is also
pretty nice (\h  and \?).  That's certainly what I typically
use.  I admit that we don't include the full command description in the
\h (just the syntax), but that's still extremely useful.

Would a \h+ that gave you the text from the web-page be useful..?  That,
plus the various man pages, would cover an awful lot of what's in SGML..

Thanks,

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Cognitive dissonance

[Justin Graf]

On 6/8/2010 9:23 AM, Peter Hunsberger wrote:
> On Tue, Jun 8, 2010 at 4:04 AM, John Gage  wrote:
>
>> Unix is a text-based operating system with unbelievably helpful text
>> manipulation tools.
>>
>> Postgres is a creature of Unix which happens to have unbelievable text
>> searching and manipulation tools.
>>
>> Yet, the only one file edition of the Postgres documentation is in...pdf
>> format.  Huh?
>>
>>  
> I suppose the next thing you'll be suggesting is that, because
> Postgres is a database, the documentation should be stored as some
> form of searchable table within the database itself?
>
> 
>
>

Its also available in chm  windows help file format.  Which i find allot 
more useful
http://www.postgresql.org/docs/manuals/
you could print chm to a text file.

also it not hard to dump a PDF document into a text file.




All legitimate Magwerks Corporation quotations are sent in a .PDF file 
attachment with a unique ID number generated by our proprietary quotation 
system. Quotations received via any other form of communication will not be 
honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally 
privileged, confidential or other information proprietary to Magwerks 
Corporation and is intended solely for the use of the individual to whom it 
addresses. If the reader of this e-mail is not the intended recipient or 
authorized agent, the reader is hereby notified that any unauthorized viewing, 
dissemination, distribution or copying of this e-mail is strictly prohibited. 
If you have received this e-mail in error, please notify the sender by replying 
to this message and destroy all occurrences of this e-mail immediately.
Thank you.
<>
-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cognitive dissonance

[Peter Hunsberger]

On Tue, Jun 8, 2010 at 4:04 AM, John Gage  wrote:
> Unix is a text-based operating system with unbelievably helpful text
> manipulation tools.
>
> Postgres is a creature of Unix which happens to have unbelievable text
> searching and manipulation tools.
>
> Yet, the only one file edition of the Postgres documentation is in...pdf
> format.  Huh?
>

I suppose the next thing you'll be suggesting is that, because
Postgres is a database, the documentation should be stored as some
form of searchable table within the database itself?



-- 
Peter Hunsberger

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

Re: [GENERAL] Cognitive dissonance

[Lew]

John Gage wrote:

I also use the National Library of Medicine's MeSH subject headings.
25,000 descriptors with definitions, synonyms and a lot of other things.
They give it to you in single files either as text, xml, or other ways.
Big files. Hundreds of megabytes. That makes it so that you can do just
about anything with it you want. It is one of the seven wonders of the
world.

I do suggest that a plain text file of the entire documentation be made
part of the documentation armamentarium.


From :
"The DocBook SGML source for the manuals is available as part of the 
PostgreSQL source download available in the FTP area."


--
Lew

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

[GENERAL] >>relation with OID 1211822032 does not exist

[Dhaval Jaiswal]

Hi
All, 

We are getting following error on intermittent basis when we write data into 
database. We are on postgreSQL
8.0.2, with slony I. We used to stop the replication, taking back-up from the
primary, restore it on secondary server and again start the replication again. 
We
used to do above activity once in a  month. After re-starting the
replication last time we are getting this error. 

  
We have checked at database side but didn't find much to solve this error. Can 
somebody point us where we can look for. We have found following from the logs. 


ERROR:  P0001: usr(): Password provided not equal to stored password.
ERROR:  P0001: usr(): Password provided not equal to stored password.
ERROR:  22004: cannot EXECUTE a null querystring
ERROR:  42P01: relation "sl_confirm" does not exist
ERROR:  42P01: relation "sl_confirm" does not exist

  

ERROR: relation with  OID   1211822032 does not exist  

--
Thanks 
Bablu



  

[GENERAL] 3rd time is a charm.....right sibling is not next child crash.

[Jeff Amiel]

Not looking for help...just putting some data out there.

2 previous crashes caused by corrupt slony indexes

http://archives.postgresql.org/pgsql-general/2010-02/msg00022.php

http://archives.postgresql.org/pgsql-general/2009-12/msg01172.php

New one yesterday.

Jun  7 15:05:01 db-1 postgres[9334]: [ID 748848 local0.crit] [3989781-1] 
2010-06-07 15:05:01.087 CDT9334PANIC:  right sibling 169 of block 168 is 
not next child of 249 in index "sl_seqlog_idx"

We are on the eve of switching off our SAN to some direct attached storage and 
upgrading postgres and slony in the process this weekendso any thoughts 
that it might be hardware, driver or even postgres/slony should be alleviated 
by the fact that everything is changing.

That being said, the fact that each time this has happened, it has been a slony 
index that has been corrupt, I find it 'odd'.  While I can't imagine a bug in 
slony corrupting postgres indexes...and I can't imagine a bug in postgres 
corrupting only slony indexes, I don't really know what to think.  Just putting 
this out there in case anyone has similar issues or can use this data in some 
meaningful way. 

Stack trace looks similar to last time.

Program terminated with signal 6, Aborted.
#0  0xfecba227 in _lwp_kill () from /lib/libc.so.1
(gdb) bt
#0  0xfecba227 in _lwp_kill () from /lib/libc.so.1
#1  0xfecb598f in thr_kill () from /lib/libc.so.1
#2  0xfec61ed3 in raise () from /lib/libc.so.1
#3  0xfec41d0d in abort () from /lib/libc.so.1
#4  0x0821b8a6 in errfinish (dummy=0) at elog.c:471
#5  0x0821c74b in elog_finish (elevel=22, fmt=0x82b7780 "right sibling %u of 
block %u is not next child of %u in index \"%s\"") at elog.c:964
#6  0x0809e1a0 in _bt_pagedel (rel=0x867bcd8, buf=139905, stack=0x86b3768, 
vacuum_full=0 '\0') at nbtpage.c:1141
#7  0x0809f835 in btvacuumscan (info=0x8043f70, stats=0x86b5c30, callback=0, 
callback_state=0x0, cycleid=29488) at nbtree.c:936
#8  0x0809fc65 in btbulkdelete (fcinfo=0x0) at nbtree.c:547
#9  0x0821f424 in FunctionCall4 (flinfo=0x0, arg1=0, arg2=0, arg3=0, arg4=0) at 
fmgr.c:1215
#10 0x0809a89f in index_bulk_delete (info=0x8043f70, stats=0x0, 
callback=0x812ffc8 , callback_state=0x86b5818) at indexam.c:573
#11 0x0812ff54 in lazy_vacuum_index (indrel=0x867bcd8, stats=0x86b5b70, 
vacrelstats=0x86b5818) at vacuumlazy.c:660
#12 0x0813055a in lazy_vacuum_rel (onerel=0x867b7f8, vacstmt=0x86659b8) at 
vacuumlazy.c:487
#13 0x0812e910 in vacuum_rel (relid=140925368, vacstmt=0x86659b8, 
expected_relkind=114 'r') at vacuum.c:1107
#14 0x0812f95a in vacuum (vacstmt=0x86659b8, relids=0x8665bc0) at vacuum.c:400
#15 0x08186e16 in AutoVacMain (argc=0, argv=0x0) at autovacuum.c:914
#16 0x08187278 in autovac_start () at autovacuum.c:178
#17 0x0818bfed in ServerLoop () at postmaster.c:1252
#18 0x0818d16d in PostmasterMain (argc=3, argv=0x833adc8) at postmaster.c:966
#19 0x08152cce in main (argc=3, argv=0x833adc8) at main.c:188
(gdb) 







  


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

Re: [GENERAL] >>relation with OID 1211822032 does not exist

[Craig Ringer]

On 8/06/2010 8:25 PM, Jaiswal Dhaval Sudhirkumar wrote:

Hi All,

We are getting following error on intermittent basis when we write data
into database. We are on postgreSQL 8.0.2


While this doesn't help you track down your problem directly ... 8.0.2? 
Really? Version 8.0 is up to patch level 25 (!!) so you're missing out 
on a LOT of bug fixes. 8.0 needed plenty of them, too :S


Anyway, first I strongly recommend that you try to pg_dumpall and make 
sure you keep that dump somewhere other than the problem machine.


Next, I'd look at whether that report could be coming from a stored 
procedure that hadn't been re-loaded after a table/view/proc that it 
uses changed. Before 8.3, stored procedures had to be reloaded after 
such changes (and didn't work quite right with temp tables, either).


Then I'd turn up the log level and detail, and see if I could find out 
what statement was triggering the error report.


--
Craig Ringer

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

Re: [GENERAL] Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

[John T. Dow]

On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote:

>On 8/06/2010 9:11 AM, John T. Dow wrote:
>> OP here
>>
>> We removed AVG from the computer and rebooted.
>>
>> Same problem.
>
>OK, good to know. Thanks very much for testing that, and my apologies 
>for recommending something that didn't work out. Of course, it would 
>have been hard to progress without eliminating that possible factor.
>
>> Could it be 2000 Server? SP4? I've seen reports of other problems that went 
>> away depending on the version of Windows.
>
>Well, certainly I'd expect that Pg on Windows 2000 server gets about 
>zero regular testing. Why would you deploy a server OS that's already 10 
>years out of date, went EOL five years ago, and lost even the option of 
>paid extended support this year?
>
>http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274
>





Good point.

It's not my server, it's my client's server, and I don't know the history of it.

They have mentioned another computer which runs XP I believe. It's dedicated to 
a single task and could double as the database server, although I don't think 
it has any RAID. I will suggest that we try installing Postgres on that 
computer and see if the problem goes away. If so, they might choose to make 
that their solution (perhaps adding another hard drive and a RAID controller). 
The application, daily backups, and WAL files could all live on the original 
server.

If they go that route, we'd never know for certain what the original problem 
was.

I'll post back after anything is done.

JOhn









>It'd be interesting to investigate this issue ... but win2k server isn't 
>exactly easy to come by. Anyone on the list got a win2k server (or 
>license) around they can do some experimenting on? All I have here is 
>NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop 
>suspects.
>
>--
>Craig Ringer
>
>
>-- 
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general



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

[GENERAL] >>relation with OID 1211822032 does not exist

[Jaiswal Dhaval Sudhirkumar]

Hi All, 

We are getting following error on intermittent basis when we write data
into database. We are on postgreSQL 8.0.2, with slony I. We used to stop
the replication, taking back-up from the primary, restore it on
secondary server and again start the replication again. We used to do
above activity once in a  month. After re-starting the replication last
time we are getting this error. 

  
We have checked at database side but didn't find much to solve this
error. Can somebody point us where we can look for. We have found
following from the logs. 

ERROR:  P0001: usr(): Password provided not equal to stored password.
ERROR:  P0001: usr(): Password provided not equal to stored password.
ERROR:  22004: cannot EXECUTE a null querystring
ERROR:  42P01: relation "sl_confirm" does not exist
ERROR:  42P01: relation "sl_confirm" does not exist

  

ERROR: relation with  OID   1211822032 does not exist 

 

 

--

Thanks 

Dj

 

The information transmitted is intended only for the person or entity to which 
it is addressed and may contain confidential and/or privileged material. 
Any review, re-transmission, dissemination or other use of or taking of any 
action in reliance upon,this information by persons or entities other than the 
intended recipient is prohibited. 
If you received this in error, please contact the sender and delete the 
material from your computer. 
Microland takes all reasonable steps to ensure that its electronic 
communications are free from viruses. 
However, given Internet accessibility, the Company cannot accept liability for 
any virus introduced by this e-mail or any attachment and you are advised to 
use up-to-date virus checking software. 

Re: [GENERAL] Performance drop after upgrading to 8.4.4?

[Merlin Moncure]

On Thu, Jun 3, 2010 at 9:22 AM, Max Williams  wrote:
> Hi,
>
> I was doing some benchmarking while changing configuration options to try to
> get more performance out of our postgresql servers and noticed that when
> running pgbench against 8.4.3 vs 8.4.4 on identical hardware and
> configuration there is a large difference in performance. I know tuning is a
> very deep topic and benchmarking is hardly an accurate indication of real
> world performance but I was still surprised by these results and wanted to
> know what I am doing wrong.
>
>
>
> Hardware specs are:
>
> 2x Quad core Xeons 2.4Ghz
>
> 16GB RAM
>
> 2x RAID1 7.2k RPM disks
>
>
>
> Relevant Postgresql Configuration:
>
> max_connections = 1000
>
> shared_buffers = 4096MB
>
> temp_buffers = 8MB
>
> max_prepared_transactions = 1000
>
> work_mem = 8MB
>
> maintenance_work_mem = 512MB
>
> wal_buffers = 8MB
>
> checkpoint_segments = 192
>
> checkpoint_timeout = 30min
>
> effective_cache_size = 12288MB
>
>
>
> Results for the 8.4.3 (8.4.3-2PGDG.el5) host:
>
> [r...@some-host ~]# pgbench -h dbs3 -U postgres -i -s 100 pgbench1 >
> /dev/null 2>&1 && pgbench -h dbs3 -U postgres -c 100 -t 10 pgbench1
>
> starting vacuum...end.
>
> transaction type: TPC-B (sort of)
>
> scaling factor: 100
>
> query mode: simple
>
> number of clients: 100
>
> number of transactions per client: 10
>
> number of transactions actually processed: 1000/1000
>
> tps = 4612.734318 (including connections establishing)
>
> tps = 4613.308264 (excluding connections establishing)
>
>
>
> Results for the 8.4.4 (8.4.4-1PGDG.el5) host:
>
> [root@ some-host ~]# pgbench -h dbs4 -U postgres -i -s 100 pgbench1 >
> /dev/null 2>&1 && pgbench -h dbs4 -U postgres -c 100 -t 10 pgbench1
>
> starting vacuum...end.
>
> transaction type: TPC-B (sort of)
>
> scaling factor: 100
>
> query mode: simple
>
> number of clients: 100
>
> number of transactions per client: 10
>
> number of transactions actually processed: 1000/1000
>
> tps = 2799.134267 (including connections establishing)
>
> tps = 2799.451407 (excluding connections establishing)
>
>
>
> Any input? I can reproduce these numbers consistently. By the way, I am a
> new postgresql user so my experience is limited.

nothing jumps out to me in terms of the release notes.   any chance of
getting some oprofile runs?

merlin

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

[GENERAL] Cognitive dissonance

[John Gage]

Unix is a text-based operating system with unbelievably helpful text  
manipulation tools.


Postgres is a creature of Unix which happens to have unbelievable text  
searching and manipulation tools.


Yet, the only one file edition of the Postgres documentation is  
in...pdf format.  Huh?


I know.  I know.  I have already brought this up.  And various ways of  
creating a one file text edition of the documentation have been  
proposed to me.  I know.


But either I am a visitor from the Crab Nebula, or there is someone  
else out there who would like to have a text file of the entire  
documentation.


Two examples from other applications.

I use Vim.  Vim's documentation is as easy to access as any  
documentation on earth...as long as you know exactly what you are  
looking for.  Otherwise, it is a tremendous pain.


I also use the National Library of Medicine's MeSH subject headings.   
25,000 descriptors with definitions, synonyms and a lot of other  
things.  They give it to you in single files either as text, xml, or  
other ways.  Big files.  Hundreds of megabytes.  That makes it so that  
you can do just about anything with it you want.  It is one of the  
seven wonders of the world.


I do suggest that a plain text file of the entire documentation be  
made part of the documentation armamentarium.


Respectfully,

John Gage



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

Re: [GENERAL] Deferrable constraint checking with SAVEPOINT?

[Dean Rasheed]

On 8 June 2010 03:02, Mike Toews  wrote:
> Hi,
>
> I have a question that is not specified in the docs[1]. I am using
> deferrable constraints in a transaction with SET CONSTRAINTS ALL
> DEFERRED. Now I know that DEFERRED constraints are not checked until
> transaction COMMIT (i.e., the end), however are they checked with
> SAVEPOINT (i.e., part-way in)?
>

No, SAVEPOINTs will not force a check. A SAVEPOINT is nothing like a
COMMIT (it's more like a nested BEGIN). The only things that will
cause deferred constraints to be checked are a COMMIT or a SET
CONSTRAINTS .. IMMEDIATE.

Regards,
Dean

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