Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.

[Tom Lane]

"Takayuki Tsunakawa" <[EMAIL PROTECTED]> writes:
> BTW, why does the bgwriter try to open and write the pages of already
> dropped relations?

It does not; the problem is with stale fsync requests.

> If the relation being dropeed has
> already been registered in the list of files to be fsynced, isn't it
> possible to remove the file from the list before unlinking the file,
> asking bgwriter in a similar way as ForwardFsyncRequest()?

I suggested that here
http://archives.postgresql.org/pgsql-hackers/2007-01/msg00642.php
but have received no feedback about it ...

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Improve Postgres Query Speed

[Tom Lane]

"carter ck" <[EMAIL PROTECTED]> writes:
> And you are definitely right when updating the table. The time it takes is 
> getting longer and longer. When I do a select statement, the speed has also 
> degraded.

Seems like you need a VACUUM in there somewhere...

regards, tom lane

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

   http://archives.postgresql.org/

Re: [GENERAL] Reserve a value in a serial type field

[A. Kretschmer]

am  Mon, dem 15.01.2007, um 10:57:17 -0800 mailte Albert folgendes:
> Hi all. I have this issue: I need to load a value for an integer field
> (with auto increment) which I implemented with a serial type and to
> avoid inserts with that value in this field. The task is for a
> distributed program I'm creating and I wonder if there is an efficient
> solution for it.
> 
> I thought about setting the current value of the sequence when loading
> the needed value..is it a solution? Is there something more efficient?

A squence with a gap?

See http://a-kretschmer.de/diverses.shtml -> "A Sequence with a gap".

I hope this can solve your 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 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] Improve Postgres Query Speed

[Chris]

carter ck wrote:


Hi, the rpt_generated is a boolean value.

And you are definitely right when updating the table. The time it takes 
is getting longer and longer. When I do a select statement, the speed 
has also degraded.


If you send us the query that is slow, then I'm sure you'll get some 
suggestions about how to speed it up.


Have you run 'analyze' on the table?

--
Postgresql & php tutorials
http://www.designmagick.com/

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

Re: [GENERAL] Collating Question...

[Michael Glaesemann]

On Jan 16, 2007, at 11:52 , Jerry LeVan wrote:


The locale function on the mac has everything set to "C"
and on the linux box it is all "en_US.UTF-8".

Is there a simple way to get the collating sequences to be
the same on both boxen?


My understanding is that to have the same ordering you need to have  
initdb'd with the same locale on both machines. Hopefully PostgreSQL  
will have better collation support in the future.


Michael Glaesemann
grzm seespotcode 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

[GENERAL] Collating Question...

[Jerry LeVan]

Hi,

I am running 8.2.1 on my Mac OS X  and my X86 Fedora Laptop...

I just noticed that on my mac:

levan=# select 'A' < 'a' ;
?column?
--
t
(1 row)

and on the linux box:

levan=# select 'A' < 'a' ;
?column?
--
f
(1 row)

The locale function on the mac has everything set to "C"
and on the linux box it is all "en_US.UTF-8".

Is there a simple way to get the collating sequences to be
the same on both boxen?

If I do an "order by " on the mac then if the referenced
field starts with a lower case letter then it will appear after
all of the fields that start with an upper case letter

Thanks,

Jerry






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

Re: [GENERAL] Improve Postgres Query Speed

[Jorge Godoy]

"carter ck" <[EMAIL PROTECTED]> writes:

> Hi, the rpt_generated is a boolean value.

Is there any special reason, then, for not using a boolean type?  

> And you are definitely right when updating the table. The time it takes is
> getting longer and longer. When I do a select statement, the speed has also
> degraded.

And it is expected to be like that if you filter on unindexed columns...
Maybe you should go back to design and fix this :-)  (Remember that too much
indices is also bad.)

-- 
Jorge Godoy  <[EMAIL PROTECTED]>

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

Re: [GENERAL] Linking tables and indexes

[Jorge Godoy]

Alvaro Herrera <[EMAIL PROTECTED]> writes:

> If you're in 8.1 or better, I'd suggest defining only two indexes, one

I'm on 8.1.  Waiting for SuSE to update to 8.2... ;-)  

> on (ci_id) and other on (document_client_id), and let the system deal
> with mixing them using the bitmap scan technique when appropriate.

I thought about that but then I'd loose the UNIQUE constraint on this set.
I've also thought about creating a third index to specify the UNIQUE
constraint but ...

> OTOH since the columns are probably not separately unique, you'll need
> the primary key anyway, in which case leave the PK alone and create
> another index on (document_client_id).

... I haven't thought on this and it is much better :-)  I played with some
possibilities and I forgot mixing a composed index with a simple one... :-) 

This is what I went with.  Thanks! 


-- 
Jorge Godoy  <[EMAIL PROTECTED]>

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

Re: [GENERAL] Improve Postgres Query Speed

[carter ck]

Hi, the rpt_generated is a boolean value.

And you are definitely right when updating the table. The time it takes is 
getting longer and longer. When I do a select statement, the speed has also 
degraded.


Thanks.


From: Jorge Godoy <[EMAIL PROTECTED]>
To: "carter ck" <[EMAIL PROTECTED]>
CC: [EMAIL PROTECTED],  pgsql-general@postgresql.org
Subject: Re: [GENERAL] Improve Postgres Query Speed
Date: Mon, 15 Jan 2007 23:19:13 -0200

"carter ck" <[EMAIL PROTECTED]> writes:

> Hi,
>
> Thanks for reminding me. And the actual number of records is 100,000.
>
> The table is as following:

You forgot the EXPLAIN ANALYZE output...

> Table my_messages
> 

> midx  | integer| not null 
default

> nextval('public.my_messages_midx_seq'::text)
> msg_from   | character varying(150)   |
> msg_to   | character varying(150)   |
> msg_content   | text |
> msg_status  | character(1) | default 
'N'::bpchar
> created_dtm| timestamp without time zone | not null default 
now()

> processed_dtm | timestamp without time zone |
> rpt_generated   | character(1)| default 'N'::bpchar

Is rpt_generated a boolean column?

> Indexes:
>"msgstat_pkey" PRIMARY KEY, btree (midx)
>"my_messages_msgstatus_index" btree (msg_status)

If your query doesn't filter with those indices then you won't gain much 
with
them...  E.g. "UPDATE my_messages SET rpt_generated='Y' WHERE 
rpt_generated='N';"

won't use any of those indices and will seq scan the whole table.

--
Jorge Godoy  <[EMAIL PROTECTED]>


_
Get MSN Messenger emoticons and display pictures here! 
http://ilovemessenger.msn.com/?mkt=en-sg



---(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] Linking tables and indexes

[Alvaro Herrera]

Jorge Godoy wrote:

