[GENERAL] hi, how to let the inserted tuple visible to other backend when current backend hasn't finish?
hi, These codes are in the postgresql engine, just assume they are in
PortalRun() function:
//1.create table structure
char *relname = "test";
...
relOid = heap_create_with_catalog(relname, );
CommandCounterIncrement();
...
//2.then i can use SPI_execute to create index on this created table
SPI_connect();
char *sqlCreate_index ="create index on test."
int ret = SPI_execute(sqlCreate_index , false, 1);
SPI_finish();
.
//3.until now it performs well,but after i insert a tuple in this table
Relation mcir_relation = relation_open(relOid); //the relation just created
HeapTupleData htup;
simple_heap_insert(relation, &htup);
CommandCounterIncrement();
...
//4.then again want to invoke SPI_execute("select"), it seems the
inserted tuple is not visible to SPI_execute()
SPI_connect();
int ret = SPI_execute("select * from test;", true, 1);
if (ret == SPI_OK_SELECT && SPI_processed == 1 ) {
}
the ret is SPI_OK_SELECT ,but SPI_processed == 0, the inserted tuple is not
visible to SPI_execute() .
i've used these methods to try to let it visible to SPI_execute() :
simple_heap_insert()
CommandCounterIncrement();
or:
BeginInternalSubTransaction(NULL);
simple_heap_insert()...
ReleaseCurrentSubTransaction();
but they all don't work, how to resolve it?
thanks!
[GENERAL] Monitoring Object access
Hi, Using the catalog tables, is there any way to correlate session id/user id to which object (i.e. tables, indexes etc) it access and much how disk reads or I/O wait has been done against the objects. in general, I'd like to see which objects are being accessed by which user and the time/amount of I/O wait/reads. Thanks in advance, Adi
Re: [GENERAL] Incrementally Updated Backups
On Sunday 12 September 2010 00:43:19 Bruce Momjian wrote: > Gabe Nell wrote: > > > That section has been removed from the current 9.0 docs because we are > > > unsure it works. > > > > Hmm. So the only way to make a consistent backup from a standby server > > is to shut down the standby first? Or is even that problematic? > > > > Would it change anything if we are able to guarantee that the > > filesystem is snapshotted as a point-in-time snapshot by using LVM or > > on Amazon EC2 by snapshotting an EBS volume? > > I believe a snapshot of the standby is fine even if it is running, just > like on the master. How can you ensure the snapshot is in a consistent state if the server is running? If a snapshot is taken between 2 updates in a single transaction, only half of this transaction is included in the snapshot. I would never take an LVM (or similar) snapshot of an application that can't be paused in a way to provide a consistent filesystem. -- Joost -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote: > How can you ensure the snapshot is in a consistent state if the server is > running? > > If a snapshot is taken between 2 updates in a single transaction, only half > of > this transaction is included in the snapshot. > I would never take an LVM (or similar) snapshot of an application that can't > be paused in a way to provide a consistent filesystem. That's the trick, the filesystem is always in a consistant state, otherwise how could a database survive a power failure? The trick is WAL, which ensure that changes are logged consistantly and replays them if the database crashes. If you take a snapshot the database will simply startup and replay the log as if the machine crashed at the point. All committed transactions appears anything uncommitted vanishes. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Patriotism is when love of your own people comes first; nationalism, > when hate for people other than your own comes first. > - Charles de Gaulle signature.asc Description: Digital signature
[GENERAL] why can't see the updated value after SPI_execute("update ....", false, 1);
First I use SPI_execute("update
bool succ;
SPI_connect();
int ret = SPI_execute("update where uid = 1", false, 1);//later will
add error processing
if (ret == SPI_OK_UPDATE && SPI_processed == 1) {
succ = true;
}
SPI_finish();
Then I use SPI_execute("select .where uid = 1",
SPI_connect();
int ret = SPI_execute("select .where uid = 1", true, 1);//later will
add error processing
if (ret == SPI_OK_SELECT && SPI_processed == 1) {
HeapTuple tuple;
tuple = SPI_tuptable->vals[0];
...
datum = heap_getattr(tuple,attrno,SPI_tuptable->tupdesc,&isnull);
}
the datum doesn't change, why ?
Re: [GENERAL] How to inherit search_path from template
On Thu, Sep 9, 2010 at 7:41 PM, Merlin Moncure wrote: > On Thu, Sep 9, 2010 at 7:13 AM, Phui Hock wrote: >> Hi, >> How can I create a database template with altered search_path to be >> inherited by child databases? Say, I created a template named >> template_a with the following commands: > > It doesn't really work that way -- GUC values are global, not per > database. You can do "alter database xyz set search_path=..." -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Huge amount of memory errors with libpq
On 09/12/2010 02:53 PM, Casey Jones wrote:
My development server was initially running 8.4.4 on Gentoo. I downgraded to
8.1.21 (still on Gentoo) to match my CentOS production server to see if the
problems would go away, but they didn't.
Thanks for the test case. It's rare - and delightful - to see a neat,
minimal test case for any kind of question or issue report posted here.
The test case helps eliminate any outside code as a problem. In this
case, though, it's almost certainly that valgrind doesn't know about
glibc's sse3 code, for which it needs additional suppressions to handle
apparent memory errors that are in fact OK.
http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=583856
Anyway, since you've provided a test program, I can at least run it here
on a modern PostgreSQL and see what results I get to provide some more
info. In this case, it runs fine and no issues are detected. I'm on a
64-bit Fedora 13 install with glibc 2.12.3 and postgresql 9.0rc1 , so
it's not exactly a close match for your system. It is a Core 2 Duo, so
it's SSE3 capable hardware as confirmed by /proc/cpuinfo. I'm using
valgrind 3.5.0 .
$ valgrind --tool=memcheck --leak-check=full ./a.out
==26001== Memcheck, a memory error detector
==26001== Copyright (C) 2002-2009, and GNU GPL'd, by Julian Seward et al.
==26001== Using Valgrind-3.5.0 and LibVEX; rerun with -h for copyright info
==26001== Command: ./a.out
==26001==
==26001==
==26001== HEAP SUMMARY:
==26001== in use at exit: 0 bytes in 0 blocks
==26001== total heap usage: 102 allocs, 102 frees, 47,606 bytes allocated
==26001==
==26001== All heap blocks were freed -- no leaks are possible
==26001==
==26001== For counts of detected and suppressed errors, rerun with: -v
==26001== ERROR SUMMARY: 0 errors from 0 contexts (suppressed: 6 from 6)
Looking at the trace from yours, it appears to me that it's trying to
use an operation with an 8 byte input on the last four bytes of a
12-byte string. That string is certainly going to be "dbname=mydb\0", as
"dbname=mydb" is 11 bytes long and is the conninfo string being supplied
to libpq.
It's hard to see how strcmp could perform an incorrect read on that due
to bad input from libpq, so long as the null-terminator is present on at
least the shorter of the inputs if not both. In this case it's present
on the string the error report complains about, excluding a missing
terminator as a problem cause. There's no length argument to be wrong,
nothing much else at all to be wrong in what libpq supplies to libc.
I strongly suspect that glibc is doing funky magic with sse3 string
operations that cause apparently invalid reads that are actually safe,
*or* there's an issue with valgrind its self.
It'd be interesting to test the following program:
#include
#include
#include
int main(int argc, char **argv)
{
int cmpresult = strcmp("user", strdup("dbname=classads"));
printf("Comparison: %i\n", cmpresult);
}
... to see if it, too, reports errors from valgrind. It doesn't here, of
course (though interestingly strcmp returns 1 under valgrind and 17
outside it); I'd like to see what your results are.
--
Craig Ringer
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monitoring Object access
On 09/12/2010 06:52 PM, adi hirschtein wrote: Hi, Using the catalog tables, is there any way to correlate session id/user id to which object (i.e. tables, indexes etc) it access and much how disk reads or I/O wait has been done against the objects. in general, I'd like to see which objects are being accessed by which user and the time/amount of I/O wait/reads. There isn't really anything like that, no. You have pg_stat_activity, but it's a pretty coarse tool. The shared buffer cache and the use of things like synchronized sequential scans means that it wouldn't even be possible to truly track who's causing load reliably. As I understand it, if Joe and Fred both to a "SELECT * FROM sometable", it's quite likely that only one of the backends will actually appear to read anything from disk. Which one is pretty much luck. Adding something like this would require instrumenting not only the backends' disk I/O code, but also their shared buffer access code. It'd potentially add a lot of overhead, and I'm not sure the results would mean very much because the caching effects would make "fred's backend did X I/O operations" less meaningful. Fred's might just have been the first that ran after Joe's giant seqscan cross join of two big tables that forced everything else out of shared_buffers. Even if you did have that instrumentation, you'd need OS-level instrumentation (dtrace, prof, etc) to track the OS's buffer cache, which PostgreSQL relies on heavily. Without that you can't tell the difference between a query that caused I/O calls from postgresql but could be satisfied by OS buffer cache and one that required expensive physical disk I/O to satisfy. Really, I don't know if it's realistic to do what you're suggesting unless every user's data set is isolated from every other user's, in which case you can maybe use OS-level tools like prof or DTrace to achieve it if you set postgresql up to log whichstarts which backend pid. If there's any significant overlap in the data sets used by the users (if they use the same databases or schema) I wouldn't think you'd be able to get any meaningful results. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote: > On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote: > > How can you ensure the snapshot is in a consistent state if the server is > > running? > > > > If a snapshot is taken between 2 updates in a single transaction, only > > half of this transaction is included in the snapshot. > > I would never take an LVM (or similar) snapshot of an application that > > can't be paused in a way to provide a consistent filesystem. > > That's the trick, the filesystem is always in a consistant state, > otherwise how could a database survive a power failure? This is something you want to try to avoid. Recovery situations are not always reliable. > The trick is WAL, which ensure that changes are logged consistantly and > replays them if the database crashes. > > If you take a snapshot the database will simply startup and replay the > log as if the machine crashed at the point. All committed transactions > appears anything uncommitted vanishes. Nice in theory. Except backups can not be fully trusted if they rely on database recovery mechanics as part of the restore process. How certain can you be that the data you have in your backup will always result to being able to recover 100%? -- Joost -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sun, Sep 12, 2010 at 7:39 AM, J. Roeleveld wrote: > On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote: >> On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote: >> > How can you ensure the snapshot is in a consistent state if the server is >> > running? >> > >> > If a snapshot is taken between 2 updates in a single transaction, only >> > half of this transaction is included in the snapshot. >> > I would never take an LVM (or similar) snapshot of an application that >> > can't be paused in a way to provide a consistent filesystem. >> >> That's the trick, the filesystem is always in a consistant state, >> otherwise how could a database survive a power failure? > > This is something you want to try to avoid. > Recovery situations are not always reliable. When hardware doesn't fsync properly, this can be a problem. >> The trick is WAL, which ensure that changes are logged consistantly and >> replays them if the database crashes. >> >> If you take a snapshot the database will simply startup and replay the >> log as if the machine crashed at the point. All committed transactions >> appears anything uncommitted vanishes. > > Nice in theory. > Except backups can not be fully trusted if they rely on database recovery > mechanics as part of the restore process. Why? > How certain can you be that the data you have in your backup will always > result to being able to recover 100%? 100% certain if you test said restores often. It's not uncommon to use this method to bring up a slave which you then run pg_dump on to see if you get any errors. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
J. Roeleveld wrote: > On Sunday 12 September 2010 13:32:00 Martijn van Oosterhout wrote: > > On Sun, Sep 12, 2010 at 12:18:10PM +0200, J. Roeleveld wrote: > > > How can you ensure the snapshot is in a consistent state if the server is > > > running? > > > > > > If a snapshot is taken between 2 updates in a single transaction, only > > > half of this transaction is included in the snapshot. > > > I would never take an LVM (or similar) snapshot of an application that > > > can't be paused in a way to provide a consistent filesystem. > > > > That's the trick, the filesystem is always in a consistant state, > > otherwise how could a database survive a power failure? > > This is something you want to try to avoid. > Recovery situations are not always reliable. With Postgres, they are always reliable, unless your hardware isn't. > > The trick is WAL, which ensure that changes are logged consistantly and > > replays them if the database crashes. > > > > If you take a snapshot the database will simply startup and replay the > > log as if the machine crashed at the point. All committed transactions > > appears anything uncommitted vanishes. > > Nice in theory. > Except backups can not be fully trusted if they rely on database recovery > mechanics as part of the restore process. > How certain can you be that the data you have in your backup will always > result to being able to recover 100%? If you don't want to trust our recovery, that is your decision. We are telling you others do trust it. If you don't trust our crash recovery, perhaps you shouldn't trust Postgres and consider another database that you trust more. I am afraid you have used some untrustworth databases in your past --- Postgres is not one of them. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sun, Sep 12, 2010 at 7:51 AM, Bruce Momjian wrote: > J. Roeleveld wrote: >> Nice in theory. >> Except backups can not be fully trusted if they rely on database recovery >> mechanics as part of the restore process. > >> How certain can you be that the data you have in your backup will always >> result to being able to recover 100%? > > If you don't want to trust our recovery, that is your decision. We are > telling you others do trust it. Agreed. If you can't trust your database to recover from a power failure, you can't trust your database, period. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monitoring Object access
Hi Craig, Thanks a lot for the quick response! I'm coming from the Oracle side of the house and In oracle for instance, you use shared buffer as well, but you are still able to see which session is waiting for which blocks and if one session is doing the "real" I/O then the other one wait on 'wait for other session" event so you are able to know who did the actual I/O the reason behind it is that you want to check which objects is being heavily hit by which business processes or users and then tier your storage accordingly. I agree with your point about the OS buffer cache, I need to monitor it as well. is there any place rather than pg_stat_activity that you think I should take a look at? Best Regard, Adi On Sun, Sep 12, 2010 at 4:04 PM, Craig Ringer wrote: > On 09/12/2010 06:52 PM, adi hirschtein wrote: > >> Hi, >> Using the catalog tables, is there any way to correlate session id/user >> id to which object (i.e. tables, indexes etc) it access and much how >> disk reads or I/O wait has been done against the objects. >> in general, I'd like to see which objects are being accessed by which >> user and the time/amount of I/O wait/reads. >> > > There isn't really anything like that, no. You have pg_stat_activity, but > it's a pretty coarse tool. > > The shared buffer cache and the use of things like synchronized sequential > scans means that it wouldn't even be possible to truly track who's causing > load reliably. As I understand it, if Joe and Fred both to a "SELECT * FROM > sometable", it's quite likely that only one of the backends will actually > appear to read anything from disk. Which one is pretty much luck. > > Adding something like this would require instrumenting not only the > backends' disk I/O code, but also their shared buffer access code. It'd > potentially add a lot of overhead, and I'm not sure the results would mean > very much because the caching effects would make "fred's backend did X I/O > operations" less meaningful. Fred's might just have been the first that ran > after Joe's giant seqscan cross join of two big tables that forced > everything else out of shared_buffers. > > Even if you did have that instrumentation, you'd need OS-level > instrumentation (dtrace, prof, etc) to track the OS's buffer cache, which > PostgreSQL relies on heavily. Without that you can't tell the difference > between a query that caused I/O calls from postgresql but could be satisfied > by OS buffer cache and one that required expensive physical disk I/O to > satisfy. > > Really, I don't know if it's realistic to do what you're suggesting unless > every user's data set is isolated from every other user's, in which case you > can maybe use OS-level tools like prof or DTrace to achieve it if you set > postgresql up to log whichstarts which backend pid. > > If there's any significant overlap in the data sets used by the users (if > they use the same databases or schema) I wouldn't think you'd be able to get > any meaningful results. > > -- > Craig Ringer >
Re: [GENERAL] why can't see the updated value after SPI_execute("update ....", false, 1);
sunpeng writes:
> First I use SPI_execute("update
> bool succ;
>SPI_connect();
> int ret = SPI_execute("update where uid = 1", false, 1);//later will
> add error processing
> if (ret == SPI_OK_UPDATE && SPI_processed == 1) {
> succ = true;
>}
>SPI_finish();
> Then I use SPI_execute("select .where uid = 1",
> SPI_connect();
> int ret = SPI_execute("select .where uid = 1", true, 1);//later will
> add error processing
> if (ret == SPI_OK_SELECT && SPI_processed == 1) {
>HeapTuple tuple;
>tuple = SPI_tuptable->vals[0];
> ...
>datum = heap_getattr(tuple,attrno,SPI_tuptable->tupdesc,&isnull);
>
> }
> the datum doesn't change, why ?
You're missing a CommandCounterIncrement() call between the two actions.
If you hadn't passed read_only = true to the second SPI_execute, it
would have done one for you.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgcrypto pgp_pub_decrypt() fails with secret key password
Hi,
I have pgcrypto working fine with gpg keys that do *not* have a passphrase.
But,
if I try the exact same gpg -a --export commands and application code with gpg
keys that are generated *with* a passphrase, then I get the following
pgp_pub_decrypt() error within psql:
\set pubkey `sed -e "s/'/''/g" -e 's/\\n/\\r\\n/g' -e 's/\\//g' <
test-public.key`
\set prvkey `sed -e "s/'/''/g" -e 's/\\n/\\r\\n/g' -e 's/\\//g' <
test-secret.key`
SELECT pgp_pub_decrypt( dearmor( (SELECT armor((SELECT
pgp_pub_encrypt('mypass',
dearmor(:pubkey) ), dearmor(:prvkey), 'test' );
ERROR: Corrupt data
Here are my versions:
PostgreSQL version: 8.4.4
GPG version: 1.4.10
(both were built with OpenSSL support)
I've verified my SQL code via psql and I feel pretty confident it's OK, since I
have it narrowed down to where the only difference is whether the gpg exported
keys were originally assigned with a passphrase, or not assigned a passphrase
(just press enter when prompted), when running "gpg --gen-key".
Any troubleshooting help or suggestions would be greatly appreciated!
Thanks, -Eric
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Huge amount of memory errors with libpq
On Sun, Sep 12, 2010 at 7:54 AM, Craig Ringer wrote: > > Anyway, since you've provided a test program, I can at least run it here on > a modern PostgreSQL and see what results I get to provide some more info. In > this case, it runs fine and no issues are detected. I'm on a 64-bit Fedora > 13 install with glibc 2.12.3 and postgresql 9.0rc1 , so it's not exactly a > close match for your system. It is a Core 2 Duo, so it's SSE3 capable > hardware as confirmed by /proc/cpuinfo. I'm using valgrind 3.5.0 . > I use a AMD Athlon II X4. It's based off the new Phenom II's, so it certainly supports SSE3 and SSE4a as well. > ... to see if it, too, reports errors from valgrind. It doesn't here, of > course (though interestingly strcmp returns 1 under valgrind and 17 outside > it); I'd like to see what your results are. I get 17 as a result with or without valgrind. And I don't get any memory errors. ==23894== Memcheck, a memory error detector ==23894== Copyright (C) 2002-2009, and GNU GPL'd, by Julian Seward et al. ==23894== Using Valgrind-3.5.0 and LibVEX; rerun with -h for copyright info ==23894== Command: ./a.out ==23894== Comparison: 17 ==23894== ==23894== HEAP SUMMARY: ==23894== in use at exit: 16 bytes in 1 blocks ==23894== total heap usage: 1 allocs, 0 frees, 16 bytes allocated ==23894== ==23894== 16 bytes in 1 blocks are definitely lost in loss record 1 of 1 ==23894==at 0x4C260AE: malloc (vg_replace_malloc.c:195) ==23894==by 0x4EA8B41: strdup (in /lib64/libc-2.12.1.so) ==23894==by 0x40061C: main (in /home/casey/kwooty/Download/a.out) ==23894== ==23894== LEAK SUMMARY: ==23894==definitely lost: 16 bytes in 1 blocks ==23894==indirectly lost: 0 bytes in 0 blocks ==23894== possibly lost: 0 bytes in 0 blocks ==23894==still reachable: 0 bytes in 0 blocks ==23894== suppressed: 0 bytes in 0 blocks ==23894== ==23894== For counts of detected and suppressed errors, rerun with: -v ==23894== ERROR SUMMARY: 1 errors from 1 contexts (suppressed: 6 from 6) This bug from Gentoo may be related, but I thought I had worked around it. http://bugs.gentoo.org/show_bug.cgi?id=274771 It says to compile glibc with splitdebug, which I have and it got me past a fatal error in valgrind. But it does mention sse-optimized strlen(). I just checked an older program I had written, and I'm getting tons of errors on that too. Just a few months ago I had it down to just a couple of errors. Now I'm seeing lots of errors ending at __strncmp_ssse3. I don't think valgrind is the only issue here because outside valgrind my data is getting magically overwritten. In the function causing that problem I set all the fields I wanted to set by hand instead of using PQgetvalue(). If I leave PQexec() uncommented, my data in a totally unrelated area would change, but when I comment it out I get the expected results. There might be an error I'm making thats causing this, but I can't find it in valgrind because of the huge number of errors.
Re: [GENERAL] why can't see the updated value after SPI_execute("update ....", false, 1);
I then added , yet it still doesn't work.
2010/9/12 Tom Lane
> sunpeng writes:
> > First I use SPI_execute("update
> > bool succ;
> >SPI_connect();
> > int ret = SPI_execute("update where uid = 1", false, 1);//later
> will
> > add error processing
> > if (ret == SPI_OK_UPDATE && SPI_processed == 1) {
> > succ = true;
> >}
> >SPI_finish();
>
> > Then I use SPI_execute("select .where uid = 1",
>
> > SPI_connect();
> > int ret = SPI_execute("select .where uid = 1", true, 1);//later
> will
> > add error processing
> > if (ret == SPI_OK_SELECT && SPI_processed == 1) {
> >HeapTuple tuple;
> >tuple = SPI_tuptable->vals[0];
> > ...
> >datum =
> heap_getattr(tuple,attrno,SPI_tuptable->tupdesc,&isnull);
> >
> > }
> > the datum doesn't change, why ?
>
> You're missing a CommandCounterIncrement() call between the two actions.
> If you hadn't passed read_only = true to the second SPI_execute, it
> would have done one for you.
>
>regards, tom lane
>
Re: [GENERAL] Huge amount of memory errors with libpq
Casey Jones writes: > I don't think valgrind is the only issue here because outside valgrind my > data is getting magically overwritten. In the function causing that problem > I set all the fields I wanted to set by hand instead of using PQgetvalue(). > If I leave PQexec() uncommented, my data in a totally unrelated area would > change, but when I comment it out I get the expected results. There might > be an error I'm making thats causing this, but I can't find it in valgrind > because of the huge number of errors. FWIW, that test case shows no errors at all for me, on an x86_64 running Fedora 13. I'd suggest trying it on something other than Gentoo. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query plan choice issue
Hi all,
I'm seeing a funny behaviour in Postgresql 8.4.4. Namely, a query can
be executed using either of two different query plans, one taking a
few milliseconds and the other, tens of seconds. The work_mem setting
doesn't seem to affect it -- tried to increase or decrease it by 2 or
4 times, but it didn't seem to favour the fast plan choice. Honestly,
I have no idea what affects the plan choice, but I saw Postgresql
change it at random.
The query in question looks like this -- sorry, it's rather complex:
SELECT message_idnr
FROM dbmail_messages m
JOIN dbmail_physmessage p ON m.physmessage_id=p.id
JOIN dbmail_headervalue v ON v.physmessage_id=p.id
JOIN dbmail_headername n ON v.headername_id=n.id
WHERE mailbox_idnr = 12345 AND status IN (0,1) AND
headername ILIKE 'MESSAGE-ID' AND SUBSTRING(headervalue,0,255)
ILIKE '%<@mail.gmail.com>%'
ORDER BY message_idnr;
It comes from DBMail. That said, I don't think DBMail can be blamed
here because at least sometimes Postgresql is able to do the right
thing with respect to that query.
Here is the slow plan:
QUERY
PLAN
--
Sort (cost=1234610.11..1234610.11 rows=1 width=8) (actual
time=20933.166..20933.168 rows=1 loops=1)
Sort Key: m.message_idnr
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..1234610.10 rows=1 width=8) (actual
time=3327.658..20933.122 rows=1 loops=1)
-> Nested Loop (cost=0.00..1234601.59 rows=1 width=24)
(actual time=3327.599..20933.056 rows=1 loops=1)
-> Nested Loop (cost=0.00..1234549.74 rows=6 width=8)
(actual time=3327.503..20932.944 rows=1 loops=1)
Join Filter: (v.headername_id = n.id)
-> Seq Scan on dbmail_headername n
(cost=0.00..108.46 rows=1 width=8) (actual time=0.027..0.985 rows=1
loops=1)
Filter: ((headername)::text ~~* 'MESSAGE-
ID'::text)
-> Seq Scan on dbmail_headervalue v
(cost=0.00..1234407.96 rows=2666 width=16) (actual
time=3327.465..20931.942 rows=1 loops=1)
Filter: ("substring"(v.headervalue, 0,
255) ~~* '%<@mail.gmail.com>%'::text)
-> Index Scan using dbmail_messages_physmessage_idx on
dbmail_messages m (cost=0.00..8.63 rows=1 width=16) (actual
time=0.088..0.100 rows=1 loops=1)
Index Cond: (m.physmessage_id = v.physmessage_id)
Filter: ((m.status = ANY ('{0,1}'::integer[]))
AND (m.mailbox_idnr = 12345))
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..8.50 rows=1 width=8) (actual
time=0.048..0.050 rows=1 loops=1)
Index Cond: (p.id = m.physmessage_id)
Total runtime: 20933.241 ms
(17 rows)
And here is the fast plan:
QUERY
PLAN
---
Sort (cost=1175284.93..1175284.94 rows=1 width=8) (actual
time=14.163..14.165 rows=1 loops=1)
Sort Key: m.message_idnr
Sort Method: quicksort Memory: 25kB
-> Nested Loop (cost=0.00..1175284.92 rows=1 width=8) (actual
time=4.272..14.152 rows=1 loops=1)
-> Nested Loop (cost=0.00..1175276.41 rows=1 width=24)
(actual time=4.261..14.135 rows=1 loops=1)
-> Nested Loop (cost=0.00..1175268.46 rows=1 width=32)
(actual time=4.249..14.117 rows=1 loops=1)
-> Index Scan using dbmail_messages_mailbox_idx
on dbmail_messages m (cost=0.00..4153.35 rows=786 width=16) (actual
time=0.043..2.810 rows=358 loops=1)
Index Cond: (mailbox_idnr = 12345)
Filter: (status = ANY ('{0,1}'::integer[]))
-> Index Scan using dbmail_headervalue_2 on
dbmail_headervalue v (cost=0.00..1489.96 rows=1 width=16) (actual
time=0.028..0.029 rows=0 loops=358)
Index Cond: (v.physmessage_id =
m.physmessage_id)
Filter: ("substring"(v.headervalue, 0,
255) ~~* '%<@mail.gmail.com>%'::text)
-> Index Scan using dbmail_headername_pkey on
dbmail_headername n (cost=0.00..7.94 rows=1 width=8) (actual
time=0.007..0.008 rows=1 loops=1)
Index Cond: (n.id = v.headername_id)
Filter: ((n.headername)::text ~~* 'MESSAGE-
ID'::text)
-> Index Scan using dbmail_physmessage_pkey on
dbmail_physmessage p (cost=0.00..8.50 rows=1 width=8) (actual
time=0.006..0.007 rows=1 loops=1)
Index Cond: (p.id = m.physmessage_id)
Total runtime: 14.231 ms
(18 row
Re: [GENERAL] Query plan choice issue
a cursory look of the plan details a FTS on dbmail_headername invoked by the
JOIN clause
JOIN dbmail_headername n ON v.headername_id=n.id
you would accelerate the seek appreciably by placing indexes on both
participating columns
v.headername_id
n.id
I also see a FTS on domain_headervalue invoked by the JOIN cluase
JOIN dbmail_headervalue v ON v.physmessage_id=p.id
place indexes on both columns
v.physmessage_id
p.id
the join clause JOIN dbmail_physmessage p ON m.physmessage_id=p.id uses indexed
for both participants
Martin
__
Verzicht und Vertraulichkeitanmerkung
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger
sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung
oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem
Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung.
Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung
fuer den Inhalt uebernehmen.
> From: y...@barnet.com.au
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Query plan choice issue
> Date: Mon, 13 Sep 2010 09:36:35 +1000
>
> Hi all,
>
> I'm seeing a funny behaviour in Postgresql 8.4.4. Namely, a query can
> be executed using either of two different query plans, one taking a
> few milliseconds and the other, tens of seconds. The work_mem setting
> doesn't seem to affect it -- tried to increase or decrease it by 2 or
> 4 times, but it didn't seem to favour the fast plan choice. Honestly,
> I have no idea what affects the plan choice, but I saw Postgresql
> change it at random.
>
> The query in question looks like this -- sorry, it's rather complex:
>
> SELECT message_idnr
> FROM dbmail_messages m
> JOIN dbmail_physmessage p ON m.physmessage_id=p.id
> JOIN dbmail_headervalue v ON v.physmessage_id=p.id
> JOIN dbmail_headername n ON v.headername_id=n.id
> WHERE mailbox_idnr = 12345 AND status IN (0,1) AND
> headername ILIKE 'MESSAGE-ID' AND SUBSTRING(headervalue,0,255)
> ILIKE '%<@mail.gmail.com>%'
> ORDER BY message_idnr;
>
> It comes from DBMail. That said, I don't think DBMail can be blamed
> here because at least sometimes Postgresql is able to do the right
> thing with respect to that query.
>
> Here is the slow plan:
> QUERY
> PLAN
> --
> Sort (cost=1234610.11..1234610.11 rows=1 width=8) (actual
> time=20933.166..20933.168 rows=1 loops=1)
> Sort Key: m.message_idnr
> Sort Method: quicksort Memory: 25kB
> -> Nested Loop (cost=0.00..1234610.10 rows=1 width=8) (actual
> time=3327.658..20933.122 rows=1 loops=1)
> -> Nested Loop (cost=0.00..1234601.59 rows=1 width=24)
> (actual time=3327.599..20933.056 rows=1 loops=1)
> -> Nested Loop (cost=0.00..1234549.74 rows=6 width=8)
> (actual time=3327.503..20932.944 rows=1 loops=1)
> Join Filter: (v.headername_id = n.id)
> -> Seq Scan on dbmail_headername n
> (cost=0.00..108.46 rows=1 width=8) (actual time=0.027..0.985 rows=1
> loops=1)
> Filter: ((headername)::text ~~* 'MESSAGE-
> ID'::text)
> -> Seq Scan on dbmail_headervalue v
> (cost=0.00..1234407.96 rows=2666 width=16) (actual
> time=3327.465..20931.942 rows=1 loops=1)
> Filter: ("substring"(v.headervalue, 0,
> 255) ~~* '%<@mail.gmail.com>%'::text)
> -> Index Scan using dbmail_messages_physmessage_idx on
> dbmail_messages m (cost=0.00..8.63 rows=1 width=16) (actual
> time=0.088..0.100 rows=1 loops=1)
> Index Cond: (m.physmessage_id = v.physmessage_id)
> Filter: ((m.status = ANY ('{0,1}'::integer[]))
> AND (m.mailbox_idnr = 12345))
> -> Index Scan using dbmail_physmessage_pkey on
> dbmail_physmessage p (cost=0.00..8.50 rows=1 width=8) (actual
> time=0.048..0.050 rows=1 loops=1)
> Index Cond: (p.id = m.physmessage_id)
> Total runtime: 20933.241 ms
> (17 rows)
>
> And here is the fast plan:
> QUERY
> PLAN
> ---
> Sort (cost=1175284.93..1175284.94 rows=1 width=8) (actual
> time=14.163..14.165 rows=1 loops=1)
> Sort Key: m.message_idnr
> Sort Method: quicksort Memory: 25kB
> -> Nested Loop (cost=0.00..1175284.92 rows=1 width=8) (actual
> time=4.272..14.152 rows=1 loops=1)
> -> Nested Loop (cost=0.00..1175276.41 rows=1 width=24)
> (actual time=4.261..14.135 rows=1 loops=1)
> -> Nested Loop (cost=0.00..1175268.46 rows=1 width=32)
> (actual time=4.249..14.117 rows=1 loops=1)
> -> Index Scan using dbmail_messages_mailbox_idx
> on dbmail_messages m (cost=0.00..4153.35 rows=786 width=16) (actual
> time=0.043..2.810 rows=358 loops=1)
> Index Cond: (mailbox_idnr = 12345)
> Filter: (status = ANY ('{0,1}'::integer[]))
> -> Index Scan using dbmail_headervalue_2 on
> dbmail_headervalue v (cost=0.0
[GENERAL] Schema search path
Hi there, Sorry but I've got yet another issue to discuss today, this time that on schema search path. In fact it may not be a bug, but it may be worth a note in the documentation. It seems that if the table in SELECT FROM has an explicit schema specifier, further references to the same table name will implicitly inherit it. E.g., this query will be valid because the second reference will be to foo.bar not public.bar: SELECT * FROM foo.bar WHERE bar.a=1; ^^^ this means foo.bar Here is a more complex case where I initially came across this issue: psql (8.4.4) Type "help" for help. pgsql=# show search_path; search_path "$user",public (1 row) pgsql=# create table public.tbl_bar (a int); CREATE TABLE pgsql=# create schema sch_foo; CREATE SCHEMA pgsql=# create table sch_foo.tbl_bar (a int); CREATE TABLE pgsql=# insert into public.tbl_bar (a) values (1); INSERT 0 1 pgsql=# insert into sch_foo.tbl_bar (a) values (2); INSERT 0 1 pgsql=# select a from tbl_bar where not exists (select a from sch_foo.tbl_bar where tbl_bar.a=sch_foo.tbl_bar.a); a --- (0 rows) pgsql=# select a from tbl_bar where not exists (select a from sch_foo.tbl_bar where public.tbl_bar.a=sch_foo.tbl_bar.a); a --- 1 (1 row) As just shown, this can be even more confusing with nested queries. Do you think it's a feature or a bug? :-) Thanks! Yar -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Monitoring Object access
On 09/12/2010 10:02 PM, adi hirschtein wrote: Hi Craig, Thanks a lot for the quick response! I'm coming from the Oracle side of the house and In oracle for instance, you use shared buffer as well, but you are still able to see which session is waiting for which blocks and if one session is doing the > "real" I/O then the other one wait on 'wait for other session" > event so you are able to know who did the actual I/O There's nothing like that in PostgreSQL. There's some lock monitoring support for seeing what transactions hold locks and which other transactions are waiting on those locks, but AFAIK nothing like that for I/O. PostgreSQL does have DTrace hooks, so if you're on Solaris or some BSDs you might be able to use those to get the data you want. It'd be a pretty significant job to add a decent I/O monitoring system to PostgreSQL. Personally, if I needed something like that, I'd want to base it on an existing system-level tracing toolkit like Solaris's DTrace or Linux's "perf". I'd want to add some additional instrumentation hooks - some of which already exist in Pg for DTrace - to permit the tools to beaware of transactions, statements, the current database, which tables are which, which indexes are associated with which tables, etc. Then I'd use the data collected by the performance monitoring tools to report on load associated with particular users, indexes, tables, queries, etc. That way I'd be able to handle things like whether a request was satisfied with OS buffer cache or had to go to real disk, report on disk queue depth, etc as part of the whole system. It'd be a big job even with the use of existing trace tools to help. Currently there are some DTrace hooks, but I don't think there's any kind of integrated toolset like I've described to use the monitoring hooks plus the existing system hooks to do detailed reporting of load/user, load/tablespace, etc. the reason behind it is that you want to check which objects is being heavily hit by which business processes or users and then tier your storage accordingly. At the moment, all you can really do is turn up the logging levels to log queries, logins, etc. Then watch pg_stat_activity and use system-level tools like iostat, vmstat, top, perf/dtrace, etc. If you see backends that're hogging resources you can look their pid up in pg_stat_activity or the logs, see what they were doing, and run controlled tests to see what can be improved. It's somewhat clumsy, but seems to work pretty well most of the time. Nobody has stepped up to build a comprehensive tracing and performance framework - and even if they did, they'd have to make it lightweight enough that it didn't slow PostgreSQL down when it wasn't in use, show that it wouldn't add an excessive maintenance burden for the developers, show that it wouldn't break or produce incorrect results the first time something changed, etc. The Linux kernel demonstrates just how hard getting this right can be. So does the amount of effort Sun put in to DTrace. Sure, PostgreSQL isn't an OS kernel, but it's far from simple. I guess that's why Oracle charges the big bucks - because of all the extras they include that round the database out into the kitchen-sink monster that it is. is there any place rather than pg_stat_activity that you think I should take a look at? System-level tools and the postgresql logs, especially after proper configuration. There are some tools on pgfoundry that help a little with log analysis. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] JSON output
I'm trying to import from a postgres database (which will work in parallel) to a ElasticSearch databse (JSON input). Is there anyway to get JSON output from postgres? (googled, found only 'planner' output does this) Dennis Gearon Signature Warning EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Change the UI language of psql connection
I have a PostgreSQL 8.4 database, installed with Japanese as the main language. I am connecting from an English Windows 7 client using psql.exe. Is there any way to change the UI of the messages shown to me (e.g. the help, column headers from the \l command, etc) to English? I know that I can set the character encoding using \encoding, but this is not the same. I want to change the UI language from ja_JP to en_US (or similar). I can't see anything in the psql documentation. Any ideas? Regards, Brodie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change the UI language of psql connection
Brodie Thiesfield writes: > I have a PostgreSQL 8.4 database, installed with Japanese as the main > language. I am connecting from an English Windows 7 client using > psql.exe. Is there any way to change the UI of the messages shown to > me (e.g. the help, column headers from the \l command, etc) to > English? set lc_messages to an appropriate locale. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JSON output
On 13/09/10 10:36, Dennis Gearon wrote: > I'm trying to import from a postgres database (which will work in parallel) > to a ElasticSearch databse (JSON input). > > Is there anyway to get JSON output from postgres? Not out of the box. The closest you'll get, AFAIK, is XML output from the SQLXML functions. http://www.postgresql.org/docs/current/static/datatype-xml.html http://www.postgresql.org/docs/current/static/functions-xml.html In particular, table_to_xml, query_to_xml or cursor_to_xml will probably be helpful. Once you have the XML, you can use any existing tool for an XML-to-JSON transform, possibly in a plpython/plperl stored procedure within PostgreSQL its self. classads=> select * FROM table_to_xml( 'ad_status'::regclass, true, false, ''); table_to_xml --- http://www.w3.org/2001/XMLSchema-instance";> 0 OK 1 HELD 2 CANCELLED If you need something more complex than table contents or the end result of a query, you'll have to roll your own. There isn't anything to do ORM-like extraction of join results into nested sets if you need that. -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Help! pg_dump: Error message from server: ERROR: cache lookup failed for type 19
On 11/09/10 14:21, 夏武 wrote: > i use the slony for replication of postgresql database. it work fine > some day. > After i use the slony command to delete the replication node, pg_dump > does not work, the error message is: > *pg_dump: schema with OID 73033 does not exist* > > Then i delete the name space of slony in pg_namespace, and pg_dump does > not work. > So i delete the data of the name space in pg_class, pg_type, the command is: > *DELETE FROM pg_class where relnamespace=73033* > *DELETE FROM pg_type where relnamespace=73033* That might not have been a very good idea. You're really not meant to go messing with the catalog unless you *really* know what you are doing. It sounds like you have a very badly messed up catalog. You will need to find a way to dump your database so you can drop it, re-create it and reload it. > i got the error message: > pg_dump: Error message from server: ERROR: cache lookup failed for type 19 > How to fix it? How to recovery the database? Your best chance is to do a pg_dump of each table, one by one, using the "--table" option. Find out which table or tables are failing. Once you know that, post here with the table definition, the exact error, and the output of "select * from pg_class" and "select * from pg_type". Somebody might be able to help you if there's enough information. Alternately, you might be able to extract the data from the problem table(s) using the \copy command from psql. Then you can re-create them in the new database and re-load their contents with \copy . -- Craig Ringer Tech-related writing: http://soapyfrogs.blogspot.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Change the UI language of psql connection
On Mon, Sep 13, 2010 at 12:33 PM, Tom Lane wrote: > Brodie Thiesfield writes: >> I have a PostgreSQL 8.4 database, installed with Japanese as the main >> language. I am connecting from an English Windows 7 client using >> psql.exe. Is there any way to change the UI of the messages shown to >> me (e.g. the help, column headers from the \l command, etc) to >> English? > > set lc_messages to an appropriate locale. Ah yes. I forgot about that. That fixed my problem, thanks. Brodie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Huge amount of memory errors with libpq
On Sunday 12 September 2010 5:44:26 pm you wrote: > Casey Jones writes: > > I don't think valgrind is the only issue here because outside valgrind my > > data is getting magically overwritten. In the function causing that > > problem I set all the fields I wanted to set by hand instead of using > > PQgetvalue(). > > > > If I leave PQexec() uncommented, my data in a totally unrelated area > > would > > > > change, but when I comment it out I get the expected results. There > > might be an error I'm making thats causing this, but I can't find it in > > valgrind because of the huge number of errors. > > FWIW, that test case shows no errors at all for me, on an x86_64 running > Fedora 13. I'd suggest trying it on something other than Gentoo. > > regards, tom lane I set up Fedora 13 and ran the test case, and I didn't get any errors. I also tested my project and it had significantly fewer errors. So yeah, it looks like a glibc problem on Gentoo. Thanks for the help everyone. -- Casey Jones -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] JSON output
No, don't need all the set stuff, just the result of either: 1/ A SELECT * from a view that is a query, 2/ Or the result of the same query. Seems like it'd take a lot of horsepower to: A/ Do the query B/ Convert it to XML C/ Convert it to JSON D/ Then insert it into ElasticSearch. I'm either going to use C++/Perl/PHP/JAVA doing a simple query to a result array, then convert it and send it to ElasticSearch (via a http socket) I'll start with PHP. Probably going to have to use a cursor (one more thing to learn) as eventually, I'll have 'billions and billlions' of records, Mwah Ha Ha Dennis Gearon Signature Warning EARTH has a Right To Life, otherwise we all die. Read 'Hot, Flat, and Crowded' Laugh at http://www.yert.com/film.php --- On Sun, 9/12/10, Craig Ringer wrote: > From: Craig Ringer > Subject: Re: [GENERAL] JSON output > To: "Dennis Gearon" , "PG-General Mailing List" > > Date: Sunday, September 12, 2010, 8:57 PM > On 13/09/10 10:36, Dennis Gearon > wrote: > > I'm trying to import from a postgres database (which > will work in parallel) to a ElasticSearch databse (JSON > input). > > > > Is there anyway to get JSON output from postgres? > > Not out of the box. > > The closest you'll get, AFAIK, is XML output from the > SQLXML functions. > > http://www.postgresql.org/docs/current/static/datatype-xml.html > http://www.postgresql.org/docs/current/static/functions-xml.html > > In particular, table_to_xml, query_to_xml or cursor_to_xml > will probably > be helpful. Once you have the XML, you can use any existing > tool for an > XML-to-JSON transform, possibly in a plpython/plperl stored > procedure > within PostgreSQL its self. > > > > classads=> select * FROM table_to_xml( > 'ad_status'::regclass, true, > false, ''); > > > table_to_xml > --- > http://www.w3.org/2001/XMLSchema-instance";> > > > 0 > OK > > > > 1 > HELD > > > > 2 > CANCELLED > > > > > > > > > > If you need something more complex than table contents or > the end result > of a query, you'll have to roll your own. There isn't > anything to do > ORM-like extraction of join results into nested sets if you > need that. > > -- > Craig Ringer > > Tech-related writing: http://soapyfrogs.blogspot.com/ > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Incrementally Updated Backups
On Sun, 2010-09-12 at 12:18 +0200, J. Roeleveld wrote: > On Sunday 12 September 2010 00:43:19 Bruce Momjian wrote: > > Gabe Nell wrote: > > > > That section has been removed from the current 9.0 docs because we are > > > > unsure it works. > > > > > > Hmm. So the only way to make a consistent backup from a standby server > > > is to shut down the standby first? Or is even that problematic? > > > > > > Would it change anything if we are able to guarantee that the > > > filesystem is snapshotted as a point-in-time snapshot by using LVM or > > > on Amazon EC2 by snapshotting an EBS volume? > > > > I believe a snapshot of the standby is fine even if it is running, just > > like on the master. > > How can you ensure the snapshot is in a consistent state if the server is > running? > > If a snapshot is taken between 2 updates in a single transaction, only half > of > this transaction is included in the snapshot. > I would never take an LVM (or similar) snapshot of an application that can't > be paused in a way to provide a consistent filesystem. You need to read up on things like WAL and MVCC. Joshua D. Drake > > -- > Joost > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
