Re: [GENERAL] (another ;-)) PostgreSQL-derived project ...
~
Well, at least I thought you would tell me where the postgresql-base
is to be found. The last version I found is:
~
http://freebsd.csie.nctu.edu.tw/pub/distfiles/postgresql/postgresql-base-8.3beta2.tar.bz2
~
and I wondered what that is and why there are no postgresql-base
after 8.3beta2
~
I cannot imagine you would benefit that much by removing these capabilities
compared to simply ignoring them.
Plus, by having it in the DB I avoid considerable considerable overhead
~
Can you or do you know of anyone who has made those kinds of
imaginations falsifiable?
~
... and can now use those features within my SQL statements/queries.
~
For what exactly? Isn't a comparison on 4 numeric bytes (1 (or 1/2)
word in modern hardware) more efficient than comparing sequences of
string characters?
~
simply guessing that in simply being feature rich PostgreSQL has sub-optimal
performance
~
I never said that
~
... you might want to look at SQLite. It provides a number of compile-time
options where you can exclude various features you don't want from the binary
~
I couldn't find the compile options you mentioned: sqlite.org/
{faq.html, custombuild.html, docs.html}
~
... you're looking for a non-sql compliant SQL database where a lot of the
data integrity is actually coded in the application :-)
~
First past of your statement I acknowledged, but how is it exactly
that lot of the data integrity is actually coded in the application
~
That approach strips down on application complexity. My apps don't have to do
any post-processing
of the data - I query the records I need and the app merely displays them.
~
Again have you actually tested those assumptions?
~
My point being: postgresql does what it does very reliably
~
I never said otherwise
~
lbrtchx
--
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] looking for a faster way to do that
Eduardo Morras wrote:
Hi,
if I understand this right, it does not mean check if the string
appears at position 0
which could translate into an index query, but rather check if the
string appears anywhere
and then check if that is position 0, so the entire table is checked.
The second one yes, as it checks all patterns you want only one time
per row they only needs one table scan. The first one eliminates the
substring 'ABC' from the string, if the lengths of both strings are
equal, the substring 'ABC' wasn't in it. If they are different, the
trimmed string will be shorter.
explain analyze select items.num, wantcode from items, n where
strpos(code, wantcode) = 0;
Nested Loop (cost=167.14..196066.54 rows=39178 width=36) (actual
time=0.074..36639.312 rows=7832539 loops=1)
Join Filter: (strpos((inner.code)::text, outer.wantcode) = 0)
- Seq Scan on n (cost=0.00..14.15 rows=815 width=32) (actual
time=0.005..2.212 rows=815 loops=1)
- Materialize (cost=167.14..263.28 rows=9614 width=42)
(actual time=0.007..13.970 rows=9614 loops=815)
- Seq Scan on items (cost=0.00..167.14 rows=9614
width=42) (actual time=0.044..14.855 rows=9614 loops=1)
Total runtime: 46229.836 ms
The query ran much faster than the pattern query, however. This
seems to be the performance
of just searching for a plain string vs. initializing the regex
engine every time (for 815
queries in a test set)
It will do only one table scan while your original code will do one
for each substring you want to test. You can add more and more
substrings without too much cost. If you want to use the regex engine
instead the postgresql string funtions check the regexp_matches(), it
should be faster if you have 3000 substrings.
select * from items where regexp_matches(items.code,'(ABC) (DE1)
(any_substring)'){};
Hi Eduardo,
it is clear that scanning the table once with a list of matches will outperform
rescanning the table for every string wanted. Now, my problem is that the
patterns are
dynamic as well. So if I could translate a table with one column and a few
thousand rows
into something like
regexp_matches(code,'string1|string2|.string2781')
would ideally be a performant query. Unfortunately I have no idea how I could
achieve this
transformation inside the database. Doing it externally fails, because any
single query cannot
be more than so few characters.
Regards
Wolfgang Hamann
--
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] In which case PG_VERSION file updates ?
Thanks Adrian Klaver, Provided link gives about the information of what PG_VERSION file, which am aware of:) In my observation, all the object related OID's, _fsm, _vm files under $PGDATA/base/database-oid/ directory will change as per the changes made to the database, whereas PG_VERSION file never changes. Because its the file tells on which version of Binary the Data directory is built upon. So, my question is, Is there any case, where PG_VERSION file updates with any of the utility process or PG_VERSION file never been touched by PG-Instance ? --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Sun, Sep 25, 2011 at 2:00 AM, Adrian Klaver adrian.kla...@gmail.comwrote: On Saturday, September 24, 2011 12:34:02 pm Raghavendra wrote: Respected All, In which case $PGDATA/base/database-oid/PG_VERSION file updates ? I have observed, PG_VERSION file is created at DB creation time and will never get updated. I mean file PG_VERSION TIMESTAMP. See here: http://www.postgresql.org/docs/9.1/interactive/storage-file-layout.html Thanks in advance. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ -- Adrian Klaver adrian.kla...@gmail.com
Re: [GENERAL] Speed of lo_unlink vs. DELETE on BYTEA
At 07:43 25/09/2011, Reuven M. Lerner wrote: Hi, everyone. Daniel Verite mailto:dan...@manitou-mail.orgdan...@manitou-mail.org wrote: It would thus appear that there's a slight edge for dumping bytea, but nothing super-amazing. Deleting, however, is still much faster with bytea than large objects. The problem you have is with compression/decompression on large objects. If you see at it's sizes, you get 680KB for large objects and 573MB for bytea. Postgresql needs to decompress them before the dump. Even worse, if your dump is compressed, postgres decompress each large object , dump it and recompress. For this test, switch off compression on large objects/toast. For long term, perhaps a request to postgresql hackers to directly dump the already compressed large objects. The toast maybe more difficult because there are not only big size columns, but any column whose* size is bigger than a threshold (don't remember now, 1-2KB or similar) * Is it whose the correct word? I hope i have expressed correctly. EFME -- 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] looking for a faster way to do that
On 25 Sep 2011, at 8:04, haman...@t-online.de wrote: Hi Eduardo, it is clear that scanning the table once with a list of matches will outperform rescanning the table for every string wanted. Now, my problem is that the patterns are dynamic as well. So if I could translate a table with one column and a few thousand rows into something like regexp_matches(code,'string1|string2|.string2781') would ideally be a performant query. Unfortunately I have no idea how I could achieve this transformation inside the database. Doing it externally fails, because any single query cannot be more than so few characters. To me it sounds a little bit like you're comparing every item in a warehouse to a set of descriptions to see what type of item it is, which is something you would be much better off storing as a property of the item. If an item is a fruit, store that it's a fruit! But I'm guessing at what you're trying to accomplish, so here's a few other options... I guess you could create 2781 expression indexes to do what you want (is there a limit that prevents this?). Query planning would probably become kind of slow and the indices will take up a considerable fraction of the total table storage required - that's a pretty outlandish approach. CREATE INDEX tbl_str_regex1 ON tbl (regexp_matches(str, 'string1')); CREATE INDEX tbl_str_regex1 ON tbl (regexp_matches(str, 'string2')); ... CREATE INDEX tbl_str_regex1 ON tbl (regexp_matches(str, 'string2781')); Or are you really going to query every record against all 2781 regexes? I can't figure out a realistic scenario why you (or anyone) would want that. In that case those indices aren't going to help you much, as the planner would have to hold every record in tbl to each index - it won't do that. You could also create a giant lookup table (a materialized view, if you like) where the results of every match of str in tbl against the wantcode in the regex table is stored. That's some huge overhead, but it will probably outperform most other options. With the numbers you gave that table will hold about 2-3 billion records with two foreign key values and a truth value each. Alban Hertroys -- The scale of a problem often equals the size of an ego. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] New feature: accumulative functions.
Hi. I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const and so on. -- pasman -- 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] (another ;-)) PostgreSQL-derived project ...
On Sep 25, 2011, at 2:11, Albretch Mueller lbrt...@gmail.com wrote: ~ Well, at least I thought you would tell me where the postgresql-base is to be found. The last version I found is: ~ http://freebsd.csie.nctu.edu.tw/pub/distfiles/postgresql/postgresql-base-8.3beta2.tar.bz2 ~ and I wondered what that is and why there are no postgresql-base after 8.3beta2 ~ I cannot imagine you would benefit that much by removing these capabilities compared to simply ignoring them. Plus, by having it in the DB I avoid considerable considerable overhead ~ Can you or do you know of anyone who has made those kinds of imaginations falsifiable? No; not worth my effort. ~ ... and can now use those features within my SQL statements/queries. ~ For what exactly? Isn't a comparison on 4 numeric bytes (1 (or 1/2) word in modern hardware) more efficient than comparing sequences of string characters? The ARRAY_AGG() function in particular has been very useful in queries I write. ~ simply guessing that in simply being feature rich PostgreSQL has sub-optimal performance ~ I never said that Your whole post implies this otherwise there is no meaningful reason to look for something excluding features (assuming proper and correct implementation). That approach strips down on application complexity. My apps don't have to do any post-processing of the data - I query the records I need and the app merely displays them. ~ Again have you actually tested those assumptions? Is this the best response you can come up with? The crux of the counter-argument is that by having PostgreSQL handle 'advanced' features application code avoids the need to do so. The principle of code-reuse and the fact the features are executed by the same program holding the data make this a de-facto truth (and yes, one that we are probably taking for granted). But, if you really feel a bare-bones implementation of PostgreSQL is worthwhile you are the one that needs to test (and state explicitly) your own underlying assumptions to see whether they hold and thus make such an endeavor worthwhile. David 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] New feature: accumulative functions.
On Sep 25, 2011, at 9:19, pasman pasmański pasma...@gmail.com wrote: Hi. I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const and so on. We can term it a Schrodinger function :) I don't understand how it can have mutable state (accumulator) and still be called immutable. Can explain further and give an example (or better yet, real life) use-case? David 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] (another ;-)) PostgreSQL-derived project ...
On 25 Sep 2011, at 8:11, Albretch Mueller wrote: ... and can now use those features within my SQL statements/queries. ~ For what exactly? Isn't a comparison on 4 numeric bytes (1 (or 1/2) word in modern hardware) more efficient than comparing sequences of string characters? Data types aren't stored in the database as character strings (unless you define your columns as text, of course). When data in the database gets compared to data in a query (for example, when you use a WHERE clause that compares a date column to a given date), the data in the query gets transformed to the appropriate type (text to date, in this case) - just once. That's efficient enough that the difference in performance between a numerical value and the string representation doesn't matter. I don't know what you're trying to say in the above, but you seem to base your hypothesis on wrong assumptions. Alban Hertroys -- The scale of a problem often equals the size of an ego. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Alter column...using failure under 9.0.4
Dear list, Ive a strange issue here. Ive a table that Im sure the column type is boolean. I can see the datatype is boolean on PgAdmin. Nevertheless, when I issue this command alter table usuario alter column ativo type smallint using (case when ativo then 1 else 0 end); I get the following error: ERROR: argument of IS FALSE must be type boolean, not type smallint ** Error ** ERROR: argument of IS FALSE must be type boolean, not type smallint SQL state: 42804 I already run Vacuum just to be sure, but still not working. I have a script with hundred similar lines (for other tables) without any error, just this table. Any hints? Edson Carlos Ericksson Richter SimKorp Infomática Ltda Fone: (51) 3366-7964 Celular: (51) 8585-0796 Embedded Image http://www.simkorp.com.br/ www.simkorp.com.br image001.jpg smime.p7s Description: S/MIME cryptographic signature
Re: [GENERAL] (another ;-)) PostgreSQL-derived project ...
On Sep 25, 2011, at 2:11 AM, Albretch Mueller wrote: For what exactly? Isn't a comparison on 4 numeric bytes (1 (or 1/2) word in modern hardware) more efficient than comparing sequences of string characters? What on earth makes you think the db engine compares numbers as strings??? -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- 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] Alter column...using failure under 9.0.4
Hello, you could check for indices or something like that. Björn Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter: Dear list, I’ve a strange issue here. I’ve a table that I’m sure the column type is boolean. I can see the datatype is boolean on PgAdmin. Nevertheless, when I issue this command alter table usuario alter column ativo type smallint using (case when ativo then 1 else 0 end); I get the following error: ERROR: argument of IS FALSE must be type boolean, not type smallint ** Error ** ERROR: argument of IS FALSE must be type boolean, not type smallint SQL state: 42804 I already run Vacuum just to be sure, but still not working. I have a script with hundred similar lines (for other tables) without any error, just this table. Any hints? Edson Carlos Ericksson Richter SimKorp Infomática Ltda Fone: (51) 3366-7964 Celular: (51) 8585-0796 image001.jpg www.simkorp.com.br -- 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] Alter column...using failure under 9.0.4
=?iso-8859-1?Q?Bj=F6rn_H=E4user?= bjoernhaeu...@gmail.com writes: Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter: alter table usuario alter column ativo type smallint using (case when ativo then 1 else 0 end); ERROR: argument of IS FALSE must be type boolean, not type smallint you could check for indices or something like that. Yeah, looks like expression index or CHECK constraint or something similar that includes ativo IS FALSE. Note to hackers: I wonder whether we could make this a bit more user-friendly by providing a CONTEXT line that shows which table property we're trying to convert. 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] New feature: accumulative functions.
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. Maybe you'd better define what you mean by accumulative ... This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const ... because it's sure not clear how you would get that to work. 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
RES: [GENERAL] Alter column...using failure under 9.0.4
That's it: a check constraint I was not aware of... Thanks! Edson -Mensagem original- De: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] Em nome de Tom Lane Enviada em: domingo, 25 de setembro de 2011 13:04 Para: pgsql-general@postgresql.org; pgsql-hack...@postgresql.org Assunto: Re: [GENERAL] Alter column...using failure under 9.0.4 =?iso-8859-1?Q?Bj=F6rn_H=E4user?= bjoernhaeu...@gmail.com writes: Am 25.09.2011 um 17:17 schrieb Edson Carlos Ericksson Richter: alter table usuario alter column ativo type smallint using (case when ativo then 1 else 0 end); ERROR: argument of IS FALSE must be type boolean, not type smallint you could check for indices or something like that. Yeah, looks like expression index or CHECK constraint or something similar that includes ativo IS FALSE. Note to hackers: I wonder whether we could make this a bit more user-friendly by providing a CONTEXT line that shows which table property we're trying to convert. 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 -- 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] (another ;-)) PostgreSQL-derived project ...
On Sun, Sep 25, 2011 at 06:11:36AM +, Albretch Mueller wrote: ~ Well, at least I thought you would tell me where the postgresql-base is to be found. The last version I found is: ~ http://freebsd.csie.nctu.edu.tw/pub/distfiles/postgresql/postgresql-base-8.3beta2.tar.bz2 Notwithstanding the rest of your post, I'm surpised you missed the website: http://www.postgresql.org/download/ There's a source code link, as well as several others. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [GENERAL] (another ;-)) PostgreSQL-derived project ...
Albretch Mueller lbrt...@gmail.com writes: Well, at least I thought you would tell me where the postgresql-base is to be found. The last version I found is: http://freebsd.csie.nctu.edu.tw/pub/distfiles/postgresql/postgresql-base-8.3beta2.tar.bz2 and I wondered what that is and why there are no postgresql-base after 8.3beta2 We stopped bothering because the split tarballs weren't really good for anything separately. They were never independently buildable pieces, and were only meant to ease downloading the distribution over unreliable internet connections. That concern was obsolete some years ago. The only part of the PG distribution that's ever been meant to be separately buildable is libpq and some of the client-side tools. If you want to start stripping down the server, you're on your own. Now, having said that, there has been some interest in pushing lesser-used chunks like the geometric datatypes out into extensions. I don't see how that's going to result in any significant performance gain, though. 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] pg_dump compress
Adrian Klaver adrian.kla...@gmail.com writes: On Saturday, September 24, 2011 7:16:11 am Roger Niederland wrote: [ pg_dump 9.1 no longer honors -Z when emitting plain text ] Not sure why the compression was removed, there is no explanation in either the commit or the release notes. If you are interested, the commit that removed it is: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;f=src/bin/pg_dump/pg_dump.c;h=7f508f1c6b515df66d27f860b2faa7b5761fa55d It appears to me that this was just a careless misunderstanding of what pg_dump is supposed to do with this switch. Heikki, do you recall any intentional change here, and if so why wasn't it documented? 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] New feature: accumulative functions.
pasman pasmański pasma...@gmail.com Sunday 25 of September 2011 15:19:28 Hi. I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. This flag allows to use an index on x for clauses containing f(x): where f(x) = const I think for this index designe will require that f(x) will be stored, additional behaviour of fucntion are not required, is quite enaugh that it will be function. where f(x) const and so on. By this assume that accumulative function is (weakly) increasing or decreasing f such that x y = f(x) = f(y) ? I only may deduce it for idea that search will be faster on index. Regards, Radosław Smogura http://softperience.eu/ -- 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] New feature: accumulative functions.
My english is not perfect, by accumulative i think about monotonically increasing function. It works that for clause WHERE f(x)=const: 1. Read root page of index_on_x and get x1 ... Xn 2. Calculate f(x1) ... f(xn) for this page 3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can test smaller range (xlower, xgreater). 4. Otherwise no rows satisfy condition. Step 3 we repeat for current index's page and subpages until xlower = searched x = xgreater 2011/9/25, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. Maybe you'd better define what you mean by accumulative ... This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const ... because it's sure not clear how you would get that to work. regards, tom lane -- pasman -- 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] Mac OS X shared_buffers not same as postgresql.conf file
On Sun, 2011-09-25 at 12:13 -0500, Neil Tiffin wrote: On Sep 24, 2011, at 4:21 PM, Guillaume Lelarge wrote: On Sat, 2011-09-24 at 14:43 -0500, Neil Tiffin wrote: On Sep 24, 2011, at 1:31 PM, Joe Conway wrote: On 09/23/2011 02:33 PM, Neil Tiffin wrote: I have shared_buffers in the config file set for 32 MB and pgAdmin reports a value of 32 MB, but pgAdmin also says the current value is 4096. Can anyone point me to any docs about why the current value may be different than the config value? Temp_buffers are the same way, config file 8MB, but current value in pgAdmin is 1024? Internally shared_buffers is tracked as number of 8K pages. postgres=# show shared_buffers; shared_buffers 32MB (1 row) postgres=# select setting from pg_settings where name='shared_buffers'; setting - 4096 (1 row) postgres=# select 4096 * 8 / 1024 as MB; mb 32 (1 row) HTH, Excellent, just what I was looking for. I know there had to be a simple explanation, even though it seems a little retarded that pgAdmin does not point this out. And how do you get that value from pgAdmin? the config editor gives me the value with its unit, so I guess it's not from the config editor. I got the values from selecting in pgAdmin: Tools - Server Configuration - postgresql.conf, then I get a table with setting name, value, current value, and comment columns. The 'value' column shows the unit, but the 'current value' column does not, at least on Mac OS X. Yes, I have the same behaviour. The Value comes from the file (hence with the unit), and the Current Value comes from PostgreSQL (simple query: select setting from pg_settings where name='shared_buffers'). -- Guillaume http://blog.guillaume.lelarge.info http://www.dalibo.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] (another ;-)) PostgreSQL-derived project ...
... you're looking for a non-sql compliant SQL database where a lot of the data integrity is actually coded in the application :-) ~ First past of your statement I acknowledged, but how is it exactly that lot of the data integrity is actually coded in the application ~ Take dates for example: you'd have to code very carefully to catch all the different ways dates are represented on this planet. Your application has to handle this since all the database knows at this point is an absolute time (i.e. seconds since epoch or something the like) and your app has to convert every occurrence of a date to this format or the database won't find anything or even worse store something wrong. Same goes for numbers: if everything is stored in a byte sequence, how does the database know you try to compare the number 1 with the character 1? Again, your app would have to handle that. To me, that's moving data integrity into the application. That approach strips down on application complexity. My apps don't have to do any post-processing of the data - I query the records I need and the app merely displays them. ~ Again have you actually tested those assumptions? Yes, I have, as have many others. Simple example: program a website like, say Facebook. So you have thousands of users from all over the world. Your website code handles all the data conversions. Now Apple comes along and sells an iPhone which silly enough a lot of people like and try to use to access your website. You now face the problem that you need a second website doing the same thing as the first website except solely made for touch-screen devices. You will be forced to rewrite a lot of your code because all the data conversion is in the code. Even worse, if you'd have to make an iphone or android app in lieu of the second website, you'd have to recode everything you did in a different language - i.e. objective C. If you leave these things to the database, you simply write a second client for a different platform and you don't have to fuzz around to get the conversions correct because the application receives the data already converted. Sure this all depends on what application you need this specialized database engine for. If it's an application for a very defined environment you can dictate how data is to be input and train users. If it's an application for the big wild world you will have problems with users doing stupid things beyond your control like writing P.O. Box 1 into the zipcode field where you expected a 10 digit number. I rather have the database catch those cases and reject storing the bad input. That saves me a lot of validation code in my 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] New feature: accumulative functions.
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: My english is not perfect, by accumulative i think about monotonically increasing function. Oh, I see how that would work. I can't get real excited about it though. The use-case seems a bit narrow, and the amount of complexity added to the btree search mechanism (thereby slowing down all btree searches) would be significant. Furthermore, unless f() is pretty cheap to evaluate, you'd end up preferring an index on f(x) anyway, because that can be searched without any new evaluations of f(). 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] looking for a faster way to do that
At 08:04 25/09/2011, haman...@t-online.de wrote:
select * from items where regexp_matches(items.code,'(ABC) (DE1)
(any_substring)'){};
Hi Eduardo,
it is clear that scanning the table once with a list of matches will
outperform
rescanning the table for every string wanted. Now, my problem is
that the patterns are
dynamic as well. So if I could translate a table with one
column and a few thousand rows
into something like
regexp_matches(code,'string1|string2|.string2781')
would ideally be a performant query. Unfortunately I have no idea
how I could achieve this
transformation inside the database. Doing it externally fails,
because any single query cannot
be more than so few characters.
You can create a plsql function and pass a setof text that do it.
Sorry but instead saying you What Must You Type, WMYT(c), i prefer
the How Should You Do way, HSYD(c). Note that you can get the same
results using other approachs (f.ex. using FTS described in chapter 12)
Check this topics:
Function
Creation http://www.postgresql.org/docs/9.0/static/sql-createfunction.html
Tutorial about Function
Creation http://www.adderpit.com/practical-postgresql/x10374.htm
HTH
--
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] Mac OS X shared_buffers not same as postgresql.conf file
On Sep 24, 2011, at 4:21 PM, Guillaume Lelarge wrote: On Sat, 2011-09-24 at 14:43 -0500, Neil Tiffin wrote: On Sep 24, 2011, at 1:31 PM, Joe Conway wrote: On 09/23/2011 02:33 PM, Neil Tiffin wrote: I have shared_buffers in the config file set for 32 MB and pgAdmin reports a value of 32 MB, but pgAdmin also says the current value is 4096. Can anyone point me to any docs about why the current value may be different than the config value? Temp_buffers are the same way, config file 8MB, but current value in pgAdmin is 1024? Internally shared_buffers is tracked as number of 8K pages. postgres=# show shared_buffers; shared_buffers 32MB (1 row) postgres=# select setting from pg_settings where name='shared_buffers'; setting - 4096 (1 row) postgres=# select 4096 * 8 / 1024 as MB; mb 32 (1 row) HTH, Excellent, just what I was looking for. I know there had to be a simple explanation, even though it seems a little retarded that pgAdmin does not point this out. And how do you get that value from pgAdmin? the config editor gives me the value with its unit, so I guess it's not from the config editor. I got the values from selecting in pgAdmin: Tools - Server Configuration - postgresql.conf, then I get a table with setting name, value, current value, and comment columns. The 'value' column shows the unit, but the 'current value' column does not, at least on Mac OS X. Neil -- 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] New feature: accumulative functions.
I think, it should be new node in executor. Planner select classic index scan or new functional index scan. 2011/9/25, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: My english is not perfect, by accumulative i think about monotonically increasing function. Oh, I see how that would work. I can't get real excited about it though. The use-case seems a bit narrow, and the amount of complexity added to the btree search mechanism (thereby slowing down all btree searches) would be significant. Furthermore, unless f() is pretty cheap to evaluate, you'd end up preferring an index on f(x) anyway, because that can be searched without any new evaluations of f(). regards, tom lane -- pasman -- 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] (another ;-)) PostgreSQL-derived project ...
On Sat, Sep 24, 2011 at 11:11 PM, Albretch Mueller lbrt...@gmail.com wrote:
I cannot imagine you would benefit that much by removing these capabilities
compared to simply ignoring them.
Plus, by having it in the DB I avoid considerable considerable overhead
~
Can you or do you know of anyone who has made those kinds of
imaginations falsifiable?
~
My own experience here is that while it is generally possible to
create additional overhead by mis-use of advanced features, *in
general* you save more overhead and get clearer code by pushing what
you can into the database within reason.
... and can now use those features within my SQL statements/queries.
~
For what exactly? Isn't a comparison on 4 numeric bytes (1 (or 1/2)
word in modern hardware) more efficient than comparing sequences of
string characters?
~
simply guessing that in simply being feature rich PostgreSQL has sub-optimal
performance
~
I never said that
~
... you might want to look at SQLite. It provides a number of compile-time
options where you can exclude various features you don't want from the binary
~
I couldn't find the compile options you mentioned: sqlite.org/
{faq.html, custombuild.html, docs.html}
~
... you're looking for a non-sql compliant SQL database where a lot of the
data integrity is actually coded in the application :-)
~
First past of your statement I acknowledged, but how is it exactly
that lot of the data integrity is actually coded in the application
I can give you a good example. Some years ago, I was working on an
accounting application someone else wrote which stored all monetary
values as double-precision floats and then handled arbitrary precision
math in the front-end of the application. This meant:
1) To detect if an invoice was closed, it would retrieve all gl lines
associated with the invoice and an AR/AP account and see if these
totalled to 0 in the middleware. This performed ok for a small
database, but for a large one, it didn't work so well... Had the
application used NUMERIC types, this could have been more easily done
with HAVING clause, and this could have been done far more efficiently
on the db server.
2) It made the application relatively sensitive to rounding errors---
sum() with group by would return different numbers with different
groupings in sufficiently large databases.
So here you get a case where the application was made less robust and
performed quite a bit worse by not using arbitrary math capabilities
of PostgreSQL.
~
That approach strips down on application complexity. My apps don't have to
do any post-processing
of the data - I query the records I need and the app merely displays them.
~
Again have you actually tested those assumptions?
In general my experience is that it is far easier to tune performance
of an app as is described here (where all presentation is done in db)
than it is an app where a lot of it is done in middle-ware or
front-end.
For example, consider the following: I need to determine all of the
years that have dates in a database table with, say, 50M records. If
I have a database query which does this all at once, when it performs
badly, I can tune it, and there are fewer tradeoffs I have to make.
~
My point being: postgresql does what it does very reliably
~
I never said otherwise
I'd add it performs remarkably well IMHO as well as reliably.
Best Wishes,
Chris Travers
--
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] New feature: accumulative functions.
2011/9/25 pasman pasmański pasma...@gmail.com: This feature give profits for increasing muliti-arg functions. Example: WHERE f(x,param) = const it may be impossible to create functional indexes for all params. Sorry, I asked on real use case. Do you know some one? Pavel 2011/9/25, Pavel Stehule pavel.steh...@gmail.com: Hello what is a real use case? Regards Pavel 2011/9/25 pasman pasmański pasma...@gmail.com: My english is not perfect, by accumulative i think about monotonically increasing function. It works that for clause WHERE f(x)=const: 1. Read root page of index_on_x and get x1 ... Xn 2. Calculate f(x1) ... f(xn) for this page 3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can test smaller range (xlower, xgreater). 4. Otherwise no rows satisfy condition. Step 3 we repeat for current index's page and subpages until xlower = searched x = xgreater 2011/9/25, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. Maybe you'd better define what you mean by accumulative ... This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const ... because it's sure not clear how you would get that to work. regards, tom lane -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- 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] New feature: accumulative functions.
... When n changes of course. Sorry for top posting, phone not allows to move cite. 2011/9/25, pasman pasmański pasma...@gmail.com: I found second use case. Look at expression: where left(str,n)='value' function left(str,n) increase monotonically for str and n. With this feature it can use index on str. Classic index needs recreating. 2011/9/25, Pavel Stehule pavel.steh...@gmail.com: Hello what is a real use case? Regards Pavel 2011/9/25 pasman pasmański pasma...@gmail.com: My english is not perfect, by accumulative i think about monotonically increasing function. It works that for clause WHERE f(x)=const: 1. Read root page of index_on_x and get x1 ... Xn 2. Calculate f(x1) ... f(xn) for this page 3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can test smaller range (xlower, xgreater). 4. Otherwise no rows satisfy condition. Step 3 we repeat for current index's page and subpages until xlower = searched x = xgreater 2011/9/25, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. Maybe you'd better define what you mean by accumulative ... This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const ... because it's sure not clear how you would get that to work. regards, tom lane -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- pasman -- 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] New feature: accumulative functions.
I found second use case. Look at expression: where left(str,n)='value' function left(str,n) increase monotonically for str and n. With this feature it can use index on str. Classic index needs recreating. 2011/9/25, Pavel Stehule pavel.steh...@gmail.com: Hello what is a real use case? Regards Pavel 2011/9/25 pasman pasmański pasma...@gmail.com: My english is not perfect, by accumulative i think about monotonically increasing function. It works that for clause WHERE f(x)=const: 1. Read root page of index_on_x and get x1 ... Xn 2. Calculate f(x1) ... f(xn) for this page 3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can test smaller range (xlower, xgreater). 4. Otherwise no rows satisfy condition. Step 3 we repeat for current index's page and subpages until xlower = searched x = xgreater 2011/9/25, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. Maybe you'd better define what you mean by accumulative ... This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const ... because it's sure not clear how you would get that to work. regards, tom lane -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- 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] New feature: accumulative functions.
2011/9/25 pasman pasmański pasma...@gmail.com:
I found second use case. Look at expression:
where left(str,n)='value'
function left(str,n) increase monotonically for str and n. With this
feature it can use index on str.
Classic index needs recreating.
these use cases are just theory - for example - this case should be
solved with immutable functions
I can use a functional index left(str, const) and use a query
where left(str, const) = left('value', const) and left(str, n) = 'value'
There are a theoretical cases, but these cases should be solved via
special data type and GiST index
Pavel
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
Hello
what is a real use case?
Regards
Pavel
2011/9/25 pasman pasmański pasma...@gmail.com:
My english is not perfect, by accumulative i think about monotonically
increasing function.
It works that for clause WHERE f(x)=const:
1. Read root page of index_on_x and get x1 ... Xn
2. Calculate f(x1) ... f(xn) for this page
3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can
test smaller range (xlower, xgreater).
4. Otherwise no rows satisfy condition.
Step 3 we repeat for current index's page and subpages until xlower =
searched x = xgreater
2011/9/25, Tom Lane t...@sss.pgh.pa.us:
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes:
I propose to add accumulative flag to a function definition. This
flag would be set for function f(x) which is accumulative and
immutable.
Maybe you'd better define what you mean by accumulative ...
This flag allows to use an index on x for clauses containing f(x):
where f(x) = const
where f(x) const
... because it's sure not clear how you would get that to work.
regards, tom lane
--
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
pasman
--
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] New feature: accumulative functions.
This feature give profits for increasing muliti-arg functions. Example: WHERE f(x,param) = const it may be impossible to create functional indexes for all params. 2011/9/25, Pavel Stehule pavel.steh...@gmail.com: Hello what is a real use case? Regards Pavel 2011/9/25 pasman pasmański pasma...@gmail.com: My english is not perfect, by accumulative i think about monotonically increasing function. It works that for clause WHERE f(x)=const: 1. Read root page of index_on_x and get x1 ... Xn 2. Calculate f(x1) ... f(xn) for this page 3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can test smaller range (xlower, xgreater). 4. Otherwise no rows satisfy condition. Step 3 we repeat for current index's page and subpages until xlower = searched x = xgreater 2011/9/25, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I propose to add accumulative flag to a function definition. This flag would be set for function f(x) which is accumulative and immutable. Maybe you'd better define what you mean by accumulative ... This flag allows to use an index on x for clauses containing f(x): where f(x) = const where f(x) const ... because it's sure not clear how you would get that to work. regards, tom lane -- pasman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- pasman -- 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] New feature: accumulative functions.
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I found second use case. Look at expression: where left(str,n)='value' function left(str,n) increase monotonically for str and n. With this feature it can use index on str. Can't get excited about that, because that only works in C locale, and in C locale you can already get the same result with WHERE str LIKE '...%' Also, I think you just moved the goalposts quite a bit by introducing multiple-argument functions into the proposed feature. That's going to add even more complexity, for instance there would need to be a way to specify which argument(s) the function was monotonic in. The C versus not-C locale aspect also shows that for textual arguments, it might matter which locale you're talking about. In short, this is looking awfully complicated, and I gauge the probable level of interest by the fact that you're the first person to ask for it in more than a dozen years of Postgres development. 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] New feature: accumulative functions.
See that setting flag on function need less work than create new gist
operator. Of course if postgresql's developers do biggest work before.
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
2011/9/25 pasman pasmański pasma...@gmail.com:
I found second use case. Look at expression:
where left(str,n)='value'
function left(str,n) increase monotonically for str and n. With this
feature it can use index on str.
Classic index needs recreating.
these use cases are just theory - for example - this case should be
solved with immutable functions
I can use a functional index left(str, const) and use a query
where left(str, const) = left('value', const) and left(str, n) = 'value'
There are a theoretical cases, but these cases should be solved via
special data type and GiST index
Pavel
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
Hello
what is a real use case?
Regards
Pavel
2011/9/25 pasman pasmański pasma...@gmail.com:
My english is not perfect, by accumulative i think about monotonically
increasing function.
It works that for clause WHERE f(x)=const:
1. Read root page of index_on_x and get x1 ... Xn
2. Calculate f(x1) ... f(xn) for this page
3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can
test smaller range (xlower, xgreater).
4. Otherwise no rows satisfy condition.
Step 3 we repeat for current index's page and subpages until xlower =
searched x = xgreater
2011/9/25, Tom Lane t...@sss.pgh.pa.us:
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes:
I propose to add accumulative flag to a function definition. This
flag would be set for function f(x) which is accumulative and
immutable.
Maybe you'd better define what you mean by accumulative ...
This flag allows to use an index on x for clauses containing f(x):
where f(x) = const
where f(x) const
... because it's sure not clear how you would get that to work.
regards, tom lane
--
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
pasman
--
pasman
--
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] New feature: accumulative functions.
Yes, i wrote this for pleasure and discusion, not for solve a real problem :). 2011/9/25, Tom Lane t...@sss.pgh.pa.us: =?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes: I found second use case. Look at expression: where left(str,n)='value' function left(str,n) increase monotonically for str and n. With this feature it can use index on str. Can't get excited about that, because that only works in C locale, and in C locale you can already get the same result with WHERE str LIKE '...%' Also, I think you just moved the goalposts quite a bit by introducing multiple-argument functions into the proposed feature. That's going to add even more complexity, for instance there would need to be a way to specify which argument(s) the function was monotonic in. The C versus not-C locale aspect also shows that for textual arguments, it might matter which locale you're talking about. In short, this is looking awfully complicated, and I gauge the probable level of interest by the fact that you're the first person to ask for it in more than a dozen years of Postgres development. regards, tom lane -- pasman -- 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] New feature: accumulative functions.
2011/9/25 pasman pasmański pasma...@gmail.com:
See that setting flag on function need less work than create new gist
operator. Of course if postgresql's developers do biggest work before.
any feature in pg should to have a general usage - with real use cases
and real performance advantages.
I am not sure, if monotonically functions should be useful - this
request is relative strong. This feature should be interesting, but
should be a source of bugs if somebody use it wrong. Some similar
searching is possible with multidimensional indexes.
note: a searching is one task - second task is design of estimations.
Pavel
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
2011/9/25 pasman pasmański pasma...@gmail.com:
I found second use case. Look at expression:
where left(str,n)='value'
function left(str,n) increase monotonically for str and n. With this
feature it can use index on str.
Classic index needs recreating.
these use cases are just theory - for example - this case should be
solved with immutable functions
I can use a functional index left(str, const) and use a query
where left(str, const) = left('value', const) and left(str, n) = 'value'
There are a theoretical cases, but these cases should be solved via
special data type and GiST index
Pavel
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
Hello
what is a real use case?
Regards
Pavel
2011/9/25 pasman pasmański pasma...@gmail.com:
My english is not perfect, by accumulative i think about monotonically
increasing function.
It works that for clause WHERE f(x)=const:
1. Read root page of index_on_x and get x1 ... Xn
2. Calculate f(x1) ... f(xn) for this page
3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can
test smaller range (xlower, xgreater).
4. Otherwise no rows satisfy condition.
Step 3 we repeat for current index's page and subpages until xlower =
searched x = xgreater
2011/9/25, Tom Lane t...@sss.pgh.pa.us:
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes:
I propose to add accumulative flag to a function definition. This
flag would be set for function f(x) which is accumulative and
immutable.
Maybe you'd better define what you mean by accumulative ...
This flag allows to use an index on x for clauses containing f(x):
where f(x) = const
where f(x) const
... because it's sure not clear how you would get that to work.
regards, tom lane
--
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
pasman
--
pasman
--
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] In which case PG_VERSION file updates ?
On Sunday, September 25, 2011 2:26:56 am Raghavendra wrote: Thanks Adrian Klaver, Provided link gives about the information of what PG_VERSION file, which am aware of:) In my observation, all the object related OID's, _fsm, _vm files under $PGDATA/base/database-oid/ directory will change as per the changes made to the database, whereas PG_VERSION file never changes. Because its the file tells on which version of Binary the Data directory is built upon. So, my question is, Is there any case, where PG_VERSION file updates with any of the utility process or PG_VERSION file never been touched by PG-Instance ? From what I can see, PG_VERSION is written at database creation to document the major version of the Postgres instance used to create the database. Since it only holds the major version string (i.e. 9.0) it is not touched during minor updates, for example 9.0.0 -- 9.0.1. Grepping the pg_upgrade code shows it might touch PG_VERSION. A quick grep on the rest of the source code shows only initdb writing out PG_VERSION, though it is checked by other code. This is by no means an in depth look and it would take some one with more knowledge of Postgres internals to give you a definitive answer. Might be worth a post on - hackers. --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ -- 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] New feature: accumulative functions.
For single argument strict increasing function f(x), estimation is
simple: it is f(estimation of x).
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
2011/9/25 pasman pasmański pasma...@gmail.com:
See that setting flag on function need less work than create new gist
operator. Of course if postgresql's developers do biggest work before.
any feature in pg should to have a general usage - with real use cases
and real performance advantages.
I am not sure, if monotonically functions should be useful - this
request is relative strong. This feature should be interesting, but
should be a source of bugs if somebody use it wrong. Some similar
searching is possible with multidimensional indexes.
note: a searching is one task - second task is design of estimations.
Pavel
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
2011/9/25 pasman pasmański pasma...@gmail.com:
I found second use case. Look at expression:
where left(str,n)='value'
function left(str,n) increase monotonically for str and n. With this
feature it can use index on str.
Classic index needs recreating.
these use cases are just theory - for example - this case should be
solved with immutable functions
I can use a functional index left(str, const) and use a query
where left(str, const) = left('value', const) and left(str, n) = 'value'
There are a theoretical cases, but these cases should be solved via
special data type and GiST index
Pavel
2011/9/25, Pavel Stehule pavel.steh...@gmail.com:
Hello
what is a real use case?
Regards
Pavel
2011/9/25 pasman pasmański pasma...@gmail.com:
My english is not perfect, by accumulative i think about monotonically
increasing function.
It works that for clause WHERE f(x)=const:
1. Read root page of index_on_x and get x1 ... Xn
2. Calculate f(x1) ... f(xn) for this page
3. When f(x1)=const= f(xn) then x1 = searched x = xn and we can
test smaller range (xlower, xgreater).
4. Otherwise no rows satisfy condition.
Step 3 we repeat for current index's page and subpages until xlower =
searched x = xgreater
2011/9/25, Tom Lane t...@sss.pgh.pa.us:
=?ISO-8859-2?Q?pasman_pasma=F1ski?= pasma...@gmail.com writes:
I propose to add accumulative flag to a function definition. This
flag would be set for function f(x) which is accumulative and
immutable.
Maybe you'd better define what you mean by accumulative ...
This flag allows to use an index on x for clauses containing f(x):
where f(x) = const
where f(x) const
... because it's sure not clear how you would get that to work.
regards, tom lane
--
pasman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
--
pasman
--
pasman
--
pasman
--
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] In which case PG_VERSION file updates ?
On Sun, Sep 25, 2011 at 3:26 AM, Raghavendra raghavendra@enterprisedb.com wrote: Thanks Adrian Klaver, Provided link gives about the information of what PG_VERSION file, which am aware of:) In my observation, all the object related OID's, _fsm, _vm files under $PGDATA/base/database-oid/ directory will change as per the changes made to the database, whereas PG_VERSION file never changes. Because its the file tells on which version of Binary the Data directory is built upon. So, my question is, Is there any case, where PG_VERSION file updates with any of the utility process or PG_VERSION file never been touched by PG-Instance ? If you run pg_upgrade I'd expect that to change it. -- 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] (another ;-)) PostgreSQL-derived project ...
On 9/25/11, David Johnston pol...@yahoo.com wrote:
On Sep 25, 2011, at 2:11, Albretch Mueller lbrt...@gmail.com wrote:
Can you or do you know of anyone who has made those kinds of
imaginations falsifiable?
No; not worth my effort.
~
;-)
~
That approach strips down on application complexity. My apps don't have
to do any post-processing of the data - I query the records I need and
the app merely displays them.
~
Again have you actually tested those assumptions?
Is this the best response you can come up with? The crux of the
counter-argument is that by having PostgreSQL handle 'advanced' features
application code avoids the need to do so. The principle of code-reuse and
the fact the features are executed by the same program holding the data make
this a de-facto truth (and yes, one that we are probably taking for
granted).
~
First, I wonder what kind of technical person would say there are
de-facto truth(s). I thought only politicians would talk like that.
Now, in a sense you are right, I am talking from the background of my
own experiences (and so are you). When I have developed relatively
complicated and heavily accessed websites I only use DBMS when I need
to actually persist any data. For example there are options in Tomcat
(the java-based web serverrr) to offload session handling to a DBMS
(which is great when you need to stat(istically trace and infer users'
navigation) and establish transactions offloading a timed-out session
to an actual database hitting thhard drivevee (some user got
distracted ...) ...) and that sounds great, but anything dealing with
I/O would invariably slow your apps, so what I do is use in-mem (lite)
DBMS such as Hypersonic SQL (hsqldb.org) to keep sessions off the I/O
subsystem and the speed increase is --very-- noticeable
~
Since any piece of code engaging the I/O such as database access code
should be as fast and simple as possible; yes, I would design, say,
java code wrappers doing anything that is not strictly INSERT and
SELECT raw data ... let me deal with the semantics and business
intelligence of the data myself
~
But, if you really feel a bare-bones implementation of PostgreSQL
is worthwhile you are the one that needs to test (and state explicitly) your
own underlying assumptions to see whether they hold and thus make such an
endeavor worthwhile.
~
you are right and I am up to it
~
On 9/25/11, Alban Hertroys haram...@gmail.com wrote:
... and can now use those features within my SQL statements/queries.
~
For what exactly? Isn't a comparison on 4 numeric bytes (1 (or 1/2)
word in modern hardware) more efficient than comparing sequences of
string characters?
Data types aren't stored in the database as character strings (unless you
define your columns as text, of course).
~
I was talking about text and any formatting option in NUMERIC or DATE data
~
On 9/25/11, Tom Lane t...@sss.pgh.pa.us wrote:
Albretch Mueller lbrt...@gmail.com writes:
Well, at least I thought you would tell me where the postgresql-base
is to be found. The last version I found is:
http://freebsd.csie.nctu.edu.tw/pub/distfiles/postgresql/postgresql-base-8.3beta2.tar.bz2
and I wondered what that is and why there are no postgresql-base
after 8.3beta2
We stopped bothering because the split tarballs weren't really good for
anything separately ...
~
Thank you for answering my question
~
Now, having said that, there has been some interest in pushing
lesser-used chunks like the geometric datatypes out into extensions ...
~
Yes, that is it, extensions! It would be great if PG could be built
in a more modular way, with just the features you need. Clusters of
dependencies will have to be checked ... I think that would enrich PG
development
~
On 9/25/11, Uwe Schroeder u...@oss4u.com wrote:
... you're looking for a non-sql compliant SQL database where a lot of
the data integrity is actually coded in the application :-)
~
First past of your statement I acknowledged, but how is it exactly
that lot of the data integrity is actually coded in the application
~
Take dates for example: you'd have to code very carefully to catch all the
different ways dates are represented on this planet.
~
Yeah! And java does an exceptionally good job at that (including
internationalization)
~
http://download.oracle.com/javase/7/docs/api/java/ {util/Date.html,
text/DateFormat.html}
~
So, you would ultimately just have to store a long value into the DBMS
~
I am amazed to read that you/the PC community were still running
regression tests in ASCII
http://www.postgresql.org/docs/9.1/static/release-9-1.html
(E.1.3.12. Source Code)
* Run regression tests
(postgresql.org/docs/9.1/static/install-procedure.html#BUILD) in the
default encoding (Peter Eisentraut)
Regression tests were previously always run with SQL_ASCII encoding.
~
Same goes for numbers: if everything is stored in a byte sequence, how does
the database know you try to compare the number 1 with the character 1?
~
Re: [GENERAL] (another ;-)) PostgreSQL-derived project ...
On Sun, Sep 25, 2011 at 09:41:19PM +, Albretch Mueller wrote: I am amazed to read that you/the PC community were still running regression tests *in ASCII*: http://www.postgresql.org/docs/9.1/static/release-9-1.html (E.1.3.12. Source Code) * Run regression tests (postgresql.org/docs/9.1/static/install-procedure.html#BUILD) in the default encoding (Peter Eisentraut) Regression tests were previously always run *with SQL_ASCII* encoding. Quite obviously you have got no clue and didn't bother checking either. Karsten -- GPG key ID E4071346 @ gpg-keyserver.de E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- 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] New feature: accumulative functions.
I write small summary. Feature details: additional flags for monotonical functions. Learn planner to use them. New node in execution plan - functional index scan. Pro: single btree index may be used in many expressions containing only monotonnical functions. Contra: big developement effort. No new functionalities - functional or gist index does the same work. Not for all encodings. Unknown performance advantages. pasman -- 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] (another ;-)) PostgreSQL-derived project ...
First, I wonder what kind of technical person would say there are
de-facto truth(s). I thought only politicians would talk like that.
Well, politicians and Microsoft, Oracle etc. :-)
Now, in a sense you are right, I am talking from the background of my
own experiences (and so are you). When I have developed relatively
complicated and heavily accessed websites I only use DBMS when I need
to actually persist any data. For example there are options in Tomcat
(the java-based web serverrr) to offload session handling to a DBMS
(which is great when you need to stat(istically trace and infer users'
navigation) and establish transactions offloading a timed-out session
to an actual database hitting thhard drivevee (some user got
distracted ...) ...) and that sounds great, but anything dealing with
I/O would invariably slow your apps, so what I do is use in-mem (lite)
DBMS such as Hypersonic SQL (hsqldb.org) to keep sessions off the I/O
subsystem and the speed increase is --very-- noticeable
~
Since any piece of code engaging the I/O such as database access code
should be as fast and simple as possible; yes, I would design, say,
java code wrappers doing anything that is not strictly INSERT and
SELECT raw data ... let me deal with the semantics and business
intelligence of the data myself
So you're keeping a lot in memory, which to me suggests plenty of hardware is
available. One of my current apps chews up 8Gb of memory just for the app and
I can't afford to get a 64Gb or more server. If I wanted to keep permanently
accessed data in memory, I'd need somewhere around 1/2 a terrabyte of memory -
so obviously not an option (or maybe really bad database design :-) )
That said, just considering the cost/effort it takes to strip Postgresql down,
why don't you go with a server that has 1TB of solid state discs? That strips
down the I/O bottleneck considerably without any effort.
Data types aren't stored in the database as character strings (unless you
define your columns as text, of course).
~
I was talking about text and any formatting option in NUMERIC or DATE data
In my experience data formatting goes both ways, in and out. Out is
obviously not a major issue because errors don't cause data corruption. In,
however, is a different issue. Errors in inwards conversion will cause data
corruption. So unless you have an application that does very little in and a
lot of out, you still have to code a lot of data conversion which otherwise
someone else (the postgresql developers) have already done for you.
Take dates for example: you'd have to code very carefully to catch all
the different ways dates are represented on this planet.
~
Yeah! And java does an exceptionally good job at that (including
internationalization)
Maybe it does. I never coded Java because I don't like to use technology where
Oracle can come sue me :-) I do know however that a lot of languages have
quirks with dates and internationalization (python you mentioned earlier being
one of them)
http://download.oracle.com/javase/7/docs/api/java/ {util/Date.html,
text/DateFormat.html}
~
So, you would ultimately just have to store a long value into the DBMS
Yes, a long value - which can represent pretty much any valid and invalid date
ever devised, so again you don't really know what's in the database when you
leave the validation to the application.
This is something I would do with wrapping code using input and
output bound command objects which are indexed after the same column
index the DBMS uses
Which still depends on your use case. Your assumption is that every piece of
code is coded in Java - which is fine if that's what your application calls
for. It's going to be a major hassle when you ever have to re-code in a
different language though.
To me, that's moving data integrity into the application.
~
Not exactly! Integrity is still a matter of the DBMS which can now
handle it in an easier way in someone write a date in bangla and
somebody else in Ukranian this is still the same date/time value
ultimately determined by the rotation of our planet around the sun ...
and all we need for that is a long value. Now, aren't we easying
things for the DBMS?
I agree to disagree on this one. The date value the database stores in this
case is a long. Any long can be converted into a valid date - but is it
really the date that was entered in the first place? If I give a date
representation, i.e. 12/31/2010 to the database, I personally don't really
care how the database stores the date underneath. All that interests me is
that the next time I ask for that field I get 12/31/2010 back. There is no
error that can be made other than user error if you ask the database to store
a specific date representation. There are errors you can make in your own
conversion code which can lead to a different long stored than intended. So
again data integrity is at least partially in
Re: [GENERAL] (another ;-)) PostgreSQL-derived project ...
On 09/25/11 7:48 PM, Uwe Schroeder wrote: Which still depends on your use case. Your assumption is that every piece of code is coded in Java - which is fine if that's what your application calls for. It's going to be a major hassle when you ever have to re-code in a different language though. its the old hammer and nail thing [1]. a pure Java programmer wants to see everything in Java as its the tool he knows. [1] - If your only tool is a hammer, the whole world looks like a nail. -- john r pierceN 37, W 122 santa cruz ca mid-left coast -- 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] (another ;-)) PostgreSQL-derived project ...
On 9/25/11, Karsten Hilbert karsten.hilb...@gmx.net wrote: On Sun, Sep 25, 2011 at 09:41:19PM +, Albretch Mueller wrote: I am amazed to read that you/the PC community were still running regression tests *in ASCII*: http://www.postgresql.org/docs/9.1/static/release-9-1.html (E.1.3.12. Source Code) * Run regression tests (postgresql.org/docs/9.1/static/install-procedure.html#BUILD) in the default encoding (Peter Eisentraut) Regression tests were previously always run *with SQL_ASCII* encoding. Quite obviously you have got no clue and didn't bother checking either. ~ Karsten, you are right to some extent, but this is what I plainly read/it says and I can't keep a mental map of PG's code base and culture. ~ Now, a more insufferable one, when I said: ~ ... if someone write a date in Bangla and somebody else in Ukrainian this is still the same date/time value ultimately determined by the rotation of our planet around the sun ~ I meant by the rotation of our planet around the its own axis and even if it is primary school knowledge as a Physicist I more than enough know the difference even though the rotation of our planet around the sun does influence what we nominally consider to be the wall clock time (day light savings ...) ~ lbrtchx -- 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] (another ;-)) PostgreSQL-derived project ...
On 9/26/11, Uwe Schroeder u...@oss4u.com wrote:
In my experience data formatting goes both ways, in and out. Out is
obviously not a major issue because errors don't cause data corruption. In,
however, is a different issue. Errors in inwards conversion will cause
data
corruption. So unless you have an application that does very little in and
a
lot of out, you still have to code a lot of data conversion which
otherwise
someone else (the postgresql developers) have already done for you.
~
Well, yes Uwe, but any code in the DBMS would slow a bit its main job
which (to me) is store data and keeping its consistency as soon as
possible
~
Take dates for example: you'd have to code very carefully to catch all
the different ways dates are represented on this planet.
~
Yeah! And java does an exceptionally good job at that (including
internationalization)
Maybe it does. I never coded Java because I don't like to use technology
where
Oracle can come sue me :-)
~
Yeah! I felt like sh!t when I heard that Oracle had bought Sun, a
-slightly- more open company, at least they like to keep that
perception of themselves ...
~
http://download.oracle.com/javase/7/docs/api/java/ {util/Date.html,
text/DateFormat.html}
~
So, you would ultimately just have to store a long value into the DBMS
Yes, a long value - which can represent pretty much any valid and invalid
date
ever devised, so again you don't really know what's in the database when you
leave the validation to the application.
~
Not if you get that long value through java ;-)
~
This is something I would do with wrapping code using input and
output bound command objects which are indexed after the same column
index the DBMS uses
Which still depends on your use case. Your assumption is that every piece of
code is coded in Java - which is fine if that's what your application calls
for. It's going to be a major hassle when you ever have to re-code in a
different language though.
~
Well, yes you are right
~
To me, that's moving data integrity into the application.
~
Not exactly! Integrity is still a matter of the DBMS which can now
handle it in an easier way in someone write a date in bangla and
somebody else in Ukranian this is still the same date/time value
ultimately determined by the rotation of our planet around the sun ...
and all we need for that is a long value. Now, aren't we easying
things for the DBMS?
I agree to disagree on this one. The date value the database stores in this
case is a long. Any long can be converted into a valid date - but is it
really the date that was entered in the first place?
~
Again, not if you get that long value through java. It takes care of
doing those checks for you. You could for example not enter 02/30/2011
as a date object in java and try to get a long out of it
~
If I give a date
representation, i.e. 12/31/2010 to the database, I personally don't really
care how the database stores the date underneath. All that interests me is
that the next time I ask for that field I get 12/31/2010 back.
~
But 12/31/2010 is ultimately a representation of that long you
would have inserted that was and can be represented in many different
ways, depending on user preferences
~
There is no
error that can be made other than user error if you ask the database to
store
a specific date representation. There are errors you can make in your own
conversion code which can lead to a different long stored than intended.
~
conversion code will not be mine and it is part of java and I would
guess here python or any decent programming language
~
~
Well, the code you will have to write either way, regardless of where
you keep it and in order to not even have to restart the application
server cold I would code command objects (like function pointers in C)
to handle those cases. It is ultimately a strings of characters you
are dealing with
With the right design, you will have to rewrite the visual layer, not the
application logic. Errors in the visual layer are of little consequence
(except disgruntled users). So yes, if you use some kind of middleware that
does all the converting and validating for you, the difference is
negligible.
But then, why write your own when the database already provides that
functionality?
~
Because DBMS are I/O beasts and are way more likely to enter into
delayed states and concurrency conflicts, so to me, the less you
hassle them the better, for example I never handle http sessions with
a DBMS because they are very volatile, temporary and user specific. An
also, the hardware DBMSs sit on mechanically and magnetically wears
off with usage
~
On 9/26/11, John R Pierce pie...@hogranch.com wrote:
its the old hammer and nail thing [1]. a pure Java programmer wants to
see everything in Java as its the tool he knows.
~
Well, not exactly. I am not religious about code. The most
sophisticated code I have written in my life was in FORTRAN, but the
most amount
