Re: [GENERAL] 'default nextval()' loses schema-qualification in dump ?

[Arnaud Lesauvage]

Le 6/07/2010 17:17, Tom Lane a écrit :

Arnaud Lesauvage  writes:

 As you have understood, I am not very savvy about postgresql's
 internals, but from what you say my guess is that the problem is int the
 psqlODBC is getting the default value of the sequence ?


I have no idea, because you haven't showed us what's happening, only
your oversimplified description of what's happening.  We really need to
see the exact SQL used to define the table (copy that from your dump,
perhaps) as well as the exact SQL used in the misbehaving insert
commands.


OK, here's the SQL.
First the creation of the table :

CREATE TABLE myschema.mytable
(
   gid serial NOT NULL,
   data character varying(255),
   CONSTRAINT pkey_mytable PRIMARY KEY (gid)
);


Then the dump :

SET statement_timeout = 0;
SET client_encoding = 'LATIN9';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET search_path = myschema, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
CREATE TABLE mytable (
 gid integer NOT NULL,
 data character varying(255)
);
ALTER TABLE myschema.mytable OWNER TO postgres;
CREATE SEQUENCE mytable_gid_seq
 START WITH 1
 INCREMENT BY 1
 NO MAXVALUE
 NO MINVALUE
 CACHE 1;
ALTER TABLE myschema.mytable_gid_seq OWNER TO postgres;
ALTER SEQUENCE mytable_gid_seq OWNED BY mytable.gid;
ALTER TABLE mytable ALTER COLUMN gid SET DEFAULT 
nextval('mytable_gid_seq'::regclass);

ALTER TABLE ONLY mytable
 ADD CONSTRAINT pkey_mytable PRIMARY KEY (gid);


The tables are linked via PsqlODBC with the following options :
TrueIsMinus1=1
BoolsAsChar=0
TextAsLongVarchar=1
Protocol=7.4-1
AB=0x2
Rowversionning=1
CommLog=1

The PsqlODBC log for the insert before the dump/restore (i.e. the good 
one) :


[0.063]conn=095C4198, query='SELECT "myschema"."mytable"."gid" FROM 
"myschema"."mytable" '

[0.063][ fetched 0 rows ]
[9.125]conn=095C4198, query='INSERT INTO  "myschema"."mytable"  ("data") 
VALUES (E'somedata')'
[9.125]conn=095C4198, query='select n.nspname, c.relname, a.attname, 
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, 
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then 
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c 
inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and 
c.relname = E'mytable' and n.nspname = E'myschema') inner join 
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and 
a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = 
a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = 
a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'

[9.141][ fetched 2 rows ]
[9.141]PGAPI_Columns: table='mytable',field_name='gid',type=23,name='int4'
[9.141]PGAPI_Columns: 
table='mytable',field_name='data',type=1043,name='varchar'
[9.141]conn=095C4198, query='SELECT 
currval('myschema.mytable_gid_seq'::regclass)'

[9.141][ fetched 1 rows ]
[9.141]conn=095C4198, query='COMMIT'
[9.141]conn=095C4198, query='SELECT "gid","data"  FROM 
"myschema"."mytable"  WHERE "gid" = 1'

[9.141][ fetched 1 rows ]



The PsqlODBC log for the insert after the dump/restore (i.e. the bad one) :

[11.328]conn=09FC0048, query='SELECT "myschema"."mytable"."gid" FROM 
"myschema"."mytable" '

[11.328][ fetched 0 rows ]
[15.438]conn=09FC0048, query='INSERT INTO  "myschema"."mytable" ("data") 
VALUES (E'somedata')'
[15.438]conn=09FC0048, query='select n.nspname, c.relname, a.attname, 
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull, 
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then 
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c 
inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and 
c.relname = E'mytable' and n.nspname = E'myschema') inner join 
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and 
a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid = 
a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid = 
a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'

[15.453][ fetched 2 rows ]
[15.453]PGAPI_Columns: table='mytable',field_name='gid',type=23,name='int4'
[15.453]PGAPI_Columns: 
table='mytable',field_name='data',type=1043,name='varchar'

[15.453]conn=09FC0048, query='SELECT currval('mytable_gid_seq'::regclass)'
[15.453]ERROR from backend during send_query: 'SERREUR'
[15.453]ERROR from backend during send_query: 'C42P01'
[15.453]ERROR from backend during send_query: 'Mla relation « 
mytable_gid_seq » n'existe pas'

[15.453]ERROR from backend during send_query: 'P16'
[15.453]ERROR from backend during send_query: 
'F.\src\backend\catalog\namespace.c'

[15.453]ERROR from backend during send_query: 'L276'
[15.453]ERROR from backend during send_query: 'RRangeVarGetRelid'
[15.453]STATEMENT ERROR: func=SC_execute, desc='(null

Re: [GENERAL] 'default nextval()' loses schema-qualification in dump ?

[Richard Huxton]

On 07/07/10 07:47, Arnaud Lesauvage wrote:

Le 6/07/2010 17:17, Tom Lane a écrit :

Arnaud Lesauvage writes:

As you have understood, I am not very savvy about postgresql's
internals, but from what you say my guess is that the problem is int the
psqlODBC is getting the default value of the sequence ?



[9.125]conn=095C4198, query='select n.nspname, c.relname, a.attname,
a.atttypid, t.typname, a.attnum, a.attlen, a.atttypmod, a.attnotnull,
c.relhasrules, c.relkind, c.oid, d.adsrc, case t.typtype when 'd' then
t.typbasetype else 0 end, t.typtypmod from (((pg_catalog.pg_class c
inner join pg_catalog.pg_namespace n on n.oid = c.relnamespace and
c.relname = E'mytable' and n.nspname = E'myschema') inner join
pg_catalog.pg_attribute a on (not a.attisdropped) and a.attnum > 0 and
a.attrelid = c.oid) inner join pg_catalog.pg_type t on t.oid =
a.atttypid) left outer join pg_attrdef d on a.atthasdef and d.adrelid =
a.attrelid and d.adnum = a.attnum order by n.nspname, c.relname, attnum'


This is psqlODBC getting the sequence name (if you run this query it's 
the adsrc column). If I remember correctly, that's supposed to be the 
human-readable version of an expression and preserved *as entered by the 
user* (or pg_restore in your case).


If you start psql with the "-E" option and do \d myschema.mytable you'll 
be able to see how it gets the sequence-name. About half-way down the 
list of queries it runs you'll see a reference to pg_get_expr(...) - 
that turns an internal representation into a useful usable one.


I don't know why psqlODBC isn't using that. The function has been around 
for a while. Hmm - it's present back in 7.4 although it's not used in \d 
- that does reference adsrc directly.


