RE: [HACKERS] Re: Toast,bytea, Text -blob all confusing
> > > >For bytea, follow this rule: to escape a null character, use this: > > > >'\\0'. To escape a backslash, use this: ''. Can anybody explain in technical terms why this is implemented so inconveniently ? Since bytea is probably not very common among users yet we could imho still change it to not do double escapes. Imho we need to decide where to do the escaping, eighter in the parser or in the input functions. I think actually the backend parser has no business changing constants, he is imho only allowed to parse it, so he knows where a constant begins, and where it ends. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Odd rule behavior?
Stephan Szabo wrote: > > When you drop and recreate the table, you'll need to drop and recreate the > rules that reference it as well. There's been little to no concensus as to > what the correct behavior should be in such cases: delete the rules when > a referenced table is removed, refuse to remove the table due to the > references, try to reconnect by name (and somehow handle the possibility > that the reference is no longer valid, like say the lack of a logid column > in your case)... > > Okay, thanks, dropping and recreating the rule worked. After thinking a bit about this, it would seem that the 'problem' is that I was *able* to drop a table that had rules referencing it. Would it be possible to either not allow this, or to issue some type of warning message? Otherwise, you go down the path of this (for me anyway) subtle problem. Also, who should I send documentation patches to about this? I couldn't find any mention of this issue in the "create rule" documentation (or am I looking in the wrong place?) or in "Chapter 17: The Postgres Rule System". Hmmm, further perusal shows that Jan Weick is the author of the Chapter 17 documentation, I guess I can send text to Jan. -- -**-*-*---*-*---*-*---*-*-*-*---*-*---*-*-*-*-*--- Jon Lapham Extracta Moléculas Naturais, Rio de Janeiro, Brasil email: [EMAIL PROTECTED] web: http://www.extracta.com.br/ ***-*--**---***--- ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Escaping strings for inclusion into SQL queries
Florian Weimer <[EMAIL PROTECTED]> writes: > We therefore suggest that a string escaping function is included in a > future version of PostgreSQL and libpq. A sample implementation is > provided below, along with documentation. We have now released a description of the problems which occur when a string escaping function is not used: http://cert.uni-stuttgart.de/advisories/apache_auth.php What further steps are required to make the suggested patch part of the official libpq library? Thanks, -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Odd rule behavior?
On Thu, 30 Aug 2001, Jon Lapham wrote: > Okay, thanks, dropping and recreating the rule worked. > > After thinking a bit about this, it would seem that the 'problem' is > that I was *able* to drop a table that had rules referencing it. Would > it be possible to either not allow this, or to issue some type of > warning message? Otherwise, you go down the path of this (for me > anyway) subtle problem. The problem is right now we don't keep track of that sort of information in any really usable way (apart from scanning all objects that might refer to an oid). There've been discussions on -hackers in the past about this and it should be on the todo list. > Also, who should I send documentation patches to about this? I couldn't > find any mention of this issue in the "create rule" documentation (or am > I looking in the wrong place?) or in "Chapter 17: The Postgres Rule > System". Hmmm, further perusal shows that Jan Weick is the author of > the Chapter 17 documentation, I guess I can send text to Jan. You might as well send patches to pgsql-patches and let everyone see them. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Re: Toast,bytea, Text -blob all confusing
Zeugswetter Andreas SB SD wrote:
>
> > > > >For bytea, follow this rule: to escape a null character, use
> this:
> > > > >'\\0'. To escape a backslash, use this: ''.
>
> Can anybody explain in technical terms why this is implemented
> so inconveniently ?
I think that this has to to with making textin and textout behave
symmetrically, and the requirement that textout must produce a
valid C-string for ASCII transfer format.
> Since bytea is probably not very common among users yet
> we could imho still change it to not do double escapes.
But how ?
> Imho we need to decide where to do the escaping,
> eighter in the parser or in the input functions.
It would be probably hard to make the parser to _not_ unescape some
types, as it does not yet know it
> I think actually the backend parser has no business changing
> constants, he is imho only allowed to parse it, so he knows
> where a constant begins, and where it ends.
If it is any consolation then you have to write the inset of
a single \ from shell command so:
> psql -c "insert into t values('')"
;)
--
Hannu
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[HACKERS] Re: INTERVAL type: SQL92 implementation
> If full SQL92 implementation of INTERVAL would be a welcome addition, > could it be added as a TODO item? I would like to work on it, since I > want to use some features that are not currently supported. ... > Valid SQL92 syntax that is not currently supported: ... > junk=# SELECT INTERVAL '1990' YEAR(4); ... So far, I've had shift/reduce troubles trying to have a trailing qualifier field like this. > junk=# SELECT INTERVAL -'1-1' YEAR TO MONTH; > ERROR: parser: parse error at or near "YEAR" A leading sign in front of a string-like field? Yuck. ... > Valid interval value format not currently supported: > year-month I'll look at accepting this for the current INTERVAL type too. > Since there are aspects of SQL92 interval representation that clash > with the current implementation, I would suggest that current > practice be followed unless SQL92 syntax is used. So a field that > is of type INTERVAL without qualification would continue to work > as it does now (except that I would like to implement range checking). I like this point. Really, SQL99 intervals are a bit unwieldy, though they do have "extra features" which someone might find useful. > The main difference would be in the output format... > ... parts of either year-month or > day hour:minute:second.fractional_second We could probably support this format (now that you have described it to us) at least for the "SQL" datestyle even for the existing INTERVAL type. > according to the field definition, without any words (i.e.: "1 03:46:40.00" > instead of "1 day 03:46:40.00", and "3-5" instead of "3 years 5 mons"). > All parts within the range will be shown, even if they are trailing zeros. This set of conventions might let the date/time parser do a complete job. I put in the "days" text label to reduce the ambiguity of a single, unlabeled integer. > What do you think? Have you gotten started yet? Finished yet?? ;) - Thomas ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] Multiple semicolon separated statements and autocommit
We're discussing an implementation of JDBC's Statement.executeBatch() on the pgsql-jdbc list. The idea is to send multiple semicolon separated statements in one call to the backend. The purpose of this feature is of course a performance improvement, since it executes multiple (non-select) statements with one round trip to the server. If autocommit is _enabled_ and S1;S2;S3 is send to the database, what exactly is the behaviour of the backend? For example, what happens if S1 succeeds, S2 fails and S3 would succeed? Does autocommit apply to the statement list send in one call as a whole? Or does it apply to individual statements? If autocommit applies to the list as a whole I assume the failure of S2 would cause the entire statement list to fail and be rolled back. If autocommit applies to individual statements in the list, I assume that S1 succeeds and is committed, S2 fails and is rolled back. But is S3 still executed? And what update count is returned to the client in that case? I will summarize on pgsql-jdbc. Regards, René Pijlman <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Odd rule behavior?
Jon Lapham writes: > I'm receiving the following error message: > ERROR: Relation "log" with OID 3694127 no longer exists As a general rule, this won't work in PostgreSQL: CREATE TABLE foo (...); CREATE RULE bar ... ON foo ...; # view, trigger, etc. DROP TABLE foo (...); CREATE TABLE foo (...); The rule (view, trigger) references the table by oid, not by name. (This is a good thing. Consider what happens when the newly created table has a totally different structure.) The correct fix would be to prevent the DROP TABLE or drop the rule with it, but it hasn't been done yet. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] test 2 ...
ignore, just making sure it works ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Escaping strings for inclusion into SQL queries
"Mitch Vincent" <[EMAIL PROTECTED]> writes: > Perhaps I'm not thinking correctly but isn't it the job of the application > that's using the libpq library to escape special characters? Yes, it is. > I guess I don't see a down side though, if it's implemented > correctly to check and see if characters are already escaped before > escaping them (else major breakage of existing application would > occur).. You can't do this automatically because the strings needing escaping are not marked in any way at the moment. -- Florian Weimer[EMAIL PROTECTED] University of Stuttgart http://cert.uni-stuttgart.de/ RUS-CERT +49-711-685-5973/fax +49-711-685-5898 ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Escaping strings for inclusion into SQL queries
> Florian Weimer <[EMAIL PROTECTED]> writes: > > > We therefore suggest that a string escaping function is included in a > > future version of PostgreSQL and libpq. A sample implementation is > > provided below, along with documentation. > > We have now released a description of the problems which occur when a > string escaping function is not used: > > http://cert.uni-stuttgart.de/advisories/apache_auth.php > > What further steps are required to make the suggested patch part of > the official libpq library? Will be applied soon. I was waiting for comments before adding it to the patch queue. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Escaping strings for inclusion into SQL queries
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. > It has come to our attention that many applications which use libpq > are vulnerable to code insertion attacks in strings and identifiers > passed to these applications. We have collected some evidence which > suggests that this is related to the fact that libpq does not provide > a function to escape strings and identifiers properly. (Both the > Oracle and MySQL client libraries include such a function, and the > vast majority of applications we examined are not vulnerable to code > insertion attacks because they use this function.) > > We therefore suggest that a string escaping function is included in a > future version of PostgreSQL and libpq. A sample implementation is > provided below, along with documentation. > > -- > Florian Weimer [EMAIL PROTECTED] > University of Stuttgart http://cert.uni-stuttgart.de/ > RUS-CERT +49-711-685-5973/fax +49-711-685-5898 [ Attachment, skipping... ] [ Attachment, skipping... ] [ Attachment, skipping... ] > > ---(end of broadcast)--- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Escaping strings for inclusion into SQL queries
Perhaps I'm not thinking correctly but isn't it the job of the application that's using the libpq library to escape special characters? I guess I don't see a down side though, if it's implemented correctly to check and see if characters are already escaped before escaping them (else major breakage of existing application would occur).. I didn't see the patch but I assume that someone took a look to make sure before applying it. -Mitch - Original Message - From: "Bruce Momjian" <[EMAIL PROTECTED]> To: "Florian Weimer" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, August 30, 2001 6:43 PM Subject: Re: [HACKERS] Escaping strings for inclusion into SQL queries > > Florian Weimer <[EMAIL PROTECTED]> writes: > > > > > We therefore suggest that a string escaping function is included in a > > > future version of PostgreSQL and libpq. A sample implementation is > > > provided below, along with documentation. > > > > We have now released a description of the problems which occur when a > > string escaping function is not used: > > > > http://cert.uni-stuttgart.de/advisories/apache_auth.php > > > > What further steps are required to make the suggested patch part of > > the official libpq library? > > Will be applied soon. I was waiting for comments before adding it to > the patch queue. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Escaping strings for inclusion into SQL queries
It is. Application is responsible to call PGescapeString (included in the patch in question) to escape command that may possibly have user-specified data... This function isn't called automatically. On Thu, 30 Aug 2001, Mitch Vincent wrote: > Perhaps I'm not thinking correctly but isn't it the job of the application > that's using the libpq library to escape special characters? I guess I don't > see a down side though, if it's implemented correctly to check and see if > characters are already escaped before escaping them (else major breakage of > existing application would occur).. I didn't see the patch but I assume that > someone took a look to make sure before applying it. > > > -Mitch > > - Original Message - > From: "Bruce Momjian" <[EMAIL PROTECTED]> > To: "Florian Weimer" <[EMAIL PROTECTED]> > Cc: <[EMAIL PROTECTED]> > Sent: Thursday, August 30, 2001 6:43 PM > Subject: Re: [HACKERS] Escaping strings for inclusion into SQL queries > > > > > Florian Weimer <[EMAIL PROTECTED]> writes: > > > > > > > We therefore suggest that a string escaping function is included in a > > > > future version of PostgreSQL and libpq. A sample implementation is > > > > provided below, along with documentation. > > > > > > We have now released a description of the problems which occur when a > > > string escaping function is not used: > > > > > > http://cert.uni-stuttgart.de/advisories/apache_auth.php > > > > > > What further steps are required to make the suggested patch part of > > > the official libpq library? > > > > Will be applied soon. I was waiting for comments before adding it to > > the patch queue. > > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > > ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Multiple semicolon separated statements and autocommit
Rene Pijlman writes: > If autocommit is _enabled_ and S1;S2;S3 is send to the database, > what exactly is the behaviour of the backend? For example, what > happens if S1 succeeds, S2 fails and S3 would succeed? All three commands are executed in a single transaction. So if S2 fails, S3 would not be executed. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Full Text Indexing
> "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > I'm playing around with the Full Text Indexing module, and I notice that > > it's case-sensitive. This seems to be pretty useless to me - > especially for > > my application. I wonder if there'd be any objections to me > modifying it to > > be case-insensitive. Or at least be configurable either way... > > Seems like a good idea, but make it configurable. I actually came up with another way of solving the problem. The FTI table has two columns: (string, id). The code needs to do two things; delete all strings for an id, and join to the main table based on the id. The docs for FTI recommend indexing (string, id). This is poor as the delete based on id does a sequential scan, although the join seems to be able to use the index (as long was you have a where string ~ '^something'). I indexed as follows: -- Functional index that lets us do case-insensitivity without hacking fti.so CREATE INDEX fti_string_idx ON fti_table(lower(string)); -- Index on id to allow fast deletes CREATE INDEX fti_id_idx ON fti_table(id); That seems to be a good solution to me - it allows case-insensitivity, fast deletion and fast joining. > > Also, the fti.pl that comes with the contrib seems to be using > an outdated > > version of CPAN's Pg.pm. > > It hasn't been touched in awhile, so feel free to update it. BTW, > someone ought to look at bringing src/interfaces/perl5 into sync with > the CPAN version, too. Or possibly we should stop distributing that > altogether, if the CPAN copy is being maintained? I'll have a look someday maybe, but I'll try to get this harder-than-expected ADD CONSTRAINT UNIQUE/PRIMARY patch off my hands first. Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] OpenFTS (Open Source Full Text Search engine) pre-announce
Doh! Guess that makes our work on contrib/fulltextindex a waste of time, huh? Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian > Sent: Friday, 10 August 2001 12:34 AM > To: Oleg Bartunov > Cc: Pgsql Hackers; [EMAIL PROTECTED] > Subject: Re: [HACKERS] OpenFTS (Open Source Full Text Search engine) > pre-announce > > > > GREAT! We need to get this information to people so they know it is > available. It is a major feature that few people know exists. > > > > Hi, > > > > I'm about to announce a release of OpenFTS search engine. > > > > Short blurb: > > > > OpenFTS (Open Source Full Text Search engine) > > is an advanced PostgreSQL-based search engine > > that provides online indexing of data and relevance > > ranking for database searching. Close integration > > with database allows use of metadata to restrict > > search results. > > > > This is actually what we use at fts.postgresql.org and > > several other sites. Perl version is available for download, > > TCL version (with AOL server support) will be available soon - > > actually it just needs to wrap a release. > > > > The OpenFTS project web site - http://openfts.sourceforge.net/ > > > > OpenFTS team: > > Oleg Bartunov - Project Manager > > Teodor Sigaev - Principal Developer > > Daniel Wickstrom- Developer of TCL Version > > Neophytos Demetriou - Documentation Writer > > > > Copyright: > > > > OpenFTS is Copyright 2000-2001 XWare and > > licensed under the GNU General Public License, > > version 2 (June 1991). This means you can use it > > and modify it in any way you want. If you choose to > > redistribute OpenFTS, you must do so under the > > terms of the GNU license. > > > > Regards, > > Oleg > > > > PS. > > > > Marc, > > > > I didn't subscribe to announce list. I think it's worth to > > put link somewhere to OpenFTS web site > > > > _ > > Oleg Bartunov, sci.researcher, hostmaster of AstroNet, > > Sternberg Astronomical Institute, Moscow University (Russia) > > Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ > > phone: +007(095)939-16-83, +007(095)939-23-83 > > > > > > > > > > > > ---(end of broadcast)--- > > TIP 2: you can get off all lists at once with the unregister command > > (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) > > > > -- > Bruce Momjian| http://candle.pha.pa.us > [EMAIL PROTECTED] | (610) 853-3000 > + If your life is a hard drive, | 830 Blythe Avenue > + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] > ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Multiple semicolon separated statements and autocommit
Are you sure? I thought all that autocommit meant was that a statement that is not enclosed within a begin/commit is automatically committed after it is run. So, in the this case all three queries will be independent, unless the first statements is a 'begin;' and the last is a 'commit;'... Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Peter Eisentraut > Sent: Friday, 31 August 2001 1:57 AM > To: Rene Pijlman > Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: [HACKERS] Multiple semicolon separated statements and > autocommit > > > Rene Pijlman writes: > > > If autocommit is _enabled_ and S1;S2;S3 is send to the database, > > what exactly is the behaviour of the backend? For example, what > > happens if S1 succeeds, S2 fails and S3 would succeed? > > All three commands are executed in a single transaction. So if S2 fails, > S3 would not be executed. > > -- > Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter > > > ---(end of broadcast)--- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl > ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Escaping strings for inclusion into SQL queries
Ok, I misudnerstood, I had long included my own escaping function in programs that used libpq, I thought the intent was to make escaping happen automatically.. Thanks! -Mitch - Original Message - From: "Alex Pilosov" <[EMAIL PROTECTED]> To: "Mitch Vincent" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Thursday, August 30, 2001 7:32 PM Subject: Re: [HACKERS] Escaping strings for inclusion into SQL queries > It is. Application is responsible to call PGescapeString (included in the > patch in question) to escape command that may possibly have user-specified > data... This function isn't called automatically. > > On Thu, 30 Aug 2001, Mitch Vincent wrote: > > > Perhaps I'm not thinking correctly but isn't it the job of the application > > that's using the libpq library to escape special characters? I guess I don't > > see a down side though, if it's implemented correctly to check and see if > > characters are already escaped before escaping them (else major breakage of > > existing application would occur).. I didn't see the patch but I assume that > > someone took a look to make sure before applying it. > > > > > > -Mitch > > > > - Original Message - > > From: "Bruce Momjian" <[EMAIL PROTECTED]> > > To: "Florian Weimer" <[EMAIL PROTECTED]> > > Cc: <[EMAIL PROTECTED]> > > Sent: Thursday, August 30, 2001 6:43 PM > > Subject: Re: [HACKERS] Escaping strings for inclusion into SQL queries > > > > > > > > Florian Weimer <[EMAIL PROTECTED]> writes: > > > > > > > > > We therefore suggest that a string escaping function is included in a > > > > > future version of PostgreSQL and libpq. A sample implementation is > > > > > provided below, along with documentation. > > > > > > > > We have now released a description of the problems which occur when a > > > > string escaping function is not used: > > > > > > > > http://cert.uni-stuttgart.de/advisories/apache_auth.php > > > > > > > > What further steps are required to make the suggested patch part of > > > > the official libpq library? > > > > > > Will be applied soon. I was waiting for comments before adding it to > > > the patch queue. > > > > > > > > ---(end of broadcast)--- > > TIP 6: Have you searched our list archives? > > > > http://www.postgresql.org/search.mpl > > > > > > > ---(end of broadcast)--- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html > ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Multiple semicolon separated statements and autocommit
"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > Are you sure? I thought all that autocommit meant was that a statement that > is not enclosed within a begin/commit is automatically committed after it is > run. So, in the this case all three queries will be independent, unless the > first statements is a 'begin;' and the last is a 'commit;'... What does the JDBC spec say about autocommit and ExecuteBatch()? -Doug -- Free Dmitry Sklyarov! http://www.freesklyarov.org/ We will return to our regularly scheduled signature shortly. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Full Text Indexing
Doh - sorry about these hideously late posts. I think my mail queue has been clogged up for a while! Chris > -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On Behalf Of Christopher > Kings-Lynne > Sent: Monday, 23 July 2001 10:34 AM > To: Tom Lane > Cc: Hackers > Subject: Re: [HACKERS] Full Text Indexing > > > > "Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes: > > > I'm playing around with the Full Text Indexing module, and I > notice that > > > it's case-sensitive. This seems to be pretty useless to me - > > especially for > > > my application. I wonder if there'd be any objections to me > > modifying it to > > > be case-insensitive. Or at least be configurable either way... > > > > Seems like a good idea, but make it configurable. > > I actually came up with another way of solving the problem. > > The FTI table has two columns: (string, id). The code needs to do two > things; delete all strings for an id, and join to the main table based on > the id. > > The docs for FTI recommend indexing (string, id). This is poor as the > delete based on id does a sequential scan, although the join seems to be > able to use the index (as long was you have a where string ~ > '^something'). > > I indexed as follows: > > -- Functional index that lets us do case-insensitivity without hacking > fti.so > CREATE INDEX fti_string_idx ON fti_table(lower(string)); > > -- Index on id to allow fast deletes > CREATE INDEX fti_id_idx ON fti_table(id); > > That seems to be a good solution to me - it allows > case-insensitivity, fast > deletion and fast joining. > > > > Also, the fti.pl that comes with the contrib seems to be using > > an outdated > > > version of CPAN's Pg.pm. > > > > It hasn't been touched in awhile, so feel free to update it. BTW, > > someone ought to look at bringing src/interfaces/perl5 into sync with > > the CPAN version, too. Or possibly we should stop distributing that > > altogether, if the CPAN copy is being maintained? > > I'll have a look someday maybe, but I'll try to get this > harder-than-expected ADD CONSTRAINT UNIQUE/PRIMARY patch off my > hands first. > > Chris > > > ---(end of broadcast)--- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to [EMAIL PROTECTED] so that your > message can get through to the mailing list cleanly > ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Multiple semicolon separated statements and autocommit
On Thu, 30 Aug 2001 19:56:53 +0200 (CEST), you wrote: >Rene Pijlman writes: >> If autocommit is _enabled_ and S1;S2;S3 is send to the database, >> what exactly is the behaviour of the backend? For example, what >> happens if S1 succeeds, S2 fails and S3 would succeed? > >All three commands are executed in a single transaction. So if S2 fails, >S3 would not be executed. And both S1 and S2 will be rolled back, as I understand it. Thank you. Regards, René Pijlman <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [HACKERS] Majordomo being upgraded ...
> Its been much much too long since I've upgraded Majordomo2 on the server, > so this is the last email I'm sending out prior to upgrading her today ... > if anyone notices the lists go suddenly quiet, or the way it works > changing, please let me know ... > > My main worry is that in the past 6+ months, some of the defaults might > have been reversed, so that they default to off instead of on, or vice > versa ... just a heads up so that ppl are watching for it ... I have not gotten back mails from pgsql-committers. I'm sure I have committed to the CVS repository yesterday. cvs log queries.sgml [snip] revision 1.8 date: 2001/08/30 08:16:42; author: ishii; state: Exp; lines: +2 -2 Fix typo. -- Tatsuo Ishii ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Escaping strings for inclusion into SQL queries
Bruce Momjian wrote: > > Your patch has been added to the PostgreSQL unapplied patches list at: > > http://candle.pha.pa.us/cgi-bin/pgpatches > > I will try to apply it within the next 48 hours. > > > It has come to our attention that many applications which use libpq > > are vulnerable to code insertion attacks in strings and identifiers > > passed to these applications. We have collected some evidence which > > suggests that this is related to the fact that libpq does not provide > > a function to escape strings and identifiers properly. (Both the > > Oracle and MySQL client libraries include such a function, and the > > vast majority of applications we examined are not vulnerable to code > > insertion attacks because they use this function.) I think the real difference is what I complained in another mail to this list - in postgresql you can't do PREPARE / EXECUTE which could _automatically_ detect where string escaping is needed or just eliminate the need for escaping. In postgreSQL you have to construct all queries yourself by inserting your parameters inside your query strings in right places and escaping them when needed. That is unless you use an interface like ODBC/JDBS that fakes the PREPARE/EXECUTE on the client side and thus does the auto-escaping for you . I think that this should be added to TODO * make portable BINARY representation for frontend-backend protocol by using typsend/typreceive functions for binary and typinput typoutput for ASCII (as currently typinput==typreceive and typoutput==typsend is suspect the usage to be inconsistent). * make SQL changes to allow PREPARE/EXECUTE in main session, not only in SPI * make changes to client libraries to support marshalling arguments to EXECUTE using BINARY wire protocol or correctly escaped ASCII. The binary protocol would be very helpful for BYTEA and other big binary types. > > We therefore suggest that a string escaping function is included in a > > future version of PostgreSQL and libpq. A sample implementation is > > provided below, along with documentation. While you are at it you could also supply a standard query delimiter function as this is also a thing that seems to vary from db to db. -- Hannu ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Re: [SQL] getting the oid for a new tuple in a BEFORE trigger
On Wed, Aug 29, 2001 at 11:15:08AM +0200, Markus Wagner wrote: > Hi, > > we need to control database changes within BEFORE triggers. > There is no problem with triggers called by update, but there is > a problem with triggers called by insert. > > We strongly need to know the oid of a newly inserted tuple. In this case, we > use tg_newtuple of the TriggerData structure passed to thetrigger function, > and its t_data -> t_oid will have the value '0'. > > Using BEFORE and AFTER triggers would make our lives much harder. > > Is there any way (even hack) to get the oid the newly inserted tuple will > receive? > > Thank you very much, > > Markus > > > ---(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 > > end of the original message Read section 24.2.5.4 'Obtaining other results status' of the Programmer's Guide. This is for the PL/pgSQL language, though. Francesco Casadei ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
[HACKERS] Majordomo being upgraded ...
Its been much much too long since I've upgraded Majordomo2 on the server, so this is the last email I'm sending out prior to upgrading her today ... if anyone notices the lists go suddenly quiet, or the way it works changing, please let me know ... My main worry is that in the past 6+ months, some of the defaults might have been reversed, so that they default to off instead of on, or vice versa ... just a heads up so that ppl are watching for it ... ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
