Re: [GENERAL] RETURNING clause: how to specifiy column indexes?

[Ken Johanson]

Tom Lane wrote:

Kris Jurka <[EMAIL PROTECTED]> writes:

I think the expectation is that:



CREATE TABLE t(a int, b int);
INSERT INTO t(b,a) VALUES (1,2) RETURNING *;



will return 1,2 instead of 2,1 as it does now.


Hmm ... I see your point, but on what grounds could one argue that
a "*" targetlist here should return something different from what
"SELECT * FROM t" would return?

I'd say that an app that wants that should write

INSERT INTO t(b,a) VALUES (1,2) RETURNING b,a;

which is surely not that hard if you've got the code to produce
the "(b,a)" part.

In any case it's not clear this is the same thing Ken is complaining
about ...



I am only seeking to have the columns returned in the order they appear 
naturally. JDBC says "This array contains the indexes of the columns in 
the target table that contain the auto-generated keys that should be 
made available."


For the record I was not "complaining", only citing in advance the fact 
that while some consider selecting the keys by index to be dubious, it 
nonetheless must be done because an API requires it. Casting my question 
into a complaint is another topic.




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

  http://archives.postgresql.org/

Re: [GENERAL] what is the date format in binary query results

[Tom Lane]

Samantha Atkins <[EMAIL PROTECTED]> writes:
> This brings up a second question.  How should I do byte order  
> conversion for 8 byte ints?  I can't use hton ntoh routines as they  
> max out at 32 bits.  Is there a better way?

Well, there's the PDP-endianness of odious memory, but AFAIK all current
platforms are internally consistent about the ordering of smaller and
larger pieces.  Look at the float8 and int64 send/recv routines in our
current sources.

> Also, are floating point numbers guaranteed uniform?

No :-( ... although there are darn few machines anymore that don't
at least claim to follow the IEEE 754 spec.

regards, tom lane

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

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

Re: [GENERAL] what is the date format in binary query results

[Tom Lane]

Samantha Atkins <[EMAIL PROTECTED]> writes:
> How can it be a simple 8 byte int or float and specify a timezone?

It doesn't.  Read the thread again.

regards, tom lane

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

Re: [GENERAL] Using hashtext and unique constraints together

[Peter Childs]

On 11/12/2007, Mason Hale <[EMAIL PROTECTED]> wrote:
>
>
> I'm thinking that an insert trigger that ensures (SELECT count(*) FROM
> page WHERE hashtext(url) = 
> hashtext('http://www.postgresql.org')
> AND url = ' http://www.postgresql.org' ) = 0 won't work given MVCC, as two
> transactions could simultaneously insert the same url at the same time.



Why not so long as it also locks the table (share lock should be enough) but
it could slow the table down if lots of transactions write to the table at
once.

Regards

Peter.

Re: [GENERAL] Slow PITR restore

[Gregory Stark]

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:

> Wow, o.k. well it is something we (the community) really should look at for
> 8.4. I am surprised that it is slower than just walking through the xlogs on
> recovery. I am sure there is a reason just surprised.

Well in the worst case it has to do nearly as much work as the original
database did. And it only gets to use 1 cpu so it can only have one i/o
request pending.

bgwriter is started already when doing recovery, right? Perhaps things could
be helped by telling bgwriter to behave differently during recovery.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.com
  Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL 
training!

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

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

Re: [GENERAL] Hijack!

[Peter Childs]

On 11/12/2007, Obe, Regina <[EMAIL PROTECTED]> wrote:
>
>  Well said Greg.  I have the same problem too of having a crippled mail
> reader :)  Really I find mid posting hard to follow especially if I'm the
> one that posted the question.  I hope we aren't going to hit people with
> hammers over this minor infraction.  It really makes one feel unwelcome.
>
> I guess we have beaten this horse enough though.
>
>  --
> **
>

Hmm Can't stop laughing I think you managed to break every rule in the book
with that post.

Peter.

Re: [GENERAL] top posting

[Peter Childs]

On 12/12/2007, Stephen Cook <[EMAIL PROTECTED]> wrote:
>
>
> I am subscribed to some other technical mailing lists on which the
> standard is top posting. Those people claim that filing through
> interleaved quotes or scrolling to the bottom just to see a sentence or
> two is a waste of their time. It is the same thing only backwards.
>
> Me, I don't care either way. I try to conform to whatever is the
> standard for whatever list it is. Why annoy the people giving free
> support?
>
> I suspect that neither is truly better, and that some of the original /
> very early / expert members just preferred bottom posting for whatever
> reasons, and it propagated into the "standard" for this list.
>
>
Top posting is bad grammar its like English if I wrote the sentence
backwards would you under stand it?

Its as simple as that I can't under stand whats going on if I need to start
at the back of (or bottom) and work back. Its like reading a book you start
at the beginning and work to the end, Top Posting is like putting the last
chapter or the conclusion at the start. It just does not work.

Cutting the original is summarizing what gone before so we can we know the
story so far quickly. Maybe we should start teaching this in schools?

Different languages have different rules there are languages that do read
right to left rather than left to right it does not mean there is anything
wrong with those languages, They are just not used here.

It understand you would backwards sentence the wrote I. If English like its
grammar bad is posting top.

(Sounds like something from Star Wars and the meaning has changed)


Peter Childs

Re: [GENERAL] Killing a session in windows

[Magnus Hagander]

On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote:
> Alvaro Herrera <[EMAIL PROTECTED]> writes:
> > Howard Cole wrote:
> >> I take it from the lack of response that nobody knows how to kill a 
> >> connection from the postgresql side on windows?
> 
> > You can't, short of sending a signal to the process or restarting the
> > service.
> 
> Which you can do, no?  I thought pg_ctl's kill option was invented
> specifically to make this less painful on Windows.

It does, and it shuold work. But it's just as dangerous as using kill
directly on the backends on Unix, of course.

//Magnus

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

Re: [GENERAL] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)

[Pavel Stehule]

Hello

this is bug. Please send backtrace from core file.

Regards
Pavel Stehule

On 12/12/2007, Lawrence Oluyede <[EMAIL PROTECTED]> wrote:
> While developing a Python program I encountered a situation that makes
> on of the PostgreSQL 8.3b4's processes crash badly with a segfault.
> Let me explain.
>
> After enabling "debug5" as log level I was able to locate and
> reproduce the exact sequence of SQL queries made against my test
> database
> and crash postgres again using only "psql". See for yourself:
>
>
> foodb=# \o out.txt
> foodb=# SELECT * FROM foobaz.instrument WHERE code = 'TEST0118';
> LOG:  duration: 3.683 ms  statement: SELECT * FROM foobaz.instrument
> WHERE code = 'TEST0118';
> foodb=# SELECT xpath('//sp:description/text()', content,
> ARRAY[ARRAY['sp', 'http://www.foobaz.com/']])
> foodb-#FROM foobaz.instrument WHERE code = 'TEST0018';
> server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> The connection to the server was lost. Attempting reset: Failed.
>
> The pgsql log file contains these error logs (the pid 753 is a
> postgres child process):
>
> "
> [loluyede:foodb:2007-12-11 14:38:20.992 CET]LOG:  0: duration:
> 0.090 ms  statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ
> COMMITTED
> [loluyede:foodb:2007-12-11 14:38:20.992 CET]LOCATION:
> exec_simple_query, postgres.c:1040
> [::2007-12-11 14:38:20.993 CET]LOG:  0: server process (PID 753)
> was terminated by signal 11: Segmentation fault
> [::2007-12-11 14:38:20.993 CET]LOCATION:  LogChildExit, postmaster.c:2510
> [::2007-12-11 14:38:20.993 CET]LOG:  0: terminating any other
> active server processes
> [::2007-12-11 14:38:20.993 CET]LOCATION:  HandleChildCrash, postmaster.c:2355
> [::2007-12-11 14:38:20.997 CET]LOG:  0: all server processes
> terminated; reinitializing
> [::2007-12-11 14:38:20.997 CET]LOCATION:  PostmasterStateMachine,
> postmaster.c:2663
> [::2007-12-11 14:38:21.000 CET]LOG:  0: database system was
> interrupted; last known up at 2007-12-11 14:38:02 CET
> [::2007-12-11 14:38:21.000 CET]LOCATION:  StartupXLOG, xlog.c:4789
> [::2007-12-11 14:38:21.000 CET]DEBUG:  0: checkpoint record is at 
> 0/2AB634A0
> 
>
> The out.txt file contains the output of the "SELECT *" statement
>
>code   |kind| last_modified |
> created| modified_by | created_by |
>content
> --++---+--+-++--
>  TEST0118 | bazfoo | 2007-12-12 08:17:01.846919+01 | 2007-12-12
> 08:17:00.32337+01 | anonymous   | anonymous  | 
> (1 row)
>
> The "instrument" table has the following schema:
>
> """
> CREATE TABLE foobaz.instrument
> (
> -- Inherited:   code character varying(32) NOT NULL,
> -- Inherited:   kind character varying(32) NOT NULL DEFAULT
> 'bazfoo'::character varying,
>   last_modified timestamp with time zone NOT NULL,
>   created timestamp with time zone NOT NULL DEFAULT
> ('now'::text)::timestamp(3) with time zone,
>   modified_by character varying(64),
>   created_by character varying(64) NOT NULL DEFAULT
> 'anonymous'::character varying,
>   content xml NOT NULL,
>   CONSTRAINT instrument_pkey PRIMARY KEY (code),
>   CONSTRAINT instrument_kind_check CHECK (kind::text = 'bazfoo'::text)
> )
> INHERITS (foobaz.instrument_base)
> WITH (OIDS=FALSE);
> """
>
> Its "parent" table is instrument_base and has the following schema:
>
> """
> CREATE TABLE foobaz.instrument_base
> (
>   code character varying(32) NOT NULL,
>   kind character varying(32) NOT NULL,
>   CONSTRAINT instrument_base_pkey PRIMARY KEY (code)
> )
> WITH (OIDS=FALSE);
> """
>
> PostgreSQL is again 8.3beta 4 on Ubuntu Linux 7.10.
> It's compiled with the following flags:
> ./configure --with-python --with-openssl --with-pam --with-libxml
> --with-libxslt --enable-thread-safety --enable-debug
>
> libxml is 2.6.30, libxslt is 1.1.21
>
> I think that is all.
>
> Is it a bug or am I doing something wrong?
>
> --
> Lawrence, oluyede.org - neropercaso.it
> "It is difficult to get a man to understand
> something when his salary depends on not
> understanding it" - Upton Sinclair
>
> ---(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] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)

[Lawrence Oluyede]

While developing a Python program I encountered a situation that makes
on of the PostgreSQL 8.3b4's processes crash badly with a segfault.
Let me explain.

After enabling "debug5" as log level I was able to locate and
reproduce the exact sequence of SQL queries made against my test
database
and crash postgres again using only "psql". See for yourself:


foodb=# \o out.txt
foodb=# SELECT * FROM foobaz.instrument WHERE code = 'TEST0118';
LOG:  duration: 3.683 ms  statement: SELECT * FROM foobaz.instrument
WHERE code = 'TEST0118';
foodb=# SELECT xpath('//sp:description/text()', content,
ARRAY[ARRAY['sp', 'http://www.foobaz.com/']])
foodb-#FROM foobaz.instrument WHERE code = 'TEST0018';
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.

The pgsql log file contains these error logs (the pid 753 is a
postgres child process):

"
[loluyede:foodb:2007-12-11 14:38:20.992 CET]LOG:  0: duration:
0.090 ms  statement: BEGIN; SET TRANSACTION ISOLATION LEVEL READ
COMMITTED
[loluyede:foodb:2007-12-11 14:38:20.992 CET]LOCATION:
exec_simple_query, postgres.c:1040
[::2007-12-11 14:38:20.993 CET]LOG:  0: server process (PID 753)
was terminated by signal 11: Segmentation fault
[::2007-12-11 14:38:20.993 CET]LOCATION:  LogChildExit, postmaster.c:2510
[::2007-12-11 14:38:20.993 CET]LOG:  0: terminating any other
active server processes
[::2007-12-11 14:38:20.993 CET]LOCATION:  HandleChildCrash, postmaster.c:2355
[::2007-12-11 14:38:20.997 CET]LOG:  0: all server processes
terminated; reinitializing
[::2007-12-11 14:38:20.997 CET]LOCATION:  PostmasterStateMachine,
postmaster.c:2663
[::2007-12-11 14:38:21.000 CET]LOG:  0: database system was
interrupted; last known up at 2007-12-11 14:38:02 CET
[::2007-12-11 14:38:21.000 CET]LOCATION:  StartupXLOG, xlog.c:4789
[::2007-12-11 14:38:21.000 CET]DEBUG:  0: checkpoint record is at 0/2AB634A0


The out.txt file contains the output of the "SELECT *" statement

   code   |kind| last_modified |
created| modified_by | created_by |
   content
--++---+--+-++--
 TEST0118 | bazfoo | 2007-12-12 08:17:01.846919+01 | 2007-12-12
08:17:00.32337+01 | anonymous   | anonymous  | 
(1 row)

The "instrument" table has the following schema:

