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)
Hello all,
one of our customers is using PostgreSQL with tables containing millions
of records. A simple 'SELECT * FROM table' 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
Jan van der Weijde wrote:
Hello all,
one of our customers is using PostgreSQL with tables containing millions
of records. A simple 'SELECT * FROM table' 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
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 dev@archonet.com wrote:
Jan van der Weijde wrote:
Hello all,
one of our customers is
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
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 table though. Unless you access the
specific partitioned table.
On 1/15/07, Richard Huxton dev@archonet.com wrote:
Jan
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
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 dev@archonet.com wrote:
Shoaib Mir wrote:
You can also opt for partitioning
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
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
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
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
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.
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
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
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
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
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
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 |
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
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
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;.
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
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
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
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
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
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
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 (
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
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
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 -
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
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
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
[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,
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
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)
Yes, the problem was the memory (testing with memtest)
I took us long to have the machine check
Thanks,
- Original Message -
From: Richard Huxton dev@archonet.com
To: Ardian Xharra [EMAIL PROTECTED]
Cc: postgreSQL postgreSQL pgsql-general@postgresql.org
Sent: Friday, November 17, 2006
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
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
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.
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
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
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
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
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
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
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
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
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).
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
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?
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
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
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.
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
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
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
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
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
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
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
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
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
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
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
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
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 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
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
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
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
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
74 matches
Mail list logo
