Re: [GENERAL] Clustering

[Shane Ambler]

There are a few options depending on your needs.

PGCluster, Slony and pgpool would be the main ones to look at.

More info on them can be found at http://pgfoundry.org

Searching for replication will find more results than cluster


On 20/9/2006 14:17, Samad, Alex [EMAIL PROTECTED] wrote:

 
 Hi
 
 Can somebody point me to some articles/how-to's on postgres clustering
 and maybe comparisons to MySQL.  (Recently saw an article on scaling
 MySQL - up to 16 nodes)
 
 Alex
 
 ---(end of broadcast)---
 TIP 6: explain analyze is your friend
 

-- 

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz


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

Re: [GENERAL] Initializing Datums for use with SPI_execute_plan

[Martijn van Oosterhout]

On Tue, Sep 19, 2006 at 04:00:43PM -0400, Jack Orenstein wrote:
 Can you provide some guidance (or point to some documentation) on how
 to manage memory? Is the idea that I should (must?) not pfree
 palloc'ed memory from Int64GetDatum, but I should free anything I
 allocate myself using palloc? Or not even that?

If you want the gory details, check out the mmgr README file.

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/mmgr/README?rev=1.9

However, except in fairly special circumstances (eg. index comparison
functions) you don't ever need to bother with pfree(). The memory will
all be freed at the right time.

The documentation on C functions and set-returning functions also has
info about when the context is reset.

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] vista

[Andrew Kelly]

On Tue, 2006-09-19 at 10:26 -0400, Tom Lane wrote:
 Naz Gassiep [EMAIL PROTECTED] writes:
  For something like Vista 
  compatibility, if you want to be taken seriously by anyone who uses 
  Windows (hands up anyone who knows a Windows user), scratch your own 
  itch is not really going to cut it, IMHO.
 
 I was responding to someone who is obviously a Windows beta tester and
 therefore presumably has more clue than the average Windows-oid ---
 asking for a patch didn't seem unreasonable.  But for arguments like the
 above, I will happily say apparently you've confused me with someone
 who gives a damn about Windows.
 
   regards, tom lane


Where can I buy a little plastic statue of Tom Lane for my dashboard?




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

Re: [GENERAL] vista

[Geoffrey]

Andrew Kelly wrote:

On Tue, 2006-09-19 at 10:26 -0400, Tom Lane wrote:

Naz Gassiep [EMAIL PROTECTED] writes:
For something like Vista 
compatibility, if you want to be taken seriously by anyone who uses 
Windows (hands up anyone who knows a Windows user), scratch your own 
itch is not really going to cut it, IMHO.

I was responding to someone who is obviously a Windows beta tester and
therefore presumably has more clue than the average Windows-oid ---
asking for a patch didn't seem unreasonable.  But for arguments like the
above, I will happily say apparently you've confused me with someone
who gives a damn about Windows.

regards, tom lane



Where can I buy a little plastic statue of Tom Lane for my dashboard?


You mean the one with the hula skirt?

--
Until later, Geoffrey

Those who would give up essential Liberty, to purchase a little
temporary Safety, deserve neither Liberty nor Safety.
 - Benjamin Franklin

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

[GENERAL] Strange database corruption with PostgreSQL 7.4.x on Debian Sarge

[Matthias . Pitzl]

Hello!

We're running the latest release of PostgreSQL 7.4.13 on a Debian Sarge
machine. Postgres has been compiled by oureselves.
We have a pretty big database running on this machine, it has about 6.4 GB
approximately. One table contains about 55 million rows.
Into this table we insert about 50 rows each day. Our problem is that
without any obvious reason the database gets corrupt. The messages we get
are:
invalid page header in block 437702 of relation 
We already have tried out some other versions of 7.4. On another machine
running Debian Woody with PotgreSQL 7.4.10 we don't have any problems.
Kernels are 2.4.33 on the Sarge machine, 2.4.28 on the Woody machine. Both
are SMP kernels.
Does anyone of you perhaps have some hints what's going wrong here?

Best regards,
Matthias

---(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] Strange database corruption with PostgreSQL 7.4.x on

[Scott Marlowe]

On Wed, 2006-09-20 at 14:34 +0200, [EMAIL PROTECTED] wrote:
 Hello!
 
 We're running the latest release of PostgreSQL 7.4.13 on a Debian Sarge
 machine. Postgres has been compiled by oureselves.
 We have a pretty big database running on this machine, it has about 6.4 GB
 approximately. One table contains about 55 million rows.
 Into this table we insert about 50 rows each day. Our problem is that
 without any obvious reason the database gets corrupt. The messages we get
 are:
 invalid page header in block 437702 of relation 
 We already have tried out some other versions of 7.4. On another machine
 running Debian Woody with PotgreSQL 7.4.10 we don't have any problems.
 Kernels are 2.4.33 on the Sarge machine, 2.4.28 on the Woody machine. Both
 are SMP kernels.
 Does anyone of you perhaps have some hints what's going wrong here?

Most likely causes in these cases tends to be, bad memory, bad hard
drive, bad cpu, bad RAID / IDE / SCSI controller, loss of power when
writing to IDE drives / RAID controllers with cache with no battery
backup.

I.e. check your hardware.

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

Re: [GENERAL] postgresql rising

[AgentM]

On Sep 19, 2006, at 23:57 , Merlin Moncure wrote:


I have seen a steady progressive rise in the number of postgresql
related jobs and the quality of those jobs.   Major companies are
apparently rolling out critical infrastructure on postgresql...Vonage
is one example:
(http://jobsearch.monster.com/getjob.asp?JobID=47975237AVSDM=2006% 
2D09%2D15+13%3A07%3A10Logo=1JobTitle=PostgreSQL+Databa%2E%2E% 
2Eq=postgresqlcy=usJSNONREG=1Image1.x=0Image1.y=0dcjvlid=380).

Salaries for a capable pg dba are really attractive, I have seen
several in the 6 figure range.  If you are reading this list and you
like making money, this is amazing news folks.  I am seeing a
confluence of many factors leading to serious penetration into the
enterprise market.

Around 5 years ago after being mostly a c/c++ developer I decided
postgresql was where it was at.  Learning the database and becoming
productive with it has been professionally rewarding on many levels.
It's really exciting watching the community evolve.


I have noticed the same. One thing you didn't mention is how  
postgresql gets into such companies. I highly doubt there is a new  
general managerial acceptance of postgresql itself- I haven't had any  
of my management mention it from management magazines- rather it  
seems to be a grassroots effort by developers who started out using a  
free LAMP stack, know the benefits, and then bring that experience to  
the workplace.


-M

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

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x o

[Matthias . Pitzl]

Hello Scott!

Thank you for the quick answer. I'll try to check our hardware which is a
Compaq DL380 G4 with a batteyr buffered write cache on our raid controller.
As the system is running stable at all i think it's not the cpu or memory. 
At moment i tend more to a bad disk or SCSI controller but even with that i
don't get any message in my logs...
Any ideas how i could check the hardware?

Best regards,
Matthias

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe
 Sent: Wednesday, September 20, 2006 2:56 PM
 To: [EMAIL PROTECTED]
 Cc: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Strange database corruption with 
 PostgreSQL 7.4.x on
 
 
 On Wed, 2006-09-20 at 14:34 +0200, [EMAIL PROTECTED] wrote:
  Hello!
  
  We're running the latest release of PostgreSQL 7.4.13 on a 
 Debian Sarge
  machine. Postgres has been compiled by oureselves.
  We have a pretty big database running on this machine, it 
 has about 6.4 GB
  approximately. One table contains about 55 million rows.
  Into this table we insert about 50 rows each day. Our 
 problem is that
  without any obvious reason the database gets corrupt. The 
 messages we get
  are:
  invalid page header in block 437702 of relation 
  We already have tried out some other versions of 7.4. On 
 another machine
  running Debian Woody with PotgreSQL 7.4.10 we don't have 
 any problems.
  Kernels are 2.4.33 on the Sarge machine, 2.4.28 on the 
 Woody machine. Both
  are SMP kernels.
  Does anyone of you perhaps have some hints what's going wrong here?
 
 Most likely causes in these cases tends to be, bad memory, bad hard
 drive, bad cpu, bad RAID / IDE / SCSI controller, loss of power when
 writing to IDE drives / RAID controllers with cache with no battery
 backup.
 
 I.e. check your hardware.
 
 ---(end of 
 broadcast)---
 TIP 6: explain analyze is your friend
 

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

[GENERAL] Memory efficient insertion/retrieval of bytea

[Tomasz Ostrowski]

I'd like to propose something which would make an easy way for memory
efficient insertion/retrieval of bytea data.

Now for inserting data to a bytea we need 5*data_size in a buffer for
escaped data representation. 6*data_size if we do PQescapeByteaConn
in one chunk. I've made some experiments with attached program and
came up that for inserting 1kB of data this program needs about
5120kB of virtual memory, 1MB - 10MB, 10MB - 55MB. This is a lot for
a low-end workstation, too much for embedded devices.

