Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Vincenzo Romano]

2010/1/13 Vincenzo Romano :
> The static binding worked fine in the second EXECUTE USING statement but not
> in the first one.
> I still think that it's weird more than wishful.
> I can work it around, though.
>
> Il giorno 12 gen, 2010 4:13 p., "Tom Lane"  ha scritto:
>
> Vincenzo Romano  writes:
>
>> I don't think so. Those variables should be evaluated with the USING >
>> *before* the actual executi...
>
> Unfortunately, that's just wishful thinking, not how EXECUTE USING
> actually works.
>
>                        regards, tom lane
>

Sorry for top posting: I used a mobile phone ...


-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

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

[GENERAL] Extremely Slow Cascade Delete Operation

[Yan Cheng Cheok]

I have 3 tables - lot, unit and measurement

1 lot is having relationship to many unit.
1 unit is having relationship to many measurement.
delete cascade is being used among their relationship

I try to perform delete operation on single row of lot.
===
SemiconductorInspection=# select count(*) from lot;
 count
---
 2
(1 row)


SemiconductorInspection=# select count(*) from unit;
  count
-
 1151927
(1 row)


SemiconductorInspection=# select count(*) from measurement;
  count
-
 9215416
(1 row)


SemiconductorInspection=# VACUUM ANALYZE;
VACUUM

SemiconductorInspection=# delete from lot where lot_id = 2;

Opps, this is a coffee operation. That's mean I can go out to have few cups of 
coffee and the operation still on going.

Even I use :
SemiconductorInspection=# EXPLAIN ANALYZE delete from lot where lot_id = 2;

It still hang there :(

Any suggestion? Anything I am doing wrong? Or this is the expected performance?

The table SQL is as follow :

   IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = 
'lot') THEN
CREATE TABLE lot
(
  lot_id bigserial NOT NULL,
  CONSTRAINT pk_lot_id PRIMARY KEY (lot_id)
);
END IF;
IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = 
'unit') THEN
CREATE TABLE unit
(
  unit_id bigserial NOT NULL,
  fk_lot_id bigint NOT NULL,
  CONSTRAINT pk_unit_id PRIMARY KEY (unit_id),
  CONSTRAINT fk_lot_id FOREIGN KEY (fk_lot_id)
  REFERENCES lot (lot_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE CASCADE
);
END IF;
IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name = 
'measurement') THEN
CREATE TABLE measurement
(
  measurement_id bigserial NOT NULL,
  fk_unit_id bigint NOT NULL,
  CONSTRAINT pk_measurement_id PRIMARY KEY (measurement_id),
  CONSTRAINT fk_unit_id FOREIGN KEY (fk_unit_id)
  REFERENCES unit (unit_id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE CASCADE
);
END IF;

Thanks and Regards
Yan Cheng CHEOK


  


-- 
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] Extremely Slow Cascade Delete Operation

[Grzegorz Jaśkiewicz]

try checking if it is waiting perhaps for something (is locked).

Peek at: (using different connection)
SELECT * FROM pg_stat_activity;
SELECT * FROM pg_locks;

Did you used prepared transactions ?
Try:
SELECT * FROM pg_prepared_xacts ;

Maybe some other transaction is blocking it.


HTH

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

[GENERAL] Is It Good Practice That I use TableName-Month-Year Convention

[Yan Cheng Cheok]

I realize the READ performance goes down dramatically when my table goes large. 
Every new day goes on, my table can increase x millions of new rows.

I was wondering whether this is good practice I can design my database in this 
way?

Instead of having

lot <-> unit <-> measurement

Can I have

lot-March-2010 <-> unit-March-2010 <-> measurement-March-2010
lot-April-2010 <-> unit-April-2010 <-> measurement-April-2010

(1) That's mean in my stored procedure, I need to dynamically generate the 
table name. Is this the "dynamic SQL" to correct way, to dynamically generate 
table name : http://www.postgresql.org/docs/8.1/interactive/ecpg-dynamic.html

(2) Is this consider a good approach, to overcome speed problem (especially 
read speed). Any potential problem I should put an eye on, before I implement 
this strategy?

Thanks and Regards
Yan Cheng CHEOK


  


-- 
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] Extremely Slow Cascade Delete Operation

[Yan Cheng Cheok]

It looks like this :

http://sites.google.com/site/yanchengcheok/Home/log.txt

I put it in google site, for easy reading)

Any hint? Thanks!

Thanks and Regards
Yan Cheng CHEOK


--- On Wed, 1/13/10, Grzegorz Jaśkiewicz  wrote:

> From: Grzegorz Jaśkiewicz 
> Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation
> To: "Yan Cheng Cheok" 
> Cc: pgsql-general@postgresql.org
> Date: Wednesday, January 13, 2010, 4:13 PM
> try checking if it is waiting perhaps
> for something (is locked).
> 
> Peek at: (using different connection)
> SELECT * FROM pg_stat_activity;
> SELECT * FROM pg_locks;
> 
> Did you used prepared transactions ?
> Try:
> SELECT * FROM pg_prepared_xacts ;
> 
> Maybe some other transaction is blocking it.
> 
> 
> HTH
> 
> -- 
> 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] Extremely Slow Cascade Delete Operation

[Grzegorz Jaśkiewicz]

It doesn't look like it is locked, so it is carrying the delete out.
However that doesn't mean, that there isn't any other locking
occurring, or simply your disks are rather busy.

Also, maybe the DB is rather big, what are the table sizes ?
If you are using 8.4+, than do \dt+ to get an idea, otherwise SELECT
pg_size_pretty(pg_total_relation_size('table_name')); for each table.

-- 
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] Extremely Slow Cascade Delete Operation

[Yan Cheng Cheok]

SemiconductorInspection=# \dt+
List of relations
 Schema |   Name   | Type  |  Owner   |Size| Description
+--+---+--++-
 public | lot  | table | postgres | 8192 bytes |
 public | measurement  | table | postgres | 529 MB |
 public | measurement_type | table | postgres | 8192 bytes |
 public | measurement_unit | table | postgres | 8192 bytes |
 public | unit | table | postgres | 57 MB  |
(5 rows)

I can see the PostgreSQL process is occupy CPU. But how come it takes so long? 
There are only 1000++ row of unit, where their lot_id is 2.

Seems not reasonable to me. :(

Thanks and Regards
Yan Cheng CHEOK


--- On Wed, 1/13/10, Grzegorz Jaśkiewicz  wrote:

> From: Grzegorz Jaśkiewicz 
> Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation
> To: "Yan Cheng Cheok" 
> Cc: pgsql-general@postgresql.org
> Date: Wednesday, January 13, 2010, 4:35 PM
> It doesn't look like it is locked, so
> it is carrying the delete out.
> However that doesn't mean, that there isn't any other
> locking
> occurring, or simply your disks are rather busy.
> 
> Also, maybe the DB is rather big, what are the table sizes
> ?
> If you are using 8.4+, than do \dt+ to get an idea,
> otherwise SELECT
> pg_size_pretty(pg_total_relation_size('table_name')); for
> each table.
> 
> -- 
> 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] Is It Good Practice That I use TableName-Month-Year Convention

[Tino Wildenhain]

Hi,

Am 13.01.2010 09:16, schrieb Yan Cheng Cheok:

I realize the READ performance goes down dramatically when my table goes large. 
Every new day goes on, my table can increase x millions of new rows.

I was wondering whether this is good practice I can design my database in this 
way?

Instead of having

lot<->  unit<->  measurement

Can I have

lot-March-2010<->  unit-March-2010<->  measurement-March-2010
lot-April-2010<->  unit-April-2010<->  measurement-April-2010

(1) That's mean in my stored procedure, I need to dynamically generate the table name. Is 
this the "dynamic SQL" to correct way, to dynamically generate table name : 
http://www.postgresql.org/docs/8.1/interactive/ecpg-dynamic.html

(2) Is this consider a good approach, to overcome speed problem (especially 
read speed). Any potential problem I should put an eye on, before I implement 
this strategy?


You might combine this approach with table partitioning to give you a
cleaner view to your data like this:

http://www.postgresql.org/docs/current/static/ddl-partitioning.html

in your situation it would probably make sense to put the actual
partitiones into a separate schema to keep your main work area clean
from clutter.

HTH
Tino



smime.p7s
Description: S/MIME Cryptographic Signature

Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

[Maximilian Tyrtania]

Am 12.01.2010 um 12:36 schrieb Martin Flahault:

> We have spend some time evaluating PostgreSQL and we can't get correct 
> outputs with the ORDER BY command.
> LC_COLLATE and LC_CTYPE are set to fr_FR.UTF-8.
> 
> It seems there is a known problem with the collating order of text including 
> diacritics with the UTF8 encodings on BSD systems.
> 
> Does anyone know a workaround ?

The best i've seen so far is:

CREATE OR REPLACE FUNCTION f_getorderbyfriendlyversion(texttoconvert text)
 RETURNS text AS
$BODY$
select translate(upper($1),'ÄÖÜ','AOU')--add french diacritical characters here
$BODY$
 LANGUAGE 'sql' IMMUTABLE STRICT
 COST 100;
ALTER FUNCTION f_getorderbyfriendlyversion(text) OWNER TO postgres;

CREATE INDEX idx_mytable_myfield_orderbyfriendly
 ON mytable
 USING btree
 (f_getorderbyfriendlyversion(myfield::text));

Select * from mytable order by f_getorderbyfriendlyversion(myfield);

Not an ideal solution, but it seems to perform quite well.

Best wishes from Berlin,

Maximilian Tyrtania

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

[GENERAL] What happens when you kill the postmaster?

[Ralf Schuchardt]

Hi,

on one of our Mac servers an update (Remote Desktop Client) killed yesterday 
the postmaster process. Apparently this did not have any influence on existing 
connections and therefore was not detected until some time later, when no 
connection for a backup could be made.

