[GENERAL] group by and count(*) behaviour in 8.3

[Edoardo Panfili]

I am using this query in 8.3beta4 (compiled from source) in MacOS X 10.5.1

SELECT webName,count(*) FROM contenitore NATURAL JOIN cartellino WHERE
contenitore.tipo='e' GROUP BY webName;

this is the result
  webName  | count
--+---
  test palermo | 36679
  Herbarium Camerinensis - CAME| 36679
  Herbarium Universitatis Aeserniae - IS   | 36679
  Herbarium Universitatis Civitatis Perusii - PERU | 36679
  Herbarium Anconitanum - ANC  | 36679
  Test database - São Paulo| 36679
  Herbarium Universitatis Genuensis - GE   | 36679
  Herbarium Universitatis Senensis - SIENA | 36679
  Segnalazioni Siena   | 36679
  Herbarium Aquilanum - AQUI   | 36679
(10 rows)

but 36679 is the total number of row of the table.
The same query in 8.1.4 retrieves the aspected result (the number of
elements for each webName).

Is this a bug or a change in the semantic of SQL?

thank you
Edoardo

--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766

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

Re: [GENERAL] Need help requiring uniqueness in text columns

[Pavel Stehule]

Hello

IDEA 3:

Use two hash functions:

CREATE UNIQUE INDEX uidx ON TEST((decode(md5(a),'hex')),(hashtext(a)));

removing spaces helps
CREATE UNIQUE INDEX uidx ON test((decode(md5(lower(replace(a,' ',''))),'hex')));

Regards
Pavel Stehule

CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, md5(my_text_col));
>

On 02/01/2008, Matthew Wilson <[EMAIL PROTECTED]> wrote:
> I have a table MESSAGE with a text column and a timestamp column.  I
> want to make sure that I have no rows with duplicates for both values.
> I have two ideas right now for how to do this.
>
> IDEA 1:
>
> CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, my_text_col);
>
> IDEA 2:
>
> CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, md5(my_text_col));
>
> I am speculating that an index on the md5 is cheaper than on a text
> column.  I'm willing to risk the chance of a hash collision.
>
> I don't want to use this index to allow searching inside the text
> column.  I just want to protect against duplication.
>
> Are either of these approaches any good?  What are other ways to
> guarantee uniqueness for the pair of my timestamp column and my text
> column?
>
> TIA
>
> Matt
>
>
> --
> Programming, economics, gardening, life in Cleveland.
> http://blog.tplus1.com
>
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>

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

Re: [GENERAL] group by and count(*) behaviour in 8.3

[Pavel Stehule]

Hello

it works to me:

postgres=# create table c1(n varchar, e integer);
CREATE TABLE
postgres=# create table c2(n2 varchar, e integer);
CREATE TABLE
postgres=# insert into c1 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# insert into c2 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# select * from c1 natural join c2;
 e | n  | n2
---++
 1 | aa | aa
 2 | bb | bb
 3 | aa | aa
(3 rows)

postgres=# select * from c1 natural join c2 where c1.e = 3;
 e | n  | n2
---++
 3 | aa | aa
(1 row)

postgres=# select n, count(*) from c1 natural join c2 where c1.e = 3 group by n;
 n  | count
+---
 aa | 1
(1 row)

postgres=# select n, count(*) from c1 natural join c2  group by n;
 n  | count
+---
 bb | 1
 aa | 2
(2 rows)

postgres=# select n2, count(*) from c1 natural join c2  group by n2;
 n2 | count
+---
 bb | 1
 aa | 2
(2 rows)


can you send structure and execution plan?

Regards
Pavel Stehule

On 02/01/2008, Edoardo Panfili <[EMAIL PROTECTED]> wrote:
> I am using this query in 8.3beta4 (compiled from source) in MacOS X 10.5.1
>
> SELECT webName,count(*) FROM contenitore NATURAL JOIN cartellino WHERE
> contenitore.tipo='e' GROUP BY webName;
>
> this is the result
>webName  | count
> --+---
>test palermo | 36679
>Herbarium Camerinensis - CAME| 36679
>Herbarium Universitatis Aeserniae - IS   | 36679
>Herbarium Universitatis Civitatis Perusii - PERU | 36679
>Herbarium Anconitanum - ANC  | 36679
>Test database - São Paulo| 36679
>Herbarium Universitatis Genuensis - GE   | 36679
>Herbarium Universitatis Senensis - SIENA | 36679
>Segnalazioni Siena   | 36679
>Herbarium Aquilanum - AQUI   | 36679
> (10 rows)
>
> but 36679 is the total number of row of the table.
> The same query in 8.1.4 retrieves the aspected result (the number of
> elements for each webName).
>
> Is this a bug or a change in the semantic of SQL?
>
> thank you
> Edoardo
>
> --
> Jabber: [EMAIL PROTECTED]
> tel: 075 9142766
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>

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

