Re: [GENERAL] Convert from hex to string

[Adrian Klaver]

On 11/25/2015 07:47 AM, Yuriy Rusinov wrote:

Hello, Colleagues !

I have to transform string, encoded to hexadecimal to text, but if I try to
select encode('Qt is great!', 'hex'); I receive valid and correct results

517420697320677265617421

but if I try to select decode ('517420697320677265617421', 'hex'), I
receive the same string, such as
'\x517420697320677265617421, which way I have to do for valid convert
to/from hexadecimal ?


aklaver@test=> select encode('Qt is great!', 'hex');
  encode
--
 517420697320677265617421
(1 row)

aklaver@test=> select decode ('517420697320677265617421', 'hex');
   decode

 \x517420697320677265617421
(1 row)


aklaver@test=> select convert_from('\x517420697320677265617421', 'UTF8');
 convert_from
--
 Qt is great!
(1 row)




Thanks for attention.




--
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] Convert from hex to string

[Yuriy Rusinov]

Hello, Colleagues !

I have to transform string, encoded to hexadecimal to text, but if I try to
select encode('Qt is great!', 'hex'); I receive valid and correct results

517420697320677265617421

but if I try to select decode ('517420697320677265617421', 'hex'), I
receive the same string, such as
'\x517420697320677265617421, which way I have to do for valid convert
to/from hexadecimal ?

Thanks for attention.
-- 
Best regards,
Sincerely yours,
Yuriy Rusinov.


-- 
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] Convert from hex to string

[Adrian Klaver]

On 11/25/2015 08:56 AM, Francisco Olarte wrote:

Hello Yuriy...

On Wed, Nov 25, 2015 at 4:47 PM, Yuriy Rusinov  wrote:

I have to transform string, encoded to hexadecimal to text, but if I try to
select encode('Qt is great!', 'hex'); I receive valid and correct results

517420697320677265617421

but if I try to select decode ('517420697320677265617421', 'hex'), I
receive the same string, such as
'\x517420697320677265617421, which way I have to do for valid convert
to/from hexadecimal ?


I seem to recall having answered this a very short time ago, but maybe
it was in the spanish list.

decode/encode are for converting bytes to a string. You need to
convert the string to bytes in a controlled way first ( bear in mind
there are implicit conversions ).

What you want is, given a text:

1.- Convert it to a bytea, in a controlled encoding: convert_to(string
text, dest_encoding name) => bytea
2.- Then encode the bytes in hex: encode(data bytea, format text) => text

then, to revert it you:

3.- Decode the hex string to bytes: decode(string text, format text) => bytea


Can't this be shortened to:

aklaver@test=> select convert_to( 'é', 'latin1');
 convert_to

 \xe9
(1 row)

aklaver@test=> select convert_from( '\xe9', 'latin1');
 convert_from
--
 é

since convert_to() outputs bytea and convert_from() accepts bytea?


4.- Convert the bytea, in a controlled encoding, to text:
convert_from(string bytea, src_encoding name) => text

As you see, they are nicelly paired. I see another response which just
does encode , decode+convert_from. This works because the database
does implicit conversions, but I would not recomend it. I cannot try
it because all my databases are UTF-8 but I feel Adrians example would
not work if your database encoding is NOT UTF-8 ( and you use any char
outside of ascii range ).


If you are doing all this in the same database I am not sure how the 
above applies?


Would you not just use the database encoding for the src_encoding?



Look at the docs of the functions, section 9.4 table 9.7 int the 9.4.5 manual.

If you do it this way, you can also choose the encoding, ie, if you
know your data is latin1 you can convert from/to it and save a few
bytes, or you can convert to/from utf8 an insure you can represent
anything. Then you can encode/decode the bytes in whatever sutis you,
hex, as in yuour eample or base64 if you need to save a few bytes.

Types are there for a reason.

Francisco Olarte.





--
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] Convert from hex to string

[Francisco Olarte]

Hello Yuriy...

On Wed, Nov 25, 2015 at 4:47 PM, Yuriy Rusinov  wrote:
> I have to transform string, encoded to hexadecimal to text, but if I try to
> select encode('Qt is great!', 'hex'); I receive valid and correct results
>
> 517420697320677265617421
>
> but if I try to select decode ('517420697320677265617421', 'hex'), I
> receive the same string, such as
> '\x517420697320677265617421, which way I have to do for valid convert
> to/from hexadecimal ?

I seem to recall having answered this a very short time ago, but maybe
it was in the spanish list.

decode/encode are for converting bytes to a string. You need to
convert the string to bytes in a controlled way first ( bear in mind
there are implicit conversions ).

What you want is, given a text:

1.- Convert it to a bytea, in a controlled encoding: convert_to(string
text, dest_encoding name) => bytea
2.- Then encode the bytes in hex: encode(data bytea, format text) => text

then, to revert it you:

3.- Decode the hex string to bytes: decode(string text, format text) => bytea
4.- Convert the bytea, in a controlled encoding, to text:
convert_from(string bytea, src_encoding name) => text

As you see, they are nicelly paired. I see another response which just
does encode , decode+convert_from. This works because the database
does implicit conversions, but I would not recomend it. I cannot try
it because all my databases are UTF-8 but I feel Adrians example would
not work if your database encoding is NOT UTF-8 ( and you use any char
outside of ascii range ).

Look at the docs of the functions, section 9.4 table 9.7 int the 9.4.5 manual.

If you do it this way, you can also choose the encoding, ie, if you
know your data is latin1 you can convert from/to it and save a few
bytes, or you can convert to/from utf8 an insure you can represent
anything. Then you can encode/decode the bytes in whatever sutis you,
hex, as in yuour eample or base64 if you need to save a few bytes.

Types are there for a reason.

Francisco Olarte.


-- 
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] Convert from hex to string

[Francisco Olarte]

On Wed, Nov 25, 2015 at 6:22 PM, Adrian Klaver
 wrote:
>> 1.- Convert it to a bytea, in a controlled encoding: convert_to(string
>> text, dest_encoding name) => bytea
>> 2.- Then encode the bytes in hex: encode(data bytea, format text) => text
>>
>> then, to revert it you:
>>
>> 3.- Decode the hex string to bytes: decode(string text, format text) =>
>> bytea
>
>
> Can't this be shortened to:
>
> aklaver@test=> select convert_to( 'é', 'latin1');
>  convert_to
> 
>  \xe9
> (1 row)
>
> aklaver@test=> select convert_from( '\xe9', 'latin1');
>  convert_from
> --
>  é
>
> since convert_to() outputs bytea and convert_from() accepts bytea?

Yes, but he originally asked to convert to hex, I assumed a hex string.

Note you are using hex because this is th default encoding for bytea
output in the database, as the database needs to convert everything to
text to send it to psql to display it and psql sends text which the
database needs to convert to operate.

But if you are using something like, say, java, you would need to bind
convert_to output and convert_from input to a byte[] ( although maybe
jdbc is sending/receivine hex strings in the client encoding, the wire
protocol is transparent to you ), which you can then print however you
like, if you want to bind String with hex encoded data you nide the
encode/decode steps.


>> As you see, they are nicelly paired. I see another response which just
>> does encode , decode+convert_from. This works because the database
>> does implicit conversions, but I would not recomend it. I cannot try
>> it because all my databases are UTF-8 but I feel Adrians example would
>> not work if your database encoding is NOT UTF-8 ( and you use any char
>> outside of ascii range ).
>
> If you are doing all this in the same database I am not sure how the above
> applies?

You explicitly used convert_from with UTF8, if the database was latin
1 or ebcdic you would have an encoding mismatch, as the text output
routines will convert the input text to bytea using that encoding.

> Would you not just use the database encoding for the src_encoding?

I do not quite understand the question, if you want your
encode-decode-convert_from towork, yes, you use the database encoding
in convert from, but I did not see a 'show client encoding' or similar
thing in your sample.

Anyway, I was assuming the hex conversion was needed for something
more complex than just pasting the data, for that anything can go,
including a \g | perl -pe 'unpack H*'. So I tried to show how the data
flows without relying on any implicit conversion, the
convert_to+encode => decode+convert_from works in any client encoding,
even in a thing like ebcdic.

Francisco Olarte.


-- 
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 a postgres table as a multi-writer multi-updater queue

[Jeff Janes]

On Mon, Nov 23, 2015 at 2:41 AM, Chris Withers  wrote:
> Hi All,
>
> I wondered if any of you could recommend best practices for using a postgres
> table as a queue. Roughly speaking, 100-200 workers will vomit rows and
> rates of a few hundres per second into the table leaving the status as new
> and then as many workers as needed to keep up with the load will plough
> through the queue changing the status to something other than new.

Is that several hundred per second per worker, or just several hundred
per second in total?

What happens if the worker finds the database has crashed when it goes
to insert the records?  That would determine what kind of
transactional system you need.  On the consumer side, what would
happen if a record were processed twice?


>
> My naive implementation would be something along the lines of:
>
> CREATE TABLE event (
> tstimestamp,
> event char(40),
> statuschar(10),
> CONSTRAINT pkey PRIMARY KEY(ts, event)
> );

How long are going you keep these records around for once processed?

Unless you delete them immediately, you will probably want a partial
index on (ts) where status='new'

> ...with writers doing INSERT or COPY to get data into the table and readers
> doing something like:
>
> SELECT FOR UPDATE * FROM event WHERE status='new' LIMIT 1000;
>
> ...so, grabbing batches of 1,000, working on them and then setting their
> status.

Why 1000 at a time?  Also, you probably need an ORDER BY

> But, am I correct in thinking that SELECT FOR UPDATE will not prevent
> multiple workers selecting the same rows?

You are correct, but the way it will prevent multiple workers from
selecting them at the same time is that the next worker will block
until the first one commits.  You would either use need to use SKIP
LOCKED in 9.5 release, or you would need to use
pg_try_advisory_xact_lock on lower versions, to avoid that.

And, how do you unlock the rows?  There are two general approaches.
One is to lock the row using PostgreSQL's FOR UPDATE type locks, and
hold the transaction open while processing, then updating the row to
mark it done and committing to release the lock.  This cleans up after
itself in the case a worker crashes, but there is no visibility into
what is going on.

