[GENERAL] arrays in where

[Tom Allison]

I have a table

select * from history;
idx |   tokens
-+-
   2 | {10633,10634,10636}


And the values in the tokens field are taken from sequence values  
from another table.


Can I use this kind of storage to identify all the tokens in the  
first table that make these token_idx values?


Normally I would store these under a table with a structure like:
2 | 10633
2 | 10634
2 | 10636
as a "join table"

and simply join all these rows together.
but I'm curious if I can use the ARRAY and if it might save me some  
performance because this join table would also have a lot of  
redundant data...


---(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

[GENERAL] prepare()

[Tom Allison]

Is there an advantage to using something like $dbh->prepare($sql) if  
the SQL is going to be run once within the scope of the code?  The  
code block may be run many times in a minute as in a function call ---


while (<>) {
   insert_something($_);
}

Will the prepare statement be cached @ the database even if it's  
destroyed in the code/application?


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

[GENERAL] removing a restriction

[Tom Allison]

I have a column that was created with a 'not null' restriction.
How do I remove it?



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

  http://archives.postgresql.org/

Re: [GENERAL] removing a restriction

[Tom Allison]

alter TABLE user_history ALTER COLUMN seen_as set not null;

turns it on...
I want to turn it off

On May 30, 2007, at 9:05 PM, Joshua D. Drake wrote:



Tom Allison wrote:

I have a column that was created with a 'not null' restriction.
How do I remove it?


alter table set

---(end of  
broadcast)---

TIP 4: Have you searched our list archives?
  http://archives.postgresql.org/



--

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/ 
donate

PostgreSQL Replication: http://www.commandprompt.com/products/


---(end of  
broadcast)---

TIP 5: don't forget to increase your free space map settings



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

[GENERAL] left outer join and values()

[Tom Allison]

I did something like this with a single VALUES statment [eg:  VALUES 
((2),(3))]

and thought I could extend this to two columns
But I'm not having any luck.

BTW - history_idx is an integer and token_idx is a bigint.

select v.history.idx, v.token_idx
from (
values ((3,1),(3,2))) as v(history_idx, token_idx)
left outer join history_token ht on v.history_idx = ht.history_idx
and v.token_idx = ht.token_idx
where ht.history_idx is null;
ERROR:  operator does not exist: record = integer
LINE 4: left outer join history_token ht on v.history_idx = ht.histo...
  ^
HINT:  No operator matches the given name and argument type(s). You  
may need to add explicit type casts.


NOTE: the '^' is pointing to the '=' on Line 4

---(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] left outer join and values()

[Tom Allison]

Thank you for the response.
I did figure this out a few minutes after I sent this post.   
Apologies for jumping the gun.


I must say, I am absolutely impressed with what pgsql's  
implimentation of VALUES allows me to do.

It's kind of ridiculous how much "work" goes away in my code.
Too bad I can't do this at work (Oracle 8/9).

On May 31, 2007, at 11:48 PM, Tom Lane wrote:



Tom Allison <[EMAIL PROTECTED]> writes:

select v.history.idx, v.token_idx
from (
values ((3,1),(3,2))) as v(history_idx, token_idx)
left outer join history_token ht on v.history_idx = ht.history_idx
and v.token_idx = ht.token_idx
where ht.history_idx is null;
ERROR:  operator does not exist: record = integer
LINE 4: left outer join history_token ht on v.history_idx =  
ht.histo...

   ^


You've got too many parentheses --- the system thinks that "values"
specification is a single row containing two fields that are each
two-column records.  I think you want

select v.history_idx, v.token_idx
from (
values (3,1),(3,2)) as v(history_idx, token_idx)
left outer join history_token ht on v.history_idx = ht.history_idx
and v.token_idx = ht.token_idx
where ht.history_idx is null;

Note the "history.idx" typo as well.

regards, tom lane

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster



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

[GENERAL] When should I worry?

[Tom Allison]

I've started a database that's doing wonderfully and I'm watching the tables 
grow and a steady clip.


Performance is great, indexes are nice, sql costs are low.  As far as I can 
tell, I've done a respectable job of setting up the database, tables, sequence, 
indexes...



But a little math tells me that I have one table that's particularly ugly.

This is for a total of 6 users.

If the user base gets to 100 or more, I'll be hitting a billion rows before too 
long.  I add about 70,000 rows per user per day.  At 100 users this is 7 million 
rows per day.  I'll hit a billion in 142 days, call it six months for simplicity.



The table itself is small (two columns: bigint, int) but I'm wondering when I'll 
start to hit a knee in performance and how I can monitor that.  I know where I 
work (day job) they have Oracle tables with a billion rows that just plain suck. 
 I don't know if a billion is bad or if the DBA's were not given the right 
opportunity to make their tables work.


But if they are any indication, I'll feeling some hurt when I exceed a billion 
rows.  Am I going to just fold up and die in six months?


I can't really expect anyone to have an answer regarding hardware, table size, 
performance speeds ...  but is there some way I can either monitor for this or 
estimate it before it happens?


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

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

Re: [GENERAL] When should I worry?

[Tom Allison]

On Jun 10, 2007, at 2:14 PM, Joe Conway wrote:



Bill Moran wrote:

Tom Allison <[EMAIL PROTECTED]> wrote:


If the user base gets to 100 or more, I'll be hitting a billion  
rows before too long.  I add about 70,000 rows per user per day.   
At 100 users this is 7 million rows per day.  I'll hit a billion  
in 142 days, call it six months for simplicity.


The table itself is small (two columns: bigint, int) but I'm  
wondering when I'll start to hit a knee in performance and how I  
can monitor that.  I know where I work (day job) they have Oracle  
tables with a billion rows that just plain suck.   I don't know  
if a billion is bad or if the DBA's were not given the right  
opportunity to make their tables work.


But if they are any indication, I'll feeling some hurt when I  
exceed a billion rows.  Am I going to just fold up and die in six  
months?


Alot depends on your specific use case.

- Will you be just storing the data for archival purposes, or  
frequently querying the data?


- If you need to run queries, are they well bounded to certain  
subsets of the data (e.g. a particular range of time for a  
particular user) or are they aggregates across the entire billion  
rows?


- Is the data temporal in nature, and if so do you need to purge it  
after some period of time?


As an example, I have an application with temporal data, that needs  
periodic purging, and is typically queried for small time ranges  
(tens of minutes). We have set up partitioned tables (partitioned  
by date range and data source -- akin to your users) using  
constraint exclusion that contain 3 or 4 billion rows (total of all  
partitions), and we have no problem at all with performance. But I  
suspect that if we needed to do an aggregate across the entire  
thing it would not be particularly fast ;-)


Why not just create a simulation of 100 users and run it as hard  
as your
can until it starts to degrade?  Then you'll have some real-world  
experience

to tell you how much you can handle.


This is good advice. Without much more detail, folks on the list  
won't be able to help much, but a with simulation such as this you  
can answer your own question...


Joe




Good questions.  I guess there are two answers.  There are times when  
I will want aggregate data and I'm not as concerned about the  
execution time.
But there are other queries that are part of the application design.   
These are always going to be of a type where I know a single specific  
primary key value and I want to find all the rows that are related.



First table has a row of
idx serial primary key
Third table has a row of
idx bigserial primary key

and a second table (the billion row table) consistes of two rows:
first_idx integer not null references first(idx) on delete cascade,
third_idx bigint not null references third(idx) on delete cascade,
constraint pkey_first_third primary key (first_idx, third_idx)

The common query will be:

select t.string
from first f, second s, third t
where f.idx = s.first_idx
and s.third_idx = t.idx
and f.idx = 4 (or whatever...).

So, I think the answer is that the data isn't going to be temporal or  
otherwise segragated or subsets.

I'll assume this is a lead in for partitions?
The data will be queried very frequently.  Probably plan on a query  
every 10 seconds and I don't know what idx ranges will be involved.
Would it be possible to partition this by the first_idx value?  An  
improvement?


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

  http://archives.postgresql.org/

Re: [GENERAL] When should I worry?

[Tom Allison]

Greg Smith wrote:


On Mon, 11 Jun 2007, Steve Crawford wrote:


In my experience the more common situation is to "go off a cliff."


Yeah, I think the idea that you'll notice performance degrading and be 
able to extrapolate future trends using statistical techniques is a 
bit...optimistic.


Anyway, back to the original question here.  If you're worried about 
catching when performance starts becoming an issue, you need to do some 
sort of logging of how long statements are taking to execute.  The main 
choice is whether to log everything, at which point the logging and 
sorting through all the data generated may become its own performance 
concern, or whether to just log statements that take a long time and 
then count how many of them show up.  Either way will give you some sort 
of early warning once you get a baseline; it may take a bit of tweaking 
to figure out where to draw the line at for what constitutes a "long" 
statement if you only want to see how many of those you get.


There are two tools you should look at initially to help process the 
logging information you get back:  pgFouine and PQA.  Here are intros to 
each that also mention how to configure the postgresql.conf file:


http://pgfouine.projects.postgresql.org/tutorial.html
http://www.databasejournal.com/features/postgresql/article.php/3323561

As they're similar programs, which would work better for you is hard to 
say; check out both and see which seems more practical or easier to get 
running.  For example, if you only have one of PHP/Ruby installed, that 
may make one tool or the easier preferred.


If you can get yourself to the point where you can confidently say 
something like "yesterday we had 346 statements that took more then 
200ms to execute, which is 25% above this month's average", you'll be in 
a positition to catch performance issues before they completely 
blindside you; makes you look good in meetings, too.




Starting to sound like a sane idea.
I've been running a test job for almost 24 hours and have accumulated only 8 
million rows.  That's another 125 days to get to the big 'B'.  I think by then 
I'll have blown a hard drive or worse.  I'm running this on some very old 
hardware that I have available (more of this at the bottom).


However, at this point the machine is running all of the SQL at < 0.2 seconds 
each.  Which I consider just fine for 7,599,519 rows.


Here's some specifics about the tables:
count() from headers: 890300
count() from tokens:  89
count() from header_token: 7599519


CREATE TABLE header_token (
header_idx integer NOT NULL,
token_idx integer NOT NULL
);

CREATE TABLE headers (
idx serial NOT NULL,
hash character varying(64) NOT NULL
);

CREATE TABLE tokens (
idx bigserial NOT NULL,
hash character varying(64) NOT NULL
);

ALTER TABLE ONLY headers
ADD CONSTRAINT headers_hash_key UNIQUE (hash);
ALTER TABLE ONLY headers
ADD CONSTRAINT headers_pkey PRIMARY KEY (idx);
ALTER TABLE ONLY header_token
ADD CONSTRAINT pkey_header_token PRIMARY KEY (header_idx, token_idx);
ALTER TABLE ONLY tokens
ADD CONSTRAINT tokens_hash_key UNIQUE (hash);
ALTER TABLE ONLY tokens
ADD CONSTRAINT tokens_pkey PRIMARY KEY (idx);
ALTER TABLE ONLY header_token
ADD CONSTRAINT header_token_header_idx_fkey FOREIGN KEY (header_idx) 
REFERENCES headers(idx) ON DELETE CASCADE;

ALTER TABLE ONLY header_token
ADD CONSTRAINT header_token_token_idx_fkey FOREIGN KEY (token_idx) 
REFERENCES tokens(idx) ON DELETE CASCADE;




The SQL I was timing were:
select t.hash, h.hash
from headers h, header_token ht, tokens t
where h.idx = ht.header_idx
and ht.token_idx = t.idx
and h.idx = ?


insert into header_token
select $header, idx from tokens where idx in (...)