Just grabbed the source download for the latest version and it still 
looks like it's using adsrc (I just searched for that and pg_get_expr). 
There should probably be a change in info.c around line 2091 to add a 
check for a recent version of PG (8+) and use pg_get_expr. Check on the 
odbc mailing-list - there may be an updated version available for you to 
test.


--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Problems with Vista and Windows 7

[el dorado]

Hello!
I use Postgres 8.4.1 as service, with Windows 7. It seems to me the same 
situation will take place with Vista.

I've notice that in some special cases PG uses folder %APPDATA% for Windows 
user 'postgres', instead of the PostgreSQL-specific application data folder 
(f.e., C:\PostgreSQL\8.4\data). It results in some unexpected bugs. There were 
no such effects in Win XP and 2000.

Has anyone fixed something similar to the described situation? Is it possible 
to improve it?

Regards, Marina.

-- 
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 Get Column Names from the Table

[venkat]

Dear All,

   How to get Column Names from Table in PostgreSQL.

Thanks and Regards,

Venkat

Re: [GENERAL] How to Get Column Names from the Table

[AI Rumman]

Use:

\d tablename


On Wed, Jul 7, 2010 at 3:08 PM, venkat  wrote:

> Dear All,
>
>How to get Column Names from Table in PostgreSQL.
>
> Thanks and Regards,
>
> Venkat
>

Re: [GENERAL] [SQL] How to Get Column Names from the Table

[Viktor Bojović]

\d tableName

On Wed, Jul 7, 2010 at 11:08 AM, venkat  wrote:

> Dear All,
>
>How to get Column Names from Table in PostgreSQL.
>
> Thanks and Regards,
>
> Venkat
>



-- 
---
Viktor Bojović
---
Wherever I go, Murphy goes with me

Re: [GENERAL] How to Get Column Names from the Table

[Jayadevan M]

> Use:

> \d tablename

And what I really like about it is the way you can make a guess about the 
table name and use * . 

postgres-# \d mt*
  Table "public.mt1"
 Column |  Type   | Modifiers
+-+---
 id | integer |


  Table "public.mt2"
 Column |  Type   | Modifiers
+-+---
 id | integer |


  Table "public.mt3"
 Column |  Type   | Modifiers
+-+---
 id | integer |

Regards,
Jayadevan





DISCLAIMER: 

"The information in this e-mail and any attachment is intended only for 
the person to whom it is addressed and may contain confidential and/or 
privileged material. If you have received this e-mail in error, kindly 
contact the sender and destroy all copies of the original communication. 
IBS makes no warranty, express or implied, nor guarantees the accuracy, 
adequacy or completeness of the information contained in this email or any 
attachment and is not liable for any errors, defects, omissions, viruses 
or for resultant loss or damage, if any, direct or indirect."






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

[GENERAL] Odd behaviour in update rule

[Denis Gasparin]

Hi. 

I have an odd behaviour on an update rule in postgresql 8.2 and i'd like to 
know if the behaviour is as expected or not. 

The following sql statements prepare the table, view and rule. 

create table main ( 
id integer not null primary key, 
value integer not null 
); 

create view view_main (id,value) as select * from main; 

create rule update_view_main as on update to view_main do instead update main 
set value=new.value where id = old.id; 

insert into main values(1,1); 

In table main we have only one record with id=1 and value=1. 

Now we suppose that two clients connect simultaneously to the database and 
execute the following statements in parallel (CLIENT A first then CLIENT B). 



CLIENT A: begin; 
CLIENT B: begin; 
CLIENT A: update view_main set value=value+1 where id=1; 
CLIENT B: update view_main set value=value+1 where id=1; --waits for client A 
to commit changes 
CLIENT A: commit; 
CLIENT B: commit; 

CLIENT A: select * from view_main; 
--expected value = 2 
--returned value = 2 

CLIENT A: select * from view_main; 
--expected value = 2 
--returned value = 3 


I would expect that the two updates behaves exactly as a direct update on 
main... (returned value=3) but this is not the case... 
Is it the correct behaviour? 

Thank you, 
Denis 

Re: [GENERAL] [SQL] How to Get Column Names from the Table

[Sreelatha G]

Hi,

To get column names only

 select column_name from information_schema.columns where
table_name='captor_prime_aggregates';

Thanks
Sreelatha
On Wed, Jul 7, 2010 at 2:44 PM, Viktor Bojović wrote:

> \d tableName
>
>
> On Wed, Jul 7, 2010 at 11:08 AM, venkat  wrote:
>
>> Dear All,
>>
>>How to get Column Names from Table in PostgreSQL.
>>
>> Thanks and Regards,
>>
>> Venkat
>>
>
>
>
> --
> ---
> Viktor Bojović
> ---
> Wherever I go, Murphy goes with me
>

Re: [GENERAL] [SQL] How to Get Column Names from the Table

[venkat]

Dear Sreelatha.

  It is working fine. Thanks alot.

Thanks and Regards,

Venkat

On Wed, Jul 7, 2010 at 3:42 PM, Sreelatha G  wrote:

> Hi,
>
> To get column names only
>
>  select column_name from information_schema.columns where
> table_name='captor_prime_aggregates';
>
> Thanks
> Sreelatha
> On Wed, Jul 7, 2010 at 2:44 PM, Viktor Bojović 
> wrote:
>
>> \d tableName
>>
>>
>> On Wed, Jul 7, 2010 at 11:08 AM, venkat  wrote:
>>
>>> Dear All,
>>>
>>>How to get Column Names from Table in PostgreSQL.
>>>
>>> Thanks and Regards,
>>>
>>> Venkat
>>>
>>
>>
>>
>> --
>> ---
>> Viktor Bojović
>> ---
>> Wherever I go, Murphy goes with me
>>
>
>

Re: [GENERAL] TupleDesc and HeapTuple

[Luca Ferrari]

On Tuesday, July 06, 2010 09:58:45 pm Alvaro Herrera's cat walking on the 
keyboard wrote: 
> You're supposed to know which relation you got the HeapTuple from, so
> you get the TupleDesc from there.

True, but if I want to pass the heaptuple around I must pass also its tuple 
desc, or the receiver will not know how the tuple is composed. Maybe this case 
never happens/happened.

Luca

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

[GENERAL] optimizer choosing the wrong index

[Martin Below]

Hello,

I'm facing a strange problem where the optimizer does pick the wrong index.
Im using postgres 8.4, and my schema look like this:

 client_id   | character varying(36)       | not null
 key         | character varying(16)       | not null
 expires_on  | timestamp without time zone | not null

Indexe:
   "ps_pkey" PRIMARY KEY, btree (client_id, key)
   "idx_correct" btree (client_id, expires_on)
   "idx_wrong" btree (expires_on)


the query:
explain analyze select * from ps where client_id='foo' and expires_on
= timestamp '2010-11-24';

                                                 QUERY PLAN
