Re: [GENERAL] Unused item pointers - can someone explain?

[Peter Childs]

On 26/11/2007, Elmer <[EMAIL PROTECTED]> wrote:
>
> On 23 Lis, 10:23, [EMAIL PROTECTED] ("Peter Childs") wrote:
> > Yes, however
> >
> > found x removable
> >
> > have just been found and are now unused, so on the next run this number
> will
> > be added to the unused unless they get used again in the mean time.
> >
> > The number ie the unused is the number of tuples left in the free space
> map
> > unused since the last vacuum. If its high it may be worth clustering or
> > running vacuum full but only if you don't think you table will never or
> > unlikely to grow (insert) or change (update) by less than that number of
> > records before you next run vacuum.
> >
> > Generally only worry if the number is very very high (over 1).
> >
> > The best way of understanding the numbers is to run vacuum at regular
> > intervals and compare the output.
> >
> > Peter.
>
> Thank you for your explanation - it's not easy to get help in this
> subject... But how it is possible that new unused.
>
> You wrote:
>
> > The number ie the unused is the number of tuples left in the free space
> map unused since the last vacuum.
>
> This is important information for me but I still can't understand why
> this number keeps growing.Correct me if I wrong but if there is 17000
> unused tuples in free space map, they should be used in first place
> for creating new tuples versions. This should cause that next 17000
> operations (consists of INSERT,UPDATE,DELETE) would use fsm for row
> version creation instead of creating entirely new tuples at the end of
> table file. If I understand it correct number of unused item pointers
> should shrink between vacuums (but it still grows)...
>
>
Hmm that should have been number of tuples left in the free space map at the
start of the vacuum. So if you run a second vacuum in quick succession the
number under removable will have been added the the unused value to become
the new unused value.

If you have a growing table unused should be 0 (or shrinking)

If you have a busy table thats not vacuumed often enough unused should be 0
but removable will be high (so long as you vacuum at regular intervals)

If you have a shrinking table unused will grow.

If you have a steady table that was larger at some point the past eg an
update to the whole table. unused will be large

If unused is the amount of "bloat" you have but is only useful depending on
when you last vacuumed if you vacuumed twice within a couple of seconds the
second number is not very useful.

If I'm wrong I'm sure someone will correct me.

Peter Childs

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Magnus Hagander]

On Tue, Nov 27, 2007 at 05:01:06PM -0700, Scott Ribe wrote:
> > In general, you can expect any Unix based OS, which includes MacOS X, to
> > perform noticeably better than Windows for PostgreSQL.
> 
> Is that really true of BSD UNIXen??? I've certainly heard it's true of
> Linux. But with BSD you have the "kernel funnel" which can severely limit
> multitasking, regardless of whether threads or processes were used.

Yes, very much so. Windows lacks the fork() concept, which is what makes
PostgreSQL much slower there.

//Magnus

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] System Load analyze

[Peter Bauer]

Am Dienstag 27 November 2007 schrieb Scott Marlowe:
> On Nov 24, 2007 10:57 AM, Peter Bauer <[EMAIL PROTECTED]> wrote:
> > i have a system here with 2 2.4GHz Xeon Processors, 2GB RAM, ONE Disk on
> > a Battery Backed Write Cache SCSI Controller and PostgreSQL 8.1.4
> > running with the data on a DRBD Device for High Availability. The used
> > database is also replicated to two similar machines with slony1.
>
> Why are you running a version of PostgreSQL with known data eating
> bugs? If you care for your data, you will keep up to date on releases.
>  8.1.10 was released on 2007-09-17.  8.1.4 was released on 2006-05-23.
>  That's 16 months of bug fixes you're missing.  Go here:
> http://www.postgresql.org/docs/8.1/static/release.html and read up on
> the fixes you're missing.  Then update.  Or just update.
>
> OK, on the the issue at hand.
>
> > Since the load average is between 1 (most of the time) and 10 (peeks) i
> > am worried about the load and executed vmstat and iostat which show that
> > 1000-6000 Blocks are writen per second. Please check the attached output
> > for further details.
> > top shows that the CPUs are at least 80% idle most of the time so i
> > think there is an I/O bottleneck. I'm aware that this hardware setup is
> > probably not sufficient but is would like to investigate how critical
> > the situation is.
>
> Yes.  Battery backed cache can only do so much, it's not magic pixie
> dust.  Once it's full, the drive becomes the bottle neck.  Real db
> servers have more than one disk drive.  They usually have at least 4
> or so, and often dozens to hundreds. Also, not all battery backed
> caching RAID controllers are created equal.
>
> > procs ---memory-- ---swap-- -io --system--
> > cpu r  b   swpd   free   buff  cache   si   sobibo   in  
> >  cs us sy id wa 0  0 398256  78328 140612 106355600 0  1472
> > 2029  5081  4  3 92  0 0  2 398256  78268 140612 106357600 0 
> > 2304 1928  4216  0  2 98  0 1  2 398256  78100 140612 106357600  
> >   0  1464 1716  3994  1  3 96  0 0  0 398256  78704 140612 10635920  
> >  0 0   916 1435  3570  5  3 91  0 0  0 398256  77876 140612 1063616  
> >  00 0 0  305  1169  3  1 96  0
>
> See that dip in the last line above where the blocks in drop to 0,
> idle jumps to 96, and blocks out drops, and context switches drop?
> That's most likely where postgresql is checkpointing.  Checkpointing
> is where it writes out all the dirty buffers to disk.  If the bgwriter
> is not tuned aggresively enough, checkpoints happen and make the whole
> database slow down for a few seconds.  If it's tuned too aggresively
> then the db spends too much CPU time tracking the dirty buffers and
> then writing them.  If tuned just right, it will write out the dirty
> buffers just fast enough that a checkpoint is never needed.
>
> You tune the bgwriter to your machine and I/O subsystem.  If you're
> planning on getting more hard drives, do that first.  Then tune the
> bgwriter.
>
> btw, if this is "vmstat 1" running, it's showing a checkpoint every 20
> or so seconds I think
>
> >  0  2 398256  79136 140612 106396400 0  1736 1959  4494  4  2
> > 94  0
>
> checkpoint here:
> >  0  0 398256  79132 140612 106396400 0 4  260  1039  1  1
> > 98  0 0  0 398256  79052 140612 106398000 0  2444 3084  6955 
> > 6  5 89  0 0  2 398256  79060 140612 106398800 0   948 1146 
> > 3616  3  1 96  0 0  1 398256  78268 140612 106405600 0  1908
> > 1809  4086  6  5 88  0 0  1 398256  76728 140612 106405600 0 
> > 6256 6637 15472  5  5 90  0 0  2 398256  77000 140612 106406400  
> >   0  4916 5840 12107  1  4 95  0 0  2 398256  76956 140612 10640680  
> >  0 0  6468 7432 15211  1  3 96  0 0  6 398256  77388 140612 1064072  
> >  00 0  8116 7826 18265  1  8 91  0 0  2 398256  74312 140612
> > 106407600 0  7032 6886 16136  2  7 91  0 0  2 398256  74264
> > 140612 106407600 0  5680 7143 13411  0  5 95  0 0  2 398256 
> > 72980 140612 106414000 0  5396 6377 13251  6  6 88  0 0  3
> > 398256  76972 140612 106414800 0  5652 6793 14079  4  9 87  0
> > 0  2 398256  77836 140612 106414800 0  3968 5321 14187 10  8
> > 82  0 1  0 398256  77280 140612 106414800 0  1608 3188  8974
> > 21 12 67  0 1  0 398256  77832 140612 106415200 0   236  834 
> > 2625  7  5 87  0 0  0 398256  77464 140612 106415200 0   244 
> > 505  1378  2  4 94  0 1  0 398256  77828 140612 106416400 0  
> > 316  580  1954  7  2 91  0 0  0 398256  77804 140612 106418000   
> >  0   740  673  2248  2  2 96  0 0  0 398256  77000 140612 10641800   
> > 0 0   304  589  1739  1  3 96  0
>
> 20 rows later, checkpoint here:
> >  0  0 398256  77000 140612 106418400 0 0  216   886  0  1
> > 99  0 0  0 398256  75452 140612 106

[GENERAL] I have a select statement on the issue.

[gongzhixiao]

I have a select statement on the issue.
Following is the process I operate in four steps totally:
Step 1:Create Table
CREATE TABLE test
(
  code character varying(32) NOT NULL,
  name character varying(32) NOT NULL DEFAULT ''::character varying,
  qty integer NOT NULL DEFAULT 0,
  CONSTRAINT pk_test PRIMARY KEY (code)
)
Step 2:Insert Data
insert into test(code,name,qty) values('1001','1001name','qty');
insert into test(code,name,qty) values('1002','1002name','qty');
insert into test(code,name,qty) values('1003','1003name','qty');
Step 3:Select Data
select * from test
Results:
code name qty
1001 1001name  1
1002 1002name  2
1003 1003name  3
Step 4:Update Date
update test set name='name' where code='1002'
Results:
code name qty
1001 1001name  1
1003 1003name  3
1002 name  2

Question:
1. Why the default output changes after I execute the update statement?
2. Qustion, sorting as main keys when query, how to do?
Thank you.

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] WAL Recovery

[Ashish Karalkar]

Hello List Members,

I have PostgreSQL 8.2.4 on Suse Linux

Whne I am doing recovery from WAL archives I see in the logger that 
"01.history file not found" infact server has not created such file insted 
it created 01.backup  file which contains the history which documents also 
suggest is history file. 
Should I ignore this message in logger?

Secondly, the logger says for e.g file 57 not fond in archive area which is 
present in pg_xlog and was not archived when server went down
 
file upto 56 are there in archive.

can anybody please explain why server giving message for filer 57 not found in 
archive area.

With Regards
Ashish...





   
-
 Explore your hobbies and interests. Click here to begin.

[GENERAL] client_encoding error

[Glyn Astill]

Whilst trying to migrate one of our tables to postgres we get the
following error:

invalid byte sequence for encoding "EUC_JP"
: 0x9c32
HINT:  This error can also happen if the byte sequence does not match
the enccding expected by the server, which is cont   
  rolled by "client_encoding".

Does anyone have any idea why this might be? The data cing into the
table should be plain ASCII


Glyn Astill



  ___ 
Want ideas for reducing your carbon footprint? Visit Yahoo! For Good  
http://uk.promotions.yahoo.com/forgood/environment.html

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] is the default of lowercase enforced on all OS platforms?

[Bruce Momjian]

