Re: Speed up UPDATE query?

On Thu, Oct 29, 2009 at 11:50 AM, Lee Hachadoorian
<lee.hachadoorian <at> gmail.com> wrote:
> I'm trying to update several tables (all child tables of the same
> parent), and as the number of records increases, the length of time it
> takes to run the update is shooting up exponentially. I have imported
> the new data to an import table, and then join the import table to the
> update table. The update statement looks like:
>
> UPDATE
>        household_2000 h
> SET
>        hhincome = new_hhincome
> FROM (
>        SELECT
>                serial, new_hhincome
>        FROM
>                import
>        WHERE
>                year = 2000
>        ) r
> WHERE
>        h.serial = r.serial
>
> household_2000 is a child table of a household table that, as you
> might guess, only contains records from the year 2000. I am putting a
> year = 2000 restriction on the import table and then linking on the
> unique identifier.
>
> For different child tables, this is how long the update takes to run
> (numbers are approximate):

PostgreSQL Security/Roles/Grants

Re: PostgreSQL Security/Roles/Grants

Andrew,

* Andrew Hall (andrewah <at> hotmail.com) wrote:
> 2. Non-default role -> a role which has to be explicitly activated during the lifecycle of an application
in order to gain access to database resources. There are no limits on the number of non-default roles. This
type of role helps us to only provide a user with the minimal set of privileges that they require at any given
time, and minimise their access to database resources.
> 
> I have looked through the PostgreSQL documentation, and cannot find anything analogous to the
'non-default role' which I have outlined above - although obviously it does support roles.
> 
> I just want to confirm that all roles in postgreSQL are activated at login time?

No.  You need to read the documentation on the 'noinherit' attribute of
roles.

See:

http://www.postgresql.org/docs/8.4/static/role-membership.html