> Thinking about how PostgreSQL is able to use composed indices should I create
> the reverse index ("CREATE INDEX something ON ged.documents_clients_cis
> (document_client_id, ci_id)") or I'd only be wasting disk and processing?
> 
> The query can be done from either side (i.e. I might know either ci_id or
> document_client_id only).

If you're in 8.1 or better, I'd suggest defining only two indexes, one
on (ci_id) and other on (document_client_id), and let the system deal
with mixing them using the bitmap scan technique when appropriate.

OTOH since the columns are probably not separately unique, you'll need
the primary key anyway, in which case leave the PK alone and create
another index on (document_client_id).

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

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

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

Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.

[Takayuki Tsunakawa]

From: "Magnus Hagander" <[EMAIL PROTECTED]>
> But yeah, that's probably a good idea. A quick look at the code says
we
> should at least ask people who have this problem to give it a run
with
> logging at DEBUG5 which should then log exactly what the errorcode
was.
> Or are you seeing more places that need such logging first?

I'm sorry we can't get get the Win32 error code to be displayed.  I
got the following messages:

2007-01-16 09:24:48 DEBUG:  checkpoint starting
2007-01-16 09:24:48 ERROR:  could not open relation 1663/10819/18296:
Permission denied
2007-01-16 09:24:48 ERROR:  checkpoint request failed
2007-01-16 09:24:48 HINT:  Consult recent messages in the server log
for details.
2007-01-16 09:24:48 STATEMENT:  checkpoint;


The reason is that src/port/open.c does not use _dosmaperr().  It
converts the Win32 error code to errno directly.  EACCES is converted
from ERROR_ACCESS_DENIED only.  Mmm, we may have to compromise as
Tom-san says.

BTW, why does the bgwriter try to open and write the pages of already
dropped relations?  When dropping relations, DropRelFileNodeBuffers is
called to discard dirty buffers.  If the relation being dropeed has
already been registered in the list of files to be fsynced, isn't it
possible to remove the file from the list before unlinking the file,
asking bgwriter in a similar way as ForwardFsyncRequest()?

#
The timestamp at the head of each message is noisy since the event
viewer has the time info, isn't it?  Besides, several PostgreSQL
messages appeared as one entry of event log, separated by a LF instead
of CR LF.  On Windows, CR LF should separate lines.





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

Re: [GENERAL] Improve Postgres Query Speed

[Jorge Godoy]

"carter ck" <[EMAIL PROTECTED]> writes:

> Hi,
>
> Thanks for reminding me. And the actual number of records is 100,000.
>
> The table is as following:

You forgot the EXPLAIN ANALYZE output...

> Table my_messages
> 
> midx  | integer| not null default
> nextval('public.my_messages_midx_seq'::text)
> msg_from   | character varying(150)   |
> msg_to   | character varying(150)   |
> msg_content   | text |
> msg_status  | character(1) | default 'N'::bpchar
> created_dtm| timestamp without time zone | not null default now()
> processed_dtm | timestamp without time zone |
> rpt_generated   | character(1)| default 'N'::bpchar

Is rpt_generated a boolean column?

> Indexes:
>"msgstat_pkey" PRIMARY KEY, btree (midx)
>"my_messages_msgstatus_index" btree (msg_status)

If your query doesn't filter with those indices then you won't gain much with
them...  E.g. "UPDATE my_messages SET rpt_generated='Y' WHERE 
rpt_generated='N';" 
won't use any of those indices and will seq scan the whole table. 

-- 
Jorge Godoy  <[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] Improve Postgres Query Speed

[Alvaro Herrera]

carter ck wrote:
> 
> Hi,
> 
> Thanks for reminding me. And the actual number of records is 100,000.
> 
> The table is as following:

And the query?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

Re: [GENERAL] Transaction callback

[Jorge Godoy]

Kevin Field <[EMAIL PROTECTED]> writes:

> http://archives.postgresql.org/pgsql-hackers/2004-01/msg00861.php
>
> (Just to dredge up another old thread...)
>
>> While its true that a post-commit operation cannot modify a database, it
>> might still for instance propagate the outcome of the transaction to another
>> resource outside of the database, it might invalidate a transaction local
>> in-memory cache, or it might clear resources occupied in memory for a rule
>> system, etc.
>
> This is exactly what I'd like to do.  Is there a way to do it nowadays? What
> I'm going for is an AJAX app that doesn't need to poll the server, but rather
> can have the server callback to it when a transaction messes with the data a
> user at which a user is looking.
>
> Any advice would be much appreciated.

I believe that you'll be wanting to take a look at the NOTIFY and LISTEN
commands.

http://www.postgresql.org/docs/8.1/interactive/sql-notify.html
http://www.postgresql.org/docs/8.1/interactive/sql-listen.html


Be seeing you,
-- 
Jorge Godoy  <[EMAIL PROTECTED]>

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

Re: [GENERAL] Improve Postgres Query Speed

[carter ck]

Hi,

Thanks for reminding me. And the actual number of records is 100,000.

The table is as following:

Table my_messages

midx  | integer| not null 
default nextval('public.my_messages_midx_seq'::text)

msg_from   | character varying(150)   |
msg_to   | character varying(150)   |
msg_content   | text |
msg_status  | character(1) | default 'N'::bpchar
created_dtm| timestamp without time zone | not null default now()
processed_dtm | timestamp without time zone |
rpt_generated   | character(1)| default 'N'::bpchar

Indexes:
   "msgstat_pkey" PRIMARY KEY, btree (midx)
   "my_messages_msgstatus_index" btree (msg_status)

Thanks for help.


From: "Chad Wagner" <[EMAIL PROTECTED]>
To: "carter ck" <[EMAIL PROTECTED]>
CC: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Improve Postgres Query Speed
Date: Mon, 15 Jan 2007 19:54:51 -0500

On 1/15/07, carter ck <[EMAIL PROTECTED]> wrote:


I am having slow performance issue when querying a table that contains
more
than 1 records.

Everything just slow down when executing a query though I have created
Index
on it.



You didn't really provide much information for anyone to help you.  I would
suggest posting the table definition (columns & indexes), the queries you
are running, and the output of "EXPLAIN ANALYZE ;".

--
Chad
http://www.postgresqlforums.com/


_
Receive MSN Hotmail alerts over SMS! 
http://en-asiasms.mobile.msn.com/ac.aspx?cid=1002



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

  http://archives.postgresql.org/

Re: [GENERAL] Transaction callback

[Kevin Field]

http://archives.postgresql.org/pgsql-hackers/2004-01/msg00861.php

(Just to dredge up another old thread...)


While its true that a post-commit operation cannot modify a database, it
might still for instance propagate the outcome of the transaction to another
resource outside of the database, it might invalidate a transaction local
in-memory cache, or it might clear resources occupied in memory for a rule
system, etc.


This is exactly what I'd like to do.  Is there a way to do it nowadays? 
 What I'm going for is an AJAX app that doesn't need to poll the 
server, but rather can have the server callback to it when a transaction 
messes with the data a user at which a user is looking.


Any advice would be much appreciated.

Thanks,
Kev

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

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

[GENERAL] Linking tables and indexes

[Jorge Godoy]

Hi!


I'm not sure about the English terminology for that so I'm sorry if I made a
mistake on the subject and on this message.  I hope de code explains it better
if I missed it :-)

I have some tables that will have N:M relationships between themselves and for
that I created some linking tables such as:

 CREATE TABLE ged.documents_clients_cis (
 ci_id INT NOT NULL,
 CONSTRAINT ci_id_exists 
 FOREIGN KEY (ci_id) 
 REFERENCES ged.cis (id) ,
 document_client_id INT NOT NULL,
 CONSTRAINT document_client_id_exists 
 FOREIGN KEY (document_client_id) 
 REFERENCES ged.documents_clients (id),
 PRIMARY KEY (ci_id, document_client_id)
 );


Thinking about how PostgreSQL is able to use composed indices should I create
the reverse index ("CREATE INDEX something ON ged.documents_clients_cis
(document_client_id, ci_id)") or I'd only be wasting disk and processing?

The query can be done from either side (i.e. I might know either ci_id or
document_client_id only).


Thanks for your attention,
-- 
Jorge Godoy  <[EMAIL PROTECTED]>

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

Re: [GENERAL] Improve Postgres Query Speed

[Chad Wagner]

On 1/15/07, carter ck <[EMAIL PROTECTED]> wrote:


I am having slow performance issue when querying a table that contains
more
than 1 records.

Everything just slow down when executing a query though I have created
Index
on it.



You didn't really provide much information for anyone to help you.  I would
suggest posting the table definition (columns & indexes), the queries you
are running, and the output of "EXPLAIN ANALYZE ;".

--
Chad
http://www.postgresqlforums.com/

[GENERAL] Improve Postgres Query Speed

[carter ck]

Hi all,

I am having slow performance issue when querying a table that contains more 
than 1 records.


Everything just slow down when executing a query though I have created Index 
on it.


Can anyone suggest ways to improve the speed?

Thanks.

_
Find just what you are after with the more precise, more powerful new MSN 
Search. http://search.msn.com.sg/ Try it now.



---(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] PQexec does not return.

[Steve Martin]

 Hi All,

Found the problem.  This was caused by a memory leak in our application.

Regards
Steve Martin


Steve Martin wrote:


Hi All,

We have an intermittent problem where PQexec does not seem to return 
even though the server seems to have sent the results.


From the gdb output , the sql statement can be seen, and from the log, 
the result can be seen to be sent.  Both process are running on the 
same machine.  Version of postgres and other info:


   1. Postgres Version 8.1.4 
   2. Machine HP rx4640

   3. OS: HPUX 11.23
   4. Mem 8G 



Has anyone seen this type of problem before, can it be cause by a 
TCP/IP communication failure?



From gdb:

% /opt/langtools/bin/gdb /path/name -p 3587   
HP gdb 5.2 for HP Itanium (32 or 64 bit) and target HP-UX 11.2x.

Copyright 1986 - 2001 Free Software Foundation, Inc.
Hewlett-Packard Wildebeest 5.2 (based on GDB) is covered by the
GNU General Public License. Type "show copying" to see the
conditions to
change it and/or distribute copies. Type "show warranty" for
warranty/support.
..
Attaching to program: /path/name, process 3587

warning: No unwind information found.
 Skipping this library /usr/lib/hpux32/libcl.so.1.

0x6000c0342810:0 in _poll_sys+0x30 () from
/usr/lib/hpux32/libc.so.1
(gdb) bt
#0  0x6000c0342810:0 in _poll_sys+0x30 () from
/usr/lib/hpux32/libc.so.1
#1  0x6000c03553e0:0 in poll+0x160 () from
/usr/lib/hpux32/libc.so.1
#2  0x6000cefa75b0:0 in pqSocketCheck+0xb00 ()
   from /usr/local/pgsql/lib/libpq.so.4
#3  0x6000cefa77c0:0 in pqWaitTimed+0x40 ()
   from /usr/local/pgsql/lib/libpq.so.4
#4  0x6000cefa7890:0 in pqWait+0x40 () from
/usr/local/pgsql/lib/libpq.so.4
#5  0x6000cefa53b0:0 in PQgetResult+0x180 ()
   from /usr/local/pgsql/lib/libpq.so.4
#6  0x6000cefa56f0:0 in PQexecFinish+0x40 ()
   from /usr/local/pgsql/lib/libpq.so.4
#7  0x6000c1c83870:0 in DBIFPostgreSelect::getRecord
(this=0x40114840)
at DBIFPostgre.C:1688
#8  0x6000c1c73d20:0 in DBIFPostgreSelect::selectNext
(this=0x40114840,
[EMAIL PROTECTED]) at DBIFPostgre.C:1902
#9  0x6000c1c64b90:0 in DBIFSelect::selectNext (this=0x7fff5240,
[EMAIL PROTECTED]) at DBIF.C:2704
#10 0x404ff00:0 in TableStatus::checkDeferredTR (this=0x403104c0)
at DbSContTable.C:1468
#11 0x405b430:0 in TableStatusManager::checkDeferredTR
(this=0x400bde90)
at DbSContTable.C:3146
#12 0x4068960:0 in Controller::go (this=0x7fffc320) at
DbSController.C:1950
#13 0x406b1b0:0 in main (argc=1, argv=0x7fffed74) at DbSContMain.C:137
(gdb) q
The program is running.  Quit anyway (and detach it)? (y or n) y
Detaching from program: /path/name, process 3587