---
 Index Scan using idx_wrong on ps  (cost=0.00..8.29 rows=1 width=61)
(actual time=0.010..0.010 rows=0 loops=1)
  Index Cond: (expires_on = '2010-11-24 00:00:00'::timestamp without time zone)
  Filter: ((client_id)::text = 'foo'::text)
 Total runtime: 0.089 ms


Why is "idx_wrong" used (which only includes one of the fields
queried) instead of idx_correct (which contains both fields)?
If I drop idx_wrong, the correct index is choosen:

test=# explain analyze select * from ps where client_id='foo' and
expires_on = timestamp '2010-11-24';

                                                      QUERY PLAN
-
 Index Scan using idx_correct on ps  (cost=0.00..8.34 rows=1 width=53)
(actual time=0.023..0.023 rows=0 loops=1)
  Index Cond: (((client_id)::text = 'foo'::text) AND (expires_on =
'2010-11-24 00:00:00'::timestamp without time zone))
 Total runtime: 0.058 ms



The problem seems to me that the estimates costs are not correct.
With the table containing about 200.000 records, using the "wrong"
index takes about 22 times as long as using the "right" index. I did
run "vacuum analyze", without any effect.

Any help would be very much appreciated.

Thanks,
Martin Below

-- 
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] TupleDesc and HeapTuple

[Pavel Stehule]

2010/7/7 Luca Ferrari :
> On Tuesday, July 06, 2010 09:58:45 pm Alvaro Herrera's cat walking on the
> keyboard wrote:
>> You're supposed to know which relation you got the HeapTuple from, so
>> you get the TupleDesc from there.
>
> True, but if I want to pass the heaptuple around I must pass also its tuple
> desc, or the receiver will not know how the tuple is composed. Maybe this case
> never happens/happened.
>

In pg core - receiver have to have actual datatype everywhere - so if
you write a some general function, then you put a Datum value -
universal PostgreSQL datatype and you have to put a datatype
identification - Oid, typmod or TupleDesc.

Pavel

> Luca
>
> --
> 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] optimizer choosing the wrong index

[Merlin Moncure]

On Wed, Jul 7, 2010 at 7:42 AM, Martin Below
 wrote:
> Hello,
>
> I'm facing a strange problem where the optimizer does pick the wrong index.
> Im using postgres 8.4, and my schema look like this:
>
>  client_id   | character varying(36)       | not null
>  key         | character varying(16)       | not null
>  expires_on  | timestamp without time zone | not null
>
> Indexe:
>    "ps_pkey" PRIMARY KEY, btree (client_id, key)
>    "idx_correct" btree (client_id, expires_on)
>    "idx_wrong" btree (expires_on)
>
>
> the query:
> explain analyze select * from ps where client_id='foo' and expires_on
> = timestamp '2010-11-24';
>
>                                                  QUERY PLAN
> ---
>  Index Scan using idx_wrong on ps  (cost=0.00..8.29 rows=1 width=61)
> (actual time=0.010..0.010 rows=0 loops=1)
>   Index Cond: (expires_on = '2010-11-24 00:00:00'::timestamp without time 
> zone)
>   Filter: ((client_id)::text = 'foo'::text)
>  Total runtime: 0.089 ms
>
>
> Why is "idx_wrong" used (which only includes one of the fields
> queried) instead of idx_correct (which contains both fields)?
> If I drop idx_wrong, the correct index is choosen:
>
> test=# explain analyze select * from ps where client_id='foo' and
> expires_on = timestamp '2010-11-24';
>
>                                                       QUERY PLAN
> -
>  Index Scan using idx_correct on ps  (cost=0.00..8.34 rows=1 width=53)
> (actual time=0.023..0.023 rows=0 loops=1)
>   Index Cond: (((client_id)::text = 'foo'::text) AND (expires_on =
> '2010-11-24 00:00:00'::timestamp without time zone))
>  Total runtime: 0.058 ms
>
>
>
> The problem seems to me that the estimates costs are not correct.
> With the table containing about 200.000 records, using the "wrong"
> index takes about 22 times as long as using the "right" index. I did
> run "vacuum analyze", without any effect.
>
> Any help would be very much appreciated.

can you supply the plans on the actual tables?  the 'wrong' index
might actually be the 'right' one if expires_on is of high cardinality
(perhaps it's distributed badly and the table needs a stats tweak to
make it correct).

btw, consider using 'date' type for dates vs non timezone timestamp,
which is a bit of a kludge imo.

You can probably force the right index like this:
explain analyze select * from ps where (client_id, expires_on) =
('foo', '2010-11-24'::timestamp);

merlin

-- 
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] make view with union return one record

[Merlin Moncure]

On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson  wrote:
> I have gis data in layers, and a pin might appear in either layer, or both
> (parcelPoly, parcelPoint), or neither (and I dont care which I find)... so I
> have this view:
>
>
> create view howardia.getPoint(shapeid, x, y, acres, pin, extent) as
>  SELECT gid,
>        st_x(st_centroid(the_geom)) AS x,
>        st_y(st_centroid(the_geom)) AS y,
>        acreage,
>        county_pin,
>        st_box2d(st_expand(the_geom, 100))
>  FROM howardia.parcelPoly
>  UNION ALL
>  SELECT gid,
>        st_x(the_geom) AS x,
>        st_y(the_geom) AS y,
>        acreage,
>        county_pin,
>        st_box2d(st_expand(the_geom, 100))
>  FROM howardia.parcelPoint;
>
> Which works fine for what I'm using.. it returns one or two records, and my
> code just takes the first record and runs with it.
>
> but now... I'm adding something new, and having it return multiple records
> per pin is causing problems.  I tried adding a limit inside the view but
> then it never returns anything:

??? why not --  can you double check that?

create view l as select * from (select 'a' union all select 'b') q limit 1;

select * from l;
 ?column?
--
 a
(1 row)

merlin

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

[GENERAL] Want to schedule tasks for the future

[Matthew Wilson]

Just recently I discovered the listen/notify feature in postgresql.
Now I don't have external processes polling tables, watching for new
inserted rows.

Anyhow, I'm curious if there is some other feature that will help me out
with a new puzzle.

I want to store emails to deliver at a later time in my database.  For
example, I want to remember that tomorrow morning at 9:00 am, I want to
send a particular email.

I'll use a table sort of like this

create table scheduled_email (
to_address text,
email_subject text,
email_body text,
deliver_at timestamp,
sent boolean
);

I know I could write an external process to poll this table and select
all rows where deliver_at < current_timestamp and sent = 'f'.

But is there some other way inside postgresql that will do something
similar?  I would want something like listen/notify, where postgres
starts an external process when any data exist.

Thanks for the help.

Matt


-- 
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] make view with union return one record

[Andy Colson]

On 7/7/2010 8:27 AM, Merlin Moncure wrote:

On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson  wrote:

I have gis data in layers, and a pin might appear in either layer, or both
(parcelPoly, parcelPoint), or neither (and I dont care which I find)... so I
have this view:


create view howardia.getPoint(shapeid, x, y, acres, pin, extent) as
  SELECT gid,
st_x(st_centroid(the_geom)) AS x,
st_y(st_centroid(the_geom)) AS y,
acreage,
county_pin,
st_box2d(st_expand(the_geom, 100))
  FROM howardia.parcelPoly
  UNION ALL
  SELECT gid,
st_x(the_geom) AS x,
st_y(the_geom) AS y,
acreage,
county_pin,
st_box2d(st_expand(the_geom, 100))
  FROM howardia.parcelPoint;

Which works fine for what I'm using.. it returns one or two records, and my
code just takes the first record and runs with it.

but now... I'm adding something new, and having it return multiple records
per pin is causing problems.  I tried adding a limit inside the view but
then it never returns anything:


??? why not --  can you double check that?

create view l as select * from (select 'a' union all select 'b') q limit 1;

select * from l;
  ?column?
--
  a
(1 row)

merlin


Humm.. yea, had to look at that a little closer... but no, it does not 
work when you put a where clause on it:


gis=# create view l as select * from (select 'a' as cname union all 
select 'b') q limit 1;

CREATE VIEW


-- This works as you noted:
gis=# select * from l ;
 cname
---
 a
(1 row)


-- And as long as you only ever want the first row, it works too:
gis=# select * from l where cname = 'a';
 cname
---
 a
(1 row)

-- But this is more akin to what I'm doing:
gis=# select * from l where cname = 'b';
 cname
---
(0 rows)


In my example, if I put the limit on the view, then I can:

gis=# select x,y,pin from getpoint;
x |y |   pin
--+--+-
 5185561.61663698 | 3906727.90184405 | 19016163402
(1 row)

So the entire view is now just one record.

I'm guessing the limit and the where are being applied in the wrong 
order.  (Correction, not the wrong order, rather not the order I want)


Hum... I wonder if I subselect the table enough times if I can swap the 
where and the limit...  I'll have to play around with that.


-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] I wonder what Postgres would be listed for?

[Merlin Moncure]

This page speaks for itself :D

https://shop.oracle.com/pls/ostore/product?p1=berkeleydb&sc=ocom_berkeleydb

merlin

-- 
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] Want to schedule tasks for the future

[Sam Mason]

On Wed, Jul 07, 2010 at 01:53:25PM +, Matthew Wilson wrote:
> create table scheduled_email (
> to_address text,
> email_subject text,
> email_body text,
> deliver_at timestamp,
> sent boolean
> );
> 
> I know I could write an external process to poll this table and select
> all rows where deliver_at < current_timestamp and sent = 'f'.
> 
> But is there some other way inside postgresql that will do something
> similar?  I would want something like listen/notify, where postgres
> starts an external process when any data exist.

You could have a trigger tell you when the first item to be delivered
changes.  I'd still be tempted to keep the waiting outside the database.

-- 
  Sam  http://samason.me.uk/

-- 
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] optimizer choosing the wrong index

[Tom Lane]

Martin Below  writes:
> Why is "idx_wrong" used (which only includes one of the fields
> queried) instead of idx_correct (which contains both fields)?

It's not immediately clear that that choice is "wrong".  A two-column
index is bigger and hence more expensive to search than a one-column
index --- perhaps quite substantially so, given this particular
combination of datatypes.  The planner is probably estimating that
the expires_on condition is selective enough that nothing much will be
gained by using the two-column index.  If that's wrong (which is not
proven by your example) you might be able to fix it by increasing
the stats target for the table.  Also, if you haven't customized
effective_cache_size, increasing that makes large indexes look
cheaper to use.

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] Want to schedule tasks for the future

[Vick Khera]

On Wed, Jul 7, 2010 at 9:53 AM, Matthew Wilson  wrote:
> But is there some other way inside postgresql that will do something
> similar?  I would want something like listen/notify, where postgres
> starts an external process when any data exist.
>

No, there is no facility to do this within the database.  If you want
a job scheduler, perhaps you should investigate such software.  Take a
look at Celery.  It is pretty new, and I have not personally tried it.
 It adds a layer on top of a messaging infrastructure (such as
rabbitmq) or a database.

-- 
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] make view with union return one record

[Andy Colson]

On 7/7/2010 8:27 AM, Merlin Moncure wrote:

On Tue, Jul 6, 2010 at 3:43 PM, Andy Colson  wrote:

I have gis data in layers, and a pin might appear in either layer, or both
(parcelPoly, parcelPoint), or neither (and I dont care which I find)... so I
have this view:


create view howardia.getPoint(shapeid, x, y, acres, pin, extent) as
  SELECT gid,
st_x(st_centroid(the_geom)) AS x,
st_y(st_centroid(the_geom)) AS y,
acreage,
county_pin,
st_box2d(st_expand(the_geom, 100))
  FROM howardia.parcelPoly
  UNION ALL
  SELECT gid,
st_x(the_geom) AS x,
st_y(the_geom) AS y,
acreage,
county_pin,
st_box2d(st_expand(the_geom, 100))
  FROM howardia.parcelPoint;

Which works fine for what I'm using.. it returns one or two records, and my
code just takes the first record and runs with it.

but now... I'm adding something new, and having it return multiple records
per pin is causing problems.  I tried adding a limit inside the view but
then it never returns anything:


??? why not --  can you double check that?

create view l as select * from (select 'a' union all select 'b') q limit 1;

select * from l;
  ?column?
--
  a
(1 row)

merlin


Ok, I found it.  I don't know why I was making it more difficult than it 
needed to be.  Once I stopped thinking about the view, and thought of it 
just like any other table, then I have dups, and I want one of each.. 
and that can be solved with distinct on:


select distinct on (pin) *
from getpoint
where pin in ('11025082102', '3207701');

Thanks for letting me think out loud.

-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] Trigger for modification timestamp column

[Johan Andersson]

Hello!

I am trying to write a trigger for updating a modification column and am
having some trouble getting it to behave as I want. 

The trigger should set the column to the supplied value if it is set in the
UPDATE statement and to the current timestamp [NOW()] if it is not. The
problem is that I don't know how to check if the column is set or not. I can
check the column's value for NULL but that doesn't work if I want the column
to accept NULL values (meaning "unmodified").

I would like something like:
CREATE FUNCTION update_modified()
RETURNS TRIGGER AS $$
BEGIN
IF NOT isset(NEW.modified) THEN
NEW.modified = NOW();
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

Does anyone know how to do this?

Thanks in advance!

/ Johan
-- 
View this message in context: 
http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] Trigger for modification timestamp column

[Pavel Stehule]

2010/7/7 Johan Andersson :
>
> Hello!
>
> I am trying to write a trigger for updating a modification column and am
> having some trouble getting it to behave as I want.
>
> The trigger should set the column to the supplied value if it is set in the
> UPDATE statement and to the current timestamp [NOW()] if it is not. The
> problem is that I don't know how to check if the column is set or not. I can
> check the column's value for NULL but that doesn't work if I want the column
> to accept NULL values (meaning "unmodified").
>
> I would like something like:
> CREATE FUNCTION update_modified()
>    RETURNS TRIGGER AS $$
>        BEGIN
>            IF NOT isset(NEW.modified) THEN
>                NEW.modified = NOW();
>            END IF;
>            RETURN NEW;
>        END;
>    $$ LANGUAGE 'plpgsql';
>
> Does anyone know how to do this?

sorry - it isn't possible - you cannot do it. You need one column more
for information if value is modified or not.

Regards

Pavel Stehule



>
> Thanks in advance!
>
> / Johan
> --
> View this message in context: 
> http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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

Re: [GENERAL] Trigger for modification timestamp column

[Susan Cassidy]

Can't you check it using something like this:

IF OLD.modified = NEW.modified THEN
  NEW.modified = NOW();
END IF;
RETURN NEW;

Susan
-Original Message-
From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Johan Andersson
Sent: Wednesday, July 07, 2010 6:49 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Trigger for modification timestamp column


Hello!

I am trying to write a trigger for updating a modification column and am
having some trouble getting it to behave as I want. 

The trigger should set the column to the supplied value if it is set in the
UPDATE statement and to the current timestamp [NOW()] if it is not. The
problem is that I don't know how to check if the column is set or not. I can
check the column's value for NULL but that doesn't work if I want the column
to accept NULL values (meaning "unmodified").

I would like something like:
CREATE FUNCTION update_modified()
RETURNS TRIGGER AS $$
BEGIN
IF NOT isset(NEW.modified) THEN
NEW.modified = NOW();
END IF;
RETURN NEW;
END;
$$ LANGUAGE 'plpgsql';

Does anyone know how to do this?

Thanks in advance!

/ Johan
-- 
View this message in context: 
http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

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

Re: [GENERAL] Trigger for modification timestamp column

[Pavel Stehule]

2010/7/7 Susan Cassidy :
> Can't you check it using something like this:
>
> IF OLD.modified = NEW.modified THEN
>  NEW.modified = NOW();
> END IF;
> RETURN NEW;

but this take only case where column modified was changed. There are
not a functionality for detection if user explicitly updated column or
not

Pavel

>
> Susan
> -Original Message-
> From: pgsql-general-ow...@postgresql.org 
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Johan Andersson
> Sent: Wednesday, July 07, 2010 6:49 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Trigger for modification timestamp column
>
>
> Hello!
>
> I am trying to write a trigger for updating a modification column and am
> having some trouble getting it to behave as I want.
>
> The trigger should set the column to the supplied value if it is set in the
> UPDATE statement and to the current timestamp [NOW()] if it is not. The
> problem is that I don't know how to check if the column is set or not. I can
> check the column's value for NULL but that doesn't work if I want the column
> to accept NULL values (meaning "unmodified").
>
> I would like something like:
> CREATE FUNCTION update_modified()
>    RETURNS TRIGGER AS $$
>        BEGIN
>            IF NOT isset(NEW.modified) THEN
>                NEW.modified = NOW();
>            END IF;
>            RETURN NEW;
>        END;
>    $$ LANGUAGE 'plpgsql';
>
> Does anyone know how to do this?
>
> Thanks in advance!
>
> / Johan
> --
> View this message in context: 
> http://old.nabble.com/Trigger-for-modification-timestamp-column-tp29096359p29096359.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

-- 
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] Problems with Vista and Windows 7

[Igor Neyman]

 

> -Original Message-
> From: el dorado [mailto:do_ra...@mail.ru] 
> Sent: Wednesday, July 07, 2010 4:19 AM
> To: pgsql-general@postgresql.org
> Subject: Problems with Vista and Windows 7
> 
> Hello!
> I use Postgres 8.4.1 as service, with Windows 7. It seems to 
> me the same situation will take place with Vista.
> 
> I've notice that in some special cases PG uses folder 
> %APPDATA% for Windows user 'postgres', instead of the 
> PostgreSQL-specific application data folder (f.e., 
> C:\PostgreSQL\8.4\data). It results in some unexpected bugs. 
> There were no such effects in Win XP and 2000.
> 
> Has anyone fixed something similar to the described 
> situation? Is it possible to improve it?
> 
> Regards, Marina.
>

Could you please be more specific in describing what problems are you
seeing on Win7?
Did you check, that registry key "Data Directory" is set properly in
"HKEY_LOCAL_MACHINE\SOFTWARE\PostgreSQL\Installations\postgresql-8.4"
folder?

Regards,
Igor Neyman 

-- 
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] Trigger for modification timestamp column

[Michael Nolan]

On Wed, Jul 7, 2010 at 8:48 AM, Johan Andersson  wrote:

>
> Hello!
>
> I am trying to write a trigger for updating a modification column and am
> having some trouble getting it to behave as I want.
>
> The trigger should set the column to the supplied value if it is set in the
> UPDATE statement and to the current timestamp [NOW()] if it is not. The
> problem is that I don't know how to check if the column is set or not. I
> can
> check the column's value for NULL but that doesn't work if I want the
> column
> to accept NULL values (meaning "unmodified").
>

It isn't very clear just what modification it is you're tracking, a specific
column or the entire row?

Is this a column of type timestamp?

You can do a comparison between OLD.XXX and NEW.XXX in a before-update
trigger but you will need to take into account null values.  For example, is
it possible for the modified flag go from non-null to null, and if so what
does that mean?

Suppose the update statement explicitly sets XXX to the same value it
already has, is that updating it or not?

Re: [GENERAL] Want to schedule tasks for the future

[bs]

Matthew Wilson schrieb:
> Just recently I discovered the listen/notify feature in postgresql.
> Now I don't have external processes polling tables, watching for new
> inserted rows.
> 


You could try
http://www.pogo.org.uk/~mark/pgnotifyd/

Just found it, not tested until now.

Bernhard


-- 
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] Want to schedule tasks for the future

[Chris Browne]

m...@tplus1.com (Matthew Wilson) writes:
> Just recently I discovered the listen/notify feature in postgresql.
> Now I don't have external processes polling tables, watching for new
> inserted rows.
>
> Anyhow, I'm curious if there is some other feature that will help me out
> with a new puzzle.
>
> I want to store emails to deliver at a later time in my database.  For
> example, I want to remember that tomorrow morning at 9:00 am, I want to
> send a particular email.
>
> I'll use a table sort of like this
>
> create table scheduled_email (
> to_address text,
> email_subject text,
> email_body text,
> deliver_at timestamp,
> sent boolean
> );
>
> I know I could write an external process to poll this table and select
> all rows where deliver_at < current_timestamp and sent = 'f'.
>
> But is there some other way inside postgresql that will do something
> similar?  I would want something like listen/notify, where postgres
> starts an external process when any data exist.
>
> Thanks for the help.

You do need to have some set of infrastructure there that is
interested in listening to such events, and doing something about
them.

That "something" needs to know about the nature of the queue that you
have set up, and it needs to know what sorts of "somethings" you want
done to the items in the queue.

Those various "somethings" are pretty specific to your application, so
I don't imagine you'll get much agreement on the notion that there
should be some internal Postgres component that should act on this.

It would probably be a neat idea to have some "sample listening
daemon" that has a series of parameters:

  - PGHOST/PGDATABASE/PGPORT/PGUSER... to indicate what database to listen to

  - LISTENEVENT to indicate which event to listen for

  - PROCESSQUEUE to indicate the Unix program to run to process the
queue when the event of LISTENEVENT is received

That's enough to characterize how to invoke such a listener.

There *might* be meaningful structure inside the queue processor, what
with various possibilities:

 - You might want to do something in a transaction for each item in the
   queue, if it's of a "pretty transactional" nature.  This is actually
   about the simplest case

 - You might want to process several queue items in a single
   transaction, to cut down on transactional costs.

   One might then debate:
   - Doing the first $N items, marking them "done," and iterating
 until complete;
   - Opening a cursor, and grabbing a few items from the cursor at a
 time.

That's starting to add to a goodly number of possibilities.

You could push work requests over to some sort of in-memory message
queueing system (lots of them out there - AMQP, RabbitMQ, ActiveMQ, or
even Spread) to make it easy to efficiently spread work across a bunch
of worker processes, if that seemed useful.  That needs extra
infrastructure (e.g. - another "MQ" subsystem), and certainly adds
complications.  To some, that may be quite worthwhile.

This is all looking increasingly like "stuff that isn't inside the
DBMS engine," and I don't imagine you'd get much agreement on the
merits of trying to force the functionality into the core of Postgres.

I don't think it's a notably good idea, myself.  Indeed, this would
force things into the identical security context as the DBMS itself,
which seems a pretty bad thing to me.
-- 
let name="cbbrowne" and tld="gmail.com" in String.concat "@" [name;tld];;
http://linuxfinances.info/info/linuxdistributions.html
Rules of the Evil Overlord #77. "If I have a fit of temporary insanity
and decide to give  the hero the chance to reject a  job as my trusted
lieutentant,  I will  retain enough  sanity to  wait until  my current
trusted  lieutenant  is  out  of  earshot before  making  the  offer."


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

[GENERAL] to_date function

[Carlos Henrique Reimer]

Hi

I've a Linux box running postgresql 8.2.17 and facing some strange results
from the to_date function.

As you can see in the following tests the problem occurs when the template
used includes upper and lower case characters for the minute (Mi or mI).

Am I using the incorrect syntax or is it a bug?

Thank you in advance!

template1=# select to_date('01/04/2013 23:59:59','DD/MM/
HH24:Mi:SS')   ;
  to_date

 2009-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mi:SS')
;
  to_date

 2013-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mI:SS')
;
  to_date

 2009-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/
hH24:MI:SS')   ;
  to_date

 2013-04-01
(1 row)

-- 
Reimer

[GENERAL] Anyone in Madison?

[Josh Berkus]

Folks,

I'll be unexpectedly in Madison next week for an onsite contract.  Is
there a user group in Madison I could meet up with?  Maybe do a quick
session on 9.0?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.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] to_date function

[Tim Landscheidt]

Carlos Henrique Reimer  wrote:

> I've a Linux box running postgresql 8.2.17 and facing some strange results
> from the to_date function.

> As you can see in the following tests the problem occurs when the template
> used includes upper and lower case characters for the minute (Mi or mI).

> Am I using the incorrect syntax or is it a bug?
> [...]

In general, the template patterns are case-sensitive (cf.
"month" vs. "Month" vs. "MONTH"). So "mI" will probably be
interpreted as a literal "m" and "I" meaning "last digit of
ISO year" which isn't what you want.

  So use "MI" and be happy.

Tim


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

Re: [GENERAL] to_date function

[Tom Lane]

Carlos Henrique Reimer  writes:
> I've a Linux box running postgresql 8.2.17 and facing some strange results
> from the to_date function.

> As you can see in the following tests the problem occurs when the template
> used includes upper and lower case characters for the minute (Mi or mI).

> Am I using the incorrect syntax or is it a bug?

The defined template patterns are MI and mi.  Random combinations of case
aren't accepted.  I believe what's actually happening is that the code
is seeing this as separate letters m (which matches nothing so it's not
a template pattern but just constant text) and i or I, which are the
ISO-week patterns.  More recent versions of PG throw an error

ERROR:  invalid combination of date conventions
HINT:  Do not mix Gregorian and ISO week date conventions in a formatting 
template.

but 8.2 probably just does something not too sensible with trying to
combine the ISO and Gregorian field values :-(

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] to_date function

[Ludwig Kniprath]

Hi,
on a Windows/PostgrSQL 8.4 three from your queries throw errors (sorry, 
but they are in german):


select to_date('01/04/2013 23:59:59','DD/MM/ HH24:Mi:SS');
=> FEHLER:  ungültige Kombination von Datumskonventionen
HINT:  Die Gregorianische und die ISO-Konvention für Wochendaten können 
nicht einer Formatvorlage gemischt werden.


select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mi:SS');
=> 2013-04-01

select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mI:SS');
=> same error as above

select to_date('01/04/2013 23:59:59','DD/MM/ hH24:MI:SS');
=> FEHLER:  ungültiger Wert »:5« für »MI«
DETAIL:  Der Wert muss eine ganze Zahl sein.

Regards
Ludwig

Am 07.07.2010 23:07, schrieb Carlos Henrique Reimer:

Hi
I've a Linux box running postgresql 8.2.17 and facing some strange 
results from the to_date function.
As you can see in the following tests the problem occurs when the 
template used includes upper and lower case characters for the minute 
(Mi or mI).

Am I using the incorrect syntax or is it a bug?
Thank you in advance!
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ 
HH24:Mi:SS')   ;

  to_date

 2009-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mi:SS')
;
  to_date

 2013-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ HH24:mI:SS')
;
  to_date

 2009-04-01
(1 row)
template1=# select to_date('01/04/2013 23:59:59','DD/MM/ 
hH24:MI:SS')   ;

  to_date

 2013-04-01
(1 row)

--
Reimer




--
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] Anyone in Madison?

[Kevin Grittner]

Josh Berkus  wrote:
 
