[GENERAL] How to delete rows number 2,3,4...
Hello. I have a table create table foo ( a serial, b int, c int, more fields ...); and now I wish to remove for each combination of b and c, all the rows except the one with the highest value of a. For example a b c other fields = 1 5 5 . 2 5 5 3 2 3 4 2 2 ... 5 5 5 ... should leave a b c other fields = 3 2 3 4 2 2 ... 5 5 5 ... is there some clever command for this or will I have to write a special function that loops through all values of b and c ? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] postgreSQL for Windows 7
On Thu, Oct 7, 2010 at 12:28 PM, quickinfo quickinfo wrote: > Dear Friends, > > I have installed windows 7 on my system. Previous I used postgreSQL 8.0 on > XP. I tried install postgres 8.3 on windows 7. but it is not installing > properly. After installtion am not able to find out pg_hba.conf files. > pgadmin III is opening fine and am able to create new database also. What > would be the problems. I'm unclear on what the problem is, other than you cannot find pg_hba.conf (which will be wherever you chose as a data directory). The fact that pgAdmin runs and you can create databases implies that the server is at least installed and running. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise Postgres Company -- 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 delete rows number 2,3,4...
On 8 Oct 2010, at 8:59, A B wrote: > Hello. > > I have a table > > create table foo ( > a serial, > b int, > c int, > more fields ...); > > and now I wish to remove for each combination of b and c, all the > rows except the one with the highest value of a. Or said differently: Delete all the rows where there exists a value of A that is higher than the one in the current row, given B and C are equal. In SQL that is: DELETE FROM foo WHERE EXISTS ( SELECT 1 FROM foo WHERE foo.a > a AND foo.b = bar.b AND foo.c = bar.c ) 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,4caeeabc678306717112265! -- 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 delete rows number 2,3,4...
Thank you all for your replies. 2010/10/8 Alban Hertroys : > On 8 Oct 2010, at 8:59, A B wrote: > >> Hello. >> >> I have a table >> >> create table foo ( >> a serial, >> b int, >> c int, >> more fields ...); >> >> and now I wish to remove for each combination of b and c, all the >> rows except the one with the highest value of a. > > Or said differently: Delete all the rows where there exists a value of A that > is higher than the one in the current row, given B and C are equal. > > In SQL that is: > > DELETE FROM foo WHERE EXISTS ( > SELECT 1 > FROM foo > WHERE foo.a > a > AND foo.b = bar.b > AND foo.c = bar.c > ) > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:871,4caeeab7678305532215207! > > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Internationalisation of database content (text columns)
(Since I got no replies on the novice list, I repost this question here) Hello, I'm working on a database schema which contains lots of "type code lookup" tables. The entries of these tables are also hierarchically related among themselves (subtype/supertype), to store rather large and quite complex taxonomies. Currently the schema does not provide for translation of the entries in these tables, although it is meant to be used for inter-company data exchange, i.e. for use by many different people having all different native languages. When searching for "best practice" guides about the topic of transparently translating database content, I came across about this example: http://rwec.co.uk/pg-atom/i18n.html The basic method looks pretty intuitive to me, except that I don't understand why it wouldn't be more obvious to use a composite type, with language codes as column names, instead of an array for the custom "translated text" data type. Any opinions from more experienced "mahouts"? Any obvious drawbacks of using such a composite type? Other than the fact that the schema has to be modified to add languages, which wouldn't be that desastrous in this case. Are there any other (better?) "best practice" methods for the internationalisation of database content? Maybe methods that use capabilities (e.g. text search/thesaurus?) already built-into PostgreSQL? BTW: Methods that use a single table to hold all translations for all strings in all tables of the entire schema are not very useful in this case, since it can't be excluded that depending on the context (i.e. the specific semantics of the specific "type code lookup" table) the translation of one and the same string in one language will be different in other languages. TIA, Sincerely, Wolfgang Keller -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL 7.4.16 is creating strange files under /var/lib/pgsql
On Oct 7, 2010, at 9:02 PM, Craig Ringer wrote: > Where? They can't be in /var/lib/pgsql if there are four of them, so they > must be in subdirectories, right? Which ones? Or they're created with odd characters which the user's shell/terminal/UI cannot display. -- Scott Ribe scott_r...@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] LOG: unexpected EOF on client connection
Hi all - I am seeing lot of these records in the log file. Not able to find why I get this in log file. Is there a way to find out info about this ? Thanks for your help LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection LOG: unexpected EOF on client connection Regards
Re: [GENERAL] LOG: unexpected EOF on client connection
> Hi all - > > I am seeing lot of these records in the log file. Not able > to > find why I get this in log file. Is there a way to find out info about > this > ? Thanks for your help > > > LOG: unexpected EOF on client connection > LOG: unexpected EOF on client connection This means the client application is dropping the connection unexpectedly. You have to find the application and fix it - a good log_line_prefix may be a good way to find the application (e.g. "%t %...@%d %r" or something like that). Not too long ago I've received a lot of these when the apache http server went crazy after an update and was dropping the connections (opened by a PHP application) for some reason. regards Tomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Optimization of FK creation
Hello.
It's often neccessary to create empty table and FK to some existing
table from this newly created table.
However, even newly created (empty) table requires lock on parent
table it wants refer to.
On large production DBs with high load on parent table it's almost
neverending operation.
For example,
CREATE TABLE "table_foo" (
...
);
ALTER TABLE "table_foo"
ADD CONSTRAINT "FK_table_foo_REF_table_parent"
FOREIGN KEY ("ColName") REFERENCES "table_parent" ("ColName");
hangs forever because requires lock on "ColName" of table_parent with
billions of records,
which for empty table seems senseless.
I think it's very easy to check if "table_foo" is empty and therefore
not requires any locks on
"parent_table" and generate FK immediately without acquiring the lock
on "parent" table.
Similar problem raised here:
http://postgresql.1045698.n5.nabble.com/probelm-with-alter-table-add-constraint-td2072411.html
Please fix this issue asap.
regards,
--
Ayrapetyan Robert
--
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] Optimization of FK creation
Robert Ayrapetyan writes: > I think it's very easy to check if "table_foo" is empty and therefore > not requires any locks on > "parent_table" and generate FK immediately without acquiring the lock > on "parent" table. Unfortunately, that's complete nonsense. The reason for the lock on the parent is so that DDL changes can be applied to it, namely addition of triggers. 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] Segfault : PostgreSQL 9.0.0 and PgPool-II.
Hello, I'm having issues trying to set up PgPool-II with PostgreSQL 9.0.0 (for HA reasons with Streaming Replication). PgPool end up with a SIGSEV whenever I have a correct authentification. To try my set up, I've installed a default PgSQL 9.0.0 server, where I can connect without a single problem. r...@test-pgsql:/# psql -h localhost -p 5432 -U postgres Password for user postgres: psql (9.0.0) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. postgres=# \q But whenever I try to connect to the PgPool port ... the PgPool process dies at the moment I enter the valid password. r...@test-pgsql:/usr/src/pgpool-II-3.0# psql -h localhost -p -U postgres Password for user postgres: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. r...@test-pgsql:/usr/src/pgpool-II-3.0# r...@test-pgsql:~# pgpool -n 2010-10-08 16:42:13 LOG: pid 14248: pgpool-II successfully started. version 3.0 (umiyameboshi) 2010-10-08 16:42:18 ERROR: pid 14280: do_md5: read_password_packet failed 2010-10-08 16:42:20 ERROR: pid 14248: Child process 14280 was terminated by segmentation fault Do you have any idea ? (I've followed the basic PgPool-II instructions for the installation part) Is there any other way to achieve some kind of HA with Streaming-Replication ? Some kind of best-practices ? Thanks a lot, Greetings, Ugo PARSI -- "Above, the stars are spiralling and Heaven, Earth are roaming in a spin" -- 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] Segfault : PostgreSQL 9.0.0 and PgPool-II.
On 10/08/2010 09:47 AM, Ugo PARSI wrote: Hello, I'm having issues trying to set up PgPool-II with PostgreSQL 9.0.0 (for HA reasons with Streaming Replication). PgPool end up with a SIGSEV whenever I have a correct authentification. To try my set up, I've installed a default PgSQL 9.0.0 server, where I can connect without a single problem. r...@test-pgsql:/# psql -h localhost -p 5432 -U postgres Password for user postgres: psql (9.0.0) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. postgres=# \q But whenever I try to connect to the PgPool port ... the PgPool process dies at the moment I enter the valid password. r...@test-pgsql:/usr/src/pgpool-II-3.0# psql -h localhost -p -U postgres Password for user postgres: psql: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. r...@test-pgsql:/usr/src/pgpool-II-3.0# r...@test-pgsql:~# pgpool -n 2010-10-08 16:42:13 LOG: pid 14248: pgpool-II successfully started. version 3.0 (umiyameboshi) 2010-10-08 16:42:18 ERROR: pid 14280: do_md5: read_password_packet failed 2010-10-08 16:42:20 ERROR: pid 14248: Child process 14280 was terminated by segmentation fault Do you have any idea ? (I've followed the basic PgPool-II instructions for the installation part) Is there any other way to achieve some kind of HA with Streaming-Replication ? Some kind of best-practices ? Thanks a lot, Greetings, Ugo PARSI From here: http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#restriction Did you follow procedure under ?: Authentication Access Controls -- 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
[GENERAL] Wrong starting value of sequences after doing pg_dump
I did a pg_dump of a database. All the sequences ended up with starting values of 1. Since this is not an empty database, this is all wrong. Is there anything I need to do at command time to change that or is it a bug? \set in psql gives: PostgreSQL 8.4.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit Dennis Gearon Signature Warning It is always a good idea to learn from your own mistakes. It is usually a better idea to learn from others’ mistakes, so you do not have to make them yourself. from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036' EARTH has a Right To Life, otherwise we all die. -- 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] Wrong starting value of sequences after doing pg_dump
On 10/08/2010 11:17 AM, Dennis Gearon wrote: I did a pg_dump of a database. All the sequences ended up with starting values of 1. Since this is not an empty database, this is all wrong. Is there anything I need to do at command time to change that or is it a bug? \set in psql gives: PostgreSQL 8.4.5 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit Dennis Gearon Signature Warning It is always a good idea to learn from your own mistakes. It is usually a better idea to learn from others’ mistakes, so you do not have to make them yourself. from 'http://blogs.techrepublic.com.com/security/?p=4501&tag=nl.e036' EARTH has a Right To Life, otherwise we all die. In the dumps I have seen they initially get set to 1, then further on in the file a setval is done to get them to the right value. Have you looked for that yet? -- 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] LOG: unexpected EOF on client connection
On Fri, Oct 8, 2010 at 8:11 AM, wrote: >> Hi all - >> >> I am seeing lot of these records in the log file. Not able >> to >> find why I get this in log file. Is there a way to find out info about >> this >> ? Thanks for your help >> >> >> LOG: unexpected EOF on client connection >> LOG: unexpected EOF on client connection > > This means the client application is dropping the connection unexpectedly. > You have to find the application and fix it - a good log_line_prefix may > be a good way to find the application (e.g. "%t %...@%d %r" or something > like that). Or that OP has a networking issue. Some firewalls are known for dropping what they think are idle connections when they aren't. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] anyone have Movable Type 5 working with Postgres?
Even though Movable Type 5 has dropped official support for Postgres it still comes with the drivers in order for it to work. I've gotten it setup and working with Postgres, but there is one problem that keeps me from being able to use it. That issue I've explained over on the MT forums: http://forums.movabletype.org/2010/10/cant-use-string-as-an-array-ref-while-strict-refs-in-use.html Is anyone here running Postgres 9.0 and MT5 together? Thanks, -M@ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Understanding PostgreSQL Storage Engines
I know that MySQL uses MyISAM storage engine by default and was just trying to look on Google to try and see if I could understand what storage engine does PostgreSQL use by default when I generate a database / table. Is there some way someone (me) who knows nothing about how a ORDBMS works understand the difference between all storage engine options and which does PostgreSQL use by default. Thanks for any help... -Carlos -- 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] Understanding PostgreSQL Storage Engines
Hello 2010/10/8 Carlos Mennens : > I know that MySQL uses MyISAM storage engine by default and was just > trying to look on Google to try and see if I could understand what > storage engine does PostgreSQL use by default when I generate a > database / table. Is there some way someone (me) who knows nothing > about how a ORDBMS works understand the difference between all storage > engine options and which does PostgreSQL use by default. > PostgreSQL supports and uses just only one storage engine - PostgreSQL. Regards Pavel Stehule > Thanks for any help... > > -Carlos > > -- > 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] Understanding PostgreSQL Storage Engines
Hey, 2010/10/9 Pavel Stehule > Hello > > 2010/10/8 Carlos Mennens : > > I know that MySQL uses MyISAM storage engine by default and was just > > trying to look on Google to try and see if I could understand what > > storage engine does PostgreSQL use by default when I generate a > > database / table. Is there some way someone (me) who knows nothing > > about how a ORDBMS works understand the difference between all storage > > engine options and which does PostgreSQL use by default. > > > > PostgreSQL supports and uses just only one storage engine - PostgreSQL. > > ... Which has a long history. Regards > > Pavel Stehule > > > Thanks for any help... > > > > -Carlos > > > > -- > > 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 > -- // Dmitriy.
Re: [GENERAL] Understanding PostgreSQL Storage Engines
On Friday 08 October 2010 2:30:40 pm Carlos Mennens wrote: > I know that MySQL uses MyISAM storage engine by default and was just > trying to look on Google to try and see if I could understand what > storage engine does PostgreSQL use by default when I generate a > database / table. Is there some way someone (me) who knows nothing > about how a ORDBMS works understand the difference between all storage > engine options and which does PostgreSQL use by default. > > Thanks for any help... > > -Carlos Postgres only has one storage engine. Sort of simplifies things. -- 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
[GENERAL] Another PostgreSQL Diff Tool 2.2.2 released
Another PostgreSQL Diff Tool 2.2.2 released --- Another PostgreSQL Diff Tool (also known as apgdiff) is free PostgreSQL database schema diff tool. Release information --- This is a bugfix release. Also Czech localization was added in this release. Release Details --- Fixes * Added missing new line after ALTER VIEW ... ALTER COLUMN ... SET/DROP DEFAULT. * Fixed parsing of quoted string values. * Fixed detection of function body separator (did not work when there was another 'AS' specified on the same line after the 'AS' starting function body). * If two dumps are completely same and there is more than one schema in the dumps, the output now does not contain 'SET search_path = ...'. In other words, if two dumps are completely same, no output is produced. * Replaced 'ALTER VIEW name ALTER COLUMN ...' with 'ALTER TABLE view_name ALTER COLUMN ...' to make it compatible with PostgreSQL releases prior to 8.4. * Fixed parsing of '' escapes. Other * Added support for localization of apgdiff. * Added Czech localization. Changelog can be also found at http://apgdiff.startnet.biz/changelog.php Translations For information about translations see http://apgdiff.startnet.biz/translations.php Download Binary distribution and sources: https://sourceforge.net/projects/apgdiff/files/ Sources from Merurial repository: |http://apgdiff.hg.sourceforge.net:8000/hgroot/apgdiff/apgdiff|| Other Information - If you have issues with running apgdiff, you can find answer for your issue at FAQ page: http://apgdiff.startnet.biz/faq.php If you find any issue with apgdiff or you miss a feature, please see http://apgdiff.startnet.biz/bugs.php for information about filing bugs and feature requests. Best regards. Miroslav Šulc Another PostgreSQL Diff Tool Developer|
Re: [GENERAL] Understanding PostgreSQL Storage Engines
On 10/08/2010 03:39 PM, Adrian Klaver wrote: > On Friday 08 October 2010 2:30:40 pm Carlos Mennens wrote: >> I know that MySQL uses MyISAM storage engine by default and was just >> trying to look on Google to try and see if I could understand what >> storage engine does PostgreSQL use by default when I generate a >> database / table. Is there some way someone (me) who knows nothing >> about how a ORDBMS works understand the difference between all storage >> engine options and which does PostgreSQL use by default. >> >> Thanks for any help... >> >> -Carlos > > Postgres only has one storage engine. Sort of simplifies things. > My guess is the OP wants to know that Postgres uses tactically in its engine: B(+)trees (or whatever it does actually use) versus Indexed Sequential Access Method (judging by the name). No? -- 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] Understanding PostgreSQL Storage Engines
On 10/09/2010 05:30 AM, Carlos Mennens wrote: I know that MySQL uses MyISAM storage engine by default and was just trying to look on Google to try and see if I could understand what storage engine does PostgreSQL use by default when I generate a database / table. Is there some way someone (me) who knows nothing about how a ORDBMS works understand the difference between all storage engine options and which does PostgreSQL use by default. In MySQL terms, PostgreSQL's one and only storage engine is much more like InnoDB than MyISAM. That's not to say it's particularly like MySQL+InnoDB in behaviour, only much *more* like InnoDB than MyISAM. It's an MVCC design with proper transaction support (like any real database) with minimal locking and a focus on concurrency, data integrity and correctness. If you're used to MySQL, you'll want to read this: http://wiki.postgresql.org/wiki/Slow_Counting as it bites MySQL people all the time. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