% /opt/langtools/bin/gdb /path/name -p 3587
HP gdb 5.2 for HP Itanium (32 or 64 bit) and target HP-UX 11.2x.
Copyright 1986 - 2001 Free Software Foundation, Inc.
Hewlett-Packard Wildebeest 5.2 (based on GDB) is covered by the
GNU General Public License. Type "show copying" to see the
conditions to
change it and/or distribute copies. Type "show warranty" for
warranty/support.
..
Attaching to program: /path/name, process 3587

warning: No unwind information found.
 Skipping this library /usr/lib/hpux32/libcl.so.1.

0x6000c0342810:0 in _poll_sys+0x30 () from
/usr/lib/hpux32/libc.so.1
(gdb) up
#1  0x6000c03553e0:0 in poll+0x160 () from
/usr/lib/hpux32/libc.so.1
(gdb) up
#2  0x6000cefa75b0:0 in pqSocketCheck+0xb00 ()
   from /usr/local/pgsql/lib/libpq.so.4
(gdb) up
#3  0x6000cefa77c0:0 in pqWaitTimed+0x40 ()
   from /usr/local/pgsql/lib/libpq.so.4
(gdb) up
#4  0x6000cefa7890:0 in pqWait+0x40 () from
/usr/local/pgsql/lib/libpq.so.4
(gdb) up
#5  0x6000cefa53b0:0 in PQgetResult+0x180 ()
   from /usr/local/pgsql/lib/libpq.so.4
(gdb) up
#6  0x6000cefa56f0:0 in PQexecFinish+0x40 ()
   from /usr/local/pgsql/lib/libpq.so.4
(gdb) up
#7  0x6000c1c83870:0 in DBIFPostgreSelect::getRecord
(this=0x40114840)
at DBIFPostgre.C:1688
1688myResultExecPrepare =
PQexec(myConnection->conn, seleStmt);
(gdb) p seleStmt
$1 = "SELECT * FROM T_AM_TERM_BILLING WHERE (TR_STATUS =
'DEFERRED') AND ((DOWNLOAD_DATE < 20070108) OR ((DOWNLOAD_DATE =
20070108) AND (DOWNLOAD_TIME < 203744)))", '\000' 
(gdb) q
The program is running.  Quit anyway (and detach it)? (y or n) y
Detaching from program: /path/name, process 3587


From postgres log

2007-01-08 20:37:44.839 NZDT [EMAIL PROTECTED]>LOG:  statement:
select pg_get_indexdef(indexrelid) from pg_index where ind
relid = ( select oid from pg_class where relname =
't_am_registration_db')
2007-01-08 20:37:44.840 NZDT [EMAIL PROTECTED]>LOG:  duration: 0.347 ms
20

Re: [GENERAL] substr negative indexes

[Guy Rouillier]

Tom Lane wrote:

and unless I'm mistaken, our behavior conforms to the spec and Oracle's
doesn't.

Strictly speaking, the spec doesn't define the behavior of "SUBSTR" at
all, only "SUBSTRING" with this weird FROM/FOR argument syntax.  But
PG treats SUBSTR(x,y,z), SUBSTRING(x,y,z) and SUBSTRING(x FROM y FOR z)
all the same.  Possibly Oracle conforms to spec for SUBSTRING but
their SUBSTR acts differently?


