2007/11/15, Andrus <[EMAIL PROTECTED]>:
> "PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)"
> Database size in disk returned by pg_database_size() is 210 MB
>
> Database compressesed backup file size is now 125 MB.
How do you produce this dump? pg_dump
Hi All,
Planning to implement tsearch2 for my websitem and dbschema. I wanted to
know if there is a "Best practices" guide I should be following. While
reading about it, I noticed there were lot of 'gotchas' with this, such as
back-up/restore, Slony 1 replication issues, etc..
What do most people
On Thu, 2007-11-15 at 17:13 +, Gregory Stark wrote:
> DELETE
> FROM atable AS x
> USING (SELECT ctid FROM atable LIMIT 5) AS y
> WHERE x.ctid = y.ctid;
Have you tried to EXPLAIN this one ? Last time I tried to do something
similar it was going for a sequential scan on atable with a fi
On Thu, 15 Nov 2007 20:35:35 +0200
"Andrus" <[EMAIL PROTECTED]> wrote:
> "PostgreSQL 8.2.3 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 3.4.2
> (mingw-special)"
> Database size in disk returned by pg_database_size() is 210 MB
>
> Database compressesed backup file size is now 125 MB.
> Thi
"nabakumar salam" <[EMAIL PROTECTED]> writes:
> "Error connecting to the server: FATAL: index "pg_class_oid_index"
> contains unexpected zero page at block 0
> HINT: Please REINDEX it."
> i tried starting the database using --single --P option , but says it
> cannot be started with user as admin.
Andrus wrote:
2. How to determine what data is containing in pg_toast_22185 ?
Why this is so big ?
That will be determined by the columns you define and the data you insert.
Read http://www.postgresql.org/docs/8.2/interactive/storage-toast.html
to get an explanation of TOAST and how you ca
On Fri, Nov 16, 2007 at 07:56:45PM -0800, adrobj wrote:
>
> Hello,
>
> I have a moderately large (~10-20GB) table:
>
> CREATE TABLE msgs (
> msg varchar(2048),
> msg_tsv tsvector,
> posted timestamp
> );
>
> CREATE INDEX msgs_i ON msgs USING gin(msg_tsv);
>
> The table never gets updated
Abhijeet <[EMAIL PROTECTED]> writes:
> I have tried following regex in & function:
>- SELECT regexp_replace('Abhijeet',
>
> '<(\s)*/?(?i:script|i|b|u|embed|object|a|frameset|frame|iframe|meta|link|style|table|th|td|tr|tbody|input|select|option|form|map|area|!--)(.|\n)*?>',
>'\&\s');
I
Ted Byers wrote:
It gave apparently correct values, but for some
reason, it insisted on returning thousands upon
thousands of identical record. There is something
awry there, but I can't place what. Yes, I know I
could use SELECT DISTINCT, but I worry that it may be
doing a full table scan, as
On Sun, Nov 18, 2007 at 04:19:01AM +, Ron Johnson wrote:
> If the PK was synthetic and generated by the engine, then a (buggy)
> app could insert duplicate tolls and the system wouldn't utter a
> peep. But the customer sure would when he saw the duplicate entries.
You'd just need to put a UNI
Looking at pg_stat_user_indexes, it seems a lot of the indexes aren't
actually used. That could be it.
Regards
Oliver
Begin forwarded message:
From: Oliver Kohll <[EMAIL PROTECTED]>
Date: 17 November 2007 15:06:38 GMT
To: pgsql-general@postgresql.org
Subject: max_fsm_relations
Hi,
My max_f
"Andrus" <[EMAIL PROTECTED]> writes:
> I created query returning biggest tables with and without indexes and found:
> 1 pg_toast_22185 95 MB 96 MB
> 1. Tables are relatively small and thus cannot create 125 MB compressed
> backup file.
You have failed to co
Shane Ambler wrote:
> I INSERTed 500 stocks entries and 10,000 stockprices entries for each
stock (that's 5,000,000 price rows), then from
EXPLAIN ANALYSE SELECT * FROM stock_price_history WHERE stock_id = 20
I got - Total runtime: 981.618 ms
EXPLAIN ANALYSE SELECT * FROM stock_price_history W
"Scott Marlowe" <[EMAIL PROTECTED]> writes:
> On Nov 16, 2007 11:59 AM, <[EMAIL PROTECTED]> wrote:
>> Does anybody know what does the subj means and why it occures ?
> You're giving us WAY too little information to troubleshoot this problem.
Indeed, but it seems to have something to do with a br
On Sun, Nov 18, 2007 at 09:59:41AM -0800, David Fetter wrote:
> Ordering is never guaranteed without an ORDER BY, except in the time
> between a CLUSTER and the first write operation after it.
It's my understanding that with the new "seqscan piggy-backing" in 8.3
even this will go. I'm not sure i
Hello, I currently have a table in Postgres 8.1 with a text column that
contains XML. I use the xml2 module to define several XPath-based function
indices on that column, and this has worked very well.
I'm trying not to evaluate the native XML support in 8.3b2. I dumped this
table from 8.1, then l
"Matt Magoffin" <[EMAIL PROTECTED]> writes:
> So I explored with a SELECT statement, thinking there was some specific
> XML document causing the crash. I could consistently execute this
> statement to get a crash:
> select XMLSERIALIZE( CONTENT
> (xpath('/als:auto-lead-service/als:[EMAIL PROTECTED
We have an application in which every customer has their own database, all
running from our Postgres server.
There is a large, mostly static, database of information (chemical
information), which each customer needs read access to. Lots of customer
data points to this static db, with foreign keys
"Robert James" <[EMAIL PROTECTED]> writes:
> 1.) Is there a way of separating, isolating, and sharing the shared data that
> will still allow FKs to it?
The only approach I know of would be to make all your customers use
independent schemas in one database, with isolation via appropriate
permissi
In article <[EMAIL PROTECTED]>,
[EMAIL PROTECTED] (Tom Lane) wrote:
> "Matt Magoffin" <[EMAIL PROTECTED]> writes:
> > So I explored with a SELECT statement, thinking there was some specific
> > XML document causing the crash. I could consistently execute this
> > statement to get a crash:
>
> >
At 5:51p -0500 on 14 Nov 2007, A.M. wrote:
> On Nov 14, 2007, at 4:23 PM, Scott Marlowe wrote:
>
>> On Nov 14, 2007 2:40 PM, madhtr <[EMAIL PROTECTED]> wrote:
>>> Quick question, are there any native functions in PostGreSQL 8.1.4
>>> that will strip HTML tags, escape chars, etc?
>>
>> I can't thin
On Mon, Nov 19, 2007 at 10:02:20AM +1300, Matt Magoffin wrote:
> Sorry if I left any relavent details out. I've been looking at this for
> a while so many things are probably obvious only to me. Could you hint
> at which additional details you think would be useful here?
What's being asked for i
Matt Magoffin <[EMAIL PROTECTED]> writes:
> Sorry if I left any relavent details out. I've been looking at this for
> a while so many things are probably obvious only to me. Could you hint
> at which additional details you think would be useful here?
What I asked for was a self-contained example
> On Mon, Nov 19, 2007 at 10:02:20AM +1300, Matt Magoffin wrote:
>> Sorry if I left any relavent details out. I've been looking at this for
>> a while so many things are probably obvious only to me. Could you hint
>> at which additional details you think would be useful here?
>
> What's being asked
Thanks. For legal requirements, we need to keep each customer in a fully
isolated, separate db. (I'm not very familiar with schema - perhaps they
can do the same thing...).
What about just dropping the FKs? Can we do cross DB joins? Are there
significant performance penalties?
On 11/18/07, Doug
"Robert James" <[EMAIL PROTECTED]> writes:
> Thanks. For legal requirements, we need to keep each customer in a fully
> isolated, separate db. (I'm not very familiar with schema - perhaps they
> can do the same thing...).
[ shrug... ] If your lawyers insist on that, wouldn't they also object
to
"Matt Magoffin" <[EMAIL PROTECTED]> writes:
> I understand. I cannot make the SELECT query nor the ADD INDEX command
> break on an empty database. I cannot share this database data, either.
So try to make a test case using dummy data, or with suitably obscured
versions of your real data.
Also, co
On Nov 19, 2007 11:39 AM, Tom Lane <[EMAIL PROTECTED]> wrote:
> [ shrug... ] If your lawyers insist on that, wouldn't they also object
> to all customers linking to the same copy of the shared data? They
> should, if they know what they're about.
You're implying that that lawyers understand what d
On Nov 19, 2007 12:29 PM, Robert James <[EMAIL PROTECTED]> wrote:
> Comedy aside, this makes a lot of sense:
> The shared data has nothing private in it at all - it's chemical info.
> Sharing it is no worse than sharing the application code, or the OS's
> libraries. It's the customer's data which
> "Matt Magoffin" <[EMAIL PROTECTED]> writes:
>> I understand. I cannot make the SELECT query nor the ADD INDEX command
>> break on an empty database. I cannot share this database data, either.
>
> So try to make a test case using dummy data, or with suitably obscured
> versions of your real data.
Comedy aside, this makes a lot of sense:
The shared data has nothing private in it at all - it's chemical info.
Sharing it is no worse than sharing the application code, or the OS's
libraries. It's the customer's data which needs to be isolated.
On 11/18/07, Andrej Ricnik-Bay <[EMAIL PROTECTED]> w
Em Friday 16 November 2007 18:57:24 Ed L. escreveu:
>
> I often have need for views that reference views that reference
> views, and so on. When I need to make a small update to one of
> the views, I am faced with having to drop and recreate all
> dependent views even if the driving change just ad
"Matt Magoffin" <[EMAIL PROTECTED]> writes:
> (gdb) bt
> #0 0x8820 in __memcpy ()
> #1 0x004d9098 in xmlBufferAdd ()
> #2 0x004e0dc4 in xmlParserInputBufferCreateMem ()
> #3 0x004ced98 in xmlCtxtReadMemory ()
> #4 0x0026ea0c in xpath (fcinfo=0x37) at xml.c:3183
> #5 0x001095bc in ExecMake
> "Matt Magoffin" <[EMAIL PROTECTED]> writes:
>> (gdb) bt
>> #0 0x8820 in __memcpy ()
>> #1 0x004d9098 in xmlBufferAdd ()
>> #2 0x004e0dc4 in xmlParserInputBufferCreateMem ()
>> #3 0x004ced98 in xmlCtxtReadMemory ()
>> #4 0x0026ea0c in xpath (fcinfo=0x37) at xml.c:3183
>> #5 0x001095bc in
> "Matt Magoffin" <[EMAIL PROTECTED]> writes:
>> (gdb) bt
>> #0 0x8820 in __memcpy ()
>> #1 0x004d9098 in xmlBufferAdd ()
>> #2 0x004e0dc4 in xmlParserInputBufferCreateMem ()
>> #3 0x004ced98 in xmlCtxtReadMemory ()
>> #4 0x0026ea0c in xpath (fcinfo=0x37) at xml.c:3183
>> #5 0x001095bc in
>>> (gdb) bt
>>> #0 0x8820 in __memcpy ()
>>> #1 0x004d9098 in xmlBufferAdd ()
>>> #2 0x004e0dc4 in xmlParserInputBufferCreateMem ()
>>> #3 0x004ced98 in xmlCtxtReadMemory ()
>>> #4 0x0026ea0c in xpath (fcinfo=0x37) at xml.c:3183
>>> #5 0x001095bc in ExecMakeFunctionResultNoSets (fcache=
On Fri, 2007-11-16 at 09:28 -0600, Erik Jones wrote:
> On Nov 16, 2007, at 3:26 AM, Ow Mun Heng wrote:
>
> >
> > On Fri, 2007-11-16 at 10:22 +0100, Martijn van Oosterhout wrote:
> >> On Fri, Nov 16, 2007 at 09:00:46AM +0800, Ow Mun Heng wrote:
> >>> I usually write a function which gets/uses the
"Matt Magoffin" <[EMAIL PROTECTED]> writes:
> I took the latest snapshot from /dev, but I still get the same crash:
Drat :-(. Please try a bit harder at generating a self-contained
test case. Given where the crash is happening, I suspect it may be
only weakly if at all dependent on your input d
> "Matt Magoffin" <[EMAIL PROTECTED]> writes:
>> I took the latest snapshot from /dev, but I still get the same crash:
>
> Drat :-(. Please try a bit harder at generating a self-contained
> test case. Given where the crash is happening, I suspect it may be
> only weakly if at all dependent on you
"Matt Magoffin" <[EMAIL PROTECTED]> writes:
> Hmm. I do have a load testing program with which I _might_ be able to get
> to generate a sufficient amount of dummy data. However, it apparently will
> require many tens of thousands of rows to reproduce the problem. Will I be
> able to post a dump fil
Ow Mun Heng wrote:
Turns out this is a 2 part question, for which I have 1 solved.
1. using perl DBI to pull from MSSQL to PG..
--> I found out I can use
my $ins_rows = $dbh_pg->do($query2) or die "prepare failed
$DBI::errstr";
2. using pure SQL (via pgagent jobs) to pull. This is the one whi
> So, how can I do to execute it as if it was the first
> time again?
Reboot.
As Lew pointed out, that might not actually be a good idea, because caching
means that most queries will most of the time not run with that "first time"
performance.
--
Scott Ribe
[EMAIL PROTECTED]
http://www.killerb
I initialized a database directory and it started fine. But when I tried to
create user I got this error.
Can you tell what is wrong?
thanks
createuser --superuser --createdb --createrole -P postgres
Enter password for new role:
Enter it again:
createuser: could not connect to database postgres:
"[EMAIL PROTECTED]" <[EMAIL PROTECTED]> writes:
> LOG: setsockopt(TCP_NODELAY) failed: Option not supported by protocol
I think you would need to have a word with Sun about that. If a
platform exposes the option then it ought to support it. For that
matter, no remotely-modern Unix platform shou
[EMAIL PROTECTED] wrote:
I initialized a database directory and it started fine. But when I
tried to create user I got this error.
Can you tell what is wrong?
thanks
I did a quick check on my Solaris box and it seems to be working fine
for me.
Can you provide more information on what exactly y
Hi there,
I run an aggregation on national statistics to retrieve regional
values (for
Africa, Europe, ...). Now, I want to have a global aggregation as
well. The
easiest thing for my PHP/HTML procedure would be to have the
global row make
appear within the regional result. So it would
On Tue, 2007-11-13 at 09:49 -0500, Bill Moran wrote:
> In response to Ow Mun Heng <[EMAIL PROTECTED]>:
> > How does one monitor it closely anyway? the warning comes when one does
> > a vacuum verbose and with autovacuum turned on, I don't even see it
> > anywhere.
>
> 1) Run vacuum verbose from c
I've working with XML in Postgres 8.3 and am trying to find a way to
create a text-based index from an XPath that returns multiple nodes. For
example, if I have an XPath like
/[EMAIL PROTECTED]"mykey"]/text()
which might return a few text nodes like
value1
value2
value3
I'd like 3 index values
48 matches
Mail list logo