"""
CREATE TABLE foobaz.instrument
(
-- Inherited:   code character varying(32) NOT NULL,
-- Inherited:   kind character varying(32) NOT NULL DEFAULT
'bazfoo'::character varying,
  last_modified timestamp with time zone NOT NULL,
  created timestamp with time zone NOT NULL DEFAULT
('now'::text)::timestamp(3) with time zone,
  modified_by character varying(64),
  created_by character varying(64) NOT NULL DEFAULT
'anonymous'::character varying,
  content xml NOT NULL,
  CONSTRAINT instrument_pkey PRIMARY KEY (code),
  CONSTRAINT instrument_kind_check CHECK (kind::text = 'bazfoo'::text)
)
INHERITS (foobaz.instrument_base)
WITH (OIDS=FALSE);
"""

Its "parent" table is instrument_base and has the following schema:

"""
CREATE TABLE foobaz.instrument_base
(
  code character varying(32) NOT NULL,
  kind character varying(32) NOT NULL,
  CONSTRAINT instrument_base_pkey PRIMARY KEY (code)
)
WITH (OIDS=FALSE);
"""

PostgreSQL is again 8.3beta 4 on Ubuntu Linux 7.10.
It's compiled with the following flags:
./configure --with-python --with-openssl --with-pam --with-libxml
--with-libxslt --enable-thread-safety --enable-debug

libxml is 2.6.30, libxslt is 1.1.21

I think that is all.

Is it a bug or am I doing something wrong?

-- 
Lawrence, oluyede.org - neropercaso.it
"It is difficult to get a man to understand
something when his salary depends on not
understanding it" - Upton Sinclair

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

Re: [GENERAL] thesaurus support in postgresql

[Florian Aumeier]

just a follow up to my question regarding thesaurus support.
Is there really no one here who knows anything about it?

If there are plans for it?
If there is someone currently working on it?
Or if there is somebody else I could ask?

Regards
Florian

--
Media Ventures GmbH 
Jabber-ID [EMAIL PROTECTED]

Telefon +49 (0) 2236 480 10 22


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

Re: [GENERAL] Hijack!

[Gregory Williamson]

Peter Childs caused electrons to shape a message:
>  
> On 11/12/2007, Obe, Regina <[EMAIL PROTECTED]> wrote:
> >
> >  Well said Greg.  I have the same problem too of having a crippled mail
> > reader :)  Really I find mid posting hard to follow especially if I'm the
> > one that posted the question.  I hope we aren't going to hit people with
> > hammers over this minor infraction.  It really makes one feel unwelcome.
> >
> > I guess we have beaten this horse enough though.
> >
>  --
> 
> Hmm Can't stop laughing I think you managed to break every rule in the book
> with that post.
> 
> Peter.

And as they say where I come from, there is _no_ point to beating a dead horse, 
aside from the sheer joy of the thing.

Seriously -- "top posting bad, bottom posting good" also misses all kinds of 
points -- intelligent quoting and interspersing comments / answers where they 
belong is the ticket, when it can be done. Well, off to top post on some other 
forums ... ;-)

Greg W.
yadda yadda

Re: [GENERAL] thesaurus support in postgresql

[Oleg Bartunov]

Florian,

I'm one of the author of thesaurus. We're interested in improving of 
thesaurus, but have no spare time. You're welcome to improve 
thesaurus dictionary.


Oleg
On Wed, 12 Dec 2007, Florian Aumeier wrote:


just a follow up to my question regarding thesaurus support.
Is there really no one here who knows anything about it?

If there are plans for it?
If there is someone currently working on it?
Or if there is somebody else I could ask?

Regards
Florian




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Re: [GENERAL] thesaurus support in postgresql

[Florian Aumeier]

Hi Oleg,

thanks for your reply. Unfortunately I can't do C programming :-(

Regards
Florian

--
Media Ventures GmbH 


Jabber-ID [EMAIL PROTECTED]
Telefon +49 (0) 2236 480 10 22


---(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] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)

[Lawrence Oluyede]

Here it is:

postgres$ gdb /usr/local/pgsql/bin/postgres core.1600
(gdb) bt
#0  0x082c101c in pfree (pointer=0x8472f00) at mcxt.c:591
#1  0xb7e46513 in xmlCleanupCharEncodingHandlers () from /usr/lib/libxml2.so.2
#2  0xb7e4f091 in xmlCleanupParser () from /usr/lib/libxml2.so.2
#3  0x082940e4 in xpath (fcinfo=0xbfee59c4) at xml.c:3441
#4  0x0817107f in ExecMakeFunctionResult (fcache=0x84723d0,
econtext=0x8472348, isNull=0x8472fa8 "", isDone=0x8472fb8) at
execQual.c:1351
#5  0x0816f522 in ExecProject (projInfo=0x8472e98, isDone=0xbfee5c78)
at execQual.c:4601
#6  0x08175987 in ExecScan (node=0x84722c0, accessMtd=0x817e310
) at execScan.c:143
#7  0x0817e2e7 in ExecIndexScan (node=0x84722c0) at nodeIndexscan.c:147
#8  0x0816ee7a in ExecProcNode (node=0x84722c0) at execProcnode.c:338
#9  0x0816e113 in ExecutorRun (queryDesc=0x845e908,
direction=ForwardScanDirection, count=0) at execMain.c:1233
#10 0x08204ea0 in PortalRunSelect (portal=0x84577a0, forward=, count=0, dest=0x845d1c8) at pquery.c:943
#11 0x08205eaa in PortalRun (portal=0x84577a0, count=2147483647,
isTopLevel=1 '\001', dest=0x845d1c8, altdest=0x845d1c8,
completionTag=0xbfee5ee8 "") at pquery.c:797
#12 0x082013c3 in exec_simple_query (
query_string=0x8453790 "SELECT xpath('//sp:description/text()',
content, ARRAY[ARRAY['sp',
'http://www.statpro.net/xml/structure/1.0']])\n   FROM instrument
WHERE code = 'TEST0018';") at postgres.c:963
#13 0x08202ebc in PostgresMain (argc=4, argv=,
username=0x83d2330 "postgres") at postgres.c:3531
#14 0x081d76cf in ServerLoop () at postmaster.c:3180
#15 0x081d83a5 in PostmasterMain (argc=3, argv=0x83ccb08) at postmaster.c:1028
#16 0x081909a0 in main (argc=3, argv=0x82c0c00) at main.c:188
(gdb) l
586 header = (StandardChunkHeader *)
587 ((char *) pointer - STANDARDCHUNKHEADERSIZE);
588
589 AssertArg(MemoryContextIsValid(header->context));
590
591 (*header->context->methods->free_p) (header->context, pointer);
592 }
593
594 /*
595  * repalloc

I also uploaded the entire core file on the net, if you want to inspect it:
http://www.oluyede.org/temp/core.1600 (it's ~ 30M)

thank you



-- 
Lawrence, oluyede.org - neropercaso.it
"It is difficult to get a man to understand
something when his salary depends on not
understanding it" - Upton Sinclair

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

   http://archives.postgresql.org/

Re: [GENERAL] thesaurus support in postgresql

[Oleg Bartunov]

On Wed, 12 Dec 2007, Florian Aumeier wrote:


Hi Oleg,

thanks for your reply. Unfortunately I can't do C programming :-(


C skill is not mandatory, you can help suggestion a ways how to improve
thesaurus.



Regards
Florian




Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

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

Re: [GENERAL] Using hashtext and unique constraints together

[Daniel Verite]

Mason Hale wrote:

> The problem I need help with is guaranteeing uniqueness of the URL on
> insert, with a non-unique index on hashtext(url) and *without* creating a
> unique index on page(url).
> 
> I'm thinking that an insert trigger that ensures (SELECT count(*) FROM page
> WHERE hashtext(url) = hashtext('http://www.postgresql.org') AND url = '
> http://www.postgresql.org' ) = 0 won't work given MVCC, as two transactions
> could simultaneously insert the same url at the same time.

Suppose that before that SELECT, the transaction would insert the url
into a small dedicated table, with a unique index on the url column.
If a second transaction starts with the same url value before the first one
commits, it will be blocked at the point of the insert, because of that
unique index. And then it will fail with a constraint violation if and when
the first one commits. And even if it didn't, the SELECT count(*) above 
would return non-zero.
What makes this table "small" is that each row in it can be discarded as
soon as its corresponding transaction has been committed, since after that
point, the SELECT count(*) inside your trigger has enough visibility to take
care of the unicity check.
So there could be a cron job periodically deleting all rows from the small
table (that is, all committed rows). That would keep its size small, otherwise
of course you'd be eventually back to the original problem of having that
big index.

It's certainly not elegant, but I believe it would work.

-- 
 Daniel
 PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

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

Re: [GENERAL] Hijack!

[Alvaro Herrera]

Guy Rouillier wrote:

> (2) delete all the individual addressees so only the list is left, then 
> change that from CC to TO

Why do you do that?  It's unnecessary.

> (3) change my from identity to the one used for the list; although the list 
> always posts to the identity I have set up for mailing lists, for some 
> reason Thunderbird selects a different identity when I reply.

Probably the easiest way to handle this on the postgresql.org server
side is to configure the other identity as an alias, so that it allows
you to post unmoderated with both.  For this, see
http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org

-- 
Alvaro Herrerahttp://www.advogato.org/person/alvherre
"I love the Postgres community. It's all about doing things _properly_. :-)"
(David Garamond)

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

Re: [GENERAL] Hijack!

[Thomas Kellerer]

Joshua D. Drake, 11.12.2007 17:43:

O.k. this might be a bit snooty but frankly it is almost 2008. If you
are still a top poster, you obviously don't care about the people's
content that you are replying to, to have enough wits to not top post.


I personally find non-trimmed bottom postings at lot more annoying than 
top-postings. But then that's probably just me.


Thomas


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

  http://archives.postgresql.org/

Re: [GENERAL] Trigger - will not perform INSERT

[Alban Hertroys]

On Dec 11, 2007, at 21:35, smiley2211 wrote:



ex:

CREATE TRIGGER mytrig AFTER INSERT OR UPDATE ON foo1 FOR EACH ROW  
EXECUTE

PROCEDURE updatefoo1('datarow');

The command itself is able to be executed without error. However,  
what then
happens, is that whenever the application attempts to insert a  
record into

foo1, it simply doesn't insert. Once I take the trigger off, it beings
inserting again.

I have checked permissions but INSERT only FAILS while trigger is  
enabled..


Thanks...Michelle


This would be expected behaviour if the trigger were a BEFORE INSERT  
one (instead of AFTER) and the procedure returned NULL. You might  
want to check that is really not the case.


AFTER INSERT triggers don't fire until the row is actually inserted  
into the table, so the only possibility I can see for the behaviour  
you describe is that the stored procedure removes the record that was  
just inserted.
Maybe there are statements in that procedure that attempt to remove  
possible duplicates that also happen to match on the new record?


Regards,
--
Alban Hertroys

"If you throw your hands up in the air,
how're you gonna catch them?"




!DSPAM:737,475fc5969651302216542!



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

Re: [GENERAL] very slow query

[A. Kretschmer]

am  Wed, dem 12.12.2007, um 11:44:58 + mailte Ashish Karalkar folgendes:
> Hi,
> I am having PostgreSQL 8.2.4 on Suse 10.3
> 
> Server is not using the index insted it chooses to take seq scan path. table 
> is
> having @ 120 million rows
> 
> here is the output from planner:
> Nested Loop IN Join  (cost=0.00..5030217.97 rows=2 width=106)
>->  Seq Scan on sms_new  (cost=0.00..5027902.00 rows=384 width=106)
>  Filter: (mobile = 919820920858::bigint)
>->  Index Scan using deliveryid_pkey on delivery  (cost=0.00..6.02 rows=1
> width=8)
>  Index Cond: ("outer".deliveryid = delivery.deliveryid)
>  Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time
> zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone)
> AND ((keyword)::text = 'CRI'::text))
> 
> column deliveryid has an index over it in sms_new table.
> 
> can anybody please guide to force the usage of index


It's using an index on "deliveryid":
Index Scan using deliveryid_pkey

Do you have an index on "mobile"? Can you show us the table definition
and the output from EXPLAIN ANALYSE?



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(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] Slow PITR restore

[Simon Riggs]

On Wed, 2007-12-12 at 08:55 +, Gregory Stark wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> 
> > Wow, o.k. well it is something we (the community) really should look at for
> > 8.4. I am surprised that it is slower than just walking through the xlogs on
> > recovery. I am sure there is a reason just surprised.

It's the same speed because it is the same recovery code.

> Well in the worst case it has to do nearly as much work as the original
> database did. And it only gets to use 1 cpu so it can only have one i/o
> request pending.

That need only be slow in certain circumstances.

> bgwriter is started already when doing recovery, right? Perhaps things could
> be helped by telling bgwriter to behave differently during recovery.

It would certainly help if bgwriter came up earlier. I've been looking
at that just recently. The main issue is how to structure the code to
tell bgwriter when it can start processing in recovery mode and then
move into normal mode.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.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

[GENERAL] very slow query

[Ashish Karalkar]

Hi,
I am having PostgreSQL 8.2.4 on Suse 10.3

Server is not using the index insted it chooses to take seq scan path. table is 
having @ 120 million rows

here is the output from planner:
Nested Loop IN Join  (cost=0.00..5030217.97 rows=2 width=106)
   ->  Seq Scan on sms_new  (cost=0.00..5027902.00 rows=384 width=106)
 Filter: (mobile = 919820920858::bigint)
   ->  Index Scan using deliveryid_pkey on delivery  (cost=0.00..6.02 rows=1 
width=8)
 Index Cond: ("outer".deliveryid = delivery.deliveryid)
 Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time 
zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone) 
AND ((keyword)::text = 'CRI'::text))

column deliveryid has an index over it in sms_new table.

can anybody please guide to force the usage of index

Thanks in advance
With Regards
Ashish






   
-
 Get the freedom to save as many mails as you wish. Click here to know how.

Re: [GENERAL] Slow PITR restore

[Alvaro Herrera]

Gregory Stark wrote:

> bgwriter is started already when doing recovery, right? Perhaps things could
> be helped by telling bgwriter to behave differently during recovery.

No.

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Amanece.   (Ignacio Reyes)
 El Cerro San Cristóbal me mira, cínicamente, con ojos de virgen"

---(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] index organized tables use case

[Enrico Sirola]

Hello,
I'm thinking about migrating from another DBMS to postgresql. I have an
almost working proof of concept, but still have some doubts about the
following use case.

I have a table like the following

CREATE TABLE test
(
  code character varying(32) NOT NULL,
  tag integer NOT NULL,
  value double precision,
  CONSTRAINT test_pkey PRIMARY KEY (code, tag)
);

It represents a sequence (with holes) of values associated with a code.
The application code usually performs selection queries like

select tag, value from test where code='XXX';

also, deletions are like

delete from test where code='XXX';

and insertions follow the same pattern (all the data for a code is
inserted using a loop in a single transaction). That's more or less all.

so this type of workload is greatly enhanced by an index-organized table
(oracle) or a clustered index (SQL Server/Sybase).

>From what I understood this kind of table is presently not supported by
postgresql (is it?) so, what do you advice?

I would think about something like

CREATE TABLE testnew
(
  code character varying(32) NOT NULL,
  first_tag integer, /* the tag value associated with the first value */
  "values" double precision[], /* the datum, or NaN if not valid */
  valid_values bit(1)[], /* true if a datum is present */
  CONSTRAINT testnew_pkey PRIMARY KEY (code)
);