I have then closed all apps with connections to the database. This brought the 
whole cluster down. It restarted then with some transaction log rollback 
messages and seems to be running fine since then.

Can I now expect that the database is in a consistent state, or must I assume 
the database is corrupted?
I could run a dump-all without problems and there are rows created after the 
death the postmaster.

Thanks.

Ralf Schuchardt
-- 
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] Extremely Slow Cascade Delete Operation

[Craig Ringer]

On 13/01/2010 4:09 PM, Yan Cheng Cheok wrote:

I have 3 tables - lot, unit and measurement

1 lot is having relationship to many unit.
1 unit is having relationship to many measurement.
delete cascade is being used among their relationship




SemiconductorInspection=# delete from lot where lot_id = 2;


Are there indexes on fk_lot_id and fk_unit_id ? If not, a DELETE from 
lot will cause a seqscan of unit for affected units, and if any must be 
deleted each will cause a seqscan of measurement for affected 
measurements. That's going to get ugly fast.


--
Craig Ringer

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

Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

[Craig Ringer]

On 12/01/2010 7:36 PM, Martin Flahault wrote:

Hi,

We are a software publisher searching for a new DBMS for our software.
We have more than one hundred installed servers, running Mac OS and a
Primebase database.

We have spend some time evaluating PostgreSQL and we can't get correct
outputs with the ORDER BY command.


Can you provide a sample? Include a table of sample values, an example 
query, its output, and what you'd expect to get instead? And why?


--
Craig Ringer

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

Re: [GENERAL] What happens when you kill the postmaster?

[Filip Rembiałkowski]

2010/1/13 Ralf Schuchardt 

> Hi,
>
> on one of our Mac servers an update (Remote Desktop Client) killed
> yesterday the postmaster process. Apparently this did not have any influence
> on existing connections and therefore was not detected until some time
> later, when no connection for a backup could be made.
>
> I have then closed all apps with connections to the database. This brought
> the whole cluster down. It restarted then with some transaction log rollback
> messages and seems to be running fine since then.
>
> Can I now expect that the database is in a consistent state, or must I
> assume the database is corrupted?
> I could run a dump-all without problems and there are rows created after
> the death the postmaster.
>
>
It depends on the signal which was sent to the postgres process.

AFAIK, only SIGKILL (unconditional kill) can make some damage to the
database.

see http://www.postgresql.org/docs/8.4/static/app-postgres.html forfull
explanation





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



-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

[GENERAL] describe relation between LDAP entries

[Mihamina Rakotomandimby]

Manao ahoana, Hello, Bonjour,

I have LDAP entries, and SQL tables are the relation between them.
There are only relations between 2 entries.

There are "modem" and "customers" entries in the LDAP directory

I need advice on optimizing the way I store relations.
For example (This is on MySQL, just for the example)

  SELECT * FROM customer_has_modem;
  +-++
  | customer| modem  |
  +-++
  | a=aa,b=bb,c=ccc | d=dd,e=ee,f=ff | 
  +-++

Is it OK?

Have you another way to do it? 
 - splitting the comma separated fields?
 - whatever?

There might later be be a "date" field in this table to trace the modem
movements.

Misaotra, Thanks, Merci.

-- 
   Architecte Informatique chez Blueline/Gulfsat:
Administration Systeme, Recherche & Developpement
+261 34 29 155 34 / +261 33 11 207 36

-- 
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] describe relation between LDAP entries

[Mihamina Rakotomandimby]

> Mihamina Rakotomandimby  : 
> Have you another way to do it? 
>  - splitting the comma separated fields?

I mean:

customer_cn|customer_sn| customer_uid|modem_cn|modem_sn|modem_uid| 
staff  |   (null)  |(null)   |staff   |  (null)| (null)  |
Joe M  |Mudd   | joem|Joe M   |   Mudd |  joem   |


-- 
   Architecte Informatique chez Blueline/Gulfsat:
Administration Systeme, Recherche & Developpement
+261 34 29 155 34 / +261 33 11 207 36

-- 
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 8.1 windows 2008 64 bit

[Daniel Schuchardt]

Hy,

can anybody give us a hint if we can use that combination?

Thanks,

Daniel.
--

Daniel Schuchardt
/Softwareentwicklung/

/http://www.prodat-sql.de/


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

[GENERAL] autovacuum_naptime seems to take minutes for seconds

[Claudio Eichenberger]

Hello,

Concerns: 8.4.2

I cannot test it on other systems than FreeBSD so I don't know whether it's 
just FreeBSD related.

The parameter autovacuum_naptime seems to take minutes for seconds.

On a completely idle system, no db access, with the default 
autovacuum_naptime=1min configuration I get:

gamma# iostat -w1 ar1
  tty ar1 cpu
 tin tout  KB/t tps  MB/s  us ni sy in id
   1  234 88.35  10  0.87   0  0  0  0 99
   1  129 125.24  29  3.54   0  0  0  0 99
   0   44 125.71  14  1.72   0  0  0  0 99
   0   44 121.47  30  3.55   0  0  1  0 99
   0   44 125.71  14  1.72   1  0  0  0 99
   0   44 125.24  29  3.54   0  0  1  0 99
   0   44 124.69  29  3.53   0  0  1  0 99
   0   44 126.29  28  3.45   0  0  0  0 100
   0   45 118.13  15  1.73   1  0  0  0 99
   0   44 121.47  30  3.55   1  0  1  0 98
   0   44 43.65 102  4.34   0  0  1  0 99
   0   43 125.24  29  3.54   0  0  1  0 99
   0   44 125.71  14  1.72   0  0  0  0 99
   0   44 117.29  31  3.55   0  0  1  0 99
   6   44 124.69  29  3.53   0  0  0  0 100
  31   45 125.71  14  1.72   0  0  0  0 99

with autovacuum_naptime=5min

gamma# iostat -w1 ar1
  tty ar1 cpu
 tin tout  KB/t tps  MB/s  us ni sy in id
   1  231 88.42  10  0.87   0  0  0  0 99
   1  129 117.94  31  3.56   0  0  1  0 99
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44 119.20  30  3.49   0  0  0  0 99
   0   44  0.00   0  0.00   0  0  0  0 99
   0   43  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44 117.94  31  3.57   0  0  1  0 99
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44 119.20  30  3.49   0  0  1  0 99
   0   44  0.00   0  0.00   0  0  0  0 100

with autovacuum_naptime=60min I get the default expected result of a minute:

gamma# iostat -w1 ar1
  tty ar1 cpu
 tin tout  KB/t tps  MB/s  us ni sy in id
   1  229 88.42  10  0.87   0  0  0  0 99
   1  129  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  2.00   2  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  4.00   1  0.00   0  0  0  0 100
   0   44 16.00   1  0.02   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44 16.00  13  0.20   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  2.00   1  0.00   0  0  0  0 100
   0   44  7.43   7  0.05   0  0  0  0 99
   0   43  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
  tty ar1 cpu
 tin tout  KB/t tps  MB/s  us ni sy in id
   0   44  0.00   0  0.00   0  0  0  0 100
   0  130  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44 124.69  29  3.53   0  0  1  0 99
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
  tty ar1 cpu
 tin tout  KB/t tps  MB/s  us ni sy in id
   0   44 16.00  11  0.17   0  0  0  0 100
   0  130  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44 20.75   8  0.16   0  0  1  0 99
   0   43  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100
   0   44  0.00   0  0.00   0  0  0  0 100

Ragards

Claudio
-- 
Tel +41 21 67 17 111
Mob +41 79 34 72 100
clau...@yourshop.com
Http://YourShop.com/ 
 

"Come to me all who are weary and burdened
and I will give you rest" -- Jesus Christ




-- 
Sent via pgsql-general

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Pavel Stehule]

2010/1/13 Daniel Schuchardt :
> Hy,
>
> can anybody give us a hint if we can use that combination?
>

first 64bit PostgreSQL on win will be 8.5

regards
Pavel

> Thanks,
>
> Daniel.
> --
>
> Daniel Schuchardt
> /Softwareentwicklung/
>
> /http://www.prodat-sql.de/
>
>
> --
> 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] postgresql 8.1 windows 2008 64 bit

[Grzegorz Jaśkiewicz]

and besides, I think 8.1 is not supported on win32 anymore.

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

Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Adrian Klaver]

On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote:
> The static binding worked fine in the second EXECUTE USING statement but
> not in the first one.
> I still think that it's weird more than wishful.
> I can work it around, though.
>
> Il giorno 12 gen, 2010 4:13 p., "Tom Lane"  ha scritto:
>
> Vincenzo Romano  writes:
> > I don't think so. Those variables should be evaluated with the USING >
>
> *before* the actual executi...
> Unfortunately, that's just wishful thinking, not how EXECUTE USING
> actually works.
>
>regards, tom lane

Without the whole function it is hard to say. Given the error I would say it is 
a quoting issue. The table name is being substituted for, the other parameters 
are not. It acts like the add_check clause is not part of the EXECUTE statement 
and is just being passed through verbatim.

ERROR:  there is no parameter $1
CONTEXT: SQL statement "
  alter table public.test_part_2 add check(
data>=$1::timestamp and data<$2::timestamp and maga=$3 )

 
-- 
Adrian Klaver
adrian.kla...@gmail.com

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

[GENERAL] log_temp_files confusion

[Filip Rembiałkowski]

I would like to log usage of temporary files for sort/join operations, but
only when size of these files exceeds some threshold.

So I set in postgresql.conf (this is 8.4.2)
log_temp_files = 4MB

But then I got these messages in log file

2010-01-12 13:24:49 CET 24899 fi...@la_filip LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp24899.1", size 162464
2010-01-12 13:24:49 CET 24899 fi...@la_filip CONTEXT:  SQL statement "insert
into foo ( ... ) select ...
PL/pgSQL function "la_foo" line 51 at SQL statement
2010-01-12 13:24:49 CET 24899 fi...@la_filip STATEMENT:  Select * From
la_foo(31968)

