Re: [GENERAL] Which version will this run on?
On Sun, 31 Jan 2010 16:11:46 -0500, t...@sss.pgh.pa.us (Tom Lane) wrote: >A quick test says that it works back to 7.4, which is the oldest >version that is supported at all anymore. I don't think you need >to worry too much. Thank you for the answer. Had it worked on just any 8.x-version, I would have been more than happy :-) -- Mads Lie Jensen - m...@gartneriet.dk - ICQ #25478403 Gartneriet - http://www.gartneriet.dk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Questions on PostGreSQL Authentication mechanism...
Hi, we have latest PostGreSQL setup and it allows everyone to connect. When
I do \du, it gives following output and it is same for all users.
TechDB=# \du
List of roles
Role name | Superuser | Create role | Create DB | Connections | Member
of
---+---+-+---+-+-
sonal | no| no | no| no limit|
{from_ldap}
sundar | no| no | no| no limit|
{from_ldap}
...
..
Moreover, anyone can connect to databases as postgres user without giving
password.
I am not aware how above setup has been made but I want to get rid of them.
Could anyone please help me in below questions?
1. When user connects to TechDB database(or any other) as a "postgres"
user, it should ask for password.
2. Remove all above users(listed with \du) and create someof users and
they will have only table creating privileges.
Thanks.
[GENERAL] combine SQL SELECT statements into one
Good Evening, Good Morning Wherever you are whenever you may be reading this. I am new to this email group and have some good experience with SQL and PostgreSQL database. I am currently working on a PHP / PostgreSQL project and I came upon something I could not figure out in SQL. I was wondering if anyone here could take a look and perhaps offer some guidance or assistance in helping me write this SQL query. Please Consider the following information: --- I have a postgresql table called 'inventory' that includes two fields: 'model' which is a character varying field and 'modified' which is a timestamp field. So the table inventory looks something like this: model modified - -- I778288176 2010-02-01 08:27:00 I778288176 2010-01-31 11:23:00 I778288176 2010-01-29 10:46:00 JKLM112345 2010-02-01 08:25:00 JKLM112345 2010-01-31 09:52:00 JKLM112345 2010-01-28 09:44:00 X22TUNM765 2010-01-17 10:13:00 V8893456T6 2010-01-01 09:17:00 Now with the table, fields and data in mind look at the following three queries: SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01'; SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20'; SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01'; All three of the above queries work and provide results. However, I want to combine the three into one SQL Statement that hits the database one time. How can I do this in one SQL Statement? Is it possible with sub select? Here is what result I am looking for from one SELECT statement using the data example from above: count1 | count2 | count3 --- 2 2 4 Can this be done with ONE SQL STATEMENT? touching the database only ONE time? Please let me know. Thanx> :) NEiL
Re: [GENERAL] combine SQL SELECT statements into one
Hi, If I were you, I worked like this. First make a union of those three query Then make a crosstab : http://www.postgresonline.com/journal/index.php?/archives/14-CrossTab-Queries-in-PostgreSQL-using-tablefunc-contrib.html documented here : http://www.postgresql.org/docs/8.4/interactive/tablefunc.html Le dimanche 31 janvier 2010 à 23:36 -0800, Neil Stlyz a écrit : > Good Evening, Good Morning Wherever you are whenever you may be > reading this. > > I am new to this email group and have some good experience with SQL > and PostgreSQL database. > > > I am currently working on a PHP / PostgreSQL project and I came upon > something I could not figure out in SQL. I was wondering if anyone > here could take a look and perhaps offer some guidance or assistance > in helping me write this SQL query. > > Please Consider the following information: > --- > > I have a postgresql table called 'inventory' that includes two fields: > 'model' which is a character varying field and 'modified' which is a > timestamp field. > > So the table inventory looks something like this: > > > model modified > --- > I7782881762010-02-01 08:27:00 > I778288176 2010-01-31 11:23:00 > I778288176 2010-01-29 10:46:00 > JKLM112345 2010-02-01 08:25:00 > JKLM112345 2010-01-31 09:52:00 > JKLM112345 2010-01-28 09:44:00 > X22TUNM7652010-01-17 10:13:00 > V8893456T6 2010-01-01 09:17:00 > > > > Now with the table, fields and data in mind look at the following > three queries: > > > > SELECT COUNT(distinct model) FROM inventory WHERE modified >= > '2010-02-01'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= > '2010-01-20'; > SELECT COUNT(distinct model) FROM inventory WHERE modified >= > '2010-01-01'; > > > > All three of the above queries work and provide results. However, I > want to combine the three into one SQL Statement that hits the > database one time. How can I do this in one SQL Statement? Is it > possible with sub select? > > > > Here is what result I am looking for from one SELECT statement using > the data example from above: > > > > count1 | count2 | count3 > --- > 2 2 4 > > > Can this be done with ONE SQL STATEMENT? touching the database only > ONE time? > > Please let me know. > > > > Thanx> :) > NEiL > > > > >
Re: [GENERAL] combine SQL SELECT statements into one
Hi pgsql-general-ow...@postgresql.org wrote on 02/01/2010 07:36:55 AM: > Good Evening, Good Morning Wherever you are whenever you may be reading this. > > snip > > count1 | count2 | count3 > --- > 2 2 4 > > Can this be done with ONE SQL STATEMENT? touching the database only ONE time? You can do the following: SELECT (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01') AS "COUNT_1", (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20') AS "COUNT_2", (SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01') AS "COUNT_3" ; PostgreSQL allows sub-queries in the select list as long as the sub-query returns one column Job done > Please let me know. > > Thanx> :) > NEiL > Chris Ellis ** 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 ** Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT. **
Re: [GENERAL] How to test my new install
On 01/02/2010 01:15, ray wrote: > I have just installed 8.4 on an XP. My intent is to use it with Trac > and Apache. > > I would like to validate the installation of pgsql. What would be a > good method to make sure that pgsql is in there right? Not sure what you mean. I'd imagine the thing to do is to run your application against it in a test setup and check tat everything works as you expect. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Questions on PostGreSQL Authentication mechanism...
On 01/02/10 07:35, dipti shah wrote: Moreover, anyone can connect to databases as postgres user without giving password. I am not aware how above setup has been made but I want to get rid of them. Could anyone please help me in below questions? You'll want to read Chapter 19 of the manuals followed by Chapter 20. http://www.postgresql.org/docs/8.4/static/client-authentication.html http://www.postgresql.org/docs/8.4/static/user-manag.html >1. When user connects to TechDB database(or any other) as a "postgres" > user, it should ask for password. I would guess your pg_hba.conf file is set to allow "trust" access. You will probably want "md5" passwords. You can also GRANT access to databases using the permissions system. >2. Remove all above users(listed with \du) and create someof users and > they will have only table creating privileges. You can DROP USER (or DROP ROLE) to remove users, but you'll want to reallocate any tables they own. You can GRANT and REVOKE various permissions, but I don't think you can just create tables without being able to access them afterwards. You could write a function that does that for you though. HTH -- Richard Huxton Archonet Ltd -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
Thanks Richard. those chapters are very useful. I got to know most of concepts but didn't find the location of pg_hba.conf file so that I can verify it. I have connected to my database using "postgres" user. Could you tell me how to open pg_hba.conf file? Thanks. On Mon, Feb 1, 2010 at 3:06 PM, Richard Huxton wrote: > On 01/02/10 07:35, dipti shah wrote: > >> >> Moreover, anyone can connect to databases as postgres user without giving >> password. >> >> I am not aware how above setup has been made but I want to get rid of >> them. >> Could anyone please help me in below questions? >> > > You'll want to read Chapter 19 of the manuals followed by Chapter 20. > http://www.postgresql.org/docs/8.4/static/client-authentication.html > http://www.postgresql.org/docs/8.4/static/user-manag.html > > > >1. When user connects to TechDB database(or any other) as a "postgres" > > user, it should ask for password. > > I would guess your pg_hba.conf file is set to allow "trust" access. You > will probably want "md5" passwords. You can also GRANT access to databases > using the permissions system. > > > >2. Remove all above users(listed with \du) and create someof users and > > they will have only table creating privileges. > > > You can DROP USER (or DROP ROLE) to remove users, but you'll want to > reallocate any tables they own. You can GRANT and REVOKE various > permissions, but I don't think you can just create tables without being able > to access them afterwards. You could write a function that does that for you > though. > > HTH > > -- > Richard Huxton > Archonet Ltd >
Re: [GENERAL] Questions on PostGreSQL Authentication mechanism...
On 01/02/10 10:24, dipti shah wrote: Thanks Richard. those chapters are very useful. I got to know most of concepts but didn't find the location of pg_hba.conf file so that I can verify it. I have connected to my database using "postgres" user. Could you tell me how to open pg_hba.conf file? It should be with your other configuration files: postgresql.conf, pg_ident.conf. Where will depend on how you installed it. If you're on Windows, it's probably in the main PostgreSQL folder on drive C: If a package manager on Linux/Unix look in /etc/postgresql/... If you compiled from source, probably something like /usr/local/postgresql/data/ It's a text file and you'll need to restart PostgreSQL to pick up your new settings. -- 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] How to test my new install
Raymond, Thank you for responding to my question. I am sorry that I wasn't clear. My concern is that when I start up my tool stack and it doesn't work, that I will have an entire chain to troubleshoot. If I could isolate each tool as I install it, and validate that it is working, it will be easier to assess any other difficulties. Ray - Original Message - From: "Raymond O'Donnell" To: "ray" Cc: Sent: Monday, February 01, 2010 3:33 AM Subject: Re: [GENERAL] How to test my new install > On 01/02/2010 01:15, ray wrote: > > I have just installed 8.4 on an XP. My intent is to use it with Trac > > and Apache. > > > > I would like to validate the installation of pgsql. What would be a > > good method to make sure that pgsql is in there right? > > Not sure what you mean. I'd imagine the thing to do is to run your > application against it in a test setup and check tat everything works as > you expect. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Unusual table size and very slow inserts
Hello, I have a software that uses Posgtres 8.4.2 on Windows. I have a database with data splitted into schemas, so that every schema replicates the same set of tables. One of the table is called "code": it has 16 columns, almos all numerics except for a carachtervarying(1024) and two text fields. It holds usually a few thousands record at most, then the file size of the table is usually around few hundred kbytes. In only one case so far, the "code" table with 442 record has a size of 18MB. If I run an vacuum full and a reindex it shrinks to less than 100KB. If I use the software to delete the rows and reinsert the same records it explodes again to 18MB. I have backed up the table (from a WIndows7 instance running on a virtual machine) and recovered it on another database (running on a"real" Windows Vista) and tried the same things with the same results. Then I have dropped the table, recreated it and reinserted the records: the anomaly has disappeared. I really don't know what to look for. I cannot ignore the problem because schemas are created by the software on software users request and it could reappear in any schema. I really don't have a clue: I would be happy to further study the problem but I don't know in which direction I have to go. Can someone point me in some (hopefully good) direction? -- == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Sede Operativa Via Gereschi 36 - 56126- Pisa tel.: +39-050- 580959 tel/fax: +39-050-9711344 web: www.archicoop.it == -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] combine SQL SELECT statements into one
On Sun, Jan 31, 2010 at 11:36:55PM -0800, Neil Stlyz wrote:
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-02-01';
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-20';
> SELECT COUNT(distinct model) FROM inventory WHERE modified >= '2010-01-01';
>
> All three of the above queries work and provide results. However,
> I want to combine the three into one SQL Statement that hits the
> database one time. How can I do this in one SQL Statement? Is it
> possible with sub select?
If you only wanted a single table scan, you could use CASE:
SELECT
COUNT(DISTINCT CASE WHEN modified >= '2010-02-01' THEN model END) AS c1,
COUNT(DISTINCT CASE WHEN modified >= '2010-01-20' THEN model END) AS c2,
COUNT(DISTINCT CASE WHEN modified >= '2010-01-01' THEN model END) AS c3
FROM inventory
WHERE modified >= '2010-01-01';
Note that the final WHERE clause isn't really needed, it'll just make
things a bit faster and give PG the opportunity to use an INDEX if it
looks helpful. If you're generating the above from code, you may want
to use the LEAST function in SQL rather than working out the smallest
value in your code, i.e:
WHERE modified >= LEAST('2010-02-01','2010-02-01','2010-02-01');
--
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] How to test my new install
On 01/02/2010 10:48, ray joseph wrote: > Raymond, > > Thank you for responding to my question. I am sorry that I wasn't clear. > > My concern is that when I start up my tool stack and it doesn't work, that I > will have an entire chain to troubleshoot. If I could isolate each tool as > I install it, and validate that it is working, it will be easier to assess > any other difficulties. Well, to see if PostgreSQL is working, the simplest thing is to try connecting with psql and see if it responds. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] Can LISTEN/NOTIFY deal with more than 100 every second?
Gavin Mu wrote: CREATE OR REPLACE RULE send_notify AS ON INSERT TO log DO ALSO NOTIFY logevent; .. when I use 3 similar programs to feed data, which means about 75 events every second, I found that Postgres didn't send NOTIFY opportunely, since the client do SELECT query every several hundreds seconds, which is too long to be acceptable. Hello Gavin, The following might help from the notify docs: "NOTIFY behaves like Unix signals in one important respect: if the same notification name is signaled multiple times in quick succession, recipients might get only one notification event for several executions of NOTIFY." So if your notify for instance could also add a unique number to the notification name, then it will probably work as expected. Regards, Yeb Havinga -- 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] How to test my new install
Raymond, Thank you. Yes, that sounds like a great step. I am new to this so I could use a little help: What do you mean to connect to it and how would I do it? Ray - Original Message - From: "Raymond O'Donnell" To: "ray joseph" Cc: Sent: Monday, February 01, 2010 6:17 AM Subject: Re: [GENERAL] How to test my new install > On 01/02/2010 10:48, ray joseph wrote: > > Raymond, > > > > Thank you for responding to my question. I am sorry that I wasn't clear. > > > > My concern is that when I start up my tool stack and it doesn't work, that I > > will have an entire chain to troubleshoot. If I could isolate each tool as > > I install it, and validate that it is working, it will be easier to assess > > any other difficulties. > > Well, to see if PostgreSQL is working, the simplest thing is to try > connecting with psql and see if it responds. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > -- 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] How to test my new install
On Mon, Feb 01, 2010 at 06:21:55AM -0600, ray joseph wrote: > I am new to this so I could > use a little help: What do you mean to connect to it and how would I do it? I'd have a flick through the manual if I were you; the following is a reasonable place to start: http://www.postgresql.org/docs/8.4/static/tutorial-start.html -- 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] How to test my new install
On 01/02/2010 12:21, ray joseph wrote: > Raymond, > > Thank you. Yes, that sounds like a great step. I am new to this so I could > use a little help: What do you mean to connect to it and how would I do it? psql is the command-line client for Postgres, which lets you connect to databases and run SQL commands directly against them. You can read about it here: http://www.postgresql.org/docs/8.4/interactive/app-psql.html On the XP machine, open a command prompt and type [path to PG install dir]\bin\pgsql -U [username] [databasename] ...and see if you can connect to the database. If you can connect and run queries, then the DB server is running. If your app stack is connecting from a different machine, I'd then try connecting from that machine to see if there are any network issues that would prevent the connection. Ray. -- Raymond O'Donnell :: Galway :: Ireland r...@iol.ie -- 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] How to test my new install
Raymond, Thank you very much. This is exactly what I was looking for. I'll jump right into it. Ray - Original Message - From: "Raymond O'Donnell" To: "ray joseph" Cc: Sent: Monday, February 01, 2010 7:08 AM Subject: Re: [GENERAL] How to test my new install > On 01/02/2010 12:21, ray joseph wrote: > > Raymond, > > > > Thank you. Yes, that sounds like a great step. I am new to this so I could > > use a little help: What do you mean to connect to it and how would I do it? > > psql is the command-line client for Postgres, which lets you connect to > databases and run SQL commands directly against them. You can read about > it here: > > http://www.postgresql.org/docs/8.4/interactive/app-psql.html > > On the XP machine, open a command prompt and type > > [path to PG install dir]\bin\pgsql -U [username] [databasename] > > ...and see if you can connect to the database. If you can connect and > run queries, then the DB server is running. > > If your app stack is connecting from a different machine, I'd then try > connecting from that machine to see if there are any network issues that > would prevent the connection. > > Ray. > > -- > Raymond O'Donnell :: Galway :: Ireland > r...@iol.ie > -- 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] Can LISTEN/NOTIFY deal with more than 100 every second?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 > I am prototyping a system which sends all INSERT/UPDATE/DELETE events > to a third party software, I do: ... > CREATE OR REPLACE RULE send_notify AS ON INSERT TO log DO > ALSO NOTIFY logevent; This is better off as a statement-level trigger, so you won't have to issue one notify per insert, but one per group of inserts. It also looks like you might be reinventing a wheel - maybe can you do what you want with Slony's log shipping? > When I inserted data to TABLE data with the rate of about 25 every > second, the client can receive the notifies without any problem, and > when I use 3 similar programs to feed data, which means about 75 > events every second, I found that Postgres didn't send NOTIFY > opportunely, since the client do SELECT query every several hundreds > seconds, which is too long to be acceptable. > > So what I want to know is, is there anything wrong with my idea? and > how frequence can LISTEN/NOTIFY support? Thanks. The question is, how often does the other side need to get notified? If things are coming in that fast, there is no need for the client to check for notifies, just have it continously poll your log table. We can probably answer your question better if it were clearer what your program is doing and where it is failing, particularly this bit: "the client do SELECT query every several hundreds seconds" - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201002010912 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAktm4f8ACgkQvJuQZxSWSshLUQCg2/TLbE0L8o6SncclQg3eNtVX UUsAnjRx9Ki6j0ATebUqTXjEs9zMrQIu =1cnk -END PGP SIGNATURE- -- 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] Versions RSS page is missing version(s)
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 >> I'm not sure how useful that is. Surely while we encourage people to run >> a recent major version, we also want to encourage people who will not >> or cannot upgrade to at least be running the latest revision of a branch, >> no matter how old it is? > We don't support 7.3. Not even if you run the latest version. No, but I imagine we still would encourage people to run the latest revision of it. Come this time next year, I hope that we'll tell people on 7.4.2 to upgrade to 9.0 as soon as possible, but to upgrade to 7.4.27 *immaediately*. >> How about a compromise? We add a new field to that XML so we can state >> that it is unsupported, but leave it in there. That way, programs such >> as check_postgres can not only distinguish between old but valid versions >> and invalid versions (e.g. "7.typo.oops") but can act in a more intelligent >> way for unsupported versions. Heck, maybe an estimated end-of-life date >> field for all versions as well? > How do you add that field in a backwards compatible way? Meaning that > people or tools relying on it should *not* see 7.3 or 6.1 or whatever. > And it needs to be done within the RSS spec (which does allow custom > namespaces though, so that may not be a problem) Well I don't know what people are reading the XML, so let's discuss tools. Do you have a use case in mind where adding old versions would break something? Has this always been advertised as a list of *supported* versions, or as a list of the *latest* revisions? I've always assumed the latter was more important that the former. > As for an estimated end-of-life, yes, we could definitely add that. > Now that we finally have it :-) +1 >> Either way, please add 7.4 back in. :) > >Done, will be on in the next site rebuild. Thanks much, and thanks to Devrim for originally spotting the bug. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201002010931 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAktm5hIACgkQvJuQZxSWSshVwwCeINTRgE7L5UWHJBIJgKDq3GIe X/gAoOivHWlQaVI3nI+TWjUkwxTlicUx =d+Yp -END PGP SIGNATURE- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Another PANIC corrupt index/crash ...any thoughts?
About a month ago I posted about a database crash possibly caused by corrupt index.. Dec 30 17:41:57 db-1 postgres[28957]: [ID 748848 local0.crit] [34004622-1] 2009-12-30 17:41:57.825 CST28957PANIC: right sibling 2019 of block 2018 is not next child of 1937 in index "sl_log_2_idx1" Has since happened again with a DIFFERENT index (interestingly also a slony related index) Jan 29 15:17:42 db-1 postgres[29025]: [ID 748848 local0.crit] [4135622-1] 2010-01-29 15:17:42.915 CST29025PANIC: right sibling 183 of block 182 is not next child of 158 in index "sl_seqlog_idx" I re-indexed the table...and restarted the database and all appears well (shut down autovacuum and slony for a while first to get feet underneath and then restarted after a few hours with no apparent ill effects) Coincidentally (or not) started getting disk errors about a minute AFTER the above error (db storage is on a fibre attached SAN) /var/log/archive/log-2010-01-29.log:Jan 29 15:18:50 db-1 scsi_vhci: [ID 734749 kern.warning] WARNING: vhci_scsi_reset 0x1 /var/log/archive/log-2010-01-29.log:Jan 29 15:18:50 db-1 scsi: [ID 243001 kern.warning] WARNING: /p...@0,0/pci10de,5...@d/pci1077,1...@0/f...@0,0 (fcp1): /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 kern.warning] WARNING: /scsi_vhci/d...@g000b08001c001958 (sd9): /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 kern.notice] Requested Block: 206265378 Error Block: 206265378 /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 kern.notice] Vendor: Pillar Serial Number: /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 kern.notice] Sense Key: Unit Attention /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 kern.notice] ASC: 0x29 (power on, reset, or bus reset occurred), ASCQ: 0x0, FRU: 0x0 Stack trace from recent crash is below: Program terminated with signal 6, Aborted. #0 0xfed00c57 in _lwp_kill () from /lib/libc.so.1 (gdb) bt #0 0xfed00c57 in _lwp_kill () from /lib/libc.so.1 #1 0xfecfe40e in thr_kill () from /lib/libc.so.1 #2 0xfecad083 in raise () from /lib/libc.so.1 #3 0xfec90b19 in abort () from /lib/libc.so.1 #4 0x0821b6ea in errfinish (dummy=0) at elog.c:471 #5 0x0821c58f in elog_finish (elevel=22, fmt=0x82b7200 "right sibling %u of block %u is not next child of %u in index \"%s\"") at elog.c:964 #6 0x0809e0a8 in _bt_pagedel (rel=0x8602f78, buf=377580, stack=0x881d660, vacuum_full=0 '\0') at nbtpage.c:1141 #7 0x0809f73d in btvacuumscan (info=0x8043f60, stats=0x8578410, callback=0, callback_state=0x0, cycleid=20894) at nbtree.c:936 #8 0x0809fb6d in btbulkdelete (fcinfo=0x0) at nbtree.c:547 #9 0x0821f268 in FunctionCall4 (flinfo=0x0, arg1=0, arg2=0, arg3=0, arg4=0) at fmgr.c:1215 #10 0x0809a7a7 in index_bulk_delete (info=0x8043f60, stats=0x0, callback=0x812fea0 , callback_state=0x85765e8) at indexam.c:573 #11 0x0812fe2c in lazy_vacuum_index (indrel=0x8602f78, stats=0x85769c8, vacrelstats=0x85765e8) at vacuumlazy.c:660 #12 0x08130432 in lazy_vacuum_rel (onerel=0x8602140, vacstmt=0x85d9f48) at vacuumlazy.c:487 #13 0x0812e7e8 in vacuum_rel (relid=140353352, vacstmt=0x85d9f48, expected_relkind=114 'r') at vacuum.c:1107 #14 0x0812f832 in vacuum (vacstmt=0x85d9f48, relids=0x85d9f38) at vacuum.c:400 #15 0x08186cee in AutoVacMain (argc=0, argv=0x0) at autovacuum.c:914 #16 0x08187150 in autovac_start () at autovacuum.c:178 #17 0x0818bec5 in ServerLoop () at postmaster.c:1252 #18 0x0818d045 in PostmasterMain (argc=3, argv=0x83399a8) at postmaster.c:966 #19 0x08152ba6 in main (argc=3, argv=0x83399a8) at main.c:188 Any thoughts on how I should proceed? We are planning an upgrade to 8.4 in the short-term, but I can see no evidence of fixes since the 8.2 version that would relate to index corruption. I have no real evidence of bad disks...iostat -E reports: # iostat -E sd2 Soft Errors: 1 Hard Errors: 4 Transport Errors: 0 Vendor: Pillar Product: Axiom 300Revision: Serial No: Size: 2.20GB <2200567296 bytes> Media Error: 0 Device Not Ready: 0 No Device: 4 Recoverable: 0 Illegal Request: 1 Predictive Failure Analysis: 0 sd3 Soft Errors: 1 Hard Errors: 32 Transport Errors: 0 Vendor: Pillar Product: Axiom 300Revision: Serial No: Size: 53.95GB <53948448256 bytes> Media Error: 0 Device Not Ready: 0 No Device: 32 Recoverable: 0 Illegal Request: 1 Predictive Failure Analysis: 0 sd7 Soft Errors: 1 Hard Errors: 40 Transport Errors: 8 Vendor: Pillar Product: Axiom 300Revision: Serial No: Size: 53.95GB <53948448256 bytes> Media Error: 0 Device Not Ready: 1 No Device: 33 Recoverable: 0 Illegal Request: 1 Predictive Failure Analysis: 0 sd8 Soft Errors: 1 Hard Errors: 34 Transport Errors: 0 Vendor: Pillar Product: Axiom 300Revision: Serial No: Size: 107.62GB <107622432256 bytes> Media Error: 0 De
Re: [GENERAL] Can LISTEN/NOTIFY deal with more than 100 every second?
Gavin Mu wrote: CREATE OR REPLACE RULE send_notify AS ON INSERT TO log DO ALSO NOTIFY logevent; .. when I use 3 similar programs to feed data, which means about 75 events every second, I found that Postgres didn't send NOTIFY opportunely, since the client do SELECT query every several hundreds seconds, which is too long to be acceptable. Hello Gavin, The following might help from the notify docs: "NOTIFY behaves like Unix signals in one important respect: if the same notification name is signaled multiple times in quick succession, recipients might get only one notification event for several executions of NOTIFY." So if your notify for instance could also add a unique number to the notification name, then it will probably work as expected. Regards, Yeb Havinga -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Another PANIC corrupt index/crash ...any thoughts?
On Mon, Feb 1, 2010 at 7:45 AM, Jeff Amiel wrote: > I have no real evidence of bad disks...iostat -E reports: Note that on a SAN you're not likely to see anything in iostat that says "bad disk block" since the SAN is hiding all that from you and presenting all the disks in it as one big disk, and the SAN will be handling things like disk block errors. You need to use your SAN management tools to troubleshoot this, most likely, assuming it's not a driver issue. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Another PANIC corrupt index/crash ...any thoughts?
On Mon, Feb 1, 2010 at 7:45 AM, Jeff Amiel wrote: > About a month ago I posted about a database crash possibly caused by corrupt > index.. > Coincidentally (or not) started getting disk errors about a minute AFTER the > above error (db storage is on a fibre attached SAN) Not likely a coincidence. > /var/log/archive/log-2010-01-29.log:Jan 29 15:18:50 db-1 scsi_vhci: [ID > 734749 kern.warning] WARNING: vhci_scsi_reset 0x1 > /var/log/archive/log-2010-01-29.log:Jan 29 15:18:50 db-1 scsi: [ID 243001 > kern.warning] WARNING: /p...@0,0/pci10de,5...@d/pci1077,1...@0/f...@0,0 > (fcp1): > /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 > kern.warning] WARNING: /scsi_vhci/d...@g000b08001c001958 (sd9): > /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 > kern.notice] Requested Block: 206265378 Error Block: > 206265378 > /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 > kern.notice] Vendor: Pillar Serial Number: > /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 > kern.notice] Sense Key: Unit Attention > /var/log/archive/log-2010-01-29.log:Jan 29 15:18:52 db-1 scsi: [ID 107833 > kern.notice] ASC: 0x29 (power on, reset, or bus reset occurred), ASCQ: 0x0, > FRU: 0x0 > > Any thoughts on how I should proceed? Figure out what's broken in your hardware? It looks like a driver issue to me. > We are planning an upgrade to 8.4 in the short-term, but I can see no > evidence of fixes since the 8.2 version that would relate to index corruption. This is not a postgresql issue, it is a bad hardware / driver issue. PostgreSQL cannot cause a SCSI reset etc on its own, it requires something be broken in the OS / hardware for that to happen. > I have no real evidence of bad disks...iostat -E reports: No, but this iostat output is evidence of a bad SAN driver / SAN or something around there. > > # iostat -E > sd2 Soft Errors: 1 Hard Errors: 4 Transport Errors: 0 > Vendor: Pillar Product: Axiom 300 Revision: Serial No: > Size: 2.20GB <2200567296 bytes> > Media Error: 0 Device Not Ready: 0 No Device: 4 Recoverable: 0 > Illegal Request: 1 Predictive Failure Analysis: 0 > sd3 Soft Errors: 1 Hard Errors: 32 Transport Errors: 0 > Vendor: Pillar Product: Axiom 300 Revision: Serial No: > Size: 53.95GB <53948448256 bytes> > Media Error: 0 Device Not Ready: 0 No Device: 32 Recoverable: 0 > Illegal Request: 1 Predictive Failure Analysis: 0 > sd7 Soft Errors: 1 Hard Errors: 40 Transport Errors: 8 > Vendor: Pillar Product: Axiom 300 Revision: Serial No: > Size: 53.95GB <53948448256 bytes> > Media Error: 0 Device Not Ready: 1 No Device: 33 Recoverable: 0 > Illegal Request: 1 Predictive Failure Analysis: 0 > sd8 Soft Errors: 1 Hard Errors: 34 Transport Errors: 0 > Vendor: Pillar Product: Axiom 300 Revision: Serial No: > Size: 107.62GB <107622432256 bytes> > Media Error: 0 Device Not Ready: 0 No Device: 34 Recoverable: 0 > Illegal Request: 1 Predictive Failure Analysis: 0 > sd9 Soft Errors: 1 Hard Errors: 32 Transport Errors: 2 > Vendor: Pillar Product: Axiom 300 Revision: Serial No: > Size: 215.80GB <215796153856 bytes> > Media Error: 0 Device Not Ready: 1 No Device: 29 Recoverable: 0 > Illegal Request: 1 Predictive Failure Analysis: 0 -- 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] Questions on PostGreSQL Authentication mechanism...
dipti shah escribió: > Thanks Richard. those chapters are very useful. I got to know most of > concepts but didn't find the location of pg_hba.conf file so that I can > verify it. I have connected to my database using "postgres" user. Could you > tell me how to open pg_hba.conf file? Run this: SHOW hba_file; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] How to test my new install
Señores, Saquen me de la lista ya no deceo resibir mas correos. atte. Jose Paredes Odar Jòse > From: r...@aarden.us > To: r...@iol.ie > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] How to test my new install > Date: Mon, 1 Feb 2010 04:48:08 -0600 > > Raymond, > > Thank you for responding to my question. I am sorry that I wasn't clear. > > My concern is that when I start up my tool stack and it doesn't work, that I > will have an entire chain to troubleshoot. If I could isolate each tool as > I install it, and validate that it is working, it will be easier to assess > any other difficulties. > > Ray > > - Original Message - > From: "Raymond O'Donnell" > To: "ray" > Cc: > Sent: Monday, February 01, 2010 3:33 AM > Subject: Re: [GENERAL] How to test my new install > > > > On 01/02/2010 01:15, ray wrote: > > > I have just installed 8.4 on an XP. My intent is to use it with Trac > > > and Apache. > > > > > > I would like to validate the installation of pgsql. What would be a > > > good method to make sure that pgsql is in there right? > > > > Not sure what you mean. I'd imagine the thing to do is to run your > > application against it in a test setup and check tat everything works as > > you expect. > > > > Ray. > > > > -- > > Raymond O'Donnell :: Galway :: Ireland > > r...@iol.ie > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ ¿Aún no sabes qué móvil eres? ¡Descúbrelo aquí! http://www.quemovileres.com/
[GENERAL] Connect to Postgres problems
Hi, I'm having trouble getting a connection to Postgres to work from outside of my local network. It was working fine at one point. Then I had to change IP addresses and I can't get it to work. This is what I've done. On the computer with Postgres installed I have this in the pg_hba.conf file: host all all 207.6.93.152/32 md5 in the postgresql.conf file I have this: listen_addresses = '*' port = 5432 I also have the router set up to forward port 5432 to the computer with Postgres installed. I have confirmed that the IP address in pg_hba.conf and the IP address being used to connect to are correct. I know the user name and password etc are correct because I can connect locally using the same stuff. The only things that have changed since it was working is IP addresses. I confirmed they are right. Also when trying to get it working I noticed that the port forwarding got disabled somehow so I set that up again but still no luck. I'm at a loss here. What else can I be missing? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.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] Connect to Postgres problems
On 02/01/2010 08:36 AM, Christine Penner wrote: Hi, I'm having trouble getting a connection to Postgres to work from outside of my local network. It was working fine at one point. Then I had to change IP addresses and I can't get it to work. This is what I've done. On the computer with Postgres installed I have this in the pg_hba.conf file: host all all 207.6.93.152/32 md5 in the postgresql.conf file I have this: listen_addresses = '*' port = 5432 I also have the router set up to forward port 5432 to the computer with Postgres installed. I have confirmed that the IP address in pg_hba.conf and the IP address being used to connect to are correct. I know the user name and password etc are correct because I can connect locally using the same stuff. The only things that have changed since it was working is IP addresses. I confirmed they are right. Also when trying to get it working I noticed that the port forwarding got disabled somehow so I set that up again but still no luck. I'm at a loss here. What else can I be missing? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.com Did you have Postgres reload the file?: pg_ctl reload -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
Did you reload postgres configuration after changing pg_hba.conf? On Mon, 2010-02-01 at 08:36 -0800, Christine Penner wrote: > Hi, > > I'm having trouble getting a connection to Postgres to work from > outside of my local network. It was working fine at one point. Then I > had to change IP addresses and I can't get it to work. This is what I've done. > > On the computer with Postgres installed I have this in the pg_hba.conf file: > host all all 207.6.93.152/32 md5 > > in the postgresql.conf file I have this: > listen_addresses = '*' > port = 5432 > > I also have the router set up to forward port 5432 to the computer > with Postgres installed. > > I have confirmed that the IP address in pg_hba.conf and the IP > address being used to connect to are correct. I know the user name > and password etc are correct because I can connect locally using the > same stuff. The only things that have changed since it was working > is IP addresses. I confirmed they are right. Also when trying to get > it working I noticed that the port forwarding got disabled somehow so > I set that up again but still no luck. > > I'm at a loss here. What else can I be missing? > > Christine Penner > Ingenious Software > 250-352-9495 > christ...@ingenioussoftware.com > > signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Connect to Postgres problems
I have re started the computer (a few times) since I did all that. Christine At 09:08 AM 01/02/2010, you wrote: On 02/01/2010 08:36 AM, Christine Penner wrote: Hi, I'm having trouble getting a connection to Postgres to work from outside of my local network. It was working fine at one point. Then I had to change IP addresses and I can't get it to work. This is what I've done. On the computer with Postgres installed I have this in the pg_hba.conf file: host all all 207.6.93.152/32 md5 in the postgresql.conf file I have this: listen_addresses = '*' port = 5432 I also have the router set up to forward port 5432 to the computer with Postgres installed. I have confirmed that the IP address in pg_hba.conf and the IP address being used to connect to are correct. I know the user name and password etc are correct because I can connect locally using the same stuff. The only things that have changed since it was working is IP addresses. I confirmed they are right. Also when trying to get it working I noticed that the port forwarding got disabled somehow so I set that up again but still no luck. I'm at a loss here. What else can I be missing? Christine Penner Ingenious Software 250-352-9495 christ...@ingenioussoftware.com Did you have Postgres reload the file?: pg_ctl reload -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
On 02/01/2010 09:10 AM, Christine Penner wrote: I have re started the computer (a few times) since I did all that. Christine What is the error that you are seeing on the client and in the logs? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
When we try to connect we don't get a specific error, just that it can't connect. I'm not sure what logs to look in or on what end. Christine At 09:13 AM 01/02/2010, you wrote: On 02/01/2010 09:10 AM, Christine Penner wrote: I have re started the computer (a few times) since I did all that. Christine What is the error that you are seeing on the client and in the logs? -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
You should be able to telnet to the port and get a response back as in the exmple below. Of course substitude the ip for the database. [postg...@pgprd01 londiste]$ telnet 127.0.0.1 5432 Trying 127.0.0.1... Connected to localhost.localdomain (127.0.0.1). Escape character is '^]'. > Date: Mon, 1 Feb 2010 09:13:34 -0800 > From: adrian.kla...@gmail.com > To: christ...@ingenioussoftware.com > CC: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Connect to Postgres problems > > On 02/01/2010 09:10 AM, Christine Penner wrote: > > I have re started the computer (a few times) since I did all that. > > > > Christine > > > > > > What is the error that you are seeing on the client and in the logs? > > > -- > Adrian Klaver > adrian.kla...@gmail.com > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _ Introducing Windows® phone. http://go.microsoft.com/?linkid=9708122
Re: [GENERAL] Connect to Postgres problems
Christine Penner wrote: Hi, I'm having trouble getting a connection to Postgres to work from outside of my local network. It was working fine at one point. Then I had to change IP addresses and I can't get it to work. This is what I've done. On the computer with Postgres installed I have this in the pg_hba.conf file: host all all 207.6.93.152/32 md5 in the postgresql.conf file I have this: listen_addresses = '*' port = 5432 I also have the router set up to forward port 5432 to the computer with Postgres installed. Did you check the firewall on the machine running Pg? Iptables etc. \\||/ Rod -- -- 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] Connect to Postgres problems
On 02/01/2010 09:16 AM, Christine Penner wrote: When we try to connect we don't get a specific error, just that it can't connect. I'm not sure what logs to look in or on what end. Christine Logging is set up in postgresql.conf per the comments in the file and for more information: http://www.postgresql.org/docs/8.4/interactive/runtime-config-logging.html If it has been set up the command show log_directory; will point you in the right direction. -- Adrian Klaver adrian.kla...@gmail.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible to set postgres in case insensitive mode ?
On Sat, Jan 30, 2010 at 4:44 AM, Scott Marlowe wrote: > On Fri, Jan 29, 2010 at 7:40 PM, Scott Marlowe > wrote: > > On Fri, Jan 29, 2010 at 2:52 PM, Moe > wrote: > >> Is it possible to set postgres in case insensitive mode ? > >> > >> If so, how? > > > > What part, exactly, do you want to be case insensitive? I assume you > > mean a text / varchar type? Look for citext, I believe it's a contrib > > module, until 9.0 is out, which will include it natively. > > It's here: > http://pgfoundry.org/projects/citext/ > But it doesn't work in 8.3 or 8.4, only 8.2 and before. So either run > that or wait for 9.0 I guess. > Sorry, I forgot I posted the message. What I mean was that a query select * from where email = ? could match on both upper and lower case emails, such myem...@hotmail.com or myem...@hotmail.com I know I can use the lower(...) function but this is not an option when using hibernate. MySql is by default case insensitive, I just figured there'd be an option to turn it on in PG as well. What about 9.0 ? How is that going to be offered ? For now I just normalized all emails to lower cased, and changed so that they are always saved in lower.. but there are other columns that are likely to need similar mathing ( firstname, lastname, address ... ) where lowering is not an option, and where storing in a separate column is really ugly. The like possibility is probably just slow. Sincerely / Moe
Re: [GENERAL] Connect to Postgres problems
I set up port forwarding. I assume that means all IP addresses using port 5432 will be sent to my laptop (that Postgres is running on). I don't remember setting up permissions for a specific IP in the router but it was a while ago so I could be wrong. When I get home I will have another look at the firewall settings and see. Christine At 09:20 AM 01/02/2010, you wrote: Christine Penner wrote: Hi, I'm having trouble getting a connection to Postgres to work from outside of my local network. It was working fine at one point. Then I had to change IP addresses and I can't get it to work. This is what I've done. On the computer with Postgres installed I have this in the pg_hba.conf file: host all all 207.6.93.152/32 md5 in the postgresql.conf file I have this: listen_addresses = '*' port = 5432 I also have the router set up to forward port 5432 to the computer with Postgres installed. Did you check the firewall on the machine running Pg? Iptables etc. \\||/ Rod -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Possible to set postgres in case insensitive mode ?
On Mon, Feb 01, 2010 at 07:35:45PM +0200, Moe wrote: > On Sat, Jan 30, 2010 at 4:44 AM, Scott Marlowe wrote: > > > On Fri, Jan 29, 2010 at 7:40 PM, Scott Marlowe > > wrote: > > > On Fri, Jan 29, 2010 at 2:52 PM, Moe > > wrote: > > >> Is it possible to set postgres in case insensitive mode ? > > >> > > >> If so, how? > > > > > > What part, exactly, do you want to be case insensitive? I assume you > > > mean a text / varchar type? Look for citext, I believe it's a contrib > > > module, until 9.0 is out, which will include it natively. > > > > It's here: > > http://pgfoundry.org/projects/citext/ > > But it doesn't work in 8.3 or 8.4, only 8.2 and before. So either run > > that or wait for 9.0 I guess. > > > > Sorry, I forgot I posted the message. What I mean was that a query select * > from where email = ? could match on both upper and lower case emails, such > myem...@hotmail.com or myem...@hotmail.com > > I know I can use the lower(...) function but this is not an option when > using hibernate. > > MySql is by default case insensitive, I just figured there'd be an option to > turn it on in PG as well. You can use citext, a supplied module. > What about 9.0 ? How is that going to be offered ? When it's ready! :) Cheers, David. -- David Fetter http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Connect to Postgres problems
Telnet is usually installed by default on windows or unix box, telnet to the database box should work. telnet 207.6.93.IP 5432 should work Make sure that windows firewall and antivirus software firewall are temporarily disabled to test. > Date: Mon, 1 Feb 2010 09:49:49 -0800 > To: raand...@cyber-office.net; pgsql-general@postgresql.org > From: christ...@ingenioussoftware.com > Subject: Re: [GENERAL] Connect to Postgres problems > > I set up port forwarding. I assume that means all IP addresses using > port 5432 will be sent to my laptop (that Postgres is running on). I > don't remember setting up permissions for a specific IP in the router > but it was a while ago so I could be wrong. When I get home I will > have another look at the firewall settings and see. > > Christine > > At 09:20 AM 01/02/2010, you wrote: > >Christine Penner wrote: > >>Hi, > >>I'm having trouble getting a connection to Postgres to work from > >>outside of my local network. It was working fine at one point. Then > >>I had to change IP addresses and I can't get it to work. This is > >>what I've done. > >>On the computer with Postgres installed I have this in the pg_hba.conf file: > >>host all all 207.6.93.152/32 md5 > >>in the postgresql.conf file I have this: > >>listen_addresses = '*' > >>port = 5432 > >>I also have the router set up to forward port 5432 to the computer > >>with Postgres installed. > > > >Did you check the firewall on the machine running Pg? Iptables etc. > > > > > >\\||/ > >Rod > >-- > > > > > >-- > >Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > >To make changes to your subscription: > >http://www.postgresql.org/mailpref/pgsql-general > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general _
[GENERAL] How Copy from a view to CSV file
I want to copy view's data into a .CSV file. But postgresql is generating an error as ERROR: cannot copy from view 'myview' I am executing the command COPY temp_error_view TO '/tmp/tempError.csv' USING DELIMITERS ',' WITH CSV ; I think postgresql doesn't allow to copy from view to csv directly. Do i have to create a temp table from the view and then copy that data to the csv file??
[GENERAL] How Copy from a view to CSV file
I want to copy view's data into a .CSV file. But postgresql is generating an error as ERROR: cannot copy from view 'myview' I am executing the command COPY temp_error_view TO '/tmp/tempError.csv' USING DELIMITERS ',' WITH CSV ; I think postgresql doesn't allow to copy from view to csv directly. Do i have to create a temp table from the view and then copy that data to the csv file??
Re: [GENERAL] How Copy from a view to CSV file
Vijay Sharma wrote: I want to copy view's data into a .CSV file. But postgresql is generating an error as ERROR: cannot copy from view 'myview' I am executing the command COPY temp_error_view TO '/tmp/tempError.csv' USING DELIMITERS ',' WITH CSV ; I think postgresql doesn't allow to copy from view to csv directly. Do i have to create a temp table from the view and then copy that data to the csv file?? Right, you can only COPY directly from a table, not a view. But you can copy out anything you can select, so this should work: COPY (SELECT * FROM temp_error_view) TO '/tmp/tempError.csv' USING DELIMITERS ',' WITH CSV ; -- Greg Smith2ndQuadrant Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.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] How Copy from a view to CSV file
Greg Smith wrote: >> I think postgresql doesn't allow to copy from view to csv directly. Do >> i have to create a temp table from the view and then copy that data to >> the csv file?? > > Right, you can only COPY directly from a table, not a view. But you can > copy out anything you can select, so this should work: > > COPY (SELECT * FROM temp_error_view) TO '/tmp/tempError.csv' USING > DELIMITERS ',' WITH CSV ; But this works only with version 8.2+, not with 8.1 or lesser. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) "If I was god, I would recompile penguin with --enable-fly." (unknown) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° -- 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] combine SQL SELECT statements into one
> -Original Message- > From: chris.el...@shropshire.gov.uk > [mailto:chris.el...@shropshire.gov.uk] > Sent: Monday, February 01, 2010 4:08 AM > To: neilst...@yahoo.com > Cc: pgsql-general@postgresql.org > Subject: Re: combine SQL SELECT statements into one > > > Hi > > pgsql-general-ow...@postgresql.org wrote on 02/01/2010 07:36:55 AM: > > > Good Evening, Good Morning Wherever you are whenever you > may be reading this. > > > > > snip > > > > count1 | count2 | count3 > > --- > > 2 2 4 > > > > Can this be done with ONE SQL STATEMENT? touching the > database only ONE time? > > You can do the following: > > SELECT > (SELECT COUNT(distinct model) FROM inventory WHERE > modified >= '2010-02-01') AS "COUNT_1", >(SELECT COUNT(distinct model) FROM inventory WHERE > modified >= '2010-01-20') AS "COUNT_2", >(SELECT COUNT(distinct model) FROM inventory WHERE > modified >= '2010-01-01') AS "COUNT_3" > ; > > PostgreSQL allows sub-queries in the select list as long as > the sub-query returns one column > > Job done > > > Please let me know. > > > > Thanx> :) > > NEiL > > > > Chris Ellis > > ** > > > 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 > > ** > > > Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT. > > ** > > Original poster asked for the sql that will touch inventory table only once. Your statement (with 3 subqueries) will do it 3 times. Igor Neyman -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Locking referenced table when creating and dropping tables with foreign key constraints
It seems that whenever I create a new empty table with a foreign key constraint, the transaction will acquire an exclusive lock on the referenced table, locking out other writers (not sure if even readers as well), and I don't quite see why that is necessary if the new entity does not contain any rows since there is nothing to check or validate in terms of the presence of values in the referenced column. This is biting us particularly now (it took the site down for a few minutes each time for the last couple of days) because we have a number of tables, and intend to add more, that are partitioned by date, all of which reference the core "person" table, for which we make new partitions daily via cron, and there is just no way we can take the app offline each time. And we will also soon start dropping them (i.e. removing from the inheritance hierarchy, archiving the content and then dropping them) automatically on an ongoing basis to keep the core data set manageable. In fact, it even looks like the dropping also requires a lock on the referenced table which makes even less sense to me ... Am I confused, or is there a way around it? We are on 8.3.7 atm. Regards, Frank -- 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] combine SQL SELECT statements into one
On Mon, Feb 1, 2010 at 12:09 PM, Igor Neyman wrote: > Original poster asked for the sql that will touch inventory table only > once. > > Your statement (with 3 subqueries) will do it 3 times. I'm pretty sure that starting with 8.3 the engine will collapse all those into one seq scan internally. -- 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] combine SQL SELECT statements into one
> > -Original Message- > > From: chris.el...@shropshire.gov.uk > > [mailto:chris.el...@shropshire.gov.uk] > > Sent: Monday, February 01, 2010 4:08 AM > > To: neilst...@yahoo.com > > Cc: pgsql-general@postgresql.org > > Subject: Re: combine SQL SELECT statements into one > > > > > > Hi > > > > pgsql-general-ow...@postgresql.org wrote on 02/01/2010 07:36:55 AM: > > > > > Good Evening, Good Morning Wherever you are whenever you > > may be reading this. > > > > > > > > snip > > > > > > count1 | count2 | count3 > > > --- > > > 2 2 4 > > > > > > Can this be done with ONE SQL STATEMENT? touching the > > database only ONE time? > > > > You can do the following: > > > > SELECT > > (SELECT COUNT(distinct model) FROM inventory WHERE > > modified >= '2010-02-01') AS "COUNT_1", > >(SELECT COUNT(distinct model) FROM inventory WHERE > > modified >= '2010-01-20') AS "COUNT_2", > >(SELECT COUNT(distinct model) FROM inventory WHERE > > modified >= '2010-01-01') AS "COUNT_3" > > ; > > > > PostgreSQL allows sub-queries in the select list as long as > > the sub-query returns one column > > > > Job done > > > > > Please let me know. > > > > > > Thanx> :) > > > NEiL > > > > > > > Chris Ellis > > > > ** > > > > > > 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 > > > > ** > > > > > > Help prevent the spread of swine flu. CATCH IT. BIN IT. KILL IT. > > > > ** > > > > > > Original poster asked for the sql that will touch inventory table only > once. > > Your statement (with 3 subqueries) will do it 3 times. > Igor Neyman > --- I think you will find that the poster asked to touch the DATABASE not the TABLE only once: 'Can this be done with ONE SQL STATEMENT? touching the database only ONE time?' While the sugested query might not me as optimised as possible, it demonstrates a possible method of folding multiple select statements into one select statement. This seemed main purpose of this post. I made the assumption that the intent was to reduce the overhead and latency caused from sending multiple statements. Chris Ellis
[GENERAL] statement_timeout problem
Somehow my previous message got grouped into the Amazon EC2 thread.. I have a problem with fetching from cursors sometimes taking an extremely long time to run. I am attempting to use the statement_timeout parameter to limit the runtime on these. PostgreSQL 8.2.4 Linux 2.6.22.14-72.fc6 #1 SMP Wed Nov 21 13:44:07 EST 2007 i686 i686 i386 GNU/Linux begin; set search_path = testdb; declare cur_rep cursor for select * from accounts, individual; set statement_timeout = 1000; fetch forward 100 from cur_rep; The open join, 1000ms, and 100 count are all intentional - just trying to figure out what's going on here. Normally those values would be 30 and 1. The accounts and individual tables have around 100 fields and 500k records each. Nested Loop (cost=21992.28..8137785497.71 rows=347496704100 width=8) -> Seq Scan on accounts (cost=0.00..30447.44 rows=623844 width=8) -> Materialize (cost=21992.28..29466.53 rows=557025 width=0) -> Seq Scan on individual (cost=0.00..19531.25 rows=557025 width=0) Yes, the plan is insane but again, just trying to figure this out. I tried moving the SET statment before the cursor delcaration and outside the transaction with the same results. I thought possibly it was getting bogged down in I/O but the timeout seems to work fine if not using a cursor. What am I missing here? Thanks, Joe _ The information contained in this message is proprietary and/or confidential. If you are not the intended recipient, please: (i) delete the message and all copies; (ii) do not disclose, distribute or use the message in any manner; and (iii) notify the sender immediately. In addition, please be aware that any message addressed to our domain is subject to archiving and review by persons other than the intended recipient. Thank you. _ -- 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] Locking referenced table when creating and dropping tables with foreign key constraints
frank joerdens writes: > It seems that whenever I create a new empty table with a foreign key > constraint, the transaction will acquire an exclusive lock on the > referenced table, locking out other writers (not sure if even readers > as well), and I don't quite see why that is necessary It involves an ALTER TABLE ADD TRIGGER command, which necessarily locks out writers to avoid race conditions. I think at the moment it may take an exclusive lock and thereby lock out readers as well. There has been some talk of trying to reduce the lock strength needed for ALTER operations, but you should not expect that it'll ever be possible to do that without blocking writers. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] statement_timeout problem
"Hardwick, Joe" writes: > I have a problem with fetching from cursors sometimes taking an > extremely long time to run. I am attempting to use the > statement_timeout parameter to limit the runtime on these. > PostgreSQL 8.2.4 > Linux 2.6.22.14-72.fc6 #1 SMP Wed Nov 21 13:44:07 EST 2007 i686 i686 > i386 GNU/Linux > begin; > set search_path = testdb; > declare cur_rep cursor for select * from accounts, individual; > set statement_timeout = 1000; > fetch forward 100 from cur_rep; Works for me --- the FETCH fails after just about a second of execution. I suspect the problem is lack of a CHECK_FOR_INTERRUPTS someplace, which probably got added sometime after 8.2.4 (I checked 8.2 branch tip). I'd suggest updating to 8.2.something-recent. If you still see the problem in latest 8.2.x then we need to look closer to figure out why the difference in results. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] reducing result set of tsvector @@ tsquery avoiding to use ts_rank
I've finally made some fruitful steps in writing C functions that
manipulate tsvectors.
I'd like to build up a simple system based on ts_rank to find
similarities between documents.
I've some documents containing 4 parts.
I build a tsvector the "usual way"
setweight(tsvector(field1), 'A') |
setweight(tsvector(field2), 'B') |
etc...
then I'd like to build a query similar to:
tsvector @@ to_tsquery(
'field1_lexeme1':A | 'field1_lexeme2':A | ...
'field2_lexeme2':B | 'field2_lexeme2':B | ...
Anyway so many OR are going to return a lot of rows and filtering on
rank is "too late" for performances.
One way to shrink the result set would be to build a query that
requires at least 2 lexemes to be present:
'field1_lexeme1':A & ('field1_lexeme2':A | ...
'field2_lexeme2':B | 'field2_lexeme2':B | ...
) |
'field1_lexeme2':A & ('field1_lexeme1 | ...
) |
I don't have very long documents and this looks feasible but I'd
like to hear any other suggestion to shrink the result set further
before filtering on ts_rank... especially suggestions that will
exploit the index.
So any suggestion that could reduce the result set before filtering
on rank is welcome and I'll try to put them in practice in some
C functions that taken a tsvector build up a tsquery to be used to
find similar documents.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Connect to Postgres problems
Christine Penner wrote: I'm having trouble getting a connection to Postgres to work from outside of my local network. It was working fine at one point. Then I had to change IP addresses and I can't get it to work. This is what I've done. On the computer with Postgres installed I have this in the pg_hba.conf file: host all all 207.6.93.152/32 md5 This will only allow the host at 207.6.93.152 to connect. If you're trying to reach the database from any other system, that won't work. I know the user name and password etc are correct because I can connect locally using the same stuff. The only things that have changed since it was working is IP addresses. I confirmed they are right. Also when trying to get it working I noticed that the port forwarding got disabled somehow so I set that up again but still no luck. Generally the troubleshooting works like this: 1) Confirm psql connects from the host (with the default, sockets based connection) 2) Confirm psql connects using TCP/IP on that same host. So "psql -h " on the server itself. If that doesn't work, probably an issue with listen_addresses or pg_hba.conf 3) Connect to the database from another host on the same local network. If this fails, check pg_hba.conf again, and make sure the server's firewall is configured to allow connections to the port 4) Connect from an outside network. Now any problem you have should be limited to just being related to things like port forwarding. -- Greg Smith 2ndQuadrant US Baltimore, MD PostgreSQL Training, Services and Support g...@2ndquadrant.com www.2ndQuadrant.us -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Use Trigger to Remove Table itself when there is no row after delete
May I know how I can use trigger technique, to remove the table itself, when after delete operation, there is 0 row in the table? Thanks and Regards Yan Cheng CHEOK -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Is this the warning message I should pay attention on it, during table partition
I am implementing table partition.
-- There is reason behind why we do not want to use trigger technique for
table unit.
-- Please refer to :
http://archives.postgresql.org/pgsql-general/2010-01/msg01184.php
-- INSERT INTO unit(fk_lot_id, cycle)
-- VALUES(_lotID, _cycle) RETURNING unit_id INTO _unit_id;
unit_table_index = _lotID;
unit_table_name = 'unit_' || _lotID;
IF NOT EXISTS(SELECT * FROM information_schema.tables WHERE table_name =
unit_table_name) THEN
EXECUTE 'CREATE TABLE ' || quote_ident(unit_table_name) || '
(
unit_id bigserial NOT NULL,
fk_lot_id bigint NOT NULL,
CHECK (fk_lot_id = ' || (unit_table_index) || '),
CONSTRAINT pk_unit_' || unit_table_index || '_id PRIMARY KEY
(unit_id),
CONSTRAINT fk_lot_' || unit_table_index || '_id FOREIGN KEY
(fk_lot_id) REFERENCES lot (lot_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE
CASCADE
) INHERITS (unit);';
EXECUTE 'CREATE INDEX fk_lot_' || unit_table_index || '_id_idx ON ' ||
quote_ident(unit_table_name) || '(fk_lot_id);';
END IF;
EXECUTE 'INSERT INTO ' || quote_ident(unit_table_name) || '(fk_lot_id,
cycle) VALUES (' || _lotID || ',' || _cycle || ') RETURNING unit_id'
INTO _unit_id;
_unit.unit_id = _unit_id;
_unit.fk_lot_id = _lotID;
_unit.cycle = _cycle;
However, I always get the following message, when there is a new table need to
be created.
NOTICE: CREATE TABLE will create implicit sequence "unit_2_unit_id_seq" for
serial column "unit_2.unit_id"
CONTEXT: SQL statement "CREATE TABLE unit_2
(
unit_id bigserial NOT NULL,
fk_lot_id bigint NOT NULL,
CHECK (fk_lot_id = 2),
CONSTRAINT pk_unit_2_id PRIMARY KEY (unit_id),
CONSTRAINT fk_lot_2_id FOREIGN KEY (fk_lot_id) REFERENCES lot
(lot_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE CAS
CADE
) INHERITS (unit);"
PL/pgSQL function "insert_unit" line 29 at EXECUTE statement
NOTICE: merging column "unit_id" with inherited definition
Is this the warning message I should take any action on it? If not, how I can
suppress it? It is quite annoying, when I saw these message keep printing out
from my c++ console.
Thanks and Regards
Yan Cheng CHEOK
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