> Secondly, is there a limit on the number of roles which can be assigned to a user (or more accurately a 'login
role') in postgreSQL?

No.

	Thanks,

		Stephen

Re: PostgreSQL Security/Roles/Grants

On 2009-11-01, Andrew Hall <andrewah <at> hotmail.com> wrote:

> 1. Default Roles -> a role which is activated at login time. Oracle imposes=
>  a limit on the number of default roles which any given user can have.
>
> 2. Non-default role -> a role which has to be explicitly activated during t=
> he lifecycle of an application in order to gain access to database resource=
> s. There are no limits on the number of non-default roles. This type of rol=
> e helps us to only provide a user with the minimal set of privileges that t=
> hey require at any given time=2C and minimise their access to database reso=
> urces.

the only way I know of to provide anything like non-default roles is
via functions declared with "security definer"

> Secondly=2C is there a limit on the number of roles which can be assigned t=
> o a user (or more accurately a 'login role') in postgreSQL?

no (2^16 maybe??) IIRC you do hit an complexity limit, O(n^2) or worse.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Why don't I get a LATIN1 encoding here with SET ENCODING?

Re: Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote:
> I'm tracking another bug, but wanted to verify stuff on the command line.  I 
> can't figure out why this did not work:

> dblack3-deleteme=> insert into bryce1 values(1,2,'test\375');
> ERROR:  invalid byte sequence for encoding "UTF8": 0xfd

I'd say the server is interpreting your query text as latin-1 and
converting it to the server encoding UTF-8 as it should, resulting in
the utf-8 string:

  insert into bryce1 values(1,2,'test\375');

which it *then* interprets escapes in. As test\xfd ('0x74 0x65 0x73 0x74
0xfd') isn't valid UTF-8, the server rejects it.

If my understanding is right then the trouble is that the
client_encoding setting doesn't affect string escapes in SQL queries.
The conversion of the query text from client to server encoding is done
before string escapes are processed.

In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd
in a string, I don't want the server to decide that I must've meant
something else because I have a different client encoding. If I wanted
encoding conversion, I wouldn't have written it in an escape form, I'd
have written 'ý' not '\375'.

--
Craig Ringer

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Why don't I get a LATIN1 encoding here with SET ENCODING?

Craig Ringer wrote:
> In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd
> in a string, I don't want the server to decide that I must've meant
> something else because I have a different client encoding. If I wanted
> encoding conversion, I wouldn't have written it in an escape form, I'd
> have written 'ý' not '\375'.
I've got a client encoding of LATIN1... so I'd expect to be able to 
present any valid LATIN1 character, not care how the backend stored it, 
then get the same character back from the database.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote:
> 
> 
> Craig Ringer wrote:
>> In truth, that's how I'd expect it to happen. If I ask for the byte 0xfd
>> in a string, I don't want the server to decide that I must've meant
>> something else because I have a different client encoding. If I wanted
>> encoding conversion, I wouldn't have written it in an escape form, I'd
>> have written 'ý' not '\375'.

> I've got a client encoding of LATIN1... so I'd expect to be able to
> present any valid LATIN1 character, not care how the backend stored it,
> then get the same character back from the database.

Yes - but you are *not* presenting a Latin-1 character. You're
presenting four Latin-1 characters:

  '\', '3', '7', '5'

The server *cannot* process those as an escape sequence before first
converting the SQL string from client to server encoding. It doesn't
know what the bytes you sent it mean until it converts the data sent by
the client to the server encoding. Not all encodings preserve the lower
128 characters - in shift-jis, for example, the bytes usually used for
the '\' and '~' characters mean '¥' and '‾' respectively. If the server
didn't do client-to-server encoding before escape processing, a user
with a shift-jis client encoding who sent:

   test¥041

would be very surprised when the server saw that as:

   test!

instead of literally test¥041 like it should.

Perhaps when processing escapes after doing the encoding conversion the
server could apply any client->server encoding transformation on escape
sequences too. That would achieve the result you wanted here, but it
would leave you very, very, very confused and frustrated the first time
you tried to insert an image into a `bytea' field or manipulate a BLOB,
because the server would 'helpfully' translate the byte escapes for you.

To come closer to what you want, the server would have to detect whether
the escape was in a string that was going to land up in a
character-typed field instead of a byte-typed field. But what about
casts, functions, etc? And how would you specify it if you really did
want exactly those bytes in a text field? It'd be a nightmare.

The server does the only sensible, consistent thing - when you give it a
byte sequence, it assumes you mean literally those bytes.

--
Craig Ringer

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Why don't I get a LATIN1 encoding here with SET ENCODING?

Craig Ringer wrote:
> Yes - but you are *not* presenting a Latin-1 character. You're
> presenting four Latin-1 characters:
>
>   '\', '3', '7', '5'
Well, then I have a different question.  If I can view a bytea column as so:

 > select object from context_objects where context_key=100041;
           object
-----------------------------
 \005\007\021\007Article\003
(1 row)

How can I modify the data and put it back into the column?

I'm trying to track down while a ten year old system no longer works 
after a Perl DBI upgrade.  Something is munging high LATIN1.  The DB 
itself is UTF-8, client_encoding is set to LATIN1.  We expect LATIN1 in, 
same stuff out.  But right now that is what I'm debugging.

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: Why don't I get a LATIN1 encoding here with SET ENCODING?

Bryce Nesbitt wrote:
> Craig Ringer wrote:
>> Yes - but you are *not* presenting a Latin-1 character. You're
>> presenting four Latin-1 characters:
>>
>>   '\', '3', '7', '5'
> Well, then I have a different question.  If I can view a bytea column as
> so:
> 
>> select object from context_objects where context_key=100041;
>           object
> -----------------------------
> \005\007\021\007Article\003
> (1 row)
> 
> How can I modify the data and put it back into the column?

Presuming the working environment:

CREATE TABLE context_objects(
  id serial primary key,
  object bytea not null
);

-- to avoid E'' and double-escaping
SET standard_conforming_strings = on;

INSERT INTO context_objects (object)
VALUES ( '\005\007\021\007Article\003' ), ( 'test\375' );

I'd just SELECT the `object' value, receiving it in escape form from the
PostgreSQL protocol:

SELECT object FROM context_objects
           object
-----------------------------
\005\007\021\007Article\003

... which is a string of 27 characters in the local client_encoding. So
in Perl (or whatever) I'd have a string of length 27:

  \005\007\021\007Article\003

I'd then convert that by parsing the escape sequences. (As Perl uses \0
as the octal escape prefix instead of just \, it's necessary to write a
simple loop for that. I don't do Perl voluntarily and haven't used it
for a while so I won't write one inline here, but it should be easy enough.)

Once you have the byte string (length 12 bytes) you manipulate it
however you want, then convert it back to octal escape form for sending
to the Pg server.

client_encoding has no effect on any of this so long as you're using a
client encoding that preserves the lower 128 characters, ie basically
anything except shift-jis.

Now, if your 'object' is actually a string in a single known text
encoding, not just a sequence of bytes, then another approach is
possible. First, stop using `bytea' for text. If the byte sequences are
all known to be latin-1 encoded text, for example, use:

ALTER TABLE context_objects ALTER COLUMN object TYPE text
USING ( convert_from(object, 'latin-1'));

--
-- Now retrieve the rows, which will be returned as text in
-- the client_encoding. Note that \375 is ý in latin-1.
--
testdb=> SELECT object FROM context_objects;
            object
-----------------------------
 \x05\x07\x11\x07Article\x03
 testý
(2 rows)

(Note: If they're all of different encodings, but you know the
encodings, you can make the encoding param of convert_from a column
reference instead).

Now you have 'object' as server-side utf-8 encoded text that'll be
automatically converted to and from the specified client_encoding . If
you want to get the raw server-side-encoded byte sequence you can always
cast to bytea in your query to get the utf-8 byte sequence in octal
escape form:

testdb=> SELECT object::bytea FROM context_objects;
           object
-----------------------------
 \005\007\021\007Article\003
 test\303\275

... but usually you'd just let the DB

> I'm trying to track down while a ten year old system no longer works
> after a Perl DBI upgrade.

Is it just Perl DBI? Or is it also Pg?

Could it be related to the standards_conforming_strings change that
affects string escaping ?

--
Craig Ringer

--

-- 
Sent via pgsql-sql mailing list (pgsql-sql <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql