Re: [GENERAL] Copy/delete issue
Adrian Klaver wrote: > > > Are you sure the problem is not in "$datefield" = "*" . That the script that > formats the data file is not correctly adding "*" to the right file. Seems > almost like sometimes the second CMD is being run against the table that the > first CMD should be run on. In other words it is not doing a complete > delete , but a date based one, and you then import duplicate records. > > Thanks for your reply. The file containing the tables list is static - it doesn't change from one run to the next (unless I edit it personally). Herouth -- 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] Copy/delete issue
(Sorry for the forward, I forgot to CC the list) On Wed, Dec 17, 2008 at 9:38 AM, Herouth Maoz wrote: > and for non-transaction tables (ones that have records that might > change but don't accumulate based on time) it's DELETE without WHERE. In that case, you are better off using TRUNCATE instead. -- 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] Copy/delete issue
On Sunday 21 December 2008 1:49:18 am Herouth Maoz wrote: > Adrian Klaver wrote: > > > > > > Are you sure the problem is not in "$datefield" = "*" . That the script > > that formats the data file is not correctly adding "*" to the right file. > > Seems almost like sometimes the second CMD is being run against the table > > that the first CMD should be run on. In other words it is not doing a > > complete delete , but a date based one, and you then import duplicate > > records. > > Thanks for your reply. The file containing the tables list is static - > it doesn't change from one run to the next (unless I edit it personally). > > Herouth Well something is not static :) You mentioned this happens only with one table. Have you tried running your procedure against that table only? Just because a DELETE did not error does not mean it succeeded in the way you wanted. You might want to throw a count() in the mix to see if you are really clearing out the table the way you want to. Also is the actual data file static from one run to the next? Would also help to see the schema for the table involved and maybe a sample of the data, if that is possible. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Tracking down log segment corruption
Howdy, all.
I have a log-shipping replication environment (based on PostgreSQL
8.3.4) using pg_lesslog+LZOP for compression of archived segments (kept
around long-term for possible use doing PITR). The slave came out of
synchronization recently, restoring a series of segments and then
failing with a SIGABORT (and doing the same after each automated restart):
[2-1] LOG: starting archive recovery
[3-1] LOG: restore_command = '/opt/extropy/postgres/bin/restore-segment
/exports/pgwal/segments.recoveryq %f %p %r'
[4-1] LOG: restored log file "00010014000B" from archive
[5-1] LOG: automatic recovery in progress
[6-1] LOG: restored log file "000100140009" from archive
[7-1] LOG: redo starts at 14/9127270
[8-1] LOG: restored log file "00010014000A" from archive
[9-1] LOG: restored log file "00010014000B" from archive
[10-1] LOG: restored log file "00010014000C" from archive
[11-1] LOG: restored log file "00010014000D" from archive
[12-1] LOG: restored log file "00010014000E" from archive
[13-1] LOG: restored log file "00010014000F" from archive
[14-1] LOG: restored log file "000100140010" from archive
[15-1] WARNING: specified item offset is too large
[15-2] CONTEXT: xlog redo insert_upper: rel 1663/16384/17763; tid 2960/89
[16-1] PANIC: btree_insert_redo: failed to add item
[16-2] CONTEXT: xlog redo insert_upper: rel 1663/16384/17763; tid 2960/89
[1-1] LOG: startup process (PID 17310) was terminated by signal 6: Aborted
[2-1] LOG: aborting startup due to startup process failure
Replacing only 000100140010 with a pristine (never processed
with pg_compresslog) copy made no difference, but doing so for all of
the involved segments permitted the slave to pick up where it left off
and continue replaying.
It seems clear, then, that pg_lesslog was responsible in some way for
this corruption, and that there is some germane difference between the
original and { pg_compresslog | pg_decompresslog } version of one of the
involved segments. I've tried using xlogdump
[http://xlogviewer.projects.postgresql.org/] on both versions to look
for differing output, but it segfaults even with the "good" log segments.
Does anyone have suggestions as to how I should go about tracking down
the root cause of this issue?
Thanks!
--
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] How are locks managed in PG?
On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera wrote: >> Oracle on the other hand stores the lock information directly in the data >> block that is locked, thus the number of locks does not affect system >> performance (in terms of managing them). >> >> I couldn't find any description on which strategy PG applies. > > None of the above. We're smarter than everyone else. Which is why Oracle's locks are more scalable than PG's? -- Jonah H. Harris, Senior DBA myYearbook.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] How are locks managed in PG?
On Sun, Dec 21, 2008 at 08:46:15PM -0500, Jonah H. Harris wrote: > On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera > wrote: > >> Oracle on the other hand stores the lock information directly in > >> the data block that is locked, thus the number of locks does not > >> affect system performance (in terms of managing them). > >> > >> I couldn't find any description on which strategy PG applies. > > > > None of the above. We're smarter than everyone else. > > Which is why Oracle's locks are more scalable than PG's? You've been talking about your super-secret test which you allege, quite implausibly, I might add, to have Oracle (8i, even!) blowing PostgreSQL's doors off for weeks now. Put up, or shut up. Regards, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] How are locks managed in PG?
On Sun, Dec 21, 2008 at 9:42 PM, David Fetter wrote: > On Sun, Dec 21, 2008 at 08:46:15PM -0500, Jonah H. Harris wrote: >> On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera >> wrote: >> >> Oracle on the other hand stores the lock information directly in >> >> the data block that is locked, thus the number of locks does not >> >> affect system performance (in terms of managing them). >> >> >> >> I couldn't find any description on which strategy PG applies. >> > >> > None of the above. We're smarter than everyone else. >> >> Which is why Oracle's locks are more scalable than PG's? > > You've been talking about your super-secret test which you allege, > quite implausibly, I might add, to have Oracle (8i, even!) blowing > PostgreSQL's doors off for weeks now. > > Put up, or shut up. Same to the standard PG B.S. responses such as, "None of the above. We're smarter than everyone else." When's the last time Alvaro used or tuned Oracle? Does he have a clue about how Oracle locks scale? Stop complaining. -- Jonah H. Harris, Senior DBA myYearbook.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] How are locks managed in PG?
On Sun, Dec 21, 2008 at 8:48 PM, Jonah H. Harris wrote: > On Sun, Dec 21, 2008 at 9:42 PM, David Fetter wrote: >> On Sun, Dec 21, 2008 at 08:46:15PM -0500, Jonah H. Harris wrote: >>> On Fri, Dec 19, 2008 at 7:49 AM, Alvaro Herrera >>> wrote: >>> >> Oracle on the other hand stores the lock information directly in >>> >> the data block that is locked, thus the number of locks does not >>> >> affect system performance (in terms of managing them). >>> >> >>> >> I couldn't find any description on which strategy PG applies. >>> > >>> > None of the above. We're smarter than everyone else. >>> >>> Which is why Oracle's locks are more scalable than PG's? >> >> You've been talking about your super-secret test which you allege, >> quite implausibly, I might add, to have Oracle (8i, even!) blowing >> PostgreSQL's doors off for weeks now. >> >> Put up, or shut up. > > Same to the standard PG B.S. responses such as, "None of the above. > We're smarter than everyone else." When's the last time Alvaro used > or tuned Oracle? Does he have a clue about how Oracle locks scale? > Stop complaining. The difference is HE put forth an opinion about the pg developers being smarter, but you put forth what seems like a statement of fact with no evidence to back it up. One is quite subjective and open for debate on both sides, and often to good effect. The other is a statement of fact regarding scalability in apparently all usage circumstances, since it wasn't in any way clarified if you were talking about a narrow usage case or all of the possible and / or probably ones. Having dealt with cust service for a few commercial dbs, I can safely say I get way better service from way smarter people when I have a problem. And I don't have a lot of problems. -- 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] How are locks managed in PG?
> Having dealt with cust service for a few commercial dbs, I can safely > say I get way better service from way smarter people when I have a > problem. And I don't have a lot of problems. Clarificiation: That's saying I get better service and such from pg users / developers than anywhere else. -- 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] How are locks managed in PG?
On Sun, Dec 21, 2008 at 11:04 PM, Scott Marlowe wrote: >> Having dealt with cust service for a few commercial dbs, I can safely >> say I get way better service from way smarter people when I have a >> problem. And I don't have a lot of problems. > > Clarificiation: That's saying I get better service and such from pg > users / developers than anywhere else. I'd agree with that. Unless you have lots of $$$ and/or know someone at the commercial companies, it takes a lot of work to get a hold of someone knowledgeable. -- Jonah H. Harris, Senior DBA myYearbook.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] How are locks managed in PG?
On Sun, Dec 21, 2008 at 11:02 PM, Scott Marlowe wrote: > The difference is HE put forth an opinion about the pg developers > being smarter, but you put forth what seems like a statement of fact > with no evidence to back it up. One is quite subjective and open for > debate on both sides, and often to good effect. The other is a > statement of fact regarding scalability in apparently all usage > circumstances, since it wasn't in any way clarified if you were > talking about a narrow usage case or all of the possible and / or > probably ones. Agreed. It's just that, because I know quite a few of the engineers working on Oracle and SQL Server, it generally pisses me off to see people make blanket statements about one group being smarter than another when they probably have no basis for comparison. It's all good though, I'm just cranky tonight. -Jonah -- 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] How are locks managed in PG?
Jonah H. Harris wrote: > On Sun, Dec 21, 2008 at 11:02 PM, Scott Marlowe > wrote: > > The difference is HE put forth an opinion about the pg developers > > being smarter, but you put forth what seems like a statement of fact > > with no evidence to back it up. One is quite subjective and open for > > debate on both sides, and often to good effect. The other is a > > statement of fact regarding scalability in apparently all usage > > circumstances, since it wasn't in any way clarified if you were > > talking about a narrow usage case or all of the possible and / or > > probably ones. > > Agreed. It's just that, because I know quite a few of the engineers > working on Oracle and SQL Server, it generally pisses me off to see > people make blanket statements about one group being smarter than > another when they probably have no basis for comparison. It's all > good though, I'm just cranky tonight. I am sure there are smart people at all the database companies. I do believe that open source development harnesses the abilities of its intelligent people better than commercial companies. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] How are locks managed in PG?
On Sun, Dec 21, 2008 at 9:35 PM, Bruce Momjian wrote: > Jonah H. Harris wrote: >> On Sun, Dec 21, 2008 at 11:02 PM, Scott Marlowe >> wrote: >> > The difference is HE put forth an opinion about the pg developers >> > being smarter, but you put forth what seems like a statement of fact >> > with no evidence to back it up. One is quite subjective and open for >> > debate on both sides, and often to good effect. The other is a >> > statement of fact regarding scalability in apparently all usage >> > circumstances, since it wasn't in any way clarified if you were >> > talking about a narrow usage case or all of the possible and / or >> > probably ones. >> >> Agreed. It's just that, because I know quite a few of the engineers >> working on Oracle and SQL Server, it generally pisses me off to see >> people make blanket statements about one group being smarter than >> another when they probably have no basis for comparison. It's all >> good though, I'm just cranky tonight. > > I am sure there are smart people at all the database companies. I do > believe that open source development harnesses the abilities of its > intelligent people better than commercial companies. I think one of the points that proves this is the chunks of innovative code that have been put into postgresql that were basically written by one or two guys in < 1 year. Small sharp teams can tackle one particular problem and do it very well in an open source project. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
