PostgreSQL: May 12, 2008

Monday, May 12, 2008

[COMMITTERS] pgsql: Check for non-existant connection in prepare statement handling.

Log Message:
-----------
Check for non-existant connection in prepare statement handling.
Do not close files that weren't opened.

Modified Files:
--------------
pgsql/src/interfaces/ecpg:
ChangeLog (r1.380 -> r1.381)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ChangeLog?r1=1.380&r2=1.381)
pgsql/src/interfaces/ecpg/ecpglib:
prepare.c (r1.26 -> r1.27)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/ecpglib/prepare.c?r1=1.26&r2=1.27)
pgsql/src/interfaces/ecpg/preproc:
preproc.y (r1.363 -> r1.364)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/interfaces/ecpg/preproc/preproc.y?r1=1.363&r2=1.364)

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

[COMMITTERS] pgsql: Document that "ROLLBACK TO savepoint" does not un-close cursors.

Log Message:
-----------
Document that "ROLLBACK TO savepoint" does not un-close cursors.

Modified Files:
--------------
pgsql/doc/src/sgml/ref:
close.sgml (r1.26 -> r1.27)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/doc/src/sgml/ref/close.sgml?r1=1.26&r2=1.27)

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

[PERFORM] Partitioning: INSERT 0 0 but want INSERT 0 1

--
-- PostgreSQL database dump
--

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

--
-- Name: partitions; Type: SCHEMA; Schema: -; Owner: cdr
--

CREATE SCHEMA partitions;

ALTER SCHEMA partitions OWNER TO cdr;

--
-- Name: plpgsql; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: cdr
--

CREATE PROCEDURAL LANGUAGE plpgsql;

ALTER PROCEDURAL LANGUAGE plpgsql OWNER TO cdr;

SET search_path = public, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- Name: ttt; Type: TABLE; Schema: public; Owner: cdr; Tablespace:
--

CREATE TABLE ttt (
id integer NOT NULL,
a timestamp without time zone,
b character varying
);

ALTER TABLE public.ttt OWNER TO cdr;

--
-- Name: ttt_id_seq; Type: SEQUENCE; Schema: public; Owner: cdr
--

CREATE SEQUENCE ttt_id_seq
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

ALTER TABLE public.ttt_id_seq OWNER TO cdr;

--
-- Name: ttt_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: cdr
--

ALTER SEQUENCE ttt_id_seq OWNED BY ttt.id;

--
-- Name: ttt_id_seq; Type: SEQUENCE SET; Schema: public; Owner: cdr
--

SELECT pg_catalog.setval('ttt_id_seq', 17, true);

--
-- Name: id; Type: DEFAULT; Schema: public; Owner: cdr
--

ALTER TABLE ttt ALTER COLUMN id SET DEFAULT nextval('ttt_id_seq'::regclass);

SET search_path = partitions, pg_catalog;