Thanks, Tom, I agree that PG's substr() appears to be following the spec 
(thank goodness I don't have to read that whole thing ;).  Oracle does 
not implement substring() at all (up through release 9.2, the latest I 
have to work with.  Just checked the online documentation for 10g 
Release 2 (latest available) and it doesn't have it either.  Says this 
under conformance: "E021-06, SUBSTRING function: use SUBSTR function 
instead". Sigh...


--
Guy Rouillier

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

  http://archives.postgresql.org/

Re: [GENERAL] Reserve a value in a serial type field

[Albert]

Thanks Shane.

I solved taking trace of the sequence value directly in the
program...it is not a good solution but it works until I find a better
one!


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

   http://archives.postgresql.org/

Re: [GENERAL] Reserve a value in a serial type field

[Shane Ambler]

Albert wrote:

Hi all. I have this issue: I need to load a value for an integer field
(with auto increment) which I implemented with a serial type and to
avoid inserts with that value in this field. The task is for a
distributed program I'm creating and I wonder if there is an efficient
solution for it.


Is this the initial loading of data or during normal usage?


I thought about setting the current value of the sequence when loading
the needed value..is it a solution? Is there something more efficient?


If you are loading the initial data to begin using the db then setting 
sequence values will be fine.


If you are thinking about doing this constantly with normal usage then I 
would say drop the serial type and just use a normal int column. A 
unique index on the column will stop the duplicate value inserts and 
allow your program to workout the values it wants to use.


If you are planning to keep changing the sequence next_value there is no 
real point of using the serial and you will find you will get problems 
with one client getting the current_value right before another program 
increases the value and inserts the same value as the first client.



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

---(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] Avoiding empty queries in tsearch

[Oleg Bartunov]

Doug,

numnode exists for sure ! It's plainto_tsquery function which
Teodor used in example, appeared in 8.2


Oleg
On Mon, 15 Jan 2007, Doug Cole wrote:


That sounds perfect, but it doesn't seem to exist on either of the
postgresql installations I have access to (8.1 on ubuntu and fedora
core).  Is it new to 8.2?  Is there a similar function under 8.1, or
at least a decent work-around?  Thanks for the help,
Doug



On 1/15/07, Teodor Sigaev <[EMAIL PROTECTED]> wrote:

 contrib_regression=# select numnode( plainto_tsquery('the any') );
NOTICE:  query contains only stopword(s) or doesn't contain

lexeme(s), ignored

  numnode
-
0
(1 row)

contrib_regression=# select numnode( plainto_tsquery('the table') );
  numnode
-
1
(1 row)

contrib_regression=# select numnode( plainto_tsquery('long table') );
  numnode
-
3
(1 row)


--
Teodor Sigaev   E-mail:  [EMAIL PROTECTED]
WWW:

http://www.sigaev.ru/

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



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

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

Re: [GENERAL] Avoiding empty queries in tsearch

[Teodor Sigaev]

Doug Cole wrote:
That sounds perfect, but it doesn't seem to exist on either of the 
postgresql installations I have access to (8.1 on ubuntu and fedora 
core).  Is it new to 8.2?  Is there a similar function under 8.1, or at 

Yes, it's new in 8.2


least a decent work-around?  Thanks for the help,
Doug


Not nice workaround but it works:

# create or replace function isvoid(tsquery)
returns bool as $$
select case when $1 is NULL then 't'::bool when length(textin(tsquery_out( $1 
))) = 0 then 't'::bool else 'f'::bool end;

$$ language SQL called on null input;


# select isvoid( plainto_tsquery('the & any') );
NOTICE:  query contains only stopword(s) or doesn't contain lexeme(s), ignored
 isvoid

 t
(1 row)



--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

Re: [GENERAL] Avoiding empty queries in tsearch

[Doug Cole]

That sounds perfect, but it doesn't seem to exist on either of the
postgresql installations I have access to (8.1 on ubuntu and fedora
core).  Is it new to 8.2?  Is there a similar function under 8.1, or
at least a decent work-around?  Thanks for the help,
Doug



On 1/15/07, Teodor Sigaev <[EMAIL PROTECTED]> wrote:
>  contrib_regression=# select numnode( plainto_tsquery('the any') );
> NOTICE:  query contains only stopword(s) or doesn't contain
lexeme(s), ignored
>   numnode
> -
> 0
> (1 row)
>
> contrib_regression=# select numnode( plainto_tsquery('the table') );
>   numnode
> -
> 1
> (1 row)
>
> contrib_regression=# select numnode( plainto_tsquery('long table') );
>   numnode
> -
> 3
> (1 row)
>
>
> --
> Teodor Sigaev   E-mail:  [EMAIL PROTECTED]
> WWW:
http://www.sigaev.ru/

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

[GENERAL] Reserve a value in a serial type field

[Albert]

Hi all. I have this issue: I need to load a value for an integer field
(with auto increment) which I implemented with a serial type and to
avoid inserts with that value in this field. The task is for a
distributed program I'm creating and I wonder if there is an efficient
solution for it.

I thought about setting the current value of the sequence when loading
the needed value..is it a solution? Is there something more efficient?

Thanks very much! Any help is appreciated!


---(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] Glacially slow nested SELECT

[Tom Lane]

Demitri Muna <[EMAIL PROTECTED]> writes:
> On 15 Jan 2007, at 16:21, Tom Lane wrote:
>> It looks like spview is a view with an embedded ORDER BY?  IIRC that
>> prevents any meaningful optimization of joins to it --- and
>> WHERE-IN-sub-SELECT is a kind of join.

> Thanks for the pointer Tom; removing the ORDER BY from the view did  
> the trick. Is there a reason that the sorting can't be done after the  
> selection?

Well, it'd require major revisions to the planner, and it'd break
various queries that depend on the planner honoring sub-select ORDER BY
(people use that to guarantee the order in which values are fed to
custom aggregates, for instance).

regards, tom lane

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

Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.

[Tom Lane]

Magnus Hagander <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> DEBUG5 is going to be a bit voluminous, but let's try that if we can.

> Perhaps we should switch down the DEBUG level of it, at least until we
> know what happens?

That would have to wait on another update release, or at least someone
being willing to build a nonstandard executable for Windows, so let's
first see if people are willing to do the DEBUG5 bit.

regards, tom lane

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

Re: [GENERAL] Glacially slow nested SELECT

[Demitri Muna]

On 15 Jan 2007, at 16:21, Tom Lane wrote:


[EMAIL PROTECTED] writes:

I have a query that is extraordinarily slow but I don't know why.


It looks like spview is a view with an embedded ORDER BY?  IIRC that
prevents any meaningful optimization of joins to it --- and
WHERE-IN-sub-SELECT is a kind of join.


Thanks for the pointer Tom; removing the ORDER BY from the view did  
the trick. Is there a reason that the sorting can't be done after the  
selection? I suppose I can solve this with a function.


Cheers,

Demitri



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

Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.

[Magnus Hagander]

Tom Lane wrote:
> Magnus Hagander <[EMAIL PROTECTED]> writes:
>> But yeah, that's probably a good idea. A quick look at the code says we
>> should at least ask people who have this problem to give it a run with
>> logging at DEBUG5 which should then log exactly what the errorcode was.
>> Or are you seeing more places that need such logging first?
> 
> DEBUG5 is going to be a bit voluminous, but let's try that if we can.

Perhaps we should switch down the DEBUG level of it, at least until we
know what happens?

//Magnus

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

   http://archives.postgresql.org/

Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.

[Tom Lane]

Magnus Hagander <[EMAIL PROTECTED]> writes:
> But yeah, that's probably a good idea. A quick look at the code says we
> should at least ask people who have this problem to give it a run with
> logging at DEBUG5 which should then log exactly what the errorcode was.
> Or are you seeing more places that need such logging first?

DEBUG5 is going to be a bit voluminous, but let's try that if we can.

regards, tom lane

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

Re: [HACKERS] [GENERAL] Checkpoint request failed on version 8.2.1.

[Magnus Hagander]

Tom Lane wrote:
> Magnus Hagander <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>>> pg_control is certainly not ever deleted or renamed, and in fact I
>>> believe there's an LWLock enforcing that only one PG process at a time
>>> is even touching it.  So we need another theory to explain this one :-(
> 
>> Right. What we need is a list of which processes have handles open to
>> the file, which can be dumped using Process Explorer (there are other
>> sysinternals tools to do it as well, but PE is probably the easiest)-
> 
> Hmm, are you just assuming that the underlying error is
> ERROR_SHARING_VIOLATION?  One of the things that's bothered me all along
> is that there are a dozen different Windows error codes that we map to
> EACCES ... perhaps it's time to think about disambiguating that a bit
> better?

I was. Using PE is just one way to prove that was it :-)

But yeah, that's probably a good idea. A quick look at the code says we
should at least ask people who have this problem to give it a run with
logging at DEBUG5 which should then log exactly what the errorcode was.
Or are you seeing more places that need such logging first?


//Magnus

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

   http://archives.postgresql.org/

Re: [GENERAL] Unpredicatable behavior of volatile functions used

[Aleksander Kmetec]

Tom Lane wrote:

Aleksander Kmetec <[EMAIL PROTECTED]> writes:
Some quick testing shows that util.row_number() only gets re-evaluated at every call if the subquery contains an ORDER 
BY clause. Now we can predict whether we need to compensate for that just by looking at the original query.


If you're going to use a cursor, I don't understand why you don't just
MOVE FORWARD ALL and look at the returned rowcount to determine how
many rows in the query.  This won't be any more expensive than fetching
the last row ...


That's a very good question...

It's more than a year now since I originally wrote that code, and I remember testing MOVE FORWARD ALL, but being unable 
to get my hands on the row count for that operation. At that time I just accepted it as the way things work and created 
that row_number() workaround instead.


But after your last message I went to investigate why that had happened in the first place. And sure enough, there was a 
bug in the 3rd party database library we're using. After a quick fix everything is working as it should and I can remove 
 row_number() altogether.


Thank you for your patience. :-)

Aleksander


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

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

[GENERAL] NOTIFY QUESTION

[Oisin Glynn]

From the Docs see below it implies that the WHERE clause of the update 
is ignored.( 
http://www.postgresql.org/docs/8.1/static/sql-createrule.html )

If I create a Rule as

CREATE RULE foo_update_false AS ON UPDATE TO foo
  WHERE NEW.some_flag = TRUE
  DO NOTIFY foo_update;


Does the WHERE clause in the rule get ignored as well?  If so Could I 
create a regular trigger and do the notification from within the trigger 
having put a check in the  trigger?


Thanks,

Oisin





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

Re: [GENERAL] Why the data changes it's value by itself!

[Ardian Xharra]

Yes, the problem was the memory (testing with memtest)
I took us long to have the machine check

Thanks,

- Original Message - 
From: "Richard Huxton" 

To: "Ardian Xharra" <[EMAIL PROTECTED]>
Cc: "postgreSQL postgreSQL" 
Sent: Friday, November 17, 2006 1:12 PM
Subject: Re: [GENERAL] Why the data changes it's value by itself!



Ardian Xharra wrote:

Does this message in application.log can be the source of problem

wuaueng.dll (1204) Synchronous read page checksum error -1018 ((1:801 
1:801) (0-13218) (0-14642)) occurred. Please restore the databases from a 
previous backup.


I don't recognise this as a PostgreSQL error, although it could be part of 
the Windows code I suppose.


Hmm... Googling a little, that appears to be an MS-Exchange error
  http://support.microsoft.com/kb/314917

Looks like you might have problems with your disk hardware (or RAM or 
motherboard, or drivers...)


Run some extensive hardware tests. If you can afford to, just replace the 
machine. You'll be looking at a couple of days work to be sure you've 
fixed whatever is causing this.


--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/



--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.409 / Virus Database: 268.14.6/536 - Release Date: 16/11/2006






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

  http://archives.postgresql.org/

Re: [GENERAL] Avoiding empty queries in tsearch

[Teodor Sigaev]

contrib_regression=# select numnode( plainto_tsquery('the any') );
NOTICE:  query contains only stopword(s) or doesn't contain lexeme(s), ignored
 numnode
-
   0
(1 row)

contrib_regression=# select numnode( plainto_tsquery('the table') );
 numnode
-
   1
(1 row)

contrib_regression=# select numnode( plainto_tsquery('long table') );
 numnode
-
   3
(1 row)


--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

  http://archives.postgresql.org/

Re: [GENERAL] Unpredicatable behavior of volatile functions used

[Tom Lane]

Aleksander Kmetec <[EMAIL PROTECTED]> writes:
> Some quick testing shows that util.row_number() only gets re-evaluated at 
> every call if the subquery contains an ORDER 
> BY clause. Now we can predict whether we need to compensate for that just by 
> looking at the original query.

If you're going to use a cursor, I don't understand why you don't just
MOVE FORWARD ALL and look at the returned rowcount to determine how
many rows in the query.  This won't be any more expensive than fetching
the last row ...

regards, tom lane

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

Re: [GENERAL] Glacially slow nested SELECT

[Tom Lane]

[EMAIL PROTECTED] writes:
> I have a query that is extraordinarily slow but I don't know why.

It looks like spview is a view with an embedded ORDER BY?  IIRC that
prevents any meaningful optimization of joins to it --- and
WHERE-IN-sub-SELECT is a kind of join.

regards, tom lane

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

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

Re: [GENERAL] Autovacuum Improvements

[Alban Hertroys]

Pavan Deolasee wrote:
> Simon Riggs wrote:
>> On Fri, 2006-12-29 at 20:25 -0300, Alvaro Herrera wrote:
>>> Christopher Browne wrote:
>>>
 Seems to me that you could get ~80% of the way by having the simplest
 "2 queue" implementation, where tables with size < some threshold get
 thrown at the "little table" queue, and tables above that size go to
 the "big table" queue.

 That should keep any small tables from getting "vacuum-starved."
>>
> 
> This is exectly what I am trying, two process autovacuum and a GUC to
> seperate small tables.
> 
> In this case, one process takes up vacuuming of the small tables and
> other process vacuuming of the remaining tables as well as Xid
> avoidance related vacuuming. The goal is to avoid starvation of small
> tables when a large table is being vacuumed (which may take
> several hours) without adding too much complexity to the code.

Would it work to make the queues push the treshold into the direction of
the still running queue if the other queue finishes before the still
running one? This would achieve some kind of auto-tuning, but that is
usually tricky.

For example, what if one of the queues got stuck on a lock?

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

Re: [GENERAL] Unpredicatable behavior of volatile functions used

[Aleksander Kmetec]

Tom Lane wrote:

This isn't gonna work very well if your query involves sorting, because
the SELECT-list is evaluated before the sort step ...

regards, tom lane



Thanks, this seems to solve my problem.

Some quick testing shows that util.row_number() only gets re-evaluated at every call if the subquery contains an ORDER 
BY clause. Now we can predict whether we need to compensate for that just by looking at the original query.


Regards,
Aleksander

---(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] check table existence...

[Alban Hertroys]

Shoaib Mir wrote:
> Something like this will help you..

Or shorter:

> ===
> 
> CREATE OR REPLACE FUNCTION public.check_table(varchar, varchar)
> RETURNS boolean AS $$
> DECLARE
>   v_cnt integer;
>   v_tbl boolean;
> BEGIN

   PERFORM 1 FROM pg_tables where tablename = $1 and

> schemaname = $2;

   RETURN FOUND;

> END;
>  $$ LANGUAGE 'plpgsql'

I'm pretty sure that should work.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

[GENERAL] Glacially slow nested SELECT

[thatsanicehatyouhave]

Hello all,

I have a query that is extraordinarily slow but I don't know why. It  
crosses a many-to-many join table and looks like this:


SELECT * FROM spview WHERE id IN (SELECT signal_profile_id FROM  
track_to_signal_profile WHERE track_id = 19510985);


The three tables are:

track <-> track_to_signal_profile <-> signal_profile (where spview is  
a view with rows from that table only)


I ran the query with "explain analyse" and left it over the weekend  
and it didn't finish. (The "signal_profile" table has on order  
350,000,000 rows.)


Now, this is the part I don't understand. The subquery finishes  
instantly:


driftdb=# EXPLAIN ANALYSE SELECT signal_profile_id FROM  
track_to_signal_profile WHERE track_id = 19510985;
Index Scan using unique_sp_and_track on track_to_signal_profile   
(cost=0.00..11892.92 rows=5014 width=8) (actual time=0.018..0.023  
rows=3 loops=1)

   Index Cond: (track_id = 19510985)
Total runtime: 0.058 ms
(3 rows)

If I take the result and do the rest by hand, it's also instant:

driftdb=# EXPLAIN ANALYSE SELECT * FROM spview WHERE id IN  
(1705521616, 1705521681, 1705521693);
Subquery Scan spview  (cost=63.62..63.66 rows=3 width=292) (actual  
time=0.095..0.109 rows=3 loops=1)
   ->  Sort  (cost=63.62..63.63 rows=3 width=96) (actual  
time=0.091..0.096 rows=3 loops=1)
 Sort Key: signal_profile."trigger", signal_profile.mwpc,  
signal_profile.readout, signal_profile.signal_profile_index
 ->  Bitmap Heap Scan on signal_profile  (cost=51.45..63.60  
rows=3 width=96) (actual time=0.049..0.068 rows=3 loops=1)
   Recheck Cond: (id = ANY  
('{1705521616,1705521681,1705521693}'::integer[]))
   ->  Bitmap Index Scan on signal_profile_pkey   
(cost=0.00..51.45 rows=3 width=0) (actual time=0.027..0.027 rows=3  
loops=1)
 Index Cond: (id = ANY  
('{1705521616,1705521681,1705521693}'::integer[]))

Total runtime: 0.190 ms
(8 rows)

So the data can be found instantly, but when I put the two queries in  
one line it fails. Is there a type conversion/confusion somewhere?


I would appreciate any suggestions!

Cheers,

Demitri


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

Re: [GENERAL] Unpredicatable behavior of volatile functions used in cursors

[Tom Lane]

Aleksander Kmetec <[EMAIL PROTECTED]> writes:
> We're using the following technique for counting the number of rows in a 
> cursor:
> DECLARE instance_cur_1 SCROLL CURSOR FOR
> SELECT util.row_number(), *
> FROM (
>   $LONG_RUNNING_QUERY
> ) ss
> FETCH LAST IN instance_cur_1;

> util.row_number() is a volatile function written in C which simply returns 
> "++internal_counter" every time it is called.

This isn't gonna work very well if your query involves sorting, because
the SELECT-list is evaluated before the sort step ...

regards, tom lane

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

Re: [GENERAL] Runtime error when calling function from .NET ( Function returns record)

[Tom Lane]

dparent <[EMAIL PROTECTED]> writes:
> I have a function which returns TYPE RECORD and receives a string. The passed
> string is used to build a dynamic SQL statement and the passed string is the
> SELECT LIST of the built dynamic SQL statement.

> The call works fine from Postgres SQL (PL/PG SQL) but when I make the call
> from .NET (Core labs data provider), I get a runtime error.

It sounds a bit like the .net provider is editorializing on your query :-(.
I'd suggest enabling statement logging on the server to confirm or
refute that.  If it is indeed changing your SQL then complaining to its
authors seems the next step.

regards, tom lane

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

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

[GENERAL] Unpredicatable behavior of volatile functions used in cursors

[Aleksander Kmetec]

Hi,

I'm running into some inconsistent behavior when using volatile functions with 
cursors under PG 8.1.

We're using the following technique for counting the number of rows in a cursor:

---
DECLARE instance_cur_1 SCROLL CURSOR FOR
SELECT util.row_number(), *
FROM (
$LONG_RUNNING_QUERY
) ss

FETCH LAST IN instance_cur_1;
---

util.row_number() is a volatile function written in C which simply returns 
"++internal_counter" every time it is called.

What's unusual is that for some queries FETCH LAST returns a row_number value which matches the actual number of rows, 
while for others it returns the actual number +1 (and adds +1 for each consecutive call). It seems that under some 
conditions util.row_number() gets re-evaluated for every call.


Could someone explain why and under which conditions this happens?
Is there a way to make this behavior more consistent?

Regards,
Aleksander

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

Re: [GENERAL] Performance with very large tables

[Shane Ambler]

Jan van der Weijde wrote:

That is exactly the problem I think. However I do not deliberately
retrieve the entire table. I use the default settings of the PostgreSQL


You will want to increase the default settings and let PostgreSQL use as 
much RAM as you have - especially when retrieving a large dataset.


Some good points of reference is -

http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

http://www.varlena.com/GeneralBits/Tidbits/perf.html

http://www.powerpostgresql.com/PerfList

installation and just execute a simple SELECT * FROM table. 
I am using a separate client and server (both XP in the test

environment), but that should not make much difference.
I would expect that the default behavior of PostgreSQL should be such
that without LIMIT, a SELECT returns records immediately. 


Thank you,
Jan



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

  http://archives.postgresql.org/

Re: [GENERAL] like query backslash

[ksherlock]

Sim Zacks wrote:
> To retrieve the above example, I needed to query:
> select * from filetable where filename like 'serverdir%'
>
> Is this a bug or is there a reason for this?
>
> Sim

There's a reason.  With like queries, if you want to search for the
literal characters % or ?  they need to be escaped with a \.  So
essentially, the entire string is \ escaped twice.  The good news is,
you can redefine the escape character in your query.

eg

select * from filetable where filename like 'server\\dir%' escape ''


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

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

[GENERAL] Avoiding empty queries in tsearch

[Doug Cole]

I am having trouble with to_tsquery when the query is all stop words.
Rather than return everything as a match, it returns nothing with the
notice:

NOTICE:  Query contains only stopword(s) or doesn't contain lexem(s), ignored

What is the best way to check for this, I was hoping to be able to
check if the resulting tsquery was empty or null, but haven't found a
way.  Any advice?
Thanks for your help,
Doug

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

Re: [GENERAL] Autovacuum Improvements

[Pavan Deolasee]

Simon Riggs wrote:
> On Fri, 2006-12-29 at 20:25 -0300, Alvaro Herrera wrote:
>> Christopher Browne wrote:
>>
>>> Seems to me that you could get ~80% of the way by having the simplest
>>> "2 queue" implementation, where tables with size < some threshold get
>>> thrown at the "little table" queue, and tables above that size go to
>>> the "big table" queue.
>>>
>>> That should keep any small tables from getting "vacuum-starved."
>

This is exectly what I am trying, two process autovacuum and a GUC to
seperate small tables.

In this case, one process takes up vacuuming of the small tables and
other process vacuuming of the remaining tables as well as Xid
avoidance related vacuuming. The goal is to avoid starvation of small
tables when a large table is being vacuumed (which may take
several hours) without adding too much complexity to the code.

>
> Some feedback from initial testing is that 2 queues probably isn't
> enough. If you have tables with 100s of blocks and tables with millions
> of blocks, the tables in the mid-range still lose out. So I'm thinking
> that a design with 3 queues based upon size ranges, plus the idea that
> when a queue is empty it will scan for tables slightly above/below its
> normal range. That way we wouldn't need to specify the cut-offs with a
> difficult to understand new set of GUC parameters, define them exactly
> and then have them be wrong when databases grow.
>
> The largest queue would be the one reserved for Xid wraparound
> avoidance. No table would be eligible for more than one queue at a time,
> though it might change between queues as it grows.
>
> Alvaro, have you completed your design?
>
> Pavan, what are your thoughts?
>

IMO 2-queue is a good step forward, but in long term we may need to go
for a multiprocess autovacuum where the number and tasks of processes
are either demand based and/or user configurable.

Another idea is to vacuum the tables in round-robin fashion
where the quantum could be either "time" or "number of block". The
autovacuum process would vacuum 'x' blocks of one table and then
schedule next table in the queue. This would avoid starvation of
small tables, though cost of index cleanup might go up because of
increased IO. Any thoughts of this approach ?

Thanks,
Pavan




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

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

Re: [GENERAL] index type for indexing long texts

[Aleksander Kmetec]

Thank you both for your suggestions.

I think I'll try the GiST approach first since using an existing contrib extension as a starting point seems like a 
simpler task for someone like me. :)


Regards,
Aleksander

Richard Troy wrote:



Aleksander Kmetec <[EMAIL PROTECTED]> writes:

I'm looking for a solution for indexing long TEXT columns. We're currently 
using a HASH index, which can handle most
situations, but every now and then we need support for even longer texts.
One solution would be to create a functional index which would only use the 
first N chars of mycol, but then we'd have
to change several hundred occurences of "mycol = someval" with "(mycol = someval AND 
firstN(mycol) = firstN(someval))",
as well as update some SQL generators...
That's why I'd be interested to know if there are any index types available 
which store only the first N chars or use
some highly compressed form for storing index data, and then recheck any 
potential hits against the main table. And if
something like that does not exist yet, how difficult would it be to construct such a 
solution out of many "spare parts"
that come with PG?


