Copies or tables in schema or copiyng of database - database versioning
Hi Guys, I want to create copies of tables in postgresql to use them by each user in coherent way, So version 1 will be schema1, version 2 will be schema2 (with copies of tables from version 1). How to perfom it ? Using pg dump and creating new schemas (or databases is a better idea)? Or it is a way to write sql to create new schema and copying the older one? Best, Jacek
Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
Il 21/06/2018 23:31, Adrian Klaver ha scritto:
On 06/21/2018 08:36 AM, Moreno Andreo wrote:
Hi,
while playing with pgcrypto I ran into a strange issue
(postgresql 9.5.3 x86 on Windows 7)
Having a table with a field
dateofbirth text
I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21',
'AES_KEY') where codguid = '0001-0001-0001-0001-0001';
OK
select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc
from tbl_p where codguid = '0001-0001-0001-0001-0001'
'2018-06-21'
select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY')
= '2018-06-21'
You switched gears above.
What is the data type of the natoil field in table tab_paz?
Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY')
= '2018-06-21'
Was the data encrypted in it using the 'AES_KEY'?
Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's in a
where clause it seems not to be working.
Question about getting values from range of dates
Hi I am looking for suggestions about the best way to resolve this problem. Scenario I have entries for files in database which lack specific values (xml files not generated) These values can be obtained by what are called DTC read files, where the values are the same in the last DTC read file before date of file and the next DTC read file (by date) This code works but looks horrendous, so would appreciate any ideas. thanks Mike select max(a.recordingdate) ,max(b.recordingdate) ,a.registration,max(b.filename) from create_tdms a join (select registration,recordingdate,filename from create_tdms where filename not like 'DTC%') b on b.registration=a.registration where b.recordingdatea.recordingdate and a.filename like 'DTC%' group by a.registration
Re: Question about getting values from range of dates
Hi Mike Zitat von Mike Martin : I have entries for files in database which lack specific values (xml files not generated) These values can be obtained by what are called DTC read files, where the values are the same in the last DTC read file before date of file and the next DTC read file (by date) This code works but looks horrendous, so would appreciate any ideas. Bit is formatted so little? Sorry, I did not quite get the functional specification here, so I just put forward a technical optimised proposal. It seems to me equivalent to the following. Note I used Union ALL because the data sets of the two cases seem to be disjoint to me. The ALL makes PostgreSQL leave out the discarding process of identical rows (in the complete set, i. e. if there are identical rows in one part of the set they will be singled as well). Column aliasing is partly necessary partly to be clearer. with BASE as ( select a.recordingdate as recordingdate_a, b.recordingdate as recordingdate_b, a.registration as registration_a, b.filename as filename_b from create_tdmsa join ( select registration, recordingdate, filename from create_tdms where filename not like 'DTC%') b on b.registration = a.registration where a.filename like 'DTC%' ) select max (recordingdate_a) as max_recordingdate_a, max (recordingdate_b) as max_recordingdate_b, registration_aas registration, max (filename_b) as filename from BASE where recordingdate_b < recordingdate_a group by registration_a union all select max (recordingdate_a) as max_recordingdate_a, max (recordingdate_b) as max_recordingdate_b, registration_aas registration, max (filename_b) as filename from BASE where recordingdate_b > recordingdate_a group by registration_a Kind regards Thiemo This message was sent using IMP, the Internet Messaging Program.
Re: SQL Query never ending...
Hello Tom, good evening. Thank you for your prompt answer and help. It was enough to ANALYZE the temp tables to achieve a magnificent result: 37 seconds. I'm portuguese. It's a shame you live so far from me. So I can't put a box of bottles of Porto wine at your door. I also thank David and Fabrízio for their willingness to help me. They deserve a bottle of Porto wine each. Best regards Dias Costa On 21-06-2018 22:08, Tom Lane wrote: DiasCosta writes: This is the query plan for only 19684 rows. I think you're getting a bad query plan, mostly as a result of two factors: * Poor row estimates. It looks like the bottom-most misestimations are on temp tables, which makes me wonder whether you've ANALYZEd those tables. Your application has to do that explicitly after populating the tables; auto-analyze can't help on temp tables. * Too many tables --- I count 33 table scans in this query. You might get better planning results by raising join_collapse_limit and/or from_collapse_limit, but it will come at a cost in planning time, and in any case a query with this many tables is never likely to be cheap. You might want to think about restructuring your schema to not need so many tables, or maybe just do some hand optimization of the query to eliminate unnecessary joins. (It looks to me like at least some of the joins to tt_eotb1 might be unnecessary?) regards, tom lane -- J. M. Dias Costa Telef. 214026948 Telem. 939307421 Se divulgar esta mensagem por terceiros, por favor: 1. Apague o meu endereço de correio electrónico e o meu nome. 2. Apague também os endereços dos seus amigos antes de distribuir. 3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários. Agindo deste modo, dificultará a disseminação de "vírus", "spams" e "banners" e contribuirá para manter a privacidade de todos e cada um. Obrigado. Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o malfadado acordo ortográfico.
Re: Copies or tables in schema or copiyng of database - database versioning
On 06/22/2018 01:12 AM, Łukasz Jarych wrote: Hi Guys, I want to create copies of tables in postgresql to use them by each user in coherent way, So version 1 will be schema1, version 2 will be schema2 (with copies of tables from version 1). How to perfom it ? Using pg dump and creating new schemas (or databases is a better idea)? Or it is a way to write sql to create new schema and copying the older one? I am trying to understand what you want: 1) The tables will be exactly the same in each schema or will they differ? 2) Is a user going to be restricted to a particular schema? 3) What is the purpose of this layout? Best, Jacek -- Adrian Klaver adrian.kla...@aklaver.com
Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
On 06/22/2018 01:46 AM, Moreno Andreo wrote:
Il 21/06/2018 23:31, Adrian Klaver ha scritto:
On 06/21/2018 08:36 AM, Moreno Andreo wrote:
Hi,
while playing with pgcrypto I ran into a strange issue
(postgresql 9.5.3 x86 on Windows 7)
Having a table with a field
dateofbirth text
I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21',
'AES_KEY') where codguid = '0001-0001-0001-0001-0001';
OK
select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc
from tbl_p where codguid = '0001-0001-0001-0001-0001'
'2018-06-21'
select * from tab_paz where pgp_sym_decrypt(natoil::bytea, 'AES_KEY')
= '2018-06-21'
You switched gears above.
What is the data type of the natoil field in table tab_paz?
Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY')
= '2018-06-21'
Was the data encrypted in it using the 'AES_KEY'?
Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's in a
where clause it seems not to be working.
Are you sure that the entries where not encrypted with a different key
because I can't replicate.(More comments below):
create table pgp_test(id integer, fld_1 varchar);
insert into pgp_test values (1, pgp_sym_encrypt('2018-06-21', 'AES_KEY'))
select * from pgp_test ;
id |
fld_1
+
1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
select * from pgp_test where pgp_sym_decrypt(fld_1::bytea, 'AES_KEY') =
'2018-06-21';
id |
fld_1
+
1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
Have you looked at the entry in its encrypted state to see if it looks
the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?
Can you return decrypted values for other items in the table?
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Can PostgreSQL create new WAL files instead of reusing old ones?
Thomas, Thanks for taking a look at this. I'll work on getting a patch together for the master branch. I'll also take a look at the other question you raised and get back to you once I have more information. Thanks again, Jerry On Thu, Jun 21, 2018 at 10:20 PM, Thomas Munro < thomas.mu...@enterprisedb.com> wrote: > On Fri, Jun 22, 2018 at 11:22 AM, David Pacheco wrote: > > On Wed, Jun 20, 2018 at 10:35 AM, Jerry Jelinek < > jerry.jeli...@joyent.com> > > wrote: > >> I have implemented a small change to allow WAL recycling to be disabled. > >> It is visible at: > >> https://cr.joyent.us/#/c/4263/ > >> > >> I'd appreciate getting any feedback on this. > > >> tl;dr: We've found that under many conditions, PostgreSQL's re-use of > old > >> WAL files appears to significantly degrade query latency on ZFS. > > I haven't tested by it looks reasonable to me. It needs documentation > in doc/src/sgml/config.sgml. It should be listed in > src/backend/utils/misc/postgresql.conf.sample. We'd want a patch > against our master branch. Could you please register it in > commitfest.postgresql.org so we don't lose track of it? > > Hey, a question about PostgreSQL on ZFS: what do you guys think about > pg_flush_data() in fd.c? It does mmap(), msync(), munmap() to try to > influence writeback? I wonder if at least on some operating systems > that schlepps a bunch of data out of ZFS ARC into OS page cache, kinda > trashing the latter? > > -- > Thomas Munro > http://www.enterprisedb.com >
Re: Can PostgreSQL create new WAL files instead of reusing old ones?
On Wed, Jun 20, 2018 at 1:35 PM, Jerry Jelinek wrote: > As Dave described in his original email on this topic, we'd like to avoid > recycling WAL files since that can cause performance issues when we have a > read-modify-write on a file that has dropped out of the cache. > > I have implemented a small change to allow WAL recycling to be disabled. > It is visible at: > https://cr.joyent.us/#/c/4263/ > > I'd appreciate getting any feedback on this. > > This looks so simple, yet so beneficial. Thanks for making it. Is there some other mechanism that already cleans out the old unneeded WAL files? I recall there is something that does it when you start up after changing the number of files to keep, but I don't recall if that is tested over some loop regularly. Is there some way to make it auto-detect when it should be enabled? If not, please document that it should be used on ZFS and any other file system with CoW properties on files.
Re: Can PostgreSQL create new WAL files instead of reusing old ones?
> Is there some way to make it auto-detect when it should be enabled? If not, please document that it should be used on ZFS and any other file system with CoW properties on files. In addition to this, wondering what type of performance regression this would show on something like ext4 (if any).
Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
Il 22/06/2018 15:18, Adrian Klaver ha scritto:
On 06/22/2018 01:46 AM, Moreno Andreo wrote:
Il 21/06/2018 23:31, Adrian Klaver ha scritto:
On 06/21/2018 08:36 AM, Moreno Andreo wrote:
Hi,
while playing with pgcrypto I ran into a strange issue
(postgresql 9.5.3 x86 on Windows 7)
Having a table with a field
dateofbirth text
I made the following sequence of SQL commands
update tbl_p set dateofbirth = pgp_sym_encrypt('2018-06-21',
'AES_KEY') where codguid = '0001-0001-0001-0001-0001';
OK
select pgp_sym_decrypt(dateofbirth::bytea, 'AES_KEY') as datanasc
from tbl_p where codguid = '0001-0001-0001-0001-0001'
'2018-06-21'
select * from tab_paz where pgp_sym_decrypt(natoil::bytea,
'AES_KEY') = '2018-06-21'
You switched gears above.
What is the data type of the natoil field in table tab_paz?
Sorry, just a typo... natoil is, actually dateofbirth, so it's text.
You can read it as
select * from tbl_p where pgp_sym_decrypt(dateofbirth::bytea,
'AES_KEY') = '2018-06-21'
Was the data encrypted in it using the 'AES_KEY'?
Yes, the command sequence is exactly reported above.
If I use pgp_sym_decrypt in a SELECT statement it's OK, but if it's
in a where clause it seems not to be working.
Are you sure that the entries where not encrypted with a different key
because I can't replicate.(More comments below):
(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it
succeeded.
The only difference between the 2 fields, and I don't know if it can
make any sense, is that the field I tried now and succeeded was created
as text, while the other field (dateofbirth) was a timestamp I ALTERed
with the statement
alter table tbl_p alter column dateofbirth type text using
to_char(dateofbirth, '-MM-DD');
I'm just afraid it can happen in production
create table pgp_test(id integer, fld_1 varchar);
insert into pgp_test values (1, pgp_sym_encrypt('2018-06-21',
'AES_KEY'))
select * from pgp_test ;
id | fld_1
+
1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
select * from pgp_test where pgp_sym_decrypt(fld_1::bytea, 'AES_KEY')
= '2018-06-21';
id | fld_1
+
1 |
\xc30d04070302444e9b2792436e3d7ed23b01cf097f0a6a36298bab63ae4f22f39de54a9b0d8f905d48198ce76089de5f21669c46d96439718b6b0408c541427b6e7c11008bd3d0ebdae0dceb
Have you looked at the entry in its encrypted state to see if it looks
the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?
Yes, it seems to have the same value
Re: pgp_sym_decrypt() - error 39000: wrong key or corrupt data
On 06/22/2018 09:50 AM, Moreno Andreo wrote:
Il 22/06/2018 15:18, Adrian Klaver ha scritto:
Are you sure that the entries where not encrypted with a different key
because I can't replicate.(More comments below):
(other replies below, inline)
I'm almost sure (you're never absolutely sure :-) ), since I kept all
commands I entered in PgAdminIII SQL Window, and they're reported above.
On the other side, I tried the same procedure on another field and it
succeeded.
The only difference between the 2 fields, and I don't know if it can
make any sense, is that the field I tried now and succeeded was created
as text, while the other field (dateofbirth) was a timestamp I ALTERed
with the statement
alter table tbl_p alter column dateofbirth type text using
to_char(dateofbirth, '-MM-DD');
Assuming the ALTER TABLE was done and then the values where encrypted,
that does not seem to affect anything here(More below):
test=# create table pgp_alter_test(id integer, birthdate date);
CREATE TABLE
test=# \d pgp_alter_test
Table "public.pgp_alter_test"
Column | Type | Collation | Nullable | Default
---+-+---+--+-
id| integer | | |
birthdate | date| | |
test=# insert into pgp_alter_test values (1, '2018-06-21');
INSERT 0 1
test=# select * from pgp_alter_test ;
id | birthdate
+
1 | 2018-06-21
(1 row)
test=# alter table pgp_alter_test alter column birthdate type text using
to_char(birthdate, '-MM-DD');
ALTER TABLE
test=# \d pgp_alter_test
Table "public.pgp_alter_test"
Column | Type | Collation | Nullable | Default
---+-+---+--+-
id| integer | | |
birthdate | text|
test=# select * from pgp_alter_test ;
id | birthdate
+
1 | 2018-06-21
(1 row)
test=# update pgp_alter_test set birthdate = pgp_sym_encrypt(birthdate,
'AES_KEY') where id = 1;
UPDATE 1
test=# select * from pgp_alter_test ;
id |
birthdate
+
1 |
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c
(1 row)
^
test=# select * from pgp_alter_test where
pgp_sym_decrypt(birthdate::bytea, 'AES_KEY') = '2018-06-21';
id |
birthdate
+
1 |
\xc30d04070302b3f55c80f9ab657c68d23b010cd9d4d82631f89c786394a8bceb35529db07c708e5a0c4f04cf91aed24b5ff397dd99c678ec4f5bb769e148cfae3cdfc5453daaeb34ddd2737c
(1 row)
I am at a loss now. The only thing I can think of is that data itself is
actually corrupted. Maybe some sort of language encoding/collation
issue. Just not sure how to test that at the moment.
I'm just afraid it can happen in production
create table pgp_test(id integer, fld_1 varchar);
insert into pgp_test values (1, pgp_sym_encrypt('2018-06-21',
'AES_KEY'))
Have you looked at the entry in its encrypted state to see if it looks
the same as pgp_sym_encrypt('2018-06-21', 'AES_KEY')?
Yes, it seems to have the same value
So
--
Adrian Klaver
adrian.kla...@aklaver.com
Re: Can PostgreSQL create new WAL files instead of reusing old ones?
Hi, On 2018-06-22 11:41:45 -0400, Adam Brusselback wrote: > > Is there some way to make it auto-detect when it should be enabled? If > not, please document that it should be used on ZFS and any other file > system with CoW properties on files. > In addition to this, wondering what type of performance regression this > would show on something like ext4 (if any). It's a *massive* regression on ext4 & xfs. You can very trivially compare the performance of a new cluster (which doesn't have files to recycle) against one that's running for a while. Greetings, Andres Freund
