Re: [GENERAL] Why doesn't COPY support the HEADER options for tab-separated output?
On 12/08/13 23:18, Bruce Momjian wrote: On Mon, Aug 12, 2013 at 03:17:00PM -0700, Jeff Janes wrote: On Mon, Aug 12, 2013 at 2:21 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Aug 12, 2013 at 02:15:25PM -0700, Joe Van Dyk wrote: Mostly just curious, as this is preventing me from using tab-separated output. I'd like there to be a header in my files. I have to use CSVs instead. Late to the discussion, but it does work to set format=csv and delimiter = E'\t' to get tab-separated. Be nice not to have to though. -- 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] Getting list of supported types in Postgres
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjianbr...@momjian.us wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
Regards,
Ivan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] SSL connection has been closed unexpectedly
Dear community We have a problem on our development database server, which supports a PHP application, which connects to it from a different server. Sometimes, around 1 in 4 page loads, it fails and reports the following error message: FATAL: terminating connection due to administrator command SSL connection has been closed unexpectedly Reloading the page usually works, sometimes doesn't, sometimes it requires several more refresh attempts before it magically works again. The odd thing is that we also have a live platform that is set up in the same way, and this does not occur, thankfully, but I expect it could. I've tried turning off all SSL features on the development platform, but oddly, the same problem persists. I've also tried whacking the logging level up to debug5, but still nothing appears in the PG logs when the problem occurs. Does anybody have any idea what could be happening here? Many thanks in advance Stuart Ford This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended only for the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or its content may result in legal liability. If you have received the email in error, please immediately notify us by telephone on +44 333 666 or email gl...@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internet transmission. Any views/opinions expressed within this email and any attachments are that of the individual and not necessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham, B1 1TT. Registered in England Wales. Registered Company No. 06194523. -- 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] Why doesn't COPY support the HEADER options for tab-separated output?
On Thu, Aug 15, 2013 at 09:25:07AM +0100, Richard Huxton wrote:
On 12/08/13 23:18, Bruce Momjian wrote:
On Mon, Aug 12, 2013 at 03:17:00PM -0700, Jeff Janes wrote:
On Mon, Aug 12, 2013 at 2:21 PM, Bruce Momjian br...@momjian.us wrote:
On Mon, Aug 12, 2013 at 02:15:25PM -0700, Joe Van Dyk wrote:
Mostly just curious, as this is preventing me from using tab-separated
output.
I'd like there to be a header in my files. I have to use CSVs instead.
Late to the discussion, but it does work to set format=csv and
delimiter = E'\t' to get tab-separated. Be nice not to have to
though.
I assume that is going to add quotes to a field that contains quotes:
CREATE TABLE test(x TEXT);
INSERT INTO test VALUES ('a');
COPY test TO STDOUT WITH (FORMAT CSV, DELIMITER E'\t');
a
I have added this to the TODO list:
Allow COPY text format to output a header
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] SSL connection has been closed unexpectedly
On 15 August 2013 12:41, Stuart Ford stuart.f...@glide.uk.com wrote: Dear community We have a problem on our development database server, which supports a PHP application, which connects to it from a different server. Sometimes, around 1 in 4 page loads, it fails and reports the following error message: FATAL: terminating connection due to administrator command SSL connection has been closed unexpectedly This has nothing to do with SSL. You have an administrator who's issuing commands to close database connections. Those just happen to be SSL connections. Perhaps the Linux OOM killer is at work here? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Re: [GENERAL] Getting list of supported types in Postgres
On Thu, Aug 15, 2013 at 11:33:42AM +0200, Ivan Radovanovic wrote:
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjianbr...@momjian.us wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I
need, but it is still missing info if type can have precision, and
apparently aliases are missing (no decimal for numeric, no character
for char and so on). Additionally it would be great if I could
somehow also get information if type can be indexed.
Well, some of that mapping happens in src/backend/parser/gram.y,
particularly the Numeric rule. The meaning of the precision/scale is
type-specific, but if you do -E and \d tablename, you can see how the
modifiers are shown for user-defined columns.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
--
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] SSL connection has been closed unexpectedly
Alban I would agree with you, except it still happens even after I have disabled all SSL related stuff in postgresql.conf and pg_hba.conf. I've also no evidence of any out of memory events on the server. Stuart -- From: Alban Hertroys haram...@gmail.com Date: Thursday, 15 August 2013 13:31 To: Stuart Ford stuart.f...@glide.uk.com Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Subject: Re: [GENERAL] SSL connection has been closed unexpectedly On 15 August 2013 12:41, Stuart Ford stuart.f...@glide.uk.com wrote: Dear community We have a problem on our development database server, which supports a PHP application, which connects to it from a different server. Sometimes, around 1 in 4 page loads, it fails and reports the following error message: FATAL: terminating connection due to administrator command SSL connection has been closed unexpectedly This has nothing to do with SSL. You have an administrator who's issuing commands to close database connections. Those just happen to be SSL connections. Perhaps the Linux OOM killer is at work here? -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest. This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended only for the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or its content may result in legal liability. If you have received the email in error, please immediately notify us by telephone on +44 333 666 or email gl...@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internet transmission. Any views/opinions expressed within this email and any attachments are that of the individual and not necessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham, B1 1TT. Registered in England Wales. Registered Company No. 06194523. -- 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] SSL connection has been closed unexpectedly
On 15 August 2013 15:29, Stuart Ford stuart.f...@glide.uk.com wrote: Alban I would agree with you, except ...that you misread what I wrote ;) it still happens even after I have disabled all SSL related stuff in postgresql.conf and pg_hba.conf. Well, of course. Why would that make any difference? Your problem is not SSL related. It would probably happen with a normal connection as well. That is, unless some SSL-related process on your server is crashing or something like that. I've also no evidence of any out of memory events on the server. That's only one of the candidates for processes that kill your connections. Something or someone is doing that. There's probably some evidence about this in log files, if you're on an OS that logs stuff. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
[GENERAL] last_vacuum field in not updated
Hi, I am using Postgresql 9.2 where I have a table table1. I used vacuum command in that table, but last_vacuum column of pg_stat_user_tables has not been updated. Any idea for it? \d table1 Table public.table1 Column | Type | Modifiers --+--+ batterysessionid | integer | not null processedflag| smallint | not null default 0 Indexes: table1_pkey PRIMARY KEY, btree (batterysessionid) qualitycore=# select * from pg_stat_user_tables where last_vacuum = '2013-07-28 20:04:34.821115-04'; -[ RECORD 1 ]-+-- relid | 5452445 schemaname| public relname | table1 seq_scan | 55394 seq_tup_read | 458097965 idx_scan | 3056888 idx_tup_fetch | 345092348 n_tup_ins | 1023618 n_tup_upd | 643602 n_tup_del | 642037 n_tup_hot_upd | 175225 n_live_tup| 381549 n_dead_tup| 77130 last_vacuum | 2013-07-28 20:04:34.821115-04 last_autovacuum | 2013-05-01 00:55:01.970799-04 last_analyze | 2013-07-28 20:04:34.903569-04 last_autoanalyze | 2013-05-01 06:04:12.905961-04 vacuum_count | 93 autovacuum_count | 248 analyze_count | 95 autoanalyze_count | 560 qualitycore=# vacuum table1; VACUUM qualitycore=# vacuum verbose table1; INFO: vacuuming public.table1 INFO: index table1_pkey now contains 381973 row versions in 1878 pages DETAIL: 0 index row versions were removed. 104 index pages have been deleted, 103 are currently reusable. CPU 0.00s/0.00u sec elapsed 0.00 sec. INFO: table1: found 0 removable, 1120 nonremovable row versions in 10 out of 2286 pages DETAIL: 0 dead row versions cannot be removed yet. There were 1538 unused item pointers. 0 pages are entirely empty. CPU 0.00s/0.00u sec elapsed 0.00 sec. VACUUM select * from pg_stat_user_tables where last_vacuum = '2013-07-28 20:04:34.821115-04'; -[ RECORD 1 ]-+-- relid | 5452445 schemaname| public relname | table1 seq_scan | 55394 seq_tup_read | 458097965 idx_scan | 3056888 idx_tup_fetch | 345092348 n_tup_ins | 1023618 n_tup_upd | 643602 n_tup_del | 642037 n_tup_hot_upd | 175225 n_live_tup| 381549 n_dead_tup| 77130 last_vacuum | 2013-07-28 20:04:34.821115-04 last_autovacuum | 2013-05-01 00:55:01.970799-04 last_analyze | 2013-07-28 20:04:34.903569-04 last_autoanalyze | 2013-05-01 06:04:12.905961-04 vacuum_count | 93 autovacuum_count | 248 analyze_count | 95 autoanalyze_count | 560 Please let me know. Thanks.
Re: [GENERAL] What type of index do I need for this JOIN?
Robert James srobertja...@gmail.com wrote: On 8/14/13, Kevin Grittner kgri...@ymail.com wrote: Robert James srobertja...@gmail.com wrote: I'm confused: What's the difference between col LIKE 'foo%' and col LIKE f1 || '%' ? The planner knows that 'foo%' doesn't start with a wildcard. Either way, it's anchored to the beginning of the string. Not necessarily. I see, yes, f1 might include a wildcard. Exactly. Is there a way to escape f1 so that wildcards aren't interpreted? That's anyway the behavior I need, of course. And will that help the planner? I don't think there is any way to get that to work in general, and in particular you are trying to have the right-hand side of the LIKE treated as having escapes; it would be a lot to ask of the planner to somehow recognize that part of the result of the (concatenation) expression should be treated as escaped and part not. What type of index will help the planner here? Well, with the query as you have it, you might get a trigram index to be of some help, but I think you might want to give up on LIKE or regular expressions. Perhaps you could do a range test directly, rather than wrangling the wildcards: (col = f1 AND col = (f1 || 'zzz')) You would probably want to write a function to calculate that ending value; I'm just trying to give a rough suggestion here. The idea is to avoid the danger of wildcards in the f1 values by constructing the range without scanning for special characters and basing the test on that. You could *also* do the LIKE test if desired. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Getting list of supported types in Postgres
On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjianbr...@momjian.us wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
The docs would seem to be the place to go:
http://www.postgresql.org/docs/9.2/interactive/datatype.html
Regards,
Ivan
--
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] vacuumdb uses a lot of disk
Alexander Shutyaev shuty...@gmail.com wrote: We have the following issue. When we use vacuumdb (NOT full) on our postgres database (~320Gb) it takes up ~10Gb of disk space which is never returned. Why is the space not returned? Does that happen every time? (i.e., if you run vacuumdb 10 times in a row while there is no other activity against the cluster, does it take up 100GB more space?) If not, you are probably doing an initial build of information on tuple visibility, free space in the tables, and column statistics. The cluster can run without these, but it won't be as efficient. To minimize down time, this data is built up opportunistically once the service is up and running. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL 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] Getting list of supported types in Postgres
On 08/15/13 16:30, Adrian Klaver napisa:
On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjianbr...@momjian.us wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
The docs would seem to be the place to go:
http://www.postgresql.org/docs/9.2/interactive/datatype.html
Thanks Adrian, sometimes we overlook most obvious solutions :-)
Now I just need to find out which types can be indexed (and which types
can be part of PK)
Regards,
Ivan
--
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 list of supported types in Postgres
On 08/15/2013 07:37 AM, Ivan Radovanovic wrote:
On 08/15/13 16:30, Adrian Klaver napisa:
On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjianbr...@momjian.us wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
The docs would seem to be the place to go:
http://www.postgresql.org/docs/9.2/interactive/datatype.html
Thanks Adrian, sometimes we overlook most obvious solutions :-)
Now I just need to find out which types can be indexed (and which types
can be part of PK)
http://www.postgresql.org/docs/9.2/interactive/indexes.html
Regards,
Ivan
--
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] Getting list of supported types in Postgres
On 08/15/13 16:49, Adrian Klaver napisa:
On 08/15/2013 07:37 AM, Ivan Radovanovic wrote:
On 08/15/13 16:30, Adrian Klaver napisa:
On 08/15/2013 02:33 AM, Ivan Radovanovic wrote:
On 08/15/13 05:23, Michael Paquier napisa:
On Thu, Aug 15, 2013 at 6:00 AM, Bruce Momjianbr...@momjian.us
wrote:
Try psql -E, and run the \dT command to see the query it uses.
You have also the following commands:
- ¥dT+, all types with addition information like its size ('var' is
for example variable length)
- ¥dTS+, this includes also the catalog types
Regards,
Hi Michael and Bruce,
Thank you for taking your time to reply.
If I run \dTS+ that returns something that resembles information I
need,
but it is still missing info if type can have precision, and apparently
aliases are missing (no decimal for numeric, no character for char and
so on). Additionally it would be great if I could somehow also get
information if type can be indexed.
This doesn't have to come through SQL (I simply assumed that info
can be
obtained either from information schema or Postgress specific views and
tables) - if you can point me to some source file that would be
acceptable too :-)
The docs would seem to be the place to go:
http://www.postgresql.org/docs/9.2/interactive/datatype.html
Thanks Adrian, sometimes we overlook most obvious solutions :-)
Now I just need to find out which types can be indexed (and which types
can be part of PK)
http://www.postgresql.org/docs/9.2/interactive/indexes.html
doesn't list which types can be indexed and which can't?
--
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 list of supported types in Postgres
On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http://www.postgresql.org/docs/9.2/interactive/indexes.html doesn't list which types can be indexed and which can't? Postgres can handle a variety of indexes including indexing on expressions, which is why I pointed you to that link. To cut to the chase, in the above link at: http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html there is this: SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid ORDER BY index_method, opfamily_name, opfamily_operator; -- 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] Getting list of supported types in Postgres
On 08/15/13 16:59, Adrian Klaver napisa: On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http://www.postgresql.org/docs/9.2/interactive/indexes.html doesn't list which types can be indexed and which can't? Postgres can handle a variety of indexes including indexing on expressions, which is why I pointed you to that link. To cut to the chase, in the above link at: http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html there is this: SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid ORDER BY index_method, opfamily_name, opfamily_operator; Thanks Adrian, but question was how to decide which types are indexable - query which you sent returns list of operators defined for some types - for example it returns operators for bytea too, and you can't index by bytea, so I don't see how you could decide if type can be indexed based on this? -- 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 list of supported types in Postgres
Ivan Radovanovic radovano...@gmail.com writes: Thanks Adrian, but question was how to decide which types are indexable A little bit of research in the system-catalogs documentation will show you how to find the types that can be accepted by some index opclass (hint: pg_opclass.opcintype::regtype). As far as the other question goes, you could look for types that have a pg_type.typmodin function -- though I'm not sure whether you want to consider every possible usage of typmods as being a precision. 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] Getting list of supported types in Postgres
On 08/15/13 17:15, Tom Lane napisa: Ivan Radovanovicradovano...@gmail.com writes: Thanks Adrian, but question was how to decide which types are indexable A little bit of research in the system-catalogs documentation will show you how to find the types that can be accepted by some index opclass (hint: pg_opclass.opcintype::regtype). As far as the other question goes, you could look for types that have a pg_type.typmodin function -- though I'm not sure whether you want to consider every possible usage of typmods as being a precision. regards, tom lane Thanks Tom, I will take type definitions from documentation (as Adrian suggested), and it looks like your pg_opclass suggestion will solve indexability question. Case closed I guess :-) Regards, Ivan -- 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 list of supported types in Postgres
On 08/15/2013 08:07 AM, Ivan Radovanovic wrote: On 08/15/13 16:59, Adrian Klaver napisa: On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http://www.postgresql.org/docs/9.2/interactive/indexes.html doesn't list which types can be indexed and which can't? Postgres can handle a variety of indexes including indexing on expressions, which is why I pointed you to that link. To cut to the chase, in the above link at: http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html there is this: SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid ORDER BY index_method, opfamily_name, opfamily_operator; Thanks Adrian, but question was how to decide which types are indexable - query which you sent returns list of operators defined for some types - for example it returns operators for bytea too, and you can't index by bytea, Actually you can: CREATE TABLE bytea_test(id int, fld_1 bytea); test=# \d bytea_test Table public.bytea_test Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | test=# CREATE INDEX i ON bytea_test (fld_1); test=# \d bytea_test Table public.bytea_test Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | Indexes: i btree (fld_1) -- 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] Getting list of supported types in Postgres
On 08/15/13 17:27, Adrian Klaver napisa: On 08/15/2013 08:07 AM, Ivan Radovanovic wrote: On 08/15/13 16:59, Adrian Klaver napisa: On 08/15/2013 07:53 AM, Ivan Radovanovic wrote: Now I just need to find out which types can be indexed (and which types can be part of PK) http://www.postgresql.org/docs/9.2/interactive/indexes.html doesn't list which types can be indexed and which can't? Postgres can handle a variety of indexes including indexing on expressions, which is why I pointed you to that link. To cut to the chase, in the above link at: http://www.postgresql.org/docs/9.2/interactive/indexes-opclass.html there is this: SELECT am.amname AS index_method, opf.opfname AS opfamily_name, amop.amopopr::regoperator AS opfamily_operator FROM pg_am am, pg_opfamily opf, pg_amop amop WHERE opf.opfmethod = am.oid AND amop.amopfamily = opf.oid ORDER BY index_method, opfamily_name, opfamily_operator; Thanks Adrian, but question was how to decide which types are indexable - query which you sent returns list of operators defined for some types - for example it returns operators for bytea too, and you can't index by bytea, Actually you can: CREATE TABLE bytea_test(id int, fld_1 bytea); test=# \d bytea_test Table public.bytea_test Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | test=# CREATE INDEX i ON bytea_test (fld_1); test=# \d bytea_test Table public.bytea_test Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | Indexes: i btree (fld_1) Didn't know that - I just tried on one existing table and it failed on account of index row too short ERROR: index row requires 14616 bytes, maximum size is 8191 SQL state: 54000 Although it looked suspicious like it could be solved by defining custom tablespace (never did that on Postgres so I am not sure if it would work), I assumed that it is because bytea can't be indexed. Obviously I learned one more new thing today :-) Thanks, Ivan -- 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 list of supported types in Postgres
On 15 August 2013 17:33, Ivan Radovanovic radovano...@gmail.com wrote: On 08/15/13 17:27, Adrian Klaver napisa: Actually you can: CREATE TABLE bytea_test(id int, fld_1 bytea); test=# \d bytea_test Table public.bytea_test Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | test=# CREATE INDEX i ON bytea_test (fld_1); test=# \d bytea_test Table public.bytea_test Column | Type | Modifiers +-+--- id | integer | fld_1 | bytea | Indexes: i btree (fld_1) Didn't know that - I just tried on one existing table and it failed on account of index row too short ERROR: index row requires 14616 bytes, maximum size is 8191 SQL state: 54000 Although it looked suspicious like it could be solved by defining custom tablespace (never did that on Postgres so I am not sure if it would work), I assumed that it is because bytea can't be indexed. Your conclusion is not entirely correct; the problem is that each value in an index is limited to 8191 bytes. Your bytea value is longer than that and therefore the value can't be fit into an index entry. Hence the error. People usually work around that, for example by defining a functional index on only the first 8191 bytes. I haven't personally done that with bytea columns, but I think it's safe to assume that is possible. -- If you can't see the forest for the trees, Cut the trees and you'll see there is no forest.
Re: [GENERAL] Why doesn't COPY support the HEADER options for tab-separated output?
On Thu, Aug 15, 2013 at 1:25 AM, Richard Huxton d...@archonet.com wrote: On 12/08/13 23:18, Bruce Momjian wrote: On Mon, Aug 12, 2013 at 03:17:00PM -0700, Jeff Janes wrote: On Mon, Aug 12, 2013 at 2:21 PM, Bruce Momjian br...@momjian.us wrote: On Mon, Aug 12, 2013 at 02:15:25PM -0700, Joe Van Dyk wrote: Mostly just curious, as this is preventing me from using tab-separated output. I'd like there to be a header in my files. I have to use CSVs instead. Late to the discussion, but it does work to set format=csv and delimiter = E'\t' to get tab-separated. Be nice not to have to though. Also, the escaping/quoting mechanisms are different. I often find the escapes of the TEXT format more convenient than the CSV with E'\t'. Cheers, Jeff -- 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] MinGW compiled client library
On 08/13/2013 12:35 PM, Michael Cronenworth wrote:
When the client library (version 9.2.x) is compiled with a MinGW-w64
environment
the resulting libpq.dll will not function. This has been reported previously
with two bug reports, which have gone untouched.
Bug 8151:
http://www.postgresql.org/message-id/e1ubelm-0007nk...@wrigleys.postgresql.org
Bug 8162:
http://www.postgresql.org/message-id/e1uclpd-l4...@wrigleys.postgresql.org
I have tried compiling with every option enabled and every option disabled.
Does
anyone have any pointers or would anyone be willing to help solve this issue?
The connection problem was caused by incorrect error code checking after
connect(). The libpq interface defines a macro SOCK_ERRNO that on windows
calls WSAGetLastError(), which returns a Windows error code. The check after
connect() compares the Windows error code against the UNIX errno
EINPROGRRESS/EWOULDBLOCK.
I see there is a test in win32.h to see if EINPROGRESS is defined. It may not
have been defined in very old MinGW environments, but it is defined now. The
attached patches resolve the issue.
The compiled libpq.dll allows me to connect and run a SELECT and print out data.
--- postgresql-9.2.4/src/interfaces/libpq/fe-connect.c.orig 2013-08-15 09:08:59.850609595 -0500
+++ postgresql-9.2.4/src/interfaces/libpq/fe-connect.c 2013-08-15 09:42:59.001463906 -0500
@@ -1778,10 +1778,16 @@
if (connect(conn-sock, addr_cur-ai_addr,
addr_cur-ai_addrlen) 0)
{
+#ifndef WIN32
if (SOCK_ERRNO == EINPROGRESS ||
SOCK_ERRNO == EWOULDBLOCK ||
SOCK_ERRNO == EINTR ||
+#else
+ if (SOCK_ERRNO == WSAEINPROGRESS ||
+ SOCK_ERRNO == WSAEWOULDBLOCK ||
+ SOCK_ERRNO == WSAEINTR ||
SOCK_ERRNO == 0)
+#endif
{
/*
* This is fine - we're in non-blocking mode, and
--- postgresql-9.2.4/src/interfaces/libpq/fe-misc.c.orig 2013-04-01 13:20:36.0 -0500
+++ postgresql-9.2.4/src/interfaces/libpq/fe-misc.c 2013-08-15 10:08:03.190928760 -0500
@@ -656,7 +656,11 @@
conn-inBufSize - conn-inEnd);
if (nread 0)
{
+#ifndef WIN32
if (SOCK_ERRNO == EINTR)
+#else
+ if (SOCK_ERRNO == WSAEINTR)
+#endif
goto retry3;
/* Some systems return EAGAIN/EWOULDBLOCK for no data */
#ifdef EAGAIN
@@ -664,12 +668,20 @@
return someread;
#endif
#if defined(EWOULDBLOCK) (!defined(EAGAIN) || (EWOULDBLOCK != EAGAIN))
+#ifndef WIN32
if (SOCK_ERRNO == EWOULDBLOCK)
+#else
+ if (SOCK_ERRNO == WSAEWOULDBLOCK)
+#endif
return someread;
#endif
/* We might get ECONNRESET here if using TCP and backend died */
#ifdef ECONNRESET
+#ifndef WIN32
if (SOCK_ERRNO == ECONNRESET)
+#else
+ if (SOCK_ERRNO == WSAECONNRESET)
+#endif
goto definitelyFailed;
#endif
/* pqsecure_read set the error message for us */
@@ -749,7 +761,11 @@
conn-inBufSize - conn-inEnd);
if (nread 0)
{
+#ifndef WIN32
if (SOCK_ERRNO == EINTR)
+#else
+ if (SOCK_ERRNO == WSAEINTR)
+#endif
goto retry4;
/* Some systems return EAGAIN/EWOULDBLOCK for no data */
#ifdef EAGAIN
@@ -757,12 +773,20 @@
return 0;
#endif
#if defined(EWOULDBLOCK) (!defined(EAGAIN) || (EWOULDBLOCK != EAGAIN))
+#ifndef WIN32
if (SOCK_ERRNO == EWOULDBLOCK)
+#else
+ if (SOCK_ERRNO == WSAEWOULDBLOCK)
+#endif
return 0;
#endif
/* We might get ECONNRESET here if using TCP and backend died */
#ifdef ECONNRESET
+#ifndef WIN32
if (SOCK_ERRNO == ECONNRESET)
+#else
+ if (SOCK_ERRNO == WSAECONNRESET)
+#endif
goto definitelyFailed;
#endif
/* pqsecure_read set the error message for us */
@@ -838,10 +862,18 @@
break;
#endif
#if defined(EWOULDBLOCK) (!defined(EAGAIN) || (EWOULDBLOCK != EAGAIN))
+#ifndef WIN32
case EWOULDBLOCK:
+#else
+case WSAEWOULDBLOCK:
+#endif
break;
#endif
+#ifndef WIN32
case EINTR:
+#else
+case WSAEINTR:
+#endif
continue;
default:
--- postgresql-9.2.4/src/interfaces/libpq/fe-secure.c.orig 2013-08-15 10:10:44.039355056 -0500
+++ postgresql-9.2.4/src/interfaces/libpq/fe-secure.c 2013-08-15 10:22:57.767650717 -0500
@@ -433,12 +433,20 @@
#if defined(EWOULDBLOCK) (!defined(EAGAIN) || (EWOULDBLOCK != EAGAIN))
case EWOULDBLOCK:
#endif
+#ifndef WIN32
case EINTR:
+#else
+case WSAEWOULDBLOCK:
+case WSAEINTR:
+#endif
/* no error message, caller is expected to retry */
break;
#ifdef ECONNRESET
case ECONNRESET:
+#ifdef WIN32
+case WSAECONNRESET:
+#endif
printfPQExpBuffer(conn-errorMessage,
libpq_gettext(
server closed the connection unexpectedly\n
@@ -617,7 +625,12 @@
#if defined(EWOULDBLOCK) (!defined(EAGAIN) || (EWOULDBLOCK != EAGAIN))
case EWOULDBLOCK:
#endif
+#ifndef WIN32
case EINTR:
+#else
+case WSAEWOULDBLOCK:
+case WSAEINTR:
+#endif
/* no error message, caller is expected to retry */
break;
@@ -629,6 +642,9 @@
#ifdef ECONNRESET
case ECONNRESET:
#endif
+#ifdef WIN32
+case
[GENERAL] Immediate Constraints
The direct question is: what is the advantage of an immediate constraint? My habit is to add constraints to my databases and my first lesson was to make them deferrable. But a recent fight with pg_restore taught me that to do a pg_restore that is complex, you need to defer the constraints. I cobbled a way to do that as I do the pg_restore. But that raised a question of why not just make the constraints all deferred and simplify my pg_restore process. Are immediate constraints more efficient? Does this relate to transaction isolation in that the data would be consistent after each statement and therefor give better stability when multiple transactions are running at the same time? My brain is asking this question because so far in my experience, the issues with constraints are solved by making them deferred. If I made them immediate, would I just bump into a different set of issues whose solution would be to make the constraints immediate? Thank you, Perry signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [GENERAL] Debugging Postgres?
On 08/13/2013 01:51 PM, Barth Weishoff wrote: Hello I'm having an interesting issue with PGSQL. It seems that I'm experiencing timeouts at various times. The servers are not busy and have plenty of resources. The databases are ~50GB in size, the systems typically have 8-12GB physical RAM, and the connections are low (less than 15 at any given time). The issue I'm seeing is that randomly I'm getting these pauses, or stalls, while trying to simply connect to the database server(s) from connected clients using the psql command line client. I cannot tell if the server is even getting the request for service as they don't seem to show up in the logs at the time the event is occurring, so I'm thinking it's maybe a client-side issue. Is there a good general starting place for debugging these types of issues ? General recommendations: 1. A copy of PostgreSQL High Performance by Greg Smith should be on your shelf. 2. Increase your logging and be sure to log connections so you can correlate what you observe with your PostgreSQL logs. I'm currently logging all queries and connections with minimal overhead (though I'm routing the logs to a separate server as they accumulate about 4GB/week). 3. Use a log-analyzer. Pgbadger for PostgreSQL and, perhaps, a general system activity graphing program. Sysusage is easy to install if you don't have something in place already for system activity graphing. 4. Be specific when reporting your issue. There are issues that only occur with certain versions of PostgreSQL or on selected operating-systems (even on specific kernel versions). When you have seemingly random slowdowns and have eliminated the usual suspects like unusually large queries or request spikes you should look at write cache sizing. The default settings on many Linux distributions are not optimal for a dedicated database server and can lead to periodic storms of writes. Since the OS kernel parameters are ratios of available RAM, large amounts of RAM can actually exacerbate this problem since it allows the OS to accumulate larger amounts of data that require writing to disk before hitting aggressive disk write trigger threshold. Cheers, Steve -- 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] SSL connection has been closed unexpectedly
Guy No, we don't. It's also not happening on another platform which uses the same switch stack (and indeed VMWare cluster), so these aren't factors. Stuart On 15/08/2013 16:59, Guy Helmer ghel...@palisadesystems.com wrote: On Aug 15, 2013, at 5:41 AM, Stuart Ford stuart.f...@glide.uk.com wrote: Dear community We have a problem on our development database server, which supports a PHP application, which connects to it from a different server. Sometimes, around 1 in 4 page loads, it fails and reports the following error message: FATAL: terminating connection due to administrator command SSL connection has been closed unexpectedly Reloading the page usually works, sometimes doesn't, sometimes it requires several more refresh attempts before it magically works again. The odd thing is that we also have a live platform that is set up in the same way, and this does not occur, thankfully, but I expect it could. I've tried turning off all SSL features on the development platform, but oddly, the same problem persists. I've also tried whacking the logging level up to debug5, but still nothing appears in the PG logs when the problem occurs. Does anybody have any idea what could be happening here? Many thanks in advance Stuart Ford Any chance you are using HP ProCurve switches? I believe we have seen these switches corrupt SSL connections when systems use flow control signaling. Utterly bizarre behavior, but we've seen it at multiple customer sites. Guy This email and any attachments contain confidential and proprietary information of Glide Utilities Limited intended only for the use of the person to whom it is addressed. Unauthorised disclosure, copying or distribution of the email or its content may result in legal liability. If you have received the email in error, please immediately notify us by telephone on +44 333 666 or email gl...@glide.uk.com The sender does not accept liability for any loss or damage from receipt or use thereof which arises as a result of internet transmission. Any views/opinions expressed within this email and any attachments are that of the individual and not necessarily that of Glide Utilities Limited. Glide is a registered trademark of Glide Utilities Limited. Registered Office: Alpha Tower, Suffolk Street Queensway, Birmingham, B1 1TT. Registered in England Wales. Registered Company No. 06194523. -- 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] SSL connection has been closed unexpectedly
On 08/15/2013 10:05 AM, Stuart Ford wrote: Guy No, we don't. It's also not happening on another platform which uses the same switch stack (and indeed VMWare cluster), so these aren't factors. For completeness sake: When you reset the SSL values did you restart the server? Left field category: Is there a replication server in the mix? Stuart -- 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] MinGW compiled client library
On 08/15/2013 10:59 AM, Michael Cronenworth wrote: The attached patches resolve the issue. Should I forward the patches on to the pgsql-hackers list for review or is this list sufficient? (First time PostgreSQL hacker.) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Streaming Replication Randomly Locking Up
Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 The replication works great for days, but randomly seems to lock up and replication halts. I verified that the two databases were out of sync with a query on both of them. Has anyone experienced this issue before? Here are some relevant config settings: Master: wal_level = hot_standby checkpoint_segments = 32 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f /dev/null' max_wal_senders = 2 wal_keep_segments = 32 Slave: wal_level = hot_standby checkpoint_segments = 32 #checkpoint_completion_target = 0.5 hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 #wal_receiver_status_interval = 10s #hot_standby_feedback = off Thank you for any help you can provide! Andrew
Re: [GENERAL] Streaming Replication Randomly Locking Up
I've never seen this happen. Looks like you might be using 9.1? Are you up to date on all the 9.1.x releases? Do you have just 1 slave syncing from the master? Which OS are you using? Did you verify that there aren't any network problems between the slave master? Or hardware problems (like the NIC dying, or dropping packets)? On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 The replication works great for days, but randomly seems to lock up and replication halts. I verified that the two databases were out of sync with a query on both of them. Has anyone experienced this issue before? Here are some relevant config settings: Master: wal_level = hot_standby checkpoint_segments = 32 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f /dev/null' max_wal_senders = 2 wal_keep_segments = 32 Slave: wal_level = hot_standby checkpoint_segments = 32 #checkpoint_completion_target = 0.5 hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 #wal_receiver_status_interval = 10s #hot_standby_feedback = off Thank you for any help you can provide! Andrew -- ~ L. Friedmannetll...@gmail.com LlamaLand https://netllama.linux-sxs.org -- 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] Streaming Replication Randomly Locking Up
Hi Lonni, Yes, I am using PG 9.1.9. Yes, 1 slave syncing from the master CentOS 6.4 I don't see any network or hardware issues (e.g. NIC) but will look more into this. They are communicating on a private network and switch. I forgot to mention that after I restart the slave, everything syncs right back up and all if working again so if it is a network issue, the replication is just stopping after some hiccup instead of retrying and resuming when things are back up. Thanks! On Thu, Aug 15, 2013 at 11:32 AM, Lonni J Friedman netll...@gmail.comwrote: I've never seen this happen. Looks like you might be using 9.1? Are you up to date on all the 9.1.x releases? Do you have just 1 slave syncing from the master? Which OS are you using? Did you verify that there aren't any network problems between the slave master? Or hardware problems (like the NIC dying, or dropping packets)? On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 The replication works great for days, but randomly seems to lock up and replication halts. I verified that the two databases were out of sync with a query on both of them. Has anyone experienced this issue before? Here are some relevant config settings: Master: wal_level = hot_standby checkpoint_segments = 32 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f /dev/null' max_wal_senders = 2 wal_keep_segments = 32 Slave: wal_level = hot_standby checkpoint_segments = 32 #checkpoint_completion_target = 0.5 hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 #wal_receiver_status_interval = 10s #hot_standby_feedback = off Thank you for any help you can provide! Andrew -- ~ L. Friedmannetll...@gmail.com LlamaLand https://netllama.linux-sxs.org
[GENERAL] Strange result with SELECT ... ORDER BY random() LIMIT 1 and JOINs
Hello, Consider the following tables and data: CREATE TABLE color ( color_id integer PRIMARY KEY, color_name text ); INSERT INTO color (color_id, color_name) VALUES (1, 'red'), (2, 'blue'), (3, 'green'), (4, 'yellow'), (5, 'grey'), (6, 'brown'), (7, 'black'), (8, 'white'), (9, 'white with wooden panels') ; CREATE TABLE car ( car_id integer PRIMARY KEY, car_name text ); INSERT INTO car (car_id, car_name) VALUES (1, 'Toyota Matrix'), (2, 'Mazda 3'), (3, 'Honda Fit'), (4, 'Ford F-150'), (5, 'Chevrolet Volt'), (6, 'Audi A4'), (7, 'Hyundai Elantra'), (8, 'Nissan Versa'), (9, 'Buick Estate Wagon') ; This query yields unexpected results (tested under 9.2.4): SELECT s.car_id, s.color_id AS subquery_color_id, co.color_id AS join_color_id, co.color_name FROM ( SELECT ca.car_id, ( SELECT color_id FROM color WHERE ca.car_id = ca.car_id -- dependency added to avoid getting the same value for every row in the output ORDER BY random() LIMIT 1 ) AS color_id FROM car ca ) s LEFT JOIN color co ON co.color_id = s.color_id; We can see the equality defined in the LEFT JOIN does not hold true for the subquery_color_id and join_color_id column aliases in the output. EXPLAIN also shows that the subplan for the inner subquery used to pick a random row from the color table appears twice. I don't really understand what is going on there, the result appears incorrect to me. The following page seems to offer some explanations as to what is happening: http://sql-info.de/postgresql/postgres-gotchas.html#1_6 . Indeed, adding OFFSET 0 to the s subquery solves the issue. Can somebody shed some light on this topic? Is this behaviour correct or should it be considered a bug? Thanks! Etienne -- 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] Streaming Replication Randomly Locking Up
Are you certain that there are no relevant errors in the database logs (on both master slave)? Also, are you sure that you didn't misconfigure logging such that errors wouldn't appear? On Thu, Aug 15, 2013 at 11:45 AM, Andrew Berman rexx...@gmail.com wrote: Hi Lonni, Yes, I am using PG 9.1.9. Yes, 1 slave syncing from the master CentOS 6.4 I don't see any network or hardware issues (e.g. NIC) but will look more into this. They are communicating on a private network and switch. I forgot to mention that after I restart the slave, everything syncs right back up and all if working again so if it is a network issue, the replication is just stopping after some hiccup instead of retrying and resuming when things are back up. Thanks! On Thu, Aug 15, 2013 at 11:32 AM, Lonni J Friedman netll...@gmail.com wrote: I've never seen this happen. Looks like you might be using 9.1? Are you up to date on all the 9.1.x releases? Do you have just 1 slave syncing from the master? Which OS are you using? Did you verify that there aren't any network problems between the slave master? Or hardware problems (like the NIC dying, or dropping packets)? On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 The replication works great for days, but randomly seems to lock up and replication halts. I verified that the two databases were out of sync with a query on both of them. Has anyone experienced this issue before? Here are some relevant config settings: Master: wal_level = hot_standby checkpoint_segments = 32 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f /dev/null' max_wal_senders = 2 wal_keep_segments = 32 Slave: wal_level = hot_standby checkpoint_segments = 32 #checkpoint_completion_target = 0.5 hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 #wal_receiver_status_interval = 10s #hot_standby_feedback = off Thank you for any help you can provide! Andrew -- 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] Streaming Replication Randomly Locking Up
The only thing I see that is a possibility for the issue is in the slave log: LOG: unexpected EOF on client connection LOG: could not receive data from client: Connection reset by peer I don't know if that's related or not as it could just be somebody running a query. The log file does seem to be riddled with these but the replication failures don't happen constantly. As far as I know I'm not swallowing any errors. The logging is all set as the default: log_destination = 'stderr' logging_collector = on #client_min_messages = notice #log_min_messages = warning #log_min_error_statement = error #log_min_duration_statement = -1 #log_checkpoints = off #log_connections = off #log_disconnections = off #log_error_verbosity = default I'm going to have a look at the NICs to make sure there's no issue there. Thanks again for your help! On Thu, Aug 15, 2013 at 11:51 AM, Lonni J Friedman netll...@gmail.comwrote: Are you certain that there are no relevant errors in the database logs (on both master slave)? Also, are you sure that you didn't misconfigure logging such that errors wouldn't appear? On Thu, Aug 15, 2013 at 11:45 AM, Andrew Berman rexx...@gmail.com wrote: Hi Lonni, Yes, I am using PG 9.1.9. Yes, 1 slave syncing from the master CentOS 6.4 I don't see any network or hardware issues (e.g. NIC) but will look more into this. They are communicating on a private network and switch. I forgot to mention that after I restart the slave, everything syncs right back up and all if working again so if it is a network issue, the replication is just stopping after some hiccup instead of retrying and resuming when things are back up. Thanks! On Thu, Aug 15, 2013 at 11:32 AM, Lonni J Friedman netll...@gmail.com wrote: I've never seen this happen. Looks like you might be using 9.1? Are you up to date on all the 9.1.x releases? Do you have just 1 slave syncing from the master? Which OS are you using? Did you verify that there aren't any network problems between the slave master? Or hardware problems (like the NIC dying, or dropping packets)? On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 The replication works great for days, but randomly seems to lock up and replication halts. I verified that the two databases were out of sync with a query on both of them. Has anyone experienced this issue before? Here are some relevant config settings: Master: wal_level = hot_standby checkpoint_segments = 32 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f /dev/null' max_wal_senders = 2 wal_keep_segments = 32 Slave: wal_level = hot_standby checkpoint_segments = 32 #checkpoint_completion_target = 0.5 hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 #wal_receiver_status_interval = 10s #hot_standby_feedback = off Thank you for any help you can provide! Andrew
Re: [GENERAL] Streaming Replication Randomly Locking Up
I'd suggest enhancing your logging to include time/datestamps for every entry, and also the client hostname. That will help to rule in/out those 'unexpected EOF' errors. On Thu, Aug 15, 2013 at 12:22 PM, Andrew Berman rexx...@gmail.com wrote: The only thing I see that is a possibility for the issue is in the slave log: LOG: unexpected EOF on client connection LOG: could not receive data from client: Connection reset by peer I don't know if that's related or not as it could just be somebody running a query. The log file does seem to be riddled with these but the replication failures don't happen constantly. As far as I know I'm not swallowing any errors. The logging is all set as the default: log_destination = 'stderr' logging_collector = on #client_min_messages = notice #log_min_messages = warning #log_min_error_statement = error #log_min_duration_statement = -1 #log_checkpoints = off #log_connections = off #log_disconnections = off #log_error_verbosity = default I'm going to have a look at the NICs to make sure there's no issue there. Thanks again for your help! On Thu, Aug 15, 2013 at 11:51 AM, Lonni J Friedman netll...@gmail.com wrote: Are you certain that there are no relevant errors in the database logs (on both master slave)? Also, are you sure that you didn't misconfigure logging such that errors wouldn't appear? On Thu, Aug 15, 2013 at 11:45 AM, Andrew Berman rexx...@gmail.com wrote: Hi Lonni, Yes, I am using PG 9.1.9. Yes, 1 slave syncing from the master CentOS 6.4 I don't see any network or hardware issues (e.g. NIC) but will look more into this. They are communicating on a private network and switch. I forgot to mention that after I restart the slave, everything syncs right back up and all if working again so if it is a network issue, the replication is just stopping after some hiccup instead of retrying and resuming when things are back up. Thanks! On Thu, Aug 15, 2013 at 11:32 AM, Lonni J Friedman netll...@gmail.com wrote: I've never seen this happen. Looks like you might be using 9.1? Are you up to date on all the 9.1.x releases? Do you have just 1 slave syncing from the master? Which OS are you using? Did you verify that there aren't any network problems between the slave master? Or hardware problems (like the NIC dying, or dropping packets)? On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 The replication works great for days, but randomly seems to lock up and replication halts. I verified that the two databases were out of sync with a query on both of them. Has anyone experienced this issue before? Here are some relevant config settings: Master: wal_level = hot_standby checkpoint_segments = 32 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f /dev/null' max_wal_senders = 2 wal_keep_segments = 32 Slave: wal_level = hot_standby checkpoint_segments = 32 #checkpoint_completion_target = 0.5 hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 #wal_receiver_status_interval = 10s #hot_standby_feedback = off Thank you for any help you can provide! Andrew -- ~ L. Friedmannetll...@gmail.com LlamaLand https://netllama.linux-sxs.org -- 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] Streaming Replication Randomly Locking Up
Yep, that's the first thing I'm going to do. On Thu, Aug 15, 2013 at 12:34 PM, Lonni J Friedman netll...@gmail.comwrote: I'd suggest enhancing your logging to include time/datestamps for every entry, and also the client hostname. That will help to rule in/out those 'unexpected EOF' errors. On Thu, Aug 15, 2013 at 12:22 PM, Andrew Berman rexx...@gmail.com wrote: The only thing I see that is a possibility for the issue is in the slave log: LOG: unexpected EOF on client connection LOG: could not receive data from client: Connection reset by peer I don't know if that's related or not as it could just be somebody running a query. The log file does seem to be riddled with these but the replication failures don't happen constantly. As far as I know I'm not swallowing any errors. The logging is all set as the default: log_destination = 'stderr' logging_collector = on #client_min_messages = notice #log_min_messages = warning #log_min_error_statement = error #log_min_duration_statement = -1 #log_checkpoints = off #log_connections = off #log_disconnections = off #log_error_verbosity = default I'm going to have a look at the NICs to make sure there's no issue there. Thanks again for your help! On Thu, Aug 15, 2013 at 11:51 AM, Lonni J Friedman netll...@gmail.com wrote: Are you certain that there are no relevant errors in the database logs (on both master slave)? Also, are you sure that you didn't misconfigure logging such that errors wouldn't appear? On Thu, Aug 15, 2013 at 11:45 AM, Andrew Berman rexx...@gmail.com wrote: Hi Lonni, Yes, I am using PG 9.1.9. Yes, 1 slave syncing from the master CentOS 6.4 I don't see any network or hardware issues (e.g. NIC) but will look more into this. They are communicating on a private network and switch. I forgot to mention that after I restart the slave, everything syncs right back up and all if working again so if it is a network issue, the replication is just stopping after some hiccup instead of retrying and resuming when things are back up. Thanks! On Thu, Aug 15, 2013 at 11:32 AM, Lonni J Friedman netll...@gmail.com wrote: I've never seen this happen. Looks like you might be using 9.1? Are you up to date on all the 9.1.x releases? Do you have just 1 slave syncing from the master? Which OS are you using? Did you verify that there aren't any network problems between the slave master? Or hardware problems (like the NIC dying, or dropping packets)? On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 The replication works great for days, but randomly seems to lock up and replication halts. I verified that the two databases were out of sync with a query on both of them. Has anyone experienced this issue before? Here are some relevant config settings: Master: wal_level = hot_standby checkpoint_segments = 32 checkpoint_completion_target = 0.9 archive_mode = on archive_command = 'rsync -a %p foo@foo:/var/lib/pgsql/9.1/wals/%f /dev/null' max_wal_senders = 2 wal_keep_segments = 32 Slave: wal_level = hot_standby checkpoint_segments = 32 #checkpoint_completion_target = 0.5 hot_standby = on max_standby_archive_delay = -1 max_standby_streaming_delay = -1 #wal_receiver_status_interval = 10s #hot_standby_feedback = off Thank you for any help you can provide! Andrew -- ~ L. Friedmannetll...@gmail.com LlamaLand https://netllama.linux-sxs.org
[GENERAL] Escape string for LIKE op
How can I escape a string for LIKE operations? I want to do: SELECT * FROM t WHERE a LIKE b || '%' But I want be to interpreted literally. If b is 'The 7% Solution', I don't want that '%' to be wildcard. I can't find an appropriate function to escape it and any other potential wildcards for LIKE clauses. -- 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] Immediate Constraints
From a logical standpoint, its like this. The purpose of constraints is to have the DBMS enforce your concept of consistency, wherein a database is consistent if any questions you ask it result in a valid answer insofar as the database could possibly know. Immediate constraints ensure that the database is consistent between statement boundaries, while deferred constraints only ensure that the database is consistent between transaction boundaries. Logically speaking, the purpose of immediate constraints is to ensure that the database can't give you possibly wrong/invalid/illogical answers to a query you make following a database change but before you commit. Ideally, from a logical standpoint, all constraints would be immediate, but a primary reason we have deferred constraints at all is to compensate for deficiencies in the SQL language such that we can't perform arbitrarily complex database changes in a single statement, such as inserting a record into each of 2 separate tables as a single operation, and so we may defer any constraint that requires both records to be present. Bottom line, the more of your constraints are immediate, the more the database helps you avoid program bugs or corruption due to decisions made based on incomplete or wrong database changes you make. -- Darren Duncan On 2013.08.15 9:14 AM, Perry Smith wrote: The direct question is: what is the advantage of an immediate constraint? My habit is to add constraints to my databases and my first lesson was to make them deferrable. But a recent fight with pg_restore taught me that to do a pg_restore that is complex, you need to defer the constraints. I cobbled a way to do that as I do the pg_restore. But that raised a question of why not just make the constraints all deferred and simplify my pg_restore process. Are immediate constraints more efficient? Does this relate to transaction isolation in that the data would be consistent after each statement and therefor give better stability when multiple transactions are running at the same time? My brain is asking this question because so far in my experience, the issues with constraints are solved by making them deferred. If I made them immediate, would I just bump into a different set of issues whose solution would be to make the constraints immediate? Thank you, Perry -- 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] Streaming Replication Randomly Locking Up
On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting There is a recovery conflict which it is waiting to go away. In other words, you have a long-running (or long-idle) transaction on the slave which is blocking recovery. max_standby_archive_delay = -1 max_standby_streaming_delay = -1 ...and you are willing to wait forever. Cheers, Jeff -- 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] Streaming Replication Randomly Locking Up
Hi Jeff, Here is the full process list at the time it stopped working (I have changed the actual username, db and IP for security). Would the idle in transaction process be the culprit? postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting postgres 5642 0.0 21.4 3428356 2613252 ? Ss Aug14 0:30 postgres: writer process postgres 5659 0.0 0.0 177524 788 ?Ss Aug14 0:03 postgres: stats collector process postgres 7159 1.2 0.1 3451360 18352 ? Ss Aug14 17:31 postgres: wal receiver process streaming 549/216B3730 postgres 10403 0.0 0.2 3430372 25920 ? Ss Aug14 0:31 postgres: user db x.x.x.x(61656) idle in transaction postgres 19933 0.0 0.4 3426604 49564 ? SAug05 0:06 /usr/pgsql-9.1/bin/postmaster -p 5432 -D /var/lib/pgsql/9.1/data postgres 19935 0.0 0.0 175288 396 ?Ss Aug05 0:13 postgres: logger process postgres 21133 0.0 0.2 3443600 30680 ? Ss 09:28 0:00 postgres: user db x.x.x.x(64430) idle postgres 21134 0.4 0.2 3430160 27656 ? Ss 09:28 0:16 postgres: user db x.x.x.x(64431) idle root 21529 0.0 0.0 103240 844 pts/0S+ 10:33 0:00 grep --color postgres ** Thanks, Andrew On Thu, Aug 15, 2013 at 1:20 PM, Jeff Janes jeff.ja...@gmail.com wrote: On Thu, Aug 15, 2013 at 11:07 AM, Andrew Berman rexx...@gmail.com wrote: Hello, I'm having an issue where streaming replication just randomly stops working. I haven't been able to find anything in the logs which point to an issue, but the Postgres process shows a waiting status on the slave: postgres 5639 0.1 24.3 3428264 2970236 ? Ss Aug14 1:54 postgres: startup process recovering 0001053D003F waiting There is a recovery conflict which it is waiting to go away. In other words, you have a long-running (or long-idle) transaction on the slave which is blocking recovery. max_standby_archive_delay = -1 max_standby_streaming_delay = -1 ...and you are willing to wait forever. Cheers, Jeff
Re: [GENERAL] Escape string for LIKE op
On Thu, Aug 15, 2013 at 1:16 PM, Robert James srobertja...@gmail.com wrote:
How can I escape a string for LIKE operations?
I want to do:
SELECT * FROM t WHERE a LIKE b || '%'
But I want be to interpreted literally. If b is 'The 7% Solution', I
don't want that '%' to be wildcard. I can't find an appropriate
function to escape it and any other potential wildcards for LIKE
clauses.
You could use the replace function.
select 'The 7% Solution is a good book' like replace('The 7%
Solution', '%', '\%')||'%';
true
select 'The 7pt Solution is a good book' like replace('The 7%
Solution', '%', '\%')||'%';
false
If you need to worry about underscores as well, you could chain two
replace functions together.
Cheers,
Jeff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] devide and summarize sql result
Hi, My sql query results sth. like this: user percentage franz 78% smith 98% franz 81% jason -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] devide and summarize sql result (all)
Hi, My sql query results sth. like this: user percentage franz 78% smith 98% franz 81% jason 79% smith 89% smith 85% smith 99% Now Id like to summarize the percentages oder every user like this. smith 2 matches 95-100% 2 matches 85-95% 0 mathes 75-85% franz 0 mathes 95-100% ... Hope there issomeone whocan help me Janek Sendrowksi
Re: [GENERAL] Strange result with SELECT ... ORDER BY random() LIMIT 1 and JOINs
On 08/15/2013 11:46 AM, Etienne Dube wrote: Hello, I don't really understand what is going on there, the result appears incorrect to me. The following page seems to offer some explanations as to what is happening: http://sql-info.de/postgresql/postgres-gotchas.html#1_6 . Indeed, adding OFFSET 0 to the s subquery solves the issue. Can somebody shed some light on this topic? Is this behaviour correct or should it be considered a bug? See here for explanation: http://www.postgresql.org/message-id/8569.1128439...@sss.pgh.pa.us Thanks! Etienne -- 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] Escape string for LIKE op
On 08/15/2013 10:16 PM, Robert James wrote: How can I escape a string for LIKE operations? I want to do: SELECT * FROM t WHERE a LIKE b || '%' But I want be to interpreted literally. If b is 'The 7% Solution', I don't want that '%' to be wildcard. I can't find an appropriate function to escape it and any other potential wildcards for LIKE clauses. In this particular case, you're better off changing the query to be SELECT * FROM t WHERE a = b; but if your needs are more complex than your actual question, you'll most likely need to process b like Jeff explained. -- Vik -- 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] devide and summarize sql result (all)
On Thu, Aug 15, 2013 at 1:51 PM, Janek Sendrowski jane...@web.de wrote: Hi, My sql query results sth. like this: user percentage franz 78% smith 98% franz 81% jason 79% smith 89% smith 85% smith 99% Now I'd like to summarize the percentages oder every user like this. smith 2 matches 95-100% 2 matches 85-95% 0 mathes 75-85% franz 0 mathes 95-100% ... A CASE statement should work, if you are willing to hard-code the list of expressions. SELECT username, sum(case when avg between 76 and 85 then 1 else 0 end) as 76 to 85, sum(case when avg between 86 and 95 then 1 else 0 end) as 86 to 95, sum(case when avg 95 then 1 else 0 end) as 95 FROM yourtable GROUP BY username
Re: [GENERAL] Escape string for LIKE op
On 8/15/13, Jeff Janes jeff.ja...@gmail.com wrote:
On Thu, Aug 15, 2013 at 1:16 PM, Robert James srobertja...@gmail.com
wrote:
How can I escape a string for LIKE operations?
I want to do:
SELECT * FROM t WHERE a LIKE b || '%'
But I want be to interpreted literally. If b is 'The 7% Solution', I
don't want that '%' to be wildcard. I can't find an appropriate
function to escape it and any other potential wildcards for LIKE
clauses.
You could use the replace function.
select 'The 7% Solution is a good book' like replace('The 7%
Solution', '%', '\%')||'%';
true
select 'The 7pt Solution is a good book' like replace('The 7%
Solution', '%', '\%')||'%';
false
If you need to worry about underscores as well, you could chain two
replace functions together.
This is my concern - it's never a good idea to try to find all escape
chars by hand. You end up missing one. Think about complicated cases
where the escape char is escaped itself - my experience is that a
regex to escape a string never works in every case. You need to use a
real parser - which of course Postgres already has, that's how it
interprets the string in the first place.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Forcing materialize in the planner
I have a query which, when I materialize by hand some of its components, runs 10x faster (including the time needed to materialize). Is there any way to force Postgres to do that? Or do I need to do this by hand using temp tables? -- 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] devide and summarize sql result (all)
Hi Janek, You can try: =# SELECT name, perc/5*5 || '-' || perc/5*5+5 AS range, count(*) as matches FROM test GROUP BY name, perc/5 ORDER BY perc/5; name | range | matches ---++- franz | 75-80 | 1 jason | 75-80 | 1 franz | 80-85 | 1 smith | 85-90 | 2 smith | 95-100 | 2 (5 rows) -- Beena Emerson
Re: [GENERAL] Commit problem in read-commited isolation level
Any triggers on the table? There are no trigger associated with this table. FYI 8.1 is no longer supported. I understand that. If there are some known related issues, it will be easy to convince, Product mgmt team to upgrade the version of postgresql. Are there known issues related to commit problem in 8.1 version. Date: Wed, 7 Aug 2013 17:05:59 -0700 From: adrian.kla...@gmail.com To: msq...@live.com CC: pgsql-general@postgresql.org Subject: Re: [GENERAL] Commit problem in read-commited isolation level On 08/07/2013 04:54 PM, S H wrote: Hi, I have faced very strange problem in one of psotgresql query in one of the production environment. It is working fine in development and other environment. Current value in colname = 5; Update tablename set colname = 0 where key = 18; commit , in parallel to above queries ( either vacuum or reindex of table was running) After 10 sec following query is executed. select colname from tablename where key = 18 ; it is returning old value i.e colname = 5. After another few seconds select colname from tablename where key = 18 ; it is returning new value i.e colname = 5. I thought the new value is 0? Isolevel level is readcommited. Is there any possibility of bug in commit in V8.1 leading to delay of commit ? Any triggers on the table? FYI 8.1 is no longer supported. I need to provide explanation of above behavior to my customer. Regards, -- 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