We can use large objects for this but:
- large objects do not have any security;
- it is hard to ensure data integrity - AFAIK it is not possible to
  disallow deletion of large object if there are references to it.

So we need a function that takes an oid of a large object as an
argument and returns bytea, so a client application could do for
example:
Oid oid = lo_import(conn, filename);
PQexec(insert into tablename values (bytea_from_lo(%d)), oid);
lo_unlink(oid);

For retrieval it could be possible to do something like this:
PQexec(select lo_from_bytea(columnname) as oid from tablename where 
...);
And then:
lo_export(conn, oid, filename);
lo_unlink(oid);

This bytea_from_lo and lo_from_bytea functions could be very
efficient for bytea's - and will use much less memory and run on an
easily upgradeable server not client. And the basic versions should
be easy to implement for an experienced PostgreSQL hacker.


The hassle of unlinking this large objects could be eliminated by
introducing a temporary large object. This would be objects which
live for example only until end of session. Or just use
contrib/vacuum_lo.


This would also solve Allow read/write into TOAST values like large
objects TODO entry.

So - what do you think about this?

Regards
Tometzky
-- 
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
  Winnie the Pooh

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

Re: [GENERAL] Memory efficient insertion/retrieval of bytea

[Tomasz Ostrowski]

On Wed, 20 Sep 2006, Tomasz Ostrowski wrote:

 I've made some experiments with attached program and
 came up that for inserting 1kB of data this program needs about
 5120kB of virtual memory, 1MB - 10MB, 10MB - 55MB.

Forgot to attach it.

Pozdrawiam
Tometzky
-- 
Best of prhn - najzabawniejsze teksty polskiego UseNet-u
http://prhn.dnsalias.org/
  Chaos zawsze pokonuje porządek, gdyż jest lepiej zorganizowany.
  [ Terry Pratchett ]
#include libpq-fe.h
#include sys/types.h
#include sys/stat.h
#include fcntl.h
#include unistd.h
#include stdio.h
#include stdlib.h
#include string.h
#include assert.h

int main(int argc, char* argv[]) {
int data_fd;
char* query_buf;
unsigned char* data_cache;
size_t query_buf_len, query_buf_off;
ssize_t readbytes;

assert(argc == 2);

PGconn* pgconn = PQconnectdb();
if ( PQstatus(pgconn) != CONNECTION_OK ) { fprintf(stderr, Cannot 
connect: %s\n, PQerrorMessage(pgconn)); exit(-1); };

if ( ( data_fd = open(argv[1],O_RDONLY) ) == -1 ) { perror(Cannot open 
data file); exit(-1); };

/* allocate query buffer */
#define QUERYSTART create temporary table test (data bytea); insert 
into test values ('
#define QUERYEND ')
{
struct stat statbuf;
if ( fstat(data_fd, statbuf) == -1 ) { perror(Cannot stat 
data file); exit(-1); };
/* query_buf_len = maximum needed size of query buffer */
query_buf_len = 
statbuf.st_size*5+sizeof(QUERYSTART)+sizeof(QUERYEND);
}
if ( ! (query_buf = malloc(query_buf_len)) ) { perror(NULL); exit(-1); 
};

/* fill query buffer with QUERYSTART without null termination */
memcpy(query_buf, QUERYSTART, sizeof(QUERYSTART)-1);
query_buf_off = sizeof(QUERYSTART)-1; /* Offset in the query buffer */

/* allocate cache for file data */
#define CACHESIZE (0x7fff) /* minimum POSIX limit for read cache size */
if ( ! ( data_cache = malloc(CACHESIZE) ) ) { perror(NULL); exit(-1); };

/* read file */
while ( ( readbytes = read(data_fd, data_cache, CACHESIZE) )  0 ) {
size_t esc_data_cache_len;
unsigned char* esc_data_cache;

/* escape data */
if ( ! ( esc_data_cache = PQescapeByteaConn(pgconn, data_cache, 
readbytes, esc_data_cache_len) ) ) { fprintf(stderr, Cannot escape data: 
%s\n, PQerrorMessage(pgconn)); exit(-1); };

/* copy escaped data without null termination to query buffer */
assert( query_buf_off + esc_data_cache_len  query_buf_len );
memcpy(query_buf+query_buf_off, esc_data_cache, 
esc_data_cache_len-1);
query_buf_off += esc_data_cache_len-1;

/* free escaped data */
PQfreemem(esc_data_cache);
}
if ( readbytes == -1 ) { perror(Cannot read from data file); 
exit(-1); }

/* deallocate cache for file data */
free(data_cache);

/* fill query buffer with QUERYEND with null termination */
assert(query_buf_off+sizeof(QUERYEND) = query_buf_len);
memcpy(query_buf+query_buf_off, QUERYEND, sizeof(QUERYEND));

/* fprintf(stderr, %s\n, query_buf); */

PQexec(pgconn, query_buf);

/* deallocate query buffer */
free(query_buf);

PQfinish(pgconn);

return 0;
}

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

   http://archives.postgresql.org

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x o n

[Scott Marlowe]

On Wed, 2006-09-20 at 15:14 +0200, [EMAIL PROTECTED] wrote:
 Hello Scott!
 
 Thank you for the quick answer. I'll try to check our hardware which is a
 Compaq DL380 G4 with a batteyr buffered write cache on our raid controller.
 As the system is running stable at all i think it's not the cpu or memory. 
 At moment i tend more to a bad disk or SCSI controller but even with that i
 don't get any message in my logs...
 Any ideas how i could check the hardware?

Keep in mind, a single bad memory location is all it takes to cause data
corruption, so it could well be memory.  CPU is less likely if the
machine is otherwise running stable.

The standard tool on x86 hardware is memtest86 www.memtest86.com

So, you'd have to schedule a maintenance window to run the test in since
you have to basically down the machine and run just memtest86.  I think
a few live linux distros have it built in (FC has a memtest label in
some versions I think)

My first suspicion is always memory.  We ordered a batch of memory from
a very off brand supplier, and over 75% tested bad.  And it took 24
hours to find some of the bad memory.

good luck with your testing, let us know how it goes.

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

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x o

[Matthias . Pitzl]

Hello Scott!

Thank you. Memtest86 i know. I think we will use this for testing our
hardware too.
Got some other nice information meanwhile from someone also running a DL380
server which had a defect backplane causing similar issues.
He also gave me the hint that there's a test suite CD by Compaq to run some
hardware diagnostic checks on our machine. I will try this out as soon as
possible.
I will inform you when i know more :)