Try moving where the hash takes place - ie, use your own hash function to
create the key.

RT





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

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

[GENERAL] Runtime error when calling function from .NET ( Function returns record)

[dparent]

I have a function which returns TYPE RECORD and receives a string. The passed
string is used to build a dynamic SQL statement and the passed string is the
SELECT LIST of the built dynamic SQL statement.

The call works fine from Postgres SQL (PL/PG SQL) but when I make the call
from .NET (Core labs data provider), I get a runtime error.

Any ideas on how to get this to work ? One solution we came up with is to
use a REFCURSOR or to return back a string to .NET and have .NET execute the
string (ie. select * from test2('feedid') as (name bigint);). 

We were hoping for an easier &/or better solution.

Example function:
CREATE or REPLACE FUNCTION "public"."test2"(
IN "_sfieldlist" varchar)
RETURNS SETOF "pg_catalog"."record" AS
$BODY$
DECLARE
v_feed   RECORD;
v_sfieldlist varchar(512);
BEGIN
v_sfieldlist :=   _sfieldlist;
   
FOR v_feed IN EXECUTE '
SELECT '||v_sfieldlist||'
FROM feed'
LOOP
RETURN NEXT v_feed;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE 'plpgsql' IMMUTABLE SECURITY DEFINER;

This works just ducky from PL/PG SQL when I run a select something like the
following:
  select * from test2('feedid') as (name bigint);

