Re: [GENERAL] Partioning with overlapping and non overlapping constraints
Tim Uckun wrote > 1. Should I be worried about having possibly hundreds of thousands of > shards. IIRC, yes. > 2. Is PG smart enough to handle overlapping constraints on table and limit > it's querying to only those tables that have the correct time constraint. Probably yes, but seems easy enough to verify. All constraints are checked for each partiton and if any return false the entire partiton will be excluded; which means multiple partitions can be included. Note, this is large reason why #1 poses a problem. David J. -- View this message in context: http://postgresql.nabble.com/Partioning-with-overlapping-and-non-overlapping-constraints-tp5836869p5836871.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Partioning with overlapping and non overlapping constraints
I have two partitioning questions I am hoping somebody can help me with. I have a fairly busy metric(ish) table. It gets a few million records per day, the data is transactional for a while but then settles down and is used for analytical purposes later. When a metric is reported both the UTC time and the local times are stored along with the other data belonging to the metric. I want to partition this table to both make it faster to query and also to spread out the writes. Ideally the partitions would be based on the UTC timestamp and the sending location. For example metrics_location_X_2015_01_01 First problem with this approach is that there could be tens of thousands of locations so this is going to result hundreds of thousands of tables. I know there are no upper limits to how many tables there are but I am thinking this might really get me into trouble later. Second and possibly more vexing problem is that often the local time is queried. Ideally I would like to put three constraints on the child tables. Location id, UTC timestamp and the local time but obviously the local timestamps would overlap with other locations in the same timezone Even if I was to only partition by UTC the local timestamps would overlap between tables. So the questions in a nutshell are. 1. Should I be worried about having possibly hundreds of thousands of shards. 2. Is PG smart enough to handle overlapping constraints on table and limit it's querying to only those tables that have the correct time constraint. Thanks.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On 02/05/2015 03:41 PM, Tim Smith wrote: So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult to predict. Seriously ? You still want to continue calling it user-error ? There is no other view, there is no other schema , I am not hiding anything from you ! No, what we are trying to do is work the solution, not the problem. The problem being you are getting a error, the solution being tracking down where the error is coming from. If you start a problem report with little or no information, you have to expect people are going to have to ask a spectrum of questions to get at the information necessary to solve the problem. -- Adrian Klaver adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
On 02/05/2015 03:25 PM, Tim Smith wrote: PostgreSQL doesn't lie Well if its not lying its one big stinking bug ! In my experience Postgres does not randomly make up error messages. Somewhere it is seeing a duplicate column. How about you tell me where you see these duplicate columns in my view that PostgreSQL is apparently not lying to me about So then this is not the problem, which moves the troubleshooting to the function. Have you tried the previous suggestions on modifying the function? -- Adrian Klaver adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
Tim Smith writes: > Yes, well PostgreSQL is being incredibly unhelpful in that respect, it > says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is > ambiguous)" ... but that is an utter lie. There is only one column > called session_id in my view (in both the view output and the > underlying view query, there is only one reference to "session_id") A reasonably likely bet is that the ambiguity is between a column name exposed by the query and some variable of the same name declared within the plpgsql function. But, as has been mentioned repeatedly, you've not shown us enough detail to permit a positive diagnosis. 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] Using row_to_json with %ROWTYPE ?
app_sessions is a table and app_users_vw is not hiding anything from you : tenant_id tenant_name tenant_shortname reseller_id user_id user_failedlogins user_fname user_lname user_email user_phone user_passwd user_seed user_hidden user_candelete user_newseed user_lastupdate tenant_lastupdate On 5 February 2015 at 23:38, David Johnston wrote: > On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith > wrote: >> >> You're most welcome to look at my view definition view if you don't >> believe me >> >> View definition: >> SELECT a.session_id, >> a.session_ip, >> a.session_user_agent, >> a.session_start, >> a.session_lastactive, >> b.user_id, >> b.tenant_id, >> b.reseller_id, >> b.tenant_name, >> b.user_fname, >> b.user_lname, >> b.user_email, >> b.user_phone, >> b.user_seed, >> b.user_passwd, >> b.user_lastupdate, >> b.tenant_lastupdate >>FROM app_sessions a, >> app_users_vw b >> WHERE a.user_id = b.user_id; > > > So that view and definition are correct. > > So either PostgreSQL is seeing a different view (in a different schema) or > the function is confused in ways difficult to predict. > > I guess it is possible that: > > (SELECT v_row FROM v_row) would give that message but I get a "relation > v_row does not exist" error when trying to replicate the scenario. > > It may even be a bug but since you have not provided a self-contained test > case, nor the version of PostgreSQL, the assumption is user error. > > David J. > -- 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] Using row_to_json with %ROWTYPE ?
> So either PostgreSQL is seeing a different view (in a different schema) or > the function is confused in ways difficult to predict. Seriously ? You still want to continue calling it user-error ? There is no other view, there is no other schema , I am not hiding anything from you ! On 5 February 2015 at 23:38, David Johnston wrote: > On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith > wrote: >> >> You're most welcome to look at my view definition view if you don't >> believe me >> >> View definition: >> SELECT a.session_id, >> a.session_ip, >> a.session_user_agent, >> a.session_start, >> a.session_lastactive, >> b.user_id, >> b.tenant_id, >> b.reseller_id, >> b.tenant_name, >> b.user_fname, >> b.user_lname, >> b.user_email, >> b.user_phone, >> b.user_seed, >> b.user_passwd, >> b.user_lastupdate, >> b.tenant_lastupdate >>FROM app_sessions a, >> app_users_vw b >> WHERE a.user_id = b.user_id; > > > So that view and definition are correct. > > So either PostgreSQL is seeing a different view (in a different schema) or > the function is confused in ways difficult to predict. > > I guess it is possible that: > > (SELECT v_row FROM v_row) would give that message but I get a "relation > v_row does not exist" error when trying to replicate the scenario. > > It may even be a bug but since you have not provided a self-contained test > case, nor the version of PostgreSQL, the assumption is user error. > > David J. > -- 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] Using row_to_json with %ROWTYPE ?
On Thu, Feb 5, 2015 at 4:29 PM, Tim Smith wrote: > You're most welcome to look at my view definition view if you don't > believe me > > View definition: > SELECT a.session_id, > a.session_ip, > a.session_user_agent, > a.session_start, > a.session_lastactive, > b.user_id, > b.tenant_id, > b.reseller_id, > b.tenant_name, > b.user_fname, > b.user_lname, > b.user_email, > b.user_phone, > b.user_seed, > b.user_passwd, > b.user_lastupdate, > b.tenant_lastupdate >FROM app_sessions a, > app_users_vw b > WHERE a.user_id = b.user_id; > So that view and definition are correct. So either PostgreSQL is seeing a different view (in a different schema) or the function is confused in ways difficult to predict. I guess it is possible that: (SELECT v_row FROM v_row) would give that message but I get a "relation v_row does not exist" error when trying to replicate the scenario. It may even be a bug but since you have not provided a self-contained test case, nor the version of PostgreSQL, the assumption is user error. David J.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
You're most welcome to look at my view definition view if you don't believe me View definition: SELECT a.session_id, a.session_ip, a.session_user_agent, a.session_start, a.session_lastactive, b.user_id, b.tenant_id, b.reseller_id, b.tenant_name, b.user_fname, b.user_lname, b.user_email, b.user_phone, b.user_seed, b.user_passwd, b.user_lastupdate, b.tenant_lastupdate FROM app_sessions a, app_users_vw b WHERE a.user_id = b.user_id; -- 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] Using row_to_json with %ROWTYPE ?
> PostgreSQL doesn't lie Well if its not lying its one big stinking bug ! How about you tell me where you see these duplicate columns in my view that PostgreSQL is apparently not lying to me about View "public.app_val_session_vw" Column | Type | Modifiers ---+---+--- session_id | bigint | session_ip | inet | session_user_agent | character(40) | session_start | bigint| session_lastactive | bigint| user_id| bigint | tenant_id | bigint | reseller_id| bigint | tenant_name| text | user_fname | text | user_lname | text | user_email | text | user_phone | bigint| user_seed | character(16) | user_passwd| character(60) | user_lastupdate| bigint| tenant_lastupdate | bigint| On 5 February 2015 at 23:19, David Johnston wrote: > On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith > wrote: >> >> > returning more than one row? v_row can only hold one row at a time. >> >> Absolutley not. (a) My where clause is a primary key (b) I have >> checked it manually, it only returns one row >> >> >You really need to provide error messages >> >> Yes, well PostgreSQL is being incredibly unhelpful in that respect, it >> says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is >> ambiguous)" ... but that is an utter lie. There is only one column >> called session_id in my view (in both the view output and the >> underlying view query, there is only one reference to "session_id") >> > PostgreSQL doesn't lie - it just doesn't always give all of the information > you need > to understand what it is seeing. > > You have a view definition problem since nowhere in the code you provide > should > session_id be resolved. > > A simple: > > SELECT * FROM my_view; > > would prove out that theory. > > If that works then most probably the my_view view that the function sees is > different > than the one that you think it is seeing. > >> >> On 5 February 2015 at 21:57, Adrian Klaver >> wrote: >> > On 02/05/2015 01:38 PM, Tim Smith wrote: >> >> >> >> Hi, >> >> >> >> I have a function that broadly looks like this : >> >> >> >> create function doStuff() returns json as $$ >> >> DECLARE >> >> v_row my_view%ROWTYPE; >> >> BEGIN >> >> select * into strict v_row from my_view where foo=bar; >> >> select row_to_json(v_row) from v_row; >> > > A third problem you will hit, when you fix the syntax, is that the > SELECT row_to_json(...) command has no target and thus needs > to use PERFORM, not SELECT. > David J. > -- 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] Using row_to_json with %ROWTYPE ?
On Thu, Feb 5, 2015 at 4:01 PM, Tim Smith wrote: > > returning more than one row? v_row can only hold one row at a time. > > Absolutley not. (a) My where clause is a primary key (b) I have > checked it manually, it only returns one row > > >You really need to provide error messages > > Yes, well PostgreSQL is being incredibly unhelpful in that respect, it > says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is > ambiguous)" ... but that is an utter lie. There is only one column > called session_id in my view (in both the view output and the > underlying view query, there is only one reference to "session_id") > > PostgreSQL doesn't lie - it just doesn't always give all of the information you need to understand what it is seeing. You have a view definition problem since nowhere in the code you provide should session_id be resolved. A simple: SELECT * FROM my_view; would prove out that theory. If that works then most probably the my_view view that the function sees is different than the one that you think it is seeing. > On 5 February 2015 at 21:57, Adrian Klaver > wrote: > > On 02/05/2015 01:38 PM, Tim Smith wrote: > >> > >> Hi, > >> > >> I have a function that broadly looks like this : > >> > >> create function doStuff() returns json as $$ > >> DECLARE > >> v_row my_view%ROWTYPE; > >> BEGIN > >> select * into strict v_row from my_view where foo=bar; > >> select row_to_json(v_row) from v_row; > > A third problem you will hit, when you fix the syntax, is that the SELECT row_to_json(...) command has no target and thus needs to use PERFORM, not SELECT. David J.
Re: [GENERAL] Using row_to_json with %ROWTYPE ?
On 02/05/2015 03:01 PM, Tim Smith wrote: > returning more than one row? v_row can only hold one row at a time. Absolutley not. (a) My where clause is a primary key (b) I have checked it manually, it only returns one row Well since there was no error message provided and my psychic hat is in the shop I had to start somewhere. You really need to provide error messages Yes, well PostgreSQL is being incredibly unhelpful in that respect, it says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is ambiguous)" ... but that is an utter lie. There is only one column called session_id in my view (in both the view output and the underlying view query, there is only one reference to "session_id") Actually I would say this is a pretty big clue that: select row_to_json(v_row) from v_row; is causing a problem. Try commenting it out and see what happens? On 5 February 2015 at 21:57, Adrian Klaver wrote: On 02/05/2015 01:38 PM, Tim Smith wrote: Hi, I have a function that broadly looks like this : create function doStuff() returns json as $$ DECLARE v_row my_view%ROWTYPE; BEGIN select * into strict v_row from my_view where foo=bar; select row_to_json(v_row) from v_row; END; $$ LANGUAGE plpgsql; However this does not seem to work ? What am I doing wrong ? Well for starters is: select * into strict v_row from my_view where foo=bar; returning more than one row? v_row can only hold one row at a time. Given that then: select row_to_json(v_row) from v_row; should be: select row_to_json(v_row); I would suggest taking a look at: http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING Also error messages would be helpful:) Thanks Tim -- Adrian Klaver adrian.kla...@aklaver.com -- Adrian Klaver adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
> returning more than one row? v_row can only hold one row at a time. Absolutley not. (a) My where clause is a primary key (b) I have checked it manually, it only returns one row >You really need to provide error messages Yes, well PostgreSQL is being incredibly unhelpful in that respect, it says "(SQLSTATE: 42702 - SQLERRM: column reference "session_id" is ambiguous)" ... but that is an utter lie. There is only one column called session_id in my view (in both the view output and the underlying view query, there is only one reference to "session_id") On 5 February 2015 at 21:57, Adrian Klaver wrote: > On 02/05/2015 01:38 PM, Tim Smith wrote: >> >> Hi, >> >> I have a function that broadly looks like this : >> >> create function doStuff() returns json as $$ >> DECLARE >> v_row my_view%ROWTYPE; >> BEGIN >> select * into strict v_row from my_view where foo=bar; >> select row_to_json(v_row) from v_row; >> END; >> $$ LANGUAGE plpgsql; >> >> >> However this does not seem to work ? What am I doing wrong ? > > > Well for starters is: > > select * into strict v_row from my_view where foo=bar; > > returning more than one row? v_row can only hold one row at a time. > > Given that then: > > select row_to_json(v_row) from v_row; > > should be: > > select row_to_json(v_row); > > I would suggest taking a look at: > > http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING > > > Also error messages would be helpful:) > > >> >> Thanks >> >> Tim >> >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.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] Using row_to_json with %ROWTYPE ?
Tim Smith-2 wrote > Hi, > > I have a function that broadly looks like this : > > create function doStuff() returns json as $$ > DECLARE > v_row my_view%ROWTYPE; > BEGIN > select * into strict v_row from my_view where foo=bar; > select row_to_json(v_row) from v_row; > END; > $$ LANGUAGE plpgsql; > > However this does not seem to work ? What am I doing wrong ? I suspect that the main issue you are encountering is that "FROM v_row" is complaining that v_row is not a known relation. You really need to provide error messages or your observations in situations like this. A blanket "does not seem to work" is not enough when asking others to identify what you are doing wrong. A self-contained example is even better. If the above is true then this has nothing with row_to_json other than that is the function you choose to try and use. A simple "SELECT * FROM v_row" would get you the same error. David J. -- View this message in context: http://postgresql.nabble.com/Using-row-to-json-with-ROWTYPE-tp5836841p5836848.html Sent from the PostgreSQL - general mailing list archive at Nabble.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] Using row_to_json with %ROWTYPE ?
On 02/05/2015 01:38 PM, Tim Smith wrote: Hi, I have a function that broadly looks like this : create function doStuff() returns json as $$ DECLARE v_row my_view%ROWTYPE; BEGIN select * into strict v_row from my_view where foo=bar; select row_to_json(v_row) from v_row; END; $$ LANGUAGE plpgsql; However this does not seem to work ? What am I doing wrong ? Well for starters is: select * into strict v_row from my_view where foo=bar; returning more than one row? v_row can only hold one row at a time. Given that then: select row_to_json(v_row) from v_row; should be: select row_to_json(v_row); I would suggest taking a look at: http://www.postgresql.org/docs/9.3/interactive/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING Also error messages would be helpful:) Thanks Tim -- Adrian Klaver adrian.kla...@aklaver.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Using row_to_json with %ROWTYPE ?
Hi, I have a function that broadly looks like this : create function doStuff() returns json as $$ DECLARE v_row my_view%ROWTYPE; BEGIN select * into strict v_row from my_view where foo=bar; select row_to_json(v_row) from v_row; END; $$ LANGUAGE plpgsql; However this does not seem to work ? What am I doing wrong ? Thanks Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How do I bump a row to the front of sort efficiently
Hey, I'm not a guru, here is what I understood. You are mixing several problems in the same question : - 1. why the planner isn't more efficient - 2. why the workaround is difficult to use with an ORM. for 1. you can't do much (as said by others, you don't really need a case here anyway). I think using a CASE is equivalent for the planner to using your own custom blackbox function. So no way to improve that. for 2. : if you can't pass limit and offset in your ORM, a small workaround is to number your row following the order you defined with the function row_number() over(your order here), then you can use your ORM to design where conditions equivalent to limit and offset : WHERE row_number BETWEEN your_offset AND your_limit Cheers, Rémi-C 2015-02-04 21:40 GMT+01:00 Paul Jungwirth : > >> I imagine your original would be at risk of LIMITing out the very row > you > >> seek to get at the "top", since you don't have an ORDER BY to tell it > which > >> ones to keep during the outer LIMIT. > > Here is an old thread about combining ORDER BY with UNION: > > http://www.postgresql.org/message-id/16814.1280268...@sss.pgh.pa.us > > So I think this query would work: > > select * from topic > where id = 1000 > union all > (select * from topic > where id <> 1000 > order by bumped_at desc > limit 29) > order by case when id = 1000 then 0 else 1 end, bumped_at desc > ; > > > I need to be able to offset and limit the union hack in a view, which > > is proving very tricky. > > Since this is sort of a "parameterized view" (which Postgres does not > have) you are probably better off figuring out how to make the UNION > query work with your ORM. What ORM is it? Maybe someone here can help > you with that. Or maybe instead of a view you could write a > set-returning function, e.g. as described here: > > > http://stackoverflow.com/questions/11401749/pass-in-where-parameters-to-postgresql-view > > Paul > > -- > _ > Pulchritudo splendor veritatis. > > > -- > 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] DB encoding, locale and indexes
2015-02-05 15:56 GMT+01:00 Tom Lane : > Sterfield writes: > > I'm a sysadmin working for an application that stores all its data in a > PG > > database. > > Currently, the cluster has its encoding set to UTF-8, and the locale > (both > > LC_COLLATE and LC_CTYPE) is set to 'en_US.UTF-8'. > > > I discovered recently that the indexes created on varchar fields are not > > working for LIKE operator, as they are created without the correct class > > operator (as stated in > > http://www.postgresql.org/docs/9.2/static/indexes-types.html). > > Right, because en_US.UTF-8 uses dictionary sort order rather than plain > byte-by-byte sort. > > > The most straight-forward solution would be to create a second index on > the > > same field but with the class operator, in order to have indexes both for > > =, >, < operators AND LIKE / regexp operators. Few additional indexes, > some > > diskspace eaten, problem solved. > > Yup. > > > However, some people are saying that nothing has to change on the index, > > and that the only thing we have to do is to change the LC_COLLATE of each > > databases to 'C', in order for the indexes to work without the class > > operator. > > Yes, that is another possible solution, and it's documented. Keep in mind > though that you can *not* just reach into pg_database and tweak those > fields; if you did, all your indexes would be corrupt, because they'd no > longer match the sort order the system is expecting. The only safe way to > get there would be to dump and reload into a new database set up this way. > (If you wanted to live dangerously, I guess you could manually tweak the > pg_database fields and then REINDEX every affected index ... but this > still involves substantial downtime, and I would not recommend doing it > without practicing on a test installation.) > Yeah, I'll not take the risk. For current databases, I'll probably create manually new indexes. For new environment, I'll change the LC_COLLATE to 'C'. I've spent some time re-creating a test environment, using encoding to UTF8, locale to 'en_US.UTF-8' except LC_COLLATE set to 'C'. Nothing special to report, the index is working as expected for LIKE operators, and I have correct answers if I'm doing a LIKE 'é%'. > You also have to ask whether any of your applications are expecting ORDER > BY some-text-field to produce dictionary order rather than ASCII order. > Indeed, the order of the results is not the same with a LC_COLLATE to 'en_US.UTF-8' or LC_COLLATE to 'C', but I highly doubt that the application is taking advantage of having an index already sorted. > >- If I have unicode character stored in my database (for example 'é'), > >and the LC_COLLATE set to 'C', how the index will behave if I do a > query > >with LIKE 'é%' ? > > It's still the same character, but it will sort in a possibly unexpected > way. > > regards, tom lane > Many thanks for your help, guys, especially on this non-trivial subject (at least, for me). Cheers,
Re: [GENERAL] DB encoding, locale and indexes
Sterfield writes: > I'm a sysadmin working for an application that stores all its data in a PG > database. > Currently, the cluster has its encoding set to UTF-8, and the locale (both > LC_COLLATE and LC_CTYPE) is set to 'en_US.UTF-8'. > I discovered recently that the indexes created on varchar fields are not > working for LIKE operator, as they are created without the correct class > operator (as stated in > http://www.postgresql.org/docs/9.2/static/indexes-types.html). Right, because en_US.UTF-8 uses dictionary sort order rather than plain byte-by-byte sort. > The most straight-forward solution would be to create a second index on the > same field but with the class operator, in order to have indexes both for > =, >, < operators AND LIKE / regexp operators. Few additional indexes, some > diskspace eaten, problem solved. Yup. > However, some people are saying that nothing has to change on the index, > and that the only thing we have to do is to change the LC_COLLATE of each > databases to 'C', in order for the indexes to work without the class > operator. Yes, that is another possible solution, and it's documented. Keep in mind though that you can *not* just reach into pg_database and tweak those fields; if you did, all your indexes would be corrupt, because they'd no longer match the sort order the system is expecting. The only safe way to get there would be to dump and reload into a new database set up this way. (If you wanted to live dangerously, I guess you could manually tweak the pg_database fields and then REINDEX every affected index ... but this still involves substantial downtime, and I would not recommend doing it without practicing on a test installation.) You also have to ask whether any of your applications are expecting ORDER BY some-text-field to produce dictionary order rather than ASCII order. >- If I have unicode character stored in my database (for example 'é'), >and the LC_COLLATE set to 'C', how the index will behave if I do a query >with LIKE 'é%' ? It's still the same character, but it will sort in a possibly unexpected way. 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] Error: could not read symbolic link "pg_tblspc/940585". No such file or directory
From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Guillaume Drolet Sent: Thursday, February 05, 2015 8:29 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Error: could not read symbolic link "pg_tblspc/940585". No such file or directory Dear list users, I moved a database from a tablespace I had created to pg_default using: ALTER DATABASE mydatabase SET TABLESPACE pg_default; After the database was fully copied to a new directory under PGDATA/base, I deleted the symbolic link to the old tablespace using (in Windows) rmdir PGDATA/pg_tblspc/940585. I chose to delete this now useless symlink because of an error when running pg_basebackup (something about a non-empty directory under the symlink). Now, when I start pgAdmin and connect to my cluster, I get the following pop-up message (free translation from French): An error has occurred: ERROR: could not read symbolic link "pg_tblspc/940585": No such file or directory. If I click OK on the pop-up, the connection is made and everything looks good (i.e. I can access my databases, tables, etc.). My question is: where do I have to look to delete a reference to this obsolete symbolic link/tablespace in pgAdmin or PGSQL? Thanks a lot for your help, Guillaume You probably, still have your tablespace that you created, which now using non-existing symbolic link. Before deleting windows symbolic link, you should have dropped tablespace you created. Try dropping it now. Regards, Igor Neyman
[GENERAL] Error: could not read symbolic link "pg_tblspc/940585". No such file or directory
Dear list users, I moved a database from a tablespace I had created to pg_default using: ALTER DATABASE mydatabase SET TABLESPACE pg_default; After the database was fully copied to a new directory under PGDATA/base, I deleted the symbolic link to the old tablespace using (in Windows) rmdir PGDATA/pg_tblspc/940585. I chose to delete this now useless symlink because of an error when running pg_basebackup (something about a non-empty directory under the symlink). Now, when I start pgAdmin and connect to my cluster, I get the following pop-up message (free translation from French): An error has occurred: ERROR: could not read symbolic link "pg_tblspc/940585": No such file or directory. If I click OK on the pop-up, the connection is made and everything looks good (i.e. I can access my databases, tables, etc.). My question is: where do I have to look to delete a reference to this obsolete symbolic link/tablespace in pgAdmin or PGSQL? Thanks a lot for your help, Guillaume
[GENERAL] DB encoding, locale and indexes
Hi everyone, I'm a sysadmin working for an application that stores all its data in a PG database. Currently, the cluster has its encoding set to UTF-8, and the locale (both LC_COLLATE and LC_CTYPE) is set to 'en_US.UTF-8'. I discovered recently that the indexes created on varchar fields are not working for LIKE operator, as they are created without the correct class operator (as stated in http://www.postgresql.org/docs/9.2/static/indexes-types.html). The most straight-forward solution would be to create a second index on the same field but with the class operator, in order to have indexes both for =, >, < operators AND LIKE / regexp operators. Few additional indexes, some diskspace eaten, problem solved. However, some people are saying that nothing has to change on the index, and that the only thing we have to do is to change the LC_COLLATE of each databases to 'C', in order for the indexes to work without the class operator. So, we may have a database, with UTF-8 encoding, LC_TYPE to 'en_US.UTF-8' and LC_COLLATE to 'C'. This configuration seems to be really weird to me, that's why I'm asking for your help here. Few questions : - Is it even possible ? (documentation seems to answer 'yes' to this question, according to http://www.postgresql.org/docs/current/static/multibyte.html) - If I have unicode character stored in my database (for example 'é'), and the LC_COLLATE set to 'C', how the index will behave if I do a query with LIKE 'é%' ? Many thanks for your help. Guillaume.