--
-- Name: ttt_050508; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050508 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050508_a_check CHECK (((a >= '2008-05-05'::date) AND (a < '2008-05-06'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_050508 OWNER TO cdr;

--
-- Name: ttt_050608; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050608 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050608_a_check CHECK (((a >= '2008-05-06'::date) AND (a < '2008-05-07'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_050608 OWNER TO cdr;

--
-- Name: ttt_050708; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050708 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050708_a_check CHECK (((a >= '2008-05-07'::date) AND (a < '2008-05-08'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_050708 OWNER TO cdr;

--
-- Name: ttt_050808; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050808 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050808_a_check CHECK (((a >= '2008-05-08'::date) AND (a < '2008-05-09'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_050808 OWNER TO cdr;

--
-- Name: ttt_050908; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_050908 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_050908_a_check CHECK (((a >= '2008-05-09'::date) AND (a < '2008-05-10'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_050908 OWNER TO cdr;

--
-- Name: ttt_051008; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051008 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051008_a_check CHECK (((a >= '2008-05-10'::date) AND (a < '2008-05-11'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_051008 OWNER TO cdr;

--
-- Name: ttt_051108; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051108 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051108_a_check CHECK (((a >= '2008-05-11'::date) AND (a < '2008-05-12'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_051108 OWNER TO cdr;

--
-- Name: ttt_051208; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051208 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051208_a_check CHECK (((a >= '2008-05-12'::date) AND (a < '2008-05-13'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_051208 OWNER TO cdr;

--
-- Name: ttt_051308; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051308 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051308_a_check CHECK (((a >= '2008-05-13'::date) AND (a < '2008-05-14'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_051308 OWNER TO cdr;

--
-- Name: ttt_051408; Type: TABLE; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE TABLE ttt_051408 (
id integer DEFAULT nextval('public.ttt_id_seq'::regclass),
CONSTRAINT ttt_051408_a_check CHECK (((a >= '2008-05-14'::date) AND (a < '2008-05-15'::date)))
)
INHERITS (public.ttt);

ALTER TABLE partitions.ttt_051408 OWNER TO cdr;

SET search_path = public, pg_catalog;

--
-- Name: ttt_insert_trigger(); Type: FUNCTION; Schema: public; Owner: cdr
--

CREATE FUNCTION ttt_insert_trigger() RETURNS trigger
AS $$
BEGIN
IF (NEW.a >= DATE '05-05-08' AND NEW.a < DATE '05-06-08') THEN
INSERT INTO partitions.ttt_050508 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-06-08' AND NEW.a < DATE '05-07-08') THEN
INSERT INTO partitions.ttt_050608 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-07-08' AND NEW.a < DATE '05-08-08') THEN
INSERT INTO partitions.ttt_050708 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-08-08' AND NEW.a < DATE '05-09-08') THEN
INSERT INTO partitions.ttt_050808 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-09-08' AND NEW.a < DATE '05-10-08') THEN
INSERT INTO partitions.ttt_050908 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-10-08' AND NEW.a < DATE '05-11-08') THEN
INSERT INTO partitions.ttt_051008 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-11-08' AND NEW.a < DATE '05-12-08') THEN
INSERT INTO partitions.ttt_051108 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-12-08' AND NEW.a < DATE '05-13-08') THEN
INSERT INTO partitions.ttt_051208 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-13-08' AND NEW.a < DATE '05-14-08') THEN
INSERT INTO partitions.ttt_051308 VALUES (NEW.*);
ELSIF (NEW.a >= DATE '05-14-08' AND NEW.a < DATE '05-15-08') THEN
INSERT INTO partitions.ttt_051408 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'ttt.a out of range: 2008-05-05 <= % < 2008-05-14', NEW.a;
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

ALTER FUNCTION public.ttt_insert_trigger() OWNER TO cdr;

SET search_path = partitions, pg_catalog;

--
-- Data for Name: ttt_050508; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050508 (id, a, b) FROM stdin;
1 2008-05-05 00:00:00 test11212
2 2008-05-05 00:00:00 test11212
3 2008-05-05 00:00:00 test11212
4 2008-05-05 00:00:00 test11212
5 2008-05-05 00:00:00 test11212
6 2008-05-05 00:00:00 test11212
7 2008-05-05 00:00:00 test11212
8 2008-05-05 00:00:00 test11212
9 2008-05-05 00:00:00 test11212
10 2008-05-05 00:00:00 test11212
11 2008-05-05 00:00:00 test11212
12 2008-05-05 00:00:00 test11212
13 2008-05-05 00:00:00 test11212
14 2008-05-05 00:00:00 test11212
15 2008-05-05 00:00:00 test11212
16 2008-05-05 00:00:00 test11212
17 2008-05-05 00:00:00 test11212
\.

--
-- Data for Name: ttt_050608; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050608 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_050708; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050708 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_050808; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050808 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_050908; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_050908 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_051008; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051008 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_051108; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051108 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_051208; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051208 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_051308; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051308 (id, a, b) FROM stdin;
\.

--
-- Data for Name: ttt_051408; Type: TABLE DATA; Schema: partitions; Owner: cdr
--

COPY ttt_051408 (id, a, b) FROM stdin;
\.

SET search_path = public, pg_catalog;

--
-- Data for Name: ttt; Type: TABLE DATA; Schema: public; Owner: cdr
--

COPY ttt (id, a, b) FROM stdin;
\.

--
-- Name: ttt_pkey; Type: CONSTRAINT; Schema: public; Owner: cdr; Tablespace:
--

ALTER TABLE ONLY ttt
ADD CONSTRAINT ttt_pkey PRIMARY KEY (id);

SET search_path = partitions, pg_catalog;

--
-- Name: ttt_050508_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050508_a ON ttt_050508 USING btree (a);

--
-- Name: ttt_050608_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050608_a ON ttt_050608 USING btree (a);

--
-- Name: ttt_050708_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050708_a ON ttt_050708 USING btree (a);

--
-- Name: ttt_050808_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050808_a ON ttt_050808 USING btree (a);

--
-- Name: ttt_050908_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_050908_a ON ttt_050908 USING btree (a);

--
-- Name: ttt_051008_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051008_a ON ttt_051008 USING btree (a);

--
-- Name: ttt_051108_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051108_a ON ttt_051108 USING btree (a);

--
-- Name: ttt_051208_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051208_a ON ttt_051208 USING btree (a);

--
-- Name: ttt_051308_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051308_a ON ttt_051308 USING btree (a);

--
-- Name: ttt_051408_a; Type: INDEX; Schema: partitions; Owner: cdr; Tablespace:
--

CREATE INDEX ttt_051408_a ON ttt_051408 USING btree (a);

SET search_path = public, pg_catalog;

--
-- Name: insert_ttt_trigger; Type: TRIGGER; Schema: public; Owner: cdr
--

CREATE TRIGGER insert_ttt_trigger
BEFORE INSERT ON ttt
FOR EACH ROW
EXECUTE PROCEDURE ttt_insert_trigger();

--
-- Name: public; Type: ACL; Schema: -; Owner: postgres
--

REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON SCHEMA public FROM postgres;
GRANT ALL ON SCHEMA public TO postgres;
GRANT ALL ON SCHEMA public TO PUBLIC;

--
-- PostgreSQL database dump complete
--

I figure this subject belongs on the performance mailing list because it is about partitioning, which is a performance issue.

I'm working on partitioning some of the tables used by an application that uses OpenJPA. It turns out that OpenJPA is sensitive to the numbers returned when you do an insert. So I put together a test and attached it. My postgres version is 8.3.1 compiled from source.

My problem is that this:
test=> INSERT INTO ttt (a, b) VALUES ('5-5-08', 'test11212');
INSERT 0 0
Time: 21.646 ms
needs to show:
INSERT 0 1

or OpenJPA will not accept it. The insert works, but OpenJPA does not believe it and aborts the current transaction.

Is it possible to have partitioning and have insert show the right number of rows inserted?

Thanks,

--Nik

Re: [HACKERS] Setting a pre-existing index as a primary key

Joshua D. Drake wrote:
> Tom Lane wrote:
>
> >> Well it should be optional but it would be nice if we had the option to
> >> have it renamed per the default... meaning the same output if I were to
> >> do this:
> >
> > If you want that, you can rename the index (either before or afterwards).
> > I don't see any reason to clutter the make-constraint-from-index command
> > with questions of renaming.
>
> As a counter point, I don't see any reason to make the DBA's life
> harder. Sure it is just one step but it is a human step, prone to error
> and taking more time than it should. Why not just make it easy?
> Especially when the easy isn't sacrificing data integrity or quality of
> product?

I realize most feel we don't need to add a rename to this, but there are
two more reasons _not_ to do this. First, there is the possibility of
name collision with the new name so you would then require the user to
use the option not to rename. Plus, if you renamed, the old index name
would go away, and some people might think the index was removed and not
realize it was renamed, or find it confusing it was renamed.

--
Bruce Momjian <bruce@momjian.us>

http://momjian.us

EnterpriseDB

http://enterprisedb.com

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

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

Re: [HACKERS] bloated heapam.h

Zdenek Kotala wrote:
> Alvaro Herrera wrote:

>> (Digging further, it seems like bufpage.h should also include transam.h
>> in order to get TransactionIdIsNormal ... I start to wonder how many
>> problems of this nature we have on our headers. Without having a way to
>> detect whether the defined macros are valid, it seems hard to check
>> programatically, however.)
>
> I attached script which should check it. In first step it runs C
> preprocessor on each header (postgres.h is included as well). The output
> from first step is processed again with preprocessor and define.h is
> included. Define.h contains "all" used macros in following format:
>
> #define SIGABRT "NOT_EXPANDED_SIGABRT"
>
> Main problem is how to generate define.h. I used following magic:
>
> grep "^#define" `find . -name "*.h"` | cut -d" " -f 2 | cut -f 1 | cut -f 1 -d"("
>
> but it generates some noise as well. Maybe some Perl or AWK magic should be better.

So were you able to detect anything bogus with this technique?

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [pgsql-es-ayuda] ERROR: no existe la relación con OID 26795658

Matias Ocampo escribió:
> Hola Gente, necesito de su ayuda ...

Matías,

Es muy mala educación publicar una pregunta en un hilo que trata de otro
tema.

Por favor usa "redactar" para enviar un mensaje nuevo. Y usa un Asunto
apropiado a tu pregunta.

Gracias.

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
TIP 4: No hagas 'kill -9' a postmaster

Re: [HACKERS] bloated heapam.h

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Oops :-( I just noticed that I removed bufmgr.h from bufpage.h, which
> > is a change you had objected to previously :-(
> > http://archives.postgresql.org/pgsql-patches/2008-04/msg00149.php
>
> Hmm. I did notice that the patch seemed to need to add bufmgr.h
> inclusions to an awful lot of files, but I'd forgotten the argument
> about the bufpage.h macros needing it. Do you want to undo that
> aspect of it?

Done -- I put back bufmgr.h into bufpage.h. Surely there is a better
structure for this, perhaps involving more header files, but I don't
have time for that ATM.

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

[COMMITTERS] pgsql: Put back bufmgr.h in bufpage.h -- it is needed by some macros.

Log Message:
-----------
Put back bufmgr.h in bufpage.h -- it is needed by some macros.

Remove #include bufmgr.h from (most?) source files which already include
bufpage.h.

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

[pgsql-es-ayuda] Sobre el ERROR: cross-database references are not implemented

Hola Gente, necesito de su ayuda ...

Tengo que hacer una vista que me traiga los datos de otra BD que contiene los datos del censo del año anterior.

Cuando hago referencia a la otra BD (BD del año anterior) me sale el siguiente error: "cross-database references are not implemented". ¿Que este error?

Lo que contiene la vista es lo siguiente:

SELECT CAMPO1, CAMPO2, CAMPO3

FROM bd1.schema1.tabla1 as alias

La vista que trato de crear está en la BD2.

¿Que tengo que hacer?

¿Como hago para que desde la BD2 vea la tabla de la BD1?

Lo que me puedan orientar desde ya muchas gracias ...

Un Abrazo a todos ...

Matias

Re: [pgsql-es-ayuda] ERROR: no existe la relación con OID 26795658

Hola Gente, necesito de su ayuda ...

Tengo que hacer una vista que me traiga los datos de otra BD que contiene los datos del censo del año anterior.

Cuando hago referencia a la otra BD (BD del año anterior) me sale el siguiente error: "cross-database references are not implemented". ¿Que este error?

Lo que contiene la vista es lo siguiente:

SELECT CAMPO1, CAMPO2, CAMPO3

FROM bd1.schema1.tabla1 as alias

La vista que trato de crear está en la BD2.

¿Que tengo que hacer?

¿Como hago para que desde la BD2 vea la tabla de la BD1?

Lo que me puedan orientar desde ya muchas gracias ...

Un Abrazo a todos ...

Matias

Re: [pgsql-es-ayuda] ERROR: no existe la relación con OID 26795658

Edwin Perez Lozano escribió:
> Buen dia todos..
>
> Bueno estoy realizando pruebas con slony, pero el problema pasa que
> cuando comienzo a sincronizar (una base de datos maestro-esclavo) y
> tratan de ingresar datos por medio de la aplicacion la cual esta con la
> base de datos maestra, me sale el eror del oid, para solucionar el
> percanse reincio el servicio de la base de datos.. lo cual no deberia de
> ser... porque sucede esto cuando utilizo el slony.. ?¿ falta algo por
> configurar para evitar este error...?

El error ese, ¿sale al ejecutar una función plpgsql?

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
--
TIP 2: puedes desuscribirte de todas las listas simultáneamente
(envía "unregister TuDirecciónDeCorreo" a majordomo@postgresql.org)

[GENERAL] pg_standby / WAL archive-restore through system restarts

Just wanted to see if others would confirm my impressions about running
WAL archiving and pg_standby restore.

Server A (Primary): Runs PG 8.3 with WAL archiving enabled. Each WAL is
copied over the network to Server B. (A previous 'tar backup' of the
database along with the requisite psql commands
pg_start_backup()/pg_stop_backup() was done and restored on Server B.)

Server B (Backup): Runs PG 8.3 in 'recovery mode' (recovery.conf), with
pg_standby feeding the WAL archives.

The idea is that if Server A dies, Server B will have a relatively close
snapshot of the most recent database changes and could take over as the
new primary database server.

During regular operations, Server A and Server B may independently need
to be rebooted or postgresql restarted, etc. Is there any sync-ing
between Server A and Server B I have to worry about when doing this?

That is, will Server B need to know anything about the fact that the
postmaster on Server A going up and down? Will it just run normally,
waiting for the restarted Server A to send over new WAL archives?

Can I shutdown Server B simply by killing the postmaster and restart it
back in recovery mode to continue re-syncing where it left off? Or does
stopping Server B in recovery mode require any manual re-sync steps
before it can resume recovery?

If I ever tell Server B to stop recovery (assuming I didn't intend for
it to become the new primary), I cannot just resume recovery mode unless
I do the full data backup again from Server A and restore on Server B in
start in recovery mode. That is, once you tell PG to end recovery, it
becomes a primary and no longer can resume recovery mode without redoing
the recovery setup steps.

Are these correct?

Thanks,
David

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

Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Die Optimale Tabellenstruktur für Postgres 8.1-8.3 ?

On Mon, 12 May 2008 01:12:37 +0200 rudi@je-more.de wrote:

> Ich glaube Postgres ist für meine Anforderungen doch nicht so das rechte.

Das möge sein, kannst du diese Aussage aber auch mit einigen Tests
deinerseits belegen oder sind dies bloss Vermutungen, teilweise
basierend auf Aussagen unsererseits?

> Gerade hinsichtlich des Online Backups, des Skalierbarkeitspfads und der
> Uptimemöglichkeiten die
> mir ORACLE einfach out of the Box bietet und eben das ich es kenne wird
> wohl dazu führen das ich
> eher Oracle Express oder die Standardedition nehmen werde.

Es ist schön, wenn du Oracle von Haus aus gut kennst. Wenn ich mich
richtig erinnere, waren es gerade die Kosten, die dich dazu bewegt
haben, einmal einen Blick auf PostgreSQL zu werfen.

Das Thema Backup habe ich aufgeworfen, nicht du. Auf einmal ist es
interessant für dich, schau an - aber was genau hast du davon bei
PostgreSQL gesehen? Online Backup ist ohne weiteres möglich, nicht nur
seit gestern oder der aktuellsten PG-Version.

Uptime realisiert man nicht, indem man viel Geld dem
Datenbankhersteller hinterherwirft und hofft, dass die Patchdays nur
alle 3 Monate sind ... muss man halt die DB nur vier mal im Jahr
herunterfahren: Ziel erreicht. Nebenwirkungen egal.

Wenn ich zur Skalierbarkeit folgende Aussage von dir lese:

"Richtige Liveresultate bekomme ich also erst im Betrieb mit dem neuen
System, bis dahin heißt so gut wie möglich schätzen."

Und:

"In Zukunft soll die gesammt db so mit ca. 1 Terrabyte bis 1 Exobyte
keine Probleme haben"

Dann weiss ich jetzt schon, das du ca. bei 5-10 GB die nächsten
kleineren und vielleicht bei 100 GB die nächsten größeren Probleme
hast. Auch hier zählt, dass es nicht ausreicht, den DB-Hersteller das
Geld nachzuwerfen. Dafür muss man sich in seiner Anwendung und seiner
Datenbankstruktur auch schon mal Gedanken machen.

> Auch Bilder innerhalb von Blobs sowie unfangreiche Textdokumente
> scheinen mir bei Postgres
> nicht ideal aufgehoben.

Ich lese dauernd "scheinen mir".
Du hast deine Entscheidung zwar schon getroffen, aber kein einziges
stichhaltiges Argument dargelegt. Wie bist du zu der Aussage gekommen,
dass die Bilder nicht "ideal aufgehoben" sind, wie sah dein Testcase
dafür aus und welche Ergebnisse hat er geliefert?

> aber auf ein Erstrelease will ich mich da einfach nicht verlassen

Zum Thema "Erstrelease" hat Andreas Kretschmer schon ausführlich
Stellung bezogen.

> Der Lese und Darstellungsspeed bei Blobinhalten sowie
> das gesammte drumherrum ist etwas das ORACLE schon lange kennt und relativ
> sicher abwickelt (ohne Krücken übers Filesystem mit Tempdateien u.s.w).

Redest du von "large objects" oder redest du schlicht von "bytea"?
Wenn du nur von "Blobinhalten" sprichst, dürftest du "bytea" meinen ...
zeig mir doch mal, wo du dabei "Krücken übers Filesystem mit
Tempdateien" benötigst?

Auch hier: wie sah dein Testcase aus, wie das Ergebnis?

Oder war deine gesamte Betrachtung nur eine Luftnummer ohne dass du
irgendeinen realen Test gefahren hast?

--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors

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

Re: [GENERAL] choiche of function language was: Re: dynamic procedure call

mail@webthatworks.it (Ivan Sergio Borgonovo) writes:
> On Sat, 10 May 2008 07:35:36 +0200
> "Pavel Stehule" <pavel.stehule@gmail.com> wrote:
>
>> your application different execution paths. Generally I can say, so
>> plpgsql isn't well language for this games, and better is using
>> plperl, plpython or other external language.
>
> It is clear what is at least one of the advantage of plpython or
> plperl over plpgsql, but then what are the advantages of plpgsql over
> the rest of the crowd other than resembling the language used in
> Oracle?

Well, plpgsql has the merit that its operations and control structures
are directly oriented towards "database stuff," so there's no need to
(for instance) invoke functions (e.g. - running queries via
spi_exec(), spi_query(), ...) in order to perform database
operations.

One of the salutory effects is that there is a pl/pgsql debugger that
can automatically handle things like single-stepping, and it does not
need to be aware of any Perl/Python internals.
--
"cbbrowne","@","linuxfinances.info"
http://linuxfinances.info/info/sap.html
"...you might as well skip the Xmas celebration completely, and
instead sit in front of your linux computer playing with the
all-new-and-improved linux kernel version." -- Linus Torvalds

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

Re: [HACKERS] [COMMITTERS] pgsql: Convert wal_sync_method to guc enum.

Magnus Hagander wrote:
> Tom Lane wrote:
> > mha@postgresql.org (Magnus Hagander) writes:
> > > Convert wal_sync_method to guc enum.
> >
> > Buildfarm says you broke things for Windows.
>
> Yeah, working on that with Dave. First part was to unbreak the error
> message so we can actually figure out what's broken :-(
>

I need to leave for a couple of hours, will look again when I get back.
But so far, I'm quite surprised. Here's my reasoning, please poke holes
in it :-)

1) Win32 always defines O_DSYNC (win32.h)
2) That means we should always define OPEN_DATASYNC_FLAG (xlogdefs.h,
line 107)
3) That means that the error should not happen at all, because of
xlog.c line 6358.

Anybody who can kill this argument before I get back ;-) It's obviously
flawed somewhere...

//Magnus

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

Re: [GENERAL] How to create a function with multiple RefCursor OUT parameters

On Sun, May 11, 2008 at 2:43 PM, Chuck Bai <cbai22@gmail.com> wrote:
> CREATE OR REPLACE FUNCTION test_refcursor(INOUT tcount integer, OUT o_user
> refcursor, OUT o_name refcursor)
> RETURNS record AS
> $BODY$
> BEGIN
> tcount := tcount + 1;
> OPEN o_user FOR SELECT * FROM user_table;
> OPEN o_name FOR SELECT * FROM name_table;
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE
>
> Question 1: The function is not working with Npgsql .NET data provider. It
> did not return a valid .NET DataSet. But the INOUT parameter tcount works
> fine. How could I test the above function with SQL in pgAdmin III? I want to
> find out if problem is in the function or in the Npgsql.

You can test from pgAdmin by simply running queries in the query
window. This sort of thing however might be a better fit for psql
(pasting your queries in the query window). You need to use
transactions since refcursors only only good inside a transaction.

> Question 2: pgAdmin III automatically added "RETURNS record" in the above
> function when RETURNS clause is not specified initially. Why is that? Is
> this the problem since it returns only single data table with the following
> value? How to fix it?

For a function with >1 out parameters, the output type is a record.
Your function returns (int, refcursor, refcursor) as defined. SELECT
* FROM test_refcursor(7); would returns a row with three variables ( a
record).

> tcount o_user o_name
> 23 <unnamed portal 1> <unnamed portal 2>

You probably want to name your refcursors. The way to do this is
simply o_user := 'something'; inside your pl/pgsql function.

-- inside pl/pgsql_function
refcur_variable := 'mycursor'

-- outside function, but in same transaction
FETCH ALL FROM mycursor -- or, "mycursor"

So, it would at least take a few 'queries' from the perppective of the
client to do what you are attempting. However, all the data is 'set
up' for return to the client by the server in the main function. The
server will hang on to it as long as the current transaction is valid
and then release it.

>
>
> Question 3: I want to return a single DataSet with each OUT RefCursor map
> to a DataTable within the DataSet, plus extra OUT parameters for individual
> OUT values. How could I create such a function?

Your question is a little opaque to me. A refcursor is in PostgreSQL
terms a 'hande' to a set, not a DataTable the way you are
thinking...it's really a fancy string. so, (INOUT int, OUT refcursor,
OUT refcursor) returns takes an 'int' in and returns an int and two
refcursors (strings), with extra work to return this to the client, at
least in terms of SQL statements.

I haven't used .net for a while but IIRC it's probably not possible to
'fill' multiple data tables in a single query without at least some
manual work. Some of the npgsql experts might have some suggestions
however. It really depends on how the code operates inside the npgsql
library.

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: [ADMIN] how to get a number of seconds in some interval ?

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> Î£Ï„Î¹Ï‚ Monday 12 May 2008 17:32:39 Î¿/Î· Julius Tuskenis ÎÎ³ÏÎ±ÏˆÎµ:
>> do you know why division of intervals was not implemented? As I see it -
>> there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' =
>> 1.5 .... Seems straight forward...
>>
> No idea why. However as you pose it,it makes sense.

No, it doesn't really, because intervals contain multiple components.
What would you define as the result of
'1 month 1 hour' / '1 day'
bearing in mind that the number of days per month is not fixed, and
neither is the number of hours per day?

You can derive a number if you're willing to make arbitrary assumptions
about the relationships of the different units (as indeed extract(epoch)
does), but hard-wiring such assumptions into a basic arithmetic operator
doesn't seem like a good idea to me.

regards, tom lane

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

[ANNOUNCE] New Practical SQL training course

2ndQuadrant have developed a new course aimed at helping developers,
testers and power users get to grips with the power of the SQL Language.
There is a strong emphasis on finding solutions to problems and avoiding
common mistakes - not just on learning the syntax.

Practical SQL teaches SQL in a fairly generic way, covering SQL-Standard
ways of writing queries wherever possible. This will help you to apply
that knowledge when using PostgreSQL and PostgreSQL-variant DBMS, as
well as other enterprise RDBMS.

So, if you produce business solutions using existing databases rather
than building new ones, this is the course for you. Additional courses
are also available to explain PostgreSQL specific features, performance
and server administration.

Two variants of the course are now available for public booking:

* Practical SQL for Developers
16-17 June 2008 in London

* Practical SQL for Business Intelligence
23-24 June 2008 in London

For London courses, your tutor will be Huw Ringer. Huw has worked with
RDBMS for 20 years. His in-depth experience in developing SQL
applications on at least half-a-dozen RDBMS has allowed him to deliver
many successful business intelligence projects. Huw's understanding of
the complexities of SQL will help you improve your coding to avoid
pitfalls and performance issues.

Please visit http://www.2ndquadrant.com/training.htm for booking, or
contact info@2ndquadrant.com for further information.

We look forward to working with you,

Best Regards,

--
Simon Riggs
2ndQuadrant

http://www.2ndQuadrant.com

---------------------------(end of broadcast)---------------------------
-To unsubscribe from this list, send an email to:

pgsql-announce-unsubscribe@postgresql.org

Re: [ADMIN] how to get a number of seconds in some interval ?

Στις Monday 12 May 2008 17:32:39 ο/η Julius Tuskenis έγραψε:
> Thank you, Achilleas.
>
> do you know why division of intervals was not implemented? As I see it -
> there should be an easy way to do so. '1 hour 30 minutes' / '1 hour' =
> 1.5 .... Seems straight forward...
>
No idea why. However as you pose it,it makes sense.
>
> Achilleas Mantzios rašė:
> > Στις Monday 12 May 2008 16:44:16 ο/η Julius Tuskenis έγραψε:
> >
> >> Hello,
> >>
> >> Is there a good method of knowing how mutch seconds does some interval
> >> have?? I tried my_interval / (interval '1 second'), but there is no
> >> operator interval / interval defines (I wonder why).
> >> Is there a method to do so?
> >>
> >>
> > smth like
> > dynacom=# SELECT extract(epoch from '49 hours 0 mins 12 seconds'::interval);
> >
> >
> >> --
> >> Julius Tuskenis
> >> Programavimo skyriaus vadovas
> >> UAB nSoft
> >> mob. +37068233050
> >>
> >>
> >>
> >
> >
> >
> >
>
>

--
Achilleas Mantzios

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

Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:

Abdus Samad Ansari <ansarias@wbpcb.gov.in> writes:
> I have setup PHP/Postgres and is running fine upto document root
> i.e. /var/www/html, but when i am calling it through a cgi-bin php
> file
> it is giving log error as :
> [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to
> connect
> to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF;

I don't know how the error message is structured, but if
"\x04\xe6\xe3\xbfP" refers to the host, perhaps the connection string
you are handing to pg_connect() is trashed. What is the actual code
you are using for this call?

-- Andy

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

Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches

Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> Hmm. Is that really a good idea, compared to hard-wiring the checks
>> into nodeSeqscan and friends?

> My eyebrows went up when I read this too. Presumably, if it's hardwired
> like you suggest then the planner can't take any account of the filter,
> though. Do we want it to?

Well, the planner could have hardwired knowledge about the existence of
the hardwired filters --- if anything, that'd probably be easier than
hacking it to have a similar level of knowledge about generic-looking
function calls. But in reality, since we don't know at plan time which
userid will execute the constructed plan, I'm not sure we could come up
with useful estimates anyway.

regards, tom lane

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

Re: [COMMITTERS] pgsql: Convert wal_sync_method to guc enum.

Tom Lane wrote:
> mha@postgresql.org (Magnus Hagander) writes:
> > Convert wal_sync_method to guc enum.
>
> Buildfarm says you broke things for Windows.

Yeah, working on that with Dave. First part was to unbreak the error
message so we can actually figure out what's broken :-(

//Magnus

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

Re: [HACKERS] constraint exclusion analysis caching

Stephen Frost wrote:

> > And it's completely unnecessary. For example, I have set my majordomo
> > preferences for the postgresql.org lists not to send me copies of emails
> > where I am also in the To: or Cc: lines. After doing that I get no
> > duplicates.
>
> This doesn't help at all, actually. As I pointed out previously, I
> *want* the mail through the list, what I *don't* want is people sending
> list mail directly to me.

Wouldn't it make sense, then, to filter any email which is Cc'ed to a
list, into that list's folder? Add to that a bit of duplicate removal
(say, procmail's, or whatever you use) and you're set.

--
Alvaro Herrera

http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches

Tom Lane wrote:
> KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
>
>> Tom Lane wrote:
>>
>>> Yeah, I remember those. What needs to be looked at here is *why* the
>>> output is changing. For a patch that allegedly does not touch the
>>> planner, it's fairly disturbing that you don't get the same results.
>>>
>
>
>> SE-PostgreSQL does not touch the planner, but it modifies given query
>> to filter violated tuples for the current user.
>>
>
> Hmm. Is that really a good idea, compared to hard-wiring the checks
> into nodeSeqscan and friends? I didn't look at the query-rewriting
> portion of the patch in any detail, but I'd tend not to trust such
> a technique very far: getting it right is going to be quite complex
> and probably bug prone.
>
>

My eyebrows went up when I read this too. Presumably, if it's hardwired
like you suggest then the planner can't take any account of the filter,
though. Do we want it to?

OTOH, I'm not happy about silently rewriting queries, either - it would
make optimising queries a lot harder, I suspect.

cheers

andrew

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

Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:

Abdus Samad Ansari <ansarias@wbpcb.gov.in> writes:
> I have setup PHP/Postgres and is running fine upto document root
> i.e. /var/www/html, but when i am calling it through a cgi-bin php file
> it is giving log error as :
> [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect
> to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF;

Hmm, so the most useful part of the error message is getting trashed :-(

One possible explanation for that is that you're using a non-thread-safe
libpq in a threaded environment. What PG version is your libpq,
exactly, and how did you build or obtain it?

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: [ADMIN] Orphan TOAST object

Problem has now been solved. Thanks a lot for all your help.

On 5/12/08, Tom Lane <tgl@sss.pgh.pa.us> wrote:

"James Farrugia" <james.farrugia@gmail.com> writes:
> One last thing...can we run into data-loss problems with successfully
> vacuumed tables even if there is one unvacuumed database object; what would
> have happened if I ignored to vacuum that rogue pg_toast (which was the only
> unvacuumed object within the entire database)?

The database would have shut down when you got to the
2-billion-transactions mark. So you'd have had to solve the problem
sooner or later anyway.

regards, tom lane

Re: [COMMITTERS] pgsql: Convert wal_sync_method to guc enum.

mha@postgresql.org (Magnus Hagander) writes:
> Convert wal_sync_method to guc enum.

Buildfarm says you broke things for Windows.

regards, tom lane

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

[pdxpug] Thursday Meeting! 3/15/08 - PostgreSQL for Pythoneers

Topic: PostgreSQL for Pythoneers
Speaker: Jason Kirtland
Date: Thursday, 5/15/08
Location: FreeGeek, 1731 SE 10th Ave

Come join us for an evening of Pythoneering with Jason Kirtland, a key
force behind the Portland Python Interest Group -
http://wiki.python.org/moin/PortlandPythonUserGroup.

They are having
Lightning Talks tomorrow night at Cubespace.

Jason hasn't provided too many details at this point, but I imagine it
will be a tour of a popular Python ORM and a discussion of using it
with PostgreSQL. Perhaps Jason can reply to this message with more
detail :)

Refreshments afterward at the Lucky Lab!

-selena

--
Selena Deckelmann
United States PostgreSQL Association - http://www.postgresql.us
PDXPUG - http://pugs.postgresql.org/pdx
Me - http://www.chesnok.com/daily

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

Re: [HACKERS] [0/4] Proposal of SE-PostgreSQL patches

KaiGai Kohei <kaigai@ak.jp.nec.com> writes:
> Tom Lane wrote:
>> Yeah, I remember those. What needs to be looked at here is *why* the
>> output is changing. For a patch that allegedly does not touch the
>> planner, it's fairly disturbing that you don't get the same results.

> SE-PostgreSQL does not touch the planner, but it modifies given query
> to filter violated tuples for the current user.

Hmm. Is that really a good idea, compared to hard-wiring the checks
into nodeSeqscan and friends? I didn't look at the query-rewriting
portion of the patch in any detail, but I'd tend not to trust such
a technique very far: getting it right is going to be quite complex
and probably bug prone.

>> Are you sure that the security_label type should not have an array type?

> Yes, security_label type should not have an array type.

You didn't provide one ounce of justification for making it not obey the
expected behavior, so I'm not accepting this position. It doesn't seem
to me to be all that unlikely that users would want to compute with
arrays of security labels. As an example:
select ... where security_label in ('foo', 'bar')
which will become an = ANY(ARRAY[]) construct under the hood.

regards, tom lane

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

Re: [HACKERS] constraint exclusion analysis caching

Stephen Frost wrote:
> Andrew,
>
> * Andrew Dunstan (andrew@dunslane.net) wrote:
>
>> For example, because it put *my* address in the list for your message
>> above, it caused my MUA quite correctly to add a To: line to myself,
>> which I certainly didn't want to do.
>>
>
> Honestly, I suspect thunderbird just doesn't know your addresses if
> it's adding your address back in. Adding your address isn't for you-
> it's for other people. The, completely reasonable, assumption is that
> if your address was included in a To or Cc that you're not on the list
> and stripping that out would mean you'd be left out.
>
>
>> And it's completely unnecessary. For example, I have set my majordomo
>> preferences for the postgresql.org lists not to send me copies of emails
>> where I am also in the To: or Cc: lines. After doing that I get no
>> duplicates.
>>
>
> This doesn't help at all, actually. As I pointed out previously, I
> *want* the mail through the list, what I *don't* want is people sending
> list mail directly to me.
>
>
>> And I don't casue anyone else to have to edit the addresses when they
>> reply to my mail.
>>
>
> Are you sure thunderbird recognizes the email address you use for
> posting as a local identity/account? Mutt has a specific 'alternates'
> configuration to let it know what addresses are local.
>
>
>> If you want to ensure that you reply to a list, use an MUA that has a
>> reply-to-list command - I see you use mutt, which has such a command
>> IIRC.
>>
>
> Indeed, and it's exactly what I use when replying to list mail. The
> issue isn't making sure that *I* reply to a list, it's asking other
> people to reply through the list rather than to me.
>
>
>

a. I don't use Thunderbird.
b. Of couse the MUA knows what my address is.
c. Yours are pretty much the *only* settings of all the users of this
list that cause me issues. Judging by your own words I am not alone in
being thus inconvenienced (otherwise, why would "an amazing number" of
people ask you about it?). If you don't care about that then there's
nothing much I can do. Alvaro used to have a similar setup. When I
complained he very kindly fixed it.
d. Your "completely reasonable" assumption above is, of course, bogus.
Most people when replying to a list reply to all adresses. Assuming that
the non-list addresses are for people not on the list is nonsense.

cheers

andrew

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

[COMMITTERS] pgsql: Report which WAL sync method we are trying to change to when it

Log Message:
-----------
Report which WAL sync method we are trying to change *to* when it fails,
not which one we had before (that worked, and thus is completley irrelevant)

Modified Files:
--------------
pgsql/src/backend/access/transam:
xlog.c (r1.304 -> r1.305)
(http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/access/transam/xlog.c?r1=1.304&r2=1.305)

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

Re: [ADMIN] how to get a number of seconds in some interval ?

Στις Monday 12 May 2008 16:44:16 ο/η Julius Tuskenis έγραψε:
> Hello,
>
> Is there a good method of knowing how mutch seconds does some interval
> have?? I tried my_interval / (interval '1 second'), but there is no
> operator interval / interval defines (I wonder why).
> Is there a method to do so?
>
smth like
dynacom=# SELECT extract(epoch from '49 hours 0 mins 12 seconds'::interval);

> --
> Julius Tuskenis
> Programavimo skyriaus vadovas
> UAB nSoft
> mob. +37068233050
>
>

--
Achilleas Mantzios

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

Re: [pgsql-es-ayuda] Programa similar a Enterprise Manager

begin:vcard
fn;quoted-printable:Jenaro Centeno G=C3=B3mez
n;quoted-printable:Centeno G=C3=B3mez;Jenaro
org;quoted-printable:Alimentos La Concordia, S.A. de C.V.;Tecnolog=C3=ADas de la Informaci=C3=B3n
adr;quoted-printable:Col. La Marimba;;Loma de Prados No. 1332;Lagos de Moreno;JAL;47470;M=C3=A9xico
email;internet:jcenteno@aldia.com.mx
title:Coordinador de TI
tel;work:+52 474 741 9200
tel;fax:+52 474 741 9200
x-mozilla-html:FALSE
url:http://www.aldia.com.mx
version:2.1
end:vcard

Creo que mas bien lo que buscas es algo como http://phppgadmin.sourceforge.net/ , no tiene todas las características del Enterprise Manager pero para el acceso web a ejecutar scripts y crear algunos objetos te puede funcionar. La parte del análisis y mantenimiento no esta tan completa pero de algo te puede servir. Yo tengo en mis instalaciones Oracle 10g y 11g y sinceramente casi no utilizo el EM, al igual que con PostgreSQL prefiero la interfaz de comandos, pero es cuestión de gustos.

juan jaimes escribió:

si pgadmin3, navicat, y varios mas que no recuerdo ahorita

2008/5/10 Davide Barzi! <developower@hotmail.com>:

Hola amigos quisiera saber si existe un Programa similar a Enterprise Manager de SQL, pero para PG y si es free mucho mejor.

Explore the seven wonders of the world Learn more!

--
atte

juan antonio jaimes valle
toluca, mexico
juanjava@gmail.com
juanjava@yahoo.com

Re: [ADMIN] Orphan TOAST object

"James Farrugia" <james.farrugia@gmail.com> writes:
> One last thing...can we run into data-loss problems with successfully
> vacuumed tables even if there is one unvacuumed database object; what would
> have happened if I ignored to vacuum that rogue pg_toast (which was the only
> unvacuumed object within the entire database)?

The database would have shut down when you got to the
2-billion-transactions mark. So you'd have had to solve the problem
sooner or later anyway.

regards, tom lane

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

Re: [HACKERS] constraint exclusion analysis caching

Andrew,

* Andrew Dunstan (andrew@dunslane.net) wrote:
> For example, because it put *my* address in the list for your message
> above, it caused my MUA quite correctly to add a To: line to myself,
> which I certainly didn't want to do.

Honestly, I suspect thunderbird just doesn't know your addresses if
it's adding your address back in. Adding your address isn't for you-
it's for other people. The, completely reasonable, assumption is that
if your address was included in a To or Cc that you're not on the list
and stripping that out would mean you'd be left out.

> And it's completely unnecessary. For example, I have set my majordomo
> preferences for the postgresql.org lists not to send me copies of emails
> where I am also in the To: or Cc: lines. After doing that I get no
> duplicates.

This doesn't help at all, actually. As I pointed out previously, I
*want* the mail through the list, what I *don't* want is people sending
list mail directly to me.

> And I don't casue anyone else to have to edit the addresses when they
> reply to my mail.

Are you sure thunderbird recognizes the email address you use for
posting as a local identity/account? Mutt has a specific 'alternates'
configuration to let it know what addresses are local.

> If you want to ensure that you reply to a list, use an MUA that has a
> reply-to-list command - I see you use mutt, which has such a command
> IIRC.

Indeed, and it's exactly what I use when replying to list mail. The
issue isn't making sure that *I* reply to a list, it's asking other
people to reply through the list rather than to me.

Thanks,

Stephen

[pgadmin-hackers] Windows test

Hi,

Can someone do a little test for me on Windows? Here it is:

* launch pgAdminIII
* connect to a server
* click on the second or third database (all but the first)
* right click and choose "Refresh"

Was your database replaced ? (only the node in pgAdminIII, not your real
database :) )

And was it replaced by the first database on the objects navigator ?

(it keeps doing this for me on Linux (Kubuntu), only with the database
object nodes)

Thanks.

--
Guillaume.

http://www.postgresqlfr.org

http://dalibo.com

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

Re: [HACKERS] Stack depth exceeded error

Hi,

The code uses Asynchronous I/O for fetching certain tids. The code works fine if I use only one condition in where condition, but fails if I use multiple condition.

--
Suresh Iyengar

Gregory Stark <stark@enterprisedb.com> wrote:

"Suresh" writes:

> Hello,
>
> I have custom postgres code.

What kind of code is this? The error below is typical if you create new
threads in the server.

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

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

Re: [HACKERS] Stack depth exceeded error

"Suresh" <suiyengar@yahoo.com> writes:

> Hello,
>
> I have custom postgres code.

What kind of code is this? The error below is typical if you create new
threads in the server.

--
Gregory Stark
EnterpriseDB

http://www.enterprisedb.com

Ask me about EnterpriseDB's 24x7 Postgres support!

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

[ADMIN] how to get a number of seconds in some interval ?

Hello,

Is there a good method of knowing how mutch seconds does some interval
have?? I tried my_interval / (interval '1 second'), but there is no
operator interval / interval defines (I wonder why).
Is there a method to do so?

--
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050

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

[pgsql-de-allgemein] == Wöchentlicher PostgreSQL Newsletter - 11. Mai 2008 ==

Der Originalartikel befindet sich unter:
http://people.planetpostgresql.org/dfetter/index.php?/archives/172-PostgreSQL-Weekly-News-May-11-2008.html

== Wöchentlicher PostgreSQL Newsletter - 11. Mai 2008 ==

== PostgreSQL Produkt Neuigkeiten ==

DBD::Pg 2.7.1 erschienen.
http://search.cpan.org/dist/DBD-Pg/

OpenX 2.5.67-beta erschienen, jetzt mit PostgreSQL Support.
http://www.openx.org/beta

pgsnap 0.3.1 erschienen.
http://pgsnap.projects.postgresql.org/

pg_top 3.6.2 erschienen.
http://ptop.projects.postgresql.org/

psqlODBC 08.03.0200 erschienen.
http://psqlodbc.projects.postgresql.org/release.html

PostgreSQL Data Wizard 8.4 erschienen.
http://www.sqlmaestro.com/products/postgresql/datawizard/

== PostgreSQL Jobs im Mai ==

http://archives.postgresql.org/pgsql-jobs/2008-05/threads.php

== PostgreSQL Lokal ==

BWPUG wird ihr erstes Treffen überhaupt am Mittwoch, dem 14. Mai im
OmniTI Hauptquartier in Columbia, MD, abhalten.
http://pugs.postgresql.org/bwpug

PGCon 2008 wird vom 20. bis 23. Mai in Ottawa sein.
http://www.pgcon.org/2008/

Die griechische PUG wird einen Stand auf der dritten Griechischen FLOSS
Konferenz vom 27.-29. Mai in Athen, NTUA, haben.
http://www.postgresql.gr

LAPUG trifft sich am 30. Mai 2008 im Cal Poly.
Non-trivial updateable views, von Keith Larson.
http://pugs.postgresql.org/node/390

PGDay wird in Portland am Tag vor der OSCON stattfinden.
http://pugs.postgresql.org/taxonomy/term/53

Der Call for Papers für die Utah Open Source Konferenz 2008 ist bis zum
1. Juni offen. Diese 2. jährliche Konferenz wird vom 28. bis 30. August
2008 in Salt Lake City, UT stattfinden.
http://2008.utosc.com/

== PostgreSQL in den News ==

Planet PostgreSQL: http://www.planetpostgresql.org/

General Bits, Archive und gelegentliche News Artikel:
http://www.varlena.com/GeneralBits/

Dieser wöchentliche PostgreSQL Newsletter wurde erstellt von David
Fetter

Sende Neuigkeiten und Ankündigungen bis Sonntag, 15 Uhr Pazifischer
Zeit. Bitte sende englische Beiträge an david@fetter.org, deutsche an
pwn@pgug.de, italienische an pwn@itpug.org.

== Angewandte Patches ==

Bruce Momjian committed:

- Mark TODO as done: "Improve display of enums to show valid enum
values."

- Mark TODO as done: "Add function to report the time of the most
recent server reload."

- Update libpqxx URL in README.

- Point to our download URL, rather than listing interface in the
README file:

- In pgsql/src/bin/psql/variables.c, have boolean pset values checked
against typical boolean values, rather than only 'off'.

- In TODO, add item, make text search section: "Consider changing
error to warning for strings larger than one megabyte."

- Update TODO to read: "Allow dictionaries to change the token that is
passed on to later dictionaries."

- Add URL for TODO: "Consider increasing the number of default
statistics target, and reduce statistics target overhead."

- Fixed TODO typo.

- In pgsql/doc/src/sgml/postgres.sgml, move Client Authentication
right after Server Configuration in docs. Scott Marlowe

- In pgsql/doc/src/sgml/ref/psql-ref.sgml, remove OID/foreign key
mention in psql variable interpolation docs.

- Add to TODO: "Add database and transaction-level triggers."

- Add to TODO: "Add CREATE SCHEMA ... LIKE that copies a schema."

- Update TODO wording to read: "Improve detection of shared memory
segments being used by others by checking the SysV shared memory
field 'nattch'"

- Have psql output tab as the proper number of spaces, rather than
\x09.

- In pgsql/src/backend/utils/adt/numeric.c, have numeric 0 ^ 4.3
return 1, rather than an error, and have 0 ^ 0.0 return 1, rather
than error. This was already the float8 behavior.

- Add psql '\pset format wrapped' mode to wrap output to screen width,
or file/pipe output too if \pset columns' is set. Bryce Nesbitt

- Add regression test for various power expressions with a zero base,
and adjust source code to be more modular.

- Add to TODO: "Improve the /contrib installation experience."

- Update C comments to mention SQL:2003 handling of power return
values.

- Adjust power() error messages to be more descriptive.

- Mark TODO as done: "Prevent parent tables from altering or dropping
constraints. Prevent child tables from altering or dropping
constraints."

- Add to TODO: "Clear table counters on TRUNCATE."

- Update alternative output regression files for new float8 test of
power().

- Add URL for TODO: "Add column to pg_stat_activity that shows the
progress of long-running commands like CREATE INDEX and VACUUM."

- Add to TODO: "Allow an existing index to be marked as a table's
primary key."

- Add URL for TODO: "Allow an existing index to be marked as a table's
primary key."

Magnus Hagander committed:

- In pgsql/src/interfaces/libpq/win32.mak, add more dependencies from
libpgport required by standalone msvc build of libpq. Hiroshi Saito.

- Make the pg_stat_activity view call a SRF (pg_stat_get_activity())
instead of calling a bunch of individual functions. This function
can also be called directly, taking a PID as an argument, to return
only the data for a single PID.

- Make the new pg_stat_get_activity use OUT parameters, so you don't
have to specify the column names and types. Also simplifies the
view. Per comments from Tom Lane.

Tom Lane committed:

- In pgsql/src/include/utils/syscache.h, convert the list of syscache
names from a series of #define's into an enum, to avoid the pain of
manually renumbering them anytime we insert another name in
alphabetical order. An excellent idea from Alex Hunsaker and
NikhilS' inherited-constraints patch --- whether or not the rest of
that gets in, this should. Dunno why we never thought of it before.

- In pgsql/src/backend/utils/cache/syscache.c, the CONSTROID syscache
should show conrelid as a relation OID column. Not clear that
there's any observable bug at present from this omission, but it
seems like something to fix going forward.

- Fix rules regression test for recent pg_stat_activity change.

- Fix contrib/xml2 makefile to not override CFLAGS, and in passing
make it auto-configure properly for libxslt present or not.

- Fix memory stomp that's turning the whole buildfarm pink: you can't
hack up pg_wcsformat without changing pg_wcssize to match. Add some
comments to try to make that clearer, and make a couple other minor
editorializations.

- Fix bogus expected output that should have made it quite clear that
something was wrong with that tab patch.

- In pgsql/contrib/pgbench/pgbench.c, give a warning if -s switch is
used with a non-custom pgbench test. Also, clean up the code that
assigned the scale into :scale variables. Greg Smith and Tom Lane

- Fix an ancient oversight in change_varattnos_of_a_node: it neglected
to update varoattno along with varattno. This resulted in having
Vars that were not seen as equal(), causing inheritance of the
"same" constraint from different parent relations to fail. An
example is:
create table pp1 (f1 int check (f1>0));
create table cc1 (f2 text, f3 int) inherits (pp1);
create table cc2(f4 float) inherits(pp1,cc1);
Backpatch as far as 7.4. (The test case still fails in 7.4, for
reasons that I don't feel like investigating at the moment.) This is
a backpatch commit only. The fix will be applied in HEAD as part of
the upcoming pg_constraint patch.

- Change the rules for inherited CHECK constraints to be essentially
the same as those for inherited columns; that is, it's no longer
allowed for a child table to not have a check constraint matching
one that exists on a parent. This satisfies the principle of least
surprise (rows selected from the parent will always appear to meet
its check constraints) and eliminates some longstanding bogosity in
pg_dump, which formerly had to guess about whether check constraints
were really inherited or not. The implementation involves adding
conislocal and coninhcount columns to pg_constraint (paralleling
attislocal and attinhcount in pg_attribute) and refactoring various
ALTER TABLE actions to be more like those for columns. Alex
Hunsaker, Nikhil Sontakke, Tom Lane

- In pgsql/src/bin/psql/print.c, restore psql's former behavior that
padding spaces to the right of the last output column are not
emitted. (That change already caused more noise in the regression
test output files than I would like.) Provide some needed editorial
help for comments, clean up code formatting.

Alvaro Herrera committed:

- In pgsql/doc/src/sgml/pgstandby.sgml, add Simon Riggs' email
address.

Heikki Linnakangas committed:

- Fix incorrect archive truncation point calculation in the %r
recovery_command parameter. This fixes bug 4137 reported by Wojciech
Strzalka, where a WAL file is deleted too early when starting the
recovery of a warm standby server. Also add a sanity check in
pg_standby so that it will refuse to delete anything earlier than
the file being restored, and improve the debug message in case
nothing is deleted. Simon Riggs. Backpatch to 8.3, which is where
%r was introduced.

- In pgsql/src/backend/access/transam/xlog.c, fix Assert introduced in
previous patch.

Peter Eisentraut committed:

- Add "%option noinput" to the scanners to avoid compiler warnings.
GCC 4.3 began to realize that the input() function isn't used and
printed warnings.

Andrew Dunstan committed:

- In pgsql/src/tools/msvc/Mkvcbuild.pm, improve logic for finding
object files on OBJS lines in contrib Makefiles. If this unbreaks
buildfarm mastodon, apply everywhere.

- In pgsql/src/tools/msvc/Mkvcbuild.pm, remaining pieces of fix for
contrib makefiles.

- In pgsql/src/tools/msvc/Mkvcbuild.pm, backpatch fixes for contrib
makefiles.

== Abgelehnte Patches (bis jetzt) ==

No one was disappointed this week :)

== Eingesandte Patches ==

Pavel Stehule sent in another revision of his patch to add CASE to
PL/PgSQL.

Alex Hunsaker sent in two more revisions of his patch for fixing the
problems with identifying constraints which should be inherited.

Heikki Linnakangas sent in another revision of his WIP Map Forks
patch.

Simon Riggs sent in a patch which adds --schema-pre-load and
--schema-post-load options both to pg_dump and pg_restore.

Magnus Hagander sent in a patch which adds some error checking to the
thread locking parts in libpq.

Bruce Momjian sent in two revisions of a patch to do something more
sane with tabs in psql output.

Pavel Stehule sent in another revision of his patch to add options to
PL/PgSQL's RAISE statement.

Brendan Jurd sent in another revision of his printTable API.

Bernd Helmle sent in another revision (via git) of his updateable
views patch.

Peter Eisentraut sent in a patch to fix flex warnings from GCC 4.3.

Jan Urbanski sent in a patch as infrastructure for his Summer of Code
project which changes the assumption that update_attstats() constructs
an array whose elements are of the same type as the corresponding
attribute.

Alvaro Herrera sent in another revision of his snapshot management
patch.

Hans-Juergen Schoenig sent in a patch intended to fix a performance
issue in concurrent scans.

David Gould sent in a patch to add a lock timeout option to pg_dump.

--
Andreas 'ads' Scherbaum
Deutsche PostgreSQL User Group: http://www.pgug.de
DPWN: http://ads.wars-nicht.de/blog/categories/18-PWN

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

[HACKERS] Stack depth exceeded error

Hello,

I have custom postgres code. I get the error below for the query

"select l_orderkey as a from tpcd.orders, tpcd.lineitem where o_orderkey=l_orderkey and l_partkey<100 and l_linestatus='F';"

ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth".

However, the same code runs fine with one condition in where clause, but fails with the error above in case of multiple conditions.

Whats the cause of this error ? I tried increasing the stack limit; but it doesnt help.

--
Suresh Iyengar

Be a better friend, newshound, and know-it-all with Yahoo! Mobile. Try it now.

[BUGS] BUG #4161: One useless czech translation in help of pg_ctl.exe

The following bug has been logged online:

Bug reference: 4161
Logged by: Pavel Cvrcek
Email address: jasnapaka@jasnapaka.com
PostgreSQL version: 8.3.1
Operating system: Windows Vista
Description: One useless czech translation in help of pg_ctl.exe
Details:

PostgreSQL 8.3.1 installed on Windows with czech locale. If I show help for
pg_ctl.exe (run pg_ctl.exe --help) I see help where is translated parameter
for unregister service.

It is line:
pg_ctl odregistrovanÃ [-N SERVICENAME]

Right line is:
pg_ctl unregister [-N SERVICENAME]

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

[pgsql-advocacy] ITPUG at PyCon2

Last weekend, the Italian PostgreSQL Users Group had a booth for two
days at the national conference of Python Users.

It's been an incredible opportunity to do some advocacy to over 300 IT
experts and to establish a collaboration with the Italian Python
Association.

I already posted an item on PlanetPostgreSQL but I also wish to post
another report from Gianluca Riccardi, ITPUG's member from Lazio (the
Region where Rome is):

http://moonwatcher.it/sp/4

This week, ITPUG will be at a national conference about free software
(Chris Mair) and I will finally ship some flyers and new stickers to
Kostas and the Greek Group. :)

Ciao,
Gabriele
--
Gabriele Bartolini: Open source programmer and data architect
Current Location: Prato, Tuscany, Italy
Associazione Italian PostgreSQL Users Group: www.itpug.org
gabriele.bartolini@gmail.com | www.gabrielebartolini.it
"If I had been born ugly, you would never have heard of Pelé", George Best
http://www.linkedin.com/in/gbartolini

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

Re: [GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:

hello,

most crucial information is missing, like:

- your operating system
- your postgresql version
- on which computer is your database server running
- is your database server running

But most likely your problem is that you did not configure PostgreSQL
to listen to TCP/IP-requests. Which again makes it most likely that
you are using a way outdated PostgreSQL Version (that ist < 8.x)

Please update to 8.3.1, and adjust your postgresql.conf, parameter

"listen_addresses (string)"

accordingly.

Best wishes,

Harald

On Mon, May 12, 2008 at 11:58 AM, Abdus Samad Ansari
<ansarias@wbpcb.gov.in> wrote:
> PHP Warning: pg_connect(): Unable to connect to PostgreSQL server:
> could not connect to server:
> I have setup PHP/Postgres and is running fine upto document root
> i.e. /var/www/html, but when i am calling it through a cgi-bin php file
> it is giving log error as :
> [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect
> to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF;
>
> what may be the solution.
>
> --Abdus Samad Ansari
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
>

http://www.postgresql.org/mailpref/pgsql-general
>

--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

--
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] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:

Hi,

Abdus Samad Ansari wrote:
> PHP Warning: pg_connect(): Unable to connect to PostgreSQL server:
> could not connect to server:
> I have setup PHP/Postgres and is running fine upto document root
> i.e. /var/www/html, but when i am calling it through a cgi-bin php file
> it is giving log error as :
> [error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect
> to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF;
>
well there are a couple of checks you could do to find out:

1) decide how to connect - via IP or via unix socket
2) in each of the above see if postmaster is indeed listening to that
socket (netstat is your friend)
- adjust postgresql.conf and/or start options accordingly.
depending on your distribution you might want to enable logging
3) try connection via psql command line client
- see if you have matching pg_hba.conf entries
4) try to connect via psql command line client with the same user your
script runs (apache in your case)
- same as (3) but ident vs. md5 (password) in pg_hba.conf

after all you might want to use some pooling layer because pg_connect
from cgi can get very expensive (also note that outside of PHP there
are a lot possible elegant solutions to that)

Tino

Re: [pgsql-advocacy] We're in the LWE-SF mailer!

Josh,
I'd be happy to be on the committee

Derek

Derek M. Rodner
Director, Marketing
EnterpriseDB Corporation
732.331.1333 office
484.252.1943 cell
www.enterprisedb.com
-----Original Message-----
From: pgsql-advocacy-owner@postgresql.org
[mailto:pgsql-advocacy-owner@postgresql.org] On Behalf Of Josh Berkus
Sent: Saturday, May 10, 2008 3:55 PM
To: Joshua D. Drake
Cc: PostgreSQL Advocacy; SF Postgres
Subject: [pgsql-advocacy] We're in the LWE-SF mailer!

All:

Thanks to Josh Drake's efforts, the PostgreSQL Day San Francisco is in
the advertising mailer for LinuxWorldExpo San Francisco. This means
that it's just been advertised to about 100,000 people. Yaaay!

Anyway, that probably means its time to start planning this event. I'll

be putting it on the calendar and soliciting talks soon; who wants to be

on the event committee?

--Josh

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

Re: [HACKERS] bloated heapam.h

Alvaro Herrera wrote:
> Alvaro Herrera wrote:
>
>> Oops :-( I just noticed that I removed bufmgr.h from bufpage.h, which
>> is a change you had objected to previously :-(
>
> However, it seems the right fix is to move BufferGetPageSize and
> BufferGetPage from bufpage.h to bufmgr.h.

+1 It makes more sense.

> (Digging further, it seems like bufpage.h should also include transam.h
> in order to get TransactionIdIsNormal ... I start to wonder how many
> problems of this nature we have on our headers. Without having a way to
> detect whether the defined macros are valid, it seems hard to check
> programatically, however.)
>

I attached script which should check it. In first step it runs C preprocessor on
each header (postgres.h is included as well). The output from first step is
processed again with preprocessor and define.h is included. Define.h contains
"all" used macros in following format:

#define SIGABRT "NOT_EXPANDED_SIGABRT"

Main problem is how to generate define.h. I used following magic:

grep "^#define" `find . -name "*.h"` | cut -d" " -f 2 | cut -f 1 | cut -f 1 -d"("

but it generates some noise as well. Maybe some Perl or AWK magic should be better.

Zdenek

Re: [ADMIN] pg_dump 8.3 changes from 8.2

Denison Wright написа:
> <pgsql-admin@postgresql.org>
> Hi,
>
> I had a command that I used with Postgresql 8.2 that stopped working once I
> upgraded to 8.3.
> I am running Postgresql 8.3 on Windows Vista with the following basic
> configuration:
>
> - database: mydb
> - user: mydbuser
> - password: mydbpassword
> - schema:myschema
> - table: mytable
>
> When I run the following commands:
>
>> set PGPASSWORD=mydbpassword
>> pg_dump mydb -U mydbuser -a -f backup.sql -F plain -t 'myschema.mytable'

Put the DB name at the end of the command line, e.g.
pg_dump -U mydbuser -a -f backup.sql -F plain -t 'myschema.mytable' mydb

>
> I get the following error now: "pg_dump: too many command-line arguments"

[...]

--
Milen A. Radev

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

[BUGS] BUG #4160: I get this error

The following bug has been logged online:

Bug reference: 4160
Logged by: srinivasulu
Email address: srinivasulu.dagda@gmail.com
PostgreSQL version: 8.2
Operating system: Windows-xp service pack2
Description: I get this error
Details:

I get this error when i run this program:CREATE TABLESPACE QUENCHDATA
LOCATION 'D:/programs/PostgreSQL/8.2/data/Quenchdata';

Error is:could not set permissions on directory
"D:/programs/PostgreSQL/8.2/data/Quenchdata": No such file or directory
SQL state: 58P01

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

Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Re: [pgsql-de-allgemein] Die Optimale Tabellenstruktur für Postgres 8.1-8.3 ?

rudi@je-more.de <rudi@je-more.de> schrieb:
>>> Möglichkeit für Volltextindizierbare Dokumente möglich ist, aber auf
>>> ein Erstrelease will ich
>>> mich da einfach nicht verlassen ;d Der Lese und Darstellungsspeed bei
>>>
>>
>> Das ist, sorry, Bullshit. Die Volltextsuche ist seit 8.3 im Core-System
>> mit drin, früher war es als Modul verfügbar. Ein Erstrelease ist das
>> somit definitiv nicht.
>>
> Obs nun nen Release vorher schon als Einzelkomponente da war oder nicht
> ist zweitrangig für mich.

Dann wird Dir auch egal sein, daß es das schon länger als ein Release
gibt. Egal.

>
>>> Blobinhalten sowie
>>> das gesammte drumherrum ist etwas das ORACLE schon lange kennt und relativ
>>> sicher abwickelt (ohne Krücken übers Filesystem mit Tempdateien
>>> u.s.w).
>>
>> Auch das ist ist nicht korrekt, denn auch Oracle speichert seine Daten
>> im Dateisystem.
>>
> ORACLE speichert aber keine teporären Objekte im Dateisystem um
> Blobinhalte in die Tablespaces
> schreiben zu können. Das geht schon deswegen nicht weil ORACLE sich auch
> komplett ohne ein
> Filesystem RAW auf einer unformatierten Festplatte installieren lässt.

Die Zeiten, wo RAW-Disk-Zugriff deutlich schneller als Filesystemzugriff
ist, liegt ebenfalls deutlich länger als ein Release zurück. Von daher
ist diese Diskussion, also bzgl. RAW-Disk-Zugriff, obsolet und im
übrigen schon mehr als einmal diskutiert worden.

> das Wort ergreifen. Adäquate Hilfe oder KnowHow lässt sich recht leicht
> besorgen oder
> nicht vorhandenes Wissen einkaufen!

Dann solltest Du aber noch etwas üben, das passende Medium für
kommerziellen ORA-Support zu finden. Hint: hier wohl nicht.

Wenn Du mit ORA so vertraut bist und es bevorzugst, was exakt willst Du
dann hier? Trollen?

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°

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

[GENERAL] PHP Warning: pg_connect(): Unable to connect to PostgreSQL server: could not connect to server:

PHP Warning: pg_connect(): Unable to connect to PostgreSQL server:
could not connect to server:
I have setup PHP/Postgres and is running fine upto document root
i.e. /var/www/html, but when i am calling it through a cgi-bin php file
it is giving log error as :
[error] [client 127.0.0.1] PHP Warning: pg_connect(): Unable to connect
to PostgreSQL server: could not connect to server: \x04\xe6\xe3\xbfPF;

what may be the solution.

--Abdus Samad Ansari

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

Monday, May 12, 2008

Blog Archive