Re: [HACKERS] [PATCHES] GUC description cleanup
Neil, > Sure, I'll wait for 8.3 to branch. I have some cleanup I want to do for 8.3 too. Josh Berkus PostgreSQL @ Sun San Francisco 415-752-2500 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] WAL logging freezing
On Fri, 2006-10-27 at 22:19 +0100, Simon Riggs wrote: > So we definitely have a nasty problem here. > > VACUUM FREEZE is just a loaded gun right now. > > > Maybe it's OK to say that during WAL replay we keep it > > all the way back to the freeze horizon, but I'm not sure how we keep the > > system from wiping clog it still needs right after switching to normal > > operation. Maybe we should somehow not xlog updates of datvacuumxid? > > Thinking... Suggestions: 1. Create a new Utility rmgr that can issue XLOG_UTIL_FREEZE messages for each block that has had any tuples frozen on it during normal VACUUMs. We need log only the relid, blockid and vacuum's xid to redo the freeze operation. 2. VACUUM FREEZE need not generate any additional WAL records, but will do an immediate sync following execution and before clog truncation. That way the large number of changed blocks will all reach disk before we do the updates to the catalog. 3. We don't truncate the clog during WAL replay, so the clog will grow during recovery. Nothing to do there to make things safe. 4. When InArchiveRecovery we should set all of the datminxid and datvacuumxid fields to be the Xid from where recovery started, so that clog is not truncated soon after recovery. Performing a VACUUM FREEZE after a recovery would be mentioned as an optional task at the end of a PITR recovery on a failover/second server. 5. At 3.5 billion records during recovery we should halt the replay, do a full database scan to set hint bits, truncate clog, then restart replay. (Automatically within the recovery process). 6. During WAL replay, put out a warning message every 1 billion rows saying that a hint bit scan will eventually be required if recovery continues. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_internal.init is hazardous to your health
On Wed, 2006-10-18 at 15:56 +0100, Simon Riggs wrote: > On Tue, 2006-10-17 at 22:29 -0400, Tom Lane wrote: > > The answer that ultimately emerged was that they'd been running a > > nightly maintenance script that did REINDEX SYSTEM (among other things > > I suppose). The PITR base backup included pg_internal.init files that > > were appropriate when it was taken, and the PITR recovery process did > > nothing whatsoever to update 'em :-(. So incoming backends picked up > > init files with obsolete relfilenode values. > > OK, I'm looking at this now for later discussion. I've coded a patch and am just testing now. -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] WAL logging freezing
On Fri, 2006-10-27 at 12:01 -0400, Tom Lane wrote: > "Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > > Tom Lane wrote: > >> I think it's premature to start writing > >> patches until we've decided how this really needs to work. > > > Not logging hint-bit updates seems safe to me. As long as we have the > > clog, the hint-bit is just a hint. The problem with freezing is that > > after freezing tuples, the corresponding clog page can go away. > > Actually clog can go away much sooner than that, at least in normal > operation --- that's what datvacuumxid is for, to track where we can > truncate clog. So we definitely have a nasty problem here. VACUUM FREEZE is just a loaded gun right now. > Maybe it's OK to say that during WAL replay we keep it > all the way back to the freeze horizon, but I'm not sure how we keep the > system from wiping clog it still needs right after switching to normal > operation. Maybe we should somehow not xlog updates of datvacuumxid? Thinking... Also, we should probably be setting all the hint bits for pages during recovery then, so we don't need to re-write them again later. > Another thing I'm concerned about is the scenario where a PITR > hot-standby machine tracks a master over a period of more than 4 billion > transactions. I'm not sure what will happen in the slave's pg_clog > directory, but I'm afraid it won't be good :-( I think we'll need to error-out at that point, plus produce messages when we pass 2 billion transactions recovered. It makes sense to produce a new base backup regularly anyway. We'll also need to produce an error message on the primary server so that we take a new base backup every 2 billion transactions. There are better solutions, but I'm not sure it makes sense to try and fix them right now, since that could well delay the release. If we think it is a necessary fix for the 8.2 line then we could get a better fix into 8.2.1 [I've just coded the relcache invalidation WAL logging patch also.] -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] WAL logging freezing
"Simon Riggs" <[EMAIL PROTECTED]> writes: > [I've just coded the relcache invalidation WAL logging patch also.] What? That doesn't make any sense to me. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] qsort->pg_qsort in 8.2
Jan Wieck <[EMAIL PROTECTED]> writes: > On 10/27/2006 3:47 PM, Tom Lane wrote: >> Is it a problem? If you really want the platform qsort you can #undef >> qsort, but I don't entirely see why you would. > It forces client programs to link against libpgport, which they didn't > have to before. Client programs that aren't part of Postgres have no business including postgres_fe.h, and in any case the inclusion of port.h forces you to buy into libpgport, no? This is hardly the only substitute function we have. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] qsort->pg_qsort in 8.2
On 10/27/2006 3:47 PM, Tom Lane wrote: Jan Wieck <[EMAIL PROTECTED]> writes: since rev. 1.105 of include/port.h all files that inlcude postgres_fe.h are forced to use pg_qsort() instead of qsort. Was that intended? Is it a problem? If you really want the platform qsort you can #undef qsort, but I don't entirely see why you would. It forces client programs to link against libpgport, which they didn't have to before. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] qsort->pg_qsort in 8.2
Jan Wieck <[EMAIL PROTECTED]> writes: > since rev. 1.105 of include/port.h all files that inlcude postgres_fe.h > are forced to use pg_qsort() instead of qsort. Was that intended? Is it a problem? If you really want the platform qsort you can #undef qsort, but I don't entirely see why you would. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Replication documentation addition
On Wed, 25 Oct 2006, Bruce Momjian wrote: ...snip... > > > Data partitioning is often done within a single database on a single > > server and therefore, as a concept, has nothing whatsoever to do with > > different servers. Similarly, the second paragraph of this section is > > Uh, why would someone split things up like that on a single server? > > > problematic. Please define your term first, then talk about some > > implementations - this is muddying the water. Further, there are both > > vertical and horizontal partitioning - you mention neither - and each has > > its own distinct uses. If partitioning is mentioned, it should be more > > complete. > > Uh, what exactly needs to be defined. OK, "Data partitioning"; data partitioning begins in the RDB world with the very notion of tables, and we partition our data during schema development with the goal of "normalizing" the design - "thrid normal form" being the one most Professors talk about as a target. "Data partitioning", then, is the intentional denormalization of the design to accomplish some goal(s) - not all of which are listed in this document's title. In this context, data partitioning takes two forms based upon which axis of a two-dimensional table is to be divided, with the vertical partition dividing attributes (as in a master/detail relationship with one-to-one mapping), and the horizontal partition dividing based on one or more attributes domain, or value (as in your example of London records being kept in a database in London, while Paris records are kept in Paris). The point I was making was that that section of the document was in err because it presumed there was only one form of data partitioning and that it was horizontal. (The document is now missing, so I can't look at the current content - it was here: ftp://momjian.us/pub/postgresql/mypatches/replication.) In answer to your query about why someone would use such partitioning, the nearly universal answer is performance, and the distant second answer is security. In one example that comes immediately to mind, there is a table which is a central core of an application, and, as such, there's a lot to say about the items in this table. The table's size is in the tens to hundreds of millions of rows, and needs to be joined with something else in a huge fraction of queries. For performance reasons, the tables size was therefore kept as tiny as possible and detail table(s) is(are) used for the remaining attributes that logically belong in the table - it's a vertical partition. It's an exceptionally common technique - so common, it probably didn't occur to you that you were even talking about it when you spoke of "data partitioning." > > Next, Query Broadcast Load Balancing... also needs a lot of work. First, > > it's foremost in my memory that sending read queries everywhere and > > returning the first result set back is a key way to improve application > > performance at the cost of additional load on other systems - I guess > > that's not at all what the document is after here, but it's a worthy part > > of a dialogue on broadcasting queries. In other words, this has more parts > > to it than just what the document now entertains. Secondly, the document > > Uh, do we want to go into that here? I guess I could. > > > doesn't address _at_all_ whether this is a two-phaise-commit environment > > or not. If not, how are updates managed? If each server operates > > independently and one of them fails, what do you do then? How do you know > > _any_ server got an insert/update? ... Each server _can't_ operate > > independently unless the application does its own insert/update commits to > > every one of them - and that can't be fast, nor does it load balance, > > though it may contribute to superior uptime performance by the > > application. > > I think having the application middle layer do the commits is how it > works now. Can someone explain how pgpool works, or should we mention > how two-phase commit has to be done here? pgpool2 has additional > features. Well, you hadn't mentioned two phaise commit at all and it surely belong somewhere in this document - it's a core PG feature and enables a lot of alternative solutions which the document discusses. What it needs to say but doesn't (didn't?) is that the load from read queries can be distributed for load balancing purposes but that there's no benefit possible for writes, and that replication overhead costs could possibly overwhelm the benefits in high-update scenarios. The point that each server operates independently is only true if you ignore the the necessary replication - which, to my mind, links the systems and they are not independent. ...I suppose that in a completely read-only environment - or updated nightly by dumping tarwads or something like that, they could be considered independent, but it's hardly worth the sentence. Regards, Richard -- Richard Troy, Chief Scientist Science Tools Corporation 510-924-1363 o
[HACKERS] qsort->pg_qsort in 8.2
since rev. 1.105 of include/port.h all files that inlcude postgres_fe.h are forced to use pg_qsort() instead of qsort. Was that intended? Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] bug in on_error_rollback !?
Gurjeet Singh wrote: > \ds and \dS are commands (first token on the line) so it is > acceptable that they be case-sensitive. But a command's > parameters/arguments should not be case sensitive, unless quoted. This distinction has not basis in SQL syntax. > If it is documented that psql commands are case sensitive, then I > would like to point to an ambiguity: If it helps, compare this to ECPG: The C parts follow the syntax rules of C, the SQL parts follow the syntax rules of SQL. In psql, the psql parts follow the syntax rules of psql, the SQL parts follow the syntax rules of SQL. The syntax rules of psql in turn are inspired by Unix shells, sort of because psql is used that way. (Surely one wouldn't want the argument to \i be case-insensitive?) -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] WAL logging freezing
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> I think it's premature to start writing >> patches until we've decided how this really needs to work. > Not logging hint-bit updates seems safe to me. As long as we have the > clog, the hint-bit is just a hint. The problem with freezing is that > after freezing tuples, the corresponding clog page can go away. Actually clog can go away much sooner than that, at least in normal operation --- that's what datvacuumxid is for, to track where we can truncate clog. Maybe it's OK to say that during WAL replay we keep it all the way back to the freeze horizon, but I'm not sure how we keep the system from wiping clog it still needs right after switching to normal operation. Maybe we should somehow not xlog updates of datvacuumxid? Another thing I'm concerned about is the scenario where a PITR hot-standby machine tracks a master over a period of more than 4 billion transactions. I'm not sure what will happen in the slave's pg_clog directory, but I'm afraid it won't be good :-( regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] WAL logging freezing
Tom Lane wrote: Heikki Linnakangas <[EMAIL PROTECTED]> writes: I would've liked to give freezing a new opcode, but we've ran out of them (see htup.h). Hardly ... we have plenty of unused rmgr id's still. Good point. The real issue that still has to be resolved is the interaction of all this stuff with PITR scenarios --- is it still safe to not log hint-bit updates when PITR is on? I think it's premature to start writing patches until we've decided how this really needs to work. Not logging hint-bit updates seems safe to me. As long as we have the clog, the hint-bit is just a hint. The problem with freezing is that after freezing tuples, the corresponding clog page can go away. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug in on_error_rollback !?
On Fri, 2006-10-27 at 03:50 -0500, Andrew Dunstan wrote: > psql variables and commands are not SQL, and are case sensitive. For > example, \ds and \dS are not at all the same. > > This is documented clearly on the psql man page, so it is simply not a > bug It may be documented, but \set still has a terrible user interface. There is no feedback about whether a variable has been successfully changed. Updates to non-existent variables are silently accepted, and references to non-existent variables do not result in an error. I've complained about this before[1], but there wasn't a consensus on how to improve this behavior (given the need for backward compatibility). -Neil [1] http://mail-archive.com/pgsql-hackers@postgresql.org/msg41273.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] GUC description cleanup
On Fri, 2006-10-27 at 15:59 +0200, Peter Eisentraut wrote: > I appreciate this effort, but I think it's better to hold the patch. Sure, I'll wait for 8.3 to branch. -Neil ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Traveling for two weeks
bruce wrote: > FYI, I am leaving Friday for a two-week trip for EnterpriseDB. I am > going to Tokyo, Islamabad (Pakistan), and Pune (India). I return on > Friday, November 10. I will have Internet connectivity, but of course I > will not be online as frequently as usual. My plans have changed and I will be going from India directly to San Francisco to attend a Greenplum community meeting. I now return home on Wednesday, November 15th. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Is python 2.5 supported?
Am Dienstag, 17. Oktober 2006 11:59 schrieb Hannu Krosing: > > Several of the failures appear to be a simple change in error reporting; > > I haven't investigated why import_succeed() failed. > > > > Should python 2.5 work with plpython? > > This is about pl_python ? > > Forwarding to Sven to investigate Any news on this? We've had another bug report stating the same issue. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] GUC description cleanup
Am Donnerstag, 26. Oktober 2006 19:47 schrieb Neil Conway: > Note that this patch breaks the translations of these strings, so I > haven't applied it yet. Should I apply it now, or wait for 8.3 to > branch? I appreciate this effort, but I think it's better to hold the patch. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Deadlock with pg_dump?
"Albe Laurenz" <[EMAIL PROTECTED]> writes: >> [ Memo to hackers: why is it that log_min_error_statement = error >> isn't the default? ] > To avoid spamming the logs with every failed SQL statement? Certainly there are people who will turn it off, but that's why it's configurable. I've had to answer "how do I find out what's causing error message FOO" often enough that I'm starting to think logging error statements is a more useful default than not logging 'em ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] bug in on_error_rollback !?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 > This is documented clearly on the psql man page, so it is simply not a > bug, and changing this would probably break lots of legacy scripts. In a general sense, perhaps, but in this *particular* case, I don't see what harm allowing "\set on_error_rollback" would be: it certainly won't break any existing scripts. I wrote this feature (but someone else chose the name!) and I still occasionally write it lowercase and wonder why it isn't working. :) Perhaps even allowing all of the \set commands to be case-insensitive may be a good idea? - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200610270914 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFFQgawvJuQZxSWSsgRAvBKAKDb4gcpRsjpbxo7SKJwmu5y7Bo48QCeIMao DZFuEIPJaPNyv/TRGNH6shc= =/n5U -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] 8.1-407 JDBC driver issues
This belongs on the jdbc driver list
On 27-Oct-06, at 6:29 AM, lasitha weerasinghe wrote:
Hi,
I'm using postgresql 8.1-407 jdbc driver with postgresql 7.4.13
database and it seems like there is an issue when the driver is
used in jdbc transactions. I'm using this version of the jdbc
driver, cause I haven't came across any fixes for the 8.1.4
security issuse for the 7.4.13 database driver. I only noticed that
the fix has been done for the v.8 drivers only.
But the problem i'm facing now is when i'm using statements in a
transaction (setting the connection.setAutoCommit(false)) some of
the records doesn't go to the database. This is not a frequent
issue and also I have noticed couple of times that if I try to add
the same record again the db will throw duplicate record exception
even though the record is not there in the database.
this is the code i'm using for JDBC transactions.
connection.setAutoCommit( false );
connection.setTransactionIsolation
(Connection.TRANSACTION_SERIALIZABLE);
try {
// ... execute statements
connection.commit();
} catch (Exception e) {
logger.logError(e);
connection.rollback();
}
is anyone of you came across this problem and managed to find a
solution?
The 8.1 driver should work fine on a 7.4 server, now I still don't
know the problem ? I've never seen it not put the record in the
database ? Have you looked at the server logs ?
I need more information to fix this .
Dave
_
FREE pop-up blocking with the new MSN Toolbar - get it now! http://
toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
---(end of
broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[HACKERS] 8.1-407 JDBC driver issues
Hi,
I'm using postgresql 8.1-407 jdbc driver with postgresql 7.4.13 database and
it seems like there is an issue when the driver is used in jdbc
transactions. I'm using this version of the jdbc driver, cause I haven't
came across any fixes for the 8.1.4 security issuse for the 7.4.13 database
driver. I only noticed that the fix has been done for the v.8 drivers only.
But the problem i'm facing now is when i'm using statements in a transaction
(setting the connection.setAutoCommit(false)) some of the records doesn't go
to the database. This is not a frequent issue and also I have noticed couple
of times that if I try to add the same record again the db will throw
duplicate record exception even though the record is not there in the
database.
this is the code i'm using for JDBC transactions.
connection.setAutoCommit( false );
connection.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
try {
// ... execute statements
connection.commit();
} catch (Exception e) {
logger.logError(e);
connection.rollback();
}
is anyone of you came across this problem and managed to find a solution?
_
FREE pop-up blocking with the new MSN Toolbar - get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] printing the query plan?
Try your logfile... the one specified by the '-l' option while starting the server.On 10/27/06, dakotali kasap < [EMAIL PROTECTED]> wrote: But how can I find where stout is redirected to? Baran -- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com
Re: [HACKERS] printing the query plan?
Hi,> > I want to print the query-plan that will be used before the execution> > of the query. Therefore put this line at the beginning of the> > ExecutorStart() function located in execMain.c :> >> > print_plan(queryDesc->plantree,queryDesc->parsetree);> print_plan writes to stdout, did you check where that is redirected to? But how can I find where stout is redirected to?Baran
Re: [HACKERS] bug in on_error_rollback !?
I understand that psql commands are not SQL, but since psql is used to interact with SQL database, then the assumption of case-insensitivity in psql also comes naturally to the user.\ds and \dS are commands (first token on the line) so it is acceptable that they be case-sensitive. But a command's parameters/arguments should not be case sensitive, unless quoted. If it is documented that psql commands are case sensitive, then I would like to point to an ambiguity:I have two tables:postgres=# create table "t"( a int); CREATE TABLE postgres=# create table "T"( b int);CREATE TABLE Now, according to your statement, the \d command should report about two different tables in the following first two commands:postgres=# \d t Table "public.t" Column | Type | Modifiers +-+--- a | integer |postgres=# \d T Table "public.t" Column | Type | Modifiers +-+--- a | integer |postgres=# \d "T" Table "public.T" Column | Type | Modifiers +-+--- b | integer |postgres=# But, as you can see, I had to d-quote T to get the intended result. IMHO, \d is behaving correctly, and other '\' commands should treat their parameters/arguments likewise.For the more inquisitive (I know you'll go and try the -E switch to psql), here's what's sent to the backend for the three different \d commands: postgres=# \d t* QUERY ** SELECT c.oid, n.nspname, c.relnameFROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^t$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3;** postgres=# \d T * QUERY **SELECT c.oid, n.nspname, c.relname FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE c.relname ~ '^t$' AND pg_catalog.pg_table_is_visible(c.oid)ORDER BY 2, 3; ** postgres=# \d "T"* QUERY ** SELECT c.oid, n.nspname, c.relnameFROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relname ~ '^T$' AND pg_catalog.pg_table_is_visible(c.oid) ORDER BY 2, 3;** postgres=# Regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com On 10/27/06, Andrew Dunstan <[EMAIL PROTECTED]> wrote: Gurjeet Singh wrote:> Thanks ...>> but case-sensitivity (even without quotes or d-quotes) is the last thing> I'd> expect in a SQL compliant software. This was highly unexpected. May I dare > to raise a bug to eliminate case-sensitivity in psql variables? Will I get> support from the community?>psql variables and commands are not SQL, and are case sensitive. Forexample, \ds and \dS are not at all the same. This is documented clearly on the psql man page, so it is simply not abug, and changing this would probably break lots of legacy scripts.cheersandrew
Re: [HACKERS] New CRC algorithm: Slicing by 8
> > > Putting xl_prev to the end helps only for direct IO WAL sync > > > methods, else we would need it on every page. > > > > [There is already an XLogRecPtr on each 8k page.] > > Given that hardware sector size is still 512 bytes, should > there be a way of detecting a missing 512 byte block in the > middle of an 8K block. > The idea of simply writing a serial counter every 512 bytes > seems to be a good way to handle that... No, we have CRC for that. You are not supposed to turn it off when you see a chance, that an 8k block is not whole. Andreas ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] New CRC algorithm: Slicing by 8
On Fri, Oct 27, 2006 at 10:11:08AM +0100, Simon Riggs wrote: > > Putting xl_prev to the end helps only for direct IO WAL sync methods, > > else we would need it on every page. > > [There is already an XLogRecPtr on each 8k page.] Given that hardware sector size is still 512 bytes, should there be a way of detecting a missing 512 byte block in the middle of an 8K block. The idea of simply writing a serial counter every 512 bytes seems to be a good way to handle that... Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] printing the query plan?
On Fri, Oct 27, 2006 at 01:58:21AM -0700, dakotali kasap wrote: > Hi, > > I want to print the query-plan that will be used before the execution > of the query. Therefore put this line at the beginning of the > ExecutorStart() function located in execMain.c : > > print_plan(queryDesc->plantree,queryDesc->parsetree); print_plan writes to stdout, did you check where that is redirected to? Also, I thought there was a logging option to print plans, but I might be wrong about that. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [HACKERS] New CRC algorithm: Slicing by 8
On Fri, 2006-10-27 at 10:54 +0200, Zeugswetter Andreas ADI SD wrote: > >> In the WAL we just need to be able to detect torn pages and stop > >> reading WAL at that point. That's easier and doesn't really need a > >> CRC. We could just adopt the Sybase strategy of storing a unique id > >> number every 512 bytes throughout the WAL page. If those numbers > don't > >> match then we have a torn page; the system crashed at that point and > we should stop reading WAL pages. > > > I've looked into this in more depth following your > > suggestion: I think it seems straightforward to move the > > xl_prev field from being a header to a trailer. That way when > > we do the test on the back pointer we will be assured that > > there is no torn page effecting the remainder of the xlrec. > > That would make it safer with wal_checksum = off. > > I do not think we can assume any order of when a block is written to > disk. > > I think all this can only be used on OS and hardware, that can guarantee > that what is written by one IO call (typically 8k) from pg is safe. > Those combinations do exist, so I think we want the switch. OK, good. ... but from the various comments I'll not bother moving xl_prev to be a trailer; I'll just leave that where it is now. > Putting xl_prev to the end helps only for direct IO WAL sync methods, > else we would need it on every page. [There is already an XLogRecPtr on each 8k page.] -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] printing the query plan?
Hi,I want to print the query-plan that will be used before the execution of the query. Therefore put this line at the beginning of the ExecutorStart() function located in execMain.c :print_plan(queryDesc->plantree,queryDesc->parsetree);However, it did not work. What I want to ask is:1. Does postgresql call ExecutorStart()-ExecutorRun-ExecutorEnd functions each time a new query is invoked? If so, why the above line did not work?2. Can I print just a dummy string inside ExecutorStart() function and will it be shown before the result of the query? Because I also tried this and it also did not work.Thanks a lot.Baran
Re: [HACKERS] Deadlock with pg_dump?
Csaba Nagy wrote: > On Fri, 2006-10-27 at 09:23, Albe Laurenz wrote: >> > [ Memo to hackers: why is it that log_min_error_statement = error >> > isn't the default? ] >> >> To avoid spamming the logs with every failed SQL statement? > > And it would be hurting applications where query failure is taken as a > valid path (as inserting first and update if failing)... > Both of these are arguments in favor of being able to alter the level, which would still exist. They are not good arguments for not having error as the default level. cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] New CRC algorithm: Slicing by 8
>> In the WAL we just need to be able to detect torn pages and stop >> reading WAL at that point. That's easier and doesn't really need a >> CRC. We could just adopt the Sybase strategy of storing a unique id >> number every 512 bytes throughout the WAL page. If those numbers don't >> match then we have a torn page; the system crashed at that point and we should stop reading WAL pages. > I've looked into this in more depth following your > suggestion: I think it seems straightforward to move the > xl_prev field from being a header to a trailer. That way when > we do the test on the back pointer we will be assured that > there is no torn page effecting the remainder of the xlrec. > That would make it safer with wal_checksum = off. I do not think we can assume any order of when a block is written to disk. I think all this can only be used on OS and hardware, that can guarantee that what is written by one IO call (typically 8k) from pg is safe. Those combinations do exist, so I think we want the switch. Putting xl_prev to the end helps only for direct IO WAL sync methods, else we would need it on every page. Andreas ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bug in on_error_rollback !?
Gurjeet Singh wrote: > Thanks ... > > but case-sensitivity (even without quotes or d-quotes) is the last thing > I'd > expect in a SQL compliant software. This was highly unexpected. May I dare > to raise a bug to eliminate case-sensitivity in psql variables? Will I get > support from the community? > psql variables and commands are not SQL, and are case sensitive. For example, \ds and \dS are not at all the same. This is documented clearly on the psql man page, so it is simply not a bug, and changing this would probably break lots of legacy scripts. cheers andrew ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Deadlock with pg_dump?
On Fri, 2006-10-27 at 09:23, Albe Laurenz wrote: > > [ Memo to hackers: why is it that log_min_error_statement = error > > isn't the default? ] > > To avoid spamming the logs with every failed SQL statement? And it would be hurting applications where query failure is taken as a valid path (as inserting first and update if failing)... Cheers, Csaba. ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] bug in on_error_rollback !?
Thanks ...but case-sensitivity (even without quotes or d-quotes) is the last thing I'd expect in a SQL compliant software. This was highly unexpected. May I dare to raise a bug to eliminate case-sensitivity in psql variables? Will I get support from the community? Regards,-- [EMAIL PROTECTED][EMAIL PROTECTED] gmail | hotmail | yahoo }.com On 10/27/06, Bernd Helmle <[EMAIL PROTECTED]> wrote: You have to specify the parameter in upper case:[EMAIL PROTECTED]:bernd #*= \set ON_ERROR_ROLLBACK
Re: [HACKERS] Deadlock with pg_dump?
> [ Memo to hackers: why is it that log_min_error_statement = error > isn't the default? ] To avoid spamming the logs with every failed SQL statement? Yours, Laurenz Albe ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] bug in on_error_rollback !?
--On Freitag, Oktober 27, 2006 11:00:07 +0530 Gurjeet Singh <[EMAIL PROTECTED]> wrote: I was thinking of recommending this to someone, but wanted to try it on my own first; good thing that I did. I think it is broken as of now. I assume that the error thrown for 'select 1', inside a transaction, with 'on_error_rollback on', is not supposed to raise it's head !!! Or am I missing something? [...] You have to specify the parameter in upper case: [EMAIL PROTECTED]:bernd #= BEGIN; BEGIN [EMAIL PROTECTED]:bernd #*= \set ON_ERROR_ROLLBACK [EMAIL PROTECTED]:bernd #*= SELECT a; ERROR: column "a" does not exist LINE 1: SELECT a; ^ [EMAIL PROTECTED]:bernd #*= SELECT 1; ?column? -- 1 (1 row) -- Thanks Bernd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
