On Mon, Nov 10, 2008 at 12:35 PM, Andrus <[EMAIL PROTECTED]> wrote:
> explain analyze select count(*)::INTEGER as cnt
> from dok
>WHERE dokumnr IN
> (869906,869907,869910,869911,869914,869915,869916,869917,869918,869921 )
> and
> dokumnr NOT IN (SELECT dokumnr FROM bilkaib WHERE
> alusd
Isak,
Looks to me like most of the time is spent doing "not in (select a ton
of rows from bilkaib)".
Try something like "not exists (select null from bilkaib b where
b.dokumnr = dok.dokumnr and alusdok = 'LY')".
Thank you very much.
Query seems to return now immediately.
dok.dokumnr is not n
I'm running:
ON_ERROR_STOP="on" PGPASSFILE="/somewhere" psql dbname username -f
script.sql
(or alternatively http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
IN queries have large number of int values. Sample below is query which has
only 10 values in IN list but takes more than one minute.
In real query IN list may contain up to 5000 integers.
There are indexes in both dok.dokumnr and bilkaib.dokumnr columns so it
should run fast.
How to speed up
On Mon, 10 Nov 2008 10:56:57 +
Richard Huxton <[EMAIL PROTECTED]> wrote:
> Ivan Sergio Borgonovo wrote:
> > I'm running:
> >
> > ON_ERROR_STOP="on" PGPASSFILE="/somewhere" psql dbname username
> > -f script.sql
> > (or alternatively >
> > echo $?
> > always returns 0 even when sql is clearl
Hi list,
we have a PostgreSQL 8.2 database on an xfs filesystem. By mistake the
first blocks of this filesystem were overwritten. An xfs_repair
reconstructed the superblock and also found many orphaned files and
directories. Actually, all we have on the filesystem now is in
"lost+found". ;-)
W
(Sorry for the cross posting)
Command Prompt is sponsoring "multiple PostgreSQL version installation
with RPMs" project, which has been in my todo list for a really long
time. The aim is to be able to install different PostgreSQL versions
with RPMs, like Debian folks have been doing for a long ti
Hello all,
I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I
also use Slony 1.2.14 for replication.
Is there a safe path on how to accomplish this, please advice on what
steps I will need to consider. Bear in mind that I am planning to skip
from Postgres 8.1.x to 8.3.x
Hello all,
I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I
also use Slony 1.2.14 for replication.
Is there a safe path on how to accomplish this, please advice on what
steps I will need to consider. Bear in mind that I am planning to skip
from Postgres 8.1.x to 8.3.x
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 table1 WHERE field1 '111';
When is LIKE no records matches query, when is =
my record matches query.
Hello Sam,
Thanks for your Python script! It's on my todo list to learn this
scripting language. I've done a mini script in Perl for my testing
purposes and then test full text search!
Here is my table definition:
test1=# \d pdfsys.document;
Table "pdfsys.document"
Column
Hello Thomas,
nice tool!
Thanks for your help... For batch and automation purposes, I've done a
small script... (check out my last post in the thread few minutes ago)
Bruno Lavoie
Thomas Kellerer a écrit :
Bruno Lavoie, 07.11.2008 19:20:
Hello,
The intent is to use pdftotext and store th
no quiero recibir mas correos de estos temas en donde no estoy incluida,
gracias.
Bruno Lavoie escribió:
Hello Thomas,
nice tool!
Thanks for your help... For batch and automation purposes, I've done a
small script... (check out my last post in the thread few minutes ago)
Bruno Lavoie
Th
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 table1 WHERE field1 '111';
>
> When is LIKE no records match
Richard Huxton <[EMAIL PROTECTED]> writes:
> Dmitry Teslenko wrote:
>> When is LIKE no records matches query, when is =
>> my record matches query. Why? And Does this behavior varies from
>> PostgreSQL 7.4 to 8.1?
> You're comparing a 3-character value '111' of type text to a
> 10-character one
On Mon, Nov 10, 2008 at 09:14:21AM +0100, Andreas Kraftl wrote:
> Am Sat, 08 Nov 2008 09:44:17 +0100 schrieb Andreas Kraftl:
> > How can I create a full text index over b?
>
> thanks for the answers. But nothing matches my problem.
I'm not sure what's wrong with Oleg's suggestion--he's the guy wh
On Wed, 2008-11-05 at 15:08 -0600, Tony Fernandez wrote:
> Hello all,
>
>
>
> I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I
> also use Slony 1.2.14 for replication.
>
>
>
> Is there a safe path on how to accomplish this, please advice on what
> steps I will need to c
It would be far simpler to fix your query generator to not emit the
useless "0 or".
I'm using ODBC and npgsql drivers. Those drivers replace parameters
automatically.
E.q. for npgsql or every other ADO .NET I can write
"SELECT * FROM (:param1 OR (x IN SELECT y FROM z) AND :param2) ...
etc.
DONE
thank you very much.
Best Regards,
Alam Surya
- Original Message -
From: "Sam Mason" <[EMAIL PROTECTED]>
To:
Sent: Monday, November 10, 2008 18:50
Subject: Re: [GENERAL] Get interval in months
On Mon, Nov 10, 2008 at 12:06:04PM +0700, dbalinglung wrote:
Dear Expe
Am Sat, 08 Nov 2008 09:44:17 +0100 schrieb Andreas Kraftl:
> How can I create a full text index over b?
Hello
thanks for the answers. But nothing matches my problem.
I read the manual again and decide me for an other way.
I change my table that it looks like:
lang| text
Thanks Joao,
That is what I have done, but wanted to see if there was any other known
potential risks.
The fact about including an extra backup to go back if ever needed was
underestimated, so I will consider it but not in my live servers.
Regards,
Tony Fernandez
-Original Message-
From
Tony Fernandez wrote:
> Hello all,
>
>
>
> I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I
> also use Slony 1.2.14 for replication.
The other option is to use slony itself - it will replicate between
versions.
--
Richard Huxton
Archonet Ltd
--
Sent via pgsql-gener
Ivan Sergio Borgonovo wrote:
> I'm running:
>
> ON_ERROR_STOP="on" PGPASSFILE="/somewhere" psql dbname username -f
> script.sql
> (or alternatively
> echo $?
> always returns 0 even when sql is clearly wrong.
I don't think ON_ERROR_STOP is read from the environment, you need to
define it as a p
On Wed, Nov 5, 2008 at 2:19 PM, Tony Fernandez
<[EMAIL PROTECTED]> wrote:
> Hello all,
>
>
>
> I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4. I also
> use Slony 1.2.14 for replication.
Then you're set. One of the primary purposes of slony is upgrading in
place. Take one of y
Before waking up properly, I wrote:
> Querying is a bit awkward, but works:
>
> SELECT *
> FROM test
> WHERE tsvector_concat(
> to_tsvector('english', CASE lang WHEN 'english' THEN text ELSE '' END),
> to_tsvector('german', CASE lang WHEN 'german' THEN text ELSE '' END))
> @@
On Mon, Nov 10, 2008 at 12:06:04PM +0700, dbalinglung wrote:
> Dear Expert,
>
> I have a function to getting time interval bellow :
>
> create or replace function scmaster.pr_gettimeinterval(time without time
> zone, time without time zone, numeric(5,2)) returns char(10) As '
> declare v_timein
>
> Hi everyone,
>
> I have a problem with a standby server running on Windows 2003 R2,
> Enterprise x64 edition. I use Postgres 8.3 (installed to C:\Program Files
> (x86)\ )
> Everything was working fine (base backup, archiving, recovery), until I
> wanted to test failover.
> I created the trigger
On Fri, Nov 07, 2008 at 01:20:27PM -0500, Bruno Lavoie wrote:
> The intent is to use pdftotext and store the resulting text in datbase
> for full text search purposes... I'm trying to develop a mini content
> server where I'll put pdf documents to make it searchable.
I've not tried to do this so
Hello,
I write funktions in pl/pgsql and want to abort it. For this I use raise
exception, to undo all changes for this transaction. Now I want to log
these exceptions somewhere.
DECLARE
a integer;
log_id integer;
BEGIN
SELECT a INTO b FROM c WHERE d = 10;
IF NOT FOUND THEN
INSERT INT
On Sat, Nov 08, 2008 at 07:44:45AM -0500, David Spadea wrote:
> Gerhard,
>
> Check out:
>
> http://www.postgresql.org/docs/8.3/interactive/functions-datetime.html
>
> In particular, look at 'age()' or 'justify_days()', but I think age() is the
> one you want.
>
>
> SELECT age('2008-02-01'::ti
Try something like "not exists (select null from bilkaib b where
b.dokumnr = dok.dokumnr and alusdok = 'LY')".
I tried to optimize another similar query but it is still slow.
This query has same clause
dok.dokumnr IN
(869906,869907,869910,869911,869914,869915,869916,869917,869918 )
duplicat
Bruno Lavoie, 07.11.2008 19:20:
Hello,
The intent is to use pdftotext and store the resulting text in datbase
for full text search purposes... I'm trying to develop a mini content
server where I'll put pdf documents to make it searchable.
Generally, PDFs are in size of 500 to 3000 pages resu
Let's say you're using logging_collector and you've put some %-escapes
into log_filename for daily log rotation. Perhaps it's daily rotation
with this pattern:
log_filename = 'postgresql-%Y-%m-%d.log'
Is there any good way to ask the server what log file name it's currently
writing to? I wa
Greg Smith <[EMAIL PROTECTED]> writes:
> Is there any good way to ask the server what log file name it's currently
> writing to? I was trying to write something that does a "tail" on the
> current log, and was hoping there was a simple way to figure out which
> file that goes against. Looking
Greg Smith wrote:
Let's say you're using logging_collector and you've put some %-escapes
into log_filename for daily log rotation. Perhaps it's daily rotation
with this pattern:
log_filename = 'postgresql-%Y-%m-%d.log'
Is there any good way to ask the server what log file name it's
currentl
On Mon, 2008-11-10 at 13:46 -0500, Greg Smith wrote:
> Let's say you're using logging_collector and you've put some %-escapes
> into log_filename for daily log rotation. Perhaps it's daily rotation
> with this pattern:
>
> log_filename = 'postgresql-%Y-%m-%d.log'
>
> Is there any good way to a
On Mon, 2008-11-10 at 13:46 -0500, Greg Smith wrote:
> Let's say you're using logging_collector and you've put some %-escapes
> into log_filename for daily log rotation. Perhaps it's daily rotation
> with this pattern:
>
> log_filename = 'postgresql-%Y-%m-%d.log'
>
> Is there any good way to a
On Mon, Nov 10, 2008 at 01:46:14PM -0500, Greg Smith wrote:
> strftime would both work I guess, those just seemed a little heavy (was
> hoping for an "alias"-sized answer) to figure out something that the
> server certainly knows.
it's not nice, but it works:
alias pgtail='/bin/ls -1 /var/log/
Tom Lane wrote:
Jason Long <[EMAIL PROTECTED]> writes:
I got this error
/usr/sbin/sendmail: Permission denied
So I guess I need to allow the use of sendmail.
How is postgres running the command different from my doing it as the
postgres user or cron running as the postgres user?
$ cat dbscripts/logtail.py
#!/usr/bin/env python
"""
usage: logtail [-d pathname][-n]
-d pathname Use pathname instead of /var/lib/postgresql/8.2/main/pg_log
-nJust print the current log file name and exit
-lList the log file names
-p [files]Run the files thro
On Mon, 2008-11-10 at 20:12 +0100, hubert depesz lubaczewski wrote:
> On Mon, Nov 10, 2008 at 01:46:14PM -0500, Greg Smith wrote:
> > strftime would both work I guess, those just seemed a little heavy (was
> > hoping for an "alias"-sized answer) to figure out something that the
> > server certa
One of the simplest ways to do it is with plProxy call into same database in
exception block.
Get plproxy installed in your database and after that it is as simple as
function call.
test=# create table log ( msg text );
CREATE TABLE
test=# create function add_logg ( i_msg text ) returns void as $$
On Mon, Nov 10, 2008 at 12:44 PM, Joshua D. Drake <[EMAIL PROTECTED]> wrote:
> On Mon, 2008-11-10 at 20:12 +0100, hubert depesz lubaczewski wrote:
>> On Mon, Nov 10, 2008 at 01:46:14PM -0500, Greg Smith wrote:
>> > strftime would both work I guess, those just seemed a little heavy (was
>> > hoping
On Mon, Nov 10, 2008 at 11:44:31AM -0800, Joshua D. Drake wrote:
> Hmm what about just "ls -tu"
> Which if I am reading the man page correctly sorts by last access time.
which might not be what you need. the problem is that there is no
guarantee that the last "accessed" file is the current one.
o
On Mon, 2008-11-10 at 20:55 +0100, hubert depesz lubaczewski wrote:
> On Mon, Nov 10, 2008 at 11:44:31AM -0800, Joshua D. Drake wrote:
> > Hmm what about just "ls -tu"
> > Which if I am reading the man page correctly sorts by last access time.
>
> which might not be what you need. the problem is t
Greg Smith wrote:
> Let's say you're using logging_collector and you've put some %-escapes
> into log_filename for daily log rotation. Perhaps it's daily rotation
> with this pattern:
>
> log_filename = 'postgresql-%Y-%m-%d.log'
>
> Is there any good way to ask the server what log file name it
On Mon, 10 Nov 2008, Joshua D. Drake wrote:
O.k. so I think this whole thread screams TODO...
Me too; added to the TODO list on the wiki. I presumed there had to be a
simple way to handle this as the need for it seemed pretty obvious. All
of the sample scripts and shell tricks are apprecia
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. Seems an obvious candidate for an in-core function ...
... which will still fail in corner cases. Not t
On Mon, 2008-11-10 at 16:35 -0500, 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. Seems an obvious candidate for an in-core func
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. Seems an obvious candidate for an in-core function ...
...
Is there any interest in an optional mode for pg_dump to order the
output so that it's easier to use diff?
I don't think it would make the output 100% deterministic, but it would
make it easier to at least compare the data for small databases.
I think this has been brought up before, but I couldn
On Nov 10, 2008, at 10:03 AM, Scott Marlowe wrote:
On Wed, Nov 5, 2008 at 2:19 PM, Tony Fernandez
<[EMAIL PROTECTED]> wrote:
Hello all,
I am in the process of updating my DB on Postgres 8.1.11 to 8.3.4.
I also
use Slony 1.2.14 for replication.
Then you're set. One of the primary purp
Hi,
I have a number of tables with serial columns as a primary key.
I'm looking to add lots of records via copy, but should reset the serial
counters to the appropriate value after this.
Is there a simple way to do this, or do I just update the last_value column in
each seq table to the max(id
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 les
Jeff Davis wrote:
Is there any interest in an optional mode for pg_dump to order the
output so that it's easier to use diff?
I rsync my pg_dump output (-Fc -Z0), and I would appreciate an ordered
mode very much, along with anything else that makes the output as stable
as possible.
--
Kyle
On Mon, 10 Nov 2008, 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. Seems an obvious candidate for an in-core function ...
... which wil
On Nov 10, 2008, at 6:48 PM, Brent Wood wrote:
Hi,
I have a number of tables with serial columns as a primary key.
I'm looking to add lots of records via copy, but should reset the
serial counters to the appropriate value after this.
Is there a simple way to do this, or do I just update t
On Mon, 2008-11-10 at 17:05 -0800, Jeff Davis wrote:
> Is there any interest in an optional mode for pg_dump to order the
> output so that it's easier to use diff?
>
> I don't think it would make the output 100% deterministic, but it would
> make it easier to at least compare the data for small da
58 matches
Mail list logo