Re: [GENERAL] group by and count(*) behaviour in 8.3

[Edoardo Panfili]

Pavel Stehule ha scritto:

Hello

it works to me:

postgres=# create table c1(n varchar, e integer);
CREATE TABLE
postgres=# create table c2(n2 varchar, e integer);
CREATE TABLE
postgres=# insert into c1 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# insert into c2 values('aa',1),('bb',2),('aa',3);
INSERT 0 3
postgres=# select * from c1 natural join c2;
 e | n  | n2
---++
 1 | aa | aa
 2 | bb | bb
 3 | aa | aa
(3 rows)

postgres=# select * from c1 natural join c2 where c1.e = 3;
 e | n  | n2
---++
 3 | aa | aa
(1 row)

postgres=# select n, count(*) from c1 natural join c2 where c1.e = 3 group by n;
 n  | count
+---
 aa | 1
(1 row)

postgres=# select n, count(*) from c1 natural join c2  group by n;
 n  | count
+---
 bb | 1
 aa | 2
(2 rows)

postgres=# select n2, count(*) from c1 natural join c2  group by n2;
 n2 | count
+---
 bb | 1
 aa | 2
(2 rows)


can you send structure and execution plan?
Thank you for your request, the execution  plan is the one from 
"explain" (I think) but what is the "structure plan"?

The problema was a bug on my import in new database!

To avoid future error of this type, how can I ask to postgres wath 
column is it using in "natural join"?


tanks again
and sorry for my error
Edoardo


Regards
Pavel Stehule

On 02/01/2008, Edoardo Panfili <[EMAIL PROTECTED]> wrote:

I am using this query in 8.3beta4 (compiled from source) in MacOS X 10.5.1

SELECT webName,count(*) FROM contenitore NATURAL JOIN cartellino WHERE
contenitore.tipo='e' GROUP BY webName;

this is the result
   webName  | count
--+---
   test palermo | 36679
   Herbarium Camerinensis - CAME| 36679
   Herbarium Universitatis Aeserniae - IS   | 36679
   Herbarium Universitatis Civitatis Perusii - PERU | 36679
   Herbarium Anconitanum - ANC  | 36679
   Test database - São Paulo| 36679
   Herbarium Universitatis Genuensis - GE   | 36679
   Herbarium Universitatis Senensis - SIENA | 36679
   Segnalazioni Siena   | 36679
   Herbarium Aquilanum - AQUI   | 36679
(10 rows)

but 36679 is the total number of row of the table.
The same query in 8.1.4 retrieves the aspected result (the number of
elements for each webName).

Is this a bug or a change in the semantic of SQL?

thank you
Edoardo

--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766

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




--
Jabber: [EMAIL PROTECTED]
tel: 075 9142766

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

Re: [GENERAL] group by and count(*) behaviour in 8.3

[Pavel Stehule]

 >
> > can you send structure and execution plan?
> Thank you for your request, the execution  plan is the one from
> "explain" (I think) but what is the "structure plan"?

no, only structure :) table and fields.

> The problema was a bug on my import in new database!
>
> To avoid future error of this type, how can I ask to postgres wath
> column is it using in "natural join"?
>

Don't use natural join. It has some others disadvantagedness. Use
classic JOIN. It is safe.

SELECT FROM tab1 JOIN tab2 ON 

Regards
Pavel Stehule