2010-01-12 13:24:49 CET 24899 fi...@la_filip LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp24899.0", size 153152
2010-01-12 13:24:49 CET 24899 fi...@la_filip CONTEXT:  SQL statement "insert
into foo ( ... ) select ...
PL/pgSQL function "la_foo" line 51 at SQL statement
2010-01-12 13:24:49 CET 24899 fi...@la_filip STATEMENT:  Select * From
la_foo(31968)

2010-01-12 13:24:49 CET 24899 fi...@la_filip LOG:  temporary file: path
"base/pgsql_tmp/pgsql_tmp24899.2", size 152128
2010-01-12 13:24:49 CET 24899 fi...@la_filip CONTEXT:  SQL statement "insert
into foo ( ... ) select ...
PL/pgSQL function "la_foo" line 51 at SQL statement
2010-01-12 13:24:49 CET 24899 fi...@la_filip STATEMENT:  Select * From
la_foo(31968)


The combined size of temporary files named in these three warnings is far
from 4 MB.

Why is postgres logging these operations?
How is this threshold calculated at run time?

TIA


-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Craig Ringer]

On 13/01/2010 6:15 PM, Daniel Schuchardt wrote:

Hy,

can anybody give us a hint if we can use that combination?


You can use libpq on 64-bit windows to talk to an 8.1 database if you 
really must.


I really wouldn't recommend running the 8.1 database on windows. Win32 
releases of Pg see big improvements with every version and 8.1 is really 
not recommended or supported anymore. 8.1 was ... deficient ... on Windows.


Run a recent Pg server on 64-bit windows, or run your 8.1 server on a 
UNIX machine and connect to it from 64-bit windows using a modern 64-bit 
libpq.


Why 8.1, anyway?

--
Craig Ringer

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

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Grzegorz Jaśkiewicz]

8.1 version doesn't make any sense anyway, on any platform. There's
been so many improvements since, and if one wants to be conservative -
go for 8.3, which has tons of improvements over 8.1, especially in
area of performance.

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

Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Vincenzo Romano]

2010/1/13 Adrian Klaver :
> On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote:
>> The static binding worked fine in the second EXECUTE USING statement but
>> not in the first one.
>> I still think that it's weird more than wishful.
>> I can work it around, though.
>>
>> Il giorno 12 gen, 2010 4:13 p., "Tom Lane"  ha scritto:
>>
>> Vincenzo Romano  writes:
>> > I don't think so. Those variables should be evaluated with the USING >
>>
>> *before* the actual executi...
>> Unfortunately, that's just wishful thinking, not how EXECUTE USING
>> actually works.
>>
>>                        regards, tom lane
>
> Without the whole function it is hard to say. Given the error I would say it 
> is
> a quoting issue. The table name is being substituted for, the other parameters
> are not. It acts like the add_check clause is not part of the EXECUTE 
> statement
> and is just being passed through verbatim.
>
> ERROR:  there is no parameter $1
> CONTEXT: SQL statement "
>              alter table public.test_part_2 add check(
> data>=$1::timestamp and data<$2::timestamp and maga=$3 )

Well, for these case I prefer $-quoting: it's my personal taste that should
The rest of the function budy sheds no extra light on the problem.
For sure this fragment works fine:

   execute $l2$
 insert into $l2$||ct||$l2$
   select * from only public.test
   where data>=$1::timestamp and data<$2::timestamp and maga=$3
   $l2$ using rec.d0,rec.d1,rec.maga;

while thos one doesn't:

   execute $l2$
 alter table $l2$||ct||$l2$ add check(
data>=$1::timestamp and data<$2::timestamp and maga=$3 )
   $l2$ using rec.d0,rec.d1,rec.maga;

Please, observe that the WHERE condition and the USING predicate in
the first fragment is exactly the same as
the CHECK condition and the USING predicate in the second one (that's
intentional).
What I would still expect is that the EXECUTE ... USING statically
replaces the $1,$2 and $3 "variables" in the quoted string with the
*current values* of what can be found in the USING predicate.
No function arguments should be even taken into account as the "thing"
following the EXECUTE command is a *string literal*.

In the end, I think that Tom is wrong, simply because one fragment
works and the other one doesn't.
I'd expect either both or none working and would say this is a bug.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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 8.1 windows 2008 64 bit

[Daniel Schuchardt]

Am 13.01.2010 16:00, schrieb Craig Ringer:

On 13/01/2010 6:15 PM, Daniel Schuchardt wrote:

Hy,

can anybody give us a hint if we can use that combination?


You can use libpq on 64-bit windows to talk to an 8.1 database if you 
really must.


I really wouldn't recommend running the 8.1 database on windows. Win32 
releases of Pg see big improvements with every version and 8.1 is 
really not recommended or supported anymore. 8.1 was ... deficient ... 
on Windows.


Run a recent Pg server on 64-bit windows, or run your 8.1 server on a 
UNIX machine and connect to it from 64-bit windows using a modern 
64-bit libpq.


Why 8.1, anyway?

--
Craig Ringer


we plan to upgrade to 8.4 the next time but currently everything is 
build with 8.1. thanks for your awnsers, so we wont try it.

but i think we will wait till 8.5 because of the 64 bit problem.

we did not upgrade because 81 has autocast and later version doesn't, so 
we need to check all our statements and triggers/stored procedures wich 
is currently in work.


--
Mit freundlichen Grüssen,

Daniel Schuchardt
/Softwareentwicklung/

*CIMPCS GmbH
*Grünewaldstrasse 19
D-99099 Erfurt

TelefonFaxMobil
0049 361 65347180049 361 65347170049 172 7766971

Firmensitz : Erfurt
Geschäftsführer : Herbert Weber; Heinrich Kühni
Registriert : Amtsgericht Erfurt, HRB 501091
USt-IdNr : DE252754686

www.prodat-sql.de 

Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

[Martin Flahault]

Here is an exemple :

postgres=# create database newbase;
CREATE DATABASE
postgres=# \c newbase;
psql (8.4.2)
You are now connected to database "newbase".
newbase=# create table t1 (contenu text);
CREATE TABLE
newbase=# insert into t1 values ('a'), ('e'), ('à'), ('é'), ('A'), ('E');
INSERT 0 6

newbase=# select * from t1 order by contenu;
 contenu 
-
 A
 E
 a
 e
 à
 é
(6 rows)

newbase=# select * from t1 order by upper(contenu);
 contenu 
-
 a
 A
 e
 E
 à
 é
(6 rows)


Here is the encoding informations :

newbase=# \encoding
UTF8
newbase=# show lc_collate;
 lc_collate 

 fr_FR
(1 row)

newbase=# show lc_ctype;
 lc_ctype 
--
 fr_FR
(1 row)


As with others DBMS (MySQL for example), diacritics should be ignored when 
determining the sort order. Here is the expected output:
 a
 à
 A
 e
 é 
 E


It seems there is a problem with the collating order on BSD systems with 
diacritics using UTF8.
If you put this text :
a
A
à
é
e
E

in a UTF8 text file and use the "sort" command on it, you will have the same 
wrong output as with PostgreSQL :
A
E
a
e
à
é

Hope this will help,

Martin

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Greg Smith]

Daniel Schuchardt wrote:

can anybody give us a hint if we can use that combination?


PostgreSQL 8.1 for Windows became unsupported over two years ago due to 
technical issues:  http://www.postgresql.org/about/news.865

http://wiki.postgresql.org/wiki/PostgreSQL_Release_Support_Policy

There were some major infrastructure changes in 8.3, including building 
the software using Microsoft's compiler instead of ported UNIX tools, 
that make it (or, even better, 8.4) a far better choice.


There is no specific 64-bit port of PostgreSQL yet, one is in progress 
for the next version but it's still missing a few things:  
http://blog.hagander.net/archives/160-64-bit-Windows-in-PostgreSQL.html


But you can run the regular 32-bit server just fine on the 64-bit 
versions, albeit with some concerns about client apps:  
http://blog.hagander.net/archives/73-PostgreSQL-vs-64-bit-windows.html


--
Greg Smith2ndQuadrant   Baltimore, MD
PostgreSQL Training, Services and Support
g...@2ndquadrant.com  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

Re: [GENERAL] autovacuum_naptime seems to take minutes for seconds

[Tom Lane]

Claudio Eichenberger  writes:
> The parameter autovacuum_naptime seems to take minutes for seconds.

How many databases in your installation?

autovacuum_naptime is the target cycle time for any one database.
If you have N databases then the time between launching autovacuum
workers will be autovacuum_naptime / N.

regards, tom lane

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

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[A. Kretschmer]

In response to Daniel Schuchardt :
> we plan to upgrade to 8.4 the next time but currently everything is build with
> 8.1. thanks for your awnsers, so we wont try it.
> but i think we will wait till 8.5 because of the 64 bit problem.

I'm not expect that 8.5 contains 64 bit for windows...

http://blog.hagander.net/archives/160-64-bit-Windows-in-PostgreSQL.html


Regards, Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

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

Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Vincenzo Romano]