> I'll be unexpectedly in Madison next week for an onsite contract. 
> Is there a user group in Madison I could meet up with?  Maybe do a
> quick session on 9.0?
 
There's not a PostgreSQL user group in the area, but the Madison PC
Users' Group meets the 2nd Wednesday of each month.
 
http://www.mpcug.com/public/default.htm
 
You might contact them and see about making a presentation.  (The
Contacts page identifies who sets that up.)  I have to admit to not
being a member or being otherwise involved
 
And of course, I'd be happy to buy you a beer or three if you have
any spare time.  ;-)  Give me a call if you like:
 
  (608) 264-6248 - work, weekdays 8:30 am to 5:00 pm
  (608) 334-3312 - cell, any other time
 
-Kevin

-- 
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] [pgsql-advocacy] Anyone in Madison?

[David Fetter]

On Wed, Jul 07, 2010 at 02:31:04PM -0700, Josh Berkus wrote:
> Folks,
> 
> I'll be unexpectedly in Madison next week for an onsite contract.  Is
> there a user group in Madison I could meet up with?  Maybe do a quick
> session on 9.0?

As there are almost as many Madisons as Springfields, it may help to
mention that Josh is going to the one in Wisconsin.

Cheers,
David
-- 
David Fetter  http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter  XMPP: david.fet...@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

-- 
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] Problems with Vista and Windows 7

[Craig Ringer]

On 07/07/10 16:19, el dorado wrote:
> Hello!
> I use Postgres 8.4.1 as service, with Windows 7. It seems to me the same 
> situation will take place with Vista.
> 
> I've notice that in some special cases PG uses folder %APPDATA% for Windows 
> user 'postgres', instead of the PostgreSQL-specific application data folder 
> (f.e., C:\PostgreSQL\8.4\data). It results in some unexpected bugs. There 
> were no such effects in Win XP and 2000.

What files are being created in %APPDATA% for the postgres user? Or
what's it looking for there that's causing issues?

--
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] [pgsql-advocacy] Anyone in Madison?

[Andrew Maclean]

He is not going to Australia there are none here!

Regards
  Andrew

On Thu, Jul 8, 2010 at 10:54 AM, David Fetter  wrote:
> On Wed, Jul 07, 2010 at 02:31:04PM -0700, Josh Berkus wrote:
>> Folks,
>>
>> I'll be unexpectedly in Madison next week for an onsite contract.  Is
>> there a user group in Madison I could meet up with?  Maybe do a quick
>> session on 9.0?
>
> As there are almost as many Madisons as Springfields, it may help to
> mention that Josh is going to the one in Wisconsin.
>
> Cheers,
> David
> --
> David Fetter  http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david.fet...@gmail.com
> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
___
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney  2006  NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___

-- 
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] [pgsql-advocacy] Anyone in Madison?

[Joshua D. Drake]

On Wed, 2010-07-07 at 17:54 -0700, David Fetter wrote:
> On Wed, Jul 07, 2010 at 02:31:04PM -0700, Josh Berkus wrote:
> > Folks,
> > 
> > I'll be unexpectedly in Madison next week for an onsite contract.  Is
> > there a user group in Madison I could meet up with?  Maybe do a quick
> > session on 9.0?
> 
> As there are almost as many Madisons as Springfields, it may help to
> mention that Josh is going to the one in Wisconsin.

There are people in Wisconsin?

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
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] Problems with Vista and Windows 7

[Andrew Maclean]

It is a vexed issue with Vista/Windows 7.

I found this somewhere on a blog on the web (it may help):

--
When I'm working to resolve compatibility issues, there are always
multiple options to mitigate. The solution we prefer to use is to update
the code.

A common application code update is this: "my application used to write
files to program files. It felt like as good a place to put it as any
other. It had my application's name on it already, and because my users
were admins, it worked fine. But now I see that this may not be as great
a place to stick things as I once thought, because with UAC even
Administrators run with standard user-like privileges most of the time.
So, where should I put my files instead?"

The answer, as it turns out, is: it depends.

Let's look at the options, and when you might want to choose each.

First, you'll want to use the SHGetKnownFolderPath API function to pull
the function if you are using native code. If you are using managed
code, System.Environment.GetFolderPath will do the trick for you.

FOLDERID_ProgramData /
System.Environment.SpecialFolder.CommonApplicationData
The user would never want to browse here in Explorer, and settings
changed here should affect every user on the machine. The default
location is %systemdrive%\ProgramData, which is a hidden folder, on an
installation of Windows Vista. You'll want to create your directory and
set the ACLs you need at install time.

FOLDERID_Public / FOLDERID_PublicDocuments /
System.Environment.GetEnvironmentVariable("public")
The user would want to browse here in Explorer and double click to open
the file. The default location is %public%, which has explicit links
throughout Explorer, on an installation of Windows Vista. You'll want to
create your directory and set the ACLs you need at install time.

FOLDERID_RoamingAppData /
System.Environment.SpecialFolder.ApplicationData
The user would never want to browse here in Explorer, and settings
changed here should roam with the user. The default location is %appdata
%, which is a hidden folder, on an installation of Windows Vista.

FOLDERID_LocalAppData /
System.Environment.SpecialFolder.LocalApplicationData
The user would never want to browse here in Explorer, and settings
changed here should stay local to the computer. The default location is
%localappdata%, which is a hidden folder, on an installation of Windows
Vista.

FOLDERID_Documents / System.Environment.SpecialFolder.MyDocuments
The user would want to browse here in Explorer and double click to open
the file. The default location is %userprofile%\documents, which has
explicit links throughout Explorer, on an installation of Windows Vista.

Now, you'll note that FOLDERID_Public is kind of the oddball here.
System.Environment.GetFolderPath just calls SHGetFolderPath, which takes
CSIDLs. There is no analogue for %public% here. However, we could have
gone after CSIDL_COMMON_DOCUMENTS (FOLDERID_PublicDocuments) and dropped
things there, but even though we just need to pass 0x2e (46) as the int
argument, we don't offer that. Because we have this subset going, I'd
probably start thinking about using p/invoke if I needed to support
public documents
(http://www.pinvoke.net/default.aspx/shell32.SHGetKnownFolderPath).




Regards
   Andrew

2010/7/8 Craig Ringer :
> On 07/07/10 16:19, el dorado wrote:
>> Hello!
>> I use Postgres 8.4.1 as service, with Windows 7. It seems to me the same 
>> situation will take place with Vista.
>>
>> I've notice that in some special cases PG uses folder %APPDATA% for Windows 
>> user 'postgres', instead of the PostgreSQL-specific application data folder 
>> (f.e., C:\PostgreSQL\8.4\data). It results in some unexpected bugs. There 
>> were no such effects in Win XP and 2000.
>
> What files are being created in %APPDATA% for the postgres user? Or
> what's it looking for there that's causing issues?
>
> --
> 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
>



-- 
___
Andrew J. P. Maclean
Centre for Autonomous Systems
The Rose Street Building J04
The University of Sydney  2006  NSW
AUSTRALIA
Ph: +61 2 9351 3283
Fax: +61 2 9351 7474
URL: http://www.acfr.usyd.edu.au/
___

-- 
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] Problems with Vista and Windows 7

