Re: [HACKERS] [pgsql-hackers-win32] Sync vs. fsync during checkpoint
Bruce Momjian wrote: > Here is my new idea. (I will keep throwing out ideas until I hit on a > good one.) The bgwriter it going to have to check before every write to > determine if the file is already recorded as needing fsync during > checkpoint. My idea is to have that checking happen during the bgwriter > buffer scan, rather than at write time. if we add a shared memory > boolean for each buffer, backends needing to write buffers can writer > buffers already recorded as safe to write by the bgwriter scanner. I > don't think the bgwriter is going to be able to keep up with I/O bound > backends, but I do think it can scan and set those booleans fast enough > for the backends to then perform the writes. (We might need a separate > bgwriter thread to do this or a separate process.) That seems a bit excessive. It seems to me that contention is only a problem if you keep a centralized list of files that have been written by all the backends. So don't do that. Instead, have each backend maintain its own separate list in shared memory. The only readers of a given list would be the backend it belongs to and the bgwriter, and the only time bgwriter attempts to read the list is at checkpoint time. At checkpoint time, for each backend list, the bgwriter grabs a write lock on the list, copies it into its own memory space, truncates the list, and then releases the read lock. It then deletes the entries out of its own list that have entries in the backend list it just read. It then fsync()s the files that are left, under the assumption that the backends will fsync() any file they write to directly. The sum total size of all the lists shouldn't be that much larger than it would be if you maintained it as a global list. I'd conjecture that backends that touch many of the same files are not likely to be touching a large number of files per checkpoint, and those systems that touch a large number of files probably do so through a lot of independent backends. One other thing: I don't know exactly how checkpoints are orchestrated between individual backends, but it seems clear to me that you want to do a sync() *first*, then the fsync()s. The reason is that sync() allows the OS to order the writes across all the files in the most efficient manner possible, whereas fsync() only takes care of the blocks belonging to the file in question. This won't be an option under Windows, but on Unix systems it should make a difference. On Linux it should make quite a difference, since its sync() won't return until the buffers have been flushed -- and then the following fsync()s will return almost instantaneously since their data has already been written (so there won't be any dirty blocks in those files). I suppose it's possible that on some OSes fsync()s could interfere with a running sync(), but for those OSes we can just drop back do doing only fsync()s. As usual, I could be completely full of it. Take this for what it's worth. :-) -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [pgsql-hackers-win32] Sync vs. fsync during checkpoint
Some Moron at sysexperts.com wrote: > At checkpoint time, for each backend list, the bgwriter grabs a write > lock on the list, copies it into its own memory space, truncates the > list, and then releases the read lock. Sigh. I meant to say that it then releases the *write* lock. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] COPY from question
Slavisa Garic wrote: > Using pg module in python I am trying to run the COPY command to populate > the large table. I am using this to replace the INSERT which takes about > few hours to add 7 entries where copy takes minute and a half. That difference in speed seems quite large. Too large. Are you batching your INSERTs into transactions (you should be in order to get good performance)? Do you have a ton of indexes on the table? Does it have triggers on it or some other thing (if so then COPY may well wind up doing the wrong thing since the triggers won't fire for the rows it inserts)? I don't know what kind of schema you're using, but it takes perhaps a couple of hours to insert 2.5 million rows on my system. But the rows in my schema may be much smaller than yours. -- Kevin Brown [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] session IDs
[note change of subject] I wrote: Tom Lane wrote: Andrew Dunstan <[EMAIL PROTECTED]> writes: I did think about using a cluster-wide sequence, if we can make such a thing (might also be useful for system generated UIDs too). Not a good idea IMHO. If you do that, then there will be no such thing as a purely read-only transaction, because *every* transaction will include a nextval() call. That means even read-only transactions cannot commit till the disk spins. If we want a unique id for transient purposes like logging, then make some kind of counter in shared memory. Don't use a sequence, it's much too heavyweight. I'm not sure I understand. I didn't suggest that a sequence should be used for txn ids. For the purpose I had in mind we would call nextval() once per connection, and, for the other purpose where I suggested it would be useful, once per "create user". That doesn't seem very heavyweight. If we really want a loggable session id then ISTM it should be not transient at all, but in fact unique even across server restart. One moderately simple scheme that occurred to me is to have to postmaster keep a 64 bit counter, initialised by a call to gettimeofday(), and bumped on every connection. The postmaster would just put the new counter value into the port structure for the backend (and in the exec case it would be written out and then read back by the backend, along with the other port stuff set by postmaster). No need for a persistent sequence or for shared memory, and it would be unique unless time went backwards by exactly the right amount between server starts (you do run ntp on your machines, don't you?). I am less sure of the utility of such an ID, though. After all, if you see a disconnect log message for a given PID you must know that any reuse of that PID indicates a new session, or even if you just see a connection message you know it must be a new session. OTOH, having a unique SessionID might simplify the logic required of log analysis tools. cheers andrew ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] pg_restore bug in 7.4.1 ?
> Fabien COELHO <[EMAIL PROTECTED]> writes: > > It would make sense to ignore some alter/drop errors in pg_restore. > > [...] > > This issue has been on the radar screen for awhile, but no one has > gotten around to making it happen... Maybe it could be appended to the "todo" list, so as not to be too far away from sight. Otherwise the sonar will find it again... -- Fabien Coelho - [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] session IDs
> > > > Tom Lane wrote: > > > >> Andrew Dunstan <[EMAIL PROTECTED]> writes: > >> > >>> I did think about using a cluster-wide sequence, if we can make such > >>> a thing (might also be useful for system generated UIDs too). > >> > >> Not a good idea IMHO. If you do that, then there will be no such thing > >> as a purely read-only transaction, because *every* transaction will > >> include a nextval() call. That means even read-only transactions cannot > >> commit till the disk spins. > >> A sequence could be used if it was created with a sufficiently large CACHE value, so a read only transaction would only have to hit the disk if it happened to be the one to hit an exhausted cache. Kris Jurka ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] log session end - again
Andrew Dunstan <[EMAIL PROTECTED]> writes: > I'm not sure I understand. I didn't suggest that a sequence should be > used for txn ids. For the purpose I had in mind we would call nextval() > once per connection, Oh, okay, I misunderstood. But why not just use the PID? 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] [pgsql-hackers-win32] Sync vs. fsync during checkpoint
Kevin Brown <[EMAIL PROTECTED]> writes: > Instead, have each backend maintain its own separate list in shared > memory. The only readers of a given list would be the backend it belongs > to and the bgwriter, and the only time bgwriter attempts to read the > list is at checkpoint time. > The sum total size of all the lists shouldn't be that much larger than > it would be if you maintained it as a global list. I fear that is just wishful thinking. Consider the system catalogs as a counterexample of files that are likely to be touched/modified by many different backends. The bigger problem though with this is that it makes the problem of list overflow much worse. The hard part about shared memory management is not so much that the available space is small, as that the available space is fixed --- we can't easily change it after postmaster start. The more finely you slice your workspace, the more likely it becomes that one particular part will run out of space. So the inefficient case where a backend isn't able to insert something into the appropriate list will become considerably more of a factor. > but it seems clear to me that you want to do > a sync() *first*, then the fsync()s. Hmm, that's an interesting thought. On a machine that's doing a lot of stuff besides running the database, a global sync would be counterproductive --- but we could easily make it configurable as to whether to issue the sync() or not. It wouldn't affect correctness. 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] COPY from question
Kevin Brown <[EMAIL PROTECTED]> writes: > Slavisa Garic wrote: >> Using pg module in python I am trying to run the COPY command to populate >> the large table. I am using this to replace the INSERT which takes about >> few hours to add 7 entries where copy takes minute and a half. > That difference in speed seems quite large. Too large. Are you batching > your INSERTs into transactions (you should be in order to get good > performance)? Do you have a ton of indexes on the table? Does it have > triggers on it or some other thing (if so then COPY may well wind up doing > the wrong thing since the triggers won't fire for the rows it > inserts)? COPY *does* fire triggers, and has done so for quite a few releases. My bet is that the issue is failing to batch individual INSERTs into transactions. On a properly-set-up machine you can't get more than one transaction commit per client per disk revolution, so the penalty for trivial transactions like single inserts is pretty steep. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] log session end - again
Tom Lane said: > Andrew Dunstan <[EMAIL PROTECTED]> writes: >> I'm not sure I understand. I didn't suggest that a sequence should be >> used for txn ids. For the purpose I had in mind we would call >> nextval() once per connection, > > Oh, okay, I misunderstood. But why not just use the PID? > Bruce and others have suggested that PID is not sufficiently unique. Personally, I can live with it :-) cheers andrew ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] session IDs
Andrew Dunstan wrote: > I am less sure of the utility of such an ID, though. After all, if > you see a disconnect log message for a given PID you must know that > any reuse of that PID indicates a new session, or even if you just > see a connection message you know it must be a new session. OTOH, > having a unique SessionID might simplify the logic required of log > analysis tools. The PID *is* a unique session ID. Why is it not sufficient? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] session IDs
Peter Eisentraut wrote:
Andrew Dunstan wrote:
I am less sure of the utility of such an ID, though. After all, if
you see a disconnect log message for a given PID you must know that
any reuse of that PID indicates a new session, or even if you just
see a connection message you know it must be a new session. OTOH,
having a unique SessionID might simplify the logic required of log
analysis tools.
The PID *is* a unique session ID. Why is it not sufficient?
It's unique for the duration of the session, but it won't be for logs
covering a sufficient period of time, because PIDs are reused, in some
cases not even by cycling but being allocated randomly.
As I said elsewhere, I can live with that, but others wanted something
that was more unique (if such a term has meaning ;-)
cheers
andrew ("You are unique. Just like everybody else.")
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Idea about better configuration options for sort memory
scott.marlowe wrote: > On Mon, 2 Feb 2004, Tom Lane wrote: > > > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > > any chance of having some kind of max_total_sort_mem setting to keep > > > machines out of swap storms, or would that be a nightmare to implement? > > > > I don't see any reasonable way to do that. > > I didn't think there was. just hoping... :-) Someone asked for this in Copenhagen, and I said we can't see how to do it. The only idea I had as to give the first requestor 50% of the total, then a second query 50% of the remaining memory. Is that better than what we have? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 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] log session end - again
Andrew Dunstan wrote: > Tom Lane said: > > Andrew Dunstan <[EMAIL PROTECTED]> writes: > >> I'm not sure I understand. I didn't suggest that a sequence should be > >> used for txn ids. For the purpose I had in mind we would call > >> nextval() once per connection, > > > > Oh, okay, I misunderstood. But why not just use the PID? > > > > Bruce and others have suggested that PID is not sufficiently unique. > Personally, I can live with it :-) The nice things about using xid for session id is that is is unique for a long time, rather than pid. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_restore bug in 7.4.1 ?
Fabien COELHO wrote: > > > Fabien COELHO <[EMAIL PROTECTED]> writes: > > > It would make sense to ignore some alter/drop errors in pg_restore. > > > > [...] > > > > This issue has been on the radar screen for awhile, but no one has > > gotten around to making it happen... > > Maybe it could be appended to the "todo" list, so as not to be too far > away from sight. Otherwise the sonar will find it again... If people want this, can I get some wording? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Idea about better configuration options for sort memory
Bruce Momjian <[EMAIL PROTECTED]> writes: >> I didn't think there was. just hoping... :-) > Someone asked for this in Copenhagen, and I said we can't see how to do > it. The only idea I had as to give the first requestor 50% of the > total, then a second query 50% of the remaining memory. Is that better > than what we have? How would you do that --- who's the "first requestor"? The delay between planning and execution for prepared statements (including plpgsql functions) seems to make it impossible to do anything useful in terms of dynamic allocation of memory. What would be more reasonable to try for is a per-query upper limit on space consumption. That at least avoids any concurrency issues and reduces it to a pure planning problem. However, I don't see any real good way to do that either. With the bottom-up planning process we use, the cost of (say) a first-level sort must be assigned before we know whether any additional sorts or hashes will be needed at upper levels. I thought a little bit about assuming that one workspace would be needed per input relation --- that is, if there are N relations in the query then set SortMem to TotalQueryMem/N. But this would severely penalize plans that need fewer workspaces than that. Another tack is to let the planner assume SortMem per workspace but at executor start (where we could know the number of plan nodes that actually need workspaces) set the effective SortMem to TotalQueryMem/N. The trouble with this is you could end up with a severely nonoptimal plan, eg a sort or hash being done in much too little space. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] session IDs
--On Tuesday, February 03, 2004 11:12:03 -0500 Andrew Dunstan
<[EMAIL PROTECTED]> wrote:
Peter Eisentraut wrote:
Andrew Dunstan wrote:
I am less sure of the utility of such an ID, though. After all, if
you see a disconnect log message for a given PID you must know that
any reuse of that PID indicates a new session, or even if you just
see a connection message you know it must be a new session. OTOH,
having a unique SessionID might simplify the logic required of log
analysis tools.
The PID *is* a unique session ID. Why is it not sufficient?
It's unique for the duration of the session, but it won't be for logs
covering a sufficient period of time, because PIDs are reused, in some
cases not even by cycling but being allocated randomly.
As I said elsewhere, I can live with that, but others wanted something
that was more unique (if such a term has meaning ;-)
How about pid+unix time of start of backend?
LER
cheers
andrew ("You are unique. Just like everybody else.")
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
pgp0.pgp
Description: PGP signature
Re: [HACKERS] Why has postmaster shutdown gotten so slow?
Tom Lane wrote: Shutdown of an idle postmaster used to take about two or three seconds (mostly due to the sync/sleep(2)/sync in md_sync). For the last couple of days it's taking more like a dozen seconds. I presume somebody broke something, but I'm unsure whether to pin the blame on bgwriter or Windows changes. Anyone care to fess up? I guess it could well be the bgwriter, which when having nothing to do at all is sleeping for 10 seconds. Not sure, will check. 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: 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] pg_restore bug in 7.4.1 ?
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > If people want this, can I get some wording? > > * Make pg_restore continue after errors, so it acts more like pg_dump scripts Added. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (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 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] log session end - again
Bruce Momjian <[EMAIL PROTECTED]> writes: > Andrew Dunstan wrote: >> Bruce and others have suggested that PID is not sufficiently unique. > The nice things about using xid for session id is that is is unique for > a long time, rather than pid. Hmm. Now that I think about it, InitPostgres() always runs a transaction during backend startup. If we simply saved aside the XID assigned to that transaction, it would cost next to nothing to do and would provide a reasonably unique ID. However, this happens much later than the postmaster currently prints the log_connection message ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] pg_restore bug in 7.4.1 ?
Bruce Momjian <[EMAIL PROTECTED]> writes: > If people want this, can I get some wording? * Make pg_restore continue after errors, so it acts more like pg_dump scripts regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Seaching without accents
Hi Augusto, > How can I configure postgreSQL to search without acents? > Is PostgreSQL have this support? > There isn't a specific function in postgresql. Maybe you could implement it in PL/Perl or any other PL/* language. > SELECT * FROM test WHERE name LIKE _tes%_ > > And it should return values _tést, test_ > It's not a good idea to use _ to replace accents 'cause it would match undesirable expressions like tost, tist, tust, txst, etc. -- Euler Taveira de Oliveira euler (at) ufgnet.ufg.br Desenvolvedor Web e Administrador de Sistemas UFGNet - Universidade Federal de Goiás ---(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] log session end - again
Tom Lane wrote: Bruce Momjian <[EMAIL PROTECTED]> writes: Andrew Dunstan wrote: Bruce and others have suggested that PID is not sufficiently unique. The nice things about using xid for session id is that is is unique for a long time, rather than pid. Hmm. Now that I think about it, InitPostgres() always runs a transaction during backend startup. If we simply saved aside the XID assigned to that transaction, it would cost next to nothing to do and would provide a reasonably unique ID. However, this happens much later than the postmaster currently prints the log_connection message ... Right. And if we have sessionids we would want them logged there, I think. And that would rule out anything based on xid or backend pid. OTOH I am quite sure I can implement the time based counter idea I suggested earlier with a very small amount of disturbance and very low cost. Unless someone can see a flaw in the scheme. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: PostGIS dropgeometrycolumn function (Was: Re: [HACKERS] [7.4]
One of the great annoyances of the OpenGIS spec is the requirement for a "geometry_columns" table, that has a list of all the spatial columns and a little bit of metadata on them (what type are they, what is the spatial reference system of their coordinates, what is their dimensionality). Unfortunately, we have been reduced to "manually" maintaining this table through the mechanism of the "AddGeometryColumn" "DropGeometryColumn" functions. As you noted, we had some old scruft in there dating back to the pre-DROP COLUMN days. That's gone in the current version. In an idea world though, we would construct the thing as a view, so that when you did a CREATE TABLE that included a geometry type, you would automatically get a row in geometry_columns. That requires a view on system tables though, and that just does not work. :/ Any thoughts on a nice implementation? Paul On Tuesday, January 20, 2004, at 09:07 AM, Tom Lane wrote: "Marc G. Fournier" <[EMAIL PROTECTED]> writes: So, if we replace that with: ALTER TABLE table_name ALTER column_name DROP NOT NULL; ? should be good to go? still not as clean as doing the straight DROP COLUMN, but its a fast fix ... Yeah, that's what I'd do until the PostGIS guys can rethink things at a higher level. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] Paul Ramsey Refractions Research Email: [EMAIL PROTECTED] Phone: (250) 885-0632 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: PostGIS dropgeometrycolumn function (Was: Re: [HACKERS] [7.4] "permissions problem" with pl/pgsql function )
Paul Ramsey <[EMAIL PROTECTED]> writes: > In an idea world though, we would construct the thing as a view, so > that when you did a CREATE TABLE that included a geometry type, you > would automatically get a row in geometry_columns. That requires a view > on system tables though, and that just does not work. :/ Uh, what makes you say it doesn't work? regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
[HACKERS] Multicolumn Indexing using R-Tree
Hello ! I'm using PostgreSQL 7.3.2 and I need to create a R-Tree index that uses more than one column in a table. When I run the instruction it appears this message bellow: DefineIndex: access method "rtree" does not support multi-column indexes How can I solve this problem ? Is it a limitation of PostgreSQL or the R-Tree concept ? Thank you in advance. Márcio Caetano. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Idea about better configuration options for sort
> > > "scott.marlowe" <[EMAIL PROTECTED]> writes:
> > > > any chance of having some kind of max_total_sort_mem setting to keep
> > > > machines out of swap storms, or would that be a nightmare to implement?
> Someone asked for this in Copenhagen, and I said we can't see how to do
> it. The only idea I had as to give the first requestor 50% of the
> total, then a second query 50% of the remaining memory. Is that better
> than what we have?
Lets look at it from another direction. The goal isn't to set a maximum
memory amount, but to avoid swapping.
Add a toggle to PostgreSQL that says (essentially) "I am the only
resource intensive program running".
If this was done, could we not work closer with the kernel? Ask the
kernel how much Free + Buffer memory there is, knock it down by 75% and
use that for our sort memory value (total sort memory for individual
backend -- not operation).
--
Rod Taylor
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
PGP Key: http://www.rbt.ca/rbtpub.asc
signature.asc
Description: This is a digitally signed message part
Re: PostGIS dropgeometrycolumn function (Was: Re: [HACKERS] [7.4]
Bitter experience... I am going to cc Dave here, because I could swear we went through many conniptions trying to make this work. And yet I just did this: create view mytables as select relname from pg_class where relam = 0 and relname not like 'pg_%'; And it seems to work fine. Oh, now I remember. The deal was not views, it was triggers. Since our geometry_columns contains some information not available via a query on existing data, a trigger was what we wanted, so we could harvest the information from a variety of places, and have some spare columns for things like the geometry selectivity stats. Paul On Tuesday, February 3, 2004, at 11:00 AM, Tom Lane wrote: Paul Ramsey <[EMAIL PROTECTED]> writes: In an idea world though, we would construct the thing as a view, so that when you did a CREATE TABLE that included a geometry type, you would automatically get a row in geometry_columns. That requires a view on system tables though, and that just does not work. :/ Uh, what makes you say it doesn't work? regards, tom lane Paul Ramsey Refractions Research Email: [EMAIL PROTECTED] Phone: (250) 885-0632 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Turkish Locale in Identifiers (contd.)
> Tom Lane <[EMAIL PROTECTED]> wrote: > "Nicolai Tufar" <[EMAIL PROTECTED]> writes: > >> A possible compromise is to apply ASCII downcasing (same as in > >> keywords.c) for 7-bit-ASCII characters, and apply tolower() only > >> for character codes above 127. In other words > > > If we go this way why not make a special case only and only for 'I' > > Character and not all 7-bit ASCII: > > It seems to me that that's too narrow a definition of the problem. > I think we should state our goal as "we don't want bizarre locale > definitions to interfere with downcasing of the basic ASCII letters". > If we put in a special case for 'I' we will fix the known problem > with Turkish, but what other strange locales might be out there? > And if we don't trust tolower() for 'I', why should we trust it > for 'A'-'Z'? Since nobody commented on the issue I may suggest a patch that implements 'I' special case solution. 'A'-'Z' ASCII-only downcasting idea was rejected before on basis of SQL99 compliance. I hope I would have more luck with this one. Because PostgreSQL just does not work with Turkish locale, and it was so since 7.4.0. initdb just chokes on VOID identifier and quits. Devrim Gunduz will second me on this, I am sure. With my knowledge of Russian, Arabic and -to some degree- Hebrew encodings I claim that this patch will not break them. If someone who uses far eastern Encodings would also check it, I think it would be pretty safe to apply this patch to the source. Thanks, Nicolai Tufar ---(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] Multicolumn Indexing using R-Tree
Try contrib/rtree_gist Marcio Caetano wrote: Hello ! I'm using PostgreSQL 7.3.2 and I need to create a R-Tree index that uses more than one column in a table. When I run the instruction it appears this message bellow: DefineIndex: access method "rtree" does not support multi-column indexes How can I solve this problem ? Is it a limitation of PostgreSQL or the R-Tree concept ? Thank you in advance. Márcio Caetano. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings -- Teodor Sigaev E-mail: [EMAIL PROTECTED] ---(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] Multicolumn Indexing using R-Tree
Try using GiST rtree (examples in contrib), GiST supports multi-key indexes. On Tuesday, February 3, 2004, at 06:56 AM, Marcio Caetano wrote: I'm using PostgreSQL 7.3.2 and I need to create a R-Tree index that uses more than one column in a table. When I run the instruction it appears this message bellow: DefineIndex: access method "rtree" does not support multi-column indexes How can I solve this problem ? Is it a limitation of PostgreSQL or the R-Tree concept ? Thank you in advance. Márcio Caetano ---(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: PostGIS dropgeometrycolumn function (Was: Re: [HACKERS] [7.4] "permissions problem" with pl/pgsql function )
Paul Ramsey <[EMAIL PROTECTED]> writes: > Oh, now I remember. The deal was not views, it was triggers. Oh, okay. You're right, we don't do triggers on system tables. But couldn't you combine a view on the system tables with storage of additional data outside? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] PITR Dead horse?
Has this been beaten to death now? Just curious if PITR was in Dev tree yet. Been out of the loop. TIA. -- Austin Gonyou <[EMAIL PROTECTED]> Coremetrics, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PostGIS Integration
Actually, in my wet dream, we stored everything in system tables. Dimensionality and SRID became parameters of the geometry, the selectivity stats lived in the system stats table (as Mark's patch should hopefully do) and the geometry_columns view just pulled everything together into one user-convenient location. CREATE TABLE foo ( mygeom POLYGON(4326) ); CREATE TABLE bar ( mygeom MULTILINESTRING(20711, 2 ) ); I think we had this discussion before though, and the "parameterized" types, like varchar(256), were not available for extended types, like our geometries. P. On Tuesday, February 3, 2004, at 12:06 PM, Tom Lane wrote: Paul Ramsey <[EMAIL PROTECTED]> writes: Oh, now I remember. The deal was not views, it was triggers. Oh, okay. You're right, we don't do triggers on system tables. But couldn't you combine a view on the system tables with storage of additional data outside? regards, tom lane Paul Ramsey Refractions Research Email: [EMAIL PROTECTED] Phone: (250) 885-0632 ---(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] Idea about better configuration options for sort
>Rod Taylor writes > > > > "scott.marlowe" <[EMAIL PROTECTED]> writes: > > > > > any chance of having some kind of max_total_sort_mem setting to > keep > > > > > machines out of swap storms, or would that be a nightmare to > implement? > > > Someone asked for this in Copenhagen, and I said we can't see how to do > > it. The only idea I had as to give the first requestor 50% of the > > total, then a second query 50% of the remaining memory. Is that better > > than what we have? > > Lets look at it from another direction. The goal isn't to set a maximum > memory amount, but to avoid swapping. I very much like your high level thinking, though on balance, I personally do want to control the maximum memory allocation. It seems to me that in general, there are just too many possibilities for what you might want to mix on the same system. Perhaps we should restate the goal slightly as being "maximising performance, whilst minimizing the RISK of swapping". An alternate suggestion might be a max_instance_mem setting, from which all other memory allocations by that postgresql server were derived. That way, however the "black box" operates, you have a single, well-defined control point that will allow you to be as generous as you see fit, but no further. [There's probably a few views on the instance/database etc thing... I'm happy with more than one control point - the name is less relevant] You can always write a script to calculate the setting of this as a percentage of physical memory if you want to do this automatically. The suggestion about using percentages as relative rather than absolute memory allocation has definitely been used successfully in the past on other software systems. ...not the half-again each time method, but assigning memory as a percentage of whatever's allocated. That way you can raise the limit without changing everything else. Best regards, Simon Riggs ---(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] Turkish Locale in Identifiers (contd.)
Oops, forgot the patch :) > -Original Message- > From: [EMAIL PROTECTED] [mailto:pgsql-hackers- > [EMAIL PROTECTED] On Behalf Of Nicolai Tufar > Sent: Tuesday, February 03, 2004 9:31 PM > To: [EMAIL PROTECTED] > Cc: 'Tom Lane'; [EMAIL PROTECTED] > Subject: [HACKERS] Turkish Locale in Identifiers (contd.) > > > Tom Lane <[EMAIL PROTECTED]> wrote: > > "Nicolai Tufar" <[EMAIL PROTECTED]> writes: > > >> A possible compromise is to apply ASCII downcasing (same as in > > >> keywords.c) for 7-bit-ASCII characters, and apply tolower() only > > >> for character codes above 127. In other words > > > > > If we go this way why not make a special case only and only for 'I' > > > Character and not all 7-bit ASCII: > > > > It seems to me that that's too narrow a definition of the problem. > > I think we should state our goal as "we don't want bizarre locale > > definitions to interfere with downcasing of the basic ASCII letters". > > If we put in a special case for 'I' we will fix the known problem > > with Turkish, but what other strange locales might be out there? > > And if we don't trust tolower() for 'I', why should we trust it > > for 'A'-'Z'? > > Since nobody commented on the issue I may suggest a patch that > implements > 'I' special case solution. 'A'-'Z' ASCII-only downcasting idea was > rejected > before on basis of SQL99 compliance. I hope I would have more luck with > this > one. Because PostgreSQL just does not work with Turkish locale, and it > was > so since 7.4.0. initdb just chokes on VOID identifier and quits. Devrim > Gunduz will second me on this, I am sure. > > With my knowledge of Russian, Arabic and -to some degree- Hebrew > encodings > I claim that this patch will not break them. If someone who uses far > eastern > Encodings would also check it, I think it would be pretty safe to apply > this patch to the source. > > Thanks, > Nicolai Tufar > > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) tr20040203.diff Description: Binary data ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] PostGIS Integration
Paul Ramsey <[EMAIL PROTECTED]> writes: > I think we had this discussion before though, and the "parameterized" > types, like varchar(256), were not available for extended types, like > our geometries. I can't see any way to handle parameterized types without extending the grammar individually for each one --- otherwise it's too hard to tell them apart from function calls. That makes it a bit hard to do 'em as plug-ins :-(. The grammar hacks are certainly ugly though, and if someone could think of a way, I'm all ears... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PATCHES] log session end - again
Andrew Dunstan <[EMAIL PROTECTED]> writes: > Right. And if we have sessionids we would want them logged there, I > think. And that would rule out anything based on xid or backend pid. Uh, what's wrong with backend pid? Since we fork before we start doing anything with a connection, it should surely be available soon enough for the connection log message. Larry's idea about combining PID and backend start time didn't sound too unreasonable to me. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] PITR Dead horse?
Austin Gonyou <[EMAIL PROTECTED]> writes: > Has this been beaten to death now? Just curious if PITR was in Dev tree > yet. Been out of the loop. TIA. Nope... I've got some patches from Patrick Macdonald and JR Nield that I need to integrate, but I believe those only cover some low-level changes to the WAL log contents. There's a lot of management code yet to be written. regards, tom lane ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] [PATCHES] log session end - again
Tom Lane said: > Andrew Dunstan <[EMAIL PROTECTED]> writes: >> Right. And if we have sessionids we would want them logged there, I >> think. And that would rule out anything based on xid or backend pid. > > Uh, what's wrong with backend pid? Since we fork before we start doing > anything with a connection, it should surely be available soon enough > for the connection log message. > > Larry's idea about combining PID and backend start time didn't sound > too unreasonable to me. > It did to me too. I was just looking at this code 2 days ago. Somehow when I looked again I thought we logged connections in the postmaster. Larry's idea should work just fine. For log compactness I think I'd hexencode it - the SessionID doesn't need any semantics, it's just like a cookie. cheers andrew ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Recursive queries?
Is there anyone working on recursive queries for 7.5? I know there is a patch that implements it on 7.4 (I can't seem to find the guy's webpage), but that uses Oracle syntax. Wasn't there some guy at RedHat doing it? Is RedHat working on PITR? Chris ---(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] BgWriter sync option backed out
I have backed out the background writer sync option. Tom's concerns where right and my test results have been very questionable. 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: 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] PITR Dead horse?
> Has this been beaten to death now? Just curious if PITR was in Dev tree > yet. Been out of the loop. TIA. I and my co workers are very interested in implementing PITR. We will tackle this for 7.5 if no one objects. -- Tatsuo Ishii ---(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] PITR Dead horse?
I and some other developers are also interested in. Do you think we can work together? Tatsuo Ishii <[EMAIL PROTECTED]> wrote: > > Has this been beaten to death now? Just curious if PITR was in Dev tree > > yet. Been out of the loop. TIA. > > I and my co workers are very interested in implementing PITR. We will > tackle this for 7.5 if no one objects. > -- > Tatsuo Ishii > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > -- NAGAYASU Satoshi <[EMAIL PROTECTED]> ---(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] Recursive queries?
Christopher Kings-Lynne <[EMAIL PROTECTED]> writes: > Wasn't there some guy at RedHat doing it? Andrew Overholt did some work on SQL99 recursive queries, but went back to university without having gotten to the point where it actually worked. One of the many things on my to-do list is to pick up and finish Andrew's work on this. If someone has time to work on it, let me know and I'll try to get what he had over to you. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Recursive queries?
Andrew Overholt did some work on SQL99 recursive queries, but went back to university without having gotten to the point where it actually worked. One of the many things on my to-do list is to pick up and finish Andrew's work on this. If someone has time to work on it, let me know and I'll try to get what he had over to you. There is a website somewhere where a guy posts his patch he is maintaining that does it. I'll try to find it... Chris ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] PITR Dead horse?
Tatsuo Ishii <[EMAIL PROTECTED]> writes: > I and my co workers are very interested in implementing PITR. We will > tackle this for 7.5 if no one objects. Sounds good. I'll try to push in the work that Patrick and JR did within the next day or two, and then you can take it from there... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Recursive queries?
There is a website somewhere where a guy posts his patch he is maintaining that does it. I'll try to find it... Found it. Check it out: http://gppl.terminal.ru/index.eng.html Patch is current for 7.4, Oracle syntax. Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] PITR Dead horse?
> I and some other developers are also interested in. > Do you think we can work together? Sure. Why not. I think it would be practical to decide who is the leader of this project, though. -- Tatsuo Ishii ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