Kevin Kempter wrote:
> Hi List;
> 
> I know that the default case for postgres (psql) on Linux/*ix platforms is 
> lowercase. I wonder is this also true on Windows platforms?

Yes.

-- 
  Bruce Momjian  <[EMAIL PROTECTED]>http://momjian.us
  EnterpriseDB http://postgres.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] is the default of lowercase enforced on all OS platforms?

[Kevin Kempter]

Hi List;

I know that the default case for postgres (psql) on Linux/*ix platforms is 
lowercase. I wonder is this also true on Windows platforms?


Thanks in advance..

/Kevin


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Tom Lane]

Doug McNaught <[EMAIL PROTECTED]> writes:
> On Nov 27, 2007, at 8:36 PM, Gregory Stark wrote:
>> I think (but I'm not sure) that the kernel in OSX comes from BSD.

> Kind of.  Mach is still running underneath (and a lot of the app APIs  
> use it directly) but there is a BSD 'personality' above it which  
> (AIUI) is big parts of FreeBSD ported to run on Mach.  So when you use  
> the Unix APIs you're going through that.

The one bit of the OSX userland code that I've really had my nose rubbed
in is libedit, and they definitely took that from NetBSD not FreeBSD.
You sure you got your BSDen straight?

Some random poking around at
http://www.opensource.apple.com/darwinsource/10.5/
finds a whole lot of different-looking license headers.  But it seems
pretty clear that their userland is BSD-derived, whereas I've always
heard that their kernel is Mach-based.  I've not gone looking at the
kernel though.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Scott Ribe]

> There are claims this
> is improved in current systems (Leopard + Intel), but the margin was so
> big before...

IIRC, it was later established that during those tests they had fsync
enabled on OS X and disabled on Linux.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Scott Ribe]

> Kind of.  Mach is still running underneath (and a lot of the app APIs
> use it directly) but there is a BSD 'personality' above it which
> (AIUI) is big parts of FreeBSD ported to run on Mach.

Right. Also, to be clear, OS X is not a true microkernel architecture. They
took the "division of responsibilities" from the Mach microkernel design,
but Mach is compiled into the kernel and is not a separate process from the
kernel.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Aly Dharshi]

Only under Solaris.  With Linux or BSD on it it ran pretty well.  I
had a Sparc 20 running RH 7.2 back in the day (or whatever the last
version of RH that ran on sparc was) that spanked an Ultra-2 running
slowalrus with twice the memory and hard drives handily.

Solaris has gotten much better since then, I'm sure.


	Ubuntu is supposed to be able to spin on a T1000/T2000 and they have 
come out with a magical beast called Solaris 10 and in Sun's infinite 
wisdom they have decided to abandon the /etc/init.d/ and friends way of 
startup for some complex XML way of doing things. But otherwise its 
quite good (ZFS and Cool Thread servers being among the other good 
things out of Sun's shop).


Cheers,

Aly.

--
Aly Dharshi
[EMAIL PROTECTED]
Got TELUS TV ? 310-MYTV or http://www.telus.com/tv

 "A good speech is like a good dress
  that's short enough to be interesting
  and long enough to cover the subject"


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Scott Marlowe]

On Nov 27, 2007 8:05 PM, Ron Johnson <[EMAIL PROTECTED]> wrote:
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> On 11/27/07 19:35, Greg Smith wrote:
> [snip]
> > to you.  The minute performance becomes a serious concern, you'd be much
> > better off with Linux, one of the BSDs that's not hobbled by using the
> > Mach kernel, or one of the more serious UNIXes like Solaris.
>
> Wasn't there a time (2 years ago?) when PG ran pretty dog-like on SPARC?

Only under Solaris.  With Linux or BSD on it it ran pretty well.  I
had a Sparc 20 running RH 7.2 back in the day (or whatever the last
version of RH that ran on sparc was) that spanked an Ultra-2 running
slowalrus with twice the memory and hard drives handily.

Solaris has gotten much better since then, I'm sure.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/07 19:35, Greg Smith wrote:
[snip]
> to you.  The minute performance becomes a serious concern, you'd be much
> better off with Linux, one of the BSDs that's not hobbled by using the
> Mach kernel, or one of the more serious UNIXes like Solaris.

Wasn't there a time (2 years ago?) when PG ran pretty dog-like on SPARC?

- --
Ron Johnson, Jr.
Jefferson LA  USA

%SYSTEM-F-FISH, my hovercraft is full of eels
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTMzQS9HxQb37XmcRAo91AJ0d1l1LW0REaUEyVwrkhAF7u6+EYgCaA1aG
/qrqS5JebnStbMbO/QD+YA0=
=U6ta
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/07 19:36, Gregory Stark wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
[snip]
> 
> That was true of the traditional BSD 4.3 and 4.4 design. However when people
> refer to "BSD" these days they're referring to one of the major derivatives
> which have all undergone extensive further development. FreeBSD has crowed a
> lot about their finer-grained kernel locks too for example. Other variants of
> BSD tend to focus on other areas (like portability for example) so they may
> not be as far ahead but they've still undoubtedly made significant progress
> compared to 1993.

NetBSD and OpenBSD are still pretty not-good at scaling up.

But they're darned good at running on 68K Macs (NBSD) and
semi-embedded stuff like low-end firewalling routers (OBSD).

>> Not much of a kernel guy here but my understanding is that MacOSX is
>> basically NeXT version 10, which means... Mach... which is entirely
>> different than say FreeBSD at the kernel level.
> 
> I think (but I'm not sure) that the kernel in OSX comes from BSD. What they
> took from NeXT was the GUI design and object oriented application framework
> stuff. Basically all the stuff that Unix programmers still haven't quite
> figured out what it's good for.

Even AfterStep is written is plain C...

- --
Ron Johnson, Jr.
Jefferson LA  USA

%SYSTEM-F-FISH, my hovercraft is full of eels
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTMqjS9HxQb37XmcRAmS+AKCyzxZ9b1jmcye8gEwlun7VrszhfgCfVC6B
LEaSaGlorSQ5lX5eIIgx7dM=
=NvJi
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Doug McNaught]

On Nov 27, 2007, at 8:36 PM, Gregory Stark wrote:


I think (but I'm not sure) that the kernel in OSX comes from BSD.


Kind of.  Mach is still running underneath (and a lot of the app APIs  
use it directly) but there is a BSD 'personality' above it which  
(AIUI) is big parts of FreeBSD ported to run on Mach.  So when you use  
the Unix APIs you're going through that.


-Doug

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Another question about partitioning

[paul rivers]

Alex Vinogradovs wrote:

Hello all,


I have a table which is partitioned by range into 10 pieces with
constraint exceptions. Constraint exceptions is enabled in server
configuration too. For some reason, queries to the master table
are still slower than direct queries against partitions. Is there
any real reason for that, or I should look into misconfiguration ?

Thanks!


Best regards,
Alex Vinogradovs
  
Is that true even if you type the query yourself in psql and ensure that 
the values for the partitioned columns are constants in the where 
clause?  Can you post an explain of the sql?


Paul


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Gregory Stark]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:

> On Tue, 27 Nov 2007 17:01:06 -0700
> Scott Ribe <[EMAIL PROTECTED]> wrote:
>
>> > In general, you can expect any Unix based OS, which includes MacOS
>> > X, to perform noticeably better than Windows for PostgreSQL.
>> 
>> Is that really true of BSD UNIXen??? I've certainly heard it's true of
>> Linux. But with BSD you have the "kernel funnel" which can severely
>> limit multitasking, regardless of whether threads or processes were
>> used. Apple has been working toward finer-grained locking precisely
>> because that was a serious bottleneck which limited OS X server
>> performance.
>>
>> Or have I misunderstood and this was only the design of one particular
>> flavor of BSD, not BSDen in general?

That was true of the traditional BSD 4.3 and 4.4 design. However when people
refer to "BSD" these days they're referring to one of the major derivatives
which have all undergone extensive further development. FreeBSD has crowed a
lot about their finer-grained kernel locks too for example. Other variants of
BSD tend to focus on other areas (like portability for example) so they may
not be as far ahead but they've still undoubtedly made significant progress
compared to 1993.

> Not much of a kernel guy here but my understanding is that MacOSX is
> basically NeXT version 10, which means... Mach... which is entirely
> different than say FreeBSD at the kernel level.

I think (but I'm not sure) that the kernel in OSX comes from BSD. What they
took from NeXT was the GUI design and object oriented application framework
stuff. Basically all the stuff that Unix programmers still haven't quite
figured out what it's good for.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Greg Smith]

On Tue, 27 Nov 2007, Wolfgang Keller wrote:

Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, 
XP, Vista etc.) on the same hardware? And Linux to (Free-/Net-/whatever) 
BSD?


Apple hardware gets so expensive for some types of database configurations 
that such a comparision doesn't even make a lot of sense.  For example, if 
you have an application that needs high database write throughput, to make 
that work well with PostgreSQL you must have a controller with a battery 
backed cache.  If I have a PC, the entry-level solution in that category 
can be a random sub-$1000 system that runs Linux plus around $400 for a 
RAID card with BBC, and you've got multiple vendors to consider there 
(3Ware, Areca, LSI Logic, etc.)


To do something similar with Apple hardware, you can get a Mac Pro and add 
their RAID card, at $3500 (early reports suggest even that may have 
serious problems, see http://forums.macrumors.com/showthread.php?t=384459 
). Or you can pick up an XServe RAID, but now you're talking $6350 because 
the smallest configuration is 1TB.  The amount of server you can buy for 
$3500+ running Linux is going to be much more powerful than its Apple 
equivilant.  Sure, you can run a trivial workload that features minimal 
writes even on a Mac Mini, but I don't see a lot of value to considering a 
platform where the jump to the cheapest serious server configuration is so 
big.


Also, in previous generations, the Mach kernel core of Mac OS had some 
serious performance issues for database use even in read-heavy workloads: 
http://www.anandtech.com/mac/showdoc.aspx?i=2520&p=5 There are claims this 
is improved in current systems (Leopard + Intel), but the margin was so 
big before I would need some pretty hard proof to believe they've even 
achieved parity with Linux/FreeBSD on the same hardware, and even then the 
performance/dollar is unlikely to be competative.


I'm just wondering whether the performance gain is worth the learning 
effort required for Linux or BSD compared to the Mac.


On both Windows (where you get limitations like not being able to set a 
large value for shared_buffers) and Mac OS X, PostgreSQL has enough 
performance issues that I feel using those plaforms can only be justified 
if platform compatibility is more important than performance to you.  The 
minute performance becomes a serious concern, you'd be much better off 
with Linux, one of the BSDs that's not hobbled by using the Mach kernel, 
or one of the more serious UNIXes like Solaris.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Another question about partitioning

[Gregory Stark]

"Alex Vinogradovs" <[EMAIL PROTECTED]> writes:

> Hello all,
>
>
> I have a table which is partitioned by range into 10 pieces with
> constraint exceptions. Constraint exceptions is enabled in server
> configuration too. For some reason, queries to the master table
> are still slower than direct queries against partitions. Is there
> any real reason for that, or I should look into misconfiguration ?

Well you have to look at the actual plans. Having to combine multiple
partitions does have some cost to it and does interfere somewhat in the
planner's ability to optimize plans so it might not be a win on individual
queries if they were not doing big scans of unnecessary data previously. 

You might also consider using partial indexes instead of partitioning if your
goal is just optimizing queries. The big advantage of partitioning is being
able to add and drop entire partitions effectively instantaneously.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's PostGIS support!

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 11/27/07 18:01, Scott Ribe wrote:
>> In general, you can expect any Unix based OS, which includes MacOS X, to
>> perform noticeably better than Windows for PostgreSQL.
> 
> Is that really true of BSD UNIXen??? I've certainly heard it's true of
> Linux. But with BSD you have the "kernel funnel" which can severely limit
> multitasking, regardless of whether threads or processes were used. Apple
> has been working toward finer-grained locking precisely because that was a
> serious bottleneck which limited OS X server performance.
> 
> Or have I misunderstood and this was only the design of one particular
> flavor of BSD, not BSDen in general?

IIRC, FreeBSD got rid of the Giant Lock back in v5.x.

There was a benchmark in Feb 2007 which demonstrated that FBSD 7.0
scaled *better* than Linux 2.6 after 4 CPUs.
http://jeffr-tech.livejournal.com/5705.html

Turns out that there was/is a bug in glibc's malloc().  Don't know
if it's been fixed yet.

- --
Ron Johnson, Jr.
Jefferson LA  USA

%SYSTEM-F-FISH, my hovercraft is full of eels
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTMAfS9HxQb37XmcRAg4NAJsFXVFa5NQtctsdrjbNCZ8GRAHMlwCeOfZr
kBFOQUI6zGcTDiy793+JSIc=
=/W4e
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] Another question about partitioning

[Alex Vinogradovs]

Hello all,


I have a table which is partitioned by range into 10 pieces with
constraint exceptions. Constraint exceptions is enabled in server
configuration too. For some reason, queries to the master table
are still slower than direct queries against partitions. Is there
any real reason for that, or I should look into misconfiguration ?

Thanks!


Best regards,
Alex Vinogradovs

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Joshua D. Drake]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On Tue, 27 Nov 2007 17:01:06 -0700
Scott Ribe <[EMAIL PROTECTED]> wrote:

> > In general, you can expect any Unix based OS, which includes MacOS
> > X, to perform noticeably better than Windows for PostgreSQL.
> 
> Is that really true of BSD UNIXen??? I've certainly heard it's true of
> Linux. But with BSD you have the "kernel funnel" which can severely
> limit multitasking, regardless of whether threads or processes were
> used. Apple has been working toward finer-grained locking precisely
> because that was a serious bottleneck which limited OS X server
> performance.
> 
> Or have I misunderstood and this was only the design of one particular
> flavor of BSD, not BSDen in general?

Not much of a kernel guy here but my understanding is that MacOSX is
basically NeXT version 10, which means... Mach... which is entirely
different than say FreeBSD at the kernel level.

Joshua D. Drake

> 


- -- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFHTLBoATb/zqfZUUQRAs6OAJ4yIYWauPpZybyVZJlF/RScFoZrawCeOYv7
osMbcJEVqqJfLGOo6uRJBMY=
=hgrE
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Scott Ribe]

> In general, you can expect any Unix based OS, which includes MacOS X, to
> perform noticeably better than Windows for PostgreSQL.

Is that really true of BSD UNIXen??? I've certainly heard it's true of
Linux. But with BSD you have the "kernel funnel" which can severely limit
multitasking, regardless of whether threads or processes were used. Apple
has been working toward finer-grained locking precisely because that was a
serious bottleneck which limited OS X server performance.

Or have I misunderstood and this was only the design of one particular
flavor of BSD, not BSDen in general?

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] spatial composition operators for PG

[Martin Gainty]

Jan-

check out
http://postgis.refractions.net/

M--
- Original Message - 
From: "Galkowski, Jan" <[EMAIL PROTECTED]>
To: 
Sent: Tuesday, November 27, 2007 6:02 PM
Subject: [GENERAL] spatial composition operators for PG


Are there any standard spatial composition operators people use for PG's
polygon and path data types?  That is, it seems there ought to be
extenders of the signatures,

 polygon <- polygon %+% (point, vertex-number)

 path <- path %+% (point, node-number)

where the right-hand-sided point is the new point, and the vertex-number
or node-number tell where to put it.  This function might be an
update-in-place. 

I saw nothing like this in the PG docs, but I figured people probably
made such things up.  Any pointers?  Anyone willing to share their
favorites online some place?

TIA, 
 
 - Jan


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] spatial composition operators for PG

[Galkowski, Jan]

Are there any standard spatial composition operators people use for PG's
polygon and path data types?  That is, it seems there ought to be
extenders of the signatures,

 polygon <- polygon %+% (point, vertex-number)

 path <- path %+% (point, node-number)

where the right-hand-sided point is the new point, and the vertex-number
or node-number tell where to put it.  This function might be an
update-in-place. 

I saw nothing like this in the PG docs, but I figured people probably
made such things up.  Any pointers?  Anyone willing to share their
favorites online some place?

TIA, 
 
 - Jan


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[GENERAL] Config settings for large restore

[Erik Jones]

Hi,

I'm just wondering what is considered the general wisdom on config  
setting for large pg_restore runs.  I know to increase  
maintenance_work_mem and turn off autovacuum and stats collection.   
Shoule should checkpoint_segments and checkpoint_timeout be  
increased?  Would twiddling shared_buffers help?  What about the  
bgwriter (I'm thinking this should just follow the checkpoint and  
shared_buffers settings)?  I realize that the actual setting will be  
hardware dependent, I'm just looking for general rules of thumb for  
what to tweak wrt increased restore speed/performance.



Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] Unknown winsock error 10061while dumping a big database

[Cyril VELTER]

I recently upgraded a 8.0 database to 8.2 (server is running windows 
2003 
server) and now I cannot dump the database any more.

At some point in the dump, pg_dump (run on a centos 3 linux) return : 

pg_dump: Dumping the contents of table "c2674" failed: PQgetCopyData() failed.
pg_dump: Error message from server: out of memory
pg_dump: The command was: COPY public.c2674 (p2, p9, p3337, p18155, p18154, 
p17561, p4065, p689, p43711, p4083, p4020, p4029, p4218, p4074, p45127, p3857, 
p7, p6, p5, p32402, p5512, p18175, p42542, p17562, p4) TO stdout;

In the server logs I only get :

LOG:  could not send data to client: Unknown winsock error 10061


I tried two time and the dump nearly stop at the same point. I'm trying 
now to 
dump only this tables (c2674), it's a 60 GB table in a ~100 GB database 
initialized in C locale. This table have several fields which can be pretty 
large (the biggest row is ~35MB).


Any ideas ?


Cyril VELTER


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Connection idle broken

[Scott Marlowe]

On Nov 27, 2007 12:50 PM, Douglas McNaught <[EMAIL PROTECTED]> wrote:
> On 11/27/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> >
> > Actually, if you use the linux based ones, or download dd-wrt or
> > openwrt and install that, the linksys routers are quite stable,
> > reliable, and performant.
>
> I'm sure they are.  Custom software helps a lot.  :)
>
> That said, my definition of "production" doesn't include hardware
> that's built as cheaply as possible for the consumer market.  Others
> may differ, and the OPs need may very well be satisfied by a custom
> software load on a cheap consumer router.

Well, if I had a choice between a single internet connection on a big
cisco, and a dual connect through a sonet ring through a pair of
buffalo consumer grade routers with dd-wrt, I'd take the sonet ring.

Course, we'd all rather have both the sonet ring and the big
commercial routers...

(am seriously amazed how many 24/7 ops are out there running with a
sonet ring into the building that turns into a single point of failure
after that.   )

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Magnus Hagander]

On Tue, 2007-11-27 at 11:11 +0100, Wolfgang Keller wrote:
> Hello,
> 
> sorry for "butting in", but I'm just curious...
> 
> > resolution?
> >
> > http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php
> >
> > conclusion?
> >
> > Mac was still pretty slow in comparison
> 
> Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, 
> XP, Vista etc.) on the same hardware?

In general, you can expect any Unix based OS, which includes MacOS X, to
perform noticeably better than Windows for PostgreSQL.

//Magnus


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Tom Lane]

"Thomas Chille" <[EMAIL PROTECTED]> writes:
> I think this are the relevant pg_locks entries:

> relation7568577875686189
>  9017862 25467   AccessShareLock f
> relation7568577875686189
>  9009323 9317ShareUpdateExclusiveLock
>   t
> relation7568577875686189
>  9009312 9293AccessShareLock t
> relation7568577875686189
>  9009312 9293RowExclusiveLockt
> relation7568577875686189
>  9009312 9293AccessExclusiveLock f
> relation7568577875686189
>  9012978 28370   AccessShareLock f

I don't think the vacuum is the problem here.  The problem is process
9293, which for some reason is trying to get AccessExclusiveLock, and is
blocked behind autovac, and everything else is stacking up behind it.

You didn't happen to note what 9293 was doing did you?  It's living
fairly dangerously in any case by trying to acquire exclusive lock
when it already holds a bunch of other lower-level locks; that's a
recipe for deadlock if I ever saw one.

regards, tom lane

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Connection idle broken

[Douglas McNaught]

On 11/27/07, Scott Marlowe <[EMAIL PROTECTED]> wrote:
> On Nov 27, 2007 12:33 PM, Douglas McNaught <[EMAIL PROTECTED]> wrote:

> > Get a better router then. Something between your clients and the
> > database server is timing out those connections, and it's most likely
> > that box--NAT connections are timed out fairly aggressively by default
> > on consumer routers (you didn't say whether you were using NAT or not,
> > but it may be turned on by default).  Relying on anything labeled
> > "Linksys" for production work is a terrible idea.
>
> Actually, if you use the linux based ones, or download dd-wrt or
> openwrt and install that, the linksys routers are quite stable,
> reliable, and performant.

I'm sure they are.  Custom software helps a lot.  :)

That said, my definition of "production" doesn't include hardware
that's built as cheaply as possible for the consumer market.  Others
may differ, and the OPs need may very well be satisfied by a custom
software load on a cheap consumer router.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Res: [GENERAL] Connection idle broken

[Fernando Xavier]

Right.. i'm sorry :-)

i solved this problem according Scott Marlowe suggestion. I changed the 
postgresql.conf

tcp_keepalives_idle = 300 
tcp_keepalives_interval = 60 
tcp_keepalives_count = 10  

The connection don't broke more...Actually, the router linksys isn't good for 
network complex.. 

Thanks for all replies! :-)
 
regards, 
Fernando

- Mensagem original 
De: Douglas McNaught <[EMAIL PROTECTED]>
Para: Fernando Xavier <[EMAIL PROTECTED]>
Cc: pgsql-general@postgresql.org
Enviadas: Terça-feira, 27 de Novembro de 2007 16:33:35
Assunto: Re: [GENERAL] Connection idle broken

On 11/27/07, Fernando Xavier <[EMAIL PROTECTED]> wrote:
>
>
> Hi, thanks for reply!
>
> No, my router don't have configurations for timeout connections..

Get a better router then. Something between your clients and the
database server is timing out those connections, and it's most likely
that box--NAT connections are timed out fairly aggressively by default
on consumer routers (you didn't say whether you were using NAT or not,
but it may be turned on by default).  Relying on anything labeled
"Linksys" for production work is a terrible idea.

Also, please keep your replies on the mailing list so others can
benefit from the discussion.

-Doug







  Abra sua conta no Yahoo! Mail, o único sem limite de espaço para 
armazenamento!
http://br.mail.yahoo.com/

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Connection idle broken

[Scott Marlowe]

On Nov 27, 2007 12:33 PM, Douglas McNaught <[EMAIL PROTECTED]> wrote:

> Get a better router then. Something between your clients and the
> database server is timing out those connections, and it's most likely
> that box--NAT connections are timed out fairly aggressively by default
> on consumer routers (you didn't say whether you were using NAT or not,
> but it may be turned on by default).  Relying on anything labeled
> "Linksys" for production work is a terrible idea.

Actually, if you use the linux based ones, or download dd-wrt or
openwrt and install that, the linksys routers are quite stable,
reliable, and performant.

Myself, I prefer the pre-installed buffalo router that DD-WRT ships,
but the software they make is quite amazing.

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Connection idle broken

[Douglas McNaught]

On 11/27/07, Fernando Xavier <[EMAIL PROTECTED]> wrote:
>
>
> Hi, thanks for reply!
>
> No, my router don't have configurations for timeout connections..

Get a better router then. Something between your clients and the
database server is timing out those connections, and it's most likely
that box--NAT connections are timed out fairly aggressively by default
on consumer routers (you didn't say whether you were using NAT or not,
but it may be turned on by default).  Relying on anything labeled
"Linksys" for production work is a terrible idea.

Also, please keep your replies on the mailing list so others can
benefit from the discussion.

-Doug

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] indexing tables using my owns functions

[Martijn van Oosterhout]

On Mon, Nov 26, 2007 at 04:54:10PM +0100, Pau Marc Munoz Torres wrote:
> Hi every body
> 
>  Recently i wrote my own function into postgesql... and it works perfecly!!,
> now i would like use it to index a table like this guy do at the link (
> http://www.faqs.org/docs/ppbook/r24254.htm)
> 
> but i get the following error
> 
> mhc2db=> create index h2iab on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));
> ERROR:  functions in index expression must be marked IMMUTABLE

Where you declare the function, add the modifier IMMUTABLE.

Have a nice day,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution 
> inevitable.
>  -- John F Kennedy


signature.asc
Description: Digital signature

Re: [GENERAL] Non-unique values problem after 'add column'

[Tom Lane]

[EMAIL PROTECTED] writes:
> I've encountered a very strange problem with Postgresql 7.3.

7.3.what?

FWIW, I cannot replicate your problem using 7.3.20.  It doesn't sound
like any bug I can remember, but certainly there have been a lot of
bugs fixed over the past five years.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Error while compiling PostgreSQL with Java

[Никоноров Григорий]

Thanks al for the answers !!! I solve my problem uninstalling source  
PostgreSQL and installed from binarys. I use JDBC driver from  
http://jdbc.postgresql.org/. Now all work fine. :)


p.s. Unfortunately i dont solve problem how to compile postgreSQL with  
java but found very easy method to install PostgreSQL with full java  
support from binarys.


Grigory.

On Mon, 26 Nov 2007 00:36:01 +0300, Lew <[EMAIL PROTECTED]>  
wrote:



Peter Eisentraut wrote:

Никоноров Григорий wrote:

I have a problem. I try to install PostgreSQL from source with java. I
Installed JDK, ANT properly but when i try to configure i have an  
error -

Warning.Ignored options - --with-java. Please help me !
 PostgreSQL has no such option.  You may be looking for the JDBC  
driver.  If so, see http://jdbc.postgresql.org/.


Yes, this answer has been provided to the OP already.





--
Отправлено M2, революционной почтовой программой Opera:  
http://www.opera.com/mail/


---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] \copy ... with null as '' csv doesn't get nulls

[Ivan Sergio Borgonovo]

On Sun, 25 Nov 2007 13:22:48 -0500
Lew <[EMAIL PROTECTED]> wrote:

> I went to the docs for COPY and they say,
> > The default is \N (backslash-N) in text mode, and a empty value
> > with no quotes in CSV mode.

> That "with no quotes" phrase caught my attention.

> Try eliminating the double quotes in the CSV file.  "Wannabe" NULL
> would then be ,, (consecutive commas)
> in the CSV.  From the docs, you don't even need the "NULL AS"
> clause in your COPY statement.

Exactly what I did because fortunately there weren't too many chances
of weird stuff in 2000 records (sed -e 's/,""/,/g').

Anyway with NULL AS '' and without it I can't still import NULL
without pre-processing.

I thought it may be missing total support of UTF-8 or if I did
something wrong or it is actually a "feature".

thx

-- 
Ivan Sergio Borgonovo
http://www.webthatworks.it


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] postgres 8.3 beta 2 storage question

[SHARMILA JOTHIRAJAH]

> 1.How does postgres version 8.3 betat 2 handle varchar and numeric
 data
> types in terms of storage
> I understand for varchar it has 1byte overhead (instead of 4) if
 length<128
> How does it handle for numeric? The manual says
> "The actual storage requirement is two bytes for each group of four
 decimal
> digits, plus five to eight bytes overhead."
>which manual?
>( not these two: )
>http://www.postgresql.org/docs/8.1/static/datatype.html#DATATYPE-NUMERIC-DECIMAL
>http://www.postgresql.org/docs/8.2/static/datatype-numeric.html#DATATYPE-NUMERIC-DECIMAL

http://www.postgresql.org/docs/8.3/static/datatype-numeric.html

> 2. Does postgres always inserts a complete record into a page unlike
 oracle
> whose record can span multiple pages? Does this waste space too?
>records larger than one page are TOASTed

>http://www.postgresql.org/docs/current/static/storage-page-layout.html

Thanks
sharmila






  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

Re: [GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?

[Andrew Sullivan]

On Mon, Nov 26, 2007 at 11:57:37PM -0800, Alagu Madhu wrote:
> Which is the best Stored Procedures or Dynamic SQL generated by ORM ?

This is roughly similar to the question, "Is it better to travel by car,
boat, or plane?"  It rather depends on what you're trying to accomplish. 

A

-- 
Andrew Sullivan
Old sigs will return after re-constitution of blue smoke

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?

[Scott Marlowe]

On Nov 27, 2007 1:57 AM, Alagu Madhu <[EMAIL PROTECTED]> wrote:
>
> Which is the best Stored Procedures or Dynamic SQL generated by ORM ?

Depends on what you mean by best.  Dynamic SQL costs more in the
planning stage because the db has to plan out each statement before
running it.  However, it can consider the actual values in the queries
when creating those plans, so you get individually optimized plans for
each query.  Generally speaking Stored Procs (UDFs in pgsql) use
static query  / execution plans created at the time the UDF was
brought into being.

You should also considered prepared SQL from the client side as well.
Not sure if your ORM supports that or not.

Not a fan of ORM, but it's a necessary evil sometimes. :)

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Non-unique values problem after 'add column'

[Scott Marlowe]

On Nov 26, 2007 3:06 AM,  <[EMAIL PROTECTED]> wrote:
> Hello!
>
> I've encountered a very strange problem with Postgresql 7.3.
> I have the following set of queries running in transaction, using
> libpqxx 2.6.9:

7.3 is getting pretty old.  I can't say anything though, I've still
got a 7.4 db hanging around somewhere too. :)

> ALTER TABLE my_table ADD new_column TEXT
> UPDATE my_table SET new_column = 'disable'
> ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 'disable'
> CREATE UNIQUE INDEX my_index ON my_table (old_column)
>
> The last query fails with:
> ERROR:  Cannot create unique index. Table contains non-unique values
>
> Running the same set of queries manually causes no problems. Moreover,
> running the last query in a separate transaction works as well.

I'm guessing the difference here is NOT libpqxx, but the data in the
db.  Are you sure you're running this against the exact same data set?
 If so, you need to make a self-contained test that shows this
happening only to libpqxx.  I.e. table def, data, sql executed, etc.

Often times, while making such a self-contained proof, you'll figure
out what's really going on, and it usually isn't a bug in libpqxx at
that point.

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Why LIMIT and OFFSET are commutative

[Erik Jones]

On Nov 26, 2007, at 5:29 AM, Andrus wrote:


Under what interpretation would the results differ?


Results must differ for easy creation of LinQ-PostgreSQL driver.
If results are always the same , PostgreSQL should not allow to use  
both

order of clauses.

Nicholas explains:

   Assuming the ordering is the same on each of them (because Skip  
and Take
make no sense without ordering, LINQ to SQL will create an order  
for you,

which irritates me to no end, but that's a separate thread), they will
produce different results.

Say your query will produce the ordered set {1, 2, 3}.  Let n =  
1, m =

2.

The first query:

var query = query.Skip(n).Take(m);

converted to SELECT ... OFFSET n LIMIT m

Will return the ordered set {2, 3}, while the second query:

var query = query.Take(m).Skip(n);

converted to SELECT ... LIMIT m OFFSET n

Will return the ordered set {2}.

The reason for this is that in the first query, the Skip method  
skips
one element, then takes the remaining two, while in the second  
query, the

first two elements are taken, and then the first one is skipped.


Nice.  Yet another example of an Object-Relational impedance  
mismatch.  SQL is declarative, not procedural.


Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] System Load analyze

[Scott Marlowe]

On Nov 24, 2007 10:57 AM, Peter Bauer <[EMAIL PROTECTED]> wrote:
>
> i have a system here with 2 2.4GHz Xeon Processors, 2GB RAM, ONE Disk on
> a Battery Backed Write Cache SCSI Controller and PostgreSQL 8.1.4
> running with the data on a DRBD Device for High Availability. The used
> database is also replicated to two similar machines with slony1.

Why are you running a version of PostgreSQL with known data eating
bugs? If you care for your data, you will keep up to date on releases.
 8.1.10 was released on 2007-09-17.  8.1.4 was released on 2006-05-23.
 That's 16 months of bug fixes you're missing.  Go here:
http://www.postgresql.org/docs/8.1/static/release.html and read up on
the fixes you're missing.  Then update.  Or just update.

OK, on the the issue at hand.

> Since the load average is between 1 (most of the time) and 10 (peeks) i
> am worried about the load and executed vmstat and iostat which show that
> 1000-6000 Blocks are writen per second. Please check the attached output
> for further details.
> top shows that the CPUs are at least 80% idle most of the time so i
> think there is an I/O bottleneck. I'm aware that this hardware setup is
> probably not sufficient but is would like to investigate how critical
> the situation is.

Yes.  Battery backed cache can only do so much, it's not magic pixie
dust.  Once it's full, the drive becomes the bottle neck.  Real db
servers have more than one disk drive.  They usually have at least 4
or so, and often dozens to hundreds. Also, not all battery backed
caching RAID controllers are created equal.

> procs ---memory-- ---swap-- -io --system-- cpu
>  r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
>  0  0 398256  78328 140612 106355600 0  1472 2029  5081  4  3 92  > 0
>  0  2 398256  78268 140612 106357600 0  2304 1928  4216  0  2 98  > 0
>  1  2 398256  78100 140612 106357600 0  1464 1716  3994  1  3 96  > 0
>  0  0 398256  78704 140612 106359200 0   916 1435  3570  5  3 91  > 0
>  0  0 398256  77876 140612 106361600 0 0  305  1169  3  1 96  > 0

See that dip in the last line above where the blocks in drop to 0,
idle jumps to 96, and blocks out drops, and context switches drop?
That's most likely where postgresql is checkpointing.  Checkpointing
is where it writes out all the dirty buffers to disk.  If the bgwriter
is not tuned aggresively enough, checkpoints happen and make the whole
database slow down for a few seconds.  If it's tuned too aggresively
then the db spends too much CPU time tracking the dirty buffers and
then writing them.  If tuned just right, it will write out the dirty
buffers just fast enough that a checkpoint is never needed.

You tune the bgwriter to your machine and I/O subsystem.  If you're
planning on getting more hard drives, do that first.  Then tune the
bgwriter.

btw, if this is "vmstat 1" running, it's showing a checkpoint every 20
or so seconds I think

>  0  2 398256  79136 140612 106396400 0  1736 1959  4494  4  2 94  > 0
checkpoint here:
>  0  0 398256  79132 140612 106396400 0 4  260  1039  1  1 98  > 0
>  0  0 398256  79052 140612 106398000 0  2444 3084  6955  6  5 89  > 0
>  0  2 398256  79060 140612 106398800 0   948 1146  3616  3  1 96  > 0
>  0  1 398256  78268 140612 106405600 0  1908 1809  4086  6  5 88  > 0
>  0  1 398256  76728 140612 106405600 0  6256 6637 15472  5  5 90  > 0
>  0  2 398256  77000 140612 106406400 0  4916 5840 12107  1  4 95  > 0
>  0  2 398256  76956 140612 106406800 0  6468 7432 15211  1  3 96  > 0
>  0  6 398256  77388 140612 106407200 0  8116 7826 18265  1  8 91  > 0
>  0  2 398256  74312 140612 106407600 0  7032 6886 16136  2  7 91  > 0
>  0  2 398256  74264 140612 106407600 0  5680 7143 13411  0  5 95  > 0
>  0  2 398256  72980 140612 106414000 0  5396 6377 13251  6  6 88  > 0
>  0  3 398256  76972 140612 106414800 0  5652 6793 14079  4  9 87  > 0
>  0  2 398256  77836 140612 106414800 0  3968 5321 14187 10  8 82  > 0
>  1  0 398256  77280 140612 106414800 0  1608 3188  8974 21 12 67  > 0
>  1  0 398256  77832 140612 106415200 0   236  834  2625  7  5 87  > 0
>  0  0 398256  77464 140612 106415200 0   244  505  1378  2  4 94  > 0
>  1  0 398256  77828 140612 106416400 0   316  580  1954  7  2 91  > 0
>  0  0 398256  77804 140612 106418000 0   740  673  2248  2  2 96  > 0
>  0  0 398256  77000 140612 106418000 0   304  589  1739  1  3 96  > 0
20 rows later, checkpoint here:
>  0  0 398256  77000 140612 106418400 0 0  216   886  0  1 99  > 0
>  0  0 398256  75452 140612 106418400 0   432  755  2032  6  1 93  > 0

> max_fsm_pages = 4   # min max_fsm_relations*16, 6 bytes 
> each, AP

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Tom Lane]

"Thomas Chille" <[EMAIL PROTECTED]> writes:
> Ah ok, 9293 is a triggerd process and tries to "ALTER TABLE ...
> DISABLE TRIGGER (other trigger)" and so implicitly tries to acquire an
> AccessExclusiveLock and runs in a deadlock?

Well, you're certainly risking deadlock with that; and even if no
actual deadlock happens, waiting for the exclusive lock needed to
do the ALTER can cause the kind of lock queueing you had here.

I'd suggest trying to think of a way to solve your problem that doesn't
need trigger enabling/disabling.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly

Re: [GENERAL] Why LIMIT and OFFSET are commutative

[Gregory Stark]

"Andrus" <[EMAIL PROTECTED]> writes:

>> Under what interpretation would the results differ?
>
> Results must differ for easy creation of LinQ-PostgreSQL driver.
> If results are always the same , PostgreSQL should not allow to use both
> order of clauses.
>
> Nicholas explains:
>
>Assuming the ordering is the same on each of them (because Skip and Take
> make no sense without ordering, LINQ to SQL will create an order for you,
> which irritates me to no end, but that's a separate thread), they will
> produce different results.
>
> Say your query will produce the ordered set {1, 2, 3}.  Let n = 1, m =
> 2.
>
> The first query:
>
> var query = query.Skip(n).Take(m);
>
> converted to SELECT ... OFFSET n LIMIT m
>
> Will return the ordered set {2, 3}, while the second query:
>
> var query = query.Take(m).Skip(n);
>
> converted to SELECT ... LIMIT m OFFSET n

You should use subqueries if you want to do that. Take() and Skip() can take a
query and rewrite it as:

SELECT * FROM (old query) LIMIT n
or
SELECT * FROM (old query) OFFSET n

So you'll end up with a query like

SELECT * FROM (SELECT * FROM (query) LIMIT n) OFFSET n

or vice versa.

Or alternatively do the arithmetic. If there's already an offset in the query
structure when Skip() is called then add that amount to the offset. I'm
assuming your methods are called on some kind of object which can store
arbitrary state and not simply on a query string.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Ask me about EnterpriseDB's On-Demand Production Tuning

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] select from an index

[Steve Atkins]

On Nov 27, 2007, at 3:15 AM, Pau Marc Munoz Torres wrote:


Hi

 Recently i created an index in a table using a function (not a  
column) as following


create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,


now, i would like to perform a query using this index, something like


Select * from precalc where h2iab>2

but obviously h2iab  is not a column...

some of you knows what i should do?



select * from precalc where idr(p1,p4,p6,p7,p9,'H-2*IAb') > 2

Cheers,
  Steve


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Unused item pointers - can someone explain?

[Elmer]

On 23 Lis, 10:23, [EMAIL PROTECTED] ("Peter Childs") wrote:
> Yes, however
>
> found x removable
>
> have just been found and are now unused, so on the next run this number will
> be added to the unused unless they get used again in the mean time.
>
> The number ie the unused is the number of tuples left in the free space map
> unused since the last vacuum. If its high it may be worth clustering or
> running vacuum full but only if you don't think you table will never or
> unlikely to grow (insert) or change (update) by less than that number of
> records before you next run vacuum.
>
> Generally only worry if the number is very very high (over 1).
>
> The best way of understanding the numbers is to run vacuum at regular
> intervals and compare the output.
>
> Peter.

Thank you for your explanation - it's not easy to get help in this
subject... But how it is possible that new unused.

You wrote:

> The number ie the unused is the number of tuples left in the free space map 
> unused since the last vacuum.

This is important information for me but I still can't understand why
this number keeps growing.Correct me if I wrong but if there is 17000
unused tuples in free space map, they should be used in first place
for creating new tuples versions. This should cause that next 17000
operations (consists of INSERT,UPDATE,DELETE) would use fsm for row
version creation instead of creating entirely new tuples at the end of
table file. If I understand it correct number of unused item pointers
should shrink between vacuums (but it still grows)...

Regards
Elmer

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Wolfgang Keller]

Hello,

sorry for "butting in", but I'm just curious...


resolution?

http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php

conclusion?

Mac was still pretty slow in comparison


Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, 
XP, Vista etc.) on the same hardware?


And Linux to (Free-/Net-/whatever) BSD?

No flamebait, I'm just wondering whether the performance gain is worth 
the learning effort required for Linux or BSD compared to the Mac.


Sincerely,

Wolfgang Keller

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] select from an index

[Pau Marc Munoz Torres]

Hi

 Recently i created an index in a table using a function (not a column) as
following

create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,

where idr is a function that returns a real number,

as a result i got the following table

mhc2db=> \d precalc;
Table "public.precalc"
 Column | Type|  Modifiers
-+---+--
 id  | integer | not null default
nextval('precalc_id_seq'::regclass)
 p1 | character(1)  |
 p4 | character(1)  |
 p6 | character(1)  |
 p7 | character(1)  |
 p9 | character(1)  |
Indexes:
"h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),

now, i would like to perform a query using this index, something like


Select * from precalc where h2iab>2

but obviously h2iab  is not a column...

some of you knows what i should do?

thanks

-- 
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de  Biotecnologia   i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : [EMAIL PROTECTED]

Re: [GENERAL] replication in Postgres

[Sascha Bohnenkamp]

> Sorry, this makes no sense to me -- EnterpriseDB has no replication
> solution that I know of.
slony is bundled with the database

>> Postgres-r sounds very nice but moving our organisations data onto a
>> system that it work in progress is very scary.
> 
> You are already offloading your data to PostgreSQL which is a work in
> progress too ...
;)

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Stored Procedures vs Dynamic SQL generated by ORM ?

[Alagu Madhu]

Hi All,


Which is the best Stored Procedures or Dynamic SQL generated by ORM ?



Thanks

Madhu Alagu

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Help Needed

[Amit]

Hi,

Hope you all are doing gr8!

I need a help regarding PostgreSQL installation. I am using PostgreSQL for a 
web-based application using Tomcat. I want to install PostgreSQL 
automatically to user system(using some EXE) with a default database(which 
is required for my application to run). The main problem is how the default 
database can be created and restore of the data can be done automatically 
using some script or so. Any suggestion or idea are welcome.
Thanks in advance.
Regards,
Amit 

Re: [GENERAL] Error while compiling PostgreSQL with Java

[Lew]

Peter Eisentraut wrote:

Никоноров Григорий wrote:

I have a problem. I try to install PostgreSQL from source with java. I
Installed JDK, ANT properly but when i try to configure i have an error -
Warning.Ignored options - --with-java. Please help me !


PostgreSQL has no such option.  You may be looking for the JDBC driver.  If 
so, see http://jdbc.postgresql.org/.


Yes, this answer has been provided to the OP already.

--
Lew

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Why LIMIT and OFFSET are commutative

[Andrus]

> Under what interpretation would the results differ?

Results must differ for easy creation of LinQ-PostgreSQL driver.
If results are always the same , PostgreSQL should not allow to use both
order of clauses.

Nicholas explains:

   Assuming the ordering is the same on each of them (because Skip and Take
make no sense without ordering, LINQ to SQL will create an order for you,
which irritates me to no end, but that's a separate thread), they will
produce different results.

Say your query will produce the ordered set {1, 2, 3}.  Let n = 1, m =
2.

The first query:

var query = query.Skip(n).Take(m);

converted to SELECT ... OFFSET n LIMIT m

Will return the ordered set {2, 3}, while the second query:

var query = query.Take(m).Skip(n);

converted to SELECT ... LIMIT m OFFSET n

Will return the ordered set {2}.

The reason for this is that in the first query, the Skip method skips
one element, then takes the remaining two, while in the second query, the
first two elements are taken, and then the first one is skipped.

> 
>> If a limit count is given, no more than that many rows will be returned
>> (but possibly less, if the query itself yields less rows).
> ...
>> OFFSET says to skip that many rows before beginning to return rows.
>
> Why would the position of either clause matter, assuming the clause is in
> a legal position?
> In both your examples, the LIMIT is 'm', so you will get 'm' rows.
>
> In both your examples, the OFFSET is 'n', so you will skip 'n' rows before
> returning those 'm' rows.
>
> I see no inconsistency.

Different results - the first gives results (zero-based) n to n+m-1.
The second gives results 0 to Min(n-1, m-1).

> Also, neither LIMIT nor OFFSET is a binary operator, so the term
> "commutative" has to be understood metaphorically at best.  What exactly
> do you mean by "commutative"?

I meant result should depend on the order of OFFSET and LIMIT clauses are
present in SELECT clause.

SELECT ... OFFSET n LIMIT m
SELECT ... LIMIT m OFFSET n

should return different results in sime cases.

Filtering and ordering are effectively orthogonal. LIMIT and OFFSET are
clearly *not* orthogonal.

Otherwise I see no way to implement efficient LinQ-PostgreSQL driver.


Andrus.




---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Error while compiling PostgreSQL with Java

[Lew]

Никоноров Григорий wrote:

I have a problem. I try to install PostgreSQL from source with java. I
Installed JDK, ANT properly but when i try to configure i have an error -
Warning.Ignored options - --with-java. Please help me !


Kris Jurka wrote:
Starting with the 8.0 release the JDBC driver was made an external 
project instead of bundled with the server source code.  The JDBC driver 
is available from http://jdbc.postgresql.org/download.html


Cross-posted to correct the OP's multi-post, so that pgsql.admin will also 
contain the response(s).


--
Lew

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] indexing tables using my owns functions

[Pau Marc Munoz Torres]

Hi every body

 Recently i wrote my own function into postgesql... and it works perfecly!!,
now i would like use it to index a table like this guy do at the link (
http://www.faqs.org/docs/ppbook/r24254.htm)

but i get the following error

mhc2db=> create index h2iab on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));
ERROR:  functions in index expression must be marked IMMUTABLE
mhc2db=>

in this indexation p1,p4,p6,p7,p9 are variables that depends on the field.

could anyone help me?

--
Pau Marc Muñoz Torres

Laboratori de Biologia Computacional
Institut de  Biotecnologia   i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : [EMAIL PROTECTED]

[GENERAL] System Load analyze

[Peter Bauer]

Hi all,

i have a system here with 2 2.4GHz Xeon Processors, 2GB RAM, ONE Disk on
a Battery Backed Write Cache SCSI Controller and PostgreSQL 8.1.4
running with the data on a DRBD Device for High Availability. The used
database is also replicated to two similar machines with slony1.

Since the load average is between 1 (most of the time) and 10 (peeks) i
am worried about the load and executed vmstat and iostat which show that
1000-6000 Blocks are writen per second. Please check the attached output
for further details.
top shows that the CPUs are at least 80% idle most of the time so i
think there is an I/O bottleneck. I'm aware that this hardware setup is
probably not sufficient but is would like to investigate how critical
the situation is.

thanks,
Peter

procs ---memory-- ---swap-- -io --system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   incs us sy id wa
 0  0 398256  78328 140612 106355600 0  1472 2029  5081  4  3 92  0
 0  2 398256  78268 140612 106357600 0  2304 1928  4216  0  2 98  0
 1  2 398256  78100 140612 106357600 0  1464 1716  3994  1  3 96  0
 0  0 398256  78704 140612 106359200 0   916 1435  3570  5  3 91  0
 0  0 398256  77876 140612 106361600 0 0  305  1169  3  1 96  0
 0  1 398256  79200 140612 106362800 0  2404 2787  6555  7  6 86  0
 0  2 398256  79256 140612 106366000 0  1564 1640  3577  4  2 94  0
 0  0 398256  79324 140612 106369200 0  1660 2322  5615  5  2 93  0
 0  0 398256  78668 140612 106372400 0  1048 1390  4197 16 13 72  0
 1  0 398256  79344 140612 106374800 0  1896 2416  5675  5  5 89  0
 0  0 398256  79336 140612 106378400 0  1176 1720  4436  5  3 92  0
 1  2 398256  79280 140612 106381200 0  1800 1697  3931  3  3 94  0
 0  1 398256  79048 140612 106383200 0  1384 1733  4137  4  2 93  0
 0  1 398256  78792 140612 106386800 0  1672 2163  5241  3  2 94  0
 0  0 398256  77608 140612 106388000 0  1088 1638  3484  4  2 93  0
 0  0 398256  79108 140612 106388400 0  1568 2103  5382  7  5 88  0
 0  0 398256  79100 140612 106389200 0  1556 1394  3135  2  1 97  0
 0  2 398256  79084 140612 106390000 0  1644 2072  4953  2  0 97  0
 0  0 398256  79060 140612 106393200 0  1240 1714  3888  5  2 92  0
 0  2 398256  79032 140612 106394000 0  1328 1694  4135  4  2 94  0
 0  1 398256  78452 140612 106394400 0   620  925  2824  6  7 87  0
 0  0 398256  79036 140612 106395600 0  1196 1293  2954  6  7 87  0
 0  2 398256  79136 140612 106396400 0  1736 1959  4494  4  2 94  0
 0  0 398256  79132 140612 106396400 0 4  260  1039  1  1 98  0
 0  0 398256  79052 140612 106398000 0  2444 3084  6955  6  5 89  0
 0  2 398256  79060 140612 106398800 0   948 1146  3616  3  1 96  0
 0  1 398256  78268 140612 106405600 0  1908 1809  4086  6  5 88  0
 0  1 398256  76728 140612 106405600 0  6256 6637 15472  5  5 90  0
 0  2 398256  77000 140612 106406400 0  4916 5840 12107  1  4 95  0
 0  2 398256  76956 140612 106406800 0  6468 7432 15211  1  3 96  0
 0  6 398256  77388 140612 106407200 0  8116 7826 18265  1  8 91  0
 0  2 398256  74312 140612 106407600 0  7032 6886 16136  2  7 91  0
 0  2 398256  74264 140612 106407600 0  5680 7143 13411  0  5 95  0
 0  2 398256  72980 140612 106414000 0  5396 6377 13251  6  6 88  0
 0  3 398256  76972 140612 106414800 0  5652 6793 14079  4  9 87  0
 0  2 398256  77836 140612 106414800 0  3968 5321 14187 10  8 82  0
 1  0 398256  77280 140612 106414800 0  1608 3188  8974 21 12 67  0
 1  0 398256  77832 140612 106415200 0   236  834  2625  7  5 87  0
 0  0 398256  77464 140612 106415200 0   244  505  1378  2  4 94  0
 1  0 398256  77828 140612 106416400 0   316  580  1954  7  2 91  0
 0  0 398256  77804 140612 106418000 0   740  673  2248  2  2 96  0
 0  0 398256  77000 140612 106418000 0   304  589  1739  1  3 96  0
 0  0 398256  77000 140612 106418400 0 0  216   886  0  1 99  0
 0  0 398256  75452 140612 106418400 0   432  755  2032  6  1 93  0
 0  0 398256  76964 140616 106420000 0  1980 2722  6452  7  2 91  0
 0  0 398256  77040 140616 106421200 0  1524 1536  3635  5  1 93  0
 0  2 398256  77028 140616 106421600 0  1136 1489  3342  1  2 97  0
 0  0 398256  76976 140616 106423600 0  1392 1831  4468  3  2 94  0
 0  0 398256  76944 140616 106425600 0   932 1374  3318  7  8 85  0
 0  0 398256  76856 140616 106427600 0  1392 1773  3985  5  4 91  0
 1  4 398256  76536 140616 106444800 0  1432 1537  3950  4  

Re: [GENERAL] Why LIMIT and OFFSET are commutative

[Andrus]

> That's SQL, my friend.  OFFSET first, then LIMIT.  Irrespective of the 
> order
> in the query statement.  It is what it is.  SQL doesn't depend on LINQ for 
> its semantics.

SQL requires strong order of all cases. Postgres syntax help about OFFSET / 
LIMIT  also
describes only single order.

Actually Postgres allows order of OFFSET / LIMIT clauses swapped without any 
error.

Why Postgres does not throw error when SELECT ... LIMIT ... OFFSET is used ? 
That's not sql and should cause error.

Andrus. 



---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Help Needed

[Amit]

Hi,

Hope you all are doing gr8!

I need a help regarding PostgreSQL installation. I am using PostgreSQL for a 
web-based application using Tomcat. I want to install PostgreSQL 
automatically to user system(using some EXE) with a default database(which 
is required for my application to run). The main problem is how the default 
database can be created and restore of the data can be done automatically 
using some script or so. Any suggestion or idea are welcome.
Thanks in advance.
Regards,
Amit 

[GENERAL] Why LIMIT and OFFSET are commutative

[Andrus]

I found that

SELECT * FROM foo
ORDER BY bar
OFFSET n
LIMIT m

and

SELECT * FROM foo
ORDER BY bar
LIMIT m
OFFSET n

produce always same results.

Why ?
OFFSET and LIMIT operations are NOT commutative in general.

Andrus. 



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] Why LIMIT and OFFSET are commutative

[Lew]

Andrus wrote:

I found that

SELECT * FROM foo
ORDER BY bar
OFFSET n
LIMIT m

and

SELECT * FROM foo
ORDER BY bar
LIMIT m
OFFSET n

produce always same results.

Why ?
OFFSET and LIMIT operations are NOT commutative in general.


Under what interpretation would the results differ?


If a limit count is given, no more than that many rows will be returned 
(but possibly less, if the query itself yields less rows).

...
OFFSET says to skip that many rows before beginning to return rows. 


Why would the position of either clause matter, assuming the clause is in a 
legal position?


In both your examples, the LIMIT is 'm', so you will get 'm' rows.

In both your examples, the OFFSET is 'n', so you will skip 'n' rows before 
returning those 'm' rows.


I see no inconsistency.

Also, neither LIMIT nor OFFSET is a binary operator, so the term "commutative" 
has to be understood metaphorically at best.  What exactly do you mean by 
"commutative"?


--
Lew
This post contained three requests for answers.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] Why LIMIT and OFFSET are commutative

[Lew]

Andrus wrote:

Under what interpretation would the results differ?


Results must differ for easy creation of LinQ-PostgreSQL driver.
If results are always the same , PostgreSQL should not allow to use both
order of clauses.


Um, no, and your original post had nothing to do with LINQ.

My answer was only about SQL.

Your LINQ problems have to be solved in LINQ.


Nicholas explains:

   Assuming the ordering is the same on each of them (because Skip and Take
make no sense without ordering, LINQ to SQL will create an order for you,
which irritates me to no end, but that's a separate thread), they will
produce different results.


Irrelevant.  The SQL definitions of these clauses is the SQL definition.  LINQ 
is a separate issue.


Again, the *SQL* definition says, quoting the PG docs at

If both OFFSET and LIMIT appear, then OFFSET rows are skipped 
*before starting to count the LIMIT rows that are returned.*

(emphasis added)

That's SQL, my friend.  OFFSET first, then LIMIT.  Irrespective of the order 
in the query statement.  It is what it is.  SQL doesn't depend on LINQ for its 
semantics.


--
Lew

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[GENERAL] Non-unique values problem after 'add column'

[dima . kagan]

Hello!

I've encountered a very strange problem with Postgresql 7.3.
I have the following set of queries running in transaction, using
libpqxx 2.6.9:

ALTER TABLE my_table ADD new_column TEXT
UPDATE my_table SET new_column = 'disable'
ALTER TABLE my_table ALTER COLUMN new_column SET DEFAULT 'disable'
CREATE UNIQUE INDEX my_index ON my_table (old_column)

The last query fails with:
ERROR:  Cannot create unique index. Table contains non-unique values

Running the same set of queries manually causes no problems. Moreover,
running the last query in a separate transaction works as well.

Why is a new column affecting unique index creation on an already
existing one? Is there any workaround for this issue?

Thank you,
Dima Kagan

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] Why LIMIT and OFFSET are commutative

[Lew]

Andrus wrote:
Why Postgres does not throw error when SELECT ... LIMIT ... OFFSET is used ? 
That's not sql and should cause error.


Good point, it is not standard SQL.  Postgres varies from the standard in 
several ways, some of them useful.  (INSERT ... RETURNING is a useful one.) 
No SQL RDBMS follows the standard, AFAIK.  LIMIT / OFFSET is a common 
enhancement, but being non-standard, appears in different forms in different 
dialects.


However, the LIMIT / OFFSET idiom *is* Postgres SQL, it *is* documented and 
therefore it should *not* cause an error when used.  That would just be whacky.


Given that the idiom is documented and does work in Postgres's particular 
dialect of SQL, it then must work exactly as described in Postgres's 
documentation of its particular dialect of SQL.  And, hey, presto!  It does.


I agree that they should document the extra flexibility it provides in the 
order of its clauses.  I find it hard to assess such flexibility as a bad 
thing; I tend to appreciate it.


They do hint at it in one place - Section VI, Chapter I, _SELECT_, the LIMIT 
clause,



 The LIMIT clause consists of two independent sub-clauses:

LIMIT { /count/ | ALL }
OFFSET /start/

/count/ specifies the maximum number of rows to return, 
while /start/ specifies the number of rows to skip 
before starting to return rows. When both are specified, 
/start/ rows are skipped before starting to count the 
/count/ rows to be returned. 


The word "independent" indicates at least the either may appear without the 
other, and implies that the order of the clauses doesn't matter.


Note also that the behavior of the clause is *exactly* as documented.  You 
really have to stop resisting that, now, and accept it.


--
Lew

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] \copy ... with null as '' csv doesn't get nulls

[Lew]

Ivan Sergio Borgonovo wrote:

I'd expect this:

\copy tablename from 'filename.csv' WITH NULL as '' CSV HEADER

whould import "" as NULL.

The input file is UTF-8 (not Unicode/UTF-16).
I checked the hexdump and the "wannabe" NULL are actually
2c 22 22 2c -> ,"",

all fields are varchar that admit NULL

but still I get empty strings ('') in spite of NULL.

Am I missing something or it is a well known "feature"?


I went to the docs for COPY and they say,

The default is \N (backslash-N) in text mode, and a empty value with no quotes 
in CSV mode.


That "with no quotes" phrase caught my attention.

Try eliminating the double quotes in the CSV file.  "Wannabe" NULL would then be
  ,, (consecutive commas)
in the CSV.  From the docs, you don't even need the "NULL AS" clause in your 
COPY statement.


--
Lew

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [GENERAL] Error while compiling PostgreSQL with Java

[Lew]

Никоноров Григорий wrote:
Thanks for your answer.When i [sic] download this "JDBC4 Postgresql Driver, 
Version 8.2-506" driver what should i do next to configure PostgreSQL 
with it ?


Place the JAR containing the driver in the classpath for your Java 
application.  In the Java program be sure to load the org.postgresql.Driver 
class and establish the connection to the database via a URL such as 
jdbc:postgresql://yourhost:5432/yourdb.  (JNDI is another approach which does 
these steps via a declarative approach.)


If you had read any of the PG JDBC documentation

you might have found instructions there.  Chapters 2 and 3 of the referenced 
link (from the very same site from which you got the driver) go into some detail.


 has some excellent information about JDBC in general.


SunIYF, PGIYF (in this case, jdbc.postgresql.org IYF) and GIYF.

--
Lew

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Thomas Chille]

On Nov 27, 2007 4:52 PM, Tom Lane <[EMAIL PROTECTED]> wrote:
> You didn't happen to note what 9293 was doing did you?  It's living
> fairly dangerously in any case by trying to acquire exclusive lock
> when it already holds a bunch of other lower-level locks; that's a
> recipe for deadlock if I ever saw one.
>
> regards, tom lane
>

Ah ok, 9293 is a triggerd process and tries to "ALTER TABLE ...
DISABLE TRIGGER (other trigger)" and so implicitly tries to acquire an
AccessExclusiveLock and runs in a deadlock?

So is better not to use "ALTER TABLE ... " in triggerfunctions,
because there are always existing lower-level locks?

regards,
thomas

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] ERROR: invalid restriction selectivity: 224359728.000000

[xeb]

> > error message in log:
> > ERROR:  invalid restriction selectivity: 0.49
>
> [ blink... ]  Surely it didn't really say that, because a moment's
> glance at the code shows that it's impossible:
>
>   if (result < 0.0 || result > 1.0)
>   elog(ERROR, "invalid restriction selectivity: %f", result);
>
> If it really did say that, then you have either seriously flaky hardware
> or a broken compiler.  In either case, us mere database weenies can't
> help much.
>
>   regards, tom lane

Really, it seems that is hardware trouble, beacause it works on virtual 
machine (KVM), which is buggy enough :( 
Thanks.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Thomas Chille]

yes, u are right.

this are the 3 involved indexes:

hst_timerecording_business_day_idx on hst_timerecording
hst_timerecording_id_employee_idxon hst_timerecording
hst_timerecording_id_timerecording_idxon hst_timerecording

lg t

On Nov 27, 2007 4:07 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> Alvaro Herrera wrote:
> > Thomas Chille wrote:
> > > On Nov 27, 2007 3:14 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > > hat are the column headings?  I find this difficult to read.
> > > >
> > > > Please post the whole of pg_locks.  I may be missing something but I
> > > > think we're missing part of the picture here.  Autovacuum does not seem
> > > > to be locking on anything.
> > >
> > > Unfortunately i logged the pg_locks-state not well formated. i added
> > > now the heading manually. maybe it is better or i have to wait till
> > > tomorrow morning.
> > >
> > > but what i think i can see, is that the process with pid 9317 is
> > > holding a ShareUpdateExclusiveLock
> >
> > You missed that 9317 is also holding an ExclusiveLock.
>
> Sorry, I misread it.  My analysis is bogus :-)  I suggest you upgrade
> anyway because there are nasty bugs in the version you are using.
>
> I assume 77862554, 77862552 and 77862553 are indexes on the 75686189
> table?
>
> --
> Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
>
> "La experiencia nos dice que el hombre peló millones de veces las patatas,
> pero era forzoso admitir la posibilidad de que en un caso entre millones,
> las patatas pelarían al hombre" (Ijon Tichy)
>

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Connection idle broken

[Scott Marlowe]

On Nov 27, 2007 8:33 AM, Fernando Xavier <[EMAIL PROTECTED]> wrote:
> Hi,
>
> I have trouble with my  java application. Since i change the network 
> configuration, the postgresql idle connections broken after 10 minutes. (i 
> set authentication_timeout = 600 in postgresql.conf).
>
> My network:
>
> 192.168.1.1 (postgresql server and gateway server)
> 192.168.0.1 (linksys wireless router)
> 192.168.0.x (clients)
>
> How i make my idle connections alive for long time?

You can try lowering your tcp_keepalive settings.

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Alvaro Herrera]

Alvaro Herrera wrote:
> Thomas Chille wrote:
> > On Nov 27, 2007 3:14 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> > hat are the column headings?  I find this difficult to read.
> > >
> > > Please post the whole of pg_locks.  I may be missing something but I
> > > think we're missing part of the picture here.  Autovacuum does not seem
> > > to be locking on anything.
> > 
> > Unfortunately i logged the pg_locks-state not well formated. i added
> > now the heading manually. maybe it is better or i have to wait till
> > tomorrow morning.
> > 
> > but what i think i can see, is that the process with pid 9317 is
> > holding a ShareUpdateExclusiveLock
> 
> You missed that 9317 is also holding an ExclusiveLock.

Sorry, I misread it.  My analysis is bogus :-)  I suggest you upgrade
anyway because there are nasty bugs in the version you are using.

I assume 77862554, 77862552 and 77862553 are indexes on the 75686189
table?

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Alvaro Herrera]

Thomas Chille wrote:
> On Nov 27, 2007 3:14 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
> hat are the column headings?  I find this difficult to read.
> >
> > Please post the whole of pg_locks.  I may be missing something but I
> > think we're missing part of the picture here.  Autovacuum does not seem
> > to be locking on anything.
> 
> Unfortunately i logged the pg_locks-state not well formated. i added
> now the heading manually. maybe it is better or i have to wait till
> tomorrow morning.
> 
> but what i think i can see, is that the process with pid 9317 is
> holding a ShareUpdateExclusiveLock

You missed that 9317 is also holding an ExclusiveLock.  What are the
vacuum_cost_delay and autovacuum_vacuum_cost_delay setting?  I think
what's happening here is that you're being bitten by the bug that made
autovac sleep because of vacuum_delay, with the exclusive lock held
trying to truncate the table.

The solution is to upgrade to 8.1.10.

Also I just noticed you're on 8.1.4.  This is a bad idea because of
another ancient autovacuum bug.  As soon as you upgrade, allow
connections to template0 (update pg_database, set datallowconn), connect
to it and issue VACUUM FREEZE.  Then disallow connections to it again.

-- 
Alvaro Herrera   http://www.PlanetPostgreSQL.org/
"La experiencia nos dice que el hombre peló millones de veces las patatas,
pero era forzoso admitir la posibilidad de que en un caso entre millones,
las patatas pelarían al hombre" (Ijon Tichy)

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

Re: [GENERAL] Connection idle broken

[Douglas McNaught]

On 11/27/07, Fernando Xavier <[EMAIL PROTECTED]> wrote:
>
> Hi,
>
> I have trouble with my  java application. Since i change the network
> configuration, the postgresql idle connections broken after 10 minutes. (i
> set authentication_timeout = 600 in postgresql.conf).
>
> My network:
>
> 192.168.1.1 (postgresql server and gateway server)
> 192.168.0.1 (linksys wireless router)
> 192.168.0.x (clients)
>
> How i make my idle connections alive for long time?


Is the Linksys doing NAT?  Can you configure it with a longer timeout on
inactive connections?

-Doug




Any idea?
>
> Regards,
>
> Fernando
>
>
>   Abra sua conta no Yahoo! Mail, o único sem limite de espaço para
> armazenamento!
> http://br.mail.yahoo.com/
>
> ---(end of broadcast)---
> TIP 2: Don't 'kill -9' the postmaster
>

[GENERAL] postgres 8.3 beta 2 storage question

[SHARMILA JOTHIRAJAH]

Hi,

1.How does postgres version 8.3 betat 2 handle varchar and numeric data types 
in terms of storage 
I understand for varchar it has 1byte overhead (instead of 4) if length<128
How does it handle for numeric? The manual says
"The actual storage  requirement is two bytes for each group of four 
decimal digits,  plus five to eight bytes overhead."  
I dont understand how the overhead is measured here?...5 to 8 bytes?

2. Does postgres always inserts a complete record into a page unlike oracle 
whose record can span multiple pages? Does this waste space too? 

Thanks
sharmila





  

Be a better sports nut!  Let your teams follow you 
with Yahoo Mobile. Try it now.  
http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Thomas Chille]

On Nov 27, 2007 3:14 PM, Alvaro Herrera <[EMAIL PROTECTED]> wrote:
hat are the column headings?  I find this difficult to read.
>
> Please post the whole of pg_locks.  I may be missing something but I
> think we're missing part of the picture here.  Autovacuum does not seem
> to be locking on anything.

Unfortunately i logged the pg_locks-state not well formated. i added
now the heading manually. maybe it is better or i have to wait till
tomorrow morning.

but what i think i can see, is that the process with pid 9317 is
holding a ShareUpdateExclusiveLock

locktype   |  database|  relation   |   transaction |  pid
|  mode |  granted
relation|  75685778| 75686189   |9009323|   9317   |
ShareUpdateExclusiveLock | t

and process with pid 9293 is waiting to get AccessExclusiveLock on the
same relation:

relation|  75685778| 75686189   |9009312|   9293   |
AccessShareLock |  f

... and 9317 is the autvacuumprocess?

afterkiling it, erything is fine.

i attached the full pg_lock.

regards,
thomas
locktype  | database  | relation |transactionid | classid | 
objid | objsubid | transaction |  pid  |  mode   | granted 

relation7568577875686652
9009312 9293AccessShareLock t
transactionid   9017862 
9017862 25467   ExclusiveLock   t
relation7568577877862554
9009312 9293RowExclusiveLockt
relation7568577875686561
9009312 9293AccessShareLock t
relation7568577875686037
9012978 28370   AccessShareLock t
relation7568577875686037
9012978 28370   RowExclusiveLockt
relation7568577875686371
9012978 28370   AccessShareLock t
relation7568577875686371
9012978 28370   RowExclusiveLockt
relation7568577875686671
9009312 9293AccessShareLock t
relation7568577875686189
9017862 25467   AccessShareLock f
relation7568577875686044
9009312 9293AccessShareLock t
relation7568577875686138
9009312 9293AccessShareLock t
relation7568577875686138
9009312 9293RowExclusiveLockt
relation7568577875685984
9017862 25467   AccessShareLock t
relation7568577875686133
9009312 9293AccessShareLock t
relation7568577875686652
9012978 28370   AccessShareLock t
transactionid   9018078 
9018078 10605   ExclusiveLock   t
relation7568577875685979
9009312 9293AccessShareLock t
relation7568577875686031
9012978 28370   AccessShareLock t
relation7568577875686031
9012978 28370   RowExclusiveLockt
relation7568577875686011
9009312 9293AccessShareLock t
relation7568577875686176
9009312 9293AccessShareLock t
relation7568577877862554
9009323 9317ShareUpdateExclusiveLockt
relation7568577875685833
9012978 28370   AccessShareLock t
relation7568577875686189
9009323 9317ShareUpdateExclusiveLockt
relation7568577875686727
9009312 9293AccessShareLock t
relation7568577875686199
9017862 25467   AccessShareLock t
relation7568577877862552
9009312 9293AccessShareLock t
relation7568577877862552

[GENERAL] Connection idle broken

[Fernando Xavier]

Hi, 

I have trouble with my  java application. Since i change the network 
configuration, the postgresql idle connections broken after 10 minutes. (i set 
authentication_timeout = 600 in postgresql.conf).

My network:

192.168.1.1 (postgresql server and gateway server)
192.168.0.1 (linksys wireless router)
192.168.0.x (clients)

How i make my idle connections alive for long time?

Any idea?
 
Regards,
 
Fernando


  Abra sua conta no Yahoo! Mail, o único sem limite de espaço para 
armazenamento!
http://br.mail.yahoo.com/

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Alvaro Herrera]

Thomas Chille wrote:

> I think this are the relevant pg_locks entries:
> 
> relation7568577875686189
>  9017862 25467   AccessShareLock f
> relation7568577875686189
>  9009323 9317ShareUpdateExclusiveLock
>   t
> relation7568577875686189
>  9009312 9293AccessShareLock t
> relation7568577875686189
>  9009312 9293RowExclusiveLockt
> relation7568577875686189
>  9009312 9293AccessExclusiveLock f
> relation7568577875686189
>  9012978 28370   AccessShareLock f
> 
> 75686189 is the table hst_timerecording. for me it looks like the
> autovacuum is not releasing the blocking ShareUpdateExclusiveLock?

What are the column headings?  I find this difficult to read.

Please post the whole of pg_locks.  I may be missing something but I
think we're missing part of the picture here.  Autovacuum does not seem
to be locking on anything.

-- 
Alvaro Herrera http://www.amazon.com/gp/registry/CTMLCN8V17R4
One man's impedance mismatch is another man's layer of abstraction.
(Lincoln Yeoh)

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] autovacuum process blocks without reporting a deadlock

[Thomas Chille]

On Nov 24, 2007 6:20 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> What other indexes does that table have?
>
> regards, tom lane
>

Hi,

last night it happend again. In the log-snippet u can see all indexes
of this table:

[9293 / 2007-11-26 21:46:28 CET]CONTEXT:  SQL statement "UPDATE
hst_timerecording SET id_timerecording_join = NULL WHERE
id_timerecording_join = -1"
PL/pgSQL function "set_id_timerecording_join" line 121 at SQL statement
SQL statement "UPDATE hst_timerecording SET sales_volume =
NULL, sales_volume_commission = NULL WHERE business_day =  $1  AND
id_employee =  $2 "
PL/pgSQL function "compress_salaries_day" line 168 at SQL statement
SQL statement "SELECT  compress_salaries_day( $1 , NULL, NULL)"
PL/pgSQL function "compress" line 460 at perform
[9293 / 2007-11-26 21:46:28 CET]LOCATION:  exec_stmt_raise, pl_exec.c:2110
[9317 / 2007-11-26 21:46:34 CET]DEBUG:  0: index
"hst_timerecording_business_day_idx" now contains 8640 row versions in
80 pages
[9317 / 2007-11-26 21:46:34 CET]DETAIL:  4469 index row versions were removed.
13 index pages have been deleted, 9 are currently reusable.
CPU 0.00s/0.00u sec elapsed 3.40 sec.
[9317 / 2007-11-26 21:46:34 CET]LOCATION:  lazy_vacuum_index, vacuumlazy.c:736
[9317 / 2007-11-26 21:46:39 CET]DEBUG:  0: index
"hst_timerecording_id_employee_idx" now contains 8640 row versions in
95 pages
[9317 / 2007-11-26 21:46:39 CET]DETAIL:  4469 index row versions were removed.
10 index pages have been deleted, 6 are currently reusable.
CPU 0.00s/0.00u sec elapsed 4.22 sec.
[9317 / 2007-11-26 21:46:39 CET]LOCATION:  lazy_vacuum_index, vacuumlazy.c:736
[9317 / 2007-11-26 21:46:43 CET]DEBUG:  0: index
"hst_timerecording_id_timerecording_idx" now contains 8640 row
versions in 97 pages
[9317 / 2007-11-26 21:46:43 CET]DETAIL:  4469 index row versions were removed.
11 index pages have been deleted, 5 are currently reusable.
CPU 0.00s/0.00u sec elapsed 4.40 sec.
[9317 / 2007-11-26 21:46:43 CET]LOCATION:  lazy_vacuum_index, vacuumlazy.c:736

This are again the last log-entries of the blocking processes.
Obviously the three indexes where successful vacuumed?

I think this are the relevant pg_locks entries:

relation7568577875686189
 9017862 25467   AccessShareLock f
relation7568577875686189
 9009323 9317ShareUpdateExclusiveLock
  t
relation7568577875686189
 9009312 9293AccessShareLock t
relation7568577875686189
 9009312 9293RowExclusiveLockt
relation7568577875686189
 9009312 9293AccessExclusiveLock f
relation7568577875686189
 9012978 28370   AccessShareLock f

75686189 is the table hst_timerecording. for me it looks like the
autovacuum is not releasing the blocking ShareUpdateExclusiveLock?

I hope this infos could help.

regards, t
thomas

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[GENERAL] "Invalid Pointer Operation" while reading tuples

[claudia . amorim]

Hello,

I'm receiving an error message "Invalid pointer operation" when I'm reading data
from my database.
I'm using PostGreSQL 8.2.5 with Windows 2003 Server Enterprise Edition.
When I saw the psqlodbc log I found teh messages below:


[11.281]CONN ERROR: func=PGAPI_SetConnectOption, desc='', errnum=206,
errmsg='Illegal parameter value for SQL_TXN_ISOLATION'


[0.250]STATEMENT ERROR: func=set_statement_option, desc='', errnum=30,
errmsg='The option may be for MS SQL Server(Set)'


Some parts of the log:

[11.281]conn=02FB5D98, query='COMMIT'
[11.281]CONN ERROR: func=PGAPI_SetConnectOption, desc='', errnum=206,
errmsg='Illegal parameter value for SQL_TXN_ISOLATION'
[11.281]
[11.281]henv=02FB2F58, conn=02FB5D98, status=1, num_stmts=16
[11.281]sock=02FA3178, stmts=02DEBD38, lobj_type=17288
[11.281] Socket Info ---
[11.281]socket=724, reverse=0, errornumber=0, errormsg='(NULL)'
[11.281]buffer_in=48140048, buffer_out=48153296
[11.281]buffer_filled_in=18, buffer_filled_out=0, buffer_read_in=18





[0.250]conn=02DE3A70, query='close "SQL_CUR02FA8CA8"'
[0.250]conn=02DE3A70, query='RELEASE _per_query_svp_'
[0.250]conn=02DE3A70, query='close "SQL_CUR02DE85C8"'
[0.250]conn=02DE3A70, query='RELEASE _per_query_svp_'
[0.250]conn=02DE3A70, query='COMMIT'
[0.250]STATEMENT ERROR: func=set_statement_option, desc='', errnum=30,
errmsg='The option may be for MS SQL Server(Set)'
[0.250]

[0.250] hdbc=02DE3A70, stmt=02DEABE8, result=
[0.250] prepare=0, internal=0
[0.250] bindings=, bindings_allocated=0
[0.250] parameters=, parameters_allocated=0
[0.250] statement_type=-2, statement='(NULL)'
[0.250] stmt_with_params='(NULL)'
[0.250] data_at_exec=-1, current_exec_param=-1, put_data=0
[0.250] currTuple=-1, current_col=-1, lobj_fd=-1
[0.250] maxRows=0, rowset_size=1, keyset_size=0, cursor_type=0,
scroll_concurrency=1
[0.250] cursor_name=''
[0.250] QResult Info
---
[0.250]CONN ERROR: func=set_statement_option, desc='', errnum=0, errmsg='(NULL)'



Can you help me?


Thanks,
Cláudia.






---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/

Re: [GENERAL] select using an index

[Zoltan Boszormenyi]

Pau Marc Munoz Torres írta:

Hi

 Recently i created an index in a table using a function (not a 
column) as following


create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,

where idr is a function that returns a real number,

as a result i got the following table

mhc2db=> \d precalc;
Table "public.precalc"
 Column | Type|  Modifiers
-+---+-- 

 id  | integer | not null default 
nextval('precalc_id_seq'::regclass)

 p1 | character(1)  |
 p4 | character(1)  |
 p6 | character(1)  |
 p7 | character(1)  |
 p9 | character(1)  |
Indexes:
"h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),

now, i would like to perform a query using this index, something like

 
Select * from precalc where h2iab>2


but obviously h2iab  is not a column...

some of you knows what i should do?

thanks


Formulate your WHERE clause so it matches
your index's expression. E.g.

SELECT * FROM precalc WHERE
idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying) > 2;

Best regards,
Zoltán Böszörményi


--
--
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org/

[GENERAL] select using an index

[Pau Marc Munoz Torres]

Hi

 Recently i created an index in a table using a function (not a column) as
following

create index H2IAb on precalc (idr(p1,p4,p6,p7,p9,'H-2*IAb'));,

where idr is a function that returns a real number,

as a result i got the following table

mhc2db=> \d precalc;
Table "public.precalc"
 Column | Type|  Modifiers
-+---+--

 id  | integer | not null default
nextval('precalc_id_seq'::regclass)
 p1 | character(1)  |
 p4 | character(1)  |
 p6 | character(1)  |
 p7 | character(1)  |
 p9 | character(1)  |
Indexes:
"h2iab" btree (idr(p1, p4, p6, p7, p9, 'H-2*IAb'::character varying)),

now, i would like to perform a query using this index, something like


Select * from precalc where h2iab>2

but obviously h2iab  is not a column...

some of you knows what i should do?

thanks

-- 
Pau Marc Muñoz Torres
Laboratori de Biologia Computacional
Institut de  Biotecnologia   i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : [EMAIL PROTECTED]

-- 
Pau Marc Muñoz Torres

Laboratori de Biologia Computacional
Institut de  Biotecnologia   i Biomedicina Vicent
Villar
Universitat Autonoma de Barcelona
E-08193 Bellaterra (Barcelona)

telèfon: 93 5812807
Email : [EMAIL PROTECTED]

Re: [GENERAL] Linux v.s. Mac OS-X Performance

[Wolfgang Keller]

Hello,

sorry for "butting in", but I'm just curious...


resolution?

http://archives.postgresql.org/pgsql-general/2007-11/msg00946.php

conclusion?

Mac was still pretty slow in comparison


Anyway, how does MacOS X (both 10.4 and 10.5) compare to Windows (2000, 
XP, Vista etc.) on the same hardware?


And Linux to (Free-/Net-/whatever) BSD?

No flamebait, I'm just wondering whether the performance gain is worth 
the learning effort required for Linux or BSD compared to the Mac.


Sincerely,

Wolfgang Keller 


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq

Re: [GENERAL] Trigger problem

[Christian Rengstl]

Thanks for pinpointing that out. After including your solution it shows
at least something like "UPDATE OF  with id: 123456567". Unfortunately
after using coalesce, too, when building the field variable like: field
= 'field gruppe from: ' || coalesce(OLD.gruppe, '') || ' TO ' ||
coalesce(NEW.gruppe, ''); there still seem to be null values. Of course,
it can be that the user deletes a value and then there is a NULL value,
say for NEW.gruppe for example, but shouldn't the coalesce in this case
avoid that the whole field-string is empty?


Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




>>> On Tue, Nov 27, 2007 at 10:53 AM, in message
<[EMAIL PROTECTED]>, "A. Kretschmer"
<[EMAIL PROTECTED]> wrote: 
> am  Tue, dem 27.11.2007, um 10:38:09 +0100 mailte Christian Rengstl 
> folgendes:
>> Hi list,
>> 
>>  act = 'DELETION of row with id: ' || OLD.id;
>>   act = 'UPDATE OF ' || field || ' with id: ' ||
>> ...
>> INSERT INTO history(aennam, action, table_name)
VALUES(current_user,
>> act, tab);
>> 
>> Now the problem is that a tuple gets added to the table history,
but
>> the field "action" (whatever the user did) is 99% empty, whereas
the
>> others are filled and I don't see why...
>> 
>> Any hint is greatly appreciated
> 
> Maybe sometime the concateneted fields (e.g. field, OLD.id) contains
> NULL- values. If so, the complete string 'act' will be NULL.
> 
> Solution: use coalesce(field,''). Hope that helps.
> 
> 
> Andreas


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [GENERAL] Trigger problem

[A. Kretschmer]

am  Tue, dem 27.11.2007, um 10:38:09 +0100 mailte Christian Rengstl folgendes:
> Hi list,
> 
>   act = 'DELETION of row with id: ' || OLD.id;
>   act = 'UPDATE OF ' || field || ' with id: ' ||
> ...
> INSERT INTO history(aennam, action, table_name) VALUES(current_user,
> act, tab);
> 
> Now the problem is that a tuple gets added to the table history, but
> the field "action" (whatever the user did) is 99% empty, whereas the
> others are filled and I don't see why...
> 
> Any hint is greatly appreciated

Maybe sometime the concateneted fields (e.g. field, OLD.id) contains
NULL-values. If so, the complete string 'act' will be NULL.

Solution: use coalesce(field,''). Hope that helps.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(end of broadcast)---
TIP 6: explain analyze is your friend

[GENERAL] Trigger problem

[Christian Rengstl]

Hi list,

I have a trigger to log what the users do on the database:


DECLARE _query VARCHAR;
DECLARE valid BOOL;
DECLARE act VARCHAR;
DECLARE tab VARCHAR;
DECLARE field VARCHAR;
BEGIN

IF(TG_OP = 'DELETE') THEN
act = 'DELETION of row with id: ' || OLD.id;
ELSIF(TG_OP = 'UPDATE') THEN
IF NEW.id<>OLD.id THEN
field = 'id from: ' || OLD.id || ' TO ' || NEW.id;
ELSIF NEW.entry_no<>OLD.entry_no THEN
RAISE EXCEPTION 'Die generische Seriennummer kann von Ihnen
nicht
verändert werden!';
RETURN NULL;
ELSIF NEW.gruppe<>OLD.gruppe THEN
valid = NEW.gruppe > 0 AND NEW.gruppe<>999 AND NEW.gruppe IS NOT
NULL;

field = 'field gruppe from: ' || OLD.gruppe || ' TO ' ||
NEW.gruppe;

ELSIF NEW.kombi<>OLD.kombi THEN
valid = (NEW.kombi>=0) AND (NEW.kombi<=2);

field = 'field kombi from: ' || OLD.kombi || ' TO ' ||
NEW.kombi;

END IF;
  act = 'UPDATE OF ' || field || ' with id: ' ||
OLD.id;
END IF;

tab = TG_TABLE_SCHEMA || '.' || TG_TABLE_NAME;
INSERT INTO history(aennam, action, table_name) VALUES(current_user,
act, tab);
RETURN NULL;
END;

Now the problem is that a tuple gets added to the table history, but
the field "action" (whatever the user did) is 99% empty, whereas the
others are filled and I don't see why...

Any hint is greatly appreciated



Christian Rengstl M.A.
Klinik und Poliklinik für Innere Medizin II
Kardiologie - Forschung
Universitätsklinikum Regensburg
B3 1.388
Franz-Josef-Strauss-Allee 11
93053 Regensburg
Tel.: +49-941-944-7230




---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq

[GENERAL] cp: cannot stat `/mnt/server/archivedir/00000001.history': No such file or directory

[Ashish Karalkar]

Hello List Member,

I am trying to setup recovery from WAL, recovery is done properly but there are 
some alerting messages in the log file 

log file :
*
2007-11-27 14:08:04 IST   LOG:  database system was interrupted at 2007-11-27 
13:59:44 IST
2007-11-27 14:08:04 IST   LOG:  starting archive recovery
2007-11-27 14:08:04 IST   LOG:  restore_command = "cp /mnt/server/archivedir/%f 
%p"
cp: cannot stat `/mnt/server/archivedir/0001.history': No such file or 
directory
2007-11-27 14:08:04 IST   LOG:  restored log file 
"00010053.0020.backup" from archive
2007-11-27 14:08:05 IST   LOG:  restored log file "00010053" 
from archive
2007-11-27 14:08:05 IST   LOG:  checkpoint record is at 0/5320
2007-11-27 14:08:05 IST   LOG:  redo record is at 0/5320; undo record is at 
0/0; shutdown FALSE
2007-11-27 14:08:05 IST   LOG:  next transaction ID: 0/13326; next OID: 69934
2007-11-27 14:08:05 IST   LOG:  next MultiXactId: 1; next MultiXactOffset: 0
2007-11-27 14:08:05 IST   LOG:  automatic recovery in progress
2007-11-27 14:08:05 IST   LOG:  redo starts at 0/5370
2007-11-27 14:08:06 IST   LOG:  restored log file "00010054" 
from archive
2007-11-27 14:08:06 IST   LOG:  restored log file "00010055" 
from archive
2007-11-27 14:08:07 IST   LOG:  restored log file "00010056" 
from archive
cp: cannot stat `/mnt/server/archivedir/00010057': No such file 
or directory
2007-11-27 14:08:07 IST   LOG:  record with zero length at 0/5770
2007-11-27 14:08:07 IST   LOG:  redo done at 0/5720
cp: cannot stat `/mnt/server/archivedir/00010057': No such file 
or directory
2007-11-27 14:08:07 IST   LOG:  archive recovery complete
2007-11-27 14:08:08 IST   LOG:  database system is ready
2007-11-27 14:08:18 IST   LOG:  archived transaction log file 
"00010057"
**

from above log it seems that server is looking for 0001.history which is 
not there , but this file is not created by the server insted it created 
0001.backup (which document also suggest is the history file)

Should i ignore this message?

Secondly,
output from 0001.backup file:
**
START WAL LOCATION: 0/5320 (file 00010053)
STOP WAL LOCATION: 0/5400 (file 00010054)
CHECKPOINT LOCATION: 0/5320
START TIME: 2007-11-27 13:59:44 IST
LABEL: /mnt/server/base_backup/20071127
STOP TIME: 2007-11-27 14:00:09 IST
***
last WAL when server was shut down was 00010059
and file upto 00010056 where archived.

I copied WAL 00010057,00010058 
,00010059 to another location.

removed the data directory and restored the same from base backup, cleaned the 
pg_xlog and kept WAL 00010057,00010058 
,00010059 in it.

Now from log it can be seen warning for WAL 00010057

Can anybody please explain me wht is going wrong?

JUST for note:recover was complete.

Thanks in advance
With regards
Ashish...






















   
-
 Find a mall, hotel or ATM in any part of India. Click here.