[Craig Ringer]

On 08/07/10 09:22, Andrew Maclean wrote:
> It is a vexed issue with Vista/Windows 7.
> 
> I found this somewhere on a blog on the web (it may help):

The default datadir location has been discussed to death. There are
fairly decent reasons it's still in Program Files on Vista/Win7 - not
the least of which is "that's where Microsoft SQL Server puts it".

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

[GENERAL] \COPY ... CSV with hex escapes

[Craig Ringer]

Hi folks

I have an odd csv input format to deal with. I'm about to put some
Python together to reprocess it, but I thought I'd check in and see if
I'm missing something obvious in \copy's capabilities.

The input is fairly conventional comma-delimeted text with quoted
fields, as output by Sybase SQL Anywhere 5.5's isql 'OUTPUT TO' filter.
Yes, that's ancient. It is handled quite happily by \copy in csv mode,
except that when csv mode is active, \xnn escapes do not seem to be
processed. So I can have *either* \xnn escape processing *or* csv-style
input processing.

Anyone know of a way to get escape processing in csv mode?

--
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] \COPY ... CSV with hex escapes

[Peter Hunsberger]

On Wed, Jul 7, 2010 at 9:21 PM, Craig Ringer
 wrote:
> Hi folks
>
> I have an odd csv input format to deal with. I'm about to put some
> Python together to reprocess it, but I thought I'd check in and see if
> I'm missing something obvious in \copy's capabilities.
>
> The input is fairly conventional comma-delimeted text with quoted
> fields, as output by Sybase SQL Anywhere 5.5's isql 'OUTPUT TO' filter.
> Yes, that's ancient. It is handled quite happily by \copy in csv mode,
> except that when csv mode is active, \xnn escapes do not seem to be
> processed. So I can have *either* \xnn escape processing *or* csv-style
> input processing.
>
> Anyone know of a way to get escape processing in csv mode?
>
Don't know if you can do it directly, but this seem like one of those
cases where a ETL tool like that from Pentaho (Kettle / Spoon) might
be in order?  One step to handle the escape chars and one to load the
actual CSV...

-- 
Peter Hunsberger

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

[GENERAL] Harshad Pethe wants to stay in touch on LinkedIn

[Harshad Pethe]

LinkedIn
Harshad Pethe requested to add you as a connection on LinkedIn:
--

Andrew,

I'd like to add you to my professional network on LinkedIn.

- Harshad Pethe

Accept invitation from Harshad Pethe
http://www.linkedin.com/e/v74zw8-gbd3gwoo-k/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2178319170_2/1BpC5vrmRLoRZcjkkZt5YCpnlOt3RApnhMpmdzgmhxrSNBszYOnP0TcjANcPwTcj99bSFWcQQMdkJ5bPoVc3AOejsQd34LrCBxbOYWrSlI/EML_comm_afe/

View invitation from Harshad Pethe
http://www.linkedin.com/e/v74zw8-gbd3gwoo-k/ATaEtqAFKHyclb4yaP2gMVdFpLR9OBj5WwZ3C3FunFK/blk/I2178319170_2/39vc3sNej4Pe3sNcAALqnpPbOYWrSlI/svi/
 

--
DID YOU KNOW you can use your LinkedIn profile as your website? Select a vanity 
URL and then promote this address on your business cards, email signatures, 
website, etc
http://www.linkedin.com/e/v74zw8-gbd3gwoo-k/ewp/inv-21/


 
--
(c) 2010, LinkedIn Corporation

Re: [GENERAL] pl-perl for 64 bits in Solaris 9

[Felipe de Jesús Molina Bravo]

El 26 de junio de 2010 16:56, Felipe de Jesús Molina Bravo <
fjmolinabr...@gmail.com> escribió:

>
>
> 2010/6/25 John R Pierce 
>
>> On 06/25/10 9:28 PM, Felipe de Jesús Molina Bravo wrote:
>>
>> original perl is 5.6. so i installed perl 5.8.9 for 64 bit and the
>>> error is the same... maybe my error is with the compiler, it is "gcc"
>>>  (3.4.6).  is it possible?
>>>
>>
>> where did this 64bit 5.8.9 come from?
>>
>
> from cpan soucesit was compiled...
>
>>
>>
>> I try to downloaded SunStudio c/c++ compiler ... but it is not free...
>>>  and the administration don't have it
>>>
>>>
>> it was free last time I looked.   you just have to have a sunsolve account
>> to access it.
>>
>>
> I tried from
> http://developers.sun.com/sunstudio/downloads/
>
>
> it is an upgrade . and it is only for solaris 10. I will find old
> version
>
>
> well after many problems i can install sunstudio 11; but now my problem
is:


- environment:
 bash-2.05# echo $CC
/opt/SunStudio/bin/cc

bash-2.05# echo  $CFLAGS
-xarch=v9 -O

- my configure was:

./configure --with-CC=/opt/SunStudio/bin/cc  --with-perl --without-readline


- when i did make, got the next error:

make[3]: Entering directory
`/export/fmolina/pgsql/postgresql-8.4.4/src/port'
make[3]: Nothing to be done for `all'.
make[3]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src/port'
/opt/SunStudio/bin/cc -Xa -xarch=v9 -O -xcode=pic13 -I../../src/include   -c
-o zic.o zic.c
/opt/SunStudio/bin/cc -Xa -xarch=v9 -O -xcode=pic13 -I../../src/include   -c
-o ialloc.o ialloc.c
/opt/SunStudio/bin/cc -Xa -xarch=v9 -O -xcode=pic13 -I../../src/include   -c
-o scheck.o scheck.c
/opt/SunStudio/bin/cc -Xa -xarch=v9 -O -xcode=pic13 zic.o ialloc.o scheck.o
localtime.o -L../../src/port  -Wl,-rpath,'/usr/local/pgsql/lib' -lpgport -lz
-lnsl -lrt -lsocket -ldl -lm  -o zic
ld: fatal: option -dn and -P are incompatible
ld: fatal: Flags processing errors
make[2]: *** [zic] Error 1
make[2]: Leaving directory
`/export/fmolina/pgsql/postgresql-8.4.4/src/timezone'
make[1]: *** [all] Error 2
make[1]: Leaving directory `/export/fmolina/pgsql/postgresql-8.4.4/src'
make: *** [all] Error 2

How can solve this problem?


thank's in advanced...