2010/1/13 Vincenzo Romano :
> 2010/1/13 Adrian Klaver :
>> On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote:
>>> The static binding worked fine in the second EXECUTE USING statement but
>>> not in the first one.
>>> I still think that it's weird more than wishful.
>>> I can work it around, though.
>>>
>>> Il giorno 12 gen, 2010 4:13 p., "Tom Lane"  ha scritto:
>>>
>>> Vincenzo Romano  writes:
>>> > I don't think so. Those variables should be evaluated with the USING >
>>>
>>> *before* the actual executi...
>>> Unfortunately, that's just wishful thinking, not how EXECUTE USING
>>> actually works.
>>>
>>>                        regards, tom lane
>>
>> Without the whole function it is hard to say. Given the error I would say it 
>> is
>> a quoting issue. The table name is being substituted for, the other 
>> parameters
>> are not. It acts like the add_check clause is not part of the EXECUTE 
>> statement
>> and is just being passed through verbatim.
>>
>> ERROR:  there is no parameter $1
>> CONTEXT: SQL statement "
>>              alter table public.test_part_2 add check(
>> data>=$1::timestamp and data<$2::timestamp and maga=$3 )
>
> Well, for these case I prefer $-quoting: it's my personal taste that should
> The rest of the function budy sheds no extra light on the problem.
> For sure this fragment works fine:
>
>           execute $l2$
>             insert into $l2$||ct||$l2$
>               select * from only public.test
>               where data>=$1::timestamp and data<$2::timestamp and maga=$3
>           $l2$ using rec.d0,rec.d1,rec.maga;
>
> while thos one doesn't:
>
>           execute $l2$
>             alter table $l2$||ct||$l2$ add check(
> data>=$1::timestamp and data<$2::timestamp and maga=$3 )
>           $l2$ using rec.d0,rec.d1,rec.maga;
>
> Please, observe that the WHERE condition and the USING predicate in
> the first fragment is exactly the same as
> the CHECK condition and the USING predicate in the second one (that's
> intentional).
> What I would still expect is that the EXECUTE ... USING statically
> replaces the $1,$2 and $3 "variables" in the quoted string with the
> *current values* of what can be found in the USING predicate.
> No function arguments should be even taken into account as the "thing"
> following the EXECUTE command is a *string literal*.
>
> In the end, I think that Tom is wrong, simply because one fragment
> works and the other one doesn't.
> I'd expect either both or none working and would say this is a bug.
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>

One can also check the documentation (v8.4.2) at page 800, chapter
"38.5.4. Executing Dynamic Commands"

The command string can use parameter values, which are referenced in
the command as $1, $2,
etc. These symbols refer to values supplied in the USING clause. This
method is often preferable to
inserting data values into the command string as text: it avoids
run-time overhead of converting the
values to text and back, and it is much less prone to SQL-injection
attacks since there is no need for
quoting or escaping. An example is:
EXECUTE ’SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= $2’
INTO c
USING checked_user, checked_date;


Moreover, by putting the logging level to the maximum I've found where
the error is generated:

ERROR:  42P02: there is no parameter $1
...
LOCATION:  find_param_type, parse_expr.c:655

This is the backend (src/backend/parser), while I was expecting the
expansion to happen in the PL (src/pl/plpgsql/src).
This seems to me to confirm a bug where the actual string inside the
EXECUTE gets interpreted before (or without) the USING predicate,
at least in the case of the "ALTER TABLE", but not in the case of the SELECT.
Which in turn sounds even more weird to me.

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

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

Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Vincenzo Romano]

2010/1/13 Vincenzo Romano :
> 2010/1/13 Vincenzo Romano :
>> 2010/1/13 Adrian Klaver :
>>> On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote:
 The static binding worked fine in the second EXECUTE USING statement but
 not in the first one.
 I still think that it's weird more than wishful.
 I can work it around, though.

 Il giorno 12 gen, 2010 4:13 p., "Tom Lane"  ha scritto:

 Vincenzo Romano  writes:
 > I don't think so. Those variables should be evaluated with the USING >

 *before* the actual executi...
 Unfortunately, that's just wishful thinking, not how EXECUTE USING
 actually works.

                        regards, tom lane
>>>
>>> Without the whole function it is hard to say. Given the error I would say 
>>> it is
>>> a quoting issue. The table name is being substituted for, the other 
>>> parameters
>>> are not. It acts like the add_check clause is not part of the EXECUTE 
>>> statement
>>> and is just being passed through verbatim.
>>>
>>> ERROR:  there is no parameter $1
>>> CONTEXT: SQL statement "
>>>              alter table public.test_part_2 add check(
>>> data>=$1::timestamp and data<$2::timestamp and maga=$3 )
>>
>> Well, for these case I prefer $-quoting: it's my personal taste that should
>> The rest of the function budy sheds no extra light on the problem.
>> For sure this fragment works fine:
>>
>>           execute $l2$
>>             insert into $l2$||ct||$l2$
>>               select * from only public.test
>>               where data>=$1::timestamp and data<$2::timestamp and maga=$3
>>           $l2$ using rec.d0,rec.d1,rec.maga;
>>
>> while thos one doesn't:
>>
>>           execute $l2$
>>             alter table $l2$||ct||$l2$ add check(
>> data>=$1::timestamp and data<$2::timestamp and maga=$3 )
>>           $l2$ using rec.d0,rec.d1,rec.maga;
>>
>> Please, observe that the WHERE condition and the USING predicate in
>> the first fragment is exactly the same as
>> the CHECK condition and the USING predicate in the second one (that's
>> intentional).
>> What I would still expect is that the EXECUTE ... USING statically
>> replaces the $1,$2 and $3 "variables" in the quoted string with the
>> *current values* of what can be found in the USING predicate.
>> No function arguments should be even taken into account as the "thing"
>> following the EXECUTE command is a *string literal*.
>>
>> In the end, I think that Tom is wrong, simply because one fragment
>> works and the other one doesn't.
>> I'd expect either both or none working and would say this is a bug.
>>
>> --
>> Vincenzo Romano
>> NotOrAnd Information Technologies
>> NON QVIETIS MARIBVS NAVTA PERITVS
>>
>
> One can also check the documentation (v8.4.2) at page 800, chapter
> "38.5.4. Executing Dynamic Commands"
> 
> The command string can use parameter values, which are referenced in
> the command as $1, $2,
> etc. These symbols refer to values supplied in the USING clause. This
> method is often preferable to
> inserting data values into the command string as text: it avoids
> run-time overhead of converting the
> values to text and back, and it is much less prone to SQL-injection
> attacks since there is no need for
> quoting or escaping. An example is:
> EXECUTE ’SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted <= 
> $2’
> INTO c
> USING checked_user, checked_date;
> 
>
> Moreover, by putting the logging level to the maximum I've found where
> the error is generated:
>
> ERROR:  42P02: there is no parameter $1
> ...
> LOCATION:  find_param_type, parse_expr.c:655
>
> This is the backend (src/backend/parser), while I was expecting the
> expansion to happen in the PL (src/pl/plpgsql/src).
> This seems to me to confirm a bug where the actual string inside the
> EXECUTE gets interpreted before (or without) the USING predicate,
> at least in the case of the "ALTER TABLE", but not in the case of the SELECT.
> Which in turn sounds even more weird to me.
>
> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>

Even worse!

This is one of my (best) attempts to work the issue around:

execute $l2$
  select $l3$alter table $l2$||ct||$l2$ add check (
data>=$1::timestamp and data<$2::timestamp and maga=$3 )$l3$
$l2$ into pr using rec.d0,rec.d1,rec.maga;
raise info '%',pr;
execute pr;

So, basically I (tried to) expand the ALTER TABLE command into a text
variable for later execution.
The RAISE statement is for basic debugging. The output is

INFO:  alter table public.test_part_1 add check ( data>=$1::timestamp
and data<$2::timestamp and maga=$3 )

despite the (usual) USING predicate!
Also in this case the $1, $2 and $3 "variables" have not been substituted.
Please, remember that this fragment works fine:

 execute $l2$
 insert into $l2$||ct||$l2$
   select * from only public.test
   where data>=$1::timestamp

Re: [GENERAL] ChronicDB: Live database schema updates with zero downtime

[Gurjeet Singh]

On Wed, Jan 13, 2010 at 12:29 AM, ChronicDB  wrote:

>
> [3] http://chronicdb.com/chronicdb_early_adoption_program


"Page not found"

-- 
gurjeet.singh
@ EnterpriseDB - The Enterprise Postgres Company
http://www.enterprisedb.com

singh.gurj...@{ gmail | yahoo }.com
Twitter/Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Joshua D. Drake]

> we plan to upgrade to 8.4 the next time but currently everything is
> build with 8.1. thanks for your awnsers, so we wont try it. 
> but i think we will wait till 8.5 because of the 64 bit problem.
> 
> we did not upgrade because 81 has autocast and later version doesn't,
> so we need to check all our statements and triggers/stored procedures
> wich is currently in work.
> 
PostgreSQL 8.3/8.4/8.5 will run just fine on 64bit Windows. Just
understand that PostgreSQL will be running in 32bit mode, which frankly
on windows is not a problem compared to other platforms.

Joshua D. Drake



-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
Respect is earned, not gained through arbitrary and repetitive use or Mr. or 
Sir.


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

[GENERAL] pl/perl setof function

[Alex -]

Hi,i have a PL/PERL RETURN SETOF function which processes a few 10k records.The 
processing takes quite some time and in order to display progressI use a 
return_next after every few thousand records.
However, the function returns all messages when it completes and exists which 
is not really what i want. I also tried the elog NOTICE but then I only get the 
messages back to the psql.
I am calling the function from a perl script and would like to catch these 
progress messages.
Is there any way to solve my problem?
ThanksAlex
_
Search for properties that match your lifestyle! Start searching NOW!
http://clk.atdmt.com/NMN/go/157631292/direct/01/

Re: [GENERAL] postgresql 8.1 windows 2008 64 bit

[Daniel Schuchardt]

Ok, thats fine for us.

Most of our customers run Debian in 64Bit mode with Postgres 81 64 Bit, 
but some won't use a *nix.


Thanks.


Am 13.01.2010 18:49, schrieb Joshua D. Drake:
   

we plan to upgrade to 8.4 the next time but currently everything is
build with 8.1. thanks for your awnsers, so we wont try it.
but i think we will wait till 8.5 because of the 64 bit problem.

