Re: [HACKERS] [PATCHES] default database creation with initdb
Andreas Pflug wrote: Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Can't tell whether I could find time for reviewing the docs the next days (more interesting for feature freeze is having fixed the implementation anyway). Of the sixty-odd files that mention template1 in current CVS, only about half are documentation. The decision which files should be changed must be taken. e.g. createdb, dropdb will use template1 hardcoded. Is it acceptable that those tools fail if the postgres database isn't present any more? How about template1 as a fallback? cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] default database creation with initdb
Andrew Dunstan wrote: The decision which files should be changed must be taken. e.g. createdb, dropdb will use template1 hardcoded. Is it acceptable that those tools fail if the postgres database isn't present any more? How about template1 as a fallback? Fallback is a fine idea, but this brings up another problem I'm currently facing: how to identify the problem the connection has from libpq? If the problem is a wrong password, we certainly don't want to try again. I browsed the sources over and over, but apparently there's no machine readable return code to distinguish the reason of connection failure apart from examining the errormessage string. I have the same problem in pgAdmin, where I try to give extended messages like http://svn.pgadmin.org/cgi-bin/viewcvs.cgi/trunk/pgadmin3/docs/en_US/hints/conn-listen.html?rev=4056view=markup Regards, Andreas ---(end of broadcast)--- TIP 3: 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] [PATCHES] default database creation with initdb
Dave Page wrote: That's what I'm working on atm, and given Tom's previous comment about small-footprint users not wanting an extra 5/6MB on the size of a new cluster, I'm leaving most things using template1 and mainly just updating docs and examples. 'postgres' can then be dropped with no ill effects other than a return to the old template1 etc. issues. I'm confused. I thought avoiding those issues was one of the main purposes for this. cheers andrew ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] default database creation with initdb
-Original Message- From: Andrew Dunstan [mailto:[EMAIL PROTECTED] Sent: 20 June 2005 10:41 To: Dave Page Cc: Andreas Pflug; Tom Lane; Robert Treat; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb Dave Page wrote: That's what I'm working on atm, and given Tom's previous comment about small-footprint users not wanting an extra 5/6MB on the size of a new cluster, I'm leaving most things using template1 and mainly just updating docs and examples. 'postgres' can then be dropped with no ill effects other than a return to the old template1 etc. issues. I'm confused. I thought avoiding those issues was one of the main purposes for this. No, it's mainly things like pgAdmin that create persistent connections that are the problem. Createuser et al. connect and disconnect so quickly it's unlikely to cause any problems. Regards, Dave. ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] HOOKS for Synchronous Replication
Hi, The implementation of a set of hooks for efficient synchronous replication without extensive patching of Postgresql source is now available at: http://gorda.di.uminho.pt/community/ Note that this is far from a full replication product. It is made available for early feedback by the Postgresql hacker community and because it is probably already useful for other projects (e.g. materialized views). This is the first self-contained component extracted from a working prototype of replication based on group communication under development by the GORDA project. Comments and suggestions welcome, Regards Alfranio Junior. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] quote_boolean() and friends missing
Hi, I'm writing an application with a lot of PL/pgSQL and am constructing SQL queries on the fly. The quote_literal() and quote_ident() functions proved very much needed. Similarly, I need functions that return the SQL representation of all the other datatypes, not just TEXT: quote_boolean (), quote_date() and so on. For the sake of completeness I think these functions should exist. While the use of to_char() resp. decode() for some types is possible, it does feel like a workaround to me. Opinions? Do these functions belong in the PostgreSQL core? -- Markus Bertheau ☭ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] default database creation with initdb
Andrew Dunstan [EMAIL PROTECTED] writes: Dave Page wrote: That's what I'm working on atm, and given Tom's previous comment about small-footprint users not wanting an extra 5/6MB on the size of a new cluster, I'm leaving most things using template1 and mainly just updating docs and examples. 'postgres' can then be dropped with no ill effects other than a return to the old template1 etc. issues. I'm confused. I thought avoiding those issues was one of the main purposes for this. +1. If we still have the problem that CREATE DATABASE is likely to fail because of random connections to template1 from other tools, then we haven't fixed one of the problems this was advertised to fix. I don't see that much of a problem with having createdb etc. hardwire postgres instead of template1 as the db-to-connect-to. What that implies is that if you do have to drop and recreate it, you have to do it the hard way: psql template1 DROP DATABASE postgres; CREATE DATABASE postgres; But this isn't *that* hard, certainly way easier than recreating template1 from template0 which is the procedure that you have to follow now in comparable circumstances. Alternatively one could imagine special-casing dropdb and createdb to connect to template1 if the target database is postgres, and to postgres in all other cases. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] [PATCHES] default database creation with initdb
Andreas Pflug [EMAIL PROTECTED] writes: Fallback is a fine idea, but this brings up another problem I'm currently facing: how to identify the problem the connection has from libpq? If the problem is a wrong password, we certainly don't want to try again. I browsed the sources over and over, but apparently there's no machine readable return code to distinguish the reason of connection failure apart from examining the errormessage string. If it's a server-side failure it should have a SQLSTATE code. I think it'd be OK to look for ERRCODE_UNDEFINED_DATABASE to determine this. However that still leaves us with an issue: $ psql -U foo psql: FATAL: database foo does not exist The real problem here is that there's no user foo, but the backend is currently coded in such a way that it detects the bad implied database name first (at least in non-password-based auth methods). Not sure if this is a big problem for code that's not defaulting the database name though. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] quote_boolean() and friends missing
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes: I'm writing an application with a lot of PL/pgSQL and am constructing SQL queries on the fly. The quote_literal() and quote_ident() functions proved very much needed. Similarly, I need functions that return the SQL representation of all the other datatypes, not just TEXT: quote_boolean (), quote_date() and so on. For the sake of completeness I think these functions should exist. While the use of to_char() resp. decode() for some types is possible, it does feel like a workaround to me. Opinions? Do these functions belong in the PostgreSQL core? What exactly do you think they would do? There is no analogy to inserting escape characters for those other datatypes. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] quote_boolean() and friends missing
В Пнд, 20/06/2005 в 10:01 -0400, Tom Lane пишет:
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes:
I'm writing an application with a lot of PL/pgSQL and am constructing
SQL queries on the fly. The quote_literal() and quote_ident() functions
proved very much needed. Similarly, I need functions that return the SQL
representation of all the other datatypes, not just TEXT: quote_boolean
(), quote_date() and so on. For the sake of completeness I think these
functions should exist. While the use of to_char() resp. decode() for
some types is possible, it does feel like a workaround to me. Opinions?
Do these functions belong in the PostgreSQL core?
What exactly do you think they would do? There is no analogy to
inserting escape characters for those other datatypes.
They would return the SQL representation of a given value.
quote_boolean(TRUE) = 'TRUE'
quote_bytea('\377'::bytea) = '\\377' (literally \377)
Maybe quote_* is not the right name, but the functionality is needed.
I'm currently looking for a way to get the SQL representation of a bytea
value and it looks like I'm going to have to write a C function for
that.
Markus
--
Markus Bertheau ☭ [EMAIL PROTECTED]
---(end of broadcast)---
TIP 3: 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] quote_boolean() and friends missing
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes: Maybe quote_* is not the right name, but the functionality is needed. I don't think so --- at least not in plpgsql, which can do this already. Just assign the value to a text variable and then use quote_literal. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] quote_boolean() and friends missing
В Пнд, 20/06/2005 в 10:10 -0400, Tom Lane пишет: Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes: Maybe quote_* is not the right name, but the functionality is needed. I don't think so --- at least not in plpgsql, which can do this already. Just assign the value to a text variable and then use quote_literal. Isn't that a workaround? Or is that the way such things are done in plpgsql? Markus -- Markus Bertheau ☭ [EMAIL PROTECTED] ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] quote_boolean() and friends missing
On Mon, 20 Jun 2005, Tom Lane wrote: Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes: Maybe quote_* is not the right name, but the functionality is needed. I don't think so --- at least not in plpgsql, which can do this already. Just assign the value to a text variable and then use quote_literal. Would that work for a bytea with embedded nul characters or does that run the risk of terminating the value early? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] default database creation with initdb
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Fallback is a fine idea, but this brings up another problem I'm currently facing: how to identify the problem the connection has from libpq? If the problem is a wrong password, we certainly don't want to try again. I browsed the sources over and over, but apparently there's no machine readable return code to distinguish the reason of connection failure apart from examining the errormessage string. If it's a server-side failure it should have a SQLSTATE code. I think it'd be OK to look for ERRCODE_UNDEFINED_DATABASE to determine this. SQLSTATE? Specifically, I'm talking about no pg_hba.conf entry for , Ident authentication failed.. (both server sice) and Is the server running on host ... from libpq which I receive from PQerrorMessage after PQconnectdb; I only receive CONNECTION_BAD from PQstatus where I'd like it a little more specific (maybe an PQextendedStatus) Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] quote_boolean() and friends missing
Stephan Szabo [EMAIL PROTECTED] writes:
On Mon, 20 Jun 2005, Tom Lane wrote:
I don't think so --- at least not in plpgsql, which can do this already.
Just assign the value to a text variable and then use quote_literal.
Would that work for a bytea with embedded nul characters or does that run
the risk of terminating the value early?
Works for me:
regression=# create function froob(bytea) returns text as $$
regression$# declare t text;
regression$# begin
regression$# t := $1;
regression$# return quote_literal(t);
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# select froob('\\377\\000\\377'::bytea);
froob
---
'\\377\\000\\377'
(1 row)
regression=#
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] quote_boolean() and friends missing
Markus Bertheau =?UTF-8?Q?=E2=98=AD?= [EMAIL PROTECTED] writes: Just assign the value to a text variable and then use quote_literal. Isn't that a workaround? Or is that the way such things are done in plpgsql? It's the way it's done --- plpgsql does this by invoking the datatype output function and then the text input function. There has been talk of formalizing that in other contexts by making an explicit cast to text do that for every datatype, but nothing's been done about it yet. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] default database creation with initdb
Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: If it's a server-side failure it should have a SQLSTATE code. Specifically, I'm talking about no pg_hba.conf entry for , ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION Ident authentication failed.. (both server sice) Ditto. Do you need to know the difference? What exactly would client code do differently for these two cases? and Is the server running on host ... from libpq libpq doesn't currently assign SQLSTATEs to internally detected errors ... someday someone should fix that. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] default database creation with initdb
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 20 June 2005 14:19 To: Andrew Dunstan Cc: Dave Page; Andreas Pflug; Robert Treat; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb I don't see that much of a problem with having createdb etc. hardwire postgres instead of template1 as the db-to-connect-to. OK, new patch posted to -patches that updates all the utilities as well. The only change I didn't make was in line 3458 (in StartChildProcess) of postmaster.c - template1 seemed the more sensible option to leave there. Let me know if you disagree and I'll change it :-) Regards, Dave. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] query plan ignoring check constraints
Hi I have createda base table with a column id of type int. This table is inherited by a few subtables each of which have rows which satisfy a range of ids. The ranges are mutually exclusive. For example: 0=id1 subtable1 1=id2 subtable2 2=id3 subtable3 3=id4 subtable4 . . (n-1)*1idn*1 subtable_n Additionally, I have created check constraints for each table as per their range. So subtable_i can contain ids only in the range (i-1)*1 id i*1 . The check constraints work well when i try to insert a value outside the range by disallowing such inserts. Each of the subtables have been indexed on id. Now, whenI do a select on the basetable with a range of ids, it looks up each subtable that inherits from the base table and using an indexed scan searches for values in the range. It does it evenfor subtableswhose check constraint completely rules out the possibility of it containing any such row . Should not check constraint act as the first filter? The index should ideally be scanned only when the check constraint is passed by the search criteria but surprisingly it did not happen. The explain analyze showed cost for index scans of subtables that cannot contain rows matching the search criteria. Is there any way I can enforce this or is there any other wayof hinting the query planner to completely ignore looking ata subtable for a given range/criteria? Thanks, Rohit Too much spam in your inbox? Yahoo! Mail gives you the best spam protection for FREE!http://in.mail.yahoo.com
Re: [HACKERS] query plan ignoring check constraints
Rohit, Now, when I do a select on the basetable with a range of ids, it looks up each subtable that inherits from the base table and using an indexed scan searches for values in the range. It does it even for subtables whose check constraint completely rules out the possibility of it containing any such row . Should not check constraint act as the first filter? The index should ideally be scanned only when the check constraint is passed by the search criteria but surprisingly it did not happen. The explain analyze showed cost for index scans of subtables that cannot contain rows matching the search criteria. This is called range partitioning. We're working on it. You're welcome to join the Bizgres project where most of the discussion on this feature takes place: www.bizgres.org http://pgfoundry.org/mail/?group_id=1000107 -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] buildfarm notifications
On Sunday 19 June 2005 15:42, Josh Berkus wrote:
What are they testing and how? How often?
Regression tests on PostgreSQL, their own php tests on phpPgAdmin, and
standard JDBC test on pgJDBC.
Tests are based on when there have been submissions to CVS. They are doing
their best to do tests by patch.
I'd be interested in getting failure reports on phpPgAdmin... can you put me
in touch with someone wrt that ?
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Re: [HACKERS] HOOKS for Synchronous Replication
On Mon, Jun 20, 2005 at 11:03:45AM +0100, Alfranio Correia Junior wrote: Alfranio, The implementation of a set of hooks for efficient synchronous replication without extensive patching of Postgresql source is now available at: http://gorda.di.uminho.pt/community/ Some random coments, from general eyeballing the patch: First of all your patch does not conform with the project style. Please have a look at how other files are indented, in particular regarding brace position and ereport() arguments (any function arguments really, but in ereport your problems are more visible). Also, always use ereport() for user messages, elog() for conditions that involve can't-happen situations (server bugs, like not finding a tuple in a catalog that should be there, etc). Also there are some changes that you certainly don't want committed. For example why are you removing the TransState from xact.c? Also, consider using the XactCallback mechanism instead of inventing your own. On a different front, have you considered talking to the people behind Slony-II to see if they'd have some use for your hooks? -- Alvaro Herrera (alvherre[a]surnet.cl) Thou shalt check the array bounds of all strings (indeed, all arrays), for surely where thou typest foo someone someday shall type supercalifragilisticexpialidocious (5th Commandment for C programmers) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] query plan ignoring check constraints
On Mon, Jun 20, 2005 at 10:35:24AM -0700, Josh Berkus wrote: Rohit, Now, when I do a select on the basetable with a range of ids, it looks up each subtable that inherits from the base table and using an indexed scan searches for values in the range. It does it even for subtables whose check constraint completely rules out the possibility of it containing any such row . Should not check constraint act as the first filter? The index should ideally be scanned only when the check constraint is passed by the search criteria but surprisingly it did not happen. The explain analyze showed cost for index scans of subtables that cannot contain rows matching the search criteria. This is called range partitioning. We're working on it. You're welcome to join the Bizgres project where most of the discussion on this feature takes place: Why are you discussing development there? I can see in the archives that people are talking about changing page format, semantics of tuple info bits, and it's not getting to some people that matters. -- Alvaro Herrera (alvherre[a]surnet.cl) La web junta la gente porque no importa que clase de mutante sexual seas, tienes millones de posibles parejas. Pon buscar gente que tengan sexo con ciervos incendiándose, y el computador dirá especifique el tipo de ciervo (Jason Alexander) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] buildfarm notifications
Robert, I'd be interested in getting failure reports on phpPgAdmin... can you put me in touch with someone wrt that ? I'll post to you KL as soon as it's publicly available. Currently you can only access the tests on Spike's intranet. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] Issue with plpython write_file and read_file tests
Setting up the buildfarm on a machine I've discovered an issue with the plpython write_file and read_file tests. The problem can be seen at http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=platypusdt=2005-06-20%2018:15:00 The specific issue is that the test doesn't clean up after itself, leaving /tmp/plpython behind. This raised it's ugly head when I tested the buildfarm stuff as one user and then switched to using a different user. What would be the best way to handle this? A delete_file plpython function might work, though if read_file fails then you'd still have a file left to clean up, so it seems like it might be better to put the cleanup functionality in the test suite. ISTM it would also be good if the test honored $TEMP. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] HOOKS for Synchronous Replication
On Mon, 20 Jun 2005, Alvaro Herrera wrote: On Mon, Jun 20, 2005 at 11:03:45AM +0100, Alfranio Correia Junior wrote: Alfranio, The implementation of a set of hooks for efficient synchronous replication without extensive patching of Postgresql source is now available at: http://gorda.di.uminho.pt/community/ ... Also there are some changes that you certainly don't want committed. For example why are you removing the TransState from xact.c? That puzzled me too, until I noticed that it's not removed, just moved to xact.h. The current transaction state is exposed in a new getCurrentTransactionState function, that's why TransState has to be in a header file... I just took a quick glance, I can't say why it's that way. - Heikki ---(end of broadcast)--- TIP 3: 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
[HACKERS] index selection by query planner
Hi, I have a tablewith two indiceson the same column, one ofwhich is apartial index. I would like the query planner to use the partial index whenever the query condition lies in the range of the partial index as it would yield better performance.Is there any way to enforce the ordering for the indices? How does the query planner decide which index to use when a particular query is fired? 'Explain Analyze' showed the total index being used in a situation that could be fulfiled by the partial index. Thanks, Rohit Free antispam, antivirus and 1GB to save all your messages Only in Yahoo! Mail: http://in.mail.yahoo.com
Re: [HACKERS] pg_locks view versus prepared transactions
I think the minimum thing we ought to do about this is add an XID column to pg_locks to show the transaction ID holding each lock. Then you could join that to pg_prepared_xacts to see what's what. I was also wondering about adding a current-XID column to pg_stat_activity, and encouraging people to join pg_locks and pg_stat_activity on XID instead of PID. That would be awesome. Is there any performance penalty to do this? (I don't care about performance of pg_lock_status function execution, just overall overhead). Ultimately we should maybe even remove PID from pg_locks, but probably for backwards compatibility it'd have to be deprecated for a release or two first. It is interesting to note that systems with stats disabled are unable to get lock owner information in this case (so what?). Merlin ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_locks view versus prepared transactions
On Mon, Jun 20, 2005 at 03:55:45PM -0400, Merlin Moncure wrote: Ultimately we should maybe even remove PID from pg_locks, but probably for backwards compatibility it'd have to be deprecated for a release or two first. It is interesting to note that systems with stats disabled are unable to get lock owner information in this case (so what?). We could make the pg_stat_activity view show information from the ProcArray shared struct, when stats are disabled. -- Alvaro Herrera (alvherre[a]surnet.cl) In fact, the basic problem with Perl 5's subroutines is that they're not crufty enough, so the cruft leaks out into user-defined code instead, by the Conservation of Cruft Principle. (Larry Wall, Apocalypse 6) ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_locks view versus prepared transactions
Merlin Moncure [EMAIL PROTECTED] writes: I was also wondering about adding a current-XID column to pg_stat_activity, and encouraging people to join pg_locks and pg_stat_activity on XID instead of PID. That would be awesome. Is there any performance penalty to do this? I gave up on the idea after I realized that current XID tends to change a lot faster than the pg_stats mechanism is designed to track. (Consider the half-second lag for starters...) Turning pg_stats into a realtime mechanism would be horridly expensive. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_locks view versus prepared transactions
Alvaro Herrera [EMAIL PROTECTED] writes: On Mon, Jun 20, 2005 at 03:55:45PM -0400, Merlin Moncure wrote: It is interesting to note that systems with stats disabled are unable to get lock owner information in this case (so what?). We could make the pg_stat_activity view show information from the ProcArray shared struct, when stats are disabled. pg_stat_activity is not real time, and should not be because its intended use is to help interpret the also-not-real-time other statistics. If people are concerned about this, my inclination would be to add a separate view, or just add even more columns to pg_locks? However, all you could get from ProcArray would be the database in which the backend is running, and maybe the owning user's ID if we cared to expend the extra space to store it there. We're certainly not going to add current_query or any such thing into that array. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Checkpointing problem with new buffer mgr.
Tom, The latest omit-the-hole change went in 2005-06-06 16:22 (EDT), so anything older than that is probably not representative. Looks like this was 5/29. Re-running the tests with current CVS now. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] index selection by query planner
Rohit, I have a table with two indices on the same column, one of which is a partial index. I would like the query planner to use the partial index whenever the query condition lies in the range of the partial index as it would yield better performance. Is there any way to enforce the ordering for the indices? How does the query planner decide which index to use when a particular query is fired? 'Explain Analyze' showed the total index being used in a situation that could be fulfiled by the partial index. Please post the query, the table and index definitions, and the EXPLAIN ANALYZE to the PGSQL-PERFORMANCE mailing list. Thanks! -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Issue with plpython write_file and read_file tests
Jim C. Nasby [EMAIL PROTECTED] writes: Setting up the buildfarm on a machine I've discovered an issue with the plpython write_file and read_file tests. The problem can be seen at http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=platypusdt=2005-06-20%2018:15:00 The specific issue is that the test doesn't clean up after itself, leaving /tmp/plpython behind. This raised it's ugly head when I tested the buildfarm stuff as one user and then switched to using a different user. What would be the best way to handle this? Remove that test altogether. It'd only be sane in a test of a trusted plpython language, which we have not got now. I would imagine that the original writer of the code never expected the write to succeed at all. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_locks view versus prepared transactions
On Mon, Jun 20, 2005 at 04:18:03PM -0400, Tom Lane wrote: However, all you could get from ProcArray would be the database in which the backend is running, and maybe the owning user's ID if we cared to expend the extra space to store it there. We're certainly not going to add current_query or any such thing into that array. You could show the current transaction Id, which is also useful. (Presently there's no way to know even a backend's own TransactionId, and people is suggested to use hacks like insert a row in a table and check its xmin.) Maybe we could add an adittional view, with all the info from ProcArray, which is useful sometimes. Then you could join that to pg_locks, and it would work even if the statistic collector is disabled. -- Alvaro Herrera (alvherre[a]surnet.cl) La felicidad no es mañana. La felicidad es ahora ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] default database creation with initdb
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Tom Lane wrote: If it's a server-side failure it should have a SQLSTATE code. Specifically, I'm talking about no pg_hba.conf entry for , ERRCODE_INVALID_AUTHORIZATION_SPECIFICATION Ident authentication failed.. (both server sice) Ditto. Do you need to know the difference? What exactly would client code do differently for these two cases? Display different hints how to cope with this. The hint will arise for newbies, and it would be confusing to discuss ident auth when a missing pg_hba.conf line is the problem, and vice versa. and Is the server running on host ... from libpq libpq doesn't currently assign SQLSTATEs to internally detected errors ... someday someone should fix that. Ok, I'm blind. Where in h**l do I find the SQLSTATE from a PGconn?!? Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] make distclean keeps some files
Playing with diffs, I've noticed, that after `make distclean` command some files are still present in the source tree. They're were not there before ./configure make. They are: src/backend/bootstrap/bootparse.c src/backend/bootstrap/bootscanner.c src/backend/bootstrap/bootstrap_tokens.h src/backend/parser/gram.c src/backend/parser/parse.h src/backend/parser/scan.c src/backend/utils/misc/guc-file.c src/bin/psql/psqlscan.c src/bin/psql/sql_help.h src/interfaces/ecpg/preproc/pgc.c src/interfaces/ecpg/preproc/preproc.c src/interfaces/ecpg/preproc/preproc.h src/interfaces/libpq/blibpqdll.def src/interfaces/libpq/libpqddll.def src/interfaces/libpq/libpqdll.def src/interfaces/libpq/libpq.rc src/pl/plpgsql/src/pl_gram.c src/pl/plpgsql/src/pl_scan.c src/pl/plpgsql/src/pl.tab.h Are they kept intentionally? -- Victor Y. Yegorov ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] query plan ignoring check constraints
On Mon, 2005-06-20 at 14:40 -0400, Alvaro Herrera wrote: On Mon, Jun 20, 2005 at 10:35:24AM -0700, Josh Berkus wrote: Rohit, Now, when I do a select on the basetable with a range of ids, it looks up each subtable that inherits from the base table and using an indexed scan searches for values in the range. It does it even for subtables whose check constraint completely rules out the possibility of it containing any such row . Should not check constraint act as the first filter? The index should ideally be scanned only when the check constraint is passed by the search criteria but surprisingly it did not happen. The explain analyze showed cost for index scans of subtables that cannot contain rows matching the search criteria. This is called range partitioning. We're working on it. You're welcome to join the Bizgres project where most of the discussion on this feature takes place: Why are you discussing development there? I can see in the archives that people are talking about changing page format, semantics of tuple info bits, and it's not getting to some people that matters. IRC, telephone, private mail and face-to-face have also been used to discuss development... Hackers has been used to discuss how to implement the ideas raised in other forums. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] make distclean keeps some files
Victor Y. Yegorov [EMAIL PROTECTED] writes: Are they kept intentionally? Yes. See maintainer-clean if you don't want 'em. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] query plan ignoring check constraints
Someone Wrote:
Should not check constraint act as the first filter? The index should
ideally be scanned only when the check constraint is passed by the
search
criteria but surprisingly it did not happen. The explain analyze
showed
cost for index scans of subtables that cannot contain rows matching
the
search criteria.
Obviously, indexes on columns with a check constraint, should be
qualified with the same check constraint.
test=# CREATE TABLE test (
foo text check(foo IN ('YES','NO'))
);
CREATE TABLE
test=# CREATE INDEX text_foo_idx ON test (foo) WHERE foo IN('YES','NO');
CREATE INDEX
test=# INSERT INTO test VALUES ('YES');
INSERT 280188 1
test=# INSERT INTO test VALUES ('NO');
INSERT 280189 1
test=# INSERT INTO test VALUES ('no');
ERROR: new row for relation test violates check constraint
test_foo_check
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'YES';
QUERY PLAN
Index Scan using text_foo_idx on test (cost=0.00..5.82 rows=7
width=32) (actual time=0.369..0.376 rows=1 loops=1)
Index Cond: (foo = 'YES'::text)
Total runtime: 0.490 ms
(3 rows)
test=# EXPLAIN ANALYZE SELECT * FROM test WHERE foo = 'no';
QUERY PLAN
Seq Scan on test (cost=0.00..25.38 rows=7 width=32) (actual
time=0.358..0.358 rows=0 loops=1)
Filter: (foo = 'no'::text)
Total runtime: 0.421 ms
(3 rows)
test=#
... John
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [HACKERS] [PATCHES] default database creation with initdb
Dave Page dpage@vale-housing.co.uk writes: I don't see that much of a problem with having createdb etc. hardwire postgres instead of template1 as the db-to-connect-to. OK, new patch posted to -patches that updates all the utilities as well. I'm going to apply this this evening (ie, before any of the code drifts under it) unless I hear squawks in the next hour or so, or find some fatal problem while reviewing. The only change I didn't make was in line 3458 (in StartChildProcess) of postmaster.c - template1 seemed the more sensible option to leave there. Let me know if you disagree and I'll change it :-) Will look at it. I'm sure there are a few references that *should* be template1 ... regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] query plan ignoring check constraints
This is called range partitioning. We're working on it. You're welcome to join the Bizgres project where most of the discussion on this feature takes place: www.bizgres.org http://pgfoundry.org/mail/?group_id=1000107 I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... Chris ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] query plan ignoring check constraints
KL- I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... What, like phpPgAdmin? ;-) -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] query plan ignoring check constraints
On Mon, 20 Jun 2005, Josh Berkus wrote: KL- I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... What, like phpPgAdmin? ;-) What on earth does phpPgAdmin have to do with the backend? I'm on the list and there's nothing happening there which doesn't belong here. Gavin ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] query plan ignoring check constraints
I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... What, like phpPgAdmin? ;-) Erm. Last time I checked phpPgAdmin was a userland application, using PHP and libpq. Bizgres is proposing modifying PostgreSQL itself and getting those changes into PostgreSQL proper. Please move your discussions to -hackers. I, and many other devs have no interest in subscribing to your own little list. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] query plan ignoring check constraints
Josh Berkus said: KL- I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... What, like phpPgAdmin? ;-) Josh, That is not an appropriate analogy at all - range partitioning is an inherently server-side feature, while phppgadmin is a client-side admin GUI. Chris' point as I understand it is that server-side features should be discussed on -hackers, and I have some sympathy with that POV. And if you feel like mentioning plperlng in this context, I will tell you that I wouldn't do it again that way in the light of experience, even though it is arguably far more separable. cheers andrew ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] query plan ignoring check constraints
Doh, sorry for coming off sounding like a knob here...my point is that it's not like you guys are some sort of rogue faction implementing range partitioning against the wishes of the Man - it's something that I think we all agree we want in the backend, so I don't see why you are making it hard to discuss and follow the project. Keeping it among yourselves is just a recipe for a bad case of group think... Chris Christopher Kings-Lynne wrote: I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... What, like phpPgAdmin? ;-) Erm. Last time I checked phpPgAdmin was a userland application, using PHP and libpq. Bizgres is proposing modifying PostgreSQL itself and getting those changes into PostgreSQL proper. Please move your discussions to -hackers. I, and many other devs have no interest in subscribing to your own little list. Chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] query plan ignoring check constraints
Christopher Kings-Lynne [EMAIL PROTECTED] writes: This is called range partitioning. We're working on it. You're welcome to join the Bizgres project where most of the discussion on this feature takes place: www.bizgres.org http://pgfoundry.org/mail/?group_id=1000107 I still think the fact that that discussion is taking place on a completely non-hackers mailing list is the lamest thing ever... Any discussions at the level of changing infomask bits definitely belong on -hackers. Do not be too surprised if you get an unfriendly reception when you post low-level changes to -patches that were never previously discussed on -hackers ... regards, tom lane ---(end of broadcast)--- TIP 3: 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] query plan ignoring check constraints
Folks, Any discussions at the level of changing infomask bits definitely belong on -hackers. Do not be too surprised if you get an unfriendly reception when you post low-level changes to -patches that were never previously discussed on -hackers ... Oh, I'm not expecting this to make it into 8.1 (Simon may feel different but I'll leave those explanations to him). That's part of the reason we're doing some of this work on -bizgres; I know from experience the difficulty of discussing anything which doesn't have to do with a release after feature freeze. The other main reason for the -bizgres list is to solicit opinions from people (such as the Mondrian team) who would not subscribe to -hackers. -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Schedule for 8.1 feature freeze
We have addressed all the open issues for 8.1 except for auto-vacuum, which Alvaro is working on, so I think we are ready for a feature freeze on July 1. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] default database creation with initdb
Dave Page dpage@vale-housing.co.uk writes: OK, new patch posted to -patches that updates all the utilities as well. Applied. One thing that neither Dave nor I wanted to touch is pg_autovacuum. If that gets integrated into the backend by feature freeze then the question is moot, but if it doesn't then we'll have to decide whether autovac should preferentially connect to template1 or postgres. Neither choice seems real appealing to me: if autovac connects to template1 then it could interfere with CREATE DATABASE, but if it connects to postgres then it could fail if postgres isn't there. Now the latter does not bother me if autovac is considered a client, but it does bother me if autovac is considered part of the backend. I think that template1 and template0 can reasonably be considered special from the point of view of the backend --- but I really don't want postgres to be special in that way. Another point is that Dave added code to pg_dumpall to not dump the postgres database. This seems mistaken to me, so I did not include it in the applied patch: if someone is doing real work in postgres then they'll be pretty annoyed if it's not backed up. But perhaps the question needs debate. Any thoughts? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Autovacuum Patch Status
Sorry to do this on the hackers list, but I have tried to email Alvaro off-list and my email keeps getting bounced so Alvaro, I was just wondering what the current status of your work with the Autovacuum patch is. Also, if you would like to discuss anything and also if I can help you. My time is limited but I can help think thinks through / help with design issues. Also I can probably contribute a few hours of actual coding time this week / this coming weekend. Anyway, I know there was a lot of conversation on the hackers list and I just wanted to see how you were doing. Thanks, Matthew O'Connor ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Escape handling in strings
[ BCC to general. ] I have received very few replies to my suggestion that we implement E'' for escaped strings, so eventually, after a few major releases, we can have '' treat backslashes literally like the SQL standard requires. I assume this is because most people say, yea, it is going to be a pain, and yea, we should probably do it. A summary of the plan is at: http://candle.pha.pa.us/cgi-bin/pgescape Therefore, I will soon apply the escape patch at: ftp://candle.pha.pa.us/pub/postgresql/mypatches/escape I will also backpatch the E'' syntax and read-only GUC variables to earlier releases. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(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] Escape handling in strings
Bruce Momjian wrote: I have received very few replies to my suggestion that we implement E'' for escaped strings, so eventually, after a few major releases, we can have '' treat backslashes literally like the SQL standard requires. Just checking: with this plan, a client needs to know what server version is in use to correctly escape strings, correct? That is, there is no escape mechanism that works correctly for both old and new servers? -O ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] O_DIRECT for WAL writes
Hi all, O_DIRECT for WAL writes was discussed at http://archives.postgresql.org/pgsql-patches/2005-06/msg00064.php but I have some items that want to be discussed, so I would like to re-post it to HACKERS. Bruce Momjian pgman@candle.pha.pa.us wrote: I think the conclusion from the discussion is that O_DIRECT is in addition to the sync method, rather than in place of it, because O_DIRECT doesn't have the same media write guarantees as fsync(). Would you update the patch to do and see if there is a performance win? I tested two combinations, - fsync_direct: O_DIRECT+fsync() - open_direct: O_DIRECT+O_SYNC to compare them with O_DIRECT on my linux machine. The pgbench results still shows a performance win: scale| DBsize | open_sync | fsync=false | O_DIRECT only| fsync_direct | open_direct -++---+--+--+--+--- 10 | 150MB | 252.6 tps | 263.5(+ 4.3%)| 253.4(+ 0.3%)| 253.6(+ 0.4%)| 253.3(+ 0.3%) 100 | 1.5GB | 102.7 tps | 117.8(+14.7%)| 147.6(+43.7%)| 148.9(+45.0%)| 150.8(+46.8%) 60runs * pgbench -c 10 -t 1000 on one Pentium4, 1GB mem, 2 ATA disks, Linux 2.6.8 O_DIRECT, fsync_direct and open_direct show the same tendency of performance. There were a win on scale=100, but no win on scale=10, which is a fully in-memory benchmark. The following items still want to be discussed: - Are their names appropriate? Simplify to 'direct'? - Are both fsync_direct and open_direct necessary? MySQL seems to use only O_DIRECT+fsync() combination. - Is it ok to set the dio buffer alignment to BLCKSZ? This is simple way to set the alignment to match many environment. If it is not enough, BLCKSZ would be also a problem for direct io. BTW, IMHO the major benefit of direct io is saving memory. O_DIRECT gives a hint that OS should not cache WAL files. Without direct io, OS might make a effort to cache WAL files, which will never be used, and might discard data file cache. --- ITAGAKI Takahiro NTT Cyber Space Laboratories ---(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] default database creation with initdb
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED] Sent: 20 June 2005 03:46 To: Andreas Pflug Cc: Dave Page; Robert Treat; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb Andreas Pflug [EMAIL PROTECTED] writes: Can't tell whether I could find time for reviewing the docs the next days (more interesting for feature freeze is having fixed the implementation anyway). Of the sixty-odd files that mention template1 in current CVS, only about half are documentation. If you think a patch that patches only initdb is enough to get this feature in, you are very mistaken ... even if we were inclined to accept patches that blatantly omit documentation, which as a rule we do not. ... And rightly so imho :-). I will spend some time on this today. Regards, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] default database creation with initdb
Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Can't tell whether I could find time for reviewing the docs the next days (more interesting for feature freeze is having fixed the implementation anyway). Of the sixty-odd files that mention template1 in current CVS, only about half are documentation. The decision which files should be changed must be taken. e.g. createdb, dropdb will use template1 hardcoded. Is it acceptable that those tools fail if the postgres database isn't present any more? Regards, Andreas ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] default database creation with initdb
-Original Message- From: Andreas Pflug [mailto:[EMAIL PROTECTED] Sent: 20 June 2005 10:14 To: Tom Lane Cc: Dave Page; Robert Treat; Magnus Hagander; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [PATCHES] default database creation with initdb Tom Lane wrote: Andreas Pflug [EMAIL PROTECTED] writes: Can't tell whether I could find time for reviewing the docs the next days (more interesting for feature freeze is having fixed the implementation anyway). Of the sixty-odd files that mention template1 in current CVS, only about half are documentation. The decision which files should be changed must be taken. e.g. createdb, dropdb will use template1 hardcoded. Is it acceptable that those tools fail if the postgres database isn't present any more? That's what I'm working on atm, and given Tom's previous comment about small-footprint users not wanting an extra 5/6MB on the size of a new cluster, I'm leaving most things using template1 and mainly just updating docs and examples. 'postgres' can then be dropped with no ill effects other than a return to the old template1 etc. issues. Regards, Dave. ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