but this would require an application refactoring. Any idea?

TIA,
e.


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

Re: [GENERAL] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)

[Pavel Stehule]

sorry

s/date/data/g

Pavel

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

[GENERAL] LIBPQ Exception

[Abraham, Danny]

How do I program a DML statement that does not roll back using C.

res=PQexec(conn,"BEGIN; insert into x values(100); exception when others
then null; end;")

fails on syntax.

Any other alternative?

Thanks

Danny



---(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] very slow query

[A. Kretschmer]

am  Wed, dem 12.12.2007, um 12:25:20 + mailte Ashish Karalkar folgendes:
> > here is the output from planner:
> > Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
> > -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> > Filter: (mobile = 919820920858::bigint)
> 
> Do you have an index on "mobile"? Can you show us the table definition
> and the output from EXPLAIN ANALYSE?
> 
> no index on mobile

Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint)
(cost=0.00..5027902.00


Can you see the problem?


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(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] Instaltiating an ARRAY within a function

[Abraham, Danny]

===

CREATE OR REPLACE FUNCTION arr( inout x varchar[] )
AS
$Z$
DECLARE
  i integer;
BEGIN
select ARRAY['Danny','Eissam','Moshe'] into x;
end;
$Z$ LANGUAGE 'plpgsql' VOLATILE;

===

CREATE OR REPLACE FUNCTION callarr()
returns integer
AS
$Z$
DECLARE
  x varchar[6]; 
BEGIN
perform arr(x);
RAISE NOTICE 'x[1]=%',x[1];
return 0;
end;
$Z$ LANGUAGE 'plpgsql' VOLATILE;

===

select callarr();
NOTICE:  x[1]=  ??? Should have been DANNY


Should it work?

Thanks

Danny

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

Re: [GENERAL] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)

[Lawrence Oluyede]

On Dec 12, 2007 1:29 PM, Pavel Stehule <[EMAIL PROTECTED]> wrote:
> I cannot repeat this bug on my test date. Can you send some small set
> that reproduce the bug?

I dumped the entire table here: http://www.oluyede.org/temp/instrument.txt

Let me know if you need anything else


-- 
Lawrence, oluyede.org - neropercaso.it
"It is difficult to get a man to understand
something when his salary depends on not
understanding it" - Upton Sinclair

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

Re: [GENERAL] Hijack!

[Alvaro Herrera]

Thomas Kellerer wrote:
> Joshua D. Drake, 11.12.2007 17:43:
>> O.k. this might be a bit snooty but frankly it is almost 2008. If you
>> are still a top poster, you obviously don't care about the people's
>> content that you are replying to, to have enough wits to not top post.
>
> I personally find non-trimmed bottom postings at lot more annoying than 
> top-postings. But then that's probably just me.

It's not just you.  Much as I am annoyed by top-posting, I am much more
so by people who top-post at the bottom.  Hey, did I say something
stupid?  No -- think about it.  These guys do exactly the same thing as
top-posters, except it is much worse because the actual text they wrote
is harder to find.

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"In fact, the basic problem with Perl 5's subroutines is that they're not
crufty enough, so the cruft leaks out into user-defined code instead, by
the Conservation of Cruft Principle."  (Larry Wall, Apocalypse 6)

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

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

Re: [GENERAL] Possible bug in PostgreSQL 8.3beta4 (postgres process segfaults)

[Pavel Stehule]

Hello

I cannot repeat this bug on my test date. Can you send some small set
that reproduce the bug?

Regards
Pavel

