On Thu, 7 Jan 2010 21:04:45 -0700,
Scott Marlowe wrote:
> On Wed, Dec 30, 2009 at 6:39 PM, Seb wrote:
>> CREATE RULE footwear_nothing_upd AS ON UPDATE TO footwear DO
>> INSTEAD NOTHING; CREATE RULE footwear_newshoelaces_upd AS ON
>> UPDATE TO footwear WHERE NEW.sl_name <> OLD.sl_name AN
On Jan 8, 2010, at 4:50 PM, Erik Jones wrote:
>
> On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote:
>
>> OK,
>>
>> So what am I doing wrong here?
>>
>> Installed PG 8.3.7 on Slave machine
>>
>> Restored from last evening's backup from the master DB to make the rsync
>> across the network fini
To clean up from a prior run.
Erik Jones wrote:
On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote:
> OK,
>
> So what am I doing wrong here?
>
> Installed PG 8.3.7 on Slave machine
>
> Restored from last evening's backup from the master DB to make the rsync
> across the network finish sooner.
>
>
On Jan 8, 2010, at 9:50 AM, Keaton Adams wrote:
> OK,
>
> So what am I doing wrong here?
>
> Installed PG 8.3.7 on Slave machine
>
> Restored from last evening's backup from the master DB to make the rsync
> across the network finish sooner.
>
> Shut down the PG instance on the slave machine
Well, kinder and gentler on my disks, at least. I'm hoping for something more
terse than what I'm seeing in my default 8.4.2 install, and also something that
can be combined with the functionality of log_min_duration_statement.
Is there such a thing? My goal is to achieve some accounting on whic
Alban Hertroys writes:
> You seem to know what you're doing, but just in case we missed something as
> this is strange enough to have even the devs scratching their heads. The rows
> are there, so it _has_ to be an index or a transaction visibility issue...
The successful fetch-by-ctid test see
On 8 Jan 2010, at 18:28, Tom Lane wrote:
>> # select attachment_id from attachment where ctid = '(603713,1)';
>> attachment_id
>> ---
>> 15460683
>> (1 row)
>
>> # select attachment_id from attachment where attachment_id = 15460683;
>> attachment_id
>> ---
>> (0 rows)
On Fri, Jan 8, 2010 at 3:07 PM, Greg Smith wrote:
> Basically, you have a couple of standard issues here:
>
> 1) You're using RAID-5, which is not known for good write performance. Are
> you sure the disk array performs well on writes? And if you didn't
> benchmark it, you can't be sure.
This c
Anton Belyaev wrote:
I think all the IOwait comes during sync time, which is 80 s,
according to the log entry.
I believe you are correctly diagnosing the issue. The "sync time" entry
in the log was added there specifically to make it easier to confirm
this problem you're having exists on
Le 08/01/2010 21:22, glaucomag a écrit :
> Hi, I've a problem with pgadmin. If I access to database with user X
> and I save password, when I access to database from shell (psql)
> password is not required. Of course pg_hba.conf is:
>
> local database X md5
>
> If I don't save password in pgadmin
Hi, I've a problem with pgadmin. If I access to database with user X
and I save password, when I access to database from shell (psql)
password is not required. Of course pg_hba.conf is:
local database X md5
If I don't save password in pgadmin, it's ok (psql required password).
The question is thi
When joining two large tables [common in warehousing], a hash join is commonly
selected. Calculating hash values for the merge phase is CPU intensive. Is
there any way to pre-calculate value hashes to save that time? Would it even
grant any performance to skip the build phase of the hash join?
From: akp geek [mailto:akpg...@gmail.com]
Sent: Thursday, January 07, 2010 9:04 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Index question on postgres
{snip}
Why would the index I have created not being used?
>>
The index you have created will not be used in
OK.. got you.
Regards
On Fri, Jan 8, 2010 at 2:37 PM, Vick Khera wrote:
> there ya go. the query plan will change based on the data statistics
> on the tables and indexes.
>
> On Fri, Jan 8, 2010 at 2:09 PM, akp geek wrote:
> > The volume of data is less in Test compared to prod. and I synced
there ya go. the query plan will change based on the data statistics
on the tables and indexes.
On Fri, Jan 8, 2010 at 2:09 PM, akp geek wrote:
> The volume of data is less in Test compared to prod. and I synced the
> postgresql.conf file in both environments
--
Sent via pgsql-general mailing
The volume of data is less in Test compared to prod. and I synced the
postgresql.conf file in both environments
Regards
On Fri, Jan 8, 2010 at 1:55 PM, Vick Khera wrote:
> On Thu, Jan 7, 2010 at 11:11 PM, akp geek wrote:
> > I have query in production and test. The tables in both the environme
On Thu, Jan 7, 2010 at 11:11 PM, akp geek wrote:
> I have query in production and test. The tables in both the environment has
> the same structure ,indexes and constraints. But the in the test and the
> prod the explain plan is totally different. In test environment the query is
> taking long tim
On Fri, Jan 8, 2010 at 9:58 AM, Adrian Klaver wrote:
> On 01/08/2010 09:53 AM, Fernando Morgenstern wrote:
>
>>
>>
>
> Actually what is strange is that your previous listing :
>
> postgres=# select '"' || datname || '"' from pg_database;
> ?column?
> -
> "template1"
> "template0"
Hello dear list members,
I have strange problem with my new 8.4 deployment, which I never
encountered on previous 8.3 deployment.
IOwait values are extremely high exactly when Postgres finishes a checkpoint.
During the checkpoint itself (which is quite lengthy) IOwait is very low.
Why does this ha
On 01/08/2010 09:53 AM, Fernando Morgenstern wrote:
Em 08/01/2010, às 15:49, Adrian Klaver escreveu:
On 01/08/2010 08:55 AM, Fernando Morgenstern wrote:
Hello,
Thanks for your quick answers. The extra space is indeed a copy-and-paste
issue. Here it is the select that you suggested:
postgre
Adrian Klaver wrote:
In the case you describe the below might work:
http://www.postgresql.org/docs/8.4/interactive/app-psql.html
"Before starting up, psql attempts to read and execute commands from the
system-wide psqlrc file and the user's ~/.psqlrc file. (On Windows, the
user's startup file
Em 08/01/2010, às 15:49, Adrian Klaver escreveu:
> On 01/08/2010 08:55 AM, Fernando Morgenstern wrote:
>
>> Hello,
>>
>> Thanks for your quick answers. The extra space is indeed a copy-and-paste
>> issue. Here it is the select that you suggested:
>>
>> postgres=# select '"' || datname || '"' f
OK,
So what am I doing wrong here?
Installed PG 8.3.7 on Slave machine
Restored from last evening's backup from the master DB to make the rsync across
the network finish sooner.
Shut down the PG instance on the slave machine
Ran a script that does the following:
select pg_start_backup('Maste
On 01/08/2010 08:55 AM, Fernando Morgenstern wrote:
Hello,
Thanks for your quick answers. The extra space is indeed a copy-and-paste
issue. Here it is the select that you suggested:
postgres=# select '"' || datname || '"' from pg_database;
?column?
-
"template1"
"template0"
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> What the above says is that you have a
>> corrupt index on attachment_id, which you should be able to fix via
>> REINDEX.
> This is not correct. The dumps are made with pg_dump. We did reindex
> on the table. I also tried looking for the row with anot
2010/1/8 Adrian Klaver :
> This looks a lot like this thread:
> http://archives.postgresql.org/pgsql-general/2009-12/msg00726.php
>
> Could we see the schema and indexes for this table?
Table "public.attachment"
Column |Type | Modifiers
On 01/08/2010 09:31 AM, Konrad Garus wrote:
2010/1/8 Tom Lane:
Oh, so the row *is* there.
Right. I'm happy to see it.
What the above says is that you have a
corrupt index on attachment_id, which you should be able to fix via
REINDEX.
This is not correct. The dumps are made with pg_dump.
2010/1/8 Tom Lane :
> Oh, so the row *is* there.
Right. I'm happy to see it.
> What the above says is that you have a
> corrupt index on attachment_id, which you should be able to fix via
> REINDEX.
This is not correct. The dumps are made with pg_dump. We did reindex
on the table. I also tried l
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> Just to confirm, if you try to select any of these rows by ctid, ie
>> select * from tablename where ctid = '(603713,1)';
>> you get nothing? What *should* happen is that you get the row if you
>> mention offset 1, 3, or 5, but nothing if you s
2010/1/8 Tom Lane :
> Just to confirm, if you try to select any of these rows by ctid, ie
> select * from tablename where ctid = '(603713,1)';
> you get nothing? What *should* happen is that you get the row if you
> mention offset 1, 3, or 5, but nothing if you say 2 or 4.
How about this?
Konrad Garus writes:
> 2010/1/8 Alvaro Herrera :
>> I'm a bit surprised by the block numbers in the block header vs. t_self ...
>> I would have guessed that they come from a different segment (and
>> the numbers seem to match, as 603713 % 131072 = 79425), but Konrad
>> doesn't seem to be using the
On 01/08/2010 08:59 AM, Mark Morgan Lloyd wrote:
hubert depesz lubaczewski wrote:
On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote:
Is there any way of getting psql to display the name of the
currently-connected server in its prompt, and perhaps a custom string
identifying e.g.
2010/1/8 Alvaro Herrera :
> I'm a bit surprised by the block numbers in the block header vs. t_self ...
> I would have guessed that they come from a different segment (and
> the numbers seem to match, as 603713 % 131072 = 79425), but Konrad
> doesn't seem to be using the foo.4 file.
I am not sure
hubert depesz lubaczewski wrote:
On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote:
Is there any way of getting psql to display the name of the
currently-connected server in its prompt, and perhaps a custom string
identifying e.g. a disc set, without having to create a psqlrc
Em 08/01/2010, às 14:48, Tom Lane escreveu:
> Adrian Klaver writes:
>> On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
>>> Name| Owner | Encoding | Collation |Ctype| Access
>>> privileges
>>> ---+--+--+-+-+---
Konrad Garus escribió:
> 2010/1/8 Tom Lane :
> > So, no wraparound problem ... odder and odder. Could we see the whole
> > -i -f printout for that block? You trimmed some of it before,
> > particularly the block header.
>
> Attached.
>
> Since data on disk looks correct, is it possible to diagn
Adrian Klaver writes:
> On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
>> Name| Owner | Encoding | Collation |Ctype| Access
>> privileges
>> ---+--+--+-+-+---
>> skynet| postgres | UTF8 | en_US.UT
2010/1/8 Tom Lane :
> So, no wraparound problem ... odder and odder. Could we see the whole
> -i -f printout for that block? You trimmed some of it before,
> particularly the block header.
Attached.
Since data on disk looks correct, is it possible to diagnose it on a
higher level? Could the dam
On Fri, Jan 08, 2010 at 02:39:03PM -0200, Fernando Morgenstern wrote:
> postgres=# \l
> List of databases
>Name| Owner | Encoding | Collation |Ctype| Access
> privileges
> ---+--+--+-+-+
On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
Hello,
I'm running version 8.4.1 and have a table that appears on listing ( when i
run \l ) but i can't drop it. Example:
postgres=# \l
List of databases
Name| Owner | Encoding | Collation |
Hello,
I'm running version 8.4.1 and have a table that appears on listing ( when i
run \l ) but i can't drop it. Example:
postgres=# \l
List of databases
Name| Owner | Encoding | Collation |Ctype| Access
privileges
---+
Konrad Garus writes:
> Latest checkpoint's NextXID: 0/83037806
So, no wraparound problem ... odder and odder. Could we see the whole
-i -f printout for that block? You trimmed some of it before,
particularly the block header.
regards, tom lane
--
Sent via pgs
Just a reminder - these rows are over 6 months old and were lost at
night when the system was lightly used.
--
Konrad Garus
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2010/1/8 Tom Lane :
> Also, what are the XMINs of the non-missing tuples in the adjacent
> blocks?
# /usr/lib/postgresql/8.3/bin/pg_controldata /var/lib/postgresql/8.3/main/
pg_control version number:833
Catalog version number: 200711281
Database system identifier:
I wrote:
> Huh. Nothing obviously wrong with the data ... maybe an xid wraparound
> issue? What's your current XID counter? (pg_controldata is the easiest
> way to answer that)
Also, what are the XMINs of the non-missing tuples in the adjacent
blocks?
regards, tom lane
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> So which of these rows are invisible? According to the flags
>> items 1, 3 and 5 should be visible while 2 and 4 are dead versions
>> (of 3 and 5 respectively).
> All 3 are invisible, and at the same time they are the only 3 rows
> missing from the t
2010/1/8 Tom Lane :
> So which of these rows are invisible? According to the flags
> items 1, 3 and 5 should be visible while 2 and 4 are dead versions
> (of 3 and 5 respectively).
All 3 are invisible, and at the same time they are the only 3 rows
missing from the table.
--
Konrad Garus
--
Se
Konrad Garus writes:
> OK, I got it. Attached is the dump of the missing block.
So which of these rows are invisible? According to the flags
items 1, 3 and 5 should be visible while 2 and 4 are dead versions
(of 3 and 5 respectively).
regards, tom lane
--
Sent via pgsq
OK, I got it. Attached is the dump of the missing block.
--
Konrad Garus
missing_block
Description: Binary data
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
2010/1/8 Konrad Garus :
> I'm unsure about it, because the resulting file does not seem to have
> the rows I saw listed for block 603712 or 603714. I checked by text in
> VARCHAR columns.
I must've done something wrong.
I found that row listed at:
Block 603712 **
How shall I do it? Is this correct:
1. Run:
select ctid, * from attachment where ...
on the table with such a WHERE clause that includes rows around the
missing ones. ctid around missing rows seems to be (603712,78) and
(603714,1). Note that 603713 is missing.
2. Run:
select relfilenode from p
2010/1/8 Grzegorz Jaśkiewicz :
> what is that "(t" in the SELECT there for ?
> or is it just typo, or something missing/etc ?
>
ignore it. That's cast, for type t (table).
--
GJ
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http:/
I did find some references to a fix of last-completed transaction time and I
looked in the postgresql-bugs archive, but I'm not having any luck confirming
that this is a problem in 8.3.7 and an upgrade to 8.3.9 would fix the issue.
postgresql 8.3.7 Fix incorrect logging of last-completed-tr
what is that "(t" in the SELECT there for ?
or is it just typo, or something missing/etc ?
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On Fri, Jan 8, 2010 at 2:58 PM, Sam Mason wrote:
> Yup, this thing is a bit fiddly. Try:
>
> http://archives.postgresql.org/pgsql-general/2009-09/msg01176.php
I searched for it, but didn't stumble upon that one. Thanks.
--
GJ
--
Sent via pgsql-general mailing list (pgsql-general@postgres
On Fri, Jan 08, 2010 at 02:55:53PM +, Grzegorz Jaaakiewicz wrote:
> Is there any nice way to do something like that in plpgsql:
>
> EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';
>
> It would probably work, but some values are NULL, and plpgsql
> interpreter just puts
Is there any nice way to do something like that in plpgsql:
EXECUTE 'INSERT INTO '||partition_table_name||' VALUES'||(NEW.*)||'';
It would probably work, but some values are NULL, and plpgsql
interpreter just puts empty space there. So I get ('1',2,3,,,); Which
obviously is confusing INSERT.
t
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= writes:
> create table test (id serial primary key, t1 text, t2 text);
> create function myhash(test) returns text as 'select md5($1::text)' language
> sql immutable;
> create index myhash on test( myhash(test) );
> alter table test add t3 text;
> alter table t
-BEGIN PGP SIGNED MESSAGE-
Hash: RIPEMD160
> Is there any way of getting psql to display the name of the
> currently-connected server in its prompt, and perhaps a custom string
> identifying e.g. a disc set, without having to create a psqlrc file on
> every client system that's got a pre
Yan Cheng Cheok writes:
> Currently, I try to call a stored procedure with void returned type.
> PGresult *res = PQexec(this->getConnection(), "SELECT * FROM
> create_tables()");
> if (PQresultStatus(res) != PGRES_COMMAND_OK)
> {
> PQclear(res);
> return false;
> }
Konrad Garus writes:
> 2010/1/8 Tom Lane :
>> Do you know that the rows disappeared recently?
> Yes. They are present in dump from 9 PM and missing from dump from 1
> AM. It must've happened within this 4-hour window.
Hm. It would be interesting to see if you can find the place where the
rows h
2010/1/8 Tom Lane :
> Do you know that the rows disappeared recently?
Yes. They are present in dump from 9 PM and missing from dump from 1
AM. It must've happened within this 4-hour window.
--
Konrad Garus
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes t
On Fri, 2010-01-08 at 11:20 +, Mark Morgan Lloyd wrote:
> Is there any way of getting psql to display the name of the
> currently-connected server in its prompt, and perhaps a custom string
> identifying e.g. a disc set, without having to create a psqlrc file on
> every client system that's
Konrad Garus writes:
> 2010/1/8 Alban Hertroys :
>> I get the impression the data you lost and the data around it hasn't been
>> written to in a long time; it wouldn't surprise me if your problem would
>> have been caused by a bad sector on a disk, but that depends on how reliable
>> your stora
On Fri, Jan 08, 2010 at 11:20:36AM +, Mark Morgan Lloyd wrote:
> Is there any way of getting psql to display the name of the
> currently-connected server in its prompt, and perhaps a custom string
> identifying e.g. a disc set, without having to create a psqlrc file on
> every client syst
2010/1/8 Alban Hertroys :
> You seem to have lost the actual data, not the index entries pointing to it,
> or a sequential scan (eg. pg_dump) would still have found your rows.
I agree.
> What kind of file-system is the affected table on? - and while we're at it,
> what OS/Distribution and vers
--- On Fri, 8/1/10, Mark Morgan Lloyd Is there any way of getting psql to
> display the name of the currently-connected server in its
> prompt, and perhaps a custom string identifying e.g. a disc
> set, without having to create a psqlrc file on every client
> system that's got a precompiled psql i
2010/1/8 Mark Morgan Lloyd
> Is there any way of getting psql to display the name of the
> currently-connected server in its prompt, and perhaps a custom string
> identifying e.g. a disc set, without having to create a psqlrc file on every
> client system that's got a precompiled psql installed?
Is there any way of getting psql to display the name of the
currently-connected server in its prompt, and perhaps a custom string
identifying e.g. a disc set, without having to create a psqlrc file on
every client system that's got a precompiled psql installed?
I've just come close to dropping
On 7 Jan 2010, at 11:12, Konrad Garus wrote:
> Hello,
>
> We use PG 8.3. We use pg_dump and pg_restore overnight to create
> ...
You seem to have lost the actual data, not the index entries pointing to it, or
a sequential scan (eg. pg_dump) would still have found your rows.
> Do you have any i
Full test case, reproduced in 8.4.2 on two different hosts
create table test (id serial primary key, t1 text, t2 text);
create function myhash(test) returns text as 'select md5($1::text)' language
sql immutable;
create index myhash on test( myhash(test) );
alter table test add t3 text;
alter table
Is there a way to know/estimate how much is left to complete a
restore?
It would be enough just knowing which part of the file is being
restored (without causing too much extra IO, that will definitively
put my notebook on its knee).
Next time I try a restore on this box is there anything I could
In response to Yan Cheng Cheok :
> Hello all,
>
> I have the following procedure. I wish it will return a single row
> result to caller, after I insert the value (as the row contains
> several auto generated fields), without perform additional SELECT
> query.
>
> According to
> http://wischner.bl
73 matches
Mail list logo