Re: [GENERAL] variable in COPY TO variable
Rob Shepherd wrote: > I'm following the synatx > > COPY TO 'filename' > > .but i'm trying to use a variable as the filename. > [...] > > returns the error > > ERROR: syntax error at or near "$1" at character 21 > QUERY: COPY tt_archive TO $1 > CONTEXT: SQL statement in PL/PgSQL function "archive" near line 16 A case for dynamic SQL! http://www.postgresql.org/docs/8.2/static/plpgsql-statements.html#PLPGSQ L-STATEMENTS-EXECUTING-DYN Yours, Laurenz Albe ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.3 beta problems
Tom Lane pisze: I'm wondering if you have an example that doesn't work well with the gist page-splitting heuristic changes that Teodor put in awhile back. Those are all in 8.2.5 already, though you might have to REINDEX to really notice a change if the index was first built with an older 8.2.x release. Can you provide sample data illustrating the problem? I've checked how 8.3beta will perform on windows machine and it seems that there is no problem with gist index - it means that that my problems are related only to linux machine, where 8.3beta is working. Is it possible, that 8.3beta compilation went wrong or maybe there are other issues that may have influence on the performance ? I will add, that on the same machine there was 8.2.5 installed and everything was working just fine. Regards, ML ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit
On Wed, Oct 17, 2007 at 02:40:14AM -0400, Tom Lane wrote: > "Magnus Hagander" <[EMAIL PROTECTED]> writes: > >> - Max_connections is set to 500. > > > There's your problem. 500 is way above what the windows version can > > handle. IIRC the hard max is somewhere around 200 depending on some OS > > factors that we don't entirely know. > > Maybe we should put an #ifdef WIN32 into guc.c to limit max_connections > to something we know the platform can stand? It'd be more comfortable > if we understood exactly where the limit was, but I think I'd rather > have an "I'm sorry Dave, I can't do that" than random-seeming crashes. Yeayh, that's probably a good idea - except we never managed to figure out where the limit is. It appears to vary pretty wildly between different machines, for reasons we don't really know why (total RAM has some effect on it, but that's not the only one, for example) //Magnus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] newbie question
Hi, In the mid 1990's I used the Progress Database which had a great feature where the it was possible to set the software version in the configuration file. For example, if you had a built a database and application using version 3 and then upgraded the version 7, it was possible to set the database configuration to version 3 and then continue to use the same database and applications without having to dump the database tables and data then import them into a version 7 database or update applications. Does this feature exist in ProgreSQL, can a v8 access a database created using v7? -- Regards Russell ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Possibilities of Fine Grained Access Control?
I need a solution for the following: With all data stored in the same schema, consumers may only retreive and update data that is relevant to them. At the same time, users in our own company need to see all data. Is a solution similar to Oracle's Virtual Private Database possible with PostgreSQL, as this is precisely what we need? thanks, Uden Documentation: http://www.oracle.com/technology/pub/articles/10gdba/week14_10gdba.html http://www.databasesecurity.com/oracle-vpd.htm ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Index Usage
On Tue, 2007-10-16 at 15:51 -0700, Ben wrote: > You could take a look at pg_statio_user_indexes and/or > pg_stat_user_indexes, if you have stats enabled > > On Tue, 16 Oct 2007, Bryan Murphy wrote: If your intention is to eliminate the unused indexes rows you should run 'vaccum' and/or 'vacuum full' and/or 'reindex'. This also has the consequence of freing filesystem space and returning it back to the OS. Check it out here: http://www.postgresql.org/docs/8.1/static/maintenance.html chapters 22.1, 22.2 and 22.3 I use: VACUUM FULL ANALYZE; REINDEX INDEX yourIndex; REINDEX TABLE yourTable it works just great for me. Cheers joao > > > Is there a way I can track index usage over a long period of time? > > Specifically, I'd like to identify indexes that aren't being regularly > > used and drop them. > > > > Bryan > > > > ---(end of broadcast)--- > > TIP 2: Don't 'kill -9' the postmaster > > > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] replicating to a stopped server
On Fri, 2007-10-12 at 19:59 +0100, Richard Huxton wrote: > You could use WAL archiving, but you'll want a fairly regular > full-backup of PG's files otherwise the recovery could take a long time. > See Ch 23 of the manuals for details. Restartable recovery avoids the need to re-sync from the primary as often as was the case with 8.1 and prior. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] newbie question
>-Original Message- >From: [EMAIL PROTECTED] >[mailto:[EMAIL PROTECTED] On Behalf Of >Russell Aspinwall >Sent: woensdag 17 oktober 2007 9:34 >To: pgsql-general@postgresql.org >Subject: [GENERAL] newbie question > >Hi, > >[snip] For example, if you had a >built a database and application using version 3 and then >upgraded the version 7, it was possible to set the database >configuration to version 3 and then continue to use the same >database and applications without having to dump the database >tables and data then import them into a version 7 database or >update applications. >Does this feature exist in ProgreSQL, can a v8 access a >database created using v7? No it cannot. You must perform a dump and restore. Also note that between different architectures (and sometimes between different compiles) the file format might also be different. See also: http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html. However all queries running on v7 should work on v8. The application should not require any modifications. In practice you should, of course, test that before putting it into production. - Joris ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Calculation of per Capita on-the-fly - problems with SQL syntax
On Wed, Oct 17, 2007 at 08:29:58AM +0200, Stefan Schwarzer wrote: > Hmmm Actually, my intention was to get a more "excel" like output, that > is the formatting into year-columns. This eases a lot the PHP/HTML > display/loop. Otherwise I would have to start to do some joggling inside > PHP to get it that way When you say "excel-like output" do you mean some sort of cross- tabulated output? i.e. instead of n y v x 01 5 x 02 7 y 01 3 z 02 4 you want: n 01 02 x 5 7 y 3 z 4 or something like that? If you do then I tend to get that effect by doing something like: SELECT n, SUM(CASE WHEN y = 01 THEN v END) AS y01, SUM(CASE WHEN y = 02 THEN v END) AS y02 FROM table GROUP BY n ORDER BY n; This gets a bit fiddly if you're doing lots of columns/categories, but gives lots of flexibility with the columns you produce. There's a subdirectory in the contrib directory called "tablefunc" that provides some code to do crosstabs if all you want to do is something simple. If you're doing it by hand, you have the advantage of being able to have something like: SUM(v) AS total at the end, or maybe a mean (AVG) or standard deviation (STDDEV) if it makes sense to do anything like that. Sam ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] CVS Commands
Hi All, The ls and rls commands are used to list files and directories in the repository, but we should be logged in to the repository for getting the listing of the same. I use the command "cvs login" to connect to the repository but instead of connecting to the local repository it is connecting to the postgreSQL repository, but when I specify the command -> cvs -d :pserver:[EMAIL PROTECTED]:/u01/Installs/cvsrepository/cvsroot login where /u01/Installs/cvsrepository/cvsroot is the repository path, root is the username and inblr-kuriakcc.eu.uis.unisys.com is the hostname it is asking for the password, now I am not sure what is the password, can anyone please tell me the method to change the CVS password, so that I can change the password and get connected to the local repository. Thanks in Advance Cinu ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] insert continue on error
On Tue, Oct 16, 2007 at 03:37:46PM -0400, Tom Hart wrote: > I'm trying to migrate our data mine from a MS Access backend to a > postgresql backend, and I'm bumping into problems with the conversion. When I've had to do this before, I've tended to create a table with the same columns but all of text type. I can then import into there without worry too much about errors. I'd then write a script to do the movement across into the final tables. If the access database is still live then you can spend a while getting the script right, then at some appropriate time you can just quickly move everything over. Sam ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] DROP VIEW lasts forever
Hi list, Whenever I try dropping a view using DROP VIEW myschema.myview the query runs forever. The last time I tried was ten minutes ago and the query is still running even though the view queries just data of one single table with around 5 fields. What could be the reason? By the way, I'm using psql 8.2 Thanks in advance Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DROP VIEW lasts forever
Nevermind, by mistake I tried dropping it as a different user. But, just a question, wouldn't it make more sense to issue an error message or something instead of letting the query run indefinitely? Christian Rengstl M.A. Klinik und Poliklinik für Innere Medizin II Kardiologie - Forschung Universitätsklinikum Regensburg B3 1.388 Franz-Josef-Strauss-Allee 11 93053 Regensburg Tel.: +49-941-944-7230 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pg_cancel_backend() does not work with buzz queries
2007/10/3, Alvaro Herrera <[EMAIL PROTECTED]>: > Sergey Konoplev escribió: > > > > AFAIK, pl/pgsql does listen for SIGINT during execution, but I don't nkow > > > abuot plpython. > > > > How can we find it out? > > Let's see one of the functions to find out if anyone else can reproduce > the problem. > It happens with simple queries also. For example: select * from ( select d.*, cto.full_name, cast(st.name || ', ' || r.name as varchar) as cityname from drivers d join cars_trailers_owners cto on d.cars_trailers_owner_id = cto.id join settles st on d.settle_id = st.id join regions r on st.region_id = r.id order by sname, name, pname ) as sq -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] pg_cancel_backend() does not work with buzz queries
2007/10/3, Erik Jones <[EMAIL PROTECTED]>: > On Oct 3, 2007, at 6:47 AM, Richard Huxton wrote: > > > Sergey Konoplev wrote: > >>> Don't forget to cc: the list. > >>> Try not to top-post replies, it's easier to read if you reply > >>> below the > >>> text you're replying to. > >> Thanx for your advice. I'm just absolutely worned out. Sorry. > > > > Know that feeling - let's see if we can't sort this out. > > > > 1. Is it always the same query? > > 2. Does the client still think it's connected? > > 3. Is that query using up CPU, or just idling? > > 4. Anything odd in pg_locks for the problem pid? > 1. No it isn't. I have few functions (plpgsql, plpython) that cause > such situations more often than another but they are called more > often > also. > >>> OK, so there's no real pattern. That would suggest it's not a > >>> particular > >>> query-plan that's got something wrong. > >>> > >>> Do you always get this problem inside a function? > >> As far as I remember I do. > > > > Hmm - check Magnus' thoughts on pl/python. Can't comment on Python > > myself. Are you sure it's not always the same few function(s) that > > cause this problem? > > > 2. The client just waits for query and buzz. > 3. They are using CPU in usual way and their pg_lock activity > seems normal. > >>> So the backend that appears "stuck" is still using CPU? > >> Yes but the metter is that this procedures usualy use CPU just a > >> little so I can't find out if there is some oddity or not. > > > > OK, so it's not that it's stuck in a loop wasting a lot of CPU > > In order to get at least some idea of what these processes are (or, > are not) doing, run an strace (or your OS's equivalent) on the > process before killing it. Let us know what you see there. > That is what I've got using strace with the buzzed process: pgdb:~ # strace -dirfvx -p 19313 Process 19313 attached - interrupt to quit [wait(0x137f) = 19313] pid 19313 stopped, [SIGSTOP] [wait(0x57f) = 19313] pid 19313 stopped, [SIGTRAP] 0.00 [e410] send(8, "\x00\x01\x74\xff\xff\xff\xff\x00\x00\x00\x01\x66\xff\xff"..., 8192, 0 [Output stoped here and after half hour I interupted strace] cleanup: looking at pid 19313 Process 19313 detached pgdb:~ # -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] dblink and hostname resolution problem
Hi list!
I am using DBLink on a Win32 system running PostgreSQL 8.2 to query a table on
another similar system (Win32 / PostgreSQL 8.2).
If I query the server with dblink('hostaddr=ip_address ...', ...), it works
fine, but if I try to use the hostname instead
(dblink('hostaddr=myserver.mydomain.com ...', ...)), I have an error :
ERROR: could not establish connection
DETAIL: could not translate host name "myserver.mydomain.com" to address:
Unknown server error
Both server can resolve this address though.
Is this a known issue ? Is there something I can do about it ?
Thansk a lot !
Regards
--
Arnaud
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_cancel_backend() does not work with buzz queries
Hello again, Sorry for the deal with my answer it was realy hectic week so I couldn't even check my mail. 2007/10/3, Richard Huxton <[EMAIL PROTECTED]>: > Sergey Konoplev wrote: > >> Don't forget to cc: the list. > >> Try not to top-post replies, it's easier to read if you reply below the > >> text you're replying to. > > > > Thanx for your advice. I'm just absolutely worned out. Sorry. > > Know that feeling - let's see if we can't sort this out. > > 1. Is it always the same query? > 2. Does the client still think it's connected? > 3. Is that query using up CPU, or just idling? > 4. Anything odd in pg_locks for the problem pid? > >>> 1. No it isn't. I have few functions (plpgsql, plpython) that cause > >>> such situations more often than another but they are called more often > >>> also. > >> OK, so there's no real pattern. That would suggest it's not a particular > >> query-plan that's got something wrong. > >> > >> Do you always get this problem inside a function? > > > > As far as I remember I do. > > Hmm - check Magnus' thoughts on pl/python. Can't comment on Python > myself. Are you sure it's not always the same few function(s) that cause > this problem? Yes I'm shure. I've noticed about 10 queries and procedure calls buzzing at least. > > >>> 2. The client just waits for query and buzz. > >>> 3. They are using CPU in usual way and their pg_lock activity seems > >>> normal. > >> So the backend that appears "stuck" is still using CPU? > > > > Yes but the metter is that this procedures usualy use CPU just a > > little so I can't find out if there is some oddity or not. > > OK, so it's not that it's stuck in a loop wasting a lot of CPU > > >> So - the symptoms are: > [snip] > > Exactly. > > So - we need to solve two mysteries > 1. Why are these functions not returning? > 2. Why does SIGINT not interrupt them? > > >> Are you happy that your hardware and drivers are OK? There aren't > >> problems with any other servers on this machine? > > > > Yes I'm quite happy. My hardware is: 2 double-core Xeon, 8Gb RAM, > > RAID5. What about other software... it's dedicated PG server so I have > > no problem with it. > > Well, the places I'd look would be: > 1. Hardware (you're happy that's fine, and it's not quite the problems > I'd expect) > 2. Drivers (same as #1) > 3. Client connectivity (but you say the client is fine) > 4. External interactions (see below) > 5. Bug in PG extension (pl/python) I think it's not only lp/python problem cos I saw pl/pgsql and simple queries also. For example: select * from ( select d.*, cto.full_name, cast(st.name || ', ' || r.name as varchar) as cityname from drivers d join cars_trailers_owners cto on d.cars_trailers_owner_id = cto.id join settles st on d.settle_id = st.id join regions r on st.region_id = r.id order by sname, name, pname ) as sq > 6. Bug in PG core code > > Do any of your functions interact with the outside world - fetch > webpages or similar? It could be they're waiting for that. If you're > using a library that could hang waiting for a response and also block > SIGINT at the same time that would explain everything. No, most of them don't. -- Regards, Sergey Konoplev ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] DROP VIEW lasts forever
am Wed, dem 17.10.2007, um 13:01:25 +0200 mailte Christian Rengstl folgendes: > Hi list, > > Whenever I try dropping a view using DROP VIEW myschema.myview the > query runs forever. The last time I tried was ten minutes ago and the > query is still running even though the view queries just data of one > single table with around 5 fields. > What could be the reason? maybe a look because an other transaction? Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] atomic commit;begin for long running transactions , in combination with savepoint.
my understanding was it used more resources than read committed because it keeps track of the version id of rows selected so far in a transaction, "transaction-level consistency", so it has the potential to do the xmin co-selecting , and checking, if it were a transaction isolation level in postgres. google found my reference, and the reference mentioned it was different from serializable. On Mon Oct 15 9:09 , "Trevor Talbot" sent: >On 10/15/07, Syan Tan wrote: > >> >Also keep in mind that MVCC is not the only way to implement >> >transactions; pure locking is more common in other databases. In the >> >locking model, most transactions prevent others from writing until >> >after they are finished. Rows simply can't have different versions >> >(and of course concurrent performance is awful). >> >> what about postgresql doing something like snapshot isolation level as per >> the enemy M$ ? > >SQL Server is normally a pure locking database; from what I can tell, >its snapshot isolation level adds a limited form of MVCC above that, >making its concurrent behavior closer to PostgreSQL's: >http://msdn2.microsoft.com/en-us/library/ms345124\(d=printer\).aspx ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Possibilities of Fine Grained Access Control?
In response to Uden van Hout <[EMAIL PROTECTED]>: > I need a solution for the following: With all data stored in the same > schema, consumers may only retreive and update data that is relevant to > them. At the same time, users in our own company need to see all data. > > Is a solution similar to Oracle's Virtual Private Database possible with > PostgreSQL, as this is precisely what we need? Not familiar with Oracle's solution, but ... Without knowing the details, it's difficult to be sure if PostgreSQL's native security meets your needs. You can assign read/write/create permissions to databases, schemas, tables, and other objects: http://www.postgresql.org/docs/8.2/static/sql-grant.html This falls short if you need permissions at the row or column level, which PG doesn't support naively (unless this has been added in 8.3 and I simply haven't see the announcement). For that, the best approach I know for you is Veil: http://veil.projects.postgresql.org/curdocs/index.html -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Index Usage
On 10/17/07, Joao Miguel Ferreira <[EMAIL PROTECTED]> wrote: > If your intention is to eliminate the unused indexes rows you should run > 'vaccum' and/or 'vacuum full' and/or 'reindex'. > > This also has the consequence of freing filesystem space and returning > it back to the OS. > > Check it out here: > > http://www.postgresql.org/docs/8.1/static/maintenance.html > > chapters 22.1, 22.2 and 22.3 > > I use: > > VACUUM FULL ANALYZE; > REINDEX INDEX yourIndex; > REINDEX TABLE yourTable > > it works just great for me. > > Cheers > joao That's not my intention at all. My intention is to justify the validity of each index in our database. Some indexes have snuck in that I find of questionable value, and I want the data to backup my intuition. Anyway, I'll look into the pg_stat* tables and see if those give me the data I want. Thanks for the advice guys! Bryan ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] newbie question
>-Original Message- >From: Russell Aspinwall [mailto:[EMAIL PROTECTED] >Sent: woensdag 17 oktober 2007 11:37 >To: Joris Dobbelsteen >Subject: Re: [GENERAL] newbie question > >Joris Dobbelsteen wrote: >>> -Original Message- >>> From: [EMAIL PROTECTED] >>> [mailto:[EMAIL PROTECTED] On Behalf Of Russell >>> Aspinwall >>> Sent: woensdag 17 oktober 2007 9:34 >>> To: pgsql-general@postgresql.org >>> Subject: [GENERAL] newbie question >>> >>> Hi, >>> >>> [snip] For example, if you had a >>> built a database and application using version 3 and then upgraded >>> the version 7, it was possible to set the database configuration to >>> version 3 and then continue to use the same database and >applications >>> without having to dump the database tables and data then >import them >>> into a version 7 database or update applications. >>> Does this feature exist in ProgreSQL, can a v8 access a database >>> created using v7? >>> >> >> No it cannot. You must perform a dump and restore. >> >> Also note that between different architectures (and >sometimes between >> different compiles) the file format might also be different. >> See also: >> >http://www.postgresql.org/docs/8.1/interactive/install-upgrading.html. >> >> However all queries running on v7 should work on v8. The application >> should not require any modifications. In practice you should, of >> course, test that before putting it into production. > >Thank you for the reply, could this feature be added in future? > That's not a question for me, but rather for the people who spend a lot of time creating this excellent database server. Nevertheless, given that it: * significantly complicates the software * might hinder (some) new features to be implemented * there is little demand, hence nobody is willing to spend the incredible amount of time on programming & maintenance * Binary format already differs between different processor architectures/compliations * the SQL interface itself already shouldn't change (so from the application point of view) I would say that such a feature is highly unlikely that it will be added in the foreseeable future. A more likely feature is inplace/live (whatever you call it) upgrades of the data to a newer version. - Joris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] [HACKERS] CVS and Eclipse
>>> On Wed, Oct 17, 2007 at 1:40 AM, in message <[EMAIL PROTECTED]>, "Kuriakose, Cinu Cheriyamoozhiyil" <[EMAIL PROTECTED]> wrote: > > I am trying to configure CVS through Eclipse, infact i was able to do that > but when I map the postgreSQL code into CVS through Eclipse, it is displaying > the folders but the files in those folders are not getting displayed. If you look at the subdirectories in your workspace, are the files there? -Kevin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Prepared statement parameters for an 'IN ()' clause
Can someone point me to an example of creating a prepared statement for
a query with an 'IN' clause?
The query looks like this:
select value from table where
state = $1 and city = $2 and zip = $3 and
date in ( $4 );
For the prepared statement, I have tried:
prepare st1(text, text, text, text[] );
Then invoked it as:
execute st1('CA', 'SUNNYVALE', '94086',
'{2007-10-01,2007-09-25,2007-09-15}' );
But the use of the text array as input parameter does not seem to be
correctly used in the 'IN' clause. My query consistently returns no results.
Any quick syntax fix for this?
Thanks
Jason
Re: [GENERAL] What encoding to use for English, French, Spanish
Peter Eisentraut escribió: > novnov wrote: > > My project is currently SQL_ASCII encoded. I will need to accomodate > > both French and Spanish in addition to English. I don't anticipate > > needing Far East languages. Reading here on the forums I come up with > > Latin9 as perhaps adequate. But others recommend unicode for > > relatively simple needs like my own. > > LATIN9 or UTF-8 are the appropriate choices for your project. The > choice between these is mostly a matter of taste, unless there are > additional requirements in the project. I used to think that there was no practical difference between using LATIN9 or UTF8, but experience (not my own, but those from people in the pgsql-es-ayuda list) has told me otherwise. When people start mixing environments, it is quite common that they get the client_encoding wrong in some cases. In those cases, having an encoding able to tell a valid string from an invalid one is really helpful -- thus using UTF8 as the server encoding is the way to go. Latin9 is _capable_ of storing your data, yes, but if you fail to set client_encoding then it is also capable of storing something you don't really want to store. I'd stay away from it. -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "Los románticos son seres que mueren de deseos de vida" ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Array intersection
Hi,
Is it possible to find the intersection of two array values?
a = '{1,2,3}'
b = '{2,3,4}'
a intersect b = '{2,3}'
Assume I need to write a pl/pgsql function to do this.
Josh
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Prepared statement parameters for an 'IN ()' clause
On Wed, Oct 17, 2007 at 07:37:15AM -0700, Jason L. Buberel wrote: > Can someone point me to an example of creating a prepared statement for a > query with an 'IN' clause? > > The query looks like this: > > select value from table where > state = $1 and city = $2 and zip = $3 and > date in ( $4 ); You're asking here whether "date" matches a single item named by parameter "4". > Any quick syntax fix for this? Try: date = ANY ($4); "ANY" treats its parameter as an array, so will accept an array as a parameter. I've not tried this on a large dataset, so I'm not sure what sort of performance characteristics you should expect. Sam ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 10:19:43 -0500
Josh Trutwin <[EMAIL PROTECTED]> wrote:
> Hi,
>
> Is it possible to find the intersection of two array values?
>
> a = '{1,2,3}'
> b = '{2,3,4}'
>
> a intersect b = '{2,3}'
>
> Assume I need to write a pl/pgsql function to do this.
nm - I just wrote a function - though curious if this is the most
effecient way:
CREATE OR REPLACE FUNCTION array_has_intersect (array1 INTEGER[],
array2 INTEGER[]) RETURNS BOOLEAN
AS $$
BEGIN
IF array1 IS NULL OR array2 IS NULL THEN
RETURN FALSE;
END IF;
FOR i IN ARRAY_LOWER(array1,1) .. ARRAY_UPPER(array1,1) LOOP
FOR j IN ARRAY_LOWER(array2,1) .. ARRAY_UPPER(array2,1) LOOP
IF (array1[i] = array2[j]) THEN
RETURN TRUE;
END IF;
END LOOP;
END LOOP;
RETURN FALSE;
END;
$$ LANGUAGE PLPGSQL;
psql=> select array_has_intersect('{1,2,3}', '{1,3,4}');
array_has_intersect
-
t
psql=> select array_has_intersect('{1,2,3}', '{21,23,24}');
array_has_intersect
-
f
It doesn't return the actual intersection, but could easily be
modified to do so.
Josh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Prepared statement parameters for an 'IN ()' clause
Jason L. Buberel wrote:
> Can someone point me to an example of creating a prepared
> statement for a query with an 'IN' clause?
>
> The query looks like this:
>
> select value from table where
> state = $1 and city = $2 and zip = $3 and
> date in ( $4 );
>
> For the prepared statement, I have tried:
>
> prepare st1(text, text, text, text[] );
>
> Then invoked it as:
>
> execute st1('CA', 'SUNNYVALE', '94086',
> '{2007-10-01,2007-09-25,2007-09-15}' );
>
> But the use of the text array as input parameter does not
> seem to be correctly used in the 'IN' clause. My query
> consistently returns no results.
Two things:
a) the fourth parameter of the function should be declared as
date[] and not as text[].
b) use =ANY instead of IN
Example:
CREATE TABLE t (id serial PRIMARY KEY, datum date);
INSERT INTO t (datum)
VALUES ('2000-01-01'), ('2001-01-01'), ('2002-01-01');
CREATE FUNCTION f(date[]) RETURNS SETOF integer
LANGUAGE sql STABLE STRICT AS
$$SELECT id FROM t WHERE datum =ANY ($1)$$;
SELECT * FROM f('{2001-01-01,2006-01-01}');
f
---
2
(1 row)
Yours,
Laurenz Albe
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] DROP VIEW lasts forever
[EMAIL PROTECTED] ("Christian Rengstl") writes:
> Whenever I try dropping a view using DROP VIEW myschema.myview the
> query runs forever. The last time I tried was ten minutes ago and the
> query is still running even though the view queries just data of one
> single table with around 5 fields.
> What could be the reason?
>
> By the way, I'm using psql 8.2
Chances are that the problem was that some connection had a lock on
the VIEW.
As a rule, when something "takes forever" when there is no reasonable
reason for it to be expected to run for a long time (e.g. - where
you're doing a cross join of two tables that each have a billion
tuples), it is entirely likely that you have run afoul of some locks,
and you can expect that NOTHING will happen until such time as
whatever is holding onto the locks lets go of them.
--
output = reverse("gro.mca" "@" "enworbbc")
http://cbbrowne.com/info/nonrdbms.html
"Unlike computers, guns don't have Y2K problems..."
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Array intersection
On Wed, Oct 17, 2007 at 10:37:23AM -0500, Josh Trutwin wrote:
> On Wed, 17 Oct 2007 10:19:43 -0500
> Josh Trutwin <[EMAIL PROTECTED]> wrote:
>
> > Hi,
> >
> > Is it possible to find the intersection of two array values?
> >
> > a = '{1,2,3}'
> > b = '{2,3,4}'
> >
> > a intersect b = '{2,3}'
> >
> > Assume I need to write a pl/pgsql function to do this.
>
> nm - I just wrote a function - though curious if this is the most
> effecient way:
>
>
> CREATE OR REPLACE FUNCTION array_has_intersect (array1 INTEGER[],
> array2 INTEGER[]) RETURNS BOOLEAN
> AS $$
>BEGIN
> IF array1 IS NULL OR array2 IS NULL THEN
> RETURN FALSE;
> END IF;
>
> FOR i IN ARRAY_LOWER(array1,1) .. ARRAY_UPPER(array1,1) LOOP
> FOR j IN ARRAY_LOWER(array2,1) .. ARRAY_UPPER(array2,1) LOOP
> IF (array1[i] = array2[j]) THEN
>RETURN TRUE;
> END IF;
> END LOOP;
> END LOOP;
>
> RETURN FALSE;
>END;
> $$ LANGUAGE PLPGSQL;
This is only going to work for one-dimensional arrays (I'm not sure how
you would ever fix that with the support postgres has for arrays) but
the (computational) complexity of having an embedded FOR loops looks bad
for performance. As you can already use '=ANY' syntax to search inside
an array, you may as well use that---it's probably a bit more faster
than the plpgsql work-alike. Leading to the following implementation of
intersect:
CREATE OR REPLACE FUNCTION array_intersect (array1 INTEGER[],array2
INTEGER[]) RETURNS INTEGER[]
AS $$
DECLARE
out INTEGER[];
BEGIN
IF array1 IS NULL OR array2 IS NULL THEN
RETURN '[]';
END IF;
FOR i IN ARRAY_LOWER(array1,1) .. ARRAY_UPPER(array1,1) LOOP
IF (array1[i] =ANY (array2)) THEN
out := array_append(out,array1[i]);
END IF;
END LOOP;
RETURN out;
END;
$$ LANGUAGE PLPGSQL;
It seems to work for me, but as a side effect will leave the array sorted
in the same order as the first parameter and with any duplicates it has.
Even more annoyingly if there is no intersection it will return NULL
instead of an empty array, how do I fix this?
Sam
p.s. plpgsql is badly in need of parametric polymorphism, the silly
anyarray/anyelement it's got at the moment means it's impossible to
declare an array of the same type as the type of its parameters.
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Array intersection
On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> nm - I just wrote a function - though curious if this is the most
> effecient way:
If you only want TRUE or FALSE, you can use '&&':
t=# SELECT '{1,2}'::INT[] && '{2,3}'::INT[];
?column?
--
t
(1 row)
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 11:12:27 -0500
"Rodrigo De León" <[EMAIL PROTECTED]> wrote:
> On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > nm - I just wrote a function - though curious if this is the most
> > effecient way:
>
> If you only want TRUE or FALSE, you can use '&&':
>
> t=# SELECT '{1,2}'::INT[] && '{2,3}'::INT[];
> ?column?
> --
> t
> (1 row)
Is that 8.2 or above? I'm on 8.1.10:
psql=> SELECT '{1,2}'::INT[] && '{2,3}'::INT[];
ERROR: operator does not exist: integer[] && integer[]
HINT: No operator matches the given name and argument type(s). You
may need to add explicit type casts.
Josh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Will UPDATE lock if FROM refers to target table?
On Mon, Oct 15, 2007 at 03:23:59PM -0400, Carlo Stonebanks wrote: > Would someone be kind enough to tell me if there is somethign wrong with > this apporach: Can't see anything obviously wrong, but if you want more help you'll need to provide the EXPLAIN output. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Array intersection
> This is only going to work for one-dimensional arrays (I'm not sure
> how you would ever fix that with the support postgres has for
> arrays) but the (computational) complexity of having an embedded
> FOR loops looks bad for performance. As you can already use '=ANY'
> syntax to search inside an array, you may as well use that---it's
> probably a bit more faster than the plpgsql work-alike. Leading to
> the following implementation of intersect:
Thanks for the pointers.
> It seems to work for me, but as a side effect will leave the array
> sorted in the same order as the first parameter and with any
> duplicates it has. Even more annoyingly if there is no intersection
> it will return NULL instead of an empty array, how do I fix this?
It's inelegant, but I just did this:
CREATE OR REPLACE FUNCTION array_intersect (array1 INTEGER[],array2
INTEGER[]) RETURNS INTEGER[]
AS $$
DECLARE
out INTEGER[];
return_empty BOOLEAN := TRUE;
BEGIN
IF array1 IS NULL OR array2 IS NULL THEN
RETURN '[]';
END IF;
FOR i IN ARRAY_LOWER(array1,1) .. ARRAY_UPPER(array1,1) LOOP
IF (array1[i] =ANY (array2)) THEN
out := array_append(out,array1[i]);
return_empty := FALSE;
END IF;
END LOOP;
IF return_empty THEN
RETURN '{}';
END IF;
RETURN out;
END;
$$ LANGUAGE PLPGSQL;
psql=> select array_intersect('{1,2,3}', '{6,7,8}');
array_intersect
-
{}
(1 row)
Josh
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Array intersection
On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> On Wed, 17 Oct 2007 11:12:27 -0500
> "Rodrigo De León" <[EMAIL PROTECTED]> wrote:
>
> > On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > > nm - I just wrote a function - though curious if this is the most
> > > effecient way:
> >
> > If you only want TRUE or FALSE, you can use '&&':
> >
> > t=# SELECT '{1,2}'::INT[] && '{2,3}'::INT[];
> > ?column?
> > --
> > t
> > (1 row)
>
> Is that 8.2 or above? I'm on 8.1.10:
introduced in 8.2
merlin
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 12:33:13 -0400
"Merlin Moncure" <[EMAIL PROTECTED]> wrote:
> On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > On Wed, 17 Oct 2007 11:12:27 -0500
> > "Rodrigo De León" <[EMAIL PROTECTED]> wrote:
> >
> > > On 10/17/07, Josh Trutwin <[EMAIL PROTECTED]> wrote:
> > > > nm - I just wrote a function - though curious if this is the
> > > > most effecient way:
> > >
> > > If you only want TRUE or FALSE, you can use '&&':
> > >
> > > t=# SELECT '{1,2}'::INT[] && '{2,3}'::INT[];
> > > ?column?
> > > --
> > > t
> > > (1 row)
> >
> > Is that 8.2 or above? I'm on 8.1.10:
>
> introduced in 8.2
http://www.postgresql.org/docs/8.2/static/functions-array.html
Thanks - yet another reason to upgrade - waiting till 8.3 tho.
Josh
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] Array intersection
On Wed, Oct 17, 2007 at 11:28:31AM -0500, Josh Trutwin wrote:
> It's inelegant, but I just did this:
> IF return_empty THEN
> RETURN '{}';
> END IF;
humm, why didn't that seem to work for me... ah well. Next version
fixes a problem that I didn't test of the inputs being NULL. '[]' isn't
semantically correct, let alone the correct syntax. I've also fixed the
problem with items in the first array appearing twice. Try:
CREATE OR REPLACE FUNCTION array_intersect (array1 INTEGER[],array2
INTEGER[]) RETURNS INTEGER[]
AS $$
DECLARE
out INTEGER[];
BEGIN
out := '{}'::INTEGER[];
IF array1 IS NULL OR array2 IS NULL THEN
RETURN NULL;
END IF;
FOR i IN array_lower(array1,1) .. array_upper(array1,1) LOOP
IF (array1[i] = ANY (array2)) AND NOT array1[i] = ANY (out) THEN
out := array_append(out,array1[i]);
END IF;
END LOOP;
RETURN out;
END;
$$ LANGUAGE PLPGSQL;
Sam
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Array intersection
On Wed, Oct 17, 2007 at 11:31:51AM -0500, Josh Trutwin wrote:
> Is the =ANY specific to PG 8.2 or higher? On 8.1.10:
It appears (according to [1] and [2]) that you may be able to just
remove the '=' to get it working with 8.1.x. i.e.
v ANY ('{1,2}')
is correct in 8.1.x but in 8.2.x it's
v = ANY ('{1,2}')
I don't have an 8.1 box so I can't test unfortunatly.
Sam
[1]
http://www.postgresql.org/docs/8.1/static/functions-comparisons.html#AEN13394
[2]
http://www.postgresql.org/docs/8.2/static/functions-comparisons.html#AEN14115
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 17:08:06 +0100
Sam Mason <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION array_intersect (array1
> INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
> DECLARE
>out INTEGER[];
> BEGIN
> IF array1 IS NULL OR array2 IS NULL THEN
>RETURN '[]';
> END IF;
> FOR i IN ARRAY_LOWER(array1,1) .. ARRAY_UPPER(array1,1) LOOP
>IF (array1[i] =ANY (array2)) THEN
> out := array_append(out,array1[i]);
>END IF;
> END LOOP;
> RETURN out;
> END;
> $$ LANGUAGE PLPGSQL;
Is the =ANY specific to PG 8.2 or higher? On 8.1.10:
psql=> select array_intersect('{1,2,3}', '{1,2,6,7,8}');
array_intersect
-
(1 row)
Also, I think the first return needs to be:
RETURN '{}';
instead of:
RETURN '[]';
Josh
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Array intersection
On Wed, Oct 17, 2007 at 10:19:43AM -0500, Josh Trutwin wrote:
> Hi,
>
> Is it possible to find the intersection of two array values?
>
> a = '{1,2,3}'
> b = '{2,3,4}'
>
> a intersect b = '{2,3}'
>
> Assume I need to write a pl/pgsql function to do this.
You can use an SQL function, which has the advantage of always being
available :)
It's up to you to ensure that the input arrays have the same type.
Cheers,
David.
CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
RETURNS ANYARRAY
LANGUAGE SQL
AS $$
SELECT ARRAY(
SELECT $1[i] AS "the_intersection"
FROM generate_series(
array_lower($1,1),
array_upper($1,1)
) AS i
INTERSECT
SELECT $2[j] AS "the_intersection"
FROM generate_series(
array_lower($2,1),
array_upper($2,1)
) AS j
);
$$;
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 17:49:01 +0100
Sam Mason <[EMAIL PROTECTED]> wrote:
> On Wed, Oct 17, 2007 at 11:31:51AM -0500, Josh Trutwin wrote:
> > Is the =ANY specific to PG 8.2 or higher? On 8.1.10:
>
> It appears (according to [1] and [2]) that you may be able to just
> remove the '=' to get it working with 8.1.x. i.e.
>
> v ANY ('{1,2}')
>
> is correct in 8.1.x but in 8.2.x it's
>
> v = ANY ('{1,2}')
>
> I don't have an 8.1 box so I can't test unfortunatly.
= ANY appears to work in 8.1 - at least your function from the other
reply worked just fine on 8.1 and I have used = ANY in some other
queries.
Thanks,
Josh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 17:42:21 +0100
Sam Mason <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION array_intersect (array1
> INTEGER[],array2 INTEGER[]) RETURNS INTEGER[] AS $$
> DECLARE
>out INTEGER[];
> BEGIN
> out := '{}'::INTEGER[];
> IF array1 IS NULL OR array2 IS NULL THEN
> RETURN NULL;
> END IF;
> FOR i IN array_lower(array1,1) .. array_upper(array1,1) LOOP
> IF (array1[i] = ANY (array2)) AND NOT array1[i] = ANY
> (out) THEN out := array_append(out,array1[i]);
> END IF;
> END LOOP;
> RETURN out;
> END;
> $$ LANGUAGE PLPGSQL;
Works like a champ on 8.1.
Thanks!
Did you see David Fetter's reply to the original post? He has
an interesting alternative.
Josh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 10:04:21 -0700
David Fetter <[EMAIL PROTECTED]> wrote:
> CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
> RETURNS ANYARRAY
> LANGUAGE SQL
> AS $$
> SELECT ARRAY(
> SELECT $1[i] AS "the_intersection"
> FROM generate_series(
> array_lower($1,1),
> array_upper($1,1)
> ) AS i
> INTERSECT
> SELECT $2[j] AS "the_intersection"
> FROM generate_series(
> array_lower($2,1),
> array_upper($2,1)
> ) AS j
> );
> $$;
Doesn't appear to work on 8.1:
psql=> select array_intersect('{1,2,3}', '{2,3,4}');
ERROR: could not determine anyarray/anyelement type because input
has type "unknown"
Hopefully upgrading to 8.3 soon so I'll have another look then.
Thanks,
Josh
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Array intersection
On Wed, Oct 17, 2007 at 01:06:35PM -0500, Josh Trutwin wrote:
> On Wed, 17 Oct 2007 10:04:21 -0700
> David Fetter <[EMAIL PROTECTED]> wrote:
>
>
>
> > CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY)
> > RETURNS ANYARRAY
> > LANGUAGE SQL
> > AS $$
> > SELECT ARRAY(
> > SELECT $1[i] AS "the_intersection"
> > FROM generate_series(
> > array_lower($1,1),
> > array_upper($1,1)
> > ) AS i
> > INTERSECT
> > SELECT $2[j] AS "the_intersection"
> > FROM generate_series(
> > array_lower($2,1),
> > array_upper($2,1)
> > ) AS j
> > );
> > $$;
>
> Doesn't appear to work on 8.1:
>
> psql=> select array_intersect('{1,2,3}', '{2,3,4}');
> ERROR: could not determine anyarray/anyelement type because input
> has type "unknown"
As mentioned in the "release notes" ;), it's up to you to ensure that
the arrays have the right types, so you'd write the above as:
SELECT array_intersect('{1,2,3}'::int[], '{2,3,4}'::int[]);
Cheers,
David.
--
David Fetter <[EMAIL PROTECTED]> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: [EMAIL PROTECTED]
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [GENERAL] Array intersection
On Wed, 17 Oct 2007 11:26:05 -0700
David Fetter <[EMAIL PROTECTED]> wrote:
> > Doesn't appear to work on 8.1:
> >
> > psql=> select array_intersect('{1,2,3}', '{2,3,4}');
> > ERROR: could not determine anyarray/anyelement type because input
> > has type "unknown"
>
> As mentioned in the "release notes" ;), it's up to you to ensure
> that the arrays have the right types, so you'd write the above as:
>
> SELECT array_intersect('{1,2,3}'::int[], '{2,3,4}'::int[]);
Ah - I saw that but I figured that just meant you couldn't do
something like this:
SELECT array_intersect('{1,2,3}', '{"a","b","c"}');
So basically with this array intersect function you always need to
cast the inputs.
Josh
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
Ow Mun Heng wrote: Query2 is way faster mainly because the plan does not choose a seq scan on a table w/ >20million rows. The only difference between Query 1 and query 2 is that the audio_key_dtime is chosen from a table rather than provided on the query. I'm not sure why this is the case and why it chooses such plans. (should I be posting to pg-performance?) Your query plans don't seem to match your queries. That makes it difficult to provide meaningful advice. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Array intersection
> > > > > > > CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY) > > > RETURNS ANYARRAY > > > LANGUAGE SQL > > > AS $$ > > > SELECT ARRAY( > > > SELECT $1[i] AS "the_intersection" > > > FROM generate_series( > > > array_lower($1,1), > > > array_upper($1,1) > > > ) AS i > > > INTERSECT > > > SELECT $2[j] AS "the_intersection" > > > FROM generate_series( > > > array_lower($2,1), > > > array_upper($2,1) > > > ) AS j > > > ); > > > $$; > > nice :) Maybe we can add function "generate_iterator" CREATE OR REPLACE FUNCTION generate_iterator(ANYARRAY) RETURNS SETOF integer AS $$ SELECT i FROM generate_series(array_lower($1, 1), array_upper($1,1)) AS i $$ LANGUAGE SQL; then CREATE OR REPLACE FUNCTION array_intersect(ANYARRAY, ANYARRAY) RETURNS ANYARRAY LANGUAGE SQL AS $$ SELECT ARRAY( SELECT $1[i] FROM genarate_iterator($1) i INTERSECT SELECT $2[j] FROM generate_iterator($2) j ) $$ ; Regars Pavel ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] conditional alter table add ?
Hi, I do convert an UML XMI model to a database script to create the database schema. To enable multiple iterations I need conditional alter table add column like syntax. Is there any way to do this ? Tanks, Lothar ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] atomic commit;begin for long running transactions , in combination with savepoint.
On 10/17/07, Syan Tan <[EMAIL PROTECTED]> wrote: > my understanding was it used more resources than read committed > because it keeps track of the version id of rows selected > so far in a transaction, "transaction-level consistency", Transaction-level consistency covers all the data in aggregate, it doesn't track which individual rows you may have read. They are simply available or not in transaction-sized sets. MVCC uses more resources than pure locking in general, since it needs to apply versions to rows and store previous versions somewhere for as long as they're needed. PostgreSQL always uses the necessary resources, since it's built on MVCC. SQL Server is natively pure locking with MVCC as a limited option, so the DBA can make a tradeoff between concurrency and resources when appropriate. > google found my reference, and the reference mentioned it was > different from serializable. SQL Server's SNAPSHOT and SERIALIZABLE both implement SQL standard SERIALIZABLE-level isolation, in that they guarantee reads are repeatable without phantoms. There are subtle differences in other guarantees they make, though, due to the implementation. SQL Server has had SERIALIZABLE as a pure locking implementation for a long time, so it can't replace it without potentially breaking applications that depend on the locking behavior. Since the MVCC behavior is slightly different, it had to be added under a new name. There's a good example in the PostgreSQL docs that illustrates the difference. Consider this table: class | value ---+--- 1 |10 1 |20 2 | 100 2 | 200 Transactions A and B both begin at the same time, using MVCC rules for SERIALIZABLE-level isolation. Transaction A executes SELECT SUM(value) FROM table WHERE class = 1; and gets 30 as the result, which it then inserts with the other class: INSERT INTO table VALUES (2, 30); Transaction B executes SELECT SUM(value) FROM table WHERE class = 2; and gets 300 as the result, which it also inserts with the other class: INSERT INTO table VALUES (1, 300); When both transactions commit, the table will look like this: class | value ---+--- 1 |10 1 |20 2 | 100 2 | 200 2 |30 1 | 300 Under MVCC rules this is perfectly fine. Both transactions got a frozen snapshot of the original 4 rows in the table. Neither transaction modified those rows, so there was no conflict. MVCC guarantees each transaction will not see new rows inserted by other transactions, so there was no conflict there either. But if you wanted SERIALIZABLE to mean "the same as if the transactions were executed one after the other", that result is clearly wrong. If transaction A executed first, transaction B would have gotten 330 as its answer, since it would have seen the row with value 30 that A inserted. Similar if the transactions went in the other order. If you repeat that situation using pure locking rules, one of the transactions fails, forcing it to be tried again later in proper order. Most of the time this isn't necessary, because your data doesn't have interesting interdependencies like that, but sometimes it is the behavior you want. SQL Server's SNAPSHOT isolation mode is identical to PostgreSQL's SERIALIZABLE mode: they both execute the above scenario using MVCC rules. SQL Server's SERIALIZABLE isolation mode executes the above using pure locking rules, something PostgreSQL doesn't support. (PostgreSQL does expose locking primitives so applications can get that behavior anyway, but it's not automatic and supported as a transaction isolation level.) SQL Server also has an option to use snapshots under the READ COMMITTED isolation level, to allow more concurrency at the expense of storing row versions in the background. It still doesn't track what you actually read, just freezes a copy of the data while a single query is being executed, instead of holding locks during that time. Locks would prevent other transactions from modifying the same rows, which could be a problem if the query takes a while to run. PostgreSQL always takes snapshots. For both databases, an UPDATE always finds the latest version of a row. I spent some time testing this with SQL Server Express, so as far as I know the above is accurate. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] downloading and installing postgreSQL
Im installing the latest software ( postgresql-8.2.5-1-binaries-no-installer.) on windows xp but i dont know what file to click to install - this seems very ambiguous. Can anyone help? -- Ken
Re: [GENERAL] Array intersection
On Wed, Oct 17, 2007 at 01:00:48PM -0500, Josh Trutwin wrote: > Works like a champ on 8.1. > > Thanks! That's OK, it was a good learning experience. > Did you see David Fetter's reply to the original post? He has > an interesting alternative. I did, it's much more elegant. I've never seen generate_series used like that, it's a very nice way of doing things. If your arrays are ever more than a few elements long his is probably going to be much faster. Generally for less than 6 or 7 elements it's better doing the naive thing, not sure if that applies here though. Sam ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] downloading and installing postgreSQL
Ken Johansson wrote: Im installing the latest software ( postgresql-8.2.5-1-binaries-no-installer.) on windows xp but i dont know what file to click to install - this seems very ambiguous. Can anyone help? You don't mention platform, but Win32 is the only one with a no-installer binary, so I guess we can assume you are installing on some approved version of Windows. Why did you elect a no-installer version? The regular version comes with an installer and is very easy to install. I see the following note on the download page: "postgresql-8.2.5-1-binaries-no-installer.zip is a zip of the PostgreSQL installation directory. It does not include any of the bundled apps or drivers and is intended for expert users only!" It's a zip file, you just unzip it where you want it installed. But since you don't know what to do with it, I'd suggest you go with postgresql-8.2.5-1.zip instead. Unzip in a temporary directory, read the README file, then double click the MSI file (or just type the name at a command prompt if you use that.) -- Guy Rouillier ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] downloading and installing postgreSQL
On 10/17/07, Ken Johansson <[EMAIL PROTECTED]> wrote: > Im installing the latest software > (postgresql-8.2.5-1-binaries-no-installer.) on windows > xp but i dont know what file to click to install - this seems very > ambiguous. Can anyone help? You want to get postgresql-8.2.5-1.zip instead. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] downloading and installing postgreSQL
Great, thanks. Is this the appropriate place to ask those types of support questions? Well, any type of support questions? thanks Ken On 17/10/2007, Guy Rouillier <[EMAIL PROTECTED]> wrote: > > Ken Johansson wrote: > > Im installing the latest software ( > > postgresql-8.2.5-1-binaries-no-installer.) on windows xp but i dont > know > > what file to click to install - this seems very ambiguous. Can anyone > > help? > > You don't mention platform, but Win32 is the only one with a > no-installer binary, so I guess we can assume you are installing on some > approved version of Windows. Why did you elect a no-installer version? > The regular version comes with an installer and is very easy to > install. I see the following note on the download page: > > "postgresql-8.2.5-1-binaries-no-installer.zip is a zip of the PostgreSQL > installation directory. It does not include any of the bundled apps or > drivers and is intended for expert users only!" > > It's a zip file, you just unzip it where you want it installed. But > since you don't know what to do with it, I'd suggest you go with > postgresql-8.2.5-1.zip instead. Unzip in a temporary directory, read > the README file, then double click the MSI file (or just type the name > at a command prompt if you use that.) > > -- > Guy Rouillier > > ---(end of broadcast)--- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org/ > -- Ken
Re: [GENERAL] downloading and installing postgreSQL
On Oct 17, 2007, at 4:28 PM, Ken Johansson wrote: On 17/10/2007, Guy Rouillier <[EMAIL PROTECTED]> wrote: Ken Johansson wrote: > Im installing the latest software ( > postgresql-8.2.5-1-binaries-no-installer .) on windows xp but i dont know > what file to click to install - this seems very ambiguous. Can anyone > help? You don't mention platform, but Win32 is the only one with a no-installer binary, so I guess we can assume you are installing on some approved version of Windows. Why did you elect a no-installer version? The regular version comes with an installer and is very easy to install. I see the following note on the download page: "postgresql-8.2.5-1-binaries-no-installer.zip is a zip of the PostgreSQL installation directory. It does not include any of the bundled apps or drivers and is intended for expert users only!" It's a zip file, you just unzip it where you want it installed. But since you don't know what to do with it, I'd suggest you go with postgresql-8.2.5-1.zip instead. Unzip in a temporary directory, read the README file, then double click the MSI file (or just type the name at a command prompt if you use that.) Great, thanks. Is this the appropriate place to ask those types of support questions? Well, any type of support questions? thanks Ken Those "types" yes. Other "types" may have more specific mailing lists (jdbc, sql, etc...). However, none of the Postgres lists are appropriate places for top-posting (I moved your response to the bottom). Erik Jones Software Developer | Emma® [EMAIL PROTECTED] 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] problemas zona horaria
Fernando De Pedro wrote: > Estimados...tengo un problema con la zona horaria de (GMT-06:00) Guadalajara, > Ciudad de México, Monterrey - Nuevo. > > Cuando me conecto desde una aplicacion java a un servidor postgres todo en > la misma maquina a el momento de insertar registros me cambia las horas me > adelanta 5 horas imagino que es porque no esta definido este timezone en > postgres cuando le pongo show timezone me dice GMT en cambio si uso > (GMT-06:00) Guadalajara, Ciudad de México, Monterrey - Antiguo me pone > "America/Mexico_City". y anda bien alguien sabe como solucionar este tema Y solucionaste el problema? Que sistema operativo estas usando? Las zonas "America/Monterrey" y "America/Mexico_City" son un poco distintas. Quizas deberias configurar la correcta en postgresql.conf (para eso cambias el parametro TimeZone). # Coahuila, Durango, Nuevo Leon, Tamaulipas Zone America/Monterrey -6:41:16 - LMT 1921 Dec 31 23:18:44 -6:00 - CST 1988 -6:00 US C%sT1989 -6:00 Mexico C%sT # Central Mexico Zone America/Mexico_City -6:36:36 - LMT 1922 Jan 1 0:23:24 -7:00 - MST 1927 Jun 10 23:00 -6:00 - CST 1930 Nov 15 -7:00 - MST 1931 May 1 23:00 -6:00 - CST 1931 Oct -7:00 - MST 1932 Apr 1 -6:00 Mexico C%sT2001 Sep 30 02:00 -6:00 - CST 2002 Feb 20 -6:00 Mexico C%sT -- Alvaro Herrera http://www.amazon.com/gp/registry/DXLWNGRJD34J "Si quieres ser creativo, aprende el arte de perder el tiempo" ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
On Wed, 2007-10-17 at 20:37 +0100, Richard Huxton wrote: > Ow Mun Heng wrote: > > Query2 is way faster mainly because the plan does not choose a seq scan > > on a table w/ >20million rows. > > The only difference between Query 1 and query 2 is that the > > audio_key_dtime is chosen from a table rather than provided on the > > query. > > > > I'm not sure why this is the case and why it chooses such plans. > > (should I be posting to pg-performance?) > > Your query plans don't seem to match your queries. That makes it > difficult to provide meaningful advice. > > Well, then that makes both you and me(both) stumped. because the 2 queries are exactly the same except for the data part. Index Cond: ((audit_key_dtime >= $0) AND (audit_key_dtime < $1)) Index Cond: ((audit_key_dtime >= '2007-08-08 18:00:00'::timestamp without time zone) AND (audit_key_dtime < '2007-08-08 18:01:00'::timestamp without time zone)) This is _the_ only difference between the 2 queries where on one, the dates are provided, and the other is selected from a table. I have no idea why the plans are so different between the two. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
Ow Mun Heng wrote: > Index Cond: ((audit_key_dtime >= $0) AND (audit_key_dtime < $1)) > > > Index Cond: ((audit_key_dtime >= '2007-08-08 18:00:00'::timestamp without > time zone) > AND (audit_key_dtime < '2007-08-08 18:01:00'::timestamp without > time zone)) > > This is _the_ only difference between the 2 queries where on one, the > dates are provided, and the other is selected from a table. > > I have no idea why the plans are so different between the two. The difference is that it has to consider the worst possibility in the second case, whereas the other one knows the interval is just one minute. I didn't read the entire thread but I've seen similar things go much better when you grab the constants beforehand and interpolate them into the query by yourself. Yes, it's really ugly. -- Alvaro Herrera http://www.flickr.com/photos/alvherre/ "Crear es tan difícil como ser libre" (Elsa Triolet) ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
On Wed, 2007-10-17 at 22:47 -0300, Alvaro Herrera wrote: > Ow Mun Heng wrote: > > > Index Cond: ((audit_key_dtime >= $0) AND (audit_key_dtime < $1)) > > > > > > Index Cond: ((audit_key_dtime >= '2007-08-08 18:00:00'::timestamp without > > time zone) > > AND (audit_key_dtime < '2007-08-08 18:01:00'::timestamp without > > time zone)) > > > > This is _the_ only difference between the 2 queries where on one, the > > dates are provided, and the other is selected from a table. > > > > I have no idea why the plans are so different between the two. > > The difference is that it has to consider the worst possibility in the > second case, whereas the other one knows the interval is just one minute. Actually the dates are just 1 min apart in both cases. > > I didn't read the entire thread but I've seen similar things go much > better when you grab the constants beforehand and interpolate them into > the query by yourself. Yes, it's really ugly. > Yeah.. In the end, I just made a hack of it and did sort of like a dymanic sql. (it is a dynamic sql anyway) where I selected the dates into a variable (the whole query is encapsulated as a function ran at x intervals) and then use query_string := replace(query_string,'fromdate',date_inputed_into_variable) query_string := replace(query_string,'todate',date_inputed_into_variable+refresh_interval) and that got me much better performance. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Suggestions for Remote Procedure Calls from PG, please?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 What's the best way to invoke a process on another server from a PG trigger or procedure? I was thinking of using pl/java to invoke a web service on the other box... Can pl/tcl run Expect scripts? That'd be an option, too. Or I could use XMLBlaster to send a message to the other box to start the other process, but that's an asynchronous call and I can't be sure if the remote procedure ran properly. Does anyone else invoke a process on a remote server? How do you do it? Thanks, Bret -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) iD8DBQFHFsVCIeMC5lK637kRAvBvAKCRAgAg3H7jK/efm8KBlUKUifKV0ACgmo07 3eLZT6pB2XI8uTS47fdYcSw= =rXIJ -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
Bret, I had quick and robust success using pl/python and pyro. Pyro is Python Remote object. All mentioned modules are BSD-like in license. Harald > What's the best way to invoke a process on another server from a PG > trigger or procedure? I was thinking of using pl/java to invoke a web > service on the other box... > Does anyone else invoke a process on a remote server? How do you do it? -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 fx 01212-5-13695179 - EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
Bret Schuhmacher <[EMAIL PROTECTED]> writes: > What's the best way to invoke a process on another server from a PG > trigger or procedure? I was thinking of using pl/java to invoke a web > service on the other box... Can pl/tcl run Expect scripts? No, but pl/tclu could. > Or I could use XMLBlaster to send a message to the > other box to start the other process, but that's an asynchronous call > and I can't be sure if the remote procedure ran properly. You've almost figured out the big problem with anything like this; the trouble spot is the other way around. What if you launch some remote operation, and it succeeds, and then later your own transaction rolls back for some unrelated reason? Action FOO did happen in the external world, but there is no change in the state of the database --- which at the minimum probably means you'll try to do FOO again later. Lather, rinse, repeat. The general conclusion among folks who have actually done this is that launching external actions from inside a transaction isn't robust. Instead, have the transaction put an entry into a "to-do queue" table that is monitored by some client process. You still have to be careful about failure conditions, but there's a whole lot more flexibility when it's being driven from a client that's outside the database. See previous discussions in the archives. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Tom Lane wrote: > > > You've almost figured out the big problem with anything like this; > the trouble spot is the other way around. What if you launch some > remote operation, and it succeeds, and then later your own transaction > rolls back for some unrelated reason? Action FOO did happen in the > external world, but there is no change in the state of the database > --- which at the minimum probably means you'll try to do FOO again > later. Lather, rinse, repeat. > . Thanks for the reply, Tom. I was thinking I could have my remote process send a message back to PG via XMLBlaster, too. XMLBlaster is a MOM-like message-queuing app that guarantees delivery to subscribers. (www.xmlblaster.org). The problem, as you stated, though, is transactional integrity :-(. Hmmm, I'll see about the to-do queue idea. Thanks again for your time! Bret -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.7 (MingW32) iD8DBQFHFtagIeMC5lK637kRAg56AJsF6eNlQWPdpjb8ufiO+xRqZTXymgCfdJFG 4igU9pCasxaVSGOxC0DBbHg= =qKK2 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
am Wed, dem 17.10.2007, um 22:30:26 -0400 mailte Bret Schuhmacher folgendes: > Does anyone else invoke a process on a remote server? How do you do it? You can use any untrusted programming language like pl/perlU or plsh. Other solution: use LISTEN/NOTIFY, see http://www.postgresql.org/docs/8.2/interactive/sql-notify.html Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Suggestions for Remote Procedure Calls from PG, please?
> What's the best way to invoke a process on another server from a PG > trigger or procedure? I was thinking of using pl/java to invoke a web > service on the other box... Can pl/tcl run Expect scripts? That'd be > an option, too. Or I could use XMLBlaster to send a message to the > other box to start the other process, but that's an asynchronous call > and I can't be sure if the remote procedure ran properly. > > Does anyone else invoke a process on a remote server? How do you do it? > pl/perl samples: http://www.pgsql.cz/index.php/PL/Perlu_-_Untrusted_Perl_%28en%29 ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL]
Hi all, Initially i installed postgresql 8.2.5 for my Dspace software..everything was fine.. but all of sudden yeterday while connecting an error popped out sayin server could'nt listen i have uninstalled it and reinstalled many a times with different ports and diffferent accounts.. but its still not working.. how do i go ahead.. Regards, Roopal Unlimited freedom, unlimited storage. Get it now, on http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
Ow Mun Heng wrote: On Wed, 2007-10-17 at 20:37 +0100, Richard Huxton wrote: Your query plans don't seem to match your queries. That makes it difficult to provide meaningful advice. Well, then that makes both you and me(both) stumped. because the 2 queries are exactly the same except for the data part. Your queries were like this: > select > foo, > bar, > foobar > from > A, > join B > on a.id = b.id > join C > on c.id = b.id > and c.start_dtime = b.start_dtime > where audit_key_dtime >= '2007-08-08 18:00:00' > and audit_key_dtime < '2007-08-08 18:01:00' Your plans contained these: > Filter: (pber_err_rate_hd_zn_2 <> 0::numeric) > Filter: (web_id <> 0::numeric) I don't see the <> 0 condition anywhere in your SQL - presumably there's a view involved somehwere? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL]
roopal oswal wrote: Hi all, Initially i installed postgresql 8.2.5 for my Dspace software..everything was fine.. but all of sudden yeterday while connecting an error popped out sayin server could'nt listen i have uninstalled it and reinstalled many a times with different ports and diffferent accounts.. but its still not working.. how do i go ahead.. You'll need to provide an actual error message before anyone can give you any help - "server could'nt listen" isn't something that PostgreSQL will display. What's the error message, and what's in the logs? -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Poor Plan selected w/ not provided a date/time but selecting date/time from a table
On Thu, 2007-10-18 at 07:28 +0100, Richard Huxton wrote: > Ow Mun Heng wrote: > > On Wed, 2007-10-17 at 20:37 +0100, Richard Huxton wrote: > >> Your query plans don't seem to match your queries. That makes it > >> difficult to provide meaningful advice. > > > > Well, then that makes both you and me(both) stumped. because the 2 > > queries are exactly the same except for the data part. > > Your queries were like this: > > > select > > foo, > > bar, > > foobar > > from > > A, > > join B > > on a.id = b.id > > join C > > on c.id = b.id > > and c.start_dtime = b.start_dtime > > where audit_key_dtime >= '2007-08-08 18:00:00' > > and audit_key_dtime < '2007-08-08 18:01:00' and web_id <> 0 > > > Your plans contained these: > > > Filter: (pber_err_rate_hd_zn_2 <> 0::numeric) > > > Filter: (web_id <> 0::numeric) > > I don't see the <> 0 condition anywhere in your SQL - presumably there's > a view involved somehwere? My apologies, in an effort to disguise the column_names, I missed out the 1st one. There is no View it's just a filtering condition. Having said that, the only difference between the 2 queries are just the dates. (or the way the dates are provided to the query..) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