The other is to claim the row for the worker by updating a status
field (for example, to have a hostname and pid), and committing that.
And then doing the processing, then updating it again to set it as
done, and committing that.  An abnormal terminated worker will need to
have someone or something clean up after it, but it gives you much
better visibility into what is happening.

>
> Anyway, is this approach reasonable? If so, what tweaks/optimisations should
> I be looking to make?

The best optimization would be to not do it at all.  Why can't the 200
inserting worker just do the work themselves immediately (either
client side or server side), instead of queuing it?  If there is any
possibility of making the processing fast enough to do it that way,
I'd surely spend my time optimizing the actual work, rather than
optimizing a queuing system.

And depending on what failure modes you can tolerate, consider a
best-effort dedicated queuing system rather than a perfectly ACID one
built on PostgreSQL.

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] Error creating Tablespace on Windows

[Begin Daniel]

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of deepak
Sent: November-25-15 17:07
To: John R Pierce
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Error creating Tablespace on Windows

I chose temp only for illustration purposes.  Actually the regression test 
"tablespace" fails too,
for the same reason, when run from sources (which creates a tablespace 
directory under
src/test/regress)

The above query used to work for me. May be you should make sure to use the 
appropriate owner.
CREATE TABLESPACE workspace1 OWNER postgres LOCATION 'E:\\TempFiles\\pgsqlData';
--
Deepak

On Wed, Nov 25, 2015 at 2:15 PM, John R Pierce 
> wrote:
On 11/25/2015 12:01 PM, deepak wrote:
I am getting this permission denied error on Windows 2008 trying to create a 
tablespace.

template1=# create tablespace testtablespace location 
'c:/windows/temp1/testtablespace';
ERROR:  could not set permissions on directory 
"c:/windows/temp1/testtablespace": Permission denied


why are you putting database tables in the Windows directory?!?   I would 
instead put them somewhere like

C:\Users\postgres\AppData\Local\Temp   (assuming C:\Users\postgres is the 
%USERPROFILE% directory for the postgres service owner...)



--
john r pierce, recycling bits in santa cruz



--
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] Convert from hex to string

[Adrian Klaver]

On 11/25/2015 08:56 AM, Francisco Olarte wrote:

Hello Yuriy...

On Wed, Nov 25, 2015 at 4:47 PM, Yuriy Rusinov  wrote:

I have to transform string, encoded to hexadecimal to text, but if I try to
select encode('Qt is great!', 'hex'); I receive valid and correct results

517420697320677265617421

but if I try to select decode ('517420697320677265617421', 'hex'), I
receive the same string, such as
'\x517420697320677265617421, which way I have to do for valid convert
to/from hexadecimal ?


I seem to recall having answered this a very short time ago, but maybe
it was in the spanish list.

decode/encode are for converting bytes to a string. You need to
convert the string to bytes in a controlled way first ( bear in mind
there are implicit conversions ).

What you want is, given a text:

1.- Convert it to a bytea, in a controlled encoding: convert_to(string
text, dest_encoding name) => bytea
2.- Then encode the bytes in hex: encode(data bytea, format text) => text

then, to revert it you:

3.- Decode the hex string to bytes: decode(string text, format text) => bytea
4.- Convert the bytea, in a controlled encoding, to text:
convert_from(string bytea, src_encoding name) => text

As you see, they are nicelly paired. I see another response which just
does encode , decode+convert_from. This works because the database
does implicit conversions, but I would not recomend it. I cannot try
it because all my databases are UTF-8 but I feel Adrians example would
not work if your database encoding is NOT UTF-8 ( and you use any char
outside of ascii range ).


To follow up:

postgres@postgres=# CREATE DATABASE latin1_db ENCODING 'LATIN1' LC_CTYPE 
'C' LC_COLLATE 'C' TEMPLATE template0;

CREATE DATABASE

postgres@latin1_db=# \l latin1_db
   List of databases
   Name|  Owner   | Encoding | Collate | Ctype | Access privileges
---+--+--+-+---+---
 latin1_db | postgres | LATIN1   | C   | C |


postgres@postgres=# \c latin1_db
You are now connected to database "latin1_db" as user "postgres".

postgres@latin1_db=# \encoding
UTF8

postgres@latin1_db=# select encode('é', 'hex');
 encode

 e9
(1 row)

postgres@latin1_db=# select decode('e9', 'hex');
 decode

 \xe9
(1 row)

postgres@latin1_db=# select convert_from('\xe9', 'latin1');
 convert_from
--
 é
(1 row)



Look at the docs of the functions, section 9.4 table 9.7 int the 9.4.5 manual.

If you do it this way, you can also choose the encoding, ie, if you
know your data is latin1 you can convert from/to it and save a few
bytes, or you can convert to/from utf8 an insure you can represent
anything. Then you can encode/decode the bytes in whatever sutis you,
hex, as in yuour eample or base64 if you need to save a few bytes.

Types are there for a reason.

Francisco Olarte.





--
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] Convert from hex to string

[Yuriy Rusinov]

Thanks a lot.

All works fine.

On Wed, Nov 25, 2015 at 9:49 PM, Francisco Olarte 
wrote:

> Mail pingpong day. ;-)
>
> On Wed, Nov 25, 2015 at 7:27 PM, Adrian Klaver
>  wrote:
>
> > postgres@latin1_db=# \encoding
> > UTF8
>
> This does not matter, as you are sending/receiving hex data, and the
> encoding done when sending query results to you gets reverted when you
> send queries back to the server.
>
>
> > postgres@latin1_db=# select convert_from('\xe9', 'latin1');
> >  convert_from
> > --
> >  é
> > (1 row)
>
> This DOES matter, you've had to change the query for it to work, you
> had to look up ( or know beforehand ) the database encoding and change
> it accordingly. . I mean, when you do encode('text',) what you are
> really asking for is encode( implicit_text_to_bytea_conversion(text),
> ), so you save one step, but them you have to change your query to
> the 'latin1' encoding you discovered. This is what I meant, you had to
> look at the database properties. But if you do an explicit
> convert_form with any encoding capable of representing all your data,
> like utf-8 which can represent anything,  the database encoding does
> not matter. And it should not, proper code should work with any
> database encoding. Even more, I can do encode(convert_to(utf8)) in a
> latin1 database conecting with any encoding, then send the hex to
> convert_from(decode(),utf8) to an ebcdic database use another encoding
> ( Of course I may need to transcode the hex, but not to the ebcdic,
> but to the client encoding been used in teh second case ), ant it
> would work as long as all the characters In the source data are
> representable in the destination database ( if they are not you are
> out luck in any scheme you may think off ).
>
> You cannot encode generically an string to hex unless you define an
> encoding. How do you encode '1', "31" or "F1" ? or maybe "0031" or
> "3100"? You can do a quick hack, but normally what you want is first
> to encode a sequence of characters to a sequence of bytes and then
> hex-encode that, as nearly everybody uses the same conversion for
> hex-encoding a byte sequence. This means you can have a '0' in a
> ebcdic database, transform it to to [0x30] byte array, encode this as
> "30" and then transform the later to 00 30 00 10 because you are using
> UTF16-BE wire encoding. Encoding is tricky enough without relying on
> implicit convertion or on a character being the same as a byte.
>
> Francisco Olarte.
>



-- 
Best regards,
Sincerely yours,
Yuriy Rusinov.

Re: [GENERAL] Taking lot time

[Ramesh T]

Hi All,
9.3 version using pgadmin3

Query like below,20 columns fetching
 SELECT col1,col2,col3,...col.20
   FROM detail i,
adjdetail ia,
  WHERE i.trans_id = ia.detail_id AND
(i.event = ANY (ARRAY[21, 22, 3, 5]))
created indexes on  where clause declared columns.

Taking a lot of time above ..?any help apprectiated !.

I have total 4 gb ram,i changed below in postgres.conf

shared_buffers--1024mb
temp_bufffers=8mb
work_mem=200mb
maintanace_work_mem=500mb
seq_page_cost = 1.0
random_page_cost = 5.0
effective_cache_size = 1024MB



On Wed, Nov 18, 2015 at 11:12 PM, Adrian Klaver 
wrote:

> On 11/17/2015 04:18 AM, Ramesh T wrote:
>
>> the query is big it's selecting 20 rows from two table like i mentioned
>> above exaplain analyze
>>
>> what should i do..?any help
>>
>
> Please do not top post.
>
> I must be missing a post, as I see no explanation of what the query is
> doing.
>
>>
>> On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver > > wrote:
>>
>> On 11/03/2015 06:42 AM, Ramesh T wrote:
>>
>> I have a Query it taking a lot of time to fetch results
>> so,explain query gave
>>
>> "Hash Join  (cost=55078.00..202405.95 rows=728275 width=418)"
>> "  Hash Cond: (itd.tran_id = iad._adj__id)"
>> "  ->  Seq Scan on inv_detail itd  (cost=0.00..40784.18
>> rows=731029
>> width=95)"
>> "Filter: (event_type = ANY ('{21,22,3,5}'::integer[]))"
>> "  ->  Hash  (cost=20590.78..20590.78 rows=610978 width=331)"
>> "->  Seq Scan on inv_adj  iad  (cost=0.00..20590.78
>> rows=610978
>> width=331)"
>>
>> Can you Please let me know wt happen in query..?wt should i do..
>>
>>
>> And the query is?
>>
>>
>> --
>> Adrian Klaver
>> adrian.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

Re: [GENERAL] Taking lot time

[Adrian Klaver]

On 11/25/2015 07:46 AM, Ramesh T wrote:

First, please take a look at this:

https://en.wikipedia.org/wiki/Posting_style



Hi All,
 9.3 version using pgadmin3

Query like below,20 columns fetching
  SELECT col1,col2,col3,...col.20


When this thread started you said you where selecting 20 rows.
Which is correct 20 rows or columns and if columns how many rows?


FROM detail i,
 adjdetail ia,
   WHERE i.trans_id = ia.detail_id AND