we did not upgrade because 81 has autocast and later version doesn't,
so we need to check all our statements and triggers/stored procedures
wich is currently in work.

 

PostgreSQL 8.3/8.4/8.5 will run just fine on 64bit Windows. Just
understand that PostgreSQL will be running in 32bit mode, which frankly
on windows is not a problem compared to other platforms.

Joshua D. Drake



   



--
Mit freundlichen Grüssen,

Daniel Schuchardt
/Softwareentwicklung/

*CIMPCS GmbH
*Grünewaldstrasse 19
D-99099 Erfurt

TelefonFaxMobil
0049 361 65347180049 361 65347170049 172 7766971

Firmensitz : Erfurt
Geschäftsführer : Herbert Weber; Heinrich Kühni
Registriert : Amtsgericht Erfurt, HRB 501091
USt-IdNr : DE252754686

www.prodat-sql.de 

Re: [GENERAL] autovacuum_naptime seems to take minutes for seconds

[Claudio Eichenberger]

Tom

Hello,

55 databases which made me believe minutes correspond to seconds!

Many thanks for your explanation

Regards

Claudio



On 2010-01-13 11:08:35, Tom Lane wrote:
> Claudio Eichenberger  writes:
> > The parameter autovacuum_naptime seems to take minutes for seconds.
> 
> How many databases in your installation?
> 
> autovacuum_naptime is the target cycle time for any one database.
> If you have N databases then the time between launching autovacuum
> workers will be autovacuum_naptime / N.
> 
>   regards, tom lane
> 

-- 
Tel +41 21 67 17 111
Mob +41 79 34 72 100
clau...@yourshop.com
Http://YourShop.com/ 
 

"Come to me all who are weary and burdened
and I will give you rest" -- Jesus Christ



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

[GENERAL] postgres

[Amy Smith]

all
how to set up  PGTAB file ? any example for
PGTAB=/opt/postgres/utilities/conf/pgtab

thanks

Re: [GENERAL] log_temp_files confusion

[Andrej]

2010/1/14 Filip Rembiałkowski :
> I would like to log usage of temporary files for sort/join operations, but
> only when size of these files exceeds some threshold.
>
> So I set in postgresql.conf (this is 8.4.2)
> log_temp_files = 4MB
Just a wild guess... the DOCU says it's an integer, not an INT & STRING.


Try
log_temp_files = 4194304

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

[GENERAL] PgEast CFP (second call)

[Joshua D. Drake]

January 13, 2010

PostgreSQL Conference East, The PostgreSQL Conference for Decision
Makers, End Users and Developers, is being held at the Radisson Plaza,
Warwick Hotel in Philadelphia on March 25th through 28th.

This is the second call for papers for this conference. You can review
the skeletal agenda here:

http://www.postgresqlconference.org/2010/east/agenda

You can review content from the recent West conference here:

http://www.postgresqlconference.org/2009/west/

You can submit your talk here:

http://www.postgresqlconference.org/talksubmission

Time line:
December 14th: Talk submission opens
January 30th: Talk submission closes
February 15th: Speaker notification

This year we will be continuing our trend of covering the entire
PostgreSQL ecosystem. We would like to see talks and tutorials on the
following topics:

  * General PostgreSQL: 
  * Administration 
  * Performance 
  * High Availability 
  * Migration 
  * GIS 
  * Integration 
  * Solutions and White Papers 
  * The Stack: 
  * Python/Django/Pylons/TurboGears/Custom 
  * Perl5/Catalyst/Bricolage 
  * Ruby/Rails 
  * Java (PLJava would be great)/Groovy/Grails 
  * Operating System optimization
(Linux/FBSD/Solaris/Windows) 
  * Solutions and White Papers 

Submit Session: http://www.postgresqlconference.org/talksubmission

Sincerely,

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering







-- 
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] pl/perl setof function

[Andy Colson]

On 1/13/2010 12:20 PM, Alex - wrote:

Hi,
i have a PL/PERL RETURN SETOF function which processes a few 10k records.
The processing takes quite some time and in order to display progress
I use a return_next after every few thousand records.

However, the function returns all messages when it completes and exists
which is not really what i want. I also tried the elog NOTICE but then I
only get the messages back to the psql.

I am calling the function from a perl script and would like to catch
these progress messages.

Is there any way to solve my problem?

Thanks
Alex



In the doc's you'll see that return_next copies the record into an 
in-memory table which is then returned once the storedproc is finished.


Not sure how raise works though.

-Andy

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

Re: [GENERAL] pl/perl setof function

[Andy Colson]

On 1/13/2010 1:26 PM, Andy Colson wrote:

On 1/13/2010 12:20 PM, Alex - wrote:

Hi,
i have a PL/PERL RETURN SETOF function which processes a few 10k records.
The processing takes quite some time and in order to display progress
I use a return_next after every few thousand records.

However, the function returns all messages when it completes and exists
which is not really what i want. I also tried the elog NOTICE but then I
only get the messages back to the psql.

I am calling the function from a perl script and would like to catch
these progress messages.

Is there any way to solve my problem?

Thanks
Alex



In the doc's you'll see that return_next copies the record into an
in-memory table which is then returned once the storedproc is finished.

Not sure how raise works though.

-Andy



Humm.. further to that, you could probably catch the notices from perl 
with this:


http://search.cpan.org/dist/DBD-Pg/Pg.pm#pg_notifies

I'm assuming elog is like raise, and that they get sent immediately and 
not when the stored proc is finished.


-Andy

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

[GENERAL] How to subscribe to your security list?

[akuster]

Morning,

How can I receive vulnerability notifications?

Regards,
Armin Kuster

-- 
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] How to subscribe to your security list?

[Tom Lane]

akuster  writes:
> How can I receive vulnerability notifications?

Read release announcements in pgsql-announce.

regards, tom lane

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

Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Adrian Klaver]

On 01/13/2010 09:37 AM, Vincenzo Romano wrote:

2010/1/13 Vincenzo Romano:

2010/1/13 Vincenzo Romano:

2010/1/13 Adrian Klaver:

On Tuesday 12 January 2010 9:38:06 pm Vincenzo Romano wrote:

The static binding worked fine in the second EXECUTE USING statement but
not in the first one.
I still think that it's weird more than wishful.
I can work it around, though.

Il giorno 12 gen, 2010 4:13 p., "Tom Lane"  ha scritto:

Vincenzo Romano  writes:

I don't think so. Those variables should be evaluated with the USING>


*before* the actual executi...
Unfortunately, that's just wishful thinking, not how EXECUTE USING
actually works.

regards, tom lane


Without the whole function it is hard to say. Given the error I would say it is
a quoting issue. The table name is being substituted for, the other parameters
are not. It acts like the add_check clause is not part of the EXECUTE statement
and is just being passed through verbatim.

ERROR:  there is no parameter $1
CONTEXT: SQL statement "
  alter table public.test_part_2 add check(
data>=$1::timestamp and data<$2::timestamp and maga=$3 )


Well, for these case I prefer $-quoting: it's my personal taste that should
The rest of the function budy sheds no extra light on the problem.
For sure this fragment works fine:

   execute $l2$
 insert into $l2$||ct||$l2$
   select * from only public.test
   where data>=$1::timestamp and data<$2::timestamp and maga=$3
   $l2$ using rec.d0,rec.d1,rec.maga;

while thos one doesn't:

   execute $l2$
 alter table $l2$||ct||$l2$ add check(
data>=$1::timestamp and data<$2::timestamp and maga=$3 )
   $l2$ using rec.d0,rec.d1,rec.maga;

Please, observe that the WHERE condition and the USING predicate in
the first fragment is exactly the same as
the CHECK condition and the USING predicate in the second one (that's
intentional).
What I would still expect is that the EXECUTE ... USING statically
replaces the $1,$2 and $3 "variables" in the quoted string with the
*current values* of what can be found in the USING predicate.
No function arguments should be even taken into account as the "thing"
following the EXECUTE command is a *string literal*.

In the end, I think that Tom is wrong, simply because one fragment
works and the other one doesn't.
I'd expect either both or none working and would say this is a bug.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS



One can also check the documentation (v8.4.2) at page 800, chapter
"38.5.4. Executing Dynamic Commands"

The command string can use parameter values, which are referenced in
the command as $1, $2,
etc. These symbols refer to values supplied in the USING clause. This
method is often preferable to
inserting data values into the command string as text: it avoids
run-time overhead of converting the
values to text and back, and it is much less prone to SQL-injection
attacks since there is no need for
quoting or escaping. An example is:
EXECUTE ’SELECT count(*) FROM mytable WHERE inserted_by = $1 AND inserted<= $2’
INTO c
USING checked_user, checked_date;


Moreover, by putting the logging level to the maximum I've found where
the error is generated:

ERROR:  42P02: there is no parameter $1
...
LOCATION:  find_param_type, parse_expr.c:655

This is the backend (src/backend/parser), while I was expecting the
expansion to happen in the PL (src/pl/plpgsql/src).
This seems to me to confirm a bug where the actual string inside the
EXECUTE gets interpreted before (or without) the USING predicate,
at least in the case of the "ALTER TABLE", but not in the case of the SELECT.
Which in turn sounds even more weird to me.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS



Even worse!

This is one of my (best) attempts to work the issue around:

 execute $l2$
   select $l3$alter table $l2$||ct||$l2$ add check (
data>=$1::timestamp and data<$2::timestamp and maga=$3 )$l3$
 $l2$ into pr using rec.d0,rec.d1,rec.maga;
 raise info '%',pr;
 execute pr;

So, basically I (tried to) expand the ALTER TABLE command into a text
variable for later execution.
The RAISE statement is for basic debugging. The output is

INFO:  alter table public.test_part_1 add check ( data>=$1::timestamp
and data<$2::timestamp and maga=$3 )