However, when I make a straight call to this function from .NET we get a
runtime error (similar to the error you would get if you ran the following:
select * from test2('feedid')).
-- 
View this message in context: 
http://www.nabble.com/Runtime-error-when-calling-function-from-.NET-%28-Function-returns-record%29-tf3014812.html#a8372092
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Backup the part of postgres database

[btober]

Joshua D. Drake wrote:
roopa perumalraja wrote: 
  Thanks a lot for your immediate reply. can you please tell me how to use the command pg_dump to backup specific tables. Thanks a lot in advance.
  
pg_dump --help


Better yet, try this first: 
"http://www.catb.org/~esr/faqs/smart-questions.html";.





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

  http://archives.postgresql.org/

Re: [GENERAL] check table existence...

[Moritz Bayer]

Thanks, that's exactly what I was looking for :-)

kind regards,
Morirt



2007/1/15, A. Kretschmer <[EMAIL PROTECTED]>:


am  Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer folgendes:
> Dear list,
>
> I would like to create a function which gets a tablename and checks if
the
> specific table exists.The return value should be a bool.
> Now I'm wondering how to do this the best way.
>
> Any suggestions?

You can ask pg_tables:

select count(1) from pg_tables where tablename = 'foo' and schemaname =
'public';


This ask for a table called 'foo' in the schema 'public'.


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 4: Have you searched our list archives?

  http://archives.postgresql.org/

Re: [GENERAL] Performance with very large tables

[Shoaib Mir]

An old post on the archives might help you -->
http://archives.postgresql.org/pgsql-interfaces/2005-11/msg00010.php that
was an attempt to simulate functionality similar to setFetchSize in JDBC.

---
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 1/15/07, Jan van der Weijde <[EMAIL PROTECTED]> wrote:


 Unfortunately a large C program has already been written.. But if a
function like PQsetFetchSize() was available in libpq, that would also solve
the problem.

 --
*From:* Shoaib Mir [mailto:[EMAIL PROTECTED]
*Sent:* Monday, January 15, 2007 13:49
*To:* Jan van der Weijde
*Cc:* Alban Hertroys; pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] Performance with very large tables

If you go with Java, you can make it faster by using setFetchSize (JDBC
functionality) from client and that will help you with the performance in
case of fetching large amounts of data.

---
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)


