I've got a 7.4.6 db running on linux where we've been vacuuming "full"
the database each night, and have recently switched to "vacuum analyse"
instead.
We keep seeing this message at the end of the vacuum run:
WARNING: some databases have not been vacuumed in 2013308218 transactions
HINT: Bett
le 10/02/05 4:22, Jonel Rienton à [EMAIL PROTECTED] a écrit :
> -BEGIN PGP SIGNED MESSAGE-
> Hash: SHA1
>
> seems like it still exists in 10.3.8 which was just released today
>
> - -
> Jonel Rienton
> http://blogs.road14.com
> Software Developer, *nix Advocate
> On Feb 9, 2005, at 3:
I've been reading some posts on this list, trying to figure out how to
send a function an array of a user-defined type. I have figured out a
way to do this. To some extent, this serves as a follow-up to
"User-defined type arrays?"
(http://archives.postgresql.org/pgsql-novice/2004-10/msg00132.
Tom Lane <[EMAIL PROTECTED]> writes:
> Martijn van Oosterhout writes:
> > I've been wondering about that. A while ago the change was made from
> > outputting a NOTICE with the EXPLAIN output to returning a resultset.
> > If you could agree on what columns to return it might not be so hard
> > fo
David Fetter <[EMAIL PROTECTED]> writes:
> On Thu, Feb 10, 2005 at 09:51:18PM -0500, Tom Lane wrote:
>> ... it loses the distinction between utility statements that
>> can return tuples and those that can't.
> Where is this distinction in SPI?
Well, the point is that there are potentially three t
Certainly you can change this.
Edit the following file in your source tree:
[EMAIL PROTECTED]:/brownes/wolfe/compiles/pgsql> vi
src/include/pg_config_manual.h
/*
* Maximum number of columns in an index and maximum number of
* arguments to a function. They must be the same value.
*
* The min
Michael Fuhr <[EMAIL PROTECTED]> writes:
> It sounds like the best a check could do would be the amazingly
> astute "some transaction held a lock on this row at one time and
> may or may not still hold that lock, and even if it did when you
> checked it might have gone away by now and some other tr
Just a small warning for those people using python with postgresql:
pysgresql and psycopg are very different animals. You cannot drop in
one as a replacement for the other, even though both 'claim' to be DB
API 2.0 compliant. If you are starting out with python on postgresql,
I would strongly rec
David Fetter <[EMAIL PROTECTED]> writes:
> On Thu, Feb 10, 2005 at 08:11:42PM -0500, Tom Lane wrote:
>> Seems we have three possibilities to fix this:
>>
>> 1. Alter SPI_execute to say SPI_OK_SELECT after executing a utility
>> statement that returns tuples.
> This doesn't sound good.
It does se
On Thu, Feb 10, 2005 at 10:40:44AM -0500, Tom Lane wrote:
>
> Remember RC mode takes a new snapshot for each query. You'd need to
> use serializable mode --- and do something to actually freeze the
> transaction snapshot, which BEGIN does not --- to see the issue in a
> manual test.
It sounds li
On Thu, Feb 10, 2005 at 08:11:42PM -0500, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > Do PL/Perl and the other PLs require any changes to make this work?
> > I tried $rv = spi_exec_query("EXPLAIN $query") but $rv contained
> > only the following:
>
> > $rv->{processed} = 0
> > $
Michael Fuhr <[EMAIL PROTECTED]> writes:
> Do PL/Perl and the other PLs require any changes to make this work?
> I tried $rv = spi_exec_query("EXPLAIN $query") but $rv contained
> only the following:
> $rv->{processed} = 0
> $rv->{status} = SPI_OK_UTILITY
Looking around, it seems that the PLs (an
On Thursday February 10 2005 5:01, David Fetter wrote:
> On Thu, Feb 10, 2005 at 05:19:41PM -0500, Tom Lane wrote:
> > David Fetter <[EMAIL PROTECTED]> writes:
> > > I'd be delighted to, but I'm not sure how to see to it
> > > that EXPLAIN gets the CMD_SELECT flag. What all files
> > > need to cha
Martijn van Oosterhout writes:
> I've been wondering about that. A while ago the change was made from
> outputting a NOTICE with the EXPLAIN output to returning a resultset.
> If you could agree on what columns to return it might not be so hard
> for the EXPLAIN to return full tuples...
The major
"Stapleton, Mike" <[EMAIL PROTECTED]> writes:
> ... after I run ./configure (and out
> put looks OK), I run gmake and it goes into some recursive loop that never
> ends.
If you would have shown us some output, it might be easier to determine
what's going on. But I'm going to take a wild guess and
Hi to whoever is getting these. I sent this out the other day to the admin,
general, and novice group since I wasn't getting any response and was just
curious if anyone in postgresql.org had read it. I'm not sure which group to
send it to cause it's such and odd problem. Also did several google
On Thu, Feb 10, 2005 at 05:19:41PM -0500, Tom Lane wrote:
> David Fetter <[EMAIL PROTECTED]> writes:
> > I'd be delighted to, but I'm not sure how to see to it that EXPLAIN
> > gets the CMD_SELECT flag. What all files need to change to effect
> > this? Or have I drastically misunderstood what's i
On Fri, Feb 11, 2005 at 12:06:45AM +0100, Martijn van Oosterhout wrote:
> On Thu, Feb 10, 2005 at 11:38:47PM +0100, Harald Fuchs wrote:
> > But I think it should be the other way round: EXPLAIN ANALYZE should
> > return its output in table form, and there should be a function to
> > convert that t
On Thu, Feb 10, 2005 at 11:38:47PM +0100, Harald Fuchs wrote:
> But I think it should be the other way round: EXPLAIN ANALYZE should
> return its output in table form, and there should be a function to
> convert that table into what EXPLAIN ANALYZE outputs now.
I've been wondering about that. A w
On Thu, Feb 10, 2005 at 03:48:48PM -0700, Michael Fuhr wrote:
>
> Below is a simple proof of concept for an explain() function
> in PL/pgSQL.
Do PL/Perl and the other PLs require any changes to make this work?
I tried $rv = spi_exec_query("EXPLAIN $query") but $rv contained
only the following:
$
On Thu, Feb 10, 2005 at 03:38:05PM -0500, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
>
> > Any suggestions for the meantime?
>
> Update to CVS tip ;-)
Done. Below is a simple proof of concept for an explain() function
in PL/pgSQL. It's not necessarily correct -- it just shows w
On Fri, Feb 11, 2005 at 09:28:04AM +1100, Jamie Deppeler wrote:
>
> have a pretty simple question i know its possible to catch a delete
> statement through a trigger is it possible to prevent the delete from
> happing through the same trigger function?
The trigger documentation describes how a
In article <[EMAIL PROTECTED]>,
Tom Lane <[EMAIL PROTECTED]> writes:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
>> On Wed, Feb 09, 2005 at 11:36:22PM -0500, Tom Lane wrote:
>>> (And no, EXECUTE doesn't help.) This seems like an oversight. We
>>> already have some understanding in the backend tha
David Fetter <[EMAIL PROTECTED]> writes:
> I'd be delighted to, but I'm not sure how to see to it that EXPLAIN
> gets the CMD_SELECT flag. What all files need to change to effect
> this? Or have I drastically misunderstood what's involved?
It doesn't. See
http://archives.postgresql.org/pgsql-c
Hi
have a pretty simple question i know its possible to catch a delete
statement through a trigger is it possible to prevent the delete from
happing through the same trigger function?
---(end of broadcast)---
TIP 8: explain analyze is your friend
On Thu, Feb 10, 2005 at 10:07:50AM -0800, David Fetter wrote:
>
> Short of recompiling as others have suggested, you could put large #
> of params into an array and have the function unwind that.
In 8.0 or later, you could also use a function that takes a single
parameter of a composite type and
You've got two columns that the system wants to call "obj_id" here.
Try something like
SELECT s.obj_id AS s_obj_id, ...
That would have been elegant, but returns a 'syntax error at or near
"s_obj_id"'. Hm...
Was just some minor syntax error. Works now - this is exactly what I wanted.
Thanks!
re
On Thu, Feb 10, 2005 at 01:29:42PM -0600, James Thompson wrote:
> Though if IIRC it does round timestamps which can burn a person at times :)
> I don't have a test case to verify if my memory is correct about this though.
Actually, I think it's just that mxDateTime defaults to displaying
times
I'm interested in getting some benchmark numbers from postgres in
situations that generate errors. Specifically, I'm trying to analyze
high-volume attempts to insert into tables such that unique constraints
(primary keys) are violated by duplicate keys.
EXPLAIN ANALYZE INSERT INTO ...
doesn't w
On Thu, Feb 10, 2005 at 03:38:05PM -0500, Tom Lane wrote:
> Michael Fuhr <[EMAIL PROTECTED]> writes:
> > On Wed, Feb 09, 2005 at 11:36:22PM -0500, Tom Lane wrote:
> >> (And no, EXECUTE doesn't help.) This seems like an oversight.
> >> We already have some understanding in the backend that certain
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Wed, Feb 09, 2005 at 11:36:22PM -0500, Tom Lane wrote:
>> (And no, EXECUTE doesn't help.) This seems like an oversight. We
>> already have some understanding in the backend that certain utility
>> commands return query results; the SPI code should be
On Thursday 10 February 2005 10:19 am, you wrote:
> Alex Turner wrote:
> >Can anyone recommend a python interface other than pygresql for
> >postgresql. Yet again they have changed the API (pg.error is now
> >pg.Error), and I can get no information from the mailing list, which
> >seems dead.
>
> P
You've got two columns that the system wants to call "obj_id" here.
Try something like
SELECT s.obj_id AS s_obj_id, ...
That would have been elegant, but returns a 'syntax error at or near
"s_obj_id"'. Hm...
Thanks,
Christoph
---(end of broadcast)---
BTW: that's not a postgres problem, ANY relational database will
give the same error here.
I didn't suspect it to be a postgres problem. I only didn't have the
opportunity to make this mistake working with MySQL. :-)
best,
Christoph
---(end of broadcast)---
On Wed, Feb 09, 2005 at 10:11:19PM -0500, Oisin Glynn wrote:
> Hi,
>
> I am trying to keep database access encapsulated in functions from an
> insert and update point of view so as all of the applications writing to
> the database are using the same functions/statements.
>
> My problem is I was
Christoph Pingel wrote:
Could you give an actual example?
Sure. The idea is that 'objects' (persons, books, places) from a table
obj are linked with each other in a link table ool where objects from
obj can appear in an 'subject' or a 'object' column. Since the relation
can be any, this is a ve
On Thu, 2005-02-10 at 18:20 +0100, Christoph Pingel wrote:
> >Could you give an actual example?
>
> Sure. The idea is that 'objects' (persons, books, places) from a
> table obj are linked with each other in a link table ool where
> objects from obj can appear in an 'subject' or a 'object' column
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
You missed something obvious. Executing this query in psql you have no
problem, because there is no structure created from the result. A view
behaves like a table - try creating a table like
create table blah (
id int,
id int
);
and you'll hit the
On Thu, Feb 10, 2005 at 05:56:33AM -0500, Jan wrote:
>
> I write a program that mines data from a small few websites. I revisit
> those websites on a daily basis. I find a matching key (actually two fields
> comprise my unique key) and with the data collected on this visit I attempt
> to UPDATE
Could you give an actual example?
Sure. The idea is that 'objects' (persons, books, places) from a
table obj are linked with each other in a link table ool where
objects from obj can appear in an 'subject' or a 'object' column.
Since the relation can be any, this is a very flexible and lean
des
On Thu, Feb 10, 2005 at 05:13:51PM +0100, Christoph Pingel wrote:
>
> is there any restriction re the use of self joins in views? I have a
> moderately complicated query where I scan the same table twice using
> aliases, and I would like to put that into a view to keep my python
> code clean. H
Christoph Pingel wrote:
Hello,
is there any restriction re the use of self joins in views? I have a
moderately complicated query where I scan the same table twice using
aliases, and I would like to put that into a view to keep my python code
clean. However, I get an error 'column xy duplicated'.
On Thu, Feb 10, 2005 at 05:13:51PM +0100, Christoph Pingel wrote:
> Hello,
>
> is there any restriction re the use of self joins in views? I have a
> moderately complicated query where I scan the same table twice using
> aliases, and I would like to put that into a view to keep my python
> code
Alex Turner wrote:
Can anyone recommend a python interface other than pygresql for
postgresql. Yet again they have changed the API (pg.error is now
pg.Error), and I can get no information from the mailing list, which
seems dead.
Pyscopg is very popular. We use it hear at Command Prompt
with gre
Hello,
is there any restriction re the use of self joins in views? I have a
moderately complicated query where I scan the same table twice using
aliases, and I would like to put that into a view to keep my python
code clean. However, I get an error 'column xy duplicated'. Is this
so by design?
On Thu, Feb 10, 2005 at 10:58:05AM -0500, Alex Turner wrote:
> Can anyone recommend a python interface other than pygresql for
> postgresql. Yet again they have changed the API (pg.error is now
> pg.Error), and I can get no information from the mailing list, which
> seems dead.
psycopg is popula
Can anyone recommend a python interface other than pygresql for
postgresql. Yet again they have changed the API (pg.error is now
pg.Error), and I can get no information from the mailing list, which
seems dead.
Thanks,
Alex Turner
netEconomist
---(end of broadcast)---
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Thu, Feb 10, 2005 at 09:53:49AM -0500, Tom Lane wrote:
>> xactC: starts, sets snapshot
> Hmmm...that's not what I'm seeing in 8.0.1, at least not when
> xactC is READ COMMITTED:
Remember RC mode takes a new snapshot for each query. You'd need to
use
On Thu, Feb 10, 2005 at 08:04:52AM -0700, Ed L. wrote:
> On Wednesday February 9 2005 10:10, Michael Fuhr wrote:
> >
> > Any suggestions for the meantime?
>
> Well, this would be a bit unsightly and not exactly elegant, but
> I'd think you could do this via DBI from within plperl or maybe
> plpe
On Thu, Feb 10, 2005 at 09:53:49AM -0500, Tom Lane wrote:
>
> Right. Furthermore, xactC's query result could have been stale when it
> was obtained, nevermind the separate query to pg_locks:
>
> xactA: updates row
> xactC: starts, sets snapshot
> xactB: attempts to update same row, blocks until
Michael Fuhr <[EMAIL PROTECTED]> writes:
> On Thu, Feb 10, 2005 at 12:16:53AM -0700, Michael Fuhr wrote:
>> SELECT textin(bit_out(b)) FROM foo;
> Is it generally true that you can convert between types using the
> above method, assuming compatible syntax?
IIRC, this has been reasonably safe since
On Wednesday February 9 2005 10:10, Michael Fuhr wrote:
>
> > (And no, EXECUTE doesn't help.) This seems like an
> > oversight. We already have some understanding in the
> > backend that certain utility commands return query results;
> > the SPI code should be letting those results be scanned as
Michael Fuhr <[EMAIL PROTECTED]> writes:
> xactA: updates row
> xactB: attempts to update same row, blocks until xactA completes
> xactC: query finds xactA in row's xmax
> xactA: commits
> xactB: unblocks and acquires a lock on the row
> xactC: query to pg_locks doesn't find xactA, so assumes row n
Centuries ago, Nostradamus foresaw when [EMAIL PROTECTED] ("Oisin Glynn") would
write:
> I am trying to keep database access encapsulated in functions from an
> insert and update point of view so as all of the applications writing
> to the database are using the same functions/statements.
>
> My p
Surabhi Ahuja wrote:
I have a query regarding, the postgres Sever failure, for eg say i
am parsng a file and inserting rows into a table. Now while this
opeartion is taking place the server crashes, or say the power goes
off, or say someone kills the postmaster demon.
Any rows you have committed (
Surabhi Ahuja wrote:
I have a query regarding, the postgres Sever failure, for eg say i am
parsng a file and inserting rows into a table. Now while this
opeartion is taking place the server crashes, or say the power goes
off, or say someone kills the postmaster demon.
what happens to the rows
I have a query regarding, the postgres Sever failure,
for eg say i am parsng a file and inserting rows into a table. Now
while this opeartion is taking place the server crashes, or say the power goes
off, or say someone kills the postmaster demon.
what happens to the rows that have been inser
Hi Michael,
at the first glance it seems to be perfect. In addition it works even for
arrays. Wow! :)
Thank you very much,
-- Csaba
-Original Message-
From: Michael Fuhr [mailto:[EMAIL PROTECTED]
Sent: Thursday, February 10, 2005 8:32 AM
To: Együd Csaba
Cc: pgsql-general@postgresql.org
58 matches
Mail list logo