(i.event = ANY (ARRAY[21, 22, 3, 5]))
created indexes on  where clause declared columns.

Taking a lot of time above ..?any help apprectiated !.


A lot of time being?

Where are you measuring this time?
Remember populating a GUI with lots of data can take time.



I have total 4 gb ram,i changed below in postgres.conf

shared_buffers--1024mb
temp_bufffers=8mb
work_mem=200mb
maintanace_work_mem=500mb
seq_page_cost = 1.0
random_page_cost = 5.0
effective_cache_size = 1024MB



On Wed, Nov 18, 2015 at 11:12 PM, Adrian Klaver
> wrote:

On 11/17/2015 04:18 AM, Ramesh T wrote:

the query is big it's selecting 20 rows from two table like i
mentioned
above exaplain analyze

what should i do..?any help


Please do not top post.

I must be missing a post, as I see no explanation of what the query
is doing.


On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver

>> wrote:

 On 11/03/2015 06:42 AM, Ramesh T wrote:

 I have a Query it taking a lot of time to fetch results
 so,explain query gave

 "Hash Join  (cost=55078.00..202405.95 rows=728275
width=418)"
 "  Hash Cond: (itd.tran_id = iad._adj__id)"
 "  ->  Seq Scan on inv_detail itd  (cost=0.00..40784.18
rows=731029
 width=95)"
 "Filter: (event_type = ANY
('{21,22,3,5}'::integer[]))"
 "  ->  Hash  (cost=20590.78..20590.78 rows=610978
width=331)"
 "->  Seq Scan on inv_adj  iad  (cost=0.00..20590.78
 rows=610978
 width=331)"

 Can you Please let me know wt happen in query..?wt
should i do..


 And the query is?


 --
 Adrian Klaver
adrian.kla...@aklaver.com 
>




--
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] Convert from hex to string

[Francisco Olarte]

Mail pingpong day. ;-)

On Wed, Nov 25, 2015 at 7:27 PM, Adrian Klaver
 wrote:

> postgres@latin1_db=# \encoding
> UTF8

This does not matter, as you are sending/receiving hex data, and the
encoding done when sending query results to you gets reverted when you
send queries back to the server.


> postgres@latin1_db=# select convert_from('\xe9', 'latin1');
>  convert_from
> --
>  é
> (1 row)

This DOES matter, you've had to change the query for it to work, you
had to look up ( or know beforehand ) the database encoding and change
it accordingly. . I mean, when you do encode('text',) what you are
really asking for is encode( implicit_text_to_bytea_conversion(text),
), so you save one step, but them you have to change your query to
the 'latin1' encoding you discovered. This is what I meant, you had to
look at the database properties. But if you do an explicit
convert_form with any encoding capable of representing all your data,
like utf-8 which can represent anything,  the database encoding does
not matter. And it should not, proper code should work with any
database encoding. Even more, I can do encode(convert_to(utf8)) in a
latin1 database conecting with any encoding, then send the hex to
convert_from(decode(),utf8) to an ebcdic database use another encoding
( Of course I may need to transcode the hex, but not to the ebcdic,
but to the client encoding been used in teh second case ), ant it
would work as long as all the characters In the source data are
representable in the destination database ( if they are not you are
out luck in any scheme you may think off ).

You cannot encode generically an string to hex unless you define an
encoding. How do you encode '1', "31" or "F1" ? or maybe "0031" or
"3100"? You can do a quick hack, but normally what you want is first
to encode a sequence of characters to a sequence of bytes and then
hex-encode that, as nearly everybody uses the same conversion for
hex-encoding a byte sequence. This means you can have a '0' in a
ebcdic database, transform it to to [0x30] byte array, encode this as
"30" and then transform the later to 00 30 00 10 because you are using
UTF16-BE wire encoding. Encoding is tricky enough without relying on
implicit convertion or on a character being the same as a byte.

Francisco Olarte.


-- 
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 creating Tablespace on Windows

[deepak]

I chose temp only for illustration purposes.  Actually the regression test
"tablespace" fails too,
for the same reason, when run from sources (which creates a tablespace
directory under
src/test/regress)

--
Deepak

On Wed, Nov 25, 2015 at 2:15 PM, John R Pierce  wrote:

> On 11/25/2015 12:01 PM, deepak wrote:
>
>> I am getting this permission denied error on Windows 2008 trying to
>> create a tablespace.
>>
>> template1=# create tablespace testtablespace location
>> 'c:/windows/temp1/testtablespace';
>> ERROR:  could not set permissions on directory
>> "c:/windows/temp1/testtablespace": Permission denied
>>
>
>
> why are you putting database tables in the Windows directory?!?   I would
> instead put them somewhere like
>
> C:\Users\postgres\AppData\Local\Temp   (assuming C:\Users\postgres is the
> %USERPROFILE% directory for the postgres service owner...)
>
>
>
> --
> john r pierce, recycling bits in santa cruz
>
>
>
> --
> 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] Convert from hex to string

[Adrian Klaver]

On 11/25/2015 10:49 AM, Francisco Olarte wrote:

Mail pingpong day. ;-)

On Wed, Nov 25, 2015 at 7:27 PM, Adrian Klaver
 wrote:


postgres@latin1_db=# \encoding
UTF8


This does not matter, as you are sending/receiving hex data, and the
encoding done when sending query results to you gets reverted when you
send queries back to the server.



postgres@latin1_db=# select convert_from('\xe9', 'latin1');
  convert_from
--
  é
(1 row)


This DOES matter, you've had to change the query for it to work, you
had to look up ( or know beforehand ) the database encoding and change
it accordingly. . I mean, when you do encode('text',) what you are
really asking for is encode( implicit_text_to_bytea_conversion(text),
), so you save one step, but them you have to change your query to
the 'latin1' encoding you discovered. This is what I meant, you had to
look at the database properties. But if you do an explicit
convert_form with any encoding capable of representing all your data,
like utf-8 which can represent anything,  the database encoding does
not matter. And it should not, proper code should work with any
database encoding. Even more, I can do encode(convert_to(utf8)) in a
latin1 database conecting with any encoding, then send the hex to
convert_from(decode(),utf8) to an ebcdic database use another encoding
( Of course I may need to transcode the hex, but not to the ebcdic,
but to the client encoding been used in teh second case ), ant it
would work as long as all the characters In the source data are
representable in the destination database ( if they are not you are
out luck in any scheme you may think off ).



I will grant you that working with encodings is like working with 
timestamps, explicit is better. The thing I am having a problem with is 
how not knowing the context of the bytea value is different in the 
implicit vs explicit case:


create table hex_test(hex_fld bytea);

aklaver@latin1_db=> select convert_to('é', 'utf8');
 convert_to

 \xc3a9
(1 row)

aklaver@latin1_db=> insert into hex_test values ('\xc3a9');


aklaver@latin1_db=> select encode('é', 'hex');
 encode

 e9
(1 row)

aklaver@latin1_db=> select decode('e9', 'hex');
 decode

 \xe9
(1 row)


aklaver@latin1_db=> insert into hex_test values ('\xe9');

aklaver@latin1_db=> select * from hex_test ;
 hex_fld
-
 \xc3a9
 \xe9
(2 rows)


aklaver@latin1_db=> select convert_from(hex_fld, 'latin1') from hex_test ;
 convert_from
--
 é
 é
(2 rows)

aklaver@latin1_db=> select convert_from(hex_fld, 'utf8') from hex_test ;
ERROR:  invalid byte sequence for encoding "UTF8": 0xe9

Granted the above is contrived and bound to fail, but the point is you 
need to know what created the bytea however it got there. Now if you are 
in charge of both ends of the process, then the above is your own fault. 
Otherwise, you are down to detective work on what encoding was used 
whether it was implicit or explicit. As the OP was working in a single 
context I am not seeing the issue in making use of that context to do 
the heavy lifting. For the use cases that you show I agree that a 
defined convert_to/encode/decode/convert_from chain is a best practice 
and something I had not really thought out, so thanks.




You cannot encode generically an string to hex unless you define an
encoding. How do you encode '1', "31" or "F1" ? or maybe "0031" or
"3100"? You can do a quick hack, but normally what you want is first
to encode a sequence of characters to a sequence of bytes and then
hex-encode that, as nearly everybody uses the same conversion for
hex-encoding a byte sequence. This means you can have a '0' in a
ebcdic database, transform it to to [0x30] byte array, encode this as
"30" and then transform the later to 00 30 00 10 because you are using
UTF16-BE wire encoding. Encoding is tricky enough without relying on
implicit convertion or on a character being the same as a byte.

Francisco Olarte.




--
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] Taking lot time

[Adrian Klaver]

On 11/25/2015 07:46 AM, Ramesh T wrote:


Hi All,
 9.3 version using pgadmin3

Query like below,20 columns fetching
  SELECT col1,col2,col3,...col.20
FROM detail i,
 adjdetail ia,
   WHERE i.trans_id = ia.detail_id AND
(i.event = ANY (ARRAY[21, 22, 3, 5]))
created indexes on  where clause declared columns.

Taking a lot of time above ..?any help apprectiated !.


Realized my previous post was not really helpful. What I should have 
said is below.


To get a useful answer you will need to supply a complete(from one run 
of the query) set of information for the problem you are experiencing.


So:

1) The complete definitions for the tables involved in the query, 
including FKs, triggers, etc.


2) The query you are running.

3) The EXPLAIN ANALYZE for said query

4) The number of rows returned from the query.

5) The procedure you used to getting the time value and where it was 
applied(psql, pgAdmin, etc).




I have total 4 gb ram,i changed below in postgres.conf

shared_buffers--1024mb
temp_bufffers=8mb
work_mem=200mb
maintanace_work_mem=500mb
seq_page_cost = 1.0
random_page_cost = 5.0
effective_cache_size = 1024MB



On Wed, Nov 18, 2015 at 11:12 PM, Adrian Klaver
> wrote:

On 11/17/2015 04:18 AM, Ramesh T wrote:

the query is big it's selecting 20 rows from two table like i
mentioned
above exaplain analyze

what should i do..?any help


Please do not top post.

