Re: [GENERAL] Pet Peeves?
On Wed, 2009-02-04 at 03:00 +, Greg Stark wrote: > We already have autovacuum, which runs VACUUM and ANALYZE to a set > > schedule. We could have kept that outside core, but didn't. > > > > It's not too big a stretch to imagine we could redesign autovacuum > as a > > GP scheduler, with autovacuum as just one/two regular scheduled > jobs. > > Except autovacuum *isn't* a regularly scheduled job and doesn't run > vacuum and analyze on a set schedule. It runs them on a highly dynamic > schedule based on observations of activity in the database. It also > has privileged access to the database, reading from all databases and > receiving signals when various events occur. You cannot implement > autovacuum's current behaviour in cron no matter how clever you make > cron. So putting a scheduler inside the database allows it to do things it couldn't otherwise do. Sounds like a great argument for *inclusion*. AV runs every (configurable) 60 secs. What it does when it runs is its own business. It has a pool of slaves ready to do real transactional work and an infrastructure to preferentially cancel work if it interferes with users. It's clearly a great place to hang other code that (somebody) would like to run on a regular basis: * regular maintenance tasks * performance tuning * summary table creation/maintenance * adding partitions * health checks * etc We can keep adding processes every time we want a new function in the db, or we can add a generic facility. I've already added two special processes, so I'd rather not add too many more. -- 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] Pet Peeves?
On Wed, 2009-02-04 at 02:39 +, Greg Stark wrote: > On Tue, Feb 3, 2009 at 10:06 PM, Simon Riggs wrote: > > > > On Tue, 2009-02-03 at 15:03 -0500, Chris Mayfield wrote: > > > >> 1. Having to rewrite entire tables out to disk the first time I scan > >> them, for example: > >> > >> CREATE TABLE t1 AS ...; -- writes 100 GB to disk > >> CREATE INDEX i1 ON t1 ...; -- rewrites 100 GB to disk > >> > >> The main issue is setting the hint bits for each tuple, which IMO should > >> initially be set for "CREATE TABLE AS" statements. To work around this > >> for now, I modified heap_insert (in heapam.c) to mark tuples as > >> committed when inserting them into newly added pages without WAL: > > > > I'll take this for 8.5. > > This was proposed once already and some difficulties were identified. > Do you remember what they were? Time, mainly. Technical issues were not insurmountable, just more subtle than I had originally thought. -- 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] Pet Peeves?
> > Craig, what kind of "events" are you thinking about? Triggers are > > already pieces of code that run upon "certain events", namely insert, > > update or delete events. What others do you have in mind? > > That's a good point, actually. I can't think of much you can't do with a > trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. I would find ON CONNECT/DISCONNECT triggers very useful. Probably this is more similar to database-wide assertions. Karsten -- Pt! Schon vom neuen GMX MultiMessenger gehört? Der kann`s mit allen: http://www.gmx.net/de/go/multimessenger01 -- 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] Pet Peeves?
Gregory Stark wrote: > Steve Crawford writes: > 3. Date handling Sometimes I've got data with invalid dates and it would be great if it could replace all the bad ones with, say "-00-00". >> Oh dear $DEITY, no. > > I think it would be best if we limited ourselves right now to discussing the > problems themselves and not debating the pros and cons of possible solutions. It seems I need to point out that the -00-00 thing was supposed to be a joke. -- 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
[GENERAL] installation
Hello all, i am new to postgresql, i want to create tables in this i have followed 10 Steps to Installing PostgreSQL which is chapter two of installing postgresql i could follow upto step no 7 but i am getting errors from step 8 while following the steps i got message like postgresql is sucessfully installed now my doubt if it is installed, how should i start working on it i used sudo su postgres -c psql template1 password for my super user:** then the prompt changed to postgres=# what does this implies can i start working on that or do i have to modify furthur sorry for such a long mail and Thanks for any help!!! Thanks & Regards kusuma.p -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] field with Password
Hi. I would like to create a new table where one of the field would be a user password. Is there any data type for supporting this functionality? Something like Password DataType. I've taken a look of the available data types in PgAdmin Application and there is nothing similar to this. Thanks in advance. Best, -- 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] field with Password
what do you expect from such type? try to use a text field for plain passwords or better store only hashvalues. see md5() regards thomas Iñigo Barandiaran schrieb: Hi. I would like to create a new table where one of the field would be a user password. Is there any data type for supporting this functionality? Something like Password DataType. I've taken a look of the available data types in PgAdmin Application and there is nothing similar to this. Thanks in advance. Best, -- 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] Connecting to old 7.1 Database
On Tue, Nov 18, 2008 at 18:34, Andy Greensted wrote: > So, two questions: > > - Is there anyway to run a newer version (8.3.5) of psql in some sort of > 'backwards compatible' mode? > > - Do you have any tips on making 7.1.3 compile on a newer system? The last time I had to compile super old code (was super old apache with super old php) I had to setup a separate libs, etc enviroment wherein I could compile it. Perhaps you can find a very old Live CD from gentoo, knoppix or other and use this? -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclai...@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates. -- 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] field with Password
> >> I would like to create a new table where one of the field would be a > >> user password. Is there any data type for supporting this > >> functionality? Something like Password DataType. I've taken a look > of > >> the available data types in PgAdmin Application and there is nothing > >> similar to this. > > > > most commonly, passwords are stored as hashes, such as md5, rather > > than plaintext.'text' would be as suitable for this as anything, > > or bytea, if you want to store the hashes in binary. > > > Thanks for your answers. Sorry for the questions but I'm new to Postgre > :) > > The problem with a plain text password is that a user can see it by > looking at the user table. > Both suggest to use MD5. How can i use it? Any link, example about this > would be very appreciated. Insert new users like this: insert into myusers (usernm, passwd) values ($user, MD5($pass)); So the paintext password is not stored. But you should still restrict access to this table. Revoke rights to regular users. When a user logs in, check for their access like this: select * from myusers where usernm=$user and passwd=MD5($pass); The hash of a particular password is always the same. To make this scheme more secure, you should add a salt before hashing. (You can find how to do this via google). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Crash of Postgresql on Windows
Hello, I am sending this email since I am hitting a snag with postgresql. I am currently running the following version of postgresql: Postgresql 8.3.5 on Windows XP Pro 32bits I am getting a crash whenever I try to perform an update on some rows. More precisely, I have recently imported a dataset and managed to work through it easily. However, when performing one update I am getting the following error: GMT PANIC: could not write to log file 40, segment 44 at offset 8929280, length 1744896: Invalid argument This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. 2009-02-04 12:02:34 GMT LOG: WAL writer process (PID 2916) exited with exit code 3 2009-02-04 12:02:34 GMT LOG: terminating any other active server processes 2009-02-04 12:02:34 GMT WARNING: terminating connection because of crash of another server process 2009-02-04 12:02:34 GMT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-02-04 12:02:34 GMT HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-02-04 12:02:34 GMT WARNING: terminating connection because of crash of another server process 2009-02-04 12:02:34 GMT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-02-04 12:02:34 GMT HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-02-04 12:02:34 GMT WARNING: terminating connection because of crash of another server process 2009-02-04 12:02:34 GMT DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. 2009-02-04 12:02:34 GMT HINT: In a moment you should be able to reconnect to the database and repeat your command. 2009-02-04 12:02:34 GMT LOG: all server processes terminated; reinitializing 2009-02-04 12:02:35 GMT FATAL: pre-existing shared memory block is still in use 2009-02-04 12:02:35 GMT HINT: Check if there are any old server processes still running, and terminate them. I am a bit lost on what to do next. I have tried running the query several times even changing the parameters to make sure if it wasn't something in the sql. UPDATE gtable AS g SET code = '00' -- 04 WHERE g.code = '04' AND g.cc = 'TW'; UPDATE gtable AS g SET code = '00' -- 04 WHERE g.d IN (6724652, 1673813); Those two queries do exactly the same thing but they both crash with the same information, except for the length between the two queries. What should I do? Emilie Laffray -- 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] Vacuums taking forever :(
Phoenix Kiula writes: > Thanks, Gregory and Simon, for the very useful posts. > > I have increased the vacuum_cost_limit to 2000 for now, just to see if > that has an impact. Hopefully positive. Note that that was offhand speculation. Conventional wisdom is that it should make things *worse* -- you're saying to process more pages between sleeping so it'll use more i/o. I was speculating that you increased both vacuum_cost_limit and vacuum_cost_delay proportionally it might use the i/o more efficiently even though it's using the same amount of total bandwidth. The more normal suggestion is to increase *vacuum_cost_delay* which tells it to sleep longer between bits of work. Don't increase it too much or vacuum will take forever. But if you increase it from 20 to 40 it should use half as much i/o as bandwidth as now. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGAdmin and records as inserts (like in SQLyog)
Hi! I wanna ask, that have the PGAdmin same possibility (like in SQLyog) to copy records (from the Query, from the Table View) to clipboard in the "INSERT SQL" format? This can speed up the work, and I can insert the record to another table that have similar (but a little different) format without field content confusion. Thanks for your help: dd -- 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] PGAdmin and user privileges - what I do wrong?
On 04/02/2009 12:40, durumdara wrote: > When I want to manually assign this user to a table, I have problem in > the PGAdmin's Privilege tab. > The privileges GroupBox have a ComboBox, named Role. This ComboBox is > not containing the zx user in it's list. By default, that combo box shows only group roles. You need to go to File -> Options -> Preferences tab, and select "Show users for privileges". By the way, there's a separate mailing list for specifically pgAdmin questions - pgadmin-supp...@postgresql.org - you'll find subscription instructions on the pgAdmin website. This list is for PostgreSQL itself. HTH, Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] PGAdmin and user privileges - what I do wrong?
On Wed, Feb 4, 2009 at 12:40 PM, durumdara wrote: > Hi! > > Please help me a little. > > I used PGAdmin to administrate my databases. > I created a new user named "zx". > > CREATE ROLE zx LOGIN > ENCRYPTED PASSWORD '*' > NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; > > When I want to manually assign this user to a table, I have problem in the > PGAdmin's Privilege tab. > The privileges GroupBox have a ComboBox, named Role. This ComboBox is not > containing the zx user in it's list. > > When I write the user (zx) into the combo , then the "Add" button have been > disabled. > > I can write only an SQL cmd to add this privilege. When I do it, the > Privilege list extended with this user, I can select it from the combobox. > > What I do wrong? Which property I need to set to I can Add privileges to the > tables/objects with this user? See File -> Options -> Preferences -> Show users for privileges? By default, only group roles are listed. -- Dave Page EnterpriseDB UK: http://www.enterprisedb.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] C function question
Tom Lane writes: > Alvaro Herrera writes: >> Grzegorz Jaśkiewicz wrote: >>> looks like it really has to be defined with "char" in double quotes. I >>> thought just char is enough... > >> They're different types. > > You know, maybe we should stop holding our noses and do something about > this old gotcha. That type's not going away anytime soon, but could we > rename it to char1 or something like that? int1? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication 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] Crash of Postgresql on Windows
Hello, 1) I can perform a query on the rows that I am trying to update. I have also successfully updated rows before in the same table and also after. 2) I have no reason to believe that the filesystem got corrupted. I have started a scan before you asked the question wondering if it could be the problem. So far, no problem has been found. 3) I have an antivirus running, but again this antivirus has been running for months and I have worked on postgresql also for months. There has been an update yesterday to that antivirus, but I don't think it had an impact. 4) The disk space is not a problem with more than 160GB free. I tried also in the meantime to run a vacuum, and I got some messages that the indexes had a problem and it was working to fix it. It crashed. I restarted my pc and could perform the vacuum just fine afterwards. Richard Huxton wrote: Emilie Laffray wrote: Hello, I am sending this email since I am hitting a snag with postgresql. I am currently running the following version of postgresql: Postgresql 8.3.5 on Windows XP Pro 32bits I am getting a crash whenever I try to perform an update on some rows. More precisely, I have recently imported a dataset and managed to work through it easily. However, when performing one update I am getting the following error: GMT PANIC: could not write to log file 40, segment 44 at offset 8929280, length 1744896: Invalid argument 1. Can you run any other queries a. A SELECT b. A different UPDATE 2. Do you have any reason to think the filesystem may have become corrupted? 3. Do you have an antivirus scanner that might be interfering with PostgreSQL? That frequently causes strange problems. -- 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] Crash of Postgresql on Windows
Andrew Gould wrote: > On Wed, Feb 4, 2009 at 6:18 AM, Emilie Laffray > wrote: >> UPDATE gtable AS g >> SET code = '00' -- 04 >> WHERE g.code = '04' >> AND g.cc = 'TW'; > What does '--' do? It's a comment delimeter -- 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] field with Password
Thanks! Ok. I've found http://256.com/sources/md5/ library. So the idea is to define in the dataBase a Field of PlainText type. When I want to insert a new user, I define a password, convert to MD5 hash with the library and store it in the DataBase. Afterwards, any user check should get the content of the DataBase of do the inverse process with the library. Is it correct? Thanks so much!! Best, Iñigo Barandiaran wrote: Thanks for your answers. Sorry for the questions but I'm new to Postgre :) The problem with a plain text password is that a user can see it by looking at the user table. Both suggest to use MD5. How can i use it? Any link, example about this would be very appreciated. md5 is a library function that converts a string to a 'hash', typically 32 bytes. so, when the user enters a password, you encode it with md5() and compare it with the stored hash.the md5 hash is not readily reversible (although brute force techniques can theoretically come up with strings that will generate the same hash) otoh, any table used to store security information probably should not be directly viewable by the end user. application programming techniques for ensuring application security go far beyond the charter of this email list, however. -- 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] Crash of Postgresql on Windows
Emilie Laffray wrote: > 3) I have an antivirus running, but again this antivirus has been > running for months and I have worked on postgresql also for months. > There has been an update yesterday to that antivirus, but I don't think > it had an impact. > I tried also in the meantime to run a vacuum, and I got some messages > that the indexes had a problem and it was working to fix it. It crashed. > I restarted my pc and could perform the vacuum just fine afterwards. I don't run PG on Windows other than to test, but this definitely sounds like antivirus problems to me. They're quite sophisticated nowadays and it might be that it takes a certain pattern of activity to trigger it. Try (1) turning your antivirus off and running the update and if that works, (2) excluding all the data directories and PostgreSQL from it's checking. -- 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] C function question
On Tue, Feb 3, 2009 at 6:16 PM, Tom Lane wrote: > Merlin Moncure writes: >> On Tue, Feb 3, 2009 at 4:28 PM, Tom Lane wrote: >>> You know, maybe we should stop holding our noses and do something about >>> this old gotcha. That type's not going away anytime soon, but could we >>> rename it to char1 or something like that? (With some sort of backward >>> compatibility hack, like a domain named "char".) > >> domains are out, unless arrays of domains are addressed first. > > [ raised eyebrow... ] You've got apps that depend on array of "char"? yes. For example. I wrote a ISAM emulation wrapper for libpq a few years back that supported some cobol applicaitons. char(1) fields are common cobol (and were mapped to "char" for performance), as are arrays of records. since at the time there was no support for arrays of composites or domains, I had to use parallel arrays of POD types when mapping these types of records to PostgreSQL. This would now break. Anyways, why prefer domain to type alias (char1 : "char" :: bigint : int8)? Main advantage of domains is being able to add user level constraints, which is kinda weird for system provided type. merlin -- 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] field with Password
Iñigo Barandiaran wrote:
Thanks!
Ok. I've found http://256.com/sources/md5/ library. So the idea is to
define in the dataBase a Field of PlainText type. When I want to
insert a new user, I define a password, convert to MD5 hash with the
library and store it in the DataBase. Afterwards, any user check
should get the content of the DataBase of do the inverse process with
the library. Is it correct?
Thanks so much!!
Best,
Well, you can use the built-in md5 function for this purpose. For
instance, you could insert a password into the table with a statement like:
insert into auth_data (user_id, password) values (1, md5('test'));
And compare the supplied password with something like:
select true from auth_data where user_id = 1 and password = md5('test');
You don't need to depend on an external library for this functionality;
it's built right into Postgres. Personally, in my own apps I write in
PHP, I use a combination of sha1 and md5 to hash user passwords,
without depending on Postgres to do the hashing, but the effect is
basically the same.
Raymond
Re: [GENERAL] Crash of Postgresql on Windows
Hello, well Ｉcouldn't turn off my antivirus since it is controlled by our IT management team. However, since I rebuilt the indexes, the query has been running fine. I don't understand what happpened. Before asking for help, I made sure I could repeat the problem over several reboots. Emilie Laffray Richard Huxton wrote: Emilie Laffray wrote: 3) I have an antivirus running, but again this antivirus has been running for months and I have worked on postgresql also for months. There has been an update yesterday to that antivirus, but I don't think it had an impact. I tried also in the meantime to run a vacuum, and I got some messages that the indexes had a problem and it was working to fix it. It crashed. I restarted my pc and could perform the vacuum just fine afterwards. I don't run PG on Windows other than to test, but this definitely sounds like antivirus problems to me. They're quite sophisticated nowadays and it might be that it takes a certain pattern of activity to trigger it. Try (1) turning your antivirus off and running the update and if that works, (2) excluding all the data directories and PostgreSQL from it's checking. -- 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] Crash of Postgresql on Windows
Emilie Laffray wrote: > Hello, > > well Ｉcouldn't turn off my antivirus since it is controlled by our IT > management team. However, since I rebuilt the indexes, the query has > been running fine. I don't understand what happpened. Before asking for > help, I made sure I could repeat the problem over several reboots. It's not impossible you have found a bug in PostgreSQL's code. However, I can't think of a situation where rebuilding an index would fail once then work fine after a reboot. Not if your hardware is working OK. I would contact your IT team and ask them to add an exclusion for your PostgreSQL directories though. Otherwise you'll never be sure what is causing any crashes. -- 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] ramblings about password exposure (WAS: field with Password)
On Wed, Feb 04, 2009 at 09:34:56AM -0500, Raymond C. Rodgers wrote: > You don't need to depend on an external library for this functionality; > it's built right into Postgres. Personally, in my own apps I write in > PHP, I use a combination of sha1 and md5 to hash user passwords, > without depending on Postgres to do the hashing, but the effect is > basically the same. Doing the hashing outside PG would reduce the chance of the password being exposed, either accidentally by, say, turning on statement logging, or maliciously. A general rule with passwords is to throw away any copy of a plain text password as quickly as possible, sending the password over to another process would go against this. -- Sam http://samason.me.uk/ -- 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] PGAdmin and records as inserts (like in SQLyog)
On 04/02/2009 12:31, durumdara wrote: > I wanna ask, that have the PGAdmin same possibility (like in SQLyog) to > copy records (from the Query, from the Table View) to clipboard in the > "INSERT SQL" format? > > This can speed up the work, and I can insert the record to another table > that have similar (but a little different) format without field content > confusion. I don't think so, but you can use the pg_dump utility with the -D option to generate plain-text dump files with INSERT statements and column specifiers; then copy-and-paste the rows you need via your favourite text editor. Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Crash of Postgresql on Windows
Emilie Laffray wrote: > Hello, > > I am sending this email since I am hitting a snag with postgresql. I > am currently running the following version of postgresql: > Postgresql 8.3.5 on Windows XP Pro 32bits > > I am getting a crash whenever I try to perform an update on some rows. > More precisely, I have recently imported a dataset and managed to work > through it easily. However, when performing one update I am getting > the following error: > > GMT PANIC: could not write to log file 40, segment 44 at offset > 8929280, length 1744896: Invalid argument 1. Can you run any other queries a. A SELECT b. A different UPDATE 2. Do you have any reason to think the filesystem may have become corrupted? 3. Do you have an antivirus scanner that might be interfering with PostgreSQL? That frequently causes strange problems. -- 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] field with Password
Thanks for your answers. Sorry for the questions but I'm new to Postgre :) The problem with a plain text password is that a user can see it by looking at the user table. Both suggest to use MD5. How can i use it? Any link, example about this would be very appreciated. Thanks in advance! Iñigo Barandiaran wrote: Hi. I would like to create a new table where one of the field would be a user password. Is there any data type for supporting this functionality? Something like Password DataType. I've taken a look of the available data types in PgAdmin Application and there is nothing similar to this. most commonly, passwords are stored as hashes, such as md5, rather than plaintext.'text' would be as suitable for this as anything, or bytea, if you want to store the hashes in binary. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Sort method: external merge
It's kind of lame questions, possibly I'm missing something but my doubts are as follow: When planner/executor needs to sort rowsit sorts whole records (i think so). So in the case when there are many wide columns it takes quite a lot of memory and sort goes out to the disk because it excess the work_mem. Isn't it possible to sort only fields that order matters & some row identifier/position (don't really know what - oid/ctid are tight to table but something temporary tight to 'resultset')? It would take much less memory and could be processed in the work_mem more often. # select sum(length(title)) from contacts; sum -- 4225 (1 row) # explain analyze SELECT * FROM contacts ORDER BY title; QUERY PLAN -- Sort (cost=6303.07..6369.65 rows=26634 width=269) (actual time=71.945..92.989 rows=26634 loops=1) Sort Key: title Sort Method: external sort Disk: 7368kB -> Seq Scan on contacts (cost=0.00..1456.34 rows=26634 width=269) (actual time=0.008..10.995 rows=26634 loops=1) -- 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] Crash of Postgresql on Windows
what about free disc space ? aren't you running out of ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Which is best, timestamp as float or integer ?
Hi. >From the manual I read that timestamps are stored as double but they can also be stored as 8 byte integers. I understand the precision problem with floats and the limited range of the integers and I feel confident that I should not worry about the Year 294276 or Year 5874897 problems (highest values that can be stored) so I ask for your experience on this matter when it comes to all other operations. On the x86-64 platform, I guess that 8 byte integers are generally handled faster than double floatingpoint numbers? Are operations like NOW + INTERVAL ' 232 HOURS' also faster with integers? Are there considerable timesavings? There are basically two operations I want to do (a lot!) SELECT ... ORDER BY my_timestamp; and SELECT ... WHERE my_timestamp > now(); -- or better using some variable for the now value to avoid all the function calls. Btw, what are the words of wisdom when it comes to creating index for fields of timestamp type, or other clever things? Best wishes. -- 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] field with Password
On Wed, Feb 04, 2009 at 11:09:51AM +0100, Iñigo Barandiaran wrote: > I would like to create a new table where one of the field would be a > user password. Is there any data type for supporting this functionality? > Something like Password DataType. I've taken a look of the available > data types in PgAdmin Application and there is nothing similar to this. you might find this post useful: http://www.depesz.com/index.php/2007/11/05/encrypted-passwords-in-database/ depesz -- Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/ jid/gtalk: dep...@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007 -- 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] Pet Peeves?
On Feb 3, 2009, at 11:55 PM, Guy Rouillier wrote: Craig Ringer wrote: An internal job scheduler with the ability to fire jobs on certain events as well as on a fixed schedule could be particularly handy in conjunction with true stored procedures that could explicitly manage transactions. Craig, what kind of "events" are you thinking about? Triggers are already pieces of code that run upon "certain events", namely insert, update or delete events. What others do you have in mind? What about LISTEN/NOTIFY events? That would be one way to create autonomous transactions. Cheers, M -- 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] field with Password
Iñigo Barandiaran wrote: Hi. I would like to create a new table where one of the field would be a user password. Is there any data type for supporting this functionality? Something like Password DataType. I've taken a look of the available data types in PgAdmin Application and there is nothing similar to this. most commonly, passwords are stored as hashes, such as md5, rather than plaintext.'text' would be as suitable for this as anything, or bytea, if you want to store the hashes in binary. -- 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] field with Password
In response to Iñigo Barandiaran : > Hi. > > > I would like to create a new table where one of the field would be a > user password. Is there any data type for supporting this functionality? > Something like Password DataType. I've taken a look of the available > data types in PgAdmin Application and there is nothing similar to this. You can store the md5-Hash instead the plaintext password. (char(32) and a length-check) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- 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] field with Password
Thanks!
This is great. I'm now implementing this functionality.
Thank you all.
You are great!
Best,
You should always salt your password
hashes.
Ie randomly generate a salt string,
the store this and the password hash:
insert
into auth (user_id, salt, password) values (1,'blah',md5('blah' +
'test'))
;
then to check the password
select
true from auth where user_id = 1 and password = md5( salt + 'test') ;
I tend to set a trigger function to
auto generate a salt and hash the password.
If you want to be really secure, use
both a md5 and sha1 hash, snice it has been proved you can generate
hash
collisions so you could use:
insert
into auth (user_id, salt, password) values (1,'blah',md5('blah' ||
'test')
|| sha1('blah' || 'test')) ;
then to check the password
select
true from auth where user_id = 1 and password = md5( salt || 'test')
||
sha1( salt || 'test') ;
Chris Ellis
"Raymond C.
Rodgers"
Sent by:
pgsql-general-ow...@postgresql.org
04/02/2009 14:34
To
Iñigo Barandiaran
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] field
with Password
Iñigo Barandiaran wrote:
Thanks!
Ok. I've found http://256.com/sources/md5/
library. So the idea is to define in the dataBase a Field of PlainText
type. When I want to insert a new user, I define a password, convert to
MD5 hash with the library and store it in the DataBase. Afterwards, any
user check should get the content of the DataBase of do the inverse
process
with the library. Is it correct?
Thanks so much!!
Best,
Well, you can use the built-in md5 function for this
purpose.
For instance, you could insert a password into the table with a
statement
like:
insert into auth_data (user_id, password) values (1,
md5('test'));
And compare the supplied password with something like:
select true from auth_data where user_id = 1 and
password
= md5('test');
You don't need to depend on an external library for this functionality;
it's built right into Postgres. Personally, in my own apps I write in
PHP,
I use a combination of sha1 and md5 to hash user passwords, without
depending on Postgres to do the hashing, but the effect is basically
the
same.
Raymond
**
If
you are not the intended recipient of this email please do not send it
on
to
others, open any attachments or file the email locally.
Please
inform the sender of the error and then delete the original email.
For
more information, please refer to
http://www.shropshire.gov.uk/privacy.nsf
**
Re: [GENERAL] field with Password
Thanks Raymond
That is something I wanted! It's Great if it is already integrated in
Postgre! Superb. This is much more easy.
Thank you All.
Best,
Iñigo Barandiaran wrote:
Well, you can use the built-in md5 function for this purpose. For
instance, you could insert a password into the table with a statement
like:
insert into auth_data (user_id, password) values (1,
md5('test'));
And compare the supplied password with something like:
select true from auth_data where user_id = 1 and
password
=
md5('test');
You don't need to depend on an external library for this functionality;
it's built right into Postgres. Personally, in my own apps I write in
PHP, I use a combination of sha1 and md5 to hash user passwords,
without depending on Postgres to do the hashing, but the effect is
basically the same.
Raymond
Re: [GENERAL] Crash of Postgresql on Windows
Andrew Gould wrote: What does '--' do? -- Is an SQL comment -- Tommy Gildseth DBA, Gruppe for databasedrift Universitetet i Oslo, USIT m: +47 45 86 38 50 t: +47 22 85 29 39 -- 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] field with Password
You should always salt your password hashes.
Ie randomly generate a salt string, the store this and the password hash:
insert into auth (user_id, salt, password) values
(1,'blah',md5('blah' + 'test')) ;
then to check the password
select true from auth where user_id = 1 and password = md5( salt +
'test') ;
I tend to set a trigger function to auto generate a salt and hash the
password.
If you want to be really secure, use both a md5 and sha1 hash, snice it
has been proved you can generate hash collisions so you could use:
insert into auth (user_id, salt, password) values
(1,'blah',md5('blah' || 'test') || sha1('blah' || 'test')) ;
then to check the password
select true from auth where user_id = 1 and password = md5( salt
|| 'test') || sha1( salt || 'test') ;
Chris Ellis
"Raymond C. Rodgers"
Sent by: pgsql-general-ow...@postgresql.org
04/02/2009 14:34
To
Iñigo Barandiaran
cc
pgsql-general@postgresql.org
Subject
Re: [GENERAL] field with Password
Iñigo Barandiaran wrote:
Thanks!
Ok. I've found http://256.com/sources/md5/ library. So the idea is to
define in the dataBase a Field of PlainText type. When I want to insert a
new user, I define a password, convert to MD5 hash with the library and
store it in the DataBase. Afterwards, any user check should get the
content of the DataBase of do the inverse process with the library. Is it
correct?
Thanks so much!!
Best,
Well, you can use the built-in md5 function for this purpose. For
instance, you could insert a password into the table with a statement
like:
insert into auth_data (user_id, password) values (1, md5('test'));
And compare the supplied password with something like:
select true from auth_data where user_id = 1 and password = md5('test');
You don't need to depend on an external library for this functionality;
it's built right into Postgres. Personally, in my own apps I write in PHP,
I use a combination of sha1 and md5 to hash user passwords, without
depending on Postgres to do the hashing, but the effect is basically the
same.
Raymond
**
If you are not the intended recipient of this email please do not send it on
to others, open any attachments or file the email locally.
Please inform the sender of the error and then delete the original email.
For more information, please refer to http://www.shropshire.gov.uk/privacy.nsf
**
Re: [GENERAL] Crash of Postgresql on Windows
On Wed, Feb 4, 2009 at 6:18 AM, Emilie Laffray wrote: > Hello, > > I am sending this email since I am hitting a snag with postgresql. I > am currently running the following version of postgresql: > Postgresql 8.3.5 on Windows XP Pro 32bits > > I am getting a crash whenever I try to perform an update on some rows. > More precisely, I have recently imported a dataset and managed to work > through it easily. However, when performing one update I am getting > the following error: > > GMT PANIC: could not write to log file 40, segment 44 at offset > 8929280, length 1744896: Invalid argument > This application has requested the Runtime to terminate it in an unusual > way. > Please contact the application's support team for more information. > 2009-02-04 12:02:34 GMT LOG: WAL writer process (PID 2916) exited > with exit code 3 > 2009-02-04 12:02:34 GMT LOG: terminating any other active server processes > 2009-02-04 12:02:34 GMT WARNING: terminating connection because of > crash of another server process > 2009-02-04 12:02:34 GMT DETAIL: The postmaster has commanded this > server process to roll back the current transaction and exit, because > another server process exited abnormally and possibly corrupted shared > memory. > 2009-02-04 12:02:34 GMT HINT: In a moment you should be able to > reconnect to the database and repeat your command. > 2009-02-04 12:02:34 GMT WARNING: terminating connection because of > crash of another server process > 2009-02-04 12:02:34 GMT DETAIL: The postmaster has commanded this > server process to roll back the current transaction and exit, because > another server process exited abnormally and possibly corrupted shared > memory. > 2009-02-04 12:02:34 GMT HINT: In a moment you should be able to > reconnect to the database and repeat your command. > 2009-02-04 12:02:34 GMT WARNING: terminating connection because of > crash of another server process > 2009-02-04 12:02:34 GMT DETAIL: The postmaster has commanded this > server process to roll back the current transaction and exit, because > another server process exited abnormally and possibly corrupted shared > memory. > 2009-02-04 12:02:34 GMT HINT: In a moment you should be able to > reconnect to the database and repeat your command. > 2009-02-04 12:02:34 GMT LOG: all server processes terminated; > reinitializing > 2009-02-04 12:02:35 GMT FATAL: pre-existing shared memory block is still > in use > 2009-02-04 12:02:35 GMT HINT: Check if there are any old server > processes still running, and terminate them. > > I am a bit lost on what to do next. I have tried running the query > several times even changing the parameters to make sure if it wasn't > something in the sql. > UPDATE gtable AS g > SET code = '00' -- 04 > WHERE g.code = '04' > AND g.cc = 'TW'; > > UPDATE gtable AS g > SET code = '00' -- 04 > WHERE g.d IN (6724652, 1673813); > > Those two queries do exactly the same thing but they both crash with > the same information, except for the length between the two queries. > What should I do? > > Emilie Laffray > > What does '--' do? Thanks, Andrew
Re: [GENERAL] installation
On Wed, Feb 4, 2009 at 9:09 AM, Kusuma Pabba wrote: > Hello all, > i am new to postgresql, i want to create tables in this > > i have followed > 10 Steps to Installing PostgreSQL which is chapter two of installing > postgresql > > i could follow upto step no 7 > but i am getting errors from step 8 > while following the steps i got message like postgresql is sucessfully > installed > > now my doubt if it is installed, how should i start working on it > i used > sudo su postgres -c psql template1 > password for my super user:** > then the prompt changed to postgres=# > > what does this implies can i start working on that or do i have to modify > furthur > > sorry for such a long mail and Thanks for any help!!! Quite short actually. Just about enough information. So I assume it is unix/linux. if you get postgre=# prompt, you're in psql shell. So you can start issue queries, create users/databases etc. For instance, try: select version(); in that shell. personally I would suggest following the steps: sudo su - postgres createuser createdb -U mydb exit psql -U mydb HTH -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PGAdmin and user privileges - what I do wrong?
Hi! Please help me a little. I used PGAdmin to administrate my databases. I created a new user named "zx". CREATE ROLE zx LOGIN ENCRYPTED PASSWORD '*' NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE; When I want to manually assign this user to a table, I have problem in the PGAdmin's Privilege tab. The privileges GroupBox have a ComboBox, named Role. This ComboBox is not containing the zx user in it's list. When I write the user (zx) into the combo , then the "Add" button have been disabled. I can write only an SQL cmd to add this privilege. When I do it, the Privilege list extended with this user, I can select it from the combobox. What I do wrong? Which property I need to set to I can Add privileges to the tables/objects with this user? Thanks for your help: dd -- 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] field with Password
Iñigo Barandiaran wrote: Thanks for your answers. Sorry for the questions but I'm new to Postgre :) The problem with a plain text password is that a user can see it by looking at the user table. Both suggest to use MD5. How can i use it? Any link, example about this would be very appreciated. md5 is a library function that converts a string to a 'hash', typically 32 bytes. so, when the user enters a password, you encode it with md5() and compare it with the stored hash.the md5 hash is not readily reversible (although brute force techniques can theoretically come up with strings that will generate the same hash) otoh, any table used to store security information probably should not be directly viewable by the end user. application programming techniques for ensuring application security go far beyond the charter of this email list, however. -- 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] field with Password
Thanks Raymond
That is something I wanted! It's Great if it is already integrated in
Postgre! Superb. This is much more easy.
Thank you All.
Best,
Iñigo Barandiaran wrote:
Thanks!
Ok. I've found http://256.com/sources/md5/
library. So the idea is to
define in the dataBase a Field of PlainText type. When I want to insert
a new user, I define a password, convert to MD5 hash with the library
and store it in the DataBase. Afterwards, any user check should get the
content of the DataBase of do the inverse process with the library. Is
it correct?
Thanks so much!!
Best,
Well, you can use the built-in md5 function for this purpose. For
instance, you could insert a password into the table with a statement
like:
insert into auth_data (user_id, password) values (1,
md5('test'));
And compare the supplied password with something like:
select true from auth_data where user_id = 1 and password
=
md5('test');
You don't need to depend on an external library for this functionality;
it's built right into Postgres. Personally, in my own apps I write in
PHP, I use a combination of sha1 and md5 to hash user passwords,
without depending on Postgres to do the hashing, but the effect is
basically the same.
Raymond
Re: [GENERAL] Pet Peeves?
Karsten Hilbert wrote: Craig, what kind of "events" are you thinking about? Triggers are already pieces of code that run upon "certain events", namely insert, update or delete events. What others do you have in mind? That's a good point, actually. I can't think of much you can't do with a trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. I would find ON CONNECT/DISCONNECT triggers very useful. Probably this is more similar to database-wide assertions. But a job scheduler would not help with that. Perhaps you intended your comment to fall into the "pet peeves" bucket rather than the "job scheduler" bucket. -- Guy Rouillier -- 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] Pet Peeves?
On Feb 3, 2009, at 3:41 PM, Peter Geoghegan wrote: What about postgreSQL's inability to re-order columns? Please don't point out that I shouldn't rely on things being in a certain order when I SELECT * FROM table. I'm well aware of that, I just generally have an aesthetic preference for a table's columns being in a certain order. Somewhat related, it would be nice if columns had a unique identifier in the catalog rather than just a sequence number for the table. This would make it possible to distinguish between altering a column versus dropping/adding when comparing schemas or detecting DDL changes. John DeSoi, Ph.D. -- 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] Pet Peeves?
On Wed, Feb 04, 2009 at 12:37:31PM -0500, Guy Rouillier wrote: > Karsten Hilbert wrote: Craig, what kind of "events" are you thinking about? Triggers are already pieces of code that run upon "certain events", namely insert, update or delete events. What others do you have in mind? >>> That's a good point, actually. I can't think of much you can't do >>> with a trigger (SECURITY DEFINER if necessary) on a table. Not >>> thinking straight. >> >> I would find ON CONNECT/DISCONNECT triggers very useful. Probably >> this is more similar to database-wide assertions. > > But a job scheduler would not help with that. Perhaps you intended your > comment to fall into the "pet peeves" bucket rather than the "job > scheduler" bucket. Yep, just like the Subject suggested ;-) The logic is a bit twisted but I was thinking "other kinds of events - oh, I'd like to be able to make something happen on this event". Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Pet Peeves?
I dream about db wide checks on tables, without need to write expensive triggers. Basically, something that would run a select query after insert/update/delete and based on result commit or rollback. unless there's something like that already in SQL (I am not aware of all features in sql2008 draft). -- 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] field with Password
On Wed, Feb 04, 2009 at 04:42:05PM +, chris.el...@shropshire.gov.uk wrote:
> If you want to be really secure, use both a md5 and sha1 hash, snice it
> has been proved you can generate hash collisions so you could use:
>
> insert into auth (user_id, salt, password) values
> (1,'blah',md5('blah' || 'test') || sha1('blah' || 'test')) ;
That sounds like a really *bad* idea to me; you've just given an
attacker two choices, MD5 is currently easier to attack than SHA1 but
that may change. If an attacker can find a password that hashes to the
same thing using one hash there's a reasonable chance it'll hash to the
same thing using the other and they will have broken your scheme (they
have 16 and 20 octets of state respectively, more than most passwords).
It would be much better just to use a stronger hash function to start
with.
--
Sam http://samason.me.uk/
--
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] Pet Peeves?
On Wed, 2009-02-04 at 14:09 +0900, Craig Ringer wrote: > Guy Rouillier wrote: > > Craig Ringer wrote: > >> An internal job scheduler with the ability to fire jobs on certain > >> events as well as on a fixed schedule could be particularly handy in > >> conjunction with true stored procedures that could explicitly manage > >> transactions. > > > > Craig, what kind of "events" are you thinking about? Triggers are > > already pieces of code that run upon "certain events", namely insert, > > update or delete events. What others do you have in mind? > > That's a good point, actually. I can't think of much you can't do with a > trigger (SECURITY DEFINER if necessary) on a table. Not thinking straight. True, but the trigger does its work while the user waits. If we have a 30 min task, we don't want to just tack that on to the end of a random insert. As A.M. says elsewhere, it would be good to have a trigger that fired a NOTIFY that was picked up by a scheduled job that LISTENs every 10 minutes for certain events. We need a place for code that is *not* directly initiated by a user's actions, yet works as part of a closed loop system. -- 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] Pet Peeves?
On Thu, 2009-01-29 at 13:16 +, Gregory Stark wrote: > I'm putting together a talk on "PostgreSQL Pet Peeves" for discussion at > FOSDEM 2009 this year. I have a pretty good idea what some them are of course, > but I would be interested to hear if people have any complaints from personal > experience. What would be most interesting is if you can explain an example of > when the problem caused real inconvenience to you, since sometimes it's hard > to see from a theoretical description where the real harm lies. > > So, what do people say? Is Postgres perfect in your world or does it do some > things which rub you the wrong way? > > Feel free to respond on-list or if you prefer in personal emails. I do intend > to use the ideas you give in my presentation so mark anything you wouldn't be > happy to see in a slide at a conference some day. > > -- > Gregory Stark > EnterpriseDB http://www.enterprisedb.com > Ask me about EnterpriseDB's Slony Replication support! > A couple of the things weighing in on my mind right now (I could be utterly wrong in all of them): - In place upgrade. Seriously, 3.5TB DBs make me cry... - Lack of good documentation for the binary copy input format for 'copy foo from stdin with binary'. Also, I don't *seem* to be able to use the same file that I copied out for copy in, which was a total surprise. - The fetch time with lots of byteas is really bad - selecting them out effectively *forces* scattered IO, even in what would normally be seq IO. It would be really nice if you did all the grabbing of rows that was required and then *at the end* fetched the appropriate bytea fields and re-aggregated them appropriately. This is a *HUGE* performance killer. - Bytea copy input format is *exceedingly* large - and the dual parser thing requiring two backslashes doesn't help!. W T F, I have got to be missing something. Consider the case where I want to write an int16_t. What should be sprintf(s, "\\%o", i); becomes sprintf(s, "%03o%03o", (i & 0x00FF), (i & 0xFF00)); - Query planning with heavy partitioning takes a huge hit, and this isn't helped by multiple cores on the same box. It would be very nice of subpartitions could simply be ignored if their parent partition wasn't required, but the planner still takes locks on them. - The ability to add a table to the inheritance structure without obtaining an acc ex lock would be really nice. - The ability to rebuild a table or index concurrently would be nice, especially if it automatically picked up interim changes and applied them before switching out and dropping the table. - Slony is really too slow to use for large quantities of data shipping. IIRC we had to move off of it when the DB was still sub 1 TB. - Lots of temp table creation/dropping plays havoc with the catalog tables and eventually requires a full maintenance window to resolve. - Creating an empty table with foreign keys requires an acc ex lock on all tables. Blargh. - It'd be nice if the query planner was more "stable" - sometimes the queries run fast, and then sometimes they randomly take 2 hours for a delete that normally runs in a couple of minutes. There's (alot) more, but I can't recall it all because I'm overall pretty happy with Postgres. -Mark -- 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] Pet Peeves?
On Mon, Feb 2, 2009 at 4:54 PM, Christopher Browne wrote: > - Stored procedures that can manage transactions (e.g. - contrast with > present stored functions that forcibly live *inside* a transaction > context; the point isn't functions vs procedures, but rather to have > something that can do txn management) IMO, once the current crop of in-progress features are rolled up (in place upgrade, hot standby, etc)...this is one of two 'must have' features...the other being revamped listen/notify. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] case sensitive db name?
I have a case sensitivity problem I dont understand. On a Solaris 10 with pg 8.2.6 (Sun build) I get problems when I do a CREATE DATABASE with a db name with case. On a Kubuntu machine with pg 8.2.7 it is not a problem. More specifically the problem arises after the CREATE DATABASE, it occurs when I try to connect to the newly created database. The script is as follows, execute with the command $ psql -U postgres -f create_db.sql # create_db.sql script start \c postgres create database Test1; ... create user etc... \c Test1 user1 #Script end at the connect command the script fails. when I list the databases, it shows the db name with small letters, as in: "test1" instead of "Test1" Why is this a problem on one installation but not another? and how can I fix the solaris installation? regards thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Array, bytea and large objects
I am trying to assess the db issues surrounding several constructs allowed in PG 8.3, including ARRAY, BYTEA and large objects (LO). We store a lot of data as encrypted XML structures (name-value pairs mostly) that can be updated many times during its lifetime (most updates occur over several days and then the data tends to change no more), as well as storing images and uploaded files (these rarely change and are only inserted/deleted). We currently use LO for all of these. We mostly use the JDBC library for access to PG. First, LOs seem to allow an OID column to be added to any number of tables, but is it true that the actual large object data is stored in a single table (pg_largeobject?). If so, wouldn't this become a bottleneck if LOs were used frequently? Even vacuuming and vacuumlo must create a lot of pressure on that one table if LOs are used extensively. And can you backup a table with an OID column and get only those LOs referenced in the dump? Does the JDBC library support LO streaming? Can I receive data, compress, encrypt and stream into the database as well as do the opposite when reading it back? If I have an "unlimited" number of name-value pairs that I'd like to get easy access to for flexible reports, could I store these in two arrays (one for name, the other for value) in a table so that if I had 10 name-value pairs or 200 name-value pairs, I could store these into a single row using arrays so I could retrieve all name-value pairs in a single SELECT from the db? How are these arrays stored -- does it use an underlying type like LO or BYTEA? How big can an LO get? Is it 2GB? How many LO fields can I have in a database? It seems that the LO may even be implemented as an OID with one or more BYTEA storage structure in the pg_largeobject table (loid,pageno,data). Is that true? How big is a "page"? Maybe an LO is more efficient than a BYTEA if it's bigger than one page? How big can a BYTEA get? Is it 1GB? At what size does it make more sense to store in LO instead of a BYTEA (because of all the escaping and such)? How many BYTEA fields can I have in a database? Are the BYTEA fields stored in the same table as the rest of the data? I believe this is yes, so a backup of that table will include the binary data, too, correct? How big can an ARRAY get? Is it 1GB? How many ARRAY fields can I have in a table or database? Are there limits? Are the ARRAY fields stored in the same table as the rest of the data? Sorry for all the questions, but I'm trying to research it but the info is not always clear (and perhaps some of the stuff I find is not even true). I am wondering if when my encrypted XML data is small, should I choose to store it in a table using BYTEA so that each "record" in my application (which uses the encrypted XML name-value storage) is not forced to be in a single pg_largeobject table, and use LO when my data reaches a threshold size? Thoughts? Thanks, David -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SELECT on a table with Time values
Hi all!! I have a simple table with two column. The first column is a time type and the other is a integer type. This table have datas from each minute of day. Example : 15:00:00, 15:01:00, 15:02:00 etc... I want a SELECT command wich return for me the datas from each five minutes of day. Example: 15:00:00, 15:05:00, 15:10:00, 15:15:00 etc There is a way to do this?
Re: [GENERAL] SELECT on a table with Time values
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Anderson dos Santos Donda Sent: Wednesday, February 04, 2009 12:05 PM To: pgsql-general@postgresql.org Subject: [GENERAL] SELECT on a table with Time values Hi all!! I have a simple table with two column. The first column is a time type and the other is a integer type. This table have datas from each minute of day. Example : 15:00:00, 15:01:00, 15:02:00 etc... I want a SELECT command wich return for me the datas from each five minutes of day. Example: 15:00:00, 15:05:00, 15:10:00, 15:15:00 etc There is a way to do this? >> How about: SELECT integer_column, timestamp_column FROM my_table WHERE EXTRACT(MINUTE FROM timestamp_column) % 5 = 0 <<
Re: [GENERAL] case sensitive db name?
Thomas Finneid wrote: > I have a case sensitivity problem I dont understand. > > On a Solaris 10 with pg 8.2.6 (Sun build) I get problems when I do a > CREATE DATABASE with a db name with case. > > On a Kubuntu machine with pg 8.2.7 it is not a problem. > > More specifically the problem arises after the CREATE DATABASE, it > occurs when I try to connect to the newly created database. You are creating one database with its name quoted, or accessing it that way. PostgreSQL folds identifiers to lower-case unless you quote them, in which case it leaves them alone. CREATE DATABASE Test1 => test1 CREATE DATABASE TEST1 => test1 CREATE DATABASE "Test1" => Test1 Oh - most recent 8.2 is 8.2.11. too. -- 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] Pet Peeves?
On Wednesday 04 February 2009 20:36:24 Grzegorz Jaśkiewicz wrote: > I dream about db wide checks on tables, without need to write > expensive triggers. > Basically, something that would run a select query after > insert/update/delete and based on result commit or rollback. > unless there's something like that already in SQL (I am not aware of > all features in sql2008 draft). Sounds like ASSERTION, standard SQL feature. Certainly interesting. -- 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] Pet Peeves?
On Wednesday 04 February 2009 19:39:42 John DeSoi wrote: > Somewhat related, it would be nice if columns had a unique identifier > in the catalog rather than just a sequence number for the table. This > would make it possible to distinguish between altering a column versus > dropping/adding when comparing schemas or detecting DDL changes. It would also make quite a bit of internal code much simpler if pg_attribute had OIDs. I'm not sure if the demand for that is high beyond you, though. -- 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] Which is best, timestamp as float or integer ?
On Wednesday 04 February 2009 15:48:41 A B wrote: > From the manual I read that timestamps are stored as double but they > can also be stored as 8 byte integers. The advantage of the integer storage is mainly that calculations and comparisons have a predictable error and don't suffer from some of the funny business that comes with floating-point calculations. Performance is probably similar. Integer storage is now the default (and has been in some distributions for a while), and would already have been the sole world order if 8-byte integer support were universally available. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Elapsed time between timestamp variables in Function
Hi ! I saw previous postings about elapsed time between 2 timestamps, using SELECT EXTRACT... I have similar question, but it's not in a select statement, but between 2 variables in a function. To keep it simple, I have 2 variables, let's say A and B, both TimeStamp. Now I would like to know the absolute value of elapsed seconds between the 2 timestamps. Has to be absolute value, because can be positive or negative, depends if A > B or A < B. I tried with age(A, B), but that gives me something like 00:00:01, not really numeric value for number of seconds. Many thanks in advance ! Nico
Re: [GENERAL] ramblings about password exposure (WAS: field with Password)
> On Wed, Feb 04, 2009 at 09:34:56AM -0500, Raymond C. Rodgers wrote: > > You don't need to depend on an external library for this > functionality; > > it's built right into Postgres. Personally, in my own apps I write in > > PHP, I use a combination of sha1 and md5 to hash user passwords, > > without depending on Postgres to do the hashing, but the effect is > > basically the same. > > Doing the hashing outside PG would reduce the chance of the password > being exposed, either accidentally by, say, turning on statement > logging, or maliciously. A general rule with passwords is to throw > away > any copy of a plain text password as quickly as possible, sending the > password over to another process would go against this. > Agreed. Another benefit of this is the hashing support in PHP is more flexible. I personally use the hash() function to get a SHA-256 hash instead of the weaker sha1 or md5. -- 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] Elapsed time between timestamp variables in Function
On 04/02/2009 21:59, Nico Callewaert wrote: > To keep it simple, I have 2 variables, let's say A and B, both > TimeStamp. Now I would like to know the absolute value of elapsed > seconds between the 2 timestamps. Has to be absolute value, because > can be positive or negative, depends if A > B or A < B. I tried with > age(A, B), but that gives me something like 00:00:01, not really > numeric value for number of seconds. I had to do something similar recently (interval to minutes), and rolled my own: create or replace function interval_to_minutes(interval) returns integer as $$ select cast( ( extract(hour from $1) * 60 + extract(minute from $1) ) as integer ); $$ language sql stable; Then you can do: select abs(interval_to_minutes(A - B)); HTH, Ray. -- Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland r...@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals -- -- 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] Pet Peeves?
Mark Roberts wrote: - It'd be nice if the query planner was more "stable" - sometimes the queries run fast, and then sometimes they randomly take 2 hours for a delete that normally runs in a couple of minutes. I was going to stay silent, because my pet peeves were already covered or had been fixed (btw, thanks to whomever fixed sql standard "quote escaping a quote" all those years ago :-) ). But Mark's suggestion is excellent. Plan stability / Stored planner outlines / whatever you want to call it, is hugely valuable when data volumes change so frequently that the planner never knows the "good" stats from the "bad", and also when upgrading to lessen the "OMG, I have to add set enable_nestloop=false to 48 billion queries just to overcome new planner quirks" situations. $OTHER_BIG_RDBMS have had this to varying degrees for a while (stored outlines/plan stability in Oracle; bind in DB2; whatever crap name MS gave their half-arsed version), and when it's mature, the certainty around execution is a life-saver. And just to chime in on the already mentioned things: - in-place upgrades - replication engine in the core - true stored procedures - job scheduler in the core In all, a short list, which is an oblique way of saying thanks to everyone for the enormous strides that have been made in the last few years :-) Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.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] Sort method: external merge
On Wed, 2009-02-04 at 02:15 -0800, wstrzalka wrote: > Isn't it possible to sort only fields that order matters & some row > identifier/position (don't really know what - oid/ctid are tight to > table but something temporary tight to 'resultset')? It would take > much less memory and could be processed in the work_mem more often. ctid is not sufficient for all uses of Sort, because sometimes you are sorting tuples that don't come from a table. Consider sorting the results after a function has been applied to some other field -- how can you get the result of that function? What you're talking about is kind of like building an index on the fly. You might as well just make a BTree normally, which is exactly the kind of result structure you are suggesting: a sorted mapping between the sort key and the ctid. Even in the case where you already have an index, the index scan can actually be more expensive. The reason is that accessing tuples in a table by ctid is random access (except in the case of a clustered table), while pulling the tuples from an external sort is more sequential. If you are interested, you can take this discussion to pgsql-hackers. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] debugging plpgsql functions
Hi all, I have a few plpgsql functions to debug to see why they are slow. They consist of a bunch of sql statements using new.* / old.* variables (ie not using "EXECUTE", the sql is being called directly). Is there a way to capture the actual sql that's being executed with variables substituted in, or even an easy way to log the variables being used? I couldn't see anything in the manual but maybe I missed something - any pointers appreciated. I tried http://pgfoundry.org/projects/edb-debugger/ but after loading the module, the functions wouldn't run properly (can't remember the error message but I can do it again if need be). -- Postgresql & php tutorials http://www.designmagick.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] Elapsed time between timestamp variables in Function
2009/2/4 Nico Callewaert : > Hi ! > > I saw previous postings about elapsed time between 2 timestamps, using > SELECT EXTRACT... > I have similar question, but it's not in a select statement, but between 2 > variables in a function. > > To keep it simple, I have 2 variables, let's say A and B, both TimeStamp. > Now I would like to know the absolute value of elapsed seconds between the 2 > timestamps. Has to be absolute value, because can be positive or negative, > depends if A > B or A < B. > I tried with age(A, B), but that gives me something like 00:00:01, not > really numeric value for number of seconds. > > Many thanks in advance ! > Nico EXTRACT(EPOCH FROM age(A,B)) ? Osvaldo -- 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] debugging plpgsql functions
Chris wrote: Hi all, I have a few plpgsql functions to debug to see why they are slow. They consist of a bunch of sql statements using new.* / old.* variables (ie not using "EXECUTE", the sql is being called directly). Is there a way to capture the actual sql that's being executed with variables substituted in, or even an easy way to log the variables being used? I couldn't see anything in the manual but maybe I missed something - any pointers appreciated. I tried http://pgfoundry.org/projects/edb-debugger/ but after loading the module, the functions wouldn't run properly (can't remember the error message but I can do it again if need be). Debugging sql functions is sometimes a not to fun event. pgdebugger has some gotchas that will bite you big time. One big item don't run pgdebugger in a production machine i've had lock process and its done some other odd things. I normally run the pgdebugger on a windows install as it will install it for you. and use pgadmin two step through the code. One option use Raise Notice to see whats in a variable example RAISE NOTICE 'Var1 %, Var2 %, ' , MemoryVar1, MemoryVar2 ; then look whats returned to the client. Again i use pgAdmin for this as it shows me all the messages sent from the server and keeps a nice easy to read history. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] running postgres
may this be a silly doubts but , i am new to postgres, please answer to these:: /usr/local/pgsql/bin/psql test test=# sudo su postgres -c psql template1 template=# what is the difference between the above two and, why is the path different in both cases which should i use now how can i create a user in test or template when i give create user it is asking for create role , how should i create role? Thanks & Regards kusuma.p -- 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] running postgres
On Thu, Feb 5, 2009 at 15:35, Kusuma Pabba wrote: > > may this be a silly doubts but , i am new to postgres, please answer to > these:: > > /usr/local/pgsql/bin/psql test > test=# > > > sudo su postgres -c psql template1 > template=# > > > what is the difference between the above two and, > why is the path different in both cases > which should i use now first you connect to the database test in the second one you run the command "psql" on the database template1 -c COMMAND run only single command (SQL or internal) and exit > > how can i create a user in test or template > when i give create user > it is asking for create role , how should i create role? you can create a user with "createuser" commandline tool or use the CREATE ROLE sql command (http://www.postgresql.org/docs/8.3/static/sql-createrole.html) and then use the GRANT command (http://www.postgresql.org/docs/8.3/static/sql-grant.html) to give the user rights to the database you want. See the GRANT ... ON DATABASE part for this. -- [ Clemens Schwaighofer -=:~ ] [ IT Engineer/Manager] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclai...@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: (Questioning the planner's mind) - was Re: [GENERAL] Fastest way to drop an index?
On Feb 4, 2009, at 5:23 AM, Phoenix Kiula wrote: On Wed, Feb 4, 2009 at 5:13 AM, Tom Lane wrote: Phoenix Kiula writes: Index Scan using new_idx_testimonials_userid on testimonials (cost=0.00..157.78 rows=1 width=9) (actual time=8809.715..8809.715 rows=0 loops=1) Index Cond: ((user_id)::text = 'superman'::text) Filter: ((title_encrypted)::text = 'b333dc1b0992cb8c70b58a418211389a'::text) Total runtime: 8809.750 ms This is using the index to fetch the rows that match user_id = 'superman', and then testing each fetched row to see if it has the desired value of title_encrypted. The fact that hardly any rows pass the filter test tells nearly nothing about how long this should be expected to run. The rather high estimated cost suggests that the planner thinks there are several dozen rows matching the index condition, and the actual runtime suggests that there are actually hundred or thousands of 'em. If so, your problem is that you need a different index. I'd bet on an index on title_encrypted being more useful for this query than the one on user_id; or you could experiment with a two-column index. Thanks Tom. My thinking exactly. So I have made a two column index on (user_id, title_encrypted) already. It's done. But the planner keeps insisting on using the "user_id" as you see from that EXPLAIN ANALYZE. This was done when the other two-col index already exists! Could I force the 2-col index? I googled for "force index postgresql" and came upon this discussion - http://archives.postgresql.org/pgsql-sql/2006-02/msg00190.php - which suggests that the planner may be selecting indexes based on "cost'. I am not too technically savvy, but I think this means that given the choice of these two scenarios... 1. Search through "user_id" index, and then limit it by "title_encrypted" or 2. Search through "user_id, title_encrypted" 2-col index ...the planner decides that it is less resource intensive to go through the somewhat smaller user_id index and then limit it (i.e., scenario 1) than to wade through the bigger second index. The EXPLAIN ANALYZE of the second form of the query would tell. I suppose you'd rather not lose your new_idx_testimonials_userid index, but you can drop that in a transaction and roll back. I don't think that'd impact other transactions at all, seeing how MVCC just marks it deleted for your session, but I can't promise you that. Caution is advised ;) Try: BEGIN; EXPLAIN ANALYZE SELECT * FROM testimonials WHERE userid='superman' AND title_encrypted='b333dc1b0992cb8c70b58a418211389a'; DROP INDEX new_idx_testimonial_userid; ANALYZE testimonials; EXPLAIN ANALYZE SELECT * FROM testimonials WHERE userid='superman' AND title_encrypted='b333dc1b0992cb8c70b58a418211389a'; ROLLBACK; Am I on the right track? If I am, well what's the way around this? How can I make the planner make use of the 2-col index? Or if my understanding is not right, why is the scenario 1 being chosen to begin with? Thanks for any thoughts! This single query, which used to be much faster than this, is now slowing down our operations by about 8 second per query! Let me know if you need to know any pgsql.conf settings. Only index related setting I know of are these: enable_indexscan = on enable_bitmapscan= off enable_nestloop = on I think for this case the planner might be trying a bitmap scan and you upped the costs of that significantly by turning it 'off'. The earlier query-plan will show. There shouldn't be any need to disable bitmap scans in the first place, you might want to try that same query with bitmap scans enabled and see how it performs. Another possible cause is that both indexes are quite large and they can't both fit into memory. I suspect the new_idx_tesimonials_userid index gets used in other queries as well, so it's likely in the cache. I guess that could make using that index and scan through the results faster than reading the new index from disk. Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,498a9909747034241410875! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
