[GENERAL] read and restore deleted record
Hi all.. Start from vacuum feature information from PGSQL helps documentation, it telling me that Postgresql didn't delete data permanently when we execute delete command, it just made the data invalid. By following this email archive : http://archives.postgresql.org/pgsql-admin/2005-01/msg00176.php I could get the table files, my question is, how could I read again the deleted record from that files. Thank you and best regards, Soultani
[GENERAL] libpq: multiple commands within single query
By which rules database returns results for multiple commands within single query? For example I send (execute) such query (obtain different information about just connected client): SELECT column1 FROM table1; SELECT column2 FROM table2; SELECT column3 FROM table3; And when I get response I expect some results (PGresult structure) for EACH SELECT - so there must be 3 PGresult structures and if one of results contains zero rows - it means that there are no data (of such type) for such client yet, but there still can be some data for following SELECT. For example (referring to previous example) response can contain 3 results (as I expect) where first contains 1 row, second 0 rows and third 1 row. So when I examine the response I can match each PGresult to each SELECT. Does it works on this way? Or I missed something?
Re: [GENERAL] libpq: multiple commands within single query
Hey, 18 января 2011 г. 14:24 пользователь Вячеслав Блинников slav...@gmail.comнаписал: By which rules database returns results for multiple commands within single query? For example I send (execute) such query (obtain different information about just connected client): SELECT column1 FROM table1; SELECT column2 FROM table2; SELECT column3 FROM table3; And when I get response I expect some results (PGresult structure) for EACH SELECT - so there must be 3 PGresult structures and if one of results contains zero rows - it means that there are no data (of such type) for such client yet, but there still can be some data for following SELECT. For example (referring to previous example) response can contain 3 results (as I expect) where first contains 1 row, second 0 rows and third 1 row. So when I examine the response I can match each PGresult to each SELECT. Does it works on this way? Or I missed something? According the documentation: the client can be handling the results of one command while the server is still working on later queries in the same command string.. The key word here is later. -- // Dmitriy.
Re: [GENERAL] database slowdown
Hi Andy, No, I don't shrink the database. I simply purge the whole thing and then let it populate again. The data isn't too critical. The disks I have are internal SAS disks. I get around 150MB/sec write and 250MB/sec read. Its a RAID1 . ps does show idle in transactions. I've never checked pg_locks. How would I do that ? On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson a...@squeakycode.net wrote: On 01/16/2011 10:44 AM, Mag Gam wrote: I am running Redhat 5.2 Linux with Postgresql 8.4.4; When my disk space is 90% free the database performance is very good. However, when it reaches close to 20% free the database performance is bad. I know its bad because I see a lot of 'D' next to 'postgresql' process when running top and I see a lot 'WAITING' in the 'ps' tree. I was wondering if there are any filesystem tweaks I can do to make postgresql run smoother. I am currently using ext3. Should I consider using a different file system for my database? I doubt its a disk space problem. Sounds like a cache/disk io problem. When you are using 80% space, do you do something to get it back to 90% free (ie shrink the db)? When the db is small, it fits in ram, and read's will be very quick. Once your db gets bigger than cache it'll have to start using a lot more disk io. What kind of disk IO throughput do yo have? What kind of disk system is this? (raid, scsi, etc) Does ps ever show 'idle in transaction'? Have you checked pg_locks to see what you are waiting on? Have you watched vmstat while its at 80% full vs when its at 80% free? (does the iowait go up?) -Andy -- 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] database slowdown
In response to Mag Gam magaw...@gmail.com: Hi Andy, No, I don't shrink the database. I simply purge the whole thing and then let it populate again. The data isn't too critical. What does purge mean? Are you doing an SQL DELETE, or a TRUNCATE, or dropping the DB and recreating? Each of these functions differently behind the scenes, in particular DELETE will not actually free up the space used by the records you deleted right away, and depending on many other factors, may be the cause of your problems. The disks I have are internal SAS disks. I get around 150MB/sec write and 250MB/sec read. Its a RAID1 . ps does show idle in transactions. I've never checked pg_locks. How would I do that ? On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson a...@squeakycode.net wrote: On 01/16/2011 10:44 AM, Mag Gam wrote: I am running Redhat 5.2 Linux with Postgresql 8.4.4; When my disk space is 90% free the database performance is very good. However, when it reaches close to 20% free the database performance is bad. I know its bad because I see a lot of 'D' next to 'postgresql' process when running top and I see a lot 'WAITING' in the 'ps' tree. I was wondering if there are any filesystem tweaks I can do to make postgresql run smoother. I am currently using ext3. Should I consider using a different file system for my database? I doubt its a disk space problem. Sounds like a cache/disk io problem. When you are using 80% space, do you do something to get it back to 90% free (ie shrink the db)? When the db is small, it fits in ram, and read's will be very quick. Once your db gets bigger than cache it'll have to start using a lot more disk io. What kind of disk IO throughput do yo have? What kind of disk system is this? (raid, scsi, etc) Does ps ever show 'idle in transaction'? Have you checked pg_locks to see what you are waiting on? Have you watched vmstat while its at 80% full vs when its at 80% free? (does the iowait go up?) -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] database slowdown
Purge meaning, stop postgresql, rm -rf $PGDATA, recreate the environment, and start up postgresql again. On Tue, Jan 18, 2011 at 8:32 AM, Bill Moran wmo...@potentialtech.com wrote: In response to Mag Gam magaw...@gmail.com: Hi Andy, No, I don't shrink the database. I simply purge the whole thing and then let it populate again. The data isn't too critical. What does purge mean? Are you doing an SQL DELETE, or a TRUNCATE, or dropping the DB and recreating? Each of these functions differently behind the scenes, in particular DELETE will not actually free up the space used by the records you deleted right away, and depending on many other factors, may be the cause of your problems. The disks I have are internal SAS disks. I get around 150MB/sec write and 250MB/sec read. Its a RAID1 . ps does show idle in transactions. I've never checked pg_locks. How would I do that ? On Sun, Jan 16, 2011 at 12:28 PM, Andy Colson a...@squeakycode.net wrote: On 01/16/2011 10:44 AM, Mag Gam wrote: I am running Redhat 5.2 Linux with Postgresql 8.4.4; When my disk space is 90% free the database performance is very good. However, when it reaches close to 20% free the database performance is bad. I know its bad because I see a lot of 'D' next to 'postgresql' process when running top and I see a lot 'WAITING' in the 'ps' tree. I was wondering if there are any filesystem tweaks I can do to make postgresql run smoother. I am currently using ext3. Should I consider using a different file system for my database? I doubt its a disk space problem. Sounds like a cache/disk io problem. When you are using 80% space, do you do something to get it back to 90% free (ie shrink the db)? When the db is small, it fits in ram, and read's will be very quick. Once your db gets bigger than cache it'll have to start using a lot more disk io. What kind of disk IO throughput do yo have? What kind of disk system is this? (raid, scsi, etc) Does ps ever show 'idle in transaction'? Have you checked pg_locks to see what you are waiting on? Have you watched vmstat while its at 80% full vs when its at 80% free? (does the iowait go up?) -Andy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- Bill Moran http://www.potentialtech.com http://people.collaborativefusion.com/~wmoran/ -- 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] database slowdown
On 18/01/2011 13:43, Mag Gam wrote: Purge meaning, stop postgresql, rm -rf $PGDATA, recreate the environment, and start up postgresql again. Goodness, that's fairly dramatic. What's wrong with DROP DATABASE? :-) 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] postgresql-9.0.2-1-windows_x64 from EnterpriseDB can't install on Win 7 home basic 64 bit
Hi, I know you people have talked a lot about this, but until now I still can't install the newest release installed on my new Win7 Home basic 64 bit notebook, the user I run has administration previliege, and I have tried C and POSIX locale, neither works. The error message are the same: Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed. Do you have any suggestion aout this, thanks. Xiaob Gu -- 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] plpythonu memory leak
Alex Hunsaker writes: FYI if I don't use a slice copy here I can't get it to leak. ( find my test case at the end ) I don't know enough about python to know if thats a pl/python issue or python doing what its told-- having never really wrote any python myself. --- -- leaks big time CREATE or replace FUNCTION pygaps_leak() RETURNS void LANGUAGE plpythonu AS $$ results = plpy.execute(select generate_series(0, 100)) prev = results[0] for curr in results[1:]: prev = curr return $$; -- does not leak CREATE or replace FUNCTION pygaps_no_leak() RETURNS void LANGUAGE plpythonu AS $$ results = plpy.execute(select generate_series(0, 100)) prev = results[0] for curr in range(1, len(results)): prev = curr return $$; Alex, Great find! Yes, it's definitely leaking when taking a slice. Something is hanging on to the reference to the slice object and/or the reference count is not properly managed: I modified your leak function and added explicit calls to the python garbage collector with no result. I'll hunt around in the source for the leak. Regardless of my findings, I'll submit a bug. Thanks! Dan Popowich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Re: postgresql-9.0.2-1-windows_x64 from EnterpriseDB can't install on Win 7 home basic 64 bit
Hi, I have tried initdb manually, it failed: initdb -D D:\\Amber\\Program\\PostgreSQL\\9.0\\data --lc-messages=English -U postgres E UTF8 -A md5 and unfortunately initdb can't show the error messages probably, I think it is because of encoding, my notebook is in Simplified Chinese Locale, and I know EnterpriseDB does not support it now, do you have a road map to support this Locale in the near future? Thanks Xiaobo Gu On Tue, Jan 18, 2011 at 10:38 PM, Xiaobo Gu guxiaobo1...@gmail.com wrote: Hi, I know you people have talked a lot about this, but until now I still can't install the newest release installed on my new Win7 Home basic 64 bit notebook, the user I run has administration previliege, and I have tried C and POSIX locale, neither works. The error message are the same: Problem running post-install step. Installation may not complete correctly. The database cluster initialisation failed. Do you have any suggestion aout this, thanks. Xiaob Gu -- 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] database slowdown
On Sun, Jan 16, 2011 at 12:28 PM, Andy Colsona...@squeakycode.net wrote: On 01/16/2011 10:44 AM, Mag Gam wrote: I am running Redhat 5.2 Linux with Postgresql 8.4.4; When my disk space is 90% free the database performance is very good. However, when it reaches close to 20% free the database performance is bad. I know its bad because I see a lot of 'D' next to 'postgresql' process when running top and I see a lot 'WAITING' in the 'ps' tree. I was wondering if there are any filesystem tweaks I can do to make postgresql run smoother. I am currently using ext3. Should I consider using a different file system for my database? I doubt its a disk space problem. Sounds like a cache/disk io problem. When you are using 80% space, do you do something to get it back to 90% free (ie shrink the db)? When the db is small, it fits in ram, and read's will be very quick. Once your db gets bigger than cache it'll have to start using a lot more disk io. What kind of disk IO throughput do yo have? What kind of disk system is this? (raid, scsi, etc) Does ps ever show 'idle in transaction'? Have you checked pg_locks to see what you are waiting on? Have you watched vmstat while its at 80% full vs when its at 80% free? (does the iowait go up?) -Andy On 1/18/2011 7:11 AM, Mag Gam wrote: Hi Andy, No, I don't shrink the database. I simply purge the whole thing and then let it populate again. The data isn't too critical. The disks I have are internal SAS disks. I get around 150MB/sec write and 250MB/sec read. Its a RAID1 . ps does show idle in transactions. I've never checked pg_locks. How would I do that ? When you say Database performance is bad... are you doing mostly read or mostly write operations? Do you vacuum? Idle in transaction is probably bad. It means a process has started a transaction and not commit. For any rows that transaction touched PG must keep its row versions alive, plus any new versions of the same row. It can lead to excess memory usage, slow table reads (because not only does PG have to scan the rows in a table, it has to scan the versions of each row). It'll also block vacuum's, so you wont be reclaiming disk space and your database will just grow and grow. I realize I never touched on your actual question about file systems. I dont think with a two disk mirror you are going to stress your FS too much. I have seen benchmarks give the edge to XFS. I dont think it'll even be in the 10's of percentages better. I use XFS myself, so dont really have any experience with ext3. Its also important to have the memory usage setup well so PG can cache as much data as possible. (shared_buffers and effective_cache_size are probably most important) select * from pg_locks; it'll tell you if one process (that maybe has not commit?!) is holding a lock that other processes are waiting for. PG works best when you run small transactions, commit, and let autovacuum do its magic. -Andy -- 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] libpq: multiple commands within single query
Yeah - that is how I do (PGgetResult function returns results until it will return NULL pointer which means that all data for whole PGsendQuery was returned). But what does it mean when query which contain 3 SELECT commands returns just one result (second PGgetResult() already returns null pointer)? 18 января 2011 г. 14:55 пользователь Dmitriy Igrishin dmit...@gmail.comнаписал: Hey, 18 января 2011 г. 14:24 пользователь Вячеслав Блинников slav...@gmail.com написал: By which rules database returns results for multiple commands within single query? For example I send (execute) such query (obtain different information about just connected client): SELECT column1 FROM table1; SELECT column2 FROM table2; SELECT column3 FROM table3; And when I get response I expect some results (PGresult structure) for EACH SELECT - so there must be 3 PGresult structures and if one of results contains zero rows - it means that there are no data (of such type) for such client yet, but there still can be some data for following SELECT. For example (referring to previous example) response can contain 3 results (as I expect) where first contains 1 row, second 0 rows and third 1 row. So when I examine the response I can match each PGresult to each SELECT. Does it works on this way? Or I missed something? According the documentation: the client can be handling the results of one command while the server is still working on later queries in the same command string.. The key word here is later. -- // Dmitriy.
Re: [GENERAL] libpq: multiple commands within single query
=?KOI8-R?B?99HexdPMwdcg4szJzs7Jy8/X?= slav...@gmail.com writes: Yeah - that is how I do (PGgetResult function returns results until it will return NULL pointer which means that all data for whole PGsendQuery was returned). But what does it mean when query which contain 3 SELECT commands returns just one result (second PGgetResult() already returns null pointer)? Most likely, the command failed ... have you tried inspecting the PGresult to see what status it's reporting? 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] plpythonu memory leak
I found the bug and it has been reported. Bug #5842. Details here: http://archives.postgresql.org/pgsql-bugs/2011-01/msg00134.php Dan Popowich -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL 9.0.1 PITR can not copy WAL file
My PITR work well from 01/01/2011 to 06/01/2011. At 06/01/2011 postgresql log have issue 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_19.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_49.tmpct70s in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_34.kmtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_34.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_34.dmckcttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_58.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_53.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_120.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_119.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_119.dmckcttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_119.kmtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_22.dmgiabancttmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_18.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:48 ICT LOG: autovacuum: found orphan temp table pg_temp_15.cdvt13newtmp in database cpnvn_data 2011-01-06 08:27:54 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:54 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:27:55 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:55 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:27:56 ICT LOG: archive command failed with exit code 1 2011-01-06 08:27:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:27:56 ICT WARNING: transaction log file 00010004005E could not be archived: too many failures ... 2011-01-06 08:28:56 ICT LOG: archive command failed with exit code 1 2011-01-06 08:28:56 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:28:57 ICT LOG: archive command failed with exit code 1 2011-01-06 08:28:57 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:28:58 ICT LOG: archive command failed with exit code 1 2011-01-06 08:28:58 ICT DETAIL: The failed archive command was: copy pg_xlog\00010004005E D:/3SDATABACKUP/PITR/WAL/00010004005E 2011-01-06 08:28:58 ICT WARNING: transaction log file 00010004005E could not be archived: too many failures And my pg_xlog can not copy to D:/3SDATABACKUP/PITR/WAL from 06/01/2011. How to fix error ? Please help me Here is my Archive # - Archiving - archive_mode = on# allows archiving to be done # (change requires restart) archive_command = 'copy %p D:/3SDATABACKUP/PITR/WAL/%f'# command to use to archive a logfile segment Thanks in advance Tuan Hoang ANh
[GENERAL] Case Insensitive Foreign Key Constraint
Hi all,
Is there a way to have text-type foreign keys be case insensitive?
development=# CREATE TABLE foo (foo text PRIMARY KEY);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index foo_pkey for
table foo
CREATE TABLE
development=# INSERT INTO foo VALUES ('foo');
INSERT 0 1
development=# CREATE TABLE foo1 (foo text REFERENCES foo);
CREATE TABLE
development=# INSERT INTO foo1 VALUES ('FOO');
ERROR: insert or update on table foo1 violates foreign key constraint
foo1_foo_fkey
DETAIL: Key (foo)=(FOO) is not present in table foo.
Thanks,
George
Re: [GENERAL] Case Insensitive Foreign Key Constraint
I would probably just have a check constraint that prevented the relevant PK field from being lower case in the first place. I had to do that recently, but my approach reflected the business rules. You may prefer to use citext: http://www.postgresql.org/docs/current/interactive/citext.html -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Getting a sample data set.
I am working with Ruby on Rails and I have stumbled into a situation which turned out to be, surprisingly for me, somewhat involved. Given a table shipments having a column called mode I want to extract one entire shipment row (all columns) for each distinct value of mode. Assuming that there are 1700 rows and that there are just five distinct values in use for mode then I want to return five rows with all their columns and each one having a different value for mode. If I use the distinct clause then I only return the rows making up the distinct clause. Employing this approach produces either many more matches than I want or only returns the mode column. While I could not accomplish this with a single ORM call to ActiveRecord I solved this using an iterator inside RoR. My programmatic solution was: x = Shipment.select(DISTINCT(mode)) ms = [] x.each do |s| ms Shipment.find_by_mode(s.mode) end Which gives me a collection of rows each having a different mode. But now I am curious how this is done in plain SQL. I have have not found any useful guide as to how to approach this problem in the reference materials I have to hand. I cannot believe that I am the first person to require this sort of thing of SQL so if anyone can point me to a reference that explicitly sets out how to accomplish this I would greatly appreciate it. -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Getting a sample data set.
On 18 Jan 2011, at 19:02, James B. Byrne wrote: Given a table shipments having a column called mode I want to extract one entire shipment row (all columns) for each distinct value of mode. Assuming that there are 1700 rows and that there are just five distinct values in use for mode then I want to return five rows with all their columns and each one having a different value for mode. But now I am curious how this is done in plain SQL. I have have not found any useful guide as to how to approach this problem in the reference materials I have to hand. I cannot believe that I am the first person to require this sort of thing of SQL so if anyone can point me to a reference that explicitly sets out how to accomplish this I would greatly appreciate it. Postgres has it's own extension for that, namely DISTINCT ON, used as follows: SELECT DISTINCT ON (mode) mode FROM shipments ORDER BY mode; For consistent results in the other column some more ordering would be required, or Postgres would just be returning the first row per mode that it encounters. Then again, that's often what people want in this case anyway. Standard SQL alternatives tend to get complex, using self-joins to weed out all the records you don't want (the exact term for such joins escapes me right now, that would help with Googling if you're looking for examples). Basically you do something like: SELECT s1.mode FROM shipments AS s1 WHERE NOT EXISTS ( SELECT NULL FROM shipments AS s2 WHERE s1.mode = s2.mode AND s1.somecolumn s2.somecolumn ) Basically you exclude all the records with the same mode that have a larger value for somecolumn than the lowest you encountered. Only the records with the lowest value for somecolumn remain for each mode. If you turn the sign around for that last condition you'd get the highest value instead. Important here is that somecolumn doesn't contain any values for the same mode where its values would be considered equal, or you end up with multiple matches for that mode. It gets extra interesting if you don't have any columns that are distinct per mode. In such cases you can join your table(s) against generate_series() or use a windowing function with ranking. This has gotten much easier with our new CTE's (see the WITH keyword). Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d35dad711701679817192! -- 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] Case Insensitive Foreign Key Constraint
From: Peter Geoghegan Subject: Re: [GENERAL] Case Insensitive Foreign Key Constraint I would probably just have a check constraint that prevented the relevant PK field from being lower case in the first place. I had to do that recently, but my approach reflected the business rules. This is what I've been looking at doing. My need reflects a customer's business rules as well. You may prefer to use citext: http://www.postgresql.org/docs/current/interactive/citext.html Interesting - good long term solution! Thanks Peter. -- Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PgEast: 2011, 2nd call for papers
Hey folks, PgEast is being held in NYC this year from 03/22-03-25. Get your papers in, the deadline is soon! http://www.postgresqlconference.org/ Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- 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] Getting a sample data set.
On Tue, January 18, 2011 13:23, Alban Hertroys wrote: Standard SQL alternatives tend to get complex, using self-joins to weed out all the records you don't want (the exact term for such joins escapes me right now, that would help with Googling if you're looking for examples). Would the term be a grouped self join? Basically you do something like: SELECT s1.mode FROM shipments AS s1 WHERE NOT EXISTS ( SELECT NULL FROM shipments AS s2 WHERE s1.mode = s2.mode AND s1.somecolumn s2.somecolumn ) I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Need help writing exclusion constraint
On Sat, 2011-01-15 at 19:17 +, Matthew Wilson wrote: create table event( destination_id integer not null references destination (destination_id), starts timestamp, ends timestamp ); I want to make sure that no two rows **with the same destination_id** overlap in time. First, you need to have some notion of overlaps, so you need to combine the starts and ends into a single value. I recommend trying the PERIOD datatype (as Andreas suggests). They don't have to be in the same column necessarily (you could use a functional index that combines the values), but typically it would be helpful anyway. If you use the PERIOD datatype, the overlaps operator is . So, assuming that the combined start/end is called during, the exclusion constraint might look something like: EXCLUDE USING gist (destination_id WITH =, during WITH ) You'll need to install the contrib module btree_gist first, so that = is indexable over integers using GiST. What's the above constraint says is: rows R1 and R2 conflict if R1.destination_id = R2.destination_id AND R1.during R2.during, and it will prevent R1 and R2 from both existing at the same time in your table. This method will be safe from race conditions. Hope this helps. Also, for more detailed examples that happen to be very similar to your problem, see: http://thoughts.j-davis.com/2009/11/08/temporal-keys-part-2/ http://thoughts.j-davis.com/2010/09/25/exclusion-constraints-are-generalized-sql-unique/ Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
On 18 Jan 2011, at 19:59, James B. Byrne wrote: On Tue, January 18, 2011 13:23, Alban Hertroys wrote: Standard SQL alternatives tend to get complex, using self-joins to weed out all the records you don't want (the exact term for such joins escapes me right now, that would help with Googling if you're looking for examples). Would the term be a grouped self join? Nope, but some Googling put me on the right track. It's called a correlated subquery. I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :) 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,4d35e9d011708045415059! -- 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] Need help writing exclusion constraint
On Sat, 2011-01-15 at 15:07 -0500, Daniel Popowich wrote: Constraint expressions can only be simple boolean expressions, so can refer only to the column(s) of the current row you're inserting/updating, Exclusion Constraints are a new feature in 9.0: http://www.postgresql.org/docs/9.0/static/ddl-constraints.html#DDL-CONSTRAINTS-EXCLUSION http://www.postgresql.org/docs/9.0/static/sql-createtable.html#SQL-CREATETABLE-EXCLUDE They allow you to constrain across rows, much like UNIQUE (in fact, the constraints that can be expressed by an exclusion constraint are a superset of the constraints that can be expressed by UNIQUE). so to refer to other records (which you'll need to do to compare destination_ids) you need to create a function...something along the lines of this: ... ALTER TABLE event ADD CONSTRAINT event_overlap CHECK(overlap_at_dest(destination_id, starts, ends)); As Tomas said, that's an unsafe thing to do. I do not recommend using a table-reading function in a check constraint. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
Alban Hertroys dal...@solfertje.student.utwente.nl writes: On 18 Jan 2011, at 19:59, James B. Byrne wrote: I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :) Actually, peculiar to is perfectly correct here, though a bit old-fashioned. According to my dictionary, it originally meant belonging exclusively to. The meaning of odd developed in the 17th century, long after the other meaning. 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] Need help writing exclusion constraint
On Sat, 2011-01-15 at 21:32 +0100, Tomas Vondra wrote: ALTER TABLE event ADD CONSTRAINT event_overlap CHECK(overlap_at_dest(destination_id, starts, ends)); There's a race condition ... One way to fix this is locking I do not recommend locking. In fact, the primary reason that exclusion constraints exist is to prevent unnecessary locking for problems exactly like this. I included some links in my other reply that demonstrate how to avoid that excessive locking while still being safe from race conditions. Regards, Jeff Davis -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] excessive escaping in regular expression functions
Hello,
The following statement replaces an asterisk in a string with a double-escaped
asterisk:
SELECT regexp_replace('*',E'\\*',E'\*');
I got this result through experimentation and I am at a loss to explain why so
much escaping is necessary for the third argument. Is there a better way?
Cheers,
M
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] excessive escaping in regular expression functions
On Jan 18, 2011, at 14:52, A.M. wrote: Is there a better way? Use dollar quotes or standard quoting instead of E strings. Michael Glaesemann grzm seespotcode net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Getting a sample data set.
On Tue, January 18, 2011 14:28, Alban Hertroys wrote: Nope, but some Googling put me on the right track. It's called a correlated subquery. Thank you for this. I will delve further. I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :) No. I meant peculiar. As in characteristic of only one person, group, or thing; distinctive . . . to PostgreSQL Regards, -- *** E-Mail is NOT a SECURE channel *** James B. Byrnemailto:byrn...@harte-lyne.ca Harte Lyne Limited http://www.harte-lyne.ca 9 Brockley Drive vox: +1 905 561 1241 Hamilton, Ontario fax: +1 905 561 0757 Canada L8E 3C3 -- 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] Getting a sample data set.
On 18/01/2011 19:34, Tom Lane wrote: Alban Hertroysdal...@solfertje.student.utwente.nl writes: On 18 Jan 2011, at 19:59, James B. Byrne wrote: I can see the motivation for something like DISTINCT ON. I take it that this syntax is peculiar to PostgreSQL?: I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :) Actually, peculiar to is perfectly correct here, though a bit old-fashioned. According to my dictionary, it originally meant That's a phrase we use in this part of the world also or maybe it's just what I learnt from my mother. :-) 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] Getting a sample data set.
On 18 Jan 2011, at 23:03, Raymond O'Donnell wrote: I suppose you meant particular? Yes, definitely. Although I'm sure some would find it peculiar as well :) Actually, peculiar to is perfectly correct here, though a bit old-fashioned. According to my dictionary, it originally meant That's a phrase we use in this part of the world also or maybe it's just what I learnt from my mother. :-) I can't really comment on that, I'm Dutch. English is but my second language. This usage of peculiar is just peculiar to me. But, I've learned something new from you guys, so thanks for that. I'll probably be pestering some British friends with it in the coming days :) Talk about getting off-topic... I didn't get the impression anyone did mind though. Alban Hertroys -- Screwing up is an excellent way to attach something to the ceiling. !DSPAM:737,4d362a8a11702229214598! -- 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] help understanding collation order
Peter Eisentraut wrote: On tis, 2011-01-18 at 10:33 +1100, raf wrote: p.s. if anyone in debian locale land is listening, 'E' does not sort before ','. what were you thinking? :-) What is actually happening is that the punctuation is sorted in a second pass after the letters. Which is both correct according to the relevant standards and also practical in many situations. i have no doubt that what you say is true just as i have no doubt that it is also incorrect and impractical in every situation i'll ever encounter. i can't imagine ever wanting CLARK to sort both before and after CLARKE. i'll just re-sort all name-ordered reports in the client. pity. It's usually actually the Mac OS X locales that are broken. only when ignoring the principle of least astonishment. :-) but it's not the same locale on the two hosts. the macosx locale is en_AU. the debian locale is en_AU.utf8 so i don't think they can be compared for brokenness purposes in this case. they may both be correct. cheers, raf -- 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 table/column has semi-current COUNTs?
A while back someone answered a question I was running into - poor performance on count(*) from a table which is constantly growing. The answer was like looking at oracle's V$ table - and I could get a semi-current count. (I do not need the exact count - just checking to make sure the table is growing). Ozz -- 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] help understanding collation order
raf wrote: Peter Eisentraut wrote: On tis, 2011-01-18 at 10:33 +1100, raf wrote: p.s. if anyone in debian locale land is listening, 'E' does not sort before ','. what were you thinking? :-) What is actually happening is that the punctuation is sorted in a second pass after the letters. Which is both correct according to the relevant standards and also practical in many situations. i have no doubt that what you say is true just as i have no doubt that it is also incorrect and impractical in every situation i'll ever encounter. i can't imagine ever wanting CLARK to sort both before and after CLARKE. i'll just re-sort all name-ordered reports in the client. pity. better idea: i'll separate the family name from the personal names using regexp_replace() and use them separately in the order by clause so the sorting can remain in postgres where it belongs without any commas getting in the way. cheers, raf -- 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 table/column has semi-current COUNTs?
Hello 2011/1/19 Ozz Nixon ozzni...@gmail.com: A while back someone answered a question I was running into - poor performance on count(*) from a table which is constantly growing. The answer was like looking at oracle's V$ table - and I could get a semi-current count. (I do not need the exact count - just checking to make sure the table is growing). http://www.commandprompt.com/community/pgdocs81/planner-stats-details column reltuples in table pg_class. Regards Pavel Stehule Ozz -- 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