I must be missing a post, as I see no explanation of what the query
is doing.


On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver

>> wrote:

 On 11/03/2015 06:42 AM, Ramesh T wrote:

 I have a Query it taking a lot of time to fetch results
 so,explain query gave

 "Hash Join  (cost=55078.00..202405.95 rows=728275
width=418)"
 "  Hash Cond: (itd.tran_id = iad._adj__id)"
 "  ->  Seq Scan on inv_detail itd  (cost=0.00..40784.18
rows=731029
 width=95)"
 "Filter: (event_type = ANY
('{21,22,3,5}'::integer[]))"
 "  ->  Hash  (cost=20590.78..20590.78 rows=610978
width=331)"
 "->  Seq Scan on inv_adj  iad  (cost=0.00..20590.78
 rows=610978
 width=331)"

 Can you Please let me know wt happen in query..?wt
should i do..


 And the query is?


 --
 Adrian Klaver
adrian.kla...@aklaver.com 
>




--
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 a postgres table as a multi-writer multi-updater queue

[Tim Uckun]

I'll add my two cents.

I set up something similar a while ago. Here are my suggestions for what
they are worth.

You don't ever want to delete from such a table so you need to set up
something which allows you to truncate the tables when you no longer need
them.

One way to accomplish this is with rules (not triggers, rules are blazingly
fast compared to triggers).Set up a table inheritance scheme based on
whatever search criteria you have (date, sequence etc).   Set up a cron job
to create the child tables well ahead and to set up the rule redirecting
the insert. For example let's say you have a date partitioned table and you
want to  keep a table for every day.  Your cron job would run once a day
and would create the next seven days worth of tables (just incase the cron
job fails to run for some reason) and would rewrite the rule to insert into
the table with a if then else type of logic.  This method is preferable to
the dynamic creation of the table name with string concatenation because
again it's significantly faster.

Another method I tried was to have one "primary" child table and "archival"
child tables and insert directly into the primal child table.  For example
say you have a table called "Q".  You set up a table called Q_in which
inherits from Q.  Your code inserts into the Q_in table, you select from
the Q table.  On a periodic basis you do this

BEGIN TRANSACTION
LOCK TABLE Q_IN IN EXCLUSIVE MODE;
ALTER TABLE Q_IN RENAME TO Q_SOME_DATETIME;
CREATE TABLE Q_IN
  (LIKE Q_SOME_DATETIME INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING
INDEXES);
ALTER SEQUENCE q_in_id_seq OWNED BY q_in.id;
 -- Set some constraints so the query optimizer knows what to do
END TRANSACTION


There is one other method which is the Kafka approach( You can use this in
addition to the above methods)

Create N tables for incoming queue, each one has a sequence for their ID
number. N should be determined by how many clients you expect to run.
Create a rule which uses some hash function or round robin or randomly to
insert data into one of the tables.   Create a different table which keeps
track of client connections.   The clients use this table to keep track of
the last id fetched.  For example let's have I have three types of
processes that run on the incoming data p1,p2, p3  The table logs the
highest ID fetched from each table for each process. When a client connects
it connects to the table with the lowest used ID for that process,  and it
locks it for that client (not process because you can multiple clients
running each process), it processes the records, it updates the id, it
unlocks the table and it backs off for a few seconds.  The next client
which woke up goes through the same process and so on.  Both Apache Kafka
and Amazon kinesis use this approach.  One nice thing about this approach
is that you can put each table in it's own tablespace in it's own disk for
higher performance.

One other thing. This is dangerous but you can turn off logging of the
tables, this will make the inserts much faster.

Finally:

There is no need to do any of this.  Kinesis is cheap, Kafka is pretty
awesome, Rabbit is crazy useful.

Hope this helps, ping me offline if you want more details.

Cheers.


On Tue, Nov 24, 2015 at 11:51 AM, Merlin Moncure  wrote:

> On Mon, Nov 23, 2015 at 12:54 PM, John R Pierce 
> wrote:
> > On 11/23/2015 2:41 AM, Chris Withers wrote:
> >>
> >>
> >> If it's totally wrong, how should I be looking to approach the problem?
> >
> > depending on where these queue entries are coming from, I'd considering
> > using a message queueing system like AMS, MQseries, etc, rather than
> trying
> > to use a relational database table as a queue. your external data
> source(s)
> > would write messages to this queue, and you'd have 'subscriber' processes
> > that listen to the queue and process the messages, inserting persistent
> data
> > into the database as needed.
>
> I just don't agree with this generalization.  Keeping the state of the
> queue in the database has a lot of advantages and is a lot easier to
> deal with from a programming perspective especially if SQL is your
> core competency.  Being able to produce and consume in SQL based on
> other relational datasources is...elegant.
>
> Specialized queue systems are a very heavy dependency and adding a new
> server to your platform to mange queues is not something to take
> lightly.  This advice also applies to scheduling systems like quartz,
> specialized search like solr and elastisearch, and distributed data
> platforms like hadoop.  I've used all of these things and have tended
> to wish I had just used the database instead in just about every case.
>
> Also, check out pgq (https://wiki.postgresql.org/wiki/PGQ_Tutorial).
> Personally, I tend to roll my own queues.   It's not difficult.
>
> merlin
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your 

[GENERAL] Error creating Tablespace on Windows

[deepak]

Hi,

I am getting this permission denied error on Windows 2008 trying to create
a tablespace.

template1=# create tablespace testtablespace location
'c:/windows/temp1/testtablespace';
ERROR:  could not set permissions on directory
"c:/windows/temp1/testtablespace": Permission denied


If I use 'c:/windows/temp/testtablespace' for location, it works.

If I look at properties for c:/windows/temp and c:/windows/temp1, they look
identical (same ownership and read/write permissions).

Any thoughts on what else to check?

This is with Postgresql 9.1.9.


--
Deepak

Re: [GENERAL] Error creating Tablespace on Windows

[John R Pierce]

On 11/25/2015 12:01 PM, deepak wrote:
I am getting this permission denied error on Windows 2008 trying to 
create a tablespace.


template1=# create tablespace testtablespace location 
'c:/windows/temp1/testtablespace';
ERROR:  could not set permissions on directory 
"c:/windows/temp1/testtablespace": Permission denied



why are you putting database tables in the Windows directory?!?   I 
would instead put them somewhere like


C:\Users\postgres\AppData\Local\Temp   (assuming C:\Users\postgres is 
the %USERPROFILE% directory for the postgres service owner...)




--
john r pierce, recycling bits in santa cruz



--
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] Taking lot time

[Melvin Davidson]

Adrian,

He never stated what the O/S is. I strongly suspect that he is using
PgAdmin to connect to a remote server, which adds another degree of
complication. So at the very least, we also need to know if the server is
local or remote and what the O/S is. Plus, as you have requested before,
exactly what time is "a long time"?

On Wed, Nov 25, 2015 at 3:50 PM, Adrian Klaver 
wrote:

> On 11/25/2015 07:46 AM, Ramesh T wrote:
>
>>
>> Hi All,
>>  9.3 version using pgadmin3
>>
>> Query like below,20 columns fetching
>>   SELECT col1,col2,col3,...col.20
>> FROM detail i,
>>  adjdetail ia,
>>WHERE i.trans_id = ia.detail_id AND
>> (i.event = ANY (ARRAY[21, 22, 3, 5]))
>> created indexes on  where clause declared columns.
>>
>> Taking a lot of time above ..?any help apprectiated !.
>>
>
> Realized my previous post was not really helpful. What I should have said
> is below.
>
> To get a useful answer you will need to supply a complete(from one run of
> the query) set of information for the problem you are experiencing.
>
> So:
>
> 1) The complete definitions for the tables involved in the query,
> including FKs, triggers, etc.
>
> 2) The query you are running.
>
> 3) The EXPLAIN ANALYZE for said query
>
> 4) The number of rows returned from the query.
>
> 5) The procedure you used to getting the time value and where it was
> applied(psql, pgAdmin, etc).
>
>
>> I have total 4 gb ram,i changed below in postgres.conf
>>
>> shared_buffers--1024mb
>> temp_bufffers=8mb
>> work_mem=200mb
>> maintanace_work_mem=500mb
>> seq_page_cost = 1.0
>> random_page_cost = 5.0
>> effective_cache_size = 1024MB
>>
>>
>>
>> On Wed, Nov 18, 2015 at 11:12 PM, Adrian Klaver
>> > wrote:
>>
>> On 11/17/2015 04:18 AM, Ramesh T wrote:
>>
>> the query is big it's selecting 20 rows from two table like i
>> mentioned
>> above exaplain analyze
>>
>> what should i do..?any help
>>
>>
>> Please do not top post.
>>
>> I must be missing a post, as I see no explanation of what the query
>> is doing.
>>
>>
>> On Wed, Nov 4, 2015 at 4:27 AM, Adrian Klaver
>> 
>> > >> wrote:
>>
>>  On 11/03/2015 06:42 AM, Ramesh T wrote:
>>
>>  I have a Query it taking a lot of time to fetch results
>>  so,explain query gave
>>
>>  "Hash Join  (cost=55078.00..202405.95 rows=728275
>> width=418)"
>>  "  Hash Cond: (itd.tran_id = iad._adj__id)"
>>  "  ->  Seq Scan on inv_detail itd  (cost=0.00..40784.18
>> rows=731029
>>  width=95)"
>>  "Filter: (event_type = ANY
>> ('{21,22,3,5}'::integer[]))"
>>  "  ->  Hash  (cost=20590.78..20590.78 rows=610978
>> width=331)"
>>  "->  Seq Scan on inv_adj  iad
>> (cost=0.00..20590.78
>>  rows=610978
>>  width=331)"
>>
>>  Can you Please let me know wt happen in query..?wt
>> should i do..
>>
>>
>>  And the query is?
>>
>>
>>  --
>>  Adrian Klaver
>> adrian.kla...@aklaver.com 
>> > >
>>
>>
>>
>>
>> --
>> 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
>



-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

[George Neuner]

On Wed, 25 Nov 2015 23:39:39 -0500, "Steve Petrie, P.Eng."
 wrote:


>Now, I'm reading in this forum that in fact, postgres does not efficiently
>automatically recycle storage space released by row DELETion.

Yes and no.  

Space resulting from deleted rows is not given back to the OS unless
you perform a full vacuum.  Autovacuum compacts the table, squeezing
out empty rows and leaving free space at the end for new insertions.

If the table is heavily used, you will reuse the free space quickly
anyway.  The problem you may run into is needing to autovacuum too
often to control the size of the table.

Space from truncated or dropped tables is immediately given back to
the OS.

The issue with heavy deletion usually is performance.  Deleting scans
the rows and fires any relevant triggers ... truncating or dropping
the table does not.  


>My plan was always, to avoid eventual exhaustion of the SERIAL
>sequence number integer value series, by swapping in during the 
>periodic app shutdown, a freshly truncated postgres 
>  table.

Is there a logical problem with letting the sequence wrap around?  


>So my question to this postgres forum is -- should I just remove from 
>the online app the "fuzzy" probability mechanism, that DELETEs expired
>rows from the session table -- because the postgres server is not going 
>to dynamically recycle the released storage space anyway?

I'm not sure I understand the reason for "fuzzy" deletion.  There are
a number of timestamps in your data ... is it not possible to delete
deterministically based on one of them?


Hope this helps,
George 



-- 
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] Unexpected behaviour of 'DEFERRABLE INITIALLY DEFERRED'

[Adrian Klaver]

On 11/25/2015 04:32 AM, Jong-won Choi wrote:

Hi all,

This is from my local PostgreSQL log:


LOG:  statement: begin;
LOG:  statement: INSERT INTO site_owner (email, name, pwhash, data)
VALUES ('xyz', 'xyz', crypt('xyz', gen_salt('bf', 8)), '{}'::JSONB)
RETURNING id;
LOG:  statement: INSERT INTO site (owner, name, timezone, data) VALUES
('150c2fff-1029-47a5-aaa8-3e974e8442c6', 'sitex', 'Australia/NSW',
'{}'::JSONB) RETURNING id;
LOG:  statement: commit;



LOG:  statement: BEGIN
LOG:  execute : INSERT INTO site_owner (email, name, pwhash,
data) VALUES ($1, $2, crypt($3, gen_salt('bf', 8)), $4::JSONB) RETURNING id
DETAIL:  parameters: $1 = 'test@email6', $2 = 'Name', $3 = 'password',
$4 = '{}'
LOG:  execute : INSERT INTO site (owner, name, timezone, data)
VALUES ($1, $2, $3, $4::JSONB) RETURNING id
DETAIL:  parameters: $1 = '3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e', $2 =
'Test', $3 = 'Australia/NSW', $4 = '{}'
ERROR:  insert or update on table "site" violates foreign key constraint
"site_owner_fkey"
DETAIL:  Key (owner)=(3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e) is not
present in table "site_owner".
LOG:  statement: ROLLBACK



The first 'successful' part is a result of SQL statements from psql, and
the second 'unsuccessful' part is a result of client library function
calls from my application.

It seems to me they both have the same structure, except the first part
is consist of 'statements' and the second part is consist of
'execute'(prepare statements, I guess).

Any hints which can make the second part successful?


First what has this to do with the subject, in particular "'DEFERRABLE 
INITIALLY DEFERRED"?


The error seems fairly straight forward:

ERROR:  insert or update on table "site" violates foreign key constraint 
"site_owner_fkey"
DETAIL:  Key (owner)=(3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e) is not 
present in table "site_owner"


To understand more we will need to see the schema for:

site_owner
site

including the FK relationship between them.

Also what is creating the owner key in site_owner as I do not see that 
field in the INSERT to site_owner.





Thanks

- Jong-won





--
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 a postgres table as a multi-writer multi-updater queue

[Jeff Janes]

On Wed, Nov 25, 2015 at 8:39 PM, Steve Petrie, P.Eng.
 wrote:
>
>> You don't ever want to delete from such a table so you need to set up
>> something which allows you to truncate the tables when you no longer need
>> them.
>
> I am migrating a web PHP application (called ITS-ETO) from mysql to
> postgres. The app INSERTs a row into a postgres table
>  to manage each session with a web browser. Here is
> the DDL for the session table:
>
> CREATE TABLE its_eto.eto_sql_tb_session_www
> (
>   session_www_code   char(32)  NOT NULL UNIQUE PRIMARY KEY,
>
>   session_www_type   int   NOT NULL,
>   session_www_state  int   NOT NULL,
>   session_verify_codechar(7)   NOT NULL,
>
>   session_www_serno  SERIALNOT NULL UNIQUE,
>
>   session_target_serno   int   NULL,
>   session_target_datavarchar(1000) NULL,
>
>   session_www_init_utc   timestamp NOT NULL,
>   session_www_last_utc   timestamp NOT NULL,
>   session_www_expiry_utc timestamp NOT NULL,
>   session_www_delete_utc timestamp NOT NULL,
>   session_www_hit_count  int   NOT NULL,
>   session_www_act_seqno  int   NULL
>
> );
>
> CREATE INDEX ON its_eto.eto_sql_tb_session_www (session_www_serno);
>
> Using a "fuzzy" probability mechanism, some randomly-selected fraction of
> the HTTP requests that initiate a new session, also SELECT and DELETE
> expired rows from the session table. I naively assumed that the database
> server would automatically recycle the storage space dynamically released in
> this way.
>
> Now, I'm reading in this forum that in fact, postgres does not efficiently
> automatically recycle storage space released by row DELETion.

> My application is quite simple and will be supporting a modest workload,
> using a small amount of storage space, compared to the massive transaction
> rates and gigantic space usages, I'm reading about in this forum.


Truncation is far more efficient than deletion + vacuuming.  If you
are running on the edge of your hardware's capabilities, this
efficiency is important.  But if you are not on the edge, then it is
not worth worrying about.  Just make sure your autovacuum settings are
at least as aggressive as the default settings.


>
> I do have the luxury of being able to shut down the application for a few
> minutes periodically e.g every 24 hours.
>
> My plan was always, to avoid eventual exhaustion of the SERIAL sequence
> number integer value series, by swapping in during the periodic app
> shutdown, a freshly truncated postgres  table.

I'd make the serial column and bigserial, and then forget about it.

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] Convert from hex to string

[Francisco Olarte]

Hi Adrian:

On Wed, Nov 25, 2015 at 9:33 PM, Adrian Klaver
 wrote:
> I will grant you that working with encodings is like working with
> timestamps, explicit is better. The thing I am having a problem with is how
> not knowing the context of the bytea value is different in the implicit vs
> explicit case:

> Granted the above is contrived and bound to fail, but the point is you need
> to know what created the bytea however it got there. Now if you are in
> charge of both ends of the process, then the above is your own fault.
> Otherwise, you are down to detective work on what encoding was used whether
> it was implicit or explicit. As the OP was working in a single context I am
> not seeing the issue in making use of that context to do the heavy lifting.
> For the use cases that you show I agree that a defined
> convert_to/encode/decode/convert_from chain is a best practice and something
> I had not really thought out, so thanks.

Well, I tend to think as a programmer. So I was thinking of the
scenario where you want to get some text from the database and move it
around, and you control all the code. What I was trying to point is
that using explicit all the character sets that matter are in the
database, so I can move it around freely, as I'm the one defining the
queries, while in the implicit case I have to know, or get from the
catalogs, the encoding of the database. I do not know what context the
OP was working and wanted to point he was mixing types. Postgres has a
lot of them, specially to/from text, and I've found the hard way that
lots of implicit conversions are great for one shot programs or
interactive tests, but relying on implicit type conversions for real
production code, put in a source, causes a lot of problems. In a
single session case you can even use implicit conversion + encode and
then paste the result into a convert_from adding quotes and x and it's
going to work, but if you write down that in code you are going to be
confused if something fails later, things like:

cdrs=> select encode('Año','hex');
  encode
--
 41c3b16f
cdrs=> select convert_from('\x41c3b16f', 'UTF-8');
 convert_from
--
 Año
(1 row)

seem like you are converting back and forth, but then:

cdrs=> select convert_from(encode('Año','hex'), 'UTF-8');
ERROR:  function convert_from(text, unknown) does not exist
LINE 1: select convert_from(encode('Año','hex'), 'UTF-8');

And also, the encode() example will give different results depending
on database encoding. Using explicit charsets and correct types frees
me from it. After all, to make the full round trip a
covert-to+encode+decode+convert-from is needed, and making it explciti
makes things easier.


After all, not everybody knows that all values in the database pass
through a conversion to/from text to be sent to / received from psql,
and that you can use it if you just want the hex digits in the current
database encoding:

cdrs=> select 'Año'::bytea;
   bytea

 \x41c3b16f
(1 row)

But I doubt using cast instead of encode can be recomended.

And for the heavy lifting, you are just saving some keystrokes, which
IMO is a cheap price to pay for having a nearly self documenting
conversion chain.

Anyway, I think people should be told to respect types, and people
should be teach that strings are sequences of characters, that to do
what people think is 'hex encoding' ( two digits per *byte*, no
delimiter ), you need to first transform the string to bytes, then
hex-encode that. Nearly all the encoding problems I see is because
people thing strings are sequences of bytes, which they ceased to be
when multibyte encodings where detected ( and even without them, in
Java and I think some of the windows NT API Strings where sequences of
16 bits thingos, first UCS-2. then UTF16. Once people stop trying to
encode/decode strings directly normally they problems vanish.

Francisco Olarte.


-- 
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] Problems with pg_upgrade after change of unix user running db.

[Benedikt Grundmann]

On Tue, Nov 24, 2015 at 8:04 PM, Bruce Momjian  wrote:

> On Mon, Nov 23, 2015 at 11:12:25AM +, Benedikt Grundmann wrote:
> > I got this error trying to upgrade one of our database clusters (happily
> in
> > testing) from 9.2 to 9.4:
> >
> > Old and new cluster install users have different values for pg_authid.oid
> >
> > Important background here is that we used to run the database as the
> postgres
> > unix user, but recently we had changed it to run as a different user
> (because
> > we have several different databases all running as the postgres user on
> > different machines and we wanted each logically separate database to run
> as a
> > different extra for that purpose unix user -- this simplified internal
> > administration management).
> >
> > We had done this by adding a new superuser to the database (with the
> name of
> > the unix user it will run as in the future). turning off the database,
> chown -R
> >  databasedir, starting the database
>
> Your description is very clear.  In 9.4 and earlier, Postgres checks
> that the user running upgrade has the same pg_authid.oid in the old and
> new clusters.  In 9.5 we check that the user is the
> BOOTSTRAP_SUPERUSERID (10) on both the old and new cluster.
>
> Therefore, what I suggest you do, before running pg_upgrade, is to
> rename the pg_authid.oid = 10 row to be your new install user instead of
> 'postgres', and make your new user row equal 'postgres', e.g. something
> like:
>
> --  You already did this first one
> --> test=> create user my_new_install_user;
> --> CREATE ROLE
>
> select oid from pg_authid where rolname = 'my_new_install_user';
>   oid
> ---
>  16385
> (1 row)
>
> select oid from pg_authid where rolname = 'postgres';
>  oid
> -
>   10
> (1 row)
>
> -- 'XXX' prevents duplicate names
> update pg_authid set rolname = 'XXX' where oid = 10;
> UPDATE 1
> update pg_authid set rolname = 'postgres' where oid = 16385;
> UPDATE 1
> update pg_authid set rolname = 'my_new_install_user' where oid =
> 10;
> UPDATE 1
>
> What this does it to make your new install user the bootstrap user,
> which is a requirement for 9.5 pg_upgrade.  You would do this _before_
> running pg_upgrade as my_new_install_user.  However, keep in mind that
> once you do this, everthing owned by my_new_install_user and postgres
> are now swapped.  This is basically what you need to do after changing
> the ownership of the Postgres file system files.
>
> You can see the 9.5 requirements in the pg_upgrade function
> check_is_install_user().  You might as well just honor what that
> requires as you will eventually be moving to 9.5.
>

Thanks I'll try this in one of the next days.  Sorry for the radio silence
in the last 2 days.  We have been quite busy at work.  I don't think I
understand yet why this restriction exists (Neither the old nor the new).
Is there some doc somewhere that explains what's going on?  I tried to find
something in the otherwise excellent postgres docs but failed.




>
> --
>   Bruce Momjian  http://momjian.us
>   EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Roman grave inscription +
>

Re: [GENERAL] What could cause CREATE TEMP... "could not read block" error?

[Albe Laurenz]

Chris Richards wrote:
[had corruption in pg_depend indexes]
> I've observed the problem on other than the "pg_depend" table. A normal table 
> (called "blocks") had
> the problem. A "REINDEX TABLE blocks" did not fix the problem, however a 
> "VACUUM FULL" did "fix" it.

Did you dump/remove cluster/initdb/restore?
That's the only reliable way to get rid of corruption.

>> Are you running the latest minor release for your PostgreSQL version?
> 
> No. 9.3.9 whereas the lastest is 9.3.10

I think no data corruption bugs have been discovered since 9.3.9.

>> Did you make sure that you have a reliable storage system?
> 
> RAID-6 SSDs on ext4

Did you test if it is reliable?
Pulled the plug on the server during a busy checkpoint?
Or read http://brad.livejournal.com/2116715.html

But that shouldn't apply to your case if there was no crash.

>> Were there any crashes recently?
> 
> No[*]. See comments below about LVM snapshots.

> We had thought we figured out what might have caused the problem. We were 
> taking LVM snapshots while
> the database was running and then at sometime later we reverted the snapshot 
> (while postgres was
> stopped). Our theory was that since postgres was running and therefore we 
> captured its backing store
> in an indeterminate state. We changed our snapshot process to shutdown 
> postgres, take the LVM
> snapshot, and then start postgres.

If the snapshot is truly atomic, it should work - that would be just like 
restarting
PostgreSQL after a server crash.  PostgreSQL is designed to handle such a case 
well,
provided that the storage does what it promises, i.e. make sure that data are 
really on disk
when an fsync() is issued.

Is the whole database, including pg_xlog, on one logical volume?

> Unfortunately, the problem cropped up again.
> 
> We had an empty database except for our created tables, took the snapshot, 
> and then populated the
> tables using normal INSERT/UPDATE/DELETEs while the system ran. Then, we 
> reboot and revert the LVM
> snapshot--back to the empty database. We then populated the tables by using a 
> series "COPY 
> FROM ". The only things special about this is we dropped one foreign 
> key constraint and that
> with our data files is that we may issue back-to-back COPY's to the same 
> table (each COPY pulling in
> different data as we pre-process the data before COPY'ing it). The amount of 
> data is relatively small
> from our COPY's:
> 
> dirtable - 1 row
> dirpath - 1 row
> cloud - 940 rows
> blocks - 176 rows (before it died)
> 
> Indexes were not disabled during the bulk import.

It "died" because of data curruption?
And you had PostgreSQL shut down when you took the snapshot?
Was this a new database cluster (not just a new database) created
with initdb just before the test?

Now that should work even if your storage lies to you.
Unless there is something quite wrong with your storage - RAID checksums
should prevent the effects of a failing disk from affecting you.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

[NTPT]

Hi, all. 

I need help.

pg_exec(): Query failed: ERROR: character with byte sequence 0xc2 0x96 in 
encoding "UTF8" has no equivalent in encoding "WIN1250"

It is a strange. First there was a database with latin2 encoding.

to this database connect an aplicaton with "set client encoding to win1250" 
and manipulating data

then database was dumped with pg_dump -E UTF8

then database was restored pg_restore on another cluster in database with 
UTF8 encoding

then application connect to new database with "set client encoding to win
1250"

and - query failed


How in this scenario could invaid  characters reach the database ???

And how to solve this ? Errort message is not very useful, because does not 
provide any hint (at least column and row)

[GENERAL] Unexpected behaviour of 'DEFERRABLE INITIALLY DEFERRED'

[Jong-won Choi]

Hi all,

This is from my local PostgreSQL log:


LOG:  statement: begin;
LOG:  statement: INSERT INTO site_owner (email, name, pwhash, data) 
VALUES ('xyz', 'xyz', crypt('xyz', gen_salt('bf', 8)), '{}'::JSONB) 
RETURNING id;
LOG:  statement: INSERT INTO site (owner, name, timezone, data) VALUES 
('150c2fff-1029-47a5-aaa8-3e974e8442c6', 'sitex', 'Australia/NSW', 
'{}'::JSONB) RETURNING id;

LOG:  statement: commit;



LOG:  statement: BEGIN
LOG:  execute : INSERT INTO site_owner (email, name, pwhash, 
data) VALUES ($1, $2, crypt($3, gen_salt('bf', 8)), $4::JSONB) RETURNING id
DETAIL:  parameters: $1 = 'test@email6', $2 = 'Name', $3 = 'password', 
$4 = '{}'
LOG:  execute : INSERT INTO site (owner, name, timezone, data) 
VALUES ($1, $2, $3, $4::JSONB) RETURNING id
DETAIL:  parameters: $1 = '3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e', $2 = 
'Test', $3 = 'Australia/NSW', $4 = '{}'
ERROR:  insert or update on table "site" violates foreign key constraint 
"site_owner_fkey"
DETAIL:  Key (owner)=(3a83db7f-c82e-4b7a-a5c7-c0b43f415b0e) is not 
present in table "site_owner".

LOG:  statement: ROLLBACK



The first 'successful' part is a result of SQL statements from psql, and 
the second 'unsuccessful' part is a result of client library function 
calls from my application.


It seems to me they both have the same structure, except the first part 
is consist of 'statements' and the second part is consist of 
'execute'(prepare statements, I guess).


Any hints which can make the second part successful?

Thanks

- Jong-won


--
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] full_page_writes on SSD?

[NTPT]

Hi, 


I investigate bit about SSD  and how it works and need to be aligned .

And I  conclude that in the ideal world we need a  general --ebs=xxx switch 
in various linux tools to ensure alignment. Or make calculation by had.. 

On the market there are SSD disks with page size 4 or 8 kb. But there is  
for ssd disk typical property - the EBS - Erase Block Size. If disk operate 
and write to single sector, whole Erase block must be read by driver 
electronic, modified and write back to the drive. 

On the market there are devices with multiple EBS sizes . 128, 256, 512 1024
1534 2048 kib etc 
In my case Samsung 850evo  there are 8k pages and 1536 Erase Block

So first problem with alegment - partition should start on the  Erase block 
bounduary .  So --ebs  switch in partition tools for propper aignment  would
be practical. Or calculate by hand. In my sase 1536 = 3072 512b sectors.

Things get complicate if You use  mdadm raid. Because Raid superblock is 
located on the begining of the raid  device and  does not fill whole rerase 
block, it is practical to set in creation of raid  --offset to real 
filesystem start at next erase block from the begining of raid device so 
underlying filesystem would be aligned as well.  so --ebs=xxx on mdadm would
be practice

And now ext4  so blocksize 4096 . because page size of ssd is 8kb , setting 
stride´wit is a smallest unit on with filesystem operate in one disk to 2  
to fill ssd pagesize is practical. And stripe size set  as ebs/pagesize or 
as whole ebs . and may be it would be useful to use ext4 --offset to edb as 
well. 

this should align partition, raid and filesystem. fix me if I am wrong. 

And  now it is turn for database storage engine. I think try to write on 
erase block size bounduary and  erase block size amount of data may have 
some benefits not with the speed but in lower wear-out of the entire ssd 
disk.. 

  

  



-- Původní zpráva --
Od: Marcin Mańk 
Komu: PostgreSQL 
Datum: 24. 11. 2015 20:07:30
Předmět: [GENERAL] full_page_writes on SSD?

"

I saw this: http://blog.pgaddict.com/posts/postgresql-on-ssd-4kb-or-8kB-
pages(http://blog.pgaddict.com/posts/postgresql-on-ssd-4kb-or-8kB-pages)



It made me wonder: if SSDs have 4kB/8kB sectors, and we'd make the Postgres 
page size equal to the SSD page size, do we still need full_page_writes?




Regards

Marcin Mańk


"

Re: [GENERAL] full_page_writes on SSD?

[Tomas Vondra]

On 11/24/2015 08:14 PM, Andres Freund wrote:

On 2015-11-24 13:09:58 -0600, Kevin Grittner wrote:

On Tue, Nov 24, 2015 at 12:48 PM, Marcin Mańk  wrote:


if SSDs have 4kB/8kB sectors, and we'd make the Postgres page
size equal to the SSD page size, do we still need
full_page_writes?


If an OS write of the PostgreSQL page size has no chance of being
partially persisted (a/k/a torn), I don't think full page writes
are needed. That seems likely to be true if pg page size matches
SSD sector size.


At the very least it also needs to match the page size used by the
OS (4KB on x86).


Right. I find this possibility (when the OS and SSD page sizes match) 
interesting, exactly because it might make the storage resilient to torn 
pages.




But be generally wary of turning of fpw's if you use replication.
Not having them often turns a asynchronously batched write workload
into one containing a lot of synchronous, single threaded, reads.
Even with SSDs that can very quickly lead to not being able to keep
up with replay anymore.



I don't immediately see why that would happen? Can you elaborate?

regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
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] full_page_writes on SSD?

[Tomas Vondra]

On 11/24/2015 08:40 PM, John R Pierce wrote:

On 11/24/2015 10:48 AM, Marcin Mańk wrote:

I saw this:
http://blog.pgaddict.com/posts/postgresql-on-ssd-4kb-or-8kB-pages

It made me wonder: if SSDs have 4kB/8kB sectors, and we'd make the
Postgres page size equal to the SSD page size, do we still need
full_page_writes?



an SSD's actual write block is much much larger than that. they
emulate 512 or 4k sectors, but they are not actually written in
sector order, rather new writes are accumulated in a buffer on the
drive, then written out to a whole block, and a sector mapping table
is maintained by the drive.


I don't see how that's related to full_page_writes?

It's true that SSDs optimize the writes in various ways, generally along 
the lines you described, because they do work with "erase 
blocks"(generally 256kB - 1MB chunks) and such.


But the internal structure of SSD has very little to do with FPW because 
what matters is whether the on-drive write cache is volatile or not (SSD 
can't really work without it).


What matters (when it comes to resiliency to torn pages) is the page 
size at the OS level, because that's what's being handed over to the SSD.


Of course, there might be other benefits of further lowering page sizes 
at the OS/database level (and AFAIK there are SSD drives that use pages 
smaller than 4kB).


regards

--
Tomas Vondra  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


--
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] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

[Albe Laurenz]

NTPT wrote:
> I need help.
> 
> pg_exec(): Query failed: ERROR: character with byte sequence 0xc2 0x96 in 
> encoding "UTF8" has no
> equivalent in encoding "WIN1250"
> 
> It is a strange. First there was a database with latin2 encoding.
> 
> to this database connect an aplicaton with "set client encoding to win1250" 
> and manipulating data
> 
> then database was dumped with pg_dump -E UTF8
> 
> then database was restored pg_restore on another cluster in database with 
> UTF8 encoding
> 
> then application connect to new database with "set client encoding to win1250"
> 
> and - query failed
> 
> 
> How in this scenario could invaid  characters reach the database ???
> 
> And how to solve this ? Errort message is not very useful, because does not 
> provide any hint (at least
> column and row)

I can reproduce that, and I think it is a bug.

Hex 96 is Unicode Code Point 2013 in Windows-1250, that is an "en dash".

1) You enter this byte into a Latin 2 database with client_encoding WIN1250,
   and it gets stored as hex 96 in the database.

2) You dump this database with -E UTF8 and get hex C2 96 in the dump.

3) You restore this database to a new UTF8 database, the data end up
   as hex C2 96.

4) You query with client_encoding WIN1250 and get the error you quote.

Now I think that the bug is in step 1).
Wikipedia says that hex 96 is undefined in Latin 2
(https://en.wikipedia.org/wiki/ISO/IEC_8859-2),
so instead of storing this byte, PostgreSQL should have complained that it 
cannot be converted to Latin 2, since indeed there is no "em dash" defined
in Latin 2.

The bug seems to be in
backend/utils/mb/conversion_procs/latin2_and_win1250/latin2_and_win1250.c,
function win12502mic().
I think that the entries in win1250_2_iso88592 corresponding to undefined 
characters
should be 0x00 to produce an error.

Yours,
Laurenz Albe

-- 
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] full_page_writes on SSD?

[FarjadFarid(ChkNet)]

I am constantly using SSD both on my OS and database and have none of these 
problems. 

 

However I don’t use SSD for O/S’s virtual memory. 

 

>From what I have read of this thread. 

 

Potentially there could also be a situation that SSD is hitting its limit of 
auto recovery and has been over used. 

It is well known that using SSD’s for OS’s virtual memory causes SSDs to wear 
out much quicker. 

 

To really test all these. One needs to use a brand new SSD. Also ensure you are 
not using O/S’s virtual memory on the same SSD as DB and its log file. 

 

You might want to also double check the language of the OS and postgresql 
installed. As these determine the final size of memory used to read and write. 

 

 

 

 

From: pgsql-general-ow...@postgresql.org 
[mailto:pgsql-general-ow...@postgresql.org] On Behalf Of NTPT
Sent: 25 November 2015 12:10
To: Marcin Mańk
Cc: PostgreSQL
Subject: Re: [GENERAL] full_page_writes on SSD?

 

Hi, 


I investigate bit about SSD  and how it works and need to be aligned .

And I  conclude that in the ideal world we need a  general --ebs=xxx switch in 
various linux tools to ensure alignment. Or make calculation by had.. 

On the market there are SSD disks with page size 4 or 8 kb. But there is  for 
ssd disk typical property - the EBS - Erase Block Size. If disk operate and 
write to single sector, whole Erase block must be read by driver electronic, 
modified and write back to the drive. 

On the market there are devices with multiple EBS sizes . 128, 256, 512 1024 
1534 2048 kib etc 
In my case Samsung 850evo  there are 8k pages and 1536 Erase Block

So first problem with alegment - partition should start on the  Erase block 
bounduary .  So --ebs  switch in partition tools for propper aignment  would be 
practical. Or calculate by hand. In my sase 1536 = 3072 512b sectors.

Things get complicate if You use  mdadm raid. Because Raid superblock is 
located on the begining of the raid  device and  does not fill whole rerase 
block, it is practical to set in creation of raid  --offset to real filesystem 
start at next erase block from the begining of raid device so underlying 
filesystem would be aligned as well.  so --ebs=xxx on mdadm would be practice

And now ext4  so blocksize 4096 . because page size of ssd is 8kb , setting 
stride´wit is a smallest unit on with filesystem operate in one disk to 2  to 
fill ssd pagesize is practical. And stripe size set  as ebs/pagesize or as 
whole ebs . and may be it would be useful to use ext4 --offset to edb as well. 

this should align partition, raid and filesystem. fix me if I am wrong. 

And  now it is turn for database storage engine. I think try to write on erase 
block size bounduary and  erase block size amount of data may have some 
benefits not with the speed but in lower wear-out of the entire ssd disk.. 

  

  



-- Původní zpráva --
Od: Marcin Mańk 
Komu: PostgreSQL 
Datum: 24. 11. 2015 20:07:30
Předmět: [GENERAL] full_page_writes on SSD?

 

I saw this: http://blog.pgaddict.com/posts/postgresql-on-ssd-4kb-or-8kB-pages

 

It made me wonder: if SSDs have 4kB/8kB sectors, and we'd make the Postgres 
page size equal to the SSD page size, do we still need full_page_writes?

 

Regards

Marcin Mańk

=

[GENERAL] Re: [GENERAL] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

[NTPT]

Hi, 


Thanx for explanation. 


but how to solve this  in existing  database ? 



-- Původní zpráva --
Od: Albe Laurenz 
Komu: 'NTPT *EXTERN*' , pgsql-general@postgresql.org 
Datum: 25. 11. 2015 12:54:17
Předmět: Re: [GENERAL] Query failed: ERROR: character with byte sequence 0xc
2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

"NTPT wrote:
> I need help.
> 
> pg_exec(): Query failed: ERROR: character with byte sequence 0xc2 0x96 in 
encoding "UTF8" has no
> equivalent in encoding "WIN1250"
> 
> It is a strange. First there was a database with latin2 encoding.
> 
> to this database connect an aplicaton with "set client encoding to win
1250" and manipulating data
> 
> then database was dumped with pg_dump -E UTF8
> 
> then database was restored pg_restore on another cluster in database with 
UTF8 encoding
> 
> then application connect to new database with "set client encoding to win
1250"
> 
> and - query failed
> 
> 
> How in this scenario could invaid characters reach the database ???
> 
> And how to solve this ? Errort message is not very useful, because does 
not provide any hint (at least
> column and row)

I can reproduce that, and I think it is a bug.

Hex 96 is Unicode Code Point 2013 in Windows-1250, that is an "en dash".

1) You enter this byte into a Latin 2 database with client_encoding WIN1250,
and it gets stored as hex 96 in the database.

2) You dump this database with -E UTF8 and get hex C2 96 in the dump.

3) You restore this database to a new UTF8 database, the data end up
as hex C2 96.

4) You query with client_encoding WIN1250 and get the error you quote.

Now I think that the bug is in step 1).
Wikipedia says that hex 96 is undefined in Latin 2
(https://en.wikipedia.org/wiki/ISO/IEC_8859-2),
so instead of storing this byte, PostgreSQL should have complained that it 
cannot be converted to Latin 2, since indeed there is no "em dash" defined
in Latin 2.

The bug seems to be in
backend/utils/mb/conversion_procs/latin2_and_win1250/latin2_and_win1250.c,
function win12502mic().
I think that the entries in win1250_2_iso88592 corresponding to undefined 
characters
should be 0x00 to produce an error.

Yours,
Laurenz Albe

-- 
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] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