> tanks again
> and sorry for my error
> Edoardo
> >
> > Regards
> > Pavel Stehule
> >
> > On 02/01/2008, Edoardo Panfili <[EMAIL PROTECTED]> wrote:
> >> I am using this query in 8.3beta4 (compiled from source) in MacOS X 10.5.1
> >>
> >> SELECT webName,count(*) FROM contenitore NATURAL JOIN cartellino WHERE
> >> contenitore.tipo='e' GROUP BY webName;
> >>
> >> this is the result
> >>webName  | count
> >> --+---
> >>test palermo | 36679
> >>Herbarium Camerinensis - CAME| 36679
> >>Herbarium Universitatis Aeserniae - IS   | 36679
> >>Herbarium Universitatis Civitatis Perusii - PERU | 36679
> >>Herbarium Anconitanum - ANC  | 36679
> >>Test database - São Paulo| 36679
> >>Herbarium Universitatis Genuensis - GE   | 36679
> >>Herbarium Universitatis Senensis - SIENA | 36679
> >>Segnalazioni Siena   | 36679
> >>Herbarium Aquilanum - AQUI   | 36679
> >> (10 rows)
> >>
> >> but 36679 is the total number of row of the table.
> >> The same query in 8.1.4 retrieves the aspected result (the number of
> >> elements for each webName).
> >>
> >> Is this a bug or a change in the semantic of SQL?
> >>
> >> thank you
> >> Edoardo
> >>
> >> --
> >> Jabber: [EMAIL PROTECTED]
> >> tel: 075 9142766
> >>
> >> ---(end of broadcast)---
> >> TIP 5: don't forget to increase your free space map settings
> >>
>
>
> --
> Jabber: [EMAIL PROTECTED]
> tel: 075 9142766
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>

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

Re: [GENERAL] visibility rules for AFTER UPDATE Constraint Triggers Function

[Richard Broersma Jr]

--- On Tue, 1/1/08, Tom Lane <[EMAIL PROTECTED]> wrote:
> 
> Please provide a self-contained example.

Basically the example demonstrates the difference a single tuple UPDATE when 
preformed the following two ways:

UPDATE table ...;  -- Sees OLD.

BEGIN; UPDATE table ...; COMMIT; --Sees NEW.

I was my understanding that all single DML statement are wrapped in their own 
transaction so I thought that these two statements should preform the same.

Regards,
Richard Broersma Jr.--
-- PostgreSQL database dump
--

-- Started on 2008-01-02 05:35:55

SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;

--
-- TOC entry 7 (class 2615 OID 605027)
-- Name: instrumentation; Type: SCHEMA; Schema: -; Owner: teaminst
--

CREATE SCHEMA instrumentation;


ALTER SCHEMA instrumentation OWNER TO teaminst;

--
-- TOC entry 1784 (class 0 OID 0)
-- Dependencies: 6
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


--
-- TOC entry 325 (class 2612 OID 16386)
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: postgres
--

CREATE PROCEDURAL LANGUAGE plpgsql;


ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO postgres;

SET search_path = instrumentation, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 1502 (class 1259 OID 605179)
-- Dependencies: 1769 1770 1771 1772 1773 1774 1775 1776 7
-- Name: looptimelines; Type: TABLE; Schema: instrumentation; Owner: teaminst; 
Tablespace: 
--

CREATE TABLE looptimelines (
startdate date DEFAULT ('now'::text)::date NOT NULL,
enddate date DEFAULT '-12-31'::date NOT NULL,
startproject_code character varying(15) NOT NULL,
endproject_code character varying(15),
area integer NOT NULL,
process character(1) NOT NULL,
loop_nbr integer NOT NULL,
suffix character(1) DEFAULT ''::bpchar NOT NULL,
service_desc text DEFAULT '--HOLD--'::text NOT NULL,
CONSTRAINT looptimelines_area_check CHECK (((area >= 0) AND (area <= 99))),
CONSTRAINT looptimelines_loop_nbr_check CHECK (((loop_nbr >= 1) AND 
(loop_nbr <= ))),
CONSTRAINT looptimelines_process_check CHECK (((process >= 'A'::bpchar) AND 
(process <= 'Z'::bpchar))),
CONSTRAINT looptimelines_suffix_check CHECK suffix >= 'A'::bpchar) AND 
(suffix <= 'Z'::bpchar)) OR (suffix = ''::bpchar)))
);


ALTER TABLE instrumentation.looptimelines OWNER TO teaminst;

SET search_path = public, pg_catalog;

--
-- TOC entry 276 (class 1247 OID 16405)
-- Dependencies: 6 1497
-- Name: breakpoint; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE breakpoint AS (
func oid,
linenumber integer,
targetname text
);


ALTER TYPE public.breakpoint OWNER TO postgres;

--
-- TOC entry 278 (class 1247 OID 16408)
-- Dependencies: 6 1498
-- Name: frame; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE frame AS (
level integer,
targetname text,
func oid,
linenumber integer,
args text
);


ALTER TYPE public.frame OWNER TO postgres;

--
-- TOC entry 320 (class 1247 OID 16417)
-- Dependencies: 6 1501
-- Name: proxyinfo; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE proxyinfo AS (
serverversionstr text,
serverversionnum integer,
proxyapiver integer,
serverprocessid integer
);


ALTER TYPE public.proxyinfo OWNER TO postgres;

