[HACKERS] Something I don't understand with the use of schemas
Hi, At my work, I have to dump a database and restore it on another database under a specific schema. My first idea was to create the new schema on the old database, move all the database objects on this new schema, dump the old db and restore on the new one. But I have to move quite a lot of objets. So, I took another way to do it : rename the public schema, dump and restore. Pretty simple and straightforward. After the "ALTER SCHEMA public RENAME TO foobar", pgAdmin can't see it anymore. I made a patch to fix this, send a mail to the pgAdmin hackers and a thread began on the right way to handle this. I was pretty sure I was right but I'm not so sure anymore. Apparently, I can rename all schemas, even system schemas ! metier=# alter schema pg_catalog rename to foobar; ALTER SCHEMA Doing so is a bit dumb because everything is now broken on this database. I can't use psql, pg_dump, ... But I think this command should protect the user from doing weird stuff. For example, DROP SCHEMA doesn't allow me to drop pg_* schemas and that seems right to me. So here it is. I think we should disallow user from renaming system schemas and I think we should made clear if public is a system or public schema. Regards. -- Guillaume. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] int to inet conversion [or Re: inet to bigint?]
On Dec 08 04:36, Kai wrote: > After working regularly with inet values in sql, it would be nice to be able > to do this: > > => select '192.168.1.1'::inet + 1 as result; > result > - >192.168.1.2 > (1 row) You may take a look at ip4r[1] project too. For a full list for its availabilities (like +/- operators) here[2] is the related SQL file. [1] http://pgfoundry.org/projects/ip4r/ [2] http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/ip4r/ip4r/ip4r.sql.in?rev=1.4&content-type=text/plain Regards. -- "We are the middle children of history, raised by television to believe that someday we'll be millionaires and movie stars and rock stars, but we won't. And we're just learning this fact," Tyler said. "So don't fuck with us." ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Replication on the backend
Hello, On Fri, 2005-12-09 at 08:47 -0500, Christopher Browne wrote: > We *know* (particularly those of us that have had involvement in > actually implementing replication systems used in production > environments) that "user space" implementations of replication can > function satisfactorily. We've implemented it. While this might be true, allow me a sidenote: AFAIK the very first, functional prototype we know of was Postgres-R for PostgreSQL 6.4.2 (1). So the very same holds true for a replication solution integrated into the backend: we know such an implementation can function satisfactorily. As we mostly agree, the performance bottelneck is _not_ the CPU, but the nodes interconnects (the network). Regarding communication between the backends and the replication solution, performance isn't that much of an issue, because the inter-node communication will allways be slower than inter-process communication. A different problem is how to distribute PostgreSQL with different upcomming replication solutions. It seems to me that most people's main concern is not being able to get a prebuilt PostgreSQL with _just_one_replication_solution_that_works_(tm) For most users it really doesn't matter _how_ exactly the solution technically got integrated. This problem gets solved with hooks and preloading a library: you could simply provide _one_ PostgreSQL package which provides hooks for replication solutions. Those could then provide a package with their library. This of course is only doable if the number of hooks is kept low. Regards Markus [1] pgreplication project on gborg: http://gborg.postgresql.org/project/pgreplication/projdisplay.php ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Min Xid problem proposal
On Fri, 2005-12-09 at 12:32 -0500, Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > My proposal to solve that problem, is to make any transaction that > > inserts or modifies tuples in a table that is marked as frozen, unfreeze > > it first. The problem I had last time was finding a good spot in the > > code for doing so. I'm now proposing to do it in the parser, in > > setTargetTable(). > > My god, no. Do you have any idea how many paths for updates you've missed? > (Think about prepared plans for starters.) > > Furthermore, you can't do this in the way you propose (non-WAL-logged > update to pg_class). What if the system crashes without ever having > written this update to disk? The inserted tuples might have made it --- > whether they're committed or not doesn't matter, you've still blown it. > > I don't see any very good argument for allowing this mechanism to set > minxid = FrozenXid in the first place. If there are only frozenXid in > the table, set minxid = current XID. That eliminates the entire problem > at a stroke. > > (Yes, I know what you are going to say. The idea of freezing a table > and then never having to vacuum it at all is NOT worth the cost of > putting in a mechanism that would guarantee its safety.) >From what's been said VACUUM FREEZE will not alter the fact that a frozen table will need vacuuming again in the future and so cannot ever be read-only. I can't really see any reason to run VACUUM FREEZE... If you want to make a table read-only forever, we need a separate command to do that, ISTM. ALTER TABLE ... READONLY could set minXid = FrozenTransactionId, indicating no further VACUUMs required, ever. We can then disallow INSERT/UPDATE/DELETE against the table in the permissions layer. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Log of CREATE USER statement
On Fri, 2005-12-09 at 19:41 +0100, Peter Eisentraut wrote: > Tom Lane wrote: > > As I said already, if the user wishes the password to be secure, he > > needs to encrypt it on the client side. > > Maybe we should provide a backslash command in psql That is a good option, but not the only option required. There are many reasons to need to supply the password as part of a command, rather than an interactive input. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] int to inet conversion [or Re: inet to bigint?]
We have a patch for this for application to 8.2. --- Volkan YAZICI wrote: > On Dec 08 04:36, Kai wrote: > > After working regularly with inet values in sql, it would be nice to be able > > to do this: > > > > => select '192.168.1.1'::inet + 1 as result; > >result > > - > > 192.168.1.2 > > (1 row) > > You may take a look at ip4r[1] project too. For a full list for its > availabilities (like +/- operators) here[2] is the related SQL file. > > [1] http://pgfoundry.org/projects/ip4r/ > [2] > http://cvs.pgfoundry.org/cgi-bin/cvsweb.cgi/~checkout~/ip4r/ip4r/ip4r.sql.in?rev=1.4&content-type=text/plain > > > Regards. > > -- > "We are the middle children of history, raised by television to believe > that someday we'll be millionaires and movie stars and rock stars, but > we won't. And we're just learning this fact," Tyler said. "So don't > fuck with us." > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > >http://archives.postgresql.org > -- 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 5: don't forget to increase your free space map settings
Re: [HACKERS] Min Xid problem proposal
Simon Riggs wrote: > From what's been said VACUUM FREEZE will not alter the fact that a > frozen table will need vacuuming again in the future and so cannot ever > be read-only. I can't really see any reason to run VACUUM FREEZE... Yeah. > If you want to make a table read-only forever, we need a separate > command to do that, ISTM. Let's get this goose cooked and then we can improve it. This patch has been waiting on my queue for too long. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Log of CREATE USER statement
Simon Riggs <[EMAIL PROTECTED]> writes: > On Fri, 2005-12-09 at 19:41 +0100, Peter Eisentraut wrote: >> Maybe we should provide a backslash command in psql > That is a good option, but not the only option required. > There are many reasons to need to supply the password as part of a > command, rather than an interactive input. You miss the point entirely. Normal *use* of a password is not part of the SQL command language and is already adequately encrypted. It's only supplying a new password in CREATE/ALTER USER that has the security hazard of exposing the password in command logs, pg_stat_activity, etc. AFAICS, Peter's idea covers that case satisfactorily. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] pgInstaller 8.1.1
I've just uploaded pgInstaller 8.1.1 - it should be at http://ftp.postgresql.org/pub/binary/v8.1.1/binary/win32 within an hour or so. Please take a peek as soon as possible if you have a Windows box and report any problems before any announcements go out. This include PostgreSQL 8.1.1 of course, as well as pgAdmin 1.4.1, psqlODBC 08.01.0102 and JDBC 8.1.404. Thanks, Dave. ---(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] Something I don't understand with the use of schemas
Guillaume LELARGE <[EMAIL PROTECTED]> writes: > Apparently, I can rename all schemas, even system schemas ! > metier=# alter schema pg_catalog rename to foobar; > ALTER SCHEMA If you are superuser, you can do anything you want, up to and including breaking the system irretrievably. Compare "rm -rf /" on Unix. We won't be putting training wheels on superuser status for the same reasons that no one finds it a good idea to restrict root's abilities. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Something I don't understand with the use of schemas
Tom Lane wrote: > If you are superuser, you can do anything you want, up to and including > breaking the system irretrievably. Compare "rm -rf /" on Unix. We > won't be putting training wheels on superuser status for the same > reasons that no one finds it a good idea to restrict root's abilities. However there is an effort to get rid of root in some Unix lands, separating its responsabilities with more granularity. Maybe there could be an effort, not to hand-hold the true superusers, but to delegate some of its responsabilities to other users. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Something I don't understand with the use of schemas
Alvaro Herrera <[EMAIL PROTECTED]> writes: > However there is an effort to get rid of root in some Unix lands, > separating its responsabilities with more granularity. Maybe there > could be an effort, not to hand-hold the true superusers, but to > delegate some of its responsabilities to other users. We did that already (see CREATEROLE privilege in 8.1) regards, tom lane ---(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] Something I don't understand with the use of schemas
However there is an effort to get rid of root in some Unix lands, separating its responsabilities with more granularity. Maybe there could be an effort, not to hand-hold the true superusers, but to delegate some of its responsabilities to other users. Like sudo? Joshua D. Drake ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Something I don't understand with the use of schemas
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > However there is an effort to get rid of root in some Unix lands, > > separating its responsabilities with more granularity. Maybe there > > could be an effort, not to hand-hold the true superusers, but to > > delegate some of its responsabilities to other users. > > We did that already (see CREATEROLE privilege in 8.1) Part of it. We can still improve, I think. Not that I have a concrete proposal to make though. Regarding CREATEROLE, I wonder why is that a role with that privilege is able to create other roles containing any privileges (except superuserness), and not just the privileges the creating role has. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Something I don't understand with the use of schemas
Joshua D. Drake wrote: > > >However there is an effort to get rid of root in some Unix lands, > >separating its responsabilities with more granularity. Maybe there > >could be an effort, not to hand-hold the true superusers, but to > >delegate some of its responsabilities to other users. > > Like sudo? I was thinking in the thing called "capabilities". -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Backslashes in string literals
>>> On Fri, Dec 9, 2005 at 11:24 am, in message <[EMAIL PROTECTED]>, Peter Eisentraut <[EMAIL PROTECTED]> wrote: > Kevin Grittner wrote: >> direction PostgreSQL is headed is to drop the nonstandard escapes, >> unless an extended literal is explicitly used. I've attached a patch >> which supports this as a configure option, using a >> -- enable- standard- strings switch. > > There is already a run- time configuration option > standard_conforming_strings which does what you seem to have in mind. As Bruce has mentioned, this is currently read-only, set to off. I needed something fast, and I could see a way to do it quickly with a configure switch, to compile it for standard behavior. Since the non-standard behavior is in the lexer, I couldn't see any reasonable way to base it on a runtime switch. I'm curious what is intended here. Can anyone give a one-paragraph explanation of how this configuration option will work? -Kevin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Warm-cache prefetching
On Fri, Dec 09, 2005 at 11:32:48AM -0500, Bruce Momjian wrote: > Tom Lane wrote: > > Bruce Momjian writes: > > > I can see that being useful for a single-user application that doesn't > > > have locking or I/O bottlenecks, and doesn't have a multi-stage design > > > like a database. Do we do enough of such processing that we will _see_ > > > an improvement, or will our code become more complex and it will be > > > harder to make algorithmic optimizations to our code? > > > > The main concern I've got about this is the probable negative effect on > > code readability. There's a limit to the extent to which I'm willing to > > uglify the code for processor-specific optimizations, and that limit is > > not real far off. There are a lot of other design levels we can work at > > to obtain speedups that won't depend on the assumption we are running > > on this-year's Intel hardware. > > That is my guess too. We have seen speedups by inlining and optimizing > frequently-called functions and using assembler for spinlocks. Proof of > the assembler is in /pg/include/storage/s_lock.h and proof of the > inlining is in /pg/include/access/heapam.h. Those were chosen for > optimization because they were used a lot. > > I think the big question is whether there are other areas that have a > similar CPU load and can be meaningfully optimized, and does the > optimization include such things as multi-staging. I think we should > take a wait and see attitude and see what test results people get. > I also agree that we should go for the most bang for the buck and include the coding/maint. aspects in the cost. Pre-fetching is not just available in x86 processors. Most modern processors now support memory prefetch operations. If we do not consider memory cache-line stalls while the processor waits for data in our designs going forward, there will be substantial performance gains that will be forever out of reach. Ken ---(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] Something I don't understand with the use of schemas
Like sudo? I was thinking in the thing called "capabilities". I just meant as a metaphor ;) -- Command Prompt, Inc., Your PostgreSQL solutions company. 503-667-4564 Custom programming, 24x7 support, managed services, and hosting Open Source Authors: plPHP, pgManage, Co-Authors: plPerlNG Reliable replication, Mammoth Replicator - http://www.commandprompt.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Something I don't understand with the use of schemas
On Sat, Dec 10, 2005 at 14:25:46 -0300, Alvaro Herrera <[EMAIL PROTECTED]> wrote: > Joshua D. Drake wrote: > > > > >However there is an effort to get rid of root in some Unix lands, > > >separating its responsabilities with more granularity. Maybe there > > >could be an effort, not to hand-hold the true superusers, but to > > >delegate some of its responsabilities to other users. > > > > Like sudo? > > I was thinking in the thing called "capabilities". Note that the linux 'capabilities' is not the same thing as 'capabilities' is to some security researchers. To them a capability is sort of like a file handle, and you can't do anything with an object until you get a file handle to it. If you want to give some one else access to something you have access to, you give them a copy of the file handle you hold. Doing things this way simplifies some aspects of designing secure systems. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Log of CREATE USER statement
On Sat, 2005-12-10 at 11:15 -0500, Tom Lane wrote: > Simon Riggs <[EMAIL PROTECTED]> writes: > > On Fri, 2005-12-09 at 19:41 +0100, Peter Eisentraut wrote: > >> Maybe we should provide a backslash command in psql > > > That is a good option, but not the only option required. > > There are many reasons to need to supply the password as part of a > > command, rather than an interactive input. > > You miss the point entirely. Normal *use* of a password is not part of > the SQL command language and is already adequately encrypted. It's only > supplying a new password in CREATE/ALTER USER that has the security > hazard of exposing the password in command logs, pg_stat_activity, etc. > AFAICS, Peter's idea covers that case satisfactorily. Peter's idea is great and I agree with everything he says. I meant that if we are helping psql users to encrypt the password, we should help others as well, thats all. At very least this should be documented better. At best we could change the protocol to encrypt things client-side, so that plaintext never goes across the wire in any circumstance. That would then be good security by default. I'm not volunteering to write that code anytime soon, but I could work on some docs to better explain this. We could also change the logging and pg_stat_activity so that we never output the password at all, plaintext or otherwise. Best Regards, Simon Riggs ---(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] Upcoming PG re-releases
Was thinking if someone could summarize this all it would make a really good
FAQ entry.
Robert Treat
On Friday 09 December 2005 13:28, Martijn van Oosterhout wrote:
> On Fri, Dec 09, 2005 at 12:38:21PM -0500, Bruce Momjian wrote:
> > > This means someone who is planning on upgrading to 8.1 in two months
> > > can use this function now to weed out the bad data before the upgrade
> > > even starts.
> >
> > Oh, so you back-load it into the old database. Interesting. I assume
> > to be useful you would have to write something that checked every column
> > values in every table and database.
>
> Umm, yeah. I was thinking about how to do that. pl/pgsql is not the
> best language to do that in. In any case I found a bug in the version I
> posted and also added a function that does:
>
> test=# select * from db_utf8_verify();
> tab | fld | location
> --+-+--
> tbl1 | foo | (12,3)
> (1 row)
>
> It gives the table, field and ctid of any values that failed. It skips
> pg_catalog. It's also *really* slow for long strings. Just executing it
> on the pg_rewrite in the default installation takes forever. If someone
> really wanted this for a large database maybe they should recode it in
> C.
>
> http://svana.org/kleptog/pgsql/utf8_verify.sql
>
> Have a nice day,
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [HACKERS] Warm-cache prefetching
"Simon Riggs" <[EMAIL PROTECTED]> wrote
>
> You may be trying to use the memory too early. Prefetched memory takes
> time to arrive in cache, so you may need to issue prefetch calls for N
> +2, N+3 etc rather than simply N+1.
>
> p.6-11 covers this.
>
I actually tried it and no improvements have been observed. Also, this may
conflict with "try to mix prefetch with computation" suggestion from the
manual that you pointed out. But anyway, this looks like fixable compared to
the following "prefetch distance" problem. As I read from the manual, this
is one key factor of the efficiency, which also matches our intuition.
However, when we process each tuple on a page, CPU clocks that are needed
might be quite different:
---
for (each tuple on a page)
{
if (ItemIdIsUsed(lpp))/* some stopped here */
{
...
/* some involves deeper function calls here */
valid = HeapTupleSatisfiesVisibility(&loctup, snapshot, buffer);
if (valid)
scan->rs_vistuples[ntup++] = lineoff;
}
}
---
So it is pretty hard to predicate the prefetch distance. The prefetch
improvements to memcpy/memmove does not have this problem, the prefecth
distance can be fixed, and it does not change due to the different speed
CPUs of the same processor serials.
Maybe L2 cache is big enough so no need to worry about fetch too ahead?
Seems not true, since this idea is vulnerable to a busy system. No data in
L2 will be saved for you for a long time.
As Luke suggested, the code above scan operators like sort might be a better
place to look at. I will take a look there.
Regards,
Qingqing
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Reducing relation locking overhead
Ühel kenal päeval, N, 2005-12-08 kell 14:53, kirjutas Tom Lane: > Given the discussion so far, it seems likely to me that completely > concurrent REINDEX is indeed out of reach, and that what we ought to > be thinking about is what sort of compromise design (ie, partially > concurrent REINDEX) is reasonable. > > Something that might work is: > > 1. Take ShareUpdateExclusiveLock (this blocks VACUUM and DDL changes), > then run existing CREATE INDEX code. The finished index may be missing > some tuples inserted during the run. > > 2. Commit transaction so that index becomes visible (we assume it's > marked so that the planner will know not to rely on it). Continue to > hold ShareUpdateExclusiveLock so VACUUM doesn't run. > > 3. Attempt to acquire ShareLock (possibly a ConditionalLockAcquire/sleep > loop instead of just flat-out LockAcquire). Once we have this we know > there are no active writer transactions. Release the lock immediately. > > 4. Make a new scan of the table and insert any rows not already present > in the index. (This need not process anything inserted later than step > 3, because any new transactions will know to insert rows in the index > anyway.) How do you plan to determine "any rows not already present in the index" without explicitly remembering the start and end snapshots of existing CREATE INDEX (SNAP1 and SNAP2 in my proposal)? actually the end point seems to be covered, but what about start condition ? In the last round of discussion you pointed out that index itself can't be effectively used for this in case there are lots of equal index keys. (As I pointed out, this can be fixed if we will start using ctid to determine placement/order of equal keys, but I don't think we are building indexes this way now). I still think that wedging start of 1. and end of 2. into points where no concurrent transaction is running would be the easiest and most robust way to do it. And if the attempts (locking periods) to find/force that spot are short enough, they can be tolerated in practice. > 5. Mark index good and commit, releasing all locks. > > I don't think that it's worth the effort and complexity to try to avoid > a full table scan in step 4. At best you would save much less than 50% > of the total work, and the impact on normal operations is not free. Agreed. The usecase needing concurrent index, being already slow, can probably be made to tolerate another 2-3x slowdown. > If what you want is a REINDEX rather than creating an independent new > index, then at step 5 you need to do a swap operation which'll require > obtaining exclusive lock on the index. This creates another opportunity > for deadlock failures, but again a conditional loop might help. > > There are still some issues about the behavior when the index is UNIQUE. > Ideally you would like the CREATE INDEX to fail on a duplicate, not any > concurrent writer transaction, but I don't think it's possible to > guarantee that. Ideally, but probably not too important in practice. The point can be always made that there already is a unique index at the point where concurrent trx fails. If the point is before end of 2. the concurrent trx will probably wait until first commit before failing, no ? > Also, I'm not sure how we get rid of the broken index if there is a > failure later than step 2. What about expicit DROP INDEX ? Even for REINDEX the index has to be visible as a separate index after 2. so that inserts updates will be aware of it. -- Hannu ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Backslashes in string literals
Kevin Grittner wrote: > >>> On Fri, Dec 9, 2005 at 11:24 am, in message > <[EMAIL PROTECTED]>, Peter Eisentraut > <[EMAIL PROTECTED]> wrote: > > > Kevin Grittner wrote: > >> direction PostgreSQL is headed is to drop the nonstandard escapes, > >> unless an extended literal is explicitly used. I've attached a > patch > >> which supports this as a configure option, using a > >> -- enable- standard- strings switch. > > > > There is already a run- time configuration option > > standard_conforming_strings which does what you seem to have in > mind. > > As Bruce has mentioned, this is currently read-only, set to off. > > I needed something fast, and I could see a way to do it quickly with a > configure switch, to compile it for standard behavior. Since the > non-standard behavior is in the lexer, I couldn't see any reasonable way > to base it on a runtime switch. I'm curious what is intended here. Can > anyone give a one-paragraph explanation of how this configuration option > will work? Have you read our documentation? http://www.postgresql.org/docs/8.1/static/sql-syntax.html#SQL-SYNTAX-CONSTANTS http://www.postgresql.org/docs/8.1/static/runtime-config-compatible.html#RUNTIME-CONFIG-COMPATIBLE-VERSION Between those and the release notes, I don't know what additional information you want. In the future you will set standard_conforming_strings to on and backslashes will be treated literally. -- 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 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Reducing relation locking overhead
Hannu Krosing <[EMAIL PROTECTED]> writes: > How do you plan to determine "any rows not already present in the index" > without explicitly remembering the start and end snapshots of existing > CREATE INDEX (SNAP1 and SNAP2 in my proposal)? I was thinking in terms of actually looking into the index to see if the particular TID is present or not. You could use snapshots to optimize this by avoiding index probes for tuples that must be present, which hopefully will be most of 'em. Also you need a snapshot to detect tuples that are new enough that they certainly will be indexed by their inserting transaction, so that you don't have a race condition between an active inserter and the REINDEX. (I think this is possible but maybe I missed something.) That leaves you looking at just the tuples inserted by transactions that might or might not have known about the index. So yeah, you do need SNAP1 and SNAP2 but they're being used in a different way than the original proposal. > In the last round of discussion you pointed out that index itself can't > be effectively used for this in case there are lots of equal index keys. True, but if you can avoid going to the index at all for the majority of the tuples, I think this is tolerable. In any case the design idea here seems to be "we don't care how long REINDEX takes as long as it's not blocking anyone". regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Upcoming PG re-releases
I don't see it asked very often, and I think our 8.1 releae note
addition (plus a mention in the 8.1.1 notes) will complete this.
---
Robert Treat wrote:
> Was thinking if someone could summarize this all it would make a really good
> FAQ entry.
>
> Robert Treat
>
> On Friday 09 December 2005 13:28, Martijn van Oosterhout wrote:
> > On Fri, Dec 09, 2005 at 12:38:21PM -0500, Bruce Momjian wrote:
> > > > This means someone who is planning on upgrading to 8.1 in two months
> > > > can use this function now to weed out the bad data before the upgrade
> > > > even starts.
> > >
> > > Oh, so you back-load it into the old database. Interesting. I assume
> > > to be useful you would have to write something that checked every column
> > > values in every table and database.
> >
> > Umm, yeah. I was thinking about how to do that. pl/pgsql is not the
> > best language to do that in. In any case I found a bug in the version I
> > posted and also added a function that does:
> >
> > test=# select * from db_utf8_verify();
> > tab | fld | location
> > --+-+--
> > tbl1 | foo | (12,3)
> > (1 row)
> >
> > It gives the table, field and ctid of any values that failed. It skips
> > pg_catalog. It's also *really* slow for long strings. Just executing it
> > on the pg_rewrite in the default installation takes forever. If someone
> > really wanted this for a large database maybe they should recode it in
> > C.
> >
> > http://svana.org/kleptog/pgsql/utf8_verify.sql
> >
> > Have a nice day,
>
> --
> Robert Treat
> Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
>
--
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 4: Have you searched our list archives?
http://archives.postgresql.org
[HACKERS] New packages built ...
7.3.12, 7.4.10, 8.0.5 and 8.1.1 ... all should be available on the ftp mirrors by now ... please take a quick peak at them, and let us know if there appear to be any problems with them ... General announce of availability going out on monday ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Upcoming PG re-releases
Bruce Momjian wrote:
I don't see it asked very often, and I think our 8.1 releae note
addition (plus a mention in the 8.1.1 notes) will complete this.
Actually a "upgrade" FAQ is probably a good idea. Something that says
what really happens
when foo changes in 8.1 or how foo is different then 8.0.
The idea that there is a practical (for those that have practical
implications) resource for finding
out what it really means that the UTF-8 stuff changed .
Joshua D. Drake
---
Robert Treat wrote:
Was thinking if someone could summarize this all it would make a really good
FAQ entry.
Robert Treat
On Friday 09 December 2005 13:28, Martijn van Oosterhout wrote:
On Fri, Dec 09, 2005 at 12:38:21PM -0500, Bruce Momjian wrote:
This means someone who is planning on upgrading to 8.1 in two months
can use this function now to weed out the bad data before the upgrade
even starts.
Oh, so you back-load it into the old database. Interesting. I assume
to be useful you would have to write something that checked every column
values in every table and database.
Umm, yeah. I was thinking about how to do that. pl/pgsql is not the
best language to do that in. In any case I found a bug in the version I
posted and also added a function that does:
test=# select * from db_utf8_verify();
tab | fld | location
--+-+--
tbl1 | foo | (12,3)
(1 row)
It gives the table, field and ctid of any values that failed. It skips
pg_catalog. It's also *really* slow for long strings. Just executing it
on the pg_rewrite in the default installation takes forever. If someone
really wanted this for a large database maybe they should recode it in
C.
http://svana.org/kleptog/pgsql/utf8_verify.sql
Have a nice day,
--
Robert Treat
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(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