[Albe Laurenz]

NTPT wrote:
> but how to solve this  in existing  database ?

Either update all affected columns in the source database
or edit the database dump...

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] RE: [GENERAL] Query failed: ERROR: character with byte sequence 0xc2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

[NTPT]

but how to update affected columns ? error message does not  provide single 
 clue ( at least row name)

And dump-restore ?  It  do not underestand how it could help.. dumped as 
unicode  restore as unicode = I am at the same point ... dumping as latin2 
and restore to utf8 will end with the some errors..  I suspect 


-- Původní zpráva --
Od: Albe Laurenz 
Komu: 'NTPT *EXTERN*' 
Datum: 25. 11. 2015 14:47:46
Předmět: RE: [GENERAL] Query failed: ERROR: character with byte sequence 0xc
2 0x96 in encoding "UTF8" has no equivalent in encoding "WIN1250"

"NTPT wrote:
> but how to solve this in existing database ?

Either update all affected columns in the source database
or edit the database dump...

Yours,
Laurenz Albe"

Re: [GENERAL] Can row level security policies also be implemented for views?

[Stephen Frost]

Caleb,

* Caleb Meredith (calebmeredi...@gmail.com) wrote:
> I'm developing an application where strict control of my data is important.
> Views allow me to build a strict custom reading experience, allowing me to
> add computed columns and hide private and metadata columns. Row level
> security allows me strict write control of my data. However, I can't use
> both technologies together, why?

The short and simple answer is that it simply hasn't been done yet.

> It seems easy conceptually, RLS just adds a WHERE clause to queries if I'm
> not mistaken, and conceptually a view is just a query. The CURRENT_USER
> issue is valid, but personally it's not too big for me as most auth is done
> through database parameters.

The hard part is making sure that what happens when there are policies
on views actually makes sense and works as users expect.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] "trust" authentication in pg_hba.conf

[Stephen Frost]

* Chris Withers (ch...@simplistix.co.uk) wrote:
> What's the default contents of pg_hba.conf that postgres ships with?

The PG community provides both source code, which is expected to be used
by developers and is therefore wide open, and binary packages, which are
expected to be used by end users and therefore has sensible defaults for
authentication (mainly 'peer').

> I've been to it contains 'trust' for all local connections.
> Is this wise? Anyone who can get a shell on your database server can
> connect to any database as any user?

It is not wise to run with 'trust' in a non-development environment.

Thanks!

Stephen


signature.asc
Description: Digital signature

Re: [GENERAL] Problems with pg_upgrade after change of unix user running db.

[Bruce Momjian]

On Wed, Nov 25, 2015 at 08:04:49AM +, Benedikt Grundmann wrote:
> You can see the 9.5 requirements in the pg_upgrade function
> check_is_install_user().  You might as well just honor what that
> requires as you will eventually be moving to 9.5.
> 
> 
> Thanks I'll try this in one of the next days.  Sorry for the radio silence in
> the last 2 days.  We have been quite busy at work.  I don't think I understand

Sure, no problem.  I would have liked to reply to this sooner too, but
had to do some research.

> yet why this restriction exists (Neither the old nor the new).  Is there some
> doc somewhere that explains what's going on?  I tried to find something in the
> otherwise excellent postgres docs but failed.

The comments at the top of pg_upgrade.c do explain this:

 *  To simplify the upgrade process, we force certain system values to be
 *  identical between old and new clusters:
 *
 *  We control all assignments of pg_class.oid (and relfilenode) so toast
 *  oids are the same between old and new clusters.  This is important
 *  because toast oids are stored as toast pointers in user tables.
 *
 *  While pg_class.oid and pg_class.relfilenode are initially the same
 *  in a cluster, they can diverge due to CLUSTER, REINDEX, or VACUUM
 *  FULL.  In the new cluster, pg_class.oid and pg_class.relfilenode will
 *  be the same and will match the old pg_class.oid value.  Because of
 *  this, old/new pg_class.relfilenode values will not match if CLUSTER,
 *  REINDEX, or VACUUM FULL have been performed in the old cluster.
 *
 *  We control all assignments of pg_type.oid because these oids are stored
 *  in user composite type values.
 *
 *  We control all assignments of pg_enum.oid because these oids are stored
 *  in user tables as enum values.
 *
 *  We control all assignments of pg_authid.oid because these oids are stored  
<---
 *  in pg_largeobject_metadata.
<---

I never expected users to care, but based on what you did, you obviously
did need to care.  The good news is that the system generated an error
message that helped diagnose the problem, and the 9.5 error message is
much clearer.

-- 
  Bruce Momjian  http://momjian.us
  EnterpriseDB http://enterprisedb.com

+ As you are, so once was I. As I am, so you will be. +
+ Roman grave inscription +


-- 
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 a postgres table as a multi-writer multi-updater queue

[George Neuner]

On Wed, 25 Nov 2015 23:39:39 -0500, "Steve Petrie, P.Eng."
 wrote:

>My stupid email client software (Microsoft Outlook Express on Win XP) 
>refuses to respect its own "Reply" option settings for inline reply text.
>I've been looking for a replacement email client but so far without success.)

Without further comment about a 15 year old, unsupported OS ...

My vote for an email client would be Thunderbird.  It runs on XP or
higher and you can import Outlook's PST files so as to keep your mail
archives.  Importing PST files directly requires Outlook be available
on the same system [there is also a less friendly way to do it via EML
files exported from Outlook where Outlook is not on the same system].

Thunderbird directly supports net news, so you don't have to get news
mixed with your mail (unless you want to).


Hope this helps,
George



-- 
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 a postgres table as a multi-writer multi-updater queue

[Steve Petrie, P.Eng.]

(Kindly forgive my top-post. My stupid email client software (Microsoft Outlook 
Express on Win XP) refuses to respect its own "Reply" option settings for 
inline reply text. I've been looking for a replacement email client but so far 
without success.)

* * *
* * *

> You don't ever want to delete from such a table so you need to set up 
> something which allows you to truncate the tables when you no longer need 
> them.

I am migrating a web PHP application (called ITS-ETO) from mysql to postgres. 
The app INSERTs a row into a postgres table  to manage 
each session with a web browser. Here is the DDL for the session table:
  CREATE TABLE its_eto.eto_sql_tb_session_www
  (
session_www_code   char(32)  NOT NULL UNIQUE PRIMARY KEY,

session_www_type   int   NOT NULL,
session_www_state  int   NOT NULL,
session_verify_codechar(7)   NOT NULL,

session_www_serno  SERIALNOT NULL UNIQUE,

session_target_serno   int   NULL,
session_target_datavarchar(1000) NULL,

session_www_init_utc   timestamp NOT NULL,
session_www_last_utc   timestamp NOT NULL,
session_www_expiry_utc timestamp NOT NULL,
session_www_delete_utc timestamp NOT NULL,
session_www_hit_count  int   NOT NULL,
session_www_act_seqno  int   NULL

  );

  CREATE INDEX ON its_eto.eto_sql_tb_session_www (session_www_serno);
Using a "fuzzy" probability mechanism, some randomly-selected fraction of the 
HTTP requests that initiate a new session, also SELECT and DELETE expired rows 
from the session table. I naively assumed that the database server would 
automatically recycle the storage space dynamically released in this way.

Now, I'm reading in this forum that in fact, postgres does not efficiently 
automatically recycle storage space released by row DELETion.

* * *
* * *

My application is quite simple and will be supporting a modest workload, using 
a small amount of storage space, compared to the massive transaction rates and 
gigantic space usages, I'm reading about in this forum.

I do have the luxury of being able to shut down the application for a few 
minutes periodically e.g every 24 hours.

My plan was always, to avoid eventual exhaustion of the SERIAL sequence number 
integer value series, by swapping in during the periodic app shutdown, a 
freshly truncated postgres  table.

Before going online, the freshly truncated postgres  
table will receive INSERTs of any active session rows copied over from the old 
 table. The copied session rows will get new sequence 
numbers, but that won't matter, because a session row is referenced within each 
incoming HTTP request, not by its row serial number column  
integer value, but by a randomly-generated (MD5) 32-character unique key column 
 value. 

So my question to this postgres forum is -- should I just remove from the 
online app the "fuzzy" probability mechanism, that DELETEs expired rows from 
the session table -- because the postgres server is not going to dynamically 
recycle the released storage space anyway?

Any comments appreciated.

Steve

- Original Message - 
  From: Tim Uckun 
  To: Merlin Moncure 
  Cc: John R Pierce ; PostgreSQL General 
  Sent: Wednesday, November 25, 2015 3:50 PM
  Subject: Re: [GENERAL] using a postgres table as a multi-writer multi-updater 
queue


  I'll add my two cents.


  I set up something similar a while ago. Here are my suggestions for what they 
are worth.


  You don't ever want to delete from such a table so you need to set up 
something which allows you to truncate the tables when you no longer need them. 
 


  One way to accomplish this is with rules (not triggers, rules are blazingly 
fast compared to triggers).Set up a table inheritance scheme based on 
whatever search criteria you have (date, sequence etc).   Set up a cron job to 
create the child tables well ahead and to set up the rule redirecting the 
insert. For example let's say you have a date partitioned table and you want to 
 keep a table for every day.  Your cron job would run once a day and would 
create the next seven days worth of tables (just incase the cron job fails to 
run for some reason) and would rewrite the rule to insert into the table with a 
if then else type of logic.  This method is preferable to the dynamic creation 
of the table name with string concatenation because again it's significantly 
faster.  


  Another method I tried was to have one "primary" child table and "archival" 
child tables and insert directly into the primal child table.  For example say 
you have a table called "Q".  You set up a table called Q_in which inherits 
from Q.  Your code inserts into the Q_in table, you select from the Q table.  
On a periodic basis you do this 


  BEGIN TRANSACTION
  LOCK TABLE Q_IN IN EXCLUSIVE MODE;
  ALTER TABLE Q_IN RENAME TO Q_SOME_DATETIME;
  CREATE TABLE Q_IN
(LIKE Q_SOME_DATETIME INCLUDING