Re: [GENERAL] XML validation of whitespace values

Tim Kane wrote > clone=# create temp table xml_test (document xml); > CREATE TABLE If you know you need to use xpath on this content then you should do one of the following: SELECT CASE WHEN document IS DOCUMENT THEN xpath(...) ELSE default_value_for_missing_data END; CREATE TABLE xml_test ( doc

Re: [GENERAL] XML validation of whitespace values

On 3/14/14, 11:12 AM, Tim Kane wrote: > clone=# select xml_is_well_formed(' '); > xml_is_well_formed > > t > (1 row) > > > clone=# select xpath_exists (‘//test', ' '); > ERROR: could not parse XML document > DETAIL: line 1: Start tag expected, '<' not found There are sev

Re: [GENERAL] Re: Partitioning such that key field of inherited tables no longer retains any selectivity

W dniu 11.05.2014 22:01, David G Johnston pisze: On Sun, May 11, 2014 at 2:08 PM, Tim Kane [via PostgreSQL] <[hidden email] >wrote: [--] ​This is basically what I intended to describe in "option 2"...without the benefit of ever having really read the SQL standard. So the p

Re: [GENERAL] Re: Creating a role with read only privileges but user is allowed to change password

On 12/05/14 08:33, David G Johnston wrote: ​ I​ suggest that you move the password to a separate table (my_role_password) with 2 columns: 1. my_role_id 2. password. This way you can make the my_role table totally unalterable by the user, yet they can change thei

[GENERAL] Re: Creating a role with read only privileges but user is allowed to change password

> > > ​I​ > suggest that you move the password to a separate table (my_role_password) > with 2 columns: > >1. my_role_id >2. password. > > This way you can make the my_role table totally unalterable by the user, > yet they can change their own password. > > Actually, you should NOT be stor

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

On 12/05/14 06:09, Adrian Klaver wrote: On 05/11/2014 10:17 AM, Ravi Roy wrote: Thanks a lot Tom, it worked by putting off the read only mode to off before changing the password and putting it on again. SET default_transaction_read_only = off; Worked for me.. It works but the point Tom was

[GENERAL] Re: Creating a role with read only privileges but user is allowed to change password

Adrian Klaver-4 wrote > On 05/11/2014 10:17 AM, Ravi Roy wrote: >> Thanks a lot Tom, it worked by putting off the read only mode to off >> before changing the password and putting it on again. >> >>> SET default_transaction_read_only = off; >> >> Worked for me.. > > It works but the point Tom was

[GENERAL] Re: Partitioning such that key field of inherited tables no longer retains any selectivity

On Sun, May 11, 2014 at 2:08 PM, Tim Kane [via PostgreSQL] < ml-node+s1045698n5803574...@n5.nabble.com> wrote: > > > From: Tom Lane <[hidden > email] > > > > David G Johnston <[hidden > email]

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

On 05/11/2014 10:17 AM, Ravi Roy wrote: Thanks a lot Tom, it worked by putting off the read only mode to off before changing the password and putting it on again. SET default_transaction_read_only = off; Worked for me.. It works but the point Tom was making is here: "You realize, I hope, t

Re: [GENERAL] Re: Partitioning such that key field of inherited tables no longer retains any selectivity

> > From: Tom Lane > David G Johnston writes: >> Two approaches: >> 1. Standard virtual column name that, when used, gets rewritten into a >> constant that is stored at the table level. >> 2. A way for a column's value to be defined as a function call. > > Recent versions of the SQL spec

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

Thanks a lot Tom, it worked by putting off the read only mode to off before changing the password and putting it on again. > SET default_transaction_read_only = off; Worked for me.. Many thanks to you! Regards Ravi On Sun, May 11, 2014 at 10:26 PM, Tom Lane wrote: > Ravi Roy writes: > > I'

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

Thanks Melvin for your investigation. >Could it be you were enclosing the password in double, not single quotes >that caused the problem? In fact i'm using single quote for password. >1. Try connecting as user postgres, then DROP ROLE "MyRole"; >and recreate as above. I just tested on Windows 7 a

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

Ravi Roy writes: > I've created a role named "MyRole" in posgresql with the following : > CREATE ROLE "MyRole" NOSUPERUSER LOGIN NOCREATEDB NOCREATEROLE NOINHERIT > PASSWORD "MyPassword"; > ALTER ROLE "MyRole" set default_transaction_read_only = on; > Because I wanted this role to readonly (can

Re: [GENERAL] Re: Partitioning such that key field of inherited tables no longer retains any selectivity

David G Johnston writes: > Two approaches: > 1. Standard virtual column name that, when used, gets rewritten into a > constant that is stored at the table level. > 2. A way for a column's value to be defined as a function call. Recent versions of the SQL spec have a notion of "generated columns"

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

Thanks Melvin for your reply. Sorry I missed the command i'm using. >Finally, I see no reason why this user should not be able to change it's >own password. >What is the exact command you are using to try to change the >password? What is the >exact error? Command : ALTER ROLE MyRole WITH PASSWORD

Re: [GENERAL] Creating a role with read only privileges but user is allowed to change password

Thanks Melvin for your reply. >First, it would be helpful if you indicated the O/S and version of >PostgreSQL you are using. I'm using Postgresl 9.1 on Windows 7 (64 bit) and Debian Linux 6.x (64 bit) >Also, it is not a good idea to use CamelCase names in POstgreSQL. There is >no need for >that a

[GENERAL] Re: Creating a role with read only privileges but user is allowed to change password

I'm sorry, i forgot to mention my environemnt : Postgresql version 9.1 on Windows 7 (64 bit) and Debian Linux 6.x (64 bit) Thanks! Regards Ravi. On Sun, May 11, 2014 at 7:47 PM, Ravi Roy wrote: > Hi Guys, > > I've created a role named "MyRole" in posgresql with the following : > > CREATE R

[GENERAL] Creating a role with read only privileges but user is allowed to change password

Hi Guys, I've created a role named "MyRole" in posgresql with the following : CREATE ROLE "MyRole" NOSUPERUSER LOGIN NOCREATEDB NOCREATEROLE NOINHERIT PASSWORD "MyPassword"; ALTER ROLE "MyRole" set default_transaction_read_only = on; Because I wanted this role to readonly (can not change anythi

[GENERAL] Re: Partitioning such that key field of inherited tables no longer retains any selectivity

Tim Kane wrote > The subject line may not actually describe what I want to illustrate… > > Basically, let’s say we have a nicely partitioned data-set. Performance is > a > net win and I’m happy with it. > The partitioning scheme is equality based, rather than range based. > > That is, each partit

[GENERAL] Partitioning such that key field of inherited tables no longer retains any selectivity

The subject line may not actually describe what I want to illustrate… Basically, let’s say we have a nicely partitioned data-set. Performance is a net win and I’m happy with it. The partitioning scheme is equality based, rather than range based. That is, each partition contains a subset of the da