--
-- TOC entry 316 (class 1247 OID 16411)
-- Dependencies: 6 1499
-- Name: targetinfo; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE targetinfo AS (
target oid,
schema oid,
nargs integer,
argtypes oidvector,
targetname name,
argmodes "char"[],
argnames text[],
targetlang oid,
fqname text,
returnsset boolean,
returntype oid
);


ALTER TYPE public.targetinfo OWNER TO postgres;

--
-- TOC entry 318 (class 1247 OID 16414)
-- Dependencies: 6 1500
-- Name: var; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE var AS (
name text,
varclass character(1),
linenumber integer,
isunique boolean,
isconst boolean,
isnotnull boolean,
dtype oid,
value text
);


ALTER TYPE public.var OWNER TO postgres;

SET search_path = instrumentation, pg_catalog;

--
-- TOC entry 41 (class 1255 OID 605215)
-- Dependencies: 7 325 322 322
-- Name: sample_for_new_or_old(looptimelines, looptimelines); Type: FUNCTION; 
Schema: instrumentation; Owner: postgres
--

CREATE FUNCTION sample_for_new_or_old(mynew looptimelines, myold looptimelines) 
RETURNS character varying
AS $$
BEGIN

 IF 'NEW'::VARCHAR = ( SELECT 'NEW'::VARCHAR
FROM Instrumentation.looptimelines AS L
   WHERE ROW( L.* ) = ROW( myNEW.* ) ) THEN
RETURN 'NEW'::VARCHAR;
 ELSIF 'OLD'::VARCHAR = 

[GENERAL] Fwd: [ADMIN] Shutting down warm standby server? "

[Glyn Astill]

 Hi people,
 
 I've setup a warm standby reading WAL files, however when I try to
 shut it down I get the message "server does not shut down".
 
 Can someone help please, I presume it has something to do with the
 server being busy waiting for the next WAL file? What is the
 correct
 way to shut down a server reading WALs?
 
 The command line output is below:
 
 [EMAIL PROTECTED]:/mnt/archive$ /usr/local/pgsql/bin/pg_ctl stop -D
 /data/postgres/ -m smart
 waiting for server to shut
 down...
 failed
 pg_ctl: server does not shut down
 
 
 If I do a ps -ax:
 
  7956 pts/0S  0:00 /usr/local/pgsql/bin/postgres -D
 /data/postgres/
  7957 ?Ss 0:10 postgres: startup process
  7371 ?S  0:00 /bin/bash /data/postgres/restore.sh
 /mnt/archive/0001001A00A8 pg_xlog/RECOVERYXLOG
  1160 ?S  0:00 sleep 1
  1161 pts/0R+ 0:00 ps -ax
 
 it looks like it's waiting for the next WAL file.
 
 



  __
Sent from Yahoo! Mail - a smarter inbox http://uk.mail.yahoo.com



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

[GENERAL] Creating XML/KML documents from single tables

[William Temperley]

Hi
I would be most grateful if someone could help me create an xml doc in the form:


  
-3.04,53.56,0
-2.04,55.56,0
  
  
-3.44,57.56,0
  


This will be created from a single table of point geometries where
each belongs to a layer, e.g. x1 or x2 etc. The layer a geometry
belongs to is stored in a varchar column called layername.

Creating the marker tags is easily done using the query:
# select xmlelement(name marker, xmlattributes(gid as id, typeid as
class), askml(the_geom)) from poi

Or creating a list of layers is also simple:
#select xmlelement(name layer, xmlattributes(layername as name)) from
poi group by layername

However I cannot work out how to insert more than one child node into
a layer element.

Thanks

Will T

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

   http://archives.postgresql.org/

Re: [GENERAL] Need help requiring uniqueness in text columns

[Matthew Wilson]

