Re: [GENERAL] Maximum transaction rate
On Thu, Mar 19, 2009 at 12:49:52AM +0100, Marco Colombo wrote: > It has to wait for I/O completion on write(), then, it has to go to > sleep. If two different processes do a write(), you don't know which > will be awakened first. Preallocation don't mean much here, since with > O_SYNC you expect a physical write to be done (with the whole sleep/ > HW interrupt/SW interrupt/awake dance). It's true that you may expect > the writes to be carried out in order, and that might be enough. I'm > not sure tho. True, but the relative wakeup order of two different processes is not important since by definition they are working on different transactions. As long as the WAL writes for a single transaction (in a single process) are not reordered you're fine. The benefit of a non-overwriting storage manager is that you don't need to worry about undo's. Any incomplete transaction is uncomitted and so any data produced by that transaction is ignored. > It may be acceptable or not. Sometimes it's not. Sometimes you must be > sure the data in on platters before you report "committed". Sometimes > when you say "fsync!" you mean "i want data flushed to disk NOW, and I > really mean it!". :) Ofcourse. Committing a transaction comes down to flipping a single bit. Before you flip it all the WAL data for that transaction must have hit disk. And you don't tell the client the transaction has committed until the fipped bit has hit disk. And fsync better do what you're asking (how fast is just a performance issue, just as long as it's done). Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] Special charaters
Alvaro Herrera wrote: > > > > This happens when I import csv file via my app into postgres. > > > > The csv file has some “hello” from microsoft word 2003. > > > > In postgres it appears as hello > > > > > > > > Could somebody help on this? > > > > > > Check your encodings. > > > > I doubt that's it, but I've been wrong before... I say, Google for > > "smart quotes" and... good luck. (They don't belong to any encoding.) > > Well, they must exist at least on utf8, otherwise they couldn't have > been pasted in the original message. To be more precise, the two characters in question are UNICODE 201C and 201D. They exist only in UNICODE and Windows encodings. Concerning the original problem: - Figure out what the encoding of the CSV-File is. If the quotes are one byte wide, it is a windows encoding, otherwise something UNICODE. - Set the environment variable PGCLIENTENCODING to the value that belongs to this encoding. There's a list in the documentation: http://www.postgresql.org/docs/8.3/static/multibyte.html - Make sure that the database is UTF-8. Then e.g. import via psql's \copy should work fine. Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is there a method to Execute update in Pl/Pgsql function using rowtype variable
Greetings
I have a record variable in plpgsql function:
DECLARE
tabRow TAB%ROWTYPE;
someVar varchar;
BEGIN
/*I Can do something like this*/
insert into TAB select tabRow.*;
/*but i want to update table TAB using variable rowTab*/
/* i know the list of colums that i want to update */
/*I've tried casting Record to text but I can't cast text to record:*/
someVar:=tabRow;
execute 'UPDATE TAB '||upd_expr()||' from CAST ('||someVar||' as TAB) foo where
pkey='||tabRow.pkey;
/*
function upd_expr returns string ' col1=x.col2,col2=x.col2, ... , coln=x.coln
';
*/
/*
but "someVar:=tabRow;" makes someVar='(A,B,C,D,I)' and cast doesn work, it
should be someVar='(''A'',''B'',''C'',''D'',NULL,NULL,NULL,NULL,''I'')'
*/
Is there a wa I can do such update?
Regards,
Tomasz
Re: [GENERAL] Fulltext - multiple single column indexes
On Thu, 19 Mar 2009, esemba wrote:
Hi,
I have table with several columns and need to perform fulltext search over
volatile number of columns.
I can't use multicolumn gist index or gin index over concatenated columns,
so I've created several single column indexes (one for each column I want to
search) and now I need to query them like this:
to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
coalesce(resume, '')) || ...
@@ to_tsquery('cs', 'Query text');
alter table YOURTABLE add columnt fts tsvector;
update YOURTABLE set fts=
to_tsvector('cs', coalesce(annotation, '')) ||
to_tsvector('cs', coalesce(resume, '')) || ...
create index fts_idx on YOURTABLE using gin(fts);
vacuum analyze YOURTABLE;
select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
This query works, but EXPLAIN has shown me, that postgres doesn't use the
indexes, so the query over a table with several thousands of records last
very long time. I've figured out, that indexes probably cannot be used this
way. What is a recommendation for this scenario?
Indexes over static number of columns work fine, but I can't use them,
because in my application logic I want to let user choose which columns to
search.
Thank you for your reply.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Multiple natural joins
Sam Mason wrote: On Thu, Mar 19, 2009 at 04:22:56PM +0200, Peter Eisentraut wrote: Joins nest from left to write, so tbl_c NATURAL JOIN tbl_a NATURAL JOIN tbl_b means (tbl_c NATURAL JOIN tbl_a) NATURAL JOIN tbl_b If you want a different order, you can set the parentheses differently, with possibly different results. When would you get different results? As far as I can tell, for INNER joins, you'd always get the same thing back for any ordering. With OUTER joins it obviously matters what's going on, but that's expected. Right. -- 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] Fulltext - multiple single column indexes
esemba wrote:
> Hi,
> I have table with several columns and need to perform fulltext search over
> volatile number of columns.
> I can't use multicolumn gist index or gin index over concatenated columns,
> so I've created several single column indexes (one for each column I want to
> search) and now I need to query them like this:
>
> to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
> coalesce(resume, '')) || ...
> @@ to_tsquery('cs', 'Query text');
>
> This query works, but EXPLAIN has shown me, that postgres doesn't use the
> indexes
[snip]
You're right in concluding this isn't really going to work. You could
have separate indexes for each column and check them all:
SELECT ... WHERE col1 @@ ... OR col2 @@ ...
Where it thinks it is sensible, PG should use a bitmap and combine the
different index scans. If you already have single-column indexes this
makes a lot of sense.
Alternatively, you could add a fulltext_blocks table with a "source"
column and keep it up to date via triggers. That way you could search
something like:
SELECT some_id FROM fulltext_blocks WHERE words @@ ... AND source IN
('col11', 'col2');
This is more effort, but has the advantage that you can add scores to
each column if you require. It also lets you be really clever and say to
users "you searched for 'foo' on columns 1,2,3 - no matches. There are
matches on other columns - show you these?"
HTH
--
Richard Huxton
Archonet Ltd
--
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] Maximum transaction rate
Martijn van Oosterhout wrote: > True, but the relative wakeup order of two different processes is not > important since by definition they are working on different > transactions. As long as the WAL writes for a single transaction (in a > single process) are not reordered you're fine. I'm not totally sure, but I think I understand what you mean here, indipendent transactions by definition don't care about relative ordering. .TM. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
John Cheng wrote: >> This is question for Juan, have you asked the MySQL mailing list? I'm afraid MySQL general list isn't as dynamic as PostgreSQL general list. http://lists.mysql.com/mysql/216795 MySQL general list: 4 answers in about 48 hours PostgreSQL general list: 27 answers in about 72 hours Thanks again to everybody for the amount of knowledge you have shared in this thread. Juan Karlos 2009/3/17 John Cheng > This is question for Juan, have you asked the MySQL mailing list? What do > they say about this? > > On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones wrote: > >> >> On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: >> >> The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? >>> >>> As you can imagine, PostgreSQL. >>> >>> My main reasons are that in a proper transactional environment (ie >>> you're not using scary MyISAM tables) Pg is *much* better about handling >>> concurrent load, particularly concurrent activity by readers and writers. >>> >> >> Actually, following this comment it should be noted that if you were to >> choose MySQL you'd pretty much be making a decision to *not* be using >> transactions at all. The reason for this is that while InnoDB does support >> MySQL's geometry data types it does *not* support indexes on geometry >> columns, only MyISAM does which does not support transactions. Call me old >> fashioned if you like, but I like my data to have integrity ;) >> >> Erik Jones, Database Administrator >> Engine Yard >> Support, Scalability, Reliability >> 866.518.9273 x 260 >> Location: US/Pacific >> IRC: mage2k >> >> >> >> >> >> >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> > > > > -- > - John L Cheng >
Re: [GENERAL] Fulltext - multiple single column indexes
Well, thank you both for response, but I'm not sure, I understand Oleg's
solution. This would work, but where is the variability of searched columns?
In your example, I create new indexed column with concatenated vectors of 2
columns. But I sometimes new to search only annotation, sometimes resume,
sometomes both.
Oleg Bartunov wrote:
>
> On Thu, 19 Mar 2009, esemba wrote:
>
>>
>> Hi,
>> I have table with several columns and need to perform fulltext search
>> over
>> volatile number of columns.
>> I can't use multicolumn gist index or gin index over concatenated
>> columns,
>> so I've created several single column indexes (one for each column I want
>> to
>> search) and now I need to query them like this:
>>
>> to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
>> coalesce(resume, '')) || ...
>> @@ to_tsquery('cs', 'Query text');
>
> alter table YOURTABLE add columnt fts tsvector;
> update YOURTABLE set fts=
> to_tsvector('cs', coalesce(annotation, '')) ||
> to_tsvector('cs', coalesce(resume, '')) || ...
> create index fts_idx on YOURTABLE using gin(fts);
> vacuum analyze YOURTABLE;
> select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
>
>
>>
>> This query works, but EXPLAIN has shown me, that postgres doesn't use the
>> indexes, so the query over a table with several thousands of records last
>> very long time. I've figured out, that indexes probably cannot be used
>> this
>> way. What is a recommendation for this scenario?
>> Indexes over static number of columns work fine, but I can't use them,
>> because in my application logic I want to let user choose which columns
>> to
>> search.
>>
>> Thank you for your reply.
>>
>
> Regards,
> Oleg
> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
--
View this message in context:
http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22617663.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Breaking national language support in Solaris 8
There is a proposal being discussed on the hackers list about adding gettext plural support to the national language support (NLS) feature (--enable-nls) in the upcoming PostgreSQL 8.4. This would mean that plurals of translated messages (e.g., in psql: 1 row/n rows) will work properly in languages that don't have plural rules similar to English. This will, however, only work with GNU Gettext (as used in Linux and BSD distributions) and Solaris 9 or later, and it is not easy to provide a backward compatible mode. So we would probably break NLS in Solaris 8 or earlier. Workarounds would be building PostgreSQL 8.4 without NLS or building with GNU Gettext instead of the one built into Solaris. If any user of such older Solaris systems or perhaps other systems with similar issues that we have not considered thinks this would be a terrible idea, please speak up now. The thread on the hackers list can also be followed here: http://archives.postgresql.org/message-id/87y6v3gpa8@seb.progtech.ru -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Hello it isn't correct comparation. MySQL people use mainly web forum regards Pavel Stehule 2009/3/20 Juan Pereira : > John Cheng wrote: > >>> This is question for Juan, have you asked the MySQL mailing list? > > I'm afraid MySQL general list isn't as dynamic as PostgreSQL general list. > > http://lists.mysql.com/mysql/216795 > > MySQL general list: 4 answers in about 48 hours > PostgreSQL general list: 27 answers in about 72 hours > > > Thanks again to everybody for the amount of knowledge you have shared in > this thread. > > Juan Karlos > > > 2009/3/17 John Cheng >> >> This is question for Juan, have you asked the MySQL mailing list? What do >> they say about this? >> >> On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones wrote: >>> >>> On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: >>> > The question is: Which DBMS do you think is the best for this kind of > application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. >>> >>> Actually, following this comment it should be noted that if you were to >>> choose MySQL you'd pretty much be making a decision to *not* be using >>> transactions at all. The reason for this is that while InnoDB does support >>> MySQL's geometry data types it does *not* support indexes on geometry >>> columns, only MyISAM does which does not support transactions. Call me old >>> fashioned if you like, but I like my data to have integrity ;) >>> >>> Erik Jones, Database Administrator >>> Engine Yard >>> Support, Scalability, Reliability >>> 866.518.9273 x 260 >>> Location: US/Pacific >>> IRC: mage2k >>> >>> >>> >>> >>> >>> >>> -- >>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >>> To make changes to your subscription: >>> http://www.postgresql.org/mailpref/pgsql-general >> >> >> >> -- >> - John L Cheng > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Hello,
I try to use the srf API and I have a link error. When I did :
FuncCallContext *funcctx;
...
if (SRF_IS_FIRSTCALL()){
funcctx = SRF_FIRSTCALL_INIT(); // If I remove this line, no link
error
...
}
I have this link error : "undefined reference to
'init_MultiFuncCall(FunctionCallInfoData*)'".
The init_MultiFuncCal function is in the funcapi.h file, but where is
the associated library ? I try all the libraries of PostreSQL, but no
success .
Any idea ? Thanks.
[GENERAL] Move PG installation to a new computer
Hi, I'm getting a new notebook and want to confirm that my idea on how to move my Postgres installation will work This is a development/test installation and not a production system, so it doesn't need to be 100% fail safe. Both systems are Windows XP 32bit. My plan was to install the same PG release (8.3) on the new computer, skip the initdb task during installation, but let the installer create the necessary service pointing to an empty data directory. Using the same OS user account. Once the installation is finished, copy all files from the old datadir (after shutting down that postmaster) to the new datadir as the PG Windows user, so that access right are setup correctly. As both machines have the same OS and architecture my assumption is that this should work. Or am I mistaken? Thanks Thomas -- 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] Is there a meaningful benchmark?
> That's why I was looking for a more balanced benchmark that exercises > said capabilities. OK, here's the thing, I will give you *one* sample issue to think about, as an illustration of the kinds of differences there are. - PostgresQL uses MVCC instead of row or page locking, which means only writer vs writer locks, which means in many situations less contention and better throughput as your concurrent load goes up. - But it also means slower count(*) and no covering indexes. (Keeping multiple versions of index pages would be too slow for many other operations, so PG has go to the table pages to check which rows are actually visible in the current transaction.) See? Overall, PG performs very well and is certainly comparable to the "big boys" on the same hardware. But the strengths & weaknesses can be *very* specific to particular queries. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.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] Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Ben Ali Rachid writes:
> I have this link error : "undefined reference to
> 'init_MultiFuncCall(FunctionCallInfoData*)'".
The fact that the linker knows the argument data type indicates that
you're trying to compile your code as C++. Postgres is written in C,
not C++.
You can try putting extern "C" { ... } around your inclusions of
Postgres header files, but you're likely to run into other problems
later on. On the whole I'd recommend using plain C for backend
functions if you possibly can.
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] What are the benefits of using a clustered index?
On Fri, 2009-03-20 at 09:33 -0600, Scott Ribe wrote: > > Hopefully we regard it as a missing feature rather than as a separate > > definition. We could cluster the index, we just don't, yet. > > Wouldn't this require keeping around multiple versions of index pages for > MVCC? No, it wouldn't require that. Just think one index tuple points to more than one heap row. We would still need to check visibility on the rows returned to ensure MVCC. Less index pointers, smaller index. The trick is: How? But that's a secondary issue to getting it on the TODO list, which is all I'm suggesting at present. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What are the benefits of using a clustered index?
> Just think one index tuple points to more than one heap row. We would > still need to check visibility on the rows returned to ensure MVCC. So you wind up with the heap rows stored in their own tree-like structure outside the index? OK. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.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] Is there a meaningful benchmark?
You have a point, as do a lot of the other folks. However, keeping the KISS principle in mind, you can create a benchmark that simply sets up a sample database and forks off a bunch of processes to do random updates for an hour, say. Dead simple. In fact, it's so simple that I've already written the code and have it running against Postgres now. A Perl DBI script runs in a loop updating, and later prints out the number of transactions it completed in the given time frame. At the end I just tally up the numbers and I have the Will Rutherdale benchmark number for Postgres. It will give me a simple number in units of transactions per second. When I get time I'll set up an equivalent MySQL database on the same machine and run it against that. I have 'top' running in one screen and can see all the copies of my script and all the copies of postmaster and the CPU they're using. The degree to which I got excoriated just for asking the question convinced me that I was onto something and had to do it myself. -Will -Original Message- From: Scott Ribe [mailto:scott_r...@killerbytes.com] Sent: 20 March 2009 11:27 To: Will Rutherdale (rutherw); pgsql-general@postgresql.org Subject: Re: [GENERAL] Is there a meaningful benchmark? > That's why I was looking for a more balanced benchmark that exercises > said capabilities. OK, here's the thing, I will give you *one* sample issue to think about, as an illustration of the kinds of differences there are. - PostgresQL uses MVCC instead of row or page locking, which means only writer vs writer locks, which means in many situations less contention and better throughput as your concurrent load goes up. - But it also means slower count(*) and no covering indexes. (Keeping multiple versions of index pages would be too slow for many other operations, so PG has go to the table pages to check which rows are actually visible in the current transaction.) See? Overall, PG performs very well and is certainly comparable to the "big boys" on the same hardware. But the strengths & weaknesses can be *very* specific to particular queries. -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.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] Is there a meaningful benchmark?
On Fri, Mar 20, 2009 at 9:55 AM, Will Rutherdale (rutherw) wrote: > You have a point, as do a lot of the other folks. > > However, keeping the KISS principle in mind, you can create a benchmark > that simply sets up a sample database and forks off a bunch of processes > to do random updates for an hour, say. Dead simple. > > In fact, it's so simple that I've already written the code and have it > running against Postgres now. A Perl DBI script runs in a loop > updating, and later prints out the number of transactions it completed > in the given time frame. At the end I just tally up the numbers and I > have the Will Rutherdale benchmark number for Postgres. It will give me > a simple number in units of transactions per second. Just keep in mind that a single thread updating the database is not a very realistic benchmark. Databases tend to not get interesting until there are dozens to hundreds of threads running against it at the same time. -- 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] Is there a meaningful benchmark?
"Will Rutherdale (rutherw)" writes: > However, keeping the KISS principle in mind, you can create a benchmark > that simply sets up a sample database and forks off a bunch of processes > to do random updates for an hour, say. Dead simple. Indeed, and more than likely dead useless. The only benchmark that really counts is one's live application, which is probably not update-only and probably has a fairly non-random update pattern too. What people have been trying to point out to you is that you can certainly measure *something* with a benchmark test that has no thought behind it, but it's not clear whether the numbers you come up with will have any real-world value. 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] Is there a meaningful benchmark?
On Thu, 19 Mar 2009, Dann Corbit wrote: Oracle won't let you publish any benchmark numbers. So if you find an Oracle comparison, it's "unauthorized" You can find some useful comparisons that include Oracle if you look at the audited benchmarks from the TPC. I've collected links to a bunch at http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007#Sun_Microsystems_2007_jAppServer2004_Benchmark_Results -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SRID conflict, PostgreSQL 8.3
Hello,
I serve a Geospatial IS project that for some years has used PostgreSQL
and PostGIS. A strategy/scheme that has worked for all previous
versions has failed with a recent upgrade to 8.3 (e.g. 8.3.6-1).
Relation "catalog" is a large geospatially-enabled aggregation of data
with a variety of SRID's imbedded within "the_geom" attribute values.
Querying a view into the "catalog" which describes a subset of it's
tuples with identical SRID's (the uniqueness of the SRID associated with
this view's data is easily demonstrable with an ad-hoc query) has always
worked smoothly. With the 8.3 engine, an error is introduced:
SELECT "whatever"
FROM "a view into 'catalog'"
WHERE ((TRUE AND TRUE) AND "the_geom" && GeometryFromText('POLYGON
((-83.28 26.07,
-83.28 28.26,
-81.59 28.26,
-81.59 26.07,
-83.28 26.07))', -1))
results in this error:
"Operation on two geometries with different SRIDs"
The result of the GeometryFromText routine has, of course, but one SRID,
thus the SRID from "the_geom" must be the culprit. It's as if the
query is considering tuples in "catalog" outside of the view's domain.
(note: I can offer further evidence of this behavior- removing all tuples
from "catalog" other than those returned by a query against the view
eliminates the conflict/error).
Can someone comment on this mystery/phenomenon vis-a-vis PostgreSQL
version 8.3 (PostGIS 1.3.5)?
Many thanks,
Christopher Smith
--
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] Is there a meaningful benchmark?
On Fri, 20 Mar 2009, Will Rutherdale (rutherw) wrote: However, keeping the KISS principle in mind, you can create a benchmark that simply sets up a sample database and forks off a bunch of processes to do random updates for an hour, say. Dead simple. There's a benchmark tool that does something like this that comes with PostgreSQL named pgbench. A MySQL-oriented tool named sysbench also can do that, and it supports running against PostgreSQL as well--badly though, so it's hard to use that to do a fair comparison. Simple benchmarks tend to measure only one thing though, and it's often not what you think you're measuring. For example, pgbench produces a transactions/per second number. It's useful for comparing the relative performance between two PostgreSQL instances, and people think it gives you an idea of transactional performance. What the actual magnitude of the result measures in many cases is instead how well the generated data set fits in cache. If you're doing something update heavy, a lot of the time what you actually will measure is how fast your disk can seek, process a disk commit done using fsync, or some combination of the two. If you're not careful to make sure you're using the same level of disk commit guarantee on both installations, it's real easy to get bad benchmark results here. The intro to that subject from the PostgreSQL perspective is at http://www.postgresql.org/docs/8.3/static/wal-reliability.html On MySQL, the parameters that controls this behavior are described starting at http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit For something with lots of disk commits, it's critical that you have both systems configured identically here. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- 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] Is there a meaningful benchmark?
http://blog.redfin.com/devblog/2007/11/elephant_versus_dolphin_which_is_faster_which_is_smarter.html Here's another PgSQL/MySQL testimonial, with a spatial twist :) P On Fri, Mar 20, 2009 at 10:15 AM, Greg Smith wrote: > On Fri, 20 Mar 2009, Will Rutherdale (rutherw) wrote: > >> However, keeping the KISS principle in mind, you can create a benchmark >> that simply sets up a sample database and forks off a bunch of processes >> to do random updates for an hour, say. Dead simple. > > There's a benchmark tool that does something like this that comes with > PostgreSQL named pgbench. A MySQL-oriented tool named sysbench also can do > that, and it supports running against PostgreSQL as well--badly though, so > it's hard to use that to do a fair comparison. > > Simple benchmarks tend to measure only one thing though, and it's often not > what you think you're measuring. For example, pgbench produces a > transactions/per second number. It's useful for comparing the relative > performance between two PostgreSQL instances, and people think it gives you > an idea of transactional performance. What the actual magnitude of the > result measures in many cases is instead how well the generated data set > fits in cache. > > If you're doing something update heavy, a lot of the time what you actually > will measure is how fast your disk can seek, process a disk commit done > using fsync, or some combination of the two. If you're not careful to make > sure you're using the same level of disk commit guarantee on both > installations, it's real easy to get bad benchmark results here. The intro > to that subject from the PostgreSQL perspective is at > http://www.postgresql.org/docs/8.3/static/wal-reliability.html > > On MySQL, the parameters that controls this behavior are described starting > at > http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit > > For something with lots of disk commits, it's critical that you have both > systems configured identically here. > > -- > * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD > > -- > 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] Fulltext - multiple single column indexes
On Fri, 20 Mar 2009, esemba wrote:
Well, thank you both for response, but I'm not sure, I understand Oleg's
solution. This would work, but where is the variability of searched columns?
In your example, I create new indexed column with concatenated vectors of 2
columns. But I sometimes new to search only annotation, sometimes resume,
sometomes both.
if you assign different labels to the concatenated columns, you can
specify in query which columns you're interested in. Also, you
can explicitly specify weight=0 for columns you're not interested.
Oleg Bartunov wrote:
On Thu, 19 Mar 2009, esemba wrote:
Hi,
I have table with several columns and need to perform fulltext search
over
volatile number of columns.
I can't use multicolumn gist index or gin index over concatenated
columns,
so I've created several single column indexes (one for each column I want
to
search) and now I need to query them like this:
to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
coalesce(resume, '')) || ...
@@ to_tsquery('cs', 'Query text');
alter table YOURTABLE add columnt fts tsvector;
update YOURTABLE set fts=
to_tsvector('cs', coalesce(annotation, '')) ||
to_tsvector('cs', coalesce(resume, '')) || ...
create index fts_idx on YOURTABLE using gin(fts);
vacuum analyze YOURTABLE;
select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
This query works, but EXPLAIN has shown me, that postgres doesn't use the
indexes, so the query over a table with several thousands of records last
very long time. I've figured out, that indexes probably cannot be used
this
way. What is a recommendation for this scenario?
Indexes over static number of columns work fine, but I can't use them,
because in my application logic I want to let user choose which columns
to
search.
Thank you for your reply.
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Srf function : missing library on PostgreSQL 8.3.6 on Windows?
Thanks for your help. I try "extern C" for the inclusions and everything is OK.
But now I have execution error (server crash), but it's another problem that I
will try to solve.
--- En date de : Ven 20.3.09, Tom Lane a écrit :
De: Tom Lane
Objet: Re: [GENERAL] Srf function : missing library on PostgreSQL 8.3.6 on
Windows?
À: "Ben Ali Rachid"
Cc: pgsql-general@postgresql.org
Date: Vendredi 20 Mars 2009, 16h37
Ben Ali Rachid writes:
> I have this link error : "undefined reference to
> 'init_MultiFuncCall(FunctionCallInfoData*)'".
The fact that the linker knows the argument data type indicates that
you're trying to compile your code as C++. Postgres is written in C,
not C++.
You can try putting extern "C" { ... } around your inclusions of
Postgres header files, but you're likely to run into other problems
later on. On the whole I'd recommend using plain C for backend
functions if you possibly can.
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] Is there a meaningful benchmark?
Point taken. Thank you for the help. -Will -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 20 March 2009 12:06 To: Will Rutherdale (rutherw) Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Is there a meaningful benchmark? "Will Rutherdale (rutherw)" writes: > However, keeping the KISS principle in mind, you can create a benchmark > that simply sets up a sample database and forks off a bunch of processes > to do random updates for an hour, say. Dead simple. Indeed, and more than likely dead useless. The only benchmark that really counts is one's live application, which is probably not update-only and probably has a fairly non-random update pattern too. What people have been trying to point out to you is that you can certainly measure *something* with a benchmark test that has no thought behind it, but it's not clear whether the numbers you come up with will have any real-world value. 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] Is there a meaningful benchmark?
Thanks for the references. -Will -Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Paul Ramsey Sent: 20 March 2009 13:29 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Is there a meaningful benchmark? http://blog.redfin.com/devblog/2007/11/elephant_versus_dolphin_which_is_faster_which_is_smarter.html Here's another PgSQL/MySQL testimonial, with a spatial twist :) P On Fri, Mar 20, 2009 at 10:15 AM, Greg Smith wrote: > On Fri, 20 Mar 2009, Will Rutherdale (rutherw) wrote: > >> However, keeping the KISS principle in mind, you can create a benchmark >> that simply sets up a sample database and forks off a bunch of processes >> to do random updates for an hour, say. Dead simple. > > There's a benchmark tool that does something like this that comes with > PostgreSQL named pgbench. A MySQL-oriented tool named sysbench also can do > that, and it supports running against PostgreSQL as well--badly though, so > it's hard to use that to do a fair comparison. > > Simple benchmarks tend to measure only one thing though, and it's often not > what you think you're measuring. For example, pgbench produces a > transactions/per second number. It's useful for comparing the relative > performance between two PostgreSQL instances, and people think it gives you > an idea of transactional performance. What the actual magnitude of the > result measures in many cases is instead how well the generated data set > fits in cache. > > If you're doing something update heavy, a lot of the time what you actually > will measure is how fast your disk can seek, process a disk commit done > using fsync, or some combination of the two. If you're not careful to make > sure you're using the same level of disk commit guarantee on both > installations, it's real easy to get bad benchmark results here. The intro > to that subject from the PostgreSQL perspective is at > http://www.postgresql.org/docs/8.3/static/wal-reliability.html > > On MySQL, the parameters that controls this behavior are described starting > at > http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit > > For something with lots of disk commits, it's critical that you have both > systems configured identically here. > > -- > * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD > > -- > 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 -- 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] Maximum transaction rate
Ron Mayer wrote: > Marco Colombo wrote: >> Yes, but we knew it already, didn't we? It's always been like >> that, with IDE disks and write-back cache enabled, fsync just >> waits for the disk reporting completion and disks lie about > > I've looked hard, and I have yet to see a disk that lies. No, "lie" in the sense they report completion before the data hit the platters. Of course, that's the expected behaviour with write-back caches. > ext3, OTOH seems to lie. ext3 simply doesn't know, it interfaces with a block device, which does the caching (OS level) and the reordering (e.g. elevator algorithm). ext3 doesn't directly send commands to the disk, neither manages the OS cache. When software raid and device mapper come into play, you have "virtual" block devices built on top of other block devices. My home desktop has ext3 on top of a dm device (/dev/mapper/something, a LV set up by LVM in this case), on top of a raid1 device (/dev/mdX), on top of /dev/sdaX and /dev/sdbX, which, in a way, on their own are blocks device built on others, /dev/sda and /dev/sdb (you don't actually send commands to partitions, do you? although the mapping "sector offset relative to partition -> real sector on disk" is trivial). Each of these layers potentially caches writes and reorders them, it's the job of a block device, although it makes sense at most only for the last one, the one that controls the disk. Anyway there isn't much ext3 can do, but posting wb and flush requests to the block device at the top of the "stack". > IDE drives happily report whether they support write barriers > or not, which you can see with the command: > %hdparm -I /dev/hdf | grep FLUSH_CACHE_EXT Of course a write barrier is not a cache flush. A flush is synchronous, a write barrier asyncronous. The disk supports flushing, not write barriers. Well, technically if you can control the ordering of the requests, that's barriers proper. With SCSI you can, IIRC. But a cache flush is, well, a flush. > Linux kernels since 2005 or so check for this feature. It'll > happily tell you which of your devices don't support it. > %dmesg | grep 'disabling barriers' > JBD: barrier-based sync failed on md1 - disabling barriers > And for devices that do, it will happily send IDE FLUSH CACHE > commands to IDE drives that support the feature. At the same > time Linux kernels started sending the very similar. SCSI > SYNCHRONIZE CACHE commands. >> Anyway, it's the block device job to control disk caches. A >> filesystem is just a client to the block device, it posts a >> flush request, what happens depends on the block device code. >> The FS doesn't talk to disks directly. And a write barrier is >> not a flush request, is a "please do not reorder" request. >> On fsync(), ext3 issues a flush request to the block device, >> that's all it's expected to do. > > But AFAICT ext3 fsync() only tell the block device to > flush disk caches if the inode was changed. No, ext3 posts a write barrier request when the inode changes and it commits the journal, which is not a flush. [*] > Or, at least empirically if I modify a file and do > fsync(fd); on ext3 it does not wait until the disk > spun to where it's supposed to spin. But if I put > a couple fchmod()'s right before the fsync() it does. If you were right, and ext3 didn't wait, it would make no difference to have disk cache enabled or not, on fsync. My test shows a 50x speedup when turning the disk cache on. So for sure ext3 is waiting for the block device to report completion. It's the block device that - on flush - doesn't issue a FLUSH command to the disk. .TM. [*] A barrier ends up in a FLUSH for the disk, but it doesn't mean it's synchronous, like a real flush. Even journal updates done with barriers don't mean "hit the disk now", they just mean "keep order" when writing. If you turn off automatic page cache flushing and if you have zero memory pressure, a write request with a barrier may stay forever in the OS cache, at least in theory. Imagine you don't have bdflush and nothing reclaims resources: days of activity may stay in RAM, as far as write barriers are concerned. Now someone types 'sync' as root. The block device starts flushing dirty pages, reordering writes, but honoring barriers, that is, it reorders anything up to the first barrier, posts write requests to the disk, issues a FLUSH command then waits until the flush is completed. Then "consumes" the barrier, and starts processing writes, reordering them up to the next barrier, and so on. So yes, a barrier turns into a FLUSH command for the disk. But in this scenario, days have passed since the original write/barrier request from the filesystem. Compare with a fsync(). Even in the above scenario, a fsync() should end up in a FLUSH command to the disk, and wait for the request to complete, before awakening the process that issued it. So the filesystem has to request a flush operation to the block device, not a barrier. And so it does. I
Re: [GENERAL] What are the benefits of using a clustered index?
> Hopefully we regard it as a missing feature rather than as a separate > definition. We could cluster the index, we just don't, yet. Wouldn't this require keeping around multiple versions of index pages for MVCC? Which would create performance degradations elsewhere? -- Scott Ribe scott_r...@killerbytes.com http://www.killerbytes.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] postgreSQL & amazon ec2 cloud
Sanjay Arora wrote: > > Hello all > > Is it possible to host postgreSQL on Amazon's cloud? What are the issues > involved? > > With best regards. > Sanjay. > > You create postgres on EC2 in the same way you would on any Linux server. I created one on the Amazon-Fedora AMI about a year and half ago by uploading the binaries for postgres and compiling. No problems. I compiled to insure compatibility with the OS and processor. At the time, all of Amazon's AMIs were 32 bit even though the processors were 64 bit. I don't know if that has changed. You don't need S3 to run postgres, but S3 provides a convenient storage for backups and S3 is where you store your customized AMIs. My concerns at the time were the one spindle per processor limitation (since changed), no permanent IP (since changed) and concerns about losing the database if the processor or anything else hiccuped. The last concern has been addressed with the advent of Elastic Blocks. So we are about to try it all again and see if it will work for us. -Cheers -- View this message in context: http://www.nabble.com/postgreSQL---amazon-ec2-cloud-tp22302502p22626764.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Fulltext - multiple single column indexes
Thanks, this works quite fine, but I've ran into some problems so far:
- It's not possible to specify more than 4 labels (just ABCD)
- In query I have to specify searched vectors for each lexem. I think It
would be better to specify searched vectors per-query.
Oleg Bartunov wrote:
>
> On Fri, 20 Mar 2009, esemba wrote:
>
>>
>> Well, thank you both for response, but I'm not sure, I understand Oleg's
>> solution. This would work, but where is the variability of searched
>> columns?
>> In your example, I create new indexed column with concatenated vectors of
>> 2
>> columns. But I sometimes new to search only annotation, sometimes resume,
>> sometomes both.
>
>
> if you assign different labels to the concatenated columns, you can
> specify in query which columns you're interested in. Also, you
> can explicitly specify weight=0 for columns you're not interested.
>
>>
>>
>> Oleg Bartunov wrote:
>>>
>>> On Thu, 19 Mar 2009, esemba wrote:
>>>
Hi,
I have table with several columns and need to perform fulltext search
over
volatile number of columns.
I can't use multicolumn gist index or gin index over concatenated
columns,
so I've created several single column indexes (one for each column I
want
to
search) and now I need to query them like this:
to_tsvector('cs', coalesce(annotation, '')) || to_tsvector('cs',
coalesce(resume, '')) || ...
@@ to_tsquery('cs', 'Query text');
>>>
>>> alter table YOURTABLE add columnt fts tsvector;
>>> update YOURTABLE set fts=
>>> to_tsvector('cs', coalesce(annotation, '')) ||
>>> to_tsvector('cs', coalesce(resume, '')) || ...
>>> create index fts_idx on YOURTABLE using gin(fts);
>>> vacuum analyze YOURTABLE;
>>> select * from YOURTABLE where to_tsquery('cs', 'Query text') @@ fts;
>>>
>>>
This query works, but EXPLAIN has shown me, that postgres doesn't use
the
indexes, so the query over a table with several thousands of records
last
very long time. I've figured out, that indexes probably cannot be used
this
way. What is a recommendation for this scenario?
Indexes over static number of columns work fine, but I can't use them,
because in my application logic I want to let user choose which columns
to
search.
Thank you for your reply.
>>>
>>> Regards,
>>> Oleg
>>> _
>>> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
>>> Sternberg Astronomical Institute, Moscow University, Russia
>>> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
>>> phone: +007(495)939-16-83, +007(495)939-23-83
>>>
>>> --
>>> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-general
>>>
>>>
>>
>>
>
> Regards,
> Oleg
> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
>
--
View this message in context:
http://www.nabble.com/Fulltext---multiple-single-column-indexes-tp22611952p22627255.html
Sent from the PostgreSQL - general mailing list archive at Nabble.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] SRID conflict, PostgreSQL 8.3
This should be addressed to the Postgis list.
However, you are spatially joining two geometries, and they need be in the same
coordinate system.
The column "the_geom" has a defined SRID (spatial reference id) when created in
it's original table. Your hard coded POLYGON in the SQL below has a SRID of -1
(the last value in the argument).
You can fix this by either changing the -1 to be the same number as the SRID
specified for the_geom, or by setting this arbitrarily to -1 for this operation
(as below):
SELECT "whatever"
FROM "a view into 'catalog'"
WHERE ((TRUE AND TRUE) AND SetSrid("the_geom", -1) && GeometryFromText('POLYGON
((-83.28 26.07,
-83.28 28.26,
-81.59 28.26,
-81.59 26.07,
-83.28 26.07))', -1));
(This assumes that "the_geom" & the coordinates you specify in the query are in
fact in the same coordinate system
Note that SRID of -1 means unknown coordinate system.)
I'm not sure of the relevance of the (TRUE AND TRUE) in the where clause, it
seems redundant, as it will always return true.
Cheers,
Brent Wood
Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> csmith 03/21/09 8:57 AM >>>
Hello,
I serve a Geospatial IS project that for some years has used PostgreSQL
and PostGIS. A strategy/scheme that has worked for all previous
versions has failed with a recent upgrade to 8.3 (e.g. 8.3.6-1).
Relation "catalog" is a large geospatially-enabled aggregation of data
with a variety of SRID's imbedded within "the_geom" attribute values.
Querying a view into the "catalog" which describes a subset of it's
tuples with identical SRID's (the uniqueness of the SRID associated with
this view's data is easily demonstrable with an ad-hoc query) has always
worked smoothly. With the 8.3 engine, an error is introduced:
SELECT "whatever"
FROM "a view into 'catalog'"
WHERE ((TRUE AND TRUE) AND "the_geom" && GeometryFromText('POLYGON
((-83.28 26.07,
-83.28 28.26,
-81.59 28.26,
-81.59 26.07,
-83.28 26.07))', -1))
results in this error:
"Operation on two geometries with different SRIDs"
The result of the GeometryFromText routine has, of course, but one SRID,
thus the SRID from "the_geom" must be the culprit. It's as if the
query is considering tuples in "catalog" outside of the view's domain.
(note: I can offer further evidence of this behavior- removing all tuples
from "catalog" other than those returned by a query against the view
eliminates the conflict/error).
Can someone comment on this mystery/phenomenon vis-a-vis PostgreSQL
version 8.3 (PostGIS 1.3.5)?
Many thanks,
Christopher Smith
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
NIWA is the trading name of the National Institute of Water & Atmospheric
Research Ltd.
--
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] SRID conflict, PostgreSQL 8.3
csmith writes: > ... It's as if the > query is considering tuples in "catalog" outside of the view's domain. This isn't particularly surprising: the planner will feel free to push that WHERE condition down as far as it can. Probably there is some aspect of the view definition that prevented such an optimization in prior versions but doesn't deter 8.3. You didn't show us the view though, nor any EXPLAIN output ... 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
[GENERAL] Defer a functional index calculation?
Hi folks, I am holding hierarchical data in a table and often need to calculate the "root" of a hierarchy. Initially, a recursive plpgsql function worked just fine. But performance started to lag when I got to scale. So, I added a functional index. create table example (id serial primary key, stuff text, parent_id int); create index example_root_idx on example (get_root_id(id)); (get_root_id(id) pulls an example row and recurses onto parent_id until it hits a root) This works fine for speeding up access to the existing data, but breaks for inserting new rows because get_root_id(id) can't find the new row. It looks like the index is getting calculated, and the function called, before the row becomes visible to the function. Is there a way to set a functional index to be deferred (like for FKs), or calculated on an AFTER INSERT ON basis (like with triggers), or similar? If not, my backup plan is to precalculate get_root_id via a trigger and store it in a column, but that kind of smells (even worse than my current schema, I know). Thank you, Randall
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
You would get better results if you posted in mysql forums. http://forums.mysql.com/ Amitabh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] (odbc) multiple step ole db generated error - date/timestamp column
when importing from oracle 10g, i get "multiple step ole db generated error". i narrowed this down to a date/timestamp column - actually to about 100 rows within that column, all = '01-JAN-01' (never null). there is another date/timestamp column that gets imported error-free, and other tables also get imported fine, so i am stumped? query log not very helpful. using 8.1 with navicat. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
