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
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
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
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 ideas
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
2010/1/8 Mark Morgan Lloyd markmll.pgsql-gene...@telemetry.co.uk
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
--- On Fri, 8/1/10, Mark Morgan Lloyd markMLl.pgsql-
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
2010/1/8 Alban Hertroys dal...@solfertje.student.utwente.nl:
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,
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 system
Konrad Garus konrad.ga...@gmail.com writes:
2010/1/8 Alban Hertroys dal...@solfertje.student.utwente.nl:
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
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
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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)
Konrad Garus konrad.ga...@gmail.com writes:
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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
Yan Cheng Cheok ycch...@yahoo.com 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;
-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
=?UTF-8?Q?Filip_Rembia=C5=82kowski?= plk.zu...@gmail.com 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;
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.
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
On Fri, Jan 8, 2010 at 2:58 PM, Sam Mason s...@samason.me.uk 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
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
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
2010/1/8 Grzegorz Jaśkiewicz gryz...@gmail.com:
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
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
2010/1/8 Konrad Garus konrad.ga...@gmail.com:
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
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
Konrad Garus konrad.ga...@gmail.com 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
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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 konrad.ga...@gmail.com writes:
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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
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
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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
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
Konrad Garus konrad.ga...@gmail.com 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
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
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 |
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
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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
Adrian Klaver adrian.kla...@gmail.com writes:
On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
Name| Owner | Encoding | Collation |Ctype| Access
privileges
---+--+--+-+-+---
skynet| postgres | UTF8
Konrad Garus escribió:
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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
Em 08/01/2010, às 14:48, Tom Lane escreveu:
Adrian Klaver adrian.kla...@gmail.com writes:
On 01/08/2010 08:39 AM, Fernando Morgenstern wrote:
Name| Owner | Encoding | Collation |Ctype| Access
privileges
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
2010/1/8 Alvaro Herrera alvhe...@commandprompt.com:
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
Konrad Garus konrad.ga...@gmail.com writes:
2010/1/8 Alvaro Herrera alvhe...@commandprompt.com:
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),
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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.
Konrad Garus konrad.ga...@gmail.com writes:
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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.
On 01/08/2010 09:31 AM, Konrad Garus wrote:
2010/1/8 Tom Lanet...@sss.pgh.pa.us:
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
2010/1/8 Adrian Klaver adrian.kla...@gmail.com:
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
Konrad Garus konrad.ga...@gmail.com writes:
2010/1/8 Tom Lane t...@sss.pgh.pa.us:
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
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
t1
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
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 || '' from
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
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:
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
On Fri, Jan 8, 2010 at 9:58 AM, Adrian Klaver adrian.kla...@gmail.comwrote:
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
On Thu, Jan 7, 2010 at 11:11 PM, akp geek akpg...@gmail.com 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
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 vi...@khera.org wrote:
On Thu, Jan 7, 2010 at 11:11 PM, akp geek akpg...@gmail.com wrote:
I have query in production and test.
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 akpg...@gmail.com wrote:
The volume of data is less in Test compared to prod. and I synced the
postgresql.conf file in both environments
--
Sent via
OK.. got you.
Regards
On Fri, Jan 8, 2010 at 2:37 PM, Vick Khera vi...@khera.org 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 akpg...@gmail.com wrote:
The volume of data is less in Test
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
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?
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
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, it's
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 a
On Fri, Jan 8, 2010 at 3:07 PM, Greg Smith g...@2ndquadrant.com 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
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)
Oh, so the row
Alban Hertroys dal...@solfertje.student.utwente.nl 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
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
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
Ran a
To clean up from a prior run.
Erik Jones ejo...@engineyard.com 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
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 finish sooner.
Shut
On Thu, 7 Jan 2010 21:04:45 -0700,
Scott Marlowe scott.marl...@gmail.com wrote:
On Wed, Dec 30, 2009 at 6:39 PM, Seb splu...@gmail.com wrote:
CREATE RULE footwear_nothing_upd AS ON UPDATE TO footwear DO
INSTEAD NOTHING; CREATE RULE footwear_newshoelaces_upd AS ON
UPDATE TO footwear
73 matches
Mail list logo
