Re: [GENERAL] Postgres doesn't use indexes for prefix matching?
On Fri, 2 Jul 2004, Jon Valvatne wrote: > Pasted below, I demonstrate two queries which both produce the same two > records in their result set (the two objects which have my full name in > their name field). Based on my experiences with other DBMS, I would have > thought Postgres could do a simple index scan for both queries and thus > produce the results equally fast in both cases, but not so. In the > second case it fails to use the index, falling back to filtering a slow > sequential scan. > > I can't seem to find anything in the manual about this, but I'm sure > I've read several places that Postgres can do what I want here. The only > place I can dig up right now is in contrib/fulltextindex/README.fti, > which mentions: "If a ~ search starts with a ^ (match start of string), > btree indices can be used by PostgreSQL." > > Is there something I am doing wrong? Prefix matching using indexes is available in "C" locale or if you have an index in a special opclass (_pattern_ops -- I think the operator class part of the docs describe them). ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Formatting Function..
[EMAIL PROTECTED] (Vinay Jain) wrote in news:[EMAIL PROTECTED]: > Hi.. > I am newbe in postgresql so please help me though the question may be > very easy to answer.. > Is there any formatting function to get output with fix lengths..for > example my query is.. > schema is: > > Student > (name Varchar, > age integer); > > select name, age from student; > the output is like this.. > Name | Age > xyz | 22 I am a little curious about where you want your data to be presented. If it is only output in psql or other places where text is presented with a fixed with font then my suggestions will help. (If the data is to be outputted in html format then there are other methods.) One option is to store text data in char coloumns, it will fill the field with spaces to the left. This i will only recommend in special cases. Use formatting functions instead for text use lpad and rpad for numbers use to_char example: select rpad(name,20) as 'Name', to_char(age,'') as 'Age' from student; read more about it in the manual (Chapter 9 in the 7.4. documentation) -- Rolf ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] Postgres doesn't use indexes for prefix matching?
Your message from 7/2 just showed up today. > db=# explain analyze SELECT id FROM object WHERE name ~ '^Jon V'; I use leading substring indexing all the time. Try: SELECT id FROM object WHERE name like 'Jon V%'; Wes ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Row-level security--is it possible?
Michal Taborsky wrote:
> Doug McNaught wrote:
>> But why not create a "products_restricted" view that uses the
>> CURRENT_USER function to see who's running it?
>>
>> CREATE VIEW products_restricted AS
>> SELECT * FROM products WHERE Producer_ID = get_producer_id(CURRENT_USER);
>>
>> [CURRENT_USER returns a string, so you would need to map it to your
>> producer_id somehow.]
>
> This would work only for this case (limiting single producer to one
> user). But we want to have a bit more flexible system, so we'd be able
> define the restrictions freely (like "only producers 1 and 5 and price
> less than 100"). I'm sorry I did not mention this.
>
How about something like:
CREATE TABLE perms (
user text not null,
producer int non null,
constraint user_once_per_producer unique (user,producer)
);
CREATE FUNCTION prods_for_user () RETURNS SETOF INT AS '
select producer from perms where user = CURRENT_USER;
' LANGUAGE SQL STABLE;
INSERT INTO perms ('pete',100);
INSERT INTO perms ('joe',100);
INSERT INTO perms ('joe',101);
...
CREATE VIEW restricted_products AS SELECT * FROM products where producer_id
in (select prods_for_user());
-- END
Now, mind you, I've not used set returning functions myself so the syntax
may be off, but I think you can see the idea there.
--miker
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Problems restarting after database crashed (signal 11).
At Sat, 03 Jul 04, Unidentified Flying Banana Greg Stark, said: > Is there an NFS server involved? If an NFS server disappears any process > waiting on I/O for it enters disk-wait indefinitely until it reappears. Nope. Everything was local on this machine. > -- > greg -- | Christopher ++ | Here I stand. I can do no other. | ++ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] How to filter on timestamps?
Gentle people, I don't get it. I have a table where one of the columns is of type 'TIMESTAMP' How can I do a query to filter on the TIMESTAMP value, e.g. to obtain all rows earlier than a certain time stamp? Regards, ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] Slony-I Release 1.0 available
The Slony-I team proudly presents the 1.0 Release of its new enterprise-level replication solution for PostgreSQL. After more than 9 months of design, planning, development and testing, sponsored by the global domain registry company Afilias, we believe that this software has reached production quality. Please visit the project homepage at http://slony.info to download the software, subscribe to the mailing list, view documentation or join the development team. If you plan to deploy replication for your PostgreSQL databases, don't miss the Slony-I Configuration Workshop hosted by Afilias on July 31, 2004 (Saturday after OSCON) in the Portland Marriott Downtown Hotel in Portland, OR. The registration form can be downloaded here: http://developer.postgresql.org/~wieck/SlonyWorkshop.pdf -- Jan Wieck Software Engineer Afilias 300 Welsh Road - Building 3, Suite 105 Horsham, PA 19044 Tel: +1-215-706-5700 x118 Fax: +1-215-706-5701 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] enable thready safety on Mac OS X 10.3.4
On 7/8/04 11:28 AM, "Bruce Momjian" <[EMAIL PROTECTED]> wrote: > OK, I have removed the changes I just added to allow threads for 7.4.X > on OSX. This stuff had to be dealt with before 7.4 final, and I don't > want to play with it at this point. 7.5 thread testing is automatic so > people will have to wait for that. Just as a note to anyone searching the archives... # tools/thread/thread_test must be run THREAD_SUPPORT=yes THREAD_CPPFLAGS="-D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS" THREAD_LIBS="-lpthread" NEED_REENTRANT_FUNCS=yes works on both OS X 10.2 and 10.3 Wes ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] enable thready safety on Mac OS X 10.3.4
[EMAIL PROTECTED] wrote: > On 7/8/04 11:28 AM, "Bruce Momjian" <[EMAIL PROTECTED]> wrote: > > > OK, I have removed the changes I just added to allow threads for 7.4.X > > on OSX. This stuff had to be dealt with before 7.4 final, and I don't > > want to play with it at this point. 7.5 thread testing is automatic so > > people will have to wait for that. > > > Just as a note to anyone searching the archives... > > # tools/thread/thread_test must be run > THREAD_SUPPORT=yes > THREAD_CPPFLAGS="-D_REENTRANT -D_THREAD_SAFE -D_POSIX_PTHREAD_SEMANTICS" > THREAD_LIBS="-lpthread" > > NEED_REENTRANT_FUNCS=yes > > > works on both OS X 10.2 and 10.3 > OK, I put this back in for 7.4.X since you tested to OSX versions, which helps me think it is reliable. Thanks. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] Enough RAM for entire Database.. cost aside, is this
Greetings! On Fri, 2 Jul 2004, Mike Rylander wrote: > I find that experience does not bear this out. There is a saying a coworker > of mine has about apps that try to solve problems, in this case caching, > that are well understood and generally handled well at other levels of the > "software stack"... he calls them "too smart by half" :) But on the other hand, general algorithms which are designed to work under a wide variety of circumstances may fail in specific cases. I am thinking of VACUUM which would kill most caching algorithms simply because we cannot tell the O/S "by the by, this set of pages will not be used again, and therefore it would be fine to use almost none of the general cache to store this". All algorithms have assumptions of value distribution and usages. Caches depend on locality of reference, and we do not have an easy way to say when this is broken. Regards! Ed ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] enable thready safety on Mac OS X 10.3.4
On Thu, 8 Jul 2004 14:25:48 -0400 (EDT), Bruce Momjian <[EMAIL PROTECTED]> wrote: > > works on both OS X 10.2 and 10.3 > > OK, I put this back in for 7.4.X since you tested to OSX versions, which > helps me think it is reliable. Thanks. I can confirm this as well, and I'll be including these changes in the next release of the PostgreSQL packages for Fink on Mac OS X. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[GENERAL] Subtransaction syntax in 7.5??
I'm thrilled by the news that nested transactions will make it into 7.5 after all, and trying to learn how this will work. The question is what will be the syntax to start / commit / rollback a subtransaction. Leaking a bit from pgsql-hackers [hope it's not bad etiquette to do this] I see this very subject was the subject of some lively discussion as recently as 3 days ago. Could one of you kind folk on the hackers list post something here when there's a resolution of this? Just a pointer to a message on the hackers list would be fine ... -Thanks, Jim ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] How to filter on timestamps?
On Tue, Jul 06, 2004 at 01:27:29AM +0200, B.W.H. van Beest wrote:
> I have a table where one of the columns is of type 'TIMESTAMP'
>
> How can I do a query to filter on the TIMESTAMP value, e.g. to obtain
> all rows earlier than a certain time stamp?
SELECT ... WHERE timestamp_field < 'certain timestamp';
You may need an explicit cast to timestamp in the constant, like
cast('certain timestamp' as timestamp)
--
Alvaro Herrera ()
"El día que dejes de cambiar dejarás de vivir"
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Subtransaction syntax in 7.5??
Jim Rosenberg wrote: > I'm thrilled by the news that nested transactions will make it into 7.5 > after all, and trying to learn how this will work. The question is > what will be the syntax to start / commit / rollback a subtransaction. > > Leaking a bit from pgsql-hackers [hope it's not bad etiquette to do this] > I see this very subject was the subject of some lively discussion as > recently as 3 days ago. > > Could one of you kind folk on the hackers list post something here when > there's a resolution of this? Just a pointer to a message on the hackers > list would be fine ... We are still discussing --- no resolution yet. If you want to be involved, you will have to subscribe to hackers. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] help required
On Mon, Jul 05, 2004 at 05:40:34PM +0800, Ramesh Yeligar wrote: > We have been using pgsql for our retail business, now, due hard drive > crash, the databse corrupted and we are unable to start pgsql > database. Pl help me if you know any commands or tools to recover this > database. You'd need to post more details, like the server logs that show why you are unable to start pgsql. -- Alvaro Herrera () "Cada quien es cada cual y baja las escaleras como quiere" (JMSerrat) ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] how can Iconnect a Postgres database using odbc in c language?
On 02/07/2004 16:02 Maus wrote: [snip] I'd like to know the equivalent instructions in C language using ODBC for connecting a Postgres DB (with also includes library) Please, is there anyone can tell me which these C language instructions are? ODBC involves a fair bit more programming than JDBC. I suggest you search the net for an ODBC tutorial. You can find the API documentation on Microsoft's web site. HTH -- Paul Thomas +--+-+ | Thomas Micro Systems Limited | Software Solutions for Business | | Computer Consultants | http://www.thomas-micro-systems-ltd.co.uk | +--+-+ ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] unexpected update behavior with temp tables
On Thu, Jul 08, 2004 at 09:28:16AM -0500, Timothy Perrigo wrote: > Thanks for the reply, Stephan. I guess I can see the rationale for > this, though it is quite easy to cause yourself quite a bit of grief. > It would certainly make things safer if columns in the subselect which > refer to columns in the table from the outer query where required to be > fully specified (i.e. "foo.id", instead of just "id"), but if this > behavior is part of the standard, I imagine there's little chance of > changing it... Not to mention the amount of SQL code out there it would break! We use this feature a lot. -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them. pgpZT2rr1PIYT.pgp Description: PGP signature
Re: [GENERAL] Slow deletion of data from tables
On Fri, 2004-07-02 at 04:35, Rune Froysa wrote: > I have one table with columns that are used as foreign-keys from > several other tables. Sometimes deletion from this table takes +5 > seconds for a single row. Do you have indexes on the foreign key columns in the child tables? For example, say you have a person table with primary key person_id and a hobby table where each row uses person_id as a foreign key. When you create the person table and declare person_id as the primary key, PostgreSQL will automatically create a unique index on that column. When you create the hobby table and declare its person_id references person_id in the person table then PostgreSQL automatically installs triggers on the hobby table to ensure updates meet the constraint *and* it installs triggers on the person table to ensure updates there don't make records in the hobby table invalid. But no extra indexes will be created. If you delete from the person table, a trigger will fire and run a query something like this: SELECT 1 FROM ONLY hobby x WHERE person_id = $1 FOR UPDATE OF x If you don't have a (non-unique) index on the person_id column in the hobby table then this query will require a full table scan. So, as a rule of thumb, if a table contains a foreign key, you should create a non-unique index on that column. There may be good reasons not to bother in certain cases, but it's a good starting point. Regards Grant ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] ANNOUNCE: Bricolage 1.8.1
The Bricolage development team is pleased to announce the release of Bricolage 1.8.1. This maintenance release address a number of issues in Bricolage 1.8.0. Here are the highlights: Improvements * More complete Traditional Chinese and Simplified Chinese localizations. Also, the Mandarin localization now simply inherits from the Traditional Chinese localization. * make clone now copies the lib directory and all of the bin scripts from the target to the clone, rather than from the sources. This allows any changes that have been made to scripts and classes to be properly cloned. * When installing Bricolage, it will now allow you to proceed if the database already exists by asking if you want to create the Bricolage tables in the existing database. Suggested by Mark Fournier and Marshall Roch. * The installer is now a bit smarter in how it handles loading the log_config (or config_log, as the case may be) module. * Added language-specific style sheets. This is especially useful for right-to-left languages or for languages that require special fonts. * The "New Alias" search interface now displays thumbnails when searching for media documents to alias and the USE_THUMBNAILS bricolage.conf directive is enabled. * Aliases can now be made to documents within the same site. * The SOAP interface for importing and exporting elements now properly has "key_name" XML elements instead of "name" XML elements. The changes are backwards compatible with XML exported from Bricolage 1.8.0 servers, however. * Added move() method to the virtual FTP interface. This means that to deploy a template, rather than having to rename it locally to append ".deploy", one can simply move in FTP to its new name with ".deploy" on appended to the new name. * Document expirations are now somewhat more intelligent. Rather than just scheduling an expiration job only if there is an expiration date the first time a document is published, Bricolage will now always schedule an expiration job for a document provided that one does not already exist (scheduled or completed) for the same time and for one of the file resources for the document. This should allow people to more easily and arbitrarily expire content whenever necessary. * Burner notes now persist for all sub burns (triggered by publish_another() and preview_another() in a single burn. * Added ability to create and manage groups of objects for several different types of objects. Also added the ability manage group membership within the administrative profiles for those objects. This change makes it possible to give users permission to administer subsets of objects. The new groupable objects are: Preferences Groups Alert Types Element Types Keywords Contributors * Alert rules are now evaluated within a safe compartment (using Safe.pm) to prevent security exploits. * The Bulk Publish admin tool is no longer limited to use only by members of the Global Admins group. Now anyone can use it. All one needs is READ permission to the categories of stories, and PUBLISH permission to the stories and media documents to be published. Bug Fixes * Eliminated 'Bareword "ENABLE_HTMLAREA" not allowed while "strict subs" in use' warning that prevented startup for some installations. * Changes made to user or contributor contacts without changing any other part of the user or contributor object are now properly saved. * The upgrade to 1.8.0 now correctly updates story URIs that use the URI Suffix of an output channel instead of using the URI Prefix twice. * Aliases of Image, Audio, or Video media documents no longer remain stuck on desks. * Related media and story subelements of media documents now work properly. * Calls to preview_another() in Bric::Util::Burner will now use any templates in the current user's sandbox and properly burn them to the preview root rather than to the staging root used for publishing. * Contributor fields for roles other than the default role now properly store and retain their values. * The virtual FTP server now properly checks out templates when a template is uploaded and is already in workflow. * Uploading a non-existent template via the virtual FTP server now correctly creates a new template. The type of template depends on the name of the template being uploaded, and for element templates, on whether there is an element with the appropriate key name. The user must have CREATE permission to All Templates or to the start desk in the first template workflow in the relevant s
[GENERAL] HELP
I accidentaly deleted over 1.000.000 records from one table in my database. Is there any way to recover them? Please help me as soon as possible... After the records were deleted, no other operation was made on the database. In fact, all the postgres services were shut down. I found a xlog file containing those records but I don't know the structure of this file. This is Postgres 7.2.1 on Mandrake 8.1 Marek Nowiński ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] Training and certification
I realise that these have been discuuseed before, but a couple of things have happened that caused me to bring this up again, and to raise some questions. A couple of nights ago, a seminar was presented in Perth, Western Australia, by an institution offering IT masters degrees. One of the masters degrees, was a Master of Systems Development (MSD). Their masters degrees incorporated industry certification. The MSD incorporated the Microsoft MCAD and MCSD, and related to .NET systems development. The institution is also investigating possibly offering a similar masters degree, relating specifically to, or emphasising, databases. After the presentation, I asked the presenter about the possibility of incorporating open source, rather than proprietary system development, with open source industry certification. Unfortunately, the only open source industry certification that was relevant, that I could cite, was the MySQL certification set, along with LPI and RHCE certification, with no apparent industry certification for PostgreSQL. The presenter said that the institution was considering Oracle as a possibility in the future, Oracle having industry certification. Thus, recognised, international, industry certification of open source application systems development, either involving PostgreSQL as a database backend by itself, or, involving PostgreSQL as a factor (eg, open source database applications development involving either PostgreSQL or MySQL as separate options, or, competency with both), could be useful, apart from having the internationally recognised industry certification in its own right, encouraging acceptance of open source software development in such qualifications as these masters degrees with their incorporated recognised international industry certification. In trying to find the organisation that I had understood to be the main one for providing PostgreSQL certification (I had understood that it was PostgreSQL.com, or something like that, or, possibly pgsql.com), and, being unable to find any details of any competency-based certification at these sites, I did a bit of searching, and I found a postgresql.org web page at http://www.postgresql.org/survey.php?View=1&SurveyID=22 , which gave the results of a survey, with the question "Should we create a standard worldwide PostgreSQL training course?", with 79.691% of the votes, being votes for the yes side - and of that percentage, 50.386 "strongly yes". The survey is not dated, so I do not know how long ago it was held. However, it appears to have indicated support for "a standard worldwide PostgreSQL training course". On the web page, whils other survey questions are listed, no further reference is made to the result of the survey, for example, "The PostgreSQL guru's are developing a standard worldwide PostgreSQL traing course, in response to the survey results". So, I ask, given the result of the poll, however old it is, is any action being taken, to develop "a standard worldwide PostgreSQL training course", or set of such courses (eg, core, DBA, developer, PHP|Perl/PostgreSQL web developer, etc)? I also found a web page at http://advocacy.postgresql.org/advantages/?lang=en , in which the text was included; "our training programs are generally regarded as being more cost-effective, manageable, and practical in the real world than that of the leading proprietary database vendors.". To what training programs, does this refer? Are they standardised, or, does this refer to separate, independent, autonomous, individual training programs that are not standardised? Also, in my searching, I did manage to find a certification at http://www.postgresql.at/certification.html , which also has a link to training provided by that company. From the web site, it appears that the training/certtification provided by that company, is of ten days duration, assumedly of 8 hours per day, thence 80 hours, the equivalent of about 6 hours per week over a 13 week period, plus two hours, thence, the equivalent of a single semester, university unit. That appeared to be the only detailed training/certification course that I could find in PostgreSQL. >From the web page at http://techdocs.postgresql.org/companies.php , that company appears to be a small company in Austria, and the company and certification appear to be recognised by PostgreSQL.org . Is that the only PostgreSQL certification that is recognised? Is it recognised internationally? Thank you in anticipation, for answers to all of these questions. -- Bret Busby Armadale West Australia .. "So once you do know what the question actually is, you'll know what the answer means." - Deep Thought, Chapter 28 of "The Hitchhiker's Guide to the Galaxy: A Trilogy In Four Parts", written by Douglas Adams, published by Pan Books, 1992 ---(end of bro
