Hi Sergey,
- It's just guess but it could be the range of a SERIAL TYPE that is
generating this behavior.
An example is:
Knowing that table1_id is primary a key ( the table will be ordered
by it ) and that a serial range is 2147483647 long.
(a) you use 2000 different numbers of this range
Web Application Engineer - Drupal, PHP, CSS, JavaScript, Postgresql
Position Title: Web Application Developer - Drupal, PHP, CSS,
JavaScript, Postgresql, Linux, HTML, Apache
Location: Boston, MA (Back Bay - Prudential Building - Commuter Rail
and T-accessible).
Status: Full Time, Regular
On Tue, 2008-11-11 at 06:45 -0800, NetGraviton wrote:
Web Application Engineer - Drupal, PHP, CSS, JavaScript, Postgresql
Please use the proper list... pgsql-jobs.
Joshua D. Drake
--
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your
Hello all,
I have one question about converting timestamps to text. I believed
that using to_char function with proper mask and casting by style
COLUMN::text is equal, but when I issue following query against 8.3.3
database it returns me different values (they differ on last position
of
I did \timing and run my query in console. You can find the result in
attachement. Will it be enough?
Very strange.
The explain runtime is 3.1 seconds, but \timing shows 37.8 seconds
before it returns.
And it only does this for the NOT IN version of the query, but the IN
version seems
Hi,
I've noticed that if I set my SHMMAX to 256 * 1024 * 1024 (268435456)
and my shared_buffers value in postgresql.conf to 256MB the server
fails to start. I managed to find a tipping point: 249MB seems to be
too much and 248MB seems to be okay.
Could someone explain what I'm missing here? I
Thanks Erik...
I found an alternative to psql copy to stdout | psql copy from stdout.
I used pg_dump -n schema | psql
This approach replicated the entire schema, rather than just the table
contents,
into the new database, and therefore copied over all the seq data as well. It
worked well in
On Tue, Nov 11, 2008 at 02:17:20PM -0300, Alvaro Herrera wrote:
Thom Brown escribió:
I've noticed that if I set my SHMMAX to 256 * 1024 * 1024 (268435456)
and my shared_buffers value in postgresql.conf to 256MB the server
fails to start. I managed to find a tipping point: 249MB seems to be
Sergey Konoplev wrote:
Can you post the EXPLAIN ANALYSE output from your NOT IN query?
Seq Scan on table1 (cost=0.00..12648.25 rows=3351 width=0) (actual
time=0.054..140.596 rows=5000 loops=1)
Filter: (table1_id ALL ('{123456789000, ... plus 1999 ids'::bigint[]))
Total runtime:
It isn't guaranteed, but I think a clustered index on the attrs you want
the dump ordered by will give an ordered dump.
This may depend on your filesystem, and on what else your system
is doing at the time, as interupted disk reads may disrupt the sequence.
It has worked for me on Suse Linux
Yasuo Ohgaki [EMAIL PROTECTED] writes:
It would be nice to have API like PQquerySingle that allows only a single SQL
statement at a time.
We have one (the extended query protocol).
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
On Tue, Nov 11, 2008 at 10:58 AM, Sam Mason [EMAIL PROTECTED] wrote:
On Tue, Nov 11, 2008 at 02:17:20PM -0300, Alvaro Herrera wrote:
Thom Brown escribió:
I've noticed that if I set my SHMMAX to 256 * 1024 * 1024 (268435456)
and my shared_buffers value in postgresql.conf to 256MB the server
Scott Marlowe escribió:
On Tue, Nov 11, 2008 at 10:58 AM, Sam Mason [EMAIL PROTECTED] wrote:
what is this extra slop needed for?
free space map and temp buffers. Not sure what else.
Lock space, pg_clog, pg_subtrans and pg_multixact buffers, FSM, and some
other things. External modules can
I'm not sure if this is the write place to post this question or not, but I
hope someone can help me out.
I am using the copy_from command from the python psycopg2 library to do some
bulk data loading of a postgres database. This had been working OK, until
my script barfed because I was being
Sergey Konoplev wrote:
Finally - did you compile this from source yourself, or is it installed
via apt? I'm wondering whether you have an unusual version of a library
linked in, and it's taking a long time to parse the query.
I've compiled it from sources. BTW, I tested it on both 8.3.3
Glen Beane [EMAIL PROTECTED] writes:
I am using the copy_from command from the python psycopg2 library to do some
bulk data loading of a postgres database. This had been working OK, until
my script barfed because I was being careless, and it seemed to leave the
database in a strange state
Sam Mason [EMAIL PROTECTED] writes:
On Tue, Nov 11, 2008 at 02:17:20PM -0300, Alvaro Herrera wrote:
shared_buffers is not the only factor to shared memory, so you need to
provide some extra SHMMAX slop.
what is this extra slop needed for?
See table 17-2 here
Tom Lane wrote:
Richard Huxton [EMAIL PROTECTED] writes:
I've never heard of EXPLAIN ANALYSE being *faster* than the actual
query, it's usually slower due to all the timing calls. The only thing
it doesn't do is actually send the results over the connection to the
client. In your case, you're
Sergey Konoplev wrote:
I've never heard of EXPLAIN ANALYSE being *faster* than the actual
query, it's usually slower due to all the timing calls. The only thing
it doesn't do is actually send the results over the connection to the
client. In your case, you're not actually selecting any
hello all,
I have 2 dumps of the same Pg database in diferent instants.
I'dd like to merge the two dumps into one single dump in order to
restore all data at one time.
Is this possible ? are there any helper tools to aid in dealing with
text dump files ?
thanks
Joao
--
Sent via
On Tue, 2008-11-11 at 11:16 +, Richard Huxton wrote:
Joao Ferreira gmail wrote:
hello all,
I have 2 dumps of the same Pg database in diferent instants.
I'dd like to merge the two dumps into one single dump in order to
restore all data at one time.
Is there any overlap in the
Developers,
It seems you are overlooking application user/system admin perspective.
I agree developers should use prepared statement, but application user or
system admins are not able to modify codes usually.
There are many PostgreSQL/MySQL applications that generating SQL statements.
MySQL's
I've never heard of EXPLAIN ANALYSE being *faster* than the actual
query, it's usually slower due to all the timing calls. The only thing
it doesn't do is actually send the results over the connection to the
client. In your case, you're not actually selecting any columns, so that
can't be it.
Sergey Konoplev [EMAIL PROTECTED] writes:
I've faced strange parser (or may be planner) behaviour. When I do
EXPLAIN SELECT 1 FROM table1 WHERE table1_id IN (...~2000 ids
here...); it works as fast as I expect (50 ms). But when I rewrite it
using NOT IN EXPLAIN SELECT 1 FROM table1 WHERE
Vaclav TVRDIK [EMAIL PROTECTED] writes:
I have one question about converting timestamps to text. I believed
that using to_char function with proper mask and casting by style
COLUMN::text is equal, but when I issue following query against 8.3.3
database it returns me different values (they
Sergey Konoplev [EMAIL PROTECTED] writes:
Finally - did you compile this from source yourself, or is it installed
via apt? I'm wondering whether you have an unusual version of a library
linked in, and it's taking a long time to parse the query.
I've compiled it from sources. BTW, I tested it
Richard Huxton [EMAIL PROTECTED] writes:
If you connect via psql and then (as root, in another terminal) do:
ps auxw | grep postgres
you should see the backend that corresponds to your psql connection.
strace -p pid
should then show system calls as they are executed (assuming you have it
Tom Lane wrote:
Hm, I can't replicate that here --- but if you're using floating-point
timestamps, as is default in 8.3, then a certain amount of
machine-dependent roundoff fuzziness is not surprising.
regards, tom lane
Column is defined in such way:
CREATE_DAT
Por favor, no quiero seguir recibiendo mensajes
Tom Lane escribió:
Richard Huxton [EMAIL PROTECTED] writes:
If you connect via psql and then (as root, in another terminal) do:
ps auxw | grep postgres
you should see the backend that corresponds to your psql connection.
strace -p pid
should
Por favor no quiero seguir recibiendo mensajes
Vaclav TVRDIK escribió:
Tom Lane wrote:
Hm, I can't replicate that here --- but if you're using floating-point
timestamps, as is default in 8.3, then a certain amount of
machine-dependent roundoff fuzziness is not surprising.
Hi. I found this somewhere:
select a from b order by a using ~~
I've been searching the docs but I found nothing about this weird ~~
comparator. Not in comparison operators, String functions and operators,
nor the order by clause. Maybe it has something to do with regular
expressions, but so
Joao Ferreira gmail wrote:
hello all,
I have 2 dumps of the same Pg database in diferent instants.
I'dd like to merge the two dumps into one single dump in order to
restore all data at one time.
Is there any overlap in the data?
If so, simplest might be to restore dump1, rename all the
Dear Group,
Please note that to avoid confusion with a previously subscribed
member, I have unsubscribed as James N Hitz ([EMAIL PROTECTED]), and I
am now using WaGathoni ([EMAIL PROTECTED]).
After all.. I realized GMail knits the threads together better than Yahoo.
Just thought, I should clear
Appreciate all the help. Thank you
On Mon, Nov 10, 2008 at 8:36 AM, Michael Black
[EMAIL PROTECTED] wrote:
James,
It is not good practice to delete an account with out first transfering the
amount in that account to another account. You will also need to make sure
the account has a zero
explain analyze SELECT sum(xxx)
FROM dok JOIN rid USING (dokumnr)
WHERE dok.kuupaev BETWEEN '2008-04-01' AND '2008-04-30'
Hash Join (cost=29584.84..308259.32 rows=142686 width=0) (actual
time=68510.748..96932.174 rows=117883 loops=1)
Hash Cond: (outer.dokumnr = inner.dokumnr)
- Seq
Can you post the EXPLAIN ANALYSE output from your NOT IN query?
Seq Scan on table1 (cost=0.00..12648.25 rows=3351 width=0) (actual
time=0.054..140.596 rows=5000 loops=1)
Filter: (table1_id ALL ('{123456789000, ... plus 1999 ids'::bigint[]))
Total runtime: 142.303 ms
(3 rows)
But actual
Bruce Momjian wrote:
Bruce Momjian wrote:
Alvaro Herrera wrote:
Tom Lane escribi?:
Bruce Momjian [EMAIL PROTECTED] writes:
I don't know of a way to make MD5 and db_user_namespace work cleanly so
we are considering removing db_user_namespace in 8.4.
We are? It's no more or less ugly than
Sergey Konoplev wrote:
Another thing is that even I set statement_timeout to 20s the query
with NOT IN finishes working after 30+ seconds without canceled by
statement timeout error.
Maybe it's not taking that long to execute the query then.
Maybe something to do with process startup is
Joao Ferreira gmail wrote:
On Tue, 2008-11-11 at 11:16 +, Richard Huxton wrote:
Joao Ferreira gmail wrote:
hello all,
I have 2 dumps of the same Pg database in diferent instants.
I'dd like to merge the two dumps into one single dump in order to
restore all data at one time.
Is there
On Mon, Nov 10, 2008 at 18:14, Richard Huxton [EMAIL PROTECTED] wrote:
Dmitry Teslenko wrote:
Hello!
There's table:
CREATE TABLE table1 (
field1 CHARACTER(10),
...
);
Then there's record: INSERT INTO table1(field1, ..) VALUES ('111', ...);
Then I query it:
SELECT * FROM
Sergey Konoplev wrote:
Maybe something to do with process startup is delaying things - could
you tweak the test script to send the outputs of the explain somewhere
other than /dev/null? That way we'd know if there was a big difference
between query-execution-time and process-execution-time.
Dmitry Teslenko wrote:
richardh= SELECT * FROM chartbl WHERE c LIKE '111';
c
---
(0 rows)
richardh= SELECT * FROM chartbl WHERE c LIKE '111 ';
c
111
(1 row)
richardh= SELECT * FROM chartbl WHERE c LIKE '111%';
c
111
(1 row)
'111%'
Thom Brown escribió:
Hi,
I've noticed that if I set my SHMMAX to 256 * 1024 * 1024 (268435456)
and my shared_buffers value in postgresql.conf to 256MB the server
fails to start. I managed to find a tipping point: 249MB seems to be
too much and 248MB seems to be okay.
Could someone
I've never heard of EXPLAIN ANALYSE being *faster* than the actual
query, it's usually slower due to all the timing calls. The only thing
it doesn't do is actually send the results over the connection to the
client. In your case, you're not actually selecting any columns, so that
can't be it.
Richard Huxton [EMAIL PROTECTED] writes:
I've never heard of EXPLAIN ANALYSE being *faster* than the actual
query, it's usually slower due to all the timing calls. The only thing
it doesn't do is actually send the results over the connection to the
client. In your case, you're not actually
Actually, I think I may have solved it, but I would like someone to verify it.
The temp_buffers takes up 8MB which suggests that needs to be taken
into account, so 256MB - 8MB = 248MB.
Is this right?
Thanks
Thom
On Tue, Nov 11, 2008 at 4:57 PM, Thom Brown [EMAIL PROTECTED] wrote:
Hi,
I've
On Mon, Nov 10, 2008 at 02:30:41PM -0800, Steve Atkins wrote:
On Nov 10, 2008, at 1:35 PM, Tom Lane wrote:
Alvaro Herrera [EMAIL PROTECTED] writes:
It seems that there is enough need for this feature, that it has been
implemented multiple times -- but most of them will fail in corner
cases.
It seems that there is enough need for this feature, that it has been
implemented multiple times -- but most of them will fail in corner
cases. Seems an obvious candidate for an in-core function ...
... which will still fail in corner cases. Not to mention the race
condition when the logger
Hi all,
Here is my environment information:
# select version();
version
PostgreSQL 8.3.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC)
4.2.4 (Ubuntu
On 11/11/08 2:25 PM, Tom Lane [EMAIL PROTECTED] wrote:
Glen Beane [EMAIL PROTECTED] writes:
I am using the copy_from command from the python psycopg2 library to do some
bulk data loading of a postgres database. This had been working OK, until
my script barfed because I was being careless,
Hello.
Hope this will be helpful for agile developers.
http://en.dklab.ru/lib/dklab_pgunit/
PGUnit is a xUnit-style framework for stored procedures in PostgreSQL 8.3+.
It allows database developers to write automated tests for existed stored
procedures or develop procedures using concepts of
Sergey Konoplev wrote:
I've faced strange parser (or may be planner) behaviour. When I do
EXPLAIN SELECT 1 FROM table1 WHERE table1_id IN (...~2000 ids
here...); it works as fast as I expect (50 ms). But when I rewrite it
using NOT IN EXPLAIN SELECT 1 FROM table1 WHERE table1_id NOT IN
Diego Manilla Suárez wrote:
Hi. I found this somewhere:
select a from b order by a using ~~
I've been searching the docs but I found nothing about this weird ~~
comparator. Not in comparison operators, String functions and operators,
nor the order by clause. Maybe it has something to do
I'm still fighting with my very long gin index creation that happens
randomly.
At the beginning I had a pretty long transaction that filled several
tables starting from some temporary[1] tables.
After filling the tables I updated a tsvector column in one of them
and finally in another connection
On Tue, 11 Nov 2008, Thom Brown wrote:
I've noticed that if I set my SHMMAX to 256 * 1024 * 1024 (268435456)
and my shared_buffers value in postgresql.conf to 256MB the server
fails to start. I managed to find a tipping point: 249MB seems to be
too much and 248MB seems to be okay.
Buffers
On 11/11/08 2:25 PM, Tom Lane [EMAIL PROTECTED] wrote:
The most direct evidence about why it's stuck would probably be had by
attaching to the backend process with gdb and getting a stack trace.
It wasn't built with debugging symbols so there is some missing info, but
here is what I get if
Glen Beane [EMAIL PROTECTED] writes:
On 11/11/08 2:25 PM, Tom Lane [EMAIL PROTECTED] wrote:
The most direct evidence about why it's stuck would probably be had by
attaching to the backend process with gdb and getting a stack trace.
It wasn't built with debugging symbols so there is some
On 11/11/08 9:09 PM, Tom Lane [EMAIL PROTECTED] wrote:
Glen Beane [EMAIL PROTECTED] writes:
On 11/11/08 2:25 PM, Tom Lane [EMAIL PROTECTED] wrote:
The most direct evidence about why it's stuck would probably be had by
attaching to the backend process with gdb and getting a stack trace.
Andrus [EMAIL PROTECTED] writes:
explain analyze SELECT sum(xxx)
FROM dok JOIN rid USING (dokumnr)
WHERE dok.kuupaev BETWEEN '2008-04-01' AND '2008-04-30'
Hash Join (cost=29584.84..308259.32 rows=142686 width=0) (actual
time=68510.748..96932.174 rows=117883 loops=1)
Hash Cond:
On Tuesday 11 November 2008 6:20:09 pm Glen Beane wrote:
Python with the psycopg2 library. I swear this was working earlier today.
Maybe I am imagining things :)
It does work with a unix socket, and I have a deadline to meet, so for now
I just need to make sure I am running this on the same
On Tuesday 11 November 2008 6:31:04 pm Adrian Klaver wrote:
On Tuesday 11 November 2008 6:20:09 pm Glen Beane wrote:
Python with the psycopg2 library. I swear this was working earlier
today. Maybe I am imagining things :)
It does work with a unix socket, and I have a deadline to meet, so
Ivan Sergio Borgonovo [EMAIL PROTECTED] writes:
Any suggestion about how to track down the problem?
What you are describing sounds rather like a use-of-uninitialized-memory
problem, wherein the behavior depends on what happened to be in that
memory previously. If so, using a
Hi All,
--PG8.3
--Windows 2k3 SBS
I would like to apply an automatic filter condition to a table. I create a
TEMP table at the beginning of the session to store a value to build up a
filter condition and I would like to apply this condition to every select
statement to a table during the
am Wed, dem 12.11.2008, um 8:08:08 +0100 mailte Csaba Együd folgendes:
Hi All,
--PG8.3
--Windows 2k3 SBS
I would like to apply an automatic filter condition to a table. I create a
TEMP table at the beginning of the session to store a value to build up a
filter condition and I would
64 matches
Mail list logo