On Wed 02 Jan 2008 04:23:46 AM EST, Pavel Stehule wrote:
> Hello
>
> IDEA 3:
>
> Use two hash functions:
>
> CREATE UNIQUE INDEX uidx ON TEST((decode(md5(a),'hex')),(hashtext(a)));
>
> removing spaces helps
> CREATE UNIQUE INDEX uidx ON test((decode(md5(lower(replace(a,' 
> ',''))),'hex')));
>
> Regards
> Pavel Stehule
>
> CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, md5(my_text_col));

What is the advantage of this method?

-- 
Programming, economics, gardening, life in Cleveland.
http://blog.tplus1.com


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

   http://archives.postgresql.org/

Re: [GENERAL] Need help requiring uniqueness in text columns

[Pavel Stehule]

On 02/01/2008, Matthew Wilson <[EMAIL PROTECTED]> wrote:
> On Wed 02 Jan 2008 04:23:46 AM EST, Pavel Stehule wrote:
> > Hello
> >
> > IDEA 3:
> >
> > Use two hash functions:
> >
> > CREATE UNIQUE INDEX uidx ON TEST((decode(md5(a),'hex')),(hashtext(a)));
> >
> > removing spaces helps
> > CREATE UNIQUE INDEX uidx ON test((decode(md5(lower(replace(a,' 
> > ',''))),'hex')));
> >
> > Regards
> > Pavel Stehule
> >
> > CREATE UNIQUE INDEX nodups on MESSAGE (my_timestamp_col, md5(my_text_col));
>
> What is the advantage of this method?
>

much less unique index.

Regards
Pavel

> --
> Programming, economics, gardening, life in Cleveland.
> http://blog.tplus1.com
>
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
>

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

Re: [GENERAL] Creating XML/KML documents from single tables

[Pavel Stehule]

Hello

you have to use xmlagg function

Regards
Pavel Stehule

On 02/01/2008, William Temperley <[EMAIL PROTECTED]> wrote:
> Hi
> I would be most grateful if someone could help me create an xml doc in the 
> form:
>
> 
>   
>  class="1">-3.04,53.56,0
>  class="4">-2.04,55.56,0
>   
>   
>  class="3">-3.44,57.56,0
>   
> 
>
> This will be created from a single table of point geometries where
> each belongs to a layer, e.g. x1 or x2 etc. The layer a geometry
> belongs to is stored in a varchar column called layername.
>
> Creating the marker tags is easily done using the query:
> # select xmlelement(name marker, xmlattributes(gid as id, typeid as
> class), askml(the_geom)) from poi
>
> Or creating a list of layers is also simple:
> #select xmlelement(name layer, xmlattributes(layername as name)) from
> poi group by layername
>
> However I cannot work out how to insert more than one child node into
> a layer element.
>
> Thanks
>
> Will T
>
> ---(end of broadcast)---
> TIP 4: Have you searched our list archives?
>
>http://archives.postgresql.org/
>

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

Re: [GENERAL] basic questions: Postgres with yum on CentOS 5.1

[Andrew Sullivan]

Along with the other good remarks people have made, I want to point
something out.

On Tue, Jan 01, 2008 at 06:49:40PM -0800, Chuck wrote:
> 
> I created a test database and confirmed that it's created with 
> 'SQL_ASCII' encoding.
> [EMAIL PROTECTED] ~]# sudo -u postgres createdb myTest
> could not change directory to "/root"
> CREATE DATABASE

There are two issues above worth noting.  First, by doing this as user
postgres, you're creating a database _owned by postgres_.  There's nothing
fundamentally wrong with that, but I think it generally a good practice to
reduce as much as possible the reliance on the superuser.  You can create
other accounts.  See the sections of the manual on users.  

Second, that "could not change directory to '/root'" tells me that your
postgres user really is, as already suggested upthread, not intended by your
package maintainer to be used with any regularity.  It has no home
directory. 

> Without a package manager, I believe that this would be my initdb command:
> sudo -u postgres initdb -D /var/lib/pgsql/data -E UTF8 --no-locale
> 
> I found the '--no-locale' option from 'initdb --help' and from this link:
> http://docs.planetargon.com/PostgreSQL_Installation
> I couldn't find the '--no-locale' option in the documentation.

Yes, that should work.  But see the other posts for suggestions on better
ways to deal with this.  

A


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

[GENERAL] Table auditing / Pg/tcl help

[Glyn Astill]

Hi people,

I've been trying to set up table auditing using a tcl function and a
trigger. I followed the guide here to start with:

http://www.alberton.info/postgresql_table_audit.html

The thing is we have multiple fields in our primary keys so I need to
change it to handle them all. I was thinking for now to just set
pk_name like "fieldName1,field2Name" and pk_value to
"fieldvalue1,fieldvalue2" etc.

The script runs the query:

"SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a,
pg_index i WHERE c.relname = '$tgname' AND c.oid=i.indrelid AND
a.attnum > 0 AND a.attrelid = i.indexrelid AND i.indisprimary='t'"

which should produce a list of the values. It then does:

  #get PK value
  foreach field $TG_relatts {
if {[string equal -nocase [lindex [array get NEW $field] 0]
$pk_name]} {
  set pk_value [lindex [array get NEW $field] 1]
  break;
}
  }

Which I presume just gets the first value, then breaks. I want to get
them all and put them into a comma separated string. So (baering in
mind I've not touched tcl before) I changed this to:

  #get PK value
  foreach field $TG_relatts {
if {[string equal -nocase [lindex [array get NEW $field] 0]
$pk_name]} {
  if {[string length $pk_value] > 0} {
append pk_value "," [lindex [array get NEW $field] 1]
append pk_list "," $pk_name
  } else {
set pk_value [lindex [array get NEW $field] 1]
set pk_list $pk_name
  }
  #break;
}
  }

But it didn't work, so I guess I'm missing something.








  ___
Support the World Aids Awareness campaign this month with Yahoo! For Good 
http://uk.promotions.yahoo.com/forgood/


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

Re: [GENERAL] Any big slony and WAL shipping users?

[Andrew Sullivan]

On Fri, Dec 28, 2007 at 12:06:42PM -0500, Josh Harrison wrote:
> I also thought abt having 2 setups for backup and replication so that even
> when slony fails I will always have the standby server (WAL shipping) to
> help me out.

Ok, but do realise that what this form of redundancy provides you with is
two independent paths for different kinds of failure.  It may also mean you
have more complicated troubleshooting procedures.  I'm not advising not to
do it; I'm rather advising you to think carefully about what problems you
think you're solving, and what potential problems you are adding by taking
this approach.

>  I have another question regarding this. I also want to write these to the
> tape. Right now we have a cron job doing level 0,1,2,...  backups of the
> other servers to the tape regularly. What is the good way to include
> postgres server backup to tape?

If you're going to use WAL shipping anyway, then I'd do fairly regular full
backups plus WAL archiving.  This is outlined completely in the manual in
section 23.3.

A

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

Re: [GENERAL] [HACKERS] Slow count(*)

[Pavel Stehule]

On 02/01/2008, Abraham, Danny <[EMAIL PROTECTED]> wrote:
> When comparing the OLTP part of our product, PG is about 15% slower
> compared to Oracle, which is reasonable.
>
> When comparing the DSS part, it is about 20 times slower.
>
> Unfortunately, we need both.
>

Send slow queries, please, and than we can help you.

don't forget on VACUUM and ANALYZE statements before and then send
execution plans

http://www.postgresql.org/docs/8.2/static/sql-explain.html


>
> -Original Message-
> From: Pavel Stehule [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, January 02, 2008 5:47 PM
> To: Abraham, Danny
> Cc: [EMAIL PROTECTED]
> Subject: Re: [HACKERS] Slow count(*)
>
> On 02/01/2008, Abraham, Danny <[EMAIL PROTECTED]> wrote:
> > Hi,
> >
> > We are looking for a patch that will help us  count using the indexes.
> >
> > Our product is about 20 times slower on Postgres compared to MS SQL
> > Server.
> >
> > Any ideas?
>
> There isn't any similar patch and will not be.
>
> Use materialized views or similar techniques.
>
> Are you sure, so all your problems are only in SELECT COUNT(*)?
>
> Check, please, all slow queries.
>
> Regards
> Pavel Stehule
> >
> > Danny Abraham
> > BMC Software
> > CTM&D Business Unit
> > 972-52-4286-513
> > [EMAIL PROTECTED]
> >
> >
> > ---(end of
> broadcast)---
> > TIP 5: don't forget to increase your free space map settings
> >
>
>

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

[GENERAL] tablefunc and crosstab

[Vincent Bernat]

Hi !

I am trying to use crosstab function from tablefunc.

SELECT * from crosstab('select date, source, name, value FROM cth ORDER by
1', 'select distinct name from cth order by 1') AS ct(date timestamp,
source text, val1 text, val2 text, val3 text, val4 text, val5 text);

The content of the table that I get is fine. However, I would like to get
appropriate names instead of arbitrary 'val1', 'val2', ...

Is it possible to rename the table columns with the result of 'select
distinct name from cth order by 1' ?

Thanks.


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

Re: [GENERAL] visibility rules for AFTER UPDATE Constraint Triggers Function

[Tom Lane]

Richard Broersma Jr <[EMAIL PROTECTED]> writes:
> --- On Tue, 1/1/08, Tom Lane <[EMAIL PROTECTED]> wrote:
>> Please provide a self-contained example.

> Basically the example demonstrates the difference a single tuple UPDATE when 
> preformed the following two ways:

> UPDATE table ...;  -- Sees OLD.

> BEGIN; UPDATE table ...; COMMIT; --Sees NEW.

> I was my understanding that all single DML statement are wrapped in their own 
> transaction so I thought that these two statements should preform the same.

[ pokes at it... ]  The reason is that you defined both the trigger and
the testing function as STABLE, which means that they see a snapshot of
the database as of the start of the calling SQL command.  In the first
case that's the UPDATE, in the second it's the COMMIT.

If you remove the STABLE label from the trigger function then both
variants act the same, because the trigger can see the results of
the command that called it:

d2=# UPDATE Looptimelines SET enddate = enddate + INTERVAL '5 DAYS' WHERE 
endproject_code = '02U20420';
NOTICE:  After performing the UPDATE operation, the NEW record is
visible before the commit.
UPDATE 1
d2=# BEGIN; UPDATE Looptimelines SET enddate = enddate + INTERVAL '5 DAYS' 
WHERE endproject_code = '02U20420'; COMMIT;
BEGIN
UPDATE 1
NOTICE:  After performing the UPDATE operation, the NEW record is
visible before the commit.
COMMIT
d2=# 

By and large I'd not recommend marking trigger functions as STABLE
(or IMMUTABLE).  You usually want 'em to see current data.

Because the sample_for_new_or_old() function is STABLE, it sees what
its calling statement sees (in this case, the PERFORM in the trigger).
That probably is OK --- it seems likely that you want both probes in
that function to use the same snapshot, which they will if it's
STABLE.

regards, tom lane

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

Re: [GENERAL] Hinting the planner

[Chris Browne]

[EMAIL PROTECTED] (Martin Gainty) writes:
> Not the planner but you can hint the query as in this example
> select /*+ ordered use_hash(code1)*/ * from table_1;

That might have some effect with Oracle; is there some reason why you
think this would be expected to have any effect on a PostgreSQL query?
-- 
select 'cbbrowne' || '@' || 'acm.org';
http://www3.sympatico.ca/cbbrowne/x.html
Rules of the Evil Overlord #178. "If  I have the  hero cornered and am
about to finish him off and he says "Look out behind you!!" I will not
laugh and  say "You  don't expect me  to fall  for that old  trick, do
you?" Instead I will take a step to the side and half turn. That way I
can still  keep my  weapon trained on  the hero,  I can scan  the area
behind me, and  if anything was heading for me it  will now be heading
for him." 

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

Re: [GENERAL] Fwd: [ADMIN] Shutting down warm standby server? "

[Erik Jones]

On Jan 2, 2008, at 7:47 AM, Glyn Astill wrote:


 Hi people,

 I've setup a warm standby reading WAL files, however when I try to
 shut it down I get the message "server does not shut down".

 Can someone help please, I presume it has something to do with the
 server being busy waiting for the next WAL file? What is the
 correct
 way to shut down a server reading WALs?

 The command line output is below:

 [EMAIL PROTECTED]:/mnt/archive$ /usr/local/pgsql/bin/pg_ctl stop -D
 /data/postgres/ -m smart
 waiting for server to shut
 down...
 failed
 pg_ctl: server does not shut down


 If I do a ps -ax:

  7956 pts/0S  0:00 /usr/local/pgsql/bin/postgres -D
 /data/postgres/
  7957 ?Ss 0:10 postgres: startup process
  7371 ?S  0:00 /bin/bash /data/postgres/restore.sh
 /mnt/archive/0001001A00A8 pg_xlog/RECOVERYXLOG
  1160 ?S  0:00 sleep 1
  1161 pts/0R+ 0:00 ps -ax

 it looks like it's waiting for the next WAL file.


I've seen the same behavior with the '-m smart' option while in  
standby recovery.  The '-m fast' option seems to work fine, though.


Erik Jones

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

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



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

Re: [GENERAL] [SQL] PG is in different timezone than the OS

[Scott Marlowe]

Alright, reading the file in

postgresql-8.2.5/src/timezone/README

I take it that anyone with a source compiled pg or using source rpms
should be able to download the tzdata from here:

ftp://elsie.nci.nih.gov/pub/tzcode2007k.tar.gz

and put it here:

postgresql-8.2.5/src/timezone/data

and recompile and have the new timezone they need?

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

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

Re: [GENERAL] visibility rules for AFTER UPDATE Constraint Triggers Function

[Richard Broersma Jr]

--- On Wed, 1/2/08, Tom Lane <[EMAIL PROTECTED]> wrote:

> [ pokes at it... ]  The reason is that you defined both the trigger and
> the testing function as STABLE, which means that they see a snapshot of
> the database as of the start of the calling SQL command.  In the first
> case that's the UPDATE, in the second it's the COMMIT.

Thanks for the help Tom.  This information is good to know.

Regards,
Richard Broersma Jr.

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

[GENERAL] PITR - filter by database?

[Martin Langhoff]

Is it possible to segregate the PITR data by database at any stage? We are

 - taking regular (daily) snapshots straight from the disk

 - storing WALs

 - restoring the snapshot

 - replaying the WALs

My guess is that at snapshot time, I could use oid2name to focus on the
database I'm interested in plus core Pg data structures, but then the
WAL replay later will fail for transactions that affect other DBs. Is
there a better way?

Background: The goal here is to have a "web application rewind"
facility, based on PITR, that allows a webapp administrator to say
"please rewind my Moodle/Drupal/Whatever" to arbitrary point in time X,
where X is in the last Y days, via a web UI. It is normally configured
to rewind not the master, but a secondary install of your web app --
although there's some interest in rewinding the master too for training
and software testing purposes.

Making good progress so far. It is using git as the storage mechanism
for Pg data and uploaded files, which results in very tight disk footprint.

But it does require a single Pg instance for each web app 'master', plus
a temporary Pg instance for the rewound install. On shared HW there's a
big downside in memory footprint and disk IO to running many Pg instances.

cheers,


martin
-- 
---
Martin @ Catalyst .Net .NZ  Ltd, PO Box 11-053, Manners St,  Wellington
WEB: http://catalyst.net.nz/   PHYS: Level 2, 150-154 Willis St
NZ: +64(4)916-7224MOB: +64(21)364-017UK: 0845 868 5733 ext 7224
  Make things as simple as possible, but no simpler - Einstein
---

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

Re: [GENERAL] PITR - filter by database?

[Usama Dar]

On Jan 3, 2008 2:27 AM, Martin Langhoff <[EMAIL PROTECTED]> wrote:

> Is it possible to segregate the PITR data by database at any stage? We are


i don't think so.

My guess is that at snapshot time, I could use oid2name to focus on the
> database I'm interested in plus core Pg data structures,


can you eleborate on this , how do you plan to use oid2name exactly?

Background: The goal here is to have a "web application rewind"
> facility, based on PITR, that allows a webapp administrator to say
> "please rewind my Moodle/Drupal/Whatever" to arbitrary point in time X,
> where X is in the last Y days, via a web UI. It is normally configured
> to rewind not the master, but a secondary install of your web app --
> although there's some interest in rewinding the master too for training
> and software testing purposes.


not sure if the postgres PITR is designed  for this kind of facility like
that, i would think you will have to incorporate such facility into your
application/ database design

-- 
Usama Munir Dar http://www.linkedin.com/in/usamadar
Consultant Architect
Cell:+92 321 5020666
Skype: usamadar

Re: [GENERAL] replication in Postgres

[Ow Mun Heng]

On Mon, 2007-11-26 at 12:39 -0500, Chris Browne wrote:
> [EMAIL PROTECTED] ("Jeff Larsen") writes:
> Unfortunately, the only way to make things deterministic (or to get
> from "near real time" to "*GUARANTEED* real time") is to jump to
> synchronous replication, which is not much different from 2PC (Two
> Phase Commit), and which is certain to be prohibitively expensive
> across a WAN.
> 

2PC is costly and will make things slow overall if there ever was issues
with the WAN. And to alleviate that, I believe one would have to get a
dedicated WAN line just for the syncing process. Expensive.. Anyone can
Spell S-L-A??

> At this point, I tend to get visions of Tom Cruise telling Jack
> Nicholson, "I want real time replication!", and getting the response:
> "You can't HANDLE real time replication!"

Woo.. I like this movie. _best_ court scene _ever_!


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

Re: [GENERAL] Read-only availability of a standby server?

[Ow Mun Heng]

On Wed, 2007-11-21 at 15:33 -0500, Andrew Sullivan wrote:
> On Wed, Nov 21, 2007 at 12:20:51PM -0800, Garber, Mikhail wrote:
> 
> > In the high-availabilty situation with a warm standby, is it possible (or
> > planned) to be able to make standby readable?
> 
> Yes, but it won't happen for 8.3.  It's a feature you can have today with
> Slony, by the way.

Slony provides the _means_ for it to be a standby-read-only server, but
it _does_ not provide a way for load balancing the queries, (which is
what I  really want anyway) The only way to do that is through another
app like LVS / pgpool or even coding it directly into the frontend app.



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

   http://archives.postgresql.org/