-- Matthias

 -Original Message-
 From: Scott Marlowe [mailto:[EMAIL PROTECTED] 
 Sent: Wednesday, September 20, 2006 4:12 PM
 To: [EMAIL PROTECTED]
 Cc: pgsql-general@postgresql.org
 Subject: RE: [GENERAL] Strange database corruption with 
 PostgreSQL 7.4.x o n
 
 Keep in mind, a single bad memory location is all it takes to 
 cause data
 corruption, so it could well be memory.  CPU is less likely if the
 machine is otherwise running stable.
 
 The standard tool on x86 hardware is memtest86 www.memtest86.com
 
 So, you'd have to schedule a maintenance window to run the 
 test in since
 you have to basically down the machine and run just 
 memtest86.  I think
 a few live linux distros have it built in (FC has a memtest label in
 some versions I think)
 
 My first suspicion is always memory.  We ordered a batch of 
 memory from
 a very off brand supplier, and over 75% tested bad.  And it took 24
 hours to find some of the bad memory.
 
 good luck with your testing, let us know how it goes.
 

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

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

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x o

[Tomasz Ostrowski]

On Wed, 20 Sep 2006, [EMAIL PROTECTED] wrote:

 Any ideas how i could check the hardware?

1. memtest86 or memtest86+ at least 8 hours

2. CPU Burn-in
http://users.bigpond.net.au/cpuburn/ at least 8 hours

3. badblocks -s -v -t random /dev/sd%
WARNING: this will destroy your data!

4. smartctl -a /dev/sd%
Does not have to work. Sometimes needs some hacking to make it work.

Dave KernelSlacker Jones wrote a very good article about hardware
problems:
http://people.redhat.com/davej/hardware-problems.txt

Regards
Tometzky
-- 
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
  Winnie the Pooh

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

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

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x on Debian Sarge

[Tom Lane]

[EMAIL PROTECTED] writes:
 invalid page header in block 437702 of relation 

I concur with Scott that this sounds suspiciously like a hardware
problem ... but have you tried dumping out the bad pages with
pg_filedump or even just od?  The pattern of damage would help to
confirm or disprove the theory.

You can find pg_filedump source code at
http://sources.redhat.com/rhdb/

regards, tom lane

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

Re: [GENERAL] Memory efficient insertion/retrieval of bytea

[Tom Lane]

Tomasz Ostrowski [EMAIL PROTECTED] writes:
 Now for inserting data to a bytea we need 5*data_size in a buffer for
 escaped data representation. 6*data_size if we do PQescapeByteaConn
 in one chunk.

If you send the value as an out-of-line binary parameter then you don't
need any of that.  See PQexecParams.

regards, tom lane

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

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

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x o

[Matthias . Pitzl]

Hello Tom!

Not yet, but i will try this one too. Anything special i should look for
when dumping out the bad pages?

-- Matthias

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane
 Sent: Wednesday, September 20, 2006 4:32 PM
 To: [EMAIL PROTECTED]
 Cc: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Strange database corruption with 
 PostgreSQL 7.4.x on Debian Sarge
 
 
 [EMAIL PROTECTED] writes:
  invalid page header in block 437702 of relation 
 
 I concur with Scott that this sounds suspiciously like a hardware
 problem ... but have you tried dumping out the bad pages with
 pg_filedump or even just od?  The pattern of damage would help to
 confirm or disprove the theory.
 
 You can find pg_filedump source code at
 http://sources.redhat.com/rhdb/
 
   regards, tom lane
 
 ---(end of 
 broadcast)---
 TIP 2: Don't 'kill -9' the postmaster
 

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

[GENERAL] pg_dump output containing CREATE TYPE does not restore with psql

[Harry Hehl]

I am using the following commands to dump and then restore a database. 

pg_dump -Fp ${PGDATABASE}  ${BACKUPFILE} 
psql --variable ON_ERROR_STOP=1 -f ${BACKUPFILE} 

The restore fails with:

psql:x:384: ERROR:  foreign key constraint accepttaskevent cannot be
implemented
DETAIL:  Key columns accepttaskevent and objectid are of
incompatible types: public.ds_uuid and public.ds_uuid.

The an pg_dump example output is shown below.  There seems to be a
problem with the search_path.
I have try several experiements with no success. 

Does any have an ideas on how to get around this issue?


(from Postgres 8.1.3 on linux.)

--
-- PostgreSQL database dump
--

SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;

--
-- Name: capsa; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA capsa;


ALTER SCHEMA capsa OWNER TO postgres;

--
-- Name: capsa_sys; Type: SCHEMA; Schema: -; Owner: postgres
--

CREATE SCHEMA capsa_sys;


ALTER SCHEMA capsa_sys OWNER TO postgres;

--
-- Name: SCHEMA public; Type: COMMENT; Schema: -; Owner: postgres
--

COMMENT ON SCHEMA public IS 'Standard public schema';


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

CREATE PROCEDURAL LANGUAGE plpgsql;


--
-- Name: plpythonu; Type: PROCEDURAL LANGUAGE; Schema: -; Owner: 
--

CREATE PROCEDURAL LANGUAGE plpythonu;

--
-- Name: ds_uuid_in(cstring); Type: FUNCTION; Schema: public; Owner:
postgres
--

CREATE FUNCTION ds_uuid_in(cstring) RETURNS ds_uuid
AS 'pgextensions.so', 'ds_uuid_in'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_in(cstring) OWNER TO postgres;

--
-- Name: ds_uuid_out(ds_uuid); Type: FUNCTION; Schema: public; Owner:
postgres
--

CREATE FUNCTION ds_uuid_out(ds_uuid) RETURNS cstring
AS 'pgextensions.so', 'ds_uuid_out'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_out(ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_recv(internal); Type: FUNCTION; Schema: public; Owner:
postgres
--

CREATE FUNCTION ds_uuid_recv(internal) RETURNS ds_uuid
AS 'pgextensions.so', 'ds_uuid_recv'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_recv(internal) OWNER TO postgres;

--
-- Name: ds_uuid_send(ds_uuid); Type: FUNCTION; Schema: public; Owner:
postgres
--

CREATE FUNCTION ds_uuid_send(ds_uuid) RETURNS bytea
AS 'pgextensions.so', 'ds_uuid_send'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_send(ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid; Type: TYPE; Schema: public; Owner: postgres
--

CREATE TYPE ds_uuid (
INTERNALLENGTH = 16,
INPUT = ds_uuid_in,
OUTPUT = ds_uuid_out,
RECEIVE = ds_uuid_recv,
SEND = ds_uuid_send,
ALIGNMENT = int4,
STORAGE = plain
);


ALTER TYPE public.ds_uuid OWNER TO postgres;

SET search_path = capsa, pg_catalog;

SET search_path = public, pg_catalog;

--
-- Name: ds_uuid_cmp(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION ds_uuid_cmp(ds_uuid, ds_uuid) RETURNS integer
AS 'pgextensions.so', 'ds_uuid_cmp'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_cmp(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_eq(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION ds_uuid_eq(ds_uuid, ds_uuid) RETURNS boolean
AS 'pgextensions.so', 'ds_uuid_eq'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_eq(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_ge(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION ds_uuid_ge(ds_uuid, ds_uuid) RETURNS boolean
AS 'pgextensions.so', 'ds_uuid_ge'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_ge(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_gt(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION ds_uuid_gt(ds_uuid, ds_uuid) RETURNS boolean
AS 'pgextensions.so', 'ds_uuid_gt'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_gt(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_le(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION ds_uuid_le(ds_uuid, ds_uuid) RETURNS boolean
AS 'pgextensions.so', 'ds_uuid_le'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_le(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_lt(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION ds_uuid_lt(ds_uuid, ds_uuid) RETURNS boolean
AS 'pgextensions.so', 'ds_uuid_lt'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_lt(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: ds_uuid_ne(ds_uuid, ds_uuid); Type: FUNCTION; Schema: public;
Owner: postgres
--

CREATE FUNCTION ds_uuid_ne(ds_uuid, ds_uuid) RETURNS boolean
AS 'pgextensions.so', 'ds_uuid_ne'
LANGUAGE c IMMUTABLE STRICT;


ALTER FUNCTION public.ds_uuid_ne(ds_uuid, ds_uuid) OWNER TO postgres;

--
-- Name: 

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x o

[Matthias . Pitzl]

Hello all!

Ok, i found out some more informations. According to
http://h2.www2.hp.com/bizsupport/TechSupport/Document.jsp?lang=encc=us;
taskId=110prodSeriesId=397634prodTypeId=15351prodSeriesId=397634objectID
=PSD_EX050119_CW01 one of our four disks in the server has a firmware issue.
The problem are incomplete writes onto disk while on high I/O load...
We will check this one first. If it won't help, we will try the hardware
diagnostics and some other tests...
Meanwhile thank you all for your suggestions :)

-- Matthias

 -Original Message-
 From: [EMAIL PROTECTED] 
 [mailto:[EMAIL PROTECTED] On Behalf Of 
 [EMAIL PROTECTED]
 Sent: Wednesday, September 20, 2006 3:14 PM
 To: [EMAIL PROTECTED]
 Cc: pgsql-general@postgresql.org
 Subject: Re: [GENERAL] Strange database corruption with 
 PostgreSQL 7.4.x o
 
 
 Hello Scott!
 
 Thank you for the quick answer. I'll try to check our 
 hardware which is a
 Compaq DL380 G4 with a batteyr buffered write cache on our 
 raid controller.
 As the system is running stable at all i think it's not the 
 cpu or memory. 
 At moment i tend more to a bad disk or SCSI controller but 
 even with that i
 don't get any message in my logs...
 Any ideas how i could check the hardware?
 
 Best regards,
 Matthias
 

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

[SeUr2000]

set LISTNAME digest

Re: [GENERAL] Strange database corruption with PostgreSQL 7.4.x o n Debian Sarge

[Tom Lane]

[EMAIL PROTECTED] writes:
 Not yet, but i will try this one too. Anything special i should look for
 when dumping out the bad pages?

If we knew what it was we would learn, it wouldn't be research ...

regards, tom lane

---(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] Getting prepared statement parameters in log when using JDBC with PG 8

[Kevin Murphy]

I'm hoping to see the parameters of prepared statements in the 
postgresql log when using a JDBC client against postgresql 8.1?


I saw a post  about using the V2 protocol.

Is there any downside to this?

Thanks,
Kevin

P.S.  Looking at the driver documentation, I see that I can append the 
protocol specification to the jdbc connect string, e.g.:


jdbc:postgresql://host:port?protocolVersion=2

I'll give that a try.


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

Re: [GENERAL] Initializing Datums for use with SPI_execute_plan

[Jack Orenstein]

On 9/20/06, Martijn van Oosterhout kleptog@svana.org wrote:

On Tue, Sep 19, 2006 at 04:00:43PM -0400, Jack Orenstein wrote:
 Can you provide some guidance (or point to some documentation) on how
 to manage memory? Is the idea that I should (must?) not pfree
 palloc'ed memory from Int64GetDatum, but I should free anything I
 allocate myself using palloc? Or not even that?

If you want the gory details, check out the mmgr README file.

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/mmgr/README?rev=1.9


Thank you, that is a useful document. So let me make sure I understand:

- My C function is invoked in a MessageContext (which you referred to
in an earlier message as a short-lived context).

- In such a context, I do not need to pfree anything, including memory
that I palloc myself, and plans returned by SPI_prepare invocations,
(assuming that I don't hold onto these things past the function
invocation, e.g. in a static).

Is that correct?

Jack

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

Re: [GENERAL] Memory efficient insertion/retrieval of bytea

[Tomasz Ostrowski]

On Wed, 20 Sep 2006, Tom Lane wrote:

 Tomasz Ostrowski [EMAIL PROTECTED] writes:
  Now for inserting data to a bytea we need 5*data_size in a buffer for
  escaped data representation. 6*data_size if we do PQescapeByteaConn
  in one chunk.
 
 If you send the value as an out-of-line binary parameter then you don't
 need any of that.  See PQexecParams.

Nice. I've overlooked this.

This still needs 1*data_size for a buffer though. Much better but
sometimes not enough.

Regards
Tometzky
-- 
...although Eating Honey was a very good thing to do, there was a
moment just before you began to eat it which was better than when you
were...
  Winnie the Pooh

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

Re: [GENERAL] postgresql rising

[Tony Caduto]

Merlin Moncure wrote:

I have seen a steady progressive rise in the number of postgresql
related jobs and the quality of those jobs.   Major companies are
apparently rolling out critical infrastructure on postgresql...Vonage
is one example:

That is good news, I wish there where some of those Postgresql jobs in 
the Milwaukee area :-)

Don't want to move to NJ :-(

It does seem to be a grass roots kind of thing as the major corp 
managers have no clue what Postgresql is.
For a high level corp manager all they ever hear about is MS SQL Server, 
Oracle and DB2, and the more it costs the more they think it is what 
they need :-)


--
Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration 



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

  http://archives.postgresql.org

Re: [GENERAL] Initializing Datums for use with SPI_execute_plan

[Martijn van Oosterhout]

On Wed, Sep 20, 2006 at 11:02:46AM -0400, Jack Orenstein wrote:
 http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/mmgr/README?rev=1.9
 
 Thank you, that is a useful document. So let me make sure I understand:
 
 - My C function is invoked in a MessageContext (which you referred to
 in an earlier message as a short-lived context).
 
 - In such a context, I do not need to pfree anything, including memory
 that I palloc myself, and plans returned by SPI_prepare invocations,
 (assuming that I don't hold onto these things past the function
 invocation, e.g. in a static).

In general, any memory you allocate in a function will have been free'd
by the next time you're called. If you want memory to survive (like in
a static) you need to place it in a longer lived context.

The SPI reference has some details about how long various bits of
memory survive.

It's not a garbage collector, everything in a context goes when the
context is reset or deleted. If you still have a pointer there, it'll
be invalid. If you enable debugging stuff it'll clear freed memory so
the error becomes more obvious.

Hope this helps,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

Re: [GENERAL] pg_dump output containing CREATE TYPE does not restore with psql

[Tom Lane]

Harry Hehl [EMAIL PROTECTED] writes:
 The restore fails with:

 psql:x:384: ERROR:  foreign key constraint accepttaskevent cannot be
 implemented
 DETAIL:  Key columns accepttaskevent and objectid are of
 incompatible types: public.ds_uuid and public.ds_uuid.

This seems to be a variant of the problem discussed here:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00284.php

I thought we had fixed it in 8.1.3, but apparently there's another
problem behind that one :-(.  Your best short-term answer may be to
create the uuid type and its operators in the pg_catalog schema.

regards, tom lane

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

   http://archives.postgresql.org

[GENERAL] Problems converting between C string and Datum

[Jack Orenstein]

Thanks for all your help with the memory management problems. Next
problem: I'm having problems converting from a char* to a Datum and back
again.

I have a char* which I need as a Datum, for use with a plan returned
from SPI_prepare, so I'm doing this:

   char* string;
   Datum d;
   ...
   d = DirectFunctionCall1(textin, CStringGetDatum(string));
   ...
   ereport(WARNING, (errmsg(string: %s,

DatumGetCString(DirectFunctionCall1(textout, d);

(The ereport is for debugging -- my code then proceeds to call
SPI_execute_plan.)

This crashes doing the ereport:

   psql:test.sql:23: server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.

So my questions are:

1) Is this code the way to convert char* to Datum and back again?

   DirectFunctionCall1(textin, CStringGetDatum(string))
   ...
   DatumGetCString(DirectFunctionCall1(textout, d))

I've put this code together based on examples I've found on the web
and in the postgres source.

2) Is there some neater way to generate debug output than ereport(WARNING, ...)?
The output is quite verbose, e.g.

   psql:test.sql:23: WARNING:  MY DEBUG OUTPUT
   CONTEXT:  SQL statement insert into log select insert_ifs(  $1 ,
$2 ,  $3 ,  $4 ,  $5 ,  $6 ,  $7 )
   PL/pgSQL function regress line 5 at SQL statement

for each line of output.


Jack Orenstein

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

[GENERAL] 8.2: select from an INSERT returning?

[Jeff Davis]

I was trying to use an INSERT ... RETURNING as a subselect in 8.2. Is
that possible?

jdavis=# create table foo(i int);
CREATE TABLE
jdavis=# insert into foo(i) values(1) returning i;
 i
---
 1
(1 row)

INSERT 0 1
jdavis=# select * from (insert into foo(i) values(1) returning i) t;
ERROR:  syntax error at or near into
LINE 1: select * from (insert into foo(i) values(1) returning i) t;
  ^

If not, is there a reason it shouldn't be allowed, or is that a possible
feature for 8.3?

Also, why no GROUP BY or aggregate functions?

I was interested in using the RETURNING clause in place of using
PQcmdTuples() to get information about what was inserted. I don't think
there's any way for a function to modify what is returned by
PQcmdTuples, right?

Regards,
Jeff Davis



---(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] Problems converting between C string and Datum

[Tom Lane]

Jack Orenstein [EMAIL PROTECTED] writes:
 I have a char* which I need as a Datum, for use with a plan returned
 from SPI_prepare, so I'm doing this:

 char* string;
 Datum d;
 ...
 d = DirectFunctionCall1(textin, CStringGetDatum(string));
 ...
 ereport(WARNING, (errmsg(string: %s,
 DatumGetCString(DirectFunctionCall1(textout, d);

That looks OK as far as it goes, so I speculate the problem is in
something you didn't show us.

 This crashes doing the ereport:

If you are doing backend C code you should certainly learn how to use
gdb to narrow things down more than that.  A stack trace would be much
more informative than server closed the connection unexpectedly.

regards, tom lane

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

Re: [GENERAL] 8.2: select from an INSERT returning?

[Tom Lane]

Jeff Davis [EMAIL PROTECTED] writes:
 I was trying to use an INSERT ... RETURNING as a subselect in 8.2. Is
 that possible?

No.

regards, tom lane

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

   http://archives.postgresql.org

Re: [GENERAL] pg_dump output containing CREATE TYPE does not restore with psql

[Tom Lane]

I wrote:
 Harry Hehl [EMAIL PROTECTED] writes:
 The restore fails with:

 psql:x:384: ERROR:  foreign key constraint accepttaskevent cannot be
 implemented
 DETAIL:  Key columns accepttaskevent and objectid are of
 incompatible types: public.ds_uuid and public.ds_uuid.

 This seems to be a variant of the problem discussed here:
 http://archives.postgresql.org/pgsql-hackers/2006-02/msg00284.php

Actually, it's exactly the problem discussed in this thread:
http://archives.postgresql.org/pgsql-hackers/2006-02/msg00960.php

We had more or less arrived at a consensus about what to do about it,
but nothing ever actually got done, and I fear it's too late now for
8.2 :-(

regards, tom lane

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

Re: [GENERAL] postgresql rising

[David Fetter]

On Wed, Sep 20, 2006 at 09:05:00AM -0400, AgentM wrote:
 On Sep 19, 2006, at 23:57 , Merlin Moncure wrote:
 
 I have seen a steady progressive rise in the number of postgresql
 related jobs and the quality of those jobs.   Major companies are
 apparently rolling out critical infrastructure on postgresql
 
 I have noticed the same.  One thing you didn't mention is how
 postgresql gets into such companies.  I highly doubt there is a new
 general managerial acceptance of postgresql itself- I haven't had
 any  of my management mention it from management magazines- rather
 it  seems to be a grassroots effort by developers who started out
 using a  free LAMP stack, know the benefits, and then bring that
 experience to  the workplace.

That's one stage, and I thing we're getting past it.  Now, management
is enthusiastic to have FOSS OSs like Linux and FreeBSD as server OSs,
and they're coming around, company by company, to the idea that this
FOSS stuff applies to server software in general.

Even in client-side software, people don't look at you as though
you're insane when you say you're using Firefox or OOo :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

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

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

Re: [GENERAL] 8.2: select from an INSERT returning?

[David Fetter]

On Wed, Sep 20, 2006 at 01:42:59PM -0400, Tom Lane wrote:
 Jeff Davis [EMAIL PROTECTED] writes:
  I was trying to use an INSERT ... RETURNING as a subselect in 8.2. Is
  that possible?
 
 No.

What would be involved in making INSERT/UPDATE/DELETE ... RETURNING be
on the same level as other table-like things such as VALUES (...),
..., (...)?

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 415 235 3778AIM: dfetter666
  Skype: davidfetter

Remember to vote!

---(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] 8.2: select from an INSERT returning?

[Tom Lane]

David Fetter [EMAIL PROTECTED] writes:
 What would be involved in making INSERT/UPDATE/DELETE ... RETURNING be
 on the same level as other table-like things such as VALUES (...),
 ..., (...)?

Getting rid of their side-effects, which of course ain't happening.

The problem is the surrounding query might try to execute the command
multiple times ... or not at all ... and what would you like that to
mean?

regards, tom lane

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

Re: [GENERAL] 8.2: select from an INSERT returning?

[Jeff Davis]

On Wed, 2006-09-20 at 14:08 -0400, Tom Lane wrote:
 David Fetter [EMAIL PROTECTED] writes:
  What would be involved in making INSERT/UPDATE/DELETE ... RETURNING be
  on the same level as other table-like things such as VALUES (...),
  ..., (...)?
 
 Getting rid of their side-effects, which of course ain't happening.
 
 The problem is the surrounding query might try to execute the command
 multiple times ... or not at all ... and what would you like that to
 mean?
 

Wouldn't that be the same as a volatile set-returning function? As I
understand it, 8.2 introduced a feature to prevent a volatile function
from being executed more times than it is listed in the query.

Regards,
Jeff Davis


---(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] Problems converting between C string and Datum

[Martijn van Oosterhout]

On Wed, Sep 20, 2006 at 11:19:29AM -0400, Jack Orenstein wrote:
 Thanks for all your help with the memory management problems. Next
 problem: I'm having problems converting from a char* to a Datum and back
 again.
 
 I have a char* which I need as a Datum, for use with a plan returned
 from SPI_prepare, so I'm doing this:

snip

They look OK. For examples see the PG_STR_GET_TEXT() and
PG_TEXT_GET_STR() macros in utils/adt/varlena.c.

 This crashes doing the ereport:
 
psql:test.sql:23: server closed the connection unexpectedly
   This probably means the server terminated abnormally
   before or while processing the request.

Obviously something is going wrong. Perhaps you should enable coredumps
and use gdb to examine them, or attach gdb to the running backend to
catch the error.

 2) Is there some neater way to generate debug output than ereport(WARNING, 
 ...)?
 The output is quite verbose, e.g.

Try elog(), but the details are also controlled by the client also,
perhaps you can reduce the verbosity there also?

Have a nice day,
-- 
Martijn van Oosterhout   kleptog@svana.org   http://svana.org/kleptog/
 From each according to his ability. To each according to his ability to 
 litigate.


signature.asc
Description: Digital signature

[GENERAL] Selecting from two unrelated tables

[CSN]

I have two tables:

items: id, title, added, ...
news: id, headline, datetime, ...

I'd like to select the latest 25 combined records from both tables. Is there a 
way to do this
using just select?

Thanks,
csn

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

   http://archives.postgresql.org

Re: [GENERAL] Selecting from two unrelated tables

[Bricklen Anderson]

CSN wrote:

I have two tables:

items: id, title, added, ...
news: id, headline, datetime, ...

I'd like to select the latest 25 combined records from both tables. Is there a 
way to do this
using just select?

Thanks,
csn



Maybe something like this?

select id,title_headline,dt
from (
  select id,title as title_headline,added as dt from ...
  union all
  select id,headline as title_headline,datetime as dt from ...) as a
order by dt limit 25;

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

  http://archives.postgresql.org

Re: [GENERAL] Selecting from two unrelated tables

[John Sidney-Woollett]

You can use an union if the column types match

{...off the top of my head...}

select id, title, added as sortcol from table1
union
select id, headline, datetime as sortcol from table2
order by sortcol
limit 25;

John

CSN wrote:

I have two tables:

items: id, title, added, ...
news: id, headline, datetime, ...

I'd like to select the latest 25 combined records from both tables. Is there a 
way to do this
using just select?

Thanks,
csn

__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.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] postgresql rising

[Andrew Sullivan]

On Wed, Sep 20, 2006 at 10:10:56AM -0500, Tony Caduto wrote:
 For a high level corp manager all they ever hear about is MS SQL Server, 
 Oracle and DB2, and the more it costs the more they think it is what 
 they need :-)

I think that description is false.  At a certain point in the
management hierarchy, the only way anyone has the ability to evaluate
something is on the basis of reputation.  PostgreSQL is building its
reputation, but it doesn't have the marketing budget of those three. 
Therefore, it's safer to pick the thing that has a better reputation,
and that makes those reputations stronger still.  So what we need is
a spotless reputation -- which we're building.

A

-- 
Andrew Sullivan  | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell

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

   http://archives.postgresql.org

[GENERAL] Question Regarding a Temporary Table

[Terry Lee Tucker]

Greetings:

I have have a plpgsql function that creates a temporary table to facilitate 
some processing. Here is the code:
CREATE TEMP TABLE tmp (code VARCHAR,
   booked   INTEGER,
   availINTEGER,
   covered  INTEGER,
   profit   NUMERIC (10,2),
   billed   NUMERIC (10,2))
WITHOUT OIDS ON COMMIT DROP;

Note the ON COMMIT DROP. I would expect this table to disapear after the 
function completes, but it does not. Also, if I execute the the function 
twice in a row from the psql interface, on the second try, I get the 
following error:
sev=# select * from custSprtRpt('04/01/06', current_date);
NOTICE:  custSprtRpt ()
ERROR:  relation with OID 123654 does not exist
CONTEXT:  PL/pgSQL function custsprtrpt line 39 at SQL statement

If have to reload the function with \i sqlfunc/custSprtRpt.plsql so that it 
will work again. Why is this?

Also, this function does not perform any updates to a permanent database 
table.

Anyone have any insight into this issue?

sev=# select version();
   version
--
 PostgreSQL 7.4.6 on i686-redhat-linux-gnu, compiled by GCC gcc (GCC) 3.2.3 
20030502 (Red Hat Linux 3.2.3-49)
(1 row)

Thanks...
-- 
Terry Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 363-4719
[EMAIL PROTECTED]
www.turbocorp.com

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

Re: [GENERAL] Question Regarding a Temporary Table

[Jeff Davis]

On Wed, 2006-09-20 at 16:51 -0400, Terry Lee Tucker wrote:
 Greetings:
 
 I have have a plpgsql function that creates a temporary table to facilitate 
 some processing. Here is the code:
 CREATE TEMP TABLE tmp (code VARCHAR,
booked   INTEGER,
availINTEGER,
covered  INTEGER,
profit   NUMERIC (10,2),
billed   NUMERIC (10,2))
 WITHOUT OIDS ON COMMIT DROP;
 
 Note the ON COMMIT DROP. I would expect this table to disapear after the 
 function completes, but it does not. Also, if I execute the the function 

In 8.1 it does drop for me on COMMIT, as you would expect. I don't know
about 7.4.

 twice in a row from the psql interface, on the second try, I get the 
 following error:
 sev=# select * from custSprtRpt('04/01/06', current_date);
 NOTICE:  custSprtRpt ()
 ERROR:  relation with OID 123654 does not exist
 CONTEXT:  PL/pgSQL function custsprtrpt line 39 at SQL statement
 
 If have to reload the function with \i sqlfunc/custSprtRpt.plsql so that it 
 will work again. Why is this?

PL/pgSQL caches query plans. Unfortunately, there is currently no good
mechanism to invalidate the plans, and the function is using a stale
plan with an OID that no longer exists.

The workaround is to use EXECUTE in the function, and build the query
from a string. That prevents PL/pgSQL from caching the plan.

What confuses me is, if it didn't drop your table, why would it say the
oid doesn't exist?

Regards,
Jeff Davis




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

Re: [GENERAL] Question Regarding a Temporary Table

[Terry Lee Tucker]

Thanks for the reponse Jeff. See comments below.

On Wednesday 20 September 2006 05:09 pm, Jeff Davis [EMAIL PROTECTED] thus 
communicated:
-- On Wed, 2006-09-20 at 16:51 -0400, Terry Lee Tucker wrote:
--  Greetings:
-- 
--  I have have a plpgsql function that creates a temporary table to
 facilitate --  some processing. Here is the code:
--  CREATE TEMP TABLE tmp (code VARCHAR,
-- booked   INTEGER,
-- availINTEGER,
-- covered  INTEGER,
-- profit   NUMERIC (10,2),
-- billed   NUMERIC (10,2))
--  WITHOUT OIDS ON COMMIT DROP;
-- 
--  Note the ON COMMIT DROP. I would expect this table to disapear after
 the --  function completes, but it does not. Also, if I execute the the
 function --
-- In 8.1 it does drop for me on COMMIT, as you would expect. I don't know
-- about 7.4.
--
--  twice in a row from the psql interface, on the second try, I get the
--  following error:
--  sev=# select * from custSprtRpt('04/01/06', current_date);
--  NOTICE:  custSprtRpt ()
--  ERROR:  relation with OID 123654 does not exist
--  CONTEXT:  PL/pgSQL function custsprtrpt line 39 at SQL statement
-- 
--  If have to reload the function with \i sqlfunc/custSprtRpt.plsql so
 that it --  will work again. Why is this?
--
-- PL/pgSQL caches query plans. Unfortunately, there is currently no good
-- mechanism to invalidate the plans, and the function is using a stale
-- plan with an OID that no longer exists.
--
-- The workaround is to use EXECUTE in the function, and build the query
-- from a string. That prevents PL/pgSQL from caching the plan.
--
-- What confuses me is, if it didn't drop your table, why would it say the
-- oid doesn't exist?

Well, I was assuming that that the table wasn't being dropped and that was 
what was causing the error. I can see from your comments, that I was wrong on 
that asssumption. I can do this with and execute, but it's going to be a pain 
to acomplish. I wonder what good a temporary table is if you can't use the 
code which creates it twice in a row with reloading the function?

Anyway, thanks for the response...

--
-- Regards,
-- Jeff Davis
--
--
--
--
-- ---(end of broadcast)---
-- TIP 6: explain analyze is your friend
--

-- 
Terry Tucker
Turbo's IT Manager
Turbo, division of Ozburn-Hessey Logistics
2251 Jesse Jewell Pkwy NE
Gainesville, GA 30501
Tel: (336) 372-6812  Fax: (336) 372-6812  Cell: (336) 363-4719
[EMAIL PROTECTED]
www.turbocorp.com

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

Re: [GENERAL] Question Regarding a Temporary Table

[Jeff Davis]

On Wed, 2006-09-20 at 17:29 -0400, Terry Lee Tucker wrote:
 Well, I was assuming that that the table wasn't being dropped and that was 
 what was causing the error. I can see from your comments, that I was wrong on 
 that asssumption. I can do this with and execute, but it's going to be a pain 
 to acomplish. I wonder what good a temporary table is if you can't use the 
 code which creates it twice in a row with reloading the function?
 

Well, the problem is not with temporary tables so much as the cached
plans. PL/pgSQL decides when the function is first run that the
temporary table you're using has OID 123654 (or whatever), and rather
than using the table name on the function call, it assumes that the OID
has not changed.

So, a temporary table is still useful for any situation where it doesn't
cache the query plan (like a normal query, or an EXECUTE inside
PL/pgSQL).

But yes, it is frustrating, and will hopefully be fixed in later
versions.

Regards,
Jeff Davis


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

Re: [GENERAL] postgresql rising

[Philip Hallstrom]

On Wed, Sep 20, 2006 at 10:10:56AM -0500, Tony Caduto wrote:

For a high level corp manager all they ever hear about is MS SQL Server,
Oracle and DB2, and the more it costs the more they think it is what
they need :-)


I think that description is false.  At a certain point in the
management hierarchy, the only way anyone has the ability to evaluate
something is on the basis of reputation.


I think that description is false.  At a certain point in the management 
hierarchy, the only way anyone has the ability to evaluate something is on 
the basis of


- if there is someone they can sue.
- how attractive the sales rep is.
- how much swag the sales rep brings with them.

:-/

Sadly, I once worked for a company that spent close to $500K on a 
commercial product when PHP would have worked just as well...  I did make 
sure I wrote a very very long CYA email myself so when someone asked why 
that decision was made they wouldn't look at me :)



PostgreSQL is building its
reputation, but it doesn't have the marketing budget of those three.
Therefore, it's safer to pick the thing that has a better reputation,
and that makes those reputations stronger still.  So what we need is
a spotless reputation -- which we're building.

A

--
Andrew Sullivan  | [EMAIL PROTECTED]
The whole tendency of modern prose is away from concreteness.
--George Orwell

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

  http://archives.postgresql.org



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

Re: [GENERAL] plz xxxxxxxxxxx me

[Michelle Konzack]

Am 2006-09-18 10:23:21, schrieb Csaba Nagy:

 The point was that you will never ever be able to cover all the variants
 existing in the weirdest email clients... however hard you try. You
 missed Hungarian which is my native language for example ;-)

But such things can be added later...

My strings hit nearly ALL *subscribe mails.

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant


-- 
Linux-User #280138 with the Linux Counter, http://counter.li.org/
# Debian GNU/Linux Consultant #
Michelle Konzack   Apt. 917  ICQ #328449886
   50, rue de Soultz MSM LinuxMichi
0033/6/6192519367100 Strasbourg/France   IRC #Debian (irc.icq.com)


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

Re: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?

[Najib Abi Fadel]

I have a web application that is accessed by a large number of users. My application is written in PHP and uses postgres. Apache is our web server.The performance of my application drops down when a large numbers of users connects at the same time. I need to have a better response time ! That's why i need to load balance the web requests and the database.Regards,Najib.Ben Trewern [EMAIL PROTECTED] wrote: The solution you need all depends on the problem you are having.  If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better answer!Regards,Ben"Najib Abi Fadel" <[EMAIL PROTECTED]> wrote in message
 news:[EMAIL PROTECTED]Robin Ericsson <[EMAIL PROTECTED]> wrote:On 9/18/06, Najib Abi Fadel wrote: Hi, i was searching for a load balancing solution for postgres, I found some ready to use software like PGCluster, Slony, pgpool and others. It would really be nice if someone knows which one is the best taking in consideration that i have an already running application that i need to load balance.There isn't one tool that is the best, all three work very good basedon where they are used and what they are used for.-- regards,Robin---(end of broadcast)---TIP 5: don't forget to increase your free space map settingsDid you try them or have any experience with them. I need them for load balancing my database and thus making the queries faster. I
 have a web application heavely using a postgres database. Hundreds of users can connect at the same time to my web application.Thanks in advance for any help.Najib.How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. ---(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 
		Get your email and more, right on the  new Yahoo.com 

Re: [GENERAL] Access to databas from the Internet

[Lukasz]

Shane Ambler napisal(a):
 On 19/9/2006 22:41, Lukasz [EMAIL PROTECTED] wrote:

  Hello,
 
  I would like to install a PostgreSQL. I know how to manage the database
  itself, creae databases, user, groups and so on. But I don't know how
  to allow other users, who are outside LAN to connect to database
  through Internet.
 
  For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local
  network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the
  computer with yyy.yyy... What and where I need to configure to have
  access to my database from Internet?


 I will assume that you want to allow normal psql client access and not
 through a web server.

 There is two places you will need to configure.

 One is your router - you will need to setup port forwarding . The default
 port for connecting to the PostgreSQL server is 5432 so the router will need
 to forward any incoming requests on tcp port 5432 to tcp port 5432  at
 server address yyy.yyy.yyy.yyy (your PostgreSQL server address)
 If you have configured a different port then adjust accordingly.
 This is a common configuration option and shouldn't be hard to find.

 Second you will need to configure PostgreSQL to accept connections from
 outside your network. This is done in pg_hba.conf which is in your data
 folder by default.
 If you currently connect to the server from another machine on your network
 you will have a line similar to
 hostall all yyy.yyy.yyy.yyy/24  md5

 To allow PostgreSQL to accept outside connections you will add another line
 such as
 hostall all zzz.zzz.zzz.zzz/32  md5

 Where zzz.zzz.zzz.zzz is the ip address of the remote computer that wants to
 connect.
 The pg_hba.conf file has notes explaining these entries or you can read up
 the docs at
 http://www.postgresql.org/docs/8.1/static/client-authentication.html

 Basically this entry says you are willing to accept network connections from
 another computer and how they are allowed to connect and which databases
 they can connect to.
 If you allow connections from anywhere then anyone has the chance of getting
 into your database.
 It is preferable to only allow connections from a specific ip address but if
 they don't have a static ip address you won't be able to do that. If they
 have a dynamic ip address one suggestion is try and limit them to
 connections from the isp they are connecting from instead of just any
 computer in the world.

 --

 Shane Ambler
 [EMAIL PROTECTED]

 Get Sheeky @ http://Sheeky.Biz



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

I will connect to my PostgreSQL by an Java applet, as also, from time
to time, by PGAdmin.


---(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] IF EXISTS

[stevethames]

I have a script that sets up my databases which I run whenever I change any
of the functions.  It has a number of things it does that are unnecessary
and cause errors.  I create some types, sequences, etc.  The error messages
are irritating.  I'd like to do something like this:

IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seqid')
  CREATE SEQUENCE SeqID;

This works in other SQL languages like SQLServer and MySQL.  Is there
anything like this in PostgreSQL?

-- 
View this message in context: 
http://www.nabble.com/IF-EXISTS-tf2308139.html#a6416894
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

   http://archives.postgresql.org

[GENERAL] column names in select don't exists in insert to

[Johan]

Hi,
I encountered a strange problem while trying to solve a bug. I use a
postgresql 8.x database and a jdbc driver from
postgresql-8.1dev-400.jdbc3.jar. The following is happening

The table is created like
create table test (
  field1 int8 not null,
  field2 int8 not null);

if I do a

select field1 from test;

results are returned normal, no problems at all, but when i do a

insert into test (field2, field1) values (1, 2);

It complains that field1 doesn't exists.

Has anyone encountered this same problem or does have anyone a pointer
for a bit more info on this problem?

Thanks,

Johan


---(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] What is the Best Postgresql Load Balancing Solution available ?

[Najib Abi Fadel]

I have a web application that is accessed by a large number of users. My application is written in PHP and uses postgres. Apache is our web server.The performance of my application drops down when a large numbers of users connects at the same time. I need to have a better response time ! That's why i need to load balance the web requests and the database.Regards,Najib.Ben Trewern [EMAIL PROTECTED] wrote: The solution you need all depends on the problem you are having.  If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better answer!Regards,Ben"Najib Abi Fadel" <[EMAIL PROTECTED]> wrote in message
 news:[EMAIL PROTECTED]Robin Ericsson <[EMAIL PROTECTED]> wrote:On 9/18/06, Najib Abi Fadel wrote: Hi, i was searching for a load balancing solution for postgres, I found some ready to use software like PGCluster, Slony, pgpool and others. It would really be nice if someone knows which one is the best taking in consideration that i have an already running application that i need to load balance.There isn't one tool that is the best, all three work very good basedon where they are used and what they are used for.-- regards,Robin---(end of broadcast)---TIP 5: don't forget to increase your free space map settingsDid you try them or have any experience with them. I need them for load balancing my database and thus making the queries faster. I
 have a web application heavely using a postgres database. Hundreds of users can connect at the same time to my web application.Thanks in advance for any help.Najib.How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. ---(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 
		How low will we go? Check out Yahoo! Messenger’s low  PC-to-Phone call rates.

[GENERAL] Table Inheritance / VARCHAR search question

[Jonathan Vanasco]

Hi,

I'm hoping someone on this list can save me some unnecessary  
benchmarking today


I have the  following table in my system

BIGSERIAL , INT , INT,  VARCHAR(32)

There are currently 1M records , it will grow to be much much  
bigger.  It's used as a search/dispatch table,  and gets the most  
traffic on my entire app.


I'm working on some new functionality, which will require the same 3  
colums as above but with 3 new VARCHAR(32) columns

BIGSERIAL , INT , INT,  +VARCHAR(32) , +VARCHAR(32) , +VARCHAR(32)

ie, the new  function shares the same  serial and the  the 2 INT columns

I'm trying to get this to work efficiently on speed and on disk space.

i've figured that my options are:

a)  one table with everything in it
pro:
simple
possible con:
		when i had  something similar in mysql 4 years ago, i had to make  
all the varchars chars , because speed was awful.  under this system,  
80% of the 3 new VARCHAR fields will always be null, so that  disk  
waste will be noticable.  thats only IF there is a speed issue with  
VARCHAR searching.


b) keep current table, create new table that inherits and has the 3   
new fields

pro: simple
possible con:
		i can't find any documentation on how an inherit works behind the  
scenes.  is the data cloned into the new table?  is there a join on  
every search?  if this is constantly doing a join behind the  scenes,  
thats probably not going to work for me


c) move to a 3 table structure
table1- serial
table2 - current table, bigserial is not bigint
table3- bigint + 3 varchars

pro:
obviously will work
con:
a lot of restructuring

i was going to have both table share a seqeunce, but then i  
remembered that the  id is foreign keyed by other tables


if anyone can offer a suggestion, i'd be greatly appreciative

---(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] What is the Best Postgresql Load Balancing Solution

[Leonel Nunez]

 I have a web application that is accessed by a large number of users.
 My application is written in PHP and uses postgres.  Apache is our web
 server.
 The performance of my application drops down when a large numbers of users
 connects at the same time. I need to have a better response time !  That's
 why i need to load balance the web requests and the database.

 Regards,
 Najib.


Before that, are you using  persistent  conecctions ?

Leonel



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

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

Re: [GENERAL] IF EXISTS

[Jeff Davis]

On Wed, 2006-09-20 at 14:18 -0700, stevethames wrote:
 I have a script that sets up my databases which I run whenever I change any
 of the functions.  It has a number of things it does that are unnecessary
 and cause errors.  I create some types, sequences, etc.  The error messages
 are irritating.  I'd like to do something like this:
 
 IF NOT EXISTS (SELECT relname FROM pg_class WHERE relname='seqid')
   CREATE SEQUENCE SeqID;
 
 This works in other SQL languages like SQLServer and MySQL.  Is there
 anything like this in PostgreSQL?
 

In 8.2, which is currently still in production, they have added the
feature where you can do things like:

DROP SEQUENCE IF EXISTS mysequence;
CREATE SEQUENCE mysequence;

Which makes writing SQL scripts much easier. They also made it work for
other objects, like DROP TABLE IF EXISTS, etc.

This might not help you, because 8.2 is still months away from being
production quality. However, a beta isn't too far off and you may be
interested to check it out. At least you know the code you want is
already written :)

I think the DROP IF EXISTS syntax makes more sense than CREATE IF NOT
EXISTS, because normally the purpose of this type of thing is to reset
your tables or sequences to the starting state.

Regards,
Jeff Davis




---(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] postgresql rising

[Ron Johnson]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

On 09/20/06 16:38, Philip Hallstrom wrote:
[snip]
 I think that description is false.  At a certain point in the
 management hierarchy, the only way anyone has the ability to
 evaluate something is on the basis of
 
 - if there is someone they can sue.
 - how attractive the sales rep is.

Back in my youth, working for the family business (roofing/siding
distributor, not many women, fewer attractive women), the most
successful salespeople were always... young attractive women.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is common sense really valid?
For example, it is common sense to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that common sense is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.5 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFFEc5eS9HxQb37XmcRAiJKAJ90OXzgizJtjFaOdsiB4jA4R/ogPQCgoF/n
50e84k21jUSP653XaMQjEq8=
=o9TW
-END PGP SIGNATURE-

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

Re: [GENERAL] What is the Best Postgresql Load Balancing Solution

[Jeff Davis]

On Wed, 2006-09-20 at 00:46 -0700, Najib Abi Fadel wrote:
 I have a web application that is accessed by a large number of users. 
 My application is written in PHP and uses postgres.  Apache is our web
 server.
 The performance of my application drops down when a large numbers of
 users connects at the same time. I need to have a better response
 time !  That's why i need to load balance the web requests and the
 database.
 
 Regards,
 Najib.
 

Slony is a well-tested system. I recommend you try Slony and see if it
suits your needs. If not, please tell us where it falls short, and
someone will have another suggestion for you.

Also, as someone else pointed out, make sure you're using connection
pooling if that's appropriate (which it usually is for web
applications). 

Regards,
Jeff Davis


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

Re: [GENERAL] What is the Best Postgresql Load Balancing Solution available ?

[Talha Khan]

You should also consider using connection pooling inorder to attain better performance.

Regards
Talha Khan
On 9/20/06, Najib Abi Fadel [EMAIL PROTECTED] wrote:

I have a web application that is accessed by a large number of users. My application is written in PHP and uses postgres. Apache is our web server.The performance of my application drops down when a large numbers of users connects at the same time. I need to have a better response time ! That's why i need to load balance the web requests and the database.
Regards,Najib.Ben Trewern [EMAIL PROTECTED] wrote:


The solution you need all depends on the problem you are having. If you explain how your application is written PHP, Java, etc and where your performance problems are coming from, then someone could give you a better 
answer!Regards,Ben
Najib Abi Fadel wrote in message 
news:[EMAIL PROTECTED]
Robin Ericsson wrote:On 9/18/06, Najib Abi Fadel wrote: Hi, i was searching for a load balancing solution for postgres, I found some ready to use software like
 PGCluster, Slony, pgpool and others. It would really be nice if someone knows which one is the best taking in consideration that i have an already running application that i need to load
 balance.There isn't one tool that is the best, all three work very good basedon where they are used and what they are used for.-- regards,Robin---(end of broadcast)---
TIP 5: don't forget to increase your free space map settingsDid you try them or have any experience with them. I need them for load balancing my database and thus making the queries faster. I have a web 
application heavely using a postgres database. Hundreds of users can connect at the same time to my web application.Thanks in advance for any help.Najib.How low will we go? Check out Yahoo! Messenger's low PC-to-Phone call rates. 
---(end of broadcast)---TIP 1: if posting/reading through Usenet, please send an appropriatesubscribe-nomail command to 
[EMAIL PROTECTED] so that yourmessage can get through to the mailing list cleanly





How low will we go? Check out Yahoo! Messenger's low 
PC-to-Phone call rates. 

Re: [GENERAL] postgresql rising

[Alvaro Herrera]

Ron Johnson wrote:

 On 09/20/06 16:38, Philip Hallstrom wrote:
 [snip]
  I think that description is false.  At a certain point in the
  management hierarchy, the only way anyone has the ability to
  evaluate something is on the basis of
  
  - if there is someone they can sue.
  - how attractive the sales rep is.
 
 Back in my youth, working for the family business (roofing/siding
 distributor, not many women, fewer attractive women), the most
 successful salespeople were always... young attractive women.

Pheromones sell.

I wonder if we could replace the elephant logo with a female elephant
logo.  That could work wonders ... among the elephant community at
least.  Are there many elephants among decision makers?

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

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

[GENERAL] creating table while slony-I replication is running

[gurkan]

Hi,
I am trying to find out how to CREATE a table while Slony-I replication is
running (meaning without stopping Slony-I replication adding/creating new table
into replication)

I have tried to run this but wont work. please help. thanks.

add_table.sh

#!/bin/sh

/opt/EnterpriseDB/8.1.4.17/dbserver/bin/slonik _EOF_
#--
# define the namespace the replication system uses in our example it is
# slony_example
#--
cluster name = $CLUSTERNAME;

node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOST
port=$MASTERDBPORT user=$REPLICATIONUSER';

EXECUTE SCRIPT (
SET ID = 1,
FILENAME = 
'/opt/EnterpriseDB/8.1.4.17/dbserver/bin/add_table.sql',
EVENT NODE = 1
);

_EOF_
--

add_table.sql
--
CREATE TABLE gurkan
  (
id serial not null ,
fname varchar(155),
lname varchar(155),
primary key (id)
  );
--

ERROR:
-
[EMAIL PROTECTED] bin]$ ./add_table.sh
stdin:9: PGRES_FATAL_ERROR select _edb_replication_example.ddlScript(1,
'CREATE TABLE gurkan
  (
id serial not null ,
fname varchar(155),
lname varchar(155),
primary key (id)
  );
X\u\u', -1);  - ERROR:  invalid byte sequence for encoding UNICODE: 
0xc1c4
-
-


-
This mail sent through IMP: www.resolution.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

Re: [GENERAL] postgresql rising

[CSN]

PostgreSQL doesn't have any booth babes? ;P

csn

 On 09/20/06 16:38, Philip Hallstrom wrote:
 [snip]
  I think that description is false.  At a certain point in the
  management hierarchy, the only way anyone has the ability to
  evaluate something is on the basis of
  
  - if there is someone they can sue.
  - how attractive the sales rep is.
 
 Back in my youth, working for the family business (roofing/siding
 distributor, not many women, fewer attractive women), the most
 successful salespeople were always... young attractive women.


__
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com 

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

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

Re: [GENERAL] postgresql rising

[Merlin Moncure]

On 9/21/06, Alvaro Herrera [EMAIL PROTECTED] wrote:

I wonder if we could replace the elephant logo with a female elephant
logo.  That could work wonders ... among the elephant community at
least.  Are there many elephants among decision makers?


our elephant isn't female?  that changes everything.

merlin

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

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

Re: [GENERAL] creating table while slony-I replication is running

[Shoaib Mir]

Hi Gurkan,I just tried the same but didn't face a problem while doing so. You can try the same steps that I did:1. Made a exec_scr file for Slonik with execute script command (i.e attached with the email)
2. Made a test.sql file which has the create table just like the one you mentioned (i.e attached with the email)3. Now using Slonik executed the script test.sql for master and salve like this
/opt/EnterpriseDB/8.1.4.17/dbserver/slonik exec_scrand that created the same table on the master and respective slave node.The encoding error that you are getting might be due to some extra or some special hidden characters in one of your script files. 
Please try using the script files that I have attached with the email and following the steps given above.Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com
)
On 9/21/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote:
Hi,I am trying to find out how to CREATE a table while Slony-I replication is
running (meaning without stopping Slony-I replication adding/creating new tableinto replication)I have tried to run this but wont work. please help. thanks.add_table.sh
#!/bin/sh/opt/EnterpriseDB/8.1.4.17/dbserver/bin/slonik _EOF_#--# define the namespace the replication system uses in our example it is# slony_example#--
cluster name = $CLUSTERNAME;node 1 admin conninfo = 'dbname=$MASTERDBNAME host=$MASTERHOSTport=$MASTERDBPORT user=$REPLICATIONUSER';EXECUTE SCRIPT (SET ID = 1,
FILENAME = '/opt/EnterpriseDB/8.1.4.17/dbserver/bin/add_table.sql',EVENT NODE = 1);_EOF_--add_table.sql--
CREATE TABLE gurkan(id serial not null ,fname varchar(155),lname varchar(155),primary key (id));--ERROR:-
[EMAIL PROTECTED] bin]$ ./add_table.shstdin:9: PGRES_FATAL_ERROR select _edb_replication_example.ddlScript(1,'CREATE TABLE gurkan(id serial not null ,fname varchar(155),
lname varchar(155),primary key (id));X\u\u', -1);- ERROR:invalid byte sequence for encoding UNICODE: 0xc1c4--
-This mail sent through IMP: www.resolution.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


exec_scr
Description: Binary data
CREATE TABLE gurkan ( id serial not null , fname varchar(155), lname varchar(155), primary key (id) );

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

   http://archives.postgresql.org

[GENERAL] Postgres Team: Thank You All

[Brian Maguire]

To all involved in this project,

I justed wanted to let you know how impressed and pleased  I have been with 
postgres over the past 5 years .  The timeliness and quality of the releases 
are always robust and stable.  Every release has a very nice mix of admin, 
performance, platform, and feature adds.  The support of the listserves is bar 
none to any commercial support I have ever purchased. I can't remember a time I 
did not get 3-5 answers to any question I have ever had. I must also note that 
the new website design and organization have added very nice polish to the 
project.  

Cheers and thank you all,

Brian

 

 


---(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 table while slony-I replication is running

[Christopher Browne]

In the last exciting episode, [EMAIL PROTECTED] wrote:
 Hi,
 I am trying to find out how to CREATE a table while Slony-I replication is
 running (meaning without stopping Slony-I replication adding/creating new 
 table
 into replication)

I think you have some extra characters in the script.  (e.g. - add_table.sql)
-- 
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/
I still maintain the point that designing a monolithic kernel in 1991
is  a fundamental error.   Be thankful  you are  not my  student.  You
would not get a high grade  for such a design :-) -- Andrew Tanenbaum
to Linus Torvalds

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

Re: [GENERAL] Access to databas from the Internet

[Shane Ambler]

On 20/9/2006 16:55, Lukasz [EMAIL PROTECTED] wrote:

 
 Shane Ambler napisal(a):
 On 19/9/2006 22:41, Lukasz [EMAIL PROTECTED] wrote:
 
 Hello,
 
 I would like to install a PostgreSQL. I know how to manage the database
 itself, creae databases, user, groups and so on. But I don't know how
 to allow other users, who are outside LAN to connect to database
 through Internet.
 
 For example, my external IP is xxx.xxx.xxx.xxx, and my IP in the local
 network is yyy.yyy.yyy.yyy. I want to install PostgreSQL on the
 computer with yyy.yyy... What and where I need to configure to have
 access to my database from Internet?
 
 
 I will assume that you want to allow normal psql client access and not
 through a web server.
 
 There is two places you will need to configure.
 
 One is your router - you will need to setup port forwarding . The default
 port for connecting to the PostgreSQL server is 5432 so the router will need
 to forward any incoming requests on tcp port 5432 to tcp port 5432  at
 server address yyy.yyy.yyy.yyy (your PostgreSQL server address)
 If you have configured a different port then adjust accordingly.
 This is a common configuration option and shouldn't be hard to find.
 
 I will connect to my PostgreSQL by an Java applet, as also, from time
 to time, by PGAdmin.
 

They will both connect the same as psql - through port 5432. If you wanted
them to connect to a web server in your network to access the db then you
would use port 80.
  
-- 

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz



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

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

Re: [GENERAL] postgresql rising

[Shane Ambler]

On 21/9/2006 9:39, Alvaro Herrera [EMAIL PROTECTED] wrote:

 I wonder if we could replace the elephant logo with a female elephant
 logo.  That could work wonders ... among the elephant community at
 least.  Are there many elephants among decision makers?

Aren't they all elephants?

Oh hang on ... I might be thinking of dinosaurs ;-)


-- 

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz


---(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] column names in select don't exists in insert to

[Michael Fuhr]

On Wed, Sep 20, 2006 at 02:29:16PM -0700, Johan wrote:
 I encountered a strange problem while trying to solve a bug. I use a
 postgresql 8.x database and a jdbc driver from
 postgresql-8.1dev-400.jdbc3.jar. The following is happening

Any reason you're not using the latest, postgresql-8.1-407.jdbc3.jar?

 The table is created like
 create table test (
   field1 int8 not null,
   field2 int8 not null);
 
 if I do a
 
 select field1 from test;
 
 results are returned normal, no problems at all, but when i do a
 
 insert into test (field2, field1) values (1, 2);
 
 It complains that field1 doesn't exists.

I can't reproduce this problem; could you post a complete test case?
Do you see the problem if you execute the same statements in psql?

-- 
Michael Fuhr

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

Re: [GENERAL] IF EXISTS

[Karsten Hilbert]

On Wed, Sep 20, 2006 at 04:12:16PM -0700, Jeff Davis wrote:

 In 8.2, which is currently still in production, they have added the
 feature where you can do things like:
 
 DROP SEQUENCE IF EXISTS mysequence;
 CREATE SEQUENCE mysequence;
 
 Which makes writing SQL scripts much easier. They also made it work for
 other objects, like DROP TABLE IF EXISTS, etc.
Hurra !

Karsten
-- 
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD  4537 78B9 A9F9 E407 1346

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