despite the (usual) USING predicate!
Also in this case the $1, $2 and $3 "variables" have not been substituted.
Please, remember that this fragment works fine:

  execute $l2$
  insert into $l2$||ct||$l2$
select * from only public.test
where data>=$1::timestamp and data<$2::timestamp and maga=$3
$l2$ using rec.d0,rec.d1,rec.maga;





CREATE OR REPLACE FUNCTION public.alter_test(tbl text)
 RETURNS void
 LANGUAGE plpgsql
AS $function$
DECLARE
  len integer :=3;
BEGIN
  RAISE NOTICE

R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Vincenzo Romano]

My issue involves the USING predicates, though.

Il giorno 13 gen, 2010 10:26 p., "Adrian Klaver" 
ha scritto:

On 01/13/2010 09:37 AM, Vincenzo Romano wrote: > > 2010/1/13 Vincenzo
Romano

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Adrian Klaver]

On 01/13/2010 01:39 PM, Vincenzo Romano wrote:

My issue involves the USING predicates, though.



WARNING:Old joke

Doctor: What is wrong?
Patient: My elbow hurts when I do this, what should I do?
Doctor: Quit doing that.

USING is not working the way you want, mainly for the reason you found, 
the CHECK is being parsed before the variable is substituted. At this 
point it is time to do something different.






--
Adrian Klaver
adrian.kla...@gmail.com

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

R: Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Vincenzo Romano]

It is not the check or the select.
It is the way the substitution has been implemented. It looks like the code
replaces the variable name and not the value.
Which is different from what is written at page 800.
I only hope they won't change the manual to match the feature/bug (warning:
new joke)

Il giorno 13 gen, 2010 10:53 p., "Adrian Klaver" 
ha scritto:

On 01/13/2010 01:39 PM, Vincenzo Romano wrote: > > My issue involves the
USING predicates, though. >...
WARNING:Old joke

Doctor: What is wrong?
Patient: My elbow hurts when I do this, what should I do?
Doctor: Quit doing that.

USING is not working the way you want, mainly for the reason you found, the
CHECK is being parsed before the variable is substituted. At this point it
is time to do something different.





-- 

Adrian Klaver adrian.kla...@gmail.com

Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

[Martijn van Oosterhout]

On Wed, Jan 13, 2010 at 04:15:06PM +0100, Martin Flahault wrote:

[postgres]
> newbase=# select * from t1 order by contenu;
>  contenu 
> -
>  A
>  E
>  a
>  e

Postgresql outputs whatever the C library does on the underlying
system. The quality of this varies wildly.
>  à
> As with others DBMS (MySQL for example), diacritics should be ignored when 
> determining the sort order. Here is the expected output:

MySQL implements the unicode collation algorithm, which means it
essentially does what you want.
> 
> It seems there is a problem with the collating order on BSD systems with 
> diacritics using UTF8.

Last I checked, BSD did not support useful sorting on UTF-8 at all, so
it's not surprised it doesn't work.

> in a UTF8 text file and use the "sort" command on it, you will have the same 
> wrong output as with PostgreSQL :

Yes, that's the basic idea. Mac OS X apparently provides ICU underneath
for programs that would like true unicode collation, but there is
little chance that postgresql will ever use this.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while 
> boarding. Thank you for flying nlogn airlines.


signature.asc
Description: Digital signature

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Scott Mead]

On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
vincenzo.rom...@notorand.it> wrote:

> It is not the check or the select.
> It is the way the substitution has been implemented. It looks like the code
> replaces the variable name and not the value.
> Which is different from what is written at page 800.
> I only hope they won't change the manual to match the feature/bug (warning:
> new joke)
>

Page 800:

"Another restriction on parameter symbols is that they only work in SELECT,
INSERT, UPDATE, and DELETE commands. In other statement types (generically
called utility statements), you must insert values textually even if they
are just data values."

Re: [GENERAL] log_temp_files confusion

[Filip Rembiałkowski]

W dniu 13 stycznia 2010 19:52 użytkownik Andrej
napisał:

> 2010/1/14 Filip Rembiałkowski :
> > I would like to log usage of temporary files for sort/join operations,
> but
> > only when size of these files exceeds some threshold.
> >
> > So I set in postgresql.conf (this is 8.4.2)
> > log_temp_files = 4MB
> Just a wild guess... the DOCU says it's an integer, not an INT & STRING.
>

postgresql.conf.sample has these lines:
#log_temp_files = -1# log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files

I've set it in the config file to
log_temp_files = 4096

which shows as

fi...@postgres=# show log_temp_files ;
 log_temp_files

 4MB
(1 row)



>
> Try
> log_temp_files = 4194304
>

I will try this, but this would mean that config system is somehow broken.




-- 
Filip Rembiałkowski
JID,mailto:filip.rembialkow...@gmail.com
http://filip.rembialkowski.net/

Re: [GENERAL] postgres

[Jaime Casanova]

On Wed, Jan 13, 2010 at 11:31 AM, Amy Smith  wrote:
> all
> how to set up  PGTAB file ? any example for
> PGTAB=/opt/postgres/utilities/conf/pgtab
>

what is pgtab for? and where do you get it?

-- 
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

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

[GENERAL] Index not used when using a function

[Nick]

SELECT * FROM locations WHERE id = 12345 LIMIT 1

uses the primary key (id) index, but...

SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892)
LIMIT 1

does not and is verrry slow. Any ideas why? Whats weird is that it
works (uses index) on a previous db, but when I copied everything over
to a new db it doesnt. Ive ran vacuum + analyze, does the planner just
need more time to figure out that it needs to use an index?

-- 
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] How to subscribe to your security list?

[akuster]

On 01/13/2010 11:25 AM, Tom Lane wrote:
> akuster  writes:
>> How can I receive vulnerability notifications?
> 
> Read release announcements in pgsql-announce.
> 
>   regards, tom lane

Thanks,

- Armin

-- 
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] Index not used when using a function

[Tom Lane]

Nick  writes:
> SELECT * FROM locations WHERE id = 12345 LIMIT 1
> uses the primary key (id) index, but...

> SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892)
> LIMIT 1

> does not and is verrry slow. Any ideas why?

You didn't mark the function stable or immutable.
http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html

regards, tom lane

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

Re: [GENERAL] Index not used when using a function

[Nick]

On Jan 13, 4:21 pm, t...@sss.pgh.pa.us (Tom Lane) wrote:
> Nick  writes:
> > SELECT * FROM locations WHERE id = 12345 LIMIT 1
> > uses the primary key (id) index, but...
> > SELECT * FROM locations WHERE id = get_location_id_from_ip(641923892)
> > LIMIT 1
> > does not and is verrry slow. Any ideas why?
>
> You didn't mark the function stable or 
> immutable.http://www.postgresql.org/docs/8.4/static/xfunc-volatility.html
>
>                         regards, tom lane
>
> --
> Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org)
> To make changes to your 
> subscription:http://www.postgresql.org/mailpref/pgsql-general

That was it, thank you.

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

Re: [GENERAL] Extremely Slow Cascade Delete Operation

[Yan Cheng Cheok]

OMG, I never know what is index (Sorry for my newbies) I will study about them 
and update you all about their performance.

Thanks and Regards
Yan Cheng CHEOK


--- On Wed, 1/13/10, Craig Ringer  wrote:

> From: Craig Ringer 
> Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation
> To: "Yan Cheng Cheok" 
> Cc: pgsql-general@postgresql.org
> Date: Wednesday, January 13, 2010, 7:01 PM
> On 13/01/2010 4:09 PM, Yan Cheng
> Cheok wrote:
> > I have 3 tables - lot, unit and measurement
> > 
> > 1 lot is having relationship to many unit.
> > 1 unit is having relationship to many measurement.
> > delete cascade is being used among their relationship
> > 
> 
> > SemiconductorInspection=# delete from lot where lot_id
> = 2;
> 
> Are there indexes on fk_lot_id and fk_unit_id ? If not, a
> DELETE from lot will cause a seqscan of unit for affected
> units, and if any must be deleted each will cause a seqscan
> of measurement for affected measurements. That's going to
> get ugly fast.
> 
> --
> Craig Ringer
> 
> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 


  


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

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Adrian Klaver]

On Wednesday 13 January 2010 2:17:51 pm Scott Mead wrote:
> On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
>
> vincenzo.rom...@notorand.it> wrote:
> > It is not the check or the select.
> > It is the way the substitution has been implemented. It looks like the
> > code replaces the variable name and not the value.
> > Which is different from what is written at page 800.
> > I only hope they won't change the manual to match the feature/bug
> > (warning: new joke)
>
> Page 800:
>
> "Another restriction on parameter symbols is that they only work in SELECT,
> INSERT, UPDATE, and DELETE commands. In other statement types (generically
> called utility statements), you must insert values textually even if they
> are just data values."

Scott, thanks for that I must have read through that section several times at 
least with out picking up on it.

-- 
Adrian Klaver
adrian.kla...@gmail.com

-- 
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] Collate order on Mac OS X, text with diacritics in UTF-8

[Craig Ringer]

On 13/01/2010 11:15 PM, Martin Flahault wrote:


It seems there is a problem with the collating order on BSD systems with
diacritics using UTF8.
If you put this text :
a
A
à
é
e
E

in a UTF8 text file and use the "sort" command on it, you will have the
same wrong output as with PostgreSQL :
A
E
a
e
à
é


First: PostgreSQL expects the OS to behave correctly and sort according 
to the locale. It relies on the C library for this. If the C library 
doesn't do it right, PostgreSQL won't do it right either. So you need to 
get Mac OS X to do the right thing.


Your results match what I get on a Linux system without a properly 
generated fr_FR.UTF-8 locale. Libc falls back on the "C" locale, which 
sorts that way.


If I generate the fr_FR.UTF-8 locale and run the sort (on the file "x"), 
I get the desired result:


