On Fri, Jul 10, 2009 at 4:02 PM, Steve Crawford
wrote:
>
> Peter Hunsberger wrote:
>>
>> We're looking at potentially using Postgres to store a variety of molecular
>> and genetic data. At this point I have a bunch of general questions...
>
> I don't know enough about your area of expertise to k
Hello-
I am working on an e-commerce system that has different lists of products
which contain many of the same products, at different prices. When a user
searches for a certain set of part numbers, I would like the resulting
products (and prices) to come from one of the lists, according to the
l
We have a java web page that will give us the stack trace of all the
running JDBC connections inside our system. The problem is that we
currently have no way of relating those stack traces back to a PID so
the programmers can get the stack trace of the hung database connection.
We use the JDBC con
On Tue, Jul 07, 2009 at 06:45:36PM -0700, Dennis Gearon wrote:
> When locking is involved, does a transaction wait for access to a row
> or table, or does it just fail back to the calling code? Would it be
> up to my PHP code to keep hammeing for access to a row/table, or could
> a user defined fun
On Jul 10, 2009, at 1:31 PM, James B. Byrne wrote:
On Fri, July 10, 2009 16:10, hubert depesz lubaczewski wrote:
truncate. but first simple question - did you commit the inserts?
But if it were done with truncate then I would see truncate in the
log file, yes?
Second, I am working with
Hi. Thanks for the quick and definitive answers to my questions.
The information you provided will save me from wasting time and
energy trying to see how far I could get otherwise. Thanks very much.
Janet
Tom Lane wrote:
> Janet Jacobsen writes:
>
>> Is it possible to create a database clu
On Jul 10, 2009, at 3:34 PM, Scott Marlowe wrote:
Assuming that tracking down the process that's connected might help,
you can use pg_stat_activity to find the port that the client is
connecting from, then on the client machine, use lsof to hunt down the
process that is connecting via that port
On Fri, Jul 10, 2009 at 4:40 PM, Scot Kreienkamp wrote:
> Thanks scott, but I wrote a cgi to combine all of the process info and allow
> me to kill errant queries. So I know how to track down the pid. Thanks for
> trying to help though. :-)
So, what are you looking for, a stack trace dump from jav
On Fri, July 10, 2009 18:13, Alvaro Herrera wrote:
>
> Is it using a different PG connection than the one doing the
> insert? In that case, it won't see the new row until the
> inserting transaction commits.
That is almost certainly the exact problem. I will check and
determine if this is so b
On Fri, Jul 10, 2009 at 4:53 PM, James B. Byrne wrote:
>
> On Fri, July 10, 2009 18:48, Scott Marlowe wrote:
>> On Fri, Jul 10, 2009 at 2:13 PM, James B.
>> Byrne wrote:
>>>
>>> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
>>> hll_theheart_db_admin : LOCATION: exec_simple_query,
On Fri, July 10, 2009 18:48, Scott Marlowe wrote:
> On Fri, Jul 10, 2009 at 2:13 PM, James B.
> Byrne wrote:
>>
>> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
>> hll_theheart_db_admin : LOCATION: exec_simple_query,
>> postgres.c:1105
>> 2009-07-10 15:59:17 EDT hll_theheart_test
Greg Stark wrote:
It won't work even a little bit before 8.3. For 8.3 or later you could
maybe make it work using vacuum freeze but there's no facility to
verify that it's really frozen everything and you'll still be taken by
surprise by queries which try to use temporary space for large sorts
or
On Fri, Jul 10, 2009 at 2:13 PM, James B. Byrne wrote:
>
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOCATION: exec_simple_query,
> postgres.c:1105
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOG: 0:
On Fri, Jul 10, 2009 at 11:17 PM, Janet Jacobsen wrote:
> Is it possible to create a database cluster on a machine that
> has write access to the shared file system, shut down the
> Postgres server on that machine, and then start up the
> Postgres server on the machine that cannot write to the
> sh
Janet Jacobsen writes:
> Is it possible to create a database cluster on a machine that
> has write access to the shared file system, shut down the
> Postgres server on that machine, and then start up the
> Postgres server on the machine that cannot write to the
> shared file system, and thereafter
Thanks scott, but I wrote a cgi to combine all of the process info and allow me
to kill errant queries. So I know how to track down the pid. Thanks for trying
to help though. :-)
- Original Message -
From: Scott Marlowe
To: Scot Kreienkamp
Cc: pgsql-general@postgresql.org
Sent: Fri Jul
On Fri, Jul 10, 2009 at 2:05 PM, Scot Kreienkamp wrote:
> Hi everyone,
>
> I need some help with tracking down idle in transaction problems. We have a
> custom application that is leaving queries in idle in transaction status for
> unknown reasons. The developers are working on ways to track it d
Hi. We are looking into the possibility of running a Postgres
server on an underutilized machine. This machine has very
little local disk space, so we would have to create the data
directory on a shared file system.
The underutilized machine was set up so that it can *only
read* from the shared
James B. Byrne wrote:
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOG: 0: duration: 0.782 ms statement:
> SELECT * FROM "currencies"
>
> The client program that receives this result reports that there are
> no rows returned. So where did they g
Hiroshi Saito wrote:
> Is the state where you wish this?
>
> example
> C:\Program Files\PostgreSQL\8.4\bin>psql -p 5433 postgres postgres
> psql (8.4.0)
> "help" でヘルプを表示します.
>
> C:\Program Files\PostgreSQL\8.4\bin>set LANG=C
>
> C:\Program Files\PostgreSQL\8.4\bin>psql -p 5433 postgres
Scot Kreienkamp wrote:
It is Java. I asked our programmers to check on the JDBC version as I
had seen that on the list previously. It is using postgresql-8.2-504.
Is that one of the problem versions? I had thought it was new enough
that it would not be subject to that problem.
well, the cu
On Fri, Jul 10, 2009 at 2:47 PM, Willy-Bas Loos wrote:
> Hi,
>
> Thanks for your answers!
> I'm using 8.1 and 8.2 on windows2003 servers, and it's true that i could
> probably configure them much better.
Note that support for 8.1 on windows is gone, as it is no longer
considered supportable due to
Peter Hunsberger wrote:
We're looking at potentially using Postgres to store a variety of
molecular and genetic data. At this point I have a bunch of general
questions...
I don't know enough about your area of expertise to know if this is
useful, but I'd look at the Unison project to see wha
Hi,
Thanks for your answers!
I'm using 8.1 and 8.2 on windows2003 servers, and it's true that i could
probably configure them much better.
We've recently moved to brand new dedicated database servers with pg8.3 on
debian in 2 projects and it has been much easier to configure these
correctly. There
On Fri, July 10, 2009 16:20, Bill Moran wrote:
>
>
> Also, look for a BEGIN statement that is never COMMITed. If
> the client starts a transaction, INSERTs a bunch of stuff, then
> disconnects without issuing a COMMIT, Postgres will rollback
> the transaction, thus it will be as if the data was n
Hi John,
It is Java. I asked our programmers to check on the JDBC version as I
had seen that on the list previously. It is using postgresql-8.2-504.
Is that one of the problem versions? I had thought it was new enough
that it would not be subject to that problem.
The unexplained part is why are
On Fri, July 10, 2009 16:10, hubert depesz lubaczewski wrote:
>
> truncate. but first simple question - did you commit the inserts?
>
But if it were done with truncate then I would see truncate in the
log file, yes?
Second, I am working with PG through an ORM called ActiveRecord,
part of the R
Scot Kreienkamp wrote:
Hi everyone,
I need some help with tracking down idle in transaction problems. We
have a custom application that is leaving queries in idle in
transaction status for unknown reasons. The developers are working on
ways to track it down, but right now the options on thei
In response to "James B. Byrne" :
>
> This is a portion of the log for the most recent run that exhibits
> the problem:
>
> ...
> 2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
> hll_theheart_db_admin : LOG: 0: duration: 0.446 ms statement:
> INSERT INTO "currencies" ("is_in
I am sorry for this but I do not know how else to communicate what
is apparently happening:
This is a portion of the log for the most recent run that exhibits
the problem:
...
2009-07-10 15:59:17 EDT hll_theheart_test 216.185.71.24(49133)
hll_theheart_db_admin : LOG: 0: duration: 0.446 ms s
We're looking at potentially using Postgres to store a variety of molecular
and genetic data. At this point I have a bunch of general questions which I
can take to other lists if someone can tell me where
they would be most appropriate:
1) are there groups or individuals already doing this that ha
On Fri, Jul 10, 2009 at 03:45:35PM -0400, James B. Byrne wrote:
> I believe that this is what I want to examine. Is there a server
> side technique that I can use which will tell me what data this
> statement returned or if it found nothing?
not really, sorry.
> In any case, I see the INSERTS an
Hi everyone,
I need some help with tracking down idle in transaction problems. We
have a custom application that is leaving queries in idle in transaction
status for unknown reasons. The developers are working on ways to track
it down, but right now the options on their end are limited and it
On Fri, July 10, 2009 14:58, hubert depesz lubaczewski wrote:
> You can enable by database:
>
> alter database x set log_min_duration_statement = 0;
Many, many thanks. Now of course I need more help...
The situation is that data inserted into the DB is not being found
on a subsequent select an
> Hm, I'm not sure I believe any of that except the last bit, seeing that
> he's got plenty of excess CPU capability. But the last bit fits with
> the wimpy-I/O problem, and it also offers something we could test.
> Dan, please see what happens when you vary the wal_buffers setting.
> (Note you ne
On Fri, Jul 10, 2009 at 01:38:57PM -0400, James B. Byrne wrote:
> I have a situation with a Rails project where test data in
> mysteriously "disappearing" in the middle of a test run. I would
> like to see the exact SQL of all client requests issued against a
> single table during a fixed time spa
On Fri, 2009-07-10 at 18:47 +0100, Greg Stark wrote:
> > -- foo has a primary key
> Well no, it's equivalent to SELECT DISTINCT * FROM foo;
I think you missed that "foo" has a primary key.
Regards,
Jeff Davis
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To mak
On Fri, Jul 10, 2009 at 11:47 AM, Greg Stark wrote:
> On Fri, Jul 10, 2009 at 6:37 PM, Jeff Davis wrote:
>>
>> -- foo has a primary key
>> SELECT * FROM foo UNION SELECT * FROM foo;
>>
>> That's logically equivalent to:
>>
>> SELECT * FROM foo;
>>
>> But postgresql will add a sort anyway.
>
>
> Wel
Post a snippet of the xml and xpath you are trying to use.
Scott
- Original Message -
From: "Roy Walter"
To: pgsql-general@postgresql.org
Sent: Friday, July 10, 2009 7:49:00 AM GMT -08:00 US/Canada Pacific
Subject: [GENERAL] XML import with DTD
Hi
I'm trying to use the XPath f
On Fri, Jul 10, 2009 at 6:37 PM, Jeff Davis wrote:
>
> -- foo has a primary key
> SELECT * FROM foo UNION SELECT * FROM foo;
>
> That's logically equivalent to:
>
> SELECT * FROM foo;
>
> But postgresql will add a sort anyway.
Well no, it's equivalent to SELECT DISTINCT * FROM foo;
--
greg
htt
I have a situation with a Rails project where test data in
mysteriously "disappearing" in the middle of a test run. I would
like to see the exact SQL of all client requests issued against a
single table during a fixed time span.
How can I best accomplish this in PostgreSQL?
#client_min_messages
On Jul 10, 2009, at 6:47 AM, Vanessa Lopez wrote:
I discovered the table that was causing the error, delete it and
create it again (I miss some data but at least everything else is
working now)
Yes, for the backup we copy everything we had under /data (the
directory containing "base", "glo
On Fri, 2009-07-10 at 14:22 +0100, Simon Riggs wrote:
> I mean it seems possible to prove that the distinct removal step is not
> necessary, by proving that the various sub-queries are already disjoint.
> It's a common manual optimization, so automating it seems a reasonable
> future goal.
There a
On Fri, 2009-07-10 at 01:36 +0100, Greg Stark wrote:
> Arguably the missing feature here is skip-scans where we scan the
> index but only pull out one record for each distinct value. I'm not
> sure there's anything particularly stopping Postgres from being able
> to do them, but it might be a lot o
t == t...@sss.pgh.pa.us writes:
t> Brandon Metcalf writes:
t> > I tried moving the last group of WHERE, GROUP BY, and ORDER BY before
t> > the UNION with the query it belongs to, but that results in a
t> > different syntax error.
t> I think that's probably what you want to do. What you're
M == matthew.hart...@krcc.on.ca writes:
M> > > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
M> > > ow...@postgresql.org] On Behalf Of Brandon Metcalf
M> > > Sent: Friday, July 10, 2009 12:16 PM
M> >
M> > Change it to this:
M> Sorry, I forgot that you need to split the GRO
Brandon Metcalf writes:
> I tried moving the last group of WHERE, GROUP BY, and ORDER BY before
> the UNION with the query it belongs to, but that results in a
> different syntax error.
I think that's probably what you want to do. What you're missing is
you need parentheses to put an ORDER BY in
> > From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> > ow...@postgresql.org] On Behalf Of Brandon Metcalf
> > Sent: Friday, July 10, 2009 12:16 PM
>
> Change it to this:
Sorry, I forgot that you need to split the GROUP BY clause as well in a
similar manner to the WHERE clause. An
> -Original Message-
> From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-
> ow...@postgresql.org] On Behalf Of Brandon Metcalf
> Sent: Friday, July 10, 2009 12:16 PM
Change it to this:
> SELECT t.name AS machine_type_name,
> j.workorder,
>
Is the following even possible? I keep getting a syntax error at the
last WHERE:
ERROR: syntax error at or near "WHERE"
LINE 20: WHERE p.part_id=379 AND t.machine_type_id=1
The SQL is
SELECT t.name AS machine_type_name,
j.workorder,
round(sum(EXTRACT
Simon Riggs writes:
> I think its a traffic jam.
> After checkpoint in XLogInsert(), we discover that we now have to backup
> a block that we didn't think so previously. So we have to drop the lock
> and then re-access WALInsertLock. So every backend has to go through the
> queue twice the first
On Fri, 2009-07-10 at 10:27 -0400, Tom Lane wrote:
> Simon Riggs writes:
> > ISTM more likely to be a problem with checkpointing clog or subtrans.
> > That would block everybody and the scale of the problem is about right.
>
> That's what I had been thinking too, but the log_checkpoint output
>
On Friday 10 July 2009, Vanessa Lopez wrote:
> What do you mean by we can't simply take a filesystem copy of a
> running database? :-O ... How should we then do the backups (so next
> time I will not have the same problem again) ?
There is extensive documentation on how to do backups. For filesys
Vanessa Lopez writes:
> What do you mean by we can't simply take a filesystem copy of a
> running database? :-O ... How should we then do the backups (so next
> time I will not have the same problem again) ?
Read the fine manual ...
http://www.postgresql.org/docs/8.3/static/backup.html
Sectio
Marek Lewczuk writes:
> I have made an upgrade to PG 8.4 and following error was thrown during
> execution of some pl/pgsql function:
> ERROR: XX000: SPI_connect failed: SPI_ERROR_CONNECT
Really? Could we see a self-contained example?
regards, tom lane
--
Sent via pgs
Hi
I'm trying to use the XPath functionality of Postgres.
I can populate a text field (unparsed) with XML data but as far as I can
see the xpath() function [now] only works on the xml data type.
When I try to populate a text field with XML data containing a DTD,
however, the parser chokes. I
Hello,
I have made an upgrade to PG 8.4 and following error was thrown during
execution of some pl/pgsql function:
ERROR: XX000: SPI_connect failed: SPI_ERROR_CONNECT
CONTEXT: PL/pgSQL function "price_aftertrigger" line 30 at IF
SQL statement "update price set validFrom = $1 , validTo =
Hello,
Thanks for all your answers!
I discovered the table that was causing the error, delete it and
create it again (I miss some data but at least everything else is
working now)
Yes, for the backup we copy everything we had under /data (the
directory containing "base", "global", and so
On Fri, Jul 10, 2009 at 8:43 AM, John R Pierce wrote:
> nabble.30.miller_2...@spamgourmet.com wrote:
>>
>> The database server is a quad core machine, so it sounds as though
>> software RAID should work fine for the present setup. However, it
>> sounds as though I should put some money into a hardw
In response to nabble.30.miller_2...@spamgourmet.com:
> > On Thu, Jul 9, 2009 at 7:29 PM, Greg Stark wrote:
> >> On Fri, Jul 10, 2009 at 1:28 AM, Scott Marlowe
> >> wrote:
> >>>
> >>> $750 is about what a decent RAID controller would cost you, but again
> >>> it's likely that given your bulk impo
nabble.30.miller_2...@spamgourmet.com wrote:
The database server is a quad core machine, so it sounds as though
software RAID should work fine for the present setup. However, it
sounds as though I should put some money into a hardware RAID
controller if the database becomes more active. I had ass
> On Thu, Jul 9, 2009 at 7:29 PM, Greg Stark wrote:
>> On Fri, Jul 10, 2009 at 1:28 AM, Scott Marlowe
>> wrote:
>>>
>>> $750 is about what a decent RAID controller would cost you, but again
>>> it's likely that given your bulk import scenario, you're probably ok
>>> without one. In this instance
Simon Riggs writes:
> ISTM more likely to be a problem with checkpointing clog or subtrans.
> That would block everybody and the scale of the problem is about right.
That's what I had been thinking too, but the log_checkpoint output
conclusively disproves it: those steps are taking less than 20ms
Hi Stuart-san.
Is the state where you wish this?
example
C:\Program Files\PostgreSQL\8.4\bin>psql -p 5433 postgres postgres
psql (8.4.0)
"help" でヘルプを表示します.
C:\Program Files\PostgreSQL\8.4\bin>set LANG=C
C:\Program Files\PostgreSQL\8.4\bin>psql -p 5433 postgres postgres
psql (8.4.0)
T
On Fri, 2009-07-10 at 09:46 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> > or a query like this
> >
> > Select '1', ...
> > ...
> > union
> > Select status, ...
> > ...
> > where status != '1';
> > ;
> >
> > then it is clear that we could automatically prove that the the distinct
>
Simon Riggs wrote:
> or a query like this
>
> Select '1', ...
> ...
> union
> Select status, ...
> ...
> where status != '1';
> ;
>
> then it is clear that we could automatically prove that the the distinct
> step is redundant and so we could either hash or sort. This is the same
> as repl
On Fri, 2009-07-10 at 09:28 -0400, Bruce Momjian wrote:
> Simon Riggs wrote:
> >
> > On Fri, 2009-07-10 at 08:59 -0400, Bruce Momjian wrote:
> >
> > > > I think it should be possible to use predtest theorem proving to
> > > discard
> > > > the sort/hash step in cases where we can prove the sets
Simon Riggs wrote:
>
> On Fri, 2009-07-10 at 08:59 -0400, Bruce Momjian wrote:
>
> > > I think it should be possible to use predtest theorem proving to
> > discard
> > > the sort/hash step in cases where we can prove the sets are
> > disjoint.
> > > Often there are top-level quals that can be com
On Fri, 2009-07-10 at 08:59 -0400, Bruce Momjian wrote:
> > I think it should be possible to use predtest theorem proving to
> discard
> > the sort/hash step in cases where we can prove the sets are
> disjoint.
> > Often there are top-level quals that can be compared in the WHERE
> > clauses of t
On 2009-07-08, Massa, Harald Armin wrote:
> a quite interesting read.
>
> http://www.codingthewheel.com/archives/stranger-than-fiction-story-online-poker-tracker-postgresql
>
>
> especially as an explanation of the growing number of questions from
> Windows-Users of PostgreSQL
>
> And ... for a ta
Hi,
I will be developing a WorkFlow Application, but I don't know the best
practices on how to design a WorkFlow on a Database.
Can you give me some clues? Books, links on the Internet, etc...
Best Regards,
André.
Simon Riggs wrote:
>
> On Thu, 2009-07-09 at 20:41 -0600, Scott Marlowe wrote:
> > On Thu, Jul 9, 2009 at 7:58 PM, Bruce Momjian wrote:
> > > Scott Bailey wrote:
> > >> Alvaro Herrera wrote:
> > >> > Tim Keitt wrote:
> > >> >> I am combining query results that I know are disjoint. I'm wondering
>
On Fri, Jul 10, 2009 at 1:41 PM, Ben Harper wrote:
>
> Unfortunately I can't use GROUP BY, because what I'm really doing is
> SELECT DISTINCT ON(unique_field) id FROM table;
You could do that using GROUP BY if you define a first() aggregate. In
this case that would just be SELECT first(id) AS id f
Thanks for all the feedback.
Using GROUP BY is indeed much faster (about 1 second).
Unfortunately I can't use GROUP BY, because what I'm really doing is
SELECT DISTINCT ON(unique_field) id FROM table;
I'm not familiar with the Postgres internals, but in my own DB system
that I have written, I do
On Fri, Jul 10, 2009 at 9:22 PM, Stuart Bishop wrote:
> On Fri, Jul 10, 2009 at 8:28 AM, Tim Uckun wrote:
>> I don't see any ubuntu packages for 8.4 in the default repositories.
>>
>> Does anybody know if they will be upgrading the postgresql package to
>> 8.4 or creating a new package for it.
>
>
On Wed, 2009-07-08 at 18:22 -0400, Tom Lane wrote:
> As Greg commented upthread, we seem to be getting forced to the
> conclusion that the initial buffer scan in BufferSync() is somehow
> causing this. There are a couple of things it'd be useful to try
> here:
Not sure why you're forced to that
On Fri, Jul 10, 2009 at 8:28 AM, Tim Uckun wrote:
> I don't see any ubuntu packages for 8.4 in the default repositories.
>
> Does anybody know if they will be upgrading the postgresql package to
> 8.4 or creating a new package for it.
The postgresql-8.4 packages arrived in 9.10 (Karmic) about two
On Thu, 2009-07-09 at 20:41 -0600, Scott Marlowe wrote:
> On Thu, Jul 9, 2009 at 7:58 PM, Bruce Momjian wrote:
> > Scott Bailey wrote:
> >> Alvaro Herrera wrote:
> >> > Tim Keitt wrote:
> >> >> I am combining query results that I know are disjoint. I'm wondering
> >> >> how much overhead there is
> > testinsert contains t values between '2009-08-01' and '2009-08-09', and
> > ne_id
> from 1 to 2. But only 800 out of 2 ne_id have to be read; there's no
> need for a table scan!
> > I guess this is a reflection of the poor "correlation" on ne_id; but, as I
> said, I don't really t
79 matches
Mail list logo
