Re: [GENERAL] libreadline and Debian 5 - not missing just badly named
On Thursday 03 June 2010 20.03:19 J. Bagg wrote: > because I tend to skim > over the PostgreSQL related ones, assuming they're connected with the > ready-built version. I'm just curious: why are you compiling your own? If you want to stick with lenny and need 8.4: It's in backports.org (package posgtgresql-8.4). On the other hand, squeeze (what is to become the next Debian version any month now) is already quite stable, so you could update to squeeze. If you need some extensions or contrib modules: have you looked through all pstgres packages, the postgresql-8.3 (or postgresql) packages only pull in a core set, additional stuff is packaged, and to compile you own extensions, just install postgresql-server-dev-8.4. (Of course there are valid reasons to compile your own, but for me as a pg user and not developer, I've not met them in a long time...) cheers -- vbi -- Umlaut Zebra über alles! signature.asc Description: This is a digitally signed message part.
[GENERAL] Requested addition to the todo list [was]: Re: Altering Domain Constraints on composite types
I'd like to propose the following items be added to the todo list:
(Any thoughts?)
1) Modify composite types to allow ALTER DOMAIN(s) to ADD CONSTRAINT.
2) Allow a since ALTER DOMAIN issue multiple ADD and DROP commands in
a single statement.
On Thu, Jun 3, 2010 at 9:12 AM, Richard Broersma
wrote:
> While playing with domains and composite types, I discovered a problem
> when I tried to alter a domain constraint. I don't believe that this
> problem exists for traditional types.
>
> for example:
>
> broersr=> insert into tags values ((84,'PDSL',1,''),(84,'P',1, ''),'TEST
> TAG');
> ERROR: invalid regular expression: invalid repetition count(s)
> broersr=> \dD
>
> List of domains
> Schema | Name | Type |
> Modifier | Check
> +--+---++---
> ...
> public | tag_function | character varying(4) | not null
> | CHECK (VALUE::text ~ '^[A-Z]{2-4}$'::text)
> ...
> (11 rows)
>
> broersr=> --oops I made a mistake in the definition of my REGEX
> constraint so lets fix it.
>
> broersr=> begin;
> BEGIN
> broersr=> alter domain tag_function drop constraint valid_tag_function;
> ALTER DOMAIN
> broersr=> alter domain tag_function add constraint valid_tag_function
> check(value ~ E'^[A-Z]{2,4}$');
> ERROR: cannot alter type "tag_function" because column "tags"."tag" uses it
> broersr=> rollback;
> ROLLBACK
>
>
> --
> Regards,
> Richard Broersma Jr.
>
> Visit the Los Angeles PostgreSQL Users Group (LAPUG)
> http://pugs.postgresql.org/lapug
>
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove the current database and populate the database with new data?
On 4/06/2010 7:26 AM, Wang, Mary Y wrote: I thought about DROP DATABASE, but wasn't sure if it would clean up EVERYTHING. It won't remove your users and roles, or anything else that you see reported in pg_dumpall --globals-only . I assume most of you would just do the DROP DATABASE for the scenario that I described. Is that correct? Yep. I essentially _never_ re-initdb, personally. For one thing, I often have other databases in a cluster that I'd rather not lose, but it's also generally unnecessary. ( I do frequently wish for the ability to create roles at the database rather than cluster level, though, as it'd make cleaning the DB for testing of schema creation scripts and the like considerably easier. Not to mention backups. ) -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] create index concurrently - duplicate index to reduce time without an index
> -Original Message- > From: Greg Smith [mailto:g...@2ndquadrant.com] -snip- > > Gareth.Williams wrote: > > So the rest of the question is, if I have two indexes with identical > definitions, what happens? I've confirmed that I can create indexes with > identical definitions (except name) without postgres complaining - and > without breaking the client on my test system - but I am wary of trying it > on my production system where there is much more data (8GB) and I care > about it's integrity so much more. > > > > The database doesn't care one bit if you create a bunch of redundant > indexes. So long as one of them is around to satisfy the queries that > need the index to run well, you're fine. > > The main thing you can't do with the index concurrently/rename shuffle > you've discovered here is use that approach to concurrently rebuild an > index that enforces a constraint or unique index. If your index is > enforcing a PRIMARY KEY for example, you'll discover a major roadblock > were you to try this same technique to rebuild it. Those are tied into > the constraint portion of the system catalogs and manipulating them > isn't so easy. > > Regular indexes that exist just to speed up queries, those you can > rename around as you've been figuring out without any downside. From a > general paranoia perspective, you should run an explicit ANALYZE on the > underlying table after you finish the shuffle, just to make absolutely > sure the right statistics are available afterwards. > > -- > Greg Smith 2ndQuadrant US Baltimore, MD > PostgreSQL Training, Services and Support > g...@2ndquadrant.com www.2ndQuadrant.us Thanks Greg, Alban and others, This has cleared up a misunderstanding I had about why one should reindex. Re-reading the documentation http://www.postgresql.org/docs/8.4/interactive/sql-reindex.html it is clear now that reindex or recreating and index should not normally be needed - certainly not to keep an index up-to-date. I would have guessed that VACUUM or VACUUM ANALYSE on the table that the index is associated would have been sufficient to reclaim space for a 'bloated' index (maybe only VACUUM FULL would help). In any case we can leave reindexing or full vacuum for outages where we are interrupting service anyway. I was heartened by the responses and tried further testing (if it could not hurt, why not try and see if it could be faster), but ran into a problem. A few times when I was trying to drop an index (before or after creating a duplicate index with 'concurrently'), the dropping of the index stalled. It seems that this was because of existing connection: postgres: rods ICAT 130.102.163.141(58061) idle in transaction And new clients block. Is that expected? Should we look for 'bugs' in out client that seems to be holding a connection? For the application we have, I'm ready to give up on this train of investigation for optimization and just vacuum analyse key tables regularly and vaccuum and maybe reindex more completely during outages - though we aim for outages to be infrequent. The database holds data representing a virtual filesystem structure with millions of file (and associated access controls, and information on underlying storage resources and replication). There is probably not much update or delete of the main data - at least compared with the total holdings and the new data/files which are regularly being added to the system. Thanks again, Gareth Ps. Greg, I don't understand the issue with 'concurrently rebuild (of) an index that enforces a constraint or unique index'. I don't think I care much right at the moment, but I'm generally interested and others might be too. Would you expect the create index to fail or to cause locking or just transient performance degradation? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] c program fails to run with the postgres which is installed at user location
On Thu, Jun 3, 2010 at 2:15 AM, John R Pierce wrote: > zhong ming wu wrote: >> >> ... >> This machine is RHEL 5.5 and has both RH stock postgres 8.1.18 in > > you probably need to either use "-R $HOME/local/lib" on the link command to > specify the runtime path to find .so's in, or add $HOME/local/lib to > LD_LIBRARY_PATH (I prefer the former if the linking is under my control, but > revert to the latter if I absolutely have to) > Thanks. Setting LD_LIBRARY_PATH works. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove the current database and populate the database with new data?
I thought about DROP DATABASE, but wasn't sure if it would clean up EVERYTHING. I had a bad experience early this year when I restored a database that was running on Postgres 7.x.x. The database crashed badly, that I couldn't recover it. It ended up that I had to restore it from a previous night's backup. I noticed a huge decrease in performance after the restore. I always have thought that there was something that hasn't been cleaned up (Yes, I did run the VACUUM command). I decided not to investigate it anymore, because I already had a plan to upgrade to 8.3.8 anyway. I assume most of you would just do the DROP DATABASE for the scenario that I described. Is that correct? Mary -Original Message- From: Adrian Klaver [mailto:adrian.kla...@gmail.com] Sent: Thursday, June 03, 2010 4:10 PM To: pgsql-general@postgresql.org Cc: Wang, Mary Y Subject: Re: [GENERAL] How to remove the current database and populate the database with new data? On Thursday 03 June 2010 4:05:14 pm Wang, Mary Y wrote: > Hi, > > I've some test data in a database and would like to delete that > database and clean everything that is associated with that database. > Then I'd like to populate the same database with different data. My plan is > to: > (1) Remove the /usr/local/pgsql/data directory > (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt > (/tmp/indumpfile.txt has all the sql statements to restore the > database) > (3) Restart the postgres server > > Not sure if I need to run the VACCUM command, because I know Postgres > 8.3.8 has the auto-vacuum daemon on to perform VACCUMs when it's > necessary. Did I miss any other steps for cleaning up? > > Please advise. > > Thanks > Mary Why not use DROP DATABASE? Removing the data directory removes the whole Postgres cluster, possibly including the config files. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove the current database and populate the database with new data?
On Thursday 03 June 2010 4:05:14 pm Wang, Mary Y wrote: > Hi, > > I've some test data in a database and would like to delete that database > and clean everything that is associated with that database. Then I'd like > to populate the same database with different data. My plan is to: > (1) Remove the /usr/local/pgsql/data directory > (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt > (/tmp/indumpfile.txt has all the sql statements to restore the database) > (3) Restart the postgres server > > Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8 > has the auto-vacuum daemon on to perform VACCUMs when it's necessary. Did > I miss any other steps for cleaning up? > > Please advise. > > Thanks > Mary Why not use DROP DATABASE? Removing the data directory removes the whole Postgres cluster, possibly including the config files. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to remove the current database and populate the database with new data?
On Thu, 2010-06-03 at 16:05 -0700, Wang, Mary Y wrote: > Hi, > > I've some test data in a database and would like to delete that database and > clean everything that is associated with that database. Then I'd like to > populate the same database with different data. > My plan is to: > (1) Remove the /usr/local/pgsql/data directory > (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt > (/tmp/indumpfile.txt has all the sql statements to restore the database) > (3) Restart the postgres server > > Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8 > has the auto-vacuum daemon on to perform VACCUMs when it's necessary. Did I > miss any other steps for cleaning up? How about: psql -U postgres template1 -c "drop database "; psql -U postgres < inputfile.txt psql -U postgres -c "ANALYZE VERBOSE" What you have above won't work anyway as you need to stop postgres, initdb, recreate your users etc... Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to remove the current database and populate the database with new data?
Hi, I've some test data in a database and would like to delete that database and clean everything that is associated with that database. Then I'd like to populate the same database with different data. My plan is to: (1) Remove the /usr/local/pgsql/data directory (2) psql -e mydatabase -f /tmp/indumpfile.txt & > /tmp/outdumpfile.txt (/tmp/indumpfile.txt has all the sql statements to restore the database) (3) Restart the postgres server Not sure if I need to run the VACCUM command, because I know Postgres 8.3.8 has the auto-vacuum daemon on to perform VACCUMs when it's necessary. Did I miss any other steps for cleaning up? Please advise. Thanks Mary -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL and NeXpose Rapid 7 Vulnerbility scanning software
Arnold, Sandra osti.gov> writes: > > Is anyone using > NeXpose Rapid 7 to scan your PostgreSQL databases for vulnerbilities? If > so, what authentication are you using to allow it to connect to your > database? Or, how are you configuring the software to allow it to connect > to the database? Hi Sandra, Although Nexpose scans for PostgreSQL databases and can discover weak credentials, etc, it does not currently allow endusers to specify credentials. This feature will likely soon be available. PS: we are being off-topic here, please use the nexpose-users mailing list: https://mail.metasploit.com/mailman/listinfo/nexpose-users -mrb -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgbouncer
On Thu, Jun 3, 2010 at 4:03 PM, Merlin Moncure wrote: > I'm interested in this too...does anyone else have experience in this matter? > I've pondered this many times. My questions are what failure mode are you looking to avoid, especially if you're using pgbouncer for load balance in addition to connection pool. I think the best spot in most cases is the web server itself, but If the web server is dead, then the pooler goes with it, whereas if the pooler is elsewhere you have yet another point of failure when load balancing -- that is if db1 goes down and takes pgpool with it, db2 is unused and won't save your app. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Removing CONTEXT from RAISE EXCEPTION messages from triggers
Excerpts from Peter Geoghegan's message of jue jun 03 16:23:07 -0400 2010: > > > > \set VERBOSITY terse > > > > Well, I actually didn't mean through psql, but I see I can set > verbosity though a call to PQsetErrorVerbosity(). > > Maybe this would work better as a GUC that can be set per session? > That way, we wouldn't have to worry about downstream driver authors > supporting it. This is a client setting, not a server setting, so a GUC doesn't make much sense. The server always sends everything. It's up to the client to filter according to the user's preferences. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Removing CONTEXT from RAISE EXCEPTION messages from triggers
> > \set VERBOSITY terse > Well, I actually didn't mean through psql, but I see I can set verbosity though a call to PQsetErrorVerbosity(). Maybe this would work better as a GUC that can be set per session? That way, we wouldn't have to worry about downstream driver authors supporting it. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgbouncer
On Thu, Jun 3, 2010 at 12:40 PM, Mario Ignacio Rodríguez Cortés wrote: > hello all: > > Well, i have a question if you know about this, i have a webserver in a > server and i have a database server, the question is: where should I > install the pgbouncer? in webserver, in database server or is the same? > whats your experience. my gut (guess) says here that if the db and web server are on different boxes, you will want to put pgbouncer on the web server box. my reasoning is that you have lots of libpq 'auths' going on to pgbouncer and far fewer real auths to the database, so you want the leg with the greater # of auths to get the local benefit. reason #2 is the less going on in a dedicated db box the better. I'm interested in this too...does anyone else have experience in this matter? merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Removing CONTEXT from RAISE EXCEPTION messages from triggers
Hello 2010/6/3 Peter Geoghegan : > Hello, > > Is it possible to avoid seeing a CONTEXT notice from error messages > returned by the server due to a RAISE EXCEPTION within a trigger? > yes \set VERBOSITY terse regards Pavel Stehule > -- > Regards, > Peter Geoghegan > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Removing CONTEXT from RAISE EXCEPTION messages from triggers
Hello, Is it possible to avoid seeing a CONTEXT notice from error messages returned by the server due to a RAISE EXCEPTION within a trigger? -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgbouncer
hello all: Well, i have a question if you know about this, i have a webserver in a server and i have a database server, the question is: where should I install the pgbouncer? in webserver, in database server or is the same? whats your experience. Thanks. ISC: Mario Ignacio Rodríguez Cortés. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Auto-partitioning in COPY
Bruce Momjian wrote: Leonardo F wrote: At this page: http://wiki.postgresql.org/wiki/Auto-partitioning_in_COPY I read: "The automatic hierarchy loading code is currently integrated in the code of the COPY command of Postgres 8.5" Is that true? It might be true for Aster Data's version of Postgres, but not for the community version. I have relabeled those pages as "Aster Data", and emailed the author mentioning the problem and asking why Aster features are being listed on our wiki, which causes confusion. These wiki pages described features that were submitted for community PostgreSQL a while back: https://commitfest.postgresql.org/action/patch_view?id=173 https://commitfest.postgresql.org/action/patch_view?id=205 But that were both returned with feedback suggesting they weren't suitable to apply, and so far they haven't been followed up on since. I'm going to revert your edits because they just make the situation more confusing (those aren't patches in Aster's product!) and label them correctly. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pgbouncer
On 06/03/2010 10:05 AM, Mario Rodriguez wrote: Well, i have a question if you know about this, i have a webserver in a server and i have a database server, the question is: where should I install the pgbouncer? in webserver, in database server or is the same? what's your experience. I haven't hammered pgbouncer super-hard but in my experience it is pretty lightweight so if you are so close to the edge on remaining resources on either your webserver or database-server that pgbouncer's resource use is an issue, then you probably have other issues to fix, first. I have run it on the webserver, on the db-server and on a third unrelated server without difficulty. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libreadline and Debian 5 - not missing just badly named
Thanks Dim, I didn't realise they had that package - probably because I tend to skim over the PostgreSQL related ones, assuming they're connected with the ready-built version. A good lesson for me. I'm new to Debian - just switched from Red Hat as Fedora seems to be getting monstrous and too user oriented (the machine is a small server, mostly used headless). J -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] pgbouncer
hello all: Well, i have a question if you know about this, i have a webserver in a server and i have a database server, the question is: where should I install the pgbouncer? in webserver, in database server or is the same? what's your experience. Thanks. ISC: Mario Ignacio Rodríguez Cortés.
[GENERAL] Altering Domain Constraints on composite types
While playing with domains and composite types, I discovered a problem
when I tried to alter a domain constraint. I don't believe that this
problem exists for traditional types.
for example:
broersr=> insert into tags values ((84,'PDSL',1,''),(84,'P',1, ''),'TEST TAG');
ERROR: invalid regular expression: invalid repetition count(s)
broersr=> \dD
List of domains
Schema | Name | Type |
Modifier| Check
+--+---++---
...
public | tag_function | character varying(4) | not null
| CHECK (VALUE::text ~ '^[A-Z]{2-4}$'::text)
...
(11 rows)
broersr=> --oops I made a mistake in the definition of my REGEX
constraint so lets fix it.
broersr=> begin;
BEGIN
broersr=> alter domain tag_function drop constraint valid_tag_function;
ALTER DOMAIN
broersr=> alter domain tag_function add constraint valid_tag_function
check(value ~ E'^[A-Z]{2,4}$');
ERROR: cannot alter type "tag_function" because column "tags"."tag" uses it
broersr=> rollback;
ROLLBACK
--
Regards,
Richard Broersma Jr.
Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock issues with partitioned table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/06/2010 16:00, Tom Lane wrote: > "Jehan-Guillaume (ioguix) de Rorthais" writes: >> Shouldn't locks only be on tables/indexes that are actually used by the >> planner ? > > Well, yeah, they are. The planner must take at least AccessShareLock > on any relation referenced by the query. It might later be able to > prove that the relation needn't be scanned to deliver the query answer, > but it first has to lock the relation enough to examine its constraints > before it can prove that. Understood, thanks. > Similarly, indexes get locked for the purpose > of inspecting them, whether or not they actually get selected for use in > the plan. Ok. One question though, as soon as the planer locked the table relation to check its CHECK contraint then exclude it from its plan (here test_1), it doesn't need to locks its indexes as well. So I guess the planer just lock everything first, tables and indexes, THEN, check the CHECK relations ? In a partitioned table couldn't it be 1/ lock the table relation 2/ check the CHECK constraint 3.1/ inclusion: lock the indexes 3.2/ exclusion: do nothing > > AccessShareLock is a weak enough lock that this generally isn't a > problem; all that it's doing is ensuring that the table's schema > doesn't change while we're trying to devise a plan. Yeah, that's my understanding. However, in the final schema I am messing with, there's 2 level of partitioning resulting to 409 child tables (!), each of them with 12 indexes. A simple request on the top table with correct conditions shows a good plan, but more than 6500+ locks. I agree the schema himself is definitely not the best though, and I already talked about that with its owner... > > regards, tom lane - -- Jehan-Guillaume (ioguix) de Rorthais -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwHxiMACgkQxWGfaAgowiLdDACfZNumDbI3KVPZoyxXbpGhKCoE rbIAnRfQmVwm3YF+WGKZ4JWKbGANVtkX =zfiP -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] server-side extension in c++
Bruce Momjian wrote: > Peter Geoghegan wrote: > > >> I would have > > >> imagined that ultimately, the call to the Pg C function must return, > > >> and therefore cannot affect stack unwinding within the C++ part of the > > >> program. > > > > > > That's the whole point; a longjmp breaks the call chain, and the > > > guarantee that eventually the stack will unwind as functions return. > > > > Yes, but my point was that if that occurs above the C++ code, it will > > never be affected by it. We have to longjmp() *over* C++ code before > > we have a problem. However, Bruce has answered the question of whether > > or not that happens - it does, so I guess it doesn't matter. > > Yes. I have updated the C++ doc patch to call it a "distant" > longjump(). Applied. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Auto-partitioning in COPY
Leonardo F wrote: > At this page: > > http://wiki.postgresql.org/wiki/Auto-partitioning_in_COPY > > > I read: > "The automatic hierarchy loading code is currently integrated > in the code of the COPY command of Postgres 8.5" > > Is that true? It might be true for Aster Data's version of Postgres, but not for the community version. I have relabeled those pages as "Aster Data", and emailed the author mentioning the problem and asking why Aster features are being listed on our wiki, which causes confusion. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] so, does this overlap or not...? - fencepost question on overlaps()
Frank van Vugt writes: > This doesn't seem to make sense to me, can someone explain the rationale > behind it? The rationale is "do what the SQL spec says" ;-) What the spec says is ( S1 > S2 AND NOT ( S1 >= T2 AND T1 >= T2 ) ) OR ( S2 > S1 AND NOT ( S2 >= T1 AND T2 >= T1 ) ) OR ( S1 = S2 AND ( T1 <> T2 OR T1 = T2 ) ) I seem to recall a previous discussion in the PG lists where we reverse-engineered a plausible explanation of what the standards committee had in mind when they wrote this, but I don't have time right now to go looking for it. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Lock issues with partitioned table
"Jehan-Guillaume (ioguix) de Rorthais" writes: > Shouldn't locks only be on tables/indexes that are actually used by the > planner ? Well, yeah, they are. The planner must take at least AccessShareLock on any relation referenced by the query. It might later be able to prove that the relation needn't be scanned to deliver the query answer, but it first has to lock the relation enough to examine its constraints before it can prove that. Similarly, indexes get locked for the purpose of inspecting them, whether or not they actually get selected for use in the plan. AccessShareLock is a weak enough lock that this generally isn't a problem; all that it's doing is ensuring that the table's schema doesn't change while we're trying to devise a plan. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to debug efficiently
On 6/3/2010 5:43 AM, Jamie Lawrence-Jenner wrote: > > Hi All > > In SQL Server I could copy sql code out of an application and paste it > into SSMS, declare & assign vars that exist in the sql and run.. yay > great debugging scenario. > > e.g. (please note I am rusty and syntax may be incorrect) > > declare @x as varchar(10) > set @x = 'abc' > select * from sometable where somefield = @x > > I want to do something simular with postgres in pgadmin3 (or another > postgres tool, anyy reccomendations?) > > I realise you can create pgscript, but it doesn't appear to be very > good, for example, if I do the equlivent of above, it doesn't put the > single quotes around the value in @x, nor does it let me by doubling > them up and you don't get a table out after - only text... > > Currently I have a peice of sql someone has written that has 3 unique > varibles in it which are used around 6 times each... > > So the question is how do other people debug sql this sql EFFICIENTLY, > preferably in a simular fashion to my sql server days. > > by pgscript I take you meaning pl/pgsql which unlike Transact-SQL is actually useful. to debug in PG with pgadmin we have http://pgfoundry.org/projects/edb-debugger/ that makes debugging pl/pgsql very easy and it works with pgadmin and http://www.sqlmaestro.com/products/postgresql/maestro/tour/pgsql_debugger/ Your little example would like so in pl/pgsql - Create or Replace function MyTest() returns integer AS $BODYOFFUNCTION$ declare x text = 'abc'; Begin perform (select * from sometable where somefield = x); end; return 1 ; $BODYOFFUNCTION$ LANGUAGE 'plpgsql' VOLATILE COST 100; All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. <> -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Performance drop after upgrading to 8.4.4?
Hi, I was doing some benchmarking while changing configuration options to try to get more performance out of our postgresql servers and noticed that when running pgbench against 8.4.3 vs 8.4.4 on identical hardware and configuration there is a large difference in performance. I know tuning is a very deep topic and benchmarking is hardly an accurate indication of real world performance but I was still surprised by these results and wanted to know what I am doing wrong. Hardware specs are: 2x Quad core Xeons 2.4Ghz 16GB RAM 2x RAID1 7.2k RPM disks Relevant Postgresql Configuration: max_connections = 1000 shared_buffers = 4096MB temp_buffers = 8MB max_prepared_transactions = 1000 work_mem = 8MB maintenance_work_mem = 512MB wal_buffers = 8MB checkpoint_segments = 192 checkpoint_timeout = 30min effective_cache_size = 12288MB Results for the 8.4.3 (8.4.3-2PGDG.el5) host: [r...@some-host ~]# pgbench -h dbs3 -U postgres -i -s 100 pgbench1 > /dev/null 2>&1 && pgbench -h dbs3 -U postgres -c 100 -t 10 pgbench1 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 query mode: simple number of clients: 100 number of transactions per client: 10 number of transactions actually processed: 1000/1000 tps = 4612.734318 (including connections establishing) tps = 4613.308264 (excluding connections establishing) Results for the 8.4.4 (8.4.4-1PGDG.el5) host: [root@ some-host ~]# pgbench -h dbs4 -U postgres -i -s 100 pgbench1 > /dev/null 2>&1 && pgbench -h dbs4 -U postgres -c 100 -t 10 pgbench1 starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 100 query mode: simple number of clients: 100 number of transactions per client: 10 number of transactions actually processed: 1000/1000 tps = 2799.134267 (including connections establishing) tps = 2799.451407 (excluding connections establishing) Any input? I can reproduce these numbers consistently. By the way, I am a new postgresql user so my experience is limited. Cheers, Max
Re: [GENERAL] Query Slow in Postgres 8.4.3 than Postgres 8.1.5
On 3 Jun 2010, at 24:42, federalbird wrote: > > The following query is very slow in Postgres 8.4.3 as compared to Postgres > 8.1.5. Please reply. Thanx in advance. Did you check the output of EXPLAIN ANALYSE to see if the plans are different between the two? Are your database settings identical? To improve your chances on an answer, does the problem still occur if you strip out all the irrelevant crud (like all those CASE's) from that query? I bet it does and it would make the query a whole lot more readable. I left the plain text version of your message below, as the HTML version was just one big blob of text and people may not realise the plain text version was less badly formatted: > select f.finance_company_name, b.brokerage_name, bc.quote_no as > ContractNumber, cl.first_name as ClientFirstName, cl.last_name as > ClientLastName, mcsh.status_type_cd as ContractStatus, > (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN > rp.num_retained > * monthly_amt ELSE 0 END)) as due_amount, > (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN > rp.num_retained > * monthly_amt ELSE 0 END)) as received_amount, > (gl.ds - gl.cs - gl.d + gl.c) as ledger, > (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs - gl.d + > gl.c) else 0 end) as active_ledger, > (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs+(CASE WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as active_due_amount, > (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.d - gl.c + (CASE WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as active_received_amount, > (case when mcsh.status_type_cd = 'ACTIVE' THEN 1 else 0 end) as > active_count, > (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs - gl.d > + > gl.c) else 0 end) as cancelled_ledger, > (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs+(CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as cancelled_due_amount, > (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.d - gl.c + (CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as cancelled_received_amount, > (case when mcsh.status_type_cd = 'CANCELLED' THEN 1 else 0 end) as > cancelled_count, > (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs - gl.d > + > gl.c) else 0 end) as default_ledger, > (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs+(CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as default_due_amount, > (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.d - gl.c + (CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as default_received_amount, > (case when mcsh.status_type_cd = 'DEFAULTED' THEN 1 else 0 end) as > default_count, > (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs - gl.d + > gl.c) else 0 end) as payout_ledger, > (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs+(CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as payout_due_amount, > (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.d - gl.c + (CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as payout_received_amount, > (case when mcsh.status_type_cd = 'PAIDOUT' THEN 1 else 0 end) as > payout_count, > (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs - gl.d + > gl.c) else 0 end) as expired_ledger, > (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs+(CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as expired_due_amount, > (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.d - gl.c + (CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as expired_received_amount, > (case when mcsh.status_type_cd = 'EXPIRED' THEN 1 else 0 end) as > expired_count, > (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs - gl.d + > gl.c) else 0 end) as writeoff_ledger, > (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs+(CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as writeoff_due_amount, > (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.d - gl.c + (CASE > WHEN > rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) > else 0 end) as writeoff_received_amount, > (case when mcsh.status_type_cd = 'WRITEOFF' THEN 1 else 0 end) as > writeoff_count, > (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs - gl.d + >
Re: [GENERAL] Query Slow in Postgres 8.4.3 than Postgres 8.1.5
Thought I'd reformat your query for readability: SELECT f.finance_company_name, b.brokerage_name, bc.quote_no AS contractnumber, cl.first_name AS clientfirstname, cl.last_nameAS clientlastname, mcsh.status_type_cd AS contractstatus, ( gl.ds - gl.cs + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) AS due_amount, ( gl.d - gl.c + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) AS received_amount, ( gl.ds - gl.cs - gl.d + gl.c ) AS ledger, ( CASE WHEN mcsh.status_type_cd = 'ACTIVE' THEN ( gl.ds - gl.cs - gl.d + gl.c ) ELSE 0 END ) AS active_ledger, ( CASE WHEN mcsh.status_type_cd = 'ACTIVE' THEN ( gl.ds - gl.cs + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) ELSE 0 END ) AS active_due_amount, ( CASE WHEN mcsh.status_type_cd = 'ACTIVE' THEN ( gl.d - gl.c + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) ELSE 0 END ) AS active_received_amount, ( CASE WHEN mcsh.status_type_cd = 'ACTIVE' THEN 1 ELSE 0 END ) AS active_count, ( CASE WHEN mcsh.status_type_cd = 'CANCELLED' THEN ( gl.ds - gl.cs - gl.d + gl.c ) ELSE 0 END ) AS cancelled_ledger, ( CASE WHEN mcsh.status_type_cd = 'CANCELLED' THEN ( gl.ds - gl.cs + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) ELSE 0 END ) AS cancelled_due_amount, ( CASE WHEN mcsh.status_type_cd = 'CANCELLED' THEN ( gl.d - gl.c + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) ELSE 0 END ) AS cancelled_received_amount, ( CASE WHEN mcsh.status_type_cd = 'CANCELLED' THEN 1 ELSE 0 END ) AS cancelled_count, ( CASE WHEN mcsh.status_type_cd = 'DEFAULTED' THEN ( gl.ds - gl.cs - gl.d + gl.c ) ELSE 0 END ) AS default_ledger, ( CASE WHEN mcsh.status_type_cd = 'DEFAULTED' THEN ( gl.ds - gl.cs + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) ELSE 0 END ) AS default_due_amount, ( CASE WHEN mcsh.status_type_cd = 'DEFAULTED' THEN ( gl.d - gl.c + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) ELSE 0 END ) AS default_received_amount, ( CASE WHEN mcsh.status_type_cd = 'DEFAULTED' THEN 1 ELSE 0 END ) AS default_count, ( CASE WHEN mcsh.status_type_cd = 'PAIDOUT' THEN ( gl.ds - gl.cs - gl.d + gl.c ) ELSE 0 END ) AS payout_ledger, ( CASE WHEN mcsh.status_type_cd = 'PAIDOUT' THEN ( gl.ds - gl.cs + ( CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END ) ) ELSE 0 END ) AS payout_due_amount, ( CASE WHEN mcsh.status_type_cd = 'PAIDOUT' THEN ( gl.d - gl.c + ( CASE WHEN rp.num_
Re: [GENERAL] libreadline and Debian 5 - not missing just badly named
"J. Bagg" writes: > I've just had the common problem with not finding the readline library while > compiling/linking 8.4.4 on a new linux (Debian 5 - lenny). Tried: apt-get build-dep postgresql-8.4 That command will install all what you need to compile your own PostgreSQL. Some will add "and some more", because it will care for doc building dependencies to. Regards, -- dim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Query Slow in Postgres 8.4.3 than Postgres 8.1.5
The following query is very slow in Postgres 8.4.3 as compared to Postgres 8.1.5. Please reply. Thanx in advance. select f.finance_company_name, b.brokerage_name, bc.quote_no as ContractNumber, cl.first_name as ClientFirstName, cl.last_name as ClientLastName, mcsh.status_type_cd as ContractStatus, (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) as due_amount, (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) as received_amount, (gl.ds - gl.cs - gl.d + gl.c) as ledger, (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as active_ledger, (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as active_due_amount, (case when mcsh.status_type_cd = 'ACTIVE' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as active_received_amount, (case when mcsh.status_type_cd = 'ACTIVE' THEN 1 else 0 end) as active_count, (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as cancelled_ledger, (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as cancelled_due_amount, (case when mcsh.status_type_cd = 'CANCELLED' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as cancelled_received_amount, (case when mcsh.status_type_cd = 'CANCELLED' THEN 1 else 0 end) as cancelled_count, (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as default_ledger, (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as default_due_amount, (case when mcsh.status_type_cd = 'DEFAULTED' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as default_received_amount, (case when mcsh.status_type_cd = 'DEFAULTED' THEN 1 else 0 end) as default_count, (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as payout_ledger, (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as payout_due_amount, (case when mcsh.status_type_cd = 'PAIDOUT' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as payout_received_amount, (case when mcsh.status_type_cd = 'PAIDOUT' THEN 1 else 0 end) as payout_count, (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as expired_ledger, (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as expired_due_amount, (case when mcsh.status_type_cd = 'EXPIRED' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as expired_received_amount, (case when mcsh.status_type_cd = 'EXPIRED' THEN 1 else 0 end) as expired_count, (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as writeoff_ledger, (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as writeoff_due_amount, (case when mcsh.status_type_cd = 'WRITEOFF' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as writeoff_received_amount, (case when mcsh.status_type_cd = 'WRITEOFF' THEN 1 else 0 end) as writeoff_count, (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs - gl.d + gl.c) else 0 end) as rescind_ledger, (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.ds - gl.cs+(CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as rescind_due_amount, (case when mcsh.status_type_cd = 'RESCIND' THEN (gl.d - gl.c + (CASE WHEN rp.num_retained IS NOT NULL THEN rp.num_retained * monthly_amt ELSE 0 END)) else 0 end) as rescind_received_amount, (case when mcsh.status_type_cd = 'RESCIND' THEN 1 else 0 end) as rescind_count from nq_finance_company f inner join nq_group g on (f.finance_company_id = g.group_id and g.group_id =3299) inner join nq_group ug on (g.left_index < ug.left_index and g.right_index
Re: [GENERAL] tsearch2 & dictionaries - possible problem
Ivan,
did you found your misunderstooding ? You forget how dictionaries work.
You need to put some dictionary, which recognize anything, like simple, or
stemmer dictionary to recognize 'unknown' word. Look into documentation.
Oleg
On Wed, 2 Jun 2010, Ivan Voras wrote:
hello,
I think I have a problem with tsearch2 configuration I'm trying to use.
I have created a text search configuration as:
--
CREATE TEXT SEARCH DICTIONARY hr_ispell (
TEMPLATE = ispell,
DictFile = 'hr',
AffFile = 'hr',
StopWords = 'hr'
);
CREATE TEXT SEARCH CONFIGURATION public.ts2hr (COPY=pg_catalog.english);
ALTER TEXT SEARCH CONFIGURATION ts2hr
ALTER MAPPING FOR asciiword, asciihword, hword_asciipart, word,
hword, hword_part
WITH hr_ispell;
SET default_text_search_config = 'public.ts2hr';
--
and here are some queries:
--
cms=> select to_tsvector('voras vorasom');
to_tsvector
-
(1 row)
cms=> SET default_text_search_config = 'simple';
SET
cms=> select to_tsvector('voras vorasom');
to_tsvector
---
'voras':1 'vorasom':2
(1 row)
cms=> SET default_text_search_config = 'ts2hr';
SET
cms=> select to_tsvector('voras vorasom');
to_tsvector
-
(1 row)
cms=> select to_tsvector('kiЪЪa kiЪЪi');
to_tsvector
-
'kiЪЪa':1,2
(1 row)
--
The good news is that the text search configuration is actually used
(the 'kiЪЪa kiЪЪi') example but apparently on an uncommon word,
to_tsvector() returns nothing (the 'voras vorasom' example).
Is there something wrong in the configuration? I would definitely not
want unknown words to be ignored.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] so, does this overlap or not...? - fencepost question on overlaps()
Hi,
This doesn't seem to make sense to me, can someone explain the rationale
behind it?
postgres=# select version();
version
---
PostgreSQL 8.4.4 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC)
4.3.3, 64-bit
(1 row)
Range 1 ending on date A does not overlap with range 2 starting on date A:
postgres=# select ('2010-01-01'::date, '2010-01-05'::date) overlaps
('2010-01-05'::date, '2010-01-10'::date);
overlaps
--
f
(1 row)
But it does when range 1 is only a single day:
postgres=# select ('2010-01-05'::date, '2010-01-05'::date) overlaps
('2010-01-05'::date, '2010-01-10'::date);
overlaps
--
t
(1 row)
BTW, it doesn't matter whether one casts to date or timestamp
--
Best,
Frank.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Attempting to get kerberos authentication working
On Wed, Jun 2, 2010 at 22:42, Bryan Montgomery wrote: > Hello, > I'm trying to get kerberos working with postgres 8.4 on openSUSE > authenticating against AD. I have the server configured and can do a kinit > against my account on the server. I have a keytab file produced by the > administrators. > > $ klist -kt poe3b.keytab > Keytab name: FILE:bob.keytab > KVNO Timestamp Principal > - > > 1 12/31/69 19:00:00 HTTP/bob.lab2k@lab2k.net > I've added > krb_srvname = 'HTTP' to postgresl.conf Have you also added this on the client side? Either to the connection string or to the environment variable? And if you did it with the environment variable, double-check that it actually took effect in the client app - sometimes you need to log out and back in again when using the GUI editors, and if you changed it from the commandline it might simply be gone. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to debug efficiently
On 3 Jun 2010, at 12:43, Jamie Lawrence-Jenner wrote: > Hi All > > In SQL Server I could copy sql code out of an application and paste it into > SSMS, declare & assign vars that exist in the sql and run.. yay great > debugging scenario. > > e.g. (please note I am rusty and syntax may be incorrect) > > declare @x as varchar(10) > set @x = 'abc' > select * from sometable where somefield = @x > > I want to do something simular with postgres in pgadmin3 (or another postgres > tool, anyy reccomendations?) I don't use pgadmin, I usually use psql. If it gets complicated enough that it's inconvenient to use from the psql prompt, I usually just whip up a quick script to test stuff like this in. I often keep those in a tests directory using a descriptive name for what the script tests, so that co-workers can easily see what they are. So far that's usually been PHP (for work), but I have the feeling that I could create something that'd be usable from within the Python interpreter prompt, allowing to adjust definitions while you get further in your debugging session. > I realise you can create pgscript, but it doesn't appear to be very good, for > example, if I do the equlivent of above, it doesn't put the single quotes > around the value in @x, nor does it let me by doubling them up and you don't > get a table out after - only text... Never heard of pgscript, is that something that pgadmin provides? I have no idea how it interprets parameter values or how it outputs query results, so I don't understand what problems you're seeing. > Currently I have a peice of sql someone has written that has 3 unique > varibles in it which are used around 6 times each... It's probably easiest to create a prepared statement from that query and execute it with different parameter values. It depends on what you're debugging though; if that's a performance problem, then turning the query into a prepared statement could (probably would) change the behaviour. > So the question is how do other people debug sql this sql EFFICIENTLY, > preferably in a simular fashion to my sql server days. I usually find the psql prompt more efficient to work with than, for example, pgadmin. But I'm a typical command line user; What's efficient for one may not be for someone else. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4c078fa710151658735671! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to debug efficiently
Hi All In SQL Server I could copy sql code out of an application and paste it into SSMS, declare & assign vars that exist in the sql and run.. yay great debugging scenario. e.g. (please note I am rusty and syntax may be incorrect) declare @x as varchar(10) set @x = 'abc' select * from sometable where somefield = @x I want to do something simular with postgres in pgadmin3 (or another postgres tool, anyy reccomendations?) I realise you can create pgscript, but it doesn't appear to be very good, for example, if I do the equlivent of above, it doesn't put the single quotes around the value in @x, nor does it let me by doubling them up and you don't get a table out after - only text... Currently I have a peice of sql someone has written that has 3 unique varibles in it which are used around 6 times each... So the question is how do other people debug sql this sql EFFICIENTLY, preferably in a simular fashion to my sql server days. Regards Dan
[GENERAL] Auto-partitioning in COPY
At this page: http://wiki.postgresql.org/wiki/Auto-partitioning_in_COPY I read: "The automatic hierarchy loading code is currently integrated in the code of the COPY command of Postgres 8.5" Is that true? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Lock issues with partitioned table
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I have some trouble understanding the locking policy with partitioned tables. Here is a simple schema based on a real one: CREATE DATABASE test; \c test CREATE TABLE test( id integer PRIMARY KEY, id_dummy integer, id_part1 integer ); CREATE INDEX i_fk_test_dummy ON test (id_dummy); CREATE INDEX i_fk_test_part1 ON test (id_part1); CREATE TABLE test_1 ( CONSTRAINT test_1_pkey PRIMARY KEY (id), CONSTRAINT test_1_check_part1 CHECK (id_part1 = 1) ) INHERITS (test); INSERT INTO test_1 SELECT x, RANDOM(), 1 FROM generate_series(1,1000,1) AS t(x); CREATE INDEX i_fk_test_1_dummy ON test_1 (id_dummy); CREATE INDEX i_fk_test_1_part1 ON test_1 (id_part1); CREATE TABLE test_2 ( CONSTRAINT test_2_pkey PRIMARY KEY (id), CONSTRAINT test_2_check_part1 CHECK (id_part1 = 2) ) INHERITS (test); INSERT INTO test_2 SELECT x, RANDOM(), 2 FROM generate_series(1,1000,1) AS t(x); CREATE INDEX i_fk_test_2_dummy ON test_2 (id_dummy); CREATE INDEX i_fk_test_2_part1 ON test_2 (id_part1); ANALYZE; Explain on "SELECT id FROM test WHERE id_part1=2;" gives me: Result (cost=4.33..32.38 rows=1010 width=4) -> Append (cost=4.33..32.38 rows=1010 width=4) -> Bitmap Heap Scan on test (cost=4.33..14.88 rows=10 width=4) Recheck Cond: (id_part1 = 2) -> Bitmap Index Scan on i_fk_test_part1 (cost=0.00..4.33 rows=10 width=0) Index Cond: (id_part1 = 2) -> Seq Scan on test_2 test (cost=0.00..17.50 rows=1000 width=4) Filter: (id_part1 = 2) Which looks perfect (but those indexes on table test which is supposed to be empty so that are useless and add the Bitmap nodes instead of one costless seqscan). But pg_locks shows me something I don't understand: test=# SELECT c.relname, l.locktype, l.mode FROM pg_locks l JOIN pg_class c ON (c.oid=l.relation) WHERE l.pid=pg_backend_pid(); relname | locktype | mode +--+- pg_locks | relation | AccessShareLock pg_class | relation | AccessShareLock pg_class_oid_index | relation | AccessShareLock pg_class_relname_nsp_index | relation | AccessShareLock test_pkey | relation | AccessShareLock i_fk_test_dummy| relation | AccessShareLock i_fk_test_part1| relation | AccessShareLock test | relation | AccessShareLock test_1 | relation | AccessShareLock test_1_pkey| relation | AccessShareLock i_fk_test_1_dummy | relation | AccessShareLock i_fk_test_1_part1 | relation | AccessShareLock test_2 | relation | AccessShareLock test_2_pkey| relation | AccessShareLock i_fk_test_2_dummy | relation | AccessShareLock i_fk_test_2_part1 | relation | AccessShareLock (16 rows) Why do we lock on i_fk_test_dummy, all test_1 related relations and i_fk_test_2_dummy ? Actually I don't even understand why we lock on pkeys as well here... Shouldn't locks only be on tables/indexes that are actually used by the planner ? Thanks for lights and feedbacks ! -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iEYEARECAAYFAkwHgBoACgkQxWGfaAgowiIgDACdE8qz/AxHHkUfyuuhKMdxz14j 1QQAn355bPAxfFsuWP1qmjmtH1TGboUF =LicT -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What Linux edition we should chose?
On 06/01/2010 03:34 AM, Tom Lane wrote: > Ivan Sergio Borgonovo writes: >> On Mon, 31 May 2010 08:47:25 -0600 >> Scott Marlowe wrote: >>> Pgsql is pretty easy to build from source. > >> Yeah it is. But what is it going to be an upgrade process? On a >> production box? > > If it makes you feel better, build your own RPMs (or > $package-style-of-choice). This is actually a pretty good idea if you > are on a package-manager-based platform, as it makes it far simpler to > keep track of exactly what you've got installed. It's generally not > hard to take the source package supplied by your distro and stick a > new minor-release source tarball into it. Amen. We do this for anything not supplied with RHEL, although our first trip is usually a quick look at the EPEL repos to see if they have a suitable build we can use. As an aside, though, I personally gave up the gotta-have-the-latest treadmill some time ago. There's a lot to be said for letting a distribution engineering team spend the time and effort tracking security fixes and suchlike. (And to answer the original question, I'd use RHEL or CentOS; but these things tend to devolve into a simple way of exposing the distro prejudices of the responders) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] server-side extension in c++
David Fetter wrote: It's good to have actual working code in production to bolster the case that the design is sound. How much work would it be to refactor libgeos_c to use a catch-all exception handler? Cheers, David. Given that GEOS is not used exclusively by PostGIS but also by quite a few other open source GIS packages, I'd say quite small unless it was a very minimal change. I also should point out that I have very little C++ experience, and so would be the wrong person to ask ;) ATB, Mark. -- Mark Cave-Ayland - Senior Technical Architect PostgreSQL - PostGIS Sirius Corporation plc - control through freedom http://www.siriusit.co.uk t: +44 870 608 0063 Sirius Labs: http://www.siriusit.co.uk/labs -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] libreadline and Debian 5 - not missing just badly named
Thanks for the pointer to the correct packages. I didn't realise that the dev versions had the generic libs but, yes, you do need them for the headers anyway. Apologies for wasting time. J -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Commit every N rows in PL/pgsql
> > > > ... > > AfterTriggerEvents: 2642403328 total in 327 blocks; 10176 free (319 > > chunks); 2642393152 used > > And there's the problem. Evidently you have an AFTER trigger on the > table, and the queued events for that trigger are overrunning memory. > That's interesting - I don't know of any triggers on this table, but it does have a lot of foreign key constraints. Would they fall into the same category? -- len.wal...@gmail.com skype:lenwalter msn:len.wal...@gmail.com