LANG=fr_FR.UTF-8 LC_ALL=fr_FR.UTF-8 sort x
a
A
à
e
E
é

I don't know Mac OS X well, but this is making me wonder if maybe you're 
just missing the required information for the locale, so libc is falling 
back on the "C" locale.


(Of course, being Mac OS X there are probably at least three out of date 
or simply false "man" pages describing the behaviour, none of which 
reflect the reality of a magic config key buried somewhere in NetInfo, 
for which the documentation is also completely out of date. Bitter? Me? 
Yeah, I admin a bunch of OS X machines on a business network.)


Hmm... a quick test suggests that Mac OS X (testing on 10.4) at least 
*thinks* it supports the fr_FR.UTF-8 locale:


osx104$ LANG=xxx LC_ALL=xxx locale
LANG="xxx"
LC_COLLATE="C"
LC_CTYPE="C"
LC_MESSAGES="C"
LC_MONETARY="C"
LC_NUMERIC="C"
LC_TIME="C"
LC_ALL="C"

osx104$ LANG=fr_FR.UTF-8 LC_ALL=fr_FR.UTF-8 locale
LANG="fr_FR.UTF-8"
LC_COLLATE="fr_FR.UTF-8"
LC_CTYPE="fr_FR.UTF-8"
LC_MESSAGES="fr_FR.UTF-8"
LC_MONETARY="fr_FR.UTF-8"
LC_NUMERIC="fr_FR.UTF-8"
LC_TIME="fr_FR.UTF-8"
LC_ALL="fr_FR.UTF-8"

osx104$ locale -a  | grep fr_FR
fr_FR
fr_FR.ISO8859-1
fr_FR.ISO8859-15
fr_FR.UTF-8

... yet it clearly doesn't:

osx104$ LANG=C LC_ALL=C sort x
A
E
a
e
à
é
osx104$ LANG=fr_FR.UTF-8 LC_ALL=fr_FR.UTF-8 sort x
A
E
a
e
à
é
osx104$ LANG=fr_FR.ISO8859-1 LC_ALL=fr_FR.ISO8859-1 sort x
A
E
a
e
à
é

Mac OS X seems to keep its locale config in /usr/share/locale . Looking 
there, there are clearly LC_COLLATE files for fr_FR.UTF-8 . However, 
they're identical to those for en_US.UTF-8:


osx104$ cd /usr/share/locale
osx104$ diff fr_FR.UTF-8/LC_COLLATE en_US.UTF-8/LC_COLLATE

... so your OS's localized collation support is broken/missing, at least 
if the same is true for more modern versions of OS X.


--
Craig Ringer

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

Re: [GENERAL] Backup strategies with significant bytea data

[Leigh Dyer]

Ivan Voras wrote:
> Leigh Dyer wrote:
>> Hi,
>>
>>For years now I've simply backed up my databases by doing a nightly
>>pg_dump, but since we added the ability for users to import binary
files
>>in to our application, which are stored in a bytea fields, the dump
>>sizes have gone through the roof — even with gzip compression,
they're
>>significantly larger than the on-disk size of the database. My guess
is
>>that this due to the way that the binary data from the bytea fields
is
>>encoded in the dump file when it's produced.
>
>Have you tried another dump format? E.g. "-F c" argument to pg_dump?

Hi Ivan,

I have tried the custom dump format, but it made no difference to the
backup sizes. Here are some numbers:

Database size on disk (according to pg_database_size): 1017MB
pg_dump size (standard format): 3475MB
pg_dump size (standard format, gzip-compressed): 1132MB
pg_dump size (custom format, ie: "-F c" option): 1134MB

This testing was done on my development server, with the database
restored a backup from the production server, so the database size on
disk might be a bit smaller than it would be if this was a running
database with some dead tuples in there. Still, there's a very big
difference between the raw data size and the dump sizes.

Thanks
Leigh



Please consider the environment before printing this message

-- 
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] Extremely Slow Cascade Delete Operation

[Yan Cheng Cheok]

I try to add index to tables.

(please refer to http://sites.google.com/site/yanchengcheok/Home/question.txt)

Database is designed in the following graphical view

(please refer to http://sites.google.com/site/yanchengcheok/Home/question.png)

Here is the setting of my database. All using default except syncrhonous_commit 
= off.

(please refer to http://sites.google.com/site/yanchengcheok/Home/database.PNG)

My database size is as follow :

SemiconductorInspection=# \timing on
Timing is on.
SemiconductorInspection=# SELECT count(*) FROM lot;
 count
---
 2
(1 row)


Time: 1.003 ms
SemiconductorInspection=# SELECT count(*) FROM unit;
 count

 206363
(1 row)


Time: 92.766 ms
SemiconductorInspection=# SELECT count(*) FROM measurement;
  count
-
 1650904
(1 row)


Time: 355.161 ms
SemiconductorInspection=#

I simply run a delete operation :

delete from lot where lot_id = 3;

It takes TWO hours and never able to return!

I even run VACUUM, with options FULL + FREEZE + ANALYZE

but it does not help at all.

Is there other optimization steps I had missed out?


Thanks and Regards
Yan Cheng CHEOK


--- On Wed, 1/13/10, Craig Ringer  wrote:

> From: Craig Ringer 
> Subject: Re: [GENERAL] Extremely Slow Cascade Delete Operation
> To: "Yan Cheng Cheok" 
> Cc: pgsql-general@postgresql.org
> Date: Wednesday, January 13, 2010, 7:01 PM
> On 13/01/2010 4:09 PM, Yan Cheng
> Cheok wrote:
> > I have 3 tables - lot, unit and measurement
> > 
> > 1 lot is having relationship to many unit.
> > 1 unit is having relationship to many measurement.
> > delete cascade is being used among their relationship
> > 
> 
> > SemiconductorInspection=# delete from lot where lot_id
> = 2;
> 
> Are there indexes on fk_lot_id and fk_unit_id ? If not, a
> DELETE from lot will cause a seqscan of unit for affected
> units, and if any must be deleted each will cause a seqscan
> of measurement for affected measurements. That's going to
> get ugly fast.
> 
> --
> Craig Ringer
> 
> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
> 


  


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

Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

[Craig Ringer]

Martijn van Oosterhout wrote:

>> in a UTF8 text file and use the "sort" command on it, you will have the same 
>> wrong output as with PostgreSQL :
> 
> Yes, that's the basic idea. Mac OS X apparently provides ICU underneath
> for programs that would like true unicode collation, but there is
> little chance that postgresql will ever use this.

Out of interest: Why not?

Using ICU would permit Pg to be independent of libc's collation rules,
finally permitting things like specifying a specific collation for a
textual sort. It'd make mixing data from different locales in a database
a lot easier (read: possible to do correctly).

Is this just a matter of "nobody cares enough to produce a solid, tested
patch with equivalent performance that doesn't turn people who try to
review it green with disgust" ... or are there specific reasons why
using something like ICU instead of libc's locale support is not
appropriate for Pg?

--
Craig Ringer

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

Re: [GENERAL] Extremely Slow Cascade Delete Operation

[Craig Ringer]

Yan Cheng Cheok wrote:

> I simply run a delete operation :
> 
> delete from lot where lot_id = 3;
> 
> It takes TWO hours and never able to return!

What does:

EXPLAIN DELETE FROM lot WHERE lot_id = 3;

report?

By the way, you've created a LOT of indexes. Indexes speed up lookups,
but can slow down insert/update/delete. They also use disk space. So
avoid creating indexes for things unless you know the index will
actually be used and be useful. Using EXPLAIN and EXPLAIN ANALYZE are
helpful for discovering this.

In general, I find creating indexes on foreign key columns to be a good
idea unless you never expect to DELETE from the parent table (say, if
you only TRUNCATE, if you rely on partitioning, or if the parent table
is append-only).

--
Craig Ringer

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

Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

[Tom Lane]

Craig Ringer  writes:
> Martijn van Oosterhout wrote:
>> Yes, that's the basic idea. Mac OS X apparently provides ICU underneath
>> for programs that would like true unicode collation, but there is
>> little chance that postgresql will ever use this.

> Out of interest: Why not?

There's plenty of discussion in the archives about it, but basically
ICU would represent a pretty enormous dependency and would lock us in
to having no other backend encoding but UTF8.

The state of OS X's POSIX-spec locale support is pretty pitiful, but on
the whole I'd say if you need better UTF8 locale support you could use
another OS.

regards, tom lane

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

Re: [GENERAL] FOSDEM dinner

[Dave Page]

Anyone else want to join us for dinner? Please shout even if you
haven't firmed up your travel plans yet, so I have a rough idea of
numbers.

Thanks.

On Fri, Dec 11, 2009 at 6:59 PM, Dave Page  wrote:
> As in previous years, we're looking at organising a dinner prior to
> the FOSDEM conference in Brussels, on the 5th February for anyone that
> wants to join us.
>
> So I can get an idea of numbers and provisionally book the restaurant,
> if you expect to be there (don't worry if you haven't booked your
> travel yet), please let me know off-list. I'll firm up the number
> closer to February.
>
> Thanks!
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
>



-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

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

Re: [GENERAL] postgres

[A. Kretschmer]

In response to Jaime Casanova :
> On Wed, Jan 13, 2010 at 11:31 AM, Amy Smith  wrote:
> > all
> > how to set up  PGTAB file ? any example for
> > PGTAB=/opt/postgres/utilities/conf/pgtab
> >
> 
> what is pgtab for? and where do you get it?

See:
http://postgresqldba.org/pgdatabase


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

-- 
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] Collate order on Mac OS X, text with diacritics in UTF-8

[Craig Ringer]

Tom Lane wrote:
> Craig Ringer  writes:
>> Martijn van Oosterhout wrote:
>>> Yes, that's the basic idea. Mac OS X apparently provides ICU underneath
>>> for programs that would like true unicode collation, but there is
>>> little chance that postgresql will ever use this.
> 
>> Out of interest: Why not?
> 
> There's plenty of discussion in the archives about it, but basically
> ICU would represent a pretty enormous dependency and would lock us in
> to having no other backend encoding but UTF8.

Thanks. You're right - I should've just STFA ;-) so I appreciate the
brief explanation.

> The state of OS X's POSIX-spec locale support is pretty pitiful, but on
> the whole I'd say if you need better UTF8 locale support you could use
> another OS.

That's my personal opinion too ... I have the "pleasure" of
administrating an OS X Server and six Mac Pro clients at work, and have
become well acquainted with the exciting variety of bugs, undocumented
"features", and bizarre quirks of that particular OS. POSIX locale
issues are the least of its issues.

Alas, people will want to run Pg on it anyway, especially when bundling
with an app. It'd be nice if it could be made to work smoothly ... but I
certainly don't care enough to try! Like you, I favour using an OS that
follows the specs it claims to support instead.

Perhaps someone who wants to use Mac OS X and Pg for their product will
come forward with some compat wrapper functions for the localizable
libc/posix functions, so Pg can just be built against the wrapper and
the rest of us need not care about OS X's bugs.

--
Craig Ringer

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

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Vincenzo Romano]