The SELECT was <0.2
The INSERT was easily <.7 (most of the time -- ranged because the idx IN (..) 
varied from 200 to 700.  The min was <2 and the max was >1.0 from a few minutes 
of observation.



All of this was run on a Pentium II 450 MHz with 412MB RAM and a software linear 
0 pair or UDMA 66 7200RPM 8MB Cache drives (really old) on seperate IDE channels 
with ReiserFS disk format.  The actual script was running on a seperate machine 
across a 100-base-T full duplex network through a firewall machine between the 
two subnets.


I can't imagine how long it would take to run:
delete from tokens;
with the CASCADE option...

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

  http://archives.postgresql.org/

Re: [GENERAL] When should I worry?

[Tom Allison]

On Jun 12, 2007, at 12:00 AM, Greg Smith wrote:



On Mon, 11 Jun 2007, Tom Allison wrote:

All of this was run on a Pentium II 450 MHz with 412MB RAM and a  
software linear 0 pair or UDMA 66 7200RPM 8MB Cache drives (really  
old) on seperate IDE channels with ReiserFS disk format.


Sometimes it's not clear if someone can speed up what they're doing  
simply by using more expensive hardware.  In your case, I think  
it's safe to say you've got quite a bit of margin for improvement  
that way when you run into a problem.


No doubt!  But I think it's worth nothing how much performance I  
*can* get out of such an old piece of hardware.


My other computer is a Cray.  No, that's the bumper sticker on my car.

My other computer is an Athlong 64 2.?GHz with a single disk but less  
RAM.  It's a xen virtual machine that I'm renting, so increasing the  
power of the machine is actually very easy to do, but not yet required.


But I was impressed with how well it works on such an old machine.

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

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

Re: [GENERAL] DeadLocks..., DeadLocks...

[Tom Allison]

Gregory Stark wrote:


I'm still not precisely clear what's going on, it might help if you posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.

Are any of the DML you mention on other tables on those tables with foreign
key references to this one?

It's impossible for two inserts on the same table to deadlock against each
other so there must be more going on than what you've described. It's hard to
help much without a complete picture.



This is an example of what comes out of the apache logs...

[Thu Jun 14 19:29:41 2007] [warn] mod_fcgid: stderr: DBD::Pg::db do failed: 
ERROR:  deadlock detected
[Thu Jun 14 19:29:41 2007] [warn] mod_fcgid: stderr: DETAIL:  Process 16214 
waits for ShareLock on transaction 297563; blocked by process 16211.




This is what I found in my postgresql logs (after I turned on a few more items).
I can repeat this really easily.  Is there specific flags I should 
enable/disable for logging for this?


My guess is the problem is related to 'insert into history_token..."
but I haven't any Process ID's in here to be certain.


2007-06-14 19:50:35 EDT LOG:  execute dbdpg_11: insert into history(signature) 
values ($1)

2007-06-14 19:50:35 EDT DETAIL:  parameters: $1 = 
'53111e6c5c65570ec2e85636271a5b90'
2007-06-14 19:50:35 EDT LOG:  duration: 0.169 ms
2007-06-14 19:50:35 EDT LOG:  statement: select history_idx from history where 
signature = '53111e6c5c65570ec2e85636271a5b90'

2007-06-14 19:50:35 EDT LOG:  duration: 0.328 ms
2007-06-14 19:50:35 EDT LOG:  statement: insert into history_token(history_idx, 
token_idx)

select values.history_idx, values.token_idx
from ( values 
(2703,260),(2703,31789),(2703,1518),(2703,59),(2703,555),(2703,4),(2703,66447),(2703,8178),(2703,64),(2703,132),(2703,6126),(2703,135),(2

703,69),(2703,9166),(2703,629),(2703,73),(2703,74),(2703,2271),(2703,78),(2703,493),(2703,8164),(2703,211),(2703,8166),(2703,84),(2703,60608),(2703,217),(2703,
88),(2703,8207),(2703,161),(2703,33518),(2703,220),(2703,222),(2703,446),(2703,2188),(2703,336),(2703,1197),(2703,166),(2703,1537),(2703,28),(2703,168),(2703,2
481),(2703,1081),(2703,99),(2703,100),(2703,172),(2703,8209),(2703,231),(2703,1900),(2703,344),(2703,104),(2703,24694),(2703,106),(2703,37),(2703,107),(2703,17
9),(2703,8203),(2703,85629),(2703,3671),(2703,98970),(2703,8187),(2703,187),(2703,306),(2703,254),(2703,415),(2703,256),(2703,257),(2703,98975),(2703,98976),(2
703,98977),(2703,98978) ) as values(history_idx, token_idx)
left outer join history_token ht using (history_idx, token_idx)
where ht.history_idx is null

2007-06-14 19:50:35 EDT ERROR:  deadlock detected
2007-06-14 19:50:35 EDT DETAIL:  Process 17253 waits for ShareLock on 
transaction 303949; blocked by process 17229.
Process 17229 waits for ShareLock on transaction 303950; blocked by 
process 17253.
2007-06-14 19:50:35 EDT STATEMENT:  update tokens set last_seen = now() where 
token_idx in (260,31789,1518,59,555,4,66447,8178,64,132,6126,135,69,9166,629,73,7

4,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,98963,8209,231,1900,344,104,24694,106
,37,107,179,8203,85629,3671,8187,187,306,254,415,256,257,98968,98969,98970,98971)
2007-06-14 19:50:35 EDT LOG:  disconnection: session time: 0:00:13.810 user=spam 
database=spam host=127.0.0.1 port=38126




---(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] DeadLocks..., DeadLocks...

[Tom Allison]

Gregory Stark wrote:


I'm still not precisely clear what's going on, it might help if you posted the
actual schema and the deadlock message which lists the precise locks that
deadlocked.

Are any of the DML you mention on other tables on those tables with foreign
key references to this one?

It's impossible for two inserts on the same table to deadlock against each
other so there must be more going on than what you've described. It's hard to
help much without a complete picture.



I think I found the problem.  And it's not at all where I thought it was.
Process 17583 waits for ShareLock on transaction 306841;
blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840;
blocked by process 17583.

Where I'm at a lost is the deadlocks reported are on different tables.
However, getting back to the Foreign Key question
history_token does have a foreign key constraint on tokens.token_idx on delete 
cascade.


So is the INSERT statement on history_token getting deadlocked by the token 
UPDATE statement?  Looks that way and the only think I can see causing that 
might be a foreign key issue.


Am I correctly identifying the problem?
Any options?


2007-06-14 19:58:43 EDT 17725 306927 LOG:  statement: select token_idx from 
tokens where token in ('ShareLock','hdr:414A79FBC82','ht.history_idx','2271','hdr:
2007-06-14 19:58:31 EDT 17583 306840 LOG:  statement: insert into 
history_token(history_idx, token_idx)

select values.history_idx, values.token_idx
from ( values 
(2862,260),(2862,31789),(2862,1518),(2862,59),(2862,555),(2862,4),(2862,66447),(2862,8178),(2862,64),(2862,132),(2862,6126),(2862,135),(2

862,69),(2862,9166),(2862,629),(2862,73),(2862,74),(2862,2271),(2862,78),(2862,493),(2862,8164),(2862,211),(2862,8166),(2862,84),(2862,60608),(2862,217),(2862,
88),(2862,8207),(2862,161),(2862,33518),(2862,220),(2862,222),(2862,446),(2862,2188),(2862,336),(2862,1197),(2862,166),(2862,1537),(2862,28),(2862,168),(2862,2
481),(2862,1081),(2862,99),(2862,100),(2862,172),(2862,8209),(2862,231),(2862,1900),(2862,344),(2862,104),(2862,24694),(2862,106),(2862,37),(2862,107),(2862,17
9),(2862,8203),(2862,99140),(2862,85629),(2862,3671),(2862,8187),(2862,187),(2862,306),(2862,254),(2862,415),(2862,256),(2862,257),(2862,99227),(2862,99228),(2
862,99229),(2862,99230) ) as values(history_idx, token_idx)
left outer join history_token ht using (history_idx, token_idx)
where ht.history_idx is null

2007-06-14 19:58:31 EDT 17725 306841 LOG:  statement: update tokens set 
last_seen = now() where token_idx in 
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13

5,69,9166,629,73,74,2271,78,493,8164,211,8166,84,99222,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,8209,231,1900
,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99224,99225,99226)
2007-06-14 19:58:31 EDT 17657 306842 LOG:  duration: 0.033 ms
2007-06-14 19:58:31 EDT 17657 306842 LOG:  execute dbdpg_105: insert into 
user_history(user_idx, history_idx, seen_as) values ($1,$2,'noscore')

2007-06-14 19:58:31 EDT 17657 306842 DETAIL:  parameters: $1 = '1', $2 = '2853'
2007-06-14 19:58:31 EDT 17657 306842 LOG:  duration: 0.194 ms
2007-06-14 19:58:32 EDT 17657 306843 LOG:  statement: DEALLOCATE dbdpg_105
2007-06-14 19:58:32 EDT 17657 0 LOG:  duration: 0.164 ms
2007-06-14 19:58:32 EDT 17657 306844 LOG:  statement: select h_msgs, s_msgs from 
user_token where user_idx = 1 and token_idx in (260,31789,1518,59,555,4,66447,

8178,64,132,6126,135,69,9166,629,73,74,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,
8209,231,1900,344,104,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:32 EDT 17657 0 LOG:  duration: 1.408 ms
2007-06-14 19:58:32 EDT 17657 306845 LOG:  statement: update tokens set 
last_seen = now() where token_idx in 
(260,31789,1518,59,555,4,66447,8178,64,132,6126,13

5,69,9166,629,73,74,2271,78,493,8164,211,8166,84,60608,217,88,8207,161,33518,220,222,446,2188,336,1197,166,1537,28,168,2481,1081,99,100,172,8209,231,1900,344,1
04,24694,106,37,107,179,8203,99140,85629,3671,8187,187,306,254,415,256,257,99216,99217,99218,99219)
2007-06-14 19:58:33 EDT 17583 306840 ERROR:  deadlock detected
2007-06-14 19:58:33 EDT 17583 306840 DETAIL:  Process 17583 waits for ShareLock 
on transaction 306841; blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840; blocked by 
process 17583.
2007-06-14 19:58:33 EDT 17583 306840 CONTEXT:  SQL statement "SELECT 1 FROM ONLY 
"public"."tokens" x WHERE "token_idx" = $1 FOR SHARE OF x"
2007-06-14 19:58:33 EDT 17583 306840 STATEMENT:  insert into 
history_token(history_idx, token_idx)

select values.history_idx, values.token_idx
from ( values 
(2862,260),(2862,31789),(2862,1518),(2862,59),(2862,555),(2862,4),(2862,66447),(2862,8178),(2862,6

Re: [GENERAL] DeadLocks..., DeadLocks...

[Tom Allison]

Tom Allison wrote:


Gregory Stark wrote:


I'm still not precisely clear what's going on, it might help if you 
posted the

actual schema and the deadlock message which lists the precise locks that
deadlocked.

Are any of the DML you mention on other tables on those tables with 
foreign

key references to this one?

It's impossible for two inserts on the same table to deadlock against 
each
other so there must be more going on than what you've described. It's 
hard to

help much without a complete picture.



I think I found the problem.  And it's not at all where I thought it was.
Process 17583 waits for ShareLock on transaction 306841;
blocked by process 17725.
Process 17725 waits for ShareLock on transaction 306840;
blocked by process 17583.

Where I'm at a lost is the deadlocks reported are on different tables.
However, getting back to the Foreign Key question
history_token does have a foreign key constraint on tokens.token_idx on 
delete cascade.


So is the INSERT statement on history_token getting deadlocked by the 
token UPDATE statement?  Looks that way and the only think I can see 
causing that might be a foreign key issue.


Am I correctly identifying the problem?
Any options?




HISTORY_TOKEN:
{
eval{$dbh->do($sql)};
if ($@) {
if ($@ =~ /deadlock detected/) {
warn "$$: deadlock detected on HISTORY_TOKEN\n";
usleep 150_000;
warn "$$: retrying HISTORY_TOKEN\n";
redo HISTORY_TOKEN;
}
croak "$sql\n$dbh->[EMAIL PROTECTED]";
}
};


This seems to help a lot.
At least it's getting done.

Now, is there a shorter usleep time I can use safely or should I just leave well 
enough alone?


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

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

Re: [GENERAL] DeadLocks..., DeadLocks...

[Tom Allison]

Terry Fielder wrote:


My 2 cents:

I used to get a lot of these sharelock problems.
Users using different records, but same tables in different order.
(apparently 7.x was not as good as 8.x at row level locking)

I was advised to upgrade from 7.x to 8.x
I did, and all those sharelock problems went away.



I'm on version 8.2 and not all the problems have gone away.

All I can do right now is just trap the error and retry...
Gets bogged down after a while.  Not sure how much of a limitation the hardware 
is but 6 users and I start to run into a deadlock almost every 10 seconds.


I rarely need to go to 6 users, but it's interesting to see what happens when I 
do.

I'm finding the length of time necessary to wait for a retry can very a lot.

But I'm open to suggestions.

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

Re: [GENERAL] DeadLocks..., DeadLocks...

[Tom Allison]

Gregory Stark wrote:


The insert is deadlocking against the update delete.

The problem is that the insert has to lock the records to be sure they aren't
deleted. This prevents the update for updating them. But the update has
already updated some other records which the insert hasn't referred to yet.
When the insert tries to insert a record referring to those it can't lock them
before they're already locked by the update and you have a deadlock.

Do you really need the update at all? Do you use the last_seen field for
anything other than diagnostics?

You could try breaking the update up into separate transactions instead of a
single batch statement. That would perform poorly but never deadlock.

You could try to order them both but I don't know if that's possible. UPDATE
doesn't take an ORDER BY clause. I suppose you could execute the update
statement as separate queries within a single transaction in whatever order
you want which would avoid the performance issue of issuing hundreds of
transactions while allowing you to control the order.



The last_seen is a purge control -- when last_seen < current_date - ?? then I 
remove the record.


I think there are two ways I could do this without killing performance.  Please 
let me know what you think...


I could modify the update to something more like:

update tokens set last_seen = now() where token_idx in (...)
and last_seen < current_date
or even push it back multiple days.

There's always the risk of losing a few records, but I'm probably not going to 
notice.  (Not bank transactions)


The other approach would be to use an external file to queue these updates and 
run them from a crontab.  Something like:

  open (my $fh, ">> /var/spool/last_seen");
  flock($fh, LOCK_EX);
  seek($fh, 0, 2)
  print join("\n", @$tokens),"\n";
  flock($fh, LOCK_UN);
  close $fh
and then run a job daily to read all these in to a hash (to make them unique 
values) and then run one SQL statement at the end of the day.


Is there a limit to the number of values you can have in an IN(...) statement?

---(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] DeadLocks..., DeadLocks...

[Tom Allison]

Gregory Stark wrote:

"Tom Allison" <[EMAIL PROTECTED]> writes:


The other approach would be to use an external file to queue these updates and
run them from a crontab.  Something like:



and then run a job daily to read all these in to a hash (to make them unique
values) and then run one SQL statement at the end of the day.


Well probably better to keep it in the database. The database also knows how
to use hashes to get distinct values too.

So if you have a "history" table which records ids with dates and then do a
transaction like:

BEGIN;
DELETE FROM tokens WHERE id NOT IN (select id from history);
DELETE from history WHERE seen < now()-'3 days'::interval;
END;

This could still deadlock so it may make sense for it to do it in a
transaction and add LOCK TABLE statements to lock the tables which refer to
the tokens table. 





I ended up with two steps to the solution.
First, I do handle the deadlock errors with a sleep/redo loop.
I add a bit more time with each sleep so eventually everything slows down so 
much it can't deadlock.


Second, the offending SQL was to UPDATE the table that was the target of a 
Foreign Key constraint.  I modified the SQL from:

update tokens set last_seen = now() where token_idx in (...)
to:
update tokens set last_seen = now() where
token_idx in (...) and last_seen < current_date;

Since this only happens when things are running at full...
Previously I could deadlock on 60 emails.
Now I can't deadlock on 8000.
I would venture to say the problem is effectively fixed.

I have a question though.
I noticed a particular format for identifying dates like:
now()-'3 days'::interval;

What's '::interval' and why should I use it?

---(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] Using the GPU

[Tom Allison]

On Jun 11, 2007, at 4:31 AM, Alban Hertroys wrote:



Alexander Staubo wrote:

On 6/8/07, Billings, John <[EMAIL PROTECTED]> wrote:

If so  which part of the database, and what kind of parallel
algorithms would be  used?


GPUs are parallel vector processing pipelines, which as far as I can
tell do not lend themselves right away to the data structures that
PostgreSQL uses; they're optimized for processing high volumes of
homogenously typed values in sequence.


But wouldn't vector calculations on database data be sped up? I'm
thinking of GIS data, joins across ranges like matching one (start,  
end)

range with another, etc.
I realize these are rather specific calculations, but if they're
important to your application...

OTOH modern PC GPU's are optimized for pushing textures; basically
transferring a lot of data in as short a time as possible. Maybe  
it'd be

possible to move result sets around that way? Do joins even maybe?


OTOH databases might not be running on modern desktop PC's with the  
GPU investment.
Rather they might be running on a "headless" machine that has little  
consideration for the GPU.


It might make an interesting project, but I would be really depressed  
if I had to go buy an NVidia card instead of investing in more RAM to  
optimize my performance!  



---(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] INSERT ... RETURNING in v8.2

[Tom Allison]

On Jun 12, 2007, at 10:18 AM, Vincenzo Romano wrote:



Hi all.
I'm trying to use this wonderful feature (thanks to anyone who
suggested/committed/implemented it).

According to the documentation:
(http://www.postgresql.org/docs/8.2/interactive/sql-insert.html)

"The optional RETURNING clause causes INSERT to compute and return
value(s) based on each row actually inserted. This is primarily
useful for obtaining values that were supplied by defaults, such
as a serial sequence number. However, any expression using the
table's columns is allowed. The syntax of the RETURNING list is
identical to that of the output list of SELECT."


Holy Crud!
you mean to tell me I can replace:

insert into table(string) values(('one'),('two'),('three'));
select idx from table where string in ('one','two','three');

with

insert into table(string) values(('one'),('two'),('three')) returning  
idx;


?

I realize that this is an extension to standard SQL but it sure would  
save me a lot.


I'm wondering just how many other things I'm missing
(I am really starting to like this database more every week)

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

Re: [GENERAL] INSERT ... RETURNING in v8.2

[Tom Allison]

On Jun 12, 2007, at 11:40 AM, Vincenzo Romano wrote:



On Tuesday 12 June 2007 16:35:05 Martijn van Oosterhout wrote:

On Tue, Jun 12, 2007 at 04:18:32PM +0200, Vincenzo Romano wrote:

Well, at least on v8.2.4 I cannot return count(*), that is the
number of lines actually inserted into the table. Nor I can
return any aggregate function of them.


I don't think anybody considered the possibility of using an
aggregate there, primary because for an aggregate you need a group
by. What has been discussed is nested statements, like:

SELECT a, count(*) FROM
  (INSERT  RETURNING a, b)
GROUP BY a;

But I don't think that's implemented (the interactions with
triggers havn't been worked out I think)


Amk I doing anything wrong or is there some missing sentence in
the documentation?


When the docs talk about an "expression" they don't mean
aggregates, since they are not functions in the ordinary sense.

Hope this helps,


I feel that your remarks make some sense.

First, the documentation says "any expression using the table's
columns is allowed".

Second, I'm not using nested statements, but rather a plain
INSERT ... RETURNING COUNT(*) INTO var (it's inside a PL/PgSQL
function body). It should not need any GROUP BY as the query is
plain.

Maybe the solution is somewhere in between what you say and what I'd
expect. Of course at the moment I have added an extra SELECT COUNT(*)
in order to get that number.



Not entirely sure what you're doing but at least with Perl you can  
always ask for the number of affected rows:  $sth->rows after you run  
an INSERT.



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

Re: [GENERAL] Using the GPU

[Tom Allison]

Tom Lane wrote:

"Alexander Staubo" <[EMAIL PROTECTED]> writes:

On 6/16/07, Tom Allison <[EMAIL PROTECTED]> wrote:

It might make an interesting project, but I would be really depressed
if I had to go buy an NVidia card instead of investing in more RAM to
optimize my performance!  



Why does it matter what kind of hardware you can (not "have to") buy
to give your database a performance boost? With a GPU, you would have
one more component that you could upgrade to improve performance;
that's more possibilities, not less. I only see a problem with a
database that would *require* a GPU to achieve adequate performance,
or to function at all, but that's not what this thread is about.


Too often, arguments of this sort disregard the opportunity costs of
development going in one direction vs another.  If we make any
significant effort to make Postgres use a GPU, that's development effort
spent on that rather than some other optimization; and more effort,
ongoing indefinitely, to maintain that code; and perhaps the code
will preclude other possible optimizations or features because of
assumptions wired into it.  So you can't just claim that using a GPU
might be interesting; you have to persuade people that it's more
interesting than other places where we could spend our
performance-improvement efforts.


You have a good point.

I don't know enough about how/what people use databases for in general to know 
what would be a good thing to work on.  I'm still trying to find out the 
particulars of postgresql, which are always sexy.


I'm also trying to fill in the gaps between what I already know in Oracle and 
how to implement something similar in postgresq.  But I probably don't know 
enough about Oracle to do much there either.


I'm a believer in strong fundamentals over glamour.

---(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] PHP sucks!! - was: persistent db connections in PHP

[Tom Allison]

On Jun 16, 2007, at 3:38 PM, John Smith wrote:



guys,
love both tools but php @ 2.5 *billion* google results is far more
popular than postgresql @ 25 million google results. *if* somebody's
gotto adapt it's not php. php does what it does best in a way that
stuffy academics don't get.


I would oppose the notion that someone needs to adapt to meet a  
lowest common denominator.  That's a willing sacrifice of yourself.


I've been around a lot of different languages to different levels of  
involvement and have formed a theory that hasn't been disputed in 5  
years.


Popular languages have nothing to do with how good they are.
They have to do with the lower entry barrier.

Examples:

Java developers are crawling out of the woodwork.  That's because I  
can go to the local community college and get a couple classes done.   
Run over to some training company like New Horizons, and walk away  
with a Java Certified Programmer paper in a month.  Now I can walk  
into a company and say, "I'm a Java developer, see!".  Unfortunately  
they also think Java is the only language anything can be programmed in.


Similarly the MCSE (Must Consult Someone Else) is a easy piece of  
paper to obtain that says you are Software Engineer.  I took the  
tests 8 years ago, they're crap.


MySQL is insanely easy to set up.  But there are a lot of MySQL users  
who really don't understand the concept of a database and tend to  
treat it like a flat file or spreadsheet.


I got in a shouting match at a Rails conference because they didn't  
see why you should put things like UNIQUE indexes or Referential  
Integrity in their database since they have the Rails functions  
of .exists? and .unique? (or something like that...)  Easy to use,  
but it requires smarts to use it well.


Contrary to these examples:

Perl programmers who actually do this for a period of time tend to be  
extremely good at general programming concepts.  Perl has a high  
entry barrier.  There is a LOT to learn before you can do much of  
significance.  Few of them are the "idiots" that the other fast track  
certification languages can provide.  It's certification by war stories.


C/C++ programmers who do this beyond their BSCSE courses are the same  
way.  They know their stuff and can be trusted to do it well.  Again,  
not a low barrier language.


It is my opinion that MySQL and PHP both fall into the category of  
low entry barrier applications.  They are easy to set up, easy to get  
"hello world" and you can find lots of company supported training  
programs.  But this has nothing to do with a language/application  
being good or safe.


But at the same time, I think the communities do not suffer from  
having higher entry barriers to them.  If everything was as popular  
as Windows then there would be a lot of AOL addresses signed up on  
this list, something I'm OK with not having.


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

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

Re: [GENERAL] PHP sucks!! - was: persistent db connections in PHP

[Tom Allison]

On Jun 16, 2007, at 4:12 PM, Uwe C. Schroeder wrote:




On Saturday 16 June 2007, John Smith wrote:

guys,
love both tools but php @ 2.5 *billion* google results is far more
popular than postgresql @ 25 million google results. *if* somebody's
gotto adapt it's not php. php does what it does best in a way that
stuffy academics don't get.


Mhhh - what does PHP have to do with Postgresql? Lots of pages just  
end

in .php, which is why the google results are so high - guess what, the
tool "html" hits 3.2 billion :-)



That's me, sorry.
I forgot I put:

AddHandler cgi-script .html

in my web server...




---(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] PHP sucks!! - was: persistent db connections in PHP

[Tom Allison]

On Jun 16, 2007, at 7:51 PM, Leif B. Kristensen wrote:



On Saturday 16. June 2007 23:34, Erick Papadakis wrote:

How much value you derive from a language
depends on how you use it. After playing for years with Perl, and now
with Python and Ruby, I think PHP is still where it's at.


I too have played around with Perl and Python, and use both of them  
for

special jobs, but I'm writing my dynamic web pages in PHP. In
hindsight, I might have been better off writing them in Perl, but I  
was

put off by the lousy HTML generated by the CGI module. It doesn't even
close paragraphs. PHP doesn't have anything like the CGI.pm, but I'm
quite content with hand coding where every single HTML tag should go.


Have you tried:
print $q->p("This is a paragraph");
recently?
Does a nice job of closing paragraphs.


---(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] Duplicate Unique Key constraint error

[Tom Allison]

On Jul 10, 2007, at 3:09 PM, Tom Lane wrote:



"Harpreet Dhaliwal" <[EMAIL PROTECTED]> writes:
Transaction 1 started, saw max(dig_id) = 30 and inserted new  
dig_id=31.
Now the time when Transaction 2 started and read max(dig_id) it  
was still 30

and by the time it tried to insert 31, 31 was already inserted by
Transaction 1 and hence the unique key constraint error.


This is exactly why you're recommended to use sequences (ie serial
columns) for generating IDs.  Taking max()+1 does not work, unless
you're willing to lock the whole table and throw away vast amounts of
concurrency.


I wonder how SQL server is handling this?  Are they locking the table?
I realize it's off-topic, but I'm still curious.

Sequences are your friend.  they come in INT and BIGINT flavors, but  
BIGINT is a lot of rows.


Can set set Sequences to automatically rollover back to zero?


---(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

[GENERAL] deleting rows with foreign keys

[Tom Allison]

Here's my table:

Table "public.tokens"
  Column   |Type |  
Modifiers
---+- 
+
token_idx | bigint  | not null default nextval 
('tokens_token_idx_seq'::regclass)

token | character varying(140)  | not null
last_seen | timestamp without time zone | default now()
Indexes:
"tokens_pkey" PRIMARY KEY, btree (token_idx)
"tokens_token_key" UNIQUE, btree (token)


I have ~250K rows in this table.
the token_idx is referenced in two other tables, both of whom have a  
foreign key constraint to ON DELETE CASCADE.
Of the other two tables, one has ~1M rows and the other ~350K rows.   
So they are both one to many relationships with many of the token  
table rows appearing in the other two.


Problem:
Deleting one row can take 2 seconds.

Is there something I can do to improve the speed.  Locking the tables  
is an option as this is a maintenance procedure and not regular  
business.  But I don't know that this will be useful or practical.


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

[GENERAL] Mac OS X

[Tom Allison]

I tried to install postgres onto my macbook via 'fink' and don't like  
it all that much.

I decided to install from source, it's a fallback to my slackware days.

But fink already created a user postgres and I can't seem to find  
anything to change it's configuration settings for shell, home  
director...


If anyone has suggestions I would appreciate it.

Also, does anyone know of a more current installation write-up for  
Mac other than what Apple provides?  It's written around 7.4 and I'm  
not sure it's going to be optimal.


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

Re: [GENERAL] multirow insert

[Tom Allison]

On Jul 13, 2007, at 2:11 PM, A. Kretschmer wrote:



am  Fri, dem 13.07.2007, um 18:50:26 +0200 mailte Zlatko Mati?  
folgendes:
When using multirow INSERT INTO...VALUES command, are all rows  
inserted in a

batch, or row by row?


Within one transaction, yes.


Trust me... It's MUCH faster then trying to do each insert.

I have a little program that was a 100x improvement in performance by  
changing my SQL to use INSERT INTO ... VALUES



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

Re: [GENERAL] CASE in ORDER BY clause

[Tom Allison]

Uwe C. Schroeder wrote:


On Saturday 07 July 2007, Lew wrote:


So if your RDBMS sorts NULLs after all other values, then from


select start_date from show_date
order by
  case when start_date > CURRENT_DATE then start_date end desc,
  case when start_date <= CURRENT_DATE then start_date end asc;

all rows with start_date > CURRENT_DATE will appear first, in start_date
descending order,
then all rows with start_date <= CURRENT_DATE will appear, in start_date
ascending order.

Is CURRENT_DATE evaluated once for the query or twice for each row?


CURRENT_DATE is evaluated once per transaction.  If you run in autocommit - 
mode, then the single query is wrapped in a transaction by itself.

Either way it's never evaluated per occurrence.



I'm coming in late on this but you might try something like...

select ... from (
select
...
case when start_date > current date
then 1 || start_date - current_date
else 0 || current_date - start_date end "FOO"
)
order by FOO desc

Or something like that...

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

  http://archives.postgresql.org/

Re: [GENERAL] When is PostgreSQL 8.3 slated for release?

[Tom Allison]

Keaton Adams wrote:
I am being asked by management when PostgreSQL 8.3 will become generally 
available.  Is there an updated timeline for 8.3?




You know the answer is supposed to be "when it's ready" but we all know 
Management doesn't like that kind of an answer.


Of course, you could just pull the same thing a proprietary software company 
does.  Make up a date based on some WAG and then excuse the delays for years 
afterwards by citing all the really great features that are going to be included 
and how wonderful it's going to be when it's finally released.


Here's my WAG:

Release 8.0  Release date: 2005-01-19
Release 8.1  Release date: 2005-11-08
Release 8.2  Release date: 2006-12-05

I wouldn't expect anything earlier than say...
2008-01-12

I am purely guessing here and I haven't sat down and read through all the change 
notes to see what really happened with PostgreSQL but based on other projects


x.0 to x.1 is a lot of relatively easy things to address from a major point 
release.
As time progresses -- the time between these sub point releases (x.1->x.2->...) 
will increase slightly as the level of effort to identify and resolve any issues 
 increases based on a "low hanging fruit" and "severity" approach.
There may be a significant reduction in point releases at the end of the cycle 
as there is a lot of "cleanup" activity before the next major release.  So the 
x.9 to x.10 time might be reduced before you get to 9.0.0


So I would expect the next release (8.3) to not be any sooner then January 2008 
at best.  Anything before that could be considered a gift and anything beyond 
that shouldnt' be considered "bad".


If management is the type who prefers to wait until the ".1" release, then it's 
going to be a bit longer.  Me?  I tend to be a ".1" guy much of the time.


And this is just a guess.

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

  http://archives.postgresql.org/

[GENERAL] pg_dump

[Tom Allison]

just checking to be sure.

pg_dump does not support SSL connections?

I have been using pgsql with ssl connections to my database.
But when I tried pg_dump I was hit with the "no ssl" error message.
Didn't see an option for it in the RTFM so ..  Am I correct in  
assuming that pg_dump/pg_restore are not supposed to run via ssl?


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

  http://archives.postgresql.org/

[GENERAL] Debian problem...

[Tom Allison]

Ran into a problem.

I hosed up postgresql by deleting the data directory.
So I thought I would just uninstall and reinstall postgres using  
Debian packages.


Now I have nothing working.

Wondering if anyone here has any suggestions on what to do with a  
Debian installation.

If not, I'm checking that list too, but it might be a while...

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

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

Re: [GENERAL] Debian problem...

[Tom Allison]

I was able get my database working again.
Never figured out why...

On Sep 11, 2007, at 12:52 AM, 李彦 Ian Li wrote:



Maybe some Debian specific commands will help:
pg_lsclusters: list clusters you have on the machine;
pg_dropcluster: drop an existing cluster;
pg_createcluster: create new cluster.

Regards.

Tom Allison wrote:

Ran into a problem.
I hosed up postgresql by deleting the data directory.
So I thought I would just uninstall and reinstall postgres using  
Debian packages.

Now I have nothing working.
Wondering if anyone here has any suggestions on what to do with a  
Debian installation.

If not, I'm checking that list too, but it might be a while...
---(end of  
broadcast)---

TIP 3: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faq



---(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



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

Re: [GENERAL] Debian problem...

[Tom Allison]

On Sep 11, 2007, at 5:49 AM, Tom Allison wrote:



I was able get my database working again.
Never figured out why...


My database data (sorry about the redundancy there) is sitting on a  
RAID1 array with LVM and ReiserFS.


I've heard some dissention about the use of ReiserFS and was wondering:

true?
alternatives?


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

Re: [GENERAL] Debian problem...

[Tom Allison]

On Sep 12, 2007, at 3:52 AM, Tino Wildenhain wrote:



Hi,

Tom Allison schrieb:

On Sep 11, 2007, at 5:49 AM, Tom Allison wrote:


I was able get my database working again.
Never figured out why...
My database data (sorry about the redundancy there) is sitting on  
a RAID1 array with LVM and ReiserFS.
I've heard some dissention about the use of ReiserFS and was  
wondering:


No problem with RazorFS if your hardware does not fail. But if...
chances are you have to use your backup.

Alternatively you could use XFS but I'm not sure if it performs
better on failing hardware. I guess not.

Regards
Tino


Well, when one is looking at hardware failure, the performance  
doesn't really matter that much.

Is EXT3 better for dealing with a RAID1 failure?
I know reiserfs does better performance wise, but there's no point in  
going fast if you can't steer.


---(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

[GENERAL] IP addresses

[Tom Allison]

I am planning on doing a LOT of work with ip addresses and thought that the
inet data type would be a great place to start.

But I'm not sure how this works in with accessing the addresses.  In perl or
ruby how is the value returned?
Or should I stricly use host() and other functions to be explicit about what
I'm doing.


Another question.
Given a subnet (eg: 192.168.1.0/24) is there some way to pull all the
addresses therein?
I can do this in code - but I was curious if there was a postgres way of
doing it (didn't see any, but..)

[GENERAL] power failure....

[Tom Allison]

How do I restart the following with some level of sanity?

2007-11-30 19:35:20 EST LOG:  could not load root certificate file  
"root.crt": no SSL error reported

2007-11-30 19:35:20 EST DETAIL:  Will not verify client certificates.
2007-11-30 19:35:20 EST LOG:  database system was interrupted at  
2007-11-27 09:08:44 EST

2007-11-30 19:35:20 EST LOG:  record with zero length at 0/40808BA0
2007-11-30 19:35:20 EST LOG:  invalid primary checkpoint record
2007-11-30 19:35:20 EST LOG:  record with zero length at 0/407C9628
2007-11-30 19:35:20 EST LOG:  invalid secondary checkpoint record
2007-11-30 19:35:20 EST PANIC:  could not locate a valid checkpoint  
record
2007-11-30 19:35:20 EST LOG:  startup process (PID 8755) was  
terminated by signal 6
2007-11-30 19:35:20 EST LOG:  aborting startup due to startup process  
failure



postgres version 8.2 sitting on a Reiser FS on RAID1

I'm not finding much on the web other than bad news...


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

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

Re: [GENERAL] power failure....

[Tom Allison]

Tom Lane wrote:

Tom Allison <[EMAIL PROTECTED]> writes:
2007-11-30 19:35:20 EST PANIC:  could not locate a valid checkpoint  
record


Ugh :-(.  pg_resetxlog should get you back into the database, but it's
anybody's guess whether and how badly the contents will be corrupted.
I would recommend trying a dump/reload and also doing any manual
consistency crosschecks you can think of.


postgres version 8.2 sitting on a Reiser FS on RAID1


Better take a second look at your disk hardware configuration.  At least
in theory, this Can't Happen if your disk hardware is handling fsync
honestly.

regards, tom lane



Cool, thanks.

I wonder what I'm supposed to do with my debian installation since there doesn't 
seem to be any such thing as pg_resetxlog.  Or is it hiding somewhere?


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

Re: [GENERAL] power failure....

[Tom Allison]

Tom Lane wrote:

Tom Allison <[EMAIL PROTECTED]> writes:
2007-11-30 19:35:20 EST PANIC:  could not locate a valid checkpoint  
record


Ugh :-(.  pg_resetxlog should get you back into the database, but it's
anybody's guess whether and how badly the contents will be corrupted.
I would recommend trying a dump/reload and also doing any manual
consistency crosschecks you can think of.


postgres version 8.2 sitting on a Reiser FS on RAID1


Better take a second look at your disk hardware configuration.  At least
in theory, this Can't Happen if your disk hardware is handling fsync
honestly.

regards, tom lane



I tracked it down and did a reset.
I only have one large table right now.

And now I've decided to start using fsync=on!!!  :)
I don't think I want to "test" this just yet...

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

Re: [GENERAL] power failure....

[Tom Allison]

I was able to get things up and running OK.
Don't have any WAL that I'm aware of, but it managed to have another  
power failure hours later.


I seems that the UPS is more POS than UPS.  I think the battery is dead.




On Dec 2, 2007, at 3:52 AM, Greg Smith wrote:


On Sat, 1 Dec 2007, Joshua D. Drake wrote:

change wal_sync_method to open_sync and fsync=on isn't nearly as  
bad as

it sounds.


Just be warned that there's been one report that some Linux  
versions have bugs that make open_sync problematic:


http://archives.postgresql.org/pgsql-hackers/2007-10/msg01310.php

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



---(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

[GENERAL] CPU

[Tom Allison]

is there much of a difference in performance between a XEON, dual  
core from intel and a dual core AMD 64 CPU?


I need a bit of an upgrade and am not sure which, if any, have a  
significant advantage for postgres databases.


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

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

[GENERAL] mac ports question

[Tom Allison]

I ran into a problem today where somewhere my port of postgresql82 just 
stopped working.  I'm largely an idiot on Mac because I use is as a 
workstation/development box and do most of the real system related work 
on my debian boxes.


But I don't know how to get the port working again.

Then I saw there is a new version 8.3 in port.

So, if I upgrade does anyone know if this cleanly removes version 8.2 
from the box so I don't have to start carrying multiple versions?


I don't really need to upgrade, but I figured it would be an easy way to 
get my database running again and get me back up to current toys.  Of 
course, upgrades don't often work out that well...


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

Re: [GENERAL] mac ports question

[Tom Allison]

If it doesn't remove the 8.2 then I guess I can migrate it.
But that requires that I still need to get 8.2 running.


Right now it complains that it can't find a listening socket at /tmp/... 
(localhost mode).  And I can't find the configuration files in this set-up.


I do have this running:
/opt/local/bin/daemondo --label=postgresql82-server --start-cmd 
/opt/local/etc/LaunchDaemons/org.macports.postgresql82-server/postgresql82-server.wrapper 
start ; --stop-c


But that doesn't actually mean anything to me other that I guess it's 
trying to start.  And it's stuck somewhere.


And no logs.
And no config file to make the logs hyper verbose.

This stinks.  I can find all this stuff on my Debian boxes, but on this 
mac -- have no clue where things live.


Stephan Szabo wrote:

On Sat, 5 Apr 2008, Tom Allison wrote:


I ran into a problem today where somewhere my port of postgresql82 just
stopped working.  I'm largely an idiot on Mac because I use is as a
workstation/development box and do most of the real system related work
on my debian boxes.

But I don't know how to get the port working again.

Then I saw there is a new version 8.3 in port.

So, if I upgrade does anyone know if this cleanly removes version 8.2
from the box so I don't have to start carrying multiple versions?


It won't remove 8.2 automatically. You'd have to ask port to deactivate
and uninstall it. Unfortunately, you may find it complains about
dependencies when you do that.

Are you using the server as well, or just the client components? If the
server, AFAIK it also won't try to do any database migration, which
probably isn't a huge problem on a dev system, but could be.



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

Re: [GENERAL] Need to update all my 60 million rows at once without transactional integrity

[Tom Allison]

Far from being an expert on postgres, but there are two ideas-- 
assuming that you cannot afford the time it would take to simply  
UPDATE and wait...


Write a script to update all the rows, one at a time. Lowest impact to  
operations but would take a very long time.


Assuming you have a sequence primary key value on each row, update by  
ID blocks on the order of 10,000's or 100,000's at a time (or more).   
This is a balancing act between time to complete and immediate load on  
the server.


I've used both options but I don't think I've exceeded 15 million rows.

Sent from my iPhone.

On Apr 23, 2008, at 2:15 PM, Simon Riggs <[EMAIL PROTECTED]> wrote:


On Mon, 2008-04-21 at 00:19 +0200, [EMAIL PROTECTED] wrote:

How can I make a Update of a column in a very large table for all  
rows
without using the double amount of disc space and without any need  
for

atomic operation?

I have a very large table with about 60 million rows. I sometimes  
need

to do a simple update to ALL rows that resets a status-flag to zero.

I don't need to have transactional integrity (but of course if the
system crashes, there should be no data corruption.


No such thing. Without transactions you have no sensible definition of
what constitutes data corruption.


A separate flag in the file system can well save the fact that that
bulk update was in progress) for this, I don't care or want a abort  
or

"all or nothing" like SQL mandates. The idea is basically that either
this update succeeds or it succeeds or - there is no "not". It must
succeed. It must be tried until it works. If the update is halfway
finished, that's okay.


Don't reset them to zero, just redefine the meaning of the counter.  
Take

the max value in the table and then have the app understand that
anything <= the previous max value means the same thing as whatever
"status = 0" means now. The app would need to read the new baseline
value before performing any work.

This is roughly the technique used by Slony to avoid needing to update
every row in the log table to show that it has successfully replicated
it. It's also the technique MVCC relies upon internally.

It's less work and crash safe in all cases.

--
 Simon Riggs
 2ndQuadrant  http://www.2ndQuadrant.com


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


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

[GENERAL] inheritance...

[Tom Allison]

Ran into something really unexpected, but then I've never tried using 
inherited tables.


I have a master table (named master) that has two child tables.

create table master (
  id serial,
  foo varchar(20),
  bar varchar(20),
  constraint foobar_master unique (foo,bar)
);

Now when I do this with just a table, the unique constraint works.

But when I have child tables:

create table slave (
  status varchar(20),
  deleted boolean default false
) inherits (master);

I seem to lose that unique constraint.  Which makes for a HUGE problem.

Am I missing something in the fine print?

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

[GENERAL] inheritance. more.

[Tom Allison]

create table master (
id serial,
mdn varchar(11),
meid varchar(18),
min varchar(11),
constraint mmm_master unique (mdn, meid, min)
);
insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;


Everything works up to this point...

insert into master(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;


And this fails, like I would expect it to.


create table slave (
deleted boolean default false
) inherits (master);

insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;

insert into slave(mdn, meid, min)
select mdn, meid, min from test_data where meid != '00' 
limit 10;


I now have 30 rows in the master table, with duplicates...

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

Re: [GENERAL] SUMMARY: Solaved. apache perl cgi script cant load libpq.5.dylib on mac os tiger

[Tom Allison]

I found a similar problem only with a slight variation.

postgresql, for me, is installed in /opt/local/ with the particular file 
being located at:

/opt/local/lib/postgresql83/libpq.5.dylib

This is all a part of the mac ports installation for apache2 and postgres.

It seems that the problem isn't so much pgsql but apache2 and/or mac 
ports.  But would you (or anyone else) have a suggestion on how to 
proceed?  I could probably just make a lot of soft links, but that 
doesn't seem very robust.


Fergus McMenemie wrote:

Hmmm,

It was the protection on the /usr/local/pgsql directory. Solved, by myself
an hour after making this post. Odd that, its been bugging the heck out of
me for over a week now, and then one hour after posting



At 16:05 +0100 23/5/08, Fergus McMenemie wrote:

I have a perl cgi script that wont run on a new mac.  The script runs fine
from the command line, so the perl and postgresql seem good. However
when run as an apache cgi script the postgresql stuff fails as shown
below.  I altered the script to dump %ENV and the paths seem in order.

The script is not using mod perl. It is using the apple os x 10.4.11 supplied
version of apache, postgresql-8.3.0  and perl, v5.8.8 with DBD::Pg, DBI and
JSON installed.

I am at a total loss as to why the library cant be found and would be
very grateful for any help.

Content-type: text/html Software error:

install_driver(Pg) failed:
  Can't load 
'/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle'
 for module DBD::Pg: 
dlopen(/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle,
 1):
 Library not loaded: /usr/local/pgsql/lib/libpq.5.dylib
 Referenced from: 
/usr/local/lib/perl5/site_perl/5.8.8/darwin-2level/auto/DBD/Pg/Pg.bundle
 Reason: image not found at 
/usr/local/lib/perl5/5.8.8/darwin-2level/DynaLoader.pm line 230.
at (eval 45) line 3
 Compilation failed in require at (eval 45) line 3.
Perhaps a required shared library or dll isn't installed where expected at 
/Library/WebServer/cgi-bin/pgcgi.pl line 383

$ENV{AUTH_TYPE} = Basic
$ENV{DOCUMENT_ROOT} = /Library/WebServer/Documents
$ENV{DYLD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
$ENV{GATEWAY_INTERFACE} = CGI/1.1
$ENV{HTTPS} = on
$ENV{HTTP_ACCEPT} = 
text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,/;q=0.5
$ENV{HTTP_ACCEPT_CHARSET} = ISO-8859-1,utf-8;q=0.7,;q=0.7
$ENV{HTTP_ACCEPT_ENCODING} = gzip,deflate
$ENV{HTTP_ACCEPT_LANGUAGE} = en,en-us;q=0.5
$ENV{HTTP_CONNECTION} = keep-alive
$ENV{HTTP_HOST} = X.co.uk
$ENV{HTTP_KEEP_ALIVE} = 300
$ENV{HTTP_USER_AGENT} = Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; 
en-US; rv:1.8.1.14) Gecko/20080404 Firefox/2.0.0.14
$ENV{LD_LIBRARY_PATH} = /usr/local/pgsql/lib:/usr/lib:/usr/local/lib
$ENV{PATH} = 
/usr/local/pgsql/bin:/usr/local/bin:/bin:/sbin:/usr/bin:/usr/sbin
$ENV{QUERY_STRING} =
$ENV{REMOTE_ADDR} = XX
$ENV{REMOTE_PORT} = 53698
$ENV{REMOTE_USER} = fergus
$ENV{REQUEST_METHOD} = GET
$ENV{REQUEST_URI} = /cgi-bin/pgcgi.pl
$ENV{SCRIPT_FILENAME} = /Library/WebServer/cgi-bin/pgcgi.pl
$ENV{SCRIPT_NAME} = /cgi-bin/pgcgi.pl
$ENV{SERVER_ADDR} = 
$ENV{SERVER_ADMIN} = [EMAIL PROTECTED]
$ENV{SERVER_NAME} = X.co.uk
$ENV{SERVER_PORT} = 443
$ENV{SERVER_PROTOCOL} = HTTP/1.1
$ENV{SERVER_SIGNATURE} =  Apache/1.3.41 Server at .co.uk Port 443
$ENV{SERVER_SOFTWARE} = Apache/1.3.41 (Darwin) mod_ssl/2.8.31 OpenSSL/0.9.7l

--

===
Fergus McMenemie   Email:[EMAIL PROTECTED]
Techmore Ltd   Phone:(UK) 07721 376021

Unix/Mac/Intranets Analyst Programmer
===

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





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

Re: [GENERAL] NULL values seem to short-circuit my unique index

[Tom Allison]

You can always add a constraint that these columns cannot be NULL  
themselves. That removes this problem.


On Sep 28, 2008, at 11:17 PM, Klint Gore <[EMAIL PROTECTED]> wrote:


Matthew Wilson wrote:
I'm trying to comprehend how NULL values interact with unique  
indexes.
It seems like I can insert two rows with NULL values in a column  
with a

unique constraint just fine.

Is there something special about NULL?  Can anyone post some links to
explain what is going on?



http://www.postgresql.org/docs/8.3/interactive/ddl-constraints.html#AEN2058
Last paragraph just above 5.3.4.


What's the deal with NULL?



NULL = NULL is not true, it's null
NULL <> NULL is not false, it's null

It's the normal SQL 3 state logic (true/false/null) with only the  
true value from the comparison causing the constraint violation.   
Think of the unique constraint check like "does this value equal any  
other value already recorded".


klint.

--
Klint Gore
Database Manager
Sheep CRC
A.G.B.U.
University of New England
Armidale NSW 2350

Ph: 02 6773 3789  Fax: 02 6773 3266
EMail: [EMAIL PROTECTED]


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


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

[GENERAL] postgresql and Mac OS X

[Tom Allison]

Relatively simple question that I hope doesn't start too much "Flame".

I have recently had the opportunity to reformat my macbook hard drive, 
many thanks to suggestions from the actual Apple support team.  That's 
not why I'm writing to the postgres group...  But it's related.



I have a fresh slate from which to build my development platform!!


I can get postgresql installed in three flavors:
EnterpriseDB has a dmg package for Mac.
macports has their own package.
fink also has their own package.

I was using macports but got into a cluster-F on versions and multiple 
installs.  After a spell I had all four versions 8.0 - 8.3 installed in 
order to use postgres, ruby, perl, and rails together.


I'm interesting in knowing if this can be avoided by selecting one of 
the alternative sources of installation.  Any experiences with 
differences in installation and long term management from these sources?


I'm more accustomed to using Linux for PostgreSQL, but in this situation 
Linux probably won't be my development arena but test/prod.  In the 
Linux environment I've had great success in getting migrations, 
upgrades, and languages to play well with PostgreSQL without the multi 
version issue.


Many thanks in advance for all your input!

Tom

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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

Niklas Johansson wrote:


On 28 okt 2008, at 23.41, Tom Allison wrote:
I was using macports but got into a cluster-F on versions and multiple 
installs.  After a spell I had all four versions 8.0 - 8.3 installed 
in order to use postgres, ruby, perl, and rails together.


Do you mean that Macports installed different versions of Postgres 
because the other packages had different dependencies? Don't know if 
compiling from source would help you there, but surely there must be 
some way to tell the package manager that a certain dependency already 
exists, albeit somewhere else?





Yes.  Between different programing libraries to access postgresql I was 
getting caught up in multiple versions of the database itself.  Very 
unpleasant.


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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

Grzegorz Jaśkiewicz wrote:
I use postgresql on MBP, current head, for testing and development. Just 
from sources, it won't bite :)


you just have to add user postgres to your system, place $PGDATA 
wherever you feel you should, and you're done.




Yes.  I actually started using Nix from Slackware.  Which means, by 
definition, installation from scratch is "trivial".  I can see the value 
in doing an installation on your own because you do have absolute 
control over the version/options of the packages.


I guess my reluctance against compiling is that I have little interest 
in tuning development box and going through the nuances of 
configuration.  And as such -- plug & chug seems easy.


I think I found my answer though -- DIY.  It's the control and knowing I 
 have all the binaries and source code I need to.  now, wish me luck! 
I might be back on the list really soon...


:)

- Tom

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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

Grzegorz Jaśkiewicz wrote:
I feel good about control here, and I certainly don't have any problems. 
So, please don't whine :)
Especially since I want to run cvs head, and be able to actually update 
it from cvs when I want to, that's the only choice. Postgresql is so 
easy to get from sources, compared to other software packages, I can't 
understand people even with slightest expierence in unix to have any 
problems with it. 



I tried getting a source install on my mac book yesterday and today.
It's not a normal *nix installation.  The location of the files are all 
non-standard.

'make' is prefixed by /Developer/usr/bin/.

I added /Developer/usr/bin to PATH and tried ./configure.

checking build system type... i386-apple-darwin9.5.0
checking host system type... i386-apple-darwin9.5.0
checking which template to use... darwin
checking whether to build with 64-bit integer date/time support... no
checking whether NLS is wanted... no
checking for default port number... 5432
checking for gcc... gcc
checking for C compiler default output file name... configure: error: C 
compiler cannot create executables

See `config.log' for more details.


config.log shows an exit code of 77 with a statement that compiler 
cannot create executables.  ???



configure:2213: $? = 0
configure:2215: gcc -v &5
Using built-in specs.
Target: i686-apple-darwin9
Configured with: /var/tmp/gcc/gcc-5488~2/src/configure 
--disable-checking -enabl
e-werror --prefix=/usr --mandir=/share/man 
--enable-languages=c,objc,c++,obj-c++
 --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/ 
--with-gxx-include-dir=/includ
e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9 
--with-arch=apple

 --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple-darwin9
Thread model: posix
gcc version 4.0.1 (Apple Inc. build 5488)
configure:2218: $? = 0
configure:2220: gcc -V &5
gcc-4.0: argument to `-V' is missing
configure:2223: $? = 1
configure:2246: checking for C compiler default output file name
configure:2249: gccconftest.c  >&5
ld: library not found for -lcrt1.10.5.o
collect2: ld returned 1 exit status
configure:2252: $? = 1
configure: failed program was:


I think he questin is, what lib was missing so I can go find it and add 
it to some path/dir variable?


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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

Francisco Figueiredo Jr. wrote:

On Tue, Nov 4, 2008 at 8:21 AM, Tom Allison <[EMAIL PROTECTED]> wrote:

Grzegorz Jaśkiewicz wrote:

I feel good about control here, and I certainly don't have any problems.
So, please don't whine :)
Especially since I want to run cvs head, and be able to actually update it
from cvs when I want to, that's the only choice. Postgresql is so easy to
get from sources, compared to other software packages, I can't understand
people even with slightest expierence in unix to have any problems with it.

I tried getting a source install on my mac book yesterday and today.
It's not a normal *nix installation.  The location of the files are all
non-standard.
'make' is prefixed by /Developer/usr/bin/.

I added /Developer/usr/bin to PATH and tried ./configure.

checking build system type... i386-apple-darwin9.5.0
checking host system type... i386-apple-darwin9.5.0
checking which template to use... darwin
checking whether to build with 64-bit integer date/time support... no
checking whether NLS is wanted... no
checking for default port number... 5432
checking for gcc... gcc
checking for C compiler default output file name... configure: error: C
compiler cannot create executables
See `config.log' for more details.


config.log shows an exit code of 77 with a statement that compiler cannot
create executables.  ???


configure:2213: $? = 0
configure:2215: gcc -v &5
Using built-in specs.
Target: i686-apple-darwin9
Configured with: /var/tmp/gcc/gcc-5488~2/src/configure --disable-checking
-enabl
e-werror --prefix=/usr --mandir=/share/man
--enable-languages=c,objc,c++,obj-c++
 --program-transform-name=/^[cg][^.-]*$/s/$/-4.0/
--with-gxx-include-dir=/includ
e/c++/4.0.0 --with-slibdir=/usr/lib --build=i686-apple-darwin9
--with-arch=apple
 --with-tune=generic --host=i686-apple-darwin9 --target=i686-apple-darwin9
Thread model: posix
gcc version 4.0.1 (Apple Inc. build 5488)
configure:2218: $? = 0
configure:2220: gcc -V &5
gcc-4.0: argument to `-V' is missing
configure:2223: $? = 1
configure:2246: checking for C compiler default output file name
configure:2249: gccconftest.c  >&5
ld: library not found for -lcrt1.10.5.o
collect2: ld returned 1 exit status
configure:2252: $? = 1
configure: failed program was:


I think he questin is, what lib was missing so I can go find it and add it
to some path/dir variable?




I think you need to install the developer tools.

I compile postgresql from sources with no problem on osx 10.5.4 but I
installed developer tools before.

The library which is missing is the following:


configure:2246: checking for C compiler default output file name
configure:2249: gccconftest.c  >&5
ld: library not found for -lcrt1.10.5.o  <-


crt1.10.5.o

I hope it helps.







It confirms what I'm working through.

crt1.o located at /Developer/SDKs/MacOSX10.5.sdk/usr/lib/crt1.o
crt1.10.5.0 at /Developer/SDKs/MacOSX10.5.sdk/usr/lib/crt1.10.5.o

So I'm trying to find how to get these directories included in the 
compilation.  I thought --with-libs and/or --with-includes would have 
helped.  But it didn't.


This is what I ran (I'm running this from a script so I can repeat it)


--
cd /Users/tom/src/postgresql-8.3.4

export PATH=$PATH:/Developer/usr/bin/

./configure \
--with-libs=/Developer/SDKs/MacOSX10.5.sdk/usr/lib/ \
--with-includes=/Developer/SDKs/MacOSX10.5.sdk/usr/lib/



But I'm on the same error...

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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

Tom Lane wrote:

Tom Allison <[EMAIL PROTECTED]> writes:

I tried getting a source install on my mac book yesterday and today.
It's not a normal *nix installation.  The location of the files are all
non-standard.
'make' is prefixed by /Developer/usr/bin/.


The question is *why* the location is nonstandard.  Other people's Macs
are not set up that way (mine seems to have these files in the expected
place, for example).


I added /Developer/usr/bin to PATH and tried ./configure.


That would help configure find the stuff in /Developer/usr/bin, but
it does nothing for files that ought to be in /usr/lib, /usr/include,
etc.  I am not sure whether adding these to the configure command
would be sufficient:

--with-includes=/Developer/usr/include --with-libraries=/Developer/usr/lib

On the whole the best thing would be to toss /Developer and reinstall
your devtools in the standard places.  The nonstandard location is going
to bite you for every package you work with, not only Postgres.

regards, tom lane


I have installed xcode311_2517_developerdvd that I added after I 
installed the Leopard OS.  This was an upgrade from Tiger but that puked 
so I installed Leopard from scratch.


I will try installing this package again.
(note: Unix Tools is checked)



Running just ./configure

I got past that part...

And finished the configure.

So, the answer seems to be that I did not install the Unix Tools portion 
of the XCode tools.  Which naturally is so very obvious for installation 
of anything used to unix installations...  I did strictly the default 
installation.



Sorry to run everyone through these loops.  But now we all know 
something new about Mac OSX


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

[GENERAL] postgresql installation - PL/???

[Tom Allison]

This should be a dumb question:

--with-perl

I don't see that I have to do this in order to load pl/perl as a 
function/trigger language option.  So I should assume that this will 
compile pl/perl in rather than having it available as a loadable 
function.  Nice for optimizations?


I'm assuming this is the case because there is no option for something 
like PL/Ruby support, but Ruby is available as a loadable function.


And should I also be able to assume that PL/PgSQL is compiled into 
postgresql?



so I don't really need to use any particular flags, with the probable 
exception of --with-ssl?





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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

Scott Ribe wrote:

'make' is prefixed by /Developer/usr/bin/.

The question is *why* the location is nonstandard.


Starting with Xcode 3, all the developer tools get installed under the
Developer directory, in order to allow one to easily have multiple versions
of Xcode installed alongside each other. The question then is why the OP
doesn't also have make in /usr/bin, or why his path is configured so that it
finds /Developer/usr/bin first--*that* is what is non-standard.




There is an option during installation for a Unix Tools installation.
Which puts everything where it's expected.

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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

Tom Allison wrote:

Scott Ribe wrote:

'make' is prefixed by /Developer/usr/bin/.

The question is *why* the location is nonstandard.


Starting with Xcode 3, all the developer tools get installed under the
Developer directory, in order to allow one to easily have multiple 
versions

of Xcode installed alongside each other. The question then is why the OP
doesn't also have make in /usr/bin, or why his path is configured so 
that it

finds /Developer/usr/bin first--*that* is what is non-standard.




There is an option during installation for a Unix Tools installation.
Which puts everything where it's expected.


Made great progress on getting everything to work.
I'm down to one detail

It runs, it starts with the notebook on boot.
But there is no logging...

When I do the install script in contrib it says I have no rights to the 
directory.  It was in /usr/local/pgsql/data/log and I changed it to 
/usr/local/pgsql/log.  It was set as root.wheel with 755 permissions so 
I suspect it's mad at me because the postgres user was left in the cold.


Now - I have what I hope is the last question related to Mac OSX and not 
so much the Postgresql.  How do you start/stop services without 
rebooting the machine?  I tried launchctl but it's not listed.


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

Re: [GENERAL] postgresql and Mac OS X

[Tom Allison]

adam_pgsql wrote:
When I do the install script in contrib it says I have no rights to 
the directory.  It was in /usr/local/pgsql/data/log and I changed it 
to /usr/local/pgsql/log.  It was set as root.wheel with 755 
permissions so I suspect it's mad at me because the postgres user was 
left in the cold.


Have you switched on logging in postgresql.conf?



doh!

There's no postgresql.conf file, just a postgresql.conf.sample.

Guess I have to start from .sample and work my way up...

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

[GENERAL] pg_hba.conf

[Tom Allison]

Ran into a mystery that I can't seem to figure out


I want to authenticate using SSL for all external IP addresses that I have in my 
subnet.  I also want to be able to authenticate via non-SSL for localhost (not 
unix socket).


I thought something like this would work:

host   allall127.0.0.1/32 md5
hostsslallall192.168.0.1/24   md5

But I have a localhost client that can't log in because it keeps trying to 
authenticate via SSL.


What am I doing wrong?  It seems simple enough.

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

Re: [GENERAL] pg_hba.conf

[Tom Allison]

Tom Lane wrote:

Tom Allison <[EMAIL PROTECTED]> writes:

host   allall127.0.0.1/32 md5
hostsslallall192.168.0.1/24   md5

   ^^

That needs to be 192.168.0.0/24 ... as is, it won't match anything.

But I have a localhost client that can't log in because it keeps trying to 
authenticate via SSL.




Sorry, I mixed it up.

Copying from the pg_hba.conf:

# Database administrative login by UNIX sockets
local   all postgres  ident sameuser

# TYPE  DATABASEUSERCIDR-ADDRESS  METHOD

# "local" is for Unix domain socket connections only
local   all all   md5
# IPv4 local connections:
hostdbmail  all 127.0.0.1/32  md5
hostall all 192.168.1.0/24md5
hostall all 192.168.0.0/24md5
# IPv6 local connections:
hostall all ::1/128   md5



I would like to be able to set change the lines maching 192.168...
to

hostssl   all   all   192.168

and set ssl=true in postgres.conf

But when I do, the localhost connections try to do ssl first and then fail.

Setting
hostnossl  dbmail   all 127.0.0.1/32  md5

didn't seem to help but I might have missed something at the time.

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

  http://archives.postgresql.org/

[GENERAL] indexes

[Tom Allison]

I notice a lot of places where people use the approach of creating an index and 
a unique key like:


CREATE TABLE foo (
  idx SERIAL PRIMARY KEY,
  name varchar(32) UNIQUE NOT NULL
)

instead of
CREATE TABLE foo (
  name varchar(32) PRIMARY KEY
)

If the name is NEVER going to change, is there any advantage to doing this?
If there are many-to-many reference tables (like name-to-friends) is this any 
different?


I've seen this a lot, but I've always assumed that with the condition that 
'name' would NEVER change, there was no advantage.





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

Re: [GENERAL] indexes

[Tom Allison]

Ben wrote:
Yes, it does. So of course it depends on how you use it to know what's 
going to be more efficient. For instance, if the rows in this table 
contain strings of more than a few bytes, and more than a couple tables 
reference this table with a foreign key, then you will quickly start to 
save space by using a numeric primary key, even if it is an artificial 
construct.


For the kind of work I find myself doing, it's rare that it would be 
more efficient to not have the artificial construct. But that doesn't 
mean one is always better than the other.




So let me see if I understand this correctly.

If the real-world primary key is large (say up to 100 characters in length) then 
the disadvantage is that you are duplicating this referenced key in several 
other tables, each element taking up 100 characters.  Space is wasted when 
compared to int4 ID's.  But not really sure if this is a performance problem for 
SELECT except for the space required (varchar(128) vs. int4).


Having two keys, a primary_key of int4 and a unique key of varchar(128) would be 
very ugly on INSERT/DELETE/UPDATE queries because of the index overhead.



One table may have rows on the order of 100's.
Another table will be 1,000,000.
The many-to-many join would be..  100,000,000's
And maybe there I would have need for smaller physical index variable types...

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

  http://archives.postgresql.org/

Re: [GENERAL] PG Email Client

[Tom Allison]

Tino Wildenhain wrote:

Sim Zacks schrieb:
Are there any postgresql based email clients that anyone has 
experience with?



Well "client" would be the wrong approach imho, but there
is for example dbmail which can use postgres as storage
for mail and opens a lot of the usecases above. You would
continue to use your MUA for reading mail but some of the
mails could be handled by your application directly.



I'll second the dbmail application.  It's POP or IMAP server based.
But it does what you are looking for.

I used to use dovecot, which is extremely fast.
But, if you gave it too much to do it would hang for hours chewing up the hard 
drive and generating heat.


dbmail isn't as fast as dovecot for a single user, single event.  But not by 
much.  However, it scales *much* better into the 100's and 1000's of actions.


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

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

Re: [GENERAL] PG Email Client

[Tom Allison]

Alban Hertroys wrote:

Jean-Gérard Pailloncy wrote:

I do not have test numbers rigth now, because I need first to configure
the box for virtual hosting, then dspam.


It would be really wonderful if someone managed to combine this with
dspams own database. Maybe it'd be possible to pull dspam's algorithms
as a lib into the database?

I've been wanting an IMAP spam drop-box like forever, this seems like a
possibility to achieve this. Combine a spam/not-spam trigger to specific
IMAP boxes and make those update dspam's statistics - seems possible :)

All I'd need to do to mark something spam is drop the mail in the right
box from my client. The quarantine box could work similarly.



I would be careful about using dspam.

I have documented cases where it lost email and even with posting all the debug 
and other logs on the mailing lists I've never received any indication that they 
recognize this as a dspam problem.


dspam will lose your mail.

I wouldn't spend a second considering trying to merge a spam filtering 
application into an imap server.  It's just plain dumb.  You have lose all 
modularity of code or function and now you exponentially increase the 
probability that you will have defects and they will be exponentially more 
difficult to identify and fix.


Leave dbmail to do what it does and invent something else to do what other clear 
functionality you want available.  That's the only sane way to do this kind of 
development.


For example -- spam filtering in itself can require a very large box with 
extensive amounts of resources in terms of memory, cpu, disk IO  If you have 
that combined with a large dbmail installation and a postgres database what you 
end up with is wanting multiple boxes to disperse the loads around.
What you are describing here is the opposite.  Make one box do everything in the 
entire environment of email.


There are other things you can do to use imap folders and spam training that 
would make more sense.


The *one* feature I did like about dspam is they quarantine spam and you can 
sort it out later.  That can be done easily enough and won't interfere with 
dbmail if you don't want it to.


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

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

Re: [GENERAL] Database performance comparison paper.

[Tom Allison]

Leif B. Kristensen wrote:

On Friday 16. February 2007 07:10, Tom Lane wrote:


Perhaps this
paper can be described as "comparing an F-15 to a 747 on the basis of
required runway length".


There ought to be a proper name for this kind of pseudo-technical Gonzo 
journalism. The Internet is full of it.


advertalism?

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

[GENERAL] Debian upgrade and PGSQL pid file

[Tom Allison]

A while back I threw together a postgresql installation on a computer with a 
RAID disk for performance reasons.  I never got it working 100% since it was 
just a quick weekend adventure and I never expected much to come of it.



Famous last words...

I'm not trying to upgrade the database via debians recent release and ran into 
an error from the debian scripts:


Preparing to replace postgresql-8.1 8.1.5-1 (using 
.../postgresql-8.1_8.1.8-1_i386.deb) ...
Stopping PostgreSQL 8.1 database server: main* Error: pid file is invalid, 
please manually kill the stale server process.

 failed!
invoke-rc.d: initscript postgresql-8.1, action "stop" failed.
dpkg: warning - old pre-removal script returned error exit status 1
dpkg - trying script from the new package instead ...
Stopping PostgreSQL 8.1 database server: main* Error: pid file is invalid, 
please manually kill the stale server process.

 failed!
invoke-rc.d: initscript postgresql-8.1, action "stop" failed.


From the postgres config file I have:
# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.

data_directory = '/raid/postgresql/'# use data in another 
directory
hba_file = '/etc/postgresql/8.1/main/pg_hba.conf'   # host-based 
authentication file

ident_file = '/etc/postgresql/8.1/main/pg_ident.conf'   # IDENT configuration 
file

# If external_pid_file is not explicitly set, no extra pid file is written.
external_pid_file = '/var/run/postgresql/8.1-main.pid'  # write an extra 
pid file



This is my mount entry for my /raid/ partition
/dev/md0 on /raid type reiserfs (rw,noatime)


---
I actually have a pid file in both locations:
/raid/postgresql/postmaster.pid,
/var/run/postgresql/8.1-main.pid

So I guess it never reall worked right.
BTW: PG_VERSION says 8.1

And:
/raid/postgresql/ has a *lot* of files I was expecting to see somewhere else 
(like /etc/postgresql)


CoolerMaster:/raid/postgresql# ls -l
total 37
-rw--- 1 postgres postgres 4 Dec  4 03:35 PG_VERSION
drwx-- 9 postgres postgres   216 Jan 24 20:37 base
drwx-- 2 postgres postgres   672 Apr 10 06:00 global
drwx-- 2 postgres postgres96 Apr  1 19:14 pg_clog
-rw--- 1 postgres postgres  3396 Dec  4 03:35 pg_hba.conf
-rw--- 1 postgres postgres  1460 Dec  4 03:35 pg_ident.conf
drwx-- 4 postgres postgres96 Dec  4 03:35 pg_multixact
drwx-- 2 postgres postgres72 Apr  6 14:19 pg_subtrans
drwx-- 2 postgres postgres48 Dec  4 03:35 pg_tblspc
drwx-- 2 postgres postgres48 Dec  4 03:35 pg_twophase
drwx-- 3 postgres postgres   880 Apr 10 02:22 pg_xlog
-rw--- 1 postgres postgres 13682 Dec  4 03:35 postgresql.conf
-rw--- 1 postgres postgres   125 Mar  9 05:36 postmaster.opts
-rw--- 1 postgres postgres42 Mar  9 05:36 postmaster.pid
CoolerMaster:/raid/postgresql# cat PG_VERSION
8.1

My permissions setting in pg_hba.conf indicate that I'm using the pg_hba.conf 
from /etc/postgresql/... and not this one listed above.  So I'm not really sure 
what they are even doing here, but am hesitant to just delete them.


How should it work and how do I get it there?

---(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] permissions

[Tom Allison]

I am not sure how the permissions work anymore.

What I want to do is create a database and have a user create all the tables and 
rules.


I created the database from user postgres.
I then set the owner of the database to my userid

my userid created a number of tables and rules.

I then tried to migrate this database to a new user/owner.
But I keep getting

 permission denied for relation user_token


Which probably has to do with a relationship I have of referential integrity and 
a rule.  The rule updates a second table, which I have update/insert rights to.


It's got the be the rule.  I can do everything else, including what the rule is 
supposed to be.


how do a change the owner/permissions of the rule?

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

[GENERAL] understanding output from Explain

[Tom Allison]

"Seq Scan on"  - is this just a FULL TABLE scan?

I'm familiar with Oracles explain plans and terminology.
But not so much so with psql...

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

  http://archives.postgresql.org/

[GENERAL] pgsql and Mac OS X

[Tom Allison]

I'm trying to find the binaries for pgsql (the client) for Mac OSX.
Is there any way to get these without installing all of postgres on a  
computer?


I'm not going to use postgres on my MacBook, just connect to it.

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

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

Re: [GENERAL] pgsql and Mac OS X

[Tom Allison]

I found psql in
/System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/psql
which means that I can, as a user, access the database from a Mac.
But I'm still unable to build the perl modules for DBD:Pg support.
And this one seems a bit screwed up from default.
Port is 5433, not 5432.
pg_config shows it configured with a prefix path that doesn't exist:
/System/Library/CoreServices/RemoteManagement/sqldb

And I'm starting to think I'm way out of my league on how to get this  
working.


On Apr 30, 2007, at 3:45 PM, Erik Jones wrote:


On Apr 30, 2007, at 2:28 PM, Tom Allison wrote:


I'm trying to find the binaries for pgsql (the client) for Mac OSX.
Is there any way to get these without installing all of postgres  
on a computer?


I'm not going to use postgres on my MacBook, just connect to it.


If you have macports installed you can install the postgresql82  
port (not postgresql82-server).


erik jones <[EMAIL PROTECTED]>
software developer
615-296-0838
emma(r)




---(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



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

Re: [GENERAL] pgsql and Mac OS X

[Tom Allison]

On Apr 30, 2007, at 5:20 PM, Randal L. Schwartz wrote:


"Tom" == Tom Allison <[EMAIL PROTECTED]> writes:


Tom> /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/ 
psql


That's not on my mac.  Must be some bolt-on you installed.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503  
777 0095

<[EMAIL PROTECTED]> http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl  
training!


So how did you get it working?
(I have no idea how this file got there.  Wouldn't know where to begin)

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

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

Re: [GENERAL] pgsql and Mac OS X

[Tom Allison]

That might be the thing to do.
I'm wondering how Apple Remote Desktop got onto my machine and how to  
remove it.


On Apr 30, 2007, at 5:38 PM, Michael Glaesemann wrote:



On Apr 30, 2007, at 16:20 , Randal L. Schwartz wrote:


"Tom" == Tom Allison <[EMAIL PROTECTED]> writes:


Tom> /System/Library/CoreServices/RemoteManagement/rmdb.bundle/bin/ 
psql


That's not on my mac.  Must be some bolt-on you installed.


Apple Remote Desktop uses PostgreSQL as it's data store (at least  
through version 2). I believe it's PostgreSQL 7.3, so the psql  
binary isn't going to be much use in connecting to newer PostgreSQL  
servers, and in any event I'm pretty sure the Remote Desktop  
installation does not include the libraries necessary to build the  
Perl modules, even if they were up to date. Better just to install  
via MacPorts or even from source: it builds pretty easily on Mac OS X.


Michael Glaesemann
grzm seespotcode net





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

  http://archives.postgresql.org/

Re: [GENERAL] pgsql and Mac OS X

[Tom Allison]

Please update the Randall Notebook to read:
sudo fink install dbd-pg-unified-pm586

Perhaps this will be done in time for YAPC?

On Apr 30, 2007, at 6:22 PM, Randal L. Schwartz wrote:


"Tom" == Tom Lane <[EMAIL PROTECTED]> writes:


Tom> What you can do if you want to build PG from source is build  
normally

Tom> but only install the client programs.  The Fine Manual recommends

Tom>  gmake -C src/bin install
Tom>  gmake -C src/include install
Tom>  gmake -C src/interfaces install
Tom>  gmake -C doc install

Tom> instead of the usual "gmake install".

The Randal Notebook recommends:

  fink install postgresql

:-)

Then you get automatic startup on boot, usernames added, etc.

--
Randal L. Schwartz - Stonehenge Consulting Services, Inc. - +1 503  
777 0095

<[EMAIL PROTECTED]> http://www.stonehenge.com/merlyn/>
Perl/Unix/security consulting, Technical writing, Comedy, etc. etc.
See PerlTraining.Stonehenge.com for onsite and open-enrollment Perl  
training!



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

Re: [UNSURE] Re: [GENERAL] Streaming large data into postgres [WORM like applications]

[Tom Allison]

One approach would be to spool all the data to a flat file and then  
pull them into the database as you are able to.  This would give you  
extremely high peak capability.



On May 11, 2007, at 10:35 PM, Dhaval Shah wrote:


I do care about the following:

1. Basic type checking
2. Knowing failed inserts.
3. Non-corruption
4. Macro transactions. That is a minimal read consistency.

The following is not necessary

1. Referential integrity

In this particular scenario,

1. There is a sustained load and peak loads. As long as we can handle
peak loads, the sustained loads can be half of the quoted figure.
2.  The row size has limited columns. That is, it is spans at most a
dozen or so columns and most integer or varchar.

It is more data i/o heavy rather than cpu heavy.

Regards
Dhaval

On 5/11/07, Ben <[EMAIL PROTECTED]> wrote:

Inserting 50,000 rows a second is, uh... difficult to do, no matter
what database you're using. You'll probably have to spool the inserts
and insert them as fast as you can, and just hope you don't fall too
far behind.

But I'm suspecting that you aren't going to be doing much, if any,
referential integrity checking, at least beyond basic type checking.
You probably aren't going to care about multiple inserts affecting
each other, or worry about corruption if a given insert fails... in
fact, you probably aren't even going to need transactions at all,
other than as a way to insert faster. Is SQL the right tool for you?

On May 11, 2007, at 1:43 PM, Dhaval Shah wrote:

> Here is the straight dope, one of internal teams at my customer  
site

> is looking into MySql and replacing its storage engine so that they
> can store large amount of streamed data. The key here is that  
the data

> they are getting is several thousands of rows in an extremely short
> duration. They say that only MySql provides them the ability to
> replace the storage engine, which granted is easier.
>
> If I go with the statement that postgres can basically do what they
> intend to do for handling large datasets, I need to prepare my  
talking

> points.
>
> The requirements are as follows:
>
> 1. Large amount of streamed rows. In the order of @50-100k rows per
> second. I was thinking that the rows can be stored into a file  
and the
> file then copied into a temp table using copy and then appending  
those
> rows to the master table. And then dropping and recreating the  
index

> very lazily [during the first query hit or something like that]
>
> The table size can grow extremely large. Of course, if it can be
> partitioned, either by range or list.
>
> 2. Most of the streamed rows are very similar. Think syslog rows,
> where for most cases only the timestamp changes. Of course, if the
> data can be compressed, it will result in improved savings in  
terms of

> disk size.
>
> The key issue here is that the ultimate data usage is Write Once  
Read
> Many, and in that sense I am looking for a very optimal solution  
for

> bulk writes and maintaining indexes during bulk writes.
>
> So with some intelligent design, it is possible to use postgres.  
Any

> help in preparing my talking points is appreciated.
>
> Regards
> Dhaval
>
> ---(end of
> broadcast)---
> TIP 5: don't forget to increase your free space map settings





--
Dhaval Shah

---(end of  
broadcast)---

TIP 6: explain analyze is your friend



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

[GENERAL] postgres upgrade

[Tom Allison]

I'm trying to upgrade from 8.1 to 8.2.

Apparently the upgrade I just ran through installed a second database  
on my server.


I was hoping that the debian configuration would migrate this for me.
I recall there was once a time when it would ask you about moving  
data from old to new databases.  Is there some way I can do that?


Meanwhile, can I just use pg_dumpall to pull from 8.1 and then reload  
into 8.2?


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

[GENERAL] stuck on values in 8.2

[Tom Allison]

OK, after reviewing many emails and what I was trying to do I  
upgraded from 8.2.


Seems to work as it did in 8.1 which is a good start.

I'm doing all of this so I can use the 'values'  that was described  
as being something like:


select * from (values ('one','two','three')) "foo";

But instead of getting three rows of one value each, I get three  
columns of one row.

Not what I was looking for. :(

What I am doing now is run a large list (300+) of values that is  
being iterated through a sql statement like:  'select * from mytable  
where token = ?' to see if the value exists or not and then "do stuff".


A while back I was told that this would all be fixed if I used values 
(...)


Did I misunderstand the proper care and feeding of the VALUES  
statement or is there something else I need to do here?


I initially thought that I could do this with:
select t.value, v.value from
values('one','two','three') left outer join mytable using (value)

except
-- not sure how to name the column in values() to come out as name  
"value"
-- and quite frankly I can't get anything to actually run other than  
my first example.


Ideally the output would have something like:
v.value | t.value
--+-
one   |
two|  two
three|  three

where the value 'one' is not in the table...

Am I still able to do something like this but don't have the syntax  
correct?

Or am I more SOL than RTFM?

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

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

Re: [GENERAL] stuck on values in 8.2

[Tom Allison]

This is getting really ugly...
it won't finish in less than .. minutes.

spam=> explain select u2.token_idx, t.token_idx, foo.token from
tokens t left outer join user_token u1 using (token_idx),
users u left outer join user_token u2 using (user_idx),
(values('one'),('want'),('examine'),('three')) as foo(token)
left outer join tokens using (token)
where u.user_idx = 15;
  QUERY PLAN
 
--

Nested Loop Left Join  (cost=423.70..4327392.26 rows=216168368 width=48)
   ->  Nested Loop  (cost=250.49..3851.69 rows=30472 width=44)
 ->  Hash Left Join  (cost=249.40..3241.16 rows=30472 width=40)
   Hash Cond: (t.token_idx = u1.token_idx)
   ->  Nested Loop  (cost=0.00..967.34 rows=29908 width=40)
 ->  Nested Loop Left Join  (cost=0.00..33.18  
rows=4 width=32)
   ->  Values Scan on  
"*VALUES*"  (cost=0.00..0.05 rows=4 width=32)
   ->  Index Scan using tokens_token_key on  
tokens  (cost=0.00..8.27 rows=1 width=16)
 Index Cond: ("*VALUES*".column1 =  
(tokens.token)::text)
 ->  Seq Scan on tokens t  (cost=0.00..158.77  
rows=7477 width=8)

   ->  Hash  (cost=145.29..145.29 rows=8329 width=8)
 ->  Seq Scan on user_token u1   
(cost=0.00..145.29 rows=8329 width=8)

 ->  Materialize  (cost=1.09..1.10 rows=1 width=4)
   ->  Seq Scan on users u  (cost=0.00..1.09 rows=1  
width=4)

 Filter: (user_idx = 15)
   ->  Materialize  (cost=173.21..244.15 rows=7094 width=12)
 ->  Seq Scan on user_token u2  (cost=0.00..166.11 rows=7094  
width=12)

       Filter: (user_idx = 15)
(18 rows)

On May 12, 2007, at 11:08 AM, Gregory Stark wrote:



"Tom Allison" <[EMAIL PROTECTED]> writes:

OK, after reviewing many emails and what I was trying to do I  
upgraded from 8.2.


Seems to work as it did in 8.1 which is a good start.

I'm doing all of this so I can use the 'values'  that was  
described as being

something like:

select * from (values ('one','two','three')) "foo";


SELECT * FROM (VALUES ('one'),('two'),('three')) AS foo(value)


I initially thought that I could do this with:
select t.value, v.value from
values('one','two','three') left outer join mytable using (value)


  postgres=# SELECT *
FROM (VALUES ('one'),('two'),('three')) AS foo(value)
LEFT OUTER JOIN mytable ON (foo.value = mytable.value);

   value | value
  ---+---
   one   |
   two   | two
   three | three
  (3 rows)

"USING" would work too but then you only get one output column  
rather than two

which is not so helpful in this case.

  postgres=# SELECT *
FROM (VALUES ('one'),('two'),('three')) AS foo(value)
LEFT OUTER JOIN mytable USING (value) ;

   value
  ---
   one
   two
   three
  (3 rows)

--
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com


---(end of  
broadcast)---

TIP 4: Have you searched our list archives?

   http://archives.postgresql.org/



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

Re: [GENERAL] stuck on values in 8.2

[Tom Allison]

I agree I'm going in the wrong direction.

in a Venn sort of way, what I'm trying to do is:

values(...) --> left outer --> tokens --> left outer --> (user_tokens  
where user_tokens.user_idx = users.user_idx and users.user_idx = 4)


To give me a list of
all values ||  any matching token || any matching user_token where  
user_idx = 4


something like:
SELECT values.token, t.token_idx, ut.token_idx
FROM
(values('one'),('want'),('examine'),('three')) as values(token)
left outer join tokens t using (token)
left outer join ( select token_idx from user_token where  
user_idx = 14) "ut"

using (token_idx)
;

That seems to be better.
I think the part I was trying to get my brain around was how, in  
postgres, do you do multiple outer joins.
On my day job I do this in Oracle without thinking, but the syntax of  
postgres is new to me.
Like doing dates.  Everyone has a different way of doing dates and  
they are all weird.


Now I have to go impliment it into my code and see what it actually  
does.

I'm hoping to peel 3-5 seconds off each process!

On May 12, 2007, at 12:06 PM, Richard Broersma Jr wrote:



--- Tom Allison <[EMAIL PROTECTED]> wrote:


This is getting really ugly...
it won't finish in less than .. minutes.

spam=> explain select u2.token_idx, t.token_idx, foo.token from
tokens t left outer join user_token u1 using (token_idx),
users u left outer join user_token u2 using (user_idx),
(values('one'),('want'),('examine'),('three')) as foo(token)
left outer join tokens using (token)
where u.user_idx = 15;


It looks to me that your query has (3) left joins and (3) implied  
cross-joins.  Perhaps reforming
your query to eliminate the cross-joins with help performance.  In  
regard to your tables 
and , in this query you are referring to (2) separate  
instances of these tables when

a single instance these tables would probably work just fine. i.e.

tokens t vs. tokens,
user_token u1 vs user_token u2

Regards,
Richard Broersma Jr.

---(end of  
broadcast)---

TIP 6: explain analyze is your friend



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

  http://archives.postgresql.org/

Re: [GENERAL] stuck on values in 8.2

[Tom Allison]

Thank you very much for all your help!!!

Solved this one rather nicely:

my $glue = q{'),(E'};

my $string = "(E'" . join($glue, map{quotemeta } @$tokens) . "')";

my $sql =<( select token_idx from user_token where user_idx = $self-> 
{user_idx}) "ut"

using (token_idx)
SQL

$sql =~ s/TOKEN_LIST_STRING/$string/o;

-
This is something on the order to 10-1000 times faster depending on  
the various result sets matching more/less across the various tables.

"very nice..."

I'm thinking I could do the same thing where I have a lot of inserts.
But this is going to be considerably harder because I don't have any  
really good experience with doing this with transactional integrity.


From this query, I eventually want to grab all the values.token and  
insert them into the token and user_token table.


I can call:
insert into tokens(token) values(('one'),('two'),('three'));
then call:
insert into user_token(user_idx, token_idx)
select 14, token_idx from tokens where token in 
('one','two','three')

And that should work.
HOWEVER:
If I have two simultaneous INSERT INTO token(token) queries with  
overlapping values I'm going to get into all kinds of trouble with  
the integrity constraint on
my index of unique tokens.  Typically I'll get an integrity violation  
error.


Or am I looking at something like:
insert into tokens
select ..
values(..) as values(token)
left outer join tokens using (token)

(Am I getting the hang of this any better?)



On May 12, 2007, at 12:06 PM, Richard Broersma Jr wrote:



--- Tom Allison <[EMAIL PROTECTED]> wrote:


This is getting really ugly...
it won't finish in less than .. minutes.

spam=> explain select u2.token_idx, t.token_idx, foo.token from
tokens t left outer join user_token u1 using (token_idx),
users u left outer join user_token u2 using (user_idx),
(values('one'),('want'),('examine'),('three')) as foo(token)
left outer join tokens using (token)
where u.user_idx = 15;


It looks to me that your query has (3) left joins and (3) implied  
cross-joins.  Perhaps reforming
your query to eliminate the cross-joins with help performance.  In  
regard to your tables 
and , in this query you are referring to (2) separate  
instances of these tables when

a single instance these tables would probably work just fine. i.e.

tokens t vs. tokens,
user_token u1 vs user_token u2

Regards,
Richard Broersma Jr.



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

Re: [GENERAL] stuck on values in 8.2

[Tom Allison]

I think I fixed the rest of my sql statements with the following:

insert into tokens (token)select values.token
from (values TOKEN_LIST_STRING ) as values(token)
left outer join tokens t using (token)
where t.token_idx is null

insert into user_token(user_idx, token_idx)select $self->{user_idx},  
token_idxfrom tokens left outer join (select token_idx from  
user_token where user_idx = $self->{user_idx}) ut using (token_idx)

where ut.token_idx is null
and token_idx in ($string)


I think does what I was trying to accomplish.  At least the little  
test sql seems to work.


interestingly, the time to process has gone from >100s to <1s.



On May 12, 2007, at 2:23 PM, Tom Allison wrote:


Thank you very much for all your help!!!

Solved this one rather nicely:

my $glue = q{'),(E'};

my $string = "(E'" . join($glue, map{quotemeta } @$tokens) . "')";

my $sql =<( select token_idx from user_token where user_idx = $self-> 
{user_idx}) "ut"

using (token_idx)
SQL

$sql =~ s/TOKEN_LIST_STRING/$string/o;

-
This is something on the order to 10-1000 times faster depending on  
the various result sets matching more/less across the various tables.

"very nice..."

I'm thinking I could do the same thing where I have a lot of inserts.
But this is going to be considerably harder because I don't have  
any really good experience with doing this with transactional  
integrity.


From this query, I eventually want to grab all the values.token and  
insert them into the token and user_token table.


I can call:
insert into tokens(token) values(('one'),('two'),('three'));
then call:
insert into user_token(user_idx, token_idx)
		select 14, token_idx from tokens where token in  
('one','two','three')


And that should work.
HOWEVER:
If I have two simultaneous INSERT INTO token(token) queries with  
overlapping values I'm going to get into all kinds of trouble with  
the integrity constraint on
my index of unique tokens.  Typically I'll get an integrity  
violation error.


Or am I looking at something like:
insert into tokens
select ..
values(..) as values(token)
left outer join tokens using (token)

(Am I getting the hang of this any better?)



On May 12, 2007, at 12:06 PM, Richard Broersma Jr wrote:



--- Tom Allison <[EMAIL PROTECTED]> wrote:


This is getting really ugly...
it won't finish in less than .. minutes.

spam=> explain select u2.token_idx, t.token_idx, foo.token from
tokens t left outer join user_token u1 using (token_idx),
users u left outer join user_token u2 using (user_idx),
(values('one'),('want'),('examine'),('three')) as foo(token)
left outer join tokens using (token)
where u.user_idx = 15;


It looks to me that your query has (3) left joins and (3) implied  
cross-joins.  Perhaps reforming
your query to eliminate the cross-joins with help performance.  In  
regard to your tables 
and , in this query you are referring to (2) separate  
instances of these tables when

a single instance these tables would probably work just fine. i.e.

tokens t vs. tokens,
user_token u1 vs user_token u2

Regards,
Richard Broersma Jr.



---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster



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

  http://archives.postgresql.org/

[GENERAL] autovacuum

[Tom Allison]

This database is working GREAT!!!

I noticed that under 8.2 the autovacuum isn't running (logging) every  
60s like I'm used to seeing.
I pretty much just took the defaults in the postgresql.conf file  
since that's always seemed to work before.


I'm not making a lot of changes to the database right now (insert/ 
update/delete) but I thought I would still get the logging.


any thing I can check?

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

Re: [UNSURE] Re: [GENERAL] autovacuum

[Tom Allison]

From what you described, I am running with autovacuum.

Makes sense to make a good idea default in the distro builds...

On May 13, 2007, at 9:49 AM, Michael Fuhr wrote:


On Sat, May 12, 2007 at 03:48:14PM -0400, Tom Allison wrote:

I noticed that under 8.2 the autovacuum isn't running (logging) every
60s like I'm used to seeing.


See the 8.2 Release Notes:

http://www.postgresql.org/docs/8.2/interactive/release-8-2.html

  * Remove routine autovacuum server log entries (Bruce)

pg_stat_activity now shows autovacuum activity.

In 8.2 the "autovacuum: processing database" messages are logged
at DEBUG1; in 8.1 they were logged at LOG.


I pretty much just took the defaults in the postgresql.conf file
since that's always seemed to work before.


Autovacuum was first incorporated into the backend in 8.1 and it's
disabled by default in 8.1 and 8.2, at least in source builds (it
might be enabled by default in some pre-packaged distributions).
What do you have in postgresql.conf for the following settings?

autovacuum
autovacuum_naptime
stats_start_collector
stats_row_level
log_min_messages

Do you see any warnings like the following in the server logs?

WARNING:  autovacuum not started because of misconfiguration
HINT:  Enable options "stats_start_collector" and "stats_row_level".


I'm not making a lot of changes to the database right now (insert/
update/delete) but I thought I would still get the logging.


If you have autovacuum and row-level statistics enabled then
autovacuum should be running.  I'd guess you aren't seeing the
routine messages because they're logged at DEBUG1 and you have
log_min_messages at a level that doesn't show debug messages.

--  
Michael Fuhr


---(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



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

  http://archives.postgresql.org/

[GENERAL] What does this error mean?

[Tom Allison]

Cannot mix placeholder styles ":foo" and "$1" at / 
sw/lib/perl5/5.8.6/darwin-thread-multi-2level//DBD/Pg.pm line 174.



I keep finding it from time to time on one script I have and I have  
no clue what it's telling me.


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

[GENERAL] bytea & perl

[Tom Allison]

I've been running into problems with some characters that I believe  
can be solved using bytea variable type instead of varchar()

I'm picking up data from email and trying to put it into a table.

I'm trying to "merge" two different types of SQL and I'm really not  
sure how this can be done...


I had a previous version of my SQL that looked like:
my $sth = $dbh->prepare("insert into quarantine values (?,?)");
$sth->bind_param(1, $idx);
$sth->bind_param(2, $text, { pg_type => DBD::Pg::PG_BYTEA });
$sth->execute();
In this case I was inserting an entire email content into the second  
parameter as type bytea.  Originally I was doing this as text and  
running into problems inserting records when there were weird characers.
I want to be able to use the BYTEA data type for inserting records  
into another SQL that looks like:


my $sql=

Re: [GENERAL] Integrity on large sites

[Tom Allison]

On May 23, 2007, at 12:54 PM, Scott Ribe wrote:

"*Really* big sites don't ever have referential integrity. Or if  
the few
spots they do (like with financial transactions) it's implemented  
on the
application level (via, say, optimistic locking), never the  
database level."


Pure, utter, unadulterated bullshit. Speaking as someone who had  
years of
experience with Sybase SQL Server before either MySQL or PostgreSQL  
were

even created...

Some big sites do of course juggle performance vs in-database run-time
checks, but the statements as typically presented by MySQL  
partisans, that
it's never done in the database level, is just wrong. Whether it's  
a direct
deception, iow speaker knows it to be false, or an indirect  
deception, iow
speaker is implying a level of expertise he does not possess,  
either way I

would categorize it as a lie.


I concur with the claim of organic fertilizer.

I got into a rather spicy argument at the only RAILS conference I  
went to.  They have this mentality that with Rails you don't need to  
put in RI on the database because you can always run exists? checks  
right before you do the insert to ensure integrity of your data.  Not  
only does this apply to Referential Integrity, but also unique  
values.  I was damn near screaming at them over the stupidity of such  
a notion.


My experience is based on working at a rather large company that has  
a really huge Oracle database.
When they designed it, they passed up on all Referential integrity  
and all unique constraints.
After five years, we have tables that are >60% duplicate records and  
the database is coming to a standstill.

And there is no known method in sight on being able to fix this one.

Bottom line, if the DBA or anyone says we can't support RI or UNIQUE  
because of the performance overhead...  I would be inclined to look  
for another DBA.
But I have to admit.  I am extremely opinionated about this as I'm  
the guy who does most of the performance and metric reporting using  
these horrid tables.
it does provide infinite job security, but it's hardly worth it in  
the long run.



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

Re: [GENERAL] What does this error mean?

[Tom Allison]

The " is definitely not the error -- it's a CGI thing.

I think this might  tie back to another email I posted where I'm  
trying to escape all these characters programmatically instead of  
passing them in as '?' and letting perl to the parsing for me.


On May 23, 2007, at 8:00 PM, [EMAIL PROTECTED] wrote:



Hi,

If you have a statement using placeholders like:

 select id from info where device_type = ? and drive_mfg = ?

and then prepare and execute it, something like:
   $sth=$dbh->prepare($stmt) ||  errexit("bad prepare for stmt  
$stmt, error: $DBI::errstr");
   $rc=$sth->execute('TYPE1','ACME') ||  errexit("can't execute  
statement:\n$stmt\nreturn code $rc: DB error: $DBI::errstr");


If the prepare thinks that the placeholders are of mixed types (as  
supported by the driver),  like:


select id from info where device_type = ? and drive_mfg = :2

Then, you will get the type of message you saw.

Probably your query is being built by the program where some odd  
character is occasionally creeping in where you don't expect it.  I  
assume that the html entity references ('"') are not actually  
in the error message.



Susan Cassidy



Tom Allison <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/23/2007 03:59 PM

To
General PostgreSQL List 
cc
Subject
[GENERAL] What does this error mean?





Cannot mix placeholder styles ":foo" and "$1" at /
sw/lib/perl5/5.8.6/darwin-thread-multi-2level//DBD/Pg.pm line 174.


I keep finding it from time to time on one script I have and I have
no clue what it's telling me.

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster



-- 


Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at  
http://www.overlandstorage.com
-- 

Re: [GENERAL] What does this error mean?

[Tom Allison]

I noticed that the option for 'E' is not enabled in 8.2 by default  
(  standard_conforming_strings (boolean)  ).
I turned this on and the SQL statements went from 0.5 sec to 3.8 sec  
each.

Is this expected?

On May 23, 2007, at 8:00 PM, [EMAIL PROTECTED] wrote:



Hi,

If you have a statement using placeholders like:

 select id from info where device_type = ? and drive_mfg = ?

and then prepare and execute it, something like:
   $sth=$dbh->prepare($stmt) ||  errexit("bad prepare for stmt  
$stmt, error: $DBI::errstr");
   $rc=$sth->execute('TYPE1','ACME') ||  errexit("can't execute  
statement:\n$stmt\nreturn code $rc: DB error: $DBI::errstr");


If the prepare thinks that the placeholders are of mixed types (as  
supported by the driver),  like:


select id from info where device_type = ? and drive_mfg = :2

Then, you will get the type of message you saw.

Probably your query is being built by the program where some odd  
character is occasionally creeping in where you don't expect it.  I  
assume that the html entity references ('"') are not actually  
in the error message.



Susan Cassidy



Tom Allison <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/23/2007 03:59 PM

To
General PostgreSQL List 
cc
Subject
[GENERAL] What does this error mean?





Cannot mix placeholder styles ":foo" and "$1" at /
sw/lib/perl5/5.8.6/darwin-thread-multi-2level//DBD/Pg.pm line 174.


I keep finding it from time to time on one script I have and I have
no clue what it's telling me.

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster



-- 


Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us at  
http://www.overlandstorage.com
-- 

Re: [GENERAL] What does this error mean?

[Tom Allison]

never mind.  first query after a restart is slow...

On May 23, 2007, at 10:14 PM, Tom Allison wrote:

I noticed that the option for 'E' is not enabled in 8.2 by default  
(  standard_conforming_strings (boolean)  ).
I turned this on and the SQL statements went from 0.5 sec to 3.8  
sec each.

Is this expected?

On May 23, 2007, at 8:00 PM, [EMAIL PROTECTED] wrote:



Hi,

If you have a statement using placeholders like:

 select id from info where device_type = ? and drive_mfg = ?

and then prepare and execute it, something like:
   $sth=$dbh->prepare($stmt) ||  errexit("bad prepare for stmt  
$stmt, error: $DBI::errstr");
   $rc=$sth->execute('TYPE1','ACME') ||  errexit("can't  
execute statement:\n$stmt\nreturn code $rc: DB error: $DBI::errstr");


If the prepare thinks that the placeholders are of mixed types (as  
supported by the driver),  like:


select id from info where device_type = ? and drive_mfg = :2

Then, you will get the type of message you saw.

Probably your query is being built by the program where some odd  
character is occasionally creeping in where you don't expect it.   
I assume that the html entity references ('"') are not  
actually in the error message.



Susan Cassidy



Tom Allison <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
05/23/2007 03:59 PM

To
General PostgreSQL List 
cc
Subject
[GENERAL] What does this error mean?





Cannot mix placeholder styles ":foo" and "$1"  
at /

sw/lib/perl5/5.8.6/darwin-thread-multi-2level//DBD/Pg.pm line 174.


I keep finding it from time to time on one script I have and I have
no clue what it's telling me.

---(end of  
broadcast)---

TIP 2: Don't 'kill -9' the postmaster



- 
-

Simply protected storage solutions ensure that your information is
automatically safe, readily available and always there, visit us  
at http://www.overlandstorage.com
- 
-

Re: [GENERAL] why postgresql over other RDBMS

[Tom Allison]

You've addressed cost and performance.
Not much left.

Try it out for yourself and see if it works for you.

On May 24, 2007, at 4:06 AM, Jasbinder Singh Bali wrote:


Hi
I was wondering, apart from extensive procedural language support  
and being free,
what are other major advantages of Postgresql over other major  
RDBMS like oracle and sql server.


Any pointers would be highly appreciated.

Thanks,
~Jas



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

[GENERAL] using bytea vartype non-prepared statements

[Tom Allison]

I would like to build a sql statement in perl and execute it without  
binding parameters if possible.
But I also need to use bytea variable type because I'm storing two  
byte characters (Big5, utf8...)


In case of using a varchar and ASCII I would simply write a sql  
statement like this:


INSERT INTO foo values('bar');
by building the SQL ...
my $sql = "INSERT INTO foo VALUES('$string')";
$dbh->do($sql);

I'm not sure if I can even do this if the underlying table has a  
field of type bytea.


I was reading in the archives an entry which said I may not be able  
to do this because the variable $string might contain null characters...


"If you are asking why the bind has to happen at all, it is partly  
because

libpq does not support returning the data types yet, and partly because
unlike most other data types, it is very important that DBD::Pg (and  
libpq,

and the backend) be told explicitly that a binary string is being used,
so that the length can be sent, as a null character may not represent  
the

end of the string."

In order to address this I was using a SQL statement previously where  
I knew that the number of parameters was only two and I could write  
the perl to handle this:

my $sth = $dbh->prepare("insert into quarantine values (?,?)");
$sth->bind_param(1, $idx);
$sth->bind_param(2, $text, { pg_type => DBD::Pg::PG_BYTEA });
$sth->execute();

In this case, I don't actually know before hand just how many  
variables I need to bind.  Rather, I don't know at compile time.


Using these examples I am not sure how I can incorporate building a  
SQL string like the first INSERT statement which will be able to  
correctly handle byte data.
Or is this a case of my reading too much into it and just leaving  
things up to the "magic" of the libraries to sort it out?


---(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 postgresql over other RDBMS

[Tom Allison]

I think the general topic got lost in the weeds there...

But it would be helpful if you identified what you care about in a  
database.  That drives a lot of these decisions.


Example:  My brother uses MySQL and I use Postgresql.
His reasons for MySQL:
More books on it at the bookstore.
It was already a default build in Rails.
Came installed on his SuSE build.
Didn't like the pg_hba.conf file and preferred to do all the  
permissions via SQL tables.


My reasons for Postgresql:
Very focused on consistent data and not losing information when  
things go wrong.
The performance curve as you add users is much flatter than MySQL.   
Under MySQL you have better speed with ~3 users but at 50 users  
Postgresql is a clear winner on my hardware.  In general I found it  
to scale smoother without a sweet spot.

I prefer pg_hba.conf over tables.
Zero maintenance & great tools.
It's more SQL standard so what I do on Oracle I can generally do on  
Postgresql and visa versa.


None of this has anything to do with Oracle or SQL Server, but these  
are examples of what is important to each of us.


What's important to you?

On May 24, 2007, at 4:06 AM, Jasbinder Singh Bali wrote:


Hi
I was wondering, apart from extensive procedural language support  
and being free,
what are other major advantages of Postgresql over other major  
RDBMS like oracle and sql server.


Any pointers would be highly appreciated.

Thanks,
~Jas



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

Re: [GENERAL] bytea & perl

[Tom Allison]

I think the quote() part for bytes is deprecated already?

my $string = "(" . join($glue, map{$dbh->quote($_,PG_BYTEA)} @ 
$tokens) . ")";


returns
Unknown type 17, defaulting to VARCHAR
as a warning...

On May 24, 2007, at 1:11 PM, [EMAIL PROTECTED] wrote:



Hi,

First, I would advise never using " insert into xx values (y,x)"  
without explicitly naming the columns;  same for select statements  
- never use select * (a table change can mess things up).


By the way, I just noticed in the release notes for the very latest  
couple of versions of DBD:Pg that some placeholder changes were  
made.  You might want to check the release notes and your version  
of DBD:Pg about your placeholder issues.

[GENERAL] unix_socket

[Tom Allison]

I'm trying to configure postfix to use the postgres database for lookups.
Since I have a localhost based server, I was going to use unix_sockets.
The postgres docs call for only a /directory.
unix_socket_directory (string)
   Specifies the directory of the Unix-domain socket on which the
   server is to listen for connections from client applications. The
   default is normally /tmp, but can be changed at build time.
The postfix docs call for a /directory/name.
  inet: for TCP connections (default).  Example:
  hosts = host1.some.domain host2.some.domain
  hosts = unix:/file/name
--
How do I get this sorted out so that I can make a unix connection?
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [GENERAL] unix_socket

[Tom Allison]

Tom Allison wrote:
I'm trying to configure postfix to use the postgres database for lookups.
Since I have a localhost based server, I was going to use unix_sockets.
The postgres docs call for only a /directory.
unix_socket_directory (string)
   Specifies the directory of the Unix-domain socket on which the
   server is to listen for connections from client applications. The
   default is normally /tmp, but can be changed at build time.
I found that my Debian installation had unix_socket_directory built to
unix_socket_directory=''
and in order to use sockets, you have to explicitly call it out.
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

[GENERAL] timestamps

[Tom Allison]

How would I specify a field that's to be updated to current_time 
everytime the row is created/altered?

Is there some way to put this 'update' property into the table instead 
of running some query to do it?

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

Re: [GENERAL] timestamps

[Tom Allison]

Doug McNaught wrote:
Tom Allison <[EMAIL PROTECTED]> writes:

How would I specify a field that's to be updated to current_time
everytime the row is created/altered?

Create a trigger.  There are some good examples in the PL/pgSQL docs.

Is there some way to put this 'update' property into the table instead
of running some query to do it?

Triggers are the way to go in Postgres.
-Doug
Thanks for the pointer.  That should be enough.
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] insert/update

[Tom Allison]

I seemed to remember being able to do this but I can't find the docs.
Can I run a sql query to insert new or update existing rows in one query?
Otherwise I have to run a select query to see if it's there and then 
another one to update/insert.

What I'm trying to do is create a counter for each key, insert a value 
of 1 or increment the value by 1 and then set another specific row 
(where key = $key) to always increment by 1.

And the more I type, the more this sounds like the answer is going to be 
part function, part trigger  Maybe I should post to 'novice' for a 
while!  ;)

---(end of broadcast)---
TIP 3: 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

[GENERAL] perl access

[Tom Allison]

I'm used to using the DBI modules in perl.
The online docs mention DBD as expiremental.
I'm thinking of sticking with DBI, unless there's some compelling reason 
to do otherwise.

---(end of broadcast)---
TIP 3: 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

[GENERAL] perl access

[Tom Allison]

I'm stuck on something stupid.
I'm trying to use perl to open up a database handle and I can't find the 
right database dsn string.

my $data_source = "dbi:Pg:mydatabase";
my $username = "mydatebasereader";
my $auth = "secret";
my $dbh = DBI->connect($data_source, $username, $auth);

DBI connect('postfix','postfix',...) failed: missing "=" after "postfix" 
in connection info string at /home/tallison/bin/perl.pl line 21

I've tried variations on the $data_source but I'm afraid I'm working on 
a hit-n-miss process.

Can someone point me?
---(end of broadcast)---
TIP 6: Have you searched our list archives?
  http://archives.postgresql.org

[GENERAL] pg_hba auth failure

[Tom Allison]

pg_hba.conf entry:
host all all 192.168.0/24md5
I get the error
2004-06-14 20:51:48 [21072] LOG:  invalid entry in pg_hba.conf file at 
line 89, token "md5"

When I connect as:
psql -U postfix -h 192.168.0.5 postfix
This user works locally with version 7.4.2.
Incompatable?
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
 joining column's datatypes do not match

Re: [GENERAL] perl access

[Tom Allison]

Jim Seymour wrote:
Like this:
my $data_source = "dbi:Pg:dbname=mydatabase";
  ^^^
thank you very much.
Now if I could just authenticate correctly...
---(end of broadcast)---
TIP 3: 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