On 12/12/2007, Lawrence Oluyede <[EMAIL PROTECTED]> wrote:
> Here it is:
>
> postgres$ gdb /usr/local/pgsql/bin/postgres core.1600
> (gdb) bt
> #0  0x082c101c in pfree (pointer=0x8472f00) at mcxt.c:591
> #1  0xb7e46513 in xmlCleanupCharEncodingHandlers () from /usr/lib/libxml2.so.2
> #2  0xb7e4f091 in xmlCleanupParser () from /usr/lib/libxml2.so.2
> #3  0x082940e4 in xpath (fcinfo=0xbfee59c4) at xml.c:3441
> #4  0x0817107f in ExecMakeFunctionResult (fcache=0x84723d0,
> econtext=0x8472348, isNull=0x8472fa8 "", isDone=0x8472fb8) at
> execQual.c:1351
> #5  0x0816f522 in ExecProject (projInfo=0x8472e98, isDone=0xbfee5c78)
> at execQual.c:4601
> #6  0x08175987 in ExecScan (node=0x84722c0, accessMtd=0x817e310
> ) at execScan.c:143
> #7  0x0817e2e7 in ExecIndexScan (node=0x84722c0) at nodeIndexscan.c:147
> #8  0x0816ee7a in ExecProcNode (node=0x84722c0) at execProcnode.c:338
> #9  0x0816e113 in ExecutorRun (queryDesc=0x845e908,
> direction=ForwardScanDirection, count=0) at execMain.c:1233
> #10 0x08204ea0 in PortalRunSelect (portal=0x84577a0, forward= optimized out>, count=0, dest=0x845d1c8) at pquery.c:943
> #11 0x08205eaa in PortalRun (portal=0x84577a0, count=2147483647,
> isTopLevel=1 '\001', dest=0x845d1c8, altdest=0x845d1c8,
> completionTag=0xbfee5ee8 "") at pquery.c:797
> #12 0x082013c3 in exec_simple_query (
> query_string=0x8453790 "SELECT xpath('//sp:description/text()',
> content, ARRAY[ARRAY['sp',
> 'http://www.statpro.net/xml/structure/1.0']])\n   FROM instrument
> WHERE code = 'TEST0018';") at postgres.c:963
> #13 0x08202ebc in PostgresMain (argc=4, argv=,
> username=0x83d2330 "postgres") at postgres.c:3531
> #14 0x081d76cf in ServerLoop () at postmaster.c:3180
> #15 0x081d83a5 in PostmasterMain (argc=3, argv=0x83ccb08) at postmaster.c:1028
> #16 0x081909a0 in main (argc=3, argv=0x82c0c00) at main.c:188
> (gdb) l
> 586 header = (StandardChunkHeader *)
> 587 ((char *) pointer - STANDARDCHUNKHEADERSIZE);
> 588
> 589 AssertArg(MemoryContextIsValid(header->context));
> 590
> 591 (*header->context->methods->free_p) (header->context, 
> pointer);
> 592 }
> 593
> 594 /*
> 595  * repalloc
>
> I also uploaded the entire core file on the net, if you want to inspect it:
> http://www.oluyede.org/temp/core.1600 (it's ~ 30M)
>
> thank you
>
>
>
> --
> Lawrence, oluyede.org - neropercaso.it
> "It is difficult to get a man to understand
> something when his salary depends on not
> understanding it" - Upton Sinclair
>

---(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] very slow query

[Ashish Karalkar]

"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am  Wed, dem 12.12.2007, um 12:44:09 
+ mailte Ashish Karalkar folgendes:
> > Do you have an index on "mobile"? Can you show us the table definition
> > and the output from EXPLAIN ANALYSE?
> >
> > no index on mobile
> 
> Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint)
> (cost=0.00..5027902.00
> 
> 
> Can you see the problem?
> 
> So i will have to create index on mobile is taht so?

Try it.
Thanks for your answer actually that  was  the part of full query here is the 
actuall plan

HashAggregate  (cost=5895532.37..5895534.35 rows=158 width=32)
   ->  Hash Join  (cost=215823.74..5895449.38 rows=5533 width=32)
 Hash Cond: ("outer".deliveryid = "inner".deliveryid)
 ->  Seq Scan on sms_new  (cost=0.00..5038183.09 rows=128277444 width=8)
   Filter: ((otid)::text !~~ 'ERROR%'::text)
 ->  Hash  (cost=215823.35..215823.35 rows=158 width=32)
   ->  Bitmap Heap Scan on delivery  (cost=2178.24..215823.35 
rows=158 width=32)
 Recheck Cond: ((createddate >= '2007-12-11 
00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
23:59:59'::timestamp without time zone))
 Filter: ((taskid = 1024) AND (((remoteip)::text = 
'192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR 
((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = 
'192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR 
((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = 
'202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR 
((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = 
'192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR 
((remoteip)::text = '202.162.231.7'::text)))
 ->  Bitmap Index Scan on createddate_idx  
(cost=0.00..2178.24 rows=195039 width=0)
   Index Cond: ((createddate >= '2007-12-11 
00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
23:59:59'::timestamp without time zone))   


any suggestion on this


With Regards
Ashish...



Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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


   
-
 Get the freedom to save as many mails as you wish. Click here to know how.

[GENERAL] executing a procedure with delay

[Ottavio Campana]

I'd like to execute a stored procedure in postgresql one minute after a
table has been modified. In case there are two changes in less than one
minute, I want to reset the time that has to be waited before running
the procedure.

I think I need to use a trigger, but I don't know how...

Can you help me please?

-- 
Non c'e' piu' forza nella normalita', c'e' solo monotonia.



signature.asc
Description: OpenPGP digital signature

Re: [GENERAL] very slow query

[Alvaro Herrera]

Ashish Karalkar wrote:

> Thanks for your answer actually that  was  the part of full query here is the 
> actuall plan

I think you are confusing this for "here is a completely different plan
for a completely different query that has nothing to do whatsoever to
what I was asking before".

On this plan it looks like you need an index on otid.  Or maybe not.
It's hard to tell with only an EXPLAIN.

> HashAggregate  (cost=5895532.37..5895534.35 rows=158 width=32)
>->  Hash Join  (cost=215823.74..5895449.38 rows=5533 width=32)
>  Hash Cond: ("outer".deliveryid = "inner".deliveryid)
>  ->  Seq Scan on sms_new  (cost=0.00..5038183.09 rows=128277444 
> width=8)
>Filter: ((otid)::text !~~ 'ERROR%'::text)
>  ->  Hash  (cost=215823.35..215823.35 rows=158 width=32)
>->  Bitmap Heap Scan on delivery  (cost=2178.24..215823.35 
> rows=158 width=32)
>  Recheck Cond: ((createddate >= '2007-12-11 
> 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
> 23:59:59'::timestamp without time zone))
>  Filter: ((taskid = 1024) AND (((remoteip)::text = 
> '192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR 
> ((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = 
> '192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR 
> ((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = 
> '202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR 
> ((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = 
> '192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR 
> ((remoteip)::text = '202.162.231.7'::text)))
>  ->  Bitmap Index Scan on createddate_idx  
> (cost=0.00..2178.24 rows=195039 width=0)
>Index Cond: ((createddate >= '2007-12-11 
> 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
> 23:59:59'::timestamp without time zone))   

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)

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

[GENERAL] Southern California Linux Expo

[Richard Broersma Jr]

Are there any planes in the works for a booth and talks for PostgreSQL?

Regards,
Richard Broersma Jr.

---(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] very slow query

[A. Kretschmer]

am  Wed, dem 12.12.2007, um 12:44:09 + mailte Ashish Karalkar folgendes:
> > Do you have an index on "mobile"? Can you show us the table definition
> > and the output from EXPLAIN ANALYSE?
> >
> > no index on mobile
> 
> Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint)
> (cost=0.00..5027902.00
> 
> 
> Can you see the problem?
> 
> So i will have to create index on mobile is taht so?

Try it.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

---(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] very slow query

[Ashish Karalkar]

Alvaro Herrera <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote:

> Thanks for your answer actually that  was  the part of full query here is the 
> actuall plan

I think you are confusing this for "here is a completely different plan
for a completely different query that has nothing to do whatsoever to
what I was asking before".

Actually all issue is with sms_new table which is having 120 M rows.
an planer is ignoring to use index on delivery id.
EXPLAIN ANALYSE too take long time to show output :(


On this plan it looks like you need an index on otid.  Or maybe not.
It's hard to tell with only an EXPLAIN.

> HashAggregate  (cost=5895532.37..5895534.35 rows=158 width=32)
>->  Hash Join  (cost=215823.74..5895449.38 rows=5533 width=32)
>  Hash Cond: ("outer".deliveryid = "inner".deliveryid)
>  ->  Seq Scan on sms_new  (cost=0.00..5038183.09 rows=128277444 
> width=8)
>Filter: ((otid)::text !~~ 'ERROR%'::text)
>  ->  Hash  (cost=215823.35..215823.35 rows=158 width=32)
>->  Bitmap Heap Scan on delivery  (cost=2178.24..215823.35 
> rows=158 width=32)
>  Recheck Cond: ((createddate >= '2007-12-11 
> 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
> 23:59:59'::timestamp without time zone))
>  Filter: ((taskid = 1024) AND (((remoteip)::text = 
> '192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR 
> ((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = 
> '192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR 
> ((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = 
> '202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR 
> ((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = 
> '192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR 
> ((remoteip)::text = '202.162.231.7'::text)))
>  ->  Bitmap Index Scan on createddate_idx  
> (cost=0.00..2178.24 rows=195039 width=0)
>Index Cond: ((createddate >= '2007-12-11 
> 00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
> 23:59:59'::timestamp without time zone))   

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
La web junta la gente porque no importa que clase de mutante sexual seas,
tienes millones de posibles parejas. Pon "buscar gente que tengan sexo con
ciervos incendiándose", y el computador dirá "especifique el tipo de ciervo"
(Jason Alexander)

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


   
-
 Now you can chat without downloading messenger. Click here to know how.

Re: [GENERAL] Killing a session in windows

[Thomas H.]

On Tue, Dec 11, 2007 at 05:50:46PM -0500, Tom Lane wrote:

Alvaro Herrera <[EMAIL PROTECTED]> writes:

Howard Cole wrote:
I take it from the lack of response that nobody knows how to kill a 
connection from the postgresql side on windows?

You can't, short of sending a signal to the process or restarting the
service.

Which you can do, no?  I thought pg_ctl's kill option was invented
specifically to make this less painful on Windows.


It does, and it shuold work. But it's just as dangerous as using kill
directly on the backends on Unix, of course.


wasn't the OP asking for a way to kill active connections to a db? afaik 
pgAdmin3 does provide this functionality:


pgadmin3 > tools > server status

there you can easily terminate connections & transactions to a 
particular db. works pretty well in my test, i can kill active 
connections and drop the db afterwards.


regards,
thomas


---(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] Creating Aggregate functions in PLpgSQL

[Martin Gainty]

MG>obligatory toppost

> > http://www.postgresql.org/docs/8.3/static/sql-createaggregate.html
> > is for
> > 8.3 where there were some changes to the number of
> > arguments an aggregate
> > could take.  I believe it's also more descriptive
> > documentation.
> >
> > of course for things like average you wouldn't need a
> > custom type...
>
> Thanks for the information it is very useful.  The reason that I ask, is
that I am trying to develop a way to calculate the average power
factor(scalar) of a Motor Control Center (among other things).  The
Electrical Engineers that are asking for the report introduced me to a
rather complicated formula to implement in standard SQL.
>
> The best way that I can describe the problem get the PF Is this Picofarad
or PetaFarad?

is finding the angle between Xcomp
MG>what is Xcomp?

/hypotenuse after having used Pythagorean theorem to find the hypotenuse
after having summing of multiple 2 coordinate vectors(the EEs use the term
phasers)
MG>set on stun?

 for each MCC cubical.
MG>assume Motor Control Cubical?

>
> It seems they have need for quite a few other little aggregate functions
that they would like me to make if I can get this one done first.
>
> Anyway thank for the push in the right direction!
>
> Regards,
> Richard Broersma Jr.
>
>
> ---(end of broadcast)---
> TIP 6: explain analyze is your friend
>


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

Re: [GENERAL] very slow query

[Ashish Karalkar]

Alvaro Herrera <[EMAIL PROTECTED]> wrote: Ashish Karalkar wrote:

> Thanks for your answer actually that  was  the part of full query here is the 
> actuall plan

I think you are confusing this for "here is a completely different plan
for a completely different query that has nothing to do whatsoever to
what I was asking before".

On this plan it looks like you need an index on otid.  Or maybe not.
It's hard to tell with only an EXPLAIN.

Here is the OUPTPUT from EXPLAIN ANALYSE

 HashAggregate  (cost=5893012.31..5893014.28 rows=158 width=32) (actual 
time=829.511..829.511 rows=0 loops=1)
   ->  Hash Join  (cost=215823.74..5892929.49 rows=5521 width=32) (actual 
time=829.502..829.502 rows=0 loops=1)
 Hash Cond: ("outer".deliveryid = "inner".deliveryid)
 ->  Seq Scan on sms_new  (cost=0.00..5036990.11 rows=128012086 
width=8) (actual time=8.620..8.620 rows=1 loops=1)
   Filter: ((otid)::text !~~ 'ERROR%'::text)
 ->  Hash  (cost=215823.35..215823.35 rows=158 width=32) (actual 
time=820.865..820.865 rows=0 loops=1)
   ->  Bitmap Heap Scan on delivery  (cost=2178.24..215823.35 
rows=158 width=32) (actual time=820.857..820.857 rows=0 loops=1)
 Recheck Cond: ((createddate >= '2007-12-11 
00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
23:59:59'::timestamp without time zone))
 Filter: ((taskid = 1024) AND (((remoteip)::text = 
'192.168.3.26'::text) OR ((remoteip)::text = '202.162.231.230'::text) OR 
((remoteip)::text = '202.162.231.2'::text) OR ((remoteip)::text = 
'192.168.4.3'::text) OR ((remoteip)::text = '192.168.3.3'::text) OR 
((remoteip)::text = '202.162.231.105'::text) OR ((remoteip)::text = 
'202.162.231.5'::text) OR ((remoteip)::text = '202.162.231.1'::text) OR 
((remoteip)::text = '192.168.4.6'::text) OR ((remoteip)::text = 
'192.168.3.6'::text) OR ((remoteip)::text = '202.162.231.107'::text) OR 
((remoteip)::text = '202.162.231.7'::text)))
 ->  Bitmap Index Scan on createddate_idx  
(cost=0.00..2178.24 rows=195039 width=0) (actual time=264.982..264.982 
rows=208124 loops=1)
   Index Cond: ((createddate >= '2007-12-11 
00:00:00'::timestamp without time zone) AND (createddate <= '2007-12-11 
23:59:59'::timestamp without time zone))
 Total runtime: 829.864 ms
(12 rows)


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


   
-
 5, 50, 500, 5000 - Store N number of mails in your inbox. Click here.

Re: [GENERAL] POSIX and libpq

[Richard Huxton]

[EMAIL PROTECTED] wrote:

I've a multithread application running on Linux. In each thread i've a loop
performing a single INSERT operation on the DB.
Sometimes (not always), two of the three thread die apparently without any
reason. The strange thing is that if I remove the store operation from
the threads, all runs fine. Viceversa, if I run the INSERT loops out of the
threads all runs the DB operation are executed without any problems.

On Linux I'm super user and I create the three thread using the posix
function pthread_create. The same code, on windows, doesn't present any
problem (in windows  I use CreateThread function).


It's almost certainly a threading problem. Have you read the relevant 
section of the manuals?

http://www.postgresql.org/docs/8.2/static/libpq-threading.html


--
  Richard Huxton
  Archonet Ltd

---(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] executing a procedure with delay

[Pavel Stehule]

Hello

you can use pg_sleep function. But using it in trigger is ugly,
because transaction stay in open state. Look to orafce on intersession
communication. Maybe you can use it.

http://www.pgsql.cz/index.php/Oracle_functionality_%28en%29

Regards
Pavel Stehule

On 12/12/2007, Ottavio Campana <[EMAIL PROTECTED]> wrote:
> I'd like to execute a stored procedure in postgresql one minute after a
> table has been modified. In case there are two changes in less than one
> minute, I want to reset the time that has to be waited before running
> the procedure.
>
> I think I need to use a trigger, but I don't know how...
>
> Can you help me please?
>
> --
> Non c'e' piu' forza nella normalita', c'e' solo monotonia.
>
>
>

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

Re: [GENERAL] Instaltiating an ARRAY within a function

[Pavel Stehule]

Hello

problem is elsewhere. PostgreSQL doesn't support by ref variables. In
your sample you have to do:

CREATE OR REPLACE FUNCTION callarr()
returns integer
AS
$Z$
DECLARE
 x varchar[6];
BEGIN
x := arr(x); <---!
RAISE NOTICE 'x[1]=%',x[1];
return 0;
end;
$Z$ LANGUAGE 'plpgsql' VOLATILE;

Pavel

On 12/12/2007, Abraham, Danny <[EMAIL PROTECTED]> wrote:
> ===
>
> CREATE OR REPLACE FUNCTION arr( inout x varchar[] )
> AS
> $Z$
> DECLARE
>   i integer;
> BEGIN
> select ARRAY['Danny','Eissam','Moshe'] into x;
> end;
> $Z$ LANGUAGE 'plpgsql' VOLATILE;
>
> ===
>
> CREATE OR REPLACE FUNCTION callarr()
> returns integer
> AS
> $Z$
> DECLARE
>   x varchar[6];
> BEGIN
> perform arr(x);
> RAISE NOTICE 'x[1]=%',x[1];
> return 0;
> end;
> $Z$ LANGUAGE 'plpgsql' VOLATILE;
>
> ===
>
> select callarr();
> NOTICE:  x[1]=  ??? Should have been DANNY
>
>
> Should it work?
>
> Thanks
>
> Danny
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
>

---(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] Multiple clusters on one box for PITR

[Richard Huxton]

Leigh Dyer wrote:


However, I'm running on the Debian Etch packages, which seem to make it 
quite easy to run multiple clusters, enabling me to run PITR just on the 
application that requires it. Would running separate clusters on the one 
box for these two applications be a sensible thing to do? Are there 
resource limits that I should be aware of? Any other issues?


There's no problem running multiple copies of PG on the same box - lots 
of the developers do it, and I do on my laptop here. Different data-dir 
and port and you're away.


Efficiency can be an issue though - you'll have two sets of WAL files 
being written to, and two separate sections of shared memory to balance. 
If you're not pushing the hardware though, it should be straightforward 
enough.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Creating Aggregate functions in PLpgSQL

[Richard Broersma Jr]

--- On Wed, 12/12/07, Martin Gainty <[EMAIL PROTECTED]> wrote:

> MG>what is Xcomp?
I really meant x component = effective component of power as opposed to the 
reactive component of power.


> MG>set on stun?
:o)  I guess a better way to describe the problem is that total power has both 
a real and imaginary component.  So in this way it can be liked to a complex 
numeric value.

> MG>assume Motor Control Cubical?
correct.

There is a power distribution hierarchy that they would like to analyze. 

1) the average pF of all motor in a Motor Control Center (MCC).
2) the average pF of all MCCs that are fed from a Load Center (LC).
3) the average pF of all LC that are fed from the facility main feed.

The pF is measured between 0 and 1.  1 being purely effective power and 0 being 
purge reactive power. The EEs want to identify MCCs or LCs that may need 
Capacitor banks to help offset the effective of reactive power inherent in the 
inductive load of motors.

This is a perfect problem for a custom aggregate.

Regards,
Richard Broersma Jr.

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

[GENERAL] fiori zanella

[Patricia Rodriguez Tome]

We have sent flowers to Zanella this morning, who wants to participate 
-> 4 euros each

p.

--
Dr. Patricia Rodriguez-Tomé, PhD
CRS4 - Bioinformatics
Loc. Pixina Manna Edificio 3 
Pula 09010 (CA), Italy

http://www.bioinformatica.crs4.org



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

Re: [GENERAL] executing a procedure with delay

[A. Kretschmer]

am  Wed, dem 12.12.2007, um 14:43:55 +0100 mailte Pavel Stehule folgendes:
> Hello
> 
> you can use pg_sleep function. But using it in trigger is ugly,
> because transaction stay in open state. Look to orafce on intersession
> communication. Maybe you can use it.

other solution (vaguely): LISTEN/NOFIFY and an external prozess.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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

Re: [GENERAL] Function to convert from TEXT to BYTEA?

[Richard Huxton]

D. Dante Lorenso wrote:

All,

I want to use the ENCRYPT and DECRYPT functions from contrib, but they 
require inputs of BYTEA.


My data is in VARCHAR and TEXT fields and when I try to use the contrib 
functions, they complain about wrong datatypes.  Is there a string 
function or something that will take a VARCHAR or TEXT input and output 
a BYTEA so that I can use that as input for the ENCRYPT/DECRYPT functions?


I know about creating a CAST from VARCHAR to BYTEA, but the problem with 
a CAST is that it doesn't port to other database servers when I do a 
dump and restore. 


Doesn't it?
Hmm... seems to dump for me in 8.2

> That forces me to manually have to recreate the cast
each time a new database is set up and usually that's the step that gets 
forgotten.


Surely you have a script that creates your databases for you?

Is there a function that will do what I want to convert the datatype 
without having to create a CAST that PostgreSQL doesn't have natively? 
How else are you supposed to use the ENCRYPT and DECRYPT functions?


With actual bytea types?

Anyway this will convert for you - PG can get from an unknown quoted 
literal to bytea just fine.


CREATE FUNCTION text2bytea(text) RETURNS bytea AS $_$
DECLARE
  b bytea;
BEGIN
  EXECUTE 'SELECT  ' || quote_literal($1) || '::bytea' INTO b;
  RETURN b;
END
$_$
LANGUAGE plpgsql;

And here's the cast definition that goes with it

CREATE CAST (text AS bytea) WITH FUNCTION public.text2bytea(text);

HTH
--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] index organized tables use case

[Isak Hansen]

On 12/12/07, Enrico Sirola <[EMAIL PROTECTED]> wrote:
> Hello Isak,
>
> Isak Hansen ha scritto:
>
> > Have a look at the cluster operation;
> > .
> >
> > AFAIK it does lock & duplicate the whole table during reordering,
> > which may or may not be an issue for you.
>

Sorry Enrico and list, the respond-to setting on this list gets me every time..


> thanks for the reply; I was aware about this option, anyway I think
> probably it won't be practical: the table is very big and this exclusive
> lock would probably be a pain. I think probably I will start with the
> present relation and then redesign the application at a second stage

This is a long shot, but if the table is huge you could also consider
partitioning. That really depends on the contents of 'code', though..

See .


Isak

> Thanks,
> e.
>

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

Re: [GENERAL] Slow PITR restore

[Simon Riggs]

On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:
> "Joshua D. Drake" <[EMAIL PROTECTED]> writes:
> > ... Now I understand that restoring log files can be slow but this is a big 
> > machine.
> 
> Yeah, restoring is known to be less than speedy, because essentially
> zero optimization work has been done on it.

If there was a patch to improve this, would it be applied to 8.3?

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


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

   http://archives.postgresql.org/

Re: [GENERAL] partitioned table query question

[Robert Treat]

On Monday 10 December 2007 20:01, Erik Jones wrote:
> Again, though, is there some better way to go about implementing some
> kind of hash based partitioning in postgres besides this that would
> be more natural wrt queries?
>

One way is to set a static bin id for each partition, then do a select with 
where bin_id = mod(2112,3);   Where 2112 equals the number your looking for, 
and 3 would be the number of buckets. 

-- 
Robert Treat
http://www.omniti.com/
We're Big. On PostgreSQL.

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

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

[GENERAL] convert function

[Jan Sunavec]

Hi all

I have problem with "convert" function. Previous behaviour was
SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
===
jan

In postgresql 8.3 is quite new behaviour.
SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
==
"j\241n"

This, drives me crazy. I mean, this is not useable for non english  
country. I don't need convert to \241 characters. I understand that  
someone need this behavour. But there should be possibility switch to  
"normal" behaviour.


  John

---(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] very slow query

[Ashish Karalkar]

"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am  Wed, dem 12.12.2007, um 12:25:20 
+ mailte Ashish Karalkar folgendes:
> > here is the output from planner:
> > Nested Loop IN Join (cost=0.00..5030217.97 rows=2 width=106)
> > -> Seq Scan on sms_new (cost=0.00..5027902.00 rows=384 width=106)
> > Filter: (mobile = 919820920858::bigint)
> 
> Do you have an index on "mobile"? Can you show us the table definition
> and the output from EXPLAIN ANALYSE?
> 
> no index on mobile

Seq Scan on sms_new ... Filter: (mobile = 919820920858::bigint)
(cost=0.00..5027902.00


Can you see the problem?

So i will have to create index on mobile is taht so?


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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


   
-
 Unlimited freedom, unlimited storage. Get it now

[GENERAL] WHERE (columnX IN (x,y,z)) ORDER BY columnY conflict

[Jay]

I've been trying to get this query to work to no avail. Both parts
work in isolation. I am able to replace the IN clause with an ILIKE
statement and the ORDER BY works fine. But in combination with the IN
statement, the ORDER BY statement does not actually order the results,
though no errors are thrown and the IN section returns results as
expected.  Am I missing something simple here? I haven't been able to
turn up any results on google...

Thanks,

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

Re: [GENERAL] Understanding Aliases

[Vyacheslav Kalinin]

> SELECT-list output aliases name the *output* columns of the SELECT.
> Those output columns are not available to the SELECT's computation
> clauses

Then it's unclear how could the first query work

Re: [GENERAL] Improving the timing of a query

[Matthew Pulis]

Thanks for your suggestions :)

Even though the ideas are good, the function is working really fast now,
1.5-2seconds which is quite enough I guess :)

This is how the function is at the moment :
http://yancho.pastebin.com/d236fd97f

Sorry I forgot to update this thread :(

On 12/7/07, Martijn van Oosterhout <[EMAIL PROTECTED]> wrote:
>
> On Fri, Dec 07, 2007 at 12:07:52PM -0500, [EMAIL PROTECTED] wrote:
> > other DB's do FTS when there is a function involved in the predicate
> (WHERE
> > clause)
> > so a possible workaround would be to look at all function calls in your
> > predicate (WHERE clause) and
> > populate a new column with the results of the function(column)
> > and then create and populate an index which will reference the
> > function(column)
>
> You don't need to create a column, yu can create functional indexes in
> postgres:
>
> CREATE INDEX foo ON bar ((myfunction(column)));
>
> And it can be used anytime your query says: WHERE myfunction(column) = X
>
> Have a nice day,
> --
> Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> > Those who make peaceful revolution impossible will make violent
> revolution inevitable.
> >  -- John F Kennedy
>
> -BEGIN PGP SIGNATURE-
> Version: GnuPG v1.4.1 (GNU/Linux)
>
> iD8DBQFHWYaJIB7bNG8LQkwRAmOyAJ92ZtXFiQfme/Rn2+2ylISrR3YM+ACfTWO8
> ftUieOQhrQCQy+rBAElRr68=
> =ALYk
> -END PGP SIGNATURE-
>
>


-- 
Matthew Pulis
URL : http://www.solutions-lab.net
MSN : [EMAIL PROTECTED]
ICQ : 145951110
Skype : solutions-lab.net

[GENERAL] How can I insert NULL into column with the type of timestamp?

[bookman bookman]

H i ,

I want to tansfer a database from sqlserver2005 to postgresql8.2.5.I
use bcp to export a table named admin in sqlserver to a text file:

--table T_admin
id name   key  regDate
 isLock  realName
1   rison 9988772007-08-27 10:24:57 False  admin
2   lijun 778899NULLFalse   
  NULL
3   guanliyuan112007-11-05 10:30:08 False  
myAdmin

   --admin.txt
id   name  key   regDate
  isLockrealname
1   ris 998877  2007-08-27 10:24:57.000 0   admin
2   lij 778899  0   
3   guanliyuan  11  2007-11-05 10:30:08.813 0   myAdmin

I created a table in postgresql,and I use "copy" to import datas

  create table T_Admin(
adminID serial not null primary key,
name varchar(30) null,
key varchar(30) null,
regDate timestamp null,
isLock bool null,
realName varchar(30) null
)

  copy admin from "/home/postgres/data/admin.txt"

Then error occured:
error:invalid input syntax for type timestamp:""
context:copy T_Admin ,line 2,column regDate:""
It seemed that the column REGDATE cannot accept a NULL.I tested it use:
   insert into T_Admin(name,key,regDate,isLock,realName)
   values('aaa','aaa','','1','aaa');
The same error occured.

  So it means that the column with type timestamp cannot accept a NULL
.Is there any way I can tansfer this table into postgre?How can i deal
with NULL in this case?
   Thank you!

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

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

[GENERAL] about the performance of autovacuum and vacuumdb?

[Charles.Hou]

i try to compare two methods of garbage-collect.(Postgresql Ver:8.1.3)

1. enable autovacuum without using vacuumdb -f mydb in crontab.
2. using crontab to vacuumdb in every 10 min (autovacuum disable)

the method 2 got more free space than method 1.

the disk size of database still  increased in method 1.
so, what's the advantage of autovacuum?

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

   http://archives.postgresql.org/

Re: [GENERAL] very slow query

[Ashish Karalkar]

"A. Kretschmer" <[EMAIL PROTECTED]> wrote: am  Wed, dem 12.12.2007, um 11:44:58 
+ mailte Ashish Karalkar folgendes:
> Hi,
> I am having PostgreSQL 8.2.4 on Suse 10.3
> 
> Server is not using the index insted it chooses to take seq scan path. table 
> is
> having @ 120 million rows
> 
> here is the output from planner:
> Nested Loop IN Join  (cost=0.00..5030217.97 rows=2 width=106)
>->  Seq Scan on sms_new  (cost=0.00..5027902.00 rows=384 width=106)
>  Filter: (mobile = 919820920858::bigint)
>->  Index Scan using deliveryid_pkey on delivery  (cost=0.00..6.02 rows=1
> width=8)
>  Index Cond: ("outer".deliveryid = delivery.deliveryid)
>  Filter: ((createddate > '2007-12-11 00:00:00'::timestamp without time
> zone) AND (createddate < '2007-12-11 21:00:00'::timestamp without time zone)
> AND ((keyword)::text = 'CRI'::text))
> 
> column deliveryid has an index over it in sms_new table.
> 
> can anybody please guide to force the usage of index


It's using an index on "deliveryid":
Index Scan using deliveryid_pkey


It is using index on deliveryid of table delivery but not of sms_new which is 
having 120 million rows.

deliveryid is also present in sms_new having index on it. instead of using 
index on sms_new its doing seq scan. I want to force index scan on sms_new

Do you have an index on "mobile"? Can you show us the table definition
and the output from EXPLAIN ANALYSE?

no index on mobile

output of EXPLAIn ANALYSE taking very long time.


Andreas
-- 
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net

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


   
-
 Did you know? You can CHAT without downloading messenger.  Click here

Re: [GENERAL] Pg_catalog reference

[Vyacheslav Kalinin]

It is not possible to assign triggers to system tables hence it is
impossible to reference system table in FK constraint. Obviously, users that
don't exist in the database cannot modify anything, you can use
"session_user" and "current_user" functions for logging purposes, see
http://www.postgresql.org/docs/8.2/interactive/functions-info.html

[GENERAL] copy a large table raises out of memory exception

[A. Ozen Akyurek]

We have a large table (about 9,000,000 rows and total size is about 2.8 GB)
which is exported to a binary file. Postgre 8.2 is running on a Windows 2003
Small business Server which has a 2 GB RAM. When we run "copy tablename from
filepath" command, memory usage increases up to 1.8 GB and postgre raises
exception "out of memory". If we copy a small part of the table (e.g
1,000,000 rows) everything works fine. 

As far as I understand, postgre is trying to load all the rows into RAM
before writing it to the database. I tried running postgre with several
different configuration parameters but the result is same. Did anybody face
a similar problem?

Kind regards
A. Ozen Akyurek



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

[GENERAL] Better alternative for Primary Key then serial??

[pilzner]

Hi - I'm new to PostGres, but have used MSSQL for about a year. I'm going
through the documentation, but after reading about serials have a lot of
worries about keeping referential integrity in place and other things.
Specifically, here are a few scenarios:

a.) 
CREATE TABLE TestTable (
TestID SERIAL NOT NULL PRIMARY KEY, 
TestData varchar(20) NOT NULL
);

INSERT INTO TestTable(TestData) VALUES ('Data1');
INSERT INTO TestTable(TestData) VALUES ('Data2');
INSERT INTO TestTable(TestData) VALUES ('Data3');

UPDATE TestTable SET TestID = 10 WHERE TestID = 1;


Ok, red flag for me right here. The above works just fine. Granted, if
another table referenced the row w/ TestID = 1, it should violate foreign
key constraints and error out. However, with the use of serial, this is
going to run into another problem, down the road right?? Demonstrated here:

b.) 
CREATE TABLE TestTable2 (
TestID SERIAL NOT NULL PRIMARY KEY, 
TestData varchar(20) NOT NULL
);

INSERT INTO TestTable2(TestID, TestData) VALUES (1, 'DataData');

INSERT INTO TestTable(TestData) VALUES ('NextData');
--duplicate key violation occurs

INSERT INTO TestTable(TestData) VALUES ('NextData');
--Works fine

To phrase what happens, the next number from serial is '1', but that number
was already explicitly entered. The next call works, because the next serial
number is '2'. Ideally, the first insert would -never- happen and TestID
wouldn't ever be explicitly given a value, but if it were, its a guaranteed
error down the road some ways. 

Does stuff like this cause any aches and pains to developers out there, or
do I just need to get in a new mindset??? Also, is there a way to be sure
the primary key is *ONLY* ever given a value by serial, and not subject to
updates???

Thanks, 




-- 
View this message in context: 
http://www.nabble.com/Better-alternative-for-Primary-Key-then-serial---tp14289409p14289409.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Trigger - will not perform INSERT

[smiley2211]

Ok, thanks...I will do some more testing and see what I get...

-- 
View this message in context: 
http://www.nabble.com/Trigger---will-not-perform-INSERT-tp14282848p14296002.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

[GENERAL] Would it be OK if I put db file on a ext2 filesystem?

[Magicloud Wang]

Dear,
I think database has its own operation journal, and different journal 
filesystem does give different performance. So if I put database file on a 
non-journal filesystem, would it be safe? Does this like using a raw device?

Thanks.

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

   http://archives.postgresql.org/

Re: [GENERAL] Hijack!

[Gregory Stark]

"Alvaro Herrera" <[EMAIL PROTECTED]> writes:

> Thomas Kellerer wrote:
>> Joshua D. Drake, 11.12.2007 17:43:
>>> O.k. this might be a bit snooty but frankly it is almost 2008. If you
>>> are still a top poster, you obviously don't care about the people's
>>> content that you are replying to, to have enough wits to not top post.
>>
>> I personally find non-trimmed bottom postings at lot more annoying than 
>> top-postings. But then that's probably just me.
>
> It's not just you.  Much as I am annoyed by top-posting, I am much more
> so by people who top-post at the bottom.  Hey, did I say something
> stupid?  No -- think about it.  These guys do exactly the same thing as
> top-posters, except it is much worse because the actual text they wrote
> is harder to find.
>
> -- 
> Alvaro Herrera http://www.flickr.com/photos/alvherre/
> "In fact, the basic problem with Perl 5's subroutines is that they're not
> crufty enough, so the cruft leaks out into user-defined code instead, by
> the Conservation of Cruft Principle."  (Larry Wall, Apocalypse 6)
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq

I agree.

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

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

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

Re: [GENERAL] index organized tables use case

[Richard Huxton]

Enrico Sirola wrote:

The application code usually performs selection queries like

select tag, value from test where code='XXX';

also, deletions are like

delete from test where code='XXX';

and insertions follow the same pattern (all the data for a code is
inserted using a loop in a single transaction). That's more or less all.

so this type of workload is greatly enhanced by an index-organized table
(oracle) or a clustered index (SQL Server/Sybase).


Hmm - I'm not sure it does benefit that much. I mean, if you're going to 
be accessing XXXA, then XXXB, XXXC etc. in order then it clearly helps 
to have the table with the same order as your primary key. Otherwise, 
I'd be doubtful you'd see that much benefit.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Hijack!

[statman]

Gregory Williamson wrote:


Peter Childs caused electrons to shape a message:
> 
> On 11/12/2007, Obe, Regina <[EMAIL PROTECTED]> wrote:

> >
> >  Well said Greg.  I have the same problem too of having a crippled 
mail
> > reader :)  Really I find mid posting hard to follow especially if 
I'm the
> > one that posted the question.  I hope we aren't going to hit 
people with
> > hammers over this minor infraction.  It really makes one feel 
unwelcome.

> >
> > I guess we have beaten this horse enough though.
> >
>  --
>
> Hmm Can't stop laughing I think you managed to break every rule in 
the book

> with that post.
>
> Peter.

And as they say where I come from, there is _no_ point to beating a 
dead horse, aside from the sheer joy of the thing.


Seriously -- "top posting bad, bottom posting good" also misses all 
kinds of points -- intelligent quoting and interspersing comments / 
answers where they belong is the ticket, when it can be done. Well, 
off to top post on some other forums ... ;-)


Greg W.
yadda yadda

 Should that not be "Well, off to post on some other 
fora"?  8¬>


Mike

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

  http://archives.postgresql.org/

Re: [GENERAL] Determining current block size?

[Patrick TJ McPhee]

In article <[EMAIL PROTECTED]>,
John Wells <[EMAIL PROTECTED]> wrote:
% I see that BLOCK_SIZE can be set at compile time, but is there a way
% to determine what block size is in use in a running system? I've been
% searching but have been unsuccessful so far.

show block_size;

If you try to start the database with a postmaster compiled with the
wrong block size, the error message tells you what block size you need.

-- 

Patrick TJ McPhee
North York  Canada
[EMAIL PROTECTED]

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

   http://archives.postgresql.org/

[GENERAL] VTD-XML Tutorial by Code Examples Posted Options

[[EMAIL PROTECTED]]

The C version: 
http://downloads.sourceforge.net/vtd-xml/c_tutorial_by_code_examples

The C# version: 
http://downloads.sourceforge.net/vtd-xml/CSharp_tutorial_by_code_exam...

The Java version: 
http://downloads.sourceforge.net/vtd-xml/Java_tutorial_by_code_exampl...

Also some latest articles:

Schemaless Java-XML databinding with VTD-XML
http://www.onjava.com/pub/a/onjava/2007/09/07/schema-less-java-xml-da...

Index XML documents with VTD-XML
http://webservices.sys-con.com/read/453082.htm

Improve XPath Efficiency with VTD-XML
http://www.devx.com/xml/Article/34045





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

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

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

[Sam Mason]

On Tue, Dec 11, 2007 at 10:29:04AM +0800, bookman bookman wrote:
>   So it means that the column with type timestamp cannot accept a NULL
> .Is there any way I can tansfer this table into postgre?How can i deal
> with NULL in this case?

NULL values are encoded as an unquoted \N by default in PG.  You've got
a few ways of fixing things then.  Tell MS-SQL to do the same, write a
sed script to do the translation, or use the "NULL AS 'NULL'" option in
the COPY command.


  Sam

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

Re: [GENERAL] Slow PITR restore

[Joshua D. Drake]

Simon Riggs wrote:

On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:

"Joshua D. Drake" <[EMAIL PROTECTED]> writes:
... Now I understand that restoring log files can be slow but this is a big 
machine.

Yeah, restoring is known to be less than speedy, because essentially
zero optimization work has been done on it.


If there was a patch to improve this, would it be applied to 8.3?



Sheesh Simon you are really pushing this release :). I would love to see 
a patch to resolve this, especially since it appears to be a fairly 
glaring oversight. We can't really expect people to use PITR if they new 
it would take hours to recover even on the size of machine I was working on.


On the other hand... we are about to go to RC1 :)

Joshua D. Drake

---(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] Southern California Linux Expo

[Joshua D. Drake]

Richard Broersma Jr wrote:

Are there any planes in the works for a booth and talks for PostgreSQL?


This is already being discussed on the advocacy list :)

Joshua D. Drake



Regards,
Richard Broersma Jr.

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




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

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

[GENERAL] slony1 replication question

[SHARMILA JOTHIRAJAH]

Hi,
I need some help with slony...

I read the slony manual and tried to replicate the database as given in this... 
http://www.slony.info/documentation/firstdb.html


These are the steps that I did.
1. set the shell variables like clustername,masterdbname etc thro export command
2. created a user 'josh' with 'somepassword'
3. createdb -O $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME

createdb -O $PGBENCHUSER -h $SLAVEHOST $SLAVEDBNAME
pgbench -i -s 1 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME
4.createlang -h $MASTERHOST plpgsql $MASTERDBNAME
5.pg_dump -s -U $REPLICATIONUSER -h $MASTERHOST $MASTERDBNAME | psql -U 
$REPLICATIONUSER -h $SLAVEHOST $SLAVEDBNAME

6. pgbench -s 1 -c 5 -t 1000 -U $PGBENCHUSER -h $MASTERHOST $MASTERDBNAME

7.
slony1 was configured using --perltools option. I copied
slon_tools.conf-sample to slon_tools.conf and placed it in the /etc
dir. This is my conf file (deleted the commented lines here for
clarity)


-
if ($ENV{"SLONYNODES"}) {
require $ENV{"SLONYNODES"};
} else {
   
$CLUSTER_NAME = 'slony_example';

$LOGDIR = 'export/home/josh/slony1';

$MASTERNODE = 1;

add_node(node => 1,
 host => 'localhost',
 dbname   => 'pgbench',
 port => 5432,
 user => 'josh',

 password => 'somepassword');

add_node(node => 2,
 host => 'localhost',
 dbname   => 'pgbenchslave',
 port => 5432,

 user => 'josh',
 password => 'somepassword');
}
$SLONY_SETS = {
"set1" => {
"set_id" => 1,
"table_id"=> 1,

"sequence_id" => 1,
"pkeyedtables" => [
  'accounts',
  'tellers',
  'branches',

  ],
"serialtables" => ["history"],
},
};
if ($ENV{"SLONYSET"}) {
require $ENV{"SLONYSET"};
}
1;
--


8. slonik_init_cluster | slonik
:10: Set up replication nodes
:13: Next: configure paths for each node/origin
:16: Replication nodes prepared
:17: Please start a slon replication daemon for each node


9. slon_start 1
..
Slon successfully started for cluster slony_example, node node1
Start the watchdog process as well...

10.slon_start 2
...
Slon successfully started for cluster slony_example, node node2

PID [16697]
Start the watchdog process as well...

11. slonik_create_set 1
cluster name = slony_example;
 
.
  echo 'All tables added';

12. slonik_subscribe_set 1  2 | slonik

:4: PGRES_FATAL_ERROR select "_slony_example".subscribeSet(1, 1, 2, 
't');  - ERROR:  Slony-I: subscribeSet(): set 1 not found

What is this error? What am I doing wrong here? When i check the pgbenchslave  
database, the table rows are not replicated?

Please advice
Thanks
josh




  

Looking for last minute shopping deals?  
Find them fast with Yahoo! Search.  
http://tools.search.yahoo.com/newsearch/category.php?category=shopping

Re: [GENERAL] Better alternative for Primary Key then serial??

[Adrian Klaver]

On Tuesday 11 December 2007 9:42 pm, pilzner wrote:
> Hi - I'm new to PostGres, but have used MSSQL for about a year. I'm going
> through the documentation, but after reading about serials have a lot of
> worries about keeping referential integrity in place and other things.
> Specifically, here are a few scenarios:
>
> a.)
> CREATE TABLE TestTable (
> TestID SERIAL NOT NULL PRIMARY KEY,
> TestData varchar(20) NOT NULL
> );
>
> INSERT INTO TestTable(TestData) VALUES ('Data1');
> INSERT INTO TestTable(TestData) VALUES ('Data2');
> INSERT INTO TestTable(TestData) VALUES ('Data3');
>
> UPDATE TestTable SET TestID = 10 WHERE TestID = 1;
>
>
> Ok, red flag for me right here. The above works just fine. Granted, if
> another table referenced the row w/ TestID = 1, it should violate foreign
> key constraints and error out. However, with the use of serial, this is
> going to run into another problem, down the road right?? Demonstrated here:
>
> b.)
> CREATE TABLE TestTable2 (
> TestID SERIAL NOT NULL PRIMARY KEY,
> TestData varchar(20) NOT NULL
> );
>
> INSERT INTO TestTable2(TestID, TestData) VALUES (1, 'DataData');
>
> INSERT INTO TestTable(TestData) VALUES ('NextData');
> --duplicate key violation occurs
>
> INSERT INTO TestTable(TestData) VALUES ('NextData');
> --Works fine
>
> To phrase what happens, the next number from serial is '1', but that number
> was already explicitly entered. The next call works, because the next
> serial number is '2'. Ideally, the first insert would -never- happen and
> TestID wouldn't ever be explicitly given a value, but if it were, its a
> guaranteed error down the road some ways.
>
> Does stuff like this cause any aches and pains to developers out there, or
> do I just need to get in a new mindset??? Also, is there a way to be sure
> the primary key is *ONLY* ever given a value by serial, and not subject to
> updates???
>
> Thanks,

Each table that has SERIAL column created gets its own sequence, so there will 
be no conflict between tables. That case would only arise if you assigned the 
same sequence to multiple tables using DEFAULT nextval("some_sequence") and 
mixed manual updating of the sequence and auto updating.  By default a 
sequence will always increment forward so you will have a fresh number for 
the next request. This means a sequence can have holes as it increments even 
if a transaction fails. You can create a duplicate key violation within a 
single table by manualling entering a SERIAL id that was already generated. 
This applies to any PRIMARY KEY and is sort of the point. The best thing to 
do is let the SERIAL sequence work on it own.  If you want to deal with 
sequences you should take a look at:
http://www.postgresql.org/docs/8.2/interactive/functions-sequence.html

As to preventing updates. You have a couple of choices. 
1)Do not let that field be changed by the user. I usually in either hid the 
field or prevented data entry on that field.
2) Create an ON UPDATE TRIGGER that does what you want with the field.

-- 
Adrian Klaver
[EMAIL PROTECTED]

---(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] Would it be OK if I put db file on a ext2 filesystem?

[Collin Kidder]

Magicloud Wang wrote:

Dear,
	I think database has its own operation journal, and different journal 
filesystem does give different performance. So if I put database file on a 
non-journal filesystem, would it be safe? Does this like using a raw device?



  
You lose a little bit of data integrity in exchange for a little bit of 
speed. I suppose it'd be a fine thing to do so long as you can live with 
that trade off. If you want good data integrity you are more likely to 
get it from battery backed RAID5 or RAID10 or something of that sort 
rather than just trusting something like EXT3 or Reiser. EXT2 isn't a 
bad file system.


---(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 #1: top posting (was: [GENERAL] Hijack!)

[Robert Treat]

You criticize that Joshua's reply was dogmatism but was yours any better?

I think people can see through these weak ad hominem arguments; no matter how 
much you try to cast the technique in a negative light, that doesn't really 
make it wrong, and in fact, there are many reasons to encourage people to do 
it (bandwidth saving alone is one benefit)

Adding something to the FAQ/Subscribe message certainly couldnt hurt.

On Tuesday 11 December 2007 12:23, Andrew Sullivan wrote:
> On Tue, Dec 11, 2007 at 09:00:05AM -0800, Joshua D. Drake wrote:
> > -BEGIN PGP SIGNED MESSAGE-
> > Hash: SHA1
> >
> > On Tue, 11 Dec 2007 11:49:54 -0500
> >
> > Andrew Sullivan <[EMAIL PROTECTED]> wrote:
> > > On a mailing list, perhaps one can argue that the conventions simply
> > > have to be followed.  But I know I find it pretty annoying to get 36
> > > lines of quoted text followed by something like, "No: see the manual,
> > > section x.y.z."
> >
> > That is what  is for :)
> >
> > > I don't think top posting is always the crime it's made to be (and I
> > > get a little tired of lectures to others about it on these lists).
> >
> > I can appreciate that but regardless of various "opinions" (mine
> > included). It is the PostgreSQL communities decision and I believe
> > except for newbies and a few long timers who should know better,
> > everyone avoids top posting.
> >
> > Top posting makes it hard to read.
> >
> > Sincerely,
> >
> > Joshua D. Drake
>
> Simply replying to an argument with an assertion to the contrary is, I
> think, dogmatism.  The argument for top posting is that it is _easier_ to
> read for certain kinds of cases.  I have already rehearsed those arguments;
> I think they are both sound and valid, but they don't consider every
> situation, and so they also lead to a wrong conclusion sometimes.
>
> I would argue that this message is harder to read than if I'd just replied
> at the top.  It's pointlessly long -- but without including everything, you
> wouldn't have all the context, and you might have missed something.  (The
> context argument is, of course, the usual one favoured by
> call-and-response/"bottom posting" advocates.  So, your context is above.)
>
> As for the "snip" claim, it has several problems:
>
> 1.It is easy, by injudicious, careless, or malicious use of cutting
> from others' posts, to change the main focus of their argument, and thereby
> draw the thread in a completely new direction.
>
> 2.Owing to (1), snipping is a favourite tactic of trollers.
>
> 3.Owing to (1), snipping is a favourite target for cranks, who
> immediately turn such threads into long _ad hominems_ about the malicious
> slurs being heaped on them by others.
>
> 4.Poor editors often obscure enough in their editing that they provide
> no more elucidation than nothing, and rather less than there might be with
> a top-posted response and a complete copy of the earlier message below it.
>
> I can, of course, produce equally good arguments for not top posting.  My
> point is not that we should change the convention; but rather, that we
> should accept that this is a convention and nothing more.  It makes reading
> easier for you because it's the convention with which you're familiar.  If
> you were used to the alternative, you'd find this convention annoying and
> pointlessly noisy.
>
> I think it's worthwhile putting a note in the welcome-to-new-subscribers
> that this community doesn't like top posting, and that top posting may well
> cause your messages to be ignored.  Those claims are both true, and we
> don't need to justify it with jumped-up claims about the objective
> superiority of one method over another.  I think we should also avoid being
> too doctrinaire about it.
>
> A
>
> ---(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

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

[Pavel Stehule]

Hello

use simply NULL

postgres=# create table bb(a timestamp, b integer);
CREATE TABLE
postgres=# insert into bb values(null, null);
INSERT 0 1
postgres=# insert into bb values(null, 10);
INSERT 0 1
postgres=# insert into bb values(current_timestamp, 10);
INSERT 0 1
postgres=# copy bb to stdout;
\N  \N
\N  10
2007-12-12 16:48:28.122776  10
or
postgres=# copy bb to stdout with null as 'null';
nullnull
null10
2007-12-12 16:48:28.122776  10

Regards
Pavel Stehule


On 11/12/2007, bookman bookman <[EMAIL PROTECTED]> wrote:
> H i ,
>
> I want to tansfer a database from sqlserver2005 to postgresql8.2.5.I
> use bcp to export a table named admin in sqlserver to a text file:
>
> --table T_admin
> id name   key  regDate
>  isLock  realName
> 1   rison 9988772007-08-27 10:24:57 False  
> admin
> 2   lijun 778899NULLFalse 
> NULL
> 3   guanliyuan112007-11-05 10:30:08 False  
> myAdmin
>
>--admin.txt
> id   name  key   regDate
>   isLockrealname
> 1   ris 998877  2007-08-27 10:24:57.000 0   admin
> 2   lij 778899  0
> 3   guanliyuan  11  2007-11-05 10:30:08.813 0   myAdmin
>
> I created a table in postgresql,and I use "copy" to import datas
>
>   create table T_Admin(
> adminID serial not null primary key,
> name varchar(30) null,
> key varchar(30) null,
> regDate timestamp null,
> isLock bool null,
> realName varchar(30) null
> )
>
>   copy admin from "/home/postgres/data/admin.txt"
>
> Then error occured:
> error:invalid input syntax for type timestamp:""
> context:copy T_Admin ,line 2,column regDate:""
> It seemed that the column REGDATE cannot accept a NULL.I tested it use:
>insert into T_Admin(name,key,regDate,isLock,realName)
>values('aaa','aaa','','1','aaa');
> The same error occured.
>
>   So it means that the column with type timestamp cannot accept a NULL
> .Is there any way I can tansfer this table into postgre?How can i deal
> with NULL in this case?
>Thank you!
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/docs/faq
>

---(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] How can I insert NULL into column with the type of timestamp?

[Obe, Regina]

Actually what you are doing below is trying to stuff '' in a timestamp
field.

Keep in mind '' and NULL are not the same.  ''  is invalid for timestamp
where as NULL is fine.  Your example should be  
 insert into T_Admin(name,key,regDate,isLock,realName)
   values('aaa','aaa',NULL,'1','aaa');

I think the bcp is trying to insert 'NULL' instead of NULL.  

According to the docs - looks like you can tell copy that
http://www.postgresql.org/docs/techdocs.15

So my guess is you should do
copy t_admin from "/home/postgres/data/admin.txt" USING DELIMITERS '\t'
WITH NULL As 'NULL' 

Hope that helps,
Regina



-Original Message-
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of bookman bookman
Sent: Monday, December 10, 2007 9:29 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] How can I insert NULL into column with the type of
timestamp?

H i ,

I want to tansfer a database from sqlserver2005 to postgresql8.2.5.I
use bcp to export a table named admin in sqlserver to a text file:

--table T_admin
id name   key  regDate
 isLock  realName
1   rison 9988772007-08-27 10:24:57 False
admin
2   lijun 778899NULL
False NULL
3   guanliyuan112007-11-05 10:30:08 False
myAdmin

   --admin.txt
id   name  key   regDate
  isLockrealname
1   ris 998877  2007-08-27 10:24:57.000 0   admin
2   lij 778899  0   
3   guanliyuan  11  2007-11-05 10:30:08.813 0
myAdmin

I created a table in postgresql,and I use "copy" to import datas

  create table T_Admin(
adminID serial not null primary key,
name varchar(30) null,
key varchar(30) null,
regDate timestamp null,
isLock bool null,
realName varchar(30) null
)

  copy admin from "/home/postgres/data/admin.txt"

Then error occured:
error:invalid input syntax for type timestamp:""
context:copy T_Admin ,line 2,column regDate:""
It seemed that the column REGDATE cannot accept a NULL.I tested it use:
   insert into T_Admin(name,key,regDate,isLock,realName)
   values('aaa','aaa','','1','aaa');
The same error occured.

  So it means that the column with type timestamp cannot accept a NULL
..Is there any way I can tansfer this table into postgre?How can i deal
with NULL in this case?
   Thank you!

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

   http://www.postgresql.org/docs/faq
-
The substance of this message, including any attachments, may be
confidential, legally privileged and/or exempt from disclosure
pursuant to Massachusetts law. It is intended
solely for the addressee. If you received this in error, please
contact the sender and delete the material from any computer.


---(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] index organized tables use case

[Thomas Kellerer]

Richard Huxton, 12.12.2007 16:12:
Hmm - I'm not sure it does benefit that much. I mean, if you're going to 
be accessing XXXA, then XXXB, XXXC etc. in order then it clearly helps 
to have the table with the same order as your primary key. Otherwise, 
I'd be doubtful you'd see that much benefit.




At least for Oracle it's not mainly the order that improves the 
performance, but the fact that all the data is kept in the index, so 
Oracle does not need to go back to the table data after looking up the 
index entry. There is no "table data" for an index-organized table in 
Oracle, so only a single lookup is needed.


Thomas


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

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

Re: [GENERAL] How can I insert NULL into column with the type of timestamp?

[Alvaro Herrera]

bookman bookman escribió:

> Then error occured:
> error:invalid input syntax for type timestamp:""
> context:copy T_Admin ,line 2,column regDate:""
> It seemed that the column REGDATE cannot accept a NULL.I tested it use:
>insert into T_Admin(name,key,regDate,isLock,realName)
>values('aaa','aaa','','1','aaa');
> The same error occured.
> 
>   So it means that the column with type timestamp cannot accept a NULL
> .Is there any way I can tansfer this table into postgre?How can i deal
> with NULL in this case?

No, it doesn't mean that.  It means that you are trying to insert an
empty string.  Try this:

insert into T_Admin(name,key,regDate,isLock,realName)
values('aaa','aaa',NULL,'1','aaa');

Similarly, the NULL timestamp column in the file you give to COPY should
not contain empty quotes.

-- 
Alvaro Herrerahttp://www.advogato.org/person/alvherre
"Crear es tan difícil como ser libre" (Elsa Triolet)

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

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

Re: [GENERAL] Better alternative for Primary Key then serial??

[Alvaro Herrera]

pilzner wrote:

> Does stuff like this cause any aches and pains to developers out there, or
> do I just need to get in a new mindset??? Also, is there a way to be sure
> the primary key is *ONLY* ever given a value by serial, and not subject to
> updates???

It doesn't.  Just do not update the ID -- what use do you have for that
anyway?  If you want to prevent it, you can put a trigger to the column,
but IMHO it would be a waste of your time and machine resources.

-- 
Alvaro Herrera http://www.flickr.com/photos/alvherre/
"Vivir y dejar de vivir son soluciones imaginarias.
La existencia está en otra parte" (Andre Breton)

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

Re: [GENERAL] Hijack!

[Leif B. Kristensen]

me too.

On Wednesday 12. December 2007, Gregory Stark wrote:
>"Alvaro Herrera" <[EMAIL PROTECTED]> writes:
>> Thomas Kellerer wrote:
>>> Joshua D. Drake, 11.12.2007 17:43:
 O.k. this might be a bit snooty but frankly it is almost 2008. If
 you are still a top poster, you obviously don't care about the
 people's content that you are replying to, to have enough wits to
 not top post.
>>>
>>> I personally find non-trimmed bottom postings at lot more annoying
>>> than top-postings. But then that's probably just me.
>>
>> It's not just you.  Much as I am annoyed by top-posting, I am much
>> more so by people who top-post at the bottom.  Hey, did I say
>> something stupid?  No -- think about it.  These guys do exactly the
>> same thing as top-posters, except it is much worse because the
>> actual text they wrote is harder to find.
>>
>> --
>> Alvaro Herrera
>> http://www.flickr.com/photos/alvherre/ "In fact, the basic problem
>> with Perl 5's subroutines is that they're not crufty enough, so the
>> cruft leaks out into user-defined code instead, by the Conservation
>> of Cruft Principle."  (Larry Wall, Apocalypse 6)
>>
>> ---(end of
>> broadcast)--- TIP 3: Have you checked our
>> extensive FAQ?
>>
>>http://www.postgresql.org/docs/faq
>
>I agree.



-- 
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---(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] Instaltiating an ARRAY within a function

[Albe Laurenz]

Danny Abraham wrote:
> ===
> 
> CREATE OR REPLACE FUNCTION arr( inout x varchar[] )
> AS
> $Z$
> DECLARE
>   i integer;
> BEGIN
> select ARRAY['Danny','Eissam','Moshe'] into x;
> end;
> $Z$ LANGUAGE 'plpgsql' VOLATILE;
> 
> ===
> 
> CREATE OR REPLACE FUNCTION callarr()
> returns integer
> AS
> $Z$
> DECLARE
>   x varchar[6]; 
> BEGIN
> perform arr(x);
> RAISE NOTICE 'x[1]=%',x[1];
> return 0;
> end;
> $Z$ LANGUAGE 'plpgsql' VOLATILE;
> 
> ===
> 
> select callarr();
> NOTICE:  x[1]=  ??? Should have been DANNY
> 
> 
> Should it work?

Not the way you wrote it.
You are confused by output parameters which do not work the way one
might expect. They are just a simple syntax for composite return types.

CREATE FUNCTION arr(INOUT x varchar[])
is synonymous to
CREATE FUNCTION arr(x varchar[]) RETURNS varchar[]

So your example should work if you replace

PERFORM arr(x);
with
x := arr(x);

Yours,
Laurenz Albe

---(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] Slow PITR restore

[Simon Riggs]

On Wed, 2007-12-12 at 07:36 -0800, Joshua D. Drake wrote:

> We can't really expect people to use PITR if they new 
> it would take hours to recover even on the size of machine I was working on.

That's not true statement in all cases and can often be improved with
some monitoring and tuning. Just get your client to call me :-)

Are you doing replication, or a PITR for another reason?

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


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

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

Re: [GENERAL] convert function

[Pavel Stehule]

Hello

It's look like SQL_ASCII support diacritic chars now. First you have
to encode from bytea to text

postgres=# SELECT encode(convert('ján', 'UNICODE', 'SQL_ASCII'),'escape');
 encode

 ján
(1 row)

you wont
postgres=# SELECT to_ascii(encode(convert_to('ján',
'latin2'),'escape'),'latin2');
 to_ascii
--
 jan
(1 row)

Regards
Pavel Stehule



convert do conversion from text to bytea type. For diacritic
elimination use to_ascii function:

postgres=# select to_ascii(convert('Příliš žlutý kůň' using
utf8_to_iso_8859_2),'latin2');
 to_ascii
--
 Prilis zluty kun
(1 row)


On 12/12/2007, Jan Sunavec <[EMAIL PROTECTED]> wrote:
> Hi all
>
> I have problem with "convert" function. Previous behaviour was
> SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
> ===
> jan
>
> In postgresql 8.3 is quite new behaviour.
> SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
> ==
> "j\241n"
>
> This, drives me crazy. I mean, this is not useable for non english
> country. I don't need convert to \241 characters. I understand that
> someone need this behavour. But there should be possibility switch to
> "normal" behaviour.
>
>John
>
> ---(end of broadcast)---
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>choose an index scan if your joining column's datatypes do not
>match
>

---(end of broadcast)---
TIP 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] Would it be OK if I put db file on a ext2 filesystem?

[Alvaro Herrera]

Magicloud Wang wrote:
> Dear,
>   I think database has its own operation journal, and different journal 
> filesystem does give different performance. So if I put database file on a 
> non-journal filesystem, would it be safe? Does this like using a raw device?

Regular database files need metadata journalling (data=writeback mount
option for ext3).  This is quite faster than full-blown journalling
which is what you get with default ext3 mount options.  WAL files
(pg_xlog) do not need any kind of journalling, so you can save the
overhead and put them on an ext2 filesystem (or any other nonjournalled
filesystem).

-- 
Alvaro Herrera  Developer, http://www.PostgreSQL.org/
Voy a acabar con todos los humanos / con los humanos yo acabaré
voy a acabar con todos / con todos los humanos acabaré (Bender)

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

Re: [GENERAL] Would it be OK if I put db file on a ext2 filesystem?

[Scott Marlowe]

On Dec 12, 2007 9:46 AM, Collin Kidder <[EMAIL PROTECTED]> wrote:
> Magicloud Wang wrote:
> > Dear,
> >   I think database has its own operation journal, and different journal
> > filesystem does give different performance. So if I put database file on a
> > non-journal filesystem, would it be safe? Does this like using a raw device?
> >
> >
> >
> You lose a little bit of data integrity in exchange for a little bit of
> speed. I suppose it'd be a fine thing to do so long as you can live with
> that trade off. If you want good data integrity you are more likely to
> get it from battery backed RAID5 or RAID10 or something of that sort
> rather than just trusting something like EXT3 or Reiser. EXT2 isn't a
> bad file system.

It's one of things where the known bugs in ext2/3 aren't as bad as
they sound, while the unknown bugs in some newer, less tested file
systems are often worse.  OTOH, ext2/3 do have a 2 TB partition size
limit (or at least used to) so for some things, you just gotta go to a
different file system.

Back to the subject at hand, do you need journaling for the db, this
thread from last year has a lot of good info in it.  It's the one
where I got the impression that ext2 for pg_xlog was fine and dandy.
I remember now, after reading it, that certain types of fsync might be
dangerous with non-journaled file systems.

http://archives.postgresql.org/pgsql-performance/2006-08/msg00101.php

---(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] Would it be OK if I put db file on a ext2 filesystem?

[Douglas McNaught]

On 12/12/07, Alvaro Herrera <[EMAIL PROTECTED]> wrote:

> Regular database files need metadata journalling (data=writeback mount
> option for ext3).  This is quite faster than full-blown journalling
> which is what you get with default ext3 mount options.  WAL files
> (pg_xlog) do not need any kind of journalling, so you can save the
> overhead and put them on an ext2 filesystem (or any other nonjournalled
> filesystem).

I think in practice there won't be a performance difference between
ext3 with 'data=writeback' and ext2 for WAL-the files are
preallocated, so the only activity that's occurring with any frequency
is fsync'd data writes to existing file blocks, which don't go through
the journal.  So you might as well go with ext3 since (a) you won't
have to fsck, and (b) ext3 is the bog-standard Linux filesystem now
and as such gets the most testing--ext2 is gradually dropping into
obsolescence.

-Doug

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

Re: [GENERAL] Slow PITR restore

[Joshua D. Drake]

Simon Riggs wrote:

On Wed, 2007-12-12 at 07:36 -0800, Joshua D. Drake wrote:

We can't really expect people to use PITR if they new 
it would take hours to recover even on the size of machine I was working on.


That's not true statement in all cases and can often be improved with
some monitoring and tuning. Just get your client to call me :-)



Uhh.. right.


Are you doing replication, or a PITR for another reason?


Warm standby. Normally we pull every 5 minutes which is why we hadn't 
noticed this before. However last night we pulled a full sync and 
recover and that is when we noticed it.


8 seconds for a single archive recovery is very slow in consideration of 
this machine. Even single threaded that seems slow.


Sincerely,

Joshua D. Drake



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

  http://archives.postgresql.org/

Re: [GENERAL] slony1 replication question

[Joshua D. Drake]

SHARMILA JOTHIRAJAH wrote:

Hi,
I need some help with slony...


Which comes from the Slony lists:

http://lists.slony.info/mailman/listinfo

Sincerely,

Joshua D. Drake


---(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] General Q's

[David]

Hello

 

I have some questions regarding pgsql and how it may apply to other open
source software.  Could you advise which forum is best suited to answer
these questions.

 

Background

I am undertaking a development which typically relies on the type of
functionality that can be found at Joomla (www.joomla.com
 ) and Drupel (www.drupel.com
 ) - account management, user content development,
social networking, etc.  I want to have it developed using Postgsql and NOT
MySql due to the inherent scalability of Postgsql.

 

Both Joomla / Drupal are making their databases work with Postgsql using an
abstraction layer.

 

Questions - re MySQL/ Postgresql/Joomla/Drupel

Could you advise on the following?

*   Is there a project to create MySQL compatibility for Postgresql? I
am not technical so you will need to explain as if I am a 3-year old!
*   What other Open Source solutions which support the similar type of
functionality found on Joomla and Drupal work with Postgresql? 

 

 

David Brown

 

   

 

Eclipse Consulting

M (UK)   +447939544481

F  (UK)   +442071932590

E: [EMAIL PROTECTED]

 

 

 

 

Re: [GENERAL] convert function

[Pavel Stehule]

On 12/12/2007, Jan Sunavec <[EMAIL PROTECTED]> wrote:
> Thanks a lot
>
> Lots like nice a easy solution.. I am not sure if this is fast solution..
> Many convertions you know.. :-(
> Thanks a lot anyway.
>

If you do this often, use functional index.

Pavel

>John
>
> On Wed, 12 Dec 2007 17:13:01 +0100, Pavel Stehule
> <[EMAIL PROTECTED]> wrote:
>
> > Hello
> >
> > It's look like SQL_ASCII support diacritic chars now. First you have
> > to encode from bytea to text
> >
> > postgres=# SELECT encode(convert('ján', 'UNICODE',
> > 'SQL_ASCII'),'escape');
> >  encode
> > 
> >  ján
> > (1 row)
> >
> > you wont
> > postgres=# SELECT to_ascii(encode(convert_to('ján',
> > 'latin2'),'escape'),'latin2');
> >  to_ascii
> > --
> >  jan
> > (1 row)
> >
> > Regards
> > Pavel Stehule
> >
> >
> >
> > convert do conversion from text to bytea type. For diacritic
> > elimination use to_ascii function:
> >
> > postgres=# select to_ascii(convert('Příliš žlutý kůň' using
> > utf8_to_iso_8859_2),'latin2');
> >  to_ascii
> > --
> >  Prilis zluty kun
> > (1 row)
> >
> >
> > On 12/12/2007, Jan Sunavec <[EMAIL PROTECTED]> wrote:
> >> Hi all
> >>
> >> I have problem with "convert" function. Previous behaviour was
> >> SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
> >> ===
> >> jan
> >>
> >> In postgresql 8.3 is quite new behaviour.
> >> SELECT convert('ján', 'UNICODE', 'SQL_ASCII');
> >> ==
> >> "j\241n"
> >>
> >> This, drives me crazy. I mean, this is not useable for non english
> >> country. I don't need convert to \241 characters. I understand that
> >> someone need this behavour. But there should be possibility switch to
> >> "normal" behaviour.
> >>
> >>John
> >>
> >> ---(end of broadcast)---
> >> TIP 9: In versions below 8.0, the planner will ignore your desire to
> >>choose an index scan if your joining column's datatypes do not
> >>match
> >>
>
>
>

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

Re: [GENERAL] General Q's

[Joshua D. Drake]

Dave Page wrote:

Joshua D. Drake wrote:



* Is there a project to create MySQL compatibility for Postgresql? I


No. Thank god.



Just think of all those potential customers you could be missing JD :-)

http://pgfoundry.org/projects/mysqlcompat/


I know you put a smiley face there but... I find that people that want 
to do it the "compatible" way don't want to do it the "right" way.


I have enough trouble with people wanting to do it the right way, I 
don't need to add the compatible way to the list ;)


Sincerely,

Joshua D. Drake





/D

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

  http://archives.postgresql.org/




---(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] General Q's

[Richard Huxton]

David wrote:

Both Joomla / Drupal are making their databases work with Postgsql using an
abstraction layer.


Nothing wrong with that.


Questions - re MySQL/ Postgresql/Joomla/Drupel

Could you advise on the following?

*   Is there a project to create MySQL compatibility for Postgresql? I
am not technical so you will need to explain as if I am a 3-year old!


No. Unlikely to be too - you'd need to have bug-for-bug compatibility to 
make it worthwhile. At which point you might as well use MySQL.



*   What other Open Source solutions which support the similar type of
functionality found on Joomla and Drupal work with Postgresql? 


If you're not particularly skilled in either MySQL or PostgreSQL then 
I'd pick the application first and use whatever database it works best on.


You might find the following useful:
 http://en.wikipedia.org/wiki/List_of_content_management_systems
 http://www.opensourcecms.com/

For a local community site I'm working with I chose cmsmadesimple, but 
both Joomla and Drupal have a lot of users.


--
  Richard Huxton
  Archonet Ltd

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

[Rodrigo De León]

On Dec 12, 2007 7:36 AM, Abraham, Danny <[EMAIL PROTECTED]> wrote:
> Any other alternative?

There are no anonymous blocks in PostgreSQL:

You could try creating a function:

create or replace function
  shootmyselfinthefoot()
returns boolean as
$$
declare
  (...);
begin
  execute 'SOME DML HERE';
  execute 'SOME MORE DML HERE';
  execute 'ETC.';
  (...)
  exception when others then
-- whatever...
end;
$$ language plpgsql;

... and call that from your C program.

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

Re: [GENERAL] General Q's

[Joshua D. Drake]

I am undertaking a development which typically relies on the type of 
functionality that can be found at Joomla (www.joomla.com 
) and Drupel (www.drupel.com 


Drupal.



Both Joomla / Drupal are making their databases work with Postgsql using 
an abstraction layer.




Correct.


Could you advise on the following?

* Is there a project to create MySQL compatibility for Postgresql? I


No. Thank god.


  am not technical so you will need to explain as if I am a 3-year old!
* What other Open Source solutions which support the similar type of
  functionality found on Joomla and Drupal work with Postgresql?


Those are the two I know best. We have several extremely high profile 
customers that use Drupal & PostgreSQL with great success.


Joshua D. Drake


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

Re: [GENERAL] General Q's

[Dave Page]

Joshua D. Drake wrote:



* Is there a project to create MySQL compatibility for Postgresql? I


No. Thank god.



Just think of all those potential customers you could be missing JD :-)

http://pgfoundry.org/projects/mysqlcompat/

/D

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

  http://archives.postgresql.org/

Re: [GENERAL] Slow PITR restore

[Tom Lane]

Simon Riggs <[EMAIL PROTECTED]> writes:
> On Tue, 2007-12-11 at 22:21 -0500, Tom Lane wrote:
>> Yeah, restoring is known to be less than speedy, because essentially
>> zero optimization work has been done on it.

> If there was a patch to improve this, would it be applied to 8.3?

Good grief, no.  We have not even done the research to find out where
the bottleneck(s) is/are.  We're not holding up 8.3 while we go back
into development mode, especially not when this problem has existed
for seven or eight years (even if JD failed to notice before) and
there are already some improvements for it in 8.3.

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Slow PITR restore

[Tom Lane]

Jeff Trout <[EMAIL PROTECTED]> writes:
> I've seen this on my PITR restores (thankfully, they were for  
> fetching some old data, not because we expoded).  On a 2.4ghz opteron  
> it took 5-50 seconds per wal segment, and there were a LOT of  
> segments (replay took hours and hours).  I asked a few folks and was  
> told it is the nature of the beast.  Hopefully something in 8.4 can  
> be done.

Before we get all panicked about that, someone should try to measure the
restore speed on 8.3.  It's possible that this patch already
alleviated the problem:
http://archives.postgresql.org/pgsql-committers/2007-05/msg00041.php

regards, tom lane

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