2010/1/14 Adrian Klaver :
> On Wednesday 13 January 2010 2:17:51 pm Scott Mead wrote:
>> On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
>>
>> vincenzo.rom...@notorand.it> wrote:
>> > It is not the check or the select.
>> > It is the way the substitution has been implemented. It looks like the
>> > code replaces the variable name and not the value.
>> > Which is different from what is written at page 800.
>> > I only hope they won't change the manual to match the feature/bug
>> > (warning: new joke)
>>
>> Page 800:
>>
>> "Another restriction on parameter symbols is that they only work in SELECT,
>> INSERT, UPDATE, and DELETE commands. In other statement types (generically
>> called utility statements), you must insert values textually even if they
>> are just data values."
>
> Scott, thanks for that I must have read through that section several times at
> least with out picking up on it.
>
> --
> Adrian Klaver
> adrian.kla...@gmail.com
>

Really?

That section is not in any page of the v8.4.2 documentation either PDF or HTML.
The sentence has been introduced (yesterday?) in 8.5devel, which is
far from being "current".

I only hope they won't change the manual to match the feature/bug
(warning: new joke)

So that was not a joke at all! :-(

-- 
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

-- 
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] Collate order on Mac OS X, text with diacritics in UTF-8

[Scott Marlowe]

On Wed, Jan 13, 2010 at 11:15 PM, Craig Ringer
 wrote:
> Perhaps someone who wants to use Mac OS X and Pg for their product will
> come forward with some compat wrapper functions for the localizable
> libc/posix functions, so Pg can just be built against the wrapper and
> the rest of us need not care about OS X's bugs.

I know this sounds crazy, but couldn't Apple be bothered to fix their OS?  :)

-- 
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] Collate order on Mac OS X, text with diacritics in UTF-8

[Tom Lane]

Craig Ringer  writes:
> Tom Lane wrote:
>> The state of OS X's POSIX-spec locale support is pretty pitiful, but on
>> the whole I'd say if you need better UTF8 locale support you could use
>> another OS.

> Alas, people will want to run Pg on it anyway, especially when bundling
> with an app. It'd be nice if it could be made to work smoothly ... but I
> certainly don't care enough to try! Like you, I favour using an OS that
> follows the specs it claims to support instead.

For the record, I *like* OS X; I'm typing this on a Macbook Pro.  But
it doesn't do everything, and one of the things it doesn't do well is
POSIX-spec locale support.

As you now know from having looked at the archives, there've been many
discussions of changing PG to not rely on the platform's locale
support.  But the bang-for-buck ratio of any such change doesn't seem
very attractive --- we have a lot of higher priority things to spend
our finite development manpower on.

regards, tom lane

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

Re: [GENERAL] Collate order on Mac OS X, text with diacritics in UTF-8

[Craig Ringer]

Scott Marlowe wrote:
> On Wed, Jan 13, 2010 at 11:15 PM, Craig Ringer
>  wrote:
>> Perhaps someone who wants to use Mac OS X and Pg for their product will
>> come forward with some compat wrapper functions for the localizable
>> libc/posix functions, so Pg can just be built against the wrapper and
>> the rest of us need not care about OS X's bugs.
> 
> I know this sounds crazy, but couldn't Apple be bothered to fix their OS?  :)

I see you don't use Macs much :-P

( Goes back to researching a way to make network automounts on login
work and properly support search in 10.6, since the only way that used
to work in 10.4 is broken by 10.6 )

--
Craig Ringer

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

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Scott Mead]

On Thu, Jan 14, 2010 at 7:19 AM, Vincenzo Romano <
vincenzo.rom...@notorand.it> wrote:

> 2010/1/14 Adrian Klaver :
> > On Wednesday 13 January 2010 2:17:51 pm Scott Mead wrote:
> >> On Wed, Jan 13, 2010 at 11:00 PM, Vincenzo Romano <
> >>
> >> vincenzo.rom...@notorand.it> wrote:
> >> > It is not the check or the select.
> >> > It is the way the substitution has been implemented. It looks like the
> >> > code replaces the variable name and not the value.
> >> > Which is different from what is written at page 800.
> >> > I only hope they won't change the manual to match the feature/bug
> >> > (warning: new joke)
> >>
> >> Page 800:
> >>
> >> "Another restriction on parameter symbols is that they only work in
> SELECT,
> >> INSERT, UPDATE, and DELETE commands. In other statement types
> (generically
> >> called utility statements), you must insert values textually even if
> they
> >> are just data values."
> >
> > Scott, thanks for that I must have read through that section several
> times at
> > least with out picking up on it.
> >
> > --
> > Adrian Klaver
> > adrian.kla...@gmail.com
> >
>
> Really?
>
> That section is not in any page of the v8.4.2 documentation either PDF or
> HTML.
> The sentence has been introduced (yesterday?) in 8.5devel, which is
> far from being "current".
>
>I only hope they won't change the manual to match the feature/bug
> (warning: new joke)
>
> So that was not a joke at all! :-(
>


   Well it is in 8.5 Devel, so it could have been added immediately after
your thread started yesterday, I'm honestly not sure.

http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html

Sorry for not posting that.  Either way, I if you really want a feature like
this added, it's usually better to take people's word and then make the case
for adding the feature.  If you declare it a bug and get belligerent, it
makes it harder to get features you'd like added.  I would say that coming
into this asking for a new feature would maybe have helped gain more ground.

Good luck

--Scott

PS -- I did see this in the 8.3 Docs after writing this note:

http://www.postgresql.org/docs/8.3/static/plpgsql-implementation.html

and I do see it here:

http://www.postgresql.org/docs/current/static/plpgsql-implementation.html








> --
> Vincenzo Romano
> NotOrAnd Information Technologies
> NON QVIETIS MARIBVS NAVTA PERITVS
>

Re: R: Re: R: Re: [GENERAL] Weird EXECUTE ... USING behaviour

[Tom Lane]

Scott Mead  writes:
>Well it is in 8.5 Devel, so it could have been added immediately after
> your thread started yesterday, I'm honestly not sure.

The particular paragraph mentioned was committed here
http://archives.postgresql.org/pgsql-committers/2009-11/msg00094.php
but as you note it was just a relocation of a comment that appeared
elsewhere for at least two years before that (and even then, it was
just documenting behavior that had existed since day one).

regards, tom lane

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

Re: [GENERAL] FOSDEM dinner

[Dave Page]

Just to clarify - this dinner is *not* the FOSDEM beer event, but an
Open Source database geeks get-together. It is on the same night as
the beer event though and will be very close assuming we get the
restaurant we want, so we can join the FOSDEM party after eating :-)

On Thu, Jan 14, 2010 at 11:33 AM, Dave Page  wrote:
> Anyone else want to join us for dinner? Please shout even if you
> haven't firmed up your travel plans yet, so I have a rough idea of
> numbers.
>
> Thanks.
>
> On Fri, Dec 11, 2009 at 6:59 PM, Dave Page  wrote:
>> As in previous years, we're looking at organising a dinner prior to
>> the FOSDEM conference in Brussels, on the 5th February for anyone that
>> wants to join us.
>>
>> So I can get an idea of numbers and provisionally book the restaurant,
>> if you expect to be there (don't worry if you haven't booked your
>> travel yet), please let me know off-list. I'll firm up the number
>> closer to February.
>>
>> Thanks!
>>
>> --
>> Dave Page
>> EnterpriseDB UK: http://www.enterprisedb.com
>>
>
>
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
>



-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] FOSDEM dinner

[Dave Coventry]

Sounds great fun!

However, I presume I'm on the wrong Continent! ;)

-- 
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] FOSDEM dinner

[Dave Page]

On Thu, Jan 14, 2010 at 1:14 PM, Dave Coventry  wrote:
> Sounds great fun!
>
> However, I presume I'm on the wrong Continent! ;)
>

I have no idea. I'm on the wrong landmass though, so there will be at
least one person arriving by air :-)

-- 
Dave Page
EnterpriseDB UK: http://www.enterprisedb.com

-- 
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] FOSDEM dinner

[Dave Coventry]

Yes, I'm in South Africa, which might make it problematic!

2010/1/14 Dave Page :
> On Thu, Jan 14, 2010 at 1:14 PM, Dave Coventry  wrote:
>> Sounds great fun!
>>
>> However, I presume I'm on the wrong Continent! ;)
>>
>
> I have no idea. I'm on the wrong landmass though, so there will be at
> least one person arriving by air :-)
>
> --
> Dave Page
> EnterpriseDB UK: http://www.enterprisedb.com
>

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