On 1/15/07, Jan van der Weijde < [EMAIL PROTECTED]> wrote:
>
> That is exactly the problem I think. However I do not deliberately
> retrieve the entire table. I use the default settings of the PostgreSQL
> installation and just execute a simple SELECT * FROM table.
> I am using a separate client and server (both XP in the test
> environment), but that should not make much difference.
> I would expect that the default behavior of PostgreSQL should be such
> that without LIMIT, a SELECT returns records immediately.
>
> Thank you,
> Jan
>
> -Original Message-
> From: Alban Hertroys [mailto: [EMAIL PROTECTED]
> Sent: Monday, January 15, 2007 12:49
> To: Jan van der Weijde
> Cc: Richard Huxton; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Performance with very large tables
>
> Jan van der Weijde wrote:
> > Thank you.
> > It is true he want to have the first few record quickly and then
> > continue with the next records. However without LIMIT it already takes
>
>
> > a very long time before the first record is returned.
> > I reproduced this with a table with 1.1 million records on an XP
> > machine and in my case it took about 25 seconds before the select
> > returned the first record. I tried it both interactively with pgAdmin
> > and with a C-application using a cursor (with hold). Both took about
> the same time.
>
> Are you sure you don't retrieve the entire result set first, and only
> start iterating it after that? Notably the fact that LIMIT changes this
> behaviour seems to point in that direction.
>
> A quick calculation shows that (provided my assumption holds true)
> fetching each record takes about 12.5 usec on average (25s / 2m
> records). A quick test on our dev-db fetches (~40k records) in 5 usec
> average, so that looks reasonable to me (apples and oranges, I know).
>
> --
> Alban Hertroys
> [EMAIL PROTECTED]
>
> magproductions b.v.
>
> T: ++31(0)534346874
> F: ++31(0)534346876
> M:
> I: www.magproductions.nl
> A: Postbus 416
>7500 AK Enschede
>
> // Integrate Your World //
>
> ---(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] Performance with very large tables

[Gregory S. Williamson]

Limit is somewhat magical ... at least to a degree. Not sure about cursors 
since I am not currently using them.

select count(*) from bill_rpt_work;
  count
-
 2317451
(1 row)

Time: 1709.829 ms

billing=# \d bill_rpt_work
  Table "reporting.bill_rpt_work"
Column | Type  | Modifiers
---+---+---
 report_id | integer   |
 client_id | character varying(10) |
 contract_id   | integer   | not null
 rate  | numeric   | not null
 appid | character varying(10) | not null
 userid| text  | not null
 collection_id | integer   | not null
 client_name   | character varying(60) |
 use_sius  | integer   | not null
 is_subscribed | integer   | not null
 hits  | numeric   | not null
 sius  | numeric   | not null
 total_amnt| numeric   | not null
 royalty_total | numeric   |
Indexes:
"billrptw_ndx" UNIQUE, btree (report_id, client_id, contract_id, rate, 
appid, userid, collection_id)
"billrpt_cntrct_ndx" btree (report_id, contract_id, client_id)
"billrpt_collid_ndx" btree (report_id, collection_id, client_id, 
contract_id)
Foreign-key constraints:
"$1" FOREIGN KEY (report_id) REFERENCES billing_reports(report_id)
"$2" FOREIGN KEY (client_id) REFERENCES "work".clients(client_id)

billing=# select * from bill_rpt_work order by report_id, client_id, 
contract_id, rate, appid, userid, collection_id;
Cancel request sent

after more than 10 seconds

THEN:

select * from bill_rpt_work order by report_id, client_id, contract_id, rate, 
appid, userid, collection_id limit 1000;
 report_id | client_id  | contract_id |  rate  |  appid   |
userid
   | collection_id | client_name | 
use_sius | is_subscr
ibed |hits |  sius  | total_amnt |  
 royalty_total

---++-++--+
---+---+-+--+--
-+-+++-
---
<...deleted details...>
Time: 52.745 ms

THEN:

billing=#  select * from bill_rpt_work order by report_id, client_id, 
contract_id, rate, appid, userid, collection_id limit 10;
 report_id | client_id  | contract_id |   rate   |  appid   |   
  u
serid | collection_id | 
client_name
 | use_sius | is_subscribed | hits |  sius  
| total
_amnt |   royalty_total
---++-+--+--+--
--+---+
-+--+---+--++--
--+
<...deleted details...>
Time: 1043.582 ms

Noticibly longer but not bad ...

But with no limit it takes quite a while:
select * from bill_rpt_work order by report_id, client_id, contract_id, rate, 
appid, userid, collection_id;
<...>
Time: 132033.171 ms

but with a limit, even a fairly large one (a little less than half the table) 
it was markedly faster. Maybe try more than half, see if there's a limit to 
what you can do ...

YMMV, HTH, yadda yadda ...

Greg Williamson
DBA
GlobeXplorer LLC (part of Digital Globe Inc.) 

-Original Message-
From:   [EMAIL PROTECTED] on behalf of Jan van der Weijde
Sent:   Mon 1/15/2007 4:44 AM
To: Alban Hertroys
Cc: pgsql-general@postgresql.org
Subject:Re: [GENERAL] Performance with very large tables

That is exactly the problem I think. However I do not deliberately
retrieve the entire table. I use the default settings of the PostgreSQL
installation and just execute a simple SELECT * FROM table. 
I am using a separate client and server (both XP in the test
environment), but that should not make much difference.
I would expect that the default behavior of PostgreSQL should be such
that without LIMIT, a SELECT returns records immediately. 

Thank you,
Jan

-Original Message-
From: Alban Hertroys [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 15, 2007 12:49
To: Jan van der Weijde
Cc: Richard Huxton; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance with very large tables

Jan van der Weijde wrote:
> Thank you. 
> It is true he want to have the first few record quickly and then 
> continue with the next records. However without LIMIT it already takes

> a very long time before the first record is returned.
> I reproduced this with a table with 1.1 million records on an XP 
> machine and in my case it took about 25

Re: [GENERAL] Performance with very large tables

[Richard Huxton]

Jan van der Weijde wrote:

That is exactly the problem I think. However I do not deliberately
retrieve the entire table. I use the default settings of the PostgreSQL
installation and just execute a simple SELECT * FROM table. 
I am using a separate client and server (both XP in the test

environment), but that should not make much difference.
I would expect that the default behavior of PostgreSQL should be such
that without LIMIT, a SELECT returns records immediately. 


No it doesn't. You've asked for all the records, so it assumes you want 
all the records. If you want a few at a time, use a cursor.

--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Performance with very large tables

[Jan van der Weijde]

Unfortunately a large C program has already been written.. But if a
function like PQsetFetchSize() was available in libpq, that would also
solve the problem.



From: Shoaib Mir [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 15, 2007 13:49
To: Jan van der Weijde
Cc: Alban Hertroys; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance with very large tables


If you go with Java, you can make it faster by using setFetchSize (JDBC
functionality) from client and that will help you with the performance
in case of fetching large amounts of data.

---
Shoaib Mir 
EnterpriseDB (www.enterprisedb.com)



On 1/15/07, Jan van der Weijde < [EMAIL PROTECTED]
 > wrote: 

That is exactly the problem I think. However I do not
deliberately 
retrieve the entire table. I use the default settings of the
PostgreSQL
installation and just execute a simple SELECT * FROM table.
I am using a separate client and server (both XP in the test
environment), but that should not make much difference. 
I would expect that the default behavior of PostgreSQL should be
such
that without LIMIT, a SELECT returns records immediately.

Thank you,
Jan

-Original Message-
From: Alban Hertroys [mailto: [EMAIL PROTECTED]
Sent: Monday, January 15, 2007 12:49
To: Jan van der Weijde
Cc: Richard Huxton; pgsql-general@postgresql.org 
Subject: Re: [GENERAL] Performance with very large tables

Jan van der Weijde wrote:
> Thank you.
> It is true he want to have the first few record quickly and
then
> continue with the next records. However without LIMIT it
already takes 

> a very long time before the first record is returned.
> I reproduced this with a table with 1.1 million records on an
XP
> machine and in my case it took about 25 seconds before the
select
> returned the first record. I tried it both interactively with
pgAdmin 
> and with a C-application using a cursor (with hold). Both took
about
the same time.

Are you sure you don't retrieve the entire result set first, and
only
start iterating it after that? Notably the fact that LIMIT
changes this 
behaviour seems to point in that direction.

A quick calculation shows that (provided my assumption holds
true)
fetching each record takes about 12.5 usec on average (25s / 2m
records). A quick test on our dev-db fetches (~40k records) in 5
usec 
average, so that looks reasonable to me (apples and oranges, I
know).

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874 
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(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] Performance with very large tables

[Shoaib Mir]

If you go with Java, you can make it faster by using setFetchSize (JDBC
functionality) from client and that will help you with the performance in
case of fetching large amounts of data.

---
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)


On 1/15/07, Jan van der Weijde <[EMAIL PROTECTED]> wrote:


That is exactly the problem I think. However I do not deliberately
retrieve the entire table. I use the default settings of the PostgreSQL
installation and just execute a simple SELECT * FROM table.
I am using a separate client and server (both XP in the test
environment), but that should not make much difference.
I would expect that the default behavior of PostgreSQL should be such
that without LIMIT, a SELECT returns records immediately.

Thank you,
Jan

-Original Message-
From: Alban Hertroys [mailto:[EMAIL PROTECTED]
Sent: Monday, January 15, 2007 12:49
To: Jan van der Weijde
Cc: Richard Huxton; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance with very large tables

Jan van der Weijde wrote:
> Thank you.
> It is true he want to have the first few record quickly and then
> continue with the next records. However without LIMIT it already takes

> a very long time before the first record is returned.
> I reproduced this with a table with 1.1 million records on an XP
> machine and in my case it took about 25 seconds before the select
> returned the first record. I tried it both interactively with pgAdmin
> and with a C-application using a cursor (with hold). Both took about
the same time.

Are you sure you don't retrieve the entire result set first, and only
start iterating it after that? Notably the fact that LIMIT changes this
behaviour seems to point in that direction.

A quick calculation shows that (provided my assumption holds true)
fetching each record takes about 12.5 usec on average (25s / 2m
records). A quick test on our dev-db fetches (~40k records) in 5 usec
average, so that looks reasonable to me (apples and oranges, I know).

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(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] check table existence...

[Shoaib Mir]

Something like this will help you..

===

CREATE OR REPLACE FUNCTION public.check_table(varchar, varchar)
RETURNS boolean AS $$
DECLARE
  v_cnt integer;
  v_tbl boolean;
BEGIN
  SELECT count(1) INTO v_cnt FROM pg_tables where tablename = $1 and
schemaname = $2;

   IF v_cnt > 0 THEN
v_tbl = 'true';
   END IF;

   IF v_cnt = 0 THEN
v_tbl = 'false';
   END IF;
return v_tbl;
END;
 $$ LANGUAGE 'plpgsql'

=

select check_table('emp', 'public');

---
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 1/15/07, Alban Hertroys <[EMAIL PROTECTED]> wrote:


A. Kretschmer wrote:
> am  Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer
folgendes:
>> Dear list,
>>
>> I would like to create a function which gets a tablename and checks if
the
>> specific table exists.The return value should be a bool.
>> Now I'm wondering how to do this the best way.
>>
>> Any suggestions?
>
> You can ask pg_tables:
>
> select count(1) from pg_tables where tablename = 'foo' and schemaname =
'public';
>
>
> This ask for a table called 'foo' in the schema 'public'.

If you do (something like) that in pl/pgsql, you could RETURN FOUND
after performing that query.

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

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

Re: [GENERAL] Performance with very large tables

[Jan van der Weijde]

That is exactly the problem I think. However I do not deliberately
retrieve the entire table. I use the default settings of the PostgreSQL
installation and just execute a simple SELECT * FROM table. 
I am using a separate client and server (both XP in the test
environment), but that should not make much difference.
I would expect that the default behavior of PostgreSQL should be such
that without LIMIT, a SELECT returns records immediately. 

Thank you,
Jan

-Original Message-
From: Alban Hertroys [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 15, 2007 12:49
To: Jan van der Weijde
Cc: Richard Huxton; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance with very large tables

Jan van der Weijde wrote:
> Thank you. 
> It is true he want to have the first few record quickly and then 
> continue with the next records. However without LIMIT it already takes

> a very long time before the first record is returned.
> I reproduced this with a table with 1.1 million records on an XP 
> machine and in my case it took about 25 seconds before the select 
> returned the first record. I tried it both interactively with pgAdmin 
> and with a C-application using a cursor (with hold). Both took about
the same time.

Are you sure you don't retrieve the entire result set first, and only
start iterating it after that? Notably the fact that LIMIT changes this
behaviour seems to point in that direction.

A quick calculation shows that (provided my assumption holds true)
fetching each record takes about 12.5 usec on average (25s / 2m
records). A quick test on our dev-db fetches (~40k records) in 5 usec
average, so that looks reasonable to me (apples and oranges, I know).

--
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

---(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] check table existence...

[Alban Hertroys]

A. Kretschmer wrote:
> am  Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer folgendes:
>> Dear list,
>>  
>> I would like to create a function which gets a tablename and checks if the
>> specific table exists.The return value should be a bool. 
>> Now I'm wondering how to do this the best way.
>>  
>> Any suggestions?
> 
> You can ask pg_tables:
> 
> select count(1) from pg_tables where tablename = 'foo' and schemaname = 
> 'public';
> 
> 
> This ask for a table called 'foo' in the schema 'public'.

If you do (something like) that in pl/pgsql, you could RETURN FOUND
after performing that query.

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

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

Re: [GENERAL] check table existence...

[A. Kretschmer]

am  Mon, dem 15.01.2007, um 13:18:11 +0100 mailte Moritz Bayer folgendes:
> Dear list,
>  
> I would like to create a function which gets a tablename and checks if the
> specific table exists.The return value should be a bool. 
> Now I'm wondering how to do this the best way.
>  
> Any suggestions?

You can ask pg_tables:

select count(1) from pg_tables where tablename = 'foo' and schemaname = 
'public';


This ask for a table called 'foo' in the schema 'public'.


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 4: Have you searched our list archives?

   http://archives.postgresql.org/

[GENERAL] check table existence...

[Moritz Bayer]

Dear list,

I would like to create a function which gets a tablename and checks if the
specific table exists.The return value should be a bool.
Now I'm wondering how to do this the best way.

Any suggestions?

kind regards and thanks in advance,

Moritz

[GENERAL] Persistent connections in PHP with PDO

[Alan T. Miller]

Has anyone played around with the new PHP ODO drivers and been able to 
successfully set up an object using persistent connections? I tried to 
follow the documentation in the PHP manual to send an array in the PDO 
constructor but receive a warning message that the underlying driver 
does not support this. I mucked around in the source code for the driver 
in the PHP source tree and it appears there is no facility in the driver 
for sending such options, which leaves me to wonder if you simply set 
the php.ini value to support persistent connections that would be all 
you need to do? Can anyone shed any light on this problem? There seems 
to be little if any documentation on this.


Thanks in advance.

Alan

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

Re: [GENERAL] Performance with very large tables

[Alban Hertroys]

Jan van der Weijde wrote:
> Thank you. 
> It is true he want to have the first few record quickly and then
> continue with the next records. However without LIMIT it already takes a
> very long time before the first record is returned. 
> I reproduced this with a table with 1.1 million records on an XP machine
> and in my case it took about 25 seconds before the select returned the
> first record. I tried it both interactively with pgAdmin and with a
> C-application using a cursor (with hold). Both took about the same time.

Are you sure you don't retrieve the entire result set first, and only
start iterating it after that? Notably the fact that LIMIT changes this
behaviour seems to point in that direction.

A quick calculation shows that (provided my assumption holds true)
fetching each record takes about 12.5 usec on average (25s / 2m
records). A quick test on our dev-db fetches (~40k records) in 5 usec
average, so that looks reasonable to me (apples and oranges, I know).

-- 
Alban Hertroys
[EMAIL PROTECTED]

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

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

   http://archives.postgresql.org/

Re: [GENERAL] Performance with very large tables

[Shoaib Mir]

Oh yes, need to have a condition first for which you have partitioned
tables. Only in that case it will work with partitions.

---
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 1/15/07, Richard Huxton  wrote:


Shoaib Mir wrote:
> You can also opt for partitioning the tables and this way select will
only
> get the data from the required partition.

Not in the case of SELECT * FROM  though. Unless you access the
specific partitioned table.

> On 1/15/07, Richard Huxton  wrote:
>>
>> Jan van der Weijde wrote:
>> > Hello all,
>> >
>> > one of our customers is using PostgreSQL with tables containing
>> millions
>> > of records. A simple 'SELECT * FROM '  takes way too much
>> time in
>> > that case, so we have advised him to use the LIMIT and OFFSET
clauses.

--
   Richard Huxton
   Archonet Ltd

Re: [GENERAL] Performance with very large tables

[Richard Huxton]

Jan van der Weijde wrote:
Thank you. 
It is true he want to have the first few record quickly and then

continue with the next records. However without LIMIT it already takes a
very long time before the first record is returned. 
I reproduced this with a table with 1.1 million records on an XP machine

and in my case it took about 25 seconds before the select returned the
first record. I tried it both interactively with pgAdmin and with a
C-application using a cursor (with hold). Both took about the same time.


That doesn't sound right to me, not for a straigforward SELECT * FROM 
where there's no sorting etc.


--
  Richard Huxton
  Archonet Ltd

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

Re: [GENERAL] Performance with very large tables

[Richard Huxton]

Shoaib Mir wrote:

You can also opt for partitioning the tables and this way select will only
get the data from the required partition.


Not in the case of SELECT * FROM  though. Unless you access the 
specific partitioned table.



On 1/15/07, Richard Huxton  wrote:


Jan van der Weijde wrote:
> Hello all,
>
> one of our customers is using PostgreSQL with tables containing 
millions
> of records. A simple 'SELECT * FROM '  takes way too much 
time in

> that case, so we have advised him to use the LIMIT and OFFSET clauses.


--
  Richard Huxton
  Archonet Ltd

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

  http://archives.postgresql.org/

Re: [GENERAL] Performance with very large tables

[Jan van der Weijde]

Thank you. 
It is true he want to have the first few record quickly and then
continue with the next records. However without LIMIT it already takes a
very long time before the first record is returned. 
I reproduced this with a table with 1.1 million records on an XP machine
and in my case it took about 25 seconds before the select returned the
first record. I tried it both interactively with pgAdmin and with a
C-application using a cursor (with hold). Both took about the same time.

Thanks,
Jan van der Weijde

-Original Message-
From: Richard Huxton [mailto:[EMAIL PROTECTED] 
Sent: Monday, January 15, 2007 12:01
To: Jan van der Weijde
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Performance with very large tables

Jan van der Weijde wrote:
> Hello all,
>  
> one of our customers is using PostgreSQL with tables containing 
> millions of records. A simple 'SELECT * FROM '  takes way too 
> much time in that case, so we have advised him to use the LIMIT and
OFFSET clauses.

That won't reduce the time to fetch millions of rows.

It sounds like your customer doesn't want millions of rows at once, but
rather a few rows quickly and then to fetch more as required. For this
you want to use a cursor. You can do this via SQL, or perhaps via your
database library.

In SQL:
http://www.postgresql.org/docs/8.2/static/sql-declare.html
http://www.postgresql.org/docs/8.2/static/sql-fetch.html
In pl/pgsql:
http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html

HTH
-- 
   Richard Huxton
   Archonet Ltd

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

Re: [GENERAL] Performance with very large tables

[Shoaib Mir]

You can also opt for partitioning the tables and this way select will only
get the data from the required partition.

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 1/15/07, Richard Huxton  wrote:


Jan van der Weijde wrote:
> Hello all,
>
> one of our customers is using PostgreSQL with tables containing millions
> of records. A simple 'SELECT * FROM '  takes way too much time in
> that case, so we have advised him to use the LIMIT and OFFSET clauses.

That won't reduce the time to fetch millions of rows.

It sounds like your customer doesn't want millions of rows at once, but
rather a few rows quickly and then to fetch more as required. For this
you want to use a cursor. You can do this via SQL, or perhaps via your
database library.

In SQL:
http://www.postgresql.org/docs/8.2/static/sql-declare.html
http://www.postgresql.org/docs/8.2/static/sql-fetch.html
In pl/pgsql:
http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html

HTH
--
   Richard Huxton
   Archonet Ltd

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

Re: [GENERAL] Performance with very large tables

[Richard Huxton]

Jan van der Weijde wrote:

Hello all,
 
one of our customers is using PostgreSQL with tables containing millions

of records. A simple 'SELECT * FROM '  takes way too much time in
that case, so we have advised him to use the LIMIT and OFFSET clauses.


That won't reduce the time to fetch millions of rows.

It sounds like your customer doesn't want millions of rows at once, but 
rather a few rows quickly and then to fetch more as required. For this 
you want to use a cursor. You can do this via SQL, or perhaps via your 
database library.


In SQL:
http://www.postgresql.org/docs/8.2/static/sql-declare.html
http://www.postgresql.org/docs/8.2/static/sql-fetch.html
In pl/pgsql:
http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html

HTH
--
  Richard Huxton
  Archonet Ltd

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

[GENERAL] Performance with very large tables

[Jan van der Weijde]

Hello all,
 
one of our customers is using PostgreSQL with tables containing millions
of records. A simple 'SELECT * FROM '  takes way too much time in
that case, so we have advised him to use the LIMIT and OFFSET clauses.
However now he has a concurrency problem. Records deleted, added or
updated in one process have an influence on the OFFSET value of another
process such that records are either skipped of read again. 
The solution to that problem is to use transactions with isolation level
serializable. But to use transactions around a loop that reads millions
of records is far from ideal I think.
Does anyone have a suggestion for this problem ? Is there for instance
an alternative to LIMIT/OFFSET so that SELECT on large tables has a good
performance ?
 
Thank you for your help
 
Jan van der Weijde
 

Re: [GENERAL] Backup the part of postgres database

[Shoaib Mir]

COPY command might also help

COPY (SELECT * FROM country WHERE country_name LIKE 'A%') TO
'/usr1/proj/bray/sql/a_list_countries.copy';

Details can be found at -->
http://www.postgresql.org/docs/current/static/sql-copy.html

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 1/15/07, roopa perumalraja <[EMAIL PROTECTED]> wrote:


Hi all,

Is it possible to back up only part of my database for example from tables
which have data from April to July 2006, if so what will be the command for
that.

Thanks a lot in advance
Roopa

--
It's here! Your new message!
Get new email 
alertswith
 the free Yahoo!
Toolbar.